override protected void Do()
{
base.Do();
DbApi db = Fhr.ProductOffice.Models.DbApi.Create();
if (Properties.Settings.Default.DeletePricesOlderThanDays > 0)
{
DateTime old_time = DateTime.Now.AddDays(-Properties.Settings.Default.DeletePricesOlderThanDays);
IQueryable <Cliver.Fhr.ProductOffice.Models.Price> prices = db.Prices.Where(p => p.Time < old_time);
db.Prices.RemoveRange(prices);
Log.Main.Write("Deleting Prices older than " + old_time.ToShortDateString() + ": " + prices.Count());
db.SaveChanges();
}
DbApi.RenewContext(ref db);
if (Properties.Settings.Default.DeleteProductsOlderThanDays > 0)
{
DateTime old_time = DateTime.Now.AddDays(-Properties.Settings.Default.DeleteProductsOlderThanDays);
IQueryable <Fhr.ProductOffice.Models.Product> products = db.Products.Where(p => p.UpdateTime == null || p.UpdateTime < old_time);
foreach (Fhr.ProductOffice.Models.Product product in products)
{
Fhr.ProductOffice.DataApi.Product.Delete(db, product.Id);
}
Log.Main.Write("Deleting Products older than " + old_time.ToShortDateString() + ": " + products.Count());
}
}
bool process_file(string file, Import import, Action <int> progress = null)
{
DateTime start_time = DateTime.Now;
Excel.IExcelDataReader edr;
FileStream stream = System.IO.File.Open(file, FileMode.Open, FileAccess.Read);
if (file.EndsWith(".xlsx", StringComparison.InvariantCultureIgnoreCase))
{
edr = Excel.ExcelReaderFactory.CreateOpenXmlReader(stream);
}
else
{
edr = Excel.ExcelReaderFactory.CreateBinaryReader(stream);
}
//edr = Excel.ExcelReaderFactory.CreateOpenXmlReader(stream);
System.Data.DataSet ds = edr.AsDataSet();
edr.Close();
Fhr.ProductOffice.Models.ImportMap import_map = db.ImportMaps.Where(r => r.Id == import.MapId).First();
//if (import_map.C_CompanyProductIdI == null)
// throw new Exception("C_CompanyProductIdI in map #" + import_map.Id + " is not specified.");
//if (import_map.C_NameI == null)
// throw new Exception("C_NameI in map #" + import_map.Id + " is not specified.");
db.Configuration.AutoDetectChangesEnabled = false;
db.Configuration.ValidateOnSaveEnabled = false;
int sheet_count = ds.Tables.Count;
int invalid_product_count = 0;
int new_product_count = 0;
int updated_product_count = 0;
int processed_product_count = 0;
int invalid_price_count = 0;
int new_price_count = 0;
int updated_price_count = 0;
int processed_price_count = 0;
int row_number = -1;
List <string> error_messages = new List <string>();
foreach (System.Data.DataTable dt in ds.Tables)
{
if (import_map.SkipFirstRow)
{
dt.Rows.RemoveAt(0);
}
foreach (System.Data.DataRow row in dt.Rows)
{
row_number++;
Fhr.ProductOffice.Models.Product product = new Fhr.ProductOffice.Models.Product();
product.CompanyId = import_map.CompanyId;
product.ExternalId = row[import_map.C_CompanyProductIdI].ToString().Trim();
if (string.IsNullOrWhiteSpace(product.ExternalId))
{
bool line_is_empty = true;
for (int i = 0; i < 5; i++)
{
if (!string.IsNullOrWhiteSpace(row[i].ToString()))
{
line_is_empty = false;
break;
}
}
if (!line_is_empty)
{
string error = "Cell (" + import_map.C_PriceI + ", " + row_number + ") ExternalId is empty.";
if (import.CheckNotImport)
{
invalid_product_count++;
error_messages.Add(error);
}
else
{
throw new Exception(error);
}
}
continue;
}
product.ModifyTime = null;
product.UpdateTime = import.UpdateTime;
product.Name = row[import_map.C_NameI].ToString().Trim();
if (product.Name == null)
{
string error = "Cell (" + import_map.C_PriceI + ", " + row_number + ") Name is empty.";
if (import.CheckNotImport)
{
invalid_product_count++;
error_messages.Add(error);
}
else
{
throw new Exception(error);
}
}
product.Source = "file:" + import.File.FileName;
if (import_map.C_SkuI != null)
{
product.Sku = row[(int)import_map.C_SkuI].ToString().Trim();
}
if (import_map.C_CategoryI != null)
{
product.Category = row[(int)import_map.C_CategoryI].ToString().Trim();
}
if (import_map.C_DescriptionI != null)
{
product.Description = row[(int)import_map.C_DescriptionI].ToString().Trim();
}
if (!import.CheckNotImport)
{
Fhr.ProductOffice.Models.Product p = db.Products.Where(r => r.CompanyId == product.CompanyId && r.ExternalId == product.ExternalId).FirstOrDefault();
if (p == null)
{
product.CreateTime = import.UpdateTime;
product.LinkId = null;
db.Products.Add(product);
new_product_count += db.SaveChanges();
}
else
{
product.Id = p.Id;
if (product.Description != null)
{
p.Description = product.Description;
}
if (product.ImageUrls != null)
{
p.ImageUrls = product.ImageUrls;
}
if (product.ModifyTime != null)
{
p.ModifyTime = product.ModifyTime;
}
p.UpdateTime = product.UpdateTime;
if (product.Name != null)
{
p.Name = product.Name;
}
if (product.Sku != null)
{
p.Sku = product.Sku;
}
if (product.Source != null)
{
p.Source = product.Source;
}
if (product.Url != null)
{
p.Url = product.Url;
}
//db.Entry(product).State = EntityState.Modified;
updated_product_count += db.SaveChanges();
}
}
processed_product_count++;
if (progress != null)
{
progress(processed_product_count);
}
if (import_map.C_PriceI != null)
{
Price price = new Price();
price.CurrencyId = import_map.CurrencyId;
price.ProductId = product.Id;
price.Time = (DateTime)product.UpdateTime;
decimal v;
if (!decimal.TryParse(row[(int)import_map.C_PriceI].ToString().Trim(), out v))
{
string error = "Cell (" + import_map.C_PriceI + ", " + row_number + ") Price cannot be parsed:" + row[(int)import_map.C_PriceI].ToString().Trim();
if (import.CheckNotImport)
{
invalid_price_count++;
error_messages.Add(error);
}
else
{
throw new Exception(error);
}
}
price.Value = v;
if (!import.CheckNotImport)
{
Price p = db.Prices.Where(r => r.ProductId == price.ProductId && r.Time == price.Time).FirstOrDefault();
if (p == null)
{
db.Prices.Add(price);
new_price_count += db.SaveChanges();
}
else
{
price.Id = p.Id;
db.Entry(p).CurrentValues.SetValues(price);
updated_price_count += db.SaveChanges();
}
}
processed_price_count++;
}
DbApi.RenewContext(ref db);
}
}
if (import.CheckNotImport)
{
Messages.Add("CHECK RESULT", "File " + import.File.FileName + " has been checked with map " + db.ImportMaps.Where(r => r.Id == import.MapId).First().Name);
Messages.Add("CHECK RESULT", "Spreadsheets: " + sheet_count);
Messages.Add("CHECK RESULT", "Found products: " + processed_product_count);
Messages.Add("CHECK RESULT", "Invalid products: " + invalid_product_count);
Messages.Add("CHECK RESULT", "Invalid prices: " + invalid_price_count);
Messages.Add("CHECK RESULT", "Process time: " + String.Format("{0:0.00}", (DateTime.Now - start_time).TotalSeconds) + " secs");
add_error_messages("CHECK RESULT", error_messages);
}
else
{
Messages.Add("IMPORT RESULT", "File " + import.File.FileName + " has been imported with map " + db.ImportMaps.Where(r => r.Id == import.MapId).First().Name);
Messages.Add("IMPORT RESULT", "Spreadsheets: " + sheet_count);
Messages.Add("IMPORT RESULT", "Processed products: " + processed_product_count);
Messages.Add("IMPORT RESULT", "Invalid products: " + invalid_product_count);
Messages.Add("IMPORT RESULT", "Added products: " + new_product_count);
Messages.Add("IMPORT RESULT", "Changed products: " + updated_product_count);
Messages.Add("IMPORT RESULT", "Invalid prices: " + invalid_price_count);
Messages.Add("IMPORT RESULT", "Added prices: " + new_price_count);
Messages.Add("IMPORT RESULT", "Changed prices: " + updated_price_count);
Messages.Add("CHECK RESULT", "Process time: " + String.Format("{0:0.00}", (DateTime.Now - start_time).TotalSeconds) + " secs");
add_error_messages("IMPORT RESULT", error_messages);
}
return(true);
}
