public ActionResult Index()
{
List <vm_dailyReportViewAll> dailyRptViewAlls = new List <vm_dailyReportViewAll>();
string mainconn = ConfigurationManager.ConnectionStrings["allpaxServiceRecordEntities"].ConnectionString;
SqlConnection sqlconn = new SqlConnection(mainconn);
sqlconn.Open();
string sqlquery1 =
"SELECT tbl_dailyReport.dailyReportID, tbl_Jobs.active, tbl_dailyReport.date, tbl_dailyReport.jobID, tbl_dailyReport.subJobID, tbl_subJobTypes.description, " +
"tbl_customers.customerName, tbl_customers.address, tbl_customers.customerCode, tbl_jobs.customerContact, tbl_dailyReport.dailyReportAuthor, tbl_dailyReport.equipment " +
"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";
SqlCommand sqlcomm1 = new SqlCommand(sqlquery1, sqlconn);
SqlDataAdapter sda1 = new SqlDataAdapter(sqlcomm1);
DataTable dt1 = new DataTable();
sda1.Fill(dt1);
foreach (DataRow dr1 in dt1.Rows)
{
vm_dailyReportViewAll dailyRptViewAll = new vm_dailyReportViewAll();
dailyRptViewAll.dailyReportID = (int)dr1[0];
dailyRptViewAll.active = (Boolean)dr1[1];
dailyRptViewAll.date = String.Format("{0:yyyy-MM-dd}", dr1[2]);
dailyRptViewAll.jobID = dr1[3].ToString();
dailyRptViewAll.subJobID = dr1[4].ToString();
dailyRptViewAll.description = dr1[5].ToString();
dailyRptViewAll.customerName = dr1[6].ToString();
dailyRptViewAll.address = dr1[7].ToString();
dailyRptViewAll.customercode = dr1[8].ToString();
dailyRptViewAll.customerContact = dr1[9].ToString();
dailyRptViewAll.teamUserNames = TeamUserNamesByDailyReportID(dailyRptViewAll.dailyReportID);
dailyRptViewAll.teamNames = TeamNamesByDailyReportID(dailyRptViewAll.dailyReportID);
dailyRptViewAll.workDescription = WorkDescsByDailyReportID(dailyRptViewAll.dailyReportID);
dailyRptViewAll.dailyReportAuthor = dr1[10].ToString();
dailyRptViewAll.equipment = dr1[11].ToString();
dailyRptViewAlls.Add(dailyRptViewAll);
}
sqlconn.Close();
return(View(dailyRptViewAlls));
}
public ActionResult Filtered(string startDate, string endDate)
{
List <vm_dailyReportViewAll> dailyRptViewAlls = new List <vm_dailyReportViewAll>();
string mainconn = ConfigurationManager.ConnectionStrings["allpaxServiceRecordEntities"].ConnectionString;
SqlConnection sqlconn = new SqlConnection(mainconn);
sqlconn.Open();
string sqlquery1 =
"SELECT tbl_dailyReport.dailyReportID, tbl_Jobs.active, tbl_dailyReport.date, tbl_dailyReport.jobID, tbl_dailyReport.subJobID, " +
"tbl_subJobTypes.description, tbl_customers.customerName, tbl_customers.address, tbl_customers.customerCode, tbl_jobs.customerContact, 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.date >= @startDate " +
"AND " +
"tbl_dailyReport.date <= @endDate";
SqlCommand sqlcomm1 = new SqlCommand(sqlquery1, sqlconn);
sqlcomm1.Parameters.AddWithValue("@startDate", startDate);
sqlcomm1.Parameters.AddWithValue("@endDate", endDate);
SqlDataAdapter sda1 = new SqlDataAdapter(sqlcomm1);
DataTable dt1 = new DataTable();
sda1.Fill(dt1);
foreach (DataRow dr1 in dt1.Rows)
{
vm_dailyReportViewAll dailyRptViewAll = new vm_dailyReportViewAll();
bool userInReport;
dailyRptViewAll.dailyReportID = (int)dr1[0];
dailyRptViewAll.active = (Boolean)dr1[1];
dailyRptViewAll.date = String.Format("{0:yyyy-MM-dd}", dr1[2]);
dailyRptViewAll.jobID = dr1[3].ToString();
dailyRptViewAll.subJobID = dr1[4].ToString();
dailyRptViewAll.description = dr1[5].ToString();
dailyRptViewAll.customerName = dr1[6].ToString();
dailyRptViewAll.address = dr1[7].ToString();
dailyRptViewAll.customercode = dr1[8].ToString();
dailyRptViewAll.customerContact = dr1[9].ToString();
dailyRptViewAll.teamUserNames = TeamUserNamesByDailyReportID(dailyRptViewAll.dailyReportID);
dailyRptViewAll.teamNames = TeamNamesByDailyReportID(dailyRptViewAll.dailyReportID);
dailyRptViewAll.teamShortNames = TeamShortNamesByDailyReportID(dailyRptViewAll.dailyReportID);
dailyRptViewAll.workDescription = WorkDescsByDailyReportID(dailyRptViewAll.dailyReportID);
dailyRptViewAll.dailyReportAuthor = dr1[10].ToString();
dailyRptViewAll.submissionStatus = (int)dr1[11];
userInReport = dailyRptViewAll.teamUserNames.Contains(User.Identity.GetUserName());
if (User.IsInRole("Admin"))
{
//System.Diagnostics.Debug.WriteLine("user is an admin.");
dailyRptViewAlls.Add(dailyRptViewAll);
}
if (!User.IsInRole("Admin") && (userInReport) && (dailyRptViewAll.active))
{
//System.Diagnostics.Debug.WriteLine("user not an admin and part of an active report.");
dailyRptViewAlls.Add(dailyRptViewAll);
}
}
sqlconn.Close();
return(View(dailyRptViewAlls));
}
