/// <summary>
/// Gets all by project identifier paged.
/// </summary>
/// <param name="projectId">The project identifier.</param>
/// <param name="gridSettings">The grid settings.</param>
/// <returns></returns>
public List <Review> GetAllByProjectIdPaged(int projectId, GridSettings gridSettings)
{
if (string.IsNullOrEmpty(gridSettings.SortColumn))
{
gridSettings.SortColumn = "ReviewId";
}
const string sprocName = "PaReviewsGetAllByProject";
var reviewList = new List <Review>();
using (var connection = new SqlConnection(DbConnection))
{
using (var command = new SqlCommand(sprocName, connection))
{
var sqlParams = new List <SqlParameter>();
var paramReturnValue = new SqlParameter("@return_value", SqlDbType.Int)
{
Direction = ParameterDirection.ReturnValue
};
sqlParams.Add(paramReturnValue);
SqlHelper.AddIntPara(projectId, "@ProjectId", sqlParams);
SqlHelper.AddVarcharPara(gridSettings.SortColumn, "@sortColumnName", sqlParams);
SqlHelper.AddVarcharPara(gridSettings.SortOrder, "@sortOrder", sqlParams);
if (gridSettings.PageSize > 0)
{
SqlHelper.AddIntPara(gridSettings.PageSize, "@pageSize", sqlParams);
}
if (gridSettings.PageIndex > 0)
{
SqlHelper.AddIntPara(gridSettings.PageIndex, "@pageIndex", sqlParams);
}
if (gridSettings.IsSearch && gridSettings.Where != null)
{
foreach (var rule in gridSettings.Where.rules)
{
// convert rule into a parameter
if (rule.field.IndexOf("Date", StringComparison.Ordinal) > -1)
{
SqlHelper.AddDatePara(DateTime.Parse(rule.data), "@" + rule.field, sqlParams);
}
else
{
SqlHelper.AddVarcharPara(rule.data, "@" + rule.field, sqlParams);
}
}
}
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(sqlParams.ToArray());
connection.Open();
LoadList(reviewList, command, sprocName);
}
}
return(reviewList);
}
public static GridSettings GridSettingsFromReviewCriteria(ReviewListCriteriaViewModel criteria)
{
var gs = new GridSettings
{
IsSearch = true,
PageIndex = 1,
PageSize = 999999999,
SortColumn = "ReviewId",
SortOrder = "asc",
Where = new Filter()
};
var ruleList = new List <Rule>();
AddRuleFor(criteria.ReviewId, "ReviewId", ruleList);
AddRuleFor(criteria.UploadId, "UploadId", ruleList);
AddRuleFor(criteria.ClaimId, "ClaimId", ruleList);
AddRuleFor(criteria.JobseekerId, "JobseekerId", ruleList);
AddRuleFor(criteria.ActivityId, "ActivityId", ruleList);
AddRuleFor(criteria.AssessmentCode, "AssessmentCode", ruleList);
AddRuleFor(criteria.OutcomeCode, "OutcomeCode", ruleList);
AddRuleFor(criteria.AssessmentAction, "AssessmentAction", ruleList);
AddRuleFor(criteria.RecoveryReason, "RecoveryReason", ruleList);
AddRuleFor(criteria.SiteCode, "SiteCode", ruleList);
AddRuleFor(criteria.EsaCode, "EsaCode", ruleList);
AddRuleFor(criteria.StateCode, "StateCode", ruleList);
AddRuleFor(criteria.OrgCode, "OrgCode", ruleList);
gs.Where.groupOp = "AND";
gs.Where.rules = ruleList.ToArray();
return(gs);
}
/// <summary>
/// Gets all the reviews that match the grid criteria.
/// </summary>
/// <param name="gridSettings">The grid settings.</param>
/// <returns>
/// list of review objects
/// </returns>
public List <Review> GetAll(GridSettings gridSettings)
{
// Not required
var list = new List <Review>();
return(list);
}
public static string SearchCriteria(GridSettings gridSettings)
{
var criteriaString = string.Format("{0}:{1}-{2}", gridSettings.PageIndex, gridSettings.PageSize,
CriteriaOut(gridSettings.Where));
return(criteriaString);
}
public void TestProjectCount2()
{
var gridSettings = new MvcJqGrid.GridSettings();
var sut = new PatService();
var projectCount = sut.CountProjects(gridSettings);
Assert.IsTrue(projectCount > 0);
}
public static GridSettings DefaultGridSettings()
{
var gs = new GridSettings {
IsSearch = false, PageIndex = 1, PageSize = 9999999, SortOrder = "asc"
};
return(gs);
}
/// <summary>
/// Gets all the reviews for a particular upload.
/// </summary>
/// <param name="uploadId">The upload identifier.</param>
/// <returns>
/// list of review objects
/// </returns>
public List <Review> GetAllByUploadId(int uploadId)
{
var gs = new GridSettings {
SortColumn = "ReviewId", SortOrder = "ASC", IsSearch = false
};
return(GetAllByUploadIdPaged(uploadId, gs));
}
/// <summary>
/// Counts the site visit report.
/// </summary>
/// <param name="gridSettings">The grid settings.</param>
/// <param name="viewModel">The view model.</param>
/// <returns></returns>
public int CountSiteVisitReport(GridSettings gridSettings, SearchCriteriaViewModel viewModel)
{
var count = 0;
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())
{
count++;
}
}
finally
{
if (reader != null)
{
reader.Close();
}
}
}
}
return(count);
}
/// <summary>
/// To get the Provider Summary Report data.
/// </summary>
/// <param name="gridSettings">The grid settings.</param>
/// <param name="viewModel">The view model.</param>
/// <returns></returns>
public ActionResult ProviderSummaryGetData(GridSettings gridSettings, SearchCriteriaViewModel viewModel)
{
if (ModelState.IsValid)
{
if (string.IsNullOrEmpty(gridSettings.SortColumn))
{
gridSettings.SortOrder = "DESC";
}
try
{
var data = PatService.GetProviderSummaryReport(gridSettings, GetValues(viewModel));
if (data != null)
{
var totalRecords = viewModel.TotalRecords; // PatService.CountProviderSummaryReport(GetValues(viewModel));
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.State,
string.Format("{0}", e.TotalReviewCount),
string.Format("{0}", e.CompletedReviewCount),
string.Format("{0}", e.RecoveryCount),
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 question answers by upload identifier and grid settings
/// </summary>
/// <param name="gridSettings">The grid settings.</param>
/// <param name="uploadId">The upload identifier.</param>
/// <returns></returns>
public List <QuestionAnswer> GetQuestionAnswersByUploadId(GridSettings gridSettings, int uploadId)
{
var questionAnswers = new List <QuestionAnswer>();
using (var connection = new SqlConnection(DbConnection))
{
using (var command = new SqlCommand("PaQuestionAnswerGetBy", 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.AddIntPara(uploadId, "@UploadId", sqlParams);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(sqlParams.ToArray());
connection.Open();
SqlDataReader reader = null;
try
{
reader = command.ExecuteReader();
while (reader.Read())
{
questionAnswers.Add(new QuestionAnswer
{
QuestionId = (int)reader["QuestionId"],
QuestionHeadingCode = string.Format("{0}", reader["QuestionHeadingCode"]),
QuestionCode = string.Format("{0}", reader["QuestionCode"]),
QuestionText = string.Format("{0}", reader["QuestionText"]),
AnswerText = string.Format("{0}", reader["AnswerText"])
});
}
}
finally
{
if (reader != null)
{
reader.Close();
}
}
}
}
return(questionAnswers);
}
/// <summary>
/// To get data of Projects Type report.
/// </summary>
/// <param name="gridSettings">The grid settings.</param>
/// <param name="projectTypeBy">The project type by.</param>
/// <param name="viewModel">The view model.</param>
/// <returns></returns>
public ActionResult ProjectTypeGetData(GridSettings gridSettings, string projectTypeBy, SearchCriteriaViewModel viewModel)
{
if (ModelState.IsValid)
{
if (string.IsNullOrEmpty(gridSettings.SortColumn))
{
gridSettings.SortOrder = "DESC";
}
try
{
var projectType = PatService.GetProjectTypeReport(GetValues(viewModel));
var data = PatService.GetProjectTypeBy(projectTypeBy, projectType);
if (data != null)
{
var totalRecords = data.Count();
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.Type,
e.Code,
e.Description,
string.Format("{0}", e.ProjectCount),
string.Format("{0}", e.ReviewCount)
}
}
).ToArray()
};
return(Json(jsonData, JsonRequestBehavior.AllowGet));
}
}
catch (Exception ex)
{
Elmah.ErrorLog.GetDefault(null).Log(new Elmah.Error(ex));
throw;
}
}
return(null);
}
/// <summary>
/// Gets all by project identifier.
/// </summary>
/// <param name="projectId">The project identifier.</param>
/// <returns></returns>
public List <Upload> GetAllByProjectId(int projectId)
{
var gs = new MvcJqGrid.GridSettings
{
IsSearch = false,
SortColumn = "ProjectId",
SortOrder = "ASC",
PageIndex = 1,
PageSize = 999999
};
return(GetAllByProjectId(projectId, gs));
}
/// <summary>
/// To get all bulletin data based on its type and admin privilage.
/// </summary>
/// <param name="bulletinType">Type of the bulletin.</param>
/// <param name="isAdmin">if set to <c>true</c> [is admin].</param>
/// <returns>
/// a list of bulletin
/// </returns>
public IQueryable <Bulletin> GetAll(string bulletinType, bool isAdmin)
{
var gridSettings = new MvcJqGrid.GridSettings
{
IsSearch = false,
PageSize = 99999999,
PageIndex = 1,
SortColumn = "EndDate",
SortOrder = DataConstants.Descending
};
var results = GetAll(gridSettings, bulletinType, isAdmin);
return(results.AsQueryable());
}
/// <summary>
/// Gets all the projects.
/// </summary>
/// <returns>
/// a list of projects
/// </returns>
public IQueryable <Project> GetAll(DateTime uploadFrom, DateTime uploadTo)
{
var gridSettings = new GridSettings
{
IsSearch = false,
PageSize = 99999999,
PageIndex = 1,
SortColumn = "ProjectId",
SortOrder = "asc"
};
var results = GetAll(gridSettings, uploadFrom, uploadTo);
return(results.AsQueryable());
}
private IEnumerable <Project> FindProjectsByName(string projectName)
{
var myGs = new GridSettings
{
Where = ProjectNameRule(projectName),
PageIndex = 1,
PageSize = 99999999,
IsSearch = true
};
var projectList = GetAll(myGs, new DateTime(1, 1, 1), new DateTime(1, 1, 1));
return(projectList);
}
/// <summary>
/// Gets all by project identifier.
/// </summary>
/// <param name="projectId">The project identifier.</param>
/// <param name="gridSettings">The grid settings.</param>
/// <returns></returns>
public List <Upload> GetAllByProjectId(int projectId, MvcJqGrid.GridSettings gridSettings)
{
if (gridSettings.SortColumn == null)
{
gridSettings.SortColumn = "DateUploaded";
}
if (gridSettings.SortColumn.Equals("UploadDateShort"))
{
gridSettings.SortColumn = "DateUploaded";
}
var uploadList = new List <Upload>();
using (var connection = new SqlConnection(DbConnection))
{
using (var command = new SqlCommand("PaUploadGetAllByProject", connection))
{
var sqlParams = new List <SqlParameter>();
var paramReturnValue = new SqlParameter("@return_value", SqlDbType.Int)
{
Direction = ParameterDirection.ReturnValue
};
sqlParams.Add(paramReturnValue);
SqlHelper.AddIntPara(projectId, "@ProjectId", sqlParams);
SqlHelper.AddVarcharPara(gridSettings.SortColumn, "@sortColumnName", sqlParams);
SqlHelper.AddVarcharPara(gridSettings.SortOrder, "@sortOrder", sqlParams);
SqlHelper.AddIntPara(gridSettings.PageSize, "@pageSize", sqlParams);
SqlHelper.AddIntPara(gridSettings.PageIndex, "@startingAt", sqlParams);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(sqlParams.ToArray());
connection.Open();
LoadList(uploadList, command);
}
}
return(uploadList);
}
public ActionResult Details(int id, string bulletinType)
{
ViewData["BulletinType"] = bulletinType;
ViewData["SelectedBulletinID"] = id;
// this is a setting to retrieve all records
var gridSettings = new MvcJqGrid.GridSettings
{
IsSearch = false,
PageSize = 99999999,
PageIndex = 1,
SortColumn = "EndDate",
SortOrder = DataConstants.Descending
};
var model = PatService.GetBulletins(gridSettings, bulletinType, CanEdit);
// for each bulletin, populate the project name
model.ForEach(b =>
{
if (b.ProjectId > 0)
{
var project = PatService.GetProject(b.ProjectId);
if (project != null && !string.IsNullOrWhiteSpace(project.ProjectName))
{
b.ProjectField = string.Format("{0} - {1}", b.ProjectId, project.ProjectName);
}
else
{
b.ProjectField = string.Format("{0} - {1}", b.ProjectId, "Project name is `empty`");
}
}
else
{
// bulletin does not have related project
b.ProjectField = string.Empty;
}
});
return(View(model));
}
public void TestProjectGetByProjectName()
{
var sut = new PatService();
var gridSettings = new MvcJqGrid.GridSettings {
IsSearch = true, PageSize = 99999999, PageIndex = 1, SortColumn = "ProjectId"
};
var rule1 = new MvcJqGrid.Rule {
field = "ProjectName", op = DataConstants.SqlOperationContains, data = "Steve"
};
var ruleArray = new MvcJqGrid.Rule[1];
ruleArray[0] = rule1;
var filter = new MvcJqGrid.Filter {
rules = ruleArray
};
gridSettings.Where = filter;
var projectList = sut.GetProjects(gridSettings);
Assert.IsTrue(projectList.Any());
}
/// <summary>
/// To Geth the Site Visit Report
/// </summary>
/// <param name="gridSettings">The grid settings.</param>
/// <param name="viewModel">The view model.</param>
/// <returns></returns>
public ActionResult SiteVisitGetData(GridSettings gridSettings, SearchCriteriaViewModel viewModel)
{
if (ModelState.IsValid)
{
if (string.IsNullOrEmpty(gridSettings.SortColumn))
{
gridSettings.SortOrder = "DESC";
}
try
{
var data = PatService.GetSiteVisitReport(gridSettings, GetValues(viewModel));
if (data != null)
{
var totalRecords = viewModel.TotalRecords; // PatService.CountSiteVisitReport(GetValues(viewModel));
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.OrgName,
e.ESACode,
e.ESAName,
e.SiteCode,
e.SiteName,
string.Format("{0}", e.ProjectID),
e.ProjectName,
string.Format("{0}", e.JobSeekerID),
e.JobSeekerFirstName,
e.JobSeekerFamilyName,
string.Format("{0}", e.ClaimID),
e.ClaimType,
e.ClaimTypeDescription,
string.Format("{0}", e.ClaimAmount),
string.Format("{0}", e.ClaimCreationDate.ToShortDateString()),
e.ContractType,
string.Format("{0}", e.DaysOverdue),
e.AssessmentAction,
e.AssessmentOutcome,
string.Format("{0}", e.FinalOutcome),
string.Format("{0}", e.LastUpdateDate.ToShortDateString()),
e.RecoveryReason,
e.ReviewStatus,
string.Format("{0}", e.UploadDate.ToShortDateString())
}
}
).ToArray()
};
return(Json(jsonData, JsonRequestBehavior.AllowGet));
}
}
catch (Exception ex)
{
Elmah.ErrorLog.GetDefault(null).Log(new Elmah.Error(ex));
throw;
}
}
return(null);
}
/// <summary>
/// Counts the bulletin by its grid settings, bulletin type and admin privilage
/// </summary>
/// <param name="gridSettings">The grid settings.</param>
/// <param name="bulletinType">Type of the bulletin.</param>
/// <param name="isAdmin">if set to <c>true</c> [is admin].</param>
/// <returns>
/// number of bulletin matching those criterias
/// </returns>
public int Count(MvcJqGrid.GridSettings gridSettings, string bulletinType, bool isAdmin)
{
return(GetAll(gridSettings, bulletinType, isAdmin).Count());
}
/// <summary>
/// Gets all the projects mathing the grid criteria.
/// </summary>
/// <param name="gridSettings">The grid settings.</param>
/// <param name="uploadFrom"></param>
/// <param name="uploadTo"></param>
/// <returns>
/// a list of projects
/// </returns>
public List <Project> GetAll(GridSettings gridSettings, DateTime uploadFrom, DateTime uploadTo)
{
if (string.IsNullOrEmpty(gridSettings.SortColumn))
{
gridSettings.SortColumn = "ProjectId";
}
if (string.IsNullOrEmpty(gridSettings.SortOrder))
{
gridSettings.SortOrder = "ASC";
}
var projectList = new List <Project>();
using (var connection = new SqlConnection(DbConnection))
{
using (var command = new SqlCommand("PaProjectGetAll", connection))
{
var sqlParams = new List <SqlParameter>();
var paramReturnValue = new SqlParameter("@return_value", SqlDbType.Int)
{
Direction = ParameterDirection.ReturnValue
};
sqlParams.Add(paramReturnValue);
SqlHelper.AddVarcharPara(gridSettings.SortColumn, "@sortColumnName", sqlParams);
SqlHelper.AddVarcharPara(gridSettings.SortOrder, "@sortOrder", sqlParams);
SqlHelper.AddIntPara(gridSettings.PageIndex, "@pageIndex", sqlParams);
SqlHelper.AddIntPara(gridSettings.PageSize, "@pageSize", sqlParams);
if (uploadFrom != new DateTime(1, 1, 1))
{
SqlHelper.AddDatePara(uploadFrom, "@UploadDateFrom", sqlParams);
}
if (uploadTo != new DateTime(1, 1, 1))
{
SqlHelper.AddDatePara(uploadTo, "@UploadDateTo", sqlParams);
}
if (gridSettings.IsSearch && gridSettings.Where != null)
{
foreach (var rule in gridSettings.Where.rules)
{
// convert rule into a parameter
if (rule.field.IndexOf("Date", StringComparison.Ordinal) > -1)
{
DateTime theDate;
var isValid = AppHelper.ToDbDateTime(rule.data, out theDate);
if (isValid)
{
SqlHelper.AddDatePara(theDate, "@" + rule.field, sqlParams);
}
else
{
return(projectList);
}
}
else
{
if (rule.field.Equals("ProjectId"))
{
var id = Regex.Replace(rule.data, @"[^\d]", ""); // make sure that user hasnt accidentally typed a non numeric
int projectId;
int.TryParse(id, out projectId);
SqlHelper.AddIntPara(projectId, "@" + rule.field, sqlParams);
}
else
{
SqlHelper.AddVarcharPara(rule.data, "@" + rule.field, sqlParams);
var opValue = rule.op.Equals("eq") ? 1 : 0;
SqlHelper.AddIntPara(opValue, "@" + rule.field + "Op", sqlParams);
}
}
}
}
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(sqlParams.ToArray());
connection.Open();
LoadProjectList(projectList, command);
}
}
return(projectList);
}
/// <summary>
/// To get all bulletin data based on its type and admin privilage and grid setting
/// </summary>
/// <param name="gridSettings">The grid settings.</param>
/// <param name="bulletinType">Type of the bulletin.</param>
/// <param name="isAdmin">if set to <c>true</c> [is admin].</param>
/// <returns>
/// a list of bulletin
/// </returns>
public List <Bulletin> GetAll(MvcJqGrid.GridSettings gridSettings, string bulletinType, bool isAdmin)
{
if (string.IsNullOrEmpty(gridSettings.SortColumn))
{
gridSettings.SortColumn = "EndDate";
gridSettings.SortOrder = DataConstants.Descending;
}
if (string.IsNullOrEmpty(gridSettings.SortOrder))
{
gridSettings.SortOrder = DataConstants.Descending;
}
var bulletinList = new List <Bulletin>();
using (var connection = new SqlConnection(DbConnection))
{
using (var command = new SqlCommand("PaBulletinGetAll", connection))
{
var sqlParams = new List <SqlParameter>();
var paramReturnValue = new SqlParameter("@return_value", SqlDbType.Int)
{
Direction = ParameterDirection.ReturnValue
};
sqlParams.Add(paramReturnValue);
SqlHelper.AddVarcharPara(gridSettings.SortColumn, "@sortColumnName", sqlParams);
SqlHelper.AddVarcharPara(gridSettings.SortOrder, "@sortOrder", sqlParams);
SqlHelper.AddIntPara(gridSettings.PageIndex, "@pageIndex", sqlParams);
SqlHelper.AddIntPara(gridSettings.PageSize, "@pageSize", sqlParams);
SqlHelper.AddVarcharPara(bulletinType, "@bulletinType ", sqlParams);
SqlHelper.AddBitPara(isAdmin, "@isAdmin", sqlParams);
if (gridSettings.IsSearch && gridSettings.Where != null)
{
foreach (var rule in gridSettings.Where.rules)
{
// convert rule into a parameter
if (rule.field.IndexOf("Date", StringComparison.Ordinal) > -1)
{
DateTime theDate;
var isValid = AppHelper.ToDbDateTime(rule.data, out theDate);
if (isValid)
{
SqlHelper.AddDatePara(theDate, "@" + rule.field, sqlParams);
}
else
{
return(bulletinList);
}
}
else
{
SqlHelper.AddVarcharPara(rule.data, "@" + rule.field, sqlParams);
}
}
}
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(sqlParams.ToArray());
connection.Open();
LoadBulletinList(bulletinList, command);
}
}
return(bulletinList);
}
/// <summary>
/// Counts the project attachment by its project id and grid settings.
/// </summary>
/// <param name="gridSettings">The grid settings.</param>
/// <param name="projectId">The project identifier.</param>
/// <returns></returns>
public int Count(GridSettings gridSettings, int projectId)
{
return(GetAll(gridSettings, projectId).Count());
}
/// <summary>
/// Gets all project attachment by its grid setting and project id
/// </summary>
/// <param name="gridSettings">The grid settings.</param>
/// <param name="projectId">The project identifier.</param>
/// <returns></returns>
public List <ProjectAttachment> GetAll(GridSettings gridSettings, int projectId)
{
if (string.IsNullOrEmpty(gridSettings.SortColumn))
{
gridSettings.SortColumn = "Id";
gridSettings.SortOrder = DataConstants.Descending;
}
if (string.IsNullOrEmpty(gridSettings.SortOrder))
{
gridSettings.SortOrder = DataConstants.Descending;
}
var projectAttachmentList = new List <ProjectAttachment>();
using (var connection = new SqlConnection(DbConnection))
{
using (var command = new SqlCommand("PaProjectAttachmentGetAllByProject", connection))
{
var sqlParams = new List <SqlParameter>();
var paramReturnValue = new SqlParameter("@return_value", SqlDbType.Int)
{
Direction = ParameterDirection.ReturnValue
};
sqlParams.Add(paramReturnValue);
//also get the count back
var totalRecord = new SqlParameter("@TotalRecs", SqlDbType.Int)
{
Direction = ParameterDirection.InputOutput,
Value = 0
};
sqlParams.Add(totalRecord);
SqlHelper.AddIntPara(projectId, "@ProjectId", sqlParams);
SqlHelper.AddVarcharPara(gridSettings.SortColumn, "@sortColumnName", sqlParams);
SqlHelper.AddVarcharPara(gridSettings.SortOrder, "@sortOrder", sqlParams);
SqlHelper.AddIntPara(gridSettings.PageIndex, "@pageIndex", sqlParams);
SqlHelper.AddIntPara(gridSettings.PageSize, "@pageSize", sqlParams);
if (gridSettings.IsSearch && gridSettings.Where != null)
{
foreach (var rule in gridSettings.Where.rules)
{
// convert rule into a parameter
SqlHelper.AddVarcharPara(rule.data, "@" + rule.field, sqlParams);
}
}
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(sqlParams.ToArray());
connection.Open();
LoadProjectAttachmentList(projectAttachmentList, command);
/*
* if (((Int32)command.Parameters["@return_value"].Value) == 0)
* {
* var totalCount = (int)totalRecord.Value;
* }
*/
}
}
return(projectAttachmentList);
}
/// <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);
}
/// <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 review questionnaire data by upload id and grid settings
/// </summary>
/// <param name="gridSettings">The grid settings.</param>
/// <param name="uploadId">The upload identifier.</param>
/// <returns></returns>
public List <ReviewQuestionnaire> GetReviewQuestionnaireData(GridSettings gridSettings, int uploadId)
{
var reviewQuestions = new List <ReviewQuestionnaire>();
using (var connection = new SqlConnection(DbConnection))
{
using (var command = new SqlCommand("PaReviewQuestionnaireGetBy", 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.AddIntPara(uploadId, "@UploadId", 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())
{
if (reader.FieldCount > 10)
{
// 1st result set which is the review question
reviewQuestions.Add(new ReviewQuestionnaire
{
ProjectID = (int)reader["ProjectId"],
ProjectName = string.Format("{0}", reader["ProjectName"]),
UploadID = (int)reader["UploadId"],
UploadName = string.Format("{0}", reader["ProjectName"]),
QuestionnaireID = (int)reader["QuestionnaireID"],
ReviewID = (int)reader["ReviewId"],
ReferenceID = (long)reader["ReferenceId"],
UserID = string.Format("{0}", reader["UserId"]),
QuestionnaireCode = string.Format("{0}", reader["QuestionnaireCode"]),
AssessmentOutcomeCode = string.Format("{0}", reader["AssessmentOutcomeCode"]),
AssessmentOutcomeDescription = string.Format("{0}", reader["AssessmentOutcomeDescription"]),
RecoveryReasonCode = string.Format("{0}", reader["RecoveryReasonCode"]),
RecoveryReasonDescription = string.Format("{0}", reader["RecoveryReasonDescription"]),
RecoveryActionCode = string.Format("{0}", reader["RecoveryActionCode"]),
RecoveryActionDescription = string.Format("{0}", reader["RecoveryActionDescription"]),
FinalOutcomeCode = string.Format("{0}", reader["FinalOutcomeCode"]),
FinalOutcomeDescription = string.Format("{0}", reader["FinalOutcomeDescription"]),
Date = reader["Date"] as DateTime? ?? default(DateTime),
CreatedBy = string.Format("{0}", reader["CreatedBy"]),
CreatedOn = reader["CreatedOn"] as DateTime? ?? default(DateTime),
UpdatedBy = string.Format("{0}", reader["UpdatedBy"]),
UpdatedOn = reader["ProjectName"] as DateTime? ?? default(DateTime)
});
}
else
{
// 2nd result set which is the extra columns of the row from 1st result set
var questionnairId = (int)reader["QuestionnaireId"];
var reviewQuestionsnaire = reviewQuestions.First(qa => qa.QuestionnaireID == questionnairId);
if (reviewQuestionsnaire != null)
{
reviewQuestionsnaire.QuestionAnswers.Add(new QuestionAnswer
{
QuestionId = (int)reader["QuestionId"],
QuestionCode = string.Format("{0}", reader["QuestionCode"]),
AnswerText = string.Format("{0}", reader["AnswerText"]),
QuestionText = string.Format("{0}", reader["QuestionText"])
}
);
}
}
}
reader.NextResult();
}
}
finally
{
if (reader != null)
{
reader.Close();
}
}
}
}
return(reviewQuestions);
}
/// <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>
/// 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 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);
}
