///<summary>
///Uses the dictionary returned from GetAllPrices to create a list of chartmodels with appropriate values
/// </summary>
///<returns>
///List of chart models with calculated average for the month and moving average across 3 months
/// </returns>
public List <ChartViewModel> GetAvgPriceList(string Location)
{
PullData db = new PullData(Configuration);
string Query = "SELECT Sum(price) FROM bricks.pmiresidenceresult ";
if (Location != "" && Location != null)
{
Query = Query + " Where street='" + Location + "'";
}
DataTable dtPriceSum = db.LoadData(Query, "PriceSum");
string resultQuery = "SELECT contractDate,FORMAT(((Sum(price)/" + dtPriceSum.Rows[0][0].ToString() + ")*100),6) AveragePrice FROM bricks.pmiresidenceresult ";
if (Location != "" && Location != null)
{
resultQuery = resultQuery + " Where street='" + Location + "'";
}
resultQuery = resultQuery + @" Group by contractDate order by STR_TO_DATE(concat('01,', left(contractDate, 2),',20',right(contractDate, 2)),'%d,%m,%Y')";
DataTable dtresultQuery = db.LoadData(resultQuery, "resultQuery");
for (int i = 0; i < dtresultQuery.Rows.Count; i++)
{
string strMonth = dtresultQuery.Rows[i]["contractDate"].ToString().Substring(0, 2);
string strYear = "20" + dtresultQuery.Rows[i]["contractDate"].ToString().Substring(2, 2);
dtresultQuery.Rows[i]["contractDate"] = strMonth + "-" + strYear;
}
var datalist = new List <ChartViewModel>();
datalist.Add(new ChartViewModel {
saleTime = dtresultQuery.Rows[0][0].ToString(), averagePrice = dtresultQuery.Rows[0][1].ToString(), movingAveragePrice = dtresultQuery.Rows[0][1].ToString()
});
double sumOfMonths = Double.Parse(dtresultQuery.Rows[0][1].ToString()) + Double.Parse(dtresultQuery.Rows[1][1].ToString());
double movingAverage = sumOfMonths / 2;
datalist.Add(new ChartViewModel {
saleTime = dtresultQuery.Rows[1][0].ToString(), averagePrice = dtresultQuery.Rows[1][1].ToString(), movingAveragePrice = movingAverage.ToString("N6")
});
for (int i = 2; i < dtresultQuery.Rows.Count; i++)
{
sumOfMonths = Double.Parse(dtresultQuery.Rows[i - 2][1].ToString()) + Double.Parse(dtresultQuery.Rows[i - 1][1].ToString()) + Double.Parse(dtresultQuery.Rows[i][1].ToString());
movingAverage = sumOfMonths / 3;
datalist.Add(new ChartViewModel {
saleTime = dtresultQuery.Rows[i][0].ToString(), averagePrice = dtresultQuery.Rows[i][1].ToString(), movingAveragePrice = movingAverage.ToString("N6")
});
}
return(datalist);
}
public string AjaxFilter(string Locations, string propertyTypes, string PriceRange, string SaleType, string LandArea, string FloorRange)
{
PullData db = new PullData(Configuration);
string whrQuery = " Where 0=0";
if (Locations != null)
{
whrQuery = whrQuery + " and street='" + Locations + "'";
}
if (propertyTypes != null)
{
whrQuery = whrQuery + " and propertyType='" + propertyTypes + "'";
}
if (SaleType != null)
{
whrQuery = whrQuery + " and typeOfSale='" + SaleType + "'";
}
if (FloorRange != null)
{
whrQuery = whrQuery + " and floorRange='" + FloorRange + "'";
}
if (PriceRange != null)
{
if (PriceRange == "1")
{
whrQuery = whrQuery + " and CAST(price AS UNSIGNED)<" + 1000000;
}
else if (PriceRange == "11")
{
whrQuery = whrQuery + " and CAST(price AS UNSIGNED)>" + 10000000;
}
else
{
whrQuery = whrQuery + " and (CAST(price AS UNSIGNED)<" + Convert.ToInt32(PriceRange) * 1000000 + " and CAST(price AS UNSIGNED)>" + ((Convert.ToInt32(PriceRange) * 1000000) - 1000000) + " )";
}
}
if (LandArea != null)
{
if (LandArea == "1")
{
whrQuery = whrQuery + " and CAST(area AS UNSIGNED)<" + 100;
}
else
{
whrQuery = whrQuery + " and (CAST(area AS UNSIGNED)<" + Convert.ToInt32(LandArea) * 100 + " and CAST(area AS UNSIGNED)>" + ((Convert.ToInt32(LandArea) * 100) - 100) + " )";
}
}
whrQuery = whrQuery + " ORDER BY idtransaction DESC LIMIT 600";
string query = " Select * From pmiresidenceresult " + whrQuery;
DataTable dt = db.LoadData(query, "Loaddt");
string json = JsonConvert.SerializeObject(dt);
return(JsonConvert.SerializeObject(dt));
}