Decision.php 103 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139114011411142114311441145114611471148114911501151115211531154115511561157115811591160116111621163116411651166116711681169117011711172117311741175117611771178117911801181118211831184118511861187118811891190119111921193119411951196119711981199120012011202120312041205120612071208120912101211121212131214121512161217121812191220122112221223122412251226122712281229123012311232123312341235123612371238123912401241124212431244124512461247124812491250125112521253125412551256125712581259126012611262126312641265126612671268126912701271127212731274127512761277127812791280128112821283128412851286128712881289129012911292129312941295129612971298129913001301130213031304130513061307130813091310131113121313131413151316131713181319132013211322132313241325132613271328132913301331133213331334133513361337133813391340134113421343134413451346134713481349135013511352135313541355135613571358135913601361136213631364136513661367136813691370137113721373137413751376137713781379138013811382138313841385138613871388138913901391139213931394139513961397139813991400140114021403140414051406140714081409141014111412141314141415141614171418141914201421142214231424142514261427142814291430143114321433143414351436143714381439144014411442144314441445144614471448144914501451145214531454145514561457145814591460146114621463146414651466146714681469147014711472147314741475147614771478147914801481148214831484148514861487148814891490149114921493149414951496149714981499150015011502150315041505150615071508150915101511151215131514151515161517151815191520152115221523152415251526152715281529153015311532153315341535153615371538153915401541154215431544154515461547154815491550155115521553155415551556155715581559156015611562156315641565156615671568156915701571157215731574157515761577157815791580158115821583158415851586158715881589159015911592159315941595159615971598159916001601160216031604160516061607160816091610161116121613161416151616161716181619162016211622162316241625162616271628162916301631163216331634163516361637163816391640164116421643164416451646164716481649165016511652165316541655165616571658165916601661166216631664166516661667166816691670167116721673167416751676167716781679168016811682168316841685168616871688168916901691169216931694169516961697169816991700170117021703170417051706170717081709171017111712171317141715171617171718171917201721172217231724172517261727172817291730173117321733173417351736173717381739174017411742174317441745174617471748174917501751175217531754175517561757175817591760176117621763176417651766176717681769177017711772177317741775177617771778177917801781178217831784178517861787178817891790179117921793179417951796179717981799180018011802180318041805180618071808180918101811181218131814181518161817181818191820182118221823182418251826182718281829183018311832183318341835183618371838183918401841184218431844184518461847184818491850185118521853185418551856185718581859186018611862186318641865186618671868186918701871187218731874187518761877187818791880188118821883188418851886188718881889189018911892189318941895189618971898189919001901190219031904190519061907190819091910191119121913191419151916191719181919192019211922192319241925192619271928192919301931193219331934193519361937193819391940194119421943194419451946194719481949195019511952195319541955195619571958195919601961196219631964196519661967196819691970197119721973197419751976197719781979198019811982198319841985198619871988198919901991199219931994199519961997199819992000200120022003200420052006200720082009201020112012201320142015201620172018201920202021202220232024202520262027202820292030203120322033203420352036203720382039204020412042204320442045204620472048204920502051205220532054205520562057205820592060206120622063206420652066206720682069207020712072207320742075207620772078207920802081208220832084208520862087208820892090209120922093209420952096209720982099210021012102210321042105210621072108210921102111211221132114211521162117211821192120212121222123212421252126212721282129213021312132213321342135213621372138213921402141214221432144214521462147214821492150215121522153215421552156215721582159216021612162216321642165216621672168216921702171217221732174217521762177217821792180218121822183218421852186218721882189219021912192219321942195219621972198219922002201220222032204220522062207220822092210221122122213221422152216221722182219222022212222222322242225222622272228222922302231223222332234223522362237223822392240224122422243224422452246224722482249225022512252225322542255225622572258225922602261226222632264226522662267226822692270227122722273227422752276227722782279228022812282228322842285228622872288228922902291229222932294229522962297229822992300230123022303230423052306230723082309231023112312231323142315231623172318231923202321232223232324232523262327232823292330233123322333233423352336233723382339234023412342234323442345234623472348234923502351235223532354235523562357235823592360236123622363236423652366236723682369237023712372237323742375237623772378237923802381238223832384238523862387238823892390239123922393239423952396239723982399240024012402240324042405240624072408240924102411241224132414241524162417241824192420242124222423242424252426242724282429243024312432243324342435243624372438243924402441244224432444244524462447244824492450245124522453245424552456245724582459246024612462246324642465246624672468246924702471247224732474247524762477247824792480248124822483248424852486248724882489249024912492249324942495249624972498249925002501250225032504250525062507250825092510251125122513251425152516251725182519252025212522252325242525252625272528252925302531253225332534253525362537253825392540254125422543254425452546254725482549255025512552255325542555255625572558255925602561256225632564256525662567256825692570257125722573257425752576257725782579258025812582258325842585258625872588258925902591259225932594259525962597259825992600260126022603260426052606260726082609261026112612261326142615261626172618261926202621262226232624262526262627262826292630263126322633263426352636263726382639264026412642264326442645264626472648264926502651265226532654265526562657265826592660266126622663266426652666266726682669267026712672267326742675267626772678267926802681268226832684268526862687268826892690269126922693269426952696269726982699270027012702270327042705270627072708270927102711271227132714271527162717271827192720272127222723272427252726272727282729273027312732273327342735273627372738273927402741274227432744274527462747274827492750275127522753275427552756275727582759276027612762276327642765276627672768276927702771277227732774277527762777277827792780278127822783278427852786278727882789279027912792
  1. <?php
  2. namespace app\api\controller;
  3. use app\common\controller\Api;
  4. use think\Db;
  5. use think\Request;
  6. /**
  7. * 决策支持
  8. */
  9. class Decision extends Api
  10. {
  11. protected $noNeedLogin = ['*'];
  12. protected $noNeedRight = ['*'];
  13. //月度产量统计菜单
  14. public function OutputSstatisticsMenu()
  15. {
  16. if ($this->request->isGet() === false){
  17. $this->error('请求错误');
  18. }
  19. $mouth = \db('设备_产量计酬')
  20. ->distinct(true)
  21. ->field('DATE_FORMAT(sczl_rq, "%Y-%m") AS month')
  22. ->order('month desc')
  23. ->select();
  24. $sist = \db('设备_基本资料')
  25. ->whereNotNull('设备编组')
  26. ->group('设备编组')
  27. ->column('rtrim(设备编组) as 设备编组');
  28. $data = [];
  29. foreach ($mouth as $key=>$value) {
  30. $arr = [
  31. 'date'=>date('Ym',strtotime($value['month'])),
  32. 'sbbh'=>$sist,
  33. ];
  34. array_push($data,$arr);
  35. }
  36. $this->success('成功',$data);
  37. }
  38. /**
  39. * 月度产量统计上方机台生产数据
  40. * @return void
  41. * @throws \think\db\exception\DataNotFoundException
  42. * @throws \think\db\exception\ModelNotFoundException
  43. * @throws \think\exception\DbException
  44. */
  45. public function MachineProduction()
  46. {
  47. // 验证请求方式
  48. if ($this->request->isGet() === false) {
  49. $this->error('请求错误');
  50. }
  51. // 获取请求参数
  52. $param = $this->request->param();
  53. if (empty($param['mouth'])) {
  54. $this->error('参数错误');
  55. }
  56. // 初始化查询条件
  57. $where = [];
  58. if (!empty($param['sist'])) {
  59. $where['b.设备编组'] = $param['sist'];
  60. }
  61. // 转换日期格式
  62. $mouth = date_create_from_format('Ym', $param['mouth'])->format('Y-m');
  63. // 使用单个查询获取所有需要的数据
  64. $results = \db('设备_产量计酬')
  65. ->alias('a')
  66. ->join('设备_基本资料 b', 'a.sczl_jtbh = b.设备编号')
  67. ->join('工单_印件资料 c', 'a.sczl_gdbh = c.Yj_Gdbh AND a.sczl_yjno = c.yj_Yjno', 'LEFT')
  68. ->join('工单_工艺资料 d', 'a.sczl_gdbh = d.Gy0_gdbh AND a.sczl_yjno = d.Gy0_yjno AND a.sczl_gxh = d.Gy0_gxh', 'LEFT')
  69. ->field([
  70. 'a.sczl_jtbh',
  71. 'a.sczl_bzdh',
  72. 'DATE_FORMAT(a.sczl_rq, "%Y-%m-%d") as day',
  73. 'SUM(a.sczl_cl) AS total_cl',
  74. 'c.yj_yjmc AS 印件名称',
  75. 'CONCAT(d.Gy0_gxmc,"(",d.Add_gxmc,")") AS 工序名称',
  76. 'RTRIM(d.印刷方式) AS 印刷方式',
  77. 'RTRIM(d.版距) AS 版距'
  78. ])
  79. ->where('a.sczl_rq', 'like', $mouth . '%')
  80. ->where('b.sys_sbID', '<>', '')
  81. ->where($where)
  82. ->group('a.sczl_jtbh, a.sczl_bzdh, day, c.yj_yjmc, d.Gy0_gxmc')
  83. ->order('a.sczl_jtbh, a.sczl_bzdh, day')
  84. ->select();
  85. // 数据处理
  86. $data = [];
  87. // 获取唯一日期并排序
  88. $data['head'] = array_unique(array_column($results, 'day'));
  89. // 将日期转为时间戳以排序
  90. usort($data['head'], function($a, $b) {
  91. return strtotime($a) - strtotime($b);
  92. });
  93. // 准备按设备和班组整理的数据
  94. $total_by_machine = [];
  95. foreach ($results as $item) {
  96. $machine_id = $item['sczl_jtbh'];
  97. $group_id = $item['sczl_bzdh'];
  98. $day = $item['day'];
  99. $printed_type = $item['印刷方式'];
  100. // 初始化设备和班组
  101. if (!isset($total_by_machine[$machine_id])) {
  102. $total_by_machine[$machine_id] = [];
  103. }
  104. if (!isset($total_by_machine[$machine_id][$group_id])) {
  105. $total_by_machine[$machine_id][$group_id] = [
  106. '机台编号' => $machine_id,
  107. '班组编号' => $group_id,
  108. '印件名称' => $item['印件名称'],
  109. '工序名称' => $item['工序名称'],
  110. 'total_cl' => 0,
  111. 'day_total' => []
  112. ];
  113. }
  114. // 更新累计数量
  115. if ($printed_type === '卷对卷') {
  116. $total_by_machine[$machine_id][$group_id]['total_cl'] += round($item['total_cl'] / $item['版距'] * 1000);
  117. } else {
  118. $total_by_machine[$machine_id][$group_id]['total_cl'] += $item['total_cl'];
  119. }
  120. // 更新每日总计
  121. if (!isset($total_by_machine[$machine_id][$group_id]['day_total'][$day])) {
  122. $total_by_machine[$machine_id][$group_id]['day_total'][$day] = 0;
  123. }
  124. if ($printed_type === '卷对卷') {
  125. $total_by_machine[$machine_id][$group_id]['day_total'][$day] += round($item['total_cl'] / $item['版距'] * 1000);
  126. } else {
  127. $total_by_machine[$machine_id][$group_id]['day_total'][$day] += $item['total_cl'];
  128. }
  129. }
  130. // 格式化输出数据
  131. $data['total'] = [];
  132. foreach ($total_by_machine as $machine_group) {
  133. foreach ($machine_group as $group_data) {
  134. $data['total'][] = $group_data;
  135. }
  136. }
  137. $this->success('成功', $data);
  138. }
  139. /**
  140. * 机台班次生产工单明细
  141. * @return void
  142. * @throws \think\db\exception\DataNotFoundException
  143. * @throws \think\db\exception\ModelNotFoundException
  144. * @throws \think\exception\DbException
  145. */
  146. public function MachineProductDetail()
  147. {
  148. if (!$this->request->isGet()) {
  149. $this->error('请求错误');
  150. }
  151. $param = $this->request->param();
  152. // 检查必需的参数
  153. if (empty($param['mouth'])) {
  154. $this->error('参数错误');
  155. }
  156. // Initializing where conditions
  157. $where = [];
  158. // 添加可选的查询条件
  159. if (!empty($param['machine'])) {
  160. $where['a.sczl_jtbh'] = $param['machine'];
  161. }
  162. if (!empty($param['team'])) {
  163. $where['a.sczl_bzdh'] = ['like', substr($param['team'], 0, 1) . '%'];
  164. }
  165. // 将参数转换成标准日期格式
  166. $mouth = date_create_from_format('Ym', $param['mouth'])->format('Y-m');
  167. $where['a.sczl_rq'] = ['like', $mouth . '%'];
  168. // 分页配置
  169. $page = !empty($param['page']) ? (int)$param['page'] : 1;
  170. $limit = !empty($param['limit']) ? (int)$param['limit'] : 9999; // 默认查询所有
  171. // 查询数据
  172. $list = \db('设备_产量计酬')
  173. ->alias('a')
  174. ->join('工单_印件资料 c', 'a.sczl_gdbh = c.Yj_Gdbh AND a.sczl_yjno = c.yj_Yjno')
  175. ->join('工单_工艺资料 d', 'a.sczl_gdbh = d.Gy0_gdbh AND a.sczl_yjno = d.Gy0_yjno AND a.sczl_gxh = d.Gy0_gxh')
  176. ->join('dic_lzde e', 'a.sczl_dedh = e.sys_bh')
  177. ->field([
  178. 'a.sczl_gdbh' => '工单编号',
  179. 'a.sczl_yjno' => '印件号',
  180. 'a.sczl_gxh' => '工序号',
  181. 'c.yj_yjmc' => '印件名称',
  182. 'CONCAT(d.Gy0_gxmc,"(",d.Add_gxmc,")")' => '工序名称',
  183. 'DATE(a.sczl_rq)' => '工作日期',
  184. 'a.sczl_jtbh' => '机台编号',
  185. 'a.sczl_bzdh' => '班组编号',
  186. 'SUM(a.sczl_cl)' => '产量',
  187. 'a.sczl_ms' => '墨色数',
  188. 'rtrim(d.印刷方式)' => '印刷方式',
  189. 'rtrim(d.版距)' => '版距',
  190. 'rtrim(e.补产标准)' => '补产标准',
  191. 'rtrim(d.工价系数)' => '工价系数'
  192. ])
  193. ->where($where)
  194. ->group('a.sczl_gdbh, a.sczl_yjno, a.sczl_gxh, a.sczl_rq, a.sczl_jtbh, a.sczl_bzdh')
  195. ->order('工作日期')
  196. ->page($page, $limit) // 使用 page 和 limit 实现分页
  197. ->select();
  198. // 数据处理
  199. if (!empty($list)) {
  200. foreach ($list as $key => $value) {
  201. $list[$key]['印件名称'] = $value['印件号'] . '-' . $value['印件名称'];
  202. $list[$key]['工序名称'] = $value['工序号'] . '-' . $value['工序名称'];
  203. unset($list[$key]['印件号'], $list[$key]['工序号']);
  204. // 产量计算
  205. if ($value['印刷方式'] === '卷对卷') {
  206. $list[$key]['产量'] = round($value['产量'] / $value['版距'] * 1000);
  207. }
  208. // 墨色数修正
  209. if ($value['墨色数'] === '0.00') {
  210. $list[$key]['墨色数'] = '1.00';
  211. }
  212. }
  213. }
  214. $this->success('成功', $list);
  215. }
  216. /**
  217. * 月度机台运行工时汇总
  218. * @return void
  219. * @throws \think\db\exception\DataNotFoundException
  220. * @throws \think\db\exception\ModelNotFoundException
  221. * @throws \think\exception\DbException
  222. */
  223. public function MachineOperation()
  224. {
  225. // 确保请求是GET
  226. if ($this->request->isGet() === false) {
  227. $this->error('请求错误');
  228. }
  229. // 获取请求参数
  230. $param = $this->request->param();
  231. if (empty($param['mouth'])) {
  232. $this->error('参数错误');
  233. }
  234. // 构建查询条件
  235. $where = [];
  236. if (!empty($param['sist'])) {
  237. $where['a.设备编组'] = $param['sist'];
  238. }
  239. // 将参数转换成标准日期格式
  240. $mouth = date_create_from_format('Ym', $param['mouth'])->format('Y-m');
  241. // 单次查询,计算产量和各项工时
  242. $list = \db('设备_基本资料')
  243. ->alias('a')
  244. ->join('设备_产量计酬 b', 'a.设备编号 = b.sczl_jtbh')
  245. ->join('工单_印件资料 c', 'b.sczl_gdbh = c.Yj_Gdbh AND b.sczl_yjno = c.yj_Yjno')
  246. ->join('工单_工艺资料 d', 'b.sczl_gdbh = d.Gy0_gdbh AND b.sczl_yjno = d.Gy0_yjno AND b.sczl_gxh = d.Gy0_gxh')
  247. ->field([
  248. 'a.设备编号' => '设备编号',
  249. 'rtrim(a.设备名称)' => '设备名称',
  250. 'SUM(CASE WHEN rtrim(d.版距) = "卷对卷" THEN b.sczl_cl / NULLIF(d.版距, 0) * 1000 ELSE b.sczl_cl END) AS 产量',
  251. 'SUM(b.sczl_设备运行工时)' => '设备运行工时',
  252. 'SUM(b.sczl_保养工时)' => '保养工时',
  253. 'SUM(b.sczl_打样总工时)' => '打样总工时',
  254. 'SUM(b.sczl_打样工时)' => '打样补产工时',
  255. 'SUM(b.sczl_装版总工时)' => '装版总工时',
  256. 'SUM(b.sczl_装版工时)' => '装版补产工时',
  257. 'SUM(b.sczl_异常停机工时)' => '异常停机工时',
  258. 'rtrim(d.印刷方式)' => '印刷方式',
  259. 'rtrim(d.版距)' => '版距'
  260. ])
  261. ->where($where)
  262. ->where('b.sczl_rq', 'like', $mouth . '%')
  263. ->group('a.设备编号')
  264. ->order('a.设备编号')
  265. ->select();
  266. // 处理总工时的计算
  267. $total = [
  268. '设备运行工时' => 0,
  269. '保养工时' => 0,
  270. '打样总工时' => 0,
  271. '打样补产工时' => 0,
  272. '装版总工时' => 0,
  273. '装版补产工时' => 0,
  274. '异常停机工时' => 0
  275. ];
  276. // 计算各项总工时
  277. foreach ($list as $item) {
  278. $total['设备运行工时'] += $item['设备运行工时'];
  279. $total['保养工时'] += $item['保养工时'];
  280. $total['打样总工时'] += $item['打样总工时'];
  281. $total['打样补产工时'] += $item['打样补产工时'];
  282. $total['装版总工时'] += $item['装版总工时'];
  283. $total['装版补产工时'] += $item['装版补产工时'];
  284. $total['异常停机工时'] += $item['异常停机工时'];
  285. }
  286. // 将总工时添加到列表中
  287. $list['total'] = $total;
  288. // 返回成功响应
  289. $this->success('成功', $list);
  290. }
  291. /**
  292. * 设备运行工时机台生产工单数据详情
  293. * @return void
  294. * @throws \think\db\exception\DataNotFoundException
  295. * @throws \think\db\exception\ModelNotFoundException
  296. * @throws \think\exception\DbException
  297. */
  298. public function MachineOperationProductDetail()
  299. {
  300. if (!$this->request->isGet()) {
  301. $this->error('请求错误');
  302. }
  303. $param = $this->request->param();
  304. if (empty($param['mouth'])) {
  305. $this->error('参数错误');
  306. }
  307. // Initialize where conditions
  308. $where = [];
  309. if (!empty($param['machine'])) {
  310. $where['a.sczl_jtbh'] = $param['machine'];
  311. }
  312. // 将参数转换成标准日期格式
  313. $mouth = date_create_from_format('Ym', $param['mouth'])->format('Y-m');
  314. $where['a.sczl_rq'] = ['like', $mouth . '%'];
  315. // 分页配置
  316. $page = !empty($param['page']) ? (int)$param['page'] : 1;
  317. $limit = !empty($param['limit']) ? (int)$param['limit'] : 9999; // 默认查询所有
  318. // 查询数据
  319. $list = \db('设备_产量计酬')
  320. ->alias('a')
  321. ->join('设备_基本资料 b', 'a.sczl_jtbh = b.设备编号')
  322. ->join('工单_印件资料 c', 'a.sczl_gdbh = c.Yj_Gdbh AND a.sczl_yjno = c.yj_Yjno')
  323. ->join('工单_工艺资料 d', 'a.sczl_gdbh = d.Gy0_gdbh AND a.sczl_yjno = d.Gy0_yjno AND a.sczl_gxh = d.Gy0_gxh')
  324. ->join('dic_lzde e', 'a.sczl_dedh = e.sys_bh')
  325. ->field([
  326. 'a.sczl_jtbh' => '设备编号',
  327. 'rtrim(b.设备名称)' => '设备名称',
  328. 'DATE(a.sczl_rq)' => '日期',
  329. 'a.sczl_gdbh' => '工单编号',
  330. 'a.sczl_yjno' => '印件号',
  331. 'a.sczl_gxh' => '工序号',
  332. 'c.yj_yjmc' => '印件名称',
  333. 'CONCAT(d.Gy0_gxmc, "(", d.Add_gxmc, ")")' => '工序名称',
  334. 'SUM(a.sczl_cl)' => '产量',
  335. 'SUM(a.sczl_设备运行工时)' => '设备运行工时',
  336. 'SUM(a.sczl_保养工时)' => '保养工时',
  337. 'SUM(a.sczl_打样总工时)' => '打样总工时',
  338. 'SUM(a.sczl_打样工时)' => '打样补产工时',
  339. 'SUM(a.sczl_装版总工时)' => '装板总工时',
  340. 'SUM(a.sczl_装版工时)' => '装板补产工时',
  341. 'SUM(a.sczl_异常停机工时)' => '异常停机工时',
  342. 'a.sczl_ms' => '墨色数',
  343. 'rtrim(d.印刷方式)' => '印刷方式',
  344. 'rtrim(d.版距)' => '版距',
  345. 'rtrim(e.补产标准)' => '补产标准',
  346. 'rtrim(d.工价系数)' => '工价系数'
  347. ])
  348. ->where($where)
  349. ->group('a.sczl_rq, a.sczl_gdbh, a.sczl_gxh, a.sczl_jtbh')
  350. ->order('a.sczl_rq')
  351. ->page($page, $limit) // 使用 page 和 limit 技术实现分页
  352. ->select();
  353. // 数据处理
  354. if (!empty($list)) {
  355. foreach ($list as $key => $value) {
  356. $list[$key]['工序名称'] = $value['印件号'] . '-' . $value['工序号'] . '-' . $value['工序名称'];
  357. // 移除不需要的字段
  358. unset($list[$key]['印件号'], $list[$key]['工序号']);
  359. // 产量计算
  360. if ($value['印刷方式'] === '卷对卷') {
  361. $list[$key]['产量'] = round($value['产量'] / $value['版距'] * 1000);
  362. }
  363. // 墨色数修正
  364. if ($value['墨色数'] === '0.00') {
  365. $list[$key]['墨色数'] = '1.00';
  366. }
  367. // 计算实际每小时车头产量and实际每小时计件产量与补产标准差额
  368. $output = $list[$key]['产量'];
  369. $runningHours = $value['设备运行工时'] ?: 0.0001;
  370. $standardOutput = $value['补产标准'] ?: 0;
  371. $priceCoefficient = $value['工价系数'] ?: 0;
  372. //修正前公式
  373. // 实际每小时车头产量 = 产量 / 设备运行工时
  374. // $list[$key]['实际每小时车头产量'] = $runningHours > 0 ? round($output / $runningHours, 2) : 0;
  375. //修正后公式
  376. //实际每小时车头产量=产量/(设备运行工时-保养工时-打样总工时-装版总工时-异常停机工时)
  377. $totalHours = $value['设备运行工时'] ?: 0
  378. - $value['保养工时'] ?: 0
  379. - $value['打样总工时'] ?: 0
  380. - $value['装版总工时'] ?: 0
  381. - $value['异常停机工时'] ?: 0;
  382. $totalHours = max($totalHours, 0.0001); // 防止除数为0
  383. $list[$key]['实际每小时车头产量'] = round($output / $totalHours, 2);
  384. // 实际每小时计件产量与补产标准差额 = 实际每小时车头产量 * 计件系数 - 补产标准
  385. $list[$key]['实际每小时计件产量与补产标准差额'] = round(
  386. $list[$key]['实际每小时车头产量'] * $priceCoefficient - $standardOutput, 2);
  387. }
  388. }
  389. $this->success('成功', $list);
  390. }
  391. /**
  392. * 工序产出率月度统计报表
  393. * @return void
  394. * @throws \think\db\exception\DataNotFoundException
  395. * @throws \think\db\exception\ModelNotFoundException
  396. * @throws \think\exception\DbException
  397. */
  398. public function ProcessOutputRate()
  399. {
  400. if (!$this->request->isGet()) {
  401. $this->error('请求错误');
  402. }
  403. $param = $this->request->param();
  404. if (empty($param) || empty($param['year'])) {
  405. $this->error('参数错误');
  406. }
  407. // 1. 获取设备分组,作为工序依据
  408. $sist = ['胶印车间','凹丝印车间','印后车间','检验车间'];
  409. $processOrder = \db('设备_基本资料')
  410. ->whereNotNull('设备编组')
  411. ->whereIn('使用部门', $sist)
  412. ->group('设备编组')
  413. ->column('RTRIM(设备编组) as 设备编组');
  414. if (empty($processOrder)) {
  415. $this->success('成功', []);
  416. }
  417. // 2. 初始化结果数组(按工序)
  418. $result = [];
  419. foreach ($processOrder as $process) {
  420. $key = substr($process, 5, 6);
  421. $result[$key] = [];
  422. for ($i = 1; $i <= 12; $i++) {
  423. $result[$key][str_pad($i, 2, '0', STR_PAD_LEFT) . '月'] = '';
  424. }
  425. $result[$key]['平均值'] = '0.00%';
  426. }
  427. // 3. 汇总所有年份内数据,一次查出所有“工序-月份”数据
  428. $dataList = \db('工单_质量考核汇总')
  429. ->alias('a')
  430. ->join('工单_工艺资料 b', 'a.Gy0_gdbh = b.Gy0_gdbh AND a.印件及工序 = b.Gy0_yjno AND a.工序 = b.Gy0_gxh')
  431. ->join('工单_印件资料 c', 'a.Gy0_gdbh = c.Yj_Gdbh AND a.印件及工序 = c.yj_Yjno')
  432. ->join('成品入仓 d', 'a.Gy0_gdbh = d.jjcp_gdbh AND a.印件及工序 = d.jjcp_yjno')
  433. ->join('设备_基本资料 e', 'a.sczl_jtbh = e.设备编号')
  434. ->whereIn('e.设备编组', $processOrder)
  435. ->where('d.jjcp_smb', 'like', '末%')
  436. ->where('a.sys_ny', 'like', $param['year'] . '%')
  437. ->field([
  438. 'e.设备编组',
  439. 'a.sys_ny',
  440. 'SUM(a.班组制程废品/ a.联数) AS 制程废品',
  441. 'SUM(a.班组质检废品/ a.联数) AS 质检废品',
  442. 'SUM(a.班组产量/ a.联数) AS 产量'
  443. ])
  444. ->group('e.设备编组, a.sys_ny')
  445. ->select();
  446. //修正前计算方式
  447. // // 4. 处理统计数据,填充到$result
  448. // $annual = []; // 年均合格率累加用
  449. // foreach ($dataList as $item) {
  450. // $processKey = substr($item['设备编组'], 5, 6);
  451. // $monthKey = substr($item['sys_ny'], 4, 2) . '月';
  452. //
  453. // $output = floatval($item['产量']);
  454. // $waste = floatval($item['制程废品']) + floatval($item['质检废品']);
  455. // $rate = $output > 0 ? (1 - $waste / $output) * 100 : 0;
  456. // $result[$processKey][$monthKey] = sprintf('%.2f%%', $rate);
  457. //
  458. // if (!isset($annual[$processKey])) {
  459. // $annual[$processKey] = ['sum' => 0, 'count' => 0];
  460. // }
  461. // $annual[$processKey]['sum'] += $rate;
  462. // $annual[$processKey]['count']++;
  463. // }
  464. // // 5. 补充“平均值”
  465. // foreach ($annual as $processKey => $v) {
  466. // if ($v['count'] > 0) {
  467. // $result[$processKey]['平均值'] = sprintf('%.2f%%', $v['sum']/$v['count']);
  468. // }
  469. // }
  470. //修正后计算方式
  471. // 【用于算平均值】
  472. $yearTotal = [];
  473. // 4. 处理每月数据
  474. foreach ($dataList as $item) {
  475. $processKey = substr($item['设备编组'], 5, 6);
  476. $monthKey = substr($item['sys_ny'], 4, 2) . '月';
  477. $output = floatval($item['产量']);
  478. $waste = floatval($item['制程废品']) + floatval($item['质检废品']);
  479. $rate = $output > 0 ? (1 - $waste / $output) * 100 : 0;
  480. $result[$processKey][$monthKey] = sprintf('%.2f%%', $rate);
  481. // 【累计:总产量、总废品】
  482. if (!isset($yearTotal[$processKey])) {
  483. $yearTotal[$processKey] = [
  484. 'totalOutput' => 0,
  485. 'totalWaste' => 0
  486. ];
  487. }
  488. $yearTotal[$processKey]['totalOutput'] += $output;
  489. $yearTotal[$processKey]['totalWaste'] += $waste;
  490. }
  491. //打印总计
  492. // echo "<pre>";
  493. // print_r($yearTotal);
  494. // echo "<pre>";
  495. // 【平均值:用全年总和计算】
  496. foreach ($yearTotal as $processKey => $val) {
  497. $totalOutput = $val['totalOutput'];
  498. $totalWaste = $val['totalWaste'];
  499. if ($totalOutput > 0) {
  500. $avgRate = (1 - $totalWaste / $totalOutput) * 100;
  501. $result[$processKey]['平均值'] = sprintf('%.2f%%', $avgRate);
  502. } else {
  503. $result[$processKey]['平均值'] = '0.00%';
  504. }
  505. }
  506. // 6. 转前端格式
  507. $formattedData = [];
  508. foreach ($result as $process => $data) {
  509. $row = array_merge(['工序类型' => $process], $data);
  510. $formattedData[] = $row;
  511. }
  512. $this->success('成功', $formattedData);
  513. }
  514. /**
  515. * 获取年分数据
  516. * @return void
  517. */
  518. public function GetYear()
  519. {
  520. if ($this->request->isGet() === false){
  521. $this->error('请求错误');
  522. }
  523. $data = \db('工单_质量考核汇总')
  524. ->group('year')
  525. ->column('YEAR(STR_TO_DATE(sys_ny, "%Y%m")) as year');
  526. $this->success('成功',$data);
  527. }
  528. /**
  529. * 数据透视表
  530. * @return null
  531. * @throws \think\Exception
  532. * @throws \think\db\exception\DataNotFoundException
  533. * @throws \think\db\exception\ModelNotFoundException
  534. * @throws \think\exception\DbException
  535. */
  536. public function PoductData()
  537. {
  538. if (!$this->request->isGet()) {
  539. $this->error('请求错误');
  540. }
  541. $param = $this->request->param();
  542. if (empty($param['year'])) {
  543. $this->error('参数错误');
  544. }
  545. // 获取设备编组列表(已按编号排序)
  546. $processGroups = \db('设备_基本资料')
  547. ->whereNotNull('设备编组')
  548. ->group('设备编组')
  549. ->column('rtrim(设备编组) as 设备编组');
  550. // 构建基础查询
  551. $query = \db('工单_质量考核汇总')
  552. ->alias('a')
  553. ->field('
  554. a.sczl_jtbh AS 机台编号,
  555. a.Gy0_gdbh AS 工单编号,
  556. a.印件及工序 AS 工序号,
  557. a.产品名称 AS 印件名称,
  558. a.工序 AS 工序,
  559. a.工序名称,
  560. b.Gy0_gxmc,
  561. a.联数,
  562. a.班组产量,
  563. a.班组制程废品,
  564. a.班组质检废品,
  565. a.sczl_bzdh AS 班组编号,
  566. b.印刷方式,
  567. b.版距,
  568. DATE_FORMAT(a.入仓日期, \'%Y%m\') AS 完工年月,
  569. d.设备编组
  570. ')
  571. ->join('工单_工艺资料 b', 'a.Gy0_gdbh = b.Gy0_gdbh AND a.印件及工序 = b.Gy0_yjno AND a.工序 = b.Gy0_gxh')
  572. ->join('成品入仓 c', 'a.Gy0_gdbh = c.jjcp_gdbh AND a.印件及工序 = c.jjcp_yjno')
  573. ->join('设备_基本资料 d', 'a.sczl_jtbh = d.设备编号')
  574. ->where('a.sys_ny', 'like', $param['year'] . '%')
  575. ->where('a.工序名称', 'not like', '%机检%')
  576. ->where('c.jjcp_smb', 'like', '末%')
  577. ->where('a.工序名称', 'not like', '%拆片%');
  578. // 获取总数
  579. $total = clone $query;
  580. $total = $total->count();
  581. // 执行查询
  582. $rawList = isset($param['page'], $param['limit'])
  583. ? $query->page($param['page'])->limit($param['limit'])->select()
  584. : $query->select();
  585. // 构建分类映射表
  586. $groupMap = [];
  587. foreach ($processGroups as $index => $group) {
  588. // 提取机组名称核心词(去除编号和"机组"字样)
  589. $groupName = preg_replace('/^\d+、/', '', $group); // 移除开头编号
  590. $coreName = str_replace('机组', '', $groupName); // 移除尾部的"机组"
  591. $groupMap[] = [
  592. 'id' => $index + 1,
  593. 'name' => $groupName,
  594. 'core' => $coreName
  595. ];
  596. }
  597. // 处理分类标记
  598. $processedList = [];
  599. foreach ($rawList as $item) {
  600. foreach ($groupMap as $group) {
  601. // 使用核心词进行模糊匹配
  602. if (strpos($item['设备编组'], $group['core']) !== false) {
  603. $item['type_id'] = $group['id'];
  604. $item['type_name'] = $group['name'];
  605. $processedList[] = $item;
  606. continue 2;
  607. }
  608. }
  609. }
  610. // 按分类ID排序
  611. usort($processedList, function($a, $b) {
  612. return $a['type_id'] <=> $b['type_id'];
  613. });
  614. // 返回结构
  615. $data['data'] = $processedList;
  616. $data['total'] = $total;
  617. $data['filtered_total'] = count($processedList);
  618. return count($processedList) > 0
  619. ? $this->success('成功', $data)
  620. : $this->error('未找到数据');
  621. }
  622. /**
  623. * 月度色度数导出
  624. * @return void
  625. * @throws \think\db\exception\DataNotFoundException
  626. * @throws \think\db\exception\ModelNotFoundException
  627. * @throws \think\exception\DbException
  628. */
  629. public function ChromaticityNumber()
  630. {
  631. if (!$this->request->isGet()) {
  632. $this->error('请求错误');
  633. }
  634. $param = $this->request->param();
  635. if (empty($param['month'])) {
  636. $this->error('参数错误');
  637. }
  638. $month = date_create_from_format('Ym', $param['month'])->format('Y-m');
  639. $where = ['a.sczl_rq' => ['like', $month . '%']];
  640. $list = \db('设备_产量计酬')
  641. ->alias('a')
  642. ->join('工单_印件资料 c', 'a.sczl_gdbh = c.Yj_Gdbh AND a.sczl_yjno = c.yj_Yjno')
  643. ->join('工单_工艺资料 d', 'a.sczl_gdbh = d.Gy0_gdbh AND a.sczl_yjno = d.Gy0_yjno AND a.sczl_gxh = d.Gy0_gxh')
  644. ->join('设备_基本资料 e', 'a.sczl_jtbh = e.设备编号')
  645. ->field([
  646. 'a.sczl_gdbh' => '工单编号',
  647. 'a.sczl_yjno' => '印件号',
  648. 'a.sczl_gxh' => '工序号',
  649. 'd.Gy0_gxmc' => '工序名称',
  650. 'SUM(a.sczl_cl)' => '产量',
  651. 'a.sczl_ms' => '墨色数',
  652. 'rtrim(d.印刷方式)' => '印刷方式',
  653. 'rtrim(d.版距)' => '版距',
  654. 'rtrim(d.Gy0_SITE)' => '车间名称',
  655. 'rtrim(e.设备编组)' => '设备编组',
  656. 'a.sczl_jtbh'
  657. ])
  658. ->where($where)
  659. ->group('a.sczl_gdbh, a.sczl_yjno, a.sczl_gxh, a.sczl_rq, a.sczl_jtbh, a.sczl_bzdh')
  660. ->select();
  661. $data = [];
  662. if (!empty($list)) {
  663. foreach ($list as $key => $value) {
  664. // Convert ALL outputs to integers
  665. $value['产量'] = (int)round($value['产量']);
  666. if ($value['印刷方式'] === '卷对卷' && $value['版距'] != 0) {
  667. $value['产量'] = (int)round($value['产量'] / $value['版距'] * 1000);
  668. }
  669. if($value['墨色数'] == 0 && $value['设备编组'] !== '11、检品机组'){
  670. $value['墨色数'] = 1;
  671. }
  672. if($value['设备编组'] === '11、检品机组'){
  673. $value['墨色数'] = 0;
  674. }
  675. $data[$key] = [
  676. 'gdbh' => $value['工单编号'],
  677. 'ms' => $value['墨色数'],
  678. '产量' => $value['产量'],
  679. '墨色' => $value['产量'] * $value['墨色数'],
  680. '设备编组' => $value['设备编组']
  681. ];
  682. }
  683. }
  684. $grouped = [];
  685. foreach ($data as $item) {
  686. $key = substr($item['设备编组'], 5, 6);
  687. if (!isset($grouped[$key])) {
  688. $grouped[$key] = [
  689. '产量合计' => 0,
  690. '工序名称' => $key,
  691. '色度数' => 0
  692. ];
  693. }
  694. $grouped[$key]['产量合计'] += (int)$item['产量'];
  695. $grouped[$key]['色度数'] += (int)$item['墨色'];
  696. }
  697. foreach ($grouped as &$group) {
  698. $group['产量合计'] = (int)$group['产量合计'];
  699. $group['色度数'] = (int)$group['色度数'];
  700. }
  701. $this->success('成功', array_values($grouped));
  702. }
  703. /**
  704. * 年度质检废品率统计左侧菜单
  705. * @return void
  706. * @throws \think\db\exception\DataNotFoundException
  707. * @throws \think\db\exception\ModelNotFoundException
  708. * @throws \think\exception\DbException
  709. */
  710. public function QualityInspectionGetTab()
  711. {
  712. if (!$this->request->isGet()) {
  713. $this->error('请求错误');
  714. }
  715. $monthStats = \db('db_qczl')
  716. ->group('month')
  717. ->order('month desc')
  718. ->column("DATE_FORMAT(qczl_rq, '%Y%m') AS month");
  719. // 按年月层次结构归类(返回树形结构)
  720. $treeData = \db('db_qczl')
  721. ->field([
  722. "DATE_FORMAT(qczl_rq, '%Y') AS year",
  723. "DATE_FORMAT(qczl_rq, '%m') AS month",
  724. "COUNT(*) AS total"
  725. ])
  726. ->order('year desc,month desc')
  727. ->group('year, month')
  728. ->select();
  729. // 构建树形结构
  730. $result = [];
  731. foreach ($treeData as $item) {
  732. $year = $item['year'];
  733. $month = $year . $item['month'];
  734. if (!isset($result[$year])) {
  735. $result[$year] = [
  736. 'year' => $year,
  737. 'children' => []
  738. ];
  739. }
  740. $result[$year]['children'][] = [
  741. 'month' => $month,
  742. 'total' => $item['total']
  743. ];
  744. }
  745. $this->success('成功', $result);
  746. }
  747. /**
  748. * 年度质检废品统计右侧上方列表
  749. * @return void
  750. * @throws \think\db\exception\DataNotFoundException
  751. * @throws \think\db\exception\ModelNotFoundException
  752. * @throws \think\exception\DbException
  753. */
  754. public function QualityInspectionList()
  755. {
  756. if (!$this->request->isGet()) {
  757. $this->error('请求错误');
  758. }
  759. $param = $this->request->param();
  760. $where = ['年月' => ['like', $param['month'] . '%']];
  761. // 查询工单列表
  762. $workList = \db('rec_月度废品汇总')
  763. ->where($where)
  764. ->field("CONCAT(Gd_gdbh,'-',印件号) as 工单,实际投料")
  765. ->group('工单')
  766. ->select();
  767. $work = [];
  768. foreach ($workList as $item) {
  769. $work[$item['工单']] = $item['实际投料'];
  770. }
  771. // 查询工单对应的废品类别首字母(按工单和首字母分组)
  772. $WasteWorkList = \db('rec_月度废品汇总')
  773. ->where($where)
  774. ->field("CONCAT(Gd_gdbh,'-',印件号) as 工单, left(废品类别,1) as 废品类别首字母")
  775. ->group('工单, 废品类别首字母')
  776. ->order('废品类别首字母 asc')
  777. ->select();
  778. $waste = [];
  779. foreach ($WasteWorkList as $item) {
  780. if (!isset($waste[$item['废品类别首字母']])) {
  781. $waste[$item['废品类别首字母']] = [];
  782. }
  783. $waste[$item['废品类别首字母']][] = $item['工单'];
  784. }
  785. // 计算每个首字母的实际投料总和
  786. $res = [];
  787. foreach ($waste as $key => $item) {
  788. $res[$key] = array_sum(array_intersect_key($work, array_flip($item)));
  789. }
  790. // 查询废品类别对应的废品数量
  791. $wasteList = \db('rec_月度废品汇总')
  792. ->where($where)
  793. ->group('废品类别')
  794. ->order('废品类别 asc')
  795. ->column("sum(废品数量) as 废品数量", "废品类别");
  796. // 从废品类别数据计算每个首字母的废品数量总和
  797. $classification = [];
  798. foreach ($wasteList as $category => $amount) {
  799. $firstChar = substr($category, 0, 1);
  800. if (!isset($classification[$firstChar])) {
  801. $classification[$firstChar] = 0;
  802. }
  803. $classification[$firstChar] += $amount;
  804. }
  805. // 构建数据数组
  806. $data = [];
  807. foreach ($wasteList as $category => $amount) {
  808. $firstChar = substr($category, 0, 1);
  809. $number = $res[$firstChar] ?? 0;
  810. $data[] = [
  811. '废品类别' => $category,
  812. '实际投料' => $number,
  813. '废品数量' => $amount,
  814. '质检废品率' => (round($amount/$number, 7) * 100) . '%',
  815. ];
  816. }
  817. // 添加首字母合计行
  818. foreach ($classification as $firstChar => $amount) {
  819. $number = $res[$firstChar] ?? 0;
  820. $data[] = [
  821. '废品类别' => $firstChar . '-合计',
  822. '实际投料' => $number,
  823. '废品数量' => $amount,
  824. '质检废品率' => (round($amount/$number, 7) * 100) . '%',
  825. ];
  826. }
  827. $this->success('成功', $data);
  828. }
  829. //判断字符串首位是否为英文字母
  830. function isFirstCharEnglish($str) {
  831. if (mb_strlen($str) == 0) return false;
  832. $firstChar = mb_substr($str, 0, 1);
  833. return preg_match('/^[A-Za-z]$/u', $firstChar);
  834. }
  835. /**
  836. * 年度质检废品率统计下方列表
  837. * @return void
  838. * @throws \think\db\exception\DataNotFoundException
  839. * @throws \think\db\exception\ModelNotFoundException
  840. * @throws \think\exception\DbException
  841. */
  842. public function QualityInspectionDetailList()
  843. {
  844. if ($this->request->isGet() === false) {
  845. $this->error('请求错误');
  846. }
  847. $param = $this->request->param();
  848. $where = [
  849. '年月' => ['like',$param['month'].'%'],
  850. '废品类别' => $param['class']
  851. ];
  852. $list = \db('rec_月度废品汇总')
  853. ->where($where)
  854. ->field('年月,客户编号,客户名称,产品名称,Gd_gdbh as 工单编号,印件号,实际投料,废品类别,sum(废品数量) as 废品数量,质检完工时间')
  855. ->group('工单编号,印件号,废品类别')
  856. ->select();
  857. foreach ($list as $key => $item) {
  858. $list[$key]['质检废品率'] = (round($item['废品数量']/$item['实际投料'], 7)*100).'%';
  859. $list[$key]['年周数'] = substr($list[$key]['质检完工时间'],0,4).'年第'.date('W',strtotime($item['质检完工时间'])).'周';
  860. }
  861. $this->success('成功', $list);
  862. }
  863. /**
  864. * 创建月度废品率系数
  865. * @return void
  866. * @throws \think\db\exception\DataNotFoundException
  867. * @throws \think\db\exception\ModelNotFoundException
  868. * @throws \think\exception\DbException
  869. */
  870. public function QualityInspectionAdd()
  871. {
  872. if (!$this->request->isGet()) {
  873. $this->error('请求错误');
  874. }
  875. $param = $this->request->param();
  876. // 1. 修复WHERE条件(原来的第二个WHERE会覆盖第一个)
  877. $where = ['jjcp_sj' => ['between', [$param['startMonth'], $param['endMonth']]]];
  878. $where = ['jjcp_smb' => ['like', '末%']];
  879. // 2. 优化字段选择,只选择必要的字段
  880. $field = [
  881. 'a.qczl_gdbh', 'a.qczl_yjno', 'a.qczl_rq',
  882. 'c.成品编码', 'c.成品名称', 'd.规格',
  883. 'b.实际投料', 'e.客户编号', 'e.客户名称',
  884. "DATE_FORMAT(c.jjcp_sj, '%Y%m') AS ym",
  885. 'c.jjcp_cpdh', 'c.jjcp_cpmc'
  886. ];
  887. // 3. 添加fp_lb和fp_sl字段
  888. for ($i = 1; $i <= 13; $i++) {
  889. $field[] = "a.fp_lb{$i}";
  890. $field[] = "a.fp_sl{$i}";
  891. }
  892. // 4. 执行查询
  893. $list = \db('db_qczl')
  894. ->alias('a')
  895. ->join('工单_基本资料 b', 'a.qczl_gdbh = b.Gd_gdbh and a.qczl_yjno = b.行号', 'LEFT')
  896. ->join('成品入仓 c', 'a.qczl_gdbh = c.jjcp_gdbh and a.qczl_yjno = c.jjcp_yjno', 'LEFT')
  897. ->join('物料_存货编码 d', 'c.jjcp_cpdh = d.物料代码', 'LEFT')
  898. ->join('产品_基本资料 e', 'c.成品编码 = e.产品编号', 'LEFT')
  899. ->field($field)
  900. ->where($where)
  901. ->select();
  902. if (empty($list)) {
  903. $this->error('没有找到符合条件的数据');
  904. }
  905. $data = [];
  906. $currentTime = date('Y-m-d H:i:s');
  907. // 5. 优化循环处理
  908. foreach ($list as $item) {
  909. // 预先处理公共数据
  910. $commonData = [
  911. '年月' => $item['ym'],
  912. '客户编号' => $item['客户编号'],
  913. '客户名称' => $item['客户名称'],
  914. '产品类别' => $item['规格'],
  915. '产品编号' => $item['成品编码'],
  916. '产品名称' => $item['成品名称'],
  917. 'Gd_gdbh' => $item['qczl_gdbh'],
  918. '印件号' => $item['qczl_yjno'],
  919. 'Gd_cpdh' => $item['jjcp_cpdh'] ?? '',
  920. 'Gd_cpmc' => $item['jjcp_cpmc'] ?? '',
  921. '实际投料' => ($item['实际投料'] ?? 0) * 10000,
  922. '质检完工时间' => $item['qczl_rq'],
  923. 'sys_id' => $param['sys_id'],
  924. 'Sys_rq' => $currentTime
  925. ];
  926. // 处理13个废品类别
  927. for ($i = 1; $i <= 13; $i++) {
  928. $lbField = "fp_lb{$i}";
  929. $slField = "fp_sl{$i}";
  930. if (!empty($item[$lbField]) && $this->isFirstCharEnglish($item[$lbField]) && $item[$slField] != 0) {
  931. $data[] = array_merge($commonData, [
  932. '废品类别' => $item[$lbField],
  933. '废品数量' => $item[$slField] ?? 0
  934. ]);
  935. }
  936. }
  937. }
  938. if (empty($data)) {
  939. $this->error('没有符合条件的数据需要插入');
  940. }
  941. // 6. 分批插入避免单次数据量过大
  942. $chunks = array_chunk($data, 100); // 每批100条
  943. $successCount = 0;
  944. foreach ($chunks as $chunk) {
  945. $result = \db('rec_月度废品汇总')->insertAll($chunk);
  946. if ($result !== false) {
  947. $successCount += $result;
  948. }
  949. }
  950. if ($successCount > 0) {
  951. $this->success("成功插入 {$successCount} 条数据");
  952. } else {
  953. $this->error('插入数据失败');
  954. }
  955. }
  956. /**
  957. * 成品入仓印件名称分类
  958. * 含“小盒” -> 小盒;含“面彩/围条/内贴/上盖/下盖” -> 其他;其余 -> 条盒
  959. */
  960. private function CprcCategory($cpmc)
  961. {
  962. $cpmc = trim((string)$cpmc);
  963. if ($cpmc === '') {
  964. return '条盒';
  965. }
  966. if (strpos($cpmc, '小盒') !== false) {
  967. return '小盒';
  968. }
  969. foreach (['面彩', '围条', '内贴', '上盖', '下盖'] as $kw) {
  970. if (strpos($cpmc, $kw) !== false) {
  971. return '其他';
  972. }
  973. }
  974. return '条盒';
  975. }
  976. /**
  977. * 取成品入仓指定账期(年月)的入仓记录
  978. * @param string $ny 6位年月(YYYYMM) 或 4位年份(YYYY)
  979. */
  980. private function CprcMonthRows($ny)
  981. {
  982. $ny = (string)$ny;
  983. if (strlen($ny) === 4) {
  984. $year = (int)$ny;
  985. $start = $year . '-01-01 00:00:00';
  986. $end = ($year + 1) . '-01-01 00:00:00';
  987. } else {
  988. $year = (int)substr($ny, 0, 4);
  989. $month = (int)substr($ny, 4, 2);
  990. $start = date('Y-m-01 00:00:00', mktime(0, 0, 0, $month, 1, $year));
  991. $end = date('Y-m-01 00:00:00', mktime(0, 0, 0, $month + 1, 1, $year));
  992. }
  993. return \db('成品入仓')
  994. ->field('jjcp_gdbh, jjcp_yjno, jjcp_cpdh, jjcp_cpmc, jjcp_sl, jjcp_dw, jjcp_sj')
  995. ->where('jjcp_sj', '>=', $start)
  996. ->where('jjcp_sj', '<', $end)
  997. ->order('jjcp_sj desc')
  998. ->select();
  999. }
  1000. /**
  1001. * 成品入仓统计左侧菜单(年 -> 年月,值为入仓数量合计/10000)
  1002. */
  1003. public function CprctjLeft()
  1004. {
  1005. if ($this->request->isGet() === false) {
  1006. $this->error('请求错误');
  1007. }
  1008. $rows = \db('成品入仓')
  1009. ->field('jjcp_sj, jjcp_cpmc, jjcp_sl')
  1010. ->whereNotNull('jjcp_sj')
  1011. ->select();
  1012. // 先按月聚合
  1013. $months = [];
  1014. foreach ($rows as $row) {
  1015. $sj = trim((string)$row['jjcp_sj']);
  1016. if ($sj === '') {
  1017. continue;
  1018. }
  1019. $ny = substr(str_replace('-', '', substr($sj, 0, 10)), 0, 6);
  1020. if (!preg_match('/^\d{6}$/', $ny)) {
  1021. continue;
  1022. }
  1023. $cat = $this->CprcCategory($row['jjcp_cpmc']);
  1024. if ($cat === '其他') {
  1025. continue;
  1026. }
  1027. if (!isset($months[$ny])) {
  1028. $months[$ny] = ['小盒' => 0, '条盒' => 0];
  1029. }
  1030. $months[$ny][$cat] += (float)$row['jjcp_sl'];
  1031. }
  1032. krsort($months);
  1033. // 再按年聚合
  1034. $years = [];
  1035. foreach ($months as $ny => $item) {
  1036. $year = substr((string)$ny, 0, 4);
  1037. if (!isset($years[$year])) {
  1038. $years[$year] = ['小盒' => 0, '条盒' => 0, '月份' => []];
  1039. }
  1040. $years[$year]['小盒'] += $item['小盒'];
  1041. $years[$year]['条盒'] += $item['条盒'];
  1042. $years[$year]['月份'][] = [
  1043. '年月' => (string)$ny,
  1044. '小盒' => round($item['小盒'] / 10000, 3),
  1045. '条盒' => round($item['条盒'] / 10000, 3),
  1046. ];
  1047. }
  1048. krsort($years);
  1049. $data = [];
  1050. foreach ($years as $year => $item) {
  1051. $data[] = [
  1052. '年份' => (string)$year,
  1053. '小盒' => round($item['小盒'] / 10000, 2),
  1054. '条盒' => round($item['条盒'] / 10000, 2),
  1055. '月份' => $item['月份'],
  1056. ];
  1057. }
  1058. $this->success('成功', $data);
  1059. }
  1060. /**
  1061. * 成品入仓统计上方表格(按日期汇总)
  1062. */
  1063. public function CprctjList_hz()
  1064. {
  1065. if ($this->request->isGet() === false) {
  1066. $this->error('请求错误');
  1067. }
  1068. $param = $this->request->param();
  1069. $ny = trim((string)(isset($param['ym']) ? $param['ym'] : ''));
  1070. if (!preg_match('/^\d{4}(\d{2})?$/', $ny)) {
  1071. $this->error('参数错误');
  1072. }
  1073. $rows = $this->CprcMonthRows($ny);
  1074. $days = [];
  1075. foreach ($rows as $row) {
  1076. $day = substr(trim((string)$row['jjcp_sj']), 0, 10);
  1077. if (!isset($days[$day])) {
  1078. $days[$day] = [
  1079. '日期' => $day,
  1080. '小盒入仓数' => 0,
  1081. '条盒入仓数' => 0,
  1082. '其他类' => 0,
  1083. '入仓数量' => 0,
  1084. ];
  1085. }
  1086. $cat = $this->CprcCategory($row['jjcp_cpmc']);
  1087. $sl = (float)$row['jjcp_sl'];
  1088. if ($cat === '小盒') {
  1089. $days[$day]['小盒入仓数'] += $sl;
  1090. } elseif ($cat === '其他') {
  1091. $days[$day]['其他类'] += $sl;
  1092. } else {
  1093. $days[$day]['条盒入仓数'] += $sl;
  1094. }
  1095. $days[$day]['入仓数量'] += $sl;
  1096. }
  1097. krsort($days);
  1098. $this->success('成功', array_values($days));
  1099. }
  1100. /**
  1101. * 成品入仓统计下方表格(工单 + 印件明细)
  1102. * 客户信息成品入仓表中没有,取自 工单_基本资料
  1103. */
  1104. public function CprctjList_mx()
  1105. {
  1106. if ($this->request->isGet() === false) {
  1107. $this->error('请求错误');
  1108. }
  1109. $param = $this->request->param();
  1110. $ny = trim((string)(isset($param['ym']) ? $param['ym'] : ''));
  1111. if (!preg_match('/^\d{4}(\d{2})?$/', $ny)) {
  1112. $this->error('参数错误');
  1113. }
  1114. $date = trim((string)(isset($param['date']) ? $param['date'] : ''));
  1115. $rows = $this->CprcMonthRows($ny);
  1116. $list = [];
  1117. $gdbhList = [];
  1118. foreach ($rows as $row) {
  1119. $day = substr(trim((string)$row['jjcp_sj']), 0, 10);
  1120. if ($date !== '' && $day !== $date) {
  1121. continue;
  1122. }
  1123. $gdbh = trim((string)$row['jjcp_gdbh']);
  1124. $key = $gdbh . '|' . trim((string)$row['jjcp_cpdh']) . '|' . trim((string)$row['jjcp_cpmc']) . '|' . $day;
  1125. if (!isset($list[$key])) {
  1126. $list[$key] = [
  1127. '工单编号' => $gdbh,
  1128. '印件代号' => trim((string)$row['jjcp_cpdh']),
  1129. '印件名称' => trim((string)$row['jjcp_cpmc']),
  1130. '日期' => $day,
  1131. '小盒数' => 0,
  1132. '条盒数' => 0,
  1133. '其他类' => 0,
  1134. '入仓数量' => 0,
  1135. '单位' => trim((string)$row['jjcp_dw']),
  1136. '客户编号' => '',
  1137. '客户名称' => ''
  1138. ];
  1139. if ($gdbh !== '') {
  1140. $gdbhList[$gdbh] = 1;
  1141. }
  1142. }
  1143. $cat = $this->CprcCategory($row['jjcp_cpmc']);
  1144. $sl = (float)$row['jjcp_sl'];
  1145. if ($cat === '小盒') {
  1146. $list[$key]['小盒数'] += $sl;
  1147. } elseif ($cat === '其他') {
  1148. $list[$key]['其他类'] += $sl;
  1149. } else {
  1150. $list[$key]['条盒数'] += $sl;
  1151. }
  1152. $list[$key]['入仓数量'] += $sl;
  1153. }
  1154. // 客户信息:按工单编号取最新一条工单_基本资料
  1155. if (!empty($gdbhList)) {
  1156. $orders = \db('工单_基本资料')
  1157. ->field('Gd_gdbh, Gd_客户代号, Gd_客户名称')
  1158. ->whereIn('Gd_gdbh', array_keys($gdbhList))
  1159. ->order('Sys_rq desc')
  1160. ->select();
  1161. $customerMap = [];
  1162. foreach ($orders as $order) {
  1163. $gdbh = trim((string)$order['Gd_gdbh']);
  1164. if (!isset($customerMap[$gdbh])) {
  1165. $customerMap[$gdbh] = [
  1166. '编号' => trim((string)$order['Gd_客户代号']),
  1167. '名称' => trim((string)$order['Gd_客户名称'])
  1168. ];
  1169. }
  1170. }
  1171. foreach ($list as $key => $item) {
  1172. if (isset($customerMap[$item['工单编号']])) {
  1173. $list[$key]['客户编号'] = $customerMap[$item['工单编号']]['编号'];
  1174. $list[$key]['客户名称'] = $customerMap[$item['工单编号']]['名称'];
  1175. }
  1176. }
  1177. }
  1178. $data = array_values($list);
  1179. usort($data, function ($a, $b) {
  1180. if ($a['日期'] !== $b['日期']) {
  1181. return strcmp($b['日期'], $a['日期']);
  1182. }
  1183. return strcmp($a['工单编号'], $b['工单编号']);
  1184. });
  1185. $this->success('成功', $data);
  1186. }
  1187. /**
  1188. * 成品编码对应的客户信息(产品_基本资料)
  1189. */
  1190. private function CprcProductCustomer($codes)
  1191. {
  1192. $map = [];
  1193. $codes = array_values(array_unique(array_filter($codes, function ($code) {
  1194. return trim((string)$code) !== '';
  1195. })));
  1196. if (empty($codes)) {
  1197. return $map;
  1198. }
  1199. $rows = \db('产品_基本资料')
  1200. ->field('产品编号, 客户编号, 客户名称')
  1201. ->whereIn('产品编号', $codes)
  1202. ->select();
  1203. foreach ($rows as $row) {
  1204. $code = trim((string)$row['产品编号']);
  1205. if ($code === '' || isset($map[$code])) {
  1206. continue;
  1207. }
  1208. $map[$code] = [
  1209. '客户编号' => trim((string)$row['客户编号']),
  1210. '客户名称' => trim((string)$row['客户名称']),
  1211. ];
  1212. }
  1213. return $map;
  1214. }
  1215. /**
  1216. * 年度成品入仓统计左侧菜单(年 -> 客户)
  1217. */
  1218. public function YearcprctjLeft()
  1219. {
  1220. if ($this->request->isGet() === false) {
  1221. $this->error('请求错误');
  1222. }
  1223. $rows = \db('成品入仓')
  1224. ->field([
  1225. "DATE_FORMAT(jjcp_sj, '%Y') AS 年份",
  1226. '成品编码',
  1227. ])
  1228. ->where('jjcp_sj', '>=', '2000-01-01')
  1229. ->group('年份, 成品编码')
  1230. ->select();
  1231. $customerMap = $this->CprcProductCustomer(array_column($rows, '成品编码'));
  1232. $years = [];
  1233. foreach ($rows as $row) {
  1234. $year = trim((string)$row['年份']);
  1235. $code = trim((string)$row['成品编码']);
  1236. if ($year === '' || $year === '0' || !isset($customerMap[$code])) {
  1237. continue;
  1238. }
  1239. $customer = $customerMap[$code];
  1240. if ($customer['客户名称'] === '') {
  1241. continue;
  1242. }
  1243. if (!isset($years[$year])) {
  1244. $years[$year] = ['年份' => $year, '客户' => []];
  1245. }
  1246. $customerKey = $customer['客户编号'] !== '' ? $customer['客户编号'] : $customer['客户名称'];
  1247. $years[$year]['客户'][$customerKey] = [
  1248. '客户编号' => $customer['客户编号'],
  1249. '客户名称' => $customer['客户名称'],
  1250. ];
  1251. }
  1252. krsort($years);
  1253. $data = [];
  1254. foreach ($years as $year) {
  1255. ksort($year['客户']);
  1256. $year['客户'] = array_values($year['客户']);
  1257. $data[] = $year;
  1258. }
  1259. $this->success('成功', $data);
  1260. }
  1261. /**
  1262. * 年度成品入仓统计右侧表格
  1263. * 产品取自成品入仓(成品编码/成品名称),大箱数取自 db_包装计件 的 sczl_cl1-6,按 sczl_rq 月份汇总
  1264. */
  1265. public function YearcprctjList()
  1266. {
  1267. if ($this->request->isGet() === false) {
  1268. $this->error('请求错误');
  1269. }
  1270. $param = $this->request->param();
  1271. $year = trim((string)(isset($param['year']) ? $param['year'] : ''));
  1272. if (!preg_match('/^\d{4}$/', $year)) {
  1273. $this->error('参数错误');
  1274. }
  1275. $customer = trim((string)(isset($param['customer']) ? $param['customer'] : ''));
  1276. $search = trim((string)(isset($param['search']) ? $param['search'] : ''));
  1277. $start = $year . '-01-01 00:00:00';
  1278. $end = ((int)$year + 1) . '-01-01 00:00:00';
  1279. // 1、成品入仓:本年度有入仓记录的产品
  1280. $warehouseRows = \db('成品入仓')
  1281. ->field('jjcp_gdbh, 成品编码, 成品名称')
  1282. ->where('jjcp_sj', '>=', $start)
  1283. ->where('jjcp_sj', '<', $end)
  1284. ->where('jjcp_gdbh', '<>', '')
  1285. ->select();
  1286. $products = []; // 产品编号 => 行数据
  1287. $orderProduct = []; // 工单编号(大写) => 产品编号
  1288. foreach ($warehouseRows as $row) {
  1289. $code = trim((string)$row['成品编码']);
  1290. if ($code === '') {
  1291. continue;
  1292. }
  1293. if (!isset($products[$code])) {
  1294. $name = trim((string)$row['成品名称']);
  1295. $products[$code] = [
  1296. '产品编号' => $code,
  1297. '产品名称' => $name,
  1298. '产品类别' => strpos($name, '小盒') !== false ? '小盒' : '条盒',
  1299. '客户编号' => '',
  1300. '客户名称' => '',
  1301. 'monthly' => array_fill(1, 12, 0),
  1302. ];
  1303. }
  1304. $order = strtoupper(trim((string)$row['jjcp_gdbh']));
  1305. if ($order !== '' && !isset($orderProduct[$order])) {
  1306. $orderProduct[$order] = $code;
  1307. }
  1308. }
  1309. if (empty($products)) {
  1310. $this->success('成功', []);
  1311. }
  1312. // 2、包装计件:按 sczl_rq 的月份汇总对应工单的 sczl_cl1-6
  1313. $packRows = \db('db_包装计件')
  1314. ->field('sczl_rq, sczl_gdbh1, sczl_gdbh2, sczl_gdbh3, sczl_gdbh4, sczl_gdbh5, sczl_gdbh6, sczl_cl1, sczl_cl2, sczl_cl3, sczl_cl4, sczl_cl5, sczl_cl6')
  1315. ->where('sczl_rq', '>=', $start)
  1316. ->where('sczl_rq', '<', $end)
  1317. ->select();
  1318. foreach ($packRows as $row) {
  1319. $rq = str_replace('/', '-', trim((string)$row['sczl_rq']));
  1320. $month = (int)substr($rq, 5, 2);
  1321. if ($month < 1 || $month > 12) {
  1322. continue;
  1323. }
  1324. for ($i = 1; $i <= 6; $i++) {
  1325. $order = strtoupper(trim((string)$row['sczl_gdbh' . $i]));
  1326. if ($order === '' || !isset($orderProduct[$order])) {
  1327. continue;
  1328. }
  1329. $products[$orderProduct[$order]]['monthly'][$month] += (float)$row['sczl_cl' . $i];
  1330. }
  1331. }
  1332. // 3、客户信息:产品_基本资料(按成品编码)
  1333. $customerMap = $this->CprcProductCustomer(array_keys($products));
  1334. foreach ($products as $code => $product) {
  1335. if (isset($customerMap[$code])) {
  1336. $products[$code]['客户编号'] = $customerMap[$code]['客户编号'];
  1337. $products[$code]['客户名称'] = $customerMap[$code]['客户名称'];
  1338. }
  1339. }
  1340. // 4、组装数据:无数据的月份留空
  1341. $data = [];
  1342. foreach ($products as $product) {
  1343. if ($customer !== '' && $product['客户编号'] !== $customer) {
  1344. continue;
  1345. }
  1346. if ($search !== ''
  1347. && strpos($product['产品编号'], $search) === false
  1348. && strpos($product['产品名称'], $search) === false) {
  1349. continue;
  1350. }
  1351. $total = 0;
  1352. $months = [];
  1353. for ($m = 1; $m <= 12; $m++) {
  1354. $value = $product['monthly'][$m];
  1355. $total += $value;
  1356. $months[str_pad($m, 2, '0', STR_PAD_LEFT) . '月'] = $value > 0 ? round($value, 2) : '';
  1357. }
  1358. $data[] = array_merge([
  1359. '产品编号' => $product['产品编号'],
  1360. '产品名称' => $product['产品名称'],
  1361. '产品类别' => $product['产品类别'],
  1362. '每月合计' => round($total, 2),
  1363. ], $months, [
  1364. '客户编号' => $product['客户编号'],
  1365. '客户名称' => $product['客户名称'],
  1366. ]);
  1367. }
  1368. usort($data, function ($a, $b) {
  1369. return strcmp($a['产品编号'], $b['产品编号']);
  1370. });
  1371. // 5、底部合计行:小盒合计、条盒合计
  1372. $totalRows = [];
  1373. foreach (['小盒', '条盒'] as $category) {
  1374. $exists = false;
  1375. $total = 0;
  1376. $sums = array_fill(1, 12, 0);
  1377. foreach ($data as $row) {
  1378. if ($row['产品类别'] !== $category) {
  1379. continue;
  1380. }
  1381. $exists = true;
  1382. $total += (float)$row['每月合计'];
  1383. for ($m = 1; $m <= 12; $m++) {
  1384. $monthKey = str_pad($m, 2, '0', STR_PAD_LEFT) . '月';
  1385. $sums[$m] += $row[$monthKey] === '' ? 0 : (float)$row[$monthKey];
  1386. }
  1387. }
  1388. if (!$exists) {
  1389. continue;
  1390. }
  1391. $totalRow = [
  1392. '产品编号' => '',
  1393. '产品名称' => $category . '合计(大箱)',
  1394. '产品类别' => '',
  1395. '每月合计' => round($total, 2),
  1396. '合计类型' => $category,
  1397. ];
  1398. for ($m = 1; $m <= 12; $m++) {
  1399. $monthKey = str_pad($m, 2, '0', STR_PAD_LEFT) . '月';
  1400. $totalRow[$monthKey] = $sums[$m] > 0 ? round($sums[$m], 2) : '';
  1401. }
  1402. $totalRow['客户编号'] = '';
  1403. $totalRow['客户名称'] = '';
  1404. $totalRows[] = $totalRow;
  1405. }
  1406. $data = array_merge($data, $totalRows);
  1407. $this->success('成功', $data);
  1408. }
  1409. /**
  1410. * 客诉质量缺陷统计A 左侧菜单(年 -> 缺陷关键字)
  1411. */
  1412. public function KhtjLeftA()
  1413. {
  1414. if ($this->request->isGet() === false) {
  1415. $this->error('请求错误');
  1416. }
  1417. $rows = \db('db_客诉记录')
  1418. ->field("DATE_FORMAT(客诉日期, '%Y') AS 年份, rtrim(缺陷关键字) AS 缺陷关键字, count(1) AS num")
  1419. ->group("DATE_FORMAT(客诉日期, '%Y'), rtrim(缺陷关键字)")
  1420. ->select();
  1421. $years = [];
  1422. foreach ($rows as $row) {
  1423. $year = trim((string)$row['年份']);
  1424. if ($year === '' || $year === '0') {
  1425. continue;
  1426. }
  1427. if (!isset($years[$year])) {
  1428. $years[$year] = ['总数' => 0, '关键字' => []];
  1429. }
  1430. $num = (int)$row['num'];
  1431. $years[$year]['总数'] += $num;
  1432. $keyword = trim((string)$row['缺陷关键字']);
  1433. if ($keyword !== '') {
  1434. $years[$year]['关键字'][$keyword] = $num;
  1435. }
  1436. }
  1437. krsort($years);
  1438. $data = [];
  1439. foreach ($years as $year => $info) {
  1440. arsort($info['关键字']);
  1441. $children = [];
  1442. foreach ($info['关键字'] as $keyword => $num) {
  1443. $children[] = ['关键字' => $keyword, '次数' => $num];
  1444. }
  1445. $data[] = [
  1446. '年份' => $year,
  1447. '次数' => $info['总数'],
  1448. 'children' => $children,
  1449. ];
  1450. }
  1451. $this->success('成功', $data);
  1452. }
  1453. /**
  1454. * 客诉质量缺陷统计A 明细(年 + 缺陷关键字筛选)
  1455. */
  1456. public function KhtjListA()
  1457. {
  1458. if ($this->request->isGet() === false) {
  1459. $this->error('请求错误');
  1460. }
  1461. $param = $this->request->param();
  1462. $year = trim((string)(isset($param['year']) ? $param['year'] : ''));
  1463. if (!preg_match('/^\d{4}$/', $year)) {
  1464. $this->error('参数错误');
  1465. }
  1466. $keyword = trim((string)(isset($param['keyword']) ? $param['keyword'] : ''));
  1467. $search = trim((string)(isset($param['search']) ? $param['search'] : ''));
  1468. $data = $this->KhtjDetailRows(
  1469. $year . '-01-01 00:00:00',
  1470. ((int)$year + 1) . '-01-01 00:00:00',
  1471. '',
  1472. $keyword,
  1473. $search
  1474. );
  1475. $this->success('成功', $data);
  1476. }
  1477. /**
  1478. * 客诉质量缺陷统计B 左侧菜单(年 -> 客户)
  1479. */
  1480. public function KhtjLeftB()
  1481. {
  1482. if ($this->request->isGet() === false) {
  1483. $this->error('请求错误');
  1484. }
  1485. $rows = \db('db_客诉记录')
  1486. ->field("DATE_FORMAT(客诉日期, '%Y') AS 年份, rtrim(客户编号) AS 客户编号, rtrim(客户名称) AS 客户名称")
  1487. ->group("DATE_FORMAT(客诉日期, '%Y'), rtrim(客户编号), rtrim(客户名称)")
  1488. ->select();
  1489. $years = [];
  1490. foreach ($rows as $row) {
  1491. $year = trim((string)$row['年份']);
  1492. $code = trim((string)$row['客户编号']);
  1493. $name = trim((string)$row['客户名称']);
  1494. if ($year === '' || $year === '0' || $code === '') {
  1495. continue;
  1496. }
  1497. if (!isset($years[$year])) {
  1498. $years[$year] = [];
  1499. }
  1500. if (!isset($years[$year][$code]) && $name !== '') {
  1501. $years[$year][$code] = $name;
  1502. }
  1503. }
  1504. krsort($years);
  1505. $data = [];
  1506. foreach ($years as $year => $customers) {
  1507. ksort($customers);
  1508. $children = [];
  1509. foreach ($customers as $code => $name) {
  1510. $children[] = ['客户编号' => $code, '客户名称' => $name];
  1511. }
  1512. $data[] = ['年份' => $year, '客户' => $children];
  1513. }
  1514. $this->success('成功', $data);
  1515. }
  1516. /**
  1517. * 客诉质量缺陷统计B 月度汇总 + 明细
  1518. */
  1519. public function KhtjListB()
  1520. {
  1521. if ($this->request->isGet() === false) {
  1522. $this->error('请求错误');
  1523. }
  1524. $param = $this->request->param();
  1525. $year = trim((string)(isset($param['year']) ? $param['year'] : ''));
  1526. if (!preg_match('/^\d{4}$/', $year)) {
  1527. $this->error('参数错误');
  1528. }
  1529. $customer = trim((string)(isset($param['customer']) ? $param['customer'] : ''));
  1530. $search = trim((string)(isset($param['search']) ? $param['search'] : ''));
  1531. $start = $year . '-01-01 00:00:00';
  1532. $end = ((int)$year + 1) . '-01-01 00:00:00';
  1533. // 1、月度汇总:按客户统计全年及每月客诉次数(按客诉日期所在月份)
  1534. $summary = $this->KhtjSummaryRows($start, $end, $customer, $search);
  1535. // 2、客诉明细
  1536. $list = $this->KhtjDetailRows($start, $end, $customer, '', $search);
  1537. $this->success('成功', ['summary' => $summary, 'list' => $list]);
  1538. }
  1539. /**
  1540. * 客诉月度汇总(按客户统计全年及每月客诉次数)
  1541. */
  1542. private function KhtjSummaryRows($start, $end, $customer = '', $search = '')
  1543. {
  1544. $rows = \db('db_客诉记录')
  1545. ->field("rtrim(客户编号) AS 客户编号, rtrim(客户名称) AS 客户名称, DATE_FORMAT(客诉日期, '%m') AS 月")
  1546. ->where('客诉日期', '>=', $start)
  1547. ->where('客诉日期', '<', $end)
  1548. ->select();
  1549. $customers = [];
  1550. foreach ($rows as $row) {
  1551. $code = trim((string)$row['客户编号']);
  1552. if ($code === '') {
  1553. continue;
  1554. }
  1555. $month = (int)$row['月'];
  1556. if ($month < 1 || $month > 12) {
  1557. continue;
  1558. }
  1559. if (!isset($customers[$code])) {
  1560. $customers[$code] = [
  1561. '客户名称' => trim((string)$row['客户名称']),
  1562. '总数' => 0,
  1563. 'monthly' => array_fill(1, 12, 0),
  1564. ];
  1565. }
  1566. $customers[$code]['总数']++;
  1567. $customers[$code]['monthly'][$month]++;
  1568. }
  1569. $year = substr($start, 0, 4);
  1570. $summary = [];
  1571. foreach ($customers as $code => $info) {
  1572. if ($customer !== '' && $code !== $customer) {
  1573. continue;
  1574. }
  1575. if ($search !== '' && mb_stripos($code . ' ' . $info['客户名称'], $search) === false) {
  1576. continue;
  1577. }
  1578. $summaryRow = [
  1579. '年月' => $year,
  1580. '客户编号' => $code,
  1581. '客户名称' => $info['客户名称'],
  1582. '年度投诉次数' => $info['总数'],
  1583. ];
  1584. for ($m = 1; $m <= 12; $m++) {
  1585. $summaryRow['月份' . str_pad($m, 2, '0', STR_PAD_LEFT)] = $info['monthly'][$m] > 0 ? $info['monthly'][$m] : '';
  1586. }
  1587. $summary[] = $summaryRow;
  1588. }
  1589. usort($summary, function ($a, $b) {
  1590. return strcmp($a['客户编号'], $b['客户编号']);
  1591. });
  1592. return $summary;
  1593. }
  1594. /**
  1595. * 客诉明细查询(统计A/B共用)
  1596. */
  1597. private function KhtjDetailRows($start, $end, $customer = '', $keyword = '', $search = '')
  1598. {
  1599. $field = 'rtrim(客诉单号) AS 客诉单号, 客诉日期, rtrim(客诉方式) AS 客诉方式, rtrim(订单编号) AS 订单编号, '
  1600. . 'rtrim(产品编号) AS 产品编号, rtrim(产品名称) AS 产品名称, rtrim(客户编号) AS 客户编号, rtrim(客户名称) AS 客户名称, '
  1601. . 'rtrim(客诉描述) AS 客诉描述, rtrim(缺陷关键字) AS 缺陷关键字, rtrim(客诉性质) AS 客诉性质, 扣分, '
  1602. . 'rtrim(主要责任部门) AS 主要责任部门, rtrim(次要责任部门) AS 次要责任部门, rtrim(Sys_id) AS 创建用户, '
  1603. . 'Sys_rq AS 创建时间, 关闭时间 AS 结案时间, UniqID';
  1604. $query = \db('db_客诉记录')
  1605. ->field($field)
  1606. ->where('客诉日期', '>=', $start)
  1607. ->where('客诉日期', '<', $end);
  1608. if ($customer !== '') {
  1609. $query->where('客户编号', $customer);
  1610. }
  1611. if ($keyword !== '') {
  1612. $query->where('缺陷关键字', $keyword);
  1613. }
  1614. if ($search !== '') {
  1615. $query->where('客诉单号|订单编号|产品编号|产品名称|客户编号|客户名称|客诉描述|缺陷关键字', 'like', '%' . $search . '%');
  1616. }
  1617. return $query->order('客诉日期 desc')->select();
  1618. }
  1619. /**
  1620. * 年度打样效率统计 左侧菜单(年 -> 年月)
  1621. */
  1622. public function DyxlLeft()
  1623. {
  1624. if ($this->request->isGet() === false) {
  1625. $this->error('请求错误');
  1626. }
  1627. $list = \db('工单_基本资料')
  1628. ->field([
  1629. "DATE_FORMAT(工单完工日期, '%Y') AS 年份",
  1630. "DATE_FORMAT(工单完工日期, '%Y%m') AS 年月",
  1631. ])
  1632. ->where('gd_statu', '1-已完工')
  1633. ->whereRaw($this->DyxlOrderWhere())
  1634. ->whereRaw('工单完工日期 IS NOT NULL')
  1635. ->group("DATE_FORMAT(工单完工日期, '%Y'), DATE_FORMAT(工单完工日期, '%Y%m')")
  1636. ->orderRaw("DATE_FORMAT(工单完工日期, '%Y%m') DESC")
  1637. ->select();
  1638. $years = [];
  1639. foreach ($list as $row) {
  1640. $year = trim((string)$row['年份']);
  1641. $ym = trim((string)$row['年月']);
  1642. if ($year === '' || $ym === '') {
  1643. continue;
  1644. }
  1645. if (!isset($years[$year])) {
  1646. $years[$year] = [];
  1647. }
  1648. if (!in_array($ym, $years[$year], true)) {
  1649. $years[$year][] = $ym;
  1650. }
  1651. }
  1652. $data = [];
  1653. foreach ($years as $year => $months) {
  1654. rsort($months);
  1655. $data[] = ['年份' => (string)$year, '月份' => $months];
  1656. }
  1657. usort($data, function ($a, $b) {
  1658. return strcmp($b['年份'], $a['年份']);
  1659. });
  1660. $this->success('成功', $data);
  1661. }
  1662. /**
  1663. * 年度打样效率统计 汇总 + 明细
  1664. */
  1665. public function DyxlList()
  1666. {
  1667. if ($this->request->isGet() === false) {
  1668. $this->error('请求错误');
  1669. }
  1670. $param = $this->request->param();
  1671. $date = trim((string)(isset($param['date']) ? $param['date'] : ''));
  1672. if (!preg_match('/^(\d{4})(\d{2})?$/', $date, $match)) {
  1673. $this->error('参数错误');
  1674. }
  1675. $year = (int)$match[1];
  1676. if (isset($match[2]) && $match[2] !== '') {
  1677. $month = (int)$match[2];
  1678. if ($month < 1 || $month > 12) {
  1679. $this->error('参数错误');
  1680. }
  1681. $start = sprintf('%04d-%02d-01', $year, $month);
  1682. if ($month === 12) {
  1683. $end = sprintf('%04d-01-01', $year + 1);
  1684. } else {
  1685. $end = sprintf('%04d-%02d-01', $year, $month + 1);
  1686. }
  1687. } else {
  1688. $start = sprintf('%04d-01-01', $year);
  1689. $end = sprintf('%04d-01-01', $year + 1);
  1690. }
  1691. // 1、打样工单(按工单编号去重)
  1692. $orders = \db('工单_基本资料')
  1693. ->field([
  1694. "RTRIM(Gd_gdbh) AS 工单编号",
  1695. "DATE_FORMAT(工单完工日期, '%Y%m') AS 年月",
  1696. "RTRIM(Gd_客户代号) AS 客户编号",
  1697. "RTRIM(Gd_客户名称) AS 客户名称",
  1698. "RTRIM(成品名称) AS 成品名称",
  1699. ])
  1700. ->where('gd_statu', '1-已完工')
  1701. ->where('工单完工日期', '>=', $start)
  1702. ->where('工单完工日期', '<', $end)
  1703. ->whereRaw($this->DyxlOrderWhere())
  1704. ->order('工单编号')
  1705. ->select();
  1706. $orderMap = [];
  1707. foreach ($orders as $row) {
  1708. $gdbh = trim((string)$row['工单编号']);
  1709. if ($gdbh === '') {
  1710. continue;
  1711. }
  1712. if (!isset($orderMap[$gdbh])) {
  1713. $orderMap[$gdbh] = [
  1714. '年月' => trim((string)$row['年月']),
  1715. '客户编号' => trim((string)$row['客户编号']),
  1716. '客户名称' => trim((string)$row['客户名称']),
  1717. '工单编号' => $gdbh,
  1718. '成品名称' => trim((string)$row['成品名称']),
  1719. ];
  1720. } else {
  1721. if ($orderMap[$gdbh]['客户编号'] === '') {
  1722. $orderMap[$gdbh]['客户编号'] = trim((string)$row['客户编号']);
  1723. }
  1724. if ($orderMap[$gdbh]['客户名称'] === '') {
  1725. $orderMap[$gdbh]['客户名称'] = trim((string)$row['客户名称']);
  1726. }
  1727. if ($orderMap[$gdbh]['成品名称'] === '') {
  1728. $orderMap[$gdbh]['成品名称'] = trim((string)$row['成品名称']);
  1729. }
  1730. }
  1731. }
  1732. if (empty($orderMap)) {
  1733. $this->success('成功', ['summary' => [], 'detail' => []]);
  1734. }
  1735. $gdbhs = array_keys($orderMap);
  1736. $categories = ['胶印', '凹印', '单凹', '喷码', '丝印', '烫金', '模切'];
  1737. // 2、工艺资料:工单 + 印件 + 工序
  1738. $crafts = \db('工单_工艺资料')
  1739. ->field([
  1740. "RTRIM(Gy0_gdbh) AS 工单编号",
  1741. "FLOOR(Gy0_yjno) AS 印件号",
  1742. "FLOOR(Gy0_gxh) AS 工序号",
  1743. "RTRIM(Gy0_gxmc) AS 工序名称",
  1744. "RTRIM(Gy0_sbbh) AS 设备编号",
  1745. "Gy0_计划接货数 AS 计划接货数",
  1746. "Gy0_ks AS 开数",
  1747. "Gy0_ls AS 印数",
  1748. "Gy0_小时产能 AS 小时产能",
  1749. ])
  1750. ->whereIn('Gy0_gdbh', $gdbhs)
  1751. ->order('工单编号, 印件号, 工序号')
  1752. ->select();
  1753. // 3、实际工时:设备_产量计酬 中同工单+印件+工序、设备运行工时不为0的记录
  1754. $actualRows = \db('设备_产量计酬')
  1755. ->field([
  1756. "RTRIM(sczl_gdbh) AS 工单编号",
  1757. "FLOOR(sczl_yjno) AS 印件号",
  1758. "FLOOR(sczl_gxh) AS 工序号",
  1759. "MAX(sczl_设备运行工时) AS 实际工时",
  1760. ])
  1761. ->whereIn('sczl_gdbh', $gdbhs)
  1762. ->where('sczl_设备运行工时', '<>', 0)
  1763. ->group('RTRIM(sczl_gdbh), FLOOR(sczl_yjno), FLOOR(sczl_gxh)')
  1764. ->select();
  1765. $actualMap = [];
  1766. foreach ($actualRows as $row) {
  1767. $actualMap[$this->DyxlKey($row['工单编号'], $row['印件号'], $row['工序号'])] = (float)$row['实际工时'];
  1768. }
  1769. $sumPlan = [];
  1770. $sumActual = [];
  1771. $detail = [];
  1772. foreach ($crafts as $craft) {
  1773. $gdbh = trim((string)$craft['工单编号']);
  1774. if (!isset($orderMap[$gdbh])) {
  1775. continue;
  1776. }
  1777. $category = $this->DyxlProcessCategory($craft['设备编号'], $craft['工序名称']);
  1778. if ($category === '') {
  1779. continue;
  1780. }
  1781. $yjno = (int)$craft['印件号'];
  1782. $gxh = (int)$craft['工序号'];
  1783. $plan = $this->DyxlPlanHours($craft);
  1784. $key = $this->DyxlKey($gdbh, $yjno, $gxh);
  1785. $actual = isset($actualMap[$key]) ? $actualMap[$key] : null;
  1786. if ($plan !== null) {
  1787. if (!isset($sumPlan[$gdbh][$category])) {
  1788. $sumPlan[$gdbh][$category] = 0;
  1789. }
  1790. $sumPlan[$gdbh][$category] += $plan;
  1791. }
  1792. if ($actual !== null) {
  1793. if (!isset($sumActual[$gdbh][$category])) {
  1794. $sumActual[$gdbh][$category] = 0;
  1795. }
  1796. $sumActual[$gdbh][$category] += $actual;
  1797. }
  1798. $detailRow = $orderMap[$gdbh];
  1799. $detailRow['印件及工序'] = str_pad($gxh, 2, '0', STR_PAD_LEFT) . '-' . trim((string)$craft['工序名称']);
  1800. foreach ($categories as $cat) {
  1801. $detailRow[$cat . '计划工时'] = '';
  1802. $detailRow[$cat . '实际工时'] = '';
  1803. }
  1804. if ($plan !== null) {
  1805. $detailRow[$category . '计划工时'] = $this->DyxlNumber($plan);
  1806. }
  1807. if ($actual !== null) {
  1808. $detailRow[$category . '实际工时'] = $this->DyxlNumber($actual);
  1809. }
  1810. $detail[] = $detailRow;
  1811. }
  1812. // 4、按工单汇总
  1813. $summary = [];
  1814. $totalPlan = array_fill_keys($categories, 0);
  1815. $totalActual = array_fill_keys($categories, 0);
  1816. $hasPlan = [];
  1817. $hasActual = [];
  1818. foreach ($orderMap as $gdbh => $info) {
  1819. $row = $info;
  1820. foreach ($categories as $cat) {
  1821. $plan = isset($sumPlan[$gdbh][$cat]) ? $sumPlan[$gdbh][$cat] : null;
  1822. $actual = isset($sumActual[$gdbh][$cat]) ? $sumActual[$gdbh][$cat] : null;
  1823. $row[$cat . '计划工时'] = $plan === null ? '' : $this->DyxlNumber($plan);
  1824. $row[$cat . '实际工时'] = $actual === null ? '' : $this->DyxlNumber($actual);
  1825. if ($plan !== null) {
  1826. $totalPlan[$cat] += $plan;
  1827. $hasPlan[$cat] = true;
  1828. }
  1829. if ($actual !== null) {
  1830. $totalActual[$cat] += $actual;
  1831. $hasActual[$cat] = true;
  1832. }
  1833. }
  1834. $summary[] = $row;
  1835. }
  1836. $totalRow = ['年月' => '合计:', '客户编号' => '', '客户名称' => '', '工单编号' => '', '成品名称' => ''];
  1837. foreach ($categories as $cat) {
  1838. $totalRow[$cat . '计划工时'] = !empty($hasPlan[$cat]) ? $this->DyxlNumber($totalPlan[$cat]) : '';
  1839. $totalRow[$cat . '实际工时'] = !empty($hasActual[$cat]) ? $this->DyxlNumber($totalActual[$cat]) : '';
  1840. }
  1841. $totalRow['isTotal'] = true;
  1842. if (!empty($summary)) {
  1843. $summary[] = $totalRow;
  1844. }
  1845. $this->success('成功', ['summary' => $summary, 'detail' => $detail]);
  1846. }
  1847. /**
  1848. * 打样工单条件:工单编号第3位(带Y前缀则第4位)为8
  1849. */
  1850. private function DyxlOrderWhere()
  1851. {
  1852. return "SUBSTRING(RTRIM(Gd_gdbh), IF(LEFT(RTRIM(Gd_gdbh), 1) = 'Y', 4, 3), 1) = '8'";
  1853. }
  1854. /**
  1855. * 按设备编号前缀判定工序类别
  1856. */
  1857. private function DyxlProcessCategory($sbbh, $gxmc)
  1858. {
  1859. $map = ['YJY' => '胶印', 'YWY' => '凹印', 'YDW' => '单凹', 'YPM' => '喷码', 'YSY' => '丝印', 'YTJ' => '烫金', 'YMQ' => '模切'];
  1860. $prefix = strtoupper(substr(trim((string)$sbbh), 0, 3));
  1861. if (isset($map[$prefix])) {
  1862. return $map[$prefix];
  1863. }
  1864. $name = trim((string)$gxmc);
  1865. foreach (['单凹' => '单凹', '凹印' => '凹印', '卷凹' => '凹印', '胶印' => '胶印', '喷码' => '喷码', '丝印' => '丝印', '烫金' => '烫金', '模切' => '模切'] as $keyword => $category) {
  1866. if (strpos($name, $keyword) !== false) {
  1867. return $category;
  1868. }
  1869. }
  1870. return '';
  1871. }
  1872. /**
  1873. * 计划工时 = 计划接货数 * 开数 * 印数 / 小时产能
  1874. */
  1875. private function DyxlPlanHours($craft)
  1876. {
  1877. $qty = (float)$craft['计划接货数'];
  1878. $ks = (float)$craft['开数'];
  1879. $ls = (float)$craft['印数'];
  1880. $capacity = (float)$craft['小时产能'];
  1881. if ($qty <= 0 || $ks <= 0 || $ls <= 0 || $capacity <= 0) {
  1882. return null;
  1883. }
  1884. return $qty * $ks * $ls / $capacity;
  1885. }
  1886. private function DyxlKey($gdbh, $yjno, $gxh)
  1887. {
  1888. return trim((string)$gdbh) . '|' . (int)$yjno . '|' . (int)$gxh;
  1889. }
  1890. private function DyxlNumber($value)
  1891. {
  1892. return number_format(round((float)$value, 2), 2, '.', '');
  1893. }
  1894. /**
  1895. * 年度手工检验统计 左侧菜单(年 -> 客户)
  1896. * 两阶段:先查手工检验取工单号 → 再工单_基本资料去重取客户
  1897. */
  1898. public function YearSgyjLeft()
  1899. {
  1900. if ($this->request->isGet() === false) {
  1901. $this->error('请求错误');
  1902. }
  1903. $inspectRows = \db('db_手工检验')
  1904. ->field([
  1905. "DATE_FORMAT(sczl_rq, '%Y') AS 年份",
  1906. 'UPPER(RTRIM(sczl_gdbh)) AS 工单编号',
  1907. ])
  1908. ->where('sczl_rq', '>=', '2000-01-01')
  1909. ->group('年份, 工单编号')
  1910. ->select();
  1911. if (empty($inspectRows)) {
  1912. $this->success('成功', []);
  1913. }
  1914. // 提取工单编号,查工单_基本资料并按 Gd_gdbh 去重
  1915. $gdbhs = array_values(array_unique(array_column($inspectRows, '工单编号')));
  1916. $orderMap = $this->YearSgyjOrderMap($gdbhs);
  1917. $years = [];
  1918. foreach ($inspectRows as $row) {
  1919. $year = trim((string)$row['年份']);
  1920. $gdbh = $row['工单编号'];
  1921. if ($year === '' || !isset($orderMap[$gdbh])) {
  1922. continue;
  1923. }
  1924. $info = $orderMap[$gdbh];
  1925. $code = $info['客户编号'];
  1926. $name = $info['客户名称'];
  1927. if ($code === '' && $name === '') {
  1928. continue;
  1929. }
  1930. if (!isset($years[$year])) {
  1931. $years[$year] = ['年份' => $year, '客户' => []];
  1932. }
  1933. $customerKey = $code !== '' ? $code : $name;
  1934. if (!isset($years[$year]['客户'][$customerKey])) {
  1935. $years[$year]['客户'][$customerKey] = [
  1936. '客户编号' => $code,
  1937. '客户名称' => $name,
  1938. ];
  1939. }
  1940. }
  1941. krsort($years);
  1942. $data = [];
  1943. foreach ($years as $year) {
  1944. ksort($year['客户']);
  1945. $year['客户'] = array_values($year['客户']);
  1946. $data[] = $year;
  1947. }
  1948. $this->success('成功', $data);
  1949. }
  1950. /**
  1951. * 年度手工检验统计 右侧数据
  1952. * 两阶段:先查手工检验 → 工单_基本资料按 Gd_gdbh 去重后 PHP 关联
  1953. */
  1954. public function YearSgyjList()
  1955. {
  1956. if ($this->request->isGet() === false) {
  1957. $this->error('请求错误');
  1958. }
  1959. $param = $this->request->param();
  1960. $year = trim((string)(isset($param['year']) ? $param['year'] : ''));
  1961. if (!preg_match('/^\d{4}$/', $year)) {
  1962. $this->error('参数错误');
  1963. }
  1964. $customer = trim((string)(isset($param['customer']) ? $param['customer'] : ''));
  1965. $search = trim((string)(isset($param['search']) ? $param['search'] : ''));
  1966. $start = $year . '-01-01 00:00:00';
  1967. $end = ((int)$year + 1) . '-01-01 00:00:00';
  1968. // 1、手工检验数据(不 join)
  1969. $inspectRows = \db('db_手工检验')
  1970. ->field([
  1971. "DATE_FORMAT(sczl_rq, '%Y%m') AS 年月",
  1972. 'UPPER(RTRIM(sczl_gdbh)) AS 工单编号',
  1973. 'DATE_FORMAT(sczl_rq, \'%m\') AS 月份',
  1974. '(sczl_cl0+sczl_cl1+sczl_cl2+sczl_cl3+sczl_cl4+sczl_cl5+sczl_cl6+sczl_cl7+sczl_cl8+sczl_cl9+sczl_cl10+sczl_cl11+sczl_cl12) AS 总产量',
  1975. ])
  1976. ->where('sczl_rq', '>=', $start)
  1977. ->where('sczl_rq', '<', $end)
  1978. ->select();
  1979. if (empty($inspectRows)) {
  1980. $this->success('成功', ['summary' => [], 'detail' => []]);
  1981. }
  1982. // 2、工单编号 → 工单信息(去重)
  1983. $gdbhs = array_values(array_unique(array_filter(array_column($inspectRows, '工单编号'))));
  1984. $orderMap = $this->YearSgyjOrderMap($gdbhs);
  1985. // 3、组装完整行:合并工单信息到手工检验
  1986. $mergedRows = [];
  1987. foreach ($inspectRows as $row) {
  1988. $gdbh = $row['工单编号'];
  1989. if (!isset($orderMap[$gdbh])) {
  1990. continue;
  1991. }
  1992. $info = $orderMap[$gdbh];
  1993. $mergedRows[] = [
  1994. '年月' => trim((string)$row['年月']),
  1995. '工单编号' => $gdbh,
  1996. '月份' => (int)$row['月份'],
  1997. '总产量' => (float)$row['总产量'],
  1998. '产品编号' => $info['产品编号'],
  1999. '产品名称' => $info['产品名称'],
  2000. '客户编号' => $info['客户编号'],
  2001. '客户名称' => $info['客户名称'],
  2002. '产品类别' => $info['产品类别'],
  2003. ];
  2004. }
  2005. // 4、客户过滤 + 搜索过滤
  2006. if ($customer !== '') {
  2007. $mergedRows = array_values(array_filter($mergedRows, function ($r) use ($customer) {
  2008. return $r['客户编号'] === $customer;
  2009. }));
  2010. }
  2011. if ($search !== '') {
  2012. $mergedRows = array_values(array_filter($mergedRows, function ($r) use ($search) {
  2013. return strpos($r['产品编号'], $search) !== false || strpos($r['产品名称'], $search) !== false;
  2014. }));
  2015. }
  2016. if (empty($mergedRows)) {
  2017. $this->success('成功', ['summary' => [], 'detail' => []]);
  2018. }
  2019. // 5、summary:按 产品编号 聚合(产品编号为空时用产品名称兜底),按月份分月汇总
  2020. $products = [];
  2021. foreach ($mergedRows as $row) {
  2022. $code = $row['产品编号'];
  2023. if ($code === '') {
  2024. continue;
  2025. }
  2026. $m = $row['月份'];
  2027. if ($m < 1 || $m > 12) {
  2028. continue;
  2029. }
  2030. if (!isset($products[$code])) {
  2031. $products[$code] = [
  2032. '产品编号' => $code,
  2033. '产品名称' => $row['产品名称'],
  2034. '产品类别' => $row['产品类别'],
  2035. '客户编号' => $row['客户编号'],
  2036. '客户名称' => $row['客户名称'],
  2037. 'monthly' => array_fill(1, 12, 0),
  2038. ];
  2039. }
  2040. $products[$code]['monthly'][$m] += $row['总产量'];
  2041. }
  2042. // 6、组装 summary 数组
  2043. $summary = [];
  2044. foreach ($products as $product) {
  2045. $totalAll = 0;
  2046. $months = [];
  2047. for ($m = 1; $m <= 12; $m++) {
  2048. $value = $product['monthly'][$m];
  2049. $totalAll += $value;
  2050. $months[str_pad($m, 2, '0', STR_PAD_LEFT) . '月'] = $value > 0 ? round($value, 2) : '';
  2051. }
  2052. $summary[] = array_merge([
  2053. '产品编号' => $product['产品编号'],
  2054. '产品名称' => $product['产品名称'],
  2055. '产品类别' => $product['产品类别'],
  2056. '每月合计' => round($totalAll, 2),
  2057. ], $months, [
  2058. '客户编号' => $product['客户编号'],
  2059. '客户名称' => $product['客户名称'],
  2060. ]);
  2061. }
  2062. usort($summary, function ($a, $b) {
  2063. return strcmp($a['产品编号'], $b['产品编号']);
  2064. });
  2065. // 7、summary 合计行:小盒合计、条盒合计
  2066. $totalRows = [];
  2067. foreach (['小盒', '条盒'] as $category) {
  2068. $exists = false;
  2069. $total = 0;
  2070. $sums = array_fill(1, 12, 0);
  2071. foreach ($summary as $row) {
  2072. if ($row['产品类别'] !== $category) {
  2073. continue;
  2074. }
  2075. $exists = true;
  2076. $total += (float)$row['每月合计'];
  2077. for ($m = 1; $m <= 12; $m++) {
  2078. $mk = str_pad($m, 2, '0', STR_PAD_LEFT) . '月';
  2079. $sums[$m] += $row[$mk] === '' ? 0 : (float)$row[$mk];
  2080. }
  2081. }
  2082. if (!$exists) continue;
  2083. $totalRow = [
  2084. '产品编号' => $category . '合计',
  2085. '产品名称' => '',
  2086. '产品类别' => '',
  2087. '每月合计' => round($total, 2),
  2088. '合计类型' => true,
  2089. '客户编号' => '',
  2090. '客户名称' => '',
  2091. ];
  2092. for ($m = 1; $m <= 12; $m++) {
  2093. $totalRow[str_pad($m, 2, '0', STR_PAD_LEFT) . '月'] = round($sums[$m], 2);
  2094. }
  2095. $totalRows[] = $totalRow;
  2096. }
  2097. $summary = array_merge($summary, $totalRows);
  2098. // 8、detail:按 工单编号+年月 聚合
  2099. $detailMap = [];
  2100. foreach ($mergedRows as $row) {
  2101. $dKey = $row['工单编号'] . '|' . $row['年月'];
  2102. if (!isset($detailMap[$dKey])) {
  2103. $detailMap[$dKey] = [
  2104. '年月' => $row['年月'],
  2105. '工单编号' => $row['工单编号'],
  2106. '产品编号' => $row['产品编号'],
  2107. '产品名称' => $row['产品名称'],
  2108. '产品类别' => $row['产品类别'],
  2109. '总产量' => 0,
  2110. '客户编号' => $row['客户编号'],
  2111. '客户名称' => $row['客户名称'],
  2112. ];
  2113. }
  2114. $detailMap[$dKey]['总产量'] += $row['总产量'];
  2115. }
  2116. $detail = array_values($detailMap);
  2117. usort($detail, function ($a, $b) {
  2118. $c = strcmp($a['年月'], $b['年月']);
  2119. if ($c !== 0) return $c;
  2120. return strcmp($a['工单编号'], $b['工单编号']);
  2121. });
  2122. foreach ($detail as &$d) {
  2123. $d['总产量'] = round($d['总产量'], 2);
  2124. }
  2125. unset($d);
  2126. $this->success('成功', ['summary' => $summary, 'detail' => $detail]);
  2127. }
  2128. /**
  2129. * 工单_基本资料 按 Gd_gdbh 去重取一条
  2130. * 取第一个有客户代号且有成品编号的行;都没有时取第一条非空行
  2131. */
  2132. private function YearSgyjOrderMap(array $gdbhs)
  2133. {
  2134. if (empty($gdbhs)) {
  2135. return [];
  2136. }
  2137. $rows = \db('工单_基本资料')
  2138. ->field([
  2139. 'UPPER(RTRIM(Gd_gdbh)) AS 工单编号',
  2140. 'RTRIM(Gd_cpdh) AS 产品编号',
  2141. 'RTRIM(成品名称) AS 产品名称',
  2142. 'RTRIM(Gd_客户代号) AS 客户编号',
  2143. 'RTRIM(Gd_客户名称) AS 客户名称',
  2144. ])
  2145. ->whereIn('Gd_gdbh', $gdbhs)
  2146. ->select();
  2147. $map = [];
  2148. foreach ($rows as $row) {
  2149. $gdbh = trim((string)$row['工单编号']);
  2150. if ($gdbh === '') continue;
  2151. $code = trim((string)$row['产品编号']);
  2152. $name = trim((string)$row['产品名称']);
  2153. $khdh = trim((string)$row['客户编号']);
  2154. $khmc = trim((string)$row['客户名称']);
  2155. $category = $name !== '' ? (strpos($name, '小盒') !== false ? '小盒' : '条盒') : '';
  2156. if (!isset($map[$gdbh])) {
  2157. // 第一次见到:如果有客户代号且有成品编号,直接用;否则先占坑
  2158. $map[$gdbh] = [
  2159. '产品编号' => $code,
  2160. '产品名称' => $name,
  2161. '客户编号' => $khdh,
  2162. '客户名称' => $khmc,
  2163. '产品类别' => $category,
  2164. ];
  2165. } else {
  2166. // 已存在:如果现有记录缺客户,新记录有客户则覆盖;同理产品编号
  2167. if ($map[$gdbh]['客户编号'] === '' && $khdh !== '') {
  2168. $map[$gdbh]['客户编号'] = $khdh;
  2169. $map[$gdbh]['客户名称'] = $khmc;
  2170. }
  2171. if ($map[$gdbh]['产品编号'] === '' && $code !== '') {
  2172. $map[$gdbh]['产品编号'] = $code;
  2173. $map[$gdbh]['产品名称'] = $name;
  2174. $map[$gdbh]['产品类别'] = $category;
  2175. }
  2176. }
  2177. }
  2178. return $map;
  2179. }
  2180. /**
  2181. * 设备周数据统计 左侧菜单(车间 -> 机台)
  2182. * 参考 facility/getTab,但去掉日期子节点
  2183. */
  2184. public function EqwdtLeft()
  2185. {
  2186. if ($this->request->isGet() === false) {
  2187. $this->error('请求错误');
  2188. }
  2189. $department = \db('设备_基本资料')
  2190. ->distinct(true)
  2191. ->where('使用部门', '<>', '研发中心')
  2192. ->where('设备编组', '<>', '')
  2193. ->where('sys_sbID', '<>', '')
  2194. ->order('设备编组')
  2195. ->column('rtrim(使用部门) as 使用部门');
  2196. if (empty($department)) {
  2197. $this->success('成功', []);
  2198. }
  2199. $data = [];
  2200. foreach ($department as $dept) {
  2201. $machines = \db('设备_基本资料')
  2202. ->where('使用部门', $dept)
  2203. ->where('sys_sbID', '<>', '')
  2204. ->field('rtrim(设备编号) as 设备编号, rtrim(设备名称) as 设备名称')
  2205. ->order('设备编号')
  2206. ->select();
  2207. $machineNodes = [];
  2208. foreach ($machines as $m) {
  2209. $code = trim((string)$m['设备编号']);
  2210. if ($code === '') continue;
  2211. $machineNodes[] = [
  2212. 'label' => $code . '-->' . trim((string)$m['设备名称']),
  2213. 'key' => 'M' . $code,
  2214. 'machine' => $code,
  2215. ];
  2216. }
  2217. $data[] = [
  2218. 'label' => $dept,
  2219. 'key' => 'D' . $dept,
  2220. 'children' => $machineNodes,
  2221. ];
  2222. }
  2223. $this->success('成功', $data);
  2224. }
  2225. /**
  2226. * 设备周数据统计 右侧汇总 + 明细
  2227. * 固定取数据库中最新日期往前 6 天(共 7 天)
  2228. * 参数: machine (可选机台编号)
  2229. */
  2230. public function EqwdtList()
  2231. {
  2232. if ($this->request->isGet() === false) {
  2233. $this->error('请求错误');
  2234. }
  2235. $param = $this->request->param();
  2236. $machine = trim((string)(isset($param['machine']) ? $param['machine'] : ''));
  2237. // 以数据库最新日期为基准,取最近 7 天
  2238. $maxDate = \db('设备_产量计酬')->max('sczl_rq');
  2239. if (!$maxDate) {
  2240. $this->success('成功', ['summary' => [], 'detail' => [], 'range' => ['start' => '', 'end' => '']]);
  2241. }
  2242. $maxTs = strtotime($maxDate);
  2243. $end = date('Y-m-d', $maxTs);
  2244. $start = date('Y-m-d', $maxTs - 6 * 86400);
  2245. $startTime = $start . ' 00:00:00';
  2246. $endTime = date('Y-m-d', $maxTs + 86400) . ' 00:00:00';
  2247. // 1、汇总:按机台编号聚合
  2248. $summaryQuery = \db('设备_产量计酬')
  2249. ->alias('s')
  2250. ->join('设备_基本资料 b', 'RTRIM(b.设备编号) = RTRIM(s.sczl_jtbh)', 'LEFT')
  2251. ->field([
  2252. 'RTRIM(s.sczl_jtbh) AS 机台编号',
  2253. 'RTRIM(b.设备名称) AS 设备名称',
  2254. 'RTRIM(b.使用部门) AS 使用部门',
  2255. 'RTRIM(b.生产工序) AS 生产工序',
  2256. 'RTRIM(b.设备编组) AS 设备编组',
  2257. 'SUM(s.sczl_cl) AS 总产量',
  2258. 'SUM(s.sczl_设备运行工时) AS 设备运行工时',
  2259. 'SUM(s.sczl_装版工时) AS 装版工时',
  2260. 'SUM(s.sczl_装版总工时) AS 装版总工时',
  2261. 'SUM(s.sczl_保养工时) AS 保养工时',
  2262. 'SUM(s.sczl_打样工时) AS 打样工时',
  2263. 'SUM(s.sczl_打样总工时) AS 打样总工时',
  2264. 'SUM(s.sczl_异常停机工时) AS 异常停机工时',
  2265. 'SUM(s.sczl_zcfp) AS 制程废品',
  2266. 'SUM(s.sczl_zccp) AS 制程次品',
  2267. 'SUM(s.sczl_前工序废) AS 前工序废',
  2268. 'SUM(s.sczl_来料少数) AS 来料少数',
  2269. 'SUM(s.sczl_异常工时1) AS 异常补时',
  2270. ])
  2271. ->where('s.sczl_rq', '>=', $startTime)
  2272. ->where('s.sczl_rq', '<', $endTime);
  2273. if ($machine !== '') {
  2274. $summaryQuery->where('s.sczl_jtbh', $machine);
  2275. }
  2276. $summary = $summaryQuery
  2277. ->group('机台编号, 设备名称, 使用部门, 生产工序, 设备编组')
  2278. ->order('机台编号')
  2279. ->select();
  2280. // 2、明细:工单级(按 机台编号 + 工单编号 + 日期 聚合)
  2281. $detailQuery = \db('设备_产量计酬')
  2282. ->alias('s')
  2283. ->join('设备_基本资料 b', 'RTRIM(b.设备编号) = RTRIM(s.sczl_jtbh)', 'LEFT')
  2284. ->join('工单_基本资料 g', 'RTRIM(g.Gd_gdbh) = RTRIM(s.sczl_gdbh)', 'LEFT')
  2285. ->field([
  2286. 'DATE_FORMAT(s.sczl_rq, \'%Y-%m-%d\') AS 日期',
  2287. 'RTRIM(s.sczl_jtbh) AS 机台编号',
  2288. 'RTRIM(b.设备名称) AS 设备名称',
  2289. 'RTRIM(s.sczl_gdbh) AS 工单编号',
  2290. 'CONCAT(FLOOR(s.sczl_yjno), \'-\', RTRIM(s.sczl_gxmc)) AS 印件及工序',
  2291. 'RTRIM(g.Gd_cpdh) AS 产品编号',
  2292. 'RTRIM(g.成品名称) AS 成品名称',
  2293. 'RTRIM(g.Gd_客户名称) AS 客户名称',
  2294. 'SUM(s.sczl_cl) AS 产量',
  2295. 'SUM(s.sczl_设备运行工时) AS 设备运行工时',
  2296. 'SUM(s.sczl_装版工时) AS 装版工时',
  2297. 'SUM(s.sczl_保养工时) AS 保养工时',
  2298. 'SUM(s.sczl_打样工时) AS 打样工时',
  2299. 'SUM(s.sczl_异常停机工时) AS 异常停机工时',
  2300. 'SUM(s.sczl_zcfp) AS 制程废品',
  2301. 'SUM(s.sczl_zccp) AS 制程次品',
  2302. ])
  2303. ->where('s.sczl_rq', '>=', $startTime)
  2304. ->where('s.sczl_rq', '<', $endTime);
  2305. if ($machine !== '') {
  2306. $detailQuery->where('s.sczl_jtbh', $machine);
  2307. }
  2308. $detail = $detailQuery
  2309. ->group('日期, 机台编号, 设备名称, 工单编号, 印件及工序, 产品编号, 成品名称, 客户名称')
  2310. ->order('日期, 机台编号, 工单编号')
  2311. ->select();
  2312. // 数值格式化(0 显示为空字符串)
  2313. foreach ($summary as &$row) {
  2314. foreach (['总产量', '设备运行工时', '装版工时', '装版总工时', '保养工时', '打样工时', '打样总工时', '异常停机工时', '制程废品', '制程次品', '前工序废', '来料少数', '异常补时'] as $k) {
  2315. $v = (float)$row[$k];
  2316. $row[$k] = $v === 0 ? '' : round($v, 2);
  2317. }
  2318. }
  2319. unset($row);
  2320. foreach ($detail as &$row) {
  2321. foreach (['产量', '设备运行工时', '装版工时', '保养工时', '打样工时', '异常停机工时', '制程废品', '制程次品'] as $k) {
  2322. $v = (float)$row[$k];
  2323. $row[$k] = $v === 0 ? '' : round($v, 2);
  2324. }
  2325. }
  2326. unset($row);
  2327. $this->success('成功', [
  2328. 'summary' => $summary,
  2329. 'detail' => $detail,
  2330. 'range' => ['start' => $start, 'end' => $end],
  2331. ]);
  2332. }
  2333. /**
  2334. * 完工工单生产周期统计 左侧菜单(年月 → 客户)
  2335. * 参考 order_super_loss/getTab
  2336. */
  2337. public function CopsTab()
  2338. {
  2339. if ($this->request->isGet() === false) {
  2340. $this->error('请求错误');
  2341. }
  2342. $sql = "SELECT
  2343. DATE_FORMAT(a.接单日期, '%Y%m') AS date,
  2344. COUNT(DISTINCT a.Gd_gdbh) AS cnt,
  2345. RTRIM(a.Gd_客户代号) AS code,
  2346. RTRIM(a.Gd_客户名称) AS name
  2347. FROM `工单_基本资料` a
  2348. WHERE a.接单日期 >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
  2349. AND a.接单日期 IS NOT NULL
  2350. AND (a.工单完工日期 IS NOT NULL OR EXISTS (SELECT 1 FROM `成品入仓` c WHERE c.jjcp_gdbh = a.Gd_gdbh))
  2351. GROUP BY date, code, name
  2352. ORDER BY date DESC, code ASC";
  2353. $rows = Db::query($sql);
  2354. // 组装为 { date-total: [{code, name, total}] } 结构
  2355. $organized = [];
  2356. foreach ($rows as $r) {
  2357. $date = $r['date'];
  2358. if (!isset($organized[$date])) {
  2359. $organized[$date] = ['total_count' => 0, 'items' => []];
  2360. }
  2361. $organized[$date]['items'][] = [
  2362. 'code' => $r['code'],
  2363. 'name' => $r['name'],
  2364. 'total' => intval($r['cnt']),
  2365. ];
  2366. $organized[$date]['total_count'] += intval($r['cnt']);
  2367. }
  2368. $data = [];
  2369. foreach ($organized as $date => $info) {
  2370. $key = $date . '-' . $info['total_count'];
  2371. foreach ($info['items'] as $it) {
  2372. $data[$key][] = $it;
  2373. }
  2374. }
  2375. $this->success('成功', $data);
  2376. }
  2377. /**
  2378. * 完工工单生产周期统计 右侧列表(纯 SQL 避免 TP5 Query 方法缺失)
  2379. * 参数: date (年月 YYYYMM), code (客户代号), search (关键字搜索),
  2380. * page (页码, 默认1), limit (每页条数, 默认50), export (1=导出全量, 不分页)
  2381. */
  2382. public function CopsList()
  2383. {
  2384. if ($this->request->isGet() === false) {
  2385. $this->error('请求错误');
  2386. }
  2387. $param = $this->request->param();
  2388. $date = trim((string)(isset($param['date']) ? $param['date'] : ''));
  2389. $code = trim((string)(isset($param['code']) ? $param['code'] : ''));
  2390. $search = trim((string)(isset($param['search']) ? $param['search'] : ''));
  2391. $page = empty($param['page']) ? 1 : max(1, intval($param['page']));
  2392. $limit = empty($param['limit']) ? 50 : max(1, intval($param['limit']));
  2393. $isExport = !empty($param['export']);
  2394. // 导出全量时限制上限,避免一次性拉爆内存
  2395. if ($isExport) {
  2396. $page = 1;
  2397. $limit = 50000;
  2398. }
  2399. $offset = ($page - 1) * $limit;
  2400. $where = [];
  2401. $where[] = "a.接单日期 >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)";
  2402. $where[] = "a.接单日期 IS NOT NULL";
  2403. $where[] = "(a.工单完工日期 IS NOT NULL AND a.工单完工日期 <> '1900-01-01 00:00:00') OR EXISTS (SELECT 1 FROM `成品入仓` c2 WHERE c2.jjcp_gdbh = a.Gd_gdbh)";
  2404. if ($date !== '') {
  2405. $where[] = "DATE_FORMAT(a.接单日期, '%Y%m') = " . intval($date);
  2406. }
  2407. if ($code !== '') {
  2408. $escaped = str_replace("'", "''", $code);
  2409. $where[] = "UPPER(RTRIM(a.Gd_客户代号)) = UPPER(RTRIM('{$escaped}'))";
  2410. }
  2411. if ($search !== '') {
  2412. $escaped = str_replace("'", "''", $search);
  2413. $where[] = "(a.Gd_gdbh LIKE '%{$escaped}%' OR a.成品名称 LIKE '%{$escaped}%' OR a.Gd_cpdh LIKE '%{$escaped}%')";
  2414. }
  2415. $whereSql = implode(' AND ', $where);
  2416. // 公共 FROM 段(与 a 表 1:1 关联,可用于统计总数)
  2417. $fromSql = "FROM `工单_基本资料` a
  2418. LEFT JOIN `工单_印件资料` b
  2419. ON a.Gd_gdbh = b.Yj_Gdbh AND CAST(a.行号 AS UNSIGNED) = b.yj_Yjno
  2420. LEFT JOIN (
  2421. SELECT jjcp_gdbh, jjcp_yjno,
  2422. SUM(jjcp_sl) AS 入仓数量,
  2423. MIN(jjcp_sj) AS 检验日期,
  2424. MAX(jjcp_sj) AS 入仓完工日期
  2425. FROM `成品入仓`
  2426. GROUP BY jjcp_gdbh, jjcp_yjno
  2427. ) c
  2428. ON a.Gd_gdbh = c.jjcp_gdbh AND CAST(a.行号 AS UNSIGNED) = c.jjcp_yjno
  2429. WHERE {$whereSql}";
  2430. // 总数
  2431. $totalRow = Db::query("SELECT COUNT(*) AS total {$fromSql}");
  2432. $total = isset($totalRow[0]['total']) ? (int)$totalRow[0]['total'] : 0;
  2433. $sql = "SELECT
  2434. RTRIM(a.Gd_生产分类) AS 生产分类,
  2435. RTRIM(a.Gd_gdbh) AS 工单编号,
  2436. RTRIM(b.yj_Yjdh) AS 印件代号,
  2437. RTRIM(a.Gd_cpdh) AS 产品代号,
  2438. RTRIM(a.成品名称) AS 产品名称,
  2439. a.订单数量,
  2440. a.实际投料,
  2441. RTRIM(a.计量单位) AS 计量单位,
  2442. IFNULL(c.入仓数量, 0) AS 入仓数量,
  2443. DATEDIFF(COALESCE(DATE(c.检验日期), DATE(a.工单完工日期)), DATE(a.接单日期)) AS 工单周期,
  2444. DATE(COALESCE(c.检验日期, a.工单完工日期)) AS 检验日期,
  2445. TIME(COALESCE(c.检验日期, a.工单完工日期)) AS 检验时间,
  2446. DATE(a.接单日期) AS 接单日期,
  2447. DATE(COALESCE(c.入仓完工日期, NULLIF(a.入仓日期, '1900-01-01 00:00:00'), a.工单完工日期)) AS 入仓完工日期,
  2448. RTRIM(a.Gd_客户代号) AS 客户代号,
  2449. RTRIM(a.Gd_客户名称) AS 客户名称,
  2450. RTRIM(a.销售订单号) AS 销售订单号
  2451. {$fromSql}
  2452. ORDER BY COALESCE(c.检验日期, a.工单完工日期) DESC, a.Gd_gdbh DESC
  2453. LIMIT {$offset}, {$limit}";
  2454. $list = Db::query($sql);
  2455. foreach ($list as &$row) {
  2456. $row['生产公分'] = '';
  2457. $row['工单公休'] = '';
  2458. $row['计划开工日期'] = '';
  2459. $row['实际开工日期'] = '';
  2460. $row['目标合格率'] = '';
  2461. foreach (['订单数量', '实际投料'] as $k) {
  2462. $v = (float)$row[$k];
  2463. $row[$k] = $v === 0 ? '' : number_format($v, 2);
  2464. }
  2465. $row['入仓数量'] = (int)$row['入仓数量'] === 0 ? '' : (int)$row['入仓数量'];
  2466. $row['工单周期'] = $row['工单周期'] === null ? '' : (int)$row['工单周期'];
  2467. $sl = (int)$row['入仓数量'];
  2468. $tl = (float)$row['实际投料'];
  2469. if ($sl > 0 && $tl > 0) {
  2470. $row['实际合格率'] = number_format($sl / ($tl * 10000) * 100, 2) . '%';
  2471. } else {
  2472. $row['实际合格率'] = '';
  2473. }
  2474. foreach (['检验日期', '检验时间', '接单日期', '入仓完工日期'] as $k) {
  2475. if ($row[$k] === null || $row[$k] === '1900-01-01' || $row[$k] === '1900-01-01 00:00:00') {
  2476. $row[$k] = '';
  2477. }
  2478. }
  2479. }
  2480. unset($row);
  2481. $this->success('成功', [
  2482. 'list' => $list,
  2483. 'total' => $total,
  2484. 'page' => $page,
  2485. 'limit' => $limit,
  2486. ]);
  2487. }
  2488. }