public static List <clsIFA> getIFAList()
{
List <clsIFA> oIFAList = new List <clsIFA>();
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_IFAGet";
cmd.Parameters.Add("@param_intIFA_ID", System.Data.SqlDbType.Int).Value = 0;
dr = cmd.ExecuteReader();
while (dr.Read())
{
clsIFA oIFA = new clsIFA();
oIFA.propIFA_ID = int.Parse(dr["IFA_ID"].ToString().Trim());
oIFA.propIFA_Name = dr["IFA_Name"].ToString().Trim();
oIFAList.Add(oIFA);
}
return(oIFAList);
}
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 void insertSwitchDetails(List <clsSwitchDetails_Client> listSwitchDetails, string strUserID, int intSwitchID)
{
SqlConnection con = new clsSystem_DBConnection(clsSystem_DBConnection.strConnectionString.NavIntegrationDB).propConnection;
con.Open();
foreach (clsSwitchDetails_Client SwitchDetail in listSwitchDetails)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "[SWITCHclient_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 List<clsIFA> getIFAList()
{
List<clsIFA> oIFAList = new List<clsIFA>();
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_IFAGet";
cmd.Parameters.Add("@param_intIFA_ID", System.Data.SqlDbType.Int).Value = 0;
dr = cmd.ExecuteReader();
while (dr.Read())
{
clsIFA oIFA = new clsIFA();
oIFA.propIFA_ID = int.Parse(dr["IFA_ID"].ToString().Trim());
oIFA.propIFA_Name = dr["IFA_Name"].ToString().Trim();
oIFAList.Add(oIFA);
}
return oIFAList;
}
public static void editSMSTemplate(Int16 intSMSTemplateID, string strTemplateName, string strTemplateFor, string Message)
{
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_SMSTemplateUpdate]";
cmd.Parameters.Add("@param_sintSMSTemplateID", System.Data.SqlDbType.SmallInt).Value = intSMSTemplateID;
cmd.Parameters.Add("@param_strTemplateName", System.Data.SqlDbType.NVarChar).Value = strTemplateName;
cmd.Parameters.Add("@param_strTemplateFor", System.Data.SqlDbType.NVarChar).Value = strTemplateFor;
cmd.Parameters.Add("@param_strMessage", System.Data.SqlDbType.NVarChar).Value = Message;
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
}
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 declineSwitch(int intSwitchID)
{
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 = "[SWITCHSchemeClient_Decline]";
cmd.Parameters.Add("@param_SwitchID", System.Data.SqlDbType.Int).Value = intSwitchID;
cmd.ExecuteNonQuery();
}
public static void deleteSwitchTemp(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_Temp_Delete]";
cmd.Parameters.Add("@param_strClientID", System.Data.SqlDbType.NVarChar).Value = strClientID;
cmd.Parameters.Add("@param_strPortfolioID", System.Data.SqlDbType.NVarChar).Value = strPortfolioID;
cmd.ExecuteNonQuery();
}
public static void updateSwitchHeader(int intSwitchID, clsSwitch.enumSwitchStatus SwitchStatus)
{
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 = "[SWITCHSCheme_HeaderUpdate]";
cmd.Parameters.Add("@param_intSwitchID", System.Data.SqlDbType.Int).Value = intSwitchID;
cmd.Parameters.Add("@param_Status", System.Data.SqlDbType.SmallInt).Value = SwitchStatus;
cmd.ExecuteNonQuery();
}
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 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 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 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 void insertEmailLog(int intSwitchID, string strRecipient, string strClientID, string strMessage, enumEmailPurpose enumPurpose)
{
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_EmailLogInsert]";
cmd.Parameters.Add("@param_intSwitchID", System.Data.SqlDbType.Int).Value = intSwitchID;
cmd.Parameters.Add("@param_strRecipient", System.Data.SqlDbType.NVarChar).Value = strRecipient;
cmd.Parameters.Add("@param_strClientID", System.Data.SqlDbType.NVarChar).Value = strClientID;
cmd.Parameters.Add("@param_strMessage", System.Data.SqlDbType.NVarChar).Value = strMessage;
cmd.Parameters.Add("@param_strPurpose", System.Data.SqlDbType.NVarChar).Value = enumPurpose.ToString();
cmd.ExecuteNonQuery();
}
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 void insertEmailLog(int intSwitchID, string strRecipient, string strClientID, string strMessage, enumEmailPurpose enumPurpose)
{
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_EmailLogInsert]";
cmd.Parameters.Add("@param_intSwitchID", System.Data.SqlDbType.Int).Value = intSwitchID;
cmd.Parameters.Add("@param_strRecipient", System.Data.SqlDbType.NVarChar).Value = strRecipient;
cmd.Parameters.Add("@param_strClientID", System.Data.SqlDbType.NVarChar).Value = strClientID;
cmd.Parameters.Add("@param_strMessage", System.Data.SqlDbType.NVarChar).Value = strMessage;
cmd.Parameters.Add("@param_strPurpose", System.Data.SqlDbType.NVarChar).Value = enumPurpose.ToString();
cmd.ExecuteNonQuery();
}
public static void LogOutput(enumSwitchType switchType, int SwitchID, string Filename, enumOutputType outputType)
{
using (System.Data.SqlClient.SqlConnection con = new clsSystem_DBConnection(clsSystem_DBConnection.strConnectionString.NavIntegrationDB).propConnection)
{
con.Open();
using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand())
{
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "[SWITCH_Output_Log]";
cmd.Parameters.Add("@SwitchType", System.Data.SqlDbType.VarChar).Value = switchType.ToString();
cmd.Parameters.Add("@SwitchID", System.Data.SqlDbType.Int).Value = SwitchID;
cmd.Parameters.Add("@Filename", System.Data.SqlDbType.VarChar).Value = Filename;
cmd.Parameters.Add("@OutputType", System.Data.SqlDbType.VarChar).Value = outputType.ToString();
cmd.ExecuteNonQuery();
}
}
}
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 = "[SWITCH_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 int insertSwitchHeader(string strPortfolioID, string strClientID, string strUserID, enumSwitchStatus SwitchStatus, Nullable <int> intSwitchID, string strDescription)
{
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_HeaderInsert]";
cmd.Parameters.Add("@param_strPortfolioID", System.Data.SqlDbType.NVarChar).Value = strPortfolioID;
cmd.Parameters.Add("@param_strClientID", System.Data.SqlDbType.NVarChar).Value = strClientID;
cmd.Parameters.Add("@param_intStatus", System.Data.SqlDbType.SmallInt).Value = SwitchStatus;
cmd.Parameters.Add("@param_strCreated_By", System.Data.SqlDbType.NVarChar).Value = strUserID;
cmd.Parameters.Add("@param_intSwitchID", System.Data.SqlDbType.Int).Value = intSwitchID;
cmd.Parameters.Add("@param_strDescription", System.Data.SqlDbType.NVarChar).Value = strDescription;
return(int.Parse(cmd.ExecuteScalar().ToString()));
}
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 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 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 insertSwitchHeaderTemp(int IFA_ID, int intModelID, string strModelGroupID, string strModelPortfolioID, string strClientID, string strPortfolioID, string strUserID)
{
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_Temp_HeaderInsert]";
cmd.Parameters.Add("@param_intIFA_ID", System.Data.SqlDbType.Int).Value = IFA_ID;
cmd.Parameters.Add("@param_ModelID", System.Data.SqlDbType.Int).Value = intModelID;
cmd.Parameters.Add("@param_strModelGroupID", System.Data.SqlDbType.NVarChar).Value = strModelGroupID;
cmd.Parameters.Add("@param_strModelPortfolioID", System.Data.SqlDbType.NVarChar).Value = strModelPortfolioID;
cmd.Parameters.Add("@param_strClientID", System.Data.SqlDbType.NVarChar).Value = strClientID;
cmd.Parameters.Add("@param_strPortfolioID", System.Data.SqlDbType.NVarChar).Value = strPortfolioID;
cmd.Parameters.Add("@param_strUser", System.Data.SqlDbType.NVarChar).Value = strUserID;
cmd.ExecuteNonQuery();
}
public static void deleteAllDetails(Nullable <int> intSwitchID)
{
if (intSwitchID == null)
{
return;
}
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 = "[SWITCHSchemeClient_DetailsDeleteAll]";
cmd.Parameters.Add("@param_SwitchID", System.Data.SqlDbType.Int).Value = intSwitchID;
cmd.ExecuteNonQuery();
}
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 <clsPortfolio> getClientListWithModel(int intIFA_ID, int intModelID, string strModelGroupID, string strModelPortfolioID, string strSortBy)
{
List <clsPortfolio> clsPortfolioList = new List <clsPortfolio>();
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_ModelPortfolioClientGet]";
cmd.Parameters.Add("@param_IFA_ID", System.Data.SqlDbType.Int).Value = intIFA_ID;
cmd.Parameters.Add("@param_ModelID", System.Data.SqlDbType.NVarChar).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.Parameters.Add("@param_SortBy", System.Data.SqlDbType.NVarChar).Value = strSortBy;
dr = cmd.ExecuteReader();
while (dr.Read())
{
clsPortfolio _clsPortfolio = new clsPortfolio();
_clsPortfolio.propModelID = intModelID;
_clsPortfolio.propModelGroupID = dr["ModelGroupID"].ToString();
_clsPortfolio.propModelPortfolioID = dr["ModelPortfolioID"].ToString();
_clsPortfolio.propClientID = dr["ClientID"].ToString();
_clsPortfolio.propClient = new clsClient(_clsPortfolio.propClientID);
_clsPortfolio.propPortfolioID = dr["ClientPortfolioID"].ToString();
_clsPortfolio.propCompany = dr["ClientPortfolioCompany"].ToString();
_clsPortfolio.propAccountNumber = dr["ClientPortfolioAccountNumber"].ToString();
_clsPortfolio.propMFPercent = int.Parse(dr["Discretionary"].ToString());
_clsPortfolio.propSwitch = new clsSwitch();
_clsPortfolio.propSwitch.propSwitchID = int.Parse(dr["SwitchID"].ToString());
_clsPortfolio.propSwitchTemp = new clsSwitchTemp();
_clsPortfolio.propSwitchTemp.propModelID = int.Parse(dr["SwitchTemp"].ToString());
clsPortfolioList.Add(_clsPortfolio);
}
return(clsPortfolioList);
}
public static clsModelGroup getModelPortfolioClientList(string strIFACode, string strModelGroup, string strModelName)
{
clsModelGroup _clsModelGroup = new clsModelGroup();
List <clsPortfolio> listPortfolio = new List <clsPortfolio>();
SqlConnection con = new clsSystem_DBConnection(clsSystem_DBConnection.strConnectionString.NavIntegrationDB).propConnection;
SqlCommand cmd = new SqlCommand();
SqlDataReader dr;
string strClientID = string.Empty;
string strPortfolioID = string.Empty;
con.Open();
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "SWITCH_ModelPortfolioClientGet";
cmd.Parameters.Add("", System.Data.SqlDbType.NVarChar).Value = strIFACode;
cmd.Parameters.Add("", System.Data.SqlDbType.NVarChar).Value = strModelGroup;
cmd.Parameters.Add("", System.Data.SqlDbType.NVarChar).Value = strModelName;
dr = cmd.ExecuteReader();
while (dr.Read())
{
_clsModelGroup.propModelID = dr["ModelID"].ToString();
_clsModelGroup.propModelIFA = dr["ModelIFA"].ToString();
_clsModelGroup.propModelGroup = dr["ModelGroup"].ToString();
_clsModelGroup.propModelName = dr["ModelName"].ToString();
strClientID = dr["ClientID"].ToString();
strPortfolioID = dr["ClientPortfolioID"].ToString();
clsPortfolio _clsPortfolio = new clsPortfolio();
//_clsPortfolio.(strClientID, strPortfolioID);
_clsPortfolio.propClient = new clsClient(strClientID);
listPortfolio.Add(_clsPortfolio);
}
return(_clsModelGroup);
}
public static void deleteSMSTemplate(Int16 intSMSTemplateID)
{
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_SMSTemplateDelete]";
cmd.Parameters.Add("@param_sintSMSTemplateID", System.Data.SqlDbType.SmallInt).Value = intSMSTemplateID;
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
}
public static List <clsSwitchFee> computePerSwitchFeeReport(int intIFA_ID, string strStartDate, string strEndDate)
{
List <clsSwitchFee> oSwitchFeeList = new List <clsSwitchFee>();
decimal dTotalFee = 0;
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_FeeReportGet";
cmd.Parameters.Add("@param_intIFA_ID", System.Data.SqlDbType.Int).Value = intIFA_ID;
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())
{
clsSwitchFee oSwitchFee = new clsSwitchFee();
//oSwitchFee.propSwitches = Array.ConvertAll(dr["Switches"].ToString().Split(','), s => int.Parse(s));
oSwitchFee.propSwitches = dr["Switches"].ToString();
oSwitchFee.propIFA_ID = (int)dr["IFA_ID"];
oSwitchFee.propIFA_Name = dr["IFA_Name"].ToString();
oSwitchFee.propPer_Switch_Fee = (decimal)dr["Per_Switch_Fee"];
oSwitchFee.propQuantity = (int)dr["Quantity"];
oSwitchFee.propStartDate = DateTime.Parse(dr["StartDate"].ToString());
oSwitchFee.propEndDate = DateTime.Parse(dr["EndDate"].ToString());
dTotalFee = dTotalFee + oSwitchFee.propFees_Due;
oSwitchFee.propTotal_Fee = dTotalFee;
oSwitchFeeList.Add(oSwitchFee);
}
return(oSwitchFeeList);
}
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 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<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 clsModelGroup getModelPortfolioClientList(string strIFACode, string strModelGroup, string strModelName)
{
clsModelGroup _clsModelGroup = new clsModelGroup();
List<clsPortfolio> listPortfolio = new List<clsPortfolio>();
SqlConnection con = new clsSystem_DBConnection(clsSystem_DBConnection.strConnectionString.NavIntegrationDB).propConnection;
SqlCommand cmd = new SqlCommand();
SqlDataReader dr;
string strClientID = string.Empty;
string strPortfolioID = string.Empty;
con.Open();
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "SWITCH_ModelPortfolioClientGet";
cmd.Parameters.Add("", System.Data.SqlDbType.NVarChar).Value = strIFACode;
cmd.Parameters.Add("", System.Data.SqlDbType.NVarChar).Value = strModelGroup;
cmd.Parameters.Add("", System.Data.SqlDbType.NVarChar).Value = strModelName;
dr = cmd.ExecuteReader();
while (dr.Read())
{
_clsModelGroup.propModelID = dr["ModelID"].ToString();
_clsModelGroup.propModelIFA = dr["ModelIFA"].ToString();
_clsModelGroup.propModelGroup = dr["ModelGroup"].ToString();
_clsModelGroup.propModelName = dr["ModelName"].ToString();
strClientID = dr["ClientID"].ToString();
strPortfolioID = dr["ClientPortfolioID"].ToString();
clsPortfolio _clsPortfolio = new clsPortfolio();
//_clsPortfolio.(strClientID, strPortfolioID);
_clsPortfolio.propClient = new clsClient(strClientID);
listPortfolio.Add(_clsPortfolio);
}
return _clsModelGroup;
}
//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 void LogOutput(enumSwitchType switchType, int SwitchID, string Filename, enumOutputType outputType)
{
using (System.Data.SqlClient.SqlConnection con = new clsSystem_DBConnection(clsSystem_DBConnection.strConnectionString.NavIntegrationDB).propConnection )
{
con.Open();
using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand())
{
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "[SWITCH_Output_Log]";
cmd.Parameters.Add("@SwitchType", System.Data.SqlDbType.VarChar).Value = switchType.ToString();
cmd.Parameters.Add("@SwitchID", System.Data.SqlDbType.Int).Value = SwitchID;
cmd.Parameters.Add("@Filename", System.Data.SqlDbType.VarChar).Value = Filename;
cmd.Parameters.Add("@OutputType", System.Data.SqlDbType.VarChar).Value = outputType.ToString();
cmd.ExecuteNonQuery();
}
}
}
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 List<clsSwitchFee> computePerSwitchFeeReport(int intIFA_ID, string strStartDate, string strEndDate)
{
List<clsSwitchFee> oSwitchFeeList = new List<clsSwitchFee>();
decimal dTotalFee = 0;
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_FeeReportGet";
cmd.Parameters.Add("@param_intIFA_ID", System.Data.SqlDbType.Int).Value = intIFA_ID;
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())
{
clsSwitchFee oSwitchFee = new clsSwitchFee();
//oSwitchFee.propSwitches = Array.ConvertAll(dr["Switches"].ToString().Split(','), s => int.Parse(s));
oSwitchFee.propSwitches = dr["Switches"].ToString();
oSwitchFee.propIFA_ID = (int)dr["IFA_ID"];
oSwitchFee.propIFA_Name = dr["IFA_Name"].ToString();
oSwitchFee.propPer_Switch_Fee = (decimal)dr["Per_Switch_Fee"];
oSwitchFee.propQuantity = (int)dr["Quantity"];
oSwitchFee.propStartDate = DateTime.Parse(dr["StartDate"].ToString());
oSwitchFee.propEndDate = DateTime.Parse(dr["EndDate"].ToString());
dTotalFee = dTotalFee + oSwitchFee.propFees_Due;
oSwitchFee.propTotal_Fee = dTotalFee;
oSwitchFeeList.Add(oSwitchFee);
}
return oSwitchFeeList;
}
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;
}
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 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<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<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 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<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 int insertSwitchHeader(string strPortfolioID, string strClientID, string strUserID, enumSwitchStatus SwitchStatus, Nullable<int> intSwitchID, string strDescription)
{
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_HeaderInsert]";
cmd.Parameters.Add("@param_strPortfolioID", System.Data.SqlDbType.NVarChar).Value = strPortfolioID;
cmd.Parameters.Add("@param_strClientID", System.Data.SqlDbType.NVarChar).Value = strClientID;
cmd.Parameters.Add("@param_intStatus", System.Data.SqlDbType.SmallInt).Value = SwitchStatus;
cmd.Parameters.Add("@param_strCreated_By", System.Data.SqlDbType.NVarChar).Value = strUserID;
cmd.Parameters.Add("@param_intSwitchID", System.Data.SqlDbType.Int).Value = intSwitchID;
cmd.Parameters.Add("@param_strDescription", System.Data.SqlDbType.NVarChar).Value = strDescription;
return int.Parse(cmd.ExecuteScalar().ToString());
}
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 insertSwitchHeaderTemp(int IFA_ID, int intModelID, string strModelGroupID, string strModelPortfolioID, string strClientID, string strPortfolioID, string strUserID)
{
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_Temp_HeaderInsert]";
cmd.Parameters.Add("@param_intIFA_ID", System.Data.SqlDbType.Int).Value = IFA_ID;
cmd.Parameters.Add("@param_ModelID", System.Data.SqlDbType.Int).Value = intModelID;
cmd.Parameters.Add("@param_strModelGroupID", System.Data.SqlDbType.NVarChar).Value = strModelGroupID;
cmd.Parameters.Add("@param_strModelPortfolioID", System.Data.SqlDbType.NVarChar).Value = strModelPortfolioID;
cmd.Parameters.Add("@param_strClientID", System.Data.SqlDbType.NVarChar).Value = strClientID;
cmd.Parameters.Add("@param_strPortfolioID", System.Data.SqlDbType.NVarChar).Value = strPortfolioID;
cmd.Parameters.Add("@param_strUser", System.Data.SqlDbType.NVarChar).Value = strUserID;
cmd.ExecuteNonQuery();
}
public static void updateSwitchHeader(int intSwitchID, enumSwitchStatus SwitchStatus, string strDescription)
{
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_HeaderUpdate]";
cmd.Parameters.Add("@param_intSwitchID", System.Data.SqlDbType.Int).Value = intSwitchID;
cmd.Parameters.Add("@param_Status", System.Data.SqlDbType.SmallInt).Value = SwitchStatus;
cmd.Parameters.Add("@param_Description", System.Data.SqlDbType.NVarChar).Value = strDescription;
cmd.ExecuteNonQuery();
}
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 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 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 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 insertSwitchDetails(int intSwitchID, 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_Customized_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_strPortfolioID", System.Data.SqlDbType.NVarChar).Value = strPortfolioID;
cmd.Parameters.Add("@param_strClientID", System.Data.SqlDbType.NVarChar).Value = strClientID;
//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();
}
