public dynamic MassLoad(HttpPostedFileBase dealers)
{
PartStates state = new PartStates();
List<string> errors = new List<string>();
try {
CurtDevDataContext db = new CurtDevDataContext();
string dir_path = Server.MapPath("/Content/locator_csv");
GoogleMaps map = new GoogleMaps(System.Configuration.ConfigurationManager.AppSettings["GoogleMapsKey"]);
DirectoryInfo dir_info = new DirectoryInfo(dir_path);
int file_count = dir_info.GetFiles().Count();
string file_path = Server.MapPath("/Content/locator_csv/" + (file_count + 1) + ".xlsx");
dealers.SaveAs(file_path);
FileInfo existingFile = new FileInfo(file_path);
using (ExcelPackage package = new ExcelPackage(existingFile)) {
// get the first worksheet in the workbook
ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
int row = 2; //The first row with data
while (worksheet.Cells[row, 2].Value != null) {
// col 1 = Customer ID Code
// col 2 = Customer Name
// col 3 = Website
// col 4 = Location Name
// col 5 = Contact First Name
// col 6 = Contact Last Name
// col 7 = phone
// col 8 = street
// col 9 = city
// col 10 = state
// col 11 = zip
// col 12 = sales rep
// col 13 = Dealer Tier
// col 14 = Dealer Type
// col 15 = Parent curt acct # (meaning a third party client)
try {
int acctnum = (worksheet.Cells[row, 1].Value != null) ? Convert.ToInt32(worksheet.Cells[row, 1].Value) : 0;
string rep = (worksheet.Cells[row, 12].Value != null) ? worksheet.Cells[row, 12].Value.ToString() : "";
string dtier = worksheet.Cells[row, 13].Value.ToString().Trim().ToLower();
string dtype = worksheet.Cells[row, 14].Value.ToString().Trim().ToLower();
string customername = (worksheet.Cells[row, 2].Value != null) ? worksheet.Cells[row, 2].Value.ToString() : "";
string locationname = (worksheet.Cells[row, 4].Value != null) ? worksheet.Cells[row, 4].Value.ToString() : "";
string contact = ((worksheet.Cells[row, 5].Value != null) ? worksheet.Cells[row, 5].Value.ToString() : "") + ((worksheet.Cells[row, 6].Value != null) ? " " + worksheet.Cells[row, 6].Value.ToString() : "");
string website = (worksheet.Cells[row, 3].Value != null) ? worksheet.Cells[row, 3].Value.ToString().Trim() : null;
string statestr = (worksheet.Cells[row, 10].Value != null) ? worksheet.Cells[row, 10].Value.ToString() : "";
string postalCode = (worksheet.Cells[row, 11].Value != null) ? worksheet.Cells[row, 11].Value.ToString() : "";
string address = (worksheet.Cells[row, 8].Value != null) ? worksheet.Cells[row, 8].Value.ToString() : "";
string city = (worksheet.Cells[row, 9].Value != null) ? worksheet.Cells[row, 9].Value.ToString() : "";
string phone = (worksheet.Cells[row, 7].Value != null) ? worksheet.Cells[row, 7].Value.ToString() : "";
string parentacct = (worksheet.Cells[row, 15].Value != null) ? worksheet.Cells[row, 15].Value.ToString() : "";
int stateid = db.PartStates.Where(x => x.abbr.ToLower().Trim() == statestr.ToLower().Trim()).Select(x => x.stateID).FirstOrDefault();
LatLng geo = map.GetLatLng(address, city, stateid);
double latitude = Convert.ToDouble(geo.latitude);
double longitude = Convert.ToDouble(geo.longitude);
SalesRepresentative salesrep = db.SalesRepresentatives.Where(x => x.name.ToLower().Trim().Equals(rep.Trim().ToLower())).FirstOrDefault<SalesRepresentative>();
DealerType type = db.DealerTypes.Where(x => x.type.Trim().ToLower() == dtype).First();
DealerTier tier = db.DealerTiers.Where(x => x.tier.Trim().ToLower() == dtier).First();
Customer customer = new Customer();
if (acctnum != 0) {
customer = (from c in db.Customers
join d in db.DealerTypes on c.dealer_type equals d.dealer_type
where c.customerID.Equals(acctnum) && d.online.Equals(type.online)
select c).FirstOrDefault<Customer>();
} else {
customer = (from c in db.Customers
join d in db.DealerTypes on c.dealer_type equals d.dealer_type
where c.name.ToLower().Equals(customername.Trim().ToLower()) && d.online.Equals(type.online) && c.parentID.Equals(Convert.ToInt32(parentacct))
select c).FirstOrDefault<Customer>();
}
if (customer == null) {
// new customer
Customer new_customer = new Customer {
name = customername,
contact_person = contact,
tier = tier.ID,
dealer_type = type.dealer_type,
};
if (salesrep != null && salesrep.salesRepID != 0) {
new_customer.salesRepID = salesrep.salesRepID;
}
if (acctnum != 0) {
new_customer.customerID = acctnum;
}
if (parentacct.Trim() != "") {
new_customer.parentID = Convert.ToInt32(parentacct);
}
if (website != "") {
new_customer.website = website;
}
db.Customers.InsertOnSubmit(new_customer);
db.SubmitChanges();
if (address != "" && city != "" && postalCode != "") {
CustomerLocation location = new CustomerLocation {
cust_id = new_customer.cust_id,
name = locationname,
address = address,
city = city,
postalCode = postalCode,
phone = phone,
contact_person = contact,
latitude = latitude,
longitude = longitude,
isprimary = true
};
if (stateid != 0) {
location.stateID = stateid;
}
db.CustomerLocations.InsertOnSubmit(location);
}
db.SubmitChanges();
} else {
if (parentacct.Trim() != "") {
customer.parentID = Convert.ToInt32(parentacct);
}
// prior customer
// check for location
customer.dealer_type = type.dealer_type;
customer.tier = tier.ID;
if (salesrep != null && salesrep.salesRepID != 0) {
customer.salesRepID = salesrep.salesRepID;
}
CustomerLocation location = db.CustomerLocations.Where(x => x.address.ToLower() == address.ToLower() && x.postalCode.Trim() == postalCode.Trim() && x.cust_id.Equals(customer.cust_id)).FirstOrDefault<CustomerLocation>();
if (location == null) {
CustomerLocation new_location = new CustomerLocation {
cust_id = customer.cust_id,
name = locationname,
address = address,
city = city,
postalCode = postalCode,
phone = phone,
latitude = latitude,
longitude = longitude,
contact_person = contact
};
if (stateid != 0) {
new_location.stateID = stateid;
}
db.CustomerLocations.InsertOnSubmit(new_location);
db.SubmitChanges();
} else {
// update location
location.contact_person = contact;
location.name = locationname;
location.city = city;
location.phone = phone;
location.postalCode = postalCode;
location.latitude = latitude;
location.longitude = longitude;
db.SubmitChanges();
}
}
} catch (Exception e) {
errors.Add(row + ": " + e.Message);
}
row++;
}
}
if (errors.Count == 0) {
return RedirectToAction("Index", new { msg = "Customer Import Successful" });
} else {
return RedirectToAction("MassUpload", new { msg = "There were some errors", errors = errors });
}
} catch (Exception e) {
return RedirectToAction("MassUpload", new { msg = e.Message });
}
}
/// <summary>
/// Edit the information about a given location
/// </summary>
/// <param name="id">ID of location</param>
/// <returns>View</returns>
public ActionResult EditLocation(int id = 0)
{
string error = "";
#region Form Submission
try {
if (Request.Form["btnSubmit"] != null) {
CurtDevDataContext db = new CurtDevDataContext();
// Save form values
string name = (Request.Form["name"] != null) ? Request.Form["name"] : "";
string email = (Request.Form["email"] != null) ? Request.Form["email"] : "";
string phone = (Request.Form["phone"] != null) ? Request.Form["phone"] : "";
string fax = (Request.Form["fax"] != null) ? Request.Form["fax"] : "";
string address = (Request.Form["address"] != null) ? Request.Form["address"] : "";
string city = (Request.Form["city"] != null) ? Request.Form["city"] : "";
string postalCode = (Request.Form["postalCode"] != null) ? Request.Form["postalCode"] : "";
double latitude = (Request.Form["latitude"] != null) ? Convert.ToDouble(Request.Form["latitude"]) : 0;
double longitude = (Request.Form["longitude"] != null) ? Convert.ToDouble(Request.Form["longitude"]) : 0;
int stateID = (Request.Form["state"] != null) ? Convert.ToInt32(Request.Form["state"]) : 0;
// Validate the form fields
if (name.Length == 0) throw new Exception("Name is required.");
if (phone.Length == 0) throw new Exception("Phone is required.");
if (address.Length == 0) throw new Exception("Address is required.");
if (city.Length == 0) throw new Exception("City is required.");
if (stateID == 0) throw new Exception("State is required.");
if (postalCode.Length == 0) throw new Exception("Postal Code is required.");
// Get the location
CustomerLocation updated_loc = (from cl in db.CustomerLocations
where cl.locationID.Equals(id)
select cl).FirstOrDefault<CustomerLocation>();
GoogleMaps map = new GoogleMaps(System.Configuration.ConfigurationManager.AppSettings["GoogleMapsKey"]);
LatLng geo = map.GetLatLng(address, city, stateID);
if (geo.latitude != "0") {
updated_loc.latitude = Convert.ToDouble(geo.latitude);
updated_loc.longitude = Convert.ToDouble(geo.longitude);
} else {
updated_loc.latitude = latitude;
updated_loc.longitude = longitude;
}
// Create the new customer and save
updated_loc.name = name;
updated_loc.email = email;
updated_loc.phone = phone;
updated_loc.fax = fax;
updated_loc.address = address;
updated_loc.city = city;
updated_loc.postalCode = postalCode;
updated_loc.stateID = stateID;
db.SubmitChanges();
return RedirectToAction("Locations", new { id = updated_loc.cust_id });
}
} catch (Exception e) {
error = e.Message;
}
#endregion
// Get the information for this location
CustomerLocation location = CustomerModel.GetLocation(id);
ViewBag.location = location;
// Get the customer record that we're adding the location to
Customer customer = CustomerModel.Get(location.cust_id);
ViewBag.customer = customer;
// Get the states
List<FullCountry> countries = CustomerModel.GetCountries();
ViewBag.countries = countries;
ViewBag.error = error;
return View();
}
public ActionResult Edit(int id = 0)
{
string error = "";
#region Form Submission
try {
if (Request.Form["btnSubmit"] != null) {
CurtDevDataContext db = new CurtDevDataContext();
// Save form values
string name = (Request.Form["name"] != null) ? Request.Form["name"] : "";
string email = (Request.Form["email"] != null) ? Request.Form["email"] : "";
string phone = (Request.Form["phone"] != null) ? Request.Form["phone"] : "";
string fax = (Request.Form["fax"] != null) ? Request.Form["fax"] : "";
string address = (Request.Form["address"] != null) ? Request.Form["address"] : "";
string address2 = (Request.Form["address2"] != null) ? Request.Form["address2"] : "";
string city = (Request.Form["city"] != null) ? Request.Form["city"] : "";
int stateID = (Request.Form["state"] != null) ? Convert.ToInt32(Request.Form["state"]) : 0;
string postalCode = (Request.Form["postal_code"] != null) ? Request.Form["postal_code"] : "";
string contact = (Request.Form["contact_person"] != null) ? Request.Form["contact_person"] : "";
string website = (Request.Form["website"] != null) ? Request.Form["website"] : "";
string eLocalURL = (Request.Form["eLocalURL"] != null) ? Request.Form["eLocalURL"] : "";
string searchURL = (Request.Form["searchURL"] != null) ? Request.Form["searchURL"] : "";
string logo = (Request.Form["logo"] != null && Request.Form["logo"].Trim() != "") ? Request.Form["logo"] : null;
int dealer_type = (Request.Form["dealer_type"] != null) ? Convert.ToInt32(Request.Form["dealer_type"]) : 0;
int customerID = (Request.Form["customerID"] != null && Request.Form["customerID"] != "") ? Convert.ToInt32(Request.Form["customerID"]) : 0;
int salesRepID = (Request.Form["salesRepID"] != null && Request.Form["salesRepID"] != "") ? Convert.ToInt32(Request.Form["salesRepID"]) : 0;
int mapixCodeID = (Request.Form["mapixCodeID"] != null && Request.Form["mapixCodeID"] != "") ? Convert.ToInt32(Request.Form["mapixCodeID"]) : 0;
int parentID = (Request.Form["parentID"] != null && Request.Form["parentID"] != "") ? Convert.ToInt32(Request.Form["parentID"]) : 0;
int tier = (Request.Form["tier"] != null && Request.Form["tier"] != "") ? Convert.ToInt32(Request.Form["tier"]) : 1;
bool isDummy = (Request.Form["isDummy"] != null && Request.Form["isDummy"] != "") ? Convert.ToBoolean(Request.Form["isDummy"]) : false;
bool showWebsite = (Request.Form["showWebsite"] != null && Request.Form["showWebsite"] != "") ? Convert.ToBoolean(Request.Form["showWebsite"]) : false;
// Validate the form fields
if (name.Length == 0) throw new Exception("Name is required.");
if (customerID == 0 && parentID == 0) throw new Exception("Either a Customer ID or a Parent Customer is required.");
if (dealer_type == 0) throw new Exception("A Dealer Type is required.");
Customer cust = (from c in db.Customers
where c.cust_id.Equals(id)
select c).FirstOrDefault<Customer>();
// Get the geographic position of this customer
GoogleMaps map = new GoogleMaps(System.Configuration.ConfigurationManager.AppSettings["GoogleMapsKey"]);
LatLng location = map.GetLatLng(address, city, stateID);
// Save new values
cust.name = name;
cust.email = email;
cust.phone = phone;
cust.fax = fax;
cust.address = address;
cust.address2 = address2;
cust.city = city;
cust.stateID = stateID;
cust.postal_code = postalCode;
cust.contact_person = contact;
cust.website = website;
cust.eLocalURL = eLocalURL;
cust.searchURL = searchURL;
cust.logo = logo;
cust.dealer_type = dealer_type;
cust.mCodeID = mapixCodeID;
cust.salesRepID = salesRepID;
cust.latitude = location.latitude;
cust.longitude = location.longitude;
cust.isDummy = isDummy;
cust.tier = tier;
cust.showWebsite = showWebsite;
if (customerID != 0) {
cust.customerID = customerID;
}
if (parentID != 0) {
cust.parentID = parentID;
}
db.SubmitChanges();
return RedirectToAction("Index");
}
} catch (Exception e) {
error = e.Message;
}
#endregion
// Get the customer for this id
Customer customer = CustomerModel.Get(id);
ViewBag.customer = customer;
// Get the dealer tiers
List<DealerTier> dealer_tiers = CustomerModel.GetDealerTiers();
ViewBag.dealer_tiers = dealer_tiers;
// Get the dealer types
List<DealerType> dealer_types = CustomerModel.GetDealerTypes();
ViewBag.dealer_types = dealer_types;
// Get the mapix codes
List<MapixCode> mapix_codes = CustomerModel.GetMapixCodes();
ViewBag.mapix_codes = mapix_codes;
// Get the mapix codes
List<SalesRepresentative> sales_reps = CustomerModel.GetSalesReps();
ViewBag.sales_reps = sales_reps;
// Get the states
List<FullCountry> countries = CustomerModel.GetCountries();
ViewBag.countries = countries;
// Get the customer list
List<Customer> customers = CustomerModel.GetAll();
ViewBag.customers = customers;
ViewBag.error = error;
return View();
}
/// <summary>
/// Add a new customer location
/// </summary>
/// <param name="id">ID of the customer we're adding the location for.</param>
/// <returns>View</returns>
public ActionResult AddLocation(int id = 0)
{
string error = "";
#region Form Submission
try {
if (Request.Form["btnSubmit"] != null) {
CurtDevDataContext db = new CurtDevDataContext();
// Save form values
string name = (Request.Form["name"] != null) ? Request.Form["name"] : "";
string email = (Request.Form["email"] != null) ? Request.Form["email"] : "";
string phone = (Request.Form["phone"] != null) ? Request.Form["phone"] : "";
string fax = (Request.Form["fax"] != null) ? Request.Form["fax"] : "";
string address = (Request.Form["address"] != null) ? Request.Form["address"] : "";
string city = (Request.Form["city"] != null) ? Request.Form["city"] : "";
string postalCode = (Request.Form["postalCode"] != null) ? Request.Form["postalCode"] : "";
int stateID = (Request.Form["state"] != null) ? Convert.ToInt32(Request.Form["state"]) : 0;
// Validate the form fields
if (name.Length == 0) throw new Exception("Name is required.");
if (phone.Length == 0) throw new Exception("Phone is required.");
if (address.Length == 0) throw new Exception("Address is required.");
if (city.Length == 0) throw new Exception("City is required.");
if (stateID == 0) throw new Exception("State is required.");
if (postalCode.Length == 0) throw new Exception("Postal Code is required.");
GoogleMaps map = new GoogleMaps(System.Configuration.ConfigurationManager.AppSettings["GoogleMapsKey"]);
LatLng location = map.GetLatLng(address, city, stateID);
// Create the new customer and save
CustomerLocation new_location = new CustomerLocation {
name = name,
email = email,
phone = phone,
fax = fax,
address = address,
city = city,
stateID = stateID,
postalCode = postalCode,
latitude = Convert.ToDouble(location.latitude),
longitude = Convert.ToDouble(location.longitude),
cust_id = id
};
db.CustomerLocations.InsertOnSubmit(new_location);
db.SubmitChanges();
return RedirectToAction("Locations", new { id = id });
}
} catch (Exception e) {
error = e.Message;
}
#endregion
// Get the customer that we're adding the location to
Customer customer = CustomerModel.Get(id);
ViewBag.customer = customer;
// Get the states
List<FullCountry> countries = CustomerModel.GetCountries();
ViewBag.countries = countries;
ViewBag.error = error;
return View();
}
public ActionResult Add()
{
string error = "";
#region Form Submission
try {
if (Request.Form["btnSubmit"] != null) {
CurtDevDataContext db = new CurtDevDataContext();
// Save form values
string name = (Request.Form["name"] != null) ? Request.Form["name"] : "";
string email = (Request.Form["email"] != null) ? Request.Form["email"] : "";
string phone = (Request.Form["phone"] != null) ? Request.Form["phone"] : "";
string fax = (Request.Form["fax"] != null) ? Request.Form["fax"] : "";
string address = (Request.Form["address"] != null) ? Request.Form["address"] : "";
string address2 = (Request.Form["address2"] != null) ? Request.Form["address2"] : "";
string city = (Request.Form["city"] != null) ? Request.Form["city"] : "";
int stateID = (Request.Form["state"] != null) ? Convert.ToInt32(Request.Form["state"]) : 0;
string postalCode = (Request.Form["postal_code"] != null) ? Request.Form["postal_code"] : "";
string contact = (Request.Form["contact_person"] != null) ? Request.Form["contact_person"] : "";
string website = (Request.Form["website"] != null) ? Request.Form["website"] : "";
string eLocalURL = (Request.Form["eLocalURL"] != null) ? Request.Form["eLocalURL"] : "";
string searchURL = (Request.Form["searchURL"] != null) ? Request.Form["searchURL"] : "";
string logo = (Request.Form["logo"] != null && Request.Form["logo"].Trim() != "") ? Request.Form["logo"] : null;
int dealer_type = (Request.Form["dealer_type"] != null) ? Convert.ToInt32(Request.Form["dealer_type"]) : 0;
int customerID = (Request.Form["customerID"] != null && Request.Form["customerID"] != "") ? Convert.ToInt32(Request.Form["customerID"]) : 0;
int salesRepID = (Request.Form["salesRepID"] != null && Request.Form["salesRepID"] != "") ? Convert.ToInt32(Request.Form["salesRepID"]) : 0;
int mapixCodeID = (Request.Form["mapixCodeID"] != null && Request.Form["mapixCodeID"] != "") ? Convert.ToInt32(Request.Form["mapixCodeID"]) : 0;
int parentID = (Request.Form["parentID"] != null && Request.Form["parentID"] != "") ? Convert.ToInt32(Request.Form["parentID"]) : 0;
int tier = (Request.Form["tier"] != null && Request.Form["tier"] != "") ? Convert.ToInt32(Request.Form["tier"]) : 1;
bool isDummy = (Request.Form["isDummy"] != null && Request.Form["isDummy"] != "") ? Convert.ToBoolean(Request.Form["isDummy"]) : false;
bool showWebsite = (Request.Form["showWebsite"] != null && Request.Form["showWebsite"] != "") ? Convert.ToBoolean(Request.Form["showWebsite"]) : false;
LatLng location = new LatLng();
// Validate the form fields
if (name.Length == 0) throw new Exception("Name is required.");
if (customerID == 0 && parentID == 0) throw new Exception("Either a Customer ID or a Parent Customer is required.");
if (dealer_type == 0) throw new Exception("A Dealer Type is required.");
if (address != "" && city != "" && stateID != 0) {
GoogleMaps map = new GoogleMaps(System.Configuration.ConfigurationManager.AppSettings["GoogleMapsKey"]);
location = map.GetLatLng(address, city, stateID);
}
// Create the new customer and save
Customer new_cust = new Customer {
name = name,
email = email,
phone = phone,
fax = fax,
address = address,
address2 = address2,
city = city,
stateID = stateID,
postal_code = postalCode,
contact_person = contact,
website = website,
eLocalURL = eLocalURL,
searchURL = searchURL,
logo = logo,
mCodeID = mapixCodeID,
salesRepID = salesRepID,
dealer_type = dealer_type,
isDummy = isDummy,
tier = tier,
showWebsite = showWebsite
};
if (location.latitude != null && location.longitude != null) {
new_cust.latitude = location.latitude;
new_cust.longitude = location.longitude;
}
if (customerID != 0) {
new_cust.customerID = customerID;
}
if (parentID != 0) {
new_cust.parentID = parentID;
}
db.Customers.InsertOnSubmit(new_cust);
db.SubmitChanges();
return RedirectToAction("Add");
}
} catch (Exception e) {
error = e.Message;
}
#endregion
// Get the dealer tiers
List<DealerTier> dealer_tiers = CustomerModel.GetDealerTiers();
ViewBag.dealer_tiers = dealer_tiers;
// Get the dealer types
List<DealerType> dealer_types = CustomerModel.GetDealerTypes().Reverse<DealerType>().ToList<DealerType>();
ViewBag.dealer_types = dealer_types;
// Get the mapix codes
List<MapixCode> mapix_codes = CustomerModel.GetMapixCodes();
ViewBag.mapix_codes = mapix_codes;
// Get the mapix codes
List<SalesRepresentative> sales_reps = CustomerModel.GetSalesReps();
ViewBag.sales_reps = sales_reps;
// Get the states
List<FullCountry> countries = CustomerModel.GetCountries();
ViewBag.countries = countries;
// Get the customer list
List<Customer> customers = CustomerModel.GetAll();
ViewBag.customers = customers;
ViewBag.error = error;
return View();
}
public ActionResult PopulateLocations(int id = 0)
{
if (id > 0) {
try {
CurtDevDataContext db = new CurtDevDataContext();
GoogleMaps map = new GoogleMaps(System.Configuration.ConfigurationManager.AppSettings["GoogleMapsKey"]);
List<CustomerLocation> locations = db.CustomerLocations.Where(x => x.cust_id.Equals(id) && x.latitude.Equals(0) && x.longitude.Equals(0)).ToList<CustomerLocation>();
foreach (CustomerLocation l in locations) {
LatLng geo = map.GetLatLng(l.address, l.city, l.stateID);
if (geo != null) {
l.latitude = Convert.ToDouble(geo.latitude);
l.longitude = Convert.ToDouble(geo.longitude);
}
}
db.SubmitChanges();
} catch {}
}
return RedirectToAction("Locations", new { id = id });
}
