private void cmdSearch_Click(object sender, EventArgs e)
{
bool criteriaValid = true;
criteriaValid = addCriteria(cboCriteria1.Text, cboCrit1Bool.Text, txtCrit1Str.Text, cboIndustry1.Text);
criteriaValid = addCriteria(cboCriteria2.Text, cboCrit2Bool.Text, txtCrit2Str.Text, cboIndustry2.Text);
criteriaValid = addCriteria(cboCriteria3.Text, cboCrit3Bool.Text, txtCrit3Str.Text, cboIndustry3.Text);
criteriaValid = addCriteria(cboCriteria4.Text, cboCrit4Bool.Text, txtCrit4Str.Text, cboIndustry4.Text);
criteriaValid = addCriteria(cboCriteria5.Text, cboCrit5Bool.Text, txtCrit5Str.Text, cboIndustry5.Text);
if (!cboYear.Text.Equals("Average"))
{
wheres.Add("AND PlaceIsIn.Year = " + cboYear.Text);
}
sql = @"
SELECT Place.placeId, MAX(Place.name) Place, MAX(PlaceIsIn.stateName) State
FROM Place
INNER JOIN PlaceIsIn
ON Place.placeId = PlaceIsIn.placeId
";
foreach (string join in joins)
{
sql += FlushWith(" INNER JOIN PlaceIsIn", join);
sql += "\n";
}
sql += FlushWith(" FROM Place", "WHERE 1 = 1");
sql += "\n";
foreach (string where in wheres)
{
sql += FlushWith(" FROM Place", where, 2);
sql += "\n";
}
sql += FlushWith(" FROM Place", "GROUP BY Place.PlaceId");
sql += "\n";
sql += FlushWith(" FROM Place", "HAVING 1 = 1");
sql += "\n";
foreach (string having in havings)
{
sql += FlushWith(" FROM Place", having, 2);
sql += "\n";
}
sql += FlushWith(" FROM Place", "ORDER BY MAX(Place.Name)");
sql += "\n";
Debug.WriteLine(sql);
Main.sql = sql;
if (!criteriaValid)
{
MessageBox.Show("All partial criteria ignored");
}
// Clear criteria
joins = new HashSet <string>();
wheres = new HashSet <string>();
havings = new HashSet <string>();
this.Close();
Results results = new Results();
results.Show();
}
