blank/docs/db.md

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for excel_templates
-- ----------------------------
DROP TABLE IF EXISTS `excel_templates`;
CREATE TABLE `excel_templates`  (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `template_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '模板名称',
  `template_key` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '模板唯一标识键',
  `template_config` json NOT NULL COMMENT 'Excel模板配置（存储header配置、字段映射规则等）',
  `created_by` int NOT NULL COMMENT '创建人ID（关联member_auth_users.id）',
  `is_deleted` int NULL DEFAULT 0 COMMENT '是否删除 0-正常 1-删除',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `uk_template_key`(`template_key`) USING BTREE COMMENT '模板唯一键索引',
  INDEX `idx_created_by`(`created_by`) USING BTREE COMMENT '创建人索引'
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci 
COMMENT='Excel模板配置表' 
ROW_FORMAT = Dynamic;


-- 向 excel_templates 表插入默认的Excel模板配置
INSERT INTO `excel_templates` 
(`template_name`, `template_key`, `template_config`, `created_by`) 
VALUES 
(
  '默认Excel模板', 
  'default_template', 
  '{
    "sheets": [
      {
        "headerRowIndex": 5,
        "orderNumberRow": 2,
        "orderNumberCol": 3,
        "dataStartRow": 6,
        "endMarker": "订单统计数据:",
        "sheetName": "五金清单",
        "exportFields": ["订单号", "序号", "名称", "颜色", "长度", "宽度", "数量", "单位", "工艺说明"],
        "fieldMappings": {
          "序号": "index",
          "名称": "name",
          "颜色": "color",
          "长度": "length",
          "宽度": "width",
          "数量": "quantity",
          "单位": "unit",
          "工艺说明": "processDescription",
          "订单号": "orderNumber"
        },
        "requiredFields": ["名称", "数量"]
      },
      {
        "headerRowIndex": 5,
        "orderNumberRow": 2,
        "orderNumberCol": 3,
        "dataStartRow": 6,
        "endMarker": "拆单签名",
        "sheetName": "灯带",
        "exportFields": ["订单号", "序号", "名称", "型号", "规格", "数量", "单位", "柜号"],
        "fieldMappings": {
          "序号": "index",
          "名称": "name",
          "型号": "model",
          "规格": "specification",
          "数量": "quantity",
          "单位": "unit",
          "柜号": "cabinetNumber",
          "订单号": "orderNumber"
        },
        "requiredFields": ["名称", "型号", "数量"]
      },
      {
        "headerRowIndex": 3,
        "orderNumberRow": 1,
        "orderNumberCol": 1,
        "dataStartRow": 4,
        "endMarker": "备注:",
        "sheetName": "统计",
        "exportFields": ["序号", "封边条颜色", "封边条规格", "单位(米)", "备注"],
        "fieldMappings": {
          "序号": "index",
          "封边条颜色": "color",
          "封边条规格": "specification",
          "单位(米)": "unit",
          "备注": "remark"
        },
        "requiredFields": ["封边条颜色", "封边条规格"]
      },
      {
        "headerRowIndex": 9,
        "orderNumberRow": 7,
        "orderNumberCol": 3,
        "dataStartRow": 10,
        "endMarker": "合计数量:",
        "sheetName": "外购单",
        "exportFields": ["订单号", "序号", "柜名", "材料名称", "长(高)", "宽", "厚(深)", "单位", "数量", "门铰位置", "单价", "金额", "备注", "分类"],
        "fieldMappings": {
          "订单号": "orderNumber",
          "序号": "index",
          "柜名": "cabinetName",
          "材料名称": "materialName",
          "长(高)": "length",
          "宽": "width",
          "厚(深)": "depth",
          "单位": "unit",
          "数量": "quantity",
          "门铰位置": "hingePosition",
          "单价": "unitPrice",
          "金额": "amount",
          "备注": "remark",
          "分类": "category"
        },
        "requiredFields": ["材料名称", "数量"]
      },
      {
        "headerRowIndex": 4,
        "orderNumberRow": 3,
        "orderNumberCol": 1,
        "dataStartRow": 5,
        "endMarker": "<数据区域结束>",
        "sheetName": "包装数据",
        "exportFields": ["订单号", "序号", "名称", "部件代码", "颜色", "长度", "宽度", "厚度", "数量", "平方数", "开料长度", "开料宽度", "开料厚度", "备注", "正面条码", "反面条码"],
        "fieldMappings": {
          "订单号": "orderNumber",
          "序号": "index",
          "名称": "name",
          "部件代码": "componentCode",
          "颜色": "color",
          "长度": "length",
          "宽度": "width",
          "厚度": "thickness",
          "数量": "quantity",
          "平方数": "square",
          "开料长度": "cuttingLength",
          "开料宽度": "cuttingWidth",
          "开料厚度": "cuttingThickness",
          "备注": "remark",
          "正面条码": "frontBarcode",
          "反面条码": "backBarcode"
        },
        "requiredFields": ["名称", "颜色", "数量"],
        "isMultiTable": true,
        "multiTableHeaderOffset": 4,
        "multiTableDataOffset": 1,
        "multiTableOrderNumberOffset": -1
      }
    ],
    "activeSheetIndex": 0
  }',
  1
);

INSERT INTO `excel_templates` 
(`template_name`, `template_key`, `template_config`, `created_by`) 
VALUES 
(
  '五金清单模板', 
  'hardware_list_template', 
  '{
    "sheets": [
      {
        "headerRowIndex": 5,
        "orderNumberRow": 2,
        "orderNumberCol": 3,
        "dataStartRow": 6,
        "endMarker": "订单统计数据:",
        "sheetName": "五金清单",
        "exportFields": ["订单号", "序号", "名称", "颜色", "长度", "宽度", "数量", "单位", "工艺说明"],
        "fieldMappings": {
          "序号": "index",
          "名称": "name",
          "颜色": "color",
          "长度": "length",
          "宽度": "width",
          "数量": "quantity",
          "单位": "unit",
          "工艺说明": "processDescription",
          "订单号": "orderNumber"
        },
        "requiredFields": ["名称", "数量"]
      }
    ],
    "activeSheetIndex": 0
  }',
  1
);

INSERT INTO `excel_templates` 
(`template_name`, `template_key`, `template_config`, `created_by`) 
VALUES 
(
  '灯带模板', 
  'light_band_template', 
  '{
    "sheets": [
      {
        "headerRowIndex": 5,
        "orderNumberRow": 2,
        "orderNumberCol": 3,
        "dataStartRow": 6,
        "endMarker": "拆单签名",
        "sheetName": "灯带",
        "exportFields": ["订单号", "序号", "名称", "型号", "规格", "数量", "单位", "柜号"],
        "fieldMappings": {
          "序号": "index",
          "名称": "name",
          "型号": "model",
          "规格": "specification",
          "数量": "quantity",
          "单位": "unit",
          "柜号": "cabinetNumber",
          "订单号": "orderNumber"
        },
        "requiredFields": ["名称", "型号", "数量"]
      }
    ],
    "activeSheetIndex": 0
  }',
  1
);

INSERT INTO `excel_templates` 
(`template_name`, `template_key`, `template_config`, `created_by`) 
VALUES 
(
  '统计模板', 
  'statistics_template', 
  '{
    "sheets": [
      {
        "headerRowIndex": 3,
        "orderNumberRow": 1,
        "orderNumberCol": 1,
        "dataStartRow": 4,
        "endMarker": "备注:",
        "sheetName": "统计",
        "exportFields": ["序号", "封边条颜色", "封边条规格", "单位(米)", "备注"],
        "fieldMappings": {
          "序号": "index",
          "封边条颜色": "color",
          "封边条规格": "specification",
          "单位(米)": "unit",
          "备注": "remark"
        },
        "requiredFields": ["封边条颜色", "封边条规格"]
      }
    ],
    "activeSheetIndex": 0
  }',
  1
);

INSERT INTO `excel_templates` 
(`template_name`, `template_key`, `template_config`, `created_by`) 
VALUES 
(
  '外购单模板', 
  'purchase_template', 
  '{
    "sheets": [
      {
        "headerRowIndex": 9,
        "orderNumberRow": 7,
        "orderNumberCol": 3,
        "dataStartRow": 10,
        "endMarker": "合计数量:",
        "sheetName": "外购单",
        "exportFields": ["订单号", "序号", "柜名", "材料名称", "长(高)", "宽", "厚(深)", "单位", "数量", "门铰位置", "单价", "金额", "备注", "分类"],
        "fieldMappings": {
          "订单号": "orderNumber",
          "序号": "index",
          "柜名": "cabinetName",
          "材料名称": "materialName",
          "长(高)": "length",
          "宽": "width",
          "厚(深)": "depth",
          "单位": "unit",
          "数量": "quantity",
          "门铰位置": "hingePosition",
          "单价": "unitPrice",
          "金额": "amount",
          "备注": "remark",
          "分类": "category"
        },
        "requiredFields": ["材料名称", "数量"]
      }
    ],
    "activeSheetIndex": 0
  }',
  1
);

INSERT INTO `excel_templates` 
(`template_name`, `template_key`, `template_config`, `created_by`) 
VALUES 
(
  '包装数据模板', 
  'packaging_template', 
  '{
    "sheets": [
      {
        "headerRowIndex": 4,
        "orderNumberRow": 3,
        "orderNumberCol": 1,
        "dataStartRow": 5,
        "endMarker": "<数据区域结束>",
        "sheetName": "包装数据",
        "exportFields": ["订单号", "序号", "名称", "部件代码", "颜色", "长度", "宽度", "厚度", "数量", "平方数", "开料长度", "开料宽度", "开料厚度", "备注", "正面条码", "反面条码"],
        "fieldMappings": {
          "订单号": "orderNumber",
          "序号": "index",
          "名称": "name",
          "部件代码": "componentCode",
          "颜色": "color",
          "长度": "length",
          "宽度": "width",
          "厚度": "thickness",
          "数量": "quantity",
          "平方数": "square",
          "开料长度": "cuttingLength",
          "开料宽度": "cuttingWidth",
          "开料厚度": "cuttingThickness",
          "备注": "remark",
          "正面条码": "frontBarcode",
          "反面条码": "backBarcode"
        },
        "requiredFields": ["名称", "颜色", "数量"],
        "isMultiTable": true,
        "multiTableHeaderOffset": 4,
        "multiTableDataOffset": 1,
        "multiTableOrderNumberOffset": -1
      }
    ],
    "activeSheetIndex": 0
  }',
  1
); 

SET FOREIGN_KEY_CHECKS = 1;