// get the customers information for checking with database
        public static Customer GetCustomerInfo(string userName)
        {
            Customer cust  = null;
            string   query = "SELECT CustomerId, CustFirstName, CustLastName, CustAddress, CustCity, CustProv, CustPostal, CustCountry, " +
                             "CustHomePhone, CustBusPhone, CustEmail, UserName " +
                             "FROM Customers " +
                             "WHERE UserName = @UserName";

            using (SqlConnection connection = new SqlConnection(TravelExpertsDB.GetConnectionString()))
            {
                using (SqlCommand cmd = new SqlCommand(query, connection))
                {
                    cmd.Parameters.AddWithValue("@UserName", userName);
                    connection.Open();
                    SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    while (reader.Read())
                    {
                        cust               = new Customer();
                        cust.CustomerId    = Convert.ToInt32(reader["CustomerId"]);
                        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.CustPostal    = reader["CustPostal"].ToString();
                        cust.CustCountry   = reader["CustCountry"].ToString();
                        cust.CustHomePhone = reader["CustHomePhone"].ToString();
                        cust.CustBusPhone  = reader["CustBusPhone"].ToString();
                        cust.CustEmail     = reader["CustEmail"].ToString();
                        cust.UserName      = reader["UserName"].ToString();
                    }
                }
            }
            return(cust);
        }
        // register customers information
        public static void CustomerRegister(Customer customer)
        {
            string sql = "INSERT INTO Customers "
                         + "(CustFirstName, CustLastName, CustAddress, CustCity, CustProv, CustPostal, CustCountry, CustHomePhone, CustBusPhone, CustEmail, UserName, Password) "
                         + "VALUES (@CustFirstName, @CustLastName, @CustAddress, @CustCity, @CustProv, @CustPostal, @CustCountry, @CustHomePhone, @CustBusPhone, @CustEmail, @UserName, @Password)";

            using (SqlConnection con = new SqlConnection(TravelExpertsDB.GetConnectionString()))
            {
                using (SqlCommand cmd = new SqlCommand(sql, con))
                {
                    cmd.Parameters.AddWithValue("CustFirstName", customer.CustFirstName);
                    cmd.Parameters.AddWithValue("CustLastName", customer.CustLastName);
                    cmd.Parameters.AddWithValue("CustAddress", customer.CustAddress);
                    cmd.Parameters.AddWithValue("CustCity", customer.CustCity);
                    cmd.Parameters.AddWithValue("CustProv", customer.CustProv);
                    cmd.Parameters.AddWithValue("CustPostal", customer.CustPostal);
                    cmd.Parameters.AddWithValue("CustCountry", customer.CustCountry);
                    cmd.Parameters.AddWithValue("CustHomePhone", customer.CustHomePhone);
                    cmd.Parameters.AddWithValue("CustBusPhone", customer.CustBusPhone);
                    cmd.Parameters.AddWithValue("CustEmail", customer.CustEmail);
                    cmd.Parameters.AddWithValue("UserName", customer.UserName);
                    cmd.Parameters.AddWithValue("Password", customer.Password);
                    con.Open();
                    cmd.ExecuteNonQuery();
                }
            }
        }
        //check login credentials
        public static Customer CustomerLogin(string custUserName)
        {
            Customer cust  = null;
            string   query = "SELECT CustomerId, UserName, Password, CustFirstName " +
                             "FROM Customers " +
                             "WHERE UserName = @UserName";

            using (SqlConnection connection = new SqlConnection(TravelExpertsDB.GetConnectionString()))
            {
                using (SqlCommand cmd = new SqlCommand(query, connection))
                {
                    cmd.Parameters.AddWithValue("@UserName", custUserName);
                    connection.Open();
                    SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    while (reader.Read())
                    {
                        cust               = new Customer();
                        cust.CustomerId    = Convert.ToInt32(reader["CustomerId"]);
                        cust.UserName      = reader["UserName"].ToString();
                        cust.Password      = reader["Password"].ToString();
                        cust.CustFirstName = reader["CustFirstName"].ToString();
                    }
                }
            }
            return(cust);
        }
        // edit customers information
        public static int UpdateCustomer(Customer original_Customer,
                                         Customer new_Customer)
        {
            int updateCount = 0;

            string sql = "UPDATE Customers SET "
                         + "CustFirstName = @CustFirstName, "
                         + "CustLastName = @CustLastName, "
                         + "CustAddress = @CustAddress, "
                         + "CustCity = @CustCity, "
                         + "CustProv = @CustProv, "
                         + "CustPostal = @CustPostal, "
                         + "CustCountry = @CustCountry, "
                         + "CustHomePhone = @CustHomePhone, "
                         + "CustBusPhone = @CustBusPhone, "
                         + "CustEmail = @CustEmail, "
                         + "UserName = @UserName, "
                         + "Password = @Password "
                         + "WHERE CustomerId = @original_CustomerId";

            using (SqlConnection con = new SqlConnection(TravelExpertsDB.GetConnectionString()))
            {
                using (SqlCommand cmd = new SqlCommand(sql, con))
                {
                    cmd.Parameters.AddWithValue("CustFirstName", new_Customer.CustFirstName);
                    cmd.Parameters.AddWithValue("CustLastName", new_Customer.CustLastName);
                    cmd.Parameters.AddWithValue("CustAddress", new_Customer.CustAddress);
                    cmd.Parameters.AddWithValue("CustCity", new_Customer.CustCity);
                    cmd.Parameters.AddWithValue("CustProv", new_Customer.CustProv);
                    cmd.Parameters.AddWithValue("CustPostal", new_Customer.CustPostal);
                    cmd.Parameters.AddWithValue("CustCountry", new_Customer.CustCountry);
                    cmd.Parameters.AddWithValue("CustHomePhone", new_Customer.CustHomePhone);
                    cmd.Parameters.AddWithValue("CustBusPhone", new_Customer.CustBusPhone);
                    cmd.Parameters.AddWithValue("CustEmail", new_Customer.CustEmail);
                    cmd.Parameters.AddWithValue("UserName", new_Customer.UserName);
                    cmd.Parameters.AddWithValue("Password", new_Customer.Password);
                    cmd.Parameters.AddWithValue("original_CustomerId", original_Customer.CustomerId);
                    con.Open();
                    updateCount = cmd.ExecuteNonQuery();
                }
            }
            return(updateCount);
        }
示例#5
0
        public static List <CustomerBookings> GetPackBookings(int custId) // custID = userID which is passed by the controller
        {
            //make an empty list
            List <CustomerBookings> accPackBookingsList = new List <CustomerBookings>();

            string sql1 = "Select C.CustFirstName, P.ProdName,BD.BasePrice, Pack.PkgName,Pack.PkgBasePrice,B.BookingId from "
                          + "Customers as C join Bookings as B "
                          + "on C.CustomerId= B.CustomerId "
                          + "join Packages as Pack on "
                          + "B.PackageId = Pack.PackageId "
                          + "join BookingDetails as BD "
                          + "on B.BookingId = BD.BookingId "
                          + "join  Products_Suppliers as PS "
                          + "on BD.ProductSupplierId= PS.ProductSupplierId "
                          + "join Products as P "
                          + "on Ps.ProductId=P.ProductId "
                          + "where C.CustomerId = @CustomerId ";

            string sql2 = "Select C.CustFirstName, P.ProdName, BD.BasePrice, B.BookingId from "
                          + "Customers as C join Bookings as B "
                          + "on C.CustomerId= B.CustomerId "
                          + "join BookingDetails as BD "
                          + "on B.BookingId = BD.BookingId "
                          + "join  Products_Suppliers as PS "
                          + "on BD.ProductSupplierId= PS.ProductSupplierId "
                          + "join Products as P "
                          + "on Ps.ProductId=P.ProductId "
                          + "where C.CustomerId = @CustomerId ";


            using (SqlConnection con = new SqlConnection(TravelExpertsDB.GetConnectionString()))
            {
                //used in PACKAGES after PRODUCTS
                CustomerBookings packbooking;
                packbooking = new CustomerBookings();



                //PRODUCTS
                using (SqlCommand cmd = new SqlCommand(sql2, con))
                {
                    //put userID into sql
                    cmd.Parameters.AddWithValue("@CustomerId", custId);

                    //open connectoin
                    con.Open();
                    SqlDataReader    dr = cmd.ExecuteReader(); //variable to capture all the data that is gathered by sql query
                    CustomerBookings prodbooking;              // creating object based off of customerBookings model

                    decimal?total = 0;                         // empty var for use later

                    while (dr.Read())                          //for each set of data that is sent by the sql query
                    {
                        if (dr["ProdName"] != null)            // if there is relevant data
                        {
                            //put data into object earlier made object
                            prodbooking = new CustomerBookings();
                            prodbooking.PRODCustFirstName = dr["CustFirstName"].ToString();
                            prodbooking.PRODProdName      = dr["ProdName"].ToString();
                            prodbooking.PRODBasePrice     = Convert.ToDecimal(dr["BasePrice"]);
                            prodbooking.PRODBookingId     = Convert.ToInt32(dr["BookingId"]);

                            //add price to the total for each set of data
                            total += prodbooking.PRODBasePrice;

                            //sending the prod information to the list
                            accPackBookingsList.Add(prodbooking);
                        }
                    }
                    //close the reader
                    dr.Close();

                    //make another object
                    CustomerBookings prodbookingtest = new CustomerBookings();
                    //put data into object
                    prodbookingtest.PRODTotal = total;
                    //send object to the list
                    accPackBookingsList.Add(prodbookingtest);

                    //close connection
                    con.Close();
                }

                //PACKAGES
                //follow same principals as prods, please refer to PRODUCTS for comments
                using (SqlCommand cmd = new SqlCommand(sql1, con))
                {
                    cmd.Parameters.AddWithValue("@CustomerId", custId);


                    con.Open();

                    SqlDataReader dr = cmd.ExecuteReader();

                    decimal?total = 0;


                    while (dr.Read())
                    {
                        if (dr["PkgName"] != null)
                        {
                            packbooking.PACKCustFirstName = dr["CustFirstName"].ToString();
                            packbooking.PACKProdName      = dr["ProdName"].ToString();
                            packbooking.PACKBasePrice     = Convert.ToDecimal(dr["BasePrice"]);
                            packbooking.PACKPkgName       = dr["PkgName"].ToString();
                            packbooking.PACKPkgBasePrice  = Convert.ToDecimal(dr["PkgBasePrice"]);
                            packbooking.PACKBookingId     = Convert.ToInt32(dr["BookingId"]);

                            total += packbooking.PACKPkgBasePrice;

                            //sending the packes information to the list
                            accPackBookingsList.Add(packbooking);
                        }
                    }
                    dr.Close();

                    CustomerBookings packbookingtest = new CustomerBookings();
                    packbookingtest.PACKTotal = total;
                    accPackBookingsList.Add(packbookingtest);
                }
            }
            //return the list to whatever calls the method
            return(accPackBookingsList);
        }