<?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['b.设备编组'] = $param['sist'];
        }

        // 转换日期格式
        $mouth = date_create_from_format('Ym', $param['mouth'])->format('Y-m');

        // 使用单个查询获取所有需要的数据
        $results = \db('设备_产量计酬')
            ->alias('a')
            ->join('设备_基本资料 b', 'a.sczl_jtbh = b.设备编号')
            ->join('工单_印件资料 c', 'a.sczl_gdbh = c.Yj_Gdbh AND a.sczl_yjno = c.yj_Yjno', 'LEFT')
            ->join('工单_工艺资料 d', 'a.sczl_gdbh = d.Gy0_gdbh AND a.sczl_yjno = d.Gy0_yjno AND a.sczl_gxh = d.Gy0_gxh', 'LEFT')
            ->field([
                'a.sczl_jtbh',
                'a.sczl_bzdh',
                'DATE_FORMAT(a.sczl_rq, "%Y-%m-%d") as day',
                'SUM(a.sczl_cl) AS total_cl',
                'c.yj_yjmc AS 印件名称',
                'CONCAT(d.Gy0_gxmc,"(",d.Add_gxmc,")") AS 工序名称',
                'RTRIM(d.印刷方式) AS 印刷方式',
                'RTRIM(d.版距) AS 版距'
            ])
            ->where('a.sczl_rq', 'like', $mouth . '%')
            ->where('b.sys_sbID', '<>', '')
            ->where($where)
            ->group('a.sczl_jtbh, a.sczl_bzdh, day, c.yj_yjmc, d.Gy0_gxmc')
            ->order('a.sczl_jtbh, a.sczl_bzdh, day')
            ->select();

        // 数据处理
        $data = [];
        // 获取唯一日期并排序
        $data['head'] = array_unique(array_column($results, 'day'));

        // 将日期转为时间戳以排序
        usort($data['head'], function($a, $b) {
            return strtotime($a) - strtotime($b);
        });

        // 准备按设备和班组整理的数据
        $total_by_machine = [];

        foreach ($results as $item) {
            $machine_id = $item['sczl_jtbh'];
            $group_id = $item['sczl_bzdh'];
            $day = $item['day'];
            $printed_type = $item['印刷方式'];

            // 初始化设备和班组
            if (!isset($total_by_machine[$machine_id])) {
                $total_by_machine[$machine_id] = [];
            }
            if (!isset($total_by_machine[$machine_id][$group_id])) {
                $total_by_machine[$machine_id][$group_id] = [
                    '机台编号' => $machine_id,
                    '班组编号' => $group_id,
                    '印件名称' => $item['印件名称'],
                    '工序名称' => $item['工序名称'],
                    'total_cl' => 0,
                    'day_total' => []
                ];
            }

            // 更新累计数量
            if ($printed_type === '卷对卷') {
                $total_by_machine[$machine_id][$group_id]['total_cl'] += round($item['total_cl'] / $item['版距'] * 1000);
            } else {
                $total_by_machine[$machine_id][$group_id]['total_cl'] += $item['total_cl'];
            }

            // 更新每日总计
            if (!isset($total_by_machine[$machine_id][$group_id]['day_total'][$day])) {
                $total_by_machine[$machine_id][$group_id]['day_total'][$day] = 0;
            }

            if ($printed_type === '卷对卷') {
                $total_by_machine[$machine_id][$group_id]['day_total'][$day] += round($item['total_cl'] / $item['版距'] * 1000);
            } else {
                $total_by_machine[$machine_id][$group_id]['day_total'][$day] += $item['total_cl'];
            }
        }

        // 格式化输出数据
        $data['total'] = [];

        foreach ($total_by_machine as $machine_group) {
            foreach ($machine_group as $group_data) {
                $data['total'][] = $group_data;
            }
        }

        $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()
    {
        // 确保请求是GET
        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 = [
            '设备运行工时' => 0,
            '保养工时' => 0,
            '打样总工时' => 0,
            '打样补产工时' => 0,
            '装版总工时' => 0,
            '装版补产工时' => 0,
            '异常停机工时' => 0
        ];

        // 计算各项总工时
        foreach ($list as $item) {
            $total['设备运行工时'] += $item['设备运行工时'];
            $total['保养工时'] += $item['保养工时'];
            $total['打样总工时'] += $item['打样总工时'];
            $total['打样补产工时'] += $item['打样补产工时'];
            $total['装版总工时'] += $item['装版总工时'];
            $total['装版补产工时'] += $item['装版补产工时'];
            $total['异常停机工时'] += $item['异常停机工时'];
        }

        // 将总工时添加到列表中
        $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()) {
            $this->error('请求错误');
        }

        $param = $this->request->param();
        if (empty($param)) {
            $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' => ['单凹']]
        ];

        // 获取存在数据的月份
        $monthsWithData = \db('工单_质量考核汇总')
            ->where('sys_ny', 'like', $param['year'] . '%')
            ->column('distinct(sys_ny) as month');

        // 初始化结果数组，按工序名称归类
        $result = [];
        foreach ($processOrder as $processId => $process) {
            $result[$process['name']] = [
                '01月' => '',
                '02月' => '',
                '03月' => '',
                '04月' => '',
                '05月' => '',
                '06月' => '',
                '07月' => '',
                '08月' => '',
                '09月' => '',
                '10月' => '',
                '11月' => '',
                '12月' => '',
                '平均值' => '0.00%'
            ];
        }

        // 遍历每个月份
        foreach ($monthsWithData as $month) {
            foreach ($processOrder as $processId => $process) {
                // 查询该月份该工序的合格率数据
                $query = \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')
                    ->join('成品入仓 d', 'a.Gy0_gdbh = d.jjcp_gdbh AND a.印件及工序 = d.jjcp_yjno')
                    ->field('a.sys_ny,   
                        (1 - ((SUM(a.班组制程废品) + SUM(a.班组质检废品)) / SUM(a.班组产量))) * 100 as 合格率')
                    ->where(function ($query) use ($process) {
                        foreach ($process['keys'] as $key) {
                            $query->whereOr('a.工序名称', 'like', "%{$key}%");
                        }
                    })
                    ->where('d.jjcp_smb', 'like', '末%')
                    ->where('a.sys_ny', $month)
                    ->group('a.sys_ny');

                $records = $query->select();

                if (!empty($records)) {
                    $monthName = substr($month, 4, 2) . '月';
                    $result[$process['name']][$monthName] = sprintf("%.2f%%", $records[0]['合格率']);
                }
            }
        }

        // 计算年度平均值
        foreach ($result as $processName => &$processData) {
            $totalAnnual = 0;
            $monthCount = 0;

            foreach ($processData as $month => $rate) {
                if ($month !== '平均值' && $rate !== '') {
                    $totalAnnual += floatval(str_replace('%', '', $rate));
                    $monthCount++;
                }
            }

            if ($monthCount > 0) {
                $processData['平均值'] = sprintf("%.2f%%", $totalAnnual / $monthCount);
            }
        }

        // 转换为目标格式
        $formattedData = [];
        foreach ($result as $processName => $processData) {
            $formattedData[] = array_merge(['工序类型' => $processName], $processData);
        }

        // 返回JSON响应
       $this->success('成功',$formattedData);
    }
    /**
     * 获取年分数据
     * @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')
                ->join('成品入仓 c','a.Gy0_gdbh = c.jjcp_gdbh AND a.印件及工序 = c.jjcp_yjno')
                ->where('a.sys_ny', 'like', $param['year'] . '%')
                ->where('a.工序名称','not like','%机检%')
                ->where('c.jjcp_smb','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('未找到数据');
    }
}