public List <CarDetailed> GetCarsByQuickSearch(string searchterm)//not by year will need split in controller
{
List <CarDetailed> Cars = new List <CarDetailed>();
using (SqlConnection cn = new SqlConnection(DataSettings.GetConnectionString()))
{
SqlCommand cmd = new SqlCommand("GetCarsByQuickSearch", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@searchstring", searchterm);
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
CarDetailed current = new CarDetailed();
current.CarID = (int)dr["CarID"];
current.ModelName = dr["ModelName"].ToString();
current.ModelID = (int)dr["ModelID"];
current.MakeName = dr["MakeName"].ToString();
current.MakeID = (int)dr["MakeID"];
current.IsNew = (bool)dr["Isnew"];
current.ModelYear = (int)dr["ModelYear"];
current.TransmissionID = (int)dr["TransmissionID"];
current.TransmissionName = dr["TransmissionName"].ToString();
current.Color = (int)dr["color"];
current.ColorName = dr["Colorname"].ToString();
current.Interior = (int)dr["Interior"];
current.InteriorDescription = dr["InteriorDescription"].ToString();
current.Mileage = (int)dr["Mileage"];
current.VIN = dr["VIN"].ToString();
current.MSRP = (decimal)dr["MSRP"];
current.Saleprice = (decimal)dr["Saleprice"];
current.CarDescription = dr["CarDescription"].ToString();
current.ImageFileName = dr["ImageFileName"].ToString();
current.AddedBy = dr["Addedby"].ToString();
string cdate = dr["CreatedDate"].ToString();
current.CreatedDate = DateTime.Parse(cdate);
current.IsSold = (bool)dr["IsSold"];
current.IsFeatured = (bool)dr["IsFeatured"];
current.StyleID = (int)dr["StyleID"];
current.BodyStyle = dr["StyleName"].ToString();
Cars.Add(current);
}
}
}
return(Cars);
}
public CarDetailed GetCarByID(int ID)
{
CarDetailed current = new CarDetailed();
using (SqlConnection cn = new SqlConnection(DataSettings.GetConnectionString()))
{
SqlCommand cmd = new SqlCommand("GetCarByID", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@CarID", ID);
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
if (dr.Read())
{
current.CarID = (int)dr["CarID"];
current.ModelName = dr["ModelName"].ToString();
current.ModelID = (int)dr["ModelID"];
current.MakeName = dr["MakeName"].ToString();
current.MakeID = (int)dr["MakeID"];
current.IsNew = (bool)dr["Isnew"];
current.ModelYear = (int)dr["ModelYear"];
current.TransmissionID = (int)dr["TransmissionID"];
current.TransmissionName = dr["TransmissionName"].ToString();
current.Color = (int)dr["color"];
current.ColorName = dr["Colorname"].ToString();
current.Interior = (int)dr["Interior"];
current.InteriorDescription = dr["InteriorDescription"].ToString();
current.Mileage = (int)dr["Mileage"];
current.VIN = dr["VIN"].ToString();
current.MSRP = (decimal)dr["MSRP"];
current.Saleprice = (decimal)dr["Saleprice"];
current.CarDescription = dr["CarDescription"].ToString();
current.ImageFileName = dr["ImageFileName"].ToString();
current.AddedBy = dr["Addedby"].ToString();
string cdate = dr["CreatedDate"].ToString();
current.CreatedDate = DateTime.Parse(cdate);
current.IsSold = (bool)dr["IsSold"];
current.IsFeatured = (bool)dr["IsFeatured"];
current.StyleID = (int)dr["StyleID"];
current.BodyStyle = dr["StyleName"].ToString();
}
}
}
return(current);
}
public void SellCarNoPromo(SoldCar car, Customer customer, decimal FinalPrice, int PaymentTypeID, string SoldBy)
{
using (SqlConnection cn = new SqlConnection(DataSettings.GetConnectionString()))
{
SqlCommand cmd = new SqlCommand("AddCustomer", cn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter Param = new SqlParameter("@CustomerID", SqlDbType.Int);//c
Param.Direction = ParameterDirection.Output;
cmd.Parameters.Add(Param);
cmd.Parameters.AddWithValue("@CustomerName", customer.CustomerName);
cmd.Parameters.AddWithValue("@Phone", customer.Phone);
cmd.Parameters.AddWithValue("@Email", customer.Email);
cmd.Parameters.AddWithValue("@Streetaddress", customer.StreetAddress);
if (string.IsNullOrWhiteSpace(customer.StreetAddress2))
{
cmd.Parameters.AddWithValue("@Streetaddress2", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@Streetaddress2", customer.StreetAddress2);
}
cmd.Parameters.AddWithValue("@City", customer.City);
cmd.Parameters.AddWithValue("@StateID", customer.StateID);
cmd.Parameters.AddWithValue("@Zipcode", customer.Zipcode);
cn.Open();
cmd.ExecuteNonQuery();
int x = (int)Param.Value;
SqlCommand cmd1 = new SqlCommand("SellaCar", cn);
cmd1.CommandType = CommandType.StoredProcedure;
cmd1.Parameters.AddWithValue("@CarID", car.Car.CarID);
cmd1.Parameters.AddWithValue("@CustomerID", x);
cmd1.Parameters.AddWithValue("@SalesPrice", FinalPrice);
cmd1.Parameters.AddWithValue("@PurchaseTypeID", PaymentTypeID);
cmd1.Parameters.AddWithValue("@PromotonID", DBNull.Value);
cmd1.Parameters.AddWithValue("@Soldby", SoldBy);
cmd1.ExecuteNonQuery();
}
}
public void AddCar(CarDetailed ToAdd)
{
using (SqlConnection cn = new SqlConnection(DataSettings.GetConnectionString()))
{
SqlCommand cmd = new SqlCommand("AddCar", cn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter Param = new SqlParameter("@CarID", SqlDbType.Int);
Param.Direction = ParameterDirection.Output;
cmd.Parameters.Add(Param);
cmd.Parameters.AddWithValue("@ModelID", ToAdd.ModelID);
cmd.Parameters.AddWithValue("@StyleID", ToAdd.StyleID);
cmd.Parameters.AddWithValue("@Mileage", ToAdd.Mileage);
cmd.Parameters.AddWithValue("@Salesprice", ToAdd.Saleprice);
cmd.Parameters.AddWithValue("@Isnew", ToAdd.IsNew);
cmd.Parameters.AddWithValue("@IsSold", ToAdd.IsSold);
cmd.Parameters.AddWithValue("@IsFeatured", ToAdd.IsFeatured);
cmd.Parameters.AddWithValue("@ModelYear", ToAdd.ModelYear);
cmd.Parameters.AddWithValue("@ColorID", ToAdd.Color);
cmd.Parameters.AddWithValue("@transmissionID", ToAdd.TransmissionID);
cmd.Parameters.AddWithValue("@interiorID", ToAdd.Interior);
cmd.Parameters.AddWithValue("@VIN", ToAdd.VIN);
cmd.Parameters.AddWithValue("@MSRP", ToAdd.MSRP);
if (string.IsNullOrWhiteSpace(ToAdd.CarDescription))
{
ToAdd.CarDescription = null;
}
cmd.Parameters.AddWithValue("@Description", ToAdd.CarDescription);
if (string.IsNullOrWhiteSpace(ToAdd.ImageFileName))
{
ToAdd.ImageFileName = "Placeholder.jpg";
}
cmd.Parameters.AddWithValue("@ImageFileName", ToAdd.ImageFileName);
cn.Open();
cmd.ExecuteNonQuery();
ToAdd.CarID = (int)Param.Value;
}
}
public List <Promotion> GetALLpromotions()
{
List <Promotion> promos = new List <Promotion>();
using (SqlConnection cn = new SqlConnection(DataSettings.GetConnectionString()))
{
SqlCommand cmd = new SqlCommand("Select * from promotion", cn);
cmd.CommandType = CommandType.Text;
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
Promotion p = new Promotion();
p.PromotionID = (int)dr["PromotionID"];
p.PromotionName = dr["PromotionName"].ToString();
p.Description = dr["PromotionDescription"].ToString();
string sdate = dr["PromotionStartDate"].ToString();
if (!string.IsNullOrEmpty(sdate))
{
p.StartDate = DateTime.Parse(sdate);
}
string edate = dr["PromotionEndDate"].ToString();
if (!string.IsNullOrEmpty(edate))
{
p.EndDate = DateTime.Parse(edate);
}
p.FlatDiscount = (decimal)dr["FlatDiscount"];
p.PercentDiscount = (int)dr["PercentDiscount"];
p.IsCanceled = (bool)dr["IsCanceled"];
p.IsForNew = (bool)dr["IsForNew"];
p.IsForUsed = (bool)dr["IsForUsed"];
promos.Add(p);
}
}
}
return(promos);
}
public void EditCar(CarDetailed ToEdit)
{
using (SqlConnection cn = new SqlConnection(DataSettings.GetConnectionString()))
{
SqlCommand cmd = new SqlCommand("EditCar", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@CarID", ToEdit.CarID);
cmd.Parameters.AddWithValue("@ModelID", ToEdit.ModelID);
cmd.Parameters.AddWithValue("@StyleID", ToEdit.StyleID);
cmd.Parameters.AddWithValue("@Mileage", ToEdit.Mileage);
cmd.Parameters.AddWithValue("@Salesprice", ToEdit.Saleprice);
cmd.Parameters.AddWithValue("@Isnew", ToEdit.IsNew);
cmd.Parameters.AddWithValue("@isFeatured", ToEdit.IsFeatured);
cmd.Parameters.AddWithValue("@ModelYear", ToEdit.ModelYear);
cmd.Parameters.AddWithValue("@ColorID", ToEdit.Color);
cmd.Parameters.AddWithValue("@transmissionID", ToEdit.TransmissionID);
cmd.Parameters.AddWithValue("@interiorID", ToEdit.Interior);
cmd.Parameters.AddWithValue("@VIN", ToEdit.VIN);
cmd.Parameters.AddWithValue("@MSRP", ToEdit.MSRP);
if (string.IsNullOrWhiteSpace(ToEdit.CarDescription))
{
ToEdit.CarDescription = " ";
}
cmd.Parameters.AddWithValue("@Description", ToEdit.CarDescription);
if (string.IsNullOrWhiteSpace(ToEdit.ImageFileName))
{
ToEdit.ImageFileName = "Placeholder.JPG";
}
cmd.Parameters.AddWithValue("@ImageFileName", ToEdit.ImageFileName);
cn.Open();
cmd.ExecuteNonQuery();
}
}