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