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();
        $this->nbschInsert2();
        echo '时长:'.number_format(((time()-$start_time)/60),2).'分钟';
    }

    public function nbschInsert()
    {
        $master = DB::connection('mysql_nbsch')
            ->table('RT_SERVICE_SETTLEMENT_MASTER')
            ->select(DB::raw("DATE_FORMAT(document_date, '%Y-%m') as document_month"))
            ->groupBy('document_month')
            ->get()
            ->toArray();

        $data = DB::connection('mysql_fwe10')->table('uf_jsxmsp')->select('document_date')->groupBy('document_date')->get()->toArray();

        // 提取第二个数组中的所有日期
        $dataDates = [];
        foreach ($data as $item) {
            $dataDates[] = $item->document_date;
        }

        // 过滤第一个数组，移除在第二个数组中存在的日期
        $filteredMaster = [];
        foreach ($master as $item) {
            if (!in_array($item->document_month, $dataDates)) {
                $filteredMaster[] = $item->document_month;
            }
        }

        if(count($filteredMaster) > 0){
            for($j=0;$j<count($filteredMaster);$j++){
                $riqi = $filteredMaster[$j];
                $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') = ".$riqi."
                    GROUP BY a.DOCUMENT_TYPE, DATE_FORMAT(a.DOCUMENT_DATE, '%Y-%m')
                    ORDER BY DATE_FORMAT(a.DOCUMENT_DATE, '%Y-%m') DESC
                ");

                if(count($result) > 0){
                    for($i=0;$i<count($result);$i++){
                        $id = mt_rand(pow(10, 18), pow(10, 18) + 999999999);
                        $insert = [
                            'ID' => $id,
                            'FORM_DATA_ID' => $id,
                            'DATA_INDEX' => 0.0,
                            'CREATE_TIME' => date('Y-m-d H:i:s'),
                            'TENANT_KEY' => 't1zz9w8165',
                            'IS_DELETE' => 0,
                            'DELETE_TYPE' => 0,
                            'FT_STATUS' => 0,

                            'document_type' => $result[$i]->DOCUMENT_TYPE,
                            'document_date' => $result[$i]->DOCUMENT_DATE,
                            'dyjsje' => $result[$i]->dyjsje,
                            'zcspje' => $result[$i]->zcspje,
                            'qtje' => $result[$i]->qtje,
                            'sptgl' => $result[$i]->sptgl,
                            'sptgl_val' => $result[$i]->sptgl_val
                        ];

                        DB::connection('mysql_fwe10')->table('uf_jsxmsp')->insert($insert);
                    }
                }
            }
        }
    }

    public function nbschInsert2()
    {
        $result = DB::connection('mysql_nbsch')->select("
            SELECT 
                TRIM(
                    REPLACE(
                        REPLACE(
                            REPLACE(
                                ou.dept_name, 
                                '国家能源集团宁夏煤业有限责任公司', 
                                ''
                            ),
                            '国家能源集团宁夏煤业',
                            ''
                        ),
                        '国能宁夏煤业',
                        ''
                    )
                ) AS dept_name,
                a.DOCUMENT_DATE,
                ROUND(a.dyjsje / 10000, 2) AS dyjsje,
                ROUND(a.zcspje / 10000, 2) AS zcspje,
                ROUND(a.qtje / 10000, 2) AS qtje,
                CONCAT(ROUND(a.sptgl * 100, 2), '%') AS sptgl,
                ROUND(a.sptgl * 100, 2) AS sptgl_val
            FROM (
                SELECT 
                    a.INCOME_DEPT_ID,
                    DATE_FORMAT(a.DOCUMENT_DATE, '%Y-%m') AS DOCUMENT_DATE,
                    SUM(a.SETTLEMENT_SUM_AMOUNT) AS dyjsje,
                    (SUM(a.SETTLEMENT_SUM_AMOUNT) - SUM(CASE WHEN a.isNull = '1' THEN a.SETTLEMENT_SUM_AMOUNT ELSE 0 END)) AS zcspje, 
                    SUM(CASE WHEN a.isNull = '1' THEN a.SETTLEMENT_SUM_AMOUNT ELSE 0 END) AS qtje,
                    ROUND(SUM(CASE WHEN a.isNull = '0' THEN 1 ELSE 0 END) / COUNT(a.isNull), 2) AS sptgl
                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 t.* 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 t.lcjdhub NOT REGEXP '被服务'
                    ) lcjl ON fwjs.id = lcjl.BILL_ID 
                    WHERE fwjs.APPROVE_NODE_NAME = '已完成' 
                      AND DATE_FORMAT(fwjs.DOCUMENT_DATE, '%Y-%m') = '2025-06'
                ) a 
                GROUP BY a.INCOME_DEPT_ID, DATE_FORMAT(a.DOCUMENT_DATE, '%Y-%m')
            ) a 
            LEFT JOIN SYS_DEPT ou ON ou.id = a.INCOME_DEPT_ID
            ORDER BY a.DOCUMENT_DATE DESC
        ");

        if(count($result) > 0){
            for($i=0;$i<count($result);$i++){
                $id = mt_rand(pow(10, 18), pow(10, 18) + 999999999);
                $insert = [
                    'ID' => $id,
                    'FORM_DATA_ID' => $id,
                    'DATA_INDEX' => 0.0,
                    'CREATE_TIME' => date('Y-m-d H:i:s'),
                    'TENANT_KEY' => 't1zz9w8165',
                    'IS_DELETE' => 0,
                    'DELETE_TYPE' => 0,
                    'FT_STATUS' => 0,

                    'document_type' => $result[$i]->dept_name,
                    'document_date' => $result[$i]->DOCUMENT_DATE,
                    'dyjsje' => $result[$i]->dyjsje,
                    'zcspje' => $result[$i]->zcspje,
                    'qtje' => $result[$i]->qtje,
                    'sptgl' => $result[$i]->sptgl,
                    'sptgl_val' => $result[$i]->sptgl_val
                ];

                DB::connection('mysql_fwe10')->table('uf_zzdwsp')->insert($insert);
            }
        }
    }
}