/// <summary>
/// List all the journals that post to this account, along with document info and the balance after the posting.
/// Detects documents which are splits (i.e. have more than 2 journals) and sets the DocumentAccountName to "-split-"
/// </summary>
protected IEnumerable <JObject> detailsWithBalance(int id)
{
JObject last = null; // previous document
int lastId = 0; // Id of previous journal
decimal balance = 0; // Running total balance
// Query gets all journals to this account, joined to document header
// Then joins to any other journals for this document, so documents with only 2 journals
// will appear once, but if there are more than 2 journals the document will appear more times
foreach (JObject l in Database.Query(@"SELECT Journal.idJournal, Document.*, NameAddress.Name As DocumentName, DocType, Journal.Cleared AS Clr, Journal.Amount As DocumentAmount, AccountName As DocumentAccountName
FROM Journal
LEFT JOIN Document ON idDocument = Journal.DocumentId
LEFT JOIN DocumentType ON DocumentType.idDocumentType = Document.DocumentTypeId
LEFT JOIN NameAddress ON NameAddress.idNameAddress = Journal.NameAddressId
LEFT JOIN Journal AS J ON J.DocumentId = Journal.DocumentId AND J.AccountId <> Journal.AccountId
LEFT JOIN Account ON Account.idAccount = J.AccountId
WHERE Journal.AccountId = " + id + @"
ORDER BY DocumentDate, idDocument"))
{
if (last != null)
{
if (lastId == l.AsInt("idJournal"))
{
// More than 1 line in this document
last["DocumentAccountName"] = "-split-";
// Only emit each journal to this account once
continue;
}
balance += last.AsDecimal("DocumentAmount");
last["Balance"] = balance;
yield return(last);
}
last = l;
lastId = l.AsInt("idJournal");
}
if (last != null)
{
balance += last.AsDecimal("DocumentAmount");
last["Balance"] = balance;
yield return(last);
}
}
public void Reconcile(int id)
{
JObject header = Database.QueryOne("*", "WHERE idAccount = " + id, "Account");
JObject openingBalance = Database.QueryOne("SELECT SUM(Amount) AS OpeningBalance FROM Journal WHERE AccountId = " + id
+ " AND Cleared = 'X'");
header["OpeningBalance"] = openingBalance == null ? 0 : openingBalance.AsDecimal("OpeningBalance");
Title += " - " + header.AsString("AccountName");
checkAccountIsAcctType(id, AcctType.Bank, AcctType.CreditCard,
AcctType.OtherAsset, AcctType.OtherLiability);
Record = new JObject().AddRange("header", header,
"detail", Database.Query(@"SELECT Extended_Document.*, Journal.idJournal, Journal.Cleared, Journal.Amount
FROM Journal
JOIN Extended_Document ON idDocument = DocumentId
WHERE Journal.AccountId = " + id + @"
AND Journal.Cleared <> 'X'
ORDER BY DocumentDate, idDocument"));
}
/// <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.Withdrawal:
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);
}
}
/// <summary>
/// Extract the value directly as a decimal
/// </summary>
public decimal Value(JObject record)
{
return(record.AsDecimal(Names.DataName));
}
public override FieldValue ValueFor(JObject o)
{
return(new FieldValue <decimal>(o.AsDecimal(Names.DataName)));
}