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