Exemple #1
0
        public async Task <IActionResult> Post([FromQuery] Int32 hid, Int32 loanAccountID, Int32?tmpdocid, [FromBody] FinanceDocumentUIViewModel repaydoc)
        {
            if (!ModelState.IsValid)
            {
                return(BadRequest(ModelState));
            }

            // The post here is:
            // 1. Post a repayment document with the content from this template doc
            // 2. Update the template doc with REFDOCID
            // 3. If the account balance is zero, close the account;

            // Basic check
            if (hid <= 0 || (tmpdocid.HasValue && tmpdocid.Value <= 0) ||
                loanAccountID <= 0 ||
                repaydoc == null || repaydoc.HID != hid ||
                repaydoc.DocType != FinanceDocTypeViewModel.DocType_Repay)
            {
                return(BadRequest("No data inputted!"));
            }
            SqlConnection  conn        = null;
            SqlCommand     cmd         = null;
            SqlDataReader  reader      = null;
            SqlTransaction tran        = null;
            String         queryString = String.Empty;
            String         strErrMsg   = String.Empty;
            HttpStatusCode errorCode   = HttpStatusCode.OK;
            Decimal        acntBalance = 0M;

            String usrName = String.Empty;

            if (Startup.UnitTestMode)
            {
                usrName = UnitTestUtility.UnitTestUser;
            }
            else
            {
                var usrObj = HIHAPIUtility.GetUserClaim(this);
                usrName = usrObj.Value;
            }
            if (String.IsNullOrEmpty(usrName))
            {
                return(BadRequest("User cannot recognize"));
            }

            // Update the database
            FinanceTmpDocLoanViewModel vmTmpDoc  = new FinanceTmpDocLoanViewModel();
            HomeDefViewModel           vmHome    = new HomeDefViewModel();
            FinanceAccountUIViewModel  vmAccount = new FinanceAccountUIViewModel();

            try
            {
                using (conn = new SqlConnection(Startup.DBConnectionString))
                {
                    await conn.OpenAsync();

                    // Check: HID, it requires more info than just check, so it implemented it
                    try
                    {
                        HIHAPIUtility.CheckHIDAssignment(conn, hid, usrName);
                    }
                    catch (Exception)
                    {
                        errorCode = HttpStatusCode.BadRequest;
                        throw;
                    }

                    // Check: DocID
                    String checkString = "";
                    if (tmpdocid.HasValue)
                    {
                        checkString = HIHDBUtility.GetFinanceDocLoanListQueryString() + " WHERE [DOCID] = " + tmpdocid.Value.ToString() + " AND [HID] = " + hid.ToString();
                        cmd         = new SqlCommand(checkString, conn);
                        reader      = cmd.ExecuteReader();

                        if (!reader.HasRows)
                        {
                            errorCode = HttpStatusCode.BadRequest;
                            throw new Exception("Invalid Doc ID inputted: " + tmpdocid.Value.ToString());
                        }
                        else
                        {
                            while (reader.Read())
                            {
                                HIHDBUtility.FinTmpDocLoan_DB2VM(reader, vmTmpDoc);

                                // It shall be only one entry if found!
                                break;
                            }
                        }

                        reader.Dispose();
                        reader = null;
                        cmd.Dispose();
                        cmd = null;
                    }

                    // Check: Tmp doc has posted or not?
                    if (vmTmpDoc == null || (vmTmpDoc.RefDocID.HasValue && vmTmpDoc.RefDocID.Value > 0) ||
                        vmTmpDoc.AccountID != loanAccountID)
                    {
                        errorCode = HttpStatusCode.BadRequest;
                        throw new Exception("Tmp Doc not existed yet or has been posted");
                    }

                    // Check: Loan account
                    checkString = HIHDBUtility.GetFinanceLoanAccountQueryString(hid, loanAccountID);
                    cmd         = new SqlCommand(checkString, conn);
                    reader      = cmd.ExecuteReader();
                    if (!reader.HasRows)
                    {
                        errorCode = HttpStatusCode.BadRequest;
                        throw new Exception("Loan account read failed based on Doc ID inputted: " + tmpdocid.ToString());
                    }
                    else
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                HIHDBUtility.FinAccountHeader_DB2VM(reader, vmAccount, 0);
                                break;
                            }
                        }
                        reader.NextResult();

                        vmAccount.ExtraInfo_Loan = new FinanceAccountExtLoanViewModel();
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                HIHDBUtility.FinAccountLoan_DB2VM(reader, vmAccount.ExtraInfo_Loan, 0);
                                break;
                            }
                        }
                        reader.NextResult();

                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                if (!reader.IsDBNull(0))
                                {
                                    acntBalance = reader.GetDecimal(0);
                                }
                                break;
                            }
                        }
                    }
                    reader.Dispose();
                    reader = null;
                    cmd.Dispose();
                    cmd = null;

                    // Data validation - basic
                    try
                    {
                        await FinanceDocumentController.FinanceDocumentBasicValidationAsync(repaydoc, conn);
                    }
                    catch (Exception)
                    {
                        errorCode = HttpStatusCode.BadRequest;
                        throw;
                    }

                    // Data validation - loan specific
                    try
                    {
                        int ninvaliditems = 0;
                        // Only four tran. types are allowed
                        if (vmAccount.CtgyID == FinanceAccountCtgyViewModel.AccountCategory_BorrowFrom)
                        {
                            ninvaliditems = repaydoc.Items.Where(item => item.TranType != FinanceTranTypeViewModel.TranType_InterestOut &&
                                                                 item.TranType != FinanceTranTypeViewModel.TranType_RepaymentOut &&
                                                                 item.TranType != FinanceTranTypeViewModel.TranType_RepaymentIn)
                                            .Count();
                        }
                        else if (vmAccount.CtgyID == FinanceAccountCtgyViewModel.AccountCategory_LendTo)
                        {
                            ninvaliditems = repaydoc.Items.Where(item => item.TranType != FinanceTranTypeViewModel.TranType_InterestIn &&
                                                                 item.TranType != FinanceTranTypeViewModel.TranType_RepaymentOut &&
                                                                 item.TranType != FinanceTranTypeViewModel.TranType_RepaymentIn)
                                            .Count();
                        }
                        if (ninvaliditems > 0)
                        {
                            throw new Exception("Items with invalid tran type");
                        }

                        // Check the amount
                        decimal totalOut = repaydoc.Items.Where(item => item.TranType == FinanceTranTypeViewModel.TranType_RepaymentOut).Sum(item2 => item2.TranAmount);
                        decimal totalIn  = repaydoc.Items.Where(item => item.TranType == FinanceTranTypeViewModel.TranType_RepaymentIn).Sum(item2 => item2.TranAmount);
                        //decimal totalintOut = repaydoc.Items.Where(item => (item.TranType == FinanceTranTypeViewModel.TranType_InterestOut)).Sum(item2 => item2.TranAmount);

                        // New account balance
                        if (vmAccount.CtgyID == FinanceAccountCtgyViewModel.AccountCategory_BorrowFrom)
                        {
                            acntBalance += totalOut;
                        }
                        else if (vmAccount.CtgyID == FinanceAccountCtgyViewModel.AccountCategory_LendTo)
                        {
                            acntBalance -= totalIn;
                        }
                        if (totalOut != totalIn)
                        {
                            throw new Exception("Amount is not equal!");
                        }
                    }
                    catch (Exception)
                    {
                        errorCode = HttpStatusCode.BadRequest;
                        throw;
                    }

                    // Now go ahead for the creating
                    tran = conn.BeginTransaction();
                    Int32 nNewDocID = 0;

                    // Now go ahead for creating
                    queryString = HIHDBUtility.GetFinDocHeaderInsertString();

                    // Header
                    cmd = new SqlCommand(queryString, conn)
                    {
                        Transaction = tran
                    };

                    HIHDBUtility.BindFinDocHeaderInsertParameter(cmd, repaydoc, usrName);
                    SqlParameter idparam = cmd.Parameters.AddWithValue("@Identity", SqlDbType.Int);
                    idparam.Direction = ParameterDirection.Output;

                    Int32 nRst = await cmd.ExecuteNonQueryAsync();

                    nNewDocID   = (Int32)idparam.Value;
                    repaydoc.ID = nNewDocID;
                    cmd.Dispose();
                    cmd = null;

                    // Then, creating the items
                    foreach (FinanceDocumentItemUIViewModel ivm in repaydoc.Items)
                    {
                        queryString = HIHDBUtility.GetFinDocItemInsertString();

                        SqlCommand cmd2 = new SqlCommand(queryString, conn)
                        {
                            Transaction = tran
                        };
                        HIHDBUtility.BindFinDocItemInsertParameter(cmd2, ivm, nNewDocID);

                        await cmd2.ExecuteNonQueryAsync();

                        cmd2.Dispose();
                        cmd2 = null;
                    }

                    // Then, update the template doc
                    queryString = @"UPDATE [dbo].[t_fin_tmpdoc_loan]
                                       SET [REFDOCID] = @REFDOCID
                                          ,[UPDATEDBY] = @UPDATEDBY
                                          ,[UPDATEDAT] = @UPDATEDAT
                                     WHERE [HID] = @HID AND [DOCID] = @DOCID";
                    SqlCommand cmdTmpDoc = new SqlCommand(queryString, conn)
                    {
                        Transaction = tran
                    };
                    cmdTmpDoc.Parameters.AddWithValue("@REFDOCID", nNewDocID);
                    cmdTmpDoc.Parameters.AddWithValue("@UPDATEDBY", usrName);
                    cmdTmpDoc.Parameters.AddWithValue("@UPDATEDAT", DateTime.Now);
                    cmdTmpDoc.Parameters.AddWithValue("@HID", hid);
                    cmdTmpDoc.Parameters.AddWithValue("@DOCID", tmpdocid);
                    await cmdTmpDoc.ExecuteNonQueryAsync();

                    cmdTmpDoc.Dispose();
                    cmdTmpDoc = null;

                    // Incase balance is zero, update the account status
                    if (Decimal.Compare(acntBalance, 0) == 0)
                    {
                        queryString = HIHDBUtility.GetFinanceAccountStatusUpdateString();
                        SqlCommand cmdAccount = new SqlCommand(queryString, conn, tran);
                        HIHDBUtility.BindFinAccountStatusUpdateParameter(cmdAccount, FinanceAccountStatus.Closed, loanAccountID, hid, usrName);
                        await cmdAccount.ExecuteNonQueryAsync();

                        cmdAccount.Dispose();
                        cmdAccount = null;
                    }

                    tran.Commit();

                    // Update the buffer of the relevant Account!
                    // Account List
                    try
                    {
                        var cacheKey = String.Format(CacheKeys.FinAccountList, hid, null);
                        this._cache.Remove(cacheKey);

                        cacheKey = String.Format(CacheKeys.FinAccount, hid, loanAccountID);
                        this._cache.Remove(cacheKey);
                    }
                    catch (Exception)
                    {
                        // Do nothing here.
                    }
                }
            }
            catch (Exception exp)
            {
#if DEBUG
                System.Diagnostics.Debug.WriteLine(exp.Message);
#endif

                if (tran != null)
                {
                    tran.Rollback();
                }

                strErrMsg = exp.Message;
                if (errorCode == HttpStatusCode.OK)
                {
                    errorCode = HttpStatusCode.InternalServerError;
                }
            }
            finally
            {
                if (tran != null)
                {
                    tran.Dispose();
                    tran = null;
                }
                if (reader != null)
                {
                    reader.Dispose();
                    reader = null;
                }
                if (cmd != null)
                {
                    cmd.Dispose();
                    cmd = null;
                }
                if (conn != null)
                {
                    conn.Dispose();
                    conn = null;
                }
            }

            if (errorCode != HttpStatusCode.OK)
            {
                switch (errorCode)
                {
                case HttpStatusCode.Unauthorized:
                    return(Unauthorized());

                case HttpStatusCode.NotFound:
                    return(NotFound());

                case HttpStatusCode.BadRequest:
                    return(BadRequest(strErrMsg));

                default:
                    return(StatusCode(500, strErrMsg));
                }
            }

            var setting = new Newtonsoft.Json.JsonSerializerSettings
            {
                DateFormatString = HIHAPIConstants.DateFormatPattern,
                ContractResolver = new Newtonsoft.Json.Serialization.CamelCasePropertyNamesContractResolver()
            };

            return(new JsonResult(repaydoc, setting));
        }
Exemple #2
0
        public async Task <IActionResult> Post([FromQuery] Int32 hid, Int32 docid)
        {
            // The post here is:
            // 1. Post a normal document with the content from this template doc
            // 2. Update the template doc with REFDOCID

            // Basic check
            if (hid <= 0 || docid <= 0)
            {
                return(BadRequest("No data inputted!"));
            }

            // Update the database
            SqlConnection              conn        = null;
            SqlCommand                 cmd         = null;
            SqlDataReader              reader      = null;
            SqlTransaction             tran        = null;
            String                     queryString = String.Empty;
            String                     strErrMsg   = String.Empty;
            FinanceTmpDocDPViewModel   vmTmpDoc    = new FinanceTmpDocDPViewModel();
            HomeDefViewModel           vmHome      = new HomeDefViewModel();
            FinanceDocumentUIViewModel vmFIDOC     = new FinanceDocumentUIViewModel();
            HttpStatusCode             errorCode   = HttpStatusCode.OK;

            String usrName = String.Empty;

            if (Startup.UnitTestMode)
            {
                usrName = UnitTestUtility.UnitTestUser;
            }
            else
            {
                var usrObj = HIHAPIUtility.GetUserClaim(this);
                usrName = usrObj.Value;
            }
            if (String.IsNullOrEmpty(usrName))
            {
                return(BadRequest("User cannot recognize"));
            }

            try
            {
                using (conn = new SqlConnection(Startup.DBConnectionString))
                {
                    await conn.OpenAsync();

                    // Check: HID, it requires more info than just check, so it implemented it
                    if (hid != 0)
                    {
                        String strHIDCheck = HIHDBUtility.getHomeDefQueryString() + " WHERE [ID]= @hid AND [USER] = @user";
                        cmd = new SqlCommand(strHIDCheck, conn);
                        cmd.Parameters.AddWithValue("@hid", hid);
                        cmd.Parameters.AddWithValue("@user", usrName);
                        reader = await cmd.ExecuteReaderAsync();

                        if (!reader.HasRows)
                        {
                            errorCode = HttpStatusCode.BadRequest;
                            throw new Exception("Not home found!");
                        }
                        else
                        {
                            while (reader.Read())
                            {
                                HIHDBUtility.HomeDef_DB2VM(reader, vmHome);

                                // It shall be only one entry if found!
                                break;
                            }
                        }

                        reader.Dispose();
                        reader = null;
                        cmd.Dispose();
                        cmd = null;
                    }

                    if (vmHome == null || String.IsNullOrEmpty(vmHome.BaseCurrency) || vmHome.ID != hid)
                    {
                        errorCode = HttpStatusCode.BadRequest;
                        throw new Exception("Home Definition is invalid");
                    }

                    // Check: DocID
                    String checkString = HIHDBUtility.getFinanceDocADPListQueryString() + " WHERE [DOCID] = " + docid.ToString() + " AND [HID] = " + hid.ToString();
                    cmd    = new SqlCommand(checkString, conn);
                    reader = cmd.ExecuteReader();
                    if (!reader.HasRows)
                    {
                        errorCode = HttpStatusCode.BadRequest;
                        throw new Exception("Invalid Doc ID inputted: " + docid.ToString());
                    }
                    else
                    {
                        while (reader.Read())
                        {
                            HIHDBUtility.FinTmpDocADP_DB2VM(reader, vmTmpDoc);

                            // It shall be only one entry if found!
                            break;
                        }
                    }
                    reader.Dispose();
                    reader = null;
                    cmd.Dispose();
                    cmd = null;

                    // Check: Tmp doc has posted or not?
                    if (vmTmpDoc == null || (vmTmpDoc.RefDocID.HasValue && vmTmpDoc.RefDocID.Value > 0))
                    {
                        errorCode = HttpStatusCode.BadRequest;
                        throw new Exception("Tmp Doc not existed yet or has been posted");
                    }
                    if (!vmTmpDoc.ControlCenterID.HasValue && !vmTmpDoc.OrderID.HasValue)
                    {
                        errorCode = HttpStatusCode.BadRequest;
                        throw new Exception("Tmp doc lack of control center or order");
                    }
                    if (vmTmpDoc.TranAmount == 0)
                    {
                        errorCode = HttpStatusCode.BadRequest;
                        throw new Exception("Tmp doc lack of amount");
                    }

                    // Now go ahead for the creating
                    tran = conn.BeginTransaction();

                    cmd = null;
                    Int32 nNewDocID = 0;
                    vmFIDOC.Desp    = vmTmpDoc.Desp;
                    vmFIDOC.DocType = FinanceDocTypeViewModel.DocType_Normal;
                    vmFIDOC.HID     = hid;
                    //vmFIDOC.TranAmount = vmTmpDoc.TranAmount;
                    vmFIDOC.TranCurr  = vmHome.BaseCurrency;
                    vmFIDOC.TranDate  = vmTmpDoc.TranDate;
                    vmFIDOC.CreatedAt = DateTime.Now;
                    FinanceDocumentItemUIViewModel vmItem = new FinanceDocumentItemUIViewModel
                    {
                        AccountID = vmTmpDoc.AccountID
                    };
                    if (vmTmpDoc.ControlCenterID.HasValue)
                    {
                        vmItem.ControlCenterID = vmTmpDoc.ControlCenterID.Value;
                    }
                    if (vmTmpDoc.OrderID.HasValue)
                    {
                        vmItem.OrderID = vmTmpDoc.OrderID.Value;
                    }
                    vmItem.Desp       = vmTmpDoc.Desp;
                    vmItem.ItemID     = 1;
                    vmItem.TranAmount = vmTmpDoc.TranAmount;
                    vmItem.TranType   = vmTmpDoc.TranType;
                    vmFIDOC.Items.Add(vmItem);

                    // Now go ahead for the creating
                    queryString = HIHDBUtility.GetFinDocHeaderInsertString();

                    // Header
                    cmd = new SqlCommand(queryString, conn)
                    {
                        Transaction = tran
                    };

                    HIHDBUtility.BindFinDocHeaderInsertParameter(cmd, vmFIDOC, usrName);
                    SqlParameter idparam = cmd.Parameters.AddWithValue("@Identity", SqlDbType.Int);
                    idparam.Direction = ParameterDirection.Output;

                    Int32 nRst = await cmd.ExecuteNonQueryAsync();

                    nNewDocID  = (Int32)idparam.Value;
                    vmFIDOC.ID = nNewDocID;
                    cmd.Dispose();
                    cmd = null;

                    // Then, creating the items
                    foreach (FinanceDocumentItemUIViewModel ivm in vmFIDOC.Items)
                    {
                        queryString = HIHDBUtility.GetFinDocItemInsertString();

                        cmd = new SqlCommand(queryString, conn)
                        {
                            Transaction = tran
                        };
                        HIHDBUtility.BindFinDocItemInsertParameter(cmd, ivm, nNewDocID);

                        await cmd.ExecuteNonQueryAsync();

                        cmd.Dispose();
                        cmd = null;
                    }

                    // Then, update the template doc
                    queryString = @"UPDATE [dbo].[t_fin_tmpdoc_dp]
                                       SET [REFDOCID] = @REFDOCID
                                          ,[UPDATEDBY] = @UPDATEDBY
                                          ,[UPDATEDAT] = @UPDATEDAT
                                     WHERE [HID] = @HID AND [DOCID] = @DOCID";
                    cmd         = new SqlCommand(queryString, conn)
                    {
                        Transaction = tran
                    };
                    cmd.Parameters.AddWithValue("@REFDOCID", nNewDocID);
                    cmd.Parameters.AddWithValue("@UPDATEDBY", usrName);
                    cmd.Parameters.AddWithValue("@UPDATEDAT", DateTime.Now);
                    cmd.Parameters.AddWithValue("@HID", hid);
                    cmd.Parameters.AddWithValue("@DOCID", docid);
                    await cmd.ExecuteNonQueryAsync();

                    tran.Commit();

                    // Update the buffer of the relevant Account!
                    var cacheAccountKey = String.Format(CacheKeys.FinAccount, hid, vmTmpDoc.AccountID);
                    this._cache.Remove(cacheAccountKey);
                }
            }
            catch (Exception exp)
            {
#if DEBUG
                System.Diagnostics.Debug.WriteLine(exp.Message);
#endif

                if (tran != null)
                {
                    tran.Rollback();
                }

                strErrMsg = exp.Message;
                if (errorCode == HttpStatusCode.OK)
                {
                    errorCode = HttpStatusCode.InternalServerError;
                }
            }
            finally
            {
                if (tran != null)
                {
                    tran.Dispose();
                    tran = null;
                }
                if (reader != null)
                {
                    reader.Dispose();
                    reader = null;
                }
                if (cmd != null)
                {
                    cmd.Dispose();
                    cmd = null;
                }
                if (conn != null)
                {
                    conn.Dispose();
                    conn = null;
                }
            }

            if (errorCode != HttpStatusCode.OK)
            {
                switch (errorCode)
                {
                case HttpStatusCode.Unauthorized:
                    return(Unauthorized());

                case HttpStatusCode.NotFound:
                    return(NotFound());

                case HttpStatusCode.BadRequest:
                    return(BadRequest(strErrMsg));

                default:
                    return(StatusCode(500, strErrMsg));
                }
            }

            var setting = new Newtonsoft.Json.JsonSerializerSettings
            {
                DateFormatString = HIHAPIConstants.DateFormatPattern,
                ContractResolver = new Newtonsoft.Json.Serialization.CamelCasePropertyNamesContractResolver()
            };

            return(new JsonResult(vmFIDOC, setting));
        }
        public async Task <IActionResult> Post([FromBody] FinanceAssetSoldoutDocViewModel vm)
        {
            if (!ModelState.IsValid)
            {
                return(BadRequest(ModelState));
            }

            // Perform checks
            if (vm.HID <= 0)
            {
                return(BadRequest("Not HID inputted"));
            }
            if (vm.AssetAccountID <= 0)
            {
                return(BadRequest("Asset Account is invalid"));
            }
            if (vm.TranAmount <= 0)
            {
                return(BadRequest("Amount is less than zero"));
            }
            if (vm.Items.Count <= 0)
            {
                return(BadRequest("No items inputted"));
            }

            String usrName = String.Empty;

            if (Startup.UnitTestMode)
            {
                usrName = UnitTestUtility.UnitTestUser;
            }
            else
            {
                var usrObj = HIHAPIUtility.GetUserClaim(this);
                usrName = usrObj.Value;
            }
            if (String.IsNullOrEmpty(usrName))
            {
                return(BadRequest("User cannot recognize"));
            }

            // Construct the Doc.
            var vmFIDoc = new FinanceDocumentUIViewModel();

            vmFIDoc.DocType  = FinanceDocTypeViewModel.DocType_AssetSoldOut;
            vmFIDoc.Desp     = vm.Desp;
            vmFIDoc.TranDate = vm.TranDate;
            vmFIDoc.HID      = vm.HID;
            vmFIDoc.TranCurr = vm.TranCurr;

            Decimal totalAmt  = 0;
            var     maxItemID = 0;

            foreach (var di in vm.Items)
            {
                if (di.ItemID <= 0 || di.TranAmount == 0 || di.AccountID <= 0 ||
                    di.TranType != FinanceTranTypeViewModel.TranType_AssetSoldoutIncome ||
                    (di.ControlCenterID <= 0 && di.OrderID <= 0))
                {
                    return(BadRequest("Invalid input data in items!"));
                }

                totalAmt += di.TranAmount;
                vmFIDoc.Items.Add(di);

                if (maxItemID < di.ItemID)
                {
                    maxItemID = di.ItemID;
                }
            }
            if (Decimal.Compare(totalAmt, vm.TranAmount) != 0)
            {
                return(BadRequest("Amount is not even"));
            }

            var nitem = new FinanceDocumentItemUIViewModel();

            nitem.ItemID     = ++maxItemID;
            nitem.AccountID  = vm.AssetAccountID;
            nitem.TranAmount = vm.TranAmount;
            nitem.Desp       = vmFIDoc.Desp;
            nitem.TranType   = FinanceTranTypeViewModel.TranType_AssetSoldout;
            if (vm.ControlCenterID.HasValue)
            {
                nitem.ControlCenterID = vm.ControlCenterID.Value;
            }
            if (vm.OrderID.HasValue)
            {
                nitem.OrderID = vm.OrderID.Value;
            }
            vmFIDoc.Items.Add(nitem);

            // Update the database
            SqlConnection  conn         = null;
            SqlCommand     cmd          = null;
            SqlDataReader  reader       = null;
            SqlTransaction tran         = null;
            String         queryString  = "";
            Int32          nNewDocID    = -1;
            String         strErrMsg    = "";
            Decimal        dCurrBalance = 0;
            HttpStatusCode errorCode    = HttpStatusCode.OK;

            try
            {
                // Basic check again
                FinanceDocumentController.FinanceDocumentBasicCheck(vmFIDoc);

                using (conn = new SqlConnection(Startup.DBConnectionString))
                {
                    await conn.OpenAsync();

                    // Check Home assignment with current user
                    try
                    {
                        HIHAPIUtility.CheckHIDAssignment(conn, vm.HID, usrName);
                    }
                    catch (Exception)
                    {
                        errorCode = HttpStatusCode.BadRequest;
                        throw;
                    }

                    // Perfrom the doc. validation
                    await FinanceDocumentController.FinanceDocumentBasicValidationAsync(vmFIDoc, conn);

                    // Additional checks
                    SqlCommand    cmdAddCheck    = null;
                    SqlDataReader readerAddCheck = null;
                    try
                    {
                        // Check 1: check account is a valid asset?
                        String strsqls = @"SELECT 
                                      [t_fin_account].[STATUS]
                                      ,[t_fin_account_ext_as].[REFDOC_SOLD] AS [ASREFDOC_SOLD]
                                  FROM [dbo].[t_fin_account]
                                  INNER JOIN [dbo].[t_fin_account_ext_as]
                                       ON [t_fin_account].[ID] = [t_fin_account_ext_as].[ACCOUNTID]
                                  WHERE [t_fin_account].[ID] = " + vm.AssetAccountID.ToString()
                                         + " AND [t_fin_account].[HID] = " + vm.HID.ToString();
                        cmdAddCheck    = new SqlCommand(strsqls, conn);
                        readerAddCheck = await cmdAddCheck.ExecuteReaderAsync();

                        if (readerAddCheck.HasRows)
                        {
                            while (readerAddCheck.Read())
                            {
                                if (!readerAddCheck.IsDBNull(0))
                                {
                                    var acntStatus = (FinanceAccountStatus)readerAddCheck.GetByte(0);
                                    if (acntStatus != FinanceAccountStatus.Normal)
                                    {
                                        throw new Exception("Account status is not normal");
                                    }
                                }
                                else
                                {
                                    // Status is NULL stands for Active Status
                                    // throw new Exception("Account status is not normal");
                                }

                                if (!readerAddCheck.IsDBNull(1))
                                {
                                    throw new Exception("Account has soldout doc already");
                                }

                                break;
                            }
                        }
                        readerAddCheck.Close();
                        readerAddCheck = null;
                        cmdAddCheck.Dispose();
                        cmdAddCheck = null;

                        // Check 2: check the inputted date is valid > must be the later than all existing transactions;
                        strsqls        = @"SELECT MAX(t_fin_document.TRANDATE)
                                    FROM [dbo].[t_fin_document_item]
	                                INNER JOIN [dbo].[t_fin_document] 
                                       ON [dbo].[t_fin_document_item].[DOCID] = [dbo].[t_fin_document].[ID]
                                    WHERE [dbo].[t_fin_document_item].[ACCOUNTID] = " + vm.AssetAccountID.ToString();
                        cmdAddCheck    = new SqlCommand(strsqls, conn);
                        readerAddCheck = await cmdAddCheck.ExecuteReaderAsync();

                        if (readerAddCheck.HasRows)
                        {
                            while (readerAddCheck.Read())
                            {
                                var latestdate = readerAddCheck.GetDateTime(0);
                                if (vm.TranDate.Date < latestdate.Date)
                                {
                                    throw new Exception("Invalid date");
                                }

                                break;
                            }
                        }
                        else
                        {
                            throw new Exception("Invalid account - no doc items");
                        }

                        readerAddCheck.Close();
                        readerAddCheck = null;
                        cmdAddCheck.Dispose();
                        cmdAddCheck = null;

                        // Check 3. Fetch current balance
                        strsqls        = @"SELECT [balance]
                            FROM [dbo].[v_fin_report_bs] WHERE [accountid] = " + vm.AssetAccountID.ToString();
                        cmdAddCheck    = new SqlCommand(strsqls, conn);
                        readerAddCheck = await cmdAddCheck.ExecuteReaderAsync();

                        if (readerAddCheck.HasRows)
                        {
                            while (readerAddCheck.Read())
                            {
                                dCurrBalance = readerAddCheck.GetDecimal(0);
                                if (dCurrBalance <= 0)
                                {
                                    throw new Exception("Balance is zero");
                                }

                                break;
                            }
                        }
                        else
                        {
                            throw new Exception("Invalid account - no doc items");
                        }

                        readerAddCheck.Close();
                        readerAddCheck = null;
                        cmdAddCheck.Dispose();
                        cmdAddCheck = null;

                        var ncmprst = Decimal.Compare(dCurrBalance, vm.TranAmount);
                        if (ncmprst > 0)
                        {
                            var nitem2 = new FinanceDocumentItemUIViewModel();
                            nitem2.ItemID     = ++maxItemID;
                            nitem2.AccountID  = vm.AssetAccountID;
                            nitem2.TranAmount = Decimal.Subtract(dCurrBalance, vm.TranAmount);
                            nitem2.Desp       = vmFIDoc.Desp;
                            nitem2.TranType   = FinanceTranTypeViewModel.TranType_AssetValueDecrease;
                            if (vm.ControlCenterID.HasValue)
                            {
                                nitem2.ControlCenterID = vm.ControlCenterID.Value;
                            }
                            if (vm.OrderID.HasValue)
                            {
                                nitem2.OrderID = vm.OrderID.Value;
                            }
                            vmFIDoc.Items.Add(nitem2);
                        }
                        else if (ncmprst < 0)
                        {
                            var nitem2 = new FinanceDocumentItemUIViewModel();
                            nitem2.ItemID     = ++maxItemID;
                            nitem2.AccountID  = vm.AssetAccountID;
                            nitem2.TranAmount = Decimal.Subtract(vm.TranAmount, dCurrBalance);
                            nitem2.Desp       = vmFIDoc.Desp;
                            nitem2.TranType   = FinanceTranTypeViewModel.TranType_AssetValueIncrease;
                            if (vm.ControlCenterID.HasValue)
                            {
                                nitem2.ControlCenterID = vm.ControlCenterID.Value;
                            }
                            if (vm.OrderID.HasValue)
                            {
                                nitem2.OrderID = vm.OrderID.Value;
                            }
                            vmFIDoc.Items.Add(nitem2);
                        }
                    }
                    catch (Exception)
                    {
                        errorCode = HttpStatusCode.BadRequest;
                        throw;
                    }
                    finally
                    {
                        if (readerAddCheck != null)
                        {
                            readerAddCheck.Close();
                            readerAddCheck = null;
                        }
                        if (cmdAddCheck != null)
                        {
                            cmdAddCheck.Dispose();
                            cmdAddCheck = null;
                        }
                    }

                    // Begin the modification
                    tran = conn.BeginTransaction();

                    // First, craete the doc header => nNewDocID
                    queryString = HIHDBUtility.GetFinDocHeaderInsertString();
                    cmd         = new SqlCommand(queryString, conn)
                    {
                        Transaction = tran
                    };

                    HIHDBUtility.BindFinDocHeaderInsertParameter(cmd, vmFIDoc, usrName);
                    SqlParameter idparam = cmd.Parameters.AddWithValue("@Identity", SqlDbType.Int);
                    idparam.Direction = ParameterDirection.Output;

                    Int32 nRst = await cmd.ExecuteNonQueryAsync();

                    nNewDocID = (Int32)idparam.Value;
                    cmd.Dispose();
                    cmd = null;

                    // Then, creating the items
                    foreach (FinanceDocumentItemUIViewModel ivm in vmFIDoc.Items)
                    {
                        queryString = HIHDBUtility.GetFinDocItemInsertString();
                        cmd         = new SqlCommand(queryString, conn)
                        {
                            Transaction = tran
                        };
                        HIHDBUtility.BindFinDocItemInsertParameter(cmd, ivm, nNewDocID);

                        await cmd.ExecuteNonQueryAsync();

                        cmd.Dispose();
                        cmd = null;

                        // Tags
                        if (ivm.TagTerms.Count > 0)
                        {
                            // Create tags
                            foreach (var term in ivm.TagTerms)
                            {
                                queryString = HIHDBUtility.GetTagInsertString();

                                cmd = new SqlCommand(queryString, conn, tran);

                                HIHDBUtility.BindTagInsertParameter(cmd, vm.HID, HIHTagTypeEnum.FinanceDocumentItem, nNewDocID, term, ivm.ItemID);

                                await cmd.ExecuteNonQueryAsync();

                                cmd.Dispose();
                                cmd = null;
                            }
                        }
                    }

                    // Third, update the Account's status
                    queryString = HIHDBUtility.GetFinanceAccountStatusUpdateString();
                    cmd         = new SqlCommand(queryString, conn)
                    {
                        Transaction = tran
                    };

                    // Close this account
                    HIHDBUtility.BindFinAccountStatusUpdateParameter(cmd, FinanceAccountStatus.Closed, vm.AssetAccountID, vm.HID, usrName);
                    nRst = await cmd.ExecuteNonQueryAsync();

                    cmd.Dispose();
                    cmd = null;

                    // Fourth, Update the Asset account part for sold doc
                    queryString = HIHDBUtility.GetFinanceAccountAssetUpdateSoldDocString();
                    cmd         = new SqlCommand(queryString, conn)
                    {
                        Transaction = tran
                    };
                    HIHDBUtility.BindFinAccountAssetUpdateSoldDocParameter(cmd, nNewDocID, vm.AssetAccountID);
                    nRst = await cmd.ExecuteNonQueryAsync();

                    cmd.Dispose();
                    cmd = null;

                    // Do the commit
                    tran.Commit();

                    // Update the buffer
                    // Account List
                    try
                    {
                        var cacheKey = String.Format(CacheKeys.FinAccountList, vm.HID, null);
                        this._cache.Remove(cacheKey);
                    }
                    catch (Exception)
                    {
                        // Do nothing here.
                    }
                    // Account itself
                    try
                    {
                        var cacheKey = String.Format(CacheKeys.FinAccount, vm.HID, vm.AssetAccountID);
                        this._cache.Remove(cacheKey);
                    }
                    catch (Exception)
                    {
                        // Do nothing here.
                    }
                }
            }
            catch (Exception exp)
            {
#if DEBUG
                System.Diagnostics.Debug.WriteLine(exp.Message);
#endif

                strErrMsg = exp.Message;
                if (errorCode == HttpStatusCode.OK)
                {
                    errorCode = HttpStatusCode.InternalServerError;
                }

                if (tran != null)
                {
                    tran.Rollback();
                }
            }
            finally
            {
                if (tran != null)
                {
                    tran.Dispose();
                    tran = null;
                }
                if (reader != null)
                {
                    reader.Dispose();
                    reader = null;
                }
                if (cmd != null)
                {
                    cmd.Dispose();
                    cmd = null;
                }
                if (conn != null)
                {
                    conn.Dispose();
                    conn = null;
                }
            }

            if (errorCode != HttpStatusCode.OK)
            {
                switch (errorCode)
                {
                case HttpStatusCode.Unauthorized:
                    return(Unauthorized());

                case HttpStatusCode.NotFound:
                    return(NotFound());

                case HttpStatusCode.BadRequest:
                    return(BadRequest(strErrMsg));

                default:
                    return(StatusCode(500, strErrMsg));
                }
            }

            // Return nothing
            return(Ok(nNewDocID));
        }
        public async Task <IActionResult> Post([FromBody] FinanceLoanDocumentUIViewModel vm)
        {
            if (!ModelState.IsValid)
            {
                return(BadRequest(ModelState));
            }

            if (vm == null || (vm.DocType != FinanceDocTypeViewModel.DocType_BorrowFrom &&
                               vm.DocType != FinanceDocTypeViewModel.DocType_LendTo))
            {
                return(BadRequest("No data is inputted"));
            }
            if (vm.HID <= 0)
            {
                return(BadRequest("Not HID inputted"));
            }

            String usrName = String.Empty;

            if (Startup.UnitTestMode)
            {
                usrName = UnitTestUtility.UnitTestUser;
            }
            else
            {
                var usrObj = HIHAPIUtility.GetUserClaim(this);
                usrName = usrObj.Value;
            }
            if (String.IsNullOrEmpty(usrName))
            {
                return(BadRequest("User cannot recognize"));
            }

            // Check the items
            if (vm.Items.Count != 1)
            {
                return(BadRequest("Only one item doc is supported by far"));
            }
            if (vm.AccountVM == null || vm.AccountVM.ExtraInfo_Loan == null)
            {
                return(BadRequest("No account info!"));
            }
            if (vm.AccountVM.ExtraInfo_Loan.LoanTmpDocs.Count <= 0)
            {
                return(BadRequest("No template docs defined!"));
            }
            else
            {
                foreach (var tdoc in vm.AccountVM.ExtraInfo_Loan.LoanTmpDocs)
                {
                    if (!tdoc.ControlCenterID.HasValue && !tdoc.OrderID.HasValue)
                    {
                        return(BadRequest("Either control center or order shall be specified in Loan Template doc"));
                    }
                    if (tdoc.TranAmount <= 0)
                    {
                        return(BadRequest("Amount is zero!"));
                    }
                }
            }

            // Update the database
            SqlConnection  conn        = null;
            SqlCommand     cmd         = null;
            SqlDataReader  reader      = null;
            SqlTransaction tran        = null;
            String         queryString = "";
            Int32          nNewDocID   = -1;
            String         strErrMsg   = "";
            HttpStatusCode errorCode   = HttpStatusCode.OK;

            try
            {
                using (conn = new SqlConnection(Startup.DBConnectionString))
                {
                    await conn.OpenAsync();

                    // Check Home assignment with current user
                    try
                    {
                        HIHAPIUtility.CheckHIDAssignment(conn, vm.HID, usrName);
                    }
                    catch (Exception)
                    {
                        errorCode = HttpStatusCode.BadRequest;
                        throw;
                    }

                    tran = conn.BeginTransaction();

                    // First, create the doc header => nNewDocID
                    queryString = HIHDBUtility.GetFinDocHeaderInsertString();
                    cmd         = new SqlCommand(queryString, conn)
                    {
                        Transaction = tran
                    };

                    HIHDBUtility.BindFinDocHeaderInsertParameter(cmd, vm, usrName);
                    SqlParameter idparam = cmd.Parameters.AddWithValue("@Identity", SqlDbType.Int);
                    idparam.Direction = ParameterDirection.Output;

                    Int32 nRst = await cmd.ExecuteNonQueryAsync();

                    nNewDocID = (Int32)idparam.Value;
                    cmd.Dispose();
                    cmd = null;

                    // Then, creating the items
                    foreach (FinanceDocumentItemUIViewModel ivm in vm.Items)
                    {
                        if (vm.DocType == FinanceDocTypeViewModel.DocType_BorrowFrom)
                        {
                            ivm.TranType = FinanceTranTypeViewModel.TranType_BorrowFrom;
                        }
                        else if (vm.DocType == FinanceDocTypeViewModel.DocType_LendTo)
                        {
                            ivm.TranType = FinanceTranTypeViewModel.TranType_LendTo;
                        }

                        queryString = HIHDBUtility.GetFinDocItemInsertString();
                        cmd         = new SqlCommand(queryString, conn)
                        {
                            Transaction = tran
                        };
                        HIHDBUtility.BindFinDocItemInsertParameter(cmd, ivm, nNewDocID);

                        await cmd.ExecuteNonQueryAsync();

                        cmd.Dispose();
                        cmd = null;

                        // Tags
                        if (ivm.TagTerms.Count > 0)
                        {
                            // Create tags
                            foreach (var term in ivm.TagTerms)
                            {
                                queryString = HIHDBUtility.GetTagInsertString();

                                cmd = new SqlCommand(queryString, conn, tran);

                                HIHDBUtility.BindTagInsertParameter(cmd, vm.HID, HIHTagTypeEnum.FinanceDocumentItem, nNewDocID, term, ivm.ItemID);

                                await cmd.ExecuteNonQueryAsync();

                                cmd.Dispose();
                                cmd = null;
                            }
                        }
                    }

                    // Third, go to the account creation => nNewAccountID
                    queryString = HIHDBUtility.GetFinanceAccountHeaderInsertString();

                    cmd = new SqlCommand(queryString, conn)
                    {
                        Transaction = tran
                    };
                    HIHDBUtility.BindFinAccountInsertParameter(cmd, vm.AccountVM, usrName);

                    SqlParameter idparam2 = cmd.Parameters.AddWithValue("@Identity", SqlDbType.Int);
                    idparam2.Direction = ParameterDirection.Output;

                    nRst = await cmd.ExecuteNonQueryAsync();

                    Int32 nNewAccountID = (Int32)idparam2.Value;
                    cmd.Dispose();
                    cmd = null;

                    // 3a. Create another item to loan document
                    var nMaxItemID = vm.Items.Max(item => item.ItemID);
                    foreach (FinanceDocumentItemUIViewModel ivm in vm.Items)
                    {
                        ivm.ItemID    = ++nMaxItemID;
                        ivm.AccountID = nNewAccountID;
                        if (vm.DocType == FinanceDocTypeViewModel.DocType_BorrowFrom)
                        {
                            ivm.TranType = FinanceTranTypeViewModel.TranType_OpeningLiability;
                        }
                        else if (vm.DocType == FinanceDocTypeViewModel.DocType_LendTo)
                        {
                            ivm.TranType = FinanceTranTypeViewModel.TranType_OpeningAsset;
                        }

                        queryString = HIHDBUtility.GetFinDocItemInsertString();
                        SqlCommand cmd2 = new SqlCommand(queryString, conn)
                        {
                            Transaction = tran
                        };
                        HIHDBUtility.BindFinDocItemInsertParameter(cmd2, ivm, nNewDocID);

                        await cmd2.ExecuteNonQueryAsync();

                        cmd2.Dispose();
                        cmd2 = null;
                    }

                    // Fourth, creat the Loan part
                    queryString = HIHDBUtility.GetFinanceAccountLoanInsertString();
                    cmd         = new SqlCommand(queryString, conn)
                    {
                        Transaction = tran
                    };

                    HIHDBUtility.BindFinAccountLoanInsertParameter(cmd, vm.AccountVM.ExtraInfo_Loan, nNewDocID, nNewAccountID, usrName);
                    nRst = await cmd.ExecuteNonQueryAsync();

                    cmd.Dispose();
                    cmd = null;

                    // Fifth, create template docs
                    foreach (FinanceTmpDocLoanViewModel avm in vm.AccountVM.ExtraInfo_Loan.LoanTmpDocs)
                    {
                        queryString = HIHDBUtility.GetFinanceTmpDocLoanInsertString();

                        cmd = new SqlCommand(queryString, conn)
                        {
                            Transaction = tran
                        };

                        HIHDBUtility.BindFinTmpDocLoanParameter(cmd, avm, nNewAccountID, usrName);
                        await cmd.ExecuteNonQueryAsync();

                        cmd.Dispose();
                        cmd = null;
                    }

                    tran.Commit();

                    // Update the buffer
                    // Account list
                    var cacheKey = String.Format(CacheKeys.FinAccountList, vm.HID, null);
                    this._cache.Remove(cacheKey);
                }
            }
            catch (Exception exp)
            {
#if DEBUG
                System.Diagnostics.Debug.WriteLine(exp.Message);
#endif

                if (tran != null)
                {
                    tran.Rollback();
                }

                strErrMsg = exp.Message;
                if (errorCode == HttpStatusCode.OK)
                {
                    errorCode = HttpStatusCode.InternalServerError;
                }
            }
            finally
            {
                if (tran != null)
                {
                    tran.Dispose();
                    tran = null;
                }
                if (reader != null)
                {
                    reader.Dispose();
                    reader = null;
                }
                if (cmd != null)
                {
                    cmd.Dispose();
                    cmd = null;
                }
                if (conn != null)
                {
                    conn.Dispose();
                    conn = null;
                }
            }

            if (errorCode != HttpStatusCode.OK)
            {
                switch (errorCode)
                {
                case HttpStatusCode.Unauthorized:
                    return(Unauthorized());

                case HttpStatusCode.NotFound:
                    return(NotFound());

                case HttpStatusCode.BadRequest:
                    return(BadRequest(strErrMsg));

                default:
                    return(StatusCode(500, strErrMsg));
                }
            }

            vm.ID = nNewDocID;
            var setting = new Newtonsoft.Json.JsonSerializerSettings
            {
                DateFormatString = HIHAPIConstants.DateFormatPattern,
                ContractResolver = new Newtonsoft.Json.Serialization.CamelCasePropertyNamesContractResolver()
            };

            return(new JsonResult(vm, setting));
        }
Exemple #5
0
        public async Task <IActionResult> Post([FromBody] FinanceAssetBuyinDocViewModel vm)
        {
            if (!ModelState.IsValid)
            {
                return(BadRequest(ModelState));
            }

            if (vm == null || vm.accountAsset == null)
            {
                return(BadRequest("No data is inputted"));
            }

            if (vm.HID <= 0)
            {
                return(BadRequest("Not HID inputted"));
            }

            // Do basic checks
            if (String.IsNullOrEmpty(vm.TranCurr) || String.IsNullOrEmpty(vm.accountAsset.Name) ||
                (vm.IsLegacy.HasValue && vm.IsLegacy.Value && vm.Items.Count > 0) ||
                ((!vm.IsLegacy.HasValue || (vm.IsLegacy.HasValue && !vm.IsLegacy.Value)) && vm.Items.Count <= 0)
                )
            {
                return(BadRequest("Invalid input data"));
            }

            foreach (var di in vm.Items)
            {
                if (di.TranAmount == 0 || di.AccountID <= 0 || di.TranType <= 0 || (di.ControlCenterID <= 0 && di.OrderID <= 0))
                {
                    return(BadRequest("Invalid input data in items!"));
                }
            }

            String usrName = String.Empty;

            if (Startup.UnitTestMode)
            {
                usrName = UnitTestUtility.UnitTestUser;
            }
            else
            {
                var usrObj = HIHAPIUtility.GetUserClaim(this);
                usrName = usrObj.Value;
            }
            if (String.IsNullOrEmpty(usrName))
            {
                return(BadRequest("User cannot recognize"));
            }

            // Construct the Account
            var vmAccount = new FinanceAccountViewModel();

            vmAccount.HID                     = vm.HID;
            vmAccount.Name                    = vm.accountAsset.Name;
            vmAccount.Status                  = FinanceAccountStatus.Normal;
            vmAccount.CtgyID                  = FinanceAccountCtgyViewModel.AccountCategory_Asset;
            vmAccount.ExtraInfo_AS            = new FinanceAccountExtASViewModel();
            vmAccount.Owner                   = vm.AccountOwner;
            vmAccount.Comment                 = vm.accountAsset.Name;
            vmAccount.ExtraInfo_AS.Name       = vm.accountAsset.Name;
            vmAccount.ExtraInfo_AS.Comment    = vm.accountAsset.Comment;
            vmAccount.ExtraInfo_AS.CategoryID = vm.accountAsset.CategoryID;

            // Construct the Doc.
            var vmFIDoc = new FinanceDocumentUIViewModel();

            vmFIDoc.DocType  = FinanceDocTypeViewModel.DocType_AssetBuyIn;
            vmFIDoc.Desp     = vm.Desp;
            vmFIDoc.TranDate = vm.TranDate;
            vmFIDoc.HID      = vm.HID;
            vmFIDoc.TranCurr = vm.TranCurr;

            var maxItemID = 0;

            if (vm.IsLegacy.HasValue && vm.IsLegacy.Value)
            {
                // Legacy account...
            }
            else
            {
                Decimal totalAmt = 0;
                foreach (var di in vm.Items)
                {
                    if (di.ItemID <= 0 || di.TranAmount == 0 || di.AccountID <= 0 ||
                        (di.ControlCenterID <= 0 && di.OrderID <= 0))
                    {
                        return(BadRequest("Invalid input data in items!"));
                    }

                    // Todo: new check the tran. type is an expense!

                    totalAmt += di.TranAmount;
                    vmFIDoc.Items.Add(di);

                    if (maxItemID < di.ItemID)
                    {
                        maxItemID = di.ItemID;
                    }
                }

                if (totalAmt != vm.TranAmount)
                {
                    return(BadRequest("Amount is not even"));
                }
            }

            var nitem = new FinanceDocumentItemUIViewModel();

            nitem.ItemID     = ++maxItemID;
            nitem.AccountID  = -1;
            nitem.TranAmount = vm.TranAmount;
            nitem.Desp       = vmFIDoc.Desp;
            nitem.TranType   = FinanceTranTypeViewModel.TranType_OpeningAsset;
            if (vm.ControlCenterID.HasValue)
            {
                nitem.ControlCenterID = vm.ControlCenterID.Value;
            }
            if (vm.OrderID.HasValue)
            {
                nitem.OrderID = vm.OrderID.Value;
            }
            vmFIDoc.Items.Add(nitem);

            // Update the database
            SqlConnection  conn        = null;
            SqlCommand     cmd         = null;
            SqlDataReader  reader      = null;
            SqlTransaction tran        = null;
            String         queryString = "";
            Int32          nNewDocID   = -1;
            String         strErrMsg   = "";
            HttpStatusCode errorCode   = HttpStatusCode.OK;

            try
            {
                // Basic check again - document level
                FinanceDocumentController.FinanceDocumentBasicCheck(vmFIDoc);

                using (conn = new SqlConnection(Startup.DBConnectionString))
                {
                    await conn.OpenAsync();

                    // Check Home assignment with current user
                    try
                    {
                        HIHAPIUtility.CheckHIDAssignment(conn, vm.HID, usrName);
                    }
                    catch (Exception)
                    {
                        errorCode = HttpStatusCode.BadRequest;
                        throw;
                    }

                    // Perfrom the doc. validation
                    await FinanceDocumentController.FinanceDocumentBasicValidationAsync(vmFIDoc, conn, -1);

                    // 0) Start the trasnaction for modifications
                    tran = conn.BeginTransaction();

                    // 1) craete the doc header => nNewDocID
                    queryString = HIHDBUtility.GetFinDocHeaderInsertString();
                    cmd         = new SqlCommand(queryString, conn)
                    {
                        Transaction = tran
                    };

                    HIHDBUtility.BindFinDocHeaderInsertParameter(cmd, vmFIDoc, usrName);
                    SqlParameter idparam = cmd.Parameters.AddWithValue("@Identity", SqlDbType.Int);
                    idparam.Direction = ParameterDirection.Output;

                    Int32 nRst = await cmd.ExecuteNonQueryAsync();

                    nNewDocID  = (Int32)idparam.Value;
                    vmFIDoc.ID = nNewDocID;
                    cmd.Dispose();
                    cmd = null;

                    // 2), create the new account => nNewAccountID
                    queryString = HIHDBUtility.GetFinanceAccountHeaderInsertString();

                    cmd = new SqlCommand(queryString, conn)
                    {
                        Transaction = tran
                    };
                    HIHDBUtility.BindFinAccountInsertParameter(cmd, vmAccount, usrName);
                    SqlParameter idparam2 = cmd.Parameters.AddWithValue("@Identity", SqlDbType.Int);
                    idparam2.Direction = ParameterDirection.Output;

                    nRst = await cmd.ExecuteNonQueryAsync();

                    vmAccount.ID = (Int32)idparam2.Value;
                    cmd.Dispose();
                    cmd = null;

                    // 3) create the Asset part of account
                    vmAccount.ExtraInfo_AS.AccountID    = vmAccount.ID;
                    vmAccount.ExtraInfo_AS.RefDocForBuy = nNewDocID;
                    queryString = HIHDBUtility.GetFinanceAccountAssetInsertString();
                    cmd         = new SqlCommand(queryString, conn)
                    {
                        Transaction = tran
                    };
                    HIHDBUtility.BindFinAccountAssetInsertParameter(cmd, vmAccount.ExtraInfo_AS);
                    nRst = await cmd.ExecuteNonQueryAsync();

                    cmd.Dispose();
                    cmd = null;

                    // 4) create the doc items
                    foreach (FinanceDocumentItemUIViewModel ivm in vmFIDoc.Items)
                    {
                        if (ivm.AccountID == -1)
                        {
                            ivm.AccountID = vmAccount.ID;
                        }

                        queryString = HIHDBUtility.GetFinDocItemInsertString();
                        cmd         = new SqlCommand(queryString, conn)
                        {
                            Transaction = tran
                        };
                        HIHDBUtility.BindFinDocItemInsertParameter(cmd, ivm, nNewDocID);

                        await cmd.ExecuteNonQueryAsync();

                        cmd.Dispose();
                        cmd = null;

                        // Tags
                        if (ivm.TagTerms.Count > 0)
                        {
                            // Create tags
                            foreach (var term in ivm.TagTerms)
                            {
                                queryString = HIHDBUtility.GetTagInsertString();

                                cmd = new SqlCommand(queryString, conn, tran);

                                HIHDBUtility.BindTagInsertParameter(cmd, vm.HID, HIHTagTypeEnum.FinanceDocumentItem, nNewDocID, term, ivm.ItemID);

                                await cmd.ExecuteNonQueryAsync();

                                cmd.Dispose();
                                cmd = null;
                            }
                        }
                    }

                    // 5) Do the commit
                    tran.Commit();

                    // Update the buffer
                    // Account List
                    try
                    {
                        var cacheKey = String.Format(CacheKeys.FinAccountList, vm.HID, null);
                        this._cache.Remove(cacheKey);
                    }
                    catch (Exception)
                    {
                        // Do nothing here.
                    }
                    // B.S.
                    try
                    {
                        var cacheKey = String.Format(CacheKeys.FinReportBS, vm.HID);
                        this._cache.Remove(cacheKey);
                    }
                    catch (Exception)
                    {
                        // Do nothing here.
                    }
                }
            }
            catch (Exception exp)
            {
#if DEBUG
                System.Diagnostics.Debug.WriteLine(exp.Message);
#endif

                strErrMsg = exp.Message;
                if (errorCode == HttpStatusCode.OK)
                {
                    errorCode = HttpStatusCode.InternalServerError;
                }

                if (tran != null)
                {
                    tran.Rollback();
                }
            }
            finally
            {
                if (tran != null)
                {
                    tran.Dispose();
                    tran = null;
                }
                if (reader != null)
                {
                    reader.Dispose();
                    reader = null;
                }
                if (cmd != null)
                {
                    cmd.Dispose();
                    cmd = null;
                }
                if (conn != null)
                {
                    conn.Dispose();
                    conn = null;
                }
            }

            if (errorCode != HttpStatusCode.OK)
            {
                switch (errorCode)
                {
                case HttpStatusCode.Unauthorized:
                    return(Unauthorized());

                case HttpStatusCode.NotFound:
                    return(NotFound());

                case HttpStatusCode.BadRequest:
                    return(BadRequest(strErrMsg));

                default:
                    return(StatusCode(500, strErrMsg));
                }
            }

            return(Ok(nNewDocID));
        }