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