Пример #1
0
        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
        }
Пример #2
0
        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);
                }
            }
        }