//This method is used to work with all filters
//Filter options are stored in the generic filters list
//SqlCommand starts with standard beginning of SQL query
//In switch we append to it necessary values,
//value by default is "Any'
public DataTable Filter(List<string> filterList)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * FROM [Screener].[dbo].[ScreenerSmall] WHERE ");
for (int i = 0; i < filterList.Count; i++)
{
if (filterList[i] != ANY)
{
switch (i)
{
case (int)FiltersNames.Country:
string insert = "[Country] Like ('%" + filterList[i] + "%') And ";
sqlCommand.Append(insert);
break;
case (int)FiltersNames.Industry:
insert = "[Industry] Like ('%" + filterList[i] + "%') And ";
sqlCommand.Append(insert);
break;
case (int)FiltersNames.Sector:
insert = "[Sector] Like ('%" + filterList[i] + "%') And ";
sqlCommand.Append(insert);
break;
case (int)FiltersNames.Price:
if (filterList[i][0] == UNDER)
{
insert = "[Price] < " +
float.Parse(filterList[i].Remove(REMOVE_ZERO_INDEX,
PRICE_UNDER_AMOUNT_OF_SYMBOLS)) + " And ";
}
else if (filterList[i][0] == OVER)
{
insert = "[Price] > " +
float.Parse(filterList[i].Remove(REMOVE_ZERO_INDEX,
PRICE_OVER_AMOUNT_OF_SYMBOLS)) + " And ";
}
else
{
insert = sqlCommand.ToString();
}
sqlCommand.Append(insert);
break;
case (int)FiltersNames.Date:
insert = "[Date] = '" + filterList[i] + "'";
sqlCommand.Append(insert);
break;
}
}
}
if (sqlCommand[sqlCommand.Length - INDEX] == WHITE_SPACE)
{
sqlCommand.Remove(sqlCommand.Length - REMOVE_START_INDEX, REMOVE_LENGTH);
}
DataAccess data = new DataAccess();
DataSet dataSet = data.SortByFilters(sqlCommand.ToString());
DataTable tables = dataSet.Tables[0];
return tables;
}
public DataTable FindByTicker(string ticker)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * FROM [Screener].[dbo].[ScreenerSmall] WHERE ");
string insert = "[Ticker] = '" + ticker + "'";
sqlCommand.Append(insert);
DataAccess data = new DataAccess();
DataSet dataSet = data.SortByFilters(sqlCommand.ToString());
DataTable tables = dataSet.Tables[0];
return tables;
}