<?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()
    {
        $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('654321');
        Log::info($result);
    }
}