/// <summary>
/// Post a BalanceAdjustment after editing.
/// Transaction amount is NewBalance - ExistingBalance
/// </summary>
public AjaxReturn BalanceAdjustmentPost(BalanceAdjustmentDocument json)
{
checkAccountIsAcctType(json.DocumentAccountId, AcctType.Investment);
Utils.Check(json.AccountId > 0, "No account selected");
// Pointless to post a new transaction that does nothing
Utils.Check(json.idDocument > 0 || json.NewBalance != json.ExistingBalance, "Balance is unchanged");
if (json.NameAddressId == 0)
json.NameAddressId = Database.ForeignKey("NameAddress",
"Type", "O",
"Name", json.Name);
else
checkNameType(json.NameAddressId, "O");
JObject old = getCompleteDocument(json.idDocument);
json.Amount = json.NewBalance - json.ExistingBalance;
json.DocumentTypeId = (int)(json.Amount < 0 ? DocType.Cheque : DocType.Deposit);
Database.BeginTransaction();
Database.Update(json);
Journal j = new Journal();
j.AccountId = json.DocumentAccountId;
j.Outstanding = j.Amount = json.Amount;
j.DocumentId = (int)json.idDocument;
j.JournalNum = 1;
j.Memo = json.DocumentMemo;
j.NameAddressId = json.NameAddressId;
Database.Update(j);
j = new Journal();
j.AccountId = json.AccountId;
j.Outstanding = j.Amount = -json.Amount;
j.DocumentId = (int)json.idDocument;
j.JournalNum = 2;
j.Memo = json.DocumentMemo;
j.NameAddressId = json.NameAddressId;
Database.Update(j);
Line line = Database.Get<Line>((int)j.idJournal);
line.idLine = j.idJournal;
line.LineAmount = Math.Abs(json.Amount);
Database.Update(line);
JObject full = getCompleteDocument(json.idDocument);
Database.AuditUpdate("Document", json.idDocument, old, full);
Database.Commit();
return new AjaxReturn() { message = "Balance adjusted", id = json.idDocument };
}
/// <summary>
/// Update a General Ledger Journal after editing.
/// </summary>
public AjaxReturn DocumentSave(JournalDocument json)
{
Database.BeginTransaction();
Extended_Document document = json.header;
JObject oldDoc = getCompleteDocument(document.idDocument);
checkDocType(document.DocumentTypeId, DocType.GeneralJournal);
allocateDocumentIdentifier(document);
decimal total = 0, vat = 0;
int lineNum = 1;
Database.Update(document);
Settings.RegisterNumber(this, (int?)DocType.GeneralJournal, Utils.ExtractNumber(document.DocumentIdentifier));
// Find any existing VAT record
Journal vatJournal = Database.QueryOne <Journal>("SELECT * FROM Journal WHERE DocumentId = " + document.idDocument
+ " AND AccountId = " + (int)Acct.VATControl + " ORDER BY JournalNum DESC");
JournalDetail vatDetail = null;
if (vatJournal.idJournal != null)
{
Database.Delete("Journal", (int)vatJournal.idJournal, false);
}
foreach (JournalDetail detail in json.detail)
{
if (detail.AccountId == 0)
{
Utils.Check(detail.Amount == 0, "All lines must be allocated to an account");
continue;
}
total += detail.Amount;
if (detail.AccountId == (int)Acct.VATControl)
{
// Vat has to all be posted on the last line
vatDetail = detail;
vat += detail.Amount;
continue;
}
// Get existing journal (if any)
Journal journal = Database.Get(new Journal()
{
DocumentId = (int)document.idDocument,
JournalNum = lineNum
});
detail.Id = journal.Id;
detail.DocumentId = (int)document.idDocument;
detail.JournalNum = lineNum;
if (detail.NameAddressId == null || detail.NameAddressId == 0)
{
detail.NameAddressId = string.IsNullOrWhiteSpace(detail.Name) ?
1 :
Database.ForeignKey("NameAddress",
"Type", "O",
"Name", detail.Name);
}
// Change outstanding by the change in the amount
detail.Outstanding = journal.Outstanding + detail.Amount - journal.Amount;
Database.Update(detail);
if (lineNum > 1)
{
// Create a dummy line record
Line line = new Line()
{
idLine = detail.idJournal,
Qty = 0,
LineAmount = -detail.Amount,
VatCodeId = null,
VatRate = 0,
VatAmount = 0
};
Database.Update(line);
}
lineNum++;
}
Utils.Check(total == 0, "Journal does not balance by {0}", total);
// Delete any lines and journals that were in the old version, but not in the new
Database.Execute("DELETE FROM Line WHERE idLine IN (SELECT idJournal FROM Journal WHERE DocumentId = " + document.idDocument + " AND JournalNum >= " + lineNum + ")");
Database.Execute("DELETE FROM Journal WHERE DocumentId = " + document.idDocument + " AND JournalNum >= " + lineNum);
if (vat != 0 || vatJournal.idJournal != null)
{
// There is, or was, a posting to vat
decimal changeInVatAmount = vat - vatJournal.Amount;
vatJournal.DocumentId = (int)document.idDocument;
vatJournal.AccountId = (int)Acct.VATControl;
if (vatDetail != null)
{
if ((vatDetail.NameAddressId == null || vatDetail.NameAddressId == 0) && !string.IsNullOrWhiteSpace(vatDetail.Name))
{
vatJournal.NameAddressId = Database.ForeignKey("NameAddress",
"Type", "O",
"Name", vatDetail.Name);
}
else
{
vatJournal.NameAddressId = vatDetail.NameAddressId;
}
}
if (vatJournal.NameAddressId == null || vatJournal.NameAddressId == 0)
{
vatJournal.NameAddressId = 1;
}
vatJournal.Memo = "Total VAT";
vatJournal.JournalNum = lineNum++;
vatJournal.Amount = vat;
vatJournal.Outstanding += changeInVatAmount;
Database.Update(vatJournal);
}
// Audit the change
JObject newDoc = getCompleteDocument(document.idDocument);
Database.AuditUpdate("Document", document.idDocument, oldDoc, newDoc);
Settings.RegisterNumber(this, document.DocumentTypeId, Utils.ExtractNumber(document.DocumentIdentifier));
Database.Commit();
return(new AjaxReturn()
{
message = "Journal saved", id = document.idDocument
});
}
/// <summary>
/// Update a VAt Return after review
/// </summary>
public AjaxReturn VatReturnSave(JObject json)
{
Database.BeginTransaction();
Extended_Document header = json["payment"].To <Extended_Document>();
Utils.Check(header.idDocument == null, "Cannot amend existing VAT return");
// Need to go to and back from json to normalize numbers
VatReturnDocument record = getVatReturn(null, Utils.Today).ToString().JsonTo <VatReturnDocument>();
VatReturnDocument r = json["return"].To <VatReturnDocument>();
Utils.Check(record.ToString() == r.ToString(),
"Another user has changed the VAT data - please refresh the page to get the latest data");
FullAccount acct = Database.Get <FullAccount>((int)header.DocumentAccountId);
allocateDocumentIdentifier(header, acct);
fixNameAddress(header, "O");
decimal toPay = record.ToPay;
DocType t;
switch ((AcctType)acct.AccountTypeId)
{
case AcctType.Bank:
t = toPay < 0 ? DocType.Deposit : DocType.Withdrawal;
break;
case AcctType.CreditCard:
t = toPay < 0 ? DocType.CreditCardCredit : DocType.CreditCardCharge;
break;
default:
throw new CheckException("Account missing or invalid");
}
header.DocumentTypeId = (int)t;
Database.Insert(header);
int nextDocid = Utils.ExtractNumber(header.DocumentIdentifier);
if (nextDocid > 0 && acct.RegisterNumber(t, nextDocid))
{
Database.Update(acct);
}
// Flag this document as part of this VAT return
header.VatPaid = header.idDocument;
Database.Update(header);
Journal journal = new Journal()
{
DocumentId = (int)header.idDocument,
AccountId = header.DocumentAccountId,
NameAddressId = header.DocumentNameAddressId,
Memo = header.DocumentMemo,
JournalNum = 1,
Amount = -toPay,
Outstanding = -toPay
};
Database.Insert(journal);
journal.idJournal = null;
journal.AccountId = (int)Acct.VATControl;
journal.JournalNum = 2;
journal.Amount = toPay;
journal.Outstanding = toPay;
Database.Insert(journal);
Line line = new Line()
{
idLine = journal.idJournal,
LineAmount = toPay
};
Database.Insert(line);
// Flag all documents from last quarter as part of this VAT return
Database.Execute(@"UPDATE Document
JOIN Vat_Journal ON Vat_Journal.idDocument = Document.idDocument
SET Document.VatPaid = " + header.idDocument + @"
WHERE (Document.VatPaid IS NULL OR Document.VatPaid < 1)
AND Document.DocumentDate < " + Database.Quote(Settings.QuarterStart(Utils.Today)));
JObject newDoc = getCompleteDocument(header.idDocument);
Database.AuditUpdate("Document", header.idDocument, null, newDoc);
Settings.RegisterNumber(this, header.DocumentTypeId, Utils.ExtractNumber(header.DocumentIdentifier));
Database.Commit();
return(new AjaxReturn()
{
message = "Vat paid", id = header.idDocument
});
}
/// <summary>
/// Set up transaction ready to process
/// </summary>
void startTransaction()
{
_transaction = new Transaction();
_transaction.Line = Line;
_transaction.AccountId = _account;
_detail = new Journal();
_transaction.Journals.Add(_detail);
}
/// <summary>
/// Update Buy/Sell after editing
/// </summary>
public AjaxReturn DocumentSave(InvestmentDocument json)
{
Database.BeginTransaction();
JObject oldDoc = getCompleteDocument(json.idDocument);
DocType t = checkDocType(json.DocumentTypeId, DocType.Buy, DocType.Sell);
FullAccount acct = Database.Get <FullAccount>((int)json.DocumentAccountId);
checkAcctType(acct.AccountTypeId, AcctType.Investment);
int sign = SignFor(t);
fixNameAddress(json, "O");
if (json.SecurityId == 0)
{
Utils.Check(!string.IsNullOrEmpty(json.SecurityName), "No Security Name supplied");
json.SecurityId = Database.ForeignKey("Security", "SecurityName", json.SecurityName);
}
if (string.IsNullOrEmpty(json.DocumentMemo))
{
json.DocumentMemo = json.SecurityName;
}
if (json.idDocument == null)
{
StockPrice p = Database.QueryOne <StockPrice>("SELECT * FROM " + LatestPrice(Database, json.DocumentDate) + " WHERE SecurityId = " + json.SecurityId);
if (p.Price != json.Price)
{
// Stock price is different from file price, and its a new buy/sell - update file price for security date
p.SecurityId = (int)json.SecurityId;
p.Date = json.DocumentDate;
p.Price = json.Price;
Database.Update(p);
}
}
decimal cost = (decimal)Math.Round(json.Price * json.Quantity, 2);
decimal amount = json.Fee + sign * cost;
Database.Update(json);
// First journal is posting to this account
Journal journal = Database.Get(new Journal()
{
DocumentId = (int)json.idDocument,
JournalNum = 1
});
journal.DocumentId = (int)json.idDocument;
journal.AccountId = json.DocumentAccountId;
journal.NameAddressId = json.DocumentNameAddressId;
journal.Memo = json.DocumentMemo;
journal.JournalNum = 1;
journal.Amount = -amount;
journal.Outstanding = -amount;
Database.Update(journal);
// Second journal is to subaccount for this security (account:security)
journal = Database.Get(new Journal()
{
DocumentId = (int)json.idDocument,
JournalNum = 2
});
journal.DocumentId = (int)json.idDocument;
journal.AccountId = (int)Database.ForeignKey("Account",
"AccountName", acct.AccountName + ":" + json.SecurityName,
"AccountTypeId", (int)AcctType.Security);
journal.NameAddressId = json.DocumentNameAddressId;
journal.Memo = json.DocumentMemo;
journal.JournalNum = 2;
journal.Amount = journal.Outstanding = sign * cost;
Database.Update(journal);
// Corresponding line
Line line = Database.Get <Line>((int)journal.idJournal);
line.idLine = journal.idJournal;
line.LineAmount = cost;
Database.Update(line);
// Now update the stock transaction
StockTransaction st = Database.Get <StockTransaction>((int)journal.idJournal);
st.idStockTransaction = journal.idJournal;
st.ParentAccountId = json.DocumentAccountId;
st.SecurityId = (int)json.SecurityId;
st.Price = json.Price;
st.Quantity = sign * json.Quantity;
st.CostPer = Math.Round((double)amount / json.Quantity, 4);
Database.Update(st);
if (json.Fee != 0)
{
// Need another journal and line for the fee
Utils.Check(json.FeeAccount > 0, "No Fee Account supplied");
journal = Database.Get(new Journal()
{
DocumentId = (int)json.idDocument,
JournalNum = 3
});
journal.DocumentId = (int)json.idDocument;
journal.AccountId = (int)json.FeeAccount;
journal.NameAddressId = json.DocumentNameAddressId;
journal.Memo = json.FeeMemo;
journal.JournalNum = 3;
journal.Amount = journal.Outstanding = json.Fee;
Database.Update(journal);
line = Database.Get <Line>((int)journal.idJournal);
line.idLine = journal.idJournal;
line.LineAmount = sign * json.Fee;
Database.Update(line);
}
// Delete any left over lines from the old transaction
Database.Execute("DELETE FROM Line WHERE idLine IN (SELECT idJournal FROM Journal WHERE DocumentId = " + json.idDocument + " AND JournalNum > " + journal.JournalNum + ")");
Database.Execute("DELETE FROM Journal WHERE Documentid = " + json.idDocument + " AND JournalNum > " + journal.JournalNum);
// Audit
JObject newDoc = getCompleteDocument(json.idDocument);
Database.AuditUpdate("Document", json.idDocument, oldDoc, newDoc);
Database.Commit();
return(new AjaxReturn()
{
message = "Document saved", id = json.idDocument
});
}
/// <summary>
/// Post all transactions in _transactions to the database
/// </summary>
void postTransactions()
{
_module.Batch.Record = 0;
_module.Batch.Records = _transactions.Count;
status("Updating database");
foreach (Transaction t in _transactions.OrderBy(t => t.DocumentDate)) {
_module.Batch.Record++;
decimal total = 0;
Line = t.Line;
Utils.Check(t.NameAddressId > 0, "No NameAddressId");
_module.Batch.Record++;
_module.Database.Insert(t);
int docid = (int)t.idDocument;
int sign = t.DocumentTypeId == (int)DocType.Transfer || t.Amount < 0 ? 1 : -1;
Journal j = new Journal();
j.DocumentId = docid;
j.JournalNum = 1;
j.NameAddressId = t.NameAddressId;
j.AccountId = t.AccountId;
j.Cleared = t.Cleared;
j.Memo = t.DocumentMemo;
j.Amount = j.Outstanding = t.Amount;
total += j.Amount;
_module.Database.Insert(j);
for (int i = 0; i < t.Journals.Count; i++) {
Journal d = t.Journals[i];
d.DocumentId = docid;
d.JournalNum = i + 2;
d.NameAddressId = t.NameAddressId;
if(d.AccountId == 0)
d.AccountId = (int)_module.Database.ForeignKey("Account", "AccountName", "Uncategorised", "AccountTypeId", (int)AcctType.Expense);
total += d.Amount;
_module.Database.Insert(d);
Line l = new Line();
l.idLine = d.idJournal;
l.LineAmount = sign * d.Amount;
_module.Database.Insert(l);
if (i == 0 && t.Stock != null) {
t.Stock.idStockTransaction = d.idJournal;
t.Stock.ParentAccountId = t.AccountId;
t.Stock.CostPer = -(double)t.Amount / t.Stock.Quantity;
_module.Database.Insert(t.Stock);
}
}
Utils.Check(total == 0, "Transaction total not zero {0}", t);
}
}
/// <summary>
/// Set up transaction ready to process an investment
/// </summary>
void startInvestment()
{
_transaction = new Transaction();
_transaction.Line = Line;
_transaction.AccountId = _account;
_detail = new Journal();
_transaction.Journals.Add(_detail);
_transaction.Stock = new StockTransaction();
}
void importTransactions(DocType debit, DocType credit)
{
decimal value;
string lAccount = null;
status("Importing Transactions");
// For de-duplicating transfers
_accountsProcessed.Add(_account);
startTransaction();
while (getLine()) {
switch (_tag) {
case "!":
return;
case "^":
addTransaction();
startTransaction();
lAccount = null;
break;
case "C":
_transaction.Cleared = _value;
break;
case "D":
_transaction.DocumentDate = getDate();
break;
case "M": // Memo
if(_transaction.DocumentMemo == null)
_transaction.DocumentMemo = _value;
else if(_transaction.DocumentIdentifier == null)
_transaction.DocumentIdentifier = _value;
break;
case "N":
_transaction.DocumentIdentifier = _value;
break;
case "E": // Split memo
if (_detail.Memo != null)
_transaction.Journals.Add(_detail = new Journal());
_detail.Memo = _value;
break;
case "L": // Category/account
lAccount = _value;
account();
// Generate an initial journal line (in case there are no S split lines)
_detail.Amount = _detail.Outstanding = -_transaction.Amount;
break;
case "S": // Split category/account
if (_value == lAccount && _transaction.Journals.Count == 1) {
// Transaction has both L and S lines, and first S line is same as L
// We must ignore the initial journal line we generated from the L line (in case there were no S lines)
_detail.AccountId = 0;
_detail.Amount = _detail.Outstanding = 0;
}
lAccount = null;
account();
break;
case "P": // Payee
_transaction.Name = _value;
break;
case "T": // Amount
value = decimal.Parse(_value);
_transaction.DocumentTypeId = (int)(value < 0 ? debit : credit);
_transaction.Amount = value;
break;
case "$": // Split amount
value = -decimal.Parse(_value);
if (value == 0) {
// Value is zero - we need to remove this line and go back to previous one
if (_transaction.Journals.Count > 1) {
_detail = _transaction.Journals[_transaction.Journals.Count - 2];
_transaction.Journals.RemoveAt(_transaction.Journals.Count - 1);
} else {
// No previous line - re-initialise this line
_detail = new Journal();
_transaction.Journals[0] = _detail;
}
break;
}
if (_detail.Amount != 0)
_transaction.Journals.Add(_detail = new Journal());
_detail.Amount = value;
_detail.Outstanding = value;
break;
case "U": // ?? Same value as T
break;
case "Y": // Security name
case "Q": // Quantity (of shares)
case "I": // Price
case "O": // Commission cost
default:
throw new CheckException("Unexpected input:{0}", _line);
}
}
}
void importInvestments()
{
decimal value;
status("Importing Investments");
_accountsProcessed.Add(_account);
startInvestment();
while (getLine()) {
switch (_tag) {
case "!":
return;
case "^": // End of record
addInvestment();
startInvestment();
break;
case "D":
_transaction.DocumentDate = getDate();
break;
case "M": // Memo
if (_transaction.DocumentMemo == null)
_transaction.DocumentMemo = _value;
else if (_transaction.DocumentIdentifier == null)
_transaction.DocumentIdentifier = _value;
break;
case "N":
_transaction.DocumentIdentifier = _value;
break;
case "P": // Payee
_transaction.Name = _value;
break;
case "T": // Amount
value = decimal.Parse(_value);
_transaction.DocumentTypeId = (int)(value < 0 ? DocType.Buy : DocType.Sell);
_transaction.Amount = value;
break;
case "Y": // Security name
if (!string.IsNullOrEmpty(_value)) {
_transaction.SecurityName = _value;
_transaction.Stock.SecurityId = (int)_module.Database.ForeignKey("Security", "SecurityName", _value);
}
break;
case "Q": // Quantity (of shares)
_transaction.Stock.Quantity = double.Parse(_value);
break;
case "I": // Price
_transaction.Stock.Price = double.Parse(_value);
break;
case "O": // Commission cost
value = decimal.Parse(_value);
if (value != 0) {
Journal j = new Journal();
_transaction.Journals.Add(j);
j.AccountId = (int)_module.Database.ForeignKey("Account",
"AccountName", _accountName + " fees",
"AccountTypeId", (int)AcctType.OtherExpense);
j.Memo = "Fees";
j.Amount = j.Outstanding = decimal.Parse(_value);
}
break;
case "L": // Category/account
account();
_detail.Amount = _detail.Outstanding = -_transaction.Amount;
break;
case "U": // ?? Same value as T
case "$": // Split amount
break;
case "C": // Cleared
case "E": // Split memo
case "S": // Split category/account
default:
throw new CheckException("Unexpected input:{0}", _line);
}
}
}
/// <summary>
/// Have come across an account.
/// If necessary add a new journal, and set AccountId
/// </summary>
void account()
{
if (string.IsNullOrEmpty(_value))
return;
// Transfers are shown as "[accountname]"
string a = Regex.Replace(_value, @"^\[(.*)\]$", "$1");
if (_detail.AccountId != 0)
_transaction.Journals.Add(_detail = new Journal());
_detail.AccountId = _transactionsOnly ?
(int)_module.Database.LookupKey("Account", "AccountName", a, "AccountTypeId", (int)AcctType.Expense) :
(int)_module.Database.ForeignKey("Account", "AccountName", a, "AccountTypeId", (int)AcctType.Expense);
if (a != _value)
_transaction.DocumentTypeId = (int)DocType.Transfer;
}
/// <summary>
/// Update a document after editing
/// </summary>
public AjaxReturn DocumentSave(BankingDocument json)
{
Database.BeginTransaction();
Extended_Document document = json.header;
JObject oldDoc = getCompleteDocument(document.idDocument);
DocType t = checkDocType(document.DocumentTypeId, DocType.Withdrawal, DocType.Deposit, DocType.CreditCardCharge, DocType.CreditCardCredit);
FullAccount acct = Database.Get <FullAccount>((int)document.DocumentAccountId);
checkAcctType(acct.AccountTypeId, AcctType.Bank, AcctType.CreditCard, AcctType.Investment,
AcctType.OtherAsset, AcctType.OtherLiability);
allocateDocumentIdentifier(document, acct);
int sign = SignFor(t);
Extended_Document original = getDocument(document);
decimal vat = 0;
decimal net = 0;
bool lineVat = false; // Flag to indicate this is a withdrawal to pay the VAT to HMRC
foreach (InvoiceLine detail in json.detail)
{
if (detail.AccountId == 0 || detail.AccountId == null)
{
Utils.Check(detail.LineAmount == 0 && detail.VatAmount == 0, "All lines must be allocated to an account");
continue;
}
net += detail.LineAmount;
vat += detail.VatAmount;
}
Utils.Check(document.DocumentAmount == net + vat, "Document does not balance");
decimal changeInDocumentAmount = -sign * (document.DocumentAmount - original.DocumentAmount);
int lineNum = 1;
fixNameAddress(document, "O");
Database.Update(document);
int nextDocid = Utils.ExtractNumber(document.DocumentIdentifier);
if (nextDocid > 0 && acct.RegisterNumber(t, nextDocid))
{
Database.Update(acct);
}
// Find any existing VAT record
Journal vatJournal = Database.QueryOne <Journal>("SELECT * FROM Journal WHERE DocumentId = " + document.idDocument
+ " AND AccountId = " + (int)Acct.VATControl + " ORDER BY JournalNum DESC");
Journal journal = Database.Get(new Journal()
{
DocumentId = (int)document.idDocument,
JournalNum = lineNum
});
journal.DocumentId = (int)document.idDocument;
journal.AccountId = document.DocumentAccountId;
journal.NameAddressId = document.DocumentNameAddressId;
journal.Memo = document.DocumentMemo;
journal.JournalNum = lineNum++;
journal.Amount += changeInDocumentAmount;
journal.Outstanding += changeInDocumentAmount;
Database.Update(journal);
foreach (InvoiceLine detail in json.detail)
{
if (detail.AccountId == 0 || detail.AccountId == null)
{
continue;
}
Utils.Check(!lineVat, "Withdrawal to VAT account may only have 1 line");
if (detail.AccountId == (int)Acct.VATControl)
{
// This is a VAT payment to HMRC
Utils.Check(lineNum == 2, "Withdrawal to VAT account may only have 1 line");
Utils.Check(vat == 0, "Withdrawal to VAT account may not have a VAT amount");
vat = detail.LineAmount;
lineVat = true;
}
journal = Database.Get(new Journal()
{
DocumentId = (int)document.idDocument,
JournalNum = lineNum
});
journal.DocumentId = (int)document.idDocument;
journal.JournalNum = lineNum++;
journal.AccountId = (int)detail.AccountId;
journal.NameAddressId = document.DocumentNameAddressId;
journal.Memo = detail.Memo;
journal.Amount = sign * detail.LineAmount;
journal.Outstanding = sign * detail.LineAmount;
Database.Update(journal);
Line line = new Line()
{
idLine = journal.idJournal,
Qty = 0,
LineAmount = detail.LineAmount,
VatCodeId = detail.VatCodeId,
VatRate = detail.VatRate,
VatAmount = detail.VatAmount
};
Database.Update(line);
}
Database.Execute("DELETE FROM Line WHERE idLine IN (SELECT idJournal FROM Journal WHERE DocumentId = " + document.idDocument + " AND JournalNum >= " + lineNum + ")");
Database.Execute("DELETE FROM Journal WHERE DocumentId = " + document.idDocument + " AND JournalNum >= " + lineNum);
if (vat != 0 || vatJournal.idJournal != null)
{
// Add the VAT journal at the end
vat *= sign;
decimal changeInVatAmount = vat - vatJournal.Amount;
Utils.Check(document.VatPaid == null || document.VatPaid < 1 || changeInVatAmount == 0, "Cannot alter VAT on this document, it has already been declared");
if (!lineVat)
{
vatJournal.DocumentId = (int)document.idDocument;
vatJournal.AccountId = (int)Acct.VATControl;
vatJournal.NameAddressId = document.DocumentNameAddressId;
vatJournal.Memo = "Total VAT";
vatJournal.JournalNum = lineNum++;
vatJournal.Amount = vat;
vatJournal.Outstanding += changeInVatAmount;
Database.Update(vatJournal);
}
}
JObject newDoc = getCompleteDocument(document.idDocument);
Database.AuditUpdate("Document", document.idDocument, oldDoc, newDoc);
Settings.RegisterNumber(this, document.DocumentTypeId, Utils.ExtractNumber(document.DocumentIdentifier));
Database.Commit();
return(new AjaxReturn()
{
message = "Document saved", id = document.idDocument
});
}
public AjaxReturn DocumentSave(SubscriptionDocument json)
{
AjaxReturn result = new AjaxReturn();
Database.BeginTransaction();
JObject oldDoc = null;
if (json.header.idDocument > 0)
{
oldDoc = getCompleteDocument(json.header.idDocument);
checkDocType(((JObject)oldDoc["header"]).AsInt("DocumentTypeId"), DocType.Subscriptions);
}
Utils.Check(json.header.DocumentAccountId > 0, "Account not supplied");
decimal total = 0;
foreach (SubscriptionPayment detail in json.detail)
{
if (detail.NameAddressId > 0 && detail.Amount != 0)
{
total += detail.Amount;
}
else
{
Utils.Check(detail.Amount == 0, "All lines must be allocated to a member");
}
}
Utils.Check(json.header.DocumentAmount == total, "Document does not balance");
var lineNum = 1;
Document document = new Document()
{
idDocument = json.header.idDocument,
DocumentTypeId = (int)DocType.Subscriptions,
DocumentDate = json.header.DocumentDate,
DocumentIdentifier = json.header.DocumentIdentifier,
DocumentMemo = json.header.DocumentMemo
};
Database.Update(document);
result.id = document.idDocument;
Journal journal = Database.Get(new Journal()
{
DocumentId = (int)document.idDocument,
JournalNum = lineNum
});
journal.DocumentId = (int)document.idDocument;
journal.JournalNum = lineNum++;
journal.AccountId = json.header.DocumentAccountId;
journal.NameAddressId = 1;
journal.Memo = json.header.DocumentMemo;
journal.Amount = json.header.DocumentAmount;
journal.Outstanding = json.header.DocumentAmount;
Database.Update(journal);
foreach (SubscriptionPayment detail in json.detail)
{
if (detail.NameAddressId > 0 && detail.Amount != 0)
{
journal = Database.Get(new Journal()
{
DocumentId = (int)document.idDocument,
JournalNum = lineNum
});
if (journal.idJournal > 0)
{
decimal amount = -journal.Amount;
// Existing payment
if (journal.NameAddressId != detail.NameAddressId)
{
// Changed member
Database.Execute("UPDATE Member SET AmountDue = AmountDue + " + amount + " WHERE NameAddressId = " + journal.NameAddressId);
Database.Execute("UPDATE Member SET AmountDue = AmountDue - " + detail.Amount + " WHERE NameAddressId = " + detail.NameAddressId);
}
else if (amount != detail.Amount)
{
// Just changed amount
Database.Execute("UPDATE Member SET AmountDue = AmountDue - " + (detail.Amount - amount) + " WHERE NameAddressId = " + detail.NameAddressId);
}
}
else
{
Database.Execute("UPDATE Member SET AmountDue = AmountDue - " + detail.Amount + " WHERE NameAddressId = " + detail.NameAddressId);
}
journal.DocumentId = (int)document.idDocument;
journal.JournalNum = lineNum;
journal.AccountId = (int)Acct.SubscriptionsIncome;
checkNameType(detail.NameAddressId, "M");
journal.NameAddressId = detail.NameAddressId;
journal.Memo = detail.Memo;
journal.Outstanding = -detail.Amount;
journal.Amount = -detail.Amount;
Database.Update(journal);
// Create a dummy line record
Line line = new Line()
{
idLine = journal.idJournal,
Qty = 0,
LineAmount = detail.Amount,
VatCodeId = null,
VatRate = 0,
VatAmount = 0
};
Database.Update(line);
lineNum++;
}
}
foreach (Journal j in Database.Query <Journal>("SELECT * FROM Journal WHERE DocumentId = " + document.idDocument + " AND JournalNum >= " + lineNum))
{
decimal amount = -j.Amount;
Database.Execute("UPDATE Member SET AmountDue = AmountDue + " + amount + " WHERE NameAddressId = " + j.NameAddressId);
}
Database.Execute("DELETE FROM Line WHERE idLine IN (SELECT idJournal FROM Journal WHERE DocumentId = " + document.idDocument + " AND JournalNum >= " + lineNum + ")");
Database.Execute("DELETE FROM Journal WHERE DocumentId = " + document.idDocument + " AND JournalNum >= " + lineNum);
JObject newDoc = getCompleteDocument(document.idDocument);
Database.AuditUpdate("Document", document.idDocument, oldDoc, newDoc);
Database.Commit();
result.message = "Subscription Payments saved";
return(result);
}