public object TrialBalancePost(JObject json) { _total = false; initialiseReport(json); addTable("!AccountType", "Heading", "AcctType"); addTable("Account", "idAccount", "AccountCode", "AccountName", "AccountDescription"); fieldFor("idAccount").Hide(); addTable("Journal", "Amount"); fieldFor("Amount").FullFieldName = "Amount"; fieldFor("Credit").FullFieldName = "Amount"; fieldFor("Debit").FullFieldName = "Amount"; DateFilter date = new DateFilter("DocumentDate", DateRange.LastYear); _filters.Add(date); setDefaultFields(json, "AccountName", "Credit", "Debit"); _sortOrder = "AcctType"; setFilters(json); DateTime[] cPeriod = date.CurrentPeriod(); 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(); // Need Old (= opening balance) and final values for each account JObjectEnumerable report = Database.Query("SELECT " + string.Join(",", fields) + @", BalanceSheet, 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 Amount, SUM(CASE WHEN DocumentDate < " + Database.Quote(cPeriod[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" }; // Need to add investment gains // then process further to sort, add opening balances where required, and total return reportJson(json, fixTrialBalance(addInvestmentGains(addRetainedEarnings(report), "Old", cPeriod[0], "Amount", cPeriod[1])), "AccountType", "Account"); }
public object JournalsPost(JObject json) { initialiseReport(json); addTable("AccountType"); fieldFor("idAccountType").Hide().Essential = false; addTable("Account", "idAccount", "AccountCode", "AccountName", "AccountDescription"); addTable("!Journal"); addTable("!NameAddress"); addTable("Document", "idDocument", "DocumentDate", "DocumentIdentifier", "DocumentTypeId"); fieldFor("idDocument").MakeEssential()["heading"] = "Trans no"; addTable("DocumentType", "DocType"); fieldFor("DocumentIdentifier")["heading"] = "Doc Id"; fieldFor("DocumentDate").FullFieldName = "rDocDate AS DocumentDate"; fieldFor("DocumentTypeId").MakeEssential().Hide().FullFieldName = "rDocType AS DocumentTypeId"; fieldFor("Amount").FullFieldName = "Result.Amount"; fieldFor("Credit").FullFieldName = "Result.Amount"; fieldFor("Debit").FullFieldName = "Result.Amount"; fieldFor("idAccount").Hide().Essential = true; DateFilter date = new DateFilter("DocumentDate", DateRange.ThisMonth); RecordFilter account = new RecordFilter("Account", "Journal.AccountId", _sel.Account("")); date.Apply = false; account.Apply = false; _filters.Add(date); _filters.Add(account); _filters.Add(new StringFilter("Id", "DocumentIdentifier")); _filters.Add(new RecordFilter("DocumentType", "DocumentTypeId", _sel.DocumentType(""))); _filters.Add(new RecordFilter("NameAddress", "Journal.NameAddressId", _sel.Name(""))); _filters.Add(new DecimalFilter("JournalAmount", "Result.Amount")); _filters.Add(new StringFilter("Memo", "Journal.Memo")); _sortOrder = "idAccountType,AcctType,AccountName"; makeSortable("idAccountType,AcctType,AccountCode,AccountName=Account Type", "AccountName", "AccountCode,AccountName=AccountCode", "Name", "DocumentDate", "DocumentIdentifier=Doc Id", "DocType"); setDefaultFields(json, "AcctType", "AccountName", "Amount", "Memo", "Name", "DocType", "DocumentDate", "DocumentIdentifier"); setFilters(json); // we need account filter now! string where = account.Active ? "\r\nAND " + account.Where() : ""; // Need opening balance before start of period // Journals in period // Security gains/losses List<JObject> report = finishReport(@"( SELECT * FROM (SELECT Account.idAccount AS rAccount, Account.AccountTypeId as rAcctType, SUM(Journal.Amount) AS Amount, " + (int)DocType.OpeningBalance + " AS rDocType, 0 as rJournal, 0 as rDocument, 0 AS rJournalNum, " + Database.Cast(Database.Quote(date.CurrentPeriod()[0]), "DATETIME") + @" AS rDocDate FROM Account LEFT JOIN AccountType ON AccountType.idAccountType = Account.AccountTypeId LEFT JOIN Journal ON Journal.AccountId = Account.idAccount LEFT JOIN Document ON Document.idDocument = Journal.DocumentId WHERE DocumentDate < " + Database.Quote(date.CurrentPeriod()[0]) + @" AND BalanceSheet = 1" + where + @" GROUP BY AccountName) AS OpeningBalances WHERE Amount <> 0 OR rAcctType IN (" + (int)AcctType.Investment + "," + (int)AcctType.Security + @") UNION SELECT Account.idAccount AS rAccount, Account.AccountTypeId as rAcctType, Journal.Amount, DocumentTypeId As rDocType, idJournal AS rJournal, idDocument as rDocument, JournalNum as rJournal, DocumentDate AS rDocDate FROM Account LEFT JOIN AccountType ON AccountType.idAccountType = Account.AccountTypeId LEFT JOIN Journal ON Journal.AccountId = Account.idAccount LEFT JOIN Document ON Document.idDocument = Journal.DocumentId WHERE " + date.Where() + where + @" UNION SELECT Account.idAccount AS rAccount, Account.AccountTypeId as rAcctType, 0 AS Amount, " + (int)DocType.Gain + " AS rDocType, 0 as rJournal, 0 as rDocument, 0 AS rJournalNum, " + Database.Cast(Database.Quote(date.CurrentPeriod()[1].AddDays(-1)), "DATETIME") + @" AS rDocDate FROM Account WHERE AccountTypeId = " + (int)AcctType.Security + where.Replace("Journal.AccountId", "idAccount") + @" ) AS Result", "idAccountType,AccountName,DocumentDate,idDocument,JournalNum", @" LEFT JOIN Account on Account.idAccount = rAccount LEFT JOIN AccountType ON AccountType.idAccountType = Account.AccountTypeId LEFT JOIN Journal ON Journal.idJournal = rJournal LEFT JOIN NameAddress ON NameAddress.idNameAddress = Journal.NameAddressId LEFT JOIN Document ON Document.idDocument = rDocument LEFT JOIN DocumentType ON DocumentType.idDocumentType = rDocType ", json).ToList(); return reportJson(json, addInvestmentGains(date.CurrentPeriod(), account, report), "Account", "AccountType"); }
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"); }