using System.Data;
using System.Diagnostics;
using System.Text;
using Admin.NET.Plugin.AiDOP.Entity.S0.Sales;
using SqlSugar;
namespace Admin.NET.Plugin.AiDOP.Infrastructure;
///
/// S0 Excel 字段口径迁移：将现有旧列名重命名为 Excel 字段名。
/// 仅处理文档中已确认映射的字段，不自动补 Excel 独有字段。
///
public static class AidopS0ExcelColumnMigration
{
private sealed record ColumnRename(string TableName, string OldColumnName, string NewColumnName);
private sealed record PrimaryKeyRepair(string TableName, string PkColumnName, bool IsIdentity, string PkComment, params string[] ShadowColumns);
private sealed record MySqlColumnMeta(
string ColumnName,
string ColumnType,
string IsNullable,
string? ColumnDefault,
string? Extra,
string? ColumnComment,
string? CollationName,
string? CharacterSetName);
private static readonly ColumnRename[] Renames =
[
new("CostCtrMaster", "cost_ctr", "CostCtr"),
new("CostCtrMaster", "create_time", "CreateTime"),
new("CostCtrMaster", "create_user", "CreateUser"),
new("CostCtrMaster", "descr", "Descr"),
new("CostCtrMaster", "domain_code", "Domain"),
new("CostCtrMaster", "eff_time", "EffTime"),
new("CostCtrMaster", "rec_id", "RecID"),
new("CostCtrMaster", "ufld1", "Ufld1"),
new("CostCtrMaster", "update_time", "UpdateTime"),
new("CostCtrMaster", "update_user", "UpdateUser"),
new("CustMaster", "create_time", "CreateTime"),
new("CustMaster", "create_user", "CreateUser"),
new("CustMaster", "credit_rating", "CreditRating"),
new("CustMaster", "curr", "Curr"),
new("CustMaster", "cust", "Cust"),
new("CustMaster", "domain_code", "Domain"),
new("CustMaster", "is_active", "IsActive"),
new("CustMaster", "is_confirm", "IsConfirm"),
new("CustMaster", "rec_id", "RecID"),
new("CustMaster", "remarks", "Remarks"),
new("CustMaster", "ship_to", "ShipTo"),
new("CustMaster", "slspsn1", "Slspsn1"),
new("CustMaster", "slspsn2", "Slspsn2"),
new("CustMaster", "sort_name", "SortName"),
new("CustMaster", "tax_class", "TaxClass"),
new("CustMaster", "tax_in", "TaxIn"),
new("CustMaster", "update_time", "UpdateTime"),
new("CustMaster", "update_user", "UpdateUser"),
new("DepartmentMaster", "create_time", "CreateTime"),
new("DepartmentMaster", "create_user", "CreateUser"),
new("DepartmentMaster", "department", "Department"),
new("DepartmentMaster", "descr", "Descr"),
new("DepartmentMaster", "domain_code", "Domain"),
new("DepartmentMaster", "is_active", "IsActive"),
new("DepartmentMaster", "rec_id", "RecID"),
new("DepartmentMaster", "update_time", "UpdateTime"),
new("DepartmentMaster", "update_user", "UpdateUser"),
new("DevMonitor", "code_descr", "CodeDescr"),
new("DevMonitor", "code_type", "CodeType"),
new("DevMonitor", "create_time", "CreateTime"),
new("DevMonitor", "create_user", "CreateUser"),
new("DevMonitor", "device_descr", "DeviceDescr"),
new("DevMonitor", "device_number", "DeviceNumber"),
new("DevMonitor", "domain", "Domain"),
new("DevMonitor", "id", "RecID"),
new("DevMonitor", "low_limit", "LowLimit"),
new("DevMonitor", "message_code", "MessageCode"),
new("DevMonitor", "std_value", "StdValue"),
new("DevMonitor", "top_limit", "TopLimit"),
new("DevMonitor", "ufld1", "Ufld1"),
new("DevMonitor", "um", "UM"),
new("DevMonitor", "update_time", "UpdateTime"),
new("DevMonitor", "update_user", "UpdateUser"),
new("DevMonitor", "value_type", "ValueType"),
new("EmpSkills", "create_time", "CreateTime"),
new("EmpSkills", "create_user", "CreateUser"),
new("EmpSkills", "domain", "Domain"),
new("EmpSkills", "efficiency_coefficient", "EfficiencyCoefficient"),
new("EmpSkills", "employee", "Employee"),
new("EmpSkills", "employee_id", "EmployeeID"),
new("EmpSkills", "employee_name", "EmployeeName"),
new("EmpSkills", "id", "RecID"),
new("EmpSkills", "site", "Site"),
new("EmpSkills", "skill_description", "SkillDescription"),
new("EmpSkills", "skill_level", "SkillLevel"),
new("EmpSkills", "skill_no", "SkillNo"),
new("EmpSkills", "update_time", "UpdateTime"),
new("EmpSkills", "update_user", "UpdateUser"),
new("EmpWorkDutyMaster", "create_time", "CreateTime"),
new("EmpWorkDutyMaster", "create_user", "CreateUser"),
new("EmpWorkDutyMaster", "domain_code", "Domain"),
new("EmpWorkDutyMaster", "duty", "Duty"),
new("EmpWorkDutyMaster", "emp_type", "EmpType"),
new("EmpWorkDutyMaster", "employee", "Employee"),
new("EmpWorkDutyMaster", "item_num1", "ItemNum1"),
new("EmpWorkDutyMaster", "item_num2", "ItemNum2"),
new("EmpWorkDutyMaster", "location", "Location"),
new("EmpWorkDutyMaster", "prod_line", "ProdLine"),
new("EmpWorkDutyMaster", "rec_id", "RecID"),
new("EmpWorkDutyMaster", "ufld2", "Ufld2"),
new("EmpWorkDutyMaster", "update_time", "UpdateTime"),
new("EmpWorkDutyMaster", "update_user", "UpdateUser"),
new("EmployeeMaster", "birth_date", "BirthDate"),
new("EmployeeMaster", "car_id", "CarId"),
new("EmployeeMaster", "create_time", "CreateTime"),
new("EmployeeMaster", "create_user", "CreateUser"),
new("EmployeeMaster", "date_employed", "DateEmployed"),
new("EmployeeMaster", "date_terminated", "DateTerminated"),
new("EmployeeMaster", "default_work_location", "DefaultWorkLocation"),
new("EmployeeMaster", "department", "Department"),
new("EmployeeMaster", "domain_code", "Domain"),
new("EmployeeMaster", "email", "Email"),
new("EmployeeMaster", "employee", "Employee"),
new("EmployeeMaster", "employment_status", "EmploymentStatus"),
new("EmployeeMaster", "is_active", "IsActive"),
new("EmployeeMaster", "job_title", "JobTitle"),
new("EmployeeMaster", "marital_status", "MaritalStatus"),
new("EmployeeMaster", "name", "Name"),
new("EmployeeMaster", "phone", "Phone"),
new("EmployeeMaster", "rec_id", "RecID"),
new("EmployeeMaster", "sex", "Sex"),
new("EmployeeMaster", "update_time", "UpdateTime"),
new("EmployeeMaster", "update_user", "UpdateUser"),
new("EmployeeMaster", "work_ctr", "WorkCtr"),
new("ItemMaster", "allocate_single_lot", "AllocateSingleLot"),
new("ItemMaster", "auto_lot_nums", "AutoLotNums"),
new("ItemMaster", "commodity_code", "CommodityCode"),
new("ItemMaster", "create_time", "CreateTime"),
new("ItemMaster", "create_user", "CreateUser"),
new("ItemMaster", "days_between_pm", "DaysBetweenPM"),
new("ItemMaster", "default_shelf", "DefaultShelf"),
new("ItemMaster", "descr", "Descr"),
new("ItemMaster", "descr1", "Descr1"),
new("ItemMaster", "domain_code", "Domain"),
new("ItemMaster", "drawing", "Drawing"),
new("ItemMaster", "emt_type", "EMTType"),
new("ItemMaster", "ins_lt", "InsLT"),
new("ItemMaster", "inspect", "Inspect"),
new("ItemMaster", "install", "Install"),
new("ItemMaster", "is_active", "IsActive"),
new("ItemMaster", "is_confirm", "IsConfirm"),
new("ItemMaster", "is_main_mas", "IsMainMas"),
new("ItemMaster", "issue_policy", "IssuePolicy"),
new("ItemMaster", "item_num", "ItemNum"),
new("ItemMaster", "item_type", "ItemType"),
new("ItemMaster", "key_item", "KeyItem"),
new("ItemMaster", "length", "Length"),
new("ItemMaster", "location", "Location"),
new("ItemMaster", "location_type", "LocationType"),
new("ItemMaster", "lot_serial_control", "LotSerialControl"),
new("ItemMaster", "max_ord", "MaxOrd"),
new("ItemMaster", "mfg_lt", "MfgLT"),
new("ItemMaster", "mfgmttr", "MFGMTTR"),
new("ItemMaster", "min_ord", "MinOrd"),
new("ItemMaster", "min_ord_sales", "MinOrdSales"),
new("ItemMaster", "net_weight", "NetWeight"),
new("ItemMaster", "net_weight_um", "NetWeightUM"),
new("ItemMaster", "ord_mult", "OrdMult"),
new("ItemMaster", "owner_application", "OwnerApplication"),
new("ItemMaster", "pkg_code", "PkgCode"),
new("ItemMaster", "planner", "Planner"),
new("ItemMaster", "pur_lt", "PurLT"),
new("ItemMaster", "pur_mfg", "PurMfg"),
new("ItemMaster", "rec_id", "RecID"),
new("ItemMaster", "remark", "Remark"),
new("ItemMaster", "rev", "Rev"),
new("ItemMaster", "safety_stk", "SafetyStk"),
new("ItemMaster", "size", "Size"),
new("ItemMaster", "size_um", "SizeUM"),
new("ItemMaster", "status", "Status"),
new("ItemMaster", "stock_turn_over", "StockTurnOver"),
new("ItemMaster", "trace_detail", "TraceDetail"),
new("ItemMaster", "um", "UM"),
new("ItemMaster", "update_time", "UpdateTime"),
new("ItemMaster", "update_user", "UpdateUser"),
new("ItemOpCondition", "code_type", "CodeType"),
new("ItemOpCondition", "create_time", "CreateTime"),
new("ItemOpCondition", "create_user", "CreateUser"),
new("ItemOpCondition", "descr", "Descr"),
new("ItemOpCondition", "domain", "Domain"),
new("ItemOpCondition", "id", "RecID"),
new("ItemOpCondition", "is_main", "IsMain"),
new("ItemOpCondition", "item_code", "ItemCode"),
new("ItemOpCondition", "item_num", "ItemNum"),
new("ItemOpCondition", "line", "Line"),
new("ItemOpCondition", "op", "Op"),
new("ItemOpCondition", "typed", "Typed"),
new("ItemOpCondition", "update_time", "UpdateTime"),
new("ItemOpCondition", "update_user", "UpdateUser"),
new("ItemPackMaster", "create_time", "CreateTime"),
new("ItemPackMaster", "create_user", "CreateUser"),
new("ItemPackMaster", "cust_item", "CustItem"),
new("ItemPackMaster", "domain_code", "Domain"),
new("ItemPackMaster", "high", "High"),
new("ItemPackMaster", "is_active", "IsActive"),
new("ItemPackMaster", "iss_specific", "IssSpecific"),
new("ItemPackMaster", "item_num", "ItemNum"),
new("ItemPackMaster", "length", "Length"),
new("ItemPackMaster", "net_weight", "NetWeight"),
new("ItemPackMaster", "packing_qty", "PackingQty"),
new("ItemPackMaster", "packing_type", "PackingType"),
new("ItemPackMaster", "rec_id", "RecID"),
new("ItemPackMaster", "remark", "Remark"),
new("ItemPackMaster", "small_packing_qty", "SmallPackingQty"),
new("ItemPackMaster", "update_time", "UpdateTime"),
new("ItemPackMaster", "update_user", "UpdateUser"),
new("ItemPackMaster", "weight_um", "WeightUM"),
new("ItemPackMaster", "width", "Width"),
new("ItemSubstituteDetail", "create_time", "CreateTime"),
new("ItemSubstituteDetail", "create_user", "CreateUser"),
new("ItemSubstituteDetail", "domain", "Domain"),
new("ItemSubstituteDetail", "id", "RecID"),
new("ItemSubstituteDetail", "item_num", "ItemNum"),
new("ItemSubstituteDetail", "parent_item", "ParentItem"),
new("ItemSubstituteDetail", "remark", "Remark"),
new("ItemSubstituteDetail", "substitute_item", "SubstituteItem"),
new("ItemSubstituteDetail", "substitute_qty", "SubstituteQty"),
new("ItemSubstituteDetail", "update_time", "UpdateTime"),
new("ItemSubstituteDetail", "update_user", "UpdateUser"),
new("LineMaster", "create_time", "CreateTime"),
new("LineMaster", "create_user", "CreateUser"),
new("LineMaster", "domain", "Domain"),
new("LineMaster", "id", "RecID"),
new("LineMaster", "is_active", "IsActive"),
new("LineMaster", "line", "Line"),
new("LineMaster", "line_category", "LineCategory"),
new("LineMaster", "line_type", "LineType"),
new("LineMaster", "location", "Location"),
new("LineMaster", "mid_location", "MidLocation"),
new("LineMaster", "picking_location", "PickingLocation"),
new("LineMaster", "update_time", "UpdateTime"),
new("LineMaster", "update_user", "UpdateUser"),
new("LineMaster", "v_location", "VLocation"),
new("LineMaster", "workshop", "Workshop"),
new("LineSkillDetail", "create_time", "CreateTime"),
new("LineSkillMaster", "create_time", "CreateTime"),
new("LineSkillMaster", "create_user", "CreateUser"),
new("LineSkillMaster", "domain", "Domain"),
new("LineSkillMaster", "id", "RecID"),
new("LineSkillMaster", "job_descr", "JOBDescr"),
new("LineSkillMaster", "job_no", "JOBNo"),
new("LineSkillMaster", "prod_line", "ProdLine"),
new("LineSkillMaster", "update_time", "UpdateTime"),
new("LineSkillMaster", "update_user", "UpdateUser"),
new("PriorityCode", "create_time", "CreateTime"),
new("PriorityCode", "create_user", "CreateUser"),
new("PriorityCode", "descr", "Descr"),
new("PriorityCode", "domain_code", "Domain"),
new("PriorityCode", "is_active", "IsActive"),
new("PriorityCode", "priority", "Priority"),
new("PriorityCode", "rec_id", "RecID"),
new("PriorityCode", "source_column", "SourceColumn"),
new("PriorityCode", "source_id", "SourceID"),
new("PriorityCode", "source_table", "SourceTable"),
new("PriorityCode", "source_type", "SourceType"),
new("PriorityCode", "update_time", "UpdateTime"),
new("PriorityCode", "update_user", "UpdateUser"),
new("PriorityCode", "value", "Value"),
new("PriorityCode", "value_id", "ValueID"),
new("PriorityCode", "value_type", "ValueType"),
new("ProdLineDetail", "create_time", "CreateTime"),
new("ProdLineDetail", "create_user", "CreateUser"),
new("ProdLineDetail", "domain", "Domain"),
new("ProdLineDetail", "id", "RecID"),
new("ProdLineDetail", "is_active", "IsActive"),
new("ProdLineDetail", "is_confirm", "IsConfirm"),
new("ProdLineDetail", "line", "Line"),
new("ProdLineDetail", "mold_type_code", "MoldTypeCode"),
new("ProdLineDetail", "op", "Op"),
new("ProdLineDetail", "part", "Part"),
new("ProdLineDetail", "rate", "Rate"),
new("ProdLineDetail", "setup_time", "SetupTime"),
new("ProdLineDetail", "site", "Site"),
new("ProdLineDetail", "skill_no", "SkillNo"),
new("ProdLineDetail", "standard_staff_count", "StandardStaffCount"),
new("ProdLineDetail", "start", "Start"),
new("ProdLineDetail", "update_time", "UpdateTime"),
new("ProdLineDetail", "update_user", "UpdateUser"),
new("ProductStructureMaster", "end_eff", "EndEff"),
new("ProductStructureMaster", "id", "RecID"),
new("ProductStructureMaster", "qty", "Qty"),
new("ProductStructureMaster", "qty_consumed", "QtyConsumed"),
new("ProductStructureMaster", "refs", "Refs"),
new("ProductStructureMaster", "remark", "Remark"),
new("ProductStructureMaster", "scrap", "Scrap"),
new("ProductStructureMaster", "start_eff", "StartEff"),
new("ProductStructureMaster", "structure_type", "StructureType"),
new("ProductStructureMaster", "um", "UM"),
new("ProductStructureOp", "id", "RecID"),
new("ProductStructureOp", "op", "Op"),
new("RoutingOpDetail", "comment_index", "CommentIndex"),
new("RoutingOpDetail", "create_user", "CreateUser"),
new("RoutingOpDetail", "id", "RecID"),
new("RoutingOpDetail", "is_confirm", "IsConfirm"),
new("RoutingOpDetail", "u_deci1", "UDeci1"),
new("RoutingOpDetail", "u_deci2", "UDeci2"),
new("RoutingOpDetail", "u_deci3", "UDeci3"),
new("RoutingOpDetail", "u_deci4", "UDeci4"),
new("RoutingOpDetail", "u_deci5", "UDeci5"),
new("RoutingOpDetail", "ufld1", "Ufld1"),
new("RoutingOpDetail", "ufld2", "Ufld2"),
new("RoutingOpDetail", "ufld3", "Ufld3"),
new("RoutingOpDetail", "ufld4", "Ufld4"),
new("RoutingOpDetail", "ufld5", "Ufld5"),
new("RoutingOpDetail", "ufld6", "Ufld6"),
new("RoutingOpDetail", "ufld7", "Ufld7"),
new("RoutingOpDetail", "ufld8", "Ufld8"),
new("RoutingOpDetail", "ufld9", "Ufld9"),
new("RoutingOpDetail", "update_user", "UpdateUser"),
new("SuppMaster", "ap_end_day1", "APEndDay1"),
new("SuppMaster", "ap_end_day2", "APEndDay2"),
new("SuppMaster", "ap_start_day1", "APStartDay1"),
new("SuppMaster", "ap_start_day2", "APStartDay2"),
new("SuppMaster", "cr_terms", "CrTerms"),
new("SuppMaster", "create_time", "CreateTime"),
new("SuppMaster", "create_user", "CreateUser"),
new("SuppMaster", "curr", "Curr"),
new("SuppMaster", "domain_code", "Domain"),
new("SuppMaster", "is_active", "IsActive"),
new("SuppMaster", "pur_contact", "PurContact"),
new("SuppMaster", "rec_id", "RecID"),
new("SuppMaster", "remark", "Remark"),
new("SuppMaster", "sort_name", "SortName"),
new("SuppMaster", "supp", "Supp"),
new("SuppMaster", "tax_class", "TaxClass"),
new("SuppMaster", "tax_in", "TaxIn"),
new("SuppMaster", "type", "Type"),
new("SuppMaster", "update_time", "UpdateTime"),
new("SuppMaster", "update_user", "UpdateUser"),
new("WorkCtrMaster", "create_time", "CreateTime"),
new("WorkCtrMaster", "create_user", "CreateUser"),
new("WorkCtrMaster", "department", "Department"),
new("WorkCtrMaster", "descr", "Descr"),
new("WorkCtrMaster", "domain", "Domain"),
new("WorkCtrMaster", "id", "RecID"),
new("WorkCtrMaster", "is_active", "IsActive"),
new("WorkCtrMaster", "update_time", "UpdateTime"),
new("WorkCtrMaster", "update_user", "UpdateUser"),
new("WorkCtrMaster", "work_ctr", "WorkCtr"),
new("ic_substitute", "id", "Id"),
new("ic_substitute", "is_deleted", "IsDeleted"),
new("srm_purchase", "id", "Id"),
new("srm_purchase", "is_require_goods", "IsRequireGoods"),
];
private static readonly PrimaryKeyRepair[] PrimaryKeyRepairs =
[
new("ItemSubstituteDetail", "RecID", true, "物料替代关系主键", "Id"),
new("CostCtrMaster", "RecID", true, "主键"),
new("CustMaster", "RecID", true, "主键"),
new("DepartmentMaster", "RecID", true, "主键"),
new("DevMonitor", "RecID", true, "主键", "Id"),
new("EmpSkills", "RecID", true, "主键", "Id"),
new("EmpWorkDutyMaster", "RecID", true, "主键"),
new("EmployeeMaster", "RecID", true, "主键"),
new("ItemMaster", "RecID", true, "主键"),
new("ItemOpCondition", "RecID", true, "物料工序生产要素主键", "Id"),
new("ItemPackMaster", "RecID", true, "主键"),
new("LineMaster", "RecID", true, "生产线主键", "Id"),
new("LineSkillMaster", "RecID", true, "产线岗位主键", "Id"),
new("PriorityCode", "RecID", true, "主键"),
new("ProdLineDetail", "RecID", true, "生产线物料主键"),
new("ProductStructureMaster", "RecID", true, "主键", "Id"),
new("ProductStructureOp", "RecID", true, "主键", "Id"),
new("RoutingOpDetail", "RecID", true, "主键", "Id"),
new("SuppMaster", "RecID", true, "主键"),
new("WorkCtrMaster", "RecID", true, "工作中心主键"),
new("ic_substitute", "Id", false, "主键"),
new("srm_purchase", "Id", true, "货源清单主键")
];
public static void Migrate(ISqlSugarClient db)
{
var databaseName = db.Ado.SqlQuerySingle("SELECT DATABASE();");
foreach (var group in Renames.GroupBy(x => x.TableName))
{
var tableName = group.Key;
var tables = db.DbMaintenance.GetTableInfoList(false);
if (!tables.Any(t => t.Name.Equals(tableName, StringComparison.OrdinalIgnoreCase)))
continue;
try
{
var columns = db.DbMaintenance.GetColumnInfosByTableName(tableName, false) ?? [];
foreach (var rename in group)
{
var oldColumn = columns.FirstOrDefault(c => c.DbColumnName.Equals(rename.OldColumnName, StringComparison.Ordinal));
var newColumn = columns.FirstOrDefault(c => c.DbColumnName.Equals(rename.NewColumnName, StringComparison.Ordinal));
if (oldColumn == null)
continue;
if (newColumn != null)
{
if (oldColumn.IsPrimarykey)
{
db.Ado.ExecuteCommand($"ALTER TABLE `{tableName}` DROP COLUMN `{rename.NewColumnName}`");
Trace.TraceInformation($"Ai-DOP S0 drop duplicate new PK shadow column: {tableName}.{rename.NewColumnName}");
RenameColumnMySql(db, databaseName, tableName, rename.OldColumnName, rename.NewColumnName);
Trace.TraceInformation($"Ai-DOP S0 rename PK column: {tableName}.{rename.OldColumnName} -> {rename.NewColumnName}");
}
else
{
db.Ado.ExecuteCommand($"ALTER TABLE `{tableName}` DROP COLUMN `{rename.OldColumnName}`");
Trace.TraceInformation($"Ai-DOP S0 drop duplicate old column: {tableName}.{rename.OldColumnName}");
}
columns = db.DbMaintenance.GetColumnInfosByTableName(tableName, false) ?? [];
continue;
}
RenameColumnMySql(db, databaseName, tableName, rename.OldColumnName, rename.NewColumnName);
Trace.TraceInformation($"Ai-DOP S0 rename column: {tableName}.{rename.OldColumnName} -> {rename.NewColumnName}");
columns = db.DbMaintenance.GetColumnInfosByTableName(tableName, false) ?? [];
}
}
catch (Exception ex)
{
Trace.TraceError($"Ai-DOP S0 rename columns failed for {tableName}: {ex}");
throw;
}
}
RepairPrimaryKeys(db, databaseName);
EnsureS0SalesAddonColumnsAndSeeds(db, databaseName);
}
///
/// 产销扩展列（客户全称、优先级业务维度、订单评审订单类型）及示范合同评审五阶段种子。
///
private static void EnsureS0SalesAddonColumnsAndSeeds(ISqlSugarClient db, string databaseName)
{
EnsureColumn(db, databaseName, "CustMaster", "CustFullName",
"varchar(500) NULL COMMENT '客户全称/正式名称'");
EnsureColumn(db, databaseName, "StdOpMaster", "std_op_code",
"varchar(100) NULL COMMENT '标准工序编码'");
EnsureColumn(db, databaseName, "ItemSubstituteDetail", "SubstituteType",
"varchar(20) NULL COMMENT '替代关系类型（替代/取代）'");
EnsureColumn(db, databaseName, "SuppMaster", "address",
"varchar(500) NULL COMMENT '地址'");
EnsureColumn(db, databaseName, "SuppMaster", "contact",
"varchar(200) NULL COMMENT '联系人'");
EnsureColumn(db, databaseName, "SuppMaster", "position",
"varchar(200) NULL COMMENT '职务'");
EnsureColumn(db, databaseName, "SuppMaster", "contact_info",
"varchar(200) NULL COMMENT '联系方式'");
EnsureColumn(db, databaseName, "SuppMaster", "certification_level",
"varchar(100) NULL COMMENT '认证等级'");
EnsureColumn(db, databaseName, "EmpWorkDutyMaster", "ItemNum",
"varchar(100) NULL COMMENT '单物料编码'");
EnsureColumn(db, databaseName, "qms_sampscheme", "FFIXEDRATE",
"decimal(10,4) NULL COMMENT '固定抽样比例(%)'");
EnsureColumn(db, databaseName, "qms_lymjbmd", "wlbm",
"varchar(255) NULL COMMENT '物料编码'");
EnsureColumn(db, databaseName, "qms_lymjbmd", "wlmc",
"varchar(255) NULL COMMENT '物料名称'");
EnsureColumn(db, databaseName, "qms_lymjbmd", "dim_type",
"varchar(50) NULL COMMENT '维度类型（supplier/material/material_supplier）'");
EnsureColumn(db, databaseName, "qms_inspectioninstru", "gauge_category",
"varchar(255) NULL COMMENT '计量器具分类'");
EnsureColumn(db, databaseName, "qms_inspectioninstru", "calibration_cycle_days",
"int NULL COMMENT '校准周期(天)'");
EnsureColumn(db, databaseName, "qms_inspectioninstru", "next_calibration_date",
"varchar(50) NULL COMMENT '下次校准日期'");
EnsureColumn(db, databaseName, "PriorityCode", "ScopeCustClass",
"varchar(100) NULL COMMENT '业务维度：客户类型/级别（可空=不限）'");
EnsureColumn(db, databaseName, "PriorityCode", "ScopeOrderType",
"varchar(100) NULL COMMENT '业务维度：订单类型（可空=不限）'");
EnsureColumn(db, databaseName, "PriorityCode", "ScopeDueDaysMax",
"int NULL COMMENT '业务维度：承诺交期上限天数（可空=不限）'");
var hasOrderReviewTable = db.DbMaintenance.GetTableInfoList(false)
.Any(t => t.Name.Equals("S0OrderReviewCycle", StringComparison.OrdinalIgnoreCase));
if (hasOrderReviewTable)
{
EnsureColumn(db, databaseName, "S0OrderReviewCycle", "order_type",
"varchar(100) NOT NULL DEFAULT '' COMMENT '订单类型；空字符串=默认/不限'");
db.Ado.ExecuteCommand("UPDATE `S0OrderReviewCycle` SET `order_type` = '' WHERE `order_type` IS NULL");
TryDropIndex(db, databaseName, "S0OrderReviewCycle", "uk_S0OrderReviewCycle_factory");
EnsureUniqueIndex(db, databaseName, "S0OrderReviewCycle", "uk_S0OrderReviewCycle_factory_ordertype",
"`factory_ref_id`, `order_type`");
}
EnsureContractReviewCycleDemoStages(db);
}
private static void EnsureColumn(ISqlSugarClient db, string databaseName, string tableName, string columnName, string columnDefinitionTail)
{
var tables = db.DbMaintenance.GetTableInfoList(false);
if (!tables.Any(t => t.Name.Equals(tableName, StringComparison.OrdinalIgnoreCase)))
return;
var dbLit = databaseName.Replace("'", "''", StringComparison.Ordinal);
var tblLit = tableName.Replace("'", "''", StringComparison.Ordinal);
var colLit = columnName.Replace("'", "''", StringComparison.Ordinal);
var count = db.Ado.SqlQuerySingle(
$"""
SELECT COUNT(*) FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = '{dbLit}' AND TABLE_NAME = '{tblLit}' AND COLUMN_NAME = '{colLit}'
""");
if (count > 0)
return;
db.Ado.ExecuteCommand($"ALTER TABLE `{tableName}` ADD COLUMN `{columnName}` {columnDefinitionTail}");
Trace.TraceInformation($"Ai-DOP S0 add column: {tableName}.{columnName}");
}
private static void TryDropIndex(ISqlSugarClient db, string databaseName, string tableName, string indexName)
{
var tables = db.DbMaintenance.GetTableInfoList(false);
if (!tables.Any(t => t.Name.Equals(tableName, StringComparison.OrdinalIgnoreCase)))
return;
var dbLit = databaseName.Replace("'", "''", StringComparison.Ordinal);
var tblLit = tableName.Replace("'", "''", StringComparison.Ordinal);
var idxLit = indexName.Replace("'", "''", StringComparison.Ordinal);
var count = db.Ado.SqlQuerySingle(
$"""
SELECT COUNT(*) FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = '{dbLit}' AND TABLE_NAME = '{tblLit}' AND INDEX_NAME = '{idxLit}'
""");
if (count == 0)
return;
db.Ado.ExecuteCommand($"ALTER TABLE `{tableName}` DROP INDEX `{indexName}`");
Trace.TraceInformation($"Ai-DOP S0 drop index: {tableName}.{indexName}");
}
private static void EnsureUniqueIndex(ISqlSugarClient db, string databaseName, string tableName, string indexName, string columnListSql)
{
var tables = db.DbMaintenance.GetTableInfoList(false);
if (!tables.Any(t => t.Name.Equals(tableName, StringComparison.OrdinalIgnoreCase)))
return;
var dbLit = databaseName.Replace("'", "''", StringComparison.Ordinal);
var tblLit = tableName.Replace("'", "''", StringComparison.Ordinal);
var idxLit = indexName.Replace("'", "''", StringComparison.Ordinal);
var count = db.Ado.SqlQuerySingle(
$"""
SELECT COUNT(*) FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = '{dbLit}' AND TABLE_NAME = '{tblLit}' AND INDEX_NAME = '{idxLit}'
""");
if (count > 0)
return;
db.Ado.ExecuteCommand($"ALTER TABLE `{tableName}` ADD UNIQUE INDEX `{indexName}` ({columnListSql})");
Trace.TraceInformation($"Ai-DOP S0 add unique index: {tableName}.{indexName}");
}
private static void EnsureContractReviewCycleDemoStages(ISqlSugarClient db)
{
const long s0CompanyId = 1329900200001L;
const long s0Factory1Id = 1329900200002L;
const long s0Factory2Id = 1329900200003L;
var tables = db.DbMaintenance.GetTableInfoList(false);
if (!tables.Any(t => t.Name.Equals("S0ContractReviewCycle", StringComparison.OrdinalIgnoreCase)))
return;
var stages = new (string Code, string Name, int Order)[]
{
("CR1", "意见评审", 1),
("CR2", "意见反馈", 2),
("CR3", "二次评审", 3),
("CR4", "领导意见", 4),
("CR5", "合同盖章", 5),
};
foreach (var factoryId in new[] { s0Factory1Id, s0Factory2Id })
{
var hasAny = db.Queryable()
.Where(x => x.FactoryRefId == factoryId)
.Any();
if (hasAny)
continue;
var now = DateTime.Now;
foreach (var s in stages)
{
db.Insertable(new AdoS0ContractReviewCycle
{
CompanyRefId = s0CompanyId,
FactoryRefId = factoryId,
DomainCode = null,
StageCode = s.Code,
StageName = s.Name,
StdHours = 0,
OrderNo = s.Order,
IsActive = true,
CreateTime = now,
}).ExecuteCommand();
}
Trace.TraceInformation($"Ai-DOP S0 seeded contract review stages for factory {factoryId}");
}
}
private static void RenameColumnMySql(ISqlSugarClient db, string databaseName, string tableName, string oldColumnName, string newColumnName)
{
var meta = GetMySqlColumnMeta(db, databaseName, tableName, oldColumnName);
if (meta == null)
return;
var ddl = $"ALTER TABLE `{tableName}` CHANGE COLUMN `{oldColumnName}` `{newColumnName}` {BuildColumnDefinition(meta)}";
db.Ado.ExecuteCommand(ddl);
}
private static void RepairPrimaryKeys(ISqlSugarClient db, string databaseName)
{
var tableList = db.DbMaintenance.GetTableInfoList(false);
foreach (var repair in PrimaryKeyRepairs)
{
var tableName = tableList.FirstOrDefault(t => t.Name.Equals(repair.TableName, StringComparison.OrdinalIgnoreCase))?.Name;
if (string.IsNullOrEmpty(tableName))
continue;
var tableColumns = db.DbMaintenance.GetColumnInfosByTableName(tableName, false) ?? [];
if (tableColumns.Count == 0)
continue;
foreach (var shadowColumn in repair.ShadowColumns)
{
if (tableColumns.Any(c => c.DbColumnName.Equals(shadowColumn, StringComparison.Ordinal)))
{
db.Ado.ExecuteCommand($"ALTER TABLE `{tableName}` DROP COLUMN `{shadowColumn}`");
}
}
SugarParameter[] pkParameters =
[
new SugarParameter("@db", databaseName),
new SugarParameter("@table", tableName)
];
var pkTable = db.Ado.GetDataTable(
"""
SELECT COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = @db
AND TABLE_NAME = @table
AND CONSTRAINT_NAME = 'PRIMARY'
ORDER BY ORDINAL_POSITION;
""",
pkParameters);
var primaryKeyColumns = pkTable.Rows.Cast()
.Select(r => r["COLUMN_NAME"]?.ToString())
.Where(x => !string.IsNullOrWhiteSpace(x))
.ToList();
if (primaryKeyColumns.Count == 0)
{
primaryKeyColumns = tableColumns
.Where(c => c.IsPrimarykey)
.OrderBy(c => c.DbColumnName)
.Select(c => c.DbColumnName)
.Where(x => !string.IsNullOrWhiteSpace(x))
.Select(x => (string?)x)
.ToList();
}
var pkMatches = primaryKeyColumns.Count == 1
&& primaryKeyColumns[0]!.Equals(repair.PkColumnName, StringComparison.OrdinalIgnoreCase);
if (!pkMatches)
{
if (primaryKeyColumns.Count > 0)
db.Ado.ExecuteCommand($"ALTER TABLE `{tableName}` DROP PRIMARY KEY");
db.Ado.ExecuteCommand($"ALTER TABLE `{tableName}` ADD PRIMARY KEY(`{repair.PkColumnName}`)");
}
if (!repair.IsIdentity)
continue;
var meta = GetMySqlColumnMeta(db, databaseName, tableName, repair.PkColumnName)
?? GetMySqlColumnMeta(db, databaseName, tableName, primaryKeyColumns.FirstOrDefault() ?? repair.PkColumnName);
if (meta == null || ContainsToken(meta.Extra, "auto_increment"))
continue;
var ddl = $"ALTER TABLE `{tableName}` MODIFY COLUMN `{meta.ColumnName}` {BuildColumnDefinition(meta, forceNotNull: true, forceAutoIncrement: true, skipDefault: true, commentOverride: repair.PkComment)}";
db.Ado.ExecuteCommand(ddl);
}
}
private static MySqlColumnMeta? GetMySqlColumnMeta(ISqlSugarClient db, string databaseName, string tableName, string columnName)
{
var sql = """
SELECT
COLUMN_NAME AS ColumnName,
COLUMN_TYPE AS ColumnType,
IS_NULLABLE AS IsNullable,
COLUMN_DEFAULT AS ColumnDefault,
EXTRA AS Extra,
COLUMN_COMMENT AS ColumnComment,
COLLATION_NAME AS CollationName,
CHARACTER_SET_NAME AS CharacterSetName
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = @db
AND TABLE_NAME = @table
AND BINARY COLUMN_NAME = @column
LIMIT 1;
""";
SugarParameter[] parameters =
[
new SugarParameter("@db", databaseName),
new SugarParameter("@table", tableName),
new SugarParameter("@column", columnName)
];
var table = db.Ado.GetDataTable(sql, parameters);
if (table.Rows.Count == 0)
return null;
var row = table.Rows[0];
return new MySqlColumnMeta(
row["ColumnName"]?.ToString() ?? columnName,
row["ColumnType"]?.ToString() ?? throw new InvalidOperationException($"Missing ColumnType for {tableName}.{columnName}"),
row["IsNullable"]?.ToString() ?? "YES",
row["ColumnDefault"] == DBNull.Value ? null : row["ColumnDefault"]?.ToString(),
row["Extra"] == DBNull.Value ? null : row["Extra"]?.ToString(),
row["ColumnComment"] == DBNull.Value ? null : row["ColumnComment"]?.ToString(),
row["CollationName"] == DBNull.Value ? null : row["CollationName"]?.ToString(),
row["CharacterSetName"] == DBNull.Value ? null : row["CharacterSetName"]?.ToString());
}
private static string BuildColumnDefinition(MySqlColumnMeta meta, bool forceNotNull = false, bool forceAutoIncrement = false, bool skipDefault = false, string? commentOverride = null)
{
var builder = new StringBuilder();
builder.Append(meta.ColumnType);
if (!string.IsNullOrWhiteSpace(meta.CharacterSetName))
builder.Append($" CHARACTER SET {meta.CharacterSetName}");
if (!string.IsNullOrWhiteSpace(meta.CollationName))
builder.Append($" COLLATE {meta.CollationName}");
var isNullable = !forceNotNull && meta.IsNullable.Equals("YES", StringComparison.OrdinalIgnoreCase);
builder.Append(isNullable ? " NULL" : " NOT NULL");
if (!skipDefault && meta.ColumnDefault != null)
{
builder.Append(" DEFAULT ");
builder.Append(FormatDefaultValue(meta.ColumnType, meta.ColumnDefault));
}
else if (!skipDefault && isNullable &&
!ContainsToken(meta.Extra, "auto_increment") &&
!ContainsToken(meta.Extra, "DEFAULT_GENERATED"))
{
builder.Append(" DEFAULT NULL");
}
if (forceAutoIncrement || ContainsToken(meta.Extra, "auto_increment"))
builder.Append(" AUTO_INCREMENT");
if (ContainsToken(meta.Extra, "on update CURRENT_TIMESTAMP"))
builder.Append(" ON UPDATE CURRENT_TIMESTAMP");
var comment = commentOverride ?? meta.ColumnComment;
if (!string.IsNullOrWhiteSpace(comment))
builder.Append($" COMMENT '{EscapeSqlLiteral(comment)}'");
return builder.ToString();
}
private static string FormatDefaultValue(string columnType, string columnDefault)
{
if (columnDefault.Equals("NULL", StringComparison.OrdinalIgnoreCase))
return "NULL";
if (columnDefault.StartsWith("CURRENT_TIMESTAMP", StringComparison.OrdinalIgnoreCase))
return columnDefault;
return IsNumericLikeType(columnType)
? columnDefault
: $"'{EscapeSqlLiteral(columnDefault)}'";
}
private static bool IsNumericLikeType(string columnType)
{
var normalized = columnType.ToLowerInvariant();
return normalized.StartsWith("tinyint") ||
normalized.StartsWith("smallint") ||
normalized.StartsWith("mediumint") ||
normalized.StartsWith("int") ||
normalized.StartsWith("bigint") ||
normalized.StartsWith("decimal") ||
normalized.StartsWith("numeric") ||
normalized.StartsWith("float") ||
normalized.StartsWith("double") ||
normalized.StartsWith("real") ||
normalized.StartsWith("bit") ||
normalized.StartsWith("bool") ||
normalized.StartsWith("boolean");
}
private static bool ContainsToken(string? value, string token)
=> !string.IsNullOrWhiteSpace(value) && value.Contains(token, StringComparison.OrdinalIgnoreCase);
private static string EscapeSqlLiteral(string value)
=> value.Replace("\\", "\\\\").Replace("'", "\\'");
}