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