private void cboStatus_SelectedIndexChanged(object sender, EventArgs e)
{
switch (cboStatus.Text)
{
case "BORROWER RECORD LIST":
e_ReportStatus = ePublicVariable.eVariable.BORROWER_STATUS.BORROWER_RECORDLIST;
break;
case "UNRETURNED BOOKS":
e_ReportStatus = ePublicVariable.eVariable.BORROWER_STATUS.UNRETURNED_BOOKS;
break;
case "REQUESTED BOOKS":
e_ReportStatus = ePublicVariable.eVariable.BORROWER_STATUS.REQUESTED_BOOKS;
break;
default:
e_ReportStatus = ePublicVariable.eVariable.BORROWER_STATUS.BORROWER_RECORDLIST;
break;
}
}
public DataTable Get_BorrowerReport(ePublicVariable.eVariable.BORROWER_STATUS e_ReportStatus, DateTime dDateFrom, DateTime dDateTo)
{
try
{
string sQuery = "";
osb.ConnectionString = sConnectionString;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = osb.ConnectionString;
switch (e_ReportStatus)
{
case eVariable.BORROWER_STATUS.BORROWER_RECORDLIST:
sQuery = "SELECT * FROM TBL_BORROWER WHERE STATUS = 'ACTIVE' AND ADDED_DATE BETWEEN '" + dDateFrom.ToString("yyyy-MM-dd") + "' AND '" + dDateTo.ToString("yyyy-MM-dd") + "'";
break;
case eVariable.BORROWER_STATUS.REQUESTED_BOOKS:
sQuery = " SELECT A.BORROWER_ID, B.FIRST_NAME, B.MIDDLE_NAME, B.LAST_NAME, A.ADDED_DATE FROM TBL_BORROWERREQUEST A INNER JOIN TBL_BORROWER B ON A.BORROWER_ID = B.BORROWER_ID " +
" WHERE A.[STATUS]= 'REQUEST' AND A.ADDED_DATE BETWEEN '" + dDateFrom.ToString("yyyy-MM-dd") + "' AND '" + dDateTo.ToString("yyyy-MM-dd") + "' GROUP BY A.BORROWER_ID, B.FIRST_NAME, B.MIDDLE_NAME, B.LAST_NAME,A.ADDED_DATE";
break;
case eVariable.BORROWER_STATUS.UNRETURNED_BOOKS:
sQuery = " SELECT A.BORROWER_ID, B.FIRST_NAME, B.MIDDLE_NAME, B.LAST_NAME, A.ADDED_DATE FROM TBL_BORROWEDBOOKS A INNER JOIN TBL_BORROWER B ON A.BORROWER_ID = B.BORROWER_ID " +
" WHERE A.[STATUS]= 'BORROWED' AND A.ADDED_DATE BETWEEN '" + dDateFrom.ToString("yyyy-MM-dd") + "' AND '" + dDateTo.ToString("yyyy-MM-dd") + "' GROUP BY A.BORROWER_ID, B.FIRST_NAME, B.MIDDLE_NAME, B.LAST_NAME,A.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 Get_BorrowerSubReport(ePublicVariable.eVariable.BORROWER_STATUS e_ReportStatus, string sBorrower_ID, DateTime dDateFrom, DateTime dDateTo)
{
try
{
string sQuery = "";
osb.ConnectionString = sConnectionString;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = osb.ConnectionString;
switch (e_ReportStatus)
{
case eVariable.BORROWER_STATUS.BORROWER_RECORDLIST:
sQuery = "SELECT * FROM TBL_BORROWER WHERE STATUS = 'ACTIVE' AND ADDED_DATE BETWEEN '" + dDateFrom.ToString("yyyy-MM-dd") + "' AND '" + dDateTo.ToString("yyyy-MM-dd") + "'";
break;
case eVariable.BORROWER_STATUS.REQUESTED_BOOKS:
sQuery = " SELECT BORROWER_ID, BOOK_NO, TITLE, AUTHOR, ISBN_NUMBER, BOOK_PRICE, RENT_PRICE, DAYS_BORROWED " +
" FROM TBL_BORROWERREQUEST WHERE [STATUS] = 'REQUEST' AND ADDED_DATE BETWEEN '" + dDateFrom.ToString("yyyy-MM-dd") + "' AND '" + dDateTo.ToString("yyyy-MM-dd") + "' AND BORROWER_ID = '" + sBorrower_ID + "'";
break;
case eVariable.BORROWER_STATUS.UNRETURNED_BOOKS:
sQuery = " SELECT BORROWER_ID, BOOK_NO, TITLE, AUTHOR, ISBN_NUMBER, BOOK_PRICE, RENT_PRICE, DAYS_BORROWED " +
" FROM TBL_BORROWEDBOOKS WHERE [STATUS] = 'BORROWED' AND ADDED_DATE BETWEEN '" + dDateFrom.ToString("yyyy-MM-dd") + "' AND '" + dDateTo.ToString("yyyy-MM-dd") + "' AND BORROWER_ID = '" + sBorrower_ID + "'";
break;
}
ddq.CommandText = sQuery;
ds = ddq.GetDataset(CommandType.Text);
return(ds.Tables.Count > 0 ? ds.Tables[0] : null);
}
catch (Exception ex)
{
throw ex;
}
}
private void frmBorrowerReport_Load(object sender, EventArgs e)
{
dtDateFrom.Value = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd"));
dtDateTo.Value = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd"));
e_ReportStatus = ePublicVariable.eVariable.BORROWER_STATUS.BORROWER_RECORDLIST;
}