public object ProfitAndLossPost(JObject json)
{
_total = false;
initialiseReport(json);
addTable("!AccountType");
addTable("Account", "idAccount", "AccountCode", "AccountName", "AccountDescription");
fieldFor("idAccount").Hide();
fieldFor("AccountName")["sClass"] = "sa";
fieldFor("Heading").MakeEssential().Hide();
fieldFor("Negate").MakeEssential().Hide();
fieldFor("BalanceSheet").MakeEssential().Hide();
DateFilter date = new DateFilter("DocumentDate", DateRange.LastYear);
ReportField cp = new ReportField("SUM(Amount) AS CurrentPeriod", "decimal", "Current Period");
_fields.Add(cp);
ReportField lp = new ReportField("SUM(Amount) AS PreviousPeriod", "decimal", "Previous Period");
_fields.Add(lp);
_filters.Add(date);
setDefaultFields(json, "AcctType", "AccountName", "CurrentPeriod", "PreviousPeriod");
_sortOrder = "AcctType";
setFilters(json);
// P & L needs 2 period buckets for the 2 columns
DateTime[] cPeriod = date.CurrentPeriod();
cp.FullFieldName = "SUM(CASE WHEN DocumentDate >= " + Database.Quote(cPeriod[0]) + " AND DocumentDate < " + Database.Quote(cPeriod[1]) + " THEN Amount ELSE 0 END) AS CurrentPeriod";
cp["heading"] = date.PeriodName(cPeriod);
DateTime[] lPeriod = date.PreviousPeriod();
lp.FullFieldName = "SUM(CASE WHEN DocumentDate >= " + Database.Quote(lPeriod[0]) + " AND DocumentDate < " + Database.Quote(lPeriod[1]) + " THEN Amount ELSE 0 END) AS PreviousPeriod";
lp["heading"] = date.PeriodName(lPeriod);
string [] sort = new string[] { "AccountTypeId", "AccountCode", "AccountName" };
string[] fields = _fields.Where(f => f.Include || f.Essential || _sortFields.Contains(f.Name)).Select(f => f.FullFieldName).Distinct().ToArray();
JObjectEnumerable report = Database.Query("SELECT " + string.Join(",", fields)
+ @"
FROM AccountType
LEFT JOIN Account ON Account.AccountTypeId = AccountType.idAccountType
JOIN Journal ON Journal.AccountId = Account.idAccount
LEFT JOIN Document ON Document.idDocument = Journal.DocumentId
"
+ "\r\nWHERE BalanceSheet = 0"
+ "\r\nAND ((DocumentDate >= " + Database.Quote(lPeriod[0])
+ "\r\nAND DocumentDate < " + Database.Quote(cPeriod[1]) + ")"
+ "\r\nOR Account.AccountTypeId = " + (int)AcctType.Security + ")"
+ "\r\nGROUP BY idAccount"
+ "\r\nORDER BY " + string.Join(",", sort.Select(s => s + (_sortDescending ? " DESC" : "")).ToArray())
);
// Needs further processing to add investment gains
// total, etc.
return reportJson(json, fixProfitAndLoss(addInvestmentGains(report.ToList(), "Old", lPeriod[0], "PreviousPeriod", cPeriod[0], "CurrentPeriod", cPeriod[1])), "AccountType", "Account");
}
public object BalanceSheetPost(JObject json)
{
_total = false;
initialiseReport(json);
addTable("!AccountType");
addTable("Account", "idAccount", "AccountCode", "AccountName", "AccountDescription");
fieldFor("idAccount").Hide();
fieldFor("AccountName")["sClass"] = "sa";
fieldFor("Heading").MakeEssential();
fieldFor("Negate").MakeEssential().Hide();
fieldFor("BalanceSheet").MakeEssential().Hide();
DateFilter date = new DateFilter("DocumentDate", DateRange.LastYear);
ReportField cp = new ReportField("CurrentPeriod", "decimal", "Current Period");
_fields.Add(cp);
ReportField lp = new ReportField("PreviousPeriod", "decimal", "Previous Period");
_fields.Add(lp);
_filters.Add(date);
setDefaultFields(json, "Heading", "AcctType", "AccountName", "CurrentPeriod", "PreviousPeriod");
_sortOrder = "AcctType";
setFilters(json);
// Balance sheet needs 2 period buckets for the 2 columns
DateTime[] cPeriod = date.CurrentPeriod();
cp["heading"] = date.PeriodName(cPeriod);
DateTime[] lPeriod = date.PreviousPeriod();
lp["heading"] = date.PeriodName(lPeriod);
string[] sort = new string[] { "AccountTypeId", "AccountCode", "AccountName" };
string[] fields = _fields.Where(f => f.Include || f.Essential || _sortFields.Contains(f.Name)).Select(f => f.FullFieldName).Distinct().ToArray();
// We want one record per account, with totals for each bucket, and an Old value
// which is sum of all transactions before first bucket (opening balance)
JObjectEnumerable report = Database.Query("SELECT " + string.Join(",", fields) + @", Old
FROM AccountType
LEFT JOIN Account ON Account.AccountTypeId = AccountType.idAccountType
JOIN (SELECT AccountId,
SUM(CASE WHEN DocumentDate < " + Database.Quote(cPeriod[1]) + " AND DocumentDate >= " + Database.Quote(cPeriod[0]) + @" THEN Amount ELSE 0 END) AS CurrentPeriod,
SUM(CASE WHEN DocumentDate < " + Database.Quote(lPeriod[1]) + " AND DocumentDate >= " + Database.Quote(lPeriod[0]) + @" THEN Amount ELSE 0 END) AS PreviousPeriod,
SUM(CASE WHEN DocumentDate < " + Database.Quote(lPeriod[0]) + @" THEN Amount ELSE 0 END) AS Old
FROM Journal
LEFT JOIN Document ON Document.idDocument = Journal.DocumentId
WHERE DocumentDate < " + Database.Quote(cPeriod[1]) + @"
GROUP BY AccountId
) AS Summary ON AccountId = idAccount
ORDER BY " + string.Join(",", sort.Select(s => s + (_sortDescending ? " DESC" : "")).ToArray())
);
_sortFields = new string[] { "Heading", "AcctType", "AccountCode", "AccountName" };
// Report now needs further processing to:
// Calculate retained earnings account
// Add investment gains
// Consolidate P & L accounts and produce totals
return reportJson(json, fixBalanceSheet(addInvestmentGains(addRetainedEarnings(report), "Old", lPeriod[0], "PreviousPeriod", cPeriod[0], "CurrentPeriod", cPeriod[1])), "AccountType", "Account");
}
