public List<Order> GetOrdersByCustomerEmail(string email)
{
using (var db = new JONEntities())
{
var orders = db.acc_ORDERS.Where(x => x.usr_CUSTOMERS.email == email).ToList();
return mapper.Map<List<acc_ORDERS>, List<Order>>(orders);
}
}
public List<Testimonial> GetRandomTestimonails(int howMany)
{
using (var db = new JONEntities())
{
var testimonialsFromDB = db.usr_TESTIMONIALS.OrderBy(x => Guid.NewGuid()).Take(howMany).ToList();
return mapper.Map<List<usr_TESTIMONIALS>, List<Testimonial>>(testimonialsFromDB);
}
}
public List<Diamond> DiamondsBySearchParameters(DiamondSearchParameters mappedSearchParameters)
{
if (mappedSearchParameters.PriceFrom > mappedSearchParameters.PriceTo && mappedSearchParameters.PriceTo != 0)
{
var temp = mappedSearchParameters.PriceFrom;
mappedSearchParameters.PriceFrom = mappedSearchParameters.PriceTo;
mappedSearchParameters.PriceTo = temp;
}
if (mappedSearchParameters.WeightFrom > mappedSearchParameters.WeightTo && mappedSearchParameters.WeightTo != 0)
{
decimal temp2 = mappedSearchParameters.WeightFrom;
mappedSearchParameters.WeightFrom = mappedSearchParameters.WeightTo;
mappedSearchParameters.WeightTo = temp2;
}
using (var db = new JONEntities())
{
var dbdiamonds = db.v_jd_diamonds
.ExtWhereIn(cond => cond.color, mappedSearchParameters.Color)
.ExtWhereIn(cond => cond.clarity, mappedSearchParameters.Clarity)
.ExtWhereIn(cond => cond.shape, mappedSearchParameters.Shape)
.ExtWhereIn(cond => cond.report, mappedSearchParameters.Report)
.ExtWhereIn(cond => cond.cut, mappedSearchParameters.Cut)
.ExtWhereFromToRangeAndIgnoreZero(x => x.totalprice,mappedSearchParameters.PriceFrom,mappedSearchParameters.PriceTo)
.ExtWhereFromToRangeAndIgnoreZero(x => x.weight, mappedSearchParameters.WeightFrom, mappedSearchParameters.WeightTo)
;
if (mappedSearchParameters.ItemsPerPage == 0)
{
mappedSearchParameters.ItemsPerPage = 10;
}
totalRecords = dbdiamonds.Count();
if (TotalRecords % mappedSearchParameters.ItemsPerPage == 0)
{
lastOporationTotalPages = TotalRecords / mappedSearchParameters.ItemsPerPage;
}
else
{
lastOporationTotalPages = TotalRecords / mappedSearchParameters.ItemsPerPage + 1;
}
if (mappedSearchParameters.OrderBy != null)
{
dbdiamonds = dbdiamonds.OrderBy(mappedSearchParameters.OrderBy.SQLString);
}
var dbdiamondslist = dbdiamonds.Skip((mappedSearchParameters.Page - 1) * mappedSearchParameters.ItemsPerPage).Take(mappedSearchParameters.ItemsPerPage).ToList();
var diamonds = mapper.Map<IList<v_jd_diamonds>, List<Diamond>>(dbdiamondslist);
return diamonds;
}
}
private List<KeyValuePair<string, int>> JewelSubCategoryValuesFromJewelCategory(string[] filterParams)
{
using (var db = new JONEntities())
{
var jewelType = Convert.ToInt32(filterParams[0]);
var subcategories = db.inv_JEWELSUBTYPE_JEWEL.Where(x => x.JEWELTYPE_ID == jewelType).ToList();
var list = subcategories.Select(subcategory => new KeyValuePair<string, int>(subcategory.LANG1_LONGDESCR, subcategory.ID)).ToList();
list.Insert(0, new KeyValuePair<string, int>("All Types",0));
return list;
}
}
public Order GetOrderByOrderNumber(int orderNumber)
{
try
{
using (var db = new JONEntities())
{
var orderdto = db.acc_ORDERS.Where(x => x.OrderNumber == orderNumber).SingleOrDefault();
return mapper.Map<acc_ORDERS, Order>(orderdto);
}
}
catch (Exception ex)
{
throw new Exception("When asked to get the orderid="+ orderNumber + " an error occured:" + ex.Message);
}
}
public MembershipCreateStatus CreateCustomer(Customer customer)
{
var customerdto = mapper.Map<Customer, usr_CUSTOMERS>(customer);
try
{
using (var db = new JONEntities())
{
db.usr_CUSTOMERS.AddObject(customerdto);
db.SaveChanges();
return MembershipCreateStatus.Success;
}
}
catch (Exception ex)
{
return MembershipCreateStatus.ProviderError;
}
}
public void ChangePassword(string email, string oldpassword, string newpassword)
{
try
{
using (var db = new JONEntities())
{
var customer = db.usr_CUSTOMERS.Where(x => x.email == email).SingleOrDefault();
if (customer.password == oldpassword)
{
customer.password = newpassword;
db.SaveChanges();
return;
}
throw new Exception("Your password didn't match our records, please try again");
}
}
catch (Exception ex)
{
throw new Exception("When asked to change your password an error occured\r\n" + ex.Message);
}
}
public MembershipCreateStatus UpdateCustomer(ExtendedCustomer customer)
{
try
{
using (var db = new JONEntities())
{
var existingCustomer = db.usr_CUSTOMERS.Where(x => x.email == customer.Email).SingleOrDefault();
var source = new MergeExistingCustomerAndExtendedCustomer();
source.First = existingCustomer;
source.Second = customer;
var customerdto = mapper.Map<MergeExistingCustomerAndExtendedCustomer, usr_CUSTOMERS>(source);
db.usr_CUSTOMERS.Detach(existingCustomer);
db.usr_CUSTOMERS.Attach(customerdto);
db.ObjectStateManager.ChangeObjectState(customerdto, EntityState.Modified);
// db.usr_CUSTOMERS.AddObject(customerdto);
db.SaveChanges();
return MembershipCreateStatus.Success;
}
}
catch (Exception ex)
{
return MembershipCreateStatus.ProviderError;
}
}
public virtual Jewel GetJewelByID(int jewelryID)
{
using (var db = new JONEntities())
{
var item = GetJewelItemsObjectSet().Where(t => t.id == jewelryID).Where(ParseMetalFilter(requestedJewelMediaTypeByUser)).SingleOrDefault();
if (item != null)
{
return JewelClassFactory(item);
}
return null;
}
}
public virtual List<Jewel> GetJewelsByDynamicSQL(DynamicSQLWhereObject dynamicSQL)
{
var jewelrylist = new List<Jewel>();
using (var db = new JONEntities())
{
var items = db.v_jewel_items.Where(dynamicSQL.Pattern, dynamicSQL.Valuelist.ToArray());
if (HasFilter)
{
items = items.Where(filter.Pattern, filter.Valuelist.ToArray());
}
items = items.Where(ParseMetalFilter(requestedJewelMediaTypeByUser));
foreach (var customfilter in customFilters)
{
if (!customfilter.IsDoingNothing)
{
items = items.Where(customfilter.Pattern, customfilter.Valuelist.ToArray());
}
}
items = items.OrderBy(orderBy.SQLString);
totalItems = items.Count();
items = items.Skip(itemsPerPage*(page - 1)).Take(itemsPerPage);
foreach (var item in items)
{
var jewel = JewelClassFactory(item);
jewelrylist.Add(jewel);
}
currentPage = page;
}
return jewelrylist;
}
public Customer GetCustomerByEmail(string email)
{
try
{
using (var db = new JONEntities())
{
var customer =db.usr_CUSTOMERS.Where(x => x.email == email).SingleOrDefault();
if (customer != null)
{
return mapper.Map<usr_CUSTOMERS, Customer>(customer);
}
throw new Exception("Customer not found");
}
}
catch (Exception ex)
{
throw new Exception("When asked to get a customer with the email=" + email + " an error occured\r\n" + ex.Message);
}
}
public bool ValidateCustomer(string email, string password)
{
if (String.IsNullOrEmpty(email) || String.IsNullOrEmpty(password))
{
return false;
}
try
{
using (var db = new JONEntities())
{
var validatedCustomer =
db.usr_CUSTOMERS.Where(x => x.email == email && x.password == password).SingleOrDefault();
if (validatedCustomer != null)
{
return true;
}
return false;
}
}
catch (Exception ex)
{
throw new Exception("When asked to validate the cutomer using a password and email= " + email + " the following error occured\r\n" + ex.Message);
}
}
public bool ValidateCustomerUsingOrderNumber(string email, string orderNumber)
{
if (String.IsNullOrEmpty(email) || String.IsNullOrEmpty(orderNumber))
{
return false;
}
try
{
using (var db = new JONEntities())
{
var orderNumberForDB = Convert.ToInt32(orderNumber);
var validatedCustomer =
db.v_orders_list.Where(x => x.CustomerEmail == email && x.OrderNumber == orderNumberForDB).
SingleOrDefault();
if (validatedCustomer != null)
{
return true;
}
return false;
}
}
catch (Exception ex)
{
throw new Exception("When asked to validate the cutomer using a password and email= " + email + " the following error occured\r\n" + ex.Message);
}
}
private bool CustomerExists(string email)
{
using (var db = new JONEntities())
{
var count = db.usr_CUSTOMERS.Where(x => x.email == email).Count();
if (count>0)
{
return true;
}
return false;
}
}
public string RecoverPassword(string email)
{
try
{
using (var db = new JONEntities())
{
var customer = db.usr_CUSTOMERS.Where(x => x.email == email).SingleOrDefault();
if (customer != null)
{
return customer.password;
}
throw new Exception("Customer email was not found");
}
}
catch (Exception ex)
{
throw new Exception("When asked to recover password for customer: " + email + " an error occured:\r\n" + ex.Message);
}
}
private usr_CUSTOMERS CustomerFromEmail(string email)
{
try
{
using (var db = new JONEntities())
{
var customer = db.usr_CUSTOMERS.Where(x => x.email == email).SingleOrDefault();
if (customer != null)
{
return customer;
}
return null;
}
}
catch (Exception ex)
{
throw new Exception("When asked to get the id of a customer:" + email + " an error occured:\r\n" + ex.Message);
}
}
public int Save(Order orderdto)
{
using (var db = new JONEntities())
{
var totalPrice = orderdto.TotalPrice;
var customer = mapper.Map<Order, usr_CUSTOMERS>(orderdto);
if (CustomerExists(customer.email))
{
var customerExists = CustomerFromEmail(orderdto.Email);
customer.password = customerExists.password;
customer.id = customerExists.id;
db.usr_CUSTOMERS.Attach(customer);
db.ObjectStateManager.ChangeObjectState(customer, EntityState.Modified);
}
else
{
customer.password = Guid.NewGuid().ToString().Substring(0, 8);
db.usr_CUSTOMERS.AddObject(customer);
}
// ObjectSet<acc_ORDERS> orderset = new ObjectSet<acc_ORDERS>();
var nextOrderNumber = db.acc_ORDERS.Count() + 1;
var order = new acc_ORDERS
{
user_id = customer.id,
OrderNumber = nextOrderNumber+10000,
orderdate = DateTime.Now,
campaign = "notset",
affiliate = "notset",
referrer = "notset",
remote_ip = "notset",
order_transacted = true,
InvoiceNumber = 1,
InvoiceCopy = true,
InvoiceDate = DateTime.Now,
JewelrySize = "",
ClubOrder = false,
packaging_id = 1,
payment_id = orderdto.PaymentID,
shipping_id = 1,
shipping_tracking_no = "",
amnt_discount = 0,
amnt_extracharges = 0,
amnt_grandtotal = totalPrice,
amnt_items = totalPrice,
amnt_labor = 0,
amnt_shipping = 0,
amnt_subtotal = totalPrice,
amnt_vat = 0,
amnt_wrapping = 0,
adrs_billing_firstname =
orderdto.BillingAddress.FirstName,
adrs_billing_lastname = orderdto.BillingAddress.LastName,
adrs_billing_city = orderdto.BillingAddress.City,
adrs_billing_phone = orderdto.BillingAddress.Phone,
adrs_billing_state_id = orderdto.BillingAddress.StateID,
adrs_billing_street =
orderdto.BillingAddress.Address1,
adrs_billing_zip = orderdto.BillingAddress.ZipCode,
adrs_delivery_country_id = orderdto.ShippingAddress.CountryID,
adrs_delivery_firstname =
orderdto.ShippingAddress.FirstName,
adrs_delivery_lastname= orderdto.ShippingAddress.LastName,
adrs_delivery_city = orderdto.ShippingAddress.City,
adrs_delivery_phone = orderdto.ShippingAddress.Phone,
adrs_delivery_state_id = orderdto.ShippingAddress.StateID,
adrs_delivery_street =
orderdto.ShippingAddress.Address1,
adrs_delivery_zip = orderdto.ShippingAddress.ZipCode,
adrs_billing_country_id = orderdto.BillingAddress.CountryID,
cannot_be_edited = false,
OrderDeleted = false,
Customer_Notes = orderdto.Comment ?? String.Empty,
sts1_new_order_received = true,
sts1_new_order_received_date = DateTime.Now,
sts1_new_order_received_viewed = false,
sts2_waiting_for_authorization = false,
sts2_waiting_for_authorization_date = DateTime.Now,
sts2_waiting_for_authorization_note = "",
sts2_waiting_for_authorization_viewed = false,
sts3_waiting_for_payment = false,
sts3_waiting_for_payment_date = DateTime.Now,
sts3_waiting_for_payment_note = "",
sts3_waiting_for_payment_viewed = false,
sts4_order_confirmed = false,
sts4_order_confirmed_date = DateTime.Now,
sts4_order_confirmed_note = "",
sts4_order_confirmed_viewed = false,
sts5_partial_order_confirmed = false,
sts5_partial_order_confirmed_date = DateTime.Now,
sts5_partial_order_confirmed_note = "",
sts5_partial_order_confirmed_viewed = false,
sts6_order_failed = false,
sts6_order_failed_date = DateTime.Now,
sts6_order_failed_note = "",
sts6_order_failed_viewed = false,
sts7_order_waiting_to_be_send = false,
sts7_order_waiting_to_be_send_date = DateTime.Now,
sts7_order_waiting_to_be_send_note = "",
sts7_order_waiting_to_be_send_viewed = false,
sts8_order_send = false,
sts8_order_send_date = DateTime.Now,
sts8_order_send_note = "",
sts8_order_send_viewed = false,
sts9_partial_order_send = false,
sts9_partial_order_send_date = DateTime.Now,
sts9_partial_order_send_note = "",
sts9_partial_order_send_viewed = false,
sts10_order_received_by_customer = false,
sts10_order_received_by_customer_date = DateTime.Now,
sts10_order_received_by_customer_note = "",
sts10_order_received_by_customer_viewed = false,
sts11_partial_order_received_by_customer = false,
sts11_partial_order_received_by_customer_date = DateTime.Now,
sts11_partial_order_received_by_customer_note = "",
sts11_partial_order_received_by_customer_viewed = false,
sts12_customer_returning_order = false,
sts12_customer_returning_order_date = DateTime.Now,
sts12_customer_returning_order_note = "",
sts12_customer_returning_order_viewed = false,
sts13_customer_returning_part_order = false,
sts13_customer_returning_part_order_date = DateTime.Now,
sts13_customer_returning_part_order_note = "",
sts13_customer_returning_part_order_viewed = false,
sts14_customer_refunded = false,
sts14_customer_refunded_date = DateTime.Now,
sts14_customer_refunded_note = "",
sts14_customer_refunded_viewed = false,
sts15_customer_partly_refunded = false,
sts15_customer_partly_refunded_date = DateTime.Now,
sts15_customer_partly_refunded_note = "",
sts15_customer_partly_refunded_viewed = false,
sts16_order_closed = false,
sts16_order_closed_date = DateTime.Now,
sts16_order_closed_note = "",
sts16_order_closed_viewed = false,
sts17_order_cancelled = false,
sts17_order_cancelled_date = DateTime.Now,
sts17_order_cancelled_note = "",
sts17_order_cancelled_viewed = false,
sts_curr_stat = "",
sts_curr_date = DateTime.Now,
Interest_start_date = DateTime.Now,
Interest_percent = 0,
Merchant_Notes = "",
LastModify_Date = DateTime.Now,
LastModify_User= "",
LastModify_User_Id = customer.id,
order_currency = "USD",
order_currency_rate = 1,
include_receipt = false,
hear_fromus = ""
};
db.acc_ORDERS.AddObject(order);
foreach (var item in orderdto.Items)
{
switch (item.Type)
{
case CartItemType.Jewelry:
{
var cartItem = item as JewelCartItem;
var orderItem = new acc_JEWELRY_ORDER_ITEMS()
{
Item_id = cartItem.ID,
Item_quantity = 1,
description = "",
jewelsize = cartItem.Size,
metal = (int) cartItem.MediaType,
Item_no = "",
OrderNumber = order.OrderNumber
};
db.acc_JEWELRY_ORDER_ITEMS.AddObject(orderItem);
}
break;
case CartItemType.Diamond:
{
var cartItem = item as DiamondCartItem;
var orderItem = new acc_DIAMOND_ORDER_ITEMS()
{
Item_id = cartItem.ID,
Item_quantity = 1,
description = "",
OrderNumber = order.OrderNumber
};
db.acc_DIAMOND_ORDER_ITEMS.AddObject(orderItem);
}
break;
case CartItemType.CustomJewel:
{
var cartItem = item as CustomJewelCartItem;
var orderItem = new acc_CUSTOMJEWEL_ORDER_ITEMS()
{
Setting_id = cartItem.SettingID,
Diamond_id = cartItem.DiamondID,
Item_quantity = 1,
size = cartItem.Size,
metal = (int)cartItem.MediaType,
diamond_description = "",
setting_description = "",
OrderNumber = order.OrderNumber
};
db.acc_CUSTOMJEWEL_ORDER_ITEMS.AddObject(orderItem);
}
break;
default:
throw new ArgumentOutOfRangeException();
}
}
switch (orderdto.PaymentID)
{
case 1:
{
var payment = new acc_CASHFLOW()
{
user_id = customer.id,
payment_type = 1,
LastModify_Date = DateTime.Now,
LastModify_User = customer.email,
LastModify_User_Id = customer.id,
amount_interest = 0,
amount_actual = totalPrice,
amount_costs = 0,
amount_total = totalPrice,
approved = false,
approved_date = DateTime.Now,
cc_batch = "",
cc_cleared = false,
cc_clubmember = false,
cc_confirmation = "",
cc_cvv = orderdto.CreditCard.CCV,
cc_exp_month = orderdto.CreditCard.Month.ToString(),
cc_exp_year = orderdto.CreditCard.Year.ToString(),
cc_name = orderdto.FirstName + " " + orderdto.LastName,
cc_number = orderdto.CreditCard.CreditCardsNumber,
cc_type_id = orderdto.CreditCard.CreditCardID,
cc_user_ssn = "",
cq_account = "",
cq_bank = "",
cq_date = DateTime.Now,
cq_name = "",
master = false,
mt_account = "",
mt_bank = "",
mt_code = "",
mt_name = "",
notes = "",
order_id = order.id,
paypal = false
};
db.acc_CASHFLOW.AddObject(payment);
}
break;
case 2:
{
var payment = new acc_CASHFLOW()
{
payment_type = 2,
LastModify_Date = DateTime.Now,
LastModify_User = customer.email,
LastModify_User_Id = customer.id,
amount_interest = 0,
amount_actual = totalPrice,
amount_costs = 0,
amount_total = totalPrice,
approved = false,
approved_date = DateTime.Now,
cc_batch = "",
cc_cleared = false,
cc_clubmember = false,
cc_confirmation = "",
cc_user_ssn = "",
cq_account = "",
cq_bank = "",
cq_date = DateTime.Now,
cq_name = "",
master = false,
mt_account = "",
mt_bank = "",
mt_code = "",
mt_name = "",
notes = "",
order_id = order.id,
paypal = false,
user_id = customer.id
};
db.acc_CASHFLOW.AddObject(payment);
}
break;
case 3:
{
var payment = new acc_CASHFLOW()
{
payment_type = 3,
LastModify_Date = DateTime.Now,
LastModify_User = customer.email,
LastModify_User_Id = customer.id,
amount_interest = 0,
amount_actual = totalPrice,
amount_costs = 0,
amount_total = totalPrice,
approved = false,
approved_date = DateTime.Now,
cc_batch = "",
cc_cleared = false,
cc_clubmember = false,
cc_confirmation = "",
cc_user_ssn = "",
cq_account = "",
cq_bank = "",
cq_date = DateTime.Now,
cq_name = "",
master = false,
mt_account = "",
mt_bank = "",
mt_code = "",
mt_name = "",
notes = "",
order_id = order.id,
paypal = true,
user_id = customer.id
};
db.acc_CASHFLOW.AddObject(payment);
}
break;
}
db.SaveChanges();
return order.OrderNumber;
}
}
public Diamond GetDiamondByID(int diamondID)
{
using (var db = new JONEntities())
{
var diamond = db.v_jd_diamonds.Where(d => d.diamondid == diamondID).SingleOrDefault();
if (diamond != null)
{
return mapper.Map<v_jd_diamonds, Diamond>(diamond);
}
throw new Exception("Diamond not found is was: " + diamondID.ToString());
}
}