public JsonResult GetListings(string sortdatafield, string sortorder, int pagesize, int pagenum)
{
Models.SummaryModel summary = new Models.SummaryModel();
var query = BuildQuery(@"
Select VLISTING.*
from VLISTING ");
if (String.IsNullOrEmpty(sortdatafield))
{
//sortdatafield = "c.sort_order asc,VBUILDING.building_name asc,VBUILDING_VACANCY.floor_numeric desc";
sortdatafield = "ID";
sortorder = "";
}
query = String.Format("{0} order by {1} {2}", query, sortdatafield, sortorder);
OracleRepository repo = new OracleRepository();
var buildings = repo.Query(query, pagenum, pagesize);
var resultBuilding = repo.Query(BuildQuery(@"Select COUNT(VLISTING.ID) from VLISTING inner join (Select MAX(ID)as ID from VLISTING Group BY Address, CITY) t on t.ID = VLISTING.ID"));
var resultVac = repo.Query(BuildQuery(@"Select Count(ID) from VLISTING "));
//TODO get total acreage for land
var resultSq = repo.Query(BuildQuery(@"Select Sum(TOTAL_AREA) from VLISTING "));
if (resultBuilding != null) { summary.TotalBuilding = resultBuilding.ToString(); }
else { summary.TotalBuilding = "0"; }
if (resultVac != null) { summary.TotalVacancy = resultVac.ToString(); }
else { summary.TotalVacancy = "0"; }
if (resultSq != null) { summary.TotalSqFeet = String.Format("{0:n0}", resultSq); }
else { summary.TotalSqFeet = "0"; }
var result = new
{
TotalRows = repo.Total(BuildQuery("Select COUNT(*) from VLISTING")),
Rows = buildings,
Summary = summary
};
return Json(result, JsonRequestBehavior.AllowGet);
}
public JsonResult GetBuildingVacancies(string sortdatafield, string sortorder, int pagesize, int pagenum)
{
Models.SummaryModel summary = new Models.SummaryModel();
var query = BuildQuery(@"
Select VBUILDING.BUILDING_ID, VBUILDING.EXT_REF_ID, VBUILDING.BUILDING_NAME, VBUILDING.CLASS_TYP_ID, VBUILDING.CLASS_TYP,
VBUILDING.LOCATION_TYP_ID, VBUILDING.LOCATION_TYP, VBUILDING.QRTRLY_RPT_FLAG, VBUILDING.CONTACT_MECH_ID,
VBUILDING.ADDRESS1, VBUILDING.POSTAL_CODE, VBUILDING.YEAR_BUILT, VBUILDING.STOREYS, VBUILDING.NET_RENTABLE,
VBUILDING.FLOOR_PLATE, VBUILDING.PARKING_RATIO, VBUILDING.PARKING_RATIO_FORMATTED, VBUILDING.LATEST_OP_COST_AMT,
VBUILDING.OP_COST_YEAR, VBUILDING.LANDLORD_CONTACT_ID, VBUILDING.LANDLORD_NAME, VBUILDING.OWNER_CONTACT_ID,
VBUILDING.OWNER_NAME, VBUILDING.CREATE_DATE, VBUILDING.CREATE_USER, VBUILDING.UPDATE_DATE,
VBUILDING.UPDATE_USER, VBUILDING_VACANCY.BUILDING_VACANCY_ID, VBUILDING_VACANCY.LEASE_TYP_ID,
VBUILDING_VACANCY.LEASE_TYP_CODE, VBUILDING_VACANCY.LEASE_TYP, VBUILDING_VACANCY.SUITE_NO, VBUILDING_VACANCY.FLOOR,
VBUILDING_VACANCY.FLOOR_NUMERIC, VBUILDING_VACANCY.SIZE_SQ, VBUILDING_VACANCY.SMALLEST_DIV,
VBUILDING_VACANCY.INDUCEMENTS, VBUILDING_VACANCY.BASE_RENT, VBUILDING_VACANCY.TERM, VBUILDING_VACANCY.ARCHIVED_YN,
VBUILDING_VACANCY.LAST_ARCHIVED_DATE, VBUILDING_VACANCY.RETAIL_YN, VBUILDING_VACANCY.EXCLUSIVE_YN,
VBUILDING_VACANCY.VACANCY_DESC, VBUILDING_VACANCY.BROKER_NOTES
from VBUILDING
inner join VBUILDING_VACANCY on VBUILDING.BUILDING_ID= VBUILDING_VACANCY.BUILDING_ID
inner join (select typ_id, sort_order
from ttype where typ_group = 'CLASS')c on VBUILDING.class_typ_id = c.typ_id");
if (String.IsNullOrEmpty(sortdatafield))
{
sortdatafield = "c.sort_order asc,VBUILDING.building_name asc,VBUILDING_VACANCY.floor_numeric desc";
sortorder = "";
}
query = String.Format("{0} order by {1} {2}", query, sortdatafield, sortorder);
OracleRepository repo = new OracleRepository();
var buildings = repo.Query(query, pagenum, pagesize);
var resultBuilding = repo.Query(BuildQuery(@"
Select Count(DISTINCT VBUILDING.BUILDING_ID)
from VBUILDING
inner join VBUILDING_VACANCY on VBUILDING.BUILDING_ID= VBUILDING_VACANCY.BUILDING_ID
inner join (select typ_id, sort_order
from ttype where typ_group = 'CLASS')c on VBUILDING.class_typ_id = c.typ_id"));
var resultVac = repo.Query(BuildQuery(@"
Select Count(*)
from VBUILDING
inner join VBUILDING_VACANCY on VBUILDING.BUILDING_ID= VBUILDING_VACANCY.BUILDING_ID
inner join (select typ_id, sort_order
from ttype where typ_group = 'CLASS')c on VBUILDING.class_typ_id = c.typ_id "));
var resultSq = repo.Query(BuildQuery(@"
Select Sum(SIZE_SQ)
from VBUILDING
inner join VBUILDING_VACANCY on VBUILDING.BUILDING_ID= VBUILDING_VACANCY.BUILDING_ID
inner join (select typ_id, sort_order
from ttype where typ_group = 'CLASS')c on VBUILDING.class_typ_id = c.typ_id "));
if (resultBuilding != null) { summary.TotalBuilding = resultBuilding.ToString(); }
else { summary.TotalBuilding = "0"; }
if (resultVac != null) { summary.TotalVacancy = resultVac.ToString(); }
else { summary.TotalVacancy = "0"; }
if (resultSq != null) { summary.TotalSqFeet = String.Format("{0:n0}", resultSq); }
else { summary.TotalSqFeet = "0"; }
var result = new
{
TotalRows = repo.Total(BuildQuery("Select COUNT(*) from VBUILDING inner join VBUILDING_VACANCY on VBUILDING.BUILDING_ID= VBUILDING_VACANCY.BUILDING_ID")),
Rows = buildings,
Summary = summary
};
return Json(result, JsonRequestBehavior.AllowGet);
}
