public JsonResult GetTaxForListing(string id, string sortdatafield, string sortorder, int pagesize, int pagenum) { if (String.IsNullOrEmpty(sortdatafield)) { sortdatafield = "ID"; sortorder = ""; } var query = BuildQuery( String.Format(@" SELECT ID, LISTING_ID, EFFECTIVE_DATE, NAME,AMOUNT, CREATE_DATE,CREATE_USER, UPDATE_DATE, UPDATE_USER FROM TTAX_LISTING WHERE LISTING_ID = {0}", id)); query = String.Format("{0} order by {1} {2}", query, sortdatafield, sortorder); OracleRepository repo = new OracleRepository(); var taxes = repo.Query(query, pagenum, pagesize); if (taxes!=null && taxes.Count()>0) { foreach (var tax in taxes) { if (tax["EFFECTIVE_DATE"]!=DBNull.Value) { var effectiveDate = (DateTime?)tax["EFFECTIVE_DATE"]; if (effectiveDate != null) { tax["EFFECTIVE_DATE"] = effectiveDate.Value.Year.ToString(); } } } } var result = new { TotalRows = repo.Total(BuildQuery(String.Format("Select COUNT(*) from TTAX_LISTING WHERE LISTING_ID = {0}", id))), Rows = taxes }; return Json(result, JsonRequestBehavior.AllowGet); }
public JsonResult GetOfficesForListing(string id, string sortdatafield, string sortorder, int pagesize, int pagenum) { try { int nID = int.Parse(id); var query = BuildQuery( String.Format(@"SELECT ID, LISTING_ID, NAME,FLOOR_SIZE, CREATE_DATE,CREATE_USER, UPDATE_DATE, UPDATE_USER FROM TOFFICE_LISTING WHERE LISTING_ID = {0}", nID)); OracleRepository repo = new OracleRepository(); var offs = repo.Query(query, pagenum, pagesize); var result = new { TotalRows = repo.Total(BuildQuery(String.Format("Select COUNT(*) from TOFFICE_LISTING WHERE LISTING_ID = {0}", id))), Rows = offs }; return Json(result, JsonRequestBehavior.AllowGet); } catch(Exception ex) { return Json(null, JsonRequestBehavior.AllowGet); } }
public JsonResult GetRentsForListing(string id, string sortdatafield, string sortorder, int pagesize, int pagenum) { if (String.IsNullOrEmpty(sortdatafield)) { sortdatafield = "ID"; sortorder = ""; } var query = BuildQuery( String.Format(@" SELECT ID, LISTING_ID, EFFECTIVE_DATE, TOTAL_AREA,NET_RENT, OP_COSTS, CREATE_DATE,CREATE_USER, UPDATE_DATE, UPDATE_USER FROM VRENT_LISTING WHERE LISTING_ID = {0}", id)); query = String.Format("{0} order by {1} {2}", query, sortdatafield, sortorder); OracleRepository repo = new OracleRepository(); var rents = repo.Query(query, pagenum, pagesize); var result = new { TotalRows = repo.Total(BuildQuery(String.Format("Select COUNT(*) from VRENT_LISTING WHERE LISTING_ID = {0}", id))), Rows = rents }; return Json(result, JsonRequestBehavior.AllowGet); }
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 GetLoadingDocksForListing(string id, string sortdatafield, string sortorder, int pagesize, int pagenum) { if (String.IsNullOrEmpty(sortdatafield)) { sortdatafield = "ID"; sortorder = ""; } var query = BuildQuery( String.Format(@" SELECT ID, LISTING_ID, NO_LOADING_DOCKS, LOADING_SIZE, CREATE_DATE,CREATE_USER, UPDATE_DATE, UPDATE_USER FROM TLOADINGDOCK_LISTING WHERE LISTING_ID = {0}", id)); query = String.Format("{0} order by {1} {2}", query, sortdatafield, sortorder); OracleRepository repo = new OracleRepository(); var loadingdock = repo.Query(query, pagenum, pagesize); var result = new { TotalRows = repo.Total(BuildQuery(String.Format("Select COUNT(*) from TLOADINGDOCK_LISTING WHERE LISTING_ID = {0}", id))), Rows = loadingdock }; return Json(result, JsonRequestBehavior.AllowGet); }
public JsonResult GetDriveInOrDockForListing(string id, string sortdatafield, string sortorder, int pagesize, int pagenum) { if (String.IsNullOrEmpty(sortdatafield)) { sortdatafield = "ID"; sortorder = ""; } var queryDriveIn = BuildQuery( String.Format(@" SELECT ID,LISTING_ID, 'DRIVE IN' as TYPE, NO_DRIVE_IN as AMOUNT, SIZE_DRIVE_IN as SIZE_OF , CREATE_DATE,CREATE_USER, UPDATE_DATE, UPDATE_USER FROM TDRIVE_IN_LISTING WHERE LISTING_ID = {0}" , id)); var queryDock = BuildQuery( String.Format(@" SELECT ID,LISTING_ID, 'LOADING DOCK' as TYPE, NO_LOADING_DOCKS as AMOUNT, LOADING_SIZE as SIZE_OF , CREATE_DATE,CREATE_USER, UPDATE_DATE, UPDATE_USER FROM TLOADINGDOCK_LISTING WHERE LISTING_ID = {0}", id)); queryDriveIn = String.Format("{0} order by {1} {2}", queryDriveIn, sortdatafield, sortorder); queryDock = String.Format("{0} order by {1} {2}", queryDock, sortdatafield, sortorder); OracleRepository repo = new OracleRepository(); var drivein = repo.Query(queryDriveIn, pagenum, pagesize); var loading_dock = repo.Query(queryDock, pagenum, pagesize); var all = new List<Dictionary<string, object>>(); all.AddRange(drivein); all.AddRange(loading_dock); var total = repo.Total(BuildQuery(String.Format("Select COUNT(*) from TDRIVE_IN_LISTING WHERE LISTING_ID = {0}", id))); total += repo.Total(BuildQuery(String.Format("Select COUNT(*) from TLOADINGDOCK_LISTING WHERE LISTING_ID = {0}", id))); var result = new { TotalRows =total , Rows = all }; return Json(result, JsonRequestBehavior.AllowGet); }
public JsonResult GetBuildingListingList(decimal id, string sortdatafield, string sortorder, int pagesize, int pagenum) { if (String.IsNullOrEmpty(sortdatafield)) { sortdatafield = "ID"; sortorder = ""; } var query = BuildQuery( String.Format(@" SELECT * FROM VLISTING WHERE BUILDING_LISTING_ID = {0}", id)); query = String.Format("{0} order by {1} {2}", query, sortdatafield, sortorder); OracleRepository repo = new OracleRepository(); var broker = repo.Query(query, pagenum, pagesize); var result = new { TotalRows = repo.Total(BuildQuery(String.Format("Select COUNT(*) from VLISTING WHERE BUILDING_LISTING_ID = {0}", id))), Rows = broker }; return Json(result, JsonRequestBehavior.AllowGet); }
public JsonResult GetDocuments(string sortdatafield, string sortorder, int pagesize, int pagenum) { var query = BuildQuery(@"Select TDOCUMENT.NAME, TDOCUMENT.UPDATE_USER, TDOCUMENT.UPDATE_DATE, TDOCUMENT.CREATE_USER, TDOCUMENT.CREATE_DATE, TDOCUMENT.ID, TDOCUMENT.REVISION, TDOCUMENT.PARENT_DOCUMENT_ID, TDOCUMENT.SEQUENCE_NO, TDOCUMENT.TYPE , parent.NAME AS PARENT_NAME from TDOCUMENT LEFT OUTER JOIN TDOCUMENT parent ON parent.ID = TDOCUMENT.PARENT_DOCUMENT_ID WHERE (TDOCUMENT.TYPE = 'USER TEMPLATE' OR TDOCUMENT.TYPE ='DETAIL' OR TDOCUMENT.TYPE='SUMMARY' OR TDOCUMENT.TYPE='PARENT TEMPLATE') AND TDOCUMENT.STATUS<>'D' ", true); if (String.IsNullOrEmpty(sortdatafield)) { sortdatafield = "ID"; sortorder = ""; } query = String.Format("{0} order by {1} {2}", query, sortdatafield, sortorder); OracleRepository repo = new OracleRepository(); var documents = repo.Query(query, pagenum, pagesize); var result = new { TotalRows = repo.Total(BuildQuery("Select COUNT(*) from from TDOCUMEN LEFT OUTER JOIN TDOCUMENT parent ON parent.ID = TDOCUMENT.PARENT_DOCUMENT_ID WHERE (TYPE = 'USER TEMPLATE' OR TYPE ='DETAIL' OR TYPE='SUMMARY' OR TDOCUMENT.TYPE='PARENT TEMPLATE') AND STATUS<>'D'")), Rows = documents }; 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); }
public JsonResult GetAllBuildings(string sortdatafield, string sortorder, int pagesize, int pagenum) { var query = BuildQuery(@" SELECT b.BUILDING_ID, b.BUILDING_NAME, b.CLASS_TYP, b.LOCATION_TYP, b.QRTRLY_RPT_FLAG, b.ADDRESS1, b.POSTAL_CODE, b.YEAR_BUILT, b.STOREYS, b.NET_RENTABLE, b.FLOOR_PLATE, b.PARKING_RATIO_FORMATTED, b.latest_op_cost_amt, op_cost_year latest_op_cost_year, b.OWNER_NAME, B.LANDLORD_NAME, nvl(total_vacancy_avail,0) total_vacancy_avail, vp.headlease,vp.sublease,vp.ghost,vp.upcoming, b.CREATE_DATE, b.CREATE_USER, b.UPDATE_DATE, b.UPDATE_USER FROM VBUILDING b, VBUILDING_VACANCY_PIVOT vp, (select typ_id, sort_order from ttype where typ_group = 'CLASS') C, (select building_id,sum(size_sq) total_vacancy_avail from vbuilding_vacancy group by building_id) va WHERE B.CLASS_TYP_ID = C.TYP_ID and b.building_id = va.building_id and b.building_id = vp.building_id ",true); if (String.IsNullOrEmpty(sortdatafield)) { sortdatafield = "C.SORT_ORDER,B.BUILDING_NAME"; sortorder = ""; } query = String.Format("{0} order by {1} {2}", query, sortdatafield, sortorder); OracleRepository repo = new OracleRepository(); var buildings = repo.Query(query, pagenum, pagesize); var result = new { TotalRows = repo.Total(BuildQuery(@" Select COUNT(*) FROM VBUILDING b, VBUILDING_VACANCY_PIVOT vp, (select typ_id, sort_order from ttype where typ_group = 'CLASS') C, (select building_id,sum(size_sq) total_vacancy_avail from vbuilding_vacancy group by building_id) va WHERE B.CLASS_TYP_ID = C.TYP_ID and b.building_id = va.building_id and b.building_id = vp.building_id")), Rows = buildings, }; return Json(result, JsonRequestBehavior.AllowGet); }