public PartialViewResult ReportView(string startDate, string endDate, string[] companyIDs, string[] propertyIDs, string[] unitIDs, string[] statusIDs, string[] contractorIDs)
{
Session["startDate"] = startDate;
Session["endDate"] = endDate;
Session["selectedCompanyIDs"] = companyIDs;
Session["selectedPropertyIDs"] = propertyIDs;
Session["selectedUnitIDs"] = unitIDs;
Session["selectedStatusIDs"] = statusIDs;
Session["selectedContractorIDs"] = contractorIDs;
DateTime start = DateTime.Parse(startDate);
DateTime end = DateTime.Parse(endDate);
double totalRentRoll = 0;
double totalSecurityDeposit = 0;
double totalBalance = 0;
StringBuilder sbOperation = new StringBuilder();
sbOperation.Append("Select distinct tblTenant.*, tblProperty.Address,cUser.*, tblPropertyUnit.UnitName,cStatusType.Name as StatusName, OperationSummary.balance ,PaidSecurity ");
sbOperation.Append(" from tblTenant ");
sbOperation.Append(" inner join tblPropertyUnit on tblPropertyUnit.UnitID = tblTenant.UnitID ");
sbOperation.Append(" INNER JOIN tblProperty ON tblProperty.PropertyID = tblPropertyUnit.PropertyID ");
sbOperation.Append(" INNER JOIN mCompanyProperty on mCompanyProperty.PropertyID = tblProperty.PropertyID ");
sbOperation.Append(" INNER JOIN cUser on cUser.UserID = tblTenant.UserID ");
sbOperation.Append(" INNER JOIN cStatusType on cStatusType.StatusTypeID = tblTenant.StatusID ");
sbOperation.Append(" LEFT OUTER JOIN (select sum(DueAmount - Amount) as balance, contractorid from tblUnitOperation where CategoryID=36 group by contractorid) as OperationSummary on OperationSummary.ContractorID = tblTenant.UserID ");
sbOperation.Append(" LEFT OUTER JOIN (select sum(Amount) as PaidSecurity, contractorid from tblUnitOperation where categoryid=32 group by contractorid) as SecuritySummary on SecuritySummary.ContractorID = tblTenant.UserID ");
sbOperation.Append(" where dateadd(month, tblTenant.LeaseTerm,tblTenant.StartDate)>='" + start + "' ");
sbOperation.Append(" and tblTenant.StartDate<='" + end + "'");
// Add modality id to the where clause if appropriate
if (companyIDs != null && companyIDs.Count() > 0 && !string.IsNullOrEmpty(companyIDs[0]))
{
sbOperation.Append(" AND mCompanyProperty.CompanyID IN (" + String.Join(",", companyIDs) + ")");
}
else
{
//get the companys only the owner can access
sbOperation.Append(" AND mCompanyProperty.CompanyID IN (" + GetUserManagedCompanyString() + ")");
}
// Add modality id to the where clause if appropriate
if (propertyIDs != null && propertyIDs.Count() > 0 && !string.IsNullOrEmpty(propertyIDs[0]))
{
sbOperation.Append(" AND tblProperty.PropertyID IN (" + String.Join(",", propertyIDs) + ")");
}
// Add modality id to the where clause if appropriate
if (unitIDs != null && unitIDs.Count() > 0 && !string.IsNullOrEmpty(unitIDs[0]))
{
sbOperation.Append(" AND tblPropertyUnit.UnitID IN (" + String.Join(",", unitIDs) + ")");
}
if (statusIDs != null && statusIDs.Count() > 0 && !string.IsNullOrEmpty(statusIDs[0]))
{
sbOperation.Append(" AND tblTenant.StatusID IN (" + String.Join(",", statusIDs) + ")");
}
if (contractorIDs != null && contractorIDs.Count() > 0 && !string.IsNullOrEmpty(contractorIDs[0]))
{
sbOperation.Append(" AND [tblTenant].UserID IN (" + String.Join(",", contractorIDs) + ")");
}
sbOperation.Append(" Order by StartDate");
// Create a list of our result class to hold the data from the query
// Please ensure you instatiate the class for this controller and not a different controller
List <Tenant> result = new List <Tenant>();
// Execute the SQL query and get the results
using (SqlDataAdapter adapter = new SqlDataAdapter(sbOperation.ToString(), Helpers.Helpers.GetAppConnectionString()))
{
DataSet ds = new DataSet();
adapter.Fill(ds);
DataTable tb = (DataTable)ds.Tables[0];
if (tb != null && tb.Rows.Count > 0)
{
for (int i = 0; i < tb.Rows.Count; i++)
{
Tenant tr = TenantManager.FillInTenantWithData(tb.Rows[i]);
result.Add(tr);
totalRentRoll += tr.MonthlyPayment;
totalSecurityDeposit += tr.SecurityDeposit;
}
}
}
ViewBag.TableCaption = reporttitle + " Tenant: " + start.ToString("g") + " thru " + end.ToString("g");
ViewBag.TotalRentRoll = totalRentRoll;
ViewBag.TotalDeposit = totalSecurityDeposit;
ViewBag.TotalBalace = 0;
return(PartialView("ReportView", result));
}
