public void OnGet() { List<Models.Crimes> crimes = new List<Models.Crimes>(); AllCrimes allCrimes = new AllCrimes(); double totalCrimes = allCrimes.GetAllCrimes(); // clear exception: EX = null; try { string sql = string.Format(@" SELECT TOP 10 A.IUCR, A.PrimaryDesc + ' ' + A.SecondaryDesc description, A.Total_Crimes,B.Total_Arrested FROM (SELECT CO.IUCR, CO.PrimaryDesc, CO.SecondaryDesc, COUNT(*) Total_Crimes FROM dbo.Codes CO RIGHT JOIN dbo.Crimes CR ON CO.IUCR = CR.IUCR GROUP BY CO.IUCR, CO.PrimaryDesc, CO.SecondaryDesc) A JOIN (SELECT CO.IUCR, CO.PrimaryDesc, CO.SecondaryDesc, COUNT(*) Total_Arrested FROM dbo.Codes CO RIGHT JOIN dbo.Crimes CR ON CO.IUCR = CR.IUCR WHERE Arrested = 1 GROUP BY CO.IUCR, CO.PrimaryDesc, CO.SecondaryDesc) B ON B.IUCR = A.IUCR ORDER BY A.Total_Crimes DESC "); DataSet ds = DataAccessTier.DB.ExecuteNonScalarQuery(sql); foreach (DataRow row in ds.Tables["TABLE"].Rows) { Models.Crimes c = new Models.Crimes(); c.IUCR = Convert.ToString(row["IUCR"]); c.description = Convert.ToString(row["description"]); c.totalCrimes = Convert.ToInt32(row["Total_Crimes"]); c.percentTotal = Math.Round(((c.totalCrimes / totalCrimes) * 100.00),2); double arrested = Convert.ToDouble(row["Total_Arrested"]); c.percentArrested = Math.Round(((arrested / c.totalCrimes) * 100.00),2); crimes.Add(c); } } catch(Exception ex) { EX = ex; } finally { CrimesList = crimes; } }
public void OnGet() { List<Models.Crimes> crimes = new List<Models.Crimes>(); // clear exception: EX = null; try { string sql = string.Format(@" SELECT TOP 10 Codes.IUCR, PrimaryDesc, SecondaryDesc, Count(Crimes.IUCR) AS NumCrimes, ROUND(( CONVERT(float,(Count(Crimes.IUCR))) / MAX(CID) * 100), 2) AS PercentCrime, ROUND((SUM(CONVERT(float, Arrested)) / Count(Crimes.IUCR) * 100.0), 2) AS ArrestPercent FROM Crimes INNER JOIN Codes ON Crimes.IUCR = Codes.IUCR GROUP BY Codes.IUCR, PrimaryDesc, SecondaryDesc ORDER BY NumCrimes DESC, IUCR ASC; "); DataSet ds = DataAccessTier.DB.ExecuteNonScalarQuery(sql); foreach (DataRow row in ds.Tables["TABLE"].Rows) { Models.Crimes m = new Models.Crimes(); m.IUCR = Convert.ToString(row["IUCR"]); m.PrimaryDesc = Convert.ToString(row["PrimaryDesc"]); m.SecondaryDesc = Convert.ToString(row["SecondaryDesc"]); m.NumCrimes = Convert.ToInt32(row["NumCrimes"]); m.PercentCrime = Convert.ToSingle(row["PercentCrime"]); m.ArrestPercent = Convert.ToSingle(row["ArrestPercent"]); crimes.Add(m); } } catch(Exception ex) { EX = ex; } finally { CrimesList = crimes; } }
public void OnGet(string input) { List <Models.Crimes> crimes = new List <Models.Crimes>(); // make input available to web page: Input = input; // clear exception: EX = null; try { // // Do we have an input argument? If so, we do a lookup: // if (input == null) { // // there's no page argument, perhaps user surfed to the page directly? // In this case, nothing to do. // } else { // // Lookup movie(s) based on input, which could be id or a partial name: // int id; string sql; if (System.Int32.TryParse(input, out id)) { // lookup movie by movie id: sql = string.Format(@" DECLARE @total AS FLOAT; SET @total = (SELECT COUNT(Crimes.IUCR) FROM Crimes WHERE Area = {0}); SELECT TOP 10 Codes.IUCR, PrimaryDesc, SecondaryDesc, Count(Crimes.IUCR) AS NumCrimes, ROUND( ( (Count(Crimes.IUCR) ) / @total ) * 100 , 2) AS PercentCrime, ROUND((SUM(CONVERT(float, Arrested)) / Count(Crimes.IUCR) * 100.0), 2) AS ArrestPercent FROM Crimes INNER JOIN Codes ON Crimes.IUCR = Codes.IUCR INNER JOIN Areas ON Crimes.Area = Areas.Area WHERE Areas.Area = {0} GROUP BY Codes.IUCR, PrimaryDesc, SecondaryDesc ORDER BY NumCrimes DESC; " , id); } else { // lookup movie(s) by partial name match: input = input.Replace("'", "''"); sql = string.Format(@" DECLARE @total AS FLOAT; SET @total = (SELECT COUNT(Crimes.IUCR) FROM Crimes INNER JOIN Areas ON Crimes.Area = Areas.Area WHERE AreaName = '{0}'); SELECT TOP 10 Codes.IUCR, PrimaryDesc, SecondaryDesc, Count(Crimes.IUCR) AS NumCrimes, ROUND( ( (Count(Crimes.IUCR) ) / @total ) * 100 , 2) AS PercentCrime, ROUND((SUM(CONVERT(float, Arrested)) / Count(Crimes.IUCR) * 100.0), 2) AS ArrestPercent FROM Crimes INNER JOIN Codes ON Crimes.IUCR = Codes.IUCR INNER JOIN Areas ON Crimes.Area = Areas.Area WHERE AreaName = '{0}' GROUP BY Codes.IUCR, PrimaryDesc, SecondaryDesc ORDER BY NumCrimes DESC; " , input); } DataSet ds = DataAccessTier.DB.ExecuteNonScalarQuery(sql); foreach (DataRow row in ds.Tables["TABLE"].Rows) { Models.Crimes m = new Models.Crimes(); m.IUCR = Convert.ToString(row["IUCR"]); m.PrimaryDesc = Convert.ToString(row["PrimaryDesc"]); m.SecondaryDesc = Convert.ToString(row["SecondaryDesc"]); m.NumCrimes = Convert.ToInt32(row["NumCrimes"]); m.PercentCrime = Convert.ToSingle(row["PercentCrime"]); m.ArrestPercent = Convert.ToSingle(row["ArrestPercent"]); crimes.Add(m); } } //else } catch (Exception ex) { EX = ex; } finally { CrimesList = crimes; // NumCrimes = crimes.Count; } }
public void OnGet(string input) { List<Models.Crimes> crimes = new List<Models.Crimes>(); AllCrimes allCrimes = new AllCrimes(); double totalCrimes = allCrimes.GetAllCrimes(); Input = input; // clear exception: EX = null; try { if (input == null) { // // there's no page argument, perhaps user surfed to the page directly? // In this case, nothing to do. // } else { // // Lookup movie(s) based on input, which could be id or a partial name: // int id; string sql; if (System.Int32.TryParse(input, out id)) { sql = string.Format(@" SELECT TOP 10 A.IUCR, A.PrimaryDesc + ' ' + A.SecondaryDesc description, A.Area, A.AreaName, A.Total_Crimes,B.Total_Arrested FROM (SELECT CO.IUCR, CO.PrimaryDesc, CO.SecondaryDesc, COUNT(*) Total_Crimes, AR.AreaName, AR.Area FROM dbo.Codes CO RIGHT JOIN dbo.Crimes CR ON CO.IUCR = CR.IUCR RIGHT JOIN dbo.Areas AR ON AR.Area = CR.Area WHERE AR.Area = {0} GROUP BY CO.IUCR, CO.PrimaryDesc, CO.SecondaryDesc, AR.AreaName, AR.Area) A JOIN (SELECT CO.IUCR, CO.PrimaryDesc, CO.SecondaryDesc, COUNT(*) Total_Arrested, AR.AreaName, AR.Area FROM dbo.Codes CO RIGHT JOIN dbo.Crimes CR ON CO.IUCR = CR.IUCR RIGHT JOIN dbo.Areas AR ON AR.Area = CR.Area WHERE AR.Area = {1} AND Arrested = 1 GROUP BY CO.IUCR, CO.PrimaryDesc, CO.SecondaryDesc, AR.AreaName, AR.Area) B ON B.IUCR = A.IUCR ORDER BY A.Total_Crimes DESC ",input,input); } else { // lookup movie(s) by partial name match: input = input.Replace("'", "''"); sql = string.Format(@" SELECT TOP 10 A.IUCR, A.PrimaryDesc + ' ' + A.SecondaryDesc description, A.Area, A.AreaName, A.Total_Crimes,B.Total_Arrested FROM (SELECT CO.IUCR, CO.PrimaryDesc, CO.SecondaryDesc, COUNT(*) Total_Crimes, AR.AreaName, AR.Area FROM dbo.Codes CO RIGHT JOIN dbo.Crimes CR ON CO.IUCR = CR.IUCR RIGHT JOIN dbo.Areas AR ON AR.Area = CR.Area WHERE AR.AreaName = '{0}' GROUP BY CO.IUCR, CO.PrimaryDesc, CO.SecondaryDesc, AR.AreaName, AR.Area) A JOIN (SELECT CO.IUCR, CO.PrimaryDesc, CO.SecondaryDesc, COUNT(*) Total_Arrested, AR.AreaName, AR.Area FROM dbo.Codes CO RIGHT JOIN dbo.Crimes CR ON CO.IUCR = CR.IUCR RIGHT JOIN dbo.Areas AR ON AR.Area = CR.Area WHERE AR.AreaName = '{1}' AND Arrested = 1 GROUP BY CO.IUCR, CO.PrimaryDesc, CO.SecondaryDesc, AR.AreaName, AR.Area) B ON B.IUCR = A.IUCR ORDER BY A.Total_Crimes DESC ", input,input); } DataSet ds = DataAccessTier.DB.ExecuteNonScalarQuery(sql); if(ds == null || ds.Tables.Count == 0 || ds.Tables["Table"].Rows.Count == 0){ throw new Exception("No data found for " + input); } foreach (DataRow row in ds.Tables["TABLE"].Rows) { Models.Crimes c = new Models.Crimes(); AreaName = Convert.ToString(row["AreaName"]); c.IUCR = Convert.ToString(row["IUCR"]); c.description = Convert.ToString(row["description"]); c.totalCrimes = Convert.ToInt32(row["Total_Crimes"]); c.percentTotal = Math.Round(((c.totalCrimes / totalCrimes) * 100.00),2); double arrested = Convert.ToDouble(row["Total_Arrested"]); c.percentArrested = Math.Round(((arrested / c.totalCrimes) * 100.00),2); Area = Convert.ToInt32(row["Area"]); crimes.Add(c); } } } catch(Exception ex) { EX = ex; } finally { CrimesList = crimes; } }