mes-server-api/application/api/controller/PrintingPlate.php

<?php

namespace app\api\controller;

use app\common\controller\Api;
use think\Request;

/**
 * 印版库管理
 */

class PrintingPlate extends Api
{
    protected $noNeedLogin = ['*'];
    protected $noNeedRight = ['*'];


    /**
     * 印版库管理左侧菜单
     * @return void
     */
    public function getTab()
    {
        if (!$this->request->isGet()) {
            $this->error('请求错误');
        }

        $data = [
            'MN印版' => $this->buildTree('05'),
            '翌星印版' => $this->buildTree('Y05')
        ];
        $this->success('成功', $data);
    }
    //私有方法，构建数据
    private function buildTree($prefix)
    {
        $list = db('物料_存货结构')
            ->where('编号', 'like', $prefix . '%')
            ->order('编号', 'asc')
            ->select();

        $map = [];
        $tree = [];

        // 创建编号映射表
        foreach ($list as $item) {
            $map[$item['编号']] = $item['名称'];
        }

        // 构建树形结构
        foreach ($list as $item) {
            $code = $item['编号'];
            $name = $item['名称'];
            $key = "{$code} {$name}";
            $depth = strlen($code) - strlen($prefix);

            switch ($depth) {
                case 0: // 根节点 (05/Y05)
                    $tree[$key] = [];
                    break;

                case 2: // 二级节点 (0501/Y0501)
                    $parentCode = substr($code, 0, strlen($prefix));
                    $parentKey = "{$parentCode} {$map[$parentCode]}";
//                    $number = db('产品_印版库')->where('存货编码','like',$code.'%')->count();
//                    if ($number > 0) {{
                        $tree[$parentKey][$key] = [];
//                    }}

                    break;

                case 4: // 三级节点 (050100/Y050101)
                    $parentCode = substr($code, 0, strlen($prefix) + 2);
                    $grandCode = substr($code, 0, strlen($prefix));

                    $grandKey = "{$grandCode} {$map[$grandCode]}";
                    $parentKey = "{$parentCode} {$map[$parentCode]}";

                    if (isset($tree[$grandKey][$parentKey])) {
                        $tree[$grandKey][$parentKey][] = $key;
                    }
                    break;
            }
        }

        return $tree;
    }


    /**
     * 存货编码列表
     * @return void
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */

    public function MaterailCodeList()
    {
        if (!$this->request->isGet()) {
            $this->error('请求错误');
        }
        $params = $this->request->param();
        if (empty($params)) {
            $this->error('参数错误');
        }

        $page = max(intval($params['page']), 1); // 确保页码 >=1
        $limit = max(intval($params['limit']), 10); // 最小限制10条
        $where = [];

        // 条件优化：使用索引列优先
        if (!empty($params['code'])) {
            $codeLength = preg_match('/[a-zA-Z]/', $params['code']) ? 7 : 6;
            $where['a.存货编码'] = ['like', substr($params['code'], 0, $codeLength) . '%'];
        }

        if (!empty($params['key']) && isset($params['key'])) {
            $where['a.报废日期'] = '1900-01-01 00:00:00';
        }

        // 全文搜索优化：拆分为单字段 OR 避免全局模糊索引失效
        if (!empty($params['search'])) {
            $search = trim($params['search']);
            $where['a.存货编码|b.物料名称|c.Yb_工单编号'] = ['like', "%{$search}%"];
        }

        // 1. 解决 COUNT 性能问题：单独处理避免大表 GROUP BY
        $countQuery = db('产品_印版库')
            ->alias('a')
            ->join('物料_存货编码 b', 'a.存货编码 = b.物料代码')
            ->join('工单_印版领用记录 c', 'a.存货编码 = c.Yb_存货编码 AND a.供方批号 = c.Yb_供方批号', 'LEFT')
            ->where($where);

        $total = $countQuery->group('a.存货编码, a.供方批号')->count();

        // 2. 主查询优化：移除子查询改用 JOIN 聚合
        $baseQuery = db('产品_印版库')
            ->alias('a')
            ->join('物料_存货编码 b', 'a.存货编码 = b.物料代码')
            ->join('工单_印版领用记录 c', 'a.存货编码 = c.Yb_存货编码 AND a.供方批号 = c.Yb_供方批号', 'LEFT')
            ->where($where)
            ->group('a.存货编码, a.供方批号')
            ->order('a.报废日期, a.存货编码 , a.供方批号')  // 明确表别名
            ->limit(($page - 1) * $limit, $limit);

        // 3. 累计印数计算优化：使用 LEFT JOIN 聚合结果
        $subSum = db('工单_印版领用记录')
            ->field('Yb_存货编码, Yb_供方批号, SUM(Yb_印数) as total')
            ->group('Yb_存货编码, Yb_供方批号')
            ->buildSql();

        $list = $baseQuery
            ->Join([$subSum => 'd'], 'd.Yb_存货编码 = a.存货编码 AND d.Yb_供方批号 = a.供方批号','left')
            ->field([
                'rtrim(a.存货编码) as 存货编码',
                'rtrim(b.物料名称) as 物料名称',
                'rtrim(a.印版名称) as 印版名称',
                'rtrim(a.供方批号) as 供方批号',
                'DATE(a.制造日期) as 制造日期',
                "CASE 
                WHEN a.报废日期 = '1900-01-01 00:00:00' THEN NULL 
                ELSE DATE(a.报废日期) 
             END as 报废日期",
                'a.原始印数',
                'a.考核印数',
                'a.UniqID',
                "MAX(c.UniqID) as GDUID",
                'rtrim(a.Sys_id) as 创建用户',
                'a.Sys_rq as 创建日期',
                'a.Mod_rq as 修改时间',
                "COALESCE(d.total, 0) as 累计印数", // 直接使用聚合结果
                "MAX(CASE 
                WHEN c.Yb_领用日期 IS NOT NULL AND c.Yb_退还日期 IS NULL 
                THEN c.Yb_工单编号 
             END) as 工单编号"
            ])
            ->select();

        if (empty($list)) {
            $this->error('未找到相关记录');
        }

        $this->success('查询成功', [
            'data' => $list,
            'total' => $total,
        ]);
    }
    /**
     * 印版管理印版修改
     * @return void
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function MaterailDetail()
    {
        if ($this->request->isGet() === false){
            $this->error('请求错误');
        }
        $param = $this->request->param();
        if (empty($param) || !isset($param['UniqID'])){
            $this->error('参数错误');
        }
        $result = db('产品_印版库')
            ->alias('a')
            ->join('物料_存货编码 b', 'a.存货编码 = b.物料代码')
            ->where('a.UniqID', $param['UniqID'])
            ->order('报废日期, a.存货编码')
            ->field('
                rtrim(a.存货编码) as 存货编码,
                rtrim(b.物料名称) as 物料名称,
                rtrim(a.印版名称) as 印版备注,
                rtrim(a.供方批号) as 供方批号,
                DATE(a.制造日期) as 制造日期,
                CASE 
                    WHEN a.报废日期 = "1900-01-01 00:00:00" THEN NULL 
                    ELSE DATE(a.报废日期) 
                END as 报废日期,
                a.原始印数,
                a.考核印数,
                a.UniqID
            ')
            ->find();
        if (empty($result)) {
            $this->error('未找到数据');
        }else{
            $this->success('成功', $result);
        }
    }


    /**
     * 印版资料新增修改
     * @return void
     * @throws \think\Exception
     * @throws \think\db\exception\BindParamException
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     * @throws \think\exception\PDOException
     */
    public function MaterailEdit()
    {
        if ($this->request->isPost() === false){
            $this->error('请求错误');
        }
        $param = Request::instance()->post();
        if (empty($param) || !isset($param['code'])){
            $this->error('参数错误');
        }
        $data = [
            '存货编码'   => $param['code'],
            '供方批号'   => $param['batch'],
            '印版名称'   => $param['desc'],
            '制造日期'   => $param['Manufactur_date'],
            '原始印数'   => $param['start_num'],
            '考核印数'   => $param['Assessment_num'],
            '报废日期'   => $param['Scrappe_date']?:'1900-01-01 00:00:00',
            'Sys_id'    => $param['sys_id']
        ];
        //查询数据是否存在，存在则修改，不存在则增加
        $res = db('产品_印版库')
            ->where('存货编码',$param['code'])
            ->where('供方批号',$param['batch'])
            ->find();
        if (empty($res)){
            $data['Sys_rq'] = date('Y-m-d H:i:s',time());
            if (isset($param['number']) || $param['number'] !== '' || $param['number'] !== '0'){
                if ($param['sist'] === '凹丝印车间'){
                    $data['供方批号'] = $param['batch'].'-'.$param['number'];
                    $sql = db('产品_印版库')->fetchSql(true)->insert($data);
                }else{
                    for ($i = 1; $i <= $param['number']; $i++){
                        if ($i<10){
                            $number = '0'.$i;
                        }else{
                            $number = $i;
                        }
                        $data['供方批号'] = $param['batch'].'-'.$number;
                        $resData[] = $data;
                    }
                    $sql = db('产品_印版库')->fetchSql(true)->insertAll($resData);
                }

            }else{
                $sql = db('产品_印版库')->fetchSql(true)->insert($data);
            }
        }else{
            $UniqID = $param['UniqID'];
            $data['Mod_rq'] = date('Y-m-d H:i:s',time());
            $sql = db('产品_印版库')->where('UniqID',$UniqID)->fetchSql(true)->update($data);
        }
        $result = db()->query($sql);
        if ($result === false){
            $this->error('修改失败');
        }else{
            $this->success('修改成功');
        }
    }


    /**
     * 印版新增->存货编码获取
     * @return void
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function getInventoryCode()
    {
        if (!$this->request->isGet()) {
            $this->error('请求错误');
        }

        $param = $this->request->param();
        if (empty($param) || !isset($param['code'])) {
            $this->error('参数错误');
        }
        // 提取前缀
        $codeLength = preg_match('/[a-zA-Z]/', $param['code']) ? 5 : 4;
        $code = substr($param['code'], 0, $codeLength);
        $where['物料代码'] = ['like', $code . '%'];

        if (!empty($param['search'])) {
            $where['物料代码'] =  $param['search'];
        }
        // 查询物料存货编码
        $list = db('物料_存货编码')
            ->where($where)
            ->field('rtrim(物料代码) as 物料代码, rtrim(物料名称) as 物料名称, rtrim(规格) as 规格')
            ->select();
        // 数据处理
        $data = [];
        foreach ($list as $item) {
            // 提取物料代码的前缀
            $prefixLength = preg_match('/[a-zA-Z]/', $item['物料代码']) ? [3, 5, 7] : [2, 4, 6];

            // 使用普通的数组映射替代箭头函数
            $num1 = substr($item['物料代码'], 0, $prefixLength[0]);
            $num2 = substr($item['物料代码'], 0, $prefixLength[1]);
            $num3 = substr($item['物料代码'], 0, $prefixLength[2]);

            // 批量查询物料结构名称
            $names = db('物料_存货结构')
                ->whereIn('编号', [$num1, $num2, $num3])
                ->column('名称', '编号');

            // 将数据组织进数组
            $name1 = isset($names[$num1]) ? $names[$num1] : '';
            $name2 = isset($names[$num2]) ? $names[$num2] : '';
            $name3 = isset($names[$num3]) ? $names[$num3] : '';

            if (!isset($data["$num1/$name1"][$num2][$num3.'/'.$name3])) {
                $data["$num1/$name1"][$num2][$num3.'/'.$name3] = [];
            }

            $data["$num1/$name1"][$num2][$num3.'/'.$name3][] = "{$item['物料代码']}/{$item['物料名称']}/{$item['规格']}";
        }
        $this->success('成功', $data);
    }


    /**
     * 印版领用
     * @return void
     * @throws \think\db\exception\BindParamException
     * @throws \think\exception\PDOException
     */
    public function PrintingPlateReceive()
    {
        if (!$this->request->isPost()) {
            $this->error('请求错误');
        }
        $param = Request::instance()->post();
        if (empty($param)) {
            $this->error('参数错误');
        }
        $data = [];
        foreach ($param as $item) {
            $data[] = [
                'Yb_工单编号'   =>  $item['gdbh'],
                'Yb_印件号'     =>  $item['yjno'],
                'Yb_存货编码'   =>  $item['code'],
                'Yb_供方批号'   =>  $item['batch'],
                'Yb_领用日期'   =>  date('Y-m-d',time()),
                'Yb_领用机台'   =>  $item['machine'],
                'Sys_id'   =>   $item['sys_id'],
                'Sys_rq'   =>   date('Y-m-d H:i:s',time()),
            ];
        }
        $sql = db('工单_印版领用记录')
            ->fetchSql(true)
            ->insertAll($data);
        $res = db('')->query($sql);
        if ($res === false){
            $this->error('领用失败');
        }else{
            $this->success('领用成功');
        }
    }

    /**
     * 印版工单领用
     * @return void
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function PrintDetailReceive()
    {
        if (!$this->request->isGet()) {
            $this->error('请求错误');
        }
        $param = $this->request->param();
        if (empty($param) || !isset($param['code'])) {
            $this->error('参数错误');
        }
        $list = db('工单_印版领用记录')
            ->alias('a')
            ->field('a.Yb_工单编号 as 工单编号,a.Yb_印件号 as 印件号,a.Yb_领用日期 as 领用日期,a.Yb_退还日期 as 退还日期,a.Yb_领用机台 as 领用机台,
                a.Yb_印数 as 印数,a.Sys_id as 创建用户,a.Sys_rq as 创建时间,a.UniqID,b.成品代号,rtrim(b.成品名称) as 成品名称')
            ->join('工单_基本资料 b', 'a.Yb_工单编号 = b.Gd_gdbh','left')
            ->where('a.Yb_存货编码',$param['code'])
            ->where('a.Yb_供方批号',$param['batch'])
            ->order('a.UniqID desc')
            ->group('a.Yb_工单编号,a.Yb_印件号,a.UniqID')
            ->select();
        if (empty($list)){
            $this->error('未找到领用记录');
        }else{
            $this->success('成功', $list);
        }
    }


    /**
     * 印版领用记录删除
     * @return void
     * @throws \think\Exception
     * @throws \think\exception\PDOException
     */
    public function PrintReceiveDelete()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }
        $param = $this->request->param();
        if (empty($param) || !isset($param['id'])) {
            $this->error('参数错误');
        }
        $id = explode(',', $param['id']);
        if (empty($id)) {
            $this->error('请先选中要删除数据');
        }
        $res = db('工单_印版领用记录')
            ->where('UniqID','in',$id)
            ->delete();
        if ($res === false){
            $this->error('删除失败');
        }else{
            $this->success('删除成功');
        }
    }


    //印版库管理菜单下方客户产品印版管理
    public function PrintDetailTab()
    {
        if($this->request->isGet() === false){
            $this->error('请求错误');
        }
        $list = db('产品_印版资料')
            ->alias('a')
            ->field('
                rtrim(a.YB_Cpdh) as 产品代号,
                rtrim(b.产品名称) as 产品名称,
                rtrim(b.客户编号) as 客户编号,
                rtrim(b.客户名称) as 客户名称,
                rtrim(a.存货编码) as 存货编码,
                rtrim(c.名称) as 名称
            ')
            ->join('产品_基本资料 b', 'a.YB_Cpdh = b.产品编号')
            ->join('物料_存货结构 c',
                "(CASE 
                    WHEN a.存货编码 REGEXP '[A-Za-z]' 
                    THEN LEFT(rtrim(a.存货编码), 5) 
                    ELSE LEFT(rtrim(a.存货编码), 4) 
                END) = rtrim(c.编号)"
            )
            ->group('a.YB_Cpdh,名称')
            ->order('a.YB_Cpdh,a.存货编码')
            ->select();
        if (empty($list)){
            $this->error('未找到客户数据');
        }
        foreach ($list as $item) {
            $customerKey = $item['客户编号'] . '【' . $item['客户名称'] . '】';
            $productKey = $item['产品代号'] . '【' . $item['产品名称'] . '】';
            if (!isset($data[$customerKey])) {
                $data[$customerKey] = [];
            }
            if (!isset($data[$customerKey][$productKey])) {
                $data[$customerKey][$productKey] = [];
            }
            if (!in_array($item['名称'], $data[$customerKey][$productKey])) {
                $data[$customerKey][$productKey][] = $item['名称'];
            }
        }
        $this->success('成功', $data);
    }


    //客户印版库右侧列表
    public function PrintDetail()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }
        $param = $this->request->param();
        if (empty($param) || !isset($param['code']) || !isset($param['category'])) {
            $this->error('请求错误');
        }

        //获取印版库分类编号
        $categoryNumber = db('物料_存货');
        $where =  [
            'a.YB_Cpdh' => $param['code'],
            'b.YB_Cpdh' => $param['category'],
        ];
//        $list = db('<UNK>_<UNK>')
    }


    /**
     * 印版删除
     * @return void
     * @throws \think\Exception
     * @throws \think\exception\PDOException
     */
    public function PrintDetaiDel()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }
        $param = $this->request->param();
        if (empty($param) || !isset($param['UniqID'])) {
            $this->error('参数错误');
        }
        $id = explode(',', $param['UniqID']);
        $res = db('产品_印版库')
            ->where('UniqID','in',$id)
            ->delete();
        if ($res === false){
            $this->error('删除失败');
        }else{
            $this->success('删除成功');
        }
    }


    /**
     * 报废印版左侧菜单
     * @return void
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function getPrintDateList()
    {
        if (!$this->request->isGet()) {
            $this->error('请求错误');
        }

        $result = db('产品_印版库')
            ->field([
                'YEAR(报废日期) as year',
                'DATE_FORMAT(报废日期, "%Y-%m") as month',
                'DATE_FORMAT(报废日期, "%Y-%m-%d") as day'
            ])
            ->where('报废日期', '<>', '1900-01-01 00:00:00')
            ->group('day')
            ->order('day desc')
            ->select();

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

        // 构建层次结构
        $data = [];
        foreach ($result as $item) {
            if (empty($item['year']) || empty($item['month']) || empty($item['day'])) {
                continue;
            }

            $year = (string)$item['year'];
            $month = $item['month'];
            $day = $item['day'];

            if (!isset($data[$year])) {
                $data[$year] = [];
            }
            if (!isset($data[$year][$month])) {
                $data[$year][$month] = [];
            }

            $data[$year][$month][] = $day;
        }

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


    /**
     * 获取报废印版资料
     * @return void
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function getPrintdata()
    {
        if (!$this->request->isGet()) {
            $this->error('请求错误');
        }
        $param = $this->request->param();
        if (empty($param)) {
            $this->error('参数错误');
        }
        $list = db('产品_印版库')
            ->where('报废日期', 'like', $param['date'].'%')
            ->group('存货编码')
            ->select();
        if (empty($list)) {
            $this->error('未获取数据');
        }else{
            $this->success('成功', $list);
        }
    }
}