public Student GetStudent(string regNo)
{
try
{
SqlConnectionObj.Open();
string query = string.Format("select * from tbl_Student where RegNo={0}", regNo);
SqlCommandObj.CommandText = query;
SqlDataReader reader = SqlCommandObj.ExecuteReader();
Student aStudent = new Student();
while (reader.Read())
{
aStudent.Name = reader[1].ToString();
aStudent.RegNo = reader[3].ToString();
aStudent.Email = reader[2].ToString();
aStudent.ContactNo = Convert.ToInt32(reader[4]);
}
return(aStudent);
}
catch (Exception exception)
{
throw new Exception("Error occurred during student management system.", exception);
}
finally
{
SqlConnectionObj.Close();
}
}
public ScheduleVehicle IsScheduleBooked(VehicleInformation vehicle, DateTime selectedDate, string shift)
{
try
{
SqlConnectionObj.Open();
string query = string.Format("SELECT * FROM ScheduleVehicle WHERE VehicleRegNo='{0}' AND Date='{1}' AND Shift='{2}'", vehicle.RegNo, selectedDate, shift);
SqlCommandObj.CommandText = query;
SqlDataReader reader = SqlCommandObj.ExecuteReader();
while (reader.Read())
{
ScheduleVehicle vehicleSchedule = new ScheduleVehicle();
vehicleSchedule.Vehicle.RegNo = reader[0].ToString();
vehicleSchedule.SelectedDate = (DateTime)reader[1];
vehicleSchedule.SelectShift = reader[2].ToString();
vehicleSchedule.BookedBy = reader[3].ToString();
vehicleSchedule.Address = reader[4].ToString();
return(vehicleSchedule);
}
}
catch (Exception exceptionObj)
{
throw new Exception("Exception occured! In Schedule Booking.", exceptionObj);
}
finally
{
if (SqlConnectionObj != null && SqlConnectionObj.State == ConnectionState.Open)
{
SqlConnectionObj.Close();
}
}
return(null);
}
public bool ShowStudentInfo()
{
try
{
SqlConnectionObj.Open();
SqlCommandObj.Connection = SqlConnectionObj;
string query = String.Format("SELECT * FROM tblStudent");
SqlCommandObj.CommandText = query;
SqlDataReader reader = SqlCommandObj.ExecuteReader();
if (reader != null)
{
return(reader.Read());
}
return(false);
}
catch (Exception ex)
{
throw new Exception("Information couldn't loaded from your system", ex);
}
finally
{
if (SqlConnectionObj != null && SqlConnectionObj.State == ConnectionState.Open)
{
SqlConnectionObj.Close();
}
}
}
public List <SchedulesOfAVehicle> GetSchedulesOfASingleVehicle(VehicleInformation vehicle)
{
List <SchedulesOfAVehicle> schedulesOfAVehicle = new List <SchedulesOfAVehicle>();
try
{
SqlConnectionObj.Open();
string query = string.Format("SELECT Date,Shift FROM ScheduleVehicle WHERE VehicleRegNo='{0}'", vehicle.RegNo);
SqlCommandObj.CommandText = query;
SqlDataReader reader = SqlCommandObj.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
SchedulesOfAVehicle singleScheduleOfAVehicle = new SchedulesOfAVehicle();
singleScheduleOfAVehicle.DateTime = (DateTime)reader[0];
singleScheduleOfAVehicle.Shift = reader[1].ToString();
schedulesOfAVehicle.Add(singleScheduleOfAVehicle);
}
return(schedulesOfAVehicle);
}
}
catch (Exception exceptionObj)
{
throw new Exception("Exception Occured in GetScheduleOfASingleVehicle method!!!", exceptionObj);
}
finally
{
if (SqlConnectionObj != null && SqlConnectionObj.State == ConnectionState.Open)
{
SqlConnectionObj.Close();
}
}
return(null);
}
public AutoCompleteStringCollection autoComplete()
{
AutoCompleteStringCollection collection = new AutoCompleteStringCollection();
try
{
SqlConnectionObj.Open();
string query = "select * from tbl_room";
SqlCommandObj.CommandText = query;
SqlDataReader reader = SqlCommandObj.ExecuteReader();
while (reader.Read())
{
string roomNo = reader["roomNo"].ToString();
collection.Add(roomNo);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if (SqlConnectionObj != null && SqlConnectionObj.State == ConnectionState.Open)
{
SqlConnectionObj.Close();
}
}
return(collection);
}
public Book GetBooks(int toInt32)
{
try
{
SqlConnectionObj.Open();
string query = string.Format("select * from tbl_book where Id={0}", toInt32);
SqlCommandObj.CommandText = query;
SqlDataReader reader = SqlCommandObj.ExecuteReader();
Book aBook = new Book();
while (reader.Read())
{
aBook.Id = Convert.ToInt32(reader[0]);
aBook.Title = reader[1].ToString();
aBook.AuthorName = reader[2].ToString();
aBook.ISbnNo = reader[3].ToString();
aBook.Price = Convert.ToInt32(reader[4]);
}
return(aBook);
}
catch (Exception exception)
{
throw new Exception("System can not return those books.", exception);
}
finally
{
SqlConnectionObj.Close();
}
}
public List <Transaction> GetDetailTransaction(string date)
{
List <Transaction> transactions = new List <Transaction>();
try
{
SqlConnectionObj.Open();
string query = "Select * From tbl_Transaction where Date = '" + date + "'";
SqlCommandObj.CommandText = query;
SqlDataReader reader = SqlCommandObj.ExecuteReader();
while (reader.Read())
{
Transaction aTransaction = new Transaction();
aTransaction.Amount = Convert.ToDouble(reader[1]);
aTransaction.Type = reader[3].ToString();
aTransaction.Remarks = remarkGateway.GetRemark(Convert.ToInt32(reader[4]));
transactions.Add(aTransaction);
}
}
catch (Exception exception)
{
throw new Exception("Error occurred during transactions loading from your system.", exception);
}
finally
{
if (SqlConnectionObj != null && SqlConnectionObj.State == ConnectionState.Open)
{
SqlConnectionObj.Close();
}
}
return(transactions);
}
public List <string> GetAllEngineNo()
{
List <string> allEngineNo = new List <string>();
try
{
SqlConnectionObj.Open();
string query = string.Format("select EngineNo from VehicleInformation");
SqlCommandObj.CommandText = query;
SqlDataReader reader = SqlCommandObj.ExecuteReader();
while (reader.Read())
{
string regNo = reader[0].ToString();
allEngineNo.Add(regNo);
}
}
catch (Exception exceptionObj)
{
throw new Exception("Engine Num. can not be loaded.", exceptionObj);
}
finally
{
if (SqlConnectionObj != null && SqlConnectionObj.State == ConnectionState.Open)
{
SqlConnectionObj.Close();
}
}
return(allEngineNo);
}
public Book SearchBook(int bookId)
{
Book aBook = new Book();
try
{
SqlConnectionObj.Open();
string query = string.Format("select * from tbl_book where Id='{0}'", bookId);
SqlCommandObj.CommandText = query;
SqlDataReader reader = SqlCommandObj.ExecuteReader();
while (reader.Read())
{
aBook.Id = Convert.ToInt32(reader["Id"]);
aBook.Title = reader["Title"].ToString();
aBook.AuthorName = reader["AuthorName"].ToString();
aBook.ISbnNo = reader["IsbnNo"].ToString();
aBook.InitialCopy = Convert.ToInt32(reader["InitialCopy"]);
aBook.OutsideCopy = Convert.ToInt32(reader["OutsideCopy"]);
aBook.Price = Convert.ToInt32(reader["Price"]);
}
}
catch (Exception exception)
{
throw new Exception("Error occurred during search this book.", exception);
}
finally
{
SqlConnectionObj.Close();
}
return(aBook);
}
public List <IssueBook> GetIssueBooks(string regNo)
{
List <IssueBook> issueBooks = new List <IssueBook>();
try
{
SqlConnectionObj.Open();
string query = string.Format("select * from tbl_Issueed where student_Reg='{0}' and Return_date is null", regNo);
SqlCommandObj.CommandText = query;
SqlDataReader reader = SqlCommandObj.ExecuteReader();
while (reader.Read())
{
IssueBook issueBook = new IssueBook();
issueBook.Book = bookGateway.GetBooks(Convert.ToInt32(reader[0]));
issueBook.IssueCopy = Convert.ToInt32(reader[1]);
issueBook.DateTime = Convert.ToDateTime(reader[3]);
issueBook.Student = studentGateway.GetStudent(regNo);
issueBooks.Add(issueBook);
}
}
catch (Exception exception)
{
throw new Exception("Error occurred during search this book.", exception);
}
finally
{
SqlConnectionObj.Close();
}
return(issueBooks);
}
public int GetBookId()
{
try
{
int bookId = 0;
SqlConnectionObj.Open();
string query = string.Format("select * from tbl_book");
SqlCommandObj.CommandText = query;
SqlDataReader reader = SqlCommandObj.ExecuteReader();
while (reader.Read())
{
bookId = (int)reader[0];
}
bookId++;
return(bookId);
}
catch (Exception)
{
throw;
}
finally
{
SqlConnectionObj.Close();
}
}
public bool HasThisRemarkName(string name)
{
try
{
SqlConnectionObj.Open();
string query = String.Format("SELECT * FROM tbl_Remark WHERE Name='{0}'", name);
SqlCommandObj.CommandText = query;
SqlDataReader reader = SqlCommandObj.ExecuteReader();
if (reader != null)
{
return(reader.HasRows);
}
return(false);
}
catch (Exception ex)
{
throw new Exception("Remark couldn't loaded from your system", ex);
}
finally
{
if (SqlConnectionObj != null && SqlConnectionObj.State == ConnectionState.Open)
{
SqlConnectionObj.Close();
}
}
}
public Remark GetRemark(int remarkId)
{
try
{
SqlConnectionObj.Open();
string query = string.Format("Select * From tbl_Remark where ID = " + remarkId);
SqlCommandObj.CommandText = query;
SqlDataReader dataReader = SqlCommandObj.ExecuteReader();
Remark aRemark = new Remark();
while (dataReader.Read())
{
aRemark.Id = Convert.ToInt32(dataReader["ID"]);
aRemark.Name = dataReader["Name"].ToString();
}
return(aRemark);
}
catch (Exception ex)
{
throw new Exception("Remarks can not loaded from your system", ex);
}
finally
{
if (SqlCommandObj != null && SqlConnectionObj.State == ConnectionState.Open)
{
SqlConnectionObj.Close();
}
}
}
public List <Remark> GetAll()
{
List <Remark> remarks = new List <Remark>();
try
{
SqlConnectionObj.Open();
string query = String.Format("Select * From tbl_Remark");
SqlCommandObj.CommandText = query;
SqlDataReader dataReader = SqlCommandObj.ExecuteReader();
while (dataReader.Read())
{
Remark aRemark = new Remark();
aRemark.Id = Convert.ToInt32(dataReader["ID"]);
aRemark.Name = dataReader["Name"].ToString();
remarks.Add(aRemark);
}
}
catch (Exception ex)
{
throw new Exception("Remarks can not loaded from your system", ex);
}
finally
{
if (SqlCommandObj != null && SqlConnectionObj.State == ConnectionState.Open)
{
SqlConnectionObj.Close();
}
}
return(remarks);
}
public Student SearchStudent(string studentId)
{
Student aStudent = new Student();
try
{
SqlConnectionObj.Open();
string query = string.Format("select * from tbl_Student where regNo='{0}'", studentId);
SqlCommandObj.CommandText = query;
SqlDataReader reader = SqlCommandObj.ExecuteReader();
while (reader.Read())
{
aStudent.Id = Convert.ToInt32(reader["Id"]);
aStudent.Name = reader["Name"].ToString();
aStudent.Email = reader["Email"].ToString();
aStudent.RegNo = reader["RegNo"].ToString();
aStudent.ContactNo = Convert.ToInt32(reader["ContactNo"]);
}
}
catch (Exception exception)
{
throw new Exception("System doesnot load this student reg no.", exception);
}
finally
{
SqlConnectionObj.Close();
}
return(aStudent);
}
public List <VehicleInformation> GetAllVehicle()
{
List <VehicleInformation> allVehicles = new List <VehicleInformation>();
try
{
SqlConnectionObj.Open();
string query = string.Format("select * from VehicleInformation");
SqlCommandObj.CommandText = query;
SqlDataReader reader = SqlCommandObj.ExecuteReader();
while (reader.Read())
{
VehicleInformation aVehicleInformation = new VehicleInformation();
aVehicleInformation.RegNo = reader[0].ToString();
aVehicleInformation.EngineNo = reader[1].ToString();
allVehicles.Add(aVehicleInformation);
}
}
catch (Exception exceptionObj)
{
throw new Exception("Vehicle inforamtion number can not be loaded.", exceptionObj);
}
finally
{
if (SqlConnectionObj != null && SqlConnectionObj.State == ConnectionState.Open)
{
SqlConnectionObj.Close();
}
}
return(allVehicles);
}
public List <Student> GetAllStudent(string roomNo)
{
List <Student> students = new List <Student>();
try
{
SqlConnectionObj.Open();
string query = String.Format("select * from tbl_Student WHERE roomNo LIKE '%{0}%'", roomNo);
//query += "order by studentName";
//SqlConnectionObj.Open();
//
//SqlCommandObj.CommandText = query;
//SqlDataReader reader = SqlCommandObj.ExecuteReader();
//SqlCommandObj.CommandText = query;
SqlCommandObj.CommandText = query;
SqlDataReader reader = SqlCommandObj.ExecuteReader();
while (reader.Read())
{
Student aStudent = new Student();
aStudent.StudentId = reader["studentId"].ToString();
aStudent.Name = reader["name"].ToString();
aStudent.Department = reader["department"].ToString();
aStudent.Session = reader["session"].ToString();
aStudent.HallName = reader["hall"].ToString();
aStudent.Email = reader["email"].ToString();
aStudent.RoomPosition = reader["roomPosition"].ToString();
aStudent.RoomNo = reader["roomNo"].ToString();
aStudent.Fathername = reader["fatherName"].ToString();
aStudent.Address = reader["address"].ToString();
aStudent.School = reader["school"].ToString();
aStudent.College = reader["college"].ToString();
aStudent.District = reader["district"].ToString();
students.Add(aStudent);
}
}
catch (Exception ex)
{
throw new Exception("can't populate students", ex);
}
finally
{
if (SqlConnectionObj != null && SqlConnectionObj.State == ConnectionState.Open)
{
SqlConnectionObj.Close();
}
}
return(students);
}
public bool HasBookName(string title)
{
try
{
SqlConnectionObj.Open();
string query = string.Format("select * from tbl_book where Title='{0}'", title);
SqlCommandObj.CommandText = query;
SqlDataReader reader = SqlCommandObj.ExecuteReader();
if (reader != null)
{
return(reader.HasRows);
}
return(false);
}
catch (Exception exception)
{
throw new Exception("Title couldn't loaded from your system", exception);
}finally
{
SqlConnectionObj.Close();
}
}
public bool CheckAll(string username, string password)
{
try
{
SqlConnectionObj.Open();
string query = string.Format("select * from tbl_Admin where UserName='******' AND Password='******'", username, password);
SqlCommandObj.CommandText = query;
SqlDataReader reader = SqlCommandObj.ExecuteReader();
if (reader != null)
{
return(reader.HasRows);
}
return(false);
}
catch (Exception exception)
{
throw new Exception("Error occurred during your login System.", exception);
}
finally
{
SqlConnectionObj.Close();
}
}
public bool HasRegNo(string regNo)
{
try
{
SqlConnectionObj.Open();
string query = string.Format("select * from tbl_Student where regNo='{0}'", regNo);
SqlCommandObj.CommandText = query;
SqlDataReader reader = SqlCommandObj.ExecuteReader();
if (reader != null)
{
return(reader.HasRows);
}
return(false);
}
catch (Exception exception)
{
throw new Exception("System Does not load this registration no.", exception);
}
finally
{
SqlConnectionObj.Close();
}
}
public List <DateBetweenSchedule> GetDateBetweenSchedule(DateTime startDate, DateTime endDate)
{
List <DateBetweenSchedule> dateBetweenSchedules = new List <DateBetweenSchedule>();
try
{
SqlConnectionObj.Open();
string query = string.Format("SELECT Date,VehicleRegNo,Shift FROM ScheduleVehicle WHERE Date BETWEEN '{0}' AND '{1}'", startDate, endDate);
SqlCommandObj.CommandText = query;
SqlDataReader reader = SqlCommandObj.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
DateBetweenSchedule single = new DateBetweenSchedule();
single.DateTime = (DateTime)reader[0];
single.RegNo = reader[1].ToString();
single.Shift = reader[2].ToString();
dateBetweenSchedules.Add(single);
}
return(dateBetweenSchedules);
}
}
catch (Exception exceptionObj)
{
throw new Exception("Exception Occured in GetScheduleOfASingleVehicle method!!!", exceptionObj);
}
finally
{
if (SqlConnectionObj != null && SqlConnectionObj.State == ConnectionState.Open)
{
SqlConnectionObj.Close();
}
}
return(null);
}
public List <Book> GetAllBooks(string name, string searchType)
{
List <Book> books = new List <Book>();
try
{
SqlConnectionObj.Open();
if (searchType == "name")
{
string quere = string.Format("select * from tbl_book where Title like '%{0}%'", name);
SqlCommandObj.CommandText = quere;
SqlDataReader reader = SqlCommandObj.ExecuteReader();
while (reader.Read())
{
Book aBook = new Book();
aBook.Id = Convert.ToInt32(reader["Id"]);
aBook.Title = reader["Title"].ToString();
aBook.AuthorName = reader["AuthorName"].ToString();
aBook.InitialCopy = Convert.ToInt32(reader["InitialCopy"]);
aBook.OutsideCopy = Convert.ToInt32(reader["OutsideCopy"]);
aBook.Price = Convert.ToInt32(reader["Price"]);
books.Add(aBook);
}
}
else if (searchType == "author")
{
string quere = string.Format("select * from tbl_book where AuthorName like '%{0}%'", name);
SqlCommandObj.CommandText = quere;
SqlDataReader reader = SqlCommandObj.ExecuteReader();
while (reader.Read())
{
Book aBook = new Book();
aBook.Id = Convert.ToInt32(reader["Id"]);
aBook.Title = reader["Title"].ToString();
aBook.AuthorName = reader["AuthorName"].ToString();
aBook.InitialCopy = Convert.ToInt32(reader["InitialCopy"]);
aBook.OutsideCopy = Convert.ToInt32(reader["OutsideCopy"]);
aBook.Price = Convert.ToInt32(reader["Price"]);
books.Add(aBook);
}
}
else if (searchType == "bookId")
{
string quere = string.Format("select * from tbl_book where Id={0}", name);
SqlCommandObj.CommandText = quere;
SqlDataReader reader = SqlCommandObj.ExecuteReader();
while (reader.Read())
{
Book aBook = new Book();
aBook.Id = Convert.ToInt32(reader["Id"]);
aBook.Title = reader["Title"].ToString();
aBook.AuthorName = reader["AuthorName"].ToString();
aBook.InitialCopy = Convert.ToInt32(reader["InitialCopy"]);
aBook.OutsideCopy = Convert.ToInt32(reader["OutsideCopy"]);
aBook.Price = Convert.ToInt32(reader["Price"]);
books.Add(aBook);
}
}
}
catch (Exception exception)
{
throw new Exception("Error occurred during books loading from your system.", exception);
}finally
{
SqlConnectionObj.Close();
}
return(books);
}
public List <Transaction> GetTransactionSummary(string dateFrom, string dateTo)
{
List <Transaction> transactions = new List <Transaction>();
try
{
SqlConnectionObj.Open();
string query = "Select a.Date,IsNull(Sum(b.Amount),0) as Income,IsNull(Sum(c.Amount),0) as Expense from tbl_Transaction a";
query += " Left outer Join tbl_Transaction b on b.ID = a.ID and b.Type = 'Income'";
query += " Left Join tbl_Transaction c on c.ID = a.ID and c.Type= 'Expense'";
query += " Where a.Date between '" + dateFrom + "' and '" + dateTo + "'";
query += " Group by a.Date";
query += " Order By a.Date";
SqlCommandObj.CommandText = query;
SqlDataReader reader = SqlCommandObj.ExecuteReader();
while (reader.Read())
{
Transaction aTransaction = new Transaction();
aTransaction.TransactionDate = reader[0].ToString();
aTransaction.Income = Convert.ToDouble(reader[1]);
aTransaction.Expence = Convert.ToDouble(reader[2]);
transactions.Add(aTransaction);
}
List <string> dateList = CreateDateList(dateFrom, dateTo);
List <string> tempDateList = new List <string>();
foreach (string obj in dateList)
{
bool flag = false;
foreach (var objt in transactions)
{
if (objt.TransactionDate == obj)
{
flag = true;
break;
}
}
if (flag)
{
tempDateList.Add(obj);
}
}
foreach (var obj in tempDateList)
{
dateList.Remove(obj);
}
foreach (var obj in dateList)
{
transactions.Add(new Transaction {
TransactionDate = obj
});
}
}
catch (Exception exception)
{
throw new Exception("Error occurred during transactions loading from your system.", exception);
}
finally
{
if (SqlConnectionObj != null && SqlConnectionObj.State == ConnectionState.Open)
{
SqlConnectionObj.Close();
}
}
return(transactions);
}
