xinhua/application/api/controller/Five.php

<?php
namespace app\api\controller;
use think\console\command\make\Model;
use think\Controller;
use think\Db;
use think\Request;
use think\Route;
use think\Session;
class Five extends Controller{

    //1.成品库存明细（全部)->缓存
    public function product_details_redis(){
        $redis=redis();
        $redis_key = md5('product_details_redis');
        $sql = "SELECT hz.分类, hz.订单号, hz.印件名称, hz.承印日期, hz.委印单位, hz.库存数量, hz.库龄, hz.尚需发货数量
FROM (
    SELECT 
        DATE_FORMAT(Dwcrq, '%Y-%m-%d') AS 承印日期,
        CCYDH AS 订单号,
        CYJMC AS 印件名称,
        CYWDWMC AS 委印单位,
        DATE_FORMAT(Dwcrq, '%Y-%m-%d') AS 完工日期,
        e.iAmount AS 要求印数,
        rk.nrksl AS 已入库数量,
        ck.CKSL AS 已出库数量,
        rk.nrksl - ck.CKSL AS 库存数量,
        e.iAmount - ck.CKSL AS 尚需发货数量,
        DATEDIFF(DATE(NOW()), DATE_FORMAT(Dwcrq, '%Y-%m-%d')) AS 库龄,
        CASE
            WHEN DATEDIFF(DATE(NOW()), DATE_FORMAT(Dwcrq, '%Y-%m-%d')) >= 91 THEN '3个月以上'
            ELSE '3个月以内'
        END AS 分类
    FROM MCYD cyd
    LEFT JOIN qlOrderProduct e ON e.cCode = cyd.CCYDH
    LEFT JOIN (
        SELECT 
            cCode,
            SUM(iOutAmount) AS CKSL
        FROM (
            SELECT 
                d.scSaleOrderCode AS cCode,
                b.cName,
                b.iOutAmount,
                DATE_FORMAT(a.dSendDate, '%Y-%m-%d') AS ckrq
            FROM fnOut a
            INNER JOIN fnOutDetail b ON a.ID = b.id_fnOut
            INNER JOIN fnOutPlanDetail d ON b.ID_OutPlanDetail = d.id
            WHERE a.iStatus > 20
        ) ckmx
        GROUP BY cCode
    ) ck ON cyd.CCYDH = ck.cCode
    LEFT JOIN (
        SELECT 
            rk.Code_ProductOrder Code_ProductOrder,
            SUM(rk.iAmount_In) AS nrksl
        FROM (SELECT DISTINCT
				b.iAmount_In,
				b.cCode as Code_ProductOrder,
	a.ID,
	a.ID AS ID_Request,
	b.ID_Mcyd,
	a.cCode,
	a.cName,
	a.iTag_Group,
	a.cUnit,
	1 AS iVers,
	a.cSize,
	1 AS iState,
CASE
	a.iType 
	WHEN 8 THEN
	5 ELSE 0 
	END AS iType,
	'' AS cType_P,
	'' AS cType_P1,
	'' AS cRemark,
	'' AS cAlias,
	1 AS iSign_Used,
	0 AS ID_Origin,
	CAST( a.bNumRange AS SIGNED ) AS iNumRange,
CASE
	
	WHEN b.iCount_NumRange > 1 THEN
	1 ELSE 0 
	END AS iMNumRange,
	b.iCount_NumRange,
	a.iNodePage,
	a.iNodePart,
	0 AS iSample,
	0 AS ID_Comp,
	1 AS iSign_State,
	0 AS nWeight,
	0 AS iSign_FSC,
	0 AS ID_FSCRemark,
	1 AS iState_Request,
	'' AS cGrade,
	c.cdw AS cPackUnit,
	COALESCE ( c.iAmount, 0 ) AS iAmount_Box,
	d.CCYDH,
	d.CYWYXM,
	0 AS iOutCheck,
	'' AS cGrade_OutCheck,
	0 AS iInCheck,
	'' AS cGrade_InCheck 
FROM
	qlAProduct AS a
	INNER JOIN qlOrderProduct AS b ON b.ID_qlAProduct = a.ID
	LEFT JOIN SCydbzyq AS c ON c.ID_qlOrderProduct = b.ID 
	AND c.ixh = 1
	INNER JOIN MCYD AS d ON d.ICYDID = b.ID_Mcyd 
WHERE
d.ICYDSTATE <> - 1) rk
        GROUP BY Code_ProductOrder
    ) rk ON cyd.ccydh = rk.Code_ProductOrder
    WHERE 1 = 1
    AND DATE_FORMAT(cyd.Dwcrq, '%Y-%m-%d') BETWEEN '2021-01-01' AND DATE(CONVERT(NOW(), CHAR(10)))
    AND cyd.LWCBS = '1'
    AND ck.CKSL < e.iAmount
) hz
WHERE hz.库存数量 > 0
ORDER BY hz.库龄 DESC;";
        $res = Db::query($sql);
        if($res){
            $redis->set($redis_key, json_encode($res));
            echo date("Y-m-d H:i:s").'存进去了';
            return $res;
        }
    }
    //1.已完工三个月以内库存明细->接口
    public function product_details_within(){
        $redis = redis();
        $list = json_decode($redis->get(md5('product_details_redis')), true);
//        echo "<pre>";print_r($list);echo "<pre>";

        $result['columns']=[
            ['name'=>'分类','id'=>'fl','width'=>'11','autoWrap'=>"true",'textAlign'=>'left'],
            ['name'=>'订单号','id'=>'ddh','width'=>'12','autoWrap'=>"true",'textAlign'=>'left'],
            ['name'=>'印件名称','id'=>'yjmc','width'=>'26','autoWrap'=>"true",'textAlign'=>'left'],
            ['name'=>'承印日期','id'=>'cyrq','width'=>'14','autoWrap'=>"true",'textAlign'=>'left'],
            ['name'=>'委印单位','id'=>'wydw','width'=>'24','autoWrap'=>"true",'textAlign'=>'left'],
            ['name'=>'库存数量','id'=>'kcsl','width'=>'8','autoWrap'=>"true",'textAlign'=>'left'],
            ['name'=>'库龄','id'=>'kl','width'=>'8','autoWrap'=>"true",'textAlign'=>'left'],
            ['name'=>'尚需发货数量','id'=>'sxfhsl','width'=>'10','autoWrap'=>"true",'textAlign'=>'left']
        ];

        // 过滤分类为“3个月以内”的记录
        $filtered_list = array_filter($list, function($v) {
            return $v['分类'] === '3个月以内';
        });
        // 截取前20条数据
        $filtered_list = array_slice($filtered_list, 0, 20);
        foreach($filtered_list as $k=>$v){
            $result['rows'][$k]['fl']=$v['分类'];
            $result['rows'][$k]['ddh']=$v['订单号'];
            $result['rows'][$k]['yjmc']=$v['印件名称'];
            $result['rows'][$k]['cyrq']=$v['承印日期'];
            $result['rows'][$k]['wydw']=$v['委印单位'];
            $result['rows'][$k]['kcsl']=$v['库存数量'];
            $result['rows'][$k]['kl']=$v['库龄'];  // 修正此处
            $result['rows'][$k]['sxfhsl']=$v['尚需发货数量'];
        }
        // 如果过滤后的列表为空，则添加空数据行
        if(empty($filtered_list)){
            $result['rows'][] = ['fl'=>'', 'ddh'=>'', 'yjmc'=>'', 'cyrq'=>'', 'wydw'=>'', 'kcsl'=>'', 'kl'=>'', 'sxfhsl'=>''];
        }
        $res['status']=0;
        $res['msg']='';
        $res['data']=$result;
        return json($res);
    }
    //1.已完工三个月以上库存明细->接口
    public function product_details_more(){
        $redis = redis();
        $list = json_decode($redis->get(md5('product_details_redis')), true);
//        echo "<pre>";print_r($list);echo "<pre>";

        $result['columns']=[
            ['name'=>'分类','id'=>'fl','width'=>'11','autoWrap'=>"true",'textAlign'=>'left'],
            ['name'=>'订单号','id'=>'ddh','width'=>'12','autoWrap'=>"true",'textAlign'=>'left'],
            ['name'=>'印件名称','id'=>'yjmc','width'=>'26','autoWrap'=>"true",'textAlign'=>'left'],
            ['name'=>'承印日期','id'=>'cyrq','width'=>'14','autoWrap'=>"true",'textAlign'=>'left'],
            ['name'=>'委印单位','id'=>'wydw','width'=>'24','autoWrap'=>"true",'textAlign'=>'left'],
            ['name'=>'库存数量','id'=>'kcsl','width'=>'8','autoWrap'=>"true",'textAlign'=>'left'],
            ['name'=>'库龄','id'=>'kl','width'=>'8','autoWrap'=>"true",'textAlign'=>'left'],
            ['name'=>'尚需发货数量','id'=>'sxfhsl','width'=>'10','autoWrap'=>"true",'textAlign'=>'left']
        ];

        // 过滤分类为“3个月以上”的记录
        $filtered_list = array_filter($list, function($v) {
            return $v['分类'] === '3个月以上';
        });
        // 截取前20条数据
        $filtered_list = array_slice($filtered_list, 0, 20);
        foreach($filtered_list as $k=>$v){
            $result['rows'][$k]['fl']=$v['分类'];
            $result['rows'][$k]['ddh']=$v['订单号'];
            $result['rows'][$k]['yjmc']=$v['印件名称'];
            $result['rows'][$k]['cyrq']=$v['承印日期'];
            $result['rows'][$k]['wydw']=$v['委印单位'];
            $result['rows'][$k]['kcsl']=$v['库存数量'];
            $result['rows'][$k]['kl']=$v['库龄'];  // 修正此处
            $result['rows'][$k]['sxfhsl']=$v['尚需发货数量'];
        }
        // 如果过滤后的列表为空，则添加空数据行
        if(empty($filtered_list)){
            $result['rows'][] = ['fl'=>'', 'ddh'=>'', 'yjmc'=>'', 'cyrq'=>'', 'wydw'=>'', 'kcsl'=>'', 'kl'=>'', 'sxfhsl'=>''];
        }
        $res['status']=0;
        $res['msg']='';
        $res['data']=$result;
        return json($res);
    }

    //2.当日送货完成情况->缓存
    public function Delivery_day_redis(){
        $redis=redis();
        $redis_key = md5('Delivery_day_redis');
        $sql = "SELECT A.CCYDH AS 订单号,
       A.CYJMC AS 印件名称,
       A.CYWDWMC AS 客户名称,
       A.NCS AS 印数,
       jhs.iAmount AS 要求印数,
       dr.当日出库,
       dr.累计出库,
       dr.未出库
FROM mcyd A
LEFT JOIN (
    SELECT hb.订单号,
           hb.客户名称,
           hb.印件名称,
           SUM(hb.当日出库) AS 当日出库,
           COALESCE(hj.ljcksl, 0) + COALESCE(xlj.ck, 0) AS 累计出库,
           CASE
               WHEN hb.册数 - (COALESCE(hj.ljcksl, 0) + COALESCE(xlj.ck, 0)) < 0 THEN 0
               ELSE hb.册数 - (COALESCE(hj.ljcksl, 0) + COALESCE(xlj.ck, 0))
           END AS 未出库
    FROM (
        SELECT DATE_FORMAT(NOW(), '%Y-%m-%d') AS 送货日期,
               q.客户名称,
               q.订单号,
               q.单位,
               q.印件名称,
               q.册数,
               SUM(q.出库数量) AS 当日出库
        FROM (
            SELECT DISTINCT a.cBillCode,
                            a.cClientName AS 客户名称,
                            d.scSaleOrderCode AS 订单号,
                            b.cName AS 印件名称,
                            b.cUnit AS 单位,
                            md.ncs AS 册数,
                            b.iOutAmount AS 出库数量
            FROM fnOut a
            INNER JOIN fnOutDetail b ON a.ID = b.id_fnOut
            INNER JOIN fnOutPlan c ON a.ID_OutPlan = c.id
            INNER JOIN fnOutPlanDetail d ON b.ID_OutPlanDetail = d.id
            INNER JOIN (SELECT DISTINCT
				b.iAmount_In,
				b.cCode as Code_ProductOrder,
				b.iAmount,
	a.ID,
	a.ID AS ID_Request,
	b.ID_Mcyd,
	a.cCode,
	a.cName,
	a.iTag_Group,
	a.cUnit,
	1 AS iVers,
	a.cSize,
	1 AS iState,
CASE
	a.iType 
	WHEN 8 THEN
	5 ELSE 0 
	END AS iType,
	'' AS cType_P,
	'' AS cType_P1,
	'' AS cRemark,
	'' AS cAlias,
	1 AS iSign_Used,
	0 AS ID_Origin,
	CAST( a.bNumRange AS SIGNED ) AS iNumRange,
CASE
	
	WHEN b.iCount_NumRange > 1 THEN
	1 ELSE 0 
	END AS iMNumRange,
	b.iCount_NumRange,
	a.iNodePage,
	a.iNodePart,
	0 AS iSample,
	0 AS ID_Comp,
	1 AS iSign_State,
	0 AS nWeight,
	0 AS iSign_FSC,
	0 AS ID_FSCRemark,
	1 AS iState_Request,
	'' AS cGrade,
	c.cdw AS cPackUnit,
	COALESCE ( c.iAmount, 0 ) AS iAmount_Box,
	d.CCYDH,
	d.CYWYXM,
	0 AS iOutCheck,
	'' AS cGrade_OutCheck,
	0 AS iInCheck,
	'' AS cGrade_InCheck 
FROM
	qlAProduct AS a
	INNER JOIN qlOrderProduct AS b ON b.ID_qlAProduct = a.ID
	LEFT JOIN SCydbzyq AS c ON c.ID_qlOrderProduct = b.ID 
	AND c.ixh = 1
	INNER JOIN MCYD AS d ON d.ICYDID = b.ID_Mcyd 
WHERE
d.ICYDSTATE <> - 1) f ON b.cFnCode = f.cCode
            LEFT JOIN mcyd md ON d.scSaleOrderCode = md.CCYDH
            WHERE a.iStatus > 20
        ) q
        GROUP BY q.客户名称, q.订单号, q.单位, q.册数, q.印件名称
    ) hb
    LEFT JOIN (
        SELECT lj.ccydh,
               lj.cywdwmc,
               lj.ccpmc,
               SUM(lj.ncksl) AS ljcksl
        FROM (
            SELECT a.cywdwmc,
                   a.ccydh,
                   a.ccpmc,
                   a.ncksl
            FROM sexpstore a,
                 dzprod b
            WHERE a.ccpbh = b.ccpbh
                  AND a.ibilltype = 0
                  AND a.dckrq >= @dDate_Star
                  AND a.dckrq <= @dDate_End
                  AND a.btempstore = 0
        ) lj
        GROUP BY lj.ccydh, lj.cywdwmc, lj.ccpmc
    ) hj ON hj.ccydh = hb.订单号
    LEFT JOIN (
        SELECT f.ccydh,
               SUM(r.iOutAmount) AS ck
        FROM fnOutDetail r
        LEFT JOIN fnOut a ON a.ID = r.id_fnOut
        LEFT JOIN mcyd f ON f.icydid = r.id_scSaleOrder
        WHERE a.iStatus > 20
        GROUP BY f.ccydh
    ) xlj ON xlj.CCYDH = hb.订单号
		GROUP  BY hb.客户名称,
                                  hb.订单号,
                                  hb.印件名称,
                                  hb.单位,
                                  hb.册数,
                                  hb.送货日期,
                                  hj.ljcksl,
                                  xlj.ck
) dr ON A.CCYDH = dr.订单号
LEFT JOIN (SELECT DISTINCT
				b.iAmount_In,
				b.cCode as Code_ProductOrder,
				b.iAmount,
	a.ID,
	a.ID AS ID_Request,
	b.ID_Mcyd,
	a.cCode,
	a.cName,
	a.iTag_Group,
	a.cUnit,
	1 AS iVers,
	a.cSize,
	1 AS iState,
CASE
	a.iType 
	WHEN 8 THEN
	5 ELSE 0 
	END AS iType,
	'' AS cType_P,
	'' AS cType_P1,
	'' AS cRemark,
	'' AS cAlias,
	1 AS iSign_Used,
	0 AS ID_Origin,
	CAST( a.bNumRange AS SIGNED ) AS iNumRange,
CASE
	
	WHEN b.iCount_NumRange > 1 THEN
	1 ELSE 0 
	END AS iMNumRange,
	b.iCount_NumRange,
	a.iNodePage,
	a.iNodePart,
	0 AS iSample,
	0 AS ID_Comp,
	1 AS iSign_State,
	0 AS nWeight,
	0 AS iSign_FSC,
	0 AS ID_FSCRemark,
	1 AS iState_Request,
	'' AS cGrade,
	c.cdw AS cPackUnit,
	COALESCE ( c.iAmount, 0 ) AS iAmount_Box,
	d.CCYDH,
	d.CYWYXM,
	0 AS iOutCheck,
	'' AS cGrade_OutCheck,
	0 AS iInCheck,
	'' AS cGrade_InCheck 
FROM
	qlAProduct AS a
	INNER JOIN qlOrderProduct AS b ON b.ID_qlAProduct = a.ID
	LEFT JOIN SCydbzyq AS c ON c.ID_qlOrderProduct = b.ID 
	AND c.ixh = 1
	INNER JOIN MCYD AS d ON d.ICYDID = b.ID_Mcyd 
WHERE
d.ICYDSTATE <> - 1) jhs ON A.CCYDH = jhs.Code_ProductOrder
WHERE A.DJFRQ >= DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00')
      AND A.DJFRQ <= DATE_FORMAT(NOW(), '%Y-%m-%d 23:59:59')
      AND A.iCydState <> -1
ORDER BY dr.客户名称, dr.印件名称;";
        $res = Db::query($sql);
        if($res){
            $redis->set($redis_key, json_encode($res));
            echo date("Y-m-d H:i:s").'存进去了';
            return $res;
        }
    }
    //2.当日送货完成情况->接口
    public function Delivery_day(){
        $redis = redis();
        $list = json_decode($redis->get(md5('Delivery_day_redis')), true);
        $result['columns']=[
            ['name'=>'订单号','id'=>'ddh','width'=>'14','autoWrap'=>"true",'textAlign'=>'left'],
            ['name'=>'印件名称','id'=>'yjmc','width'=>'18','autoWrap'=>"true",'textAlign'=>'left'],
            ['name'=>'客户名称','id'=>'khmc','width'=>'18','autoWrap'=>"true",'textAlign'=>'left'],
            ['name'=>'印数','id'=>'ys','width'=>'11','autoWrap'=>"true",'textAlign'=>'left'],
            ['name'=>'要求印数','id'=>'yqys','width'=>'13','autoWrap'=>"true",'textAlign'=>'left'],
            ['name'=>'当日出库','id'=>'drck','width'=>'12','autoWrap'=>"true",'textAlign'=>'left'],
            ['name'=>'累计出库','id'=>'ljck','width'=>'12','autoWrap'=>"true",'textAlign'=>'left'],
            ['name'=>'未出库','id'=>'wck','width'=>'12','autoWrap'=>"true",'textAlign'=>'left']
        ];
        foreach($list as $k=>$v){
            $result['rows'][$k]['ddh'] = $v['订单号'];
            $result['rows'][$k]['yjmc'] = $v['印件名称'];
            $result['rows'][$k]['khmc'] = $v['客户名称'];
            $result['rows'][$k]['ys'] = $v['印数'];
            $result['rows'][$k]['yqys'] = $v['要求印数'];
            $result['rows'][$k]['drck'] = isset($v['当日出库']) ? $v['当日出库'] : '';
            $result['rows'][$k]['ljck'] = isset($v['累计出库']) ? $v['累计出库'] : '';
            $result['rows'][$k]['wck'] = isset($v['未出库']) ? $v['未出库'] : '';
        }
        // 如果过滤后的列表为空，则添加空数据行
        // 注意：这里似乎有一个未定义的变量$filtered_list，你可能需要确保它在使用前已被正确定义和过滤
        if(empty($filtered_list)){
            $result['rows'][] = ['ddh'=>'', 'yjmc'=>'', 'khmc'=>'','ys'=>'', 'yqys'=>'', 'drck'=>'', 'ljck'=>'', 'wck'=>''];
        }
        $res['status'] = 0;
        $res['msg'] = '';
        $res['data'] = $result;
        return json($res);
    }

    //三个月以上 //三个月以内 ->缓存
    public function sys_redis(){
        $redis=redis();
        $redis_key = md5('sys_redis');
        $sql = "SELECT hz.分类, SUM(hz.库存数量) AS 库存数量, SUM(hz.尚需发货数量) AS 尚需发货数量
FROM (
    SELECT 
        DATE(Dwcrq) AS 完工日期,
        CCYDH AS 订单号,
        CYJMC AS 印件名称,
        CYWDWMC AS 委印单位,
        e.iAmount AS 要求印数,
        rk.nrksl AS 已入库数量,
        ck.CKSL AS 已出库数量,
        rk.nrksl - ck.CKSL AS 库存数量,
        e.iAmount - ck.CKSL AS 尚需发货数量,
        DATEDIFF(CURDATE(), DATE(Dwcrq)) AS 库存周期,
        CASE
            WHEN DATEDIFF(CURDATE(), DATE(Dwcrq)) >= 91 THEN '3个月以上'
            ELSE '3个月以内'
        END AS 分类
    FROM MCYD cyd
    LEFT JOIN qlOrderProduct e ON e.cCode = cyd.CCYDH
    LEFT JOIN (
        SELECT 
            MAX(ckmx.ckrq) AS zwckrq,
            cCode,
            SUM(iOutAmount) AS CKSL
        FROM (
            SELECT 
                d.scSaleOrderCode AS cCode,
                b.cName,
                b.iOutAmount,
                DATE(a.dSendDate) AS ckrq
            FROM fnOut a
            INNER JOIN fnOutDetail b ON a.ID = b.id_fnOut
            INNER JOIN fnOutPlanDetail d ON b.ID_OutPlanDetail = d.id
            WHERE a.iStatus > 20
        ) ckmx
        GROUP BY cCode
    ) ck ON cyd.CCYDH = ck.cCode
    LEFT JOIN (
        SELECT 
            rk.Code_ProductOrder AS Code_ProductOrder,
            SUM(iAmount_In) AS nrksl
        FROM (SELECT DISTINCT
				b.iAmount_In,
				b.cCode as Code_ProductOrder,
				b.iAmount,
	a.ID,
	a.ID AS ID_Request,
	b.ID_Mcyd,
	a.cCode,
	a.cName,
	a.iTag_Group,
	a.cUnit,
	1 AS iVers,
	a.cSize,
	1 AS iState,
CASE
	a.iType 
	WHEN 8 THEN
	5 ELSE 0 
	END AS iType,
	'' AS cType_P,
	'' AS cType_P1,
	'' AS cRemark,
	'' AS cAlias,
	1 AS iSign_Used,
	0 AS ID_Origin,
	CAST( a.bNumRange AS SIGNED ) AS iNumRange,
CASE
	
	WHEN b.iCount_NumRange > 1 THEN
	1 ELSE 0 
	END AS iMNumRange,
	b.iCount_NumRange,
	a.iNodePage,
	a.iNodePart,
	0 AS iSample,
	0 AS ID_Comp,
	1 AS iSign_State,
	0 AS nWeight,
	0 AS iSign_FSC,
	0 AS ID_FSCRemark,
	1 AS iState_Request,
	'' AS cGrade,
	c.cdw AS cPackUnit,
	COALESCE ( c.iAmount, 0 ) AS iAmount_Box,
	d.CCYDH,
	d.CYWYXM,
	0 AS iOutCheck,
	'' AS cGrade_OutCheck,
	0 AS iInCheck,
	'' AS cGrade_InCheck 
FROM
	qlAProduct AS a
	INNER JOIN qlOrderProduct AS b ON b.ID_qlAProduct = a.ID
	LEFT JOIN SCydbzyq AS c ON c.ID_qlOrderProduct = b.ID 
	AND c.ixh = 1
	INNER JOIN MCYD AS d ON d.ICYDID = b.ID_Mcyd 
WHERE
d.ICYDSTATE <> - 1) rk
        GROUP BY rk.Code_ProductOrder
    ) rk ON cyd.ccydh = rk.Code_ProductOrder
    WHERE 
        DATE(Dwcrq) BETWEEN '2021-01-01' AND CURDATE()
        AND cyd.LWCBS = '1'
        AND ck.CKSL < e.iAmount
) hz
GROUP BY hz.分类;";
        $res = Db::query($sql);
        if($res){
            $redis->set($redis_key, json_encode($res));
            echo date("Y-m-d H:i:s").'存进去了';
            return $res;
        }
    }
    //三个月以上\三个月以内 柱状->接口
    public function sys() {
        $redis = redis();
        $list = json_decode($redis->get(md5('sys_redis')), true);
        // 分类数组，对应每个分类的名称
        $categories = [];
        // 库存数量数组，对应每个分类的库存数量
        $inventoryData = [];
        // 遍历查询结果，将每个分类的名称和对应的库存数量添加到数组中
        foreach ($list as $item) {
            $categories[] = ' '; // 添加分类名称到数组
            $inventoryData[] = $item['库存数量']; // 添加库存数量到数组
        }
        // 构建最终的数据结构
        $result = [
            'status' => 0, // 状态码，0代表成功
            'msg' => '', // 消息内容，这里为空字符串
            'data' => [
                'categories' => $categories, // 分类名称数组
                'series' => [
                    [
                        'name' => '库存数量', // 系列名称
                        'data' => $inventoryData // 库存数量数据
                    ]
                ]
            ]
        ];
        // 将结果转换为JSON格式并返回
        return json_encode($result);
    }

    //库存趋势图（两年对比）->缓存
    public function kcqst_redis(){
        $redis=redis();
        $redis_key = md5('kcqst_redis');
        $sql = "WITH RECURSIVE LastDayOfMonth AS (
  SELECT LAST_DAY( CURDATE()) AS LastDay
  UNION ALL
  SELECT LAST_DAY(LastDay - INTERVAL 1 MONTH) AS LastDay
  FROM LastDayOfMonth
  WHERE LastDay > DATE_SUB( CURDATE(), INTERVAL 3 YEAR)
)

SELECT 
  LastDayOfMonth.LastDay AS 日期,
  IFNULL(SUM(CASE
    WHEN AA.完工日期 <= LastDayOfMonth.LastDay THEN AA.库存数量
    ELSE 0
  END), 0) AS 库存总量,
  IFNULL(SUM(CASE
    WHEN AA.完工日期 <= LastDayOfMonth.LastDay THEN AA.尚需发货数量
    ELSE 0
  END), 0) AS 尚需发货数量

FROM LastDayOfMonth
LEFT JOIN (
  SELECT 
    DATE(Dwcrq) AS 完工日期,
    CCYDH AS 订单号,
    CYJMC AS 印件名称,
    CYWDWMC AS 委印单位,
    e.iAmount AS 要求印数,
    rk.nrksl AS 已入库数量,
    ck.CKSL AS 已出库数量,
    rk.nrksl - ck.CKSL AS 库存数量,
    e.iAmount - ck.CKSL AS 尚需发货数量,
    DATEDIFF(CURDATE(), DATE(Dwcrq)) AS 库存周期
  FROM MCYD cyd
  LEFT JOIN qlOrderProduct e ON e.cCode = cyd.CCYDH
  LEFT JOIN (
    SELECT 
      MAX(ckmx.ckrq) AS zwckrq,
      cCode,
      SUM(iOutAmount) AS CKSL
    FROM (
      SELECT 
        d.scSaleOrderCode AS cCode,
        b.cName,
        b.iOutAmount,
        DATE(a.dSendDate) AS ckrq
      FROM fnOut a
      INNER JOIN fnOutDetail b ON a.ID = b.id_fnOut
      INNER JOIN fnOutPlanDetail d ON b.ID_OutPlanDetail = d.id
      WHERE a.iStatus > 20
    ) ckmx
    GROUP BY cCode
  ) ck ON cyd.CCYDH = ck.cCode
  LEFT JOIN (
    SELECT 
      rk.Code_ProductOrder AS Code_ProductOrder,
      SUM(iAmount_In) AS nrksl
    FROM (SELECT DISTINCT
				b.iAmount_In,
				b.cCode as Code_ProductOrder,
				b.iAmount,
	a.ID,
	a.ID AS ID_Request,
	b.ID_Mcyd,
	a.cCode,
	a.cName,
	a.iTag_Group,
	a.cUnit,
	1 AS iVers,
	a.cSize,
	1 AS iState,
CASE
	a.iType 
	WHEN 8 THEN
	5 ELSE 0 
	END AS iType,
	'' AS cType_P,
	'' AS cType_P1,
	'' AS cRemark,
	'' AS cAlias,
	1 AS iSign_Used,
	0 AS ID_Origin,
	CAST( a.bNumRange AS SIGNED ) AS iNumRange,
CASE
	
	WHEN b.iCount_NumRange > 1 THEN
	1 ELSE 0 
	END AS iMNumRange,
	b.iCount_NumRange,
	a.iNodePage,
	a.iNodePart,
	0 AS iSample,
	0 AS ID_Comp,
	1 AS iSign_State,
	0 AS nWeight,
	0 AS iSign_FSC,
	0 AS ID_FSCRemark,
	1 AS iState_Request,
	'' AS cGrade,
	c.cdw AS cPackUnit,
	COALESCE ( c.iAmount, 0 ) AS iAmount_Box,
	d.CCYDH,
	d.CYWYXM,
	0 AS iOutCheck,
	'' AS cGrade_OutCheck,
	0 AS iInCheck,
	'' AS cGrade_InCheck 
FROM
	qlAProduct AS a
	INNER JOIN qlOrderProduct AS b ON b.ID_qlAProduct = a.ID
	LEFT JOIN SCydbzyq AS c ON c.ID_qlOrderProduct = b.ID 
	AND c.ixh = 1
	INNER JOIN MCYD AS d ON d.ICYDID = b.ID_Mcyd 
WHERE
d.ICYDSTATE <> - 1) rk
    GROUP BY rk.Code_ProductOrder
  ) rk ON cyd.ccydh = rk.Code_ProductOrder
  WHERE 
    DATE(Dwcrq) BETWEEN  '2022-01-01' AND CURDATE()
    AND cyd.LWCBS = '1'
    AND ck.CKSL < e.iAmount
) AA ON LastDayOfMonth.LastDay >= DATE(AA.完工日期)
GROUP BY LastDayOfMonth.LastDay
ORDER BY LastDayOfMonth.LastDay;";
        $res = Db::query($sql);
        if($res){
            $redis->set($redis_key, json_encode($res));
            echo date("Y-m-d H:i:s").'存进去了';
            return $res;
        }
    }
    //库存趋势图（两年对比）->接口
    public function kcqst(){
        $redis = redis();
        $result = json_decode($redis->get(md5('kcqst_redis')), true);
//        echo "<pre>";print_r($result);echo "<pre>";

        // 初始化数据
        $list['categories'] = ['01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12'];
        $currentYear = date("Y"); // 当前年份
        $currentMonth = date("m"); // 当前月份
        $previousYear = (int)$currentYear - 1; // 前一年
        // 为前一年和当前年份创建数据数组，初始值为0
        $list['series'][0]['name'] = $previousYear . '年';
        $list['series'][0]['data'] = array_fill(0, 12, 0); // 前一年的数据数组
        $list['series'][1]['name'] = $currentYear . '年';
        $list['series'][1]['data'] = array_fill(0, $currentMonth, 0); // 当前年份的数据数组
        // 遍历查询结果
        foreach ($result as $v) {
            // 获取年份和月份
            $year = substr($v['日期'], 0, 4);
            $month = substr($v['日期'], 5, 2);
            // 如果是前一年的数据
            if ($year == $previousYear) {
                $list['series'][0]['data'][(int)$month - 1] = $v['库存总量'];
            }
            // 如果是当前年份的数据
            if ($year == $currentYear) {
                $list['series'][1]['data'][(int)$month - 1] = $v['库存总量'];
            }
        }
        // 设置响应数据
        $res['status'] = 0;
        $res['msg'] = '';
        $res['data'] = $list;
        return json_encode($res);
    }

    // 总库存->缓存
    public function numcont_redis(){
        $redis=redis();
        $redis_key = md5('numcont_redis');
        $sql = "SELECT 
  YEAR(CURDATE()) AS 年份,
  IFNULL(SUM(CASE
    WHEN YEAR(AA.完工日期) = YEAR(CURDATE()) THEN AA.库存数量
    ELSE 0
  END), 0) AS 库存总量,
  IFNULL(SUM(CASE
    WHEN YEAR(AA.完工日期) = YEAR(CURDATE()) THEN AA.尚需发货数量
    ELSE 0
  END), 0) AS 尚需发货数量
FROM (
  SELECT 
    DATE(Dwcrq) AS 完工日期,
    CCYDH AS 订单号,
    CYJMC AS 印件名称,
    CYWDWMC AS 委印单位,
    e.iAmount AS 要求印数,
    rk.nrksl AS 已入库数量,
    ck.CKSL AS 已出库数量,
    rk.nrksl - ck.CKSL AS 库存数量,
    e.iAmount - ck.CKSL AS 尚需发货数量
  FROM MCYD cyd
  LEFT JOIN qlOrderProduct e ON e.cCode = cyd.CCYDH
  LEFT JOIN (
    SELECT 
      cCode,
      SUM(iOutAmount) AS CKSL
    FROM (
      SELECT 
        d.scSaleOrderCode AS cCode,
        b.iOutAmount,
        DATE(a.dSendDate) AS ckrq
      FROM fnOut a
      INNER JOIN fnOutDetail b ON a.ID = b.id_fnOut
      INNER JOIN fnOutPlanDetail d ON b.ID_OutPlanDetail = d.id
      WHERE a.iStatus > 20
      GROUP BY d.scSaleOrderCode, DATE(a.dSendDate)
    ) ckmx
    GROUP BY cCode
  ) ck ON cyd.CCYDH = ck.cCode
  LEFT JOIN (
    SELECT 
      Code_ProductOrder,
      SUM(iAmount_In) AS nrksl
    FROM (
      
      SELECT DISTINCT
				b.iAmount_In,
				b.cCode as Code_ProductOrder,
				b.iAmount,
	a.ID,
	a.ID AS ID_Request,
	b.ID_Mcyd,
	a.cCode,
	a.cName,
	a.iTag_Group,
	a.cUnit,
	1 AS iVers,
	a.cSize,
	1 AS iState,
CASE
	a.iType 
	WHEN 8 THEN
	5 ELSE 0 
	END AS iType,
	'' AS cType_P,
	'' AS cType_P1,
	'' AS cRemark,
	'' AS cAlias,
	1 AS iSign_Used,
	0 AS ID_Origin,
	CAST( a.bNumRange AS SIGNED ) AS iNumRange,
CASE
	
	WHEN b.iCount_NumRange > 1 THEN
	1 ELSE 0 
	END AS iMNumRange,
	b.iCount_NumRange,
	a.iNodePage,
	a.iNodePart,
	0 AS iSample,
	0 AS ID_Comp,
	1 AS iSign_State,
	0 AS nWeight,
	0 AS iSign_FSC,
	0 AS ID_FSCRemark,
	1 AS iState_Request,
	'' AS cGrade,
	c.cdw AS cPackUnit,
	COALESCE ( c.iAmount, 0 ) AS iAmount_Box,
	d.CCYDH,
	d.CYWYXM,
	0 AS iOutCheck,
	'' AS cGrade_OutCheck,
	0 AS iInCheck,
	'' AS cGrade_InCheck 
FROM
	qlAProduct AS a
	INNER JOIN qlOrderProduct AS b ON b.ID_qlAProduct = a.ID
	LEFT JOIN SCydbzyq AS c ON c.ID_qlOrderProduct = b.ID 
	AND c.ixh = 1
	INNER JOIN MCYD AS d ON d.ICYDID = b.ID_Mcyd 
WHERE
d.ICYDSTATE <> - 1
    
    ) rk
    GROUP BY Code_ProductOrder
  ) rk ON cyd.ccydh = rk.Code_ProductOrder
  WHERE 
    YEAR(Dwcrq) = YEAR(CURDATE())
    AND cyd.LWCBS = '1'
    AND ck.CKSL < e.iAmount
) AA;";
        $res = Db::query($sql);
        if($res){
            $redis->set($redis_key, json_encode($res));
            echo date("Y-m-d H:i:s").'存进去了';
            return $res;
        }
    }
    // 总库存->接口
    public function numcont() {
        $redis = redis();
        $result = json_decode($redis->get(md5('numcont_redis')), true);
        $res = [
            'status' => 0,
            'msg' => '',
        ];
        // 如果结果为空，则直接返回预设的数据结构
        if (empty($result)) {
            $res['data'] = [
                ['name' => ' ', 'value' => 0],
//                ['name' => ' ', 'value' => 0],
            ];
        } else {
            // 注意：这里假设$result总是有数据并且结构固定
            // 对于“尚需发货数量”，除以10000的目的可能是为了单位转换，例如从“个”转换到“万个”
            $res['data'] = [
                ['name' => ' ', 'value' => $result[0]['库存总量']],
//                ['name' => ' ', 'value' => round($result[0]['尚需发货数量'])],
            ];
        }
        return json($res);
    }
}