public void CreateCustomerMFTransactionBalance(DataSet dsCustomerMFTransBalanceSellPair, int customerId)
{
string conString;
conString = ConfigurationManager.ConnectionStrings["wealtherp"].ConnectionString;
SqlConnection sqlCon = new SqlConnection(conString);
DataTable dtCustomerMFTransactionBalance = new DataTable();
DataTable dtCustomerMFTransactionSellPair = new DataTable();
try
{
if (dsCustomerMFTransBalanceSellPair.Tables.Count > 0)
{
dtCustomerMFTransactionBalance = dsCustomerMFTransBalanceSellPair.Tables["TransactionBalance"];
dtCustomerMFTransactionSellPair = dsCustomerMFTransBalanceSellPair.Tables["TransactionSellPair"];
}
SqlCommand cmd = new SqlCommand("SPROC_CreateCustomerMFTransactionBalance", sqlCon);
cmd.CommandType = CommandType.StoredProcedure;
//SqlParameter param = new SqlParameter("@BalanceTable", SqlDbType.Structured);
//cmd.Parameters.Add(param);
//SqlDataAdapter da = new SqlDataAdapter(cmd);
// Add the input parameter and set its properties.
SqlParameter sqlParameterBalanceTable = new SqlParameter();
sqlParameterBalanceTable.ParameterName = "@BalanceTableTemp";
sqlParameterBalanceTable.SqlDbType = SqlDbType.Structured;
//parameter.Direction = ParameterDirection.Input;
sqlParameterBalanceTable.Value = dtCustomerMFTransactionBalance;
// Add the parameter to the Parameters collection.
cmd.Parameters.Add(sqlParameterBalanceTable);
// Add the input parameter and set its properties.
SqlParameter sqlParameterSellPair = new SqlParameter();
sqlParameterSellPair.ParameterName = "@TransactionSellPair";
sqlParameterSellPair.SqlDbType = SqlDbType.Structured;
//parameter.Direction = ParameterDirection.Input;
sqlParameterSellPair.Value = dtCustomerMFTransactionSellPair;
// Add the parameter to the Parameters collection.
cmd.Parameters.Add(sqlParameterSellPair);
sqlCon.Open();
cmd.ExecuteNonQuery();
//SqlParameter parameter = new SqlParameter();
////The parameter for the SP must be of SqlDbType.Structured
//parameter.ParameterName = "@Sample";
//parameter.SqlDbType = System.Data.SqlDbType.Structured;
//parameter.Value = dtCustomerMFTransactionBalance;
//command.Parameters.Add(parameter);
//db = DatabaseFactory.CreateDatabase("wealtherp");
//getdtCustomerMFTransactionBalanceCmd = db.GetStoredProcCommand("SPROC_GetCustomerTransactionsForBalanceCreation");
//db.AddInParameter(getCustomerTransactionDetailsCmd, "@CustomerId", DbType.Int32, customerId);
//dsGetCustomerTransactionDetails = db.ExecuteDataSet(getCustomerTransactionDetailsCmd);
}
catch (BaseApplicationException Ex)
{
emailSMSBo.SendErrorExceptionMail(customerId, "", 0, Ex.Message, "MFEngineDao.cs_CreateCustomerMFTransactionBalance");
throw Ex;
}
catch (Exception Ex)
{
BaseApplicationException exBase = new BaseApplicationException(Ex.Message, Ex);
NameValueCollection FunctionInfo = new NameValueCollection();
//FunctionInfo.Add("Method", "MFEngineDao.cs:GetCustomerTransactionsForBalanceCreation()");
//object[] objects = new object[1];
//objects[0] = customerId;
//FunctionInfo = exBase.AddObject(FunctionInfo, objects);
//exBase.AdditionalInformation = FunctionInfo;
//ExceptionManager.Publish(exBase);
//throw exBase;
}
finally
{
sqlCon.Close();
}
}
protected DataTable MFInstantValuation(int accountId, int schemePlanCode, DateTime valuationDate)
{
DataTable dtInstantNetPosition = new DataTable();
DataSet dsMFBalancedSellPairedForNetPosition = new DataSet();
try
{
DateTime dtMinDateTransToBeProcess = new DateTime();
DateTime dtMaxDateTransProcessed = new DateTime();
DataSet dsTransactionBalanceReadyToProcess = new DataSet();
DataTable dtMFTransactionBalance = new DataTable();
DataTable dtMFTransactionProcessedBalance = new DataTable();
DataTable dtMFTransactionProcessedBalanceTemp = new DataTable();
DataTable dtMFTransactionsToProcess = new DataTable();
DataSet dsMFBalanceSellpaired = new DataSet();
DataTable dtMFNetPositionFinalDataTable = new DataTable();
DataTable dtMFTransactionBalanceSellPaired = new DataTable();
dtMFTransactionProcessedBalance = mfEngineBo.CreateTransactionBalanceTable();
dtMFTransactionBalance = mfEngineBo.CreateTransactionBalanceTable();
DataSet dsMFTransactionBalance = mfEngineDao.GetMFTransactionsForBalanceCreation(accountId, schemePlanCode);
if (dtMFTransactionsToProcess != null)
{
dsTransactionBalanceReadyToProcess.Tables.Add(dsMFTransactionBalance.Tables[0].DefaultView.ToTable());
isMFTractionSellPairRecreate = false;
dsTransactionBalanceReadyToProcess.Tables[0].TableName = "Transaction";
//if (commonId == 227617)
//{
//}
if (dsTransactionBalanceReadyToProcess.Tables["Transaction"].Rows.Count > 0)
{
dsMFBalanceSellpaired = mfEngineBo.TransactionBalanceProcess(dsTransactionBalanceReadyToProcess);
}
else
{
dsMFBalanceSellpaired.Tables.Add(dtMFTransactionBalance.DefaultView.ToTable());
dsMFBalanceSellpaired.Tables.Add(dtMFTransactionBalanceSellPaired.DefaultView.ToTable());
}
dsMFBalancedSellPairedForNetPosition = mfEngineBo.CreateMFNetPositionDataTable(dsMFBalanceSellpaired, valuationDate, schemePlanCode);
dtMFNetPositionFinalDataTable = mfEngineBo.CreateMFNetPositionDataTable(dsMFBalancedSellPairedForNetPosition, valuationDate);
//dsMFBalancedSellPairedForNetPosition.Tables.Add(dsMFBalanceSellpaired.Tables[0].DefaultView.ToTable());
//dsMFBalancedSellPairedForNetPosition.Tables.Add(dsMFBalanceSellpaired.Tables[1].DefaultView.ToTable());
dtInstantNetPosition.Merge(dtMFNetPositionFinalDataTable);
}
}
catch (BaseApplicationException Ex)
{
emailSMSBo.SendErrorExceptionMail(accountId, "AccountId", schemePlanCode, Ex.Message, "MFInstantValuationBo.Cs_MFInstantValuation");
throw Ex;
}
catch (Exception Ex)
{
BaseApplicationException exBase = new BaseApplicationException(Ex.Message, Ex);
NameValueCollection FunctionInfo = new NameValueCollection();
FunctionInfo.Add("Method", "MFEngineBo.cs:MFBalanceCreation()");
object[] objects = new object[3];
objects[0] = accountId;
objects[1] = schemePlanCode;
FunctionInfo = exBase.AddObject(FunctionInfo, objects);
exBase.AdditionalInformation = FunctionInfo;
ExceptionManager.Publish(exBase);
throw exBase;
}
return(dtInstantNetPosition);
}
public void MFNetPositionCreation(int commonId, int schemePlanCode, ValuationLabel startFrom, DateTime valuationDate, int iSForPreviousDate)
{
try
{
switch (startFrom.ToString())
{
case "Advisor":
{
adviserId = commonId;
AdviserCustomers = mfEngineDao.GetAdviserCustomerList_MF(commonId);
if (AdviserCustomers != null)
{
foreach (int customerId in AdviserCustomers)
{
//if (customerId == 85747)
//{
//}
MFNetPositionCreation(customerId, 0, ValuationLabel.Customer, valuationDate, iSForPreviousDate);
}
if (dtCustomerMutualFundNetPosition.Rows.Count > 0)
{
mfEngineDao.CreateAdviserMFNetPosition(adviserId, valuationDate, dtCustomerMutualFundNetPosition, iSForPreviousDate);
}
dtCustomerMutualFundNetPosition.Clear();
}
break;
}
case "Customer":
{
dsCustomerTransactionsDetails = mfEngineDao.GetCustomerTransactionsForBalanceCreation(commonId, valuationDate);
dtCustomerPortfolio = dsCustomerTransactionsDetails.Tables[0];
dtCustomerAccount = dsCustomerTransactionsDetails.Tables[1];
dtCustomerTransactionsToProcess = dsCustomerTransactionsDetails.Tables[2];
dtCustomerTransactionBalance = mfEngineBo.CreateTransactionBalanceTable();
// dtCustomerTransactionBalance = dsCustomerTransactionsDetails.Tables[3];
dtCustomerMFTransactionSellPaired = mfEngineBo.CreateSellPairedTable();
dtFinalCustomerMFTransactionBalance = mfEngineBo.CreateTransactionBalanceTable();
if (dtCustomerPortfolio.Rows.Count > 0)
{
foreach (DataRow drProftfolio in dtCustomerPortfolio.Rows)
{
MFNetPositionCreation(Convert.ToInt32(drProftfolio["CP_PortfolioId"].ToString()), 0, ValuationLabel.Portfolio, valuationDate, iSForPreviousDate);
}
}
break;
}
case "Portfolio":
{
if (dtCustomerAccount != null)
{
if (dtCustomerAccount.Rows.Count > 0)
{
dtCustomerAccount.DefaultView.RowFilter = "CP_PortfolioId=" + commonId.ToString();
dtMFAccount = dtCustomerAccount.DefaultView.ToTable();
foreach (DataRow drCustomerMFAccount in dtMFAccount.Rows)
{
MFNetPositionCreation(Convert.ToInt32(drCustomerMFAccount["CMFA_AccountId"].ToString()), Convert.ToInt32(drCustomerMFAccount["PASP_SchemePlanCode"].ToString()), ValuationLabel.AccountScheme, valuationDate, iSForPreviousDate);
}
}
}
break;
}
case "AccountScheme":
{
DateTime dtMinDateTransToBeProcess = new DateTime();
DateTime dtMaxDateTransProcessed = new DateTime();
DataSet dsTransactionBalanceReadyToProcess = new DataSet();
DataTable dtMFTransactionBalance = new DataTable();
DataTable dtMFTransactionProcessedBalance = new DataTable();
DataSet dsMFTransactionProcessedBalance = new DataSet();
DataTable dtMFTransactionProcessedBalanceTemp = new DataTable();
DataTable dtMfNetPositionFinalDataTable = new DataTable();
DataSet dsMfBalancedSellPairedForNetPosition = new DataSet();
//dtMFTransactionProcessedBalance = mfEngineBo.CreateTransactionBalanceTable();
//dtMFTransactionBalance = mfEngineBo.CreateTransactionBalanceTable();
if (dtCustomerTransactionsToProcess != null)
{
if (dtCustomerTransactionsToProcess.Rows.Count > 0)
{
dtCustomerTransactionsToProcess.DefaultView.RowFilter = "CMFA_AccountId=" + commonId.ToString() + " AND " + "PASP_SchemePlanCode=" + schemePlanCode.ToString();
dtMFTransactionsToProcess = dtCustomerTransactionsToProcess.DefaultView.ToTable();
////DataView dvMFTransactionsProcessed = new DataView(dtMFTransactionsToProcess, "CMFT_IsValued='1'", "CMFT_TransactionDate", DataViewRowState.CurrentRows);
////DataView dvMFTransactionsToBeProcess = new DataView(dtMFTransactionsToProcess, "CMFT_IsValued='0'", "CMFT_TransactionDate", DataViewRowState.CurrentRows);
//dvMFTransactionsProcessed.RowFilter = "CMFT_IsValued='1'";
//dvMFTransactionsToBeProcess.RowFilter = "CMFT_IsValued='0'";
//if (dvMFTransactionsToBeProcess.ToTable().Rows.Count > 0)
//{
// dtMinDateTransToBeProcess = Convert.ToDateTime((dvMFTransactionsToBeProcess.ToTable().Compute("Min(CMFT_TransactionDate)", string.Empty)));
//}
//if (dvMFTransactionsProcessed.ToTable().Rows.Count > 0)
//{
// dtMaxDateTransProcessed = Convert.ToDateTime((dvMFTransactionsProcessed.ToTable().Compute("Max(CMFT_TransactionDate)", string.Empty)));
//}
//dtMinDateForNotBalancedCreated=dtMFTransactionsToProcess.
//if (dtCustomerTransactionBalance != null)
//{
// if (dtCustomerTransactionBalance.Rows.Count > 0)
// {
// dtCustomerTransactionBalance.DefaultView.RowFilter = "CMFA_AccountId=" + commonId.ToString() + " AND " + "PASP_SchemePlanCode=" + schemePlanCode.ToString();
// dtMFTransactionBalance = dtCustomerTransactionBalance.DefaultView.ToTable();
// }
//}
//if (dtMinDateTransToBeProcess != DateTime.MinValue && dtMaxDateTransProcessed != DateTime.MinValue && (dtMinDateTransToBeProcess < dtMaxDateTransProcessed))
//{
// isMFTractionSellPairRecreate = true;
// dsTransactionBalanceReadyToProcess.Tables.Add(dtMFTransactionsToProcess);
// if (dtMFTransactionBalance.TableName != "")
// {
// DataColumn dcInsertUpdate = new DataColumn("CMFTB_InsertUpdate_Flag");
// dcInsertUpdate.DataType = typeof(int);
// dcInsertUpdate.DefaultValue = 3; //3 is used to delete the balanced record from TransactionBalanced Table
// dtMFTransactionBalance.Columns.Remove("CMFTB_InsertUpdate_Flag");
// dtMFTransactionBalance.Columns.Add(dcInsertUpdate);
// }
//}
//else
//{
isMFTractionSellPairRecreate = false;
dsTransactionBalanceReadyToProcess.Tables.Add(dtMFTransactionsToProcess);
// dsTransactionBalanceReadyToProcess.Tables.Add(dtMFTransactionBalance);
//dsTransactionBalanceReadyToProcess.Tables[1].TableName = "Balance";
//}
//if (commonId == 90012)
//{
//}
dsTransactionBalanceReadyToProcess.Tables[0].TableName = "Transaction";
//if (commonId == 227617)
//{
//}
if (dsTransactionBalanceReadyToProcess.Tables["Transaction"].Rows.Count > 0)
{
dsMFTransactionProcessedBalance = mfEngineBo.TransactionBalanceProcess(dsTransactionBalanceReadyToProcess);
//For creating DataTable As per Stored Procedure
dsMfBalancedSellPairedForNetPosition = mfEngineBo.CreateMFNetPositionDataTable(dsMFTransactionProcessedBalance, valuationDate, schemePlanCode);
//For creating NetPosition details
dtMfNetPositionFinalDataTable = mfEngineBo.CreateMFNetPositionDataTable(dsMfBalancedSellPairedForNetPosition, valuationDate);
//dtCustomerMutualFundNetPosition.Merge(dtMFAccountSchemeNetPosition);
if (dtMfNetPositionFinalDataTable.Rows.Count > 0)
{
dtCustomerMutualFundNetPosition.Merge(dtMfNetPositionFinalDataTable);
}
dtMfNetPositionFinalDataTable.Clear();
}
//if (dtMinDateTransToBeProcess != DateTime.MinValue && dtMaxDateTransProcessed != DateTime.MinValue && (dtMinDateTransToBeProcess < dtMaxDateTransProcessed))
//{
// dtMFTransactionProcessedBalance.Merge(dtMFTransactionBalance, false, MissingSchemaAction.Ignore);
//}
//if (dtMFTransactionProcessedBalance.Rows.Count > 0)
// dtFinalCustomerMFTransactionBalance.Merge(dtMFTransactionProcessedBalance, false, MissingSchemaAction.Ignore);
//dtMFTransactionProcessedBalance.Clear();
//dtMFTransactionBalance.Clear();
// dtCustomerMFTransactionSellPaired.Merge(dtMFTrasactionSellPair);
// dtMFTrasactionSellPair.Clear();
}
}
break;
}
}
}
catch (BaseApplicationException Ex)
{
emailSMSBo.SendErrorExceptionMail(adviserId, startFrom.ToString(), schemePlanCode, Ex.Message, "MFHistoricalValuationBo.Cs_MFNetPositionCreation");
throw Ex;
}
catch (Exception Ex)
{
BaseApplicationException exBase = new BaseApplicationException(Ex.Message, Ex);
NameValueCollection FunctionInfo = new NameValueCollection();
FunctionInfo.Add("Method", "MFHistoricalValuationBo.cs:MFNetPositionCreation()");
object[] objects = new object[3];
objects[0] = commonId;
objects[1] = schemePlanCode;
objects[2] = startFrom;
FunctionInfo = exBase.AddObject(FunctionInfo, objects);
exBase.AdditionalInformation = FunctionInfo;
ExceptionManager.Publish(exBase);
throw exBase;
}
}