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