public static void loadAccount()
{
DataSet dsAccount = new DataSet();
SQLConnection.Instance.OpenConnection();
MySqlCommand AccountData = new MySqlCommand("select * from Account where accountID = '" + accountID + "';", SQLConnection.Instance.GetConnection());
MySqlDataAdapter daAccount = new MySqlDataAdapter(AccountData);
daAccount.Fill(dsAccount);
SQLConnection.Instance.CloseConnection();
accountObject[0] = new AccountP();
DataRow dataRow = dsAccount.Tables[0].Rows[0];
accountObject[0].setFirstName((string)dataRow[2]);
accountObject[0].setMidName((string)dataRow[3]);
accountObject[0].setLastName((string)dataRow[4]);
accountObject[0].setAddress((string)dataRow[5]);
accountObject[0].setState((string)dataRow[6]);
accountObject[0].setZipCode(Convert.ToInt32(dataRow[7]));
accountObject[0].setPhone((string)dataRow[8]);
accountObject[0].setCity((string)dataRow[9]);
}
public void fillLabels()
{
reserve_textBox1.ReadOnly = true;
reserve_textBox1.Text = RandomStr();
ticket = reserve_textBox1.Text;
flight_label.Text = "From " + FlightP.getDepName() + " to " + FlightP.getArrName();
reserve_label.Text += AccountP.getAccountName();
payment_textBox2.Text = "$ " + Price.getCharge().ToString();
}
/// <summary>
/// Seat button Calls the class Passenger Container that will save all the passengers in a list to use for the seat form.
/// The seat form is created and Passenger's form is closed.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void seat_button2_Click(object sender, EventArgs e)
{
//fill passenger list in class
//PassengerContainer.loadPassengers(FlightP.getFlightNumber(), AccountP.getAccountID());
PassengerContainer.loadPassengers(FlightP.getFlightNumber(), AccountP.getAccountID());
Price.loadPrices(FlightP.getFlightNumber());
Price.resetDiscount();
Seating nfseat = new Seating();
this.Close();
}
private void fillEmail()
{
if (string.IsNullOrEmpty(Customer.getUserName()))
{
Customer.setUserName(AccountP.getAccountID());
username = Customer.getUserName();
}
else
{
username = Customer.getUserName();
}
//MessageBox.Show(Customer.getEmail());
SendEmail(Customer.getEmail());
}
private void fillGrid()
{
PassengerContainer.loadPassengers(FlightP.getFlightNumber(), AccountP.getAccountID());
}
private void insertTicket()
{
//for all passengers you insert ticket number
MySqlCommand saveReserve = new MySqlCommand("Update Passenger Set TicketID = '" + ticket + "' where FlightID = '" + FlightP.getFlightNumber() + "' and AccountID = '" + AccountP.getAccountID() + "';", SQLConnection.Instance.GetConnection());
saveReserve.ExecuteNonQuery();
}
private void insertReserve()
{
//pass on further
DateTime dateTime = DateTime.UtcNow.Date;
string today = dateTime.ToString("d");
string date = DateTime.Parse(today).ToString("yyyy-MM-dd");
//Price.setPaymentID(AccountP.getAccountID());
MySqlCommand reserveFlight = new MySqlCommand("insert into Reserve values('','" + date + "', '" + ticket + "', '" + AccountP.getAccountID() + "' , '" + Price.getPaymentID() + "', '" + FlightP.getFlightNumber() + "' );", SQLConnection.Instance.GetConnection());
reserveFlight.ExecuteNonQuery();
}
private static void SendEmail(string emailTo)
{
string smtpAddress = "smtp.gmail.com";
int portNumber = 587;
bool enableSSL = true;
string emailFrom = "*****@*****.**";
string password = "******";
string subject = "Reservation Remove";
string body = "Hi,\nThis email to confirm that reservation " + ticketID + " has been removed from your account " + AccountP.getAccountName();
try
{
using (MailMessage mail = new MailMessage())
{
mail.From = new MailAddress(emailFrom);
mail.To.Add(emailTo);
mail.Subject = subject;
mail.Body = body;
mail.IsBodyHtml = true;
// Can set to false, if you are sending pure text.
//mail.Attachments.Add(new Attachment("C:\\SomeFile.txt"));
//mail.Attachments.Add(new Attachment("C:\\SomeZip.zip"));
using (SmtpClient smtp = new SmtpClient(smtpAddress, portNumber))
{
smtp.Credentials = new NetworkCredential(emailFrom, password);
smtp.EnableSsl = enableSSL;
smtp.Send(mail);
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
////reserve
//public static void setReserveID(int res)
//{
// ReserveID = res;
//}
//public static int getReserveID()
//{
// return ReserveID;
//}
//if empty returns true if not empty returns false
public static bool isEmptyReseve()
{
bool found = true;
int check;
SQLConnection.Instance.OpenConnection();
MySqlCommand findReserves = new MySqlCommand("select count(AccountID) from Reserve where AccountID = '" + AccountP.getAccountID() + "';", SQLConnection.Instance.GetConnection());
check = Convert.ToInt32(findReserves.ExecuteScalar());
SQLConnection.Instance.CloseConnection();
if (check != 0)
{
found = false;
numberofReserves = check;
}
return(found);
}
private void login_login_button_Click(object sender, EventArgs e)
{
//searches customer table if user credentials exist
MySqlCommand SelectCommand = new MySqlCommand("select * from Customer where UserName='******' and Password='******' ; ", SQLConnection.Instance.GetConnection());
MySqlDataReader myReader;
//opens our connection string
SQLConnection.Instance.OpenConnection();
myReader = SelectCommand.ExecuteReader();
bool found = false;
//reads to find user credentials entered
while (myReader.Read())
{
found = true;
}
//if credentials found we save the users account number to use in our other forms
if (found)
{
myReader.Close(); //need to close reader to use executeSacalar()
//finds thes customerID which is the same as customer ID
MySqlCommand findCustomer = new MySqlCommand("select CustomerID from Customer where UserName='******' and Password='******' ; ", SQLConnection.Instance.GetConnection());
AccountP.setAccountID((Convert.ToInt32(findCustomer.ExecuteScalar()))); //saving user account number to use in other forms
Customer.setCustomerID(AccountP.getAccountID()); //since both are the same
correct = true;
MessageBox.Show("Loged in Successful.");
}
else
{
MessageBox.Show("User does not exist.");
}
SQLConnection.Instance.CloseConnection();
//control flow if credentials are valid will let user proceed to next form or remain in form to enter valid credentials
if (correct)
{
this.Close();
Account account = new Account();
}
else
{
login_user_textbox.Clear();
login_pass_textbox.Clear();
}
////bool valid = IsValid(login_user_textbox.Text, login_pass_textbox.Text);
//if (valid)
//{
// //searches customer table if user credentials exist
// MySqlCommand SelectCommand = new MySqlCommand("select * from Customer where UserName='******' and Password='******' ; ", SQLConnection.Instance.GetConnection());
// MySqlDataReader myReader;
// //opens our connection string
// SQLConnection.Instance.OpenConnection();
// myReader = SelectCommand.ExecuteReader();
// bool found = false;
// //reads to find user credentials entered
// while (myReader.Read())
// {
// found = true;
// }
// //string welcomeMsg = LoginResult(found);
// //myReader.Close();
// //MessageBox.Show(welcomeMsg);
// //if credentials found we save the users account number to use in our other forms
// if (found)
// {
// myReader.Close(); //need to close reader to use executeSacalar()
// //LoginProcess();
// //finds thes customerID which is the same as customer ID
// MySqlCommand findCustomer = new MySqlCommand("select CustomerID from Customer where UserName='******' and Password='******' ; ", SQLConnection.Instance.GetConnection());
// AccountP.setAccountID((int)findCustomer.ExecuteScalar()); //saving user account number to use in other forms
// Customer.setCustomerID(AccountP.getAccountID()); //since both are the same
// correct = true;
// MessageBox.Show("Loged in Successful.");
// }
// else
// {
// MessageBox.Show("User does not exist.");
// }
// SQLConnection.Instance.CloseConnection();
// //control flow if credentials are valid will let user proceed to next form or remain in form to enter valid credentials
// if (correct)
// {
// this.Close();
// Account account = new Account();
// }
// else
// {
// login_user_textbox.Clear();
// login_pass_textbox.Clear();
// }
//}
//else
//{
// MessageBox.Show("Invalid user name/password");
//}
}
/// <summary>
/// Changes the Label in account form to display the Account owner's
/// first and last name
/// </summary>
private void getLabel()
{
account_label.Text += AccountP.getAccountName();
}
/// <summary>
/// This function save the account variables to display in account_update form
/// for user to change easily
/// </summary>
public void getData() //save account information to use in account_update form
{
AccountP.loadAccount();
}
/// <summary>
/// Save is click the functions check to see which textboxes where change
/// and if they were change runs a query to change the account at that specific variable
/// accordingly. After running the if statement, account form is called again with the new variable changes appearing
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void save_button_Click(object sender, EventArgs e)
{
SQLConnection.Instance.OpenConnection();
if (fname_textBox1.Text != FirstName)
{
MySqlCommand SelectCommand = new MySqlCommand("UPDATE Account Set FirstName = '" + fname_textBox1.Text + "' where AccountID = '" + AccountP.getAccountID() + "'; ", SQLConnection.Instance.GetConnection());
SelectCommand.ExecuteNonQuery();
}
if (Mname_textBox2.Text != MidName)
{
MySqlCommand SelectCommand = new MySqlCommand("UPDATE Account Set MidName = '" + Mname_textBox2.Text + "' where AccountID = '" + AccountP.getAccountID() + "'; ", SQLConnection.Instance.GetConnection());
SelectCommand.ExecuteNonQuery();
}
if (Lname_textBox3.Text != LastName)
{
MySqlCommand SelectCommand = new MySqlCommand("UPDATE Account Set LastName = '" + Lname_textBox3.Text + "' where AccountID = '" + AccountP.getAccountID() + "'; ", SQLConnection.Instance.GetConnection());
SelectCommand.ExecuteNonQuery();
}
if (address_textBox4.Text != Address)
{
MySqlCommand SelectCommand = new MySqlCommand("UPDATE Account Set Address = '" + address_textBox4.Text + "' where AccountID = '" + AccountP.getAccountID() + "'; ", SQLConnection.Instance.GetConnection());
SelectCommand.ExecuteNonQuery();
}
if (state_combobox.Text != State)
{
MySqlCommand SelectCommand = new MySqlCommand("UPDATE Account Set State = '" + state_combobox.Text + "' where AccountID = '" + AccountP.getAccountID() + "'; ", SQLConnection.Instance.GetConnection());
SelectCommand.ExecuteNonQuery();
}
if (zip_textBox6.Text != Zipcode.ToString())
{
MySqlCommand SelectCommand = new MySqlCommand("UPDATE Account Set Zipcode = '" + zip_textBox6.Text + "' where AccountID = '" + AccountP.getAccountID() + "'; ", SQLConnection.Instance.GetConnection());
SelectCommand.ExecuteNonQuery();
}
if (phone_textBox7.Text != Phone)
{
MySqlCommand SelectCommand = new MySqlCommand("UPDATE Account Set Phone = '" + phone_textBox7.Text + "' where AccountID = '" + AccountP.getAccountID() + "'; ", SQLConnection.Instance.GetConnection());
SelectCommand.ExecuteNonQuery();
}
if (city_textBox8.Text != City)
{
MySqlCommand SelectCommand = new MySqlCommand("UPDATE Account Set City = '" + city_textBox8.Text + "' where AccountID = '" + AccountP.getAccountID() + "'; ", SQLConnection.Instance.GetConnection());
SelectCommand.ExecuteNonQuery();
}
MessageBox.Show("Changes Successful");
SQLConnection.Instance.CloseConnection();
this.Close();
Account reset = new Account();
}
/// <summary>
/// Saves the user's payment information into the database.
/// Closes the Form and goes to reservation form
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void next_payment_button_Click(object sender, EventArgs e)
{
if (cardnum_textbox.ToString() == "")
{
MessageBox.Show("Please enter the last 4 digit of the card number");
}
else if (cardowner_textBox1.ToString() == "")
{
MessageBox.Show("Please enter the owner of the card");
}
else if (cvc_textbox.ToString() == "")
{
MessageBox.Show("Please enter the cvc");
}
else
{
if (cardnum_textbox.ToString().All(char.IsDigit) || cardnum_textbox.TextLength != 4)
{
MessageBox.Show("Please enter a valid card owner");
cardnum_textbox.Text = "";
}
else if (cardowner_textBox1.ToString().All(char.IsLetter)) //PROBLEM. cardowner is allowing numbers
{
MessageBox.Show("Please enter a correct card owner");
cardowner_textBox1.Text = "";
}
else if (cvc_textbox.ToString().All(char.IsDigit) || cvc_textbox.TextLength != 3)
{
MessageBox.Show("Please enter a correct cvc");
cvc_textbox.Text = "";
}
else
{
SQLConnection.Instance.OpenConnection();
string date = DateTime.Parse(carddateTimePicker1.Text).ToString("yyyy-MM-dd"); //shortens the selected date to date format in the database
MySqlCommand insertPayment = new MySqlCommand("insert into Fare values ('','" + totalPayment + "', '" + cardtype_comboBox1.Text + "', '" + cardnum_textbox.Text + "', '" + cardowner_textBox1.Text + "' , '" + date + "', '" + cvc_textbox.Text + "', '" + AccountP.getAccountID() + "', '" + FlightP.getFlightNumber() + "'); ", SQLConnection.Instance.GetConnection());
insertPayment.ExecuteNonQuery();
MySqlCommand findFareID = new MySqlCommand("Select FareID from Fare where Amount = '" + totalPayment + "' and FlightID = '" + FlightP.getFlightNumber() + "';", SQLConnection.Instance.GetConnection());
Price.setPaymentID(Convert.ToInt32(findFareID.ExecuteScalar()));
Price.setCharge(totalPayment);
SQLConnection.Instance.CloseConnection();
this.Close();
Reserve rs = new Reserve();
}
}
}
/// <summary>
/// Querys to find the amount of each type of passengers
/// for example how many first class passengers were selected for the passenger
/// in this specific flight
/// </summary>
private void getClass()
{
SQLConnection.Instance.OpenConnection();
MySqlCommand findFirstClass = new MySqlCommand("select count(Class) from Passenger where FlightID = '" + FlightP.getFlightNumber() + "' and AccountID = '" + AccountP.getAccountID() + "' and Class = 'F';", SQLConnection.Instance.GetConnection());
MySqlCommand findBusinessClass = new MySqlCommand("select count(Class) from Passenger where FlightID = '" + FlightP.getFlightNumber() + "' and AccountID = '" + AccountP.getAccountID() + "' and Class = 'B';", SQLConnection.Instance.GetConnection());
MySqlCommand findEconomicClass = new MySqlCommand("select count(Class) from Passenger where FlightID = '" + FlightP.getFlightNumber() + "' and AccountID = '" + AccountP.getAccountID() + "' and Class = 'E';", SQLConnection.Instance.GetConnection());
FirstClassCount = Convert.ToInt32(findFirstClass.ExecuteScalar());
BusinessCount = Convert.ToInt32(findBusinessClass.ExecuteScalar());
EconomicCount = Convert.ToInt32(findEconomicClass.ExecuteScalar());
SQLConnection.Instance.CloseConnection();
}
/// <summary>
/// Add Passenger button calls the query with the information provided by the user to add to database
/// and calls clearboxes and fillform to prepare form for the next passenger(s).
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button1_Click(object sender, EventArgs e)
{
if (fname_textBox1.ToString() == "")
{
MessageBox.Show("Please enter a first name");
}
else if (lname_textBox3.ToString() == "")
{
MessageBox.Show("Please enter a last name");
}
else if (age_textBox1.ToString() == "")
{
MessageBox.Show("Please enter an age");
}
else
{
if (fname_textBox1.ToString().All(char.IsLetter))
{
MessageBox.Show("Please enter a correct first name");
fname_textBox1.Text = "";
}
else if (lname_textBox3.ToString().All(char.IsLetter))
{
MessageBox.Show("Please enter a correct last name");
lname_textBox3.Text = "";
}
else if (age_textBox1.ToString().All(char.IsDigit))
{
MessageBox.Show("Error. Please enter an actual age");
age_textBox1.Text = "";
}
else
{
SQLConnection.Instance.OpenConnection();
string date = DateTime.Parse(birthDateCalender.Text).ToString("yyyy-MM-dd");
string agender = gender_comboBox2.Text.ToString();
char letter = agender[0];
string getAge = age_textBox1.Text.ToString();
char age = getAge[0];
MySqlCommand SelectCommand = new MySqlCommand("insert into Passenger values('', '" + fname_textBox1.Text + "', '" + Mname_textBox2.Text + "', '" + lname_textBox3.Text + "', '" + age + "','" + letter + "' ,'" + date + "','', '" + AccountP.getAccountID() + "', '" + FlightP.getFlightNumber() + "', '', '' ); ", SQLConnection.Instance.GetConnection());
SelectCommand.ExecuteNonQuery();
SQLConnection.Instance.CloseConnection();
//clear
//load label second passenger until list empty
clearBoxes();
fillForm();
}
}
}
private void fillReserveBox()
{
string ticket;
int flightNum;
string name;
SQLConnection.Instance.OpenConnection();
MySqlCommand findSlots = new MySqlCommand("Select * from Reserve where AccountID = '" + AccountP.getAccountID() + "';", SQLConnection.Instance.GetConnection());
MySqlDataReader myReader;
myReader = findSlots.ExecuteReader();
while (myReader.Read())
{
ticket = myReader.GetString("ticketNo");
flightNum = myReader.GetInt32("FlightID");
name = ticket + " " + flightNum;
reservations_comboBox1.Items.Add(ticket);
flightsList.Add(name);
}
myReader.Close();
SQLConnection.Instance.CloseConnection();
}
/// <summary>
/// Singup button is click creates a new customer entry in database and stores the customerID to create
/// the account for the created customer
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void signup_signup_button_Click(object sender, EventArgs e)
{
//if (DataValidation.IsBlank(signup_user_textbox.Text))
//{
// MessageBox.Show("Please enter a username");
//}
//else if (DataValidation.IsBlank(signup_password_textbox.Text))
//{
// MessageBox.Show("Please enter a password");
//}
//else if (DataValidation.IsBlank(signup_email_textbox.Text))
//{
// MessageBox.Show("Please enter an email");
//}
//else if (DataValidation.IsBlank(acct_fname.Text))
//{
// MessageBox.Show("Please enter an first name");
//}
//else if (DataValidation.IsBlank(acct_lname.Text))
//{
// MessageBox.Show("Please enter a last name");
//}
//else if (DataValidation.IsBlank(acct_adr.Text))
//{
// MessageBox.Show("Please enter an address");
//}
//else if (DataValidation.IsBlank(acct_zip.Text))
//{
// MessageBox.Show("Please enter a zip code");
//}
//else if (DataValidation.IsBlank(acct_phone.Text))
//{
// MessageBox.Show("Please enter a phone number");
//}
//else if (DataValidation.IsBlank(acct_city.Text))
//{
// MessageBox.Show("Please enter a city");
//}
//else
//{
// if (!DataValidation.IsAllLetters(acct_fname.Text))
// {
// MessageBox.Show("First name must only have letters");
// acct_fname.Text = "";
// }
// else if (!DataValidation.IsAllLetters(acct_lname.Text))
// {
// MessageBox.Show("Last name must only have letters");
// acct_lname.Text = "";
// }
// else
// {
// if (!DataValidation.IsAllDigits(acct_zip.Text) || !DataValidation.IsLength(acct_zip.Text, 5))
// {
// MessageBox.Show("Incorrect zip code. Please enter again");
// acct_zip.Text = "";
// }
// else if (!DataValidation.IsLength(acct_phone.Text, 12))
// {
// MessageBox.Show("Incorrect format of phone. Please enter again");
// acct_phone.Text = "";
// //!DataValidation.IsAllDigits(acct_phone.Text) ||
// }
// else if (!DataValidation.IsEmail(signup_email_textbox.Text))
// {
// MessageBox.Show("Incorrect format of email.");
// signup_email_textbox.Text = "";
// }
// else if (!DataValidation.IsAllLetters(acct_city.Text))
// {
// MessageBox.Show("City must only contains letters");
// acct_city.Text = "";
// }
// else
// {
//inserts new customer into customer table
MySqlCommand SelectCommand = new MySqlCommand("insert into Customer values ('','" + this.signup_user_textbox.Text + "', '" + this.signup_password_textbox.Text + "', '" + this.signup_email_textbox.Text + "'); ", SQLConnection.Instance.GetConnection());
Customer.setEmail(this.signup_email_textbox.Text);
SQLConnection.Instance.OpenConnection(); //open database connection
//adds cusomter to database
SelectCommand.ExecuteNonQuery();
//finds customerID of created user to be stored in variable
MySqlCommand findCustomer = new MySqlCommand("select CustomerID from Customer where UserName='******' and Password='******' ; ", SQLConnection.Instance.GetConnection());
Customer.setCustomerID(Convert.ToInt32(findCustomer.ExecuteScalar()));
AccountP.setAccountID(Customer.getCustomerID());
//creats account for customer in account table
MySqlCommand accountMaker = new MySqlCommand("insert into Account values ('', '" + Customer.getCustomerID() + "','" + this.acct_fname.Text + "', '" + this.acct_mid.Text +
"', '" + this.acct_lname.Text + "', '" + this.acct_adr.Text + "', '" + this.acct_state_comboBox1.Text + "', '" + this.acct_zip.Text + "', '" + this.acct_phone.Text + "','" + this.acct_city.Text + "'); ", SQLConnection.Instance.GetConnection());
accountMaker.ExecuteNonQuery();
SQLConnection.Instance.CloseConnection(); //closes connection with database
///get customer username
Customer.setUserName(Customer.getCustomerID());
customer_username = Customer.getUserName();
SendEmail(signup_email_textbox.Text);
MessageBox.Show("Account Created. An email has been sent to your registered email"); //shows only if the code prior works
this.Close();
Account account = new Account();
//}
//}
//}
}
