/// <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);
}
/// <summary>
/// Updates the specified entity.
/// </summary>
/// <param name="entity">The entity.</param>
public void Update(ProjectAttachment entity)
{
var projectAttachment = entity;
using (var connection = new SqlConnection(DbConnection))
{
using (var command = new SqlCommand("PaProjectAttachementUpdate", connection))
{
var sqlParams = new List <SqlParameter>();
SqlHelper.AddReturnPara("@return_value", sqlParams);
SqlHelper.AddIntPara(projectAttachment.Id, "@Id", sqlParams);
SqlHelper.AddIntPara(projectAttachment.ProjectId, "@ProjectId", sqlParams);
SqlHelper.AddVarcharPara(projectAttachment.DocumentName, "@DocumentName", sqlParams);
SqlHelper.AddVarcharPara(projectAttachment.Description, "@Description", sqlParams);
SqlHelper.AddVarcharPara(projectAttachment.Url, "@Url", sqlParams);
SqlHelper.AddVarcharPara(projectAttachment.UpdatedBy, "@UpdatedBy", sqlParams);
SqlHelper.AddDatePara(DateTime.Now, "@UpdatedOn", sqlParams);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(sqlParams.ToArray());
connection.Open();
command.ExecuteNonQuery();
}
}
}
/// <summary>
/// Method for cleaning out the old records in the audit trail
/// </summary>
/// <param name="keepDate">The keep date.</param>
public void DeletePriorTo(DateTime keepDate)
{
if (keepDate.Equals(new DateTime(1, 1, 1)))
{
keepDate = DateTime.Now.Subtract(new TimeSpan(30, 0, 0, 0));
}
using (var connection = new SqlConnection(DbConnection))
{
using (var command = new SqlCommand("PaUserActivityDelete", connection))
{
var sqlParams = new List <SqlParameter>();
var paramReturnValue = new SqlParameter("@return_value", SqlDbType.Int)
{
Direction = ParameterDirection.ReturnValue
};
sqlParams.Add(paramReturnValue);
SqlHelper.AddDatePara(keepDate, "@keepFrom", sqlParams);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(sqlParams.ToArray());
connection.Open();
command.ExecuteNonQuery();
}
}
}
/// <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>
/// Adds the specified Project.
/// </summary>
/// <param name="entity">The entity.</param>
public void Add(Project entity)
{
var project = entity;
Debug.Assert(project != null, "project != null");
using (var connection = new SqlConnection(DbConnection))
{
using (var command = new SqlCommand("PaProjectInsert", connection))
{
var sqlParams = new List <SqlParameter>();
SqlHelper.AddReturnPara("@return_value", sqlParams);
if (project.ProjectId > 0)
{
SqlHelper.AddIntPara(project.ProjectId, "@ProjectId", sqlParams);
}
SqlHelper.AddVarcharPara(project.ProjectType, "@ProjectType", sqlParams);
SqlHelper.AddVarcharPara(project.OrgCode, "@OrgCode", sqlParams);
SqlHelper.AddVarcharPara(project.ProjectName, "@ProjectName", sqlParams);
SqlHelper.AddVarcharPara(project.Coordinator, "@Coordinator", sqlParams);
SqlHelper.AddVarcharPara(project.Comments, "@Comments", sqlParams);
SqlHelper.AddDatePara(DateTime.Now, "@CreatedOn", sqlParams);
SqlHelper.AddVarcharPara(project.CreatedBy, "@CreatedBy", sqlParams);
SqlHelper.AddBitPara(project.Resource_NO, "@Resource_NO", sqlParams);
SqlHelper.AddBitPara(project.Resource_NSW_ACT, "@Resource_NSW_ACT", sqlParams);
SqlHelper.AddBitPara(project.Resource_QLD, "@Resource_QLD", sqlParams);
SqlHelper.AddBitPara(project.Resource_NT, "@Resource_NT", sqlParams);
SqlHelper.AddBitPara(project.Resource_WA, "@Resource_WA", sqlParams);
SqlHelper.AddBitPara(project.Resource_SA, "@Resource_SA", sqlParams);
SqlHelper.AddBitPara(project.Resource_TAS, "@Resource_TAS", sqlParams);
SqlHelper.AddBitPara(project.Resource_VIC, "@Resource_VIC", sqlParams);
// Output parameters
var paramId = new SqlParameter("@ProjectId", SqlDbType.Int)
{
Direction = ParameterDirection.InputOutput,
Value = 0
};
sqlParams.Add(paramId);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(sqlParams.ToArray());
connection.Open();
command.ExecuteNonQuery();
if (((Int32)command.Parameters["@return_value"].Value) != 0)
{
return;
}
project.ProjectId = (int)paramId.Value;
}
}
}
/// <summary>
/// Adds the specified bulletin.
/// </summary>
/// <param name="entity">The bulletin.</param>
public void Add(Bulletin entity)
{
var bulletin = entity;
Debug.Assert(bulletin != null, "bulletin != null");
using (var connection = new SqlConnection(DbConnection))
{
using (var command = new SqlCommand("PaBulletinInsert", connection))
{
var sqlParams = new List <SqlParameter>();
SqlHelper.AddReturnPara("@return_value", sqlParams);
if (bulletin.ProjectId > 0)
{
SqlHelper.AddIntPara(bulletin.ProjectId, "@ProjectId", sqlParams);
}
SqlHelper.AddVarcharPara(bulletin.BulletinTitle, "@Title", sqlParams);
SqlHelper.AddVarcharPara(bulletin.Description, "@Description", sqlParams);
SqlHelper.AddVarcharPara(bulletin.BulletinType, "@BulletinType", sqlParams);
if (bulletin.StartDate != new DateTime(1, 1, 1))
{
SqlHelper.AddDatePara(bulletin.StartDate, "@StartDate", sqlParams);
}
if (bulletin.EndDate != new DateTime(1, 1, 1))
{
SqlHelper.AddDatePara(bulletin.EndDate, "@EndDate", sqlParams);
}
SqlHelper.AddDatePara(DateTime.Now, "@CreatedOn", sqlParams);
SqlHelper.AddVarcharPara(bulletin.CreatedBy, "@CreatedBy", sqlParams);
// Output parameters
var paramBulletinId = new SqlParameter("@BulletinId", SqlDbType.Int)
{
Direction = ParameterDirection.InputOutput,
Value = 0
};
sqlParams.Add(paramBulletinId);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(sqlParams.ToArray());
connection.Open();
command.ExecuteNonQuery();
if (((Int32)command.Parameters["@return_value"].Value) != 0)
{
return;
}
bulletin.BulletinId = (int)paramBulletinId.Value;
}
}
}
/// <summary>
/// Inserts the specified entity.
/// </summary>
/// <param name="entity">The entity.</param>
/// <param name="fileData">The file data.</param>
/// <exception cref="System.ArgumentNullException">fileData</exception>
public void Insert(ProjectAttachment entity, byte[] fileData)
{
if (fileData == null)
{
throw new ArgumentNullException("fileData");
}
using (var connection = new SqlConnection(DbConnection))
{
using (var command = new SqlCommand("PaProjectAttachementInsert", connection))
{
var sqlParams = new List <SqlParameter>();
var paramReturnValue = new SqlParameter("@return_value", SqlDbType.Int)
{
Direction = ParameterDirection.ReturnValue
};
sqlParams.Add(paramReturnValue);
var documentId = new SqlParameter("@Id", SqlDbType.Int)
{
Direction = ParameterDirection.InputOutput,
Value = 0
};
sqlParams.Add(documentId);
SqlHelper.AddIntPara(entity.ProjectId, "@ProjectId", sqlParams);
SqlHelper.AddVarcharPara(entity.DocumentName, "@DocumentName", sqlParams);
SqlHelper.AddVarcharPara(entity.Description, "@Description", sqlParams);
SqlHelper.AddVarcharPara(entity.Url, "@Url", sqlParams);
SqlHelper.AddVarbinaryPara(fileData, "@Attachment", sqlParams);
SqlHelper.AddVarcharPara(entity.CreatedBy, "@CreatedBy", sqlParams);
SqlHelper.AddDatePara(DateTime.Now, "@CreatedOn", sqlParams);
SqlHelper.AddVarcharPara(entity.UpdatedBy, "@UpdatedBy", sqlParams);
SqlHelper.AddDatePara(DateTime.Now, "@UpdatedOn", sqlParams);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(sqlParams.ToArray());
connection.Open();
command.ExecuteNonQuery();
if (((Int32)command.Parameters["@return_value"].Value) != 0)
{
return;
}
entity.Id = (int)documentId.Value;
}
}
}
/// <summary>
/// Updates the specified upload object.
/// </summary>
/// <param name="entity">The upload.</param>
public void Update(Upload entity)
{
var upload = entity;
Debug.Assert(upload != null, "upload != null");
using (var connection = new SqlConnection(DbConnection))
{
using (var command = new SqlCommand("PaUploadUpdate", connection))
{
var sqlParams = new List <SqlParameter>();
var paramReturnValue = new SqlParameter("@return_value", SqlDbType.Int)
{
Direction = ParameterDirection.ReturnValue
};
sqlParams.Add(paramReturnValue);
SqlHelper.AddIntPara(upload.UploadId, "@Id", sqlParams);
SqlHelper.AddIntPara(upload.ProjectId, "@ProjectId", sqlParams);
SqlHelper.AddDatePara(upload.DateUploaded, "@DateUploaded", sqlParams);
SqlHelper.AddVarcharPara(upload.SourceFile, "@SourceFile", sqlParams);
SqlHelper.AddIntPara(upload.Rows, "@Rows", sqlParams);
SqlHelper.AddVarcharPara(upload.Status, "@Status", sqlParams);
SqlHelper.AddVarcharPara(upload.Name, "@Name", sqlParams);
SqlHelper.AddVarcharPara(upload.UploadedBy, "@UploadedBy", sqlParams);
SqlHelper.AddBitPara(upload.IncludesOutcomes, "@IncludesOutcomes", sqlParams);
SqlHelper.AddBitPara(upload.AdditionalReview, "@AdditionalReview", sqlParams);
SqlHelper.AddBitPara(upload.InScope, "@InScope", sqlParams);
SqlHelper.AddBitPara(upload.AcceptedFlag, "@AcceptedFlag", sqlParams);
SqlHelper.AddBitPara(upload.RandomFlag, "@RandomFlag", sqlParams);
SqlHelper.AddBitPara(upload.NationalFlag, "@NationalFlag", sqlParams);
SqlHelper.AddVarcharPara(upload.ServerFile, "@ServerFile", sqlParams);
SqlHelper.AddNullableDateParameter(upload.DueDate, "@DueDate", sqlParams);
SqlHelper.AddVarcharPara(upload.UpdatedBy, "@UpdatedBy", sqlParams);
SqlHelper.AddDatePara(DateTime.Now, "@UpdatedOn", sqlParams);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(sqlParams.ToArray());
connection.Open();
command.ExecuteNonQuery();
}
}
}
/// <summary>
/// Updates the specified bulletin.
/// </summary>
/// <param name="entity">The bulletin.</param>
public void Update(Bulletin entity)
{
var bulletin = entity;
Debug.Assert(bulletin != null, "Bulletin != null");
using (var connection = new SqlConnection(DbConnection))
{
using (var command = new SqlCommand("PaBulletinUpdate", connection))
{
var sqlParams = new List <SqlParameter>();
SqlHelper.AddReturnPara("@return_value", sqlParams);
SqlHelper.AddIntPara(bulletin.BulletinId, "@BulletinId", sqlParams);
if (bulletin.ProjectId > 0)
{
SqlHelper.AddIntPara(bulletin.ProjectId, "@ProjectId", sqlParams);
}
SqlHelper.AddVarcharPara(bulletin.BulletinTitle, "@Title", sqlParams);
SqlHelper.AddVarcharPara(bulletin.Description, "@Description", sqlParams);
SqlHelper.AddVarcharPara(bulletin.BulletinType, "@BulletinType", sqlParams);
if (bulletin.StartDate != new DateTime(1, 1, 1))
{
SqlHelper.AddDatePara(bulletin.StartDate, "@StartDate", sqlParams);
}
if (bulletin.EndDate != new DateTime(1, 1, 1))
{
SqlHelper.AddDatePara(bulletin.EndDate, "@EndDate", sqlParams);
}
//SqlHelper.AddVarcharPara( bulletin.CreatedBy, "@CreatedBy", sqlParams );
//SqlHelper.AddDatePara( bulletin.CreatedOn, "@CreatedOn", sqlParams );
SqlHelper.AddVarcharPara(bulletin.UpdatedBy, "@UpdatedBy", sqlParams);
SqlHelper.AddDatePara(DateTime.Now, "@UpdatedOn", sqlParams);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(sqlParams.ToArray());
connection.Open();
command.ExecuteNonQuery();
}
}
}
/// <summary>
/// Updates the Control record
/// </summary>
/// <param name="entity">The entity.</param>
public void Update(PatControl entity)
{
var patControl = entity;
Debug.Assert(patControl != null, "control != null");
using (var connection = new SqlConnection(DbConnection))
{
using (var command = new SqlCommand("PaControlUpdate", connection))
{
var sqlParams = new List <SqlParameter>();
SqlHelper.AddReturnPara("@return_value", sqlParams);
if (patControl.ControlId > 0)
{
SqlHelper.AddIntPara(patControl.ControlId, "@Id", sqlParams);
}
SqlHelper.AddIntPara(patControl.ProjectCount, "@ProjectCount", sqlParams);
SqlHelper.AddIntPara(patControl.SampleCount, "@SampleCount", sqlParams);
SqlHelper.AddIntPara(patControl.ReviewCount, "@ReviewCount", sqlParams);
SqlHelper.AddDecimalPara(patControl.ProjectCompletion, 3, "@ProjectCompletion", sqlParams);
SqlHelper.AddDecimalPara(patControl.TotalComplianceIndicator, 3, "@TotalComplianceIndicator", sqlParams);
SqlHelper.AddBitPara(patControl.SystemAvailable, "@SystemAvailable", sqlParams);
if (patControl.LastBatchRun != new DateTime(1, 1, 1))
{
SqlHelper.AddDatePara(patControl.LastBatchRun, "@LastBatchRun", sqlParams);
}
if (patControl.LastComplianceRun != new DateTime(1, 1, 1))
{
SqlHelper.AddDatePara(patControl.LastComplianceRun, "@LastComplianceRun", sqlParams);
}
SqlHelper.AddVarcharPara(patControl.UpdatedBy, "@UpdatedBy", sqlParams);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(sqlParams.ToArray());
connection.Open();
command.ExecuteNonQuery();
}
}
}
/// <summary>
/// Updates the specified Project.
/// </summary>
/// <param name="entity">The entity.</param>
public void Update(Project entity)
{
var project = entity;
Debug.Assert(project != null, "project != null");
using (var connection = new SqlConnection(DbConnection))
{
using (var command = new SqlCommand("PaProjectUpdate", connection))
{
var sqlParams = new List <SqlParameter>();
SqlHelper.AddReturnPara("@return_value", sqlParams);
if (project.ProjectId > 0)
{
SqlHelper.AddIntPara(project.ProjectId, "@ProjectId", sqlParams);
}
SqlHelper.AddVarcharPara(project.ProjectType, "@ProjectType", sqlParams);
SqlHelper.AddVarcharPara(project.ProjectName, "@ProjectName", sqlParams);
SqlHelper.AddVarcharPara(project.OrgCode, "@OrgCode", sqlParams);
SqlHelper.AddVarcharPara(project.Coordinator, "@Coordinator", sqlParams);
SqlHelper.AddVarcharPara(project.Comments, "@Comments", sqlParams);
SqlHelper.AddBitPara(project.Resource_NO, "@Resource_NO", sqlParams);
SqlHelper.AddBitPara(project.Resource_NSW_ACT, "@Resource_NSW_ACT", sqlParams);
SqlHelper.AddBitPara(project.Resource_QLD, "@Resource_QLD", sqlParams);
SqlHelper.AddBitPara(project.Resource_NT, "@Resource_NT", sqlParams);
SqlHelper.AddBitPara(project.Resource_WA, "@Resource_WA", sqlParams);
SqlHelper.AddBitPara(project.Resource_SA, "@Resource_SA", sqlParams);
SqlHelper.AddBitPara(project.Resource_TAS, "@Resource_TAS", sqlParams);
SqlHelper.AddBitPara(project.Resource_VIC, "@Resource_VIC", sqlParams);
SqlHelper.AddVarcharPara(project.UpdatedBy, "@UpdatedBy", sqlParams);
SqlHelper.AddDatePara(DateTime.Now, "@UpdatedOn", sqlParams);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(sqlParams.ToArray());
connection.Open();
command.ExecuteNonQuery();
}
}
}
/// <summary>
/// Gets all the audit records based on the selection criteria.
/// </summary>
/// <param name="userId">The user identifier.</param>
/// <param name="from">From date time.</param>
/// <param name="to">To date time.</param>
/// <returns>List of audit records</returns>
public List <UserActivity> GetAll(string userId, DateTime from, DateTime to)
{
if (string.IsNullOrEmpty(userId))
{
userId = String.Empty;
}
if (from.Equals(new DateTime(1, 1, 1)))
{
from = new DateTime(2011, 1, 1);
}
if (to.Equals(new DateTime(1, 1, 1)))
{
to = DateTime.Now;
}
var activityList = new List <UserActivity>();
using (var connection = new SqlConnection(DbConnection))
{
using (var command = new SqlCommand("PaUserActivityGet", connection))
{
var sqlParams = new List <SqlParameter>();
var paramReturnValue = new SqlParameter("@return_value", SqlDbType.Int)
{
Direction = ParameterDirection.ReturnValue
};
sqlParams.Add(paramReturnValue);
if (!string.IsNullOrEmpty(userId))
{
SqlHelper.AddVarcharPara(userId, "@UserId", sqlParams);
}
SqlHelper.AddDatePara(from, "@CreatedFrom", sqlParams);
SqlHelper.AddDatePara(to, "@CreatedTo", sqlParams);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(sqlParams.ToArray());
connection.Open();
SqlDataReader rdr = null;
try
{
rdr = command.ExecuteReader();
while (rdr.Read())
{
AddActivityToList(activityList, rdr);
}
}
finally
{
if (rdr != null)
{
rdr.Close();
}
}
}
}
return(activityList);
}
/// <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 all by upload identifier.
/// </summary>
/// <param name="uploadId">The upload identifier.</param>
/// <param name="gridSettings">The grid settings.</param>
/// <param name="projectStatus">The project status.</param>
/// <returns></returns>
public List <Review> GetAllByUploadId(int uploadId, Models.GridSettings gridSettings, string projectStatus)
{
if (string.IsNullOrEmpty(gridSettings.SortColumn))
{
gridSettings.SortColumn = "ReviewId";
}
const string sprocName = "PaReviewsGetAllByUpload";
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(uploadId, "@UploadId", 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)
{
if (!rule.field.Equals("ReviewStatus"))
{
// convert rule into a parameter
if (rule.field.IndexOf("Date", StringComparison.Ordinal) > -1)
{
SqlHelper.AddDatePara(DateTime.Parse(rule.data), "@" + rule.field, sqlParams);
}
else
{
if ((rule.field.IndexOf("Id", StringComparison.Ordinal) > -1) ||
(rule.field.IndexOf("Number", StringComparison.Ordinal) > -1))
{
SqlHelper.AddBigIntPara(Int64.Parse(rule.data), "@" + rule.field, sqlParams);
}
else
{
switch (rule.field)
{
case "ReferredToFraud":
case "IsAdditionalReview":
case "IsOutOfScope":
SqlHelper.AddBitPara(ConvertStringToBool(rule.data), "@" + rule.field, sqlParams);
break;
default:
SqlHelper.AddVarcharPara(rule.data, "@" + rule.field, sqlParams);
if (!rule.field.Equals("AssignedTo"))
{
var opValue = rule.op.Equals("eq") ? 1 : 0;
SqlHelper.AddIntPara(opValue, "@" + rule.field + "Op", sqlParams);
}
break;
}
// add the Op parameter
}
}
}
}
}
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(sqlParams.ToArray());
connection.Open();
LoadList(reviewList, command, sprocName);
}
}
return(reviewList);
}
/// <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);
}
public void Add(Upload upload)
{
Debug.Assert(upload != null, "upload != null");
using (var connection = new SqlConnection(DbConnection))
{
using (var command = new SqlCommand("PaUploadInsert", connection))
{
var sqlParams = new List <SqlParameter>();
var paramReturnValue = new SqlParameter("@return_value", SqlDbType.Int)
{
Direction = ParameterDirection.ReturnValue
};
sqlParams.Add(paramReturnValue);
//also get the Id back
var paramUploadId = new SqlParameter("@UploadId", SqlDbType.Int)
{
Direction = ParameterDirection.InputOutput,
Value = 0
};
sqlParams.Add(paramUploadId);
SqlHelper.AddIntPara(upload.ProjectId, "@ProjectId", sqlParams);
SqlHelper.AddDatePara(upload.DateUploaded, "@DateUploaded", sqlParams);
SqlHelper.AddVarcharPara(upload.SourceFile, "@SourceFile", sqlParams);
SqlHelper.AddVarcharPara(upload.Name, "@Name", sqlParams);
SqlHelper.AddVarcharPara(upload.UploadedBy, "@UploadedBy", sqlParams);
SqlHelper.AddBitPara(upload.IncludesOutcomes, "@IncludesOutcomes", sqlParams);
SqlHelper.AddBitPara(upload.AdditionalReview, "@AdditionalReview", sqlParams);
SqlHelper.AddBitPara(upload.InScope, "@InScope", sqlParams);
SqlHelper.AddBitPara(upload.AcceptedFlag, "@AcceptedFlag", sqlParams);
SqlHelper.AddBitPara(upload.RandomFlag, "@RandomFlag", sqlParams);
SqlHelper.AddBitPara(upload.NationalFlag, "@NationalFlag", sqlParams);
SqlHelper.AddNullableDateParameter(upload.DueDate, "@DueDate", sqlParams);
SqlHelper.AddVarcharPara(upload.ServerFile, "@ServerFile", sqlParams);
SqlHelper.AddVarcharPara(upload.CreatedBy, "@CreatedBy", sqlParams);
SqlHelper.AddVarcharPara(upload.UpdatedBy, "@UpdatedBy", sqlParams);
SqlHelper.AddDatePara(upload.CreatedOn, "@CreatedOn", sqlParams);
SqlHelper.AddDatePara(upload.UpdatedOn, "@UpdatedOn", sqlParams);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(sqlParams.ToArray());
connection.Open();
command.ExecuteNonQuery();
if (((Int32)command.Parameters["@return_value"].Value) != 0)
{
return;
}
upload.UploadId = (int)paramUploadId.Value;
}
}
}
/// <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 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 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 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>
/// Adds the specified entity.
/// </summary>
/// <param name="entity">The entity.</param>
public void Add(Sample entity)
{
var sample = entity;
Debug.Assert(sample != null, "sample != null");
using (var connection = new SqlConnection(DbConnection))
{
using (var command = new SqlCommand("PaSampleInsert", connection))
{
var sqlParams = new List <SqlParameter>();
SqlHelper.AddReturnPara("@return_value", sqlParams);
SqlHelper.AddVarcharPara(sample.SessionKey, "@SessionKey", sqlParams);
SqlHelper.AddVarcharPara(sample.ClaimTypeDescription, "@ClaimTypeDescription", sqlParams);
SqlHelper.AddVarcharPara(sample.ContractTypeDescription, "@ContractTypeDescription", sqlParams);
SqlHelper.AddVarcharPara(sample.SiteDescription, "@SiteDescription", sqlParams);
SqlHelper.AddVarcharPara(sample.EsaDescription, "@EsaDescription", sqlParams);
SqlHelper.AddVarcharPara(sample.OrgDescription, "@OrgDescription", sqlParams);
SqlHelper.AddBigIntPara(sample.ClaimId, "@ClaimId", sqlParams);
SqlHelper.AddIntPara(sample.ClaimSequenceNumber, "@ClaimSequenceNumber", sqlParams);
SqlHelper.AddVarcharPara(sample.ClaimType, "@ClaimType", sqlParams);
SqlHelper.AddMoneyPara(sample.ClaimAmount, "@ClaimMoney", sqlParams);
SqlHelper.AddVarcharPara(sample.SiteCode, "@SiteCode", sqlParams);
SqlHelper.AddVarcharPara(sample.SupervisingSiteCode, "@SupervisingSiteCode", sqlParams);
SqlHelper.AddVarcharPara(sample.OrgCode, "@OrgCode", sqlParams);
SqlHelper.AddBigIntPara(sample.ActivityId, "@ActivityId", sqlParams);
SqlHelper.AddDatePara(sample.ClaimCreationDate, "@ClaimCreationDate", sqlParams);
SqlHelper.AddVarcharPara(sample.StatusCode, "@ClaimStatusCode", sqlParams);
SqlHelper.AddVarcharPara(sample.StatusCodeDescription, "@ClaimStatusDescription", sqlParams);
SqlHelper.AddVarcharPara(sample.StateCode, "@StateCode", sqlParams);
SqlHelper.AddVarcharPara(sample.ManagedBy, "@ManagedBy", sqlParams);
SqlHelper.AddVarcharPara(sample.ContractId, "@ContractId", sqlParams);
SqlHelper.AddVarcharPara(sample.ContractType, "@ContractType", sqlParams);
SqlHelper.AddVarcharPara(sample.ContractTitle, "@ContractTitle", sqlParams);
SqlHelper.AddVarcharPara(sample.EsaCode, "@EsaCode", sqlParams);
SqlHelper.AddBigIntPara(sample.JobseekerId, "@JobseekerId", sqlParams);
SqlHelper.AddVarcharPara(sample.GivenName, "@GivenName", sqlParams);
SqlHelper.AddVarcharPara(sample.Surname, "@LastName", sqlParams);
SqlHelper.AddCharPara(sample.AutoSpecialClaimFlag, "@AutoSpecialClaimFlag", sqlParams);
SqlHelper.AddCharPara(sample.ManSpecialClaimFlag, "@ManualSpecialClaimFlag", sqlParams);
SqlHelper.AddVarcharPara(sample.CreatedBy, "@CreatedBy", sqlParams);
// Output parameters
var paramId = new SqlParameter("@Id", SqlDbType.Int)
{
Direction = ParameterDirection.InputOutput,
Value = 0
};
sqlParams.Add(paramId);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(sqlParams.ToArray());
connection.Open();
command.ExecuteNonQuery();
if (((Int32)command.Parameters["@return_value"].Value) != 0)
{
return;
}
sample.Id = (int)paramId.Value;
}
}
}
