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

<?php

namespace app\api\controller;

use app\common\controller\Api;
use \think\Request;
use \think\Db;
/**
 * 检验计件单据维护接口
 */
class Inspect extends Api
{
    protected $noNeedLogin = ['*'];
    protected $noNeedRight = ['*'];

    /**
     * 首页
     *
     */
    public function index()
    {
        $this->success('请求成功');
    }
    /**
     * 获取手工检验左侧菜单栏
     *
     * @ApiMethod GET
    */
    public function getInspectCount(){
        if (Request::instance()->isGet() == false){
            $this->error('非法请求');
        }

        $rows = db('db_手工检验')
            ->field('LEFT(sys_rq, 10) as date, COUNT(*) as counts')
            ->group('date')
            ->order('UniqId desc')
            ->limit(30)
            ->select();
        $num = db('db_手工检验')
            ->where('sys_rq','>=',$rows[count($rows)-1]['date'])
            ->count();
        $arr = db('db_手工检验')
            ->field('LEFT(sys_rq, 10) as date, rtrim(sys_id) as sys_id, COUNT(*) as count')
            ->where('sys_rq','>=',$rows[count($rows)-1]['date'])
            ->group('date, sys_id')
            ->limit($num)
            ->select();
        foreach($rows as $key=>$value){
            $rows[$key]['sys'] = [];
            foreach($arr as $k=>$v){
                if($value['date'] == $v['date']){
                    unset($v['date']);
                    array_push($rows[$key]['sys'],$v);
                    unset($arr[$k]);
                }
            }
        }

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

    /**
     * 获取手工检验左侧菜单栏
     * 通过工单
     * @ApiMethod GET
    */
    public function getInspectCountByGdbh(){
        if (Request::instance()->isGet() == false){
            $this->error('非法请求');
        }
        $sql = 'SELECT DISTINCT sczl_gdbh  FROM `db_手工检验` ORDER BY sczl_gdbh DESC LIMIT 65 ';
        $list = db()->query($sql);
//        $rows = db('db_手工检验')
//            ->field('sczl_gdbh')
//            ->group('sczl_gdbh')
//            ->order('sczl_gdbh desc')
//            ->limit(65)
//            ->select();

        foreach ($list as $key => $value){
            $dataCount = db('db_手工检验')->alias('d')
                ->where('sczl_gdbh',$value['sczl_gdbh'])
                ->where('SUBSTR(d.sczl_yjgx,1,2) = g.yj_Yjno')
                ->field('rtrim(d.sys_id) as sys_id, rtrim(g.yj_yjmc) as yj_yjmc')
                ->join(['工单_印件资料'=>'g'],'d.sczl_gdbh=g.Yj_Gdbh')
                ->order('sys_id asc')
                ->select();

            $list[$key]['cpmc'] = $dataCount[0]['yj_yjmc'];
            // 提取 sys_id 列成新数组
            $sysIds = array_column($dataCount, 'sys_id');
            //统计每个不同的 sys_id 的数量
            $counts = array_count_values($sysIds);

            $i = 0;
            foreach ($counts as $k=>$v){
                $list[$key]['sys'][$i]['sys_id'] = $k;
                $list[$key]['sys'][$i]['count'] = $v;
                $i++;
            }
        }
        $this->success('请求成功',$list);
    }


    /**
     * 获取某个日期手工单据
     *
     * @ApiMethod GET
     * @params string date
    */
    public function getDateList(){
        if (Request::instance()->isGet() == false){
            $this->error('非法请求');
        }
        $params = Request::instance()->param();

        if (!isset($params['date']) || empty($params['date'])) $this->error('参数缺失');

        $page = 1;
        $limit = 15;
        if (isset($params['page']) && !empty($params['page'])) $page = $params['page'];
        if (isset($params['limit']) && !empty($params['limit'])) $limit = $params['limit'];

        if(strpos($params['date'],'-')){
            $where = ['a.sys_rq'=>['like',$params['date'].'%']];
            $option = ['sys_rq'=>['like',$params['date'].'%']];
        }else{
            $where = ['a.sczl_gdbh'=>$params['date']];
            $option = ['sczl_gdbh'=>$params['date']];
        }
        if (isset($params['sys_id']) && !empty($params['sys_id'])){
            $where['a.sys_id'] = ['LIKE',$params['sys_id'].'%'];
            $option['sys_id'] = ['LIKE',$params['sys_id'].'%'];
        }


        $field = 'a.sczl_gdbh,a.sczl_yjgx,rtrim(a.sczl_gxmc) as sczl_gxmc,DATE(a.sczl_rq) as sczl_rq,a.sczl_cl,a.sczl_dedh,a.sczl_废品率系数,a.sczl_检验类别,a.sczl_bh0,
        rtrim(a.qczl_NumDesc) as qczl_NumDesc,rtrim(a.sys_id) as sys_id,a.sys_rq,a.mod_rq,a.UniqId,b.千件工价,b.日定额,rtrim(c.员工姓名) as 员工姓名,rtrim(d.yj_yjmc) as yj_yjmc';
        $list = db('db_手工检验')->alias('a')
            ->join('dic_lzde b','a.sczl_dedh = b.sys_bh','left')
            ->join('人事_基本资料 c','a.sczl_bh0 = c.员工编号','left')
            ->join('工单_印件资料 d','a.sczl_gdbh = d.Yj_Gdbh','left')
            ->where($where)
            ->where('SUBSTR(a.sczl_yjgx,1,2) = d.yj_Yjno')
            ->field($field)->page($page,$limit)->order('a.sczl_rq,a.UniqId asc')->select();

        $count = db('db_手工检验')->where($option)->count();

        $data['data'] = $list;
        $data['total'] = $count;

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

    /**
     * 获取机器检验左侧菜单栏
     *
     * @ApiMethod GET
    */
    public function getMachineCount(){
        if (Request::instance()->isGet() == false){
            $this->error('非法请求');
        }
        $sql = 'SELECT DISTINCT DATE(sys_rq) AS sys_rq  FROM `db_机器检验` ORDER BY sys_rq DESC LIMIT 30 ';
        $list = db()->query($sql);
        foreach ($list as $key => $value){
            $where = [$value['sys_rq'].'00:00:00',$value['sys_rq'].'24:00:00'];
            $dataCount = db('db_机器检验')->where('sys_rq','between time',$where)->field('rtrim(sys_id) as sys_id')->order('sys_id asc')->select();
            $list[$key]['count'] = count($dataCount);
            // 提取 sys_id 列成新数组
            $sysIds = array_column($dataCount, 'sys_id');
            //统计每个不同的 sys_id 的数量
            $counts = array_count_values($sysIds);
            $i = 0;
            foreach ($counts as $k=>$v){
                $list[$key]['sys'][$i]['sys_id'] = $k;
                $list[$key]['sys'][$i]['count'] = $v;
                $i++;
            }
        }
        $this->success('请求成功',$list);
    }

    /**
     * 获取机器检验左侧菜单栏
     *
     * @ApiMethod GET
    */
    public function getMachineCountByGdbh(){
        if (Request::instance()->isGet() == false){
            $this->error('非法请求');
        }
        $sql = 'SELECT DISTINCT sczl_gdbh  FROM `db_机器检验` ORDER BY sczl_gdbh DESC LIMIT 65 ';
        $list = Db::query($sql);
        foreach ($list as $key => $value){
            $dataCount = Db::name('db_机器检验')->alias('d')
                ->where('sczl_gdbh',$value['sczl_gdbh'])
                ->field('rtrim(d.sys_id) as sys_id, rtrim(g.Gd_cpmc) as Gd_cpmc')
                ->join(['工单_基本资料'=>'g'],'d.sczl_gdbh=g.Gd_gdbh')
                ->order('sys_id asc')
                ->select();
            $list[$key]['cpmc'] = $dataCount[0]['Gd_cpmc'];
            // 提取 sys_id 列成新数组
            $sysIds = array_column($dataCount, 'sys_id');
            //统计每个不同的 sys_id 的数量
            $counts = array_count_values($sysIds);
            $i = 0;
            foreach ($counts as $k=>$v){
                $list[$key]['sys'][$i]['sys_id'] = $k;
                $list[$key]['sys'][$i]['count'] = $v;
                $i++;
            }
        }
        $this->success('请求成功',$list);
    }

    /**
     * 获取某个日期机器单据
     *
     * @ApiMethod GET
     * @params string date
    */
    public function getDateMachine(){
        if (Request::instance()->isGet() == false){
            $this->error('非法请求');
        }
        $params = Request::instance()->param();

        if (!isset($params['date']) || empty($params['date'])) $this->error('参数缺失');

        $page = 1;
        $limit = 15;
        if (isset($params['page']) && !empty($params['page'])) $page = $params['page'];
        if (isset($params['limit']) && !empty($params['limit'])) $limit = $params['limit'];

        if(strpos($params['date'],'-')){
            $where = ['a.sys_rq'=>['like',$params['date'].'%']];
            $option = ['sys_rq'=>['like',$params['date'].'%']];
        }else{
            $where = ['a.sczl_gdbh'=>$params['date']];
            $option = ['sczl_gdbh'=>$params['date']];
        }
        if (isset($params['sys_id']) && !empty($params['sys_id'])){
            $where['a.sys_id'] = ['LIKE',$params['sys_id'].'%'];
            $option['sys_id'] = ['LIKE',$params['sys_id'].'%'];
        }

        $field = 'a.sczl_gdbh,trim(a.sczl_yjgx) as sczl_yjgx,rtrim(a.sczl_gxmc) as sczl_gxmc,a.sczl_jtbh,a.sczl_bzdh,DATE(a.sczl_rq) as sczl_rq,a.sczl_cl,rtrim(a.sczl_单据类型) as sczl_单据类型,
        a.sczl_设备运行工时,a.sczl_dedh,a.sczl_废品率系数,rtrim(a.sczl_检验类别) as sczl_检验类别,a.sczl_bh1,a.sczl_bh2,a.sczl_bh3,a.sczl_bh4,a.qczl_NumDesc,rtrim(a.sys_id) as sys_id,
        a.sys_rq,a.mod_rq,a.UniqId,b.千件工价,b.日定额,rtrim(c1.员工姓名) as sczl_bh1_name,rtrim(c2.员工姓名) as sczl_bh2_name,rtrim(c3.员工姓名) as sczl_bh3_name,
        rtrim(c4.员工姓名) as sczl_bh4_name,rtrim(d.yj_yjmc) as yj_yjmc';
        $list = db('db_机器检验')->alias('a')
            ->join('dic_lzde b','a.sczl_dedh = b.sys_bh','left')
            ->join('人事_基本资料 c1','a.sczl_bh1 = c1.员工编号','left')
            ->join('人事_基本资料 c2','a.sczl_bh2 = c2.员工编号','left')
            ->join('人事_基本资料 c3','a.sczl_bh3 = c3.员工编号','left')
            ->join('人事_基本资料 c4','a.sczl_bh4 = c4.员工编号','left')
            ->join('工单_印件资料 d','a.sczl_gdbh = d.Yj_Gdbh','left')
            ->where($where)
            ->where('SUBSTR(a.sczl_yjgx,1,2) = d.yj_Yjno')
            ->field($field)->page($page,$limit)->order('a.UniqId desc')->select();
        $count = db('db_机器检验')->where($option)->count();

        $data['data'] = $list;
        $data['total'] = $count;
        $this->success('请求成功',$data);
    }

    /**
     * 定位
     * @ApiMethod (GET)
     * @param string $gdbh 工单编号
     * @param string $cpmc 产品名称
     * @param string $page 页码
     * @param string $limit 数量
     */
    public function locate()
    {
        //get请求
        if(!$this->request->isGet()){
            $this->error('请求方式错误');
        }
        $req = $this->request->param();

        $page = 1;
        $limit = 15;
        if (isset($req['page']) && !empty($req['page'])) $page = $req['page'];
        if (isset($req['limit']) && !empty($req['limit'])) $limit = $req['limit'];

        if (isset($req['gdbh']) && !empty($req['gdbh'])){
            $where = [
                'a.sczl_gdbh'=>[ 'like', '%' . $req['gdbh'] . '%']
            ];

            $field = 'a.sczl_gdbh,a.sczl_yjgx,rtrim(a.sczl_gxmc) as sczl_gxmc,DATE(a.sczl_rq) as sczl_rq,a.sczl_cl,a.sczl_dedh,a.sczl_废品率系数,a.sczl_检验类别,a.sczl_bh0,
        rtrim(a.qczl_NumDesc) as qczl_NumDesc,rtrim(a.sys_id) as sys_id,a.sys_rq,a.mod_rq,a.UniqId,b.千件工价,b.日定额,rtrim(c.员工姓名) as 员工姓名,rtrim(d.yj_yjmc) as yj_yjmc';
            $rows = db('db_手工检验')->alias('a')
                ->join('dic_lzde b','a.sczl_dedh = b.sys_bh','left')
                ->join('人事_基本资料 c','a.sczl_bh0 = c.员工编号','left')
                ->join('工单_印件资料 d','a.sczl_gdbh = d.Yj_Gdbh','left')
                ->where($where)
                ->where('SUBSTR(a.sczl_yjgx,1,2) = d.yj_Yjno')
                ->field($field)->page($page,$limit)->order('a.sczl_rq,a.UniqId asc')->select();

            $option['sczl_gdbh'] = [ 'like', '%' . $req['gdbh'] . '%'];
            $total = db('db_手工检验')->where($option)->count();
        }else{
            if (isset($req['cpmc']) && !empty($req['cpmc'])){
                //查询工单表
                $gd = db('工单_基本资料')
                    ->where('Gd_cpmc', 'LIKE', '%'.$req['cpmc'].'%')
                    ->column('Gd_gdbh');
                $where = [
                    'a.sczl_gdbh'=>['in', $gd]
                ];
                $field = 'a.sczl_gdbh,a.sczl_yjgx,rtrim(a.sczl_gxmc) as sczl_gxmc,DATE(a.sczl_rq) as sczl_rq,a.sczl_cl,a.sczl_dedh,a.sczl_废品率系数,a.sczl_检验类别,a.sczl_bh0,
        rtrim(a.qczl_NumDesc) as qczl_NumDesc,rtrim(a.sys_id) as sys_id,a.sys_rq,a.mod_rq,a.UniqId,b.千件工价,b.日定额,rtrim(c.员工姓名) as 员工姓名,rtrim(d.yj_yjmc) as yj_yjmc';
                $rows = db('db_手工检验')->alias('a')
                    ->join('dic_lzde b','a.sczl_dedh = b.sys_bh','left')
                    ->join('人事_基本资料 c','a.sczl_bh0 = c.员工编号','left')
                    ->join('工单_印件资料 d','a.sczl_gdbh = d.Yj_Gdbh','left')
                    ->where($where)
                    ->where('SUBSTR(a.sczl_yjgx,1,2) = d.yj_Yjno')
                    ->field($field)->page($page,$limit)->order('a.sczl_rq,a.UniqId asc')->select();

                $option['sczl_gdbh'] = ['in', $gd];
                $total = db('db_手工检验')->where($option)->count();
            }else{
                $this->error('参数错误');
            }
        }

        $data = [
            'total'    => $total,
            'rows'          => $rows,
        ];
        $this->success('成功',$data);
    }

    /**
     * 定位
     * @ApiMethod (GET)
     * @param string $gdbh 工单编号
     * @param string $cpmc 产品名称
     * @param string $page 页码
     * @param string $limit 数量
     */
    public function machineLocate()
    {
        //get请求
        if(!$this->request->isGet()){
            $this->error('请求方式错误');
        }
        $req = $this->request->param();

        $page = 1;
        $limit = 15;
        if (isset($req['page']) && !empty($req['page'])) $page = $req['page'];
        if (isset($req['limit']) && !empty($req['limit'])) $limit = $req['limit'];

        if (isset($req['gdbh']) && !empty($req['gdbh'])){
            $where = [
                'sczl_gdbh'=>[ 'like', '%' . $req['gdbh'] . '%']
            ];
            $where['a.sczl_单据类型'] = '初检单';
            $field = 'a.sczl_gdbh,trim(a.sczl_yjgx) as sczl_yjgx,rtrim(a.sczl_gxmc) as sczl_gxmc,a.sczl_jtbh,a.sczl_bzdh,DATE(a.sczl_rq) as sczl_rq,a.sczl_cl,rtrim(a.sczl_单据类型) as sczl_单据类型,
        a.sczl_设备运行工时,a.sczl_dedh,a.sczl_废品率系数,rtrim(a.sczl_检验类别) as sczl_检验类别,a.sczl_bh1,a.sczl_bh2,a.sczl_bh3,a.sczl_bh4,a.qczl_NumDesc,rtrim(a.sys_id) as sys_id,
        a.sys_rq,a.mod_rq,a.UniqId,b.千件工价,b.日定额,rtrim(c1.员工姓名) as sczl_bh1_name,rtrim(c2.员工姓名) as sczl_bh2_name,rtrim(c3.员工姓名) as sczl_bh3_name,
        rtrim(c4.员工姓名) as sczl_bh4_name,rtrim(d.yj_yjmc) as yj_yjmc';
            $rows = db('db_机器检验')->alias('a')
                ->join('dic_lzde b','a.sczl_dedh = b.sys_bh','left')
                ->join('人事_基本资料 c1','a.sczl_bh1 = c1.员工编号','left')
                ->join('人事_基本资料 c2','a.sczl_bh2 = c2.员工编号','left')
                ->join('人事_基本资料 c3','a.sczl_bh3 = c3.员工编号','left')
                ->join('人事_基本资料 c4','a.sczl_bh4 = c4.员工编号','left')
                ->join('工单_印件资料 d','a.sczl_gdbh = d.Yj_Gdbh','left')
                ->where($where)
                ->where('SUBSTR(a.sczl_yjgx,1,2) = d.yj_Yjno')
                ->field($field)->page($page,$limit)->order('a.sczl_rq asc,a.UniqId desc')->select();
            $option['sczl_单据类型'] = '初检单';
            $option['sczl_gdbh'] = [ 'like', '%' . $req['gdbh'] . '%'];
            $total = db('db_机器检验')->where($option)->count();

        }else{
            if (isset($req['cpmc']) && !empty($req['cpmc'])){
                //查询工单表
                $gd = db('工单_基本资料')
                    ->where('Gd_cpmc', 'LIKE', '%'.$req['cpmc'].'%')
                    ->column('Gd_gdbh');
                $where = [
                    'sczl_gdbh'=>['in', $gd]
                ];
                $where['a.sczl_单据类型'] = '初检单';
                $field = 'a.sczl_gdbh,trim(a.sczl_yjgx) as sczl_yjgx,rtrim(a.sczl_gxmc) as sczl_gxmc,a.sczl_jtbh,a.sczl_bzdh,DATE(a.sczl_rq) as sczl_rq,a.sczl_cl,rtrim(a.sczl_单据类型) as sczl_单据类型,
        a.sczl_设备运行工时,a.sczl_dedh,a.sczl_废品率系数,rtrim(a.sczl_检验类别) as sczl_检验类别,a.sczl_bh1,a.sczl_bh2,a.sczl_bh3,a.sczl_bh4,a.qczl_NumDesc,rtrim(a.sys_id) as sys_id,
        a.sys_rq,a.mod_rq,a.UniqId,b.千件工价,b.日定额,rtrim(c1.员工姓名) as sczl_bh1_name,rtrim(c2.员工姓名) as sczl_bh2_name,rtrim(c3.员工姓名) as sczl_bh3_name,
        rtrim(c4.员工姓名) as sczl_bh4_name,rtrim(d.yj_yjmc) as yj_yjmc';
                $rows = db('db_机器检验')->alias('a')
                    ->join('dic_lzde b','a.sczl_dedh = b.sys_bh','left')
                    ->join('人事_基本资料 c1','a.sczl_bh1 = c1.员工编号','left')
                    ->join('人事_基本资料 c2','a.sczl_bh2 = c2.员工编号','left')
                    ->join('人事_基本资料 c3','a.sczl_bh3 = c3.员工编号','left')
                    ->join('人事_基本资料 c4','a.sczl_bh4 = c4.员工编号','left')
                    ->join('工单_印件资料 d','a.sczl_gdbh = d.Yj_Gdbh','left')
                    ->where($where)
                    ->where('SUBSTR(a.sczl_yjgx,1,2) = d.yj_Yjno')
                    ->field($field)->page($page,$limit)->order('a.sczl_rq asc,a.UniqId desc')->select();
                $option['sczl_单据类型'] = '初检单';
                $option['sczl_gdbh'] = ['in', $gd];
                $total = db('db_机器检验')->where($option)->count();
            }else{
                $this->error('参数错误');
            }
        }

        $data = [
            'total'    => $total,
            'rows'          => $rows,
        ];
        $this->success('成功',$data);
    }

    /**
     * 获取信息
     * @ApiMethod (GET)
     * @param string $UniqId UniqId
     */
    public function getInfo()
    {
        //get请求
        if(!$this->request->isGet()){
            $this->error('请求方式错误');
        }
        $req = $this->request->param();
        if (isset($req['UniqId']) && !empty($req['UniqId'])){
            $UniqId = $req['UniqId'];
        }else{
            $this->error('参数错误');
        }
        $where['a.UniqId'] = $UniqId;
        $field = 'DATE(a.sczl_rq) as sczl_rq, a.sczl_bzdh,a.sczl_bh0, rtrim(c0.员工姓名) as sczl_bh0_name, a.sczl_cl0, a.sczl_fp0,
        a.sczl_gdbh,trim(a.sczl_yjgx) as sczl_yjgx,rtrim(a.sczl_gxmc) as sczl_gxmc,rtrim(d.yj_yjmc) as yj_yjmc,
        a.sczl_cl,rtrim(a.sczl_dedh) as sczl_dedh,rtrim(a.sczl_检验类别) as sczl_检验类别,a.sczl_废品率系数,
        a.sczl_jsss,a.sczl_冲定额,a.qczl_Num as 流程单号1,a.qczl_NumDesc1 as 流程单号2,a.qczl_NumDesc2 as 流程单号3,
        a.qczl_NumDesc3 as 流程单号4,a.qczl_NumDesc4 as 流程单号5,a.qczl_NumDesc5 as 流程单号6,
        a.qczl_NumDesc6 as 流程单号7,a.qczl_NumDesc7 as 流程单号8,a.qczl_NumDesc8 as 流程单号9,
        rtrim(a.qczl_NumDesc) as qczl_NumDesc,rtrim(a.sczl_desc) as sczl_desc,
        a.sczl_bh1,a.sczl_bh2,a.sczl_bh3,a.sczl_bh4,a.sczl_bh5, a.sczl_bh6,
        a.sczl_bh7,a.sczl_bh8,a.sczl_bh9,a.sczl_bh10,a.sczl_bh11, a.sczl_bh12,
        rtrim(c1.员工姓名) as sczl_bh1_name,rtrim(c2.员工姓名) as sczl_bh2_name,
        rtrim(c3.员工姓名) as sczl_bh3_name,rtrim(c4.员工姓名) as sczl_bh4_name,
        rtrim(c5.员工姓名) as sczl_bh5_name,rtrim(c6.员工姓名) as sczl_bh6_name,
        rtrim(c7.员工姓名) as sczl_bh7_name,rtrim(c8.员工姓名) as sczl_bh8_name,
        rtrim(c9.员工姓名) as sczl_bh9_name,rtrim(c10.员工姓名) as sczl_bh10_name,
        rtrim(c11.员工姓名) as sczl_bh11_name,rtrim(c12.员工姓名) as sczl_bh12_name,
        a.sczl_cl1,a.sczl_cl2,a.sczl_cl3,a.sczl_cl4,a.sczl_cl5,a.sczl_cl6,
        a.sczl_cl7,a.sczl_cl8,a.sczl_cl9,a.sczl_cl10,a.sczl_cl11,a.sczl_cl12,
        a.sczl_fp1,a.sczl_fp2,a.sczl_fp3,a.sczl_fp4,a.sczl_fp5,a.sczl_fp6,
        a.sczl_fp7,a.sczl_fp8,a.sczl_fp9,a.sczl_fp10,a.sczl_fp11,a.sczl_fp12';
        $rows = db('db_手工检验')->alias('a')
            ->join('人事_基本资料 c0','a.sczl_bh0 = c0.员工编号','left')
            ->join('人事_基本资料 c1','a.sczl_bh1 = c1.员工编号','left')
            ->join('人事_基本资料 c2','a.sczl_bh2 = c2.员工编号','left')
            ->join('人事_基本资料 c3','a.sczl_bh3 = c3.员工编号','left')
            ->join('人事_基本资料 c4','a.sczl_bh4 = c4.员工编号','left')
            ->join('人事_基本资料 c5','a.sczl_bh5 = c5.员工编号','left')
            ->join('人事_基本资料 c6','a.sczl_bh6 = c6.员工编号','left')
            ->join('人事_基本资料 c7','a.sczl_bh7 = c7.员工编号','left')
            ->join('人事_基本资料 c8','a.sczl_bh8 = c8.员工编号','left')
            ->join('人事_基本资料 c9','a.sczl_bh9 = c9.员工编号','left')
            ->join('人事_基本资料 c10','a.sczl_bh10 = c10.员工编号','left')
            ->join('人事_基本资料 c11','a.sczl_bh11 = c11.员工编号','left')
            ->join('人事_基本资料 c12','a.sczl_bh12 = c12.员工编号','left')
            ->join('工单_印件资料 d','a.sczl_gdbh = d.Yj_Gdbh','left')
            ->where($where)
            ->where('SUBSTR(a.sczl_yjgx,1,2) = d.yj_Yjno')
            ->field($field)->find();

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

    /**
     * 获取信息
     * @ApiMethod (GET)
     * @param string $UniqId UniqId
     */
    public function getMachineInfo()
    {
        //get请求
        if(!$this->request->isGet()){
            $this->error('请求方式错误');
        }
        $req = $this->request->param();
        if (isset($req['UniqId']) && !empty($req['UniqId'])){
            $UniqId = $req['UniqId'];
        }else{
            $this->error('参数错误');
        }
        $where['a.UniqId'] = $UniqId;
        $field = 'a.sczl_gdbh,trim(a.sczl_yjgx) as sczl_yjgx,rtrim(a.sczl_gxmc) as sczl_gxmc,
        a.sczl_jtbh,a.sczl_bzdh,DATE(a.sczl_rq) as sczl_rq,a.sczl_cl,a.sczl_PgCl,
        rtrim(a.sczl_单据类型) as sczl_单据类型,rtrim(a.sczl_dedh) as sczl_dedh,
        a.sczl_装版工时,a.sczl_保养工时,a.sczl_异常停机工时,a.sczl_设备运行工时,
        a.sczl_废品率系数,rtrim(a.sczl_检验类别) as sczl_检验类别,
        a.sczl_bh1,a.sczl_bh2,a.sczl_bh3,a.sczl_bh4,a.sczl_bh5, a.sczl_bh6,a.sczl_bh7,a.sczl_bh8,
        a.qczl_Num as 流程单号1,a.qczl_NumDesc1 as 流程单号2,a.qczl_NumDesc2 as 流程单号3,
        a.qczl_NumDesc3 as 流程单号4,a.qczl_NumDesc4 as 流程单号5,a.qczl_NumDesc5 as 流程单号6,
        a.qczl_NumDesc6 as 流程单号7,a.qczl_NumDesc7 as 流程单号8,a.qczl_NumDesc8 as 流程单号9,
        rtrim(a.qczl_NumDesc) as qczl_NumDesc,rtrim(a.sczl_desc) as sczl_desc,
        rtrim(c1.员工姓名) as sczl_bh1_name,rtrim(c2.员工姓名) as sczl_bh2_name,
        rtrim(c3.员工姓名) as sczl_bh3_name,rtrim(c4.员工姓名) as sczl_bh4_name,
        rtrim(c5.员工姓名) as sczl_bh5_name,rtrim(c6.员工姓名) as sczl_bh6_name,
        rtrim(c7.员工姓名) as sczl_bh7_name,rtrim(c8.员工姓名) as sczl_bh8_name,
        a.sczl_rate1, a.sczl_rate2, a.sczl_rate3, a.sczl_rate4, 
        a.sczl_rate5, a.sczl_rate6, a.sczl_rate7, a.sczl_rate8, 
        rtrim(d.yj_yjmc) as yj_yjmc';
        $rows = db('db_机器检验')->alias('a')
            ->join('dic_lzde b','a.sczl_dedh = b.sys_bh','left')
            ->join('人事_基本资料 c1','a.sczl_bh1 = c1.员工编号','left')
            ->join('人事_基本资料 c2','a.sczl_bh2 = c2.员工编号','left')
            ->join('人事_基本资料 c3','a.sczl_bh3 = c3.员工编号','left')
            ->join('人事_基本资料 c4','a.sczl_bh4 = c4.员工编号','left')
            ->join('人事_基本资料 c5','a.sczl_bh5 = c5.员工编号','left')
            ->join('人事_基本资料 c6','a.sczl_bh6 = c6.员工编号','left')
            ->join('人事_基本资料 c7','a.sczl_bh7 = c7.员工编号','left')
            ->join('人事_基本资料 c8','a.sczl_bh8 = c8.员工编号','left')
            ->join('工单_印件资料 d','a.sczl_gdbh = d.Yj_Gdbh','left')
            ->where($where)
            ->where('SUBSTR(a.sczl_yjgx,1,2) = d.yj_Yjno')
            ->field($field)->find();

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

    /**
     * 查询印件工序及产品名称
     * @ApiMethod (GET)
     * @param string $gdbh 工单编号
     * @param string $gxmc 工序名称
     */
    public function getGxMc()
    {
        //get请求
        if(!$this->request->isGet()){
            $this->error('请求方式错误');
        }
        $req = $this->request->param();
        if (isset($req['gdbh']) && !empty($req['gdbh'])){
            $gdbh = $req['gdbh'];
        }else{
            $this->error('参数错误');
        }
        $gxmc = ['手检','手检(圆切)','次品检','抽检','大张翻检'];

        $gxmcs = [];
        foreach ($gxmc as $k=>$v){
            array_push($gxmcs,['like','%'.$v.'%']);
        }
        array_push($gxmcs,'OR');

        $rows = db('工单_基本资料')->alias('g')
            ->field('rtrim(g.Gd_cpmc) as Gd_cpmc, c.Gy0_yjno, c.Gy0_gxh, rtrim(c.Gy0_gxmc) as Gy0_gxmc')
            ->where(['g.Gd_gdbh'=>$gdbh])
            ->where(['c.Gy0_gxmc'=>$gxmcs])
            ->join(['产品_印件资料'=>'j'],'g.Gd_cpdh=j.yj_yjdh and g.成品代号=j.yj_cpdh')
            ->join(['工单_工艺资料'=>'c'],'c.Gy0_gdbh=g.Gd_gdbh and c.Gy0_yjno=j.yj_Yjno')
            ->select();

        foreach ($rows as $key=>$value){
            $rows[$key]['jyGx'] =  sprintf("%02d", $value['Gy0_yjno']).'-'.$value['Gy0_gxh'];
        }

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

    /**
     * 查询印件工序及产品名称
     * @ApiMethod (GET)
     * @param string $gdbh 工单编号
     * @param string $gxmc 工序名称
     */
    public function getMachineGxMc()
    {
        //get请求
        if(!$this->request->isGet()){
            $this->error('请求方式错误');
        }
        $req = $this->request->param();
        if (isset($req['gdbh']) && !empty($req['gdbh'])){
            $gdbh = $req['gdbh'];
        }else{
            $this->error('参数错误');
        }
        $gxmc = ['机检'];

        $gxmcs = [];
        foreach ($gxmc as $k=>$v){
            array_push($gxmcs,['like','%'.$v.'%']);
        }
        array_push($gxmcs,'OR');

        $rows = db('工单_基本资料')->alias('g')
            ->field('rtrim(g.Gd_cpmc) as Gd_cpmc, c.Gy0_yjno, c.Gy0_gxh, rtrim(c.Gy0_gxmc) as Gy0_gxmc')
            ->where(['g.Gd_gdbh'=>$gdbh])
            ->where(['c.Gy0_gxmc'=>$gxmcs])
            ->join(['产品_印件资料'=>'j'],'g.Gd_cpdh=j.yj_yjdh and g.成品代号=j.yj_cpdh')
            ->join(['工单_工艺资料'=>'c'],'c.Gy0_gdbh=g.Gd_gdbh and c.Gy0_yjno=j.yj_Yjno')
            ->select();

        foreach ($rows as $key=>$value){
            $rows[$key]['jyGx'] =  sprintf("%02d", $value['Gy0_yjno']).'-'.$value['Gy0_gxh'];
        }

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

    /**
     * 查询定额代号
     * @ApiMethod (GET)
     */
    public function getDedh()
    {
        //get请求
        if(!$this->request->isGet()){
            $this->error('请求方式错误');
        }

        $row = db('dic_lzde')
            ->field('Key_, sys_bh, rtrim(sys_mc) as sys_mc')
            ->where('sys_mc','初检(手工)')
            ->find();
        $rows = db('dic_lzde')
            ->field('sys_bh, rtrim(sys_mc) as sys_mc')
//            ->where('Parent',$row['Key_'])
            ->where('sys_bh','like',$row['sys_bh'].'%')
            ->where('sys_bh','<>',$row['sys_bh'])
            ->select();
        $row['bh_mc'] = $rows;

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

    /**
     * 查询定额代号
     * @ApiMethod (GET)
     */
    public function getMachineDedh()
    {
        //get请求
        if(!$this->request->isGet()){
            $this->error('请求方式错误');
        }
//        $req = $this->request->param();
//        if (isset($req['sys_mc']) && !empty($req['sys_mc'])){
//            $sys_mc = $req['sys_mc'];
//        }else{
//            $this->error('参数错误');
//        }
        $row = db('dic_lzde')
            ->field('Key_, sys_bh, rtrim(sys_mc) as sys_mc')
            ->where('适用机型','JP01#')
            ->find();
        $rows = db('dic_lzde')
            ->field('sys_bh, rtrim(sys_mc) as sys_mc')
//            ->where('Parent',$row['Key_'])
            ->where('sys_bh','like',$row['sys_bh'].'%')
            ->where('sys_bh','<>',$row['sys_bh'])
            ->select();
        $row['bh_mc'] = $rows;

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

    /**
     * 查询废品系数
     * @ApiMethod (GET)
     */
    public function getScrapFactor()
    {
        //get请求
        if(!$this->request->isGet()){
            $this->error('请求方式错误');
        }
        $req = $this->request->param();
        isset($req['gdbh']) && !empty($req['gdbh']) ? $gdbh = $req['gdbh'] : $this->error('参数错误');
        isset($req['yjno']) && !empty($req['yjno']) ? $yjno = $req['yjno'] : $this->error('参数错误');
        isset($req['gxh'])  && !empty($req['gxh'])  ? $gxh  = $req['gxh']  : $this->error('参数错误');
        isset($req['type']) && !empty($req['type']) ? $type = $req['type'] : $this->error('参数错误');

        switch ($type){
            case '正品板':
                $field = '人工检_正品板 as num';
            case '次品板':
                $field = '人工检_次品板 as num';
            case '废品板':
                $field = '人工检_废检 as num';
        }
        $rows = db('工单_工艺资料')
            ->field($field)
            ->where(['Gy0_gdbh'=>$gdbh, 'Gy0_yjno'=>$yjno, 'Gy0_gxh'=>$gxh])
            ->find();

        $this->success('成功',$rows);
    }
    /**
     * 查询废品系数
     * @ApiMethod (GET)
     */
    public function getMachineScrapFactor()
    {
        //get请求
        if(!$this->request->isGet()){
            $this->error('请求方式错误');
        }
        $req = $this->request->param();
        isset($req['gdbh']) && !empty($req['gdbh']) ? $gdbh = $req['gdbh'] : $this->error('参数错误');
        isset($req['yjno']) && !empty($req['yjno']) ? $yjno = $req['yjno'] : $this->error('参数错误');
        isset($req['gxh'])  && !empty($req['gxh'])  ? $gxh  = $req['gxh']  : $this->error('参数错误');
        isset($req['type']) && !empty($req['type']) ? $type = $req['type'] : $this->error('参数错误');

        switch ($type){
            case '正品板':
                $field = '机检_正品板 as num';
            case '次品板':
                $field = '机检_次品板 as num';
            case '废品板':
                $field = '机检_废检 as num';
        }
        $rows = db('工单_工艺资料')
            ->field($field)
            ->where(['Gy0_gdbh'=>$gdbh, 'Gy0_yjno'=>$yjno, 'Gy0_gxh'=>$gxh])
            ->find();

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

    /**
     * 查询机台
     * @ApiMethod (GET)
     */
    public function getJtbh()
    {
        //get请求
        if(!$this->request->isGet()){
            $this->error('请求方式错误');
        }
        $req = $this->request->param();
        if (isset($req['sys_mc']) && !empty($req['sys_mc'])){
            $sys_mc = $req['sys_mc'];
        }else{
            $this->error('参数错误');
        }
//        $row = db('dic_lzde')
//            ->field('Key_, sys_bh, rtrim(sys_mc) as sys_mc')
//            ->where('sys_mc',$sys_mc)
//            ->find();
        $rows = db('dic_lzde')->alias('d')
            ->field('rtrim(s.设备编号) as jtbh, rtrim(s.设备名称) as jtmc')
//            ->where('Parent',$row['Key_'])
            ->where('d.适用机型','like','JP%')
            ->join(['设备_基本资料'=>'s'],'d.适用机型=s.设备编号')
            ->select();

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

    /**
     * 新增
     * @ApiMethod (POST)
     * @param string
     */
    public function add()
    {
        if(!$this->request->isPost()){
            $this->error('请求方式错误');
        }
        $req = $this->request->param();
        //sczl_num, sczl_gs, sczl_fp
        $arr = [
            'sys_id','sczl_rq','sczl_bzdh','sczl_bh0','sczl_cl0','sczl_fp0',
            'sczl_gdbh','sczl_yjgx','sczl_gxmc',
            'sczl_cl','sczl_dedh','sczl_检验类别','sczl_废品率系数',
            'sczl_jsss','sczl_冲定额',
            'qczl_Num','qczl_NumDesc1','qczl_NumDesc2','qczl_NumDesc3','qczl_NumDesc4',
            'qczl_NumDesc5','qczl_NumDesc6','qczl_NumDesc7','qczl_NumDesc8','qczl_NumDesc',
            'sczl_bh1','sczl_bh2','sczl_bh3','sczl_bh4','sczl_bh5', 'sczl_bh6',
            'sczl_bh7','sczl_bh8','sczl_bh9','sczl_bh10','sczl_bh11', 'sczl_bh12',
            'sczl_cl1','sczl_cl2','sczl_cl3','sczl_cl4','sczl_cl5','sczl_cl6',
            'sczl_cl7','sczl_cl8','sczl_cl9','sczl_cl10','sczl_cl11','sczl_cl12',
            'sczl_fp1','sczl_fp2','sczl_fp3','sczl_fp4','sczl_fp5','sczl_fp6',
            'sczl_fp7','sczl_fp8','sczl_fp9','sczl_fp10','sczl_fp11','sczl_fp12','sczl_desc'
        ];
        $data = [];
        foreach ($arr as $key => $value){
            if (!(isset($req[$value]))){
                $this->error('参数错误',$value,$key+1);
            }
            $data[$value] = $req[$value];
        }
        $data['sys_rq'] = date('Y-m-d H:i:s');

        //查询UniqId
        $UniqId = db('db_手工检验')->max('UniqId');
        $data['UniqId'] = $UniqId < 10000000 ? 10000000 : $UniqId + 1;

        //开启事务
        db()->startTrans();
        try{
            $sql = db('db_手工检验')->fetchSql(true)->insert($data);
            $bool = db()->query($sql);
            // 提交事务
            db()->commit();
        } catch (\Exception $e) {
            // 回滚事务
            db()->rollback();
            $this->error($e->getMessage());
        }

        if($bool===false) $this->error('失败');

        $this->success('成功');

    }

    /**
     * 新增
     * @ApiMethod (POST)
     * @param string
     */
    public function machineAdd()
    {
        if(!$this->request->isPost()){
            $this->error('请求方式错误');
        }
        $req = $this->request->param();
        //sczl_num, sczl_NumDesc, sczl_fp
        $arr = [
            'sys_id','sczl_rq','sczl_bzdh',
            'sczl_gdbh','sczl_yjgx','sczl_gxmc',
            'sczl_jtbh','sczl_单据类型','sczl_检验类别','sczl_废品率系数',
            'sczl_cl','sczl_PgCl','sczl_dedh',
            'qczl_Num','qczl_NumDesc1','qczl_NumDesc2','qczl_NumDesc3','qczl_NumDesc4',
            'qczl_NumDesc5','qczl_NumDesc6','qczl_NumDesc7','qczl_NumDesc8','qczl_NumDesc',
            'sczl_装版工时','sczl_保养工时','sczl_异常停机工时','sczl_设备运行工时',
            'sczl_bh1','sczl_bh2','sczl_bh3','sczl_bh4','sczl_bh5', 'sczl_bh6','sczl_bh7','sczl_bh8',
            'sczl_desc'
        ];
        $data = [];
        foreach ($arr as $key => $value){
            if (!(isset($req[$value]))){
                $this->error('参数错误',$value,$key+1);
            }
            $data[$value] = $req[$value];
        }
        $data['sys_rq'] = date('Y-m-d H:i:s');

        //查询UniqId
        $UniqId = db('db_机器检验')->max('UniqId');
        $data['UniqId'] = $UniqId < 10000000 ? 10000000 : $UniqId + 1;

        //开启事务
        db()->startTrans();
        try{
            $sql = db('db_机器检验')->fetchSql(true)->insert($data);
            $bool = db()->query($sql);
            // 提交事务
            db()->commit();
        } catch (\Exception $e) {
            // 回滚事务
            db()->rollback();
            $this->error($e->getMessage());
        }

        if($bool===false) $this->error('失败');

        $this->success('成功');

    }

    /**
     * 修改
     * @ApiMethod (POST)
     * @param string 'UniqId'
     */
    public function edit()
    {
        if(!$this->request->isPost()){
            $this->error('请求方式错误');
        }
        $req = $this->request->param();
        $arr = [
            'sczl_rq','sczl_bzdh','sczl_bh0','sczl_cl0','sczl_fp0',
            'sczl_gdbh','sczl_yjgx','sczl_gxmc',
            'sczl_cl','sczl_dedh','sczl_检验类别','sczl_废品率系数',
            'sczl_jsss','sczl_冲定额',
            'qczl_Num','qczl_NumDesc1','qczl_NumDesc2','qczl_NumDesc3','qczl_NumDesc4',
            'qczl_NumDesc5','qczl_NumDesc6','qczl_NumDesc7','qczl_NumDesc8','qczl_NumDesc',
            'sczl_bh1','sczl_bh2','sczl_bh3','sczl_bh4','sczl_bh5', 'sczl_bh6',
            'sczl_bh7','sczl_bh8','sczl_bh9','sczl_bh10','sczl_bh11', 'sczl_bh12',
            'sczl_cl1','sczl_cl2','sczl_cl3','sczl_cl4','sczl_cl5','sczl_cl6',
            'sczl_cl7','sczl_cl8','sczl_cl9','sczl_cl10','sczl_cl11','sczl_cl12',
            'sczl_fp1','sczl_fp2','sczl_fp3','sczl_fp4','sczl_fp5','sczl_fp6',
            'sczl_fp7','sczl_fp8','sczl_fp9','sczl_fp10','sczl_fp11','sczl_fp12','sczl_desc'
        ];
        $data = [];
        foreach ($arr as $key => $value){
            if (!(isset($req[$value]))){
                continue;
            }
            $data[$value] = $req[$value];
        }
        if (!(isset($req['UniqId']) && trim($req['UniqId'])!='')){
            $this->error('参数错误','UniqId',100);
        }
        $data['mod_rq'] = date('Y-m-d H:i:s');

        //开启事务
        db()->startTrans();
        try{
            $sql = db('db_手工检验')->where('UniqId',$req['UniqId'])->fetchSql(true)->update($data);
            $bool = db()->query($sql);
            // 提交事务
            db()->commit();
        } catch (\Exception $e) {
            // 回滚事务
            db()->rollback();
            $this->error($e->getMessage());
        }

        if($bool===false) $this->error('失败');

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

    /**
     * 修改
     * @ApiMethod (POST)
     * @param string 'UniqId'
     */
    public function machineEdit()
    {
        if(!$this->request->isPost()){
            $this->error('请求方式错误');
        }
        $req = $this->request->param();
        $arr = [
            'sys_id','sczl_rq','sczl_bzdh',
            'sczl_gdbh','sczl_yjgx','sczl_gxmc',
            'sczl_jtbh','sczl_单据类型','sczl_检验类别','sczl_废品率系数',
            'sczl_cl','sczl_PgCl','sczl_dedh',
            'qczl_Num','qczl_NumDesc1','qczl_NumDesc2','qczl_NumDesc3','qczl_NumDesc4',
            'qczl_NumDesc5','qczl_NumDesc6','qczl_NumDesc7','qczl_NumDesc8','qczl_NumDesc',
            'sczl_装版工时','sczl_保养工时','sczl_异常停机工时','sczl_设备运行工时',
            'sczl_bh1','sczl_bh2','sczl_bh3','sczl_bh4','sczl_bh5', 'sczl_bh6','sczl_bh7','sczl_bh8',
            'sczl_desc'
        ];
        $data = [];
        foreach ($arr as $key => $value){
            if (!(isset($req[$value]))){
                continue;
            }
            $data[$value] = $req[$value];
        }
        if (!(isset($req['UniqId']) && trim($req['UniqId'])!='')){
            $this->error('参数错误','UniqId',100);
        }
        $data['mod_rq'] = date('Y-m-d H:i:s');

        //开启事务
        db()->startTrans();
        try{
            $sql = db('db_机器检验')->where('UniqId',$req['UniqId'])->fetchSql(true)->update($data);
            $bool = db()->query($sql);
            // 提交事务
            db()->commit();
        } catch (\Exception $e) {
            // 回滚事务
            db()->rollback();
            $this->error($e->getMessage());
        }

        if($bool===false) $this->error('失败');

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

    /**
     * 修改
     * @ApiMethod (POST)
     * @param string 'UniqId'
     */
    public function del()
    {
        if(!$this->request->isPost()){
            $this->error('请求方式错误');
        }
        $req = $this->request->param();

        if (!(isset($req['UniqId']) && trim($req['UniqId'])!='')){
            $this->error('参数错误','UniqId',100);
        }

        //开启事务
        db()->startTrans();
        try{
            $bool = db('db_手工检验')->where('UniqId',$req['UniqId'])->delete();

            // 提交事务
            db()->commit();
        } catch (\Exception $e) {
            // 回滚事务
            db()->rollback();
            $this->error($e->getMessage());
        }

        if($bool===false) $this->error('失败');

        $this->success('成功');

    }

    /**
     * 修改
     * @ApiMethod (POST)
     * @param string 'UniqId'
     */
    public function machineDel()
    {
        if(!$this->request->isPost()){
            $this->error('请求方式错误');
        }
        $req = $this->request->param();

        if (!(isset($req['UniqId']) && trim($req['UniqId'])!='')){
            $this->error('参数错误','UniqId',100);
        }

        //开启事务
        db()->startTrans();
        try{
            $bool = db('db_机器检验')->where('UniqId',$req['UniqId'])->delete();

            // 提交事务
            db()->commit();
        } catch (\Exception $e) {
            // 回滚事务
            db()->rollback();
            $this->error($e->getMessage());
        }

        if($bool===false) $this->error('失败');

        $this->success('成功');

    }

    /**
     * 工序汇总导出
     * @ApiMethod (GET)
     * @param string date
     * @param string sys_id
     * @param array fields
     */
    public function export(){
        if(!$this->request->isGet()){
            $this->error('请求方式错误');
        }
        $req = $this->request->param();

        $where = [];
        if (!isset($req['date']) || empty($req['date'])) $this->error('参数缺失');

        if (!(isset($req['fields']) && count($req['fields'])!=0)){
            $this->error('参数错误','fields',100);
        }
        if(strpos($req['date'],'-')){
            $where = ['a.sys_rq'=>['like',$req['date'].'%']];
        }else{
            $where = ['a.sczl_gdbh'=>$req['date']];
        }
        if (isset($req['sys_id']) && !empty($req['sys_id'])){
            $where['a.sys_id'] = ['LIKE',$req['sys_id'].'%'];
        }


        $field = '';
        $arr = [
            'sczl_gdyj'         =>['工单印件','CONCAT(a.sczl_gdbh, "--", rtrim(d.yj_yjmc)) as sczl_gdyj'],
            'sczl_yjgx'         =>['印件工序','CONCAT(rtrim(a.sczl_yjgx), " -", rtrim(a.sczl_gxmc)) as sczl_yjgx'],
            'sczl_rq'           =>['日期','LEFT(a.sczl_rq, 10) as sczl_rq'],
            'sczl_cl'           =>['计件产量','a.sczl_cl'],
            '千件工价'           =>['千件工价','b.千件工价'],
            'sczl_检验类别'      =>['检验类别','rtrim(a.sczl_检验类别) as sczl_检验类别'],
            'sczl_废品率系数'    =>['废品率系数','a.sczl_废品率系数'],
            '日定额'             =>['日定额','b.日定额'],
            'sczl_bh0'          =>['组长编号','a.sczl_bh0'],
            'name'              =>['组长姓名','rtrim(c.员工姓名) as name'],
            'qczl_NumDesc'      =>['流程单备注','a.qczl_NumDesc'],
        ];
        $data[0] = [];
        foreach ($req['fields'] as $k=>$v){
            if(array_key_exists($v,$arr)){
                if ($k==0){
                    $field .= $arr[$v][1];
                }else{
                    $field .= ','.$arr[$v][1];
                }
                array_push($data[0],$arr[$v][0]);
            }
        }

        $rows = db('db_手工检验')->alias('a')
            ->join('dic_lzde b','a.sczl_dedh = b.sys_bh','left')
            ->join('人事_基本资料 c','a.sczl_bh0 = c.员工编号','left')
            ->join('工单_印件资料 d','a.sczl_gdbh = d.Yj_Gdbh','left')
            ->where($where)
            ->where('SUBSTR(a.sczl_yjgx,1,2) = d.yj_Yjno')
            ->field($field)->order('a.sczl_rq,a.UniqId asc')->select();

        foreach ($rows as $key=>$value) {
            if (isset($value['sczl_cl'])) $value['sczl_cl'] = floatval($value['sczl_cl']);
            if (isset($value['千件工价'])) $value['千件工价'] = floatval($value['千件工价']);
            $subArray = [];
            foreach ($req['fields'] as $k=>$v){
                if(array_key_exists($v,$arr)){
                    if($value[$v]){
                        array_push($subArray,$value[$v]);
                    }else{
                        array_push($subArray,'');
                    }
                }
            }
            array_push($data,$subArray);
        }
        $this->success('成功',['file_name'=>$req['file_name'],'data'=>$data]);
    }

    /**
     * 工序汇总导出
     * @ApiMethod (GET)
     * @param string file_name
     * @param string date
     * @param string sys_id
     * @param array fields
     */
    public function machineExport(){
        if(!$this->request->isGet()){
            $this->error('请求方式错误');
        }
        $req = $this->request->param();

        if (!isset($req['date']) || empty($req['date'])) $this->error('参数缺失');

        if (!(isset($req['fields']) && count($req['fields'])!=0)){
            $this->error('参数错误','fields',100);
        }
        if(strpos($req['date'],'-')){
            $where = ['a.sys_rq'=>['like','%'.$req['date'].'%']];
        }else{
            $where = ['a.sczl_gdbh'=>$req['date']];
        }
        if (isset($req['sys_id']) && !empty($req['sys_id'])){
            $where['a.sys_id'] = ['LIKE','%'.$req['sys_id'].'%'];
        }


        $field = '';
        $arr = [
            'sczl_单据类型'      =>['分类','rtrim(a.sczl_单据类型) as sczl_单据类型'],
            'sczl_gdyj'         =>['工单印件','CONCAT(a.sczl_gdbh, "--", rtrim(d.yj_yjmc)) as sczl_gdyj'],
            'sczl_yjgx'         =>['印件工序','CONCAT(rtrim(a.sczl_yjgx), " -", rtrim(a.sczl_gxmc)) as sczl_yjgx'],
            'sczl_jtbh'         =>['机台编号','a.sczl_jtbh'],
            'sczl_rq'           =>['日期','LEFT(a.sczl_rq, 10) as sczl_rq'],
            'sczl_cl'           =>['计件产量','a.sczl_cl'],
            'sczl_检验类别'      =>['检验类别','rtrim(a.sczl_检验类别) as sczl_检验类别'],
            'sczl_废品率系数'    =>['废品率系数','a.sczl_废品率系数'],
            '日定额'             =>['日定额','b.日定额'],
            'sczl_bh1'          =>['员工姓名1','rtrim(c1.员工姓名) as sczl_bh1'],
            'sczl_bh2'          =>['员工姓名2','rtrim(c2.员工姓名) as sczl_bh2'],
            'sczl_bh3'          =>['员工姓名3','rtrim(c3.员工姓名) as sczl_bh3'],
            'sczl_bh4'          =>['员工姓名4','rtrim(c4.员工姓名) as sczl_bh4'],
            'sczl_bzdh'         =>['班组代号','a.sczl_bzdh'],
            '千件工价'           =>['千件工价','b.千件工价'],
            'sczl_设备运行工时'  =>['通电工时','a.sczl_设备运行工时'],
            'qczl_NumDesc'      =>['流程单备注','a.qczl_NumDesc'],
        ];
        $data[0] = [];
        foreach ($req['fields'] as $k=>$v){
            if(array_key_exists($v,$arr)){
                if ($k==0){
                    $field .= $arr[$v][1];
                }else{
                    $field .= ','.$arr[$v][1];
                }
                array_push($data[0],$arr[$v][0]);
            }
        }

        $rows = db('db_机器检验')->alias('a')
            ->join('dic_lzde b','a.sczl_dedh = b.sys_bh','left')
            ->join('人事_基本资料 c1','a.sczl_bh1 = c1.员工编号','left')
            ->join('人事_基本资料 c2','a.sczl_bh2 = c2.员工编号','left')
            ->join('人事_基本资料 c3','a.sczl_bh3 = c3.员工编号','left')
            ->join('人事_基本资料 c4','a.sczl_bh4 = c4.员工编号','left')
            ->join('工单_印件资料 d','a.sczl_gdbh = d.Yj_Gdbh','left')
            ->where($where)
            ->where('SUBSTR(a.sczl_yjgx,1,2) = d.yj_Yjno')
            ->field($field)->order('a.UniqId desc')->select();

        foreach ($rows as $key=>$value) {
            if (isset($value['sczl_cl'])) $value['sczl_cl'] = floatval($value['sczl_cl']);
            if (isset($value['千件工价'])) $value['千件工价'] = floatval($value['千件工价']);
            if (isset($value['sczl_设备运行工时'])) $value['sczl_设备运行工时'] = floatval($value['sczl_设备运行工时']);
            $subArray = [];
            foreach ($req['fields'] as $k=>$v){
                if(array_key_exists($v,$arr)){
                    if($value[$v]){
                        array_push($subArray,$value[$v]);
                    }else{
                        array_push($subArray,'');
                    }
                }
            }
            array_push($data,$subArray);
        }
        $this->success('成功',['file_name'=>$req['file_name'],'data'=>$data]);
    }

    /**
     * 月度汇总导出
     * @ApiMethod (GET)
     * @param string month
     * @param string file_name
     * @param array fields
     */
    public function monthExport(){
        if(!$this->request->isGet()){
            $this->error('请求方式错误');
        }
        $req = $this->request->param();

        if (isset($req['date']) && !empty($req['date'])){
            $where = ['a.sczl_rq'=>['like',$req['date'].'%']];
        }else{
            $this->error('参数缺失');
        }

        if (!(isset($req['fields']) && count($req['fields'])!=0)){
            $this->error('参数错误','fields',100);
        }


        $field = '';
        $arr = [
            'sczl_rq'           =>['日期','LEFT(a.sczl_rq, 10) as sczl_rq'],
            'sczl_gdbh'         =>['工单编号','a.sczl_gdbh'],
            'sczl_gdyj'         =>['工单印件','rtrim(d.yj_yjmc) as sczl_gdyj'],
            'sczl_yjgx'         =>['印件工序','CONCAT(rtrim(a.sczl_yjgx), " -", rtrim(a.sczl_gxmc)) as sczl_yjgx'],
            'sczl_cl'           =>['计件产量','sum(a.sczl_cl) as sczl_cl'],
        ];
        $data[0] = [];
        foreach ($req['fields'] as $k=>$v){
            if(array_key_exists($v,$arr)){
                if ($k==0){
                    $field .= $arr[$v][1];
                }else{
                    $field .= ','.$arr[$v][1];
                }
                array_push($data[0],$arr[$v][0]);
            }
        }

        $rows = db('db_手工检验')->alias('a')
            ->field($field)
            ->where($where)
            ->where('SUBSTR(a.sczl_yjgx,1,2) = d.yj_Yjno')
            ->join('工单_印件资料 d', 'a.sczl_gdbh = d.Yj_Gdbh', 'left')
            ->group('a.sczl_gdbh, d.yj_yjmc, a.sczl_yjgx')
            ->order('a.sczl_gdbh asc')
            ->select();

        foreach ($rows as $key=>$value) {
            if (isset($value['sczl_cl'])) $value['sczl_cl'] = floatval($value['sczl_cl']);
            $subArray = [];
            foreach ($req['fields'] as $k=>$v){
                if(array_key_exists($v,$arr)){
                    if($value[$v]){
                        array_push($subArray,$value[$v]);
                    }else{
                        array_push($subArray,'');
                    }
                }
            }
            array_push($data,$subArray);
        }
        $this->success('成功',['file_name'=>$req['file_name'],'data'=>$data]);
    }

    /**
     * 手检流程单统计导出
     * @ApiMethod (GET)
     * @param string start_time
     * @param string end_time
     * @param string file_name
     * @param array fields
     */
    public function handInspectExport(){
        if(!$this->request->isGet()){
            $this->error('请求方式错误');
        }
        $req = $this->request->param();

        $arr = [
            'start_time', 'end_time', 'file_name'
        ];
        foreach ($arr as $key => $value){
            if (!isset($req[$value])){
                $this->error('参数错误',$value,$key+1);
            }
        }

        if (!(isset($req['fields']) && count($req['fields'])!=0)){
            $this->error('参数错误','fields',100);
        }


        $field = '';
        $arr = [
            'sczl_gdbh'         =>['工单编号',''],
            'sczl_yjgx'         =>['印件工序','rtrim(sczl_yjgx) as sczl_yjgx'],
            'sczl_gxmc'         =>['工序名称','rtrim(sczl_gxmc) as sczl_gxmc'],
            'sczl_rq'           =>['日期','sczl_rq'],
            'sczl_Num'          =>['流程单号','qczl_Num,qczl_NumDesc1,qczl_NumDesc2,qczl_NumDesc3,qczl_NumDesc4,qczl_NumDesc5,qczl_NumDesc6,qczl_NumDesc7,qczl_NumDesc8'],
            'sczl_cl'           =>['质检产量','qczl_Num,qczl_NumDesc1,qczl_NumDesc2,qczl_NumDesc3,qczl_NumDesc4,qczl_NumDesc5,qczl_NumDesc6,qczl_NumDesc7,qczl_NumDesc8,sczl_cl'],
        ];
        $data[0] = [];
        foreach ($req['fields'] as $k=>$v){
            if(array_key_exists($v,$arr)){
                if ($k==0){
                    $field .= $arr[$v][1];
                }else{
                    $field .= ','.$arr[$v][1];
                }
                array_push($data[0],$arr[$v][0]);
            }
        }

        $rows = db('db_手工检验')
            ->field('sczl_gdbh,'.$field)
            ->where('sczl_rq',['>=',$req['start_time']],['<=',$req['end_time']],'and')
            ->where('qczl_Num|qczl_NumDesc1|qczl_NumDesc2|qczl_NumDesc3|qczl_NumDesc4|qczl_NumDesc5|qczl_NumDesc6|qczl_NumDesc7|qczl_NumDesc8','<>',0)
            ->order('sczl_gdbh asc')
            ->select();

        $data = [];
        $brr = [
            'qczl_Num','qczl_NumDesc1','qczl_NumDesc2','qczl_NumDesc3','qczl_NumDesc4',
            'qczl_NumDesc5','qczl_NumDesc6','qczl_NumDesc7','qczl_NumDesc8'
        ];
        foreach($rows as $key=>$value){
            $flag = 0;
            foreach($brr as $ke=>$va){
                if ($value[$va]!=0){
                    $subArray = [];
                    foreach ($req['fields'] as $k=>$v) {
                        if (array_key_exists($v, $arr)) {
                            if ($v=='sczl_cl'){
                                if ($flag==0){
                                    $subArray['sczl_cl'] = $value['sczl_cl'];
                                }else{
                                    $subArray['sczl_cl'] = 0;
                                }
                            }else if ($v=='sczl_Num'){
                                $subArray['sczl_Num'] = $value[$va];
                            }else{
                                $subArray[$v] = $value[$v];
                            }

                        }
                    }
                    $flag++;
                    if(array_key_exists($value['sczl_gdbh'].'-'.$value['sczl_rq'].'-'.$value[$va],$data)){
                        if(array_key_exists('sczl_cl',$subArray)){
                            $data[$value['sczl_gdbh'].'-'.$value['sczl_rq'].'-'.$value[$va]]['sczl_cl'] += $value['sczl_cl'];
                        }
                    }else{
                        $data[$value['sczl_gdbh'].'-'.$value['sczl_rq'].'-'.$value[$va]] = $subArray;
                    }
                }
            }

        }
        $data = array_values($data);

        $this->success('成功',['file_name'=>$req['file_name'],'data'=>$data]);
    }
}