public List<Product> GetWishListProducts(int userID)
{
List<Product> products = new List<Product>();
using (SqlConnection objConn = new SqlConnection(WebConfigurationManager.ConnectionStrings["eshopConnectionString"].ConnectionString))
{
using (SqlCommand objComm = new SqlCommand("wishList_get", objConn))
{
objConn.Open();
objComm.CommandType = CommandType.StoredProcedure;
objComm.Parameters.Add("@userID", SqlDbType.Int).Value = userID;
using (SqlDataReader reader = objComm.ExecuteReader())
{
while (reader.Read())
{
Product product = new Product();
product.ProductID = reader.GetInt32(0);
product.Code = reader.GetString(1);
product.Name = reader.GetString(4);
product.Description = reader.GetString(5);
product.Price = reader.GetDouble(6);
product.WebPrice = reader.GetDouble(7);
product.Brand = new Brand(reader.GetInt32(3), reader.GetString(8));
product.Categories = new List<Category>();
product.Categories.Add(new Category(reader.GetInt32(19), reader.GetString(20), null, reader.GetString(21), string.Empty, 0, 0, 0, string.Empty, true));
product.Images = new ProductDL().GetProductImages(product.ProductID);
products.Add(product);
}
}
}
}
return products;
}
public OrderItem(int orderItemID, int orderID, Product product, double price, double userPrice, double quantity)
{
_orderItemID = orderItemID;
_orderID = orderID;
_product = product;
_productPrice = price;
_userPrice = userPrice;
_quantity = quantity;
}
public int[] SaveProduct(System.Web.UI.WebControls.GridViewRow row, int categoryID, bool isApproved, bool isActive, int kimtecCategoryID)
{
Category category = new CategoryBL().GetCategory(categoryID);
ProductBL productBL = new ProductBL();
Product product = new Product();
product.ProductID = productBL.GetProductIDBySupplierCode(((Label)row.FindControl("lblCode")).Text);
bool isLocked = productBL.IsLocked(product.ProductID);
int newProducts = 0;
int updatedProducts = 0;
bool isNew = product.ProductID <= 0;
if (!isLocked)
{
product.Code = ((Label)row.FindControl("lblCode")).Text;
product.SupplierCode = ((Label)row.FindControl("lblCode")).Text;
product.Brand = GetBrand(((Label)row.FindControl("lblBrand")).Text);
product.Name = ((Label)row.FindControl("lblName")).Text;
product.Description = ((Label)row.FindControl("lblDescription")).Text;
product.Price = calculatePrice(double.Parse(((Label)row.FindControl("lblPartnerPrice")).Text != string.Empty ? ((Label)row.FindControl("lblPartnerPrice")).Text : "0,00"), category.PricePercent);
product.WebPrice = calculatePrice(double.Parse(((Label)row.FindControl("lblPartnerPrice")).Text != string.Empty ? ((Label)row.FindControl("lblPartnerPrice")).Text : "0,00"), category.WebPricePercent);
product.Images = new List<string>();
product.Images.Add(saveProductImage(((Label)row.FindControl("lblImageUrl")).Text));
product.Attributes = GetProductAttributes(product.Code, kimtecCategoryID);
product.Categories = new List<Category>();
product.Categories.Add(category);
product.SupplierID = 1004;
product.IsApproved = isApproved;
product.IsActive = isActive;
product.VatID = 4;
product.Specification = string.Empty;
product.IsInStock = int.Parse(((Label)row.FindControl("lblStock")).Text != string.Empty ? ((Label)row.FindControl("lblStock")).Text : "0") > 0 ? true : false;
product.Ean = ((Label)row.FindControl("lblBarcode")).Text;
if (productBL.SaveProduct(product) > 0)
{
if (isNew)
newProducts++;
else
updatedProducts++;
}
}
return new int[] { newProducts, updatedProducts };
}
private List<Product> GetProducts(int productID, string code, string supplierCode, List<eshopBE.AttributeValue> attributes, int categoryID, int supplierID, bool? isApproved, bool? isActive, int? brandID)
{
List<Product> products = null;
bool whereExists = false;
using (SqlConnection objConn = new SqlConnection(WebConfigurationManager.ConnectionStrings["eshopConnectionString"].ConnectionString))
{
using (SqlCommand objComm = new SqlCommand("SELECT product.productID, code, supplierCode, brand.brandID, product.name, description, price, webPrice, brand.name, isApproved, isActive, isLocked, isInStock FROM product INNER JOIN brand ON product.brandID=brand.brandID", objConn))
{
try
{
objConn.Open();
if (productID > 0)
{
objComm.CommandText += " WHERE product.productID=@productID";
objComm.Parameters.Add("@productID", SqlDbType.Int).Value = productID;
whereExists = true;
}
else if (code != string.Empty)
{
objComm.CommandText += " WHERE product.code=@code";
objComm.Parameters.Add("@code", SqlDbType.NVarChar, 50).Value = code;
whereExists = true;
}
else if (supplierCode != string.Empty)
{
objComm.CommandText += " WHERE product.supplierCode=@supplierCode";
objComm.Parameters.Add("@supplierCode", SqlDbType.NVarChar, 50).Value = supplierCode;
whereExists = true;
}
else if (attributes != null || categoryID > 0)
{
if (attributes != null)
for (int i = 0; i < attributes.Count; i++)
objComm.CommandText += " INNER JOIN productAttributeValue as a" + (i + 1).ToString() + " ON product.productID=a" + (i + 1).ToString() + ".productID";
if (categoryID > 0)
objComm.CommandText += " INNER JOIN productCategory ON product.productID=productCategory.productID";
if (attributes != null)
{
for (int i = 0; i < attributes.Count; i++)
{
if (!whereExists)
{
objComm.CommandText += " WHERE a" + (i + 1).ToString() + ".attributeValueID=@attributeValueID" + (i + 1).ToString();
whereExists = true;
}
else
objComm.CommandText += " AND a" + (i + 1).ToString() + ".attributeValueID=@attributeValueID" + (i + 1).ToString();
objComm.Parameters.Add("@attributeValueID" + (i + 1).ToString(), SqlDbType.Int).Value = attributes[i].AttributeValueID;
}
}
if (categoryID > 0)
{
if (!whereExists)
{
objComm.CommandText += " WHERE productCategory.categoryID=@categoryID";
whereExists = true;
}
else
objComm.CommandText += " AND productCategory.categoryID=@categoryID";
objComm.Parameters.Add("@categoryID", SqlDbType.Int).Value = categoryID;
}
}
if (supplierID > -1)
{
objComm.CommandText += (whereExists) ? " AND supplierID=@supplierID" : " WHERE supplierID=@supplierID";
objComm.Parameters.Add("@supplierID", SqlDbType.Int).Value = supplierID;
whereExists = true;
}
if (isApproved != null)
{
objComm.CommandText += (whereExists) ? " AND isApproved=@isApproved" : " WHERE isApproved=@isApproved";
objComm.Parameters.Add("@isApproved", SqlDbType.Bit).Value = isApproved;
whereExists = true;
}
if (isActive != null)
{
objComm.CommandText += (whereExists) ? " AND isActive=@isActive" : " WHERE isActive=@isActive";
objComm.Parameters.Add("@isActive", SqlDbType.Bit).Value = isActive;
whereExists = true;
}
if (brandID != null)
{
objComm.CommandText += (whereExists) ? " AND brand.brandID=@brandID" : " WHERE brand.brandID=@brandID";
objComm.Parameters.Add("@brandID", SqlDbType.Int).Value = brandID;
whereExists = true;
}
using (SqlDataReader reader = objComm.ExecuteReader())
{
Product product;
if (reader.HasRows)
products = new List<Product>();
while (reader.Read())
{
product = new Product();
product.ProductID = reader.GetInt32(0);
product.Code = reader.GetString(1);
product.SupplierCode = reader.GetString(2);
product.Brand = new Brand(reader.GetInt32(3), reader.GetString(8));
product.Name = reader.GetString(4);
product.Description = reader.GetString(5);
product.Price = reader.GetDouble(6);
product.WebPrice = reader.GetDouble(7);
product.IsApproved = reader.GetBoolean(9);
product.IsActive = reader.GetBoolean(10);
product.IsLocked = reader.GetBoolean(11);
product.IsInStock = reader.GetBoolean(12);
product.Images = GetProductImages(product.ProductID);
products.Add(product);
}
}
}
catch (SqlException ex)
{
ErrorLog.LogError(ex);
throw new DLException("Error while loading products list", ex);
}
}
}
return products;
}
public Product GetProduct(int productID, string url)
{
Product product = null;
using (SqlConnection objConn = new SqlConnection(WebConfigurationManager.ConnectionStrings["eshopConnectionString"].ConnectionString))
{
using (SqlCommand objComm = new SqlCommand("SELECT productID, code, supplierCode, brand.brandID, product.name, description, price, webPrice, brand.name, isApproved, isActive, insertDate, updateDate, vatID, supplierID, specification, isLocked, isInStock, ean FROM product INNER JOIN brand ON product.brandID=brand.brandID", objConn))
{
try
{
objConn.Open();
if (productID > 0)
{
objComm.CommandText += " WHERE productID=@productID";
objComm.Parameters.Add("@productID", SqlDbType.Int).Value = productID;
}
else if (url != string.Empty)
{
objComm.CommandText += " WHERE url=@url";
objComm.Parameters.Add("@url", SqlDbType.NVarChar, 100).Value = url;
}
using (SqlDataReader reader = objComm.ExecuteReader())
{
if (reader.HasRows)
product = new Product();
while (reader.Read())
{
product.ProductID = productID;
product.Code = reader.GetString(1);
product.SupplierCode = reader.GetString(2);
product.Brand = new Brand(reader.GetInt32(3), reader.GetString(8));
product.Name = reader.GetString(4);
product.Description = reader.GetString(5);
product.Price = reader.GetDouble(6);
product.WebPrice = reader.GetDouble(7);
product.IsApproved = reader.GetBoolean(9);
product.IsActive = reader.GetBoolean(10);
product.InsertDate = reader.GetDateTime(11);
product.UpdateDate = reader.GetDateTime(12);
product.VatID = reader.GetInt32(13);
product.SupplierID = reader.GetInt32(14);
//if (!Convert.IsDBNull(reader[15]))
product.Specification = createProductSpecification(product.ProductID);//reader.GetString(15);
product.IsLocked = reader.GetBoolean(16);
product.IsInStock = reader.GetBoolean(17);
if (!Convert.IsDBNull(reader[18]))
product.Ean = reader.GetString(18);
product.Categories = GetProductCategories(product.ProductID);
product.Attributes = GetProductAttributes(product.ProductID);
product.Images = GetProductImages(product.ProductID);
product.Promotion = getPromotions(product.ProductID);
if (product.Description == string.Empty)
product.Description = GetProductAttributeValues(product.ProductID, true);
//if (product.Specification == string.Empty)
//product.Specification = createProductSpecification(product.ProductID);
}
}
}
catch (SqlException ex)
{
ErrorLog.LogError(ex);
throw new DLException("Error while loading product", ex);
}
}
}
return product;
}
public int UpdateProduct(Product product)
{
int status;
using (SqlConnection objConn = new SqlConnection(WebConfigurationManager.ConnectionStrings["eshopConnectionString"].ConnectionString))
{
using (SqlCommand objComm = new SqlCommand("UPDATE product SET code=@code, supplierCode=@supplierCode, brandID=@brandID, name=@name, description=@description, price=@price, webPrice=@webPrice, isApproved=@isApproved, isActive=@isActive, supplierID=@supplierID, vatID=@vatID, updateDate=@updateDate, specification=@specification, isLocked=@isLocked, isInStock=@isInStock, ean=@ean WHERE productID=@productID", objConn))
{
try
{
objConn.Open();
if (product.Specification == string.Empty)
objComm.CommandText = "UPDATE product SET code=@code, supplierCode=@supplierCode, brandID=@brandID, name=@name, description=@description, price=@price, webPrice=@webPrice, isApproved=@isApproved, isActive=@isActive, supplierID=@supplierID, vatID=@vatID, updateDate=@updateDate, isLocked=@isLocked, isInStock=@isInStock, ean=@ean WHERE productID=@productID";
objComm.Parameters.Add("@code", SqlDbType.NVarChar, 50).Value = product.Code;
objComm.Parameters.Add("@supplierCode", SqlDbType.NVarChar, 50).Value = product.SupplierCode;
objComm.Parameters.Add("@brandID", SqlDbType.Int).Value = product.Brand.BrandID;
objComm.Parameters.Add("@name", SqlDbType.NVarChar, 50).Value = product.Name;
objComm.Parameters.Add("@description", SqlDbType.NVarChar).Value = product.Description;
objComm.Parameters.Add("@price", SqlDbType.Float).Value = product.Price;
objComm.Parameters.Add("@webPrice", SqlDbType.Float).Value = product.WebPrice;
objComm.Parameters.Add("@isApproved", SqlDbType.Bit).Value = product.IsApproved;
objComm.Parameters.Add("@isActive", SqlDbType.Bit).Value = product.IsActive;
objComm.Parameters.Add("@supplierID", SqlDbType.Int).Value = product.SupplierID;
objComm.Parameters.Add("@vatID", SqlDbType.Int).Value = product.VatID;
objComm.Parameters.Add("@updateDate", SqlDbType.DateTime).Value = product.UpdateDate;
if (product.Specification != string.Empty)
objComm.Parameters.Add("@specification", SqlDbType.NVarChar).Value = product.Specification;
objComm.Parameters.Add("@isLocked", SqlDbType.Bit).Value = product.IsLocked;
objComm.Parameters.Add("@isInStock", SqlDbType.Bit).Value = product.IsInStock;
objComm.Parameters.Add("@ean", SqlDbType.NVarChar, 50).Value = product.Ean;
objComm.Parameters.Add("@productID", SqlDbType.Int).Value = product.ProductID;
status = objComm.ExecuteNonQuery();
if (status > 0)
{
if (product.Attributes != null)
SaveProductAttributes(product.Attributes, product.ProductID);
SaveProductCategories(product.Categories, product.ProductID);
if (product.Images != null)
SaveProductImages(product.Images, product.ProductID);
//else
//DeleteProductImages(product.ProductID);
if (product.Promotion != null)
saveProductPromotion(product.Promotion, product.ProductID);
}
}
catch (SqlException ex)
{
ErrorLog.LogError(ex);
throw new DLException("Error while updating product", ex);
}
}
}
return status;
}
public int SaveProduct(Product product)
{
using (SqlConnection objConn = new SqlConnection(WebConfigurationManager.ConnectionStrings["eshopConnectionString"].ConnectionString))
{
using (SqlCommand objComm = new SqlCommand("INSERT INTO product (code, supplierCode, brandID, name, description, price, webPrice, isApproved, isActive, supplierID, vatID, insertDate, updateDate, specification, isLocked, isInStock, ean) VALUES (@code, @supplierCode, @brandID, @name, @description, @price, @webPrice, @isApproved, @isActive, @supplierID, @vatID, @insertDate, @updateDate, @specification, @isLocked, @isInStock, @ean); SELECT SCOPE_IDENTITY()", objConn))
{
try
{
objConn.Open();
objComm.Parameters.Add("@code", SqlDbType.NVarChar, 50).Value = product.Code;
objComm.Parameters.Add("@supplierCode", SqlDbType.NVarChar, 50).Value = product.SupplierCode;
objComm.Parameters.Add("@brandID", SqlDbType.Int).Value = product.Brand.BrandID;
objComm.Parameters.Add("@name", SqlDbType.NVarChar, 50).Value = product.Name;
objComm.Parameters.Add("@description", SqlDbType.NVarChar).Value = product.Description;
objComm.Parameters.Add("@price", SqlDbType.Float).Value = product.Price;
objComm.Parameters.Add("@webPrice", SqlDbType.Float).Value = product.WebPrice;
objComm.Parameters.Add("@isApproved", SqlDbType.Bit).Value = product.IsApproved;
objComm.Parameters.Add("@isActive", SqlDbType.Bit).Value = product.IsActive;
objComm.Parameters.Add("@supplierID", SqlDbType.Int).Value = product.SupplierID;
objComm.Parameters.Add("@vatID", SqlDbType.Int).Value = product.VatID;
objComm.Parameters.Add("@insertDate", SqlDbType.DateTime).Value = product.InsertDate;
objComm.Parameters.Add("@updateDate", SqlDbType.DateTime).Value = product.UpdateDate;
objComm.Parameters.Add("@specification", SqlDbType.NVarChar).Value = product.Specification;
objComm.Parameters.Add("@isLocked", SqlDbType.Bit).Value = product.IsLocked;
objComm.Parameters.Add("@isInStock", SqlDbType.Bit).Value = product.IsInStock;
objComm.Parameters.Add("@ean", SqlDbType.NVarChar, 50).Value = product.Ean;
product.ProductID = int.Parse(objComm.ExecuteScalar().ToString());
if (product.ProductID > 0)
{
if (product.Attributes != null)
SaveProductAttributes(product.Attributes, product.ProductID);
SaveProductCategories(product.Categories, product.ProductID);
if (product.Images != null)
SaveProductImages(product.Images, product.ProductID);
else
DeleteProductImages(product.ProductID);
if (product.Promotion != null)
saveProductPromotion(product.Promotion, product.ProductID);
}
}
catch (SqlException ex)
{
ErrorLog.LogError(ex);
throw new DLException("Error while saving product", ex);
}
}
}
return product.ProductID;
}
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 };
}
public List<Product> GetProductsForFirstPage(int categoryID, int numberOfProducts, string orderBy)
{
List<Product> products = null;
Product product = null;
using (SqlConnection objConn = new SqlConnection(WebConfigurationManager.ConnectionStrings["eshopConnectionString"].ConnectionString))
{
using (SqlCommand objComm = new SqlCommand("SELECT TOP " + numberOfProducts.ToString() + " product.productID, product.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 LEFT JOIN promotionProduct ON product.productID=promotionProduct.productID LEFT JOIN promotion ON promotionProduct.promotionID=promotion.promotionID INNER JOIN productCategory ON product.productID=productCategory.productID INNER JOIN category ON productCategory.categoryID=category.categoryID WHERE category.categoryID=@categoryID AND isActive=1 AND isApproved=1 AND productImageUrl.sortOrder=1 ORDER BY " + orderBy, objConn))
{
objConn.Open();
objComm.Parameters.Add("@categoryID", SqlDbType.Int).Value = categoryID;
using (SqlDataReader reader = objComm.ExecuteReader())
{
if (reader.HasRows)
products = new List<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>();
if (System.IO.File.Exists(HttpContext.Current.Server.MapPath("~/images/" + reader.GetString(7))))
product.Images.Add("/images/" + reader.GetString(7));
else
product.Images.Add("/images/no-image.jpg");
if (Convert.IsDBNull(reader[8]) == false)
{
if (reader.GetDateTime(10) <= DateTime.Now && reader.GetDateTime(11) >= DateTime.Now)
{
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));
product.Description = GetProductAttributeValues(product.ProductID, true);
products.Add(product);
}
}
}
}
return products;
}
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";
objComm.CommandText += " AND productCategory.categoryID=@categoryID";
objComm.Parameters.Add("@categoryID", SqlDbType.Int).Value = categoryID;
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>();
if (System.IO.File.Exists(HttpContext.Current.Server.MapPath("~/images/" + reader.GetString(7))))
product.Images.Add("/images/" + reader.GetString(7));
else
product.Images.Add("/images/no-image.jpg");
if (!Convert.IsDBNull(reader[8]))
{
if (reader.GetDateTime(10) < DateTime.Now && reader.GetDateTime(11) > DateTime.Now)
{
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));
product.Description = GetProductAttributeValues(product.ProductID, true);
products.Add(product);
}
}
}
}
return products;
}
public List<Product> SearchProducts(string search, string sort)
{
List<Product> products = new List<Product>();
using (SqlConnection objConn = new SqlConnection(WebConfigurationManager.ConnectionStrings["eshopConnectionString"].ConnectionString))
{
using (SqlCommand objComm = new SqlCommand("product_search", objConn))
{
DataTable searchTable = new DataTable();
searchTable.Columns.Add("search");
DataRow newRow;
foreach (string searchItem in search.Split(' '))
{
newRow = searchTable.NewRow();
newRow["search"] = searchItem;
searchTable.Rows.Add(newRow);
}
objConn.Open();
objComm.CommandType = CommandType.StoredProcedure;
//objComm.Parameters.Add("@search", SqlDbType.NVarChar, 50).Value = search;
objComm.Parameters.AddWithValue("@search", searchTable);
objComm.Parameters.Add("@sort", SqlDbType.NVarChar, 50).Value = sort;
objComm.Parameters[0].SqlDbType = SqlDbType.Structured;
using(SqlDataReader reader = objComm.ExecuteReader())
{
while (reader.Read())
{
Product 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(reader.GetInt32(6), reader.GetString(7));
product.Images = new List<string>();
string directory = createImageUrl(reader.GetString(8));
if (System.IO.File.Exists(HttpContext.Current.Server.MapPath("~" + directory)))
{
product.Images.Add(directory);
}
else
product.Images.Add("/images/no-image.jpg");
if (!Convert.IsDBNull(reader[9]))
{
if (reader.GetDateTime(11) < DateTime.UtcNow && reader.GetDateTime(12) > DateTime.UtcNow)
{
product.Promotion = new Promotion();
product.Promotion.Price = reader.GetDouble(9);
product.Promotion.ImageUrl = reader.GetString(10);
}
}
product.Categories = new List<Category>();
product.Categories.Add(new CategoryDL().GetCategory(reader.GetInt32(13)));
products.Add(product);
}
}
}
}
return products;
}
public int SaveProduct(Product product)
{
ProductDL productDL = new ProductDL();
if (product.ProductID > 0)
{
product.UpdateDate = DateTime.Now.ToUniversalTime();
return productDL.UpdateProduct(product);
}
else
{
product.InsertDate = DateTime.Now.ToUniversalTime();
product.UpdateDate = DateTime.Now.ToUniversalTime();
return productDL.SaveProduct(product);
}
}
public bool SaveProductFromExternalApplication(string barcode, string name, double quantity, double price)
{
Product product = new ProductDL().GetProduct(-1, string.Empty, false, barcode);
if(product != null)
{
product.Name = name;
product.Price = price;
product.WebPrice = price;
product.IsInStock = quantity > 0;
}
else
{
product = new Product();
product.Code = barcode;
product.Name = name;
product.Price = price;
product.WebPrice = price;
product.Brand = new Brand(0, "Nepoznat");
product.Categories = new List<Category>();
product.Categories.Add(new Category(0, "Nepoznat", null, string.Empty, string.Empty, 0, 0, 0, string.Empty, -1));
product.Description = string.Empty;
product.Ean = string.Empty;
product.Images = new List<string>();
product.Images.Add("000.jpg");
product.IsActive = false;
product.IsApproved = false;
product.IsInStock = quantity > 0;
product.IsLocked = false;
product.Specification = string.Empty;
product.SupplierCode = string.Empty;
product.SupplierID = 0;
product.VatID = 4;
}
int status = SaveProduct(product);
return status > 0;
}
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 List<Product> GetProductsForPromotion(int promotionID)
{
List<Product> products = null;
Product product=null;
using (SqlConnection objConn = new SqlConnection(WebConfigurationManager.ConnectionStrings["eshopConnectionString"].ConnectionString))
{
using (SqlCommand objComm = new SqlCommand("SELECT product.productID, product.code, product.name, product.description, product.price, webPrice, brand.name, productImageUrl.imageUrl, promotionProduct.price, promotion.imageUrl, category.name, category.categoryID FROM product INNER JOIN brand ON product.brandID=brand.brandID INNER JOIN productImageUrl ON product.productID=productImageUrl.productID INNER JOIN promotionProduct ON product.productID=promotionProduct.productID INNER JOIN promotion ON promotionProduct.promotionID=promotion.promotionID INNER JOIN productCategory ON productCategory.productID=product.productID INNER JOIN category ON productCategory.categoryID=category.categoryID WHERE promotion.promotionID=@promotionID AND isActive=1 AND isApproved=1 AND productImageUrl.sortOrder=1 ORDER BY product.name", objConn))
{
objConn.Open();
objComm.Parameters.Add("@promotionID", SqlDbType.Int).Value = promotionID;
using (SqlDataReader reader = objComm.ExecuteReader())
{
if (reader.HasRows)
products = new List<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>();
if (System.IO.File.Exists(HttpContext.Current.Server.MapPath("~/images/" + reader.GetString(7))))
product.Images.Add("/images/" + reader.GetString(7));
else
product.Images.Add("/images/no-image.jpg");
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(reader.GetInt32(11), reader.GetString(10), -1, string.Empty, string.Empty, 0, 0, 0, string.Empty));
products.Add(product);
}
}
}
}
return products;
}
private void saveProduct()
{
//main data
Product product = new Product();
product.Name = txtName.Text;
product.Code = txtCode.Text;
product.SupplierCode = txtSupplierCode.Text;
product.Brand = new Brand();
product.Brand.BrandID = int.Parse(cmbBrand.SelectedValue);
product.Description = txtDescription.Text;
product.Price = double.Parse(txtPrice.Text);
product.WebPrice = double.Parse(txtWebPrice.Text);
product.VatID = int.Parse(cmbVat.SelectedValue);
//product.InsertDate = product.UpdateDate = DateTime.Now;
product.SupplierID = int.Parse(cmbSupplier.SelectedValue);
product.IsApproved = chkApproved.Checked;
product.IsActive = chkActive.Checked;
product.IsLocked = chkLocked.Checked;
product.IsInStock = chkInStock.Checked;
product.Ean = txtEan.Text;
product.Specification = txtSpecification.Text;
product.ProductID = (lblProductID.Value != string.Empty) ? int.Parse(lblProductID.Value) : 0;
if (cmbPromotions.SelectedIndex > 0)
{
product.Promotion = new Promotion();
product.Promotion.PromotionID = int.Parse(cmbPromotions.SelectedValue);
product.Promotion.Price = double.Parse(txtPromotionPrice.Text);
}
//category and attributes
if (cmbCategory.SelectedIndex > -1)
{
product.Categories = new List<Category>();
product.Categories.Add(new Category(int.Parse(cmbCategory.SelectedValue), cmbCategory.SelectedItem.Text, 0, string.Empty, string.Empty, 0, 0, 0, string.Empty, -1));
product.Attributes = new List<AttributeValue>();
//foreach (object obj in TabContainer1.Controls)
//{
//if (obj is AjaxControlToolkit.TabPanel)
//{
//AjaxControlToolkit.TabPanel tabPanel = obj as AjaxControlToolkit.TabPanel;
//if (tabPanel.ID == "tbpCategories")
//{
foreach (object control in pnlAttributes.Controls)
if (control is customControls.AttributeControl)
{
//Control c = tpControl as Control;
//foreach (object innerCtrl in c.Controls)
//{
//if (innerCtrl is DropDownList)
//if (((DropDownList)tpControl).ID != "cmbCategory")
product.Attributes.Add(new AttributeValue(((customControls.AttributeControl)control).AttributeValueID, ((customControls.AttributeControl)control).AttributeValue, -1, 0, string.Empty, 0));
//}
}
//}
//}
//}
}
//images
if (rptImages.Items.Count > 0)
{
product.Images = new List<string>();
List<string> images = (List<string>)ViewState["images"];
foreach (string imageUrl in images)
{
product.Images.Add(imageUrl);
}
}
ProductBL productBL = new ProductBL();
string productID = productBL.SaveProduct(product).ToString();
if (lblProductID.Value == "")
lblProductID.Value = productID;
}
public bool SaveProduct(string supplierCode, bool isApproved, bool isActive, int categoryID)
{
DataTable eweProduct = new EweDL().GetProductBySupplierCode(supplierCode);
Category category = new CategoryBL().GetCategory(categoryID);
List<eshopBE.Attribute> attributes = new AttributeBL().GetAttributesForCategory(categoryID);
Product product = new Product();
product.IsApproved = isApproved;
product.IsActive = isActive;
product.SupplierID = 1;
product.SupplierCode = supplierCode;
product.VatID = 4;
product.Categories = new List<Category>();
product.Categories.Add(category);
product.Specification = specificationToHtml(eweProduct.Rows[0]["specification"].ToString());
product.IsInStock = true;
bool isNew = false;
bool isLocked = false;
product.Code = product.SupplierCode;
product.Description = string.Empty;
product.ProductID = new ProductBL().GetProductIDBySupplierCode(product.SupplierCode);
if (product.ProductID <= 0)
isNew = true;
isLocked = new ProductBL().IsLocked(product.ProductID);
Brand brand = new BrandBL().GetBrandByName(eweProduct.Rows[0]["brand"].ToString());
if(brand == null)
{
brand = new Brand();
brand.Name = eweProduct.Rows[0]["brand"].ToString();
brand.BrandID = new BrandBL().SaveBrand(brand);
}
if (product.Brand == null)
product.Brand = new Brand();
product.Brand.BrandID = brand.BrandID;
product.Name = eweProduct.Rows[0]["name"].ToString();
product.Price = calculatePrice(double.Parse(eweProduct.Rows[0]["price"].ToString()), category.PricePercent);
product.WebPrice = calculatePrice(double.Parse(eweProduct.Rows[0]["price"].ToString()), category.WebPricePercent);
product.Ean = eweProduct.Rows[0]["ean"].ToString();
product.SupplierPrice = double.Parse(eweProduct.Rows[0]["price"].ToString());
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.LoadXml(eweProduct.Rows[0]["images"].ToString());
XmlNode xmlImagesNode = xmlDoc.SelectSingleNode("images");
foreach (XmlNode xmlImageNode in xmlImagesNode.ChildNodes)
{
if (product.Images == null)
product.Images = new List<string>();
if(xmlImageNode.Name == "image")
{
if (saveImageFromUrl(xmlImageNode.InnerText.Trim()))
product.Images.Add(Path.GetFileName(xmlImageNode.InnerText.Trim()));
}
}
int attributeID;
if(eweProduct.Rows[0]["specification"].ToString() != string.Empty) {
xmlDoc.LoadXml(eweProduct.Rows[0]["specification"].ToString());
XmlNode xmlSpecificationsNode = xmlDoc.SelectSingleNode("specifications");
foreach(XmlNode xmlSpecificationNode in xmlSpecificationsNode.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);
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));
}
}
}
}
}
if (!isLocked)
if (new ProductBL().SaveProduct(product) > 0)
return true;
return false;
}
