/// <summary> /// To Get the data of Compliance Indicator Report /// </summary> /// <param name="gridSettings">The grid settings.</param> /// <param name="viewModel">The view model.</param> /// <returns></returns> public ActionResult ComplianceGetData(GridSettings gridSettings, SearchCriteriaViewModel viewModel) { if (ModelState.IsValid) { if (string.IsNullOrEmpty(gridSettings.SortColumn)) { gridSettings.SortOrder = "DESC"; } try { // pass the projet type as part of search criteria var projecType = viewModel.ProjectType; var searchCriteria = GetValues(viewModel); searchCriteria.ProjectType = projecType; var data = PatService.GetComplianceRiskIndicatorReport(gridSettings, searchCriteria); if (data != null) { var totalRecords = viewModel.TotalRecords; // PatService.CountComplianceRiskIndicatorReport(searchCriteria); var jsonData = new { total = AppHelper.PagesInTotal(totalRecords, gridSettings.PageSize), page = gridSettings.PageIndex, records = totalRecords, rows = ( from e in data.AsEnumerable() select new { id = 1, cell = new List <string> { e.OrgCode, e.ESACode, e.SiteCode, e.ProjectType, string.Format("{0}", e.TotalCompliancePoint), string.Format("{0}", e.ComplianceIndicator), string.Format("{0}", e.InProgressReviewCount), string.Format("{0}", e.CompletedReviewCount), string.Format("{0}", e.TotalReviewCount), string.Format("{0}", e.TotalRecoveryAmount), string.Format("{0}", e.ValidCount), string.Format("{0}", e.ValidAdminCount), string.Format("{0}", e.InvalidAdminCount), string.Format("{0}", e.InvalidRecovery), string.Format("{0}", e.InvalidNoRecovery) } } ).ToArray() }; return(Json(jsonData, JsonRequestBehavior.AllowGet)); } } catch (Exception ex) { Elmah.ErrorLog.GetDefault(null).Log(new Elmah.Error(ex)); throw; } } return(null); }
/// <summary> /// Gets the finding summary report. /// </summary> /// <param name="viewModel">The view model.</param> /// <returns></returns> public FindingSummary GetFindingSummaryReport(SearchCriteriaViewModel viewModel) { var results = new FindingSummary(); using (var connection = new SqlConnection(DbConnection)) { using (var command = new SqlCommand("PaReportFindingSummary", connection)) { var sqlParams = new List <SqlParameter>(); var paramReturnValue = new SqlParameter("@return_value", SqlDbType.Int) { Direction = ParameterDirection.ReturnValue }; sqlParams.Add(paramReturnValue); SqlHelper.AddVarcharPara(viewModel.OrgCode, "@OrgCode", sqlParams); SqlHelper.AddVarcharPara(viewModel.ESACode, "@ESACode", sqlParams); SqlHelper.AddVarcharPara(viewModel.SiteCode, "@SiteCode", sqlParams); SqlHelper.AddVarcharPara(viewModel.ProjectID, "@ProjectID", sqlParams); SqlHelper.AddVarcharPara(viewModel.ProjectType, "@ProjectType", sqlParams); SqlHelper.AddVarcharPara(viewModel.ContractType, "@ContractType", sqlParams); SqlHelper.AddDatePara(viewModel.UploadDateFrom, "@UploadDateFrom", sqlParams); SqlHelper.AddDatePara(viewModel.UploadDateTo, "@UploadDateTo", sqlParams); command.CommandType = CommandType.StoredProcedure; command.Parameters.AddRange(sqlParams.ToArray()); connection.Open(); SqlDataReader reader = null; try { reader = command.ExecuteReader(); while (reader.HasRows) { while (reader.Read()) { var findingSummaryDetail = new FindingSummaryDetail { Type = string.Format("{0}", reader["Type"]), Code = string.Format("{0}", reader["Code"]), Description = string.Format("{0}", reader["Description"]), ReviewCount = AppHelper.ToDecimal(reader["ReviewCount"]) }; var findingSummaryType = GetFindingSummaryType(findingSummaryDetail.Type, results); findingSummaryType.Add(findingSummaryDetail); } reader.NextResult(); } } finally { if (reader != null) { reader.Close(); } } } } return(results); }
/// <summary> /// Gets the dashboard report. /// </summary> /// <param name="viewModel">The view model.</param> /// <returns></returns> public List <Dashboard> GetDashboardReport(SearchCriteriaViewModel viewModel) { var results = new List <Dashboard>(); using (var connection = new SqlConnection(DbConnection)) { using (var command = new SqlCommand("PaReportDashboard", connection)) { var sqlParams = new List <SqlParameter>(); var paramReturnValue = new SqlParameter("@return_value", SqlDbType.Int) { Direction = ParameterDirection.ReturnValue }; sqlParams.Add(paramReturnValue); SqlHelper.AddVarcharPara(viewModel.OrgCode, "@OrgCode", sqlParams); SqlHelper.AddVarcharPara(viewModel.ESACode, "@ESACode", sqlParams); SqlHelper.AddVarcharPara(viewModel.SiteCode, "@SiteCode", sqlParams); SqlHelper.AddVarcharPara(viewModel.ProjectID, "@ProjectID", sqlParams); SqlHelper.AddVarcharPara(viewModel.ProjectType, "@ProjectType", sqlParams); SqlHelper.AddVarcharPara(viewModel.ContractType, "@ContractType", sqlParams); SqlHelper.AddDatePara(viewModel.UploadDateFrom, "@UploadDateFrom", sqlParams); SqlHelper.AddDatePara(viewModel.UploadDateTo, "@UploadDateTo", sqlParams); command.CommandType = CommandType.StoredProcedure; command.Parameters.AddRange(sqlParams.ToArray()); connection.Open(); SqlDataReader reader = null; try { reader = command.ExecuteReader(); while (reader.Read()) { results.Add(new Dashboard { OutcomeCode = "InProgress", OutcomeDescription = "In Progress", ReviewCount = AppHelper.ToInt(reader["InProgressCount"]) }); results.Add(new Dashboard { OutcomeCode = "Completed", OutcomeDescription = "Completed", ReviewCount = AppHelper.ToInt(reader["CompletedCount"]) }); results.Add(new Dashboard { OutcomeCode = "VAN", OutcomeDescription = "Valid (NFA)", ReviewCount = AppHelper.ToInt(reader["OutcomeCodeVANCount"]) }); results.Add(new Dashboard { OutcomeCode = "VAD", OutcomeDescription = "Valid (Admin Deficiency – Provider Education)", ReviewCount = AppHelper.ToInt(reader["OutcomeCodeVADCount"]) }); results.Add(new Dashboard { OutcomeCode = "INR", OutcomeDescription = "Invalid (Recovery)", ReviewCount = AppHelper.ToInt(reader["OutcomeCodeINRCount"]) }); results.Add(new Dashboard { OutcomeCode = "INN", OutcomeDescription = "Invalid (No Recovery)", ReviewCount = AppHelper.ToInt(reader["OutcomeCodeINNCount"]) }); results.Add(new Dashboard { OutcomeCode = "IAD", OutcomeDescription = "Invalid (Admin Deficiency - Provider Education)", ReviewCount = AppHelper.ToInt(reader["OutcomeCodeIADCount"]) }); } } finally { if (reader != null) { reader.Close(); } } } } return(results); }
/// <summary> /// Gets the progress report. /// </summary> /// <param name="gridSettings">The grid settings.</param> /// <param name="viewModel">The view model.</param> /// <returns></returns> public List <Progress> GetProgressReport(GridSettings gridSettings, SearchCriteriaViewModel viewModel) { var results = new List <Progress>(); using (var connection = new SqlConnection(DbConnection)) { using (var command = new SqlCommand("PaReportProgresses", connection)) { var sqlParams = new List <SqlParameter>(); var paramReturnValue = new SqlParameter("@return_value", SqlDbType.Int) { Direction = ParameterDirection.ReturnValue }; sqlParams.Add(paramReturnValue); SqlHelper.AddIntPara(gridSettings.PageIndex, "@pageIndex", sqlParams); SqlHelper.AddIntPara(gridSettings.PageSize, "@pageSize", sqlParams); SqlHelper.AddVarcharPara(viewModel.OrgCode, "@OrgCode", sqlParams); SqlHelper.AddVarcharPara(viewModel.ESACode, "@ESACode", sqlParams); SqlHelper.AddVarcharPara(viewModel.SiteCode, "@SiteCode", sqlParams); SqlHelper.AddVarcharPara(viewModel.ProjectID, "@ProjectID", sqlParams); SqlHelper.AddVarcharPara(viewModel.ProjectType, "@ProjectType", sqlParams); SqlHelper.AddVarcharPara(viewModel.ContractType, "@ContractType", sqlParams); SqlHelper.AddDatePara(viewModel.UploadDateFrom, "@UploadDateFrom", sqlParams); SqlHelper.AddDatePara(viewModel.UploadDateTo, "@UploadDateTo", sqlParams); SqlHelper.AddVarcharPara(viewModel.SortColumn, "@SortColumn", sqlParams); SqlHelper.AddVarcharPara(viewModel.SortBy, "@SortBy", sqlParams); command.CommandType = CommandType.StoredProcedure; command.Parameters.AddRange(sqlParams.ToArray()); connection.Open(); SqlDataReader reader = null; try { reader = command.ExecuteReader(); while (reader.Read()) { var record = new Progress { SampleName = string.Format("{0}", reader["UploadName"]), ProjectType = string.Format("{0}", reader["ProjectType"]), InProgressReviewCount = AppHelper.ToInt(reader["InProgressReviews"]), CompletedReviewCount = AppHelper.ToInt(reader["CompletedReviews"]), TotalReviewCount = AppHelper.ToInt(reader["TotalReviews"]), PercentCompleted = AppHelper.ToDecimal(reader["PercentCompleted"]), LastUpdateDate = reader["LastUpdateDate"] as DateTime? ?? default(DateTime) }; results.Add(record); } } finally { if (reader != null) { reader.Close(); } } } } return(results); }
/// <summary> /// Gets the compliance risk indicator report. /// </summary> /// <param name="gridSettings">The grid settings.</param> /// <param name="viewModel">The view model.</param> /// <returns></returns> public List <ComplianceRiskIndicator> GetComplianceRiskIndicatorReport(GridSettings gridSettings, SearchCriteriaViewModel viewModel) { var results = new List <ComplianceRiskIndicator>(); using (var connection = new SqlConnection(DbConnection)) { using (var command = new SqlCommand("PaReportComplianceIndicators", connection)) { var sqlParams = new List <SqlParameter>(); var paramReturnValue = new SqlParameter("@return_value", SqlDbType.Int) { Direction = ParameterDirection.ReturnValue }; sqlParams.Add(paramReturnValue); SqlHelper.AddIntPara(gridSettings.PageIndex, "@pageIndex", sqlParams); SqlHelper.AddIntPara(gridSettings.PageSize, "@pageSize", sqlParams); SqlHelper.AddVarcharPara(viewModel.OrgCode, "@OrgCode", sqlParams); SqlHelper.AddVarcharPara(viewModel.ESACode, "@ESACode", sqlParams); SqlHelper.AddVarcharPara(viewModel.SiteCode, "@SiteCode", sqlParams); SqlHelper.AddVarcharPara(viewModel.ProjectID, "@ProjectID", sqlParams); SqlHelper.AddVarcharPara(viewModel.ProjectType, "@ProjectType", sqlParams); SqlHelper.AddVarcharPara(viewModel.ContractType, "@ContractType", sqlParams); SqlHelper.AddDatePara(viewModel.UploadDateFrom, "@UploadDateFrom", sqlParams); SqlHelper.AddDatePara(viewModel.UploadDateTo, "@UploadDateTo", sqlParams); SqlHelper.AddVarcharPara(viewModel.SortColumn, "@SortColumn", sqlParams); SqlHelper.AddVarcharPara(viewModel.SortBy, "@SortBy", sqlParams); command.CommandType = CommandType.StoredProcedure; command.Parameters.AddRange(sqlParams.ToArray()); connection.Open(); SqlDataReader reader = null; try { reader = command.ExecuteReader(); while (reader.Read()) { var record = new ComplianceRiskIndicator { OrgCode = string.Format("{0}", reader["OrgCode"]), ESACode = string.Format("{0}", reader["ESACode"]), SiteCode = string.Format("{0}", reader["SiteCode"]), ProjectType = string.Format("{0}", reader["ProjectType"]), TotalCompliancePoint = AppHelper.ToDecimal(reader["TotalCompliancePoint"]), ComplianceIndicator = AppHelper.ToDecimal(reader["ComplianceIndicator"]), InProgressReviewCount = AppHelper.ToInt(reader["InProgressCount"]), CompletedReviewCount = AppHelper.ToInt(reader["CompletedCount"]), TotalReviewCount = AppHelper.ToInt(reader["TotalReviewsCount"]), TotalRecoveryAmount = AppHelper.ToDecimal(reader["TotalRecoveryAmount"]), ValidCount = AppHelper.ToInt(reader["OutcomeCodeVANCount"]), ValidAdminCount = AppHelper.ToInt(reader["OutcomeCodeVADCount"]), InvalidAdminCount = AppHelper.ToInt(reader["OutcomeCodeIADCount"]), InvalidRecovery = AppHelper.ToInt(reader["OutcomeCodeINRCount"]), InvalidNoRecovery = AppHelper.ToInt(reader["OutcomeCodeINNCount"]) }; results.Add(record); } } finally { if (reader != null) { reader.Close(); } } } } return(results); }
/// <summary> /// Gets the provider summary report. /// </summary> /// <param name="gridSettings">The grid settings.</param> /// <param name="viewModel">The view model.</param> /// <returns></returns> public List <ProviderSummary> GetProviderSummaryReport(GridSettings gridSettings, SearchCriteriaViewModel viewModel) { var results = new List <ProviderSummary>(); using (var connection = new SqlConnection(DbConnection)) { using (var command = new SqlCommand("PaReportProviderSummary", connection)) { var sqlParams = new List <SqlParameter>(); var paramReturnValue = new SqlParameter("@return_value", SqlDbType.Int) { Direction = ParameterDirection.ReturnValue }; sqlParams.Add(paramReturnValue); SqlHelper.AddIntPara(gridSettings.PageIndex, "@pageIndex", sqlParams); SqlHelper.AddIntPara(gridSettings.PageSize, "@pageSize", sqlParams); SqlHelper.AddVarcharPara(viewModel.OrgCode, "@OrgCode", sqlParams); SqlHelper.AddVarcharPara(viewModel.ESACode, "@ESACode", sqlParams); SqlHelper.AddVarcharPara(viewModel.SiteCode, "@SiteCode", sqlParams); SqlHelper.AddVarcharPara(viewModel.ProjectID, "@ProjectID", sqlParams); SqlHelper.AddVarcharPara(viewModel.ProjectType, "@ProjectType", sqlParams); SqlHelper.AddVarcharPara(viewModel.ContractType, "@ContractType", sqlParams); SqlHelper.AddDatePara(viewModel.UploadDateFrom, "@UploadDateFrom", sqlParams); SqlHelper.AddDatePara(viewModel.UploadDateTo, "@UploadDateTo", sqlParams); SqlHelper.AddVarcharPara(viewModel.SortColumn, "@SortColumn", sqlParams); SqlHelper.AddVarcharPara(viewModel.SortBy, "@SortBy", sqlParams); command.CommandType = CommandType.StoredProcedure; command.Parameters.AddRange(sqlParams.ToArray()); connection.Open(); SqlDataReader reader = null; try { reader = command.ExecuteReader(); while (reader.Read()) { var record = new ProviderSummary { OrgCode = string.Format("{0}", reader["OrgCode"]), ESACode = string.Format("{0}", reader["ESACode"]), SiteCode = string.Format("{0}", reader["SiteCode"]), //State = reader["State"] as string, RecoveryCount = AppHelper.ToInt(reader["NoOfRecoveries"]), CompletedReviewCount = AppHelper.ToInt(reader["NoOfCompletedReviews"]), TotalReviewCount = AppHelper.ToInt(reader["TotalReviewsCount"]), ValidCount = AppHelper.ToInt(reader["NoOfReviewVAN"]), ValidAdminCount = AppHelper.ToInt(reader["NoOfReviewVAD"]), InvalidAdminCount = AppHelper.ToInt(reader["NoOfReviewIAD"]), InvalidRecovery = AppHelper.ToInt(reader["NoOfReviewINR"]), InvalidNoRecovery = AppHelper.ToInt(reader["NoOfReviewINN"]) }; results.Add(record); } } finally { if (reader != null) { reader.Close(); } } } } return(results); }
/// <summary> /// Gets the site visit report. /// </summary> /// <param name="gridSettings">The grid settings.</param> /// <param name="viewModel">The view model.</param> /// <returns></returns> public List <SiteVisit> GetSiteVisitReport(GridSettings gridSettings, SearchCriteriaViewModel viewModel) { var results = new List <SiteVisit>(); using (var connection = new SqlConnection(DbConnection)) { using (var command = new SqlCommand("PaReportSiteVisits", connection)) { // this type of data need more than the default timeout 30 seconds. command.CommandTimeout = 120; // seconds var sqlParams = new List <SqlParameter>(); var paramReturnValue = new SqlParameter("@return_value", SqlDbType.Int) { Direction = ParameterDirection.ReturnValue }; sqlParams.Add(paramReturnValue); SqlHelper.AddIntPara(gridSettings.PageIndex, "@pageIndex", sqlParams); SqlHelper.AddIntPara(gridSettings.PageSize, "@pageSize", sqlParams); SqlHelper.AddVarcharPara(viewModel.OrgCode, "@OrgCode", sqlParams); SqlHelper.AddVarcharPara(viewModel.ESACode, "@ESACode", sqlParams); SqlHelper.AddVarcharPara(viewModel.SiteCode, "@SiteCode", sqlParams); SqlHelper.AddVarcharPara(viewModel.ProjectID, "@ProjectID", sqlParams); SqlHelper.AddVarcharPara(viewModel.ProjectType, "@ProjectType", sqlParams); SqlHelper.AddVarcharPara(viewModel.ContractType, "@ContractType", sqlParams); SqlHelper.AddDatePara(viewModel.UploadDateFrom, "@UploadDateFrom", sqlParams); SqlHelper.AddDatePara(viewModel.UploadDateTo, "@UploadDateTo", sqlParams); SqlHelper.AddVarcharPara(viewModel.SortColumn, "@SortColumn", sqlParams); SqlHelper.AddVarcharPara(viewModel.SortBy, "@SortBy", sqlParams); command.CommandType = CommandType.StoredProcedure; command.Parameters.AddRange(sqlParams.ToArray()); connection.Open(); SqlDataReader reader = null; try { reader = command.ExecuteReader(); while (reader.Read()) { var record = new SiteVisit { OrgCode = string.Format("{0}", reader["OrgCode"]), OrgName = string.Format("{0}", reader["OrgName"]), ESACode = string.Format("{0}", reader["ESACode"]), ESAName = string.Format("{0}", reader["ESAName"]), SiteCode = string.Format("{0}", reader["SiteCode"]), SiteName = string.Format("{0}", reader["SiteName"]), ProjectID = AppHelper.ToInt(reader["ProjectId"]), ProjectName = string.Format("{0}", reader["ProjectName"]), ClaimID = AppHelper.ToInt(reader["ClaimId"]), ClaimType = string.Format("{0}", reader["ClaimType"]), ClaimTypeDescription = string.Format("{0}", reader["ClaimTypeDescription"]), ClaimAmount = AppHelper.ToDecimal(reader["ClaimAmount"]), ClaimCreationDate = reader["ClaimCreationDate"] as DateTime? ?? default(DateTime), ContractType = string.Format("{0}", reader["ContractType"]), DaysOverdue = AppHelper.ToInt(reader["DaysOverdue"]), AssessmentAction = string.Format("{0}", reader["AssessmentAction"]), AssessmentOutcome = string.Format("{0}", reader["ReviewAssessmentCode"]), FinalOutcome = AppHelper.ToDecimal(reader["FinalOutcome"]), JobSeekerID = AppHelper.ToInt(reader["JobSeekerID"]), JobSeekerFirstName = string.Format("{0}", reader["JobSeekerGivenName"]), JobSeekerFamilyName = string.Format("{0}", reader["JobSeekerSurname"]), JobSeekerName = string.Format("{0} {1}", reader["JobSeekerGivenName"], reader["JobSeekerSurname"]), LastUpdateDate = reader["LastUpdateDate"] as DateTime? ?? default(DateTime), RecoveryReason = string.Format("{0}", reader["RecoveryReason"]), ReviewStatus = string.Format("{0}", reader["ReviewStatus"]), UploadDate = reader["UploadedDate"] as DateTime? ?? default(DateTime) }; results.Add(record); } } finally { if (reader != null) { reader.Close(); } } } } return(results); }
public async Task<ActionResult> Listing(SearchCriteriaViewModel criteria) { var zipCodesWithinDistance = new List<string>(); if (!string.IsNullOrEmpty(criteria.Zip) && criteria.MilesWithinZip.HasValue) { var codes = ZipCodeReader.Codes; var zip = 0; int.TryParse(criteria.Zip, out zip); var codesWithinDistance = codes.FindLessThanDistance(codes[zip], criteria.MilesWithinZip.Value); foreach (var zipCode in codesWithinDistance) { zipCodesWithinDistance.Add(zipCode.ZipCode.Code.ToString()); } } var keyword = (criteria.Keyword ?? string.Empty).Trim().ToLower(); var profileQuery = db.Profiles .Where(p => ( (criteria.Keyword == null || p.Blurb.ToLower().Contains(keyword)) || (criteria.Keyword == null || p.BusinessName.ToLower().Contains(keyword)) || (criteria.Keyword == null || p.SupervisorType.ToLower().Contains(keyword)) || (criteria.Keyword == null || p.Credentials.ToLower().Contains(keyword)) || (criteria.Keyword == null || p.BusinessDescription.ToLower().Contains(keyword)) || (criteria.Keyword == null || p.Address1.ToLower().Contains(keyword)) || (criteria.Keyword == null || p.Address2.ToLower().Contains(keyword)) || (criteria.Keyword == null || p.City.ToLower().Contains(keyword)) || (criteria.Keyword == null || p.State.ToLower().Contains(keyword)) || (criteria.Keyword == null || p.Zip.ToLower().Contains(keyword)) || (criteria.Keyword == null || p.School.ToLower().Contains(keyword)) || (criteria.Keyword == null || p.Categories.ToLower().Contains(keyword)) || (criteria.Keyword == null || p.Services.ToLower().Contains(keyword)) || (criteria.Keyword == null || p.AgeGroups.ToLower().Contains(keyword)) || (criteria.Keyword == null || p.BusinessUrl.ToLower().Contains(keyword)) ) && (criteria.SupervisorType == null || criteria.SupervisorType.Equals(p.SupervisorType)) && (criteria.Category == null || p.Categories.Contains(criteria.Category)) && (criteria.Service == null || p.Services.Contains(criteria.Service)) && (criteria.AgeGroup == null || p.AgeGroups.Contains(criteria.AgeGroup)) && (criteria.State == null || p.State.Equals(criteria.State)) && (zipCodesWithinDistance.Count() == 0 || zipCodesWithinDistance.Contains(p.Zip)) ); var take = criteria.Top ?? configuration.DefaultPageSize; var skip = take * (criteria.Page ?? 0) - take; var profileQueryResults = profileQuery .SortBy(criteria.SortBy, criteria.IsSortAscending) .Skip(skip) .Take(take) .Select(p => new SearchResultViewModel { ProfileId = p.ProfileId, PicturePath = p.PicturePath, BusinessName = p.BusinessName, SupervisorType = p.SupervisorType, Categories = p.Categories, Services = p.Services, AgeGroups = p.AgeGroups, Credentials = p.Credentials, Blurb = p.Blurb, Phone = p.Phone, PhoneExt = p.PhoneExt, City = p.City, State = p.State, Zip = p.Zip }); return View(new Listing { Criteria = criteria, SearchResults = profileQueryResults.ToList(), TotalRecords = await profileQuery.CountAsync(), }); }