excel_str = "预警进项单据金额不一致：warning_input_receipt_amount_inconsistent
每次执行预警定时任务时，删除该表所有数据，再插入新预警数据
只处理普通订单
字段 类型 索引 默认 描述 其它
id unsigned
bigint 主键索引 AUTO_INCREMENT
trader_name varchar(64) 零值 撮合姓名
trader_id unsigned bigint 零值 撮合ID
trader_department_path varchar(512) 零值 撮合部门路径
purchase_name varchar(64) 零值 采购姓名
purchase_id unsigned bigint 零值 采购ID
purchase_department_path varchar(512) 零值 采购部门路径
order_detail_no varchar(32) 零值 订单明细编号
invoice_amount decimal(16,2) 零值 开票金额 进项单据
purchase_amount decimal(16,2) 零值 采购金额 订单明细
detail_type unsigned
tinyint 零值 预警订单明细类型 值请查看通用配置文档
invoice_status unsigned tinyint 零值 供应商开票状态 来自财务进项单据，值请查看通用配置文档
invoice_no_list varchar(2048) 零值 发票号列表 多个发票号通过逗号隔开
create_time datetime 1972-01-01 00:00:00 新建时间 预警时间
索引
trader_name，普通索引
trader_id，普通索引
trader_department_path，普通索引
purchase_name，普通索引
purchase_id，普通索引
purchase_department_path，普通索引"
table_str,data_str = excel_str.split("\n字段\t类型\t索引\t默认\t描述\t其它\n",2)
table_comment,table_name = table_str.split("：",2)
table_name = table_name.to_s.split("\n",2)[0].to_s.strip
column_str,index_str = data_str.split("\n索引\n",2)
table_data = column_str.split(/\t/)
table_data = table_data.map.with_index{|e,index| (index>0 and index < table_data.length-1 and index%5==0) ? [e.rpartition("\n")[0].gsub("\n"," "),e.rpartition("\n")[2]] : [e.gsub("\n"," ")]}.flatten.each_slice(6).to_a
index_data = []
ddl = "CREATE TABLE `#{table_name}` (\n"
table_data.each do |column, data_type, index_type, default_value, description, other|
column = column.to_s.strip
data_type = data_type.to_s.strip.split(" ").reverse.join(" ") if data_type.to_s.strip.start_with?("unsigned")
ddl << " `#{column}` #{data_type}"
ddl << " COLLATE utf8mb4_unicode_ci" if data_type.include?("char") || data_type.include?("text")
ddl << " NOT NULL" unless data_type.include?("text")
ddl << " AUTO_INCREMENT" if default_value.include? "AUTO_INCREMENT"
if data_type.include?("char")
default_value = default_value.gsub("零值","")
elsif data_type.include?("int")
default_value = default_value.gsub("零值","0")
elsif data_type.include? "text"
default_value = nil
end
ddl << " DEFAULT '#{default_value.to_s.strip}'" unless default_value==nil || default_value.include?("AUTO_INCREMENT" )
ddl << " COMMENT '#{description.to_s.strip}"
ddl << ",#{other.strip}" unless other == nil || other.empty?
ddl << "',\n"
if index_type.include? "主键"
index_data << "PRIMARY KEY (`#{column}`)"
elsif index_type.include? "唯一"
index_data << "UNIQUE KEY `uk_#{column}`(`#{column}`)"
elsif index_type.include? "索引"
index_data << "KEY `idx_#{column}`(`#{column}`)"
end
end
if index_str != nil && !index_str.empty?
index_str.split("\n").each do |line|
next if line == ""
index_column = line.split("，")
if index_column.pop.include? "唯一"
index_data << "UNIQUE KEY `uk_#{index_column.join('_')}`(`#{index_column.join('`,`')}`)"
else
index_data << "KEY `idx_#{index_column.join('_')}`(`#{index_column.join('`,`')}`)"
end
end
end
index_data.uniq.each do |index|
ddl << " #{index}"
ddl << ",\n"
end
ddl = ddl[0,ddl.length-2]
ddl << "\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='#{table_comment}';"
puts ddl
puts ""