/// <summary>
/// 更新HMNUMCosting的信息，用于HMNUM Management页面的的inline-edit的编辑更新
/// 需要注意的是每一次的跟新都将在库表新增一条价格信息，影响将来报表的生成。
/// CreateDate:2013年11月13日6:00:34
/// </summary>
/// <param name="model"></param>
/// <param name="costing"></param>
/// <returns></returns>
public bool EditHMNUMCosting(CMS_HMNUM_Model model, CMS_HM_Costing_Model costing, string User_Account)
{
//逻辑：先讲当前最新的价格插入到Costing表（注意是新增不是编辑），然后更新当前HMNUM的Costing信息，取最新的那条。
//EF本身自带有Transaction功能。
using (PermaisuriCMSEntities db = new PermaisuriCMSEntities())
{
int retVal = 0;
var newCosting = new CMS_HM_Costing
{
CreateBy = User_Account,
CreateOn = DateTime.Now,
EffectiveDate = DateTime.Now,
HMNUM = costing.HMNUM,
FirstCost = Convert.ToDecimal(costing.FirstCost),
LandedCost = Convert.ToDecimal(costing.LandedCost),
EstimateFreight = Convert.ToDecimal(costing.EstimateFreight),
OceanFreight = Convert.ToDecimal(costing.OceanFreight),
USAHandlingCharge = Convert.ToDecimal(costing.USAHandlingCharge),
Drayage = Convert.ToDecimal(costing.Drayage),
};
db.CMS_HM_Costing.Add(newCosting);
long newCostID = newCosting.HMCostID;
var HMNUM = db.CMS_HMNUM.FirstOrDefault(h => h.ProductID == model.ProductID);
HMNUM.HMCostID = newCostID;
retVal = db.SaveChanges();
newCosting.HisProductID = HMNUM.ProductID;
retVal = db.SaveChanges();
return(retVal > 0);
}
}
/// <summary>
/// 插入基础产品的HMNUM
/// </summary>
/// <param name="db"></param>
/// <param name="groupProductID">组合产品的ID</param>
/// <param name="newHMCost"></param>
/// <param name="newHM"></param>
/// <param name="newCTN"></param>
/// <param name="newDim"></param>
/// <param name="SellSets">...</param>
public void InsertBasicHM(PermaisuriCMSEntities db, long groupProductID, CMS_HM_Costing newHMCost, CMS_HMNUM newHM,
CMS_ProductCTN newCTN, CMS_ProductDimension newDim, int SellSets)
{
//先判断是否存在这个基础HMNUM
long basicProductID = 0;
var isExistHM = db.CMS_HMNUM.FirstOrDefault(h => h.HMNUM == newHM.HMNUM);
if (isExistHM == null)
{
db.CMS_HM_Costing.Add(newHMCost);
long newHMCostID = newHMCost.HMCostID;
newHM.HMCostID = newHMCostID;
db.CMS_HMNUM.Add(newHM);
db.SaveChanges();
basicProductID = newHM.ProductID;
newHMCost.HisProductID = newHM.ProductID;
//尺寸箱柜
newCTN.ProductID = basicProductID;
newDim.ProductID = basicProductID;
db.CMS_ProductCTN.Add(newCTN);
db.CMS_ProductDimension.Add(newDim);
}
else
{
OnOperateNotify(this, new HMEventArgs(String.Format("CMS_HMNUM(基础数据）表已经存在HMNUM={0}的数据，忽略过不插入", newHM.HMNUM)));
HMLog.Info(String.Format("CMS_HMNUM(基础数据）表已经存在HMNUM={0}的数据，忽略过不插入", newHM.HMNUM));
basicProductID = isExistHM.ProductID;
}
var newRelation = db.CMS_HMGroup_Relation.FirstOrDefault(r => r.ProductID == groupProductID && r.ChildrenProductID == basicProductID);
if (newRelation == null)
{
//组合产品--基础产品--的关系表
var newRel = new CMS_HMGroup_Relation
{
ChildrenProductID = basicProductID,
ProductID = groupProductID,
SellSets = SellSets,
CreateBy = UpdateBy,
CreateOn = DateTime.Now
};
db.CMS_HMGroup_Relation.Add(newRel);
}
else
{
OnOperateNotify(this, new HMEventArgs(String.Format("CMS_HMGroup_Relation表已经存在ProductID={0},ChildrenProductID={1}的数据，忽略过不插入", groupProductID, basicProductID)));
HMLog.Info(String.Format("CMS_HMGroup_Relation表已经存在ProductID={0},ChildrenProductID={1}的数据，忽略过不插入", groupProductID, basicProductID));
}
db.SaveChanges();
}
/// <summary>
/// 组合产品的基础信息添加，用于Create New Product Group 页面的第一阶段
/// Change:增加一个重复HMNUM插入的判断，如果已经存在则不插入，返回错误提示。2013年11月19日11:08:15
/// Change2:新增StockKey关联表，所以在插入之前需要新增插入StockKey表 2014年3月25日
/// </summary>
/// <param name="gpModel"></param>
/// <param name="User_Account"></param>
/// <returns></returns>
public long HMGroupBaseInfoAdd(CMS_HMNUM_Model gpModel, String User_Account, ref string errMsg)
{
using (PermaisuriCMSEntities db = new PermaisuriCMSEntities())
{
errMsg = string.Empty;
var query = db.CMS_HMNUM.Where(c => c.HMNUM == gpModel.HMNUM).FirstOrDefault();
if (query != null)
{
errMsg = String.Format("this item (HMNUM={0}) has been existed!", gpModel.HMNUM);
return(0);
}
var newCosting = new CMS_HM_Costing
{
CreateBy = User_Account,
CreateOn = DateTime.Now,
EffectiveDate = DateTime.Now,
HMNUM = gpModel.HMNUM,
FirstCost = 0,
LandedCost = 0,
EstimateFreight = 0,
HisProductID = 0
};
var newStockkey = new CMS_StockKey
{
StockKey = gpModel.HMNUM,//stockKey拿HMNUM 2014年2月18日15:05:25
CreateOn = User_Account,
CreateTime = DateTime.Now,
UpdateOn = User_Account,
UdateTime = DateTime.Now
};
var newModel = new CMS_HMNUM
{
HMNUM = gpModel.HMNUM,
ProductName = gpModel.ProductName,
Comments = gpModel.Comments,
HMCostID = gpModel.HMCostID,
CategoryID = gpModel.CategoryID,
ColourID = 0,
MaterialID = 0,
IsGroup = true,
StatusID = gpModel.StatusID,
ShipViaTypeID = gpModel.ShipViaTypeID,
NetWeight = gpModel.NetWeight,
CreateOn = DateTime.Now,
CreateBy = User_Account,
ModifyOn = DateTime.Now,
ModifyBy = User_Account,
MasterPack = 1,//组合产品默认设置为1 Lee 2014年2月12日16:13:12 2014年3月25日
//StockKey = gpModel.HMNUM,//stockKey拿HMNUM 2014年2月18日15:05:25
CMS_StockKey = newStockkey,
StockKey = newStockkey.StockKey,
CMS_HM_Costing = newCosting//2014年3月18日
};
db.CMS_HMNUM.Add(newModel);
db.SaveChanges();
newCosting.HisProductID = newModel.ProductID;
db.SaveChanges();
return(newModel.ProductID);
}
}
//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();
/// <summary>
/// 更新HMNUMCosting的信息，用于HMNUM Configuration页面的的Costing的编辑更新
/// 需要注意的是每一次的跟新都将在库表新增一条价格信息，影响将来报表的生成。
/// 虽然和HMNUMController页面的方法一样，但是还是分开维护，因为2个展示有可能不同，遇到需求变动会变得痛苦！
/// </summary>
/// <param name="model"></param>
/// <param name="costing">输入输出参数，输入时候代表客户端需要更新的价格传递给服务器，输出代表价格跟新后在数据库的实际存储方式。比如，客户端输入10个小数点的数字后....</param>
/// <param name="userAccount"></param>
/// <returns></returns>
public bool EditHmnumCosting(CMS_HMNUM_Model model, ref CMS_HM_Costing_Model costing, string userAccount)
{
//逻辑：先讲当前最新的价格插入到Costing表（注意是新增不是编辑），然后更新当前HMNUM的Costing信息，取最新的那条。
//EF本身自带有Transaction功能。
using (var db = new PermaisuriCMSEntities())
{
var newCosting = new CMS_HM_Costing
{
CreateBy = userAccount,
CreateOn = DateTime.Now,
EffectiveDate = DateTime.Now,
HMNUM = costing.HMNUM, // out: use unsigned parameter costing....2013年11月14日11:25:25
FirstCost = Convert.ToDecimal(costing.FirstCost),
LandedCost = Convert.ToDecimal(costing.LandedCost),
EstimateFreight = Convert.ToDecimal(costing.EstimateFreight),
OceanFreight = Convert.ToDecimal(costing.OceanFreight),
USAHandlingCharge = Convert.ToDecimal(costing.USAHandlingCharge),
Drayage = Convert.ToDecimal(costing.Drayage),
};
//db.CMS_HM_Costing.Add(newCosting);
//long newCostID = newCosting.HMCostID;
var hmnum = db.CMS_HMNUM.FirstOrDefault(h => h.ProductID == model.ProductID);
//HMNUM.HMCostID = newCostID;
if (hmnum != null)
{
hmnum.CMS_HM_Costing = newCosting;
hmnum.SKU_HM_Relation.Select(r => r.CMS_SKU.CMS_Ecom_Sync ?? (r.CMS_SKU.CMS_Ecom_Sync = new CMS_Ecom_Sync {
StatusID = 1//这里可以不用做任何设置，因为后面那个操作会全面覆盖这个...
})).ForEach(k =>
{
k.StatusID = 0;
k.StatusDesc = "NeedSend";
k.UpdateBy = userAccount;
k.UpdateOn = DateTime.Now;
});
var sb = new StringBuilder();
sb.Append(" <b> [FirstCost] </b> : old value = <span style='color:red'> " + hmnum.CMS_HM_Costing.FirstCost + " </span> , new Value = <span style='color:red'> " + newCosting.FirstCost + " </span>");
sb.Append("<br>");
sb.Append(" <b> [LandedCost] </b> : old value = <span style='color:red'> " + hmnum.CMS_HM_Costing.LandedCost + " </span> , new Value = <span style='color:red'> " + newCosting.LandedCost + " </span>");
sb.Append("<br>");
sb.Append(" <b> [EstimateFreight] </b> : old value = <span style='color:red'> " + hmnum.CMS_HM_Costing.EstimateFreight + " </span> , new Value = <span style='color:red'> " + newCosting.EstimateFreight + " </span>");
sb.Append(" <b> [OceanFreight] </b> : old value = <span style='color:red'> " + hmnum.CMS_HM_Costing.OceanFreight + " </span> , new Value = <span style='color:red'> " + newCosting.OceanFreight + " </span>");
sb.Append(" <b> [USAHandlingCharge] </b> : old value = <span style='color:red'> " + hmnum.CMS_HM_Costing.USAHandlingCharge + " </span> , new Value = <span style='color:red'> " + newCosting.USAHandlingCharge + " </span>");
sb.Append(" <b> [Drayage] </b> : old value = <span style='color:red'> " + hmnum.CMS_HM_Costing.Drayage + " </span> , new Value = <span style='color:red'> " + newCosting.Drayage + " </span>");
sb.Append("<br>");
BllExtention.DbRecorder(new LogOfUserOperatingDetails
{
ModelName = ModelName,
ActionName = MethodBase.GetCurrentMethod().Name,
ActionType = LogActionTypeEnum.Inert.GetHashCode(),
ProductID = hmnum.ProductID,
HMNUM = hmnum.HMNUM,
Descriptions = sb.ToString(),
CreateBy = userAccount,
CreateOn = DateTime.Now
});
db.SaveChanges();
newCosting.HisProductID = hmnum.ProductID;
}
var retVal = db.SaveChanges();
costing.FirstCost = newCosting.FirstCost.ConvertToNotNull().ToString("C", new CultureInfo("en-US"));
costing.LandedCost = newCosting.LandedCost.ConvertToNotNull().ToString("C", new CultureInfo("en-US"));
costing.EstimateFreight = newCosting.EstimateFreight.ConvertToNotNull().ToString("C", new CultureInfo("en-US"));
costing.OceanFreight = newCosting.OceanFreight.ConvertToNotNull().ToString("C", new CultureInfo("en-US"));
costing.USAHandlingCharge = newCosting.USAHandlingCharge.ConvertToNotNull().ToString("C", new CultureInfo("en-US"));
costing.Drayage = newCosting.Drayage.ConvertToNotNull().ToString("C", new CultureInfo("en-US"));
return(retVal > 0);
}
}
//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 以及他们之间的关联信息到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);
}
