public static int AddProducts(YLHProductModel product)
{
DalYLHProduct dal = new DalYLHProduct();
return(dal.AddProducts(product));
}
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"));
}
}
/// <summary>
///将数据导入到永隆行的商品表中
/// </summary>
/// <param name="product"></param>
/// <returns></returns>
public int AddProducts(YLHProductModel product)
{
#region sql
string sql = @"
INSERT Tuhu_productcatalog..YLH_Product
( oid ,
StoreName ,
[1stProductType] ,
[2ndProductType] ,
[3rdProductType] ,
[4thProductType] ,
[5thProductType] ,
counter_id ,
ProductNunber ,
ProductName ,
Specification ,
Price ,
SystemQuantity ,
SyetemSettlement ,
RealQuantity ,
RealSettlement ,
QuantityDiff ,
SettlementDiff ,
DiffReason ,
LastPurchaseDate ,
YearInWareHouse ,
DayInWareHouse ,
DistributionAmount ,
BuyoutAmount ,
MonthlySales ,
QualityClassification ,
Remark ,
CreatedTime ,
UpdatedTime ,
cy_list_price ,
MonthInWareHouse
)
VALUES ( @oid , -- oid - int
@StoreName , -- StoreName - nvarchar(50)
@1stProductType , -- 1stProductType - nvarchar(50)
@2ndProductType, -- 2ndProductType - nvarchar(50)
@3rdProductType, -- 3rdProductType - nvarchar(50)
@4thProdictType, -- 4thProductType - nvarchar(50)
@5thProductType, -- 5thProductType - nvarchar(50)
@counter_id , -- counter_id - int
@ProductNunber, -- ProductNunber - nvarchar(256)
@ProductName, -- ProductName - nvarchar(128)
@Specification, -- Specification - nvarchar(128)
@Price , -- Price - money
@SystemQuantity , -- SystemQuantity - int
@SyetemSettlement , -- SyetemSettlement - money
@RealQuantity , -- RealQuantity - int
@RealSettlement , -- RealSettlement - money
@QuantityDiff , -- QuantityDiff - int
@SettlementDiff , -- SettlementDiff - money
@DiffReason , -- DiffReason - nvarchar(256)
@LastPurchaseDate , -- LastPurchaseDate - datetime
@YearInWareHouse , -- YearInWareHouse - int
@DayInWareHouse , -- DayInWareHouse - int
@DistributionAmount , -- DistributionAmount - money
@BuyoutAmount , -- BuyoutAmount - money
@MonthlySales , -- MonthlySales - int
@QualityClassificat , -- QualityClassification - nvarchar(50)
@Remark , -- Remark - nvarchar(50)
GETDATE() , -- CreatedTime - datetime
GETDATE() , -- UpdatedTime - datetime
@cy_list_price , -- cy_list_price - money
@MonthInWareHouse -- MonthInWareHouse - int
)";
#endregion
using (var db = DbHelper.CreateDefaultDbHelper())
{
SqlCommand cmd = new SqlCommand(sql);
#region sqlParameters
cmd.Parameters.AddWithValue("@oid", product.oid);
cmd.Parameters.AddWithValue("@StoreName", product.StoreName);
cmd.Parameters.AddWithValue("@1stProductType", product.ProductType1St);
cmd.Parameters.AddWithValue("@2ndProductType", product.ProductType2Nd);
cmd.Parameters.AddWithValue("@3rdProductType", product.ProductType3Rd);
cmd.Parameters.AddWithValue("@4thProdictType", product.ProductType4Th);
cmd.Parameters.AddWithValue("@5thProductType", product.ProductType5Th);
cmd.Parameters.AddWithValue("@counter_id", product.counter_id);
cmd.Parameters.AddWithValue("@ProductNunber", product.ProductNunber);
cmd.Parameters.AddWithValue("@ProductName", product.ProductName);
cmd.Parameters.AddWithValue("@Specification", product.Specification);
cmd.Parameters.AddWithValue("@Price", product.Price);
cmd.Parameters.AddWithValue("@SystemQuantity", product.SystemQuantity);
cmd.Parameters.AddWithValue("@SyetemSettlement", product.SyetemSettlement);
cmd.Parameters.AddWithValue("@RealQuantity", product.RealQuantity);
cmd.Parameters.AddWithValue("@RealSettlement", product.RealSettlement);
cmd.Parameters.AddWithValue("@QuantityDiff", product.QuantityDiff);
cmd.Parameters.AddWithValue("@SettlementDiff", product.SettlementDiff);
cmd.Parameters.AddWithValue("@DiffReason", product.DiffReason);
cmd.Parameters.AddWithValue("@LastPurchaseDate", product.LastPurchaseDate);
cmd.Parameters.AddWithValue("@YearInWareHouse", product.YearInWareHouse);
cmd.Parameters.AddWithValue("@DayInWareHouse", product.DayInWareHouse);
cmd.Parameters.AddWithValue("@DistributionAmount", product.DistributionAmount);
cmd.Parameters.AddWithValue("@BuyoutAmount", product.BuyoutAmount);
cmd.Parameters.AddWithValue("@MonthlySales", product.MonthlySales);
cmd.Parameters.AddWithValue("@QualityClassificat", product.QualityClassification);
cmd.Parameters.AddWithValue("@Remark", product.Remark);
cmd.Parameters.AddWithValue("@cy_list_price", product.cy_list_price);
cmd.Parameters.AddWithValue("@MonthInWareHouse", product.MonthInWareHouse);
#endregion
var result = db.ExecuteNonQuery(cmd);
return(result);
}
}
