php_script/red_order.php

<?php

require_once 'DB_PDO.class.php';
require_once 'TBK.class.php';
require_once 'confv2.class.php';
require_once 'SendMsg.class.php';
require_once 'YPSM.class.php';

define("DETAILDEBUG", 0);//打印流程详细信息，如有需要，设为1
define("LIMIT_NUM", 0);//限制导入条数,0不限制,方便测试,上线设为0
define("ACT_PUSH_URL",'https://tbk.726p.com/api/v2/MessagePush/act_push');//push接口地址
define("ACT_GOODS_IMG",  'https://kx-youhuiquan.oss-cn-beijing.aliyuncs.com/v2/hongbaojiangli.png');

$redConfig = getConfig();
if($redConfig['red_active_status'] == 0){
	//exit('活动未开启');
}

define("EXTRATE", $redConfig['red_rebate_rate']); //红包提现匹配的佣金比例
define('RED_ACT_OPEN', $redConfig['red_active_status']);//活动开启状态

/**
 * 导入及更新 order/order_rebate数据 + 淘宝拉新活动
 */
#设置时区
date_default_timezone_set('PRC');
set_time_limit(0);
ini_set('memory_limit','1024M');

function readDataFromDb(){
	# 记录脚本开始时间
	$scriptStart = date("Y-m-d H:i:s");
	# 1.获取8分钟内订单
	//$stime = date('Y-m-d H:i:s',strtotime('2018-08-07'));
	$stime = date('Y-m-d H:i:s', time()-360);
	$sql = "SELECT * from ftxia_taoke_detail2 where update_time > :stime and adv_name like 'api_%' and create_time>'2019-01-25'  order by create_time asc,order_sn asc";  
	$dumpsql = str_replace(':stime', "'".$stime."'", $sql);
	echo "\n\n".$dumpsql."\n";
	$_PDO=DB_PDO::getInstance( conf::$DB_CONF );
	$stmt = $_PDO->prepare($sql);  
	$stmt->execute(array(':stime'=>$stime));  	 
	$orderResult = $stmt->fetchAll(PDO::FETCH_ASSOC); 

	# 遍历订单
	# 统计
	$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_updateno_num'  => 0,
	);
	$orderList = array();    
        $rewardOrder = array();  //同订单多商品记录
        $orderRedArr = array(); // 红包订单记录
	$nums = 0;
	foreach( $orderResult as $key=>$orderDetail ){
		$nums++;
		$res_count['total_num']++;
		echo "/ ************************ order{$res_count['total_num']}-Start ************************* / \n";
		if( DETAILDEBUG == 1){
			echo 'orderDetail:';
		}

		$order_id=$orderDetail["order_sn"];
		$update_time=$orderDetail["update_time"];
		$order_create_at=$orderDetail["create_time"];
		$order_click_at=$orderDetail["click_time"];
		$order_balance_at=$orderDetail["balance_time"];
		$goods_id=$orderDetail["goods_id"];
		$goods_name=$orderDetail["goods_name"];
		$goods_name=str_replace(['"',"'"],'',$goods_name);
		$num=$orderDetail["goods_number"];
		$price=intval($orderDetail["goods_price"]*100);
		$sum_price=intval($orderDetail["order_amount"]*100);

		$statusDesc=$orderDetail["order_status"];
		$typeDesc=$orderDetail["order_type"];
		$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["adv_id"];
		$ceil_income=round($orderDetail["effect_prediction"], 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";
		    $orderExists=orderMysql($order_id,$goods_id);

		    if( empty($orderExists) || in_array($order_id,$orderList)){
			if(RED_ACT_OPEN == 0){
				//活动未开启时，跳过插入
				continue;
			}
			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);

			$detail = TBK::tbkItemInfoGet( ['goods_id'=>$goods_id] );                
			if( !empty($detail) ){
				$goods_img=$detail->pict_url;
			}
			else{
			    $goods_img="";
			}
			try{
				$_PDO->beginTransaction();               	
				
			     	/*** 判断是否是红包商品 ***/
				if(!in_array($order_id, $orderRedArr)){
				    $is_red_good = array('discount_price'=>$sum_price/100);//isRedGoods($goods_id);
				    if($sum_price>0 && !empty($is_red_good)){
					//是红包商品，给下单人分红包
					$addred = addRed($resultone['user_id'], $order_id, $goods_id, $is_red_good['discount_price']);
								
					# 奖励推送
					if($addred){
					    $params = array();
					    $params['user_id'] = $resultone['user_id'];
					    $params['type'] = 1; //1 收到新红包
					    $params['money'] = $addred; 					    
					    $push_res = curl_post( ACT_PUSH_URL, $params);

					    #发送升级短信提醒
					    sendUpMsg($resultone['user_id'], $addred);
					}

					$orderRedArr[] = $order_id;
				    }
				}

			    	#订单表插入
			    	$res=InsertRedOrder($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";
			   
			    	//$isTooNew = 0;
			    	//$isNew = 0; 
			    	if($sum_price>=0 and $orderStatus!=0){  

				    /*if(array_key_exists($order_id, $rewardOrder)){
					$isTooNew = 1; //一单多商品
				    }else{
					$isNew = 1;   //一单第一个商品
				    }
				   
				    //记录订单分佣金情况
				    if($isNew){
					$rewardOrder[$order_id] = $moneyInfo; 
				    }else{
					$rewardOrder[$order_id] = arraySums($rewardOrder[$order_id],$moneyInfo);
				    }*/
				    //获取下一个分佣的订单
				    //$nkey = $nums; 
				    //若当前订单和下一笔订单号一样，继续循环，直到本订单分佣rewardOrder[$order_id]统计完毕
				   /* if(isset($orderResult[$nkey]['order_sn']) && $order_id == $orderResult[$nkey]['order_sn']){
					
				    }else{*/ 
					// 红包提现 < --------- Start --------- >
					
					$rewardInfo = $moneyInfo;  //本订单分佣情况                              
					 #获取每一层balance
					$balanceArr = getBalance($userList, $rewardInfo);

					echo 'balanceArr'."\n";
					print_r($balanceArr);

					foreach($balanceArr as $k=>$v){
					    if($v>0 ){
						# 插入奖励
						$rebaseType=7;//$TypeArr[$k]; 
						# 计算奖励佣金
						$reward = getReward($rewardInfo[$k], $v, EXTRATE);
						//$goods_name_new = $k==0 ? '恭喜您获得红包提现奖励' : '恭喜您获得红包提现奖励';
						$goods_img_new = ACT_GOODS_IMG;   
						//插入red_order_reabte 
						$res=rewardOrderInsert($userLevel[$k],$order_balance_at,$order_create_at,$goods_img_new,$goods_name,$order_id,$userList[$k],$rebaseType,$reward,$sum_price,$orderStatus,$goods_id,$predict_income);
						echo 'order_rebate_insert_'.$k.':'.$res."\n";
						//记入流水
						$redAccount = redAccountRecord($userList[$k], 2, round($reward/100, 2), $order_id, round(($balanceArr[$k]-$reward)/100,2));
						echo '记入流水表:'.$redAccount."\n";

						# 奖励推送
						if($res){
						    $params = array();
						    $params['user_id'] = $userList[$k];
						    $params['type'] = $k==0 ? 2 : 3; //2自购 3下级购买
						    $params['money'] = round($reward/100,2);
						    $push_res = curl_post( ACT_PUSH_URL, $params);
						}   
					    }
					}                                     
				    //}
				    
			    }

			    $_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);
			    $redUpRes = updateRed( $order_id, $orderStatus, $goods_id );
			    /*** 失效订单单独更新 Start ***/
			    if($orderStatus == 0){
				//首先判断该订单是否已经失效
				$order_old_status = $orderExists['status'];
				if($order_old_status != 0){ 
				    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);                            

				}
			    }
			    /*** 增加拉新更新 End ***/  

			    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){
				echo 'err_msg'.$e->getMessage()."\n";
			    $_PDO->rollback();
			}
		    }
				//echo "-----------------IssetUserEnd:--------------------\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);
    	echo "\n本次脚本开始时间：". $scriptStart;
    	echo "\n本次脚本结束时间：". date("Y-m-d H:i:s");

}	


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;
    }
}
        
# 获取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 `red_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));  	 
	$result = $stmt->fetch(PDO::FETCH_ASSOC);
    return $result;
}


#order表订单插入脚本
function InsertRedOrder($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){
    $_PDO=DB_PDO::getInstance( conf::$DB_CONF );
    if( empty($order_balance_at) ){
        $sql="insert into `red_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'=>0,':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 `red_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'=>0,':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();
    }
}    

#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 `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();

	//更新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();

    }
    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();

       	$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();
    }  


    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;
}


# 计算提现红包
function getReward($money, $balance, $rate){
    $money = $money * $rate;
    if($money > $balance){
        return $balance;
    }

    return round($money);
}

# 同订单多分佣累加
function arraySums($arr1, $arr2){
    $res = array();
    foreach($arr1 as $k=>$v){
        $res[$k] = $arr1[$k] + $arr2[$k];
    }
    return $res;
}

#提现红包订单插入
function rewardOrderInsert($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 getBalance($userList, $moneyInfo){
    $result = array();
    foreach($userList as $k=>$v){
        if($moneyInfo[$k]>0){
            $res = getUserBalace($v);
            if(isset($res['balance'])){
                $result[$k] = $res['balance']*100;
            }else{
                $result[$k] = 0;
            }
        }else{
            $result[$k] = 0;
        }
    }

    return $result;
}

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 updateRed($order_id, $status, $goods_id ){
    $_PDO=DB_PDO::getInstance( conf::$DB_CONF );
    $sql="update `red_user_info` set order_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();

    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 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;
}

function addRed($user_id, $order_id, $goods_id, $price){
	$red_config = getARedConfig($price);
    $red_money = $red_config['red_money'];
    $open_red_limit = $red_config['open_red_limit'];
    $red_money_open_limit = $red_config['red_money_open_limit'];
    $red_money_open_limit_new = $red_config['red_money_open_limit_new'];

    $_PDO=DB_PDO::getInstance( conf::$DB_CONF );
    $sql = "insert into red_user_info (user_id, order_id, goods_id, red_money, type, red_open_money, open_red_limit, red_money_open_limit, red_money_open_limit_new) values({$user_id}, '{$order_id}', '{$goods_id}', {$red_money}, 1, 0, {$open_red_limit}, '{$red_money_open_limit}', '{$red_money_open_limit_new}') ";
    $stmt = $_PDO->prepare($sql);  
    $stmt->execute();
    if($_PDO->lastinsertid()){
    	return $red_money;
    }else{
    	return 0;
    }
}

function isRedGoods($goods_id){
    $sql="SELECT id,discount_price from `red_goods` where goods_id='{$goods_id}' limit 1";
    $_PDO=DB_PDO::getInstance( conf::$DB_CONF );
    $stmt = $_PDO->prepare($sql);
    $stmt->execute();
    $res = $stmt->fetch(PDO::FETCH_ASSOC);
    return $res;
}

function getConfig(){
	$result = array(
		'red_active_status' => 0	
	);

	# 活动开启
	$sql="SELECT value from `global_variable` where variable_key='red_active_status' and is_deleted=0 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);

    if(empty($res) || $res['value'] == 0){
    	return $result;
    }

    $result['red_active_status'] = 1;
   
    $sql="SELECT value from `global_variable` where variable_key='red_rebate_rate' and is_deleted=0 ";
    $_PDO=DB_PDO::getInstance( conf::$DB_CONF );
    $stmt = $_PDO->prepare($sql);
    $stmt->execute();
    $rate = $stmt->fetch(PDO::FETCH_ASSOC);

    $result['red_rebate_rate'] = $rate['value'];
    return $result;
}

function getARedConfig($price){
	# 获取相关配置
    $sql="SELECT value from `global_variable` where variable_key='red_conf_json' and is_deleted=0 ";
    $_PDO=DB_PDO::getInstance( conf::$DB_CONF );
    $stmt = $_PDO->prepare($sql);
    $stmt->execute();
    $configs = $stmt->fetch(PDO::FETCH_ASSOC);
	$configs = json_decode($configs['value'], true);

	$result = array();
	foreach($configs as $k=>$v){
		if($price>=$v['min'] && $price<$v['max']){
			$result['red_money'] = $v['redmoney'];
			$result['open_red_limit'] = $v['op'];
			$result['red_money_open_limit'] = $v['open_limit'];
			$result['red_money_open_limit_new'] = $v['open_limit'];
			break;
		}
	}

	if(empty($result)){
		# 获取其他相关配置
	    $sql="SELECT variable_key,value from `global_variable` where variable_key in('red_money','open_red_limit','red_money_open_limit','red_money_open_limit_new') and is_deleted=0 ";
	    $stmt = $_PDO->prepare($sql);
	    $stmt->execute();
	    $configs = $stmt->fetchAll(PDO::FETCH_ASSOC);

	    foreach($configs as $k=>$v){
	    	$result[$v['variable_key']] = $v['value'];
	    }
	}

	return $result;	
}

# 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;
}

function sendUpMsg($user_id, $money){
	$sql = " select phone from users where id={$user_id} ";
	$_PDO=DB_PDO::getInstance( conf::$DB_CONF );
    $stmt = $_PDO->prepare($sql);
    $stmt->execute();
    $info = $stmt->fetch(PDO::FETCH_ASSOC);
    if(empty($info)){
    	return false;
    }
    $mobile = $info['phone'];

	//云片发信息
	$ypre = YPSMS::sendSMS($mobile,$money);
	if(isset($ypre['code']) && $ypre['code']==0){
		return $ypre;
	}
	$params = array();
	$params['mobile'] = $mobile;

	//阿里测试      
	$params['SignName'] = '猎豆优选';      
	$params['tpl_id'] = 'SMS_157454480';
	$result = SendMsg::AliSendMsg($params);
	return $result;
	//天瑞测试
	/*
	$params['SignName'] = '猎豆优选';
	$params['tpl_id'] = '30290';
	//$params['extra']['code'] = '111256';
	//$params['extra']['minutes'] = 2;
	$result = SendMsg::TruiSendMsg($params);
	var_dump($result);exit;
	*/

	//云片测试
	/*
	$params['tpl_id'] = '2731830';
	//$params['extra']['code'] = '9000';
	//$params['extra']['minutes'] = 2;
	$params['extra']['money'] = $money;

	$result = SendMsg::YpianSendMsg($params);
	print_r($result);
	return $result;
	*/
	
}

readDataFromDb();