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