Achievementatestatistics.php 55 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036103710381039104010411042104310441045104610471048104910501051105210531054105510561057105810591060106110621063106410651066106710681069107010711072107310741075107610771078107910801081108210831084108510861087108810891090109110921093109410951096109710981099110011011102110311041105110611071108110911101111111211131114111511161117111811191120112111221123112411251126112711281129113011311132113311341135113611371138113911401141114211431144114511461147114811491150115111521153115411551156115711581159116011611162116311641165116611671168116911701171117211731174117511761177117811791180118111821183118411851186118711881189119011911192119311941195119611971198119912001201120212031204120512061207120812091210121112121213121412151216121712181219122012211222122312241225122612271228122912301231123212331234123512361237123812391240124112421243124412451246124712481249125012511252125312541255125612571258125912601261126212631264126512661267126812691270127112721273127412751276127712781279128012811282128312841285128612871288128912901291129212931294129512961297129812991300130113021303130413051306130713081309131013111312131313141315131613171318131913201321132213231324132513261327132813291330133113321333133413351336133713381339134013411342134313441345134613471348134913501351135213531354135513561357135813591360136113621363
  1. <?php
  2. namespace app\api\controller;
  3. use app\common\controller\Api;
  4. use Monolog\Handler\IFTTTHandler;
  5. use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
  6. use think\Db;
  7. use think\Request;
  8. use PhpOffice\PhpSpreadsheet\IOFactory;
  9. class Achievementatestatistics extends Api
  10. {
  11. protected $noNeedLogin = ['*'];
  12. protected $noNeedRight = ['*'];
  13. /**
  14. * 1.1达成率统计->左侧菜单
  15. */
  16. public function Leftmenu(){
  17. // 获取所有部门(排除特定)
  18. $departments = Db::name('设备_基本资料')
  19. ->field('使用部门')
  20. ->whereNotIn('使用部门', ['打样室', '智能车间'])
  21. ->group('使用部门')
  22. ->select();
  23. // 部门名数组
  24. $deptList = array_map(function($item) {
  25. return trim($item['使用部门']);
  26. }, $departments);
  27. // 获取产量表日期
  28. $rawData = Db::name('设备_产量计酬')
  29. ->field("DATE(sys_rq) as rq")
  30. ->whereRaw("YEAR(sys_rq) >= 2000")
  31. ->order("rq desc")
  32. ->select();
  33. $workStats = [];
  34. $addedMonths = []; // 记录已处理年月
  35. foreach ($rawData as $item) {
  36. $rq = $item['rq'];
  37. $year = date('Y', strtotime($rq));
  38. $yearMonth = date('Ym', strtotime($rq));
  39. // 如果该年月还没加过,才添加
  40. if (!isset($addedMonths[$year][$yearMonth])) {
  41. $workStats[$year][$yearMonth] = $deptList;
  42. $addedMonths[$year][$yearMonth] = true;
  43. }
  44. }
  45. $result = [
  46. '排产计划达成率统计' => $departments,
  47. '设备工时达成率统计' => $workStats
  48. ];
  49. $this->success('成功',$result);
  50. }
  51. public function Machine_List()
  52. {
  53. if (!$this->request->isGet()) {
  54. $this->error('请求方式错误');
  55. }
  56. $param = $this->request->param();
  57. if (empty($param['Machine'])) {
  58. $this->error('缺少参数 Machine');
  59. }
  60. // 日期映射:近 7 天
  61. $dateMap = [];
  62. $recentDates = [];
  63. for ($i = 0; $i < 7; $i++) {
  64. $date = date('Y-m-d', strtotime("-{$i} days"));
  65. $label = date('m月d日', strtotime($date));
  66. $dateMap[$date] = $label;
  67. $recentDates[] = $date;
  68. }
  69. $devices = Db::name('设备_基本资料')
  70. ->field('设备编号, 设备名称')
  71. ->whereLike('使用部门', '%' . $param['Machine'] . '%')
  72. ->select();
  73. $results = [];
  74. foreach ($devices as $device) {
  75. $jtbh = $device['设备编号'];
  76. $jtname = trim($device['设备名称']);
  77. // 获取原始产量数据
  78. $records = Db::name('设备_产量计酬')
  79. ->field([
  80. 'sczl_bzdh as 班组',
  81. 'sczl_cl',
  82. 'sczl_Pgcl',
  83. 'sczl_rq as 日期',
  84. 'sczl_gdbh',
  85. 'sczl_yjno',
  86. 'sczl_gxh'
  87. ])
  88. ->where('sczl_jtbh', $jtbh)
  89. ->where('sczl_rq', '>=', date('Y-m-d 00:00:00', strtotime('-6 days')))
  90. ->where('sczl_rq', '<=', date('Y-m-d 23:59:59'))
  91. ->select();
  92. $stat = [];
  93. $summary = [];
  94. foreach ($records as $row) {
  95. $bz = $row['班组'] ?: '未分组';
  96. $rq = date('Y-m-d', strtotime($row['日期']));
  97. $cl = floatval($row['sczl_cl']);
  98. $pgcl = floatval($row['sczl_Pgcl']);
  99. $actual = ($cl == 0) ? 0 : (($pgcl > 0) ? $cl * $pgcl : $cl);
  100. // 查询排产产量
  101. $plan = Db::name('工单_工艺资料')
  102. ->where('Gy0_gdbh', $row['sczl_gdbh'])
  103. ->where('Gy0_yjno', $row['sczl_yjno'])
  104. ->where('Gy0_gxh', $row['sczl_gxh'])
  105. ->value('Gy0_计划接货数');
  106. $plan = floatval($plan);
  107. // 班组维度统计
  108. if (!isset($stat[$bz][$rq])) {
  109. $stat[$bz][$rq] = ['实际产量' => 0, '排产产量' => 0];
  110. }
  111. $stat[$bz][$rq]['实际产量'] += $actual;
  112. $stat[$bz][$rq]['排产产量'] += $plan;
  113. // 合计维度统计
  114. if (!isset($summary[$rq])) {
  115. $summary[$rq] = ['实际产量' => 0, '排产产量' => 0];
  116. }
  117. $summary[$rq]['实际产量'] += $actual;
  118. $summary[$rq]['排产产量'] += $plan;
  119. }
  120. // 输出每个班组
  121. foreach ($stat as $bz => $dateList) {
  122. $item = [
  123. '机台编号' => $jtbh,
  124. '机台名称' => $jtname,
  125. '班组' => $bz,
  126. '实际总产量' => 0,
  127. '排产总产量' => 0,
  128. '近7天综合达成率' => '0%'
  129. ];
  130. $i = 1;
  131. foreach ($dateMap as $date => $label) {
  132. $actual = isset($dateList[$date]) ? round($dateList[$date]['实际产量'], 2) : 0;
  133. $plan = isset($dateList[$date]) ? round($dateList[$date]['排产产量'], 2) : 0;
  134. $rate = ($plan > 0) ? round($actual / $plan * 100, 2) . '%' : '0%';
  135. $item[$label] = $rate;
  136. $item["实际产量{$i}"] = $actual;
  137. $item["排产产量{$i}"] = $plan;
  138. $item['实际总产量'] += $actual;
  139. $item['排产总产量'] += $plan;
  140. $i++;
  141. }
  142. $item['近7天综合达成率'] = ($item['排产总产量'] > 0)
  143. ? round($item['实际总产量'] / $item['排产总产量'] * 100, 2) . '%'
  144. : '0%';
  145. $results[] = $item;
  146. }
  147. // 合计行
  148. $item = [
  149. '机台编号' => $jtbh,
  150. '机台名称' => '合计',
  151. '班组' => '',
  152. '实际总产量' => 0,
  153. '排产总产量' => 0,
  154. '近7天综合达成率' => '0%'
  155. ];
  156. $i = 1;
  157. foreach ($dateMap as $date => $label) {
  158. $actual = isset($summary[$date]) ? round($summary[$date]['实际产量'], 2) : 0;
  159. $plan = isset($summary[$date]) ? round($summary[$date]['排产产量'], 2) : 0;
  160. // 重新计算达成率,不使用之前累加的百分比
  161. $rate = ($plan > 0) ? round($actual / $plan * 100, 2) . '%' : '0%';
  162. $item[$label] = $rate;
  163. $item["实际产量{$i}"] = $actual;
  164. $item["排产产量{$i}"] = $plan;
  165. $item['实际总产量'] += $actual;
  166. $item['排产总产量'] += $plan;
  167. $i++;
  168. }
  169. // 修正“近7天综合达成率”的计算
  170. $item['近7天综合达成率'] = ($item['排产总产量'] > 0)
  171. ? round($item['实际总产量'] / $item['排产总产量'] * 100, 2) . '%'
  172. : '0%';
  173. $results[] = $item;
  174. }
  175. $this->success('成功', [
  176. 'data' => $results,
  177. 'list' => $recentDates
  178. ]);
  179. }
  180. /**
  181. * 1.3达成率统计->机台生产详情
  182. */
  183. public function Machine_Detail()
  184. {
  185. if (!$this->request->isGet()) {
  186. $this->error('请求方式错误');
  187. }
  188. $param = $this->request->param();
  189. if (empty($param['jtbh']) || empty($param['bz'])) {
  190. // $this->error('缺少参数:jtbh(机台编号)或 bz(班组)');
  191. }
  192. $jtbh = $param['jtbh'];
  193. $bz = $param['bz'];
  194. // 查询近 7 天产量记录
  195. $records = Db::name('设备_产量计酬')->alias('a')
  196. ->field('
  197. a.sczl_rq as 日期,
  198. a.sczl_jtbh as 机台编号,
  199. a.sczl_bzdh as 班组,
  200. a.sczl_gdbh as 工单编号,
  201. a.sczl_yjno as 印件号,
  202. b.yj_yjmc as 印件名称,
  203. a.sczl_gxh as 工序号,
  204. a.sczl_gxmc as 工序名称,
  205. a.sczl_cl as 实际产量,
  206. c.排单小时定额 as 排产标准产能,
  207. d.Gy0_计划接货数 as 排产产量,
  208. a.sczl_设备运行工时 as 上报运行工时,
  209. a.sczl_装版总工时 as 装版实际工时,
  210. a.sczl_保养工时 as 保养工时,
  211. a.sczl_打样总工时 as 打样工时,
  212. d.版距 as 版距,
  213. c.设备名称 as 机台名称
  214. ')
  215. ->join('工单_印件资料 b','a.sczl_gdbh = b.Yj_Gdbh')
  216. ->join('设备_基本资料 c','a.sczl_jtbh = c.设备编号')
  217. ->join('工单_工艺资料 d','a.sczl_gdbh = d.Gy0_gdbh AND a.sczl_yjno = d.Gy0_yjno AND a.sczl_gxh = d.Gy0_gxh')
  218. ->where('a.sczl_jtbh', $jtbh)
  219. ->where('a.sczl_bzdh', $bz)
  220. ->where('a.sczl_rq', '>=', date('Y-m-d 00:00:00', strtotime('-6 days')))
  221. ->where('a.sczl_rq', '<=', date('Y-m-d 23:59:59'))
  222. ->order('a.sczl_rq desc')
  223. ->select();
  224. $details = [];
  225. foreach ($records as $row) {
  226. $rq = date('Y-m-d', strtotime($row['日期']));
  227. $details[] = [
  228. '日期' => $rq,
  229. '机台编号' => $row['机台编号'],
  230. '班组' => $row['班组'],
  231. '工单编号' => $row['工单编号'],
  232. '印件号' => $row['印件号'],
  233. '印件名称' => $row['印件名称'],
  234. '工序号' => $row['工序号'],
  235. '工序名称' => $row['工序名称'],
  236. '实际产量' => floatval($row['实际产量']),
  237. '排产标准产能' => $row['排产标准产能'],
  238. '排产产量' => floatval($row['排产产量']),
  239. '上报运行工时' => $row['上报运行工时'],
  240. '装版实际工时' => $row['装版实际工时'],
  241. '保养工时' => $row['保养工时'],
  242. '打样工时' => $row['打样工时'],
  243. '版距' => $row['版距'],
  244. '机台名称' => $row['机台名称'],
  245. ];
  246. }
  247. $this->success('明细获取成功', $details);
  248. }
  249. /**
  250. * 1.4达成率统计 -> 按月份汇总机台产量(按班组分组)
  251. */
  252. public function Machine_production_list()
  253. {
  254. if (!$this->request->isGet()) {
  255. $this->error('请求方式错误');
  256. }
  257. $param = $this->request->param();
  258. if (empty($param['Machine']) || empty($param['rq'])) {
  259. $this->error('参数错误');
  260. }
  261. $machine = $param['Machine'];
  262. $rq = $param['rq'];
  263. $ym = substr($rq, 0, 4) . '-' . substr($rq, 4, 2);
  264. // 查询设备编号
  265. $devices = Db::name('设备_基本资料')
  266. ->where('使用部门', $machine)
  267. ->column('设备编号');
  268. $devices = array_map('trim', $devices);
  269. // 查询产量记录
  270. $records = Db::name('设备_产量计酬')
  271. ->alias('a')
  272. ->field([
  273. 'a.sczl_jtbh as 机台编号',
  274. 'a.sczl_bzdh as 班组',
  275. 'a.sczl_rq',
  276. 'a.sczl_cl',
  277. 'a.sczl_ls',
  278. 'a.sczl_rq',
  279. 'a.sczl_装版总工时 as 装板实际工时',
  280. 'a.sczl_装版工时 as 装板补产工时',
  281. 'a.sczl_保养工时 as 保养工时',
  282. 'a.sczl_打样总工时 as 打样总工时',
  283. 'a.sczl_打样工时 as 打样补产工时',
  284. 'a.sczl_异常停机工时 as 异常总工时',
  285. 'a.sczl_异常工时1 as 异常补时',
  286. 'a.sczl_设备运行工时 as 运行工时',
  287. 'c.排单小时定额 as 小时产能',
  288. 'c.平均车速'
  289. ])
  290. ->join('设备_基本资料 c', 'a.sczl_jtbh = c.设备编号')
  291. ->whereIn('a.sczl_jtbh', $devices)
  292. ->where('a.sczl_rq', 'like', $ym . '%')
  293. ->select();
  294. // 分组汇总:按 机台编号 + 班组
  295. $resultList = [];
  296. foreach ($records as $row) {
  297. $jtbh = $row['机台编号'];
  298. $bz = $row['班组'] ?: '未分组';
  299. $key = $jtbh . '|' . $bz;
  300. if ($row['运行工时'] >= 8) {
  301. $row['运行工时'] = $row['运行工时'] - 1;
  302. }elseif ($row['运行工时'] < 8 && $row['运行工时'] > 4) {
  303. $row['运行工时'] = $row['运行工时'] - 0.5;
  304. }
  305. if (strpos($row['机台编号'],'YJY') !== false) {
  306. $plate_hours = $row['装板补产工时'] * 1.7;
  307. $plate_mounting_hours = $row['装板实际工时'] * 1.5;
  308. if ($plate_hours < $plate_mounting_hours) {
  309. $row['装板补产工时'] = $plate_hours;
  310. }elseif ($plate_hours > $plate_mounting_hours) {
  311. $row['装板补产工时'] = $plate_mounting_hours;
  312. }elseif ($row['装板补产工时'] > $plate_mounting_hours) {
  313. $row['装板补产工时'] = $row['装板补产工时'];
  314. }else{
  315. $row['装板补产工时'] = $plate_hours;
  316. }
  317. }
  318. if (!isset($resultList[$key])) {
  319. $resultList[$key] = [
  320. '机台编号' => $jtbh,
  321. '班组' => $bz,
  322. '实际产量' => 0,
  323. '装板实际工时' => 0,
  324. '装板补产工时' => 0,
  325. '保养工时' => 0,
  326. '打样总工时' => 0,
  327. '打样补产工时' => 0,
  328. '异常总工时' => 0,
  329. '异常补时' => 0,
  330. '运行工时' => 0,
  331. 'sczl_rq' => date('Ym', strtotime($row['sczl_rq'])),
  332. // 新增字段
  333. '目标产量' => 0,
  334. '负荷产量' => 0,
  335. '小时产能' => $row['小时产能'],
  336. '平均车速' => $row['平均车速']
  337. ];
  338. }
  339. // 累加字段
  340. $resultList[$key]['实际产量'] += floatval($row['sczl_cl']);
  341. $resultList[$key]['装板实际工时'] += floatval($row['装板实际工时']);
  342. $resultList[$key]['装板补产工时'] += floatval($row['装板补产工时']);
  343. $resultList[$key]['保养工时'] += floatval($row['保养工时']);
  344. $resultList[$key]['打样总工时'] += floatval($row['打样总工时']);
  345. $resultList[$key]['打样补产工时'] += floatval($row['打样补产工时']);
  346. $resultList[$key]['异常总工时'] += floatval($row['异常总工时']);
  347. $resultList[$key]['异常补时'] += floatval($row['异常补时']);
  348. $resultList[$key]['运行工时'] += floatval($row['运行工时']);
  349. }
  350. // 汇总并计算目标/负荷产量及达成率
  351. $grouped = []; // [机台编号 => [班组数据...]]
  352. foreach ($resultList as $item) {
  353. $jtbh = $item['机台编号'];
  354. $grouped[$jtbh][] = $item;
  355. }
  356. $finalList = [];
  357. foreach ($grouped as $jtbh => $rows) {
  358. $sum = [
  359. '机台编号' => $jtbh,
  360. '班组' => '合计',
  361. '实际产量' => 0,
  362. '装板实际工时' => 0,
  363. '装板补产工时' => 0,
  364. '保养工时' => 0,
  365. '打样总工时' => 0,
  366. '打样补产工时' => 0,
  367. '异常总工时' => 0,
  368. '异常补时' => 0,
  369. '运行工时' => 0,
  370. '目标产量' => 0,
  371. '负荷产量' => 0,
  372. 'sczl_rq' => $rows[0]['sczl_rq'] ?? '',
  373. ];
  374. foreach ($rows as &$row) {
  375. // 计算目标 & 负荷产量
  376. $有效工时 = max(0, $row['运行工时'] - $row['保养工时'] - $row['装板补产工时'] - $row['异常总工时'] - $row['打样补产工时']);
  377. $row['目标产量'] = round($有效工时 * $row['小时产能'], 2);
  378. $row['负荷产量'] = round($有效工时 * $row['平均车速'], 2);
  379. // 计算达成率 & 利用率
  380. $row['目标达成'] = ($row['目标产量'] > 0)
  381. ? round($row['实际产量'] / $row['目标产量'] * 100, 2) . '%'
  382. : '0%';
  383. $row['综合利用率'] = ($row['负荷产量'] > 0)
  384. ? round($row['实际产量'] / $row['负荷产量'] * 100, 2) . '%'
  385. : '0%';
  386. $finalList[] = $row;
  387. // 合计累加
  388. $sum['实际产量'] += $row['实际产量'];
  389. $sum['装板实际工时'] += $row['装板实际工时'];
  390. $sum['装板补产工时'] += $row['装板补产工时'];
  391. $sum['保养工时'] += $row['保养工时'];
  392. $sum['打样总工时'] += $row['打样总工时'];
  393. $sum['打样补产工时'] += $row['打样补产工时'];
  394. $sum['异常总工时'] += $row['异常总工时'];
  395. $sum['异常补时'] += $row['异常补时'];
  396. $sum['运行工时'] += $row['运行工时'];
  397. $sum['目标产量'] += $row['目标产量'];
  398. $sum['负荷产量'] += $row['负荷产量'];
  399. }
  400. // 合计达成率
  401. $sum['目标达成'] = ($sum['目标产量'] > 0)
  402. ? round($sum['实际产量'] / $sum['目标产量'] * 100, 2) . '%'
  403. : '0%';
  404. $sum['综合利用率'] = ($sum['负荷产量'] > 0)
  405. ? round($sum['实际产量'] / $sum['负荷产量'] * 100, 2) . '%'
  406. : '0%';
  407. $finalList[] = $sum;
  408. }
  409. $this->success('汇总成功', $finalList);
  410. }
  411. /**
  412. * 1.5达成率统计 -> 按日期+工单统计生产明细
  413. */
  414. public function Machine_production_details()
  415. {
  416. if (!$this->request->isGet()) {
  417. $this->error('请求方式错误');
  418. }
  419. $param = $this->request->param();
  420. if (empty($param['jtbh']) || empty($param['rq'])) {
  421. $this->error('缺少必要参数:jtbh(机台编号)、rq(年月)');
  422. }
  423. $jtbh = $param['jtbh'];
  424. $rq = $param['rq'];
  425. $ym = substr($rq, 0, 4) . '-' . substr($rq, 4, 2);
  426. // 查询该月份内该机台的每日工单产量明细
  427. $records = Db::name('设备_产量计酬')->alias('a')
  428. ->field([
  429. 'a.sczl_jtbh as 机台编号',
  430. 'a.sczl_rq as 日期',
  431. 'a.sczl_bzdh as 班组',
  432. 'a.sczl_gdbh as 工单编号',
  433. 'b.yj_yjmc as 印件名称',
  434. 'a.sczl_yjno as 印件号',
  435. 'a.sczl_gxh as 工序号',
  436. 'a.sczl_gxmc as 工序名称',
  437. 'a.sczl_ls',
  438. 'SUM(a.sczl_cl) as 实际产量',
  439. 'SUM(a.sczl_装版总工时) as 装版实际工时',
  440. 'SUM(a.sczl_装版工时) as 装版补产工时',
  441. 'SUM(a.sczl_保养工时) as 保养工时',
  442. 'SUM(a.sczl_打样总工时) as 打样总工时',
  443. 'SUM(a.sczl_打样工时) as 打样补产工时',
  444. 'SUM(a.sczl_异常停机工时) as 异常总工时',
  445. 'SUM(a.sczl_异常工时1) as 异常补时',
  446. 'SUM(a.sczl_设备运行工时) as 运行工时',
  447. 'c.排单小时定额 as 小时产能',
  448. 'c.平均车速',
  449. 'd.工价系数 as 难度系数'
  450. ])
  451. ->join('工单_印件资料 b', 'a.sczl_gdbh = b.Yj_Gdbh AND a.sczl_yjno = b.Yj_YjNo', 'LEFT') // 添加印件号关联条件
  452. ->join('设备_基本资料 c', 'a.sczl_jtbh = c.设备编号')
  453. ->join('工单_工艺资料 d', 'a.sczl_gdbh = d.Gy0_gdbh AND a.sczl_yjno = d.Gy0_yjno AND a.sczl_gxh = d.Gy0_gxh', 'LEFT')
  454. ->where('a.sczl_jtbh', $jtbh)
  455. ->whereLike('a.sczl_rq', $ym . '%')
  456. ->group('a.sczl_rq, a.sczl_gdbh, a.sczl_yjno, a.sczl_gxh, a.sczl_jtbh ,a.sczl_bzdh') // 确保唯一性分组
  457. ->order('a.sczl_rq desc, a.sczl_bzdh')
  458. ->select();
  459. // 查询每个班组的运行工时
  460. $Operating_hours = \db('设备_产量计酬')
  461. ->field('sczl_rq as 日期,sczl_bzdh as 班组,sum(sczl_设备运行工时) as 设备运行工时')
  462. ->where('sczl_jtbh', $jtbh)
  463. ->whereLike('sczl_rq', $ym . '%')
  464. ->group('sczl_rq, sczl_bzdh')
  465. ->order('sczl_rq desc, sczl_bzdh')
  466. ->select();
  467. // 设备运行达成率设备运行工时计算规则:
  468. // 1.上报通电工时>8小时,设备运行工时=上报通电工时-1小时
  469. // 2.上报通电工时<8小时且上报通电工时>4小时,设备运行工时= 上报通电工时-0.5小时
  470. // 3.上报通电工时<4小时,设备运行工时 = 上报通电工时
  471. foreach ($Operating_hours as $k => $v) {
  472. if ($v['设备运行工时'] >= 8) {
  473. $Operating_hours[$k]['设备运行工时'] = $v['设备运行工时'] - 1;
  474. }elseif ($v['设备运行工时'] < 8 && $v['设备运行工时'] > 4) {
  475. $Operating_hours[$k]['设备运行工时'] = $v['设备运行工时'] - 0.5;
  476. }else{
  477. $Operating_hours[$k]['设备运行工时'] = $v['设备运行工时'];
  478. }
  479. }
  480. // 数据格式化与目标产量计算
  481. foreach ($records as $key => $row) {
  482. $records[$key]['日期'] = date('Y-m-d', strtotime($row['日期']));
  483. $records[$key]['印件工序'] = $row['印件号'] . '-' . $row['工序名称'];
  484. // 胶印设备换型工时(装版补产工时)计算规则:
  485. //1.按班次工单分开计算装版补产工时,基础计算规则:装版补产工时*1.7
  486. //2.当装版补产工时*1.7<实际装版工时*1.5时,按照装版补产工时*1.7计算
  487. //3.当装版补产工时*1.7>实际装版工时*1.5,按照实际装版工时*1.5计算
  488. //4.当装版补产工时>实际装版工时*1.5,按照装版补产工时计算
  489. if (strpos($row['机台编号'],'YJY') !== false) {
  490. $plate_hours = $row['装版补产工时'] * 1.7;
  491. $plate_mounting_hours = $row['装版实际工时'] * 1.5;
  492. if ($plate_hours < $plate_mounting_hours) {
  493. $records[$key]['装版补产工时'] = $plate_hours;
  494. }elseif ($plate_hours > $plate_mounting_hours) {
  495. $records[$key]['装版补产工时'] = $plate_mounting_hours;
  496. }elseif ($row['装版补产工时'] > $plate_mounting_hours) {
  497. $records[$key]['装版补产工时'] = $row['装版补产工时'];
  498. }else{
  499. $records[$key]['装版补产工时'] = $plate_hours;
  500. }
  501. }
  502. // 计算目标产量
  503. foreach ($Operating_hours as $k => $v) {
  504. if ($v['日期'] === $row['日期'] && $v['班组'] === $row['班组']) {
  505. //重新赋值运行工时
  506. $records[$key]['运行工时'] = $v['设备运行工时'];
  507. //计算目标产量
  508. $records[$key]['目标产量'] = round(
  509. max(0, $v['设备运行工时'] - $row['保养工时'] - $row['装版补产工时'] - $row['异常补时'] - $row['打样补产工时']) * $row['小时产能'],
  510. 2
  511. );
  512. // 计算负荷产量
  513. $records[$key]['负荷产量'] = round(
  514. max(0, $v['设备运行工时'] - $row['保养工时'] - $row['异常补时'] - $row['打样补产工时'] ) * $row['平均车速'],
  515. 2
  516. );
  517. // 计算目标达成率
  518. $records[$key]['目标达成'] = $records[$key]['目标产量'] > 0
  519. ? round($row['实际产量'] / $records[$key]['目标产量'] * 100, 2) . '%'
  520. : '0%';
  521. // 计算综合利用率
  522. $records[$key]['综合利用率'] = $records[$key]['负荷产量'] > 0
  523. ? round($row['实际产量'] / $records[$key]['负荷产量'] * 100, 2) . '%'
  524. : '0%';
  525. }
  526. }
  527. }
  528. $this->success('明细获取成功', $records);
  529. }
  530. /**
  531. * 1.6达成率统计->按时段导出Excel
  532. */
  533. public function Machine_date_excel()
  534. {
  535. if (!$this->request->isGet()) {
  536. $this->error('请求方式错误');
  537. }
  538. $param = $this->request->param();
  539. if (empty($param['start_rq']) || empty($param['end_rq']) || empty($param['bm'])) {
  540. $this->error('缺少参数:start_rq(开始日期)或 end_rq(结束日期)或 bm(部门)');
  541. }
  542. $start_rq = $param['start_rq'];
  543. $end_rq = $param['end_rq'];
  544. $bm = $param['bm'];
  545. // 1. 先查询设备_基本资料表,获取该部门下的所有设备编号(去重)
  546. $deviceIds = Db::name('设备_基本资料')
  547. ->where('使用部门', $bm)
  548. ->group('设备编号') // 去重
  549. ->column('设备编号');
  550. if (empty($deviceIds)) {
  551. $this->error('该部门下没有设备数据');
  552. }
  553. // 2. 查询设备_产量计酬表,筛选符合条件的记录
  554. $records = Db::name('设备_产量计酬')->alias('a')
  555. ->field([
  556. 'a.sczl_jtbh as 机台编号',
  557. 'a.sczl_rq as 日期',
  558. 'a.sczl_bzdh as 班组',
  559. 'a.sczl_gdbh as 工单编号',
  560. 'b.yj_yjmc as 印件名称',
  561. 'a.sczl_yjno as 印件号',
  562. 'a.sczl_gxh as 工序号',
  563. 'a.sczl_gxmc as 工序名称',
  564. 'a.sczl_ls',
  565. 'SUM(a.sczl_cl) as 实际产量',
  566. 'sczl_装版总工时 as 装板实际工时',
  567. 'sczl_装版工时 as 装板补产工时',
  568. 'sczl_保养工时 as 保养工时',
  569. 'sczl_打样总工时 as 打样总工时',
  570. 'sczl_打样工时 as 打样补产工时',
  571. 'sczl_异常停机工时 as 异常总工时',
  572. 'sczl_异常工时1 as 异常补时',
  573. 'sczl_设备运行工时 as 运行工时'
  574. ])
  575. ->join('工单_印件资料 b', 'a.sczl_gdbh = b.Yj_Gdbh')
  576. ->whereIn('a.sczl_jtbh', $deviceIds) // 使用设备编号列表筛选
  577. ->whereBetween('a.sczl_rq', [
  578. $start_rq . ' 00:00:00',
  579. $end_rq . ' 23:59:59'
  580. ])
  581. ->group('a.sczl_jtbh, a.sczl_rq, a.sczl_bzdh, a.sczl_gdbh') // 按设备、日期、班组、工单分组
  582. ->order('a.sczl_rq desc')
  583. ->select();
  584. // 格式化数据
  585. foreach ($records as &$row) {
  586. $row['日期'] = date('Y-m-d', strtotime($row['日期']));
  587. $row['印件工序'] = $row['印件号'] . '-' . $row['工序名称'];
  588. }
  589. $this->success('明细获取成功', $records);
  590. }
  591. /**
  592. * 01.产品年度投入产出率统计->左侧菜单
  593. */
  594. // public function left_Productyear() {
  595. // $list = \db('产品_基本资料')
  596. // ->field('客户编号,客户名称,Mod_rq')
  597. // ->group('客户编号,客户名称,Mod_rq')
  598. // ->where('客户编号','<>','')
  599. // ->order('客户编号')
  600. // ->select();
  601. //
  602. //
  603. // $data['翌星工单'] = [];
  604. // $data['MN工单'] = [];
  605. //
  606. // foreach ($list as $key => $value) {
  607. // $value['客户编号'] = rtrim($value['客户编号']);
  608. // $value['客户名称'] = rtrim($value['客户名称']);
  609. // if (empty($value['客户名称'])) {
  610. // continue;
  611. // }
  612. //
  613. // $name = $value['客户编号'] . '【' . $value['客户名称'] . '】';
  614. // $year = date('Y', strtotime($value['Mod_rq']));
  615. // $uniqueKey = $value['客户编号'] . '_' . $value['客户名称'] . '_' . $year;
  616. //
  617. // if (isset($seenEntries[$uniqueKey])) {
  618. // continue; // 如果已经存在,跳过重复项
  619. // }
  620. // $seenEntries[$uniqueKey] = true;
  621. //
  622. // if (strpos($value['客户编号'], 'J') !== false || strpos($value['客户编号'], 'Y') !== false) {
  623. // if (!isset($data['翌星工单'][$year])) {
  624. // $data['翌星工单'][$year] = [];
  625. // }
  626. // array_push($data['翌星工单'][$year], $name);
  627. // } else {
  628. // if (!isset($data['MN工单'][$year])) {
  629. // $data['MN工单'][$year] = [];
  630. // }
  631. // array_push($data['MN工单'][$year], $name);
  632. // }
  633. // }
  634. //
  635. // // 按年份排序(从最新到最旧)
  636. // krsort($data['翌星工单']);
  637. // krsort($data['MN工单']);
  638. //
  639. // // 对每个年份内的数据进行排序(可选)
  640. // foreach ($data['翌星工单'] as $year => &$items) {
  641. // sort($items);
  642. // }
  643. // foreach ($data['MN工单'] as $year => &$items) {
  644. // sort($items);
  645. // }
  646. // $this->success('成功', $data);
  647. // }
  648. public function left_Productyear() {
  649. // 先从工单_质量考核汇总获取产品代号及各产品最新账期(sys_ny 格式 YYYYMM,如 202610)
  650. $list = \db('工单_质量考核汇总')
  651. ->field('产品代号, MAX(sys_ny) as latest_ny')
  652. ->where('产品代号', '<>', '')
  653. ->group('产品代号')
  654. ->select();
  655. // 根据产品代号到产品_基本资料获取客户编号和客户名称
  656. $codes = array_unique(array_map(function ($item) {
  657. return trim($item['产品代号']);
  658. }, $list));
  659. $productRows = \db('产品_基本资料')
  660. ->field('产品编号, 客户编号, 客户名称')
  661. ->whereIn('产品编号', $codes)
  662. ->select();
  663. $productMap = [];
  664. foreach ($productRows as $p) {
  665. $productMap[trim($p['产品编号'])] = [
  666. '客户编号' => trim($p['客户编号']),
  667. '客户名称' => trim($p['客户名称'])
  668. ];
  669. }
  670. // 按客户汇总,取该客户下所有产品的最新账期年份
  671. $customerYear = [];
  672. foreach ($list as $value) {
  673. $code = trim($value['产品代号']);
  674. if (!isset($productMap[$code])) {
  675. continue;
  676. }
  677. $khbh = $productMap[$code]['客户编号'];
  678. $khmc = $productMap[$code]['客户名称'];
  679. if (empty($khbh) || empty($khmc) || empty($value['latest_ny'])) {
  680. continue;
  681. }
  682. // 账期取自 sys_ny(YYYYMM),年份取前4位
  683. $latestNy = trim($value['latest_ny']);
  684. if (strlen($latestNy) < 6) {
  685. continue;
  686. }
  687. $year = substr($latestNy, 0, 4);
  688. $key = $khbh . '【' . $khmc . '】';
  689. if (!isset($customerYear[$key]) || $year > $customerYear[$key]['year']) {
  690. $customerYear[$key] = ['year' => $year, 'khbh' => $khbh];
  691. }
  692. }
  693. $data['翌星工单'] = [];
  694. $data['MN工单'] = [];
  695. foreach ($customerYear as $name => $info) {
  696. $khbh = $info['khbh'];
  697. $year = $info['year'];
  698. if (strpos($khbh, 'J') !== false || strpos($khbh, 'Y') !== false) {
  699. if (!isset($data['翌星工单'][$year])) {
  700. $data['翌星工单'][$year] = [];
  701. }
  702. array_push($data['翌星工单'][$year], $name);
  703. } else {
  704. if (!isset($data['MN工单'][$year])) {
  705. $data['MN工单'][$year] = [];
  706. }
  707. array_push($data['MN工单'][$year], $name);
  708. }
  709. }
  710. // 按年份排序(从最新到最旧)
  711. krsort($data['翌星工单']);
  712. krsort($data['MN工单']);
  713. // 对每个年份内的数据进行排序(可选)
  714. foreach ($data['翌星工单'] as $year => &$items) {
  715. sort($items);
  716. }
  717. foreach ($data['MN工单'] as $year => &$items) {
  718. sort($items);
  719. }
  720. $this->success('成功', $data);
  721. }
  722. /**
  723. * 01.产品年度投入产出率统计->数据汇总表
  724. */
  725. public function list_Productyear() {
  726. if ($this->request->isGet() === false) {
  727. $this->error('请求错误');
  728. }
  729. $param = $this->request->param();
  730. $rq = isset($param['rq']) ? trim($param['rq']) : '';
  731. $where = [];
  732. if (!empty($param['search'])){
  733. $where['产品代号|产品名称'] = ['like','%'.$param['search'].'%'];
  734. }
  735. // 客户筛选:根据客户编号到产品_基本资料找到对应产品编号,再过滤产品代号
  736. $codeFilter = null;
  737. if (!empty($param['khdh'])){
  738. $codeFilter = \db('产品_基本资料')
  739. ->where('客户编号', 'like', $param['khdh'].'%')
  740. ->column('产品编号');
  741. if (empty($codeFilter)) {
  742. $this->success('成功', []);
  743. }
  744. }
  745. // rq 为空时不限年份,即"整个表"查询
  746. $query = \db('工单_质量考核汇总')
  747. ->field('Gy0_gdbh, sys_ny, 实际投料, 入仓数量, 产品代号, 产品名称, 销售订单号, 订单数量,计量单位')
  748. ->where($where);
  749. if ($codeFilter !== null) {
  750. $query->whereIn('产品代号', $codeFilter);
  751. }
  752. if ($rq !== '') {
  753. $query->where('sys_ny', 'like', $rq . '%');
  754. }
  755. $qualityData = $query->select();
  756. if (empty($qualityData)) {
  757. $this->success('未查询到数据', []);
  758. }
  759. // 第一步:按工单去重,每个工单只取一条记录
  760. $uniqueByGongdan = [];
  761. foreach ($qualityData as $data) {
  762. $gdbh = $data['Gy0_gdbh'];
  763. if (!isset($uniqueByGongdan[$gdbh])) {
  764. $uniqueByGongdan[$gdbh] = $data;
  765. }
  766. }
  767. // 第二步:按产品代号和月份分组并计算总和
  768. $productMap = [];
  769. foreach ($uniqueByGongdan as $gdbh => $data) {
  770. $productCode = $data['产品代号'];
  771. $sysNy = trim($data['sys_ny']);
  772. // 从 sys_ny(YYYYMM) 提取月份 1-12
  773. $month = (int)substr($sysNy, 4, 2);
  774. if (!isset($productMap[$productCode])) {
  775. $productMap[$productCode] = [
  776. 'sys_rq' => substr($sysNy, 0, 4),
  777. '计量单位' => $data['计量单位'],
  778. '成品编码' => $data['产品代号'],
  779. '成品名称' => $data['产品名称'],
  780. 'months' => [],
  781. '实际投料' => 0,
  782. '入仓数量' => 0,
  783. '工单列表' => [] // 记录包含的工单编号
  784. ];
  785. }
  786. if (!isset($productMap[$productCode]['months'][$month])) {
  787. $productMap[$productCode]['months'][$month] = [
  788. '实际投料' => 0,
  789. '入仓数量' => 0
  790. ];
  791. }
  792. //实际投料先乘以10000再累加
  793. $adjustedActual = round($data['实际投料'] * 10000);
  794. // 累加月度数据
  795. $productMap[$productCode]['months'][$month]['实际投料'] += $adjustedActual;
  796. $productMap[$productCode]['months'][$month]['入仓数量'] += $data['入仓数量'];
  797. // 累加年度总量(实际投料已经乘以10000)
  798. $productMap[$productCode]['实际投料'] += $adjustedActual;
  799. $productMap[$productCode]['入仓数量'] += $data['入仓数量'];
  800. // 记录工单编号
  801. $productMap[$productCode]['工单列表'][] = $data['Gy0_gdbh'];
  802. }
  803. //月份
  804. $monthNames = [
  805. 1 => '1月', 2 => '2月', 3 => '3月', 4 => '4月',
  806. 5 => '5月', 6 => '6月', 7 => '7月', 8 => '8月',
  807. 9 => '9月', 10 => '10月', 11 => '11月', 12 => '12月'
  808. ];
  809. //合并数据并计算合格率
  810. $result = [];
  811. foreach ($productMap as $productCode => $productData) {
  812. $resultItem = [
  813. 'sys_rq' => $productData['sys_rq'],
  814. '成品编码' => $productData['成品编码'],
  815. '成品名称' => $productData['成品名称'],
  816. '实际投料' => $productData['实际投料'],
  817. '入仓数量' => $productData['入仓数量'],
  818. '计量单位' => $productData['计量单位'],
  819. '工单数量' => count($productData['工单列表']),
  820. '工单列表' => implode(', ', $productData['工单列表'])
  821. ];
  822. // 初始化各月份数据
  823. foreach ($monthNames as $monthNum => $monthName) {
  824. $resultItem[$monthName] = '-';
  825. }
  826. // 处理该产品的月度数据
  827. if (isset($productData['months'])) {
  828. foreach ($productData['months'] as $month => $monthData) {
  829. $adjustedActual = $monthData['实际投料'];
  830. $delivery = $monthData['入仓数量'];
  831. // 计算月度合格率
  832. if ($adjustedActual > 0) {
  833. $monthlyRate = round(($delivery / $adjustedActual) * 100, 2);
  834. $resultItem[$monthNames[$month]] = $monthlyRate . '%';
  835. } else if ($delivery > 0) {
  836. $resultItem[$monthNames[$month]] = '100%';
  837. } else {
  838. $resultItem[$monthNames[$month]] = '0%';
  839. }
  840. }
  841. }
  842. // 计算年度综合合格率
  843. if ($resultItem['实际投料'] > 0) {
  844. $overallRate = round(($resultItem['入仓数量'] / $resultItem['实际投料']) * 100, 2);
  845. $resultItem['综合合格率'] = $overallRate . '%';
  846. } else if ($resultItem['入仓数量'] > 0) {
  847. $resultItem['综合合格率'] = '100%';
  848. } else {
  849. $resultItem['综合合格率'] = '0%';
  850. }
  851. $result[] = $resultItem;
  852. }
  853. $this->success('成功', $result);
  854. }
  855. /**
  856. * 01.产品年度投入产出率统计->数据明细表
  857. */
  858. public function list_Productmonth() {
  859. if ($this->request->isGet() === false) {
  860. $this->error('请求错误');
  861. }
  862. $param = $this->request->param();
  863. $productCode = $param['product_code'] ?? '';
  864. $year = $param['year'] ?? date('Y');
  865. if (empty($productCode)) {
  866. $this->error('请提供成品编码');
  867. }
  868. // 1. 查询对应的工单数据
  869. $qualityData = \db('工单_质量考核汇总')
  870. ->field('Gy0_gdbh as 工单编号, 订单数量, 入仓日期, sys_ny, 实际投料, 入仓数量, 产品代号, 产品名称, 销售订单号')
  871. ->where('产品代号', $productCode)
  872. ->where('sys_ny', 'like', $year . '%')
  873. ->order('入仓日期','desc')
  874. ->select();
  875. if (empty($qualityData)) {
  876. $this->success('该产品无质量考核数据', []);
  877. }
  878. // 根据产品代号到产品_基本资料获取客户编号和客户名称
  879. $codes = array_unique(array_map(function ($item) {
  880. return trim($item['产品代号']);
  881. }, $qualityData));
  882. $productRows = \db('产品_基本资料')
  883. ->field('产品编号, 客户编号, 客户名称')
  884. ->whereIn('产品编号', $codes)
  885. ->select();
  886. $productMap = [];
  887. foreach ($productRows as $p) {
  888. $productMap[trim($p['产品编号'])] = [
  889. '客户编号' => trim($p['客户编号']),
  890. '客户名称' => trim($p['客户名称'])
  891. ];
  892. }
  893. // 2. 先按工单去重,每个工单只取一条记录
  894. $uniqueByGongdan = [];
  895. foreach ($qualityData as $data) {
  896. $gdbh = $data['工单编号'];
  897. if (!isset($uniqueByGongdan[$gdbh])) {
  898. $uniqueByGongdan[$gdbh] = $data;
  899. }
  900. }
  901. // 3. 按月份分组累计数据
  902. $monthlyData = [];
  903. // $monthNames = [
  904. // 1 => '1月', 2 => '2月', 3 => '3月', 4 => '4月',
  905. // 5 => '5月', 6 => '6月', 7 => '7月', 8 => '8月',
  906. // 9 => '9月', 10 => '10月', 11 => '11月', 12 => '12月'
  907. // ];
  908. foreach ($uniqueByGongdan as $gdbh => $data) {
  909. $sysNy = trim($data['sys_ny']);
  910. $month = (int)substr($sysNy, 4, 2); // 1-12 的月份数字
  911. // 初始化月份数据
  912. if (!isset($monthlyData[$data['工单编号']])) {
  913. $code = trim($data['产品代号']);
  914. $khbh = isset($productMap[$code]) ? $productMap[$code]['客户编号'] : '';
  915. $khmc = isset($productMap[$code]) ? $productMap[$code]['客户名称'] : '';
  916. $monthlyData[$data['工单编号']] = [
  917. '月份' => $month,
  918. '订单数量' => 0,
  919. '实际投料' => 0,
  920. '入仓数量' => 0,
  921. '入仓日期' => $data['入仓日期'],
  922. '客户代号' => $khbh,
  923. '客户名称' => $khmc,
  924. '产品代号' => $data['产品代号'],
  925. '产品名称' => $data['产品名称'],
  926. '销售订单号' => $data['销售订单号']
  927. ];
  928. }
  929. $monthlyData[$data['工单编号']]['订单数量'] += round($data['订单数量'] * 10000);
  930. $monthlyData[$data['工单编号']]['实际投料'] += round($data['实际投料'] * 10000);
  931. $monthlyData[$data['工单编号']]['入仓数量'] += round($data['入仓数量']);
  932. // 记录工单编号
  933. $monthlyData[$data['工单编号']]['工单编号'] = $data['工单编号'];
  934. }
  935. // 4. 计算每个月的实际合格率并整理结果
  936. $detailList = [];
  937. foreach ($monthlyData as $month => $monthData) {
  938. // 计算实际合格率
  939. $actualRate = '-';
  940. if ($monthData['实际投料'] > 0) {
  941. $actualRate = round(($monthData['入仓数量'] / $monthData['实际投料']) * 100, 2) . '%';
  942. } else if ($monthData['入仓数量'] > 0) {
  943. $actualRate = '100%';
  944. } else {
  945. $actualRate = '0%';
  946. }
  947. $detailList[$month] = [
  948. '日期' => $monthData['月份'],
  949. '入仓日期' => substr($monthData['入仓日期'], 0, 10),
  950. '订单数量' => $monthData['订单数量'],
  951. '实际投料' => $monthData['实际投料'],
  952. '入仓数量' => $monthData['入仓数量'],
  953. '实际合格率' => $actualRate,
  954. '客户代号' => $monthData['客户代号'],
  955. '客户名称' => $monthData['客户名称'],
  956. '产品代号' => $monthData['产品代号'],
  957. '产品名称' => $monthData['产品名称'],
  958. '销售订单号' => $monthData['销售订单号'],
  959. '工单编号' => $monthData['工单编号']
  960. ];
  961. }
  962. $this->success('成功', array_values($detailList));
  963. }
  964. /**
  965. * 02.产品月度投入产出率统计->左侧菜单
  966. */
  967. public function left_Productmonth() {
  968. $list = \db('工单_质量考核汇总')
  969. ->field('客户代号, 客户名称, sys_ny')
  970. ->where('客户代号', '<>', '')
  971. ->order('sys_ny desc')
  972. ->select();
  973. $data['翌星工单'] = [];
  974. $data['MN工单'] = [];
  975. $seenEntries = [];
  976. foreach ($list as $value) {
  977. $khbh = rtrim($value['客户代号']);
  978. $khmc = rtrim($value['客户名称']);
  979. $ny = trim((string)$value['sys_ny']);
  980. if (empty($khmc) || strlen($ny) < 6) {
  981. continue;
  982. }
  983. $year = substr($ny, 0, 4);
  984. $ym = substr($ny, 0, 6);
  985. $name = $khbh . '【' . $khmc . '】';
  986. $uniqueKey = $khbh . '_' . $khmc . '_' . $ym;
  987. if (isset($seenEntries[$uniqueKey])) {
  988. continue;
  989. }
  990. $seenEntries[$uniqueKey] = true;
  991. if (strpos($khbh, 'J') !== false || strpos($khbh, 'Y') !== false) {
  992. $data['翌星工单'][$year][$ym][] = $name;
  993. } else {
  994. $data['MN工单'][$year][$ym][] = $name;
  995. }
  996. }
  997. // 按年份、月份倒序排序
  998. krsort($data['翌星工单']);
  999. krsort($data['MN工单']);
  1000. foreach ($data as &$years) {
  1001. foreach ($years as &$months) {
  1002. krsort($months);
  1003. foreach ($months as &$items) {
  1004. sort($items);
  1005. }
  1006. unset($items);
  1007. }
  1008. unset($months);
  1009. }
  1010. unset($years);
  1011. $this->success('成功', $data);
  1012. }
  1013. /**
  1014. * 02.产品月度投入产出率统计->数据汇总表
  1015. */
  1016. public function list_Productmonth_hz() {
  1017. if ($this->request->isGet() === false) {
  1018. $this->error('请求错误');
  1019. }
  1020. $param = $this->request->param();
  1021. $ym = trim((string)($param['ym'] ?? ''));
  1022. $rq = trim((string)($param['rq'] ?? ''));
  1023. // 解析年月:优先使用 ym(YYYYMM),否则回退 year + month
  1024. if ($ym !== '' && preg_match('/^\d{6}$/', $ym)) {
  1025. $year = substr($ym, 0, 4);
  1026. $month = (int)substr($ym, 4, 2);
  1027. } else {
  1028. $year = $rq !== '' ? $rq : date('Y');
  1029. $month = !empty($param['month']) ? (int)$param['month'] : (int)date('n');
  1030. }
  1031. // 账期(YYYYMM),以 sys_ny 为准
  1032. $ny = $year . str_pad((string)$month, 2, '0', STR_PAD_LEFT);
  1033. $where = [];
  1034. if (!empty($param['search'])) {
  1035. $where['产品代号|产品名称'] = ['like', '%' . $param['search'] . '%'];
  1036. }
  1037. if (!empty($param['khdh'])) {
  1038. $where['客户代号'] = ['like', $param['khdh'] . '%'];
  1039. }
  1040. $qualityData = \db('工单_质量考核汇总')
  1041. ->field('Gy0_gdbh, 客户代号, 客户名称, sys_ny, 实际投料, 入仓数量, 产品代号, 产品名称, 销售订单号, 订单数量, 计量单位')
  1042. ->where('sys_ny', $ny)
  1043. ->where($where)
  1044. ->select();
  1045. if (empty($qualityData)) {
  1046. $this->success('未查询到数据', []);
  1047. }
  1048. // 第一步:按工单去重,每个工单只取一条记录
  1049. $uniqueByGongdan = [];
  1050. foreach ($qualityData as $data) {
  1051. $gdbh = $data['Gy0_gdbh'];
  1052. if (!isset($uniqueByGongdan[$gdbh])) {
  1053. $uniqueByGongdan[$gdbh] = $data;
  1054. }
  1055. }
  1056. // 印件信息(按工单编号)
  1057. $yjMap = [];
  1058. $gdbhList = array_keys($uniqueByGongdan);
  1059. if (!empty($gdbhList)) {
  1060. $yjList = \db('工单_印件资料')
  1061. ->field('Yj_Gdbh, Yj_Yjdh, yj_yjmc')
  1062. ->whereIn('Yj_Gdbh', $gdbhList)
  1063. ->select();
  1064. foreach ($yjList as $yj) {
  1065. $yjMap[$yj['Yj_Gdbh']] = $yj;
  1066. }
  1067. }
  1068. // 第二步:按成品编码分组汇总
  1069. $productMap = [];
  1070. foreach ($uniqueByGongdan as $gdbh => $data) {
  1071. $productCode = $data['产品代号'];
  1072. if (!isset($productMap[$productCode])) {
  1073. $productMap[$productCode] = [
  1074. 'sys_rq' => $ny,
  1075. '成品编码' => $data['产品代号'],
  1076. '成品名称' => $data['产品名称'],
  1077. '客户编号' => rtrim($data['客户代号']),
  1078. '客户名称' => rtrim($data['客户名称']),
  1079. '计量单位' => $data['计量单位'],
  1080. '订单数量' => 0,
  1081. '实际投料' => 0,
  1082. '入仓数量' => 0,
  1083. '印件代号' => [],
  1084. '印件名称' => [],
  1085. '工单列表' => []
  1086. ];
  1087. }
  1088. $yj = isset($yjMap[$gdbh]) ? $yjMap[$gdbh] : [];
  1089. $productMap[$productCode]['印件代号'][] = isset($yj['Yj_Yjdh']) ? rtrim($yj['Yj_Yjdh']) : '';
  1090. $productMap[$productCode]['印件名称'][] = isset($yj['yj_yjmc']) ? rtrim($yj['yj_yjmc']) : '';
  1091. $productMap[$productCode]['订单数量'] += round($data['订单数量'] * 10000);
  1092. $productMap[$productCode]['实际投料'] += round($data['实际投料'] * 10000);
  1093. $productMap[$productCode]['入仓数量'] += $data['入仓数量'];
  1094. $productMap[$productCode]['工单列表'][] = $data['Gy0_gdbh'];
  1095. }
  1096. // 第三步:计算投入产出率(入仓数量 / 实际投料)
  1097. $result = [];
  1098. foreach ($productMap as $productCode => $productData) {
  1099. if ($productData['实际投料'] > 0) {
  1100. $rate = round(($productData['入仓数量'] / $productData['实际投料']) * 100, 2) . '%';
  1101. } else if ($productData['入仓数量'] > 0) {
  1102. $rate = '100%';
  1103. } else {
  1104. $rate = '0%';
  1105. }
  1106. $result[] = [
  1107. 'sys_rq' => $productData['sys_rq'],
  1108. '成品编码' => $productData['成品编码'],
  1109. '成品名称' => $productData['成品名称'],
  1110. '印件代号' => implode('、', array_unique(array_filter($productData['印件代号']))),
  1111. '印件名称' => implode('、', array_unique(array_filter($productData['印件名称']))),
  1112. '订单数量' => $productData['订单数量'],
  1113. '实际投料' => $productData['实际投料'],
  1114. '入仓数量' => $productData['入仓数量'],
  1115. '投入产出率' => $rate,
  1116. '计量单位' => $productData['计量单位'],
  1117. '工单数量' => count($productData['工单列表']),
  1118. '工单列表' => implode(', ', $productData['工单列表']),
  1119. '客户编号' => $productData['客户编号'],
  1120. '客户名称' => $productData['客户名称']
  1121. ];
  1122. }
  1123. $this->success('成功', $result);
  1124. }
  1125. /**
  1126. * 02.产品月度投入产出率统计->数据明细表
  1127. */
  1128. public function list_Productmonth_mx() {
  1129. if ($this->request->isGet() === false) {
  1130. $this->error('请求错误');
  1131. }
  1132. $param = $this->request->param();
  1133. $productCode = trim((string)($param['product_code'] ?? ''));
  1134. $ym = trim((string)($param['ym'] ?? ''));
  1135. $rq = trim((string)($param['rq'] ?? ''));
  1136. if (empty($productCode)) {
  1137. $this->error('请提供成品编码');
  1138. }
  1139. // 解析年月:优先使用 ym(YYYYMM),否则回退 year + month
  1140. if ($ym !== '' && preg_match('/^\d{6}$/', $ym)) {
  1141. $year = substr($ym, 0, 4);
  1142. $month = (int)substr($ym, 4, 2);
  1143. } else {
  1144. $year = $rq !== '' ? $rq : date('Y');
  1145. $month = !empty($param['month']) ? (int)$param['month'] : (int)date('n');
  1146. }
  1147. // 账期(YYYYMM),以 sys_ny 为准
  1148. $ny = $year . str_pad((string)$month, 2, '0', STR_PAD_LEFT);
  1149. $query = \db('工单_质量考核汇总')
  1150. ->field('Gy0_gdbh as 工单编号, 订单数量, 入仓日期, sys_ny, 实际投料, 入仓数量, 客户代号, 客户名称, 产品代号, 产品名称, 销售订单号')
  1151. ->where('产品代号', $productCode)
  1152. ->where('sys_ny', $ny);
  1153. if (!empty($param['khdh'])) {
  1154. $query->where('客户代号', 'like', $param['khdh'] . '%');
  1155. }
  1156. $qualityData = $query->order('入仓日期', 'desc')->select();
  1157. if (empty($qualityData)) {
  1158. $this->success('该产品无质量考核数据', []);
  1159. }
  1160. // 按工单去重,每个工单只取一条记录
  1161. $uniqueByGongdan = [];
  1162. foreach ($qualityData as $data) {
  1163. $gdbh = $data['工单编号'];
  1164. if (!isset($uniqueByGongdan[$gdbh])) {
  1165. $uniqueByGongdan[$gdbh] = $data;
  1166. }
  1167. }
  1168. // 印件信息(按工单编号)
  1169. $yjMap = [];
  1170. $gdbhList = array_keys($uniqueByGongdan);
  1171. if (!empty($gdbhList)) {
  1172. $yjList = \db('工单_印件资料')
  1173. ->field('Yj_Gdbh, Yj_Yjdh, yj_yjmc, Yj_Yjno')
  1174. ->whereIn('Yj_Gdbh', $gdbhList)
  1175. ->select();
  1176. foreach ($yjList as $yj) {
  1177. $yjMap[$yj['Yj_Gdbh']] = $yj;
  1178. }
  1179. }
  1180. $detailList = [];
  1181. foreach ($uniqueByGongdan as $gdbh => $data) {
  1182. $actual = round($data['实际投料'] * 10000);
  1183. $delivery = round($data['入仓数量']);
  1184. // 投入产出率 = 入仓数量 / 实际投料
  1185. if ($actual > 0) {
  1186. $rate = round(($delivery / $actual) * 100, 2) . '%';
  1187. } else if ($delivery > 0) {
  1188. $rate = '100%';
  1189. } else {
  1190. $rate = '0%';
  1191. }
  1192. $yj = isset($yjMap[$gdbh]) ? $yjMap[$gdbh] : [];
  1193. $detailList[] = [
  1194. '工单编号' => $data['工单编号'],
  1195. '印件号' => isset($yj['Yj_Yjno']) ? rtrim($yj['Yj_Yjno']) : '',
  1196. '印件代号' => isset($yj['Yj_Yjdh']) ? rtrim($yj['Yj_Yjdh']) : '',
  1197. '印件名称' => isset($yj['yj_yjmc']) ? rtrim($yj['yj_yjmc']) : '',
  1198. '订单数量' => round($data['订单数量'] * 10000),
  1199. '实际投料' => $actual,
  1200. '入仓数量' => $delivery,
  1201. '完工时间' => substr($data['入仓日期'], 0, 10),
  1202. '投入产出率' => $rate,
  1203. '客户编号' => rtrim($data['客户代号']),
  1204. '客户名称' => rtrim($data['客户名称']),
  1205. '成品编码' => $data['产品代号'],
  1206. '成品名称' => $data['产品名称'],
  1207. '销售订单号' => $data['销售订单号']
  1208. ];
  1209. }
  1210. $this->success('成功', $detailList);
  1211. }
  1212. }