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);
        }