public List <Employee> GetAllEmployees()
{
string query = "SELECT * from Employee";
List <Employee> emList = new List <Employee>();
DatabaseConnection dcc = new DatabaseConnection();
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
while (sdr.Read())
{
Employee em = new Employee();
em.EmployeeID = Convert.ToInt32(sdr["EmployeeId"]);
em.Username = sdr["Username"].ToString();
em.Name = sdr["Name"].ToString();
em.Email = sdr["Email"].ToString();
em.Gender = Convert.ToString(sdr["Gender"]);
em.Address = sdr["Address"].ToString();
em.Age = Convert.ToInt32(sdr["Age"]);
em.Question = sdr["Question"].ToString();
em.Password = sdr["Password"].ToString();
emList.Add(em);
}
dcc.CloseConnection();
return(emList);
}
public List <Passenger> GetAllPassengers()
{
string query = "SELECT * from Passengers";
List <Passenger> pList = new List <Passenger>();
DatabaseConnection dcc = new DatabaseConnection();
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
while (sdr.Read())
{
Passenger p = new Passenger();
p.PassengerId = sdr["PassengerId"].ToString();
p.Pname = sdr["Name"].ToString();
p.Pusername = sdr["UserName"].ToString();
p.Pemail = sdr["Email"].ToString();
p.Ppassword = sdr["Password"].ToString();
p.Pgender = sdr["Gender"].ToString();
p.Page = Convert.ToInt32(sdr["Age"]);
p.Paddress = sdr["Address"].ToString();
p.Pquestion = sdr["Question"].ToString();
pList.Add(p);
}
dcc.CloseConnection();
return(pList);
}
public Employee GetEmployees(string employeeId)
{
int id = Convert.ToInt32(employeeId);
Console.WriteLine(id);
string query = "SELECT * from Employee WHERE EmployeeId = " + Convert.ToInt32(employeeId) + " OR Name LIKE '%" + employeeId + "%' OR Username LIKE '%" + employeeId + "%' ";
Employee em = null;
DatabaseConnection dcc = new DatabaseConnection();
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
if (sdr.Read())
{
em = new Employee();
em.EmployeeID = Convert.ToInt32(sdr["EmployeeId"]);
em.Username = sdr["Username"].ToString();
em.Name = sdr["Name"].ToString();
em.Email = sdr["Email"].ToString();
em.Gender = Convert.ToString(sdr["Gender"]);
em.Address = sdr["Address"].ToString();
em.Age = Convert.ToInt32(sdr["Age"]);
em.Question = sdr["Question"].ToString();
em.Password = sdr["Password"].ToString();
}
dcc.CloseConnection();
return(em);
}
public List <Admin> GetAllAdmins()
{
string query = "SELECT * from Admin";
List <Admin> aList = new List <Admin>();
DatabaseConnection dcc = new DatabaseConnection();
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
while (sdr.Read())
{
Admin a = new Admin();
a.AdminId = sdr["AdminId"].ToString();
a.Username = sdr["Username"].ToString();
a.Name = sdr["Name"].ToString();
a.Email = sdr["Email"].ToString();
a.Gender = Convert.ToString(sdr["Gender"]);
a.Address = sdr["Address"].ToString();
a.Age = Convert.ToInt32(sdr["Age"]);
a.Question = sdr["Question"].ToString();
a.Password = sdr["Password"].ToString();
aList.Add(a);
}
dcc.CloseConnection();
return(aList);
}
public Passenger GetPassengers(string userId)
{
string query = "SELECT * from Passengers WHERE PassengerId = '" + userId + "'";
Passenger p = null;
DatabaseConnection dcc = new DatabaseConnection();
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
if (sdr.Read())
{
p = new Passenger();
p.PassengerId = sdr["PassengerId"].ToString();
p.Pusername = sdr["Username"].ToString();
p.Pname = sdr["Name"].ToString();
p.Pemail = sdr["Email"].ToString();
p.Pgender = sdr["Gender"].ToString();
p.Paddress = sdr["Address"].ToString();
p.Page = Convert.ToInt32(sdr["Age"]);
p.Pquestion = sdr["Question"].ToString();
p.Ppassword = sdr["Password"].ToString();
}
dcc.CloseConnection();
return(p);
}
public bool InsertTenant(Tenant t)
{
string query = "INSERT into Tenants VALUES ('" + t.TId + "', '" + t.Tname + "', '" + t.TphnNumber + "', " + t.Rent + ", '" + t.Institution + "')";
try
{
dcc.ConnectWithDB();
int n = dcc.ExecuteSQL(query);
dcc.CloseConnection();
return(true);
}
catch (Exception exp)
{
return(false);
}
}
public Admin GetAdmins(string adminId)
{
string query = "SELECT * from Admin WHERE AdminId = '" + adminId + "'";
Admin a = null;
DatabaseConnection dcc = new DatabaseConnection();
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
if (sdr.Read())
{
a = new Admin();
a.AdminId = sdr["AdminId"].ToString();
a.Username = sdr["Username"].ToString();
a.Name = sdr["Name"].ToString();
a.Email = sdr["Email"].ToString();
a.Gender = Convert.ToString(sdr["Gender"]);
a.Address = sdr["Address"].ToString();
a.Age = Convert.ToInt32(sdr["Age"]);
a.Question = sdr["Question"].ToString();
a.Password = sdr["Password"].ToString();
}
dcc.CloseConnection();
return(a);
}
public List <BookTicket> GetTicket(int ticketId)
{
string query = "SELECT * from BookTicket WHERE BTId = " + ticketId + "";
BookTicket b = null;
List <BookTicket> blist = new List <BookTicket>();
DatabaseConnection dcc = new DatabaseConnection();
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
if (sdr.Read())
{
b = new BookTicket();
b.BookTicketId = Convert.ToInt32(sdr["BTId"]);
b.PassengerId = Convert.ToInt32(sdr["PassengerId"]);
b.FlightId = Convert.ToInt32(sdr["FlightId"]);
b.PassengerUsername = sdr["PassengerUsername"].ToString();
b.PassengerFullName = sdr["PassengerFullname"].ToString();
b.Airplane = sdr["Airplane"].ToString();
b.Source = sdr["Source"].ToString();
b.Destination = sdr["Destination"].ToString();
b.Departure = sdr["Departure"].ToString();
b.Seats = sdr["Seats"].ToString();
b.Cost = Convert.ToInt32(sdr["Cost"]);
blist.Add(b);
}
dcc.CloseConnection();
return(blist);
}
public List <Flight> GetALlFlights()
{
string query = "SELECT * from Flight";
List <Flight> fList = new List <Flight>();
DatabaseConnection dcc = new DatabaseConnection();
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
while (sdr.Read())
{
var f = new Flight();
f.FlightId = sdr["FlightID"].ToString();
f.AirlineName = sdr["AirlineName"].ToString();
f.Source = sdr["Source"].ToString();
f.Destination = sdr["Destination"].ToString();
f.Arrival = Convert.ToString(sdr["Arrival"]);
f.Departure = Convert.ToString(sdr["Departure"]);
f.Cost = Convert.ToInt32(sdr["Cost"]);
fList.Add(f);
}
dcc.CloseConnection();
return(fList);
}
public Bookticket GetTicket(string ticketId)
{
string query = "SELECT * from BookTicket WHERE BookTicketId = '" + ticketId + "'";
Bookticket b = null;
DatabaseConnection dcc = new DatabaseConnection();
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
if (sdr.Read())
{
b = new Bookticket();
b.BookTicketId = sdr["BookTicketId"].ToString();
b.PassengerId = sdr["PassengerId"].ToString();
b.PassengerUsername = sdr["PassengerName"].ToString();
b.Airplane = sdr["Airplane"].ToString();
b.Source = sdr["Source"].ToString();
b.Destination = sdr["Destination"].ToString();
b.Departure = sdr["Departure"].ToString();
b.Arrival = sdr["Arrival"].ToString();
b.Seats = sdr["Seats"].ToString();
b.Cost = Convert.ToInt32(sdr["Cost"]);
}
dcc.CloseConnection();
return(b);
}
public bool Insertcomplain(Complain c)
{
string query = "INSERT into Complains VALUES ('" + c.CId + "', '" + c.Csub + "', '" + c.Cbody + "','" + c.CuserId + "')";
try
{
dcc.ConnectWithDB();
int n = dcc.ExecuteSQL(query);
dcc.CloseConnection();
return(true);
}
catch (Exception exp)
{
return(false);
}
}
public List <Airport> GetAllAirports()
{
string query = "SELECT * from Airport";
List <Airport> airList = new List <Airport>();
DatabaseConnection dcc = new DatabaseConnection();
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
while (sdr.Read())
{
Airport air1 = new Airport();
air1 = new Airport();
air1.AirportId = sdr["AirportID"].ToString();
air1.AirportName = sdr["AirportName"].ToString();
air1.City = sdr["City"].ToString();
air1.Airportcode = sdr["AirportCode"].ToString();
airList.Add(air1);
}
dcc.CloseConnection();
return(airList);
}
public List <Bookticket> GetAllTickets()
{
string query = "SELECT * from BookTicket";
List <Bookticket> tList = new List <Bookticket>();
DatabaseConnection dcc = new DatabaseConnection();
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
while (sdr.Read())
{
Bookticket b = new Bookticket();
b.BookTicketId = sdr["BookTicketId"].ToString();
b.PassengerId = sdr["PassengerId"].ToString();
b.PassengerUsername = sdr["PassengerName"].ToString();
b.Airplane = sdr["Airplane"].ToString();
b.Source = sdr["Source"].ToString();
b.Destination = sdr["Destination"].ToString();
b.Departure = sdr["Departure"].ToString();
b.Arrival = sdr["Arrival"].ToString();
b.Seats = sdr["Seats"].ToString();
b.Cost = Convert.ToInt32(sdr["Cost"]);
tList.Add(b);
}
dcc.CloseConnection();
return(tList);
}
public AllUsers GetUser(string userId)
{
string query = "SELECT * FROM AllUsers WHERE UserType LIKE '%" + userId + "%' OR Username LIKE '%" + userId + "%' OR Fullname LIKE '%" + userId + "%' ";
string query2 = "SELECT * FROM AllUsers WHERE UserId = " + userId + "";
AllUsers a = new AllUsers();
DatabaseConnection dcc = new DatabaseConnection();
dcc.ConnectWithDB();
float y;
try
{
y = Convert.ToInt32(userId) / 2;
y = 1;
}
catch (Exception er) { y = -10; }
if (y + 1 >= 1)
{
SqlDataReader sdr2 = dcc.GetData(query2);
//List<Passenger> pList = new List<Passenger>();
//id
if (sdr2.Read())
{
a.UserId = Convert.ToInt32(sdr2["UserId"]);
a.UserName = sdr2["Username"].ToString();
a.UserFullName = sdr2["Fullname"].ToString();
a.UserPassword = sdr2["Password"].ToString();
a.UserType = sdr2["UserType"].ToString();
a.UserQuestion = sdr2["Question"].ToString();
//pList.Add(a);
}
dcc.CloseConnection();
return(a);
}
else
{
SqlDataReader sdr = dcc.GetData(query);
//List<Passenger> pList = new List<Passenger>();
if (sdr.Read())
{
a.UserId = Convert.ToInt32(sdr["UserId"]);
a.UserName = sdr["Username"].ToString();
a.UserFullName = sdr["Fullname"].ToString();
a.UserPassword = sdr["Password"].ToString();
a.UserType = sdr["UserType"].ToString();
a.UserQuestion = sdr["Question"].ToString();
//pList.Add(a);
}
dcc.CloseConnection();
return(a);
}
}
public bool InsertApplication(Applications ap)
{
string query = "INSERT into Applications VALUES ('" + ap.AppId + "','" + ap.AppuserId + "','" + ap.Astatus + "','" + ap.Appsub + "','" + ap.Appbody + "')";
try
{
dcc.ConnectWithDB();
int n = dcc.ExecuteSQL(query);
dcc.CloseConnection();
return(true);
}
catch (Exception exp)
{
return(false);
}
finally
{
dcc.CloseConnection();
}
}
public bool InsertEmployee(Employee emp)
{
string query = "INSERT into Employees VALUES ('" + emp.EmpId + "', '" + emp.Name + "', '" + emp.PhnNumber + "', " + emp.Salary + ", '" + emp.Designation + "')";
try
{
dcc.ConnectWithDB();
int n = dcc.ExecuteSQL(query);
dcc.CloseConnection();
return(true);
}
catch (Exception exp)
{
return(false);
}
finally
{
dcc.CloseConnection();
}
}
public bool InsertUser(Login l)
{
string query = "INSERT into Login Values ('" + l.Id + "', " + l.Role + ", '" + l.Password + "')";
try
{
dcc.ConnectWithDB();
int n = dcc.ExecuteSQL(query);
return(true);
}
catch (Exception exp)
{
return(false);
}
finally
{
dcc.CloseConnection();
}
}
public int EmployeeCount()
{
string query = "SELECT COUNT(*) FROM Employee";
DatabaseConnection dcc = new DatabaseConnection();
dcc.ConnectWithDB();
int res = dcc.ExecuteSQL(query);
Console.WriteLine(res);
return(res);
}
public bool Insert(BookedSeats b)
{
try
{
string query = "INSERT into BookedSeats VALUES (" + b.FlightId + ", " + b.BookTicketId + " , '" + b.Seats + "')";
DatabaseConnection dcc = new DatabaseConnection();
dcc.ConnectWithDB();
int x = dcc.ExecuteSQL(query);
dcc.CloseConnection();
return(true);
}
catch (Exception e)
{
Console.WriteLine(e);
return(false);
}
}
public bool Insert(BookTicket bTicket)
{
try
{
string query = "INSERT into BookTicket VALUES ( " + bTicket.BookTicketId + ", " + bTicket.PassengerId + ", " + bTicket.FlightId + " , '" + bTicket.PassengerFullName + "', '" + bTicket.PassengerUsername + "', '" + bTicket.Airplane + "', '" + bTicket.Source + "', '" + bTicket.Destination + "', '" + bTicket.Departure + "', " + bTicket.Cost + " , '" + bTicket.Seats + "')";
DatabaseConnection dcc = new DatabaseConnection();
dcc.ConnectWithDB();
int x = dcc.ExecuteSQL(query);
dcc.CloseConnection();
return(true);
}
catch (Exception e)
{
Console.WriteLine(e);
return(false);
}
}
public bool Insert(Airplane air)
{
try
{
string query = "INSERT into Airplane VALUES ('" + air.AirplaneId + "', '" + air.AirplaneName + "', " + air.Capacity + ")";
DatabaseConnection dcc = new DatabaseConnection();
dcc.ConnectWithDB();
int x = dcc.ExecuteSQL(query);
dcc.CloseConnection();
return(true);
}
catch (Exception e)
{
Console.WriteLine(e);
return(false);
}
}
public bool Insert(AllUsers a)
{
try
{
string query = "INSERT into UserInfo VALUES ('" + a.UserId + "', '" + a.UserType + "', '" + a.UserName + "')";
DatabaseConnection dcc = new DatabaseConnection();
dcc.ConnectWithDB();
int x = dcc.ExecuteSQL(query);
dcc.CloseConnection();
return(true);
}
catch (Exception e)
{
Console.WriteLine(e);
return(false);
}
}
public bool Update(Flight f)
{
try
{
string query = "UPDATE Flight SET AirlineName = '" + f.AirlineName + "', Source = '" + f.Source + "', Destination = '" + f.Destination + "', Arrival = '" + f.Arrival + "', Departure = '" + f.Departure + "', Cost = " + f.Cost + " WHERE FlightID = '" + f.FlightId + "'";
DatabaseConnection dcc = new DatabaseConnection();
dcc.ConnectWithDB();
int x = dcc.ExecuteSQL(query);
dcc.CloseConnection();
return(true);
}
catch (Exception e)
{
Console.WriteLine(e + "error database");
return(false);
}
}
/*public bool Update(Bookticket bt)
* {
*
* try
* {
* string query = "UPDATE Airplane SET Source = '" + bt.Source + "', Destination = '" + bt.Destination + "', Departure = '" + bt.Departure
+ "', Arrival = '" + bt.Arrival + "', Airplane = '" + bt.Airplane + "', '" + bTicket.Seats + "', " + bTicket.Cost + "'";
+ DatabaseConnection dcc = new DatabaseConnection();
+ dcc.ConnectWithDB();
+ int x = dcc.ExecuteSQL(query);
+ dcc.CloseConnection();
+ return true;
+ }
+ catch (Exception ex)
+ {
+ Console.WriteLine(ex);
+ return false;
+ }
+ }*/
public bool Delete(string ticketId)
{
try
{
string query = "DELETE From BookTicket WHERE BookTicketId = '" + ticketId + "' ";
DatabaseConnection dcc = new DatabaseConnection();
dcc.ConnectWithDB();
int x = dcc.ExecuteSQL(query);
dcc.CloseConnection();
return(true);
}
catch (Exception ex)
{
Console.WriteLine(ex);
return(false);
}
}
public bool Update(Airplane air)
{
try
{
string query = "UPDATE Airplane SET AirplaneName = '" + air.AirplaneName
+ "', Capacity = " + air.Capacity + " WHERE AirplaneId = '" + air.AirplaneId + "'";
DatabaseConnection dcc = new DatabaseConnection();
dcc.ConnectWithDB();
int x = dcc.ExecuteSQL(query);
dcc.CloseConnection();
return(true);
}
catch (Exception ex)
{
Console.WriteLine(ex);
return(false);
}
}
public bool Update(AllUsers a)
{
try
{
string query = "UPDATE UserInfo SET UserType = '" + a.UserType
+ "', UserName = '******' WHERE UserId = '" + a.UserId + "'";
DatabaseConnection dcc = new DatabaseConnection();
dcc.ConnectWithDB();
int x = dcc.ExecuteSQL(query);
dcc.CloseConnection();
return(true);
}
catch (Exception ex)
{
Console.WriteLine(ex);
return(false);
}
}
public bool CancelTicketByPassenger(int pId, int bId)
{
try
{
string query = "DELETE from BookTicket WHERE PassengerId = " + pId + " AND BTId = " + bId + "";
DatabaseConnection dcc = new DatabaseConnection();
dcc.ConnectWithDB();
int x = dcc.ExecuteSQL(query);
dcc.CloseConnection();
return(true);
}
catch (Exception ex)
{
Console.WriteLine(ex);
return(false);
}
}
public bool Update(Airport airport)
{
try
{
string query = "UPDATE Airport SET Name = '" + airport.AirportName + "', City = '" +
airport.City + "' WHERE AirportId = " + airport.AirportId + "";
DatabaseConnection dcc = new DatabaseConnection();
dcc.ConnectWithDB();
int x = dcc.ExecuteSQL(query);
dcc.CloseConnection();
return(true);
}
catch (Exception ex)
{
Console.WriteLine(ex);
return(false);
}
}
public bool Delete(int employeeId)
{
try
{
string query = "DELETE From Employee WHERE EmployeeId = " + employeeId + " ";
DatabaseConnection dcc = new DatabaseConnection();
dcc.ConnectWithDB();
int x = dcc.ExecuteSQL(query);
dcc.CloseConnection();
return(true);
}
catch (Exception ex)
{
Console.WriteLine(ex);
return(false);
}
}
public bool Insert(Airport airport)
{
try
{
string query = "INSERT into Airport VALUES (" + airport.AirportId + ", '" + airport.AirportName + "', '" +
airport.City + "')";
DatabaseConnection dcc = new DatabaseConnection();
dcc.ConnectWithDB();
int x = dcc.ExecuteSQL(query);
dcc.CloseConnection();
return(true);
}
catch (Exception e)
{
Console.WriteLine(e);
return(false);
}
}
