php_script/onlive/order_balanc...

<?php
/**
 * Created by PhpStorm.
 * User: hy
 * Date: 2019-04-12
 * Time: 13:54
 */
#佣金结算脚本

require_once 'DB_PDO.class.php';
require_once 'conf.class.php';

#连接数据库
$_PDO=DB_PDO::getInstance( conf::$DB_CONF );
date_default_timezone_set('PRC');
set_time_limit(0);
ini_set('memory_limit','512M');

$before_day = 1; //结算几天前的数据
$rebate = 0.5; //结算比率
$today = date('Y-m-d 00:00:00');
#查询上次结算信息
$stime = date('Y-m-d', strtotime(-$before_day.' day')).' 00:00:00';
$etime = date('Y-m-d', strtotime(-$before_day.' day')).' 23:59:59';
$sql = "SELECT user_id,sum(rebate) as money,count(1) as total_count,sum(`money`) as total_price from `order_rebate` where order_create_at>=:stime and order_create_at<=:etime and status>0 and maintain_power<3 group by user_id ";

echo "beginTime:".$stime." -> endTime:".$etime."\n";
$stmt = $_PDO->prepare($sql);
$stmt->execute(array(':stime'=>$stime,':etime'=>$etime));
$rebateInfo = $stmt->fetchAll(PDO::FETCH_ASSOC);

if( empty($rebateInfo) ){
    exit('未获取到'.$stime.'~'.$etime.'结算信息'."\n");
}
$totalcount = count($rebateInfo);
$success = 0;
$fail = 0;
$already = 0;
foreach( $rebateInfo as $k=>$v){
    $sql = "SELECT * from account_detail where type=:type and user_id=:user_id and balance_at=:stime ";
    $stmt = $_PDO->prepare($sql);
    $stmt->execute(array(':user_id'=>$v['user_id'],':type'=>3,':stime'=>$stime));
    $res = $stmt->fetch(PDO::FETCH_ASSOC);
    if( !empty($res) ){
        $already++;
        echo "用户已结算过user_id:".$v['user_id']."\n";
        continue;
    }
    //查询欠款
    $debt_money = 0;
    $if_debt = 0;
    //1.获取最近月结
    $sql = "SELECT rebate,create_at from account_detail where type=:type and user_id=:user_id order by id desc limit 1";
    $stmt = $_PDO->prepare($sql);
    $stmt->execute(array(':user_id'=>$v['user_id'],':type'=>1));
    $last_info = $stmt->fetch(PDO::FETCH_ASSOC);
    $last_balance = 0;
    if( !empty($last_info) ){
        $last_balance = $last_info['rebate'];
    }
    if($last_balance<0){
        //上次月结欠款 ，日结还款
        //1.计算已还
        $y_money = 0;
        $sql = "SELECT sum(rebate) as y_money from account_detail where type=:type and user_id=:user_id and create_at>=:create_at and if_debt=1";
        $stmt = $_PDO->prepare($sql);
        $stmt->execute(array(':user_id'=>$v['user_id'],':type'=>3,':create_at'=>$last_info['create_at']));
        $y_info = $stmt->fetch(PDO::FETCH_ASSOC);
        if(!empty($y_info)){
            $y_money = $y_info['y_money'];
        }
        if($y_money + $last_balance >= 0){
            //1.已经还完
        }else{
            //2.未还完,欠金额
            $debt_money = $y_money + $last_balance;
            $if_debt = 1;
        }
    }

    try{
        $sql = "INSERT INTO account_detail(user_id,type,rebate,balance_at,order_count,order_total_price,order_total_rebate,day_debt_money,if_debt) values(:user_id,:type,:rebate,:balance_at,:count,:total_price,:total_rebate,:day_debt_money,:if_debt)";
        $stmt = $_PDO->prepare($sql);
        //结算预估金额的一半
        $money = (int)($v['money']*$rebate);
        $stmt->execute(array(':user_id'=>$v['user_id'],':type'=>3,':rebate'=>$money,':balance_at' => $stime,':count'=>$v['total_count'],':total_price'=>$v['total_price'],':total_rebate'=>$v['money'], ':day_debt_money'=>$debt_money, ':if_debt'=>$if_debt ));
        $res = $_PDO->lastinsertid();
        //echo "插入结果:".$res."\n";
        if($res){
            $success++;
        }else{
            $fail++;
        }
    }catch(Exception $e){
        echo "err_msg:".$e->getMessage()." user_id".$v['user_id']."\n";
        $fail++;
        continue;
    }
}

echo "成功结算笔数：".$success."\n";
echo "已结算过笔数：".$already."\n";
echo "失败结算笔数：".$fail."\n";