public byte[] MaintenanceReport(string sqlConnectionString, MaintenanceReportType reportType, DateTime fromDate, DateTime toDate, int buildingId, string buildingName, string dataPath)
{
using (var dc = new DataContext(sqlConnectionString))
{
var rp = new MaintenanceReport(dc);
return(rp.RunReport(reportType, fromDate, toDate, buildingId, buildingName, dataPath));
}
}
public byte[] RunReport(MaintenanceReportType reportType, DateTime fromDate, DateTime toDate, int buildingId, string buildingName, string dataPath)
{
DateTime startDate = fromDate;
DateTime endDate = toDate;
var pastelTransactions = new MaintenanceProcessor(_dataContext, buildingId).FetchAndLinkMaintenanceTransactions(startDate, endDate);
var buildingConfig = _dataContext.BuildingMaintenanceConfigurationSet.Where(a => a.BuildingId == buildingId).ToList();
var q = from r in _dataContext.tblRequisitions
join maint in _dataContext.MaintenanceSet on r.id equals maint.RequisitionId into maintSet
from m in maintSet.DefaultIfEmpty()
join d in _dataContext.MaintenanceDetailItemSet on m.id equals d.MaintenanceId into maintenanceDetail
from detail in maintenanceDetail.DefaultIfEmpty()
where r.trnDate >= startDate &&
r.trnDate <= endDate &&
r.building == buildingId
select new MaintenanceReportDataItem()
{
Ledger = r.ledger,
MaintenanceClassificationType = m != null ? m.BuildingMaintenanceConfiguration.MaintenanceClassificationType : MaintenanceClassificationType.MaintenancePlan,
Unit = detail == null ? string.Empty : detail.IsForBodyCorporate ? "Body Corporate" : detail.CustomerAccount,
MaintenanceType = m == null ? string.Empty : m.BuildingMaintenanceConfiguration.Name,
PastelAccountNumber = m == null ? string.Empty : m.BuildingMaintenanceConfiguration.PastelAccountNumber,
PastelAccountName = m == null ? string.Empty : m.BuildingMaintenanceConfiguration.PastelAccountName,
MaintenanceDate = r.trnDate,
Description = m == null ? r.reference : m.Description,
Supplier = r.Supplier == null ? string.Empty : r.Supplier.CompanyName,
CompanyReg = r.Supplier == null ? string.Empty : r.Supplier.CompanyRegistration,
VatNumber = r.Supplier == null ? string.Empty : r.Supplier.VATNumber,
ContactPerson = r.Supplier == null ? string.Empty : r.Supplier.ContactPerson,
EmailAddress = r.Supplier == null ? string.Empty : r.Supplier.EmailAddress,
ContactNumber = r.Supplier == null ? string.Empty : r.Supplier.ContactNumber,
Bank = r.BankName,
Branch = r.BranchName,
BranchCode = r.BranchCode,
AccountNumber = r.AccountNumber,
InvoiceNumber = m == null ? string.Empty : m.InvoiceNumber,
WarrantyDuration = m == null ? null : m.WarrantyDuration,
WarrantyType = m == null ? null : m.WarrantyDurationType,
WarrantyNotes = m == null ? string.Empty : m.WarrantyNotes,
WarrantyExpires = m == null ? null : m.WarrentyExpires,
SerialNumber = m == null ? string.Empty : m.WarrantySerialNumber,
Amount = detail == null ? r.amount : detail.Amount,
Paid = r.paid ? "Y" : "N",
LinkedPastelTransaction = r.PastelLedgerAutoNumber
};
var reportData = q.ToList();
reportData = (from r in reportData
join bc in buildingConfig on r.LedgerAccountNumber equals bc.PastelAccountNumber
select new MaintenanceReportDataItem
{
Ledger = r.Ledger,
MaintenanceClassificationType = bc.MaintenanceClassificationType,
Unit = r.Unit,
MaintenanceType = bc.Name,
PastelAccountNumber = bc.PastelAccountNumber,
PastelAccountName = bc.PastelAccountName,
MaintenanceDate = r.MaintenanceDate,
Description = r.Description,
Supplier = r.Supplier,
CompanyReg = r.CompanyReg,
VatNumber = r.VatNumber,
ContactPerson = r.ContactPerson,
EmailAddress = r.EmailAddress,
ContactNumber = r.ContactNumber,
Bank = r.Bank,
Branch = r.Branch,
BranchCode = r.BranchCode,
AccountNumber = r.AccountNumber,
InvoiceNumber = r.InvoiceNumber,
WarrantyDuration = r.WarrantyDuration,
WarrantyType = r.WarrantyType,
WarrantyNotes = r.WarrantyNotes,
WarrantyExpires = r.WarrantyExpires,
SerialNumber = r.SerialNumber,
Amount = r.Amount,
Paid = r.Paid,
LinkedPastelTransaction = r.LinkedPastelTransaction
}).ToList();
//union the first query with the second one
// now remove all pastel transctions from the pastel transaction list not already in the report
var toRemove = from p in pastelTransactions
join r in reportData on p.AutoNumber equals r.LinkedPastelTransaction
select p;
pastelTransactions = pastelTransactions.Except(toRemove.ToList()).ToList();
//now add these to the report data
var configPastel = from p in pastelTransactions
join c in buildingConfig on p.Account equals c.PastelAccountNumber
select new { pastel = p, BuildingMaintenanceConfiguration = c };
reportData.AddRange(configPastel.Select(r => new MaintenanceReportDataItem()
{
MaintenanceClassificationType = r.BuildingMaintenanceConfiguration.MaintenanceClassificationType,
Unit = "Unknown Unit",
MaintenanceType = r.BuildingMaintenanceConfiguration.Name,
PastelAccountNumber = r.BuildingMaintenanceConfiguration.PastelAccountNumber,
PastelAccountName = r.BuildingMaintenanceConfiguration.PastelAccountName,
MaintenanceDate = r.pastel.TransactionDate,
Description = r.pastel.Description,
Supplier = string.Empty,
CompanyReg = string.Empty,
VatNumber = string.Empty,
ContactPerson = string.Empty,
EmailAddress = string.Empty,
ContactNumber = string.Empty,
Bank = string.Empty,
Branch = string.Empty,
BranchCode = string.Empty,
AccountNumber = string.Empty,
InvoiceNumber = r.pastel.Reference,
WarrantyDuration = null,
WarrantyType = null,
WarrantyNotes = string.Empty,
WarrantyExpires = null,
SerialNumber = string.Empty,
Amount = Math.Abs(r.pastel.Amount),
Paid = string.Empty,
LinkedPastelTransaction = r.pastel.AutoNumber
}));
if (reportData.Count <= 0)
{
return(null);
}
reportData = reportData.OrderBy(a => a.MaintenanceClassificationType).ThenBy(a => a.Unit).ThenBy(a => a.MaintenanceType).ThenBy(a => a.MaintenanceDate).ThenBy(a => a.Supplier).ToList();
var accountNumbers = buildingConfig.Select(a => a.PastelAccountNumber).ToArray();// reportData.Select(a => a.PastelAccountNumber).Distinct().ToArray();
var accountList = LoadAccountValues(startDate, endDate, dataPath, accountNumbers);
//merge account data with account list
string currentLedgerAccount = "";
//to load the budgets - foreach month foreach account find the first record and then apply the budget amount.
foreach (var account in accountList.Where(a => a.Budget != 0))
{
var transaction = reportData.Where(a => a.PastelAccountNumber == account.AccNumber && a.PeriodMonth == account.PeriodMonth).FirstOrDefault();
if (transaction == null)
{
var config = buildingConfig.Where(a => a.PastelAccountNumber == account.AccNumber).First();
transaction = new MaintenanceReportDataItem()
{
MaintenanceClassificationType = config.MaintenanceClassificationType,
Unit = "Unknown Unit",
MaintenanceType = config.Name,
PastelAccountNumber = config.PastelAccountNumber,
PastelAccountName = config.PastelAccountName,
MaintenanceDate = account.PeriodMonth,
Description = string.Empty,
Supplier = string.Empty,
CompanyReg = string.Empty,
VatNumber = string.Empty,
ContactPerson = string.Empty,
EmailAddress = string.Empty,
ContactNumber = string.Empty,
Bank = string.Empty,
Branch = string.Empty,
BranchCode = string.Empty,
AccountNumber = string.Empty,
InvoiceNumber = string.Empty,
WarrantyDuration = null,
WarrantyType = null,
WarrantyNotes = string.Empty,
WarrantyExpires = null,
SerialNumber = string.Empty,
Amount = 0,
Paid = string.Empty,
LinkedPastelTransaction = null
};
reportData.Add(transaction);
}
transaction.Budget = account.Budget;
transaction.BudgetAvailable = account.BudgetAvailable;
}
decimal balance = 0;
foreach (var dataItem in reportData.Where(a => a.LinkedPastelTransaction != null))
{
if (dataItem.PastelAccountNumber != currentLedgerAccount)
{
balance = dataItem.Amount;
currentLedgerAccount = dataItem.PastelAccountNumber;
}
else
{
balance = balance + dataItem.Amount;
}
dataItem.Balance = balance;
}
return(RunReportToPdf(reportData, startDate, endDate, buildingName, reportType != MaintenanceReportType.SummaryReport));
}
