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