public static string SelectQuery(DbViewWindow dbView)
{
int tabId = dbView.GetActiveTabId;
string detailsTableQuery = "";
string whereQuery = getWhereQuery(dbView);
if (tabId == 0)
{
detailsTableQuery = "details.engine AS Engine,details.doors AS No_of_doors FROM car";
}
else if (tabId == 1)
{
detailsTableQuery = $"details.engine AS Engine,details.capacity As Max_capacity_kg FROM truck";
}
else if (tabId == 2)
{
detailsTableQuery = "details.type AS Type FROM bike";
}
else
{
detailsTableQuery = "details.range As Range_in_km FROM scooter";
}
return($"SELECT v.brand AS Brand, v.model AS Model, v.prodYear AS Production_Year," +
$" v.color AS Color, {detailsTableQuery} details LEFT JOIN vehicle v ON v.vid = details.vid {whereQuery}");
}
public void SetYearFilter(DbViewWindow dbView)
{
List <string> yearFilter = new List <string>();
DataTable ModelFiltersTable = new DataTable();
sqlTable = "";
string whereQuery = "";
activeTabId = dbView.GetActiveTabId;
sqlTable = GetTableName(activeTabId);
if (dbView.BrandFilterValue != "")
{
whereQuery += $"WHERE v.brand = '{dbView.BrandFilterValue.ToString()}'";
}
if (dbView.ModelFilterValue != "")
{
whereQuery += $"AND v.model = '{dbView.ModelFilterValue.ToString()}'";
}
qry = $"SELECT MIN(v.prodYear) FROM {sqlTable} details LEFT JOIN vehicle v ON v.vid = details.vid {whereQuery}";
Database.sqliteCommand(ModelFiltersTable, qry);
foreach (DataRow row in ModelFiltersTable.Rows)
{
yearFilter.Add(row[0].ToString());
}
qry = $"SELECT MAX(v.prodYear) FROM {sqlTable} details LEFT JOIN vehicle v ON v.vid = details.vid {whereQuery}";
Database.sqliteCommand(ModelFiltersTable, qry);
foreach (DataRow row in ModelFiltersTable.Rows)
{
yearFilter.Add(row[1].ToString());
}
dbView.AddYearFilter(yearFilter);
}
public static string getWhereQuery(DbViewWindow dbView)
{
List <string> filtersList = new List <string>();
string whereQuery = "";
string modelFilterWhereQuery = "";
string brandFilterWhereQuery = "";
string yearToFilterWhereQuery = "";
string yearFromFilterWhereQuery = "";
int nrOfFiltersActive = 0;
if (dbView.BrandFilterValue.ToString() != "")
{
brandFilterWhereQuery = $"v.brand = '{ dbView.BrandFilterValue.ToString() }'";
nrOfFiltersActive++;
}
if (dbView.ModelFilterValue.ToString() != "")
{
if (nrOfFiltersActive > 0)
{
modelFilterWhereQuery += " AND ";
}
modelFilterWhereQuery += $" v.model = '{ dbView.ModelFilterValue.ToString() }'";
nrOfFiltersActive++;
}
if (dbView.YearFromFilterValue.ToString() != "")
{
if (nrOfFiltersActive > 0)
{
yearFromFilterWhereQuery += " AND ";
}
yearFromFilterWhereQuery += $" v.prodYear >= '{ dbView.YearFromFilterValue.ToString() }'";
nrOfFiltersActive++;
}
if (dbView.YearToFilterValue.ToString() != "")
{
if (nrOfFiltersActive > 0)
{
yearToFilterWhereQuery += " AND ";
}
yearToFilterWhereQuery += $" v.prodYear <= '{ dbView.YearToFilterValue.ToString() } '";
nrOfFiltersActive++;
}
if (nrOfFiltersActive > 0)
{
whereQuery = $"WHERE {brandFilterWhereQuery}{modelFilterWhereQuery}{yearFromFilterWhereQuery}{yearToFilterWhereQuery}";
}
return(whereQuery);
}
public void DbViewVechiles(DbViewWindow dbView)
{
if (SqliteConnection.State == System.Data.ConnectionState.Open)
{
DataTable selectTable = new DataTable();
string qry = QueryCreator.SelectQuery(dbView);
sqliteCommand(selectTable, qry);
dbView.ViewOnWindow(selectTable);
}
else
{
MessageBox.Show("Error. Database not connected");
}
}
public void SetBrandFilter(DbViewWindow dbView)
{
List <string> brandFilter = new List <string>();
activeTabId = dbView.GetActiveTabId;
DataTable BrandFiltersTable = new DataTable();
sqlGroupDetails = "";
sqlTable = "";
sqlTable = GetTableName(activeTabId);
sqlGroupDetails = "v.brand";
qry = $"SELECT {sqlGroupDetails} FROM {sqlTable} details LEFT JOIN vehicle v ON v.vid = details.vid GROUP BY {sqlGroupDetails}";
Database.sqliteCommand(BrandFiltersTable, qry);
foreach (DataRow row in BrandFiltersTable.Rows)
{
brandFilter.Add(row[0].ToString());
}
dbView.AddBrandFilter(brandFilter);
}
public void SetModelFilter(DbViewWindow dbView)
{
string brand = "";
sqlGroupDetails = "";
sqlTable = "";
activeTabId = dbView.GetActiveTabId;
sqlTable = GetTableName(activeTabId);
DataTable ModelFiltersTable = new DataTable();
List <string> modelFilter = new List <string>();
brand = dbView.BrandFilterValue;
sqlGroupDetails = "v.model";
qry = $"SELECT {sqlGroupDetails} FROM {sqlTable} details LEFT JOIN vehicle v ON v.vid = details.vid WHERE v.brand = '{brand}' GROUP BY {sqlGroupDetails} ";
Database.sqliteCommand(ModelFiltersTable, qry);
foreach (DataRow row in ModelFiltersTable.Rows)
{
modelFilter.Add(row[0].ToString());
}
dbView.AddModelFilter(modelFilter);
}