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