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);
}
