php_script/excel_third_...

<?php

require_once 'DB_PDO.class.php';
require_once 'TBK.class.php';
require_once 'confv2.class.php';
require_once 'PHPExcel-1.8/Classes/PHPExcel.php';
require_once 'PHPExcel-1.8/Classes/PHPExcel/IOFactory.php';
require_once 'PHPExcel-1.8/Classes/PHPExcel/Reader/Excel5.php';

# 定义下载订单时间段
date_default_timezone_set('PRC');
define("END_TIME", date('Y-m-d%20H:i:s', strtotime('2019-02-27 00:00:00')) );  
define("START_TIME", date('Y-m-d%20H:i:s', strtotime('2019-02-15 00:00:00')) );
define("DETAILDEBUG", 0);//打印流程详细信息，如有需要，设为1
define("LIMIT_NUM", 0);//限制导入条数,0不限制,方便测试,上线设为0
//define("PUSH_URL",'http://yhq.quyaqu.com/api/v2/MessagePush/rebatepush');//push接口地址,测试
define("PUSH_URL",'https://tbk.726p.com/api/v2/MessagePush/rebatepush');//push接口地址
/**
 * 导入及更新 order/order_rebate数据
 */
#设置时区
set_time_limit(0);
ini_set('memory_limit','1024M');
error_reporting(E_ALL);

function readDataFromDb(){
    # 1.下载excel
    $res = down_excel();    
    if( !$res ){
        echo '下载excel文件失败';
        exit;
    }

    # 遍历订单
    # 统计
    $res_count = array(
        'total_num' => 0,
        'generalinsert_num' => 0,
        'generalupdate_num' => 0,
        'nouserinsert_num' => 0,
        'nouserupdate_num' => 0,
        'fail_insert_num'  => 0,
        'fail_insert_ids'  => '',
        'fail_insertno_num'  => 0,
        'fail_insertno_ids'  => '',
        'fail_update_num'  => 0,
        //'fail_update_ids'  => '',
        'fail_updateno_num'  => 0,
        //'fail_updateno_ids'  => '',
    );
    #初始化pdo
    $_PDO=DB_PDO::getInstance( conf::$DB_CONF );
    # 读取excel
    $templateName = $res;
    //实例化Excel读取类
    $objReader = new PHPExcel_Reader_Excel2007();
    if(!$objReader->canRead($templateName)){
        $objReader = new PHPExcel_Reader_Excel5();
        if(!$objReader->canRead($templateName)){
            echo "\n".'无法识别的Excel文件！';
            return false;
        }
    }
    $objPHPExcel=$objReader->load($templateName);
    $sheet=$objPHPExcel->getSheet(0);//获取第一个工作表
    $highestRow=$sheet->getHighestRow();//取得总行数
    $highestColumn=$sheet->getHighestColumn(); //取得总列数
    if( strlen($highestColumn) == 2 ){
        $highestColumn = 'Z';
    }
    $orderList = array();
    //循环读取excel文件,读取一条,插入一条
    for($j=2;$j<=$highestRow;$j++){//从第二行开始读取数据
        $str='';
        for($k='A';$k<=$highestColumn;$k++){            //从A列读取数据
            //这种方法简单，但有不妥，以'\\'合并为数组，再分割\\为字段值插入到数据库,实测在excel中，如果某单元格的值包含了\\导入的数据会为空        
            $str.=$objPHPExcel->getActiveSheet()->getCell("$k$j")->getValue().'\\';//读取单元格
            if( $k=='AE' ){ 
                break;
            }
        }
        //explode:函数把字符串分割为数组。
        $orderDetail = explode('\\',trim($str,'\\') );  
	//if( substr($orderDetail[29], 0, 4) != 'api_') continue;

        $res_count['total_num']++;
        echo "/ ************************ order{$res_count['total_num']}-Start ************************* / \n";
        if( DETAILDEBUG == 1){
            echo 'orderDetail:';
            var_dump($orderDetail);
        }

        //0创建时间 1点击时间   2商品信息   3商品ID   4掌柜旺旺   5所属店铺   6商品数    7商品单价   8订单状态   9订单类型   10收入比率  11分成比率  12付款金额  13效果预估  14结算金额  15预估收入  16结算时间  17佣金比率  18佣金金额  19补贴比率  20补贴金额  21补贴类型  22成交平台  23产品类型  24订单编号  25类目名称  26来源媒体ID    27来源媒体名称    28广告位ID 29广告位名称
	
        $order_id=$orderDetail[24];
        $update_time=date('Y-m-d H:i:s');
        $order_create_at=$orderDetail[0];
        $order_click_at=$orderDetail[1];
        $order_balance_at=$orderDetail[16];
        $goods_id=$orderDetail[3];
        $goods_name=$orderDetail[2];
        $goods_name=str_replace(['"',"'"],'',$goods_name);
        $num=$orderDetail[6];
        $price=intval($orderDetail[7]*100);
        $sum_price=intval($orderDetail[12]*100);

        $statusDesc=$orderDetail[8];
        $typeDesc=$orderDetail[9];
        $orderStatus = 1;
        $type=1;
        if($statusDesc=="订单付款"){
            $orderStatus=1;
        }
        elseif($statusDesc=="订单失效"){
            $orderStatus=0;
        }
        elseif($statusDesc=="订单结算"){
            $orderStatus=2;
        }
        if($typeDesc=="聚划算"){
            $type=0;
        }
        elseif($typeDesc=="淘宝"){
            $type=1;
        }
        elseif($typeDesc=="天猫"){
            $type=2;
        }

        $adzone_id=$orderDetail[28];
        $ceil_income=round($orderDetail[13], 2);
        $predict_income=$ceil_income*100;
        $predict_income=ceil($predict_income*conf::$RATE_CONF["globalzk"]);

        echo 'order_id:'.$order_id.'|| goods_id:'.$goods_id.'|| orderstatus:'.$orderStatus.'|| order_balance_at:'.$order_balance_at.'|| update_time:'.$update_time;
        echo '|| predict_income:'.$predict_income.'|| adzone_id:'.$adzone_id."\n";

        $resultone=UserInfo($adzone_id);

        if( DETAILDEBUG == 1){
            echo 'userinfo:';
            var_dump($resultone);
            echo "\n";
        }
        if($sum_price!=0){
            if($predict_income<5){
                $predict_income=5;
            }
        }
        if( !empty($resultone) ){
			echo "User:存在\n";
            $orderResult=orderMysql($order_id,$goods_id);

            if( empty($orderResult) || in_array($order_id,$orderList)){
				echo "action:插入操作\n";
		#获取层级规则信息
                $userAllInfo=userLevelandPrentInfo($resultone);
                echo "<pre>\nuserAllInfo";
                print_r($userAllInfo);
                                if( DETAILDEBUG == 1){
                                        echo 'userallinfo:'."\n";
                                        var_dump($userAllInfo);
                                }

                $userList = $userAllInfo['userList'];   //4层 每层userId
                $userLevel = $userAllInfo['userLevel']; //4层 每层userlevel

                # 从数据库获取规则
                $rebateRuleInfo = getRebateRule($userAllInfo['levelRule']);
                echo "\nRuleInfo:";
                print_r($rebateRuleInfo);
                $RuleArr = explode(',',$rebateRuleInfo['rebate_rule']);   //4层 每层分佣比例
                $TypeArr = explode(',',$rebateRuleInfo['rebate_type']);   //4层 每层分佣级别

                #获取每一层返利
                $moneyInfo=orderMoney($predict_income, $RuleArr);  //4层 每层分佣钱数
                echo "\nmoneyInfo:";
                print_r($moneyInfo);

				if( DETAILDEBUG == 1){
					echo 'rebateInfo'."\n";
					var_dump($moneyInfo);
				}
            	$detail = TBK::tbkItemInfoGet( ['goods_id'=>$goods_id] );                
                if( !empty($detail) ){
                	$goods_img=$detail->pict_url;
                }
                else{
                    $goods_img="";
                }
                try{
                	$_PDO->beginTransaction();               	
                    #订单表插入
                    $res=generateSqlInsertOrder($goods_img,$goods_name,$userList[0],$order_id,$adzone_id,$goods_id,$num,$price,$sum_price,$predict_income,$moneyInfo[0],$orderStatus,$type,$userLevel[0],$order_create_at,$order_click_at,$order_balance_at);

                                        if($res){
                                                $orderList[] = $order_id;
                                                $res_count['generalinsert_num']++;
                                        }else{
                                                $res_count['fail_insert_num']++;
                                                $res_count['fail_insert_ids'].=$order_id.'|';
                                        }
                                        echo 'orderinsert:'.$res;
                                        echo "\n";

                    if($sum_price>0 and $orderStatus!=0){
                        foreach($moneyInfo as $k=>$v){
                            if($v>0){
                                #order_rebate表插入父级佣金
                                $rebaseType=$TypeArr[$k];
                                $res=generateSqlInsertRebateSelf($userLevel[$k],$order_balance_at,$order_create_at,$goods_img,$goods_name,$order_id,$userList[$k],$rebaseType,$moneyInfo[$k],$sum_price,$orderStatus,$goods_id,$predict_income);
                                                        # 推送
                                if($res){
                                    $params = array();
                                    $params['user_id'] = $userList[$k];
                                    $params['type'] = 2;
                                    $params['money'] = round($moneyInfo[$k]/100,2);
                                    //$push_res = curl_post( PUSH_URL, $params);
                                }
                                echo 'rebateinsert_'.$k.':'.$res;
                                                        echo "\n";
                            }
                        }

                    }
			
		    //津贴单独计算
                            # 获取顶级运营商-控制中心
                                        if($sum_price>0 and $orderStatus !=0){
                                                $YysManageInfo = YysManageInfo( $userList[0] );
                                                if( !empty($YysManageInfo) ){
                                                        $rebaseType=4;
                                                        $YysManageRabate = round($predict_income*conf::$RATE_CONF["YysManage"]);
                                                        if($YysManageRabate < 1){
                                                                $YysManageRabate = 1;
                                                        }
                                                        $res = YysManageInsertRebate(4,$order_balance_at,$order_create_at,$goods_img,$goods_name,$order_id,$YysManageInfo['user_id'],$rebaseType,$YysManageRabate,$sum_price,$orderStatus,$goods_id,$predict_income);
                                                        echo 'rebateinsert_YysManage:'.$res;
                                                        echo "\n";
                                                }
                                        }
 
                    $_PDO->commit();
                }catch(PDOException $e){
		    echo 'err_msg'.$e->getMessage()."\n";
                    $_PDO->rollback();

                }
				//echo "-----------------insertEnd--------------------\n";
            }
            else{
                try{
                	$_PDO->beginTransaction();
                	//更新order/order_rebate表
					echo "action:更新操作\n";
                    $res=generateSqlupdate($orderStatus,$order_balance_at,$order_id,$goods_id);   
                    $redOrderUpRes=redSqlupdate($orderStatus,$order_balance_at,$order_id,$goods_id);
                    $redUpRes = updateRed( $order_id, $orderStatus );

                    /*** 失效订单单独更新 Start ***/
                    if($orderStatus == 0){
                    //首先判断该订单是否已经失效
                    //$order_old_status = $redOrderExists['status'];
                    if($redOrderUpRes){ 
                        if(!empty($redUpRes) && $redUpRes['red_open_money'] > 0){   //存在红包并且有拆除金额           
                        //1. 退红包的处理           
                        //更新流水表
                        //获取用户最新balance
                        echo "红包订单失效\n";
                        echo "失效红包：".$redUpRes['id']."\n";
                        $userAccount = getUserBalace($resultone['user_id']);
                        $upAccount = redAccountRecord( $resultone['user_id'], 3, $redUpRes['red_open_money'], $redUpRes['id'], round(($userAccount['balance']*100 - $redUpRes['red_open_money']*100)/100, 2));
                        echo "更新流水：".$upAccount."\n";
                        }

                        //判断是否该订单存在红包提现分单
                        $redAccountInfo = insertLoseAccount($order_id);                            

                    }
                    }

					if($res){
                        $res_count['generalupdate_num']++;
                    }else{
                        $res_count['fail_update_num']++;
                        //$res_count['fail_update_ids'].=$order_id.'|';
                    }
					echo "更新数据-> orderstatus:".$orderStatus." order_balance_at:".$order_balance_at;
					echo "\n";
					echo "order/reabte_update:".$res;
					echo "\n";
					//echo "-----------------updateEnd-----------------------\n";
                    $_PDO->commit();
                }catch(PDOException $e){
                    $_PDO->rollback();
			echo 'err_msg'.$e->getMessage()."\n";
                }
        	}
			//echo "-----------------IssetUserEnd:--------------------\n";
        }            
        else{
        	$_PDO->beginTransaction();
        	try{
	        	#user不存在的处理
				echo "User:不存在\n";
	            $orderResult=orderMysql($order_id,$goods_id);            	            
            
	            if( empty($orderResult) ){
	                $detail = TBK::tbkItemInfoGet( ['goods_id'=>$goods_id] );                
	                if( !empty($detail) ){
	                	$goods_img=$detail->pict_url;
	                }
	                else{
	                    $goods_img="";
	                }
			$res=generateSqlInsertOrderOther($goods_img,$goods_name,$order_id,$adzone_id,$goods_id,$num,$price,$sum_price,$predict_income,0,$orderStatus,$type,$order_create_at,$order_click_at,$order_balance_at);
					if($res){
                        $res_count['nouserinsert_num']++;
                    }else{
                        $res_count['fail_insertno_num']++;
                        $res_count['fail_insertno_ids'].=$order_id.'|';
                    }
					echo "action:插入操作\n";
					echo "orderinsert:".$res;
					echo "\n";
					
	                $_PDO->commit();
	            }
	            else{
	                $res=generateSqlUpdateOther($orderStatus,$order_balance_at,$order_id,$goods_id);                      
					if($res){
                        $res_count['nouserupdate_num']++;
                    }else{
                        $res_count['fail_updateno_num']++;
                        //$res_count['fail_updateno_ids'].=$order_id.'|';
                    }
					echo "action:更新操作\n";
					echo "orderupdate:".$res;
					echo "\n";
	                $_PDO->commit();
				}
				//echo "-----------------EmptyUserEnd:--------------------\n";
				
			}catch(PDOException $e){
                $_PDO->rollback();
		echo 'err_msg'.$e->getMessage()."\n";
            }
        }
		echo "/ ** order{$res_count['total_num']}-End ** /";
		echo "\n\n\n";
		if(LIMIT_NUM >0 && $res_count['total_num'] == LIMIT_NUM) break;
	}
	print_r($res_count);
}	

function YysManageInfo( $user_id ){
        $sql = "SELECT user_id,parent_user_id,level FROM user_level WHERE user_id=:user_id ";
        $_PDO=DB_PDO::getInstance( conf::$DB_CONF );
        $stmt = $_PDO->prepare($sql);
        $stmt->execute(array(':user_id'=>$user_id));
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        if( empty($result) ) return false;
        if($result['level'] == 4){
                return $result;
        }else{
            if( empty($result['parent_user_id']) ){
                    return false;
            }
            return YysManageInfo( $result['parent_user_id']);
        }
        return false;
}

function YysInfo( $user_id ){
        $sql = "SELECT user_id,parent_user_id,level FROM user_level WHERE user_id=:user_id ";
        $_PDO=DB_PDO::getInstance( conf::$DB_CONF );
        $stmt = $_PDO->prepare($sql);
        $stmt->execute(array(':user_id'=>$user_id));
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        if( empty($result) ) return ['user_id'=>438999,'level'=>4,'parent_user_id'=>'-1'];
        if($result['level'] >= 3){
                return $result;
        }else{
                if( empty($result['parent_user_id']) ){
                        return ['user_id'=>438999,'level'=>4,'parent_user_id'=>'-1'];
                }
                return YysInfo( $result['parent_user_id']);
        }
        return ['user_id'=>438999,'level'=>4,'parent_user_id'=>'-1'];
}

function UserInfo($adzone_id){
    $sql = "SELECT * FROM user_adzone WHERE adzone_id = :adzone_id ";
    $_PDO=DB_PDO::getInstance( conf::$DB_CONF );
    $stmt = $_PDO->prepare($sql);  
	$stmt->execute(array(':adzone_id'=>$adzone_id));  	 
	$resultone = $stmt->fetch(PDO::FETCH_ASSOC);
    if($resultone){
        $sql="SELECT * FROM user_level WHERE user_id = :user_id";
        $stmt = $_PDO->prepare($sql);  
		$stmt->execute(array(':user_id'=>$resultone['user_id']));  	 
		$userResult = $stmt->fetch(PDO::FETCH_ASSOC);
        return $userResult;
    }
    else{
        return false;   
    }
}

function userLevelInfo($user_id){	
    $sql="SELECT * FROM user_level WHERE user_id = :user_id ";
    $_PDO=DB_PDO::getInstance( conf::$DB_CONF );
    $stmt = $_PDO->prepare($sql);  
	$stmt->execute(array(':user_id'=>$user_id));  	 
	$resultone = $stmt->fetch(PDO::FETCH_ASSOC);
    if($resultone){
        $myInfo=["user_id"=>$user_id,"level"=>$resultone['level'],"parent_user_id"=>$resultone['parent_user_id']];
        return $myInfo;
    }
    else{
        return false;
    }
}

function userMyInfo($user_id){   
    $sql="SELECT * FROM users WHERE id = :user_id "; 
    $_PDO=DB_PDO::getInstance( conf::$DB_CONF );
    $stmt = $_PDO->prepare($sql);  
	$stmt->execute(array(':user_id'=>$user_id));  	 
	$resultone = $stmt->fetch(PDO::FETCH_ASSOC);
    if($resultone){
        $myInfo['user_id'] = $user_id;
        $myInfo['origin'] = $resultone['origin']; 
        return $myInfo;
    }    
    else{
        return false;
    }
}
        
# 获取4层用户级别基础信息
function userLevelandPrentInfo($resultone){
    $resultInfo = array();

    # self
        $userId=$resultone['user_id'];
    $userLevel=$resultone['level'];
    if($userLevel>=3){
        $resultInfo['userList'] = [$userId,0,0,0];
        $resultInfo['levelRule'] = 3000;
        $resultInfo['userLevel'] = [$userLevel,0,0,0];
        return $resultInfo;
    }

    $userParentId=$resultone['parent_user_id'];
    $userGrandId=$resultone['grand_father_id'];

    if($userParentId>0){
        $resulttwo=userLevelInfo($userParentId);

        if( !empty($resulttwo) ){
            # parent
            $userParentLevel=$resulttwo['level'];
            if($userParentLevel>=3){
                $resultInfo['userList'] = [$userId,$userParentId,0,0];
                $resultInfo['levelRule'] = intval($userLevel.'300');
                $resultInfo['userLevel'] = [$userLevel,$userParentLevel,0,0];
                return $resultInfo;
            }
            if($userGrandId>0){
                $resultthree=userLevelInfo($userGrandId);

                if( !empty($resultthree) ){
                    # grand 
                    $userGrandLevel=$resultthree['level'];
                    if($userGrandLevel>=3){
                        $resultInfo['userList'] = [$userId,$userParentId,$userGrandId,0];
                        $resultInfo['levelRule'] = intval($userLevel.$userParentLevel.'30');
                        $resultInfo['userLevel'] = [$userLevel,$userParentLevel,$userGrandLevel,0];
                        return $resultInfo;
                    }
                }
            }
		}	
	}
       	if( !$userParentId )    $userParentId=0;
        if( !$userGrandId )     $userGrandId=0;
        if( !isset($userParentLevel) )  $userParentLevel=1;
        if( !isset($userGrandLevel) )   $userGrandLevel=1;

    #三级里面没有运营商，递归上级运营商
    $YysInfo = YysInfo($userGrandId);
    $userFourId = $YysInfo['user_id'];
    $userFourLevel = $YysInfo['level'];

    # 层级规则
    $levelRule = $userLevel.$userParentLevel.$userGrandLevel.'3';

    $resultInfo['userList']=[$userId,$userParentId,$userGrandId,$userFourId];
    $resultInfo['levelRule'] = (int)$levelRule;
    $resultInfo['userLevel'] = [$userLevel,$userParentLevel,$userGrandLevel,$userFourLevel];
    return $resultInfo;
}


function orderMoney($predict_income, $RuleArr){
    $result = array();
    foreach($RuleArr as $k=>$v){
        if($v>0){
            $result[$k] = round($predict_income * $v / 100);
        }else{
            $result[$k] = 0;
        }
    }
    return $result;
}


function orderMysql($order_id,$goods_id){    
    $sql="SELECT * from `order` where order_id=:order_id and goods_id=:goods_id";
    $_PDO=DB_PDO::getInstance( conf::$DB_CONF );
    $stmt = $_PDO->prepare($sql);  
	$stmt->execute(array(':order_id'=>$order_id,':goods_id'=>$goods_id));  	 
	$orderResult = $stmt->fetch(PDO::FETCH_ASSOC);
    return $orderResult;
}

function orderType($adzone_id){   
    $sql="SELECT * from `adzone` where adzone_id=:adzone_id";
    $_PDO=DB_PDO::getInstance( conf::$DB_CONF );
    $stmt = $_PDO->prepare($sql);  
	$stmt->execute(array(':adzone_id'=>$adzone_id));  	 
	$userResult = $stmt->fetch(PDO::FETCH_ASSOC);
    return $userResult;
}

function generateSqlInsertOrderOther($goods_img,$goods_name,$order_id,$adzone_id,$goods_id,$num,$price,$sum_price,$predict_income,$self_income,$orderStatus,$type,$order_create_at,$order_click_at,$order_balance_at){
   	$_PDO=DB_PDO::getInstance( conf::$DB_CONF );
    if( empty($order_balance_at) ){
        $sql="insert into `order` (company_type,goods_img_url,goods_name,order_id,adzone_id,goods_id,num,price,sum_price,predict_income,income,status,type,order_create_at,order_click_at) VALUES (:company_type,:goods_img,:goods_name,:order_id,:adzone_id,:goods_id,:num,:price,:sum_price,:predict_income,:self_income,:orderStatus,:type,:order_create_at,:order_click_at)";
        
        $stmt = $_PDO->prepare($sql);  
		$stmt->execute(array(':company_type'=>2,':goods_img'=>$goods_img,':goods_name'=>$goods_name,':order_id'=>$order_id,':adzone_id'=>$adzone_id,':goods_id'=>$goods_id,':num'=>$num,':price'=>$price,':sum_price'=>$sum_price,':predict_income'=>$predict_income,':self_income'=>$self_income,':orderStatus'=>$orderStatus,':type'=>$type,':order_create_at'=>$order_create_at,':order_click_at'=>$order_click_at));
		
		return $_PDO->lastinsertid();
    }
    else{
        $sql="insert into `order` (company_type,goods_img_url,goods_name,order_id,adzone_id,goods_id,num,price,sum_price,predict_income,income,status,type,order_create_at,order_click_at,order_balance_at) VALUES (:company_type,:goods_img,:goods_name,:order_id,:adzone_id,:goods_id,:num,:price,:sum_price,:predict_income,:self_income,:orderStatus,:type,:order_create_at,:order_click_at,:order_balance_at)";
        
        $stmt = $_PDO->prepare($sql);  
		$stmt->execute(array(':company_type'=>2,':goods_img'=>$goods_img,':goods_name'=>$goods_name,':order_id'=>$order_id,':adzone_id'=>$adzone_id,':goods_id'=>$goods_id,':num'=>$num,':price'=>$price,':sum_price'=>$sum_price,':predict_income'=>$predict_income,':self_income'=>$self_income,':orderStatus'=>$orderStatus,':type'=>$type,':order_create_at'=>$order_create_at,':order_click_at'=>$order_click_at,':order_balance_at'=>$order_balance_at));
		
		return $_PDO->lastinsertid();
    }  
}

#order表订单插入脚本
function generateSqlInsertOrder($goods_img,$goods_name,$userId,$order_id,$adzone_id,$goods_id,$num,$price,$sum_price,$predict_income,$self_income,$orderStatus,$type,$userLevel,$order_create_at,$order_click_at,$order_balance_at){
    $adzoneDetail=orderType($adzone_id);
    $_PDO=DB_PDO::getInstance( conf::$DB_CONF );
    if( empty($order_balance_at) ){
        $sql="insert into `order` (company_type,goods_img_url,goods_name,user_id,order_id,adzone_id,goods_id,num,price,sum_price,predict_income,income,status,type,user_level,order_create_at,order_click_at) VALUES (:company_type,:goods_img,:goods_name,:userId,:order_id,:adzone_id,:goods_id,:num,:price,:sum_price,:predict_income,:self_income,:orderStatus,:type,:userLevel,:order_create_at,:order_click_at)";
        
        $stmt = $_PDO->prepare($sql);  
		$stmt->execute(array(':company_type'=>$adzoneDetail["type"],':goods_img'=>$goods_img,':goods_name'=>$goods_name,':userId'=>$userId,':order_id'=>$order_id,':adzone_id'=>$adzone_id,':goods_id'=>$goods_id,':num'=>$num,':price'=>$price,':sum_price'=>$sum_price,':predict_income'=>$predict_income,':self_income'=>$self_income,':orderStatus'=>$orderStatus,':type'=>$type,':userLevel'=>$userLevel,':order_create_at'=>$order_create_at,':order_click_at'=>$order_click_at));
		
		return $_PDO->lastinsertid();
    }
    else{
        $sql="insert into `order` (company_type,goods_img_url,goods_name,user_id,order_id,adzone_id,goods_id,num,price,sum_price,predict_income,income,status,type,user_level,order_create_at,order_click_at,order_balance_at) VALUES (:company_type,:goods_img,:goods_name,:userId,:order_id,:adzone_id,:goods_id,:num,:price,:sum_price,:predict_income,:self_income,:orderStatus,:type,:userLevel,:order_create_at,:order_click_at,:order_balance_at)";
        $stmt = $_PDO->prepare($sql);  
		$stmt->execute(array(':company_type'=>$adzoneDetail["type"],':goods_img'=>$goods_img,':goods_name'=>$goods_name,':userId'=>$userId,':order_id'=>$order_id,':adzone_id'=>$adzone_id,':goods_id'=>$goods_id,':num'=>$num,':price'=>$price,':sum_price'=>$sum_price,':predict_income'=>$predict_income,':self_income'=>$self_income,':orderStatus'=>$orderStatus,':type'=>$type,':userLevel'=>$userLevel,':order_create_at'=>$order_create_at,':order_click_at'=>$order_click_at,':order_balance_at'=>$order_balance_at));
		return $_PDO->lastinsertid();
    }
}    

#返利表订单插入脚本
function generateSqlInsertRebateSelf($user_level,$order_balance_at,$order_create_at,$goods_img,$goods_name,$order_id,$userId,$rebaseType,$last_orderSelfRebaseRebase,$sum_price,$orderStatus,$goods_id,$pre_income){
    $_PDO=DB_PDO::getInstance( conf::$DB_CONF );
    if( !empty($order_balance_at) ){
        $sql="insert into order_rebate (user_level,order_balance_at,order_create_at,img,name,order_id,user_id,type,rebate,money,status,goods_id,pre_income) VALUES (:user_level,:order_balance_at,:order_create_at,:goods_img,:goods_name,:order_id,:userId,:rebaseType,:last_orderSelfRebaseRebase,:sum_price,:orderStatus,:goods_id,:pre_income)";
        $stmt = $_PDO->prepare($sql);
                $stmt->execute(array(':user_level'=>$user_level,':order_balance_at'=>$order_balance_at,':order_create_at'=>$order_create_at,':goods_img'=>$goods_img,':goods_name'=>$goods_name,':order_id'=>$order_id,':userId'=>$userId,':rebaseType'=>$rebaseType,':last_orderSelfRebaseRebase'=>$last_orderSelfRebaseRebase,':sum_price'=>$sum_price,':orderStatus'=>$orderStatus,':goods_id'=>$goods_id,':pre_income'=>$pre_income));
                return $_PDO->lastinsertid();
    }
    else{
        $sql="insert into order_rebate (user_level,order_create_at,img,name,order_id,user_id,type,rebate,money,status,goods_id,pre_income) VALUES (:user_level,:order_create_at,:goods_img,:goods_name,:order_id,:userId,:rebaseType,:last_orderSelfRebaseRebase,:sum_price,:orderStatus,:goods_id,:pre_income)";
        $stmt = $_PDO->prepare($sql);
                $stmt->execute(array(':user_level'=>$user_level,':order_create_at'=>$order_create_at,':goods_img'=>$goods_img,':goods_name'=>$goods_name,':order_id'=>$order_id,':userId'=>$userId,':rebaseType'=>$rebaseType,':last_orderSelfRebaseRebase'=>$last_orderSelfRebaseRebase,':sum_price'=>$sum_price,':orderStatus'=>$orderStatus,':goods_id'=>$goods_id,':pre_income'=>$pre_income));
                return $_PDO->lastinsertid();
    }
}

#单独处理订单插运营商佣金
function YysInsertRebate($user_level,$order_balance_at,$order_create_at,$goods_img,$goods_name,$order_id,$userId,$rebaseType,$last_orderSelfRebaseRebase,$sum_price,$orderStatus,$goods_id,$pre_income){
    $_PDO=DB_PDO::getInstance( conf::$DB_CONF );
    if( !empty($order_balance_at) ){
        $sql="insert into order_rebate (user_level,order_balance_at,order_create_at,img,name,order_id,user_id,type,rebate,money,status,goods_id,pre_income) VALUES (:user_level,:order_balance_at,:order_create_at,:goods_img,:goods_name,:order_id,:userId,:rebaseType,:last_orderSelfRebaseRebase,:sum_price,:orderStatus,:goods_id,:pre_income)";
        $stmt = $_PDO->prepare($sql);
                $stmt->execute(array(':user_level'=>$user_level,':order_balance_at'=>$order_balance_at,':order_create_at'=>$order_create_at,':goods_img'=>$goods_img,':goods_name'=>$goods_name,':order_id'=>$order_id,':userId'=>$userId,':rebaseType'=>$rebaseType,':last_orderSelfRebaseRebase'=>$last_orderSelfRebaseRebase,':sum_price'=>$sum_price,':orderStatus'=>$orderStatus,':goods_id'=>$goods_id,':pre_income'=>$pre_income));
                return $_PDO->lastinsertid();
    }
    else{
        $sql="insert into order_rebate (user_level,order_create_at,img,name,order_id,user_id,type,rebate,money,status,goods_id,pre_income) VALUES (:user_level,:order_create_at,:goods_img,:goods_name,:order_id,:userId,:rebaseType,:last_orderSelfRebaseRebase,:sum_price,:orderStatus,:goods_id,:pre_income)";
        $stmt = $_PDO->prepare($sql);
                $stmt->execute(array(':user_level'=>$user_level,':order_create_at'=>$order_create_at,':goods_img'=>$goods_img,':goods_name'=>$goods_name,':order_id'=>$order_id,':userId'=>$userId,':rebaseType'=>$rebaseType,':last_orderSelfRebaseRebase'=>$last_orderSelfRebaseRebase,':sum_price'=>$sum_price,':orderStatus'=>$orderStatus,':goods_id'=>$goods_id,':pre_income'=>$pre_income));
                return $_PDO->lastinsertid();
    }
}


#返利表订单插入运营商津贴信息
function YysManageInsertRebate($user_level,$order_balance_at,$order_create_at,$goods_img,$goods_name,$order_id,$userId,$rebaseType,$last_orderSelfRebaseRebase,$sum_price,$orderStatus,$goods_id,$pre_income){
    $_PDO=DB_PDO::getInstance( conf::$DB_CONF );
    if( !empty($order_balance_at) ){
        $sql="insert into order_rebate_profit (user_level,order_balance_at,order_create_at,img,name,order_id,user_id,type,rebate,money,status,goods_id,pre_income) VALUES (:user_level,:order_balance_at,:order_create_at,:goods_img,:goods_name,:order_id,:userId,:rebaseType,:last_orderSelfRebaseRebase,:sum_price,:orderStatus,:goods_id,:pre_income)";
        $stmt = $_PDO->prepare($sql);  
		$stmt->execute(array(':user_level'=>$user_level,':order_balance_at'=>$order_balance_at,':order_create_at'=>$order_create_at,':goods_img'=>$goods_img,':goods_name'=>$goods_name,':order_id'=>$order_id,':userId'=>$userId,':rebaseType'=>$rebaseType,':last_orderSelfRebaseRebase'=>$last_orderSelfRebaseRebase,':sum_price'=>$sum_price,':orderStatus'=>$orderStatus,':goods_id'=>$goods_id,':pre_income'=>$pre_income));
		return $_PDO->lastinsertid();
    }
    else{
        $sql="insert into order_rebate_profit (user_level,order_create_at,img,name,order_id,user_id,type,rebate,money,status,goods_id,pre_income) VALUES (:user_level,:order_create_at,:goods_img,:goods_name,:order_id,:userId,:rebaseType,:last_orderSelfRebaseRebase,:sum_price,:orderStatus,:goods_id,:pre_income)";
        $stmt = $_PDO->prepare($sql);  
		$stmt->execute(array(':user_level'=>$user_level,':order_create_at'=>$order_create_at,':goods_img'=>$goods_img,':goods_name'=>$goods_name,':order_id'=>$order_id,':userId'=>$userId,':rebaseType'=>$rebaseType,':last_orderSelfRebaseRebase'=>$last_orderSelfRebaseRebase,':sum_price'=>$sum_price,':orderStatus'=>$orderStatus,':goods_id'=>$goods_id,':pre_income'=>$pre_income));
		return $_PDO->lastinsertid();
    }
}

#order订单表返利表同步更新
function generateSqlupdate($status,$order_balance_at,$order_id,$goods_id){
    $_PDO=DB_PDO::getInstance( conf::$DB_CONF );
    if( !empty($order_balance_at) ){
        $sql="update `order` set status=:status,order_balance_at=:order_balance_at where order_id=:order_id and goods_id=:goods_id ";
        $stmt = $_PDO->prepare($sql);  
	$stmt->execute(array(':status'=>$status,':order_balance_at'=>$order_balance_at,':order_id'=>$order_id,':goods_id'=>$goods_id));
	$res = $stmt->rowCount();

	//更新order_rebate
	$sql="update order_rebate set status=:status,order_balance_at=:order_balance_at where order_id=:order_id and goods_id=:goods_id ";
	$stmt = $_PDO->prepare($sql);  
	$stmt->execute(array(':status'=>$status,':order_balance_at'=>$order_balance_at,':order_id'=>$order_id,':goods_id'=>$goods_id));
	$res = $stmt->rowCount();

        //更新order_rebate_profit
        $sql="update order_rebate_profit set status=:status,order_balance_at=:order_balance_at where order_id=:order_id and goods_id=:goods_id ";
        $stmt = $_PDO->prepare($sql);  
        $stmt->execute(array(':status'=>$status,':order_balance_at'=>$order_balance_at,':order_id'=>$order_id,':goods_id'=>$goods_id));
        $res = $stmt->rowCount();
    }
    else{
        $sql="update `order` set status=:status where order_id=:order_id and goods_id=:goods_id ";
	$stmt = $_PDO->prepare($sql);  
	$stmt->execute(array(':status'=>$status,':order_id'=>$order_id,':goods_id'=>$goods_id));
	$res = $stmt->rowCount();

       	$sql="update order_rebate set status=:status where order_id=:order_id and goods_id=:goods_id ";
	$stmt = $_PDO->prepare($sql);  
	$stmt->execute(array(':status'=>$status,':order_id'=>$order_id,':goods_id'=>$goods_id));
	$res = $stmt->rowCount();

        //更新order_rebate_profit
        $sql="update order_rebate_profit set status=:status where order_id=:order_id and goods_id=:goods_id ";
        $stmt = $_PDO->prepare($sql);  
        $stmt->execute(array(':status'=>$status,':order_id'=>$order_id,':goods_id'=>$goods_id));
        $res = $stmt->rowCount();
    }  
    return $res;
}

#订单表单独更新
function generateSqlUpdateOther($status,$order_balance_at,$order_id,$goods_id){
    $_PDO=DB_PDO::getInstance( conf::$DB_CONF );
    if( !empty($order_balance_at) ){
        $sql="update `order` set status=:status,order_balance_at=:order_balance_at where order_id=:order_id and goods_id=:goods_id ";
	$stmt = $_PDO->prepare($sql);  
	$stmt->execute(array(':status'=>$status,':order_balance_at'=>$order_balance_at,':order_id'=>$order_id,':goods_id'=>$goods_id));
	$res = $stmt->rowCount();
    }
    else{
        $sql="update `order` set status=:status where order_id=:order_id and goods_id=:goods_id ";
	$stmt = $_PDO->prepare($sql);  
	$stmt->execute(array(':status'=>$status,':order_id'=>$order_id,':goods_id'=>$goods_id));
	$res = $stmt->rowCount();
    }
    return $res;
}

# 获取rebateRule
function getRebateRule($levelRule){
    $sql="SELECT * from `rebate_rule` where level_rule = :level_rule";
    $_PDO=DB_PDO::getInstance( conf::$DB_CONF );
    $stmt = $_PDO->prepare($sql);
    $stmt->execute(array(':level_rule'=>$levelRule));
    $ruleResult = $stmt->fetch(PDO::FETCH_ASSOC);
    return $ruleResult;
}

#redorder订单表返利表同步更新
function redSqlupdate($status,$order_balance_at,$order_id,$goods_id){
    $_PDO=DB_PDO::getInstance( conf::$DB_CONF );
    if( !empty($order_balance_at) ){
        $sql="update `red_order` set status=:status,order_balance_at=:order_balance_at where order_id=:order_id and goods_id=:goods_id ";
        $stmt = $_PDO->prepare($sql);  
        $stmt->execute(array(':status'=>$status,':order_balance_at'=>$order_balance_at,':order_id'=>$order_id,':goods_id'=>$goods_id));
        $res = $stmt->rowCount();
    }
    else{
        $sql="update `red_order` set status=:status where order_id=:order_id and goods_id=:goods_id ";
        $stmt = $_PDO->prepare($sql);  
        $stmt->execute(array(':status'=>$status,':order_id'=>$order_id,':goods_id'=>$goods_id));
        $res = $stmt->rowCount();
    }  

    return $res;
}

function updateRed($order_id, $status ){
    $_PDO=DB_PDO::getInstance( conf::$DB_CONF );
    $sql="update `red_user_info` set order_status=:status where order_id=:order_id";
    $stmt = $_PDO->prepare($sql);  
    $stmt->execute(array(':status'=>$status,':order_id'=>$order_id));
    $res = $stmt->rowCount();

    if($res){
        $sql = "select * from `red_user_info` where order_id = '{$order_id}'";
        $stmt = $_PDO->prepare($sql);
        $stmt->execute();
        $redinfo = $stmt->fetch(PDO::FETCH_ASSOC);
        return $redinfo;
    }
    return $res;
}

function getUserBalace($user_id){
    $table = "red_account_record_".($user_id%10);
    $sql = "SELECT balance from {$table} where user_id={$user_id} order by id desc limit 1";
    $_PDO=DB_PDO::getInstance( conf::$DB_CONF );
    $stmt = $_PDO->prepare($sql);
    $stmt->execute();
    $res = $stmt->fetch(PDO::FETCH_ASSOC);
    return $res;
}

function redAccountRecord($user_id, $type, $money, $oh_id, $balance){
    $table = "red_account_record_".($user_id%10);
    $_PDO=DB_PDO::getInstance( conf::$DB_CONF );
    $sql = "insert into `{$table}` (user_id, type, money, oh_id, balance) values ({$user_id},{$type},{$money},'{$oh_id}', {$balance})";
    $stmt = $_PDO->prepare($sql);  
    $stmt->execute();
    return $_PDO->lastinsertid();
}

function insertLoseAccount($order_id){
    //判断是否已经处理
    // 查出哪些人分到了红包提现
    $sql = "SELECT distinct user_id from `order_rebate` where order_id='{$order_id}' and type=7 ";
    $_PDO=DB_PDO::getInstance( conf::$DB_CONF );
    $stmt = $_PDO->prepare($sql);
    $stmt->execute();
    $res = $stmt->fetchAll(PDO::FETCH_ASSOC);

    if(empty($res)){
        return false;
    }
    
    echo "退红包提现,返还流水balance：\n";
    foreach($res as $k=>$v){
        $table = "red_account_record_".($v['user_id']%10);

        //若已经处理跳过
        $sql = "SELECT id from {$table} where user_id={$v['user_id']} and oh_id='{$order_id}' and type=4";
        $stmt = $_PDO->prepare($sql);
        $stmt->execute();
        $ifExists = $stmt->fetch(PDO::FETCH_ASSOC);
        if(!empty($ifExists)){
            continue;
        }

        $sql = "SELECT sum(money) as money  from {$table} where user_id={$v['user_id']} and oh_id='{$order_id}' and type=2";
        $stmt = $_PDO->prepare($sql);
        $stmt->execute();
        $record = $stmt->fetch(PDO::FETCH_ASSOC);
        $account = getUserBalace($v['user_id']);
        $insertLose = redAccountRecord($v['user_id'], 4, $record['money'], $order_id, round(($account['balance']*100 + $record['money']*100)/100, 2));
    echo "用户".$v['user_id']."：返还".$record['money']." | 处理结果：".$insertLose."\n";
    }
    return $insertLose;
}

# curl调用接口push
function curl_post($url, $params){
	$ch = curl_init();
    curl_setopt($ch, CURLOPT_URL, $url);
    curl_setopt($ch, CURLOPT_HEADER, 0);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
	curl_setopt($ch, CURLOPT_POST, 1);
	curl_setopt($ch, CURLOPT_POSTFIELDS, $params);
	$response = curl_exec($ch);
	curl_close($ch);
	return $response;
}

# 下载excel订单文件
function down_excel(){
    $str_cookie ='cna=gGnDEwQLQAACATqEyYplpj5I; account-path-guide-s1=true; 209580064_yxjh-filter-1=true; undefined_yxjh-filter-1=true; t=8c549c3cedcac7891a2c3c7f36ca91f4; cookie2=1b6238ea20168e295d5bd7dacb9d285d; v=0; _tb_token_=f813b4de14be3; alimamapwag=TW96aWxsYS81LjAgKFdpbmRvd3MgTlQgMTAuMDsgV2luNjQ7IHg2NCkgQXBwbGVXZWJLaXQvNTM3LjM2IChLSFRNTCwgbGlrZSBHZWNrbykgQ2hyb21lLzY5LjAuMzQ5Ny4xMDAgU2FmYXJpLzUzNy4zNg%3D%3D; cookie32=2d2dbd51714bd91fa4e604f2e1050dd6; alimamapw=FnABFCAlFCZcQ3dVHXF2FncCFnF5FCAlFCFWQ3YjHXBxFnAEFnF7FCEgFCFXQ3BRHXFxFnd3FnEB%0AFCIhFCYmQ3YkMQICVwcEUgQPAQFWBlxWBVJRWwEAAABQAgQJV1dSVQAG; cookie31=OTk1MDYxNzMsJUU0JUI4JThBJUU5JUE1JUI2JUU1JUI4JTgyJUU1JTkwJTg4JUU0JUI4JTgwJUU3JUE3JTkxJUU2JThBJTgwJUU2JTlDJTg5JUU5JTk5JTkwJUU1JTg1JUFDJUU1JThGJUI4LGppYW5nbmFuQGZhbnRvdXRpYW8uY29tLFRC; login=WqG3DMC9VAQiUQ%3D%3D; JSESSIONID=BB0014635CAE3BC9A4CB4C3EB3BB1179; apush848645ac0bf07b717f88c18a7e296d1e=%7B%22ts%22%3A1551161803920%2C%22parentId%22%3A1551161793707%7D; l=bBEoP2SHvN8ixqP6XOfwiuIJckbtnIOfhsPzw4TZDICP9a1X5yJhWZaRASTWC3GVa6CpR3Wc5ILQBkLK0yznh; isg=BOHhz5mFGcQ2frL4yOZ9ppnA8K07JkFjkShWTkO32-hHqgd8i972UcusDJ6JYu24';
    $nowTime = END_TIME;
    $pastTime = START_TIME;

    $url = "https://pub.alimama.com/report/getTbkThirdPaymentDetails.json?DownloadID=DOWNLOAD_REPORT_TK3_PUB&queryType=2&payStatus=&startTime=".$pastTime."&endTime=".$nowTime;
    echo $url."\n";

    $ch = curl_init($url);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
    curl_setopt($ch, CURLOPT_COOKIE, $str_cookie); //使用上面获取的cookies
    $response = curl_exec($ch);
    curl_close($ch);
    if( $response ){
        $file = fopen("./third_order_excel.csv", "w+");
        $a = fputs($file, $response);
        fclose($file);
        return './third_order_excel.csv';
    }
    else{
        return false;
    }
        
}

readDataFromDb();