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 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 GetTransactionBookRecordPerStatus(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_BORROWED:
sQuery = "SELECT BOOK_ID, TITLE, [SUBJECT], CATEGORY, AUTHOR, PUBLISH_DATE, LOCATION, BOOK_PRICE, RENT_PRICE, DUE_PENALTY_INTEREST, LOST_DAMAGE_INTEREST,COUNT(*) BOOK_COUNT,DAYS_BORROWED FROM TBL_BORROWEDBOOKS WHERE [STATUS] = 'BORROWED' GROUP BY BOOK_ID, TITLE, [SUBJECT], CATEGORY, AUTHOR, PUBLISH_DATE, LOCATION, BOOK_PRICE, RENT_PRICE, DUE_PENALTY_INTEREST, LOST_DAMAGE_INTEREST,DAYS_BORROWED";
break;
case eVariable.FIND_BOOK.BOOK_RETURNED:
sQuery = "SELECT BOOK_ID, TITLE, [SUBJECT], CATEGORY, AUTHOR, PUBLISH_DATE, LOCATION, BOOK_PRICE, RENT_PRICE, DUE_PENALTY_INTEREST, LOST_DAMAGE_INTEREST,COUNT(*) BOOK_COUNT,DAYS_BORROWED FROM TBL_BORROWEDBOOKS WHERE [STATUS] = 'RETURNED' GROUP BY BOOK_ID, TITLE, [SUBJECT], CATEGORY, AUTHOR, PUBLISH_DATE, LOCATION, BOOK_PRICE, RENT_PRICE, DUE_PENALTY_INTEREST, LOST_DAMAGE_INTEREST,DAYS_BORROWED";
break;
case eVariable.FIND_BOOK.BOOK_REQUESTED:
sQuery = "SELECT BOOK_ID, TITLE, [SUBJECT], CATEGORY, AUTHOR, PUBLISH_DATE, LOCATION, BOOK_PRICE, RENT_PRICE, DUE_PENALTY_INTEREST, LOST_DAMAGE_INTEREST,COUNT(*) BOOK_COUNT,DAYS_BORROWED FROM TBL_BORROWEDBOOKS WHERE [STATUS] = 'REQUESTED' GROUP BY BOOK_ID, TITLE, [SUBJECT], CATEGORY, AUTHOR, PUBLISH_DATE, LOCATION, BOOK_PRICE, RENT_PRICE, DUE_PENALTY_INTEREST, LOST_DAMAGE_INTEREST,DAYS_BORROWED";
break;
case eVariable.FIND_BOOK.BOOK_PENALTY:
sQuery = "SELECT BOOK_ID, TITLE, [SUBJECT], CATEGORY, AUTHOR, PUBLISH_DATE, LOCATION, BOOK_PRICE, RENT_PRICE, DUE_PENALTY_INTEREST, LOST_DAMAGE_INTEREST,COUNT(*) BOOK_COUNT,DAYS_BORROWED FROM TBL_BORROWEDBOOKS WHERE [STATUS] = 'DAMAGED' GROUP BY BOOK_ID, TITLE, [SUBJECT], CATEGORY, AUTHOR, PUBLISH_DATE, LOCATION, BOOK_PRICE, RENT_PRICE, DUE_PENALTY_INTEREST, LOST_DAMAGE_INTEREST,DAYS_BORROWED";
break;
default:
sQuery = "SELECT BOOK_ID, TITLE, [SUBJECT], CATEGORY, AUTHOR, PUBLISH_DATE, LOCATION, BOOK_PRICE, RENT_PRICE, DUE_PENALTY_INTEREST, LOST_DAMAGE_INTEREST,COUNT(*) BOOK_COUNT,DAYS_BORROWED FROM TBL_BORROWEDBOOKS WHERE [STATUS] = 'BORROWED' GROUP BY BOOK_ID, TITLE, [SUBJECT], CATEGORY, AUTHOR, PUBLISH_DATE, LOCATION, BOOK_PRICE, RENT_PRICE, DUE_PENALTY_INTEREST, LOST_DAMAGE_INTEREST,DAYS_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 void GetBorrowerRequest()
{
oBorrower = new DataAccess.Borrower();
dgDetails.DataSource = oBorrower.GetBorrowerTransaction(ePublicVariable.eVariable.FIND_BOOK.BOOK_REQUESTED, "");
foreach (DataGridViewColumn col in dgDetails.Columns)
{
col.Width = 115;
//col.AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
if (col.Name == "ADDRESS")
{
col.AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
}
}
ChangeCellGridColor();
lblTotalRecord.Text = dgDetails.Rows.Count.ToString();
TranType = eVariable.FIND_BOOK.BOOK_REQUESTED;
}
public frmDisplayBorrowedBook(Model.Borrower oData, eVariable.FIND_BOOK oType)
{
InitializeComponent();
oMBorrower = oData;
TranType = oType;
}
