/// <summary>
/// 把实体类转换成键/值对集合
/// </summary>
/// <param name="entity"></param>
/// <param name="dict"></param>
private static void GetParameters(ProductPriceEntity entity, Dictionary <string, object> dict)
{
dict.Add("ID", entity.ID);
dict.Add("ProductID", entity.ProductID);
dict.Add("PropertyValue", entity.PropertyValue);
dict.Add("TableName", entity.TableName);
dict.Add("GroupID", entity.GroupID);
dict.Add("Price", entity.Price);
}
/// <summary>
/// 通过数据读取器生成实体类
/// </summary>
/// <param name="rdr"></param>
/// <returns></returns>
private static ProductPriceEntity GetEntityFromrdr(NullableDataReader rdr)
{
ProductPriceEntity info = new ProductPriceEntity();
info.ID = rdr.GetInt32("ID");
info.ProductID = rdr.GetInt32("ProductID");
info.PropertyValue = rdr.GetString("PropertyValue");
info.TableName = rdr.GetString("TableName");
info.GroupID = rdr.GetInt32("GroupID");
info.Price = rdr.GetDecimal("Price");
return(info);
}
/// <summary>
/// 获取实体（异步方式）
/// </summary>
/// <param name="strWhere">参数化查询条件(例如: and Name = @Name )</param>
/// <param name="dict">参数的名/值集合</param>
/// <returns></returns>
public virtual async Task <ProductPriceEntity> GetEntityAsync(string strWhere, Dictionary <string, object> dict = null)
{
ProductPriceEntity obj = null;
string strSQL = "select top 1 * from ProductPrice where 1=1 " + strWhere;
using (NullableDataReader reader = await Task.Run(() => _DB.GetDataReader(strSQL, dict)))
{
if (reader.Read())
{
obj = GetEntityFromrdr(reader);
}
}
return(obj);
}
/// <summary>
/// 更新一条记录（异步方式）
/// </summary>
/// <param name="entity">实体模型</param>
/// <returns></returns>
public virtual async Task <bool> UpdateAsync(ProductPriceEntity entity)
{
Dictionary <string, object> dict = new Dictionary <string, object>();
GetParameters(entity, dict);
string strSQL = "Update ProductPrice SET " +
"ProductID = @ProductID," +
"PropertyValue = @PropertyValue," +
"TableName = @TableName," +
"GroupID = @GroupID," +
"Price = @Price" +
" WHERE " +
"ID = @ID";
return(await Task.Run(() => _DB.ExeSQLResult(strSQL, dict)));
}
/// <summary>
/// 增加一条记录，返回新的ID号。需要有一个单一主键，并且开启有标识符属性（异步方式）
/// </summary>
/// <param name="entity">实体模型</param>
/// <returns></returns>
public virtual async Task <int> InsertAsync(ProductPriceEntity entity)
{
Dictionary <string, object> dict = new Dictionary <string, object>();
GetParameters(entity, dict);
string strSQL = "insert into ProductPrice (" +
"ProductID," +
"PropertyValue," +
"TableName," +
"GroupID," +
"Price) " +
"values(" +
"@ProductID," +
"@PropertyValue," +
"@TableName," +
"@GroupID," +
"@Price)";
return(await Task.Run(() => _DB.ReturnID(strSQL, dict)));
}
/// <summary>
/// 增加一条记录
/// </summary>
/// <param name="entity">实体模型</param>
/// <returns></returns>
public virtual bool Add(ProductPriceEntity entity)
{
Dictionary <string, object> dict = new Dictionary <string, object>();
GetParameters(entity, dict);
string strSQL = "insert into ProductPrice (" +
"ProductID," +
"PropertyValue," +
"TableName," +
"GroupID," +
"Price) " +
"values(" +
"@ProductID," +
"@PropertyValue," +
"@TableName," +
"@GroupID," +
"@Price)";
return(_DB.ExeSQLResult(strSQL, dict));
}
/// <summary>
/// 增加或更新一条记录（异步方式）
/// </summary>
/// <param name="entity">实体模型</param>
/// <param name="IsSave">是否增加</param>
/// <returns></returns>
public virtual async Task <bool> AddOrUpdateAsync(ProductPriceEntity entity, bool IsSave)
{
return(IsSave ? await AddAsync(entity) : await UpdateAsync(entity));
}
/// <summary>
/// 增加或更新一条记录
/// </summary>
/// <param name="entity">实体模型</param>
/// <param name="IsSave">是否增加</param>
/// <returns></returns>
public virtual bool AddOrUpdate(ProductPriceEntity entity, bool IsSave)
{
return(IsSave ? Add(entity) : Update(entity));
}
public async Task <IActionResult> Import([FromForm] IFormFile file)
{
if (file == null || file.Length == 0 || (file.ContentType != "application/zip" && file.ContentType != "application/x-zip-compressed"))
{
logger.LogWarning("Cannot import the specified file. Either it is null, length 0 or not a zip file.");
return(BadRequest(new ApiV1ErrorResponseModel("No file is supplied, the supplied file is empty or the supplied file is not a zip file.")));
}
var productsDe = new List <(ProductEntity productEntity, ImageEntity image, List <ProductTranslationEntity> translations, List <ProductPriceEntity> prices, string category)>();
var prodcutsEn = new List <(ProductEntity productEntity, ImageEntity image, List <ProductTranslationEntity> translations, List <ProductPriceEntity> prices, string category)>();
var jsonSerializer = new JsonSerializer();
using (var stream = file.OpenReadStream())
using (var zip = new ZipArchive(stream))
{
// Process german entries and fake english entries
foreach (var entry in zip.Entries.Where(x => x.FullName.StartsWith("crawled-data/de/")))
{
if (!string.IsNullOrEmpty(entry.Name))
{
if (entry.FullName.StartsWith("crawled-data/de/") || entry.FullName.StartsWith("crawled-data/en/"))
{
var isGermanEntry = entry.FullName.StartsWith("crawled-data/de/");
using (var entryStream = entry.Open())
using (var textReader = new StreamReader(entryStream))
{
var jsonString = textReader.ReadToEnd();
var json = JObject.Parse(jsonString);
if (!json.ContainsKey("artist") || !json.ContainsKey("label") || !json.ContainsKey("genre") || !json.ContainsKey("release-date"))
{
continue;
}
// General information
var artist = json["artist"].Value <string>();
var label = json["label"].Value <string>();
var genre = json["genre"].Value <string>();
var releaseDateRaw = json["release-date"].Value <string>();
var releaseDate = new DateTime(int.Parse(releaseDateRaw), 1, 1);
var productEntity = new ProductEntity
{
Artist = artist,
Label = label,
ReleaseDate = releaseDate
};
// Euro price
var priceEuros = json["price"].Value <decimal>();
var priceEurosEntity = new ProductPriceEntity
{
CurrencyId = isGermanEntry ? "EUR" : "USD",
Price = priceEuros
};
var priceUsdEntity = new ProductPriceEntity
{
CurrencyId = "USD",
Price = priceEuros * 0.856850m
};
// German translation
var descriptionShort = json["short_description"].Value <string>();
var description = json.ContainsKey("article_description") ? json["article_description"].Value <string>() : string.Empty;
var title = json["title"].Value <string>();
var translationDeEntity = new ProductTranslationEntity
{
DescriptionShort = descriptionShort,
Description = description,
Title = title,
LanguageId = isGermanEntry ? "de_DE" : "en_US"
};
// Fake english translation
var translationEnEntity = new ProductTranslationEntity
{
DescriptionShort = descriptionShort + " English",
Description = description + " English",
Title = title + " English",
LanguageId = "en_US"
};
// Try to get image
var itemId = json["item_id"].ToString();
var imageDescription = json["picture_alt"].Value <string>();
var imageEntry = zip.Entries.Where(x => x.Name.EndsWith($"{itemId}.jpg")).FirstOrDefault();
// Continue of no image has been found
if (imageEntry == null)
{
continue;
}
byte[] imageBytes = null;
// Copy image to byte array
using (var imageStream = imageEntry.Open())
using (var memoryStream = new MemoryStream())
{
await imageStream.CopyToAsync(memoryStream);
imageBytes = memoryStream.ToArray();
}
var imageEntity = new ImageEntity
{
Description = imageDescription,
ImageType = "image/jpeg",
Base64String = Convert.ToBase64String(imageBytes)
};
var translationEntities = new List <ProductTranslationEntity>
{
translationDeEntity,
translationEnEntity
};
var priceEntities = new List <ProductPriceEntity>
{
priceEurosEntity,
priceUsdEntity
};
if (isGermanEntry)
{
productsDe.Add((productEntity, imageEntity, translationEntities, priceEntities, genre));
}
else
{
prodcutsEn.Add((productEntity, imageEntity, translationEntities, priceEntities, genre));
}
}
}
}
}
}
if (productsDe.Count > 0)
{
// Remove all products, categories and images
await productService.DeleteAllAsync();
await categoryService.DeleteAllAsync();
await imageService.DeleteAllAsync();
var addedCategories = new List <CategoryEntity>();
// Add all new products
foreach (var(productEntity, image, translations, prices, genre) in productsDe)
{
// Check if category was added, else add id
var categoryEntity = addedCategories.FirstOrDefault(x => x.Title == genre);
if (categoryEntity == null)
{
categoryEntity = new CategoryEntity
{
Title = genre
};
await categoryService.AddAsync(categoryEntity);
addedCategories.Add(categoryEntity);
}
// Set category id
productEntity.CategoryId = categoryEntity.Id;
await imageService.AddAsync(image);
productEntity.ImageId = image.Id;
await productService.AddAsync(productEntity, prices, translations);
}
}
return(Ok());
}
