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