public List <Student> RetrieveMatchingStudents(string id = null, string partialLastName = null)
{
List <Student> students = new List <Student>();
List <Parameter> parameters = new List <Parameter>();
if (id != null)
{
parameters.Add(new Parameter("@StudentID", id, 8, SqlDbType.Char));
}
else if (partialLastName != null)
{
parameters.Add(new Parameter("@PartialLastName", partialLastName, 50, SqlDbType.NVarChar));
}
DataAccess db = new DataAccess();
DataTable dt = db.Execute("RetriveMatchingStudents", CommandType.StoredProcedure, parameters);
foreach (DataRow row in dt.Rows)
{
students.Add(PopulateStudent(row, false));
}
return(students);
}
public List <LoanLookUp> RetrieveLoanList(string id)
{
DataAccess db = new DataAccess();
List <ParmStruct> parms = new List <ParmStruct>();
parms.Add(new ParmStruct("@StudentId", id, SqlDbType.NVarChar, ParameterDirection.Input, 8));
DataTable dt = db.Execute("spRetrieveCurrentLoans", CommandType.StoredProcedure, parms);
List <LoanLookUp> Loans = new List <LoanLookUp>();
foreach (DataRow row in dt.Rows)
{
Loans.Add(
new LoanLookUp()
{
StudendId = row["StudentId"].ToString(),
ResourceId = Convert.ToInt32(row["ResourceId"]),
ResourceTitle = row["Title"].ToString(),
ResoruceType = (ResourceType)row["ResourceType"],
CheckOutDate = Convert.ToDateTime(row["CheckoutDate"]),
ImageOfItem = row["ImageOfItem"].ToString()
}
);
}
return(Loans);
}
public Student RetrieveById(string id)
{
List <ParmStruct> parms = new List <ParmStruct>();
parms.Add(new ParmStruct("@StudentId", id, SqlDbType.NVarChar, ParameterDirection.Input, 8));
DataAccess db = new DataAccess();
DataTable dt = db.Execute("spRetrieveStudentOne", CommandType.StoredProcedure, parms);
return(PopulateStudentRecord(dt.Rows[0]));
}
public DataTable RetrieveLogins(string userName, string password)
{
DataAccess db = new DataAccess();
string sql = $" SELECT * FROM Login WHERE LoginUserName = '******' AND Password = '******'";
DataTable dt = db.Execute(sql, CommandType.Text);
return(dt);
}
public Resource RetrieveByIdForResourceOnLoan(int id)
{
List <ParmStruct> parms = new List <ParmStruct>();
parms.Add(new ParmStruct("@ResourceId", id, SqlDbType.Int, ParameterDirection.Input, int.MaxValue));
DataAccess db = new DataAccess();
DataTable dt = db.Execute("spRetriveBorrowerAndResource", CommandType.StoredProcedure, parms);
return(PopulateResourceRecord(dt.Rows[0]));
}
public Item GetItemById(int id)
{
List <ParmStruct> parms = new List <ParmStruct>();
parms.Add(new ParmStruct("@Id", id, SqlDbType.Int, ParameterDirection.Input, 0));
DataAccess db = new DataAccess();
DataTable dt = db.Execute("Item_SearchById", CommandType.StoredProcedure, parms);
return(PopulateItemRecord(dt.Rows[0]));
}
public DataTable RetrieveResourcePriceById(int id)
{
DataAccess db = new DataAccess();
List <Parameter> parms = new List <Parameter>()
{
new Parameter("@ResourceID", id, 0, SqlDbType.Int, ParameterDirection.Input)
};
DataTable dt = db.Execute("SELECT Price FROM Ressource WHERE ID = @ResourceID", CommandType.Text, parms);
return(dt);
}
public Review GetReview(int id)
{
List <ParmStruct> parms = new List <ParmStruct>();
parms.Add(new ParmStruct("@Id", id, SqlDbType.Int, ParameterDirection.Input));
DataAccess db = new DataAccess();
DataTable dt = db.Execute("Review_GetById", CommandType.StoredProcedure, parms);
return(PopulateReviewDetails(dt.Rows[0]));
}
//Check Out
#region Check Out
#region Public Methods
public Student RetrieveStudentInformation(string id)
{
List <Parameter> parameters = new List <Parameter>();
parameters.Add(new Parameter("@StudentID", id, 8, SqlDbType.Char));
DataAccess db = new DataAccess();
DataTable dt = db.Execute("RetriveStudentInformation", CommandType.StoredProcedure, parameters);
return(PopulateStudent(dt.Rows[0]));
}
public Department GetDepartmentBySupId(int id)
{
List <ParmStruct> parms = new List <ParmStruct>();
parms.Add(new ParmStruct("@EmployeeId", id, SqlDbType.Int, ParameterDirection.Input, 0));
DataAccess db = new DataAccess();
DataTable dt = db.Execute("Department_Get_BySupId", CommandType.StoredProcedure, parms);
return(FillDepartmentObject(dt.Rows[0]));
}
public Student RetrieveWithConcurrency(string id)
{
List <ParmStruct> parms = new List <ParmStruct>();
parms.Add(new ParmStruct("@SID", id, SqlDbType.VarChar, ParameterDirection.Input, 8));
DataAccess db = new DataAccess();
System.Data.DataTable dt = db.Execute("Retrieve_Student_Conc", CommandType.StoredProcedure, parms);
return(PopulateStudentRecordMaintModule(dt.Rows[0]));
}
public Student RetrieveByResourceId(string id)
{
List <ParmStruct> parms = new List <ParmStruct>();
parms.Add(new ParmStruct("@RID", id, SqlDbType.VarChar, ParameterDirection.Input, 8));
DataAccess db = new DataAccess();
System.Data.DataTable dt = db.Execute("Retrieve_Student_Via_Resource", CommandType.StoredProcedure, parms);
return(PopulateStudentRecord(dt.Rows[0]));
}
//Check In
#region Check In
//public DataTable GetCurrentLoansByStudentId(string studentID)
//{
// List<Loan> loans = new List<Loan>();
// List<Parameter> parms = new List<Parameter>()
// {
// new Parameter("@StudentID", studentID, 8, SqlDbType.Char, ParameterDirection.Input)
// };
// string sql = "SELECT ID, CheckOutDate, DueDate, CheckInDate, [Status], StudentID, RessourceID FROM Loan WHERE StudentID = @StudentID";
// DataAccess db = new DataAccess();
// DataTable dt = db.Execute(sql, CommandType.Text, parms);
// return dt;
// //foreach (DataRow row in dt.Rows)
// //{
// // loans.Add(PopulateLoanObject(row));
// //}
// //return loans;
//}
public Loan RetrieveLoanByID(int loanID)
{
List <Parameter> parameters = new List <Parameter>();
parameters.Add(new Parameter("@LoanID", loanID, 0, SqlDbType.Int));
string sql = "SELECT * FROM Loan WHERE ID = @LoanID";
DataAccess db = new DataAccess();
DataTable dt = db.Execute(sql, CommandType.Text, parameters);
return(PopulateLoan(dt.Rows[0]));
}
public DataTable RetrieveResourceById(int id)
{
DataAccess db = new DataAccess();
List <Parameter> parms = new List <Parameter>()
{
new Parameter("@ResourceID", id, 0, SqlDbType.Int, ParameterDirection.Input)
};
DataTable dt = db.Execute("CheckIn_Get_Borrowings", CommandType.StoredProcedure, parms);
return(dt);
}
//Check In
#region Check In
public Resource RetrieveResourceToUpdate(int resourceID)
{
List <Parameter> parameters = new List <Parameter>();
parameters.Add(new Parameter("@ResourceID", resourceID, 0, SqlDbType.Int));
string sql = "SELECT * FROM Ressource WHERE ID = @ResourceID";
DataAccess db = new DataAccess();
DataTable dt = db.Execute(sql, CommandType.Text, parameters);
return(PopulateRessource(dt.Rows[0]));
}
public List <Item> GetItems()
{
DataAccess db = new DataAccess();
DataTable dt = db.Execute("Item_GetItems", CommandType.StoredProcedure);
List <Item> result = new List <Item>();
foreach (DataRow r in dt.Rows)
{
result.Add(PopulateItemRecord(r));
}
return(result);
}
public Resource RetrieveRessourceDetails(int ressourceId)
{
List <Parameter> parameters = new List <Parameter>();
parameters.Add(new Parameter("@RessourceID", ressourceId, 0, SqlDbType.Int));
DataAccess db = new DataAccess();
DataTable dt = db.Execute("RetriveRessourceDetails", CommandType.StoredProcedure, parameters);
dt.Columns["ReservingStudentID"].AllowDBNull = true;
return(PopulateRessource(dt.Rows[0]));
}
public List <DepartmentLookupBySupId> GetDepartmentsBySupId(int supId)
{
List <ParmStruct> parms = new List <ParmStruct>();
parms.Add(new ParmStruct("@EmployeeId", supId, SqlDbType.Int, ParameterDirection.Input));
DataAccess db = new DataAccess();
DataTable dt = db.Execute("Department_Lookup_BySupId", CommandType.StoredProcedure, parms);
List <DepartmentLookupBySupId> deps = new List <DepartmentLookupBySupId>();
foreach (DataRow row in dt.Rows)
{
deps.Add(FillDepartmentLookupId(dt.Rows[0]));
}
return(deps);
}
//Check Out
#region CheckOut
public List <Loan> RetriveStudentLoans(string studentId)
{
List <Loan> loans = new List <Loan>();
List <Parameter> parameters = new List <Parameter>();
parameters.Add(new Parameter("@StudentID", studentId, 8, SqlDbType.Char));
DataAccess db = new DataAccess();
DataTable dt = db.Execute("RetriveStudentLoans", CommandType.StoredProcedure, parameters);
foreach (DataRow row in dt.Rows)
{
loans.Add(PopulateLoan(row));
}
return(loans);
}
public List <Review> GetReviewsBySupervisor(int supervisorId)
{
List <ParmStruct> parms = new List <ParmStruct>();
parms.Add(new ParmStruct("@SupervisorId", supervisorId, SqlDbType.Int, ParameterDirection.Input));
DataAccess db = new DataAccess();
DataTable dt = db.Execute("Reviews_GetBySupervisor", CommandType.StoredProcedure, parms);
List <Review> reviews = new List <Review>();
foreach (DataRow row in dt.Rows)
{
reviews.Add(PopulateReviewDetails(row));
}
return(reviews);
}
public List <DepartmentLookup> GetDepartments()
{
DataAccess db = new DataAccess();
DataTable dt = db.Execute("Departments_Get", CommandType.StoredProcedure);
List <DepartmentLookup> departments = new List <DepartmentLookup>();
foreach (DataRow row in dt.Rows)
{
departments.Add(new DepartmentLookup()
{
Id = Convert.ToInt32(row["Id"]),
Name = row["Name"].ToString()
});
}
return(departments);
}
public List <Item> GetItemsById(int POId)
{
List <ParmStruct> parms = new List <ParmStruct>();
parms.Add(new ParmStruct("@POId", POId, SqlDbType.Int, ParameterDirection.Input, 0));
DataAccess db = new DataAccess();
DataTable dt = db.Execute("Item_SearchByPOId", CommandType.StoredProcedure, parms);
List <Item> result = new List <Item>();
foreach (DataRow r in dt.Rows)
{
result.Add(PopulateItemRecord(r));
}
return(result);
}
public List <Loan> RetrieveByLoanByStudentId(string id)
{
List <ParmStruct> parms = new List <ParmStruct>();
parms.Add(new ParmStruct("@SID", id, SqlDbType.VarChar, ParameterDirection.Input, 8));
DataAccess db = new DataAccess();
DataTable dt = db.Execute("Retrieve_LoanStu", CommandType.StoredProcedure, parms);
List <Loan> loans = new List <Loan>();
foreach (DataRow row in dt.Rows)
{
loans.Add(PopulateLoanRecord(row));
}
return(loans);
}
public List <Borrowing> RetrieveBorrowings(int resourceID)
{
DataAccess db = new DataAccess();
List <Parameter> parms = new List <Parameter>()
{
new Parameter("@ResourceID", resourceID, 0, SqlDbType.Int, ParameterDirection.Input)
};
DataTable dt = db.Execute("CheckIn_Get_Borrowings", CommandType.StoredProcedure, parms);
List <Borrowing> borrowings = new List <Borrowing>();
foreach (DataRow row in dt.Rows)
{
borrowings.Add(PopulateBorrowing(row));
}
return(borrowings);
}
public List <Loan> RetriveAllLoans()
{
List <Loan> loans = new List <Loan>();
List <Parameter> parameters = new List <Parameter>();
DataAccess db = new DataAccess();
string sql = "SELECT * FROM Loan";
DataTable dt = db.Execute(sql, CommandType.Text);
foreach (DataRow row in dt.Rows)
{
loans.Add(PopulateLoan(row));
}
return(loans);
}
public List <Item> GetItemsByDate(DateTime start, DateTime end)
{
List <ParmStruct> parms = new List <ParmStruct>();
parms.Add(new ParmStruct("@StartDate", start, SqlDbType.DateTime, ParameterDirection.Input, 0));
parms.Add(new ParmStruct("@EndDate", end, SqlDbType.DateTime, ParameterDirection.Input, 0));
DataAccess db = new DataAccess();
DataTable dt = db.Execute("Item_SearchByDate", CommandType.StoredProcedure, parms);
List <Item> result = new List <Item>();
foreach (DataRow r in dt.Rows)
{
result.Add(PopulateItemRecord(r));
}
return(result);
}
//Check In
#region Check In
public Student RetrieveStudentByResourceId(int id)
{
DataAccess db = new DataAccess();
List <Parameter> parms = new List <Parameter>()
{
new Parameter("@RessourceID", id, 0, SqlDbType.Int, ParameterDirection.Input)
};
//string sql = "SELECT * FROM Student INNER JOIN Loan ON Student.ID = Loan.StudentID WHERE RessourceID = @RessourceID";
string sql = "SELECT Student.ID AS StudentID, FirstName, LastName, BalanceDue, Student.Status AS StudentStatus, ProgramID, StartDate, EndDate, TimeStamp, Loan.ID, CheckOutDate,DueDate, CheckInDate, Loan.Status AS LoanStatus, Loan.StudentID, Loan.RessourceID FROM Student INNER JOIN Loan ON Student.ID = Loan.StudentID WHERE RessourceID = @RessourceID AND Loan.Status = 1";
DataTable dt = db.Execute(sql, CommandType.Text, parms);
if (dt.Rows.Count > 0)
{
return(PopulateStudent(dt));
}
return(new Student());
}
//Payment
#region Payment
public List <Student> PaymentGetStudents(string searchCriteria, int searchBy)
{
List <Student> students = new List <Student>();
List <Parameter> parms = new List <Parameter>()
{
new Parameter("@SearchCriteria", searchCriteria, int.MaxValue, SqlDbType.NVarChar, ParameterDirection.Input),
new Parameter("@SearchBy", searchBy, 0, SqlDbType.Int, ParameterDirection.Input)
};
DataAccess db = new DataAccess();
DataTable dt = db.Execute("PaymentRetrieveStudents", CommandType.StoredProcedure, parms);
foreach (DataRow row in dt.Rows)
{
students.Add(new Student()
{
ID = Convert.ToInt32(row["ID"]), FirstName = row["FirstName"].ToString(), LastName = row["LastName"].ToString()
});
}
return(students);
}
public List <Department> GetDepartmentsAllInfo()
{
DataAccess db = new DataAccess();
DataTable dt = db.Execute("Departments_Get_AllInfo", CommandType.StoredProcedure);
List <Department> departments = new List <Department>();
foreach (DataRow row in dt.Rows)
{
departments.Add(new Department()
{
Id = Convert.ToInt32(row["Id"]),
Name = row["Name"].ToString(),
Description = row["Description"].ToString(),
InvocationDate = Convert.ToDateTime(row["Description"])
});
;
}
return(departments);
}
public List <JobLookup> GetJobsByDepartmentId(int departmentId)
{
List <ParmStruct> parms = new List <ParmStruct>();
parms.Add(new ParmStruct("@DepartmentId", departmentId, SqlDbType.Int, ParameterDirection.Input));
DataAccess db = new DataAccess();
DataTable dt = db.Execute("Job_GetByDepartment", CommandType.StoredProcedure, parms);
List <JobLookup> jobs = new List <JobLookup>();
foreach (DataRow row in dt.Rows)
{
jobs.Add(new JobLookup()
{
Id = Convert.ToInt32(row["Id"]),
Name = row["Name"].ToString()
});
}
return(jobs);
}