Backend.php 16 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481
  1. <?php
  2. namespace app\admin\library\traits;
  3. use app\admin\library\Auth;
  4. use Exception;
  5. use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
  6. use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
  7. use PhpOffice\PhpSpreadsheet\Reader\Xls;
  8. use PhpOffice\PhpSpreadsheet\Reader\Csv;
  9. use think\Db;
  10. use think\db\exception\BindParamException;
  11. use think\db\exception\DataNotFoundException;
  12. use think\db\exception\ModelNotFoundException;
  13. use think\exception\DbException;
  14. use think\exception\PDOException;
  15. use think\exception\ValidateException;
  16. use think\response\Json;
  17. trait Backend
  18. {
  19. /**
  20. * 排除前台提交过来的字段
  21. * @param $params
  22. * @return array
  23. */
  24. protected function preExcludeFields($params)
  25. {
  26. if (is_array($this->excludeFields)) {
  27. foreach ($this->excludeFields as $field) {
  28. if (array_key_exists($field, $params)) {
  29. unset($params[$field]);
  30. }
  31. }
  32. } else if (array_key_exists($this->excludeFields, $params)) {
  33. unset($params[$this->excludeFields]);
  34. }
  35. return $params;
  36. }
  37. /**
  38. * 查看
  39. *
  40. * @return string|Json
  41. * @throws \think\Exception
  42. * @throws DbException
  43. */
  44. public function index()
  45. {
  46. //设置过滤方法
  47. $this->request->filter(['strip_tags', 'trim']);
  48. if (false === $this->request->isAjax()) {
  49. return $this->view->fetch();
  50. }
  51. //如果发送的来源是 Selectpage,则转发到 Selectpage
  52. if ($this->request->request('keyField')) {
  53. return $this->selectpage();
  54. }
  55. [$where, $sort, $order, $offset, $limit] = $this->buildparams();
  56. $list = $this->model
  57. ->where($where)
  58. ->order($sort, $order)
  59. ->paginate($limit);
  60. $result = ['total' => $list->total(), 'rows' => $list->items()];
  61. return json($result);
  62. }
  63. /**
  64. * 回收站
  65. *
  66. * @return string|Json
  67. * @throws \think\Exception
  68. */
  69. public function recyclebin()
  70. {
  71. //设置过滤方法
  72. $this->request->filter(['strip_tags', 'trim']);
  73. if (false === $this->request->isAjax()) {
  74. return $this->view->fetch();
  75. }
  76. [$where, $sort, $order, $offset, $limit] = $this->buildparams();
  77. $list = $this->model
  78. ->onlyTrashed()
  79. ->where($where)
  80. ->order($sort, $order)
  81. ->paginate($limit);
  82. $result = ['total' => $list->total(), 'rows' => $list->items()];
  83. return json($result);
  84. }
  85. /**
  86. * 添加
  87. *
  88. * @return string
  89. * @throws \think\Exception
  90. */
  91. public function add()
  92. {
  93. if (false === $this->request->isPost()) {
  94. return $this->view->fetch();
  95. }
  96. $params = $this->request->post('row/a');
  97. if (empty($params)) {
  98. $this->error(__('Parameter %s can not be empty', ''));
  99. }
  100. $params = $this->preExcludeFields($params);
  101. if ($this->dataLimit && $this->dataLimitFieldAutoFill) {
  102. $params[$this->dataLimitField] = $this->auth->id;
  103. }
  104. $result = false;
  105. Db::startTrans();
  106. try {
  107. //是否采用模型验证
  108. if ($this->modelValidate) {
  109. $name = str_replace("\\model\\", "\\validate\\", get_class($this->model));
  110. $validate = is_bool($this->modelValidate) ? ($this->modelSceneValidate ? $name . '.add' : $name) : $this->modelValidate;
  111. $this->model->validateFailException()->validate($validate);
  112. }
  113. $result = $this->model->allowField(true)->save($params);
  114. Db::commit();
  115. } catch (ValidateException|PDOException|Exception $e) {
  116. Db::rollback();
  117. $this->error($e->getMessage());
  118. }
  119. if ($result === false) {
  120. $this->error(__('No rows were inserted'));
  121. }
  122. $this->success();
  123. }
  124. /**
  125. * 编辑
  126. *
  127. * @param $ids
  128. * @return string
  129. * @throws DbException
  130. * @throws \think\Exception
  131. */
  132. public function edit($ids = null)
  133. {
  134. $row = $this->model->get($ids);
  135. if (!$row) {
  136. $this->error(__('No Results were found'));
  137. }
  138. $adminIds = $this->getDataLimitAdminIds();
  139. if (is_array($adminIds) && !in_array($row[$this->dataLimitField], $adminIds)) {
  140. $this->error(__('You have no permission'));
  141. }
  142. if (false === $this->request->isPost()) {
  143. $this->view->assign('row', $row);
  144. return $this->view->fetch();
  145. }
  146. $params = $this->request->post('row/a');
  147. if (empty($params)) {
  148. $this->error(__('Parameter %s can not be empty', ''));
  149. }
  150. $params = $this->preExcludeFields($params);
  151. $result = false;
  152. Db::startTrans();
  153. try {
  154. //是否采用模型验证
  155. if ($this->modelValidate) {
  156. $name = str_replace("\\model\\", "\\validate\\", get_class($this->model));
  157. $validate = is_bool($this->modelValidate) ? ($this->modelSceneValidate ? $name . '.edit' : $name) : $this->modelValidate;
  158. $row->validateFailException()->validate($validate);
  159. }
  160. $result = $row->allowField(true)->save($params);
  161. Db::commit();
  162. } catch (ValidateException|PDOException|Exception $e) {
  163. Db::rollback();
  164. $this->error($e->getMessage());
  165. }
  166. if (false === $result) {
  167. $this->error(__('No rows were updated'));
  168. }
  169. $this->success();
  170. }
  171. /**
  172. * 删除
  173. *
  174. * @param $ids
  175. * @return void
  176. * @throws DbException
  177. * @throws DataNotFoundException
  178. * @throws ModelNotFoundException
  179. */
  180. public function del($ids = null)
  181. {
  182. if (false === $this->request->isPost()) {
  183. $this->error(__("Invalid parameters"));
  184. }
  185. $ids = $ids ?: $this->request->post("ids");
  186. if (empty($ids)) {
  187. $this->error(__('Parameter %s can not be empty', 'ids'));
  188. }
  189. $pk = $this->model->getPk();
  190. $adminIds = $this->getDataLimitAdminIds();
  191. if (is_array($adminIds)) {
  192. $this->model->where($this->dataLimitField, 'in', $adminIds);
  193. }
  194. $list = $this->model->where($pk, 'in', $ids)->select();
  195. $count = 0;
  196. Db::startTrans();
  197. try {
  198. foreach ($list as $item) {
  199. $count += $item->delete();
  200. }
  201. Db::commit();
  202. } catch (PDOException|Exception $e) {
  203. Db::rollback();
  204. $this->error($e->getMessage());
  205. }
  206. if ($count) {
  207. $this->success();
  208. }
  209. $this->error(__('No rows were deleted'));
  210. }
  211. /**
  212. * 真实删除
  213. *
  214. * @param $ids
  215. * @return void
  216. */
  217. public function destroy($ids = null)
  218. {
  219. if (false === $this->request->isPost()) {
  220. $this->error(__("Invalid parameters"));
  221. }
  222. $ids = $ids ?: $this->request->post('ids');
  223. $pk = $this->model->getPk();
  224. $adminIds = $this->getDataLimitAdminIds();
  225. if (is_array($adminIds)) {
  226. $this->model->where($this->dataLimitField, 'in', $adminIds);
  227. }
  228. if ($ids) {
  229. $this->model->where($pk, 'in', $ids);
  230. }
  231. $count = 0;
  232. Db::startTrans();
  233. try {
  234. $list = $this->model->onlyTrashed()->select();
  235. foreach ($list as $item) {
  236. $count += $item->delete(true);
  237. }
  238. Db::commit();
  239. } catch (PDOException|Exception $e) {
  240. Db::rollback();
  241. $this->error($e->getMessage());
  242. }
  243. if ($count) {
  244. $this->success();
  245. }
  246. $this->error(__('No rows were deleted'));
  247. }
  248. /**
  249. * 还原
  250. *
  251. * @param $ids
  252. * @return void
  253. */
  254. public function restore($ids = null)
  255. {
  256. if (false === $this->request->isPost()) {
  257. $this->error(__('Invalid parameters'));
  258. }
  259. $ids = $ids ?: $this->request->post('ids');
  260. $pk = $this->model->getPk();
  261. $adminIds = $this->getDataLimitAdminIds();
  262. if (is_array($adminIds)) {
  263. $this->model->where($this->dataLimitField, 'in', $adminIds);
  264. }
  265. if ($ids) {
  266. $this->model->where($pk, 'in', $ids);
  267. }
  268. $count = 0;
  269. Db::startTrans();
  270. try {
  271. $list = $this->model->onlyTrashed()->select();
  272. foreach ($list as $item) {
  273. $count += $item->restore();
  274. }
  275. Db::commit();
  276. } catch (PDOException|Exception $e) {
  277. Db::rollback();
  278. $this->error($e->getMessage());
  279. }
  280. if ($count) {
  281. $this->success();
  282. }
  283. $this->error(__('No rows were updated'));
  284. }
  285. /**
  286. * 批量更新
  287. *
  288. * @param $ids
  289. * @return void
  290. */
  291. public function multi($ids = null)
  292. {
  293. if (false === $this->request->isPost()) {
  294. $this->error(__('Invalid parameters'));
  295. }
  296. $ids = $ids ?: $this->request->post('ids');
  297. if (empty($ids)) {
  298. $this->error(__('Parameter %s can not be empty', 'ids'));
  299. }
  300. if (false === $this->request->has('params')) {
  301. $this->error(__('No rows were updated'));
  302. }
  303. parse_str($this->request->post('params'), $values);
  304. $values = $this->auth->isSuperAdmin() ? $values : array_intersect_key($values, array_flip(is_array($this->multiFields) ? $this->multiFields : explode(',', $this->multiFields)));
  305. if (empty($values)) {
  306. $this->error(__('You have no permission'));
  307. }
  308. $adminIds = $this->getDataLimitAdminIds();
  309. if (is_array($adminIds)) {
  310. $this->model->where($this->dataLimitField, 'in', $adminIds);
  311. }
  312. $count = 0;
  313. Db::startTrans();
  314. try {
  315. $list = $this->model->where($this->model->getPk(), 'in', $ids)->select();
  316. foreach ($list as $item) {
  317. $count += $item->allowField(true)->isUpdate(true)->save($values);
  318. }
  319. Db::commit();
  320. } catch (PDOException|Exception $e) {
  321. Db::rollback();
  322. $this->error($e->getMessage());
  323. }
  324. if ($count) {
  325. $this->success();
  326. }
  327. $this->error(__('No rows were updated'));
  328. }
  329. /**
  330. * 导入
  331. *
  332. * @return void
  333. * @throws PDOException
  334. * @throws BindParamException
  335. */
  336. protected function import()
  337. {
  338. $file = $this->request->request('file');
  339. if (!$file) {
  340. $this->error(__('Parameter %s can not be empty', 'file'));
  341. }
  342. $filePath = ROOT_PATH . DS . 'public' . DS . $file;
  343. if (!is_file($filePath)) {
  344. $this->error(__('No results were found'));
  345. }
  346. //实例化reader
  347. $ext = pathinfo($filePath, PATHINFO_EXTENSION);
  348. if (!in_array($ext, ['csv', 'xls', 'xlsx'])) {
  349. $this->error(__('Unknown data format'));
  350. }
  351. if ($ext === 'csv') {
  352. $file = fopen($filePath, 'r');
  353. $filePath = tempnam(sys_get_temp_dir(), 'import_csv');
  354. $fp = fopen($filePath, 'w');
  355. $n = 0;
  356. while ($line = fgets($file)) {
  357. $line = rtrim($line, "\n\r\0");
  358. $encoding = mb_detect_encoding($line, ['utf-8', 'gbk', 'latin1', 'big5']);
  359. if ($encoding !== 'utf-8') {
  360. $line = mb_convert_encoding($line, 'utf-8', $encoding);
  361. }
  362. if ($n == 0 || preg_match('/^".*"$/', $line)) {
  363. fwrite($fp, $line . "\n");
  364. } else {
  365. fwrite($fp, '"' . str_replace(['"', ','], ['""', '","'], $line) . "\"\n");
  366. }
  367. $n++;
  368. }
  369. fclose($file) || fclose($fp);
  370. $reader = new Csv();
  371. } elseif ($ext === 'xls') {
  372. $reader = new Xls();
  373. } else {
  374. $reader = new Xlsx();
  375. }
  376. //导入文件首行类型,默认是注释,如果需要使用字段名称请使用name
  377. $importHeadType = isset($this->importHeadType) ? $this->importHeadType : 'comment';
  378. $table = $this->model->getQuery()->getTable();
  379. $database = \think\Config::get('database.database');
  380. $fieldArr = [];
  381. $list = db()->query("SELECT COLUMN_NAME,COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ? AND TABLE_SCHEMA = ?", [$table, $database]);
  382. foreach ($list as $k => $v) {
  383. if ($importHeadType == 'comment') {
  384. $v['COLUMN_COMMENT'] = explode(':', $v['COLUMN_COMMENT'])[0]; //字段备注有:时截取
  385. $fieldArr[$v['COLUMN_COMMENT']] = $v['COLUMN_NAME'];
  386. } else {
  387. $fieldArr[$v['COLUMN_NAME']] = $v['COLUMN_NAME'];
  388. }
  389. }
  390. //加载文件
  391. $insert = [];
  392. try {
  393. if (!$PHPExcel = $reader->load($filePath)) {
  394. $this->error(__('Unknown data format'));
  395. }
  396. $currentSheet = $PHPExcel->getSheet(0); //读取文件中的第一个工作表
  397. $allColumn = $currentSheet->getHighestDataColumn(); //取得最大的列号
  398. $allRow = $currentSheet->getHighestRow(); //取得一共有多少行
  399. $maxColumnNumber = Coordinate::columnIndexFromString($allColumn);
  400. $fields = [];
  401. for ($currentRow = 1; $currentRow <= 1; $currentRow++) {
  402. for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
  403. $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
  404. $fields[] = $val;
  405. }
  406. }
  407. for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {
  408. $values = [];
  409. for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
  410. $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
  411. $values[] = is_null($val) ? '' : $val;
  412. }
  413. $row = [];
  414. $temp = array_combine($fields, $values);
  415. foreach ($temp as $k => $v) {
  416. if (isset($fieldArr[$k]) && $k !== '') {
  417. $row[$fieldArr[$k]] = $v;
  418. }
  419. }
  420. if ($row) {
  421. $insert[] = $row;
  422. }
  423. }
  424. } catch (Exception $exception) {
  425. $this->error($exception->getMessage());
  426. }
  427. if (!$insert) {
  428. $this->error(__('No rows were updated'));
  429. }
  430. try {
  431. //是否包含admin_id字段
  432. $has_admin_id = false;
  433. foreach ($fieldArr as $name => $key) {
  434. if ($key == 'admin_id') {
  435. $has_admin_id = true;
  436. break;
  437. }
  438. }
  439. if ($has_admin_id) {
  440. $auth = Auth::instance();
  441. foreach ($insert as &$val) {
  442. if (empty($val['admin_id'])) {
  443. $val['admin_id'] = $auth->isLogin() ? $auth->id : 0;
  444. }
  445. }
  446. }
  447. $this->model->saveAll($insert);
  448. } catch (PDOException $exception) {
  449. $msg = $exception->getMessage();
  450. if (preg_match("/.+Integrity constraint violation: 1062 Duplicate entry '(.+)' for key '(.+)'/is", $msg, $matches)) {
  451. $msg = "导入失败,包含【{$matches[1]}】的记录已存在";
  452. };
  453. $this->error($msg);
  454. } catch (Exception $e) {
  455. $this->error($e->getMessage());
  456. }
  457. $this->success();
  458. }
  459. }