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