/// <summary>
/// Show payment history for a document (either a payment or an invoice/credit)
/// </summary>
public void PaymentHistory(int id)
{
Extended_Document document = getDocument <Extended_Document>(id);
bool payment;
Utils.Check(document.DocumentTypeId != 0, "Document {0} not found", id);
switch ((DocType)document.DocumentTypeId)
{
case DocType.Invoice:
case DocType.CreditMemo:
case DocType.Bill:
case DocType.Credit:
payment = false;
break;
case DocType.Payment:
case DocType.BillPayment:
payment = true;
break;
default:
throw new CheckException("No Payment History for {0}s", ((DocType)document.DocumentTypeId).UnCamel());
}
Record = new JObject().AddRange(
"header", document,
"detail", Database.Query(@"SELECT * FROM Payments
JOIN Extended_Document ON idDocument = " + (payment ? "idPaid" : "idPayment") + @"
WHERE " + (payment ? "idPayment" : "idPaid") + " = " + id + @"
ORDER BY DocumentDate, idDocument"));
}
/// <summary>
/// Allocate the next unused cheque number/deposit number/etc.
/// </summary>
protected void allocateDocumentIdentifier(Extended_Document document, FullAccount acct)
{
if ((document.idDocument == null || document.idDocument == 0) && document.DocumentIdentifier == "<next>")
{
DocType type = (DocType)document.DocumentTypeId;
int nextDocId = 0;
switch (type)
{
case DocType.Invoice:
case DocType.Payment:
case DocType.CreditMemo:
case DocType.Bill:
case DocType.BillPayment:
case DocType.Credit:
case DocType.GeneralJournal:
nextDocId = Settings.NextNumber(type);
break;
case DocType.Withdrawal:
case DocType.Deposit:
case DocType.CreditCardCharge:
case DocType.CreditCardCredit:
nextDocId = acct.NextNumber(type);
break;
}
document.DocumentIdentifier = nextDocId != 0 ? nextDocId.ToString() : "";
}
}
/// <summary>
/// Delete a document, first checking it is one of the supplied types
/// </summary>
protected AjaxReturn deleteDocument(int id, params DocType[] allowed)
{
AjaxReturn result = new AjaxReturn();
Database.BeginTransaction();
Extended_Document record = getDocument <Extended_Document>(id);
Utils.Check(record != null && record.idDocument != null, "Record does not exist");
DocType type = checkDocType(record.DocumentTypeId, allowed);
if (record.DocumentOutstanding != record.DocumentAmount)
{
result.error = type.UnCamel() + " has been " +
(type == DocType.Payment || type == DocType.BillPayment ? "used to pay or part pay invoices" : "paid or part paid")
+ " it cannot be deleted";
}
else if (record.VatPaid > 0)
{
result.error = "VAT has been declared on " + type.UnCamel() + " it cannot be deleted";
}
else
{
Database.Audit(AuditType.Delete, "Document", id, getCompleteDocument(record));
Database.Execute("DELETE FROM StockTransaction WHERE idStockTransaction IN (SELECT idJournal FROM Journal WHERE DocumentId = " + id + ")");
Database.Execute("DELETE FROM Line WHERE idLine IN (SELECT idJournal FROM Journal WHERE DocumentId = " + id + ")");
Database.Execute("DELETE FROM Journal WHERE DocumentId = " + id);
Database.Execute("DELETE FROM Document WHERE idDocument = " + id);
Database.Commit();
result.message = type.UnCamel() + " deleted";
}
return(result);
}
/// <summary>
/// Get a specific document (or a filled in new document) for this account
/// </summary>
internal JObject GetDocument(int id, DocType type)
{
Extended_Document header = getDocument <Extended_Document>(id);
if (header.idDocument == null)
{
header.DocumentTypeId = (int)type;
header.DocType = type.UnCamel();
header.DocumentDate = Utils.Today;
header.DocumentName = "";
if (GetParameters["acct"].IsInteger())
{
FullAccount acct = Database.QueryOne <FullAccount>("*", "WHERE idAccount = " + GetParameters["acct"], "Account");
if (acct.idAccount != null)
{
header.DocumentAccountId = (int)acct.idAccount;
header.DocumentAccountName = acct.AccountName;
}
}
}
else
{
checkDocType(header.DocumentTypeId, DocType.Withdrawal, DocType.Deposit, DocType.CreditCardCharge, DocType.CreditCardCredit);
}
return(new JObject().AddRange("header", header,
"detail", Database.Query("idJournal, DocumentId, Line.VatCodeId, VatRate, JournalNum, Journal.AccountId, Memo, LineAmount, VatAmount",
"WHERE Journal.DocumentId = " + id + " AND idLine IS NOT NULL ORDER BY JournalNum",
"Document", "Journal", "Line")));
}
/// <summary>
/// List all journals for this account
/// </summary>
public IEnumerable <Extended_Document> DetailListing(int id)
{
Extended_Document last = null;
int lastId = 0;
foreach (JObject l in Database.Query(@"SELECT Journal.idJournal, Document.*, NameAddress.Name AS DocumentName, DocType, Journal.Cleared, Journal.Amount AS DocumentAmount, AccountName AS DocumentAccountName
FROM Journal
LEFT JOIN Document ON idDocument = Journal.DocumentId
LEFT JOIN DocumentType ON DocumentType.idDocumentType = Document.DocumentTypeId
LEFT JOIN NameAddress ON NameAddress.idNameAddress = Journal.NameAddressId
LEFT JOIN Journal AS J ON J.DocumentId = Journal.DocumentId AND J.AccountId <> Journal.AccountId
LEFT JOIN Account ON Account.idAccount = J.AccountId
WHERE Journal.AccountId = " + id + @"
ORDER BY DocumentDate DESC, idDocument DESC"))
{
Extended_Document line = l.To <Extended_Document>();
if (last != null)
{
if (lastId == l.AsInt("idJournal"))
{
last.DocumentAccountName = "-split-";
continue;
}
yield return(last);
last = null;
}
last = line;
lastId = l.AsInt("idJournal");
}
if (last != null)
{
yield return(last);
}
}
/// <summary>
/// Update 1 matched transaction
/// </summary>
public AjaxReturn StatementMatchingSave(MatchInfo json)
{
int acct = SessionData.StatementImport.id;
checkAccountIsAcctType(acct, AcctType.Bank, AcctType.CreditCard, AcctType.OtherAsset, AcctType.OtherLiability);
JObject current = SessionData.StatementImport.import[json.current];
Utils.Check(current != null, "Current not found");
if (json.transaction >= 0)
{
Extended_Document transaction = SessionData.StatementImport.transactions[json.transaction].ToObject <Extended_Document>();
checkDocType(transaction.DocumentTypeId,
DocType.Payment,
DocType.BillPayment,
DocType.Withdrawal,
DocType.Deposit,
DocType.CreditCardCharge,
DocType.CreditCardCredit,
DocType.Transfer,
DocType.Subscriptions);
}
// Save json to session
SessionData.StatementMatch = json.ToJToken();
return(new AjaxReturn()
{
redirect = "statementmatch.html?from=%2Fbanking%2Fstatementmatching.html"
});
}
/// <summary>
/// Prepare an invoice for printing/saving/emailing
/// </summary>
Extended_Document prepareInvoice(int id)
{
Extended_Document header = getDocument <Extended_Document>(id);
Utils.Check(header.idDocument != null, "Document not found");
DocType type = (DocType)header.DocumentTypeId;
checkNameType(header.DocumentNameAddressId, NameType);
Title = Title.Replace("Document", type.UnCamel());
if (SignFor(type) > 0)
{
header.DocumentAmount = -header.DocumentAmount;
header.DocumentOutstanding = -header.DocumentOutstanding;
}
List <Extended_Line> detail = Database.Query <Extended_Line>("SELECT * FROM Extended_Line WHERE DocumentId = " + id + " ORDER BY JournalNum").ToList();
JObject record = new JObject().AddRange("header", header, "detail", detail);
decimal net = 0, vat = 0;
foreach (Extended_Line d in detail)
{
net += d.LineAmount;
vat += d.VatAmount;
}
record["TotalVat"] = vat;
record["TotalNet"] = net;
record["Total"] = net + vat;
Record = record;
return(header);
}
/// <summary>
/// Get a complete document (header and details) by id
/// </summary>
protected JObject getCompleteDocument(int?id)
{
Extended_Document doc = getDocument <Extended_Document>(id);
if (doc.idDocument == null)
{
return(null);
}
return(getCompleteDocument(doc));
}
/// <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>
/// Make sure the DocumentnameAddressId for a document is filled in,
/// creating a new record if DocumentName does not already exist in the NameAddress table.
/// </summary>
/// <param name="nameType">The type the NameAddress record must be (S, C or O)</param>
protected void fixNameAddress(Extended_Document document, string nameType)
{
if (document.DocumentNameAddressId == null || document.DocumentNameAddressId == 0)
{
document.DocumentNameAddressId = string.IsNullOrWhiteSpace(document.DocumentName) ? 1 :
Database.ForeignKey("NameAddress",
"Type", nameType,
"Name", document.DocumentName,
"Address", document.DocumentAddress);
}
else
{
checkNameType(document.DocumentNameAddressId, nameType);
}
}
/// <summary>
/// Allocate the next unused cheque number/deposit number/etc.
/// </summary>
protected void allocateDocumentIdentifier(Extended_Document document)
{
if ((document.idDocument == null || document.idDocument == 0) && document.DocumentIdentifier == "<next>")
{
FullAccount acct = null;
DocType type = (DocType)document.DocumentTypeId;
switch (type)
{
case DocType.Withdrawal:
case DocType.Deposit:
case DocType.CreditCardCharge:
case DocType.CreditCardCredit:
acct = Database.QueryOne <FullAccount>("*", "WHERE idAccount = " + document.DocumentAccountId, "Account");
break;
}
allocateDocumentIdentifier(document, acct);
}
}
public void Document(int id)
{
Extended_Document header = Database.Get <Extended_Document>(id);
if (header.idDocument == null)
{
header.DocumentTypeId = (int)DocType.Subscriptions;
header.DocType = "Subscriptions";
header.DocumentDate = Utils.Today;
header.DocumentName = "";
if (GetParameters["acct"].IsInteger())
{
header.DocumentAccountId = int.Parse(GetParameters["acct"]);
}
else 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;
}
}
}
else
{
checkDocType(header.DocumentTypeId, DocType.Subscriptions);
}
JObject record = getSubscriptionJournal(header);
HeaderDetailForm form = new HeaderDetailForm(this, typeof(SubscriptionJournal), typeof(SubscriptionPayment));
form.Header.Options["table"] = "Document";
form.Header.Options["canDelete"] = string.IsNullOrEmpty(header.Clr);
// Following fields will be auto-generated as readonly - we want to edit them
form.Header["DocumentAccountId"].MakeSelectable(SelectBankAccounts());
FieldAttribute member = form.Detail["Member"];
member.MakeSelectable(SelectMembers());
member.Type = "autoComplete";
member.Options["mustExist"] = true;
Form = form;
Record = record;
}
public AjaxReturn Email(int id)
{
Extended_Document header = prepareInvoice(id);
NameAddress customer = Database.Get <NameAddress>((int)header.DocumentNameAddressId);
Utils.Check(!string.IsNullOrEmpty(Settings.CompanyEmail), "Company has no email address");
Utils.Check(!string.IsNullOrEmpty(customer.Email), "Customer has no email address");
Utils.Check(customer.Email.Contains('@'), "Customer has an invalid email address");
((JObject)((JObject)Record)["header"])["doctype"] = header.DocType.ToLower();
((JObject)Record)["customer"] = customer.ToJToken();
string text = LoadTemplate("customer/email.txt", this);
string subject = Utils.NextToken(ref text, "\n").Trim();
using (MemoryStream stream = new MemoryStream(Encoding.GetBytes(LoadTemplate("customer/print", this)))) {
// Create a message and set up the recipients.
MailMessage message = new MailMessage();
message.From = new MailAddress(Settings.CompanyEmail);
foreach (string e in customer.Email.Split(','))
{
message.To.Add(e);
}
message.Bcc.Add(Settings.CompanyEmail);
message.Subject = subject;
message.Body = text;
// Create the file attachment for this e-mail message.
Attachment data = new Attachment(stream, Settings.CompanyName + "Invoice" + header.DocumentIdentifier + ".html", "text/html");
// Add the file attachment to this e-mail message.
message.Attachments.Add(data);
//Send the message.
SmtpClient client = new SmtpClient(Settings.MailServer);
// Add credentials if the SMTP server requires them.
client.Credentials = new NetworkCredential(Settings.MailUserName, Settings.MailPassword);
client.Port = Settings.MailPort;
client.EnableSsl = Settings.MailSSL;
client.Send(message);
}
return(new AjaxReturn()
{
message = "Email sent to " + customer.Email
});
}
/// <summary>
/// Get the last document of the given type with NameAddressId == id
/// </summary>
public object DocumentLast(int id, DocType type)
{
JObject result = new JObject();
Extended_Document header = Database.QueryOne <Extended_Document>("SELECT * FROM Extended_Document WHERE DocumentTypeId = " + (int)type
+ " AND DocumentNameAddressId = " + id
+ " ORDER BY DocumentDate DESC, idDocument DESC");
if (header.idDocument != null)
{
if (Utils.ExtractNumber(header.DocumentIdentifier) > 0)
{
header.DocumentIdentifier = "";
}
result.AddRange("header", header,
"detail", Database.Query("idJournal, DocumentId, Line.VatCodeId, VatRate, JournalNum, Journal.AccountId, Memo, LineAmount, VatAmount, LineAmount + VatAmount AS Gross",
"WHERE Journal.DocumentId = " + header.idDocument + " AND idLine IS NOT NULL ORDER BY JournalNum",
"Document", "Journal", "Line"));
}
return(result);
}
JObject getSubscriptionJournal(Extended_Document header)
{
return(new JObject().AddRange(
"header", new SubscriptionJournal()
{
idDocument = header.idDocument,
DocumentDate = header.DocumentDate,
DocumentAccountId = header.DocumentAccountId,
DocumentIdentifier = header.DocumentIdentifier,
DocumentAmount = -header.DocumentAmount,
DocumentMemo = header.DocumentMemo
},
"detail", Database.Query(@"SELECT Journal.NameAddressId, CONCAT(Name, ' (', " + Database.Cast("MemberNo", "CHAR") + @", ')') AS Member, -Amount AS Amount, Memo
FROM Journal
LEFT JOIN NameAddress ON idNameAddress = Journal.NameAddressId
LEFT JOIN Member ON Member.NameAddressId = Journal.NameAddressId
WHERE DocumentId = " + (header.idDocument ?? 0) + @"
AND JournalNum > 1
ORDER BY JournalNum")
));
}
/// <summary>
/// Retrieve a General Ledger Journal by id, for editing
/// </summary>
public void Document(int id)
{
Extended_Document header = getDocument <Extended_Document>(id);
if (header.idDocument == null)
{
header.DocumentTypeId = (int)DocType.GeneralJournal;
header.DocType = DocType.GeneralJournal.UnCamel();
header.DocumentDate = Utils.Today;
header.DocumentName = "";
header.DocumentIdentifier = Settings.NextJournalNumber.ToString();
if (GetParameters["acct"].IsInteger())
{
FullAccount acct = Database.QueryOne <FullAccount>("*", "WHERE idAccount = " + GetParameters["acct"], "Account");
if (acct.idAccount != null)
{
header.DocumentAccountId = (int)acct.idAccount;
header.DocumentAccountName = acct.AccountName;
}
}
}
else
{
checkDocType(header.DocumentTypeId, DocType.GeneralJournal);
}
JObject record = new JObject().AddRange("header", header,
"detail", Database.Query("idJournal, DocumentId, JournalNum, AccountId, Memo, Amount, NameAddressId, Name",
"WHERE Journal.DocumentId = " + id + " ORDER BY JournalNum",
"Document", "Journal"));
nextPreviousDocument(record, "JOIN Journal ON DocumentId = idDocument WHERE DocumentTypeId = " + (int)DocType.GeneralJournal
+ (header.DocumentAccountId > 0 ? " AND AccountId = " + header.DocumentAccountId : ""));
record.AddRange("Accounts", SelectAllAccounts(),
"VatCodes", SelectVatCodes(),
"Names", SelectNames());
Record = record;
}
/// <summary>
/// Retrieve document, or prepare new one
/// </summary>
public JObject document(int id, DocType type)
{
Title = Title.Replace("Document", type.UnCamel());
Extended_Document header = getDocument <Extended_Document>(id);
if (header.idDocument == null)
{
header.DocumentTypeId = (int)type;
header.DocType = type.UnCamel();
header.DocumentDate = Utils.Today;
header.DocumentName = "";
header.DocumentIdentifier = Settings.NextNumber(type).ToString();
if (GetParameters["name"].IsInteger())
{
JObject name = Database.QueryOne("*", "WHERE Type = " + Database.Quote(NameType) + " AND idNameAddress = " + GetParameters["name"], "NameAddress");
if (name != null)
{
checkNameType(name.AsString("Type"), NameType);
header.DocumentNameAddressId = name.AsInt("idNameAddress");
header.DocumentAddress = name.AsString("Address");
header.DocumentName = name.AsString("Name");
}
}
}
else
{
checkDocType(header.DocumentTypeId, type);
checkNameType(header.DocumentNameAddressId, NameType);
}
JObject record = new JObject().AddRange("header", header);
nextPreviousDocument(record, "WHERE DocumentTypeId = " + (int)type);
record.AddRange("VatCodes", SelectVatCodes(),
"Names", SelectNames(NameType));
return(record);
}
public void StatementMatch()
{
Utils.Check(SessionData.StatementMatch != null, "Invalid call to StatementMatch");
MatchInfo match = SessionData.StatementMatch.ToObject <MatchInfo>();
int acct = SessionData.StatementImport.id;
Account account = Database.Get <Account>(acct);
// The existing transaction to match (or empty record if none)
Extended_Document transaction = match.transaction < 0 ? Database.EmptyRecord <Extended_Document>() :
SessionData.StatementImport.transactions[match.transaction].ToObject <Extended_Document>();
// The statement transaction
dynamic current = SessionData.StatementImport.import[match.current];
Utils.Check(current != null, "No current transaction");
bool same = match.type == "Same";
bool documentHasVat = false;
bool payment = false;
decimal cAmount = current.Amount;
int id = transaction.idDocument ?? 0;
DocType type;
if (match.transaction >= 0)
{
type = (DocType)transaction.DocumentTypeId;
}
else
{
switch (match.type)
{
case "Deposit":
Utils.Check(account.AccountTypeId == (int)AcctType.Bank || account.AccountTypeId == (int)AcctType.OtherAsset ||
account.AccountTypeId == (int)AcctType.OtherLiability, "Deposit not to bank account");
type = DocType.Deposit;
break;
case "CardCredit":
Utils.Check(account.AccountTypeId == (int)AcctType.CreditCard, "Credit not to credit card");
type = DocType.CreditCardCredit;
break;
case "Transfer":
type = DocType.Transfer;
break;
case "CustomerPayment":
type = DocType.Payment;
break;
case "Subscriptions":
type = DocType.Subscriptions;
break;
case "Withdrawal":
Utils.Check(account.AccountTypeId == (int)AcctType.Bank || account.AccountTypeId == (int)AcctType.OtherAsset ||
account.AccountTypeId == (int)AcctType.OtherLiability, "Withdrawal not to bank account");
type = DocType.Withdrawal;
break;
case "CardCharge":
Utils.Check(account.AccountTypeId == (int)AcctType.CreditCard, "Charge not to credit card");
type = DocType.CreditCardCharge;
break;
case "BillPayment":
type = DocType.BillPayment;
break;
default:
throw new CheckException("Unknown match type {0}", match.type);
}
}
GetParameters["acct"] = acct.ToString(); // This bank account
string nameType = "O";
// Call appropriate method to get Record, and therefore transaction
// Also set Module and Method, so appropriate template is used to display transaction before posting
switch (type)
{
case DocType.Payment:
Module = "customer";
Method = "payment";
Customer cust = new Customer()
{
CopyFrom = this
};
cust.Payment(id);
this.Record = cust.Record;
this.Form = cust.Form;
payment = true;
nameType = "C";
break;
case DocType.BillPayment:
Module = "supplier";
Method = "payment";
Supplier supp = new Supplier()
{
CopyFrom = this
};
supp.Payment(id);
this.Record = supp.Record;
this.Form = supp.Form;
payment = true;
nameType = "S";
break;
case DocType.Withdrawal:
case DocType.Deposit:
case DocType.CreditCardCharge:
case DocType.CreditCardCredit:
Method = "document";
Document(id, type);
documentHasVat = true;
break;
case DocType.Transfer:
Method = "transfer";
Transfer(id);
break;
case DocType.Subscriptions:
Module = "Members";
Method = "document";
Members member = new Members()
{
CopyFrom = this
};
member.Document(id);
this.Record = member.Record;
this.Form = member.Form;
nameType = "M";
break;
default:
throw new CheckException("Unexpected document type:{0}", type.UnCamel());
}
dynamic record = (JObject)Record;
dynamic doc = record.header;
if (id == 0 && type == DocType.Transfer && cAmount > 0)
{
// New transfer in
doc.TransferAccountId = acct;
doc.DocumentAccountId = 0;
doc.DocumentAccountName = "";
}
if (string.IsNullOrWhiteSpace(doc.DocumentMemo.ToString()))
{
// Generate a memo
string name = current.Name;
string memo = current.Memo;
if (string.IsNullOrWhiteSpace(memo))
{
memo = name;
}
else if (!memo.Contains(name))
{
memo = name + " " + memo;
}
doc.DocumentMemo = memo;
}
if (!same && type != DocType.Subscriptions)
{
// They want to create a new document - try to guess the DocumentName
string name = doc.DocumentName;
string currentName = current.Name;
currentName = currentName.Split('\n', '\t')[0];
if (string.IsNullOrWhiteSpace(name) || (!payment && name.SimilarTo(currentName) < 0.5))
{
doc.DocumentName = currentName;
doc.DocumentNameAddressId = 0;
float maxsimilarity = 0.4999f;
foreach (NameAddress n in Database.Query <NameAddress>("SELECT * FROM NameAddress WHERE Type = " + Database.Quote(nameType)
+ " AND Name <= " + Database.Quote(currentName)
+ " AND Name LIKE " + Database.Quote(currentName.Length <= 5 ? currentName : currentName.Substring(0, 5) + "%")))
{
float similarity = n.Name.SimilarTo(currentName);
if (similarity > maxsimilarity)
{
doc.DocumentName = n.Name;
doc.DocumentNameAddressId = n.idNameAddress;
maxsimilarity = similarity;
}
}
}
}
doc.DocumentDate = current.Date;
decimal tAmount = doc.DocumentAmount;
decimal diff = Math.Abs(cAmount) - Math.Abs(tAmount);
doc.DocumentAmount += diff;
if (same)
{
Utils.Check(diff == 0, "Amounts must be the same");
}
else
{
// New transaction
doc.DocumentOutstanding = doc.DocumentAmount;
doc.Clr = "";
doc.idDocument = doc.Id = null;
if (Utils.ExtractNumber(doc.DocumentIdentifier.ToString()) > 0)
{
doc.DocumentIdentifier = "<next>";
}
}
if (string.IsNullOrEmpty(doc.DocumentIdentifier.ToString()))
{
if (current.Id != null)
{
doc.DocumentIdentifier = current.Id;
}
else
{
int no = Utils.ExtractNumber(current.Name.ToString());
if (no != 0)
{
doc.DocumentIdentifier = no.ToString();
}
}
}
if (diff != 0 && documentHasVat)
{
// Adjust first line to account for difference
if (record.detail.Count == 0)
{
record.detail.Add(new InvoiceLine().ToJToken());
}
dynamic line = record.detail[0];
decimal val = line.LineAmount + line.VatAmount + diff;
if (line.VatRate != 0)
{
line.VatAmount = Math.Round(val * line.VatRate / (100 + line.VatRate), 2);
val -= line.VatAmount;
}
line.LineAmount = val;
}
if (payment && !same)
{
removePayments(record);
}
record.StatementAccount = acct;
if (same)
{
// Just post the new information
if (type == DocType.Transfer)
{
record = record.header; // Transfer posts header alone
}
AjaxReturn p = StatementMatchSave((JObject)record);
if (p.error == null)
{
Redirect(p.redirect); // If no error, go on with matching
}
}
}
/// <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 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 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 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
});
}
