Beispiel #1
0
        public List <SummaryData> GetAllSelectedTrades(string pTrdSysCode, string pSeCptySn, string pCptySn, string pCdtyCode,
                                                       DateTime pBeginTradeDt, DateTime pEndTradeDt, string pTrdSysTicket, string pCptyTradeId, string pPermissionKeyInClause)
        {
            const string SELECT_OBJECT = "*";
            var          result        = new List <SummaryData>();
            Int32        tradeId       = 0;
            string       sql           = GetAllTradeSummarySqlCommandStr(pTrdSysCode, pSeCptySn, pCptySn, pCdtyCode,
                                                                         pBeginTradeDt, pEndTradeDt, pTrdSysTicket, pCptyTradeId, SELECT_OBJECT);

            if (pPermissionKeyInClause != "")
            {
                //Israel 11/20/2015 -- Added support for Null permission key
                //sql += " and " + pPermissionKeyInClause;
                sql += " and " + "( " + pPermissionKeyInClause + " or PERMISSION_KEY is null )";
            }

            using (SqlConnection conn = new SqlConnection(sqlConnStr))
            {
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    cmd.CommandType = System.Data.CommandType.Text;

                    conn.Open();
                    using (SqlDataReader dataReader = cmd.ExecuteReader())
                    {
                        if (dataReader.HasRows)
                        {
                            while (dataReader.Read())
                            {
                                result.Add(new SummaryData
                                {
                                    AdditionalConfirmSent = dataReader["ADDITIONAL_CONFIRM_SENT"].ToString(),
                                    AnalystName           = dataReader["ANALYST_NAME"].ToString(),
                                    ArchiveFlag           = dataReader["ARCHIVE_FLAG"].ToString(),
                                    BookingCoId           = DBUtils.HandleInt32IfNull(dataReader["BOOKING_CO_ID"].ToString()),
                                    BookingCoSn           = dataReader["BOOKING_CO_SN"].ToString(),
                                    //BookingCoSn = dataReader["SE_CPTY_SN"].ToString(),
                                    BkrDbUpd        = DBUtils.HandleInt32IfNull(dataReader["BKR_DB_UPD"].ToString()),
                                    BkrMeth         = dataReader["BKR_METH"].ToString(),
                                    BkrRqmt         = dataReader["BKR_RQMT"].ToString(),
                                    BkrStatus       = dataReader["BKR_STATUS"].ToString(),
                                    Book            = dataReader["BOOK"].ToString(),
                                    BrokerId        = DBUtils.HandleInt32IfNull(dataReader["BROKER_ID"].ToString()),
                                    BrokerLegalName = dataReader["BROKER_LEGAL_NAME"].ToString(),
                                    BrokerPrice     = dataReader["BROKER_PRICE"].ToString(),
                                    BrokerSn        = dataReader["BROKER_SN"].ToString(),
                                    BuySellInd      = dataReader["BUY_SELL_IND"].ToString(),
                                    CdtyCode        = dataReader["CDTY_CODE"].ToString(),
                                    CdtyGrpCode     = dataReader["CDTY_GRP_CODE"].ToString(),
                                    Cmt             = dataReader["CMT"].ToString(),
                                    //Comm = dataReader["COMM"].ToString(),
                                    CptyDbUpd     = DBUtils.HandleInt32IfNull(dataReader["CPTY_DB_UPD"].ToString()),
                                    CptyId        = DBUtils.HandleInt32IfNull(dataReader["CPTY_ID"].ToString()),
                                    CptyLegalName = dataReader["CPTY_LEGAL_NAME"].ToString(),
                                    //CptyLn = dataReader["CPTY_LN"].ToString(),
                                    CptyMeth      = dataReader["CPTY_METH"].ToString(),
                                    CptyRqmt      = dataReader["CPTY_RQMT"].ToString(),
                                    CptySn        = dataReader["CPTY_SN"].ToString(),
                                    CptyStatus    = dataReader["CPTY_STATUS"].ToString(),
                                    CptyTradeId   = dataReader["CPTY_TRADE_ID"].ToString(),
                                    CurrentBusnDt = DBUtils.HandleDateTimeIfNull(dataReader["CURRENT_BUSN_DT"].ToString()),
                                    //EfsCptySn = dataReader["EFS_CPTY_SN"].ToString(),
                                    //EfsFlag = dataReader["EFS_FLAG"].ToString(),
                                    EndDt                     = DBUtils.HandleDateTimeIfNull(dataReader["END_DT"].ToString()),
                                    FinalApprovalFlag         = dataReader["FINAL_APPROVAL_FLAG"].ToString(),
                                    FinalApprovalTimestampGmt = DBUtils.HandleDateTimeIfNull(dataReader["FINAL_APPROVAL_TIMESTAMP_GMT"].ToString()),
                                    GroupXref                 = dataReader["GROUP_XREF"].ToString(),
                                    HasProblemFlag            = dataReader["HAS_PROBLEM_FLAG"].ToString(),
                                    Id                      = DBUtils.HandleInt32IfNull(dataReader["ID"].ToString()),
                                    InceptionDt             = DBUtils.HandleDateTimeIfNull(dataReader["INCEPTION_DT"].ToString()),
                                    IsTestBook              = dataReader["IS_TEST_BOOK"].ToString(),
                                    LastTrdEditTimestampGmt = DBUtils.HandleDateTimeIfNull(dataReader["LAST_TRD_EDIT_TIMESTAMP_GMT"].ToString()),
                                    LastUpdateTimestampGmt  = DBUtils.HandleDateTimeIfNull(dataReader["LAST_UPDATE_TIMESTAMP_GMT"].ToString()),
                                    LocationSn              = dataReader["LOCATION_SN"].ToString(),
                                    MigrateInd              = dataReader["MIGRATE_IND"].ToString(),
                                    NoconfDbUpd             = DBUtils.HandleInt32IfNull(dataReader["NOCONF_DB_UPD"].ToString()),
                                    NoconfMeth              = dataReader["NOCONF_METH"].ToString(),
                                    NoconfRqmt              = dataReader["NOCONF_RQMT"].ToString(),
                                    NoconfStatus            = dataReader["NOCONF_STATUS"].ToString(),
                                    OpsDetActFlag           = dataReader["OPS_DET_ACT_FLAG"].ToString(),
                                    OptnPremPrice           = dataReader["OPTN_PREM_PRICE"].ToString(),
                                    OptnPutCallInd          = dataReader["OPTN_PUT_CALL_IND"].ToString(),
                                    OptnStrikePrice         = dataReader["OPTN_STRIKE_PRICE"].ToString(),
                                    //PayPrice = dataReader["PAY_PRICE"].ToString(),
                                    PermissionKey = dataReader["PERMISSION_KEY"].ToString(),
                                    PlAmt         = dataReader["PL_AMT"].ToString(),
                                    PriceDesc     = dataReader["PRICE_DESC"].ToString(),
                                    Priority      = dataReader["PRIORITY"].ToString(),
                                    //Qty = DBUtils.HandleFloatIfNull(dataReader["QTY"].ToString()),
                                    QuantityDescription = dataReader["QTY_DESC"].ToString(),
                                    QtyTot = DBUtils.HandleFloatIfNull(dataReader["QTY_TOT"].ToString()),
                                    ReadyForFinalApprovalFlag = dataReader["READY_FOR_FINAL_APPROVAL_FLAG"].ToString(),
                                    //RecPrice = dataReader["REC_PRICE"].ToString(),
                                    RecentInd        = DBUtils.HandleInt32IfNull(dataReader["RECENT_IND"].ToString()),
                                    RefSn            = dataReader["REF_SN"].ToString(),
                                    RplyRdyToSndFlag = dataReader["RPLY_RDY_TO_SND_FLAG"].ToString(),
                                    //SeCptySn = dataReader["SE_CPTY_SN"].ToString(),
                                    SetcDbUpd      = DBUtils.HandleInt32IfNull(dataReader["SETC_DB_UPD"].ToString()),
                                    SetcMeth       = dataReader["SETC_METH"].ToString(),
                                    SetcRqmt       = dataReader["SETC_RQMT"].ToString(),
                                    SetcStatus     = dataReader["SETC_STATUS"].ToString(),
                                    StartDt        = DBUtils.HandleDateTimeIfNull(dataReader["START_DT"].ToString()),
                                    SttlType       = dataReader["STTL_TYPE"].ToString(),
                                    TradeDesc      = dataReader["TRADE_DESC"].ToString(),
                                    TradeDt        = DBUtils.HandleDateTimeIfNull(dataReader["TRADE_DT"].ToString()),
                                    TradeId        = DBUtils.HandleInt32IfNull(dataReader["TRADE_ID"].ToString()),
                                    Trader         = dataReader["TRADER"].ToString(),
                                    TradeStatCode  = dataReader["TRADE_STAT_CODE"].ToString(),
                                    TradeTypeCode  = dataReader["TRADE_TYPE_CODE"].ToString(),
                                    TransactionSeq = DBUtils.HandleInt32IfNull(dataReader["TRANSACTION_SEQ"].ToString()),
                                    TransportDesc  = dataReader["TRANSPORT_DESC"].ToString(),
                                    TrdSysCode     = dataReader["TRD_SYS_CODE"].ToString(),
                                    TradeSysTicket = dataReader["TRD_SYS_TICKET"].ToString(),
                                    //UomDurCode = dataReader["UOM_DUR_CODE"].ToString(),
                                    VerblDbUpd  = DBUtils.HandleInt32IfNull(dataReader["VERBL_DB_UPD"].ToString()),
                                    VerblMeth   = dataReader["VERBL_METH"].ToString(),
                                    VerblRqmt   = dataReader["VERBL_RQMT"].ToString(),
                                    VerblStatus = dataReader["VERBL_STATUS"].ToString(),
                                    Version     = DBUtils.HandleInt32IfNull(dataReader["VERSION"].ToString()),
                                    Xref        = dataReader["XREF"].ToString()
                                });
                            }
                        }
                    }
                }
            }
            return(result);
        }
        public Int32 Insert(TradeRqmtConfirmBlobDto pData)
        {
            const string insertTSql =
                @"INSERT INTO TRADE_RQMT_CONFIRM_BLOB(ID, TRADE_RQMT_CONFIRM_ID, IMAGE_FILE_EXT)
                VALUES(@ID, @TRADE_RQMT_CONFIRM_ID, @IMAGE_FILE_EXT);
                SELECT DOC_BLOB.PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT()
                FROM TRADE_RQMT_CONFIRM_BLOB
                WHERE ID = @ID";

            int    newId = DBUtils.GetNextSequence(sqlConnStr, SEQ_NAME);
            string serverPath;

            byte[] serverTxn;
            using (TransactionScope ts = new TransactionScope())
            {
                using (SqlConnection conn = new SqlConnection(sqlConnStr))
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(insertTSql, conn))
                    {
                        cmd.Parameters.Add("@ID", SqlDbType.Int).Value = newId;
                        cmd.Parameters.Add("@TRADE_RQMT_CONFIRM_ID", SqlDbType.VarChar).Value = pData.TradeRqmtConfirmId;
                        cmd.Parameters.Add("@IMAGE_FILE_EXT", SqlDbType.VarChar).Value        = DBUtils.ValueStringOrDBNull(pData.ImageFileExt.ToUpper());
                        using (SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.SingleRow))
                        {
                            rdr.Read();
                            serverPath = rdr.GetSqlString(0).Value;
                            serverTxn  = rdr.GetSqlBinary(1).Value;
                            rdr.Close();
                        }
                    }
                    //SaveDocImageFile(pFileName, serverPath, serverTxn);
                    SaveDocImageByteArray(pData.DocBlob, serverPath, serverTxn);
                }
                ts.Complete();
            }
            return(newId);
        }
        public void Update(TradeRqmtConfirmBlobDto pData)
        {
            const string updateTSql =
                @"UPDATE TRADE_RQMT_CONFIRM_BLOB
                SET TRADE_RQMT_CONFIRM_ID = @TRADE_RQMT_CONFIRM_ID, IMAGE_FILE_EXT = @IMAGE_FILE_EXT
                WHERE TRADE_RQMT_CONFIRM_ID = @TRADE_RQMT_CONFIRM_ID
                SELECT TOP(1) DOC_BLOB.PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT() 
                FROM TRADE_RQMT_CONFIRM_BLOB
                WHERE TRADE_RQMT_CONFIRM_ID = @TRADE_RQMT_CONFIRM_ID";

            using (TransactionScope ts = new TransactionScope())
            {
                using (SqlConnection conn = new SqlConnection(sqlConnStr))
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(updateTSql, conn))
                    {
                        //cmd.Parameters.Add("@ID", SqlDbType.Int).Value = pData.Id;
                        cmd.Parameters.Add("@TRADE_RQMT_CONFIRM_ID", SqlDbType.VarChar).Value = pData.TradeRqmtConfirmId;
                        cmd.Parameters.Add("@IMAGE_FILE_EXT", SqlDbType.VarChar).Value        = DBUtils.ValueStringOrDBNull(pData.ImageFileExt.ToUpper());
                        using (SqlDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                // Get the pointer for file
                                string path = reader.GetString(0);
                                byte[] transactionContext = reader.GetSqlBytes(1).Buffer;

                                // Create the SqlFileStream
                                using (Stream fileStream = new SqlFileStream(path, transactionContext, FileAccess.Write, FileOptions.SequentialScan, allocationSize: 0))
                                {
                                    // Write a byte array to the file. This will replace any data in the file.
                                    //fileStream.WriteByte(0x01);
                                    fileStream.Write(pData.DocBlob, 0, pData.DocBlob.Length);
                                }
                            }
                        }
                    }
                }
                ts.Complete();
            }
        }
Beispiel #4
0
        public Int32 AddTradeRqmt(Int32 pTradeId, string pRqmtCode, string pReference, string pCmt)
        {
            string updateSql    = DBUtils.SCHEMA_NAME + "PKG_TRADE_RQMT$P_ADD_TRADE_RQMT_WITH_ID";
            Int32  rowsInserted = 0;
            Int32  seqNo        = 0;
            string seqName      = "SEQ_TRADE_RQMT";

            seqNo = DBUtils.GetNextSequence(sqlConnStr, seqName);

            using (SqlConnection conn = new SqlConnection(sqlConnStr))
            {
                using (SqlCommand cmd = new SqlCommand(updateSql, conn))
                {
                    //There are  3 overloaded versions of function, containing 3, 4 and 6 parameters.
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.Parameters.Add("@p_id", System.Data.SqlDbType.Int).Value            = seqNo;
                    cmd.Parameters.Add("@p_trade_id", System.Data.SqlDbType.Int).Value      = pTradeId;
                    cmd.Parameters.Add("@p_rqmt_code", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pRqmtCode);
                    cmd.Parameters.Add("@p_reference", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pReference);
                    cmd.Parameters.Add("@p_cmt", System.Data.SqlDbType.VarChar).Value       = DBUtils.ValueStringOrDBNull(pCmt);

                    conn.Open();
                    rowsInserted = cmd.ExecuteNonQuery();
                }
            }
            if (rowsInserted == 0)
            {
                return(rowsInserted);
            }
            else
            {
                return(seqNo);
            }
        }
Beispiel #5
0
        public Int32 UpdateTradeRqmt(Int32 pId, DateTime pCompletedDt, string pSecondChk, string pStatusCode, string pReference, string pCmt)
        {
            string updateSql   = DBUtils.SCHEMA_NAME + "PKG_TRADE_RQMT$p_update_trade_rqmt";
            Int32  rowsUpdated = 0;

            using (SqlConnection conn = new SqlConnection(sqlConnStr))
            {
                using (SqlCommand cmd = new SqlCommand(updateSql, conn))
                {
                    //There are  3 overloaded versions of function, containing 3, 4 and 6 parameters.
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.Parameters.Add("@p_rqmt_id", System.Data.SqlDbType.Int).Value          = pId;
                    cmd.Parameters.Add("@p_completion_date", System.Data.SqlDbType.Date).Value = DBUtils.ValueDateTimeOrDbNull(pCompletedDt);
                    cmd.Parameters.Add("@p_second_chk", System.Data.SqlDbType.VarChar).Value   = DBUtils.ValueStringOrDBNull(pSecondChk);
                    cmd.Parameters.Add("@p_status", System.Data.SqlDbType.VarChar).Value       = DBUtils.ValueStringOrDBNull(pStatusCode);
                    cmd.Parameters.Add("@p_reference", System.Data.SqlDbType.VarChar).Value    = DBUtils.ValueStringOrDBNull(pReference);
                    cmd.Parameters.Add("@p_cmt", System.Data.SqlDbType.VarChar).Value          = DBUtils.ValueStringOrDBNull(pCmt);

                    conn.Open();
                    rowsUpdated = cmd.ExecuteNonQuery();
                }
            }
            return(rowsUpdated);
        }
Beispiel #6
0
        public Int32 UpdateStatus(AssociatedDocsDto pAssocDocsData)
        {
            //Note -- this procedure also inserts data
            string updateSql   = DBUtils.SCHEMA_NAME + "PKG_INBOUND$p_update_asso_status";
            Int32  rowsUpdated = 0;

            using (SqlConnection conn = new SqlConnection(sqlConnStr))
            {
                using (SqlCommand cmd = new SqlCommand(updateSql, conn))
                {
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.Parameters.Add("@p_inb_id", System.Data.SqlDbType.Int).Value            = pAssocDocsData.InboundDocsId;
                    cmd.Parameters.Add("@p_file_name", System.Data.SqlDbType.VarChar).Value     = DBUtils.ValueStringOrDBNull(pAssocDocsData.FileName);
                    cmd.Parameters.Add("@p_trade_id", System.Data.SqlDbType.Int).Value          = pAssocDocsData.TradeId;
                    cmd.Parameters.Add("@p_status", System.Data.SqlDbType.VarChar).Value        = DBUtils.ValueStringOrDBNull(pAssocDocsData.DocStatusCode);
                    cmd.Parameters.Add("@p_cdty_grp_code", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pAssocDocsData.CdtyGroupCode);
                    cmd.Parameters.Add("@p_cpty_sn", System.Data.SqlDbType.VarChar).Value       = DBUtils.ValueStringOrDBNull(pAssocDocsData.CptySn);
                    cmd.Parameters.Add("@p_broker_sn", System.Data.SqlDbType.VarChar).Value     = DBUtils.ValueStringOrDBNull(pAssocDocsData.BrokerSn);
                    cmd.Parameters.Add("@p_rqmt_id", System.Data.SqlDbType.Int).Value           = pAssocDocsData.TradeRqmtId;
                    cmd.Parameters.Add("@p_rqmt_status", System.Data.SqlDbType.VarChar).Value   = DBUtils.ValueStringOrDBNull(pAssocDocsData.RqmtStatus);
                    cmd.Parameters.Add("@p_rqmt_type", System.Data.SqlDbType.VarChar).Value     = DBUtils.ValueStringOrDBNull(pAssocDocsData.DocTypeCode);
                    cmd.Parameters.Add("@p_sec_check", System.Data.SqlDbType.VarChar).Value     = DBUtils.ValueStringOrDBNull(pAssocDocsData.SecValidateReqFlag);
                    cmd.Parameters.Add("@p_index_val", System.Data.SqlDbType.Int).Value         = pAssocDocsData.IndexVal;
                    SqlParameter returnValue = new SqlParameter {
                        Direction = ParameterDirection.ReturnValue
                    };
                    cmd.Parameters.Add(returnValue);

                    conn.Open();
                    cmd.ExecuteNonQuery();
                    return(Convert.ToInt32(returnValue.Value));
                }
            }
            return(rowsUpdated);
        }
Beispiel #7
0
        public Int32 GetCount(Int32 pInboundDocsId, string pDocStatusCode)
        {
            Int32  result = 0;
            string sql    = "select count(*) cnt from " + DBUtils.SCHEMA_NAME + "associated_docs " +
                            "where inbound_docs_id = @inbound_docs_id and doc_status_code = @doc_status_code";

            using (SqlConnection conn = new SqlConnection(sqlConnStr))
            {
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    cmd.CommandType = System.Data.CommandType.Text;
                    cmd.Parameters.Add("@inbound_docs_id", System.Data.SqlDbType.Int).Value     = pInboundDocsId;
                    cmd.Parameters.Add("@doc_status_code", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pDocStatusCode);
                    conn.Open();
                    using (SqlDataReader dataReader = cmd.ExecuteReader())
                    {
                        if (dataReader.HasRows)
                        {
                            while (dataReader.Read())
                            {
                                result = dataReader.GetInt32(dataReader.GetOrdinal("cnt"));
                            }
                        }
                    }
                }
            }
            return(result);
        }
Beispiel #8
0
        public List <AssociatedDoc> GetAll(string pTradeIdList)
        //public List<AssociatedDoc> GetAllSelectedTrades(string pTrdSysCode, string pSeCptySn, string pCptySn,
        //            string pCdtyCode, DateTime pBeginTradeDt, DateTime pEndTradeDt, string pTrdSysTicket)
        {
            var result = new List <AssociatedDoc>();
            //string sql = "select * from " + DBUtils.SCHEMA_NAME + "v_active_associated_docs v, " +
            //             DBUtils.GetAllSqlParamStr(pTrdSysCode, pSeCptySn, pCptySn, pCdtyCode,
            //             pBeginTradeDt, pEndTradeDt, pTrdSysTicket);

            string sql = "select * from " + DBUtils.SCHEMA_NAME + "v_trade_rqmt_confirm " +
                         "where trade_id in " + pTradeIdList;

            using (SqlConnection conn = new SqlConnection(sqlConnStr))
            {
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    cmd.CommandType = System.Data.CommandType.Text;
                    conn.Open();
                    using (SqlDataReader dataReader = cmd.ExecuteReader())
                    {
                        if (dataReader.HasRows)
                        {
                            while (dataReader.Read())
                            {
                                result.Add(new AssociatedDoc
                                {
                                    Id                     = DBUtils.HandleInt32IfNull(dataReader["id"].ToString()),
                                    InboundDocsId          = DBUtils.HandleInt32IfNull(dataReader["inbound_docs_id"].ToString()),
                                    TradeFinalApprovalFlag = dataReader["trade_final_approval_flag"].ToString(),
                                    IndexVal               = DBUtils.HandleInt32IfNull(dataReader["index_val"].ToString()),
                                    TrdSysTicket           = dataReader["TRD_SYS_TICKET"].ToString(),
                                    TrdSysCode             = dataReader["TRD_SYS_CODE"].ToString(),
                                    FileName               = dataReader["file_name"].ToString(),
                                    TradeId                = DBUtils.HandleInt32IfNull(dataReader["trade_id"].ToString()),
                                    DocStatusCode          = dataReader["doc_status_code"].ToString(),
                                    AssociatedBy           = dataReader["associated_by"].ToString(),
                                    AssociatedDt           = DBUtils.HandleDateTimeIfNull(dataReader["associated_dt"].ToString()),
                                    FinalApprovedBy        = dataReader["final_approved_by"].ToString(),
                                    FinalApprovedDt        = DBUtils.HandleDateTimeIfNull(dataReader["final_approved_dt"].ToString()),
                                    DisputedBy             = dataReader["disputed_by"].ToString(),
                                    DisputedDt             = DBUtils.HandleDateTimeIfNull(dataReader["disputed_dt"].ToString()),
                                    DiscardedBy            = dataReader["discarded_by"].ToString(),
                                    DiscardedDt            = DBUtils.HandleDateTimeIfNull(dataReader["discarded_dt"].ToString()),
                                    VaultedBy              = dataReader["vaulted_by"].ToString(),
                                    VaultedDt              = DBUtils.HandleDateTimeIfNull(dataReader["vaulted_dt"].ToString()),
                                    CdtyGroupCode          = dataReader["cdty_group_code"].ToString(),
                                    CptySn                 = dataReader["cpty_sn"].ToString(),
                                    BrokerSn               = dataReader["broker_sn"].ToString(),
                                    DocTypeCode            = dataReader["doc_type_code"].ToString(),
                                    SecValidateReqFlag     = dataReader["sec_validate_req_flag"].ToString(),
                                    TradeRqmtId            = DBUtils.HandleInt32IfNull(dataReader["trade_rqmt_id"].ToString()),
                                    XmitStatusCode         = dataReader["xmit_status_code"].ToString(),
                                    XmitValue              = dataReader["xmit_value"].ToString(),
                                    SentTo                 = dataReader["sent_to"].ToString()
                                });
                            }
                        }
                    }
                }
            }
            return(result);
        }
Beispiel #9
0
        public Int32 SetFinalApprovalFlag(Int32 pTradeId, string pFinalApprovalFlag, string pOnlyIfReadyFlag, string pUserName)
        {
            string updateSql   = DBUtils.SCHEMA_NAME + "pkg_trade_appr$p_set_final_approval_flag";
            Int32  rowsUpdated = 0;

            using (SqlConnection conn = new SqlConnection(sqlConnStr))
            {
                using (SqlCommand cmd = new SqlCommand(updateSql, conn))
                {
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.Parameters.Add("@p_trade_id", System.Data.SqlDbType.Int).Value               = pTradeId;
                    cmd.Parameters.Add("@p_approval_flag", System.Data.SqlDbType.VarChar).Value      = DBUtils.ValueStringOrDBNull(pFinalApprovalFlag);
                    cmd.Parameters.Add("@p_only_if_ready_flag", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pOnlyIfReadyFlag);
                    cmd.Parameters.Add("@p_appr_by_username", System.Data.SqlDbType.VarChar).Value   = DBUtils.ValueStringOrDBNull(pUserName);
                    cmd.Parameters.Add("@open_rqmt_ct", System.Data.SqlDbType.Int).Direction         = ParameterDirection.Output;

                    conn.Open();
                    cmd.ExecuteNonQuery();
                    rowsUpdated = Convert.ToInt32(cmd.Parameters["@open_rqmt_ct"].Value);
                }
            }
            return(rowsUpdated);
        }
Beispiel #10
0
        public Int32 UpdateStatus(Int32 pId, string pStatus)
        {
            Int32  rowsUpdated = 0;
            string sql         = "update " + DBUtils.SCHEMA_NAME + "inbound_docs " +
                                 " set doc_status_code = @doc_status_code " +
                                 " where id = @id";

            using (SqlConnection conn = new SqlConnection(sqlConnStr))
            {
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    cmd.CommandType = System.Data.CommandType.Text;
                    cmd.Parameters.Add("@id", System.Data.SqlDbType.Int).Value = pId;
                    cmd.Parameters.Add("@doc_status_code", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pStatus);

                    conn.Open();
                    rowsUpdated = cmd.ExecuteNonQuery();
                }
            }
            return(rowsUpdated);
        }
Beispiel #11
0
        public Int32 Update(InboundDocsDto pData)
        {
            string updateSql   = DBUtils.SCHEMA_NAME + "PKG_INBOUND$p_update_inbound_doc";
            Int32  rowsUpdated = 0;

            using (SqlConnection conn = new SqlConnection(sqlConnStr))
            {
                using (SqlCommand cmd = new SqlCommand(updateSql, conn))
                {
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.Parameters.Add("@p_id", System.Data.SqlDbType.Int).Value                  = pData.Id;
                    cmd.Parameters.Add("@p_caller_ref", System.Data.SqlDbType.VarChar).Value      = DBUtils.ValueStringOrDBNull(pData.CallerRef);
                    cmd.Parameters.Add("@p_sent_to", System.Data.SqlDbType.VarChar).Value         = DBUtils.ValueStringOrDBNull(pData.SentTo);
                    cmd.Parameters.Add("@p_file_name", System.Data.SqlDbType.VarChar).Value       = DBUtils.ValueStringOrDBNull(pData.FileName);
                    cmd.Parameters.Add("@p_sender", System.Data.SqlDbType.VarChar).Value          = DBUtils.ValueStringOrDBNull(pData.Sender);
                    cmd.Parameters.Add("@p_cmt", System.Data.SqlDbType.VarChar).Value             = DBUtils.ValueStringOrDBNull(pData.Cmt);
                    cmd.Parameters.Add("@p_doc_status_code", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.DocStatusCode);

                    conn.Open();
                    rowsUpdated = cmd.ExecuteNonQuery();
                }
            }
            return(rowsUpdated);
        }
Beispiel #12
0
        public Int32 Insert(InboundDocsDto pData)
        {
            Int32 newId = DBUtils.GetNextSequence(sqlConnStr, SEQ_NAME);

            string sql = "Insert into " + DBUtils.SCHEMA_NAME + "inbound_docs " +
                         "   (id, caller_ref, sent_to, rcvd_ts, file_name, sender, cmt, doc_status_code, has_auto_ascted_flag, " +
                         " mapped_cpty_sn, mapped_brkr_sn, mapped_cdty_code, job_ref ) " +
                         " Values " +
                         "   (@id, @caller_ref, @sent_to, @rcvd_ts, @file_name, @sender, @cmt, @doc_status_code, @has_auto_ascted_flag, " +
                         " @mapped_cpty_sn, @mapped_brkr_sn, @mapped_cdty_code, @job_ref ) ";

            using (SqlConnection conn = new SqlConnection(sqlConnStr))
            {
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    cmd.CommandType = System.Data.CommandType.Text;
                    cmd.Parameters.Add("@id", System.Data.SqlDbType.Int).Value                       = newId;
                    cmd.Parameters.Add("@caller_ref", System.Data.SqlDbType.VarChar).Value           = DBUtils.ValueStringOrDBNull(pData.CallerRef);
                    cmd.Parameters.Add("@sent_to", System.Data.SqlDbType.VarChar).Value              = DBUtils.ValueStringOrDBNull(pData.SentTo);
                    cmd.Parameters.Add("@rcvd_ts", System.Data.SqlDbType.DateTime).Value             = DBUtils.ValueDateTimeOrDbNull(pData.RcvdTs);
                    cmd.Parameters.Add("@file_name", System.Data.SqlDbType.VarChar).Value            = DBUtils.ValueStringOrDBNull(pData.FileName);
                    cmd.Parameters.Add("@sender", System.Data.SqlDbType.VarChar).Value               = DBUtils.ValueStringOrDBNull(pData.Sender);
                    cmd.Parameters.Add("@cmt", System.Data.SqlDbType.VarChar).Value                  = DBUtils.ValueStringOrDBNull(pData.Cmt);
                    cmd.Parameters.Add("@doc_status_code", System.Data.SqlDbType.VarChar).Value      = DBUtils.ValueStringOrDBNull(pData.DocStatusCode);
                    cmd.Parameters.Add("@has_auto_ascted_flag", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.HasAutoAsctedFlag);
                    cmd.Parameters.Add("@mapped_cpty_sn", System.Data.SqlDbType.VarChar).Value       = DBUtils.ValueStringOrDBNull(pData.MappedCptySn);
                    cmd.Parameters.Add("@mapped_brkr_sn", System.Data.SqlDbType.VarChar).Value       = DBUtils.ValueStringOrDBNull(pData.MappedBrkrSn);
                    cmd.Parameters.Add("@mapped_cdty_code", System.Data.SqlDbType.VarChar).Value     = DBUtils.ValueStringOrDBNull(pData.MappedCdtyCode);
                    cmd.Parameters.Add("@job_ref", System.Data.SqlDbType.VarChar).Value              = DBUtils.ValueStringOrDBNull(pData.JobRef);
                    conn.Open();
                    cmd.ExecuteNonQuery();
                }
            }
            return(newId);
        }