/// <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); }
FieldValue <DateTime, string> period(DateTime date) { switch (Name) { // case "Day": case "Week": date = date.AddDays(-(int)date.DayOfWeek).Date; return(new FieldValue <DateTime, string>(date, date.ToShortDateString())); case "Month": date = new DateTime(date.Year, date.Month, 1); return(new FieldValue <DateTime, string>(date, date.ToString("MMM-yyyy"))); case "Quarter": DateTime year = _settings.YearStart(date); date = _settings.QuarterStart(date); int quarter = (int)Math.Round((date - year).TotalDays / 91.25, 0) + 1; return(new FieldValue <DateTime, string>(date, yearFor(date) + "-Q" + quarter)); case "Year": date = _settings.YearStart(date); return(new FieldValue <DateTime, string>(date, yearFor(date))); } date = date.Date; return(new FieldValue <DateTime, string>(date, date.ToShortDateString())); }
/// <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() ); }
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> /// Find the date of the next period after the argument /// </summary> DateTime nextPeriod(DateTime date) { switch (_x1.Name) { case "Day": return(date.AddDays(1)); case "Week": date = date.AddDays(-(int)date.DayOfWeek); return(date.AddDays(7)); case "Month": return(date.AddMonths(1)); case "Quarter": date = Settings.QuarterStart(date); return(Settings.QuarterStart(date.AddMonths(4))); case "Year": return(date.AddYears(1)); } return(DateTime.MaxValue); }
/// <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 }); }