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