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

<?php

namespace app\api\controller;

use app\common\controller\Api;

/**
 * 机台生产日报表维护接口
 */
class MachineProductionReport extends Api
{
    protected $noNeedLogin = ['*'];
    protected $noNeedRight = ['*'];

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

    /**
     * 获取机台生产日报表侧边栏
     * @ApiMethod (GET)
     */
    public function getTab()
    {
        //get请求
        if(!$this->request->isGet()){
            $this->error('请求方式错误');
        }

        $time = date('Y-m-d',time()-8640000);
        $sql = 'SELECT date, COUNT(date) as counts
            FROM (
                SELECT LEFT(sys_rq, 10) as date FROM db_sczl WHERE sys_rq > "'.$time.'"
            UNION ALL
                SELECT LEFT(sys_rq, 10) as date FROM 设备_产量计酬 WHERE sys_rq > "'.$time.'"
            ) AS t1
            GROUP BY date
            ORDER BY date DESC
            LIMIT 30';
        $rows = db()->query($sql);

        if(count($rows) < 30){
            $sql = 'SELECT date, COUNT(date) as counts
            FROM (
                SELECT LEFT(sys_rq, 10) as date FROM db_sczl WHERE sys_rq > "2023-11-01"
            UNION ALL
                SELECT LEFT(sys_rq, 10) as date FROM 设备_产量计酬 WHERE sys_rq > "2023-11-01"
            ) AS t1
            GROUP BY date
            ORDER BY date DESC
            LIMIT 30';
        $rows = db()->query($sql);
       }

        $sql = 'SELECT date, sys_id, COUNT(sys_id) as count
            FROM (
                SELECT LEFT(sys_rq, 10) as date, rtrim(sys_id) as sys_id 
                FROM db_sczl WHERE sys_rq >= "'.$rows[count($rows)-1]['date'].'"
            UNION ALL
                SELECT LEFT(sys_rq, 10) as date, rtrim(sys_id) as sys_id 
                FROM 设备_产量计酬 WHERE sys_rq >= "'.$rows[count($rows)-1]['date'].'"
            ) AS t1
            GROUP BY date, sys_id';
        $data = db()->query($sql);
        
        $result = [];
        foreach ($data as $item) {
            $date = $item['date'];
            $sysId = $item['sys_id'];
            $count = $item['count'];

            // 如果结果数组中不存在该日期和系统ID的记录，则创建新记录
            if (!isset($result[$date][$sysId])) {
                if (substr($sysId,1,3)=='00-'){
                    if (isset($result[$date])){
                        $result[$date] = array_merge([$sysId=>0],$result[$date]);
                    }else{
                        $result[$date][$sysId] = 0;
                    }
                }else{
                    $result[$date][$sysId] = 0;
                }
            }

            // 将count累加到对应的记录中
            $result[$date][$sysId] += $count;
        }

        foreach($rows as $key=>$value){
            $rows[$key]['sys'] = $result[$value['date']];
            $rows[$key]['date'] = str_replace('-', '.', $rows[$key]['date']);
        }

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

    /**
     * 通过工单获取机台生产日报表侧边栏
     * @ApiMethod (GET)
     */
    public function getTabByGdbh()
    {
        //get请求
        if(!$this->request->isGet()){
            $this->error('请求方式错误');
        }

        $date = date('Y-m-d',strtotime("-1 year"));
        $sql = 'SELECT t1.`sczl_gdbh`,t2.`yj_yjmc`
        FROM (
            SELECT DISTINCT `sczl_gdbh`, sczl_yjno FROM `db_sczl` where sys_rq >"'.$date.'"
            UNION
            SELECT  DISTINCT `sczl_gdbh`, sczl_yjno FROM `设备_产量计酬` where sys_rq >"'.$date.'"
        ) AS t1
        JOIN `工单_印件资料` AS t2 ON t1.`sczl_gdbh` = t2.`Yj_Gdbh`
        WHERE t1.sczl_yjno = t2.yj_Yjno
        ORDER BY t1.`sczl_gdbh` DESC
        LIMIT 65';
        $rows = db()->query($sql);

        $sql = 'SELECT sczl_gdbh,rtrim(sys_id) as sys_id
            FROM (
                SELECT sczl_gdbh,rtrim(sys_id) as sys_id
                FROM db_sczl WHERE sczl_gdbh >= "'.$rows[count($rows)-1]['sczl_gdbh'].'"
            UNION ALL
                SELECT sczl_gdbh,rtrim(sys_id) as sys_id
                FROM 设备_产量计酬 WHERE sczl_gdbh >= "'.$rows[count($rows)-1]['sczl_gdbh'].'"
            ) AS t1
            ORDER BY sys_id';
        $arr = db()->query($sql);

        // $arr1 = db('设备_产量计酬')->cache(true,3600)
        //     ->field('sczl_gdbh,rtrim(sys_id) as sys_id')
        //     ->where('sczl_gdbh','>=',$rows[count($rows)-1]['sczl_gdbh'])
        //     ->select();

        // $arr2 = db('db_sczl')->cache(true,3600)
        //     ->field('sczl_gdbh,rtrim(sys_id) as sys_id')
        //     ->where('sczl_gdbh','>=',$rows[count($rows)-1]['sczl_gdbh'])
        //     ->select();
        // $arr = array_merge($arr1,$arr2);

        $brr = [];
        foreach($arr as $k=>$v){
            if(array_key_exists($v['sczl_gdbh'],$brr)){
                if(array_key_exists($v['sys_id'],$brr[$v['sczl_gdbh']])){
                    $brr[$v['sczl_gdbh']][$v['sys_id']] += 1;
                }else{
                    $brr[$v['sczl_gdbh']][$v['sys_id']] = 1;
                }
            }else{
                $brr[$v['sczl_gdbh']][$v['sys_id']] = 1;
            }
        }

        foreach($rows as $key=>$value){
            $rows[$key]['sys'] = $brr[$value['sczl_gdbh']];
            $rows[$key]['yj_yjmc'] = trim($value['yj_yjmc']);
        }

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

    /**
     * 获取机台生产日报表列表
     * @ApiMethod (GET)
     * @param string $date 时间
     * @param string $sys_id 用户
     */
    public function getList()
    {
        //get请求
        if(!$this->request->isGet()){
            $this->error('请求方式错误');
        }
        $req = $this->request->param();

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

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

        //获取total
        $sql = 'SELECT sum(count) AS count FROM (
            SELECT COUNT(*) AS count FROM `设备_产量计酬` ';
            $sql .= strpos($req['date'],'-') ? 'WHERE `sys_rq` LIKE "%'.$req['date'].'%" ' : 'WHERE `sczl_gdbh` = "'.$req['date'].'" ';
            $sql .= isset($req['sys_id']) && !empty($req['sys_id']) ? 'AND `sys_id` LIKE "%'.$req['sys_id'].'%" ' : '';
            $sql .= 'UNION ALL SELECT COUNT(*) AS count FROM `db_sczl`';
            $sql .= strpos($req['date'],'-') ? 'WHERE `sys_rq` LIKE "%'.$req['date'].'%" ' : 'WHERE `sczl_gdbh` = "'.$req['date'].'" ';
            $sql .= isset($req['sys_id']) && !empty($req['sys_id']) ? 'AND `sys_id` LIKE "%'.$req['sys_id'].'%" ' : '';
            $sql .= ') as t';
        $total = db()->query($sql)[0]['count'];

        //判断是否存在缓存
        if (!cache('ReportGetList-'.$req['date'].'-'.$req['sys_id'].'-'.$req['page'].'-'.$req['limit'])){
            //UNION 查询两表数据
            // db_sczl缺少数据 --> a.sczl_来料少数,
            // 设备_产量计酬缺少数据 --> a.拆片联拼系数, a.拆片条小盒系数,
            $sql = 'SELECT a.sczl_gdbh, a.sczl_yjno, a.sczl_gxh, a.sczl_type, LEFT(a.sczl_rq, 10) as sczl_rq, a.sczl_jtbh, 
                        a.sczl_bzdh, a.sczl_num, a.sczl_cl, a.sczl_ms, a.sczl_zcfp, a.sczl_zccp,
                        a.sczl_装版工时, a.sczl_保养工时, a.sczl_打样工时, a.sczl_异常停机工时, a.sczl_设备运行工时, 
                        a.sczl_bh1, a.sczl_bh2, a.sczl_bh3, a.sczl_bh4, a.sczl_bh5, a.sczl_bh6, a.sczl_工价系数, 
                        d.日定额, d.千件工价, d.补产标准, a.sczl_来料少数, NULL AS 拆片联拼系数, NULL AS 拆片条小盒系数, 
                        rtrim(a.sys_id) as sys_id, a.sys_rq, a.mod_rq, a.UniqId, "设备_产量计酬" AS table_type
                    FROM `设备_产量计酬` `a` INNER JOIN `dic_lzde` `d` ON `a`.`sczl_dedh`=`d`.`sys_bh` ';
            $sql .= strpos($req['date'],'-') ? 'WHERE `a`.`sys_rq` LIKE "%'.$req['date'].'%" ' : 'WHERE `a`.`sczl_gdbh` = "'.$req['date'].'" ';
            $sql .= isset($req['sys_id']) && !empty($req['sys_id']) ? 'AND `a`.`sys_id` LIKE "%'.$req['sys_id'].'%" ' : '';
            $sql .= 'UNION ALL
                    SELECT a.sczl_gdbh, a.sczl_yjno, a.sczl_gxh, a.sczl_type, LEFT(a.sczl_rq, 10) as sczl_rq, a.sczl_jtbh,
                        a.sczl_bzdh, a.sczl_num, a.sczl_cl, a.sczl_ms, a.sczl_fp as sczl_zcfp, a.sczl_cp as sczl_zccp,
                        a.sczl_装版工时, a.sczl_保养工时, a.sczl_打样工时, a.sczl_异常停机工时, a.sczl_设备运行工时,
                        a.sczl_bh1, a.sczl_bh2, a.sczl_bh3, a.sczl_bh4, a.sczl_bh5, a.sczl_bh6, a.sczl_工价系数, 
                        d.日定额, d.千件工价, d.补产标准, NULL AS sczl_来料少数, a.拆片联拼系数, a.拆片条小盒系数,
                        rtrim(a.sys_id) as sys_id, a.sys_rq, a.mod_rq, a.UniqId, "db_sczl" AS table_type
                    FROM `db_sczl` `a` INNER JOIN `dic_lzde` `d` ON `a`.`sczl_dedh`=`d`.`sys_bh`';
            $sql .= strpos($req['date'],'-') ? 'WHERE `a`.`sys_rq` LIKE "%'.$req['date'].'%" ' : 'WHERE `a`.`sczl_gdbh` = "'.$req['date'].'" ';
            $sql .= isset($req['sys_id']) && !empty($req['sys_id']) ? 'AND `a`.`sys_id` LIKE "%'.$req['sys_id'].'%" ' : '';
            $sql .= 'ORDER BY sys_id ASC, sczl_num ASC LIMIT '.($page-1)*$limit.','.$limit;
            $rows = db()->query($sql);

            //设置缓存
            cache('ReportGetList-'.$req['date'].'-'.$req['sys_id'].'-'.$req['page'].'-'.$req['limit'],$rows,3600);
        }else{
            $rows = cache('ReportGetList-'.$req['date'].'-'.$req['sys_id'].'-'.$req['page'].'-'.$req['limit']);
        }

        $gd = db('工单_基本资料')->column('Gd_gdbh, 成品名称 as Gd_cpmc');
        $rs = db('人事_基本资料')->column('员工编号, 员工姓名');
        foreach ($rows as $key=>$value){
            $rows[$key]['mod_rq'] = $value['mod_rq']=='1900-01-01 00:00:00' ? '' :$value['mod_rq'];
            $rows[$key]['Gd_cpmc'] = array_key_exists($value['sczl_gdbh'],$gd) ? trim($gd[$value['sczl_gdbh']]) : '';
            $rows[$key]['name1'] = array_key_exists($value['sczl_bh1'],$rs) ? trim($rs[$value['sczl_bh1']]) : '';
            for ($i=2;$i<=6;$i++){
                if ($value['sczl_bh'.$i]){
                    if ($value['sczl_bh'.$i]==$value['sczl_bh1']){
                        $rows[$key]['name'.$i] = $rows[$key]['name1'];
                    }else{
                        $rows[$key]['name1'] = array_key_exists($value['sczl_bh1'],$rs) ? trim($rs[$value['sczl_bh1']]) : '';
                    }
                }else{
                    $rows[$key]['name'.$i] = '';
                }
            }
            $rows[$key]['jyno_gxmc'] = sprintf("%02d", $value['sczl_yjno']).'-'.sprintf("%02d", $value['sczl_gxh']).'-->'.trim($value['sczl_type']);
            $rows[$key]['sczl_num'] = $value['sczl_num'] == 0 ? '' : $value['sczl_num'];
            $rows[$key]['sczl_cl'] = $value['sczl_cl'] == 0 ? '' : floatval($value['sczl_cl']);
            $rows[$key]['sczl_ms'] = $value['sczl_ms'] == 0 ? 1 : floatval($value['sczl_ms']);
            $rows[$key]['sczl_zcfp'] = $value['sczl_zcfp'] == 0 ? '' : floatval($value['sczl_zcfp']);
            $rows[$key]['sczl_zccp'] = $value['sczl_zccp'] == 0 ? '' : floatval($value['sczl_zccp']);
            $rows[$key]['sczl_装版工时'] = $value['sczl_装版工时'] == 0 ? '' : floatval($value['sczl_装版工时']);
            $rows[$key]['sczl_保养工时'] = $value['sczl_保养工时'] == 0 ? '' : floatval($value['sczl_保养工时']);
            $rows[$key]['sczl_打样工时'] = $value['sczl_打样工时'] == 0 ? '' : floatval($value['sczl_打样工时']);
            $rows[$key]['sczl_异常停机工时'] = $value['sczl_异常停机工时'] == 0 ? '' : floatval($value['sczl_异常停机工时']);
            $rows[$key]['sczl_设备运行工时'] = $value['sczl_设备运行工时'] == 0 ? '' : floatval($value['sczl_设备运行工时']);
            $rows[$key]['sczl_工价系数'] = $value['sczl_工价系数'] == 0 ? '' : floatval($value['sczl_工价系数']);
            $rows[$key]['千件工价'] = $value['千件工价'] == 0 ? '' : floatval($value['千件工价']);
            $rows[$key]['sczl_来料少数'] = $value['sczl_来料少数'] == 0 ? '' : floatval($value['sczl_来料少数']);
            $rows[$key]['拆片联拼系数'] = $value['拆片联拼系数'] == 0 ? '' : floatval($value['拆片联拼系数']);
            $rows[$key]['拆片条小盒系数'] = $value['拆片条小盒系数'] == 0 ? '' : floatval($value['拆片条小盒系数']);

            unset($rows[$key]['sczl_yjno']);
            unset($rows[$key]['sczl_gxh']);
            unset($rows[$key]['sczl_type']);
        }

        $data = [
            'total'         => $total,
            'rows'          => $rows,
        ];
        $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'])){
            //获取total
            $sql = 'SELECT sum(count) AS count FROM (
                SELECT COUNT(*) AS count FROM `设备_产量计酬` ';
                $sql .= 'WHERE `sczl_gdbh` LIKE "%'.$req['gdbh'].'%" ';
                $sql .= 'UNION ALL SELECT COUNT(*) AS count FROM `db_sczl`';
                $sql .= 'WHERE `sczl_gdbh` LIKE "%'.$req['gdbh'].'%" ';
                $sql .= ') as t';
            $total = db()->query($sql)[0]['count'];

            //判断缓存是否存在
            if (!cache('ReportGetList-'.$req['gdbh'].'-'.$req['page'].'-'.$req['limit'])){
                //a.拆片联拼系数, a.拆片条小盒系数,
                $sql = 'SELECT a.sczl_gdbh, a.sczl_yjno, a.sczl_gxh, a.sczl_type, LEFT(a.sczl_rq, 10) as sczl_rq, a.sczl_jtbh, 
                            a.sczl_bzdh, a.sczl_num, a.sczl_cl, a.sczl_ms, a.sczl_zcfp, a.sczl_zccp,
                            a.sczl_装版工时, a.sczl_保养工时, a.sczl_打样工时, a.sczl_异常停机工时, a.sczl_设备运行工时, 
                            a.sczl_bh1, a.sczl_bh2, a.sczl_bh3, a.sczl_bh4, a.sczl_bh5, a.sczl_bh6, a.sczl_工价系数, 
                            d.日定额, d.千件工价, d.补产标准, a.sczl_来料少数, NULL AS 拆片联拼系数, NULL AS 拆片条小盒系数, 
                            rtrim(a.sys_id) as sys_id, a.sys_rq, a.mod_rq, a.UniqId, "设备_产量计酬" AS table_type
                        FROM `设备_产量计酬` `a` INNER JOIN `dic_lzde` `d` ON `a`.`sczl_dedh`=`d`.`sys_bh` ';
                $sql .= 'WHERE `a`.`sczl_gdbh` LIKE "%'.$req['gdbh'].'%" ';
                $sql .= 'UNION ALL
                        SELECT a.sczl_gdbh, a.sczl_yjno, a.sczl_gxh, a.sczl_type, LEFT(a.sczl_rq, 10) as sczl_rq, a.sczl_jtbh,
                            a.sczl_bzdh, a.sczl_num, a.sczl_cl, a.sczl_ms, a.sczl_fp as sczl_zcfp, a.sczl_cp as sczl_zccp,
                            a.sczl_装版工时, a.sczl_保养工时, a.sczl_打样工时, a.sczl_异常停机工时, a.sczl_设备运行工时,
                            a.sczl_bh1, a.sczl_bh2, a.sczl_bh3, a.sczl_bh4, a.sczl_bh5, a.sczl_bh6, a.sczl_工价系数, 
                            d.日定额, d.千件工价, d.补产标准, NULL AS sczl_来料少数, a.拆片联拼系数, a.拆片条小盒系数,
                            rtrim(a.sys_id) as sys_id, a.sys_rq, a.mod_rq, a.UniqId, "db_sczl" AS table_type
                        FROM `db_sczl` `a` INNER JOIN `dic_lzde` `d` ON `a`.`sczl_dedh`=`d`.`sys_bh`';
                $sql .= 'WHERE `a`.`sczl_gdbh` LIKE "%'.$req['gdbh'].'%" ';
                $sql .= 'ORDER BY sys_id ASC, sczl_num ASC LIMIT '.($page-1)*$limit.','.$limit;
                $rows = db()->query($sql);

                cache('ReportGetList-'.$req['gdbh'].'-'.$req['page'].'-'.$req['limit'],$rows,3600);
            }else{
                $rows = cache('ReportGetList-'.$req['gdbh'].'-'.$req['page'].'-'.$req['limit']);
            }
        }else{
            if (isset($req['cpmc']) && !empty($req['cpmc'])){
                //查询包括该名称的工单号
                $gd = db('工单_基本资料')
                    ->where('Gd_cpmc', 'LIKE', '%'.$req['cpmc'].'%')
                    ->column('Gd_gdbh');
                $in = "'" . implode("','", $gd) . "'";

                //获取total
                $sql = 'SELECT sum(count) AS count FROM (
                    SELECT COUNT(*) AS count FROM `设备_产量计酬` ';
                    $sql .= 'WHERE `sczl_gdbh` IN ('.$in.') ';
                    $sql .= 'UNION ALL SELECT COUNT(*) AS count FROM `db_sczl`';
                    $sql .= 'WHERE `sczl_gdbh` IN ('.$in.') ';
                    $sql .= ') as t';
                $total = db()->query($sql)[0]['count'];

                if (!cache('ReportGetList-'.$req['gdbh'].'-'.$req['cpmc'].'-'.$req['limit'])){
                    $sql = 'SELECT a.sczl_gdbh, a.sczl_yjno, a.sczl_gxh, a.sczl_type, LEFT(a.sczl_rq, 10) as sczl_rq, a.sczl_jtbh, 
                                a.sczl_bzdh, a.sczl_num, a.sczl_cl, a.sczl_ms, a.sczl_zcfp, a.sczl_zccp,
                                a.sczl_装版工时, a.sczl_保养工时, a.sczl_打样工时, a.sczl_异常停机工时, a.sczl_设备运行工时, 
                                a.sczl_bh1, a.sczl_bh2, a.sczl_bh3, a.sczl_bh4, a.sczl_bh5, a.sczl_bh6, a.sczl_工价系数, 
                                d.日定额, d.千件工价, d.补产标准, a.sczl_来料少数, NULL AS 拆片联拼系数, NULL AS 拆片条小盒系数, 
                                rtrim(a.sys_id) as sys_id, a.sys_rq, a.mod_rq, a.UniqId, "设备_产量计酬" AS table_type 
                            FROM `设备_产量计酬` `a` INNER JOIN `dic_lzde` `d` ON `a`.`sczl_dedh`=`d`.`sys_bh` ';
                    $sql .= 'WHERE `a`.`sczl_gdbh` IN ('.$in.') ';
                    $sql .= 'UNION ALL
                            SELECT a.sczl_gdbh, a.sczl_yjno, a.sczl_gxh, a.sczl_type, LEFT(a.sczl_rq, 10) as sczl_rq, a.sczl_jtbh,
                                a.sczl_bzdh, a.sczl_num, a.sczl_cl, a.sczl_ms, a.sczl_fp as sczl_zcfp, a.sczl_cp as sczl_zccp,
                                a.sczl_装版工时, a.sczl_保养工时, a.sczl_打样工时, a.sczl_异常停机工时, a.sczl_设备运行工时,
                                a.sczl_bh1, a.sczl_bh2, a.sczl_bh3, a.sczl_bh4, a.sczl_bh5, a.sczl_bh6, a.sczl_工价系数, 
                                d.日定额, d.千件工价, d.补产标准, NULL AS sczl_来料少数, a.拆片联拼系数, a.拆片条小盒系数,
                                rtrim(a.sys_id) as sys_id, a.sys_rq, a.mod_rq, a.UniqId, "db_sczl" AS table_type 
                            FROM `db_sczl` `a` INNER JOIN `dic_lzde` `d` ON `a`.`sczl_dedh`=`d`.`sys_bh` ';
                    $sql .= 'WHERE `a`.`sczl_gdbh` IN ('.$in.') ';
                    $sql .= 'ORDER BY sys_id ASC, sczl_num ASC LIMIT '.($page-1)*$limit.','.$limit;
                    $rows = db()->query($sql);
                    cache('ReportGetList-'.$req['cpmc'].'-'.$req['page'].'-'.$req['limit'],$rows,3600);
                }else{
                    $rows = cache('ReportGetList-'.$req['cpmc'].'-'.$req['page'].'-'.$req['limit']);
                }
            }else{
                $this->error('参数错误');
            }
        }

        $gd = db('工单_基本资料')->column('Gd_gdbh, 成品名称 as Gd_cpmc');
        $rs = db('人事_基本资料')->column('员工编号, 员工姓名');
        foreach ($rows as $key=>$value){
            $rows[$key]['mod_rq'] = $value['mod_rq']=='1900-01-01 00:00:00' ? '' :$value['mod_rq'];
            $rows[$key]['Gd_cpmc'] = array_key_exists($value['sczl_gdbh'],$gd) ? trim($gd[$value['sczl_gdbh']]) : '';
            $rows[$key]['name1'] = array_key_exists($value['sczl_bh1'],$rs) ? trim($rs[$value['sczl_bh1']]) : '';
            for ($i=2;$i<=6;$i++){
                if ($value['sczl_bh'.$i]){
                    if ($value['sczl_bh'.$i]==$value['sczl_bh1']){
                        $rows[$key]['name'.$i] = $rows[$key]['name1'];
                    }else{
                        $rows[$key]['name1'] = array_key_exists($value['sczl_bh1'],$rs) ? trim($rs[$value['sczl_bh1']]) : '';
                    }
                }else{
                    $rows[$key]['name'.$i] = '';
                }
            }
            $rows[$key]['jyno_gxmc'] = sprintf("%02d", $value['sczl_yjno']).'-'.sprintf("%02d", $value['sczl_gxh']).'-->'.trim($value['sczl_type']);
            $rows[$key]['sczl_num'] = $value['sczl_num'] == 0 ? '' : $value['sczl_num'];
            $rows[$key]['sczl_cl'] = $value['sczl_cl'] == 0 ? '' : floatval($value['sczl_cl']);
            $rows[$key]['sczl_ms'] = $value['sczl_ms'] == 0 ? 1 : floatval($value['sczl_ms']);
            $rows[$key]['sczl_zcfp'] = $value['sczl_zcfp'] == 0 ? '' : floatval($value['sczl_zcfp']);
            $rows[$key]['sczl_zccp'] = $value['sczl_zccp'] == 0 ? '' : floatval($value['sczl_zccp']);
            $rows[$key]['sczl_装版工时'] = $value['sczl_装版工时'] == 0 ? '' : floatval($value['sczl_装版工时']);
            $rows[$key]['sczl_保养工时'] = $value['sczl_保养工时'] == 0 ? '' : floatval($value['sczl_保养工时']);
            $rows[$key]['sczl_打样工时'] = $value['sczl_打样工时'] == 0 ? '' : floatval($value['sczl_打样工时']);
            $rows[$key]['sczl_异常停机工时'] = $value['sczl_异常停机工时'] == 0 ? '' : floatval($value['sczl_异常停机工时']);
            $rows[$key]['sczl_设备运行工时'] = $value['sczl_设备运行工时'] == 0 ? '' : floatval($value['sczl_设备运行工时']);
            $rows[$key]['sczl_工价系数'] = $value['sczl_工价系数'] == 0 ? '' : floatval($value['sczl_工价系数']);
            $rows[$key]['千件工价'] = $value['千件工价'] == 0 ? '' : floatval($value['千件工价']);
            $rows[$key]['sczl_来料少数'] = $value['sczl_来料少数'] == 0 ? '' : floatval($value['sczl_来料少数']);
            $rows[$key]['拆片联拼系数'] = $value['拆片联拼系数'] == 0 ? '' : floatval($value['拆片联拼系数']);
            $rows[$key]['拆片条小盒系数'] = $value['拆片条小盒系数'] == 0 ? '' : floatval($value['拆片条小盒系数']);

            unset($rows[$key]['sczl_yjno']);
            unset($rows[$key]['sczl_gxh']);
            unset($rows[$key]['sczl_type']);
        }

        $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('参数错误');
        }

        $rows = db('db_sczl')->alias('a')
            ->field('a.sczl_gdbh, rtrim(j.成品名称) as Gd_cpmc, 
            a.sczl_yjno, rtrim(y.yj_yjmc) as yj_yjmc, 
            rtrim(a.sczl_gxmc) as sczl_gxmc, a.sczl_gxh, rtrim(a.sczl_type) as sczl_type, 
            LEFT(a.sczl_rq, 10) as sczl_rq, a.sczl_jtbh, a.sczl_bzdh, 
            a.sczl_ms, a.sczl_ls, a.sczl_cl, a.sczl_fp as sczl_zcfp, 
            a.sczl_装版总工时, a.sczl_装版工时, a.sczl_保养工时, a.sczl_异常停机工时, 
            a.sczl_打样总工时, a.sczl_打样工时, a.sczl_设备运行工时, 
            a.拆片联拼系数, a.拆片条小盒系数, 
            a.sczl_dedh, a.sczl_工价系数, a.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.sczl_bh9, a.sczl_bh10,
            a.sczl_rate1, a.sczl_rate2, a.sczl_rate3, a.sczl_rate4, a.sczl_rate5, 
            a.sczl_rate6, a.sczl_rate7, a.sczl_rate8, a.sczl_rate9, a.sczl_rate10
            ')
            ->where('a.UniqId', $UniqId)
            ->where('a.sczl_yjno = y.yj_Yjno')
            ->join(['工单_基本资料'=>'j'],'a.sczl_gdbh = j.Gd_gdbh')
            ->join(['工单_印件资料'=>'y'],'a.sczl_gdbh = y.Yj_Gdbh')
            ->find();
        $rs = db('人事_基本资料')->column('员工编号, 员工姓名');
        for($i=1;$i<=10;$i++){
            if(trim($rows['sczl_bh'.$i])!=''){
                $rows['sczl_bh'.$i.'_name'] = array_key_exists($rows['sczl_bh'.$i],$rs) ? trim($rs[$rows['sczl_bh'.$i]]) : '';
            }else{
                $rows['sczl_bh'.$i.'_name'] = '';
            }
        }

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

    /**
     * 获取工单名称
     * @ApiMethod (GET)
     * @param string $gdbh 工单编号
     */
    public function getGdmc()
    {
        //get请求
        if(!$this->request->isGet()){
            $this->error('请求方式错误');
        }
        $req = $this->request->param();

        if (isset($req['gdbh']) && !empty($req['gdbh'])){
            $gdbh = $req['gdbh'];
        }else{
            $this->error('参数错误');
        }

        $row = db('工单_基本资料')
            ->field('rtrim(Gd_cpmc) as Gd_cpmc')
            ->where('Gd_gdbh',$gdbh)
            ->find();

        if (!$row) $this->error('失败');
        $this->success('成功',$row);
    }

    /**
     * 获取印件名称
     * @ApiMethod (GET)
     * @param string $gdbh 工单编号
     * @param string $yjno 印件号
     */
    public function getYjmc()
    {
        //get请求
        if(!$this->request->isGet()){
            $this->error('请求方式错误');
        }
        $req = $this->request->param();

        if (isset($req['gdbh']) && !empty($req['gdbh'])){
            $gdbh = $req['gdbh'];
        }else{
            $this->error('参数错误');
        }
        if (isset($req['yjno']) && !empty($req['yjno'])){
            $yjno = $req['yjno'];
        }else{
            $this->error('参数错误');
        }

        $row = db('工单_印件资料')
            ->field('rtrim(yj_yjmc) as yj_yjmc')
            ->where(['Yj_Gdbh'=>$gdbh,'yj_Yjno'=>$yjno])
            ->find();

        if (!$row) $this->error('失败');
        $this->success('成功',$row);
    }

    /**
     * 获取工序
     * @ApiMethod (GET)
     * @param string $gdbh 工单编号
     * @param string $yjno 印件号
     * @param string $keyword 关键词
     */
    public function getGx()
    {
        //get请求
        if(!$this->request->isGet()){
            $this->error('请求方式错误');
        }
        $req = $this->request->param();

        if (isset($req['gdbh']) && !empty($req['gdbh'])){
            $gdbh = $req['gdbh'];
        }else{
            $this->error('参数错误');
        }
        if (isset($req['yjno']) && !empty($req['yjno'])){
            $yjno = $req['yjno'];
        }else{
            $this->error('参数错误');
        }
        if (isset($req['keyword']) && !empty($req['keyword'])){
            $keyword = $req['keyword'];
        }else{
            $keyword = '';
        }

        $rows = db('工单_工艺资料')
            ->field('Gy0_gxh as sczl_gxh, rtrim(Gy0_gxmc) as sczl_type, rtrim(Add_gxmc) as Add_gxmc,Gy0_ls as sczl_ls, Gy0_ms as sczl_ms')
            ->where(['Gy0_gdbh'=>$gdbh,'Gy0_yjno'=>$yjno])
            ->where('Gy0_gxh|Gy0_gxmc|Add_gxmc','like','%'.$keyword.'%')
            ->select();
        foreach ($rows as $key=>$value) {
            if($value['Add_gxmc'] != ''){
                $rows[$key]['sczl_gxmc'] = sprintf("%02d", $value['sczl_gxh']).'-'.$value['Add_gxmc'];
            }else{
                $rows[$key]['sczl_gxmc'] = sprintf("%02d", $value['sczl_gxh']).'-'.$value['sczl_type'];
            }
            unset($rows[$key]['Add_gxmc']);
        }
        if (!$rows) $this->error('失败');
        $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('Key_, sys_bh, rtrim(sys_mc) as sys_mc')
            ->where('Parent',$row['Key_'])
            ->order('sys_bh asc')
            ->select();
        foreach ($rows as $key=>$value){
            $bool = db('dic_lzde')
                ->field('Key_,sys_bh, rtrim(sys_mc) as sys_mc')
                ->where('Parent',$value['Key_'])
                ->order('sys_bh asc')
                ->select();
            if ($bool){
                foreach ($bool as $k=>$v){
                    $bool1 = db('dic_lzde')
                        ->field('sys_bh, rtrim(sys_mc) as sys_mc')
                        ->where('Parent',$v['Key_'])
                        ->order('sys_bh asc')
                        ->select();

                    if ($bool1){
                        $bool[$k]['bh_mc'] = $bool1;
                    }
                }
                $rows[$key]['bh_mc'] = $bool;
            }
        }
        $row['bh_mc'] = $rows;

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

    /**
     * 新增
     * @ApiMethod (POST)
     * @param string
     */
    public function add()
    {
        if(!$this->request->isPost()){
            $this->error('请求方式错误');
        }
        $req = $this->request->param();
        $arr = [
            'sys_id','sczl_gdbh','sczl_yjno',
            'sczl_gxmc','sczl_gxh','sczl_type',
            'sczl_rq','sczl_jtbh','sczl_bzdh',
            'sczl_ms','sczl_ls','sczl_cl','sczl_fp',
            'sczl_装版总工时','sczl_装版工时','sczl_保养工时','sczl_异常停机工时',
            'sczl_打样总工时','sczl_打样工时','sczl_设备运行工时',
            '拆片联拼系数','拆片条小盒系数','sczl_dedh','sczl_工价系数','sczl_计产系数',
            'sczl_bh1','sczl_bh2','sczl_bh3','sczl_bh4','sczl_bh5',
            'sczl_bh6','sczl_bh7','sczl_bh8','sczl_bh9','sczl_bh10',
            'sczl_rate1','sczl_rate2','sczl_rate3','sczl_rate4','sczl_rate5',
            'sczl_rate6','sczl_rate7','sczl_rate8','sczl_rate9','sczl_rate10'
        ];
        $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_sczl')->max('UniqId');
        $data['UniqId'] = $UniqId+1;

        //开启事务
        db()->startTrans();
        try{
            $sql = db('db_sczl')->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 edit()
    {
        if(!$this->request->isPost()){
            $this->error('请求方式错误');
        }
        $req = $this->request->param();
        $arr = [
            'UniqId','sczl_gdbh','sczl_yjno',
            'sczl_gxmc','sczl_gxh','sczl_type',
            'sczl_rq','sczl_jtbh','sczl_bzdh',
            'sczl_ms','sczl_ls','sczl_cl','sczl_fp',
            'sczl_装版总工时','sczl_装版工时','sczl_保养工时','sczl_异常停机工时',
            'sczl_打样总工时','sczl_打样工时','sczl_设备运行工时',
            '拆片联拼系数','拆片条小盒系数','sczl_dedh','sczl_工价系数','sczl_计产系数',
            'sczl_bh1','sczl_bh2','sczl_bh3','sczl_bh4','sczl_bh5',
            'sczl_bh6','sczl_bh7','sczl_bh8','sczl_bh9','sczl_bh10',
            'sczl_rate1','sczl_rate2','sczl_rate3','sczl_rate4','sczl_rate5',
            'sczl_rate6','sczl_rate7','sczl_rate8','sczl_rate9','sczl_rate10'
        ];
        $data = [];
        foreach ($arr as $key => $value){
            if (!(isset($req[$value]))){
                continue;
            }
            $data[$value] = $req[$value];
        }
        $data['mod_rq'] = date('Y-m-d H:i:s');

        //开启事务
        db()->startTrans();
        try{
            $sql = db('db_sczl')->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_sczl')->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);
        }

        $arr = [
            'sczl_gdbh'          =>['工单编号'],
            'Gd_cpmc'            =>['产品名称'],
            'jyno_gxmc'          =>['印件及工序'],
            'sczl_rq'            =>['生产日期'],
            'sczl_jtbh'          =>['机台'],
            'sczl_bzdh'          =>['班组'],
            'sczl_num'           =>['流程单'],
            'sczl_cl'            =>['车头产量'],
            'sczl_ms'            =>['色度数'],
            'sczl_zcfp'          =>['制程废'],
            'sczl_zccp'          =>['次品'],
            'sczl_来料异常'      =>['来料异常'],
            'sczl_装版工时'      =>['装版工时'],
            'sczl_保养工时'      =>['保养工时'],
            'sczl_打样工时'      =>['打样工时'],
            'sczl_异常停机工时'  =>['异常停机工时'],
            'sczl_设备运行工时'  =>['设备运行工时'],
            'sczl_bh1'           =>['组员编号1'],
            'sczl_bh1_name'      =>['组员姓名1'],
            'sczl_bh2'           =>['组员编号2'],
            'sczl_bh2_name'      =>['组员姓名2'],
            'sczl_bh3'           =>['组员编号3'],
            'sczl_bh3_name'      =>['组员姓名3'],
            'sczl_bh4'           =>['组员编号4'],
            'sczl_bh4_name'      =>['组员姓名4'],
            'sczl_bh5'           =>['组员编号5'],
            'sczl_bh5_name'      =>['组员姓名5'],
            'sczl_bh6'           =>['组员编号6'],
            'sczl_bh6_name'      =>['组员姓名6'],
            '拆片联拼系数'       =>['拆片联拼系数'],
            '拆片条小盒系数'     =>['拆片条小盒系数'],
            'sczl_工价系数'      =>['工价系数'],
            '日定额'             =>['日定额'],
            '千件工价'           =>['千件工价'],
            '补产标准'           =>['补产标准'],
        ];
        $data[0] = [];
        foreach ($req['fields'] as $k=>$v){
            if(array_key_exists($v,$arr)){
                array_push($data[0],$arr[$v][0]);
            }
        }

        // UNION 查询两表数据
        // db_sczl缺少数据 --> a.sczl_来料少数,
        // 设备_产量计酬缺少数据 --> a.拆片联拼系数, a.拆片条小盒系数,
        $sql = 'SELECT a.sczl_gdbh, a.sczl_yjno, a.sczl_gxh, a.sczl_type, LEFT(a.sczl_rq, 10) as sczl_rq, a.sczl_jtbh, 
                    a.sczl_bzdh, a.sczl_num, a.sczl_cl, a.sczl_ms, a.sczl_zcfp, a.sczl_zccp,
                    a.sczl_装版工时, a.sczl_保养工时, a.sczl_打样工时, a.sczl_异常停机工时, a.sczl_设备运行工时, 
                    a.sczl_bh1, a.sczl_bh2, a.sczl_bh3, a.sczl_bh4, a.sczl_bh5, a.sczl_bh6, a.sczl_工价系数, 
                    d.日定额, d.千件工价, d.补产标准, a.sczl_来料少数 as sczl_来料异常, NULL AS 拆片联拼系数, NULL AS 拆片条小盒系数, 
                    rtrim(a.sys_id) as sys_id, a.sys_rq, a.mod_rq, a.UniqId, "设备_产量计酬" AS table_type
                FROM `设备_产量计酬` `a` INNER JOIN `dic_lzde` `d` ON `a`.`sczl_dedh`=`d`.`sys_bh` ';
        $sql .= strpos($req['date'],'-') ? 'WHERE `a`.`sys_rq` LIKE "%'.$req['date'].'%" ' : 'WHERE `a`.`sczl_gdbh` = "'.$req['date'].'" ';
        $sql .= isset($req['sys_id']) && !empty($req['sys_id']) ? 'AND `a`.`sys_id` LIKE "%'.$req['sys_id'].'%" ' : '';
        $sql .= 'UNION ALL
                SELECT a.sczl_gdbh, a.sczl_yjno, a.sczl_gxh, a.sczl_type, LEFT(a.sczl_rq, 10) as sczl_rq, a.sczl_jtbh,
                    a.sczl_bzdh, a.sczl_num, a.sczl_cl, a.sczl_ms, a.sczl_fp as sczl_zcfp, a.sczl_cp as sczl_zccp,
                    a.sczl_装版工时, a.sczl_保养工时, a.sczl_打样工时, a.sczl_异常停机工时, a.sczl_设备运行工时,
                    a.sczl_bh1, a.sczl_bh2, a.sczl_bh3, a.sczl_bh4, a.sczl_bh5, a.sczl_bh6, a.sczl_工价系数, 
                    d.日定额, d.千件工价, d.补产标准, NULL AS sczl_来料异常, a.拆片联拼系数, a.拆片条小盒系数,
                    rtrim(a.sys_id) as sys_id, a.sys_rq, a.mod_rq, a.UniqId, "db_sczl" AS table_type
                FROM `db_sczl` `a` INNER JOIN `dic_lzde` `d` ON `a`.`sczl_dedh`=`d`.`sys_bh`';
        $sql .= strpos($req['date'],'-') ? 'WHERE `a`.`sys_rq` LIKE "%'.$req['date'].'%" ' : 'WHERE `a`.`sczl_gdbh` = "'.$req['date'].'" ';
        $sql .= isset($req['sys_id']) && !empty($req['sys_id']) ? 'AND `a`.`sys_id` LIKE "%'.$req['sys_id'].'%" ' : '';
        $sql .= 'ORDER BY sys_id ASC, sczl_num ASC';
        $rows = db()->query($sql);

        $gd = db('工单_基本资料')->column('Gd_gdbh, 成品名称 as Gd_cpmc');
        $rs = db('人事_基本资料')->column('员工编号, 员工姓名');
        foreach ($rows as $key=>$value){
            $subArray = [];
            foreach ($arr as $k=>$v){
                if($k=='sczl_来料异常' || $k=='拆片联拼系数' || $k=='拆片条小盒系数'){
                    $subArray[$k] = $value[$k] == null ? '' : floatval($value[$k]);
                }else if($k=='Gd_cpmc'){
                    if(trim($value['sczl_gdbh']) != '' && array_key_exists($value['sczl_gdbh'],$gd)){
                        $subArray[$k] = trim($gd[$value['sczl_gdbh']]);
                    }else{
                        $subArray[$k] = '';
                    }
                }else if($k=='sczl_bh1_name' || $k=='sczl_bh2_name' || $k=='sczl_bh3_name' || $k=='sczl_bh4_name' || $k=='sczl_bh5_name' || $k=='sczl_bh6_name'){
                    if(trim($value[substr($k,0,8)]) != '' && array_key_exists($value[substr($k,0,8)],$rs)){
                        $subArray[$k] = trim($rs[$value[substr($k,0,8)]]);
                    }else{
                        $subArray[$k] = '';
                    }
                }else if($k=='sczl_num' || $k=='sczl_cl' || $k=='sczl_zcfp' || $k=='sczl_zccp' || $k=='sczl_装版工时' || $k=='sczl_保养工时' || $k=='sczl_打样工时' ||
                    $k=='sczl_异常停机工时' || $k=='sczl_设备运行工时' || $k=='拆片联拼系数' || $k=='拆片条小盒系数' || $k=='sczl_工价系数' || $k=='千件工价'){
                    $subArray[$k] = $value[$k] == 0 ? '' : floatval($value[$k]);
                }else if($k=='sczl_ms'){
                    $subArray[$k] = $value[$k] == 0 ? 1 : floatval($value[$k]);
                }else if($k=='jyno_gxmc'){
                    $subArray['jyno_gxmc'] = sprintf("%02d", $value['sczl_yjno']).'-'.sprintf("%02d", $value['sczl_gxh']).'-->'.trim($value['sczl_type']);
                }else{
                    $subArray[$k] = $rows[$key][$k];
                }
            }
            array_push($data,$subArray);
        }

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

}