订单数据量很大的情况临时需求加上定时统计数据

最新推荐文章于 2023-07-27 09:44:20 发布

原创最新推荐文章于 2023-07-27 09:44:20 发布 · 576 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

php 专栏收录该内容

41 篇文章

订阅专栏

本文介绍了一个PHP程序，用于从数据库中获取已支付但未更新的订单，并对其进行统计和更新。程序将订单按员工ID和日期分组，统计每个员工每天的订单数量及总额，并将这些信息保存到汇总表中。

<?php
/**
 * Created by PhpStorm.
 * User: 捉泥鳅
 * Date: 2019/3/4
 * Time: 14:42
 */

namespace app\api\controller;
use think\Db;



class EmployOrderCount
{

    function index()
    {
        $select_where['order_pay_status'] = 2;
        $select_where['update_time'] = null;
        $order_arr['order_id'] = 'asc';
        $limit_str = 500;
        $stime = '2018-09-01';
        $info_update = Db::query('SELECT train_company_id,FROM_UNIXTIME(UNIX_TIMESTAMP(order_create_date) , "%Y-%m-%d") as date,order_id,order_create_date,order_emp_name,order_emp_id,order_pay,train_company_name  FROM `d_emp_order`   WHERE `order_pay_status` = ? AND update_time IS  NULL  AND order_create_date > ? ORDER BY order_id asc limit ?' ,[2,$stime,$limit_str]);
       $order_id_arr =  array_map(function($v){return $v['order_id'];},$info_update);
       Db::startTrans();
       try {
            db('d_emp_order')->whereIn("order_id", $order_id_arr)->update(['update_time' => time()]);
            $order_emp = [];
            // echo json_encode($info_update);exit;
            foreach ($info_update as $order) {
                // db('d_emp_order')->where(['order_id'=>$order['order_id']])->update(['update_time'=>time()]);
                $date_form = $order['date'];
                $emp_id = $order['order_emp_id'];
                if (empty($order_emp[$emp_id][$date_form])) {
                    $order_emp[$emp_id][$date_form] = $order;
                    $order_emp[$emp_id][$date_form]['order_num'] = 1;
                    $order_emp[$emp_id][$date_form]['date'] = $date_form;

                } else {
                    $order_emp[$emp_id][$date_form]['order_num'] += 1;
                    $order_emp[$emp_id][$date_form]['order_pay'] = $order_emp[$emp_id][$date_form]['order_pay'] + $order['order_pay'];
                }
            }
            foreach ($order_emp as $key => $val) {
                foreach ($val as $k => $v) {
                    //  echo $k.$key;exit;
                    $is_exit = Db::table('order_sum')->where(['date' => $k, 'emp_id' => $key, 'emp_name' => $v['order_emp_name']])->find();
                    if (!empty($is_exit)) {
                        // echo 99999;
                        $data['order_num'] = $is_exit['order_num'] + $v['order_num'];
                        $data['sum_pay'] = $is_exit['sum_pay'] + $v['order_pay'];
                        $data['company_name'] = $v['train_company_name'];
                        $data['company_id'] = $v['train_company_id'];
                        $data['order_create_date'] = $v['order_create_date'];
                        $data['update_time'] = time();
                        $data['emp_id'] = $v['order_emp_id'];
                        $data['emp_name'] = $v['order_emp_name'];
                        $data['date'] = $v['date'];
                        $data['last_order_id'] = $v['order_id'];
                        Db::table('order_sum')->where(['id' => $is_exit['id']])->update($data);
                    } else {
                        $data['order_num'] = $v['order_num'];
                        $data['emp_id'] = $v['order_emp_id'];
                        $data['emp_name'] = $v['order_emp_name'];
                        $data['sum_pay'] = $v['order_pay'];
                        $data['company_name'] = $v['train_company_name'];
                        $data['company_id'] = $v['train_company_id'];
                        $data['order_create_date'] = $v['order_create_date'];
                        $data['update_time'] = time();
                        $data['add_time'] = time();
                        $data['date'] = $v['date'];
                        $data['last_order_id'] = $v['order_id'];
                        Db::table('order_sum')->insert($data);
                    }
                }
            }
            Db::commit();
        }catch (\Exception $e){
            Db::rollback();
        }

    }

    

}