MatchItemPrize.php
13.1 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
<?php
namespace app\school\controller\match;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
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);
$PHPExcel = $reader->load($file['save_path']); //载入excel表格
$currentSheet = $PHPExcel->getSheet(0); //读取文件中的第一个工作表
$allColumn = $currentSheet->getHighestDataColumn(); //取得最大的列号
$currentSheetName = $PHPExcel->getSheetNames(); //读取文件中的第一个工作表
$allRow = $currentSheet->getHighestRow(); //取得一共有多少行
$maxColumnNumber = Coordinate::columnIndexFromString($allColumn);
$datas = [];
for ($currentRow = 3; $currentRow <= $allRow; $currentRow++) {
for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
$val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
if(!$val){
//删除空行
continue;
}
$datas[$currentRow][] = $val;
}
}
unlink($_SERVER['DOCUMENT_ROOT'] . "/assets/file/".$tmpName);
if(empty($datas)) {
$this->error('更新失败');
}
$newArr = [];
$existsArr = [];
foreach ($datas as $key => &$val) {
$addIds[] = $val[0];
$val[5] = 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
$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间接的,以某个坐标为依托,联级
}
}