new_sdk_wu/www/new_sdk/application/crontab/ChannelExtraPointMonth.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 ChannelExtraPointMonth extends Command {
    
    protected $_output;

    protected function configure() {
        
        $this->addArgument('execMonth', Argument::OPTIONAL);          //指定统计的日期时,格式Y-m
        
        $this->setName('ChannelExtraPointMonth')->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')." ChannelExtraPointMonth start\r\n");

	
		$currentMonth = date('Y-m',$end_time);
		$delResult = Db::table('nw_game_channel_extra_divide_data')->where(['month'=>$currentMonth])->delete();

		//有流水的公会账号
		$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.create_time'=>[['>=', $start_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 = [];
            $key2 = 0;

			$TopChannelIds = array();

            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.create_time'] = [['>=', $start_time],['<=', $end_time]];


				$parentChannelInfo = Db::table('nw_channel')->alias('c')
										->join('nw_channel par', 'c.parent_id = par.id','left')
										->field("par.id as par_channel_id,par.name as par_channel_name,par.level,par.channel_tg_type")
										->where(['c.id'=>$channel_id])
										->find();
				if($parentChannelInfo['channel_tg_type'] == 1 || $parentChannelInfo['channel_tg_type'] == 2){  //外部渠道保存商务ID
					if(!in_array($parentChannelInfo['par_channel_id'],$TopChannelIds)){
						$TopChannelIds[] = $parentChannelInfo['par_channel_id'];
					}
				}
				else{     //普通渠道
					$GameChannelData = Db::table('cy_pay')->alias('p')
									->join('nw_extra_subgame sub', 'p.gameid = sub.game_id','inner')
									->join('nw_extra_game g', 'sub.extra_game_id = g.id','inner')
									->join('nw_game_extra_divide c', 'g.id = c.extra_game_id','left')
									->join('nw_game_channel_extra_divide d','g.id = d.extra_game_id and d.channel_id='.$channel_id,'left')
									->field("g.id AS extra_game_id,g.extra_game_name as game_name,c.id as game_divide_id,c.divide as game_divide,count(*) as pay_cnt,SUM(p.amount) AS pay_amt,d.id as channel_divide_id,d.divide as channel_divide")
									->where($where)
									->group('g.id')
									->select();

					$pay_amt = $divide_amt = $pay_cnt = $rate = 0;
					foreach ( $GameChannelData as &$det ) {
						if(intval($det['channel_divide_id'])){
							$divide_type = 2;
							$divide_id	 = $det['channel_divide_id'];
							$divide		 = $det['channel_divide'];
						}
						else if(intval($det['game_divide_id'])){
							$divide_type = 1;
							$divide_id	 = $det['game_divide_id'];
							$divide		 = $det['game_divide'];
						}
						else{
							$divide_type = 0;
							$divide_id   = 0;
							$divide      = '';
							$rate        = 0;
						}

						$pay_cnt = floatval($det['pay_cnt']);
						$pay_amt = floatval($det['pay_amt']);
						$extra_game_id = $det['extra_game_id'];
						
						$hasExtraRatio = false;

						if($pay_cnt && $pay_amt && $divide_id && $divide){
							$arr_divide = json_decode($divide);
							foreach($arr_divide as $civ){
								if(count($civ)==3){
									//实际流水大于该阶段左侧流水值并且小于右侧流水值
								//	echo $civ[0]."---from---".$civ[1]."----to----<br>";
									if($pay_amt>=$civ[0] && $pay_amt<$civ[1]){
										$hasExtraRatio = true;
										$divide_amt = floatval(intval($pay_amt*$civ[2])/100);
										$rate = $civ[2];
										break;
									}
								}
								else{
									$template = '渠道游戏补点配置有误，游戏名：'.$det['game_name'].'，当前配置为：'.$divide.' 。时间：'.date('Y-m-d H:i:s');
									$ddurl = Env::get('operat_url');
									curlDD($template, $ddurl,true);
								}
							}
					//		echo $channel_id."---channel_id----".$hasExtraRatio."---hasExtraRatio----".$rate."---rate----".$divide_amt."-----divide_amt----<br>";
							if($hasExtraRatio){
								$arr[$key2][0]  = $channel_id;
								$arr[$key2][1]  = date('Y-m',$end_time);
								$arr[$key2][2]  = $extra_game_id;
								$arr[$key2][3]  = date('Y-m-d',$start_time);
								$arr[$key2][4]  = date('Y-m-d',$end_time);
								$arr[$key2][5]  = $pay_cnt;
								$arr[$key2][6]  = $pay_amt;
								$arr[$key2][7]  = $divide_type;
								$arr[$key2][8]  = $divide_id;
								$arr[$key2][9]  = $divide;
								$arr[$key2][10] = $rate;
								$arr[$key2][11] = $divide_amt;

								$channelInfo   = Db::table('nw_channel')->field("id,name,level,parent_id")->where(['id'=>$channel_id])->find();
								$channelInfoApply   = Db::table('nw_channel_info')->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'=>$channel_id,'apply_status'=>1])->find();
								if(empty($channelInfoApply)){
									$arr[$key2][12] = '';
									$arr[$key2][13] = '';
									$arr[$key2][14] = '';
									$arr[$key2][15] = '';
									$arr[$key2][16] = '';
									$arr[$key2][17] = '';
									$arr[$key2][18] = '';
									$arr[$key2][19] = '';
									$arr[$key2][20] = '';
									$template = '公会每月补点数据：该公会尚未身份认证，公会ID：'.$channel_id.'，公会名称：'.$channelInfo['name'].' 。时间：'.date('Y-m-d H:i:s');
									$ddurl = Env::get('operat_url');
									curlDD($template, $ddurl,true);
								}
								else{
									$arr[$key2][12] = $channelInfoApply['real_name'];
									$arr[$key2][13] = $channelInfoApply['person_id'];
									$arr[$key2][14] = $channelInfoApply['zfb_account'];
									$arr[$key2][15] = $channelInfoApply['bank_ban_mobile'];
									$arr[$key2][16] = $channelInfoApply['bank_number'];
									$arr[$key2][17] = $channelInfoApply['bank_province_city'];
									$arr[$key2][18] = $channelInfoApply['bank_name'];
									$arr[$key2][19] = $channelInfoApply['bank_open_name'];
									$arr[$key2][20] = $channelInfoApply['bank_open_number'];
								}
								$arr[$key2][21] = time();
								$arr[$key2][22] = time();
								$arr[$key2][23] = 1;
								$key2++;
							}
						}
					}
				}

				if($arr){
					$arr_key    = array('channel_id','month','extra_game_id','start_date','end_date','pay_cnt','pay_amt','divide_type','divide_id','divide','rate','divide_amt','real_name','person_id','zfb_account','bank_ban_mobile','bank_number','bank_province_city','bank_name','bank_open_name','bank_open_number','create_time','update_time','channel_type');
					$arr_update = array('start_date','end_date','pay_cnt','pay_amt','divide_type','divide_id','divide','rate','divide_amt','real_name','person_id','zfb_account','bank_ban_mobile','bank_number','bank_province_city','bank_name','bank_open_name','bank_open_number','update_time','channel_type');
						
					$execInfo = $this->multArray2InsertExec('nw_game_channel_extra_divide_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");
					}
				}
			}
		}
        
        if(!empty($TopChannelIds)){
            $arr = [];
            $key2 = 0;

            foreach($TopChannelIds as $key=>$value){
				$where = [];
				$where['p.status'] = 1;
				$channel_id = $value;
				$channelIds = get_child_channel_arr($channel_id);
				array_push($channelIds,$channel_id);
				$where['p.channel_id'] = ['in',$channelIds];
				$where['p.create_time'] = [['>=', $start_time],['<=', $end_time]];

				$GameChannelData = Db::table('cy_pay')->alias('p')
									->join('nw_extra_subgame sub', 'p.gameid = sub.game_id','inner')
									->join('nw_extra_game g', 'sub.extra_game_id = g.id','inner')
									->join('nw_game_extra_divide c', 'g.id = c.extra_game_id','left')
									->join('nw_game_channel_extra_divide d','g.id = d.extra_game_id and d.channel_id='.$channel_id,'left')
									->field("g.id AS extra_game_id,g.extra_game_name as game_name,c.id as game_divide_id,c.divide as game_divide,count(*) as pay_cnt,SUM(p.amount) AS pay_amt,d.id as channel_divide_id,d.divide as channel_divide")
									->where($where)
									->group('g.id')
									->select();

				$pay_amt = $divide_amt = $pay_cnt = $rate = 0;
				foreach ( $GameChannelData as &$det ) {
					if(intval($det['channel_divide_id'])){
						$divide_type = 2;
						$divide_id	 = $det['channel_divide_id'];
						$divide		 = $det['channel_divide'];
					}
					else if(intval($det['game_divide_id'])){
						$divide_type = 1;
						$divide_id	 = $det['game_divide_id'];
						$divide		 = $det['game_divide'];
					}
					else{
						$divide_type = 0;
						$divide_id   = 0;
						$divide      = '';
						$rate        = 0;
					}

					$pay_cnt = floatval($det['pay_cnt']);
					$pay_amt = floatval($det['pay_amt']);
					$extra_game_id = $det['extra_game_id'];
						
					$hasExtraRatio = false;

					if($pay_cnt && $pay_amt && $divide_id && $divide){
						$arr_divide = json_decode($divide);
						foreach($arr_divide as $civ){
							if(count($civ)==3){
								//实际流水大于该阶段左侧流水值并且小于右侧流水值
								if($pay_amt>=$civ[0] && $pay_amt<$civ[1]){
									$hasExtraRatio = true;
									$divide_amt = floatval(intval($pay_amt*$civ[2])/100);
									$rate = $civ[2];
									break;
								}
							}
							else{
								$template = '游戏补点配置有误，游戏名：'.$det['game_name'].'，当前配置为：'.$divide.' 。时间：'.date('Y-m-d H:i:s');
								$ddurl = Env::get('operat_url');
								curlDD($template, $ddurl,true);
							}
						}
				//		echo $channel_id."---channel_id----".$hasExtraRatio."---hasExtraRatio----".$rate."---rate----".$divide_amt."-----divide_amt----<br>";
						if($hasExtraRatio){
							$arr[$key2][0]  = $channel_id;
							$arr[$key2][1]  = date('Y-m',$end_time);
							$arr[$key2][2]  = $extra_game_id;
							$arr[$key2][3]  = date('Y-m-d',$start_time);
							$arr[$key2][4]  = date('Y-m-d',$end_time);
							$arr[$key2][5]  = $pay_cnt;
							$arr[$key2][6]  = $pay_amt;
							$arr[$key2][7]  = $divide_type;
							$arr[$key2][8]  = $divide_id;
							$arr[$key2][9]  = $divide;
							$arr[$key2][10] = $rate;
							$arr[$key2][11] = $divide_amt;

							$channelInfo   = Db::table('nw_channel')->field("id,name,level,parent_id")->where(['id'=>$channel_id])->find();
							$channelInfoApply   =Db::table('nw_channel_info')->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'=>$channel_id,'apply_status'=>1])->find();
							if(empty($channelInfoApply)){
								$arr[$key2][12] = '';
								$arr[$key2][13] = '';
								$arr[$key2][14] = '';
								$arr[$key2][15] = '';
								$arr[$key2][16] = '';
								$arr[$key2][17] = '';
								$arr[$key2][18] = '';
								$arr[$key2][19] = '';
								$arr[$key2][20] = '';
								$template = '外部商务每月补点数据：该商务尚未身份认证，商务ID：'.$channel_id.'，商务名称：'.$channelInfo['name'].' 。时间：'.date('Y-m-d H:i:s');
								$ddurl = Env::get('operat_url');
								curlDD($template, $ddurl,true);
							}
							else{
								$arr[$key2][12] = $channelInfoApply['real_name'];
								$arr[$key2][13] = $channelInfoApply['person_id'];
								$arr[$key2][14] = $channelInfoApply['zfb_account'];
								$arr[$key2][15] = $channelInfoApply['bank_ban_mobile'];
								$arr[$key2][16] = $channelInfoApply['bank_number'];
								$arr[$key2][17] = $channelInfoApply['bank_province_city'];
								$arr[$key2][18] = $channelInfoApply['bank_name'];
								$arr[$key2][19] = $channelInfoApply['bank_open_name'];
								$arr[$key2][20] = $channelInfoApply['bank_open_number'];
							}
							$arr[$key2][21] = time();
							$arr[$key2][22] = time();
							$arr[$key2][23] = 2;
							$key2++;
						}
					}
				}


				if($arr){
					$arr_key    = array('channel_id','month','extra_game_id','start_date','end_date','pay_cnt','pay_amt','divide_type','divide_id','divide','rate','divide_amt','real_name','person_id','zfb_account','bank_ban_mobile','bank_number','bank_province_city','bank_name','bank_open_name','bank_open_number','create_time','update_time','channel_type');
					$arr_update = array('start_date','end_date','pay_cnt','pay_amt','divide_type','divide_id','divide','rate','divide_amt','real_name','person_id','zfb_account','bank_ban_mobile','bank_number','bank_province_city','bank_name','bank_open_name','bank_open_number','update_time','channel_type');
						
					$execInfo = $this->multArray2InsertExec('nw_game_channel_extra_divide_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')." ChannelExtraPointMonth 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'];
        }
    }
}