<?php

namespace App\Admin\Controllers;

use Dcat\Admin\Admin;
use Dcat\Admin\Layout\Content;
use Illuminate\Http\Request;

class DhController extends BaseController
{
    public function index(Content $content)
    {
        $username = Admin::user()->username;
        $sddm = Admin::user()->sddm;
        $khsx5 = Admin::user()->khsx5;
        $db2=$this->db2;

        $khs=$db2->select("SELECT a.KHDM,a.KHMC,ISNULL(b.jiancheng,a.KHDM) as DPXX
        FROM KEHU as a
        LEFT JOIN kehu_jiancheng as b ON a.KHDM=b.KHDM
        WHERE a.SX5_ID='{$khsx5}'");

        if (empty($khs)) {
            return $content->body(view('error', ['error' =>'当前账号角色不允许扫码订货！']));
        }

        $djhs = array();
        foreach ($khs as $k => $v) {
            $khdm = $v->KHDM;
            $khmc = $v->KHMC;

            $DJBH = $db2->table('DHJRD_E')
                    ->selectRaw('DJBH')
                    ->whereRaw("DM1='{$khdm}' AND DJBH LIKE '%MX%'")
                    ->first();
            
            if (empty($DJBH)) {
                //创建主表
                $DJH =  $db2->table('DJBHWH')
                    ->selectRaw('(DJH+1) DJH')
                    ->whereRaw("DJMC='M' AND DM='{$khdm}'")
                    ->first();
                if (empty($DJH)) {
                    $XH=1;
                    $db2->insert("INSERT INTO DJBHWH (BMFS,DJMC,DM,DJH) VALUES (0,'M','{$khdm}',1)");
                }else{
                    $XH=$DJH->DJH;
                    $db2->insert("UPDATE DJBHWH SET DJH='{$XH}' WHERE DM='{$khdm}'");
                }
                $djh='M_'.$khdm.'_MX_'.substr('0000'.$XH, -4);
                // 查出主表数据
                $z_sql = " INSERT INTO DHJRD_E (DJBH,RQ,ZDR,DM1,DM4_1,SL,JE,YS,SP,XC,JZ,YXRQ,RQ_4,BYZD6,BYZD15,DJXZ,DJSX) VALUES 
                ('{$djh}',CONVERT(VARCHAR(10),GETDATE(),120),'{$username}','{$khdm}','000','0','0','0','0','0','0',CONVERT(VARCHAR(10),GETDATE()+30,120),CONVERT(VARCHAR(10),GETDATE(),120),'',GETDATE(),'0','000') ";
                $db2->insert($z_sql);
            }else{
                $djh=$DJBH->DJBH;
            }
            $djhs[] = array('khdm'=>$khdm,'khmc'=>$khmc,'djh'=>$djh,'DPXX'=>$v->DPXX);
        }

        return $content->body(view('dh', ['djhs'=>json_encode($djhs, JSON_UNESCAPED_UNICODE)]));
    }
    public function getdjspmx(Request $request){
        $param=$request->input();
        $djhs=json_decode($param['djhs'],true);
        $djhs = implode("','", array_column($djhs, 'djh'));
        $data = $this->db2->select("SELECT SPDM,CAST(CKJ AS INT) CKJ,CAST(SUM(SL) AS INT) SL,CAST(SUM(SL)*CKJ AS INT) JE 
        FROM DHJRDMX_E WHERE DJBH IN ('{$djhs}')
        GROUP BY SPDM,CKJ");
        return response()->json($data);
    }
    public function deldjspmx(Request $request){
        $param=$request->input();
        $khsx5 = Admin::user()->khsx5;

        $djhs=implode("','", $param['djhs']);
        $res=$this->db2->delete("DELETE FROM DHJRDMX_E WHERE DJBH IN ('{$djhs}') AND SPDM='{$param['spdm']}'");
        $data = $this->db2->select("SELECT A.SPDM,CAST(A.CKJ AS INT) CKJ,CAST(SUM(A.SL) AS INT) SL,CAST(SUM(A.SL)*A.CKJ AS INT) JE 
        FROM DHJRDMX_E A
        INNER JOIN DHJRD_E B ON A.DJBH=B.DJBH
        INNER JOIN KEHU ON B.DM1=KEHU.KHDM
        WHERE KEHU.SX5_ID='{$khsx5}' AND SPDM='{$param['spdm']}' GROUP BY A.SPDM,A.CKJ");

        return response()->json([
            'error' => 0,
            'data'=> $data,
            'msg' => '成功',
        ]);
    }

    public function change(Request $request){
        $input      =   $request->input();
        $username   =   Admin::user()->username;
        $sddm       =   $input['sddm'];
        $db2        =   $this->db2;

        $DJBH =  $db2->table('DHJRD_E')
                ->selectRaw('DJBH')
                ->whereRaw("DM1='{$sddm}' AND DJBH LIKE '%MX%'")
                ->first();
        if (empty($DJBH)) {
            //创建主表
            $DJH =  $db2->table('DJBHWH')
                ->selectRaw('(DJH+1) DJH')
                ->whereRaw("DJMC='M' AND DM='{$sddm}'")
                ->first();
            if (empty($DJH)) {
                $XH=1;
                $db2->insert("INSERT INTO DJBHWH (BMFS,DJMC,DM,DJH) VALUES (0,'M','{$sddm}',1)");
            }else{
                $XH=$DJH->DJH;
                $db2->insert("UPDATE DJBHWH SET DJH='{$XH}' WHERE DM='{$sddm}'");
            }
            $djh='M_'.$sddm.'_MX_'.substr('0000'.$XH, -4);
            // 查出主表数据
            $z_sql = " INSERT INTO DHJRD_E (DJBH,RQ,ZDR,DM1,DM4_1,SL,JE,YS,SP,XC,JZ,YXRQ,RQ_4,BYZD6,BYZD15,DJXZ,DJSX) VALUES 
            ('{$djh}',CONVERT(VARCHAR(10),GETDATE(),120),'{$username}','{$sddm}','000','0','0','0','0','0','0',CONVERT(VARCHAR(10),GETDATE()+30,120),CONVERT(VARCHAR(10),GETDATE(),120),'',GETDATE(),'0','000') ";
            $db2->insert($z_sql);
        }else{
            $djh=$DJBH->DJBH;
        }

        return response()->json([
            'error' => 0,
            'djh' => $djh,
            'msg' => '成功',
        ]);
    }

    public function getsp(Request $request){
        $param=$request->input();
        $ID=$param['code'];
        
        if (is_numeric($ID)) {
            $data = $this->db2->select("SELECT id,spdm,xh FROM SHANGPINID WHERE xh='{$ID}' 
                    UNION ALL
                    SELECT id,spdm,xh FROM SHANGPINID WHERE SPDM LIKE '%{$param['code']}%'");
        }else{
            $data = $this->db2->select("SELECT id,spdm,xh FROM SHANGPINID WHERE SPDM LIKE '%{$param['code']}%'");
        }

        return response()->json($data);
    }

    public function searchYp(Request $request)
    {
        $username = Admin::user()->username;
        $sddm = Admin::user()->sddm;
        $param = $request->input();
        if(empty($param['code'])){
            return response()->json(['error' => 1,'msg' => '商品或ID不能为空!']);
        }

        $sddm = $param['sddm'];
        $djhs = json_decode($param['djhs'],true);
        $ID=$param['code'];
        
        if (is_numeric($ID)) {
            $where="AND (xh='{$ID}' OR spdm like '%{$param['code']}%')";
        }else{
            $where="AND spdm like '%{$param['code']}%'";
        }

        $data = $this->db2->select("SELECT SPDM FROM SHANGPINID WHERE 1=1 {$where}");
        if (empty($data)) {
            return response()->json(['error' => 1,'msg' => '当前会场无此货号！']);
        }
        $code=$data[0]->SPDM;

        $size_sql = "SELECT distinct GUIGE2.GGDM,GUIGE2.GGMC,GUIGE2.GGWZ1,GUIGE2.GGWZ2      
        FROM YPGG2,GUIGE2,YANGPIN
        where YANGPIN.YPDM = '{$code}' and YPGG2.GGID = GUIGE2.GGDM  AND YANGPIN.MID=YPGG2.PID
        order by GUIGE2.GGWZ1,GUIGE2.GGWZ2";
    

        $size_list = objectToArray($this->db2->select($size_sql));

        if(empty($size_list)){
            return response()->json(['error' => 1,'msg' => '商品未维护！']);
        }
        $strcol='';
        foreach ($size_list as $key => $value) {
            $strcol .= "CAST(SUM(CASE WHEN gg2.GGWZ2='{$value['GGWZ2']}' THEN mx.SL ELSE 0 END) AS INT) AS 'SL_{$value['GGWZ2']}',";
            $size_arr['list'][$value['GGWZ1']][$value['GGWZ2']] = $value['GGDM'];
            $ggwz2_arr[] = $value['GGWZ2'];
        }
        $ggwz2_arr = array_unique($ggwz2_arr);
        sort($ggwz2_arr);
        //列
        $size_arr['max_col'] = count($ggwz2_arr);
        $size_arr['ggwz2_arr'] = $ggwz2_arr;
        //行
        $size_arr['max_row'] = count($size_arr['list']);

        $strcol=trim($strcol,',');


        $fjsx =  $this->db2->table('TBL_YPFJSX')
        ->selectRaw('*')
        ->whereRaw("YPDM='{$code}'")
        ->first();

        $spinfo =  $this->db2->table('YANGPIN')
        ->selectRaw('YPDM,YPMC,BZSJ CKJ')
        ->whereRaw("YPDM='{$code}'")
        ->first();

        $data = array();
        $allLsdh = array();
        $allSp = array();

  
        $djhList = "'" . implode("','", array_column($djhs, 'djh')) . "'";
        $khList = "'" . implode("','", array_column($djhs, 'khdm')) . "'";
        
        $sp = $this->db2->select("SELECT dj.DJBH,ISNULL(jc.jiancheng,kh.KHDM) as DPXX,kh.KHDM,kh.KHMC,yp.YPDM,yp.YPMC,yp.BZSJ CKJ,gg1.GGDM gg1dm,gg1.GGMC gg1mc,
        CAST(ISNULL(SUM(mx.SL),0) AS INT) AS HJ_SL,{$strcol}
        FROM YANGPIN yp
        INNER JOIN YPGG1 ygg1 ON yp.MID = ygg1.PID
        INNER JOIN GUIGE1 gg1 ON ygg1.GGID = gg1.GGDM
        INNER JOIN YPGG2 ygg2 ON yp.MID = ygg2.PID
        INNER JOIN GUIGE2 gg2 ON ygg2.GGID = gg2.GGDM
        INNER JOIN DHJRDMX_E mx ON mx.SPDM=yp.YPDM AND mx.GG1DM=gg1.GGDM AND mx.GG2DM=gg2.GGDM 
        INNER JOIN DHJRD_E  dj ON mx.DJBH=dj.DJBH
        INNER JOIN KEHU kh ON dj.DM1=kh.KHDM
        LEFT JOIN kehu_jiancheng as jc ON kh.KHDM=jc.KHDM
        WHERE YPDM = '{$code}' AND yp.TZSY = 0 AND yp.DHH = '000' AND dj.DJBH IN ({$djhList})
        GROUP BY dj.DJBH,ISNULL(jc.jiancheng,kh.KHDM),kh.KHDM,kh.KHMC,yp.YPDM,yp.YPMC,gg1.GGDM,gg1.GGMC,yp.BZSJ ");

        $allLsdh = $this->db3->select("SELECT ISNULL(jc.jiancheng,kh.KHDM) as DPXX,kh.KHDM,kh.KHMC,yp.SPDM YPDM,yp.SPMC YPMC,yp.BZSJ CKJ,gg1.GGDM gg1dm,gg1.GGMC gg1mc,
        CAST(ISNULL(SUM(mx.SL),0) AS INT) AS HJ_SL,{$strcol}
        FROM SHANGPIN yp
        INNER JOIN SPGG1 ygg1 ON yp.SPDM = ygg1.SPDM
        INNER JOIN guige1 gg1 ON ygg1.GGDM = gg1.GGDM
        INNER JOIN SPGG2 ygg2 ON yp.SPDM = ygg2.SPDM
        INNER JOIN GUIGE2 gg2 ON ygg2.GGDM = gg2.GGDM
        INNER JOIN PHJRDMX mx ON mx.SPDM=yp.SPDM AND mx.GG1DM=gg1.GGDM AND mx.GG2DM=gg2.GGDM
        INNER JOIN PHJRD  dj ON mx.DJBH=dj.DJBH
        INNER JOIN KEHU kh ON dj.DM1=kh.KHDM
        LEFT JOIN kehu_jiancheng as jc ON kh.KHDM=jc.KHDM
        AND kh.KHDM IN ({$khList}) AND dj.RQ>=CONVERT(varchar(10),getdate()-120,120) AND dj.SP=0
        WHERE yp.SPDM = '{$code}'
        GROUP BY ISNULL(jc.jiancheng,kh.KHDM),kh.KHDM,kh.KHMC,yp.SPDM,yp.SPMC,gg1.GGDM,gg1.GGMC,yp.BZSJ");


        //补充没有的订单号的商品颜色尺码
        $yp = $this->db2->select("SELECT yp.YPDM,yp.YPMC,yp.BZSJ CKJ,gg1.GGDM gg1dm,gg1.GGMC gg1mc
        FROM YANGPIN yp
        INNER JOIN YPGG1 ygg1 ON yp.MID = ygg1.PID
        INNER JOIN GUIGE1 gg1 ON ygg1.GGID = gg1.GGDM
        WHERE yp.SPDM = '{$code}'");
        
        // 将 $sp 转换为以 DJBH 为键的数组
        $spArray = [];
        foreach ($sp as $item) {
            $spArray[$item->DJBH] = $item;
        }  
        // 合并数组
        $allSp = [];
        foreach ($djhs as $djhItem) {
            $djbh = $djhItem['djh'];
            // 如果 $sp 中存在该 DJBH 的记录，使用 $sp 的数据
            if (isset($spArray[$djbh])) {
                $allSp[] = $spArray[$djbh];
            } else {
                foreach ($yp as $val) {
                    // 如果 $sp 中不存在，创建一个新对象，补充空值
                    $newItem = (object)[
                        'DJBH' => $djbh,
                        'DPXX' => $djhItem['DPXX'],
                        'KHDM' => $djhItem['khdm'],
                        'KHMC' => $djhItem['khmc'],
                        'YPDM' => $val->YPDM,
                        'YPMC' => $val->YPMC,
                        'CKJ' => $val->CKJ,
                        'gg1dm' => $val->gg1dm,
                        'gg1mc' => $val->gg1mc,
                        'HJ_SL' => 0
                    ];
                    // 添加动态列
                    foreach ($ggwz2_arr as $col) {
                        $newItem->{'SL_' . $col} = 0;
                    }
                    $allSp[] = $newItem;
                }
            }
        }

        usort($allLsdh, function($a, $b) {
            return strcmp($a->gg1mc, $b->gg1mc);
        });
        usort($allSp, function($a, $b) {
            return strcmp($a->gg1mc, $b->gg1mc);
        });

        $data['lsdh'] = $allLsdh;
        $data['data'] = $allSp;
        
        if(!$data){
            return response()->json(['error' => 1,'msg' => '商品不存在！']);
        }
        return response()->json([
            'error' => 0,
            'msg' => '查询成功',
            'data' =>$data,
            'spinfo' => $spinfo,
            'fjsx' =>$fjsx,
            'size_arr' => $size_arr
        ]);
    }

    public function submit(Request $request)
    {
        $param = $request->input();
        $username = Admin::user()->username;
        $sddm = Admin::user()->sddm;

        $spdm = $param['code'];
        $ckj  = $param['ckj'];
        $json = $param['spjson'];
        $data = json_decode($json, true);

        $this->db2->beginTransaction();
        try {
            foreach ($data as $k => $v) {
                $djh=$k;
                $this->db2->delete("DELETE FROM DHJRDMX_E WHERE DJBH = '{$djh}' AND SPDM='{$spdm}'");
                $f_sql = " INSERT INTO DHJRDMX_E (DJBH,SPDM,GG1DM,GG2DM,SL,CKJ,ZK,DJ,JE,BYZD14,BZ) VALUES ";
                $sum_sl = 0;
                foreach ($v as $val){
                    $gg1 = $val['gg1dm'];
                    $gg2 = $val['gg2dm'];
                    $sl = (int)$val['sl'];
                    if($sl > 0){
                        $sum_sl += $sl;
                        $mx_info = $this->db2->select(" SELECT * FROM DHJRDMX_E WHERE DJBH = '{$djh}' AND SPDM = '$spdm' AND GG1DM = '$gg1' AND GG2DM = '$gg2' ");
                        if($mx_info){
                            $this->db2->update(" UPDATE DHJRDMX_E SET SL = {$sl} WHERE DJBH = '{$djh}' AND SPDM = '$spdm' AND GG1DM = '$gg1' AND GG2DM = '$gg2' ");
                        }else{
                            $f_sql .= " ('{$djh}','{$spdm}','{$gg1}','{$gg2}','{$sl}','{$ckj}','1','0','0',GETDATE(),''),";
                        }
                    }
                }
                // 判断最后一个是不是逗号 且$sum_sl数量大于0
                if(preg_match('/,$/', $f_sql) && $sum_sl > 0){
                    // 删除最后一个值的逗号
                    $f_sql =  substr($f_sql,0,strlen($f_sql)-1);
                    $this->db2->insert($f_sql);
                }
                $this->db2->update(" UPDATE DHJRD_E SET SL=(SELECT SUM(SL) FROM DHJRDMX_E WHERE DJBH='{$djh}') WHERE DJBH = '{$djh}' ");
            }

            $this->db2->commit();
        }catch (\Exception $e){
            $this->db2->rollBack();
            return response()->json([
                'error' => 1,
                'msg' => '失败，'.$e->getMessage(),
            ]);
        }
        return response()->json([
            'error' => 0,
            'msg' => '成功',
            'data' => ''
        ]);
    }
}
