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