/// <summary> /// Show payment history for a document (either a payment or an invoice/credit) /// </summary> public void PaymentHistory(int id) { Extended_Document document = getDocument <Extended_Document>(id); bool payment; Utils.Check(document.DocumentTypeId != 0, "Document {0} not found", id); switch ((DocType)document.DocumentTypeId) { case DocType.Invoice: case DocType.CreditMemo: case DocType.Bill: case DocType.Credit: payment = false; break; case DocType.Payment: case DocType.BillPayment: payment = true; break; default: throw new CheckException("No Payment History for {0}s", ((DocType)document.DocumentTypeId).UnCamel()); } Record = new JObject().AddRange( "header", document, "detail", Database.Query(@"SELECT * FROM Payments JOIN Extended_Document ON idDocument = " + (payment ? "idPaid" : "idPayment") + @" WHERE " + (payment ? "idPayment" : "idPaid") + " = " + id + @" ORDER BY DocumentDate, idDocument")); }
/// <summary> /// Allocate the next unused cheque number/deposit number/etc. /// </summary> protected void allocateDocumentIdentifier(Extended_Document document, FullAccount acct) { if ((document.idDocument == null || document.idDocument == 0) && document.DocumentIdentifier == "<next>") { DocType type = (DocType)document.DocumentTypeId; int nextDocId = 0; switch (type) { case DocType.Invoice: case DocType.Payment: case DocType.CreditMemo: case DocType.Bill: case DocType.BillPayment: case DocType.Credit: case DocType.GeneralJournal: nextDocId = Settings.NextNumber(type); break; case DocType.Withdrawal: case DocType.Deposit: case DocType.CreditCardCharge: case DocType.CreditCardCredit: nextDocId = acct.NextNumber(type); break; } document.DocumentIdentifier = nextDocId != 0 ? nextDocId.ToString() : ""; } }
/// <summary> /// Delete a document, first checking it is one of the supplied types /// </summary> protected AjaxReturn deleteDocument(int id, params DocType[] allowed) { AjaxReturn result = new AjaxReturn(); Database.BeginTransaction(); Extended_Document record = getDocument <Extended_Document>(id); Utils.Check(record != null && record.idDocument != null, "Record does not exist"); DocType type = checkDocType(record.DocumentTypeId, allowed); if (record.DocumentOutstanding != record.DocumentAmount) { result.error = type.UnCamel() + " has been " + (type == DocType.Payment || type == DocType.BillPayment ? "used to pay or part pay invoices" : "paid or part paid") + " it cannot be deleted"; } else if (record.VatPaid > 0) { result.error = "VAT has been declared on " + type.UnCamel() + " it cannot be deleted"; } else { Database.Audit(AuditType.Delete, "Document", id, getCompleteDocument(record)); Database.Execute("DELETE FROM StockTransaction WHERE idStockTransaction IN (SELECT idJournal FROM Journal WHERE DocumentId = " + id + ")"); Database.Execute("DELETE FROM Line WHERE idLine IN (SELECT idJournal FROM Journal WHERE DocumentId = " + id + ")"); Database.Execute("DELETE FROM Journal WHERE DocumentId = " + id); Database.Execute("DELETE FROM Document WHERE idDocument = " + id); Database.Commit(); result.message = type.UnCamel() + " deleted"; } return(result); }
/// <summary> /// Get a specific document (or a filled in new document) for this account /// </summary> internal JObject GetDocument(int id, DocType type) { Extended_Document header = getDocument <Extended_Document>(id); if (header.idDocument == null) { header.DocumentTypeId = (int)type; header.DocType = type.UnCamel(); header.DocumentDate = Utils.Today; header.DocumentName = ""; if (GetParameters["acct"].IsInteger()) { FullAccount acct = Database.QueryOne <FullAccount>("*", "WHERE idAccount = " + GetParameters["acct"], "Account"); if (acct.idAccount != null) { header.DocumentAccountId = (int)acct.idAccount; header.DocumentAccountName = acct.AccountName; } } } else { checkDocType(header.DocumentTypeId, DocType.Withdrawal, DocType.Deposit, DocType.CreditCardCharge, DocType.CreditCardCredit); } return(new JObject().AddRange("header", header, "detail", Database.Query("idJournal, DocumentId, Line.VatCodeId, VatRate, JournalNum, Journal.AccountId, Memo, LineAmount, VatAmount", "WHERE Journal.DocumentId = " + id + " AND idLine IS NOT NULL ORDER BY JournalNum", "Document", "Journal", "Line"))); }
/// <summary> /// List all journals for this account /// </summary> public IEnumerable <Extended_Document> DetailListing(int id) { Extended_Document last = null; int lastId = 0; foreach (JObject l in Database.Query(@"SELECT Journal.idJournal, Document.*, NameAddress.Name AS DocumentName, DocType, Journal.Cleared, 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 DESC, idDocument DESC")) { Extended_Document line = l.To <Extended_Document>(); if (last != null) { if (lastId == l.AsInt("idJournal")) { last.DocumentAccountName = "-split-"; continue; } yield return(last); last = null; } last = line; lastId = l.AsInt("idJournal"); } if (last != null) { yield return(last); } }
/// <summary> /// Update 1 matched transaction /// </summary> public AjaxReturn StatementMatchingSave(MatchInfo json) { int acct = SessionData.StatementImport.id; checkAccountIsAcctType(acct, AcctType.Bank, AcctType.CreditCard, AcctType.OtherAsset, AcctType.OtherLiability); JObject current = SessionData.StatementImport.import[json.current]; Utils.Check(current != null, "Current not found"); if (json.transaction >= 0) { Extended_Document transaction = SessionData.StatementImport.transactions[json.transaction].ToObject <Extended_Document>(); checkDocType(transaction.DocumentTypeId, DocType.Payment, DocType.BillPayment, DocType.Withdrawal, DocType.Deposit, DocType.CreditCardCharge, DocType.CreditCardCredit, DocType.Transfer, DocType.Subscriptions); } // Save json to session SessionData.StatementMatch = json.ToJToken(); return(new AjaxReturn() { redirect = "statementmatch.html?from=%2Fbanking%2Fstatementmatching.html" }); }
/// <summary> /// Prepare an invoice for printing/saving/emailing /// </summary> Extended_Document prepareInvoice(int id) { Extended_Document header = getDocument <Extended_Document>(id); Utils.Check(header.idDocument != null, "Document not found"); DocType type = (DocType)header.DocumentTypeId; checkNameType(header.DocumentNameAddressId, NameType); Title = Title.Replace("Document", type.UnCamel()); if (SignFor(type) > 0) { header.DocumentAmount = -header.DocumentAmount; header.DocumentOutstanding = -header.DocumentOutstanding; } List <Extended_Line> detail = Database.Query <Extended_Line>("SELECT * FROM Extended_Line WHERE DocumentId = " + id + " ORDER BY JournalNum").ToList(); JObject record = new JObject().AddRange("header", header, "detail", detail); decimal net = 0, vat = 0; foreach (Extended_Line d in detail) { net += d.LineAmount; vat += d.VatAmount; } record["TotalVat"] = vat; record["TotalNet"] = net; record["Total"] = net + vat; Record = record; return(header); }
/// <summary> /// Get a complete document (header and details) by id /// </summary> protected JObject getCompleteDocument(int?id) { Extended_Document doc = getDocument <Extended_Document>(id); if (doc.idDocument == null) { return(null); } return(getCompleteDocument(doc)); }
/// <summary> /// Retrieve a VAT return for review. /// </summary> /// <param name="id">A specific VAT return, or 0 to get one for last quarter</param> public void VatReturn(int id) { // Find the VAT payment to HMRC // It will be a withdrawal, deposit, or credit card equivalent // Journal line 2 will be to VAT control // If no id provided, get the most recently posted one Extended_Document header = Database.QueryOne <Extended_Document>(@"SELECT Extended_Document.* FROM Extended_Document JOIN Journal ON DocumentId = idDocument WHERE AccountId = " + (int)Acct.VATControl + @" AND JournalNum = 2 AND DocumentTypeId " + Database.In(DocType.Withdrawal, DocType.Deposit, DocType.CreditCardCharge, DocType.CreditCardCredit) + (id == 0 ? "" : "AND idDocument = " + id) + @" ORDER BY idDocument DESC"); if (header.idDocument == null) { Utils.Check(id == 0, "VAT return " + id + " not found"); header.DocumentNameAddressId = 1; header.DocumentName = ""; if (Settings.DefaultBankAccount > 0) { Account acc = Database.QueryOne <Account>("*", "WHERE idAccount = " + Settings.DefaultBankAccount, "Account"); if (acc.idAccount != null) { header.DocumentAccountId = (int)acc.idAccount; header.DocumentAccountName = acc.AccountName; } } } // If most recent VAT return is not for this quarter, we will create a new one (later, on save) if (id == 0 && header.DocumentDate < Settings.QuarterStart(Utils.Today)) { header.idDocument = null; } if (header.idDocument == null) { header.DocumentDate = Utils.Today; } VatReturnDocument record = getVatReturn(header.idDocument, header.DocumentDate); if (header.idDocument == null) { header.DocumentMemo = "VAT - FROM " + record.Start.ToString("d") + " To " + record.End.ToString("d"); header.DocumentDate = record.Due; } Record = new JObject().AddRange("return", record, "payment", header, "names", SelectOthers(), "accounts", SelectBankAccounts(), "otherReturns", SelectVatPayments().Reverse() ); }
/// <summary> /// Make sure the DocumentnameAddressId for a document is filled in, /// creating a new record if DocumentName does not already exist in the NameAddress table. /// </summary> /// <param name="nameType">The type the NameAddress record must be (S, C or O)</param> protected void fixNameAddress(Extended_Document document, string nameType) { if (document.DocumentNameAddressId == null || document.DocumentNameAddressId == 0) { document.DocumentNameAddressId = string.IsNullOrWhiteSpace(document.DocumentName) ? 1 : Database.ForeignKey("NameAddress", "Type", nameType, "Name", document.DocumentName, "Address", document.DocumentAddress); } else { checkNameType(document.DocumentNameAddressId, nameType); } }
/// <summary> /// Allocate the next unused cheque number/deposit number/etc. /// </summary> protected void allocateDocumentIdentifier(Extended_Document document) { if ((document.idDocument == null || document.idDocument == 0) && document.DocumentIdentifier == "<next>") { FullAccount acct = null; DocType type = (DocType)document.DocumentTypeId; switch (type) { case DocType.Withdrawal: case DocType.Deposit: case DocType.CreditCardCharge: case DocType.CreditCardCredit: acct = Database.QueryOne <FullAccount>("*", "WHERE idAccount = " + document.DocumentAccountId, "Account"); break; } allocateDocumentIdentifier(document, acct); } }
public void Document(int id) { Extended_Document header = Database.Get <Extended_Document>(id); if (header.idDocument == null) { header.DocumentTypeId = (int)DocType.Subscriptions; header.DocType = "Subscriptions"; header.DocumentDate = Utils.Today; header.DocumentName = ""; if (GetParameters["acct"].IsInteger()) { header.DocumentAccountId = int.Parse(GetParameters["acct"]); } else if (Settings.DefaultBankAccount > 0) { Account acc = Database.QueryOne <Account>("*", "WHERE idAccount = " + Settings.DefaultBankAccount, "Account"); if (acc.idAccount != null) { header.DocumentAccountId = (int)acc.idAccount; header.DocumentAccountName = acc.AccountName; } } } else { checkDocType(header.DocumentTypeId, DocType.Subscriptions); } JObject record = getSubscriptionJournal(header); HeaderDetailForm form = new HeaderDetailForm(this, typeof(SubscriptionJournal), typeof(SubscriptionPayment)); form.Header.Options["table"] = "Document"; form.Header.Options["canDelete"] = string.IsNullOrEmpty(header.Clr); // Following fields will be auto-generated as readonly - we want to edit them form.Header["DocumentAccountId"].MakeSelectable(SelectBankAccounts()); FieldAttribute member = form.Detail["Member"]; member.MakeSelectable(SelectMembers()); member.Type = "autoComplete"; member.Options["mustExist"] = true; Form = form; Record = record; }
public AjaxReturn Email(int id) { Extended_Document header = prepareInvoice(id); NameAddress customer = Database.Get <NameAddress>((int)header.DocumentNameAddressId); Utils.Check(!string.IsNullOrEmpty(Settings.CompanyEmail), "Company has no email address"); Utils.Check(!string.IsNullOrEmpty(customer.Email), "Customer has no email address"); Utils.Check(customer.Email.Contains('@'), "Customer has an invalid email address"); ((JObject)((JObject)Record)["header"])["doctype"] = header.DocType.ToLower(); ((JObject)Record)["customer"] = customer.ToJToken(); string text = LoadTemplate("customer/email.txt", this); string subject = Utils.NextToken(ref text, "\n").Trim(); using (MemoryStream stream = new MemoryStream(Encoding.GetBytes(LoadTemplate("customer/print", this)))) { // Create a message and set up the recipients. MailMessage message = new MailMessage(); message.From = new MailAddress(Settings.CompanyEmail); foreach (string e in customer.Email.Split(',')) { message.To.Add(e); } message.Bcc.Add(Settings.CompanyEmail); message.Subject = subject; message.Body = text; // Create the file attachment for this e-mail message. Attachment data = new Attachment(stream, Settings.CompanyName + "Invoice" + header.DocumentIdentifier + ".html", "text/html"); // Add the file attachment to this e-mail message. message.Attachments.Add(data); //Send the message. SmtpClient client = new SmtpClient(Settings.MailServer); // Add credentials if the SMTP server requires them. client.Credentials = new NetworkCredential(Settings.MailUserName, Settings.MailPassword); client.Port = Settings.MailPort; client.EnableSsl = Settings.MailSSL; client.Send(message); } return(new AjaxReturn() { message = "Email sent to " + customer.Email }); }
/// <summary> /// Get the last document of the given type with NameAddressId == id /// </summary> public object DocumentLast(int id, DocType type) { JObject result = new JObject(); Extended_Document header = Database.QueryOne <Extended_Document>("SELECT * FROM Extended_Document WHERE DocumentTypeId = " + (int)type + " AND DocumentNameAddressId = " + id + " ORDER BY DocumentDate DESC, idDocument DESC"); if (header.idDocument != null) { if (Utils.ExtractNumber(header.DocumentIdentifier) > 0) { header.DocumentIdentifier = ""; } result.AddRange("header", header, "detail", Database.Query("idJournal, DocumentId, Line.VatCodeId, VatRate, JournalNum, Journal.AccountId, Memo, LineAmount, VatAmount, LineAmount + VatAmount AS Gross", "WHERE Journal.DocumentId = " + header.idDocument + " AND idLine IS NOT NULL ORDER BY JournalNum", "Document", "Journal", "Line")); } return(result); }
JObject getSubscriptionJournal(Extended_Document header) { return(new JObject().AddRange( "header", new SubscriptionJournal() { idDocument = header.idDocument, DocumentDate = header.DocumentDate, DocumentAccountId = header.DocumentAccountId, DocumentIdentifier = header.DocumentIdentifier, DocumentAmount = -header.DocumentAmount, DocumentMemo = header.DocumentMemo }, "detail", Database.Query(@"SELECT Journal.NameAddressId, CONCAT(Name, ' (', " + Database.Cast("MemberNo", "CHAR") + @", ')') AS Member, -Amount AS Amount, Memo FROM Journal LEFT JOIN NameAddress ON idNameAddress = Journal.NameAddressId LEFT JOIN Member ON Member.NameAddressId = Journal.NameAddressId WHERE DocumentId = " + (header.idDocument ?? 0) + @" AND JournalNum > 1 ORDER BY JournalNum") )); }
/// <summary> /// Retrieve a General Ledger Journal by id, for editing /// </summary> public void Document(int id) { Extended_Document header = getDocument <Extended_Document>(id); if (header.idDocument == null) { header.DocumentTypeId = (int)DocType.GeneralJournal; header.DocType = DocType.GeneralJournal.UnCamel(); header.DocumentDate = Utils.Today; header.DocumentName = ""; header.DocumentIdentifier = Settings.NextJournalNumber.ToString(); if (GetParameters["acct"].IsInteger()) { FullAccount acct = Database.QueryOne <FullAccount>("*", "WHERE idAccount = " + GetParameters["acct"], "Account"); if (acct.idAccount != null) { header.DocumentAccountId = (int)acct.idAccount; header.DocumentAccountName = acct.AccountName; } } } else { checkDocType(header.DocumentTypeId, DocType.GeneralJournal); } JObject record = new JObject().AddRange("header", header, "detail", Database.Query("idJournal, DocumentId, JournalNum, AccountId, Memo, Amount, NameAddressId, Name", "WHERE Journal.DocumentId = " + id + " ORDER BY JournalNum", "Document", "Journal")); nextPreviousDocument(record, "JOIN Journal ON DocumentId = idDocument WHERE DocumentTypeId = " + (int)DocType.GeneralJournal + (header.DocumentAccountId > 0 ? " AND AccountId = " + header.DocumentAccountId : "")); record.AddRange("Accounts", SelectAllAccounts(), "VatCodes", SelectVatCodes(), "Names", SelectNames()); Record = record; }
/// <summary> /// Retrieve document, or prepare new one /// </summary> public JObject document(int id, DocType type) { Title = Title.Replace("Document", type.UnCamel()); Extended_Document header = getDocument <Extended_Document>(id); if (header.idDocument == null) { header.DocumentTypeId = (int)type; header.DocType = type.UnCamel(); header.DocumentDate = Utils.Today; header.DocumentName = ""; header.DocumentIdentifier = Settings.NextNumber(type).ToString(); if (GetParameters["name"].IsInteger()) { JObject name = Database.QueryOne("*", "WHERE Type = " + Database.Quote(NameType) + " AND idNameAddress = " + GetParameters["name"], "NameAddress"); if (name != null) { checkNameType(name.AsString("Type"), NameType); header.DocumentNameAddressId = name.AsInt("idNameAddress"); header.DocumentAddress = name.AsString("Address"); header.DocumentName = name.AsString("Name"); } } } else { checkDocType(header.DocumentTypeId, type); checkNameType(header.DocumentNameAddressId, NameType); } JObject record = new JObject().AddRange("header", header); nextPreviousDocument(record, "WHERE DocumentTypeId = " + (int)type); record.AddRange("VatCodes", SelectVatCodes(), "Names", SelectNames(NameType)); return(record); }
public void StatementMatch() { Utils.Check(SessionData.StatementMatch != null, "Invalid call to StatementMatch"); MatchInfo match = SessionData.StatementMatch.ToObject <MatchInfo>(); int acct = SessionData.StatementImport.id; Account account = Database.Get <Account>(acct); // The existing transaction to match (or empty record if none) Extended_Document transaction = match.transaction < 0 ? Database.EmptyRecord <Extended_Document>() : SessionData.StatementImport.transactions[match.transaction].ToObject <Extended_Document>(); // The statement transaction dynamic current = SessionData.StatementImport.import[match.current]; Utils.Check(current != null, "No current transaction"); bool same = match.type == "Same"; bool documentHasVat = false; bool payment = false; decimal cAmount = current.Amount; int id = transaction.idDocument ?? 0; DocType type; if (match.transaction >= 0) { type = (DocType)transaction.DocumentTypeId; } else { switch (match.type) { case "Deposit": Utils.Check(account.AccountTypeId == (int)AcctType.Bank || account.AccountTypeId == (int)AcctType.OtherAsset || account.AccountTypeId == (int)AcctType.OtherLiability, "Deposit not to bank account"); type = DocType.Deposit; break; case "CardCredit": Utils.Check(account.AccountTypeId == (int)AcctType.CreditCard, "Credit not to credit card"); type = DocType.CreditCardCredit; break; case "Transfer": type = DocType.Transfer; break; case "CustomerPayment": type = DocType.Payment; break; case "Subscriptions": type = DocType.Subscriptions; break; case "Withdrawal": Utils.Check(account.AccountTypeId == (int)AcctType.Bank || account.AccountTypeId == (int)AcctType.OtherAsset || account.AccountTypeId == (int)AcctType.OtherLiability, "Withdrawal not to bank account"); type = DocType.Withdrawal; break; case "CardCharge": Utils.Check(account.AccountTypeId == (int)AcctType.CreditCard, "Charge not to credit card"); type = DocType.CreditCardCharge; break; case "BillPayment": type = DocType.BillPayment; break; default: throw new CheckException("Unknown match type {0}", match.type); } } GetParameters["acct"] = acct.ToString(); // This bank account string nameType = "O"; // Call appropriate method to get Record, and therefore transaction // Also set Module and Method, so appropriate template is used to display transaction before posting switch (type) { case DocType.Payment: Module = "customer"; Method = "payment"; Customer cust = new Customer() { CopyFrom = this }; cust.Payment(id); this.Record = cust.Record; this.Form = cust.Form; payment = true; nameType = "C"; break; case DocType.BillPayment: Module = "supplier"; Method = "payment"; Supplier supp = new Supplier() { CopyFrom = this }; supp.Payment(id); this.Record = supp.Record; this.Form = supp.Form; payment = true; nameType = "S"; break; case DocType.Withdrawal: case DocType.Deposit: case DocType.CreditCardCharge: case DocType.CreditCardCredit: Method = "document"; Document(id, type); documentHasVat = true; break; case DocType.Transfer: Method = "transfer"; Transfer(id); break; case DocType.Subscriptions: Module = "Members"; Method = "document"; Members member = new Members() { CopyFrom = this }; member.Document(id); this.Record = member.Record; this.Form = member.Form; nameType = "M"; break; default: throw new CheckException("Unexpected document type:{0}", type.UnCamel()); } dynamic record = (JObject)Record; dynamic doc = record.header; if (id == 0 && type == DocType.Transfer && cAmount > 0) { // New transfer in doc.TransferAccountId = acct; doc.DocumentAccountId = 0; doc.DocumentAccountName = ""; } if (string.IsNullOrWhiteSpace(doc.DocumentMemo.ToString())) { // Generate a memo string name = current.Name; string memo = current.Memo; if (string.IsNullOrWhiteSpace(memo)) { memo = name; } else if (!memo.Contains(name)) { memo = name + " " + memo; } doc.DocumentMemo = memo; } if (!same && type != DocType.Subscriptions) { // They want to create a new document - try to guess the DocumentName string name = doc.DocumentName; string currentName = current.Name; currentName = currentName.Split('\n', '\t')[0]; if (string.IsNullOrWhiteSpace(name) || (!payment && name.SimilarTo(currentName) < 0.5)) { doc.DocumentName = currentName; doc.DocumentNameAddressId = 0; float maxsimilarity = 0.4999f; foreach (NameAddress n in Database.Query <NameAddress>("SELECT * FROM NameAddress WHERE Type = " + Database.Quote(nameType) + " AND Name <= " + Database.Quote(currentName) + " AND Name LIKE " + Database.Quote(currentName.Length <= 5 ? currentName : currentName.Substring(0, 5) + "%"))) { float similarity = n.Name.SimilarTo(currentName); if (similarity > maxsimilarity) { doc.DocumentName = n.Name; doc.DocumentNameAddressId = n.idNameAddress; maxsimilarity = similarity; } } } } doc.DocumentDate = current.Date; decimal tAmount = doc.DocumentAmount; decimal diff = Math.Abs(cAmount) - Math.Abs(tAmount); doc.DocumentAmount += diff; if (same) { Utils.Check(diff == 0, "Amounts must be the same"); } else { // New transaction doc.DocumentOutstanding = doc.DocumentAmount; doc.Clr = ""; doc.idDocument = doc.Id = null; if (Utils.ExtractNumber(doc.DocumentIdentifier.ToString()) > 0) { doc.DocumentIdentifier = "<next>"; } } if (string.IsNullOrEmpty(doc.DocumentIdentifier.ToString())) { if (current.Id != null) { doc.DocumentIdentifier = current.Id; } else { int no = Utils.ExtractNumber(current.Name.ToString()); if (no != 0) { doc.DocumentIdentifier = no.ToString(); } } } if (diff != 0 && documentHasVat) { // Adjust first line to account for difference if (record.detail.Count == 0) { record.detail.Add(new InvoiceLine().ToJToken()); } dynamic line = record.detail[0]; decimal val = line.LineAmount + line.VatAmount + diff; if (line.VatRate != 0) { line.VatAmount = Math.Round(val * line.VatRate / (100 + line.VatRate), 2); val -= line.VatAmount; } line.LineAmount = val; } if (payment && !same) { removePayments(record); } record.StatementAccount = acct; if (same) { // Just post the new information if (type == DocType.Transfer) { record = record.header; // Transfer posts header alone } AjaxReturn p = StatementMatchSave((JObject)record); if (p.error == null) { Redirect(p.redirect); // If no error, go on with matching } } }
/// <summary> /// Update a document after editing /// </summary> public AjaxReturn DocumentSave(InvoiceDocument json) { Database.BeginTransaction(); Extended_Document document = json.header; DocType t = checkDocType(document.DocumentTypeId, InvoiceDoc, CreditDoc); JObject oldDoc = getCompleteDocument(document.idDocument); int sign = SignFor(t); Extended_Document original = getDocument(document); decimal vat = 0; decimal net = 0; if (document.idDocument == null) { allocateDocumentIdentifier(document); } foreach (InvoiceLine detail in json.detail) { if ((detail.ProductId == 0 || detail.ProductId == null) && (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); var lineNum = 1; fixNameAddress(document, NameType); if (SecurityOn && Settings.RequireAuthorisation && NameType == "S") { if (Admin && document.Authorised == 0) { document.Authorised = null; } else if (original.Authorised == null || original.Authorised == Session.User.idUser) { document.Authorised = document.Authorised > 0 ? Session.User.idUser : null; } else { document.Authorised = original.Authorised; } } Database.Update(document); // 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.JournalNum = lineNum++; journal.AccountId = (int)LedgerAccount; journal.NameAddressId = document.DocumentNameAddressId; journal.Memo = document.DocumentMemo; journal.Amount += changeInDocumentAmount; journal.Outstanding += changeInDocumentAmount; Database.Update(journal); foreach (InvoiceLine detail in json.detail) { if ((detail.ProductId == 0 || detail.ProductId == null) && (detail.AccountId == 0 || detail.AccountId == null)) { continue; } 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.Outstanding += sign * detail.LineAmount - journal.Amount; journal.Amount = sign * detail.LineAmount; Database.Update(journal); Line line = new Line() { idLine = journal.idJournal, Qty = detail.Qty, ProductId = detail.ProductId, 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) { 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"); 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 }); }
/// <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 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 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 }); }