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

<?php

namespace app\api\controller;


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

/**
 * 设备运行跟踪
 */

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


    //月度产量统计菜单

    public function OutputSstatisticsMenu()
    {
        if ($this->request->isGet() === false){
            $this->error('请求错误');
        }
        $mouth = \db('设备_产量计酬')
            ->distinct(true)
            ->field('DATE_FORMAT(sczl_rq, "%Y-%m") AS month')
            ->order('month desc')
            ->select();
        $sist = \db('设备_基本资料')
            ->whereNotNull('设备编组')
            ->group('设备编组')
            ->column('rtrim(设备编组) as 设备编组');
        $data = [];
        foreach ($mouth as $key=>$value) {
            $arr = [
                'date'=>date('Ym',strtotime($value['month'])),
                'sbbh'=>$sist,
            ];
            array_push($data,$arr);
        }
        $this->success('成功',$data);
    }


    /**
     * 月度产量统计上方机台生产数据
     * @return void
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */

    public function MachineProduction()
    {
        if ($this->request->isGet() === false){
            $this->error('请求错误');
        }
        $param = $this->request->param();
        if (empty($param['mouth'])){
            $this->error('参数错误');
        }
        $where = [];
        if(!empty($param['sist'])){
            $where['设备编组'] = $param['sist'];
        }
        //将参数装换成标准日期格式
        $mouth = date_create_from_format('Ym', $param['mouth'])->format('Y-m');
        $machine = \db('设备_基本资料')
            ->where($where)
            ->where('sys_sbID','<>','')
            ->field('设备编号')
            ->order('设备编号')
            ->select();
        $day = \db('设备_产量计酬')
            ->distinct(true)
            ->field('DATE_FORMAT(sczl_rq,"%Y-%m-%d") as day')
            ->whereRaw("DATE_FORMAT(sczl_rq, '%Y-%m') = '$mouth'")
            ->order('day')
            ->select();
        $day = array_reduce($day, function($carry, $item) {
            return array_merge($carry, array_values($item));
        }, []);
        $data = [];
        $data['head'] = $day;
        foreach ($machine as $key=>$value){
            $data['total'][$key] = \db('设备_产量计酬')
                ->field('sczl_jtbh,sczl_bzdh,SUM(sczl_cl) as total_cl,sczl_rq')
                ->where('sczl_rq','like',$mouth.'%')
                ->where('sczl_jtbh',$value['设备编号'])
                ->group('sczl_bzdh')
                ->select();
            foreach ($data['total'][$key] as $k=>$v){
                $day_cl = \db('设备_产量计酬')
                    ->alias('a')
                    ->join('工单_印件资料 c','a.sczl_gdbh = c.Yj_Gdbh AND a.sczl_yjno = c.yj_Yjno')
                    ->join('工单_工艺资料 d','a.sczl_gdbh = d.Gy0_gdbh AND a.sczl_yjno = d.Gy0_yjno AND a.sczl_gxh = d.Gy0_gxh')
                    ->field([
                        'a.sczl_gdbh' => '工单编号',
                        'a.sczl_yjno' => '印件号',
                        'a.sczl_gxh' => '工序号',
                        'c.yj_yjmc' => '印件名称',
                        'CONCAT(d.Gy0_gxmc,"(",d.Add_gxmc,")")' => '工序名称',
                        'DATE(a.sczl_rq)' => 'day',
                        'a.sczl_jtbh' => '机台编号',
                        'a.sczl_bzdh' => '班组编号',
                        'SUM(a.sczl_cl)' => 'total_cl',
                        'a.sczl_ms' => '墨色数',
                        'rtrim(d.印刷方式)' => '印刷方式',
                        'rtrim(d.版距)' => '版距'
                    ])
                    ->where('a.sczl_rq','like',$mouth.'%')
                    ->where('a.sczl_jtbh', $value['设备编号'])
                    ->where('a.sczl_bzdh', $v['sczl_bzdh'])
                    ->group('sczl_bzdh,day')
                    ->select();
                $day_total = [];
                foreach ($day_cl as $index=>$item){
                    if ($item['印刷方式'] === '卷对卷'){
                        $day_cl[$index]['total_cl'] = round($item['total_cl']/$item['版距']*1000);
                    }
                    $day_total[$item['day']] =$day_cl[$index]['total_cl'];
                }
                $data['total'][$key][$k]['day_total'] = $day_total;
                $data['total'][$key][$k]['total_cl'] = array_sum($day_total);
            }
        }
        $this->success('成功',$data);
    }

    /**
     * 机台班次生产工单明细
     * @return void
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function MachineProductDetail()
    {
        if (!$this->request->isGet()) {
            $this->error('请求错误');
        }

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

        // 检查必需的参数
        if (empty($param['mouth'])) {
            $this->error('参数错误');
        }

        // Initializing where conditions
        $where = [];

        // 添加可选的查询条件
        if (!empty($param['machine'])) {
            $where['a.sczl_jtbh'] = $param['machine'];
        }

        if (!empty($param['team'])) {
            $where['a.sczl_bzdh'] = ['like', substr($param['team'], 0, 1) . '%'];
        }

        // 将参数转换成标准日期格式
        $mouth = date_create_from_format('Ym', $param['mouth'])->format('Y-m');
        $where['a.sczl_rq'] = ['like', $mouth . '%'];

        // 分页配置
        $page = !empty($param['page']) ? (int)$param['page'] : 1;
        $limit = !empty($param['limit']) ? (int)$param['limit'] : 9999; // 默认查询所有

        // 查询数据
        $list = \db('设备_产量计酬')
            ->alias('a')
            ->join('工单_印件资料 c', 'a.sczl_gdbh = c.Yj_Gdbh AND a.sczl_yjno = c.yj_Yjno')
            ->join('工单_工艺资料 d', 'a.sczl_gdbh = d.Gy0_gdbh AND a.sczl_yjno = d.Gy0_yjno AND a.sczl_gxh = d.Gy0_gxh')
            ->field([
                'a.sczl_gdbh' => '工单编号',
                'a.sczl_yjno' => '印件号',
                'a.sczl_gxh' => '工序号',
                'c.yj_yjmc' => '印件名称',
                'CONCAT(d.Gy0_gxmc,"(",d.Add_gxmc,")")' => '工序名称',
                'DATE(a.sczl_rq)' => '工作日期',
                'a.sczl_jtbh' => '机台编号',
                'a.sczl_bzdh' => '班组编号',
                'SUM(a.sczl_cl)' => '产量',
                'a.sczl_ms' => '墨色数',
                'rtrim(d.印刷方式)' => '印刷方式',
                'rtrim(d.版距)' => '版距'
            ])
            ->where($where)
            ->group('a.sczl_gdbh, a.sczl_yjno, a.sczl_gxh, a.sczl_rq, a.sczl_jtbh, a.sczl_bzdh')
            ->order('工作日期')
            ->page($page, $limit)  // 使用 page 和 limit 实现分页
            ->select();

        // 数据处理
        if (!empty($list)) {
            foreach ($list as $key => $value) {
                $list[$key]['印件名称'] = $value['印件号'] . '-' . $value['印件名称'];
                $list[$key]['工序名称'] = $value['工序号'] . '-' . $value['工序名称'];
                unset($list[$key]['印件号'], $list[$key]['工序号']);

                // 产量计算
                if ($value['印刷方式'] === '卷对卷') {
                    $list[$key]['产量'] = round($value['产量'] / $value['版距'] * 1000);
                }

                // 墨色数修正
                if ($value['墨色数'] === '0.00') {
                    $list[$key]['墨色数'] = '1.00';
                }
            }
        }

        $this->success('成功', $list);
    }
    /**
     * 月度机台运行工时汇总
     * @return void
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function MachineOperation()
    {
        if ($this->request->isGet() === false){
            $this->error('请求错误');
        }
        $param = $this->request->param();
        if (empty($param['mouth'])){
            $this->error('参数错误');
        }
        $where = [];
        if(!empty($param['sist'])){
            $where['a.设备编组'] = $param['sist'];
        }
        //将参数装换成标准日期格式
        $mouth = date_create_from_format('Ym', $param['mouth'])->format('Y-m');
        $list = \db('设备_基本资料')
            ->alias('a')
            ->join('设备_产量计酬 b', 'a.设备编号 = b.sczl_jtbh')
            ->join('工单_印件资料 c', 'b.sczl_gdbh = c.Yj_Gdbh AND b.sczl_yjno = c.yj_Yjno')
            ->join('工单_工艺资料 d', 'b.sczl_gdbh = d.Gy0_gdbh AND b.sczl_yjno = d.Gy0_yjno AND b.sczl_gxh = d.Gy0_gxh')
            ->field([
                'a.设备编号' => '设备编号',
                'rtrim(a.设备名称)' => '设备名称',
                'SUM(CASE WHEN rtrim(d.版距) = "卷对卷" THEN b.sczl_cl / NULLIF(d.版距, 0) * 1000 ELSE b.sczl_cl END) AS 产量',
                'SUM(b.sczl_设备运行工时)' => '设备运行工时',
                'SUM(b.sczl_保养工时)' => '保养工时',
                'SUM(b.sczl_打样总工时)' => '打样总工时',
                'SUM(b.sczl_打样工时)' => '打样补产工时',
                'SUM(b.sczl_装版总工时)' => '装版总工时',
                'SUM(b.sczl_装版工时)' => '装板补产工时',
                'SUM(b.sczl_异常停机工时)' => '异常停机工时',
                'rtrim(d.印刷方式)' => '印刷方式',
                'rtrim(d.版距)' => '版距'
            ])
            ->where($where)
            ->where('b.sczl_rq','like', $mouth.'%')
            ->group('a.设备编号')
            ->order('a.设备编号')
            ->select();
        $total = \db('设备_基本资料')
            ->alias('a')
            ->join('设备_产量计酬 b','a.设备编号 = b.sczl_jtbh')
            ->field([
                'SUM(b.sczl_设备运行工时)' => '设备运行工时',
                'SUM(b.sczl_保养工时)' => '保养工时',
                'SUM(b.sczl_打样总工时)' => '打样总工时',
                'SUM(b.sczl_打样工时)' => '打样补产工时',
                'SUM(b.sczl_装版总工时)' => '装板总工时',
                'SUM(b.sczl_装版工时)' => '装板补产工时',
                'SUM(b.sczl_异常停机工时)' => '异常停机工时'
            ])
            ->where($where)
            ->where('b.sczl_rq','like', $mouth.'%')
            ->find();
        $list['total'] = $total;
        $this->success('成功',$list);
    }

    /**
     * 设备运行工时机台生产工单数据详情
     * @return void
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function MachineOperationProductDetail()
    {
        if (!$this->request->isGet()) {
            $this->error('请求错误');
        }

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

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

        // Initialize where conditions
        $where = [];

        if (!empty($param['machine'])) {
            $where['a.sczl_jtbh'] = $param['machine'];
        }

        // 将参数转换成标准日期格式
        $mouth = date_create_from_format('Ym', $param['mouth'])->format('Y-m');
        $where['a.sczl_rq'] = ['like', $mouth . '%'];

        // 分页配置
        $page = !empty($param['page']) ? (int)$param['page'] : 1;
        $limit = !empty($param['limit']) ? (int)$param['limit'] : 9999; // 默认查询所有

        // 查询数据
        $list = \db('设备_产量计酬')
            ->alias('a')
            ->join('设备_基本资料 b', 'a.sczl_jtbh = b.设备编号')
            ->join('工单_印件资料 c', 'a.sczl_gdbh = c.Yj_Gdbh AND a.sczl_yjno = c.yj_Yjno')
            ->join('工单_工艺资料 d', 'a.sczl_gdbh = d.Gy0_gdbh AND a.sczl_yjno = d.Gy0_yjno AND a.sczl_gxh = d.Gy0_gxh')
            ->field([
                'a.sczl_jtbh' => '设备编号',
                'rtrim(b.设备名称)' => '设备名称',
                'DATE(a.sczl_rq)' => '日期',
                'a.sczl_gdbh' => '工单编号',
                'a.sczl_yjno' => '印件号',
                'a.sczl_gxh' => '工序号',
                'c.yj_yjmc' => '印件名称',
                'CONCAT(d.Gy0_gxmc, "(", d.Add_gxmc, ")")' => '工序名称',
                'SUM(a.sczl_cl)' => '产量',
                'SUM(a.sczl_设备运行工时)' => '设备运行工时',
                'SUM(a.sczl_保养工时)' => '保养工时',
                'SUM(a.sczl_打样总工时)' => '打样总工时',
                'SUM(a.sczl_打样工时)' => '打样补产工时',
                'SUM(a.sczl_装版总工时)' => '装板总工时',
                'SUM(a.sczl_装版工时)' => '装板补产工时',
                'SUM(a.sczl_异常停机工时)' => '异常停机工时',
                'a.sczl_ms' => '墨色数',
                'rtrim(d.印刷方式)' => '印刷方式',
                'rtrim(d.版距)' => '版距'
            ])
            ->where($where)
            ->group('a.sczl_rq, a.sczl_gdbh, a.sczl_gxh, a.sczl_jtbh')
            ->order('a.sczl_rq')
            ->page($page, $limit)  // 使用 page 和 limit 技术实现分页
            ->select();

        // 数据处理
        if (!empty($list)) {
            foreach ($list as $key => $value) {
                $list[$key]['工序名称'] = $value['印件号'] . '-' . $value['工序号'] . '-' . $value['工序名称'];

                // 移除不需要的字段
                unset($list[$key]['印件号'], $list[$key]['工序号']);

                // 产量计算
                if ($value['印刷方式'] === '卷对卷') {
                    $list[$key]['产量'] = round($value['产量'] / $value['版距'] * 1000);
                }

                // 墨色数修正
                if ($value['墨色数'] === '0.00') {
                    $list[$key]['墨色数'] = '1.00';
                }
            }
        }

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

    /**
     * 工序产出率月度统计报表
     * @return void
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function ProcessOutputRate()
    {
        if ($this->request->isGet() === false){
            $this->error('请求错误');
        }
        $param = $this->request->param();
        if (empty($param)){
            $this->error('参数错误');
        }
        //查询已经进入超节损的月份
        $mouth = \db('工单_质量考核汇总')
            ->where('sys_ny','like',$param['year'].'%')
            ->column('distinct(sys_ny) as mouth');
        //创建工序数组
        $processType = ['胶印','卷凹','圆烫','圆切','烫金','模切','丝印','喷码','单凹'];
        $result = $data = [];
        foreach ($mouth as $key=>$value){
            foreach ($processType as $item){
                //查询进入超节损一年内所有数据
                $list = \db('工单_质量考核汇总')
                    ->alias('a')
                    ->join('工单_工艺资料 b','a.Gy0_gdbh = b.Gy0_gdbh AND a.印件及工序 = b.Gy0_yjno AND a.工序 = b.Gy0_gxh')
                    ->join('工单_印件资料 c','a.Gy0_gdbh = c.Yj_Gdbh AND a.印件及工序 = c.yj_Yjno')
                    ->field('a.sys_ny,(a.计划损耗+sum(a.班组质检废品))/a.印件工序产量 as 废品率')
                    ->where('b.Gy0_gxmc','like',$item.'%')
                    ->where('a.sys_ny',$value)
                    ->group('a.Gy0_gdbh,b.Gy0_yjno,a.工序')
                    ->select();
                $data[$value][$item] = $list;
            }
        }
        $months = [
            $param['year'].'01' => '01月',
            $param['year'].'02' => '02月',
            $param['year'].'03' => '03月',
            $param['year'].'04' => '04月',
            $param['year'].'05' => '05月',
            $param['year'].'06' => '06月',
            $param['year'].'07' => '07月',
            $param['year'].'08' => '08月',
            $param['year'].'09' => '09月',
            $param['year'].'10' => '10月',
            $param['year'].'11' => '11月',
            $param['year'].'12' => '12月'
        ];
        //汇总数据
        foreach ($processType as $index => $process) {
            $result[$index] = array(
                "工序类型" => $process,
                "01月" => "",
                "02月" => "",
                "03月" => "",
                "04月" => "",
                "05月" => "",
                "06月" => "",
                "07月" => "",
                "08月" => "",
                "09月" => "",
                "10月" => "",
                "11月" => "",
                "12月" => ""
            );

            // 遍历月份
            foreach ($months as $month => $monthName) {
                if (isset($data[$month])){
                    $count = count($data[$month][$process]);
                }

                $totalRate = 0;

                // 检查月份是否存在于数据中
                if (isset($data[$month])) {
                    // 检查工序是否存在于月份数据中
                    if (isset($data[$month][$process])) {
                        // 累加废品率
                        foreach ($data[$month][$process] as $rate) {
                            $totalRate += floatval(1-$rate["废品率"]);
                        }
                    }
                }
                if ($count > 0){
                    $rateNumber = $totalRate/$count;
                }
                // 格式化为百分比字符串，保留两位小数
                if ($totalRate > 0) {
                    $result[$index][$monthName] = sprintf("%.2f%%", $rateNumber * 100);
                } else {
                    $result[$index][$monthName] = ""; // 如果没有数据，则为空字符串
                }
            }
        }
        foreach ($result as &$item) {
            $total = 0;
            $count = 0;

            // 遍历月份，计算总和和数量
            for ($i = 1; $i <= 12; $i++) {
                $month = sprintf("%02d月", $i); // 格式化月份，例如 "01月"
                if (isset($item[$month]) && $item[$month] !== "") {
                    // 移除百分号，并将字符串转换为浮点数
                    $value = floatval(str_replace("%", "", $item[$month]));
                    $total += $value;
                    $count++;
                }
            }

            // 计算平均值
            if ($count > 0) {
                $average = $total / $count;
                // 格式化为百分比字符串，保留两位小数
                $item["平均值"] = sprintf("%.2f%%", $average);
            } else {
                $item["平均值"] = "0.00%"; // 如果没有数据，则为 0.00%
            }
        }
        $this->success('成功',$result);
    }
    /**
     * 获取年分数据
     * @return void
     */
    public function GetYear()
    {
        if ($this->request->isGet() === false){
            $this->error('请求错误');
        }
        $data = \db('工单_质量考核汇总')
                ->group('year')
                ->column('YEAR(STR_TO_DATE(sys_ny, "%Y%m")) as year');
        $this->success('成功',$data);
    }

    /**
     * 数据透视表
     * @return null
     * @throws \think\Exception
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function PoductData()
    {
        if (!$this->request->isGet()) {
            $this->error('请求错误');
        }

        $param = $this->request->param();
        if (empty($param['year'])) {
            $this->error('参数错误');
        }

        // 定义分类规则及顺序
        $processOrder = [
            1 => ['name' => '胶印工序', 'keys' => ['胶印', '上光']],
            2 => ['name' => '凹印工序', 'keys' => ['卷凹']],
            3 => ['name' => '圆烫工序', 'keys' => ['圆烫']],
            4 => ['name' => '圆切工序', 'keys' => ['圆切']],
            5 => ['name' => '烫模工序', 'keys' => ['烫金', '模切', '凹凸']],
            6 => ['name' => '丝印工序', 'keys' => ['丝印']],
            7 => ['name' => '喷码工序', 'keys' => ['喷码']],
            8 => ['name' => '单凹工序', 'keys' => ['单凹']]
        ];

        // 构建基础查询
        $query = \db('工单_质量考核汇总')
                ->alias('a')
                ->field('
                a.sczl_jtbh AS 机台编号,
                a.Gy0_gdbh AS 工单编号,
                a.印件及工序 AS 工序号,
                a.产品名称 AS 印件名称,
                a.工序 AS 工序,
                a.工序名称,
                a.联数,
                a.班组产量,
                a.班组制程废品,
                a.班组质检废品,
                a.sczl_bzdh AS 班组编号,
                b.印刷方式,
                b.版距,
                DATE_FORMAT(a.入仓日期, \'%Y%m\') AS 完工年月
            ')
                ->join('工单_工艺资料 b', 'a.Gy0_gdbh = b.Gy0_gdbh AND a.印件及工序 = b.Gy0_yjno AND a.工序 = b.Gy0_gxh')
                ->where('a.sys_ny', 'like', $param['year'] . '%')
                ->where('a.工序名称','not like','%机检%')
                ->where('a.工序名称','not like','%拆片%');
        // 获取总数
        $total = clone $query;
        $total = $total->count();

        // 执行查询
        $rawList = isset($param['page'], $param['limit'])
            ? $query->page($param['page'])->limit($param['limit'])->select()
            : $query->select();

        // 处理分类标记
        $processedList = [];
        foreach ($rawList as $item) {
            $matched = false;

            // 顺序匹配分类规则
            foreach ($processOrder as $typeId => $rule) {
                foreach ($rule['keys'] as $keyword) {
                    if (strpos($item['工序名称'], $keyword) !== false) {
                        // 添加分类标记
                        $item['type_id'] = $typeId;
                        $item['type_name'] = $rule['name'];
                        $processedList[] = $item;
                        $matched = true;
                        break 2; // 跳出两层循环
                    }
                }
            }

            // 未匹配到分类的数据丢弃
        }

        // 按分类顺序排序（保持1-8的顺序）
        usort($processedList, function($a, $b) {
            return $a['type_id'] <=> $b['type_id'];
        });

        // 返回结构
        $data['data'] = $processedList;
        $data['total'] = $total;              // 原始总数
        $data['filtered_total'] = count($processedList); // 实际有效数

        return count($processedList) > 0
            ? $this->success('成功', $data)
            : $this->error('未找到数据');
    }
}