MatchItemPrize.php 12.5 KB
<?php

namespace app\school\controller\match;

use think\Db;
use app\common\controller\ManystoreBase;
use app\school\model\match\MatchItems;
use app\common\model\match\MatchItemPrize as MatchItemPrizeModel;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Cell\DataValidation;

/**
 * 竞赛管理获奖信息
 *
 * @icon fa fa-circle-o
 */
class MatchItemPrize extends ManystoreBase
{
    protected $model = null;
    protected $noNeedRight = ['*'];
    public function _initialize()
    {
        parent::_initialize();
        $this->model = new MatchItemPrizeModel;
        $this->assign('prize_type',$this->model->getPrizeType());
    }

    /**
     * @param null $item_id 报名id
     * @return string
     * 添加获奖信息
     */
    public function add ($item_id = null)
    {
        if ($this->request->isPost()) {
            $params = $this->request->post("row/a");
            $itemInfo = (new MatchItems())->where(['id' => $params['match_items_id']])->find();
            if($this->model->where(['match_items_id' => $params['match_items_id']])->find()) {
                $this->error(__('该项目已经添加过奖项'));
            }
            if ($params) {
                Db::startTrans();
                try {
                    $result = $this->model->save($params);
                    Db::commit();
                } catch (Exception $e) {
                    Db::rollback();
                    $this->error($e->getMessage());
                }
                $this->success();
            }
            $this->error(__('Parameter %s can not be empty', ''));
        }
        $this->assign('match_items_id',$item_id);
        return $this->view->fetch();
    }

    /**
     * @param null $item_id 报名id
     * @return string
     * 编辑获奖信息
     */
    public function edit ($item_id = null)
    {
        $row = $this->model->where(['match_items_id' => $item_id])->find();
        if ($this->request->isPost()) {
            $params = $this->request->post("row/a");
            $matchItemId = $params['match_items_id'];
            if ($params) {
                unset($params['match_items_id']);
                Db::startTrans();
                try {
                    $this->model->where(['match_items_id' => $matchItemId])->update($params);
                    Db::commit();
                } catch (Exception $e) {
                    Db::rollback();
                    $this->error($e->getMessage());
                }
                $this->success();
            }
            $this->error(__('Parameter %s can not be empty', ''));
        }
        $this->assign('match_items_id',$item_id);
        $this->assign('row',$row);
        return $this->view->fetch();
    }

    /**
     * 批量设置获奖信息
     */
    public function set_level($ids = null)
    {
        if ($this->request->isPost()) {
            $params = $this->request->post("row/a");
            $ids = explode(',',$params['ids']);
            if(empty($ids)) {
                $this->error(__('请勾选数据'));
            }

            $exists = $this->model->whereIn('match_items_id',$ids)->select();

            $new = [];
            $existsArr = [];
            foreach ($ids as $value) {
                $tmp['prize_type'] = $params['prize_type'];
                $tmp['get_time']   = $params['get_time'];
                $tmp['match_items_id'] = $value;
                $new[] = $tmp;
                foreach ($exists as $key => $exist) {
                    if($value == $exist['match_items_id']) {
                        $existsArr[$key] = $exist['id'];
                    }
                }
            }
            if (!empty($new)) {
                Db::startTrans();
                try {
                    //删除之前的获奖信息记录
                    if(!empty($existsArr)) {
                        $this->model->whereIn('id',$existsArr)->delete();
                    }
                    $result = $this->model->saveAll($new);
                    Db::commit();
                } catch (Exception $e) {
                    Db::rollback();
                    $this->error($e->getMessage());
                }
                $this->success();
            }
            $this->error(__('Parameter %s can not be empty', ''));
        }
        $this->assign('ids',$ids);
        return $this->view->fetch();
    }

    /**
     * 导入获奖信息页
     * @param null $ids  报名项目Id
     */
    public function set_prize($ids = null)
    {
        if($this->request->isPost()) {
            $params = $this->request->post("row/a");
            $tmpName = time().SCHOOL_ID.'.xlsx';
            $file = getFile('http://chuangxinchuangye.posedu.com.cn/'.$params['url'],$_SERVER['DOCUMENT_ROOT'] . "/assets/file",$tmpName,true);

            $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
            $reader->setReadDataOnly(true);
            $spreadsheet = $reader->load($file['save_path']); //载入excel表格

            $datas = $spreadsheet->getActiveSheet()->toArray();
            unlink($_SERVER['DOCUMENT_ROOT'] . "/assets/file/".$tmpName);
            if(empty($datas)) {
                $this->error('更新失败');
            }
            $newArr = [];
            $existsArr = [];
            foreach ($datas as $key => &$val) {
                if($key <= 1) {
                    unset($datas[$key]);
                    continue;
                }
                $addIds[] = $val[0];
                $val[5] = $date = date('Y-m-d', ($val[5] - 25569) * 24 * 3600);
            }
            $exists = $this->model->whereIn('match_items_id',$addIds)->select();
            foreach ($datas as $vval) {
                $tmp['match_items_id'] = $vval[0]; //id
                if(empty($vval[4]) || empty($vval[5])) {
                    $this->error('导入文件有误,请重新导入');
                }
                $tmp['prize_type'] = $vval[4];     //奖项类型
                $tmp['get_time'] = $vval[5];       //获奖时间
                if(!empty($vval[6])) {
                    if(strpos($vval[6],'chuangxinchuangye.posedu.com.cn') == false) {
                        $this->error('证书路径有误,请先去平台生成');
                    }
                }
                $tmp['url'] = $vval[6];            //获奖图片
                $newArr[] = $tmp;
                foreach ($exists as $v) {
                    //已经设置过奖项的要删除
                    if($vval[0] == $v['match_items_id']) {
                        $existsArr[] =  $v['id'];
                    }
                }
            }
            if (!empty($newArr)) {
                Db::startTrans();
                try {
                    //删除之前的获奖信息记录
                    if(!empty($existsArr)) {
                        $this->model->whereIn('id',$existsArr)->delete();
                    }
                    $result = $this->model->saveAll($newArr);
                    Db::commit();
                } catch (Exception $e) {
                    Db::rollback();
                    $this->error('导入失败,稍后请重试');
                }
                $this->success();
            }
            $this->error(__('Parameter %s can not be empty', ''));
        }
        $this->assign('ids',$ids);
        return $this->view->fetch();
    }

    public function download_file()
    {
        $ids = explode(',',request()->param('ids'));
        $excel = new Spreadsheet();
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $spreadsheet->getDefaultStyle()->getFont()->setName('Microsoft Yahei');
        $spreadsheet->getDefaultStyle()->getFont()->setSize(12);
        //默认水平居中
        $spreadsheet->getDefaultStyle()->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
//默认垂直居中
        $spreadsheet->getDefaultStyle()->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
        //样式---------start
        $spreadsheet->getActiveSheet()->getRowDimension('1')->setRowHeight(30);
        $spreadsheet->getActiveSheet()->getDefaultColumnDimension()->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(50);
        $spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(50);
        $spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(50);
        $spreadsheet->getActiveSheet()->getStyle('F')->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_YYYYMMDD);
//        $spreadsheet->getActiveSheet(0)->freezePane('A1');
        $sheet->mergeCells('A1:G1');
        //样式---------end
        //标题
        $data = (new MatchItems())->alias('m')
            ->field('m.*,m.project_name,u.username,u.student_no')
            ->join('user u','m.user_id = u.id','left')
            ->whereIn('m.id',$ids)
            ->select();
        $NewData = [];
        foreach ($data as $v) {
            $tmp['id'] = $v['id'];
            $tmp['project_name'] = $v['project_name'];
            $tmp['username'] = $v['username'];
            $tmp['student_no'] = $v['student_no'];
            $tmp['prize_type'] = '';
            $tmp['get_time'] = '';
            $tmp['url'] = '';
            $NewData[] = $tmp;
        }

        $desc = $data[0]['match_title'].'_'.$data[0]['match_type_text'];
        $fileName = $data[0]['match_title'].'_'.$data[0]['match_type_text'].date('Ymd').mt_rand();
        $sheet->setCellValue('A1',$desc);

        $title = [['id' => 'ID'],['project_name' => '项目名称'],['username' => '姓名'],['student_no' => '学号'],['prize_type' => '获奖级别(1:一等奖 2二等奖 3三等奖)'],['get_time' => '获奖时间(时间格式必须为2021-06-06)'],['url' => '获奖证书(必须为图片jpg jpeg,png格式,大小10MB)']];
        foreach ($title as $key => $value) {
            foreach ($value as $item) {
                // 单元格内容写入
                $sheet->setCellValueByColumnAndRow($key + 1, 2, $item);
            }
        }
        $row = 3; // 从第二行开始
        foreach ($NewData as $item) {
            $column = 1;
            foreach ($item as $value) {
                // 单元格内容写入
                $sheet->setCellValueByColumnAndRow($column, $row, $value);
                $column++;
            }
            $row++;
        }

        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="' . $fileName . '.xlsx"');
        header('Cache-Control: max-age=0');
        // If you're serving to IE 9, then the following may be needed
        header('Cache-Control: max-age=1');

        // If you're serving to IE over SSL, then the following may be needed
        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
        header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header('Pragma: public'); // HTTP/1.0


        $objWriter = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $objWriter->save('php://output');
        dump(collection($data)->toArray());die;
        dump($sheet);die;

    }
    /**
     * 设置某个单元格的下拉列表规则
     * @param Worksheet $sheet
     * @param [string] $cellPoint 单元格坐标. A1
     * @param [sting] $format 公式
     * @return void
     */
    protected function setValidation($sheet, $cellPoint, $format)
    {
        $validation = $sheet -> getCell($cellPoint) -> getDataValidation();
        $validation -> setType(DataValidation::TYPE_LIST);
        $validation -> setErrorStyle(DataValidation::STYLE_INFORMATION);
        $validation -> setAllowBlank(false);
        $validation -> setShowInputMessage(true);
        $validation -> setShowErrorMessage(true);
        $validation -> setShowDropDown(true);
        $validation -> setErrorTitle('输出错误');
        $validation -> setError('值不在列表中');
        $validation -> setPromptTitle('请选择');
        $validation -> setPrompt('请从列表中选择一个值');
        $validation -> setFormula1($format);
        // $validation->setFormula1('=major');//使用某个公式
        // $validation->setFormula1('"A1,A2,A3"');//可以直接写列表,用逗号分隔,最多255字符
        // $objValidation->setFormula1('=INDIRECT(F5)');//indirect间接的,以某个坐标为依托,联级
    }
}