php_script/onlive/order_balanc...

<?php
/**
 * Created by PhpStorm.
 * User: hy
 * Date: 2019-04-12
 * Time: 13:53
 */
#佣金结算脚本 每月20号结算上个月

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

#查询上月份结算信息
$stime = date('Y-m-01', strtotime('-1 month')).' 00:00:00';
$etime = date('Y-m-01').' 00:00:00';

$sql = "SELECT user_id,sum(rebate) as money from `order_rebate` where order_balance_at>=:stime and order_balance_at<:etime and status=2 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('未获取到上月结算信息'."\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 create_at>:etime";
    $stmt = $_PDO->prepare($sql);
    $stmt->execute(array(':user_id'=>$v['user_id'],':type'=>1,':etime'=>$etime));
    $res = $stmt->fetch(PDO::FETCH_ASSOC);
    if( !empty($res) ){
        $already++;
        echo "用户已结算过user_id:".$v['user_id']."\n";
        continue;
    }
    $sql = "SELECT sum(rebate) as money from account_detail where type=:type and user_id=:user_id and balance_at>=:stime and balance_at<:etime and state_of_embodiment=1";
    $stmt = $_PDO->prepare($sql);
    $stmt->execute(array(':user_id'=>$v['user_id'],':type'=>3,':stime' => $stime,':etime'=>$etime));
    $res = $stmt->fetch(PDO::FETCH_ASSOC);
    $money = $v['money'];

    # 上次月结算负值特殊处理
    $sql = "SELECT rebate 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( !empty($res)){ //减去预结算的金额, 作为本月结算
        $money = $money - $res['money'];
        $already_money = $res['money'];
    }else{
        $already_money = 0;
    }

    if($last_balance < 0){
        $money = $money + $last_balance;
    }

    $_PDO->beginTransaction();
    try{
        $sql = "INSERT INTO account_detail(user_id,type,rebate,balance_at,order_total_rebate,order_total_price,last_month_debt) values(:user_id,:type,:rebate,:balance_at,:order_total_rebate,:order_total_price,:last_month_debt)";
        $stmt = $_PDO->prepare($sql);
        $stmt->execute(array(':user_id'=>$v['user_id'],':type'=>1,':rebate'=>$money,':balance_at'=>$stime,':order_total_rebate'=>$v['money'],':order_total_price'=>$already_money,':last_month_debt'=>$last_balance));
        $res = $_PDO->lastinsertid();
        //echo "插入结果:".$res."\n";
        if($res){
            #月结成功，把上个月未打款的日结算变为‘已记入尾款’
            $sql = "UPDATE account_detail set state_of_embodiment=3 where type=:type and user_id=:user_id and balance_at>=:stime and balance_at<:etime and state_of_embodiment=0";
            $stmt = $_PDO->prepare($sql);
            $stmt->execute(array(':user_id'=>$v['user_id'],':type'=>3,':stime' => $stime,':etime'=>$etime));
            $upres = $stmt->rowCount();
            $_PDO->commit();
            $success++;
        }else{
            $_PDO->rollback();
            $fail++;
        }
    }catch(PDOException $e){
        $_PDO->rollback();
        echo "err_msg:".$e->getMessage()." user_id".$v['user_id']."\n";
        $fail++;
        continue;
    }
}

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