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