public static string GetCustomer(string CustEmail, string Password)
{
string CustFirstName = "";
String Custquery = "SELECT CustFirstName FROM customers WHERE [CustEmail] = @CustEmail AND PASSWORD =@PASSWORD";
SqlConnection Connection = new SqlConnection(TravelDB.GetConnectionString());
SqlCommand cmd = new SqlCommand(Custquery, Connection);
cmd.Parameters.AddWithValue("@CustEmail", CustEmail);
cmd.Parameters.AddWithValue("@Password", Password);
try
{
Connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
CustFirstName = reader["CustFirstName"].ToString();
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
Connection.Close();
}
return(CustFirstName);
}
public static bool addCustomer(string CustFirstName, string CustLastName, string CustAddress, string CustCity, string CustProvince, string CustPostalCode, string CustCountry, string CustHomePhone, string CustBusPhone, string CustEmail, int CustFax, string Password)
{
bool result = false;
string query = "INSERT INTO Customers(CustFirstName, CustLastName, CustAddress, CustCity, CustProv, CustPostal, CustCountry, CustHomePhone, CustBusPhone, CustEmail, CustFax, Password)VALUES(@CustFirstName, @CustLastName, @CustAddress, @CustCity, @CustProvince, @CustPostalCode, @CustCountry, @CustHomePhone, @CustBusPhone, @CustEmail, @CustFax, @Password)";
SqlConnection Connection = new SqlConnection(TravelDB.GetConnectionString());
SqlCommand cmd = new SqlCommand(query, Connection);
cmd.Parameters.AddWithValue("@CustFirstName", CustFirstName);
cmd.Parameters.AddWithValue("@CustLastName", CustLastName);
cmd.Parameters.AddWithValue("@CustAddress", CustAddress);
cmd.Parameters.AddWithValue("@CustCity", CustCity);
cmd.Parameters.AddWithValue("@CustProvince", CustProvince);
cmd.Parameters.AddWithValue("@CustPostalCode", CustPostalCode);
cmd.Parameters.AddWithValue("@CustCountry", CustCountry);
cmd.Parameters.AddWithValue("@CustHomePhone", CustHomePhone);
cmd.Parameters.AddWithValue("@CustBusPhone", CustBusPhone);
cmd.Parameters.AddWithValue("@CustEmail", CustEmail);
cmd.Parameters.AddWithValue("@CustFax", CustFax);
cmd.Parameters.AddWithValue("@Password", Password);
try
{
Connection.Open();
cmd.ExecuteNonQuery();
result = true;
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
public static bool Login(string CustEmail, string Password)
{
bool result = false;
List <Customer> CustEmailLst = new List <Customer>();
Customer Cust = null;
String CustEmailquery = "SELECT [CustEmail], PASSWORD FROM customers WHERE [CustEmail] = @CustEmail AND PASSWORD =@PASSWORD";
SqlConnection Connection = new SqlConnection(TravelDB.GetConnectionString());
SqlCommand cmd = new SqlCommand(CustEmailquery, Connection);
cmd.Parameters.AddWithValue("@CustEmail", CustEmail);
cmd.Parameters.AddWithValue("@Password", Password);
try
{
Connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Cust = new Customer();
Cust.CustEmail = reader["CustEmail"].ToString();
Cust.Password = reader["Password"].ToString();
CustEmailLst.Add(Cust);
}
foreach (var item in CustEmailLst)
{
if (item.CustEmail == CustEmail && item.Password == Password)
{
result = true;
}
else
{
result = false;
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
Connection.Close();
}
return(result);
}
public static Customer GetAllCustomer(string CustEmail)
{
Customer Cust = null;
String Custquery = "SELECT * FROM customers WHERE [CustEmail] = @CustEmail";
SqlConnection Connection = new SqlConnection(TravelDB.GetConnectionString());
SqlCommand cmd = new SqlCommand(Custquery, Connection);
cmd.Parameters.AddWithValue("@CustEmail", CustEmail);
try
{
Connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Cust = new Customer();
Cust.CustFirstName = reader["CustFirstName"].ToString();
Cust.CustLastName = reader["CustLastName"].ToString();
Cust.CustAddress = reader["CustAddress"].ToString();
Cust.CustCity = reader["CustCity"].ToString();
Cust.CustProv = reader["CustProv"].ToString();
Cust.CustCountry = reader["CustCountry"].ToString();
Cust.CustHomePhone = Convert.ToInt64(reader["CustHomePhone"]);
Cust.CustBusPhone = Convert.ToInt64(reader["CustBusPhone"]);
Cust.CustEmail = reader["CustEmail"].ToString();
Cust.CustFax = Convert.ToInt32(reader["CustFax"]);
Cust.Password = reader["Password"].ToString();
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
Connection.Close();
}
return(Cust);
}
public static List <Packages> GetPackages(string CustEmail)
{
List <Packages> PkgList = new List <Packages>();
Packages Pkg = null;
string query = "SELECT SUM(P.PkgBasePrice) CostOfAllPackages,(SUM(P.PkgBasePrice))/P.PkgBasePrice CountOfPackagesBought, p.PkgDesc, P.PkgBasePrice, P.PkgName, C.CustFirstName, c.CustLastName, c.CustEmail FROM PACKAGES P INNER JOIN BOOKINGS B ON P.PackageId = B.PackageId INNER JOIN CUSTOMERS C ON C.CustomerId = B.CustomerId where c.CustEmail = @CustEmail GROUP BY c.CustEmail, P.PkgName, P.PkgBasePrice, C.CustFirstName, c.CustLastName, p.PkgDesc";
SqlConnection Connection = new SqlConnection(TravelDB.GetConnectionString());
SqlCommand cmd = new SqlCommand(query, Connection);
cmd.Parameters.AddWithValue("@CustEmail", CustEmail);
try
{
Connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Pkg = new Packages();
Pkg.PkgDesc = reader["PkgDesc"].ToString();
Pkg.PkgBasePrice = Convert.ToDecimal(reader["PkgBasePrice"]);
Pkg.PkgName = reader["PkgName"].ToString();
Pkg.CostOfAllPackages = Convert.ToDecimal(reader["CostOfAllPackages"]);
Pkg.CountOfPackagesBought = Convert.ToInt32(reader["CountOfPackagesBought"]);
Pkg.CustEmail = reader["CustEmail"].ToString();
Pkg.CustFirstName = reader["CustFirstName"].ToString();
Pkg.CustLastName = reader["CustLastName"].ToString();
PkgList.Add(Pkg);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
Connection.Close();
}
return(PkgList);
}
public static bool UpdateCustomer(string CustFirstName, string CustLastName, string CustAddress, string CustCity, string CustProvince, string CustPostalCode, string CustCountry, string CustHomePhone, string CustBusPhone, string CustEmail, int CustFax)
{
/*if (!CustomerDB.Login(CustEmail, Password) == true)
* {
* return false;
* }*/
bool result = false;
string query = "UPDATE CUSTOMERS SET CustFirstName = @CustFirstName, CustLastName = @CustLastName, CustAddress = @CustAddress, CustCity = @CustCity, CustProv = @CustProvince, CustPostal = @CustPostalCode, CustCountry = @CustCountry, CustHomePhone = @CustHomePhone, CustBusPhone = @CustBusPhone, CustEmail = @CustEmail, CustFax = @CustFax WHERE CustEmail = @CustEmail";
SqlConnection Connection = new SqlConnection(TravelDB.GetConnectionString());
SqlCommand cmd = new SqlCommand(query, Connection);
cmd.Parameters.AddWithValue("@CustFirstName", CustFirstName);
cmd.Parameters.AddWithValue("@CustLastName", CustLastName);
cmd.Parameters.AddWithValue("@CustAddress", CustAddress);
cmd.Parameters.AddWithValue("@CustCity", CustCity);
cmd.Parameters.AddWithValue("@CustProvince", CustProvince);
cmd.Parameters.AddWithValue("@CustPostalCode", CustPostalCode);
cmd.Parameters.AddWithValue("@CustCountry", CustCountry);
cmd.Parameters.AddWithValue("@CustHomePhone", CustHomePhone);
cmd.Parameters.AddWithValue("@CustBusPhone", CustBusPhone);
cmd.Parameters.AddWithValue("@CustEmail", CustEmail);
cmd.Parameters.AddWithValue("@CustFax", CustFax);
// cmd.Parameters.AddWithValue("@Password", Password);
try
{
Connection.Open();
cmd.ExecuteNonQuery();
result = true;
}
catch (Exception ex)
{
throw ex;
}
return(result);
}