public CredentialType GetLoginCredential(int employeeId)
{
List <ParmStruct> parms = new List <ParmStruct>();
parms.Add(new ParmStruct("@EmployeeId", employeeId, SqlDbType.Int));
string sql = "SELECT Credential FROM Login WHERE EmployeeId = @EmployeeId";
DataAccess db = new DataAccess();
return((CredentialType)(db.ExecuteScaler(sql, CommandType.Text, parms)));
}
public decimal Check_Balance(string id)
{
List <ParmStruct> parms = new List <ParmStruct>();
parms.Add(new ParmStruct("@StudentId", id, SqlDbType.NVarChar, ParameterDirection.Input, 8));
DataAccess db = new DataAccess();
string sql = "SELECT BalanceDue FROM Student WHERE StudentId = @StudentId";
return(Convert.ToDecimal(db.ExecuteScaler(sql, CommandType.Text, parms)));
}
public bool Check_ReservedItem(string id)
{
List <ParmStruct> parms = new List <ParmStruct>();
parms.Add(new ParmStruct("@StudentId", id, SqlDbType.NVarChar, ParameterDirection.Input, 8));
DataAccess db = new DataAccess();
string sql = "SELECT Count(*) FROM Resource WHERE StudentId = @StudentId";
return(Convert.ToInt32(db.ExecuteScaler(sql, CommandType.Text, parms)) > 0);
}
public ItemStatusType GetItemStatus(int Id)
{
List <ParmStruct> parms = new List <ParmStruct>();
parms.Add(new ParmStruct("@Id", Id, SqlDbType.Int));
string sql = "SELECT ItemStatus FROM Item WHERE Id = @Id";
DataAccess db = new DataAccess();
return((ItemStatusType)(db.ExecuteScaler(sql, CommandType.Text, parms)));
}
public int ValidateLogin(int employeeId, string password)
{
List <ParmStruct> parms = new List <ParmStruct>();
parms.Add(new ParmStruct("@EmployeeId", employeeId, SqlDbType.Int));
parms.Add(new ParmStruct("@Password", password, SqlDbType.NVarChar, ParameterDirection.Input, 255));
string sql = "SELECT COUNT(*) FROM Login WHERE EmployeeId = @EmployeeId AND Password = @Password";
DataAccess db = new DataAccess();
return(Convert.ToInt32(db.ExecuteScaler(sql, CommandType.Text, parms)));
}
public int CheckLoans(string id, int typeId)
{
List <ParmStruct> parms = new List <ParmStruct>();
parms.Add(new ParmStruct("@SID", id, SqlDbType.VarChar, ParameterDirection.Input, 8));
parms.Add(new ParmStruct("@TypeID", typeId, SqlDbType.Int, ParameterDirection.Input));
string sql = "SELECT COUNT(*) FROM Loan INNER JOIN Resource ON Loan.ResourceID = Resource.ResourceID WHERE LoanStatusID = 1 and ResourceTypeID = @TypeID AND StudentID = @SID";
DataAccess db = new DataAccess();
return(Convert.ToInt32(db.ExecuteScaler(sql, CommandType.Text, parms)));
}
public int CheckIfStudentIsActive(string id)
{
List <ParmStruct> parms = new List <ParmStruct>();
parms.Add(new ParmStruct("@SID", id, SqlDbType.VarChar, ParameterDirection.Input, 8));
string sql = "SELECT COUNT(*) FROM Student WHERE StudentID = @SID AND StudentStatus = 0";
DataAccess db = new DataAccess();
return(Convert.ToInt32(db.ExecuteScaler(sql, CommandType.Text, parms)));
}
public int CheckIfResourceLoaned(string id)
{
List <ParmStruct> parms = new List <ParmStruct>();
parms.Add(new ParmStruct("@RID", id, SqlDbType.VarChar, ParameterDirection.Input, 8));
string sql = "SELECT COUNT(*) FROM Resource WHERE ResourceID = @RID AND ResourceStatusID = 2";
DataAccess db = new DataAccess();
return(Convert.ToInt32(db.ExecuteScaler(sql, CommandType.Text, parms)));
}
public DateTime getStudendStartDate(string keyword)
{
List <ParmStruct> parms = new List <ParmStruct>();
parms.Add(new ParmStruct("@studentId", keyword, SqlDbType.NVarChar, ParameterDirection.Input, 8));
string sql = "SELECT StartDate FROM Student WHERE StudentId = @studentId";
DataAccess db = new DataAccess();
return(Convert.ToDateTime(db.ExecuteScaler(sql, CommandType.Text, parms)));
}
public int CheckIfResourceReserved(string id, string sid)
{
List <ParmStruct> parms = new List <ParmStruct>();
parms.Add(new ParmStruct("@RID", id, SqlDbType.VarChar, ParameterDirection.Input, 8));
parms.Add(new ParmStruct("@SID", id, SqlDbType.VarChar, ParameterDirection.Input, 8));
string sql = "SELECT COUNT(*) FROM Resource INNER JOIN Student ON Resource.ReserveStudentID = Student.StudentID AND StudentID = @SID";
DataAccess db = new DataAccess();
return(Convert.ToInt32(db.ExecuteScaler(sql, CommandType.Text, parms)));
}
public DateTime?GetLastReviewDate(int id)
{
List <ParmStruct> parms = new List <ParmStruct>();
parms.Add(new ParmStruct("@EmployeeId", id, SqlDbType.Int));
string sql = "SELECT MAX(Date) FROM Review WHERE EmployeeId = @EmployeeId";
DataAccess db = new DataAccess();
var holder = db.ExecuteScaler(sql, CommandType.Text, parms);
return(holder != DBNull.Value ? (DateTime?)holder : new DateTime?());
}
public InstructorType RetrieveType(string useranme, string password)
{
List <ParmStruct> parms = new List <ParmStruct>();
parms.Add(new ParmStruct("@username", useranme, SqlDbType.NVarChar, ParameterDirection.Input, 20));
parms.Add(new ParmStruct("@Password", password, SqlDbType.NVarChar, ParameterDirection.Input, 10));
//parms.Add(new ParmStruct("@Result", 0, SqlDbType.Int, ParameterDirection.Output, 0));
DataAccess db = new DataAccess();
int type = Convert.ToInt32(db.ExecuteScaler("spLogin", CommandType.StoredProcedure, parms));
return((InstructorType)type);
}
public bool Check_Reservation(string studentId, int resourceId)
{
List <ParmStruct> parms = new List <ParmStruct>();
parms.Add(new ParmStruct("@studentId", studentId, SqlDbType.NVarChar, ParameterDirection.Input, 8));
parms.Add(new ParmStruct("@resourceId", resourceId, SqlDbType.Int, ParameterDirection.Input, int.MaxValue));
string sql = "SELECT Count(*) FROM Student " +
"INNER JOIN Resource ON Student.StudentId = Resource.StudentId " +
"WHERE Resource.StudentId = @studentId AND Resource.ResourceId = @resourceId";
DataAccess db = new DataAccess();
return(Convert.ToInt32(db.ExecuteScaler(sql, CommandType.Text, parms)) > 0);
}
public int CheckIfReturnIsMissing(string id)
{
List <ParmStruct> parms = new List <ParmStruct>();
parms.Add(new ParmStruct("@LID", id, SqlDbType.Int, ParameterDirection.Input));
parms.Add(new ParmStruct("@Now", DateTime.Now, SqlDbType.DateTime, ParameterDirection.Input));
string sql = "SELECT COUNT(*) FROM Loan WHERE DATEDIFF (DAY, CheckOutDate, @Now) > 10 AND LoanID = @LID";
DataAccess db = new DataAccess();
return(Convert.ToInt32(db.ExecuteScaler(sql, CommandType.Text, parms)));
}
public int Check_SameType(string studentId, int resourceType)
{
List <ParmStruct> parms = new List <ParmStruct>();
parms.Add(new ParmStruct("@studentId", studentId, SqlDbType.NVarChar, ParameterDirection.Input, int.MaxValue));
parms.Add(new ParmStruct("@resourceType", resourceType, SqlDbType.Int, ParameterDirection.Input, int.MaxValue));
string sql = "SELECT count(*) FROM Student " +
"INNER JOIN Loan ON Student.StudentId = Loan.StudentId " +
"INNER JOIN Resource ON Resource.ResourceId = Loan.ResourceId " +
"WHERE ResourceType = @resourceType AND Loan.StudentId = @studentId AND loan.DateReturned is null";
DataAccess db = new DataAccess();
return(Convert.ToInt32(db.ExecuteScaler(sql, CommandType.Text, parms)));
}
//This part for return
public int CalculateDate(string studentId, int resourceId)
{
List <ParmStruct> parms = new List <ParmStruct>();
parms.Add(new ParmStruct("@StudentId", studentId, SqlDbType.NVarChar, ParameterDirection.Input, 8));
parms.Add(new ParmStruct("@ResourceId", resourceId, SqlDbType.Int, ParameterDirection.Input, int.MaxValue));
//parms.Add(new ParmStruct("@DateReturned", returnDate, SqlDbType.DateTime, ParameterDirection.Input));
string sql =
//" DECLARE @LoanId int; " +
//" SET @LoanId = (SELECT LoanId FROM Loan WHERE ResourceId = @ResourceId AND StudentId = @StudentId ) " +
"SELECT DATEDIFF(DAY, Loan.DueDate, Loan.DateReturned) FROM Loan" +
" WHERE LoanId IN(SELECT LoanId FROM Loan WHERE ResourceId = @ResourceId AND StudentId = @StudentId)" +
" AND StudentId = @StudentId)";
DataAccess db = new DataAccess();
return(Convert.ToInt32(db.ExecuteScaler(sql, CommandType.Text, parms)));
}