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
//            ];
//        }

        $result = DB::connection('mysql_nbsch')->select("SELECT 
    a.DOCUMENT_TYPE,
    DATE_FORMAT(a.DOCUMENT_DATE, '%Y-%m') AS DOCUMENT_DATE,
    ROUND(SUM(a.SETTLEMENT_SUM_AMOUNT) / 10000, 2) AS dyjsje,
    ROUND((SUM(a.SETTLEMENT_SUM_AMOUNT) - SUM(CASE WHEN a.isNull = '1' THEN a.SETTLEMENT_SUM_AMOUNT ELSE 0 END)) / 10000, 2) AS zcspje,
    ROUND(SUM(CASE WHEN a.isNull = '1' THEN a.SETTLEMENT_SUM_AMOUNT ELSE 0 END) / 10000, 2) AS qtje,
    CONCAT(ROUND(SUM(CASE WHEN a.isNull = '0' THEN 1 ELSE 0 END) / COUNT(a.isNull) * 100, 2), '%') AS sptgl,
    ROUND(SUM(CASE WHEN a.isNull = '0' THEN 1 ELSE 0 END) / COUNT(a.isNull) * 100, 2) AS sptgl_val
FROM (
    SELECT 
        fwjs.*, 
        (CASE WHEN lcjl.BILL_ID IS NOT NULL THEN '1' ELSE '0' END) AS isNull 
    FROM RT_SERVICE_SETTLEMENT_MASTER fwjs
    LEFT JOIN (
        SELECT * FROM (
            SELECT 
                BILL_ID,
                GROUP_CONCAT(approve_node_name ORDER BY approve_node_name SEPARATOR ',') AS lcjdhub 
            FROM SYS_FLOW_APPROVAL_RECORD 
            WHERE approve_scheme_id IN (
                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
            )
            GROUP BY BILL_ID
        ) t 
        WHERE lcjdhub NOT REGEXP '被服务'
    ) lcjl ON fwjs.id = lcjl.BILL_ID 
    WHERE fwjs.APPROVE_NODE_NAME = '已完成' 
) a 
WHERE 1=1 AND DATE_FORMAT(a.DOCUMENT_DATE, '%Y-%m') = '2025-06'  -- 日期筛选条件
GROUP BY a.DOCUMENT_TYPE, DATE_FORMAT(a.DOCUMENT_DATE, '%Y-%m')
ORDER BY DATE_FORMAT(a.DOCUMENT_DATE, '%Y-%m') DESC");

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