Example #1
0
 /// <summary>
 /// Post a BalanceAdjustment after editing. 
 /// Transaction amount is NewBalance - ExistingBalance
 /// </summary>
 public AjaxReturn BalanceAdjustmentPost(BalanceAdjustmentDocument json)
 {
     checkAccountIsAcctType(json.DocumentAccountId, AcctType.Investment);
     Utils.Check(json.AccountId > 0, "No account selected");
     // Pointless to post a new transaction that does nothing
     Utils.Check(json.idDocument > 0 || json.NewBalance != json.ExistingBalance, "Balance is unchanged");
     if (json.NameAddressId == 0)
         json.NameAddressId = Database.ForeignKey("NameAddress",
             "Type", "O",
             "Name", json.Name);
     else
         checkNameType(json.NameAddressId, "O");
     JObject old = getCompleteDocument(json.idDocument);
     json.Amount = json.NewBalance - json.ExistingBalance;
     json.DocumentTypeId = (int)(json.Amount < 0 ? DocType.Cheque : DocType.Deposit);
     Database.BeginTransaction();
     Database.Update(json);
     Journal j = new Journal();
     j.AccountId = json.DocumentAccountId;
     j.Outstanding = j.Amount = json.Amount;
     j.DocumentId = (int)json.idDocument;
     j.JournalNum = 1;
     j.Memo = json.DocumentMemo;
     j.NameAddressId = json.NameAddressId;
     Database.Update(j);
     j = new Journal();
     j.AccountId = json.AccountId;
     j.Outstanding = j.Amount = -json.Amount;
     j.DocumentId = (int)json.idDocument;
     j.JournalNum = 2;
     j.Memo = json.DocumentMemo;
     j.NameAddressId = json.NameAddressId;
     Database.Update(j);
     Line line = Database.Get<Line>((int)j.idJournal);
     line.idLine = j.idJournal;
     line.LineAmount = Math.Abs(json.Amount);
     Database.Update(line);
     JObject full = getCompleteDocument(json.idDocument);
     Database.AuditUpdate("Document", json.idDocument, old, full);
     Database.Commit();
     return new AjaxReturn() { message = "Balance adjusted", id = json.idDocument };
 }
Example #2
0
        /// <summary>
        /// Update a General Ledger Journal after editing.
        /// </summary>
        public AjaxReturn DocumentSave(JournalDocument json)
        {
            Database.BeginTransaction();
            Extended_Document document = json.header;
            JObject           oldDoc   = getCompleteDocument(document.idDocument);

            checkDocType(document.DocumentTypeId, DocType.GeneralJournal);
            allocateDocumentIdentifier(document);
            decimal total = 0, vat = 0;
            int     lineNum = 1;

            Database.Update(document);
            Settings.RegisterNumber(this, (int?)DocType.GeneralJournal, Utils.ExtractNumber(document.DocumentIdentifier));
            // Find any existing VAT record
            Journal vatJournal = Database.QueryOne <Journal>("SELECT * FROM Journal WHERE DocumentId = " + document.idDocument
                                                             + " AND AccountId = " + (int)Acct.VATControl + " ORDER BY JournalNum DESC");
            JournalDetail vatDetail = null;

            if (vatJournal.idJournal != null)
            {
                Database.Delete("Journal", (int)vatJournal.idJournal, false);
            }
            foreach (JournalDetail detail in json.detail)
            {
                if (detail.AccountId == 0)
                {
                    Utils.Check(detail.Amount == 0, "All lines must be allocated to an account");
                    continue;
                }
                total += detail.Amount;
                if (detail.AccountId == (int)Acct.VATControl)
                {
                    // Vat has to all be posted on the last line
                    vatDetail = detail;
                    vat      += detail.Amount;
                    continue;
                }
                // Get existing journal (if any)
                Journal journal = Database.Get(new Journal()
                {
                    DocumentId = (int)document.idDocument,
                    JournalNum = lineNum
                });
                detail.Id         = journal.Id;
                detail.DocumentId = (int)document.idDocument;
                detail.JournalNum = lineNum;
                if (detail.NameAddressId == null || detail.NameAddressId == 0)
                {
                    detail.NameAddressId = string.IsNullOrWhiteSpace(detail.Name) ?
                                           1 :
                                           Database.ForeignKey("NameAddress",
                                                               "Type", "O",
                                                               "Name", detail.Name);
                }
                // Change outstanding by the change in the amount
                detail.Outstanding = journal.Outstanding + detail.Amount - journal.Amount;
                Database.Update(detail);
                if (lineNum > 1)
                {
                    // Create a dummy line record
                    Line line = new Line()
                    {
                        idLine     = detail.idJournal,
                        Qty        = 0,
                        LineAmount = -detail.Amount,
                        VatCodeId  = null,
                        VatRate    = 0,
                        VatAmount  = 0
                    };
                    Database.Update(line);
                }
                lineNum++;
            }
            Utils.Check(total == 0, "Journal does not balance by {0}", total);
            // Delete any lines and journals that were in the old version, but not in the new
            Database.Execute("DELETE FROM Line WHERE idLine IN (SELECT idJournal FROM Journal WHERE DocumentId = " + document.idDocument + " AND JournalNum >= " + lineNum + ")");
            Database.Execute("DELETE FROM Journal WHERE DocumentId = " + document.idDocument + " AND JournalNum >= " + lineNum);
            if (vat != 0 || vatJournal.idJournal != null)
            {
                // There is, or was, a posting to vat
                decimal changeInVatAmount = vat - vatJournal.Amount;
                vatJournal.DocumentId = (int)document.idDocument;
                vatJournal.AccountId  = (int)Acct.VATControl;
                if (vatDetail != null)
                {
                    if ((vatDetail.NameAddressId == null || vatDetail.NameAddressId == 0) && !string.IsNullOrWhiteSpace(vatDetail.Name))
                    {
                        vatJournal.NameAddressId = Database.ForeignKey("NameAddress",
                                                                       "Type", "O",
                                                                       "Name", vatDetail.Name);
                    }
                    else
                    {
                        vatJournal.NameAddressId = vatDetail.NameAddressId;
                    }
                }
                if (vatJournal.NameAddressId == null || vatJournal.NameAddressId == 0)
                {
                    vatJournal.NameAddressId = 1;
                }
                vatJournal.Memo         = "Total VAT";
                vatJournal.JournalNum   = lineNum++;
                vatJournal.Amount       = vat;
                vatJournal.Outstanding += changeInVatAmount;
                Database.Update(vatJournal);
            }
            // Audit the change
            JObject newDoc = getCompleteDocument(document.idDocument);

            Database.AuditUpdate("Document", document.idDocument, oldDoc, newDoc);
            Settings.RegisterNumber(this, document.DocumentTypeId, Utils.ExtractNumber(document.DocumentIdentifier));
            Database.Commit();
            return(new AjaxReturn()
            {
                message = "Journal saved", id = document.idDocument
            });
        }
Example #3
0
        /// <summary>
        /// Update a VAt Return after review
        /// </summary>
        public AjaxReturn VatReturnSave(JObject json)
        {
            Database.BeginTransaction();
            Extended_Document header = json["payment"].To <Extended_Document>();

            Utils.Check(header.idDocument == null, "Cannot amend existing VAT return");
            // Need to go to and back from json to normalize numbers
            VatReturnDocument record = getVatReturn(null, Utils.Today).ToString().JsonTo <VatReturnDocument>();
            VatReturnDocument r      = json["return"].To <VatReturnDocument>();

            Utils.Check(record.ToString() == r.ToString(),
                        "Another user has changed the VAT data - please refresh the page to get the latest data");
            FullAccount acct = Database.Get <FullAccount>((int)header.DocumentAccountId);

            allocateDocumentIdentifier(header, acct);
            fixNameAddress(header, "O");
            decimal toPay = record.ToPay;
            DocType t;

            switch ((AcctType)acct.AccountTypeId)
            {
            case AcctType.Bank:
                t = toPay < 0 ? DocType.Deposit : DocType.Withdrawal;
                break;

            case AcctType.CreditCard:
                t = toPay < 0 ? DocType.CreditCardCredit : DocType.CreditCardCharge;
                break;

            default:
                throw new CheckException("Account missing or invalid");
            }
            header.DocumentTypeId = (int)t;
            Database.Insert(header);
            int nextDocid = Utils.ExtractNumber(header.DocumentIdentifier);

            if (nextDocid > 0 && acct.RegisterNumber(t, nextDocid))
            {
                Database.Update(acct);
            }
            // Flag this document as part of this VAT return
            header.VatPaid = header.idDocument;
            Database.Update(header);
            Journal journal = new Journal()
            {
                DocumentId    = (int)header.idDocument,
                AccountId     = header.DocumentAccountId,
                NameAddressId = header.DocumentNameAddressId,
                Memo          = header.DocumentMemo,
                JournalNum    = 1,
                Amount        = -toPay,
                Outstanding   = -toPay
            };

            Database.Insert(journal);
            journal.idJournal   = null;
            journal.AccountId   = (int)Acct.VATControl;
            journal.JournalNum  = 2;
            journal.Amount      = toPay;
            journal.Outstanding = toPay;
            Database.Insert(journal);
            Line line = new Line()
            {
                idLine     = journal.idJournal,
                LineAmount = toPay
            };

            Database.Insert(line);
            // Flag all documents from last quarter as part of this VAT return
            Database.Execute(@"UPDATE Document
JOIN Vat_Journal ON Vat_Journal.idDocument = Document.idDocument
SET Document.VatPaid = " + header.idDocument + @"
WHERE (Document.VatPaid IS NULL OR Document.VatPaid < 1)
AND Document.DocumentDate < " + Database.Quote(Settings.QuarterStart(Utils.Today)));
            JObject newDoc = getCompleteDocument(header.idDocument);

            Database.AuditUpdate("Document", header.idDocument, null, newDoc);
            Settings.RegisterNumber(this, header.DocumentTypeId, Utils.ExtractNumber(header.DocumentIdentifier));
            Database.Commit();
            return(new AjaxReturn()
            {
                message = "Vat paid", id = header.idDocument
            });
        }
Example #4
0
 /// <summary>
 /// Set up transaction ready to process
 /// </summary>
 void startTransaction()
 {
     _transaction = new Transaction();
     _transaction.Line = Line;
     _transaction.AccountId = _account;
     _detail = new Journal();
     _transaction.Journals.Add(_detail);
 }
Example #5
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
            });
        }
Example #6
0
 /// <summary>
 /// Post all transactions in _transactions to the database
 /// </summary>
 void postTransactions()
 {
     _module.Batch.Record = 0;
     _module.Batch.Records = _transactions.Count;
     status("Updating database");
     foreach (Transaction t in _transactions.OrderBy(t => t.DocumentDate)) {
         _module.Batch.Record++;
         decimal total = 0;
         Line = t.Line;
         Utils.Check(t.NameAddressId > 0, "No NameAddressId");
         _module.Batch.Record++;
         _module.Database.Insert(t);
         int docid = (int)t.idDocument;
         int sign = t.DocumentTypeId == (int)DocType.Transfer || t.Amount < 0 ? 1 : -1;
         Journal j = new Journal();
         j.DocumentId = docid;
         j.JournalNum = 1;
         j.NameAddressId = t.NameAddressId;
         j.AccountId = t.AccountId;
         j.Cleared = t.Cleared;
         j.Memo = t.DocumentMemo;
         j.Amount = j.Outstanding = t.Amount;
         total += j.Amount;
         _module.Database.Insert(j);
         for (int i = 0; i < t.Journals.Count; i++) {
             Journal d = t.Journals[i];
             d.DocumentId = docid;
             d.JournalNum = i + 2;
             d.NameAddressId = t.NameAddressId;
             if(d.AccountId == 0)
                 d.AccountId = (int)_module.Database.ForeignKey("Account", "AccountName", "Uncategorised", "AccountTypeId", (int)AcctType.Expense);
             total += d.Amount;
             _module.Database.Insert(d);
             Line l = new Line();
             l.idLine = d.idJournal;
             l.LineAmount = sign * d.Amount;
             _module.Database.Insert(l);
             if (i == 0 && t.Stock != null) {
                 t.Stock.idStockTransaction = d.idJournal;
                 t.Stock.ParentAccountId = t.AccountId;
                 t.Stock.CostPer = -(double)t.Amount / t.Stock.Quantity;
                 _module.Database.Insert(t.Stock);
             }
         }
         Utils.Check(total == 0, "Transaction total not zero {0}", t);
     }
 }
Example #7
0
 /// <summary>
 /// Set up transaction ready to process an investment
 /// </summary>
 void startInvestment()
 {
     _transaction = new Transaction();
     _transaction.Line = Line;
     _transaction.AccountId = _account;
     _detail = new Journal();
     _transaction.Journals.Add(_detail);
     _transaction.Stock = new StockTransaction();
 }
Example #8
0
 void importTransactions(DocType debit, DocType credit)
 {
     decimal value;
     string lAccount = null;
     status("Importing Transactions");
     // For de-duplicating transfers
     _accountsProcessed.Add(_account);
     startTransaction();
     while (getLine()) {
         switch (_tag) {
             case "!":
                 return;
             case "^":
                 addTransaction();
                 startTransaction();
                 lAccount = null;
                 break;
             case "C":
                 _transaction.Cleared = _value;
                 break;
             case "D":
                 _transaction.DocumentDate = getDate();
                 break;
             case "M":	// Memo
                 if(_transaction.DocumentMemo == null)
                     _transaction.DocumentMemo = _value;
                 else if(_transaction.DocumentIdentifier == null)
                     _transaction.DocumentIdentifier = _value;
                 break;
             case "N":
                 _transaction.DocumentIdentifier = _value;
                 break;
             case "E":	// Split memo
                 if (_detail.Memo != null)
                     _transaction.Journals.Add(_detail = new Journal());
                 _detail.Memo = _value;
                 break;
             case "L":	// Category/account
                 lAccount = _value;
                 account();
                 // Generate an initial journal line (in case there are no S split lines)
                 _detail.Amount = _detail.Outstanding = -_transaction.Amount;
                 break;
             case "S":	// Split category/account
                 if (_value == lAccount && _transaction.Journals.Count == 1) {
                     // Transaction has both L and S lines, and first S line is same as L
                     // We must ignore the initial journal line we generated from the L line (in case there were no S lines)
                     _detail.AccountId = 0;
                     _detail.Amount = _detail.Outstanding = 0;
                 }
                 lAccount = null;
                 account();
                 break;
             case "P":	// Payee
                 _transaction.Name = _value;
                 break;
             case "T":	// Amount
                 value = decimal.Parse(_value);
                 _transaction.DocumentTypeId = (int)(value < 0 ? debit : credit);
                 _transaction.Amount = value;
                 break;
             case "$":	// Split amount
                 value = -decimal.Parse(_value);
                 if (value == 0) {
                     // Value is zero - we need to remove this line and go back to previous one
                     if (_transaction.Journals.Count > 1) {
                         _detail = _transaction.Journals[_transaction.Journals.Count - 2];
                         _transaction.Journals.RemoveAt(_transaction.Journals.Count - 1);
                     } else {
                         // No previous line - re-initialise this line
                         _detail = new Journal();
                         _transaction.Journals[0] = _detail;
                     }
                     break;
                 }
                 if (_detail.Amount != 0)
                     _transaction.Journals.Add(_detail = new Journal());
                 _detail.Amount = value;
                 _detail.Outstanding = value;
                 break;
             case "U":	// ?? Same value as T
                 break;
             case "Y":	// Security name
             case "Q":	// Quantity (of shares)
             case "I":	// Price
             case "O":	// Commission cost
             default:
                 throw new CheckException("Unexpected input:{0}", _line);
         }
     }
 }
Example #9
0
 void importInvestments()
 {
     decimal value;
     status("Importing Investments");
     _accountsProcessed.Add(_account);
     startInvestment();
     while (getLine()) {
         switch (_tag) {
             case "!":
                 return;
             case "^":	// End of record
                 addInvestment();
                 startInvestment();
                 break;
             case "D":
                 _transaction.DocumentDate = getDate();
                 break;
             case "M":	// Memo
                 if (_transaction.DocumentMemo == null)
                     _transaction.DocumentMemo = _value;
                 else if (_transaction.DocumentIdentifier == null)
                     _transaction.DocumentIdentifier = _value;
                 break;
             case "N":
                 _transaction.DocumentIdentifier = _value;
                 break;
             case "P":	// Payee
                 _transaction.Name = _value;
                 break;
             case "T":	// Amount
                 value = decimal.Parse(_value);
                 _transaction.DocumentTypeId = (int)(value < 0 ? DocType.Buy : DocType.Sell);
                 _transaction.Amount = value;
                 break;
             case "Y":	// Security name
                 if (!string.IsNullOrEmpty(_value)) {
                     _transaction.SecurityName = _value;
                     _transaction.Stock.SecurityId = (int)_module.Database.ForeignKey("Security", "SecurityName", _value);
                 }
                 break;
             case "Q":	// Quantity (of shares)
                 _transaction.Stock.Quantity = double.Parse(_value);
                 break;
             case "I":	// Price
                 _transaction.Stock.Price = double.Parse(_value);
                 break;
             case "O":	// Commission cost
                 value = decimal.Parse(_value);
                 if (value != 0) {
                     Journal j = new Journal();
                     _transaction.Journals.Add(j);
                     j.AccountId = (int)_module.Database.ForeignKey("Account",
                         "AccountName", _accountName + " fees",
                         "AccountTypeId", (int)AcctType.OtherExpense);
                     j.Memo = "Fees";
                     j.Amount = j.Outstanding = decimal.Parse(_value);
                 }
                 break;
             case "L":	// Category/account
                 account();
                 _detail.Amount = _detail.Outstanding = -_transaction.Amount;
                 break;
             case "U":	// ?? Same value as T
             case "$":	// Split amount
                 break;
             case "C":	// Cleared
             case "E":	// Split memo
             case "S":	// Split category/account
             default:
                 throw new CheckException("Unexpected input:{0}", _line);
         }
     }
 }
Example #10
0
 /// <summary>
 /// Have come across an account.
 /// If necessary add a new journal, and set AccountId
 /// </summary>
 void account()
 {
     if (string.IsNullOrEmpty(_value))
         return;
     // Transfers are shown as "[accountname]"
     string a = Regex.Replace(_value, @"^\[(.*)\]$", "$1");
     if (_detail.AccountId != 0)
         _transaction.Journals.Add(_detail = new Journal());
     _detail.AccountId = _transactionsOnly ?
         (int)_module.Database.LookupKey("Account", "AccountName", a, "AccountTypeId", (int)AcctType.Expense) :
         (int)_module.Database.ForeignKey("Account", "AccountName", a, "AccountTypeId", (int)AcctType.Expense);
     if (a != _value)
         _transaction.DocumentTypeId = (int)DocType.Transfer;
 }
Example #11
0
        /// <summary>
        /// Update a document after editing
        /// </summary>
        public AjaxReturn DocumentSave(BankingDocument json)
        {
            Database.BeginTransaction();
            Extended_Document document = json.header;
            JObject           oldDoc   = getCompleteDocument(document.idDocument);
            DocType           t        = checkDocType(document.DocumentTypeId, DocType.Withdrawal, DocType.Deposit, DocType.CreditCardCharge, DocType.CreditCardCredit);
            FullAccount       acct     = Database.Get <FullAccount>((int)document.DocumentAccountId);

            checkAcctType(acct.AccountTypeId, AcctType.Bank, AcctType.CreditCard, AcctType.Investment,
                          AcctType.OtherAsset, AcctType.OtherLiability);
            allocateDocumentIdentifier(document, acct);
            int sign = SignFor(t);
            Extended_Document original = getDocument(document);
            decimal           vat      = 0;
            decimal           net      = 0;
            bool lineVat = false;                   // Flag to indicate this is a withdrawal to pay the VAT to HMRC

            foreach (InvoiceLine detail in json.detail)
            {
                if (detail.AccountId == 0 || detail.AccountId == null)
                {
                    Utils.Check(detail.LineAmount == 0 && detail.VatAmount == 0, "All lines must be allocated to an account");
                    continue;
                }
                net += detail.LineAmount;
                vat += detail.VatAmount;
            }
            Utils.Check(document.DocumentAmount == net + vat, "Document does not balance");
            decimal changeInDocumentAmount = -sign * (document.DocumentAmount - original.DocumentAmount);
            int     lineNum = 1;

            fixNameAddress(document, "O");
            Database.Update(document);
            int nextDocid = Utils.ExtractNumber(document.DocumentIdentifier);

            if (nextDocid > 0 && acct.RegisterNumber(t, nextDocid))
            {
                Database.Update(acct);
            }
            // Find any existing VAT record
            Journal vatJournal = Database.QueryOne <Journal>("SELECT * FROM Journal WHERE DocumentId = " + document.idDocument
                                                             + " AND AccountId = " + (int)Acct.VATControl + " ORDER BY JournalNum DESC");
            Journal journal = Database.Get(new Journal()
            {
                DocumentId = (int)document.idDocument,
                JournalNum = lineNum
            });

            journal.DocumentId    = (int)document.idDocument;
            journal.AccountId     = document.DocumentAccountId;
            journal.NameAddressId = document.DocumentNameAddressId;
            journal.Memo          = document.DocumentMemo;
            journal.JournalNum    = lineNum++;
            journal.Amount       += changeInDocumentAmount;
            journal.Outstanding  += changeInDocumentAmount;
            Database.Update(journal);
            foreach (InvoiceLine detail in json.detail)
            {
                if (detail.AccountId == 0 || detail.AccountId == null)
                {
                    continue;
                }
                Utils.Check(!lineVat, "Withdrawal to VAT account may only have 1 line");
                if (detail.AccountId == (int)Acct.VATControl)
                {
                    // This is a VAT payment to HMRC
                    Utils.Check(lineNum == 2, "Withdrawal to VAT account may only have 1 line");
                    Utils.Check(vat == 0, "Withdrawal to VAT account may not have a VAT amount");
                    vat     = detail.LineAmount;
                    lineVat = true;
                }
                journal = Database.Get(new Journal()
                {
                    DocumentId = (int)document.idDocument,
                    JournalNum = lineNum
                });
                journal.DocumentId    = (int)document.idDocument;
                journal.JournalNum    = lineNum++;
                journal.AccountId     = (int)detail.AccountId;
                journal.NameAddressId = document.DocumentNameAddressId;
                journal.Memo          = detail.Memo;
                journal.Amount        = sign * detail.LineAmount;
                journal.Outstanding   = sign * detail.LineAmount;
                Database.Update(journal);
                Line line = new Line()
                {
                    idLine     = journal.idJournal,
                    Qty        = 0,
                    LineAmount = detail.LineAmount,
                    VatCodeId  = detail.VatCodeId,
                    VatRate    = detail.VatRate,
                    VatAmount  = detail.VatAmount
                };
                Database.Update(line);
            }
            Database.Execute("DELETE FROM Line WHERE idLine IN (SELECT idJournal FROM Journal WHERE DocumentId = " + document.idDocument + " AND JournalNum >= " + lineNum + ")");
            Database.Execute("DELETE FROM Journal WHERE DocumentId = " + document.idDocument + " AND JournalNum >= " + lineNum);
            if (vat != 0 || vatJournal.idJournal != null)
            {
                // Add the VAT journal at the end
                vat *= sign;
                decimal changeInVatAmount = vat - vatJournal.Amount;
                Utils.Check(document.VatPaid == null || document.VatPaid < 1 || changeInVatAmount == 0, "Cannot alter VAT on this document, it has already been declared");
                if (!lineVat)
                {
                    vatJournal.DocumentId    = (int)document.idDocument;
                    vatJournal.AccountId     = (int)Acct.VATControl;
                    vatJournal.NameAddressId = document.DocumentNameAddressId;
                    vatJournal.Memo          = "Total VAT";
                    vatJournal.JournalNum    = lineNum++;
                    vatJournal.Amount        = vat;
                    vatJournal.Outstanding  += changeInVatAmount;
                    Database.Update(vatJournal);
                }
            }
            JObject newDoc = getCompleteDocument(document.idDocument);

            Database.AuditUpdate("Document", document.idDocument, oldDoc, newDoc);
            Settings.RegisterNumber(this, document.DocumentTypeId, Utils.ExtractNumber(document.DocumentIdentifier));
            Database.Commit();
            return(new AjaxReturn()
            {
                message = "Document saved", id = document.idDocument
            });
        }
Example #12
0
        public AjaxReturn DocumentSave(SubscriptionDocument json)
        {
            AjaxReturn result = new AjaxReturn();

            Database.BeginTransaction();
            JObject oldDoc = null;

            if (json.header.idDocument > 0)
            {
                oldDoc = getCompleteDocument(json.header.idDocument);
                checkDocType(((JObject)oldDoc["header"]).AsInt("DocumentTypeId"), DocType.Subscriptions);
            }
            Utils.Check(json.header.DocumentAccountId > 0, "Account not supplied");
            decimal total = 0;

            foreach (SubscriptionPayment detail in json.detail)
            {
                if (detail.NameAddressId > 0 && detail.Amount != 0)
                {
                    total += detail.Amount;
                }
                else
                {
                    Utils.Check(detail.Amount == 0, "All lines must be allocated to a member");
                }
            }
            Utils.Check(json.header.DocumentAmount == total, "Document does not balance");
            var      lineNum  = 1;
            Document document = new Document()
            {
                idDocument         = json.header.idDocument,
                DocumentTypeId     = (int)DocType.Subscriptions,
                DocumentDate       = json.header.DocumentDate,
                DocumentIdentifier = json.header.DocumentIdentifier,
                DocumentMemo       = json.header.DocumentMemo
            };

            Database.Update(document);
            result.id = document.idDocument;
            Journal journal = Database.Get(new Journal()
            {
                DocumentId = (int)document.idDocument,
                JournalNum = lineNum
            });

            journal.DocumentId    = (int)document.idDocument;
            journal.JournalNum    = lineNum++;
            journal.AccountId     = json.header.DocumentAccountId;
            journal.NameAddressId = 1;
            journal.Memo          = json.header.DocumentMemo;
            journal.Amount        = json.header.DocumentAmount;
            journal.Outstanding   = json.header.DocumentAmount;
            Database.Update(journal);
            foreach (SubscriptionPayment detail in json.detail)
            {
                if (detail.NameAddressId > 0 && detail.Amount != 0)
                {
                    journal = Database.Get(new Journal()
                    {
                        DocumentId = (int)document.idDocument,
                        JournalNum = lineNum
                    });
                    if (journal.idJournal > 0)
                    {
                        decimal amount = -journal.Amount;
                        // Existing payment
                        if (journal.NameAddressId != detail.NameAddressId)
                        {
                            // Changed member
                            Database.Execute("UPDATE Member SET AmountDue = AmountDue + " + amount + " WHERE NameAddressId = " + journal.NameAddressId);
                            Database.Execute("UPDATE Member SET AmountDue = AmountDue - " + detail.Amount + " WHERE NameAddressId = " + detail.NameAddressId);
                        }
                        else if (amount != detail.Amount)
                        {
                            // Just changed amount
                            Database.Execute("UPDATE Member SET AmountDue = AmountDue - " + (detail.Amount - amount) + " WHERE NameAddressId = " + detail.NameAddressId);
                        }
                    }
                    else
                    {
                        Database.Execute("UPDATE Member SET AmountDue = AmountDue - " + detail.Amount + " WHERE NameAddressId = " + detail.NameAddressId);
                    }
                    journal.DocumentId = (int)document.idDocument;
                    journal.JournalNum = lineNum;
                    journal.AccountId  = (int)Acct.SubscriptionsIncome;
                    checkNameType(detail.NameAddressId, "M");
                    journal.NameAddressId = detail.NameAddressId;
                    journal.Memo          = detail.Memo;
                    journal.Outstanding   = -detail.Amount;
                    journal.Amount        = -detail.Amount;
                    Database.Update(journal);
                    // Create a dummy line record
                    Line line = new Line()
                    {
                        idLine     = journal.idJournal,
                        Qty        = 0,
                        LineAmount = detail.Amount,
                        VatCodeId  = null,
                        VatRate    = 0,
                        VatAmount  = 0
                    };
                    Database.Update(line);
                    lineNum++;
                }
            }
            foreach (Journal j in Database.Query <Journal>("SELECT * FROM Journal WHERE DocumentId = " + document.idDocument + " AND JournalNum >= " + lineNum))
            {
                decimal amount = -j.Amount;
                Database.Execute("UPDATE Member SET AmountDue = AmountDue + " + amount + " WHERE NameAddressId = " + j.NameAddressId);
            }
            Database.Execute("DELETE FROM Line WHERE idLine IN (SELECT idJournal FROM Journal WHERE DocumentId = " + document.idDocument + " AND JournalNum >= " + lineNum + ")");
            Database.Execute("DELETE FROM Journal WHERE DocumentId = " + document.idDocument + " AND JournalNum >= " + lineNum);
            JObject newDoc = getCompleteDocument(document.idDocument);

            Database.AuditUpdate("Document", document.idDocument, oldDoc, newDoc);
            Database.Commit();
            result.message = "Subscription Payments saved";
            return(result);
        }