private void cboSearchBy_SelectedIndexChanged(object sender, EventArgs e)
{
switch (cboSearchBy.Text)
{
case "TITLE":
oFilterBook = ePublicVariable.eVariable.FILTER_BOOK.BOOK_TITLE;
break;
case "AUTHOR":
oFilterBook = ePublicVariable.eVariable.FILTER_BOOK.BOOK_AUTHOR;
break;
case "CATEGORY":
oFilterBook = ePublicVariable.eVariable.FILTER_BOOK.BOOK_CATEGORY;
break;
default:
oFilterBook = ePublicVariable.eVariable.FILTER_BOOK.BOOK_TITLE;
break;
}
}
private void MAIN_Load(object sender, EventArgs e)
{
LoadRecords();
oFilterBook = eVariable.FILTER_BOOK.BOOK_TITLE;
}
public DataTable GetBookRecords(eVariable.FILTER_BOOK oFilter, string sStatus, string sFindText)
{
try
{
osb.ConnectionString = sConnectionString;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = osb.ConnectionString;
string sQuery;
switch (oFilter)
{
case eVariable.FILTER_BOOK.BOOK_TITLE:
sQuery = " SELECT B.ID AS [BOOK_ID], B.TITLE,B.[SUBJECT],B.CATEGORY,B.AUTHOR,B.PUBLISH_DATE,B.BOOK_PRICE,B.RENT_PRICE,B.LOCATION, " +
" COUNT(*) - " +
" ((SELECT COUNT(*) FROM TBL_BORROWERREQUEST WHERE BOOK_ID = B.ID AND [STATUS] = 'REQUEST') + (SELECT COUNT(*) FROM TBL_BORROWEDBOOKS WHERE BOOK_ID = B.ID AND [STATUS] = 'BORROWED')) AS [COPIES_AVAILABLE], " +
" (SELECT COUNT(*) FROM TBL_BOOKS TB WHERE TB.ID = B.ID AND TB.STATUS = 'ACTIVE')[TOTAL_COPIES] " +
" FROM TBL_BOOKS AS B WHERE B.TITLE LIKE '%" + sFindText + "%' AND B.[STATUS] = '" + sStatus + "' " +
" GROUP BY ID, TITLE,[SUBJECT],CATEGORY,AUTHOR,PUBLISH_DATE,BOOK_PRICE,RENT_PRICE,LOCATION ";
break;
case eVariable.FILTER_BOOK.BOOK_CATEGORY:
sQuery = " SELECT B.ID AS [BOOK_ID], B.TITLE,B.[SUBJECT],B.CATEGORY,B.AUTHOR,B.PUBLISH_DATE,B.BOOK_PRICE,B.RENT_PRICE,B.LOCATION, " +
" COUNT(*) - " +
" ((SELECT COUNT(*) FROM TBL_BORROWERREQUEST WHERE BOOK_ID = B.ID AND [STATUS] = 'REQUEST') + (SELECT COUNT(*) FROM TBL_BORROWEDBOOKS WHERE BOOK_ID = B.ID AND [STATUS] = 'BORROWED')) AS [COPIES_AVAILABLE], " +
" (SELECT COUNT(*) FROM TBL_BOOKS TB WHERE TB.ID = B.ID AND TB.STATUS = 'ACTIVE')[TOTAL_COPIES] " +
" FROM TBL_BOOKS AS B WHERE B.CATEGORY LIKE '%" + sFindText + "%' AND B.[STATUS] = '" + sStatus + "' " +
" GROUP BY ID, TITLE,[SUBJECT],CATEGORY,AUTHOR,PUBLISH_DATE,BOOK_PRICE,RENT_PRICE,LOCATION ";
break;
case eVariable.FILTER_BOOK.BOOK_AUTHOR:
sQuery = " SELECT B.ID AS [BOOK_ID], B.TITLE,B.[SUBJECT],B.CATEGORY,B.AUTHOR,B.PUBLISH_DATE,B.BOOK_PRICE,B.RENT_PRICE,B.LOCATION, " +
" COUNT(*) - " +
" ((SELECT COUNT(*) FROM TBL_BORROWERREQUEST WHERE BOOK_ID = B.ID AND [STATUS] = 'REQUEST') + (SELECT COUNT(*) FROM TBL_BORROWEDBOOKS WHERE BOOK_ID = B.ID AND [STATUS] = 'BORROWED')) AS [COPIES_AVAILABLE], " +
" (SELECT COUNT(*) FROM TBL_BOOKS TB WHERE TB.ID = B.ID AND TB.STATUS = 'ACTIVE')[TOTAL_COPIES] " +
" FROM TBL_BOOKS AS B WHERE B.AUTHOR LIKE '%" + sFindText + "%' AND B.[STATUS] = '" + sStatus + "' " +
" GROUP BY ID, TITLE,[SUBJECT],CATEGORY,AUTHOR,PUBLISH_DATE,BOOK_PRICE,RENT_PRICE,LOCATION ";
break;
case eVariable.FILTER_BOOK.BOOK_INACTIVE:
sQuery = " SELECT B.ID AS [BOOK_ID], B.TITLE,B.[SUBJECT],B.CATEGORY,B.AUTHOR,B.PUBLISH_DATE,B.BOOK_PRICE,B.RENT_PRICE,B.LOCATION, " +
" COUNT(*) - " +
" ((SELECT COUNT(*) FROM TBL_BORROWERREQUEST WHERE BOOK_ID = B.ID AND [STATUS] = 'REQUEST') + (SELECT COUNT(*) FROM TBL_BORROWEDBOOKS WHERE BOOK_ID = B.ID AND [STATUS] = 'BORROWED')) AS [COPIES_AVAILABLE], " +
" (SELECT COUNT(*) FROM TBL_BOOKS TB WHERE TB.ID = B.ID AND TB.STATUS = 'ACTIVE')[TOTAL_COPIES] " +
" FROM TBL_BOOKS AS B WHERE B.[STATUS] = 'INACTIVE' " +
" GROUP BY ID, TITLE,[SUBJECT],CATEGORY,AUTHOR,PUBLISH_DATE,BOOK_PRICE,RENT_PRICE,LOCATION ";
break;
case eVariable.FILTER_BOOK.NONE:
sQuery = " SELECT B.ID AS [BOOK_ID], B.TITLE,B.[SUBJECT],B.CATEGORY,B.AUTHOR,B.PUBLISH_DATE,B.BOOK_PRICE,B.RENT_PRICE,B.LOCATION, " +
" COUNT(*) - " +
" ((SELECT COUNT(*) FROM TBL_BORROWERREQUEST WHERE BOOK_ID = B.ID AND [STATUS] = 'REQUEST') + (SELECT COUNT(*) FROM TBL_BORROWEDBOOKS WHERE BOOK_ID = B.ID AND [STATUS] = 'BORROWED')) AS [COPIES_AVAILABLE], " +
" (SELECT COUNT(*) FROM TBL_BOOKS TB WHERE TB.ID = B.ID AND TB.STATUS = 'ACTIVE')[TOTAL_COPIES] " +
" FROM TBL_BOOKS AS B WHERE B.[STATUS] = 'ACTIVE' " +
" GROUP BY ID, TITLE,[SUBJECT],CATEGORY,AUTHOR,PUBLISH_DATE,BOOK_PRICE,RENT_PRICE,LOCATION ";
break;
case eVariable.FILTER_BOOK.BOOK_DEFAULT:
sQuery = "SELECT * FROM TBL_BOOKS WHERE ID = '" + sFindText + "'";
break;
default:
sQuery = " SELECT B.ID AS [BOOK_ID], B.TITLE,B.[SUBJECT],B.CATEGORY,B.AUTHOR,B.PUBLISH_DATE,B.BOOK_PRICE,B.RENT_PRICE,B.LOCATION, " +
" COUNT(*) - " +
" ((SELECT COUNT(*) FROM TBL_BORROWERREQUEST WHERE BOOK_ID = B.ID AND [STATUS] = 'REQUEST') + (SELECT COUNT(*) FROM TBL_BORROWEDBOOKS WHERE BOOK_ID = B.ID AND [STATUS] = 'BORROWED')) AS [COPIES_AVAILABLE], " +
" (SELECT COUNT(*) FROM TBL_BOOKS TB WHERE TB.ID = B.ID AND TB.STATUS = 'ACTIVE')[TOTAL_COPIES] " +
" FROM TBL_BOOKS AS B WHERE B.[STATUS] = 'ACTIVE' " +
" GROUP BY ID, TITLE,[SUBJECT],CATEGORY,AUTHOR,PUBLISH_DATE,BOOK_PRICE,RENT_PRICE,LOCATION ";
break;
}
ddq.CommandText = sQuery;
ds = ddq.GetDataset(CommandType.Text);
return(ds.Tables.Count > 0 ? ds.Tables[0] : null);
}
catch (Exception ex)
{
throw ex;
}
}
