public JsonResult BatchLogOutUserByUserIdAction() { try { if (Request.Files.Count > 0) { var file = Request.Files[0]; if (!file.FileName.Contains(".xlsx") && !file.FileName.Contains(".xls")) { return(Json(new { Status = -1, Error = "请上传.xlsx文件或者.xls文件!" }, "text/html")); } var excel = new Controls.ExcelHelper(file.InputStream, file.FileName); var dt = excel.ExcelToDataTable("sheet1", true); foreach (DataRow dr in dt.Rows) { var userid = dr[0].ToString(); if (!string.IsNullOrWhiteSpace(userid)) { LogOutUserByUserId(userid); } } } return(Json(new { Status = -1, Error = "请选择文件" }, "text/html")); } catch (Exception ex) { WebLog.LogException(ex); return(Json(new { Status = -2, Error = ex }, "text/html")); } }
public async Task <ActionResult> AddExport() { var file = Request.Files[0]; if (file.ContentType != "application/vnd.ms-excel" && file.ContentType != "application/x-xls") { return(Content(JsonConvert.SerializeObject(new { Status = -1, Error = "请上传.xlsx文件或者.xls文件" }), "text/html")); } var excel = new Controls.ExcelHelper(file.InputStream, file.FileName); var dt = excel.ExcelToDataTable("问答题库", true); if (dt != null && dt?.Rows?.Count > 0) { if (dt.Columns.Count != 8) { return(Content(JsonConvert.SerializeObject(new { Status = -1, Error = "请确认文档的列是否只有【编码】【标签】【题目】【选项A】【选项B】【选项B】【选项C】【选项D】【正确选项】这几列" }), "text/html")); } RepositoryManager manager = new RepositoryManager(); using (var db = manager.BeginTrans()) { foreach (DataRow dr in dt.Rows) { if (string.IsNullOrWhiteSpace(dr[2].ToString().Trim()))//过滤 答题 为空的 行 { continue; } AnswerInfoListEntity entity = new AnswerInfoListEntity() { Tip = dr[1].ToString(), Answer = dr[2].ToString(), OptionsA = dr[3].ToString(), OptionsB = dr[4].ToString(), OptionsC = dr[5].ToString(), OptionsD = dr[6].ToString(), OptionsReal = dr[7].ToString(), CreateDateTime = DateTime.Now, LastUpdateDateTime = DateTime.Now, IsEnabled = true }; db.Insert <AnswerInfoListEntity>(entity); } db.Commit(); } //读写分离 做延时 await Task.Delay(2000); #region 更新问答题库缓存 RefreshQuestionInfoListCache(); #endregion return(Content(JsonConvert.SerializeObject(new { Status = 1, Error = "导入成功" }), "text/html")); } else { return(Content(JsonConvert.SerializeObject(new { Status = -1, Error = "请确认xls的sheet名称是否为:问答题库;是否有内容" }), "text/html")); } }
public ActionResult ExportProducts() { if (Request.Files.Count > 0) { var file = Request.Files[0]; if (file.FileName.Contains(".xlsx") || file.FileName.Contains(".xls")) { var excel = new Controls.ExcelHelper(file.InputStream, file.FileName); var dt = excel.ExcelToDataTable("Sheet1", true); IEnumerable <QiangGouSeckillImportModel> data = GetDataFromExcel(dt); if (data.Any()) { return(CheckExcelData(data)); } else { return(Content(JsonConvert.SerializeObject(new { code = -1 }))); } } } return(Content(JsonConvert.SerializeObject(new { code = 0 }))); }
public JsonResult ImportGrade() { try { if (Request.Files.Count > 0) { var file = Request.Files[0]; if (!file.FileName.Contains(".xlsx") && !file.FileName.Contains(".xls")) { return(Json(new { Status = -1, Error = "请上传.xlsx文件或者.xls文件!" }, "text/html")); } var excel = new Controls.ExcelHelper(file.InputStream, file.FileName); var dt = excel.ExcelToDataTable("Sheet1", true); List <YLHProductModel> errordt = new List <YLHProductModel>(); // var exceldate=new Controls.ExcelHelper(new mer) #region 批量将读取到的excel数据导入到数据库 for (int i = 0; i < dt.Rows.Count; i++) { DataRow dr = dt.Rows[i]; string productId = "YLH-" + dr["ProductNunber"]?.ToString(); string variantId = "1"; var product = new WholeProductInfo #region 创建增加的产品 { FactoryNumber = dr["ProductNunber"]?.ToString(), ProductID = "YLH-" + dr["ProductNunber"]?.ToString(), VariantID = "1", cy_list_price = Convert.ToDecimal(dr["cy_list_price"].ToString() == string.Empty ? 9999 : dr["cy_list_price"]), DisplayName = dr["ProductName"]?.ToString() + dr["Specification"]?.ToString(), CatalogName = "CarPAR", PrimaryParentCategory = "YLHProduct", DefinitionName = "YLHProduct", Image_filename = "/Images/Products/1609/1CA808C19E3FF0E9.jpg", Description = "<p></p>" }; product.DisplayName = product.DisplayName.Length < 128 ? product.DisplayName : dr["ProductName"]?.ToString(); #endregion var ylhProduct = new YLHProductModel(); #region 创建永隆行各商品的对照关系; ylhProduct.StoreName = dr["StoreName"]?.ToString(); ylhProduct.ProductType1St = dr["[1stProductType]"]?.ToString(); ylhProduct.ProductType2Nd = dr["[2ndProductType]"]?.ToString(); ylhProduct.ProductType3Rd = dr["[3rdProductType]"]?.ToString(); ylhProduct.ProductType4Th = dr["[4thProductType]"]?.ToString(); ylhProduct.ProductType5Th = dr["[5thProductType]"]?.ToString(); ylhProduct.counter_id = Convert.ToInt32(dr["counter_id"]); ylhProduct.ProductNunber = dr["ProductNunber"]?.ToString(); ylhProduct.ProductName = dr["ProductName"]?.ToString(); ylhProduct.Specification = dr["Specification"]?.ToString(); ylhProduct.Price = Convert.ToDecimal(dr["Price"].ToString() == string.Empty ? 9999 : dr["Price"]); ylhProduct.SystemQuantity = float.Parse(dr["SystemQuantity"].ToString()); ylhProduct.SyetemSettlement = Convert.ToDecimal(dr["SyetemSettlement"].ToString() == string.Empty ? 9999 : dr["SyetemSettlement"]); ylhProduct.RealQuantity = float.Parse(dr["RealQuantity"].ToString()); ylhProduct.RealSettlement = Convert.ToDecimal(dr["RealSettlement"].ToString() == string.Empty ? 9999 : dr["RealSettlement"]); ylhProduct.QuantityDiff = Convert.ToInt32(dr["QuantityDiff"].ToString() == string.Empty ? "" : dr["QuantityDiff"]); ylhProduct.SettlementDiff = Convert.ToDecimal(dr["SettlementDiff"].ToString() == string.Empty ? 9999 : dr["SettlementDiff"]); ylhProduct.DiffReason = dr["DiffReason"]?.ToString(); ylhProduct.LastPurchaseDate = Convert.ToDateTime(dr["LastPurchaseDate"]); ylhProduct.YearInWareHouse = Convert.ToInt32(dr["YearInWareHouse"]); ylhProduct.DayInWareHouse = Convert.ToInt32(dr["DayInWareHouse"]); ylhProduct.DistributionAmount = Convert.ToDecimal(dr["DistributionAmount"].ToString() == string.Empty ? 9999 : dr["DistributionAmount"]); ylhProduct.BuyoutAmount = Convert.ToDecimal(dr["BuyoutAmount"].ToString() == string.Empty ? 9999 : dr["BuyoutAmount"]); ylhProduct.MonthlySales = float.Parse(dr["MonthlySales"].ToString()); ylhProduct.QualityClassification = dr["QualityClassification"]?.ToString(); ylhProduct.Remark = dr["Remark"]?.ToString(); ylhProduct.cy_list_price = Convert.ToDecimal(dr["cy_list_price"].ToString() == string.Empty ? 9999 : dr["cy_list_price"]); ylhProduct.MonthInWareHouse = Convert.ToInt32(dr["MonthInWareHouse"]); ylhProduct.PID = dr["PID"]?.ToString(); #endregion if (dr["PID"].ToString() == string.Empty) //产品在数据库中不存在,需要通过服务导入到产品库 { //通过服务创建产品库的产品 using (var client = new ProductClient()) { var result = client.Invoke( o => o.CreateProduct(product, User.Identity.Name, ChannelType.Tuhu)); if (!result.Success) { //服务调用失败 } } } else { #region 如果数据库中存在pid则通过已有的pid找到Oid var tempId = dr["PID"].ToString(); var Index = tempId.IndexOf('|'); if (Index > 0) { //pid有variantId,i_class_type=2 productId = tempId.SafeSubstring(0, Index); variantId = tempId.Substring(Index + 1); } else { //pid没有variantId,i_class_type=4 productId = tempId.Substring(Index + 1); variantId = ""; } #endregion } //查找oid ylhProduct.oid = YLHProductManager.GetOid(productId, variantId); int ylhCount = YLHProductManager.CheckoutProduct(ylhProduct.ProductNunber, ylhProduct.counter_id ?? 0); if (ylhProduct.oid == 0) //产品库中没有该产品 { errordt.Add(ylhProduct); } else if (ylhCount > 0) //已存在该永隆行产品 { } else //添加产品 { YLHProductManager.AddProducts(ylhProduct); } if (i % 100 == 0) { Thread.Sleep(3000); //每执行100条数据,休眠3秒钟 } } #endregion #region 将问题输数据导出到excel using ( MemoryStream ms = new MemoryStream(System.IO.File.ReadAllBytes(Server.MapPath(@"~/Content/Export/分享赚钱商品.xlsx"))) ) { if (errordt.Any()) { //创建工作簿对象 XSSFWorkbook book = new XSSFWorkbook(ms); //创建excel 2007工作簿对象, //创建工作表 ISheet sheet1 = book.GetSheetAt(0); //创建行row IRow row1 = sheet1.CreateRow(0); #region 工作簿的首行,头部标题 row1.CreateCell(0).SetCellValue("StoreName"); row1.CreateCell(1).SetCellValue("[1stProductType]"); row1.CreateCell(2).SetCellValue("[2ndProductType]"); row1.CreateCell(3).SetCellValue("[3rdProductType]"); row1.CreateCell(4).SetCellValue("[4thProductType]"); row1.CreateCell(5).SetCellValue("[5thProductType]"); row1.CreateCell(6).SetCellValue("counter_id"); row1.CreateCell(7).SetCellValue("ProductNunber"); row1.CreateCell(8).SetCellValue("ProductName"); row1.CreateCell(9).SetCellValue("Specification"); row1.CreateCell(10).SetCellValue("Price"); row1.CreateCell(11).SetCellValue("cy_list_price"); row1.CreateCell(12).SetCellValue("SystemQuantity"); row1.CreateCell(13).SetCellValue("SyetemSettlement"); row1.CreateCell(14).SetCellValue("RealQuantity"); row1.CreateCell(15).SetCellValue("RealSettlement"); row1.CreateCell(16).SetCellValue("QuantityDiff"); row1.CreateCell(17).SetCellValue("SettlementDiff"); row1.CreateCell(18).SetCellValue("DiffReason"); row1.CreateCell(19).SetCellValue("LastPurchaseDate"); row1.CreateCell(20).SetCellValue("YearInWareHouse"); row1.CreateCell(21).SetCellValue("MonthInWareHouse"); row1.CreateCell(22).SetCellValue("DayInWareHouse"); row1.CreateCell(23).SetCellValue("DistributionAmount"); row1.CreateCell(24).SetCellValue("BuyoutAmount"); row1.CreateCell(25).SetCellValue("MonthlySales"); row1.CreateCell(26).SetCellValue("QualityClassification"); row1.CreateCell(27).SetCellValue("Remark"); row1.CreateCell(28).SetCellValue("PID"); #endregion for (var i = 0; i < errordt.Count(); i++) { YLHProductModel item = errordt[i]; var row = sheet1.CreateRow(i + 1); #region 将list换成行放在工作簿中 row.CreateCell(0).SetCellValue(item.StoreName); row.CreateCell(1).SetCellValue(item.ProductType1St); row.CreateCell(2).SetCellValue(item.ProductType2Nd); row.CreateCell(3).SetCellValue(item.ProductType3Rd); row.CreateCell(4).SetCellValue(item.ProductType4Th); row.CreateCell(5).SetCellValue(item.ProductType5Th); row.CreateCell(6).SetCellValue(item.counter_id ?? 9999); row.CreateCell(7).SetCellValue(item.ProductNunber); row.CreateCell(8).SetCellValue(item.ProductName); row.CreateCell(9).SetCellValue(item.Specification); row.CreateCell(10).SetCellValue(item.Price.ToString()); row.CreateCell(11).SetCellValue(item.cy_list_price.ToString()); row.CreateCell(12).SetCellValue(item.SystemQuantity.ToString()); row.CreateCell(13) .SetCellValue(item.SyetemSettlement.ToString(CultureInfo.InvariantCulture)); row.CreateCell(14).SetCellValue(item.RealQuantity.ToString()); row.CreateCell(15).SetCellValue(item.RealSettlement.ToString()); row.CreateCell(16).SetCellValue(item.QuantityDiff); row.CreateCell(17).SetCellValue(item.SettlementDiff.ToString()); row.CreateCell(18).SetCellValue(item.DiffReason); row.CreateCell(19) .SetCellValue( Convert.ToDateTime(item.LastPurchaseDate.ToString() ?? "") .ToString(CultureInfo.InvariantCulture)); row.CreateCell(20).SetCellValue(item.YearInWareHouse.ToString()); row.CreateCell(21).SetCellValue(item.MonthInWareHouse.ToString()); row.CreateCell(22).SetCellValue(item.DayInWareHouse.ToString()); row.CreateCell(23).SetCellValue(item.DistributionAmount.ToString()); row.CreateCell(24).SetCellValue(item.BuyoutAmount.ToString()); row.CreateCell(25).SetCellValue(item.MonthlySales.ToString()); row.CreateCell(26).SetCellValue(item.QualityClassification); row.CreateCell(27).SetCellValue(item.Remark); row.CreateCell(28).SetCellValue(item.PID); #endregion } Response.ContentType = "application/vnd.ms-excel"; Response.Charset = ""; Response.AppendHeader("Content-Disposition", "attachment;fileName=永隆行问题商品" + ".xlsx"); book.Write(Response.OutputStream); Response.End(); } } #endregion return(Json(new { Status = 0, Result = "写入完成" }, "text/html")); } return(Json(new { Status = -1, Error = "请选中文件" }, "text/html")); } catch (Exception em) { return(Json(new { Status = -2, Error = em.Message }, "text/html")); } }
public JsonResult ImportGrade() { try { //初始化优惠项目 var promotionItems = PromotionItemInitialization(); if (Request.Files.Count > 0) { var file = Request.Files[0]; if (!file.FileName.Contains(".xlsx") && !file.FileName.Contains(".xls")) { return(Json(new { Status = -1, Error = "请上传.xlsx文件或者.xls文件!" }, "text/html")); } var excel = new Controls.ExcelHelper(file.InputStream, file.FileName); var dt = excel.ExcelToDataTable("sheet1", true); #region 初始化失败数据表结构 DataTable failData = new DataTable(); failData.Columns.Add(new DataColumn("会员卡号", typeof(string))); failData.Columns.Add(new DataColumn("会员姓名", typeof(string))); failData.Columns.Add(new DataColumn("会员手机", typeof(string))); failData.Columns.Add(new DataColumn("数量", typeof(string))); failData.Columns.Add(new DataColumn("服务项目", typeof(string))); #endregion #region 插入成功数据表结构 DataTable finishData = new DataTable(); finishData.Columns.Add(new DataColumn("会员卡号", typeof(string))); finishData.Columns.Add(new DataColumn("会员姓名", typeof(string))); finishData.Columns.Add(new DataColumn("会员手机", typeof(string))); finishData.Columns.Add(new DataColumn("数量", typeof(string))); finishData.Columns.Add(new DataColumn("服务项目", typeof(string))); #endregion Dictionary <string, Guid> insertUsers = new Dictionary <string, Guid>(); foreach (DataRow dr in dt.Rows) { DataRow failRow = failData.NewRow(); DataRow finishRow = finishData.NewRow(); bool sendPromotionCodeFail = false; #region step 1 清理出表格中手机号字段为空的数据 if (null == dr["会员手机"] || string.IsNullOrWhiteSpace(dr["会员手机"].ToString().Trim())) { for (int i = 0; i < dr.ItemArray.Length; i++) { failRow[i] = dr[i]; } failData.Rows.Add(failData); continue; } #endregion #region step 1: 获取UserID或者添加用户到UserObject var tempUserId = YLHUserManager.GetUserId(dr["会员手机"].ToString().Trim()); //获取是否已经是tuhu用户 var dtExistedMobile = insertUsers.ContainsKey(dr["会员手机"].ToString().Trim()); //是否是已经插入的用户 //主表不存在手机用户 //插入主表用户数据、YLH表用户数据 if (string.IsNullOrEmpty(tempUserId) && dtExistedMobile == false) { tbl_UserObjectModel userObject = new tbl_UserObjectModel(); Guid newGuid = Guid.NewGuid(); #region generate userObjectModel //u_user_id userObject.u_user_id = newGuid.ToString("B"); //会员手机号 userObject.u_mobile_number = dr["会员手机"].ToString().Trim(); //会员姓名 userObject.u_last_name = (null == dr["会员姓名"] || string.IsNullOrWhiteSpace(dr["会员姓名"].ToString().Trim()) || "NULL" == dr["会员姓名"].ToString().Trim().ToUpper()) ? dr["会员手机"].ToString().Trim() : dr["会员姓名"].ToString().Trim(); //会员邮箱 userObject.u_email_address = dr["会员手机"].ToString().Trim() + "@whguanggu.com"; userObject.u_application_name = "Import"; userObject.Category = "武汉光谷一路店"; #endregion #region 插入到数据库,如果插入失败,加入脏数据表,否则加入正常数据表 try { YLHUserManager.InsertUserObject(userObject); } catch (Exception ex) { for (int i = 0; i < dr.ItemArray.Length; i++) { failRow[i] = dr[i]; } failData.Rows.Add(failRow); WebLog.LogException(ex); break; } #endregion YLHUserInfoModel ylhUserInfo = new YLHUserInfoModel(); #region generate ylh_UserInfo ylhUserInfo.u_user_id = newGuid.ToString("B"); ylhUserInfo.MemberName = (null == dr["会员姓名"] || string.IsNullOrWhiteSpace(dr["会员姓名"].ToString().Trim()) || "NULL" == dr["会员姓名"].ToString().Trim().ToUpper()) ? dr["会员手机"].ToString().Trim() : dr["会员姓名"].ToString().Trim(); ylhUserInfo.MemberNumber = dr["会员卡号"].ToString().Trim(); ylhUserInfo.MemberPhone = dr["会员手机"].ToString().Trim(); ylhUserInfo.Tag = "武汉光谷一路店"; ylhUserInfo.CreatedTime = DateTime.Now; ylhUserInfo.UpdatedTime = DateTime.Now; ylhUserInfo.MemberBirthday = DateTime.Now; ylhUserInfo.MemberAddress = string.Empty; ylhUserInfo.Integration = 0; #endregion #region 插入到数据库,如果插入失败,加入脏数据表,否则加入正常数据表 try { YLHUserManager.InsertYLHUserInfo(ylhUserInfo); } catch (Exception ex) { for (int i = 0; i < dr.ItemArray.Length; i++) { failRow[i] = dr[i]; } failData.Rows.Add(failRow); WebLog.LogException(ex); break; } #endregion //插入的用户记录字典 if (!insertUsers.ContainsKey(dr["会员手机"].ToString().Trim())) { insertUsers.Add(dr["会员手机"].ToString().Trim(), newGuid); } } //主表存在手机用户数据 //插入YLH表用户数据 else { var tempPKID = YLHUserManager.GetYLHUserInfoPKID(tempUserId); if (tempPKID < 0) { YLHUserInfoModel ylhUserInfo = new YLHUserInfoModel(); #region generate ylh_UserInfo ylhUserInfo.u_user_id = tempUserId; ylhUserInfo.MemberName = (null == dr["会员姓名"] || string.IsNullOrWhiteSpace(dr["会员姓名"].ToString().Trim()) || "NULL" == dr["会员姓名"].ToString().Trim().ToUpper()) ? dr["会员手机"].ToString().Trim() : dr["会员姓名"].ToString().Trim(); ylhUserInfo.MemberNumber = dr["会员卡号"].ToString().Trim(); ylhUserInfo.MemberPhone = dr["会员手机"].ToString().Trim(); ylhUserInfo.Tag = "武汉光谷一路店"; ylhUserInfo.CreatedTime = DateTime.Now; ylhUserInfo.UpdatedTime = DateTime.Now; ylhUserInfo.MemberBirthday = DateTime.Now; ylhUserInfo.MemberAddress = string.Empty; ylhUserInfo.Integration = 0; #endregion #region 插入到数据库,如果插入失败,加入脏数据表,否则加入正常数据表 try { YLHUserManager.InsertYLHUserInfo(ylhUserInfo); } catch (Exception ex) { for (int i = 0; i < dr.ItemArray.Length; i++) { failRow[i] = dr[i]; } failData.Rows.Add(failRow); WebLog.LogException(ex); break; } #endregion } //插入的用户记录字典 if (!insertUsers.ContainsKey(dr["会员手机"].ToString().Trim())) { insertUsers.Add(dr["会员手机"].ToString().Trim(), new Guid(tempUserId)); } } #endregion #region step 2: 插入优惠券 if (!string.IsNullOrWhiteSpace(dr["服务项目"].ToString().Trim()) && insertUsers.ContainsKey(dr["会员手机"].ToString().Trim())) { var promotions = promotionItems.Where(_item => _item.ProductName == dr["服务项目"].ToString().Trim()).ToList(); if (promotions != null && promotions.Any() && promotions.Count > 0) { foreach (var promotion in promotions) { int count = Convert.ToInt32(dr["数量"].ToString().Trim()); for (int i = 0; i < count; i++) { #region generation promotionCode PromotionCode code = new PromotionCode(); code.UserID = insertUsers[dr["会员手机"].ToString().Trim()]; code.StartTime = DateTime.Now; code.EndTime = Convert.ToDateTime("2018-12-31 00:00:00"); code.PromotionName = promotion.PromotionName; code.Description = promotion.Description; code.RuleID = promotion.RuleID; code.Discount = promotion.Discount; code.MinMoney = promotion.MinMoney; code.CodeChannel = promotion.CodeChannel; code.Creater = "*****@*****.**"; code.Issuer = "*****@*****.**"; #endregion #region 塞券 try { YLHUserManager.CreatePromotionCode(code); } catch (Exception ex) { sendPromotionCodeFail = true; WebLog.LogException(ex); break; } #endregion } if (sendPromotionCodeFail) { break; } } } if (sendPromotionCodeFail) { for (int j = 0; j < dr.ItemArray.Length; j++) { failRow[j] = dr[j]; } failData.Rows.Add(failRow); } else { for (int j = 0; j < dr.ItemArray.Length; j++) { finishRow[j] = dr[j]; } finishData.Rows.Add(finishRow); } } else { for (int j = 0; j < dr.ItemArray.Length; j++) { finishRow[j] = dr[j]; } finishData.Rows.Add(finishRow); break; } #endregion } Response.Clear(); Response.Charset = "UTF-8"; Response.Buffer = true; Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); Response.AppendHeader("Content-Disposition", "attachment;filename=\"" + System.Web.HttpUtility.UrlEncode("永隆行用户导入失败记录", System.Text.Encoding.UTF8) + ".xls\""); Response.ContentType = "application/ms-excel"; string colHeaders = string.Empty; string ls_item = string.Empty; DataRow[] myRow = failData.Select(); int cl = failData.Columns.Count; foreach (DataRow row in myRow) { for (int i = 0; i < cl; i++) { if (i == (cl - 1)) { ls_item += row[i].ToString() + "\n"; } else { ls_item += row[i].ToString() + "\t"; } } Response.Output.Write(ls_item); ls_item = string.Empty; } Response.Output.Flush(); Response.End(); return(Json(new { Status = -1, Error = "全部写入完成" }, "text/html")); } return(Json(new { Status = -1, Error = "请选择文件" }, "text/html")); } catch (Exception ex) { WebLog.LogException(ex); return(Json(new { Status = -2, Error = ex }, "text/html")); } }
public ActionResult UploadBarCodeLogImport(HttpPostedFileBase fileBase) { HttpPostedFileBase file = Request.Files["files"]; #region 【参数初始化】 string uploadFileName = ""; string uploadFileAddress = ""; string failFileName = ""; string failFileAddress = ""; string ext = ""; string noFileName = ""; string uploadDomain = "/TireLog/BarCode"; string barCodeBatchNum = DateTime.Now.ToString("yyyyMMddHHmmss"); #endregion #region 【导入文件预判断】 if (file == null || file.ContentLength <= 0) { return(RedirectToAction("UploadBarCodeLog", "TireSecurityCode", new { msg = "文件不能为空" })); } var filesize = file.ContentLength; //获取上传文件的大小单位为字节byte ext = System.IO.Path.GetExtension(file.FileName); //获取上传文件的扩展名 noFileName = System.IO.Path.GetFileNameWithoutExtension(file.FileName); //获取无扩展名的文件名 const string fileType = ".xls,.xlsx"; //定义上传文件的类型字符串 if (string.IsNullOrWhiteSpace(ext)) { return(RedirectToAction("UploadBarCodeLog", "TireSecurityCode", new { msg = "文件扩展名不能为空" })); } if (!fileType.Contains(ext)) { return(RedirectToAction("UploadBarCodeLog", "TireSecurityCode", new { msg = "文件类型不对,只能导入xls和xlsx格式的文件" })); } #endregion #region 【源文件上传】 var stream = file.InputStream; var buffer = new byte[stream.Length]; stream.Read(buffer, 0, buffer.Length); uploadFileName = DateTime.Now.ToString("yyyyMMddhhmmss") + '_' + noFileName + ext; var result = FileUploadService.UploadFile(buffer, ext, uploadFileName, uploadDomain); if (!string.IsNullOrWhiteSpace(result)) { uploadFileAddress = "https://img1.tuhu.org" + result; } else { return(RedirectToAction("UploadSecurityCodeLog", "TireSecurityCode", new { msg = "文件上传失败" })); } UploadBarCodeLog barLog = new UploadBarCodeLog { UploadFileName = uploadFileName, UploadFileAddress = uploadFileAddress, Operator = ThreadIdentity.Operator.Name }; #endregion #region 【检查数据缺失】 stream.Position = 0; var excel = new Controls.ExcelHelper(stream, file.FileName); var dt = excel.ExcelToDataTable("sheet1", true); if (buffer.Length > 0 && (dt == null || dt.Rows == null || dt.Rows.Count < 1)) { return(RedirectToAction("UploadBarCodeLog", "TireSecurityCode", new { msg = "文件上传失败" })); } StringBuilder failSB = new StringBuilder(); List <InputBarCode> congifList = new List <InputBarCode>(); for (int i = 0; i < dt.Rows.Count; i++) { if (null == dt.Rows[i]["条码"] || string.IsNullOrWhiteSpace(dt.Rows[i]["条码"].ToString()) || null == dt.Rows[i]["防伪码"] || string.IsNullOrWhiteSpace(dt.Rows[i]["防伪码"].ToString())) { failSB.AppendLine("第" + (i + 2) + "行:条码或防伪码数据不完整."); } else { congifList.Add(new InputBarCode { BarCode = dt.Rows[i]["条码"].ToString(), SecurityCode = dt.Rows[i]["防伪码"].ToString(), BarCodeBatchNum = barCodeBatchNum }); } } if (failSB.Length > 0) { byte[] arrayFail_Params = Encoding.GetEncoding("gb2312").GetBytes(failSB.ToString()); failFileName = DateTime.Now.ToString("yyyyMMddhhmmss") + '_' + noFileName + "_Error" + ".txt"; result = FileUploadService.UploadFile(arrayFail_Params, ".txt", failFileName, uploadDomain); if (!string.IsNullOrWhiteSpace(result)) { failFileAddress = "https://img1.tuhu.org" + result; } else { return(RedirectToAction("UploadBarCodeLog", "TireSecurityCode", new { msg = "文件上传失败" })); } barLog.FailFileName = failFileName; barLog.FailFileAddress = failFileAddress; barLog.CreateTime = DateTime.Now; barLog.LastUpdateDataTime = DateTime.Now; var insertLog_Params = TireSecurityCodeConfigManager.InsertUploadBarCodeLog(barLog); if (insertLog_Params) { return(RedirectToAction("UploadBarCodeLog", "TireSecurityCode", new { msg = "数据有缺失,请检查错误日志,整理数据后重新上传." })); } else { return(RedirectToAction("UploadBarCodeLog", "TireSecurityCode", new { msg = "文件上传失败" })); } } #endregion #region 【检查数据重复】 var checkSCodeDup = congifList .GroupBy(item => item.SecurityCode) .Select(item => item.First()) .ToList <InputBarCode>().Count; if (checkSCodeDup < congifList.Count) { return(RedirectToAction("UploadBarCodeLog", "TireSecurityCode", new { msg = "防伪码数据有重复,请Excel排查后重新上传." })); } var checkBarCodeDup = congifList .GroupBy(item => item.BarCode) .Select(item => item.First()) .ToList <InputBarCode>().Count; if (checkBarCodeDup < congifList.Count) { return(RedirectToAction("UploadBarCodeLog", "TireSecurityCode", new { msg = "条码数据有重复,请Excel排查后重新上传." })); } #endregion #region 【批量更新数据】 congifList = congifList .GroupBy(item => item.SecurityCode) .Select(item => item.First()) .ToList <InputBarCode>(); var updateBarCode = TireSecurityCodeConfigManager.InsertBarCodeConfig(congifList); List <InputBarCode> errorInputList = new List <InputBarCode>(); switch (updateBarCode) { case -2: errorInputList = TireSecurityCodeConfigManager.QueryInputBarCodeByError("ItemNotExist", congifList); failSB.AppendLine("以下防伪码还没上传过,请检查数据."); failSB.AppendLine("防伪码,条码"); foreach (var error in errorInputList) { failSB.AppendLine(error.SecurityCode + "," + error.BarCode); } byte[] arrayFail_ItemNotExist = Encoding.GetEncoding("gb2312").GetBytes(failSB.ToString()); failFileName = DateTime.Now.ToString("yyyyMMddhhmmss") + '_' + noFileName + "_Error" + ".txt"; result = FileUploadService.UploadFile(arrayFail_ItemNotExist, ".txt", failFileName, uploadDomain); if (!string.IsNullOrWhiteSpace(result)) { failFileAddress = "https://img1.tuhu.org" + result; } else { return(RedirectToAction("UploadBarCodeLog", "TireSecurityCode", new { msg = "文件上传失败" })); } barLog.FailFileName = failFileName; barLog.FailFileAddress = failFileAddress; barLog.CreateTime = DateTime.Now; barLog.LastUpdateDataTime = DateTime.Now; var insertLog_ItemNotExist = TireSecurityCodeConfigManager.InsertUploadBarCodeLog(barLog); if (insertLog_ItemNotExist) { return(RedirectToAction("UploadBarCodeLog", "TireSecurityCode", new { msg = "数据里有不存在的防伪码,请检查错误日志,整理数据后重新上传." })); } else { return(RedirectToAction("UploadBarCodeLog", "TireSecurityCode", new { msg = "文件上传失败" })); } case -3: return(RedirectToAction("UploadBarCodeLog", "TireSecurityCode", new { msg = "本次上传数据均存在,不做更新." })); case -4: errorInputList = TireSecurityCodeConfigManager.QueryInputBarCodeByError("Difference", congifList); failSB.AppendLine("以下防伪码或条码已经存在过,请删除已存在数据后重新上传."); failSB.AppendLine("防伪码,条码"); foreach (var error in errorInputList) { failSB.AppendLine(error.SecurityCode + "," + error.BarCode); } byte[] arrayFail_Difference = Encoding.GetEncoding("gb2312").GetBytes(failSB.ToString()); failFileName = DateTime.Now.ToString("yyyyMMddhhmmss") + '_' + noFileName + "_Error" + ".txt"; result = FileUploadService.UploadFile(arrayFail_Difference, ".txt", failFileName, uploadDomain); if (!string.IsNullOrWhiteSpace(result)) { failFileAddress = "https://img1.tuhu.org" + result; } else { return(RedirectToAction("UploadBarCodeLog", "TireSecurityCode", new { msg = "文件上传失败" })); } barLog.FailFileName = failFileName; barLog.FailFileAddress = failFileAddress; barLog.CreateTime = DateTime.Now; barLog.LastUpdateDataTime = DateTime.Now; var insertLog_Difference = TireSecurityCodeConfigManager.InsertUploadBarCodeLog(barLog); if (insertLog_Difference) { return(RedirectToAction("UploadBarCodeLog", "TireSecurityCode", new { msg = "部分数据已存在,请检查错误日志,整理数据后重新上传." })); } else { return(RedirectToAction("UploadBarCodeLog", "TireSecurityCode", new { msg = "文件上传失败" })); } case -1: return(RedirectToAction("UploadBarCodeLog", "TireSecurityCode", new { msg = "上传失败,刷新页面重试." })); } #endregion #region 【记录上传文件日志】 barLog.FailFileName = failFileName; barLog.FailFileAddress = failFileAddress; barLog.CreateTime = DateTime.Now; barLog.LastUpdateDataTime = DateTime.Now; var insertLog = TireSecurityCodeConfigManager.InsertUploadBarCodeLog(barLog); if (!insertLog) { TireSecurityCodeConfigManager.DeleleBarCodeByBatchNum(barCodeBatchNum); return(RedirectToAction("UploadBarCodeLog", "TireSecurityCode", new { msg = "文件上传失败" })); } #endregion return(RedirectToAction("UploadBarCodeLog", "TireSecurityCode", new { msg = "导入成功" })); }
public JsonResult ImportGrade() { try { if (Request.Files.Count > 0) { var file = Request.Files[0]; if (!file.FileName.Contains(".xlsx") || !file.FileName.Contains(".xls")) { return(Json(new { Status = -1, Error = "请上传.xlsx文件或者.xls文件!" }, "text/html")); } var excel = new Controls.ExcelHelper(file.InputStream, file.FileName); var dt = excel.ExcelToDataTable("会员等级导入", true); List <VipMobileRequest> list = new List <VipMobileRequest>(); foreach (DataRow dr in dt.Rows) { list.Add(new VipMobileRequest() { Mobile = dr["Mobile"]?.ToString(), Pkid = dr["Pkid"] != DBNull.Value ? Convert.ToInt32(dr["Pkid"]) : 0, }); } IEnumerable <VipMobileRequest> vipList = list as IEnumerable <VipMobileRequest>; if (vipList.Any()) { if (vipList.Count() >= 500) { return(Json(new { Status = -6, Error = "单次不能超出500条数据操作!" }, "text/html")); } using (var client = new Tuhu.Service.Member.UserClient()) { var result = client.BatchCreateVipAuthorization(vipList, "Tuhu", "Import"); if (result.Success) { return(Json(new { Status = 1, Result = result.Result[1] }, "text/html")); } else { return(Json(new { Status = -5, Error = "服务器错误!" }, "text/html")); } } } else { return(Json(new { Status = -2, Error = "导入数据为空!" }, "text/html")); } } return(Json(new { Status = -3, Error = "未知错误!" }, "text/html")); } catch (Exception em) { return(Json(new { Status = -4, Error = em }, "text/html")); throw em; } }
public JsonResult Import(string batchId, DateTime endDateTime, DateTime startDateTime, int batchQty, int stockQty, int pkid) { if (Session["BatchId"] == null) { return(Json(new { Status = -4, Result = "回话结束,请重新导入" }, "text/html")); } if (batchId == "0") { batchId = Session["BatchId"].ToString(); } try { if (Request.Files.Count > 0) { var file = Request.Files[0]; if (!file.FileName.Contains(".xlsx") && !file.FileName.Contains(".xls")) { return(Json(new { Status = -1, Result = "请上传.xlsx文件或者.xls文件!" }, "text/html")); } var excel = new Controls.ExcelHelper(file.InputStream, file.FileName); var dt = excel.ExcelToDataTable("Sheet1", true); var lil = dt.ToList <string>(); List <string> errordt = new List <string>(); #region 批量将读取到的excel数据导入到数据库 for (int i = 0; i < dt.Rows.Count; i++) { DataRow dr = dt.Rows[i]; ThirdPartyExchangeCode eCode = new ThirdPartyExchangeCode(); //创建兑换码明细的对照关系 string exchangeCode = dr["ExchangeCode"]?.ToString(); eCode.ExchangeCode = exchangeCode; eCode.BatchGuid = new Guid(batchId); eCode.EndDateTime = endDateTime; eCode.GainDateTime = null; eCode.ImportDateTime = DateTime.Now; eCode.IsEnabled = true; eCode.IsGain = false; eCode.Operator = ControllerContext.HttpContext.User.Identity.Name; eCode.StartDateTime = startDateTime; var result = ThirdPartyExchangeCodeManage.InsertExchangeCode(eCode); if (result < 1) { errordt.Add(exchangeCode); } #endregion } HashSet <string> li = new HashSet <string>(lil); if (li.Count != lil.Count) { return(Json(new { Status = -3, Result = "兑换码不能重复,请检查兑换码!" }, "text/html")); } //#endregion var num = dt.Rows.Count - errordt.Count; batchQty = batchQty + num; stockQty = stockQty + num; var udResult = ThirdPartyExchangeCodeManage.UpdateQty(pkid, batchQty, stockQty); if (udResult > 0) { new OprLogManager().AddOprLog(new OprLog() { Author = HttpContext.User.Identity.Name, AfterValue = $"{{ pkid:{pkid},batchId:{batchId},startDateTime:{startDateTime},endDateTime:{endDateTime},batchQty:{batchQty },stockQty:{stockQty}", ChangeDatetime = DateTime.Now, ObjectID = pkid, ObjectType = "ExchangeCodeBatch", Operation = "导入兑换码", HostName = Request.UserHostName }); } #region 将问题输数据导出到excel using ( MemoryStream ms = new MemoryStream(System.IO.File.ReadAllBytes(Server.MapPath(@"~/Content/Export/批次导入兑换码.xlsx"))) ) { if (errordt.Any()) { //创建工作簿对象 XSSFWorkbook book = new XSSFWorkbook(ms); //创建excel 2007工作簿对象, //创建工作表 ISheet sheet1 = book.GetSheetAt(0); //创建行row IRow row1 = sheet1.CreateRow(0); #region 工作簿的首行,头部标题 row1.CreateCell(0).SetCellValue("ExchangeCode"); #endregion for (var i = 0; i < errordt.Count(); i++) { string item = errordt[i]; var row = sheet1.CreateRow(i + 1); row.CreateCell(0).SetCellValue(item); } Response.ContentType = "application/vnd.ms-excel"; Response.Charset = ""; Response.AppendHeader("Content-Disposition", "attachment;fileName=批次导入兑换码问题兑换码" + ".xlsx"); book.Write(Response.OutputStream); Response.End(); } } #endregion return(Json(new { Status = 0, Result = "写入完成" }, "text/html")); } return(Json(new { Status = -1, Result = "请选中文件" }, "text/html")); } catch (Exception em) { return(Json(new { Status = -2, Result = em.Message }, "text/html")); } }
public JsonResult ImportGrade() { try { if (Request.Files.Count > 0) { var file = Request.Files[0]; if (!file.FileName.Contains(".xlsx") && !file.FileName.Contains(".xls")) { return(Json(new { Status = -1, Error = "请上传.xlsx文件或者.xls文件!" }, "text/html")); } var excel = new Controls.ExcelHelper(file.InputStream, file.FileName); var dt = excel.ExcelToDataTable("sheet1", true); #region 初始化脏数据表结构 DataTable dirtyData = new DataTable(); dirtyData.Columns.Add(new DataColumn("开卡门店", typeof(string))); dirtyData.Columns.Add(new DataColumn("会员编号", typeof(string))); dirtyData.Columns.Add(new DataColumn("开卡日期", typeof(string))); dirtyData.Columns.Add(new DataColumn("会员姓名", typeof(string))); dirtyData.Columns.Add(new DataColumn("会员生日", typeof(string))); dirtyData.Columns.Add(new DataColumn("会员地址", typeof(string))); dirtyData.Columns.Add(new DataColumn("会员手机", typeof(string))); dirtyData.Columns.Add(new DataColumn("车牌号", typeof(string))); dirtyData.Columns.Add(new DataColumn("车型", typeof(string))); dirtyData.Columns.Add(new DataColumn("车厂", typeof(string))); dirtyData.Columns.Add(new DataColumn("车龄", typeof(string))); dirtyData.Columns.Add(new DataColumn("卡号", typeof(string))); dirtyData.Columns.Add(new DataColumn("条码", typeof(string))); dirtyData.Columns.Add(new DataColumn("卡别", typeof(string))); dirtyData.Columns.Add(new DataColumn("卡状态", typeof(string))); dirtyData.Columns.Add(new DataColumn("开卡电话", typeof(string))); dirtyData.Columns.Add(new DataColumn("UserID", typeof(string))); #endregion #region 最终插入完成数据表定义 DataTable finishData = new DataTable(); finishData.Columns.Add(new DataColumn("开卡门店", typeof(string))); finishData.Columns.Add(new DataColumn("会员编号", typeof(string))); finishData.Columns.Add(new DataColumn("开卡日期", typeof(string))); finishData.Columns.Add(new DataColumn("会员姓名", typeof(string))); finishData.Columns.Add(new DataColumn("会员生日", typeof(string))); finishData.Columns.Add(new DataColumn("会员地址", typeof(string))); finishData.Columns.Add(new DataColumn("会员手机", typeof(string))); finishData.Columns.Add(new DataColumn("车牌号", typeof(string))); finishData.Columns.Add(new DataColumn("车型", typeof(string))); finishData.Columns.Add(new DataColumn("车厂", typeof(string))); finishData.Columns.Add(new DataColumn("车龄", typeof(string))); finishData.Columns.Add(new DataColumn("卡号", typeof(string))); finishData.Columns.Add(new DataColumn("条码", typeof(string))); finishData.Columns.Add(new DataColumn("卡别", typeof(string))); finishData.Columns.Add(new DataColumn("卡状态", typeof(string))); finishData.Columns.Add(new DataColumn("开卡电话", typeof(string))); finishData.Columns.Add(new DataColumn("UserID", typeof(string))); #endregion foreach (DataRow dr in dt.Rows) { DataRow dirtyRow = dirtyData.NewRow(); DataRow finishRow = finishData.NewRow(); if (null == dr["会员手机"] || string.IsNullOrWhiteSpace(dr["会员手机"].ToString()) || //dr["会员手机"].ToString().Count() != 11 || //v2.0无视手机位数11 null == dr["会员编号"] || string.IsNullOrWhiteSpace(dr["会员编号"].ToString()) || null == dr["卡号"] || string.IsNullOrWhiteSpace(dr["卡号"].ToString()) || null == dr["条码"] || string.IsNullOrWhiteSpace(dr["条码"].ToString())) // 不正常手机号\空会员编号\空卡号\空条形码,存储脏数据表 { for (int i = 0; i < dr.ItemArray.Length; i++) { dirtyRow[i] = dr[i]; } dirtyData.Rows.Add(dirtyRow); continue; } #region step 1: 获取UserID或者添加用户到UserObject var tempUserId = YLHUserManager.GetUserId(dr["会员手机"].ToString()); //获取是否已经是tuhu用户 //var dtExistedMobile = finishData.Select("会员手机='" + dr["会员手机"].ToString() + "'"); if (string.IsNullOrEmpty(tempUserId)) //if (string.IsNullOrEmpty(tempUserId) && dtExistedMobile.Count() == 0) //不是tuhu用户,插入userobject,生成新的userid { tbl_UserObjectModel userObject = new tbl_UserObjectModel(); #region generate userObjectModel //UserID userObject.u_user_id = Guid.NewGuid().ToString("B"); //会员手机号 userObject.u_mobile_number = dr["会员手机"].ToString(); //会员姓名 userObject.u_last_name = null == dr["会员姓名"] || string.IsNullOrWhiteSpace(dr["会员姓名"].ToString()) || "NULL" == dr["会员姓名"].ToString().ToUpper() ? dr["会员手机"].ToString() : dr["会员姓名"].ToString().Substring(0, dr["会员姓名"].ToString().Length > 11 ? 11 : dr["会员姓名"].ToString().Length); //会员生日 DateTime birthday_userObject = new DateTime(); if (dr["会员生日"] == null || string.IsNullOrWhiteSpace(dr["会员生日"].ToString()) || "NULL" == dr["会员生日"].ToString().ToUpper()) { birthday_userObject = DateTime.Now; } else { if (DateTime.TryParseExact(dr["会员生日"].ToString(), "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None, out birthday_userObject)) { } else { birthday_userObject = DateTime.Now; } if (birthday_userObject < System.Data.SqlTypes.SqlDateTime.MinValue.Value) { birthday_userObject = DateTime.Now; //v2.0忽略时间不能插入数据库错误 } } userObject.dt_birthday = birthday_userObject; //会员邮箱 userObject.u_email_address = dr["会员手机"].ToString() + "@ylh.com"; #endregion #region 插入到数据库,如果插入失败,加入脏数据表,否则加入正常数据表 try { YLHUserManager.InsertUserToObjectTable(userObject); } catch (Exception ex) { for (int i = 0; i < dr.ItemArray.Length; i++) { dirtyRow[i] = dr[i]; } dirtyData.Rows.Add(dirtyRow); WebLog.LogException(ex); continue; } finishRow["UserID"] = userObject.u_user_id; for (int i = 0; i < dr.ItemArray.Length; i++) { finishRow[i] = dr[i]; } #endregion } else { if (!string.IsNullOrEmpty(tempUserId)) { finishRow["UserID"] = tempUserId; } //else //{ // finishRow["UserID"] = dtExistedMobile[0]["UserID"]; //} for (int i = 0; i < dr.ItemArray.Length; i++) { finishRow[i] = dr[i]; } } finishData.Rows.Add(finishRow); #endregion #region step 2: 把有UserID的项加到YLH_UserInfo表 var tempPKID = YLHUserManager.GetYLHUserInfoPKID(finishRow["UserID"].ToString()); YLHUserInfoModel ylhUserInfo = new YLHUserInfoModel(); //if (string.IsNullOrEmpty(tempUserId) && dtExistedMobile.Count() == 0) if (tempPKID < 0) { #region generate ylh_UserInfo //UserID ylhUserInfo.u_user_id = finishRow["UserID"].ToString(); //会员姓名 ylhUserInfo.MemberName = null == dr["会员姓名"] || string.IsNullOrWhiteSpace(dr["会员姓名"].ToString()) || "NULL" == dr["会员姓名"].ToString().ToUpper() ? dr["会员手机"].ToString() : dr["会员姓名"].ToString().Substring(0, dr["会员姓名"].ToString().Length > 11 ? 11 : dr["会员姓名"].ToString().Length); //会员编号 ylhUserInfo.MemberNumber = dr["会员编号"].ToString(); //会员地址 ylhUserInfo.MemberAddress = null == dr["会员地址"] || string.IsNullOrWhiteSpace(dr["会员地址"].ToString()) || "NULL" == dr["会员地址"].ToString().ToUpper() ? string.Empty : dr["会员地址"].ToString(); //会员手机号 ylhUserInfo.MemberPhone = dr["会员手机"].ToString(); //会员生日 DateTime birthday = new DateTime(); if (dr["会员生日"] == null || string.IsNullOrWhiteSpace(dr["会员生日"].ToString()) || "NULL" == dr["会员生日"].ToString().ToUpper()) { birthday = DateTime.Now; } else { if (DateTime.TryParseExact(dr["会员生日"].ToString(), "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None, out birthday)) { } else { birthday = DateTime.Now; } if (birthday < System.Data.SqlTypes.SqlDateTime.MinValue.Value) { birthday = DateTime.Now; //v2.0忽略时间不能插入数据库错误 } } ylhUserInfo.MemberBirthday = birthday; //会员积分 ylhUserInfo.Integration = 0; ylhUserInfo.CreatedTime = DateTime.Now; ylhUserInfo.UpdatedTime = DateTime.Now; #endregion #region 插入到数据库,如果插入失败,加入脏数据表,否则加入正常数据表 try { YLHUserManager.InsertYLHUserInfo(ylhUserInfo); } catch (Exception ex) { for (int i = 0; i < dr.ItemArray.Length; i++) { dirtyRow[i] = dr[i]; } dirtyData.Rows.Add(dirtyRow); WebLog.LogException(ex); continue; } #endregion #endregion } #region step 3: 把有UserID的项加到YLH_UserVipCardInfo表 YLHUserVipCardInfoModel ylhUserVipCardInfo = new YLHUserVipCardInfoModel(); #region generate ylh_UserVipCardInfo //UserID ylhUserVipCardInfo.u_user_id = finishRow["UserID"].ToString(); //车牌号 ylhUserVipCardInfo.CarNumber = null == dr["车牌号"] || string.IsNullOrWhiteSpace(dr["车牌号"].ToString()) || "NULL" == dr["车牌号"].ToString().ToUpper() ? string.Empty : dr["车牌号"].ToString(); //车厂信息 ylhUserVipCardInfo.CarFactory = null == dr["车厂牌"] || string.IsNullOrWhiteSpace(dr["车厂牌"].ToString()) || "NULL" == dr["车厂牌"].ToString().ToUpper() ? string.Empty : dr["车厂牌"].ToString(); //车型信息 ylhUserVipCardInfo.CarType = null == dr["车型"] || string.IsNullOrWhiteSpace(dr["车型"].ToString()) || "NULL" == dr["车型"].ToString().ToUpper() ? string.Empty : dr["车型"].ToString(); //车龄 ylhUserVipCardInfo.VehicleAge = null == dr["车龄"] || string.IsNullOrWhiteSpace(dr["车龄"].ToString()) || "NULL" == dr["车龄"].ToString().ToUpper() ? 0 :Convert.ToDouble(dr["车龄"]); //会员Vip卡号 ylhUserVipCardInfo.VipCardNumber = dr["卡号"].ToString(); //会员会员卡条形码 ylhUserVipCardInfo.Display_Card_NBR = dr["条码"].ToString(); //会员卡状态 ylhUserVipCardInfo.VipCardStatus = null == dr["卡状态"] || string.IsNullOrWhiteSpace(dr["卡状态"].ToString()) || "NULL" == dr["卡状态"].ToString().ToUpper() ? false : "有效" == dr["卡状态"].ToString(); //会员卡类别 ylhUserVipCardInfo.VipCardType = null == dr["卡别"] || string.IsNullOrWhiteSpace(dr["卡别"].ToString()) || "NULL" == dr["卡别"].ToString().ToUpper() ? string.Empty : dr["卡别"].ToString(); //会员卡开卡手机号 ylhUserVipCardInfo.RegisterPhone = null == dr["会员开卡电话"] || string.IsNullOrWhiteSpace(dr["会员开卡电话"].ToString()) || "NULL" == dr["会员开卡电话"].ToString().ToUpper() ? string.Empty : dr["会员开卡电话"].ToString(); //会员开卡门店 ylhUserVipCardInfo.RegisterAddress = null == dr["开卡门店"] || string.IsNullOrWhiteSpace(dr["开卡门店"].ToString()) || "NULL" == dr["开卡门店"].ToString().ToUpper() ? string.Empty : dr["开卡门店"].ToString(); //会员开卡日期 DateTime registerDate = new DateTime(); if (dr["开卡日期"] == null || string.IsNullOrWhiteSpace(dr["开卡日期"].ToString()) || "NULL" == dr["开卡日期"].ToString().ToUpper()) { registerDate = DateTime.Now; } else { if (DateTime.TryParseExact(dr["开卡日期"].ToString(), "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None, out registerDate)) { } else { registerDate = DateTime.Now; } if (registerDate < System.Data.SqlTypes.SqlDateTime.MinValue.Value) { registerDate = DateTime.Now; //v2.0忽略时间不能插入数据库错误 } } ylhUserVipCardInfo.RegisterDate = registerDate; ylhUserVipCardInfo.CreatedTime = DateTime.Now; ylhUserVipCardInfo.UpdatedTime = DateTime.Now; #endregion #region 插入到数据库,如果插入失败,加入脏数据表,否则加入正常数据表 try { YLHUserManager.InsertYLHVipCardInfo(ylhUserVipCardInfo); } catch (Exception ex) { for (int i = 0; i < dr.ItemArray.Length; i++) { dirtyRow[i] = dr[i]; } dirtyData.Rows.Add(dirtyRow); WebLog.LogException(ex); continue; } #endregion #endregion } //YLHUserManager.ExportDataTableToHtml(dirtyData, @"C:\Users\zhangchen3\Desktop\永隆行\技术文档\导用户数据\脏数据.HTML"); //YLHUserManager.ExportDataTableToHtml(finishData, @"C:\Users\zhangchen3\Desktop\永隆行\技术文档\导用户数据\完成数据.HTML"); Response.Clear(); Response.Charset = "UTF-8"; Response.Buffer = true; Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); Response.AppendHeader("Content-Disposition", "attachment;filename=\"" + System.Web.HttpUtility.UrlEncode("永隆行用户导入失败记录", System.Text.Encoding.UTF8) + ".xls\""); Response.ContentType = "application/ms-excel"; string colHeaders = string.Empty; string ls_item = string.Empty; DataRow[] myRow = dirtyData.Select(); int cl = dirtyData.Columns.Count; foreach (DataRow row in myRow) { for (int i = 0; i < cl; i++) { if (i == (cl - 1)) { ls_item += row[i].ToString() + "\n"; } else { ls_item += row[i].ToString() + "\t"; } } Response.Output.Write(ls_item); ls_item = string.Empty; } Response.Output.Flush(); Response.End(); return(Json(new { Status = -1, Error = "全部写入完成" }, "text/html")); } return(Json(new { Status = -1, Error = "请选择文件" }, "text/html")); } catch (Exception ex) { WebLog.LogException(ex); return(Json(new { Status = -2, Error = ex }, "text/html")); } }
public JsonResult ImportGrade() { try { if (Request.Files.Count > 0) { var file = Request.Files[0]; if (!file.FileName.Contains(".xlsx") && !file.FileName.Contains(".xls")) { return(Json(new { Status = -1, Error = "请上传.xlsx文件或者.xls文件!" }, "text/html")); } var excel = new Controls.ExcelHelper(file.InputStream, file.FileName); var dt = excel.ExcelToDataTable("sheet1", true); var pids = new List <string>(); #region 失败数据表结构 DataTable dirtyData = new DataTable(); dirtyData.Columns.Add(new DataColumn("PID", typeof(string))); dirtyData.Columns.Add(new DataColumn("产品名称(质检)", typeof(string))); dirtyData.Columns.Add(new DataColumn("产地", typeof(string))); dirtyData.Columns.Add(new DataColumn("轮辋保护", typeof(string))); dirtyData.Columns.Add(new DataColumn("载重", typeof(string))); dirtyData.Columns.Add(new DataColumn("M+S", typeof(string))); dirtyData.Columns.Add(new DataColumn("Treadwear耐磨指数", typeof(string))); dirtyData.Columns.Add(new DataColumn("Traction抓地指数", typeof(string))); dirtyData.Columns.Add(new DataColumn("Temperature温度指数", typeof(string))); dirtyData.Columns.Add(new DataColumn("胎冠结构聚酯层数", typeof(string))); dirtyData.Columns.Add(new DataColumn("胎冠结构钢丝层数", typeof(string))); dirtyData.Columns.Add(new DataColumn("胎冠结构尼龙层数", typeof(string))); dirtyData.Columns.Add(new DataColumn("胎侧结构聚酯层数", typeof(string))); dirtyData.Columns.Add(new DataColumn("轮胎标签滚动阻力", typeof(string))); dirtyData.Columns.Add(new DataColumn("轮胎标签湿滑抓地性", typeof(string))); dirtyData.Columns.Add(new DataColumn("轮胎标签噪音", typeof(string))); dirtyData.Columns.Add(new DataColumn("花纹对称", typeof(string))); dirtyData.Columns.Add(new DataColumn("导向", typeof(string))); dirtyData.Columns.Add(new DataColumn("插入数据结果", typeof(string))); #endregion foreach (DataRow dr in dt.Rows) { DataRow dirtyRow = dirtyData.NewRow(); if (null == dr["PID"] || string.IsNullOrWhiteSpace(dr["PID"].ToString())) { for (int i = 0; i < dr.ItemArray.Length; i++) { dirtyRow[i] = dr[i]; } dirtyRow["插入数据结果"] = "失败,PID缺失."; dirtyData.Rows.Add(dirtyRow); continue; } else { var existData = TireSpecParamsConfigManager.CheckPidExist(dr["PID"].ToString().Trim()); if (dr["产品名称(质检)"] != null && !string.IsNullOrWhiteSpace(dr["产品名称(质检)"].ToString())) { TireSpecParamsConfig config = new TireSpecParamsConfig(); #region generate model config.PId = dr["PID"].ToString().Trim(); config.QualityInspectionName = dr["产品名称(质检)"]?.ToString(); config.OriginPlace = dr["产地"]?.ToString(); config.RimProtection = ( null == dr["轮辋保护"] || string.IsNullOrWhiteSpace(dr["轮辋保护"].ToString()) || !IsNum(dr["轮辋保护"].ToString()) ) ? false : Convert.ToInt32(dr["轮辋保护"]) == 1 ? true : false; config.TireLoad = dr["载重"]?.ToString(); config.MuddyAndSnow = ( null == dr["M+S"] || string.IsNullOrWhiteSpace(dr["M+S"].ToString()) || !IsNum(dr["M+S"].ToString()) ) ? false : Convert.ToInt32(dr["M+S"]) == 1 ? true : false; config.ThreeT_Treadwear = dr["Treadwear耐磨指数"]?.ToString(); config.ThreeT_Traction = dr["Traction抓地指数"]?.ToString(); config.ThreeT_Temperature = dr["Temperature温度指数"]?.ToString(); config.TireCrown_Polyester = ( null == dr["胎冠结构聚酯层数"] || string.IsNullOrWhiteSpace(dr["胎冠结构聚酯层数"].ToString()) || !IsNum(dr["胎冠结构聚酯层数"].ToString()) ) ? 0 : Convert.ToInt32(dr["胎冠结构聚酯层数"]); config.TireCrown_Steel = ( null == dr["胎冠结构钢丝层数"] || string.IsNullOrWhiteSpace(dr["胎冠结构钢丝层数"].ToString()) || !IsNum(dr["胎冠结构钢丝层数"].ToString()) ) ? 0 : Convert.ToInt32(dr["胎冠结构钢丝层数"]); config.TireCrown_Nylon = ( null == dr["胎冠结构尼龙层数"] || string.IsNullOrWhiteSpace(dr["胎冠结构尼龙层数"].ToString()) || !IsNum(dr["胎冠结构尼龙层数"].ToString()) ) ? 0 : Convert.ToInt32(dr["胎冠结构尼龙层数"]); config.TireSideWall_Polyester = ( null == dr["胎侧结构聚酯层数"] || string.IsNullOrWhiteSpace(dr["胎侧结构聚酯层数"].ToString()) || !IsNum(dr["胎侧结构聚酯层数"].ToString()) ) ? 0 : Convert.ToInt32(dr["胎侧结构聚酯层数"]); config.TireLable_RollResistance = dr["轮胎标签滚动阻力"]?.ToString(); config.TireLable_WetGrip = dr["轮胎标签湿滑抓地性"]?.ToString(); config.TireLable_Noise = dr["轮胎标签噪音"]?.ToString(); config.PatternSymmetry = dr["花纹对称"]?.ToString(); config.TireGuideRotation = dr["导向"]?.ToString(); config.FactoryCode = dr["工厂编码"].ToString(); config.GrooveNum = ( null == dr["沟槽数量"] || string.IsNullOrWhiteSpace(dr["沟槽数量"].ToString()) || !IsNum(dr["沟槽数量"].ToString()) ) ? 0 : Convert.ToInt32(dr["沟槽数量"]); config.Remark = dr["备注"].ToString(); config.CreateTime = DateTime.Now; config.LastUpdateDataTime = DateTime.Now; #endregion if (!existData) { try { TireSpecParamsConfigManager.InsertTireSpecParamsConfig(config); } catch (Exception ex) { for (int i = 0; i < dr.ItemArray.Length; i++) { dirtyRow[i] = dr[i]; } dirtyRow["插入数据结果"] = "数据插入失败." + ex.Message; dirtyData.Rows.Add(dirtyRow); continue; } } else { try { TireSpecParamsConfigManager.UpdateTireSpecParamsConfig(config); } catch (Exception ex) { for (int i = 0; i < dr.ItemArray.Length; i++) { dirtyRow[i] = dr[i]; } dirtyRow["插入数据结果"] = "数据更新失败." + ex.Message; dirtyData.Rows.Add(dirtyRow); continue; } } pids.Add(config.PId); } } } //批量刷新缓存 using (var clientConfig = new ProductConfigClient()) { clientConfig.RefreashTireSpecParamsConfigCache(pids); } //Response.Clear(); //Response.Charset = "UTF-8"; //Response.Buffer = true; //Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); //Response.AppendHeader("Content-Disposition", "attachment;filename=\"" + System.Web.HttpUtility.UrlEncode("轮胎3T指数导入失败记录", System.Text.Encoding.UTF8) + ".xls\""); //Response.ContentType = "application/ms-excel"; //string colHeaders = string.Empty; //string ls_item = string.Empty; //DataRow[] myRow = dirtyData.Select(); //int cl = dirtyData.Columns.Count; //foreach (DataRow row in myRow) //{ // for (int i = 0; i < cl; i++) // { // if (i == (cl - 1)) // { // ls_item += row[i].ToString() + "\n"; // } // else // { // ls_item += row[i].ToString() + "\t"; // } // } //} //byte[] bytes = System.Text.Encoding.UTF8.GetBytes(ls_item.ToString()); //Response.AddHeader("Content-Length", bytes.Length.ToString()); //Response.BinaryWrite(bytes); //Response.Flush(); //Response.End(); return(Json(new { Status = 0, Result = "写入完成" })); } else { return(Json(new { Status = -1, Error = "请选择文件" })); } } catch (Exception ex) { WebLog.LogException(ex); return(Json(new { Status = -2, Error = ex })); } }