public string GetTableData([FromUri] StopVars query)
{
query.SetToEmptyString();
var allStops = _context.StopEntries.Where(s => s.DriverAgeRange != null)
.Where(s => s.DriverGender != null)
.Where(s => s.DriverRace != null)
.Where(s => s.OfficerGender != null)
.Where(s => s.OfficerRace != null)
.Where(s => s.OfficerAgeRange != null)
.Where(s => s.ActivityResults != null)
.Where(s => s.DriverAgeRange != "")
.Where(s => s.DriverGender != "")
.Where(s => s.DriverRace != "")
.Where(s => s.OfficerGender != "")
.Where(s => s.OfficerRace != "")
.Where(s => s.OfficerAgeRange != "")
.Where(s => s.ActivityResults != "")
.ToList();
var shortList = allStops.Take(20);
//string csvString = CsvSerializer.SerializeToCsv<dynamic>(shortList);
string output = new JavaScriptSerializer().Serialize(shortList);
//byte[] csvBytes = System.Text.Encoding.Unicode.GetBytes(csvString);
//return File(csvBytes, "text/csv", "foo.csv");
return(output);
}
public List <double> GetStats([FromUri] StopVars query)
{
List <double> stats = new List <double>();
double ratio = 0;
int allResults = 0;
int activityResults = 0;
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = ConfigurationManager.ConnectionStrings["firstDBString"].ToString();
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM dbo.LMPD_STOPS WHERE OfficerRace LIKE @officerRace AND OfficerAgeRange LIKE @officerAgeRange AND OfficerGender LIKE @officerGender AND DriverRace LIKE @driverRace AND DriverGender LIKE @driverGender AND DriverAgeRange LIKE @driverAgeRange", conn);
cmd.Parameters.Add(new SqlParameter("officerRace", "%" + query.RaceOfficer + "%"));
cmd.Parameters.Add(new SqlParameter("officerAgeRange", "%" + query.AgeOfficer + "%"));
cmd.Parameters.Add(new SqlParameter("officerGender", "%" + query.GenderOfficer + "%"));
cmd.Parameters.Add(new SqlParameter("driverRace", "%" + query.RaceDriver + "%"));
cmd.Parameters.Add(new SqlParameter("driverAgeRange", "%" + query.AgeDriver + "%"));
cmd.Parameters.Add(new SqlParameter("driverGender", "%" + query.GenderDriver + "%"));
//cmd.Parameters.Add(new SqlParameter("activityResults", "%" + "" + "%"));
using (SqlDataReader reader = cmd.ExecuteReader())
{
reader.Read();
allResults = (int)reader[0];
}
SqlCommand cmd2 = new SqlCommand("SELECT COUNT(*) FROM dbo.LMPD_STOPS WHERE OfficerRace LIKE @officerRace AND OfficerAgeRange LIKE @officerAgeRange AND OfficerGender LIKE @officerGender AND DriverRace LIKE @driverRace AND DriverGender LIKE @driverGender AND DriverAgeRange LIKE @driverAgeRange AND ActivityResults LIKE @activityResults", conn);
cmd2.Parameters.Add(new SqlParameter("officerRace", "%" + query.RaceOfficer + "%"));
cmd2.Parameters.Add(new SqlParameter("officerAgeRange", "%" + query.AgeOfficer + "%"));
cmd2.Parameters.Add(new SqlParameter("officerGender", "%" + query.GenderOfficer + "%"));
cmd2.Parameters.Add(new SqlParameter("driverRace", "%" + query.RaceDriver + "%"));
cmd2.Parameters.Add(new SqlParameter("driverAgeRange", "%" + query.AgeDriver + "%"));
cmd2.Parameters.Add(new SqlParameter("driverGender", "%" + query.GenderDriver + "%"));
cmd2.Parameters.Add(new SqlParameter("activityResults", "%" + query.Result + "%"));
using (SqlDataReader reader = cmd2.ExecuteReader())
{
reader.Read();
activityResults = (int)reader[0];
}
ratio = (double)activityResults / (double)allResults;
}
var rounded = Math.Round(ratio, 2);
stats.Add(Math.Round((rounded * 100), 2));
stats.Add(allResults);
stats.Add(activityResults);
return(stats);
}
//public ActionResult Download(StopVars query) {
// query.SetToEmptyString();
// var allStops = _context.StopEntries.Where(s => s.DriverAgeRange != null)
// .Where(s => s.DriverGender != null)
// .Where(s => s.DriverRace != null)
// .Where(s => s.OfficerGender != null)
// .Where(s => s.OfficerRace != null)
// .Where(s => s.OfficerAgeRange != null)
// .Where(s => s.ActivityResults != null)
// .Where(s => s.DriverAgeRange != "")
// .Where(s => s.DriverGender != "")
// .Where(s => s.DriverRace != "")
// .Where(s => s.OfficerGender != "")
// .Where(s => s.OfficerRace != "")
// .Where(s => s.OfficerAgeRange != "")
// .Where(s => s.ActivityResults != "")
// .ToList();
// var queryCountWandC = allStops.Where(s => s.DriverGender.Contains(query.GenderDriver))
// .Where(s => s.OfficerGender.Contains(query.GenderOfficer))
// .Where(s => s.DriverRace.Contains(query.RaceDriver))
// .Where(s => s.OfficerRace.Contains(query.RaceOfficer))
// .Where(s => s.DriverAgeRange.Contains(query.AgeDriver))
// .Where(s => s.OfficerAgeRange.Contains(query.AgeOfficer))
// .Where(s => s.ActivityResults.Contains(""))
// .ToList();
// //var shortList = allStops.Take(20);
// string csvString = CsvSerializer.SerializeToCsv<StopEntry>(queryCountWandC);
// //string output = new JavaScriptSerializer().Serialize(shortList);
// byte[] csvBytes = System.Text.Encoding.Unicode.GetBytes(csvString);
// return File(csvBytes, "text/csv", "foo.csv");
//}
public ActionResult Download(StopVars query)
{
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = ConfigurationManager.ConnectionStrings["firstDBString"].ToString();
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM dbo.LMPD_STOPS WHERE OfficerRace LIKE @officerRace AND OfficerAgeRange LIKE @officerAgeRange AND OfficerGender LIKE @officerGender AND DriverRace LIKE @driverRace AND DriverGender LIKE @driverGender AND DriverAgeRange LIKE @driverAgeRange", conn);
cmd.Parameters.Add(new SqlParameter("officerRace", "%" + query.RaceOfficer + "%"));
cmd.Parameters.Add(new SqlParameter("officerAgeRange", "%" + query.AgeOfficer + "%"));
cmd.Parameters.Add(new SqlParameter("officerGender", "%" + query.GenderOfficer + "%"));
cmd.Parameters.Add(new SqlParameter("driverRace", "%" + query.RaceDriver + "%"));
cmd.Parameters.Add(new SqlParameter("driverAgeRange", "%" + query.AgeDriver + "%"));
cmd.Parameters.Add(new SqlParameter("driverGender", "%" + query.GenderDriver + "%"));
using (SqlDataAdapter sda = new SqlDataAdapter())
{
sda.SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
sda.Fill(dt);
StringBuilder sb = new StringBuilder();
IEnumerable <string> columnNames = dt.Columns.Cast <DataColumn>().
Select(column => column.ColumnName);
sb.AppendLine(string.Join(",", columnNames));
foreach (DataRow row in dt.Rows)
{
IEnumerable <string> fields = row.ItemArray.Select(field => field.ToString());
sb.AppendLine(string.Join(",", fields));
}
byte[] csvBytes = System.Text.Encoding.Unicode.GetBytes(sb.ToString());
return(File(csvBytes, "text/csv", "foo.csv"));
}
}
}
}
