<?php
namespace app\admin\controller;
use app\common\controller\Backend;
use addons\fastexport\library\ExportLib;
use think\Db;
use think\Exception;
use think\exception\PDOException;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
/**
* 数据导出管理
*
* @icon fa fa-circle-o
*/
class Fastexport extends Backend
{
/**
* FastExport模型对象
* @var \app\admin\model\FastExport
*/
protected $model = null;
protected $database = [];
public $save_dir = RUNTIME_PATH . 'fastexport' . DS;// 临时文件保存位置(每次开始任务时清空)
public $fastExportDir;// ZIP包保存位置(重新初始化/删除任务时删除ZIP包)
public function _initialize()
{
parent::_initialize();
$rootPath = str_replace('/', DS, ROOT_PATH);
$this->model = new \app\admin\model\FastExport;
$this->database = config('database');
$this->fastExportDir = $rootPath . 'public' . DS . 'fastexport';
$tableList = [];
$list = Db::query("SELECT TABLE_NAME,TABLE_COMMENT FROM information_schema.TABLES WHERE table_schema = ? ", [$this->database['database']]);
// 需要排除的数据表
$rule_out_table = [
$this->database['prefix'] . 'config',
$this->database['prefix'] . 'version',
// $this->database['prefix'] . 'admin',
$this->database['prefix'] . 'user_token',
$this->database['prefix'] . 'fast_auth_group_access',
];
foreach ($list as $key => $row) {
if (!in_array($row['TABLE_NAME'], $rule_out_table)) {
$tableList[] = [
'name' => $row['TABLE_NAME'],
'comment' => $row['TABLE_COMMENT'] ? str_replace('表', '', $row['TABLE_COMMENT']) : '',
];
}
}
$this->view->assign("tableList", $tableList);
}
/**
* 删除
*/
public function del($ids = "")
{
if ($ids) {
$pk = $this->model->getPk();
$adminIds = $this->getDataLimitAdminIds();
if (is_array($adminIds)) {
$this->model->where($this->dataLimitField, 'in', $adminIds);
}
$list = $this->model->where($pk, 'in', $ids)->select();
$count = 0;
Db::startTrans();
try {
foreach ($list as $k => $v) {
// 同时删除临时文件和zip包
if (is_dir($this->save_dir . $v->id)) {
$this->emptydir($this->save_dir . $v->id, true);
}
if ($v->file) {
$file_name = explode(DS, str_replace('/', DS, $v->file));
if (file_exists($this->fastExportDir . DS . end($file_name))) {
unlink($this->fastExportDir . DS . end($file_name));
}
}
$count += $v->delete();
}
Db::commit();
} catch (PDOException $e) {
Db::rollback();
$this->error($e->getMessage());
} catch (Exception $e) {
Db::rollback();
$this->error($e->getMessage());
}
if ($count) {
$this->success();
} else {
$this->error(__('No rows were deleted'));
}
}
$this->error(__('Parameter %s can not be empty', 'ids'));
}
/**
* 测试任务-导出前10条数据
* @param int $ids [description]
*/
public function testTask($ids = null)
{
$row = $this->model->get($ids);
if (!$row) {
$this->error(__('No Results were found'));
}
$ExportLib = new ExportLib($row);
try {
$export_number = $row['export_number'] ? $row['export_number'] : $ExportLib->buildSql('count');
$xls_max_number = ($export_number >= $row['xls_max_number']) ? $row['xls_max_number'] : $export_number;
if (!$export_number) {
$this->error('没有数据需要导出~');
}
$min = 0;
$max = 10;
$subtask = [
1 => [
'id' => 1,
'status' => 0,// 0准备好，1进行中，2完成，3失败
'min' => $min,
'max' => $max,
'sql' => $ExportLib->buildSql('limit', [$min, $max]),
],
];
// 测试sql
Db::query($subtask[1]['sql']);
$row->subtask = $subtask;
$row->save();
} catch (PDOException $e) {
$this->error($e->getMessage());
} catch (Exception $e) {
$this->error($e->getMessage());
}
$this->success('导出任务测试准备好~', null, [
'direct_export' => true,
'subtask' => $subtask,
'id' => $ids,
]);
}
/**
* 开始任务->初始化任务配置
* @return bool
*/
public function performTask($ids = null)
{
$row = $this->model->get($ids);
if (!$row) {
$this->error(__('No Results were found'));
}
$ExportLib = new ExportLib($row);
try {
$export_number = $row['export_number'] ? $row['export_number'] : $ExportLib->buildSql('count');
$xls_max_number = ($export_number >= $row['xls_max_number']) ? $row['xls_max_number'] : $export_number;
if (!$export_number) {
$this->error('没有数据需要导出~');
}
$subtask = [];
$subtask_count = ceil($export_number / $xls_max_number);// 子任务数量
for ($i = 1; $i <= $subtask_count; $i++) {
if ($i == 1) {
$min = 0;
} else {
$min = ($i - 1) * $xls_max_number;
}
$subtask[$i] = [
'id' => $i,
'status' => 0,// 0准备好，1进行中，2完成，3失败
'min' => $min,
'max' => $xls_max_number,
'sql' => $ExportLib->buildSql('limit', [$min, $xls_max_number]),
];
// 测试sql
if ($i == 1) {
Db::query($subtask[$i]['sql']);
}
}
if ($subtask_count > 1) {
// 清理任务临时文件目录
if (!is_dir($this->save_dir . $ids)) {
mkdir($this->save_dir . $ids, 0777, true);
} else {
$this->emptydir($this->save_dir . $ids);
}
$row->progress = 5;
} else {
$row->progress = 0;
}
// 删除上次任务的zip包
if ($row->file) {
if ($row->file) {
$file_name = explode(DS, str_replace('/', DS, $row->file));
if (file_exists($this->fastExportDir . DS . end($file_name))) {
unlink($this->fastExportDir . DS . end($file_name));
}
}
$row->file = '';
}
$row->subtask = $subtask;
$row->save();
} catch (PDOException $e) {
$this->error($e->getMessage());
} catch (Exception $e) {
$this->error($e->getMessage());
}
$this->success('导出任务初始化成功！', null, [
'direct_export' => ($subtask_count == 1) ? true : false,
'subtask' => $subtask,
'id' => $ids,
]);
}
/**
* 子任务控制器
* @param [type] $task_id 任务ID
*/
public function taskControl($task_id = null)
{
$row = $this->model->get($task_id);
if (!$row) {
$this->error(__('任务找不到啦~'));
}
if (!isset($row['subtask']) || !is_array($row['subtask'])) {
$this->error('子任务找不到啦，请重新开始任务~');
}
$row = $row->toArray();
// 子任务状态:0准备好，1进行中，2完成，3失败
foreach ($row['subtask'] as $key => $value) {
switch ($value['status']) {
case '0':
$row['subtask'][$key]['status_text'] = '等待中';
break;
case '1':
$row['subtask'][$key]['status_text'] = '进行中';
break;
case '2':
$row['subtask'][$key]['status_text'] = '已完成';
break;
default:
$row['subtask'][$key]['status_text'] = '失败';
break;
}
}
$current_page = 1;
$subtask_page = [];
foreach ($row['subtask'] as $key => $value) {
$value['status'] = 0;// 用户可能会刷新任务控制页面，js将重新确定状态
$subtask_page[$current_page][] = $value;
if (count($subtask_page[$current_page]) >= $row['xls_create_concurrent']) {
$current_page++;
}
}
$subtask_count = count($row['subtask']);
$row['item_subtask_progress'] = round(92 / $subtask_count, 2);
$row['progress'] = 5;
$this->assignconfig('subtask_page', $subtask_page);
$this->assignconfig('task', $row);
$this->view->assign("task", $row);
return $this->view->fetch();
}
/**
* 执行自定义导出
* @param int $task_id 任务ID
* @param int $subtask_id 子任务ID
* @param boolean $direct_export 是否直接反回文件
*/
public function zdyTask()
{
$task_id = 1;
$subtask_id = 1;
$direct_export = true;
\think\Config::set('default_return_type', 'json');
$row = $this->model->get($task_id);
if (!$row) {
$this->error(__('No Results were found'));
}
if (isset($row['subtask'][$subtask_id]) && is_array($row['subtask'][$subtask_id])) {
$subtask = $row['subtask'][$subtask_id];
$task_name = $row['name'];
} else {
$this->result(['subtask_id' => $subtask_id], 0, '子任务找不到啦~', 'json');
}
set_time_limit(0);// 脚本执行时间限制
ini_set('memory_limit', $row['memory_limit'] . 'M');// 脚本内存限制
$ExportLib = new ExportLib($row);
unset($row);
// 检查任务状态
if (!$direct_export) {
if ($subtask['status'] == 1) {
$this->result(['subtask_id' => $subtask_id], 0, '此子任务正在执行中~', 'json');
} else if ($subtask['status'] == 2) {
if (file_exists($this->save_dir . $task_id . DS . $subtask_id . '.xlsx')) {
$this->result(['subtask_id' => $subtask_id], 1, '此子任务已经处理过啦~', 'json');
}
}
}
$spreadsheet = new Spreadsheet();
$worksheet = $spreadsheet->getActiveSheet();
$worksheet->setTitle($task_name);
//设置表头
$head = $ExportLib->getXlsTitle();
$head_count = count($head);
for ($i = 0; $i < $head_count; $i++) {
$worksheet->setCellValueByColumnAndRow($i + 1, 1, $head[$i]);
}
// 写人数据
try {
$data = Db::query($subtask['sql']);
} catch (PDOException $e) {
$this->result([
'subtask_id' => $subtask_id,
'error_msg' => $e->getMessage(),
], 0, '任务失败！', 'json');
}
$y = 2;
foreach ($data as $row_key => $row) {
$i = 1;
foreach ($ExportLib->fields as $key => $value) {
$field = $value['field'];
if ($value['discerns'] == 0) {
// 文本
$worksheet->setCellValueByColumnAndRow($i, $y, $row[$field], DataType::TYPE_STRING);
} else if ($value['discerns'] == 1) {
// 数字
$worksheet->setCellValueByColumnAndRow($i, $y, $row[$field] . "\t");
} else if ($value['discerns'] == 2) {
// 日期时间
if ($row[$field]) {
$excelDateValue = date('Y-m-d H:i:s', $row[$field]);
$worksheet->setCellValueByColumnAndRow($i, $y, $excelDateValue, DataType::TYPE_STRING);
/*$excelDateValue = PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel( $row[$field] );
if ($excelDateValue) {
// 以日期日期格式写入，没区别，有需要的可以打开
$worksheet->setCellValueByColumnAndRow($i, $y, $excelDateValue);
$worksheet->getStyleByColumnAndRow($i,$y)
->getNumberFormat()
->setFormatCode('yyyy-mm-dd h:mm:ss');
$worksheet->getStyleByColumnAndRow($i,$y)->getFont()->setBold(true);
$worksheet->getStyleByColumnAndRow($i,$y)->getFont()->setBold(false);
}*/
} else {
$worksheet->setCellValueByColumnAndRow($i, $y, '-', DataType::TYPE_STRING);
}
} else if ($value['discerns'] == 3) {
// 图片-太慢,太耗资源,弃用
/*$file_info = pathinfo($row[$field]);
$coorrow = $worksheet->getCellByColumnAndRow($i, $y)->getRow();
$coorcolumn = $worksheet->getCellByColumnAndRow($i, $y)->getColumn();
if ($y == 2) {
$worksheet->getColumnDimension($coorcolumn)->setWidth(8);
}
$worksheet->getRowDimension($coorrow)->setRowHeight(40);
if (!empty($file_info['basename'])) {
$basename = $file_info['basename'];
$drawing = new Drawing();
$drawing->setName('图片');
$drawing->setPath($remote_save_dir . $row[$field]);
$drawing->setWidth(40);
$drawing->setHeight(40);
$drawing->setCoordinates($coorcolumn . $coorrow);
$drawing->setWorksheet($worksheet);
} else {
$worksheet->setCellValueByColumnAndRow($i, $y, $row[$field], DataType::TYPE_STRING);
}*/
// 以链接导出图片
$field_value = cdnurl($row[$field], true);
$worksheet->setCellValueByColumnAndRow($i, $y, $field_value, DataType::TYPE_STRING);
$worksheet->getCellByColumnAndRow($i, $y)->getHyperlink()->setUrl($field_value);
} else if ($value['discerns'] == 4) {
// 文件
$field_value = cdnurl($row[$field], true);
$worksheet->setCellValueByColumnAndRow($i, $y, $field_value, DataType::TYPE_STRING);
$worksheet->getCellByColumnAndRow($i, $y)->getHyperlink()->setUrl($field_value);
} else if ($value['discerns'] == 5) {
// 赋值
$field_value = $ExportLib->assignment($row[$field], $value['scheme']);
$worksheet->setCellValueByColumnAndRow($i, $y, $field_value, DataType::TYPE_STRING);
}
$i++;
}
$y++;
unset($data[$row_key]); // 能节约一点内存
}
// xls文件处理
if ($direct_export) {
// 直接下载
ob_end_clean();
header("Pragma: public");
header("Expires: 0");
header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
header("Content-Type:application/force-download");
header("Content-Type:application/vnd.ms-execl");
header("Content-Type:application/octet-stream");
header("Content-Type:application/download");
$task_name = $task_id . '.' . $task_name . '.xlsx';
$encoded_filename = urlencode($task_name);
$ua = $_SERVER["HTTP_USER_AGENT"];
if (preg_match("/MSIE/", $ua)) {
header('Content-Disposition: attachment; filename="' . $encoded_filename . '"');
} else if (preg_match("/Firefox/", $ua)) {
header('Content-Disposition: attachment; filename*="utf8\'\'' . $task_name . '"');
} else {
header('Content-Disposition: attachment; filename="' . $task_name . '"');
}
header("Content-Transfer-Encoding:binary");
header('Cache-Control: max-age=0');// 禁止缓存
$writer = new Xlsx($spreadsheet);
$writer->save('php://output');
$spreadsheet->disconnectWorksheets();
unset($spreadsheet);
} else {
// 保存
$writer = new Xlsx($spreadsheet);
$writer->save($this->save_dir . $task_id . DS . $subtask_id . '.xlsx');
// 设置任务状态
$result = false;
Db::startTrans();
try {
// 读取最新的 subtask 数据
$row = $this->model->get($task_id);
$progress = $row->progress + round(92 / count($row['subtask']), 2);
$progress = ($progress > 100) ? 100 : $progress;
if (isset($row['subtask'][$subtask_id]) && is_array($row['subtask'][$subtask_id])) {
$subtask = $row['subtask'];
$subtask[$subtask_id]['status'] = 2;
$row->subtask = $subtask;
$row->progress = $progress;
$row->save();
$result = true;
}
Db::commit();
} catch (PDOException $e) {
Db::rollback();
$this->result([
'subtask_id' => $subtask_id,
'error_msg' => $e->getMessage(),
], 0, '任务失败！', 'json');
} catch (Exception $e) {
Db::rollback();
$this->result([
'subtask_id' => $subtask_id,
'error_msg' => $e->getMessage(),
], 0, '任务失败！', 'json');
}
$spreadsheet->disconnectWorksheets();
unset($spreadsheet);
if ($result) {
$this->result(['subtask_id' => $subtask_id], 1, '子任务处理成功！', 'json');
} else {
$this->result(['subtask_id' => $subtask_id], 0, '子任务找不到啦~', 'json');
}
}
}
/**
* 执行子任务
* @param int $task_id 任务ID
* @param int $subtask_id 子任务ID
* @param boolean $direct_export 是否直接反回文件
*/
public function performSubTask($task_id, $subtask_id, $direct_export = false)
{
\think\Config::set('default_return_type', 'json');
$row = $this->model->get($task_id);
if (!$row) {
$this->error(__('No Results were found'));
}
if (isset($row['subtask'][$subtask_id]) && is_array($row['subtask'][$subtask_id])) {
$subtask = $row['subtask'][$subtask_id];
$task_name = $row['name'];
} else {
$this->result(['subtask_id' => $subtask_id], 0, '子任务找不到啦~', 'json');
}
set_time_limit(0);// 脚本执行时间限制
ini_set('memory_limit', $row['memory_limit'] . 'M');// 脚本内存限制
$ExportLib = new ExportLib($row);
unset($row);
// 检查任务状态
if (!$direct_export) {
if ($subtask['status'] == 1) {
$this->result(['subtask_id' => $subtask_id], 0, '此子任务正在执行中~', 'json');
} else if ($subtask['status'] == 2) {
if (file_exists($this->save_dir . $task_id . DS . $subtask_id . '.xlsx')) {
$this->result(['subtask_id' => $subtask_id], 1, '此子任务已经处理过啦~', 'json');
}
}
}
$spreadsheet = new Spreadsheet();
$worksheet = $spreadsheet->getActiveSheet();
$worksheet->setTitle($task_name);
//设置表头
$head = $ExportLib->getXlsTitle();
$head_count = count($head);
for ($i = 0; $i < $head_count; $i++) {
$worksheet->setCellValueByColumnAndRow($i + 1, 1, $head[$i]);
}
// 写人数据
try {
$data = Db::query($subtask['sql']);
} catch (PDOException $e) {
$this->result([
'subtask_id' => $subtask_id,
'error_msg' => $e->getMessage(),
], 0, '任务失败！', 'json');
}
$y = 2;
foreach ($data as $row_key => $row) {
$i = 1;
foreach ($ExportLib->fields as $key => $value) {
$field = $value['field'];
if ($value['discerns'] == 0) {
// 文本
$worksheet->setCellValueByColumnAndRow($i, $y, $row[$field], DataType::TYPE_STRING);
} else if ($value['discerns'] == 1) {
// 数字
$worksheet->setCellValueByColumnAndRow($i, $y, $row[$field] . "\t");
} else if ($value['discerns'] == 2) {
// 日期时间
if ($row[$field]) {
$excelDateValue = date('Y-m-d H:i:s', $row[$field]);
$worksheet->setCellValueByColumnAndRow($i, $y, $excelDateValue, DataType::TYPE_STRING);
/*$excelDateValue = PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel( $row[$field] );
if ($excelDateValue) {
// 以日期日期格式写入，没区别，有需要的可以打开
$worksheet->setCellValueByColumnAndRow($i, $y, $excelDateValue);
$worksheet->getStyleByColumnAndRow($i,$y)
->getNumberFormat()
->setFormatCode('yyyy-mm-dd h:mm:ss');
$worksheet->getStyleByColumnAndRow($i,$y)->getFont()->setBold(true);
$worksheet->getStyleByColumnAndRow($i,$y)->getFont()->setBold(false);
}*/
} else {
$worksheet->setCellValueByColumnAndRow($i, $y, '-', DataType::TYPE_STRING);
}
} else if ($value['discerns'] == 3) {
// 图片-太慢,太耗资源,弃用
/*$file_info = pathinfo($row[$field]);
$coorrow = $worksheet->getCellByColumnAndRow($i, $y)->getRow();
$coorcolumn = $worksheet->getCellByColumnAndRow($i, $y)->getColumn();
if ($y == 2) {
$worksheet->getColumnDimension($coorcolumn)->setWidth(8);
}
$worksheet->getRowDimension($coorrow)->setRowHeight(40);
if (!empty($file_info['basename'])) {
$basename = $file_info['basename'];
$drawing = new Drawing();
$drawing->setName('图片');
$drawing->setPath($remote_save_dir . $row[$field]);
$drawing->setWidth(40);
$drawing->setHeight(40);
$drawing->setCoordinates($coorcolumn . $coorrow);
$drawing->setWorksheet($worksheet);
} else {
$worksheet->setCellValueByColumnAndRow($i, $y, $row[$field], DataType::TYPE_STRING);
}*/
// 以链接导出图片
$field_value = cdnurl($row[$field], true);
$worksheet->setCellValueByColumnAndRow($i, $y, $field_value, DataType::TYPE_STRING);
$worksheet->getCellByColumnAndRow($i, $y)->getHyperlink()->setUrl($field_value);
} else if ($value['discerns'] == 4) {
// 文件
$field_value = cdnurl($row[$field], true);
$worksheet->setCellValueByColumnAndRow($i, $y, $field_value, DataType::TYPE_STRING);
$worksheet->getCellByColumnAndRow($i, $y)->getHyperlink()->setUrl($field_value);
} else if ($value['discerns'] == 5) {
// 赋值
$field_value = $ExportLib->assignment($row[$field], $value['scheme']);
$worksheet->setCellValueByColumnAndRow($i, $y, $field_value, DataType::TYPE_STRING);
}
$i++;
}
$y++;
unset($data[$row_key]); // 能节约一点内存
}
// xls文件处理
if ($direct_export) {
// 直接下载
ob_end_clean();
header("Pragma: public");
header("Expires: 0");
header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
header("Content-Type:application/force-download");
header("Content-Type:application/vnd.ms-execl");
header("Content-Type:application/octet-stream");
header("Content-Type:application/download");
$task_name = $task_id . '.' . $task_name . '.xlsx';
$encoded_filename = urlencode($task_name);
$ua = $_SERVER["HTTP_USER_AGENT"];
if (preg_match("/MSIE/", $ua)) {
header('Content-Disposition: attachment; filename="' . $encoded_filename . '"');
} else if (preg_match("/Firefox/", $ua)) {
header('Content-Disposition: attachment; filename*="utf8\'\'' . $task_name . '"');
} else {
header('Content-Disposition: attachment; filename="' . $task_name . '"');
}
header("Content-Transfer-Encoding:binary");
header('Cache-Control: max-age=0');// 禁止缓存
$writer = new Xlsx($spreadsheet);
$writer->save('php://output');
$spreadsheet->disconnectWorksheets();
unset($spreadsheet);
} else {
// 保存
$writer = new Xlsx($spreadsheet);
$writer->save($this->save_dir . $task_id . DS . $subtask_id . '.xlsx');
// 设置任务状态
$result = false;
Db::startTrans();
try {
// 读取最新的 subtask 数据
$row = $this->model->get($task_id);
$progress = $row->progress + round(92 / count($row['subtask']), 2);
$progress = ($progress > 100) ? 100 : $progress;
if (isset($row['subtask'][$subtask_id]) && is_array($row['subtask'][$subtask_id])) {
$subtask = $row['subtask'];
$subtask[$subtask_id]['status'] = 2;
$row->subtask = $subtask;
$row->progress = $progress;
$row->save();
$result = true;
}
Db::commit();
} catch (PDOException $e) {
Db::rollback();
$this->result([
'subtask_id' => $subtask_id,
'error_msg' => $e->getMessage(),
], 0, '任务失败！', 'json');
} catch (Exception $e) {
Db::rollback();
$this->result([
'subtask_id' => $subtask_id,
'error_msg' => $e->getMessage(),
], 0, '任务失败！', 'json');
}
$spreadsheet->disconnectWorksheets();
unset($spreadsheet);
if ($result) {
$this->result(['subtask_id' => $subtask_id], 1, '子任务处理成功！', 'json');
} else {
$this->result(['subtask_id' => $subtask_id], 0, '子任务找不到啦~', 'json');
}
}
}
/**
* 打包ZIP
*/
public function taskFilePack($task_id)
{
$row = $this->model->get($task_id);
if (!$row) {
$this->error(__('No Results were found'));
}
if (!isset($row['subtask']) || !is_array($row['subtask'])) {
$this->result(null, 0, '打包失败，任务找不到啦~', 'json');
}
if ($row['file']) {
$this->result(['file_name' => $row['file']], 1, '此任务已经打过包了！', 'json');
}
is_dir($this->fastExportDir) or mkdir($this->fastExportDir, 0755, true);
foreach ($row['subtask'] as $key => $subtask) {
if (!file_exists($this->save_dir . $task_id . DS . $subtask['id'] . '.xlsx')) {
$this->result(null, 0, '子任务未处理完毕！', 'json');
}
}
$task_dir = $this->save_dir . $task_id . DS;// 导出任务的临时文件目录
$zipname = $task_id . '.export_' . \fast\Random::alnum(6) . '.zip';
$zipurl = request()->domain() . DS . 'fastexport' . DS . $zipname;// 绝对地址,以便各处直接点击下载
$zipname = $this->fastExportDir . DS . $zipname;
$zip = new \ZipArchive();
$res = $zip->open($zipname, \ZipArchive::CREATE);
if ($res === TRUE) {
$dh = opendir($task_dir);
while ($file = readdir($dh)) {
if ($file != "." && $file != "..") {
$fullpath = $task_dir . $file;
if (!is_dir($fullpath)) {
$zip->addFile($fullpath, $file);
}
}
}
closedir($dh);
}
$zip->close();
$row->file = $zipurl;
$row->progress = 100;
$row->save();
$this->result(['file_name' => $zipurl], 1, '文件打包完成！', 'json');
}
/**
* 获取字段列表、字段注释、表主键
* @internal
*/
public function getTableFields($table = null)
{
if ($this->request->isAjax()) {
$table = $this->request->request('table');
}
//从数据库中获取表字段信息
$sql = "SELECT * FROM `information_schema`.`columns` "
. "WHERE TABLE_SCHEMA = ? AND table_name = ? "
. "ORDER BY ORDINAL_POSITION";
//加载主表的列
$columnList = Db::query($sql, [$this->database['database'], $table]);
$fieldlist_comment = [];
foreach ($columnList as $index => $item) {
$fieldlist_comment[$item['COLUMN_NAME']] = $this->FieldComment($item['COLUMN_NAME'], $item['DATA_TYPE'], $item['COLUMN_COMMENT']);
}
if ($this->request->isAjax()) {
$this->success("", null, ['fieldlist_comment' => $fieldlist_comment]);
} else {
return $fieldlist_comment;
}
}
/**
* 字段分析
* @param String $field 字段名称
* @param String $data_type 字段数据类型
* @param String $comment 字段注释
* @return Array 可用的字段数据
*/
protected function FieldComment($field, $data_type, $comment)
{
// 字段类型识别
$data_type_int = [
'tinyint',
'int',
'smallint',
'mediumint',
'integer',
'bigint',
];
$discerns = 0;
if (in_array($data_type, $data_type_int)) {
$discerns = 1;// 数字
}
if (preg_match("/time$/i", $field)) {
$discerns = 2;// 日期时间
} else if (preg_match("/image$|avatar$/i", $field)) {
$discerns = 4;// 3为图片-导出慢弃用
} else if (preg_match("/file$/i", $field)) {
$discerns = 4;// 文件
}
/*else if (preg_match("/list$|data$|switch$/i", $field)) {
$discerns = 5;// 赋值
}*/
// 字段标题和赋值分析
if (!$comment) {
return [
'discerns' => $discerns,
'name' => $field,
'comment' => '',
];
}
$comment = str_replace('，', ',', $comment);
$comment = str_replace(['(多选)', '(单选)', '（多选）', '（单选）'], '', $comment);
if (stripos($comment, ':') !== false && stripos($comment, ',') && stripos($comment, '=') !== false) {
list($field_name, $field_comment) = explode(':', $comment);
return [
'discerns' => 5,
'name' => $field_name ? $field_name : $field,
'comment' => $field_comment,
];
} else {
if (preg_match("/switch$/i", $field)) {
$discerns = 5;// 赋值
$comment_temp = '0=关闭,1=开启';
}
return [
'discerns' => $discerns,
'name' => $comment ? $comment : $field,
'comment' => isset($comment_temp) ? $comment_temp : '',
];
}
}
/**
* 查看
*/
public function index()
{
//当前是否为关联查询
$this->relationSearch = true;
//设置过滤方法
$this->request->filter(['strip_tags']);
if ($this->request->isAjax()) {
//如果发送的来源是Selectpage，则转发到Selectpage
if ($this->request->request('keyField')) {
return $this->selectpage();
}
list($where, $sort, $order, $offset, $limit) = $this->buildparams();
$total = $this->model
->with(['admin'])
->where($where)
->order($sort, $order)
->count();
$list = $this->model
->with(['admin'])
->where($where)
->order($sort, $order)
->limit($offset, $limit)
->select();
foreach ($list as $row) {
$row->getRelation('admin')->visible(['username']);
}
$list = collection($list)->toArray();
$result = array("total" => $total, "rows" => $list);
return json($result);
}
return $this->view->fetch();
}
/**
* 添加
*/
public function add()
{
if ($this->request->isPost()) {
$params = $this->request->post("row/a");
if ($params) {
$params = method_exists($this, 'preExcludeFields') ? $this->preExcludeFields($params) : $params;
$params['admin_id'] = $this->auth->id;// 记录导出人
if (isset($params['where_field']['op']) && is_array($params['where_field']['op'])) {
foreach ($params['where_field']['op'] as $key => $value) {
$params['where_field']['op'][$key] = trim($value);
}
}
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);
}
$result = $this->model->allowField(true)->save($params);
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();
} else {
$this->error(__('No rows were inserted'));
}
}
$this->error(__('Parameter %s can not be empty', ''));
}
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 = method_exists($this, 'preExcludeFields') ? $this->preExcludeFields($params) : $params;
$params['join_table'] = isset($params['join_table']) ? $params['join_table'] : [];
$params['where_field'] = isset($params['where_field']) ? $params['where_field'] : [];
if (isset($params['where_field']['op']) && is_array($params['where_field']['op'])) {
foreach ($params['where_field']['op'] as $key => $value) {
$params['where_field']['op'][$key] = trim($value);
}
}
$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(true)->validate($validate);
}
$result = $row->allowField(true)->save($params);
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();
} else {
$this->error(__('No rows were updated'));
}
}
$this->error(__('Parameter %s can not be empty', ''));
}
$row = $row->toArray();
$row['join_table_number'] = count($row['join_table']);
// 源表字段列表
$table_field = $this->getTableFields($row['main_table']);
// 准备源表和关联表的所有字段-带表名-供筛选和排序
$allField = [];
foreach ($table_field as $key => $value) {
$field_name = $row['main_table'] . '.' . $key;
$value['field_name'] = $field_name . ' - ' . $value['name'];
$allField[$field_name] = $value;
}
// 要导出的字段
$fields = [];
if ($row['field_config'] && isset($row['field_config']['title'])) {
foreach ($row['field_config']['title'] as $key => $value) {
$fields[$key]['title'] = $value;
$fields[$key]['discerns'] = $row['field_config']['discerns'][$key];
$fields[$key]['scheme'] = $row['field_config']['scheme'][$key];
}
}
$row['fields'] = $fields;
// 源表和关联表的字段列表透传数据准备
$join_field_count = 0;
if (isset($row['join_table'])) {
foreach ($row['join_table'] as $key => $value) {
$join_table_name = isset($value['table']) ? $value['table'] : false;
if ($join_table_name) {
$row['join_table'][$key]['field_list'] = $join_table_field[$join_table_name] = $this->getTableFields($join_table_name);
$join_table_name_as = $value['join_as'] ? $value['join_as'] : $join_table_name;
foreach ($join_table_field[$join_table_name] as $join_field_key => $join_field_value) {
$field_name = $join_table_name_as . '.' . $join_field_key;
$join_field_value['field_name'] = $field_name . ' - ' . $join_field_value['name'];
$allField[$field_name] = $join_field_value;
}
}
// 关联表取值字段准备
$field_name_arr = [];
$field_name_arr_select = [];
if (isset($row['join_table'][$key]['fields']) && isset($row['join_table'][$key]['fields']['title'])) {
foreach ($row['join_table'][$key]['fields']['title'] as $fkey => $fvalue) {
$field_name_arr[$fkey]['title'] = $fvalue;
$field_name_arr[$fkey]['discerns'] = $row['join_table'][$key]['fields']['discerns'][$fkey];
$field_name_arr[$fkey]['scheme'] = $row['join_table'][$key]['fields']['scheme'][$fkey];
$field_name_arr_select[] = $fkey;
}
}
$row['join_table'][$key]['field_name_arr'] = $field_name_arr;
$row['join_table'][$key]['field_name_arr_select'] = $field_name_arr_select;// 供selectpicker初始化
$join_field_count += count($field_name_arr_select);
}
}
$row['where_field_arr_select'] = [];
$row['where_field_arr'] = [];
if ($row['where_field'] && isset($row['where_field']['op'])) {
foreach ($row['where_field']['op'] as $key => $value) {
$row['where_field_arr_select'][] = $key;
$row['where_field_arr'][$key] = [
'op' => $value,
'condition' => $row['where_field']['condition'][$key],
];
}
}
$this->assignconfig('fields', [
'table_field' => $table_field,
'join_table_field' => isset($join_table_field) ? $join_table_field : [],
'join_table_number' => $row['join_table_number'],
'field_count' => count($row['fields']) + $join_field_count,
]);
$this->view->assign("row", $row);
$this->view->assign("allField", $allField);
$this->view->assign("table_field", $table_field);
return $this->view->fetch();
}
public function emptydir($dir, $del_current_dir = false)
{
$dh = opendir($dir);
while ($file = readdir($dh)) {
if ($file != "." && $file != "..") {
$fullpath = $dir . DS . $file;
if (!is_dir($fullpath)) {
unlink($fullpath);
} else {
$this->emptydir($fullpath, true);
}
}
}
closedir($dh);
// 删除当前文件夹
if ($del_current_dir) {
rmdir($dir);
}
}
}