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

<?php

namespace app\api\controller;


use app\common\controller\Api;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Pdf\Tcpdf;
use think\Config;
use think\Db;
use think\Exception;
use think\Log;
use think\Request;
use PhpOffice\PhpSpreadsheet\IOFactory;
use function EasyWeChat\Kernel\Support\rsa_public_encrypt;
use function fast\e;


/**
 * 工单资料管理
 */
class WorkOrder extends Api
{
    protected $noNeedLogin = ['*'];
    protected $noNeedRight = ['*'];
    /**
     * 接口访问配置
     * 每个模块包含：
     * - api_key：API 调用密钥（Token）
     * - api_url：对应功能的服务端地址
     */
    protected $config = [
        //文生文-gtp-4
        'txttotxtgtp' => [
            'api_key' => 'sk-fxlawqVtbbQbNW0wInR3E4wsLo5JHozDC2XOHzMa711su6ss',
            'api_url' => 'https://chatapi.onechats.top/v1/chat/completions'
        ]
    ];

    public function _initialize()
    {

        if (isset($_SERVER['HTTP_ORIGIN'])) {
            header('Access-Control-Expose-Headers: __token__');//跨域让客户端获取到
        }
        //跨域检测
        check_cors_request();

        if (!isset($_COOKIE['PHPSESSID'])) {
            Config::set('session.id', $this->request->server("HTTP_SID"));
        }
        parent::_initialize();
    }

    /**
     * 订单资料管理左侧菜单
     */
    public function DataList()
    {
        $where['j.Mod_rq'] = null;
        $allCustomers = \db('erp_客户供应商')->alias('e')
            ->field('e.编号 as 客户编号')
            ->select();
        $customerData = [];
        foreach ($allCustomers as $customer) {
            $customerID = $customer['客户编号'];
            $customerData[$customerID] = ['计划中' => 0, '生产中' => 0, '已完工' => 0];
        }
        $data = \db('erp_客户供应商')->alias('e')
            ->join('工单_基本资料 j', 'e.编号 = j.客户编号', 'LEFT')
            ->field('e.编号 as 客户编号, j.订单编号, j.gd_statu')
            ->where($where)
            ->select();

        foreach ($data as $row) {
            $customerID = $row['客户编号'];
            $status = $row['gd_statu'];

            if ($status == '1-计划中') {
                $customerData[$customerID]['计划中']++;
            } elseif ($status == '2-生产中') {
                $customerData[$customerID]['生产中']++;
            } elseif ($status == '已完工') {
                $customerData[$customerID]['已完工']++;
            }
        }
        ksort($customerData);
        $output = [];
        foreach ($customerData as $customerID => $statusCounts) {
            $statusString = [];
            if ($statusCounts['计划中'] > 0) {
                $statusString[] = "计划中:{$statusCounts['计划中']}";
            }
            if ($statusCounts['生产中'] > 0) {
                $statusString[] = "生产中:{$statusCounts['生产中']}";
            }
            if ($statusCounts['已完工'] > 0) {
                $statusString[] = "已完工:{$statusCounts['已完工']}";
            }
            $output[] = "{$customerID}【" . implode(' ', $statusString) . "】";
        }
        $this->success('成功', $output);
    }

    /**
     * 订单发货管理左侧菜单
     */
    public function Send_Out_Goods()
    {
        $data = \db('工单_发货基本资料')->alias('f')
            ->join('工单_基本资料 j', 'f.订单编号 = j.订单编号', 'LEFT')
            ->field('DISTINCT j.客户编号')
            ->whereNotNull('j.客户编号')
            ->select();
        $customerCodes = [];
        foreach ($data as $row) {
            if (!empty($row['客户编号'])) {
                $customerCodes[] = $row['客户编号'];
            }
        }
        return json([
            'code' => 0,
            'msg' => '成功',
            'time' => time(),
            'data' => $customerCodes
        ]);
    }

    /**
     * 工单基本资料列表
     * @ApiMethod (GET)
     * @param string $limit 查询长度
     * @param string $Gd_khdh 客户代号
     * @param string $startTime 接单日期开始时间
     * @param string $endTime  接单日期结束时间
     * @return \think\response\Json
     * @throws \think\exception\DbException
     */
    public function WorkOrderList()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }

        $search = input('search');
        $page = input('page');
        $limit = input('limit');
        $param = $this->request->param();

        $where = [];
        if (!empty($search)) {
            $where['订单编号|生产款号|客户编号|款式|审核|Sys_id'] = ['like', '%' . $search . '%'];
        }
        $where['Mod_rq'] = null;

        $list = \db('工单_基本资料')
            ->where($where)
            ->order('订单编号 desc, Gd_statu desc, Sys_rq desc')
            ->limit(($page - 1) * $limit, $limit)
            ->select();

        $count = \db('工单_基本资料')
            ->where($where)
            ->order('订单编号 desc, Gd_statu desc, Sys_rq desc')
            ->select();

        // 提取所有订单编号
        $orderIds = array_column($list, '订单编号');

        // 查询所有在“工单_相关附件”表中存在的订单编号
        $relatedOrders = \db('工单_相关附件')
            ->whereIn('关联编号', $orderIds)
            ->whereIn('附件备注', '技术附件')
            ->whereNull('mod_rq')
            ->column('关联编号');

        // 遍历数据，判断每个订单编号是否在相关附件表中
        foreach ($list as &$value) {
            if (in_array($value['订单编号'], $relatedOrders)) {
                $value['status'] = '';  // 有相关附件，status为空
            } else {
                $value['status'] = '*'; // 没有相关附件，标记为新订单
            }
            if ($value['落货日期']) {
                $value['落货日期'] = date('Y-m-d', strtotime($value['落货日期']));
            }
            // 格式化接单日期，去掉时间部分
            if ($value['接单日期']) {
                $value['接单日期'] = date('Y-m-d', strtotime($value['接单日期']));
            }
        }


        $this->success('成功', ['data' => $list, 'total' => count($count)]);
    }

    /**
     * 月度客户订单汇总
     */
    public function ProductInformation()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }

        $data = \db('工单_基本资料')->alias('j')
            ->field('j.客户编号,
            REPLACE(DATE_FORMAT(j.Sys_rq, "%Y-%m"), "-", "") as 年月,
            SUM(CASE WHEN j.gd_statu LIKE "%已完工%" THEN 1 ELSE 0 END) as 已完工数量,
            SUM(CASE WHEN j.gd_statu LIKE "%1-计划中%" THEN 1 ELSE 0 END) as 计划中数量
        ')
            ->where('j.Mod_rq', null)
            ->whereNotNull('j.客户编号')
            ->whereNotNull('j.gd_statu')
            ->group('j.客户编号, 年月')
            ->order('j.客户编号 asc')
            ->select();

        // 格式化数据
        $result = [];
        foreach ($data as $item) {
            $result[$item['年月']][] = $item['客户编号'] . '【已完工' . $item['已完工数量'] . '，计划中' . $item['计划中数量'] . '】';
        }

        $this->success('请求成功', ['data' => $result]);
    }

    /**
     * U8工单资料删除
     * @param string $workOrder  工单编号
     * @return void
     * @throws \think\Exception
     * @throws \think\exception\PDOException
     */


    public function orderDataDel()
    {
        if($this->request->isGet() === false){
            $this->error('请求错误');
        }
        $workOrder = input('Uniqid');
        if (empty($workOrder)){
            $this->error('参数错误');
        }
        $order = \db('工单_基本资料')->where('UniqId',$workOrder)->find();
        \db()->startTrans();
        try {
            \db('工单_印件资料')->where('订单编号',$order['订单编号'])->update(['Mod_rq'=>date('Y-m-d H:i:s')]);
            \db('工单_工艺资料')->where('订单编号',$order['订单编号'])->update(['Mod_rq'=>date('Y-m-d H:i:s')]);
            $res = \db('工单_基本资料')->where('UniqId',$workOrder)->update(['Mod_rq'=>date('Y-m-d H:i:s')]);
            \db()->commit();
        } catch (\Exception $e){
            \db()->rollback();
        }
        if ($res !== false){
            $this->success('成功');
        }else{
            $this->error('失败');
        }
    }



    /**
     * 新增工单->添加工单
     * @ApiMethod （POST）
     * @param
     */

    public function WorkOrderAdd()
    {
        if (Request::instance()->isPost() === false){
            $this->error('请求错误');
        }
        $param = Request::instance()->post();
        if (empty($param)){
            $this->error('参数错误');
        }
        $param['Sys_rq'] = date('Y-m-d H:i:s');
        $param['gd_statu'] = '1-计划中';

        $prefix = substr($param['订单编号'], 0, 2);
        $maxOrder = \db('工单_基本资料')
            ->where('订单编号', 'like', "{$prefix}%")
            ->order('订单编号', 'desc')
            ->limit(1)
            ->value('订单编号');
        if ($maxOrder) {
            $numericPart = substr($maxOrder, 2);
            $newNumericPart = str_pad((int)$numericPart + 1, strlen($numericPart), '0', STR_PAD_LEFT);
            $param['订单编号'] = $prefix . $newNumericPart;
        } else {
            $param['订单编号'] = $param['订单编号'];
        }
        if ($maxOrder) {
            $numericPart = substr($maxOrder, 2);
            $newNumericPart = str_pad((int)$numericPart + 1, strlen($numericPart), '0', STR_PAD_LEFT);
            $param['订单编号'] = $prefix . $newNumericPart;
        } else {
            $param['订单编号'] = $param['订单编号'];
        }
        $data = $param;
        unset($data['关联订单']);
        unset($data['关联面料ID']);
        //新增订单插入至工单基本资料
        $sql = \db('工单_基本资料')->fetchSql(true)->insert($data);
        \db()->query($sql);
        if (!empty($param['关联订单']) && !empty($param['关联面料ID'])){
            //关联订单编号
            $OrderId = $param['关联订单'];
            $OrderNumber = explode(',',$OrderId);
            //关联面料ID
            $RelevanceId = $param['关联面料ID'];
            //查询订单BOM资料，面料资料
            $RelevanceList = explode(',',$RelevanceId);
            $FabricList = $BomList = $MaterielList = [];
            foreach ($RelevanceList as $item){
                //工单面料信息
                $Fabric = \db('工单_面料资料')
                    ->where('UNIQID',$item)
                    ->where('Mod_rq',null)
                    ->field('BOM_工单编号,BOM_颜色,BOM_物料编码,BOM_物料名称,BOM_投料单位,BOM_计划门幅,BOM_定额门幅')
                    ->find();
                $OrderData = $Fabric;
                $OrderData['BOM_工单编号'] = $param['订单编号'];
                $OrderData['Sys_ID'] = $param['Sys_id'];
                $OrderData['Sys_rq'] = date('Y-m-d H:i:s',time());
                $OrderData['BOM_desc'] = $param['要求'];
                array_push($FabricList,$OrderData);
                //关联订单
                $AssociatedNumber = \db('工单关联表')
                    ->where('订单编号',$Fabric['BOM_工单编号'])
                    ->where('颜色',$Fabric['BOM_颜色'])
                    ->where('物料编号',$Fabric['BOM_物料编码'])
                    ->value('关联编号');
                $materiel = [
                    '关联编号' => $AssociatedNumber,
                    '订单编号' => $Fabric['BOM_工单编号'],
                    '生产款号' => $param['生产款号'],
                    '颜色' => $Fabric['BOM_颜色'],
                    '物料编号' => $Fabric['BOM_物料编码'],
                    '物料名称' => $Fabric['BOM_物料名称'],
                    '备注' => $param['要求'],
                    'Sys_id' => $param['Sys_id'],
                    'Sys_rq' => date('Y-m-d H:i:s',time()),
                ];
                array_push($MaterielList,$materiel);
            }
            foreach ($MaterielList as $index => $item){
                $MaterielList[$index]['订单编号'] = $param['订单编号'];
                //工单BOM信息
                $Bom = \db('工单_bom资料')
                    ->where('BOM_工单编号',$item['订单编号'])
                    ->where('BOM_物料名称',$item['物料名称'])
                    ->where('Mod_rq',null)
                    ->find();
                unset($Bom['UNIQID']);
                unset($Bom['Mod_rq']);
                $Bom['Sys_ID'] = $param['Sys_id'];
                $Bom['Sys_rq'] = date('Y-m-d H:i:s',time());
                if (empty($BomList)){
                    array_push($BomList,$Bom);
                }else{
                    foreach ($BomList as $value){
                        if ($value['BOM_工单编号'] !== $Bom['BOM_工单编号'] && $value['BOM_物料名称'] !== $Bom['BOM_物料名称']){
                            array_push($BomList,$Bom);
                        }
                    }
                }
            }
            foreach ($BomList as $key => $value){
                $BomList[$key]['BOM_工单编号'] = $param['订单编号'];
            }

            //插入数据
            Db::startTrans();
            try {
                //BOM表数据插入
                $BomSql = \db('工单_bom资料')->fetchSql(true)->insertAll($BomList);
                $BomRes = \db()->query($BomSql);
                //面料表数据插入
                $FabricSql = \db('工单_面料资料')->fetchSql(true)->insertAll($FabricList);
                $FabricRes = \db()->query($FabricSql);
                //工单关联表数据插入
                $materielSql = \db('工单关联表')->fetchSql(true)->insertAll($MaterielList);
                $materielRes = \db()->query($materielSql);

                //提交数据
                Db::commit();
            }catch (\Exception $e){
                //回滚事务
                Db::rollback();
            }
        }else{
            //判断新增时面料是否存在,如果有调用gtp自动生成BOM资料
            if (!empty($param['面料'])) {
                // 只有面料不为空时，才调用 GdGtpAiOrder 方法
                $this->GdGtpAiOrder($param['订单编号']);
            }
        }
        $this->success('成功');
    }

    /**
     * 新增印件资料->印件资料添加
     * @return void
     * @throws \think\db\exception\BindParamException
     * @throws \think\exception\PDOException
     */
    public function PrintDetailAdd()
    {
        if (Request::instance()->isPost() === false){
            $this->error('请求错误');
        }
        $param = Request::instance()->post();
        if (empty($param)){
            $this->error('参数错误');
        }
        $param['Sys_rq'] = date('Y-m-d H:i:s',time());
        $process = [
            ['1','仓库出库'],['2','裁剪'],['3','车缝'],['4','后道收样'],['5','大烫'],['6','总检'],['7','包装']
        ];
        $processDetail = [];
        foreach ($process as $key=>$value){
            $total = null;
            if ($key !== 0){
                $total = $param['zdtotal'];
            }
            $processDetail[$key] = [
                '订单编号' => $param['订单编号'],
                '子订单编号' => $param['子订单编号'],
                '款号' => $param['款号'],
                '颜色' => $param['颜色'],
                '颜色备注' => $param['颜色备注'],
                '工序编号' => $value[0],
                '工序名称' => $value[1],
                '计划产量' => $total,
                'Sys_id' => $param['Sys_id'],
                'Sys_rq' => $param['Sys_rq']
            ];
        }
        $color = \db('工单_面料资料')
            ->where('Bom_工单编号',$param['订单编号'])
            ->where('BOM_颜色',$param['颜色备注'])
            ->where('Mod_rq',null)
            ->select();
        $colorList = [];
        if (empty($color)){
            $BomList = \db('工单_bom资料')
                ->where('BOM_工单编号',$param['订单编号'])
                ->where('Mod_rq',null)
                ->select();
            foreach ($BomList as $key=>$value){
                $colorList[$key] = [
                    'BOM_工单编号' => $param['订单编号'],
                    'BOM_颜色' => $param['颜色备注'],
                    'BOM_物料编码' => $param['款号'].'-'.$param['颜色备注'].($key+1),
                    'BOM_物料名称' => $value['BOM_物料名称'],
                    'U8UID' => $value['UNIQID'],
                    'BOM_投料单位' => '',
                    'Sys_ID' => $param['Sys_id'],
                    'Sys_rq' => date('Y-m-d H:i:s',time()),
                    'BOM_desc' => $value['BOM_desc']
                ];
            }
        }
        //获取最新的关联编号
        $AssociatedNumber = \db('工单关联表')->order('关联编号 desc')->column('关联编号');
        if (empty($AssociatedNumber)){
            $number = 0;
        }else{
            $number = (int)substr($AssociatedNumber[0],3);
        }
        $MaterielList = [];
        //插入工单关联数据表
        foreach ($colorList as $key=>$value){
            $MaterielList[$key] = [
                '关联编号' => 'GDGL'.($number + $key + 1),
                '订单编号' => $value['BOM_工单编号'],
                '生产款号' => $param['款号'],
                '颜色' => $param['颜色备注'],
                '物料编号' => $value['BOM_物料编码'],
                '物料名称' => $value['BOM_物料名称'],
                '备注' => $value['BOM_desc'],
                'Sys_id' => $param['Sys_id'],
                'Sys_rq' => date('Y-m-d H:i:s',time())
            ];
        }
        //开启事务
        db()->startTrans();
        try{
            //工单颜色录入
            $priSql = \db('工单_印件资料')->fetchSql(true)->insert($param);
            $priRes = \db()->query($priSql);
            //工单工艺录入
            $proSql = \db('工单_工艺资料')->fetchSql(true)->insertAll($processDetail);
            $proRes = \db()->query($proSql);
            if (!empty($colorList)){
                //工单面料录入
                $fabricSql = \db('工单_面料资料')->fetchSql(true)->insertAll($colorList);
                $fabricRes = \db()->query($fabricSql);
            }
            if (!empty($MaterielList)){
                //工单关联表录入
                $meterieSql = \db('工单关联表')->fetchSql(true)->insertAll($MaterielList);
                $meterieRes = \db()->query($meterieSql);
            }

            // 提交事务
            db()->commit();
        } catch (\Exception $e) {
            // 回滚事务
            db()->rollback();
            $this->error($e->getMessage());
        }

        if ($priRes !== false && $proRes !== false){
            $this->success('成功');
        }else{
            $this->error('失败');
        }
    }


    /**
     * 子订单列表
     * @return null
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function PrintListData()
    {
        // 检查请求方式
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }

        $param = $this->request->param();

        // 检查参数是否存在
        if (isset($param) === false) {
            $this->error('参数错误');
        }

        // 查询型号
        $where['Mod_rq'] = null;
        $xhdata = \db('工单_印件资料')
            ->where('订单编号', $param['order'])
            ->where($where)
            ->field('cm1,cm2,cm3,cm4,cm5,cm6,cm7,cm8,cm9,cm10,cm11,cm12,cm13,cm14')
            ->select();

        $arr = [];

        foreach ($xhdata as $key => $value) {
            for ($i = 1; $i <= 14; $i++) {
                if ($value['cm' . $i] !== '' && $value['cm' . $i] !== null) {
                    array_push($arr, $value['cm' . $i]);
                }
            }
        }

        $arr = array_unique($arr);
        sort($arr);

        // 查询详细列表
        $list = \db('工单_印件资料')
            ->where('订单编号', $param['order'])
            ->where($where)
            ->field('订单编号,子订单编号,款号,颜色,船样,zdtotal,Sys_id,Sys_rq,ck_rq,sc_rq,updatatime as 更新时间,Uniqid,颜色备注,
                cm1,cm2,cm3,cm4,cm5,cm6,cm7,cm8,cm9,cm10,cm11,cm12,cm13,cm14,
                cmsl1,cmsl2,cmsl3,cmsl4,cmsl5,cmsl6,cmsl7,cmsl8,cmsl9,cmsl10,cmsl11,cmsl12,cmsl13,cmsl14')
            ->select();

        // 遍历列表并处理cm和cmsl字段
        foreach ($list as $key => $value) {
            for ($i = 1; $i <= 14; $i++) {
                if ($value['cm' . $i] !== '') {
                    // 如果 cmsl 的值为 0，则设置为空字符串
                    $list[$key][$value['cm' . $i]] = ($value['cmsl' . $i] === 0) ? '' : $value['cmsl' . $i];
                }
                // 删除原有的 cm 和 cmsl 字段
//                unset($list[$key]['cm' . $i], $list[$key]['cmsl' . $i]);
            }
        }
        // 自定义型号排序
        $customOrder = array('XXS', 'XS', 'S', 'M', 'L', 'XL','2XL','XXL','3XL', 'XXXL','4XL','XXXXL','5XL','XXXXL');
        usort($arr, function ($a, $b) use ($customOrder) {
            $posA = array_search($a, $customOrder);
            $posB = array_search($b, $customOrder);
            return $posA - $posB;
        });
        $data['型号'] = $arr;
        $data['列表'] = $list;
        $this->success('成功', $data);
    }

    /**
     * 工单资料管理->印件资料删除
     * @return void
     * @throws \think\Exception
     * @throws \think\exception\PDOException
     */
    public function PrintDetailDel()
    {
        if ($this->request->isGet() === false){
            $this->error('请求错误');
        }
        $param = $this->request->param();
        if (isset($param['UniqId']) === false){
            $this->error('参数错误');
        }
        $printId = explode(',',$param['UniqId']);
        $i = 0;
        foreach ($printId as $value){
            $res = \db('工单_印件资料')
                ->where('Uniqid',$value)
                ->update(['Mod_rq'=>date('Y-m-d H:i:s',time())]);
            if ($res === false){
                $i++;
            }
        }
        if ($i === 0){
            $this->success('删除成功');
        }else{
            $this->error('删除失败');
        }

    }

    /**
     * 月度车间报工汇总->报工删除记录
     */
    public function ProcessDetailDel()
    {
        if ($this->request->isGet() === false){
            $this->error('请求错误');
        }
        $param = $this->request->param();
        if (empty($param)) {
            $this->error('参数错误');
        }

        $where['a.mod_rq'] = ['neq', ''];
        $list = \db('设备_产量计酬')->alias('a')
            ->join('工单_印件资料 b', 'b.订单编号 = a.订单编号 AND a.子订单编号 = a.子订单编号')
            ->join('工单_基本资料 j', 'b.订单编号 = j.订单编号', 'LEFT')
            ->field('
                b.订单编号, b.子订单编号, b.款号, b.颜色, b.船样, a.尺码, b.zdtotal as 制单数,b.颜色备注,
                a.数量, a.sys_rq as 上报时间,a.UniqId,a.mod_rq,a.delsys_id,a.sczl_bh,
                j.客户编号,j.生产款号,j.款式
                ')
            ->where($where)
            ->order('a.mod_rq desc')
            ->limit($param['page'],$param['limit'])
            ->group('a.UniqId')
            ->select();

        $count = \db('设备_产量计酬')->alias('a')
            ->join('工单_印件资料 b', 'b.订单编号 = a.订单编号 AND a.子订单编号 = a.子订单编号')
            ->join('工单_基本资料 j', 'b.订单编号 = j.订单编号', 'LEFT')
            ->field('
                b.订单编号, b.子订单编号, b.款号, b.颜色, b.船样, a.尺码, b.zdtotal as 制单数,b.颜色备注,
                a.数量, a.sys_rq as 上报时间,a.UniqId,a.mod_rq,a.delsys_id,a.sczl_bh,
                j.客户编号,j.生产款号,j.款式
                ')
            ->where($where)
            ->order('a.mod_rq desc')
            ->group('a.UniqId')
            ->select();

        // 提取所有的尺码，并去重
        $sizeList = array_values(array_unique(array_column($list, '尺码')));
        // 动态将尺码的数量添加到每个订单中，并替换已完成字段
        foreach ($list as &$item) {
            $size = $item['尺码'];
            $item[$size] = $item['数量']; // 动态添加尺码字段，值为数量
//            unset($item['数量']); // 移除原来的已完成字段
        }
        $data['total'] = count($count);
        $data['table'] = $list;
        $this->success('成功',$data);
    }

    /**
     * 订单附件上传新增
     * 1.前端进行上传xlsx文件表格
     * 2.接口接受数据文件进行保存xlsx文件以及pdf转换
     * 3.前端进行预览pdf图片显示文件中的内容再页面上
     */
    public function gdAnnexAdd() {
        ini_set('display_errors', 'On');
        ini_set('error_reporting', E_ALL);

        if (!$this->request->isPost()) {
            $this->error('请求方式错误');
        }

        // 获取请求参数
        $req = $this->request->param();

        $relateId = $req['关联编号'];
        if(empty($req['关联编号'])){
            $this->error('网络异常,请重新上传');
        }
        if($req['关联编号'] == 'undefined'){
            $this->error('网络异常,请重新上传');
        }

        $attachmentContent = $req['附件内容'];
        $attachmentType = $req['附件类型'];
        $prefixDir = ROOT_PATH . '/public/';
        $uploadDir = ''.'uploads/' . date('Ymd') . '/' . $relateId;

//        //技术附件上传代表订单开始生产
//        if($req['附件备注'] == '技术附件'){
//            DB::name('工单_基本资料')
//                ->where('订单编号', $relateId)
//                ->update(['gd_statu' => '2-生产中']);
//        }

        // 检查并创建目录
        if (!is_dir($prefixDir . $uploadDir)) {
            mkdir($prefixDir . $uploadDir, 0777, true);
        }

        // 处理 Base64 附件内容
        if (strpos($attachmentContent, 'base64,') !== false) {
            $attachmentContent = explode('base64,', $attachmentContent)[1];
        }
        $fileContent = base64_decode($attachmentContent);
        $filename = time();

        // 初始化文件路径变量
        $xlsxFilePath = '';
        $pdfFilePath = '';

        if ($attachmentType === 'pdf') {
            // 保存 PDF 文件
            $pdfFilePath = $uploadDir . '/' . $filename . '.pdf';
            file_put_contents($prefixDir . $pdfFilePath, $fileContent);
        } elseif ($attachmentType === 'xlsx') {
            // 保存 Excel 文件
            $xlsxFilePath = $uploadDir . '/' . $filename . '.xlsx';
            file_put_contents($prefixDir . $xlsxFilePath, $fileContent);

            // 转换为 PDF 文件
            $pdfFilePath = $uploadDir . '/' . $filename . '.pdf';
            $cmd = sprintf(
                'libreoffice --headless --convert-to pdf --outdir %s %s',
                escapeshellarg($prefixDir . $uploadDir),
                escapeshellarg($prefixDir . $xlsxFilePath)
            );
            exec($cmd, $out, $retval);

            if ($retval !== 0) {
                $this->error('Excel 转 PDF 失败');
            }
        } else {
            $this->error('不支持的附件类型');
        }

        // 获取最新的记录，按 sys_rq 降序排列
        $latestItems = \db('工单_相关附件')
            ->where('关联编号', $relateId)  // 根据关联编号筛选
            ->where('附件备注', $req['附件备注'])  // 根据附件备注筛选
            ->whereNull('mod_rq')
            ->order('sys_rq desc')  // 按照 sys_rq 降序排列
            ->group('关联编号')  // 按关联编号分组，获取每个订单的最新记录
            ->find();

        // 如果查询为空，设置默认的版本号为 'v1.0'
        if (!$latestItems) {
            $currentVersion = 'v1.0';
        } else {
            $currentVersion = $latestItems['version'];  // 当前记录的 version
        }

        // 解析当前版本号
        if (preg_match('/v(\d+)\.(\d+)/', $currentVersion, $matches)) {
            $majorVersion = (int)$matches[1];  // 主版本号
            $minorVersion = (int)$matches[2];  // 次版本号

            // 递增次版本号，若次版本号为 9，则主版本号加 1，次版本号置为 0
            if ($minorVersion < 9) {
                $minorVersion++;
            } else {
                $majorVersion++;
                $minorVersion = 0;
            }

            // 生成新的版本号
            $newVersion = 'v' . $majorVersion . '.' . $minorVersion;
        } else {
            // 如果没有匹配到版本号（例如没有 `v`），可以默认从 `v1.0` 开始
            $newVersion = 'v1.0';
        }

        if ($req['附件备注'] == '发货单附件'){
            $data = [
                'rand_number' => date('YmdHis'),
                'version' => $newVersion,
                '关联编号' => $relateId,
                'sys_id' => $req['sys_id'],
                '附件备注' => $req['附件备注'],
                '附件类型' => $attachmentType,
                'sys_rq' => date('Y-m-d H:i:s'),
                'url' => $xlsxFilePath,  // 保存 xlsx 文件路径（如果存在）
                'pdf' => $pdfFilePath  // 保存 pdf 文件路径
            ];
        }else{
            $data = [
                'version' => $newVersion,
                '关联编号' => $relateId,
                'sys_id' => $req['sys_id'],
                '附件备注' => $req['附件备注'],
                '附件类型' => $attachmentType,
                'sys_rq' => date('Y-m-d H:i:s'),
                'url' => $xlsxFilePath,  // 保存 xlsx 文件路径（如果存在）
                'pdf' => $pdfFilePath  // 保存 pdf 文件路径
            ];
        }

        // 数据库事务处理
        db()->startTrans();
        try {
            // 插入数据
            $sql = db('工单_相关附件')->fetchSql(true)->insert($data);
            $result = db()->query($sql);
            db()->commit();
        } catch (\Exception $e) {
            // 回滚事务
            db()->rollback();
            $this->error($e->getMessage());
        }

        if ($result === false) {
            $this->error('失败');
        }

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

    function fixPdfText($text) {
        // 修复数字间的空格丢失问题
        $patterns = [
            '/(\d{3})(\d{3})/' => '$1 $2', // 将6位数字分成3+3
            '/(\d{2})(\d{4})/' => '$1 $2', // 将6位数字分成2+4
        ];

        foreach ($patterns as $pattern => $replacement) {
            $text = preg_replace($pattern, $replacement, $text);
        }

        return $text;
    }

    //识别PDF文件内容
    public function Read_File(){
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }
        $param = $this->request->param();

        //查询相关订单数据信息
        $order_list = Db::name('工单_基本资料')
            ->where('订单编号',$param['order'])
            ->whereNull('Mod_rq')
            ->find();
        if (!$order_list) {
            return json(['code' => 0, 'msg' => '未找到订单数据记录']);
        }

        //查询相关订单子订单数据
        $orderids_list = Db::name('工单_印件资料')
            ->where('订单编号',$param['order'])
            ->whereNull('Mod_rq')
            ->select();
        if (!$orderids_list) {
            return json(['code' => 0, 'msg' => '未找到相关子订单数据记录']);
        }

        //查询PDF文件数据信息
        $where = [];
        $where['UniqId'] = $param['UniqId'];
        $where['附件备注'] = "发货单附件";
        $res = Db::name('工单_相关附件')
            ->whereNull('mod_rq')
            ->where($where)
            ->order('sys_rq desc')
            ->find();

        if (!$res) {
            return json(['code' => 0, 'msg' => '未找到相关文件记录']);
        }
        $pdfPath = $res['pdf'];

        try {
            if (!class_exists('Smalot\PdfParser\Parser')) {
                return json(['code' => 0, 'msg' => '请先安装PDF解析库: composer require smalot/pdfparser']);
            }

//            $pdfUrl = $param['url']. $pdfPath;
//            $pdfContent = file_get_contents($pdfUrl);
//            if ($pdfContent === false) {
//                throw new Exception("无法下载PDF文件: " . $pdfUrl);
//            }
//            $parser = new \Smalot\PdfParser\Parser();
//            $pdf = $parser->parseContent($pdfContent);
//            $text = $pdf->getText();
//            echo "<pre>";
//            print_r($text);
//            echo "<pre>";die;

            //通过pdf提取内容
            $pdfUrl = $param['url'] .'/'. $pdfPath;
            $pdfContent = file_get_contents($pdfUrl);
            if ($pdfContent === false) {
                throw new Exception("无法下载PDF文件: " . $pdfUrl);
            }
            $parser = new \Smalot\PdfParser\Parser();
            $pdf = $parser->parseContent($pdfContent);
            $text = $pdf->getText();
            $text = $this->fixPdfText($text);


            $apiKey = 'sk-fxlawqVtbbQbNW0wInR3E4wsLo5JHozDC2XOHzMa711su6ss';

            // 构建订单数据字符串
            $order_data_str = "工单_基本资料：\n";
            $order_data_str .= "订单编号：{$order_list['订单编号']}\n";
            $order_data_str .= "生产款号：{$order_list['生产款号']}\n";
            $order_data_str .= "客户编号：{$order_list['客户编号']}\n";
            $order_data_str .= "款式：{$order_list['款式']}\n";
            $order_data_str .= "订单数量：{$order_list['订单数量']}\n";

            // 构建子订单数据字符串
            $suborder_data_str = "工单_印件资料：\n";
            foreach ($orderids_list as $index => $suborder) {
                $suborder_data_str .= "子订单" . ($index + 1) . "：\n";
                $suborder_data_str .= "  子订单编号：{$suborder['子订单编号']}\n";
                $suborder_data_str .= "  款号：{$suborder['款号']}\n";
                $suborder_data_str .= "  颜色：{$suborder['颜色']}\n";
                $suborder_data_str .= "  颜色备注：{$suborder['颜色备注']}\n";
                $suborder_data_str .= "  子订单数量：{$suborder['zdtotal']}\n";

                // 添加尺码信息
                for ($i = 1; $i <= 14; $i++) {
                    $cm_field = "cm{$i}";
                    $cmsl_field = "cmsl{$i}";
                    if (!empty($suborder[$cm_field]) && !empty($suborder[$cmsl_field])) {
                        $suborder_data_str .= "  cm{$i}:{$suborder[$cm_field]}码, cmsl{$i}:{$suborder[$cmsl_field]}件\n";
                    }
                }
                $suborder_data_str .= "\n";
            }

            $messages = [
                [
                    'role' => 'user',
                    'content' =>
                        "需求说明：
                从发货单中提取所有颜色和尺码的发货数量以及发货数量汇总信息，确保不遗漏任何一行数据。
                特别注意：
                - 要提取全部数据，包括零头箱和单独行
                - 颜色代码，尺码
                - 发货数量按实际数量统计
                - 发货箱数按实际箱数统计
                - 每箱件数按标准箱规计算
                
                数据格式要求，不需要其他任何解释：
                [
                    {'颜色': '01', '尺码': 'M', '发货数量': '400', '发货箱数': '16', '每箱件数': '25'},
                    {'颜色': '01', '尺码': 'L', '发货数量': '375', '发货箱数': '15', '每箱件数': '25'},
                    {'颜色': '02', '尺码': 'M', '发货数量': '425', '发货箱数': '17', '每箱件数': '25'},
                    {'颜色': '02', '尺码': 'L', '发货数量': '425', '发货箱数': '17', '每箱件数': '25'},
                    {'颜色': '02', '尺码': 'L', '发货数量': '17', '发货箱数': '1', '每箱件数': '17'},
                    {'颜色': '01', '尺码': 'M', '发货数量': '20', '发货箱数': '1', '每箱件数': '20'},
                    {'颜色': '01', '尺码': 'L', '发货数量': '4', '发货箱数': '1', '每箱件数': '4'},
                    {'颜色': '02', '尺码': 'M', '发货数量': '15', '发货箱数': '1', '每箱件数': '15'},
                ]
                发货单内容：{$text}"
                ]
            ];
//            $result = [
//                [
//                    "订单编号" => "DC2503217",
//                    "子订单编号" => "DC2503217-0200",
//                    "颜色" => "01",
//                    "尺码" => "M",
//                    "数量" => 450,
//                    "发货数量" => "420",
//                    "箱数" => "16"
//                ],
//                [
//                    "订单编号" => "DC2503217",
//                    "子订单编号" => "DC2503217-0200",
//                    "颜色" => "01",
//                    "尺码" => "L",
//                    "数量" => 450,
//                    "发货数量" => "379",
//                    "箱数" => "15"
//                ],
//                [
//                    "订单编号" => "DC2503217",
//                    "子订单编号" => "DC2503217-0800",
//                    "颜色" => "02",
//                    "尺码" => "M",
//                    "数量" => 450,
//                    "发货数量" => "440",
//                    "箱数" => "17"
//                ],
//                [
//                    "订单编号" => "DC2503217",
//                    "子订单编号" => "DC2503217-0800",
//                    "颜色" => "02",
//                    "尺码" => "L",
//                    "数量" => 450,
//                    "发货数量" => "442",
//                    "箱数" => "17"
//                ]
//            ];
//
//            $this->success('成功', $result);
//die;
            $data = [
                'model' => 'gpt-3.5-turbo',
                'messages' => $messages,
                'max_tokens' => 2048,
                'temperature' => 0.3,
            ];

            $ch = curl_init('https://chatapi.onechats.top/v1/chat/completions');
            curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
            curl_setopt($ch, CURLOPT_HTTPHEADER, [
                'Content-Type: application/json',
                'Authorization: Bearer ' . $apiKey,
            ]);
            curl_setopt($ch, CURLOPT_POST, true);
            curl_setopt($ch, CURLOPT_POSTFIELDS, json_encode($data));
            curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
            curl_setopt($ch, CURLOPT_TIMEOUT, 30);

            $response = curl_exec($ch);
            if (curl_errno($ch)) {
                return json(['code' => 0, 'msg' => '请求失败: ' . curl_error($ch)]);
            }
            curl_close($ch);

            $responseData = json_decode($response, true);
            if (isset($responseData['error'])) {
                return json(['code' => 0, 'msg' => 'API错误: ' . $responseData['error']['message'], 'data' => $responseData]);
            }

            if (isset($responseData['choices'][0]['message']['content'])) {
                $content = $responseData['choices'][0]['message']['content'];

                // 提取JSON部分
                $json_start = strpos($content, '[');
                $json_end = strrpos($content, ']');

                if ($json_start !== false && $json_end !== false) {
                    $json_str = substr($content, $json_start, $json_end - $json_start + 1);

                    // 将单引号替换为双引号
                    $json_str = str_replace("'", '"', $json_str);

                    // 解析JSON
                    $sql_data = json_decode($json_str, true);

                    if (json_last_error() === JSON_ERROR_NONE && is_array($sql_data)) {
                        $result = [];

                        // 将API数据与订单信息结合
                        $has_unmatched = false;
                        foreach ($sql_data as $api_item) {
                            $matched = false;

                            // 在订单列表中查找匹配的颜色和尺码
                            foreach ($orderids_list as $order) {
                                $color_id = isset($order['颜色备注']) ? $order['颜色备注'] : '';

                                // 检查所有尺码
                                for ($i = 1; $i <= 14; $i++) {
                                    $cm_key = 'cm' . $i;
                                    $cmsl_key = 'cmsl' . $i;

                                    if (!empty($order[$cm_key]) &&
                                        $order[$cm_key] == $api_item['尺码'] &&
                                        $color_id == $api_item['颜色']) {

                                        $result[] = [
                                            '订单编号' => isset($order['订单编号']) ? $order['订单编号'] : '',
                                            '子订单编号' => isset($order['子订单编号']) ? $order['子订单编号'] : '',
                                            '颜色' => isset($order['颜色']) ? $order['颜色'] : $api_item['颜色'],
                                            '尺码' => $api_item['尺码'],
                                            '制单数量' => isset($order[$cmsl_key]) ? $order[$cmsl_key] : 0,
                                            '发货数量' => $api_item['发货数量'],
                                            '发货箱数' => $api_item['发货箱数'],
                                            '每箱件数' => $api_item['每箱件数'],
                                            '匹配状态' => '匹配成功'
                                        ];
                                        $matched = true;
                                    }
                                }
                            }

                            if (!$matched) {
                                $has_unmatched = true;
                                break; // 只要有一个未匹配，就直接返回提示
                            }
                        }

                        if ($has_unmatched) {
                            $this->success('颜色未匹配上');
                        } else {
                            $this->success('成功', $result);
                        }
                    } else {
                        return json(['code' => 0, 'msg' => 'JSON解析错误: ' . json_last_error_msg(), 'data' => $json_str]);
                    }
                } else {
                    return json(['code' => 0, 'msg' => '未找到有效的JSON数据', 'data' => $content]);
                }
            } else {
                return json(['code' => 0, 'msg' => 'API响应格式错误', 'data' => $responseData]);
            }

        } catch (Exception $e) {
            return json(['code' => 0, 'msg' => '解析PDF时出错: ' . $e->getMessage()]);
        }
    }

    public function Read_Add(){
        if (Request::instance()->isPost() === false){
            $this->error('请求错误');
        }
        $param = Request::instance()->post();
        foreach ($param as $item) {
            $Data = [
                '订单编号'   => $item['订单编号'],
                '子订单编号'   => $item['子订单编号'],
                '尺码' => $item['尺码'],
                '制单数量' => $item['制单数量'],
                '发货箱数' => $item['发货箱数'],
                '每箱件数' => $item['每箱件数'],
                '发货数量' => $item['发货数量'],
                'sys_id'   => $item['sys_id'],
                'sys_rq'   => date('Y-m-d H:i:s'),
            ];

            $sql = \db('工单_发货基本资料')->fetchSql(true)->insert($Data);
            \db()->query($sql);
        }
        $this->success('成功');
    }

    //按订单统计发货总数
    public function Read_List(){
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }
        $param = $this->request->param();

        $Orders = \db('工单_基本资料')
            ->where('客户编号', $param['khbh'])
            ->whereNull('Mod_rq')
            ->select();

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

        // 将订单数据转换为以订单编号为键的数组，方便查找
        $orderMap = [];
        foreach ($Orders as $order) {
            $orderMap[$order['订单编号']] = $order;
        }

        $orderNumbers = array_column($Orders, '订单编号');
        $shipmentData = \db('工单_发货基本资料')->alias('a')
            ->field('j.客户编号, j.款式, j.生产款号, a.*')
            ->join('工单_基本资料 j', 'a.订单编号 = j.订单编号', 'left')
            ->whereNull('a.mod_rq')
            ->where('a.订单编号', 'in', $orderNumbers)
            ->select();


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

        // 修正的汇总统计（不包含明细）
        $result = [];
        foreach ($shipmentData as $item) {
            $orderNo = $item['订单编号'];

            // 如果该订单还没有在结果数组中，先初始化
            if (!isset($result[$orderNo])) {
                $orderInfo = $orderMap[$orderNo];
                $result[$orderNo] = [
                    '订单编号' => $orderNo,
                    '客户编号' => $item['客户编号'],
                    '款式' => $item['款式'],
                    '生产款号' => $item['生产款号'],
                    '总发货箱数' => 0,
                    '总发货数量' => 0,
                    '制单数量' => $orderInfo['订单数量']
                ];
            }

            // 累加发货箱数和发货数量
            $result[$orderNo]['总发货箱数'] += intval($item['发货箱数'] ?? 0);
            $result[$orderNo]['总发货数量'] += intval($item['发货数量'] ?? 0);
        }

        $finalResult = array_values($result);
        $this->success('查询成功', $finalResult);
    }

    //按照子订单统计发货明细
    public function Read_ListsData(){
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }
        $param = $this->request->param();

        $sql = \db('工单_发货基本资料')->alias('a')
            ->field('j.客户编号,j.款式,j.生产款号,a.*')
            ->join('工单_基本资料 j', 'a.订单编号 = j.订单编号', 'left')
            ->whereNull('a.mod_rq')
            ->where('a.订单编号', $param['order'])
            ->select();
        $this->success('查询成功',$sql);
    }

    /**
     * 订单打印接口
     * order:订单编号
     * 通过订单编号获取订单表数据以及子订单数据
     */
    public function orderPrint(){
        if ($this->request->isGet() === false){$this->error('请求错误');}
        $param = $this->request->param();
        if (empty($param['order'])){$this->error('参数错误');}

        $where['Mod_rq'] = null;
        //订单信息
        $list = \db('工单_基本资料')
            ->where('订单编号',$param['order'])
            ->where($where)
            ->field('订单编号,img,生产款号,客户编号,款式,落货日期,箱唛要求,面料,船样描述,船样合计,粘衬,订单数量,审核,审核日期,要求,water,Sys_id')
            ->find();

        //尺码表格表头
        $xhdata = \db('工单_印件资料')->where('订单编号', $param['order'])->where($where)->select();
        $arr = [];
        foreach ($xhdata as $key => $value) {
            for ($i = 1; $i <= 14; $i++) {
                if ($value['cm' . $i] !== '' && $value['cm' . $i] !== null) {
                    array_push($arr, $value['cm' . $i]);
                }
            }
        }

        // 去重并重新索引
        $arr = array_unique($arr);
        $arr = array_values($arr);

        // 自定义排序函数
        usort($arr, function($a, $b) {
            // 判断是否为数字
            $isNumericA = is_numeric($a);
            $isNumericB = is_numeric($b);

            if ($isNumericA && $isNumericB) {
                // 如果都是数字，按从小到大排序
                return $a - $b;
            } elseif (!$isNumericA && !$isNumericB) {
                // 如果都是字母，按字母顺序排序（可以自定义顺序）
                $sizeOrder = ['XXS', 'XS', 'S', 'M', 'L', 'XL','2XL','XXL','3XL', 'XXXL','4XL','XXXXL','5XL','XXXXL'];
                $posA = array_search($a, $sizeOrder);
                $posB = array_search($b, $sizeOrder);
                return $posA - $posB;
            } else {
                // 如果一个是数字一个是字母，数字排在前
                return $isNumericA ? -1 : 1;
            }
        });


        //打印table数据表格
        $porlis = \db('工单_印件资料')
            ->where('订单编号',$param['order'])
            ->where('船样',0)
            ->where($where)
            ->field('子订单编号')
            ->select();
        //合并后的子订单条码数据
        $subOrder = $porlis[0]['子订单编号'];
        // 找到子订单编号中的 '-' 位置
        $dashPos = strpos($subOrder, '-');

        if ($dashPos !== false) {
            // 提取 '-' 后面的部分
            $afterDash = substr($subOrder, $dashPos + 1);

            // 判断长度是否等于2或等于4
            if (strlen($afterDash) == 2) {
                // 查询船样为0的数据
                $processlist = \db('工单_印件资料')
                    ->where('订单编号', $param['order'])
                    ->where($where)
                    ->where('船样', 0)
                    ->field('子订单编号,颜色,款号,zdtotal,颜色备注,color_id,Uniqid,
                            cm1,cm2,cm3,cm4,cm5,cm6,cm7,cm8,cm9,cm10,cm11,cm12,cm13,cm14,
                            cmsl1,cmsl2,cmsl3,cmsl4,cmsl5,cmsl6,cmsl7,cmsl8,cmsl9,cmsl10,cmsl11,cmsl12,cmsl13,cmsl14')
                    ->select();

                // 初始化汇总数组
                $scslTotals = [
                    "cmsl1" => 0,
                    "cmsl2" => 0,
                    "cmsl3" => 0,
                    "cmsl4" => 0,
                    "cmsl5" => 0,
                    "cmsl6" => 0,
                    "cmsl7" => 0,
                    "cmsl8" => 0,
                    "cmsl9" => 0,
                    "cmsl10" => 0,
                    "cmsl11" => 0,
                    "cmsl12" => 0,
                    "cmsl13" => 0,
                    "cmsl14" => 0,
                    "zdtotal" => 0, // 总计数量
                ];
                // 遍历数据集进行汇总
                foreach ($processlist as $item) {
                    for ($i = 1; $i <= 14; $i++) {
                        // 获取当前字段的数量
                        $sizeQty = $item['cmsl' . $i];
                        // 判断数量是否有效（非空且大于0）
                        if (!empty($sizeQty)) {
                            // 累加当前字段的数量
                            $scslTotals['cmsl' . $i] += $sizeQty;
                            // 累加到总计字段
                            $scslTotals['zdtotal'] += $sizeQty;
                        }
                    }
                }

                $data['scslTotals'] = $scslTotals;

                foreach ($processlist as $key => $value) {
                    for ($i = 1; $i <= 14; $i++) {
                        if ($value['cm' . $i] !== '' && $value['cm' . $i] !== null) {
                            $processlist[$key][$value['cm' . $i]] = $value['cmsl' . $i];
                        }
                        // 移除原始的 cm 和 cmsl 字段
                        unset($processlist[$key]['cm' . $i], $processlist[$key]['cmsl' . $i]);
                    }
                }
                // 用于存储合并后的数据
                $mergedData = [];
                // 按颜色备注进行合并
                foreach ($processlist as $item) {
                    $key = $item['颜色备注'];

                    if (!isset($mergedData[$key])) {
                        $mergedData[$key] = $item;
                        // 添加条码字段，值为子订单编号
                        $mergedData[$key]['条码'] = $item['子订单编号'];
                    } else {
                        // 合并尺码对应的数量
                        foreach ($item as $size => $quantity) {
                            if (is_numeric($size)) {
                                if (!isset($mergedData[$key][$size])) {
                                    $mergedData[$key][$size] = 0;
                                }
                                $mergedData[$key][$size] += $quantity;
                            }
                        }
                        // 合并 zdtotal
                        $mergedData[$key]['zdtotal'] += $item['zdtotal'];
                    }
                }
                // 查询船样为1的数据
                $chuanyang = \db('工单_印件资料')
                    ->where('订单编号', $param['order'])
                    ->where($where)
                    ->where('船样', 1)
                    ->field('子订单编号,颜色,款号,zdtotal,颜色备注,color_id,
                            cm1,cm2,cm3,cm4,cm5,cm6,cm7,cm8,cm9,cm10,cm11,cm12,cm13,cm14,
                            cmsl1,cmsl2,cmsl3,cmsl4,cmsl5,cmsl6,cmsl7,cmsl8,cmsl9,cmsl10,cmsl11,cmsl12,cmsl13,cmsl14,Uniqid')
                    ->select();
                foreach ($chuanyang as $key => $value) {
                    for ($i = 1; $i <= 14; $i++) {
                        if ($value['cm' . $i] !== '' && $value['cm' . $i] !== null) {
                            $chuanyang[$key][$value['cm' . $i]] = $value['cmsl' . $i];
                        }
                        // 移除原始的 cm 和 cmsl 字段
                        unset($chuanyang[$key]['cm' . $i], $chuanyang[$key]['cmsl' . $i]);
                    }
                    // 添加条码字段，值为子订单编号
                    $chuanyang[$key]['条码'] = $value['子订单编号'];
                }

                // 将合并后的数据插入到相应颜色备注的原始数据尾部
                $finalList = [];
                $groupedData = [];

                // 将原始数据按颜色备注分组
                foreach ($processlist as $item) {
                    $key = $item['颜色备注'];
                    if (!isset($groupedData[$key])) {
                        $groupedData[$key] = [];
                    }
                    $groupedData[$key][] = $item;
                }

                // 将合并后的数据插入到对应的颜色备注组的尾部
                foreach ($groupedData as $key => $items) {
                    $finalList = array_merge($finalList, $items); // 先添加原始数据
                    if (isset($mergedData[$key])) {
                        $finalList[] = $mergedData[$key]; // 在组的尾部添加合并数据
                    }
                }
                // 将船样为1的数据添加到最终列表中
                $finalList = array_merge($finalList, $chuanyang);
                $data['process'] = $finalList;
                $data['order'] = $list;
                $data['xhdata'] = $arr;
                $this->success('成功',$data);
            } elseif (strlen($afterDash) == 4) {
                //一条子订单编号一个条码，统计颜色
                $processlist = \db('工单_印件资料')
                    ->where('订单编号', $param['order'])
                    ->whereNull('Mod_rq') // 查询未删除数据
                    ->select();

                $table = [];
                foreach ($processlist as $item) {
                    // 当前子订单编号的数据
                    $subOrder = [
                        '颜色备注' => $item['颜色备注'],
                        '色系名称' => $item['颜色'],
                        '订单编号' => $item['订单编号'],
                        '子订单编号' => $item['子订单编号'],
                        '条码' => $item['子订单编号'],
                        '款号' => $item['款号'],
                        'Sys_id' => $item['Sys_id'],
                    ];
                    // 当前子订单编号的合计
                    $subOrderTotal = 0;
                    for ($i = 1; $i <= 14; $i++) {
                        // 判断 cm 和 cmsl 是否有值，空值不显示，0 则显示
                        if (isset($item['cm' . $i]) && $item['cm' . $i] !== '') {
                            $subOrder[$item['cm' . $i]] = $item['cmsl' . $i] ?? 0; // 默认数量为 0
                            // 累加每个尺码的数量
                            $subOrderTotal += (int)$item['cmsl' . $i]; // 确保是数值类型
                        }
                        // 清理字段
                        unset($item['cm' . $i], $item['cmsl' . $i]);
                    }
                    $subOrder['合计'] = $subOrderTotal; // 添加合计
                    $table[] = $subOrder; // 将当前子订单的数据添加到 $table 中
                }


                // 初始化汇总数组
                $scslTotals = [
                    "cmsl1" => 0,
                    "cmsl2" => 0,
                    "cmsl3" => 0,
                    "cmsl4" => 0,
                    "cmsl5" => 0,
                    "cmsl6" => 0,
                    "cmsl7" => 0,
                    "cmsl8" => 0,
                    "cmsl9" => 0,
                    "cmsl10" => 0,
                    "cmsl11" => 0,
                    "cmsl12" => 0,
                    "cmsl13" => 0,
                    "cmsl14" => 0,
                    "zdtotal" => 0, // 总计数量
                ];

                // 遍历数据集进行汇总
                foreach ($processlist as $item) {
                    // 遍历每个尺码字段（cmsl1 到 cmsl10）
                    for ($i = 1; $i <= 14; $i++) {
                        // 获取当前字段的数量
                        $sizeQty = $item['cmsl' . $i];
                        // 判断数量是否有效（非空且大于0）
                        if (!empty($sizeQty)) {
                            // 累加当前字段的数量
                            $scslTotals['cmsl' . $i] += $sizeQty;
                            // 累加到总计字段
                            $scslTotals['zdtotal'] += $sizeQty;
                        }
                    }
                }
                $data['scslTotals'] = $scslTotals;
                $data['process'] = $table;//汇总数据集
                $data['order'] = $list;//表格数据
                $data['xhdata'] = $arr;//尺码表头
                $this->success('成功', $data);
//                }
            } else {
                echo "子订单编号 - 后不是2位也不是4位：$afterDash";
            }
        } else {
            echo "子订单编号中没有找到'-'";
        }
    }

    /**
     * 订单编号自动获取
     * @return void
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function getWorkOrder()
    {
        if ($this->request->isGet() === false){
            $this->error('请求错误');
        }
        $time =substr( date('Ym',time()),2);
        $lastOrder = \db('工单_基本资料')
            ->where('订单编号','like','%'.$time.'%')
            ->order('Uniqid desc')
            ->find();
        if (empty($lastOrder)){
            $newNumber = 1;
        }else{
            $lastNumber = substr($lastOrder['订单编号'],6);
            $newNumber = (int)$lastNumber + 1;
        }
        if ($newNumber<10){
            $newOrder = 'DC'.$time.'00'.$newNumber;
        }elseif ($newNumber>=10 && $newNumber<100){
            $newOrder = 'DC'.$time.'0'.$newNumber;
        }else{
            $newOrder = 'DC'.$time.$newNumber;
        }
        $this->success('成功',$newOrder);
    }

    /**
     * 获取子订单编号
     * @return void
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function getSuborder(){
        // 确保是GET请求
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }
        $param = $this->request->param();
        if (empty($param) || !isset($param['cy']) || !isset($param['order'])) {
            $this->error('参数错误');
        }
        // 判断是否“船样”获取对应的子订单编号
        if ($param['cy'] == '否') {

            //1.通过色系名称查询对应的编号
            $colorlist = \db('工单_颜色编号')
                ->field('colorcode, colorname')
                ->where('colorname', $param['colorname'] ?? '')
                ->find();

            if (empty($colorlist)) {
                $this->error('未找到对应的颜色编号');
            }
            $num = $param['order'] . '-' . $colorlist['colorcode'];

            // 查询子订单编号
            $data = \db('工单_印件资料')
                ->field('子订单编号,cm1,cm2,cm3,cm4,cm5,cm6,cm7,cm8,cm9,cm10,cm11,cm12,cm13,cm14')
                ->where('子订单编号', 'like', '%' . $num . '%')
                ->where('船样', '=', 0)
                ->whereNull('Mod_rq')
                ->order('子订单编号', 'desc')
                ->find();

            if (empty($data)) {
                // 如果没有找到数据，生成默认的订单编号，后两位从00开始
                $order = $param['order'] . '-' . $colorlist['colorcode'] . '00';
            } else {

                if (strlen($data['子订单编号']) == 12) {
                    $data = \db('工单_印件资料')
                        ->where('订单编号',$param['order'])
                        ->where('船样',0)
                        ->whereNull('Mod_rq')
                        ->order('子订单编号 desc')
                        ->find();
                    if(empty($data)){
                        $order = $param['order'].'-01';
                    }else{
                        $num = (int)substr($data['子订单编号'],10) + 1;
                        if ($num<10){
                            $order = $param['order'].'-0'.$num;
                        }else{
                            $order = $param['order'].'-'.$num;
                        }
                    }
                }else{
                    // 如果找到数据，提取子订单编号并递增
                    $order = $data['子订单编号'];
                    if (preg_match('/(.*-' . $colorlist['colorcode'] . ')(\d{2})$/', $order, $matches)) {
                        $prefix = $matches[1]; // 前缀部分（包括订单编号和颜色代码）
                        $number = $matches[2]; // 数字部分（后两位）

                        // 循环生成子订单编号并检查数据库中是否存在相同编号
                        do {
                            $incrementedNumber = (int)$number + 1;
                            // 将数字部分补充为两位数，例如 1 -> 01，2 -> 02
                            $order = $prefix . str_pad($incrementedNumber, 2, '0', STR_PAD_LEFT);
                            $exists = \db('工单_印件资料')->whereNull('Mod_rq')->where('子订单编号', '=', $order)->find();
                            $number = $incrementedNumber; // 更新 number 用于下一次循环
                        } while ($exists); // 如果存在相同编号则继续循环递增
                    } else {
                        $this->error('订单编号格式错误');
                    }
                }

            }

            //2.获取色系名称信息
            $colorlist = \db('工单_颜色编号')->select();

            //3.获取历史尺码数据
            $cm_list = \db('工单_印件资料')
                ->where('订单编号', $param['order'])
                ->group('订单编号')
                ->find();

            $cm_data = [];
            if ($cm_list) {
                // 精准筛选字段名以 "cm" 开头并跟1到2位数字的字段
                foreach ($cm_list as $key => $value) {
                    if (preg_match('/^cm\d{1,2}$/', $key)) {
                        $cm_data[$key] = $value ?? ''; // 如果值为 null，设为空字符串
                    }
                }
            } else {
                // 如果查询结果为空，将都设置为空
                for ($i = 1; $i <= 14; $i++) {
                    $cm_data["cm$i"] = '';
                }
            }



            $result = ['order' => $order,'colorlist' => $colorlist,'cm' => $cm_data];
            $this->success('成功', $result);

        } else if ($param['cy'] == '是') {

            //1.获取船样子订单编号
            $data = \db('工单_印件资料')
                ->where('订单编号', $param['order'])
                ->where('船样', '=', 1)
                ->order('子订单编号 asc')
                ->find();

            if (empty($data)) {
                // 如果没有数据，初始子订单编号为 '99'
                $order = $param['order'] . '-99';
            } else {
                // 提取子订单编号中的数字部分
                $subOrder = $data['子订单编号'];  // 例如 "DC2410006-99"
                if (preg_match('/-(\d+)$/', $subOrder, $matches)) {
                    $numberPart = (int)$matches[1];  // 提取到的数字部分
                    $newNumber = $numberPart - 1;    // 减 1
                    // 将新的数字拼接回订单编号
                    $order = preg_replace('/-\d+$/', '-' . $newNumber, $subOrder);
                } else {
                    $this->error('订单编号格式错误');
                }
            }

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

    /**
     * 获取PO号 【代码展示未用到】
     */
    public function getPonumber() {
        if ($this->request->isGet() === false){
            $this->error('请求错误');
        }
        $param = $this->request->param();
        if (empty($param)){
            $this->error('参数错误');
        }
        // $num = substr($param['child_order'], 0, 12);  // 如果需要，可以用这个方式截取子订单编号
//        $sql = "SELECT * FROM `工单_印件资料` WHERE `子订单编号` LIKE '{$num}%' ORDER BY `子订单编号` DESC LIMIT 1";
//        $data = \db()->query($sql);
        $colorlist = \db('工单_颜色编号')
            ->field('colorcode,colorname')
            ->where('colorname',$param['child_order'])
            ->find();
        $num = $param['order'] . '-' . $colorlist['colorcode'];  // 生成 num，用于模糊查询
        $data = \db('工单_印件资料')
            ->where('子订单编号', 'like', '%' . $num . '%')
            ->order('子订单编号', 'desc')
            ->find();
        if(count($data) === 1){
            $order = $num.'01';
        }else{
            $number = (int)substr($data['子订单编号'],12,14) + 1;
            if ($num<10){
                $order = $num.'0'.$number;
            }else{
                $order = $num.$number;
            }
        }
        $this->success('成功',$order);
    }

    /**
     * 订单资料修改
     * @return void
     * @throws \think\Exception
     * @throws \think\db\exception\BindParamException
     * @throws \think\exception\PDOException
     */
    public function WorkOrderEdit()
    {
        if (Request::instance()->isPost() === false){
            $this->error('请求错误');
        }
        $param = Request::instance()->post();
        if (empty($param)){
            $this->error('参数错误');
        }
        $id = $param['id'];
        unset($param['id']);
        $sql = \db('工单_基本资料')
            ->where('Uniqid',$id)
            ->fetchSql(true)
            ->update($param);
        $res = \db()->query($sql);
        if ($res === false){
            $this->error('失败');
        }else{
            $this->success('成功');
        }
    }


    /**
     * 颜色资料修改
     */
    public function PrintDataEdit()
    {
        if(Request::instance()->post() === false){
            $this->error('请求错误');
        }
        $param = Request::instance()->post();
        if (empty($param)){
            $this->error('参数错误');
        }

        $updata = [
            '订单编号' => $param['订单编号'],
            '子订单编号' => $param['子订单编号'],
            '款号' => $param['款号'],
            '船样' => $param['船样'],
            '颜色' => $param['颜色'],
            'color_id' => $param['color_id'],
            '颜色备注' => $param['颜色备注'],
            'zdtotal' => $param['zdtotal'],
            'updatatime' => date('Y-m-d H:i:s')
        ];
        for ($i = 1; $i <= 14; $i++) {
            $updata["cmsl{$i}"] = isset($param["cmsl{$i}"]) ? $param["cmsl{$i}"] : '';
        }

        $sql = \db('工单_印件资料')
            ->where('Uniqid', $param['id'])
            ->fetchSql(true)
            ->update($updata);
        $res = \db()->query($sql);
        if ($res !== false) {
            $this->success('修改成功');
        } else {
            $this->error('修改失败');
        }
    }

    /**
     * 图片上传
     * @return void
     */
    public function ImgUpload(){
        $file = request()->file('image');
        if($file){
            $info = $file->validate(['size'=>10485760,'ext'=>'jpg,png'])->move(ROOT_PATH . 'public' . DS . 'uploads');
            if($info){
                $fileName = $info->getSaveName();
//                $ymd = date('Ymd');
//                $imageUrl = '/uploads/' . $ymd.'/'.$fileName;
                $imageUrl = '/uploads/' . str_replace('\\', '/', $fileName);
                return json(['code' => 0, 'msg' => '成功', 'data' => ['url' => $imageUrl]]);
            }else{
                $res = $file->getError();
                return json(['code' => 1, 'msg' => '失败', 'data' => $res]);
            }
        }
        return json(['code' => 1, 'msg' => '没有文件上传', 'data' => null]);
    }


    /**
     * 附件资料左侧菜单（日期格式）
     */
    public function OrderMenuList()
    {
        $list = \db('工单_相关附件')
            ->field('sys_rq')
            ->whereNull('mod_rq')
            ->group('sys_rq')
            ->select();

        $result = [];

        foreach ($list as $item) {
            $dateTime = $item['sys_rq'];
            $dateOnly = date('Y-m-d', strtotime($dateTime)); // 提取日期
            $year = date('Y', strtotime($dateTime));
            // 加上(int)date('m', strtotime($sys_rq))转成数字去掉前导 01 => 1
            $month = date('m', strtotime($dateTime));

            // 初始化结构
            if (!isset($result[$year])) {
                $result[$year] = [];
            }
            if (!isset($result[$year][$month])) {
                $result[$year][$month] = [];
            }

            // 避免重复添加相同日期
            if (!in_array($dateOnly, $result[$year][$month])) {
                $result[$year][$month][] = $dateOnly;
            }
        }

        // 按年月日进行倒序排序
        foreach ($result as $year => &$months) {
            krsort($months); // 月份倒序
            foreach ($months as &$dates) {
                rsort($dates); // 日期倒序
            }
        }

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

    /**
     * 订单附件技术附件
     */
    /**
     * 订单附件技术附件
     */
    public function OrderAttachments()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }

        $param = $this->request->param();

        $sys_rq = isset($param['sys_rq']) ? trim($param['sys_rq']) : ''; // yyyy-mm-dd
        $order  = $param['order'];
        $desc   = $param['desc'];
        $page   = max(1, intval(input('page', 1)));
        $limit  = max(1, intval(input('limit', 10)));

        $where = [];
        if ($order !== '') {
            $where['关联编号'] = $order;
        }

        // 附件类型筛选（如技术附件）
        if ($desc !== '') {
            $where['附件备注'] = $desc;
        }
        // 日期筛选
        if ($sys_rq !== '') {
            $where['sys_rq'] = ['like', '%' . $sys_rq . '%'];
        }

        // 基础查询条件
        $query = \db('工单_相关附件')
            ->field('UniqId, mod_rq, pdf, sys_id, sys_rq, updatetime, url, version, 关联编号, 附件内容, 附件备注, 附件类型, rand_number')
            ->where($where)
            ->where('version', '<>', '')
            ->whereNull('mod_rq')
            ->order('sys_rq desc');

        // 统一分页逻辑
        if ($order === '') {
            // 订单为空时分页查询
            $list = $query->limit(($page - 1) * $limit, $limit)->select();
        } else {
            // 订单不为空时也需要分页
            $list = $query->limit(($page - 1) * $limit, $limit)->select();
        }

        $count = $query->count();

        $data = [
            'total' => $count,
            'list'  => $list
        ];

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

    /**
     * 工单附件删除
     */
    public function delfujian(){
        if (!$this->request->isPost()) {
            $this->error('非法请求');
        }
        $params = $this->request->param();
        $updateData = [
            'mod_rq' => date('Y-m-d H:i:s'),
            'mod_id' => $params['登录用户'],
        ];
        $res = \db('工单_相关附件')->where('UniqId', $params['UniqId'])->update($updateData);
        if ($res) {
            $this->success('删除成功');
        } else {
            $this->error('删除失败');
        }
    }


    /**
     * 订单BOM资料显示
     */
    public function OrderBomList()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }
        $param = $this->request->param();
        if (empty($param) || !isset($param['order'])) {
            $this->error('参数错误');
        }
        $where = ['a.BOM_工单编号' => $param['order']];

        $list = \db('工单_bom资料')
            ->alias('a')
            ->join('工单_基本资料 b', 'b.订单编号 = a.BOM_工单编号')
            ->field('a.BOM_工单编号 as 订单编号,b.生产款号 as 生产款号,b.客户编号 as 客户编号,b.款式 as 款式,
            a.BOM_物料名称 as 物料名称,a.BOM_投料单位 as 投料单位,a.BOM_计划用量 as 计划用料,a.BOM_标准用量 as 定额用料,
                a.BOM_实际用量 as 裁床实际用料,a.BOM_desc as 备注,a.UNIQID,a.物料分类,
            a.BOM_计划门幅 as 计划门幅, a.BOM_定额门幅 as 定额门幅,a.Sys_ID as ID,a.Sys_rq as 日期')
            ->where($where)
            ->whereNull('a.Mod_rq')
            ->order('a.UNIQID desc')
            ->select();

        if (!empty($list)) {
            // 获取去重后的物料名称
            $materialNames = array_column($list, '物料名称');
            $materialNames = array_unique($materialNames);

            // 根据去重后的物料名称查询工单_面料资料表，并获取BOM_desc
            $materialDetails = \db('工单_面料资料')
                ->field('BOM_物料名称 as 物料名称,BOM_desc')
                ->whereIn('BOM_物料名称', $materialNames)
                ->select();
            // 将物料名称与BOM_desc对应起来
            $materialDescMap = [];
            foreach ($materialDetails as $detail) {
                $materialDescMap[$detail['物料名称']] = $detail['BOM_desc'];
            }
            // 在list中添加对应的BOM_desc
            foreach ($list as &$item) {
                if (isset($materialDescMap[$item['物料名称']])) {
                    $item['BOM_desc'] = $materialDescMap[$item['物料名称']];
                } else {
                    $item['BOM_desc'] = '';
                }
            }
            $this->success('成功', $list);
        } else {
            $this->GdGtpAiOrder($param['order']);
            $this->success('没有找到相关数据',[]);
//            return $this->success('没有找到相关数据', []);
        }
    }

    /**
     * Bom资料删除
     * */
    public function Bomdel(){
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }
        $param = $this->request->param();
        if (empty($param) || !isset($param['UNIQID'])) {
            $this->error('参数错误');
        }
        $uniqids = strpos($param['UNIQID'], ',') !== false ? explode(',', $param['UNIQID']) : [$param['UNIQID']];


        $where = [];
        $where['Mod_rq'] = date('Y-m-d H:i:s', time());

        $allUpdated = true;
        $failedUniqids = [];
        // 遍历所有UNIQID并更新数据库
        foreach ($uniqids as $uniqid) {

            $list = \db('工单_bom资料')
                ->where('UNIQID', $uniqid)
                ->find();

            $cangku = Db::name('库存_出入库明细')
                ->where('order_id',$list['BOM_工单编号'])
                ->where('物料名称',$list['BOM_物料名称'])
                ->whereNull('Mod_rq')
                ->find();

            if ($cangku) {
                return json([
                    'code' => 1,
                    'msg'  => '【物料名称】：' . $list['BOM_物料名称'] . '。该物料仓库已录入，暂无法删除。如需删除，请联系仓库人员清除相关明细数据后再进行操作。'
                ])->options(['json_encode_param' => JSON_UNESCAPED_UNICODE]);
            }

            $result = \db('工单_bom资料')
                ->where('UNIQID', $uniqid)
                ->update($where);

            $arr = \db('工单_面料资料')
                ->where('BOM_工单编号', $list['BOM_工单编号'])
                ->where('BOM_物料名称', $list['BOM_物料名称'])
                ->update($where);

            $arr = \db('工单关联表')
                ->where('订单编号', $list['BOM_工单编号'])
                ->where('物料名称', $list['BOM_物料名称'])
                ->update($where);

            if (!$result) {
                // 如果某个UNIQID更新失败，记录失败的ID
                $allUpdated = false;
                $failedUniqids[] = $uniqid;
            }
        }
        if ($allUpdated) {
            $list = \db('工单_bom资料')
                ->whereIn('UNIQID', $uniqids)
                ->select();
            if (!empty($list)) {
                $this->success('删除成功');
            } else {
                $this->GdGtpAiOrder($param['order']);
                return $this->success('没有找到相关数据', []);
            }
        } else {
            $this->error('部分更新失败，无法更新以下UNIQID: ' . implode(', ', $failedUniqids));
        }
    }




    /**
     * 前端选择订单时如果BOM资料数据为空则单独调用，重新生成最新
     */
    public function GdGtpAiOrder($order){
        // 判断是否有指定的订单号
        if (!empty($order)) {
            // 查询单个订单的最大编号
            $maxOrder = \db('工单_基本资料')
                ->where('订单编号', 'like', "{$order}%")
                ->order('订单编号', 'desc')
                ->limit(1)
                ->value('订单编号');

            // 查询该订单的基本资料
            $list = \db('工单_基本资料')
                ->where('订单编号', 'like', "{$order}%")
                ->order('订单编号', 'desc')
                ->limit(1)
                ->find();

            // 如果面料数据为空，提示错误
            if (empty($list['面料'])) {
                $this->error('面料数据为空无法定义BOM');
            }

            // 处理订单编号
            $numericPart = substr($maxOrder, 2);
            $newNumericPart = str_pad((int)$numericPart + 1, strlen($numericPart), '0', STR_PAD_LEFT);
            $param['订单编号'] = $order . $newNumericPart;

            // 处理物料信息
            $massage = empty($list['粘衬']) || $list['粘衬'] == '无' ? $list['面料'] : $list['面料'] . '，粘衬：' . $list['粘衬'];
            $materialCategories = [];
            $pattern = '/(\S+?)：([^，]+)/'; // 匹配 类别：物料 格式

            preg_match_all($pattern, $massage, $matches, PREG_SET_ORDER);

            foreach ($matches as $match) {
                $category = $match[1]; // 分类名称
                $materials = explode('＋', $match[2]); // 如果物料名称中有‘＋’，则分开处理多个物料

                // 将分类和对应的物料添加到数组中
                foreach ($materials as $material) {
                    $materialCategories[$category][] = trim($material); // 去除物料两边的空格
                }
            }

            $mianliao = $this->Gpt($massage);

            $data = [];

//            if ($mianliao) {
//                $this->success('成功');
//            }

            foreach ($mianliao as $value) {
                if (!empty($value) && $value !== '粘衬') { // 排除空值和粘衬
                    $category = '';

                    // 查找物料对应的分类
                    foreach ($materialCategories as $cat => $materials) {
                        if (in_array($value, $materials)) {
                            $category = $cat;
                            break;
                        }
                    }

                    // 如果找到分类，将数据存入BOM
                    $data[] = [
                        'BOM_工单编号' => $list['订单编号'],
                        'BOM_物料名称' => $value,
                        'BOM_desc' => '',
                        '物料分类' => $category ? $category : '',
                        'Sys_rq' => date('Y-m-d H:i:s'),
                        'Sys_ID' => $list['Sys_id']
                    ];
                }
            }
            foreach ($data as &$item) {
                if (empty($item['物料分类'])) {
                    $item['物料分类'] = '';
                }
                // 去除所有非汉字字符
                $item['物料分类'] = preg_replace('/[^\p{Han}]/u', '', $item['物料分类']);
            }

            // 批量插入BOM资料
            if (!empty($data)) {
                \db('工单_bom资料')->insertAll($data);
            }
            $this->success('成功',$order);
        } else {
            // 如果没有指定订单号，批量查询订单号并处理
            $has_bom = \db('工单_bom资料')->alias('a')->field('a.BOM_工单编号')->group('a.BOM_工单编号')->select();
            $all_orders = \db('工单_基本资料')->alias('a')->field('a.订单编号')->where('a.面料', '<>', '')->group('a.订单编号')->select();

            // 提取有BOM资料的订单号
            $has_bom_orders = array_column($has_bom, 'BOM_工单编号');

            // 筛选出没有对应BOM资料的订单号
            $no_bom_orders = array_filter($all_orders, function ($order) use ($has_bom_orders) {
                return !in_array($order['订单编号'], $has_bom_orders);
            });

            // 遍历没有BOM资料的订单
            foreach ($no_bom_orders as $orderData) {
                // 获取该订单号的最大订单编号
                $maxOrder = \db('工单_基本资料')
                    ->where('订单编号', 'like', "{$orderData['订单编号']}%")
                    ->order('订单编号', 'desc')
                    ->limit(1)
                    ->value('订单编号');

                // 获取该订单号的具体数据
                $list = \db('工单_基本资料')
                    ->where('订单编号', 'like', "{$orderData['订单编号']}%")
                    ->order('订单编号', 'desc')
                    ->limit(1)
                    ->find();

                if (empty($list['面料'])) {
                    $this->error("订单 {$orderData['订单编号']} 面料数据为空，无法定义BOM");
                }

                // 处理订单编号
                $numericPart = substr($maxOrder, 2);
                $newNumericPart = str_pad((int)$numericPart + 1, strlen($numericPart), '0', STR_PAD_LEFT);
                $param['订单编号'] = $order . $newNumericPart;

//                // 处理物料信息
//                $massage = empty($list['粘衬']) || $list['粘衬'] == '无' ? $list['面料'] : $list['面料'] . '，粘衬：' . $list['粘衬'];
//                $mianliao = $this->Gpt($massage);
//                // 插入物料数据
//                $data = [];
//                foreach ($mianliao as $key => $value) {
//                    if (!empty($value) && $value !== '粘衬') { // 排除空值和粘衬
//                        $data[] = [
//                            'BOM_工单编号' => $list['订单编号'],
//                            'BOM_物料名称' => $value,
//                            'BOM_desc' => '',
//                            '物料分类' => '',
//                            'Sys_rq' => date('Y-m-d H:i:s'),
//                            'Sys_ID' => '超级管理员'
//                        ];
//                    }
//                }
                // 假设massage是从数据库获取的数据
                $massage = empty($list['粘衬']) || $list['粘衬'] == '无' ? $list['面料'] : $list['面料'] . '，粘衬：' . $list['粘衬'];

                $materialCategories = [];
                $pattern = '/(\S+?)：([^，]+)/'; // 匹配 类别：物料 格式

                preg_match_all($pattern, $massage, $matches, PREG_SET_ORDER);

                foreach ($matches as $match) {
                    $category = $match[1]; // 分类名称
                    $materials = explode('＋', $match[2]); // 如果物料名称中有‘＋’，则分开处理多个物料

                    // 将分类和对应的物料添加到数组中
                    foreach ($materials as $material) {
                        $materialCategories[$category][] = trim($material); // 去除物料两边的空格
                    }
                }

                $mianliao = $this->Gpt($massage);

                $data = [];
                foreach ($mianliao as $value) {
                    if (!empty($value) && $value !== '粘衬') { // 排除空值和粘衬
                        $category = '';

                        // 查找物料对应的分类
                        foreach ($materialCategories as $cat => $materials) {
                            if (in_array($value, $materials)) {
                                $category = $cat;
                                break;
                            }
                        }

                        // 如果找到分类，将数据存入BOM
                        $data[] = [
                            'BOM_工单编号' => $list['订单编号'],
                            'BOM_物料名称' => $value,
                            'BOM_desc' => '',
                            '物料分类' => $category ? $category : '',
                            'Sys_rq' => date('Y-m-d H:i:s'),
                            'Sys_ID' => '超级管理员'
                        ];
                    }
                }
                foreach ($data as &$item) {
                    if (empty($item['物料分类'])) {
                        $item['物料分类'] = '';
                    }
                    // 去除所有非汉字字符
                    $item['物料分类'] = preg_replace('/[^\p{Han}]/u', '', $item['物料分类']);
                }

                // 批量插入BOM资料
                if (!empty($data)) {
                    \db('工单_bom资料')->insertAll($data);
                }
            }
            $this->success('成功');
        }
    }


    /**
     * 订单面料修改接口
     */
    public function FabricEdit()
    {
        if ($this->request->isPost() === false){
            $this->error('请求错误');
        }
        $param = Request::instance()->post();

        if (empty($param)){$this->error('请求错误');}
        if(empty($param[0]['BOM_工单编号'])){$this->error('请求错误,请重新打开此页面');}

        foreach ($param as $key => $value){
            $data = $value;
            unset($data['UNIQID']);

            // 更新操作
            if (!empty($value['UNIQID'])) {

                // 查询旧数据
                $oldData = \db('工单_bom资料')
                    ->where('UNIQID', $value['UNIQID'])
                    ->find();

                // 日志记录逻辑
                if ($oldData) {
                    foreach ($data as $field => $newValue) {
                        $oldValue = $oldData[$field] ?? null;
                        if ($oldValue != $newValue) {
                            $bom_sql = \db('工单_bom修改日志')
                                ->fetchSql(true)
                                ->insert([
                                    '订单编号'   => $oldData['BOM_工单编号'],
                                    '字段'       => $field,
                                    '修改前数据' => $oldValue,
                                    '修改后数据' => $newValue,
                                    '修改人'     => $param[0]['Sys_ID'],
                                    '修改时间'   => date('Y-m-d H:i:s'),
                                    'UNIQID'     => $value['UNIQID'],
                                    '物料名称' => $oldData['BOM_物料名称'],
                                ]);
                            $bom_res = \db()->query($bom_sql);

                            if ($bom_res === false) {
                                $this->error('日志写入失败');
                            }
                        }
                    }
                }
                $sql = \db('工单_bom资料')
                    ->where('UNIQID', $value['UNIQID'])
                    ->fetchSql(true)
                    ->update($data);
                $res = \db()->query($sql);
            }else {
                $sql = \db('工单_bom资料')
                    ->fetchSql(true)
                    ->insert($value);
                $res = \db()->query($sql);
            }

            if ($res === false){
                $this->error('修改失败');
            }
        }

        $orderList = \db('工单_基本资料')
            ->field('订单编号, 生产款号, Sys_id')
            ->where('订单编号', $param[0]['BOM_工单编号'])
            ->find();


        if (!$orderList) {
            $this->error('工单基本资料未找到');
        }

        $colorList = \db('工单_印件资料')
            ->field('颜色备注')
            ->where('订单编号', $orderList['订单编号'])
            ->group('颜色备注')
            ->select();

        $BomList = \db('工单_bom资料')
            ->where('BOM_工单编号', $orderList['订单编号'])
            ->whereNull('Mod_rq')
            ->select();

        if (!$BomList) {
            $this->error('BOM 面料数据未找到');
        }

        $AssociatedNumber = \db('工单关联表')->order('关联编号 desc')->value('关联编号');
        $number = empty($AssociatedNumber) ? 0 : (int)substr($AssociatedNumber, 4);

        $MaterielList = [];
        $MaterielLists = [];
        $colorCounter = [];

        foreach ($colorList as $color) {
            $colorName = $color['颜色备注'];
            $colorCounter[$colorName] = 1;

            foreach ($BomList as $bom) {
                $wulbm = $orderList['生产款号'] . '-' . $colorName . $colorCounter[$colorName]++;

                $existsMateriel = \db('工单_面料资料')
                    ->where([
                        'BOM_工单编号' => $orderList['订单编号'],
                        'BOM_颜色' => $colorName,
                        'BOM_物料名称' => $bom['BOM_物料名称']
                    ])
                    ->find();

                if (!$existsMateriel) {
                    $MaterielList[] = [
                        'BOM_工单编号' => $orderList['订单编号'],
                        'BOM_颜色' => $colorName,
                        'BOM_物料名称' => $bom['BOM_物料名称'],
                        'BOM_标准用量' => $bom['BOM_标准用量'],
                        'BOM_计划用量' => $bom['BOM_计划用量'],
                        'BOM_计划门幅' => $bom['BOM_计划门幅'],
                        'BOM_定额门幅' => $bom['BOM_定额门幅'],
                        'BOM_投料单位' => $bom['BOM_投料单位'],
                        'BOM_desc' => $bom['BOM_desc'],
                        'BOM_物料编码' => $wulbm,
                        'Sys_ID' => $bom['Sys_ID'],
                        'Sys_rq' => date('Y-m-d H:i:s')
                    ];
                }

                $existsRelation = \db('工单关联表')
                    ->where([
                        '订单编号' => $orderList['订单编号'],
                        '颜色' => $colorName,
                        '物料名称' => $bom['BOM_物料名称']
                    ])
                    ->find();

                if (!$existsRelation) {
                    $MaterielLists[] = [
                        '关联编号' => 'GDGL' . ($number + 1),
                        '订单编号' => $orderList['订单编号'],
                        '生产款号' => $orderList['生产款号'],
                        '颜色' => $colorName,
                        '物料编号' => $wulbm,
                        '物料名称' => $bom['BOM_物料名称'],
                        '备注' => $bom['BOM_desc'],
                        'Sys_id' => $bom['Sys_ID'],
                        'Sys_rq' => date('Y-m-d H:i:s')
                    ];
                    $number++;
                }
            }
        }
        if (!empty($MaterielList)) {
            \db('工单_面料资料')->insertAll($MaterielList);
        }
        if (!empty($MaterielLists)) {
            \db('工单关联表')->insertAll($MaterielLists);
        }
        $this->success('修改成功');
    }

    /**
     * Bom资料操作日志左侧菜单
     */
    public function OrderBomLog()
    {
        $list = \db('工单_bom修改日志')
            ->field('修改时间')
            ->group('修改时间')
            ->select();

        $result = [];

        foreach ($list as $item) {
            $dateTime = $item['修改时间'];
            $dateOnly = date('Y-m-d', strtotime($dateTime)); // 提取日期
            $year = date('Y', strtotime($dateTime));
            // 加上(int)date('m', strtotime($sys_rq))转成数字去掉前导 01 => 1
            $month = date('m', strtotime($dateTime));

            // 初始化结构
            if (!isset($result[$year])) {
                $result[$year] = [];
            }
            if (!isset($result[$year][$month])) {
                $result[$year][$month] = [];
            }

            // 避免重复添加相同日期
            if (!in_array($dateOnly, $result[$year][$month])) {
                $result[$year][$month][] = $dateOnly;
            }
        }

        // 按年月日进行倒序排序
        foreach ($result as $year => &$months) {
            krsort($months); // 月份倒序
            foreach ($months as &$dates) {
                rsort($dates); // 日期倒序
            }
        }

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

    /**
     * Bom资料操作变更日志记录查询
     */
    public function GetBomEditLog(){
        $param = $this->request->param();
        if (empty($param)){
            $this->error('参数错误');
        }
        $sys_rq = $param['sys_rq'];
        $where= [];
        if (isset($param['search'])) {
            $where['物料名称|订单编号'] = ['like', $param['search'] . '%'];
        }
        // 日期筛选
        if ($sys_rq !== '') {
            $where['修改时间'] = ['like', '%' . $sys_rq . '%'];
        }
        $page = isset($param['page']) ? (int)$param['page'] : 1;
        $limit = isset($param['limit']) ? (int)$param['limit'] : 50;

        $list = \db('工单_bom修改日志')
            ->where($where)
            ->order('修改时间 desc')
            ->limit(($page-1)*$limit,$limit)
            ->select();
        $count = \db('工单_bom修改日志')
            ->where($where)
            ->count();
        $data = [
            'total' => $count,
            'list'  => $list
        ];
        $this->success('成功', $data);
    }

    /**
     * 入库、出库、退还详情数据
     */
    public function FabricDetaillist()
    {
        if ($this->request->isGet() === false){
            $this->error('请求错误');
        }
        $param = $this->request->param();
        $where = [];
        if (isset($param['order']) && !empty($param['order'])){
            $where['a.order_id'] = $param['order'];
        }
        if (isset($param['lotNumber']) && !empty($param['lotNumber'])){
            $where['a.批次号'] = $param['lotNumber'];
        }
        // 定义查询字段
        $fields = '
            a.id,
            a.receipt_number as 单号,
            a.批次号,
            a.order_id as 订单编号,
            a.客户编号,
            a.款号 as 生产款号,
            a.款式,
            a.物料名称,
            c.BOM_颜色 as 颜色,
            d.BOM_计划用量 as 计划用料,
            d.BOM_计划门幅 as 计划门幅,
            d.BOM_标准用量 as 定额用料,
            d.BOM_定额门幅 as 定额门幅,
            b.单位 as 投料单位,
            d.BOM_desc as 备注,
            b.入仓总量 as 入仓总量,
            b.库存数量 as 面料结余,
            a.departname as 来料部门,
            a.rq as 操作时间,
            a.sys_id as 操作人员
        ';
        $list['入库记录'] = \db('库存_出入库明细')
            ->alias('a')
            ->join('物料_库存 b', 'a.批次号 = b.批次号 AND a.物料编码 = b.物料编号', 'left')
            ->join('工单_面料资料 c','a.order_id = c.BOM_工单编号 AND a.物料编码 = c.BOM_物料编码','left')
            ->join('工单_bom资料 d','a.order_id = d.BOM_工单编号 AND a.物料名称 = d.BOM_物料名称','left')
            ->where($where)
            ->where('a.name', '入库')
            ->field($fields)
            ->field('a.number as 入库数量')
            ->order('a.rq desc')
            ->whereNull('a.Mod_rq')
            ->select();

        //出库记录查询
        $list['出库记录'] = \db('库存_出入库明细')->alias('a')
            ->join('物料_库存 b', 'a.批次号 = b.批次号 AND a.物料编码 = b.物料编号', 'left')
            ->join('工单_面料资料 c','a.order_id = c.BOM_工单编号 AND a.物料编码 = c.BOM_物料编码','left')
            ->join('工单_bom资料 d','a.order_id = d.BOM_工单编号 AND a.物料名称 = d.BOM_物料名称','left')
            ->where($where)
            ->where('a.name', '出库')
            ->field($fields)
            ->field('b.领用数量 as 领用数量,a.number as 出库数量')
            ->order('a.rq desc')
            ->whereNull('a.Mod_rq')
            ->select();

        //退还记录查询
        $list['退还记录'] = \db('库存_出入库明细')->alias('a')
            ->join('物料_库存 b', 'a.批次号 = b.批次号 AND a.物料编码 = b.物料编号', 'left')
            ->join('工单_面料资料 c','a.order_id = c.BOM_工单编号 AND a.物料编码 = c.BOM_物料编码','left')
            ->join('工单_bom资料 d','a.order_id = d.BOM_工单编号 AND a.物料名称 = d.BOM_物料名称','left')
            ->where($where)
            ->where('a.name', '退还')
            ->field($fields)
            ->field('a.type as 退还类型,a.number as 退还数量')
            ->order('a.rq desc')
            ->whereNull('a.Mod_rq')
            ->select();

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

    /**
     * 入库、出库、退还删除
     */
    public function FabricDetaildel()
    {
        if ($this->request->isPost() === false) {
            $this->error('请求错误');
        }

        $param = $this->request->param();
        if (empty($param) || !isset($param['id'])) {
            $this->error('请求参数错误');
        }

        $id = $param['id'];
        $where = [
            'Mod_id' => $param['Mod_id'],
            'Mod_rq' => date('Y-m-d H:i:s')
        ];

        // 获取出入库记录
        $res = db('库存_出入库明细')->where('id', $id)->find();
        if (!$res) {
            $this->error('ID: ' . $id . ' 的出入库记录不存在');
        }

        // 获取库存信息
        $res_list = db('物料_库存')->where('批次号', $res['批次号'])->find();
        if (!$res_list) {
            $this->error('批次号 ' . $res['批次号'] . ' 的库存记录不存在');
        }

        $updateData = [];

        // 根据出入库类型处理逻辑
        switch ($res['name']) {
            case '入库':
                $updateData['入仓总量'] = $res_list['入仓总量'] - $res['number'];
                $updateData['库存数量'] = $res_list['库存数量'] - $res['number'];
                break;
            case '出库':
                $updateData['领用数量'] = $res_list['领用数量'] - $res['number'];
                $updateData['库存数量'] = $res_list['库存数量'] + $res['number'];
                break;
            case '退还':
                if ($res['type'] == '退仓') {
                    $updateData['退还数量'] = $res_list['退还数量'] - $res['number'];
                    $updateData['库存数量'] = $res_list['库存数量'] + $res['number'];
                }
                if ($res['type'] == '退客户') {
                    $updateData['退还数量'] = $res_list['退还数量'] - $res['number'];
                    $updateData['库存数量'] = $res_list['库存数量'] + $res['number'];
                }
                break;
            default:
                $this->error('未知的出入库类型: ' . $res['name']);
        }

        // 安全性校验（防止负数）
        foreach ($updateData as $field => $value) {
            if ($value < 0) {
                $this->error('删除失败，' . $field . ' 更新后为负数');
            }
        }

        // 更新库存表
        $updateResult = db('物料_库存')
            ->where('批次号', $res['批次号'])
            ->fetchSql(true)
            ->update($updateData);
        \db()->query($updateResult);

        if ($updateResult === false) {
            throw new \Exception('更新库存表失败');
        }

        // 更新出入库明细表（软删除）
        $deleteResult = db('库存_出入库明细')
            ->where('id', $id)
            ->fetchSql(true)
            ->update($where);
        \db()->query($deleteResult);
        if ($deleteResult === false) {
            throw new \Exception('更新出入库明细表失败');
        }
        $this->success('删除成功');
    }

    public function Gpt($massage)
    {
//        $apiKey = 'sk-e0JuPjMntkbgi1BoMjrqyyzMKzAxILkQzyGMSy3xiMupuoWY';
//        $apiKey = 'sk-Bhos1lXTRpZiAAmN06624a219a874eCd91Dc068b902a3e73';
        $apiKey = 'sk-fxlawqVtbbQbNW0wInR3E4wsLo5JHozDC2XOHzMa711su6ss';
        $messages = [
            [
                'role' => 'user',
                'content' => '你好，帮我按照：面料1、面料2、面料3……来整理归纳下面的面料信息，我只需要面料信息，不需要其他:'.$massage
            ]
        ];
        $data = [
            'model' => 'gpt-4',
            'messages' => $messages,
            'max_tokens' => 1024,
            'temperature' => 0.7,
        ];

        // 初始化 cURL 请求
//        $ch = curl_init('https://niubi.zeabur.app/v1/chat/completions');
//        $ch = curl_init('https://one.opengptgod.com/v1/chat/completions');
        $ch = curl_init('https://chatapi.onechats.top/v1/chat/completions');
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
        curl_setopt($ch, CURLOPT_HTTPHEADER, [
            'Content-Type: application/json',
            'Authorization: Bearer ' . $apiKey,
        ]);
        curl_setopt($ch, CURLOPT_POST, true);
        curl_setopt($ch, CURLOPT_POSTFIELDS, json_encode($data));

        // 跳过证书检查可暂时避免 pem 文件配置失败（测试用）
        curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);

        // 执行请求
        $response = curl_exec($ch);

        // 错误处理
        if (curl_errno($ch)) {
            return '请求失败: ' . curl_error($ch);
        }
        curl_close($ch);

        // 解析响应
        $responseData = json_decode($response, true);

        if (isset($responseData['choices'][0]['message']['content'])) {
            $gptReply = $responseData['choices'][0]['message']['content'];

            // 使用正则提取“面料X：XXX”的内容
            preg_match_all('/面料\d+[:：](.+)/u', $gptReply, $matches);

            return $matches[1] ?? [];
        } else {
            return '未能获取 GPT 回复';
        }
    }

    /**
     * 面料库存月份查询
     */
    public function fabricListmonth()
    {
        $data = \db('库存_出入库明细')
            ->whereNull('Mod_rq')
            ->field('DISTINCT DATE_FORMAT(rq, "%Y-%m") as month, DATE_FORMAT(rq, "%Y-%m-%d") as date')
            ->whereNull('Mod_rq')
            ->order('rq desc')
            ->select();

        // 按月份分组数据
        $groupedData = [];
        foreach ($data as $entry) {
            $groupedData[$entry['month']][] = $entry['date'];
        }

        // 去重处理，防止重复
        foreach ($groupedData as $month => $dates) {
            $groupedData[$month] = array_values(array_unique($dates));
        }

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

    /**
     * 获取每月的面料记录\库存_出入库明细
     * 入库出库退还日期
     */
    public function fetchMonthlyFabricRecords() {
        $types = ['入库', '出库', '退还'];
        $list = [];

        foreach ($types as $type) {
            $data = \db('库存_出入库明细')
                ->where('name', $type)
                ->whereNull('Mod_rq')
                ->field('DISTINCT DATE_FORMAT(rq, "%Y-%m") as month, DATE_FORMAT(rq, "%Y-%m-%d") as date')
                ->order('rq desc')
                ->select();

            // 按照月份分组数据
            $groupedData = [];
            foreach ($data as $entry) {
                $groupedData[$entry['month']][] = $entry['date'];
            }

            // 进一步去重，避免意外重复
            foreach ($groupedData as $month => $dates) {
                $groupedData[$month] = array_values(array_unique($dates));
            }

            // 添加到返回列表
            $list[$type] = $groupedData;
        }

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


    /**
     * 面料库存列表
     */
    public function fabricList()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }

        $param = $this->request->param();
        $where = [];

        // 根据传入的参数构造查询条件
        if (isset($param['order'])) {
            $where['b.BOM_工单编号|a.生产款号|b.BOM_物料名称'] = ['like', $param['order'] . '%'];
        }

        if (isset($param['mouth'])) {
            $where['a.Sys_rq'] = ['like', $param['mouth'] . '%'];
        }

        // 分页参数，防止未传递时出错
        $page = isset($param['page']) ? (int)$param['page'] : 1;  // 默认第1页
        $limit = isset($param['limit']) ? (int)$param['limit'] : 50; // 默认每页50条

        $excludeOrder = $param['order'];

        // 获取其他订单中相同物料名称的订单编号组合
        $subQuery = \db('工单关联表')
            ->field("物料名称, GROUP_CONCAT(DISTINCT 订单编号 SEPARATOR ',') as 关联订单")
            ->where('订单编号', '<>', $excludeOrder)
            ->group('物料名称')
            ->buildSql();

        $data = \db('工单_基本资料')->alias('a')
            ->field('
                b.BOM_工单编号 as 订单编号,
                a.生产款号,
                a.款式,
                b.BOM_颜色 as 颜色,
                b.BOM_物料编码 as 物料编码,
                b.BOM_物料名称 as 物料名称,
                b.Sys_ID,
                b.Sys_rq,
                c.单位,
                c.入仓总量,
                c.库存数量,
                c.领用数量,
                d.BOM_desc as 备注,
                d.BOM_标准用量 as 定额用料, 
                d.BOM_定额门幅 as 定额门幅,
                e.批次号,f.关联订单
            ')
            ->join('工单_面料资料 b', 'a.订单编号 = b.BOM_工单编号')
            ->join('库存_出入库明细 e', 'a.订单编号 = e.order_id')
            ->join('物料_库存 c', 'b.BOM_物料编码 = c.物料编号 AND b.BOM_物料名称 = c.物料名称 AND e.批次号 = c.批次号')
            ->join('工单_bom资料 d', 'a.订单编号 = d.BOM_工单编号 AND b.BOM_物料名称 = d.BOM_物料名称')
            ->join([$subQuery => 'f'], 'f.物料名称 = d.BOM_物料名称','LEFT')
            ->whereNull('a.Mod_rq')
            ->whereNull('b.Mod_rq')
            ->whereNull('c.Mod_rq')
            ->whereNull('d.Mod_rq')
            ->whereNull('e.Mod_rq')
            ->group('e.批次号, b.BOM_物料编码')
            ->where($where)
            ->select();

        // 统计条数进行分页
        $count = \db('工单_面料资料')->where('BOM_工单编号',$param['order'])->count();
        // 确保返回的格式固定
        $list = [
            'total' => $count,
            'table' => $data ?: []
        ];

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


    /**
     * 单条面料详情
     */
    public function oneFabricDetail()
    {
        if ($this->request->isGet() === false){
            $this->error('请求错误');
        }
        $param = $this->request->param();
        if (empty($param)){
            $this->error('参数错误');
        }
        //面料入库记录
        $list['入库'] = \db('库存_出入库明细')
            ->where('',$param['order'])
            ->where('物料名称',$param['fabricName'])
            ->where('name','入库')
            ->where('Mod_rq',null)
            ->field('order_id as 订单编号,款号,物料编码,物料名称,number as 数量,rq as 日期,sys_id as 操作机台,recipient as 入仓人员')
            ->select();
        //面料出库记录
        $list['出库'] = \db('库存_出入库明细')
            ->where('order_id',$param['order'])
            ->where('物料名称',$param['fabricName'])
            ->where('name','出库')
            ->where('Mod_rq',null)
            ->field('order_id as 订单编号,款号,物料名称,number as 数量,rq as 日期,sys_id as 操作机台,receipt_number as 出库单据编号,recipient as 领用人员')
            ->select();
        //面料退还记录
        $list['退还'] = \db('库存_出入库明细')
            ->where('order_id',$param['order'])
            ->where('物料名称',$param['fabricName'])
            ->where('name','退还')
            ->where('Mod_rq',null)
            ->field('order_id as 订单编号,款号,物料名称,number as 数量,rq as 日期,sys_id as 操作机台,recipient as 退还人员')
            ->select();
        $this->success('成功',$list);
    }

    /**
     * 单据号查询数据
     * @return void
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function ReceiptDetail()
    {
        if ($this->request->isGet() === false){
            $this->error('请求错误');
        }
        $param = $this->request->param();
        if (empty($param)){
            $this->error('单据编号参数错误');
        }
        $list = \db('库存_出入库明细')
            ->alias('a')
            ->join('工单_面料资料 b','a.order_id = b.BOM_工单编号 AND b.BOM_物料编码 = a.物料编码')
            ->join('物料_库存 c','a.物料编码 = c.物料编号 AND a.批次号 = c.批次号')
            ->where('a.receipt_number',$param['receipt'])
            ->field('a.id,a.order_id as 订单编号,a.款号,a.物料名称,a.number as 数量,a.rq as 日期,a.sys_rq as 创建日期,a.sys_id as 操作机台,
                a.departname,a.remark,a.type as 退还类型,
                a.receipt_number as 出库单据编号,a.recipient as 领用人员,b.BOM_颜色,c.单位,
                b.BOM_计划用量 as 计划用料,
                b.BOM_计划门幅 as 计划门幅,
                b.BOM_标准用量 as 定额用料,
                b.BOM_定额门幅 as 定额门幅,
                c.实际门幅,c.状态,c.库存数量,c.入仓总量
            ')
            ->whereNull('a.Mod_rq')
            ->whereNull('b.Mod_rq')
            ->whereNull('c.Mod_rq')
            ->select();
        if (empty($list)){
            $this->error('未找到该出库单');
        }else{
            $this->success('成功',$list);
        }
    }

    /**
     * 入库、出库、退还单号列表
     */
    public function ReceiptList(){
        if ($this->request->isGet() === false){
            $this->error('请求错误');
        }
        $param = $this->request->param();
        if (empty($param)){
            $this->error('参数错误');
        }
        $where= [];
        if (isset($param['search'])) {
            $where['物料名称|款号|order_id|receipt_number|款式|物料编码'] = ['like', $param['search'] . '%'];
        }
        if (isset($param['mouth'])) {
            $where['rq'] = ['like', $param['mouth'] . '%'];
        }
        $page = isset($param['page']) ? (int)$param['page'] : 1;  // 默认第1页
        $limit = isset($param['limit']) ? (int)$param['limit'] : 50; // 默认每页50条

        $list = \db('库存_出入库明细')
            ->where('Mod_rq', null)
            ->where($where)
            ->where('name',$param['code'])
            ->order('sys_rq desc')
            ->limit(($page-1)*$limit,$limit)
            ->select();
        // 初始化一个合并后的数组
        $merged = [];
        foreach ($list as $item) {
            $key = $item['receipt_number'];
            if (!isset($merged[$key])) {
                // 首次出现，初始化
                $merged[$key] = [
                    '单号类型' => $item['name'],
                    '出库单' => $item['receipt_number'],
                    '订单编号' => $item['order_id'],
                    '款号' => $item['款号'],
                    '款式' => $item['款式'],
                    '物料编码' => $item['物料编码'],
                    '物料名称' => $item['物料名称'],
                    '总数' => floatval($item['number']),
                    '日期' => $item['rq'],
                    '创建日期' => $item['sys_rq'],
                    '操作机台' => $item['sys_id'],
                    '领料人员' => $item['recipient'],
                ];
            } else {
                // 合并相同 receipt_number 的记录
                $merged[$key]['订单编号'] .= ',' . $item['order_id'];
                $merged[$key]['款号'] .= ',' . $item['款号'];
                $merged[$key]['款式'] .= ',' . $item['款式'];
                $merged[$key]['物料编码'] .= ',' . $item['物料编码'];
                $merged[$key]['物料名称'] .= ',' . $item['物料名称'];
                $merged[$key]['总数'] += floatval($item['number']);
            }
        }

        // 对需要的字段进行逗号分隔后的去重
        foreach ($merged as &$item) {
            $item['订单编号'] = implode(',', array_unique(explode(',', $item['订单编号'])));
            $item['款号'] = implode(',', array_unique(explode(',', $item['款号'])));
            $item['款式'] = implode(',', array_unique(explode(',', $item['款式'])));
            $item['物料编码'] = implode(',', array_unique(explode(',', $item['物料编码'])));
            $item['物料名称'] = implode(',', array_unique(explode(',', $item['物料名称'])));
            // 格式化总数
            $item['总数'] = round($item['总数'], 2); // 保留2位小数，防止长尾
        }
        unset($item); // 避免引用问题

        //统计数量
        $count = \db('库存_出入库明细')
            ->where($where)
            ->where('name',$param['code'])
            ->field('receipt_number as 出库单')
            ->group('出库单')
            ->where('Mod_rq',null)
            ->order('rq desc')
            ->select();

        if (empty($merged)){
            $this->success('未找到数据', '');
        } else {
            $data['total'] = count($count);
            $data['table'] = array_values($merged);
            $this->success('成功', $data);
        }

    }

    /**
     * 出库单左侧菜单
     */
    public function getReceiptTab()
    {
        if ($this->request->isGet() === false){
            $this->error('请求错误');
        }
        $list = \db('库存_出入库明细')
            ->field([
                "DATE_FORMAT(rq, '%Y-%m') AS month",
            ])
            ->group('month')
            ->order('month DESC')
            ->select();
        if (empty($list)){
            $this->error('未查询到入库、出库、退还数据');
        }else{
            $this->success('成功',$list);
        }
    }

    /**
     * 获取入库单号、出库单号
     * @return void
     */
    public function gitReceiptNumber()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }
        $param = $this->request->param();
        if (empty($param)) {
            $this->error('参数错误');
        }

        // 获取最新单号（按日期和序号排序）
        $lastNumber = \db('库存_出入库明细')
            ->where('receipt_number', 'like', $param['number'] . date('Ymd') . '-%')
            ->order('receipt_number desc')
            ->whereNull('Mod_rq')
            ->limit(1)
            ->value('receipt_number');

        if (empty($lastNumber)) {
            $num = 1;
        } else {
            // 提取最后3位并转为整数
            $lastSeq = substr($lastNumber, -3);
            $num = (int)$lastSeq + 1;
        }

        // 格式化为3位数字（001, 010, 100, 101...）
        $seq = str_pad($num, 3, '0', STR_PAD_LEFT);

        // 生成完整单号（如 BG20240615-101）
        $number = $param['number'] . date('Ymd') . '-' . $seq;

        // 获取最新出库人员
        $lastUser = \db('库存_出入库明细')
            ->where('name', '出库')
            ->order('recipient desc')
            ->group('recipient')
            ->whereNull('Mod_rq')
            ->limit(1)
            ->value('recipient');

        $data = [
            'number' => $number,
            'username' => $lastUser
        ];
        $this->success('成功', $data);
    }


    /**
     * 面料批次列表
     */
    public function FabricLotList()
    {
        if ($this->request->isGet() === false){
            $this->error('请求错误');
        }
        $param = $this->request->param();
        if (empty($param)){
            $this->error('参数错误');
        }
        $where= [];
        if (isset($param['search'])) {
            $where['c.物料编号|c.物料名称|a.生产款号|a.订单编号|b.BOM_颜色'] = ['like', '%' . $param['search'] . '%'];
        }
        if (isset($param['date'])) {
            $where['e.rq'] = ['like', $param['date'] . '%'];
        }

        $subQuery = \db('工单关联表')
            ->field("物料名称, GROUP_CONCAT(DISTINCT 订单编号 SEPARATOR ',') as 关联订单")
            ->group('物料名称')
            ->buildSql();

        $list = \db('工单_基本资料')->alias('a')
            ->field('
                c.批次号,
                c.关联号,
                c.物料编号,
                c.物料名称,
                c.入仓总量,
                c.库存数量,
                c.领用数量,
                c.退还数量 as 裁切退还,
                c.单位,
                c.实际门幅,
                c.状态,
                e.sys_id as 入仓人员,
                e.sys_rq as 入仓日期,
                b.BOM_颜色 as 颜色,
                a.生产款号 as 款号,
                d.BOM_计划用量 as 计划用料,
                d.BOM_标准用量 as 定额用料,
                d.BOM_计划门幅 as 计划门幅,
                d.BOM_定额门幅 as 定额门幅,
                f.关联订单
            ')
            ->join('工单_面料资料 b', 'a.订单编号 = b.BOM_工单编号')
            ->join('库存_出入库明细 e', 'a.订单编号 = e.order_id')
            ->join('物料_库存 c', 'b.BOM_物料编码 = c.物料编号 AND b.BOM_物料名称 = c.物料名称 AND e.批次号 = c.批次号')
            ->join('工单_bom资料 d', 'a.订单编号 = d.BOM_工单编号 AND b.BOM_物料名称 = d.BOM_物料名称')
            ->join([$subQuery => 'f'], 'f.物料名称 = d.BOM_物料名称')
            ->whereNull('a.Mod_rq')
//            ->whereNull('b.Mod_rq')
            ->whereNull('c.Mod_rq')
//            ->whereNull('d.Mod_rq')
            ->whereNull('e.Mod_rq')
            ->group('c.物料编号')
            ->where($where)
            ->select();


        if (empty($list)){
            $this->error('未找到面料数据');
        }else{
            $this->success('成功',$list);
        }
    }


    public function SubOrderProgress()
    {
        if (!$this->request->isGet()) {
            $this->error('请求错误');
        }

        $params = $this->request->param();

        if (empty($params['order'])) {
            $this->error('缺少订单编号');
        }

        $orderNo = $params['order'];

        // ▶ 获取该主订单下的全部子订单（印件资料为主）
        $styleData = Db::name('工单_印件资料')
            ->where('订单编号', $orderNo)
            ->field('子订单编号, 款号, 颜色, 颜色备注')
            ->whereNull('Mod_rq')
            ->select();

        if (empty($styleData)) {
            $this->success('未找到子订单资料', ['result' => [], 'list' => []]);
        }

        $subOrderNos = array_column($styleData, '子订单编号');

        // 子订单 → 款号/颜色
        $styleMap = [];
        foreach ($styleData as $row) {
            $styleMap[$row['子订单编号']] = [
                '款号'     => $row['款号'],
                '颜色'     => $row['颜色'],
                '颜色备注' => $row['颜色备注'],
            ];
        }

        // ▶ 入仓数量统计
        $warehouseData = Db::name('成品入仓')
            ->alias('a')
            ->field('a.order_id as 子订单编号, SUM(a.sl) as 入仓数量')
            ->where('a.order', $orderNo)
            ->whereNull('a.mod_rq')
            ->group('a.order_id')
            ->select();

        $warehouseMap = [];
        foreach ($warehouseData as $row) {
            $warehouseMap[$row['子订单编号']] = (int)$row['入仓数量'];
        }

        // ▶ 裁剪数量统计
        $prodData = Db::name('设备_产量计酬')
            ->field('子订单编号, SUM(数量) as 裁剪数量')
            ->whereIn('子订单编号', $subOrderNos)
            ->where('工序名称', '裁剪')
            ->group('子订单编号')
            ->select();

        $cutMap = [];
        foreach ($prodData as $row) {
            $cutMap[$row['子订单编号']] = (int)$row['裁剪数量'];
        }

        // ▶ 合并结果（子订单明细）
        $result = [];
        $totalCut = 0;
        $totalIn = 0;

        foreach ($subOrderNos as $subOrderNo) {
            $inQty  = $warehouseMap[$subOrderNo] ?? 0;
            $cutQty = $cutMap[$subOrderNo] ?? 0;

            $rate = $cutQty > 0 ? round($inQty / $cutQty * 100, 2) . '%' : '0%';

            $styleInfo = $styleMap[$subOrderNo] ?? ['款号'=>'','颜色'=>'','颜色备注'=>''];

            $result[] = [
                '子订单编号' => $subOrderNo,
                '生产款号'   => $styleInfo['款号'],
                '颜色'       => $styleInfo['颜色'],
                '颜色备注'   => $styleInfo['颜色备注'],
                '裁剪数量'   => $cutQty,
                '入仓数量'   => $inQty,
                '完成率'     => $rate,
            ];

            $totalCut += $cutQty;
            $totalIn  += $inQty;
        }

        // ▶ 汇总 list 字段（整张订单）
        $list = [[
            '订单编号'     => $orderNo,
            '订单总裁剪数量'   => $totalCut,
            '订单总入仓数量'   => $totalIn,
            '订单完成率'   => $totalCut > 0 ? round($totalIn / $totalCut * 100, 2) . '%' : '0%'
        ]];

        // ▶ 返回数据
        $this->success('请求成功', [
            'result' => $result,
            'list'   => $list
        ]);
    }

    /**
     * 仓库管理
     * 入库、出库、退还更新
     */
    public function rApictedit()
    {
        if (!$this->request->isPost()) {
            $this->error('请求错误');
        }

        $param = Request::instance()->post();

        if (empty($param)) {
            $this->error('参数错误');
        }

        $logData = []; // 日志记录数组
        $now = date('Y-m-d H:i:s');

        foreach ($param as $item) {
            $id = $item['id'];
            $newQty = floatval($item['数量'] ?? 0);
            $name = $item['name'];
            $returnType = $item['退还类型'];
            $sysId = $item['sys_id'];
            $rq = $item['rq'];
            $remark = $item['remark'];

            if (!$id || !$name) {
                $this->error('缺少必要参数');
            }

            // 获取库存_出入库明细 原记录
            $record = \db('库存_出入库明细')
                ->field('id, 批次号, number, type')
                ->where('name', $name)
                ->whereNull('Mod_rq')
                ->where('id', $id)
                ->find();

            if (!$record) {
                $this->error("未找到 ID 为 {$id} 的记录");
            }

            $batchNo = $record['批次号'];
            $oldQty = floatval($record['number']);
            $diff = $newQty - $oldQty;

            //修改库存_出入库明细
            $updateSql = \db('库存_出入库明细')
                ->where('id', $id)
                ->where('name', $name)
                ->whereNull('Mod_rq')
                ->fetchSql(true)
                ->update(
                    [
                        'number' => $newQty,
                        'remark' => $remark,
                        'rq' => $rq
                    ]
                );

            \db()->query($updateSql); // 执行 SQL

            // 写入日志，增加 ids 字段
            $logData[] = [
                '批次号' => $batchNo,
                '操作字段' => 'number',
                '原值' => $oldQty,
                '新值' => $newQty,
                '操作人' => $sysId,
                '操作类型' => $name,
                '操作时间' => $now,
                '修改表' => '库存_出入库明细',
                'ids' => $id
            ];

            // === 获取同批次的记录以统计新合计 ===
            $sameBatchList = \db('库存_出入库明细')
                ->field('number')
                ->where('name', $name)
                ->whereNull('Mod_rq')
                ->where('批次号', $batchNo)
                ->select();


            $newTotal = array_sum(array_column($sameBatchList, 'number'));

            // === 获取物料_库存信息（加上 id 字段） ===
            $inventory = \db('物料_库存')
                ->field('id, 入仓总量, 领用数量, 库存数量, 退还数量')
                ->where('批次号', $batchNo)
                ->find();

            if ($inventory) {
                $stockId = $inventory['id']; // 记录当前库存表的 id
                $inQty = floatval($inventory['入仓总量']);
                $stockQty = floatval($inventory['库存数量']);
                $useQty = floatval($inventory['领用数量']);
                $returnQty = floatval($inventory['退还数量']);

                if ($name == '入库') {
                    $newIn = $inQty + $diff;
                    $newStock = $stockQty + $diff;

                    $updateFields = [];

                    if ($newIn != $inQty) {
                        $updateFields['入仓总量'] = $newIn;

                        $logData[] = [
                            '批次号' => $batchNo,
                            '操作字段' => '入仓总量',
                            '原值' => $inQty,
                            '新值' => $newIn,
                            '操作人' => $sysId,
                            '操作类型' => '入库',
                            '操作时间' => $now,
                            '修改表' => '物料_库存',
                            'ids' => $stockId
                        ];
                    }

                    if ($newStock != $stockQty) {
                        $updateFields['库存数量'] = $newStock;

                        $logData[] = [
                            '批次号' => $batchNo,
                            '操作字段' => '库存数量',
                            '原值' => $stockQty,
                            '新值' => $newStock,
                            '操作人' => $sysId,
                            '操作类型' => '入库',
                            '操作时间' => $now,
                            '修改表' => '物料_库存',
                            'ids' => $stockId
                        ];
                    }

                    if (!empty($updateFields)) {
                        $updateStockSql = \db('物料_库存')
                            ->where('id', $stockId)
                            ->fetchSql(true)
                            ->update($updateFields);

                        \db()->query($updateStockSql);
                    }
                }elseif ($name == '出库') {
                    if ($newTotal != $useQty) {

                        // 计算新的库存数量
                        $newStock = $inQty - $newTotal - $returnQty;

                        $updateFields = [
                            '领用数量' => $newTotal,
                            '库存数量' => $newStock
                        ];

                        $updateStockSql = \db('物料_库存')
                            ->where('id', $stockId)
                            ->fetchSql(true)
                            ->update($updateFields);

                        \db()->query($updateStockSql);

                        $logData[] = [
                            '批次号' => $batchNo,
                            '操作字段' => '领用数量',
                            '原值' => $useQty,
                            '新值' => $newTotal,
                            '操作人' => $sysId,
                            '操作类型' => '出库',
                            '操作时间' => $now,
                            '修改表' => '物料_库存',
                            'ids' => $stockId
                        ];

                        $logData[] = [
                            '批次号' => $batchNo,
                            '操作字段' => '库存数量',
                            '原值' => $stockQty,
                            '新值' => $newStock,
                            '操作人' => $sysId,
                            '操作类型' => '出库',
                            '操作时间' => $now,
                            '修改表' => '物料_库存',
                            'ids' => $stockId
                        ];
                    }
                }elseif ($name == '退还') {
                    if ($returnType == '退面料' && $newTotal != $returnQty) {
                        $newStock = $inQty - $useQty - $newTotal;

                        $updateFields = [
                            '退还数量' => $newTotal,
                            '库存数量' => $newStock
                        ];

                        $updateStockSql = \db('物料_库存')
                            ->where('id', $stockId)
                            ->fetchSql(true)
                            ->update($updateFields);

                        \db()->query($updateStockSql);

                        $logData[] = [
                            '批次号' => $batchNo,
                            '操作字段' => '退还数量',
                            '原值' => $returnQty,
                            '新值' => $newTotal,
                            '操作人' => $sysId,
                            '操作类型' => '退还',
                            '操作时间' => $now,
                            '修改表' => '物料_库存',
                            'ids' => $stockId
                        ];

                        // 添加库存数量变更日志
                        $logData[] = [
                            '批次号' => $batchNo,
                            '操作字段' => '库存数量',
                            '原值' => $stockQty,
                            '新值' => $newStock,
                            '操作人' => $sysId,
                            '操作类型' => '退还',
                            '操作时间' => $now,
                            '修改表' => '物料_库存',
                            'ids' => $stockId
                        ];
                    }
                }
            }
        }

        // 批量写入日志
        if (!empty($logData)) {
            \db('物料_库存日志')->insertAll($logData);
        }

        $this->success('更新成功');
    }

    /**
     * 成品入仓合格率左侧菜单
     */
    public function GetWfpDataLeft()
    {


//        $countData = Db::name('设备_产量计酬')->alias('a')
//            ->field('COUNT(DISTINCT a.订单编号) as total_count,
//                 DATE_FORMAT(a.sys_rq, "%Y%m") as 年月,
//                 b.客户编号')
//            ->where('工序编号',6)
//            ->join('工单_基本资料 b', 'a.订单编号 = b.订单编号', 'left')
//            ->group('DATE_FORMAT(b.Sys_rq, "%Y%m"), b.客户编号')
//            ->whereNull('a.mod_rq')
//            ->whereNull('b.Mod_rq')
//            ->select();

        // 统计每个客户每月的去重订单数
        $countData = Db::name('成品入仓')
            ->alias('a')
            ->field('COUNT(DISTINCT a.order) as total_count,
                 DATE_FORMAT(a.sys_rq, "%Y%m") as 年月,
                 b.客户编号')
            ->join('工单_基本资料 b', 'a.order = b.订单编号', 'left')
            ->group('DATE_FORMAT(a.sys_rq, "%Y%m"), b.客户编号')
            ->whereNull('a.mod_rq')
            ->whereNull('b.Mod_rq')
            ->select();

        // 整理结果：按年月分组
        $result = [];
        foreach ($countData as $item) {
            $yearMonth = $item['年月'];
            if (!isset($result[$yearMonth])) {
                $result[$yearMonth] = [];
            }

            $result[$yearMonth][] = [
                '客户编号' => $item['客户编号'],
                'total'   => '订单数：'.(int)$item['total_count']
            ];
        }
        krsort($result);
        $this->success('请求成功', $result);
    }

    /**
        成品入仓订单列表
     */
    public function GetWfpList()
    {
        if (!$this->request->isGet()) {
            $this->error('请求失败');
        }
        $params = $this->request->get();
        // 判断是否有日期参数
        $hasDate = !empty($params['sys_rq']);
        // 时间范围处理
        if ($hasDate) {
            $startDate = date('Y-m-01', strtotime($params['sys_rq'] . '01'));
            $endDate = date('Y-m-d', strtotime("$startDate +1 month"));
        }

        // 获取订单列表（支持搜索）
        $gdQuery = Db::name('工单_基本资料')
            ->field('订单编号, 生产款号, 款式, 订单数量, 客户编号')
            ->whereNull('mod_rq');
        if (!empty($params['search'])) {
            $gdQuery->where('订单编号|生产款号|款式', 'like', '%' . $params['search'] . '%');
        }
        $gdlist = $gdQuery->select();
        if (empty($gdlist)) {
            $this->success('无匹配订单', ['result' => []]);
        }
        // 提取订单编号列表
        $filteredOrderNos = array_column($gdlist, '订单编号');

        $where = [];
        if (!empty($params['customer'])) {
            $where['a.customer'] = $params['customer'];
        }

        // 入仓数据查询
        // 确保参数存在且格式正确
        $yearMonth = !empty($params['sys_rq']) ? $params['sys_rq'] : '';

        // 构建查询
        $warehouseQuery = Db::name('成品入仓')
            ->alias('a')
            ->field('a.order as 订单编号, SUM(a.sl) as 入仓数量,a.sys_rq,a.customer')
            ->where($where)
            ->whereIn('a.order', $filteredOrderNos)
            ->whereNull('a.mod_rq');

        // 只当有有效年月参数时添加日期筛选
        if ($yearMonth && strlen($yearMonth) == 6) {
            // 使用字符串拼接的方式确保SQL函数正确执行
            $warehouseQuery->where("DATE_FORMAT(a.sys_rq, '%Y%m') = '{$yearMonth}'");
        }
        $warehouseData = $warehouseQuery->group('a.order')->select();


        // 提取入仓涉及的订单编号
        $orderNos = array_column($warehouseData, '订单编号');
        if (empty($orderNos)) {
            $this->success('无数据', ['result' => []]);
        }

        // 入仓 map
        $warehouseMap = [];
        foreach ($warehouseData as $row) {
            $warehouseMap[$row['订单编号']] = (int)$row['入仓数量'];
        }

        // 累计入仓处理
        $cumulativeMap = [];
        if ($hasDate) {
            // 有日期时，累计入仓是所有时间的总和
            $cumulativeData = Db::name('成品入仓')
                ->alias('a')
                ->field('a.order as 订单编号, SUM(a.sl) as 累计入仓数量')
                ->where($where)
                ->whereIn('a.order', $orderNos)
                ->whereNull('a.mod_rq')
                ->group('a.order')
                ->select();

            foreach ($cumulativeData as $row) {
                $cumulativeMap[$row['订单编号']] = (int)$row['累计入仓数量'];
            }
        } else {
            // 没有日期时，累计入仓就是当前入仓数量
            foreach ($warehouseData as $row) {
                $cumulativeMap[$row['订单编号']] = (int)$row['入仓数量'];
            }
        }

        // 裁剪产量
        $prodRecords = Db::name('设备_产量计酬')
            ->whereIn('订单编号', $orderNos)
            ->where('工序名称', '裁剪')
            ->select();

        $cutMap = [];
        foreach ($prodRecords as $record) {
            $orderNo = $record['订单编号'];
            $styleCode = $record['款号'] ?? '';
            $qty = (int)$record['数量'];

            if (!isset($cutMap[$orderNo])) {
                $cutMap[$orderNo] = [
                    '生产款号' => $styleCode,
                    '裁剪总产量' => 0
                ];
            }
            $cutMap[$orderNo]['裁剪总产量'] += $qty;
        }

        // 质量数据（返工/次片）
        $qualityData = Db::name('设备_质量汇总')
            ->field('订单编号, 状态, SUM(数量) as 数量')
            ->whereIn('订单编号', $orderNos)
            ->group('订单编号, 状态')
            ->select();

        $qualityMap = [];
        foreach ($qualityData as $item) {
            $orderNo = $item['订单编号'];
            $status = $item['状态'];
            $qty = (int)$item['数量'];

            if (!isset($qualityMap[$orderNo])) {
                $qualityMap[$orderNo] = [
                    '返工总数' => 0,
                    '次片总数' => 0
                ];
            }

            if ($status === '返工') {
                $qualityMap[$orderNo]['返工总数'] += $qty;
            } elseif ($status === '次片') {
                $qualityMap[$orderNo]['次片总数'] += $qty;
            }
        }

        // 获取订单信息映射
        $orderInfoList = Db::name('工单_基本资料')
            ->field('订单编号, 款式, 订单数量, 客户编号')
            ->whereIn('订单编号', $orderNos)
            ->select();

        $orderInfoMap = [];
        foreach ($orderInfoList as $orderInfo) {
            $orderNo = $orderInfo['订单编号'];
            $orderInfoMap[$orderNo] = [
                '款式' => $orderInfo['款式'] ?? '',
                '订单数量' => $orderInfo['订单数量'] ?? 0,
                '客户编号' => $orderInfo['客户编号'] ?? ''
            ];
        }

        // 汇总数据
        $result = [];
        foreach ($orderNos as $orderNo) {
            $cutQty        = $cutMap[$orderNo]['裁剪总产量'] ?? 0;
            $warehouseQty  = $warehouseMap[$orderNo] ?? 0;
            $cumulativeQty = $cumulativeMap[$orderNo] ?? 0;
            $styleCode     = $cutMap[$orderNo]['生产款号'] ?? '';

            $orderInfo = $orderInfoMap[$orderNo] ?? [];
            $khName = $orderInfo['客户编号'] ?? '';
            $styleName = $orderInfo['款式'] ?? '';
            $orderQty  = $orderInfo['订单数量'] ?? 0;

            $actualPassRate = $cutQty > 0 ? round($cumulativeQty / $cutQty * 100, 2) . '%' : '0%';

            $reworkQty = $qualityMap[$orderNo]['返工总数'] ?? 0;
            $secondQty = $qualityMap[$orderNo]['次片总数'] ?? 0;

            $result[] = [
                '订单编号'       => $orderNo,
                '生产款号'       => $styleCode,
                '客户编号'       => $khName,
                '款式'           => $styleName,
                '订单数量'       => $orderQty,
                '裁剪总产量'     => $cutQty,
                '入仓数量'       => $warehouseQty,
                '累计入仓数量'   => $cumulativeQty,
                '裁剪合格率'     => $actualPassRate,
                '返工总数'       => $reworkQty,
                '次片总数'       => $secondQty,
                '统计类型'       => $hasDate ? '月度统计' : '总计'
            ];
        }

        $this->success('请求成功', ['result' => $result]);
    }

    /**
    * 成品入仓子订单列表
    */
    public function GetSubOrderStats()
    {
        if (!$this->request->isGet()) {
            $this->error('请求失败');
        }
        $params = $this->request->get();
        $startDate = date('Y-m-01', strtotime($params['sys_rq'] . '01'));
        $endDate   = date('Y-m-d', strtotime("$startDate +1 month"));

        //获取该订单的所有子订单
        $styleData = Db::name('工单_印件资料')
            ->where('订单编号', $params['order'])
            ->field('子订单编号, 款号, 颜色备注, 颜色,zdtotal')
            ->whereNull('Mod_rq')
            ->select();

        $subOrderNos = array_column($styleData, '子订单编号');

        if (empty($subOrderNos)) {
            $this->success('未找到该订单下的子订单资料', ['result' => [], 'list' => []]);
        }

        $styleMap = [];
        foreach ($styleData as $row) {
            $styleMap[$row['子订单编号']] = [
                'zdtotal'     => $row['zdtotal'],
                '款号'     => $row['款号'],
                '颜色'     => $row['颜色'],
                '颜色备注' => $row['颜色备注']
            ];
        }

        // 子订单入仓数据（当前月）
        $warehouseData = Db::name('设备_产量计酬')
            ->field('子订单编号, SUM(s_num) as 入仓数量')
//            ->where('customer', $params['customer'])
            ->where('订单编号', $params['order'])
            ->whereNull('mod_rq')
            ->where('工序名称', '总检')
            ->whereBetween('sys_rq', [$startDate, $endDate])
            ->group('子订单编号')
            ->select();

        $warehouseMap = [];
        foreach ($warehouseData as $row) {
            $warehouseMap[$row['子订单编号']] = (int)$row['入仓数量'];
        }

        //累计入仓（不限时间）
        $cumulativeData = Db::name('设备_产量计酬')
            ->field('子订单编号, SUM(s_num) as 累计入仓数量')
//            ->where('customer', $params['customer'])
            ->where('子订单编号', $params['order'])
            ->where('工序名称', '总检')
            ->whereNull('mod_rq')
            ->group('子订单编号')
            ->select();

        $cumulativeMap = [];
        foreach ($cumulativeData as $row) {
            $cumulativeMap[$row['子订单编号']] = (int)$row['累计入仓数量'];
        }

        // 裁剪产量（不限时间）
        $prodRecords = Db::name('设备_产量计酬')
            ->whereIn('子订单编号', $subOrderNos)
            ->where('工序名称', '裁剪')
            ->select();

        $cutMap = [];
        foreach ($prodRecords as $row) {
            $subOrder = $row['子订单编号'];
            $qty = (int)$row['数量'];

            if (!isset($cutMap[$subOrder])) {
                $cutMap[$subOrder] = 0;
            }
            $cutMap[$subOrder] += $qty;
        }

        $result = [];
        $totalCut = 0;
        $totalIn = 0;
        $orderNo = $params['order'];

        foreach ($subOrderNos as $subOrderNo) {
            $inQty        = $warehouseMap[$subOrderNo] ?? 0;
            $totalInQty   = $cumulativeMap[$subOrderNo] ?? 0;
            $cutQty       = $cutMap[$subOrderNo] ?? 0;
            $styleInfo    = $styleMap[$subOrderNo] ?? ['zdtotal' => '','款号' => '', '颜色' => '', '颜色备注' => ''];

            $monthlyRate  = $cutQty > 0 ? round($inQty / $cutQty * 100, 2) . '%' : '0%';
            $cumulativeRate = $cutQty > 0 ? round($totalInQty / $cutQty * 100, 2) . '%' : '0%';

            $result[] = [
                '订单编号'       => $orderNo,
                '子订单编号'     => $subOrderNo,
                '生产款号'       => $styleInfo['款号'],
                '颜色'           => $styleInfo['颜色'],
                'zdtotal'       => $styleInfo['zdtotal'],
                '颜色备注'       => $styleInfo['颜色备注'],
                '裁剪数量'       => $cutQty,
                '入仓数量'       => $inQty,
                '累计入仓数量'   => $totalInQty,
                '月度合格率'     => $monthlyRate,
                '实际合格率'     => $cumulativeRate
            ];

            $totalCut += $cutQty;
            $totalIn  += $totalInQty;
        }

        $list = [[
            '订单编号'         => $orderNo,
            '订单累计裁剪数量' => $totalCut,
            '订单累计入仓数量' => $totalIn,
            '订单合格率'       => $totalCut > 0 ? round($totalIn / $totalCut * 100, 2) . '%' : '0%'
        ]];

        $this->success('请求成功', [
            'list'   => $list,
            'result' => $result
        ]);
    }


    /**
     * 电表管理
     */
    private function httpGetRequest($url, $params = [])
    {
        if (!empty($params)) {
            $url .= (strpos($url, '?') === false ? '?' : '&') . http_build_query($params);
        }

        $ch = curl_init();

        curl_setopt($ch, CURLOPT_URL, $url);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
        curl_setopt($ch, CURLOPT_TIMEOUT, 30);
        curl_setopt($ch, CURLOPT_HTTPHEADER, [
            'Content-Type: application/json',
        ]);

        $response = curl_exec($ch);
        $httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);

        if (curl_errno($ch)) {
            $errorMsg = curl_error($ch);
            curl_close($ch);
            throw new Exception('cURL错误: ' . $errorMsg);
        }

        curl_close($ch);

        if ($httpCode !== 200) {
            throw new Exception('API请求失败，HTTP状态码: ' . $httpCode);
        }

        $resultData = json_decode($response, true);

        if (json_last_error() !== JSON_ERROR_NONE) {
            throw new Exception('JSON解析错误: ' . json_last_error_msg());
        }

        return $resultData;
    }

    /**
     * 接口api说明：设备数据/设备状态
     * 接口说明：查询设备列表和当前状态-左侧菜单
     */
    public function Meter()
    {
        $res = Db::name('电表设备列表')->select();
        $this->success('请求成功', $res);
    }

    /**
     * 查询电表能耗数据日数据
     */
    public function StatisticEleDay()
    {
        if (!$this->request->isGet()) {
            $this->error('非法请求');
        }
        $params = $this->request->get();
        // 检查 mid 参数是否为空
        if (empty($params['mid'])) {
            $this->error('mid参数不能为空');
        }

        $mid = (int)$params['mid'];
        $riqi = $params['riqi'] ?? date('Ym');

        $where = [];
        $where['电表ID'] = $mid;

        // 通过日期查询
        if (!empty($params['riqi'])) {
            if (preg_match('/^\d{6}$/', $params['riqi'])) {
                // 年月格式：YYYYMM，查询该月份所有数据
                $startDate = $params['riqi'] . '01';
                $endDate = date('Ymd', strtotime("last day of {$params['riqi']}01"));
                $where['日期'] = ['between', [$startDate, $endDate]];
            } else {
                $this->error('日期参数格式错误，应为 YYYYMM');
            }
        } else {
            // 默认查询当前月份
            $currentMonth = date('Ym');
            $startDate = $currentMonth . '01';
            $endDate = date('Ymd');
            $where['日期'] = ['between', [$startDate, $endDate]];
        }
        $res = Db::name('电表能耗日数据')
            ->where($where)->order('日期 DESC')->select();
        $this->success('查询成功', $res);
    }

    /**
     * 导出电表月数据
     */
    public function Excel_StatisticEle(){
        if (!$this->request->isGet()) {
            $this->error('非法请求');
        }
        $params = $this->request->get();
        $res = Db::name('电表能耗月数据')->alias('a')
            ->field('
            a.id,
            a.电表ID,
            a.总电量,
            a.尖电量,
            a.峰电量,
            a.平电量,
            a.谷电量,
            a.起始总电量,
            a.结束总电量,
            a.开始时间,
            a.结束时间,
            a.日期,
            b.description as 小组
            ')
            ->join('电表设备列表 b', 'a.电表ID = b.mid', 'LEFT')
            ->where('日期',$params['sys_rq'])
            ->where('b.description', '<>', '')
            ->where('b.description', 'not null')
            ->order('b.id asc')
            ->select();
        $this->success('请求成功', $res);
    }

    /**
     * 同步电表序号列表数据
     * 查询设备列表和当前状态-左侧菜单
     */
    public function saveToDatabase()
    {
        $url = "http://api1.tqdianbiao.com/Api/Meter?type=json&auth=a15dbee8d66bf1b4149637e3912bf631";
        $resultData = $this->httpGetRequest($url);

        if (!isset($resultData['status']) || $resultData['status'] != 1) {
            throw new Exception('API返回状态异常: ' . ($resultData['message'] ?? '未知错误'));
        }

        if (!isset($resultData['data']) || !is_array($resultData['data'])) {
            throw new Exception('API返回数据格式不正确');
        }

        $result = [];

        foreach ($resultData['data'] as $meter) {
            $result[] = [
                'mid' => $meter['id'],
                'collectorid' => $meter['collectorid'],
                'description' => $meter['description']
            ];
        }

        // 批量处理数据
        foreach ($result as $item) {
            $dbData = [
                'mid' => (int)$item['mid'],
                'collectorid' => (int)$item['collectorid'],
                'description' => $item['description']
            ];
            $exists = Db::name('电表设备列表')->where('mid', $dbData['mid'])->find();
            if ($exists) {
                Db::name('电表设备列表')
                    ->where('mid', $dbData['mid'])
                    ->update($dbData);
            } else {
                Db::name('电表设备列表')->insert($dbData);
            }
        }
    }

    /**
     * 同步电表能耗数据小时数据
     * 查询指定月份的电表能耗小时数据
     */
    public function StatisticEle_Hour()
    {
        if (!$this->request->isGet()) {
            $this->error('非法请求');
        }
        $params = $this->request->get();

        $filterMid = $params['mid'] ?? null;

        // // 默认当天
        $startDate = $params['riqi'] ?? date('Ymd');
        $endDate = $params['riqi'] ?? date('Ymd');

        // 验证日期格式
        if (!preg_match('/^\d{8}$/', $startDate) || !preg_match('/^\d{8}$/', $endDate)) {
            $this->error('参数格式错误');
        }

        // 确保开始日期不大于结束日期
        if ($startDate > $endDate) {
            $this->error('开始日期不能大于结束日期');
        }

        $startTime = $startDate . '00'; // 从开始日期的00点开始
        $endTime = $endDate . '23';     // 到结束日期的23点结束
        $url = "http://api1.tqdianbiao.com/Api/StatisticEle/hour?auth=a15dbee8d66bf1b4149637e3912bf631&type=json&ignore_radio=0&start_time={$startTime}&end_time={$endTime}";

        try {
            $resultData = $this->httpGetRequest($url);

            if (!isset($resultData['status']) || $resultData['status'] != 1) {
                throw new Exception('API返回状态异常: ' . ($resultData['message'] ?? '未知错误'));
            }

            if (!isset($resultData['data']) || !is_array($resultData['data'])) {
                throw new Exception('API返回数据格式不正确');
            }

            $rawData = $resultData['data'];
            $result = [];

            foreach ($rawData as $date => $records) {
                foreach ($records as $row) {
                    $result[] = [
                        '日期'         => $date,
                        '电表ID'       => $row['mid'] ?? '',
                        '开始时间'     => $row['st'] ?? '',
                        '结束时间'     => $row['et'] ?? '',
                        '互感器变比'   => $row['r'] ?? 1,
                        '总电量'       => $row['d'][0] ?? 0,
                        '尖电量'       => $row['d'][1] ?? 0,
                        '峰电量'       => $row['d'][2] ?? 0,
                        '平电量'       => $row['d'][3] ?? 0,
                        '谷电量'       => $row['d'][4] ?? 0,
                        '起始总电量'   => $row['s'][0] ?? 0,
                        '结束总电量'   => $row['e'][0] ?? 0
                    ];
                }
            }

            // 按日期时间倒序排序
            usort($result, function ($a, $b) {
                $timeA = strtotime($a['开始时间']);
                $timeB = strtotime($b['开始时间']);
                return $timeB - $timeA;
            });

            // 直接保存到数据库
            $insertCount = 0;
            $updateCount = 0;
            $errorCount = 0;

            if (!empty($result)) {
                foreach ($result as $index => $item) {
                    $sql = '';
                    try {
                        Log::info("=== 处理第 {$index} 条数据 ===");

                        // 准备数据库数据
                        $dbData = [
                            '电表ID' => (string)$item['电表ID'],
                            '互感器变比' => (string)$item['互感器变比'],
                            '总电量' => (string)$item['总电量'],
                            '尖电量' => (string)$item['尖电量'],
                            '峰电量' => (string)$item['峰电量'],
                            '平电量' => (string)$item['平电量'],
                            '谷电量' => (string)$item['谷电量'],
                            '起始总电量' => (string)$item['起始总电量'],
                            '结束总电量' => (string)$item['结束总电量'],
                            '开始时间' => $item['开始时间'],
                            '结束时间' => $item['结束时间'],
                            '日期' => (string)$item['日期']
                        ];

                        Log::info('处理数据: ' . json_encode($dbData, JSON_UNESCAPED_UNICODE));

                        // 检查记录是否已存在 - 使用电表ID、开始时间和结束时间作为唯一标识
                        $exists = Db::name('电表能耗小时数据')
                            ->where('电表ID', $dbData['电表ID'])
                            ->where('开始时间', $dbData['开始时间'])
                            ->where('结束时间', $dbData['结束时间'])
                            ->find();

                        Log::info('查询条件: 电表ID=' . $dbData['电表ID'] . ', 开始时间=' . $dbData['开始时间'] . ', 结束时间=' . $dbData['结束时间']);
                        Log::info('查询结果: ' . ($exists ? '记录存在, ID=' . $exists['id'] : '记录不存在'));

                        if ($exists) {
                            // 检查数据是否有变化，有变化才更新
                            $needUpdate = false;
                            $updateFields = [];

                            $fieldsToCompare = [
                                '互感器变比', '总电量', '尖电量', '峰电量', '平电量', '谷电量',
                                '起始总电量', '结束总电量', '日期'
                            ];

                            foreach ($fieldsToCompare as $field) {
                                $oldValue = $exists[$field] ?? '';
                                $newValue = $dbData[$field] ?? '';
                                if ($oldValue != $newValue) {
                                    $needUpdate = true;
                                    $updateFields[$field] = $newValue;
                                    Log::info("字段 {$field} 有变化: '{$oldValue}' -> '{$newValue}'");
                                }
                            }

                            if ($needUpdate) {
                                $sql = Db::name('电表能耗小时数据')
                                    ->where('id', $exists['id'])
                                    ->fetchSql(true)
                                    ->update($updateFields);
                                Log::info('执行更新SQL: ' . $sql);
                                $updateResult = \db()->query($sql);
                                Log::info('更新结果: ' . ($updateResult !== false ? '成功' : '失败'));
                                $updateCount++;
                            } else {
                                Log::info('数据无变化，跳过更新');
                            }
                        } else {
                            // 插入新记录
                            $sql = Db::name('电表能耗小时数据')->fetchSql(true)->insert($dbData);
                            Log::info('执行插入SQL: ' . $sql);
                            $insertResult = \db()->query($sql);
                            Log::info('插入结果: ' . ($insertResult !== false ? '成功' : '失败'));
                            if ($insertResult !== false) {
                                $insertCount++;
                            } else {
                                throw new Exception('插入操作返回false');
                            }
                        }

                        Log::info("=== 第 {$index} 条数据处理完成 ===\n");

                    } catch (Exception $e) {
                        $errorCount++;
                        Log::error("=== 第 {$index} 条数据处理失败 ===");
                        Log::error('错误信息: ' . $e->getMessage());
                        Log::error('失败数据: ' . json_encode($dbData, JSON_UNESCAPED_UNICODE));
                        Log::error('执行SQL: ' . $sql);
                        Log::error('错误位置: ' . $e->getFile() . ':' . $e->getLine());
                        Log::error("=== 第 {$index} 条数据处理结束 ===\n");
                    }
                }
            }

            $saveMessage = "数据保存完成 - 新增: {$insertCount}条, 更新: {$updateCount}条, 失败: {$errorCount}条";
            $this->success($saveMessage, [
                'data' => $result,
            ]);

        } catch (Exception $e) {
            $this->error('请求失败: ' . $e->getMessage());
        }
    }

    /**
     * 同步电表能耗数据小时数据
     * 查询指定月份的电表能耗日数据
     */
    public function StatisticEle_Day()
    {
        if (!$this->request->isGet()) {
            $this->error('非法请求');
        }
        $params = $this->request->get();
        $filterMid = $params['mid'] ?? null;
        $riqi = $params['riqi'] ?? date('Ym'); // 默认当前年月

        if (!preg_match('/^\d{6}$/', $riqi)) {
            $this->error('参数 riqi 格式错误，应为 YYYYMM');
        }

        // 构造 start_time
        $startTime = $riqi . '01';

        // 判断是当前月还是历史月
        $currentMonth = date('Ym');
        if ($riqi === $currentMonth) {
            $endTime = date('Ymd'); // 今天
        } else {
            $endTime = date('Ymd', strtotime("last day of {$riqi}01")); // 历史月最后一天
        }

        // 构造 API 请求 URL - 查询指定月份的电表能耗日数据
        $url = "http://api1.tqdianbiao.com/Api/StatisticEle/day?auth=a15dbee8d66bf1b4149637e3912bf631&type=json&ignore_radio=0&start_time={$startTime}&end_time={$endTime}";

        try {
            $resultData = $this->httpGetRequest($url);

            if (!isset($resultData['status']) || $resultData['status'] != 1) {
                throw new Exception('API返回状态异常: ' . ($resultData['message'] ?? '未知错误'));
            }

            if (!isset($resultData['data']) || !is_array($resultData['data'])) {
                throw new Exception('API返回数据格式不正确');
            }

            $rawData = $resultData['data'];
            $result = [];

            foreach ($rawData as $date => $records) {
                foreach ($records as $row) {
                    $result[] = [
                        '日期'         => $date,
                        '电表ID'       => $row['mid'] ?? '',
                        '开始时间'     => $row['st'] ?? '',
                        '结束时间'     => $row['et'] ?? '',
                        '互感器变比'   => $row['r'] ?? 1,
                        '总电量'       => $row['d'][0] ?? 0,
                        '尖电量'       => $row['d'][1] ?? 0,
                        '峰电量'       => $row['d'][2] ?? 0,
                        '平电量'       => $row['d'][3] ?? 0,
                        '谷电量'       => $row['d'][4] ?? 0,
                        '起始总电量'   => $row['s'][0] ?? 0,
                        '结束总电量'   => $row['e'][0] ?? 0
                    ];
                }
            }

            usort($result, function ($a, $b) {
                return strcmp($b['日期'], $a['日期']);
            });

            // 直接保存到数据库
            $insertCount = 0;
            $updateCount = 0;
            $errorCount = 0;

            if (!empty($result)) {
                foreach ($result as $index => $item) {
                    $sql = '';
                    try {
                        Log::info("=== 处理第 {$index} 条数据 ===");

                        // 准备数据库数据 - 修正字段名为大写
                        $dbData = [
                            '电表ID' => (string)$item['电表ID'],  // 修正：电表ID（大写）
                            '互感器变比' => (string)$item['互感器变比'],
                            '总电量' => (string)$item['总电量'],
                            '尖电量' => (string)$item['尖电量'],
                            '峰电量' => (string)$item['峰电量'],
                            '平电量' => (string)$item['平电量'],
                            '谷电量' => (string)$item['谷电量'],
                            '起始总电量' => (string)$item['起始总电量'],
                            '结束总电量' => (string)$item['结束总电量'],
                            '开始时间' => $item['开始时间'],
                            '结束时间' => $item['结束时间'],
                            '日期' => (string)$item['日期']
                        ];

                        Log::info('处理数据: ' . json_encode($dbData, JSON_UNESCAPED_UNICODE));

                        // 检查记录是否已存在 - 使用正确的字段名
                        $exists = Db::name('电表能耗日数据')
                            ->where('电表ID', $dbData['电表ID'])  // 修正：电表ID（大写）
                            ->where('日期', $dbData['日期'])
                            ->find();

                        Log::info('查询条件: 电表ID=' . $dbData['电表ID'] . ', 日期=' . $dbData['日期']);
                        Log::info('查询结果: ' . ($exists ? '记录存在, ID=' . $exists['id'] : '记录不存在'));

                        if ($exists) {
                            // 检查数据是否有变化，有变化才更新
                            $needUpdate = false;
                            $updateFields = [];

                            $fieldsToCompare = [
                                '互感器变比', '总电量', '尖电量', '峰电量', '平电量', '谷电量',
                                '起始总电量', '结束总电量', '开始时间', '结束时间'
                            ];

                            foreach ($fieldsToCompare as $field) {
                                $oldValue = $exists[$field] ?? '';
                                $newValue = $dbData[$field] ?? '';
                                if ($oldValue != $newValue) {
                                    $needUpdate = true;
                                    $updateFields[$field] = $newValue;
                                    Log::info("字段 {$field} 有变化: '{$oldValue}' -> '{$newValue}'");
                                }
                            }

                            if ($needUpdate) {
                                $sql = Db::name('电表能耗日数据')
                                    ->where('id', $exists['id'])
                                    ->fetchSql(true)
                                    ->update($updateFields);
                                Log::info('执行更新SQL: ' . $sql);
                                $updateResult = \db()->query($sql);
                                Log::info('更新结果: ' . ($updateResult !== false ? '成功' : '失败'));
                                $updateCount++;
                            } else {
                                Log::info('数据无变化，跳过更新');
                            }
                        } else {
                            // 插入新记录
                            $sql = Db::name('电表能耗日数据')->fetchSql(true)->insert($dbData);
                            Log::info('执行插入SQL: ' . $sql);
                            $insertResult = \db()->query($sql);
                            Log::info('插入结果: ' . ($insertResult !== false ? '成功' : '失败'));
                            if ($insertResult !== false) {
                                $insertCount++;
                            } else {
                                throw new Exception('插入操作返回false');
                            }
                        }

                        Log::info("=== 第 {$index} 条数据处理完成 ===\n");

                    } catch (Exception $e) {
                        $errorCount++;
                        Log::error("=== 第 {$index} 条数据处理失败 ===");
                        Log::error('错误信息: ' . $e->getMessage());
                        Log::error('失败数据: ' . json_encode($dbData, JSON_UNESCAPED_UNICODE));
                        Log::error('执行SQL: ' . $sql);
                        Log::error('错误位置: ' . $e->getFile() . ':' . $e->getLine());
                        Log::error("=== 第 {$index} 条数据处理结束 ===\n");
                    }
                }
            }

            Log::info("=== 最终统计 ===");
            Log::info("新增: {$insertCount}条, 更新: {$updateCount}条, 失败: {$errorCount}条");

            $saveMessage = "数据保存完成 - 新增: {$insertCount}条, 更新: {$updateCount}条, 失败: {$errorCount}条";
            $this->success($saveMessage, [
                'data' => $result,
            ]);

        } catch (Exception $e) {
            $this->error('请求失败: ' . $e->getMessage());
        }
    }

    /**
     * 同步电表能耗数据月数据
     * 查询指定月份的电表能耗月数据
     */
    public function StatisticEle_Month()
    {
        if (!$this->request->isGet()) {
            $this->error('非法请求');
        }

        $params = $this->request->get();
        $filterMid = $params['mid'] ?? null;
        $riqi = $params['riqi'] ?? date('Ym'); // 默认当前年月

        if (!preg_match('/^\d{6}$/', $riqi)) {
            $this->error('参数 riqi 格式错误，应为 YYYYMM');
        }

        // 构造 start_time 和 end_time - 月接口通常只需要月份参数，查询指定月份的所有数据
        $startTime = $riqi . '01';
        $endTime = $riqi . '31'; // 月数据通常查询单个月份

        // 构造 API 请求 URL - 使用 month 接口查询指定月份的电表能耗月数据
        $url = "http://api1.tqdianbiao.com/Api/StatisticEle/month?auth=a15dbee8d66bf1b4149637e3912bf631&type=json&ignore_radio=0&start_time={$startTime}&end_time={$endTime}";

        try {
            $resultData = $this->httpGetRequest($url);

            if (!isset($resultData['status']) || $resultData['status'] != 1) {
                throw new Exception('API返回状态异常: ' . ($resultData['message'] ?? '未知错误'));
            }

            if (!isset($resultData['data']) || !is_array($resultData['data'])) {
                throw new Exception('API返回数据格式不正确');
            }

            $rawData = $resultData['data'];
            $result = [];

            foreach ($rawData as $date => $records) {
                foreach ($records as $row) {
                    $result[] = [
                        '月份'         => $date,
                        '电表ID'       => $row['mid'] ?? '',
                        '开始时间'     => $row['st'] ?? '',
                        '结束时间'     => $row['et'] ?? '',
                        '互感器变比'   => $row['r'] ?? 1,
                        '总电量'       => $row['d'][0] ?? 0,
                        '尖电量'       => $row['d'][1] ?? 0,
                        '峰电量'       => $row['d'][2] ?? 0,
                        '平电量'       => $row['d'][3] ?? 0,
                        '谷电量'       => $row['d'][4] ?? 0,
                        '起始总电量'   => $row['s'][0] ?? 0,
                        '结束总电量'   => $row['e'][0] ?? 0
                    ];
                }
            }

            usort($result, function ($a, $b) {
                return strcmp($b['月份'], $a['月份']);
            });

            // 直接保存到数据库
            $insertCount = 0;
            $updateCount = 0;
            $errorCount = 0;

            if (!empty($result)) {
                foreach ($result as $item) {
                    try {
                        $dbData = [
                            '电表ID' => (int)$item['电表ID'],
                            '互感器变比' => (string)$item['互感器变比'],
                            '总电量' => (string)$item['总电量'],
                            '尖电量' => (string)$item['尖电量'],
                            '峰电量' => (string)$item['峰电量'],
                            '平电量' => (string)$item['平电量'],
                            '谷电量' => (string)$item['谷电量'],
                            '起始总电量' => (string)$item['起始总电量'],
                            '结束总电量' => (string)$item['结束总电量'],
                            '开始时间' => $item['开始时间'],
                            '结束时间' => $item['结束时间'],
                            '日期' => $item['月份']
                        ];

                        // 查询是否已存在相同月份的记录
                        $exists = Db::name('电表能耗月数据')
                            ->where('电表ID', $dbData['电表ID'])
                            ->where('日期', $dbData['日期'])
                            ->find();

                        if ($exists) {
                            // 检查是否有字段值不同
                            $needUpdate = false;
                            $updateFields = [];

                            // 检查是否有字段值不同
                            $fieldsToCompare = [
                                '互感器变比', '总电量', '尖电量', '峰电量', '平电量', '谷电量',
                                '起始总电量', '结束总电量', '开始时间', '结束时间'
                            ];

                            foreach ($fieldsToCompare as $field) {
                                if (isset($exists[$field]) && isset($dbData[$field]) && $exists[$field] != $dbData[$field]) {
                                    $needUpdate = true;
                                    $updateFields[$field] = $dbData[$field];
                                }
                            }

                            if ($needUpdate) {
                                // 只更新有变化的字段
                                $sql = Db::name('电表能耗月数据')
                                    ->where('id', $exists['id'])
                                    ->fetchSql(true)
                                    ->update($updateFields);
                                \db()->query($sql);
                                $updateCount++;
                            }
                        } else {
                            // 插入新记录
                            $sql = Db::name('电表能耗月数据')->fetchSql(true)->insert($dbData);
                            \db()->query($sql);
                            $insertCount++;
                        }
                    } catch (Exception $e) {
                        $errorCount++;
                    }
                }
            }

            $saveMessage = "月数据保存完成 - 新增: {$insertCount}条, 更新: {$updateCount}条, 失败: {$errorCount}条";
            $this->success($saveMessage, [
                'data' => $result,
            ]);

        } catch (Exception $e) {
            $this->error('请求失败: ' . $e->getMessage());
        }
    }




    /**
     * 订单电量管理 - 左侧菜单
     * 客户编号按自然顺序排序
     * 返回年份分组的数据结构
     */
    public function CustomerMenu()
    {
        try {
            $startDate = input('start_date', '');
            $endDate = input('end_date', '');

            $query = Db::name('设备_产量计酬')->alias('c')
                ->field('c.订单编号, c.sys_rq, j.客户编号, DATE_FORMAT(c.sys_rq, "%Y%m") as date_group')
                ->join('工单_基本资料 j', 'c.订单编号 = j.订单编号', 'LEFT')
                ->whereNull('c.mod_rq')
                ->whereNull('j.Mod_rq')
                ->where('j.客户编号', '<>', '')
                ->whereNotNull('j.客户编号');

            if (!empty($startDate)) {
                $query->where('c.sys_rq', '>=', $startDate);
            }
            if (!empty($endDate)) {
                $query->where('c.sys_rq', '<=', $endDate);
            }

            $orderRecords = $query->select();

            // 重新组织数据结构，按年份分组
            $structuredData = [];
            foreach ($orderRecords as $record) {
                $yearMonth = $record['date_group'];
                $customerNo = $record['客户编号'];

                if ($yearMonth && $customerNo) {
                    $year = substr($yearMonth, 0, 4);  // 提取年份

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

                    if (!isset($structuredData[$year][$yearMonth])) {
                        $structuredData[$year][$yearMonth] = [];
                    }

                    if (!in_array($customerNo, $structuredData[$year][$yearMonth])) {
                        $structuredData[$year][$yearMonth][] = $customerNo;
                    }
                }
            }

            // 对每个月份的客户编号进行自然排序，并对月份按倒序排列
            foreach ($structuredData as $year => &$months) {
                foreach ($months as &$customers) {
                    natsort($customers);
                    $customers = array_values($customers);
                }
                // 对月份按倒序排列
                krsort($months);
            }

            // 对年份按倒序排列
            krsort($structuredData);

            return json([
                'code' => 0,
                'msg' => '成功',
                'time' => time(),
                'data' => $structuredData
            ]);

        } catch (\Exception $e) {
            return json([
                'code' => 1,
                'msg' => '查询失败: ' . $e->getMessage(),
                'time' => time(),
                'data' => []
            ]);
        }
    }


    /**
     * 订单电量管理 - 汇总统计
     */
    public function OrderElectricitySummary(){
        if (!$this->request->isGet()) {
            $this->error('非法请求');
        }
        $params = $this->request->get();

        $where = [];
        if (!empty($params['order'])) {
            $where['订单编号'] = $params['order'];
        }

        if (!empty($params['khbh'])) {
            $where['客户编号'] = $params['khbh'];
        }

        $orders = Db::name('工单_基本资料')
            ->field('订单编号,客户编号,生产款号')
            ->where($where)
            ->whereNull('Mod_rq')
            ->select();

        if (empty($orders)) {
            $this->success('未查询到订单数据', []);
        }

        // 获取所有订单编号
        $orderNumbers = array_column($orders, '订单编号');

        // 首先查询每个订单的总产量和总时间范围
        $orderTimeRanges = Db::name('设备_产量计酬')
            ->field('订单编号,SUM(数量) as 总产量,MIN(sys_rq) as 最早报工时间,MAX(sys_rq) as 最晚报工时间,COUNT(*) as 总报工次数')
            ->where('订单编号', 'in', $orderNumbers)
            ->whereNull('mod_rq')
            ->group('订单编号')
            ->select();

        // 构建订单时间范围映射
        $orderTimeMap = [];
        foreach ($orderTimeRanges as $timeRange) {
            $orderTimeMap[$timeRange['订单编号']] = [
                '总产量' => $timeRange['总产量'],
                '最早报工时间' => $timeRange['最早报工时间'],
                '最晚报工时间' => $timeRange['最晚报工时间'],
                '总报工次数' => $timeRange['总报工次数']
            ];
        }

        // 构建报工记录查询（按设备分组）
        $orderRecordsQuery = Db::name('设备_产量计酬')
            ->field('订单编号,sczl_bh,SUM(数量) as 总产量,MIN(sys_rq) as 最早报工时间,MAX(sys_rq) as 最晚报工时间,COUNT(*) as 报工次数')
            ->where('订单编号', 'in', $orderNumbers)
            ->whereNull('mod_rq');

        // 如果有日期参数，按日期筛选报工记录
        $startDate = null;
        $endDate = null;
        if (!empty($params['riqi'])) {
            $date = $params['riqi'];
            $startDate = date('Y-m-01', strtotime($date)); // 当月第一天
            $endDate = date('Y-m-t 23:59:59', strtotime($date)); // 当月最后一天
            $orderRecordsQuery->whereTime('sys_rq', 'between', [$startDate, $endDate]);
        }

        $orderRecords = $orderRecordsQuery->group('订单编号,sczl_bh')->select();

        if (empty($orderRecords)) {
            // 如果没有报工记录，只返回订单基本信息
            $summary = [];
            foreach ($orders as $order) {
                $orderNumber = $order['订单编号'];
                $timeInfo = $orderTimeMap[$orderNumber] ?? [
                        '总产量' => 0,
                        '最早报工时间' => null,
                        '最晚报工时间' => null,
                        '总报工次数' => 0
                    ];

                $summary[] = [
                    '订单编号' => $orderNumber,
                    '生产款号' => $order['生产款号'],
                    '客户编号' => $order['客户编号'],
                    '累计总用电量' => 0,
                    '单位电量' => 0,
                    '生产时间段' => $timeInfo['最早报工时间'] ? ($timeInfo['最早报工时间'] . ' 至 ' . $timeInfo['最晚报工时间']) : '无生产记录'
                ];
            }
            $this->success('成功', $summary);
        }

        // 获取所有涉及的设备编号
        $sczlBhs = array_unique(array_column($orderRecords, 'sczl_bh'));

        // 通过sczl_bh去电表设备列表表匹配对应的mid
        $deviceMappings = Db::name('电表设备列表')
            ->field('mid, collectorid, description')
            ->where('description', 'in', $sczlBhs)
            ->select();

        $midMap = [];
        $collectorIdMap = [];
        foreach ($deviceMappings as $mapping) {
            $midMap[$mapping['description']] = $mapping['mid'];
            $collectorIdMap[$mapping['description']] = $mapping['collectorid'];
        }

        // 获取所有相关电表的能耗数据 - 使用mid来关联
        $electricData = [];
        if (!empty($midMap)) {
            $mids = array_values($midMap);

            $electricData = Db::name('电表能耗小时数据')
                ->where('电表ID', 'in', $mids)
                ->order('开始时间', 'asc')
                ->select();
        }

        // 按订单编号分组统计总电量
        $orderStats = [];

        // 初始化订单统计
        foreach ($orders as $order) {
            $orderNumber = $order['订单编号'];
            $timeInfo = $orderTimeMap[$orderNumber] ?? [
                    '总产量' => 0,
                    '最早报工时间' => null,
                    '最晚报工时间' => null,
                    '总报工次数' => 0
                ];

            $orderStats[$orderNumber] = [
                '订单编号' => $orderNumber,
                '生产款号' => $order['生产款号'],
                '客户编号' => $order['客户编号'],
                '总电量' => 0,
                '总产量' => $timeInfo['总产量'],
                '最早报工时间' => $timeInfo['最早报工时间'],
                '最晚报工时间' => $timeInfo['最晚报工时间']
            ];
        }

        // 统计每个订单的总电量（使用与明细查询相同的逻辑）
        foreach ($orderRecords as $record) {
            $orderNumber = $record['订单编号'];
            $sczlBh = $record['sczl_bh'];

            if (!isset($orderStats[$orderNumber])) {
                continue;
            }

            // 获取对应的电表mid
            $mid = $midMap[$sczlBh] ?? null;
            if (!$mid) {
                continue;
            }

            // 使用订单总时间范围而不是设备小组的时间范围
            $orderTimeInfo = $orderTimeMap[$orderNumber] ?? null;
            if (!$orderTimeInfo) {
                continue;
            }

            $deviceStart = strtotime($orderTimeInfo['最早报工时间']);
            $deviceEnd = strtotime($orderTimeInfo['最晚报工时间']);

            $deviceElectricity = 0;
            foreach ($electricData as $elec) {
                // 正确的比较：电表能耗数据中的电表ID应该等于设备对应的mid
                if ($elec['电表ID'] != $mid) {
                    continue;
                }

                $elecStart = strtotime($elec['开始时间']);
                $elecEnd = strtotime($elec['结束时间']);

                // 匹配电表时间段在订单总生产时间段范围内
                if ($elecStart >= $deviceStart && $elecEnd <= $deviceEnd) {
                    $deviceElectricity += $elec['总电量'];
                }
            }

            $orderStats[$orderNumber]['总电量'] += $deviceElectricity;
        }

        // 格式化输出结果，并过滤掉总电量为0的记录
        $summary = [];
        foreach ($orderStats as $order) {
            // 只有当总电量大于0时才添加到结果中
            if ($order['总电量'] > 0) {
                $summary[] = [
                    '订单编号' => $order['订单编号'],
                    '生产款号' => $order['生产款号'],
                    '客户编号' => $order['客户编号'],
                    '累计总用电量' => round($order['总电量'], 2),
                    '单位电量' => $order['总产量'] > 0 ? round($order['总电量'] / $order['总产量'], 4) : 0,
                    '生产时间段' => $order['最早报工时间'] ? ($order['最早报工时间'] . ' 至 ' . $order['最晚报工时间']) : '无生产记录'
                ];
            }
        }

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

    /**
     * 订单电量管理 - 明细查询
     */
    public function OrderElectricityDetail(){
        if (!$this->request->isGet()) {
            $this->error('非法请求');
        }
        $params = $this->request->get();

        if (empty($params['order'])) {
            $this->error('订单编号不能为空');
        }

        // 查询订单的基本信息
        $orderInfo = Db::name('工单_基本资料')
            ->field('订单编号,生产款号,客户编号')
            ->where('订单编号', $params['order'])
            ->whereNull('Mod_rq')
            ->find();

        if (!$orderInfo) {
            $this->error('订单不存在');
        }

        // 查询该订单的所有报工记录，按设备分组
        $orderRecords = Db::name('设备_产量计酬')
            ->field('订单编号,sczl_bh,
            CASE 
            WHEN 工序名称 IN ("裁剪", "车缝") THEN SUM(数量)
            ELSE SUM(s_num)
            END as 总产量,MIN(sys_rq) as 最早报工时间,MAX(sys_rq) as 最晚报工时间,COUNT(*) as 报工次数')
            ->where('订单编号', $params['order'])
            ->whereNull('mod_rq')
            ->group('sczl_bh')
            ->select();

        if (empty($orderRecords)) {
            $this->success('成功', [
                '订单信息' => $orderInfo,
                '明细数据' => []
            ]);
        }

        // 获取所有涉及的设备编号
        $sczlBhs = array_column($orderRecords, 'sczl_bh');

        // 通过sczl_bh去电表设备列表表匹配对应的mid
        $deviceMappings = Db::name('电表设备列表')
            ->field('mid, collectorid, description')
            ->where('description', 'in', $sczlBhs)
            ->select();

        $midMap = [];
        $collectorIdMap = [];
        foreach ($deviceMappings as $mapping) {
            $midMap[$mapping['description']] = $mapping['mid'];
            $collectorIdMap[$mapping['description']] = $mapping['collectorid'];
        }

        // 获取所有相关电表的能耗数据 - 使用mid来关联
        $electricData = [];
        if (!empty($midMap)) {
            $mids = array_values($midMap);
            $electricData = Db::name('电表能耗小时数据')
                ->where('电表ID', 'in', $mids)  // 这里电表ID对应的是mid
                ->order('开始时间', 'asc')
                ->select();
        }

        // 组织明细数据
        $details = [];
        $orderTotalElectricity = 0;
        $orderTotalOutput = 0;

        // 定义设备小组的固定排序
        $deviceOrder = [
            '裁剪' => 1,
            '车缝' => 2,
            '手工' => 3,
            '大烫' => 4,
            '总检' => 5,
            '包装' => 6
        ];

        foreach ($orderRecords as $record) {
            $sczlBh = $record['sczl_bh'];
            $mid = $midMap[$sczlBh] ?? null;
            $collectorId = $collectorIdMap[$sczlBh] ?? null;

            $deviceElectricity = 0;
            $matchedHours = 0;
            $hourlyDetails = [];

            if ($mid && !empty($electricData)) {
                $deviceStart = strtotime($record['最早报工时间']);
                $deviceEnd = strtotime($record['最晚报工时间']);

                // 筛选该设备在报工期间的电表数据
                foreach ($electricData as $elec) {
                    // 正确的比较：电表能耗数据中的电表ID应该等于设备对应的mid
                    if ($elec['电表ID'] != $mid) {
                        continue;
                    }

                    $elecStart = strtotime($elec['开始时间']);
                    $elecEnd = strtotime($elec['结束时间']);

                    // 匹配电表时间段在设备生产时间段范围内
                    if ($elecStart >= $deviceStart && $elecEnd <= $deviceEnd) {
                        $deviceElectricity += $elec['总电量'];
                        $matchedHours++;

                        $hourlyDetails[] = [
                            '电表时间段' => $elec['开始时间'] . ' - ' . $elec['结束时间'],
                            '电量' => round($elec['总电量'], 2),
                            '日期' => $elec['日期']
                        ];
                    }
                }
            }

            // 如果累计用电量为空或0，跳过不显示
            if (empty($deviceElectricity)) {
                continue;
            }

            $orderTotalElectricity += $deviceElectricity;
            $orderTotalOutput += $record['总产量'];

            // 确定设备类型和排序权重
            $deviceType = '';
            $sortWeight = 999; // 默认权重，排在最后

            foreach ($deviceOrder as $type => $weight) {
                if (strpos($sczlBh, $type) !== false) {
                    $deviceType = $type;
                    $sortWeight = $weight;
                    break;
                }
            }

            // 改进的设备序号提取方法
            $deviceNumber = 0;
            if (preg_match('/\d+/', $sczlBh, $matches)) {
                $deviceNumber = intval($matches[0]);
            }

            $details[] = [
                '订单编号' => $record['订单编号'],
                '设备小组' => $sczlBh,
                '电表ID' => $mid,
                '采集器ID' => $collectorId,
                '生产款号' => $orderInfo['生产款号'],
                '产量' => $record['总产量'],
                '累计用电量' => round($deviceElectricity, 2),
                '单位电量' => $record['总产量'] > 0 ? round($deviceElectricity / $record['总产量'], 4) : 0,
                '生产时间段' => $record['最早报工时间'] . ' 至 ' . $record['最晚报工时间'],
                '报工次数' => $record['报工次数'],
                '匹配小时数' => $matchedHours,
                // 用于排序的字段
                '_sort_weight' => $sortWeight,
                '_device_number' => $deviceNumber,
                '_device_name' => $sczlBh // 添加设备名称用于调试
            ];
        }

        // 按照设备类型和序号排序
        usort($details, function($a, $b) {
            // 首先按设备类型排序
            if ($a['_sort_weight'] != $b['_sort_weight']) {
                return $a['_sort_weight'] - $b['_sort_weight'];
            }
            // 然后按设备序号排序
            if ($a['_device_number'] != $b['_device_number']) {
                return $a['_device_number'] - $b['_device_number'];
            }
            // 最后按设备名称排序（确保完全一致）
            return strcmp($a['_device_name'], $b['_device_name']);
        });

        // 移除排序用的临时字段
        foreach ($details as &$item) {
            unset($item['_sort_weight']);
            unset($item['_device_number']);
            unset($item['_device_name']);
        }

        $result = [
            'list' => $details
        ];
        $this->success('成功', $result);
    }

    //成品入仓左侧菜单
    public function Finished_Datalist(){
        // 获取成品入仓表中的所有数据
        $data = Db::name('成品入仓')
            ->field('sys_rq')
            ->whereNull('mod_rq')
            ->select();

        // 按年、年月、年月日构建嵌套数据结构
        $dateData = [];

        foreach ($data as $item) {
            if (!empty($item['sys_rq'])) {
                $date = $item['sys_rq'];
                $year = date('Y', strtotime($date));
                $yearMonth = date('Y-m', strtotime($date));
                $yearMonthDay = date('Y-m-d', strtotime($date));

                // 初始化年、年月、年月日的数据结构
                if (!isset($dateData[$year])) {
                    $dateData[$year] = [];
                }
                if (!isset($dateData[$year][$yearMonth])) {
                    $dateData[$year][$yearMonth] = [];
                }

                // 添加年月日到对应年月数组中，避免重复
                if (!in_array($yearMonthDay, $dateData[$year][$yearMonth])) {
                    $dateData[$year][$yearMonth][] = $yearMonthDay;
                }
            }
        }

        // 对年月日数组进行降序排序（最近的日期在前面）
        foreach ($dateData as $year => $yearMonths) {
            foreach ($yearMonths as $yearMonth => $days) {
                rsort($dateData[$year][$yearMonth]);
            }
            // 对年月进行降序排序
            krsort($dateData[$year]);
        }
        // 对年进行降序排序
        krsort($dateData);

        // 构建响应
        $res['code'] = 0;
        $res['msg'] = '成功';
        $res['time'] = time(); // 当前时间戳
        $res['data'] = $dateData;

        return json($res);
    }

    //成品入仓列表数据
    public function Finished_Product(){
        if (!$this->request->isGet()) {
            $this->error('请求方式错误');
        }
        $params = $this->request->param();
        $search = input('order', '');
        $page = input('page', 1, 'intval');
        $limit = input('limit', 10, 'intval');

        $where = [];
        if (!empty($search)) {
            $where['a.order|j.生产款号|j.款式'] = ['like', '%' . $search . '%'];
        }
        if (!empty($params['date'])) {
            $where['a.sys_rq'] = ['like', "%{$params['date']}%"];
        }

        // 计算总记录数（去重后的订单数量）
        $total = Db::name('成品入仓')->alias('a')
            ->join('工单_基本资料 j', 'a.order=j.订单编号', 'left')
            ->where($where)
            ->group('a.order')
            ->count();

        // 查询数据，对相同订单分组并求和成品数量
        $list = Db::name('成品入仓')->alias('a')
            ->field('               
                a.order as 订单编号,
                SUM(a.sl) as 成品数量,
                j.生产款号,
                j.款式,
                j.订单数量
            ')
            ->join('工单_基本资料 j', 'a.order=j.订单编号', 'left')
            ->where($where)
            ->group('a.order')
            ->limit(($page - 1) * $limit, $limit)
            ->select();

        // 计算未入仓数量和入仓率
        foreach ($list as &$item) {
            $item['未入仓数量'] = $item['订单数量'] - $item['成品数量'];
            // 计算入仓率（成品数量 / 订单数量 * 100%），保留2位小数
            $item['入仓率'] = $item['订单数量'] > 0 ? round(($item['成品数量'] / $item['订单数量']) * 100, 2) . '%' : '0%';
        }

        $res['code'] = 0;
        $res['msg'] = '成功';
        $res['count'] = $total;
        $res['data'] = $list;
        return json($res);
    }


    //面料库存列表
    public function GetFabric(){
        if (!$this->request->isGet()) {
            $this->error('请求方式错误');
        }
        $params = $this->request->param();
        $search = input('search', '');
        $page = isset($params['page']) ? (int)$params['page'] : 1;
        $limit = isset($params['limit']) ? (int)$params['limit'] : 50;

        $where = [];
        if (!empty($search)) {
            $where['物料编号|物料名称'] = ['like', '%' . $search . '%'];
        }
        $list = Db::name('物料_库存')
            ->where($where)
            ->whereNull('Mod_rq')
            ->limit(($page - 1) * $limit, $limit)
            ->select();
        $total = Db::name('物料_库存')->whereNull('Mod_rq')->where($where) ->count();
        $res['code'] = 0;
        $res['msg'] = '成功';
        $res['count'] = $total;
        $res['data'] = $list;
        return json($res);
    }
    //面料库存分析
    public function GetAPIFabric(){
        // 查询所有物料库存数据
        $allItems = Db::name('物料_库存')
            ->field('物料名称,库存数量,入仓总量,领用数量,退还数量,单位')
            ->order('Sys_rq desc')
            ->whereNull('Mod_rq')
            ->select();

        // 按物料名称分组并累加库存数量
        $groupedData = [];
        foreach ($allItems as $item) {
            $materialName = $item['物料名称'];
            if (!isset($groupedData[$materialName])) {
                // 初始化新的物料记录
                $groupedData[$materialName] = [
                    '物料名称' => $materialName,
                    '库存数量' => 0,
                    '入仓总量' => 0,
                    '领用数量' => 0,
                    '退还数量' => 0,
                    '单位' => $item['单位']
                ];
            }

            // 累加各项数量（确保转换为数字类型）
            $groupedData[$materialName]['库存数量'] += floatval($item['库存数量']);
            $groupedData[$materialName]['入仓总量'] += floatval($item['入仓总量']);
            $groupedData[$materialName]['领用数量'] += floatval($item['领用数量']);
            $groupedData[$materialName]['退还数量'] += floatval($item['退还数量'] ?? 0);
        }

        // 将关联数组转换为索引数组
        $sortedList = array_values($groupedData);

        // 按库存数量降序排序，确保库存最多的物料显示在最前面
        usort($sortedList, function($a, $b) {
            return $b['库存数量'] - $a['库存数量'];
        });

        // 格式化数值字段，保留两位小数
        foreach ($sortedList as &$item) {
            $item['库存数量'] = round($item['库存数量'], 2);
            $item['入仓总量'] = round($item['入仓总量'], 2);
            $item['领用数量'] = round($item['领用数量'], 2);
            $item['退还数量'] = round($item['退还数量'], 2);
        }

        // 限制只返回前20个物料
        $limitedList = array_slice($sortedList, 0, 20);

        $res['code'] = 0;
        $res['msg'] = '成功';
        $res['data'] = $limitedList;
        return json($res);
     }
    //Bom资料列表
    public function Get_BomList(){
        if (!$this->request->isGet()) {
            $this->error('请求方式错误');
        }
        $params = $this->request->param();
        $search = input('search', '');
        $page = isset($params['page']) ? (int)$params['page'] : 1;
        $limit = isset($params['limit']) ? (int)$params['limit'] : 50;
        $where = [];
        if (!empty($search)) {
            $where['a.BOM_工单编号|a.物料分类|a.BOM_desc'] = ['like', '%' . $search . '%'];
        }
        $list = Db::name('工单_bom资料')->alias('a')
            ->field('
                a.BOM_工单编号 as 订单编号,
                a.物料分类,
                a.BOM_物料名称 as 物料名称,
                a.BOM_计划用量 as 计划用料,
                a.BOM_计划门幅 as 计划门幅,
                a.BOM_标准用量 as 定额用料,
                a.BOM_定额门幅 as 定额门幅,
                a.BOM_desc as 备注,
                j.生产款号,
                j.款式
            ')
            ->join('工单_基本资料 j', 'a.BOM_工单编号 = j.订单编号', 'left')
            ->where($where)
            ->limit(($page - 1) * $limit, $limit)
            ->order('a.Sys_rq desc')
            ->whereNull('a.Mod_rq')
            ->select();
        $total = Db::name('工单_bom资料')->alias('a')
        ->whereNull('Mod_rq')
        ->where($where)
        ->count();
        $res['code'] = 0;
        $res['msg'] = '成功';
        $res['count'] = $total;
        $res['data'] = $list;
        return json($res);
     }

     //查询订单进度情况
    public function GetOrderprogress(){
        if (!$this->request->isGet()) {
            $this->error('请求方式错误');
        }
        $params = $this->request->param();
        $search = input('search', '');
        $page = isset($params['page']) ? (int)$params['page'] : 1;
        $limit = isset($params['limit']) ? (int)$params['limit'] : 50;
        $where = [];
        if (!empty($search)) {
            $where['a.订单编号|a.客户编号|a.生产款号|a.款式'] = ['like', '%' . $search . '%'];
        }

        // 获取所有订单基本信息（先分页）
        $orders = Db::name('工单_基本资料')->alias('a')
            ->field('                
                a.订单编号,
                a.客户编号,
                a.生产款号,
                a.款式,
                a.img,
                a.订单数量
            ')
            ->whereNull('a.Mod_rq')
            ->where($where)
            ->limit(($page - 1) * $limit, $limit)
            ->order('a.Sys_rq desc')
            ->select();

        $total = Db::name('工单_基本资料')->alias('a')
            ->whereNull('Mod_rq')
            ->where($where)
            ->count();

        if (empty($orders)) {
            $res['code'] = 0;
            $res['msg'] = '成功';
            $res['count'] = $total;
            $res['data'] = [];
            return json($res);
        }

        // 提取订单编号列表
        $orderNumbers = array_column($orders, '订单编号');

        // 获取这些订单的所有报工数据
        $reportData = Db::name('设备_产量计酬')->alias('c')
            ->field('                
                c.订单编号,
                c.工序名称,
                CASE 
                    WHEN c.工序名称 IN ("裁剪", "车缝") THEN c.数量
                    ELSE c.s_num 
                END as 报工数量,
                c.sczl_bh as 报工小组
            ')
            ->whereIn('c.订单编号', $orderNumbers)
            ->whereNull('c.mod_rq')
            ->select();

        // 按订单编号和工序名称分组处理数据
        $orderProcessReportMap = [];
        foreach ($reportData as $item) {
            $orderNo = $item['订单编号'];
            $processName = $item['工序名称'];

            if (!isset($orderProcessReportMap[$orderNo])) {
                $orderProcessReportMap[$orderNo] = [
                    '工序列表' => [],
                    '工序报工数量' => [],
                    '工序报工小组' => [],
                ];
            }

            // 初始化该工序的数据
            if (!isset($orderProcessReportMap[$orderNo]['工序报工数量'][$processName])) {
                $orderProcessReportMap[$orderNo]['工序报工数量'][$processName] = 0;
                $orderProcessReportMap[$orderNo]['工序报工小组'][$processName] = [];
            }

            // 累加各工序的报工数量
            $orderProcessReportMap[$orderNo]['工序报工数量'][$processName] += $item['报工数量'];

            // 收集该工序的报工小组（去重）
            if (!in_array($item['报工小组'], $orderProcessReportMap[$orderNo]['工序报工小组'][$processName])) {
                $orderProcessReportMap[$orderNo]['工序报工小组'][$processName][] = $item['报工小组'];
            }
        }

        // 将报工数据合并到订单数据中
        $result = [];
        foreach ($orders as $order) {
            $orderNo = $order['订单编号'];

            if (isset($orderProcessReportMap[$orderNo])) {
                $report = $orderProcessReportMap[$orderNo];

                // 将每个工序的名称和数量作为单独的字段
                foreach ($report['工序报工数量'] as $process => $quantity) {
                    $order[$process] = $quantity;
                }

                // 将每个工序的报工小组作为单独的字段
                foreach ($report['工序报工小组'] as $process => $groups) {
                    $order[$process . '小组'] = implode(',', $groups);
                }
            }

            $result[] = $order;
        }

        $res['code'] = 0;
        $res['msg'] = '成功';
        $res['count'] = $total;
        $res['data'] = $result;
        return json($res);
    }



    public function Getvideolist(){
        if (!$this->request->isGet()) {
            $this->error('请求方式错误');
        }
        $params = $this->request->param();
        $search = input('search', '');
        $page = isset($params['page']) ? (int)$params['page'] : 1;
        $limit = isset($params['limit']) ? (int)$params['limit'] : 50;
        $where = [];
        if (!empty($search)) {
            $where['prompt'] = ['like', '%' . $search . '%'];
        }
        $list = Db::name('video')->where('mod_rq', null)
            ->where($where)
            ->order('id desc')
            ->limit(($page - 1) * $limit, $limit)
            ->select();
        $total = Db::name('video')->where('mod_rq', null)
            ->where($where)
            ->count();
        $res['code'] = 0;
        $res['msg'] = '成功';
        $res['count'] = $total;
        $res['data'] = $list;
        return json($res);
    }


        //video_691c078dbb648190a17625bbef815ce50cbc1621ce1702d7
    public function video()
    {
        $params = $this->request->param();

        $apiUrl = 'https://chatapi.onechats.ai/v1/videos';
        $apiKey = 'sk-sWW1GFlnjbrDRb1DkMEzePIxgdvLK6cZt0Qg93yDMVP2z1yN';
        $postData = [
            'prompt' => $params['prompt'],
            'model' => $params['model'],
            'seconds' => $params['seconds'],
            'size' => $params['size'],
        ];

        // 初始化CURL
        $ch = curl_init();
        curl_setopt($ch, CURLOPT_URL, $apiUrl);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
        curl_setopt($ch, CURLOPT_POST, true);
        curl_setopt($ch, CURLOPT_POSTFIELDS, $postData);
        curl_setopt($ch, CURLOPT_HTTPHEADER, [
            'Authorization: Bearer ' . $apiKey
        ]);
        curl_setopt($ch, CURLOPT_TIMEOUT, 300);
        curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
        curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, 0);
        curl_setopt($ch, CURLOPT_HEADER, true); // 获取响应头
        curl_setopt($ch, CURLOPT_VERBOSE, true); // 启用详细输出以进行调试
        // 创建临时文件来捕获详细的cURL输出
        $verbose = fopen('php://temp', 'w+');
        curl_setopt($ch, CURLOPT_STDERR, $verbose);
        // 执行请求
        $response = curl_exec($ch);
        //HTTP状态码
        $httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
        // 获取详细的cURL调试信息
        rewind($verbose);
        //CURL调试信息
        $verboseLog = stream_get_contents($verbose);
        fclose($verbose);
        // 分离头部和主体
        $header_size = curl_getinfo($ch, CURLINFO_HEADER_SIZE);
        //响应头部
        $header = substr($response, 0, $header_size);
        //响应主体
        $body = substr($response, $header_size);
        // 检查CURL错误
        $curlError = curl_error($ch);
        curl_close($ch);

        $responseData = json_decode($body, true);

        // 检查API是否返回了错误信息
        if (isset($responseData['error'])) {
            $errorMessage = isset($responseData['error']['message']) ? $responseData['error']['message'] : 'API请求失败';
            return json([
                'code' => 1,
                'msg' => '视频生成请求失败',
                'data' => [
                    'error_type' => isset($responseData['error']['type']) ? $responseData['error']['type'] : 'unknown',
                    'error_code' => isset($responseData['error']['code']) ? $responseData['error']['code'] : 'unknown',
                    'error_message' => $errorMessage
                ]
            ]);
        }

        // 检查是否有自定义错误格式
        if (isset($responseData['code']) && $responseData['code'] === 'fail_to_fetch_task' && isset($responseData['message'])) {
            return json([
                'code' => 1,
                'msg' => '视频生成请求失败',
                'data' => [
                    'error_code' => $responseData['code'],
                    'error_message' => $responseData['message']
                ]
            ]);
        }

        // 检查是否存在id字段
        if (!isset($responseData['id'])) {
            return json([
                'code' => 1,
                'msg' => '无法获取视频ID',
                'data' => [
                    'response_data' => $responseData,
                    'http_code' => $httpCode
                ]
            ]);
        }

        $videoData = [
            'video_id' => $responseData['id'],
            'prompt' => $postData['prompt'],
            'model' => $postData['model'],
            'seconds' => $postData['seconds'],
            'size' => $postData['size'],
            'sys_rq' => date("Y-m-d H:i:s")
        ];

        // 尝试插入数据
        try {
            $res = Db::name('video')->insert($videoData);
            return json([
                'code' => 0,
                'msg' => '视频正在生成中',
                'data' => [
                    'video_id' => $responseData['id'],
                    'insert_result' => $res
                ]
            ]);
        } catch (Exception $e) {
            return json([
                'code' => 1,
                'msg' => '数据库操作失败',
                'data' => [
                    'error_message' => $e->getMessage()
                ]
            ]);
        }
    }

     /**
     * 获取视频内容
     * 下载已完成的视频内容
     */
    public function videoContent(){
            // 从请求参数获取video_id，如果没有则使用默认值
            $video_id = input('get.video_id');
            $apiKey = 'sk-sWW1GFlnjbrDRb1DkMEzePIxgdvLK6cZt0Qg93yDMVP2z1yN';

            // 1. 先检查视频状态
            $statusUrl = 'https://chatapi.onechats.ai/v1/videos/' . $video_id;
            $statusData = $this->fetchVideoStatus($statusUrl, $apiKey);

            // 检查视频状态
            if ($statusData['status'] !== 'completed') {
                return json([
                    'code' => 202,
                    'msg' => '视频尚未生成完成',
                    'data' => [
                        'video_id' => $video_id,
                        'status' => $statusData['status'],
                        'progress' => $statusData['progress'],
                        'created_at' => $statusData['created_at'],
                        'message' => '请稍后再试，视频仍在' . ($statusData['status'] === 'queued' ? '排队中' : '处理中')
                    ]
                ]);
            }

            // 2. 视频生成完成，准备下载
            $apiUrl = 'https://chatapi.onechats.ai/v1/videos/' . $video_id . '/content';

            // 获取可选的variant参数
            $variant = input('get.variant', '');
            if (!empty($variant)) {
                $apiUrl .= '?variant=' . urlencode($variant);
            }

            // 创建保存目录
            $saveDir = ROOT_PATH . 'public' . DS . 'uploads' . DS . 'videos' . DS . date('Ymd');
            if (!is_dir($saveDir)) {
                mkdir($saveDir, 0755, true);
            }

            // 生成唯一文件名
            $filename = $video_id . '.mp4';
            $localPath = DS . 'uploads' . DS . 'videos' . DS . date('Ymd') . DS . $filename;
            $fullPath = $saveDir . DS . $filename;

            // 3. 下载视频
            $videoData = $this->downloadVideo($apiUrl, $apiKey);

            // 4. 保存视频文件
            if (file_put_contents($fullPath, $videoData) === false) {
                throw new Exception('视频保存失败');
            }

            // 确保路径使用正斜杠，并只保存相对路径部分
            $localPath = str_replace('\\', '/', $localPath);
            // 移除开头的斜杠，确保路径格式为uploads/videos/...
            $savePath = ltrim($localPath, '/');

            // 将正确格式的文件路径存入数据库
            Db::name('video')->where('video_id', $video_id)->update([
                'web_url' => $savePath
            ]);

            // 返回成功响应
            return json([
                'code' => 0,
                'msg' => '视频下载成功',
                'data' => [
                    'video_id' => $video_id,
                    'local_path' => $localPath,
                    'web_url' => $savePath,
                    'file_size' => filesize($fullPath)
                ]
            ]);
        }


    /**
     * 获取视频状态
     */
    private function fetchVideoStatus($url, $apiKey) {
        $ch = curl_init();
        curl_setopt($ch, CURLOPT_URL, $url);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
        curl_setopt($ch, CURLOPT_HTTPGET, true);
        curl_setopt($ch, CURLOPT_HTTPHEADER, [
            'Authorization: Bearer ' . $apiKey,
            'Accept: application/json'
        ]);
        curl_setopt($ch, CURLOPT_TIMEOUT, 30);
        curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
        curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, 0);

        $response = curl_exec($ch);
        $httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
        $error = curl_error($ch);
        curl_close($ch);

        if ($error) {
            throw new Exception('获取视频状态失败: ' . $error);
        }

        if ($httpCode < 200 || $httpCode >= 300) {
            throw new Exception('获取视频状态失败，HTTP状态码: ' . $httpCode);
        }

        $data = json_decode($response, true);
        if (!is_array($data)) {
            throw new Exception('视频状态数据格式错误');
        }

        return $data;
    }

    /**
     * 下载视频文件
     */
    private function downloadVideo($url, $apiKey) {
        $ch = curl_init();
        curl_setopt($ch, CURLOPT_URL, $url);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
        curl_setopt($ch, CURLOPT_HTTPGET, true);
        curl_setopt($ch, CURLOPT_HTTPHEADER, [
            'Authorization: Bearer ' . $apiKey
        ]);
        curl_setopt($ch, CURLOPT_TIMEOUT, 300);
        curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
        curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, 0);

        $response = curl_exec($ch);
        $httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
        $error = curl_error($ch);
        curl_close($ch);

        if ($error) {
            throw new Exception('视频下载失败: ' . $error);
        }

        if ($httpCode < 200 || $httpCode >= 300) {
            throw new Exception('视频下载失败，HTTP状态码: ' . $httpCode);
        }

        return $response;
    }

}