public static IEnumerable<module> GetMyModules() { DBFactory db; SqlDataReader rdr; List<module> data = null; try { db = new DBFactory("CCATDBEntities"); rdr = db.ExecuteReader("MSI_GetLeftSideMenu", new SqlParameter("@roleName", GetRoleName())); data = new List<module>(); module record; while (rdr.Read()) { record = new module(); record.moduleId = Convert.ToInt32(rdr["moduleId"].ToString()); record.pageMenuGroups = rdr["pageMenuGroups"].ToString(); data.Add(record); } //Close the datareader rdr.Close(); } catch (Exception ex) { throw new Exception("Exception in DataQueries.GetMyModules:" + ex.Message); } return data.AsEnumerable<module>(); }
public static IEnumerable<pageMenuGroupId_and_pageMenuId> get_myPageMenuGroupIds_and_pageMenuIds(int _appId) { DBFactory db; SqlDataReader rdr; List<pageMenuGroupId_and_pageMenuId> data = null; try { db = new DBFactory("CCATDBEntities"); rdr = db.ExecuteReader("MSI_GetMenuDetails", new SqlParameter("@appId", _appId)); data = new List<pageMenuGroupId_and_pageMenuId>(); pageMenuGroupId_and_pageMenuId record; while (rdr.Read()) { record = new pageMenuGroupId_and_pageMenuId(); record.listOrder = Convert.ToInt32(rdr["listOrder"].ToString()); record.pageMenuGroupId = Convert.ToInt32(rdr["pageMenuGroupId"].ToString()); record.pageMenuId = Convert.ToInt32(rdr["pageMenuId"].ToString()); data.Add(record); } //Close the datareader rdr.Close(); } catch (Exception ex) { throw new Exception("Exception in DataQueries.get_myPageMenuGroupIds_and_pageMenuIds:" + ex.Message); } return data.AsEnumerable<pageMenuGroupId_and_pageMenuId>(); }
private List<ComplianceReportResult_Ext> BuildCommonReport(string where, string OrderBy, int pageSize, int pageNo) { DBFactory db; SqlDataReader rdr; List<ComplianceReportResult_Ext> data = null; try { db = new DBFactory("CCATDBEntities"); rdr = db.ExecuteReader("sp_ReportSearch", new SqlParameter("@userId", UserId.ToString()), new SqlParameter("@where", where), new SqlParameter("@OrderBy", OrderBy), new SqlParameter("@pageSize", pageSize), new SqlParameter("@pageNo", pageNo)); data = new List<ComplianceReportResult_Ext>(); ComplianceReportResult_Ext record; while (rdr.Read()) { record = new ComplianceReportResult_Ext(); record.LastName = rdr["LastName"].ToString(); record.FirstName = rdr["FirstName"].ToString(); record.ComPlaintId = rdr["ComPlaintId"].ToString(); record.LastFourSSN = rdr["LastFourSSN"].ToString(); record.AccountNumber = rdr["Accountnumber"].ToString(); if (rdr["UpdatedByLastName"] != DBNull.Value) record.UpdatedByLastName = rdr["UpdatedByLastName"].ToString(); if (rdr["UpdatedByFirstName"] != DBNull.Value) record.UpdatedByFirstName = rdr["UpdatedByFirstName"].ToString(); if (rdr["UpdatedByLastName"] != DBNull.Value && rdr["UpdatedByFirstName"] != DBNull.Value) record.UpdatedByFullName = rdr["UpdatedByLastName"].ToString() + " " + rdr["UpdatedByFirstName"].ToString(); if (rdr["AgencyId"] != DBNull.Value) record.AgencyId = rdr["AgencyId"].ToString(); if (rdr["ComplaintIssue"] != DBNull.Value) record.ComplaintIssue = rdr["ComplaintIssue"].ToString(); if (rdr["ComplaintDate"] != DBNull.Value) record.ComplaintDate = Convert.ToDateTime(rdr["ComplaintDate"]); if (rdr["ResolvedDate"] != DBNull.Value) record.ResolvedDate = Convert.ToDateTime(rdr["ResolvedDate"]); if (rdr["DateRequested"] != DBNull.Value) record.DateRequested = Convert.ToDateTime(rdr["DateRequested"]); if (rdr["DateSubmitted"] != DBNull.Value) record.DateSubmitted = Convert.ToDateTime(rdr["DateSubmitted"]); if (rdr["AgencyRequestDate"] != DBNull.Value) record.AgencyRequestDate = Convert.ToDateTime(rdr["AgencyRequestDate"]); if (rdr["OwnerResponseDate"] != DBNull.Value) record.OwnerResponseDate = Convert.ToDateTime(rdr["OwnerResponseDate"]); if (rdr["UpdatedDateTime"] != DBNull.Value) record.UpdatedDateTime = Convert.ToDateTime(rdr["UpdatedDateTime"]); if (rdr["ResponseTimeDays"] != DBNull.Value) record.ResponseTimeDays = Convert.ToInt32(rdr["ResponseTimeDays"]); if (rdr["TotalResponseTimeDays"] != DBNull.Value) record.TotalResponseTimeDays = Convert.ToInt32(rdr["TotalResponseTimeDays"]); if (!string.IsNullOrEmpty(rdr["AgentLastName"].ToString())) { record.AgentLastName = rdr["AgentLastName"].ToString(); record.AgentFullName = rdr["AgentLastName"].ToString() + " " + rdr["AgentFirstName"].ToString(); } if (!string.IsNullOrEmpty(rdr["AgentFirstName"].ToString())) { record.AgentFirstName = rdr["AgentFirstName"].ToString(); record.AgentFullName = record.AgentFullName + " " + rdr["AgentFirstName"].ToString(); } if (rdr["count_"] != DBNull.Value) record.count_ = Convert.ToInt32(rdr["count_"]); if (rdr["rowNo"] != DBNull.Value) record.rowNo = Convert.ToInt64(rdr["rowNo"]); if (record.ComplaintDate.ToString() == "1/1/1900 12:00:00 AM") { record.ComplaintDate = null; } data.Add(record); } //Close the datareader rdr.Close(); } catch (Exception ex) { throw new Exception("Exception in DataQueries.GetReportData:" + ex.Message); } return data; }
public JsonResult GetComplianceReportDataFiltered(string searchText, string managerIds, string collectorIds, int? page, int? pageSize) { DBFactory db; SqlDataReader rdr; List<ComplianceReportResult_Ext> data = null; string sortOptions = BuildCommonReportSortOptions(HttpContext.Request.QueryString["sort[0][field]"], HttpContext.Request.QueryString["sort[0][dir]"]); try { db = new DBFactory("CCATDBEntities"); rdr = db.ExecuteReader("sp_FilteredReportSearch", new SqlParameter("@userId", UserId.ToString()), new SqlParameter("@collectorUserIds", collectorIds), new SqlParameter("@OrderBy", sortOptions), new SqlParameter("@pageSize", (pageSize.HasValue ? pageSize.Value : 10)), new SqlParameter("@pageNo", (page.HasValue ? page.Value : 1))); data = new List<ComplianceReportResult_Ext>(); ComplianceReportResult_Ext record; while (rdr.Read()) { record = new ComplianceReportResult_Ext(); record.LastName = rdr["LastName"].ToString(); record.FirstName = rdr["FirstName"].ToString(); record.FullName = rdr["LastName"].ToString() + " " + rdr["FirstName"].ToString(); if (!string.IsNullOrEmpty(rdr["AgentLastName"].ToString())) { record.AgentLastName = rdr["AgentLastName"].ToString(); record.AgentFullName = rdr["AgentLastName"].ToString() + " " + rdr["AgentFirstName"].ToString(); } if (!string.IsNullOrEmpty(rdr["AgentFirstName"].ToString())) { record.AgentFirstName = rdr["AgentFirstName"].ToString(); record.AgentFullName = record.AgentFullName + " " + rdr["AgentFirstName"].ToString(); } record.ComPlaintId = rdr["ComPlaintId"].ToString(); record.LastFourSSN = rdr["LastFourSSN"].ToString(); record.AccountNumber = rdr["Accountnumber"].ToString(); if (rdr["AgencyId"] != DBNull.Value) record.AgencyId = rdr["AgencyId"].ToString(); if (rdr["ComplaintIssue"] != DBNull.Value) record.ComplaintIssue = rdr["ComplaintIssue"].ToString(); if (rdr["ComplaintDate"] != DBNull.Value) record.ComplaintDate = Convert.ToDateTime(rdr["ComplaintDate"]); if (rdr["ResolvedDate"] != DBNull.Value) record.ResolvedDate = Convert.ToDateTime(rdr["ResolvedDate"]); if (rdr["DateRequested"] != DBNull.Value) record.DateRequested = Convert.ToDateTime(rdr["DateRequested"]); if (rdr["DateSubmitted"] != DBNull.Value) record.DateSubmitted = Convert.ToDateTime(rdr["DateSubmitted"]); if (rdr["AgencyRequestDate"] != DBNull.Value) record.AgencyRequestDate = Convert.ToDateTime(rdr["AgencyRequestDate"]); if (rdr["OwnerResponseDate"] != DBNull.Value) record.OwnerResponseDate = Convert.ToDateTime(rdr["OwnerResponseDate"]); if (rdr["UpdatedDateTime"] != DBNull.Value) record.UpdatedDateTime = Convert.ToDateTime(rdr["UpdatedDateTime"]); if (rdr["ResponseTimeDays"] != DBNull.Value) record.ResponseTimeDays = Convert.ToInt32(rdr["ResponseTimeDays"]); if (rdr["TotalResponseTimeDays"] != DBNull.Value) record.TotalResponseTimeDays = Convert.ToInt32(rdr["TotalResponseTimeDays"]); if (rdr["count_"] != DBNull.Value) record.count_ = Convert.ToInt32(rdr["count_"]); if (rdr["rowNo"] != DBNull.Value) record.rowNo = Convert.ToInt64(rdr["rowNo"]); if (record.ComplaintDate.ToString() == "1/1/1900 12:00:00 AM") { record.ComplaintDate = null; } data.Add(record); } //Close the datareader rdr.Close(); } catch (Exception ex) { throw new Exception("Exception in DataQueries.GetReportData:" + ex.Message); } if (data.Count() > 0) { return Json(new { success = true, __count = data.FirstOrDefault().count_, results = data.ToList() }, JsonRequestBehavior.AllowGet); } else { return Json(new { success = true, __count = 0 }, JsonRequestBehavior.AllowGet); } }