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