/// <summary>
/// This function will return a list of all the Groups saved in the system database
/// </summary>
/// <param name="i_sConnectionString"></param>
/// <returns></returns>
public IEnumerable <NSEGroupDTO> getGroupsDetails(string i_sConnectionString)
{
PetaPoco.Database db = null;
IEnumerable <NSEGroupDTO> lstGroupList = null;
try
{
using (db = new PetaPoco.Database(i_sConnectionString, "System.Data.SqlClient")
{
EnableAutoSelect = false
})
{
using (var scope = db.GetTransaction())
{
lstGroupList = db.Query <NSEGroupDTO>("exec st_tra_GetGroupDetails ",
new
{
}).ToList();
scope.Complete();
}
}
}
catch (Exception exp)
{
throw exp;
}
return(lstGroupList);
}
/// <summary>
/// This function will return a list of all the Group saved in the system database
/// </summary>
/// <param name="i_sConnectionString"></param>
/// <returns></returns>
public NSEGroupDTO GetSingleGroupDetails(string i_sConnectionString, int i_sGroupId)
{
PetaPoco.Database db = null;
NSEGroupDTO objGroupobject = new NSEGroupDTO();
try
{
using (db = new PetaPoco.Database(i_sConnectionString, "System.Data.SqlClient")
{
EnableAutoSelect = false
})
{
using (var scope = db.GetTransaction())
{
objGroupobject = db.Query <NSEGroupDTO>("exec st_tra_GetGroupDetails @inp_iGroupID",
new
{
inp_iGroupID = i_sGroupId
}).Single <NSEGroupDTO>();
scope.Complete();
}
}
}
catch (Exception exp)
{
throw exp;
}
return(objGroupobject);
}
/// <summary>
/// This function will return Group Id
/// </summary>
/// <param name="i_sConnectionString"></param>
/// <returns></returns>
public IEnumerable <NSEGroupDetailsDTO> GetgroupId(string i_sConnectionString, int i_UserInfoId, int i_TransMasterId)
{
PetaPoco.Database db = null;
IEnumerable <NSEGroupDetailsDTO> lstgrpId = null;
try
{
using (db = new PetaPoco.Database(i_sConnectionString, "System.Data.SqlClient")
{
EnableAutoSelect = false
})
{
using (var scope = db.GetTransaction())
{
lstgrpId = db.Query <NSEGroupDetailsDTO>("exec st_tra_GetgroupId @inp_UserInfoId,@inp_TransMasterId",
new
{
inp_UserInfoId = i_UserInfoId,
inp_TransMasterId = i_TransMasterId
}).ToList();
scope.Complete();
}
}
}
catch (Exception exp)
{
throw exp;
}
return(lstgrpId);
}
public IEnumerable <OTPConfigurationDTO> GetUserDeatailsForOTP(string i_sConnectionString, string userLoginId)
{
PetaPoco.Database db = null;
IEnumerable <OTPConfigurationDTO> lstUserLoginDetails = null;
try
{
using (db = new PetaPoco.Database(i_sConnectionString, "System.Data.SqlClient")
{
EnableAutoSelect = false
})
{
using (var scope = db.GetTransaction())
{
lstUserLoginDetails = db.Query <OTPConfigurationDTO>("exec st_usr_GetUserDetailsForOTP @inp_LoggedUserId",
new
{
inp_LoggedUserId = userLoginId
}).ToList();
scope.Complete();
}
}
}
catch (Exception exp)
{
throw exp;
}
return(lstUserLoginDetails);
}
/// <summary>
/// This method is used for get user Get DocumentAuto ID.
/// </summary>
/// <param name="i_sConnectionString">Connection string for which database</param>
/// <param name="i_objUserInfoDTO">User Type Object</param>
/// <returns>User Information List</returns>
public UpsiDTO GetDocumentAutoID(string i_sConnectionString, int UserInfo)
{
PetaPoco.Database db = null;
UpsiDTO objUpsiTempSharingData = new UpsiDTO();
try
{
using (db = new PetaPoco.Database(i_sConnectionString, "System.Data.SqlClient")
{
EnableAutoSelect = false
})
{
using (var scope = db.GetTransaction())
{
objUpsiTempSharingData = db.Query <UpsiDTO>("exec st_UpsiAutoId @inp_iUserInfoId",
new
{
inp_iUserInfoId = UserInfo
}).Single <UpsiDTO>();
scope.Complete();
}
}
}
catch (Exception exp)
{
throw exp;
}
return(objUpsiTempSharingData);
}
/// <summary>
/// This function will Get mst_company details
/// </summary>
/// <returns></returns>
public InsiderInitialDisclosureDTO Get_mst_company_details(string inp_sConnectionString)
{
PetaPoco.Database db = null;
InsiderInitialDisclosureDTO objInsiderInitialDisclosureDAL = new InsiderInitialDisclosureDTO();
try
{
using (db = new PetaPoco.Database(inp_sConnectionString, "System.Data.SqlClient")
{
EnableAutoSelect = false
})
{
using (var scope = db.GetTransaction())
{
objInsiderInitialDisclosureDAL = db.Query <InsiderInitialDisclosureDTO>("exec st_com_mst_Company_Details",
new
{
}).Single <InsiderInitialDisclosureDTO>();
scope.Complete();
}
}
}
catch (Exception exp)
{
throw exp;
}
return(objInsiderInitialDisclosureDAL);
}
/// <summary>
/// This function will return a user logged in details saved in the system database
/// </summary>
/// <param name="i_sConnectionString"></param>
/// <returns></returns>
public InsiderDashboardDTO GetTradingCalenderDetails(string i_sConnectionString, int inp_userInfoID)
{
PetaPoco.Database db = null;
InsiderDashboardDTO TradingCalenderDetails = null;
try
{
using (db = new PetaPoco.Database(i_sConnectionString, "System.Data.SqlClient")
{
EnableAutoSelect = false
})
{
using (var scope = db.GetTransaction())
{
TradingCalenderDetails = db.Query <InsiderDashboardDTO>("exec st_usr_CalenderRuleDetails @inp_userInfoID",
new
{
inp_userInfoID = inp_userInfoID
}).FirstOrDefault <InsiderDashboardDTO>();
scope.Complete();
}
}
}
catch (Exception exp)
{
throw exp;
}
return(TradingCalenderDetails);
}
/// <summary>
/// This function will return duplicate transaction records exists in System
/// </summary>
/// <param name="i_sConnectionString"></param>
/// <returns></returns>
public IEnumerable <DupTransCntDTO> GetDupTransCnt(string i_sConnectionString, int i_UserInfoId)
{
PetaPoco.Database db = null;
IEnumerable <DupTransCntDTO> lstDupTransCnt = null;
try
{
using (db = new PetaPoco.Database(i_sConnectionString, "System.Data.SqlClient")
{
EnableAutoSelect = false
})
{
using (var scope = db.GetTransaction())
{
lstDupTransCnt = db.Query <DupTransCntDTO>("exec st_tra_GetDupTransCnt @inp_iUserInfoId",
new
{
inp_iUserInfoId = i_UserInfoId
}).ToList();
scope.Complete();
}
}
}
catch (Exception exp)
{
throw exp;
}
return(lstDupTransCnt);
}
/// <summary>
/// This function will Get mst_company details
/// </summary>
/// <returns></returns>
public InsiderInitialDisclosureDTO st_tra_GetTradingPolicyIDfor_OS(string inp_sConnectionString, int UserInfoID)
{
PetaPoco.Database db = null;
InsiderInitialDisclosureDTO objInsiderInitialDisclosureDAL = new InsiderInitialDisclosureDTO();
try
{
using (db = new PetaPoco.Database(inp_sConnectionString, "System.Data.SqlClient")
{
EnableAutoSelect = false
})
{
using (var scope = db.GetTransaction())
{
objInsiderInitialDisclosureDAL = db.Query <InsiderInitialDisclosureDTO>("exec st_tra_GetTradingPolicyIDfor_OS @inp_iUserInfoId",
new
{
inp_iUserInfoId = UserInfoID
}).Single <InsiderInitialDisclosureDTO>();
scope.Complete();
}
}
}
catch (Exception exp)
{
throw exp;
}
return(objInsiderInitialDisclosureDAL);
}
/// <summary>
/// This function will return a list of all the Groups saved in the system database
/// </summary>
/// <param name="i_sConnectionString"></param>
/// <returns></returns>
public IEnumerable <NSEGroupDocumentMappingDTO> GetNSEDocumentDetails(string i_sConnectionString, int i_sGroupId, int UserInfoIdCheck)
{
PetaPoco.Database db = null;
IEnumerable <NSEGroupDocumentMappingDTO> lstGroupDocumentList = null;
try
{
using (db = new PetaPoco.Database(i_sConnectionString, "System.Data.SqlClient")
{
EnableAutoSelect = false
})
{
using (var scope = db.GetTransaction())
{
lstGroupDocumentList = db.Query <NSEGroupDocumentMappingDTO>("exec st_tra_GetNSEDocumentDetails @inp_iGroupID,@inp_UserInfoCheck",
new
{
inp_iGroupID = i_sGroupId,
inp_UserInfoCheck = UserInfoIdCheck
}).ToList();
scope.Complete();
}
}
}
catch (Exception exp)
{
throw exp;
}
return(lstGroupDocumentList);
}
/// <summary>
/// This function will return a list of all the Groups saved in the system database
/// </summary>
/// <param name="i_sConnectionString"></param>
/// <returns></returns>
public IEnumerable <NSEGroupDocumentMappingDTO> GetSingleNSEDocumentDetails(string i_sConnectionString, int i_TransactionMasterId)
{
PetaPoco.Database db = null;
IEnumerable <NSEGroupDocumentMappingDTO> lstSingleDocumentList = null;
try
{
using (db = new PetaPoco.Database(i_sConnectionString, "System.Data.SqlClient")
{
EnableAutoSelect = false
})
{
using (var scope = db.GetTransaction())
{
lstSingleDocumentList = db.Query <NSEGroupDocumentMappingDTO>("exec st_tra_GetFileDetails @i_TransactionMasterId",
new
{
i_TransactionMasterId = i_TransactionMasterId
}).ToList();
scope.Complete();
}
}
}
catch (Exception exp)
{
throw exp;
}
return(lstSingleDocumentList);
}
private void fbLogin(HttpContext context)
{
string token = context.Request.Params["token"];
Facebook.FacebookClient client = new Facebook.FacebookClient(token);
//client.Post()
client.UseFacebookBeta = client.IsSecureConnection = true;
Facebook.JsonObject o = (Facebook.JsonObject)client.Get("/me");
var db = new PetaPoco.Database(Common.HairStyleConnectionString, "System.Data.SqlClient");
using (var scope = db.GetTransaction())
{
try
{
string first_name = (string)o["first_name"];
string name = (string)o["name"];
decimal id = Convert.ToDecimal(o["id"]);
POCOS.Facebook fb = new POCOS.Facebook();
fb.name = name;
fb.first_name = first_name;
fb.gender = (string)o["gender"];
fb.id = id;
fb.last_name = (string)o["last_name"];
fb.link = (string)o["link"];
fb.locale = (string)o["locale"];
fb.timezone = Convert.ToDouble(o["timezone"]);
string updatedtime = (string)o["updated_time"];
DateTime dt;
if (DateTime.TryParse(updatedtime, out dt))
{
fb.updated_time = dt;
}
if (db.Exists <POCOS.Facebook>(id))
{
db.Update(fb);
}
else
{
db.Insert(fb);
}
POCOS.AppUser au = POCOS.AppUser.FirstOrDefault("Select top 1 * from AppUsers where facebookid=@0", id);
if (au == null)
{
au = new POCOS.AppUser();
au.FirstName = first_name;
au.facebookid = id;
db.Insert(au);
}
scope.Complete();
CookieUtil.WriteCookie(Common.AuthCookie, EncDec.Encrypt(JsonConvert.SerializeObject(new { ID = au.ID }), Common.DefaultPassword), false);
CookieUtil.WriteCookie(Common.InfoCookie, JsonConvert.SerializeObject(new { email = au.Email, name = au.Name, avatar = string.IsNullOrWhiteSpace(au.Avatar) ? null : Common.UploadedImageRelPath + au.Avatar }), false);
}
finally
{
scope.Dispose();
}
}
}
/// <summary>
/// This method is used for the Update Notification response
/// </summary>
/// <param name="i_sConnectionString"></param>
/// <param name="i_nCompanyId"></param>
/// <param name="i_tblNotificationResponseMessage"></param>
/// <returns></returns>
public bool UpdateNotificationResponse(string i_sConnectionString, int i_nCompanyId, DataTable i_tblNotificationResponseMessage)
{
PetaPoco.Database db = null;
IEnumerable <InsiderTradingDAL.NotificationSendListDTO> lstCompanyList = null;
bool bReturn = false;
try
{
#region Out Paramter
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 = "";
var inp_tblNootification = new SqlParameter();
inp_tblNootification.DbType = DbType.Object;
inp_tblNootification.ParameterName = "@inp_tblNotificationQueueResponse";
inp_tblNootification.TypeName = "dbo.NotificationQueueResponse";
inp_tblNootification.SqlValue = i_tblNotificationResponseMessage;
inp_tblNootification.SqlDbType = SqlDbType.Structured;
#endregion Out Paramter
using (db = new PetaPoco.Database(i_sConnectionString, "System.Data.SqlClient")
{
EnableAutoSelect = false
})
{
using (var scope = db.GetTransaction())
{
lstCompanyList = db.Query <InsiderTradingDAL.NotificationSendListDTO>("exec st_cmu_UpdateNotificationResponse @inp_nCompanyId,@inp_tblNotificationQueueResponse,@out_nReturnValue OUTPUT,@out_nSQLErrCode OUTPUT,@out_sSQLErrMessage OUTPUT",
new
{
inp_nCompanyId = i_nCompanyId,
inp_tblNotificationQueueResponse = inp_tblNootification,
out_nReturnValue = nReturnValue,
out_nSQLErrCode = nSQLErrCode,
out_sSQLErrMessage = sSQLErrMessage
}).ToList();
scope.Complete();
}
}
bReturn = true;
}
catch (Exception exp)
{
bReturn = false;
throw exp;
}
return(bReturn);
}
//#region CheckPasswordHistory
///// <summary>
/////
///// </summary>
///// <param name="i_sConnectionString"></param>
///// <param name="inp_iUserInfoId"></param>
///// <param name="out_nReturnValue"></param>
///// <param name="out_nSQLErrCode"></param>
///// <param name="out_sSQLErrMessage"></param>
///// <returns></returns>
//public Boolean CheckPasswordHistory(string i_sConnectionString,string i_sPassword,int i_sUserInfoId)
//{
// List<PasswordConfigDTO> 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<PasswordConfigDTO>("exec st_usr_CheckPasswordHistory @inp_iUserInfoId, @out_nReturnValue OUTPUT,@out_nSQLErrCode OUTPUT,@out_sSQLErrMessage OUTPUT",
// new
// {
// inp_iUserInfoId = i_sUserInfoId,
// out_nReturnValue = nReturnValue,
// out_nSQLErrCode = nSQLErrCode,
// out_sSQLErrMessage = sSQLErrMessage
// }).ToList<PasswordConfigDTO>();
// if ((Convert.ToInt32(nReturnValue.Value) != 0)&&(Convert.ToInt32(nReturnValue.Value) != 1))
// {
// Exception e = new Exception();
// out_nReturnValue = Convert.ToInt32(nReturnValue.Value);
// string sReturnValue = out_nReturnValue.ToString();
// 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();
// if (Convert.ToInt32(nReturnValue.Value) == 0)
// return false;
// else
// return true;
// }
// }
// }
// }
// catch (Exception exp)
// {
// throw exp;
// }
//}
//#endregion SavePasswordConfigDetails
#region CheckPasswordHistory
/// <summary>
/// This function will return a user logged in details saved in the system database
/// </summary>
/// <param name="i_sConnectionString"></param>
/// <returns></returns>
public IEnumerable <PasswordConfigDTO> CheckPasswordHistory(string i_sConnectionString, int i_sUserInfoId)
{
IEnumerable <PasswordConfigDTO> lstUserPwdDetails = 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;
using (db = new PetaPoco.Database(i_sConnectionString, "System.Data.SqlClient")
{
EnableAutoSelect = false
})
{
using (var scope = db.GetTransaction())
{
lstUserPwdDetails = db.Query <PasswordConfigDTO>("exec st_usr_CheckPasswordHistory @inp_iUserInfoId, @out_nReturnValue OUTPUT,@out_nSQLErrCode OUTPUT,@out_sSQLErrMessage OUTPUT",
new
{
inp_iUserInfoId = i_sUserInfoId,
out_nReturnValue = nReturnValue,
out_nSQLErrCode = nSQLErrCode,
out_sSQLErrMessage = sSQLErrMessage
}).ToList();
scope.Complete();
}
}
}
catch (Exception exp)
{
throw exp;
}
return(lstUserPwdDetails);
}
/// <summary>
/// This method is used for the Get Company Details For Notification
/// </summary>
/// <param name="i_sConnectionString">DB Connection string</param>
/// <param name="i_nCompanyId">Company ID</param>
/// <param name="i_sCompanyDBName">Company database name for fetching the SMTP details for the corresponding company.
/// If Companyid is not given i.e. is 0 then this will be used for fetching the details.</param>
/// <returns></returns>
public IEnumerable <InsiderTradingDAL.CompanyDetailsForNotificationDTO> GetCompanyDetailsForNotification(string i_sConnectionString, int i_nCompanyId, string i_sCompanyDBName = "")
{
PetaPoco.Database db = null;
IEnumerable <InsiderTradingDAL.CompanyDetailsForNotificationDTO> lstCompanyList = null;
try
{
#region Out Paramter
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 = "";
#endregion Out Paramter
using (db = new PetaPoco.Database(i_sConnectionString, "System.Data.SqlClient")
{
EnableAutoSelect = false
})
{
using (var scope = db.GetTransaction())
{
lstCompanyList = db.Query <InsiderTradingDAL.CompanyDetailsForNotificationDTO>("exec st_cmu_GetCompanyDetailsForNotification @inp_nCompanyId, @inp_sCompanyDBName,@out_nReturnValue OUTPUT,@out_nSQLErrCode OUTPUT,@out_sSQLErrMessage OUTPUT",
new
{
inp_nCompanyId = i_nCompanyId,
inp_sCompanyDBName = i_sCompanyDBName,
out_nReturnValue = nReturnValue,
out_nSQLErrCode = nSQLErrCode,
out_sSQLErrMessage = sSQLErrMessage
}).ToList();
scope.Complete();
}
}
}
catch (Exception exp)
{
throw exp;
}
return(lstCompanyList);
}
public void SaveRestockLine(object parameter)
{
try
{
using (var db = new PetaPoco.Database("db"))
{
using (var scope = db.GetTransaction())
{
var vm = (RestockViewModel)parameter;
if (vm.SelectedRestockLineModel == null)
{
return;
}
var selectedRL = vm.SelectedRestockLineModel;
if (selectedRL.Amt == 0)
{
db.Execute("DELETE FROM RestockLines WHERE RestockLineId = @0", selectedRL.RestockLineId);
}
var changes = db.ExecuteScalar <int>("Update RestockLines SET Amt = @0 WHERE RestockLineId = @1 AND RestockId =@2;Select Changes()", selectedRL.Amt, selectedRL.RestockLineId, selectedRL.RestockId);
if (changes == 0)
{
var lastid = db.ExecuteScalar <int>("Select MAX(RestockLineId) FROM RestockLines");
selectedRL.RestockLineId = db.ExecuteScalar <int>("Insert Into RestockLines(RestockLineId,RestockId, Pos, ArtId, Amt) VALUES(@0, @1, @2, @3,@4);SELECT last_insert_rowid();", lastid + 1, selectedRL.RestockId, selectedRL.Pos, selectedRL.ArtId, selectedRL.Amt);
}
scope.Complete();
}
}
}
catch (Exception ex)
{
MessageBox.Show(string.Format("Daten konnten nicht gespeichert werden:\n\n" + ex.Message), "Fehler", MessageBoxButton.OK, MessageBoxImage.Asterisk);
}
}
public bool CheckIsOTPActived(string i_sConnectionString, string s_LoggedInId)
{
PetaPoco.Database db = null;
int IsActiveOTP = 0;
try
{
using (db = new PetaPoco.Database(i_sConnectionString, "System.Data.SqlClient")
{
EnableAutoSelect = false
})
{
using (var scope = db.GetTransaction())
{
IsActiveOTP = db.Query <int>("exec st_usr_EmailOTPSettingActiveOrNot @inp_LoggedUserId",
new
{
inp_LoggedUserId = s_LoggedInId
}).Single <int>();
if (Convert.ToInt32(IsActiveOTP) != 0)
{
scope.Complete();
return(true);
}
else
{
scope.Complete();
return(false);
}
}
}
}
catch (Exception exp)
{
throw exp;
}
}
public List <NotificationDTO> GetDashboardNotificationList_OS(string sConnectionString, int inp_iLoggedInUserId)
{
#region Paramters
int out_nReturnValue;
int out_nSQLErrCode;
string out_sSQLErrMessage;
List <NotificationDTO> res = null;
#endregion Paramters
try
{
#region Out Paramter
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 = "";
#endregion Out Paramter
using (var db = new PetaPoco.Database(sConnectionString, "System.Data.SqlClient")
{
EnableAutoSelect = false
})
{
using (var scope = db.GetTransaction())
{
res = db.Query <NotificationDTO>("exec st_cmu_DashboardNotificationList_OS @inp_nLoggedInUserId,@out_nReturnValue OUTPUT,@out_nSQLErrCode OUTPUT,@out_sSQLErrMessage OUTPUT",
new
{
@inp_nLoggedInUserId = inp_iLoggedInUserId,
@out_nReturnValue = nReturnValue,
@out_nSQLErrCode = nSQLErrCode,
@out_sSQLErrMessage = sSQLErrMessage
}).ToList <NotificationDTO>();
#region Error Values
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();
return(res);
}
#endregion Error Values
}
}
}
catch (Exception exp)
{
throw exp;
}
finally
{
}
}
private void DoBackgroundWork_Insert(object sender, System.ComponentModel.DoWorkEventArgs e)
{
BackgroundWorker worker = sender as BackgroundWorker;
// Create a PetaPoco database object
var db = new PetaPoco.Database("sqlite");
using (var transaction = db.GetTransaction())
{
for (int i = 0; i < count; i++)
{
foo foo = new foo();
foo.name = "PetaPoco Insert Test " + i;
db.Insert("foo", "Id", foo);
if (i % 500 == 0) worker.ReportProgress(i);//this.fooQuery1.AppendDisplay(".");
}
transaction.Complete();
}
}
public FormBDetails_OSDTO GetFormBOSDetails(string i_sConnectionString, int i_nMapToTypeCodeId, int i_nMapToId)
{
FormBDetails_OSDTO 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 <FormBDetails_OSDTO>("exec st_tra_GetGeneratedFormBDetails_OS @inp_iMapToTypeCodeId,@inp_iMapToId,@out_nReturnValue OUTPUT,@out_nSQLErrCode OUTPUT,@out_sSQLErrMessage OUTPUT",
new
{
@inp_iMapToTypeCodeId = i_nMapToTypeCodeId,
@inp_iMapToId = i_nMapToId,
out_nReturnValue = nReturnValue,
out_nSQLErrCode = nSQLErrCode,
out_sSQLErrMessage = sSQLErrMessage
}).FirstOrDefault <FormBDetails_OSDTO>();
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 IEnumerable <InsiderInitialDisclosureDTO> GetInitialDisclosureDetails(string sConnectionString, int UserInfoID)
{
#region Paramters
int out_nReturnValue;
int out_nSQLErrCode;
string out_sSQLErrMessage;
List <InsiderInitialDisclosureDTO> res = null;
#endregion Paramters
try
{
#region Out Paramter
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;
#endregion Out Paramter
using (var db = new PetaPoco.Database(sConnectionString, "System.Data.SqlClient")
{
EnableAutoSelect = false
})
{
using (var scope = db.GetTransaction())
{
res = db.Query <InsiderInitialDisclosureDTO>("exec st_tra_InitialDisclosureDetails @inp_iUserInfoID,@out_nReturnValue OUTPUT,@out_nSQLErrCode OUTPUT,@out_sSQLErrMessage OUTPUT",
new
{
@inp_iUserInfoID = UserInfoID,
out_nReturnValue = nReturnValue,
out_nSQLErrCode = nSQLErrCode,
out_sSQLErrMessage = sSQLErrMessage
}).ToList <InsiderInitialDisclosureDTO>();
#region Error Values
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();
}
#endregion Error Values
}
}
}
catch (Exception exp)
{
throw exp;
}
finally
{
}
return(res);
}
private void fbLogin(HttpContext context)
{
string token = context.Request.Params["token"];
Facebook.FacebookClient client = new Facebook.FacebookClient(token);
//client.Post()
client.UseFacebookBeta = client.IsSecureConnection = true;
Facebook.JsonObject o = (Facebook.JsonObject)client.Get("/me");
var db = new PetaPoco.Database(Common.HairStyleConnectionString, "System.Data.SqlClient");
using (var scope = db.GetTransaction())
{
try
{
string first_name = (string)o["first_name"];
string name = (string)o["name"];
decimal id = Convert.ToDecimal(o["id"]);
POCOS.Facebook fb = new POCOS.Facebook();
fb.name = name;
fb.first_name = first_name;
fb.gender = (string)o["gender"];
fb.id = id;
fb.last_name = (string)o["last_name"];
fb.link = (string)o["link"];
fb.locale = (string)o["locale"];
fb.timezone = Convert.ToDouble(o["timezone"]);
string updatedtime = (string)o["updated_time"];
DateTime dt;
if (DateTime.TryParse(updatedtime, out dt))
fb.updated_time = dt;
if (db.Exists<POCOS.Facebook>(id))
db.Update(fb);
else
db.Insert(fb);
POCOS.AppUser au = POCOS.AppUser.FirstOrDefault("Select top 1 * from AppUsers where facebookid=@0", id);
if (au == null)
{
au = new POCOS.AppUser();
au.FirstName = first_name;
au.facebookid = id;
db.Insert(au);
}
scope.Complete();
CookieUtil.WriteCookie(Common.AuthCookie, EncDec.Encrypt(JsonConvert.SerializeObject(new { ID = au.ID }), Common.DefaultPassword), false);
CookieUtil.WriteCookie(Common.InfoCookie, JsonConvert.SerializeObject(new { email = au.Email, name = au.Name, avatar = string.IsNullOrWhiteSpace(au.Avatar) ? null : Common.UploadedImageRelPath + au.Avatar }), false);
}
finally
{
scope.Dispose();
}
}
}
public bool SaveEvent(string sConnectionString, UserPolicyDocumentEventLogDTO m_objUserPolicyDocumentEventLogDTO, int nLoggedInUserId)
{
#region Paramters
bool bReturn = false;
int out_nReturnValue;
int out_nSQLErrCode;
string out_sSQLErrMessage;
List <InsiderInitialDisclosureDTO> res = null;
#endregion Paramters
try
{
#region Out Paramter
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;
#endregion Out Paramter
using (var db = new PetaPoco.Database(sConnectionString, "System.Data.SqlClient")
{
EnableAutoSelect = false
})
{
using (var scope = db.GetTransaction())
{
res = db.Query <InsiderInitialDisclosureDTO>("exec st_eve_EventLogSave @inp_iEventCodeId,@inp_iMapToTypeCodeId,@inp_iMapToid,@inp_iUserId,@out_nReturnValue OUTPUT,@out_nSQLErrCode OUTPUT,@out_sSQLErrMessage OUTPUT",
new
{
@inp_iEventCodeId = m_objUserPolicyDocumentEventLogDTO.EventCodeId,
@inp_iMapToTypeCodeId = m_objUserPolicyDocumentEventLogDTO.MapToTypeCodeId,
@inp_iMapToid = m_objUserPolicyDocumentEventLogDTO.MapToId,
@inp_iUserId = nLoggedInUserId,
@out_nReturnValue = nReturnValue,
@out_nSQLErrCode = nSQLErrCode,
@out_sSQLErrMessage = sSQLErrMessage
}).ToList <InsiderInitialDisclosureDTO>();
#region Error Values
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;
}
#endregion Error Values
}
}
}
catch (Exception exp)
{
throw exp;
}
finally
{
}
return(bReturn);
}
public TemplateMasterDTO GetFormETemplate(string sConnectionString)
{
#region Paramters
int out_nReturnValue;
int out_nSQLErrCode;
string out_sSQLErrMessage;
#endregion Paramters
try
{
#region Out Paramter
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 = "";
#endregion Out Paramter
using (var db = new PetaPoco.Database(sConnectionString, "System.Data.SqlClient")
{
EnableAutoSelect = false
})
{
using (var scope = db.GetTransaction())
{
var res = db.Query <TemplateMasterDTO>("exec st_tra_CheckFormE_Template @out_nReturnValue OUTPUT,@out_nSQLErrCode OUTPUT,@out_sSQLErrMessage OUTPUT",
new
{
@out_nReturnValue = nReturnValue,
@out_nSQLErrCode = nSQLErrCode,
@out_sSQLErrMessage = sSQLErrMessage
}).SingleOrDefault <TemplateMasterDTO>();
#region Error Values
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();
return(res);
}
#endregion Error Values
}
}
}
catch (Exception exp)
{
throw exp;
}
finally
{
}
}
public TemplateMasterDTO SaveDetails(TemplateMasterDTO m_objTemplateMasterDTO, string sConnectionString, int nLoggedInUserId)
{
#region Paramters
int out_nReturnValue;
int out_nSQLErrCode;
string out_sSQLErrMessage;
#endregion Paramters
try
{
#region Out Paramter
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.Size = 500;
sSQLErrMessage.Value = "";
#endregion Out Paramter
using (var db = new PetaPoco.Database(sConnectionString, "System.Data.SqlClient")
{
EnableAutoSelect = false
})
{
using (var scope = db.GetTransaction())
{
var res = db.Query <TemplateMasterDTO>("exec st_tra_TemplateMasterSave @inp_iTemplateMasterId,@inp_sTemplateName ,@inp_iCommunicationModeCodeId ,@inp_iDisclosureTypeCodeId ,@inp_iLetterForCodeId,@inp_bIsActive,@inp_dtDate,@inp_sToAddress1,@inp_sToAddress2,@inp_sSubject,@inp_sContents,@inp_sSignature,@inp_sCommunicationFrom,@inp_sSequenceNo,@inp_bIsCommunicationTemplate,@inp_nUserId,@out_nReturnValue OUTPUT,@out_nSQLErrCode OUTPUT,@out_sSQLErrMessage OUTPUT",
new
{
@inp_iTemplateMasterId = m_objTemplateMasterDTO.TemplateMasterId,
@inp_sTemplateName = m_objTemplateMasterDTO.TemplateName,
@inp_iCommunicationModeCodeId = m_objTemplateMasterDTO.CommunicationModeCodeId,
@inp_iDisclosureTypeCodeId = (m_objTemplateMasterDTO.DisclosureTypeCodeId == 0 ? null : m_objTemplateMasterDTO.DisclosureTypeCodeId),
@inp_iLetterForCodeId = (m_objTemplateMasterDTO.LetterForCodeId == 0 ? null :m_objTemplateMasterDTO.LetterForCodeId),
@inp_bIsActive = m_objTemplateMasterDTO.IsActive,
@inp_dtDate = m_objTemplateMasterDTO.Date,
@inp_sToAddress1 = m_objTemplateMasterDTO.ToAddress1,
@inp_sToAddress2 = m_objTemplateMasterDTO.ToAddress2,
@inp_sSubject = m_objTemplateMasterDTO.Subject,
@inp_sContents = m_objTemplateMasterDTO.Contents,
@inp_sSignature = m_objTemplateMasterDTO.Signature,
@inp_sCommunicationFrom = m_objTemplateMasterDTO.CommunicationFrom,
@inp_sSequenceNo = m_objTemplateMasterDTO.SequenceNo,
@inp_bIsCommunicationTemplate = m_objTemplateMasterDTO.IsCommunicationTemplate,
@inp_nUserId = nLoggedInUserId,
@out_nReturnValue = nReturnValue,
@out_nSQLErrCode = nSQLErrCode,
@out_sSQLErrMessage = sSQLErrMessage
}).SingleOrDefault <TemplateMasterDTO>();
#region Error Values
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();
return(res);
}
#endregion Error Values
}
}
}
catch (Exception exp)
{
throw exp;
}
finally
{
}
}
/// <summary>
/// This method is used for get Trading Policy For Transaction Security List
/// </summary>
/// <param name="i_sConnectionString">DB Connection string</param>
/// <param name="i_nTradingPolicyId">Trading Policy ID</param>
/// <param name="i_nMapToTypeCodeId">Map To Type code ID</param>
/// <returns></returns>
public IEnumerable <TradingPolicyForTransactionSecurityDTO> TradingPolicyForTransactionSecurityList(string i_sConnectionString,
int?i_nTradingPolicyId, int i_nMapToTypeCodeId)
{
IEnumerable <TradingPolicyForTransactionSecurityDTO> 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;
using (db = new PetaPoco.Database(i_sConnectionString, "System.Data.SqlClient")
{
EnableAutoSelect = false
})
{
using (var scope = db.GetTransaction())
{
res = db.Query <TradingPolicyForTransactionSecurityDTO>("exec st_rul_TradingPolicyForTransactionSecurityList @inp_iTradingPolicyId,@inp_iMapToTypeCodeId,@out_nReturnValue OUTPUT,@out_nSQLErrCode OUTPUT,@out_sSQLErrMessage OUTPUT",
new
{
inp_iTradingPolicyId = i_nTradingPolicyId,
inp_iMapToTypeCodeId = i_nMapToTypeCodeId,
out_nReturnValue = nReturnValue,
out_nSQLErrCode = nSQLErrCode,
out_sSQLErrMessage = sSQLErrMessage
}).ToList <TradingPolicyForTransactionSecurityDTO>();
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();
}
}
}
}
catch (Exception exp)
{
//bReturn = false;
throw exp;
}
return(res);
}
/// <summary>
/// This function will be used to query the database to fetch the list of active FAQ.
/// </summary>
/// <param name="sConnectionString"></param>
/// <param name="i_nUserTypeCodeId">The type of user i.e. for CO or for insider</param>
/// <param name="inp_iPageSize">Values: 0, give all records. <pageNo>, give records for given page sizePage size</param>
/// <param name="inp_iPageNo">Page number</param>
/// <param name="inp_sSortField">The name of the field on which the list showuld be sorted. The name of the field will be as seen in the procedure st_tra_TemplateMasterFAQList.</param>
/// <param name="inp_sSortOrder">Values: ASC/DESC, The order in which the list should be sorted.</param>
/// <param name="inp_bForDashboard">Values: 1/0, This parameter will be used to decide if the call is for showing the FAQ list on dashboard or on FAQ List page</param>
/// <returns></returns>
public List <FAQDTO> GetFAQList(string sConnectionString, int inp_iUserTypeCodeId, int inp_iPageSize, int inp_iPageNo, string inp_sSortField, string inp_sSortOrder, bool inp_bForDashboard)
{
#region Paramters
int out_nReturnValue;
int out_nSQLErrCode;
string out_sSQLErrMessage;
int nFAQListFor = 0;
#endregion Paramters
try
{
#region Out Paramter
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 = "";
#endregion Out Paramter
if (inp_bForDashboard)
{
nFAQListFor = 1;
}
else
{
nFAQListFor = 2;
}
using (var db = new PetaPoco.Database(sConnectionString, "System.Data.SqlClient")
{
EnableAutoSelect = false
})
{
using (var scope = db.GetTransaction())
{
var res = db.Query <FAQDTO>("exec st_tra_TemplateMasterFAQList @inp_iListForPage, @inp_iUserTypeCodeId,@inp_iPageSize,@inp_iPageNo,@inp_sSortField,@inp_sSortOrder,@out_nReturnValue OUTPUT,@out_nSQLErrCode OUTPUT,@out_sSQLErrMessage OUTPUT",
new
{
@inp_iListForPage = nFAQListFor,
@inp_iUserTypeCodeId = inp_iUserTypeCodeId,
@inp_iPageSize = inp_iPageSize,
@inp_iPageNo = inp_iPageNo,
@inp_sSortField = inp_sSortField,
@inp_sSortOrder = inp_sSortOrder,
@out_nReturnValue = nReturnValue,
@out_nSQLErrCode = nSQLErrCode,
@out_sSQLErrMessage = sSQLErrMessage
}).ToList <FAQDTO>();
#region Error Values
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();
return(res);
}
#endregion Error Values
}
}
}
catch (Exception exp)
{
throw exp;
}
finally
{
}
}
/// <summary>
/// This method is used for the get details for trading policy details.
/// </summary>
/// <param name="i_sConnectionString">DB COnnection string</param>
/// <param name="i_nTradingPolicyId">Trading Policy ID</param>
/// <returns></returns>
public InsiderTradingDAL.TradingPolicyDTO GetUserwiseOverlapTradingPolicyCount(string i_sConnectionString, int i_nTradingPolicyId, out int out_nCountUserAndOverlapTradingPolicy)
{
TradingPolicyDTO 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 nCountUserAndOverlapTradingPolicy = new SqlParameter("@out_nCountUserAndOverlapTradingPolicy", System.Data.SqlDbType.Int);
nCountUserAndOverlapTradingPolicy.Direction = System.Data.ParameterDirection.Output;
using (db = new PetaPoco.Database(i_sConnectionString, "System.Data.SqlClient")
{
EnableAutoSelect = false
})
{
db.CommandTimeout = 5000;
using (var scope = db.GetTransaction())
{
res = db.Query <TradingPolicyDTO>("exec st_rul_UserwiseOverlapTradingPolicyCount @inp_iTradingPolicyId,@out_nCountUserAndOverlapTradingPolicy OUTPUT,@out_nReturnValue OUTPUT,@out_nSQLErrCode OUTPUT,@out_sSQLErrMessage OUTPUT",
new
{
inp_iTradingPolicyId = i_nTradingPolicyId,
out_nCountUserAndOverlapTradingPolicy = nCountUserAndOverlapTradingPolicy,
out_nReturnValue = nReturnValue,
out_nSQLErrCode = nSQLErrCode,
out_sSQLErrMessage = sSQLErrMessage
}).FirstOrDefault <TradingPolicyDTO>();
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_nCountUserAndOverlapTradingPolicy = Convert.ToInt32(nCountUserAndOverlapTradingPolicy.Value);
scope.Complete();
}
}
}
}
catch (Exception exp)
{
//bReturn = false;
throw exp;
}
return(res);
}
/// <summary>
/// This method is used for the save Trading policy details
/// </summary>
/// <param name="i_sConnectionString">DB Connection string</param>
/// <param name="m_objTradingPolicyDTO">object TradingPolicyDTO</param>
/// <returns></returns>
public TradingPolicyDTO Save(string i_sConnectionString, TradingPolicyDTO m_objTradingPolicyDTO, DataTable i_tblPreclearanceSecuritywise,
DataTable i_tblContinousSecuritywise, DataTable i_tblPreclearanceTransactionSecurityMap)
{
#region Paramters
//bool bReturn = false;
int out_nReturnValue;
int out_nSQLErrCode;
string out_sSQLErrMessage;
TradingPolicyDTO res = null;
PetaPoco.Database db = null;
#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 inp_tblPreclearanceSecuritywise = new SqlParameter();
inp_tblPreclearanceSecuritywise.DbType = DbType.Object;
inp_tblPreclearanceSecuritywise.ParameterName = "@inp_tblPreClearanceSecuritywise";
inp_tblPreclearanceSecuritywise.TypeName = "dbo.SecuritywiseLimitsType";
inp_tblPreclearanceSecuritywise.SqlValue = i_tblPreclearanceSecuritywise;
inp_tblPreclearanceSecuritywise.SqlDbType = SqlDbType.Structured;
var inp_tblContinousSecuritywise = new SqlParameter();
inp_tblContinousSecuritywise.DbType = DbType.Object;
inp_tblContinousSecuritywise.ParameterName = "@inp_tblContinousSecuritywise";
inp_tblContinousSecuritywise.TypeName = "dbo.SecuritywiseLimitsType";
inp_tblContinousSecuritywise.SqlValue = i_tblContinousSecuritywise;
inp_tblContinousSecuritywise.SqlDbType = SqlDbType.Structured;
var inp_tblPreclearanceTransactionSecurityMap = new SqlParameter();
inp_tblPreclearanceTransactionSecurityMap.DbType = DbType.Object;
inp_tblPreclearanceTransactionSecurityMap.ParameterName = "@inp_tblPreclearanceTransactionSecurityMap";
inp_tblPreclearanceTransactionSecurityMap.TypeName = "dbo.TradingPolicyForTransactionSecurityMap";
inp_tblPreclearanceTransactionSecurityMap.SqlValue = i_tblPreclearanceTransactionSecurityMap;
inp_tblPreclearanceTransactionSecurityMap.SqlDbType = SqlDbType.Structured;
#endregion Output Param
using (db = new PetaPoco.Database(i_sConnectionString, "System.Data.SqlClient")
{
EnableAutoSelect = false
})
{
using (var scope = db.GetTransaction())
{
res = db.Query <TradingPolicyDTO>("exec st_rul_TradingPolicySave @inp_iTradingPolicyId,@inp_iTradingPolicyParentId,"
+ "@inp_iCurrentHistoryCodeId,@inp_iTradingPolicyForCodeId,@inp_sTradingPolicyName,@inp_sTradingPolicyDescription,@inp_dtApplicableFromDate,"
+ "@inp_dtApplicableToDate,@inp_bPreClrTradesApprovalReqFlag,@inp_iPreClrSingleTransTradeNoShares,"
+ "@inp_iPreClrSingleTransTradePercPaidSubscribedCap,@inp_bPreClrProhibitNonTradePeriodFlag,@inp_iPreClrCOApprovalLimit,"
+ "@inp_iPreClrApprovalValidityLimit,@inp_bPreClrSeekDeclarationForUPSIFlag,@inp_sPreClrUPSIDeclaration,@inp_bPreClrReasonForNonTradeReqFlag,"
+ "@inp_bPreClrCompleteTradeNotDoneFlag,@inp_bPreClrPartialTradeNotDoneFlag,@inp_iPreClrTradeDiscloLimit,"
+ "@inp_iPreClrTradeDiscloShareholdLimit,@inp_bStExSubmitTradeDiscloAllTradeFlag,@inp_iStExSingMultiTransTradeFlagCodeId,"
+ "@inp_iStExMultiTradeFreq,@inp_iStExMultiTradeCalFinYear,@inp_iStExTransTradeNoShares,@inp_iStExTransTradePercPaidSubscribedCap,"
+ "@inp_iStExTransTradeShareValue,@inp_iStExTradeDiscloSubmitLimit,@inp_bDiscloInitReqFlag,@inp_iDiscloInitLimit,"
+ "@inp_dtDiscloInitDateLimit,@inp_bDiscloPeriodEndReqFlag,@inp_iDiscloPeriodEndFreq,@inp_sGenSecurityType,@inp_bGenTradingPlanTransFlag,"
+ "@inp_iGenMinHoldingLimit,@inp_iGenContraTradeNotAllowedLimit,@inp_sGenExceptionFor,@inp_iTradingPolicyStatusCodeId,"
+ "@inp_bDiscloInitSubmitToStExByCOFlag,@inp_bStExSubmitDiscloToStExByCOFlag,@inp_iDiscloPeriodEndToCOByInsdrLimit,@inp_bDiscloPeriodEndSubmitToStExByCOFlag,@inp_iDiscloPeriodEndSubmitToStExByCOLimit,"
+ "@inp_bDiscloInitReqSoftcopyFlag,@inp_bDiscloInitReqHardcopyFlag,@inp_bDiscloInitSubmitToStExByCOSoftcopyFlag,@inp_bDiscloInitSubmitToStExByCOHardcopyFlag,"
+ "@inp_iStExTradePerformDtlsSubmitToCOByInsdrLimit,@inp_bStExSubmitDiscloToStExByCOSoftcopyFlag,@inp_bStExSubmitDiscloToStExByCOHardcopyFlag,"
+ "@inp_bDiscloPeriodEndReqSoftcopyFlag,@inp_bDiscloPeriodEndReqHardcopyFlag,@inp_bDiscloPeriodEndSubmitToStExByCOSoftcopyFlag,@inp_bDiscloPeriodEndSubmitToStExByCOHardcopyFlag,"
+ "@inp_bStExSubmitDiscloToCOByInsdrFlag,@inp_bStExSubmitDiscloToCOByInsdrSoftcopyFlag,@inp_bStExSubmitDiscloToCOByInsdrHardcopyFlag,"
+ "@inp_bPreClrForAllSecuritiesFlag,@inp_bStExForAllSecuritiesFlag,"
+ "@inp_sSelectedPreClearancerequiredforTransactionValue,@inp_sSelectedPreClearanceProhibitforNonTradingforTransactionValue,"
+ "@inp_tblPreClearanceSecuritywise,@inp_tblContinousSecuritywise,"
+ "@inp_tblPreclearanceTransactionSecurityMap,@inp_bPreClrAllowNewForOpenPreclearFlag,@inp_iPreClrMultipleAboveInCodeId,"
+ "@inp_iPreClrApprovalPreclearORPreclearTradeFlag,"
+ "@inp_bPreClrTradesAutoApprovalReqFlag,@inp_iPreClrSingMultiPreClrFlagCodeId,"
+ "@inp_iGenCashAndCashlessPartialExciseOptionForContraTrade,@inp_bGenSecuritiesPriortoAcquisitionManualInputorAutoCalculate,"
+ "@inp_bTradingThresholdLimtResetFlag,@inp_nContraTradeBasedOn,@inp_sSelectedContraTradeSecuirtyType, "
+ "@inp_nUserId,@inp_SeekDeclarationFromEmpRegPossessionOfUPSIFlag,@inp_DeclarationFromInsiderAtTheTimeOfContinuousDisclosures,@inp_DeclarationToBeMandatoryFlag,@inp_DisplayDeclarationPostSubmissionOfContinuouseDisclosureFlag,"
+ "@inp_bIsPreclearanceFormForImplementingCompany,@inp_iPreclearanceWithoutPeriodEndDisclosure,@inp_PreClrApprovalReasonReqFlag,@out_nReturnValue OUTPUT,@out_nSQLErrCode OUTPUT,@out_sSQLErrMessage OUTPUT",
new
{
inp_iTradingPolicyId = m_objTradingPolicyDTO.TradingPolicyId,
inp_iTradingPolicyParentId = m_objTradingPolicyDTO.TradingPolicyParentId,
inp_iCurrentHistoryCodeId = m_objTradingPolicyDTO.CurrentHistoryCodeId,
inp_iTradingPolicyForCodeId = m_objTradingPolicyDTO.TradingPolicyForCodeId,
inp_sTradingPolicyName = m_objTradingPolicyDTO.TradingPolicyName,
inp_sTradingPolicyDescription = m_objTradingPolicyDTO.TradingPolicyDescription,
inp_dtApplicableFromDate = m_objTradingPolicyDTO.ApplicableFromDate,
inp_dtApplicableToDate = m_objTradingPolicyDTO.ApplicableToDate,
inp_bPreClrTradesApprovalReqFlag = m_objTradingPolicyDTO.PreClrTradesApprovalReqFlag,
inp_iPreClrSingleTransTradeNoShares = m_objTradingPolicyDTO.PreClrSingleTransTradeNoShares,
inp_iPreClrSingleTransTradePercPaidSubscribedCap = m_objTradingPolicyDTO.PreClrSingleTransTradePercPaidSubscribedCap,
inp_bPreClrProhibitNonTradePeriodFlag = m_objTradingPolicyDTO.PreClrProhibitNonTradePeriodFlag,
inp_iPreClrCOApprovalLimit = m_objTradingPolicyDTO.PreClrCOApprovalLimit,
inp_iPreClrApprovalValidityLimit = m_objTradingPolicyDTO.PreClrApprovalValidityLimit,
inp_bPreClrSeekDeclarationForUPSIFlag = m_objTradingPolicyDTO.PreClrSeekDeclarationForUPSIFlag,
inp_sPreClrUPSIDeclaration = m_objTradingPolicyDTO.PreClrUPSIDeclaration,
inp_bPreClrReasonForNonTradeReqFlag = m_objTradingPolicyDTO.PreClrReasonForNonTradeReqFlag,
inp_bPreClrCompleteTradeNotDoneFlag = m_objTradingPolicyDTO.PreClrCompleteTradeNotDoneFlag,
inp_bPreClrPartialTradeNotDoneFlag = m_objTradingPolicyDTO.PreClrPartialTradeNotDoneFlag,
inp_iPreClrTradeDiscloLimit = m_objTradingPolicyDTO.PreClrTradeDiscloLimit,
inp_iPreClrTradeDiscloShareholdLimit = m_objTradingPolicyDTO.PreClrTradeDiscloShareholdLimit,
inp_bStExSubmitTradeDiscloAllTradeFlag = m_objTradingPolicyDTO.StExSubmitTradeDiscloAllTradeFlag,
inp_iStExSingMultiTransTradeFlagCodeId = m_objTradingPolicyDTO.StExSingMultiTransTradeFlagCodeId,
inp_iStExMultiTradeFreq = m_objTradingPolicyDTO.StExMultiTradeFreq,
inp_iStExMultiTradeCalFinYear = m_objTradingPolicyDTO.StExMultiTradeCalFinYear,
inp_iStExTransTradeNoShares = m_objTradingPolicyDTO.StExTransTradeNoShares,
inp_iStExTransTradePercPaidSubscribedCap = m_objTradingPolicyDTO.StExTransTradePercPaidSubscribedCap,
inp_iStExTransTradeShareValue = m_objTradingPolicyDTO.StExTransTradeShareValue,
inp_iStExTradeDiscloSubmitLimit = m_objTradingPolicyDTO.StExTradeDiscloSubmitLimit,
inp_bDiscloInitReqFlag = m_objTradingPolicyDTO.DiscloInitReqFlag,
inp_iDiscloInitLimit = m_objTradingPolicyDTO.DiscloInitLimit,
inp_dtDiscloInitDateLimit = m_objTradingPolicyDTO.DiscloInitDateLimit,
inp_bDiscloPeriodEndReqFlag = m_objTradingPolicyDTO.DiscloPeriodEndReqFlag,
inp_iDiscloPeriodEndFreq = m_objTradingPolicyDTO.DiscloPeriodEndFreq,
inp_sGenSecurityType = m_objTradingPolicyDTO.GenSecurityType,
inp_bGenTradingPlanTransFlag = m_objTradingPolicyDTO.GenTradingPlanTransFlag,
inp_iGenMinHoldingLimit = m_objTradingPolicyDTO.GenMinHoldingLimit,
inp_iGenContraTradeNotAllowedLimit = m_objTradingPolicyDTO.GenContraTradeNotAllowedLimit,
inp_sGenExceptionFor = m_objTradingPolicyDTO.GenExceptionFor,
inp_iTradingPolicyStatusCodeId = m_objTradingPolicyDTO.TradingPolicyStatusCodeId,
inp_bDiscloInitSubmitToStExByCOFlag = m_objTradingPolicyDTO.DiscloInitSubmitToStExByCOFlag,
inp_bStExSubmitDiscloToStExByCOFlag = m_objTradingPolicyDTO.StExSubmitDiscloToStExByCOFlag,
inp_iDiscloPeriodEndToCOByInsdrLimit = m_objTradingPolicyDTO.DiscloPeriodEndToCOByInsdrLimit,
inp_bDiscloPeriodEndSubmitToStExByCOFlag = m_objTradingPolicyDTO.DiscloPeriodEndSubmitToStExByCOFlag,
inp_iDiscloPeriodEndSubmitToStExByCOLimit = m_objTradingPolicyDTO.DiscloPeriodEndSubmitToStExByCOLimit,
inp_bDiscloInitReqSoftcopyFlag = m_objTradingPolicyDTO.DiscloInitReqSoftcopyFlag,
inp_bDiscloInitReqHardcopyFlag = m_objTradingPolicyDTO.DiscloInitReqHardcopyFlag,
inp_bDiscloInitSubmitToStExByCOSoftcopyFlag = m_objTradingPolicyDTO.DiscloInitSubmitToStExByCOSoftcopyFlag,
inp_bDiscloInitSubmitToStExByCOHardcopyFlag = m_objTradingPolicyDTO.DiscloInitSubmitToStExByCOHardcopyFlag,
inp_iStExTradePerformDtlsSubmitToCOByInsdrLimit = m_objTradingPolicyDTO.StExTradePerformDtlsSubmitToCOByInsdrLimit,
inp_bStExSubmitDiscloToStExByCOSoftcopyFlag = m_objTradingPolicyDTO.StExSubmitDiscloToStExByCOSoftcopyFlag,
inp_bStExSubmitDiscloToStExByCOHardcopyFlag = m_objTradingPolicyDTO.StExSubmitDiscloToStExByCOHardcopyFlag,
inp_bDiscloPeriodEndReqSoftcopyFlag = m_objTradingPolicyDTO.DiscloPeriodEndReqSoftcopyFlag,
inp_bDiscloPeriodEndReqHardcopyFlag = m_objTradingPolicyDTO.DiscloPeriodEndReqHardcopyFlag,
inp_bDiscloPeriodEndSubmitToStExByCOSoftcopyFlag = m_objTradingPolicyDTO.DiscloPeriodEndSubmitToStExByCOSoftcopyFlag,
inp_bDiscloPeriodEndSubmitToStExByCOHardcopyFlag = m_objTradingPolicyDTO.DiscloPeriodEndSubmitToStExByCOHardcopyFlag,
inp_bStExSubmitDiscloToCOByInsdrFlag = m_objTradingPolicyDTO.StExSubmitDiscloToCOByInsdrFlag,
inp_bStExSubmitDiscloToCOByInsdrSoftcopyFlag = m_objTradingPolicyDTO.StExSubmitDiscloToCOByInsdrSoftcopyFlag,
inp_bStExSubmitDiscloToCOByInsdrHardcopyFlag = m_objTradingPolicyDTO.StExSubmitDiscloToCOByInsdrHardcopyFlag,
inp_bPreClrForAllSecuritiesFlag = m_objTradingPolicyDTO.PreClrForAllSecuritiesFlag,
inp_bStExForAllSecuritiesFlag = m_objTradingPolicyDTO.StExForAllSecuritiesFlag,
inp_sSelectedPreClearancerequiredforTransactionValue = m_objTradingPolicyDTO.SelectedPreClearancerequiredforTransactionValue,
inp_sSelectedPreClearanceProhibitforNonTradingforTransactionValue = m_objTradingPolicyDTO.SelectedPreClearanceProhibitforNonTradingforTransactionValue,
inp_tblPreClearanceSecuritywise = inp_tblPreclearanceSecuritywise,
inp_tblContinousSecuritywise = inp_tblContinousSecuritywise,
inp_tblPreclearanceTransactionSecurityMap = inp_tblPreclearanceTransactionSecurityMap,
inp_bPreClrAllowNewForOpenPreclearFlag = m_objTradingPolicyDTO.PreClrAllowNewForOpenPreclearFlag,
inp_iPreClrMultipleAboveInCodeId = m_objTradingPolicyDTO.PreClrMultipleAboveInCodeId,
inp_iPreClrApprovalPreclearORPreclearTradeFlag = m_objTradingPolicyDTO.PreClrApprovalPreclearORPreclearTradeFlag,
inp_bPreClrTradesAutoApprovalReqFlag = m_objTradingPolicyDTO.PreClrTradesAutoApprovalReqFlag,
inp_iPreClrSingMultiPreClrFlagCodeId = m_objTradingPolicyDTO.PreClrSingMultiPreClrFlagCodeId,
inp_iGenCashAndCashlessPartialExciseOptionForContraTrade = m_objTradingPolicyDTO.GenCashAndCashlessPartialExciseOptionForContraTrade,
inp_bGenSecuritiesPriortoAcquisitionManualInputorAutoCalculate = m_objTradingPolicyDTO.GenSecuritiesPriortoAcquisitionManualInputorAutoCalculate,
@inp_bTradingThresholdLimtResetFlag = m_objTradingPolicyDTO.TradingThresholdLimtResetFlag,
@inp_nContraTradeBasedOn = m_objTradingPolicyDTO.ContraTradeBasedOn,
inp_sSelectedContraTradeSecuirtyType = m_objTradingPolicyDTO.SelectedContraTradeSecuirtyType,
inp_nUserId = m_objTradingPolicyDTO.LoggedInUserId,
//New column added on 2-Jun-2016(YES BANK customization)
inp_SeekDeclarationFromEmpRegPossessionOfUPSIFlag = m_objTradingPolicyDTO.SeekDeclarationFromEmpRegPossessionOfUPSIFlag,
inp_DeclarationFromInsiderAtTheTimeOfContinuousDisclosures = m_objTradingPolicyDTO.DeclarationFromInsiderAtTheTimeOfContinuousDisclosures,
inp_DeclarationToBeMandatoryFlag = m_objTradingPolicyDTO.DeclarationToBeMandatoryFlag,
inp_DisplayDeclarationPostSubmissionOfContinuouseDisclosureFlag = m_objTradingPolicyDTO.DisplayDeclarationPostSubmissionOfContinuouseDisclosureFlag,
@inp_bIsPreclearanceFormForImplementingCompany = m_objTradingPolicyDTO.IsPreclearanceFormForImplementingCompany,
@inp_iPreclearanceWithoutPeriodEndDisclosure = m_objTradingPolicyDTO.PreclearanceWithoutPeriodEndDisclosure,
@inp_PreClrApprovalReasonReqFlag = m_objTradingPolicyDTO.PreClrApprovalReasonReqFlag,
out_nReturnValue = nReturnValue,
out_nSQLErrCode = nSQLErrCode,
out_sSQLErrMessage = sSQLErrMessage
}).SingleOrDefault <TradingPolicyDTO>();
#region Error Values
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);
//bReturn = false;
throw ex;
}
else
{
scope.Complete();
//bReturn = true;
}
#endregion Error Values
}
}
}
catch (Exception exp)
{
throw exp;
}
return(res);
}
/// <summary>
/// This method is used to save Group Master entries.
/// <param name="sConnectionString">DB Connection String</param>
/// <param name="m_objGroupDTO">NSEGroupDTO objects</param>
/// <returns>if save then return true else return false</returns>
public IEnumerable <NSEGroupDTO> SaveGroup(string i_sConnectionString, NSEGroupDTO m_objGroupDTO)
{
#region Paramters
int out_nReturnValue;
int out_nSQLErrCode;
string out_sSQLErrMessage;
bool bReturn = false;
PetaPoco.Database db = null;
#endregion Paramters
#region Out Paramter
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 = "";
#endregion Out Paramter
IEnumerable <NSEGroupDTO> lstGroup = null;
try
{
using (db = new PetaPoco.Database(i_sConnectionString, "System.Data.SqlClient")
{
EnableAutoSelect = false
})
{
using (var scope = db.GetTransaction())
{
lstGroup = db.Query <NSEGroupDTO>("exec st_mst_GroupSave @GroupId OUTPUT,@DownloadedDate,@SubmissionDate,@StatusCodeId,@TypeOfDownload,@DownloadStatus,@inp_iLoggedInUserId,@out_nReturnValue OUTPUT,@out_nSQLErrCode OUTPUT,@out_sSQLErrMessage OUTPUT",
new
{
GroupId = m_objGroupDTO.GroupId,
DownloadedDate = DateTime.Now,//m_objGroupDTO.DownloadedDate,
SubmissionDate = m_objGroupDTO.SubmissionDate,
StatusCodeId = 508006,
TypeOfDownload = m_objGroupDTO.TypeOfDownload,
DownloadStatus = false,
inp_iLoggedInUserId = m_objGroupDTO.LoggedInUserId,
out_nReturnValue = 1,
out_nSQLErrCode = 1,
out_sSQLErrMessage = string.Empty
}).ToList();
#region Error Values
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);
bReturn = false;
throw ex;
}
else
{
scope.Complete();
bReturn = true;
}
#endregion Error Values
return(lstGroup);
}
}
}
catch (Exception exp)
{
throw exp;
}
}
/// <summary>
/// This method is used to delete Group details
/// </summary>
/// <param name="sConnectionString">DB COnnection string</param>
/// <param name="m_objNSEGroupDetailsDTO">NSEGroupDetailsDTO object</param>
/// <returns>if delete return true else false</returns>
public bool Delete(string i_sConnectionString, NSEGroupDetailsDTO m_objNSEGroupDetailsDTO)
{
#region Paramters
int out_nReturnValue;
int out_nSQLErrCode;
string out_sSQLErrMessage;
bool bReturn = false;
PetaPoco.Database db = null;
#endregion Paramters
try
{
#region Out Paramter
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.Size = 9000000;
sSQLErrMessage.Value = "";
#endregion Out Paramter
using (db = new PetaPoco.Database(i_sConnectionString, "System.Data.SqlClient")
{
EnableAutoSelect = false
})
{
using (var scope = db.GetTransaction())
{
var res = db.Query <ImplementedCompanyDTO>("exec st_tra_DeleteGroupDetails @i_TransactionMasterId,@inp_iUserId,@out_nReturnValue OUTPUT,@out_nSQLErrCode OUTPUT,@out_sSQLErrMessage OUTPUT",
new
{
i_TransactionMasterId = m_objNSEGroupDetailsDTO.TransactionMasterId,
inp_iUserId = m_objNSEGroupDetailsDTO.LoggedInUserId,
out_nReturnValue = nReturnValue,
out_nSQLErrCode = nSQLErrCode,
out_sSQLErrMessage = sSQLErrMessage
}).ToList();
#region Error Values
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);
bReturn = false;
throw ex;
}
else
{
scope.Complete();
bReturn = true;
}
#endregion Error Values
}
}
}
catch (Exception exp)
{
bReturn = false;
throw exp;
}
return(bReturn);
}
public bool SaveNSEDocument(string i_sConnectionString, NSEGroupDocumentMappingDTO m_objGroupDocumentDTO, string GUID)
{
#region Paramters
int out_nReturnValue;
int out_nSQLErrCode;
string out_sSQLErrMessage;
bool bReturn = false;
PetaPoco.Database db = null;
#endregion Paramters
try
{
#region Out Paramter
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 = "";
#endregion Out Paramter
using (db = new PetaPoco.Database(i_sConnectionString, "System.Data.SqlClient")
{
EnableAutoSelect = false
})
{
using (var scope = db.GetTransaction())
{
var res = db.Query <NSEGroupDTO>("exec st_tra_SaveNSEDocument @NSEGroupDetailsId, @inp_iDocumentDetailsID,@inp_iDocumentObjctMapId,@inp_sGUID, @inp_iLoggedInUserId,@out_nReturnValue OUTPUT,@out_nSQLErrCode OUTPUT,@out_sSQLErrMessage OUTPUT",
new
{
NSEGroupDetailsId = m_objGroupDocumentDTO.NSEGroupDetailsId,
inp_iDocumentDetailsID = 0,
@inp_sGUID = GUID,
inp_iDocumentObjctMapId = m_objGroupDocumentDTO.DocumentObjectMapId,
inp_iLoggedInUserId = m_objGroupDocumentDTO.LoggedInUserId,
out_nReturnValue = 1,
out_nSQLErrCode = 1,
out_sSQLErrMessage = string.Empty
}).SingleOrDefault <NSEGroupDTO>();
#region Error Values
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);
bReturn = false;
throw ex;
}
else
{
scope.Complete();
bReturn = true;
}
return(bReturn);
#endregion Error Values
}
}
}
catch (Exception exp)
{
throw exp;
}
}