/// <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 Buy or Sell document for editing
/// </summary>
public void Document(int id, DocType type)
{
Title = Title.Replace("Document", type.UnCamel());
InvestmentDocument header = getDocument <InvestmentDocument>(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;
header.FeeAccount = Database.QueryOne("SELECT idAccount FROM Account WHERE AccountName = " + Database.Quote(acct.AccountName + " fees")).AsInt("idAccount");
}
}
}
else
{
checkDocType(header.DocumentTypeId, DocType.Buy, DocType.Sell);
List <JObject> journals = Database.Query(@"SELECT *
FROM Journal
LEFT JOIN StockTransaction ON idStockTransaction = idJournal
LEFT JOIN Security ON idSecurity = SecurityId
WHERE JournalNum > 1
AND DocumentId = " + id).ToList();
header.SecurityId = journals[0].AsInt("SecurityId");
header.SecurityName = journals[0].AsString("SecurityName");
header.Quantity = journals[0].AsDouble("Quantity");
header.Price = journals[0].AsDouble("Price");
if (journals.Count > 1)
{
header.FeeAccount = journals[1].AsInt("AccountId");
header.Fee = journals[1].AsDecimal("Amount");
header.FeeMemo = journals[1].AsString("Memo");
}
if (type == DocType.Sell)
{
header.Quantity = -header.Quantity;
}
}
JObject record = new JObject().AddRange("header", header);
nextPreviousDocument(record, "JOIN Journal ON DocumentId = idDocument WHERE DocumentTypeId "
+ Database.In(DocType.Buy, DocType.Sell)
+ (header.DocumentAccountId > 0 ? " AND AccountId = " + header.DocumentAccountId : ""));
record.AddRange("Accounts", SelectExpenseAccounts(),
"Names", SelectOthers(),
"Securities", SelectSecurities());
Record = record;
}
/// <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 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>
/// Ensure a record matching a doc type exists
/// </summary>
void ensureDocTypeExists(DocType enumValue, string nameType, int?primaryAccountid)
{
Table table = TableFor("DocumentType");
JObject d = new JObject().AddRange(table.PrimaryKey.Name, (int)enumValue,
table.Indexes[1].Fields[0].Name, enumValue.UnCamel(),
"NameType", nameType,
"Sign", AppModule.SignFor(enumValue),
"PrimaryAccountId", primaryAccountid);
updateIfChanged(table, d);
}
/// <summary>
/// Get a document for editing
/// </summary>
public void Document(int id, DocType type)
{
Title = Title.Replace("Document", type.UnCamel());
JObject record = GetDocument(id, type);
dynamic header = ((dynamic)record).header;
Database.NextPreviousDocument(record, "JOIN Journal ON DocumentId = idDocument WHERE DocumentTypeId = " + (int)type
+ (header.DocumentAccountId > 0 ? " AND AccountId = " + header.DocumentAccountId : ""));
Select s = new Select();
record.AddRange("Accounts", s.Account(""),
"VatCodes", s.VatCode(""),
"Names", s.Other(""));
Record = record;
}
/// <summary>
/// Get a document for editing
/// </summary>
public void Document(int id, DocType type)
{
Title = Title.Replace("Document", type.UnCamel());
JObject record = GetDocument(id, type);
dynamic header = ((dynamic)record).header;
nextPreviousDocument(record, "JOIN Journal ON DocumentId = idDocument WHERE DocumentTypeId = " + (int)type
+ (header.DocumentAccountId > 0 ? " AND AccountId = " + header.DocumentAccountId : ""));
record.AddRange("Accounts", SelectAccounts(),
"VatCodes", SelectVatCodes(),
"Names", SelectOthers());
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 CustomerSupplier(string nameType, Acct ledgerAccount, DocType invoiceDoc, DocType creditDoc, DocType paymentDoc)
{
NameType = nameType;
Name = NameType.NameType();
LedgerAccount = ledgerAccount;
InvoiceDoc = invoiceDoc;
CreditDoc = creditDoc;
PaymentDoc = paymentDoc;
string module = nameType == "C" ? "/customer/" : "/supplier/";
Menu = new MenuOption[] {
new MenuOption("Listing", module + "default.html"),
new MenuOption("VAT codes", module + "vatcodes.html"),
new MenuOption("New " + Name, module + "detail.html?id=0"),
new MenuOption("New " + InvoiceDoc.UnCamel(), module + "document.html?id=0&type=" + (int)InvoiceDoc),
new MenuOption("New " + CreditDoc.UnCamel(), module + "document.html?id=0&type=" + (int)CreditDoc),
new MenuOption("New " + PaymentDoc.UnCamel(), module + "payment.html?id=0")
};
}
/// <summary>
/// Prepare to memorise a transaction for automatic retrieval and saving later.
/// </summary>
public void Memorise(int id)
{
dynamic record = GetDocument(id, DocType.Withdrawal);
Utils.Check(record.header.idDocument != null, "Document {0} not found", id);
DocType type = (DocType)record.header.DocumentTypeId;
Schedule job = new Schedule()
{
ActionDate = record.header.DocumentDate,
Task = type.UnCamel() + " " + record.header.DocumentAmount.ToString("0.00") + (type == DocType.Withdrawal || type == DocType.CreditCardCharge ? " to " : " from ") + record.header.DocumentName + " " + record.header.DocumentMemo,
Url = "banking/standingordersave",
Parameters = record.ToString(),
RepeatFrequency = 1,
Post = true
};
Module = "home";
Method = "job";
Record = job;
}
/// <summary>
/// Get a Buy or Sell document for editing
/// </summary>
public void Document(int id, DocType type)
{
Title = Title.Replace("Document", type.UnCamel());
InvestmentDocument header = getDocument<InvestmentDocument>(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;
header.FeeAccount = Database.QueryOne("SELECT idAccount FROM Account WHERE AccountName = " + Database.Quote(acct.AccountName + " fees")).AsInt("idAccount");
}
}
} else {
checkDocType(header.DocumentTypeId, DocType.Buy, DocType.Sell);
List<JObject> journals = Database.Query(@"SELECT *
FROM Journal
LEFT JOIN StockTransaction ON idStockTransaction = idJournal
LEFT JOIN Security ON idSecurity = SecurityId
WHERE JournalNum > 1
AND DocumentId = " + id).ToList();
header.SecurityId = journals[0].AsInt("SecurityId");
header.SecurityName = journals[0].AsString("SecurityName");
header.Quantity = journals[0].AsDouble("Quantity");
header.Price = journals[0].AsDouble("Price");
if (journals.Count > 1) {
header.FeeAccount = journals[1].AsInt("AccountId");
header.Fee = journals[1].AsDecimal("Amount");
header.FeeMemo = journals[1].AsString("Memo");
}
if (type == DocType.Sell)
header.Quantity = -header.Quantity;
}
JObject record = new JObject().AddRange("header", header);
Database.NextPreviousDocument(record, "JOIN Journal ON DocumentId = idDocument WHERE DocumentTypeId "
+ Database.In(DocType.Buy, DocType.Sell)
+ (header.DocumentAccountId > 0 ? " AND AccountId = " + header.DocumentAccountId : ""));
Select s = new Select();
record.AddRange("Accounts", s.ExpenseAccount(""),
"Names", s.Other(""),
"Securities", s.Security(""));
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);
Database.NextPreviousDocument(record, "WHERE DocumentTypeId = " + (int)type);
Select s = new Select();
record.AddRange("VatCodes", s.VatCode(""),
"Names", s.Name(NameType, ""));
return record;
}
/// <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.Cheque, 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>
/// Save a matched transaction.
/// May be called direct from StatementMatch for Same transactions,
/// or when the user presses "Save" for other transactions
/// </summary>
public AjaxReturn StatementMatchSave(JObject json)
{
Utils.Check(SessionData.StatementMatch != null, "Invalid call to StatementMatchSave");
MatchInfo match = SessionData.StatementMatch.ToObject <MatchInfo>();
JArray transactions = SessionData.StatementImport.transactions;
dynamic transaction = match.transaction < 0 ? null : SessionData.StatementImport.transactions[match.transaction];
DocType type = match.transaction < 0 ? match.type == "Transfer" ? DocType.Transfer : DocType.Withdrawal :
(DocType)((JObject)transactions[match.transaction]).AsInt("DocumentTypeId");
AjaxReturn result;
switch (type)
{
case DocType.Payment:
result = new Customer()
{
Context = Context,
GetParameters = GetParameters,
PostParameters = PostParameters,
Parameters = Parameters,
}.PaymentSave(json.To <CustomerSupplier.PaymentDocument>());
break;
case DocType.BillPayment:
result = new Supplier()
{
Context = Context,
GetParameters = GetParameters,
PostParameters = PostParameters,
Parameters = Parameters,
}.PaymentSave(json.To <CustomerSupplier.PaymentDocument>());
break;
case DocType.Withdrawal:
case DocType.Deposit:
case DocType.CreditCardCharge:
case DocType.CreditCardCredit:
result = DocumentSave(json.To <BankingDocument>());
break;
case DocType.Transfer:
result = TransferSave(json.To <TransferDocument>());
break;
case DocType.Subscriptions:
result = new Members()
{
Context = Context,
GetParameters = GetParameters,
PostParameters = PostParameters,
Parameters = Parameters,
}.DocumentSave(json.To <Members.SubscriptionDocument>());
break;
default:
throw new CheckException("Unexpected document type:{0}", type.UnCamel());
}
if (result.error == null)
{
if (match.transaction >= 0 && match.type == "Same")
{
transaction.Matched = 1;
}
JArray items = SessionData.StatementImport.import;
items.RemoveAt(match.current);
int acct = SessionData.StatementImport.id;
result.redirect = "/banking/" + (items.Count == 0 ? "detail.html?id=" + acct : "statementmatching.html");
}
return(result);
}
/// <summary>
/// Check type is one of the supplied document types
/// </summary>
protected DocType checkDocType(int?type, params DocType[] allowed)
{
Utils.Check(type != null, "Document Type missing");
DocType t = (DocType)type;
Utils.Check(Array.IndexOf(allowed, t) >= 0, "Cannot use this screen to edit {0}s", t.UnCamel());
return(t);
}
