Beispiel #1
0
        /// <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 };
 }
Beispiel #3
0
        /// <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
            });
        }