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