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