private static ImagesServerInformation PerformRowInsert(ImagesDto pData, SqlConnection conn, string insertTSql,
int newId)
{
conn.Open();
using (var cmd = new SqlCommand(insertTSql, conn))
{
cmd.Parameters.Add("@ID", SqlDbType.Int).Value = newId;
cmd.Parameters.Add("@DOCS_ID", SqlDbType.VarChar).Value = pData.DocsId;
cmd.Parameters.Add("@ORIG_IMAGE_FILE_EXT", SqlDbType.VarChar).Value =
DBUtils.ValueStringOrDBNull(pData.OriginalImageFileExt.ToUpper().Replace(".", ""));
cmd.Parameters.Add("@MARKUP_IMAGE_FILE_EXT", SqlDbType.VarChar).Value =
DBUtils.ValueStringOrDBNull(pData.MarkupImageFileExt.ToUpper().Replace(".", ""));
return(ReadImagesServerInformation(cmd));
}
}
public void Update(ImagesDto pData)
{
var updateSql = String.Format(
@"UPDATE {0}{1}
set {2} = @DocsId,
ORIG_IMAGE_FILE_EXT = @OrigImageFileExt,
MARKUP_IMAGE_FILE_EXT = @MarkupImageFileExt
where ID = @Id;
SELECT
ORIG_IMAGE_BLOB.PathName(),
MARKUP_IMAGE_BLOB.PathName(),
GET_FILESTREAM_TRANSACTION_CONTEXT()
FROM {0}{1} WHERE ID = @Id" ,
DBUtils.SCHEMA_NAME,
(pData.Type == ImagesDtoType.Inbound) ? "INBOUND_DOCS_BLOB" : "ASSOCIATED_DOCS_BLOB",
(pData.Type == ImagesDtoType.Inbound) ? "INBOUND_DOCS_ID" : "ASSOCIATED_DOCS_ID");
using (var ts = new TransactionScope())
{
using (var conn = new SqlConnection(sqlConnStr))
{
conn.Open();
ImagesServerInformation imagesServerInfo;
using (var cmd = new SqlCommand(updateSql, conn))
{
cmd.Parameters.AddWithValue("@Id", pData.ImageId);
cmd.Parameters.AddWithValue("@DocsId", pData.DocsId);
cmd.Parameters.AddWithValue("@OrigImageFileExt",
DBUtils.ValueStringOrDBNull(pData.OriginalImageFileExt.ToUpper().Replace(".", "")));
cmd.Parameters.AddWithValue("@MarkupImageFileExt",
DBUtils.ValueStringOrDBNull(pData.MarkupImageFileExt.ToUpper().Replace(".", "")));
imagesServerInfo = ReadImagesServerInformation(cmd);
}
SaveDocImage(pData.MarkupImage, imagesServerInfo.MarkupServerPath,
imagesServerInfo.ServerTransaction);
SaveDocImage(pData.OriginalImage, imagesServerInfo.OriginalServerPath,
imagesServerInfo.ServerTransaction);
}
ts.Complete();
}
}
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 UpdateDetermineActions(Int32 pTradeId, string pOptsDetActFlag)
{
string updateSql = DBUtils.SCHEMA_NAME + "pkg_trade_summary$p_update_determine_actions";
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_ops_det_act_flag", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pOptsDetActFlag);
conn.Open();
rowsUpdated = cmd.ExecuteNonQuery();
}
}
return(rowsUpdated);
}
public Int32 UpdateFinalApproval(Int32 pTradeId, string pFinalApprovalStatus)
{
string updateSql = DBUtils.SCHEMA_NAME + "pkg_trade_summary$p_update_final_approval";
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_fnlapp_status", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pFinalApprovalStatus);
conn.Open();
rowsUpdated = cmd.ExecuteNonQuery();
}
}
return(rowsUpdated);
}
public Int32 Update(UserFiltersOpsmgrDto pData)
{
Int32 rowsUpdated = 0;
string sql = "update " + DBUtils.SCHEMA_NAME + "user_filters_opsmgr " +
"set user_id = @user_id, descr = @descr, filter_expr = @filter_expr " +
" 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 = pData.Id;
cmd.Parameters.Add("@user_id", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.UserId);
cmd.Parameters.Add("@descr", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.Descr);
cmd.Parameters.Add("@filter_expr", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.FilterExpr);
conn.Open();
rowsUpdated = cmd.ExecuteNonQuery();
}
}
return(rowsUpdated);
}
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 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 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);
}
public Int32 Insert(InbAttribMapPhraseDto pData)
{
Int32 newId = DBUtils.GetNextSequence(sqlConnStr, SEQ_NAME);
string sql = "Insert into " + DBUtils.SCHEMA_NAME + "inb_attrib_map_phrase " +
" (id, inb_attrib_map_val_id, phrase, active_flag) " +
" Values " +
" (@id, @inb_attrib_map_val_id, @phrase, @active_flag) ";
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("@inb_attrib_map_val_id", System.Data.SqlDbType.Int).Value = pData.InbAttribMapValId;
cmd.Parameters.Add("@phrase", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.Phrase);
cmd.Parameters.Add("@active_flag", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.ActiveFlag);
conn.Open();
cmd.ExecuteNonQuery();
}
}
return(newId);
}
public Int32 Update(InbAttribMapPhraseDto pData)
{
Int32 rowsUpdated = 0;
string sql = "update " + DBUtils.SCHEMA_NAME + "inb_attrib_map_phrase " +
"set inb_attrib_map_val_id = @inb_attrib_map_val_id, phrase = @phrase, active_flag = @active_flag " +
" 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 = pData.Id;
cmd.Parameters.Add("@inb_attrib_map_val_id", System.Data.SqlDbType.Int).Value = pData.InbAttribMapValId;
cmd.Parameters.Add("@phrase", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.Phrase);
cmd.Parameters.Add("@active_flag", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.ActiveFlag);
conn.Open();
SqlTransaction trans = conn.BeginTransaction();
try
{
cmd.Transaction = trans;
rowsUpdated = cmd.ExecuteNonQuery();
trans.Commit();
}
catch (Exception e)
{
trans.Rollback();
//throw;
}
}
}
return(rowsUpdated);
}
public Int32 Update(TradeRqmtConfirm pData)
{
string updateSql = DBUtils.SCHEMA_NAME + "PKG_RQMT_CONFIRM$p_update_rqmt_confirm";
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_rqmt_id", System.Data.SqlDbType.Int).Value = pData.RqmtId;
cmd.Parameters.Add("@p_trade_id", System.Data.SqlDbType.Int).Value = pData.TradeId;
cmd.Parameters.Add("@p_template_name", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.TemplateName);
cmd.Parameters.Add("@p_confirm_cmt", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.ConfirmCmt);
cmd.Parameters.Add("@p_fax_telex_ind", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.FaxTelexInd);
cmd.Parameters.Add("@p_fax_telex_number", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.FaxTelexNumber);
cmd.Parameters.Add("@p_confirm_label", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.ConfirmLabel);
cmd.Parameters.Add("@p_next_status_code", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.NextStatusCode);
cmd.Parameters.Add("@p_active_flag", System.Data.SqlDbType.VarChar).Value = pData.ActiveFlag;
conn.Open();
rowsUpdated = cmd.ExecuteNonQuery();
}
}
return(rowsUpdated);
}
public Int32 Insert(TradeRqmtConfirm pData)
{
string updateSql = DBUtils.SCHEMA_NAME + "PKG_RQMT_CONFIRM$p_insert_rqmt_confirm";
Int32 rowsInserted = 0;
Int32 seqNo = 0;
string seqName = "SEQ_TRADE_RQMT_CONFIRM";
seqNo = DBUtils.GetNextSequence(sqlConnStr, seqName);
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 = seqNo;
cmd.Parameters.Add("@p_rqmt_id", System.Data.SqlDbType.Int).Value = pData.RqmtId;
cmd.Parameters.Add("@p_trade_id", System.Data.SqlDbType.Int).Value = pData.TradeId;
cmd.Parameters.Add("@p_template_name", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.TemplateName);
cmd.Parameters.Add("@p_confirm_cmt", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.ConfirmCmt);
cmd.Parameters.Add("@p_fax_telex_ind", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.FaxTelexInd);
cmd.Parameters.Add("@p_fax_telex_number", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.FaxTelexNumber);
cmd.Parameters.Add("@p_confirm_label", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.ConfirmLabel);
cmd.Parameters.Add("@p_next_status_code", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.NextStatusCode);
conn.Open();
rowsInserted = cmd.ExecuteNonQuery();
}
}
if (rowsInserted == 0)
{
return(rowsInserted);
}
else
{
return(seqNo);
}
}
public Int32 Update(InbAttribMapValDto pData)
{
Int32 rowsUpdated = 0;
string sql = "update " + DBUtils.SCHEMA_NAME + "inb_attrib_map_val " +
"set inb_attrib_code = @inb_attrib_code, mapped_value = @mapped_value, " +
" descr = @descr, active_flag = @active_flag " +
" 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 = pData.Id;
cmd.Parameters.Add("@inb_attrib_code", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.InbAttribCode);
cmd.Parameters.Add("@mapped_value", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.MappedValue);
cmd.Parameters.Add("@descr", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.Descr);
cmd.Parameters.Add("@active_flag", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.ActiveFlag);
conn.Open();
rowsUpdated = cmd.ExecuteNonQuery();
}
}
return(rowsUpdated);
}
public Int32 UpdateFlag(InboundDocUserFlagDto pUserFlagData)
{
string updateSql = DBUtils.SCHEMA_NAME + "PKG_INBOUND_EXT$p_update_user_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_inbound_doc_id", System.Data.SqlDbType.Int).Value = pUserFlagData.InboundDocId;
cmd.Parameters.Add("@p_inbound_user", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pUserFlagData.InboundUser);
cmd.Parameters.Add("@p_flag_type", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pUserFlagData.FlagType);
cmd.Parameters.Add("@p_comment", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pUserFlagData.Comments);
cmd.Parameters.Add("@p_update_delete_ind", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pUserFlagData.UpdateDeleteInd);
conn.Open();
rowsUpdated = cmd.ExecuteNonQuery();
}
}
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 Int32 MapCallerRef(string pCallerRef, string pCptySn, string pRefType)
{
string updateSql = DBUtils.SCHEMA_NAME + "PKG_RQMT_CONFIRM$p_map_caller_ref";
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_caller_ref", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pCallerRef);
cmd.Parameters.Add("@p_cpty_sn", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pCptySn);
cmd.Parameters.Add("@p_ref_type", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pRefType);
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 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(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 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 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 void Insert(FaxLogSentDto pData)
{
//int newId = DBUtils.GetNextSequence(sqlConnStr, SEQ_NAME);
string insertSql = DBUtils.SCHEMA_NAME + "PKG_RQMT_CONFIRM$p_insert_fax_log_sent";
using (SqlConnection conn = new SqlConnection(sqlConnStr))
{
using (SqlCommand cmd = new SqlCommand(insertSql, conn))
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add("@p_trade_id", System.Data.SqlDbType.Int).Value = pData.TradeId;
cmd.Parameters.Add("@p_doc_type", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.DocType);
cmd.Parameters.Add("@p_sender", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.Sender);
cmd.Parameters.Add("@p_telex_code", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.FaxTelexCode);
cmd.Parameters.Add("@p_telex_number", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.FaxTelexNumber);
cmd.Parameters.Add("@p_doc_ref_code", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.DocRefCode);
conn.Open();
cmd.ExecuteNonQuery();
}
}
//return newId;
}
public Int32 Insert(UserFiltersOpsmgrDto pData)
{
Int32 newId = DBUtils.GetNextSequence(sqlConnStr, SEQ_NAME);
string sql = "Insert into " + DBUtils.SCHEMA_NAME + "user_filters_opsmgr " +
" (id, user_id, descr, filter_expr) " +
" Values " +
" (@id, @user_id, @descr, @filter_expr) ";
using (SqlConnection conn = new SqlConnection(sqlConnStr))
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.Parameters.Add("@id", System.Data.SqlDbType.Int).Value = newId;
cmd.Parameters.Add("@user_id", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.UserId);
cmd.Parameters.Add("@descr", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.Descr);
cmd.Parameters.Add("@filter_expr", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.FilterExpr);
conn.Open();
cmd.ExecuteNonQuery();
}
}
return(newId);
}
