//Fifth Phase: Move WerpMF customer details to customer table
public bool WerpMFInsertCustomerDetails(int adviserId, int processId, int rmId, out int countCustCreated, out int countFolioCreated)
{
bool IsProcessComplete = false;
List <WerpMFUploadsVo> werpMFNewCustomerList = new List <WerpMFUploadsVo>();
Nullable <DateTime> dt = new DateTime();
WerpMFUploadsVo werpMFUploadsVo = new WerpMFUploadsVo();
string resIsdCode = "", resStdCode = "", resPhoneNum = "", offIsdCode = "", offStdCode = "", offPhoneNum = "";
string resFaxIsdCode = "", resFaxStdCode = "", resFaxNum = "", offFaxIsdCode = "", offFaxStdCode = "", offFaxNum = "";
int lenPhoneNum;
int lenFaxNum;
countCustCreated = 0;
countFolioCreated = 0;
DataSet getNewFoliosDs = new DataSet();
DataTable getNewFoliosDt = new DataTable();
userId = advisorstaffBo.GetUserId(rmId);
try
{
werpMFNewCustomerList = GetWerpMFProfNewCustomers(processId);
for (int i = 0; i < werpMFNewCustomerList.Count; i++)
{
customerVo = new CustomerVo();
userVo = new UserVo();
werpMFUploadsVo = new WerpMFUploadsVo();
werpMFUploadsVo = werpMFNewCustomerList[i];
userVo.FirstName = werpMFUploadsVo.FirstName;
userVo.MiddleName = werpMFUploadsVo.MiddleName;
userVo.LastName = werpMFUploadsVo.LastName;
userVo.Password = id.Next(10000, 99999).ToString();
userVo.Email = werpMFUploadsVo.Email;
userVo.LoginId = werpMFUploadsVo.Email;
userVo.UserType = "Customer";
//userId = userBo.CreateUser(userVo);
customerVo.UserId = userId;
customerVo.RmId = rmId;
customerVo.FirstName = werpMFUploadsVo.FirstName;
customerVo.MiddleName = werpMFUploadsVo.MiddleName;
customerVo.LastName = werpMFUploadsVo.LastName;
customerVo.Gender = werpMFUploadsVo.Gender;
if (werpMFUploadsVo.DOB == "")
{
customerVo.Dob = DateTime.Parse(dt.ToString());
}
else
{
customerVo.Dob = DateTime.Parse(werpMFUploadsVo.DOB);
}
if (werpMFUploadsVo.Type != "")
{
customerVo.Type = werpMFUploadsVo.Type;
}
if (werpMFUploadsVo.SubType != "")
{
customerVo.SubType = werpMFUploadsVo.SubType;
}
customerVo.Salutation = werpMFUploadsVo.Salutation;
customerVo.PANNum = werpMFUploadsVo.PanNumber;
customerVo.Adr1Line1 = werpMFUploadsVo.Address1;
customerVo.Adr1Line2 = werpMFUploadsVo.Address2;
customerVo.Adr1Line3 = werpMFUploadsVo.Address3;
if (werpMFUploadsVo.Pincode == "")
{
customerVo.Adr1PinCode = 0;
}
else
{
customerVo.Adr1PinCode = Int32.Parse(werpMFUploadsVo.Pincode);
}
customerVo.Adr1City = werpMFUploadsVo.City;
customerVo.Adr1State = werpMFUploadsVo.State;
customerVo.Adr1Country = werpMFUploadsVo.Country;
customerVo.Adr2Line1 = werpMFUploadsVo.Address2Line1;
customerVo.Adr2Line2 = werpMFUploadsVo.Address2Line2;
customerVo.Adr2Line3 = werpMFUploadsVo.Address2Line3;
if (werpMFUploadsVo.Address2Pincode == "")
{
customerVo.Adr2PinCode = 0;
}
else
{
customerVo.Adr2PinCode = Int32.Parse(werpMFUploadsVo.Address2Pincode);
}
customerVo.Adr2City = werpMFUploadsVo.Address2City;
customerVo.Adr2Country = werpMFUploadsVo.Address2Country;
customerVo.Adr2State = werpMFUploadsVo.Address2State;
customerVo.Email = werpMFUploadsVo.Email;
customerVo.AltEmail = werpMFUploadsVo.AltEmail;
if (werpMFUploadsVo.Mobile1 == "")
{
customerVo.Mobile1 = 0;
}
else
{
customerVo.Mobile1 = Int32.Parse(werpMFUploadsVo.Mobile1);
}
if (werpMFUploadsVo.Mobile2 == "")
{
customerVo.Mobile2 = 0;
}
else
{
customerVo.Mobile2 = Int32.Parse(werpMFUploadsVo.Mobile2);
}
if (werpMFUploadsVo.Occupation != "")
{
customerVo.Occupation = werpMFUploadsVo.Occupation;
}
if (werpMFUploadsVo.Qualification != "")
{
customerVo.Qualification = werpMFUploadsVo.Qualification;
}
//customerVo.MarriageDate
if (werpMFUploadsVo.MaritalStatus != "")
{
customerVo.MaritalStatus = werpMFUploadsVo.MaritalStatus;
}
if (werpMFUploadsVo.Nationality != "")
{
customerVo.Nationality = werpMFUploadsVo.Nationality;
}
customerVo.RBIRefNum = werpMFUploadsVo.RBIRefNum;
if (werpMFUploadsVo.RBIApprovalDate == "")
{
customerVo.RBIApprovalDate = DateTime.Parse(dt.ToString());
}
else
{
customerVo.RBIApprovalDate = DateTime.Parse(werpMFUploadsVo.RBIApprovalDate.ToString());
}
customerVo.CompanyName = werpMFUploadsVo.CompanyName;
customerVo.OfcAdrLine1 = werpMFUploadsVo.OfcAddress1;
customerVo.OfcAdrLine2 = werpMFUploadsVo.OfcAddress2;
customerVo.OfcAdrLine3 = werpMFUploadsVo.OfcAddress3;
if (werpMFUploadsVo.OfcAddressPincode == "")
{
customerVo.OfcAdrPinCode = 0;
}
else
{
customerVo.OfcAdrPinCode = Int32.Parse(werpMFUploadsVo.OfcAddressPincode);
}
customerVo.OfcAdrState = werpMFUploadsVo.State;
customerVo.OfcAdrCity = werpMFUploadsVo.OfcAddressCity;
customerVo.OfcAdrCountry = werpMFUploadsVo.OfcAddressCountry;
if (werpMFUploadsVo.RegistrationDate == "")
{
customerVo.RegistrationDate = DateTime.Parse(dt.ToString());
}
else
{
customerVo.RegistrationDate = DateTime.Parse(werpMFUploadsVo.RegistrationDate.ToString());
}
if (werpMFUploadsVo.CommencementDate == "")
{
customerVo.CommencementDate = DateTime.Parse(dt.ToString());
}
else
{
customerVo.CommencementDate = DateTime.Parse(werpMFUploadsVo.CommencementDate.ToString());
}
customerVo.RegistrationPlace = werpMFUploadsVo.RegistrationPlace;
customerVo.RegistrationNum = werpMFUploadsVo.ResPhoneNum;
customerVo.CompanyWebsite = werpMFUploadsVo.CompanyWebsite;
customerVo.LoginId = werpMFUploadsVo.Email;
customerVo.Password = id.Next(10000, 99999).ToString();
//**************************************************************************
//Office phone number
lenPhoneNum = werpMFUploadsVo.OfcPhoneNum.Length;
if (lenPhoneNum >= 8)
{
offPhoneNum = werpMFUploadsVo.OfcPhoneNum.Substring(lenPhoneNum - 8, 8);
if (lenPhoneNum >= 11)
{
offStdCode = werpMFUploadsVo.OfcPhoneNum.Substring(lenPhoneNum - 11, 3);
if (lenPhoneNum >= 12)
{
offIsdCode = werpMFUploadsVo.OfcPhoneNum.Substring(0, lenPhoneNum - 11);
}
}
else
{
offStdCode = werpMFUploadsVo.OfcPhoneNum.Substring(0, lenPhoneNum - 8);
}
}
else
{
offPhoneNum = werpMFUploadsVo.OfcPhoneNum;
}
if (offIsdCode != "")
{
customerVo.OfcISDCode = Int32.Parse(offIsdCode);
}
else
{
customerVo.OfcISDCode = 0;
}
if (offStdCode != "")
{
customerVo.OfcSTDCode = Int32.Parse(offStdCode);
}
else
{
customerVo.OfcSTDCode = 0;
}
if (offPhoneNum != "")
{
customerVo.OfcPhoneNum = Int32.Parse(offPhoneNum);
}
else
{
customerVo.OfcPhoneNum = 0;
}
//**************************************************************************
//residence phone number
lenPhoneNum = werpMFUploadsVo.ResPhoneNum.Length;
if (lenPhoneNum >= 8)
{
resPhoneNum = werpMFUploadsVo.ResPhoneNum.Substring(lenPhoneNum - 8, 8);
if (lenPhoneNum >= 11)
{
resStdCode = werpMFUploadsVo.ResPhoneNum.Substring(lenPhoneNum - 11, 3);
if (lenPhoneNum >= 12)
{
resIsdCode = werpMFUploadsVo.ResPhoneNum.Substring(0, lenPhoneNum - 11);
}
}
else
{
resStdCode = werpMFUploadsVo.ResPhoneNum.Substring(0, lenPhoneNum - 8);
}
}
else
{
resPhoneNum = werpMFUploadsVo.ResPhoneNum;
}
if (resIsdCode != "")
{
customerVo.ResISDCode = Int32.Parse(resIsdCode);
}
else
{
customerVo.ResISDCode = 0;
}
if (resStdCode != "")
{
customerVo.ResSTDCode = Int32.Parse(resStdCode);
}
else
{
customerVo.ResSTDCode = 0;
}
if (resPhoneNum != "")
{
customerVo.ResPhoneNum = Int32.Parse(resPhoneNum);
}
else
{
customerVo.ResPhoneNum = 0;
}
//**************************************************************************
//**************************************************************************
//Fax residence
lenFaxNum = werpMFUploadsVo.Fax.Length;
if (lenFaxNum >= 8)
{
resFaxNum = werpMFUploadsVo.Fax.Substring(lenFaxNum - 8, 8);
if (lenFaxNum >= 11)
{
resFaxStdCode = werpMFUploadsVo.Fax.Substring(lenFaxNum - 11, 3);
if (lenFaxNum >= 12)
{
resFaxIsdCode = werpMFUploadsVo.Fax.Substring(0, lenFaxNum - 11);
}
}
else
{
resFaxStdCode = werpMFUploadsVo.Fax.Substring(0, lenFaxNum - 8);
}
}
else
{
resFaxNum = werpMFUploadsVo.Fax;
}
if (resFaxIsdCode != "")
{
customerVo.ISDFax = Int32.Parse(resFaxIsdCode);
}
else
{
customerVo.ISDFax = 0;
}
if (resFaxStdCode != "")
{
customerVo.STDFax = Int32.Parse(resFaxStdCode);
}
else
{
customerVo.STDFax = 0;
}
if (resFaxNum != "")
{
customerVo.Fax = Int32.Parse(resFaxNum);
}
else
{
customerVo.Fax = 0;
}
//**************************************************************************
//Fax Office
lenFaxNum = werpMFUploadsVo.OfcFax.Length;
if (lenFaxNum >= 8)
{
offFaxNum = werpMFUploadsVo.OfcFax.Substring(lenFaxNum - 8, 8);
if (lenFaxNum >= 11)
{
offFaxStdCode = werpMFUploadsVo.OfcFax.Substring(lenFaxNum - 11, 3);
if (lenFaxNum >= 12)
{
offFaxIsdCode = werpMFUploadsVo.OfcFax.Substring(0, lenFaxNum - 11);
}
}
else
{
offFaxStdCode = werpMFUploadsVo.OfcFax.Substring(0, lenFaxNum - 8);
}
}
else
{
offFaxNum = werpMFUploadsVo.OfcFax;
}
if (offFaxIsdCode != "")
{
customerVo.OfcISDFax = Int32.Parse(offFaxIsdCode);
}
else
{
customerVo.OfcISDFax = 0;
}
if (offFaxStdCode != "")
{
customerVo.OfcSTDFax = Int32.Parse(offFaxStdCode);
}
else
{
customerVo.OfcSTDFax = 0;
}
if (offFaxNum != "")
{
customerVo.OfcFax = Int32.Parse(offFaxNum);
}
else
{
customerVo.OfcFax = 0;
}
//customerId2 = customerBo.CreateCustomer(customerVo, rmId, userId);
//customerPortfolioVo.CustomerId = customerId2;
customerPortfolioVo.IsMainPortfolio = 1;
customerPortfolioVo.PortfolioTypeCode = "RGL";
customerPortfolioVo.PortfolioName = "MyPortfolio";
//PortfolioBo.CreateCustomerPortfolio(customerPortfolioVo, userId);
customerBo.CreateCompleteCustomer(customerVo, userVo, customerPortfolioVo, userId);
countCustCreated++;
}
UpdateWerpMFProfileStagingIsCustomerNew(adviserId, processId);
//*****New Folios Upload from CAMS*****
getNewFoliosDs = GetWerpMFProfileNewFolios(processId);
getNewFoliosDt = getNewFoliosDs.Tables[0];
foreach (DataRow dr in getNewFoliosDt.Rows)
{
customerAccountsVo.AccountNum = dr["CMFXPS_FolioNum"].ToString();
customerAccountsVo.PortfolioId = Int32.Parse(dr["CP_PortfolioId"].ToString());
customerAccountsVo.AssetClass = "MF";
customerAccountsVo.AMCCode = Int32.Parse(dr["PA_AMCCode"].ToString());
customerAccountBo.CreateCustomerMFAccount(customerAccountsVo, userId);
countFolioCreated++;
}
UpdateWerpMFProfileStagingIsFolioNew(processId);
IsProcessComplete = true;
}
catch (BaseApplicationException Ex)
{
throw Ex;
}
catch (Exception Ex)
{
BaseApplicationException exBase = new BaseApplicationException(Ex.Message, Ex);
NameValueCollection FunctionInfo = new NameValueCollection();
FunctionInfo.Add("Method", "WerpMFUploadsBo.cs:WerpMFInsertCustomerDetails()");
object[] objects = new object[3];
objects[0] = adviserId;
objects[1] = processId;
objects[2] = rmId;
FunctionInfo = exBase.AddObject(FunctionInfo, objects);
exBase.AdditionalInformation = FunctionInfo;
ExceptionManager.Publish(exBase);
throw exBase;
}
return(IsProcessComplete);
}
public List <WerpMFUploadsVo> GetWerpMFProfNewCustomers(int processId)
{
List <WerpMFUploadsVo> uploadsCustomerList = new List <WerpMFUploadsVo>();
WerpMFUploadsVo WerpMFUploadsVo;
Database db;
DbCommand getNewCustomersCmd;
DataSet getNewCustomersDs;
try
{
db = DatabaseFactory.CreateDatabase("wealtherp");
getNewCustomersCmd = db.GetStoredProcCommand("SP_UploadGetNewCustomersWerpMFProf");
db.AddInParameter(getNewCustomersCmd, "@processId", DbType.Int32, processId);
getNewCustomersDs = db.ExecuteDataSet(getNewCustomersCmd);
//dr = getNewCustomersDs.Tables[0].Rows[0];
foreach (DataRow dr in getNewCustomersDs.Tables[0].Rows)
{
WerpMFUploadsVo = new WerpMFUploadsVo();
WerpMFUploadsVo.FirstName = dr["CMFXPS_FirstName"].ToString();
WerpMFUploadsVo.MiddleName = dr["CMFXPS_MiddleName"].ToString();
WerpMFUploadsVo.LastName = dr["CMFXPS_LastName"].ToString();
WerpMFUploadsVo.Gender = dr["CMFXPS_Gender"].ToString();
WerpMFUploadsVo.DOB = dr["CMFXPS_DOB"].ToString();
WerpMFUploadsVo.Type = dr["CMFXPS_Type"].ToString();
WerpMFUploadsVo.SubType = dr["CMFXPS_SubType"].ToString();
WerpMFUploadsVo.Salutation = dr["CMFXPS_Salutation"].ToString();
WerpMFUploadsVo.PanNumber = dr["CMFXPS_PANNum"].ToString();
WerpMFUploadsVo.Address1 = dr["CMFXPS_Adr1Line1"].ToString();
WerpMFUploadsVo.Address2 = dr["CMFXPS_Adr1Line2"].ToString();
WerpMFUploadsVo.Address3 = dr["CMFXPS_Adr1Line3"].ToString();
WerpMFUploadsVo.City = dr["CMFXPS_Adr1City"].ToString();
WerpMFUploadsVo.Pincode = dr["CMFXPS_Adr1PinCode"].ToString();
WerpMFUploadsVo.State = dr["CMFXPS_Adr1State"].ToString();
WerpMFUploadsVo.Country = dr["CMFXPS_Adr1Country"].ToString();
WerpMFUploadsVo.Address2Line1 = dr["CMFXPS_Adr2Line1"].ToString();
WerpMFUploadsVo.Address2Line2 = dr["CMFXPS_Adr2Line2"].ToString();
WerpMFUploadsVo.Address2Line3 = dr["CMFXPS_Adr2Line3"].ToString();
WerpMFUploadsVo.Address2City = dr["CMFXPS_Adr2City"].ToString();
WerpMFUploadsVo.Address2Pincode = dr["CMFXPS_Adr2PinCode"].ToString();
WerpMFUploadsVo.Address2State = dr["CMFXPS_Adr2State"].ToString();
WerpMFUploadsVo.Address2Country = dr["CMFXPS_Adr2Country"].ToString();
WerpMFUploadsVo.ResISDCode = dr["CMFXPS_ResISDCode"].ToString();
WerpMFUploadsVo.ResSTDCode = dr["CMFXPS_ResSTDCode"].ToString();
WerpMFUploadsVo.ResPhoneNum = dr["CMFXPS_ResPhoneNum"].ToString();
WerpMFUploadsVo.OfcISDCode = dr["CMFXPS_OfcISDCode"].ToString();
WerpMFUploadsVo.OfcSTDCode = dr["CMFXPS_OfcSTDCode"].ToString();
WerpMFUploadsVo.OfcPhoneNum = dr["CMFXPS_OfcPhoneNum"].ToString();
WerpMFUploadsVo.Email = dr["CMFXPS_Email"].ToString();
WerpMFUploadsVo.AltEmail = dr["CMFXPS_AltEmail"].ToString();
WerpMFUploadsVo.Mobile1 = dr["CMFXPS_Mobile1"].ToString();
WerpMFUploadsVo.Mobile2 = dr["CMFXPS_Mobile2"].ToString();
WerpMFUploadsVo.FaxISD = dr["CMFXPS_ISDFax"].ToString();
WerpMFUploadsVo.FaxSTD = dr["CMFXPS_STDFax"].ToString();
WerpMFUploadsVo.Fax = dr["CMFXPS_Fax"].ToString();
WerpMFUploadsVo.OfcFax = dr["CMFXPS_OfcFax"].ToString();
WerpMFUploadsVo.OfcFaxISD = dr["CMFXPS_OfcFaxISD"].ToString();
WerpMFUploadsVo.OfcFaxSTD = dr["CMFXPS_OfcFaxSTD"].ToString();
WerpMFUploadsVo.Occupation = dr["CMFXPS_Occupation"].ToString();
WerpMFUploadsVo.Qualification = dr["CMFXPS_Qualification"].ToString();
WerpMFUploadsVo.MarriageDate = dr["CMFXPS_MarriageDate"].ToString();
WerpMFUploadsVo.MaritalStatus = dr["CMFXPS_MaritalStatus"].ToString();
WerpMFUploadsVo.Nationality = dr["CMFXPS_Nationality"].ToString();
WerpMFUploadsVo.RBIRefNum = dr["CMFXPS_RBIRefNum"].ToString();
WerpMFUploadsVo.RBIApprovalDate = dr["CMFXPS_RBIApprovalDate"].ToString();
WerpMFUploadsVo.CompanyName = dr["CMFXPS_CompanyName"].ToString();
WerpMFUploadsVo.OfcAddress1 = dr["CMFXPS_OfcAdrLine1"].ToString();
WerpMFUploadsVo.OfcAddress2 = dr["CMFXPS_OfcAdrLine2"].ToString();
WerpMFUploadsVo.OfcAddress3 = dr["CMFXPS_OfcAdrLine3"].ToString();
WerpMFUploadsVo.OfcAddressPincode = dr["CMFXPS_OfcAdrPinCode"].ToString();
WerpMFUploadsVo.OfcAddressCity = dr["CMFXPS_OfcAdrCity"].ToString();
WerpMFUploadsVo.OfcAddressState = dr["CMFXPS_OfcAdrState"].ToString();
WerpMFUploadsVo.OfcAddressCountry = dr["CMFXPS_OfcAdrCountry"].ToString();
WerpMFUploadsVo.RegistrationDate = dr["CMFXPS_RegistrationDate"].ToString();
WerpMFUploadsVo.CommencementDate = dr["CMFXPS_CommencementDate"].ToString();
WerpMFUploadsVo.RegistrationPlace = dr["CMFXPS_RegistrationPlace"].ToString();
WerpMFUploadsVo.RegistrationNum = dr["CMFXPS_RegistrationNum"].ToString();
WerpMFUploadsVo.CompanyWebsite = dr["CMFXPS_CompanyWebsite"].ToString();
//WerpMFUploadsVo.BankName = dr["CMFXPS_BankName"].ToString();
//WerpMFUploadsVo.AccountType = dr["CMFXPS_AccountType"].ToString();
//WerpMFUploadsVo.AccountNum = dr["CMFXPS_AccountNum"].ToString();
//WerpMFUploadsVo.BankModeOfOperation = dr["CMFXPS_BankModeOfOperation"].ToString();
//WerpMFUploadsVo.BranchName = dr["CMFXPS_BranchName"].ToString();
//WerpMFUploadsVo.BranchAddress1 = dr["CMFXPS_BranchAdrLine1"].ToString();
//WerpMFUploadsVo.BranchAddress2 = dr["CMFXPS_BranchAdrLine2"].ToString();
//WerpMFUploadsVo.BranchAddress3 = dr["CMFXPS_BranchAdrLine3"].ToString();
//WerpMFUploadsVo.BranchAddressPincode = dr["CMFXPS_BranchAdrPinCode"].ToString();
//WerpMFUploadsVo.BranchAddressCity = dr["CMFXPS_BranchAdrCity"].ToString();
//WerpMFUploadsVo.BranchAddressState = dr["CMFXPS_BranchAdrState"].ToString();
//WerpMFUploadsVo.BranchAddressCountry = dr["CMFXPS_BranchAdrCountry"].ToString();
//WerpMFUploadsVo.MICR = dr["CMFXPS_MICR"].ToString();
//WerpMFUploadsVo.IFSC = dr["CMFXPS_IFSC"].ToString();
//WerpMFUploadsVo.FolioNum = dr["CMFXPS_FolioNum"].ToString();
//WerpMFUploadsVo.AccountOpeningDate = dr["CMFXPS_AccountOpeningDate"].ToString();
//WerpMFUploadsVo.FolioModeOfOperating = dr["CMFXPS_FolioModeOfOperating"].ToString();
uploadsCustomerList.Add(WerpMFUploadsVo);
}
}
catch (BaseApplicationException Ex)
{
throw Ex;
}
catch (Exception Ex)
{
BaseApplicationException exBase = new BaseApplicationException(Ex.Message, Ex);
NameValueCollection FunctionInfo = new NameValueCollection();
FunctionInfo.Add("Method", "WerpMFUploadsDao.cs:GetWerpMFProfNewCustomers()");
object[] objects = new object[1];
objects[0] = processId;
FunctionInfo = exBase.AddObject(FunctionInfo, objects);
exBase.AdditionalInformation = FunctionInfo;
ExceptionManager.Publish(exBase);
throw exBase;
}
return(uploadsCustomerList);
}
