/// <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> /// Updates the specified entity. /// </summary> /// <param name="entity">The entity.</param> public void Update(Sample entity) { var sample = entity; Debug.Assert(sample != null, "sample != null"); using (var connection = new SqlConnection(DbConnection)) { using (var command = new SqlCommand("PaSampleUpdatebyId", connection)) { var sqlParams = new List <SqlParameter>(); SqlHelper.AddReturnPara("@return_value", sqlParams); if (sample.Id > 0) { SqlHelper.AddIntPara(sample.Id, "@Id", sqlParams); } SqlHelper.AddVarcharPara(sample.UpdatedBy, "@UpdatedBy", sqlParams); SqlHelper.AddBitPara(sample.Selected, "@Selected", sqlParams); command.CommandType = CommandType.StoredProcedure; command.Parameters.AddRange(sqlParams.ToArray()); connection.Open(); command.ExecuteNonQuery(); } } }
/// <summary> /// Gets the Upload by its identifier. /// </summary> /// <param name="id">The identifier.</param> /// <returns> /// upload record /// </returns> public Upload GetById(int id) { Upload upload = null; var uploadList = new List <Upload>(); using (var connection = new SqlConnection(DbConnection)) { using (var command = new SqlCommand("PaUploadGet", connection)) { var sqlParams = new List <SqlParameter>(); var paramReturnValue = new SqlParameter("@return_value", SqlDbType.Int) { Direction = ParameterDirection.ReturnValue }; sqlParams.Add(paramReturnValue); SqlHelper.AddIntPara(id, "@Id", sqlParams); command.CommandType = CommandType.StoredProcedure; command.Parameters.AddRange(sqlParams.ToArray()); connection.Open(); LoadList(uploadList, command); if (uploadList.Count > 0) { upload = uploadList[0]; } } } return(upload); }
/// <summary> /// Gets all the finished Reviews for a particular project. /// </summary> /// <param name="projectId">The project identifier.</param> /// <returns> /// list of review objects /// </returns> public IEnumerable <Review> GetFinishedReviewsByProjectId(int projectId) { const string sprocName = "PaReviewGetFinishedByProjectId"; 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); command.CommandType = CommandType.StoredProcedure; command.Parameters.AddRange(sqlParams.ToArray()); connection.Open(); LoadList(reviewList, command, sprocName); } } return(reviewList); }
/// <summary> /// Gets the checklist by its Review Id /// </summary> /// <param name="id">The check list review Id.</param> /// <returns></returns> public CheckList GetById(int id) { var reviewID = id; CheckList checkList = null; var checkLists = new List <CheckList>(); using (var connection = new SqlConnection(DbConnection)) { using (var command = new SqlCommand("PaCheckListGetById", connection)) { var sqlParams = new List <SqlParameter>(); var paramReturnValue = new SqlParameter("@return_value", SqlDbType.Int) { Direction = ParameterDirection.ReturnValue }; sqlParams.Add(paramReturnValue); SqlHelper.AddIntPara(reviewID, "@ReviewId", sqlParams); command.CommandType = CommandType.StoredProcedure; command.Parameters.AddRange(sqlParams.ToArray()); connection.Open(); LoadList(checkLists, command); if (checkLists.Count > 0) { checkList = checkLists[0]; } } } return(checkList); }
/// <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> /// Saves the project contracts. /// </summary> /// <param name="projectId">The project identifier.</param> /// <param name="list">The list.</param> /// <param name="userId">The user identifier.</param> public void SaveProjectContracts(int projectId, List <ProjectContract> list, string userId) { const string fieldName = "ContractType"; var dtFields = SqlHelper.BuildVarcharTable(fieldName); SqlHelper.PopulateVarcharTable(fieldName, list, dtFields); using (var connection = new SqlConnection(DbConnection)) { using (var command = new SqlCommand("PaProjectContractSave", 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.AddTablePara(dtFields, "@ContractTypeTable", "ContractTypeTableType", sqlParams); SqlHelper.AddVarcharPara(userId, "@CreatedBy", sqlParams); command.CommandType = CommandType.StoredProcedure; command.Parameters.AddRange(sqlParams.ToArray()); connection.Open(); command.ExecuteNonQuery(); } } }
/// <summary> /// Gets the Review by its identifier. /// </summary> /// <param name="id">The identifier.</param> /// <returns> /// review object /// </returns> public Review GetById(int id) { Review review = null; var reviewList = new List <Review>(); const string sprocName = "PaReviewGetById"; 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(id, "@ReviewId", sqlParams); command.CommandType = CommandType.StoredProcedure; command.Parameters.AddRange(sqlParams.ToArray()); connection.Open(); LoadList(reviewList, command, sprocName); if (reviewList.Count > 0) { review = reviewList[0]; } } } return(review); }
/// <summary> /// Gets the bulletin by its id. /// </summary> /// <param name="id">The bulletin id.</param> /// <returns> /// a bulletin /// </returns> public Bulletin GetById(int id) { Bulletin bulletin = null; var bulletinList = new List <Bulletin>(); using (var connection = new SqlConnection(DbConnection)) { using (var command = new SqlCommand("PaBulletinGetById", connection)) { var sqlParams = new List <SqlParameter>(); var paramReturnValue = new SqlParameter("@return_value", SqlDbType.Int) { Direction = ParameterDirection.ReturnValue }; sqlParams.Add(paramReturnValue); SqlHelper.AddIntPara(id, "@BulletinId", sqlParams); command.CommandType = CommandType.StoredProcedure; command.Parameters.AddRange(sqlParams.ToArray()); connection.Open(); LoadBulletinList(bulletinList, command); if (bulletinList.Count > 0) { bulletin = bulletinList[0]; } } } return(bulletin); // as T; }
/// <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 <ProjectContract> GetAllByProjectId(int projectId, GridSettings gridSettings) { var list = new List <ProjectContract>(); using (var connection = new SqlConnection(DbConnection)) { using (var command = new SqlCommand("PaProjectContractGetAll", 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); command.CommandType = CommandType.StoredProcedure; command.Parameters.AddRange(sqlParams.ToArray()); connection.Open(); LoadList(list, command); } } return(list); }
/// <summary> /// Gets the Project by its identifier. /// </summary> /// <param name="id">The identifier.</param> /// <returns> /// Project object /// </returns> public Project GetById(int id) { Project project = null; var projectList = new List <Project>(); using (var connection = new SqlConnection(DbConnection)) { using (var command = new SqlCommand("PaProjectGet", connection)) { var sqlParams = new List <SqlParameter>(); var paramReturnValue = new SqlParameter("@return_value", SqlDbType.Int) { Direction = ParameterDirection.ReturnValue }; sqlParams.Add(paramReturnValue); SqlHelper.AddIntPara(id, "@ProjectId", sqlParams); command.CommandType = CommandType.StoredProcedure; command.Parameters.AddRange(sqlParams.ToArray()); connection.Open(); LoadProjectList(projectList, command); if (projectList.Count > 0) { project = projectList[0]; } } } return(project); // as T; }
/// <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> /// 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> /// 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> /// 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> /// 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 the project questions by project id /// </summary> /// <param name="projectId">The project identifier.</param> /// <returns></returns> public List <String> GetProjectQuestions(int projectId) { var list = new List <String>(); using (var connection = new SqlConnection(DbConnection)) { using (var command = new SqlCommand("PaGetProjectQuestions", 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); command.CommandType = CommandType.StoredProcedure; command.Parameters.AddRange(sqlParams.ToArray()); connection.Open(); SqlDataReader reader = null; try { reader = command.ExecuteReader(); while (reader.Read()) { list.Add(string.Format("{0}", reader["QuestionText"])); } } finally { if (reader != null) { reader.Close(); } } } } return(list); }
public void Add(ProjectContract entity) { var projectContract = entity; Debug.Assert(projectContract != null, "projectContract != null"); using (var connection = new SqlConnection(DbConnection)) { using (var command = new SqlCommand("PaProjectContractInsert", connection)) { var sqlParams = new List <SqlParameter>(); SqlHelper.AddReturnPara("@return_value", sqlParams); SqlHelper.AddIntPara(projectContract.ProjectId, "@ProjectId", sqlParams); SqlHelper.AddVarcharPara(projectContract.ContractType, "@ContractType", sqlParams); SqlHelper.AddVarcharPara(projectContract.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; } projectContract.Id = (int)paramId.Value; } } }
/// <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); }
/// <summary> /// Counts the review questionnaires by upload identifier. /// </summary> /// <param name="uploadId">The upload identifier.</param> /// <returns></returns> public int CountReviewQuestionnairesByUploadId(int uploadId) { var theCount = 0; using (var connection = new SqlConnection(DbConnection)) { using (var command = new SqlCommand("PaReviewQuestionnaireCountByUploadId", 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 paramCount = new SqlParameter("@Count", SqlDbType.Int) { Direction = ParameterDirection.InputOutput, Value = 0 }; sqlParams.Add(paramCount); SqlHelper.AddIntPara(uploadId, "@UploadId", sqlParams); command.CommandType = CommandType.StoredProcedure; command.Parameters.AddRange(sqlParams.ToArray()); connection.Open(); command.ExecuteNonQuery(); if (((int)command.Parameters["@return_value"].Value) == 0) { theCount = (int)paramCount.Value; } } return(theCount); } }
public void Delete(int id) { using (var connection = new SqlConnection(DbConnection)) { using (var command = new SqlCommand("PaUploadDelete", connection)) { var sqlParams = new List <SqlParameter>(); var paramReturnValue = new SqlParameter("@return_value", SqlDbType.Int) { Direction = ParameterDirection.ReturnValue }; sqlParams.Add(paramReturnValue); SqlHelper.AddIntPara(id, "@Id", sqlParams); command.CommandType = CommandType.StoredProcedure; command.Parameters.AddRange(sqlParams.ToArray()); connection.Open(); command.ExecuteNonQuery(); } } }
/// <summary> /// Updates the Compliance indicator. /// </summary> /// <param name="entity">The CI data.</param> public void Update(ComplianceIndicator entity) { var complianceIndicator = entity; Debug.Assert(complianceIndicator != null, "complianceIndicator != null"); using (var connection = new SqlConnection(DbConnection)) { using (var command = new SqlCommand("PaComplianceIndicatorUpdate", connection)) { var sqlParams = new List <SqlParameter>(); var paramReturnValue = new SqlParameter("@return_value", SqlDbType.Int) { Direction = ParameterDirection.ReturnValue }; sqlParams.Add(paramReturnValue); SqlHelper.AddIntPara(complianceIndicator.ComplianceIndicatorId, "@Id", sqlParams); SqlHelper.AddVarcharPara(complianceIndicator.Programme, "@Programme", sqlParams); SqlHelper.AddVarcharPara(complianceIndicator.SubjectTypeCode, "@SubjectTypeCode", sqlParams); SqlHelper.AddVarcharPara(complianceIndicator.Subject, "@Subject", sqlParams); SqlHelper.AddVarcharPara(complianceIndicator.EsaCode, "@EsaCode", sqlParams); SqlHelper.AddVarcharPara(complianceIndicator.Quarter, "@Quarter", sqlParams); SqlHelper.AddDecimalPara(complianceIndicator.Value, 3, "@ComplianceIndicator", sqlParams); SqlHelper.AddVarcharPara(complianceIndicator.UpdatedBy, "@UpdatedBy", sqlParams); command.CommandType = CommandType.StoredProcedure; command.Parameters.AddRange(sqlParams.ToArray()); connection.Open(); command.ExecuteNonQuery(); } } }
/// <summary> /// Gets the review questionnaire by review identifier. /// </summary> /// <param name="reviewId">The review identifier.</param> /// <returns></returns> public ReviewQuestionnaire GetReviewQuestionnaireByReviewId(int reviewId) { ReviewQuestionnaire reviewQuestionnaire = null; 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(reviewId, "@ReviewId", sqlParams); command.CommandType = CommandType.StoredProcedure; command.Parameters.AddRange(sqlParams.ToArray()); connection.Open(); SqlDataReader reader = null; try { reader = command.ExecuteReader(); while (reader.Read()) { reviewQuestionnaire = new ReviewQuestionnaire { ProjectID = AppHelper.ToInt(reader["ProjectId"]), ProjectName = string.Format("{0}", reader["ProjectName"]), UploadID = AppHelper.ToInt(reader["UploadId"]), UploadName = string.Format("{0}", reader["UploadName"]), QuestionnaireID = AppHelper.ToInt(reader["QuestionnaireId"]), ReviewID = AppHelper.ToInt(reader["ReviewId"]), ReferenceID = AppHelper.ToLong(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["UpdatedOn"] as DateTime? ?? default(DateTime) }; } } finally { if (reader != null) { reader.Close(); } } } } return(reviewQuestionnaire); }
/// <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> /// 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> /// Save the specified checklist. /// If the review id exists then update otherwise insert /// </summary> /// <param name="entity">The entity.</param> public void Save(CheckList entity) { using (var connection = new SqlConnection(DbConnection)) { using (var command = new SqlCommand("PaCheckListSave", connection)) { var sqlParams = new List <SqlParameter>(); var paramReturnValue = new SqlParameter("@return_value", SqlDbType.Int) { Direction = ParameterDirection.ReturnValue }; sqlParams.Add(paramReturnValue); var checkListId = new SqlParameter("@CheckListId", SqlDbType.Int) { Direction = ParameterDirection.InputOutput, Value = 0, }; sqlParams.Add(checkListId); SqlHelper.AddIntPara(entity.ReviewID, "@ReviewId", sqlParams); // need to allow nullable bit var paraIsClaimDuplicateOverlapping = new SqlParameter("@IsClaimDuplicateOverlapping", SqlDbType.Bit) { Value = AppHelper.ToNullableBool(entity.IsClaimDuplicateOverlapping), IsNullable = true, Direction = ParameterDirection.Input }; var paraIsClaimIncludedInDeedNonPayableOutcomeList = new SqlParameter("@IsClaimIncludedInDeedNonPayableOutcomeList", SqlDbType.Bit) { Value = AppHelper.ToNullableBool(entity.IsClaimIncludedInDeedNonPayableOutcomeList), IsNullable = true, Direction = ParameterDirection.Input }; var paraDoesDocEvidenceMeetGuidelineRequirement = new SqlParameter("@DoesDocEvidenceMeetGuidelineRequirement", SqlDbType.Bit) { Value = AppHelper.ToNullableBool(entity.DoesDocEvidenceMeetGuidelineRequirement), IsNullable = true, Direction = ParameterDirection.Input }; var paraIsDocEvidenceConsistentWithESS = new SqlParameter("@IsDocEvidenceConsistentWithESS", SqlDbType.Bit) { Value = AppHelper.ToNullableBool(entity.IsDocEvidenceConsistentWithESS), IsNullable = true, Direction = ParameterDirection.Input }; var paraIsDocEvidenceSufficientToSupportPaymentType = new SqlParameter("@IsDocEvidenceSufficientToSupportPaymentType", SqlDbType.Bit) { Value = AppHelper.ToNullableBool(entity.IsDocEvidenceSufficientToSupportPaymentType), IsNullable = true, Direction = ParameterDirection.Input }; sqlParams.Add(paraIsClaimDuplicateOverlapping); sqlParams.Add(paraIsClaimIncludedInDeedNonPayableOutcomeList); sqlParams.Add(paraDoesDocEvidenceMeetGuidelineRequirement); sqlParams.Add(paraIsDocEvidenceConsistentWithESS); sqlParams.Add(paraIsDocEvidenceSufficientToSupportPaymentType); SqlHelper.AddVarcharPara(entity.Comment, "@Comment", sqlParams); SqlHelper.AddVarcharPara(entity.CreatedBy, "@CreatedBy", sqlParams); SqlHelper.AddVarcharPara(entity.UpdatedBy, "@UpdatedBy", sqlParams); command.CommandType = CommandType.StoredProcedure; command.Parameters.AddRange(sqlParams.ToArray()); connection.Open(); command.ExecuteNonQuery(); if (((Int32)command.Parameters["@return_value"].Value) != 0) { return; } entity.CheckListID = (int)checkListId.Value; } } }