<?php

namespace app\api\controller;

use app\common\controller\Api;
use Overtrue\Pinyin\Pinyin;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

/**
 * 包装计件单据维护接口
 */
class PackagingCountDocument extends Api
{
    protected $noNeedLogin = ['*'];
    protected $noNeedRight = ['*'];

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

    /**
     * 获取包装计件单据侧边栏
     * @ApiMethod (GET)
     */
    public function getTab()
    {
        //get请求
        if(!$this->request->isGet()){
            $this->error('请求方式错误');
        }
        //判断是否存在缓存
        if (!cache('PackagingCountDocument-getTab')){
            $rows = db()->table('db_包装计件')
                ->field('LEFT(sys_rq, 10) as date, COUNT(*) as counts')
                ->group('date')
                ->order('UniqId desc')
                ->limit(30)
                ->select();

            $arr = db()->table('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')
                ->select();
            //设置缓存
            cache('PackagingCountDocument-getTab',['rows'=>$rows,'arr'=>$arr],3600);
        }else{
            $rows = cache('PackagingCountDocument-getTab')['rows'];
            $arr = cache('PackagingCountDocument-getTab')['arr'];
        }

        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]);
                }
            }
            $rows[$key]['date'] = str_replace('-', '.', $rows[$key]['date']);
        }
        $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();

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

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

        $rows = db()->table('db_包装计件')
            ->field('sczl_bh, LEFT(sczl_rq, 10) as sczl_rq, sczl_bzdh, 
            sczl_cl1 + sczl_cl2 + sczl_cl3 + sczl_cl4 + sczl_cl5 + sczl_cl6 as sczl_cl, 
            sczl_返工产量1 + sczl_返工产量2 + sczl_返工产量3 + sczl_返工产量4 + sczl_返工产量5 + sczl_返工产量6 as sczl_fgsl, 
            sczl_cl1 + sczl_cl2 + sczl_cl3 + sczl_cl4 + sczl_cl5 + sczl_cl6 + sczl_返工产量1 + sczl_返工产量2 + sczl_返工产量3 + sczl_返工产量4 + sczl_返工产量5 + sczl_返工产量6 as sczl_jjcl, 
            sczl_gdbh1, rtrim(sys_id) as sys_id, sys_rq, mod_rq, UniqId')
            ->where($where)
            ->page($page,$limit)
            ->select();
        $total = db()->table('db_包装计件')->where($where)->count();
        $rs = db()->table('人事_基本资料')->column('员工编号, 员工姓名');
        foreach ($rows as $key=>$value) {
            $rows[$key]['sczl_cl'] = floatval($value['sczl_cl']);
            $rows[$key]['sczl_fgsl'] = floatval($value['sczl_fgsl']);
            $rows[$key]['sczl_jjcl'] = floatval($value['sczl_jjcl']);
            $rows[$key]['mod_rq'] = $value['mod_rq']=='1900-01-01 00:00:00' ? '' :$value['mod_rq'];
            $rows[$key]['name'] = array_key_exists($value['sczl_bh'],$rs) ? trim($rs[$value['sczl_bh']]) : '';
        }

        $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'])){
            $where = [
                'sczl_gdbh1|sczl_gdbh2|sczl_gdbh3|sczl_gdbh4|sczl_gdbh5|sczl_gdbh6'=>[ 'like', '%' . $req['gdbh'] . '%']
            ];
            $rows = db()->table('db_包装计件')
                ->field('sczl_bh, LEFT(sczl_rq, 10) as sczl_rq, sczl_bzdh, 
            sczl_cl1 + sczl_cl2 + sczl_cl3 + sczl_cl4 + sczl_cl5 + sczl_cl6 as sczl_cl, 
            sczl_返工产量1 + sczl_返工产量2 + sczl_返工产量3 + sczl_返工产量4 + sczl_返工产量5 + sczl_返工产量6 as sczl_fgsl, 
            sczl_cl1 + sczl_cl2 + sczl_cl3 + sczl_cl4 + sczl_cl5 + sczl_cl6 + sczl_返工产量1 + sczl_返工产量2 + sczl_返工产量3 + sczl_返工产量4 + sczl_返工产量5 + sczl_返工产量6 as sczl_jjcl, 
            sczl_gdbh1, rtrim(sys_id) as sys_id, sys_rq, mod_rq, UniqId')
                ->where($where)
                ->page($page,$limit)
                ->select();
            $total = db()->table('db_包装计件')->where($where)->count();
        }else{
            if (isset($req['cpmc']) && !empty($req['cpmc'])){
                //查询工单表
                $gd = db()->table('工单_基本资料')
                    ->where('Gd_cpmc', 'LIKE', '%'.$req['cpmc'].'%')
                    ->column('Gd_gdbh');
                $where = [
                    'sczl_gdbh1|sczl_gdbh2|sczl_gdbh3|sczl_gdbh4|sczl_gdbh5|sczl_gdbh6'=>['in', $gd]
                ];
                $rows = db()->table('db_包装计件')
                    ->field('sczl_bh, LEFT(sczl_rq, 10) as sczl_rq, sczl_bzdh, 
            sczl_cl1 + sczl_cl2 + sczl_cl3 + sczl_cl4 + sczl_cl5 + sczl_cl6 as sczl_cl, 
            sczl_返工产量1 + sczl_返工产量2 + sczl_返工产量3 + sczl_返工产量4 + sczl_返工产量5 + sczl_返工产量6 as sczl_fgsl, 
            sczl_cl1 + sczl_cl2 + sczl_cl3 + sczl_cl4 + sczl_cl5 + sczl_cl6 + sczl_返工产量1 + sczl_返工产量2 + sczl_返工产量3 + sczl_返工产量4 + sczl_返工产量5 + sczl_返工产量6 as sczl_jjcl, 
            sczl_gdbh1, rtrim(sys_id) as sys_id, sys_rq, mod_rq, UniqId')
                    ->where($where)
                    ->page($page,$limit)
                    ->select();
                $total = db()->table('db_包装计件')->where($where)->count();
            }else{
                $this->error('参数错误');
            }
        }
        $rs = db()->table('人事_基本资料')->column('员工编号, 员工姓名');
        foreach ($rows as $key=>$value){
            $rows[$key]['mod_rq'] = $value['mod_rq']=='1900-01-01 00:00:00' ? '' :$value['mod_rq'];
            $rows[$key]['sczl_cl'] = floatval($value['sczl_cl']);
            $rows[$key]['sczl_fgsl'] = floatval($value['sczl_fgsl']);
            $rows[$key]['sczl_jjcl'] = floatval($value['sczl_jjcl']);
            $rows[$key]['name'] = array_key_exists($value['sczl_bh'],$rs) ? trim($rs[$value['sczl_bh']]) : '';
        }
        $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('参数错误');
        }
        $gd = db()->table('工单_基本资料')->column('Gd_gdbh, Gd_cpmc');
        $rows = db()->table('db_包装计件')
            ->field('LEFT(sczl_rq, 10) as sczl_rq, sczl_bh, sczl_bzdh, sczl_jsss, sczl_冲定额, 
            sczl_gdbh1, sczl_gdbh2, sczl_gdbh3, sczl_gdbh4, sczl_gdbh5, sczl_gdbh6, 
            rtrim(sczl_yjGx1) as sczl_yjGx1, rtrim(sczl_yjGx2) as sczl_yjGx2, rtrim(sczl_yjGx3) as sczl_yjGx3, rtrim(sczl_yjGx4) as sczl_yjGx4, rtrim(sczl_yjGx5) as sczl_yjGx5, rtrim(sczl_yjGx6) as sczl_yjGx6, 
            rtrim(sczl_gxmc1) as sczl_gxmc1, rtrim(sczl_gxmc2) as sczl_gxmc2, rtrim(sczl_gxmc3) as sczl_gxmc3, rtrim(sczl_gxmc4) as sczl_gxmc4, rtrim(sczl_gxmc5) as sczl_gxmc5, rtrim(sczl_gxmc6) as sczl_gxmc6, 
            sczl_cl1, sczl_cl2, sczl_cl3, sczl_cl4, sczl_cl5, sczl_cl6, 
            sczl_PgCl1, sczl_PgCl2, sczl_PgCl3, sczl_PgCl4, sczl_PgCl5, sczl_PgCl6, 
            sczl_返工产量1, sczl_返工产量2, sczl_返工产量3, sczl_返工产量4, sczl_返工产量5, sczl_返工产量6, 
            sczl_计产系数1, sczl_计产系数2, sczl_计产系数3, sczl_计产系数4, sczl_计产系数5, sczl_计产系数6, 
            rtrim(sczl_Jtbh1) as sczl_Jtbh1, rtrim(sczl_Jtbh2) as sczl_Jtbh2, rtrim(sczl_Jtbh3) as sczl_Jtbh3, rtrim(sczl_Jtbh4) as sczl_Jtbh4, rtrim(sczl_Jtbh5) as sczl_Jtbh5, rtrim(sczl_Jtbh6) as sczl_Jtbh6, 
            rtrim(sczl_dedh1) as sczl_dedh1, rtrim(sczl_dedh2) as sczl_dedh2, rtrim(sczl_dedh3) as sczl_dedh3, rtrim(sczl_dedh4) as sczl_dedh4, rtrim(sczl_dedh5) as sczl_dedh5, rtrim(sczl_dedh6) as sczl_dedh6, 
            rtrim(sczl_desc) as sczl_desc')
            ->where('UniqId',$UniqId)
            ->find();
        $rs = db()->table('人事_基本资料')->field('员工姓名')->where('员工编号',$rows['sczl_bh'])->find();
        $rows['name'] = $rs ? trim($rs['员工姓名']) : '';
        for ($i=1;$i<=6;$i++){
            $rows['Gd_cpmc'.$i] = array_key_exists($rows['sczl_gdbh'.$i], $gd) ? trim($gd[$rows['sczl_gdbh'.$i]]) : '';
        }

        $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()->table('工单_印件资料')->alias('g')
            ->field('rtrim(g.yj_yjmc) as Gd_cpmc, g.yj_Yjno as Gy0_yjno, c.Gy0_gxh, rtrim(c.Gy0_gxmc) as Gy0_gxmc')
            ->where(['g.Yj_Gdbh'=>$gdbh])
            ->where(['c.Gy0_gxmc'=>$gxmcs])
            ->join(['工单_工艺资料'=>'c'],'c.Gy0_gdbh=g.Yj_Gdbh and g.yj_Yjno=c.Gy0_yjno')
            ->select();

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

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

    /**
     * 获取单个汉字的首字母
     * @param string $chineseChar 单个汉字
     * @return string|null 首字母（大写）或 null（如果不是有效的汉字）
     */
    function getChineseCharInitial($chineseChar) {
        // 使用正则表达式匹配合法的汉字字符
        if (!preg_match('/^[\x{4e00}-\x{9fa5}]$/u', $chineseChar)) {
            return null;
        }
        // 将输入的汉字转换为 GBK 编码
        $gbk = iconv('UTF-8', 'GBK', $chineseChar);
        // 计算汉字的拼音首字母
        $initial = '';
        // 获取首字母的 ASCII 值
        $asc = ord($gbk[0]) * 256 + ord($gbk[1]);
        // 汉字范围的对应拼音首字母范围
        $zhFirstLetterRanges = array(
            array(45217, 45252,'A'), // A
            array(45253, 45760,'B'), // B
            array(45761, 46317,'C'), // C
            array(46318, 46825,'D'), // D
            array(46826, 47009,'E'), // E
            array(47010, 47296,'F'), // F
            array(47297, 47613,'G'), // G
            array(47614, 48118,'H'), // H
            array(48119, 49061,'J'), // J
            array(49062, 49323,'K'), // K
            array(49324, 49895,'L'), // L
            array(49896, 50370,'M'), // M
            array(50371, 50613,'N'), // N
            array(50614, 50621,'O'), // O
            array(50622, 50905,'P'), // P
            array(50906, 51386,'Q'), // Q
            array(51387, 51445,'R'), // R
            array(51446, 52217,'S'), // S
            array(52218, 52697,'T'), // T
            array(52698, 52979,'W'), // W
            array(52980, 53688,'X'), // X
            array(53689, 54480,'Y'), // Y
            array(54481, 55289,'Z')  // Z
        );
        // 判断汉字的拼音首字母范围并返回对应的字母
        foreach ($zhFirstLetterRanges as $key => $range) {
            if ($asc >= $range[0] && $asc <= $range[1]) {
                $initial = $range[2];
                break;
            }
        }
    
        return $initial;
    }

    /**
     * 查询员工名称
     * @ApiMethod (GET)
     * @param string $sczl_bh 员工编号
     */
    public function getYg()
    {
        //get请求
        if(!$this->request->isGet()){
            $this->error('请求方式错误');
        }
        $req = $this->request->param();
        if (isset($req['sczl_bh']) && !empty($req['sczl_bh'])){
            $sczl_bh = $req['sczl_bh'];
        }else{
            $this->error('参数错误');
        }
        // $rows = db()->table('人事_基本资料')->field('员工编号, rtrim(员工姓名) as ygxm')->where(['在职状态'=>'在职'])->select();
        // $data = [];
        // $pinyin = new Pinyin();
        // foreach ($rows as $v){
        //     $sx = '';
        //     for($i=0; $i<mb_strlen($v['ygxm']); $i++){
        //         $str = mb_substr($v['ygxm'],$i,1);
        //         $bool = $this->getChineseCharInitial($str);
        //         if($bool){
        //             $sx .= $this->getChineseCharInitial($str);
        //         }else{
        //             $sx .= strtoupper($pinyin->abbr($str));
        //         }
        //     }
        //     $data[$sx][] = $v;
        // }
        // $sx = strtoupper($sczl_bh);
        // if(array_key_exists($sx,$data)){
        //     $this->success('成功',$data[$sx]);
        // }

        $rs = db()->table('人事_基本资料')->field('员工编号, rtrim(员工姓名) as ygxm')->where(['pycode'=>$sczl_bh,'在职状态'=>'在职'])->select();
        if($rs){
            $this->success('成功',$rs);
        }
        $rs = db()->table('人事_基本资料')->field('员工编号, rtrim(员工姓名) as ygxm')->where(['员工编号'=>$sczl_bh,'在职状态'=>'在职'])->select();
        if($rs===false){
            $this->error('员工编号错误');
        }
        $this->success('成功',$rs);
    }

/**
     * 查询定额代号
     * @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','in',['包装','成品防护'])
            ->select();
        foreach ($row as $n=>$m){
            $rows = db('dic_lzde')
                ->field('Key_, sys_bh, rtrim(sys_mc) as sys_mc')
                ->where('Parent',$m['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[$n]['bh_mc'] = $rows;
        }
        $this->success('成功',$row);
    }
    
    /**
     * 新增
     * @ApiMethod (POST)
     * @param string 'sys_id'
     */
    public function add()
    {
        if(!$this->request->isPost()){
            $this->error('请求方式错误');
        }
        $req = $this->request->param();
        $arr = [
            'sys_id','sczl_rq', 'sczl_bh', 'sczl_jsss', 'sczl_冲定额', 'sczl_bzdh',
            'sczl_gdbh1', 'sczl_gdbh2', 'sczl_gdbh3', 'sczl_gdbh4', 'sczl_gdbh5', 'sczl_gdbh6',
            'sczl_yjGx1', 'sczl_yjGx2', 'sczl_yjGx3', 'sczl_yjGx4', 'sczl_yjGx5', 'sczl_yjGx6',
            'sczl_gxmc1', 'sczl_gxmc2', 'sczl_gxmc3', 'sczl_gxmc4', 'sczl_gxmc5', 'sczl_gxmc6',
            'sczl_cl1', 'sczl_cl2', 'sczl_cl3', 'sczl_cl4', 'sczl_cl5', 'sczl_cl6',
            'sczl_PgCl1', 'sczl_PgCl2', 'sczl_PgCl3', 'sczl_PgCl4', 'sczl_PgCl5', 'sczl_PgCl6',
            'sczl_返工产量1', 'sczl_返工产量2', 'sczl_返工产量3', 'sczl_返工产量4', 'sczl_返工产量5', 'sczl_返工产量6',
            'sczl_计产系数1', 'sczl_计产系数2', 'sczl_计产系数3', 'sczl_计产系数4', 'sczl_计产系数5', 'sczl_计产系数6',
            'sczl_Jtbh1','sczl_Jtbh2', 'sczl_Jtbh3', 'sczl_Jtbh4', 'sczl_Jtbh5', 'sczl_Jtbh6',
            'sczl_dedh1', 'sczl_dedh2', 'sczl_dedh3', 'sczl_dedh4', 'sczl_dedh5', 'sczl_dedh6',
            '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()->table('db_包装计件')->max('UniqId');
        $data['UniqId'] = $UniqId < 10000000 ? 10000000 : $UniqId + 1;

        //开启事务
        db()->startTrans();
        try{
            $sql = db()->table('db_包装计件')->fetchSql(true)->insert($data);
            $bool = db()->query($sql);
            // 提交事务
            db()->commit();
            cache('PackagingCountDocument-getTab',null);
        } 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 = [
            'sys_id','sczl_rq', 'sczl_bh', 'sczl_jsss', 'sczl_冲定额', 'sczl_bzdh',
            'sczl_gdbh1', 'sczl_gdbh2', 'sczl_gdbh3', 'sczl_gdbh4', 'sczl_gdbh5', 'sczl_gdbh6',
            'sczl_yjGx1', 'sczl_yjGx2', 'sczl_yjGx3', 'sczl_yjGx4', 'sczl_yjGx5', 'sczl_yjGx6',
            'sczl_gxmc1', 'sczl_gxmc2', 'sczl_gxmc3', 'sczl_gxmc4', 'sczl_gxmc5', 'sczl_gxmc6',
            'sczl_cl1', 'sczl_cl2', 'sczl_cl3', 'sczl_cl4', 'sczl_cl5', 'sczl_cl6',
            'sczl_PgCl1', 'sczl_PgCl2', 'sczl_PgCl3', 'sczl_PgCl4', 'sczl_PgCl5', 'sczl_PgCl6',
            'sczl_返工产量1', 'sczl_返工产量2', 'sczl_返工产量3', 'sczl_返工产量4', 'sczl_返工产量5', 'sczl_返工产量6',
            'sczl_计产系数1', 'sczl_计产系数2', 'sczl_计产系数3', 'sczl_计产系数4', 'sczl_计产系数5', 'sczl_计产系数6',
            'sczl_Jtbh1','sczl_Jtbh2', 'sczl_Jtbh3', 'sczl_Jtbh4', 'sczl_Jtbh5', 'sczl_Jtbh6',
            'sczl_dedh1', 'sczl_dedh2', 'sczl_dedh3', 'sczl_dedh4', 'sczl_dedh5', 'sczl_dedh6',
            '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);
        }
        if (count($data)==0){
            $this->error('参数错误','',111);
        }
        $data['mod_rq'] = date('Y-m-d H:i:s');

        //开启事务
        db()->startTrans();
        try{
            $sql = db()->table('db_包装计件')->where('UniqId',$req['UniqId'])->fetchSql(true)->update($data);
            $bool = db()->query($sql);
            // 提交事务
            db()->commit();
            cache('PackagingCountDocument-getTab',null);
        } 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()->table('db_包装计件')->where('UniqId',$req['UniqId'])->delete();
            // 提交事务
            db()->commit();
            cache('PackagingCountDocument-getTab',null);
        } catch (\Exception $e) {
            // 回滚事务
            db()->rollback();
            $this->error($e->getMessage());
        }

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

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

    }

    /**
     * 导出到excel
     * @ApiMethod (POST)
     * @param string gxmc
     * @param string start_time
     * @param string end_time
     * @param string file_name
     * @param array fields
     */
    public function processExport(){
        if(!$this->request->isPost()){
            $this->error('请求方式错误');
        }
        $req = $this->request->param();
        $arr = [
            'gxmc','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_rq'       =>['日期','LEFT(sczl_rq, 10) as sczl_rq'],
            'sczl_bh'       =>['员工编号','sczl_bh'],
            'sczl_name'     =>['员工姓名','sczl_bh'],
            'sczl_jsss'     =>['计时时数','sczl_jsss'],
            'sczl_gdbh'     =>['工单编号','sczl_gdbh1, sczl_gdbh2, sczl_gdbh3, sczl_gdbh4, sczl_gdbh5, sczl_gdbh6'],
            'sczl_yjmc'     =>['印件名称','sczl_gdbh1, sczl_gdbh2, sczl_gdbh3, sczl_gdbh4, sczl_gdbh5, sczl_gdbh6'],
            'sczl_yjGx'     =>['印件工序','rtrim(sczl_yjGx1) as sczl_yjGx1, rtrim(sczl_yjGx2) as sczl_yjGx2, rtrim(sczl_yjGx3) as sczl_yjGx3, rtrim(sczl_yjGx4) as sczl_yjGx4, rtrim(sczl_yjGx5) as sczl_yjGx5, rtrim(sczl_yjGx6) as sczl_yjGx6'],
            'sczl_gxmc'     =>['工序名称','rtrim(sczl_gxmc1) as sczl_gxmc1, rtrim(sczl_gxmc2) as sczl_gxmc2, rtrim(sczl_gxmc3) as sczl_gxmc3, rtrim(sczl_gxmc4) as sczl_gxmc4, rtrim(sczl_gxmc5) as sczl_gxmc5, rtrim(sczl_gxmc6) as sczl_gxmc6'],
            'sczl_cl'       =>['产量','sczl_cl1, sczl_cl2, sczl_cl3, sczl_cl4, sczl_cl5, sczl_cl6'],
            'sczl_PgCl'     =>['每箱产量','sczl_PgCl1, sczl_PgCl2, sczl_PgCl3, sczl_PgCl4, sczl_PgCl5, sczl_PgCl6'],
            'sczl_计产系数'  =>['计产系数','sczl_计产系数1, sczl_计产系数2, sczl_计产系数3, sczl_计产系数4, sczl_计产系数5, sczl_计产系数6'],
            'sczl_dedh'     =>['定额代号','rtrim(sczl_dedh1) as sczl_dedh1, rtrim(sczl_dedh2) as sczl_dedh2, rtrim(sczl_dedh3) as sczl_dedh3, rtrim(sczl_dedh4) as sczl_dedh4, rtrim(sczl_dedh5) as sczl_dedh5, rtrim(sczl_dedh6) as sczl_dedh6']
        ];
        $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()->table('db_包装计件')
            ->field($field)
            ->where('sczl_rq','between',[$req['start_time'],$req['end_time']])
            ->where(['sczl_gxmc1|sczl_gxmc2|sczl_gxmc3|sczl_gxmc4|sczl_gxmc5|sczl_gxmc6'=>['like','%'.$req['gxmc'].'%']])
            ->select();
        $gd = db()->table('工单_基本资料')->column('Gd_gdbh, Gd_cpmc');
        $rs = db()->table('人事_基本资料')->column('员工编号, 员工姓名');

        for ($i = 1; $i <= 6; $i++) {
            foreach ($rows as $key=>$value){
                if (trim($value['sczl_gdbh'.$i])=='') continue;
                if (trim($value['sczl_gxmc'.$i])!=$req['gxmc']) continue;
                $subArray = [];
                foreach ($req['fields'] as $k=>$v){
                    if(array_key_exists($v,$arr)){
                        if($v=='sczl_rq' || $v=='sczl_jsss' || $v=='sczl_bh'){
                            if($value[$v]){
                                array_push($subArray,$value[$v]);
                            }else{
                                array_push($subArray,'');
                            }
                        }else if($v=='sczl_name'){
                            $name = array_key_exists($value['sczl_bh'],$rs) ? trim($rs[$value['sczl_bh']]) : '';
                            if($name){
                                array_push($subArray,$name);
                            }else{
                                array_push($subArray,'');
                            }
                        }else if($v=='sczl_yjmc'){
                            $cpmc = array_key_exists($value['sczl_gdbh'.$i],$gd) ? trim($gd[$value['sczl_gdbh'.$i]]) : '';
                            if($cpmc){
                                array_push($subArray,$cpmc);
                            }else{
                                array_push($subArray,'');
                            }
                        }else{
                            if($value[$v.$i]){
                                array_push($subArray,$value[$v.$i]);
                            }else{
                                array_push($subArray,'');
                            }
                        }
                    }
                }
                array_push($data,$subArray);
            }
        }
        $this->Ex($req['file_name'],$data);

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

    /**
     * 工序汇总导出
     * @ApiMethod (POST)
     * @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'])){
            $where['sys_rq'] = ['LIKE',$req['date'].'%'];
        }else{
            $this->error('参数错误');
        }
        if (isset($req['sys_id']) && !empty($req['sys_id'])) $where['sys_id'] = ['LIKE',$req['sys_id'].'%'];
        if (!(isset($req['fields']) && count($req['fields'])!=0)){
            $this->error('参数错误','fields',100);
        }

        $field = '';
        $arr = [
            'sczl_bh'       =>['员工编号','sczl_bh'],
            'sczl_name'     =>['员工姓名','sczl_bh'],
            'sczl_rq'       =>['生产日期','LEFT(sczl_rq, 10) as sczl_rq'],
            'sczl_bzdh'     =>['班组','sczl_bzdh'],
            'sczl_cl'       =>['包装产量','sczl_cl1 + sczl_cl2 + sczl_cl3 + sczl_cl4 + sczl_cl5 + sczl_cl6 as sczl_cl'],
            'sczl_fgcl'     =>['返工产量','sczl_返工产量1 + sczl_返工产量2 + sczl_返工产量3 + sczl_返工产量4 + sczl_返工产量5 + sczl_返工产量6 as sczl_fgcl'],
            'sczl_jjcl'     =>['计件产量','sczl_cl1 + sczl_cl2 + sczl_cl3 + sczl_cl4 + sczl_cl5 + sczl_cl6 + sczl_返工产量1 + sczl_返工产量2 + sczl_返工产量3 + sczl_返工产量4 + sczl_返工产量5 + sczl_返工产量6 as sczl_jjcl'],
            'sczl_gdbh1'     =>['相关工单','sczl_gdbh1'],
        ];
        $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()->table('db_包装计件')
            ->field($field)
            ->where($where)
            ->select();

        $rs = db()->table('人事_基本资料')->column('员工编号, 员工姓名');
        foreach ($rows as $key=>$value) {
            if (isset($value['sczl_cl'])) $value['sczl_cl'] = floatval($value['sczl_cl']);
            if (isset($value['sczl_fgcl'])) $value['sczl_fgcl'] = floatval($value['sczl_fgcl']);
            if (isset($value['sczl_jjcl'])) $value['sczl_jjcl'] = floatval($value['sczl_jjcl']);
            $subArray = [];
            foreach ($req['fields'] as $k=>$v){
                if(array_key_exists($v,$arr)){
                    if($v=='sczl_name'){
                        $name = array_key_exists($value['sczl_bh'],$rs) ? trim($rs[$value['sczl_bh']]) : '';
                        if($name){
                            array_push($subArray,$name);
                        }else{
                            array_push($subArray,'');
                        }
                    }else{
                        if($value[$v]){
                            array_push($subArray,$value[$v]);
                        }else{
                            array_push($subArray,'');
                        }
                    }
                }
            }
            array_push($data,$subArray);
        }
        $this->Ex($req['file_name'],$data);
//        $this->success('成功',['file_name'=>$req['file_name'],'data'=>$data]);
    }

    public function Ex($file_name,$data)
    {
        $spreadsheet = new Spreadsheet();

        $sheet = $spreadsheet->getActiveSheet();

        $sheet->setTitle($file_name);

        // 将数据逐行写入工作表
        foreach ($data as $rowIndex => $rowData) {
            // 将每个单元格的值写入对应的列
            foreach ($rowData as $columnIndex => $cellData) {
                $sheet->setCellValueByColumnAndRow($columnIndex + 1, $rowIndex + 1, $cellData);
            }
        }
        $writer = new Xlsx($spreadsheet);
        $folderPath = 'uploads/folder';

        if (!is_dir($folderPath)) {
            mkdir($folderPath, 0777, true);
            $tempPath = $folderPath .'/'. $file_name.'.xlsx';
        } else {
            $tempPath = $folderPath .'/'. $file_name.'.xlsx';
        }

//        header('Content-Disposition: attachment;filename="'.$file_name.'.xlsx"');
//        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
//        $writer->save("php://output"); //表示在$path路径下面生成demo.xlsx文件
        $writer->save($tempPath);

        // 获取完整url
        $protocol = isset($_SERVER['HTTPS']) && $_SERVER['HTTPS'] === 'on' ? 'https' : 'http';
        $host = $_SERVER['HTTP_HOST'];
        $siteUrl = $protocol . '://' . $host .'/'.$tempPath ;

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

}