系统概述:
- 基于工作的实际需求,纪录项目实践经验。
- 这套系统是用于展会观众登记、门票销售、信息提醒、邀约参观、展商邀约、VIP邀约管理等展会相关用途;
- 技术框架:基于fastadmin+活动报名插件+数据导入插件+二次开发
- 关于代码:展示出来的95%以上都是亲手码出来的,一方面给自己做个记录方便查阅,另一方面跟大家分享一些解决方法,不足是肯定有的,欢迎留言指正~
一、活动创建
基本操作,不展示代码了。
1.1 创建分类
1.2 基于分类创建活动项目
二、渠道管理
每个报名表单都能跟踪不同的报名渠道,各个渠道可以导入数据。
2.1 渠道创建/编辑/删除
基本操作,不展示代码了。
2.2 复制链接
代码实现:原理就是 url 传参,没有什么难度了 。
只需要:
1、只需要做个小判断,是否设置了活动链接;
2、拿到这个活动链接;
上述两点我是通过 Ajax 请求实现的。
{
'name': 'project_field',
'title': function (row) {
return '项目字段管理[' + row.title + ']';
},
'icon': 'fa fa-edit',
'text': '复制链接',
'classname': 'btn btn-xs btn-warning btn-click',
click:function(value, row, index){
Fast.api.ajax({
url: 'miniform/project/info/source_id/' + Config.params.source_id,
data: {
source_id: Config.source_id
}
}, function (data, ret) {
console.log(data)
var url = data.url;
if(!url){
Toastr.error('未配置活动页面的URL');
return false;
}
// var url = 'https://mjjz.gde3f.com/html/M52/dist/#/home?ly=qd';
url += ('&fcode='+row.qdcode)
// console.log(url)
if (!document.queryCommandSupported('copy')) return Layer.msg('你的浏览器不支持复制')
const inputElement = document.createElement("input");
inputElement.value = url;
document.body.appendChild(inputElement);
inputElement.select();
document.execCommand("copy");
document.body.removeChild(inputElement);
// Layer.msg('已复制到粘贴板')
Layer.confirm('已复制到粘贴板,需要打开吗?', {icon: 3,btn: ['走你','不']}, function (index) {
window.open(url);
Layer.close(index)
},function(){
Layer.msg('🙂')
})
}, function (data, ret) {
Toastr.error(ret.msg);
});
}
}
2.3 复制导入
先说为什么要做复制导入,跟 excel 导入有什么区别,原因是:excel 导入太麻烦,工作中更多的场景是,打开 Excel 表格,复制粘贴,不快很多了吗,实际用下来也是挺好用的。
比如这是Excel 表格:
只需要复制到 textarea:
后端代码:
/**
* 数据导入
*
* @return string
* @throws \think\Exception
*/
public function import()
{
$prefix = Config::get('database.prefix');
$sourcetable = $this->request->request('sourcetable');
$table = $prefix.'miniform_'.$sourcetable;
if (false === $this->request->isPost()) {
$tableinfo = $this->tableinfo($table);
// print_r($tableinfo);exit;
$this->view->assign('tableinfo',$tableinfo);
return $this->view->fetch();
}
$params = $this->request->post('row/a');
$field = $this->request->post('field/a');
$data = $this->request->post('data');
if (empty($params)) {
$this->error(__('Parameter %s can not be empty', ''));
}
if($data == '' || !strpos($data, Chr(13)) || !strpos($data, Chr(9))){
$this->error('导入数据格式不正确!');
}
// 补充必填信息
$mustArr = ['project_id'=>$params['project_id'],'qdid'=>$params['qudaoid'],'createtime'=>strtotime($params['importtime'])];
$list = explode("\n",$data); // 导入的内容
$insertAll = array(); // 存数据库的数组
foreach ($list as $k => $v) {
$arr = explode("\t",trim($v));
if(count($arr) != count($field)) $this->error('字段匹配不上');
$insertAll[] = array_merge($mustArr,array_combine($field,$arr));
}
$count = count($insertAll);
$msg = '共计:'.$count.'条';
//号码查重开关
if($params['unique_mobile']){
// 查找数据库已存在的手机号
$phonelist = Db::name(str_replace($prefix, "", $table))->column('mobile');
// print_r($phonelist);exit;
$exists = 0;
foreach ($insertAll as $key=>$val){
if(in_array($val['mobile'],$phonelist)){
unset($insertAll[$key]);
$exists ++;
}
// 号码校验规则:todo
}
$msg .= ',重复号码'.$exists.'条,成功导入'.$count-$exists.'条';
}
// if($count == 0) $this->error();
$result = false;
Db::startTrans();
try {
// 写入数据库的逻辑
$result = Db::name(str_replace($prefix, "", $table))->insertAll($insertAll);
Db::commit();
} catch (ValidateException|PDOException|Exception $e) {
Db::rollback();
$this->error($e->getMessage());
}
if ($result === false) {
$this->error($msg);
}
// 添加完成后
$this->success($msg);
}
2.4excel 导入
实现 excel 表格数据导入,两点功能有:
1、可以不按照导入模板,匹配字段对应关系导入;
2、大数据量导入(实测 16W+数据一次性导入);
3、导入数据预览;
4、号码去重功能;
后端代码:
/**
* 添加
*/
public function add()
{
if ($this->request->isPost()) {
$params = $this->request->post("row/a");
$step = $params['step'];
if ($params) {
$params = $this->preExcludeFields($params);
if ($this->dataLimit && $this->dataLimitFieldAutoFill) {
$params[$this->dataLimitField] = $this->auth->id;
}
$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 . '.add' : $name) : $this->modelValidate;
$this->model->validateFailException(true)->validate($validate);
}
//新建表导入处理
if ($params['newtable']) {
$prefix = Config::get('database.prefix');
$table = $prefix . $params['newtable'];
$check = db()->query("SHOW TABLES LIKE '%{$table}%';");
if ($check) {
$this->error(__($params['newtable'] . '表已经存在'));
}
} else {
if (!$params['table']) $this->error('未选择目标表');
}
// 返回表格数据,数组格式,包含的内容有:
// path文件路径,field 表格的字段,fieldarr导入表的所有字段,data 表格数据,isnert 空数组,excelfield,count
$fileData = $this->fileData($params);
// print_r($fileData);exit;
$fileData['params'] = http_build_query($params);
$fileData['newtable'] = $params['newtable'];
if (!$step) {
$this->success('匹配到' . $fileData['count'] . '列,开始预览', '', $fileData);
}
// 这个插入是空白的,要自己重新拼接
$insert = $fileData['insert'];
$fieldArr = $fileData['fieldArr'];
//是否包含admin_id字段
$has_admin_id = false;
foreach ($fieldArr as $name => $key) {
if ($key == 'admin_id') {
$has_admin_id = true;
break;
}
}
if ($has_admin_id) {
foreach ($insert as &$val) {
if (!isset($val['admin_id']) || empty($val['admin_id'])) {
$val['admin_id'] = $this->auth->isLogin() ? $this->auth->id : 0;
}
}
}
// 字段对应关系
$field = [];
$set = $params['set'];
$excelfield = $fileData['excelField'];
// 判断字段对应关系是否完整
if(count($excelfield) != count($set)){
$this->error('字段对应关系不完整!请尝试减少 excel 表的字段');
}
foreach ($set as $key=>$val){
// 如果是“请选择”,删除 excel 里面的字段
if($val == '请选择') {
unset($excelfield[$key]);
}else{
// 知识点:如果参数数组中的键和值都会使用到回调函数中,则不要使用 array_map() 函数,而是应该使用 foreach 循环来进行操作
foreach ($fieldArr as $key2=>$val2){
if($key2 == $val){
$field[] = $val2['COLUMN_NAME'];
break;
}
}
}
}
// print_r($set);
// print_r($excelfield);
// print_r($fieldArr);
// print_r($field);exit;
$prefix = Config::get('database.prefix');
if(count($excelfield) != count($field)){
$this->error('字段对应失败,请检查字段格式或备注是否有空格');
}
// 生成 excel 表格字段跟数据库字段的对应关系
$duiying = array_combine($excelfield,$field);
// print_r($duiying);exit;
// 补充必填信息
$mustArr = ['project_id'=>$params['project_id'],'qdid'=>$params['qudaoid']];
// 拼装导入的数据 insertAll
$insertAll = [];
//号码查重开关
if($params['unique_mobile']){
// 查找数据库已存在的手机号
$phonelist = Db::name(str_replace($prefix, "", $params['table']))->column('mobile');
// 重复计数器
$exists = 0;
}
$count = count($fileData['data']);
foreach ($fileData['data'] as $val){
$arr = [];
foreach($val as $k=>$v){
if(isset($duiying[$k])){
if($duiying[$k] == 'mobile' && $params['unique_mobile']){
if(in_array($v,$phonelist)){
$exists ++;
break;
}else{
$arr[$duiying[$k]] = $v;
}
}
}else{
continue;
}
if(!empty($arr)) $insertAll[] = array_merge($arr,$mustArr);
}
}
// print_r($insertAll);exit;
// 写入数据库的逻辑
$res = Db::name(str_replace($prefix, "", $params['table']))->insertAll($insertAll);
Db::commit();
} catch (ValidateException $e) {
Db::rollback();
$this->error($e->getMessage());
} catch (PDOException $e) {
Db::rollback();
$this->error($e->getMessage());
} catch (Exception $e) {
Db::rollback();
$this->error($e->getMessage());
}
if ($count !== false) {
// $params['status'] = 'normal';
// $result = $this->model->allowField(true)->save($params);
// $tip = $params['update'] ? '成功更新' : '成功新增';
if(isset($exists)){
$msg = '一共' . $count . '条记录,重复号码'.$exists.'条,成功导入'.$count-$exists.'条';
}else{
$msg = '一共' . $count . '条记录,成功导入'.$count.'条';
}
$this->success($msg, '', array('count' => $count));
} else {
$this->error(__('No rows were inserted'));
}
}
$this->error(__('Parameter %s can not be empty', ''));
}
$this->view->assign("update", $this->request->request('update'));
$this->view->assign("to", $this->request->request('to'));
$sourcetable = 'fa_miniform_'.$this->request->request('sourcetable');
$this->view->assign("sourcetable", 'fa_miniform_'.$this->request->request('sourcetable'));
return $this->view->fetch();
}
/**
* 编辑
*
*/
public function edit($ids = null)
{
$row = $this->model->get($ids);
if (!$row) {
$this->error(__('No Results were found'));
}
$adminIds = $this->getDataLimitAdminIds();
if (is_array($adminIds)) {
if (!in_array($row[$this->dataLimitField], $adminIds)) {
$this->error(__('You have no permission'));
}
}
if ($this->request->isPost()) {
$params = $this->request->post("row/a");
if ($params) {
$params = $this->preExcludeFields($params);
$result = false;
Db::startTrans();
try {
$params['newtable'] = '';
$fileData = $this->fileData($params);
$fileData['params'] = http_build_query($params);
$this->success('匹配到' . $fileData['count'] . '列,开始预览', '', $fileData);
Db::commit();
} catch (ValidateException $e) {
Db::rollback();
$this->error($e->getMessage());
} catch (PDOException $e) {
Db::rollback();
$this->error($e->getMessage());
} catch (Exception $e) {
Db::rollback();
$this->error($e->getMessage());
}
if ($result !== false) {
$this->success('设置成功', url('doimport', ['ids' => $row['id']]));
} else {
$this->error(__('No rows were updated'));
}
}
$this->error(__('Parameter %s can not be empty', ''));
}
$row['params'] = http_build_query(array(
'table' => $row['table'],
'row' => $row['row'],
'head_type' => $row['head_type'],
'path' => $row['path'],
));
$this->view->assign("row", $row);
return $this->view->fetch();
}
public function preview()
{
$params = $this->request->post("row/a");
if (!isset($params["path"])) {
$this->error(__('No Results were found'));
}
if ($this->request->isAjax()) {
$fileData = $this->fileData($params,true);
if (isset($params["columns"])) {
return json(array("code" => 1, "data" => $fileData['field']));
}
return json(["code" => 1, 'data' => ['field' => $fileData['field'], 'data' => $fileData['data'], 'fieldArr' => $fileData['fieldArr']]]);
}
}
protected function fileData($params,$preview = false)
{
$file = $path = $params['path'];
$row = $params['row'];
$sheet = 0;
if (!$file) {
$this->error(__('Parameter %s can not be empty', 'file'));
}
$filePath = ROOT_PATH . DS . 'public' . DS . $file;
if (!is_file($filePath)) {
$this->error(__('No results were found'));
}
//实例化reader
$ext = pathinfo($filePath, PATHINFO_EXTENSION);
if (!in_array($ext, ['csv', 'xls', 'xlsx'])) {
$this->error(__('Unknown data format'));
}
if ($ext === 'csv') {
$file = fopen($filePath, 'r');
$filePath = tempnam(sys_get_temp_dir(), 'import_csv');
$fp = fopen($filePath, "w");
$n = 0;
while ($line = fgets($file)) {
$line = rtrim($line, "\n\r\0");
$encoding = mb_detect_encoding($line, ['utf-8', 'gbk', 'latin1', 'big5']);
if ($encoding != 'utf-8') {
$line = mb_convert_encoding($line, 'utf-8', $encoding);
}
if ($n == 0 || preg_match('/^".*"$/', $line)) {
fwrite($fp, $line . "\n");
} else {
fwrite($fp, '"' . str_replace(['"', ','], ['""', '","'], $line) . "\"\n");
}
$n++;
}
fclose($file) || fclose($fp);
$reader = new Csv();
} elseif ($ext === 'xls') {
$reader = new Xls();
} else {
$reader = new Xlsx();
}
$prefix = Config::get('database.prefix');
//导入文件首行类型,默认是注释,如果需要使用字段名称请使用name
$importHeadType = $params['head_type'];
$table = $params['table'];
$database = \think\Config::get('database.database');
$fieldArr = [];
$notnull = [];
if (!$params['newtable']) {
$pk = Db::getTableInfo($table, 'pk');
$list = db()->query(
"SELECT COLUMN_NAME,COLUMN_COMMENT,COLUMN_TYPE,IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ? AND TABLE_SCHEMA = ?",
[$table, $database]
);
foreach ($list as $k => $v) {
if ($v['COLUMN_NAME'] !== $pk) {
if ($importHeadType == 'comment') {
if ($v['COLUMN_COMMENT']) {
$importField[] = $v['COLUMN_COMMENT'];
if ($v['IS_NULLABLE']) {
$notnull[] = $v['COLUMN_COMMENT'];
}
}
$fieldArr[$v['COLUMN_COMMENT']] = $v; //['COLUMN_NAME']
} else {
$importField[] = $v['COLUMN_NAME'];
if ($v['IS_NULLABLE']) {
$notnull[] = $v['COLUMN_NAME'];
}
$fieldArr[$v['COLUMN_NAME']] = $v; //['COLUMN_NAME']
}
}
}
}
$insert = [];
$allData = [];
$count = 0;
try {
if (!$PHPExcel = $reader->load($filePath)) {
$this->error(__('Unknown data format'));
}
$currentSheet = $PHPExcel->getSheet($sheet); //读取文件中的第一个工作表
$allColumn = $currentSheet->getHighestDataColumn(); //取得最大的列号
$allRow = $currentSheet->getHighestRow(); //取得一共有多少行
if($allRow > 100 || $preview){
$allRow = 10;
}
$maxColumnNumber = Coordinate::columnIndexFromString($allColumn);
$fields = [];
for ($currentRow = 1; $currentRow <= 1; $currentRow++) {
for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
$val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
$fields[] = $val;
$col[] = array(
'title' => $val,
'class' => isset($fieldArr[$val]) ? 'success' : '-',
'type' => isset($fieldArr[$val]) ? $fieldArr[$val]['COLUMN_TYPE'] : '--',
'field' => $val,
'fieldName' => isset($fieldArr[$val]) ? $fieldArr[$val]['COLUMN_NAME'] : '--' //Pinyin::get($val),
);
if (isset($fieldArr[$val])) {
$count += 1;
}
}
}
for ($currentRow = $row; $currentRow <= $allRow; $currentRow++) {
$values = [];
for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
$val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
$values[] = is_null($val) ? '' : $val;
}
$rows = [];
$all = [];
$temp = array_combine($fields, $values);
foreach ($temp as $k => $v) {
if (isset($fieldArr[$k]) && $k !== '') {
$rows[$fieldArr[$k]['COLUMN_NAME']] = $v;
}
$all[$k] = $v;
}
if ($rows) {
$insert[] = $rows;
}
$allData[] = $all;
}
return array(
'path' => $path,
'field' => $col,
'fieldArr' => $fieldArr,
'data' => $allData,
'insert' => $insert,
'excelField' => $fields,
'count' => $count
);
} catch (Exception $exception) {
$this->error($exception->getMessage());
}
}
}
前端代码部分写的不太精致,后续有时间完善后补充上传~
2.5报名数据
bootstraptable初始化的时候,加入默认筛选条件(渠道 id)
// 初始化表格
table.bootstrapTable({
url: $.fn.bootstrapTable.defaults.extend.index_url,
pk: 'id',
sortName: 'id',
fixedColumns: true,
fixedRightNumber: 1,
renderDefault: false,
queryParams:function(params){
var filter, op, qdid = Fast.api.query('qdid');
if(Config.qdid){
filter = JSON.parse(params.filter);
op = JSON.parse(params.op);
filter.qdid = Config.qdid;
op.qdid = '=';
params.filter = JSON.stringify(filter);
params.op = JSON.stringify(op);
}
return params;
},
columns: [
columns
]
});