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

<?php

namespace app\api\controller;

use app\common\controller\Api;
use app\service\AIGatewayService;
use app\service\ImageService;
use think\Cache;
use \think\Request;
use \think\Db;
/**
 * 工单超节损核算接口
 */
class OrderSuperLoss extends Api
{
    protected $noNeedLogin = ['*'];
    protected $noNeedRight = ['*'];
    public function index()
    {
        $this->success('请求成功');
    }

    /**
     * 获取左侧菜单栏
     * @ApiMethod GET
     */
    public function getTab()
    {
        // 检查请求类型是否为 GET
        if (!Request::instance()->isGet()) {
            $this->error('非法请求');
        }

        // SQL 查询，连接 db3 数据库
        $sql = "SELECT   
                DATE_FORMAT(a.jjcp_sj, '%Y-%m') AS date,  -- 格式化为年月  
                RTRIM(b.`编号`) AS 编号,  
                RTRIM(b.`名称`) AS 客户名称,  
                COUNT(DISTINCT a.jjcp_gdbh) AS count   
            FROM   
                `成品入仓` a  
            LEFT JOIN   
                `物料_存货结构` b   
                ON (CASE   
                        WHEN LEFT(a.`jjcp_cpdh`, 1) REGEXP '^[A-Za-z]' THEN LEFT(a.`jjcp_cpdh`, 5)   
                        ELSE LEFT(a.`jjcp_cpdh`, 4)   
                    END) = RTRIM(b.`编号`)  -- 根据字母/数字判断连接方式  
            WHERE   
                a.jjcp_sj >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)  -- 最近12个月  
                AND a.jjcp_sj >= '2025-01-01 00:00:00'               -- 限定从2025年开始  
                AND a.`成品编码` != ''                                -- 编码不为空  
                AND a.jjcp_smb IN ('末 板', '末板')                 -- 状态筛选  
            GROUP BY   
                DATE_FORMAT(a.jjcp_sj, '%Y-%m'),  
                RTRIM(b.`编号`)   
            ORDER BY   
                date DESC,  
                编号 ASC";

        // 执行查询，确保连接的是 db3 数据库
        $db3 = \db()->connect(config('database.db3'));
        $data = $db3->query($sql);
        // 创建一个新数组存储整理后的数据
        $organizedData = [];

        // 遍历原始数据
        foreach ($data as $entry) {
            $date = date('Ym', strtotime($entry['date']));  // 格式化为年月，例如 202506

            // 如果当前月份已经有数据，追加数据
            if (isset($organizedData[$date])) {
                $organizedData[$date][] = [
                    '客户名称' => $entry['客户名称'],
                    '客户编号' => $entry['编号'],
                    'total' => $entry['count'],
                ];
                // 累加该月的总数
                $organizedData[$date]['total_count'] += $entry['count'];
            } else {
                // 否则初始化该月数据
                $organizedData[$date] = [
                    [
                        '客户名称' => $entry['客户名称'],
                        '客户编号' => $entry['编号'],
                        'total' => $entry['count'],
                    ]
                ];
                // 初始化总数
                $organizedData[$date]['total_count'] = $entry['count'];
            }
        }

        // 整理成最终格式，以 "年月-总数" 为键
        $finalData = [];
        foreach ($organizedData as $date => $entries) {
            $totalCount = $entries['total_count'];
            $index = $date . '-' . $totalCount;
            unset($entries['total_count']);  // 删除临时统计字段
            $finalData[$index] = $entries;
        }

        // 返回成功结果
        $this->success('请求成功', $finalData);
    }

    /**
     * 超节损列表
     */
    public function getList()
    {
        if (!Request::instance()->isGet()) {
            $this->error('非法请求');
        }

        $params = Request::instance()->param();
        $where = [];

        // 设置默认分页和限制
        $limit = empty($params['limit']) ? 15 : intval($params['limit']);
        $pages = empty($params['page']) ? 1 : intval($params['page']);

        // 构建查询条件
        if (!empty($params['code'])) {
            $prefixLength = preg_match('/[A-Za-z]/', $params['code']) ? 5 : 4;
            $where['a.jjcp_cpdh'] = ['LIKE', substr($params['code'], 0, $prefixLength) . '%'];
        }

        if (!empty($params['date'])) {
            $year = substr($params['date'], 0, 4);
            $month = substr($params['date'], 4, 2);
            $formattedDate = $year . '-' . $month;
            $where['a.jjcp_sj'] = ['like', $formattedDate . '%'];
        }

        if (!empty($params['search'])) {
            if (!empty($params['date']) || !empty($params['code'])) {
                $this->error('参数错误');
            }
            $where['a.jjcp_gdbh|a.jjcp_cpdh|a.jjcp_cpmc'] = ['like', '%' . $params['search'] . '%'];
        }

        // 数据库连接
        $db3 = \db()->connect(config('database.db3'));

        // 定义查询字段
        $field = '
        b.sys_ny,
        a.jjcp_gdbh AS Gd_gdbh,
        a.jjcp_yjno AS jjcp_yjno,
        a.jjcp_yjno AS yj_Yjno,
        c.yj_Yjdh as yjdh,
        c.yj_ls AS yj_ls,
        c.yj_cpdh AS 成品编码,
        c.yj_yjmc AS 成品名称,
        b.`实际投料`,
        b.`计量单位`,
        DATE(a.jjcp_sj) AS warehousing_date,
        total_sl.warehousing_num,
        SUM(DISTINCT b.`计划损耗`) AS `工单计划损耗`,
        b.`废品合计`,
        SUM(b.`CjsJe`) AS reward_money,
        b.`印件无形损` AS 工单无形损,
        b.`材料废`,
        b.`零头处理`,
        b.`外发废`,
        SUM(b.`班组制程废品`) AS zcfp,
        `工单质检废` AS 工单质检废,
        c.质量考核
    ';

        // 子查询：计算入仓数量
        $subQuery = $db3->name('成品入仓')
            ->alias('a_sub')
            ->field('
            a_sub.jjcp_gdbh AS Gd_gdbh_sub,
            a_sub.jjcp_yjno AS jjcp_yjno_sub,
            SUM(a_sub.jjcp_sl) AS warehousing_num
        ')
            ->group('a_sub.jjcp_gdbh, a_sub.jjcp_yjno')
            ->buildSql();

        // 主查询
        $list = $db3->name('成品入仓')
            ->alias('a')
            ->join('工单_质量考核汇总 b', 'a.jjcp_gdbh = b.Gy0_gdbh AND a.jjcp_yjno = b.印件及工序', 'left')
            ->join('工单_印件资料 c', 'a.jjcp_gdbh = c.Yj_Gdbh AND a.jjcp_yjno = c.yj_Yjno', 'left')
            ->join([$subQuery => 'total_sl'], 'a.jjcp_gdbh = total_sl.Gd_gdbh_sub AND a.jjcp_yjno = total_sl.jjcp_yjno_sub', 'left')
            ->field($field)
            ->where($where)
            ->where('a.jjcp_smb', 'like', '末%')
            ->group('Gd_gdbh, jjcp_yjno')
            ->order('warehousing_date DESC')
            ->page($pages, $limit)
            ->select();

        // 后续处理结果
        foreach ($list as $key => $value) {
            $status = $db3->name('工单_印件资料')
                ->where('Yj_Gdbh', $value['Gd_gdbh'])
                ->where('yj_Yjno', $value['jjcp_yjno'])
                ->value('质量考核');

            $list[$key]['工单质检废'] = $value['废品合计'] - $value['zcfp'];
            $list[$key]['质量考核'] = $status;
            $list[$key]['考核'] = ($status == 1) ? '否' : '是';

            if ($value['实际投料'] !== null) {
                $actualMaterial = $value['实际投料'] * 10000;
                $list[$key]['target_rate'] = $this->calculateRate($actualMaterial, $value['工单计划损耗']);
                $realRate = $value['warehousing_num'] / $actualMaterial * 100;
                $list[$key]['real_rate'] = number_format($realRate, 2) . '%';
            } else {
                $list[$key]['target_rate'] = null;
                $list[$key]['real_rate'] = null;
            }

            $list[$key]['reward_rate'] = '1'; // 如有实际逻辑，可替换
        }


        //只保留考核为“是”的数据
        $list = array_filter($list, function ($item) {
            return $item['考核'] === '是';
        });
        // 重新整理数组索引（重0开始）
        $list = array_values($list);

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

    // 计算合格率
    private function calculateRate($actual, $loss)
    {
        if ($actual == 0) {
            return '0.00%'; // 防止除以零
        }
        $rate = ($actual - $loss) / $actual * 100;
        return number_format($rate, 2) . '%';
    }


    /**
     * AI 超节损分析（月度）
     */
//    public function oversizedloss() {
//        $params = $this->request->param();
//        $service = new ImageService();
//        $service->chaojiesun_handleImage($params);
//        $this->success('任务成功提交至队列');
//    }


//    public function oversizedloss() {
//        // 读取原始 POST JSON body
//        $raw = $this->request->getInput();
//        $params = json_decode($raw, true);
//
//        if (empty($params['content'])) {
//            return json(['code' => 1, 'msg' => 'content 不能为空']);
//        }
//
//        // 取出 content
////        $promptData = $params['content']['content'];
//        $promptData = $params['content'];
//
//        // 统一处理为字符串
//        if (is_array($promptData)) {
//            // 如果是二维数组（多个工单）
//            if (isset($promptData[0]) && is_array($promptData[0])) {
//                $lines = array_map(function($row) {
//                    return implode(', ', array_map(
//                        fn($k, $v) => "$k: $v",
//                        array_keys($row),
//                        array_values($row)
//                    ));
//                }, $promptData);
//                $promptContent = implode("\n", $lines);
//            }
//            // 如果是一维数组（单个工单）
//            else {
//                $promptContent = implode(', ', array_map(
//                    fn($k, $v) => "$k: $v",
//                    array_keys($promptData),
//                    array_values($promptData)
//                ));
//            }
//        } else {
//            // 已经是字符串，无需处理
//            $promptContent = $promptData;
//        }
//
//        // 查询模板内容
//        $db3 = \db()->connect(config('database.db3'));
//        $list = $db3->name('workorder_template')->where("id",1)->find();
//
//        $prompt = $list['content'] . "\n" . $promptContent;
//
//        // 调用 AI
//        $ai = new AIGatewayService();
//        $gptRes = $ai->chaojiesunGptApi($prompt);
//
//        $gptText = trim($gptRes['choices'][0]['message']['content'] ?? '');
//
//        return json(['code' => 0, 'data' => $gptText]);
//    }
}