public List<Brand> GetBrands(string categoryUrl, bool includeChildrenCategories = false)
{
CategoryDL categoryDL = new CategoryDL();
Category category = categoryDL.GetCategoryByUrl(categoryUrl);
BrandDL brandDL = new BrandDL();
return brandDL.GetBrands(category.CategoryID, includeChildrenCategories);
}
public List<Brand> GetBrands(string categoryUrl)
{
CategoryDL categoryDL = new CategoryDL();
Category category = categoryDL.GetCategoryByUrl(categoryUrl);
BrandDL brandDL = new BrandDL();
return category != null ? brandDL.GetBrands(category.CategoryID) : null;
}
public List<Brand> GetBrands(int categoryID, bool includeChildrenCategories = false)
{
List<Brand> brands = null;
using (SqlConnection objConn = new SqlConnection(WebConfigurationManager.ConnectionStrings["eshopConnectionString"].ConnectionString))
{
using (SqlCommand objComm = new SqlCommand("SELECT brand.brandID, brand.name, COUNT(*) FROM brand INNER JOIN product ON brand.brandID=product.brandID INNER JOIN productCategory ON product.productID=productCategory.productID WHERE ", objConn))
{
objConn.Open();
List<int> categories = new CategoryDL().GetChildrenCategories(categoryID);
objComm.CommandText += " (categoryID=@categoryID";
if (includeChildrenCategories)
for (int i = 0; i < categories.Count; i++)
objComm.CommandText += " OR categoryID = @categoryID" + (i + 1);
objComm.CommandText += ")";
objComm.Parameters.Add("@categoryID", SqlDbType.Int).Value = categoryID;
if(includeChildrenCategories)
for (int i = 0; i < categories.Count; i++)
objComm.Parameters.Add("@categoryID" + (i + 1), SqlDbType.Int).Value = categories[i];
objComm.CommandText += " AND product.isActive=1 AND product.isApproved=1 GROUP BY brand.brandID, brand.name ORDER BY brand.name";
using (SqlDataReader reader = objComm.ExecuteReader())
{
if (reader.HasRows)
brands = new List<Brand>();
while (reader.Read())
brands.Add(new Brand(reader.GetInt32(0), reader.GetString(1) + " (" + reader.GetInt32(2).ToString() + ")"));
}
}
}
return brands;
}
public Category GetCategory(string name)
{
CategoryDL categoryDL = new CategoryDL();
return categoryDL.GetCategory(name);
}
public string parseProducts(string getCategory, string[] getSubcategories, bool getImages, bool getAttributes, string categoryName, int categoryID, bool overwrite, string[] codes, bool isActive, bool isApproved)
{
try
{
ErrorLog.LogMessage("parsing products");
EweDL eweDL = new EweDL();
List<eshopBE.Attribute> attributes = null;
ProductBL productBL = new ProductBL();
AttributeBL attributeBL = new AttributeBL();
attributes = attributeBL.GetAttributesForCategory(categoryID);
CategoryDL categoryDL = new CategoryDL();
Category category = categoryDL.GetCategory(categoryID);
//productBL.SetInStock(1, false, categoryID);
int newProducts = 0;
int updatedProducts = 0;
bool isNew = false;
/*List<string> subcategory = new List<string>();
string categoryString=string.Empty;
foreach (XmlNode xmlNode in nodeList)
foreach (XmlNode xmlChildNode in xmlNode.ChildNodes)
{
if (xmlChildNode.Name == "category")
{
categoryString = xmlChildNode.InnerText;
}
if (xmlChildNode.Name == "subcategory")
{
categoryString += "-" + xmlChildNode.InnerText;
subcategory.Add(categoryString);
}
}
string prethodni = string.Empty;
using (StreamWriter sw = new StreamWriter(Server.MapPath("~") + "categories.txt"))
{
foreach (string category in subcategory)
{
if (prethodni != category)
sw.WriteLine(category);
prethodni = category;
}
}*/
for (int j = 0; j < getSubcategories.Length; j++)
{
XmlDocument xmlDoc = eweDL.GetXml(getCategory, getSubcategories[j], getImages, getAttributes);
XmlNodeList nodeList = xmlDoc.DocumentElement.SelectNodes("product");
ErrorLog.LogMessage("preuzeti proizvodi");
foreach (XmlNode xmlNode in nodeList)
{
string code = xmlNode.SelectSingleNode("id").InnerText.Trim();
bool save = false;
for (int i = 0; i < codes.Length; i++)
{
if (codes[i] == code)
{
save = true;
break;
}
}
if (save)
{
Product product = new Product();
product.IsApproved = isApproved;
product.IsActive = isActive;
product.SupplierID = 1;
product.VatID = 4;
product.Categories = new List<Category>();
product.Categories.Add(category);
if (getAttributes)
product.Specification = specificationToHtml(xmlNode.SelectSingleNode("specifications").OuterXml);
else
product.Specification = string.Empty;
product.IsInStock = true;
bool isLocked = true;
isNew = false;
foreach (XmlNode xmlChildNode in xmlNode.ChildNodes)
{
switch (xmlChildNode.Name)
{
case "id":
{
product.SupplierCode = xmlChildNode.InnerText.Trim();
product.ProductID = productBL.GetProductIDBySupplierCode(product.SupplierCode);
isLocked = productBL.IsLocked(product.ProductID);
if (product.ProductID <= 0)
isNew = true;
break;
}
case "manufacturer":
{
if (!isLocked)
{
//if (product.ProductID <= 0)
//{
BrandBL brandBL = new BrandBL();
Brand brand = brandBL.GetBrandByName(xmlChildNode.InnerText.Trim());
if (brand == null)
{
brand = new Brand();
brand.Name = xmlChildNode.InnerText.Trim();
brand.BrandID = brandBL.SaveBrand(brand);
//product.Brand.BrandID = brand.BrandID;
}
if (product.Brand == null)
product.Brand = new Brand();
product.Brand.BrandID = brand.BrandID;
//}
}
break;
}
case "name":
{
if (!isLocked)
product.Name = xmlChildNode.InnerText.Trim();
break;
}
case "category":
{
break;
}
/*case "subcategory":
{
CategoryBL categoryBL = new CategoryBL();
//string categoryName = getSubcategory; //(xmlChildNode.InnerText.Trim() == "NOTEBOOK") ? "LAPTOP" : xmlChildNode.InnerText.Trim();
Category category = categoryBL.GetCategory(categoryName);
product.Categories = new System.Collections.Generic.List<Category>();
product.Categories.Add(category);
break;
}*/
case "price":
{
if (!isLocked)
{
product.Price = calculatePrice(double.Parse(xmlChildNode.InnerText.Replace('.', ',').Trim()), category.PricePercent);
product.WebPrice = calculatePrice(double.Parse(xmlChildNode.InnerText.Replace('.', ',').Trim()), category.WebPricePercent);
}
break;
}
case "price_rebate":
{
break;
}
case "vat":
{
if (!isLocked)
{
switch (xmlChildNode.InnerText.Trim())
{
case "20": { product.VatID = 4; break; }
}
}
break;
}
case "ean":
{
if (!isLocked)
product.Ean = xmlChildNode.InnerText.Trim();
break;
}
case "images":
{
if (!isLocked)
{
//if (product.ProductID <= 0 || overwrite)
//{
foreach (XmlNode xmlImageNode in xmlChildNode.ChildNodes)
{
if (product.Images == null)
product.Images = new System.Collections.Generic.List<string>();
if (xmlImageNode.Name == "image")
{
if (saveImageFromUrl(xmlImageNode.InnerText.Trim()))
product.Images.Add(Path.GetFileName(xmlImageNode.InnerText.Trim()));
}
}
//}
}
break;
}
case "specifications":
{
if (!isLocked)
{
if (product.ProductID <= 0 || overwrite)
{
if (product.Categories != null)
if (product.Categories[0] != null)
{
//List<AttributeValue> attributeValues;
int attributeID;
foreach (XmlNode xmlSpecificationNode in xmlChildNode.ChildNodes)
{
if (xmlSpecificationNode.Name == "attribute_group")
{
string attributeGroup = xmlSpecificationNode.Attributes[0].Value;
foreach (XmlNode xmlAttributeNode in xmlSpecificationNode.ChildNodes)
{
if (xmlAttributeNode.Attributes[0].Value != "Programi / Ekskluzivne aplikacije / Servisi")
{
if ((attributeID = getAttributeID(attributes, attributeGroup + "-" + xmlAttributeNode.Attributes[0].Value)) == 0)
{
attributeID = addAttribute(product.Categories[0].CategoryID, attributeGroup + "-" + xmlAttributeNode.Attributes[0].Value);
//attributes = attributeBL.GetAttributesForCategory(product.Categories[0].CategoryID);
if (attributes == null)
attributes = new List<eshopBE.Attribute>();
attributes.Add(new eshopBE.Attribute(attributeID, attributeGroup + "-" + xmlAttributeNode.Attributes[0].Value, false, false, 0));
}
if (product.Attributes == null)
product.Attributes = new List<AttributeValue>();
int attributeValueID = getAttributeValueID(attributeID, xmlAttributeNode.InnerText.Trim());
product.Attributes.Add(new AttributeValue(attributeValueID, xmlAttributeNode.InnerText.Trim(), attributeID, 0, string.Empty, 0));
}
}
}
}
}
}
}
break;
}
}
}
product.Code = product.SupplierCode;
product.Description = string.Empty;
if (!isLocked)
if (productBL.SaveProduct(product) > 0)
if (isNew)
newProducts++;
else
updatedProducts++;
}
}
}
return "Dodato " + newProducts.ToString() + " proizvoda. Izmenjeno " + updatedProducts.ToString() + " proizvoda";
}
catch (Exception ex)
{
ErrorLog.LogError(ex);
}
return string.Empty;
}
public DataTable GetCategoriesForFirstPage()
{
CategoryDL categoryDL = new CategoryDL();
return categoryDL.GetCategoriesForFirstPage();
}
public Category GetCategory(int categoryID)
{
CategoryDL categoryDL = new CategoryDL();
return categoryDL.GetCategory(categoryID);
}
public int SaveCategory(Category category)
{
CategoryDL categoryDL = new CategoryDL();
if (category.Name == string.Empty)
throw new BLException("Unesite naziv kategorije");
if (category.CategoryID > 0)
return categoryDL.UpdateCategory(category);
else
return categoryDL.SaveCategory(category);
}
public DataTable GetCategories()
{
CategoryDL categoryDL = new CategoryDL();
return categoryDL.GetCategories();
}
/*public List<Category> GetNestedCategoriesList()
{
CategoryDL categoryDL = new CategoryDL();
DataTable categoriesDT = categoryDL.GetCategories();
List<Category> list = new List<Category>();
list.Add(new Category((int)categoriesDT.Rows[0]["categoryID"], categoriesDT.Rows[0]["name"].ToString(), 0, categoriesDT.Rows[0]["url"].ToString(), categoriesDT.Rows[0]["imageUrl"].ToString()));
Category temp = list[0];
for (int i = 1; i < categoriesDT.Rows.Count; i++)
{
//while ((int)categoriesDT.Rows[i]["parentID"] == temp.CategoryID)
if ((int)categoriesDT.Rows[i]["parentID"] == temp.CategoryID)
{
if (temp.SubCategory == null)
temp.SubCategory = new List<Category>();
temp.SubCategory.Add(new Category((int)categoriesDT.Rows[i]["categoryID"], categoriesDT.Rows[i]["name"].ToString(), temp.CategoryID, categoriesDT.Rows[i]["url"].ToString(), categoriesDT.Rows[i]["imageUrl"].ToString()));
}
else
{
if ((int)categoriesDT.Rows[i]["parentID"] > temp.CategoryID)
{
temp = temp.SubCategory[temp.SubCategory.Count - 1];
i--;
}
else
{
temp = list[0];
i--;
}
}
}
return list[0].SubCategory;
}*/
/*public DataTable GetNestedCategoriesDataTable()
{
CategoryDL categoryDL = new CategoryDL();
DataTable categoriesDT = categoryDL.GetCategories();
int tempCategoryID = int.Parse(categoriesDT.Rows[0]["categoryID"].ToString());
string baseString = "--";
for (int i = 1; i < categoriesDT.Rows.Count; i++)
{
if (int.Parse(categoriesDT.Rows[i]["parentID"].ToString()) == tempCategoryID)
categoriesDT.Rows[i]["name"] = baseString + categoriesDT.Rows[i]["name"].ToString();
else
if (int.Parse(categoriesDT.Rows[i]["parentID"].ToString()) > tempCategoryID)
{
tempCategoryID = int.Parse(categoriesDT.Rows[i-1]["categoryID"].ToString());
baseString += "--";
i--;
}
else
{
tempCategoryID = int.Parse(categoriesDT.Rows[0]["categoryID"].ToString());
baseString = "--";
i--;
}
}
return categoriesDT;
}*/
/*public List<Category> GetCategories2()
{
CategoryDL categoryDL = new CategoryDL();
DataTable categoriesDT = categoryDL.GetCategories();
int level = 1;
List<Category> list = new List<Category>();
list.Add(new Category((int)categoriesDT.Rows[0]["categoryID"], categoriesDT.Rows[0]["name"].ToString(), (int)categoriesDT.Rows[0]["parentID"], categoriesDT.Rows[0]["url"].ToString(), categoriesDT.Rows[0]["imageUrl"].ToString()));
Category temp = list[0];
for (int i = 1; i < categoriesDT.Rows.Count; i++)
{
for (int j = 0; j < categoriesDT.Rows.Count; j++)
{
if ((int)categoriesDT.Rows[j]["parentID"] == temp.CategoryID)
{
if (temp.SubCategory == null)
temp.SubCategory = new List<Category>();
temp.SubCategory.Add(new Category((int)categoriesDT.Rows[j]["categoryID"], categoriesDT.Rows[j]["name"].ToString(), (int)categoriesDT.Rows[j]["parentID"], categoriesDT.Rows[j]["url"].ToString(), categoriesDT.Rows[j]["imageUrl"].ToString()));
}
}
if (level < 2)
{
temp = temp.SubCategory[i - 1];
level++;
}
else
{
if (i < 10)
temp = list[0].SubCategory[i - 1];
//level--;
}
}
return list[0].SubCategory;
}*/
/*public List<Category> GetCategories3()
{
CategoryDL categoryDL = new CategoryDL();
DataTable categoriesDT = categoryDL.GetCategories();
List<Category> list = new List<Category>();
for (int i = 0; i < categoriesDT.Rows.Count; i++)
{
if ((int)categoriesDT.Rows[i]["parentID"] == 2)
list.Add(new Category((int)categoriesDT.Rows[i]["categoryID"], categoriesDT.Rows[i]["name"].ToString(), (int)categoriesDT.Rows[i]["parentID"], categoriesDT.Rows[i]["url"].ToString(), categoriesDT.Rows[i]["imageUrl"].ToString()));
}
for (int i = 0; i < list.Count; i++)
for (int j = 0; j < categoriesDT.Rows.Count; j++)
if (list[i].CategoryID == (int)categoriesDT.Rows[j]["parentID"])
{
if (list[i].SubCategory == null)
list[i].SubCategory = new List<Category>();
list[i].SubCategory.Add(new Category((int)categoriesDT.Rows[j]["categoryID"], categoriesDT.Rows[j]["name"].ToString(), (int)categoriesDT.Rows[j]["parentID"], categoriesDT.Rows[j]["url"].ToString(), categoriesDT.Rows[j]["imageUrl"].ToString()));
}
return list;
}*/
public List<Category> GetNestedCategoriesList()
{
CategoryDL categoryDL = new CategoryDL();
DataTable categoriesDT = categoryDL.GetCategories("sortOrder");
return GetCategoriesList(categoriesDT, 1);
}
public int DeleteCategory(int categoryID)
{
CategoryDL categoryDL = new CategoryDL();
return categoryDL.DeleteCategory(categoryID);
}
public List<Product> GetProducts(int categoryID, List<string> brandsID, List<AttributeValue> attributeValues, string sort, double priceFrom, double priceTo)
{
List<Product> products = null;
int tempAttributeID;
int tableIndex;
using (SqlConnection objConn = new SqlConnection(WebConfigurationManager.ConnectionStrings["eshopConnectionString"].ConnectionString))
{
using (SqlCommand objComm = new SqlCommand("SELECT product.productID, code, product.name, product.description, product.price, webPrice, brand.name, productImageUrl.imageUrl, promotionProduct.price, promotion.imageUrl, promotion.dateFrom, promotion.dateTo, category.name FROM product INNER JOIN brand ON product.brandID=brand.brandID INNER JOIN productImageUrl ON product.productID=productImageUrl.productID INNER JOIN productCategory ON product.productID=productCategory.productID LEFT JOIN promotionProduct ON product.productID=promotionProduct.productID LEFT JOIN promotion ON promotionProduct.promotionID=promotion.promotionID INNER JOIN category ON productCategory.categoryID=category.categoryID", objConn))
{
if(attributeValues.Count > 0)
{
tempAttributeID = 0;
tableIndex = 0;
for (int i = 0; i < attributeValues.Count; i++)
{
if (attributeValues[i].AttributeID != tempAttributeID)
{
tableIndex++;
objComm.CommandText += " INNER JOIN productAttributeValue AS a" + tableIndex.ToString() + " ON product.productID=a" + tableIndex.ToString() + ".productID";
tempAttributeID = attributeValues[i].AttributeID;
}
}
}
objComm.CommandText += " WHERE productImageUrl.sortOrder=1 AND product.isActive=1 AND product.isApproved=1";
List<int> childrenCategories = new CategoryDL().GetChildrenCategories(categoryID);
objComm.CommandText += " AND (productCategory.categoryID=@categoryID";
for (int i = 0; i < childrenCategories.Count;i++)
objComm.CommandText += " OR productCategory.categoryID = @childrenCategoryID" + (i+1);
objComm.CommandText += ")";
objComm.Parameters.Add("@categoryID", SqlDbType.Int).Value = categoryID;
for (int i = 0; i < childrenCategories.Count; i++)
objComm.Parameters.Add("@childrenCategoryID" + (i + 1), SqlDbType.Int).Value = childrenCategories[i];
if (priceFrom > 0)
{
objComm.CommandText += " AND webPrice>=@priceFrom";
objComm.Parameters.Add("@priceFrom", SqlDbType.Float).Value = priceFrom;
}
if (priceTo > 0)
{
objComm.CommandText += " AND webPrice<=@priceTo";
objComm.Parameters.Add("@priceTo", SqlDbType.Float).Value = priceTo;
}
if (brandsID.Count > 0)
{
for (int i = 0; i < brandsID.Count; i++)
{
if (i == 0)
objComm.CommandText += " AND (brand.brandID=@brandID" + (i + 1).ToString();
else
objComm.CommandText += " OR brand.brandID=@brandID" + (i + 1).ToString();
objComm.Parameters.Add("@brandID" + (i + 1).ToString(), SqlDbType.Int).Value = brandsID[i];
if (i == brandsID.Count - 1)
objComm.CommandText += ")";
}
}
tempAttributeID = 0;
tableIndex = 0;
for (int i = 0; i < attributeValues.Count; i++)
{
if (attributeValues[i].AttributeID != tempAttributeID)
{
tableIndex++;
objComm.CommandText += " AND (a" + tableIndex.ToString() + ".attributeValueID=@attributeValueID" + (i + 1).ToString();
tempAttributeID = attributeValues[i].AttributeID;
}
else
objComm.CommandText += " OR a" + tableIndex.ToString() + ".attributeValueID=@attributeValueID" + (i + 1).ToString();
if (i < attributeValues.Count - 1)
if (tempAttributeID != attributeValues[i + 1].AttributeID)
objComm.CommandText += ")";
if (i == attributeValues.Count - 1)
objComm.CommandText += ")";
//objComm.CommandText += " AND a" + (i + 1).ToString() + ".attributeValueID=@attributeValueID" + (i + 1).ToString();
objComm.Parameters.Add("@attributeValueID" + (i + 1).ToString(), SqlDbType.Int).Value = attributeValues[i].AttributeValueID;
}
objComm.CommandText += " ORDER BY " + sort;
objConn.Open();
using (SqlDataReader reader = objComm.ExecuteReader())
{
if (reader.HasRows)
products = new List<Product>();
Product product;
while (reader.Read())
{
product = new Product();
product.ProductID = reader.GetInt32(0);
product.Code = reader.GetString(1);
product.Name = reader.GetString(2);
product.Description = reader.GetString(3);
product.Price = reader.GetDouble(4);
product.WebPrice = reader.GetDouble(5);
product.Brand = new Brand(-1, reader.GetString(6));
product.Images = new List<string>();
string directory = createImageUrl(reader.GetString(7));
//if (System.IO.File.Exists(HttpContext.Current.Server.MapPath("~/images/" + reader.GetString(7))))
if (System.IO.File.Exists(HttpContext.Current.Server.MapPath("~" + directory)))
//product.Images.Add("/images/" + reader.GetString(7));
product.Images.Add(directory);
else
product.Images.Add("/images/no-image.jpg");
if (!Convert.IsDBNull(reader[8]))
{
if (reader.GetDateTime(10) < DateTime.UtcNow && reader.GetDateTime(11) > DateTime.UtcNow)
{
product.Promotion = new Promotion();
product.Promotion.Price = reader.GetDouble(8);
product.Promotion.ImageUrl = reader.GetString(9);
}
}
product.Categories = new List<Category>();
product.Categories.Add(new Category(categoryID, reader.GetString(12), -1, string.Empty, string.Empty, 0, 0, 0, string.Empty, -1));
product.Description = GetProductAttributeValues(product.ProductID, true);
products.Add(product);
}
}
}
}
return products;
}
public List<Product> GetProducts(string categoryUrl, List<string> brandsID, List<AttributeValue> attributeValues, string sortName, string priceFrom, string priceTo)
{
/*string sort = string.Empty;
switch (sortString)
{
case "Nazivu":
{
sort = " brand.name";
break;
}
case "Ceni opadajuće":
{
sort = " product.price DESC";
break;
}
case "Ceni rastuće":
{
sort = " product.price";
break;
}
}*/
CategoryDL categoryDL = new CategoryDL();
Category category = categoryDL.GetCategoryByUrl(categoryUrl);
ProductDL productDL = new ProductDL();
return productDL.GetProducts(category.CategoryID, brandsID, attributeValues, getSort(sortName), getPrice(priceFrom), getPrice(priceTo));
}
public bool SaveProduct(string code, bool isActive, bool isApproved, int categoryID, int kimtecCategoryID)
{
//int newProducts = 0;
//int updatedProducts = 0;
DataTable kimtecProduct = new KimtecDL().GetProductBySupplierCode(code);
Category category = new CategoryDL().GetCategory(categoryID);
Product product = new Product();
product.IsApproved = isApproved;
product.IsActive = isActive;
product.SupplierID = 1004;
product.SupplierCode = code;
product.VatID = 4;
product.Categories = new List<Category>();
product.Categories.Add(category);
product.Specification = string.Empty;
product.IsInStock = true;
bool isNew = false;
bool isLocked = false;
product.Code = code;
product.ProductID = new ProductDL().GetProductIDBySupplierCode(code);
if (product.ProductID <= 0)
isNew = true;
isLocked = new ProductDL().IsLocked(product.ProductID);
Brand brand = new BrandDL().GetBrandByName(kimtecProduct.Rows[0]["brand"].ToString());
if(brand == null)
{
brand = new Brand();
brand.Name = kimtecProduct.Rows[0]["brand"].ToString();
brand.BrandID = new BrandDL().SaveBrand(brand);
}
if (product.Brand == null)
product.Brand = new Brand();
product.Brand = brand;
product.Name = kimtecProduct.Rows[0]["name"].ToString();
product.Price = calculatePrice(double.Parse(kimtecProduct.Rows[0]["partnerPrice"].ToString()), category.PricePercent);
product.WebPrice = calculatePrice(double.Parse(kimtecProduct.Rows[0]["partnerPrice"].ToString()), category.WebPricePercent);
product.Ean = kimtecProduct.Rows[0]["barcodeValue"].ToString();
product.SupplierPrice = double.Parse(kimtecProduct.Rows[0]["partnerPrice"].ToString());
product.Images = new List<string>();
product.Images.Add(saveProductImage(kimtecProduct.Rows[0]["imageUrl"].ToString()));
product.Attributes = GetProductAttributes(code, kimtecCategoryID);
product.Description = kimtecProduct.Rows[0]["marketingDescription"].ToString();
if (!isLocked)
if (new ProductBL().SaveProduct(product) > 0)
return true;
return false;
//return new int[] { newProducts, updatedProducts };
}
