// create new customer account
        public static void CreateAccount(Customer cust)
        {
            string addCustomerQuery = @"INSERT INTO Customers " +
                                      "(CustFirstName, CustLastName, CustAddress, CustCity, CustProv, CustPostal, CustCountry, CustHomePhone, CustBusPhone, CustEmail, CustPass) " +
                                      "VALUES (@CustFirstName, @CustLastName, @CustAddress, @CustCity, @CustProv, @CustPostal, @CustCountry, @CustHomePhone, @CustBusPhone, @CustEmail, @CustPass)";

            using (SqlConnection con = TravelExpertsConn.GetConnection())
            {
                using (SqlCommand sqlCommand = new SqlCommand(addCustomerQuery, con))
                {
                    // hash password
                    string password = Convert.ToString(cust.CustPass);
                    var    hash     = PassHash.SecurePasswordHasher.Hash(password);

                    sqlCommand.Parameters.AddWithValue("@CustFirstName", cust.CustFirstName);
                    sqlCommand.Parameters.AddWithValue("@CustLastName", cust.CustLastName);
                    sqlCommand.Parameters.AddWithValue("@CustAddress", cust.CustAddress);
                    sqlCommand.Parameters.AddWithValue("@CustCity", cust.CustCity);
                    sqlCommand.Parameters.AddWithValue("@CustProv", cust.CustProv);
                    sqlCommand.Parameters.AddWithValue("@CustPostal", cust.CustPostal);
                    sqlCommand.Parameters.AddWithValue("@CustCountry", cust.CustCountry);
                    sqlCommand.Parameters.AddWithValue("@CustHomePhone", cust.CustHomePhone);
                    sqlCommand.Parameters.AddWithValue("@CustBusPhone", cust.CustBusPhone);
                    sqlCommand.Parameters.AddWithValue("@CustEmail", cust.CustEmail);
                    sqlCommand.Parameters.AddWithValue("@CustPass", hash);

                    con.Open();
                    sqlCommand.ExecuteNonQuery();
                }
            }
        }
        // customer login
        public static int CustomerLogin(CustomerLogin login)
        {
            int    custId           = -1;
            string addCustomerQuery = @"SELECT CustomerId, CustPass " +
                                      "FROM Customers WHERE CustEmail = @CustEmail";

            using (SqlConnection con = TravelExpertsConn.GetConnection())
            {
                using (SqlCommand sqlCommand = new SqlCommand(addCustomerQuery, con))
                {
                    sqlCommand.Parameters.AddWithValue("@CustEmail", login.CustEmail);

                    con.Open();
                    SqlDataReader dr = sqlCommand.ExecuteReader();

                    if (dr.Read())
                    {
                        // retrieve and verify password against hash
                        string hashpass = Convert.ToString(dr["CustPass"]);
                        var    result   = PassHash.SecurePasswordHasher.Verify(login.CustPass, hashpass);
                        if (result)
                        {
                            custId = Convert.ToInt32(dr["CustomerId"]);
                        }
                    }
                    dr.Close();
                }
            }
            return(custId);
        }
        public static List <Package> GetPackages()
        {
            List <Package> packageList = new List <Package>();
            string         getPkgQuery = @"SELECT PackageId, PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission, PkgImage " +
                                         "FROM Packages";

            using (SqlConnection con = TravelExpertsConn.GetConnection())
            {
                using (SqlCommand cmd = new SqlCommand(getPkgQuery, con))
                {
                    con.Open();
                    SqlDataReader reader = cmd.ExecuteReader();
                    Package       packages;
                    while (reader.Read())
                    {
                        packages                     = new Package();
                        packages.PackageId           = Convert.ToInt32(reader["PackageId"]);
                        packages.PkgName             = reader["PkgName"].ToString();
                        packages.PkgStartDate        = Convert.ToDateTime(reader["PkgStartDate"]);
                        packages.PkgEndDate          = Convert.ToDateTime(reader["PkgEndDate"]);
                        packages.PkgDesc             = Convert.ToString(reader["PkgDesc"]);
                        packages.PkgBasePrice        = Convert.ToInt32(reader["PkgBasePrice"]);
                        packages.PkgAgencyCommission = Convert.ToInt32(reader["PkgAgencyCommission"]);
                        packages.PkgImage            = reader["PkgImage"].ToString();
                        packageList.Add(packages);
                    }
                    con.Close();
                }
            }
            return(packageList);
        }
Exemplo n.º 4
0
        // edit customer
        public static int EditCustomer(int id, Customer newCust)
        {
            int    updateCount = 0;
            string updateQuery = @"UPDATE Customers " +
                                 "SET CustID = @CustID, " +
                                 "CustFirstName = @CustFirstName, " +
                                 "CustLastName = @CustLastName, " +
                                 "CustAddress = @CustAddress, " +
                                 "CustCity = @CustCity, " +
                                 "CustProv = @CustProv, " +
                                 "CustPostal = @CustPostal, " +
                                 "CustCountry = @CustCountry, " +
                                 "CustHomePhone = @CustHomePhone, " +
                                 "CustBusPhone = @CustBusPhone, " +
                                 "CustEmail = @CustEmail, " +
                                 "CustPass = @CustPass " +
                                 "WHERE CustomerId = @CurrentCustId";

            using (SqlConnection con = TravelExpertsConn.GetConnection())
            {
                using (SqlCommand sqlCommand = new SqlCommand(updateQuery, con))
                {
                    string password = Convert.ToString(newCust.CustPass);
                    var    hash     = PassHash.SecurePasswordHasher.Hash(password);

                    sqlCommand.Parameters.AddWithValue("@CurrentCustId", id);
                    sqlCommand.Parameters.AddWithValue("@CustID", newCust.CustID);
                    sqlCommand.Parameters.AddWithValue("@CustFirstName", newCust.CustFirstName);
                    sqlCommand.Parameters.AddWithValue("@CustLastName", newCust.CustLastName);
                    sqlCommand.Parameters.AddWithValue("@CustAddress", newCust.CustAddress);
                    sqlCommand.Parameters.AddWithValue("@CustCity", newCust.CustCity);
                    sqlCommand.Parameters.AddWithValue("@CustProv", newCust.CustProv);
                    sqlCommand.Parameters.AddWithValue("@CustPostal", newCust.CustPostal);
                    sqlCommand.Parameters.AddWithValue("@CustCountry", newCust.CustCountry);
                    sqlCommand.Parameters.AddWithValue("@CustHomePhone", newCust.CustHomePhone);
                    sqlCommand.Parameters.AddWithValue("@CustBusPhone", newCust.CustBusPhone);
                    sqlCommand.Parameters.AddWithValue("@CustEmail", newCust.CustEmail);

                    //if (newCust.AgentId == null)
                    //{
                    //    sqlCommand.Parameters.AddWithValue("@AgentId", DBNull.Value);
                    //}
                    //else
                    //{
                    //    sqlCommand.Parameters.AddWithValue("@AgentId", Convert.ToInt32(newCust.AgentId));
                    //}

                    sqlCommand.Parameters.AddWithValue("@CustPass", hash);
                    con.Open();
                    sqlCommand.ExecuteNonQuery();
                }
            }
            return(updateCount);
        }
Exemplo n.º 5
0
        // return customer details
        public static Customer CustomerDetails(int custID)
        {
            Customer details          = null;
            string   noteDetailsQuery = @"SELECT CustID, CustFirstName, CustLastName, CustAddress, CustCity, CustProv, CustPostal, CustCountry, CustHomePhone, CustBusPhone, CustEmail " +
                                        "FROM Customers WHERE CustomerId = @CustomerId";

            using (SqlConnection con = TravelExpertsConn.GetConnection())
            {
                using (SqlCommand cmd = new SqlCommand(noteDetailsQuery, con))
                {
                    cmd.Parameters.AddWithValue("@CustomerId", custID);
                    con.Open();
                    SqlDataReader reader = cmd.ExecuteReader();

                    if (reader.Read())
                    {
                        details               = new Customer();
                        details.CustID        = reader["CustID"].ToString();
                        details.CustFirstName = reader["CustFirstName"].ToString();
                        details.CustLastName  = reader["CustLastName"].ToString();
                        details.CustAddress   = reader["CustAddress"].ToString();
                        details.CustCity      = reader["CustCity"].ToString();
                        details.CustProv      = reader["CustProv"].ToString();
                        details.CustPostal    = reader["CustPostal"].ToString();
                        details.CustCountry   = reader["CustCountry"].ToString();
                        details.CustHomePhone = reader["CustHomePhone"].ToString();
                        details.CustBusPhone  = reader["CustBusPhone"].ToString();
                        details.CustEmail     = reader["CustEmail"].ToString();

                        //if (reader["AgentId"] != DBNull.Value)
                        //{
                        //    details.AgentId = Convert.ToInt32(reader["AgentId"]);
                        //}
                        //else
                        //{
                        //    details.AgentId = null;
                        //}
                    }
                    con.Close();
                }
            }
            return(details);
        }
Exemplo n.º 6
0
        // list function for retrieving AgentIds - created for later enabling
        public static List <int?> GetAgentIdDropdown()
        {
            List <int?> agentIdList     = new List <int?>();
            string      getAgentIdQuery = @"SELECT AgentId FROM Agents";

            using (SqlConnection con = TravelExpertsConn.GetConnection())
            {
                using (SqlCommand cmd = new SqlCommand(getAgentIdQuery, con))
                {
                    con.Open();
                    SqlDataReader reader = cmd.ExecuteReader();
                    Customer      Agents = new Customer();
                    while (reader.Read())
                    {
                        Agents.AgentId = Convert.ToInt32(reader["AgentId"]);
                        agentIdList.Add(Agents.AgentId);
                    }
                    con.Close();
                }
            }
            return(agentIdList);
        }
        public static List <BookingDetail> getPurchasedDetails(int custID)
        {
            List <BookingDetail> purchasedDetails = new List <BookingDetail>();
            BookingDetail        bDetails;

            string getPurchasedDetailsQuery =
                "SELECT bd.BookingId, bd.Description, bd.Destination, bd.BasePrice, bd.AgencyCommission " +
                "FROM Customers AS cs " +
                "INNER JOIN Bookings AS bk " +
                "ON cs.CustomerId = bk.CustomerId " +
                "INNER JOIN BookingDetails AS bd " +
                "ON bk.BookingId = bd.BookingId " +
                "WHERE cs.CustomerId = @CustomerId " +
                "ORDER BY bd.BookingId";

            using (SqlConnection con = TravelExpertsConn.GetConnection())
            {
                using (SqlCommand cmd = new SqlCommand(getPurchasedDetailsQuery, con))
                {
                    cmd.Parameters.AddWithValue("@CustomerId", custID);
                    con.Open();
                    SqlDataReader reader = cmd.ExecuteReader();

                    while (reader.Read())
                    {
                        bDetails                  = new BookingDetail();
                        bDetails.BookingId        = (int)reader["BookingId"];
                        bDetails.Description      = reader["Description"].ToString();
                        bDetails.Destination      = reader["Destination"].ToString();
                        bDetails.BasePrice        = (decimal)reader["BasePrice"];
                        bDetails.AgencyCommission = (decimal)reader["AgencyCommission"];
                        purchasedDetails.Add(bDetails);
                    }
                    con.Close();
                }
            }
            return(purchasedDetails);
        }