static void BrandCollectionCheck(Product product)
{
//if (!secondaryNameBlankList.Contains(product.SecondaryName))
//{
if (product.BrandCollection != null)
{
var brandCollection =
_repositoryManager.BrandCollectionRepository.Table.FirstOrDefault(
m => m.BrandId == product.BrandId && m.Name == product.BrandCollectionName);
if (brandCollection == null)
{
brandCollection = new BrandCollection()
{
BrandId = product.BrandId,
DisplayName = product.BrandCollectionName,
Name = product.BrandCollectionName
};
_repositoryManager.BrandCollectionRepository.Create(brandCollection);
_repositoryManager.BrandCollectionRepository.Save();
}
product.BrandCollectionId = brandCollection.BrandCollectionId;
_repositoryManager.ProductRepository.Save();
//}
}
}
static int? Import(WorkbookPart wbPart, WorksheetPart wsPart, FileInfo file)
{
var startIndex = 4;
var index = startIndex;
var successedCount = 0;
while (true)
{
// 大分CD
var categoryCodeLevel1 = GetCellPathValue(wbPart, wsPart, Util.IndexAppend("B", index)).RemoveEmptyOrWrapCharacters().ToDBC();
if (categoryCodeLevel1 == "")
{
break;
}
// 棚割CD
var categoryCodeLevel2 = GetCellPathValue(wbPart, wsPart, Util.IndexAppend("C", index)).RemoveEmptyOrWrapCharacters().ToDBC();
// 中分CD
var categoryCodeLevel3 = GetCellPathValue(wbPart, wsPart, Util.IndexAppend("D", index)).RemoveEmptyOrWrapCharacters().ToDBC();
if (categoryCodeLevel3.Length > 2)
{
// take the last 2 digits
categoryCodeLevel3 = categoryCodeLevel3.Substring(4, 2);
}
Logger.Log(LogLevel.Info, string.Format("Retreiving category by category code: {0}{1}{2}", categoryCodeLevel1, categoryCodeLevel2, categoryCodeLevel3));
//取引先CD
var tradeCode = GetCellPathValue(wbPart, wsPart, Util.IndexAppend("E", index)).RemoveEmptyOrWrapCharacters().ToDBC();
//商品CD
//var productCode = GetCellPathValue(wbPart, wsPart, Util.IndexAppend("E", index)).RemoveEmptyOrWrapCharacters().ToDBC();
//催事CD
var promotionCode = GetCellPathValue(wbPart, wsPart, Util.IndexAppend("F", index)).RemoveEmptyOrWrapCharacters().ToDBC();
if (promotionCode == "")
{
promotionCode = null;
}
//クーポンCD
var cuponCode = GetCellPathValue(wbPart, wsPart, Util.IndexAppend("G", index)).RemoveEmptyOrWrapCharacters().ToDBC();
if (cuponCode == "")
{
cuponCode = null;
}
//Topic CD
var topicCode = GetCellPathValue(wbPart, wsPart, Util.IndexAppend("H", index)).RemoveEmptyOrWrapCharacters().ToDBC();
if (topicCode == "")
{
topicCode = null;
}
//中分類ランキングCD ?
var nKnow = GetCellPathValue(wbPart, wsPart, Util.IndexAppend("I", index)).RemoveEmptyOrWrapCharacters().ToDBC();
//二维码
var barCode = GetCellPathValue(wbPart, wsPart, Util.IndexAppend("J", index)).RemoveEmptyOrWrapCharacters().ToDBC();
//get category id
var categoryId = GetCategoryId(categoryCodeLevel1, categoryCodeLevel2, categoryCodeLevel3, barCode);
if (categoryId == null)
{
Logger.Log(LogLevel.Error, "Can not find matched category.");
return null;
}
//品牌 メーカー名
var brandName = GetCellPathValue(wbPart, wsPart, Util.IndexAppend("K", index)).RemoveEmptyOrWrapCharacters();
var brandId = GetBrandId(brandName);
//系列 ブランド名
var brandCollectionName = GetCellPathValue(wbPart, wsPart, Util.IndexAppend("L", index)).RemoveEmptyOrWrapCharacters();
//var brandCollectionId = GetBrandId(brandName);
//商品名1
var primaryName = GetCellPathValue(wbPart, wsPart, Util.IndexAppend("M", index)).RemoveEmptyOrWrapCharacters();
//商品名2
var secondaryName = GetCellPathValue(wbPart, wsPart, Util.IndexAppend("N", index)).RemoveEmptyOrWrapCharacters();
if (secondaryName == "")
{
secondaryName = null;
}
//特征
var flavor = GetCellPathValue(wbPart, wsPart, Util.IndexAppend("O", index)).RemoveEmptyOrWrapCharacters();
//容量
var weight = GetCellPathValue(wbPart, wsPart, Util.IndexAppend("P", index)).RemoveEmptyOrWrapCharacters().ToDBC();
//价格 希望小売価格
var priceText = GetCellPathValue(wbPart, wsPart, Util.IndexAppend("Q", index)).RemoveEmptyOrWrapCharacters().ToDBC();
// convert
double price;
if (!double.TryParse(Regex.Replace(priceText, @"[^0-9.]", string.Empty), out price))
{
price = 0;
}
//产品特征
var description = GetCellPathValue(wbPart, wsPart, Util.IndexAppend("R", index)).Trim();
//使用方法
var instruction = GetCellPathValue(wbPart, wsPart, Util.IndexAppend("S", index)).Trim();
//追加
var extraInformation = GetCellPathValue(wbPart, wsPart, Util.IndexAppend("T", index)).Trim();
// 激活日期 掲載開始日
var activationDateText = GetCellPathValue(wbPart, wsPart, Util.IndexAppend("AN", index)).RemoveEmptyOrWrapCharacters().ToDBC();
DateTime activationDate = activationDateText.From1900();
//下架时间 掲載終了日
var expiryDateText = GetCellPathValue(wbPart, wsPart, Util.IndexAppend("AO", index)).RemoveEmptyOrWrapCharacters().ToDBC();
DateTime expiryDate = expiryDateText.From1900();
//商品発売日
var 商品発売日 = GetCellPathValue(wbPart, wsPart, Util.IndexAppend("AP", index)).RemoveEmptyOrWrapCharacters().ToDBC();
DateTime 商品発売日Date = 商品発売日.From1900();
//製造中止日
var 製造中止日 = GetCellPathValue(wbPart, wsPart, Util.IndexAppend("AQ", index)).RemoveEmptyOrWrapCharacters().ToDBC();
DateTime 製造中止日Date = 製造中止日.From1900();
//入数
var amount = GetCellPathValue(wbPart, wsPart, Util.IndexAppend("AR", index)).RemoveEmptyOrWrapCharacters().ToDBC();
//W
var wData = GetCellPathValue(wbPart, wsPart, Util.IndexAppend("AS", index)).RemoveEmptyOrWrapCharacters().ToDBC();
double wValue;
Double.TryParse(wData, out wValue);
//D
var dData = GetCellPathValue(wbPart, wsPart, Util.IndexAppend("AT", index)).RemoveEmptyOrWrapCharacters().ToDBC();
double dValue;
Double.TryParse(dData, out dValue);
//H
var hData = GetCellPathValue(wbPart, wsPart, Util.IndexAppend("AU", index)).RemoveEmptyOrWrapCharacters().ToDBC();
double hValue;
Double.TryParse(hData, out hValue);
//情報更新日
var updateDataText = GetCellPathValue(wbPart, wsPart, Util.IndexAppend("AW", index)).RemoveEmptyOrWrapCharacters().ToDBC();
DateTime updateData = updateDataText.From1900();
//春
var springData = GetCellPathValue(wbPart, wsPart, Util.IndexAppend("CB", index)).Trim();
bool? spring = GetSeason(springData);
//夏
var summerData = GetCellPathValue(wbPart, wsPart, Util.IndexAppend("CC", index)).Trim();
bool? summer = GetSeason(summerData);
//秋
var autumnData = GetCellPathValue(wbPart, wsPart, Util.IndexAppend("CD", index)).Trim();
bool? autumn = GetSeason(autumnData);
//冬
var winterData = GetCellPathValue(wbPart, wsPart, Util.IndexAppend("CE", index)).Trim();
bool? winter = GetSeason(winterData);
var newProduct = new Product
{
//Todo
CategoryId = (int)categoryId,
BrandId = brandId,
BrandCollectionName = brandCollectionName,
PrimaryName = primaryName,
SecondaryName = secondaryName,
BarCode = barCode,
Flavor = flavor,
Weight = weight,
Width = wValue,
Depth = hValue,
Height = dValue,
//ProductCode = productCode,
PromotionCode = promotionCode,
CuponCode = cuponCode,
TopicCode = topicCode,
Description = description,
Instruction = instruction,
ExtraInformation = extraInformation,
// CountryId = 1, // take out contry id from product table
CreatedDate = DateTime.Now,
UpdatedDate = updateData,
ActivationDate = activationDate,
//ExpiryDate = expiryDate
Spring = spring,
Summer = summer,
Autumn = autumn,
Winter = winter,
Price = price,
IsPublished = true
};
index++;
// check if any product with same barcode exists in db
if (_repositoryManager.ProductRepository.Table.Any(m => m.BarCode == barCode))
{
Logger.Log(LogLevel.Error, string.Format("Barcode: {0} already exists", barCode));
continue;
}
try
{
_repositoryManager.ProductRepository.Create(newProduct);
_repositoryManager.ProductRepository.Save();
}
catch (Exception ex)
{
Console.WriteLine(ex.GetBaseException().Message);
throw;
}
//BrandCollectionCheck
BrandCollectionCheck(newProduct);
successedCount++;
}
return successedCount;
}
static int? Import(WorkbookPart wbPart, WorksheetPart wsPart, FileInfo file)
{
// load brand name from cell
var brandName = GetCellPathValue(wbPart, wsPart, "I6").RemoveEmptyOrWrapCharacters();
if (string.IsNullOrEmpty(brandName))
{
Logger.Log(LogLevel.Error, "Can not find brand name or invalid name");
return null;
}
Logger.Log(LogLevel.Info, "Retreiving brand by brand name: " + brandName);
// get the brand id
var brandId = GetBrandId(brandName);
// 大分类CD
var categoryCodeLevel1 = GetCellPathValue(wbPart, wsPart, "P9").RemoveEmptyOrWrapCharacters().ToDBC();
var categoryCodeLevel1Name = GetCellPathValue(wbPart, wsPart, "T9").RemoveEmptyOrWrapCharacters().ToDBC();
// 陈列标准CD
var categoryCodeLevel2 = GetCellPathValue(wbPart, wsPart, "Z9").RemoveEmptyOrWrapCharacters().ToDBC();
var categoryCodeLevel2Name = GetCellPathValue(wbPart, wsPart, "AD9").RemoveEmptyOrWrapCharacters().ToDBC();
// 中品类CD
var categoryCodeLevel3 = GetCellPathValue(wbPart, wsPart, "AJ9").RemoveEmptyOrWrapCharacters().ToDBC();
var categoryCodeLevel3Name = GetCellPathValue(wbPart, wsPart, "AN9").RemoveEmptyOrWrapCharacters().ToDBC();
if (categoryCodeLevel3.Length > 2)
{
// take the last 2 digits
categoryCodeLevel3 = categoryCodeLevel3.Substring(4, 2);
}
Logger.Log(LogLevel.Info, string.Format("Retreiving category by category code: {0}{1}{2}", categoryCodeLevel1, categoryCodeLevel2, categoryCodeLevel3));
// get category id
var categoryId = GetCategoryId(categoryCodeLevel1, categoryCodeLevel2, categoryCodeLevel3, categoryCodeLevel1Name, categoryCodeLevel2Name, categoryCodeLevel3Name);
if (categoryId == null)
{
Logger.Log(LogLevel.Error, "Can not find matched category.");
return null;
}
//价格
var priceText = GetCellPathValue(wbPart, wsPart, "AO11").RemoveEmptyOrWrapCharacters().ToDBC();
// convert
double price;
if (!double.TryParse(Regex.Replace(priceText, @"[^0-9.]", string.Empty), out price))
{
price = 0;
}
// 激活日期
var activationDateText = GetCellPathValue(wbPart, wsPart, "H44").RemoveEmptyOrWrapCharacters().ToDBC();
DateTime activationDate = activationDateText.From1900();
//下架时间
var expiryDateText = GetCellPathValue(wbPart, wsPart, "H45").RemoveEmptyOrWrapCharacters().ToDBC();
DateTime expiryDate = expiryDateText.From1900();
//商品名1
var primaryName = GetCellPathValue(wbPart, wsPart, "T6").RemoveEmptyOrWrapCharacters();
//商品名2
string secondaryName = GetCellPathValue(wbPart, wsPart, "AC6").RemoveEmptyOrWrapCharacters();
if (secondaryName == "")
{
secondaryName = null;
}
//条形码
var barCode = GetCellPathValue(wbPart, wsPart, "B6").RemoveEmptyOrWrapCharacters().ToDBC();
//特征
var flavor = GetCellPathValue(wbPart, wsPart, "AL6").RemoveEmptyOrWrapCharacters();
//容量
var weight = GetCellPathValue(wbPart, wsPart, "AP6").RemoveEmptyOrWrapCharacters().ToDBC();
//商品CD
//var productCode = GetCellPathValue(wbPart, wsPart, "AJ11").RemoveEmptyOrWrapCharacters().ToDBC();
string productCode = null;
//系列 ブランド名
string brandCollectionName = GetCellPathValue(wbPart, wsPart, "N6").RemoveEmptyOrWrapCharacters();
if (brandCollectionName == "")
brandCollectionName = null;
//促销CD
var promotionCode = GetCellPathValue(wbPart, wsPart, "P14").RemoveEmptyOrWrapCharacters().ToDBC();
//优惠CD
var cuponCode = GetCellPathValue(wbPart, wsPart, "T14").RemoveEmptyOrWrapCharacters().ToDBC();
//Topic CD
var topicCode = GetCellPathValue(wbPart, wsPart, "AE14").RemoveEmptyOrWrapCharacters().ToDBC();
//产品特征
var description = GetCellPathValue(wbPart, wsPart, "P17").Trim();
//使用方法
var instruction = GetCellPathValue(wbPart, wsPart, "B26").Trim();
//追加
var extraInformation = GetCellPathValue(wbPart, wsPart, "B40").Trim();
//春
var springData = GetCellPathValue(wbPart, wsPart, "V62").Trim();
bool? spring = GetSeason(springData);
//夏
var summerData = GetCellPathValue(wbPart, wsPart, "W62").Trim();
bool? summer = GetSeason(summerData);
//秋
var autumnData = GetCellPathValue(wbPart, wsPart, "X62").Trim();
bool? autumn = GetSeason(autumnData);
//冬
var winterData = GetCellPathValue(wbPart, wsPart, "Y62").Trim();
bool? winter = GetSeason(winterData);
//W
var wData = GetCellPathValue(wbPart, wsPart, "G58").RemoveEmptyOrWrapCharacters().ToDBC();
double wValue;
Double.TryParse(wData, out wValue);
//H
var hData = GetCellPathValue(wbPart, wsPart, "M58").RemoveEmptyOrWrapCharacters().ToDBC();
double hValue;
Double.TryParse(hData, out hValue);
//D
var dData = GetCellPathValue(wbPart, wsPart, "J58").RemoveEmptyOrWrapCharacters().ToDBC();
double dValue;
Double.TryParse(dData, out dValue);
// new product instance
var newProduct = new Product
{
CategoryId = categoryId.Value,
BrandId = brandId,
PrimaryName = primaryName,
SecondaryName = secondaryName,
BarCode = barCode,
BrandCollectionName = brandCollectionName,
Flavor = flavor,
Weight = weight,
Width = wValue,
Depth = hValue,
Height = dValue,
ProductCode = productCode,
PromotionCode = promotionCode,
CuponCode = cuponCode,
TopicCode = topicCode,
Description = description,
Instruction = instruction,
ExtraInformation = extraInformation,
// CountryId = 1, // take out contry id from product table
CreatedDate = DateTime.Now,
UpdatedDate = DateTime.Now,
ActivationDate = activationDate,
//ExpiryDate = expiryDate
Spring = spring,
Summer = summer,
Autumn = autumn,
Winter = winter,
Price = price,
IsPublished = true
};
// check if any product with same barcode exists in db
if (_repositoryManager.ProductRepository.Table.Any(m => m.BarCode == barCode))
{
Logger.Log(LogLevel.Error, string.Format("Barcode: {0} already exists", barCode));
return null;
}
_repositoryManager.ProductRepository.Create(newProduct);
_repositoryManager.ProductRepository.Save();
//BrandCollectionCheck
BrandCollectionCheck(newProduct);
//ImportImage
ImportImage(wsPart, newProduct.ProductId, newProduct.BarCode, categoryCodeLevel1, categoryCodeLevel2, categoryCodeLevel3);
return newProduct.ProductId;
}
