public JobCardDailyActivity DeleteJobCardDailyActivity(int Id) { using (IDbConnection connection = OpenConnection(dataConnection)) { IDbTransaction txn = connection.BeginTransaction(); try { string query = @"DELETE FROM JobCardDailyActivityTask WHERE JobCardDailyActivityId=@Id; DELETE FROM JobCardDailyActivity OUTPUT deleted.JobCardDailyActivityRefNo, deleted.Image1, deleted.Image2, deleted.Image3, deleted.Image4 WHERE JobCardDailyActivityId=@Id;"; JobCardDailyActivity output = connection.Query <JobCardDailyActivity>(query, new { Id = Id }, txn).FirstOrDefault(); txn.Commit(); return(output); } catch (Exception ex) { txn.Rollback(); throw ex; } } }
private int InsertDailyActivityHead(JobCardDailyActivity model, IDbConnection connection, IDbTransaction txn) { string sql = @"insert into JobCardDailyActivity (JobCardDailyActivityDate,JobCardId,JobCardDailyActivityRefNo,Remarks,EmployeeId,CreatedBy,CreatedDate,OrganizationId) Values (@JobCardDailyActivityDate,@JobCardId,@JobCardDailyActivityRefNo,@Remarks,@EmployeeId,@CreatedBy,@CreatedDate,@OrganizationId); SELECT CAST(SCOPE_IDENTITY() as int)"; return(connection.Query <int>(sql, model, txn).Single()); }
public ActionResult Create(int Id) { JobCardRepository jcRepo = new JobCardRepository(); EmployeeRepository emRepo = new EmployeeRepository(); JobCard jc = jcRepo.GetDetailsById(Id, null); //FillTaks(jc.WorkDescriptionId); JobCardDailyActivity model = new JobCardDailyActivity(); if (jc.isProjectBased == 1) { model.JobCardDailyActivityRefNo = DatabaseCommonRepository.GetNextDocNo(38, OrganizationId); //FillTasks(); FillTasks(jc.isProjectBased); FillEmployees(); } else { model.JobCardDailyActivityRefNo = DatabaseCommonRepository.GetNextDocNo(27, OrganizationId); } model.CreatedDate = DateTime.Now; model.JobCardDailyActivityDate = DateTime.Now; model.isProjectBased = jc.isProjectBased; model.CustomerName = jc.CustomerName; if (model.isProjectBased == 0) { model.JobCardDailyActivityTask = new JobCardDailyActivityRepository().GetJobCardTasksForDailyActivity(Id, OrganizationId); } else { model.JobCardDailyActivityTask = new List <JobCardDailyActivityTask>(); model.JobCardDailyActivityTask.Add(new JobCardDailyActivityTask { TaskStartDate = model.JobCardDailyActivityDate, TaskEndDate = model.JobCardDailyActivityDate }); } //if (model.JobCardDailyActivityTask.Count > 0) //{ // foreach (var item in model.JobCardDailyActivityTask) // { // item.StartTime = "00:00"; item.EndTime = "00.00"; // } //} //model.JobCardDailyActivityTask = new List<JobCardDailyActivityTask>(); //model.JobCardDailyActivityTask.Add(new JobCardDailyActivityTask() { TaskStartDate = DateTime.Now, TaskEndDate = DateTime.Now}); Employee emp = emRepo.GetEmployee(jc.EmployeeId); model.EmployeeId = jc.EmployeeId; model.EmployeeName = emp.EmployeeName; model.JobCardNo = jc.JobCardNo; model.JobCardId = Id; ViewBag.isTxnPending = true; return(View(model)); }
public int InsertJobCardDailyActivity(JobCardDailyActivity objJobCardDailyActivity) { JobCardDailyActivityTaskRepository task = new JobCardDailyActivityTaskRepository(); using (IDbConnection connection = OpenConnection(dataConnection)) { IDbTransaction trn = connection.BeginTransaction(); int id = 0; string internalId = ""; try { if (objJobCardDailyActivity.isProjectBased == 1) { internalId = DatabaseCommonRepository.GetNewDocNo(connection, objJobCardDailyActivity.OrganizationId, 38, true, trn); } else { internalId = DatabaseCommonRepository.GetNewDocNo(connection, objJobCardDailyActivity.OrganizationId, 27, true, trn); } objJobCardDailyActivity.JobCardDailyActivityRefNo = internalId.ToString(); string sql = @"insert into JobCardDailyActivity (JobCardDailyActivityDate,JobCardId,JobCardDailyActivityRefNo,Remarks,EmployeeId,CreatedBy,CreatedDate,OrganizationId) Values (@JobCardDailyActivityDate,@JobCardId,@JobCardDailyActivityRefNo,@Remarks,@EmployeeId,@CreatedBy,@CreatedDate,@OrganizationId); SELECT CAST(SCOPE_IDENTITY() as int)"; id = connection.Query <int>(sql, objJobCardDailyActivity, trn).Single(); foreach (var item in objJobCardDailyActivity.JobCardDailyActivityTask) { if (item.ActualHours == null || item.ActualHours == 0) { continue; } item.JobCardDailyActivityId = id; item.CreatedDate = DateTime.Now; sql = @"insert into JobCardDailyActivityTask (JobCardDailyActivityId,JobCardTaskId,TaskStartDate,TaskEndDate,OverTime,ActualHours,CreatedBy,CreatedDate,OrganizationId, EmployeeId, StartTime, EndTime) Values (@JobCardDailyActivityId,@JobCardTaskId,@TaskStartDate,@TaskEndDate,@OverTime,@ActualHours,@CreatedBy,@CreatedDate,@OrganizationId, NULLIF(@EmployeeId, 0), @StartTime, @EndTime); SELECT CAST(SCOPE_IDENTITY() as int)"; var taskid = connection.Query <int>(sql, item, trn).Single(); } InsertLoginHistory(dataConnection, objJobCardDailyActivity.CreatedBy, "Create", "Job Card", id.ToString(), "0"); trn.Commit(); } catch (Exception ex) { trn.Rollback(); throw ex; } return(id); } }
public ActionResult Details(JobCardDailyActivity model) { model.OrganizationId = OrganizationId; model.CreatedDate = System.DateTime.Now; model.CreatedBy = UserID.ToString(); try { new JobCardDailyActivityRepository().UpdateJobCardDailyActivity(model); TempData["success"] = "Updated Successfully (" + model.JobCardDailyActivityRefNo + ")"; return(RedirectToAction("PreviousList")); } catch (Exception) { TempData["error"] = "Some error occurred. Please try again."; } return(View(model)); }
public int UpdateJobCardDailyActivity(JobCardDailyActivity objJobCardDailyActivity) { using (IDbConnection connection = OpenConnection(dataConnection)) { IDbTransaction txn = connection.BeginTransaction(); string query = @"Delete JobCardDailyActivityTask OUTPUT DELETED.JobCardDailyActivityId WHERE JobCardDailyActivityId=@JobCardDailyActivityId;"; string output = connection.Query <string>(query, new { JobCardDailyActivityId = objJobCardDailyActivity.JobCardDailyActivityId }, txn).FirstOrDefault(); string sql = @"UPDATE JobCardDailyActivity SET JobCardDailyActivityDate=@JobCardDailyActivityDate,JobCardId = @JobCardId , JobCardDailyActivityRefNo=@JobCardDailyActivityRefNo,Remarks=@Remarks,EmployeeId=@EmployeeId, CreatedBy = @CreatedBy,CreatedDate=@CreatedDate,OrganizationId=@OrganizationId OUTPUT INSERTED.JobCardDailyActivityRefNo WHERE JobCardDailyActivityId = @JobCardDailyActivityId"; try { var id = connection.Execute(sql, objJobCardDailyActivity, txn); foreach (var item in objJobCardDailyActivity.JobCardDailyActivityTask) { item.JobCardDailyActivityId = objJobCardDailyActivity.JobCardDailyActivityId; item.CreatedDate = DateTime.Now; sql = @"insert into JobCardDailyActivityTask (JobCardDailyActivityId,JobCardTaskId,TaskStartDate,TaskEndDate,OverTime,ActualHours,CreatedBy,CreatedDate,OrganizationId, EmployeeId, StartTime, EndTime) Values (@JobCardDailyActivityId,@JobCardTaskMasterId,@TaskStartDate,@TaskEndDate,@OverTime,@ActualHours,@CreatedBy,@CreatedDate,@OrganizationId, @EmployeeId, @StartTime, @EndTime); SELECT CAST(SCOPE_IDENTITY() as int)"; var taskid = connection.Query <int>(sql, item, txn).Single(); } InsertLoginHistory(dataConnection, objJobCardDailyActivity.CreatedBy, "Update", "Job Card", id.ToString(), objJobCardDailyActivity.OrganizationId.ToString()); txn.Commit(); return(id); } catch (Exception ex) { txn.Rollback(); throw ex; } } }
public ActionResult Create(JobCardDailyActivity model) { try { model.CreatedBy = UserID.ToString(); model.OrganizationId = OrganizationId; model.CreatedDate = DateTime.Now; //if (ModelState.IsValid) //{ JobCardDailyActivityRepository repo = new JobCardDailyActivityRepository(); model.CreatedDate = DateTime.Now; int id = 0; if (model.isProjectBased == 0) { id = repo.InsertJobCardDailyActivity(model); } else { id = repo.InsertProjectDailyActivity(model); } if (id == 0) { TempData["error"] = "Some error occured while saving. Please try again."; return(View(model)); } TempData["success"] = "Saved Successfully."; TempData["previousAction"] = "Create"; return(RedirectToAction("Details", new { id = id, type = model.isProjectBased })); //} //else //{ // return View("Create", new { Id = model.JobCardId }); //} } catch (Exception) { TempData["error"] = "Some error occured. Please try again."; return(View(model)); } }
public int InsertProjectDailyActivity(JobCardDailyActivity model) { using (IDbConnection connection = OpenConnection(dataConnection)) { IDbTransaction txn = connection.BeginTransaction(); try { //int _DailyActivityId = 0; model.JobCardDailyActivityRefNo = DatabaseCommonRepository.GetNewDocNo(connection, model.OrganizationId, 38, true, txn); string sql1 = @"SELECT JobCardTaskId FROM JobCardTask WHERE JobCardTaskMasterId = @JobCardTaskMasterId AND TaskDate = CONVERT(VARCHAR, @TaskStartDate, 106) AND JobCardId = @id AND EmployeeId IS NULL", sql2 = @"DELETE FROM JobCardTask WHERE JobCardTaskId IN @id", sql3 = @"INSERT INTO JobCardTask (JobCardId, JobCardTaskMasterId, SlNo, EmployeeId, TaskDate, [Hours], CreatedBy, OrganizationId, isActive) OUTPUT inserted.JobCardTaskId VALUES (@JobCardId, @JobCardTaskMasterId, @SlNo, @EmployeeId, @TaskDate, @Hours, @CreatedBy, @OrganizationId, 1)", sql4 = @"SELECT JobCardTaskId FROM JobCardTask WHERE JobCardTaskMasterId = @JobCardTaskMasterId AND TaskDate = CONVERT(VARCHAR, @TaskStartDate, 106) AND JobCardId = @id AND EmployeeId = @EmployeeId"; model.JobCardDailyActivityId = InsertDailyActivityHead(model, connection, txn); foreach (var item in model.JobCardDailyActivityTask) { if (item.ActualHours == null || item.ActualHours == 0 || item.EmployeeId == 0 || item.JobCardTaskMasterId == 0) { continue; } item.JobCardDailyActivityId = model.JobCardDailyActivityId; List <int> existingTasks = connection.Query <int>(sql1, new { JobCardTaskMasterId = item.JobCardTaskMasterId, TaskStartDate = item.TaskStartDate, id = model.JobCardId }, txn).ToList(); if (existingTasks.Count > 0) { connection.Execute(sql2, new { id = existingTasks }, txn); } item.JobCardTaskId = connection.Query <int>(sql4, new { JobCardTaskMasterId = item.JobCardTaskMasterId, TaskStartDate = item.TaskStartDate, id = model.JobCardId, EmployeeId = item.EmployeeId }, txn).FirstOrDefault(); if (item.JobCardTaskId == 0) { item.JobCardTaskId = connection.Query <int>(sql3, new { JobCardId = model.JobCardId, JobCardTaskMasterId = item.JobCardTaskMasterId, SlNo = 0, //SlNo should be given here EmployeeId = item.EmployeeId, TaskDate = item.TaskStartDate, Hours = item.ActualHours, CreatedBy = model.CreatedBy, OrganizationId = model.OrganizationId }, txn).FirstOrDefault(); if (item.JobCardTaskId <= 0) { throw new Exception("Insertion of task failed."); } InsertDailyActivityTasks(item, connection, txn); } else { InsertDailyActivityTasks(item, connection, txn); } //} //else //{ // InsertDailyActivityTasks(item, connection, txn); //} } InsertLoginHistory(dataConnection, model.CreatedBy, "Create", "DailyActivity", model.JobCardDailyActivityId.ToString(), model.OrganizationId.ToString()); txn.Commit(); return(model.JobCardDailyActivityId); } catch (Exception ex) { txn.Rollback(); throw ex; } } }