mes-dc-server-api/application/api/controller/OrderSuperLoss.php

<?php

namespace app\api\controller;

use app\common\controller\Api;
use think\Cache;
use think\Exception;
use think\Log;
use \think\Request;
use \think\Db;
/**
 * 报表接口
 */
class OrderSuperLoss extends Api
{
    protected $noNeedLogin = ['*'];
    protected $noNeedRight = ['*'];

    /**
     * 首页-已核查成品编号
     *
     */
    public function index()
    {
        $this->success('请求成功');
    }


    /**
     * 获取工单接单日期的年月列表（左侧菜单栏）
     * @return void
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function getWorkOrderDates()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }

        $list = \think\Db::table('工单_基本资料')
            ->whereNull('Mod_rq')  // 排除 Mod_rq 不为 null 的数据
            ->where('落货日期', '<>', '')  // 排除空值
            ->field(\think\Db::raw('DATE_FORMAT(落货日期, "%Y-%m") as 年月'))
            ->group('年月')
            ->order('年月', 'desc')  // 按年月倒序排列，最新的在前面
            ->select();

        if (empty($list)) {
            $this->success('', []);
        }

        // 提取年月值组成数组
        $data = [];
        foreach ($list as $item) {
            $data[] = $item['年月'];
        }

        $this->success('成功', $data);
    }

    /**
     * 根据月份获取工单数据（排除已修改记录）
     * @return void
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function getWorkOrdersByMonth()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }

        $rq = $this->request->get('rq');
        if (empty($rq) || !preg_match('/^\d{4}-\d{2}$/', $rq)) {
            $this->error('参数格式错误，请使用 YYYY-MM 格式');
        }

        // 生成缓存key
        $cacheKey = 'work_orders_' . $rq;
        
        // 尝试从缓存获取
        $cachedData = cache($cacheKey);
        if ($cachedData !== false) {
            $this->success('成功', $cachedData);
            return;
        }

        // 计算月份起止日期
        list($year, $month) = explode('-', $rq);
        $start = "{$year}-{$month}-01";
        $end = date('Y-m-d', strtotime("{$year}-{$month}-01 +1 month"));

        // 使用原生SQL查询，避免框架对中文字段名的校验
        $sql = "SELECT 
                    g.Uniqid,
                    g.订单编号,
                    g.审核 as 核批,
                    g.审核日期 as 核批日期,
                    g.接单日期 as 下单日期,
                    g.落货日期 as 货期,
                    g.生产款号 as 款号,
                    g.订单数量,
                    g.工单入仓数量 as 入库数量,
                    g.款式,
                    g.客户编号 as 客人编号,
                    g.辅料计划入库时间 as 辅料计划入库时间,
                    
                    (SELECT MIN(rq) FROM 库存_出入库明细 WHERE order_id = g.订单编号 AND name = '入库') as 面料入库时间,
                    
                    (SELECT MIN(sczl_rq) FROM 设备_产量计酬 WHERE 订单编号 = g.订单编号 AND 工序名称 = '裁剪' AND Mod_rq IS NULL) as 开裁日期,
                    (SELECT SUM(数量) FROM 设备_产量计酬 WHERE 订单编号 = g.订单编号 AND 工序名称 = '裁剪' AND Mod_rq IS NULL) as 实裁数量,
                    
                    (SELECT MIN(sczl_rq) FROM 设备_产量计酬 WHERE 订单编号 = g.订单编号 AND 工序名称 = '车缝' AND Mod_rq IS NULL) as 上车位日期,
                    (SELECT MIN(sczl_rq) FROM 设备_产量计酬 WHERE 订单编号 = g.订单编号 AND 工序名称 = '车缝' AND 尾包 = 1 AND Mod_rq IS NULL) as 车位完成日期,
                    
                    (SELECT MIN(sczl_rq) FROM 设备_产量计酬 WHERE 订单编号 = g.订单编号 AND 工序名称 = '手工' AND 尾包 = 1 AND Mod_rq IS NULL) as 后道完成日期,
                    
                    (SELECT SUM(数量) FROM 设备_产量计酬 WHERE 订单编号 = g.订单编号 AND 工序名称 = '包装' AND Mod_rq IS NULL) as 已完成数量,
                    
                    (SELECT GROUP_CONCAT(DISTINCT SUBSTRING(sczl_jtbh, 3, 2) ORDER BY SUBSTRING(sczl_jtbh, 3, 2) SEPARATOR '、') 
                     FROM 设备_产量计酬 
                     WHERE 订单编号 = g.订单编号 AND 工序名称 = '车缝' AND Mod_rq IS NULL) as 生产组别,
                    
                    NULL as 辅料入库时间,
                    NULL as 台产,
                    NULL as 产前样批核,
                    NULL as 备注
                    
                FROM 工单_基本资料 g
                WHERE g.落货日期 >= '{$start}'
                    AND g.落货日期 < '{$end}'
                    AND g.Mod_rq IS NULL
                GROUP BY g.订单编号
                ORDER BY g.落货日期 ASC";

        $list = Db::query($sql);

        if (empty($list)) {
            $this->success('', []);
        }

        // 处理日期字段，去掉时分秒
        $dateFields = ['下单日期', '货期', '面料入库时间', '辅料入库时间', '开裁日期', '上车位日期', '车位完成日期', '后道完成日期'];

        foreach ($list as &$item) {
            foreach ($dateFields as $field) {
                if (isset($item[$field]) && !empty($item[$field])) {
                    $item[$field] = date('Y-m-d', strtotime($item[$field]));
                }
            }
            if (empty($item['生产组别'])) {
                $item['生产组别'] = '';
            }
            // 确保数值字段为整数
            $item['实裁数量'] = intval($item['实裁数量'] ?? 0);
            $item['已完成数量'] = intval($item['已完成数量'] ?? 0);
            $item['订单数量'] = intval($item['订单数量'] ?? 0);
            $item['入库数量'] = intval($item['入库数量'] ?? 0);
        }

        // 存入缓存，有效期1小时
        cache($cacheKey, $list, 3600);

        $this->success('成功', $list);
    }

    /**
     * 清除工单数据缓存
     * @param string $rq 月份（Y-m格式），为空则清除所有当前月份的缓存
     */
    public function clearWorkOrderCache()
    {
        $rq = $this->request->get('rq');
        if (empty($rq)) {
            $this->error('请指定要清除缓存的月份');
        }
        
        if (!preg_match('/^\d{4}-\d{2}$/', $rq)) {
            $this->error('参数格式错误，请使用 YYYY-MM 格式');
        }
        
        $cacheKey = 'work_orders_' . $rq;
        cache($cacheKey, null);
        
        $this->success('缓存已清除');
    }

}