new_sdk_wu/www/new_sdk/application/crontab/BusinessSettleFlow.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;

class BusinessSettleFlow extends Command {
    
    protected $_output;

    protected function configure() {
        
        $this->addArgument('execMonth', Argument::OPTIONAL);          //指定统计的日期时,格式Y-m
        
        $this->setName('BusinessSettleFlow')->setDescription('商务结算流水统计脚本');
    }

    protected function execute(Input $input, Output $output) {
        
        ini_set('memory_limit', '1024M');
        
        $now_time   = time();                               //当前时间
        $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')." BusinessSettleFlow start\r\n");
        
        //上一个自然月分成合同
        $contract_list = Db::table('nw_point_contract')->field('id,gameid,start_time,end_time')->where(['delete_time'=>0,'start_time'=>['<=',$end_time],'end_time'=>['>',$start_time]])->select();
        
        $output->writeln("contract_list sql: ".Db::table('nw_point_contract')->getLastSql()."\r\n");
        
        if(!empty($contract_list)){
            
            $arr = [];
            
            foreach($contract_list as $key=>$value){
                
                //总流水
                $total_amount = 0;
                
                //结算月份的开始时间
                $settle_start_time = ($value['start_time']>=$start_time ? $value['start_time'] : $start_time);
                //结算月份的结束时间
                $settle_end_time   = ($value['end_time']>=$end_time ? $end_time : $value['end_time']);
                
                $pay_info = Db::table('cy_pay force index(idx_pay_time)')->field('sum(amount) as amount')
                                    ->where(['status'=>1,'pay_time'=>[['>=', $settle_start_time],['<=', $settle_end_time]],'gameid'=>$value['gameid']])->find();
                
                                    
                $complex_info = Db::table('nw_complex_pay force index(idx_pay_time)')->field('sum(amount) as amount')
                                    ->where(['status'=>1,'pay_time'=>[['>=', $settle_start_time],['<=', $settle_end_time]],'gameid'=>$value['gameid']])->find();
                
                
                if(!empty($pay_info))
                    $total_amount = $total_amount+$pay_info['amount'];
                
                if(!empty($pay_info))
                    $total_amount = $total_amount+$complex_info['amount'];
                
                    
                $arr[$key][0] = $value['gameid'];
                $arr[$key][1] = $value['id'];
                $arr[$key][2] = $total_amount;
                $arr[$key][3] = $settle_start_time;
                $arr[$key][4] = $settle_end_time;
                $arr[$key][5] = $now_time;
                $arr[$key][6] = $now_time;
                
                /*
                Db::table('nw_business_settle')->insert(['gameid'           =>$value['gameid'],
                                                        'point_contract_id' =>$value['id'],
                                                        'pay_amount'        =>$total_amount,
                                                        'settle_start_time' =>($value['start_time']>=$start_time ? $value['start_time'] : $start_time),
                                                        'settle_end_time'   =>($value['end_time']>=$end_time ? $end_time : $value['end_time']),
                                                        'create_time'       =>$now_time,
                                                        'update_time'       =>$now_time
                ]);*/
            }
            
            $arr_key    = array('gameid','point_contract_id','pay_amount','settle_start_time','settle_end_time','create_time','update_time');
            $arr_update = array('pay_amount','update_time');
            
            $execInfo = $this->multArray2InsertExec('nw_business_settle',$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')." BusinessSettleFlow 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'];
        }
    }
}