public InsiderTradingDAL.PreclearanceRequestDTO GetLastPeriodEndSubmissonFlag(string i_sConnectionString, int i_nUserInfoID, out int out_nIsPreviousPeriodEndSubmission, out string out_sSubsequentPeriodEndOrPreciousPeriodEndResource, out string out_sSubsequentPeriodEndResourceOwnSecurity)
{
PreclearanceRequestDTO res = null;
//bool bReturn = false;
string sErrCode = string.Empty;
#region Paramters
int out_nReturnValue;
int out_nSQLErrCode;
string out_sSQLErrMessage;
PetaPoco.Database db = null;
#endregion Paramters
try
{
var nReturnValue = new SqlParameter("@out_nReturnValue", System.Data.SqlDbType.Int);
nReturnValue.Direction = System.Data.ParameterDirection.Output;
var nSQLErrCode = new SqlParameter("@out_nSQLErrCode", System.Data.SqlDbType.Int);
nSQLErrCode.Direction = System.Data.ParameterDirection.Output;
nSQLErrCode.Value = 0;
var sSQLErrMessage = new SqlParameter("@out_sSQLErrMessage", System.Data.SqlDbType.VarChar);
sSQLErrMessage.Direction = System.Data.ParameterDirection.Output;
sSQLErrMessage.Value = "";
sSQLErrMessage.Size = 500;
var nIsPreviousPeriodEndSubmission = new SqlParameter("@out_nIsPreviousPeriodEndSubmission", System.Data.SqlDbType.Int);
nIsPreviousPeriodEndSubmission.Direction = System.Data.ParameterDirection.Output;
var sSubsequentPeriodEndOrPreciousPeriodEndResource = new SqlParameter("@out_sSubsequentPeriodEndOrPreciousPeriodEndResource", System.Data.SqlDbType.VarChar);
sSubsequentPeriodEndOrPreciousPeriodEndResource.Direction = System.Data.ParameterDirection.Output;
sSubsequentPeriodEndOrPreciousPeriodEndResource.Value = "";
sSubsequentPeriodEndOrPreciousPeriodEndResource.Size = 500;
var sSubsequentPeriodEndResourceOwnSecurity = new SqlParameter("@out_sSubsequentPeriodEndResourceOwnSecurity", System.Data.SqlDbType.VarChar);
sSubsequentPeriodEndResourceOwnSecurity.Direction = System.Data.ParameterDirection.Output;
sSubsequentPeriodEndResourceOwnSecurity.Value = "";
sSubsequentPeriodEndResourceOwnSecurity.Size = 500;
using (db = new PetaPoco.Database(i_sConnectionString, "System.Data.SqlClient")
{
EnableAutoSelect = false
})
{
db.CommandTimeout = 5000;
using (var scope = db.GetTransaction())
{
res = db.Query <PreclearanceRequestDTO>("exec st_tra_CheckPreviousPeriodEndSubmission @inp_iUserInfoID,@out_nIsPreviousPeriodEndSubmission OUTPUT,@out_sSubsequentPeriodEndOrPreciousPeriodEndResource OUTPUT,@out_sSubsequentPeriodEndResourceOwnSecurity OUTPUT,@out_nReturnValue OUTPUT,@out_nSQLErrCode OUTPUT,@out_sSQLErrMessage OUTPUT",
new
{
@inp_iUserInfoID = i_nUserInfoID,
@out_nIsPreviousPeriodEndSubmission = nIsPreviousPeriodEndSubmission,
@out_sSubsequentPeriodEndOrPreciousPeriodEndResource = sSubsequentPeriodEndOrPreciousPeriodEndResource,
@out_sSubsequentPeriodEndResourceOwnSecurity = sSubsequentPeriodEndResourceOwnSecurity,
out_nReturnValue = nReturnValue,
out_nSQLErrCode = nSQLErrCode,
out_sSQLErrMessage = sSQLErrMessage
}).FirstOrDefault <PreclearanceRequestDTO>();
if (Convert.ToInt32(nReturnValue.Value) != 0)
{
Exception e = new Exception();
out_nReturnValue = Convert.ToInt32(nReturnValue.Value);
string sReturnValue = sLookUpPrefix + out_nReturnValue;
e.Data[0] = sReturnValue;
if (nSQLErrCode.Value != System.DBNull.Value)
{
out_nSQLErrCode = Convert.ToInt32(nSQLErrCode.Value);
e.Data[1] = out_nSQLErrCode;
}
if (sSQLErrMessage.Value != System.DBNull.Value)
{
out_sSQLErrMessage = Convert.ToString(sSQLErrMessage.Value);
e.Data[2] = out_sSQLErrMessage;
}
//bReturn = false;
Exception ex = new Exception(db.LastSQL.ToString(), e);
throw ex;
}
else
{
out_nIsPreviousPeriodEndSubmission = Convert.ToInt32(nIsPreviousPeriodEndSubmission.Value);
out_sSubsequentPeriodEndOrPreciousPeriodEndResource = Convert.ToString(sSubsequentPeriodEndOrPreciousPeriodEndResource.Value);
out_sSubsequentPeriodEndResourceOwnSecurity = Convert.ToString(sSubsequentPeriodEndResourceOwnSecurity.Value);
scope.Complete();
}
}
}
}
catch (Exception exp)
{
//bReturn = false;
throw exp;
}
return(res);
}
public InsiderTradingDAL.PreclearanceRequestDTO GetDetails(string i_sConnectionString, long i_nPreclearanceRequestId)
{
PreclearanceRequestDTO res = null;
string sErrCode = string.Empty;
#region Paramters
int out_nReturnValue;
int out_nSQLErrCode;
string out_sSQLErrMessage;
PetaPoco.Database db = null;
#endregion Paramters
try
{
var nReturnValue = new SqlParameter("@out_nReturnValue", System.Data.SqlDbType.Int);
nReturnValue.Direction = System.Data.ParameterDirection.Output;
// nReturnValue.Value = 0;
var nSQLErrCode = new SqlParameter("@out_nSQLErrCode", System.Data.SqlDbType.Int);
nSQLErrCode.Direction = System.Data.ParameterDirection.Output;
nSQLErrCode.Value = 0;
var sSQLErrMessage = new SqlParameter("@out_sSQLErrMessage", System.Data.SqlDbType.VarChar);
sSQLErrMessage.Direction = System.Data.ParameterDirection.Output;
sSQLErrMessage.Value = "";
sSQLErrMessage.Size = 500;
// db = new PetaPoco.Database(i_sConnectionString, "System.Data.SqlClient");
using (db = new PetaPoco.Database(i_sConnectionString, "System.Data.SqlClient")
{
EnableAutoSelect = false
})
{
using (var scope = db.GetTransaction())
{
res = db.Query <PreclearanceRequestDTO>("exec st_tra_PreclearanceRequestDetails @inp_sPreclearanceRequestId,@out_nReturnValue OUTPUT,@out_nSQLErrCode OUTPUT,@out_sSQLErrMessage OUTPUT",
new
{
inp_sPreclearanceRequestId = i_nPreclearanceRequestId,
out_nReturnValue = nReturnValue,
out_nSQLErrCode = nSQLErrCode,
out_sSQLErrMessage = sSQLErrMessage
}).FirstOrDefault <PreclearanceRequestDTO>();
if (Convert.ToInt32(nReturnValue.Value) != 0)
{
Exception e = new Exception();
out_nReturnValue = Convert.ToInt32(nReturnValue.Value);
string sReturnValue = sLookUpPrefix + out_nReturnValue;
e.Data[0] = sReturnValue;
if (nSQLErrCode.Value != System.DBNull.Value)
{
out_nSQLErrCode = Convert.ToInt32(nSQLErrCode.Value);
e.Data[1] = out_nSQLErrCode;
}
if (sSQLErrMessage.Value != System.DBNull.Value)
{
out_sSQLErrMessage = Convert.ToString(sSQLErrMessage.Value);
e.Data[2] = out_sSQLErrMessage;
}
Exception ex = new Exception(db.LastSQL.ToString(), e);
throw ex;
}
else
{
scope.Complete();
}
}
}
}
catch (Exception exp)
{
throw exp;
}
return(res);
}
public bool Delete(string i_sConnectionString, int i_nPreclearanceRequestId, int inp_nUserId)
{
PreclearanceRequestDTO res = null;
bool bReturn = false;
#region Paramters
int out_nReturnValue;
int out_nSQLErrCode;
string out_sSQLErrMessage;
PetaPoco.Database db = null;
#endregion Paramters
try
{
var nReturnValue = new SqlParameter("@out_nReturnValue", System.Data.SqlDbType.Int);
nReturnValue.Direction = System.Data.ParameterDirection.Output;
// nReturnValue.Value = 0;
var nSQLErrCode = new SqlParameter("@out_nSQLErrCode", System.Data.SqlDbType.Int);
nSQLErrCode.Direction = System.Data.ParameterDirection.Output;
nSQLErrCode.Value = 0;
var sSQLErrMessage = new SqlParameter("@out_sSQLErrMessage", System.Data.SqlDbType.VarChar);
sSQLErrMessage.Direction = System.Data.ParameterDirection.Output;
sSQLErrMessage.Value = "";
sSQLErrMessage.Size = 500;
using (db = new PetaPoco.Database(i_sConnectionString, "System.Data.SqlClient")
{
EnableAutoSelect = false
})
{
using (var scope = db.GetTransaction())
{
res = db.Query <PreclearanceRequestDTO>("exec st_rul_TradingPolicyDelete @inp_iTradingPolicyId, @inp_nUserId,@out_nReturnValue OUTPUT,@out_nSQLErrCode OUTPUT,@out_sSQLErrMessage OUTPUT",
new
{
inp_iTradingPolicyId = i_nPreclearanceRequestId,
inp_nUserId,
out_nReturnValue = nReturnValue,
out_nSQLErrCode = nSQLErrCode,
out_sSQLErrMessage = sSQLErrMessage
}).SingleOrDefault <PreclearanceRequestDTO>();
if (Convert.ToInt32(nReturnValue.Value) != 0)
{
Exception e = new Exception();
out_nReturnValue = Convert.ToInt32(nReturnValue.Value);
string sReturnValue = sLookUpPrefix + out_nReturnValue;
e.Data[0] = sReturnValue;
if (nSQLErrCode.Value != System.DBNull.Value)
{
out_nSQLErrCode = Convert.ToInt32(nSQLErrCode.Value);
e.Data[1] = out_nSQLErrCode;
}
if (sSQLErrMessage.Value != System.DBNull.Value)
{
out_sSQLErrMessage = Convert.ToString(sSQLErrMessage.Value);
e.Data[2] = out_sSQLErrMessage;
}
bReturn = false;
Exception ex = new Exception(db.LastSQL.ToString(), e);
throw ex;
}
else
{
scope.Complete();
bReturn = true;
}
}
}
}
catch (Exception exp)
{
bReturn = false;
throw exp;
}
return(bReturn);
}
public PreclearanceRequestDTO Save(string i_sConnectionString, PreclearanceRequestDTO m_objPreclearanceRequestDTO, out string out_sContraTradeTillDate, out string out_sPeriodEnddate, out string out_sApproveddate, out string out_sPreValiditydate, out string out_sProhibitOnPer, out string out_sProhibitOnQuantity)
{
#region Paramters
//bool bReturn = false;
int out_nReturnValue;
int out_nSQLErrCode;
string out_sSQLErrMessage;
out_sPeriodEnddate = "";
PreclearanceRequestDTO res = null;
PetaPoco.Database db = null;
out_sContraTradeTillDate = "";
out_sApproveddate = "";
out_sPreValiditydate = "";
out_sProhibitOnPer = "";
out_sProhibitOnQuantity = "";
#endregion Paramters
try
{
#region Output Param
var nReturnValue = new SqlParameter("@out_nReturnValue", System.Data.SqlDbType.Int);
nReturnValue.Direction = System.Data.ParameterDirection.Output;
nReturnValue.Value = 0;
var nSQLErrCode = new SqlParameter("@out_nSQLErrCode", System.Data.SqlDbType.Int);
nSQLErrCode.Direction = System.Data.ParameterDirection.Output;
nSQLErrCode.Value = 0;
var sSQLErrMessage = new SqlParameter("@out_sSQLErrMessage", System.Data.SqlDbType.VarChar);
sSQLErrMessage.Direction = System.Data.ParameterDirection.Output;
sSQLErrMessage.Value = "";
sSQLErrMessage.Size = 500;
var sContraTradeTillDate = new SqlParameter("@out_sContraTradeTillDate", System.Data.SqlDbType.VarChar);
sContraTradeTillDate.Direction = System.Data.ParameterDirection.Output;
sContraTradeTillDate.Value = "";
sContraTradeTillDate.Size = 500;
var sPeriodEnddate = new SqlParameter("@out_sPeriodEnddate", System.Data.SqlDbType.VarChar);
sPeriodEnddate.Direction = System.Data.ParameterDirection.Output;
sPeriodEnddate.Value = "";
sPeriodEnddate.Size = 500;
var sApproveddate = new SqlParameter("@out_sApproveddate", System.Data.SqlDbType.VarChar);
sApproveddate.Direction = System.Data.ParameterDirection.Output;
sApproveddate.Value = "";
sApproveddate.Size = 500;
var sPreValiditydate = new SqlParameter("@out_sPreValiditydate", System.Data.SqlDbType.VarChar);
sPreValiditydate.Direction = System.Data.ParameterDirection.Output;
sPreValiditydate.Value = "";
sPreValiditydate.Size = 500;
var sProhibitOnPer = new SqlParameter("@out_sProhibitOnPer", System.Data.SqlDbType.VarChar);
sProhibitOnPer.Direction = System.Data.ParameterDirection.Output;
sProhibitOnPer.Value = "";
sProhibitOnPer.Size = 500;
var sProhibitOnQuantity = new SqlParameter("@out_sProhibitOnQuantity", System.Data.SqlDbType.VarChar);
sProhibitOnQuantity.Direction = System.Data.ParameterDirection.Output;
sProhibitOnQuantity.Value = "";
sProhibitOnQuantity.Size = 500;
#endregion Output Param
using (db = new PetaPoco.Database(i_sConnectionString, "System.Data.SqlClient")
{
EnableAutoSelect = false
})
{
using (var scope = db.GetTransaction())
{
res = db.Query <PreclearanceRequestDTO>("exec st_tra_PreclearanceRequestSave @inp_nPreclearanceRequestId,@inp_iPreclearanceRequestForCodeId,@inp_iUserInfoId,@inp_iUserInfoIdRelative,@inp_iTransactionTypeCodeId,"
+ "@inp_iSecurityTypeCodeId,@inp_dSecuritiesToBeTradedQty,@inp_iPreclearanceStatusCodeId,@inp_iCompanyId,"
+ "@inp_dProposedTradeRateRangeFrom,@inp_dProposedTradeRateRangeTo,@inp_iDMATDetailsID,@inp_iReasonForNotTradingCodeId,"
+ "@inp_sReasonForNotTradingText,@inp_nPreclearanceNotTakenFlag,@inp_sReasonForRejection,@inp_dSecuritiesToBeTradedValue,@inp_nUserId,@inp_bESOPExcerciseOptionQtyFlag,@inp_bOtherESOPExcerciseOptionQtyFlag,@inp_iModeOfAcquisitionCodeId,"
+ "@inp_sReasonForApproval,@inp_iReasonForApprovalCodeId,@inp_PreclearanceValidityDateOld,@inp_PreclearanceValidityDateUpdatedByCO,"
+ "@out_sContraTradeTillDate OUTPUT,@out_nReturnValue OUTPUT,@out_nSQLErrCode OUTPUT,@out_sSQLErrMessage OUTPUT,@out_sPeriodEnddate OUTPUT,@out_sApproveddate OUTPUT,@out_sPreValiditydate OUTPUT,@out_sProhibitOnPer OUTPUT,@out_sProhibitOnQuantity OUTPUT",
new
{
inp_nPreclearanceRequestId = m_objPreclearanceRequestDTO.PreclearanceRequestId,
inp_iPreclearanceRequestForCodeId = m_objPreclearanceRequestDTO.PreclearanceRequestForCodeId,
inp_iUserInfoId = m_objPreclearanceRequestDTO.UserInfoId,
inp_iUserInfoIdRelative = m_objPreclearanceRequestDTO.UserInfoIdRelative,
inp_iTransactionTypeCodeId = m_objPreclearanceRequestDTO.TransactionTypeCodeId,
inp_iSecurityTypeCodeId = m_objPreclearanceRequestDTO.SecurityTypeCodeId,
inp_dSecuritiesToBeTradedQty = m_objPreclearanceRequestDTO.SecuritiesToBeTradedQty,
inp_iPreclearanceStatusCodeId = m_objPreclearanceRequestDTO.PreclearanceStatusCodeId,
inp_iCompanyId = m_objPreclearanceRequestDTO.CompanyId,
inp_dProposedTradeRateRangeFrom = m_objPreclearanceRequestDTO.ProposedTradeRateRangeFrom,
inp_dProposedTradeRateRangeTo = m_objPreclearanceRequestDTO.ProposedTradeRateRangeTo,
inp_iDMATDetailsID = m_objPreclearanceRequestDTO.DMATDetailsID,
inp_iReasonForNotTradingCodeId = m_objPreclearanceRequestDTO.ReasonForNotTradingCodeId,
inp_sReasonForNotTradingText = m_objPreclearanceRequestDTO.ReasonForNotTradingText,
inp_nPreclearanceNotTakenFlag = m_objPreclearanceRequestDTO.PreclearanceNotTakenFlag,
inp_sReasonForRejection = m_objPreclearanceRequestDTO.ReasonForRejection,
inp_dSecuritiesToBeTradedValue = m_objPreclearanceRequestDTO.SecuritiesToBeTradedValue,
inp_nUserId = m_objPreclearanceRequestDTO.LoggedInUserId,
inp_bESOPExcerciseOptionQtyFlag = m_objPreclearanceRequestDTO.ESOPExcerciseOptionQtyFlag,
inp_bOtherESOPExcerciseOptionQtyFlag = m_objPreclearanceRequestDTO.OtherESOPExcerciseOptionQtyFlag,
inp_iModeOfAcquisitionCodeId = m_objPreclearanceRequestDTO.ModeOfAcquisitionCodeId,
inp_sReasonForApproval = m_objPreclearanceRequestDTO.ReasonForApproval,
inp_iReasonForApprovalCodeId = m_objPreclearanceRequestDTO.ReasonForApprovalCodeId,
inp_PreclearanceValidityDateOld = m_objPreclearanceRequestDTO.PreclearanceValidityDate,
inp_PreclearanceValidityDateUpdatedByCO = m_objPreclearanceRequestDTO.PreclearanceValidityDateChanged,
out_sContraTradeTillDate = sContraTradeTillDate,
out_nReturnValue = nReturnValue,
out_nSQLErrCode = nSQLErrCode,
out_sSQLErrMessage = sSQLErrMessage,
out_sPeriodEnddate = sPeriodEnddate,
out_sApproveddate = sApproveddate,
out_sPreValiditydate = sPreValiditydate,
out_sProhibitOnPer = sProhibitOnPer,
out_sProhibitOnQuantity = sProhibitOnQuantity
}).SingleOrDefault <PreclearanceRequestDTO>();
#region Error Values
if (Convert.ToInt32(nReturnValue.Value) != 0)
{
out_sContraTradeTillDate = Convert.ToString(sContraTradeTillDate.Value);
out_sPeriodEnddate = Convert.ToString(sPeriodEnddate.Value);
out_sApproveddate = Convert.ToString(sApproveddate.Value);
out_sPreValiditydate = Convert.ToString(sPreValiditydate.Value);
out_sProhibitOnPer = Convert.ToString(sProhibitOnPer.Value);
out_sProhibitOnQuantity = Convert.ToString(sProhibitOnQuantity.Value);
Exception e = new Exception();
out_nReturnValue = Convert.ToInt32(nReturnValue.Value);
string sReturnValue = "";
if (out_nReturnValue.ToString().Contains("16"))
{
sReturnValue = sLookUpPrefix1 + out_nReturnValue;
}
else
{
sReturnValue = sLookUpPrefix + out_nReturnValue;
}
e.Data[0] = sReturnValue;
if (nSQLErrCode.Value != System.DBNull.Value)
{
out_nSQLErrCode = Convert.ToInt32(nSQLErrCode.Value);
e.Data[1] = out_nSQLErrCode;
}
if (sSQLErrMessage.Value != System.DBNull.Value)
{
out_sSQLErrMessage = Convert.ToString(sSQLErrMessage.Value);
e.Data[2] = out_sSQLErrMessage;
}
Exception ex = new Exception(db.LastSQL.ToString(), e);
//bReturn = false;
throw ex;
}
else
{
scope.Complete();
//bReturn = true;
}
#endregion Error Values
}
}
}
catch (Exception exp)
{
throw exp;
}
return(res);
}