// Build SELECT Query with Multiple fields
public void TestSelectMultipleBuild()
{
QueryBuilder query = new QueryBuilder();
query.AddField(Field.Fields.merkkiSelvakielinen, "BMW");
query.AddField(Field.Fields.valmistenumero2, "123456789");
query.Build(QueryBuilder.QueryType.Select);
string actual = query.QueryString;
string expected = "SELECT * FROM Vehicle WHERE merkkiSelvakielinen LIKE '%BMW%' AND valmistenumero2='123456789' LIMIT 100;";
Assert.AreEqual(expected, actual);
}
// Get Data from API
public void TestLoadData()
{
QueryBuilder query = new QueryBuilder();
query.AddField(Field.Fields.ID, "8897");
query.Build(QueryBuilder.QueryType.Select);
string actual = VehicleAPI.LoadData(query.QueryString);
string expected = "[{\"ID\":\"8897\",\"ajoneuvoluokka_koodi\":\"O2\",\"ajoneuvoluokka_lyhytselite\":\"Perävaunu\",\"ajoneuvoluokka_pitkaselite\":\"Perävaunu, 750 < m <= 3500 kg\",\"ensirekisterointipvm\":\"1998-04-22\",\"ajoneuvoryhma\":\"Keskiakseliperävaunu\",\"ajoneuvonkaytto\":\"Yksityinen\",\"variantti\":null,\"versio\":null,\"kayttoonottopvm\":\"1998-04-22\",\"vari\":null,\"ovienLukumaara\":null,\"korityyppi_koodi\":null,\"korityyppi_pitkaselite\":null,\"ohjaamotyyppi\":null,\"istumapaikkojenLkm\":null,\"omamassa\":\"640\",\"teknSuurSallKokmassa\":null,\"tieliikSuurSallKokmassa\":\"2100\",\"ajonKokPituus\":null,\"ajonLeveys\":\"2250\",\"ajonKorkeus\":null,\"kayttovoima\":null,\"iskutilavuus\":null,\"suurinNettoteho\":null,\"sylintereidenLkm\":null,\"ahdin\":null,\"sahkohybridi\":null,\"sahkohybridinluokka\":null,\"merkkiSelvakielinen\":\"Omavalmiste\",\"mallimerkinta\":\"PV 2100\",\"vaihteisto\":null,\"vaihteidenLkm\":null,\"kaupallinenNimi\":null,\"voimanvalJaTehostamistapa\":\"8\",\"tyyppihyvaksyntanro\":null,\"yksittaisKayttovoima\":null,\"kunta\":\"Oulu\",\"Co2\":null,\"matkamittarilukema\":null,\"alue\":\"901\",\"valmistenumero2\":null}]";
Assert.AreEqual(expected, actual);
}
// Build SELECT Query with Single fields
public void TestSelectSingleBuild()
{
QueryBuilder query = new QueryBuilder();
query.AddField(Field.Fields.merkkiSelvakielinen, "BMW");
query.Build(QueryBuilder.QueryType.Select);
string actual = query.QueryString;
string expected = "SELECT * FROM Vehicle WHERE merkkiSelvakielinen LIKE '%BMW%' LIMIT 100;";
Assert.AreEqual(expected, actual);
}
// Test Get Vehicles
public void TestGetVehicles()
{
QueryBuilder query = new QueryBuilder();
query.AddField(Field.Fields.ID, "8897");
query.Build(QueryBuilder.QueryType.Select);
List <Vehicle> vehicles = VehicleAPI.GetVehicles(query.QueryString);
string actual = vehicles[0].Ensirekisterointipvm;
string expected = "1998-04-22";
Assert.AreEqual(expected, actual);
}
public void ApplyPaginationForOracle(string orderByFieldAlias, SortOrderTypes sortDirection, int startRow, int endRow)
{
//add order by clause
this.AddOrderByClause(orderByFieldAlias, sortDirection);
//Create a new query builder for paged query
QueryBuilder pagedQueryBuilder = new QueryBuilder(SQLType.SelectSQL, this.Name);
//Add current query builder as an inner query to new query builder
pagedQueryBuilder.AddTable(this.QueryBuilder, pagedTableName);
//Add all columns to new query builder
foreach (var fieldName in this.FieldNames)
{
pagedQueryBuilder.AddField(pagedTableName, fieldName, fieldName);
}
//Add row number column to the new query builder
//TO DO:Add this to the Data Helper
pagedQueryBuilder.AddField("ROWNUM AS " + rowNumberColumnNameForOracle);
this.QueryBuilder = pagedQueryBuilder;
//Add new query builder as an inner query to ANOTHER new query builder
QueryBuilder newPagedQueryBuilder = new QueryBuilder(SQLType.SelectSQL, this.Name);
newPagedQueryBuilder.AddTable(pagedQueryBuilder, newPagedTableName);
foreach (var fieldName in this.FieldNames)
{
if (fieldName != rowNumberColumnNameForOracle)
{
newPagedQueryBuilder.AddField(newPagedTableName, fieldName, fieldName);
}
}
//Add pagination where clause to new query builder
newPagedQueryBuilder.AddWhereClauseItem(DataHelper.Between(rowNumberColumnNameForOracle, startRow.ToString(CultureInfo.InvariantCulture), endRow.ToString(CultureInfo.InvariantCulture)));
this.QueryBuilder = newPagedQueryBuilder;
}
/// <summary>
/// Apply paginiation to the query
/// </summary>
/// <param name="orderByFieldList">fileds to sort the data, in order to get page numbers</param>
/// <param name="sortDirectionList">sort direction</param>
/// <param name="startRow">page start row</param>
/// <param name="endRow">page end row</param>
public void ApplyPagination(Collection <SqlExpressionBase> orderByFieldList,
Collection <SortDirection> sortDirectionList, SqlExpressionBase startRow, SqlExpressionBase endRow)
{
//Add row number column to the current query builder
string[] fieldList = new string[orderByFieldList.Count];
SortDirection[] sortDirectionArray = new SortDirection[sortDirectionList.Count];
for (int i = 0; i < orderByFieldList.Count; i++)
{
fieldList.SetValue(orderByFieldList[i].ToString(), i);
}
for (int i = 0; i < sortDirectionList.Count; i++)
{
sortDirectionArray.SetValue(sortDirectionList[i], i);
}
this.QueryBuilder.AddField(DataHelper.RowNumber(fieldList, sortDirectionArray));
//Create a new query builder for paged query
QueryBuilder pagedQueryBuilder = new QueryBuilder(SQLType.SelectSQL, this.Name);
//Add current query builder as an inner query to new query builder
pagedQueryBuilder.AddTable(this.QueryBuilder, pagedTableName);
//Add pagination where clause to new query builder
pagedQueryBuilder.AddWhereClauseItem(DataHelper.Between(rowNumberColumnName, startRow.ToString(), endRow.ToString()));
//Add all columns to new query builder
foreach (var fieldName in this.FieldNames)
{
pagedQueryBuilder.AddField(pagedTableName, fieldName, fieldName);
}
//Add all parameters to new query builder
foreach (var parameter in this.QueryBuilder.Parameters)
{
pagedQueryBuilder.Parameters.Add(parameter);
}
//Replace curretn query builder with new query builder
this.QueryBuilder = pagedQueryBuilder;
}
/// <summary>
/// Wrapp the current Query Builder in an outer query
/// </summary>
public void WrapInOuterQuery()
{
//Create a new query builder for paged query
QueryBuilder pagedQueryBuilder = new QueryBuilder(SQLType.SelectSQL, this.Name);
//Add current query builder as an inner query to new query builder
pagedQueryBuilder.AddTable(this.QueryBuilder, pagedTableName);
//Add all columns to new query builder
foreach (var fieldName in this.FieldNames)
{
pagedQueryBuilder.AddField(pagedTableName, fieldName, fieldName);
}
//Add all parameters to new query builder
foreach (var parameter in this.QueryBuilder.Parameters)
{
pagedQueryBuilder.Parameters.Add(parameter);
}
//Replace curretn query builder with new query builder
this.QueryBuilder = pagedQueryBuilder;
this.Name = pagedTableName;
}
async private void BtnDoSearch_Click(object sender, RoutedEventArgs e)
{
//Upon clock perform the folllowing actions if possible.
try
{
QueryBuilder searchquery = new QueryBuilder();
//If Brand field is not empty, perform the following:
if (txtBrand.Text != "")
{
searchquery.AddField(Field.Fields.merkkiSelvakielinen, txtBrand.Text);
}
//If Model field is not empty, perform the following:
if (txtModel.Text != "")
{
searchquery.AddField(Field.Fields.mallimerkinta, txtModel.Text);
}
//If Color field is not empty, perform the following:
if (cbColor.SelectedValue != null)
{
searchquery.AddField(Field.Fields.vari, cbColor.SelectedValue.ToString());
}
//If First Registration Date field is not empty and the checkbox was checked to activate the field, perform the following:
if (dpFirstRegDate1.Date != null && dpFirstRegDate2.Date != null && cbFirstRegDate.IsChecked == true)
{
searchquery.AddField(Field.Fields.ensirekisterointipvm, dpFirstRegDate1.Date.ToString("yyyy-MM-dd"), dpFirstRegDate2.Date.ToString("yyyy-MM-dd"));
}
if (dpDeployDate1.Date != null && dpDeployDate2.Date != null && cbDeployDate.IsChecked == true)
{
searchquery.AddField(Field.Fields.kayttoonottopvm, dpDeployDate1.Date.ToString("yyyy-MM-dd"), dpDeployDate2.Date.ToString("yyyy-MM-dd"));
}
//If Fuel Type field is not empty, perform the following:
if (cbFuelType.SelectedValue != null)
{
searchquery.AddField(Field.Fields.kayttovoima, cbFuelType.SelectedValue.ToString());
}
//If Frame Type field is not empty, perform the following:
if (cbFrameType.SelectedValue != null)
{
searchquery.AddField(Field.Fields.korityyppi_pitkaselite, cbFrameType.SelectedValue.ToString());
}
//If County field is not empty, perform the following:
if (cbCounty.SelectedValue != null)
{
searchquery.AddField(Field.Fields.kunta, cbCounty.SelectedValue.ToString());
}
//If Gears field is not empty, perform the following:
if (txtGears.Text != "")
{
int value;
if (int.TryParse(txtGears.Text, out value) == false)
{
throw new Exception("Number of Gears value isn't integer");
}
searchquery.AddField(Field.Fields.vaihteisto, value.ToString());
}
//If Serial Number field is not empty, perform the following:
if (txtSerialNmb.Text != "")
{
searchquery.AddField(Field.Fields.valmistenumero2, txtSerialNmb.Text);
}
//If Co2 field is not empty and the input is in integer format, perform the following:
if (txtStartCo2.Text != "" && txtEndCo2.Text != "")
{
int start, end;
if (int.TryParse(txtStartCo2.Text, out start) == false)
{
throw new Exception("Co2 Start value isn't integer");
}
if (int.TryParse(txtEndCo2.Text, out end) == false)
{
throw new Exception("Co2 End value isn't integer");
}
searchquery.AddField(Field.Fields.Co2, start.ToString(), end.ToString());
}
//If Mileage field is not empty and the input is in integer format, perform the following:
if (txtStartMileage.Text != "" && txtEndMileage.Text != "")
{
int start, end;
if (int.TryParse(txtStartMileage.Text, out start) == false)
{
throw new Exception("Mileage Start value isn't integer");
}
if (int.TryParse(txtEndMileage.Text, out end) == false)
{
throw new Exception("Mileage End value isn't integer");
}
searchquery.AddField(Field.Fields.matkamittarilukema, start.ToString(), end.ToString());
}
//If Mass field is not empty and the input is in integer format, perform the following:
if (txtStartMass.Text != "" && txtEndMass.Text != "")
{
int start, end;
if (int.TryParse(txtStartMass.Text, out start) == false)
{
throw new Exception("Weight Start value isn't integer");
}
if (int.TryParse(txtEndMass.Text, out end) == false)
{
throw new Exception("Weight End value isn't integer");
}
searchquery.AddField(Field.Fields.omamassa, start.ToString(), end.ToString());
}
//If Cylinder Cap field is not empty and the input is in integer format, perform the following:
if (txtStartCylinderCap.Text != "" && txtEndCylinderCap.Text != "")
{
int start, end;
if (int.TryParse(txtStartCylinderCap.Text, out start) == false)
{
throw new Exception("Engine Size Start value isn't integer");
}
if (int.TryParse(txtEndCylinderCap.Text, out end) == false)
{
throw new Exception("Engine Size End value isn't integer");
}
searchquery.AddField(Field.Fields.sylintereidenLkm, start.ToString(), end.ToString());
}
//If Power field is not empty and the input is in integer format, perform the following:
if (txtStartNetPower.Text != "" && txtEndNetPower.Text != "")
{
int start, end;
if (int.TryParse(txtStartNetPower.Text, out start) == false)
{
throw new Exception("Power Start value isn't integer");
}
if (int.TryParse(txtEndNetPower.Text, out end) == false)
{
throw new Exception("Power End value isn't integer");
}
searchquery.AddField(Field.Fields.suurinNettoteho, start.ToString(), end.ToString());
}
//If Car Class field is not empty, perform the following:
if (cbCarClass.SelectedValue != null)
{
searchquery.AddField(Field.Fields.ajoneuvoluokka_koodi, cbCarClass.SelectedValue.ToString());
}
//If Electric Hybrid field is checked, perform the following:
if (ckbElecHybrid.IsChecked == true)
{
searchquery.AddField(Field.Fields.sahkohybridi, "1");
}
// Build Query and set limit to 100
searchquery.Build(QueryBuilder.QueryType.Select, 1000);
// Activate ProgressSpinner, Show Progress Text and Disable Search button
SearchProgress.IsActive = true;
txtSearchProgress.Text = "Searching...";
btnDoSearch.IsEnabled = false;
// Load Vehicles from API asynchronously
SearchResult.Vehicles = await VehicleAPI.GetVehiclesAsync(searchquery.QueryString);
// Disable ProgressSpinner, Show Progress Text and Enable Search button
SearchProgress.IsActive = false;
txtSearchProgress.Text = "Done";
btnDoSearch.IsEnabled = true;
// If there was no result, stay in search page
if (SearchResult.Vehicles.Count > 0)
{
// Add Search to History
SearchHistory.AddItem(new SearchHistoryItem("Search", DateTime.Now, SearchResult.Vehicles.Count + " results", searchquery.QueryString, searchquery.FieldsToString()));
SearchHistory.Save();
// Navigate to SearchResult page
Frame.Navigate(typeof(SearchResult), null, new SuppressNavigationTransitionInfo());
}
}
//Handle exceptions
catch (Exception ex)
{
MessageDialog dialog = new MessageDialog(ex.Message);
dialog.Title = "Info";
await dialog.ShowAsync();
// Disable ProgressSpinner, Show Progress Text and Enable Search button
SearchProgress.IsActive = false;
txtSearchProgress.Text = "Done";
btnDoSearch.IsEnabled = true;
}
}
