public DataTable GetCategory(string sType, string sValue)
{
try
{
osb.ConnectionString = sConnectionString;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = osb.ConnectionString;
string sQuery = String.Empty;
switch (sType)
{
case "INACTIVE":
sQuery = "SELECT * FROM TBL_CATEGORY WHERE [STATUS] = 'INACTIVE'";
break;
case "CATEGORY":
sQuery = "SELECT * FROM TBL_CATEGORY WHERE [STATUS] = 'ACTIVE' AND CATEGORY LIKE '%" + sValue + "%'";
break;
default:
sQuery = "SELECT * FROM TBL_CATEGORY";
break;
}
ddq.CommandText = sQuery;
ds = ddq.GetDataset(CommandType.Text);
return(ds.Tables.Count > 0 ? ds.Tables[0] : null);
}
catch (Exception ex)
{
return(null);
}
}
public DataTable getElectionCode(string sType, string sValue)
{
try
{
osb.ConnectionString = sConnectionString;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = osb.ConnectionString;
string sSQL = string.Empty;
switch (sType)
{
case "STUDENT ID":
sSQL = "Select * from tbl_ElectionCode where status = 'ACTIVE' AND STUDENT_ID LIKE '%" + sValue + "%' order by date_added desc";
break;
case "INACTIVE":
sSQL = "Select * from tbl_ElectionCode where status = 'INACTIVE' order by date_added desc";
break;
default:
sSQL = "Select * from tbl_ElectionCode where status = 'ACTIVE' order by date_added desc";
break;
}
ddq.CommandText = sSQL;
ds = ddq.GetDataset(CommandType.Text);
return(ds.Tables.Count > 0 ? ds.Tables[0] : null);
}
catch (Exception ex)
{
throw ex;
}
}
public DataTable getCandidatePosition(string sType, string sValue)
{
try
{
osb.ConnectionString = sConnectionString;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = osb.ConnectionString;
string sQuery = String.Empty;
switch (sType)
{
case "ID":
sQuery = "Select * from tbl_Position where ID LIKE '%" + sValue + "%' and status = 'ACTIVE'";
break;
case "POSITION":
sQuery = "Select * from tbl_Position where Position LIKE '%" + sValue + "5' and status = 'ACTIVE'";
break;
default:
sQuery = "Select * from tbl_Position where Position Like '%" + sValue + "%' and status = 'ACTIVE'";
break;
}
ddq.CommandText = sQuery;
ds = ddq.GetDataset(CommandType.Text);
return(ds.Tables.Count > 0 ? ds.Tables[0] : null);
}
catch (Exception ex)
{
throw ex;
}
}
public DataTable GetLocationRecord(String sType, string sFindText)
{
try
{
scb.ConnectionString = sConnectionString;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = scb.ConnectionString;
string sQuery = "";
switch (sType)
{
case "LOCATION":
sQuery = "SELECT * FROM TBL_LOCATION WHERE LOCATION LIKE '%" + sFindText + "%'";
break;
default:
sQuery = "SELECT * FROM TBL_LOCATION WHERE LOCATION LIKE '%" + sFindText + "%'";
break;
}
ddq.CommandText = sQuery;
ds = ddq.GetDataset(CommandType.Text);
return(ds.Tables.Count > 0 ? ds.Tables[0] : null);
}
catch (Exception ex)
{
return(null);
}
}
public Boolean IsBookRecordDataExists(eVariable.FIND_TYPE oFilter, string sFindText)
{
try
{
string sQuery = string.Empty;
osb.ConnectionString = sConnectionString;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = osb.ConnectionString;
switch (oFilter)
{
case eVariable.FIND_TYPE.ISBN_NUMBER:
sQuery = "SELECT * FROM TBL_BOOKS WHERE ISBN_NUMBER = '" + sFindText + "'";
break;
case eVariable.FIND_TYPE.BOOK_NO:
sQuery = "SELECT * FROM TBL_BOOKS WHERE BOOK_NO = '" + sFindText + "'";
break;
default:
sQuery = "SELECT * FROM TBL_BOOKS WHERE ISBN_NUMBER = '" + sFindText + "'";
break;
}
ddq.CommandText = sQuery;
ds = ddq.GetDataset(CommandType.Text);
return(ds.Tables[0].Rows.Count > 0 ? true : false);
}
catch (Exception ex)
{
throw ex;
}
}
public DataTable getGeneratedKey(String sType, string sValue)
{
try
{
osb.ConnectionString = sConnectionString;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = osb.ConnectionString;
string sQuery;
switch (sType)
{
case "STUDENT ID":
sQuery = "Select * from tbl_VotersKey where voters_id LIKE '%" + sValue + "%' and status = 'ACTIVE'";
break;
case "INACTIVE":
sQuery = "Select * from tbl_VotersKey where voters_id = '" + sValue + "' and status = 'ACTIVE'";
break;
default:
sQuery = "Select * from tbl_VotersKey where Status = 'Active'";
break;
}
ddq.CommandText = sQuery;
ds = ddq.GetDataset(CommandType.Text);
return(ds.Tables.Count > 0 ? ds.Tables[0] : null);
}
catch (Exception ex)
{
throw ex;
}
}
public DataTable GetBorrowedBookISBNPerBorrower(eVariable.FIND_TYPE oFilter, string sBookID, string sBorrowerID)
{
try
{
osb.ConnectionString = sConnectionString;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = osb.ConnectionString;
string sQuery = string.Empty;
switch (oFilter)
{
case eVariable.FIND_TYPE.BOOK_ID:
sQuery = "SELECT BOOK_ID, BOOK_NO, ISBN_NUMBER, REMARKS, [STATUS] FROM TBL_BORROWEDBOOKS WHERE BOOK_ID = '" + sBookID + "' AND [STATUS] = 'BORROWED'";
break;
case eVariable.FIND_TYPE.BORROWER_ID:
sQuery = "SELECT BOOK_ID,BOOK_NO, ISBN_NUMBER, REMARKS, [STATUS] FROM TBL_BORROWEDBOOKS WHERE BOOK_ID = '" + sBookID + "' AND BORROWER_ID = '" + sBorrowerID + "' AND [STATUS] = 'BORROWED' ";
break;
default:
sQuery = "SELECT BOOK_ID, BOOK_NO, ISBN_NUMBER, REMARKS, [STATUS] FROM TBL_BORROWEDBOOKS WHERE [STATUS] = 'BORROWED' ";
break;
}
ddq.CommandText = sQuery;
ds = ddq.GetDataset(CommandType.Text);
return(ds.Tables.Count > 0 ? ds.Tables[0] : null);
}
catch (Exception ex)
{
throw ex;
}
}
public DataTable GetRole(String sType, string sFindText)
{
try
{
scb.ConnectionString = sConnectionString;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = scb.ConnectionString;
string sQuery = "";
switch (sType)
{
case "ROLE":
sQuery = "SELECT * FROM TBL_ROLE WHERE ROLE LIKE '%" + sFindText + "%'";
break;
default:
sQuery = "SELECT * FROM TBL_ROLE WHERE ROLE LIKE '%" + sFindText + "%'";
break;
}
ddq.CommandText = sQuery;
ds = ddq.GetDataset(CommandType.Text);
return(ds.Tables.Count > 0 ? ds.Tables[0] : null);
}
catch (Exception ex)
{
throw ex;
}
}
public DataTable GetBookISBN(eVariable.FIND_TYPE o_FindType, string sFindText)
{
try
{
string sQuery = string.Empty;
osb.ConnectionString = sConnectionString;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = osb.ConnectionString;
switch (o_FindType)
{
case eVariable.FIND_TYPE.BOOK_ID:
sQuery = "SELECT ID, BOOK_NO, ISBN_NUMBER,REMARKS,[STATUS] FROM TBL_BOOKS WHERE ID = '" + sFindText + "'";
break;
case eVariable.FIND_TYPE.BOOK_NO:
sQuery = "SELECT ID, BOOK_NO, ISBN_NUMBER,REMARKS,[STATUS] FROM TBL_BOOKS WHERE BOOK_NO = '" + sFindText + "'";
break;
default:
sQuery = "SELECT ID,BOOK_NO, ISBN_NUMBER,REMARKS,[STATUS] FROM TBL_BOOKS";
break;
}
ddq.CommandText = sQuery;
ds = ddq.GetDataset(CommandType.Text);
return(ds.Tables.Count > 0 ? ds.Tables[0] : null);
}
catch (Exception ex)
{
throw ex;
}
}
public int GetBookID()
{
osb.ConnectionString = sConnectionString;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = osb.ConnectionString;
ddq.CommandText = "SELECT TOP 1 ID FROM TBL_BOOKS ORDER BY ID DESC";
ds = ddq.GetDataset(CommandType.Text);
return(ds.Tables[0].Rows.Count > 0 ? Convert.ToInt32(ds.Tables[0].Rows[0][0]) : 0);
}
public int GetBorrowerNo()
{
osb.ConnectionString = sConnectionString;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = osb.ConnectionString;
ddq.CommandText = "SELECT TOP 1 BORROWER_ID FROM TBL_BORROWER ORDER BY BORROWER_ID DESC";
ds = ddq.GetDataset(CommandType.Text);
return(ds.Tables[0].Rows.Count > 0 ? Convert.ToInt32(Regex.Replace(ds.Tables[0].Rows[0][0].ToString(), "[^0-9]", "")) : 0);
}
private int GetStudentNo()
{
osb.ConnectionString = sConnectionString;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = osb.ConnectionString;
ddq.CommandText = "SELECT TOP 1 STUDENT_ID FROM TBL_STUDENTS ORDER BY STUDENT_ID DESC";
ds = ddq.GetDataset(CommandType.Text);
return(ds.Tables[0].Rows.Count > 0 ? Convert.ToInt32(Regex.Replace(ds.Tables[0].Rows[0][0].ToString(), "[^0-9]", "")) : 0);
}
public int GetTransactionNo()
{
osb.ConnectionString = sConnectionString;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = osb.ConnectionString;
ddq.CommandText = "SELECT TOP 1 TRANSACTION_NO FROM TBL_BORROWEDBOOKS ORDER BY TRANSACTION_NO DESC";
ds = ddq.GetDataset(CommandType.Text);
return(ds.Tables[0].Rows.Count > 0 ? Convert.ToInt32(Regex.Replace(ds.Tables[0].Rows[0][0].ToString(), "[^0-9]", "")) : 0);
}
public Boolean IsBookCheckout(Model.Transaction oData)
{
osb.ConnectionString = sConnectionString;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = osb.ConnectionString;
ddq.CommandText = " SELECT ID FROM TBL_BOOKS WHERE BOOK_NO = '" + oData.BOOK_NO + "' AND [STATUS] = 'ACTIVE' " +
" AND (BOOK_NO IN (SELECT BOOK_NO FROM TBL_BORROWEDBOOKS WHERE [STATUS] = 'BORROWED') " +
" OR BOOK_NO IN (SELECT BOOK_NO FROM TBL_BORROWERREQUEST WHERE [STATUS] = 'REQUEST')) ";
ds = ddq.GetDataset(CommandType.Text);
return(ds.Tables[0].Rows.Count > 0 ? true : false);
}
public DataTable getCandidates(String sType, string sValue)
{
try
{
osb.ConnectionString = sConnectionString;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = osb.ConnectionString;
string sQuery;
switch (sType)
{
case "ID":
sQuery = "SELECT B.CANDIDATE_ID,A.FIRST_NAME,A.MIDDLE_NAME,A.LAST_NAME,A.DOB,A.AGE,A.COURSE,A.SECTION,A.CONTACT_NO,A.ADDRESS,C.ID AS POSITION_ID,C.POSITION,D.ID AS PARTY_ID,D.PARTY,B.PROFILE_PIC,B.ELECTION_CODE FROM TBL_STUDENTS A INNER JOIN TBL_CANDIDATES B ON A.STUDENT_ID = B.CANDIDATE_ID INNER JOIN TBL_POSITION C ON C.ID = B.POSITION_ID INNER JOIN TBL_PARTY D ON D.ID = B.PARTY_ID WHERE B.STATUS = 'ACTIVE' AND A.STUDENT_ID = '" + sValue + "'";
break;
case "STUDENT ID":
sQuery = "SELECT B.CANDIDATE_ID,A.FIRST_NAME,A.MIDDLE_NAME,A.LAST_NAME,A.DOB,A.AGE,A.COURSE,A.SECTION,A.CONTACT_NO,A.ADDRESS,C.ID AS POSITION_ID,C.POSITION,D.ID AS PARTY_ID,D.PARTY,B.PROFILE_PIC,B.ELECTION_CODE FROM TBL_STUDENTS A INNER JOIN TBL_CANDIDATES B ON A.STUDENT_ID = B.CANDIDATE_ID INNER JOIN TBL_POSITION C ON C.ID = B.POSITION_ID INNER JOIN TBL_PARTY D ON D.ID = B.PARTY_ID WHERE B.STATUS = 'ACTIVE' AND A.STUDENT_ID LIKE '%" + sValue + "%'";
break;
case "CANDIDATE ID":
sQuery = "SELECT B.CANDIDATE_ID,A.FIRST_NAME,A.MIDDLE_NAME,A.LAST_NAME,A.DOB,A.AGE,A.COURSE,A.SECTION,A.CONTACT_NO,A.ADDRESS,C.ID AS POSITION_ID,C.POSITION,D.ID AS PARTY_ID,D.PARTY,B.PROFILE_PIC,B.ELECTION_CODE FROM TBL_STUDENTS A INNER JOIN TBL_CANDIDATES B ON A.STUDENT_ID = B.CANDIDATE_ID INNER JOIN TBL_POSITION C ON C.ID = B.POSITION_ID INNER JOIN TBL_PARTY D ON D.ID = B.PARTY_ID WHERE B.STATUS = 'ACTIVE' AND A.STUDENT_ID LIKE '%" + sValue + "%'";
break;
case "FIRST NAME":
sQuery = "SELECT B.CANDIDATE_ID,A.FIRST_NAME,A.MIDDLE_NAME,A.LAST_NAME,A.DOB,A.AGE,A.COURSE,A.SECTION,A.CONTACT_NO,A.ADDRESS,C.ID AS POSITION_ID,C.POSITION,D.ID AS PARTY_ID,D.PARTY,B.PROFILE_PIC,B.ELECTION_CODE FROM TBL_STUDENTS A INNER JOIN TBL_CANDIDATES B ON A.STUDENT_ID = B.CANDIDATE_ID INNER JOIN TBL_POSITION C ON C.ID = B.POSITION_ID INNER JOIN TBL_PARTY D ON D.ID = B.PARTY_ID WHERE B.STATUS = 'ACTIVE' AND A.FIRST_NAME LIKE '%" + sValue + "%'";
break;
case "LAST NAME":
sQuery = "SELECT B.CANDIDATE_ID,A.FIRST_NAME,A.MIDDLE_NAME,A.LAST_NAME,A.DOB,A.AGE,A.COURSE,A.SECTION,A.CONTACT_NO,A.ADDRESS,C.ID AS POSITION_ID,C.POSITION,D.ID AS PARTY_ID,D.PARTY,B.PROFILE_PIC,B.ELECTION_CODE FROM TBL_STUDENTS A INNER JOIN TBL_CANDIDATES B ON A.STUDENT_ID = B.CANDIDATE_ID INNER JOIN TBL_POSITION C ON C.ID = B.POSITION_ID INNER JOIN TBL_PARTY D ON D.ID = B.PARTY_ID WHERE B.STATUS = 'ACTIVE' AND A.LAST_NAME LIKE '%" + sValue + "%'";
break;
case "INACTIVE":
sQuery = "SELECT B.CANDIDATE_ID,A.FIRST_NAME,A.MIDDLE_NAME,A.LAST_NAME,A.DOB,A.AGE,A.COURSE,A.SECTION,A.CONTACT_NO,A.ADDRESS,C.ID AS POSITION_ID,C.POSITION,D.ID AS PARTY_ID,D.PARTY,B.PROFILE_PIC,B.ELECTION_CODE FROM TBL_STUDENTS A INNER JOIN TBL_CANDIDATES B ON A.STUDENT_ID = B.CANDIDATE_ID INNER JOIN TBL_POSITION C ON C.ID = B.POSITION_ID INNER JOIN TBL_PARTY D ON D.ID = B.PARTY_ID WHERE B.STATUS = 'INACTIVE' AND A.STUDENT_ID LIKE '%" + sValue + "%'";
break;
default:
sQuery = "SELECT B.CANDIDATE_ID,A.FIRST_NAME,A.MIDDLE_NAME,A.LAST_NAME,A.DOB,A.AGE,A.COURSE,A.SECTION,A.CONTACT_NO,A.ADDRESS,C.ID AS POSITION_ID,C.POSITION,D.ID AS PARTY_ID,D.PARTY,B.PROFILE_PIC,B.ELECTION_CODE FROM TBL_STUDENTS A INNER JOIN TBL_CANDIDATES B ON A.STUDENT_ID = B.CANDIDATE_ID INNER JOIN TBL_POSITION C ON C.ID = B.POSITION_ID INNER JOIN TBL_PARTY D ON D.ID = B.PARTY_ID WHERE B.STATUS = 'ACTIVE' AND A.FIRST_NAME LIKE '%" + sValue + "%'";
break;
}
ddq.CommandText = sQuery;
ds = ddq.GetDataset(CommandType.Text);
return(ds.Tables.Count > 0 ? ds.Tables[0] : null);
}
catch (Exception ex)
{
throw ex;
}
}
public DataTable getCandidate(String sType, string sValue)
{
try
{
osb.ConnectionString = sConnectionString;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = osb.ConnectionString;
string sQuery;
switch (sType)
{
case "STUDENT ID":
sQuery = "SELECT * FROM TBL_STUDENTS WHERE STUDENT_ID Like '%" + sValue + "%' and status = 'ACTIVE' and Student_ID not in (Select Candidate_ID from tbl_Candidates where status = 'ACTIVE')";
break;
case "FIRST NAME":
sQuery = "SELECT * FROM TBL_STUDENTS WHERE FIRST_NAME Like '%" + sValue + "%' and status = 'ACTIVE' and Student_ID not in (Select Candidate_ID from tbl_Candidates where status = 'ACTIVE')";
break;
case "MIDDLE NAME":
sQuery = "SELECT * FROM TBL_STUDENTS WHERE MIDDLE_NAME Like '%" + sValue + "%' and status = 'ACTIVE' and Student_ID not in (Select Candidate_ID from tbl_Candidates where status = 'ACTIVE')";
break;
case "LAST NAME":
sQuery = "SELECT * FROM TBL_STUDENTS WHERE LAST_NAME Like '%" + sValue + "%' and status = 'ACTIVE' and Student_ID not in (Select Candidate_ID from tbl_Candidates where status = 'ACTIVE')";
break;
case "INACTIVE":
sQuery = "Select * from tbl_Students where status = 'INACTIVE'";
break;
default:
sQuery = "Select * from tbl_Students where status = 'ACTIVE' and Student_ID not in (Select Candidate_ID from tbl_Candidates where status = 'ACTIVE')";
break;
}
ddq.CommandText = sQuery;
ds = ddq.GetDataset(CommandType.Text);
return(ds.Tables.Count > 0 ? ds.Tables[0] : null);
}
catch (Exception ex)
{
throw ex;
}
}
public DataTable getPositionCount(string sType, string sValue)
{
try
{
osb.ConnectionString = sConnectionString;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = osb.ConnectionString;
ddq.CommandText = "Select distinct Count(Position) from tbl_Position";
ds = ddq.GetDataset(CommandType.Text);
return(ds.Tables.Count > 0 ? ds.Tables[0] : null);
}
catch (Exception ex)
{
throw ex;
}
}
public DataTable GetVotingSchedule()
{
try
{
osb.ConnectionString = sConnectionString;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = osb.ConnectionString;
ddq.CommandText = "Select * from tbl_VotingSchedule";
ds = ddq.GetDataset(CommandType.Text);
return(ds.Tables.Count > 0 ? ds.Tables[0] : null);
}
catch (Exception ex)
{
throw ex;
}
}
public Boolean IsScheduleExists()
{
try
{
osb.ConnectionString = sConnectionString;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = osb.ConnectionString;
ddq.CommandText = "Select * from tbl_VotingSchedule";
ds = ddq.GetDataset(CommandType.Text);
return(ds.Tables[0].Rows.Count > 0 ? true : false);
}
catch (Exception ex)
{
throw ex;
}
}
public Boolean IsKeyExists(String sVotersID)
{
try
{
osb.ConnectionString = sConnectionString;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = osb.ConnectionString;
ddq.CommandText = "Select * from tbl_VotersKey Where Voters_ID = '" + sVotersID + "' and Status = 'Active'";
ds = ddq.GetDataset(CommandType.Text);
return(ds.Tables[0].Rows.Count > 0 ? true : false);
}
catch (Exception ex)
{
throw ex;
}
}
public DataTable GetStudents(DateTime dDateFrom, DateTime dDateTo)
{
try
{
osb.ConnectionString = sConnectionString;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = osb.ConnectionString;
ddq.CommandText = "SELECT STUDENT_ID,(FIRST_NAME + ' ' + MIDDLE_NAME + ' ' + LAST_NAME) AS FULLNAME, DOB, AGE, COURSE,SECTION,CONTACT_NO,ADDRESS FROM TBL_STUDENTS WHERE DATE_ADDED BETWEEN '" + dDateFrom.ToString("yyyy-MM-dd") + "' AND '" + dDateTo.ToString("yyyy-MM-dd") + "'";
ds = ddq.GetDataset(CommandType.Text);
return(ds.Tables.Count > 0 ? ds.Tables[0] : null);
}
catch (Exception ex)
{
throw ex;
}
}
public DataTable getCandidates(DateTime dDateFrom, DateTime dDateTo, string sElectionCode)
{
try
{
osb.ConnectionString = sConnectionString;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = osb.ConnectionString;
ddq.CommandText = "Select b.CANDIDATE_ID, (a.FIRST_NAME + ' ' + a.MIDDLE_NAME + ' ' + a.LAST_NAME) AS FULLNAME,a.DOB,a.AGE,a.COURSE,a.SECTION,a.CONTACT_NO,a.ADDRESS,c.ID as POSITION_ID,c.POSITION,d.ID as PARTY_ID,d.PARTY,b.PROFILE_PIC from tbl_students a inner join tbl_candidates b on a.STUDENT_ID = b.CANDIDATE_ID inner join tbl_Position c on c.ID = b.POSITION_ID inner join tbl_Party d on d.ID = b.PARTY_ID where a.Status = 'ACTIVE' and b.Added_Date between '" + dDateFrom.ToString("yyyy-MM-dd") + "' AND '" + dDateTo.ToString("yyyy-MM-dd") + "' AND ELECTION_CODE ='" + sElectionCode + "'";
ds = ddq.GetDataset(CommandType.Text);
return(ds.Tables.Count > 0 ? ds.Tables[0] : null);
}
catch (Exception ex)
{
throw ex;
}
}
public Boolean IsRecordExists(Model.Borrower oData)
{
try
{
osb.ConnectionString = sConnectionString;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = osb.ConnectionString;
ddq.CommandText = "SELECT * FROM TBL_BORROWER WHERE BORROWER_ID = '" + oData.PERSON_ID + "'";
ds = ddq.GetDataset(CommandType.Text);
return(ds.Tables[0].Rows.Count > 0 ? true : false);
}
catch (Exception ex)
{
throw ex;
}
}
public Boolean IsRecordExists(Model.Student oData)
{
try
{
osb.ConnectionString = sConnectionString;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = osb.ConnectionString;
ddq.CommandText = "Select Student_ID from tbl_Students where Student_ID = '" + oData.UNIQUE_ID + "'";
ds = ddq.GetDataset(CommandType.Text);
return(ds.Tables[0].Rows.Count > 0 ? true : false);
}
catch (Exception ex)
{
throw ex;
}
}
public Boolean IsCustomerMember(string sBorrowerID)
{
try
{
osb.ConnectionString = sConnectionString;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = osb.ConnectionString;
ddq.CommandText = "SELECT * FROM TBL_BORROWER WHERE BORROWER_ID = '" + sBorrowerID + "' AND STATUS = 'ACTIVE' ";
ds = ddq.GetDataset(CommandType.Text);
return(ds.Tables[0].Rows.Count > 0 ? true : false);
}
catch (Exception ex)
{
throw ex;
}
}
public DataTable GetDashBoardCount(eVariable.FIND_BOOK oFilter)
{
try
{
osb.ConnectionString = sConnectionString;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = osb.ConnectionString;
string sQuery;
switch (oFilter)
{
case eVariable.FIND_BOOK.BOOK_REQUESTED:
sQuery = "SELECT COUNT(*) FROM TBL_BORROWER WHERE BORROWER_ID IN (SELECT BORROWER_ID FROM TBL_BORROWERREQUEST WHERE STATUS = 'REQUEST')";
break;
case eVariable.FIND_BOOK.BOOK_BORROWED:
sQuery = "SELECT COUNT(*) FROM TBL_BORROWER WHERE BORROWER_ID IN (SELECT BORROWER_ID FROM TBL_BORROWEDBOOKS WHERE STATUS = 'BORROWED')";
break;
case eVariable.FIND_BOOK.BOOK_RETURNED:
sQuery = "SELECT COUNT(DISTINCT B.BORROWER_ID) [COUNTER] TBL_BORROWER B INNER JOIN TBL_BORROWERREQUEST R ON B.BORROWER_ID = R.BORROWER_ID WHERE R.[STATUS] = 'RETURNED' GROUP BY B.BORROWER_ID";
break;
case eVariable.FIND_BOOK.BOOK_PENALTY:
sQuery = "SELECT COUNT(DISTINCT B.BORROWER_ID) [COUNTER] FROM TBL_BORROWER B INNER JOIN TBL_BORROWERREQUEST R ON B.BORROWER_ID = R.BORROWER_ID WHERE R.[STATUS] = 'DAMAGED' GROUP BY B.BORROWER_ID";
break;
default:
sQuery = "SELECT COUNT(DISTINCT B.BORROWER_ID) [COUNTER] FROM TBL_BORROWER B INNER JOIN TBL_BORROWERREQUEST R ON B.BORROWER_ID = R.BORROWER_ID WHERE R.[STATUS] = 'REQUEST' GROUP BY B.BORROWER_ID";
break;
}
ddq.CommandText = sQuery;
ds = ddq.GetDataset(CommandType.Text);
return(ds.Tables.Count > 0 ? ds.Tables[0] : null);
}
catch (Exception ex)
{
throw ex;
}
}
public DataTable getStudent(String sType, string sValue)
{
try
{
osb.ConnectionString = sConnectionString;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = osb.ConnectionString;
string sQuery;
switch (sType)
{
case "STUDENT ID":
sQuery = "SELECT * FROM TBL_STUDENTS WHERE STATUS = 'ACTIVE' AND STUDENT_ID LIKE '%" + sValue + "%'";
break;
case "FIRST NAME":
sQuery = "SELECT * FROM TBL_STUDENTS WHERE STATUS = 'ACTIVE' AND FIRST_NAME LIKE '%" + sValue + "%'";
break;
case "LAST NAME":
sQuery = "SELECT * FROM TBL_STUDENTS WHERE STATUS = 'ACTIVE' AND LAST_NAME LIKE '%" + sValue + "%'";
break;
case "INACTIVE":
sQuery = "SELECT * FROM TBL_STUDENTS WHERE STATUS = 'INACTIVE'";
break;
default:
sQuery = "SELECT * FROM TBL_STUDENTS WHERE STATUS = 'ACTIVE'";
break;
}
ddq.CommandText = sQuery;
ds = ddq.GetDataset(CommandType.Text);
return(ds.Tables.Count > 0 ? ds.Tables[0] : null);
}
catch (Exception ex)
{
throw ex;
}
}
public DataTable GetRecords(eVariable.FIND_BORROWER oFindType, string sFindText)
{
try
{
osb.ConnectionString = sConnectionString;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = osb.ConnectionString;
string sQuery;
switch (oFindType)
{
case eVariable.FIND_BORROWER.BORROWER_ID:
sQuery = "SELECT * FROM TBL_BORROWER WHERE STATUS = 'ACTIVE' AND BORROWER_ID LIKE '%" + sFindText + "%'";
break;
case eVariable.FIND_BORROWER.FIRST_NAME:
sQuery = "SELECT * FROM TBL_BORROWER WHERE STATUS = 'ACTIVE' AND FIRST_NAME LIKE '%" + sFindText + "%'";
break;
case eVariable.FIND_BORROWER.MIDDLE_NAME:
sQuery = "SELECT * FROM TBL_BORROWER WHERE STATUS = 'ACTIVE' AND MIDDLE_NAME LIKE '%" + sFindText + "%'";
break;
case eVariable.FIND_BORROWER.LAST_NAME:
sQuery = "SELECT * FROM TBL_BORROWER WHERE STATUS = 'ACTIVE' AND LAST_NAME LIKE '%" + sFindText + "%'";
break;
default:
sQuery = "SELECT * FROM TBL_BORROWER WHERE STATUS = 'ACTIVE' AND FIRST_NAME LIKE '%" + sFindText + "%'";
break;
}
ddq.CommandText = sQuery;
ds = ddq.GetDataset(CommandType.Text);
return(ds.Tables.Count > 0 ? ds.Tables[0] : null);
}
catch (Exception ex)
{
throw ex;
}
}
public DataTable GetBorrowerTransaction(eVariable.FIND_BOOK oFilter, string sFindText)
{
try
{
osb.ConnectionString = sConnectionString;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = osb.ConnectionString;
string sQuery;
switch (oFilter)
{
case eVariable.FIND_BOOK.BOOK_REQUESTED:
sQuery = "SELECT DISTINCT B.BORROWER_ID [ID],B.FIRST_NAME,B.MIDDLE_NAME,B.LAST_NAME,B.DOB,B.AGE,B.CONTACT_NO,B.[ADDRESS],R.ADDED_DATE FROM TBL_BORROWER B INNER JOIN TBL_BORROWERREQUEST R ON B.BORROWER_ID = R.BORROWER_ID WHERE R.[STATUS] = 'REQUEST' AND R.BORROWER_ID LIKE + '%" + sFindText + "%' GROUP BY B.BORROWER_ID,B.FIRST_NAME,B.MIDDLE_NAME,B.LAST_NAME,B.DOB,B.AGE,B.CONTACT_NO,B.[ADDRESS],R.ADDED_DATE";
break;
case eVariable.FIND_BOOK.BOOK_BORROWED:
sQuery = "SELECT DISTINCT B.BORROWER_ID [ID],B.FIRST_NAME,B.MIDDLE_NAME,B.LAST_NAME,B.DOB,B.AGE,B.CONTACT_NO,B.[ADDRESS],R.ADDED_DATE FROM TBL_BORROWER B INNER JOIN TBL_BORROWEDBOOKS R ON B.BORROWER_ID = R.BORROWER_ID WHERE R.[STATUS] = 'BORROWED' AND R.BORROWER_ID LIKE + '%" + sFindText + "%' GROUP BY B.BORROWER_ID,B.FIRST_NAME,B.MIDDLE_NAME,B.LAST_NAME,B.DOB,B.AGE,B.CONTACT_NO,B.[ADDRESS],R.ADDED_DATE";
break;
case eVariable.FIND_BOOK.BOOK_RETURNED:
sQuery = "SELECT DISTINCT B.BORROWER_ID [ID],B.FIRST_NAME,B.MIDDLE_NAME,B.LAST_NAME,B.DOB,B.AGE,B.CONTACT_NO,B.[ADDRESS],R.ADDED_DATE FROM TBL_BORROWER B INNER JOIN TBL_BORROWERREQUEST R ON B.BORROWER_ID = R.BORROWER_ID WHERE R.[STATUS] = 'RETURNED' AND R.BORROWER_ID LIKE + '%" + sFindText + "%' GROUP BY B.BORROWER_ID,B.FIRST_NAME,B.MIDDLE_NAME,B.LAST_NAME,B.DOB,B.AGE,B.CONTACT_NO,B.[ADDRESS],R.ADDED_DATE";
break;
case eVariable.FIND_BOOK.BOOK_PENALTY:
sQuery = "SELECT DISTINCT B.BORROWER_ID [ID],B.FIRST_NAME,B.MIDDLE_NAME,B.LAST_NAME,B.DOB,B.AGE,B.CONTACT_NO,B.[ADDRESS],R.ADDED_DATE FROM TBL_BORROWER B INNER JOIN TBL_BORROWERREQUEST R ON B.BORROWER_ID = R.BORROWER_ID WHERE R.[STATUS] = 'DAMAGED' AND R.BORROWER_ID LIKE + '%" + sFindText + "%' GROUP BY B.BORROWER_ID,B.FIRST_NAME,B.MIDDLE_NAME,B.LAST_NAME,B.DOB,B.AGE,B.CONTACT_NO,B.[ADDRESS],R.ADDED_DATE";
break;
default:
sQuery = "SELECT DISTINCT B.BORROWER_ID [ID],B.FIRST_NAME,B.MIDDLE_NAME,B.LAST_NAME,B.DOB,B.AGE,B.CONTACT_NO,B.[ADDRESS],R.ADDED_DATE FROM TBL_BORROWER B INNER JOIN TBL_BORROWERREQUEST R ON B.BORROWER_ID = R.BORROWER_ID WHERE R.[STATUS] = 'REQUEST' AND R.BORROWER_ID LIKE + '%" + sFindText + "%' GROUP BY B.BORROWER_ID,B.FIRST_NAME,B.MIDDLE_NAME,B.LAST_NAME,B.DOB,B.AGE,B.CONTACT_NO,B.[ADDRESS],R.ADDED_DATE";
break;
}
ddq.CommandText = sQuery;
ds = ddq.GetDataset(CommandType.Text);
return(ds.Tables.Count > 0 ? ds.Tables[0] : null);
}
catch (Exception ex)
{
throw ex;
}
}
public Boolean IsDatabaseExits()
{
try
{
osb.ConnectionString = sMasterDBConnection;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = osb.ConnectionString;
ddq.CommandText = "SELECT name FROM master.sys.databases WHERE name = 'iVotingSystem'";
ds = ddq.GetDataset(CommandType.Text);
return(ds.Tables[0].Rows.Count > 0 ? true : false);
}
catch (Exception ex)
{
return(false);
}
}
