public List<LibrariesInfo> Wcmm_Report(SearchInfo isearch, ListOptions options, out int numResults)
{
try
{
List<LibrariesInfo> arr = null;
using (iSqlConnection iConn = HELPER.getConnect(HELPER.SQL_SYSTEM))
{
string SQL = SQL_GETIFOPAGING.Replace(Queryparam.Varstring.VAR_SORTEXPRESSION, CFunctions.Expression_GetSort(options.SortExp, options.SortDir));
isearch.Setof_Category = isearch.Cid == 0 ? "" : (new CCategory(LANG)).Get_setof(isearch.Cid, "");
SQL += isearch.Cid == 0 ? "" : " AND A.cid IN(" + isearch.Setof_Category + ")";
isearch.Searchquery = CGeneral.Get_Searchquery(isearch.Keywords);
SQL += CFunctions.IsNullOrEmpty(isearch.Searchquery) ? "" : " AND (" + isearch.Searchquery + ")";
SQL += " AND (A.timeupdate BETWEEN '" + isearch.Datefr + "' AND '" + isearch.Dateto + "')";
SQL = "SELECT * FROM(" + SQL + ") AS T WHERE id<>0 " + CFunctions.Expression_GetLimit(options.PageIndex, options.PageSize);
using (iSqlDataReader dar = HELPER.executeReader(iConn, SQL))
{
while (dar.Read())
{
LibrariesInfo info = this.getDataReader(dar);
if (arr == null)
arr = new List<LibrariesInfo>();
arr.Add(info);
}
}
numResults = this.Wcmm_Reportcount(iConn, isearch, options);
iConn.Close();
}
return arr;
}
catch (Exception ex)
{
throw ex;
}
}
private int Wcmm_Reportcount(iSqlConnection iConn, SearchInfo isearch, ListOptions options)
{
try
{
int numResults = 0;
string SQL = SQL_COUNT.Replace(Queryparam.Varstring.VAR_TABLENAME, TABLENAME);
SQL += CFunctions.IsNullOrEmpty(isearch.Setof_Category) ? "" : " AND A.cid IN(" + isearch.Setof_Category + ")";
SQL += string.IsNullOrEmpty(isearch.Searchquery) ? "" : " AND (" + isearch.Searchquery + ")";
SQL += " AND (A.timeupdate BETWEEN '" + isearch.Datefr + "' AND '" + isearch.Dateto + "')";
using (iSqlDataReader dar = HELPER.executeReader(iConn, SQL))
{
if (dar.Read())
{
numResults = dar.IsDBNull(0) ? 0 : dar.GetInt32(0);
}
}
return numResults;
}
catch
{
return 0;
}
}
private List<LibrariesInfo> Search(out int numResults)
{
SearchInfo isearch = new SearchInfo();
isearch.Cid = CFunctions.IsNullOrEmpty(ddlCid.SelectedValue) ? 0 : int.Parse(ddlCid.SelectedValue);
isearch.Datefr = CFunctions.IsNullOrEmpty(txtDatefr.Text.Trim()) ? CFunctions.Get_Datetime(CFunctions.Set_Datetime(DateTime.Now)) : CFunctions.Get_Datetime(txtDatefr.Text.Trim());
isearch.Datefr = new DateTime(isearch.Datefr.Year, isearch.Datefr.Month, isearch.Datefr.Day, 0, 0, 0);
isearch.Dateto = CFunctions.IsNullOrEmpty(txtDateto.Text.Trim()) ? CFunctions.Get_Datetime(CFunctions.Set_Datetime(DateTime.Now)) : CFunctions.Get_Datetime(txtDateto.Text.Trim());
isearch.Dateto = new DateTime(isearch.Dateto.Year, isearch.Dateto.Month, isearch.Dateto.Day, 23, 59, 59);
ListOptions options = Get_ListOptions();
if (!CFunctions.IsNullOrEmpty(txtPagesize.Text))
options.PageSize = int.Parse(txtPagesize.Text);
options.SortExp = radReporttype.SelectedValue;
numResults = 0;
List<LibrariesInfo> list = (new CLibraries(CCommon.LANG)).Wcmm_Report(isearch, options, out numResults);
return list;
}