new_sdk_wu/www/new_sdk/application/crontab/ChannelSettleMonth.php

<?php
/**
 * 公会未结算数据统计定时器脚本
 * 
 */
namespace app\crontab;

set_time_limit(0);

use think\console\Command;
use think\console\Input;
use think\console\Output;
use think\console\Input\Argument;
use think\Db;
use think\Env;

class ChannelSettleMonth extends Command {
    
    protected $_output;

    protected function configure() {
        
        $this->addArgument('execMonth', Argument::OPTIONAL);          //指定统计的日期时,格式Y-m
        
        $this->setName('ChannelSettleMonth')->setDescription('公会未结算数据统计脚本');
    }

    protected function execute(Input $input, Output $output) {
        
        ini_set('memory_limit', '1024M');
        set_time_limit(3600);

        $month      = $input->getArgument('execMonth');     //指定的执行月份
        
        $this->_output  = $output;
        
        //指定统计的月份时
        if(!empty($month)){
            
            $start_time = mktime(0,0,0,date('m',strtotime($month)),01);
            $end_time   = mktime(0,0,0,date('m',strtotime($month))+1,01)-1;
        }
        //未指定统计的月份时
        else{
            $start_time = mktime(0,0,0,date('m')-1,01);     //上一个自然月的第一天
            $end_time   = mktime(0,0,0,date('m'),01)-1;     //上一个自然月的最后一天
        }
        
        $output->writeln(date('Y-m-d H:i:s')." ChannelSettleMonth start\r\n");

		//有流水的公会账号
		$guild_channel_list = Db::table('cy_pay')->alias('p')
        						->join('nw_channel c1', 'p.channel_id = c1.id')
								->join('nw_channel c2', 'c1.parent_id = c2.id')
								->join('nw_channel c3', 'c2.parent_id = c3.id')
								->field('DISTINCT (case when c2.`level`=1 then c2.id else c3.id end) as guild_channel_id')
								->where(['p.status'=>1,'p.create_time'=>['<=',$end_time]])
							//	->where(['p.status'=>1])
								->select();

        $output->writeln("guild_channel_list sql: ".Db::table('cy_pay')->getLastSql()."\r\n");
        
        if(!empty($guild_channel_list)){
            
            $arr = [];
            
            foreach($guild_channel_list as $key=>$value){
                
				$where = [];
				$where['p.status'] = 1;
				$channel_id = $value['guild_channel_id'];
				$channelIds = get_child_channel_arr($channel_id);
				array_push($channelIds,$channel_id);
				$where['p.channel_id'] = ['in',$channelIds];
				$where['p.settle_id'] = 0;
				$where['p.create_time'] =  ['<=',$end_time];

				$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")
								->where($where)
								->group('p.gameid')
								->select();

				$total_amt = $divide_amt = $total_cnt = 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){
						$template = '您尚有游戏未配置分成比例，游戏名：'.$det['game_name'].'，请先设置分成比例 。时间：'.date('Y-m-d H:i:s');
						$ddurl = Env::get('operat_url');
						curlDD($template, $ddurl,true);
					}
					else{
						$total_cnt += floatval($det['pay_cnt']);
						$total_amt += floatval($det['pay_amt']);
						$divide_amt += floatval(intval($det['pay_amt'] * $ratio) / 100);
					}
				}

                //总流水
                $settle_apply_amt = $unwithdraw_amt = $withdraw_apply_amt = 0;
                
                $settle_apply_amt = floatval(Db::table('nw_game_channel_divide_settle')->where(['channel_id'=>$channel_id,'first_audit_status'=>1,'second_audit_status'=>0])->sum('total_divide_amt'));
				
                $channelInfo = Db::table('nw_channel')->field('id,name,amount,js_amount')->where(['id'=>$channel_id])->find();
				if($channelInfo){
					$unwithdraw_amt = $channelInfo['js_amount'];
				}

                $withdraw_apply_amt = floatval(Db::table('nw_channel_withdraw')->where(['channel_id'=>$channel_id,'status'=>0])->sum('withdraw_amt'));
                  
                $arr[$key][0] = $channel_id;
                $arr[$key][1] = date('Y-m',$end_time);
                $arr[$key][2] = $total_amt;
                $arr[$key][3] = $total_cnt;
                $arr[$key][4] = $divide_amt;
                $arr[$key][5] = $settle_apply_amt;
                $arr[$key][6] = $unwithdraw_amt;
                $arr[$key][7] = $withdraw_apply_amt;
                $arr[$key][8] = time();
                $arr[$key][9] = time();
                
            }
            
            $arr_key    = array('channel_id','month','unsettle_total_amt','unsettle_cnt','unsettle_amt','settle_apply_amt','unwithdraw_amt','withdraw_apply_amt','create_time','update_time');
            $arr_update = array('unsettle_total_amt','unsettle_cnt','unsettle_amt','settle_apply_amt','unwithdraw_amt','withdraw_apply_amt','update_time');
            
            $execInfo = $this->multArray2InsertExec('nw_channel_month_data',$arr_key,$arr,$arr_update);
            
            
            if ($execInfo['code'] == 0) {
                $output->writeln("insert error:".json_encode($execInfo)." time:".date('Y-m-d H:i:s')."\r\n");
            }
        }
        
        $output->writeln(date('Y-m-d H:i:s')." ChannelSettleMonth end\r\n");
        
        exit;
    }
    
    
    /**
     * 多条数据同时转化成插入SQL语句,并执行SQL语句,支持批量更新和新增
     *
     * @param string $table    表名
     * @param array  $arr_key  是表字段名的key：$arr_key=array('field1','field2','field3')
     * @param array  $arr      是字段值 数组示例 arrat(('a','b','c'), ('bbc','bbb','caaa'),('add','bppp','cggg'))
     * @param array  $arr_list 是需要更新的表字段名的key $arr_list=array('field1','field2','field3')
     * @param string $split
     *
     * @return array
     */
    private function multArray2InsertExec($table,$arr_key,$arr,$arr_list,$split='`')
    {
        $arrValues=array();
        $arrListValues = array();
        
        if ( empty($table) || !is_array($arr_key) || !is_array($arr) || !is_array($arr_list)) {
            return ['code'=> 0, 'msg' => 'multArray2Insert param ERROR '];
        }
        
        $sql = "INSERT INTO %s( %s ) values %s on duplicate key update %s";
        
        foreach ($arr as $k => $v) {
            $arrValues[$k] = "'".implode("','", array_values($v))."'";
        }
        
        foreach ($arr_list as $k => $v) {
            $arrListValues[$k] = $v . "=values(".$v.')';
        }
        
        $sql = sprintf($sql,$table,"{$split}" .implode("{$split} ,{$split}",$arr_key) . "{$split}", "(". implode("),(",array_values($arrValues)) . ")",implode(',',$arrListValues));
       
        $this->_output->writeln(" insert sql: {$sql} \r\n");
        
        if($sql){
            try {
                $result =  Db::execute($sql);
            } catch (\think\exception\PDOException $e) {
                return ['code'=> 0, 'msg' => 'InsertSql ERROR '.$e->getMessage()];
            }
            
            return ['code'=> 1, 'msg' => 'SUCCESS'];
        }else{
            return ['code'=> 0, 'msg' => 'InsertSql Null'];
        }
    }
}