Exemple #1
0
        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);
        }
Exemple #3
0
        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);
        }
Exemple #4
0
 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);
        }