public static OpResult Import(ImportSet obj, System.Web.HttpFileCollectionBase httpFiles, string fieldName, string columnName)
{
var op = new OpResult();
var errLs = new List <string>();
int count = 0, rowno = 0;
var dt = new DataTable();
try
{
Dictionary <string, char> fieldCols = null;
op = ImportSetService.ImportSet(obj, httpFiles, fieldName, columnName, ref fieldCols, ref dt);
if (!op.Successed)
{
return(op);
}
var users = UserInfoService.GetList();
var barcodeIdx = Convert.ToInt32(fieldCols["Barcode"]) - 65;
var barcodes = dt.AsEnumerable().Select(o => o[barcodeIdx].ToString()).Distinct().ToList();
var payments = new List <ConsumptionPayment>();
foreach (var de in fieldCols)
{
var idx = Convert.ToInt32(de.Value) - 65;
var col = dt.Columns[idx];
if (!dt.Columns.Contains(de.Key))
{
col.ColumnName = de.Key;
}
}
dt.Columns.Add("ApiTitle");
dt.Columns.Add("ValuationType");
dt.Columns.Add("ProductCode");
dt.Columns.Add("BuyPrice");
dt.Columns.Add("StoreId");
dt.Columns.Add("SalesClassifyId2");
dt.Columns.Add("CreateUID2");
dt.Columns.Add("Salesman2");
dt.Columns.Add("InInventory");
dt.Columns.Add("Sort", typeof(int));
object ApiOrderSN = "", ApiTitle = "", ApiCode_11 = 0, ApiCode_12 = 0, ApiCode_20 = 0, ApiCode_21 = 0, ApiCode_19 = 0, PreferentialPrice = 0, Change = 0, WipeZero = 0, TotalAmount = 0, Type = 0;
int sort = 1;
if (!dt.Columns.Contains("ApiOrderSN"))
{
dt.Rows.Clear();
errLs.Add("销售流水号位置未指定!");
}
else
{
var apiOrderSns = dt.AsEnumerable().Select(o => o["ApiOrderSN"].ToString()).Distinct().Where(o => !o.IsNullOrEmpty()).ToList();
payments = ConsumptionPaymentService.FindList(o => apiOrderSns.Contains(o.ApiOrderSN));
}
if (!dt.Columns.Contains("SaleDate"))
{
dt.Columns.Add("SaleDate", typeof(DateTime));
foreach (DataRow dr in dt.Rows)
{
dr["SaleDate"] = DateTime.Now;
}
}
for (int i = 0; i < dt.Rows.Count; i++)
{
var dr = dt.Rows[i];
var ApiOrderSNCur = dr.GetValue("ApiOrderSN"); var ChangeCur = dr.GetValue("Change");
var ApiCode_11Cur = dr.GetValue("ApiCode_11"); var ApiCode_12Cur = dr.GetValue("ApiCode_12");
var ApiCode_20Cur = dr.GetValue("ApiCode_20"); var ApiCode_21Cur = dr.GetValue("ApiCode_21");
var ApiCode_19Cur = dr.GetValue("ApiCode_19"); var PreferentialPriceCur = dr.GetValue("PreferentialPrice");
var WipeZeroCur = dr.GetValue("WipeZero"); var TotalAmountCur = dr.GetValue("TotalAmount");
var TypeCur = dr.GetValue("Type");
var apiTitleCur = "";
if (!(ApiCode_11Cur is DBNull))
{
apiTitleCur += "现金,";
}
if (!(ApiCode_12Cur is DBNull))
{
apiTitleCur += "银联,";
}
if (!(ApiCode_20Cur is DBNull))
{
apiTitleCur += "支付宝,";
}
if (!(ApiCode_21Cur is DBNull))
{
apiTitleCur += "微信,";
}
if (!(ApiCode_19Cur is DBNull))
{
apiTitleCur += "即付宝,";
}
if (dr.GetValue("ApiOrderSN") is DBNull)
{
dr["ApiOrderSN"] = ApiOrderSN; dr.SetValue("Change", Change);
dr.SetValue("ApiCode_11", ApiCode_11); dr.SetValue("ApiCode_12", ApiCode_12);
dr.SetValue("ApiCode_20", ApiCode_20); dr.SetValue("ApiCode_21", ApiCode_21);
dr.SetValue("ApiCode_19", ApiCode_19); dr.SetValue("PreferentialPrice", PreferentialPrice);
dr.SetValue("WipeZero", WipeZero); dr.SetValue("TotalAmount", TotalAmount);
dr.SetValue("ApiTitle", ApiTitle); dr.SetValue("Type", Type);
sort++;
}
else
{
ApiOrderSN = ApiOrderSNCur; Change = ChangeCur;
ApiCode_11 = ApiCode_11Cur; ApiCode_12 = ApiCode_12Cur;
ApiCode_20 = ApiCode_20Cur; ApiCode_21 = ApiCode_21Cur;
ApiCode_19 = ApiCode_19Cur; PreferentialPrice = PreferentialPriceCur;
WipeZero = WipeZeroCur; TotalAmount = TotalAmountCur;
dr["ApiTitle"] = ApiTitle = apiTitleCur.TrimEnd(',');
sort = 1; Type = TypeCur;
}
//dr["Receive"] = (ApiCode_11Cur is DBNull ? 0 : ApiCode_11Cur.ToType<decimal>()) +
// (ApiCode_12Cur is DBNull ? 0 : ApiCode_12Cur.ToType<decimal>()) +
// (ApiCode_20Cur is DBNull ? 0 : ApiCode_20Cur.ToType<decimal>()) +
// (ApiCode_21Cur is DBNull ? 0 : ApiCode_21Cur.ToType<decimal>()) +
// (ApiCode_19Cur is DBNull ? 0 : ApiCode_19Cur.ToType<decimal>()) -
// (ChangeCur is DBNull ? 0 : ChangeCur.ToType<decimal>());
dr["StoreId"] = System.Web.HttpContext.Current.Request["storeId"];
dr["InInventory"] = System.Web.HttpContext.Current.Request["InInventory"];
dr["Sort"] = sort;
}
var removeDrs = new List <DataRow>();
var apisns = dt.AsEnumerable().GroupBy(o => o["ApiOrderSN"].ToString()).Select(o => o.Key).ToList();
var products = ProductService.GetProductsBybars(barcodes);
count = apisns.Count;
rowno = obj.MinRow;
for (int i = 0; i < apisns.Count; i++)
{
var apiSn = apisns[i];
var drs = dt.Select("ApiOrderSN='" + apiSn + "'");
if (payments.Any(o => o.ApiOrderSN == apiSn))
{
errLs.Add("行号[" + rowno + "] 流水号[" + apiSn + "]已存在!");
removeDrs.AddRange(drs);
rowno += drs.Length;
continue;
}
var receive = drs.Max(o => o.GetValue("Receive").ToType <decimal>());
int j = 0;
decimal totalAmount = 0;
bool haszs = false;
foreach (DataRow dr in drs)
{
var text = dr["Barcode"].ToString();
if (text.IsNullOrEmpty())
{
errLs.Add("行号[" + (rowno + j) + "]条码不存在!");
removeDrs.AddRange(drs);
break;
}
//var pro = products.FirstOrDefault(o => o.Barcode == text || ("," + o.Barcodes + ",").Contains("," + text + ","));
var pro = products.Select(string.Format("Barcode='{0}' or ','+Barcodes+',' like '%,{0},%'", text));
if (pro.Length <= 0)
{
errLs.Add("行号[" + (rowno + j) + "] 流水号[" + apiSn + "] 条码[" + text + "]档案不存在!");
removeDrs.AddRange(drs);
break;
}
dr["ProductCode"] = pro[0]["ProductCode"];
dr["BuyPrice"] = pro[0]["BuyPrice"];
dr["ValuationType"] = pro[0]["ValuationType"];
text = dr.GetValue("Type").ToString();
dr.SetValue("Type", text == "换货" ? "1" : text == "退货" ? "2" : text == "退单" ? "3" : "0");
text = dr.GetValue("CreateUID").ToString();
if (!text.IsNullOrEmpty() && users.Any(o => o.FullName == text || o.UserCode == text))
{
var user = users.FirstOrDefault(o => o.FullName == text || o.UserCode == text);
dr["CreateUID2"] = user == null ? "" : user.UID;
if (user == null)
{
dr["CreateUID"] = "";
}
}
else
{
errLs.Add("行号[" + (rowno + j) + "] 流水号[" + apiSn + "] 收银员" + (text.IsNullOrEmpty() ? "为空" : "[" + text + "]档案不存在!"));
removeDrs.AddRange(drs);
break;
}
text = dr.GetValue("Salesman").ToString();
if (!text.IsNullOrEmpty() && users.Any(o => o.FullName == text || o.UserCode == text))
{
var user = users.FirstOrDefault(o => o.FullName == text || o.UserCode == text);
dr["Salesman2"] = user == null ? "" : user.UID;
if (user == null)
{
dr["Salesman"] = "";
}
}
else
{
errLs.Add("行号[" + (rowno + j) + "] 流水号[" + apiSn + "] 导购员" + (text.IsNullOrEmpty() ? "为空" : "[" + text + "]档案不存在!"));
removeDrs.AddRange(drs);
break;
}
text = dr.GetValue("SalesClassifyId").ToString();
dr["SalesClassifyId2"] = text == "赠送" ? "49" : "47";
if (text == "赠送")
{
dr.SetValue("SubTotal", 0);
dr.SetValue("ActualPrice", 0);
if (receive == 0)
{
dr.SetValue("TotalAmount", 0);
}
haszs = true;
}
totalAmount += dr.GetValue("ActualPrice").ToType <decimal>() * dr.GetValue("PurchaseNumber").ToType <decimal>();
j++;
}
if (receive > 0 && haszs)
{
drs.Each(dr =>
{
dr.SetValue("TotalAmount", totalAmount);//重算应付金额
});
}
rowno += drs.Length;
}
foreach (var dr in removeDrs)
{
try { dt.Rows.Remove(dr); }
catch { }
}
saleOrderCache.Set(CacheKey, dt);
}
catch (Exception ex)
{
op.Message = ex.Message;
op.Successed = false;
Log.WriteError(ex);
errLs.Add("导入出现异常!");
}
return(CommonService.GenerateImportHtml(errLs, count));
}
public static OpResult OutboundImport(ImportSet obj, System.Web.HttpFileCollectionBase httpFiles, string fieldName, string columnName)
{
var op = new OpResult();
var errLs = new List <string>();
int count = 0;
var list = new List <OutboundList>();
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 storeId = System.Web.HttpContext.Current.Request["StoreId"];
var products = new List <VwProduct>();
if (!storeId.IsNullOrEmpty())
{
var ware = WarehouseService.Find(o => o.StoreId == storeId && o.CompanyId == CommonService.CompanyId);
if (ware != null)
{
var categorySNs = ware.CategorySN.Split(',').Select(o => int.Parse(o)).ToList();
var childs = ProductCategoryService.GetChildSNs(categorySNs);
var pros = BaseService <VwProduct> .FindList(o => o.CompanyId == CommonService.CompanyId && childs.Contains(o.CategorySN));
products.AddRange(pros);
}
}
var barcodeIdx = fieldCols.GetValue("Barcode").ToType <int>() - 65;
var outPriceIdx = fieldCols.GetValue("OutPrice").ToType <int>() - 65;
var numberIdx = fieldCols.GetValue("OutboundNumber").ToType <int>() - 65;
var memoIdx = fieldCols.GetValue("Memo").ToType <int>() - 65;
count = dt.Rows.Count;
for (int i = 0; i < dt.Rows.Count; i++)
{
var pos = i + obj.MinRow;
var dr = dt.Rows[i];
var barcode = dr.GetValue(barcodeIdx).ToString().Trim();
if (barcode.IsNullOrEmpty())
{
errLs.Add("行号[" + pos + "]条码为空!");
continue;
}
var pro = products.FirstOrDefault(o => o.Barcode == barcode || ("," + o.Barcodes + ",").Contains("," + barcode + ","));
if (pro == null)
{
errLs.Add("行号[" + pos + "]该门店下无此条码!");
continue;
}
var outPrice = dr.GetValue(outPriceIdx).ToType <decimal?>();
if (!outPrice.HasValue && outPriceIdx >= 0)
{
errLs.Add("行号[" + pos + "]该条码价格为空!");
continue;
}
var number = dr.GetValue(numberIdx).ToType <decimal?>();
if (!number.HasValue)
{
errLs.Add("行号[" + pos + "]该条码出库数量为空!");
continue;
}
list.Add(new OutboundList()
{
Barcode = barcode,
ProductTitle = pro.Title,
BuyPrice = pro.BuyPrice,
OutboundNumber = number.Value,
Unit = pro.SubUnit,
SysPrice = pro.SysPrice,
OutPrice = outPrice ?? pro.SysPrice,
Memo = dr.GetValue(memoIdx).ToString()
});
}
}
catch (Exception ex)
{
op.Message = ex.Message;
op.Successed = false;
Log.WriteError(ex);
errLs.Add("导入出现异常!");
}
return(CommonService.GenerateImportHtml(errLs, count, data: list, isSuccess: false));
}
public static 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 = SysDataDictService.FindList(o => o.DicPSN == (int)DicType.品牌分类 && o.CompanyId == CommonService.CompanyId);
var otherClass = brandClass.FirstOrDefault(o => o.Title.StartsWith("其"));
var brands = GetAllProductBrands(null).ToList();
var max = SysDataDictService.GetMaxSN;
var clsIdx = Convert.ToInt32(fieldCols["ClassifyId"]) - 65;
var titleIdx = Convert.ToInt32(fieldCols["Title"]) - 65;
count = dt.Rows.Count;
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 Sys.Entity.SysDataDictionary()
{
DicPSN = (int)DicType.品牌分类,
DicSN = max++,
Status = true,
Title = text,
CompanyId = CommonService.CompanyId
};
SysDataDictService.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);
}
}
max = MaxSN;
StringBuilder sb = new StringBuilder();
sb.Append("begin tran ");
foreach (DataRow dr in dt.Rows)
{
sb.Append("insert into ");
sb.Append(obj.TableName);
sb.Append("(CompanyId,BrandSN,State,");
sb.Append(string.Join(",", fieldCols.Keys));
sb.Append(") values(");
sb.AppendFormat("{0},", obj.CompanyId);
sb.AppendFormat("{0},", max++);
sb.AppendFormat("1,");
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");
op.Successed = new Pharos.Logic.DAL.CommonDAL()._db.ExecuteNonQueryText(sb.ToString(), null) > 0;
Log.WriteInsert("品牌导入", Sys.LogModule.档案管理);
}
catch (Exception ex)
{
op.Message = ex.Message;
op.Successed = false;
Log.WriteError(ex);
errLs.Add("导入出现异常!");
}
return(CommonService.GenerateImportHtml(errLs, count));
}
public static OpResult InboundImport(ImportSet obj, System.Web.HttpFileCollectionBase httpFiles, string fieldName, string columnName)
{
var op = new OpResult();
var errLs = new List <string>();
int count = 0;
var list = new List <InboundList>();
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 supplierId = System.Web.HttpContext.Current.Request["SupplierID"];
var storeId = System.Web.HttpContext.Current.Request["StoreId"];
var supplierBarcodes = new List <string>();
var storeBarcodes = new List <string>();
var products = new List <VwProduct>();
if (!supplierId.IsNullOrEmpty())
{
var bars = BaseService <ProductMultSupplier> .FindList(o => o.SupplierId == supplierId).Select(o => o.Barcode).Distinct().ToList();
var pros = BaseService <VwProduct> .FindList(o => o.SupplierId == supplierId || bars.Contains(o.Barcode));
products.AddRange(pros);
supplierBarcodes = pros.Select(o => o.Barcode).ToList();
supplierBarcodes.AddRange(pros.Where(o => !o.Barcodes.IsNullOrEmpty()).SelectMany(o => o.Barcodes.Split(',')));
}
if (!storeId.IsNullOrEmpty())
{
var ware = WarehouseService.Find(o => o.StoreId == storeId && o.CompanyId == CommonService.CompanyId);
if (ware != null)
{
var categorySNs = ware.CategorySN.Split(',').Select(o => int.Parse(o)).ToList();
var childs = ProductCategoryService.GetChildSNs(categorySNs);
var pros = BaseService <VwProduct> .FindList(o => o.CompanyId == CommonService.CompanyId && childs.Contains(o.CategorySN));
storeBarcodes = pros.Select(o => o.Barcode).ToList();
storeBarcodes.AddRange(pros.Where(o => !o.Barcodes.IsNullOrEmpty()).SelectMany(o => o.Barcodes.Split(',')));
products.AddRange(pros);
}
}
var barcodeIdx = fieldCols.GetValue("Barcode").ToType <int>() - 65;
var buyPriceIdx = fieldCols.GetValue("BuyPrice").ToType <int>() - 65;
var numberIdx = fieldCols.GetValue("InboundNumber").ToType <int>() - 65;
var proDateIdx = fieldCols.GetValue("ProducedDate").ToType <int>() - 65;
var giftIdx = fieldCols.GetValue("IsGift").ToType <int>() - 65;
var memoIdx = fieldCols.GetValue("Memo").ToType <int>() - 65;
count = dt.Rows.Count;
for (int i = 0; i < dt.Rows.Count; i++)
{
var pos = i + obj.MinRow;
var dr = dt.Rows[i];
var barcode = dr.GetValue(barcodeIdx).ToString().Trim();
if (barcode.IsNullOrEmpty())
{
errLs.Add("行号[" + pos + "]条码为空!");
continue;
}
if (!supplierBarcodes.Any(o => o == barcode))
{
errLs.Add("行号[" + pos + "]该供应单位下无此条码!");
continue;
}
if (!storeBarcodes.Any(o => o == barcode))
{
errLs.Add("行号[" + pos + "]该门店下无此条码!");
continue;
}
var buyPrice = dr.GetValue(buyPriceIdx).ToType <decimal?>();
if (!buyPrice.HasValue)
{
errLs.Add("行号[" + pos + "]该条码进价为空!");
continue;
}
var number = dr.GetValue(numberIdx).ToType <decimal?>();
if (!number.HasValue)
{
errLs.Add("行号[" + pos + "]该条码入库数量为空!");
continue;
}
var gift = dr.GetValue(giftIdx).ToString();
var proDate = dr.GetValue(proDateIdx).ToString().Trim();
DateTime t = DateTime.Now;
if (!proDate.IsNullOrEmpty() && DateTime.TryParse(proDate, out t))
{
proDate = t.ToString("yyyy-MM-dd");
}
else
{
proDate = string.Empty;
}
var pro = products.FirstOrDefault(o => o.Barcode == barcode || ("," + o.Barcodes + ",").Contains("," + barcode + ","));
list.Add(new InboundList()
{
Barcode = barcode,
ProductTitle = pro.Title,
BuyPrice = buyPrice.Value,
InboundNumber = number.Value,
Unit = pro.SubUnit,
ProducedDate = proDate,
SysPrice = pro.SysPrice,
Memo = dr.GetValue(memoIdx).ToString(),
IsGift = (short)(gift == "赠品"?1:0)
});
}
}
catch (Exception ex)
{
op.Message = ex.Message;
op.Successed = false;
Log.WriteError(ex);
errLs.Add("导入出现异常!");
}
return(CommonService.GenerateImportHtml(errLs, count, data: list, isSuccess: false));
}
public static OpResult Import(ImportSet obj, System.Web.HttpFileCollectionBase httpFiles, string fieldName, string columnName, short businessType)
{
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 supplierClass = SysDataDictService.FindList(o => o.DicPSN == (int)DicType.供应商分类);
var otherClass = supplierClass.FirstOrDefault(o => o.Title.StartsWith("其"));
var suppliers = GetList();
var users = UserInfoService.GetList();
var max = SysDataDictService.GetMaxSN;
var clsIdx = Convert.ToInt32(fieldCols["ClassifyId"]) - 65;
var titleIdx = Convert.ToInt32(fieldCols["Title"]) - 65;
var fulltitleIdx = Convert.ToInt32(fieldCols["FullTitle"]) - 65;
var linkmanIdx = Convert.ToInt32(fieldCols["Linkman"]) - 65;
var designeeIdx = Convert.ToInt32(fieldCols["Designee"]) - 65;
count = dt.Rows.Count;
for (int i = dt.Rows.Count - 1; i >= 0; i--)
{
try
{
var dr = dt.Rows[i];
var text = dr[clsIdx].ToString();
if (text.IsNullOrEmpty())
{
errLs.Add("行号[" + i + "]供应商分类为空!");
dt.Rows.RemoveAt(i);//去除不导入
continue;
}
var cls = supplierClass.FirstOrDefault(o => o.Title == text);
if (cls != null)
{
dr[clsIdx] = cls.DicSN.ToString();
}
else
{
if (obj.RefCreate)
{
var data = new Sys.Entity.SysDataDictionary()
{
DicPSN = (int)DicType.供应商分类,
DicSN = max++,
Status = true,
Title = text,
CompanyId = CommonService.CompanyId
};
SysDataDictService.Add(data);
supplierClass.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[designeeIdx].ToString();
var user = users.FirstOrDefault(o => o.FullName == text);
if (user != null)
{
dr[designeeIdx] = user.UID;
}
else
{
errLs.Add("指派人[" + text + "]不存在!");
dt.Rows.RemoveAt(i);
continue;
}
text = dr[titleIdx].ToString().Trim();
var fulltext = dr[fulltitleIdx].ToString().Trim();
var linkman = dr[linkmanIdx].ToString().Trim();
if (text.IsNullOrEmpty())
{
errLs.Add("行号[" + text + "]简称为空!");
dt.Rows.RemoveAt(i);
}
else if (fulltext.IsNullOrEmpty())
{
errLs.Add("简称[" + text + "]全称为空!");
dt.Rows.RemoveAt(i);
}
else if (linkman.IsNullOrEmpty())
{
errLs.Add("简称[" + text + "]联系人为空!");
dt.Rows.RemoveAt(i);
}
else if (suppliers.Any(o => o.Title == text))
{
errLs.Add("简称[" + text + "]已存在!");
dt.Rows.RemoveAt(i);
}
else if (suppliers.Any(o => o.FullTitle == fulltext))
{
errLs.Add("全称[" + fulltext + "]已存在!");
dt.Rows.RemoveAt(i);
}
else
{
suppliers.Add(new Supplier()
{
Title = text,
FullTitle = fulltext
});
}
}
catch (Exception e)
{
throw new Exception("供应商分类处理失败!", e);
}
}
StringBuilder sb = new StringBuilder();
sb.Append("begin tran ");
foreach (DataRow dr in dt.Rows)
{
sb.Append("insert into Supplier");
sb.Append("(CompanyId,BusinessType,Id,");
sb.Append(string.Join(",", fieldCols.Keys));
sb.Append(") values(");
sb.AppendFormat("{0},", obj.CompanyId);
sb.AppendFormat(businessType + ",");
sb.AppendFormat("'{0}',", CommonRules.GUID);
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");
op.Successed = new Pharos.Logic.DAL.CommonDAL()._db.ExecuteNonQueryText(sb.ToString(), null) > 0;
log.WriteInsert(obj.TableName == "Supplier" ? "供应商导入" : "批发商导入", Pharos.Sys.LogModule.档案管理);
}
catch (Exception ex)
{
op.Message = ex.Message;
op.Successed = false;
Log.WriteError(ex);
errLs.Add("导入出现异常!");
}
return(CommonService.GenerateImportHtml(errLs, count));
}
public static 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
{
//防伪码在同商户下唯一，防止重复
SecurityCodes = BaseService <MembershipCard> .CurrentRepository.Entities.Where(o => o.CompanyId == CommonService.CompanyId).Select(o => o.SecurityCode).ToList();
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 cardsnIdx = fieldCols.GetValue("CardSN").ToType <int>() - 65;
var balanceIdx = fieldCols.GetValue("Balance").ToType <int>() - 65;
var nameIdx = fieldCols.GetValue("MemberId").ToType <int>() - 65;
var mobileIdx = fieldCols.GetValue("MobilePhone").ToType <int>() - 65;
count = dt.Rows.Count;
if (cardsnIdx < 0 || balanceIdx < 0)
{
errLs.Add("卡号或余额列不能为空!");
dt.Rows.Clear();
count = errLs.Count;
}
var cardsns = new List <string>();
var names = new List <string>();
var mobiles = new List <string>();
foreach (DataRow dr in dt.Rows)
{
var cardsn = dr.GetValue(cardsnIdx).ToString();
var name = dr.GetValue(nameIdx).ToString();
var mobile = dr.GetValue(mobileIdx).ToString();
if (!cardsn.IsNullOrEmpty() && !cardsns.Contains(cardsn))
{
cardsns.Add(cardsn);
}
if (!name.IsNullOrEmpty() && !names.Contains(name))
{
names.Add(name);
}
if (!mobile.IsNullOrEmpty() && !mobiles.Contains(mobile))
{
mobiles.Add(mobile);
}
}
var cards = new List <MembershipCard>();
var members = new List <Members>();
if (cardsns.Any())
{
cards = CurrentRepository.QueryEntity.Where(o => o.CompanyId == CommonService.CompanyId && cardsns.Contains(o.CardSN)).ToList();
}
if (names.Any() || mobiles.Any())
{
members = MembersService.FindList(o => names.Contains(o.RealName) || mobiles.Contains(o.MobilePhone));
}
var storeId = System.Web.HttpContext.Current.Request["StoreId"];
for (int i = dt.Rows.Count - 1; i >= 0; i--)
{
var dr = dt.Rows[i];
var text = dr.GetValue(cardsnIdx).ToString().Trim();
decimal val = 0;
if (text.IsNullOrEmpty())
{
dt.Rows.RemoveAt(i);//去除不导入
continue;
}
else if (!decimal.TryParse(text, out val))
{
errLs.Add("卡号[" + text + "]不合法!");
dt.Rows.RemoveAt(i);//去除不导入
continue;
}
else if (cards.Any(o => o.CardSN == text) && System.Web.HttpContext.Current.Request["updateBalance"] != "on")
{
errLs.Add("卡号[" + text + "]已存在!");
dt.Rows.RemoveAt(i);//去除不导入
continue;
}
else
{
cards.Add(new MembershipCard()
{
CardSN = text
});
}
var name = dr.GetValue(nameIdx).ToString();
var mobile = dr.GetValue(mobileIdx).ToString();
if (!(name.IsNullOrEmpty() || mobile.IsNullOrEmpty()))
{
var mem = members.FirstOrDefault(o => o.RealName == name && o.MobilePhone == mobile);
if (mem != null)
{
dr[nameIdx] = mem.MemberId;
}
else
{
if (obj.RefCreate)
{
mem = new Members()
{
CreateDT = DateTime.Now,
CreateUID = CurrentUser.UID,
RealName = name,
MobilePhone = mobile,
Sex = -1,
Status = 1,
StoreId = storeId.IsNullOrEmpty() ? "00" : storeId,
CompanyId = obj.CompanyId,
MemberNo = CommonRules.MemberNum("00"),
MemberId = CommonRules.GUID
};
MembersService.Add(mem);
dr[nameIdx] = mem.MemberId;
}
}
}
}
fieldCols.Remove("MobilePhone");
var cardTypeId = System.Web.HttpContext.Current.Request["CardTypeId"];
var prefix = DateTime.Now.ToString("yyMMdd");
var maxsn = CurrentRepository.QueryEntity.Where(o => o.CompanyId == obj.CompanyId && o.BatchSN.StartsWith(prefix)).Select(o => o.BatchSN).Max();
int sn = 1;
if (!maxsn.IsNullOrEmpty())
{
sn = int.Parse(maxsn.Substring(maxsn.Length - 2, 2)) + 1;
if (sn >= 100)
{
errLs.Add("当天制卡批次号已使用完!");
dt.Rows.Clear();
}
}
prefix = prefix + sn.ToString("00");
StringBuilder sb = new StringBuilder();
sb.Append("begin tran ");
foreach (DataRow dr in dt.Rows)
{
//判断卡号是否操作
var cardSn = dr.GetValue(cardsnIdx).ToString().Trim();
//var isExit = CurrentRepository.Entities.Any(o => o.CompanyId == CommonService.CompanyId && o.CardSN == cardSn);
//判断是否只做更新动作 重复的卡号做余额更新动作
if (cards.Any(o => o.CardSN == cardSn) && System.Web.HttpContext.Current.Request["updateBalance"] == "on")
{
sb.Append("UPDATE " + obj.TableName);
sb.Append(" SET ");
sb.Append("Balance= ");
sb.Append(dr.GetValue(balanceIdx).ToString().Trim());
sb.Append(" WHERE ");
sb.Append(" CardSN= ");
sb.Append("'" + cardSn + "'");
sb.Append(" AND ");
sb.Append(" CompanyId= ");
sb.Append(CommonService.CompanyId);
sb.Append(";");
}
else
{
sb.Append("insert into ");
sb.Append(obj.TableName);
sb.Append("(CompanyId,BatchSN,CardTypeId,State,CreateUID,SecurityCode,");
sb.Append(string.Join(",", fieldCols.Keys));
sb.Append(") values(");
sb.AppendFormat("{0},", obj.CompanyId);
sb.AppendFormat("'{0}',", prefix);
sb.AppendFormat("'{0}',", cardTypeId);
sb.AppendFormat("1,");
sb.AppendFormat("'{0}',", CurrentUser.UID);
sb.AppendFormat("'{0}',", GetSecurityCode);
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(");");
}
sn++;
}
if (dt.Rows.Count > 0)
{
sb.Append("insert into ");
sb.Append("MakingMembershipCard");
sb.Append("(CompanyId,BatchSN,CardTypeId,State,CreateUID,MakeNumber");
sb.Append(") values(");
sb.AppendFormat("{0},'{1}','{2}',{3},'{4}','{5}')", obj.CompanyId, prefix, cardTypeId, 1, CurrentUser.UID, dt.Rows.Count);
sb.Append(" commit tran");
op.Successed = new Pharos.Logic.DAL.CommonDAL()._db.ExecuteNonQueryText(sb.ToString(), null) > 0;
if (op.Successed)
{
Log.WriteInsert("会员卡导入", Sys.LogModule.会员管理);
}
("会员卡导入:" + sb.ToString()).ToLog();
}
}
catch (Exception ex)
{
op.Message = ex.Message;
op.Successed = false;
Log.WriteError(ex);
errLs.Add("导入出现异常!");
}
return(CommonService.GenerateImportHtml(errLs, count));
}
