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;
}
}
}
