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

<?php

namespace app\api\controller;

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

/**
 * 首页接口
 */
class Index extends Api
{
    protected $noNeedLogin = ['*'];
    protected $noNeedRight = ['*'];

    /**
     * 首页
     *
     */
    public function index()
    {
        $this->success('请求成功');
    }

    //计划/排程/制程工单数量同步Redis
    public function numDbToRedis()
    {
        $numDbToRedis = [];

        //获取计划中的订单数量
        $jhz = db('工单_基本资料')
            ->where('gd_statu','like','%计划中%')
            ->count('Gd_gdbh');

        if($jhz!==false){
            $numDbToRedis['jhz'] = $jhz;
        }else{
            $numDbToRedis['jhz'] = 0;
        }

        //获取生产中的所有数据
        $rows = db('工单_基本资料')
            ->distinct('Gd_gdbh')
            ->where('gd_statu','like','%生产中%')
            ->column('Gd_gdbh');

        if($rows===false){
            $numDbToRedis['pcz'] = 0;
            $numDbToRedis['zcz'] = 0;
            $bool = cache('numDbToRedis',$jhz);
            if($bool===false) $this->error('订单数量存入redis失败','','000001');
            $this->success('请求成功');
        }

        //查询Gy0_sj1不等于1900-01-01 00:00:00的数据
        $data = db('工单_印件资料')->alias('y')
            ->join('工单_工艺资料 g','y.Yj_Gdbh = g.Gy0_gdbh AND y.yj_Yjno = g.Gy0_yjno')
            ->where('g.Gy0_sj1','<>','1900-01-01 00:00:00')
            ->where('y.Yj_Gdbh','in',$rows)
            ->group('y.Yj_Gdbh')
            ->column('y.Yj_Gdbh');
        $zcz = count($data);
        $pcz = count($rows) - count($data);

        $numDbToRedis['pcz'] = $pcz;
        $numDbToRedis['zcz'] = $zcz;
        $bool = cache('numDbToRedis',$numDbToRedis);
        if($bool===false) $this->error('订单数量存入redis失败','','000002');
        $this->success('同步成功');
    }

    //获取计划中工单数量
    public function getJhzByRedis()
    {
        $row = cache('numDbToRedis');
        if($row){
            $data = [
                'status'=>0,
                'msg'=>'',
                'data'=>[
                    'name'=>'',
                    'value'=>$row['jhz']
                ]
            ];
            return json($data);
        }else{
            $data = [
                'status'=>0,
                'msg'=>'失败',
                'data'=>[
                    'name'=>'',
                    'value'=>''
                ]
            ];
            return json($data);
        }
    }

    //获取排程中工单数量
    public function getPczByRedis()
    {
        $row = cache('numDbToRedis');
        if($row){
            $data = [
                'status'=>0,
                'msg'=>'',
                'data'=>[
                    'name'=>'',
                    'value'=>$row['pcz']
                ]
            ];
            return json($data);
        }else{
            $data = [
                'status'=>0,
                'msg'=>'失败',
                'data'=>[
                    'name'=>'',
                    'value'=>''
                ]
            ];
            return json($data);
        }
    }

    //获取制程中工单数量
    public function getZczByRedis()
    {
        $row = cache('numDbToRedis');
        if($row){
            $data = [
                'status'=>0,
                'msg'=>'',
                'data'=>[
                    'name'=>'',
                    'value'=>$row['zcz']
                ]
            ];
            return json($data);
        }else{
            $data = [
                'status'=>0,
                'msg'=>'失败',
                'data'=>[
                    'name'=>'',
                    'value'=>''
                ]
            ];
            return json($data);
        }
    }

    //印刷车间生产进度数据同步Redis
    public function yscjDbToRedis()
    {
        $yscjDbToRedis = [];

        //获取所有印刷车间
        // $rows = db('设备_基本资料')
        //     ->where('使用部门','凹丝印车间')
        //     ->column('设备编号');
        $rows = db('设备_基本资料')
            ->whereIn('使用部门', ['凹丝印车间', '胶印车间'])
            ->column('设备编号');

        foreach($rows as $value){
            $row = db('设备_产量采集')->alias('c')
                ->field('c.设备编号 as 机台号, 
                    c.工单编号 as 在产工单号, 
                    j.Gd_cpmc as 工单名称, 
                    c.工序名称 as 工序, 
                    g.Gy0_计划接货数 as 计划产量, 
                    g.Gy0_小时产能 as 小时产能, 
                    c.当前状态 as 机台状态
                    ')
                ->join('工单_工艺资料 g','c.设备编号 = g.Gy0_sbbh AND c.工单编号 = g.Gy0_gdbh AND c.印件号 = g.Gy0_yjno')
                ->join('工单_基本资料 j','j.Gd_gdbh = c.工单编号')
                ->where('c.设备编号',$value)
                ->order('c.UniqId desc')
                ->find();
            if(!$row) continue;
            $cl = db('设备_产量计酬')
                ->where(['sczl_gdbh'=>$row['在产工单号'], 'sczl_jtbh' => $row['机台号']])
                ->sum('sczl_cl');
            $row['已完成产量'] = $cl;
            array_push($yscjDbToRedis, $row);
        }
        $bool = cache('yscjDbToRedis',$yscjDbToRedis);
        if($bool===false) $this->error('存入redis失败','','000002');
        $this->success('同步成功');
    }

    //印后车间生产进度数据同步Redis
    public function yhcjDbToRedis()
    {
        $yhcjDbToRedis = [];

        //获取所有印后车间
        $rows = db('设备_基本资料')
            ->where('使用部门','印后车间')
            ->column('设备编号');

        foreach($rows as $value){
            $row = db('设备_产量采集')->alias('c')
                ->field('c.设备编号 as 机台号, 
                    c.工单编号 as 在产工单号, 
                    j.Gd_cpmc as 工单名称, 
                    c.工序名称 as 工序, 
                    g.Gy0_计划接货数 as 计划产量, 
                    g.Gy0_小时产能 as 小时产能, 
                    c.当前状态 as 机台状态
                    ')
                ->join('工单_工艺资料 g','c.设备编号 = g.Gy0_sbbh AND c.工单编号 = g.Gy0_gdbh AND c.印件号 = g.Gy0_yjno')
                ->join('工单_基本资料 j','j.Gd_gdbh = c.工单编号')
                ->where('c.设备编号',$value)
                ->order('c.UniqId desc')
                ->find();
            if(!$row) continue;
            $cl = db('设备_产量计酬')
                ->where(['sczl_gdbh'=>$row['在产工单号'], 'sczl_jtbh' => $row['机台号']])
                ->sum('sczl_cl');
            $row['已完成产量'] = $cl;
            array_push($yhcjDbToRedis, $row);
        }
        $bool = cache('yhcjDbToRedis',$yhcjDbToRedis);
        if($bool===false) $this->error('存入redis失败','','000002');
        $this->success('同步成功');
    }

    //获取印刷车间生产进度数据
    public function getYscjByRedis()
    {
        $row = cache('yscjDbToRedis');

        if (empty($row)) {
            return json([
                'status' => 0,
                'msg' => '',
                'data' => [] // 或者可以返回 []
            ]);
        }

        $result['columns'] = [
            ['name' => '机台号', 'id' => 'jtbh', 'width' => '11', 'autoWrap' => "true", 'textAlign' => 'left'],
            ['name' => '在产工单号', 'id' => 'gdbh', 'width' => '15', 'textAlign' => 'left'],
            ['name' => '工单名称', 'id' => 'gdmc', 'width' => '35', 'autoWrap' => "true", 'textAlign' => 'left'],
            ['name' => '工序', 'id' => 'gx', 'width' => '15', 'textAlign' => 'left'],
            ['name' => '计划产量', 'id' => 'jhcl', 'width' => '8', 'autoWrap' => "true", 'textAlign' => 'left'],
            ['name' => '已完成产量', 'id' => 'wccl', 'width' => '9', 'autoWrap' => "true", 'textAlign' => 'center'],
            ['name' => '小时产能', 'id' => 'xscn', 'width' => '8', 'autoWrap' => "true", 'textAlign' => 'center'],
            ['name' => '机台状态', 'id' => 'status', 'width' => '8', 'autoWrap' => "true", 'textAlign' => 'center'],
        ];

        foreach ($row as $k => $v) {
            $result['rows'][$k] = [
                'jtbh' => $v['机台号'],
                'gdbh' => $v['在产工单号'],
                'gdmc' => $v['工单名称'],
                'gx'   => $v['工序'],
                'jhcl' => $v['计划产量'],
                'wccl' => $v['已完成产量'],
                'xscn' => $v['小时产能'],
                'status' => $v['机台状态'],
            ];
        }

        return json([
            'status' => 0,
            'msg' => '',
            'data' => $result
        ]);
    }

    //获取印后车间生产进度数据
    public function getYhcjByRedis()
    {
        $row = cache('yhcjDbToRedis');
        $result['columns']=[
            ['name'=>'机台号','id'=>'jtbh','width'=>'11','autoWrap'=>"true",'textAlign'=>'left'],
            ['name'=>'在产工单号','id'=>'gdbh','width'=>'15','textAlign'=>'left'],
            ['name'=>'工单名称','id'=>'gdmc','width'=>'35','autoWrap'=>"true",'textAlign'=>'left'],
            ['name'=>'工序','id'=>'gx','width'=>'15','textAlign'=>'left'],
            ['name'=>'计划产量','id'=>'jhcl','width'=>'8','autoWrap'=>"true",'textAlign'=>'left'],
            ['name'=>'已完成产量','id'=>'wccl','width'=>'9','autoWrap'=>"true",'textAlign'=>'center'],
            ['name'=>'小时产能','id'=>'xscn','width'=>'8','autoWrap'=>"true",'textAlign'=>'center'],
            ['name'=>'机台状态','id'=>'status','width'=>'8','autoWrap'=>"true",'textAlign'=>'center'],
        ];
        if($row){
            foreach($row as $k=>$v){
                $result['rows'][$k]['jtbh']=$v['机台号'];
                $result['rows'][$k]['gdbh']=$v['在产工单号'];
                $result['rows'][$k]['gdmc']=$v['工单名称'];
                $result['rows'][$k]['gx']=$v['工序'];
                $result['rows'][$k]['jhcl'] = $v['计划产量'];
                $result['rows'][$k]['wccl'] = $v['已完成产量'];
                $result['rows'][$k]['xscn'] = $v['小时产能'];
                $result['rows'][$k]['status'] = $v['机台状态'];
            }
        }

        $res['status']=0;
        $res['msg']='';
        $res['data']=$result;
        return json($res);
    }

    //车间产量数据同步Redis
    public function jtClDbToRedis()
    {
        $jtClDbToRedis = [];

        //获取机台
        $rows = db('设备_基本资料')
            ->whereNotNull('sys_sbID')
            ->column('设备编号');

        //获取24小时内的hour组成数组
        $hour = date('H');
        $arr1 = range($hour+1,'23');
        $arr2 = range('00',$hour);
        $arr = array_merge($arr1,$arr2);
        $arr = array_map(function($num) {
            return sprintf("%02d", $num);
        }, $arr);

        foreach($rows as $value){
            $brr = [];
            //查询该机台24内的产量数据
            $row = db('设备_产量计酬')
                ->where('sys_rq', '>', date('Y-m-d H:i:s', strtotime('-23 hours')))
                ->where('sczl_jtbh', $value)
                ->group('LEFT(sys_rq, 13)')
                ->order('sys_rq')
                ->column('DATE_FORMAT(sys_rq, "%H") as hour, SUM(sczl_cl) as total_cl');
            //将产量数据赋值给小时
            foreach($arr as $v){
                $brr[$v] = array_key_exists($v,$row) ? $row[$v] : '0';
            }
            $jtClDbToRedis[$value] = $brr;
        }
        $bool = cache('jtClDbToRedis',$jtClDbToRedis);
        if($bool===false) $this->error('存入redis失败','','000002');
        $this->success('同步成功');
    }

    //获取机台产量数据
    public function getJtClDbByRedis()
    {
        $req = $this->request->param();
        if (isset($req['jtbh']) && !empty($req['jtbh'])){
            $jtbh = $req['jtbh'];
        }else{
            $this->error('参数错误');
        }
        $row = cache('jtClDbToRedis');
        $result['categories']=[];
        $result['series'][0]['name']=$jtbh;
        $result['series'][0]['data']=[];
        if($row && array_key_exists($jtbh,$row)){
            $result['categories'] = array_map('strval', array_keys($row[$jtbh]));
            $result['series'][0]['data'] = array_values($row[$jtbh]);
        }

        $res['status']=0;
        $res['msg']='';
        $res['data']=$result;
        return json($res);
    }

    /**
     * 班组工作中心负荷
     */
    public function bzDbToRedis()
    {
        $bzDbToRedis = [];
        //计算当天时间
        $sczlTime = date('Y-m-d H:i:s',time());
        if ($sczlTime>date('Y-m-d 00:00:00') && $sczlTime<date('Y-m-d 08:30:00')){
            $time = date('Y-m-d 00:00:00',time()-86400);
        }else{
            $time = date('Y-m-d 00:00:00');
        }
        //获取所有机台数据
        $machineList = db('设备_基本资料')
            ->where('sys_sbID','<>','')
            ->where('使用部门','in',['印刷车间','印后车间'])
            ->field('设备编号')
            ->select();
        //循环获取班组、工单、生产数据
        foreach ($machineList as $key=>$value){
            //获取正在生产工单信息
            $workOrder = \db('设备_产量采集')
                ->where('设备编号',$value['设备编号'])
                ->field('工单编号,印件号,工序号,工序名称,当前状态')
                ->order('UniqId desc')
                ->find();
            if (!$workOrder) continue;
            //获取生产信息
            $list = db('设备_产量计酬')
                ->alias('a')
                ->join('工单_基本资料 b','b.Gd_gdbh = a.sczl_gdbh')
                ->join('工单_工艺资料 c','a.sczl_gdbh = c.Gy0_gdbh AND a.sczl_yjno = c.Gy0_yjno AND a.sczl_gxh = c.Gy0_gxh')
                ->field([
                    'SUM(a.sczl_cl)' => '已完成产量',
                    'b.成品名称' => '产品名称',
                    'c.Gy0_计划接货数' => '计划产量',
                    'c.Gy0_小时产能' => '小时产能',
                    'a.sczl_bzdh' => '班组代号'
                ])
                ->where([
                    'a.sczl_jtbh' => $value['设备编号'],
                    'a.sczl_gdbh' => $workOrder['工单编号'],
                    'a.sczl_yjno' => $workOrder['印件号'],
                    'a.sczl_gxh' => $workOrder['工序号'],
                    'a.sczl_rq' => $time
                ])
                ->group('a.sczl_jtbh,a.sczl_bzdh')
                ->select();
            if (!$list) continue;
            foreach ($list as $v){
                $v['机台号'] = $value['设备编号'];
                $v['机台状态'] = $workOrder['当前状态'];
                $v['工单编号'] = $workOrder['工单编号'];
                $v['印件号'] = $workOrder['印件号'];
                $v['工序名称'] = $workOrder['工序名称'];
                array_push($bzDbToRedis,$v);
            }
        }
        $bool = cache('bzDbToRedis',$bzDbToRedis);
        if($bool===false) $this->error('存入redis失败','','000002');
        $this->success('同步成功');
    }

    /**
     * 获取班组工作负荷数据
     */
    public function getBzByRedis()
    {
        $row = cache('bzDbToRedis');
        $result['columns']=[
            ['name'=>'机台号','id'=>'jtbh','width'=>'14','autoWrap'=>"true",'textAlign'=>'left'],
            ['name'=>'班组','id'=>'bzdh','width'=>'16','autoWrap'=>"true",'textAlign'=>'left'],
            ['name'=>'在产工单号','id'=>'gdbh','width'=>'16','textAlign'=>'left'],
            ['name'=>'工单名称','id'=>'gdmc','width'=>'20','autoWrap'=>"true",'textAlign'=>'left'],
            ['name'=>'印件号','id'=>'yjno','width'=>'16','autoWrap'=>"true",'textAlign'=>'left'],
            ['name'=>'工序','id'=>'gx','width'=>'16','autoWrap'=>"true",'textAlign'=>'left'],
            ['name'=>'计划产量','id'=>'jhcl','width'=>'14','autoWrap'=>"true",'textAlign'=>'left'],
            ['name'=>'已完成产量','id'=>'wccl','width'=>'14','autoWrap'=>"true",'textAlign'=>'center'],
            ['name'=>'小时产能','id'=>'xscn','width'=>'14','autoWrap'=>"true",'textAlign'=>'center'],
            ['name'=>'机台状态','id'=>'status','width'=>'14','autoWrap'=>"true",'textAlign'=>'center'],
        ];
        if($row){
            foreach($row as $k=>$v){
                $result['rows'][$k]['jtbh']=$v['机台号'];
                $result['rows'][$k]['bzdh']=$v['班组代号'];
                $result['rows'][$k]['gdbh']=$v['工单编号'];
                $result['rows'][$k]['gdmc']=$v['产品名称'];
                $result['rows'][$k]['yjno']=$v['机台号'];
                $result['rows'][$k]['gx']=$v['工序名称'];
                $result['rows'][$k]['jhcl'] = $v['计划产量'];
                $result['rows'][$k]['wccl'] = $v['已完成产量'];
                $result['rows'][$k]['xscn'] = $v['小时产能'];
                $result['rows'][$k]['status'] = $v['机台状态'];
            }
        }else{
            $result['rows'][0]['jtbh']='';
            $result['rows'][0]['bzdh']='';
            $result['rows'][0]['gdbh']='';
            $result['rows'][0]['gdmc']='';
            $result['rows'][0]['yjno']='';
            $result['rows'][0]['gx']='';
            $result['rows'][0]['jhcl'] = '';
            $result['rows'][0]['wccl'] = '';
            $result['rows'][0]['xscn'] = '';
            $result['rows'][0]['status'] = '';
        }
        $res['status']=0;
        $res['msg']='';
        $res['data']=$result;
        return json($res);
    }




    /**
     * 决策支持月度设备运行数据统计->班组（接口）
     */
    public function TeamsgGoups()
    {
        // 获取设备编组信息
        $result = \db('设备_基本资料')
            ->whereNotNull('设备编组')
            ->group('设备编组')
            ->column('rtrim(设备编组) as 设备编组');

        $data = [
            "columns" => [
                ["id" => "subsys"]
            ],
            "rows" => []
        ];

        foreach ($result as $value) {
            $data["rows"][] = ["subsys" => $value];
        }

        $res = [
            'status' => 0,
            'msg' => '',
            'data' => $data
        ];
        return json($res);
    }

    /**
     * 决策支持月度设备运行数据统计->机台运行工时汇总（接口）
     */
    public function MonthlyEquipment() {
        // 获取请求参数，默认为 '01、切纸机组'
        $postData = input('post.dependence.item.subsys', '01、切纸机组');

        // 查询条件
        $where = [];
        if (!empty($postData)) {
            $where['设备编组'] = $postData;
        }

        // 获取当前年月
        $currentYearMonth = date('Y-m');
        // 获取去年的同月
        $lastYearMonth = date('Y-m', strtotime('-1 year'));

        // 查询 **今年** 当月的数据
        $currentData = \db('设备_基本资料')
            ->alias('a')
            ->join('设备_产量计酬 b', 'a.设备编号 = b.sczl_jtbh')
            ->field([
                'a.设备编号' => '设备编号',
                'SUM(b.sczl_cl)' => '产量'
            ])
            ->where($where)
            ->whereTime('b.sczl_rq', '>=', $currentYearMonth . '-01') // 查询当前月的第一天
            ->whereTime('b.sczl_rq', '<', date('Y-m-d', strtotime('next month', strtotime($currentYearMonth . '-01')))) // 下个月的第一天
            ->group('a.设备编号')
            ->order('a.设备编号')
            ->select();

        // 查询 **去年** 同月的数据
        $lastYearData = \db('设备_基本资料')
            ->alias('a')
            ->join('设备_产量计酬 b', 'a.设备编号 = b.sczl_jtbh')
            ->field([
                'a.设备编号' => '设备编号',
                'SUM(b.sczl_cl)' => '产量'
            ])
            ->where($where)
            ->whereTime('b.sczl_rq', '>=', $lastYearMonth . '-01') // 查询去年的同月第一天
            ->whereTime('b.sczl_rq', '<', date('Y-m-d', strtotime('next month', strtotime($lastYearMonth . '-01')))) // 下个月的第一天
            ->group('a.设备编号')
            ->order('a.设备编号')
            ->select();

        // 格式化数据
        $categories = [];
        $currentYearSeries = [];
        $lastYearSeries = [];

        // 转换查询结果为关联数组，方便查找
        $currentYearMap = [];
        $lastYearMap = [];

        foreach ($currentData as $item) {
            $currentYearMap[$item['设备编号']] = $item['产量'];
        }
        foreach ($lastYearData as $item) {
            $lastYearMap[$item['设备编号']] = $item['产量'];
        }

        // 统一设备编号，确保数据完整
        $allDevices = array_unique(array_merge(array_keys($currentYearMap), array_keys($lastYearMap)));

        foreach ($allDevices as $device) {
            $categories[] = $device;
            $currentYearSeries[] = isset($currentYearMap[$device]) ? intval($currentYearMap[$device]) : 0; // 今年产量
            $lastYearSeries[] = isset($lastYearMap[$device]) ? intval($lastYearMap[$device]) : 0; // 去年产量
        }

        // 组织返回数据
        $response = [
            'status' => 0,
            'msg' => '',
            'data' => [
                'categories' => $categories,
                'series' => [
                    [
                        'name' => '今年产量',
                        'data' => $currentYearSeries,
                        'type' => 'bar',
                        'yAxisIndex' => 0
                    ],
                    [
                        'name' => '去年产量',
                        'data' => $lastYearSeries,
                        'type' => 'bar',
                        'yAxisIndex' => 0
                    ]
                ]
            ]
        ];

        return json($response);
    }


    /**
     * 决策支持月度设备运行数据统计->机台运行工时汇总列表（接口）
     */
    public function Equipmenttable(){
        // 获取请求数据
        $postData = input('post.dependence.item.subsys', '01、切纸机组');

        $where = [];
        if (!empty($postData)) {
            $where['设备编组'] = $postData;
        }

        // 获取当前月份
        $mouth = date_create_from_format('Ym', date('Ym'))->format('Y-m');

        // 查询设备数据
        $list = \db('设备_基本资料')->alias('a')
            ->join('设备_产量计酬 b', 'a.设备编号 = b.sczl_jtbh')
            ->field([
                'a.设备编号' => '设备编号',
                'rtrim(a.设备名称)' => '设备名称',
                'SUM(b.sczl_cl)' => '产量',
                'SUM(b.sczl_设备运行工时)' => '设备运行工时',
                'SUM(b.sczl_保养工时)' => '保养工时',
                'SUM(b.sczl_打样总工时)' => '打样总工时',
                'SUM(b.sczl_打样工时)' => '打样补产工时',
                'SUM(b.sczl_装版总工时)' => '装板总工时',
                'SUM(b.sczl_装版工时)' => '装板补产工时',
                'SUM(b.sczl_异常停机工时)' => '异常停机工时'
            ])
            ->where($where)
            ->whereTime('b.sczl_rq', $mouth)
            ->group('a.设备编号')
            ->order('a.设备编号')
            ->select();

        $rows = [];
        foreach ($list as $item) {
            $rows[] = [
                'subsys' => $item['设备编号'],
                'module' => $item['设备名称'],
                'business' => $item['产量'],
                'sbyxgs' => $item['设备运行工时'],
                'dyzgs' => $item['打样总工时'],
                'dybcgs' => $item['打样补产工时'],
                'zbzgs' => $item['装板总工时'],
                'zbbcgs' => $item['装板补产工时'],
                'yctjgs' => $item['异常停机工时']
            ];
        }

        // 最终响应格式
        $response = [
            'status' => 0,
            'msg' => '',
            'data' => [
                'total' => count($rows),
                'columns' => [
                    ['name' => '设备编号','id' => 'subsys'],
                    ['name' => '设备名称','id' => 'module'],
                    ['name' => '产量','id' => 'business'],
                    ['name' => '设备运行工时','id' => 'sbyxgs'],
                    ['name' => '打样总工时','id' => 'dyzgs'],
                    ['name' => '打样补产工时','id' => 'dybcgs'],
                    ['name' => '装板总工时','id' => 'zbzgs'],
                    ['name' => '装板补产工时','id' => 'zbbcgs'],
                    ['name' => '异常停机工时','id' => 'yctjgs',]
                ],
//                'superHeaders' => [
//                    [
//                        [
//                            'name' => '设备信息',
//                            'colspan' => 3
//                        ],
//                        [
//                            'name' => '工时统计',
//                            'colspan' => 2
//                        ]
//                    ]
//                ],
                'rows' => $rows
            ]
        ];
        return json($response);
    }

    /**
     * 工序产出率月统计报表（接口）
     */
    public function Monthlystatistical(){

    }

    /**
     * 工序产出率月统计报表（缓存）
     */
    public function Workorderexcessloss_redis(){
        //链接定义 Redis
//        $redis = redis();
//        $redis_key = md5('Workorderexcessloss_redis');

        //获取当月日期
        $date = date('Y-m');
        $where = [];
        $where['a.jjcp_sj' ] = array('like',$date.'%');
        $sql = db('物料_收发记录')
            ->alias('d')
            ->where('d.仓库编号', 'Y101')
            ->group('d.st_gdbh, d.cpdh')
            ->field('d.st_gdbh, d.cpdh,d.st_rq, SUM(d.st_sl) as 实际投料,d.仓库编号')
            ->buildSql();
        $data = db('成品入仓')->alias('a')
            ->join('工单_基本资料 b', 'a.jjcp_gdbh = b.Gd_gdbh', 'left')
            ->join('工单_印件资料 c', 'a.jjcp_gdbh = c.Yj_Gdbh AND a.jjcp_cpdh = c.yj_Yjdh', 'left')
            ->join([$sql => 'd'], 'a.jjcp_gdbh = d.st_gdbh AND a.jjcp_cpdh = d.cpdh', 'left')
            ->where($where)
            ->where('d.仓库编号','Y101')
            // ->whereRaw('LEFT(a.成品编码, CASE WHEN a.成品编码 REGEXP "[a-zA-Z]" THEN 5 ELSE 4 END) = ?', [$params['code']])
            ->field('a.jjcp_gdbh as Gd_gdbh, a.jjcp_yjno, rtrim(a.jjcp_cpdh) as 成品编码, rtrim(a.jjcp_cpmc) as 成品名称,
            a.jjcp_sj, a.jjcp_smb, b.计量单位, b.Gd_khdh, d.实际投料, c.yj_Yjno, c.yj_ls, c.yj_ks, c.质量考核')
            ->group('a.jjcp_gdbh, a.jjcp_yjno')
            ->order('b.Gd_khdh, a.成品编码 asc, a.jjcp_yjno,d.st_rq desc')
            // ->page($pages)
            // ->limit($limit)
            ->select();


        foreach ($data as $key => $value){
            //查出成品数量及日期
            $cp_sql = "SELECT SUM(jjcp_sl) as cp_sl,MAX(jjcp_sj) as jjcp_sj FROM `成品入仓` WHERE jjcp_gdbh = '{$value['Gd_gdbh']}' AND jjcp_yjno = '{$value['jjcp_yjno']}' GROUP BY jjcp_gdbh,jjcp_yjno";
            $cp_data = Db::query($cp_sql);
            $data[$key]['warehousing_num'] = $cp_data[0]['cp_sl'];
            $data[$key]['warehousing_date'] = substr($cp_data[0]['jjcp_sj'],0,10);
            //查出进入超节损的工序，有上报产量的工序就进入超节损
            $gxh_sql = "SELECT sczl_gxh FROM 
                        (SELECT sczl_gxh FROM 设备_产量计酬 WHERE sczl_gdbh = '{$value['Gd_gdbh']}' AND sczl_yjno = '{$value['jjcp_yjno']}'
                        UNION SELECT sczl_gxh FROM db_sczl WHERE sczl_gdbh = '{$value['Gd_gdbh']}' AND sczl_yjno = '{$value['jjcp_yjno']}') AS combined_result";
            $gxh_arr = Db::query($gxh_sql);
            $gxh_values = array_column($gxh_arr, 'sczl_gxh');
//                $gy_data =db('工单_工艺资料')
//                    ->where('Gy0_gdbh',$value['Gd_gdbh'])
//                    ->where('Gy0_yjno',$value['jjcp_yjno'])
//                    ->where('Gy0_gxh','in',$gxh_values)
//                    ->field('Gy0_gxmc,Gy0_计划接货数,Gy0_计划损耗')
//                    ->select();
            $gy_data =db('工单_工艺资料')
                ->alias('a')
                ->field([
                    'a.Gy0_yjno', 'a.Gy0_gxh', 'RTRIM(a.Gy0_gxmc) as Gy0_gxmc','RTRIM(a.Add_gxmc) as Add_gxmc','a.Gy0_ks', 'a.Gy0_ls', 'a.Gy0_计划接货数',
                    'a.Gy0_计划损耗', 'a.超节损承担比例','SUM(b.sczl_zcfp) as total_fp','SUM(b.sczl_cl) as total_cl','SUM(c.sczl_cl) as cl','SUM(c.sczl_fp) as fp',
                ])
                ->join('设备_产量计酬 b', 'a.Gy0_gdbh = b.sczl_gdbh AND a.Gy0_yjno = b.sczl_yjno AND a.Gy0_gxh = b.sczl_gxh','left')
                ->join('db_sczl c', 'a.Gy0_gdbh = c.sczl_gdbh AND a.Gy0_yjno = c.sczl_yjno AND a.Gy0_gxh = c.sczl_gxh','left')
                ->where([
                    'a.Gy0_gdbh' => $value['Gd_gdbh'],
                    'a.Gy0_yjno' =>$value['jjcp_yjno'],
                    'a.Gy0_gxh' => ['in', $gxh_values]
                ])
                ->group('a.Gy0_yjno,a.Gy0_gxh')
                ->select();
            $arr = [];
            $plan_loss = [];//工单计划损耗
            $machine_plan_loss = 0;//机检计划损耗
            foreach ($gy_data as $k=>$v){
                if ($v['Gy0_计划接货数'] > 0){
                    $rate =  round($v['Gy0_计划损耗'] / $v['Gy0_计划接货数'],5);
                }else{
                    $rate = 0;
                }
                $arr[$k] = floor($rate * 10000) /10000;
                $plan_loss[$k] = $v['Gy0_计划损耗'];

//                    if (strpos($v['Gy0_gxmc'],'机检') !== false){
//                        $machine_plan_loss = $v['Gy0_计划损耗'];
//                    }
            }

            if ((int)$value['yj_ls'] > 0){
                $value['实际投料'] = number_format(((int)$value['实际投料']/10000)*$value['yj_ls']*$value['yj_ks'],4);
            }else{
                $value['实际投料'] = number_format($value['实际投料']/10000,4);
            }
            $data[$key]['实际投料'] = $value['实际投料'];

            if ($value['实际投料'] >0 ){
                $target_rate =  (1-array_sum($arr))*100;
                $data[$key]['target_rate'] =$target_rate.'%'; //目标合格率
                $real_rate = $cp_data[0]['cp_sl'] / ($value['实际投料'] * 10000) *100;
                $data[$key]['real_rate'] = number_format($real_rate,2) . '%';//实际合格率
            }else{
                $data[$key]['target_rate'] = ''; //目标合格率
                $data[$key]['real_rate'] = '';//实际合格率
            }

            //制程废品
            $zzfp_data =db('设备_产量计酬')->where('sczl_gdbh',$value['Gd_gdbh'])->where('sczl_yjno',$value['jjcp_yjno'])->field('SUM(sczl_zcfp) as sczl_zcfp')->select();
            $data[$key]['zcfp'] = $zzfp_data[0]['sczl_zcfp'] * $value['yj_ls'];//制程废品
            //废品合计
            $wasteTotal = db('db_qczl')->where('qczl_gdbh',$value['Gd_gdbh'])->where('qczl_yjno',$value['jjcp_yjno'])->sum('qczl_fp');
            if (empty($wasteTotal)){
                $wasteTotal = 0;
            }
            $data[$key]['废品合计'] = $wasteTotal + $data[$key]['zcfp'];//废品合计
            $data[$key]['工单无形损'] = (int)($value['实际投料'] *10000) - $cp_data[0]['cp_sl'] - $data[$key]['废品合计'];//工单无形损
            $out_sql = "SELECT fp_sl1,fp_sl2,fp_sl3,fp_sl4,fp_sl5,fp_sl6,fp_sl7,fp_sl8,fp_sl9,fp_sl10,fp_sl11,fp_sl12,fp_sl13,
                        fp_lb1,fp_lb2,fp_lb3,fp_lb4,fp_lb5,fp_lb6,fp_lb7,fp_lb8,fp_lb9,fp_lb10,fp_lb11,fp_lb12,fp_lb13, 
                        fp_bh1,fp_bh2,fp_bh3,fp_bh4,fp_bh5,fp_bh6,fp_bh7,fp_bh8,fp_bh9,fp_bh10,fp_bh11,fp_bh12,fp_bh13,
                        fp_gxmc1,fp_gxmc2,fp_gxmc3,fp_gxmc4,fp_gxmc5,fp_gxmc6,fp_gxmc7,fp_gxmc8,fp_gxmc9,fp_gxmc10,fp_gxmc11,fp_gxmc12,fp_gxmc13 
                        FROM db_qczl WHERE qczl_gdbh = '{$value['Gd_gdbh']}' AND  qczl_yjno = '{$value['jjcp_yjno']}'";
            $waste_out = Db::query($out_sql);
            $list = []; //外发废数组
            $quality = [];//质检废数组
            $material = [];//材料废数组
            $change = [];//零头处理数组
            $machine = [];//机检废品数组
            $j = 0;
            $m = 0;
            $n = 0;
            $l = 0;
            $o = 0;
            foreach ($waste_out as $entry) {
                for ($i = 1; $i <= 13; $i++) {
                    $labelKey = "fp_lb" . $i;
                    $bhKey = "fp_bh" . $i;
                    $gxmcKey = "fp_gxmc" . $i;
                    $slKey = "fp_sl" . $i;
//                        if (!empty($entry[$labelKey])) {
//                            if (substr($entry[$gxmcKey],0,2) == '99'){
//                                $list[$j]= $entry[$slKey];
//                                $j++;
//                            }
//                            if (substr($entry[$labelKey],0,1) == 'L' ){
//                                $material[$n]= $entry[$slKey];
//                                $n++;
//                            }
//                            if (substr($entry[$labelKey],0,3) == 'M04' ){
//                                $change[$l]= $entry[$slKey];
//                                $l++;
//                            }
//                        }
//                        if (!empty($entry[$bhKey])) {
//                            if ($entry[$bhKey] != '000000' && $entry[$slKey] > 0){
//                                $quality[$m]= $entry[$slKey];
//                                $m++;
//                            }
////                            if ($entry[$bhKey] != '000000' && substr($entry[$labelKey],0,3) == 'K01'){
////                                $machine[$o]= $entry[$slKey];
////                                $o++;
////                            }
//                        }
                    if (!empty($entry[$labelKey])) {
                        if (substr($entry[$labelKey],0,3) == 'M04'){
                            $list[$j]= $entry[$slKey];
                            $j++;
                        }
                        if (substr($entry[$labelKey],0,1) == 'L' ){
                            $material[$n]= $entry[$slKey];
                            $n++;
                        }
                        if (substr($entry[$labelKey],0,3) == 'M01' ){
                            $change[$l]= $entry[$slKey];
                            $l++;
                        }
                    }
//                if (!empty($entry[$bhKey])) {
//                    if ($entry[$bhKey] != '000000' && $entry[$slKey] > 0){
                    $quality[$m]= $entry[$slKey];
                    $m++;
//                    }
//                }

                }
            }
            $machine_waste = array_sum($machine);//机检废品
            if ($data[$key]['Gd_gdbh'] == '2401746'){
                halt($machine_waste);
            }
            $data[$key]['工单计划损耗'] = array_sum($plan_loss);//工单计划损耗
            /**
             * 奖惩系数及金额
             * 奖惩金额 = 工单节损数 * 0.02(0.09)
             * 工单节损数 = 工单计划损耗 - 废品总数 - 机检节损数
             * 机检节损数 = 机检计划损耗 - 机检质检废品
             */
            $machine_loss = $machine_plan_loss - $machine_waste;
            $order_loss = $data[$key]['工单计划损耗'] - ($data[$key]['废品合计'] - array_sum($list));
            if (strpos($data[$key]['成品名称'],'小盒')){
                $moneyRate = 0.02;
            }else{
                $moneyRate = 0.09;
            }

            $reward = $order_loss * $moneyRate;
            $data[$key]['reward_rate'] = '1';
            if ($data[$key]['质量考核'] === 0){
                $data[$key]['reward_money'] = number_format($reward,2);
            }else{
                $data[$key]['reward_money'] = '';
            }
//                $data[$key]['reward_money'] = '';
            $data[$key]['材料废'] = array_sum($material); //材料废
            $data[$key]['零头处理'] = array_sum($change); //零头处理
            $data[$key]['外发废'] = array_sum($list);//外发废
            $data[$key]['分摊废'] = '';//分摊废
            $data[$key]['工单质检废'] =  array_sum($quality);  //质检废
        }

//        if($data){
//            //将查询结果存入 Redis 缓存中
//            $redis->set($redis_key, json_encode($data));
//            echo date("Y-m-d H:i:s").'   存进去了';
//            return json($data);
//        }
        $bool = cache('cacheWorkorderexcessloss_redis',$data);
        if($bool===false) $this->error('存入redis失败','','000002');
        $this->success('存入成功');
    }

    /**
     * 工序产出率月统计报表（接口）
     */
    public function Workorderexcessloss(){
        $data = cache('cacheWorkorderexcessloss_redis');
//        $redis = redis();
//        $data = json_decode($redis->get(md5('Workorderexcessloss_redis')),true);
        $rows = [];
        foreach ($data as $item) {
            $rows[] = [
                'Gd_gdbh' => $item['Gd_gdbh'],
                'cpmc' => $item['成品名称'],
                'jjcp_sj' => $item['jjcp_sj'],
                'target_rate' => $item['target_rate'],
                'real_rate' => $item['real_rate'],
            ];
        }
        // 最终响应格式
        $response = [
            'status' => 0,
            'msg' => '',
            'data' => [
                'columns' => [
                    ['name' => '工单编号','width'=>'10','id' => 'Gd_gdbh'],
                    ['name' => '产品名称','width'=>'38','id' => 'cpmc'],
                    ['name' => '入仓时间','width'=>'20','id' => 'jjcp_sj'],
                    ['name' => '目标合格率','width'=>'16','id' => 'target_rate'],
                    ['name' => '实际合格率','width'=>'16','id' => 'real_rate'],
                ],
                'rows' => $rows
            ]
        ];
        return json($response);
    }

}