/// <summary>
/// Get houses in mentioned location, optionally order and set max price.
/// </summary>
/// <param name="location"></param>
/// <param name="price"></param>
/// <param name="order"></param>
/// <returns></returns>
public static Dictionary <int, House> GetHouses(int location, int price = 0, string order = "")
{
string querystring1 = "", querystring2 = "";
switch (order)
{
case "new":
querystring1 = $"select _key, _val, PostDate, Price, LocationID, isOpen from Houses where LocationID={location} and isOpen=true ";
querystring2 = " order by PostDate desc;";
break;
case "price":
querystring1 = $"select _key, _val, Price, LocationID, isOpen from Houses where LocationID={location} and isOpen=true ";
querystring2 = " order by Price;";
break;
case "prisqm":
querystring1 = $"select _key, _val, Price, HomeArea, (Price/HomeArea) as SqmPrice, LocationID, isOpen from Houses where LocationID={location} and isOpen=true ";
querystring2 = " order by SqmPrice;";
break;
case "pop":
querystring1 = $"select A._key, A._val, A.Price, B.Cnt, A.LocationID, A.isOpen from \"estateobject\".HOUSES as A left join (select ObjectID, count(PersonID) as Cnt from \"bookmark\".BOOKMARKS group by ObjectID) as B on A._key = B.ObjectID where A.LocationID={location} and A.isOpen=true ";
querystring2 = " order by B.Cnt desc;";
break;
case "state":
querystring1 = $"select _key, _val, Price, LocationID, State, isOpen from Houses where LocationID={location} and isOpen=true ";
querystring2 = " order by State desc;";
break;
default:
querystring1 = $"select _key, _val, Price, LocationID, isOpen from Houses where LocationID={location} and isOpen=true ";
querystring2 = ";";
break;
}
if (price > 0)
{
querystring1 += $" and Price<={price} " + querystring2;
}
else
{
querystring1 += querystring2;
}
Dictionary <int, House> result = new Dictionary <int, House>();
foreach (var row in HouseCache.Query(new SqlFieldsQuery(querystring1)))
{
result[(int)row[0]] = row[1] as House;
}
return(result);
}
/// <summary>
/// Simply get all estate objects
/// </summary>
/// <returns></returns>
public static Dictionary <int, EstateObject> GetEstateObjects()
{
Dictionary <int, EstateObject> result = new Dictionary <int, EstateObject>();
foreach (var row in ObjectCache.Query(new SqlFieldsQuery("select _key, _val from EstateObjects order by PostDate;")))
{
result[(int)row[0]] = (row[1] as EstateObject);
}
foreach (var row in HouseCache.Query(new SqlFieldsQuery("select _key, _val from Houses order by PostDate;")))
{
result[(int)row[0]] = (row[1] as House);
}
foreach (var row in FlatCache.Query(new SqlFieldsQuery("select _key, _val from Flats order by PostDate;")))
{
result[(int)row[0]] = (row[1] as Flat);
}
foreach (var row in LandplotCache.Query(new SqlFieldsQuery("select _key, _val from Landplots order by PostDate;")))
{
result[(int)row[0]] = (row[1] as Landplot);
}
return(result);
}
