public ActionResult AddJob(vm_Jobs jobAdd)
{
try
{
db.Database.ExecuteSqlCommand("Insert into tbl_Jobs Values({0},{1},{2},{3},{4},{5},{6},{7},{8}, {9})",
jobAdd.jobID, jobAdd.description, jobAdd.customerCode, jobAdd.customerContact, jobAdd.active,
jobAdd.location, jobAdd.nrmlHoursStart, jobAdd.nrmlHoursEnd, jobAdd.dblTimeHours, jobAdd.nrmlHoursDaily);
if (jobAdd.jobSubJobsAdd != null)
{
//add sub-jobs to this job
foreach (string item in jobAdd.jobSubJobsAdd)
{
db.Database.ExecuteSqlCommand("Insert into tbl_jobSubJobs Values({0},{1})",
jobAdd.jobID, item);
}
}
if (jobAdd.resourceTypes != null)
{
//add resource types to this job
foreach (tbl_resourceTypes item in jobAdd.resourceTypes)
{
db.Database.ExecuteSqlCommand(
"INSERT INTO tbl_jobResourceTypes VALUES({0}, {1}, {2}) ",
jobAdd.jobID, item.resourceTypeID, item.rate);
}
}
if (jobAdd.jobCrspdtInfo != null)
{
//add correspondents to this job
foreach (tbl_jobCorrespondents item in jobAdd.jobCrspdtInfo)
{
db.Database.ExecuteSqlCommand(
"INSERT INTO tbl_jobCorrespondents VALUES({0}, {1}, {2}, {3}) ",
//jobAdd.jobID, item.jobCrspdtName, item.jobCrspdtEmail, "1");
jobAdd.jobID, item.name, item.email, "1");
}
}
return(Json(Url.Action("Index", "Jobs")));
}
catch (Exception e)
{
return(Content(e.Message));
}
}
public ActionResult DeleteJob(vm_Jobs jobDelete)
{
try
{
db.Database.ExecuteSqlCommand("DELETE FROM tbl_Jobs WHERE jobID=({0})", jobDelete.jobID);
return(Json(Url.Action("Index", "Jobs")));
}
catch (Exception e)
{
//System.Diagnostics.Debug.WriteLine(e.Message);;
return(Content(e.Message));
}
}
public ActionResult Index()
{
vm_Jobs vm_Jobs = new vm_Jobs();
List <tbl_Jobs> jobList = new List <tbl_Jobs>();
string mainconn = ConfigurationManager.ConnectionStrings["allpaxServiceRecordEntities"].ConnectionString;
SqlConnection sqlconn = new SqlConnection(mainconn);
sqlconn.Open();
string sqlquery1 =
"SELECT tbl_Jobs.jobID, tbl_Jobs.description, tbl_Jobs.customerCode, tbl_Jobs.customerContact, tbl_Jobs.active, tbl_Jobs.location, tbl_Jobs.nrmlHoursStart, " +
"tbl_Jobs.nrmlHoursEnd, tbl_Jobs.nrmlHoursDaily, tbl_Jobs.dblTimeHours, tbl_Jobs.id " +
"FROM tbl_Jobs";
SqlCommand sqlcomm1 = new SqlCommand(sqlquery1, sqlconn);
SqlDataAdapter sda1 = new SqlDataAdapter(sqlcomm1);
DataTable dt1 = new DataTable();
sda1.Fill(dt1);
foreach (DataRow dr1 in dt1.Rows)
{
tbl_Jobs job = new tbl_Jobs();
job.jobID = dr1[0].ToString();
job.description = dr1[1].ToString();
job.customerCode = dr1[2].ToString();
job.customerContact = dr1[3].ToString();
job.active = (Boolean)dr1[4];
job.location = dr1[5].ToString();
job.nrmlHoursStart = dr1[6].ToString();
job.nrmlHoursEnd = dr1[7].ToString();
job.nrmlHoursDaily = (int)dr1[8];
job.dblTimeHours = (Boolean)dr1[9];
job.id = (int)dr1[10];
jobList.Add(job);
}
vm_Jobs.jobs = jobList;
vm_Jobs.customers = customerList();
vm_Jobs.subJobTypes = subJobTypesByJobID("%");
vm_Jobs.resourceTypes = resourceTypesByJobID("%");
sqlconn.Close();
return(View(vm_Jobs));
}
public ActionResult UpdateJob(vm_Jobs jobUpdate)
{
try
{
db.Database.ExecuteSqlCommand(
"UPDATE tbl_Jobs " +
"SET " +
"description = {1}, " +
"customerCode = {2}, " +
"customerContact = {3}, " +
"active = {4}, " +
"nrmlHoursStart = {5}, " +
"nrmlHoursEnd = {6}, " +
"dblTimeHours = {7}, " +
"nrmlHoursDaily = {8} " +
"WHERE jobID = {0}",
jobUpdate.jobID, jobUpdate.description, jobUpdate.customerCode, jobUpdate.customerContact,
jobUpdate.active, jobUpdate.nrmlHoursStart, jobUpdate.nrmlHoursEnd,
jobUpdate.dblTimeHours, jobUpdate.nrmlHoursDaily);
if (jobUpdate.jobSubJobsAdd != null)
{
foreach (string subJobID in jobUpdate.jobSubJobsAdd)
{
//System.Diagnostics.Debug.WriteLine(subJobID);
db.Database.ExecuteSqlCommand(
"INSERT INTO tbl_jobSubJobs(jobID, subJobID) VALUES({0}, {01}) ",
jobUpdate.jobID, subJobID);
}
}
if (jobUpdate.jobSubJobsDelete != null)
{
foreach (string subJobID in jobUpdate.jobSubJobsDelete)
{
db.Database.ExecuteSqlCommand("DELETE FROM tbl_jobSubJobs " +
"WHERE jobID= {0} AND subJobID = {1}", jobUpdate.jobID, subJobID);
}
}
if (jobUpdate.resourceTypesAdd != null)
{
//foreach (string resourceTypeID in jobUpdate.resourceTypesAdd)
//{
// db.Database.ExecuteSqlCommand(
// "INSERT INTO tbl_jobResourceTypes(jobID, resourceTypeID) VALUES({0}, {01}) ",
// jobUpdate.jobID, resourceTypeID);
//}
foreach (tbl_jobResourceTypes item in jobUpdate.resourceTypesAdd)
{
db.Database.ExecuteSqlCommand(
"INSERT INTO tbl_jobResourceTypes VALUES({0}, {1}, {2}) ",
item.jobID, item.resourceTypeID, item.rate);
}
}
if (jobUpdate.resourceTypesDelete != null)
{
foreach (tbl_jobResourceTypes item in jobUpdate.resourceTypesDelete)
{
db.Database.ExecuteSqlCommand("DELETE FROM tbl_jobResourceTypes " +
"WHERE jobID= {0} AND resourceTypeID = {1}", jobUpdate.jobID, item.resourceTypeID);
}
}
if (jobUpdate.resourceTypesEdit != null)
{
foreach (tbl_jobResourceTypes item in jobUpdate.resourceTypesEdit)
{
db.Database.ExecuteSqlCommand("UPDATE tbl_jobResourceTypes " +
"SET " +
"rate = {2} " +
"WHERE jobID= {0} AND resourceTypeID = {1}", jobUpdate.jobID, item.resourceTypeID, item.rate);
}
}
if (jobUpdate.jobCrspdtInfoAdd != null)
{
foreach (tbl_jobCorrespondents item in jobUpdate.jobCrspdtInfoAdd)
{
db.Database.ExecuteSqlCommand(
"INSERT INTO tbl_jobCorrespondents VALUES({0}, {1}, {2}, {3}) ",
jobUpdate.jobID, item.name, item.email, "1");
}
}
if (jobUpdate.jobCrspdtInfoDelete != null)
{
foreach (tbl_jobCorrespondents item in jobUpdate.jobCrspdtInfoDelete)
{
db.Database.ExecuteSqlCommand("DELETE FROM tbl_jobCorrespondents " +
"WHERE jobCorrespondentID= {0} ", item.jobCorrespondentID);
}
}
if (jobUpdate.jobCrspdtInfoEdit != null)
{
foreach (tbl_jobCorrespondents item in jobUpdate.jobCrspdtInfoEdit)
{
db.Database.ExecuteSqlCommand("UPDATE tbl_jobCorrespondents " +
"SET " +
"name = {1}, " +
"email = {2} " +
"WHERE jobCorrespondentID= {0} ", item.jobCorrespondentID, item.name, item.email);
}
}
return(Json(Url.Action("Index", "Jobs")));
}
catch (Exception e)
{
return(Content(e.Message));
}
}