Esempio n. 1
0
        /// <summary>
        /// 导入
        /// </summary>
        /// <param name="obj"></param>
        /// <param name="httpFiles"></param>
        /// <param name="fieldName"></param>
        /// <param name="columnName"></param>
        /// <returns></returns>

        public OpResult Import(ImportSet obj, System.Web.HttpFileCollectionBase httpFiles, string fieldName, string columnName)
        {
            var op    = new OpResult();
            var errLs = new List <string>();
            int count = 0;

            try
            {
                Dictionary <string, char> fieldCols = null;
                DataTable dt = null;
                op = ImportSetService.ImportSet(obj, httpFiles, fieldName, columnName, ref fieldCols, ref dt);
                if (!op.Successed)
                {
                    return(op);
                }

                //数据字典
                var dataDictionary = SysDataDictionaryRepository.GetQuery();

                //设备分类名称
                var CIdx = Convert.ToInt32(fieldCols["CategoryId"]) - 65;
                //设备名称
                var TitleIdx = Convert.ToInt32(fieldCols["Title"]) - 65;
                //品牌
                var BrandIdx = Convert.ToInt32(fieldCols["Brand"]) - 65;
                //型号
                var SpecIdx = Convert.ToInt32(fieldCols["Spec"]) - 65;

                count = dt.Rows.Count;
                for (int i = dt.Rows.Count - 1; i >= 0; i--)
                {
                    try
                    {
                        var dr = dt.Rows[i];
                        #region 验证
                        //设备分类名称
                        var text = dr[CIdx].ToString().Trim();
                        if (text.IsNullOrEmpty())
                        {
                            errLs.Add("行号[" + (i + 1) + "]设备分类为空!");
                            dt.Rows.RemoveAt(i);
                            continue;
                        }
                        var dd = dataDictionary.Where(o => o.DicPSN == 197 && o.Title == text);
                        if (!dd.Any())
                        {
                            errLs.Add("行号[" + (i + 1) + "]设备分类在字典中不存在,请先添加!");
                            dt.Rows.RemoveAt(i);
                            continue;
                        }
                        else
                        {
                            dr[CIdx] = dd.Select(o => o.DicSN).FirstOrDefault();
                        }

                        //设备名称
                        var text2 = dr[TitleIdx].ToString().Trim();
                        if (text2.IsNullOrEmpty())
                        {
                            errLs.Add("行号[" + (i + 1) + "]设备名称为空!");
                            dt.Rows.RemoveAt(i);
                            continue;
                        }

                        //品牌
                        var text3 = dr[BrandIdx].ToString().Trim();
                        if (text3.IsNullOrEmpty())
                        {
                            errLs.Add("行号[" + (i + 1) + "]品牌为空!");
                            dt.Rows.RemoveAt(i);
                            continue;
                        }


                        //型号
                        var text4 = dr[SpecIdx].ToString().Trim();
                        if (text4.IsNullOrEmpty())
                        {
                            errLs.Add("行号[" + (i + 1) + "]型号为空!");
                            dt.Rows.RemoveAt(i);
                            continue;
                        }

                        //设备分类ID
                        var cid     = dataDictionary.Where(o => o.DicPSN == 197 && o.Title == text).Select(o => o.DicSN).FirstOrDefault();
                        var isExist = DevicesRepository.GetQuery(o => o.CategoryId == cid && o.Title == text2 && o.Brand == text3 && o.Spec == text4);
                        if (isExist.Any())
                        {
                            errLs.Add("行号[" + (i + 1) + "]设备记录已经存在!");
                            dt.Rows.RemoveAt(i);
                            continue;
                        }

                        #endregion
                    }
                    catch (Exception e)
                    {
                        throw new Exception("创建相关记录失败," + e.Message, e);
                    }
                }
                StringBuilder sb = new StringBuilder();
                sb.Append("begin tran ");
                foreach (DataRow dr in dt.Rows)
                {
                    Devices devices = new Devices();
                    devices.DeviceId  = CommonService.GUID.ToUpper();
                    devices.CreateUID = CurrentUser.UID;
                    devices.CreateDT  = DateTime.Now;

                    sb.Append("insert into ");
                    sb.Append(obj.TableName);
                    sb.Append("(Status,DeviceId,CreateDT,CreateUID,");
                    sb.Append(string.Join(",", fieldCols.Keys));
                    sb.Append(") values(1,'" + devices.DeviceId + "','" + devices.CreateDT + "','" + devices.CreateUID + "',");
                    foreach (var de in fieldCols)
                    {
                        var index = Convert.ToInt32(de.Value) - 65;
                        try
                        {
                            var text = dr[index].ToString().Trim();
                            sb.Append("'" + text + "',");
                        }
                        catch (Exception e)
                        {
                            throw new Exception("列选择超过范围!", e);
                        }
                    }
                    sb = sb.Remove(sb.Length - 1, 1);
                    sb.Append(");");
                }
                sb.Append(" commit tran");
                new DBFramework.DBHelper().ExecuteNonQueryText(sb.ToString(), null);
            }
            catch (Exception ex)
            {
                op.Message   = ex.Message;
                op.Successed = false;
                LogEngine.WriteError(ex);
                errLs.Add("导入出现异常!");
            }
            return(CommonService.GenerateImportHtml(errLs, count));
        }
Esempio n. 2
0
        public OpResult Import(ImportSet obj, System.Web.HttpFileCollectionBase httpFiles, string fieldName, string columnName)
        {
            var op    = new OpResult();
            var errLs = new List <string>();
            int count = 0;

            try
            {
                Dictionary <string, char> fieldCols = null;
                DataTable dt = null;
                op = ImportSetService.ImportSet(obj, httpFiles, fieldName, columnName, ref fieldCols, ref dt);
                if (!op.Successed)
                {
                    return(op);
                }
                var brandClass = DataDictRepository.GetQuery(o => o.DicPSN == 5).ToList();
                var otherClass = brandClass.FirstOrDefault(o => o.Title.StartsWith("其"));
                var brands     = GetList();
                var clsIdx     = Convert.ToInt32(fieldCols["ClassifyId"]) - 65;
                var titleIdx   = Convert.ToInt32(fieldCols["Title"]) - 65;
                count = dt.Rows.Count;
                var max = DataDictRepository.GetQuery().Max(o => o.DicSN);
                for (int i = dt.Rows.Count - 1; i >= 0; i--)
                {
                    try
                    {
                        var dr   = dt.Rows[i];
                        var text = dr[clsIdx].ToString();
                        if (text.IsNullOrEmpty())
                        {
                            continue;
                        }
                        var cls = brandClass.FirstOrDefault(o => o.Title == text);
                        if (cls != null)
                        {
                            dr[clsIdx] = cls.DicSN.ToString();
                        }
                        else
                        {
                            if (obj.RefCreate)
                            {
                                var data = new SysDataDictionary
                                {
                                    DicPSN    = 5,
                                    Status    = true,
                                    Title     = text,
                                    DicSN     = max++,
                                    CreateDT  = DateTime.Now,
                                    CreateUID = CurrentUser.UID
                                };
                                DataDictRepository.Add(data);
                                brandClass.Add(data);
                                dr[clsIdx] = data.DicSN.ToString();
                            }
                            else if (otherClass != null)
                            {
                                dr[clsIdx] = otherClass.DicSN.ToString();
                            }
                            else
                            {
                                errLs.Add("行业分类[" + text + "]不存在!");
                                dt.Rows.RemoveAt(i);//去除不导入
                                continue;
                            }
                        }
                        text = dr[titleIdx].ToString().Trim();
                        if (brands.Any(o => o.Title == text))
                        {
                            errLs.Add("品牌名称[" + text + "]已存在!");
                            dt.Rows.RemoveAt(i);//去除不导入
                        }
                        else
                        {
                            brands.Add(new ProductBrand()
                            {
                                Title = text
                            });
                        }
                    }
                    catch (Exception e)
                    {
                        throw new Exception("品牌分类处理失败!", e);
                    }
                }
                var           maxsn = BrandRepository.GetQuery().Max(o => (int?)o.BrandSN).GetValueOrDefault();
                StringBuilder sb    = new StringBuilder();
                sb.Append("begin tran ");
                foreach (DataRow dr in dt.Rows)
                {
                    sb.Append("insert into ");
                    sb.Append(obj.TableName);
                    sb.Append("(BrandSN,State,Source,");
                    sb.Append(string.Join(",", fieldCols.Keys));
                    sb.Append(") values(");
                    sb.AppendFormat("{0},", ++maxsn);
                    sb.AppendFormat("1,0,");
                    foreach (var de in fieldCols)
                    {
                        var index = Convert.ToInt32(de.Value) - 65;
                        try
                        {
                            var text = dr[index].ToString();
                            sb.Append("'" + text + "',");
                        }
                        catch (Exception e)
                        {
                            throw new Exception("列选择超过范围!", e);
                        }
                    }
                    sb = sb.Remove(sb.Length - 1, 1);
                    sb.Append(");");
                }
                sb.Append(" commit tran");
                if (dt.Rows.Count > 0)
                {
                    op.Successed = new DBHelper().ExecuteNonQueryText(sb.ToString(), null) > 0;
                    LogEngine.WriteInsert("品牌导入", LogModule.档案管理);
                }
            }
            catch (Exception ex)
            {
                op.Message   = ex.Message;
                op.Successed = false;
                LogEngine.WriteError(ex);
                errLs.Add("导入出现异常!");
            }
            return(CommonService.GenerateImportHtml(errLs, count));
        }
Esempio n. 3
0
        public OpResult Import(ImportSet obj, System.Web.HttpFileCollectionBase httpFiles, string fieldName, string columnName)
        {
            var op    = new OpResult();
            var errLs = new List <string>();
            int count = 0;

            try
            {
                Dictionary <string, char> fieldCols = null;
                DataTable dt = null;
                op = ImportSetService.ImportSet(obj, httpFiles, fieldName, columnName, ref fieldCols, ref dt);
                if (!op.Successed)
                {
                    return(op);
                }
                var categorys       = ProductCategoryService.GetList();
                var otherClass      = categorys.FirstOrDefault(o => o.Title.StartsWith("其"));
                var brands          = BrandService.GetList();
                var otherBrand      = brands.FirstOrDefault(o => o.Title.StartsWith("其"));
                var brandClass      = DictionaryService.GetChildList(5);
                var otherBrandClass = brandClass.FirstOrDefault(o => o.Title.StartsWith("其"));//?
                var units           = DictionaryService.GetSubUnitCategories();
                var otherUnit       = units.FirstOrDefault(o => o.Title.StartsWith("其"));
                var products        = GetList();
                var maxCate         = ProductCategoryService.MaxSN() + 1;
                var maxBrand        = BrandService.MaxSN() + 1;
                var maxDict         = DictionaryService.MaxSN() + 1;

                var clsIdx   = Convert.ToInt32(fieldCols["CategorySN"]) - 65;
                var brandIdx = Convert.ToInt32(fieldCols["BrandSN"]) - 65;
                var unitIdx  = Convert.ToInt32(fieldCols["SubUnitId"]) - 65;
                var titleIdx = Convert.ToInt32(fieldCols["Barcode"]) - 65;
                count = dt.Rows.Count;
                for (int i = dt.Rows.Count - 1; i >= 0; i--)
                {
                    try
                    {
                        var dr = dt.Rows[i];
                        #region 条码验证
                        var text = dr[titleIdx].ToString();
                        if (text.IsNullOrEmpty())
                        {
                            errLs.Add("行号[" + i + "]条码为空!");
                            dt.Rows.RemoveAt(i);
                            continue;
                        }
                        else if (products.Any(o => o.Barcode == text))
                        {
                            errLs.Add("条码[" + text + "]已存在!");
                            dt.Rows.RemoveAt(i);
                            continue;
                        }
                        else
                        {
                            products.Add(new ProductRecord()
                            {
                                Barcode = text
                            });
                        }
                        #endregion
                        #region 处理类别
                        text = dr[clsIdx].ToString();
                        if (!text.IsNullOrEmpty() && text.Contains("/"))
                        {
                            var cates  = text.Split('/');
                            var first  = cates.FirstOrDefault();
                            var third  = cates.LastOrDefault();
                            var second = "";
                            if (cates.Length == 3)
                            {
                                second = cates[1];
                                third  = cates[2];
                            }
                            else if (cates.Length == 2)
                            {
                                second = cates[1];
                                third  = null;
                            }
                            else if (cates.Length > 3)
                            {
                                second = cates[1];
                                third  = text.Replace(first + "/" + second + "/", "");
                            }
                            var parent = categorys.FirstOrDefault(o => o.Title == first);

                            var cls = parent != null?categorys.FirstOrDefault(o => o.CategoryPSN == parent.CategorySN && o.Title == second) : null;

                            cls = cls != null && !third.IsNullOrEmpty() ? categorys.FirstOrDefault(o => o.CategoryPSN == cls.CategorySN && o.Title == third) : cls;
                            if (cls != null)
                            {
                                dr[clsIdx] = cls.CategorySN.ToString();
                            }
                            else
                            {
                                if (obj.RefCreate)
                                {
                                    var list = new List <ProductCategory>();
                                    cls = categorys.FirstOrDefault(o => o.Title == first);
                                    int   psn   = 0;
                                    short grade = 2;
                                    if (cls == null)
                                    {
                                        parent = new ProductCategory()
                                        {
                                            CategorySN   = maxCate,
                                            Title        = first,
                                            CategoryCode = 1,
                                            Grade        = 1,
                                            State        = 1,
                                        };
                                        list.Add(parent);
                                        psn = parent.CategorySN;
                                        var child = new ProductCategory()
                                        {
                                            CategoryPSN  = psn,
                                            CategorySN   = ++maxCate,
                                            Title        = second,
                                            CategoryCode = 1,
                                            Grade        = grade,
                                            State        = 1,
                                        };
                                        list.Add(child);
                                        psn   = child.CategorySN;
                                        grade = 3;
                                        if (!third.IsNullOrEmpty())
                                        {
                                            child = new ProductCategory()
                                            {
                                                CategoryPSN  = psn,
                                                CategorySN   = ++maxCate,
                                                Title        = third,
                                                CategoryCode = 1,
                                                Grade        = grade,
                                                State        = 1,
                                            };
                                            psn = child.CategorySN;
                                            list.Add(child);
                                        }
                                    }
                                    else
                                    {
                                        psn = cls.CategorySN;
                                        cls = categorys.FirstOrDefault(o => o.Title == second);
                                        if (cls == null)
                                        {
                                            var maxcode = ProductCategoryService.MaxCode(psn);
                                            var child   = new ProductCategory()
                                            {
                                                CategoryPSN  = psn,
                                                CategorySN   = ++maxCate,
                                                Title        = second,
                                                CategoryCode = ++maxcode,
                                                Grade        = grade,
                                                State        = 1
                                            };
                                            list.Add(child);
                                            psn   = child.CategorySN;
                                            grade = 3;
                                        }
                                        else
                                        {
                                            psn = cls.CategorySN;
                                        }
                                        if (!third.IsNullOrEmpty())
                                        {
                                            cls = categorys.FirstOrDefault(o => o.Title == third);
                                            if (cls == null)
                                            {
                                                var maxcode = ProductCategoryService.MaxCode(psn);
                                                var child   = new ProductCategory()
                                                {
                                                    CategoryPSN  = psn,
                                                    CategorySN   = ++maxCate,
                                                    Title        = third,
                                                    CategoryCode = ++maxcode,
                                                    Grade        = grade,
                                                    State        = 1
                                                };
                                                list.Add(child);
                                                psn = child.CategorySN;
                                            }
                                            else
                                            {
                                                psn = cls.CategorySN;
                                            }
                                        }
                                    }
                                    ProductCategoryService.AddRange(list);
                                    categorys.AddRange(list);
                                    dr[clsIdx] = psn.ToString();
                                }
                                else if (otherClass != null)
                                {
                                    dr[clsIdx] = otherClass.CategorySN.ToString();
                                }
                                else
                                {
                                    errLs.Add("条码[" + dr[titleIdx] + "]类别不存在!");
                                    dt.Rows.RemoveAt(i);//去除不导入
                                    continue;
                                }
                            }
                        }
                        else
                        {
                            errLs.Add("条码[" + dr[titleIdx] + "]类别不存在!");
                            dt.Rows.RemoveAt(i);//去除不导入
                            continue;
                        }
                        #endregion
                        #region 处理品牌
                        text = dr[brandIdx].ToString();
                        if (!text.IsNullOrEmpty())
                        {
                            var cls = brands.FirstOrDefault(o => o.Title == text);
                            if (cls != null)
                            {
                                dr[brandIdx] = cls.BrandSN.ToString();
                            }
                            else
                            {
                                if (obj.RefCreate)
                                {
                                    if (otherBrandClass == null)
                                    {
                                        errLs.Add("条码[" + dr[titleIdx] + "]品牌分类不存在!");
                                        dt.Rows.RemoveAt(i);
                                        continue;
                                    }
                                    var data = new ProductBrand()
                                    {
                                        //BrandSN = maxBrand++,
                                        Title      = text,
                                        ClassifyId = otherBrandClass.DicSN,
                                        JianPin    = "",
                                        State      = 1
                                    };
                                    BrandService.SaveOrUpdate(data);
                                    brands.Add(data);
                                    dr[brandIdx] = data.BrandSN.ToString();
                                }
                                else if (otherBrand != null)
                                {
                                    dr[brandIdx] = otherBrand.BrandSN.ToString();
                                }
                                else
                                {
                                    errLs.Add("条码[" + dr[titleIdx] + "]品牌不存在!");
                                    dt.Rows.RemoveAt(i);
                                    continue;
                                }
                            }
                        }
                        #endregion
                        #region 处理单位
                        text = dr[unitIdx].ToString();
                        if (!text.IsNullOrEmpty())
                        {
                            var cls = units.FirstOrDefault(o => o.Title == text);
                            if (cls != null)
                            {
                                dr[unitIdx] = cls.DicSN.ToString();
                            }
                            else
                            {
                                if (obj.RefCreate)
                                {
                                    var data = new SysDataDictionary()
                                    {
                                        DicPSN = 4,
                                        //DicSN = maxDict++,
                                        Status = true,
                                        Title  = text,
                                    };
                                    DictionaryService.SaveOrUpdate(data);
                                    units.Add(data);
                                    dr[unitIdx] = data.DicSN.ToString();
                                }
                                else if (otherUnit != null)
                                {
                                    dr[unitIdx] = otherUnit.DicSN.ToString();
                                }
                                else
                                {
                                    errLs.Add("条码[" + dr[titleIdx] + "]单位不存在!");
                                    dt.Rows.RemoveAt(i);
                                    continue;
                                }
                            }
                        }
                        #endregion
                    }
                    catch (Exception e)
                    {
                        throw new Exception("创建相关记录失败," + e.Message, e);
                    }
                }
                StringBuilder sb = new StringBuilder();
                sb.Append("begin tran ");
                foreach (DataRow dr in dt.Rows)
                {
                    sb.Append("insert into ");
                    sb.Append(obj.TableName);
                    sb.Append("(State,");
                    sb.Append(string.Join(",", fieldCols.Keys));
                    sb.Append(") values(1,");
                    foreach (var de in fieldCols)
                    {
                        var index = Convert.ToInt32(de.Value) - 65;
                        try
                        {
                            var text = dr[index].ToString().Trim();
                            sb.Append("'" + text + "',");
                        }
                        catch (Exception e)
                        {
                            throw new Exception("列选择超过范围!", e);
                        }
                    }
                    sb = sb.Remove(sb.Length - 1, 1);
                    sb.Append(");");
                }
                sb.Append(" commit tran");
                if (dt.Rows.Count > 0)
                {
                    new DBFramework.DBHelper().ExecuteNonQueryText(sb.ToString(), null);
                }
            }
            catch (Exception ex)
            {
                op.Message   = ex.Message;
                op.Successed = false;
                LogEngine.WriteError(ex);
                errLs.Add("导入出现异常!");
            }
            return(CommonService.GenerateImportHtml(errLs, count));
        }