//DELETE A TIME ENTRY
public ActionResult DeleteTimeEntry(vm_dailyReportByReportID timeEntryDelete)
{
if (timeEntryDelete.timeEntryDeleteArr.Count >= 1)
{
//System.Diagnostics.Debug.WriteLine("size of timeEntryDeleteArr is " + timeEntryDelete.timeEntryDeleteArr.Count);
foreach (string item in timeEntryDelete.timeEntryDeleteArr)
{
//System.Diagnostics.Debug.WriteLine(item);
db.Database.ExecuteSqlCommand("DELETE FROM tbl_dailyReportTimeEntry WHERE timeEntryID=({0})", item);
}
}
//return new EmptyResult();
return(Json(Url.Action("Index", "dailyReportAll")));
}
public ActionResult Index(int reportID)
{
ViewBag.reportID = reportID;
List <vm_dailyReportByReportID> dailyReportByID = new List <vm_dailyReportByReportID>();
string mainconn = ConfigurationManager.ConnectionStrings["allpaxServiceRecordEntities"].ConnectionString;
SqlConnection sqlconn = new SqlConnection(mainconn);
sqlconn.Open();
string sqlquery1 =
"SELECT tbl_dailyReport.dailyReportID, tbl_dailyReport.jobID, tbl_subJobTypes.description, tbl_dailyReport.date, " +
"tbl_Jobs.customerContact,tbl_customers.customerName, tbl_customers.address, tbl_dailyReport.equipment, " +
"tbl_dailyReport.startTime, tbl_dailyReport.endTime, tbl_dailyReport.lunchHours, tbl_customers.customerCode, tbl_dailyReport.dailyReportAuthor " +
"FROM tbl_dailyReport " +
"INNER JOIN " +
"tbl_Jobs ON tbl_Jobs.jobID = tbl_dailyReport.jobID " +
"INNER JOIN " +
"tbl_customers ON tbl_customers.customerCode = tbl_Jobs.customerCode " +
"INNER JOIN " +
"tbl_jobSubJobs ON tbl_jobSubJobs.jobID = tbl_Jobs.jobID " +
"INNER JOIN " +
"tbl_subJobTypes ON tbl_subJobTypes.subJobID = tbl_jobSubJobs.subJobID " +
"WHERE " +
"tbl_dailyReport.subJobID = tbl_subJobTypes.subJobID " +
"AND " +
"tbl_dailyReport.dailyReportID LIKE @reportID";
SqlCommand sqlcomm1 = new SqlCommand(sqlquery1, sqlconn);
sqlcomm1.Parameters.AddWithValue("@reportID", reportID);
SqlDataAdapter sda1 = new SqlDataAdapter(sqlcomm1);
DataTable dt1 = new DataTable();
sda1.Fill(dt1);
foreach (DataRow dr1 in dt1.Rows)
{
vm_dailyReportByReportID vm_dailyReportByReportID = new vm_dailyReportByReportID();
vm_dailyReportByReportID.dailyReportID = (int)dr1[0];
vm_dailyReportByReportID.jobID = dr1[1].ToString();
vm_dailyReportByReportID.description = dr1[2].ToString();
vm_dailyReportByReportID.date = String.Format("{0:yyyy-MM-dd}", dr1[3]);
vm_dailyReportByReportID.customerContact = dr1[4].ToString();
vm_dailyReportByReportID.customerName = dr1[5].ToString();
vm_dailyReportByReportID.address = dr1[6].ToString();
vm_dailyReportByReportID.equipment = dr1[7].ToString();
vm_dailyReportByReportID.startTime = dr1[8].ToString();
vm_dailyReportByReportID.endTime = dr1[9].ToString();
vm_dailyReportByReportID.lunchHours = (int)dr1[10];
vm_dailyReportByReportID.customerCode = dr1[11].ToString();
vm_dailyReportByReportID.jobCorrespondentName = jobCrspdtNameByJobID(vm_dailyReportByReportID.jobID);
vm_dailyReportByReportID.jobCorrespondentEmail = jobCrspdtEmailByJobID(vm_dailyReportByReportID.jobID);
vm_dailyReportByReportID.dailyReportAuthor = dr1[12].ToString();
dailyReportByID.Add(vm_dailyReportByReportID);
}
sqlconn.Close();
//end query
return(View(dailyReportByID));
}
public ActionResult Index(int reportID)
{
ViewBag.reportID = reportID;
List <vm_dailyReportByReportID> dailyReportByID = new List <vm_dailyReportByReportID>();
List <string> recipientList = new List <string>();
string mainconn = ConfigurationManager.ConnectionStrings["allpaxServiceRecordEntities"].ConnectionString;
SqlConnection sqlconn = new SqlConnection(mainconn);
sqlconn.Open();
string sqlquery1 =
"SELECT tbl_dailyReport.dailyReportID, tbl_dailyReport.jobID, tbl_subJobTypes.description, tbl_dailyReport.date, " +
"tbl_Jobs.customerContact,tbl_customers.customerName, tbl_customers.address, tbl_dailyReport.equipment, " +
"tbl_dailyReport.startTime, tbl_dailyReport.endTime, tbl_dailyReport.lunchHours, tbl_customers.customerCode " +
"FROM tbl_dailyReport " +
"INNER JOIN " +
"tbl_Jobs ON tbl_Jobs.jobID = tbl_dailyReport.jobID " +
"INNER JOIN " +
"tbl_customers ON tbl_customers.customerCode = tbl_Jobs.customerCode " +
"INNER JOIN " +
"tbl_jobSubJobs ON tbl_jobSubJobs.jobID = tbl_Jobs.jobID " +
"INNER JOIN " +
"tbl_subJobTypes ON tbl_subJobTypes.subJobID = tbl_jobSubJobs.subJobID " +
"WHERE " +
"tbl_dailyReport.subJobID = tbl_subJobTypes.subJobID " +
"AND " +
"tbl_dailyReport.dailyReportID LIKE @reportID";
SqlCommand sqlcomm1 = new SqlCommand(sqlquery1, sqlconn);
sqlcomm1.Parameters.AddWithValue("@reportID", reportID);
SqlDataAdapter sda1 = new SqlDataAdapter(sqlcomm1);
DataTable dt1 = new DataTable();
sda1.Fill(dt1);
foreach (DataRow dr1 in dt1.Rows)
{
vm_dailyReportByReportID vm_dailyReportByReportID = new vm_dailyReportByReportID();
vm_dailyReportByReportID.dailyReportID = (int)dr1[0];
vm_dailyReportByReportID.jobID = dr1[1].ToString();
vm_dailyReportByReportID.description = dr1[2].ToString();
vm_dailyReportByReportID.date = String.Format("{0:yyyy-MM-dd}", dr1[3]);
vm_dailyReportByReportID.customerContact = dr1[4].ToString();
vm_dailyReportByReportID.customerName = dr1[5].ToString();
vm_dailyReportByReportID.address = dr1[6].ToString();
vm_dailyReportByReportID.equipment = dr1[7].ToString();
vm_dailyReportByReportID.startTime = dr1[8].ToString();
vm_dailyReportByReportID.endTime = dr1[9].ToString();
vm_dailyReportByReportID.lunchHours = (int)dr1[10];
vm_dailyReportByReportID.customerCode = dr1[11].ToString();
vm_dailyReportByReportID.names = namesByTimeEntryID(vm_dailyReportByReportID.dailyReportID);
vm_dailyReportByReportID.shortNames = shortNamesByTimeEntryID(vm_dailyReportByReportID.dailyReportID);
vm_dailyReportByReportID.jobCorrespondentName = jobCrspdtNameByJobID(vm_dailyReportByReportID.jobID);
vm_dailyReportByReportID.jobCorrespondentEmail = jobCorrespondentEmailByTimeEntryID(vm_dailyReportByReportID.jobID);
recipientList = vm_dailyReportByReportID.jobCorrespondentEmail;
dailyReportByID.Add(vm_dailyReportByReportID);
}
sqlconn.Close();
string recipientListStr = string.Join(", ", recipientList);
string html = ViewRenderer.RenderView("~/views/dailyReportByReportIDPrint/index.cshtml", dailyReportByID);
SmtpClient smtp = new SmtpClient();
smtp.Host = "smtp.gmail.com";
smtp.Port = 587;
smtp.EnableSsl = true;
smtp.DeliveryMethod = SmtpDeliveryMethod.Network;
smtp.UseDefaultCredentials = false;
smtp.Credentials = new NetworkCredential("*****@*****.**", "Allpax_1234");
string body = html;
using (var message = new MailMessage("*****@*****.**", recipientListStr))
{
message.Subject = "Test";
message.Body = body;
message.IsBodyHtml = true;
smtp.Send(message);
}
return(View(dailyReportByID));
}
public ActionResult UpdateDailyReportTimeEntries(vm_dailyReportByReportID dailyReportTimeEntryUpdate)
{
string cs = ConfigurationManager.ConnectionStrings["allpaxServiceRecordEntities"].ConnectionString;
int passedDailyRptID = dailyReportTimeEntryUpdate.dailyReportID;
if (dailyReportTimeEntryUpdate.workDescArr != null)
{
foreach (vm_workDesc item in dailyReportTimeEntryUpdate.workDescArr)
{
db.Database.ExecuteSqlCommand(
"UPDATE tbl_dailyReportTimeEntry " +
"SET " +
"workDescription = {1}, " +
"hours = {3} " +
"WHERE " +
"timeEntryID = {0}",
item.timeEntryID, item.workDescription, item.workDescriptionCategory, item.hours);
if (item.userNamesToAdd != null)
{
foreach (string userNames in item.userNamesToAdd)
{
db.Database.ExecuteSqlCommand(
"INSERT INTO tbl_dailyReportTimeEntryUsers(timeEntryID, userName) VALUES({0}, {1}) ",
item.timeEntryID, userNames);
}
}
if (item.userNamesToDelete != null)
{
foreach (string userNames in item.userNamesToDelete)
{
db.Database.ExecuteSqlCommand(
"DELETE FROM tbl_dailyReportTimeEntryUsers " +
"WHERE " +
"timeEntryID=({0}) AND userName = ({1})",
item.timeEntryID, userNames);
}
}
}
}
if (dailyReportTimeEntryUpdate.delaysArr != null)
{
foreach (vm_delays item in dailyReportTimeEntryUpdate.delaysArr)
{
db.Database.ExecuteSqlCommand(
"UPDATE tbl_dailyReportTimeEntry " +
"SET " +
"workDescription = {1}, " +
"hours = {3} " +
"WHERE " +
"timeEntryID = {0}",
item.timeEntryID, item.workDescription, item.workDescriptionCategory, item.hours);
if (item.userNamesToAdd != null)
{
foreach (string userNames in item.userNamesToAdd)
{
db.Database.ExecuteSqlCommand(
"INSERT INTO tbl_dailyReportTimeEntryUsers(timeEntryID, userName) VALUES({0}, {1}) ",
item.timeEntryID, userNames);
}
}
if (item.userNamesToDelete != null)
{
foreach (string userNames in item.userNamesToDelete)
{
db.Database.ExecuteSqlCommand(
"DELETE FROM tbl_dailyReportTimeEntryUsers " +
"WHERE " +
"timeEntryID=({0}) AND userName = ({1})",
item.timeEntryID, userNames);
}
}
}
}
if (dailyReportTimeEntryUpdate.wntyDelaysArr != null)
{
foreach (vm_wntyDelays item in dailyReportTimeEntryUpdate.wntyDelaysArr)
{
db.Database.ExecuteSqlCommand(
"UPDATE tbl_dailyReportTimeEntry " +
"SET " +
"workDescription = {1}, " +
"hours = {3} " +
"WHERE " +
"timeEntryID = {0}",
item.timeEntryID, item.workDescription, item.workDescriptionCategory, item.hours);
if (item.userNamesToAdd != null)
{
foreach (string userNames in item.userNamesToAdd)
{
db.Database.ExecuteSqlCommand(
"INSERT INTO tbl_dailyReportTimeEntryUsers(timeEntryID, userName) VALUES({0}, {1}) ",
item.timeEntryID, userNames);
}
}
if (item.userNamesToDelete != null)
{
foreach (string userNames in item.userNamesToDelete)
{
db.Database.ExecuteSqlCommand(
"DELETE FROM tbl_dailyReportTimeEntryUsers " +
"WHERE " +
"timeEntryID=({0}) AND userName = ({1})",
item.timeEntryID, userNames);
}
}
}
}
//return new EmptyResult();
//return RedirectToAction("SalesLanding", "Index");
return(Json(Url.Action("Index", "dailyReportAll")));
}
public ActionResult copyDailyReport(int reportID)
{
ViewBag.reportID = reportID;
List <vm_dailyReportByReportID> dailyReportByID = new List <vm_dailyReportByReportID>();
string mainconn = ConfigurationManager.ConnectionStrings["allpaxServiceRecordEntities"].ConnectionString;
SqlConnection sqlconn = new SqlConnection(mainconn);
sqlconn.Open();
string sqlquery1 =
"SELECT tbl_dailyReport.dailyReportID, tbl_dailyReport.jobID, tbl_dailyReport.subJobID, tbl_subJobTypes.description, tbl_dailyReport.date, " +
"tbl_Jobs.customerContact,tbl_customers.customerName, tbl_customers.address, tbl_dailyReport.equipment, tbl_dailyReport.startTime, " +
"tbl_dailyReport.endTime, tbl_dailyReport.lunchHours, tbl_customers.customerCode, tbl_dailyReport.dailyReportAuthor, tbl_dailyReport.submissionStatus " +
"FROM tbl_dailyReport " +
"INNER JOIN " +
"tbl_Jobs ON tbl_Jobs.jobID = tbl_dailyReport.jobID " +
"INNER JOIN " +
"tbl_customers ON tbl_customers.customerCode = tbl_Jobs.customerCode " +
"INNER JOIN " +
"tbl_jobSubJobs ON tbl_jobSubJobs.jobID = tbl_Jobs.jobID " +
"INNER JOIN " +
"tbl_subJobTypes ON tbl_subJobTypes.subJobID = tbl_jobSubJobs.subJobID " +
"WHERE " +
"tbl_dailyReport.subJobID = tbl_subJobTypes.subJobID " +
"AND " +
"tbl_dailyReport.dailyReportID LIKE @reportID";
SqlCommand sqlcomm1 = new SqlCommand(sqlquery1, sqlconn);
sqlcomm1.Parameters.AddWithValue("@reportID", reportID);
SqlDataAdapter sda1 = new SqlDataAdapter(sqlcomm1);
DataTable dt1 = new DataTable();
sda1.Fill(dt1);
foreach (DataRow dr1 in dt1.Rows)
{
vm_dailyReportByReportID vm_dailyReportByReportID = new vm_dailyReportByReportID();
vm_dailyReportByReportID.dailyReportID = (int)dr1[0];
vm_dailyReportByReportID.jobID = dr1[1].ToString();
vm_dailyReportByReportID.subJobID = dr1[2].ToString();
vm_dailyReportByReportID.description = dr1[3].ToString();
vm_dailyReportByReportID.date = String.Format("{0:yyyy-MM-dd}", dr1[4]);
vm_dailyReportByReportID.customerContact = dr1[5].ToString();
vm_dailyReportByReportID.customerName = dr1[6].ToString();
vm_dailyReportByReportID.address = dr1[7].ToString();
vm_dailyReportByReportID.equipment = dr1[8].ToString();
vm_dailyReportByReportID.startTime = dr1[9].ToString();
vm_dailyReportByReportID.endTime = dr1[10].ToString();
vm_dailyReportByReportID.lunchHours = (int)dr1[11];
vm_dailyReportByReportID.customerCode = dr1[12].ToString();
//GET JOB CORRESPONDENT NAME RECORDS FOR THE DAILY REPORT
vm_dailyReportByReportID.jobCorrespondentName = jobCrspdtNameByJobID(vm_dailyReportByReportID.jobID);
//GET JOB CORRESPONDENT EMAIL ADDRESS RECORDS FOR THE DAILY REPORT
vm_dailyReportByReportID.jobCorrespondentEmail = jobCrspdtEmailByJobID(vm_dailyReportByReportID.jobID);
//
vm_dailyReportByReportID.dailyReportAuthor = dr1[13].ToString();
vm_dailyReportByReportID.submissionStatus = (int)dr1[14];
//GET WORK DESCRIPTION RECORDS FOR THE DAILY REPORT
vm_dailyReportByReportID.workDescArr = getWorkDescRecords(vm_dailyReportByReportID.dailyReportID);
//GET DELAYS RECORDS FOR THE DAILY REPORT
vm_dailyReportByReportID.delaysArr = getDelaysRecords(vm_dailyReportByReportID.dailyReportID);
//GET WARRANTY DELAYS RECORDS FOR THE DAILY REPORT
vm_dailyReportByReportID.wntyDelaysArr = getWntyDelaysRecords(vm_dailyReportByReportID.dailyReportID);
dailyReportByID.Add(vm_dailyReportByReportID);//add all of the revelevant data objects to dailyReportByID...
}
sqlconn.Close();
return(View(dailyReportByID));//...to be passed to the view
//return RedirectToAction("Index", "dailyReportAll");//redirects, and the daily report does kick out the records to its view
}
