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