public JsonResult GetHpdRegistrationsByBBL(string bbl) { OwnerAnalysisData data = ownerAnalysisDAL.GetHpdRegistrationsByBBL(bbl); return(new JsonResult() { Data = data.lstPortfolio, JsonRequestBehavior = JsonRequestBehavior.AllowGet, MaxJsonLength = Int32.MaxValue }); }
public FileResult DownloadOwnerAnalysis(string bbl) { OwnerAnalysisData data = ownerAnalysisDAL.GetHpdRegistrationsByBBL(bbl); byte[] excelBytes; ExcelPackage.LicenseContext = LicenseContext.NonCommercial; using (ExcelPackage xlPackage = new ExcelPackage()) { ExcelWorksheet workSheetOwnerAnalysis = xlPackage.Workbook.Worksheets.Add("OwnerAnalysis"); workSheetOwnerAnalysis.Cells["A1:D1"].Merge = true; workSheetOwnerAnalysis.Cells["A1"].Value = "Location"; workSheetOwnerAnalysis.Cells["E1:F1"].Merge = true; workSheetOwnerAnalysis.Cells["E1"].Value = "Information"; workSheetOwnerAnalysis.Cells["G1"].Value = "RS Units"; workSheetOwnerAnalysis.Cells["H1:I1"].Merge = true; workSheetOwnerAnalysis.Cells["H1"].Value = "HPD Violations"; workSheetOwnerAnalysis.Cells["J1"].Value = "Evictions"; workSheetOwnerAnalysis.Cells["K1"].Value = "Landlord"; workSheetOwnerAnalysis.Cells["L1:M1"].Merge = true; workSheetOwnerAnalysis.Cells["L1"].Value = "Last Sale"; workSheetOwnerAnalysis.Cells["A2"].Value = "Address"; workSheetOwnerAnalysis.Cells["B2"].Value = "Zipcode"; workSheetOwnerAnalysis.Cells["C2"].Value = "Borough"; workSheetOwnerAnalysis.Cells["D2"].Value = "BBL"; workSheetOwnerAnalysis.Cells["E2"].Value = "Built"; workSheetOwnerAnalysis.Cells["F2"].Value = "Units"; workSheetOwnerAnalysis.Cells["G2"].Value = "2019"; workSheetOwnerAnalysis.Cells["H2"].Value = "Open"; workSheetOwnerAnalysis.Cells["I2"].Value = "Total"; workSheetOwnerAnalysis.Cells["J2"].Value = "2019"; workSheetOwnerAnalysis.Cells["K2"].Value = "Officer/Owner"; workSheetOwnerAnalysis.Cells["L2"].Value = "Date"; workSheetOwnerAnalysis.Cells["M2"].Value = "Amount"; int i = 3; foreach (Hpd_Registrations_Group item in data.lstPortfolio) { workSheetOwnerAnalysis.Cells["A" + i].Value = item.Address; workSheetOwnerAnalysis.Cells["B" + i].Value = item.Zip; workSheetOwnerAnalysis.Cells["C" + i].Value = item.Boro; workSheetOwnerAnalysis.Cells["D" + i].Value = item.bbl; workSheetOwnerAnalysis.Cells["E" + i].Value = item.yearbuilt; workSheetOwnerAnalysis.Cells["F" + i].Value = item.unitsres; workSheetOwnerAnalysis.Cells["G" + i].Value = item.rsunits2019; workSheetOwnerAnalysis.Cells["H" + i].Value = item.openviolations; workSheetOwnerAnalysis.Cells["I" + i].Value = item.totalviolations; workSheetOwnerAnalysis.Cells["J" + i].Value = item.totalevictions; workSheetOwnerAnalysis.Cells["K" + i].Value = item.OwnerName; workSheetOwnerAnalysis.Cells["L" + i].Value = item.DOC_DATE; workSheetOwnerAnalysis.Cells["M" + i].Value = item.DOC_AMOUNT; i++; } //Header //SetHeaderStyle(workSheetDemographics, "A1:M1", "#4D7496"); //SetHeaderStyle(workSheetDemographics, "A2:M2", "#C6BFF7"); excelBytes = xlPackage.GetAsByteArray(); } return(File(excelBytes, "xlsx", "Data.xlsx")); }
// GET: OwnerAnalysis/Preview public ActionResult Preview(string bbl) { OwnerAnalysisData data = ownerAnalysisDAL.GetHpdRegistrationsByBBL(bbl); return(View(data)); }
public OwnerAnalysisData GetHpdRegistrationsByBBL(string bbl) { OwnerAnalysisData data = new OwnerAnalysisData(); List <Hpd_Registrations_Group> lstRegistrations = new List <Hpd_Registrations_Group>(); List <string> lstRegistrationIDs = new List <string>(); List <string> lstBusinessEntities = new List <string>(); List <string> lstBusinessAddresses = new List <string>(); using (var ctx = new NYC_Web_Mapping_AppEntities()) { var BBLParametar = !String.IsNullOrEmpty(bbl) ? new SqlParameter("BBL", bbl) : new SqlParameter("BBL", DBNull.Value); List <Hpd_Wow_Bldgs> lstWowBldgsRegistrations = ctx.Database.SqlQuery <Hpd_Wow_Bldgs>("EXEC NYC_Owner_Analysis.dbo.search_getRegistrationsByBBL @BBL ", BBLParametar).ToList(); if (lstWowBldgsRegistrations.Count > 0) { foreach (Hpd_Wow_Bldgs item in lstWowBldgsRegistrations) { data.FullAddress = item.FullAddress; data.GoogleStreetViewLink = System.Configuration.ConfigurationManager.AppSettings["GoogleApiLink"] + "?size=800x500&location=" + item.latitude + "," + item.longitude + "&key=AIzaSyCuf0Ca1EvxogvbZQKOBl_40y0UWm4Fk30"; lstRegistrationIDs.Add(item.RegistrationID); var RegIDParametar1 = !String.IsNullOrEmpty(item.RegistrationID) ? new SqlParameter("RegID", item.RegistrationID) : new SqlParameter("RegID", DBNull.Value); List <Hpd_Contact_Names> lstHpd_Contact_Names = ctx.Database.SqlQuery <Hpd_Contact_Names>("EXEC NYC_Owner_Analysis.dbo.search_getContactsByRegID @RegID ", RegIDParametar1).ToList(); string sqlQuery = "SELECT DISTINCT RegistrationID FROM [NYC_Owner_Analysis].[dbo].[hpd_contacts] WHERE "; string whereClauseContactNames = ""; foreach (Hpd_Contact_Names itemContactName in lstHpd_Contact_Names) { if (itemContactName.Type == "Agent") { data.Agent = itemContactName.FirstName + " " + itemContactName.LastName; } else if (itemContactName.Type == "SiteManager") { data.SiteManager = itemContactName.FirstName + " " + itemContactName.LastName; } if (itemContactName.CorporationName != null && !lstBusinessEntities.Contains(itemContactName.CorporationName.Trim(' ', '.'))) { lstBusinessEntities.Add(itemContactName.CorporationName.Trim(' ', '.')); } if ((itemContactName.Type == "CorporateOwner" || itemContactName.Type == "HeadOfficer" || itemContactName.Type == "IndividualOwner") && !String.IsNullOrEmpty(itemContactName.FirstName) && !String.IsNullOrEmpty(itemContactName.LastName)) { if (whereClauseContactNames == "") { whereClauseContactNames = "(FirstName = '" + itemContactName.FirstName + "' AND LastName = '" + itemContactName.LastName + "')"; } else { whereClauseContactNames += " OR (FirstName = '" + itemContactName.FirstName + "' AND LastName = '" + itemContactName.LastName + "')"; } } if (!String.IsNullOrEmpty(itemContactName.BusinessHouseNumber) || !String.IsNullOrEmpty(itemContactName.BusinessStreetName) || !String.IsNullOrEmpty(itemContactName.BusinessApartment) || !String.IsNullOrEmpty(itemContactName.BusinessZip)) { string BusinessHouseNumber = !String.IsNullOrEmpty(itemContactName.BusinessHouseNumber) ? "BusinessHouseNumber = '" + itemContactName.BusinessHouseNumber + "'" : "BusinessHouseNumber IS NULL"; string BusinessStreetName = !String.IsNullOrEmpty(itemContactName.BusinessStreetName) ? "BusinessStreetName = '" + itemContactName.BusinessStreetName + "'" : "BusinessStreetName IS NULL"; string BusinessZip = !String.IsNullOrEmpty(itemContactName.BusinessZip) ? "BusinessZip = '" + itemContactName.BusinessZip + "'" : "BusinessZip IS NULL"; string businessAddress = itemContactName.BusinessHouseNumber + " " + itemContactName.BusinessStreetName + " " + itemContactName.BusinessApartment + " " + itemContactName.BusinessZip; if (!lstBusinessAddresses.Contains(businessAddress)) { lstBusinessAddresses.Add(businessAddress); if (whereClauseContactNames == "") { whereClauseContactNames = "(" + BusinessHouseNumber + " AND " + BusinessStreetName + " AND " + BusinessZip + ")"; } else { whereClauseContactNames += " OR (" + BusinessHouseNumber + " AND " + BusinessStreetName + " AND " + BusinessZip + ")"; } } } } if (lstBusinessEntities.Count > 0) { foreach (string businessEntity in lstBusinessEntities) { if (whereClauseContactNames == "") { whereClauseContactNames = "(CorporationName LIKE '%" + businessEntity + "%')"; } else { whereClauseContactNames += " OR (CorporationName LIKE '%" + businessEntity + "%')"; } } } if (whereClauseContactNames != "") { sqlQuery = sqlQuery + whereClauseContactNames; List <Hpd_Contact_Names> lstContacts = ctx.Database.SqlQuery <Hpd_Contact_Names>(sqlQuery).ToList(); foreach (Hpd_Contact_Names itemContact in lstContacts) { if (!lstRegistrationIDs.Contains(itemContact.RegistrationID)) { lstRegistrationIDs.Add(itemContact.RegistrationID); } } } } string RegIDs = string.Join(",", lstRegistrationIDs); string bldgsQuery = "SELECT r.RegistrationID, (r.HouseNumber + ' ' + r.StreetName) AS Address, r.Zip, r.Boro, r.BBL, r.yearbuilt, r.unitsres, r.rsunits2019, r.openviolations, r.totalviolations, r.totalevictions, r.OwnerName, r.DOC_DATE, r.DOC_AMOUNT FROM [NYC_Owner_Analysis].[dbo].[wow_bldgs] r WHERE r.RegistrationID IN (" + RegIDs + ")"; lstRegistrations = ctx.Database.SqlQuery <Hpd_Registrations_Group>(bldgsQuery).ToList(); } } foreach (Hpd_Registrations_Group group in lstRegistrations) { string borough = group.bbl.Substring(0, 1); string block = group.bbl.Substring(1, 5); string lot = group.bbl.Substring(6); group.ACRIS_DocumentsLink = System.Configuration.ConfigurationManager.AppSettings["ACRIS_DocumentsLink"] + "?borough=" + borough + "&block=" + block + "&lot=" + lot; group.DOB_BuildingProfileLink = System.Configuration.ConfigurationManager.AppSettings["DOB_BuildingProfileLink"] + "?boro=" + borough + "&block=" + block + "&lot=" + lot; group.ANHD_DAP_PortalLink = System.Configuration.ConfigurationManager.AppSettings["ANHD_DAP_PortalLink"] + group.bbl; } data.BusinessEntities = string.Join(", ", lstBusinessEntities); data.lstPortfolio = lstRegistrations; data.selectedBuilding = lstRegistrations.Where(w => w.bbl == bbl).FirstOrDefault(); return(data); }