public IActionResult MIS(string type) { MISReportViewModel _model = new MISReportViewModel(); List <SummaryTestType> _SummaryTestTypes = new List <SummaryTestType>(); _model.Companies = GetCompanies(); _model.SummaryTestTypes = _SummaryTestTypes; _model.Details = new List <MISDetails>(); _model.Totals = new List <SummaryTestType>(); return(View(_model)); }
public IActionResult MIS(MISReportViewModel model) { // Call Stored Procedure // exec [dbo].[MISREPORT] 1,'Drug','20000101','20211231' List <SummaryTestType> _list = new List <SummaryTestType>(); List <SummaryTestType> _totalsList = new List <SummaryTestType>(); List <MISDetails> _details = new List <MISDetails>(); DateTime startDate = model.ReportRequest.StartDate; DateTime endDate = model.ReportRequest.EndDate; // Use Company in Details Search bool useCompany = model.ReportRequest.CompanyID == 0 ? false : true; // Get Totals int total_Active_Enrolled_Drivers = 0; int total_Selected_Drivers = 0; int total_Excused_Drivers = 0; int total_Positive_Tested_Drivers = 0; int total_Negative_Tested_Drivers = 0; int row_count = 0; // Get Companies model.Companies = GetCompanies(); // string _query = "Select b.RunDate as 'Random Test Selection',b.Id as 'Batch Number',b.Eligible_Drivers as 'Active Enrolled Drivers',(b.Drug_Tests + b.Alcohol_Tests) as 'Selected Drivers',(Select count([Batch_Id]) from[dbo].[TestingLogs] WHERE Batch_Id = b.Id AND Reported_Results = 3) as 'Excused Drivers',(Select count([Batch_Id]) from[dbo].[TestingLogs] WHERE Batch_Id = b.Id AND Reported_Results = 1) as 'Positive Drivers',(Select count([Batch_Id]) from[dbo].[TestingLogs] WHERE Batch_Id = b.Id AND Reported_Results = 2) as 'Negative Drivers',(Select count([Batch_Id]) from[dbo].[TestingLogs] WHERE Batch_Id = b.Id AND Reported_Results in (1, 2, 3)) as 'Total',CAST((CAST((Select count([Batch_Id]) from[dbo].[TestingLogs] WHERE Batch_Id = b.Id AND Reported_Results in (1, 2, 3)) AS float) / CAST((b.Drug_Tests + b.Alcohol_Tests) AS float)) AS float) as 'Selection Test Ratio', * from Batches b"; using (SqlConnection conn = new SqlConnection("Server=tcp:nfssql01.database.windows.net,1433;Initial Catalog=NFS_SQL_01;Persist Security Info=False;User ID=nfsadmin;Password=IFTAtaxes2017;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;")) { conn.Open(); SqlCommand cmd = new SqlCommand("[dbo].[MISREPORT]", conn); // 2. set the command object so it knows to execute a stored procedure cmd.CommandType = CommandType.StoredProcedure; // 3. add parameter to command, which will be passed to the stored procedure // cmd.Parameters.Add(new SqlParameter("@CompanyID", 1)); cmd.Parameters.Add(new SqlParameter("@TestType", model.ReportRequest.TestType)); cmd.Parameters.Add(new SqlParameter("@StartDate", startDate.ToString("yyyyMMdd"))); cmd.Parameters.Add(new SqlParameter("@EndDate", endDate.ToString("yyyyMMdd"))); // execute the command using (SqlDataReader rdr = cmd.ExecuteReader()) { // iterate through results, printing each to console while (rdr.Read()) { var test = rdr.GetValue(0); var test2 = rdr.GetValue("Selected Drivers"); if (rdr != null) { int temp_Active_Enrolled_Drivers = Convert.ToInt32(rdr.GetValue("Active Enrolled Drivers")); int temp_Selected_Drivers = Convert.ToInt32(rdr.GetValue("Selected Drivers")); int temp_Excused_Drivers = Convert.ToInt32(rdr.GetValue("Excused Drivers")); int temp_Positive_Tested_Drivers = Convert.ToInt32(rdr.GetValue("Positive Drivers")); int temp_Negative_Tested_Drivers = Convert.ToInt32(rdr.GetValue("Negative Drivers")); double _positive = Convert.ToDouble(temp_Positive_Tested_Drivers); double _negative = Convert.ToDouble(temp_Negative_Tested_Drivers); double _enrolled = Convert.ToDouble(temp_Active_Enrolled_Drivers); double _allResults = _positive + _negative; double temp_Annual_Ratio = Convert.ToDouble(_allResults / _enrolled) * 100; total_Active_Enrolled_Drivers += temp_Active_Enrolled_Drivers; total_Selected_Drivers += temp_Selected_Drivers; total_Excused_Drivers += temp_Excused_Drivers; total_Positive_Tested_Drivers += temp_Positive_Tested_Drivers; total_Negative_Tested_Drivers += temp_Negative_Tested_Drivers; row_count += 1; SummaryTestType row = new SummaryTestType { Description = GetValue <string>(rdr.GetValue("Random Test Selection")), Random_Test_Selection = GetValue <DateTime>(rdr.GetValue("Random Test Selection")), Batch_Number = GetValue <int>(rdr.GetValue("Batch Number")), Active_Enrolled_Drivers = temp_Active_Enrolled_Drivers, Selected_Drivers = temp_Selected_Drivers, Excused_Drivers = temp_Excused_Drivers, Positive_Tested_Drivers = temp_Positive_Tested_Drivers, Negative_Tested_Drivers = temp_Negative_Tested_Drivers, Selection_Test_Ratio = Convert.ToDouble(rdr.GetValue("Selection Test Ratio").ToString()), // Annual_Ratio = Convert.ToDouble(rdr.GetValue("Annual Ratio").ToString()) Annual_Ratio = temp_Annual_Ratio }; _list.Add(row); } } // Add Totals Row double _positiveTotal = Convert.ToDouble(total_Positive_Tested_Drivers); double _negativeTotal = Convert.ToDouble(total_Negative_Tested_Drivers); double _enrolledTotal = Convert.ToDouble(total_Active_Enrolled_Drivers / row_count); double _allResultsTotal = _positiveTotal + _negativeTotal; double temp_Annual_RatioTotal = Convert.ToDouble(_allResultsTotal / _enrolledTotal) * 100; SummaryTestType _totals = new SummaryTestType { Description = "Totals", Active_Enrolled_Drivers = Convert.ToInt32(total_Active_Enrolled_Drivers / row_count), Selected_Drivers = total_Selected_Drivers, Excused_Drivers = total_Excused_Drivers, Positive_Tested_Drivers = total_Positive_Tested_Drivers, Negative_Tested_Drivers = total_Negative_Tested_Drivers, Annual_Ratio = temp_Annual_RatioTotal }; _totalsList.Add(_totals); _list.Add(_totals); model.Totals = _totalsList; model.SummaryTestTypes = _list; } // Determine if you need details if (model.ReportRequest.IncludeDriverDetails == true) { if (useCompany == false) { // GET DETAILS cmd = new SqlCommand("[dbo].[MISREPORT_DETAILS]", conn); } else { // GET DETAILS WITH COMPANY cmd = new SqlCommand("[dbo].[MISREPORT_DETAILS_BY_COMPANY]", conn); } // 2. set the command object so it knows to execute a stored procedure cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@TestType", model.ReportRequest.TestType)); cmd.Parameters.Add(new SqlParameter("@StartDate", startDate.ToString("yyyyMMdd"))); cmd.Parameters.Add(new SqlParameter("@EndDate", endDate.ToString("yyyyMMdd"))); if (useCompany == true) { // GET DETAILS cmd.Parameters.Add(new SqlParameter("@Company", model.ReportRequest.CompanyID)); } // execute the command using (SqlDataReader rdr = cmd.ExecuteReader()) { // iterate through results, printing each to console while (rdr.Read()) { if (rdr != null) { MISDetails row = new MISDetails { Random_Test_Selection_Date = GetValue <DateTime>(rdr.GetValue("Random_Test_Selection_Date")), Batch_Number = GetValue <int>(rdr.GetValue("Batch_Number")), Driver_Name = GetValue <string>(rdr.GetValue("Driver_Name")), Company_Name = GetValue <string>(rdr.GetValue("Name")), Test_Type = GetValue <string>(rdr.GetValue("Test_Type")), Result = GetValue <string>(rdr.GetValue("Result")), ResultsDate = GetValue <DateTime>(rdr.GetValue("ResultsDate")) }; _details.Add(row); } } model.Details = _details; if (!model.ReportRequest.IncludeAllDriverDetails) { model.Details = _details.Where(d => d.Result == "Positive").ToList(); } } // END GET DETAILS } else { // Add blank set of details model.Details = _details; } } if (ModelState.IsValid) { //_context.Add(createBatch); //await _context.SaveChangesAsync(); //return RedirectToAction(nameof(Index)); } return(View(model)); }