tool/application/api/controller/Minong.php

<?php

namespace app\api\controller;

use app\common\controller\Api;
use think\Db;
use think\Request;
/**
 * minong数据接口
 */
class Minong extends Api
{
    protected $noNeedLogin = ['*'];
    protected $noNeedRight = ['*'];

    /**
     * 首页
     *
     */
    public function index()
    {
        $this->success('请求成功');
    }
    /*
     *1、 获取各省份订单量接口
     * */
    public function getProvinceData(){

        $date = date('Y');
        $startTime = $date.'-01-01 00:00:00';
        $res =  Db::connect('db2')
            ->query("select distinct Gd_khdh,Gd_khmc,round(sum(订单数量),2) as number from 工单_基本资料 where 接单日期 > '{$startTime}' and 订单数量 > 0 group by Gd_khdh order by number desc ");
        $res = array_slice($res,0,10);//取前十名
        foreach ($res as $key=>$value){
            $res[$key]['Gd_khdh'] = trim($value['Gd_khdh']);
            $res[$key]['Gd_khmc'] = mb_substr(trim($value['Gd_khmc']),0,4,'utf-8');//取4位中文
        }
        $this->success('请求成功',$res);
    }
    /*
    * 2、获取设备当前状态
    * */
    public function getEmStatus(){
        if (Request::instance()->isGet()){
            $this->error('非法请求');
        }
        $params = input('emid');
        if (empty($params)){
            $this->error('参数错误');
        }
        $emid = $params.'#';
        $res =  Db::connect('db2')
            ->query("select 设备编号 as emid,工单编号 as Gd_gdbh,班组编号 as Yj_member,当前状态 as status,Uniqid from 设备_产量采集 where 设备编号 = '{$emid}'  order by Uniqid desc limit 1");
        $list = array();
        $Gdbh = $res[0]['Gd_gdbh'];
        $Yj_res = Db::connect('db2')
            ->query("select yj_yjmc  from 工单_印件资料 where Yj_Gdbh = '{$Gdbh}' limit 1");
        $Yj_yjmc = rtrim($Yj_res[0]['yj_yjmc']);
        $list['emid'] = $params;
        $list['status'] = $res[0]['status'];
        $list['Yj_yjmc'] = $Yj_yjmc;

        if ($res[0]['status'] == '生产'){
            $time = date('Y-m-d H:i:s');
            $firstStartTime = date('Y-m-d').' 08:30:00';//白班开工时间
            $firstEndTime = date('Y-m-d').' 20:30:00';//白班结束时间、晚班开工时间
            $lastEndTime = strtotime("+1 day",strtotime(date('Y-m-d').' 08:30:00'));//晚班结束时间
            $strTime = strtotime($time);
            if ($strTime>strtotime($firstStartTime) && $strTime < strtotime($firstEndTime)){//表示在上白班，8点到20点
                $out_res = Db::connect('db2')
                    ->query("select sum(sczl_cl) as number,sum(sczl_装版工时) as zb_time, sum(sczl_保养工时) as by_time,
                    sum(sczl_打样工时) as dy_time,sum(sczl_异常停机工时) as yctj_time,sum(sczl_异常工时1) as yc_time , sczl_bh1,sczl_bh2,sczl_bh3,sczl_bh4  
                    from 设备_产量计酬 where sczl_jtbh = '{$emid}' and 开工时间 = '{$firstStartTime}' ");
                $hour = $strTime - strtotime($firstStartTime);
//                $hour_res = intval($hour/3600);

            }
            if ($strTime > strtotime($firstEndTime) && $strTime < $lastEndTime){//表示在上夜班,20点到第二天8点
               $out_res = Db::connect('db2')
                   ->query("select sum(sczl_cl) as number,sum(sczl_装版工时) as zb_time, sum(sczl_保养工时) as by_time,
                    sum(sczl_打样工时) as dy_time,sum(sczl_异常停机工时) as yctj_time,sum(sczl_异常工时1) as yc_time , sczl_bh1,sczl_bh2,sczl_bh3,sczl_bh4  
                    from 设备_产量计酬 where sczl_jtbh = '{$emid}' and 开工时间 = '{$firstEndTime}' ");
                $hour = $strTime - strtotime($firstEndTime);

           }
            $hour_res = round($hour/3600);
            $hours = $out_res[0]['zb_time']+$out_res[0]['by_time']+$out_res[0]['dy_time']+$out_res[0]['yctj_time']+$out_res[0]['yc_time'];
            $validTime = $hour_res - $hours; //实际工作时间
            //扣除11.30和4.30的吃饭时间,各一个小时,11.30夜宵
            if ($strTime > strtotime(date('Y-m-d').' 12:30:00') && $strTime <strtotime(date('Y-m-d'.' 17:30:00'))  ){
                $validTime = $validTime -1;
            }
            if ($strTime > strtotime(date('Y-m-d'.' 17:30:00')) && $strTime < strtotime($firstEndTime)){
                $validTime = $validTime -2;
            }
            if ($strTime > strtotime(date('Y-m-d').' 23:30:00')){
                $validTime = $validTime -2;
            }
//            print_r($validTime);die;
            $list['Yj_yield'] = $out_res[0]['number'];
            $list['production_efficiency'] = round($out_res[0]['number'] / $validTime);
            $list['production_rate'] = $list['production_efficiency']/10000*100 .'%';

            $member = $out_res[0];
            unset($member['number']);
            unset($member['zb_time']);
            unset($member['by_time']);
            unset($member['dy_time']);
            unset($member['yctj_time']);
            unset($member['yc_time']);
            $member = array_values($member);
//            print_r($member);die;
            if (empty($member[3])){
                unset($member[3]);
                $member_res = Db::connect('db2')
                    ->query("select  员工姓名 as Yj_member from 人事_基本资料 where 员工编号 in ('{$member[0]}','{$member[1]}','{$member[2]}')");
            }else{
                $member_res = Db::connect('db2')
                    ->query("select  员工姓名 as Yj_member from 人事_基本资料 where 员工编号 in ('{$member[0]}','{$member[1]}','{$member[2]}','{$member[3]}')");
            }
//            print_r($member_res);die;
            $str='';
            foreach ($member_res as $key=>$value){
                $str = $str.trim($value['Yj_member']).',';
            }
            $list['Yj_member'] = substr($str,0,strrpos($str,','));
        }else{
            $list['Yj_yield'] = '';
            $list['production_efficiency'] = '';
            $list['production_rate'] = '';
            $list['Yj_member'] = '';
        }
        $this->success('请求成功',$list);
    }
    /*
    * 3、获取年度销售量统计
    * */
    public function getMouthNumber(){
      $year = date('Y');
      $res =  Db::connect('db2')
            ->query("select
                        sum(case month(`接单日期`) when '1' then `订单数量` else 0 end) as Jan,
                        sum(case month(`接单日期`) when '2' then `订单数量` else 0 end) as Feb,
                        sum(case month(`接单日期`) when '3' then `订单数量` else 0 end) as Mar,
                        sum(case month(`接单日期`) when '4' then `订单数量` else 0 end) as Apr,
                        sum(case month(`接单日期`) when '5' then `订单数量` else 0 end) as May,
                        sum(case month(`接单日期`) when '6' then `订单数量` else 0 end) as June,
                        sum(case month(`接单日期`) when '7' then `订单数量` else 0 end) as July,
                        sum(case month(`接单日期`) when '8' then `订单数量` else 0 end) as Aug,
                        sum(case month(`接单日期`) when '9' then `订单数量` else 0 end) as Sept,
                        sum(case month(`接单日期`) when '10' then `订单数量` else 0 end) as Oct,
                        sum(case month(`接单日期`) when '11' then `订单数量` else 0 end) as Nov,
                        sum(case month(`接单日期`) when '12' then `订单数量` else 0 end) as Dece
                    from `工单_基本资料` where year(`接单日期`) = '{$year}' ");

//        print_r($res[0]);die;
        $this->success('请求成功',$res[0]);
    }
    /*
    * 4、获取设备排产状态
    * */
    public function getEmWorkStatus(){
        if (Request::instance()->isGet()){
            $this->error('非法请求');
        }
        $params = input('emid');
        if (empty($params)){
            $this->error('参数错误');
        }
        $emid = $params.'#';
        $res = Db::connect('db2')
            ->query("select Uniqid,工单编号 as sczl_gdbh,当前状态 as status from 设备_产量采集 where 设备编号 = '{$emid}' order by Uniqid desc limit 1");
        $result = array();
        if(empty($res[0]['sczl_gdbh'])){
            $result['em_status'] = $res[0]['status'];
            $result['Gd_gdbh'] = '';
            $result['Yj_yjmc'] = '';
            $result['plan_output'] = '';
            $result['finished_output'] = '';
            $result['scrap_rate'] = '';
            $result['Gd_status'] = '';
            $this->success('请求成功',$result);
        }
        $Gd_gdbh = $res[0]['sczl_gdbh'];//工单编号
        $yjmc_res = Db::connect('db2')
            ->query("select yj_yjmc,yj_zzdh from 工单_印件资料 where Yj_Gdbh = '{$Gd_gdbh}'");

        $Yj_yjmc = rtrim($yjmc_res[0]['yj_yjmc']);//工艺名称

        $Yj_zzdh = rtrim($yjmc_res[0]['yj_zzdh']);//纸张代号
        $gy_bom = Db::connect('db2')
            ->query("select Bom_计划用量 as plan_output from 工单_bom资料 where BOM_工单编号 = '{$Gd_gdbh}' and BOM_物料编码 = '{$Yj_zzdh}'");
        $plan_output = empty($gy_bom) ? ' ' : round($gy_bom[0]['plan_output'],2);//计划产量,目前不知道怎么计算

        $out_res = Db::connect('db2')
            ->query("select sum(sczl_cl) as number,sum(sczl_zcfp) as scrap from 设备_产量计酬 
                where sczl_gdbh = '{$Gd_gdbh}' and sczl_jtbh = '{$emid}' ");

        $finished_output = $out_res[0]['number'];
        if ($finished_output == 0 && $out_res[0]['scrap']== 0){
            $scrap = 0 .'%';
            $finished_output = '';
        }else{
            $scrap =round($out_res[0]['scrap'] / ($finished_output+$out_res[0]['scrap']),4) *100 .'%';
        }

        $result['em_status'] = $res[0]['status'];
        $result['Gd_gdbh'] = $Gd_gdbh;
        $result['Yj_yjmc'] = $Yj_yjmc;
        $result['plan_output'] = $plan_output;
        $result['finished_output'] = $finished_output;
        $result['scrap_rate'] = $scrap;
        $result['Gd_status'] = '2-生产中';
//        print_r($result);die;
        $this->success('请求成功',$result);
    }
    /*
    * 5、获取设备月产量
    * */
    public function getEmMouthNumber(){
        if (Request::instance()->isGet()){
            $this->error('非法请求');
        }
        $params = input('emid');
        if (empty($params)){
            $this->error('参数错误');
        }
        $emid = $params.'#';
        $year = date('Y');
        $res =  Db::connect('db2')
            ->query("select
                        sum(case month(`开工时间`) when '1' then `sczl_cl` else 0 end) as Jan,
                        sum(case month(`开工时间`) when '2' then `sczl_cl` else 0 end) as Feb,
                        sum(case month(`开工时间`) when '3' then `sczl_cl` else 0 end) as Mar,
                        sum(case month(`开工时间`) when '4' then `sczl_cl` else 0 end) as Apr,
                        sum(case month(`开工时间`) when '5' then `sczl_cl` else 0 end) as May,
                        sum(case month(`开工时间`) when '6' then `sczl_cl` else 0 end) as June,
                        sum(case month(`开工时间`) when '7' then `sczl_cl` else 0 end) as July,
                        sum(case month(`开工时间`) when '8' then `sczl_cl` else 0 end) as Aug,
                        sum(case month(`开工时间`) when '9' then `sczl_cl` else 0 end) as Sept,
                        sum(case month(`开工时间`) when '10' then `sczl_cl` else 0 end) as Oct,
                        sum(case month(`开工时间`) when '11' then `sczl_cl` else 0 end) as Nov,
                        sum(case month(`开工时间`) when '12' then `sczl_cl` else 0 end) as Dece
                    from `设备_产量计酬` where year(`开工时间`) = '{$year}' and sczl_jtbh='{$emid}'");

        $this->success('请求成功',$res[0]);
    }
    /*
    * 6、获取设备月度运行时长
    * */
    public function getEmRunTime(){
        if (Request::instance()->isGet()){
            $this->error('非法请求');
        }
        $params = input('emid');
        if (empty($params)){
            $this->error('参数错误');
        }
        $emid = $params.'#';
        $year = date('Y');
        $out_res = Db::connect('db2')
            ->query("select 
                month(开工时间) as worktime,sum(sczl_装版工时) as em_time_zb,sum(sczl_保养工时) as em_time_by,sum(sczl_打样工时) as em_time_dy,sum(sczl_异常停机工时) as em_time_tj,
			    sum(sczl_设备运行工时) as em_time_run,sum(sczl_异常工时1) as em_time_yc
	            from 设备_产量计酬 where year(开工时间) = '{$year}' and sczl_jtbh = '{$emid}' 
                and (sczl_装版工时>0 or sczl_保养工时>0 or sczl_打样工时>0 or sczl_异常停机工时>0 or sczl_设备运行工时>0 or sczl_异常工时1>0) group by worktime ");
        $number = count($out_res);
        $displarityNumber = 12 - $number;
        if ($displarityNumber > 0){
            for ($i=$number;$i<12;$i++){
                $arr = array();
                $arr['worktime'] = $i+1;
                $arr['em_time_zb'] = '0';
                $arr['em_time_by'] = '0';
                $arr['em_time_dy'] = '0';
                $arr['em_time_tj'] = '0';
                $arr['em_time_run'] = '0';
                $arr['em_time_yc'] = '0';
                array_push($out_res,$arr);
            }
        }
        $this->success('请求成功',$out_res);
    }
    /*
    * 7、获取机台合格率、达成率
    * */
    public function getEmRate(){
        if (Request::instance()->isGet()){
            $this->error('非法请求');
        }
        $params = input('emid');
        if (empty($params)){
            $this->error('参数错误');
        }
        $emid = $params.'#';
        $output_res = Db::connect('db2')
            ->query("select 工单编号 as Gd_gdbh,当前状态 as status,UniqId from 设备_产量采集 where 设备编号='{$emid}'  order by UniqId desc limit 1 ");
        if ($output_res[0]['status'] == '生产'){
            $Gd_gdbh = $output_res[0]['Gd_gdbh'];
            $hour_output = Db::connect('db2')
                ->query("select case ISNULL(B类产能) when 1 then A类产能 else B类产能 end as A_output,Gy0_小时产能 as hour_output
                from 工单_工艺资料 where Gy0_gdbh = '{$Gd_gdbh}' and Gy0_sbbh = '{$emid}'");
            $output = empty($hour_output[0]['A_output'])?$hour_output[0]['hour_output'] : $hour_output[0]['A_output'];
            $time = strtotime(date('Y-m-d H:i:s'));
            $firstStartTime = date('Y-m-d').' 08:30:00';//白班开工时间
            $firstEndTime = date('Y-m-d').' 20:30:00';//白班结束时间、晚班开工时间
            if (strtotime($firstStartTime) < $time && strtotime($firstEndTime) > $time){
                $whereTime = $firstStartTime;
                $hour = $time - strtotime($firstStartTime);
            }else{
                $whereTime = $firstEndTime;
                $hour = $time - strtotime($firstStartTime);
            }
            $now_output = Db::connect('db2')
                ->query("select sum(sczl_cl) as output,sum(sczl_zcfp) as scrap,sum(sczl_装版工时+sczl_打样工时+sczl_异常停机工时+sczl_异常工时1) as time 
                from 设备_产量计酬 where sczl_jtbh='{$emid}' and 开工时间='{$whereTime}'");
            $hour_res = round($hour/3600);
            $validTime = $hour_res - $now_output[0]['time']; //实际工作时间
            //扣除11.30和4.30的吃饭时间,各一个小时,11.30夜宵
            if ($time > strtotime(date('Y-m-d').' 12:30:00') && $time <strtotime(date('Y-m-d'.' 17:30:00'))  ){
                $validTime = $validTime -1;
            }
            if ($time > strtotime(date('Y-m-d'.' 17:30:00')) && $time < strtotime($firstEndTime)){
                $validTime = $validTime -2;
            }
            if ($time > strtotime(date('Y-m-d').' 23:30:00')){
                $validTime = $validTime -2;
            }
//            print_r($validTime);die;
            $list = array();
            $list['status'] = $output_res[0]['status'];
            $list['emid_qualified_rate'] =round($now_output[0]['output'] /($now_output[0]['output'] + $now_output[0]['scrap']),5) *100 .'%';
            $list['emid_reach_rate'] = round($now_output[0]['output'] / $validTime,4)/ $output * 100 . '%';
            $this->success('请求成功',$list);
        }
        $result = array();
        $result['status'] = $output_res[0]['status'];
        $result['emid_qualified_rate'] = 0;
        $result['emid_reach_rate'] = 0;
        $this->success('请求成功',$result);
    }
    /*
    * 8、获取上个月机台废品明细比例
    * */
    public function getEmScrap(){
        if (Request::instance()->isGet()){
            $this->error('非法请求');
        }
        $params = input('emid');
        if (empty($params)){
            $this->error('参数错误');
        }
        $emid = $params.'#';
        $date = date('Y-m',strtotime("-1 month"));
        $year = substr($date,0,4);//年
        $month = substr($date,5,2);//上月
        $em_res = Db::connect('db2')
            ->query("select distinct(sczl_gdbh) as Gd_gdbh from `设备_产量计酬` where `sczl_jtbh`='{$emid}' and year(开工时间)='{$year}' and month(开工时间)='{$month}' ");
        $arr = array_column($em_res,'Gd_gdbh');
        foreach ($arr as $key=>$value){
            $arr[$key] = "'".$value."'";
        }
        $str = implode(',',$arr);

        $scrap_res = Db::connect('db2')
            ->query("select 废品类别 as scrap_type,sum(废品数量) as scrap_number 
            from `rec_月度废品汇总` where Gd_gdbh in ($str) and `废品类别` like '%E%'   GROUP BY  `废品类别` ORDER BY scrap_number desc ");

        $allNumber = Db::connect('db2')
            ->query("select distinct(Gd_gdbh),实际投料 as number from `rec_月度废品汇总` where Gd_gdbh in ($str)  ");
        $number = array_sum(array_column($allNumber,'number'));
        $number = $number * 10000;
        foreach ($scrap_res as $k=>$v){
            $scrap_res[$k]['scrap_type'] = rtrim($v['scrap_type']);
            $scrap_res[$k]['scrap_rate'] = round($v['scrap_number']/$number,5) * 100 .'%';
        }

        $this->success('请求成功',$scrap_res);
    }
    /*
    * 9、获取机台该产品上批次问题
    * */
    public function getLastScrapType(){
        if (Request::instance()->isGet()){
            $this->error('非法请求');
        }
        $params = input('emid');
        if (empty($params)){
            $this->error('参数错误');
        }
        $emid = $params.'#';
        $em_res = Db::connect('db2')
            ->query("select distinct(`工单编号`) as Gd_gdbh,UniqId from `设备_产量采集` where `设备编号`='{$emid}' ORDER BY UniqId desc limit 1 ");
        $Gd_gdbh = $em_res[0]['Gd_gdbh'];
        $gd_res = Db::connect('db2')
            ->query("select Gd_cpdh from `工单_基本资料` where Gd_gdbh = '{$Gd_gdbh}' ");
        $Gd_cpdh = $gd_res[0]['Gd_cpdh'];
        $cp_res = Db::connect('db2')
            ->query("select distinct(a.Gy0_gdbh) as Gd_gdbh ,b.Gd_cpdh,b.Gd_cpmc,a.UniqId 
            from `工单_工艺资料` a LEFT JOIN `工单_基本资料` b on a.Gy0_gdbh = b.Gd_gdbh where a.Gy0_sbbh='{$emid}' 
            and b.Gd_cpdh = '{$Gd_cpdh}' and a.Gy0_gdbh != '{$Gd_gdbh}' ORDER BY a.UniqId desc limit 1 ");
        $scrap_gdbh = $cp_res[0]['Gd_gdbh'];

        $scrap_res = Db::connect('db2')
            ->query("select Gd_gdbh,废品类别 as scrap_type,废品数量 as scrap_number,实际投料 as feeding 
            from `rec_月度废品汇总` where Gd_gdbh = '{$scrap_gdbh}' and 废品类别 like '%E%' ORDER BY `废品数量` desc limit 3");
        if (empty($scrap_res)){
            $result = array();
            $result['product_name'] = rtrim($cp_res[0]['Gd_cpmc']);
            $result['Gd_gdbh'] = $scrap_gdbh;
            $result['scrap_type'] = '';
            $result['scrap_number'] = '';
            $result['scrap_radio'] = '';
            $this->success('请求成功',$result);
        }else{
            foreach ($scrap_res as $key=>$value){
                $scrap_res[$key]['product_name'] = rtrim($cp_res[0]['Gd_cpmc']);
                $scrap_res[$key]['scrap_radio'] = round($value['scrap_number'] / ($value['feeding'] * 10000),5) * 100 .'%';
                unset($scrap_res[$key]['feeding']);
            }
            $this->success('请求成功',$scrap_res);
        }
    }
    /*
    * 10、获取机台最近一周报废原因前三名
    * */
    public function getLastWeekScrap(){
        if (Request::instance()->isGet()){
            $this->error('非法请求');
        }
        $lastMonth = date("Ym", strtotime("-1 month"));
        $scrap_res =  Db::connect('db2')
            ->query("select 废品类别 as scrap_type,sum(废品数量) as scrap_number
             from `rec_月度废品汇总` where 年月='{$lastMonth}' and 废品类别 like '%E%'   group by  废品类别 order by scrap_number desc limit 3");
        $feed_res = Db::connect('db2')
            ->query("select distinct(Gd_gdbh),实际投料 as number from `rec_月度废品汇总` where  年月='{$lastMonth}' and 废品类别 like '%E%'");
        $number = array_sum(array_column($feed_res,'number'));
        foreach ($scrap_res as $key => $value){
            $scrap_res[$key]['scrap_radio'] = round($value['scrap_number'] / ($number * 10000),5) *100 . '%';
        }

        $this->success('请求成功',$scrap_res);
    }
    /*
    * 11、获取工序大废品奖惩记录
    * */
    public function getRewardAndPunish(){
        $date = date('Y-m-d',strtotime('-1 day'));
        $department = '胶印';
        $scrap_res = Db::connect('db2')
            ->query("select a.sczl_gdbh as Gd_gdbh,rtrim(a.sczl_fplxA) as scrap_type,a.sys_rq as date,
            rtrim(a.`责任部门` ) as department,a.sczl_ls as ls,a.sczl_cl as big_scrap, a.sczl_bh1,a.sczl_bh2,rtrim(c.员工姓名) as nameOne,rtrim(b.yj_yjmc) as Yj_yjmc
            from `db_大废品` a 
            left join `工单_印件资料` b on a.sczl_gdbh = b.Yj_Gdbh
            left join `人事_基本资料` c on a.sczl_bh1= c.员工编号  
            where date_format(a.sys_rq,'%Y-%m-%d') = '{$date}' and a.`责任部门` = '{$department}'  and b.yj_Yjno = a.sczl_yjno");
        if (empty($scrap_res)){
            $this->success('请求成功','');
        }
        $name_res = array_column($scrap_res,'sczl_bh2');
        foreach ($name_res as $key=>$value){
            $name_res[$key] = "'".$value."'";
        }
        $str = implode(',',$name_res);
        $name_bh = Db::connect('db2')
            ->query("select `员工编号` as bh2,`员工姓名` as name2 from `人事_基本资料` where `员工编号` in ($str)");
        foreach ($scrap_res as $k=>$v){
            foreach ($name_bh as $i=>$j){
                if ($v['sczl_bh2'] == $j['bh2']){
                    $scrap_res[$k]['nameTwo'] = rtrim($j['name2']);
                }
            }
            unset($scrap_res[$k]['sczl_bh1']);
            unset($scrap_res[$k]['sczl_bh2']);
        }
        $this->success('请求成功',$scrap_res);
    }
    /*
    * 12、获取机台距上次带班检查时间
    * */
    public function getLastTest(){
        if (Request::instance()->isGet()){
            $this->error('非法请求');
        }
        $params = input('emid');
        if (empty($params)){
            $this->error('参数错误');
        }
        $emid = $params.'#';
        $time = strtotime(date('Y-m-d H:i:s'));
        $firstStartTime = date('Y-m-d').' 08:30:00';//白班开工时间
        $firstEndTime = date('Y-m-d').' 20:30:00';//白班结束时间、晚班开工时间
        if (strtotime($firstStartTime) < $time && strtotime($firstEndTime) > $time){
            $whereTime = $firstStartTime;
        }else{
            $whereTime = $firstEndTime;
        }
        //首检
        $test_one = Db::connect('db2')
            ->query("select distinct 提交时间 as submitTime from 制程检验_记录 where 设备编号 = '{$emid}' and  开工时间 = '{$whereTime}' and 类别 = '首件与过程确认' order by 提交时间 desc");

        $res_one = empty($test_one) ? round(($time-strtotime($whereTime))/3600) :round(($time-strtotime($test_one[0]['submitTime']))/3600);
        $list = array();
        $list['test_one'] = $res_one;
        //机台自检
        $test_machine = Db::connect('db2')
            ->query("select distinct 提交时间 as submitTime from 制程检验_记录 where 设备编号 = '{$emid}' and  开工时间 = '{$whereTime}' and 类别 = '机台检验' order by 提交时间 desc");

        $machine_res = empty($test_machine) ? round(($time-strtotime($whereTime))/3600) :round(($time-strtotime($test_machine[0]['submitTime']))/3600);
        $list['test_machine'] = $machine_res;
        //IPQC检验
        $test_IPQC = Db::connect('db2')
            ->query("select distinct 提交时间 as submitTime from 制程检验_记录 where 设备编号 = '{$emid}' and  开工时间 = '{$whereTime}' and 类别 = 'IPQC检验' order by 提交时间 desc");

        $res = empty($test_IPQC) ? round(($time-strtotime($whereTime))/3600) :round(($time-strtotime($test_IPQC[0]['submitTime']))/3600);
        $list['test_IPQC'] = $res;

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

    /**
     * 13、获取设备月综合利用率
    */
    public function getEmUse(){
        if (Request::instance()->isGet()){
            $this->error('非法请求');
        }
        $params = input('emid');
        if (empty($params)){
            $this->error('参数错误');
        }
        $emid = $params.'#';
        $year = date('Y');
        $res = Db::connect('db2')
            ->query("select 平均车速 as speed from 设备_基本资料 where 设备编号 = '{$emid}'");
        $number = $res[0]['speed'];
        $cl_res = Db::connect('db2')
            ->query("select 
                month(开工时间) as worktime,sum(sczl_cl) as output,sum(sczl_设备运行工时-sczl_保养工时-sczl_打样总工时)*'{$number}' as time
                from 设备_产量计酬 where year(开工时间)='{$year}' and sczl_jtbh='{$emid}' group by worktime");
        foreach ($cl_res as $key=>$value){
            $cl_res[$key]['rate'] = round($value['output']/$value['time'],4) *100 .'%';
            unset($cl_res[$key]['output']);
            unset($cl_res[$key]['time']);
        }
        $number = count($cl_res);
        $displarityNumber = 12 - $number;
        if ($displarityNumber > 0){
            for ($i=$number;$i<12;$i++){
                $arr = array();
                $arr['worktime'] = $i+1;
                $arr['rate'] = '0%';
                array_push($cl_res,$arr);
            }
        }
        $this->success('请求成功',$cl_res);
    }

    //时间计算(距离现在多长时间)
    public function format_date($time){
        $t=time()-$time;
        $f=array(
            '31536000'=>'年',
            '2592000'=>'个月',
            '604800'=>'星期',
            '86400'=>'天',
            '3600'=>'小时',
            '60'=>'分钟',
            '1'=>'秒'
        );
        foreach ($f as $k=>$v)    {
            if (0 !=$c=floor($t/(int)$k)) {
                return $c.$v.'前';
            }
        }
    }
}