public List <Sales> GetListofSales(string username)
{
List <Sales> sales = new List <Sales>();
using (SqlConnection cn = new SqlConnection(DataSettings.GetConnectionString()))
{
SqlCommand cmd = new SqlCommand("GetListOfSales", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@UserName", username);
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
Sales current = new Sales();
current.UserName = dr["UserName"].ToString();
string datetemp = dr["SoldDate"].ToString();
current.DateSold = DateTime.Parse(datetemp);
current.SalePrice = (decimal)dr["SalesPrice"];
sales.Add(current);
}
}
}
return(sales);
}
public List <Contact> GetAllContacts()
{
List <Contact> contacts = new List <Contact>();
using (SqlConnection cn = new SqlConnection(DataSettings.GetConnectionString()))
{
SqlCommand cmd = new SqlCommand("Select * from Contacts", cn);
cmd.CommandType = CommandType.Text;
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
Contact current = new Contact();
current.ContactID = (int)dr["ContactID"];
current.ContactName = dr["ContactName"].ToString();
try
{
current.Phone = dr["ContactPhone"].ToString();
}
catch
{
current.Phone = "Unknown";
}
current.Email = dr["ContactEmail"].ToString();
current.ContactMessage = dr["ContactMessage"].ToString();
string contactdate = dr["ContactDate"].ToString();
current.ContactDate = DateTime.Parse(contactdate);
current.Responded = (bool)dr["Responded"];
contacts.Add(current);
}
}
}
return(contacts);
}
public List <Employee> GetAllEmployees()
{
List <Employee> employees = new List <Employee>();
using (SqlConnection cn = new SqlConnection(DataSettings.GetConnectionString()))
{
SqlCommand cmd = new SqlCommand("GetSystemUsers", cn);
cmd.CommandType = CommandType.StoredProcedure;
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
Employee current = new Employee();
current.EmployeeID = dr["Id"].ToString();
current.Email = dr["Email"].ToString();
current.UserName = dr["UserName"].ToString();
current.RoleName = dr["Name"].ToString();
current.Firstname = dr["FirstName"].ToString();
current.Lastname = dr["LastName"].ToString();
employees.Add(current);
}
}
}
return(employees);
}
public List <Makes> GetMakes()
{
List <Makes> MakeChoices = new List <Makes>();
using (SqlConnection cn = new SqlConnection(DataSettings.GetConnectionString()))
{
SqlCommand cmd = new SqlCommand("Select * from Make", cn);
cmd.CommandType = CommandType.Text;
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
Makes s = new Makes();
s.MakeID = (int)dr["MakeID"];
s.MakeName = dr["MakeName"].ToString();
s.AddedBy = dr["Addedby"].ToString();
s.DateAdded = DateTime.Parse(dr["DateAdded"].ToString()).Date;
MakeChoices.Add(s);
}
}
}
return(MakeChoices);
}
public Employee GetEmployeeByID(string EmployeeID)
{
Employee current = new Employee();
using (SqlConnection cn = new SqlConnection(DataSettings.GetConnectionString()))
{
SqlCommand cmd = new SqlCommand("GetSystemUserByID", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@UserID", EmployeeID);
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
if (dr.Read())
{
current.EmployeeID = dr["Id"].ToString();
current.Email = dr["Email"].ToString();
current.UserName = dr["UserName"].ToString();
current.RoleName = dr["Name"].ToString();
current.Firstname = dr["FirstName"].ToString();
current.Lastname = dr["LastName"].ToString();
}
}
}
return(current);
}
public List <CarModels> GetModels()
{
List <CarModels> ModelChoices = new List <CarModels>();
using (SqlConnection cn = new SqlConnection(DataSettings.GetConnectionString()))
{
SqlCommand cmd = new SqlCommand("GetModelsforDisplay", cn);
cmd.CommandType = CommandType.StoredProcedure;
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
CarModels s = new CarModels();
s.ModelID = (int)dr["ModelID"];
s.MakeName = dr["MakeName"].ToString();
s.MakeID = (int)dr["MakeID"];
s.ModelName = dr["ModelName"].ToString();
s.AddedBy = dr["Addedby"].ToString();
s.DateAdded = DateTime.Parse(dr["DateAdded"].ToString()).Date;
ModelChoices.Add(s);
}
}
}
return(ModelChoices);
}
public void DataSetup()
{
using (SqlConnection cn = new SqlConnection(DataSettings.GetConnectionString()))
{
SqlCommand cmd = new SqlCommand("dbreset", cn);
cn.Open();
cmd.ExecuteNonQuery();
}
}
public void DisableUser(string EmployeeID)
{
using (SqlConnection cn = new SqlConnection(DataSettings.GetConnectionString()))
{
SqlCommand cmd = new SqlCommand("disableuser", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@UserID", EmployeeID);
cn.Open();
cmd.ExecuteNonQuery();
}
}
public void EditEmployee(Employee ToEdit)
{
using (SqlConnection cn = new SqlConnection(DataSettings.GetConnectionString()))
{
SqlCommand cmd = new SqlCommand("UpdateUser", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@UserID", ToEdit.EmployeeID);
cmd.Parameters.AddWithValue("@FirstName", ToEdit.Firstname);
cmd.Parameters.AddWithValue("@LastName", ToEdit.Lastname);
cmd.Parameters.AddWithValue("@Email", ToEdit.Email);
cn.Open();
cmd.ExecuteNonQuery();
}
}
public void AddContact(Contact contact)
{
using (SqlConnection cn = new SqlConnection(DataSettings.GetConnectionString()))
{
SqlCommand cmd = new SqlCommand("AddContact", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ContactName", contact.ContactName);
if (!string.IsNullOrWhiteSpace(contact.Phone))
{
cmd.Parameters.AddWithValue("@Contactphone", contact.Phone);
}
if (string.IsNullOrWhiteSpace(contact.Phone))
{
cmd.Parameters.AddWithValue("@Contactphone", DBNull.Value);
}
cmd.Parameters.AddWithValue("@ContactEmail", contact.Email);
cmd.Parameters.AddWithValue("@ContactMessage", contact.ContactMessage);
cn.Open();
cmd.ExecuteNonQuery();
}
}
public List <Role> GetAllRoles()
{
List <Role> roles = new List <Role>();
using (SqlConnection cn = new SqlConnection(DataSettings.GetConnectionString()))
{
SqlCommand cmd = new SqlCommand("select * from AspNetRoles", cn);
cmd.CommandType = CommandType.Text;
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
Role r = new Role();
r.RoleID = dr["Id"].ToString();
r.RoleName = dr["Name"].ToString();
roles.Add(r);
}
}
}
return(roles);
}
public SalesReport GetSalesReportByName(string username)
{
SalesReport report = new SalesReport();
using (SqlConnection cn = new SqlConnection(DataSettings.GetConnectionString()))
{
SqlCommand cmd = new SqlCommand("GetSalesReport", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@UserName", username);
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
if (dr.Read())
{
report.UserName = dr["UserName"].ToString();
report.TotalCarsSold = (int)dr["CarsSold"];
report.NetSales = (decimal)dr["TotalSales"];
}
}
}
return(report);
}
public List <Transmission> GetTrans()
{
List <Transmission> trans = new List <Transmission>();
using (SqlConnection cn = new SqlConnection(DataSettings.GetConnectionString()))
{
SqlCommand cmd = new SqlCommand("Select * from Transmission", cn);
cmd.CommandType = CommandType.Text;
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
Transmission s = new Transmission();
s.TransmissionID = (int)dr["TransmissionID"];
s.TransmissionName = dr["TransmissionName"].ToString();
trans.Add(s);
}
}
}
return(trans);
}
public List <State> GetStates()
{
List <State> states = new List <State>();
using (SqlConnection cn = new SqlConnection(DataSettings.GetConnectionString()))
{
SqlCommand cmd = new SqlCommand("Select * from States", cn);
cmd.CommandType = CommandType.Text;
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
State s = new State();
s.StateID = dr["StateID"].ToString();
s.StateName = dr["StateName"].ToString();
states.Add(s);
}
}
}
return(states);
}
public List <PaymentType> GetPaymentTypes()
{
List <PaymentType> payments = new List <PaymentType>();
using (SqlConnection cn = new SqlConnection(DataSettings.GetConnectionString()))
{
SqlCommand cmd = new SqlCommand("Select * from PurchaseType", cn);
cmd.CommandType = CommandType.Text;
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
PaymentType p = new PaymentType();
p.PaymentTypeID = (int)dr["PurchaseTypeID"];
p.PaymentTypeName = dr["PurchaseTypeName"].ToString();
payments.Add(p);
}
}
}
return(payments);
}
public List <InventoryReportItem> GetInventoryReport()
{
List <InventoryReportItem> reports = new List <InventoryReportItem>();
using (SqlConnection cn = new SqlConnection(DataSettings.GetConnectionString()))
{
SqlCommand cmd = new SqlCommand("GetInventoryReport", cn);
cmd.CommandType = CommandType.StoredProcedure;
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
InventoryReportItem current = new InventoryReportItem();
current.ModelName = dr["ModelName"].ToString();
current.MakeName = dr["MakeName"].ToString();
current.ModelYear = (int)dr["ModelYear"];
current.InStock = (int)dr["InStock"];
reports.Add(current);
}
}
}
return(reports);
}
public List <Interiors> GetInteriors()
{
List <Interiors> interiors = new List <Interiors>();
using (SqlConnection cn = new SqlConnection(DataSettings.GetConnectionString()))
{
SqlCommand cmd = new SqlCommand("Select * from Interiors", cn);
cmd.CommandType = CommandType.Text;
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
Interiors s = new Interiors();
s.InteriorID = (int)dr["InteriorId"];
s.InteriorName = dr["InteriorDescription"].ToString();
interiors.Add(s);
}
}
}
return(interiors);
}
public List <BodyStyles> GetBodies()
{
List <BodyStyles> Styles = new List <BodyStyles>();
using (SqlConnection cn = new SqlConnection(DataSettings.GetConnectionString()))
{
SqlCommand cmd = new SqlCommand("Select * from BodyStyle", cn);
cmd.CommandType = CommandType.Text;
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
BodyStyles s = new BodyStyles();
s.StyleID = (int)dr["StyleID"];
s.BodyStyle = dr["StyleName"].ToString();
Styles.Add(s);
}
}
}
return(Styles);
}
public List <Colors> GetColors()
{
List <Colors> colors = new List <Colors>();
using (SqlConnection cn = new SqlConnection(DataSettings.GetConnectionString()))
{
SqlCommand cmd = new SqlCommand("Select * from Color", cn);
cmd.CommandType = CommandType.Text;
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
Colors s = new Colors();
s.ColorID = (int)dr["ColorID"];
s.ColorName = dr["Colorname"].ToString();
colors.Add(s);
}
}
}
return(colors);
}
