public void BuildMdmCodeSet(string excelPath, string sheetName, string dbConnName)
{
Workbook workbook = null;
IList <code_set_excel_entity> codesystemCels = new List <code_set_excel_entity>();
IList <code_set_excel_entity> codesetexcels1 = new List <code_set_excel_entity>();
using (FileStream fs = new FileStream(excelPath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
workbook = new Workbook(fs);
}
//新版MDMExcel 的说明
if (string.IsNullOrEmpty(sheetName))
{
sheetName = "代码系统";
}
Worksheet sheet = workbook.Worksheets[sheetName];
var cells = sheet.Cells;
#region 加载代码系统，包含代码集的代码系统
for (int i = 1; i <= cells.MaxDataRow; i++)
{
var row = cells.CheckRow(i);
if (row == null || row.FirstCell == null)
{
continue;
}
else if (row.GetCellOrNull(CellsHelper.ColumnNameToIndex("A")) == null)
{
continue;
}
else if (row.GetCellOrNull(CellsHelper.ColumnNameToIndex("A")).StringValue.Contains("代码"))
{
continue;
}
else
{
var hasCodeSet = row.GetCellOrNull(CellsHelper.ColumnNameToIndex("P")).StringValue;
if (string.IsNullOrWhiteSpace(hasCodeSet))
{
continue;
}
var aaa = new code_set_excel_entity();
aaa.code_sys_code = row.GetCellOrNull(CellsHelper.ColumnNameToIndex("G")).StringValue;
aaa.code_sys_name = row.GetCellOrNull(CellsHelper.ColumnNameToIndex("H")).StringValue;
var ser = row.GetCellOrNull(CellsHelper.ColumnNameToIndex("P"));
aaa.memo = sheet.Hyperlinks.FirstOrDefault(p => (p.Area.StartRow == ser.Row && p.Area.StartColumn == ser.Column)).Address;
if (!string.IsNullOrWhiteSpace(aaa.memo))
{
aaa.show_name = aaa.memo.Split('!')[0];//存放对应的sheet的名称
}
codesystemCels.Add(aaa);
}
}
#endregion
#region 循环处理所有的代码系统，并提取代码集
foreach (var item in codesystemCels) //所有的代码系统
{
string sheetNameCodeSet = item.code_sys_code;
Cells codeSetCells = workbook.Worksheets[item.show_name]?.Cells;
if (codeSetCells != null)
{ //读取worksheet codeset
for (int k = 3; k <= codeSetCells.MaxDataRow; k++) //单个codeset 从第二行开始
{
var row = codeSetCells.CheckRow(k);
if (row == null || row.FirstCell == null)
{
continue;
}
else if (row.GetCellOrNull(0).StringValue.StartsWith("代码", StringComparison.OrdinalIgnoreCase))
{
continue;
}
else
{
var aaa = new code_set_excel_entity();
aaa.code_sys_code = item.code_sys_code;
aaa.code_sys_name = item.code_sys_name;
aaa.code = row.GetCellOrNull(CellsHelper.ColumnNameToIndex("A")).StringValue; //第一列
aaa.name = row.GetCellOrNull(CellsHelper.ColumnNameToIndex("B")).StringValue; //第二列
aaa.show_name = row.GetCellOrNull(CellsHelper.ColumnNameToIndex("C")).StringValue; //第三列
codesetexcels1.Add(aaa);
}
}
}
}//循环所有的代码系统
#endregion
#region 插入数据
var connstr = System.Configuration.ConfigurationManager.ConnectionStrings[dbConnName].ConnectionString;
using (NpgsqlConnection conn = new NpgsqlConnection(connstr))
{
foreach (var item in codesystemCels)
{
var code_sys_id_list = conn.Query <int>($"select code_sys_id from mdm.code_system where code_sys_code = '{item.code_sys_code}' ").ToList();
int code_sys_id = -1;
if (code_sys_id_list == null || code_sys_id_list.Count == 0)
{
Serilog.Log.Warning($"{item.code_sys_code}{'\t'}{item.code_sys_name}");
}
else
{
code_sys_id = code_sys_id_list.FirstOrDefault();
}
var codesets = codesetexcels1.Where(p => p.code_sys_code == item.code_sys_code);
int sortno = 0;
foreach (var codeset in codesets)
{
var codeistm = new code_set_entity
{
//code_id = "879 ",
code = codeset.code,
name = codeset.name,
show_name = codeset.show_name,
spell_code = "",
wb_code = "",
code_sys_id = code_sys_id,
is_std = true,
std_class_id = 0,
unit_id = 0,
unit_name = null,
value_type = 0,
range = null,
range_low = null,
range_high = null,
note = item.show_name,
quote_code_id = 0,
state = 1,
sort_no = ++sortno,
oper_id = 3,
oper_time = DateTime.Now,
etl_time = DateTime.Now,
tenant_id = 0,
is_valid = true
};
conn.Insert <code_set_entity>(codeistm);
}
}
#region 拼音码、五笔码
//生成拼音、五笔码
conn.Execute("update mdm.code_set set spell_code = substring(get_pym_pg(name) from 0 for 20) ,wb_code = substring(get_wbm(name) from 0 for 20) ", commandTimeout: 300);
conn.Execute("update mdm.code_domain set spell_code = substring(get_pym_pg(domain_name) from 0 for 20) ,wb_code = substring(get_wbm(domain_name) from 0 for 20)", commandTimeout: 300);
conn.Execute("update mdm.code_system set spell_code = substring(get_pym_pg(code_sys_name) from 0 for 20),wb_code = substring(get_wbm(code_sys_name) from 0 for 20) ", commandTimeout: 300);
#endregion
}
#endregion
}
public void InsertCodeSet(string mdmexcelpath, string dbConnName, string sheetName)
{
Workbook workbook = null;
IList <code_set_excel_entity> codesetexcels = new List <code_set_excel_entity>();
using (FileStream fs = new FileStream(mdmexcelpath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
workbook = new Workbook(fs);
}
if (string.IsNullOrEmpty(sheetName))
{
sheetName = "代码明细";
}
Worksheet sheet = workbook.Worksheets[sheetName];
var cells = sheet.Cells;
for (int i = 0; i < cells.MaxDataRow; i++)
{
var row = cells.CheckRow(i);
if (row == null || row.FirstCell == null)
{
continue;
}
if (row.GetCellOrNull(0).StringValue == "代码系统编码")
{
continue;
}
else
{
var aaa = new code_set_excel_entity();
aaa.code_sys_code = row.GetCellOrNull(0).StringValue;
aaa.code_sys_name = row.GetCellOrNull(1).StringValue;
aaa.code = row.GetCellOrNull(2).StringValue;
aaa.name = row.GetCellOrNull(3).StringValue;
aaa.memo = row.GetCellOrNull(4).StringValue;
codesetexcels.Add(aaa);
}
}
if (string.IsNullOrEmpty(dbConnName))
{
dbConnName = "hdr";
}
var connstr = System.Configuration.ConfigurationManager.ConnectionStrings[dbConnName].ConnectionString;
//select setval(pg_get_serial_sequence('mdm.code_set', 'code_id'), max(code_id), true) from mdm.code_set 设置序列最大值
//删除数据
//delete from mdm.code_set where etl_time > current_TIMESTAMP - interval '1 days'
using (NpgsqlConnection conn = new NpgsqlConnection(connstr))
{
foreach (var item in codesetexcels)
{
var code_sys_id = conn.Query <int>($"select code_sys_id from mdm.code_system where code_sys_code = '{item.code_sys_code}' and oper_time > current_TIMESTAMP - interval '1 days'").FirstOrDefault();
var codeistm = new code_set_entity
{
//code_id = "879 ",
code = item.code,
name = item.name,
show_name = item.name,
spell_code = "",
wb_code = "",
code_sys_id = code_sys_id,
is_std = true,
std_class_id = 0,
unit_id = 0,
unit_name = null,
value_type = 0,
range = null,
range_low = null,
range_high = null,
note = item.memo,
quote_code_id = 0,
state = 1,
sort_no = 1,
oper_id = 3,
oper_time = DateTime.Now,
etl_time = DateTime.Now,
tenant_id = 0,
is_valid = true
};
conn.Insert <code_set_entity>(codeistm);
}
}
}