baoshi-was/app/Console/Commands/CreateOwnerReport.php

<?php

namespace App\Console\Commands;

use App\OwnerAreaReport;
use App\OwnerBillReport;
use App\OwnerReport;
use App\Services\common\BatchUpdateService;
use App\Services\LogService;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

class CreateOwnerReport extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'createOwnerReport';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'create owner report';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * TODO 1号生成或修改上月报表  此处假设每月生成报表数 不足1000，超过此数可能在SQL执行上溢出
     *
     * @return void
     */
    public function handle()
    {
        //转化日期格式 取得上月日期与天数 & 两月前月份
        $year = (int)date('Y');
        $month = (int)date('m');
        if ($month == 1){
            $year--;
            $lastMonth = '12';
        }else $lastMonth = ($month-1) < 10 ? "0".($month-1) : ($month-1);
        $historyYear = $year;
        if (($month-1) == 1){
            $historyYear--;
            $historyMonth = '12';
        }else $historyMonth = ($month-2) < 10 ? "0".($month-2) : ($month-2);
        $lastDay = date("d",strtotime("$year-$lastMonth +1 month -1 day"));

        //获取上月面积与报表
        $areas = OwnerAreaReport::query()->select("id","owner_id","counting_month",DB::raw("SUM(accounting_area) total,YEAR(counting_month) y,MONTH(counting_month) m"))
            ->with(['owner'=>function($query){
                $query->select('id',"code");
            }])->where("counting_month","like",$year."-".$lastMonth."%")
            ->groupBy("y","m","owner_id")->get();
        $reports = OwnerReport::query()->where("counting_month","like",$year."-".$lastMonth."%")->orWhere("counting_month","like",$historyYear."-".$historyMonth."%")->get();
        $bills = OwnerBillReport::query()->where("counting_month","like",$year."-".$lastMonth."%")->get();

        //日均单量统计
        $query = DB::raw("select  count(*) c,CUSTOMERID from DOC_ORDER_HEADER where EDITTIME >= to_date('".$year."-".$lastMonth."-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and EDITTIME <= to_date('".$year."-".$lastMonth."-".$lastDay." 23:59:59','yyyy-mm-dd hh24:mi:ss') group by CUSTOMERID");
        $orderStatistic = DB::connection("oracle")->select($query);
        $map = [];
        foreach ($orderStatistic as $item){
            $map[$item->customerid] = $item->c;
        }

        //已存在的报表记录
        $reportMap = [];
        $historyReportMap = [];
        foreach ($reports as $report){
            if ($report->counting_month == ($historyYear."-".$historyMonth)){
                $historyReportMap[$report->owner_id."_".$report->counting_month] = $report->current_month_counting_area;
                continue;
            }
            $reportMap[$report->owner_id."_".$report->counting_month] = $report->id;
        }

        //组装账单记录
        $billMap = [];
        foreach ($bills as $index => $bill){
            $bill[$bill->owner_id."_".$bill->counting_month] = $index;
        }

        //组装报表记录数据
        $updateReports = [[
            "id","daily_average_order_amount","current_month_counting_area","owner_bill_report_id"
        ]];
        $createReports = [];
        foreach ($areas as $area){
            $total = $area->owner ? ($map[$area->owner->code] ?? 0) : 0;
            if ($reportMap[$area->owner_id."_".$area->counting_month] ?? false){
                $updateReports[] = [
                    "id"=>$reportMap[$area->owner_id."_".$area->counting_month],
                    "total" =>$total,
                    "daily_average_order_amount"=>round($total / $lastDay,2),
                    "current_month_counting_area"=>$area->total ?? 0,
                    "owner_bill_report_id" => $billMap[$area->owner_id."_".$area->counting_month] ?? 0,
                ];
            }else $createReports[] = [
                "owner_id"=>$area->owner_id,
                "counting_month"=>$area->counting_month."-01",
                "total" =>$total,
                "daily_average_order_amount"=>round($total / $lastDay,2),
                "current_month_counting_area"=>$area->total ?? 0,
                "owner_bill_report_id" => $billMap[$area->owner_id."_".$area->counting_month] ?? 0,
                "last_month_counting_area" => $historyReportMap[$area->owner_id."_".($historyYear."-".$historyMonth)] ?? 0,
            ];
        }
        //执行生成或修改
        app(BatchUpdateService::class)->batchUpdate('owner_reports', $updateReports);
        app('LogService')->log(__METHOD__,"客户管理-修改原有货主报表",json_encode($updateReports));
        DB::table("owner_reports")->insert($createReports);
        app('LogService')->log(__METHOD__,"客户管理-生成货主报表",json_encode($createReports));
    }
}