public IEnumerable <string> GetCityProjectNames(string city)
{
DBLibrary db = new DBLibrary(_connectionString);
Thread.Sleep(10);
var cityClause = string.IsNullOrEmpty(city) ? "" : string.Format(" and city = '{0}'", city);
var query = string.Format("select distinct project_name from rent_data where project_name is not null{0}", cityClause);
var ds = db.ExecuteQuery(query);
List <string> list = new List <string>(ds.Tables["Table"].Rows.Count);
list = ds.Tables["Table"].AsEnumerable().Select(r =>
r["project_name"].ToString()
).ToList();
var generic = list.Where(i => i.Contains("esidential")).ToList();
foreach (var item in generic)
{
list.Remove(item);
}
return(list);
}
public IEnumerable <PropertyAverageRentDto> GetCityCurrentAverageRents(string city, string area, string project)
{
DBLibrary db = new DBLibrary(_connectionString);
Thread.Sleep(10);
var dateToday = DateTime.Today;
var dateStart = dateToday.AddDays(-100);
var dateEnd = dateToday.AddDays(100);
var dateRange = string.Format("possession_from between '{0}' and '{1}'", dateStart.ToString("yyyy-MM-dd"), dateEnd.ToString("yyyy-MM-dd"));
var cityClause = string.Format(" and city = '{0}'", city);
var areaClause = string.IsNullOrEmpty(area) ? "" : string.Format(" and area_name = '{0}'", area);
var projectClause = string.IsNullOrEmpty(project) ? "" : string.Format(" and project_name = '{0}'", project);
var query = string.Format("select avg(rent_monthly) rent, unit_type from rent_data where {0} {1}{2}{3} group by unit_type", dateRange, cityClause, areaClause, projectClause);
var ds = db.ExecuteQuery(query);
List <PropertyAverageRentDto> list = new List <PropertyAverageRentDto>(ds.Tables["Table"].Rows.Count);
list = ds.Tables["Table"].AsEnumerable().Select(r =>
new PropertyAverageRentDto
{
unit_type = r["unit_type"].ToString(),
avg_rent_monthly = Utilities.GetNullableInt(r["rent"].ToString())
}
).ToList();
return(list);
}
public IEnumerable <PropertyRentServiceTrimmedDto> TestRents(string numrec)
{
DBLibrary db = new DBLibrary(_connectionString);
var query = string.Format("select top {1} * from rent_data where city = '{0}'", "Pune", numrec);
var ds = db.ExecuteQuery(query);
//var list = ds.Tables["Table"].ToList<PropertyRentServiceDto>();
List <PropertyRentServiceDto> list = new List <PropertyRentServiceDto>(ds.Tables["Table"].Rows.Count);
foreach (DataRow dr in ds.Tables["Table"].Rows)
{
var dto = ConvertPropertyRentDataRowToServiceDto(dr);
list.Add(dto);
}
var testList = list.Select(i => new PropertyRentServiceTrimmedDto
{
property_id = i.property_id, bed_rooms = i.bed_rooms, floor_number = null, possession_from = i.possession_from,
added_on = i.added_on, last_updated = i.last_updated
});
return(testList);
}
public IEnumerable <string> GetAllCityNames()
{
DBLibrary db = new DBLibrary(_connectionString);
Thread.Sleep(10);
var query = string.Format("select distinct city from rent_data where city is not null");
var ds = db.ExecuteQuery(query);
List <string> list = new List <string>(ds.Tables["Table"].Rows.Count);
list = ds.Tables["Table"].AsEnumerable().Select(r =>
r["city"].ToString()
).ToList();
return(list);
}
public IEnumerable <string> GetCityAreaNames(string city)
{
DBLibrary db = new DBLibrary(_connectionString);
Thread.Sleep(10);
var cityClause = string.IsNullOrEmpty(city) ? "" : string.Format(" and city = '{0}'", city);
var query = string.Format("select distinct area_name from rent_data where area_name is not null{0}", cityClause);
var ds = db.ExecuteQuery(query);
List <string> list = new List <string>(ds.Tables["Table"].Rows.Count);
list = ds.Tables["Table"].AsEnumerable().Select(r =>
r["area_name"].ToString()
).ToList();
return(list);
}
public IEnumerable <PropertyRentServiceDto> GetRents(string city, string numrec)
{
DBLibrary db = new DBLibrary(_connectionString);
var query = string.Format("select top {1} * from rent_data where city = '{0}'", city, numrec);
var ds = db.ExecuteQuery(query);
//var list = ds.Tables["Table"].ToList<PropertyRentServiceDto>();
List <PropertyRentServiceDto> list = new List <PropertyRentServiceDto>(ds.Tables["Table"].Rows.Count);
foreach (DataRow dr in ds.Tables["Table"].Rows)
{
var dto = ConvertPropertyRentDataRowToServiceDto(dr);
list.Add(dto);
}
return(list);
//var only5 = list.Take(1);
//return only5;
}
public Dictionary <string, CityMetadataDto> GetCitiesMetaData(string city)
{
DBLibrary db = new DBLibrary(_connectionString);
Thread.Sleep(10);
List <string> cities = new List <string>();
if (!string.IsNullOrEmpty(city))
{
cities.Add(city);
}
else
{
cities.AddRange(GetAllCityNames());
}
var cityClause = string.Format("('{0}')", string.Join("','", cities));
var query = string.Format("select distinct city, area_name, project_name from rent_data where city in {0}", cityClause);
var ds = db.ExecuteQuery(query);
var list = ds.Tables["Table"].AsEnumerable().Select(r =>
new
{
city = r["city"].ToString(),
area = r["area_name"].ToString(),
project = r["project_name"].ToString()
}
).ToList();
var response = list.GroupBy(i => i.city).ToDictionary(g => g.Key).Select(kv =>
new CityMetadataDto
{
city = kv.Key,
areanames = kv.Value.ToList().Select(i => i.area).Distinct(),
projectnames = kv.Value.ToList().Select(i => i.project).Distinct()
}).ToDictionary(i => i.city);
return(response);
}
public IEnumerable <PropertyRentServiceDto> GetCityRents(string city, string area, string project, string maxrecords)
{
DBLibrary db = new DBLibrary(_connectionString);
var cityClause = string.Format(" city = '{0}'", city);
var areaClause = string.IsNullOrEmpty(area) ? "" : string.Format(" and area_name = '{0}'", area);
var projectClause = string.IsNullOrEmpty(project) ? "" : string.Format(" and project_name = '{0}'", project);
var query = string.Format("select top {3} * from rent_data where{0}{1}{2}", cityClause, areaClause, projectClause, maxrecords ?? "99999999");
var ds = db.ExecuteQuery(query);
//var list = ds.Tables["Table"].ToList<PropertyRentServiceDto>();
List <PropertyRentServiceDto> list = new List <PropertyRentServiceDto>(ds.Tables["Table"].Rows.Count);
foreach (DataRow dr in ds.Tables["Table"].Rows)
{
var dto = ConvertPropertyRentDataRowToServiceDto(dr);
list.Add(dto);
}
return(list);
//var only5 = list.Take(1);
//return only5;
}