<?php
namespace app\api\controller;
use think\Controller;
use think\Db;
//采购管理 第二屏 数据
class Second extends Controller{
    /** 定义的函数方法 $this->方法名调取*/
    //开始时间方法--当年01月01日
    public function start_time(){
        return  gettimeinfo();
    }
    //结束时间方法--当年12月31日
    public function end_time(){
        return gettimeinfo(1);
    }
    //开始时间方法--去年01月01日
    public function qstart_time(){
        return  getLastYear();
    }
    //结束时间方法--去年12月31日
    public function qend_time(){
        return getLastYear(1);
    }

    /**
     * 一、使用量
     * month_zz方法负责存数据 其他方法负责取该方法数据
     */

    //查询结果集————全年使用量数据->缓存调用
    public function month_zz(){
        $redis = redis();
        $redis_key = md5('month_zz');
        //查询语句
        $sql = "SELECT
	* 
FROM
	(
SELECT CONVERT
	( pp.dFZRQ, CHAR ) 年份,
	aa.cSQ_ZZMC,
	aa.czgmc,
	aa.nAmount,
	pp.cClientName 
FROM
	(
SELECT
	ppOut2.ID,
	ppOut2.cBillCode AS cDJBH,
	ppOut2.id_Center AS ID_GZZX,
	MKernel.cGzzxmc,
	ppOut2.id_Machine AS ID_JT,
	MKerJzmb.cJzmc AS cJTMC,
	ppOut2.id_Shift AS ID_BanZ,
	banzu.cBanzuMc AS cBanZMC,
	ppOut2.ID_mxcpck AS ID_CPMX,
	v_ppOutPlan.cSubpenaName AS cYJMC,
	ppOut2.dDate1 AS dSQRQ,
	ppOut2.dDate2 AS dTJRQ,
	ppOut2.dDate3 AS dFZRQ,
	ppOut2.dDate4 AS dSZRQ,
	ppOut2.cOperatorCode1 AS cSQRBH,
	DZZG.czgxm AS cSQRMC,
	ppOut2.cOperatorCode2 AS cTJRBH,
	dzzg1.czgxm AS cTJRMC,
	ppOut2.cOperatorCode3 AS cFZRBH,
	dzzg2.czgxm AS cFZRMC,
	ppOut2.cOperatorCode4 AS cSZRBH,
	dzzg3.czgxm AS cSZRMC,
	ppOut2.iPaperType AS iZZLX,
	ppOut2.iUseType AS iCKYT,
	ppOut2.iOut AS iSFWJG,
	ppOut2.cRemark AS cBZ,
	ppOut2.iStatus AS iZT,
	ppOut2.id_Role AS ID_KFJS,
	ppOut2.iplant,
	ppOut2.iSourceType,
	ppRole.cRoleName AS cKFJSMC,
	ppvBMMC.cBMBH,
	ppvBMMC.cBMMC,
	v_ppOutPlan.cSubpenaCode AS cCPBH,
	v_ppOutPlan.ckhmc,
	v_ppOutPlan.cClientName,
	v_ppOutPlan.cBillCode_SubClass,
	v_ppOutPlan.cOrderBilllOrigin_SubClass,
	ppOut2.ID_sysRDSetting,
	ppOut2.crdcode,
	ppOut2.crdname,
	ppOut2.cModifierNumber,
	ppOut2.cModifierName,
	IFNULL( v_ppOutPlan.cIsSpellPrinting, '否' ) AS cIsSpellPrinting,
	v_ppOutPlan.cWJGDWBH,
	v_ppOutPlan.cWJGDWMC,
	v_ppOutPlan.cCode_JobOrder 
FROM
	ppOut2
	LEFT OUTER JOIN (
SELECT
	ppOut2.ID,
	ppOut2.cDeptCode AS cBMBH,
	DZBM.cbmmc AS cBMMC 
FROM
	ppOut2
	LEFT OUTER JOIN DZBM ON ppOut2.cDeptCode = DZBM.cbmbh 
WHERE
	( ppOut2.iOut = 0 ) UNION
SELECT
	ppOut2.ID,
	ppOut2.cDeptCode AS cBMBH,
	DZYWDW.cdwmc AS cBMMC 
FROM
	ppOut2
	LEFT OUTER JOIN DZYWDW ON ppOut2.cDeptCode = DZYWDW.cywdwbh 
WHERE
	( ppOut2.iOut = 1 ) 
	) AS ppvBMMC ON ppOut2.ID = ppvBMMC.ID
	LEFT OUTER JOIN ppRole ON ppOut2.id_Role = ppRole.ID
	LEFT OUTER JOIN DZZG AS dzzg3 ON ppOut2.cOperatorCode4 = dzzg3.czgbh
	LEFT OUTER JOIN DZZG AS dzzg2 ON ppOut2.cOperatorCode3 = dzzg2.czgbh
	LEFT OUTER JOIN DZZG AS dzzg1 ON ppOut2.cOperatorCode2 = dzzg1.czgbh
	LEFT OUTER JOIN DZZG ON ppOut2.cOperatorCode1 = DZZG.czgbh
	LEFT OUTER JOIN (
SELECT
	a.imxcpid AS ID,
	b.csccpbh AS cSubpenaCode,
	b.csccpmc AS cSubpenaName,
	a.isign AS iPaperFrom,
	a.cbh AS cPaperCode,
	a.chj AS cPaperName,
	a.cbzdw AS cUnit,
	c.clb AS cPaperType,
	a.nbzsl AS nPlanAmount,
	a.nwfsl AS nNotOutAmount,
	a.nAmount_A,
	0 AS nPlanAmount_OP,
	b.cywdwmc AS ckhmc,
	b.cClientName,
	a.cOrderBilllOriginBillCode,
	a.cBillCode_SubClass,
	a.iStatus,
CASE
	a.iOrderBilllOrigin_SubClass 
	WHEN 0 THEN
	'正常' 
	WHEN 1 THEN
	'补料' 
	WHEN 2 THEN
	'部件补印' 
	WHEN 3 THEN
	'成品补印' 
	WHEN 4 THEN
	'补印' 
	WHEN 5 THEN
	'本厂加工' 
	WHEN 6 THEN
	'返工' 
	END AS cOrderBilllOrigin_SubClass,
	'' AS cIsSpellPrinting,
	0 AS ID_WJGDW,
	'' AS cWJGDWBH,
	'' AS cWJGDWMC,
	a.ID_scAComp,
	a.cCode_scAComp,
	a.cName_scAComp,
	0 AS iType_From,
	'产品' AS cTypeForm,
	'' AS cCode_JobOrder 
FROM
	mxcpck AS a
	INNER JOIN zbcpck AS b ON a.isccpid = b.isccpid 
	AND a.izbcpckID = b.ID
	INNER JOIN dzzzgg AS c ON a.cbh = c.cbh 
	) v_ppOutPlan ON ppOut2.ID_mxcpck = v_ppOutPlan.ID
	LEFT OUTER JOIN banzu ON ppOut2.id_Shift = banzu.banzuId
	LEFT OUTER JOIN MKerJzmb ON ppOut2.id_Machine = MKerJzmb.ID
	LEFT OUTER JOIN MKernel ON ppOut2.id_Center = MKernel.ID 
	) pp
	LEFT JOIN (
SELECT
	ppvOutDetail2.*,
	gg.cOldSystemNumber,
	gg.nhss,
CASE
	
	WHEN ppvOutDetail2.cSF_DW = '公斤' THEN
	ppvOutDetail2.nSF_SL / 1000 
	WHEN ppvOutDetail2.cSF_DW = '令' THEN
	( CASE WHEN gg.nhss = 0 THEN ppvOutDetail2.nSF_SL / 20 ELSE ppvOutDetail2.nSF_SL / gg.nhss END ) 
	WHEN ppvOutDetail2.cSF_DW = '张' THEN
	(
	CASE
			
			WHEN gg.nhss = 0 THEN
			ppvOutDetail2.nSF_SL / 500 / 1.5 ELSE ppvOutDetail2.nSF_SL / 500 / gg.nhss 
		END 
		) ELSE 0 
	END AS nAmount
FROM
	(
	SELECT
		ppOutDetail2.id,
		ppOutDetail2.ID_ppOut2,
		ppOutDetail2.cReqPaperCode AS cSQ_ZZBH,
		dzzzgg1.chj AS cSQ_ZZMC,
		dzzzgg1.czgmc AS czgmc,
		ppOutDetail2.cReqUnit AS cSQ_DW,
		IFNULL( ppOutDetail2.nReqAmount, 0 ) AS nSQ_SL,
		ppvOutDetailAccount.cPaperCode AS cSF_ZZBH,
		dzzzgg_1.chj AS cSF_ZZMC,
		ppvOutDetailAccount.cUnit AS cSF_DW,
		IFNULL( ppvOutDetailAccount.nAmount, 0 ) AS nSF_SL 
	FROM
		dzzzgg AS dzzzgg1
		RIGHT OUTER JOIN dzzzgg AS dzzzgg_1
		RIGHT OUTER JOIN (
		SELECT
			ID_ppOutDetail2,
			cPaperCode,
			cUnit,
			SUM( nAmount ) AS nAmount 
		FROM
			ppOutDetailFlat2 
		GROUP BY
			ID_ppOutDetail2,
			cPaperCode,
			cUnit UNION
		SELECT
			ID_ppOutDetail2,
			cPaperCode,
			cUnit,
			SUM( nAmount ) AS nAmount 
		FROM
			ppOutDetailRoll2 
		GROUP BY
			ID_ppOutDetail2,
			cPaperCode,
			cUnit 
		) AS ppvOutDetailAccount
		RIGHT OUTER JOIN ppOutDetail2 ON ppvOutDetailAccount.ID_ppOutDetail2 = ppOutDetail2.id ON dzzzgg_1.cbh = ppvOutDetailAccount.cPaperCode ON dzzzgg1.cbh = ppOutDetail2.cReqPaperCode 
	) ppvOutDetail2
	LEFT JOIN dzzzgg gg ON gg.cbh = ppvOutDetail2.cSQ_ZZBH 
	) aa ON pp.id = aa.ID_ppOut2 
WHERE
	1 = 1 
	AND iZT = 3 
	AND iCKYT = 1 
	AND dFZRQ BETWEEN '{$this->start_time()}' AND '{$this->end_time()}' ) tt";
        //执行语句
        $res = Db::query($sql);
        //将查询结果存入 Redis 缓存中
        $redis->set($redis_key, json_encode($res));
        echo date("Y-m-d H:i:s").'   存进去了';
        return $res;
    }
    //1.1全年总使用量（吨）->接口调用
    public function yearuse(){
        $redis = redis();
        $list = json_decode($redis->get(md5('month_zz')),true);
        $sum_dun = 0;
        foreach ($list as $item) {
            $sum_dun  += $item['nAmount'];//数量
        }
        $list=[['name'=>' ','value'=>round($sum_dun)]];
        $res['status']= 0;
        $res['msg']   = '';
        $res['data']  = $list;
        return json($res);
    }
    //1.3全年各出版社厂料使用量->接口调用
    public function yearpress(){
        $redis = redis();
        $o_list = json_decode($redis->get(md5('month_zz')), true);
        if(empty($o_list)) {
            return json_encode([
                'status' => 0,
                'msg' => '没有找到数据',
                'data' => [
                    'categories' => [''],
                    'series' => [
                        ['name' => '使用量', 'data' => [0]]
                    ]
                ]
            ]);
        }
        $list = [];
        $sort_arr = [];
        foreach($o_list as &$v){
            switch ($v['cClientName']) {
                case '浙江教育出版社集团有限公司':
                    $v['cClientName'] = '浙江教育出版社';
                    break;
                case '浙江出版传媒股份有限公司':
                    $v['cClientName'] = '浙江出版传媒';
                    break;
                case '人民教育出版社有限公司':
                    $v['cClientName'] = '人民教育出版社';
                    break;
                case '《浙江共产党员》杂志集团有限公司':
                    $v['cClientName'] = '《浙江共产党员》杂志';
                    break;
                case '教育科学出版社有限公司':
                    $v['cClientName'] = '教育科学出版社';
                    break;
                case '浙江省新华书店集团有限公司（作业本）':
                    $v['cClientName'] = '浙江省新华书店（作业本）';
                    break;
                case '中信出版集团股份有限公司':
                    $v['cClientName'] = '中信出版集团';
                    break;
                case '读者出版传媒股份有限公司':
                    $v['cClientName'] = '读者出版传媒';
                    break;
                case '大星（上海）文化传媒有限公司（浙江文艺）':
                case '大星（上海）文化传媒有限公司（浙江少儿）':
                case '大星（上海）文化传媒有限公司（上海书店）':
                case '大星（上海）文化传媒有限公司（河南文艺）':
                case '大星（上海）文化传媒有限公司':
                    $v['cClientName'] = '大星（上海）文化传媒';
                    break;
                default:
                    // 不满足条件的客户名称保持原样
                    break;
            }
        }
        foreach ($o_list as $value) {
            $key = md5($value['cClientName']);
            $nAmount = $value['nAmount'];
            if (isset($list[$key])) {
                $list[$key]['nAmount'] += $nAmount; // 吨
            } else {
                $list[$key]['nAmount'] = $nAmount; // 吨
                $list[$key]['cClientName'] = $value['cClientName'];
                $list[$key]['dun'] = '吨';
            }
            $sort_arr[$key] = $list[$key]['nAmount'];
        }
        arsort($sort_arr); // 根据 nAmount 降序排序
        array_multisort($list,SORT_DESC);
        $list=array_splice($list,0,10);
        $i=0;
        foreach($list as $v){
            $result['categories'][$i] = $v['cClientName'];
            $result['series'][0]['name'] = '使用量';
            $result['series'][0]['data'][$i] = round($v['nAmount']);
            $i++;
        }
        // echo "全年各出版社厂料使用量";echo "<pre>";print_r($result);echo "</pre>"; die;
        $res['status'] = 0;
        $res['msg'] = '';
        $res['data'] = $result;
        return json_encode($res);
    }

    /**
     * 二、采购量
     * er_lst方法负责存数据 其他方法负责取该方法数据
     */

    //查询结果集————全年采购量数据->缓存调用
    public function er_lst(){
        $redis = redis();
        $redis_key = md5('er_lst');
        //查询语句
        $sql = "SELECT ppIn.ID,
    CASE
                         WHEN ppInDetail.cunit = '公斤' THEN ppInDetail.namount / 1000
                         WHEN ppInDetail.cunit = '令' THEN 
						 (case when dzzzgg.nhss=0 then ppInDetail.namount / 20 ELSE  ppInDetail.namount / dzzzgg.nhss end)
						WHEN ppInDetail.cunit = '张' THEN  (case when dzzzgg.nhss=0 then ppInDetail.namount / 500/1.5 ELSE  ppInDetail.namount / 500/dzzzgg.nhss end)
                         ELSE 0
                       END                                         AS '数量（吨）',
dzzzgg.czgmc,
dzzzgg.nhss,
		-- ppin.iplant = 0 采购类型 2 书刊类型
       ppin.iplant 入库单类型,
       ppIn.cbillcode,
       CASE
         WHEN ppInNoticeClient.cBillCode IS NOT NULL THEN 100
         ELSE ppIn.iSourceType
       END                                         iSourceType,
       ppInDetail.cpcode,
       ppInDetail.cpname,
       -- ppInDetail.cbatchcode,
       -- ppIn.cowner,
       ppIn.csupplier 公司,
       DATE_FORMAT(ppIn.darrival, '%Y-%m-%d') darrival,
       ppInDetail.namount namount,
       ppInDetail.mPrice 标准单价,
       ppInDetail.cunit 单位,
       F.nTaxBuyPrice 含税单价,
       PPInDetail.namount * IFnull(F.nTaxPrice, 0) AS 含税金额 ,
       dzzzgg.cgg 规格
FROM   ppIn
       INNER JOIN ppInDetail
               ON ppIn.cbillcode = ppInDetail.cbillcode
                  AND ppIn.iStatus >= 0
       LEFT JOIN (SELECT cBillCode,
                         cPaperCode,
                         cBatchCode,
                         CASE Count(*)
                           WHEN 1 THEN Min(b.cPlaceShowName)
                           ELSE ( Min(b.cPlaceShowName) + ',...' )
                         END AS cKwmc
                  FROM   ppInDetailPlace a
                         LEFT JOIN (SELECT   a.ID, a.ikwxh, a.ckwmc, a.ikfid, a.ixnID, a.ikwlx, a.ipbrl, a.ijtrl, a.cbz, a.iFlag, a.iForbid, 
                c.ckfjc +  a.ckwmc AS cPlaceShowName, a.cPlaceCode, a.iProperty, b.iRoleID, b.cRoleName, c.ckfmc, c.ckfjc, c.ikflb, 
                c.ikfsx, c.iForbid AS iStoreForbid, c.ibmID, c.iPaper, c.iStuff, c.iHalf, c.iFinish, c.iWaste, c.iTemp, c.iEntrust, 
                IFNULL(c.iPSStuff, 0) AS iPSStuff, c.iRollPaper_WeightRoll, c.iSample, c.iSampleLibrary
FROM      dzkw AS a LEFT OUTER JOIN
                    (SELECT   aa.ikfID, aa.iRoleID, bb.cRoleName
                     FROM      dzStoreRole AS aa INNER JOIN
                                         (SELECT   ID, iRoleNO, cRoleName, cRemark, iType
                                          FROM      ppRole
                                          WHERE   (iType = 0)) AS bb ON aa.iRoleID = bb.ID) AS b ON a.ikfid = b.ikfID AND 
                a.iForbid = 0 LEFT OUTER JOIN
                dzkf AS c ON a.ikfid = c.ID AND c.iForbid = 0) b
                                ON a.id_Place = b.id
                  GROUP  BY cBillCode,
                            cPaperCode,
                            cBatchCode) E
              ON ppInDetail.cBillCode = E.cBillCode
                 AND ppInDetail.cPCode = E.cpapercode
                 AND ppInDetail.cBatchCode = E.cbatchcode
       LEFT JOIN ppInNoticeDetail F
              ON PPInDetail.cmessagecode = F.cbillcode
                 AND PPInDetail.cmessagepaper = F.cpcode
       LEFT JOIN ppInNotice
              ON PPInDetail.cmessagecode = ppInNotice.cBillCode
       LEFT JOIN ppInNoticeDetailClient F1
              ON PPInDetail.cmessagecode = F1.cbillcode
                 AND PPInDetail.cmessagepaper = F1.cpcode
       LEFT JOIN ppInNoticeClient
              ON PPInDetail.cmessagecode = ppInNoticeClient.cBillCode
       LEFT JOIN skBuy
              ON skBuy.id = ppInNotice.id_skBuy
       LEFT JOIN skBuyDetail
              ON skBuyDetail.id_skBuy = ppInNotice.id_skBuy
                 AND skBuyDetail.cMaterialCode = ppInDetail.cMessagePaper
       LEFT JOIN (SELECT a.cSccpBh,
                         b.cPaperCode
                  FROM   sccp a
                         INNER JOIN ppArtifactDetail b
                                 ON a.sccpid = b.ID_sccp) G
              ON PPInDetail.cmessagecode = G.cSccpBh
                 AND PPInDetail.cmessagepaper = G.cPaperCode
       LEFT JOIN dzzzgg
              ON dzzzgg.cbh = ppInDetail.cpcode
WHERE  ( ppIn.iSourceType = 0 or ppIn.iSourceType = 2 )
       AND ( ppInNoticeClient.cbillcode IS NULL )
       AND ( ppIn.dArrival >= '{$this->start_time()}' )
       AND ( ppIn.dArrival <= '{$this->end_time()}' )
ORDER  BY ppIn.cbillcode,
          ppInDetail.isn ";
        //执行语句
        $res=Db::query($sql);
        //将查询结果存入 Redis 缓存中
        $redis->set($redis_key, json_encode($res));
        echo date("Y-m-d H:i:s").'   存进去了';
        return $res;
    }
    //查询结果集————去年采购量数据->缓存调用
    public function qer_lst(){
        $redis = redis();
        $redis_key = md5('qer_lst');
        //查询语句
        $sql = "SELECT ppIn.ID,
    CASE
                         WHEN ppInDetail.cunit = '公斤' THEN ppInDetail.namount / 1000
                         WHEN ppInDetail.cunit = '令' THEN 
						 (case when dzzzgg.nhss=0 then ppInDetail.namount / 20 ELSE  ppInDetail.namount / dzzzgg.nhss end)
						WHEN ppInDetail.cunit = '张' THEN  (case when dzzzgg.nhss=0 then ppInDetail.namount / 500/1.5 ELSE  ppInDetail.namount / 500/dzzzgg.nhss end)
                         ELSE 0
                       END                                         AS '数量（吨）',
dzzzgg.czgmc,
dzzzgg.nhss,
		-- ppin.iplant = 0 采购类型 2 书刊类型
       ppin.iplant 入库单类型,
       ppIn.cbillcode,
       CASE
         WHEN ppInNoticeClient.cBillCode IS NOT NULL THEN 100
         ELSE ppIn.iSourceType
       END                                         iSourceType,
       ppInDetail.cpcode,
       ppInDetail.cpname,
       -- ppInDetail.cbatchcode,
       -- ppIn.cowner,
       ppIn.csupplier 公司,
       DATE_FORMAT(ppIn.darrival, '%Y-%m-%d') darrival,
       ppInDetail.namount namount,
       ppInDetail.mPrice 标准单价,
       ppInDetail.cunit 单位,
       F.nTaxBuyPrice 含税单价,
       PPInDetail.namount * IFnull(F.nTaxPrice, 0) AS 含税金额 ,
       dzzzgg.cgg 规格
FROM   ppIn
       INNER JOIN ppInDetail
               ON ppIn.cbillcode = ppInDetail.cbillcode
                  AND ppIn.iStatus >= 0
       LEFT JOIN (SELECT cBillCode,
                         cPaperCode,
                         cBatchCode,
                         CASE Count(*)
                           WHEN 1 THEN Min(b.cPlaceShowName)
                           ELSE ( Min(b.cPlaceShowName) + ',...' )
                         END AS cKwmc
                  FROM   ppInDetailPlace a
                         LEFT JOIN (SELECT   a.ID, a.ikwxh, a.ckwmc, a.ikfid, a.ixnID, a.ikwlx, a.ipbrl, a.ijtrl, a.cbz, a.iFlag, a.iForbid, 
                c.ckfjc +  a.ckwmc AS cPlaceShowName, a.cPlaceCode, a.iProperty, b.iRoleID, b.cRoleName, c.ckfmc, c.ckfjc, c.ikflb, 
                c.ikfsx, c.iForbid AS iStoreForbid, c.ibmID, c.iPaper, c.iStuff, c.iHalf, c.iFinish, c.iWaste, c.iTemp, c.iEntrust, 
                IFNULL(c.iPSStuff, 0) AS iPSStuff, c.iRollPaper_WeightRoll, c.iSample, c.iSampleLibrary
FROM      dzkw AS a LEFT OUTER JOIN
                    (SELECT   aa.ikfID, aa.iRoleID, bb.cRoleName
                     FROM      dzStoreRole AS aa INNER JOIN
                                         (SELECT   ID, iRoleNO, cRoleName, cRemark, iType
                                          FROM      ppRole
                                          WHERE   (iType = 0)) AS bb ON aa.iRoleID = bb.ID) AS b ON a.ikfid = b.ikfID AND 
                a.iForbid = 0 LEFT OUTER JOIN
                dzkf AS c ON a.ikfid = c.ID AND c.iForbid = 0) b
                                ON a.id_Place = b.id
                  GROUP  BY cBillCode,
                            cPaperCode,
                            cBatchCode) E
              ON ppInDetail.cBillCode = E.cBillCode
                 AND ppInDetail.cPCode = E.cpapercode
                 AND ppInDetail.cBatchCode = E.cbatchcode
       LEFT JOIN ppInNoticeDetail F
              ON PPInDetail.cmessagecode = F.cbillcode
                 AND PPInDetail.cmessagepaper = F.cpcode
       LEFT JOIN ppInNotice
              ON PPInDetail.cmessagecode = ppInNotice.cBillCode
       LEFT JOIN ppInNoticeDetailClient F1
              ON PPInDetail.cmessagecode = F1.cbillcode
                 AND PPInDetail.cmessagepaper = F1.cpcode
       LEFT JOIN ppInNoticeClient
              ON PPInDetail.cmessagecode = ppInNoticeClient.cBillCode
       LEFT JOIN skBuy
              ON skBuy.id = ppInNotice.id_skBuy
       LEFT JOIN skBuyDetail
              ON skBuyDetail.id_skBuy = ppInNotice.id_skBuy
                 AND skBuyDetail.cMaterialCode = ppInDetail.cMessagePaper
       LEFT JOIN (SELECT a.cSccpBh,
                         b.cPaperCode
                  FROM   sccp a
                         INNER JOIN ppArtifactDetail b
                                 ON a.sccpid = b.ID_sccp) G
              ON PPInDetail.cmessagecode = G.cSccpBh
                 AND PPInDetail.cmessagepaper = G.cPaperCode
       LEFT JOIN dzzzgg
              ON dzzzgg.cbh = ppInDetail.cpcode
WHERE  ( ppIn.iSourceType = 0 or ppIn.iSourceType = 2 )
       AND ( ppInNoticeClient.cbillcode IS NULL )
       AND ( ppIn.dArrival >= '{$this->qstart_time()}' )
       AND ( ppIn.dArrival <= '{$this->end_time()}' )
ORDER  BY ppIn.cbillcode,
          ppInDetail.isn ";
        //执行语句
        $res=Db::query($sql);
        //将查询结果存入 Redis 缓存中
        $redis->set($redis_key, json_encode($res));
        echo date("Y-m-d H:i:s").'   存进去了';
        return $res;
    }
    //查询结果集————进行处理每月各类纸张使用量
    public function tt12new(){
        $redis = redis();
        $list = json_decode($redis->get(md5('month_zz')),true);
        if(empty($list)) {
            return '';
        }else{
            $o_list = [];
            foreach ($list as $k => $item) {
                $tmp = [];
                $tmp['dCreate'] = date('Ym', strtotime($item['年份']));
                $tmp['czgmc'] = pdcateinfobyczgmc($item['czgmc']);
                $tmp['nAmount'] = $item['nAmount'];
                $o_list[] = $tmp;
            }
            $r_list = [];
            //按照日期分类分组求和
            foreach ($o_list as $item) {
                $key = md5($item['czgmc'] . $item['dCreate']);
                if (isset($r_list[$key])) {
                    $r_list[$key]['czgmc'] = $item['czgmc'];
                    $r_list[$key]['nsl'] += $item['nAmount'];
                    $r_list[$key]['dCreate'] = $item['dCreate'];
                } else {
                    $r_list[$key]['czgmc'] = $item['czgmc'];
                    $r_list[$key]['nsl'] = $item['nAmount'];
                    $r_list[$key]['dCreate'] = $item['dCreate'];
                }
            }
            //按照日期重组数据
            $out_list = [];
            foreach ($r_list as $key => $item) {
                $catename = $item['czgmc'];
                //归类
                if (isset($out_list[$item['dCreate']])) {
                    if (isset($out_list[$item['dCreate']][$catename])) {
                        $out_list[$item['dCreate']][$catename] += round($item['nsl'],2);
                    } else {
                        $out_list[$item['dCreate']][$catename] = round($item['nsl'],2);
                    }
                } else {
                    $out_list[$item['dCreate']][$catename] = round($item['nsl'],2);
                }
            }
            //补全分类
            foreach ($out_list as $key=>&$val) {
                //bucateinfo 显示主要纸张分类
                $val = bucateinfo($val);
                arsort($val);
            }
            //补全月份
            $month_arr = getYearInfo();
            //循环将月份分开查询
            foreach($month_arr as  $m){
                if(!isset($out_list[$m])){
                    $out_list[$m] = bucateinfo();
                }
            }
            //月份正序进行排序
            ksort($out_list);
            return $out_list;
        }
    }

    //2.1全年采购量->接口调用
    public function year_procurement(){
        $redis = redis();
        $list = json_decode($redis->get(md5('er_lst')),true);
        $sum_dun = 0;//吨
        $sum_jine = 0;//金额
        foreach ($list as $item) {
            $t = [];
            $t['number'] =    floatval($item['namount']);//数量
            $t['nhss'] =      floatval($item['nhss']);//吨折令
            $t['sumprice'] =  toround($item['含税金额']);//含税金额
            $t['unit'] =      trim($item['单位']);//单位
            $sum_dun +=       erp_price($t);
//            $sum_jine +=      $t['sumprice'];
        }
        $list=[['name'=>' ','value'=>round($sum_dun)]];
        $res['status']=0;
        $res['msg']='';
        $res['data']=$list;
        return json($res);
    }
    //2.1-1全年采购金额->接口调用
    public function year_money(){
        $redis = redis();
        $list = json_decode($redis->get(md5('er_lst')),true);
        $sum_jine = 0;//金额
        foreach ($list as $item) {
            $t = [];
            $t['number'] =    floatval($item['namount']);//数量
            $t['nhss'] =      floatval($item['nhss']);//吨折令
            $t['sumprice'] =  toround($item['含税金额']);//含税金额
            $t['unit'] =      trim($item['单位']);//单位
            $sum_jine +=      $t['sumprice'];
        }
        $list=[['name'=>' ','value'=>round($sum_jine/10000)]];
        $res['status']=0;
        $res['msg']='';
        $res['data']=$list;
        return json($res);
    }
    //2.2每月采购量、金额->接口调用
    public function month_procurement(){
        $redis = redis();
        $list = json_decode($redis->get(md5('er_lst')),true);
        $r_list = [];
        foreach ($list as $item) {
            $month = date('Ym',strtotime($item['darrival']));
            $t = [];
            $t['dArrival'] =    $month;//月份
            $t = [];
            $t['number'] =    floatval($item['namount']);//数量
            $t['nhss'] =      floatval($item['nhss']);//吨折令
            $t['sumprice'] =  toround($item['含税金额']);//含税金额
            $t['unit'] =      trim($item['单位']);//单位
            $sum_dun =        erp_price($t);//吨
            $t['dweight'] =   $sum_dun;
            $sum_jine =      $t['sumprice'];//金额

            if (isset($r_list[$month])) {
                $r_list[$month]['dArrival'] = $month;//月份
                $r_list[$month]['dun'] += $sum_dun;//吨
                $r_list[$month]['jine'] += $sum_jine;//金额
            } else {
                $r_list[$month]['dArrival'] = $month;//月份
                $r_list[$month]['dun'] = $sum_dun;//吨
                $r_list[$month]['jine'] = $sum_jine;//金额
            }
        }
        $c_list = [];
        //补全月份
        $yearInfo = getYearInfo();
        foreach ($yearInfo as $item){
            $item = intval($item);
            if(!isset($r_list[$item])){
                $c_list[] = [
                    'dtime' => $item,//月份
                    'dun' =>0,//吨
                    'jine' =>0,//金额
                ];
            }else{
                $c_list[] = $r_list[$item];
            }
        }
        $list=[];
        $list['categories']=['01','02','03','04','05','06','07','08','09','10','11','12'];
        foreach($c_list as $k=>$v){
            $list['series'][0]['name']='采购量（吨）';
            $list['series'][1]['name']='采购金额（万元）';
            $list['series'][0]['data'][]=(int)round($v['dun']);
            $list['series'][1]['data'][]=(int)round($v['jine']/10000);
        }
        $res['status']=0;
        $res['msg']='';
        $res['data']=$list;
        return json($res);
    }

    //2.3-1每月各类纸张采购量（第一页）->接口调用
    public function month_paperdun(){
        $redis = redis();
        $list = json_decode($redis->get(md5('er_lst')),true);
        if(empty($list)) {
            return '';
        }else{
            $mainCate  = getcateinfo();
            foreach ($list as $item){
                $catename = getCateName($item['czgmc']);
                //是否属于主要纸张
                if(!in_array($catename,$mainCate)){
                    continue;
                }
                $month = date('Ym',strtotime($item['darrival'] ));
                $key = md5($catename.$month);
                $t = [];
                $t['dtime'] =    $month;//月份
                $t['catename'] = $catename;//纸张名称
                $t['dweight'] =   floatval($item['数量（吨）']);//
                $t['sumprice'] = floatval($item['含税金额']);//金额
                $dweight = $t['dweight'];
                $dprice =  $t['sumprice'] ;
                if (isset($r_list[$key])) {
                    $r_list[$key]['catename'] = $catename;//
                    $r_list[$key]['month'] = $month;//
                    $r_list[$key]['dun'] += $dweight;//吨
                    $r_list[$key]['jine'] += $dprice;//金额
                } else {
                    $r_list[$key]['catename'] = $catename;//
                    $r_list[$key]['month'] = $month;//
                    $r_list[$key]['dun'] = $dweight;//吨
                    $r_list[$key]['jine'] = $dprice;//金额
                }
            }
            $c_list = [];
            foreach($r_list as $item){
                $c_list[$item['month']][$item['catename']] =  toround($item['dun']);//
            }
            //补全分类
            foreach ($c_list as &$val) {
                $val = bucateinfo($val);
                arsort($val);
            }
            //补全月份
            $month_arr = getYearInfo();
            foreach($month_arr as  $m){
                if(!isset($c_list[$m])){
                    $c_list[$m] = bucateinfo();
                }
            }
//        echo "每月各类纸张采购量（第一页）";echo "<pre>";print_r($c_list);echo "</pre>";die;
            return $c_list;
        }

    }
    //每月各类纸张采购量、使用量-高白双胶纸采购量
    public function gbsj(){
        $result = $this->month_paperdun();
        $result1= $this->tt12new();
        if(empty($result) || empty($result1)) {
            return json([
                'status' => 0,
                'msg' => '没有找到数据',
                'data' => [
                    'categories' => [''],
                    'series' => [
                        ['name' => '采购量', 'data' => [0]],
                        ['name' => '使用量', 'data' => [0]]
                    ]
                ]
            ]);
        }else{
            foreach($result as $k=>$v){
                $list['categories'][]=$k;
                $list['series'][0]['name']='高白双胶纸';
                $list['series'][0]['data'][]=round($v['高白双胶纸']);
            }
            foreach($result1 as $k=>$v){
                $list1['categories'][]=$k;
                $list1['series'][0]['name']='高白双胶纸';
                $list1['series'][0]['data'][]=round($v['高白双胶纸']);
            }
            $data=input('post.');
            if(isset($data['dependence'])){
                $dependence=$data['dependence'];
            }else{
                $dependence['item']['value']=date('m',time());
            }
            $i=(int)$dependence['item']['value'];
            $res1['categories'][]=$dependence['item']['value'];
            $res1['series'][0]['name']='采购量';
            $res1['series'][0]['data'][]=$list['series'][0]['data'][$i-1];
            $res1['series'][1]['name']='使用量';
            $res1['series'][1]['data'][]=$list1['series'][0]['data'][$i-1];
            $res['status']=0;
            $res['msg']='';
            $res['data']=$res1;
            return json($res);
        }

    }
    //每月各类纸张采购量、使用量-纯质纸采购量
    public function czz(){
        $result=$this->month_paperdun();
        $result1= $this->tt12new();
        if(empty($result) || empty($result1)) {
            return json([
                'status' => 0,
                'msg' => '没有找到数据',
                'data' => [
                    'categories' => [''],
                    'series' => [
                        ['name' => '采购量', 'data' => [0]],
                        ['name' => '使用量', 'data' => [0]]
                    ]
                ]
            ]);
        }else{
            foreach($result as $k=>$v){
                $list['categories'][]=$k;
                $list['series'][0]['name']='纯质纸';
                $list['series'][0]['data'][]=round($v['纯质纸']);
            }
            foreach($result1 as $k=>$v){
                $list1['categories'][]=$k;
                $list1['series'][0]['name']='纯质纸';
                $list1['series'][0]['data'][]=round($v['纯质纸']);
            }
            $data=input('post.');
            if(isset($data['dependence'])){
                $dependence=$data['dependence'];
            }else{
                $dependence['item']['value']=date('m',time());
            }
            $i=(int)$dependence['item']['value'];
            $res1['categories'][]=$dependence['item']['value'];
            $res1['series'][0]['name']='采购量';
            $res1['series'][0]['data'][]=$list['series'][0]['data'][$i-1];
            $res1['series'][1]['name']='使用量';
            $res1['series'][1]['data'][]=$list1['series'][0]['data'][$i-1];
            $res['status']=0;
            $res['msg']='';
            $res['data']=$res1;
            return json($res);
        }

    }
    //每月各类纸张采购量、使用量-全灰板采购量
    public function qhb(){
        $result=$this->month_paperdun();
        $result1= $this->tt12new();
        if(empty($result) || empty($result1)) {
            return json([
                'status' => 0,
                'msg' => '没有找到数据',
                'data' => [
                    'categories' => [''],
                    'series' => [
                        ['name' => '采购量', 'data' => [0]],
                        ['name' => '使用量', 'data' => [0]]
                    ]
                ]
            ]);
        }else{
            foreach($result as $k=>$v){
                $list['categories'][]=$k;
                $list['series'][0]['name']='全灰板';
                $list['series'][0]['data'][]=round($v['全灰板']);
            }
            foreach($result1 as $k=>$v){
                $list1['categories'][]=$k;
                $list1['series'][0]['name']='全灰板';
                $list1['series'][0]['data'][]=round($v['全灰板']);
            }
            $data=input('post.');
            if(isset($data['dependence'])){
                $dependence=$data['dependence'];
            }else{
                $dependence['item']['value']=date('m',time());
            }
            $i=(int)$dependence['item']['value'];
            $res1['categories'][]=$dependence['item']['value'];
            $res1['series'][0]['name']='采购量';
            $res1['series'][0]['data'][]=$list['series'][0]['data'][$i-1];
            $res1['series'][1]['name']='使用量';
            $res1['series'][1]['data'][]=$list1['series'][0]['data'][$i-1];
            $res['status']=0;
            $res['msg']='';
            $res['data']=$res1;
            return json($res);
        }

    }
    //每月各类纸张采购量、使用量-白卡纸采购量
    public function bkz(){
        $result=$this->month_paperdun();
        $result1= $this->tt12new();
        if(empty($result) || empty($result1)) {
            return json([
                'status' => 0,
                'msg' => '没有找到数据',
                'data' => [
                    'categories' => [''],
                    'series' => [
                        ['name' => '采购量', 'data' => [0]],
                        ['name' => '使用量', 'data' => [0]]
                    ]
                ]
            ]);
        }else{
            foreach($result as $k=>$v){
                $list['categories'][]=$k;
                $list['series'][0]['name']='白卡纸';
                $list['series'][0]['data'][]=round($v['白卡纸']);
            }
            foreach($result1 as $k=>$v){
                $list1['categories'][]=$k;
                $list1['series'][0]['name']='白卡纸';
                $list1['series'][0]['data'][]=round($v['白卡纸']);
            }
            $data=input('post.');
            if(isset($data['dependence'])){
                $dependence=$data['dependence'];
            }else{
                $dependence['item']['value']=date('m',time());
            }
            $i=(int)$dependence['item']['value'];
            $res1['categories'][]=$dependence['item']['value'];
            $res1['series'][0]['name']='采购量';
            $res1['series'][0]['data'][]=$list['series'][0]['data'][$i-1];
            $res1['series'][1]['name']='使用量';
            $res1['series'][1]['data'][]=$list1['series'][0]['data'][$i-1];
            $res['status']=0;
            $res['msg']='';
            $res['data']=$res1;
            return json($res);
        }

    }
    //每月各类纸张采购量、使用量-轻型纸采购量
    public function qxz(){
        $result=$this->month_paperdun();
        $result1= $this->tt12new();
        if(empty($result) || empty($result1)) {
            return json([
                'status' => 0,
                'msg' => '没有找到数据',
                'data' => [
                    'categories' => [''],
                    'series' => [
                        ['name' => '采购量', 'data' => [0]],
                        ['name' => '使用量', 'data' => [0]]
                    ]
                ]
            ]);
        }else{
            foreach($result as $k=>$v){
                $list['categories'][]=$k;
                $list['series'][0]['name']='轻型纸';
                $list['series'][0]['data'][]=round($v['轻型纸']);
            }
            foreach($result1 as $k=>$v){
                $list1['categories'][]=$k;
                $list1['series'][0]['name']='轻型纸';
                $list1['series'][0]['data'][]=round($v['轻型纸']);
            }
            $data=input('post.');
            if(isset($data['dependence'])){
                $dependence=$data['dependence'];
            }else{
                $dependence['item']['value']=date('m',time());
            }
            $i=(int)$dependence['item']['value'];
            $res1['categories'][]=$dependence['item']['value'];
            $res1['series'][0]['name']='采购量';
            $res1['series'][0]['data'][]=$list['series'][0]['data'][$i-1];
            $res1['series'][1]['name']='使用量';
            $res1['series'][1]['data'][]=$list1['series'][0]['data'][$i-1];
            $res['status']=0;
            $res['msg']='';
            $res['data']=$res1;
            return json($res);
        }

    }
    //每月各类纸张采购量、使用量-亚光双面铜版纸采购量
    public function ygsmtb(){
        $result=$this->month_paperdun();
        $result1= $this->tt12new();
        if(empty($result) || empty($result1)) {
            return json([
                'status' => 0,
                'msg' => '没有找到数据',
                'data' => [
                    'categories' => [''],
                    'series' => [
                        ['name' => '采购量', 'data' => [0]],
                        ['name' => '使用量', 'data' => [0]]
                    ]
                ]
            ]);
        }else{
            foreach($result as $k=>$v){
                $list['categories'][]=$k;
                $list['series'][0]['name']='亚光双面铜版纸';
                $list['series'][0]['data'][]=round($v['亚光双面铜版纸']);
            }
            foreach($result1 as $k=>$v){
                $list1['categories'][]=$k;
                $list1['series'][0]['name']='亚光双面铜版纸';
                $list1['series'][0]['data'][]=round($v['亚光双面铜版纸']);
            }
            $data=input('post.');
            if(isset($data['dependence'])){
                $dependence=$data['dependence'];
            }else{
                $dependence['item']['value']=date('m',time());
            }
            $i=(int)$dependence['item']['value'];
            $res1['categories'][]=$dependence['item']['value'];
            $res1['series'][0]['name']='采购量';
            $res1['series'][0]['data'][]=$list['series'][0]['data'][$i-1];
            $res1['series'][1]['name']='使用量';
            $res1['series'][1]['data'][]=$list1['series'][0]['data'][$i-1];
            $res['status']=0;
            $res['msg']='';
            $res['data']=$res1;
            return json($res);
        }

    }
    //每月各类纸张采购量、使用量-轻涂纸采购量
    public function qtz(){
        $result=$this->month_paperdun();
        $result1= $this->tt12new();
        if(empty($result) || empty($result1)) {
            return json([
                'status' => 0,
                'msg' => '没有找到数据',
                'data' => [
                    'categories' => [''],
                    'series' => [
                        ['name' => '采购量', 'data' => [0]],
                        ['name' => '使用量', 'data' => [0]]
                    ]
                ]
            ]);
        }else{
            foreach($result as $k=>$v){
                $list['categories'][]=$k;
                $list['series'][0]['name']='轻涂纸';
                $list['series'][0]['data'][]=round($v['轻涂纸']);
            }
            foreach($result1 as $k=>$v){
                $list1['categories'][]=$k;
                $list1['series'][0]['name']='轻涂纸';
                $list1['series'][0]['data'][]=round($v['轻涂纸']);
            }
            $data=input('post.');
            if(isset($data['dependence'])){
                $dependence=$data['dependence'];
            }else{
                $dependence['item']['value']=date('m',time());
            }
            $i=(int)$dependence['item']['value'];
            $res1['categories'][]=$dependence['item']['value'];
            $res1['series'][0]['name']='采购量';
            $res1['series'][0]['data'][]=$list['series'][0]['data'][$i-1];
            $res1['series'][1]['name']='使用量';
            $res1['series'][1]['data'][]=$list1['series'][0]['data'][$i-1];
            $res['status']=0;
            $res['msg']='';
            $res['data']=$res1;
            return json($res);
        }

    }
    //每月各类纸张采购量、使用量-彩画纸采购量
    public function chz(){
        $result=$this->month_paperdun();
        $result1= $this->tt12new();
        if(empty($result) || empty($result1)) {
            return json([
                'status' => 0,
                'msg' => '没有找到数据',
                'data' => [
                    'categories' => [''],
                    'series' => [
                        ['name' => '采购量', 'data' => [0]],
                        ['name' => '使用量', 'data' => [0]]
                    ]
                ]
            ]);
        }else{
            foreach($result as $k=>$v){
                $list['categories'][]=$k;
                $list['series'][0]['name']='彩画纸';
                $list['series'][0]['data'][]=round($v['彩画纸']);
            }
            foreach($result1 as $k=>$v){
                $list1['categories'][]=$k;
                $list1['series'][0]['name']='彩画纸';
                $list1['series'][0]['data'][]=round($v['彩画纸']);
            }
            $data=input('post.');
            if(isset($data['dependence'])){
                $dependence=$data['dependence'];
            }else{
                $dependence['item']['value']=date('m',time());
            }
            $i=(int)$dependence['item']['value'];
            $res1['categories'][]=$dependence['item']['value'];
            $res1['series'][0]['name']='采购量';
            $res1['series'][0]['data'][]=$list['series'][0]['data'][$i-1];
            $res1['series'][1]['name']='使用量';
            $res1['series'][1]['data'][]=$list1['series'][0]['data'][$i-1];
            $res['status']=0;
            $res['msg']='';
            $res['data']=$res1;
            return json($res);
        }

    }
    //每月各类纸张采购量、使用量-特种纸采购量
    public function tzz(){
        $result=$this->month_paperdun();
        $result1= $this->tt12new();
        if(empty($result) || empty($result1)) {
            return json([
                'status' => 0,
                'msg' => '没有找到数据',
                'data' => [
                    'categories' => [''],
                    'series' => [
                        ['name' => '采购量', 'data' => [0]],
                        ['name' => '使用量', 'data' => [0]]
                    ]
                ]
            ]);
        }else{
            foreach($result as $k=>$v){
                $list['categories'][]=$k;
                $list['series'][0]['name']='特种纸';
                $list['series'][0]['data'][]=round($v['特种纸']);
            }
            foreach($result1 as $k=>$v){
                $list1['categories'][]=$k;
                $list1['series'][0]['name']='特种纸';
                $list1['series'][0]['data'][]=round($v['特种纸']);
            }
            $data=input('post.');
            if(isset($data['dependence'])){
                $dependence=$data['dependence'];
            }else{
                $dependence['item']['value']=date('m',time());
            }
            $i=(int)$dependence['item']['value'];
            $res1['categories'][]=$dependence['item']['value'];
            $res1['series'][0]['name']='采购量';
            $res1['series'][0]['data'][]=$list['series'][0]['data'][$i-1];
            $res1['series'][1]['name']='使用量';
            $res1['series'][1]['data'][]=$list1['series'][0]['data'][$i-1];
            $res['status']=0;
            $res['msg']='';
            $res['data']=$res1;
            return json($res);
        }

    }
    //每月各类纸张采购量、使用量-本白双胶纸采购量
    public function bbsj(){
        $result=$this->month_paperdun();
        $result1= $this->tt12new();
        if(empty($result) || empty($result1)) {
            return json([
                'status' => 0,
                'msg' => '没有找到数据',
                'data' => [
                    'categories' => [''],
                    'series' => [
                        ['name' => '采购量', 'data' => [0]],
                        ['name' => '使用量', 'data' => [0]]
                    ]
                ]
            ]);
        }else{
            foreach($result as $k=>$v){
                $list['categories'][]=$k;
                $list['series'][0]['name']='本白双胶纸';
                $list['series'][0]['data'][]=round($v['本白双胶纸']);
            }
            foreach($result1 as $k=>$v){
                $list1['categories'][]=$k;
                $list1['series'][0]['name']='本白双胶纸';
                $list1['series'][0]['data'][]=round($v['本白双胶纸']);
            }
            $data=input('post.');
            if(isset($data['dependence'])){
                $dependence=$data['dependence'];
            }else{
                $dependence['item']['value']=date('m',time());
            }
            $i=(int)$dependence['item']['value'];
            $res1['categories'][]=$dependence['item']['value'];
            $res1['series'][0]['name']='采购量';
            $res1['series'][0]['data'][]=$list['series'][0]['data'][$i-1];
            $res1['series'][1]['name']='使用量';
            $res1['series'][1]['data'][]=$list1['series'][0]['data'][$i-1];
            $res['status']=0;
            $res['msg']='';
            $res['data']=$res1;
            return json($res);
        }

    }
    //每月各类纸张采购量、使用量-有光双面铜版纸采购量
    public function ygsm(){
        $result=$this->month_paperdun();
        $result1= $this->tt12new();
        if(empty($result) || empty($result1)) {
            return json([
                'status' => 0,
                'msg' => '没有找到数据',
                'data' => [
                    'categories' => [''],
                    'series' => [
                        ['name' => '采购量', 'data' => [0]],
                        ['name' => '使用量', 'data' => [0]]
                    ]
                ]
            ]);
        }else{
            foreach($result as $k=>$v){
                $list['categories'][]=$k;
                $list['series'][0]['name']='有光双面铜版纸';
                $list['series'][0]['data'][]=round($v['有光双面铜版纸']);
            }
            foreach($result1 as $k=>$v){
                $list1['categories'][]=$k;
                $list1['series'][0]['name']='有光双面铜版纸';
                $list1['series'][0]['data'][]=round($v['有光双面铜版纸']);
            }
            $data=input('post.');
            if(isset($data['dependence'])){
                $dependence=$data['dependence'];
            }else{
                $dependence['item']['value']=date('m',time());
            }
            $i=(int)$dependence['item']['value'];
            $res1['categories'][]=$dependence['item']['value'];
            $res1['series'][0]['name']='采购量';
            $res1['series'][0]['data'][]=$list['series'][0]['data'][$i-1];
            $res1['series'][1]['name']='使用量';
            $res1['series'][1]['data'][]=$list1['series'][0]['data'][$i-1];
            $res['status']=0;
            $res['msg']='';
            $res['data']=$res1;
            return json($res);
        }

    }

    //2.4全年前十供应商采购量->接口调用
    public function year_supplier(){
        $redis = redis();
        $list = json_decode($redis->get(md5('er_lst')),true);
        $r_list = [];
        //按照日期分类分组求和
        foreach ($list as $item) {
            $name = $item['公司'];
            $key = md5($name);
            $t = [];
            $t['name'] = $name;
            $t['number'] =   floatval($item['namount']);//数量
            $t['sumprice'] = floatval($item['含税金额']);//金额
            $t['unit'] =     trim($item['单位']);//单位
            $t['nhss'] =     $item['nhss'];//采购编号
            $dweight = erp_price($t);
            $dprice =  $t['sumprice'];
            if (isset($r_list[$key])) {
                $r_list[$key]['name'] = $name;//
                $r_list[$key]['dun'] += $dweight;//吨
                $r_list[$key]['jine'] += $dprice;//金额
            } else {
                $r_list[$key]['name'] = $name;//
                $r_list[$key]['dun'] = $dweight;//吨
                $r_list[$key]['jine'] = $dprice;//金额
            }
        }
        $sort_arr = [];//按金额
        $sort_arr2 = [];//按吨
        foreach($r_list as $key=>$value){
            $sort_arr[$key] = $r_list[$key]['jine'];
            $sort_arr2[$key] = $r_list[$key]['dun'];
        }
        //排序
        array_multisort($sort_arr,SORT_DESC,$r_list);
        //截取前10
        $r_list = array_splice($r_list,0,10);
        $r_list = array_values($r_list);
        $result['columns']=[
            ['name'=>'供应商','id'=>'name','width'=>'50','autoWrap'=>"true",'textAlign'=>'left'],
            ['name'=>'总量(吨)','id'=>'dun','width'=>'25','autoWrap'=>"true",'textAlign'=>'left'],
            ['name'=>'总金额(万元)','id'=>'jine','width'=>'25','autoWrap'=>"true",'textAlign'=>'left']
        ];
        if (empty($r_list)) {
            $r_list = [
                ['name' => '', 'dun' => 0, 'jine' => 0]
            ];

        }
        $delete = '有限公司';
        foreach($r_list as $k=>$v){
            if(strpos($v['name'],$delete)){
                $v['name'] = substr_replace($v['name'],'',strpos($v['name'],$delete),strlen($delete));
            }
            $result['rows'][$k]['name']=$v['name'];
            $result['rows'][$k]['dun']=floor($v['dun']);
            $result['rows'][$k]['jine']=round($v['jine']/10000);
        }
        $res['status']=0;
        $res['msg']='';
        $res['data']=$result;
        return json($res);
    }

    //2.5全年各类纸张采购量（吨）->接口调用
    public function year_supplier_procurement(){
        $redis = redis();
        $list = json_decode($redis->get(md5('er_lst')),true);
        if(empty($list)) {
            return json([
                'status' => 0,
                'msg' => '没有找到数据',
                'data' => [
                    'categories' => ['无'],
                    'series' => [
                        ['name' => '采购量', 'data' => [0]],
                        ['name' => '金额', 'data' => [0]]
                    ]
                ]
            ]);
        }
        $mainCate = getcateinfo();
        foreach ($list as $item)
        {
            $catename = getCateName($item['czgmc']);
            //是否属于主要纸张
            if(!in_array($catename,$mainCate)){
                continue;
            }
            $key = md5($catename);
            $t = [];
            $t['catename'] = $catename;//纸张名称
            $t['dweight'] =   $item['数量（吨）'];//克重
            $t['sumprice'] = $item['含税金额'];//金额
            $dweight = $t['dweight'];
            $dprice =  $t['sumprice'] ;
            if (isset($r_list[$key])) {
                $r_list[$key]['catename'] = $catename;//
                $r_list[$key]['dun'] += $dweight;//吨
                $r_list[$key]['jine'] += $dprice;//金额
            } else {
                $r_list[$key]['catename'] = $catename;//
                $r_list[$key]['dun'] = $dweight;//吨
                $r_list[$key]['jine'] = $dprice;//金额
            }
        }
        //按吨位排序
        $sort_arr = [];
        foreach($r_list as $k=>$value){
            $sort_arr[$k] = $r_list[$k]['dun'];
        }
        //排序
        array_multisort($sort_arr,SORT_DESC,$r_list);
        //截取前10
        $r_list = array_splice($r_list,0,10);
        $r_list = array_values($r_list);
//        echo "全年各类纸张采购量";echo "<pre>";print_r($r_list);echo "</pre>";die;
        $result =[];

        foreach($r_list as $k=>$v){
            $result['categories'][$k] =$v['catename'];
            $result['series'][0]['name']='采购量';
            $result['series'][1]['name']='金额';
            $result['series'][0]['data'][$k]=round($v['dun']);
            $result['series'][1]['data'][$k]=round($v['jine']/10000);
        }

        $res['status']=0;
        $res['msg']='';
        $res['data']=$result;
        return json($res);
    }

    /**
     * 第二屏第二页
     */
    //2.3-2每月各类纸张采购均价（第二页）->接口调用
    public function month_paperjie(){
        $redis = redis();
        $list = json_decode($redis->get(md5('qer_lst')),true);
        // halt($list);
        $mainCate  = getcateinfo();
        foreach ($list as $item){
            $catename = getCateName($item['czgmc']);
            //是否属于主要纸张
            if(!in_array($catename,$mainCate)){
                continue;
            }
            $month = date('Ym',strtotime($item['darrival'] ));
            $key = md5($catename.$month);
            $t = [];
            $t['dtime'] =    $month;//月份
            $t['catename'] = $catename;//纸张名称
            $t['dweight'] =   floatval($item['数量（吨）']);//
            $t['sumprice'] = floatval($item['含税金额']);//金额
            $dweight = $t['dweight'];
            $dprice =  $t['sumprice'] ;
            if (isset($r_list[$key])) {
                $r_list[$key]['catename'] = $catename;//
                $r_list[$key]['month'] = $month;//
                $r_list[$key]['dun'] += $dweight;//吨
                $r_list[$key]['jine'] += $dprice;//金额
            } else {
                $r_list[$key]['catename'] = $catename;//
                $r_list[$key]['month'] = $month;//
                $r_list[$key]['dun'] = $dweight;//吨
                $r_list[$key]['jine'] = $dprice;//金额
            }
        }
        $c_list = [];
        foreach($r_list as $item){
            // $c_list[$item['month']][$item['catename']] = round($item['jine']/$item['dun']);
            $c_list[$item['month']][$item['catename']] = floor($item['jine']/$item['dun']);
        }
        //补全分类
        foreach ($c_list as &$val) {
            $val = bucateinfo($val);
            arsort($val);
        }
        return $c_list;
    }
    //每月各类纸张采购均价-高白双胶
    public function gbsjjun(){
        $name = '高白双胶纸';
        $result = $this->month_paperjie();
        $list = [];
        $currentYear = date("Y"); // 当前年份
        $currentMonth = date("m"); // 当前月份
        $previousYear = $currentYear - 1; // 上一年
        // 初始化类别
        $list['categories'] = ['01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12'];
        // 初始化上一年的数据数组，填充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, (int)$currentMonth, 0); // 注意这里是 currentMonth，不是 currentMonth - 1
        $prevdata = 0; // 初始化前一个数据点的值
        foreach ($result as $k => $v) {
            $year = substr($k, 0, 4);
            $month = substr($k, 4, 2);
            if ($v[$name] == 0) {
                $v[$name] = $prevdata;
            }
            if ($year == $currentYear && $month < $currentMonth) {
                // 如果是当前年份，并且月份小于当前月份，则赋值
                $index = (int)$month - 1;
                $list['series'][1]['data'][$index] = $v[$name];
            } elseif ($year == $previousYear) {
                // 如果是上一年的数据，直接赋值
                $index = (int)$month - 1;
                $list['series'][0]['data'][$index] = $v[$name];
            }
            // 更新前一个数据点的值
            $prevdata = $v[$name];
        }
        $res['status'] = 0;
        $res['msg'] = '';
        $res['data'] = $list;
        return json($res);
    }
    //每月各类纸张采购均价-纯雅纸均价
    public function cyzjun(){
        $name = '纯雅纸';
        $result = $this->month_paperjie();
        $list = [];
        $i=0;
        $j=0;
        $prevdata=0;
        $list['categories']=['01','02','03','04','05','06','07','08','09','10','11','12'];
        foreach ($result as $k => $v) {
            if($k<=date('Ym',time())&$v[$name]==0){
                $v[$name]=$prevdata;
            }
            $currentYearMonth = date("Y") . "01";
            if($k>=$currentYearMonth){
                $list['series'][1]['name']= date("Y").'年';
                $list['series'][1]['data'][$j]=$v[$name];
                $j++;
            }else{
                $list['series'][0]['name']=date("Y", strtotime("-1 year")).'年';
                $list['series'][0]['data'][$i]=$v[$name];
                $i++;
            }
            $prevdata=$v[$name];
        }
        $res['status'] = 0;
        $res['msg'] = '';
        $res['data'] = $list;
        return json($res);
    }
    //每月各类纸张采购均价-纯质纸均价
    public function czzjun(){
        $name = '纯质纸';
        $result = $this->month_paperjie();
        $list = [];
        $i=0;
        $j=0;
        $prevdata=0;
        $list['categories']=['01','02','03','04','05','06','07','08','09','10','11','12'];
        foreach ($result as $k => $v) {
            if($k<=date('Ym',time())&$v[$name]==0){
                $v[$name]=$prevdata;
            }
            $currentYearMonth = date("Y") . "01";
            if($k>=$currentYearMonth){
                $list['series'][1]['name']= date("Y").'年';
                $list['series'][1]['data'][$j]=$v[$name];
                $j++;
            }else{
                $list['series'][0]['name']=date("Y", strtotime("-1 year")).'年';
                $list['series'][0]['data'][$i]=$v[$name];
                $i++;
            }
            $prevdata=$v[$name];
        }
        $res['status'] = 0;
        $res['msg'] = '';
        $res['data'] = $list;
        return json($res);
    }
    //每月各类纸张采购均价-全灰板均价
    public function qhbjun(){
        $name = '全灰板';
        $result = $this->month_paperjie();
        $list = [];
        $i=0;
        $j=0;
        $prevdata=0;
        $list['categories']=['01','02','03','04','05','06','07','08','09','10','11','12'];
        foreach ($result as $k => $v) {
            if($k<=date('Ym',time())&$v[$name]==0){
                $v[$name]=$prevdata;
            }
            $currentYearMonth = date("Y") . "01";
            if($k>=$currentYearMonth){
                $list['series'][1]['name']= date("Y").'年';
                $list['series'][1]['data'][$j]=$v[$name];
                $j++;
            }else{
                $list['series'][0]['name']=date("Y", strtotime("-1 year")).'年';
                $list['series'][0]['data'][$i]=$v[$name];
                $i++;
            }
            $prevdata=$v[$name];
        }
        $res['status'] = 0;
        $res['msg'] = '';
        $res['data'] = $list;
        return json($res);
    }
    //每月各类纸张采购均价-白卡纸均价
    public function bkzjun(){
        $name = '白卡纸';
        $result = $this->month_paperjie();
        $list = [];
        $i=0;
        $j=0;
        $prevdata=0;
        $list['categories']=['01','02','03','04','05','06','07','08','09','10','11','12'];
        foreach ($result as $k => $v) {
            if($k<=date('Ym',time())&$v[$name]==0){
                $v[$name]=$prevdata;
            }
            $currentYearMonth = date("Y") . "01";
            if($k>=$currentYearMonth){
                $list['series'][1]['name']= date("Y").'年';
                $list['series'][1]['data'][$j]=$v[$name];
                $j++;
            }else{
                $list['series'][0]['name']=date("Y", strtotime("-1 year")).'年';
                $list['series'][0]['data'][$i]=$v[$name];
                $i++;
            }
            $prevdata=$v[$name];
        }
        $res['status'] = 0;
        $res['msg'] = '';
        $res['data'] = $list;
        return json($res);
    }
    //每月各类纸张采购均价-轻型纸均价
    public function qxzjun(){
        $name = '轻型纸';
        $result = $this->month_paperjie();
        $list = [];
        $i=0;
        $j=0;
        $prevdata=0;
        $list['categories']=['01','02','03','04','05','06','07','08','09','10','11','12'];
        foreach ($result as $k => $v) {
            if($k<=date('Ym',time())&$v[$name]==0){
                $v[$name]=$prevdata;
            }
            $currentYearMonth = date("Y") . "01";
            if($k>=$currentYearMonth){
                $list['series'][1]['name']= date("Y").'年';
                $list['series'][1]['data'][$j]=$v[$name];
                $j++;
            }else{
                $list['series'][0]['name']=date("Y", strtotime("-1 year")).'年';
                $list['series'][0]['data'][$i]=$v[$name];
                $i++;
            }
            $prevdata=$v[$name];
        }
        $res['status'] = 0;
        $res['msg'] = '';
        $res['data'] = $list;
        return json($res);
    }
    //每月各类纸张采购均价-亚光双面铜版纸均价
    public function ygsmtbjun(){
        $name = '亚光双面铜版纸';
        $result = $this->month_paperjie();
        $list = [];
        $i=0;
        $j=0;
        $prevdata=0;
        $list['categories']=['01','02','03','04','05','06','07','08','09','10','11','12'];
        foreach ($result as $k => $v) {
            if($k<=date('Ym',time())&$v[$name]==0){
                $v[$name]=$prevdata;
            }
            $currentYearMonth = date("Y") . "01";
            if($k>=$currentYearMonth){
                $list['series'][1]['name']= date("Y").'年';
                $list['series'][1]['data'][$j]=$v[$name];
                $j++;
            }else{
                $list['series'][0]['name']=date("Y", strtotime("-1 year")).'年';
                $list['series'][0]['data'][$i]=$v[$name];
                $i++;
            }
            $prevdata=$v[$name];
        }
        $res['status'] = 0;
        $res['msg'] = '';
        $res['data'] = $list;
        return json($res);
    }
    //每月各类纸张采购均价-轻涂纸均价
    public function qtzjun(){
        $name = '轻涂纸';
        $result = $this->month_paperjie();
        $list = [];
        $i=0;
        $j=0;
        $prevdata=0;
        $list['categories']=['01','02','03','04','05','06','07','08','09','10','11','12'];
        foreach ($result as $k => $v) {
            if($k<=date('Ym',time())&$v[$name]==0){
                $v[$name]=$prevdata;
            }
            $currentYearMonth = date("Y") . "01";
            if($k>=$currentYearMonth){
                $list['series'][1]['name']= date("Y").'年';
                $list['series'][1]['data'][$j]=$v[$name];
                $j++;
            }else{
                $list['series'][0]['name']=date("Y", strtotime("-1 year")).'年';
                $list['series'][0]['data'][$i]=$v[$name];
                $i++;
            }
            $prevdata=$v[$name];
        }
        $res['status'] = 0;
        $res['msg'] = '';
        $res['data'] = $list;
        return json($res);
    }
    //每月各类纸张采购均价-彩画纸均价
    public function chzjun(){
        $name = '彩画纸';
        $result = $this->month_paperjie();
        $list = [];
        $i=0;
        $j=0;
        $prevdata=0;
        $list['categories']=['01','02','03','04','05','06','07','08','09','10','11','12'];
        foreach ($result as $k => $v) {
            if($k<=date('Ym',time())&$v[$name]==0){
                $v[$name]=$prevdata;
            }
            $currentYearMonth = date("Y") . "01";
            if($k>=$currentYearMonth){
                $list['series'][1]['name']= date("Y").'年';
                $list['series'][1]['data'][$j]=$v[$name];
                $j++;
            }else{
                $list['series'][0]['name']=date("Y", strtotime("-1 year")).'年';
                $list['series'][0]['data'][$i]=$v[$name];
                $i++;
            }
            $prevdata=$v[$name];
        }
        $res['status'] = 0;
        $res['msg'] = '';
        $res['data'] = $list;
        return json($res);
    }
    //每月各类纸张采购均价-特种纸均价
    public function tzzjun(){
        $name = '特种纸';
        $result = $this->month_paperjie();
        $list = [];
        $i=0;
        $j=0;
        $prevdata=0;
        $list['categories']=['01','02','03','04','05','06','07','08','09','10','11','12'];
        foreach ($result as $k => $v) {
            if($k<=date('Ym',time())&$v[$name]==0){
                $v[$name]=$prevdata;
            }
            $currentYearMonth = date("Y") . "01";
            if($k>=$currentYearMonth){
                $list['series'][1]['name']= date("Y").'年';
                $list['series'][1]['data'][$j]=$v[$name];
                $j++;
            }else{
                $list['series'][0]['name']=date("Y", strtotime("-1 year")).'年';
                $list['series'][0]['data'][$i]=$v[$name];
                $i++;
            }
            $prevdata=$v[$name];
        }
        $res['status'] = 0;
        $res['msg'] = '';
        $res['data'] = $list;
        return json($res);
    }
    //每月各类纸张采购均价-本白双胶纸均价
    public function bbsjjun(){
        $name = '本白双胶纸';
        $result = $this->month_paperjie();
        $list = [];
        $i=0;
        $j=0;
        $prevdata=0;
        $list['categories']=['01','02','03','04','05','06','07','08','09','10','11','12'];
        foreach ($result as $k => $v) {
            if($k<=date('Ym',time())&$v[$name]==0){
                $v[$name]=$prevdata;
            }
            $currentYearMonth = date("Y") . "01";
            if($k>=$currentYearMonth){
                $list['series'][1]['name']= date("Y").'年';
                $list['series'][1]['data'][$j]=$v[$name];
                $j++;
            }else{
                $list['series'][0]['name']=date("Y", strtotime("-1 year")).'年';
                $list['series'][0]['data'][$i]=$v[$name];
                $i++;
            }
            $prevdata=$v[$name];
        }
        $res['status'] = 0;
        $res['msg'] = '';
        $res['data'] = $list;
        return json($res);
    }
    //每月各类纸张采购均价-有光双面铜版纸均价
    public function ygsmtjun(){
        $name = '有光双面铜版纸';
        $result = $this->month_paperjie();
        $list = [];
        $i=0;
        $j=0;
        $prevdata=0;
        $list['categories']=['01','02','03','04','05','06','07','08','09','10','11','12'];
        foreach ($result as $k => $v) {
            if($k<=date('Ym',time())&$v[$name]==0){
                $v[$name]=$prevdata;
            }
            $currentYearMonth = date("Y") . "01";
            if($k>=$currentYearMonth){
                $list['series'][1]['name']= date("Y").'年';
                $list['series'][1]['data'][$j]=$v[$name];
                $j++;
            }else{
                $list['series'][0]['name']=date("Y", strtotime("-1 year")).'年';
                $list['series'][0]['data'][$i]=$v[$name];
                $i++;
            }
            $prevdata=$v[$name];
        }
        $res['status'] = 0;
        $res['msg'] = '';
        $res['data'] = $list;
        return json($res);
    }

    /**
     * 三、库存情况
     *
     * 表说明
     * ppin->库存表
     * ppindetail->库存详情表
     * dzzzgg->纸张规格表
     * 表的关联
     * ppin.cbillcode = ppindetail.cbillcode
     * ppindetail.cpcode = dzzzgg.cpcode
     * 字段说明
     * ppin.darrival 时间
     * ppin.cowner 本厂->厂料 其他代表各出版社
     * ppindetail.namount 数量
     * ppindetail.cpname 名称（关联纸张规格表，进行分类）
     * ppindetail.cunit 单位（令、张、公斤）
     *令（令数/顿折令(dzzzgg.nhss)） = 公斤
     *张（张数/500/顿折令(dzzzgg.nhss)） = 公斤
     * dzzzgg.czgmc 纸张名称分类
     * dzzzgg.chj 纸张详情名称
     */

    //3.库存情况->缓存调用
    public  function tt3new(){
        //链接定义 Redis
        $redis = redis();
        $redis_key = md5('tt3new');
        //超过180天以上总量
        $sql = "SELECT *
  FROM   (SELECT NN.cOwner                                          货主,
                 NN.库龄,
                 ROUND(SUM(NN.数量（吨）), 2)  数量（吨）
          FROM   (SELECT 
    cPlaceName,
    cPaperCode,
    cPaperName,
    ID_Place,
    cRoleName,
    cBatchCode,
    cUnit,
    SUM(nAmount) AS nAmount,
    CASE
        WHEN cunit = '公斤' THEN SUM(nAmount) / 1000
        WHEN cunit = '令' THEN 
            CASE
                WHEN dzzzgg.nhss = 0 THEN SUM(nAmount) / 20
                ELSE SUM(nAmount) / dzzzgg.nhss
            END
        WHEN cunit = '张' THEN 
            CASE
                WHEN dzzzgg.nhss = 0 THEN SUM(nAmount) / 500 / 1.5
                ELSE SUM(nAmount) / 500 / dzzzgg.nhss
            END
        ELSE 0
    END AS '数量（吨）',
    SUM(nNetAmount) AS nNetAmount,
    CASE 
        WHEN cPaperType = '卷筒' AND nAmount <> 0 THEN COUNT(CASE WHEN nAmount = 0 THEN NULL ELSE cPaperCode END)
        ELSE 0
    END AS iCount,
    iStoreAge,
    CASE
        WHEN cowner = '本厂' THEN 
            CASE
                WHEN iStoreAge > 180 THEN '六个月以上'
                ELSE '六个月以内'
            END
        ELSE ' '
    END AS 库龄,
    cStatus,
    cOwner,
    cPaperType,
    dzzzgg.nhss
FROM 
    (SELECT
    IFNULL( a.id, 0 ) AS id,
    ttPlace.cPaperCode,
    IFNULL( a.cBatchCode, '' ) AS cBatchCode,
    ttPlace.id_place,
    IFNULL( c.cbzdw, '' ) AS cUnit,
    IFNULL( a.nAmount, 0 ) AS nAmount,
    IFNULL( a.nNetAmount, 0 ) AS nNetAmount,
    IFNULL( a.nAvailAmount, 0 ) AS nAvailAmount,
    IFNULL( a.nLength, 0 ) AS nLength,
    IFNULL( a.ID_mxcpck, 0 ) AS ID_mxcpck,
    IFNULL( c.clb, '' ) AS cPaperType,
    IFNULL( DATEDIFF( NOW( ), b.dDate ), 0 ) AS iStoreAge,
    IFNULL( CASE b.iStatus WHEN 0 THEN '完好' WHEN 1 THEN '残破' WHEN 2 THEN '开裁' WHEN 3 THEN '换纸' END, '' ) AS cStatus,
    IFNULL( b.cOwner, '' ) AS cOwner,
    IFNULL( b.iExclusive, 0 ) AS iExclusive,
    IFNULL( c.chj, '' ) AS cPaperName,
    IFNULL( c.nkz, 0 ) AS nkz,
    IFNULL( c.cgg, '' ) AS cgg,
    c.ccd,
    c.clb,
    IFNULL( c.czgmc, '' ) AS czgmc,
    c.nstate,
    IFNULL( c.cOldSystemNumber, '' ) AS cOldSystemNumber,
    IFNULL( c.nThickness, 0 ) AS nThickness,
    IFNULL( d.cPlaceShowName, '' ) AS cPlaceName,
    d.iRoleID,
    IFNULL( d.cRoleName, '' ) AS cRoleName,
    d.ckfmc,
    d.ckfjc,
    d.ikwxh,
    d.ikwlx,
    d.iForbid,
    d.ikfsx,
    d.iFlag,
    d.iStoreForbid,
    d.iProperty 
  FROM
    (
  SELECT
    b.cPaperCode,
    b.id_place 
  FROM
    ( SELECT MAX( id ) AS id FROM ppDetailPlace GROUP BY cPaperCode, id_place ) AS a
    INNER JOIN ppDetailPlace AS b ON b.id = a.id 
    ) AS ttPlace
    LEFT OUTER JOIN (SELECT   a.ID, a.ikwxh, a.ckwmc, a.ikfid, a.ixnID, a.ikwlx, a.ipbrl, a.ijtrl, a.cbz, a.iFlag, a.iForbid, 
                  c.ckfjc  + '/' + a.ckwmc AS cPlaceShowName, a.cPlaceCode, a.iProperty, b.iRoleID, b.cRoleName, c.ckfmc, c.ckfjc, c.ikflb, 
                  c.ikfsx, c.iForbid AS iStoreForbid, c.ibmID, c.iPaper, c.iStuff, c.iHalf, c.iFinish, c.iWaste, c.iTemp, c.iEntrust, 
                  IFNULL(c.iPSStuff, 0) AS iPSStuff, c.iRollPaper_WeightRoll, c.iSample, c.iSampleLibrary
  FROM      dzkw AS a LEFT OUTER JOIN
                      (SELECT   aa.ikfID, aa.iRoleID, bb.cRoleName
                       FROM      dzStoreRole AS aa INNER JOIN
                                           (SELECT   ID, iRoleNO, cRoleName, cRemark, iType
                                            FROM     ppRole
                                            WHERE   (iType = 0)) AS bb ON aa.iRoleID = bb.ID) AS b ON a.ikfid = b.ikfID AND 
                  a.iForbid = 0 LEFT OUTER JOIN
                  dzkf AS c ON a.ikfid = c.ID AND c.iForbid = 0) AS d ON ttPlace.id_place = d.ID
    LEFT OUTER JOIN dzzzgg AS c ON ttPlace.cPaperCode = c.cbh
    LEFT OUTER JOIN (
  SELECT
    id,
    cPaperCode,
    cBatchCode,
    id_Place,
    cUnit,
    nAmount,
    nAmount AS nNetAmount,
    nAvailAmount,
    0 AS nLength,
    ID_mxcpck 
  FROM
    ppSubstanceDetail UNION ALL
  SELECT
    id,
    cpapercode,
    cbatchcode,
    id_Place,
    cUnit,
    namount,
    namount - nBrokenAmount AS nNetAmount,
    ( CASE WHEN iFlag = 0 THEN IFNULL( namount, 0 ) ELSE 0 END ) AS nAvailAmount,
    nLength,
    ID_mxcpck 
  FROM
    ppSubstanceDetailRoll 
    ) AS a ON a.cPaperCode = ttPlace.cPaperCode 
    AND a.id_Place = ttPlace.id_place
    LEFT OUTER JOIN ppBatchProp AS b ON a.cBatchCode = b.cBatchCode 
    AND a.cPaperCode = b.cPaperCode) kc
LEFT JOIN dzzzgg ON dzzzgg.cbh = kc.cPaperCode
WHERE 
    1 = 1
    -- AND cRoleName LIKE '%卷筒纸库管%'
    AND nAmount <> 0
    -- AND cOwner = '求是杂志社'
    -- AND cPaperCode ='J128078072033'
GROUP BY 
    cPaperName,
    cPaperCode,
    ID_Place,
    cPlaceName,
    cRoleName,
    cBatchCode,
    cUnit,
    iStoreAge,
    cStatus,
    cOwner,
    cPaperType,
    namount,
    dzzzgg.nhss) NN
          GROUP  BY NN.cOwner,
                    NN.库龄)KC
        ORDER  BY    CASE
        WHEN KC.库龄 = '六个月以上' THEN 1
        WHEN KC.库龄 = '六个月以内' THEN 2
        ELSE 3
    END,
    KC.数量（吨） DESC";

//        ORDER  BY    CASE
//        WHEN KC.库龄 = '六个月以上' THEN 1
//        WHEN KC.库龄 = '六个月以内' THEN 2
//        ELSE 3
//    END,
//    KC.数量（吨） DESC

        $list = Db::query($sql);
        $list = array_splice($list,0,10);
        //将查询结果存入 Redis 缓存中
        $redis->set($redis_key, json_encode($list));
        return json($list);
    }
    //库存情况->接口调用
    public function inventorydgz(){
        $redis = redis();
        $result = json_decode($redis->get(md5('tt3new')),true);
        if (empty($result)) {
            // 返回一个包含默认字段但数据为空的数组
            return json([
                'status' => 0,
                'msg' => '没有找到数据',
                'data' => [
                    'columns' => [
                        ['name'=>'货主','id'=>'cOwnerName','width'=>'40','autoWrap'=>"true",'textAlign'=>'left'],
                        ['name'=>'库龄','id'=>'year','width'=>'25','autoWrap'=>"true",'textAlign'=>'left'],
                        ['name'=>'数量(吨)','id'=>'nAmount','width'=>'25','autoWrap'=>"true",'textAlign'=>'left']
                    ],
                    'rows' => [
                        ['cOwnerName' => '', 'year' => '', 'nAmount' => '']
                    ]
                ]
            ]);
        }
        $list['columns']=[
            ['name'=>'货主','id'=>'cOwnerName','width'=>'40','autoWrap'=>"true",'textAlign'=>'left'],
            ['name'=>'库龄','id'=>'year','width'=>'25','autoWrap'=>"true",'textAlign'=>'left'],
            ['name'=>'数量(吨)','id'=>'nAmount','width'=>'25','autoWrap'=>"true",'textAlign'=>'left']
        ];
        $i=0;
        $delete = '有限公司';
        foreach($result as $v){
            if(strpos($v['货主'],$delete)){
                $v['货主'] = substr_replace($v['货主'],'',strpos($v['货主'],$delete),strlen($delete));
            }
            $list['rows'][$i]['cOwnerName']=$v['货主'];
            $list['rows'][$i]['year']=$v['库龄'];
            $list['rows'][$i]['nAmount']=round($v['数量（吨）']);
            $i++;
        }
        $res['status']=0;
        $res['msg']='';
        $res['data']=$list;
        return json($res);
    }

    /**
     * 四、全年辅料采购量
     */

    //全年辅料采购量->缓存调用
    public function accessories_number(){
        //链接定义 Redis
        $redis = redis();
        $redis_key = md5('accessories_number');
        //辅料教材采购数据
        $sql = "SELECT *
FROM   (SELECT DD.大类名称,
               DD.供应商,
               ROUND(SUM(DD.数量), 2) AS 数量,
               DD.单位,
               ROUND(SUM(DD.金额)/10000, 2) AS '金额（万元）'
        FROM   (SELECT NN.cdlmc AS 大类名称,
                       NN.cclmc AS 材料名称,
                       NN.cywdwmc AS 供应商,
                       NN.nbzsl AS 数量,
                       NN.nTaxBuyPrice AS 单价,
                       NN.nTaxPrice,
                       NN.cdw AS 单位,
                       ROUND(NN.nTaxMoney, 2) AS 金额
                FROM   (SELECT b.ixtrkdbh,
                               b.crkdbh,
                               a.cclbh,
                               a.cclmc,
                               a.cdw,
                               a.cgg,
                               c.ccd,
                               a.nbzsl,
                               a.nbzdj,
                               a.nbzje,
                               b.cywdwmc,
                               -- Linkstr(a.imxid) AS cPlace,
                               a.cph,
                               b.dzdrq,
                               b.cbz,
                               b.cczymc,
                               b.cDeliveryCode,
                               CASE a.cBuyUnit
                                 WHEN '' THEN a.cdw
                                 ELSE a.cBuyUnit
                               END AS cBuyUnit,
                               CASE a.cBuyUnit
                                 WHEN '' THEN a.nbzdj
                                 ELSE a.nBuyPrice
                               END AS nBuyPrice,
                               c.cycbh,
                               d.nTaxBuyPrice,
                               d.nTaxPrice,
                               IFNULL(d.nTaxPrice, 0) * a.nbzsl AS nTaxMoney,
                               b.iPlant,
                               a.cBillCode_Buy,
                               e.cBillCode AS cInNoticeCode,
                               b.cBillCode_Source,
                               b.iSourceType,
                               c.cdlmc,
                               c.cxlmc,
                               a.cCode_ProductOrder,
                               a.cCode_AProduct,
                               a.cName_AProduct,
                               a.cVer_AProduct,
                               a.ID_Request,
                               a.cCode_JobOrder
                        FROM   mxrkcl a
                               LEFT JOIN zbrk b ON a.ixtrkdbh = b.ixtrkdbh
                               LEFT JOIN ptkccl c ON a.cclbh = c.cclbm
                               LEFT JOIN stInNoticeDetail d ON a.cInNoticeCode = d.cBillCode
                                   AND a.cInNoticeStuffCode = d.cStuffCode
                               LEFT JOIN stInNotice e ON e.cBillCode = d.cBillCode
                               LEFT JOIN skBuy f ON f.id = e.ID_skBuy
                        WHERE  b.bsw = 1
                               AND 1 = 1
                               AND b.iState = 2
                               AND b.bth = 0 
                               AND b.drkrq BETWEEN '{$this->start_time()}' AND '{$this->end_time()}'
                ) NN
        ) DD
        GROUP  BY DD.大类名称,
                  DD.供应商,
                  DD.单位
) AA
ORDER  BY AA.`金额（万元）` DESC;";
        //执行查询结果
        $res = Db::query($sql);
        //将查询结果存入 Redis 缓存中
        $redis->set($redis_key, json_encode($res));
        echo date("Y-m-d H:i:s").'   存进去了';
        return $res;
    }
    //全年辅料采购量->接口调用
    public function accessories(){
        $redis = redis();
        $result = json_decode($redis->get(md5('accessories_number')),true);

        if (empty($result)) {
            // 返回一个包含默认字段但数据为空的数组
            return json([
                'status' => 0,
                'msg' => '没有找到数据',
                'data' => [
                    'columns' => [
                        ['name'=>'大类名称','id'=>'cdlmc','width'=>'15','autoWrap'=>"true",'textAlign'=>'left'],
                        ['name'=>'供应商','id'=>'cdwmc','width'=>'28','autoWrap'=>"true",'textAlign'=>'left'],
                        ['name'=>'数量','id'=>'nAmount','width'=>'20','autoWrap'=>"true",'textAlign'=>'left'],
                        ['name'=>'单位','id'=>'cdw','width'=>'15','autoWrap'=>"true",'textAlign'=>'left'],
                        ['name'=>'金额（万元）','id'=>'nMoney','width'=>'22','autoWrap'=>"true",'textAlign'=>'left']
                    ],
                    'rows' => [
                        ['cdlmc' => '', 'cdwmc' => '', 'nAmount' => '', 'cdw' => '', 'nMoney' => '']
                    ]
                ]
            ]);
        }
        $list=[];
        $list['columns']=[
            ['name'=>'大类名称','id'=>'cdlmc','width'=>'15','autoWrap'=>"true",'textAlign'=>'left'],
            ['name'=>'供应商','id'=>'cdwmc','width'=>'28','autoWrap'=>"true",'textAlign'=>'left'],
            ['name'=>'数量','id'=>'nAmount','width'=>'20','autoWrap'=>"true",'textAlign'=>'left'],
            ['name'=>'单位','id'=>'cdw','width'=>'15','autoWrap'=>"true",'textAlign'=>'left'],
            ['name'=>'金额（万元）','id'=>'nMoney','width'=>'22','autoWrap'=>"true",'textAlign'=>'left']
        ];
        $i=0;
        foreach($result as $v){
            $list['rows'][$i]['cdlmc']=trim($v['大类名称']);
            $list['rows'][$i]['cdwmc']=trim($v['供应商']);
            $list['rows'][$i]['nAmount']=round($v['数量']);
            $list['rows'][$i]['cdw']=trim($v['单位']);
            $list['rows'][$i]['nMoney']=round($v['金额（万元）']);
            $i++;
        }
        $res['status']=0;
        $res['msg']='';
        $res['data']=$list;
        return json($res);
    }
}