public static int addProductData(String name, String code, String specs, float price, int typeId, int items, int filterValueId, float?offer)
{
using (DB_entities db = new DB_entities())
{
product prod = new product();
prod.name = name;
prod.code = code;
prod.specs = specs;
prod.price = price;
prod.prodtype_id = typeId;
prod.items = items;
if (offer.HasValue)
{
prod.offer = offer;
}
try
{
db.products.Add(prod);
db.SaveChanges();
return(prod.id);
}
catch (Exception ex)
{
Log.error("addProductData - Insert.cs", DateTime.Now, ex);
return(0);
}
}
}
public static void insertReview(int productId, String name, String review, int stars)
{
using (DB_entities db = new DB_entities())
{
using (TransactionScope scope = new TransactionScope())
{
review rev = new review();
rev.name = name;
rev.product_id = productId;
rev.stars = stars;
rev.comment = review;
try
{
db.reviews.Add(rev);
db.SaveChanges();
}
catch (Exception ex)
{
Log.error("insertReview - Insert.cs", DateTime.Now, ex);
}
scope.Complete();
}
}
}
public static List <object> getOrderProds(int id)
{
using (DB_entities db = new DB_entities())
{
try
{
var result = from c in db.product_list
join p in db.products on c.product_id equals p.id
where c.order_id == id
select new
{
totalPrice = c.price,
items = c.no_items,
prodId = p.id,
name = p.name,
pic = p.pics.FirstOrDefault().pic_path
};
return(result.ToList <object>());
}
catch (Exception ex)
{
Log.error("getOrderProds - get.cs", DateTime.Now, ex);
return(null);
}
}
}
public static void addUser(String first_name, String last_name, String email, String password)
{
using (DB_entities db = new DB_entities())
{
using (TransactionScope scope = new TransactionScope())
{
user usr = new user();
usr.first_name = first_name;
usr.last_name = last_name;
usr.password = password;
usr.email = email;
usr.privileges = 2;
usr.location = "";
usr.phone = "";
try
{
db.users.Add(usr);
db.SaveChanges();
}
catch (Exception ex)
{
Log.error("addUser - Insert.cs", DateTime.Now, ex);
}
scope.Complete();
}
}
}
public static void addSubcategories(int categoryId, String names)
{
String[] nameList = names.Split(';');
using (DB_entities db = new DB_entities())
{
using (TransactionScope scope = new TransactionScope())
{
foreach (String name in nameList)
{
if (name == "")
{
break;
}
subcategory subcateg = new subcategory();
subcateg.name = name;
subcateg.category_id = categoryId;
try
{
db.subcategories.Add(subcateg);
db.SaveChanges();
}
catch (Exception ex)
{
Log.error("addSubcategories - Insert.cs", DateTime.Now, ex);
}
}
scope.Complete();
}
}
}
public static object getOrderInfo(int id)
{
using (DB_entities db = new DB_entities())
{
try
{
var result = from o in db.orders
join s in db.shippings on o.shipping_id equals s.id
join sts in db.order_status on o.status_id equals sts.id
where o.id == id
select new
{
first_name = o.first_name,
last_name = o.last_name,
location = o.location,
phone = o.phone,
email = o.email,
date = "" + o.dt.Day + "-" + o.dt.Month + "-" + o.dt.Year + " " + o.dt.Hour + ":" + o.dt.Minute,
status = sts.name,
shipping = s.name,
shippingCost = s.cost,
total = o.total
};
return(result.FirstOrDefault());
}
catch (Exception ex)
{
Log.error("getOrderInfo - get.cs", DateTime.Now, ex);
return(null);
}
}
}
public static void addTypes(int subcategoryId, String names)
{
String[] nameList = names.Split(';');
using (DB_entities db = new DB_entities())
{
using (TransactionScope scope = new TransactionScope())
{
foreach (String name in nameList)
{
if (name == "")
{
break;
}
product_type prodType = new product_type();
prodType.name = name;
prodType.subcategory_id = subcategoryId;
try
{
db.product_type.Add(prodType);
db.SaveChanges();
}
catch (Exception ex)
{
Log.error("addTypes - Insert.cs", DateTime.Now, ex);
}
}
scope.Complete();
}
}
}
public static object getProductDetailsByType(int id)
{
using (DB_entities db = new DB_entities())
{
try
{
var result = (from t in db.product_type
join s in db.subcategories on t.subcategory_id equals s.id
join c in db.categories on s.category_id equals c.id
where t.id == id
select new {
CategoryName = c.name,
CategoryId = c.id,
SubcategoryName = s.name,
SubcategoryId = s.id,
TypeName = t.name,
TypeId = t.id
}).FirstOrDefault();
return(result);
}
catch (Exception ex)
{
Log.error("getProductDetailsByType - get.cs", DateTime.Now, ex);
return(null);
}
}
}
public static int checkFavorite(int productId, int userId)
{
using (DB_entities db = new DB_entities())
{
try
{
var result = from e in db.userFavorites
where e.user_id == userId && e.product_id == productId
orderby e.id descending
select e;
bool exists = result.Count() > 0;
if (exists)
{
return(1);
}
else
{
return(0);
}
}
catch (Exception ex)
{
Log.error("checkFavorite - get.cs", DateTime.Now, ex);
return(-1);
}
}
}
public static void updateUser(int id, String first_name, String last_name, String address, String phone, String email)
{
using (DB_entities db = new DB_entities())
{
using (TransactionScope scope = new TransactionScope())
{
var user = (from e in db.users where e.id == id orderby e.id ascending select e).FirstOrDefault();
user.first_name = first_name;
user.last_name = last_name;
user.location = address;
user.phone = phone;
user.email = email;
try
{
db.SaveChanges();
}
catch (Exception ex)
{
Log.error("updateUser - Update.cs", DateTime.Now, ex);
}
scope.Complete();
}
}
}
public static int checkEmail(String email)
{
using (DB_entities db = new DB_entities())
{
try
{
var result = from e in db.users
where e.email == email
orderby e.id descending
select e;
bool exists = result.Count() > 0;
if (exists)
{
return(1);
}
else
{
return(0);
}
}
catch (Exception ex)
{
Log.error("checkEmail - get.cs", DateTime.Now, ex);
return(-1);
}
}
}
public static void updateProduct(int id, int type, int[] filters, String name,
String code, String specs, float price, float offer, int items, String[] uploadedImages)
{
using (DB_entities db = new DB_entities())
{
using (TransactionScope scope = new TransactionScope())
{
var prod = (from p in db.products where p.id == id select p).FirstOrDefault();
prod.name = name;
prod.code = code;
prod.specs = specs;
prod.price = price;
prod.offer = offer;
prod.items = items;
prod.prodtype_id = type;
try
{
db.SaveChanges();
var picsDel = from p in db.pics where p.product_id == id select p;
foreach (var item in picsDel)
{
db.pics.Remove(item);
}
pic pics = new pic();
for (int i = 0; i < uploadedImages.Length; i++)
{
pics.pic_path = uploadedImages[i];
pics.product_id = id;
db.pics.Add(pics);
db.SaveChanges();
}
var filterDel = from p in db.product_filters where p.product_id == id select p;
foreach (var item in filterDel)
{
db.product_filters.Remove(item);
}
product_filters filterList = new product_filters();
for (int i = 0; i < filters.Length; i++)
{
filterList.value_id = filters[i];
filterList.product_id = id;
db.product_filters.Add(filterList);
db.SaveChanges();
}
db.SaveChanges();
}
catch (Exception ex)
{
Log.error("updateProduct - Update.cs", DateTime.Now, ex);
}
scope.Complete();
}
}
}
public static void addFilterValues(int filterId, String values)
{
String[] valueList = values.Split(';');
using (DB_entities db = new DB_entities())
{
using (TransactionScope scope = new TransactionScope())
{
foreach (String value in valueList)
{
if (value == "")
{
break;
}
filter_values filterVal = new filter_values();
filterVal.value = value;
filterVal.filter_id = filterId;
try
{
db.filter_values.Add(filterVal);
db.SaveChanges();
}
catch (Exception ex)
{
Log.error("addFilterValues - Insert.cs", DateTime.Now, ex);
}
}
scope.Complete();
}
}
}
public static void addFilters(int typeId, String names)
{
String[] nameList = names.Split(';');
using (DB_entities db = new DB_entities())
{
using (TransactionScope scope = new TransactionScope())
{
foreach (String name in nameList)
{
if (name == "")
{
break;
}
filter prodFilter = new filter();
prodFilter.name = name;
prodFilter.prodtype_id = typeId;
try
{
db.filters.Add(prodFilter);
db.SaveChanges();
}
catch (Exception ex)
{
Log.error("addFilters - Insert.cs", DateTime.Now, ex);
}
}
scope.Complete();
}
}
}
public static void addProduct(int type, int[] filters, String name,
String code, String specs, float price, float offer, int items, String[] uploadedImages)
{
using (DB_entities db = new DB_entities())
{
using (TransactionScope scope = new TransactionScope())
{
product prod = new product();
prod.name = name;
prod.code = code;
prod.specs = specs;
prod.price = price;
prod.offer = offer;
prod.items = items;
prod.prodtype_id = type;
try
{
db.products.Add(prod);
db.SaveChanges();
int productId = prod.id;
pic pics = new pic();
for (int i = 0; i < uploadedImages.Length; i++)
{
pics.pic_path = uploadedImages[i];
pics.product_id = productId;
db.pics.Add(pics);
db.SaveChanges();
}
product_filters filterList = new product_filters();
for (int i = 0; i < filters.Length; i++)
{
filterList.value_id = filters[i];
filterList.product_id = productId;
db.product_filters.Add(filterList);
db.SaveChanges();
}
db.SaveChanges();
}
catch (Exception ex)
{
Log.error("addProduct - Insert.cs", DateTime.Now, ex);
}
scope.Complete();
}
}
}
public static List <Product> returnCart(String[][] productList)
{
List <Product> prods = new List <Product>();
using (var db = new DB_entities())
{
int[] prodIds = new int[productList.Length];
for (int i = 0; i < productList.Length; i++)
{
prodIds[i] = Convert.ToInt32(productList[i][0]);
}
try
{
var query = from a in db.products
where prodIds.Contains(a.id)
orderby a.id descending
select a;
foreach (var item in query)
{
Product prod = new Product();
prod.id = item.id;
prod.name = item.name;
prod.code = item.code;
prod.price = item.price;
prod.offer = item.offer;
prod.specs = item.specs;
prod.items = item.items;
List <pic> pictures = db.pics.Where(x => x.product_id == prod.id).ToList();
int i = 0;
prod.pics = new string[15];
foreach (var picture in pictures)
{
prod.pics[i] = picture.pic_path;
i++;
}
prods.Add(prod);
}
}
catch (Exception ex)
{
Log.error("returnCart - Homepage.cs", DateTime.Now, ex);
return(null);
}
}
return(prods);
}
public static List <category> getCategories()
{
using (DB_entities db = new DB_entities())
{
try
{
return(db.categories.ToList());
}
catch (Exception ex)
{
Log.error("getCategories - get.cs", DateTime.Now, ex);
return(null);
}
}
}
public static string getValue()
{
using (var db = new DB_entities())
{
var query = from b in db.categories
select b;
string result = "response: ";
foreach (var item in query)
{
result += item.name + " ";
}
return(result);
}
}
public static List <subcategory> getAllSubcategories()
{
using (DB_entities db = new DB_entities())
{
try
{
var result = from e in db.subcategories orderby e.category_id ascending select e;
return(result.ToList());
}
catch (Exception ex)
{
Log.error("getAllSubcategories - get.cs", DateTime.Now, ex);
return(null);
}
}
}
public static user getCustomer(int id)
{
using (DB_entities db = new DB_entities())
{
try
{
var result = from e in db.users where e.id == id orderby e.id ascending select e;
return(result.FirstOrDefault());
}
catch (Exception ex)
{
Log.error("getCustomer - get.cs", DateTime.Now, ex);
return(null);
}
}
}
public static List <subcategory> getSubcategoryByID(int id)
{
using (DB_entities db = new DB_entities())
{
try
{
var result = from e in db.subcategories where e.id == id select e;
return(result.ToList());
}
catch (Exception ex)
{
Log.error("getSubcategoryByID - get.cs", DateTime.Now, ex);
return(null);
}
}
}
public static List <product_type> getTypes(int subcategoryId)
{
using (DB_entities db = new DB_entities())
{
try
{
var result = from e in db.product_type where e.subcategory_id == subcategoryId select e;
return(result.ToList());
}
catch (Exception ex)
{
Log.error("getTypes - get.cs", DateTime.Now, ex);
return(null);
}
}
}
public static List <filter_values> getFilterValues(int filterId)
{
using (DB_entities db = new DB_entities())
{
try
{
var result = from e in db.filter_values where e.filter_id == filterId select e;
return(result.ToList());
}
catch (Exception ex)
{
Log.error("getFilterValues - get.cs", DateTime.Now, ex);
return(null);
}
}
}
public static List <Product> getProductOffersSub(int subcategoryId)
{
List <Product> prods = new List <Product>();
using (var db = new DB_entities())
{
try
{
var query = from a in db.products
join b in db.product_type on a.prodtype_id equals b.id
join c in db.subcategories on b.subcategory_id equals c.id
where c.id == subcategoryId && a.offer > 0
orderby a.id descending
select a;
foreach (var item in query)
{
Product prod = new Product();
prod.id = item.id;
prod.name = item.name;
prod.code = item.code;
prod.price = item.price;
prod.offer = item.offer;
prod.specs = item.specs;
prod.items = item.items;
List <pic> pictures = db.pics.Where(x => x.product_id == prod.id).ToList();
int i = 0;
prod.pics = new string[15];
foreach (var picture in pictures)
{
prod.pics[i] = picture.pic_path;
i++;
}
prods.Add(prod);
}
}
catch (Exception ex)
{
Log.error("getProductOffersSub(subcategoryId) - Homepage.cs", DateTime.Now, ex);
return(null);
}
}
return(prods);
}
public static List <object> getAllOrders()
{
using (DB_entities db = new DB_entities())
{
try
{
var result = from o in db.orders
join s in db.order_status on o.status_id equals s.id
select new { id = o.id, dt = "" + o.dt.Day + "-" + o.dt.Month + "-" + o.dt.Year + " " + o.dt.Hour + ":" + o.dt.Minute, total = o.total, status = s.name };
return(result.ToList <object>());
}
catch (Exception ex)
{
Log.error("getAllOrders - get.cs", DateTime.Now, ex);
return(null);
}
}
}
public static List <Product> getProductOffers() //without filters (for homepage only)
{
List <Product> prods = new List <Product>();
using (var db = new DB_entities())
{
try
{
var query = from a in db.products
where a.offer > 0
orderby a.id descending
select a;
foreach (var item in query)
{
Product prod = new Product();
prod.id = item.id;
prod.name = item.name;
prod.code = item.code;
prod.price = item.price;
prod.offer = item.offer;
prod.specs = item.specs;
prod.items = item.items;
List <pic> pictures = db.pics.Where(x => x.product_id == prod.id).ToList();
int i = 0;
prod.pics = new string[15];
foreach (var picture in pictures)
{
prod.pics[i] = picture.pic_path;
i++;
}
prods.Add(prod);
}
}
catch (Exception ex)
{
Log.error("getProductOffers - Homepage.cs", DateTime.Now, ex);
return(null);
}
}
return(prods);
}
public static List <review> getReviews(int productId)
{
using (DB_entities db = new DB_entities())
{
try
{
var result = from e in db.reviews
where e.product_id == productId
orderby e.id descending
select e;
return(result.ToList());
}
catch (Exception ex)
{
Log.error("getReviews - get.cs", DateTime.Now, ex);
return(null);
}
}
}
public static user login(String email, String password)
{
using (DB_entities db = new DB_entities())
{
try
{
var result = from e in db.users
where e.email == email && e.password == password
orderby e.id descending
select e;
return(result.FirstOrDefault());
}
catch (Exception ex)
{
Log.error("login - get.cs", DateTime.Now, ex);
return(null);
}
}
}
public static Product returnProductById(int id)
{
Product prod = new Product();
using (var db = new DB_entities())
{
try
{
IQueryable <product> query;
query = from a in db.products
where a.id == id
select a;
product item = query.FirstOrDefault();
prod.id = item.id;
prod.name = item.name;
prod.code = item.code;
prod.price = item.price;
prod.offer = item.offer;
prod.specs = item.specs;
prod.items = item.items;
prod.prodtype_id = item.prodtype_id;
prod.filteredPrice = item.offer.HasValue && item.price > item.offer && item.offer.Value > 0 ? item.offer.Value : item.price;
List <pic> pictures = db.pics.Where(x => x.product_id == prod.id).ToList();
int i = 0;
prod.pics = new string[15];
foreach (var picture in pictures)
{
prod.pics[i] = picture.pic_path;
i++;
}
}
catch (Exception ex)
{
Log.error("returnProductById - Homepage.cs", DateTime.Now, ex);
return(null);
}
}
return(prod);
}
public static int addPictures(String path, int productId)
{
using (DB_entities db = new DB_entities())
{
pic pics = new pic();
pics.pic_path = path;
pics.product_id = productId;
try
{
db.pics.Add(pics);
db.SaveChanges();
return(pics.id);
}
catch (Exception ex)
{
Log.error("addPictures - Insert.cs", DateTime.Now, ex);
return(0);
}
}
}
