public bool AuthenticateUser(string username, string password)
{
bool allowAccess = false;
string conString = GetConnection.GetConnectionString();
SqlConnection con = new SqlConnection(conString);
string query = "prc_authenticateUser";
con.Open();
SqlDataReader dr;
SqlCommand cmd = new SqlCommand(query, con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@username", username);
cmd.Parameters.AddWithValue("@password", password);
dr = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(dr);
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i]["CustomerId"].ToString().Equals(username))
{
allowAccess = true;
}
}
return(allowAccess);
}
/// <summary>
/// Search flight by specifying maximum price. For eg if a user enters
/// Rs 10000 flights with a fare below Rs 10000 is shown
/// </summary>
/// <param name="source"></param>
/// <param name="destination"></param>
/// <param name="travelClass"></param>
/// <param name="dateOfJourney"></param>
/// <param name="numberOfAdults"></param>
/// <param name="numberOfChildren"></param>
/// <param name="maxPrice"></param>
/// <returns></returns>
public DataTable FilterByPrice
(
string source,
string destination,
string travelClass,
string dateOfJourney,
int numberOfAdults,
int numberOfChildren,
int maxPrice
)
{
DataTable dt = new DataTable();
SqlConnection con = new SqlConnection();
con.ConnectionString = GetConnection.GetConnectionString();
SqlDataReader dr;
SqlCommand cmd;
try
{
//Establish connection with db
con.Open();
string query1 = "prc_displayFlightsFilterByPrice";
//string query1 = "select * from Flight";
cmd = new SqlCommand(query1, con);
cmd.CommandType = CommandType.StoredProcedure;
int numberOfPassengers = numberOfAdults + numberOfChildren;
cmd.Parameters.AddWithValue("@source", source);
cmd.Parameters.AddWithValue("@destination", destination);
cmd.Parameters.AddWithValue("@dateOfJourney", dateOfJourney);
cmd.Parameters.AddWithValue("@numberOfPassengers", numberOfPassengers);
cmd.Parameters.AddWithValue("@class", travelClass);
cmd.Parameters.AddWithValue("@maxPrice", maxPrice);
dr = cmd.ExecuteReader();
dt.Load(dr);
dt.Columns.Add("Fare");
for (int i = 0; i < dt.Rows.Count; i++)
{
int a = Convert.ToInt32(dt.Rows[i]["AdultPrice"]);
a = numberOfAdults * a;
int b = Convert.ToInt32(dt.Rows[i]["ChildPrice"]);
b = numberOfChildren * b;
int c = a + b;
dt.Rows[i]["Fare"] = c;
}
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
return(dt);
}
public int updateBookedTicketBeforePayment
(
string FlightId,
string travelClass,
string DateOfJourney,
string DateOfBooking,
int NumberOfAdults,
int NumberOfChildren,
string TicketFare,
string CustomerId,
string ReferenceId,
string BookingStatus,
string PaymentStatus,
int AdultPrice,
int ChildPrice
)
{
SqlConnection con = new SqlConnection();
con.ConnectionString = GetConnection.GetConnectionString();
SqlCommand cmd;
int cnt = 0;
try
{
//Establish connection with db
con.Open();
string query1 = "prc_BookingStatusBeforePayment";
cmd = new SqlCommand(query1, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@FlightId", FlightId);
cmd.Parameters.AddWithValue("@Class", travelClass);
cmd.Parameters.AddWithValue("@DateOfJourney", DateOfJourney);
cmd.Parameters.AddWithValue("@DateOfBooking", DateOfBooking);
cmd.Parameters.AddWithValue("@NumberOfAdults", NumberOfAdults);
cmd.Parameters.AddWithValue("@NumberOfChildren", NumberOfChildren);
cmd.Parameters.AddWithValue("@TicketFare", TicketFare);
cmd.Parameters.AddWithValue("@CustomerId", CustomerId);
cmd.Parameters.AddWithValue("@ReferenceId", ReferenceId);
cmd.Parameters.AddWithValue("@BookingStatus", BookingStatus);
cmd.Parameters.AddWithValue("@PaymentStatus", PaymentStatus);
cmd.Parameters.AddWithValue("@AdultPrice", AdultPrice);
cmd.Parameters.AddWithValue("@ChildPrice", ChildPrice);
cnt = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
return(cnt);
}
public void ConfirmBooking(string ReferenceId,
string BookingStatus,
string PaymentStatus,
string FlightId,
string DateOfFlight,
string travelClass,
int numberOfPassengers)
{
SqlConnection con = new SqlConnection();
con.ConnectionString = GetConnection.GetConnectionString();
SqlCommand cmd;
int cnt = 0;
con.Open();
//SqlTransaction transaction;
//transaction=con.BeginTransaction();
string query1 = "prc_ConfirmBooking";
cmd = new SqlCommand(query1, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ReferenceId", ReferenceId);
cmd.Parameters.AddWithValue("@BookingStatus", BookingStatus);
cmd.Parameters.AddWithValue("@PaymentStatus", PaymentStatus);
cnt = cmd.ExecuteNonQuery();
string query2 = "prc_ConfirmPassengers";
cmd = new SqlCommand(query2, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ReferenceId", ReferenceId);
cmd.Parameters.AddWithValue("@BookingStatus", BookingStatus);
cnt = cmd.ExecuteNonQuery();
string query3 = "prc_DecrementAvailableSeats";
cmd = new SqlCommand(query3, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@FlightId", FlightId);
cmd.Parameters.AddWithValue("@Class", travelClass);
cmd.Parameters.AddWithValue("@DateOfFlight", DateOfFlight);
cmd.Parameters.AddWithValue("@numberOfPassengers", numberOfPassengers);
cnt = cmd.ExecuteNonQuery();
//transaction.Commit();
}
//Checking Email Id Of User And Verifying Whether It is present or not
public int CheckingEmailIdInDataBase(string EmailId)
{
int cnt = 0;
SqlConnection con = new SqlConnection();
con.ConnectionString = GetConnection.GetConnectionString();
SqlCommand cmd;
try
{
//Establish connection with db
con.Open();
string query1 = "Prc_EmailIdMatching";
cmd = new SqlCommand(query1, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@EmailId", EmailId);
var returnParameter = cmd.Parameters.Add("@Exists", SqlDbType.Int);
returnParameter.Direction = ParameterDirection.ReturnValue;
cmd.ExecuteNonQuery();
int j = Convert.ToInt32(returnParameter.Value);
//int j=Convert.ToInt32(cmd.ExecuteScalar());
if (j == 1)
{
cnt = j;
}
else
{
cnt = j;
}
}
catch (Exception)
{
}
return(cnt);
}//checkEmail
/// <summary>
/// A method for getting search results
/// parameters taken in constructor
/// 6 parameters are - source,destination,class,dateofjourney,dateofreturn,nadults,ninfants
/// </summary>
/// <returns>Data Table</returns>
public DataTable SearchFlights()
{
DataTable dtSearchFlight = new DataTable();
SqlConnection con = new SqlConnection();
con.ConnectionString = GetConnection.GetConnectionString();
SqlDataReader dr;
SqlCommand cmd;
try
{
//Establish connection with db
con.Open();
string query1 = "prc_displayFlights";
cmd = new SqlCommand(query1, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@source", source);
cmd.Parameters.AddWithValue("@destination", destination);
cmd.Parameters.AddWithValue("@dateOfJourney", dateOfJourney);
cmd.Parameters.AddWithValue("@numberOfPassengers", numberOfPassengers);
cmd.Parameters.AddWithValue("@class", travelClass);
dr = cmd.ExecuteReader();
dtSearchFlight.Load(dr);
dtSearchFlight.Columns.Add("Fare");
//Calculate total fare
for (int i = 0; i < dtSearchFlight.Rows.Count; i++)
{
int adultprice = Convert.ToInt32(dtSearchFlight.Rows[i]["AdultPrice"]);
adultprice = numberOfAdults * adultprice;
int childprice = Convert.ToInt32(dtSearchFlight.Rows[i]["ChildPrice"]);
childprice = numberOfChildren * childprice;
int fare = childprice + adultprice;
dtSearchFlight.Rows[i]["Fare"] = fare;
}
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
return(dtSearchFlight);
}
public DataTable GetSource()
{
string conStr = GetConnection.GetConnectionString();
SqlConnection con = new SqlConnection(conStr);
SqlDataReader dr;
SqlCommand cmd;
DataTable dt = new DataTable();
string query;
con.Open();
query = "prc_SelectSource";
cmd = new SqlCommand(query, con);
cmd.CommandType = CommandType.StoredProcedure;
dr = cmd.ExecuteReader();
dt.Load(dr);
con.Close();
return(dt);
}
public DataTable GetDestination(string selectedSource)
{
string conStr = GetConnection.GetConnectionString();
SqlConnection con = new SqlConnection(conStr);
SqlDataReader dr;
SqlCommand cmd;
DataTable dt = new DataTable();
string query;
con.Open();
query = "prc_SelectDestination";
cmd = new SqlCommand(query, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@source", selectedSource);
dr = cmd.ExecuteReader();
dt.Load(dr);
con.Close();
return(dt);
}
public int updatePassengersBeforePayment(int PassengerId,
string ReferenceId,
string PassengerName,
int PassengerAge,
char PassengerGender,
string BookingStatus)
{
SqlConnection con = new SqlConnection();
con.ConnectionString = GetConnection.GetConnectionString();
SqlCommand cmd;
int cnt = 0;
try
{
//Establish connection with db
con.Open();
string query1 = "prc_InsertPassengersBeforePayment";
cmd = new SqlCommand(query1, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@PassengerId", PassengerId);
cmd.Parameters.AddWithValue("@ReferenceId", ReferenceId);
cmd.Parameters.AddWithValue("@PassengerName", PassengerName);
cmd.Parameters.AddWithValue("@PassengerAge", PassengerAge);
cmd.Parameters.AddWithValue("@PassengerGender", PassengerGender);
cmd.Parameters.AddWithValue("@BookingStatus", BookingStatus);
cnt = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
return(cnt);
}
public void CancelTicket(int PassengerId, string ReferenceId, string user)
{
try
{
SqlConnection con = new SqlConnection();
con.ConnectionString = GetConnection.GetConnectionString();
con.Open();
SqlCommand cmd;
string query1 = "prc_UpdateCancelledStatus";
cmd = new SqlCommand(query1, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@PassengerId", PassengerId);
cmd.Parameters.AddWithValue("@ReferenceId", ReferenceId);
cmd.Parameters.AddWithValue("@user", user);
int cnt = cmd.ExecuteNonQuery();
con.Close();
}
catch (Exception) { }
}
public DataTable ViewBookingHistory(string CustomerId)
{
DataTable dt = new DataTable();
SqlConnection con = new SqlConnection();
con.ConnectionString = GetConnection.GetConnectionString();
con.Open();
SqlDataReader dr;
SqlCommand cmd;
string query1 = "prc_BookingHistory";
cmd = new SqlCommand(query1, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@CustomerId", CustomerId);
dr = cmd.ExecuteReader();
dt.Load(dr);
con.Close();
return(dt);
}
}//checkEmail
public int SettingNewPassword(string EmailId, string pwd)
{
int n = 0;
SqlConnection con = new SqlConnection();
con.ConnectionString = GetConnection.GetConnectionString();
SqlCommand cmd;
try
{
//Establish connection with db
con.Open();
string query1 = "Prc_SetNewUserPassword";
cmd = new SqlCommand(query1, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@EmailId", EmailId);
cmd.Parameters.AddWithValue("@NewPwd", pwd);
n = cmd.ExecuteNonQuery();
//int j=Convert.ToInt32(cmd.ExecuteScalar());
}
catch (Exception)
{
}
return(n);
}
public DataTable GetPassengers(string ReferenceId)
{
DataTable dtPassengers = new DataTable();
SqlConnection con = new SqlConnection();
con.ConnectionString = GetConnection.GetConnectionString();
SqlDataReader dr;
SqlCommand cmd;
con.Open();
string query1 = "prc_GetPassengerData";
cmd = new SqlCommand(query1, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ReferenceId", ReferenceId);
dr = cmd.ExecuteReader();
dtPassengers.Load(dr);
con.Close();
return(dtPassengers);
}