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()
    {
        $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
        ");

        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_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]->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_zzdwsp')->insert($insert);
            }
        }
    }
}