/// <summary>
/// 拷贝当前文件夹（SKUOrder)及其子文件夹内所有的文件
/// </summary>
/// <param name="path"></param>
/// <param name="HMNUM"></param>
private void CopyImagesFile(string path, CMS_HMNUM HMNUM)
{
CopyImagesHandler(Directory.GetFiles(path, "*"), HMNUM);
foreach (string dir in Directory.GetDirectories(path))
{
//递归
CopyImagesFile(dir, HMNUM);
}
}
/// <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>
/// 获取SKUOrder对应HMNUM
/// </summary>
/// <param name="SKUOrder"></param>
/// <param name="ChannelID"></param>
/// <param name="maxSerNum"></param>
/// <returns></returns>
private CMS_HMNUM GetHMNUMBySKUOrder(string SKUOrder, int ChannelID, ref int maxSerNum)
{
using (PermaisuriCMSEntities db = new PermaisuriCMSEntities())
{
CMS_HMNUM HMObj = null;
var SKU = db.CMS_SKU.FirstOrDefault(w => w.SKU == SKUOrder && w.ChannelID == ChannelID);
if (SKU == null)//处理找不到SKU情况（非正常情况）
{
//如果关系不存在，则拿SKUOrder+Overstock到eCom获取StockKey(SKUBest),再拿SKUBest到CMS_HMNUM匹配HMNUM
ImageLog.Fatal(String.Format("SKUORDER为 {0} 的记录,没有关联任何HMNUM,开始去eCom库表查询关系", SKUOrder));
var StockKeyName = db.Database.SqlQuery <string>("select SKUBest from eCom.dbo.SKU where SKUOrder = '" + SKUOrder + "' and MerchantID = 'OverStock' ").FirstOrDefault();
if (StockKeyName == null)
{
ImageLog.Fatal(String.Format("SKUORDER为 {0} 的记录,在eCom库表里面也没有找到任何的记录。", SKUOrder));
ImageLog.Fatal("============End==================");
ImageLog.Fatal("");
return(null);
}
//拿StockKeyName去CMS_HMNUM获取
var aHMNUM = db.CMS_HMNUM.FirstOrDefault(h => h.StockKey == StockKeyName);
if (aHMNUM == null)
{
ImageLog.Fatal(String.Format("SKUORDER为 {0} 的记录,在eCom库表里面查询到的StockKey为{1},根据此参数在CMS_HMNUM表里面查询不到任何信息！。", SKUOrder, StockKeyName));
ImageLog.Fatal("============End==================");
ImageLog.Fatal("");
return(null);
}
ImageLog.Fatal("============End==================");
ImageLog.Fatal("");
HMObj = aHMNUM;
}
else //处理SKU 不为空的情况（正常情况）
{
var rObj = SKU.SKU_HM_Relation;
if (rObj == null)
{
//如果关系不存在，则拿SKUOrder+Overstock到eCom获取StockKey(SKUBest),再拿SKUBest到CMS_HMNUM匹配HMNUM
ImageLog.Fatal(String.Format("SKUORDER为 {0} 的记录,没有关联任何HMNUM,开始去eCom库表查询关系", SKUOrder));
var StockKeyName = db.Database.SqlQuery <string>("select SKUBest from eCom.dbo.SKU where SKUOrder = '{0}' and MerchantID = 'OverStock' ", SKUOrder).FirstOrDefault();
if (StockKeyName == null)
{
ImageLog.Fatal(String.Format("SKUORDER为 {0} 的记录,在eCom库表里面也没有找到任何的记录。", SKUOrder));
ImageLog.Fatal("============End==================");
ImageLog.Fatal("");
return(null);
}
//拿StockKeyName去CMS_HMNUM获取
var aHMNUM = db.CMS_HMNUM.FirstOrDefault(h => h.StockKey == StockKeyName);
if (aHMNUM == null)
{
ImageLog.Fatal(String.Format("SKUORDER为 {0} 的记录,在eCom库表里面查询到的StockKey为{1},根据此参数在CMS_HMNUM表里面查询不到任何信息！。", SKUOrder, StockKeyName));
ImageLog.Fatal("============End==================");
ImageLog.Fatal("");
return(null);
}
ImageLog.Fatal("============End==================");
ImageLog.Fatal("");
HMObj = aHMNUM;
}
else //处理关不为空的情况（正常情况）
{
HMObj = rObj.CMS_HMNUM;
}
}
maxSerNum = HMObj.CMS_StockKey.MediaLibrary.Count == 0 ? 0 : HMObj.CMS_StockKey.MediaLibrary.Max(m => m.SerialNum);
return(HMObj);
}
//return null;
}
/// <summary>
/// 将当前文件夹内所有的问价都全部拷贝到新的目录下
/// </summary>
/// <param name="file">当前文件夹内的文件</param>
private void CopyImagesHandler(string[] files, CMS_HMNUM HMNUM)
{
//如果当前目录下不存在以HMNUM命名的文件夹，则应该创建
string curHMPath = DestinationDir + "/" + HMNUM.HMNUM;
if (!Directory.Exists(curHMPath))
{
Directory.CreateDirectory(curHMPath);
}
foreach (string file in files)
{
var fi = new FileInfo(file);
if (imgFormat.Contains(fi.Extension.ToLower()))
{
try//在循环体内捕获异常，防止出现一张图片有异常导致整个程序停止运行，同时异常讲打印出当前图像的位置和异常信息 2014年5月27日9:52:43
{
serNum++;
fi.Attributes = FileAttributes.Normal;//设置为可以读写
string newPath = curHMPath + "/" + HMNUM.HMNUM + "_" + serNum + fi.Extension;
string thumbnailPath = curHMPath + "/" + HMNUM.HMNUM + "_" + serNum + "_th" + fi.Extension;
fi.CopyTo(newPath, true);
//生成略缩图
// SmallImageGenerated(newPath, thumbnailPath, 200);
SamllImageGengeratedFixedHW(newPath, thumbnailPath, 200, 200);
int retResult = 0;
//插入数据库
using (PermaisuriCMSEntities db = new PermaisuriCMSEntities())
{
Image imgInfo = Image.FromFile(newPath);
MediaLibrary newMedia = new MediaLibrary
{
ProductID = HMNUM.ProductID,
HMNUM = HMNUM.HMNUM,
StockKeyID = HMNUM.StockKeyID,
SerialNum = serNum,
ImgName = HMNUM.HMNUM + "_" + serNum,
MediaType = 1,
PrimaryImage = serNum == 1 ? true : false,//处理逻辑：序列号为1说明是第一张图，则设置为默认的图像。2014年5月26日
fileFormat = fi.Extension,
fileSize = fi.Length.ToString(),
fileWidth = imgInfo.Width,
fileHeight = imgInfo.Height,
CreateOn = DateTime.Now,
CreateBy = "InitByProgram",
CloudStatusID = 1 // Not yet upload
};
db.MediaLibrary.Add(newMedia);
retResult = db.SaveChanges();
imgInfo.Dispose();
}
if (retResult > 0)
{
OperateNotify(this, new InitImagesEventArgs("成功插入数据库"));
}
else
{
OperateNotify(this, new InitImagesEventArgs("插入数据库失败！！！请查看相关日志！！！"));
ImageLog.Fatal(String.Format("尝试将路径为{0}的图像信息插入数据库失败！！", newPath));
}
OperateNotify(this, new InitImagesEventArgs(">>>>>>>>>>>>>>"));
OperateNotify(this, new InitImagesEventArgs(string.Format("将文件从:{0} 拷贝到", file)));
OperateNotify(this, new InitImagesEventArgs(string.Format("将文件从:{0} 拷贝到", newPath)));
OperateNotify(this, new InitImagesEventArgs("<<<<<<<<<<<<<<<<"));
}
catch (Exception ex)
{
OperateNotify(this, new InitImagesEventArgs("==在搬迁图像的时候捕获到异常，请查看日志！="));
ImageLog.Fatal("");
ImageLog.Fatal(String.Format("尝试将路径为{0}的图像搬迁到指定目录出错！", file));
ImageLog.Fatal(String.Format("报错信息{0}！", ex.Message));
ImageLog.Fatal(String.Format("堆栈信息{0}！", ex.StackTrace));
ImageLog.Fatal("");
}
}
}
}
/// <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();
}
//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);
}