vocs.com/application/admin/controller/Res.php

<?php

namespace app\admin\controller;

use app\common\controller\Backend;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use \think\Db;
use \think\Session;
/**
 * 检测结果管理管理
 *
 * @icon fa fa-circle-o
 */
class Res extends Backend
{

    /**
     * Res模型对象
     * @var \app\admin\model\Res
     */
    protected $model = null;
    protected $searchFields = 'entrust_no,name,bach,sell_bach,standard_name,sample_info,send_sample';
    public function _initialize()
    {
        parent::_initialize();
        $this->model = new \app\admin\model\Res;
        $this->view->assign("statusList", $this->model->getStatusList());
    }



    /**
     * 默认生成的控制器所继承的父类中有index/add/edit/del/multi五个基础方法、destroy/restore/recyclebin三个回收站方法
     * 因此在当前控制器中可不用编写增删改查的代码,除非需要自己控制这部分逻辑
     * 需要将application/admin/library/traits/Backend.php中对应的方法复制到当前控制器,然后进行修改
     */
    /**
     * 查看
     *
     * @return string|Json
     * @throws \think\Exception
     * @throws DbException
     */
    public function index()
    {
        //设置过滤方法
        $this->request->filter(['strip_tags', 'trim']);
        if (false === $this->request->isAjax()) {
            return $this->view->fetch();
        }
        //如果发送的来源是 Selectpage，则转发到 Selectpage
        if ($this->request->request('keyField')) {
            return $this->selectpage();
        }
        [$where, $sort, $order, $offset, $limit] = $this->buildparams();
        $user_id = Session::get('admin')['id'];
        if ($user_id == 1){//超级管理员
            $list = $this->model
                ->where($where)
                ->whereNull('delete_time')
                ->order($sort, $order)
                ->paginate($limit);
        }else{
            $userinfo = Db::name('admin')->where('id',$user_id)->find();
            $pidList = Db::name('company')->where('pid',$userinfo['company'])->select();

            $map = [];
            if (!empty($pidList)){//总公司
                $pid = [];
                foreach ($pidList as $key=>$value){
                    $pid[$key] = $value['id'];
                }
                $map['work_unit'] = array('in',$pid);
            }else{//分公司
                $map['work_unit'] = $userinfo['company'];
            }

            $list = $this->model
                ->where($where)
                ->where($map)
                ->whereNull('delete_time')
                ->order($sort, $order)
                ->paginate($limit);
        }
        $result = ['total' => $list->total(), 'rows' => $list->items()];
        return json($result);
    }

    /**
     * 所属公司搜索
     */
    public function company()
    {
        $company = Db::name('company')->select();
        $data = [];
        for ($i=0;$i<count($company);$i++){
            $data[$i]['id'] = $company[$i]['name'];
            $data[$i]['name'] = $company[$i]['name'];
        }
        return json(['list'=>$data,'total'=>count($data)]);
    }

    //检测数据编辑
    public function edit($ids = null)
    {
        $row = $this->model->get($ids);
        if ($row['judge'] == 0){
            $row['judge'] = '不合格';
        }else{
            $row['judge'] = '合格';
        }
        if (!$row) {
            $this->error(__('No Results were found'));
        }
        $adminIds = $this->getDataLimitAdminIds();
        if (is_array($adminIds) && !in_array($row[$this->dataLimitField], $adminIds)) {
            $this->error(__('You have no permission'));
        }
        if (false === $this->request->isPost()) {
            $this->view->assign('row', $row);
            return $this->view->fetch();
        }
        $params = $this->request->post('row/a');
        if (empty($params)) {
            $this->error(__('Parameter %s can not be empty', ''));
        }
        $params = $this->preExcludeFields($params);
        if ($params['judge'] == '合格'){
            $params['judge'] = '1';
        }else{
            $params['judge'] = '0';
        }
        $result = false;
        Db::startTrans();
        try {
            //是否采用模型验证
            if ($this->modelValidate) {
                $name = str_replace("\\model\\", "\\validate\\", get_class($this->model));
                $validate = is_bool($this->modelValidate) ? ($this->modelSceneValidate ? $name . '.edit' : $name) : $this->modelValidate;
                $row->validateFailException()->validate($validate);
            }
            $result = $row->allowField(true)->save($params);
            Db::commit();
        } catch (ValidateException|PDOException|Exception $e) {
            Db::rollback();
            $this->error($e->getMessage());
        }
        if (false === $result) {
            $this->error(__('No rows were updated'));
        }
        $this->success();
    }

    //检测数据查看
    public function data(){
        $params = input('id');
        if (empty($params)){
            $this->error('参数错误');
        }
        $entrust_id = Db::name('res')->where('id',$params)->find();
        $gather_id = Db::name('entrust')->where('id',$entrust_id['entrust_id'])->find();
        $this->view->assign('row',$entrust_id);
        if (empty($gather_id['gather_id'])){
            $this->error('此委托单还未提交检测,暂无检测数据');
        }
        //此处根据实际获取到的采集表的数据 gather_tab  去对应表里查数据  重新写一个gc表格页面  js加代码
        if ($gather_id['gather_tab'] == 'gather_txt_check_gcms'){
            $gather = Db::name('gather_txt_gcms')->where('id',$gather_id['gather_id'])->find();
            $data = Db::name('gather_txt_check_gcms')->where('pid',$gather_id['gather_id'])->select();
            $this->view->assign('gather', $gather);
            $this->view->assign('data', $data);
            $this->view->assign('id', $params);
            return $this->view->fetch();
        }else if ($gather_id['gather_tab'] == 'gather_txt_check_gc'){
            $gather = Db::name('gather_txt_gc')->where('id',$gather_id['gather_id'])->find();
            $data = Db::name('gather_txt_check_gc')->where('pid',$gather_id['gather_id'])->select();
            $this->view->assign('gather', $gather);
            $this->view->assign('data', $data);
            $this->view->assign('id', $params);
            return $this->fetch('datagc');
        }

    }
    //批量审核
    public function couse(){
        $id = input('ids');
        $this->view->assign('id',$id);
        $row = input('row/a');
        return $this->fetch();
    }
    public function couses(){
        if ($this->request->isPost()){
            $id = input('id');
            $status = input('status');
            $couse = input('couse');
            $ids = explode(',',$id);
            for ($i=0;$i<count($ids);$i++){
                Db::name('res')->where('id',$ids[$i])->update(['status'=>$status,'couse'=>$couse]);
            }
            return $this->success();
        }else{
            return $this->error('参数错误');
        }
    }

    /**
     * 搜索栏检测项目下拉列表
     */
    public function companyselect(){
        $company = Db::name('item_judge')->where('delete',null)->order('weigh asc')->column('name,name');
        if (empty($company)){
            return $this->error('没有检测项目数据');
        }else{
            return json($company);
        }

    }


    //数据报告导出
    public function report(){
        if ($this->request->isGet()){
            $judge=null;
            $data=array();
            $ids = input('id');
            $id = explode(',',$ids);
            for ($i=0;$i<count($id);$i++){
                $res = Db::name('res')->where('id',$id[$i])->find();
                if ($res['judge'] == 1){
                    $judge = '合格';
                }else{
                    $judge = '不合格';
                }
                $entrust = Db::name('entrust')->where('id',$res['entrust_id'])->find();
//                $gather = Db::name($entrust['gather_tab'])->where('pid',$entrust['gather_id'])->select();
                $gather = Db::name($entrust['gather_tab'])->where('pid',$entrust['gather_id'])->column('potency','chemical_compound');
                $data[$i] = [
                    'name' => $entrust['name'],
                    'sell_bach' => $entrust['sell_bach'],
                    'bach' => $entrust['bach'],
                    'massage' => $entrust['sample_info'],
                    'user_name' => $entrust['send_sample'],
                    'create' => $res['create'],
                    'resname' => $entrust['sample_no'],
                    'dis' => $res['dis'],
                    'dis_impurity' => $res['dis_impurity'],
                    'ben_total' => $res['ben_total'],
                    'ben' => $res['ben'],
                    'judge' => $judge,
                    'maker' => $entrust['receive_sample'],
                    'remark' => '',
                    '0' => isset($gather['甲醇']) ? $gather['甲醇'] : '',
                    '1' => isset($gather['乙醇']) ? $gather['乙醇'] : '',
                    '2' => isset($gather['异丙醇']) ? $gather['异丙醇'] : '',
                    '3' => isset($gather['丙酮']) ? $gather['丙酮'] : '',
                    '4' => isset($gather['正丙醇']) ? $gather['正丙醇'] : '',
                    '5' => isset($gather['丁酮']) ? $gather['丁酮'] : '',
                    '6' => isset($gather['乙酸乙酯']) ? $gather['乙酸乙酯'] : '',
                    '7' => isset($gather['乙酸异丙酯']) ? $gather['乙酸异丙酯'] : '',
                    '8' => isset($gather['正丁醇']) ? $gather['正丁醇'] : '',
                    '9' => isset($gather['苯']) ? $gather['苯'] : '',
                    '10' => isset($gather['1-甲氧基-2-丙醇']) ? $gather['1-甲氧基-2-丙醇'] : '',
                    '11' => isset($gather['乙酸正丙酯']) ? $gather['乙酸正丙酯'] : '',
                    '12' => isset($gather['2-乙氧基乙醇']) ? $gather['2-乙氧基乙醇'] : '',
                    '13' => isset($gather['4-甲基-2-戊酮']) ? $gather['4-甲基-2-戊酮'] : '',
                    '14' => isset($gather['1-乙氧基-2-丙醇']) ? $gather['1-乙氧基-2-丙醇'] : '',
                    '15' => isset($gather['甲苯']) ? $gather['甲苯'] : '',
                    '16' => isset($gather['乙酸正丁酯']) ? $gather['乙酸正丁酯'] : '',
                    '17' => isset($gather['乙苯']) ? $gather['乙苯'] : '',
                    '18' => isset($gather['间对二甲苯']) ? $gather['间对二甲苯'] : '',
                    '19' => isset($gather['邻-二甲苯']) ? $gather['邻-二甲苯'] : '',
                    '20' => isset($gather['苯乙烯']) ? $gather['苯乙烯'] : '',
                    '21' => isset($gather['2-乙氧基乙基乙酸酯']) ? $gather['2-乙氧基乙基乙酸酯'] : '',
                    '22' => isset($gather['环己酮']) ? $gather['环己酮'] : '',
                    '23' => isset($gather['丁二酸二甲酯']) ? $gather['丁二酸二甲酯'] : '',
                    '24' => isset($gather['戊二酸二甲酯']) ? $gather['戊二酸二甲酯'] : '',
                    '25' => isset($gather['己二酸二甲酯']) ? $gather['己二酸二甲酯'] : '',
                ];
            }
            $this->exceloutput($data);
        }else{
            $this->error('参数错误');
        }
    }
    //excel格式
    public function exceloutput($data){
        vendor("phpoffice/phpspreadsheet/src/PhpSpreadsheet/Spreadsheet");
        $objexcel = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
        $sheet = $objexcel->getActiveSheet();
        //设置全局单元格垂直居中
        $objexcel->getDefaultStyle()->getAlignment()->setVertical(Alignment::HORIZONTAL_CENTER);
        //设置全局单元格水平居中
        $objexcel->getDefaultStyle()->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        //设置全局单元格自动换行
        $objexcel->getDefaultStyle()->getAlignment()->setWrapText(true);
        //设置单元格宽度
        $sheet->getDefaultColumnDimension()->setWidth(18);
        //合并单元格，设置表头格式
        $sheet->mergeCells('A1:AN1');
        $sheet->mergeCells('A2:A3');
        $sheet->mergeCells('B2:B3');
        $sheet->mergeCells('C2:C3');
        $sheet->mergeCells('D2:D3');
        $sheet->mergeCells('E2:E3');
        $sheet->mergeCells('F2:F3');
        $sheet->mergeCells('G2:G3');
        $sheet->mergeCells('H2:H3');
        $sheet->mergeCells('I2:K2');
        $sheet->mergeCells('L2:L3');
        $sheet->mergeCells('M2:M3');
        $sheet->mergeCells('N2:N3');
        $sheet->mergeCells('O2:O3');
        $sheet->mergeCells('P2:P3');
        $sheet->mergeCells('Q2:Q3');
        $sheet->mergeCells('R2:R3');
        $sheet->mergeCells('S2:S3');
        $sheet->mergeCells('T2:T3');
        $sheet->mergeCells('U2:U3');
        $sheet->mergeCells('V2:V3');
        $sheet->mergeCells('W2:W3');
        $sheet->mergeCells('X2:X3');
        $sheet->mergeCells('Y2:Y3');
        $sheet->mergeCells('Z2:Z3');
        $sheet->mergeCells('AA2:AA3');
        $sheet->mergeCells('AB2:AB3');
        $sheet->mergeCells('AC2:AC3');
        $sheet->mergeCells('AD2:AD3');
        $sheet->mergeCells('AE2:AE3');
        $sheet->mergeCells('AF2:AF3');
        $sheet->mergeCells('AG2:AG3');
        $sheet->mergeCells('AH2:AH3');
        $sheet->mergeCells('AI2:AI3');
        $sheet->mergeCells('AJ2:AJ3');
        $sheet->mergeCells('AK2:AK3');
        $sheet->mergeCells('AL2:AL3');
        $sheet->mergeCells('AM2:AM3');
        $sheet->mergeCells('AN2:AN3');
        //设置表头
        $sheet->setCellValue('A2', '产品名称');
        $sheet->setCellValue('B2','销售订单号');
        $sheet->setCellValue('C2','生产批次号');
        $sheet->setCellValue('D2','样品信息');
        $sheet->setCellValue('E2','送样人');
        $sheet->setCellValue('F2','检测日期');
        $sheet->setCellValue('G2','检测编号');
        $sheet->setCellValue('H2','溶剂残留总量 ≤70.0mg/m²');
        $sheet->setCellValue('I2','溶剂杂质');
        $sheet->setCellValue('I3','溶剂杂质总量 ≤7.0mg/m²');
        $sheet->setCellValue('J3','苯系物总量 ≤0.7mg/m²');
        $sheet->setCellValue('K3','苯含量 ≤0.010mg/m²');
        $sheet->setCellValue('L2','判定结论');
        $sheet->setCellValue('M2','检测人');
        $sheet->setCellValue('N2','备注');
        $sheet->setCellValue('O2','甲醇');
        $sheet->setCellValue('P2','乙醇');
        $sheet->setCellValue('Q2','异丙醇');
        $sheet->setCellValue('R2','丙酮');
        $sheet->setCellValue('S2','正丙醇');
        $sheet->setCellValue('T2','丁酮');
        $sheet->setCellValue('U2','乙酸乙酯');
        $sheet->setCellValue('V2','乙酸异丙酯');
        $sheet->setCellValue('W2','正丁醇');
        $sheet->setCellValue('X2','苯');
        $sheet->setCellValue('Y2','1-甲氧基-2-丙醇');
        $sheet->setCellValue('Z2','乙酸正丙酯');
        $sheet->setCellValue('AA2','2-乙氧基乙醇');
        $sheet->setCellValue('AB2','4-甲基-2-戊酮');
        $sheet->setCellValue('AC2','1-乙氧基-2-丙醇');
        $sheet->setCellValue('AD2','甲苯');
        $sheet->setCellValue('AE2','乙酸正丁酯');
        $sheet->setCellValue('AF2','乙苯');
        $sheet->setCellValue('AG2','间对-二甲苯');
        $sheet->setCellValue('AH2','邻-二甲苯');
        $sheet->setCellValue('AI2','苯乙烯');
        $sheet->setCellValue('AJ2','2-乙氧基乙基乙酸酯');
        $sheet->setCellValue('AK2','环己酮');
        $sheet->setCellValue('AL2','丁二酸二甲酯');
        $sheet->setCellValue('AM2','戊二酸二甲酯');
        $sheet->setCellValue('AN2','己二酸二甲酯');
        //设置A列宽度
        $sheet->getColumnDimension('A')->setWidth(25);
        //插入表格内容
        for ($i=0;$i<count($data);$i++){
            $k = $i+4;
            $sheet->getCell('A'.$k)->setValue($data[$i]['name']);
            $sheet->getCell('B'.$k)->setValue($data[$i]['sell_bach']);
            $sheet->getCell('C'.$k)->setValue($data[$i]['bach']);
            $sheet->getCell('D'.$k)->setValue($data[$i]['massage']);
            $sheet->getCell('E'.$k)->setValue($data[$i]['user_name']);
            $sheet->getCell('F'.$k)->setValue($data[$i]['create']);
            $sheet->getCell('G'.$k)->setValue($data[$i]['resname']);
            $sheet->getCell('H'.$k)->setValue($data[$i]['dis']);
            $sheet->getCell('I'.$k)->setValue($data[$i]['dis_impurity']);
            $sheet->getCell('J'.$k)->setValue($data[$i]['ben_total']);
            $sheet->getCell('K'.$k)->setValue($data[$i]['ben']);
            $sheet->getCell('L'.$k)->setValue($data[$i]['judge']);
            $sheet->getCell('M'.$k)->setValue($data[$i]['maker']);
            $sheet->getCell('N'.$k)->setValue($data[$i]['remark']);
            $sheet->getCell('O'.$k)->setValue($data[$i]['0']);
            $sheet->getCell('P'.$k)->setValue($data[$i][1]);
            $sheet->getCell('Q'.$k)->setValue($data[$i][2]);
            $sheet->getCell('R'.$k)->setValue($data[$i][3]);
            $sheet->getCell('S'.$k)->setValue($data[$i][4]);
            $sheet->getCell('T'.$k)->setValue($data[$i][5]);
            $sheet->getCell('U'.$k)->setValue($data[$i][6]);
            $sheet->getCell('V'.$k)->setValue($data[$i][7]);
            $sheet->getCell('W'.$k)->setValue($data[$i][8]);
            $sheet->getCell('X'.$k)->setValue($data[$i][9]);
            $sheet->getCell('Y'.$k)->setValue($data[$i][10]);
            $sheet->getCell('Z'.$k)->setValue($data[$i][11]);
            $sheet->getCell('AA'.$k)->setValue($data[$i][12]);
            $sheet->getCell('AB'.$k)->setValue($data[$i][13]);
            $sheet->getCell('AC'.$k)->setValue($data[$i][14]);
            $sheet->getCell('AD'.$k)->setValue($data[$i][15]);
            $sheet->getCell('AE'.$k)->setValue($data[$i][16]);
            $sheet->getCell('AF'.$k)->setValue($data[$i][17]);
            $sheet->getCell('AG'.$k)->setValue($data[$i][18]);
            $sheet->getCell('AH'.$k)->setValue($data[$i][19]);
            $sheet->getCell('AI'.$k)->setValue($data[$i][20]);
            $sheet->getCell('AJ'.$k)->setValue($data[$i][21]);
            $sheet->getCell('AK'.$k)->setValue($data[$i][22]);
            $sheet->getCell('AL'.$k)->setValue($data[$i][23]);
            $sheet->getCell('AM'.$k)->setValue($data[$i][24]);
            $sheet->getCell('AN'.$k)->setValue($data[$i][25]);
            //不合格字体设置红色
            $start = 'A'.$k; $xend = 'AN'.$k;
            if ($data[$i]['judge'] == '不合格'){
                $sheet->getStyle("$start:$xend")->getFont()->getColor()
                    ->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);
            }
        }
        //excel文件格式
        $filename = '数据报告'.date('ymdhis',time()).'.xlsx';
        $filename = iconv("utf-8","gb2312",$filename);
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="'.$filename.'"');
        header('Cache-Control: max-age=0');
        header("Content-Type:text/html;charset=utf-8");
        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($objexcel, 'Xlsx');
        $writer->save('php://output');
        exit;
    }
}