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