/// <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
});
}