/// <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>
/// Update Buy/Sell after editing
/// </summary>
public AjaxReturn DocumentPost(InvestmentDocument json)
{
Database.BeginTransaction();
JObject oldDoc = getCompleteDocument(json.idDocument);
DocType t = checkDocType(json.DocumentTypeId, DocType.Buy, DocType.Sell);
FullAccount acct = Database.Get<FullAccount>((int)json.DocumentAccountId);
checkAcctType(acct.AccountTypeId, AcctType.Investment);
int sign = SignFor(t);
fixNameAddress(json, "O");
if(json.SecurityId == 0) {
Utils.Check(!string.IsNullOrEmpty(json.SecurityName), "No Security Name supplied");
json.SecurityId = Database.ForeignKey("Security", "SecurityName", json.SecurityName);
}
if (string.IsNullOrEmpty(json.DocumentMemo))
json.DocumentMemo = json.SecurityName;
if (json.idDocument == null) {
StockPrice p = Database.QueryOne<StockPrice>("SELECT * FROM " + LatestPrice(json.DocumentDate) + " WHERE SecurityId = " + json.SecurityId);
if (p.Price != json.Price) {
// Stock price is different from file price, and its a new buy/sell - update file price for security date
p.SecurityId = (int)json.SecurityId;
p.Date = json.DocumentDate;
p.Price = json.Price;
Database.Update(p);
}
}
decimal cost = (decimal)Math.Round(json.Price * json.Quantity, 2);
decimal amount = json.Fee + sign * cost;
Database.Update(json);
// First journal is posting to this account
Journal journal = Database.Get(new Journal() {
DocumentId = (int)json.idDocument,
JournalNum = 1
});
journal.DocumentId = (int)json.idDocument;
journal.AccountId = json.DocumentAccountId;
journal.NameAddressId = json.DocumentNameAddressId;
journal.Memo = json.DocumentMemo;
journal.JournalNum = 1;
journal.Amount = -amount;
journal.Outstanding = -amount;
Database.Update(journal);
// Second journal is to subaccount for this security (account:security)
journal = Database.Get(new Journal() {
DocumentId = (int)json.idDocument,
JournalNum = 2
});
journal.DocumentId = (int)json.idDocument;
journal.AccountId = (int)Database.ForeignKey("Account",
"AccountName", acct.AccountName + ":" + json.SecurityName,
"AccountTypeId", (int)AcctType.Security);
journal.NameAddressId = json.DocumentNameAddressId;
journal.Memo = json.DocumentMemo;
journal.JournalNum = 2;
journal.Amount = journal.Outstanding = sign * cost;
Database.Update(journal);
// Corresponding line
Line line = Database.Get<Line>((int)journal.idJournal);
line.idLine = journal.idJournal;
line.LineAmount = cost;
Database.Update(line);
// Now update the stock transaction
StockTransaction st = Database.Get<StockTransaction>((int)journal.idJournal);
st.idStockTransaction = journal.idJournal;
st.ParentAccountId = json.DocumentAccountId;
st.SecurityId = (int)json.SecurityId;
st.Price = json.Price;
st.Quantity = sign * json.Quantity;
st.CostPer = Math.Round((double)amount / json.Quantity, 4);
Database.Update(st);
if(json.Fee != 0) {
// Need another journal and line for the fee
Utils.Check(json.FeeAccount > 0, "No Fee Account supplied");
journal = Database.Get(new Journal() {
DocumentId = (int)json.idDocument,
JournalNum = 3
});
journal.DocumentId = (int)json.idDocument;
journal.AccountId = (int)json.FeeAccount;
journal.NameAddressId = json.DocumentNameAddressId;
journal.Memo = json.FeeMemo;
journal.JournalNum = 3;
journal.Amount = journal.Outstanding = json.Fee;
Database.Update(journal);
line = Database.Get<Line>((int)journal.idJournal);
line.idLine = journal.idJournal;
line.LineAmount = sign * json.Fee;
Database.Update(line);
}
// Delete any left over lines from the old transaction
Database.Execute("DELETE FROM Line WHERE idLine IN (SELECT idJournal FROM Journal WHERE DocumentId = " + json.idDocument + " AND JournalNum > " + journal.JournalNum + ")");
Database.Execute("DELETE FROM Journal WHERE Documentid = " + json.idDocument + " AND JournalNum > " + journal.JournalNum);
// Audit
JObject newDoc = getCompleteDocument(json.idDocument);
Database.AuditUpdate("Document", json.idDocument, oldDoc, newDoc);
Database.Commit();
return new AjaxReturn() { message = "Document saved", id = json.idDocument };
}
/// <summary>
/// Update Buy/Sell after editing
/// </summary>
public AjaxReturn DocumentSave(InvestmentDocument json)
{
Database.BeginTransaction();
JObject oldDoc = getCompleteDocument(json.idDocument);
DocType t = checkDocType(json.DocumentTypeId, DocType.Buy, DocType.Sell);
FullAccount acct = Database.Get <FullAccount>((int)json.DocumentAccountId);
checkAcctType(acct.AccountTypeId, AcctType.Investment);
int sign = SignFor(t);
fixNameAddress(json, "O");
if (json.SecurityId == 0)
{
Utils.Check(!string.IsNullOrEmpty(json.SecurityName), "No Security Name supplied");
json.SecurityId = Database.ForeignKey("Security", "SecurityName", json.SecurityName);
}
if (string.IsNullOrEmpty(json.DocumentMemo))
{
json.DocumentMemo = json.SecurityName;
}
if (json.idDocument == null)
{
StockPrice p = Database.QueryOne <StockPrice>("SELECT * FROM " + LatestPrice(Database, json.DocumentDate) + " WHERE SecurityId = " + json.SecurityId);
if (p.Price != json.Price)
{
// Stock price is different from file price, and its a new buy/sell - update file price for security date
p.SecurityId = (int)json.SecurityId;
p.Date = json.DocumentDate;
p.Price = json.Price;
Database.Update(p);
}
}
decimal cost = (decimal)Math.Round(json.Price * json.Quantity, 2);
decimal amount = json.Fee + sign * cost;
Database.Update(json);
// First journal is posting to this account
Journal journal = Database.Get(new Journal()
{
DocumentId = (int)json.idDocument,
JournalNum = 1
});
journal.DocumentId = (int)json.idDocument;
journal.AccountId = json.DocumentAccountId;
journal.NameAddressId = json.DocumentNameAddressId;
journal.Memo = json.DocumentMemo;
journal.JournalNum = 1;
journal.Amount = -amount;
journal.Outstanding = -amount;
Database.Update(journal);
// Second journal is to subaccount for this security (account:security)
journal = Database.Get(new Journal()
{
DocumentId = (int)json.idDocument,
JournalNum = 2
});
journal.DocumentId = (int)json.idDocument;
journal.AccountId = (int)Database.ForeignKey("Account",
"AccountName", acct.AccountName + ":" + json.SecurityName,
"AccountTypeId", (int)AcctType.Security);
journal.NameAddressId = json.DocumentNameAddressId;
journal.Memo = json.DocumentMemo;
journal.JournalNum = 2;
journal.Amount = journal.Outstanding = sign * cost;
Database.Update(journal);
// Corresponding line
Line line = Database.Get <Line>((int)journal.idJournal);
line.idLine = journal.idJournal;
line.LineAmount = cost;
Database.Update(line);
// Now update the stock transaction
StockTransaction st = Database.Get <StockTransaction>((int)journal.idJournal);
st.idStockTransaction = journal.idJournal;
st.ParentAccountId = json.DocumentAccountId;
st.SecurityId = (int)json.SecurityId;
st.Price = json.Price;
st.Quantity = sign * json.Quantity;
st.CostPer = Math.Round((double)amount / json.Quantity, 4);
Database.Update(st);
if (json.Fee != 0)
{
// Need another journal and line for the fee
Utils.Check(json.FeeAccount > 0, "No Fee Account supplied");
journal = Database.Get(new Journal()
{
DocumentId = (int)json.idDocument,
JournalNum = 3
});
journal.DocumentId = (int)json.idDocument;
journal.AccountId = (int)json.FeeAccount;
journal.NameAddressId = json.DocumentNameAddressId;
journal.Memo = json.FeeMemo;
journal.JournalNum = 3;
journal.Amount = journal.Outstanding = json.Fee;
Database.Update(journal);
line = Database.Get <Line>((int)journal.idJournal);
line.idLine = journal.idJournal;
line.LineAmount = sign * json.Fee;
Database.Update(line);
}
// Delete any left over lines from the old transaction
Database.Execute("DELETE FROM Line WHERE idLine IN (SELECT idJournal FROM Journal WHERE DocumentId = " + json.idDocument + " AND JournalNum > " + journal.JournalNum + ")");
Database.Execute("DELETE FROM Journal WHERE Documentid = " + json.idDocument + " AND JournalNum > " + journal.JournalNum);
// Audit
JObject newDoc = getCompleteDocument(json.idDocument);
Database.AuditUpdate("Document", json.idDocument, oldDoc, newDoc);
Database.Commit();
return(new AjaxReturn()
{
message = "Document saved", id = json.idDocument
});
}