public override void Update(JObject dataOut) { base.Update(dataOut); string name = dataOut.AsString("AccountName"); Utils.Check(!_keys.Contains(name), "Account {0} has duplicate name", name); _keys.Add(name); string [] parts = name.Split(':'); if(parts.Length > 1) { string key = parts[parts.Length - 1]; Utils.Check(!_keys.Contains(key), "Subaccount of {0} has duplicate name {1}", name, key); _keys.Add(key); } }
public override bool Test(JObject data) { string value = data.AsString(JObjectFieldName); switch (_comparison) { case Comparison.Empty: return string.IsNullOrEmpty(value); case Comparison.NonEmpty: return !string.IsNullOrEmpty(value); case Comparison.Equal: return value == _value; case Comparison.Contains: return value.Contains(_value); case Comparison.StartsWith: return value.StartsWith(_value); case Comparison.EndsWith: return value.EndsWith(_value); default: return true; } }
public override bool Test(JObject data) { string id = data.AsString(JObjectFieldName); return _ids.Contains(id); }
public object AuditHistoryPost(JObject json) { OriginalMethod = json.AsString("ReportType"); Method = OriginalMethod.Substring(5).ToLower(); MethodInfo method = this.GetType().GetMethod(OriginalMethod + "Post", BindingFlags.IgnoreCase | BindingFlags.Public | BindingFlags.Instance); Utils.Check(method != null, "Invalid table {0}", Method); return method.Invoke(this, new object[] { json }); }
/// <summary> /// Remove data from certain tables which repeats in a series of records, and add totals as required /// </summary> /// <param name="tables">List of tables with potentially repeating data</param> /// <returns>Modified list</returns> IEnumerable<JObject> removeRepeatsAndTotal(IEnumerable<JObject> data, params string[] tables) { JObject last = null; JObject spacer = new JObject().AddRange("@class", "totalSpacer"); // All fields in the repeating tables HashSet<string> flds = new HashSet<string>(tables.SelectMany(t => Database.TableFor(t).Fields.Select(f => f.Name))); // Our fields in the repeating tables HashSet<string> fields = new HashSet<string>(); List<string> essentialFields = _fields.Where(f => f.Essential).Select(f => f.Name).ToList(); foreach (string f in _fields.Where(f => tables.Contains(f.Table)).Select(f => f.Name)) fields.Add(f); // One of our fields in a potentially repeating table (need to be at front) foreach (string f in flds.Where(f => !fields.Contains(f))) fields.Add(f); // Rest of potentially repeating fields string[] sortFields = _sortFields == null ? new string[0] : _sortFields.Where(f => fieldFor(f).Include).ToArray(); string[] lastTotalBreak = new string[sortFields.Length + 1]; Dictionary<string, decimal[]> totals = new Dictionary<string, decimal[]>(); string firstStringField = null; if (_total) { // Build list of totalling fields foreach (ReportField f in _fields) { if (!f.Include) continue; string type = f.AsString("type"); if (firstStringField == null && type == "string" && !sortFields.Contains(f.Name)) firstStringField = f.Name; if (f.Name == "VatRate") continue; if (type != "decimal" && type != "double" && type != "credit" && type != "debit") continue; totals[f.Name] = new decimal [sortFields.Length + 1]; } } // Function to generate total record - index is sort field number (sortFields.length for grand total) Func<int, JObject> totalRecord = delegate(int level) { JObject t = new JObject().AddRange("@class", "total"); foreach (string f in totals.Keys.ToList()) { t[f] = totals[f][level]; totals[f][level] = 0; } if (firstStringField != null) t[firstStringField] = level == sortFields.Length ? "Grand Total" : "Total"; if(level < sortFields.Length) t[sortFields[level]] = lastTotalBreak[level]; lastTotalBreak[level] = null; return t; }; foreach (JObject r in data) { JObject record = new JObject(r); JObject id = null; if (essentialFields.Count > 0) { // Make recordId object with essential fields (for user click and expand) id = new JObject(); foreach (string f in essentialFields) { id[f] = record[f]; if (!fields.Contains(f)) record.Remove(f); // Don't want essential fields to interfere with checking for duplicates } } if (last != null) { if (_total) { for (int level = sortFields.Length; level-- > 0; ) { if (record.AsString(sortFields[level]) != lastTotalBreak[level]) { if (lastTotalBreak[level] != null) { // Output totals for this sort field yield return totalRecord(level); yield return spacer; } } } } // Now remove duplicate fields foreach (string f in fields) { if (last.AsString(f) == record.AsString(f)) record.Remove(f); else break; } if (record.IsAllNull()) continue; // Everything repeats - ignore duplicate record } if(id != null) record["recordId"] = id; if (_total) { // Cache total break values for(int level = 0; level < sortFields.Length; level++) lastTotalBreak[level] = r.AsString(sortFields[level]); // Accumulate totals foreach (string f in totals.Keys.ToList()) { decimal v = record.AsDecimal(f); if (f == "Credit") { v = r.AsDecimal("Amount"); v = v < 0 ? -v : 0; } else if (f == "Debit") { v = r.AsDecimal("Amount"); if (v < 0) v = 0; } for(int level = 0; level <= sortFields.Length; level++) totals[f][level] += v; } } last = r; yield return record; } if (_total && last != null) { // Print any pending sort field totals for (int level = sortFields.Length; level-- > 0; ) { if (lastTotalBreak[level] != null) { yield return totalRecord(level); yield return spacer; } } if(_grandTotal) yield return totalRecord(sortFields.Length); } }
/// <summary> /// Set up any report /// </summary> /// <param name="json">The posted report parameters</param> void initialiseReport(JObject json) { string reportType = OriginalMethod.ToLower().Replace("post", ""); Utils.Check(json.AsString("ReportType").ToLower() == reportType, "Invalid report type"); dynamic r = SessionData.Report; if(r == null) SessionData.Report = r = new JObject(); r.reportType = json; _fields = new List<ReportField>(); _filters = new List<Filter>(); _sel = new Select(); }
// TODO: Should be more like fixBalanceSheet, and use common totalling code IEnumerable<JObject> fixProfitAndLoss(IEnumerable<JObject> data) { _total = false; JObject last = null; string lastTotalBreak = null; string lastHeading = null; int sign = 1; 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=Gross Profit, 2=Net Profit) 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; } t["Heading"] = lastHeading; t[_sortOrder] = index == 0 ? "Total " + lastTotalBreak : lastTotalBreak; return t; }; foreach (JObject r in data) { JObject record = new JObject(r); string totalBreak = record.AsString(_sortOrder); string heading = record.AsString("Heading"); if (totalBreak != lastTotalBreak) { if (last != null) { if (lastTotalBreak != null) { // Total and spacer for account type change yield return totalRecord(0); yield return spacer; if (lastHeading != heading) { // Total and spacer for gross profit lastTotalBreak = lastHeading; sign = -1; yield return totalRecord(1); yield return spacer; } } } lastTotalBreak = totalBreak; lastHeading = heading; // New 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; yield return record; } if (last != null) { // Total and spacer for last account type change yield return totalRecord(0); yield return spacer; // Total and spacer for gross profit lastTotalBreak = lastHeading; sign = -1; yield return totalRecord(2); } }
/// <summary> /// Import series of categories (accounts, in our system) /// </summary> void importCategories() { status("Importing Categories"); JObject o = new JObject(); while (getLine()) { switch (_tag) { case "!": return; case "^": // End of record if(!string.IsNullOrEmpty(o.AsString("AccountName"))) _account = (int)_module.Database.ForeignKey("Account", o); o = new JObject(); break; case "N": o["AccountName"] = _accountName = _value; break; case "D": o["AccountDescription"] = _value; break; case "E": o["AccountTypeId"] = (int)AcctType.Expense; break; case "I": o["AccountTypeId"] = (int)AcctType.Income; break; case "B": break; default: throw new CheckException("Unexpected input:{0}", _line); } } }
/// <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); } }
public override JToken Value(Database db, JObject data) { string nameType; switch (data.AsString("Account")) { case "Purchase Ledger": nameType = "S"; break; case "Sales Ledger": nameType = "C"; break; default: string type = data.AsString("Type"); switch (type) { case "Invoice": case "Payment": case "Credit Memo": nameType = "C"; break; case "Bill": case "Bill Pmt": case "Credit": nameType = "S"; break; default: nameType = type.IndexOf("Bill Pmt") == 0 ? "S" : "O"; break; } break; } JObject keyData = new JObject().AddRange("Type", nameType, "Name", base.Value(db, data)); return db.ForeignKey("NameAddress", keyData); }
public override JToken Value(Database db, JObject data) { string d = data.AsString(TheirName); return string.IsNullOrWhiteSpace(d) ? (JToken)null : string.IsNullOrWhiteSpace(Importer.DateFormat) ? DateTime.Parse(d) : DateTime.ParseExact(d, Importer.DateFormat, System.Globalization.CultureInfo.InvariantCulture); }
public override JToken Value(Database db, JObject data) { return string.Join("\r\n", _theirNames.Select(n => data.AsString(n)).Where(d => !string.IsNullOrEmpty(d)).ToArray()); }
void importSecurity() { status("Importing Security"); JObject o = new JObject(); o["PriceDate"] = new DateTime(1900, 1, 1); while (getLine()) { switch (_tag) { case "!": return; case "^": // End of record if(!string.IsNullOrWhiteSpace(o.AsString("SecurityName")) && !string.IsNullOrWhiteSpace(o.AsString("Ticker"))) _module.Database.ForeignKey("Security", o); getLine(); // Get next line for caller to process return; case "N": o["SecurityName"] = _value; break; case "S": // Security stock ticker o["Ticker"] = _value; break; case "T": // Type? (Stock) break; default: throw new CheckException("Unexpected input:{0}", _line); } } }
/// <summary> /// Produce report output from original request, list of records, and list of potentially duplicate tables /// (i.e. parent tables of main record, which you want to look like headings) /// </summary> /// <param name="json">Original request</param> /// <param name="report">Records</param> /// <param name="tables">Potentially duplicate tables</param> /// <returns>Json to send to javascript</returns> public JObject reportJson(JObject json, IEnumerable<JObject> report, params string [] tables) { // Use ReportName as right hand end of page title Title = Regex.Replace(Title, "-[^-]*$", "- " + json.AsString("ReportName")); if (_sortFields != null && _sortFields.Length > 0 && tables.Length > 0) { // SortField table is always potentially duplicated - i.e. all the data from the table for the first sort field // will repeat until sort field changes ReportField sortField = fieldFor(_sortFields[0]); tables[0] = sortField.Table; // Move sort fields to front of field list int p = 0; foreach(string f in _sortFields) positionField(f, p++); if (_split) { // Split report shows sort field data on 1 line, and main data on next line ReportField fld = _fields.FirstOrDefault(f => f.Include && !tables.Contains(f.Table)); if(fld != null) fld["newRow"] = true; } } json["fields"] = _fields.Where(f => !f.Hidden).ToJToken(); json["filters"] = getFilters().ToJToken(); json["sorting"] = new JObject().AddRange( "sort", _sortOrder, "desc", _sortDescending, "total", _total, "split", _split); return new JObject().AddRange( "settings", json, "filters", new JArray(_filters), "sortOrders", _sortOrders, "report", removeRepeatsAndTotal(report, tables) ); }
// 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; } }
protected void CreateRideStatus(JObject o) { var attraction_id=RideId(o.AsString("ride_name")); var idToken = JToken.FromObject(attraction_id); o.Add("attraction_id", idToken); o.Remove("ride_name"); CreateDoc("ride_status", o); }
/// <summary> /// Import account info for a single bank/card/security account /// </summary> void importAccount() { status("Importing Account"); JObject o = new JObject(); while (getLine()) { switch (_tag) { case "!": return; case "^": // End of record if(!string.IsNullOrEmpty(o.AsString("AccountName"))) _account = (int)_module.Database.ForeignKey("Account", o); getLine(); // Get next line for caller to process return; case "N": o["AccountName"] = _accountName = _value; break; case "D": o["AccountDescription"] = _value; break; case "S": // Security stock ticker break; case "T": switch (_value) { case "CCard": o["AccountTypeId"] = (int)AcctType.CreditCard; break; case "Bank": o["AccountTypeId"] = (int)AcctType.Bank; break; case "Stock": case "Invst": o["AccountTypeId"] = (int)AcctType.Investment; break; default: throw new CheckException("Unexpected account type:{0}", _line); } break; default: throw new CheckException("Unexpected input:{0}", _line); } } }