/// <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 a set of reviews with the same outcomes.
/// </summary>
/// <param name="reviews">The reviews.</param>
/// <param name="outcome">The outcome.</param>
/// <param name="userId">The user identifier.</param>
/// <exception cref="System.ApplicationException">Bulk Outcomes failed to update</exception>
public void BulkOutcome(IEnumerable <int> reviews, Review outcome, string userId)
{
var dtReviews = SqlHelper.BuildIdTable();
SqlHelper.PopulateIdsTable(reviews, dtReviews);
using (var connection = new SqlConnection(DbConnection))
{
using (var command = new SqlCommand("PaReviewBulkOutcome", connection))
{
var sqlParams = new List <SqlParameter>();
var paramReturnValue = new SqlParameter("@return_value", SqlDbType.Int)
{
Direction = ParameterDirection.ReturnValue
};
sqlParams.Add(paramReturnValue);
SqlHelper.AddTablePara(dtReviews, "@ReviewIds", "IntTableType", sqlParams);
SqlHelper.AddVarcharPara(outcome.AssessmentCode, "@AssessmentCode", sqlParams);
SqlHelper.AddVarcharPara(outcome.AssessmentAction, "@AssessmentAction", sqlParams);
SqlHelper.AddVarcharPara(outcome.RecoveryReason, "@RecoveryReason", sqlParams);
SqlHelper.AddVarcharPara(outcome.OutcomeCode, "@OutcomeCode", sqlParams);
SqlHelper.AddVarcharPara(outcome.Comments, "@Comments", sqlParams);
SqlHelper.AddNullableDateParameter(outcome.AssessmentDate, "@AssessmentDate", sqlParams);
SqlHelper.AddNullableDateParameter(outcome.AssessmentActionDate, "@AssessmentActionDate", sqlParams);
SqlHelper.AddNullableDateParameter(outcome.RecoveryReasonDate, "@RecoveryReasonDate", sqlParams);
SqlHelper.AddNullableDateParameter(outcome.FinalOutcomeDate, "@FinalOutcomeDate", sqlParams);
SqlHelper.AddVarcharPara(userId, "@UpdatedBy", sqlParams);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(sqlParams.ToArray());
connection.Open();
command.ExecuteNonQuery();
if (((Int32)command.Parameters["@return_value"].Value) == -1)
{
throw new ApplicationException("Bulk Outcomes failed to update");
}
}
}
}
/// <summary>
/// Updates the specified review.
/// </summary>
/// <param name="entity">The entity.</param>
public void Update(Review entity)
{
var review = entity;
Debug.Assert(review != null, "review != null");
using (var connection = new SqlConnection(DbConnection))
{
using (var command = new SqlCommand("PaReviewUpdate", connection))
{
var sqlParams = new List <SqlParameter>();
var paramReturnValue = new SqlParameter("@return_value", SqlDbType.Int)
{
Direction = ParameterDirection.ReturnValue
};
sqlParams.Add(paramReturnValue);
SqlHelper.AddIntPara(review.ReviewId, "@ReviewId", sqlParams);
SqlHelper.AddIntPara(review.ProjectId, "@ProjectId", sqlParams);
SqlHelper.AddIntPara(review.UploadId, "@UploadId", sqlParams);
SqlHelper.AddIntPara(review.ClaimSequenceNumber, "@ClaimSequenceNumber", sqlParams);
SqlHelper.AddNullableBigIntPara(review.JobseekerId, "@JobseekerId", sqlParams);
SqlHelper.AddNullableBigIntPara(review.ClaimId, "@ClaimId", sqlParams);
SqlHelper.AddNullableBigIntPara(review.ActivityId, "@ActivityId", sqlParams);
SqlHelper.AddVarcharPara(review.SiteCode, "@SiteCode", sqlParams);
SqlHelper.AddVarcharPara(review.SiteName, "@SiteName", sqlParams);
SqlHelper.AddVarcharPara(review.OrgName, "@OrgName", sqlParams);
SqlHelper.AddVarcharPara(review.OrgCode, "@OrgCode", sqlParams);
SqlHelper.AddVarcharPara(review.ESACode, "@ESACode", sqlParams);
SqlHelper.AddVarcharPara(review.StateCode, "@StateCode", sqlParams);
SqlHelper.AddVarcharPara(review.ManagedBy, "@ManagedBy", sqlParams);
SqlHelper.AddNullableVarcharPara(review.OutcomeCode, "@OutcomeCode", sqlParams);
SqlHelper.AddNullableVarcharPara(review.AssessmentCode, "@AssessmentCode", sqlParams);
SqlHelper.AddNullableVarcharPara(review.RecoveryReason, "@RecoveryReason", sqlParams);
SqlHelper.AddNullableVarcharPara(review.AssessmentAction, "@AssessmentAction", sqlParams);
SqlHelper.AddVarcharPara(review.JobSeekerGivenName, "@JobSeekerGivenName", sqlParams);
SqlHelper.AddVarcharPara(review.JobSeekerSurname, "@JobSeekerSurname", sqlParams);
SqlHelper.AddVarcharPara(review.ClaimType, "@ClaimType", sqlParams);
SqlHelper.AddVarcharPara(review.Comments, "@Comments", sqlParams);
SqlHelper.AddBitPara(review.IsAdditionalReview, "@IsAdditionalReview", sqlParams);
SqlHelper.AddBitPara(review.IsOutOfScope, "@IsOutOfScope", sqlParams);
SqlHelper.AddBitPara(review.AutoSpecialClaim, "@AutoSpecialClaim", sqlParams);
SqlHelper.AddBitPara(review.ManualSpecialClaim, "@ManualSpecialClaim", sqlParams);
SqlHelper.AddMoneyPara(review.ClaimAmount, "@ClaimAmount", sqlParams);
SqlHelper.AddMoneyPara(review.ClaimRecoveryAmount, "@ClaimRecoveryAmount", sqlParams);
SqlHelper.AddNullableDateParameter(review.AssessmentDate, "@AssessmentDate", sqlParams);
SqlHelper.AddNullableDateParameter(review.AssessmentActionDate, "@AssessmentActionDate", sqlParams);
SqlHelper.AddNullableDateParameter(review.RecoveryReasonDate, "@RecoveryReasonDate", sqlParams);
SqlHelper.AddNullableDateParameter(review.FinalOutcomeDate, "@FinalOutcomeDate", sqlParams);
SqlHelper.AddNullableDateParameter(review.ClaimCreationDate, "@ClaimCreationDate", sqlParams);
SqlHelper.AddVarcharPara(review.UpdatedBy, "@UpdatedBy", sqlParams);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(sqlParams.ToArray());
connection.Open();
command.ExecuteNonQuery();
}
}
}
/// <summary>
/// Adds the specified review.
/// </summary>
/// <param name="review">The review.</param>
/// <returns>
/// id of the new review
/// </returns>
public int Add(Review review)
{
var reviewId = review.ReviewId;
if (reviewId == 0)
{
using (var connection = new SqlConnection(DbConnection))
{
using (var command = new SqlCommand("PaReviewInsert", connection))
{
var sqlParams = new List <SqlParameter>();
var paramReturnValue = new SqlParameter("@return_value", SqlDbType.Int)
{
Direction = ParameterDirection.ReturnValue
};
sqlParams.Add(paramReturnValue);
var paramId = new SqlParameter("@ReviewId", SqlDbType.Int)
{
Direction = ParameterDirection.InputOutput,
Value = 0
};
sqlParams.Add(paramId);
SqlHelper.AddIntPara(review.ProjectId, "@ProjectId", sqlParams);
SqlHelper.AddIntPara(review.UploadId, "@UploadId", sqlParams);
SqlHelper.AddIntPara(review.ClaimSequenceNumber, "@ClaimSequenceNumber", sqlParams);
SqlHelper.AddNullableBigIntPara(review.JobseekerId, "@JobseekerId", sqlParams);
SqlHelper.AddNullableBigIntPara(review.ClaimId, "@ClaimId", sqlParams);
SqlHelper.AddNullableBigIntPara(review.ActivityId, "@ActivityId", sqlParams);
SqlHelper.AddVarcharPara(review.JobSeekerGivenName, "@JobSeekerGivenName", sqlParams);
SqlHelper.AddVarcharPara(review.JobSeekerSurname, "@JobSeekerSurname", sqlParams);
SqlHelper.AddVarcharPara(review.SiteCode, "@SiteCode", sqlParams);
SqlHelper.AddVarcharPara(review.SiteName, "@SiteName", sqlParams);
SqlHelper.AddVarcharPara(review.OrgName, "@OrgName", sqlParams);
SqlHelper.AddVarcharPara(review.OrgCode, "@OrgCode", sqlParams);
SqlHelper.AddVarcharPara(review.ESACode, "@ESACode", sqlParams);
SqlHelper.AddVarcharPara(review.StateCode, "@StateCode", sqlParams);
SqlHelper.AddVarcharPara(review.ManagedBy, "@ManagedBy", sqlParams);
SqlHelper.AddVarcharPara(review.ContractType, "@ContractType", sqlParams);
SqlHelper.AddVarcharPara(review.ClaimType, "@ClaimType", sqlParams);
SqlHelper.AddVarcharPara(review.Comments, "@Comments", sqlParams);
SqlHelper.AddVarcharPara(review.AssessmentCode, "@AssessmentCode", sqlParams);
SqlHelper.AddVarcharPara(review.RecoveryReason, "@RecoveryReason", sqlParams);
SqlHelper.AddVarcharPara(review.AssessmentAction, "@AssessmentAction", sqlParams);
SqlHelper.AddVarcharPara(review.OutcomeCode, "@OutcomeCode", sqlParams);
SqlHelper.AddBitPara(review.IsAdditionalReview, "@IsAdditionalReview", sqlParams);
SqlHelper.AddBitPara(review.IsOutOfScope, "@IsOutOfScope", sqlParams);
SqlHelper.AddBitPara(review.AutoSpecialClaim, "@AutoSpecialClaim", sqlParams);
SqlHelper.AddBitPara(review.ManualSpecialClaim, "@ManualSpecialClaim", sqlParams);
SqlHelper.AddNullableDateParameter(review.ClaimCreationDate, "@ClaimCreationDate", sqlParams);
SqlHelper.AddMoneyPara(review.ClaimAmount, "@ClaimAmount", sqlParams);
SqlHelper.AddMoneyPara(review.ClaimRecoveryAmount, "@ClaimRecoveryAmount", sqlParams);
SqlHelper.AddVarcharPara(review.CreatedBy, "@CreatedBy", sqlParams);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(sqlParams.ToArray());
connection.Open();
command.ExecuteNonQuery();
if (((Int32)command.Parameters["@return_value"].Value) == 0)
{
reviewId = (int)paramId.Value;
}
}
}
}
return(reviewId);
}
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;
}
}
}
