public override bool Test(JObject data)
{
int id = data.AsInt(JObjectFieldName);
return _ids.Contains(id);
}
public override bool Test(JObject data)
{
int value = data.AsInt(JObjectFieldName);
return _value == value;
}
IEnumerable<JObject> fixTrialBalance(IEnumerable<JObject> data)
{
_total = false;
JObject last = null;
decimal retainedProfitOld = 0;
Dictionary<string, decimal> totals = new Dictionary<string, decimal>();
// Make list of fields to total
foreach (ReportField f in _fields) {
if (!f.Include) continue;
string type = f.AsString("type");
if (type != "decimal" && type != "double" && type != "credit" && type != "debit") continue;
totals[f.Name] = 0;
}
// Function to add a total record
Func<JObject> totalRecord = delegate() {
JObject t = new JObject().AddRange("@class", "total");
foreach (string f in totals.Keys.ToList()) {
t[f] = totals[f];
}
t["AccountName"] = "Total";
return t;
};
if (data.FirstOrDefault(r => r.AsInt("idAccount") == (int)Acct.RetainedEarnings) == null) {
// Must have retained earnings account, so add if missing
data = data.Concat(Enumerable.Repeat(new JObject().AddRange(
"idAccount", (int)Acct.RetainedEarnings,
"Heading", "Equities",
"BalanceSheet", 1,
"AccountTypeId", (int)AcctType.Equity,
"AcctType", "Equity",
"AccountName", "Retained Earnings",
"Negate", 1,
"CurrentPeriod", 0M,
"PreviousPeriod", 0M,
"Old", 0M
), 1));
}
foreach (JObject r in data) {
JObject record = new JObject(r);
if (record.AsInt("BalanceSheet") == 0) {
// Accumulate all P & L before previous period into retained profit
retainedProfitOld += record.AsDecimal("Old");
} else {
// For balance sheet, add values before previous period into current value
record["Amount"] = record.AsDecimal("Amount") + record.AsDecimal("Old");
if (r.AsInt("idAccount") == (int)Acct.RetainedEarnings) {
record["Amount"] = record.AsDecimal("Amount") + retainedProfitOld;
record.Remove("idAccount"); // So user can't click on it to expand
}
record.Remove("Old");
}
decimal v = record.AsDecimal("Amount");
if (v == 0)
continue;
// Accumulate totals
foreach (string f in totals.Keys.ToList()) {
decimal v1 = v;
if (f == "Credit") {
v1 = v1 < 0 ? -v1 : 0;
} else if (f == "Debit") {
if (v1 < 0) v1 = 0;
} else {
record[f] = v;
}
totals[f] += v1;
}
last = r;
yield return record;
}
if (last != null) {
yield return totalRecord();
}
}
// TODO: Should use common totalling code
IEnumerable<JObject> fixBalanceSheet(IEnumerable<JObject> data)
{
_total = false;
JObject last = null;
string lastTotalBreak = null;
string lastHeading = null;
int sign = 1;
decimal retainedProfitCP = 0, retainedProfitPP = 0, retainedProfitOld = 0;
Dictionary<string, decimal[]> totals = new Dictionary<string, decimal[]>();
JObject spacer = new JObject().AddRange("@class", "totalSpacer");
// Make list of fields to total
foreach (ReportField f in _fields) {
if (!f.Include) continue;
string type = f.AsString("type");
if (type != "decimal" && type != "double" && type != "credit" && type != "debit") continue;
totals[f.Name] = new decimal[3];
}
// Function to add a total record (index is total level - 0=account type change, 1=heading change, 2=total assets/total liabilities & equity)
Func<int, JObject> totalRecord = delegate(int index) {
JObject t = new JObject().AddRange("@class", "total total" + index);
foreach (string f in totals.Keys.ToList()) {
t[f] = sign * totals[f][index];
totals[f][index] = 0;
}
if (index == 0) {
t["Heading"] = lastHeading;
t[_sortOrder] = "Total " + lastTotalBreak;
} else {
t["Heading"] = "Total " + lastHeading;
}
return t;
};
if(data.FirstOrDefault(r => r.AsInt("idAccount") == (int)Acct.RetainedEarnings) == null) {
// Need a retained earnings account line, so create one if missing
data = data.Concat(Enumerable.Repeat(new JObject().AddRange(
"idAccount", (int)Acct.RetainedEarnings,
"Heading", "Equities",
"BalanceSheet", 1,
"AccountTypeId", (int)AcctType.Equity,
"AcctType", "Equity",
"AccountName", "Retained Earnings",
"Negate", 1,
"CurrentPeriod", 0M,
"PreviousPeriod", 0M,
"Old", 0M
), 1));
}
foreach (JObject r in data) {
JObject record = new JObject(r);
string totalBreak = record.AsString(_sortOrder);
string heading = record.AsString("Heading");
if (record.AsInt("BalanceSheet") == 0) {
// Accumulate profit and loss postings
retainedProfitCP += record.AsDecimal("CurrentPeriod");
retainedProfitPP += record.AsDecimal("PreviousPeriod");
retainedProfitOld += record.AsDecimal("Old");
continue;
} else {
if (r.AsInt("idAccount") == (int)Acct.RetainedEarnings) {
// Add accumulated profit into retained earnings
record["PreviousPeriod"] = record.AsDecimal("PreviousPeriod") + retainedProfitOld;
record["CurrentPeriod"] = record.AsDecimal("CurrentPeriod") + retainedProfitPP;
record.Remove("idAccount"); // So user can't click on it to expand
}
// Balance sheet shows totals so far, so add in previous periods
record["PreviousPeriod"] = record.AsDecimal("PreviousPeriod") + record.AsDecimal("Old");
record["CurrentPeriod"] = record.AsDecimal("CurrentPeriod") + record.AsDecimal("PreviousPeriod");
record.Remove("Old");
}
if (totalBreak != lastTotalBreak) {
if (last != null) {
if (lastTotalBreak != null) {
// Add total and spacer for account type change
spacer["Heading"] = lastHeading;
yield return totalRecord(0);
yield return spacer;
if (lastHeading != heading) {
// Add total and spacer for heading change
lastTotalBreak = lastHeading;
yield return totalRecord(1);
spacer.Remove("Heading");
yield return spacer;
if (lastHeading.Contains("Assets") && !heading.Contains("Assets")) {
// Add total and spacer for total assets
lastHeading = "Assets";
sign = 1;
yield return totalRecord(2);
yield return spacer;
}
}
}
}
if (lastHeading != heading) // Next heading if required
yield return new JObject().AddRange("@class", "title", "Heading", heading);
lastTotalBreak = totalBreak;
lastHeading = heading; // Account type heading
yield return new JObject().AddRange("@class", "title", "Heading", heading, "AcctType", totalBreak);
}
sign = record.AsBool("Negate") ? -1 : 1;
// Accumulate totals
foreach (string f in totals.Keys.ToList()) {
decimal v = record.AsDecimal(f);
decimal[] tots = totals[f];
for (int i = 0; i < tots.Length; i++) {
tots[i] += v;
}
record[f] = sign * v;
}
last = r;
// The record itself (now all totals and headings taken care of)
yield return record;
if (r.AsInt("idAccount") == (int)Acct.RetainedEarnings) {
// Generate Net Income posting
record = new JObject(record);
record.Remove("idAccount");
record["AccountName"] = "Net Income";
record["AccountDescription"] = "";
record["CurrentPeriod"] = -retainedProfitCP;
record["PreviousPeriod"] = -retainedProfitPP;
foreach (string f in totals.Keys.ToList()) {
decimal v = record.AsDecimal(f) * sign;
decimal[] tots = totals[f];
for (int i = 0; i < tots.Length; i++) {
tots[i] += v;
}
}
yield return record;
}
}
if (last != null) {
// Liabilites and equity total
yield return totalRecord(0);
yield return spacer;
lastHeading = "Liabilities & Equity";
sign = -1;
yield return totalRecord(2);
yield return spacer;
}
}
/// <summary>
/// Return the audit report data, flattened and filtered
/// </summary>
/// <param name="json">Report settings</param>
/// <param name="type">Report type</param>
/// <param name="defaultFields">Fields that appear by default, if user has not changed settings</param>
object auditReportData(JObject json, string type, params string[] defaultFields)
{
defaultFields = (_changeTypeNotRequired ? new string[] { "DateChanged" } : new string[] { "DateChanged", "ChangeType" }).Concat(defaultFields).ToArray();
setDefaultFields(json, defaultFields);
setFilters(json);
string where = _dates.Active ? " AND " + _dates.Where() : "";
if (json.AsInt("recordId") > 0)
where += " AND RecordId = " + json.AsInt("recordId");
JObjectEnumerable report = Database.Query("SELECT ChangeType, DateChanged, Record FROM AuditTrail WHERE TableName = "
+ Database.Quote(type)
+ where
+ " ORDER BY DateChanged, idAuditTrail");
List<string> tables = new List<string>();
tables.Add("AuditTrail");
if (type == "Document") tables.Add("Extended_Document");
else if (type == "Reconciliation") tables.Add("Account");
_sortOrder = "idAuditTrail";
return reportJson(json, auditFilter(auditFlatten(report)), tables.ToArray());
}
/// <summary>
/// Update a journal line
/// </summary>
public override void Update(JObject dataOut)
{
if (dataOut["AccountId"] == null) return; // Can't post if no account
int id = dataOut.AsInt("idDocument");
if (id == 0)
return; // Can't post if no document
bool newTran = id != _tranId;
DocType docType = (DocType)dataOut.AsInt("DocumentTypeId");
if (newTran) {
// New document
_vat = 0;
_vatAmount = 0;
dataOut["DocumentAddress"] = string.Join("\r\n", Enumerable.Range(1, 5).Select(i => dataOut.AsString("Address" + i)).Where(s => !string.IsNullOrEmpty(s)).ToArray());
if (!_module.Database.RecordExists("Document", dataOut.AsInt("idDocument"))) {
dataOut["VatPaid"] = 0;
}
base.Update(dataOut); // Save the document
_tranId = id;
_line = 0;
// Save the last invoice/cheque/etc. no
int number = Utils.ExtractNumber(dataOut.AsString("DocumentIdentifier"));
switch (docType) {
case DocType.Invoice:
case DocType.CreditMemo:
if (number > _lastInvoiceNumber) _lastInvoiceNumber = number;
break;
case DocType.Bill:
case DocType.Credit:
if (number > _lastBillNumber) _lastBillNumber = number;
break;
case DocType.Cheque:
case DocType.CreditCardCharge:
registerNumber(_lastChequeNumber, dataOut.AsInt("AccountId"), number);
break;
case DocType.Deposit:
case DocType.CreditCardCredit:
registerNumber(_lastDepositNumber, dataOut.AsInt("AccountId"), number);
break;
case DocType.GeneralJournal:
if (number > _lastJournalNumber) _lastJournalNumber = number;
break;
}
// Delete any existing lines
_module.Database.Execute("DELETE FROM Line WHERE idLine IN (SELECT idJournal FROM Journal WHERE DocumentId = " + _tranId + ")");
_module.Database.Execute("DELETE FROM Journal WHERE DocumentId = " + _tranId);
}
dataOut["DocumentId"] = _tranId;
dataOut["JournalNum"] = ++_line;
dataOut["Memo"] = dataOut["DocumentMemo"];
_module.Database.Update("Journal", dataOut); // Save the journal
if (dataOut.AsInt("AccountId") == (int)Acct.VATControl) {
// This is the VAT journal
_vatAmount += dataOut.AsDecimal("Amount");
if (_vat != 0) {
// There is already a VAT journal - delete it
_module.Database.Execute("DELETE FROM Line WHERE idLine IN (SELECT idJournal FROM Journal WHERE DocumentId = " + _tranId + " AND JournalNum = " + _vat + ")");
_module.Database.Execute("DELETE FROM Journal WHERE DocumentId = " + _tranId + " AND JournalNum = " + _vat);
_module.Database.Execute("UPDATE Journal SET JournalNum = JournalNum - 1 WHERE DocumentId = " + _tranId + " AND JournalNum > " + _vat);
// Bump this journal a line earlier
dataOut["JournalNum"] = --_line;
dataOut["Amount"] = _vatAmount;
_module.Database.Update("Journal", dataOut);
}
_vat = _line; // Remember, to avoid 2 VAT lines
}
// 2nd and subsequent journals (except VAT journal) have lines
// NB VAT Payments to HMRC have are exceptions - they have a line for the VAT payment
if (!newTran && (_line == 2 || dataOut.AsInt("AccountId") != (int)Acct.VATControl)) {
int sign = AppModule.SignFor(docType);
dataOut["idLine"] = dataOut["idJournal"];
dataOut["LineAmount"] = sign * dataOut.AsDecimal("Amount");
dataOut["Qty"] = sign * dataOut.AsDecimal("Qty");
dataOut["VatRate"] = dataOut.AsDecimal("VatRate");
dataOut["VatAmount"] = sign * dataOut.AsDecimal("VatAmount");
_module.Database.Update("Line", dataOut);
}
}
