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