new_sdk_wu/www/new_sdk/application/admin/controller/ChannelSettle.php

<?php
/**
 * 渠道结算单管理相关控制器
 */

namespace app\admin\controller;
use app\common\library\MakeReportGo;
use app\common\model\Channel;
use app\common\model\Admin as AdminModel;
use app\common\model\Setting;
use app\common\library\MakeReport;
use app\common\library\FileUpload;
use think\Db;
use think\Exception;

class ChannelSettle extends Admin
{
    protected $redis;                       //redis的句柄对象
    /**
     * 不进行父类的登录验证，所以增加构造方法重写了父类的初始化方法
     */
    protected function _initialize()
    {
        parent::_initialize();
		$this->redis    = \think\Cache::store('default')->handler();
    }

    /**
     * 渠道结算单列表
     */
    public function index()
    {
		$settle_type		  = input('settle_type',0,'intval');
		$first_audit_status   = input('first_audit_status',0,'intval');
		$second_audit_status  = input('second_audit_status',0,'intval');
		$begin_time           = input('start','','trim');
		$end_time             = input('end','','trim');

        $channelModel       = new Channel;

        $where = [];
        //渠道名
        if(input('request.channel_id')!='')
        {
            $where['a.channel_id'] = input('request.channel_id',0,'intval');
        }
		//区服类型
		if(input('request.settle_type')!=''){
			$where['a.settle_type']	= $settle_type;
		}
		//一级审核状态
		if(input('request.first_audit_status')!=''){
			$where['a.first_audit_status']	= $first_audit_status;
		}
		//二级审核状态
		if(input('request.second_audit_status')!=''){
			$where['a.second_audit_status']	= $second_audit_status;
		}
	    //申请开始时间和结束时间不为空时
        if ($begin_time != '' && $end_time != '') {
            $where['a.create_time'] = [
                ['>=', strtotime($begin_time)],
                ['<=', strtotime($end_time . ' 23:59:59')],
            ];
        } //开始时间不为空时
        elseif ($begin_time != '') {
            $where['a.create_time'] = ['>=', strtotime($begin_time)];
        } //结束时间不为空时
        elseif ($end_time != '') {
            $where['a.create_time'] = ['<=', strtotime($end_time . ' 23:59:59')];
        }

        $channelList = $channelModel->getAllByCondition('id,name',['level'=>1,'flag'=>3],'name asc');

		if (request()->isAjax() && input('download')) {
			$sql = model('ChannelDivideSettle')->alias('a')
										->join('nw_channel c','a.channel_id=c.id','left')
										->field('a.*,c.name as channel_name')
										->where($where)
										->order('a.create_time desc')
										->fetchSql(true)->select();

            if((new MakeReportGo())->addTask('admin.settleIndex',$sql,session_id())){
                $this->success('报表生成的任务已经提交, 报表生成完成后，会及时通知您，请耐心稍等');
            }
            else{
                $this->error('报表生成任务不可重复提交，如遇到无法导出情况，建议修改查询条件解除当前状态，提交重新生成报表任务！');
            }

		}

        $param = input('request.');		//分页带条件

        $list       =	model('ChannelDivideSettle')->alias('a')
									    ->join('nw_channel c','a.channel_id=c.id','left')
                                        ->field('a.*,c.name as channel_name')
                                        ->where($where)
                                        ->order('a.create_time desc')
                                        ->paginate(10,false,array('query' => $param));

        // 获取分页显示的HTML
        $page = $list->render();

        $this->assign('channel_list',$channelList);
        $this->assign('list', $list);
        $this->assign('page', $page);
        $this->assign('start', $begin_time);
        $this->assign('end', $end_time);

        return $this->fetch();
    }
    /**
     * 二级结算单审核列表
     */
    public function checklist()
    {
		$settle_type		  = input('settle_type',0,'intval');
		$settle_period		  = input('settle_period',0,'intval');
		$second_audit_status  = input('second_audit_status',0,'intval');
		$begin_time           = input('start','','trim');
		$end_time             = input('end','','trim');

        $channelModel       = new Channel;

        $where = [];
		$where['a.first_audit_status'] = 1;
        //渠道名
        if(input('request.channel_id')!='')
        {
            $where['a.channel_id'] = input('request.channel_id',0,'intval');
        }
		//区服类型
		if(input('request.settle_type')!=''){
			$where['a.settle_type']	= $settle_type;
		}
		//结算方式
		if(input('request.settle_period')!=''){
			$where['a.settle_period']	= $settle_period;
		}
		//二级审核状态
		if(input('request.second_audit_status') != ''){
			$where['a.second_audit_status']	= $second_audit_status;
		}
	    //申请开始时间和结束时间不为空时
        if ($begin_time != '' && $end_time != '') {
            $where['a.create_time'] = [
                ['>=', strtotime($begin_time)],
                ['<=', strtotime($end_time . ' 23:59:59')],
            ];
        } //开始时间不为空时
        elseif ($begin_time != '') {
            $where['a.create_time'] = ['>=', strtotime($begin_time)];
        } //结束时间不为空时
        elseif ($end_time != '') {
            $where['a.create_time'] = ['<=', strtotime($end_time . ' 23:59:59')];
        }

        $channelList = $channelModel->getAllByCondition('id,name',['level'=>1,'flag'=>3],'name asc');


		if (request()->isAjax() && input('download')) {

			$sql = model('ChannelDivideSettle')->alias('a')
					->join('nw_channel c','a.channel_id=c.id','left')
                    ->field('a.*,c.name as channel_name')
                    ->where($where)
                    ->order('a.create_time desc')
					->fetchSql(true)->select();

            if((new MakeReportGo())->addTask('admin.settleCheckList',$sql,session_id())){
                $this->success('报表生成的任务已经提交, 报表生成完成后，会及时通知您，请耐心稍等');
            }
            else{
                $this->error('报表生成任务不可重复提交，如遇到无法导出情况，建议修改查询条件解除当前状态，提交重新生成报表任务！');
            }

		}

        $param = input('request.');		//分页带条件

        $list       =	model('ChannelDivideSettle')->alias('a')
									    ->join('nw_channel c','a.channel_id=c.id','left')
                                        ->field('a.*,c.name as channel_name')
                                        ->where($where)
                                        ->order('a.create_time desc')
                                        ->paginate(10,false,array('query' => $param));

        // 获取分页显示的HTML
        $page = $list->render();

        $this->assign('channel_list',$channelList);
        $this->assign('list', $list);
        $this->assign('page', $page);
        $this->assign('start', $begin_time);
        $this->assign('end', $end_time);

        return $this->fetch();
    }
	//一级审核
    public function audit()
    {
        if (request()->isAjax()) {
            $id = input('id',0,'intval');
            $beizhu = input('beizhu','','trim');
            $status = input('status','0','intval');

            if(empty($id)){
                $this->error('ID不能为空');
            }
	        if($status<>1 && $status<>2){
                $this->error('非法审核状态');
            }

			$settleInfo =  model('ChannelDivideSettle')->where(['id' => $id])->find();
			if(empty($settleInfo)){
				$this->error('结算单不存在');
			}
			else if($settleInfo['first_audit_status']<>0){
				$this->error('该结算单已经一级审核过,不能再进行审核');
			}

			if($status == $settleInfo['first_audit_status']){
				$this->error('您未更改订单状态,请选择要更改的状态');
			}

			//检查账号
			if(!$settleInfo['channel_id']){
				$this->error('结算单异常:无归属渠道');
			}
            $channelInfo = model('Channel')->where(['id'=>$settleInfo['channel_id']])->find();
            if (empty($channelInfo)){
				$this->error('结算单异常:结算单归属渠道不存在');
            }
			else if($channelInfo['level'] <> 1){
				$this->error('结算单异常:结算单归属渠道层级有误');
			}

			$auditData = array();
			$auditData['id'] = $id;
			$auditData['first_audit_status'] = $status;
			$auditData['first_audit_time'] = NOW_TIMESTAMP;
			$auditData['first_audit_remark'] = $beizhu;
			$auditData['first_audit_admin_name'] = session('USERNAME');

            Db::startTrans();
            try {
				if($status==2){  //审核不通过,退回
					$result = model('ChannelDivideSettle')->where(['id'=>$id,'first_audit_status'=>array('in',[0])])->update($auditData);
					if (!$result) {
						throw new Exception("更改结算单状态失败");
					}

					//恢复订单为未结算
					$updPayData = array();
					$updPayData['settle_channel_id']  = 0;
					$updPayData['settle_id']		  = 0;
					$updPayData['settle_update_time'] = 0;
					$updResult = model('Pay')->where(['settle_id'=>$settleInfo['id'],'settle_channel_id'=>$settleInfo['channel_id']])->update($updPayData);
					if ( !$updResult) {
						throw new Exception("更新订单为未结算失败");
					}

					$this->insertLog($this->current_node, "渠道结算单一级审核,结算单ID:".$settleInfo['id']."，审核状态：审核不通过",172);
				}
				else if($status==1){  //一级审核通过
					$result = model('ChannelDivideSettle')->where(['id'=>$id,'first_audit_status'=>array('in',[0])])->update($auditData);
					if (!$result) {
						throw new Exception("更改结算单状态失败");
					}
					$this->insertLog($this->current_node, "渠道结算单一级审核,结算单ID:".$settleInfo['id']."，审核状态：审核通过",172);
				}
                Db::commit();
            } catch (\Exception $e) {
                Db::rollback();
                $this->error("添加失败： " . $e->getMessage());
            }


			$this->success('审核成功');
        }
        else{
            $this->error('非法请求');
        }
    }

	//二级审核
    public function secondAudit()
    {
        if (request()->isAjax()) {
            $id = input('id',0,'intval');
            $beizhu = input('beizhu','','trim');
            $status = input('status','0','intval');

            if(empty($id)){
                $this->error('ID不能为空');
            }
	        if($status<>1 && $status<>2){
                $this->error('非法审核状态');
            }

			$settleInfo =  model('ChannelDivideSettle')->where(['id' => $id])->find();
			if(empty($settleInfo)){
				$this->error('结算单不存在');
			}
			else if($settleInfo['first_audit_status'] <> 1){
				$this->error('结算单一级审核未通过，不能进行审核');
			}
			else if($settleInfo['second_audit_status'] <> 0){
				$this->error('结算单审核已审核过，不能再进行审核');
			}

			if($status == $settleInfo['second_audit_status']){
				$this->error('您未更改订单状态,请选择要更改的状态');
			}

			//检查账号
			if(!$settleInfo['channel_id']){
				$this->error('结算单异常:无归属渠道');
			}
            $channelInfo = model('Channel')->where(['id'=>$settleInfo['channel_id']])->find();
            if (empty($channelInfo)){
				$this->error('结算单异常:结算单归属公会不存在');
            }
			else if($channelInfo['level'] <> 1){
				$this->error('结算单异常:结算单归属公会层级有误');
			}

			if($status==1){
				if($settleInfo['settle_method'] == 2){    //审核外放渠道结算单
					if(!$channelInfo['parent_id']){
						$this->error('结算单异常:结算单公会账号无所属商务！');
					}
					$parentChannelInfo   = model("Channel")->field("id,name,level,parent_id,status,channel_tg_type")->where(['id'=>$channelInfo['parent_id']])->find();
					if(empty($parentChannelInfo)){
						$this->error('结算单公会账号所属商务不存在,请确认结算审核单是否有误');
					}
					else if($parentChannelInfo['channel_tg_type'] <> 1){
						$this->error('结算单公会账号所属商务为非外部投放渠道,请确认结算申请是否有误！');
					}
					$relChannelInfo   = model("ChannelInfo")->where(['channel_id'=>$channelInfo['parent_id']])->find();
					if(!$relChannelInfo){
						$this->error('外部投放渠道结算审核，请先通知所属商务补充完身份认证相关信息后再进行操作');
					}
					$channelInfoApply   = model("ChannelInfo")->field("id,channel_id,real_name,apply_status,type,person_id,zfb_account,bank_ban_mobile,bank_number,bank_province_city,bank_name,bank_open_name,bank_open_number")->where(['channel_id'=>$channelInfo['parent_id'],'apply_status'=>1])->find();
					if(empty($channelInfoApply)){
						$this->error('外部投放渠道结算审核，所属商务的身份认证尚未认证通过,不能进行操作');
					}
				}
			}

			$auditData = array();
			$auditData['id'] = $id;
			$auditData['second_audit_status'] = $status;
			$auditData['second_audit_time'] = NOW_TIMESTAMP;
			$auditData['second_audit_remark'] = $beizhu;
			$auditData['second_audit_admin_name'] = session('USERNAME');

            Db::startTrans();
            try {
				if($status==1){  //审核通过
					//添加账户金额变动信息
					$detData = array();
					$detData['channel_id']      = $channelInfo['id'];
					$detData['channel_name']    = $channelInfo['name'];
					$detData['change_amount']	= $settleInfo['total_divide_amt'];
					$detData['account_type']	= 2;   //结算账户
					$detData['type']			= 8;   //结算收入
					$detData['out_orderid']		= $settleInfo['orderid'];
					$detData['create_time']     = NOW_TIMESTAMP;
					$insertDetId = model('ChannelAccountDet')->insertGetId($detData);
					if ( !$insertDetId) {
						throw new Exception("添加账户变动明细失败");
					}
					$updData = array();
					$updData['js_amount'] = Db::raw("js_amount+".$settleInfo['total_divide_amt']);
					$updData['update_time'] = time();
					$updResult = model('Channel')->where(['id'=>$channelInfo['id']])->update($updData);
					if (!$updResult) {
						throw new Exception("账户金额变动失败");
					}

					$result = model('ChannelDivideSettle')->where(['id'=>$id,'first_audit_status'=>array('in',[1]),'second_audit_status'=>array('in',[0])])->update($auditData);
					if ($result === false) {
						throw new Exception("更改结算单状态失败");
					}

					if($settleInfo['settle_method'] == 2){    //审核外放渠道结算单
						//指定时间内，禁止重复下单
						if(!requestDuplicateCheck('channel_withdraw_duplicate_'.$settleInfo['channel_id'],10)){
							throw new Exception("结算审核通过完成自动提现时请求过多，请于10s以后，再次进行提现操作");
						}
						$orderid    = 'W'.makeOrderid();
						$withdrawData = array();
						$withdrawData['orderid']		= $orderid;
						$withdrawData['channel_id']     = $channelInfo['id'];
						$withdrawData['channel_name']   = $channelInfo['name'];
						$withdrawData['withdraw_amt']	= $settleInfo['total_divide_amt'];
						$withdrawData['remark']			= '外部投放公会提现';
						$withdrawData['status']			= 1;
						$withdrawData['create_time']	= time();
						$withdrawData['audit_time']		= time();
						$withdrawData['audit_remark']	= '外部投放公会提现自动审核通过';
						$withdrawData['audit_admin_name'] = session('USERNAME');

						$withdrawData['real_name']		= $channelInfoApply['real_name'];
						$withdrawData['person_id']		= $channelInfoApply['person_id'];
						$withdrawData['zfb_account']	= $channelInfoApply['zfb_account'];
						$withdrawData['bank_ban_mobile']= $channelInfoApply['bank_ban_mobile'];
						$withdrawData['bank_number']	= $channelInfoApply['bank_number'];
						$withdrawData['bank_province_city']	= $channelInfoApply['bank_province_city'];
						$withdrawData['bank_name']		= $channelInfoApply['bank_name'];
						$withdrawData['bank_open_name']	= $channelInfoApply['bank_open_name'];
						$withdrawData['bank_open_number']	= $channelInfoApply['bank_open_number'];

						$insertWithdrawId = model("ChannelWithdraw")->insert($withdrawData);
						if(!$insertWithdrawId){
							throw new Exception("创建提现订单失败! ");
						}

						$detData = array();
						$detData['channel_id']      = $channelInfo['id'];
						$detData['channel_name']    = $channelInfo['name'];
						$detData['change_amount']	= -$settleInfo['total_divide_amt'];
						$detData['account_type']	= 2;   //结算账户
						$detData['type']			= 9;   //提现
						$detData['out_orderid']		= $orderid;
						$detData['create_time']     = NOW_TIMESTAMP;
						$insertDetId = model('ChannelAccountDet')->insertGetId($detData);
						if ( !$insertDetId) {
							throw new Exception("添加账户变动明细失败");
						}
						$updData = array();
						$updData['js_amount'] = Db::raw("js_amount-".$settleInfo['total_divide_amt']);
						$updData['update_time'] = time();
						$updResult = model('Channel')->where(['id'=>$channelInfo['id'],'js_amount'=>array('egt',$settleInfo['total_divide_amt'])])->update($updData);
						if (!$updResult) {
							throw new Exception("账户金额变动失败");
						}
						$this->redis->del('channel_withdraw_duplicate_'.$settleInfo['channel_id']);
					}

					$this->insertLog($this->current_node, "外部投放渠道结算单审核,结算单ID:".$settleInfo['id']."，审核状态：审核通过",172);

				}
				else if($status==2){  //接单已刷单改状态
					$result = model('ChannelDivideSettle')->where(['id'=>$id,'first_audit_status'=>array('in',[1]),'second_audit_status'=>array('in',[0])])->update($auditData);
					if ($result === false) {
						throw new Exception("更改结算单状态失败");
					}

					//恢复订单为未结算
					$updPayData = array();
					$updPayData['settle_channel_id']  = 0;
					$updPayData['settle_id']		  = 0;
					$updPayData['settle_update_time'] = 0;
					$updResult = model('Pay')->where(['settle_id'=>$settleInfo['id'],'settle_channel_id'=>$settleInfo['channel_id']])->update($updPayData);
					if ( !$updResult) {
						throw new Exception("更新订单为未结算失败");
					}
					$this->insertLog($this->current_node, "渠道结算单审核,结算单ID:".$settleInfo['id']."，审核状态：审核不通过",172);
				}
                Db::commit();
            } catch (\Exception $e) {
                Db::rollback();
                $this->error("添加失败： " . $e->getMessage());
            }

            $adminInfo = model('ChannelAdmin')->where(['channel_id'=>$settleInfo['channel_id'],'mobile'=>['neq','']])->find();
            if (!empty($adminInfo) && $adminInfo['mobile']){
				$result = (new \app\common\library\Sms)->sendSettleAuditInfo($channelInfo['name'], $adminInfo['mobile'], $settleInfo['orderid'], $status);
            }

			$this->success('审核成功');
        }
        else{
            $this->error('非法请求');
        }
    }

    /**
     * 渠道结算单列表
     */
    public function detail()
    {
		$settleId               = input('settleId',0,'intval');
		if(!$settleId){
			$this->error('请选择结算单ID');
		}
		$where = array();
		$where['id']		   = $settleId;
		$settleInfo = model("ChannelDivideSettle")->where($where)->field("id,orderid,total_amount,total_divide_amt,remark")->find();
		if(!$settleInfo){
			$this->error('该结算单不存在或您不能查看');
		}

		$where = array();
		$where['p.settle_id'] = $settleId;
		//	var_dump($where);
		$settleDetInfo = model("ChannelDivideSettleDet")->alias('p')
							->join('cy_game g', 'p.game_id = g.id','left')
							->field("p.game_id AS game_id,g.name as game_name,p.pay_amt,p.ratio,p.divide_amt,p.coupon_amount_amt,p.actual_amount")
							->where($where)
							->select();

        $param = input('request.');		//分页带条件
		$where = array();
		$where['p.settle_id'] = $settleId;
		$detPaylist = model("ChannelDivideSettleDetPay")->alias('p')
							->join('cy_game g', 'p.game_id = g.id','left')
							->field("p.orderid,p.username,p.create_time,p.game_id AS game_id,g.name as game_name,p.amount AS amount,p.divide_amt,p.ratio,p.serverid,p.servername,p.rolename,p.coupon_amount,p.actual_amount")
							->where($where)
                            ->order('p.create_time desc')
                            ->paginate(10,false,array('query' => $param));
        // 获取分页显示的HTML
        $page = $detPaylist->render();

        $this->assign('settleInfo',$settleInfo);
        $this->assign('settleDetInfo',$settleDetInfo);
        $this->assign('detPaylist',$detPaylist);
        $this->assign('page', $page);

        return $this->fetch('detail');
    }
    /**
     * 二级审核详情
     */
    public function secondDetail()
    {
		$this->detail();
		return $this->fetch('detail');
    }

    /**
     * 未结算数据
     */
    public function unsettle()
    {
		$settle_type		= input('settle_type',0,'intval');
		$begin_time         = input('start','','trim');
		$end_time           = input('end','','trim');
		$channel_id			= input('channel_id',0,'intval');

        //渠道名
        if($channel_id){
			$ShowDataList = 1;

			$where = [];
			$where['p.status'] = 1;
			$where['p.settle_id'] = array('eq',0);
			$channelIds = get_child_channel_arr($channel_id);
			array_push($channelIds,$channel_id);
			$where['p.channel_id'] = ['in',$channelIds];

			//区服类型
			if(input('request.settle_type')!=''){
				$where['g.game_kind']	= $settle_type;
			}
			//申请开始时间和结束时间不为空时
			if ($begin_time != '' && $end_time != '') {
				$where['p.create_time'] = [
					['>=', strtotime($begin_time)],
					['<=', strtotime($end_time . ' 23:59:59')],
				];
			} //开始时间不为空时
			elseif ($begin_time != '') {
				$where['p.create_time'] = ['>=', strtotime($begin_time)];
			} //结束时间不为空时
			elseif ($end_time != '') {
				$where['p.create_time'] = ['<=', strtotime($end_time . ' 23:59:59')];
			}
            $where['g.id'] = ['in', model('game')->getTakingGame($channel_id)];
			$GameChannelData = Db::table('cy_pay')->alias('p')
							->join('cy_game g', 'p.gameid = g.id','inner')
							->join('nw_game_channel_divide d','p.gameid = d.game_id and d.channel_id='.$channel_id,'left')
							->field("p.gameid AS game_id,g.name as game_name,g.channel_split_ratio,count(*) as pay_cnt,SUM(p.amount) AS pay_amt,d.id as ratio_id,d.ratio,SUM(p.real_amount) AS pay_real_amt,SUM(p.real_ptb) AS pay_real_ptb,SUM(p.coupon_amount) as coupon_amount_amt")
							->where($where)
							->group('p.gameid')
							->select();
			$retData = array();
			$total_amt = $divide_amt = $total_cnt = $total_coupon_amount_amt = 0;
			foreach ( $GameChannelData as &$det ) {
				if(intval($det['ratio_id'])){
					$ratio_id = $det['ratio_id'];
					$ratio = floatval($det['ratio']);
				}
				else{
					$ratio_id = 0;
					$ratio = floatval($det['channel_split_ratio']);
				}
				if(!$ratio){
					$this->jsonResult('', 0, '您尚有游戏未配置分成比例,请先联系平台设置');
				}
				else{
					$det['ratio_id'] = $ratio_id;
					$det['ratio']	 = $ratio;
					$total_cnt += floatval($det['pay_cnt']);
					$total_amt += floatval($det['pay_amt']);
					$total_coupon_amount_amt += floatval($det['coupon_amount_amt']);
					$divide_amt += floatval(intval(($det['pay_amt']-$det['coupon_amount_amt']) * $det['ratio']) / 100);
					$det['divide_amt'] = floatval(intval(($det['pay_amt']-$det['coupon_amount_amt']) * $det['ratio']) / 100);
				}
			}
			$settle = array();
			$settle['total_amount']  = $total_amt;
			$settle['divide_amount'] = $divide_amt;
			$settle['total_cnt']  = $total_cnt;
            $settle['total_coupon_amount_amt']  = $total_coupon_amount_amt;
			$param = input('request.');		//分页带条件

			$detPaylist = Db::table('cy_pay')->alias('p')
							->join('cy_game g', 'p.gameid = g.id','inner')
							->join('nw_game_channel_divide d','p.gameid = d.game_id and d.channel_id='.$channel_id,'left')
							->field("p.orderid AS orderid,p.gameid AS game_id,g.name as game_name,p.amount AS amount,round((p.amount-p.coupon_amount)*(case when d.id then d.ratio else g.channel_split_ratio end)/100,2) AS divide_amt,case when d.id then d.id else '0' end as ratio_id,case when d.id then d.ratio else g.channel_split_ratio end as ratio,p.real_amount AS real_amount,p.real_ptb AS real_ptb,p.serverid,p.servername,p.rolename,p.userid,p.username,p.create_time,p.coupon_amount")
							->where($where)
							->order("p.id desc")
							->paginate(10,false,array('query' => $param));
		//	var_dump($detPaylist);

			// 获取分页显示的HTML
			$page = $detPaylist->render();
			$this->assign('settle', $settle);
			$this->assign('settleDet', $GameChannelData);
			$this->assign('detPaylist', $detPaylist);
			$this->assign('page', $page);
		}
		else{
			$ShowDataList = 0;
		}

		$channelModel = new Channel;
		$channelList  = $channelModel->getAllByCondition('id,name',['level'=>1,'flag'=>3],'name asc');
        $this->assign('channel_list',$channelList);

        $this->assign('start', $begin_time);
        $this->assign('end', $end_time);
        $this->assign('ShowDataList', $ShowDataList);

        return $this->fetch();
    }
	public function downSettleMonthData(){
		if(input('download')==2){
			$month = date('Y-m',mktime(0,0,0,date('m'),01)-1);
			$where = array();
			$where['a.month'] = $month;
			$list =	Db::table('nw_channel_month_data')->alias('a')
											->join('nw_channel c','a.channel_id=c.id','left')
											->field('a.id,a.month,a.channel_id,a.unsettle_amt,a.settle_apply_amt,a.unwithdraw_amt,a.withdraw_apply_amt,(a.unsettle_amt+a.settle_apply_amt+a.unwithdraw_amt+a.withdraw_apply_amt) as total_amt,c.name as channel_name,a.create_time,a.update_time')
											->where($where)
											->order('a.create_time desc')
											->select();

			$title = "会长上月末数据-".date('YmdHis');
			if(!$list){
			    $this->error('暂无数据');
			}

			$this->downloadexls($list,$title,'settleMonthData');
			exit();
		}
		else{
			$this->error('参数错误！');
		}
	}
    /**
     * 外放推广结算处理
     */
    public function doSettle()
    {
		if ($this->request->isPost()) {
			$channel_id		= input('channel_id',0,'intval');            //流水
			$total_amount	= input('total_amount',0,'floatval');        //流水
			$divide_amount	= input('divide_amount',0,'floatval');       //分成金额
			$total_cnt		= input('total_cnt',0,'intval');             //结算订单笔数
			$begin_date		= input('begin_date','','trim');             //开始时间
			$end_date		= input('end_date','','trim');             //开始时间
			$settle_type	= input('settle_type','','intval');        //区服类型
			$remark			= '外放推广手动结算处理';                   //备注

			if(!$channel_id){
				$this->jsonResult('', 0, '请选择要结算的公会账号');
			}
			if(!in_array($settle_type,[1,2])){
				$this->jsonResult('', 0, '请选择区服类型');
			}
			if(!$begin_date || !$end_date){
				$this->jsonResult('', 0, '请选择结算周期');
			}
			if(!$total_amount || !$divide_amount || !$total_cnt){
				$this->jsonResult('', 0, '没有可结算单的订单');
			}

			if($settle_type==1){
				$orderid    = 'ZFS'.makeOrderid();
			}
			else if($settle_type==2){
				$orderid    = 'HFS'.makeOrderid();
			}
			else{
				$this->jsonResult('', 0, '区服类型异常，请选择正确的区服类型！！');
			}

			$channelInfo   = model("Channel")->field("id,name,parent_id,level,cps_settle_period,mcps_settle_period,status")->where(['id'=>$channel_id,'level'=>1])->find();
			if(empty($channelInfo)){
				$this->jsonResult('', 0, '公会账号异常,请选择正确的公会账号');
			}
			//只能结算外放的公会
			$parentChannelInfo = model("Channel")->field("id,name,level,cps_settle_period,mcps_settle_period,status,channel_tg_type")->where(['id'=>$channelInfo['parent_id'],'level'=>0])->find();
			if(empty($parentChannelInfo)){
				$this->jsonResult('', 0, '公会所属商务账号异常');
			}
			else if($parentChannelInfo['channel_tg_type']<>1){
				$this->jsonResult('', 0, '非外放商务下公会不能使用该功能');
			}
			/*
			$channelInfoApply   = model("ChannelInfo")->field("id,channel_id,real_name,apply_status,type")->where(['channel_id'=>$parentChannelInfo['id'],'apply_status'=>1])->find();
			if(empty($channelInfoApply)){
				$this->jsonResult('', 0, '该公会所属商务的身份尚未认证通过,不能进行结算');
			}
			*/

			$fromTime = strtotime($begin_date);
			$toTime = strtotime($end_date)+60*60*24-1;

			$where = array();
			$where['p.status'] = 1;
            $where['p.create_time'] = [
                ['>=', $fromTime],
                ['<=', $toTime],
            ];
			//所有下级渠道(包括自己)
			$channelIds = get_child_channel_arr($channel_id);
			array_push($channelIds,$channel_id);
			$where['p.channel_id'] = ['in',$channelIds];
			$where['p.settle_id'] = array('eq',0);

		//	var_dump($where);
			$GameChannelData = Db::table('cy_pay')->alias('p')
								->join('cy_game g', 'p.gameid = g.id and g.game_kind='.$settle_type,'inner')
								->join('nw_game_channel_divide d','p.gameid = d.game_id and d.channel_id='.$channel_id,'left')
								->field("p.gameid AS game_id,g.name as game_name,g.channel_split_ratio,min(p.create_time) as begin_time,max(p.create_time) as end_time,count(*) as pay_cnt,SUM(p.amount) AS pay_amt,d.id as ratio_id,d.ratio,SUM(p.real_amount) AS pay_real_amt,SUM(p.real_ptb) AS pay_real_ptb")
								->where($where)
								->group('p.gameid')
								->select();
			$retData = array();
		//	echo Db::table('cy_pay')->getLastSql()."----sql-----------<br>";
			$totalAmount = $divideAmount = $totalCnt = 0;
			$beginTime = time();
			$endTime = 0;
			foreach ( $GameChannelData as &$det ) {
				if(intval($det['ratio_id'])){
					$ratio_id = $det['ratio_id'];
					$ratio = floatval($det['ratio']);
				}
				else{
					$ratio_id = 0;
					$ratio = floatval($det['channel_split_ratio']);
				}
				if(!$ratio){
					$this->jsonResult('', 0, '您尚有游戏未配置分成比例,请先联系平台设置');
				}
				else{
					$det['ratio_id'] = $ratio_id;
					$det['ratio']	 = $ratio;
					$totalCnt += floatval($det['pay_cnt']);
					$totalAmount += floatval($det['pay_amt']);
					$divideAmount += floatval(intval($det['pay_amt'] * $det['ratio']) / 100);
					$det['divide_amt'] = floatval(intval($det['pay_amt'] * $det['ratio']) / 100);

					if($beginTime > $det['begin_time']){
						$beginTime = $det['begin_time'];
					}
					if($endTime < $det['end_time']){
						$endTime = $det['end_time'];
					}
				}
			}
			reset($GameChannelData);

			if(!$totalAmount || !$divideAmount || !$totalCnt){
				$this->jsonResult('', 0, '您已没有可结算的订单');
			}
			if(abs($totalAmount-$total_amount)>0.01 || $totalCnt<>$total_cnt || abs($divideAmount-$divide_amount)>0.01){
				$this->jsonResult('', 0, '结算数据与后台计算数据不一致,请重新核对后再提交');
			}

			//指定时间内，禁止重复下单
			if(!requestDuplicateCheck('channel_settle_duplicate_'.$channel_id,10)){
				$this->jsonResult('', 0, '结算请求过多，请于10s以后，再次进行结算操作');
			}

			$detPaylist = Db::table('cy_pay')->alias('p')
							->join('cy_game g', 'p.gameid = g.id and g.game_kind='.$settle_type,'inner')
							->join('nw_game_channel_divide d','p.gameid = d.game_id and d.channel_id='.$channel_id,'left')
							->field("p.id as payid,p.orderid AS orderid,p.gameid AS game_id,p.userid,p.username,g.name as game_name,g.channel_split_ratio,p.channel_id,p.amount AS amount,(p.amount*(case when d.id then d.ratio else g.channel_split_ratio end)/100) AS divide_amt,case when d.id then d.id else '0' end as ratio_id,case when d.id then d.ratio else g.channel_split_ratio end as ratio,p.real_amount AS real_amount,p.real_ptb AS real_ptb,p.create_time,p.serverid,p.servername,p.rolename")
							->where($where)
							->select();

			// 启动事务
			Db::startTrans();
			try{
				$settleData = array();
				$settleData['orderid'] = $orderid;
				$settleData['settle_type'] = $settle_type;
				$settleData['channel_id'] = $channel_id;
				$settleData['channel_name'] = $channelInfo['name'];
				$settleData['total_amount'] = $totalAmount;
				$settleData['total_divide_amt'] = $divideAmount;
				$settleData['begin_time'] = $beginTime;
				$settleData['end_time'] = $endTime;
				$settleData['remark'] = $remark;
				$settleData['create_time'] = time();
				$settleData['first_audit_status'] = 1;
				$settleData['first_audit_time'] = time();
				$settleData['settle_period'] = 1;
				$insertSettleId = model('ChannelDivideSettle')->insertGetId($settleData);
				if ( !$insertSettleId) {
					throw new Exception("添加结算单失败");
				}

				while(list($key,$val)=@each($GameChannelData)){
					$detData = array();
                    $detData['settle_id']		= $insertSettleId;
                    $detData['divide_id']		= $val['ratio_id'];
                    $detData['begin_time']		= $val['begin_time'];
					$detData['end_time']		= $val['end_time'];
                    $detData['game_id']			= $val['game_id'];
                    $detData['channel_id']		= $channel_id;
                    $detData['ratio']			= $val['ratio'];
                    $detData['pay_cnt']			= $val['pay_cnt'];
                    $detData['pay_amt']			= floatval($val['pay_amt']);
                    $detData['pay_real_amt']	= floatval($val['pay_real_amt']);
                    $detData['pay_real_ptb']	= floatval($val['pay_real_ptb']);
                    $detData['divide_amt']		= floatval($val['divide_amt']);
                    $detData['create_time']     = time();
                    ${'insertDet'.$val['game_id'].'ID'} = model('ChannelDivideSettleDet')->insertGetId($detData);
					if ( !${'insertDet'.$val['game_id'].'ID'}) {
						throw new Exception("添加结算单明细失败");
					}
				}

				$orderIds = array();
				while(list($key,$val)=@each($detPaylist)){
					$orderIds[]	= $val['payid'];
				//	echo ${'insertDet'.$val['game_id'].'ID'}."---insertDet".$val['game_id']."ID'--------<br>";
					$detPayData['settle_id']		= $insertSettleId;
					$detPayData['settle_det_id']	= ${'insertDet'.$val['game_id'].'ID'};
					$detPayData['orderid']			= $val['orderid'];
					$detPayData['game_id']			= $val['game_id'];
					$detPayData['channel_id']		= $val['channel_id'];
					$detPayData['ratio']			= $val['ratio'];
					$detPayData['userid']			= $val['userid'];
					$detPayData['username']			= $val['username'];
					$detPayData['amount']			= floatval($val['amount']);
					$detPayData['real_amount']		= floatval($val['real_amount']);
					$detPayData['real_ptb']			= floatval($val['real_ptb']);
					$detPayData['divide_amt']		= floatval($val['divide_amt']);
					$detPayData['serverid']			= trim($val['serverid']);
					$detPayData['servername']		= trim($val['servername']);
					$detPayData['rolename']			= trim($val['rolename']);
					$detPayData['create_time']		= $val['create_time'];
					$detPayData['add_time']			= time();
				//	var_dump($detPayData);
					$insertDetPayId = model('ChannelDivideSettleDetPay')->insertGetId($detPayData);
					if ( !$insertDetPayId) {
						throw new Exception("添加结算订单明细失败");
					}
				}

				$updPayData = array();
				$updPayData['settle_channel_id']  = $channel_id;
				$updPayData['settle_id']		  = $insertSettleId;
				$updPayData['settle_update_time'] = time();
				/*
				$gameids = model('Game')->where(['game_kind'=>2])->column('id');
				$where['p.gameid']= array('IN',$gameids);
				*/
				$where['p.id']= array('IN',$orderIds);
				$updResult = model('Pay')->alias('p')->where($where)->update($updPayData);
				if ( !$updResult) {
					throw new Exception("更新订单为已结算失败");
				}

				$this->redis->del('channel_settle_duplicate_'.$channel_id);

				// 提交事务
				Db::commit();

			} catch (\Exception $e) {
				// 回滚事务
				Db::rollback();

				$this->jsonResult('', 0, '结算申请生成失败'.$e->getMessage());
			}

			$result['orderid']      = $orderid;

			$this->jsonResult($result, 20000, '结算申请生成成功');
			exit;
		}
		else{
			$this->jsonResult('', 0, '非法请求');
			exit;
		}
    }
}