/// <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>
/// A balance adjustment posts enough to to reach the new balance.
/// Important fields are ExistingBalance and NewBalance
/// </summary>
public void BalanceAdjustment(int id, int acct)
{
checkAccountIsAcctType(acct, AcctType.Investment);
BalanceAdjustmentDocument doc = Database.Get<BalanceAdjustmentDocument>(id);
doc.NewBalance = doc.ExistingBalance = Database.QueryOne(@"SELECT SUM(Amount) AS Amount FROM Journal WHERE AccountId = " + acct).AsDecimal("Amount");
if (doc.idDocument == null) {
doc.DocumentAccountId = acct;
doc.DocumentDate = Utils.Today;
doc.DocumentMemo = "Balance Adjustment";
if(string.IsNullOrEmpty(doc.DocumentIdentifier))
doc.DocumentIdentifier = "Balance Adjustment";
JObject o = Database.QueryOne(@"SELECT J.AccountId, AccountName
FROM Journal
JOIN Document ON idDocument = Journal.DocumentId
JOIN Journal AS J ON J.DocumentId = idDocument AND J.JournalNum = 2
JOIN Account ON idAccount = J.AccountId
WHERE Journal.JournalNum = 1
AND DocumentTypeID IN (" + (int)DocType.Cheque + "," + (int)DocType.Deposit + @")
AND Journal.AccountId = " + acct);
doc.AccountId = o.AsInt("AccountId");
doc.AccountName = o.AsString("AccountName");
doc.NameAddressId = 1;
doc.Name = "";
} else {
checkDocType(doc.DocumentTypeId, DocType.Cheque, DocType.Deposit);
foreach (Journal j in Database.Query<Journal>("SELECT * FROM Journal WHERE DocumentId = " + id)) {
switch (j.JournalNum) {
case 1:
doc.DocumentAccountId = j.AccountId;
doc.NameAddressId = j.NameAddressId;
doc.Amount = j.Amount;
break;
case 2:
doc.AccountId = j.AccountId;
break;
default:
throw new CheckException("Document is not a balance adjustment");
}
}
Utils.Check(acct == doc.DocumentAccountId, "Document is for a different account");
doc.Name = Database.QueryOne("SELECT Name FROM NameAddress WHERE idNameAddress = " + doc.NameAddressId).AsString("Name");
doc.AccountName = Database.QueryOne("SELECT AccountName FROM Account WHERE idAccount = " + doc.AccountId).AsString("AccountName");
doc.ExistingBalance -= doc.Amount;
}
Select s = new Select();
Record = new JObject().AddRange(
"header", doc,
"Accounts", s.Account(""),
"Names", s.Other(""));
}
/// <summary>
/// Retrieve a payment for editing
/// </summary>
public void Payment(int id)
{
PaymentDocument document = getPayment(id);
JObject record = document.ToJObject();
Database.NextPreviousDocument(record, "WHERE DocumentTypeId = " + (int)PaymentDoc);
Select s = new Select();
record.Add("BankAccounts", s.BankAccount(""));
record.Add("Names", s.Name(NameType, ""));
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;
}
public void Product(int id) {
Select sel = new Select();
JObject inUse = Database.QueryOne("SELECT idLine FROM Line WHERE ProductId = " + id);
Record = new JObject().AddRange("header", Database.Get<FullProduct>(id),
"canDelete", inUse == null || inUse.IsAllNull(),
"VatCodes", sel.VatCode(""),
"Accounts", sel.IncomeAccount(""));
}
/// <summary>
/// Set up any report
/// </summary>
/// <param name="json">The posted report parameters</param>
void initialiseReport(JObject json)
{
string reportType = OriginalMethod.ToLower().Replace("post", "");
Utils.Check(json.AsString("ReportType").ToLower() == reportType, "Invalid report type");
dynamic r = SessionData.Report;
if(r == null)
SessionData.Report = r = new JObject();
r.reportType = json;
_fields = new List<ReportField>();
_filters = new List<Filter>();
_sel = new Select();
}
/// <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;
}