/// <summary> /// Get VAT return data for a specific VAT return (id != null) or the last quarter ending before date (id == null) /// </summary> VatReturnDocument getVatReturn(int?id, DateTime date) { VatReturnDocument record = new VatReturnDocument(); DateTime qe = Settings.QuarterStart(date); record.Start = qe.AddMonths(-3); record.End = qe.AddDays(-1); record.Due = qe.AddMonths(1).AddDays(-1); foreach (JObject r in Database.Query(@"SELECT VatType, SUM(VatAmount) AS Vat, SUM(LineAmount) AS Net FROM Vat_Journal JOIN VatCode ON idVatCode = VatCodeId WHERE " + (id == null ? "(VatPaid IS NULL OR VatPaid < 1) AND DocumentDate < " + Database.Quote(qe) : "VatPaid = " + id) + @" GROUP BY VatType")) { switch (r.AsInt("VatType")) { case -1: record.Sales = r.ToObject <VatReturnLine>(); break; case 1: record.Purchases = r.ToObject <VatReturnLine>(); break; default: throw new CheckException("Invalid VatType {0}", r["VatType"]); } } return(record); }
/// <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> /// 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 }); }