<?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()
    {
        $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_zzdwsp')->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 
                        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') = '".$riqi."'
                        ) 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);
                    }
                }
            }
        }
    }
}