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; }