wanyue_app/app/admin/service/sys_manage/FieldService.php

<?php

namespace app\admin\service\sys_manage;

use app\common\Util;
use app\model\SysField;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
use support\Db;
use support\Request;

class FieldService
{
    /**
     * @Desc 字段列表
     * @Author Gorden
     * @Date 2024/2/22 14:49
     *
     * @param $page
     * @param $limit
     * @param $keywords
     * @return \support\Response
     */
    public static function fieldList(Request $request)
    {
        $page = $request->get('page', 1);
        $pageSize = $request->get('pageSize', 10);
        $fieldName = $request->get('field_name', '');
        $tableName = $request->get('table_name', '');
        $fieldCategory = $request->get('field_category', '');

        $rows = SysField::select('*')
            ->when($fieldName != '', function ($query) use ($fieldName) {
                $query->where('field_name', 'like', '%' . $fieldName . '%');
            })->when($tableName != '', function ($query) use ($tableName) {
                $query->where('field_table', 'like', '%' . $tableName . '%');
            })->when($fieldCategory != '', function ($query) use ($fieldCategory) {
                $query->where('field_category', $fieldCategory);
            })->orderBy('field_addtimes', 'DESC')
            ->forPage($page, $pageSize)
            ->get()
            ->toArray();
        $total = SysField::when($fieldName != '', function ($query) use ($fieldName) {
            $query->where('field_name', 'like', '%' . $fieldName . '%');
        })->when($tableName != '', function ($query) use ($tableName) {
            $query->where('field_table', 'like', '%' . $tableName . '%');
        })->count();

        foreach ($rows as &$row) {
            $row['field_refer_json'] = json_decode($row['field_refer_json']);
        }

        return json_success('', compact('rows', 'page', 'pageSize', 'total'));
    }

    /**
     * @Desc 字段详情
     * @Author Gorden
     * @Date 2024/2/22 14:55
     *
     * @param $id
     * @return \support\Response
     */
    public static function fieldInfo($id)
    {
        $field = SysField::where('field_id', $id)->first();
        if (!$field) {
            return json_fail('字段不存在');
        }

        return json_success('', $field->toArray());
    }

    /**
     * @Desc 新建字段
     * @Author Gorden
     * @Date 2024/2/22 14:31
     *
     * @param $params
     * @return \support\Response
     */
    public static function addField($params)
    {
        if (!empty($params['field_refer_json']['key'])) {
            $params['field_refer_json']['val'] = $params['field_refer_json']['key'];
        }
        Db::beginTransaction();
        try {
            $data = [
                'field_status' => $params['field_status'],
                'field_category' => $params['field_category'],
                'field_name' => $params['field_name'],
                'field_table' => $params['field_table'],
                'field_column_key' => $params['field_column_key'] ?? '',
                'field_column_datatype' => $params['field_column_datatype'] ?? '',
                'field_column_default' => $params['field_column_default'] ?? '',
                'field_form_key' => $params['field_form_key'] ?? '',
                'field_form_type' => $params['field_form_type'] ?? '',
                'field_form_default' => $params['field_form_default'] ?? '',
                'field_refer_json' => !empty($params['field_refer_json']) ? json_encode($params['field_refer_json']) : [],
                'field_remark' => $params['field_remark'] ?? '',
                'field_extend_json' => !empty($params['field_extend_json']) ? $params['field_extend_json'] : [],
                'field_addtimes' => time()
            ];
            // 入库
            SysField::insert($data);

            if (!empty($params['field_table'])) {
                // 字段设置
                $column = [
                    'type' => self::$column[$params['field_column_datatype']],
                    'field' => $params['field_column_key'],
                    'primary_key' => false,
                    'nullable' => true,
                    'default' => $params['field_column_default'],
                    'auto_increment' => false,
                    'comment' => $params['field_name']
                ];
                $defaultLength = Util::fieldDefaultLength();
                if (isset($defaultLength[$column['type']])) {
                    $column['length'] = $defaultLength[$column['type']];
                }
                // 写入字段
                Util::schema()->table($params['field_table'], function (Blueprint $table) use ($column) {
                    self::createColumn($table, $column);
                });
            }
            Db::commit();
        } catch (\Exception $e) {
            Db::rollBack();
            return json_fail('创建字段失败');
        }

        return json_success('创建字段成功');
    }

    /**
     * @Desc 修改字段
     * @Author Gorden
     * @Date 2024/2/22 14:40
     *
     * @param $id
     * @param $params
     * @return \support\Response
     */
    public static function updateField($id, $params)
    {
        Db::beginTransaction();
        try {
            $field = SysField::where('field_id', $id)->first();
            $data = [
                'field_category' => $params['field_category'],
                'field_name' => $params['field_name'],
                'field_table' => $params['field_table'],
                'field_column_key' => $params['field_column_key'],
                'field_column_datatype' => $params['field_column_datatype'],
                'field_column_default' => $params['field_column_default'],
                'field_form_key' => $params['field_form_key'],
                'field_form_type' => $params['field_form_type'],
                'field_form_default' => $params['field_form_default'],
                'field_refer_json' => !empty($params['field_refer_json']) ? json_encode($params['field_refer_json']) : '[]',
                'field_remark' => $params['field_remark'],
//                'field_extend_json' => !empty($params['field_extend_json']) ? $params['field_extend_json'] : '[]'
            ];
            // 更新
            SysField::where('field_id', $id)->update($data);
            if (!empty($params['field_table'])) {
                // 字段设置
                $column = [
                    'type' => self::$column[$params['field_column_datatype']],
                    'field' => $params['field_column_key'],
                    'old_field' => $field->field_column_key,
                    'primary_key' => false,
                    'nullable' => true,
                    'default' => $params['field_column_default'],
                    'auto_increment' => false,
                    'comment' => $params['field_name']
                ];
                // 设置字段长度
                $defaultLength = Util::fieldDefaultLength();
                if (isset($defaultLength[$column['type']])) {
                    $column['length'] = $defaultLength[$column['type']];
                }
                // 表变动
                if ($field->field_table != $params['field_table']) {
                    if (!empty($field->field_table)) {
                        // 删原表字段
                        Util::db()->statement("ALTER TABLE " . getenv('DB_PREFIX') . $field->field_table . " DROP COLUMN `$field->field_column_key`");
                    }
                    // 在新表创建
                    Util::schema()->table($params['field_table'], function (Blueprint $table) use ($column) {
                        self::createColumn($table, $column);
                    });
                } else {
                    // 表没变，改就行
                    self::modifyColumn($params['field_table'], $column);
                }
            }
            Db::commit();
        } catch (\Exception $e) {
            Db::rollBack();
            dump($e->getMessage());
            return json_fail('修改字段失败');
        }

        return json_success('修改字段成功');
    }

    /**
     * @Desc 修改字段状态
     * @Author Gorden
     * @Date 2024/2/22 15:06
     *
     * @param $id
     * @param $params
     * @return \support\Response
     */
    public static function updateStatus($id, $params)
    {
        try {
            SysField::where('field_id', $id)->update(['field_status' => $params['field_status']]);
        } catch (\Exception $e) {
            return json_fail('字段状态修改失败');
        }

        return json_success('字段状态修改成功');
    }

    /**
     * @Desc 删除字段
     * @Author Gorden
     * @Date 2024/2/22 15:08
     *
     * @param $id
     * @return \support\Response
     */
    public static function delField($ids)
    {
        Db::beginTransaction();
        try {
            if (!is_array($ids)) {
                $ids = [$ids];
            }
            foreach ($ids as $id) {
                $field = SysField::where('field_id', $id)->first();
                // 删除表记录
                $field->delete();
                // 删除表字段
                Util::db()->statement("ALTER TABLE " . getenv('DB_PREFIX') . $field->field_table . " DROP COLUMN `$field->field_column_key`");
            }

            // 提交事务
            Db::commit();
        } catch (\Exception $e) {
            // 回滚
            Db::rollBack();
            return json_fail('字段删除失败');
        }

        return json_success('字段删除成功');
    }

    public static function checkTableExist($table)
    {
        if (empty($table)) {
            return true;
        }

        try {
//            $table = $table;
            $desc = (new SysField)->getConnection()->select("desc `$table`");
            if ($desc) {
                return true;
            }
        } catch (\Exception $e) {
            return false;
        }
    }

    /**
     * @Desc 验证表字段是否存在
     * @Author Gorden
     * @Date 2024/2/23 11:49
     *
     * @param $table
     * @param $field
     * @return bool
     */
    public static function checkFieldExist($table, $field)
    {
        if (empty($table)) {
            return true;
        }
        return SysField::where('field_table', $table)
            ->where('field_column_key', $field)
            ->exists();
    }

    /**
     * 创建字段
     * @param $column
     * @param Blueprint $table
     * @return mixed
     * @throws \Exception
     */
    public static function createColumn(Blueprint $table, $column)
    {
        $method = $column['type'];
        $args = [$column['field']];
        $type_method_map = Util::methodControlMap();
        if (!isset($column['type'])) {
            throw new \Exception("请为{$column['field']}选择类型");
        }
        if (!isset($type_method_map[$column['type']])) {
            throw new \Exception("不支持的类型{$column['type']}");
        }
        if (stripos($method, 'int') !== false) {
            // auto_increment 会自动成为主键
            if ($column['auto_increment']) {
                $column['nullable'] = false;
                $column['default'] = null;
                $args[] = true;
            }
        } elseif (in_array($method, ['string', 'char']) || stripos($method, 'time') !== false) {
            if ($column['length']) {
                $args[] = $column['length'];
            }
        } elseif ($method === 'enum') {
            $args[] = array_map('trim', explode(',', $column['length']));
        } elseif (in_array($method, ['float', 'decimal', 'double'])) {
            if ($column['length']) {
                $args = array_merge($args, array_map('trim', explode(',', $column['length'])));
            }
        } else {
            $column['auto_increment'] = false;
        }

        $column_def = call_user_func_array([$table, $method], $args);
        if (!empty($column['comment'])) {
            $column_def = $column_def->comment($column['comment']);
        }

        if (!$column['auto_increment'] && $column['primary_key']) {
            $column_def = $column_def->primary(true);
        }

        if ($column['auto_increment'] && !$column['primary_key']) {
            $column_def = $column_def->primary(false);
        }
        $column_def = $column_def->nullable($column['nullable']);

        if ($column['primary_key']) {
            $column_def = $column_def->nullable(false);
        }

        if ($method != 'text' && $column['default'] !== null) {
            $column_def->default($column['default']);
        }
        return $column_def;
    }

    /**
     * 更改字段
     * @param $column
     * @param $table
     * @return mixed
     * @throws \Exception
     */
    protected static function modifyColumn($table, $column)
    {
        $method = $column['type'];
        $field = $column['field'];
        $old_field = $column['old_field'] ?? null;
        $nullable = $column['nullable'];
        $default = $column['default'] !== null ? Util::pdoQuote($column['default']) : null;
        $comment = Util::pdoQuote($column['comment']);
        $auto_increment = $column['auto_increment'];
        $length = (int)$column['length'];

        if ($column['primary_key']) {
            $default = null;
        }

        if ($old_field && $old_field !== $field) {
            $sql = "ALTER TABLE `$table` CHANGE COLUMN `$old_field` `$field` ";
        } else {
            $sql = "ALTER TABLE `$table` MODIFY `$field` ";
        }

        if (stripos($method, 'integer') !== false) {
            $type = str_ireplace('integer', 'int', $method);
            if (stripos($method, 'unsigned') !== false) {
                $type = str_ireplace('unsigned', '', $type);
                $sql .= "$type ";
                $sql .= 'unsigned ';
            } else {
                $sql .= "$type ";
            }
            if ($auto_increment) {
                $column['nullable'] = false;
                $column['default'] = null;
                $sql .= 'AUTO_INCREMENT ';
            }
        } else {
            switch ($method) {
                case 'string':
                    $length = $length ?: 255;
                    $sql .= "varchar($length) ";
                    break;
                case 'char':
                case 'time':
                    $sql .= $length ? "$method($length) " : "$method ";
                    break;
                case 'enum':
                    $args = array_map('trim', explode(',', (string)$column['length']));
                    foreach ($args as $key => $value) {
                        $args[$key] = Util::pdoQuote($value);
                    }
                    $sql .= 'enum(' . implode(',', $args) . ') ';
                    break;
                case 'double':
                case 'float':
                case 'decimal':
                    if (trim($column['length'])) {
                        $args = array_map('intval', explode(',', $column['length']));
                        $args[1] = $args[1] ?? $args[0];
                        $sql .= "$method($args[0], $args[1]) ";
                        break;
                    }
                    $sql .= "$method ";
                    break;
                default :
                    $sql .= "$method ";

            }
        }

        if (!$nullable) {
            $sql .= "NOT NULL ";
        }

        if ($method != 'text' && $default !== null) {
            $sql .= "DEFAULT $default ";
        }

        if ($comment !== null) {
            $sql .= "COMMENT $comment ";
        }

        echo "$sql\n";
        Util::db()->statement($sql);
    }

    public static $column = [
        'VARCHAR' => 'string',
        'DATETIME' => 'dateTime',
        'INT' => 'integer',
        'DECIMAL' => 'decimal'
    ];
}