public override void ImportData(CsvParser csv) { _tranId = 0; _line = 0; // Do the import base.ImportData(csv); // Now update the last cheque numbers, etc. Settings settings = _module.Settings; settings.RegisterNumber(_module, (int)DocType.Invoice, _lastInvoiceNumber); settings.RegisterNumber(_module, (int)DocType.Bill, _lastBillNumber); settings.RegisterNumber(_module, (int)DocType.GeneralJournal, _lastJournalNumber); foreach (int k in _lastChequeNumber.Keys.Union(_lastDepositNumber.Keys)) { FullAccount acct = _module.Database.Get <FullAccount>(k); int n; bool save = false; if (_lastChequeNumber.TryGetValue(k, out n)) { save |= acct.RegisterNumber(DocType.Withdrawal, n); } if (_lastDepositNumber.TryGetValue(k, out n)) { save |= acct.RegisterNumber(DocType.Deposit, n); } if (save) { _module.Database.Update(acct); } } // Try to guess what VAT payments apply to each document foreach (JObject vatPayment in ((AppModule)_module).SelectVatPayments()) { int id = vatPayment.AsInt("id"); DateTime q = settings.QuarterStart(vatPayment.AsDate("value")); _module.Database.Execute(@"UPDATE Document JOIN Journal ON IdDocument = DocumentId JOIN Line ON idLine = idJournal SET VatPaid = " + id + @" WHERE (DocumentTypeId IN (1, 3, 4, 6) OR Line.VatCodeId IS NOT NULL) AND VatPaid = 0 AND idDocument < " + id + @" AND DocumentDate < " + _module.Database.Quote(q)); _module.Database.Execute("UPDATE Document SET VatPaid = " + id + " WHERE idDocument = " + id); } // Set the remainder to null (importer doesn't do nulls) _module.Database.Execute(@"UPDATE Document SET VatPaid = NULL WHERE VatPaid = 0"); }
/// <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> /// 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 }); }