最新ThinkPHP版本实现证书查询系统，实现批量数据导入，自动生成电子证书

前提：朋友弄了一个培训机构，培训考试合格后，给发证书，需要一个证书查询系统。委托我给弄一个，花了几个晚上给写的证书查询系统。

实现功能：

1. 前端按照姓名+手机号码进行证书查询
2. 证书信息展示+证书展示，支持点击下载
3. 后端证书信息录入+带一寸照片数据批量导入

技术栈：
4. ThinkPHP8.0
5. MySQL8.0.12
6. layui

有几个技术点，得记录下：
1、给一个证书模板、一寸照片、两个电子印章图片，这个技术点是：GD库添加水印技术，水印添加也包括文字水印

添加扩展：

"ext-gd": "*",

相关代码：

// 向不同格式的图片中间画一个字符串（也是文字水印）
public function image(){// $id = Request::param('id');$one = Db::table('certificate')->where(Request::param())->find();// $this->pic($one['id']);/*$one = ["certificate_no" => "440420230078","create_time" => "2022-10-10","end_time" => "2032-10-10","id" => 8,"id_no" => "4404202206223919","id_type" => "身份证","institution" => "智能研究院","job" => "电工","name" => "曹操","occupation" => "低压电工证","phone" => "13588889999","sex" => "男","skill_level" => "中级","start_time" => "2022-10-10"];*/// 图片路径$imagePath = './static/images/zsmb.png';// 图片信息$imageInfo = getimagesize($imagePath);//  [0] => 2000 [1] => 1414 [2] => 3 [3] => width="2000" height="1414" [bits] => 8 [mime] => image/png// 图片扩展名$imageExtension = image_type_to_extension($imageInfo[2], false);// png// 获取图片$func = 'imagecreatefrom' . $imageExtension;// imagecreatefrompng$image = $func($imagePath);/***************************** 文字水印开始 ********************************/// 水印字体$font = './static/fonts/SourceHanSansSC-Medium-2.otf';// 水印文字// $content = '曹操';// 水印颜色$color = imagecolorallocatealpha($image, 0, 0, 0, 30);// 添加水印imagettftext($image, 22, 0, 1450, 650, $color, $font, $one['name']);imagettftext($image, 22, 0, 1450, 740, $color, $font, $one['id_type']);imagettftext($image, 22, 0, 1450, 830, $color, $font, $one['id_no']);imagettftext($image, 22, 0, 1450, 910, $color, $font, $one['occupation']);imagettftext($image, 22, 0, 1480, 985, $color, $font, $one['job']);imagettftext($image, 22, 0, 1480, 1075, $color, $font, $one['skill_level']);imagettftext($image, 22, 0, 1450, 1155, $color, $font, $one['certificate_no']);imagettftext($image, 22, 0, 750, 1080, $color, $font, date("Y年n月j日", strtotime($one['create_time'])));imagettftext($image, 22, 0, 300, 1080, $color, $font, date("Y年n月j日", strtotime($one['create_time'])));imagettftext($image, 22, 0, 500, 498, $color, $font, $one['institution']);/***************************** 文字水印开始 ********************************//***************************** 图片水印开始 ********************************/// 获取水印图片$watermarkPath = './static/images/water.png';$watermarkInfo = getimagesize($watermarkPath);$watermarkExtension = image_type_to_extension(getimagesize($watermarkPath)[2], false);$func = 'imagecreatefrom' . $watermarkExtension;$watermark = $func($watermarkPath);// 添加图片水印imagecopymerge($image, $watermark, 1280, 180, 0, 0, $watermarkInfo[0], $watermarkInfo[1], 100);// 销毁水印图片imagedestroy($watermark);// 生成二维码$qr = "./static/images/qr.png";$str = 'http://thinkcms.cc/index/index/image?id=' . $one['id'];$img_data = \tekintian\TekinQR::getQRImg($str, 5, null, 3);file_put_contents($qr, $img_data);$watermarkInfo = getimagesize($qr);$watermarkExtension = image_type_to_extension(getimagesize($qr)[2], false);$func = 'imagecreatefrom' . $watermarkExtension;$watermark = $func($qr);// 添加图片水印imagecopymerge($image, $watermark, 1580, 370, 0, 0, $watermarkInfo[0], $watermarkInfo[1], 100);// 销毁水印图片imagedestroy($watermark);$watermarkPath = './static/images/nyyz.png';$watermarkInfo = getimagesize($watermarkPath);$watermarkExtension = image_type_to_extension(getimagesize($watermarkPath)[2], false);$func = 'imagecreatefrom' . $watermarkExtension;$watermark = $func($watermarkPath);// 添加图片水印imagecopymerge($image, $watermark, 220, 850, 0, 0, $watermarkInfo[0], $watermarkInfo[1], 100);// 销毁水印图片imagedestroy($watermark);$watermarkPath = './static/images/gjyz.png';$watermarkInfo = getimagesize($watermarkPath);$watermarkExtension = image_type_to_extension(getimagesize($watermarkPath)[2], false);$func = 'imagecreatefrom' . $watermarkExtension;$watermark = $func($watermarkPath);// 添加图片水印imagecopymerge($image, $watermark, 720, 850, 0, 0, $watermarkInfo[0], $watermarkInfo[1], 100);// 销毁水印图片imagedestroy($watermark);/***************************** 图片水印开始 ********************************/// 输出图片header('Content-Type:' . $imageInfo['mime']);$func = 'image' . $imageExtension;$func($image, null, 6);// 销毁图片imagedestroy($image);exit;}

2、证书里的二维码，扫码就能扫码出来，是PHP生成二维码，其二维码中间也是支持加logo的

添加扩展：

"tekintian/phpqrcode": "^1.1",

相关代码：

// 生成二维码
$qr = "./static/images/qr.png";
// 这个本地测试域名，不是外网，扫码不了，上线改成外网扫了才有效
$str = 'http://thinkcms.cc/index/index/image?id=' . $one['id'];
$img_data = \tekintian\TekinQR::getQRImg($str, 5, null, 3);
file_put_contents($qr, $img_data);

3、后台实现带图片excel数据导入，这个摸索了点时间，因为PHPExcel停止维护了，改了半天源码还有问题，最后放弃了，换了一个扩展。

添加扩展：

"phpoffice/phpspreadsheet": "^1.20.0"

相关代码：

/*** 字母序列化为数字*/
public function ABC2decimal($abc)
{$ten = 0;$len = strlen($abc);for ($i = 1; $i <= $len; $i++) {$char = substr($abc, 0 - $i, 1);//反向获取单个字符$int = ord($char);$ten += ($int - 65) * pow(26, $i - 1);}return $ten;
}public function excel()
{if ($file = request()->file('excel')) {try {$saveName = Filesystem::disk('public')->putFile('/static/upload/excels', $file);if (!is_file($saveName)) {return json(['code' => 1, 'msg' => '文件不存在', 'data' => null]);}$ext = pathinfo($saveName, PATHINFO_EXTENSION);// xls不支持图片导入// if (!in_array($ext, ['xlsx', 'xls'])) {if (!in_array($ext, ['xlsx'])) {return json(['code' => 1, 'msg' => '文件类型不正确', 'data' => null]);}// 有两种格式，xlsx和xlsif ($ext == 'xlsx') {$objReader = IOFactory::createReader('Xlsx');} else {$objReader = IOFactory::createReader('Xls');}// 图片保存路径$imageFilePath1 = root_path() . '/public/'; // 图片保存目录$imageFilePath2 = 'static/upload/images/' . date("Ymd") . '/';$imageFilePath = $imageFilePath1 . $imageFilePath2;if (!file_exists($imageFilePath)) {mkdir("$imageFilePath", 0777, true);}// 载入excel文件$excel = $objReader->load($saveName);// 读取第一张表$sheet = $excel->getActiveSheet();// 读取总行数$highestRow = $sheet->getHighestRow();// 读取第一张表转换成数组$data = $sheet->toArray();// 处理图片foreach ($sheet->getDrawingCollection() as $drawing) {list($startColumn, $startRow) = Coordinate::coordinateFromString($drawing->getCoordinates());$imageFileName = $drawing->getIndexedFilename();  // 获取文件名switch ($drawing->getExtension()) {case 'jpg':case 'jpeg':$source = imagecreatefromjpeg($drawing->getPath());imagejpeg($source, $imageFilePath . $imageFileName);break;case 'gif':$source = imagecreatefromgif($drawing->getPath());imagegif($source, $imageFilePath . $imageFileName);break;case 'png':$source = imagecreatefrompng($drawing->getPath());imagepng($source, $imageFilePath . $imageFileName);break;}$startColumn = $this->ABC2decimal($startColumn);$data[$startRow - 1][$startColumn] = $imageFilePath2 . $imageFileName;}// 数据写入数据库$add_data = [];for ($i = 1; $i <= $highestRow - 1; $i++) {$add_data[$i]['institution'] = $data[$i][0];$add_data[$i]['name'] = $data[$i][1];$add_data[$i]['sex'] = $data[$i][2];$add_data[$i]['phone'] = $data[$i][3];$add_data[$i]['id_type'] = $data[$i][4];$add_data[$i]['id_no'] = $data[$i][5];$add_data[$i]['occupation'] = $data[$i][6];$add_data[$i]['job'] = $data[$i][7];$add_data[$i]['skill_level'] = $data[$i][8];$add_data[$i]['certificate_no'] = $data[$i][9];$add_data[$i]['create_time'] = $data[$i][10];$add_data[$i]['start_time'] = $data[$i][11];$add_data[$i]['end_time'] = $data[$i][12];$add_data[$i]['path'] = $data[$i][13];}// 数据插入数据库$success_count = Db::table('certificate')->insertAll($add_data);if ($success_count > 0) {return json(['code' => 0, 'msg' => '数据插入成功', 'data' => null]);} else {return json(['code' => 1, 'msg' => '数据插入失败', 'data' => null]);}} catch (\Exception $e) {return json(['code' => 1, 'msg' => $e->getMessage(), 'data' => null]);}} else {return json(['code' => 1, 'msg' => '上传文件不能为空', 'data' => null]);}
}

4. ThinkPHP8.0开启session，没有采取前后端分离，就是前后端分离后，SEO不友好，再去前端优化搜索，那还不如直接MVC来的快，所以用到了session。根据官方手册没有配置成功，session没有生效，后面尝试了下面的方法，两者都进行配置才生效。这里记录下。

全局config配置middleware.php中

<?php
// 中间件配置
return [// 别名或分组'alias'    => [],// 优先级设置，此数组中的中间件会按照数组中的顺序优先执行'priority' => ['think\middleware\SessionInit'],
];

后端应用middleware.php中

<?php
// 这是系统自动生成的middleware定义文件
return [// 无关代码省略...\think\middleware\SessionInit::class
];

弄了写数据模拟下，最终实现的功能成果如下：