/// <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));
}
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));
}
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));
}