//public void StartProcess()
public void StartProcess()
{
DataSet ds = loadingHMDataFromExcel();
using (PermaisuriCMSEntities db = new PermaisuriCMSEntities())
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
try
{
#region 提出Excel数据
string line = dr["line"].ToString();
OnOperateNotify(this, new HMEventArgs(String.Format(">>>>>>>>>>>>>.Start the Lien of {0} Item <<<<<<<<<<<<<<<<<<,,,", line)));
HMLog.Info(String.Format(">>>>>>>>>>>>>.Start the Number of {0} Item <<<<<<<<<<<<<<<<<<,,,", line));
string stockKey = dr["STOCK KEY-sales data"].ToString();
string HMNUM = dr["HMNUM-New"].ToString();
string ProductName = dr["Product Name"].ToString();
string Category = dr["Category"].ToString();
string SubCategory = dr["SubCategory"].ToString();
string strFirstCost = dr["first cost"].ToString();
string strLandedCost = dr["landed cost"].ToString();
string strShippingCost = dr[" shipping cost"].ToString();
decimal landedCost = 0;
decimal firstCost = 0;
decimal ShippingCost = 0;
decimal.TryParse(strFirstCost, out firstCost);
decimal.TryParse(strLandedCost, out landedCost);
decimal.TryParse(strShippingCost, out ShippingCost);
string strBoxWeight = dr["Box Weight"].ToString();
string strCTNLength = dr["Box Length"].ToString();
string strCTNWidth = dr["Box Width"].ToString();
string strCTNHeight = dr["Box Height"].ToString();
decimal CTNLength = 0;
decimal.TryParse(strCTNLength, out CTNLength);
decimal CTNWidth = 0;
decimal.TryParse(strCTNWidth, out CTNWidth);
decimal CTNHeight = 0;
decimal.TryParse(strCTNHeight, out CTNHeight);
decimal CTNWeight = 0;
decimal.TryParse(strBoxWeight, out CTNWeight);
string strLoadability = dr["Loadability-webpo"].ToString();
decimal Loadability = 0;
decimal.TryParse(strLoadability, out Loadability);
string prDimensions = dr["Product Dimension"].ToString();
decimal DimLength = 0;
decimal DimWidth = 0;
decimal DimHeight = 0;
if (!String.IsNullOrEmpty(prDimensions))
{
var dims = prDimensions.Split('x');
if (dims.Length > 3)//不规范 以后处理
{
decimal.TryParse(dims[0], out DimLength);
decimal.TryParse(dims[1], out DimWidth);
decimal.TryParse(dims[2], out DimHeight);
}
}
string SKUORDER = dr["SKUORDER"].ToString();
string MERCHANTID = dr["MERCHANTID"].ToString().Trim();
string PrDescription = dr["Intro Sentence(s)"].ToString();
string Bullets = dr["Bullet Description"].ToString();
string UPC = String.Empty;
string BESTUPC = dr["BEST UPC"].ToString();
string CAUPC = dr["COSTCO/AMAZON UPC"].ToString();
string GUPC = dr["GROUPON UPC"].ToString();
string GDFUPC = dr["GDF UPC"].ToString();
if (!String.IsNullOrEmpty(BESTUPC))
{
UPC = BESTUPC;
}
else if (!String.IsNullOrEmpty(CAUPC))
{
UPC = CAUPC;
}
else if (!String.IsNullOrEmpty(GUPC))
{
UPC = GUPC;
}
else if (!String.IsNullOrEmpty(GDFUPC))
{
UPC = GDFUPC;
}
//normalselling是我们和网站的价钱，retail price是网站和终端客人的价钱 Boonie
string strRetail = dr["Normallselling"].ToString();
string strSalePrice = dr["retail price"].ToString();
decimal Retail = 0;
decimal.TryParse(strRetail, out Retail);
decimal SalePrice = 0;
decimal.TryParse(strSalePrice, out SalePrice);
#endregion
//根据HMNUM判断表内是否存在这条记录，不存在则插入对于的价格、箱柜尺寸、信息，否则不插入，直接插入SKU和HM-SKU关联
long ProductID = 0;
var curHM = db.CMS_HMNUM.FirstOrDefault(c => c.HMNUM == HMNUM);
if (curHM == null)
{
#region 插入HM的价格信息
var newHMCost = new CMS_HM_Costing
{
CreateBy = UpdateBy,
CreateOn = DateTime.Now,
EffectiveDate = DateTime.Now,
EstimateFreight = ShippingCost,
LandedCost = landedCost,
FirstCost = firstCost,
HMNUM = HMNUM,
HisProductID = 0,
};
db.CMS_HM_Costing.Add(newHMCost);
#endregion
var HMCostID = newHMCost.HMCostID;
long HMCategoryID = 0; long HMColourID = 0; long HMMaterialID = 0;
string HMName = ProductName;
//long HMColorID = 0;从这张表里面获取 类别 颜色 和材料ID
var temObj = db.WebPO_HM_Colour_Material_V.FirstOrDefault(v => v.HMNUM == HMNUM);
if (temObj != null)
{
HMCategoryID = temObj.CategoryID.ConvertToNotNull();
HMColourID = temObj.ColourID;
HMMaterialID = temObj.MaterialID;
HMName = temObj.ProductName;
}
#region 插入HM的基础信息
var newHM = new CMS_HMNUM
{
HMNUM = HMNUM,
// ProductName = ProductName,基础产品的Name应该要从WEBPO拿 而不是从Excel表单拿
ProductName = HMName,
StockKey = stockKey,
HMCostID = HMCostID,
CategoryID = HMCategoryID,
MaterialID = HMMaterialID,
ColourID = HMColourID,
SubCategoryID = 0,
IsGroup = false,
StatusID = 0,
Loadability = Loadability,
CreateOn = DateTime.Now,
CreateBy = UpdateBy,
ModifyOn = DateTime.Now,
ModifyBy = UpdateBy,
MasterPack = 1//暂时设置为1，后面再手动跟新
};
db.CMS_HMNUM.Add(newHM);
db.SaveChanges();//顺序不能掉，否则 ProductID = newHM.ProductID; 取出来的ID还是0；
ProductID = newHM.ProductID;
newHMCost.HisProductID = ProductID;
#endregion
#region 插入箱子基础信息
//插入尺寸
var newCTN = new CMS_ProductCTN
{
ProductID = ProductID,
HMNUM = HMNUM,
CTNTitle = "S/1",
CTNLength = CTNLength,
CTNWidth = CTNWidth,
CTNHeight = CTNHeight,
CTNWeight = CTNWeight,
CTNCube = 0,
CreateOn = DateTime.Now,
UpdateOn = DateTime.Now,
UpdateBy = UpdateBy
};
db.CMS_ProductCTN.Add(newCTN);
#endregion
#region 插入尺寸基础信息
var newDim = new CMS_ProductDimension
{
ProductID = ProductID,
HMNUM = HMNUM,
DimTitle = "S/1",
DimLength = DimLength,
DimWidth = DimWidth,
DimHeight = DimHeight,
DimCube = 0,
CreateOn = DateTime.Now,
UpdateOn = DateTime.Now,
UpdateBy = UpdateBy
};
db.CMS_ProductDimension.Add(newDim);
#endregion
}
else
{
ProductID = curHM.ProductID;
}
db.SaveChanges();
#region 插入SKU价格信息
var newSKUCost = new CMS_SKU_Costing
{
CreateBy = UpdateBy,
CreateOn = DateTime.Now,
EffectiveDate = DateTime.Now,
SalePrice = Retail
};
db.CMS_SKU_Costing.Add(newSKUCost);
db.SaveChanges();
var SKUCostID = newSKUCost.SKUCostID;
#endregion
#region 插入SKU基础信息
//插入SKUOrder
var ChannelObj = db.Channel.FirstOrDefault(c => c.ChannelName == MERCHANTID);
var ChannelID = 0;
if (ChannelObj != null)
{
ChannelID = ChannelObj.ChannelID;
}
long ColourID = 0;
string skuColor = dr["Color"].ToString();
string skuMaterial = dr["Material"].ToString();
if (!string.IsNullOrEmpty(skuColor))
{
var ColorMode = db.CMS_SKU_Colour.FirstOrDefault(c => c.ColourName == skuColor);
if (ColorMode == null)
{
var newColour = new CMS_SKU_Colour
{
ColourName = skuColor,
CreateBy = UpdateBy,
CreateOn = DateTime.Now,
ModifyBy = UpdateBy,
ModifyOn = DateTime.Now
};
db.CMS_SKU_Colour.Add(newColour);
db.SaveChanges();
ColourID = newColour.ColourID;
}
else
{
ColourID = ColorMode.ColourID;
}
}
long MaterialID = 0;
if (!string.IsNullOrEmpty(skuMaterial))
{
var Mode = db.CMS_SKU_Material.FirstOrDefault(m => m.MaterialName == skuMaterial);
if (Mode == null)
{
var newMaterial = new CMS_SKU_Material
{
MaterialName = skuMaterial,
CreateBy = UpdateBy,
CreateOn = DateTime.Now,
ModifyBy = UpdateBy,
ModifyOn = DateTime.Now
};
db.CMS_SKU_Material.Add(newMaterial);
db.SaveChanges();
MaterialID = newMaterial.MaterialID;
}
else
{
MaterialID = Mode.MaterialID;
}
}
CMS_SKU newProudct = new CMS_SKU
{
SKU = SKUORDER,
ProductName = ProductName == "" ? SKUORDER : ProductName,//Name为空则用SKUOrder代替
SKU_QTY = 0,
//Price = 0,
ChannelID = ChannelID,
UPC = UPC,
StatusID = 4, //Compelted
Visibility = 1, //---报表必须为1才有效 2013年12月14日10:08:27
ProductDesc = PrDescription,
Specifications = Bullets,
Keywords = "",
BrandID = 2,//defult
RetailPrice = SalePrice,
URL = "",
SKUCostID = SKUCostID,
CategoryID = 0,
ColourID = ColourID,
MaterialID = MaterialID,
SubCategoryID = 0,
CreateBy = UpdateBy,
CreateOn = DateTime.Now,
UpdateBy = UpdateBy,
UpdateOn = DateTime.Now
};
db.CMS_SKU.Add(newProudct);
db.SaveChanges();
var SKUID = newProudct.SKUID;
newSKUCost.HisSKUID = SKUID;
#endregion
#region HM-SKU关联
//HM-SKU关联
var newRelation = new SKU_HM_Relation
{
CreateBy = UpdateBy,
CreateOn = DateTime.Now,
ProductID = ProductID,
R_QTY = 1,
SKUID = SKUID
};
db.SKU_HM_Relation.Add(newRelation);
#endregion
db.SaveChanges();
HMLog.Info(String.Format(">>>>>>>>>>>>>.End the Number of {0} Item <<<<<<<<<<<<<<<<<<", line));
OnOperateNotify(this, new HMEventArgs(String.Format(">>>>>>>>>>>>>.End the Lien of {0} Item <<<<<<<<<<<<<<<<<<,,,", line)));
OnOperateNotify(this, new HMEventArgs(String.Format("Line为{0}的数据成功插入到数据库", line)));
OnOperateNotify(this, new HMEventArgs(""));
OnOperateNotify(this, new HMEventArgs(""));
}
catch (DbEntityValidationException e)
{
OnOperateNotify(this, new HMEventArgs("Error!"));
OnOperateNotify(this, new HMEventArgs("出错啦！!"));
HMLog.Error("");
HMLog.Error("");
foreach (var eve in e.EntityValidationErrors)
{
HMLog.Error("");
HMLog.Error("");
//HMLog.Error("Entity of type \"{0}\" in state \"{1}\" has the following validation errors:",eve.Entry.Entity.GetType().Name, eve.Entry.State);
foreach (var ve in eve.ValidationErrors)
{
HMLog.Error("- Property: \"{0}\", Error: \"{1}\"", ve.PropertyName, ve.ErrorMessage);
OnOperateNotify(this, new HMEventArgs(String.Format("- Property: \"{0}\", Error: \"{1}\"", ve.PropertyName, ve.ErrorMessage)));
}
HMLog.Error("");
}
HMLog.Error("");
HMLog.Error("");
OnOperateNotify(this, new HMEventArgs(""));
OnOperateNotify(this, new HMEventArgs(""));
}
catch (Exception ex)
{
OnOperateNotify(this, new HMEventArgs("Error~~~~~~~~~~~~~~~"));
OnOperateNotify(this, new HMEventArgs("出错啦~~~~~~~~~~~~~~"));
OnOperateNotify(this, new HMEventArgs(ex.Message));
HMLog.Error("");
HMLog.Error("Exception Started");
HMLog.Error(ex.Message);
HMLog.Error(ex.Source);
HMLog.Error(ex.StackTrace);
HMLog.Error("Exception End");
HMLog.Error("");
OnOperateNotify(this, new HMEventArgs(""));
OnOperateNotify(this, new HMEventArgs(""));
}
}
}// end of using (PermaisuriCMSEntities db = new PermaisuriCMSEntities())
if (ds != null) //release memory
{
ds.Dispose();
ds = null;
}
}//end of StartProcess();
//public void StartProcess()
public void StartProcess()
{
//构建连接字符串
OleDbConnection Conn = new OleDbConnection(connStr);
Conn.Open();
//填充数据
String SheetName = ConfigurationManager.AppSettings["SheetName"];
string sql = string.Format("select * from [{0}$]", SheetName);
OleDbDataAdapter da = new OleDbDataAdapter(sql, connStr);
DataSet ds = new DataSet();
da.Fill(ds);
Conn.Close();
//StringBuilder sb = new StringBuilder();
long groupProductID = 0;
using (PermaisuriCMSEntities db = new PermaisuriCMSEntities())
{
int iCount = 0;
foreach (DataRow dr in ds.Tables[0].Rows)
{
if (iCount > 1659)//发现无限循环下去，如果大于这个数据，就退出
{
HMLog.Info("已经全部导入数据库，可以退出当前程序");
OnOperateNotify(this, new HMEventArgs("已经全部导入数据库，可以退出当前程序"));
break;
}
iCount++;
try
{
#region 提出Excel数据
string lineHMType = dr["Group"].ToString();
OnOperateNotify(this, new HMEventArgs(String.Format(">>>>>>>>>>>>>.Start the Lien of {0} Item <<<<<<<<<<<<<<<<<<,,,", iCount)));
HMLog.Info(String.Format(">>>>>>>>>>>>>.Start the Number of {0} Item <<<<<<<<<<<<<<<<<<,,,", iCount));
string stockKey = dr["STOCK KEY-sales data"].ToString();
String HMNUM = string.Empty;
string BasicHMNUM = dr["HMNUM-New"].ToString();
string GroupHMNUM = dr["WEPO HM#"].ToString();
//IsGroup = lineHMType == "group" ? true : false,
//if (lineHMType == "group")
if (string.Compare(lineHMType, "Group", StringComparison.OrdinalIgnoreCase) == 0)
{
HMNUM = GroupHMNUM;
}
else
{
HMNUM = BasicHMNUM;
}
string ProductName = dr["Product Name"].ToString();
string Category = dr["Category"].ToString();
string SubCategory = dr["SubCategory"].ToString();
string strFirstCost = dr["first cost"].ToString();
string strLandedCost = dr["landed cost"].ToString();
string strShippingCost = dr[" shipping cost"].ToString();
decimal landedCost = 0;
decimal firstCost = 0;
decimal ShippingCost = 0;
decimal.TryParse(strFirstCost, out firstCost);
decimal.TryParse(strLandedCost, out landedCost);
decimal.TryParse(strShippingCost, out ShippingCost);
string strBoxWeight = dr["Box Weight"].ToString();
string strCTNLength = dr["Box Length"].ToString();
string strCTNWidth = dr["Box Width"].ToString();
string strCTNHeight = dr["Box Height"].ToString();
decimal CTNLength = 0;
decimal.TryParse(strCTNLength, out CTNLength);
decimal CTNWidth = 0;
decimal.TryParse(strCTNWidth, out CTNWidth);
decimal CTNHeight = 0;
decimal.TryParse(strCTNHeight, out CTNHeight);
decimal CTNWeight = 0;
decimal.TryParse(strBoxWeight, out CTNWeight);
string prDimensions = dr["Product Dimension"].ToString();
decimal DimLength = 0;
decimal DimWidth = 0;
decimal DimHeight = 0;
if (!String.IsNullOrEmpty(prDimensions))
{
var dims = prDimensions.Split('x');
if (dims.Length > 3)//不规范 以后处理
{
decimal.TryParse(dims[0], out DimLength);
decimal.TryParse(dims[1], out DimWidth);
decimal.TryParse(dims[2], out DimHeight);
}
}
string strLoadability = dr["Loadability-webpo"].ToString();
decimal Loadability = 0;
decimal.TryParse(strLoadability, out Loadability);
string SKUORDER = dr["SKUORDER"].ToString();
string MERCHANTID = dr["MERCHANTID"].ToString().Trim();
string PrDescription = dr["Intro Sentence(s)"].ToString();
string Bullets = dr["Bullet Description"].ToString();
string UPC = String.Empty;
string BESTUPC = dr["BEST UPC"].ToString();
// string CAUPC = dr["COSTCO UPC"].ToString(); COSTCO/AMAZON UPC
string CAUPC = dr["COSTCO/AMAZON UPC"].ToString();
// string GUPC = dr["GROUPON UPC"].ToString();
string GDFUPC = dr["GDF UPC"].ToString();
if (!String.IsNullOrEmpty(BESTUPC))
{
UPC = BESTUPC;
}
else if (!String.IsNullOrEmpty(CAUPC))
{
UPC = CAUPC;
}
//else if (!String.IsNullOrEmpty(GUPC))
//{
// UPC = GUPC;
//}
else if (!String.IsNullOrEmpty(GDFUPC))
{
UPC = GDFUPC;
}
//normalselling是我们和网站的价钱，retail price是网站和终端客人的价钱 Boonie
string strRetail = dr["Normallselling"].ToString();
string strSalePrice = dr["retail price"].ToString();
decimal Retail = 0;
decimal.TryParse(strRetail, out Retail);
decimal SalePrice = 0;
decimal.TryParse(strSalePrice, out SalePrice);
//string StrSkuColor = dr["New Color"].ToString();
//long SKUColorID = 0;
//long.TryParse(StrSkuColor, out SKUColorID);
//string StrMaterialColor = dr["New Material"].ToString();
//long SKUMaterialID = 0;
//long.TryParse(StrMaterialColor, out SKUMaterialID);
#endregion
#region HM的价格信息
var newHMCost = new CMS_HM_Costing
{
CreateBy = UpdateBy,
CreateOn = DateTime.Now,
EffectiveDate = DateTime.Now,
EstimateFreight = ShippingCost,
LandedCost = landedCost,
FirstCost = firstCost,
HMNUM = HMNUM
};
#endregion HM的价格信息
#region HM的类别ID
long HMCategoryID = 0; long HMColourID = 0; long HMMaterialID = 0; long MasterPack = 1;
//long HMColorID = 0;从这张表里面获取 类别 颜色 和材料ID
var temObj = db.WebPO_HM_Colour_Material_V.FirstOrDefault(v => v.HMNUM == HMNUM);
string HMName = "";//如果为空...
if (temObj != null)
{
HMCategoryID = temObj.CategoryID.ConvertToNotNull();
HMColourID = temObj.ColourID;
HMMaterialID = temObj.MaterialID;
HMName = temObj.ProductName;
MasterPack = temObj.MasterPack;
}
#endregion #region HM的类别ID
#region HM的基础信息
var newHM = new CMS_HMNUM
{
HMNUM = HMNUM,
MasterPack = MasterPack,
ProductName = lineHMType == "Group" ? ProductName : HMName,//如果是组合产品，则不取HMNUM的名称（因为全是空）2014年3月19日
//StockKey = lineHMType == "group" ? "0" : stockKey,
StockKey = lineHMType == "Group" ? HMNUM : stockKey,
CategoryID = HMCategoryID,
MaterialID = HMMaterialID,
ColourID = HMColourID,
SubCategoryID = 0,
IsGroup = lineHMType == "Group" ? true : false,
StatusID = 0,
Loadability = Loadability,
CreateOn = DateTime.Now,
CreateBy = UpdateBy,
ModifyOn = DateTime.Now,
ModifyBy = UpdateBy
};
#endregion
#region 插入箱子基础信息
//插入尺寸
var newCTN = new CMS_ProductCTN
{
HMNUM = HMNUM,
CTNTitle = "S/1",
CTNLength = CTNLength,
CTNWidth = CTNWidth,
CTNHeight = CTNHeight,
CTNWeight = CTNWeight,
CTNCube = 0,
CreateOn = DateTime.Now,
UpdateOn = DateTime.Now,
UpdateBy = UpdateBy
};
#endregion
#region 插入尺寸基础信息
var newDim = new CMS_ProductDimension
{
HMNUM = HMNUM,
DimTitle = "S/1",
DimLength = DimLength,
DimWidth = DimWidth,
DimHeight = DimHeight,
DimCube = 0,
CreateOn = DateTime.Now,
UpdateOn = DateTime.Now,
UpdateBy = UpdateBy
};
#endregion
#region 插入SKU价格信息
var newSKUCost = new CMS_SKU_Costing
{
CreateBy = UpdateBy,
CreateOn = DateTime.Now,
EffectiveDate = DateTime.Now,
SalePrice = Retail
};
#endregion
#region 插入SKU基础信息
//插入SKUOrder
var ChannelObj = db.Channel.FirstOrDefault(c => c.ChannelName == MERCHANTID);
var ChannelID = 0;
if (ChannelObj != null)
{
ChannelID = ChannelObj.ChannelID;
}
long ColourID = 0;
string skuColor = dr["Color"].ToString();
string skuMaterial = dr["Material"].ToString();
if (!string.IsNullOrEmpty(skuColor))
{
var ColorMode = db.CMS_SKU_Colour.FirstOrDefault(c => c.ColourName == skuColor);
if (ColorMode == null)
{
var newColour = new CMS_SKU_Colour
{
ColourName = skuColor,
CreateBy = UpdateBy,
CreateOn = DateTime.Now,
ModifyBy = UpdateBy,
ModifyOn = DateTime.Now
};
db.CMS_SKU_Colour.Add(newColour);
db.SaveChanges();
ColourID = newColour.ColourID;
}
else
{
ColourID = ColorMode.ColourID;
}
}
long MaterialID = 0;
if (!string.IsNullOrEmpty(skuMaterial))
{
var Mode = db.CMS_SKU_Material.FirstOrDefault(m => m.MaterialName == skuMaterial);
if (Mode == null)
{
var newMaterial = new CMS_SKU_Material
{
MaterialName = skuMaterial,
CreateBy = UpdateBy,
CreateOn = DateTime.Now,
ModifyBy = UpdateBy,
ModifyOn = DateTime.Now
};
db.CMS_SKU_Material.Add(newMaterial);
db.SaveChanges();
// ColourID = newMaterial.MaterialID;
MaterialID = newMaterial.MaterialID;//Copy害死人啊 囧 2014年3月7日17:51:03
}
else
{
MaterialID = Mode.MaterialID;
}
}
CMS_SKU newProudct = new CMS_SKU
{
SKU = SKUORDER,
//ProductName = ProductName == "" ? SKUORDER : ProductName,//Name为空则用SKUOrder代替
ProductName = ProductName,
SKU_QTY = 0,
ChannelID = ChannelID,
UPC = UPC,
StatusID = 4, //Compelted
Visibility = 1, //---报表必须为1才有效 2013年12月14日10:08:27
ProductDesc = PrDescription,
Specifications = Bullets,
Keywords = "",
BrandID = 2,//defult
RetailPrice = SalePrice,
URL = "",
CategoryID = 0, //暂时不整理，稍后让Melissa整理2014年1月27日10:10:57,
SubCategoryID = 0, //同上
ColourID = ColourID,
MaterialID = MaterialID,
UpdateBy = UpdateBy,
UpdateOn = DateTime.Now,
CreateBy = UpdateBy,
CreateOn = DateTime.Now
};
#endregion
#region HM-SKU关联
#endregion
//if (lineHMType == "Group")
if (string.Compare(lineHMType, "Group", StringComparison.OrdinalIgnoreCase) == 0)
{
groupProductID = InsertSKUAndGroupHM(db, newHMCost, newHM, newSKUCost, newProudct);//取到最新一次更新GroupHM的ID
}
// else if (lineHMType == "individual")
else if (string.Compare(lineHMType, "individual", StringComparison.OrdinalIgnoreCase) == 0)
{
string strUnit = dr["PCS"].ToString();
int unit = 0;
int.TryParse(strUnit, out unit);
InsertBasicHM(db, groupProductID, newHMCost, newHM, newCTN, newDim, unit);
}
else
{
HMLog.Error(String.Format(">>>>>>>>>>>>>.warning!! the Number of {0} Item Type can not be determined <<<<<<<<<<<<<<<<<<", iCount));
OnOperateNotify(this, new HMEventArgs(String.Format("Line为{0} 类型无法识别，警告！！！！！！！！！！！", iCount)));
}
HMLog.Info(String.Format(">>>>>>>>>>>>>.End the Number of {0} Item <<<<<<<<<<<<<<<<<<", iCount));
OnOperateNotify(this, new HMEventArgs(String.Format(">>>>>>>>>>>>>.End the Lien of {0} Item <<<<<<<<<<<<<<<<<<", iCount)));
OnOperateNotify(this, new HMEventArgs(String.Format("Line为{0}的数据成功插入到数据库", iCount)));
OnOperateNotify(this, new HMEventArgs(""));
HMLog.Info("");
// db.SaveChanges();
}
catch (DbEntityValidationException e)
{
OnOperateNotify(this, new HMEventArgs("Error!"));
OnOperateNotify(this, new HMEventArgs("出错啦！!"));
HMLog.Error("");
HMLog.Error("");
foreach (var eve in e.EntityValidationErrors)
{
HMLog.Error("");
HMLog.Error("");
//HMLog.Error("Entity of type \"{0}\" in state \"{1}\" has the following validation errors:",eve.Entry.Entity.GetType().Name, eve.Entry.State);
foreach (var ve in eve.ValidationErrors)
{
HMLog.Error("- Property: \"{0}\", Error: \"{1}\"", ve.PropertyName, ve.ErrorMessage);
OnOperateNotify(this, new HMEventArgs(String.Format("- Property: \"{0}\", Error: \"{1}\"", ve.PropertyName, ve.ErrorMessage)));
}
HMLog.Error("");
}
HMLog.Error("");
HMLog.Error("");
OnOperateNotify(this, new HMEventArgs(""));
OnOperateNotify(this, new HMEventArgs(""));
}
catch (Exception ex)
{
OnOperateNotify(this, new HMEventArgs("Error~~~~~~~~~~~~~~~"));
OnOperateNotify(this, new HMEventArgs("出错啦~~~~~~~~~~~~~~"));
OnOperateNotify(this, new HMEventArgs(ex.Message));
HMLog.Error("");
HMLog.Error("Exception Started");
HMLog.Error(ex.Message);
HMLog.Error(ex.Source);
HMLog.Error(ex.StackTrace);
HMLog.Error("Exception End");
HMLog.Error("");
OnOperateNotify(this, new HMEventArgs(""));
OnOperateNotify(this, new HMEventArgs(""));
}
}
}
}
/// <summary>
/// 新增SKU产品
/// CrateDate:2013年11月24日17:40:08
/// </summary>
/// <param name="model"></param>
/// <param name="Modifier"></param>
/// <param name="msg"></param>
/// <returns></returns>
public long AddProduct(CMS_SKU_Model model, MCCC mcModel, CMS_HMNUM_Model HMModel, String Modifier, out string msg)
{
using (PermaisuriCMSEntities db = new PermaisuriCMSEntities())
{
msg = "";
var query = db.CMS_SKU.Where(w => w.SKU == model.SKU && w.ChannelID == model.ChannelID).FirstOrDefault();
if (query != null)
{
msg = string.Format("this item is already exist");
return(-1);
}
var mat = db.CMS_SKU_Material.FirstOrDefault(s => s.MaterialName == mcModel.Material);
if (mat == null)
{
mat = new CMS_SKU_Material
{
CreateBy = Modifier,
CreateOn = DateTime.Now,
ModifyBy = Modifier,
ModifyOn = DateTime.Now,
MaterialDesc = mcModel.Material,
MaterialName = mcModel.Material
};
db.CMS_SKU_Material.Add(mat);
}
var col = db.CMS_SKU_Colour.FirstOrDefault(s => s.ColourName == mcModel.Colour);
if (col == null)
{
col = new CMS_SKU_Colour
{
CreateBy = Modifier,
CreateOn = DateTime.Now,
ModifyBy = Modifier,
ModifyOn = DateTime.Now,
ColourDesc = mcModel.Colour,
ColourName = mcModel.Colour
};
db.CMS_SKU_Colour.Add(col);
}
var cat = db.CMS_SKU_Category.FirstOrDefault(s => s.CategoryName == mcModel.Category && s.ParentID == 0);
if (cat == null)
{
cat = new CMS_SKU_Category
{
CreateBy = Modifier,
CreateOn = DateTime.Now,
UpdateBy = Modifier,
UpdateOn = DateTime.Now,
CategoryName = mcModel.Category,
CategoryDesc = mcModel.Category,
OrderIndex = 0,
ParentID = 0
};
db.CMS_SKU_Category.Add(cat);
db.SaveChanges();
}
var subCat = db.CMS_SKU_Category.FirstOrDefault(s => s.CategoryName == mcModel.SubCategory && s.ParentID != 0);
if (subCat == null)
{
subCat = new CMS_SKU_Category
{
//CategoryID = ++i,
CreateBy = Modifier,
CreateOn = DateTime.Now,
UpdateBy = Modifier,
UpdateOn = DateTime.Now,
CategoryName = mcModel.SubCategory,
CategoryDesc = mcModel.SubCategory,
OrderIndex = 0,
//CMS_SKU_Category_Parent = cat
ParentID = cat.CategoryID
};
db.CMS_SKU_Category.Add(subCat);
}
var newCosting = new CMS_SKU_Costing
{
SalePrice = 0,
EstimateFreight = 0,
EffectiveDate = DateTime.Now,
CreateBy = Modifier,
CreateOn = DateTime.Now
};
//db.CMS_SKU_Costing.Add(newCosting);
//db.SaveChanges();
CMS_SKU newProduct = new CMS_SKU
{
//SKUCostID = 0, //default...为0 会导致后期查询的如果不做NULL排除，会得到意想不到的错误！
CMS_SKU_Costing = newCosting,
SKU = model.SKU,
ProductName = model.ProductName,
SKU_QTY = model.SKU_QTY,
ChannelID = model.ChannelID,
UPC = model.UPC,
StatusID = 1,//2014年3月5日 New
Visibility = model.Visibility,
ProductDesc = model.ProductDesc,
Specifications = model.Specifications,
Keywords = model.Keywords,
BrandID = model.BrandID,
RetailPrice = model.RetailPrice,
URL = model.URL,
//MaterialID = mat.MaterialID,
//ColourID = col.ColourID,
//CategoryID = cat.CategoryID,
//SubCategoryID = subCat.CategoryID,
CMS_SKU_Material = mat,
CMS_SKU_Colour = col,
CMS_SKU_Category = cat,
CMS_SKU_Category_Sub = subCat,
CMS_Ecom_Sync = new CMS_Ecom_Sync
{
//SKUID = query.SKUID,坑爹啊 害死人，对象空引用！2014年4月9日
StatusID = 0,
StatusDesc = "NeedSend",
UpdateBy = Modifier,
UpdateOn = DateTime.Now
},
CreateBy = Modifier,
CreateOn = DateTime.Now,
UpdateBy = Modifier,
UpdateOn = DateTime.Now,
ShipViaTypeID = model.ShipViaTypeID
};
//db.CMS_SKU.Add(newProduct);
var newHM = db.CMS_HMNUM.Find(HMModel.ProductID);
SKU_HM_Relation rModel = new SKU_HM_Relation
{
CMS_HMNUM = newHM,
CMS_SKU = newProduct,
StockKeyID = newHM.StockKeyID,
R_QTY = HMModel.R_QTY,
CreateBy = Modifier,
CreateOn = DateTime.Now,
UpdateBy = Modifier,
UpdateOn = DateTime.Now
};
db.SKU_HM_Relation.Add(rModel);
db.SaveChanges();
newCosting.HisSKUID = newProduct.SKUID;//经测试：如果让这个生效，一定要在此之前先发生一次db.SaveChanges(),即使是使用强类型嵌套插入也需要这样save 2 次....2014年3月10日
db.SaveChanges();
return(newProduct.SKUID);
}
}
/// <summary>
/// 插入组合产品的以及组合产品对于的SKU 以及他们之间的关联信息到CMS中
/// CreateDate:2013年12月31日11:47:55
/// </summary>
/// <param name="db"></param>
/// <param name="newHMCost"></param>
/// <param name="CMS_HMNUM"></param>
/// <param name="newSKUCost"></param>
/// <param name="newProudct"></param>
/// <returns>返回新插入的组合产品的ID</returns>
public long InsertSKUAndGroupHM(PermaisuriCMSEntities db, CMS_HM_Costing newHMCost, CMS_HMNUM CMS_HMNUM,
CMS_SKU_Costing newSKUCost, CMS_SKU newProudct)
{
//先判断当前的HMNUM在库表是否存在了，防止重复插入
long ProductID = 0;
var isExistHM = db.CMS_HMNUM.FirstOrDefault(h => h.HMNUM == CMS_HMNUM.HMNUM);
if (isExistHM == null)
{
db.CMS_HM_Costing.Add(newHMCost);
var newHMCostID = newHMCost.HMCostID;
CMS_HMNUM.HMCostID = newHMCostID;//设置当前HMNUM的价格指向刚刚插入的价格表
db.CMS_HMNUM.Add(CMS_HMNUM);
db.SaveChanges();
ProductID = CMS_HMNUM.ProductID;
newHMCost.HisProductID = CMS_HMNUM.ProductID;
}
else
{
OnOperateNotify(this, new HMEventArgs(String.Format("CMS_HMNUM表已经存在HMNUM={0}的数据，忽略过不插入", CMS_HMNUM.HMNUM)));
HMLog.Info(String.Format("CMS_HMNUM表已经存在HMNUM={0}的数据，忽略过不插入", CMS_HMNUM.HMNUM));
ProductID = isExistHM.ProductID;
}
//插入SKU
long SKUID = 0;
var isExistSKU = db.CMS_SKU.FirstOrDefault(w => w.SKU == newProudct.SKU && w.ChannelID == newProudct.ChannelID);
if (isExistSKU == null)
{
long newSKUCostID = 0;
db.CMS_SKU_Costing.Add(newSKUCost);
newSKUCostID = newSKUCost.SKUCostID;
newProudct.SKUCostID = newSKUCostID;
db.CMS_SKU.Add(newProudct);
db.SaveChanges();
newSKUCost.HisSKUID = newProudct.SKUID;
SKUID = newProudct.SKUID;
}
else
{
OnOperateNotify(this, new HMEventArgs(String.Format("WebsiteProduct表已经存在SKU={0},Channel={1}的数据，忽略过不插入", newProudct.SKU, newProudct.ChannelID)));
HMLog.Info(String.Format("WebsiteProduct表已经存在SKU={0},Channel={1}的数据，忽略过不插入", newProudct.SKU, newProudct.ChannelID));
SKUID = isExistSKU.SKUID;
}
//插入HMNUM Group ----SKU的关系
var newRelation = db.SKU_HM_Relation.FirstOrDefault(r => r.SKUID == SKUID && r.ProductID == ProductID);
if (newRelation == null)
{
var hm_sku = new SKU_HM_Relation
{
SKUID = SKUID,
ProductID = ProductID,
R_QTY = 1,
CreateBy = UpdateBy,
CreateOn = DateTime.Now
};
db.SKU_HM_Relation.Add(hm_sku);
}
else
{
OnOperateNotify(this, new HMEventArgs(String.Format("SKU_HM_Relation表已经存在SKUID={0},ProductID={1}的数据，忽略过不插入", SKUID, ProductID)));
HMLog.Info(String.Format("SKU_HM_Relation表已经存在SKUID={0},ProductID={1}的数据，忽略过不插入", SKUID, ProductID));
}
db.SaveChanges();//为了避免出现 ProductID =0的情况
if (ProductID == 0)
{
HMLog.Info(String.Format("InsertSKUAndGroupHM：警告！！！！！！！！！！！！！HMNUM={0}的ID查询出来是0！", CMS_HMNUM.HMNUM));
}
return(ProductID);
}
