/// <summary>
/// Gets the products.
/// </summary>
/// <returns>The products.</returns>
/// <param name="worksheet">Worksheet.</param>
/// <param name="CategoriesStyles">Parameters styles.</param>
/// <param name="created_at">Create at.</param>
private List <ProductCell> GetProducts(ref ExcelWorksheet worksheet, ref Dictionary <int, int> CategoriesStyles, ref DateTime created_at)
{
string Created_At_Short = created_at.ToShortDateString();
int Row = 1;
int EmptyCell = 0;
List <ProductCell> products = new List <ProductCell>();
ProductCell categoryTransort = new ProductCell();
while (EmptyCell < 1000)
{
ExcelRange cell = worksheet.Cells[Row, 1];
if (cell.Text == "")
{
++EmptyCell;
}
else
{
EmptyCell = 0;
Match ContainsNumbers = OnlyNumbers.Match(cell.Text);
if (ContainsNumbers.Success)
{
ProductCell product = new ProductCell();
product.category = categoryTransort.category;
product.subcat = categoryTransort.subcat;
product.second_subcat = categoryTransort.second_subcat;
product.product_code = Convert.ToInt32(worksheet.Cells[Row, 1].Text);
product.product_vendore_code = worksheet.Cells[Row, 2].Text;
product.product_name = worksheet.Cells[Row, 3].Text;
string product_price = worksheet.Cells[Row, 4].Text;
product.product_price = ConvertSaveString(ref product_price, "double");
product.product_order = worksheet.Cells[Row, 5].Text;
product.product_link_1 = worksheet.Cells[Row, 6].Text;
product.product_link_2 = worksheet.Cells[Row, 7].Text;
product.product_comment = worksheet.Cells[Row, 8].Text;
product.created_at = Created_At_Short;
products.Add(product);
}
else
{
categoryTransort = GetProductRegulationParameters(ref cell, ref CategoriesStyles, ref categoryTransort, ref Created_At_Short);
}
}
++Row;
}
logger.WriteLog("Get products from first worksheet", LogLevel.Exceling);
return(products);
}
private void SortOutProductCells(ref ExcelWorksheet worksheet, ref DateTime Created_At)
{
double no_price = -1;
bool checking = false;
string htmlData = null;
ProductCell productCell = null;
Dictionary <int, int> CategoriesStyles = DefineCategoriesStyles(ref worksheet);
List <ProductCell> products = GetProducts(ref worksheet, ref CategoriesStyles, ref Created_At);
List <ProductCell> old_products = database.product.SelectProducts();
foreach (ProductCell new_product in products)
{
productCell = new_product;
if (!database.product.CheckProductExist(ref new_product.product_code))
{
htmlData = GetHTMLData(ref new_product.product_link_1);
if (htmlData != null)
{
new_product.product_url_image = GetImageUrlFromHtml(ref htmlData);
}
database.product.AddProduct(ref productCell);
database.price.AddPriceProduct(ref productCell, ref Created_At);
}
else
{
database.product.UpdateCurrentPrice(ref new_product.product_code, ref new_product.product_price);
database.price.AddPriceProduct(ref productCell, ref Created_At);
}
}
foreach (ProductCell oldProduct in old_products)
{
foreach (ProductCell new_product in products)
{
if (oldProduct.product_code == new_product.product_code)
{
checking = true;
}
}
if (checking == false)
{
database.product.UpdateCurrentPrice(ref oldProduct.product_code, ref no_price);
}
checking = false;
}
logger.WriteLog("Sorted out cells product and inserted all of that to database", LogLevel.Exceling);
}
/// <summary>
/// Gets the product regulation parameters.
/// </summary>
/// <returns>The product regulation parameters.</returns>
/// <param name="cell">Cell.</param>
/// <param name="CategoriesStyles">Parameters styles.</param>
/// <param name="Last_Category">Last parameter.</param>
private ProductCell GetProductRegulationParameters(ref ExcelRange cell, ref Dictionary <int, int> CategoriesStyles, ref ProductCell Last_Category, ref string created_at)
{
CategoryCell category;
if (CategoriesStyles.ContainsValue(cell.StyleID))
{
foreach (KeyValuePair <int, int> parameter in CategoriesStyles)
{
if (parameter.Value == cell.StyleID)
{
string cell_text = cell.Text;
category = database.category.SelectCategoryByName(ref cell_text);
if (category == null)
{
category = new CategoryCell();
category.created_at = created_at;
category.category_name = cell.Text;
category.category_position = (short)parameter.Key;
category = database.category.AddCategory(ref category);
}
switch (parameter.Key)
{
case 1:
Last_Category.category = category.category_id;
Last_Category.subcat = -1;
Last_Category.second_subcat = -1;
break;
case 2:
Last_Category.subcat = category.category_id;
Last_Category.second_subcat = -1;
break;
case 3:
Last_Category.second_subcat = category.category_id;
break;
}
}
}
}
return(Last_Category);
}