FieldService.php 15 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443
  1. <?php
  2. namespace app\admin\service\sys_manage;
  3. use app\common\Util;
  4. use app\model\SysField;
  5. use Illuminate\Database\Schema\Blueprint;
  6. use Illuminate\Support\Facades\Schema;
  7. use support\Db;
  8. use support\Request;
  9. class FieldService
  10. {
  11. /**
  12. * @Desc 字段列表
  13. * @Author Gorden
  14. * @Date 2024/2/22 14:49
  15. *
  16. * @param $page
  17. * @param $limit
  18. * @param $keywords
  19. * @return \support\Response
  20. */
  21. public static function fieldList(Request $request)
  22. {
  23. $page = $request->get('page', 1);
  24. $pageSize = $request->get('pageSize', 10);
  25. $fieldName = $request->get('field_name', '');
  26. $tableName = $request->get('table_name', '');
  27. $rows = SysField::select('*')
  28. ->when($fieldName != '', function ($query) use ($fieldName) {
  29. $query->where('field_name', 'like', '%' . $fieldName . '%');
  30. })
  31. ->when($tableName != '', function ($query) use ($tableName) {
  32. $query->where('field_table', 'like', '%' . $tableName . '%');
  33. })
  34. ->orderBy('field_addtimes', 'DESC')
  35. ->forPage($page, $pageSize)
  36. ->get()
  37. ->toArray();
  38. $total = SysField::when($fieldName != '', function ($query) use ($fieldName) {
  39. $query->where('field_name', 'like', '%' . $fieldName . '%');
  40. })->when($tableName != '', function ($query) use ($tableName) {
  41. $query->where('field_table', 'like', '%' . $tableName . '%');
  42. })->count();
  43. foreach ($rows as &$row){
  44. $row['field_refer_json'] = json_decode($row['field_refer_json']);
  45. }
  46. return json_success('', compact('rows', 'page', 'pageSize', 'total'));
  47. }
  48. /**
  49. * @Desc 字段详情
  50. * @Author Gorden
  51. * @Date 2024/2/22 14:55
  52. *
  53. * @param $id
  54. * @return \support\Response
  55. */
  56. public static function fieldInfo($id)
  57. {
  58. $field = SysField::where('field_id', $id)->first();
  59. if (!$field) {
  60. return json_fail('字段不存在');
  61. }
  62. return json_success('', $field->toArray());
  63. }
  64. /**
  65. * @Desc 新建字段
  66. * @Author Gorden
  67. * @Date 2024/2/22 14:31
  68. *
  69. * @param $params
  70. * @return \support\Response
  71. */
  72. public static function addField($params)
  73. {
  74. if (!empty($params['field_refer_json']['key'])){
  75. $params['field_refer_json']['val'] = $params['field_refer_json']['key'];
  76. }
  77. Db::beginTransaction();
  78. try {
  79. $data = [
  80. 'field_status' => $params['field_status'],
  81. 'field_category' => $params['field_category'],
  82. 'field_name' => $params['field_name'],
  83. 'field_table' => $params['field_table'],
  84. 'field_column_key' => $params['field_column_key'] ?? '',
  85. 'field_column_datatype' => $params['field_column_datatype'] ?? '',
  86. 'field_column_default' => $params['field_column_default'] ?? '',
  87. 'field_form_key' => $params['field_form_key'] ?? '',
  88. 'field_form_type' => $params['field_form_type'] ?? '',
  89. 'field_form_default' => $params['field_form_default'] ?? '',
  90. 'field_refer_json' => !empty($params['field_refer_json']) ? json_encode($params['field_refer_json']) : [],
  91. 'field_remark' => $params['field_remark'] ?? '',
  92. 'field_extend_json' => !empty($params['field_extend_json']) ? $params['field_extend_json'] : [],
  93. 'field_addtimes' => time()
  94. ];
  95. // 入库
  96. SysField::insert($data);
  97. // 字段设置
  98. $column = [
  99. 'type' => $params['field_column_datatype'],
  100. 'field' => $params['field_column_key'],
  101. 'primary_key' => false,
  102. 'nullable' => true,
  103. 'default' => $params['field_column_default'],
  104. 'auto_increment' => false,
  105. 'comment' => $params['field_name']
  106. ];
  107. $defaultLength = Util::fieldDefaultLength();
  108. if (isset($defaultLength[$column['type']])) {
  109. $column['length'] = $defaultLength[$column['type']];
  110. }
  111. // 写入字段
  112. Util::schema()->table($params['field_table'], function (Blueprint $table) use ($column) {
  113. self::createColumn($table, $column);
  114. });
  115. Db::commit();
  116. } catch (\Exception $e) {
  117. Db::rollBack();
  118. return json_fail('创建字段失败');
  119. }
  120. return json_success('创建字段成功');
  121. }
  122. /**
  123. * @Desc 修改字段
  124. * @Author Gorden
  125. * @Date 2024/2/22 14:40
  126. *
  127. * @param $id
  128. * @param $params
  129. * @return \support\Response
  130. */
  131. public static function updateField($id, $params)
  132. {
  133. Db::beginTransaction();
  134. try {
  135. $field = SysField::where('field_id', $id)->first();
  136. $data = [
  137. 'field_category' => $params['field_category'],
  138. 'field_name' => $params['field_name'],
  139. 'field_table' => $params['field_table'],
  140. 'field_column_key' => $params['field_column_key'],
  141. 'field_column_datatype' => $params['field_column_datatype'],
  142. 'field_column_default' => $params['field_column_default'],
  143. 'field_form_key' => $params['field_form_key'],
  144. 'field_form_type' => $params['field_form_type'],
  145. 'field_form_default' => $params['field_form_default'],
  146. 'field_refer_json' => !empty($params['field_refer_json']) ? $params['field_refer_json'] : [],
  147. 'field_remark' => $params['field_remark'],
  148. 'field_extend_json' => !empty($params['field_extend_json']) ? $params['field_extend_json'] : []
  149. ];
  150. // 更新
  151. SysField::where('field_id', $id)->update($data);
  152. // 字段设置
  153. $column = [
  154. 'type' => $params['field_column_datatype'],
  155. 'field' => $params['field_column_key'],
  156. 'old_field' => $field->field_column_key,
  157. 'primary_key' => false,
  158. 'nullable' => true,
  159. 'default' => $params['field_column_default'],
  160. 'auto_increment' => false,
  161. 'comment' => $params['field_name']
  162. ];
  163. // 设置字段长度
  164. $defaultLength = Util::fieldDefaultLength();
  165. if (isset($defaultLength[$column['type']])) {
  166. $column['length'] = $defaultLength[$column['type']];
  167. }
  168. // 表变动
  169. if ($field->field_table != $params['field_table']) {
  170. if (!empty($field->field_table)){
  171. // 删原表字段
  172. Util::db()->statement("ALTER TABLE " . getenv('DB_PREFIX') . $field->field_table . " DROP COLUMN `$field->field_column_key`");
  173. }
  174. // 在新表创建
  175. Util::schema()->table($params['field_table'], function (Blueprint $table) use ($column) {
  176. self::createColumn($table, $column);
  177. });
  178. } else {
  179. // 表没变,改就行
  180. self::modifyColumn(getenv('DB_PREFIX') . $params['field_table'], $column);
  181. }
  182. Db::commit();
  183. } catch (\Exception $e) {
  184. Db::rollBack();
  185. // dump($e->getTrace());
  186. return json_fail('修改字段失败');
  187. }
  188. return json_success('修改字段成功');
  189. }
  190. /**
  191. * @Desc 修改字段状态
  192. * @Author Gorden
  193. * @Date 2024/2/22 15:06
  194. *
  195. * @param $id
  196. * @param $params
  197. * @return \support\Response
  198. */
  199. public static function updateStatus($id, $params)
  200. {
  201. try {
  202. SysField::where('field_id', $id)->update(['field_status' => $params['field_status']]);
  203. } catch (\Exception $e) {
  204. return json_fail('字段状态修改失败');
  205. }
  206. return json_success('字段状态修改成功');
  207. }
  208. /**
  209. * @Desc 删除字段
  210. * @Author Gorden
  211. * @Date 2024/2/22 15:08
  212. *
  213. * @param $id
  214. * @return \support\Response
  215. */
  216. public static function delField($ids)
  217. {
  218. Db::beginTransaction();
  219. try {
  220. if (!is_array($ids)) {
  221. $ids = [$ids];
  222. }
  223. foreach ($ids as $id) {
  224. $field = SysField::where('field_id', $id)->first();
  225. // 删除表记录
  226. $field->delete();
  227. // 删除表字段
  228. Util::db()->statement("ALTER TABLE " . getenv('DB_PREFIX') . $field->field_table . " DROP COLUMN `$field->field_column_key`");
  229. }
  230. // 提交事务
  231. Db::commit();
  232. } catch (\Exception $e) {
  233. // 回滚
  234. Db::rollBack();
  235. return json_fail('字段删除失败');
  236. }
  237. return json_success('字段删除成功');
  238. }
  239. public static function checkTableExist($table)
  240. {
  241. try {
  242. $table = getenv('DB_PREFIX') . $table;
  243. $desc = (new SysField)->getConnection()->select("desc `$table`");
  244. if ($desc) {
  245. return true;
  246. }
  247. } catch (\Exception $e) {
  248. return false;
  249. }
  250. }
  251. /**
  252. * @Desc 验证表字段是否存在
  253. * @Author Gorden
  254. * @Date 2024/2/23 11:49
  255. *
  256. * @param $table
  257. * @param $field
  258. * @return bool
  259. */
  260. public static function checkFieldExist($table, $field)
  261. {
  262. return SysField::where('field_table', $table)
  263. ->where('field_column_key', $field)
  264. ->exists();
  265. }
  266. /**
  267. * 创建字段
  268. * @param $column
  269. * @param Blueprint $table
  270. * @return mixed
  271. * @throws \Exception
  272. */
  273. public static function createColumn(Blueprint $table, $column)
  274. {
  275. $method = $column['type'];
  276. $args = [$column['field']];
  277. $type_method_map = Util::methodControlMap();
  278. if (!isset($column['type'])) {
  279. throw new \Exception("请为{$column['field']}选择类型");
  280. }
  281. if (!isset($type_method_map[$column['type']])) {
  282. throw new \Exception("不支持的类型{$column['type']}");
  283. }
  284. if (stripos($method, 'int') !== false) {
  285. // auto_increment 会自动成为主键
  286. if ($column['auto_increment']) {
  287. $column['nullable'] = false;
  288. $column['default'] = null;
  289. $args[] = true;
  290. }
  291. } elseif (in_array($method, ['string', 'char']) || stripos($method, 'time') !== false) {
  292. if ($column['length']) {
  293. $args[] = $column['length'];
  294. }
  295. } elseif ($method === 'enum') {
  296. $args[] = array_map('trim', explode(',', $column['length']));
  297. } elseif (in_array($method, ['float', 'decimal', 'double'])) {
  298. if ($column['length']) {
  299. $args = array_merge($args, array_map('trim', explode(',', $column['length'])));
  300. }
  301. } else {
  302. $column['auto_increment'] = false;
  303. }
  304. $column_def = call_user_func_array([$table, $method], $args);
  305. if (!empty($column['comment'])) {
  306. $column_def = $column_def->comment($column['comment']);
  307. }
  308. if (!$column['auto_increment'] && $column['primary_key']) {
  309. $column_def = $column_def->primary(true);
  310. }
  311. if ($column['auto_increment'] && !$column['primary_key']) {
  312. $column_def = $column_def->primary(false);
  313. }
  314. $column_def = $column_def->nullable($column['nullable']);
  315. if ($column['primary_key']) {
  316. $column_def = $column_def->nullable(false);
  317. }
  318. if ($method != 'text' && $column['default'] !== null) {
  319. $column_def->default($column['default']);
  320. }
  321. return $column_def;
  322. }
  323. /**
  324. * 更改字段
  325. * @param $column
  326. * @param $table
  327. * @return mixed
  328. * @throws \Exception
  329. */
  330. protected static function modifyColumn($table, $column)
  331. {
  332. $method = $column['type'];
  333. $field = $column['field'];
  334. $old_field = $column['old_field'] ?? null;
  335. $nullable = $column['nullable'];
  336. $default = $column['default'] !== null ? Util::pdoQuote($column['default']) : null;
  337. $comment = Util::pdoQuote($column['comment']);
  338. $auto_increment = $column['auto_increment'];
  339. $length = (int)$column['length'];
  340. if ($column['primary_key']) {
  341. $default = null;
  342. }
  343. if ($old_field && $old_field !== $field) {
  344. $sql = "ALTER TABLE `$table` CHANGE COLUMN `$old_field` `$field` ";
  345. } else {
  346. $sql = "ALTER TABLE `$table` MODIFY `$field` ";
  347. }
  348. if (stripos($method, 'integer') !== false) {
  349. $type = str_ireplace('integer', 'int', $method);
  350. if (stripos($method, 'unsigned') !== false) {
  351. $type = str_ireplace('unsigned', '', $type);
  352. $sql .= "$type ";
  353. $sql .= 'unsigned ';
  354. } else {
  355. $sql .= "$type ";
  356. }
  357. if ($auto_increment) {
  358. $column['nullable'] = false;
  359. $column['default'] = null;
  360. $sql .= 'AUTO_INCREMENT ';
  361. }
  362. } else {
  363. switch ($method) {
  364. case 'string':
  365. $length = $length ?: 255;
  366. $sql .= "varchar($length) ";
  367. break;
  368. case 'char':
  369. case 'time':
  370. $sql .= $length ? "$method($length) " : "$method ";
  371. break;
  372. case 'enum':
  373. $args = array_map('trim', explode(',', (string)$column['length']));
  374. foreach ($args as $key => $value) {
  375. $args[$key] = Util::pdoQuote($value);
  376. }
  377. $sql .= 'enum(' . implode(',', $args) . ') ';
  378. break;
  379. case 'double':
  380. case 'float':
  381. case 'decimal':
  382. if (trim($column['length'])) {
  383. $args = array_map('intval', explode(',', $column['length']));
  384. $args[1] = $args[1] ?? $args[0];
  385. $sql .= "$method($args[0], $args[1]) ";
  386. break;
  387. }
  388. $sql .= "$method ";
  389. break;
  390. default :
  391. $sql .= "$method ";
  392. }
  393. }
  394. if (!$nullable) {
  395. $sql .= "NOT NULL ";
  396. }
  397. if ($method != 'text' && $default !== null) {
  398. $sql .= "DEFAULT $default ";
  399. }
  400. if ($comment !== null) {
  401. $sql .= "COMMENT $comment ";
  402. }
  403. echo "$sql\n";
  404. Util::db()->statement($sql);
  405. }
  406. }