方法名调取*/
//开始时间方法--当年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);
}
//前年的一月一日
public function qianend_time(){
$currentYear = date("Y");
$previousYear = $currentYear - 2;
$firstDayOfPreviousYear = date("Y-m-d", strtotime("$previousYear-01-01"));
return $firstDayOfPreviousYear;
}
/**
* 一、使用量
* 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;
}
//去年使用量数据->缓存调用
public function qu_month_zz(){
$redis = redis();
$redis_key = md5('qu_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->qstart_time()}' AND '{$this->qend_time()}' ) tt";
//执行语句
$res = Db::query($sql);
//将查询结果存入 Redis 缓存中
$redis->set($redis_key, json_encode($res));
echo date("Y-m-d H:i:s").' 存进去了';
return $res;
}
//当年纸张使用量（吨）->接口调用
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);
}
//上一年度总纸张使用量（吨）->接口调用
public function qu_yearuse(){
$redis = redis();
$list = json_decode($redis->get(md5('qu_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);
}
//当年各出版社厂料使用量->接口调用
public function yearpress(){
$redis = redis();
$o_lists = json_decode($redis->get(md5('month_zz')), true);
// 定义要屏蔽的出版社数组
$blockedPublishers = [
'浙江教育出版社集团有限公司',
'浙江摄影出版社有限公司',
'浙江古籍出版社有限公司',
'浙江少年儿童出版社有限公司',
'浙江文艺出版社有限公司',
'浙江科学技术出版社有限公司',
'浙江人民美术出版社有限公司',
'浙江人民出版社有限公司',
'浙江出版传媒股份有限公司',
'浙江出版传媒股份有限公司（含省出版公司）'
];
// 结果数组，不包含被屏蔽的出版社
$o_list = array_filter($o_lists, function ($item) use ($blockedPublishers) {
return !in_array($item['cClientName'], $blockedPublishers);
});
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 "";print_r($result);echo "
"; 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);
if($res){
//将查询结果存入 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->qend_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 q_er_lst(){
$redis = redis();
$redis_key = md5('q_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->qianend_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;
}
}
//当年纸张采购量->接口调用
public function year_procurement(){
$redis = redis();
$list = json_decode($redis->get(md5('er_lst')),true);
$sum_dun = 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);
}
$list=[['name'=>' ','value'=>round($sum_dun)]];
$res['status']=0;
$res['msg']='';
$res['data']=$list;
return json($res);
}
//上一年度纸张采购量->接口调用
public function qu_year_procurement(){
$redis = redis();
$list = json_decode($redis->get(md5('qer_lst')),true);
$sum_dun = 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);
}
$list=[['name'=>' ','value'=>round($sum_dun)]];
$res['status']=0;
$res['msg']='';
$res['data']=$list;
return json($res);
}
//当年采购金额->接口调用
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);
}
//上一年第采购金额->接口调用
public function qu_year_money(){
$redis = redis();
$list = json_decode($redis->get(md5('qer_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);
// $list = json_decode($redis->get(md5('qer_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 "";print_r($c_list);echo "
";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('Y',time());
}
$sy_yeardata = 0;
$cg_yeardata = 0;
if($dependence['item']['value'] == date('Y',time())){
$i=(int)$dependence['item']['value'];
$res1['categories'][]=$dependence['item']['value'];
for($i=0;$i<=11;$i++){
$cg_yeardata += $list['series'][0]['data'][$i];
$sy_yeardata += $list1['series'][0]['data'][$i];
}
$res1['series'][0]['name']='采购量';
$res1['series'][0]['data'][]=$cg_yeardata;
$res1['series'][1]['name']='使用量';
$res1['series'][1]['data'][]=$sy_yeardata;
}else{
$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('Y',time());
}
$sy_yeardata = 0;
$cg_yeardata = 0;
if($dependence['item']['value'] == date('Y',time())){
$i=(int)$dependence['item']['value'];
$res1['categories'][]=$dependence['item']['value'];
for($i=0;$i<=11;$i++){
$cg_yeardata += $list['series'][0]['data'][$i];
$sy_yeardata += $list1['series'][0]['data'][$i];
}
$res1['series'][0]['name']='采购量';
$res1['series'][0]['data'][]=$cg_yeardata;
$res1['series'][1]['name']='使用量';
$res1['series'][1]['data'][]=$sy_yeardata;
}else{
$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('Y',time());
}
$sy_yeardata = 0;
$cg_yeardata = 0;
if($dependence['item']['value'] == date('Y',time())){
$i=(int)$dependence['item']['value'];
$res1['categories'][]=$dependence['item']['value'];
for($i=0;$i<=11;$i++){
$cg_yeardata += $list['series'][0]['data'][$i];
$sy_yeardata += $list1['series'][0]['data'][$i];
}
$res1['series'][0]['name']='采购量';
$res1['series'][0]['data'][]=$cg_yeardata;
$res1['series'][1]['name']='使用量';
$res1['series'][1]['data'][]=$sy_yeardata;
}else{
$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('Y',time());
}
$sy_yeardata = 0;
$cg_yeardata = 0;
if($dependence['item']['value'] == date('Y',time())){
$i=(int)$dependence['item']['value'];
$res1['categories'][]=$dependence['item']['value'];
for($i=0;$i<=11;$i++){
$cg_yeardata += $list['series'][0]['data'][$i];
$sy_yeardata += $list1['series'][0]['data'][$i];
}
$res1['series'][0]['name']='采购量';
$res1['series'][0]['data'][]=$cg_yeardata;
$res1['series'][1]['name']='使用量';
$res1['series'][1]['data'][]=$sy_yeardata;
}else{
$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('Y',time());
}
$sy_yeardata = 0;
$cg_yeardata = 0;
if($dependence['item']['value'] == date('Y',time())){
$i=(int)$dependence['item']['value'];
$res1['categories'][]=$dependence['item']['value'];
for($i=0;$i<=11;$i++){
$cg_yeardata += $list['series'][0]['data'][$i];
$sy_yeardata += $list1['series'][0]['data'][$i];
}
$res1['series'][0]['name']='采购量';
$res1['series'][0]['data'][]=$cg_yeardata;
$res1['series'][1]['name']='使用量';
$res1['series'][1]['data'][]=$sy_yeardata;
}else{
$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('Y',time());
}
$sy_yeardata = 0;
$cg_yeardata = 0;
if($dependence['item']['value'] == date('Y',time())){
$i=(int)$dependence['item']['value'];
$res1['categories'][]=$dependence['item']['value'];
for($i=0;$i<=11;$i++){
$cg_yeardata += $list['series'][0]['data'][$i];
$sy_yeardata += $list1['series'][0]['data'][$i];
}
$res1['series'][0]['name']='采购量';
$res1['series'][0]['data'][]=$cg_yeardata;
$res1['series'][1]['name']='使用量';
$res1['series'][1]['data'][]=$sy_yeardata;
}else{
$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('Y',time());
}
$sy_yeardata = 0;
$cg_yeardata = 0;
if($dependence['item']['value'] == date('Y',time())){
$i=(int)$dependence['item']['value'];
$res1['categories'][]=$dependence['item']['value'];
for($i=0;$i<=11;$i++){
$cg_yeardata += $list['series'][0]['data'][$i];
$sy_yeardata += $list1['series'][0]['data'][$i];
}
$res1['series'][0]['name']='采购量';
$res1['series'][0]['data'][]=$cg_yeardata;
$res1['series'][1]['name']='使用量';
$res1['series'][1]['data'][]=$sy_yeardata;
}else{
$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('Y',time());
}
$sy_yeardata = 0;
$cg_yeardata = 0;
if($dependence['item']['value'] == date('Y',time())){
$i=(int)$dependence['item']['value'];
$res1['categories'][]=$dependence['item']['value'];
for($i=0;$i<=11;$i++){
$cg_yeardata += $list['series'][0]['data'][$i];
$sy_yeardata += $list1['series'][0]['data'][$i];
}
$res1['series'][0]['name']='采购量';
$res1['series'][0]['data'][]=$cg_yeardata;
$res1['series'][1]['name']='使用量';
$res1['series'][1]['data'][]=$sy_yeardata;
}else{
$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('Y',time());
}
$sy_yeardata = 0;
$cg_yeardata = 0;
if($dependence['item']['value'] == date('Y',time())){
$i=(int)$dependence['item']['value'];
$res1['categories'][]=$dependence['item']['value'];
for($i=0;$i<=11;$i++){
$cg_yeardata += $list['series'][0]['data'][$i];
$sy_yeardata += $list1['series'][0]['data'][$i];
}
$res1['series'][0]['name']='采购量';
$res1['series'][0]['data'][]=$cg_yeardata;
$res1['series'][1]['name']='使用量';
$res1['series'][1]['data'][]=$sy_yeardata;
}else{
$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('Y',time());
}
$sy_yeardata = 0;
$cg_yeardata = 0;
if($dependence['item']['value'] == date('Y',time())){
$i=(int)$dependence['item']['value'];
$res1['categories'][]=$dependence['item']['value'];
for($i=0;$i<=11;$i++){
$cg_yeardata += $list['series'][0]['data'][$i];
$sy_yeardata += $list1['series'][0]['data'][$i];
}
$res1['series'][0]['name']='采购量';
$res1['series'][0]['data'][]=$cg_yeardata;
$res1['series'][1]['name']='使用量';
$res1['series'][1]['data'][]=$sy_yeardata;
}else{
$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('Y',time());
}
$sy_yeardata = 0;
$cg_yeardata = 0;
if($dependence['item']['value'] == date('Y',time())){
$i=(int)$dependence['item']['value'];
$res1['categories'][]=$dependence['item']['value'];
for($i=0;$i<=11;$i++){
$cg_yeardata += $list['series'][0]['data'][$i];
$sy_yeardata += $list1['series'][0]['data'][$i];
}
$res1['series'][0]['name']='采购量';
$res1['series'][0]['data'][]=$cg_yeardata;
$res1['series'][1]['name']='使用量';
$res1['series'][1]['data'][]=$sy_yeardata;
}else{
$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 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']=round($v['dun'],2);
$result['rows'][$k]['jine']=round($v['jine']/10000,2);
}
$res['status']=0;
$res['msg']='';
$res['data']=$result;
return json($res);
}
//纸张库存量及金额->缓存调用【可删除】
public function Paper_inventory_amount_old(){
$redis = redis();
$redis_key = md5('Paper_inventory_amount');
$sql = "SELECT
ZZKC.类别,
CAST( Sum( ZZKC.数量（吨） ) AS DECIMAL ( 8, 2 ) ) AS '库存数量(吨)',
CAST( Sum( ZZKC.金额 ) / 10000 AS DECIMAL ( 8, 2 ) ) AS '金额(万元）'
FROM
(
SELECT
KC.cPaperCode,
KC.cPaperName,
CASE
WHEN REPLACE ( KC.cPaperName, ' ', '' ) LIKE '%轻型纸%' THEN
'轻型纸'
WHEN REPLACE ( KC.cPaperName, ' ', '' ) LIKE '%纯质纸' THEN
'纯质纸'
WHEN REPLACE ( KC.cPaperName, ' ', '' ) LIKE '%特种纸%' THEN
'特种纸'
WHEN REPLACE ( KC.cPaperName, ' ', '' ) LIKE '%书纸%' THEN
'书纸'
WHEN REPLACE ( KC.cPaperName, ' ', '' ) LIKE '%全木浆双胶纸%' THEN
'全木浆双胶纸'
WHEN REPLACE ( KC.cPaperName, ' ', '' ) LIKE '%米黄双胶纸' THEN
'米黄双胶纸' ELSE KC.czgmc
END 类别,
KC.cUnit,
KC.数量,
KC.数量（吨）,
/* CW.单价,
CG.含税单价,*/
Cast( KC.数量 * COALESCE ( CW.单价, CG.含税单价, 0 ) AS DECIMAL ( 18, 2 ) ) 金额
FROM
(
SELECT
NN.cPaperCode,
NN.cPaperName,
NN.czgmc,
NN.cUnit,
Sum( nn.nAmount ) 数量,
Sum( nn.数量（吨） ) AS '数量（吨）'
FROM
(
SELECT
cPaperCode,
cPaperName,
cUnit,
Sum( nAmount ) AS nAmount,
dzzzgg.czgmc,
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 IFNULL( dzzzgg.nhss, 0 ) = 0 THEN
Sum( nAmount ) / 500 / 1.5 ELSE Sum( nAmount ) / 500 / dzzzgg.nhss
END
) ELSE 0
END AS '数量（吨）'
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(CURDATE(), 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 nAmount <> 0
AND cOwner = '本厂'
GROUP BY
cPaperCode,
cPaperName,
dzzzgg.czgmc,
cUnit,
namount,
dzzzgg.nhss
) NN
GROUP BY
NN.cPaperCode,
NN.czgmc,
NN.cPaperName,
NN.cUnit
) KC
LEFT JOIN (
SELECT
a.cPeriod,
a.cPaperCode,
c.nOutPrice * 1.13 单价
FROM
ppAccountFinance a
LEFT OUTER JOIN ppAccountFinancePrice c ON a.cPeriod = c.cPeriod
AND a.cPaperCode = c.cPaperCode
AND a.iplant = c.iplant
WHERE
1 = 1
AND a.cPeriod = ( SELECT Max( cPeriod ) FROM ppAccountFinance )
AND (
Abs( a.nBegAmount ) + Abs( a.nBegMoney ) + Abs( a.nInAmount ) + Abs( a.nInMoney ) + Abs( a.nOutAmount ) + Abs( a.nOutMoney ) + Abs( a.nEndAmount ) + Abs( a.nEndMoney ) <> 0
)
) CW ON KC.cPaperCode = cw.cPaperCode
LEFT JOIN (
SELECT
ppInDetail.cmessagepaper,
Avg( F.nTaxPrice ) 含税单价
FROM
ppInDetail
LEFT JOIN ppInNoticeDetail F ON PPInDetail.cmessagecode = F.cbillcode
GROUP BY
ppInDetail.cmessagepaper
) CG ON KC.cPaperCode = CG.cmessagepaper
) ZZKC
GROUP BY
ZZKC.类别";
$result=Db::query($sql);
if ($result) {
$redis->set($redis_key, json_encode($result));
echo date("Y-m-d H:i:s").' 存进去了';
return $result;
}
}
//纸张库存量及金额->接口调用【可删除】
public function inventory_amount_old() {
$redis = redis();
$result = json_decode($redis->get(md5('Paper_inventory_amount')), true);
// 自定义排序函数：按照金额(万元）从大到小排序
usort($result, function($a, $b) {
if ($a['金额(万元）'] == $b['金额(万元）']) {
return 0;
}
return ($a['金额(万元）'] > $b['金额(万元）']) ? -1 : 1;
});
// echo "";
// print_r($result);
// echo "";
$new_result = [];
$new_result['columns'] = [
['name' => '类别', 'id' => 'rank', 'width' => '35', 'color' => 'blue', 'autoWrap' => "true", 'textAlign' => 'left'],
['name' => '库存数量(吨)', 'id' => 'kucun', 'width' => '35', 'color' => 'blue', 'autoWrap' => "true", 'textAlign' => 'left'],
['name' => '金额(万元）', 'id' => 'kh', 'width' => '30', 'color' => 'red', 'autoWrap' => "true", 'textAlign' => 'left'],
];
$new_result['rows'] = [];
foreach ($result as $key => $value) {
$new_result['rows'][$key]['rank'] = $value['类别'];
$new_result['rows'][$key]['kucun'] = $value['库存数量(吨)'];
$new_result['rows'][$key]['kh'] = $value['金额(万元）'];
}
$res['status'] = 0;
$res['msg'] = '';
$res['data'] = $new_result;
return json($res);
}
//纸张库存量及金额->缓存调用
public function Paper_inventory_amount(){
$redis = redis();
$redis_key = md5('Paper_inventory_amount');
$sql = "SELECT *
FROM (SELECT ZZKC.`类别`,
ZZKC.`库龄`,
CAST(SUM(ZZKC.`数量（吨）`) AS DECIMAL(8, 2)) AS `库存数量(吨)`,
CAST(SUM(ZZKC.`金额`) / 10000 AS DECIMAL(8, 2)) AS `金额(万元)`,
CASE WHEN ZZKC.库龄='六个月以内'THEN 2 ELSE 1 end 序号
FROM (SELECT KC.cPaperCode,
KC.cPaperName,
CASE
WHEN REPLACE ( KC.cPaperName, ' ', '' ) LIKE '%轻型纸%' THEN
'轻型纸'
WHEN REPLACE ( KC.cPaperName, ' ', '' ) LIKE '%纯质纸' THEN
'纯质纸'
WHEN REPLACE ( KC.cPaperName, ' ', '' ) LIKE '%特种纸%' THEN
'特种纸'
WHEN REPLACE ( KC.cPaperName, ' ', '' ) LIKE '%书纸%' THEN
'书纸'
WHEN REPLACE ( KC.cPaperName, ' ', '' ) LIKE '%全木浆双胶纸%' THEN
'全木浆双胶纸'
WHEN REPLACE ( KC.cPaperName, ' ', '' ) LIKE '%米黄双胶纸' THEN
'米黄双胶纸' ELSE KC.czgmc
END 类别,
KC.cUnit,
KC.数量,
KC.数量（吨）,
KC.库龄,
/* CW.单价,
CG.含税单价,*/
Cast( KC.数量 * COALESCE ( CW.单价, CG.含税单价, 0 ) AS DECIMAL ( 18, 2 ) ) 金额
FROM (SELECT NN.cPaperCode,
NN.cPaperName,
NN.czgmc,
NN.cUnit,
NN.库龄,
Sum(nn.nAmount)数量,
Sum(nn.数量（吨）)AS '数量（吨）'
FROM (SELECT cPaperCode,
cPaperName,
cUnit,
Sum(nAmount) AS nAmount,
dzzzgg.czgmc,
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 IFnull(dzzzgg.nhss, 0) = 0 THEN Sum(nAmount) / 500 / 1.5
ELSE Sum(nAmount) / 500 / dzzzgg.nhss
END )
ELSE 0
END AS '数量（吨）', iStoreAge,
CASE
WHEN iStoreAge >180 THEN'六个月以上'
ELSE '六个月以内'
END 库龄
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(CURDATE(), 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 nAmount <> 0
AND cOwner = '本厂'
GROUP BY cPaperCode,
cPaperName,
dzzzgg.czgmc,
cUnit,iStoreAge,
namount,
dzzzgg.nhss) NN
GROUP BY NN.cPaperCode,
NN.czgmc,
NN.cPaperName,NN.库龄,
NN.cUnit)KC
LEFT JOIN (SELECT a.cPeriod,
a.cPaperCode,
c.nOutPrice * 1.13 单价
FROM ppAccountFinance a
LEFT OUTER JOIN ppAccountFinancePrice c
ON a.cPeriod = c.cPeriod
AND a.cPaperCode = c.cPaperCode
AND a.iplant = c.iplant
WHERE 1 = 1
AND a.cPeriod = (SELECT Max(cPeriod)
FROM ppAccountFinance)
AND ( Abs(a.nBegAmount) + Abs(a.nBegMoney)
+ Abs(a.nInAmount) + Abs(a.nInMoney)
+ Abs(a.nOutAmount) + Abs(a.nOutMoney)
+ Abs(a.nEndAmount) + Abs(a.nEndMoney) <> 0 ))CW
ON KC.cPaperCode = cw.cPaperCode
LEFT JOIN (SELECT ppInDetail.cmessagepaper,
Avg(F.nTaxPrice) 含税单价
FROM ppInDetail
LEFT JOIN ppInNoticeDetail F
ON PPInDetail.cmessagecode = F.cbillcode
GROUP BY ppInDetail.cmessagepaper)CG
ON KC.cPaperCode = CG.cmessagepaper)ZZKC
GROUP BY ZZKC.`类别`, ZZKC.`库龄`
)HZ
ORDER BY HZ.`序号`, HZ.`金额(万元)` DESC;";
$result=Db::query($sql);
if ($result) {
$redis->set($redis_key, json_encode($result));
echo date("Y-m-d H:i:s").' 存进去了';
return $result;
}
}
//纸张库存量及金额->接口调用
public function inventory_amount() {
$redis = redis(); // 假设已有redis()连接方法
$jsonData = $redis->get(md5('Paper_inventory_amount')); // 获取并处理数据
$data = json_decode($jsonData, true);
if (!$data) {
return json([
'status' => 0,
'msg' => '没有找到数据',
'data' => [
'columns' => [],
'rows' => []
]
]);
}
$sortedData = ['六个月以上' => [], '六个月以内' => []];
foreach ($data as $item) {
if (isset($sortedData[$item['库龄']])) {
$sortedData[$item['库龄']][] = $item;
}
}
// 对两个分类进行排序
foreach ($sortedData as $key => $items) {
usort($sortedData[$key], function ($a, $b) {
return $b['库存数量(吨)'] - $a['库存数量(吨)'];
});
}
// 合并排序后的数据
$finalData = array_merge($sortedData['六个月以上'], $sortedData['六个月以内']);
// 构造响应数据
$response = [
'columns' => [
['name' => '类别', 'id' => 'category', 'width' => '35', 'color' => 'blue', 'autoWrap' => "true", 'textAlign' => 'left'],
['name' => '库存数量(吨)', 'id' => 'amount', 'width' => '30', 'color' => 'blue', 'autoWrap' => "true", 'textAlign' => 'left'],
['name' => '金额(万元）', 'id' => 'kh', 'width' => '28', 'color' => 'red', 'autoWrap' => "true", 'textAlign' => 'left'],
['name' => '库龄', 'id' => 'kl', 'width' => '34', 'color' => 'red', 'autoWrap' => "true", 'textAlign' => 'left'],
// 其他列定义...
],
'rows' => []
];
$totalQuantityOverSixMonths = 0;
$totalAmountOverSixMonths = 0;
$totalQuantityUnderSixMonths = 0;
$totalAmountUnderSixMonths = 0;
foreach ($finalData as $item) {
if ($item['库龄'] === '六个月以上') {
$totalQuantityOverSixMonths += $item['库存数量(吨)'];
$totalAmountOverSixMonths += $item['金额(万元)'];
} elseif ($item['库龄'] === '六个月以内') {
$totalQuantityUnderSixMonths += $item['库存数量(吨)'];
$totalAmountUnderSixMonths += $item['金额(万元)'];
}
}
// 首先将“六个月以内”的总计数据放在数组最前面
array_unshift($finalData, [
'类别' => '合计',
'库龄' => '六个月以内',
'库存数量(吨)' => round($totalQuantityUnderSixMonths,2),
'金额(万元)' => round($totalAmountUnderSixMonths,2),
]);
// 然后将“六个月以上”的总计数据放在“六个月以内”之后（现在数组的最前面）
array_unshift($finalData, [
'类别' => '合计',
'库龄' => '六个月以上',
'库存数量(吨)' => round($totalQuantityOverSixMonths,2),
'金额(万元)' => round($totalAmountOverSixMonths,2),
]);
// echo "";
// print_r($finalData);
// echo "
";
// die;
foreach ($finalData as $item) {
$response['rows'][] = [
'category' => $item['类别'],
'amount' => $item['库存数量(吨)'],
'kh' => $item['金额(万元)'],
'kl' => $item['库龄'],
// 其他字段...
];
}
//echo "";
//print_r($response);
//echo "";
return json(['status' => 0, 'msg' => '', 'data' => $response]);
}
//当年主要纸张采购量及金额（吨）->接口调用
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 "";print_r($r_list);echo "
";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();
$filteredList = json_decode($redis->get(md5('q_er_lst')),true);
// 要排除的 cbillcode 值
$excludedCbillcodes = ['PRK050886', 'PRK052233'];
// 使用 array_filter 进行过滤
$list = array_filter($filteredList, function ($item) use ($excludedCbillcodes) {
return !in_array($item['cbillcode'], $excludedCbillcodes);
});
// 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();
// echo "";print_r($result);echo "";die;
$list = [];
$currentYear = date("Y"); // 当前年份
$currentMonth = date("m"); // 当前月份
$previousYear = $currentYear - 1; // 上一年
$currentdata=$result[$currentYear.'01'];
$currentdata=0;
$previousdata=0;
foreach ($result as $k=>$v){
if($k<$currentYear.'01'){
if($v[$name]==0){
$currentdata=$currentdata;
}else{
$currentdata=$v[$name];
}
}
if($k<$previousYear.'01'){
if($v[$name]==0){
$previousdata= $previousdata;
}else{
$previousdata=$v[$name];
}
}
}
$list['categories'] = ['01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12'];
foreach ($result as $k=>$v){
if($k>=$currentYear.'01'){
if($v[$name]==0){
$list['series'][1]['name'] = $currentYear . '年';
$list['series'][1]['data'][] =$currentdata;
}else{
$list['series'][1]['name'] = $currentYear . '年';
$list['series'][1]['data'][] =$v[$name];
$currentdata=$v[$name];
}
}elseif ($k>=$previousYear.'01'){
if($v[$name]==0){
$list['series'][0]['name'] = $previousYear . '年';
$list['series'][0]['data'][] =$previousdata;
}else{
$list['series'][0]['name'] = $previousYear . '年';
$list['series'][0]['data'][] =$v[$name];
$previousdata=$v[$name];
}
}
}
$res['status'] = 0;
$res['msg'] = '';
$res['data'] = $list;
return json($res);
}
//每月各类纸张采购均价-纯雅纸均价
public function cyzjun(){
$name = '纯雅纸';
$result = $this->month_paperjie();
// echo "";print_r($result);echo "";die;
$list = [];
$currentYear = date("Y"); // 当前年份
$currentMonth = date("m"); // 当前月份
$previousYear = $currentYear - 1; // 上一年
$currentdata=$result[$currentYear.'01'];
$currentdata=0;
$previousdata=0;
foreach ($result as $k=>$v){
if($k<$currentYear.'01'){
if($v[$name]==0){
$currentdata=$currentdata;
}else{
$currentdata=$v[$name];
}
}
if($k<$previousYear.'01'){
if($v[$name]==0){
$previousdata= $previousdata;
}else{
$previousdata=$v[$name];
}
}
}
$list['categories'] = ['01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12'];
foreach ($result as $k=>$v){
if($k>=$currentYear.'01'){
if($v[$name]==0){
$list['series'][1]['name'] = $currentYear . '年';
$list['series'][1]['data'][] =$currentdata;
}else{
$list['series'][1]['name'] = $currentYear . '年';
$list['series'][1]['data'][] =$v[$name];
$currentdata=$v[$name];
}
}elseif ($k>=$previousYear.'01'){
if($v[$name]==0){
$list['series'][0]['name'] = $previousYear . '年';
$list['series'][0]['data'][] =$previousdata;
}else{
$list['series'][0]['name'] = $previousYear . '年';
$list['series'][0]['data'][] =$v[$name];
$previousdata=$v[$name];
}
}
}
$res['status'] = 0;
$res['msg'] = '';
$res['data'] = $list;
return json($res);
// $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; // 初始化前一个数据点的值
//
// // echo "";print_r($result);echo "";
// foreach ($result as $k => $v) {
// $year = substr($k, 0, 4);
// $month = substr($k, 4, 2);
// if ($v[$name] != 0) {
// $prevdata = $v[$name];
// }
// 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];
//
// // 如果上一年的某个月份为0，则获取上个月的数据
// if ($v[$name] == 0 && $prevdata != 0) {
// $list['series'][0]['data'][$index] = $prevdata;
// }
//
// // 如果上一年的某个月份为0，则获取上个月的数据
// if ($index === 0 && $prevdata !== 0) {
// $list['series'][0]['data'][$index] = $prevdata;
// }
// }
// }
// // echo "";print_r($list);echo "";die;
}
//每月各类纸张采购均价-纯质纸均价
public function czzjun(){
$name = '纯质纸';
$result = $this->month_paperjie();
// echo "";print_r($result);echo "";die;
$list = [];
$currentYear = date("Y"); // 当前年份
$currentMonth = date("m"); // 当前月份
$previousYear = $currentYear - 1; // 上一年
$currentdata=$result[$currentYear.'01'];
$currentdata=0;
$previousdata=0;
foreach ($result as $k=>$v){
if($k<$currentYear.'01'){
if($v[$name]==0){
$currentdata=$currentdata;
}else{
$currentdata=$v[$name];
}
}
if($k<$previousYear.'01'){
if($v[$name]==0){
$previousdata= $previousdata;
}else{
$previousdata=$v[$name];
}
}
}
$list['categories'] = ['01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12'];
foreach ($result as $k=>$v){
if($k>=$currentYear.'01'){
if($v[$name]==0){
$list['series'][1]['name'] = $currentYear . '年';
$list['series'][1]['data'][] =$currentdata;
}else{
$list['series'][1]['name'] = $currentYear . '年';
$list['series'][1]['data'][] =$v[$name];
$currentdata=$v[$name];
}
}elseif ($k>=$previousYear.'01'){
if($v[$name]==0){
$list['series'][0]['name'] = $previousYear . '年';
$list['series'][0]['data'][] =$previousdata;
}else{
$list['series'][0]['name'] = $previousYear . '年';
$list['series'][0]['data'][] =$v[$name];
$previousdata=$v[$name];
}
}
}
$res['status'] = 0;
$res['msg'] = '';
$res['data'] = $list;
return json($res);
}
//每月各类纸张采购均价-全灰板均价
public function qhbjun(){
$name = '全灰板';
$result = $this->month_paperjie();
// echo "";print_r($result);echo "";die;
$list = [];
$currentYear = date("Y"); // 当前年份
$currentMonth = date("m"); // 当前月份
$previousYear = $currentYear - 1; // 上一年
$currentdata=$result[$currentYear.'01'];
$currentdata=0;
$previousdata=0;
foreach ($result as $k=>$v){
if($k<$currentYear.'01'){
if($v[$name]==0){
$currentdata=$currentdata;
}else{
$currentdata=$v[$name];
}
}
if($k<$previousYear.'01'){
if($v[$name]==0){
$previousdata= $previousdata;
}else{
$previousdata=$v[$name];
}
}
}
$list['categories'] = ['01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12'];
foreach ($result as $k=>$v){
if($k>=$currentYear.'01'){
if($v[$name]==0){
$list['series'][1]['name'] = $currentYear . '年';
$list['series'][1]['data'][] =$currentdata;
}else{
$list['series'][1]['name'] = $currentYear . '年';
$list['series'][1]['data'][] =$v[$name];
$currentdata=$v[$name];
}
}elseif ($k>=$previousYear.'01'){
if($v[$name]==0){
$list['series'][0]['name'] = $previousYear . '年';
$list['series'][0]['data'][] =$previousdata;
}else{
$list['series'][0]['name'] = $previousYear . '年';
$list['series'][0]['data'][] =$v[$name];
$previousdata=$v[$name];
}
}
}
$res['status'] = 0;
$res['msg'] = '';
$res['data'] = $list;
return json($res);
}
//每月各类纸张采购均价-白卡纸均价
public function bkzjun(){
$name = '白卡纸';
$result = $this->month_paperjie();
// echo "";print_r($result);echo "";die;
$list = [];
$currentYear = date("Y"); // 当前年份
$currentMonth = date("m"); // 当前月份
$previousYear = $currentYear - 1; // 上一年
$currentdata=$result[$currentYear.'01'];
$currentdata=0;
$previousdata=0;
foreach ($result as $k=>$v){
if($k<$currentYear.'01'){
if($v[$name]==0){
$currentdata=$currentdata;
}else{
$currentdata=$v[$name];
}
}
if($k<$previousYear.'01'){
if($v[$name]==0){
$previousdata= $previousdata;
}else{
$previousdata=$v[$name];
}
}
}
$list['categories'] = ['01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12'];
foreach ($result as $k=>$v){
if($k>=$currentYear.'01'){
if($v[$name]==0){
$list['series'][1]['name'] = $currentYear . '年';
$list['series'][1]['data'][] =$currentdata;
}else{
$list['series'][1]['name'] = $currentYear . '年';
$list['series'][1]['data'][] =$v[$name];
$currentdata=$v[$name];
}
}elseif ($k>=$previousYear.'01'){
if($v[$name]==0){
$list['series'][0]['name'] = $previousYear . '年';
$list['series'][0]['data'][] =$previousdata;
}else{
$list['series'][0]['name'] = $previousYear . '年';
$list['series'][0]['data'][] =$v[$name];
$previousdata=$v[$name];
}
}
}
$res['status'] = 0;
$res['msg'] = '';
$res['data'] = $list;
return json($res);
}
//每月各类纸张采购均价-轻型纸均价
public function qxzjun(){
$name = '轻型纸';
$result = $this->month_paperjie();
// echo "";print_r($result);echo "";die;
$list = [];
$currentYear = date("Y"); // 当前年份
$currentMonth = date("m"); // 当前月份
$previousYear = $currentYear - 1; // 上一年
$currentdata=$result[$currentYear.'01'];
$currentdata=0;
$previousdata=0;
foreach ($result as $k=>$v){
if($k<$currentYear.'01'){
if($v[$name]==0){
$currentdata=$currentdata;
}else{
$currentdata=$v[$name];
}
}
if($k<$previousYear.'01'){
if($v[$name]==0){
$previousdata= $previousdata;
}else{
$previousdata=$v[$name];
}
}
}
$list['categories'] = ['01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12'];
foreach ($result as $k=>$v){
if($k>=$currentYear.'01'){
if($v[$name]==0){
$list['series'][1]['name'] = $currentYear . '年';
$list['series'][1]['data'][] =$currentdata;
}else{
$list['series'][1]['name'] = $currentYear . '年';
$list['series'][1]['data'][] =$v[$name];
$currentdata=$v[$name];
}
}elseif ($k>=$previousYear.'01'){
if($v[$name]==0){
$list['series'][0]['name'] = $previousYear . '年';
$list['series'][0]['data'][] =$previousdata;
}else{
$list['series'][0]['name'] = $previousYear . '年';
$list['series'][0]['data'][] =$v[$name];
$previousdata=$v[$name];
}
}
}
$res['status'] = 0;
$res['msg'] = '';
$res['data'] = $list;
return json($res);
}
//每月各类纸张采购均价-亚光双面铜版纸均价
public function ygsmtbjun(){
$name = '亚光双面铜版纸';
$result = $this->month_paperjie();
// echo "";print_r($result);echo "";die;
$list = [];
$currentYear = date("Y"); // 当前年份
$currentMonth = date("m"); // 当前月份
$previousYear = $currentYear - 1; // 上一年
$currentdata=$result[$currentYear.'01'];
$currentdata=0;
$previousdata=0;
foreach ($result as $k=>$v){
if($k<$currentYear.'01'){
if($v[$name]==0){
$currentdata=$currentdata;
}else{
$currentdata=$v[$name];
}
}
if($k<$previousYear.'01'){
if($v[$name]==0){
$previousdata= $previousdata;
}else{
$previousdata=$v[$name];
}
}
}
$list['categories'] = ['01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12'];
foreach ($result as $k=>$v){
if($k>=$currentYear.'01'){
if($v[$name]==0){
$list['series'][1]['name'] = $currentYear . '年';
$list['series'][1]['data'][] =$currentdata;
}else{
$list['series'][1]['name'] = $currentYear . '年';
$list['series'][1]['data'][] =$v[$name];
$currentdata=$v[$name];
}
}elseif ($k>=$previousYear.'01'){
if($v[$name]==0){
$list['series'][0]['name'] = $previousYear . '年';
$list['series'][0]['data'][] =$previousdata;
}else{
$list['series'][0]['name'] = $previousYear . '年';
$list['series'][0]['data'][] =$v[$name];
$previousdata=$v[$name];
}
}
}
$res['status'] = 0;
$res['msg'] = '';
$res['data'] = $list;
return json($res);
}
//每月各类纸张采购均价-轻涂纸均价
public function qtzjun(){
$name = '轻涂纸';
$result = $this->month_paperjie();
// echo "";print_r($result);echo "";die;
$list = [];
$currentYear = date("Y"); // 当前年份
$currentMonth = date("m"); // 当前月份
$previousYear = $currentYear - 1; // 上一年
$currentdata=$result[$currentYear.'01'];
$currentdata=0;
$previousdata=0;
foreach ($result as $k=>$v){
if($k<$currentYear.'01'){
if($v[$name]==0){
$currentdata=$currentdata;
}else{
$currentdata=$v[$name];
}
}
if($k<$previousYear.'01'){
if($v[$name]==0){
$previousdata= $previousdata;
}else{
$previousdata=$v[$name];
}
}
}
$list['categories'] = ['01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12'];
foreach ($result as $k=>$v){
if($k>=$currentYear.'01'){
if($v[$name]==0){
$list['series'][1]['name'] = $currentYear . '年';
$list['series'][1]['data'][] =$currentdata;
}else{
$list['series'][1]['name'] = $currentYear . '年';
$list['series'][1]['data'][] =$v[$name];
$currentdata=$v[$name];
}
}elseif ($k>=$previousYear.'01'){
if($v[$name]==0){
$list['series'][0]['name'] = $previousYear . '年';
$list['series'][0]['data'][] =$previousdata;
}else{
$list['series'][0]['name'] = $previousYear . '年';
$list['series'][0]['data'][] =$v[$name];
$previousdata=$v[$name];
}
}
}
$res['status'] = 0;
$res['msg'] = '';
$res['data'] = $list;
return json($res);
}
//每月各类纸张采购均价-彩画纸均价
public function chzjun(){
$name = '彩画纸';
$result = $this->month_paperjie();
// echo "";print_r($result);echo "";die;
$list = [];
$currentYear = date("Y"); // 当前年份
$currentMonth = date("m"); // 当前月份
$previousYear = $currentYear - 1; // 上一年
$currentdata=$result[$currentYear.'01'];
$currentdata=0;
$previousdata=0;
foreach ($result as $k=>$v){
if($k<$currentYear.'01'){
if($v[$name]==0){
$currentdata=$currentdata;
}else{
$currentdata=$v[$name];
}
}
if($k<$previousYear.'01'){
if($v[$name]==0){
$previousdata= $previousdata;
}else{
$previousdata=$v[$name];
}
}
}
$list['categories'] = ['01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12'];
foreach ($result as $k=>$v){
if($k>=$currentYear.'01'){
if($v[$name]==0){
$list['series'][1]['name'] = $currentYear . '年';
$list['series'][1]['data'][] =$currentdata;
}else{
$list['series'][1]['name'] = $currentYear . '年';
$list['series'][1]['data'][] =$v[$name];
$currentdata=$v[$name];
}
}elseif ($k>=$previousYear.'01'){
if($v[$name]==0){
$list['series'][0]['name'] = $previousYear . '年';
$list['series'][0]['data'][] =$previousdata;
}else{
$list['series'][0]['name'] = $previousYear . '年';
$list['series'][0]['data'][] =$v[$name];
$previousdata=$v[$name];
}
}
}
$res['status'] = 0;
$res['msg'] = '';
$res['data'] = $list;
return json($res);
}
//每月各类纸张采购均价-特种纸均价
public function tzzjun(){
$name = '特种纸';
$result = $this->month_paperjie();
// echo "";print_r($result);echo "";die;
$list = [];
$currentYear = date("Y"); // 当前年份
$currentMonth = date("m"); // 当前月份
$previousYear = $currentYear - 1; // 上一年
$currentdata=$result[$currentYear.'01'];
$currentdata=0;
$previousdata=0;
foreach ($result as $k=>$v){
if($k<$currentYear.'01'){
if($v[$name]==0){
$currentdata=$currentdata;
}else{
$currentdata=$v[$name];
}
}
if($k<$previousYear.'01'){
if($v[$name]==0){
$previousdata= $previousdata;
}else{
$previousdata=$v[$name];
}
}
}
$list['categories'] = ['01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12'];
foreach ($result as $k=>$v){
if($k>=$currentYear.'01'){
if($v[$name]==0){
$list['series'][1]['name'] = $currentYear . '年';
$list['series'][1]['data'][] =$currentdata;
}else{
$list['series'][1]['name'] = $currentYear . '年';
$list['series'][1]['data'][] =$v[$name];
$currentdata=$v[$name];
}
}elseif ($k>=$previousYear.'01'){
if($v[$name]==0){
$list['series'][0]['name'] = $previousYear . '年';
$list['series'][0]['data'][] =$previousdata;
}else{
$list['series'][0]['name'] = $previousYear . '年';
$list['series'][0]['data'][] =$v[$name];
$previousdata=$v[$name];
}
}
}
$res['status'] = 0;
$res['msg'] = '';
$res['data'] = $list;
return json($res);
}
//每月各类纸张采购均价-本白双胶纸均价
public function bbsjjun(){
$name = '本白双胶纸';
$result = $this->month_paperjie();
// echo "";print_r($result);echo "";die;
$list = [];
$currentYear = date("Y"); // 当前年份
$currentMonth = date("m"); // 当前月份
$previousYear = $currentYear - 1; // 上一年
$currentdata=$result[$currentYear.'01'];
$currentdata=0;
$previousdata=0;
foreach ($result as $k=>$v){
if($k<$currentYear.'01'){
if($v[$name]==0){
$currentdata=$currentdata;
}else{
$currentdata=$v[$name];
}
}
if($k<$previousYear.'01'){
if($v[$name]==0){
$previousdata= $previousdata;
}else{
$previousdata=$v[$name];
}
}
}
$list['categories'] = ['01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12'];
foreach ($result as $k=>$v){
if($k>=$currentYear.'01'){
if($v[$name]==0){
$list['series'][1]['name'] = $currentYear . '年';
$list['series'][1]['data'][] =$currentdata;
}else{
$list['series'][1]['name'] = $currentYear . '年';
$list['series'][1]['data'][] =$v[$name];
$currentdata=$v[$name];
}
}elseif ($k>=$previousYear.'01'){
if($v[$name]==0){
$list['series'][0]['name'] = $previousYear . '年';
$list['series'][0]['data'][] =$previousdata;
}else{
$list['series'][0]['name'] = $previousYear . '年';
$list['series'][0]['data'][] =$v[$name];
$previousdata=$v[$name];
}
}
}
$res['status'] = 0;
$res['msg'] = '';
$res['data'] = $list;
return json($res);
}
//每月各类纸张采购均价-有光双面铜版纸均价
public function ygsmtjun(){
$name = '有光双面铜版纸';
$result = $this->month_paperjie();
// echo "";print_r($result);echo "";die;
$list = [];
$currentYear = date("Y"); // 当前年份
$currentMonth = date("m"); // 当前月份
$previousYear = $currentYear - 1; // 上一年
$currentdata=$result[$currentYear.'01'];
$currentdata=0;
$previousdata=0;
foreach ($result as $k=>$v){
if($k<$currentYear.'01'){
if($v[$name]==0){
$currentdata=$currentdata;
}else{
$currentdata=$v[$name];
}
}
if($k<$previousYear.'01'){
if($v[$name]==0){
$previousdata= $previousdata;
}else{
$previousdata=$v[$name];
}
}
}
$list['categories'] = ['01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12'];
foreach ($result as $k=>$v){
if($k>=$currentYear.'01'){
if($v[$name]==0){
$list['series'][1]['name'] = $currentYear . '年';
$list['series'][1]['data'][] =$currentdata;
}else{
$list['series'][1]['name'] = $currentYear . '年';
$list['series'][1]['data'][] =$v[$name];
$currentdata=$v[$name];
}
}elseif ($k>=$previousYear.'01'){
if($v[$name]==0){
$list['series'][0]['name'] = $previousYear . '年';
$list['series'][0]['data'][] =$previousdata;
}else{
$list['series'][0]['name'] = $previousYear . '年';
$list['series'][0]['data'][] =$v[$name];
$previousdata=$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 纸张详情名称
*/
// 【可删除掉】
public function tt3new_old(){
//链接定义 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";
$list = Db::query($sql);
$list = array_splice($list,0,10);
//将查询结果存入 Redis 缓存中
$redis->set($redis_key, json_encode($list));
return json($list);
}
public function inventorydgz_old(){
$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'=>'42','autoWrap'=>"true",'textAlign'=>'left'],
['name'=>'库龄','id'=>'year','width'=>'23','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['数量（吨）'],2);
$i++;
}
$res['status']=0;
$res['msg']='';
$res['data']=$list;
return json($res);
}
//纸张库存情况->缓存调用
public function tt3new(){
//链接定义 Redis
$redis = redis();
$redis_key = md5('tt3new');
//超过180天以上总量
$sql = "SELECT *
FROM (SELECT NN.cOwner 货主,
NN.库龄,
ROUND(SUM(NN.数量（吨）), 2) 数量（吨）,
CASE WHEN NN.库龄='六个月以内'THEN 2 ELSE 1 end 序号
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) nNetAmount,
CASE cPaperType
WHEN '卷筒' THEN Count(CASE
WHEN nAmount = 0 THEN NULL
ELSE cPaperCode
END)
ELSE 0
END AS iCount,
CASE
WHEN iStoreAge >180 THEN'六个月以上'
ELSE '六个月以内'
END 库龄,
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 kc.序号, 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' => []
]
]);
}
$aboveSixMonths = [];
$withinSixMonths = [];
$delete = '有限公司';
foreach ($result as $v) {
if (strpos($v['货主'], $delete)) {
$v['货主'] = str_replace($delete, '', $v['货主']);
}
$v['数量（吨）'] = round($v['数量（吨）'], 2);
if ($v['库龄'] === '六个月以上') {
$aboveSixMonths[] = $v;
} else if ($v['库龄'] === '六个月以内') {
$withinSixMonths[] = $v;
}
}
// 对两个数组分别按数量（吨）排序
usort($aboveSixMonths, function($a, $b) {
return $b['数量（吨）'] <=> $a['数量（吨）'];
});
usort($withinSixMonths, function($a, $b) {
return $b['数量（吨）'] <=> $a['数量（吨）'];
});
// 合并排序后的数组
$sortedResult = array_merge($aboveSixMonths, $withinSixMonths);
$list = [
'columns' => [
['name' => '货主', 'id' => 'cOwnerName', 'width' => '42', 'autoWrap' => "true", 'textAlign' => 'left'],
['name' => '库龄', 'id' => 'year', 'width' => '23', 'autoWrap' => "true", 'textAlign' => 'left'],
['name' => '数量(吨)', 'id' => 'nAmount', 'width' => '25', 'autoWrap' => "true", 'textAlign' => 'left']
],
'rows' => []
];
foreach ($sortedResult as $i => $v) {
$list['rows'][] = [
'cOwnerName' => $v['货主'],
'year' => $v['库龄'],
'nAmount' => $v['数量（吨）']
];
}
$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'=>'34','autoWrap'=>"true",'textAlign'=>'left'],
['name'=>'数量','id'=>'nAmount','width'=>'14','autoWrap'=>"true",'textAlign'=>'left'],
['name'=>'单位','id'=>'cdw','width'=>'13','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'=>'42','autoWrap'=>"true",'textAlign'=>'left'],
['name'=>'数量','id'=>'nAmount','width'=>'14','autoWrap'=>"true",'textAlign'=>'left'],
['name'=>'单位','id'=>'cdw','width'=>'10','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['金额（万元）'],2);
$i++;
}
$res['status']=0;
$res['msg']='';
$res['data']=$list;
return json($res);
}
}