video_system/app/Console/Commands/Nbsch.php

<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\Input;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

class Nbsch extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'nbsch:update';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = '内部市场化';

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

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        $start_time = time();
        echo '开始时间:'.date('Y-m-d H:i:s');
        $this->nbschInsert();
        echo '时长:'.number_format(((time()-$start_time)/60),2).'分钟';
    }

    public function nbschInsert()
    {
        $id_list_data =  DB::connection('mysql_nbsch')->select("SELECT ID 
                FROM SYS_FLOW_SCHEME S 
                WHERE EXISTS(
                    SELECT ID 
                    FROM SYS_FLOW_MASTER M 
                    WHERE EXISTS(
                        SELECT 1 
                        FROM sys_flow_master_dept D 
                        WHERE M.ID = D.PID
                    ) 
                    AND S.PID = M.ID 
                    AND M.MENU_ID = '5ab7b937-19a0-47d4-b479-b9f58999abc8'
                ) 
                AND S.IS_DISABLE = 0");
        $id_list = [];
        if(count($id_list_data) > 0){
            for($i=0;$i<count($id_list_data);$i++){
                array_push($id_list,$id_list_data[$i]->ID);
            }
        }
        // dd($id_list);
        $bill_id_list = [];
        if($id_list > 0){
            for($i=0;$i<count($id_list);$i++){
                // for($i=0;$i<3;$i++){
                $bill_id_data = DB::connection('mysql_nbsch')->table('SYS_FLOW_APPROVAL_RECORD')->where('approve_scheme_id',$id_list[$i])->get(['bill_id', 'approve_node_name']);
                if(count($bill_id_data) > 0){
                    for($j=0;$j<count($bill_id_data);$j++){
                        array_push($bill_id_list,$bill_id_data[$j]);
                    }
                }
            }
        }

        $grouped = [];
        foreach ($bill_id_list as $item) {

            $billId = $item->bill_id;

            if (!isset($grouped[$billId])) {
                $grouped[$billId] = [];
            }

            $grouped[$billId][] = $item->approve_node_name;
        }

        $bill_array = [];
        foreach ($grouped as $billId => $nodeNames) {
            $bill_array[] = [
                'bill_id' => $billId,
                'approve_flow' => implode(',', array_unique($nodeNames))
            ];
        }
// dd($bill_array);
        $filteredResult = array_filter($bill_array, function($item) {
            return strpos($item['approve_flow'], '被服务') === false;
        });


// 重置索引
        $filteredResult = array_values($filteredResult);

        // dd($filteredResult);
        $id_list = [];
        foreach($filteredResult as $k=>$v){
            array_push($id_list,$v['bill_id']);
        }
        // dd($id_list);
        $master = DB::connection('mysql_nbsch')->table('RT_SERVICE_SETTLEMENT_MASTER')->where('DOCUMENT_DATE','like','%2025-06%')->where('APPROVE_NODE_NAME','已完成')->get(['ID','DOCUMENT_TYPE','DOCUMENT_DATE','SETTLEMENT_SUM_AMOUNT']);

        if(count($master) > 0){
            for($i=0;$i<count($master);$i++){
                $master[$i]->BILL_ID = null;
                $master[$i]->isNull = 0;
                $master[$i]->riqi = '2025-06';
            }
            for($i=0;$i<count($master);$i++){
                if(count($id_list) > 0){
                    for($j=0;$j<count($id_list);$j++){
                        // dd($id_list[$j]);
                        // dd($master[$i]->ID);
                        if($master[$i]->ID == $id_list[$j]){
                            $master[$i]->BILL_ID = $id_list[$j];
                            $master[$i]->isNull = 1;
                        }
                    }
                }
            }
        }


        $groupedData = [];

        foreach ($master as $item) {

            $key = $item->DOCUMENT_TYPE;

            if (!isset($groupedData[$key])) {
                $groupedData[$key] = [
                    'DOCUMENT_TYPE' => $item->DOCUMENT_TYPE,
                    'DOCUMENT_DATE' => $item->riqi,
                    'total_amount' => 0,
                    'normal_amount' => 0,
                    'null_amount' => 0
                ];
            }

            $groupedData[$key]['total_amount'] += $item->SETTLEMENT_SUM_AMOUNT;

            if ($item->isNull == 0) {
                $groupedData[$key]['normal_amount'] += $item->SETTLEMENT_SUM_AMOUNT;
            } else {
                $groupedData[$key]['null_amount'] += $item->SETTLEMENT_SUM_AMOUNT;
            }
        }

        $result = [];
        foreach ($groupedData as $item) {
            $sptgl_val = $item['total_amount'] > 0 ?
                round($item['normal_amount'] / $item['total_amount'] * 100, 2) : 0;

            $result[] = [
                'DOCUMENT_TYPE' => $item['DOCUMENT_TYPE'],
                'DOCUMENT_DATE' => $item['DOCUMENT_DATE'],
                'dyjsje' => round($item['total_amount'] / 10000, 2),
                'zcspje' => round($item['normal_amount'] / 10000, 2),
                'qtje' => round($item['null_amount'] / 10000, 2),
                'sptgl' => $sptgl_val . '%',
                'sptgl_val' => $sptgl_val
            ];
        }

        Log::info(332211);
        Log::info($result);
    }
}