public static List <Template> getListEmailTemplate()
{
SqlConnection con = new clsSystem_DBConnection(clsSystem_DBConnection.strConnectionString.NavIntegrationDB).propConnection;
SqlCommand cmd = new SqlCommand();
SqlDataReader dr;
con.Open();
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "SWITCH_EmailTemplateGetAll";
dr = cmd.ExecuteReader();
List <Template> newListTemplate = new List <Template>();
while (dr.Read())
{
Template newTemplate = new Template(int.Parse(dr["EmailTemplateID"].ToString()), null);
newListTemplate.Add(newTemplate);
}
con.Close();
cmd.Dispose();
return(newListTemplate);
}
public static float getCurrencyMultiplier(string strClientID, string strCurrencyToConvert)
{
SqlConnection con = new clsSystem_DBConnection(clsSystem_DBConnection.strConnectionString.NavIntegrationDB).propConnection;
float fConvertedValue = 0;
SqlCommand cmd = new SqlCommand();
SqlDataReader dr;
con.Open();
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "[Switch_CurrencyMultiplier]";
cmd.Parameters.Add("@param_strClientID", System.Data.SqlDbType.NVarChar).Value = strClientID;
cmd.Parameters.Add("@param_strFundCurrency", System.Data.SqlDbType.NVarChar).Value = strCurrencyToConvert;
dr = cmd.ExecuteReader();
while (dr.Read())
{
fConvertedValue = dr["CurrencyMultiplier"] != null ? float.Parse(dr["CurrencyMultiplier"].ToString()) : 0;
}
dr.Close();
con.Close();
cmd.Dispose();
con.Dispose();
return fConvertedValue;
}
public static void doBulkSwitch()
{
SqlConnection con = new clsSystem_DBConnection(clsSystem_DBConnection.strConnectionString.NavIntegrationDB).propConnection;
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "[SWITCH_BulkInsert]";
cmd.ExecuteNonQuery();
con.Close();
}
public static string getMobileNumber(string strClientID)
{
SqlConnection con = new clsSystem_DBConnection(clsSystem_DBConnection.strConnectionString.NavIntegrationDB).propConnection;
SqlCommand cmd = new SqlCommand();
con.Open();
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = string.Format("SELECT [Mobile] FROM [NavGlobalDBwwwGUID].[dbo].[ClientWebDetails] WHERE ClientID = '{0}'", strClientID);
string strMobileNumber = cmd.ExecuteScalar().ToString();
con.Close();
cmd.Dispose();
return(strMobileNumber);
}
public static void deleteSwitchFee(int intIFA_ID)
{
SqlConnection con = new clsSystem_DBConnection(clsSystem_DBConnection.strConnectionString.NavIntegrationDB).propConnection;
SqlCommand cmd = new SqlCommand();
con.Open();
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "SWITCH_FeeDelete";
cmd.Parameters.Add("@param_intIFA_ID", System.Data.SqlDbType.Int).Value = intIFA_ID;
cmd.ExecuteNonQuery();
cmd.Dispose();
con.Close();
con.Dispose();
}
public static List <clsSwitch> getSwitchList(int[] intSwitches)
{
List <clsSwitch> oSwitchList = new List <clsSwitch>();
SqlConnection con = new clsSystem_DBConnection(clsSystem_DBConnection.strConnectionString.NavIntegrationDB).propConnection;
con.Open();
for (int i = 0; i < intSwitches.Length; i++)
{
SqlCommand cmd = new SqlCommand();
SqlDataReader dr;
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "[SWITCH_HeaderGet]";
cmd.Parameters.Add("@param_intSwitchID", System.Data.SqlDbType.Int).Value = intSwitches[i];
dr = cmd.ExecuteReader();
while (dr.Read())
{
clsSwitch oSwitch = new clsSwitch();
oSwitch.propSwitchID = int.Parse(dr["SwitchID"].ToString());
oSwitch.propPortfolioID = dr["PortfolioID"].ToString();
oSwitch.propClientID = dr["ClientID"].ToString();
oSwitch.propStatus = short.Parse(dr["Status"].ToString());
oSwitch.propStatusString = clsSwitch.getSwitchStringStatus(oSwitch.propStatus);
oSwitch.propDate_Created = dr["Date_Created"] != System.DBNull.Value ? DateTime.Parse(dr["Date_Created"].ToString()) : DateTime.ParseExact("01/01/1800", "dd/MM/yyyy", null);
oSwitch.propCreated_By = dr["Created_By"].ToString();
oSwitch.propDescription = dr["Description"].ToString();
oSwitch.propSwitchClient = new clsClient(oSwitch.propClientID);
oSwitch.propPortfolio = new clsPortfolio(oSwitch.propClientID, oSwitch.propPortfolioID);
oSwitchList.Add(oSwitch);
}
dr.Close();
dr.Dispose();
cmd.Dispose();
}
con.Close();
con.Dispose();
return(oSwitchList);
}
public static string getSMSMessage(int intSMSID)
{
string resultMessage = string.Empty;
SqlConnection con = new clsSystem_DBConnection(clsSystem_DBConnection.strConnectionString.NavIntegrationDB).propConnection;
SqlCommand cmd = new SqlCommand();
con.Open();
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = string.Format("SELECT SMS_Message FROM NavIntegrationDB.dbo.SwitchSMSMessage WHERE SMS_ID = {0}", intSMSID);
resultMessage = cmd.ExecuteScalar().ToString();
con.Close();
cmd.Dispose();
return(resultMessage);
}
public static List <clsSwitch> getSwitchList(int intIFA_ID, string strClientName, string strCompany, int intStatus, string strStartDate, string strEndDate)
{
List <clsSwitch> oSwitchList = new List <clsSwitch>();
SqlConnection con = new clsSystem_DBConnection(clsSystem_DBConnection.strConnectionString.NavIntegrationDB).propConnection;
SqlCommand cmd = new SqlCommand();
SqlDataReader dr;
con.Open();
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "[SWITCH_HeaderGetAllByIFA]";
cmd.Parameters.Add("@param_IFA_ID", System.Data.SqlDbType.Int).Value = intIFA_ID;
cmd.Parameters.Add("@param_ClientName", System.Data.SqlDbType.NVarChar).Value = strClientName;
cmd.Parameters.Add("@param_Company", System.Data.SqlDbType.NVarChar).Value = strCompany;
cmd.Parameters.Add("@param_Status", System.Data.SqlDbType.Int).Value = intStatus;
cmd.Parameters.Add("@param_StartDate", System.Data.SqlDbType.NVarChar).Value = strStartDate;
cmd.Parameters.Add("@param_EndDate", System.Data.SqlDbType.NVarChar).Value = strEndDate;
dr = cmd.ExecuteReader();
while (dr.Read())
{
clsSwitch oSwitch = new clsSwitch();
oSwitch.propSwitchID = int.Parse(dr["SwitchID"].ToString());
oSwitch.propPortfolioID = dr["PortfolioID"].ToString();
oSwitch.propClientID = dr["ClientID"].ToString();
oSwitch.propStatus = short.Parse(dr["Status"].ToString());
oSwitch.propStatusString = clsSwitch.getSwitchStringStatus(oSwitch.propStatus);
oSwitch.propDate_Created = dr["Date_Created"] != System.DBNull.Value ? DateTime.Parse(dr["Date_Created"].ToString()) : DateTime.ParseExact("01/01/1800", "dd/MM/yyyy", null);
oSwitch.propCreated_By = dr["Created_By"].ToString();
oSwitch.propDescription = dr["Description"].ToString();
oSwitch.propSwitchClient = new clsClient(oSwitch.propClientID);
oSwitch.propPortfolio = new clsPortfolio(oSwitch.propClientID, oSwitch.propPortfolioID);
oSwitchList.Add(oSwitch);
}
con.Close();
cmd.Dispose();
con.Dispose();
return(oSwitchList);
}
public static void removeSwitchDetails(int intSwitchDetailsID)
{
SqlConnection con = new clsSystem_DBConnection(clsSystem_DBConnection.strConnectionString.NavIntegrationDB).propConnection;
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "[SWITCHclient_DetailsDelete]";
cmd.Parameters.Add("@param_intSwitchDetailsID", System.Data.SqlDbType.Int).Value = intSwitchDetailsID;
cmd.ExecuteNonQuery();
con.Close();
}
public int insertEncryptedCode(int intSwitchID, string strClientID, string strPortfolioID)
{
SqlConnection con = new clsSystem_DBConnection(clsSystem_DBConnection.strConnectionString.NavIntegrationDB).propConnection;
SqlCommand cmd = new SqlCommand();
con.Open();
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "[SWITCH_GenerateCodeInsert]";
cmd.Parameters.Add("@param_Code", System.Data.SqlDbType.NVarChar).Value = encryptedCode;
cmd.Parameters.Add("@param_SwitchID", System.Data.SqlDbType.Int).Value = intSwitchID;
cmd.Parameters.Add("@param_ClientID", System.Data.SqlDbType.NVarChar).Value = strClientID;
cmd.Parameters.Add("@param_PortfolioID", System.Data.SqlDbType.NVarChar).Value = strPortfolioID;
int result = (int)cmd.ExecuteScalar();
con.Close();
cmd.Dispose();
return result;
}
public static List <clsContribution> getContributions(clsScheme Scheme)
{
SqlConnection con1 = new clsSystem_DBConnection(clsSystem_DBConnection.strConnectionString.NavIntegrationDB).propConnection;
List <clsContribution> listContribution = new List <clsContribution>();
SqlCommand cmd = new SqlCommand();
SqlDataReader dr;
con1.Open();
cmd.Connection = con1;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "[SWITCHScheme_SchemeContributionsGet]";
cmd.Parameters.Add("@param_SchemeID", System.Data.SqlDbType.NVarChar).Value = Scheme.propSchemeID;
dr = cmd.ExecuteReader();
while (dr.Read())
{
clsContribution newContribution = new clsContribution();
newContribution.propClient = new clsClient(dr["ClientID"].ToString());
newContribution.propContributionAmount = float.Parse(dr["ContributionAmount"].ToString());
newContribution.propContributionID = dr["ContributionID"].ToString();
newContribution.propEndDate = dr["EndDate"] != System.DBNull.Value ? DateTime.Parse(dr["EndDate"].ToString()) : DateTime.ParseExact("01/01/1800", "dd/MM/yyyy", null);
newContribution.propIFAUpdatedBy = dr["IFAUpdatedBy"].ToString();
newContribution.propIFAUpdatedDate = dr["IFAUpdatedDate"] != System.DBNull.Value ? DateTime.Parse(dr["IFAUpdatedDate"].ToString()) : DateTime.ParseExact("01/01/1800", "dd/MM/yyyy", null);
newContribution.propScheme = Scheme;
newContribution.propSchemeContributionsUpdatedBy = dr["SchemeContributionsUpdatedBy"].ToString();
newContribution.propSchemeContributionsUpdatedDate = dr["SchemeContributionsUpdatedDate"] != System.DBNull.Value ? DateTime.Parse(dr["SchemeContributionsUpdatedDate"].ToString()) : DateTime.ParseExact("01/01/1800", "dd/MM/yyyy", null);
newContribution.propStartDate = dr["StartDate"] != System.DBNull.Value ? DateTime.Parse(dr["StartDate"].ToString()) : DateTime.ParseExact("01/01/1800", "dd/MM/yyyy", null);
newContribution.propValuationFrequency = dr["ValuationFrequency"].ToString();
listContribution.Add(newContribution);
}
con1.Close();
cmd.Dispose();
con1.Dispose();
return(listContribution);
}
public int insertEncryptedCode(int intSwitchID, string strClientID, string strPortfolioID)
{
SqlConnection con = new clsSystem_DBConnection(clsSystem_DBConnection.strConnectionString.NavIntegrationDB).propConnection;
SqlCommand cmd = new SqlCommand();
con.Open();
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "[SWITCH_GenerateCodeInsert]";
cmd.Parameters.Add("@param_Code", System.Data.SqlDbType.NVarChar).Value = encryptedCode;
cmd.Parameters.Add("@param_SwitchID", System.Data.SqlDbType.Int).Value = intSwitchID;
cmd.Parameters.Add("@param_ClientID", System.Data.SqlDbType.NVarChar).Value = strClientID;
cmd.Parameters.Add("@param_PortfolioID", System.Data.SqlDbType.NVarChar).Value = strPortfolioID;
int result = (int)cmd.ExecuteScalar();
con.Close();
cmd.Dispose();
return(result);
}
public static void deleteSwitchDetailsTemp(string strClientID, string strPortfolioID)
{
SqlConnection con = new clsSystem_DBConnection(clsSystem_DBConnection.strConnectionString.NavIntegrationDB).propConnection;
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "[SWITCH_Temp_DetailsDelete]";
cmd.Parameters.Add("@param_strClientID", System.Data.SqlDbType.NVarChar).Value = strClientID;
cmd.Parameters.Add("@param_strPortfolioID", System.Data.SqlDbType.NVarChar).Value = strPortfolioID;
cmd.ExecuteNonQuery();
con.Close();
}
public string ValidateSecurityCode(int intSwitchID, string strClientID, string strPortfolioID)
{
string strMessage = string.Empty;
SqlConnection con = new clsSystem_DBConnection(clsSystem_DBConnection.strConnectionString.NavIntegrationDB).propConnection;
SqlCommand cmd = new SqlCommand();
con.Open();
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "[SWITCH_CheckSecurityCodeValid]";
cmd.Parameters.Add("@param_Code", System.Data.SqlDbType.NVarChar).Value = encryptedCode;
cmd.Parameters.Add("@param_SwitchID", System.Data.SqlDbType.Int).Value = intSwitchID;
cmd.Parameters.Add("@param_ClientID", System.Data.SqlDbType.NVarChar).Value = strClientID;
cmd.Parameters.Add("@param_PortfolioID", System.Data.SqlDbType.NVarChar).Value = strPortfolioID;
strMessage = cmd.ExecuteScalar().ToString();
con.Close();
cmd.Dispose();
return(strMessage);
}
public static void saveSwitchFee(int intIFA_ID, string strIFA_Name, decimal dAnnual_Fee, decimal dPerSwitch_Fee, bool bAccessDenied)
{
SqlConnection con = new clsSystem_DBConnection(clsSystem_DBConnection.strConnectionString.NavIntegrationDB).propConnection;
SqlCommand cmd = new SqlCommand();
con.Open();
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "SWITCH_FeeSave";
cmd.Parameters.Add("@param_intIFA_ID", System.Data.SqlDbType.Int).Value = intIFA_ID;
cmd.Parameters.Add("@param_IFA_Username", System.Data.SqlDbType.NVarChar).Value = strIFA_Name;
cmd.Parameters.Add("@param_Annual_Fee", System.Data.SqlDbType.Decimal).Value = dAnnual_Fee;
cmd.Parameters.Add("@param_Per_Switch_Fee", System.Data.SqlDbType.Decimal).Value = dPerSwitch_Fee;
cmd.Parameters.Add("param_Access_Denied", System.Data.SqlDbType.Bit).Value = bAccessDenied;
cmd.ExecuteNonQuery();
cmd.Dispose();
con.Close();
con.Dispose();
}
public static List <clsFund> getAllFunds(String strFundName)
{
SqlConnection con = new clsSystem_DBConnection(clsSystem_DBConnection.strConnectionString.NavIntegrationDB).propConnection;
List <clsFund> listFund = new List <clsFund>();
SqlCommand cmd = new SqlCommand();
SqlDataReader dr;
con.Open();
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "[SWITCH_FundGetAll]";
cmd.Parameters.Add("@param_strFundName", System.Data.SqlDbType.NVarChar).Value = strFundName;
dr = cmd.ExecuteReader();
while (dr.Read())
{
clsFund newFund = new clsFund();
newFund.propCompanyID = dr["CompanyID"] == null?int.Parse(dr["CompanyID"].ToString()) : 0;
newFund.propCurrency = dr["Currency"].ToString();
newFund.propDatePriceUpdated = dr["DatePriceUpdated"] != System.DBNull.Value ? DateTime.Parse(dr["DatePriceUpdated"].ToString()) : DateTime.ParseExact("01/01/1800", "dd/MM/yyyy", null);
newFund.propFundID = int.Parse(dr["FundNameID"].ToString());
newFund.propFundManager = int.Parse(dr["FundManager"].ToString());
newFund.propFundName = dr["FundName"].ToString();
newFund.propPrice = dr["Price"] != System.DBNull.Value ? float.Parse(dr["Price"].ToString()) : 0f;
listFund.Add(newFund);
}
dr.Close();
con.Close();
cmd.Dispose();
con.Dispose();
return(listFund);
}
//public static void insertSwitchDetails(List<clsSwitchDetails> listSwitchDetails, string strUserID, int intSwitchID)
//{
// SqlConnection con = new clsSystem_DBConnection(clsSystem_DBConnection.strConnectionString.NavIntegrationDB).propConnection;
// con.Open();
// foreach (clsSwitchDetails SwitchDetail in listSwitchDetails)
// {
// SqlCommand cmd = new SqlCommand();
// cmd.Connection = con;
// cmd.CommandType = System.Data.CommandType.StoredProcedure;
// cmd.CommandText = "[SWITCH_DetailsInsert]";
// cmd.Parameters.Add("@param_intSwitchID", System.Data.SqlDbType.Int).Value = intSwitchID;
// cmd.Parameters.Add("@param_intFundID", System.Data.SqlDbType.Int).Value = SwitchDetail.propFund.propFundID;
// cmd.Parameters.Add("@param_fAllocation", System.Data.SqlDbType.Float).Value = SwitchDetail.propAllocation;
// cmd.Parameters.Add("@param_strCreated_By", System.Data.SqlDbType.NVarChar).Value = strUserID;
// cmd.Parameters.Add("@param_strUpdated_By", System.Data.SqlDbType.NVarChar).Value = strUserID;
// cmd.Parameters.Add("@param_intSwitchDetailsID", System.Data.SqlDbType.Int).Value = SwitchDetail.intSwitchDetailsID;
// cmd.Parameters.Add("@param_sintIsDeletable", System.Data.SqlDbType.SmallInt).Value = SwitchDetail.propIsDeletable == true ? 1 : 0;
// cmd.ExecuteNonQuery();
// }
//}
public static void deleteModelPortfolioDetails(int intIFA_ID, int intModelID, string strModelGroupID, string strModelPortfolioID)
{
SqlConnection con = new clsSystem_DBConnection(clsSystem_DBConnection.strConnectionString.NavIntegrationDB).propConnection;
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "[SWITCH_ModelPortfolioDetailsDelete]";
cmd.Parameters.Add("@param_IFA_ID", System.Data.SqlDbType.Int).Value = intIFA_ID;
cmd.Parameters.Add("@param_ModelID", System.Data.SqlDbType.Int).Value = intModelID;
cmd.Parameters.Add("@param_ModelGroupID", System.Data.SqlDbType.NVarChar).Value = strModelGroupID;
cmd.Parameters.Add("@param_ModelPortfolioID", System.Data.SqlDbType.NVarChar).Value = strModelPortfolioID;
cmd.ExecuteNonQuery();
con.Close();
}
public static List <clsSwitchFee> getSwitchFeeList()
{
List <clsSwitchFee> oSwitchFeeList = new List <clsSwitchFee>();
SqlConnection con = new clsSystem_DBConnection(clsSystem_DBConnection.strConnectionString.NavIntegrationDB).propConnection;
SqlCommand cmd = new SqlCommand();
SqlDataReader dr;
con.Open();
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "SWITCH_FeeGet";
cmd.Parameters.Add("@param_intIFA_ID", System.Data.SqlDbType.Int).Value = 0;
dr = cmd.ExecuteReader();
while (dr.Read())
{
clsSwitchFee oSwitchFee = new clsSwitchFee();
oSwitchFee.propIFA_ID = dr["IFA_ID"] == System.DBNull.Value ? 0 : int.Parse(dr["IFA_ID"].ToString());
oSwitchFee.propIFA_Name = dr["IFA_Username"] == System.DBNull.Value ? string.Empty : dr["IFA_Username"].ToString();
oSwitchFee.propAnnual_Fee = dr["Annual_Fee"] == System.DBNull.Value ? 0 : decimal.Parse(dr["Annual_Fee"].ToString());
oSwitchFee.propPer_Switch_Fee = dr["Per_Switch_Fee"] == System.DBNull.Value ? 0 : decimal.Parse(dr["Per_Switch_Fee"].ToString());
oSwitchFee.propAccess_Denied = dr["Access_Denied"] == System.DBNull.Value ? false : bool.Parse(dr["Access_Denied"].ToString());
oSwitchFeeList.Add(oSwitchFee);
}
dr.Close();
cmd.Dispose();
con.Close();
con.Dispose();
return(oSwitchFeeList);
}
private static List <subclsSMSTemplate> getListSMSTemplate()
{
SqlConnection con = new clsSystem_DBConnection(clsSystem_DBConnection.strConnectionString.NavIntegrationDB).propConnection;
List <subclsSMSTemplate> listSMSTemplate = new List <subclsSMSTemplate>();
SqlCommand cmd = new SqlCommand();
SqlDataReader dr;
con.Open();
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "[SWITCH_SMSTemplateGetAll]";
dr = cmd.ExecuteReader();
while (dr.Read())
{
subclsSMSTemplate SMSTemplate = new subclsSMSTemplate();
SMSTemplate.intSMSTemplateID = short.Parse(dr["SMSTemplateID"].ToString());
SMSTemplate.strTemplateName = dr["TemplateName"].ToString();
SMSTemplate.strTemplateFor = dr["TemplateFor"].ToString();
SMSTemplate.strMessage = dr["Message"].ToString();
listSMSTemplate.Add(SMSTemplate);
}
dr.Close();
con.Close();
cmd.Dispose();
con.Dispose();
return(listSMSTemplate);
}
public static List<clsCompany> getCompanyInsurance()
{
List<clsCompany> lstInsurance = new List<clsCompany>();
SqlConnection con = new clsSystem_DBConnection(clsSystem_DBConnection.strConnectionString.NavIntegrationDB).propConnection;
SqlDataReader dr;
SqlCommand cmd = new SqlCommand();
con.Open();
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "SWITCH_SignedConfirmationGetAll";
dr = cmd.ExecuteReader();
while (dr.Read())
{
lstInsurance.Add(new clsCompany((int)dr["CompanyID"]) { propSignedConfirmation = (bool)dr["IsRequired"] });
}
con.Close();
cmd.Dispose();
con.Dispose();
return lstInsurance;
}
//public static void insertSwitchDetails(List<clsSwitchDetails> listSwitchDetails, string strUserID, int intSwitchID)
//{
// SqlConnection con = new clsSystem_DBConnection(clsSystem_DBConnection.strConnectionString.NavIntegrationDB).propConnection;
// con.Open();
// foreach (clsSwitchDetails SwitchDetail in listSwitchDetails)
// {
// SqlCommand cmd = new SqlCommand();
// cmd.Connection = con;
// cmd.CommandType = System.Data.CommandType.StoredProcedure;
// cmd.CommandText = "[SWITCH_DetailsInsert]";
// cmd.Parameters.Add("@param_intSwitchID", System.Data.SqlDbType.Int).Value = intSwitchID;
// cmd.Parameters.Add("@param_intFundID", System.Data.SqlDbType.Int).Value = SwitchDetail.propFund.propFundID;
// cmd.Parameters.Add("@param_fAllocation", System.Data.SqlDbType.Float).Value = SwitchDetail.propAllocation;
// cmd.Parameters.Add("@param_strCreated_By", System.Data.SqlDbType.NVarChar).Value = strUserID;
// cmd.Parameters.Add("@param_strUpdated_By", System.Data.SqlDbType.NVarChar).Value = strUserID;
// cmd.Parameters.Add("@param_intSwitchDetailsID", System.Data.SqlDbType.Int).Value = SwitchDetail.intSwitchDetailsID;
// cmd.Parameters.Add("@param_sintIsDeletable", System.Data.SqlDbType.SmallInt).Value = SwitchDetail.propIsDeletable == true ? 1 : 0;
// cmd.ExecuteNonQuery();
// }
//}
public static void deleteModelPortfolioDetails(int intIFA_ID, int intModelID, string strModelGroupID, string strModelPortfolioID)
{
SqlConnection con = new clsSystem_DBConnection(clsSystem_DBConnection.strConnectionString.NavIntegrationDB).propConnection;
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "[SWITCH_ModelPortfolioDetailsDelete]";
cmd.Parameters.Add("@param_IFA_ID", System.Data.SqlDbType.Int).Value = intIFA_ID;
cmd.Parameters.Add("@param_ModelID", System.Data.SqlDbType.Int).Value = intModelID;
cmd.Parameters.Add("@param_ModelGroupID", System.Data.SqlDbType.NVarChar).Value = strModelGroupID;
cmd.Parameters.Add("@param_ModelPortfolioID", System.Data.SqlDbType.NVarChar).Value = strModelPortfolioID;
cmd.ExecuteNonQuery();
con.Close();
}
public static List<clsModelPortfolioDetails> getModelPortfolioDetails(clsPortfolio Portfolio, int intModelID, string strModelGroupID, string strModolPortfolioID)
{
SqlConnection con = new clsSystem_DBConnection(clsSystem_DBConnection.strConnectionString.NavIntegrationDB).propConnection;
List<clsModelPortfolioDetails> listModelPortfolioDetails = new List<clsModelPortfolioDetails>();
SqlCommand cmd = new SqlCommand();
SqlDataReader dr1;
//con1.Open();
con.Open();
cmd.Connection = con;//con1;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "[SWITCH_ModelPortfolioDetailsGet]";
cmd.Parameters.Add("@param_ModelID", System.Data.SqlDbType.Int).Value = intModelID;
cmd.Parameters.Add("@param_ModelGroupID", System.Data.SqlDbType.NVarChar).Value = strModelGroupID;
cmd.Parameters.Add("@param_ModelPortfolioID", System.Data.SqlDbType.NVarChar).Value = strModolPortfolioID;
dr1 = cmd.ExecuteReader();
float fTotalAllocation = 0;
while (dr1.Read())
{
clsModelPortfolioDetails ModelPortfolioDetails = new clsModelPortfolioDetails();
ModelPortfolioDetails.propModelID = int.Parse(dr1["ModelID"].ToString());
ModelPortfolioDetails.propAllocation = float.Parse(Math.Round(double.Parse(dr1["Allocation"].ToString()), 2).ToString());
ModelPortfolioDetails.propFund = new clsFund(int.Parse(dr1["FundID"].ToString()));
ModelPortfolioDetails.propFundID = int.Parse(dr1["FundID"].ToString());
ModelPortfolioDetails.propIsDeletable = dr1["isDeletable"].ToString().Equals("1") ? true : false;
if (Portfolio.propPortfolioDetails[0].propClientCurrency != ModelPortfolioDetails.propFund.propCurrency)
{
ModelPortfolioDetails.propUnits = clsSwitchDetails.computeUnits(ModelPortfolioDetails.propAllocation,
float.Parse(Math.Round(double.Parse(Portfolio.propPortfolioDetails[0].propTotalCurrentValueClient.ToString()), 0).ToString()),
clsCurrency.convertToClientCurrency(strModelGroupID, ModelPortfolioDetails.propFund.propPrice, ModelPortfolioDetails.propFund.propCurrency));
}
else
{
ModelPortfolioDetails.propUnits = clsSwitchDetails.computeUnits(ModelPortfolioDetails.propAllocation,
float.Parse(Math.Round(double.Parse(Portfolio.propPortfolioDetails[0].propTotalCurrentValueClient.ToString()), 0).ToString()),
ModelPortfolioDetails.propFund.propPrice);
}
ModelPortfolioDetails.propCurrencyMultiplier = clsCurrency.getCurrencyMultiplier(strModelGroupID, ModelPortfolioDetails.propFund.propCurrency);
ModelPortfolioDetails.propTotalValue = float.Parse(Math.Round(double.Parse(Portfolio.propPortfolioDetails[0].propTotalCurrentValueClient.ToString()), 0).ToString());
ModelPortfolioDetails.propValue = clsSwitchDetails.computeValue(ModelPortfolioDetails.propAllocation, ModelPortfolioDetails.propTotalValue);
fTotalAllocation = fTotalAllocation + ModelPortfolioDetails.propAllocation;
ModelPortfolioDetails.propTotalAllocation = fTotalAllocation;
listModelPortfolioDetails.Add(ModelPortfolioDetails);
}
//con1.Close();
con.Close();
cmd.Dispose();
//con1.Dispose();
con.Dispose();
return listModelPortfolioDetails;
}
public static List <clsModelPortfolioDetails> getModelPortfolioDetails(clsPortfolio Portfolio, int intModelID, string strModelGroupID, string strModolPortfolioID)
{
SqlConnection con = new clsSystem_DBConnection(clsSystem_DBConnection.strConnectionString.NavIntegrationDB).propConnection;
List <clsModelPortfolioDetails> listModelPortfolioDetails = new List <clsModelPortfolioDetails>();
SqlCommand cmd = new SqlCommand();
SqlDataReader dr1;
//con1.Open();
con.Open();
cmd.Connection = con;//con1;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "[SWITCH_ModelPortfolioDetailsGet]";
cmd.Parameters.Add("@param_ModelID", System.Data.SqlDbType.Int).Value = intModelID;
cmd.Parameters.Add("@param_ModelGroupID", System.Data.SqlDbType.NVarChar).Value = strModelGroupID;
cmd.Parameters.Add("@param_ModelPortfolioID", System.Data.SqlDbType.NVarChar).Value = strModolPortfolioID;
dr1 = cmd.ExecuteReader();
float fTotalAllocation = 0;
while (dr1.Read())
{
clsModelPortfolioDetails ModelPortfolioDetails = new clsModelPortfolioDetails();
ModelPortfolioDetails.propModelID = int.Parse(dr1["ModelID"].ToString());
ModelPortfolioDetails.propAllocation = float.Parse(Math.Round(double.Parse(dr1["Allocation"].ToString()), 2).ToString());
ModelPortfolioDetails.propFund = new clsFund(int.Parse(dr1["FundID"].ToString()));
ModelPortfolioDetails.propFundID = int.Parse(dr1["FundID"].ToString());
ModelPortfolioDetails.propIsDeletable = dr1["isDeletable"].ToString().Equals("1") ? true : false;
if (Portfolio.propPortfolioDetails[0].propClientCurrency != ModelPortfolioDetails.propFund.propCurrency)
{
ModelPortfolioDetails.propUnits = clsSwitchDetails.computeUnits(ModelPortfolioDetails.propAllocation,
float.Parse(Math.Round(double.Parse(Portfolio.propPortfolioDetails[0].propTotalCurrentValueClient.ToString()), 0).ToString()),
clsCurrency.convertToClientCurrency(strModelGroupID, ModelPortfolioDetails.propFund.propPrice, ModelPortfolioDetails.propFund.propCurrency));
}
else
{
ModelPortfolioDetails.propUnits = clsSwitchDetails.computeUnits(ModelPortfolioDetails.propAllocation,
float.Parse(Math.Round(double.Parse(Portfolio.propPortfolioDetails[0].propTotalCurrentValueClient.ToString()), 0).ToString()),
ModelPortfolioDetails.propFund.propPrice);
}
ModelPortfolioDetails.propCurrencyMultiplier = clsCurrency.getCurrencyMultiplier(strModelGroupID, ModelPortfolioDetails.propFund.propCurrency);
ModelPortfolioDetails.propTotalValue = float.Parse(Math.Round(double.Parse(Portfolio.propPortfolioDetails[0].propTotalCurrentValueClient.ToString()), 0).ToString());
ModelPortfolioDetails.propValue = clsSwitchDetails.computeValue(ModelPortfolioDetails.propAllocation, ModelPortfolioDetails.propTotalValue);
fTotalAllocation = fTotalAllocation + ModelPortfolioDetails.propAllocation;
ModelPortfolioDetails.propTotalAllocation = fTotalAllocation;
listModelPortfolioDetails.Add(ModelPortfolioDetails);
}
//con1.Close();
con.Close();
cmd.Dispose();
//con1.Dispose();
con.Dispose();
return(listModelPortfolioDetails);
}
public static List<clsContribution> getContributions(clsScheme Scheme) {
SqlConnection con1 = new clsSystem_DBConnection(clsSystem_DBConnection.strConnectionString.NavIntegrationDB).propConnection;
List<clsContribution> listContribution = new List<clsContribution>();
SqlCommand cmd = new SqlCommand();
SqlDataReader dr;
con1.Open();
cmd.Connection = con1;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "[SWITCHScheme_SchemeContributionsGet]";
cmd.Parameters.Add("@param_SchemeID", System.Data.SqlDbType.NVarChar).Value = Scheme.propSchemeID;
dr = cmd.ExecuteReader();
while (dr.Read())
{
clsContribution newContribution = new clsContribution();
newContribution.propClient = new clsClient(dr["ClientID"].ToString());
newContribution.propContributionAmount = float.Parse(dr["ContributionAmount"].ToString());
newContribution.propContributionID = dr["ContributionID"].ToString();
newContribution.propEndDate = dr["EndDate"] != System.DBNull.Value ? DateTime.Parse(dr["EndDate"].ToString()) : DateTime.ParseExact("01/01/1800", "dd/MM/yyyy", null);
newContribution.propIFAUpdatedBy = dr["IFAUpdatedBy"].ToString();
newContribution.propIFAUpdatedDate = dr["IFAUpdatedDate"] != System.DBNull.Value ? DateTime.Parse(dr["IFAUpdatedDate"].ToString()) : DateTime.ParseExact("01/01/1800", "dd/MM/yyyy", null);
newContribution.propScheme = Scheme;
newContribution.propSchemeContributionsUpdatedBy = dr["SchemeContributionsUpdatedBy"].ToString();
newContribution.propSchemeContributionsUpdatedDate = dr["SchemeContributionsUpdatedDate"] != System.DBNull.Value ? DateTime.Parse(dr["SchemeContributionsUpdatedDate"].ToString()) : DateTime.ParseExact("01/01/1800", "dd/MM/yyyy", null);
newContribution.propStartDate = dr["StartDate"] != System.DBNull.Value ? DateTime.Parse(dr["StartDate"].ToString()) : DateTime.ParseExact("01/01/1800", "dd/MM/yyyy", null);
newContribution.propValuationFrequency = dr["ValuationFrequency"].ToString();
listContribution.Add(newContribution);
}
con1.Close();
cmd.Dispose();
con1.Dispose();
return listContribution;
}
public static List<clsSwitchFee> getSwitchFeeList()
{
List<clsSwitchFee> oSwitchFeeList = new List<clsSwitchFee>();
SqlConnection con = new clsSystem_DBConnection(clsSystem_DBConnection.strConnectionString.NavIntegrationDB).propConnection;
SqlCommand cmd = new SqlCommand();
SqlDataReader dr;
con.Open();
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "SWITCH_FeeGet";
cmd.Parameters.Add("@param_intIFA_ID", System.Data.SqlDbType.Int).Value = 0;
dr = cmd.ExecuteReader();
while (dr.Read())
{
clsSwitchFee oSwitchFee = new clsSwitchFee();
oSwitchFee.propIFA_ID = dr["IFA_ID"] == System.DBNull.Value ? 0 : int.Parse(dr["IFA_ID"].ToString());
oSwitchFee.propIFA_Name = dr["IFA_Username"] == System.DBNull.Value ? string.Empty : dr["IFA_Username"].ToString();
oSwitchFee.propAnnual_Fee = dr["Annual_Fee"] == System.DBNull.Value ? 0 : decimal.Parse(dr["Annual_Fee"].ToString());
oSwitchFee.propPer_Switch_Fee = dr["Per_Switch_Fee"] == System.DBNull.Value ? 0 : decimal.Parse(dr["Per_Switch_Fee"].ToString());
oSwitchFee.propAccess_Denied = dr["Access_Denied"] == System.DBNull.Value ? false : bool.Parse(dr["Access_Denied"].ToString());
oSwitchFeeList.Add(oSwitchFee);
}
dr.Close();
cmd.Dispose();
con.Close();
con.Dispose();
return oSwitchFeeList;
}
private List<clsSwitchDetails> getSwitchDetails(string strUserID, clsPortfolio Portfolio, int intSwitchID)
{
SqlConnection con1 = new clsSystem_DBConnection(clsSystem_DBConnection.strConnectionString.NavIntegrationDB).propConnection;
List<clsSwitchDetails> listSwitchDetails = new List<clsSwitchDetails>();
SqlCommand cmd = new SqlCommand();
SqlDataReader dr1;
con1.Open();
cmd.Connection = con1;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "[SWITCH_DetailsGet]";
cmd.Parameters.Add("@param_intSwitchID", System.Data.SqlDbType.Int).Value = intSwitchID;
dr1 = cmd.ExecuteReader();
float fTotalAllocation = 0;
while (dr1.Read())
{
clsSwitchDetails newClsSwitchDetails = new clsSwitchDetails();
newClsSwitchDetails.propAllocation = float.Parse(Math.Round(double.Parse(dr1["Allocation"].ToString()), 2).ToString());
newClsSwitchDetails.propCreated_By = dr1["Created_By"].ToString();
newClsSwitchDetails.propDate_Created = DateTime.Parse(dr1["Date_Created"].ToString());
newClsSwitchDetails.propDate_LastUpdate = DateTime.Parse(dr1["Date_LastUpdate"].ToString());
newClsSwitchDetails.propFund = new clsFund(int.Parse(dr1["FundID"].ToString()));
newClsSwitchDetails.propFundID = int.Parse(dr1["FundID"].ToString());
newClsSwitchDetails.propSwitchDetailsID = int.Parse(dr1["SwitchDetailsID"].ToString());
newClsSwitchDetails.propSwitchID = int.Parse(dr1["SwitchID"].ToString());
newClsSwitchDetails.propUpdated_By = dr1["Updated_By"].ToString();
newClsSwitchDetails.propIsDeletable = dr1["isDeletable"].ToString().Equals("1") ? true : false;
if (Portfolio.propPortfolioDetails[0].propClientCurrency != newClsSwitchDetails.propFund.propCurrency)
{
newClsSwitchDetails.propUnits = clsSwitchDetails.computeUnits(newClsSwitchDetails.propAllocation,
float.Parse(Math.Round(double.Parse(Portfolio.propPortfolioDetails[0].propTotalCurrentValueClient.ToString()), 0).ToString()),
clsCurrency.convertToClientCurrency(this.propClientID, newClsSwitchDetails.propFund.propPrice, newClsSwitchDetails.propFund.propCurrency));
}
else
{
newClsSwitchDetails.propUnits = clsSwitchDetails.computeUnits(newClsSwitchDetails.propAllocation,
float.Parse(Math.Round(double.Parse(Portfolio.propPortfolioDetails[0].propTotalCurrentValueClient.ToString()), 0).ToString()),
newClsSwitchDetails.propFund.propPrice);
}
newClsSwitchDetails.propCurrencyMultiplier = clsCurrency.getCurrencyMultiplier(this.propClientID, newClsSwitchDetails.propFund.propCurrency);
//newClsSwitchDetails.propValue = clsSwitchDetails.computeValue(newClsSwitchDetails.propAllocation, Portfolio.propPortfolioDetails[0].propTotalCurrentValueClient);
newClsSwitchDetails.propTotalValue = float.Parse(Math.Round(double.Parse(Portfolio.propPortfolioDetails[0].propTotalCurrentValueClient.ToString()), 0).ToString());
newClsSwitchDetails.propValue = clsSwitchDetails.computeValue(newClsSwitchDetails.propAllocation, newClsSwitchDetails.propTotalValue);
fTotalAllocation = fTotalAllocation + newClsSwitchDetails.propAllocation;
newClsSwitchDetails.propTotalAllocation = fTotalAllocation;
listSwitchDetails.Add(newClsSwitchDetails);
}
con1.Close();
cmd.Dispose();
con1.Dispose();
return listSwitchDetails;
}
public static void saveSwitchFee(int intIFA_ID, string strIFA_Name, decimal dAnnual_Fee, decimal dPerSwitch_Fee, bool bAccessDenied)
{
SqlConnection con = new clsSystem_DBConnection(clsSystem_DBConnection.strConnectionString.NavIntegrationDB).propConnection;
SqlCommand cmd = new SqlCommand();
con.Open();
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "SWITCH_FeeSave";
cmd.Parameters.Add("@param_intIFA_ID", System.Data.SqlDbType.Int).Value = intIFA_ID;
cmd.Parameters.Add("@param_IFA_Username", System.Data.SqlDbType.NVarChar).Value = strIFA_Name;
cmd.Parameters.Add("@param_Annual_Fee", System.Data.SqlDbType.Decimal).Value = dAnnual_Fee;
cmd.Parameters.Add("@param_Per_Switch_Fee", System.Data.SqlDbType.Decimal).Value = dPerSwitch_Fee;
cmd.Parameters.Add("param_Access_Denied", System.Data.SqlDbType.Bit).Value = bAccessDenied;
cmd.ExecuteNonQuery();
cmd.Dispose();
con.Close();
con.Dispose();
}
public static void deleteSwitchFee(int intIFA_ID)
{
SqlConnection con = new clsSystem_DBConnection(clsSystem_DBConnection.strConnectionString.NavIntegrationDB).propConnection;
SqlCommand cmd = new SqlCommand();
con.Open();
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "SWITCH_FeeDelete";
cmd.Parameters.Add("@param_intIFA_ID", System.Data.SqlDbType.Int).Value = intIFA_ID;
cmd.ExecuteNonQuery();
cmd.Dispose();
con.Close();
con.Dispose();
}
public static List<clsSwitch> getSwitchList(int[] intSwitches)
{
List<clsSwitch> oSwitchList = new List<clsSwitch>();
SqlConnection con = new clsSystem_DBConnection(clsSystem_DBConnection.strConnectionString.NavIntegrationDB).propConnection;
con.Open();
for (int i = 0; i < intSwitches.Length; i++)
{
SqlCommand cmd = new SqlCommand();
SqlDataReader dr;
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "[SWITCH_HeaderGet]";
cmd.Parameters.Add("@param_intSwitchID", System.Data.SqlDbType.Int).Value = intSwitches[i];
dr = cmd.ExecuteReader();
while (dr.Read())
{
clsSwitch oSwitch = new clsSwitch();
oSwitch.propSwitchID = int.Parse(dr["SwitchID"].ToString());
oSwitch.propPortfolioID = dr["PortfolioID"].ToString();
oSwitch.propClientID = dr["ClientID"].ToString();
oSwitch.propStatus = short.Parse(dr["Status"].ToString());
oSwitch.propStatusString = clsSwitch.getSwitchStringStatus(oSwitch.propStatus);
oSwitch.propDate_Created = dr["Date_Created"] != System.DBNull.Value ? DateTime.Parse(dr["Date_Created"].ToString()) : DateTime.ParseExact("01/01/1800", "dd/MM/yyyy", null);
oSwitch.propCreated_By = dr["Created_By"].ToString();
oSwitch.propDescription = dr["Description"].ToString();
oSwitch.propSwitchClient = new clsClient(oSwitch.propClientID);
oSwitch.propPortfolio = new clsPortfolio(oSwitch.propClientID, oSwitch.propPortfolioID);
oSwitchList.Add(oSwitch);
}
dr.Close();
dr.Dispose();
cmd.Dispose();
}
con.Close();
con.Dispose();
return oSwitchList;
}
public string ValidateSecurityCode(int intSwitchID, string strClientID, string strPortfolioID)
{
string strMessage = string.Empty;
SqlConnection con = new clsSystem_DBConnection(clsSystem_DBConnection.strConnectionString.NavIntegrationDB).propConnection;
SqlCommand cmd = new SqlCommand();
con.Open();
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "[SWITCH_CheckSecurityCodeValid]";
cmd.Parameters.Add("@param_Code", System.Data.SqlDbType.NVarChar).Value = encryptedCode;
cmd.Parameters.Add("@param_SwitchID", System.Data.SqlDbType.Int).Value = intSwitchID;
cmd.Parameters.Add("@param_ClientID", System.Data.SqlDbType.NVarChar).Value = strClientID;
cmd.Parameters.Add("@param_PortfolioID", System.Data.SqlDbType.NVarChar).Value = strPortfolioID;
strMessage = cmd.ExecuteScalar().ToString();
con.Close();
cmd.Dispose();
return strMessage;
}
public static List <clsSwitchDetails_Client> getSwitchDetails(int intSwitchID)
{
SqlConnection con1 = new clsSystem_DBConnection(clsSystem_DBConnection.strConnectionString.NavIntegrationDB).propConnection;
List <clsSwitchDetails_Client> listSwitchDetails = new List <clsSwitchDetails_Client>();
clsSwitch IFASwitch = new clsSwitch(intSwitchID);
clsPortfolio Portfolio = new clsPortfolio(IFASwitch.propClientID.ToString(), IFASwitch.propPortfolioID);
//throw new Exception(Portfolio.propPortfolioDetails.Count.ToString());
//throw new Exception(intSwitchID.ToString() + " - " + IFASwitch.propPortfolioID);
SqlCommand cmd = new SqlCommand();
SqlDataReader dr1;
con1.Open();
cmd.Connection = con1;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "[SWITCHclient_DetailsGet]";
cmd.Parameters.Add("@param_intSwitchID", System.Data.SqlDbType.Int).Value = intSwitchID;
dr1 = cmd.ExecuteReader();
if (!dr1.HasRows)
{
SqlConnection con2 = new clsSystem_DBConnection(clsSystem_DBConnection.strConnectionString.NavIntegrationDB).propConnection;
SqlCommand cmd2 = new SqlCommand();
cmd2.Connection = con2;
cmd2.CommandType = System.Data.CommandType.StoredProcedure;
cmd2.CommandText = "[SWITCH_DetailsGet]";
cmd2.Parameters.Add("@param_intSwitchID", System.Data.SqlDbType.Int).Value = intSwitchID;
con2.Open();
dr1 = cmd2.ExecuteReader();
}
float fTotalAllocation = 0;
while (dr1.Read())
{
clsSwitchDetails_Client newClsSwitchDetails = new clsSwitchDetails_Client();
newClsSwitchDetails.propAllocation = float.Parse(Math.Round(double.Parse(dr1["Allocation"].ToString()), 2).ToString());
newClsSwitchDetails.propCreated_By = dr1["Created_By"].ToString();
newClsSwitchDetails.propDate_Created = DateTime.Parse(dr1["Date_Created"].ToString());
newClsSwitchDetails.propDate_LastUpdate = DateTime.Parse(dr1["Date_LastUpdate"].ToString());
newClsSwitchDetails.propFund = new clsFund(int.Parse(dr1["FundID"].ToString()));
newClsSwitchDetails.propFundID = int.Parse(dr1["FundID"].ToString());
newClsSwitchDetails.propSwitchDetailsID = int.Parse(dr1["SwitchDetailsID"].ToString());
newClsSwitchDetails.propSwitchID = int.Parse(dr1["SwitchID"].ToString());
newClsSwitchDetails.propUpdated_By = dr1["Updated_By"].ToString();
newClsSwitchDetails.propIsDeletable = dr1["isDeletable"].ToString().Equals("1") ? true : false;
//if (Portfolio.propPortfolioDetails[0].propClientCurrency != newClsSwitchDetails.propFund.propCurrency)
//{
newClsSwitchDetails.propUnits = clsSwitchDetails.computeUnits(newClsSwitchDetails.propAllocation,
float.Parse(Math.Round(double.Parse(Portfolio.propPortfolioDetails[0].propTotalCurrentValueClient.ToString()), 0).ToString()),
clsCurrency.convertToClientCurrency(IFASwitch.propClientID, newClsSwitchDetails.propFund.propPrice, newClsSwitchDetails.propFund.propCurrency));
//}
//else
//{
// newClsSwitchDetails.propUnits = clsSwitchDetails.computeUnits(newClsSwitchDetails.propAllocation,
// float.Parse(Math.Round(double.Parse(Portfolio.propPortfolioDetails[0].propTotalCurrentValueClient.ToString()), 0).ToString()),
// newClsSwitchDetails.propFund.propPrice);
//}
newClsSwitchDetails.propCurrencyMultiplier = clsCurrency.getCurrencyMultiplier(IFASwitch.propClientID, newClsSwitchDetails.propFund.propCurrency);
//newClsSwitchDetails.propValue = clsSwitchDetails.computeValue(newClsSwitchDetails.propAllocation, Portfolio.propPortfolioDetails[0].propTotalCurrentValueClient);
newClsSwitchDetails.propTotalValue = float.Parse(Math.Round(double.Parse(Portfolio.propPortfolioDetails[0].propTotalCurrentValueClient.ToString()), 0).ToString());
newClsSwitchDetails.propValue = clsSwitchDetails.computeValue(newClsSwitchDetails.propAllocation, newClsSwitchDetails.propTotalValue);
fTotalAllocation = fTotalAllocation + newClsSwitchDetails.propAllocation;
newClsSwitchDetails.propTotalAllocation = fTotalAllocation;
listSwitchDetails.Add(newClsSwitchDetails);
}
con1.Close();
cmd.Dispose();
con1.Dispose();
return(listSwitchDetails);
}
public static List<clsSwitchSchemeDetails> getSwitchDetails(clsScheme Scheme, clsSwitchScheme SwitchScheme, Boolean isContribution)
{
SqlConnection con1 = new clsSystem_DBConnection(clsSystem_DBConnection.strConnectionString.NavIntegrationDB).propConnection;
List<clsSwitchSchemeDetails> listSwitchDetails = new List<clsSwitchSchemeDetails>();
SqlCommand cmd = new SqlCommand();
SqlDataReader dr1;
con1.Open();
cmd.Connection = con1;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "[SWITCHScheme_DetailsGet]";
cmd.Parameters.Add("@param_intSwitchID", System.Data.SqlDbType.Int).Value = SwitchScheme.propSwitchID;
cmd.Parameters.Add("@param_isContribution", System.Data.SqlDbType.Int).Value = isContribution;
dr1 = cmd.ExecuteReader();
double dPrice;
float fTotalAllocation = 0;
while (dr1.Read())
{
dPrice = 0;
clsSwitchSchemeDetails newClsSwitchDetails = new clsSwitchSchemeDetails();
newClsSwitchDetails.propAllocation = float.Parse(Math.Round(double.Parse(dr1["Allocation"].ToString()), 2).ToString());
newClsSwitchDetails.propCreated_By = dr1["Created_By"].ToString();
newClsSwitchDetails.propDate_Created = DateTime.Parse(dr1["Date_Created"].ToString());
newClsSwitchDetails.propDate_LastUpdate = DateTime.Parse(dr1["Date_LastUpdate"].ToString());
newClsSwitchDetails.propFund = new clsFund(int.Parse(dr1["FundID"].ToString()));
newClsSwitchDetails.propIsDeletable = dr1["isDeletable"].ToString().Equals("1") ? true : false;
newClsSwitchDetails.propSwitchDetailsID = int.Parse(dr1["SwitchDetailsID"].ToString());
newClsSwitchDetails.propSwitchScheme = SwitchScheme;
newClsSwitchDetails.propUpdated_By = dr1["Updated_By"].ToString();
newClsSwitchDetails.propCurrencyMultiplier = clsCurrency.getCurrencyMultiplier(Scheme.propClient.propClientID, newClsSwitchDetails.propFund.propCurrency);
if (Scheme.propClient.propCurrency != newClsSwitchDetails.propFund.propCurrency)
{
double dConvertedValue = clsCurrency.convertToClientCurrency(Scheme.propClient.propClientID, newClsSwitchDetails.propFund.propPrice, newClsSwitchDetails.propFund.propCurrency);
int intMarker = dConvertedValue.ToString().IndexOf('.');
string strIntegerPart = dConvertedValue.ToString().Substring(0, intMarker);
string strDecimalPart = dConvertedValue.ToString().Substring(intMarker, 4);
dPrice = Convert.ToDouble(strIntegerPart + strDecimalPart);
}
else
{
dPrice = Math.Round(newClsSwitchDetails.propFund.propPrice, 4);
}
newClsSwitchDetails.propTotalValue = float.Parse(Math.Round(double.Parse(Scheme.propCC_TotalValue.ToString()), 0).ToString());
fTotalAllocation = fTotalAllocation + newClsSwitchDetails.propAllocation;
newClsSwitchDetails.propTotalAllocation = fTotalAllocation;
newClsSwitchDetails.propValue = float.Parse(((Math.Round(newClsSwitchDetails.propAllocation, 2) / 100) * Math.Round(Scheme.propCC_TotalValue, 0)).ToString());
newClsSwitchDetails.propUnits = Convert.ToDecimal((((Math.Round(newClsSwitchDetails.propAllocation, 2) / 100) * Math.Round(Scheme.propCC_TotalValue, 0)) / dPrice).ToString());
listSwitchDetails.Add(newClsSwitchDetails);
}
con1.Close();
cmd.Dispose();
con1.Dispose();
return listSwitchDetails;
}
public List <clsSwitchDetails> replicateModelPortfolio(clsPortfolio _clsPortfolio, int _intModelID, string _strModelGroupID, string _strModelPortfolioID)
{
SqlConnection con1 = new clsSystem_DBConnection(clsSystem_DBConnection.strConnectionString.NavIntegrationDB).propConnection;
List <clsSwitchDetails> listSwitchDetails = new List <clsSwitchDetails>();
SqlCommand cmd = new SqlCommand();
SqlDataReader dr1;
con1.Open();
cmd.Connection = con1;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "[SWITCH_ModelPortfolioDetailsGet]";
cmd.Parameters.Add("@param_ModelID", System.Data.SqlDbType.Int).Value = _intModelID;
cmd.Parameters.Add("@param_ModelGroupID", System.Data.SqlDbType.NVarChar).Value = _strModelGroupID;
cmd.Parameters.Add("@param_ModelPortfolioID", System.Data.SqlDbType.NVarChar).Value = _strModelPortfolioID;
dr1 = cmd.ExecuteReader();
float fTotalAllocation = 0;
while (dr1.Read())
{
clsSwitchDetails newClsSwitchDetails = new clsSwitchDetails();
newClsSwitchDetails.propAllocation = float.Parse(Math.Round(double.Parse(dr1["Allocation"].ToString()), 2).ToString());
//newClsSwitchDetails.propCreated_By = //dr1["Created_By"].ToString();
//newClsSwitchDetails.propDate_Created = DateTime.Parse(dr1["Date_Created"].ToString());
//newClsSwitchDetails.propDate_LastUpdate = DateTime.Parse(dr1["Date_Updated"].ToString());
newClsSwitchDetails.propFund = new clsFund(int.Parse(dr1["FundID"].ToString()));
newClsSwitchDetails.propFundID = int.Parse(dr1["FundID"].ToString());
//newClsSwitchDetails.propSwitchDetailsID = int.Parse(dr1["SwitchDetailsID"].ToString());
//newClsSwitchDetails.propSwitchID = int.Parse(dr1["SwitchID"].ToString());
//newClsSwitchDetails.propUpdated_By = dr1["Updated_By"].ToString();
newClsSwitchDetails.propIsDeletable = dr1["isDeletable"].ToString().Equals("1") ? true : false;
//if (Portfolio.propPortfolioDetails[0].propClientCurrency != newClsSwitchDetails.propFund.propCurrency)
if (_clsPortfolio.propClient.propCurrency != newClsSwitchDetails.propFund.propCurrency)
{
newClsSwitchDetails.propUnits = clsSwitchDetails.computeUnits(newClsSwitchDetails.propAllocation,
float.Parse(Math.Round(double.Parse(_clsPortfolio.propPortfolioDetails[0].propTotalCurrentValueClient.ToString()), 0).ToString()),
clsCurrency.convertToClientCurrency(_clsPortfolio.propClientID, newClsSwitchDetails.propFund.propPrice, newClsSwitchDetails.propFund.propCurrency));
}
else
{
newClsSwitchDetails.propUnits = clsSwitchDetails.computeUnits(newClsSwitchDetails.propAllocation,
float.Parse(Math.Round(double.Parse(_clsPortfolio.propPortfolioDetails[0].propTotalCurrentValueClient.ToString()), 0).ToString()),
newClsSwitchDetails.propFund.propPrice);
}
newClsSwitchDetails.propCurrencyMultiplier = clsCurrency.getCurrencyMultiplier(_clsPortfolio.propClientID, newClsSwitchDetails.propFund.propCurrency);
//newClsSwitchDetails.propValue = clsSwitchDetails.computeValue(newClsSwitchDetails.propAllocation, Portfolio.propPortfolioDetails[0].propTotalCurrentValueClient);
newClsSwitchDetails.propTotalValue = float.Parse(Math.Round(double.Parse(_clsPortfolio.propPortfolioDetails[0].propTotalCurrentValueClient.ToString()), 0).ToString());
newClsSwitchDetails.propValue = clsSwitchDetails.computeValue(newClsSwitchDetails.propAllocation, newClsSwitchDetails.propTotalValue);
fTotalAllocation = fTotalAllocation + newClsSwitchDetails.propAllocation;
newClsSwitchDetails.propTotalAllocation = fTotalAllocation;
listSwitchDetails.Add(newClsSwitchDetails);
}
con1.Close();
cmd.Dispose();
con1.Dispose();
return(listSwitchDetails);
}
public static List <clsSwitchSchemeDetails_Client> getSwitchDetails(int intSwitchID, Boolean isContribution)
{
SqlConnection con1 = new clsSystem_DBConnection(clsSystem_DBConnection.strConnectionString.NavIntegrationDB).propConnection;
List <clsSwitchSchemeDetails_Client> listSwitchDetails = new List <clsSwitchSchemeDetails_Client>();
clsSwitchScheme SwitchScheme = new clsSwitchScheme(intSwitchID);
clsScheme Scheme = new clsScheme(SwitchScheme.propClient.propClientID, SwitchScheme.propScheme.propSchemeID);
SqlCommand cmd = new SqlCommand();
SqlDataReader dr1;
con1.Open();
cmd.Connection = con1;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "[SWITCHSchemeClient_DetailsGet]";
cmd.Parameters.Add("@param_intSwitchID", System.Data.SqlDbType.Int).Value = intSwitchID;
cmd.Parameters.Add("@param_isContribution", System.Data.SqlDbType.Int).Value = isContribution;
dr1 = cmd.ExecuteReader();
if (!dr1.HasRows)
{
SqlConnection con2 = new clsSystem_DBConnection(clsSystem_DBConnection.strConnectionString.NavIntegrationDB).propConnection;
SqlCommand cmd2 = new SqlCommand();
cmd2.Connection = con2;
cmd2.CommandType = System.Data.CommandType.StoredProcedure;
cmd2.CommandText = "[SWITCHScheme_DetailsGet]";
cmd2.Parameters.Add("@param_intSwitchID", System.Data.SqlDbType.Int).Value = intSwitchID;
cmd2.Parameters.Add("@param_isContribution", System.Data.SqlDbType.Int).Value = isContribution;
con2.Open();
dr1 = cmd2.ExecuteReader();
}
double dPrice;
float fTotalAllocation = 0;
while (dr1.Read())
{
dPrice = 0;
clsSwitchSchemeDetails_Client newClsSwitchDetails = new clsSwitchSchemeDetails_Client();
newClsSwitchDetails.propAllocation = float.Parse(Math.Round(double.Parse(dr1["Allocation"].ToString()), 2).ToString());
newClsSwitchDetails.propCreated_By = dr1["Created_By"].ToString();
newClsSwitchDetails.propDate_Created = DateTime.Parse(dr1["Date_Created"].ToString());
newClsSwitchDetails.propDate_LastUpdate = DateTime.Parse(dr1["Date_LastUpdate"].ToString());
newClsSwitchDetails.propFund = new clsFund(int.Parse(dr1["FundID"].ToString()));
newClsSwitchDetails.propIsDeletable = dr1["isDeletable"].ToString().Equals("1") ? true : false;
newClsSwitchDetails.propSwitchDetailsID = int.Parse(dr1["SwitchDetailsID"].ToString());
newClsSwitchDetails.propSwitchScheme = SwitchScheme;
newClsSwitchDetails.propUpdated_By = dr1["Updated_By"].ToString();
newClsSwitchDetails.propCurrencyMultiplier = clsCurrency.getCurrencyMultiplier(Scheme.propClient.propClientID, newClsSwitchDetails.propFund.propCurrency);
if (Scheme.propClient.propCurrency != newClsSwitchDetails.propFund.propCurrency)
{
double dConvertedValue = clsCurrency.convertToClientCurrency(Scheme.propClient.propClientID, newClsSwitchDetails.propFund.propPrice, newClsSwitchDetails.propFund.propCurrency);
int intMarker = dConvertedValue.ToString().IndexOf('.');
string strIntegerPart = dConvertedValue.ToString().Substring(0, intMarker);
string strDecimalPart = dConvertedValue.ToString().Substring(intMarker, 4);
dPrice = Convert.ToDouble(strIntegerPart + strDecimalPart);
}
else
{
dPrice = Math.Round(newClsSwitchDetails.propFund.propPrice, 4);
}
newClsSwitchDetails.propTotalValue = float.Parse(Math.Round(double.Parse(Scheme.propCC_TotalValue.ToString()), 0).ToString());
fTotalAllocation = fTotalAllocation + newClsSwitchDetails.propAllocation;
newClsSwitchDetails.propTotalAllocation = fTotalAllocation;
newClsSwitchDetails.propValue = float.Parse(((Math.Round(newClsSwitchDetails.propAllocation, 2) / 100) * Math.Round(Scheme.propCC_TotalValue, 0)).ToString());
newClsSwitchDetails.propUnits = Convert.ToDecimal((((Math.Round(newClsSwitchDetails.propAllocation, 2) / 100) * Math.Round(Scheme.propCC_TotalValue, 0)) / dPrice).ToString());
listSwitchDetails.Add(newClsSwitchDetails);
}
con1.Close();
cmd.Dispose();
con1.Dispose();
return(listSwitchDetails);
}
public static List<Template> getListEmailTemplate()
{
SqlConnection con = new clsSystem_DBConnection(clsSystem_DBConnection.strConnectionString.NavIntegrationDB).propConnection;
SqlCommand cmd = new SqlCommand();
SqlDataReader dr;
con.Open();
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "SWITCH_EmailTemplateGetAll";
dr = cmd.ExecuteReader();
List<Template> newListTemplate = new List<Template>();
while (dr.Read())
{
Template newTemplate = new Template(int.Parse(dr["EmailTemplateID"].ToString()), null);
newListTemplate.Add(newTemplate);
}
con.Close();
cmd.Dispose();
return newListTemplate;
}
public static List<clsSwitch> getSwitchList(int intIFA_ID, string strClientName, string strCompany, int intStatus, string strStartDate, string strEndDate)
{
List<clsSwitch> oSwitchList = new List<clsSwitch>();
SqlConnection con = new clsSystem_DBConnection(clsSystem_DBConnection.strConnectionString.NavIntegrationDB).propConnection;
SqlCommand cmd = new SqlCommand();
SqlDataReader dr;
con.Open();
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "[SWITCH_HeaderGetAllByIFA]";
cmd.Parameters.Add("@param_IFA_ID", System.Data.SqlDbType.Int).Value = intIFA_ID;
cmd.Parameters.Add("@param_ClientName", System.Data.SqlDbType.NVarChar).Value = strClientName;
cmd.Parameters.Add("@param_Company", System.Data.SqlDbType.NVarChar).Value = strCompany;
cmd.Parameters.Add("@param_Status", System.Data.SqlDbType.Int).Value = intStatus;
cmd.Parameters.Add("@param_StartDate", System.Data.SqlDbType.NVarChar).Value = strStartDate;
cmd.Parameters.Add("@param_EndDate", System.Data.SqlDbType.NVarChar).Value = strEndDate;
dr = cmd.ExecuteReader();
while (dr.Read())
{
clsSwitch oSwitch = new clsSwitch();
oSwitch.propSwitchID = int.Parse(dr["SwitchID"].ToString());
oSwitch.propPortfolioID = dr["PortfolioID"].ToString();
oSwitch.propClientID = dr["ClientID"].ToString();
oSwitch.propStatus = short.Parse(dr["Status"].ToString());
oSwitch.propStatusString = clsSwitch.getSwitchStringStatus(oSwitch.propStatus);
oSwitch.propDate_Created = dr["Date_Created"] != System.DBNull.Value ? DateTime.Parse(dr["Date_Created"].ToString()) : DateTime.ParseExact("01/01/1800", "dd/MM/yyyy", null);
oSwitch.propCreated_By = dr["Created_By"].ToString();
oSwitch.propDescription = dr["Description"].ToString();
oSwitch.propSwitchClient = new clsClient(oSwitch.propClientID);
oSwitch.propPortfolio = new clsPortfolio(oSwitch.propClientID, oSwitch.propPortfolioID);
oSwitchList.Add(oSwitch);
}
con.Close();
cmd.Dispose();
con.Dispose();
return oSwitchList;
}
public static void deleteSwitchDetailsTemp(string strClientID, string strPortfolioID)
{
SqlConnection con = new clsSystem_DBConnection(clsSystem_DBConnection.strConnectionString.NavIntegrationDB).propConnection;
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "[SWITCH_Temp_DetailsDelete]";
cmd.Parameters.Add("@param_strClientID", System.Data.SqlDbType.NVarChar).Value = strClientID;
cmd.Parameters.Add("@param_strPortfolioID", System.Data.SqlDbType.NVarChar).Value = strPortfolioID;
cmd.ExecuteNonQuery();
con.Close();
}
public static void removeSwitchDetails(int intSwitchDetailsID)
{
SqlConnection con = new clsSystem_DBConnection(clsSystem_DBConnection.strConnectionString.NavIntegrationDB).propConnection;
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "[SWITCH_DetailsDelete]";
cmd.Parameters.Add("@param_intSwitchDetailsID", System.Data.SqlDbType.Int).Value = intSwitchDetailsID;
cmd.ExecuteNonQuery();
con.Close();
}
public static void doBulkSwitch()
{
SqlConnection con = new clsSystem_DBConnection(clsSystem_DBConnection.strConnectionString.NavIntegrationDB).propConnection;
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "[SWITCH_BulkInsert]";
cmd.ExecuteNonQuery();
con.Close();
}