public List <InboundDocUserFlagDto> Get(string pInboundUser)
{
var result = new List <InboundDocUserFlagDto>();
string sql = "select id, inbound_doc_id, inbound_user, flag_type, comments " +
" from " + DBUtils.SCHEMA_NAME + "inbound_doc_user_flag " +
" where inbound_user = @inbound_user ";
using (SqlConnection conn = new SqlConnection(sqlConnStr))
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.CommandType = System.Data.CommandType.Text;
cmd.Parameters.Add("@inbound_user", System.Data.SqlDbType.VarChar).Value = pInboundUser;
conn.Open();
using (SqlDataReader dataReader = cmd.ExecuteReader())
{
if (dataReader.HasRows)
{
while (dataReader.Read())
{
result.Add(new InboundDocUserFlagDto
{
Id = DBUtils.HandleInt32IfNull(dataReader["id"].ToString()),
InboundDocId = DBUtils.HandleInt32IfNull(dataReader["inbound_doc_id"].ToString()),
InboundUser = dataReader["inbound_user"].ToString(),
FlagType = dataReader["flag_type"].ToString(),
Comments = dataReader["comments"].ToString()
});
}
}
}
}
}
return(result);
}
public List <InbAttribMapPhraseDto> GetPhrases(Int32 pMappedValueId)
{
var result = new List <InbAttribMapPhraseDto>();
string sql = "select * from " + DBUtils.SCHEMA_NAME + "inb_attrib_map_phrase " +
" where active_flag = 'Y' and " +
" inb_attrib_map_val_id = @inb_attrib_map_val_id";
using (SqlConnection conn = new SqlConnection(sqlConnStr))
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.CommandType = System.Data.CommandType.Text;
cmd.Parameters.Add("@inb_attrib_map_val_id", System.Data.SqlDbType.Int).Value = pMappedValueId;
conn.Open();
using (SqlDataReader dataReader = cmd.ExecuteReader())
{
if (dataReader.HasRows)
{
while (dataReader.Read())
{
result.Add(new InbAttribMapPhraseDto
{
Id = DBUtils.HandleInt32IfNull(dataReader["id"].ToString()),
InbAttribMapValId = DBUtils.HandleInt32IfNull(dataReader["inb_attrib_map_val_id"].ToString()),
Phrase = dataReader["phrase"].ToString(),
ActiveFlag = dataReader["active_flag"].ToString()
});
}
}
}
}
}
return(result);
}
public List <UserFiltersOpsmgrDto> GetAll(string pUserId)
{
var result = new List <UserFiltersOpsmgrDto>();
string sql = "select id, user_id, descr, filter_expr " +
"from " + DBUtils.SCHEMA_NAME + "v_user_filters " +
"where user_id = @user_id ";
using (SqlConnection conn = new SqlConnection(sqlConnStr))
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.CommandType = System.Data.CommandType.Text;
cmd.Parameters.Add("@user_id", System.Data.SqlDbType.VarChar).Value = pUserId;
conn.Open();
using (SqlDataReader dataReader = cmd.ExecuteReader())
{
if (dataReader.HasRows)
{
while (dataReader.Read())
{
result.Add(new UserFiltersOpsmgrDto
{
Id = DBUtils.HandleInt32IfNull(dataReader["id"].ToString()),
UserId = dataReader["user_id"].ToString(),
Descr = dataReader["descr"].ToString(),
FilterExpr = dataReader["filter_expr"].ToString()
});
}
}
}
}
}
return(result);
}
public ImagesDto GetByDocId(Int32 docsId, ImagesDtoType imagesDtoType)
{
ImagesDto imagesDto = null;
using (var ts = new TransactionScope())
{
using (var conn = new SqlConnection(sqlConnStr))
{
conn.Open();
var selectTSqlCmd = GetSelectByDocIdSql(imagesDtoType);
using (var cmd = new SqlCommand(selectTSqlCmd, conn))
{
cmd.Parameters.AddWithValue("@DocsId", docsId);
using (var dataReader = cmd.ExecuteReader())
{
if (dataReader.Read())
{
var serverTxn = dataReader.GetSqlBinary(4).Value;
imagesDto = new ImagesDto(
DBUtils.HandleInt32IfNull(dataReader["DOCS_ID"].ToString()),
ReadBytesFromSqlFileStream(Convert.ToString(dataReader["MARKUP_IMAGE_PATH_NAME"]), serverTxn),
Convert.ToString(dataReader["MARKUP_IMAGE_FILE_EXT"]),
ReadBytesFromSqlFileStream(Convert.ToString(dataReader["ORIG_IMAGE_PATH_NAME"]), serverTxn),
Convert.ToString(dataReader["ORIG_IMAGE_FILE_EXT"]),
imagesDtoType,
Convert.ToInt32(dataReader["ID"])
);
}
}
}
}
ts.Complete();
}
return(imagesDto);
}
public List <AssociatedDoc> GetAll()
{
var result = new List <AssociatedDoc>();
string sql = "select * from " + DBUtils.SCHEMA_NAME + "v_active_associated_docs ";
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 List <RqmtData> GetAll()
{
var result = new List <RqmtData>();
string sql = "select * from " + DBUtils.SCHEMA_NAME + "v_pc_trade_rqmt " +
" where final_approval_flag = 'N' " +
" order by TRANSACTION_SEQ ASC";
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 RqmtData
{
CancelTradeNotifyId = DBUtils.HandleInt32IfNull(dataReader["CANCEL_TRADE_NOTIFY_ID"].ToString()),
Category = dataReader["CATEGORY"].ToString(),
Cmt = dataReader["CMT"].ToString(),
TrdSysTicket = dataReader["TRD_SYS_TICKET"].ToString(),
TrdSysCode = dataReader["TRD_SYS_CODE"].ToString(),
CompletedDt = DBUtils.HandleDateTimeIfNull(dataReader["COMPLETED_DT"].ToString()),
CompletedTimestampGmt = DBUtils.HandleDateTimeIfNull(dataReader["COMPLETED_TIMESTAMP_GMT"].ToString()),
DelphiConstant = dataReader["DELPHI_CONSTANT"].ToString(),
DisplayText = dataReader["DISPLAY_TEXT"].ToString(),
FinalApprovalFlag = dataReader["FINAL_APPROVAL_FLAG"].ToString(),
GuiColorCode = dataReader["GUI_COLOR_CODE"].ToString(),
Id = DBUtils.HandleInt32IfNull(dataReader["ID"].ToString()),
//PrelimAppr = dataReader["PRELIM_APPR"].ToString(),
ProblemFlag = dataReader["PROBLEM_FLAG"].ToString(),
Reference = dataReader["REFERENCE"].ToString(),
Rqmt = dataReader["RQMT"].ToString(),
RqmtTradeNotifyId = DBUtils.HandleInt32IfNull(dataReader["RQMT_TRADE_NOTIFY_ID"].ToString()),
SecondCheckFlag = dataReader["SECOND_CHECK_FLAG"].ToString(),
Status = dataReader["STATUS"].ToString(),
TerminalFlag = dataReader["TERMINAL_FLAG"].ToString(),
TradeId = DBUtils.HandleInt32IfNull(dataReader["TRADE_ID"].ToString()),
TransactionSeq = DBUtils.HandleInt32IfNull(dataReader["TRANSACTION_SEQ"].ToString())
});
}
}
}
}
}
return(result);
}
public List <TradeRqmtConfirmBlobDto> GetAll()
{
const string selectTSqlCmd = @"SELECT ID, TRADE_RQMT_CONFIRM_ID, IMAGE_FILE_EXT, DOC_BLOB.PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT()
FROM TRADE_RQMT_CONFIRM_BLOB";
var result = new List <TradeRqmtConfirmBlobDto>();
string serverPath;
byte[] serverTxn;
byte[] blobByteArray;
using (TransactionScope ts = new TransactionScope())
{
using (SqlConnection conn = new SqlConnection(sqlConnStr))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(selectTSqlCmd, conn))
{
using (SqlDataReader dataReader = cmd.ExecuteReader())
{
if (dataReader.HasRows)
{
while (dataReader.Read())
{
serverPath = dataReader.GetSqlString(3).Value;
serverTxn = dataReader.GetSqlBinary(4).Value;
using (SqlFileStream sqlFileStr = new SqlFileStream(serverPath, serverTxn, FileAccess.Read))
{
using (MemoryStream memStream = new MemoryStream())
{
sqlFileStr.CopyTo(memStream);
blobByteArray = memStream.ToArray();
}
sqlFileStr.Close();
}
result.Add(new TradeRqmtConfirmBlobDto
{
Id = DBUtils.HandleInt32IfNull(dataReader["ID"].ToString()),
TradeRqmtConfirmId = DBUtils.HandleInt32IfNull(dataReader["TRADE_RQMT_CONFIRM_ID"].ToString()),
ImageFileExt = dataReader["IMAGE_FILE_EXT"].ToString(),
DocBlob = blobByteArray
});
}
}
}
}
}
ts.Complete();
}
return(result);
}
public List <TradeRqmtConfirm> GetAll(string pTradeIdList)
{
var result = new List <TradeRqmtConfirm>();
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 TradeRqmtConfirm
{
ActiveFlag = dataReader["ACTIVE_FLAG"].ToString(),
ConfirmCmt = dataReader["CONFIRM_CMT"].ToString(),
ConfirmLabel = dataReader["CONFIRM_LABEL"].ToString(),
FaxTelexInd = dataReader["FAX_TELEX_IND"].ToString(),
FaxTelexNumber = dataReader["FAX_TELEX_NUMBER"].ToString(),
FinalApprovalFlag = dataReader["FINAL_APPROVAL_FLAG"].ToString(),
Id = DBUtils.HandleInt32IfNull(dataReader["ID"].ToString()),
NextStatusCode = dataReader["NEXT_STATUS_CODE"].ToString(),
RqmtId = DBUtils.HandleInt32IfNull(dataReader["RQMT_ID"].ToString()),
//TemplateCategory = dataReader["TEMPLATE_CATEGORY"].ToString(),
//TemplateId = DBUtils.HandleInt32IfNull(dataReader["TEMPLATE_ID"].ToString()),
TemplateName = dataReader["TEMPLATE_NAME"].ToString(),
PreparerCanSendFlag = dataReader["PREPARER_CAN_SEND_FLAG"].ToString(),
//TemplateTypeInd = dataReader["TEMPLATE_TYPE_IND"].ToString(),
TradeId = DBUtils.HandleInt32IfNull(dataReader["TRADE_ID"].ToString()),
XmitAddr = dataReader["XMIT_ADDR"].ToString(),
XmitCmt = dataReader["XMIT_CMT"].ToString(),
XmitStatusInd = dataReader["XMIT_STATUS_IND"].ToString(),
XmitTimestampGmt = DBUtils.HandleDateTimeIfNull(dataReader["XMIT_TIMESTAMP_GMT"].ToString())
});
}
}
}
}
}
return(result);
}
public List <XmitResultDto> GetByAssociatedDocsId(Int32 pAssociatedDocsId)
{
var result = new List <XmitResultDto>();
string sql = "select xmit_result_id, xmit_request_id, associated_docs_id, trade_rqmt_confirm_id, " +
" sent_by_user, xmit_status_ind, xmit_method_ind, xmit_dest, xmit_cmt, xmit_timestamp " +
"from " + DBUtils.SCHEMA_NAME + "v_xmit_result " +
"where associated_docs_id = @associated_docs_id " +
"order by xmit_result_id desc ";
using (SqlConnection conn = new SqlConnection(sqlConnStr))
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.CommandType = System.Data.CommandType.Text;
cmd.Parameters.Add("@associated_docs_id", System.Data.SqlDbType.Int).Value = pAssociatedDocsId;
conn.Open();
using (SqlDataReader dataReader = cmd.ExecuteReader())
{
if (dataReader.HasRows)
{
while (dataReader.Read())
{
result.Add(new XmitResultDto
{
XmitResultId = DBUtils.HandleInt32IfNull(dataReader["xmit_result_id"].ToString()),
XmitRequestId = DBUtils.HandleInt32IfNull(dataReader["xmit_request_id"].ToString()),
AssociatedDocsId = DBUtils.HandleInt32IfNull(dataReader["associated_docs_id"].ToString()),
TradeRqmtConfirmId = DBUtils.HandleInt32IfNull(dataReader["trade_rqmt_confirm_id"].ToString()),
SentByUser = dataReader["sent_by_user"].ToString(),
XmitStatusInd = dataReader["xmit_status_ind"].ToString(),
XmitMethodInd = dataReader["xmit_method_ind"].ToString(),
XmitDest = dataReader["xmit_dest"].ToString(),
XmitCmt = dataReader["xmit_cmt"].ToString(),
XmitTimestamp = DBUtils.HandleDateTimeIfNull(dataReader["xmit_timestamp"].ToString())
});
}
}
}
}
}
return(result);
}
public List <InboundDocsView> GetAll()
{
var result = new List <InboundDocsView>();
string sql = "select * from " + DBUtils.SCHEMA_NAME + "v_inbound_docs " +
" where doc_status_code = 'OPEN' ";
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 InboundDocsView
{
Unresolvedcount = DBUtils.HandleInt32IfNull(dataReader["unresolvedcount"].ToString()),
Id = DBUtils.HandleInt32IfNull(dataReader["id"].ToString()),
MappedCptySn = dataReader["mapped_cpty_sn"].ToString(),
CallerRef = dataReader["caller_ref"].ToString(),
SentTo = dataReader["sent_to"].ToString(),
RcvdTs = DBUtils.HandleDateTimeIfNull(dataReader["rcvd_ts"].ToString()),
FileName = dataReader["file_name"].ToString(),
Sender = dataReader["sender"].ToString(),
Cmt = dataReader["cmt"].ToString(),
DocStatusCode = dataReader["doc_status_code"].ToString(),
HasAutoAsctedFlag = dataReader["has_auto_ascted_flag"].ToString(),
ProcFlag = dataReader["proc_flag"].ToString(),
Tradeids = dataReader["tradeids"].ToString()
});
}
}
}
}
}
return(result);
}
//Currently used only for testing.
public List <TradeRqmtDto> GetTradeRqmts(Int32 pTradeId)
{
var result = new List <TradeRqmtDto>();
string sql = "select id, trade_id, rqmt_trade_notify_id, rqmt, status, completed_dt, completed_timestamp_gmt, " +
" reference, cancel_trade_notify_id, cmt, second_check_flag " +
" from " + DBUtils.SCHEMA_NAME + "trade_rqmt where trade_id = @trade_id ";
using (SqlConnection conn = new SqlConnection(sqlConnStr))
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.CommandType = System.Data.CommandType.Text;
cmd.Parameters.Add("@trade_id", System.Data.SqlDbType.Int).Value = pTradeId;
conn.Open();
using (SqlDataReader dataReader = cmd.ExecuteReader())
{
if (dataReader.HasRows)
{
while (dataReader.Read())
{
result.Add(new TradeRqmtDto
{
Id = DBUtils.HandleInt32IfNull(dataReader["id"].ToString()),
TradeId = DBUtils.HandleInt32IfNull(dataReader["trade_id"].ToString()),
RqmtTradeNotifyId = DBUtils.HandleInt32IfNull(dataReader["rqmt_trade_notify_id"].ToString()),
RqmtCode = dataReader["rqmt"].ToString(),
StatusCode = dataReader["status"].ToString(),
CompletedDt = DBUtils.HandleDateTimeIfNull(dataReader["completed_dt"].ToString()),
CompletedTimestampGmt = DBUtils.HandleDateTimeIfNull(dataReader["completed_timestamp_gmt"].ToString()),
Reference = dataReader["reference"].ToString(),
CancelTradeNotifyId = DBUtils.HandleInt32IfNull(dataReader["cancel_trade_notify_id"].ToString()),
Cmt = dataReader["cmt"].ToString(),
SecondCheckFlag = dataReader["second_check_flag"].ToString()
});
}
}
}
}
}
return(result);
}
public List <TradeAuditDto> GetTradeAudit(Int32 pTradeId)
{
var result = new List <TradeAuditDto>();
string sql = "select trade_id, trade_rqmt_id, operation, rqmt, status, machine_name, " +
" userid, timestamp, completed_dt " +
" from " + DBUtils.SCHEMA_NAME + "v_trade_audit " +
" where trade_id = @trade_id " +
" order by timestamp ";
using (SqlConnection conn = new SqlConnection(sqlConnStr))
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.CommandType = System.Data.CommandType.Text;
cmd.Parameters.Add("@trade_id", System.Data.SqlDbType.Int).Value = pTradeId;
conn.Open();
using (SqlDataReader dataReader = cmd.ExecuteReader())
{
if (dataReader.HasRows)
{
while (dataReader.Read())
{
result.Add(new TradeAuditDto
{
TradeId = DBUtils.HandleInt32IfNull(dataReader["trade_id"].ToString()),
TradeRqmtId = DBUtils.HandleInt32IfNull(dataReader["trade_rqmt_id"].ToString()),
Operation = dataReader["operation"].ToString(),
Rqmt = dataReader["rqmt"].ToString(),
Status = dataReader["status"].ToString(),
Machine = dataReader["machine_name"].ToString(),
UserId = dataReader["userid"].ToString(),
Timestamp = DBUtils.HandleDateTimeIfNull(dataReader["timestamp"].ToString()),
CompletedDt = DBUtils.HandleDateTimeIfNull(dataReader["completed_dt"].ToString()),
});
}
}
}
}
}
return(result);
}
public List <FaxLogStatusDto> Get(Int32 pTradeRqmtConfirmId)
{
var result = new List <FaxLogStatusDto>();
string sql = "select id, trade_id, trade_rqmt_confirm_id, sender, crtd_ts_gmt, " +
" fax_telex_ind, fax_telex_number, fax_status, cmt " +
"from " + DBUtils.SCHEMA_NAME + "fax_log_status " +
"where trade_rqmt_confirm_id = @trade_rqmt_confirm_id " +
"order by id desc ";
using (SqlConnection conn = new SqlConnection(sqlConnStr))
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.CommandType = System.Data.CommandType.Text;
cmd.Parameters.Add("@trade_rqmt_confirm_id", System.Data.SqlDbType.Int).Value = pTradeRqmtConfirmId;
conn.Open();
using (SqlDataReader dataReader = cmd.ExecuteReader())
{
if (dataReader.HasRows)
{
while (dataReader.Read())
{
result.Add(new FaxLogStatusDto
{
Id = DBUtils.HandleInt32IfNull(dataReader["id"].ToString()),
TradeId = DBUtils.HandleInt32IfNull(dataReader["trade_id"].ToString()),
TradeRqmtConfirmId = DBUtils.HandleInt32IfNull(dataReader["trade_rqmt_confirm_id"].ToString()),
Sender = dataReader["sender"].ToString(),
CrtdTsGmt = DBUtils.HandleDateTimeIfNull(dataReader["crtd_ts_gmt"].ToString()),
FaxTelexInd = dataReader["fax_telex_ind"].ToString(),
FaxTelexNumber = dataReader["fax_telex_number"].ToString(),
FaxStatus = dataReader["fax_status"].ToString(),
Cmt = dataReader["cmt"].ToString()
});
}
}
}
}
}
return(result);
}
public List <InbAttribMapComboDto> GetPhrases(string pMappedValue)
{
var result = new List <InbAttribMapComboDto>();
string sql = "select mv.id as mapped_val_id, mv.descr, mp.phrase, mp.id as phrase_id " +
" from " + DBUtils.SCHEMA_NAME + "inb_attrib_map_val mv, " +
DBUtils.SCHEMA_NAME + "inb_attrib_map_phrase mp " +
" where mv.active_flag = 'Y' and " +
" mv.mapped_value = @mapped_value and " +
" mp.inb_attrib_map_val_id = mv.id and " +
" mp.active_flag = 'Y' " +
" order by mp.inb_attrib_map_val_id, mp.id asc";
using (SqlConnection conn = new SqlConnection(sqlConnStr))
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.CommandType = System.Data.CommandType.Text;
cmd.Parameters.Add("@mapped_value", System.Data.SqlDbType.VarChar).Value = pMappedValue;
conn.Open();
using (SqlDataReader dataReader = cmd.ExecuteReader())
{
if (dataReader.HasRows)
{
while (dataReader.Read())
{
result.Add(new InbAttribMapComboDto
{
MappedValId = DBUtils.HandleInt32IfNull(dataReader["mapped_val_id"].ToString()),
PhraseId = DBUtils.HandleInt32IfNull(dataReader["phrase_id"].ToString()),
Phrase = dataReader["phrase"].ToString(),
Descr = dataReader["descr"].ToString()
});
}
}
}
}
}
return(result);
}
public List <InbAttribMapValDto> GetMapValues(string pInbAttribCode)
{
var result = new List <InbAttribMapValDto>();
string sql = "select id, mapped_value, descr " +
" from " + DBUtils.SCHEMA_NAME + "inb_attrib_map_val " +
" where inb_attrib_code = @inb_attrib_code " +
" and active_flag ='Y'";
using (SqlConnection conn = new SqlConnection(sqlConnStr))
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.CommandType = System.Data.CommandType.Text;
cmd.Parameters.Add("@inb_attrib_code", System.Data.SqlDbType.VarChar).Value = pInbAttribCode;
conn.Open();
using (SqlDataReader dataReader = cmd.ExecuteReader())
{
if (dataReader.HasRows)
{
while (dataReader.Read())
{
result.Add(new InbAttribMapValDto
{
//Id = Convert.ToInt32(dataReader.GetInt32(dataReader.GetOrdinal("id"))),
//MappedValue = dataReader.GetString(dataReader.GetOrdinal("mapped_value")),
//Descr = dataReader.GetString(dataReader.GetOrdinal("descr"))
Id = DBUtils.HandleInt32IfNull(dataReader["id"].ToString()),
MappedValue = dataReader["mapped_value"].ToString(),
Descr = dataReader["mapped_value"].ToString()
});
}
}
}
}
}
return(result);
}
public List <TradeContractInfoDto> GetContractList(string pTrdSysCode, string pTrdSysTicket)
{
var result = new List <TradeContractInfoDto>();
string sql = "select * from " + DBUtils.SCHEMA_NAME + "v_trade_contract_list " +
" where trd_sys_code = @trd_sys_code " +
" and trd_sys_ticket = @trd_sys_ticket " +
" and active_flag = 'Y' ";
using (SqlConnection conn = new SqlConnection(sqlConnStr))
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.CommandType = System.Data.CommandType.Text;
cmd.Parameters.Add("@trd_sys_code", System.Data.SqlDbType.VarChar).Value = pTrdSysCode;
cmd.Parameters.Add("@trd_sys_ticket", System.Data.SqlDbType.VarChar).Value = pTrdSysTicket;
conn.Open();
using (SqlDataReader dataReader = cmd.ExecuteReader())
{
if (dataReader.HasRows)
{
while (dataReader.Read())
{
result.Add(new TradeContractInfoDto
{
TrdSysCode = dataReader["trd_sys_code"].ToString(),
TrdSysTicket = dataReader["trd_sys_ticket"].ToString(),
TradeRqmtConfirmId = DBUtils.HandleInt32IfNull(dataReader["trade_rqmt_confirm_id"].ToString()),
TradeDt = DBUtils.HandleDateTimeIfNull(dataReader["trade_dt"].ToString()),
TemplateName = dataReader["template_name"].ToString()
});
}
}
}
}
}
return(result);
}
public List <RqmtStatusView> GetAll()
{
var result = new List <RqmtStatusView>();
string sql = "select rqmt_code, display_text, initial_status, status_code, ord, " +
"terminal_flag, problem_flag, color_code, descr " +
"from " + DBUtils.SCHEMA_NAME + "v_rqmt_status ";
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 RqmtStatusView
{
RqmtCode = dataReader["rqmt_code"].ToString(),
DisplayText = dataReader["display_text"].ToString(),
InitialStatus = dataReader["initial_status"].ToString(),
StatusCode = dataReader["status_code"].ToString(),
Ord = DBUtils.HandleInt32IfNull(dataReader["ord"].ToString()),
TerminalFlag = dataReader["terminal_flag"].ToString(),
ProblemFlag = dataReader["problem_flag"].ToString(),
ColorCode = dataReader["color_code"].ToString(),
Descr = dataReader["descr"].ToString()
});
}
}
}
}
}
return(result);
}
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 List <TradeDataChgDto> GetTradeDataChg(Int32 pTradeId)
{
var result = new List <TradeDataChgDto>();
string sql = "select jn_datetime, trade_id, buy_sell_ind, trade_dt, booking_co_sn, cpty_sn, " +
"broker_sn, trade_desc, price_desc, trade_stat_code, location_sn, qty_desc, " +
"qty_tot, trade_type_code, cdty_code, sttl_type, book, transport_desc, cpty_legal_name, " +
"broker_legal_name, broker_price, trader, cdty_grp_code, start_dt, end_dt, xref, " +
"ref_sn, inception_dt, optn_put_call_ind, optn_prem_price, optn_strike_price, " +
"profit_center, permission_key " +
" from " + DBUtils.SCHEMA_NAME + "trade_data_jn " +
" where trade_id = @trade_id " +
" order by jn_datetime ";
using (SqlConnection conn = new SqlConnection(sqlConnStr))
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.CommandType = System.Data.CommandType.Text;
cmd.Parameters.Add("@trade_id", System.Data.SqlDbType.Int).Value = pTradeId;
conn.Open();
using (SqlDataReader dataReader = cmd.ExecuteReader())
{
if (dataReader.HasRows)
{
while (dataReader.Read())
{
result.Add(new TradeDataChgDto
{
Book = dataReader["book"].ToString(),
BookingCoSn = dataReader["booking_co_sn"].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(),
CptyLegalName = dataReader["cpty_legal_name"].ToString(),
CptySn = dataReader["cpty_sn"].ToString(),
EndDt = DBUtils.HandleDateTimeIfNull(dataReader["end_dt"].ToString()),
InceptionDt = DBUtils.HandleDateTimeIfNull(dataReader["inception_dt"].ToString()),
JnDatetime = DBUtils.HandleDateTimeIfNull(dataReader["jn_datetime"].ToString()),
LocationSn = dataReader["location_sn"].ToString(),
OptnPremPrice = dataReader["optn_prem_price"].ToString(),
OptnPutCallInd = dataReader["optn_put_call_ind"].ToString(),
OptnStrikePrice = dataReader["optn_strike_price"].ToString(),
PermissionKey = dataReader["permission_key"].ToString(),
PriceDesc = dataReader["price_desc"].ToString(),
ProfitCenter = dataReader["profit_center"].ToString(),
QtyDesc = dataReader["qty_desc"].ToString(),
QtyTot = DBUtils.HandleFloatIfNull(dataReader["qty_tot"].ToString()),
RefSn = dataReader["ref_sn"].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()),
TradeStatCode = dataReader["trade_stat_code"].ToString(),
TradeTypeCode = dataReader["trade_type_code"].ToString(),
Trader = dataReader["trader"].ToString(),
TransportDesc = dataReader["transport_desc"].ToString(),
Xref = dataReader["xref"].ToString()
});
}
}
}
}
}
return(result);
}
