public static List<DeepBlue.Models.Entity.UnderlyingFund> ConvertBlueToDeepBlue()
{
Errors = new List<KeyValuePair<C7_10tblGPPaymentInstructions, Exception>>();
TotalConversionRecords = 0;
RecordsConvertedSuccessfully = 0;
List<DeepBlue.Models.Entity.UnderlyingFund> dbFunds = new List<DeepBlue.Models.Entity.UnderlyingFund>();
using (BlueEntities context = new BlueEntities()) {
List<C7_10tblGPPaymentInstructions> uFunds = context.C7_10tblGPPaymentInstructions.Where(x=>x.FundType == "Direct").ToList();
foreach (C7_10tblGPPaymentInstructions uf in uFunds) {
try {
TotalConversionRecords++;
DeepBlue.Models.Entity.UnderlyingFund deepBlueFund = GetUnderlyingFundFromBlue(uf);
dbFunds.Add(deepBlueFund);
RecordsConvertedSuccessfully++;
}
catch (Exception ex) {
Errors.Add(new KeyValuePair<C7_10tblGPPaymentInstructions, Exception>(uf, ex));
}
}
}
return dbFunds;
}
private static UnderlyingFundCashDistribution GetCashDistributionFromBlue(C1_10tblDistToAmberbrookCash blueCashDist, decimal amount, BlueEntities context, CookieCollection cookies, out string resp)
{
resp = string.Empty;
UnderlyingFundCashDistribution deepBlueCD = new UnderlyingFundCashDistribution();
C6_10AmberbrookFundInfo ambFundInfo = context.C6_10AmberbrookFundInfo.Where(x => x.AmberbrookFundNo == blueCashDist.AmberbrookFundNo).FirstOrDefault();
if (ambFundInfo != null) {
Fund fund = FundImport.GetFund(ambFundInfo.AmberbrookFundName, cookies);
if (fund != null) {
deepBlueCD.FundID = fund.FundID;
}
else {
resp = "Unable to find AMB Fund: " + ambFundInfo.AmberbrookFundName;
return null;
}
}
else {
resp = "Unable to find AMB Fund#: " + blueCashDist.AmberbrookFundNo;
return null;
}
List<DeepBlue.Models.Deal.UnderlyingFundListModel> underlyingFunds = GetUnderlyingFunds(cookies);
DeepBlue.Models.Deal.UnderlyingFundListModel uf = underlyingFunds.Where(x => x.FundName == blueCashDist.Fund).FirstOrDefault();
if (uf != null) {
deepBlueCD.UnderlyingFundID = uf.UnderlyingFundId;
}
else {
resp = "Unable to find Underlying fund: " + blueCashDist.Fund;
Util.WriteMissingUnderlyingFund(blueCashDist.Fund);
Util.Log(resp);
return null;
}
deepBlueCD.Amount = amount;
// On the UI, this field is labelled Due Date
if (blueCashDist.NoticeDate.HasValue)
deepBlueCD.NoticeDate = blueCashDist.NoticeDate.Value.Date;
// PRDCD is stored in the 1-30tblpostrecorddatetransactions table(Transaction type = cash distribution). so we assuming all the calls here are non-prdcc
deepBlueCD.IsPostRecordDateTransaction = false;
// We dont need to provider the value for Received date, as it is assigned on the server side (to DateTime.Now)
// deepBlueCD.ReceivedDate = DateTime.Now;
if (blueCashDist.ReceivedDate.HasValue) {
deepBlueCD.ReceivedDate = blueCashDist.ReceivedDate.Value.Date;
}
else {
deepBlueCD.ReceivedDate = (DateTime.Now).Date;
}
// This should be handled in the reconciliation
#region reconconciliation
if (blueCashDist.Received.HasValue) {
deepBlueCD.IsReconciled = blueCashDist.Received.Value;
}
deepBlueCD.PaidON = blueCashDist.ReceivedDate;
// Paid Date is not required
// TODO: Find out which of Paid Date/Paid On is used for reconciliation
// deepBlueCD.PaidDate;
// deepBlueCD.ReconciliationMethod;
#endregion
// WARNING: What should the Cash distribution type should be (Cash Distribution/Deemed Distribution/Netted Distribution)
// Cash Distribution
deepBlueCD.CashDistributionTypeID = 1;
return deepBlueCD;
}
private static C6_10AmberbrookFundInfo GetBlueFund(string fundNumber, BlueEntities context)
{
if (BlueFunds.ContainsKey(fundNumber)) {
return (C6_10AmberbrookFundInfo)BlueFunds[fundNumber];
}
else {
C6_10AmberbrookFundInfo fund = context.C6_10AmberbrookFundInfo.Where(x => x.AmberbrookFundNo.Equals(fundNumber)).FirstOrDefault();
if (fund != null) {
BlueFunds.Add(fundNumber, fund);
}
return fund;
}
}
public static List<string> GetUnderlyingFundTypesFromBlue()
{
using (BlueEntities context = new BlueEntities()) {
return context.C8_50tblFundType.Select(x => x.FundType).ToList();
}
}
public static List<string> GetSellerTypesFromBlue()
{
using (BlueEntities context = new BlueEntities()) {
return context.C8_60tblSellerType.Select(x => x.SellerType).Distinct().ToList();
}
}
public static List<string> GetDealClosingCostTypesFromBlue()
{
using (BlueEntities context = new BlueEntities()) {
return context.C6_40tblDealExpenses.Select(x => x.Description).Distinct().ToList();
}
}
private static string ImportDealExpenses(CookieCollection cookies, BlueEntities context, C6_15tblAmberbrookDealInfo blueDeal, DealDetailModel deepBlueDealDetail)
{
int dealId = deepBlueDealDetail.DealId;
StringBuilder sb = new StringBuilder();
string message = string.Empty;
if (deepBlueDealDetail.DealExpenses == null || deepBlueDealDetail.DealExpenses.Count == 0) {
List<C6_40tblDealExpenses> blueDealExpenses = context.C6_40tblDealExpenses.Where(x => x.AmberbrookFundNo == blueDeal.AmberbrookFundNo).Where(x => x.AmberbrookDealNo == blueDeal.DealNo).ToList();
if (blueDealExpenses.Count > 0) {
foreach (C6_40tblDealExpenses blueDealExpense in blueDealExpenses) {
string resp = string.Empty;
int? dealExpenseId = ImportDealExpense(cookies, blueDealExpense, dealId, out resp);
if (!dealExpenseId.HasValue) {
string error = "Failed to import deal expense for amb fund#:" + blueDealExpense.AmberbrookFundNo + " ,deal#:" + blueDealExpense.AmberbrookDealNo + ", error:" + resp;
// Util.WriteError(error);
sb.Append(error);
}
else {
message = "New DealExpenseID:" + dealExpenseId;
Util.WriteNewEntry(message);
messageLog.AppendLine(message);
}
}
}
else {
Util.Log("No deal expenses found to import..");
}
}
else {
message = "Deal:" + dealId + " already has deal expenses. Skipping Importing Deal Expenses";
Util.WriteWarning(message);
messageLog.AppendLine(message);
}
return sb.ToString();
}
private static string GetFundName(string fundNo)
{
if (!_fundNoToFundName.ContainsKey(fundNo)) {
using (BlueEntities context = new BlueEntities()) {
C6_10AmberbrookFundInfo fund = context.C6_10AmberbrookFundInfo.Where(x => x.AmberbrookFundNo.Equals(fundNo)).FirstOrDefault();
if (fund != null) {
_fundNoToFundName.Add(fundNo, fund.AmberbrookFundName);
}
else {
return null;
}
}
}
return (string)_fundNoToFundName[fundNo];
}
private static CapitalDistributionLineItem GetCapitalDistributionLineItemFromBlue(C6_30tblLPDistributions blueCapitalDistLineItem, int fundID, BlueEntities context, CookieCollection cookies, out string resp)
{
resp = string.Empty;
CapitalDistributionLineItem deepBlueCDLineItem = new CapitalDistributionLineItem();
if (blueCapitalDistLineItem.DistributionAmount.HasValue) {
deepBlueCDLineItem.DistributionAmount = (decimal)blueCapitalDistLineItem.DistributionAmount.Value;
}
// Investor ID
int? investorId = GetInvestorID(blueCapitalDistLineItem.LimitedPartner, fundID, context, cookies);
if (investorId.HasValue) {
deepBlueCDLineItem.InvestorID = investorId.Value;
} else {
resp = string.Format("Unable to find investor: {0} in FundID: {1} (AmberbrookFund#: {2})" , blueCapitalDistLineItem.LimitedPartner, fundID, blueCapitalDistLineItem.AmberbrookFundNo);
Util.WriteError(resp);
}
deepBlueCDLineItem.PaidON = blueCapitalDistLineItem.TransactionDate;
deepBlueCDLineItem.IsReconciled = true;
// WARNING: The following fields are present in DeepBlue but are not present in Blue
//deepBlueCDLineItem.CapitalReturn
//deepBlueCDLineItem.LPProfits;
//deepBlueCDLineItem.PreferredCatchUp;
//deepBlueCDLineItem.PreferredReturn;
//deepBlueCDLineItem.Profits;
//deepBlueCDLineItem.ReconciliationMethod;
//deepBlueCDLineItem.ReturnFundExpenses;
//deepBlueCDLineItem.ReturnManagementFees;
// Preferred Return and Return Management Fees are available on the Capital Distribution level.
// Distribute it pro-rate?
// Get the commitment for investor in this fund
return deepBlueCDLineItem;
}
private static CapitalDistribution GetCapitalDistributionFromBlue(C2_10tblDistFromAmberbrookCash blueCapitalDist, BlueEntities context, CookieCollection cookies, out string resp)
{
resp = string.Empty;
CapitalDistribution deepBlueCD = new CapitalDistribution();
deepBlueCD.CapitalDistributionDate = blueCapitalDist.NoticeDate.Date;
deepBlueCD.CapitalDistributionDueDate = blueCapitalDist.EffectiveDate.Date;
deepBlueCD.DistributionAmount = (decimal)blueCapitalDist.TotalCashDistribution;
deepBlueCD.IsManual = true;
if (blueCapitalDist.TotalCarry.HasValue) {
deepBlueCD.PreferredReturn = (decimal)blueCapitalDist.TotalCarry.Value;
}
if (blueCapitalDist.TotalRepayment.HasValue) {
deepBlueCD.ReturnManagementFees = (decimal)blueCapitalDist.TotalRepayment.Value;
}
// Distribution#.. The server automatically assign this.. so we dont need to provider this
// WARNING: The following fields are present in DeepBlue but are absent from blue
//deepBlueCD.CapitalDistributionProfit;
//deepBlueCD.CapitalReturn;
//deepBlueCD.LPProfits;
//deepBlueCD.PreferredCatchUp;
//deepBlueCD.Profits;
//deepBlueCD.ReturnFundExpenses;
// The following fields is present in blue but absent from DeepBlue
//blueCapitalDist.TotalUnusedCapital;
int? fundId = GetFundID(blueCapitalDist.AmberbrookFundNo, context, cookies);
if (fundId.HasValue) {
deepBlueCD.FundID = fundId.Value;
} else {
resp = "cannot find fund with AmberbrookFundNo: " + blueCapitalDist.AmberbrookFundNo;
}
return deepBlueCD;
}
private static List<CapitalDistribution> ConvertFromBlueToDeepBlue(CookieCollection cookies)
{
Util.Log("Fetching Capital Distribution from Blue............");
List<CapitalDistribution> capitalDistributions = new List<CapitalDistribution>();
TotalConversionRecords = 0;
RecordsConvertedSuccessfully = 0;
using (BlueEntities context = new BlueEntities()) {
List<C2_10tblDistFromAmberbrookCash> blueCapitalDistributions = context.C2_10tblDistFromAmberbrookCash.ToList();
foreach (C2_10tblDistFromAmberbrookCash blueCapitalDistribution in blueCapitalDistributions) {
try {
bool success = true;
string errorMsg = string.Empty;
TotalConversionRecords++;
Util.Log("<======================Importing record#" + TotalConversionRecords + "======================>");
CapitalDistribution capDist = GetCapitalDistributionFromBlue(blueCapitalDistribution, context, cookies, out errorMsg);
string msg = string.Format("#{0} Getting capital distribution C2_10tblDistFromAmberbrookCash => CapitalCall. C2_10tblDistFromAmberbrookCash.TransactionID: {1}, AmbFund#: {2} => DeepBlueFundID: {3}, NoticeDate: {4}, Total amount collected:{5}", TotalConversionRecords, blueCapitalDistribution.TransactionID, blueCapitalDistribution.AmberbrookFundNo, capDist.FundID, blueCapitalDistribution.NoticeDate, blueCapitalDistribution.TotalCashDistribution);
Util.Log(msg);
messageLog.Append(msg);
messageLog.Append(Environment.NewLine);
if (string.IsNullOrEmpty(errorMsg)) {
List<C6_30tblLPDistributions> blueCapitalDistLineItems = context.C6_30tblLPDistributions.Where(x => x.DistributionID == blueCapitalDistribution.TransactionID).ToList();
foreach (C6_30tblLPDistributions blueCapitalDistLineItem in blueCapitalDistLineItems) {
string resp = string.Empty;
CapitalDistributionLineItem lineItem = GetCapitalDistributionLineItemFromBlue(blueCapitalDistLineItem, capDist.FundID, context, cookies, out resp);
if (!string.IsNullOrEmpty(resp)) {
success = false;
errorMsg += resp;
} else {
messageLog.Append(string.Format("Line Item(C2_10tblDistFromAmberbrookCash => CapitalDistributionLineItem) level: BlueCallID:{0}, BlueMember:{1} => DeepBlueInvestorId:{2}, BlueCalledCapital:{3} => DeepBlueCapitalAmountCalled:{4}", blueCapitalDistLineItem.DistributionID, blueCapitalDistLineItem.LimitedPartner, lineItem.InvestorID, blueCapitalDistLineItem.DistributionAmount ?? 0, lineItem.DistributionAmount));
messageLog.Append(Environment.NewLine);
}
capDist.CapitalDistributionLineItems.Add(lineItem);
}
} else {
success = false;
}
if (success) {
RecordsConvertedSuccessfully++;
capitalDistributions.Add(capDist);
} else {
Util.WriteError(errorMsg);
Errors.Add(new KeyValuePair<C2_10tblDistFromAmberbrookCash, Exception>(blueCapitalDistribution, new Exception(errorMsg)));
}
} catch (Exception ex) {
Errors.Add(new KeyValuePair<C2_10tblDistFromAmberbrookCash, Exception>(blueCapitalDistribution, ex));
Util.WriteError("ConvertBlueToDeepBlue() " + ex);
}
}
}
Util.Log(string.Format("End fetching records from blue. Total Records: {0}, Records Converted successfully: {1}", TotalImportRecords, RecordsImportedSuccessfully));
return capitalDistributions;
}
private static DateTime? GetPurchaseDate(string amberBrookFundName, int dealNo)
{
using (BlueEntities blueContext = new BlueEntities()) {
string amberBrookFundNo = string.Empty;
amberBrookFundNo = (from fund in blueContext.C6_10AmberbrookFundInfo
where fund.AmberbrookFundName == amberBrookFundName
select fund.AmberbrookFundNo).FirstOrDefault();
return (from purchaseDetail in blueContext.C8_406qryDealPurchaseDate
where purchaseDetail.AmberbrookFundNo == amberBrookFundNo && purchaseDetail.DealNo == dealNo
select purchaseDetail.PurchaseDate).FirstOrDefault();
}
}
public static List<string> GetIndustriesFromBlue()
{
using (BlueEntities context = new BlueEntities()) {
return context.C8_50tblIndustryFocus.Select(x => x.IndustryFocus).ToList();
}
}
public static int? ImportUnderlyingFund(CookieCollection cookies, string blueUFName)
{
using (BlueEntities context = new BlueEntities()) {
C7_10tblGPPaymentInstructions uFund = context.C7_10tblGPPaymentInstructions.Where(x => x.FundType != "Direct").Where(x => x.Fund.Equals(blueUFName, StringComparison.OrdinalIgnoreCase)).SingleOrDefault();
Address address = null;
DeepBlue.Models.Entity.UnderlyingFund deepBlueFund = GetUnderlyingFundFromBlue(uFund, out address);
int RecordsImportedSuccessfully = 0;
List<KeyValuePair<DeepBlue.Models.Entity.UnderlyingFund, Exception>> ImportErrors = new List<KeyValuePair<UnderlyingFund, Exception>>();
if (uFund != null) {
int? issuerId = CreateIssuer(cookies);
return CreateUnderlyingFund(cookies, deepBlueFund, address, issuerId, ref RecordsImportedSuccessfully, ref ImportErrors);
}
}
return null;
}
private static string GetDealName(int dealNo, string fundNo)
{
string ident = string.Format("{0}:{1}", dealNo, fundNo);
if (!_dealNoToDealName.ContainsKey(ident)) {
using (BlueEntities context = new BlueEntities()) {
C6_15tblAmberbrookDealInfo deal = context.C6_15tblAmberbrookDealInfo.Where(x => x.AmberbrookFundNo.Equals(fundNo)).Where(x => x.DealNo.Equals(dealNo)).FirstOrDefault();
if (deal != null) {
_dealNoToDealName.Add(ident, deal.DealName);
}
else {
return null;
}
}
}
return (string)_dealNoToDealName[ident];
}
private static int? GetInvestorID(string limitedPartner, int fundID, BlueEntities context, CookieCollection cookies)
{
if (Investors.ContainsKey(limitedPartner)) {
return Convert.ToInt32(Investors[limitedPartner]);
}
int? investor = null;
List<Investor> investors = InvestorImport.GetInvestors(cookies, fundID, limitedPartner);
investors = investors.Where(x => x.InvestorName.Equals(limitedPartner, StringComparison.OrdinalIgnoreCase)).ToList();
if (investors.Count == 0) {
Util.WriteError(string.Format("No investor found with name: {0} and fund: {1}", limitedPartner, fundID));
} else if (investors.Count > 1) {
Util.WriteError(string.Format("More than one investor found with name: {0} and fund: {1}", limitedPartner, fundID));
} else if (investors.Count == 1) {
investor = investors[0].InvestorID;
Investors.Add(limitedPartner, investor.Value);
}
return investor;
}
private static int? GetFundID(string fundNumber, BlueEntities context, CookieCollection cookies)
{
int? fundId = null;
C6_10AmberbrookFundInfo blueFund = GetBlueFund(fundNumber, context);
if (blueFund != null) {
DeepBlue.Models.Entity.Fund deepBlueFund = GetDeepBlueFund(blueFund.AmberbrookFundName, cookies);
if (deepBlueFund != null) {
fundId = deepBlueFund.FundID;
}
else {
}
}
else {
}
return fundId;
}
public static void ConvertInvestorViaWeb()
{
List<CreateModel> models = new List<CreateModel>();
using (BlueEntities context = new BlueEntities()) {
List<C7_20tblLPPaymentInstructions> investors = context.C7_20tblLPPaymentInstructions.ToList();
foreach (C7_20tblLPPaymentInstructions investor in investors) {
CreateModel model = GetCreateModelFromBlue(investor);
#region Investor Account
// Currently in blue, each investor has only one account
model.AccountLength = 1; // This is used as a prefix for each InvestorAccount passed in the form
//The following Key is used to determine if a particular Account has been deleted or not
// index_BankIndex
// index is 1 based
InvestorAccount account = new InvestorAccount();
// Server looks for index_ABANumber
account.Routing = Convert.ToInt32(investor.ABANumber);
// Server looks for index_AccountNumber
account.Account = investor.AccountNumber;
account.AccountOf = investor.Accountof;
account.Attention = investor.Attn;
account.BankName = investor.Bank;
account.Reference = investor.Reference;
// WARNING: The following values are present in our database, but not present in Blue, so setting those to NULL
// FFC
// FFCNO
// IBAN
// ByOrderOf
// Swift
#endregion
#region Contact Info
//The following Key is used to determine if a particular Contact has been deleted or not
// index_ContactIndex
model.ContactLength = 0;
foreach (C7_25LPContactinfo contactInfo in investor.C7_25LPContactinfo) {
model.ContactLength++;
Contact contact = new Contact();
contact.ContactCompany = contactInfo.ContactCompany;
contact.ContactName = contactInfo.ContactName;
// WARNING: Deepblue has consolidated CallNotices/Distribution notices into one field.
if (contactInfo.DistributionNotices != null) {
contact.ReceivesDistributionNotices = contactInfo.DistributionNotices.Value;
}
if (contactInfo.Financials != null) {
contact.ReceivesFinancials = contactInfo.Financials.Value;
}
if (contactInfo.InvestorLetters != null) {
contact.ReceivesInvestorLetters = contactInfo.InvestorLetters.Value;
}
// WARNING: We dont have the following values in our database
// contactInfo.Dear; // This seems to be the first name from Contact Name
Address contactAddress = new Address();
contactAddress.Address1 = contactInfo.ContactAddress;
//contactInfo.Comments;
contactAddress.Address2 = contactInfo.ContactAddress2;
// Contact Info(Access) doesnt have the values for these properties, so using default values
contactAddress.Country = Globals.DefaultCountryID;
try {
string[] parts = new string[3];
if (ParseAddress(contactInfo.ContactAddress2, out parts)) {
contactAddress.City = parts[0];
contactAddress.PostalCode = parts[2];
contactAddress.State = Globals.States.Where(x => x.Abbr == parts[1].ToUpper().Trim()).First().StateID;
}
}
catch {
contactAddress.City = Globals.DefaultCity;
contactAddress.State = Globals.DefaultStateID;
contactAddress.PostalCode = Globals.DefaultZip;
}
AddCommunication(contact, Models.Admin.Enums.CommunicationType.Email, contactInfo.ContactEmail);
AddCommunication(contact, Models.Admin.Enums.CommunicationType.HomePhone, contactInfo.ContactPhone);
AddCommunication(contact, Models.Admin.Enums.CommunicationType.Fax, contactInfo.ContactFax);
}
#endregion
}
}
}
private static string ImportAssetInDeal(CookieCollection cookies, BlueEntities context, C6_15tblAmberbrookDealInfo blueDeal, C6_10AmberbrookFundInfo blueDealAmbFund, DeepBlue.Models.Deal.DealListModel deepBlueDeal, int fundID)
{
StringBuilder sb = new StringBuilder();
List<C5_10tblDealOrigination> blueAssetsInDeal = context.C5_10tblDealOrigination.Where(x => x.AmberbrookFundNo == blueDeal.AmberbrookFundNo).Where(x => x.DealNo == blueDeal.DealNo).ToList();
DealDetailModel deepBlueDealDetail = GetDeal(cookies, deepBlueDeal.DealId);
foreach (C5_10tblDealOrigination blueAssetInDeal in blueAssetsInDeal) {
// This can either be a direct or a UF
C7_10tblGPPaymentInstructions blueAsset = context.C7_10tblGPPaymentInstructions.Where(x => x.Fund.Equals(blueAssetInDeal.Fund, StringComparison.OrdinalIgnoreCase)).FirstOrDefault();
if (blueAsset != null) {
// Find the deal with the same DealNumber and DealName, and that belongs to the same Amberbrook fund
if (blueAsset.FundType.Equals("Direct", StringComparison.OrdinalIgnoreCase)) {
List<KeyValuePair<C5_11tblDealOriginationDirects, string>> failedDirects = ImportUnderlyingDirect(cookies, context, blueAssetInDeal, deepBlueDeal.DealId, deepBlueDealDetail, fundID);
if (failedDirects.Count > 0) {
Util.WriteError("The following " + failedDirects.Count + " securities could not be imported:");
}
foreach (KeyValuePair<C5_11tblDealOriginationDirects, string> failedDirect in failedDirects) {
string errorMsg = "Cannot import direct:" + failedDirect.Key.Direct + " into the deal:" + deepBlueDeal.DealId + ", reason:" + failedDirect.Value;
Util.WriteError(errorMsg);
sb.Append(" " + errorMsg + " ");
}
}
else {
string resp = string.Empty;
int? underlyingFundId = ImportUnderlyingFund(cookies, blueAssetInDeal, deepBlueDeal.DealId, fundID, deepBlueDealDetail, out resp);
if (!underlyingFundId.HasValue) {
string error = "Failed to import underlying fund:" + blueAssetInDeal.Fund + " into deal:" + deepBlueDeal.DealId + ", error:" + resp;
Util.WriteError(error);
sb.Append(error);
}
else {
Util.Log("New DealUnderlyingFundID:" + underlyingFundId);
}
}
}
else {
string msg = "Unable to find record in C7_10tblGPPaymentInstructions with Fund:" + blueAssetInDeal.Fund;
Util.WriteError(msg);
sb.Append(msg);
}
}
return sb.ToString();
}
public static List<DeepBlue.Models.Entity.Investor> ConvertBlueToDeepBlue()
{
Errors = new List<KeyValuePair<C7_20tblLPPaymentInstructions, Exception>>();
TotalConversionRecords = 0;
RecordsConvertedSuccessfully = 0;
List<DeepBlue.Models.Entity.Investor> dbInvestors = new List<DeepBlue.Models.Entity.Investor>();
using (BlueEntities context = new BlueEntities()) {
List<C7_20tblLPPaymentInstructions> investors = context.C7_20tblLPPaymentInstructions.ToList();
foreach (C7_20tblLPPaymentInstructions investor in investors) {
try {
TotalConversionRecords++;
DeepBlue.Models.Entity.Investor deepBlueInvestor = GetInvestorFromBlue(investor);
#region Investor Account
// Blue has only 1 account for 1 investor
InvestorAccount account = new InvestorAccount();
if (!string.IsNullOrEmpty(investor.ABANumber)) {
account.Routing = Convert.ToInt32(investor.ABANumber.Trim().Replace(" ", string.Empty).Replace("-", string.Empty));
}
if (!string.IsNullOrEmpty(investor.AccountNumber)) {
account.Account = investor.AccountNumber;
}
else {
account.Account = Globals.DefaultStringValue;
}
account.AccountOf = investor.Accountof;
account.Attention = investor.Attn;
account.BankName = investor.Bank;
account.Reference = investor.Reference;
account.CreatedBy = Globals.CurrentUser.UserID;
account.CreatedDate = DateTime.Now;
account.EntityID = Globals.DefaultEntityID;
account.IsPrimary = false;
account.LastUpdatedBy = Globals.CurrentUser.UserID;
account.LastUpdatedDate = DateTime.Now;
// WARNING: The following values are present in our database, but not present in Blue, so setting those to NULL
// FFC
// FFCNO
// IBAN
// ByOrderOf
// Swift
#endregion
deepBlueInvestor.InvestorAccounts.Add(account);
#region Contact Info
foreach (C7_25LPContactinfo contactInfo in investor.C7_25LPContactinfo) {
InvestorContact investorContact = new InvestorContact();
investorContact.CreatedBy = Globals.CurrentUser.UserID;
investorContact.CreatedDate = DateTime.Now;
investorContact.EntityID = Globals.DefaultEntityID;
investorContact.LastUpdatedBy = Globals.CurrentUser.UserID;
investorContact.LastUpdatedDate = DateTime.Now;
Contact contact = new Contact();
contact.ContactCompany = contactInfo.ContactCompany;
contact.ContactName = contactInfo.ContactName;
// WARNING: Deepblue has consolidated CallNotices/Distribution notices into one field.
if (contactInfo.DistributionNotices != null) {
contact.ReceivesDistributionNotices = contactInfo.DistributionNotices.Value;
}
if (contactInfo.Financials != null) {
contact.ReceivesFinancials = contactInfo.Financials.Value;
}
if (contactInfo.InvestorLetters != null) {
contact.ReceivesInvestorLetters = contactInfo.InvestorLetters.Value;
}
contact.CreatedBy = Globals.CurrentUser.UserID;
contact.CreatedDate = DateTime.Now;
contact.FirstName = contactInfo.ContactName;
contact.LastName = "n/a";
contact.LastUpdatedBy = Globals.CurrentUser.UserID;
contact.LastUpdatedDate = DateTime.Now;
contact.EntityID = Globals.DefaultEntityID;
investorContact.Contact = contact;
// WARNING: We dont have the following values in our database
// contactInfo.Dear; // This seems to be the first name from Contact Name
Address contactAddress = new Address();
if (contactInfo.ContactAddress != null) {
if (contactInfo.ContactAddress.Length > 40) {
contactAddress.Address1 = contactInfo.ContactAddress.Substring(0, 40);
} else {
contactAddress.Address1 = contactInfo.ContactAddress;
}
}
else {
contactAddress.Address1 = Globals.DefaultStringValue;
}
//contactInfo.Comments;
contactAddress.Address2 = contactInfo.ContactAddress2;
// Contact Info(Access) doesnt have the values for these properties, so using default values
contactAddress.Country = Globals.DefaultCountryID;
contactAddress.City = Globals.DefaultCity;
contactAddress.State = Globals.DefaultStateID;
contactAddress.PostalCode = Globals.DefaultZip;
try {
string[] parts = new string[3];
if (ParseAddress(contactInfo.ContactAddress2, out parts)) {
contactAddress.City = parts[0];
contactAddress.PostalCode = parts[2];
int postalCode = 0;
bool validZip = true;
if (Int32.TryParse(contactAddress.PostalCode, out postalCode)) {
if (contactAddress.PostalCode.Length > 5) {
validZip = false;
}
} else {
validZip = false;
}
if (!validZip) {
contactAddress.PostalCode = Globals.DefaultZip;
}
contactAddress.State = Globals.States.Where(x => x.Abbr == parts[1].ToUpper().Trim()).First().StateID;
}
else {
contactAddress.City = "dataerror: " + contactInfo.ContactAddress2;
}
}
catch {
}
AddCommunication(contact, Models.Admin.Enums.CommunicationType.Email, contactInfo.ContactEmail);
AddCommunication(contact, Models.Admin.Enums.CommunicationType.HomePhone, contactInfo.ContactPhone);
AddCommunication(contact, Models.Admin.Enums.CommunicationType.Fax, contactInfo.ContactFax);
contactAddress.AddressTypeID = (int)DeepBlue.Models.Admin.Enums.AddressType.Work;
contactAddress.CreatedBy = Globals.CurrentUser.UserID;
contactAddress.CreatedDate = DateTime.Now;
contactAddress.EntityID = Globals.DefaultEntityID;
contactAddress.LastUpdatedBy = Globals.CurrentUser.UserID;
contactAddress.LastUpdatedDate = DateTime.Now;
ContactAddress cntAddr = new ContactAddress();
cntAddr.CreatedBy = Globals.CurrentUser.UserID;
cntAddr.CreatedDate = DateTime.Now;
cntAddr.EntityID = Globals.DefaultEntityID;
cntAddr.LastUpdatedBy = Globals.CurrentUser.UserID;
cntAddr.LastUpdatedDate = DateTime.Now;
cntAddr.Address = contactAddress;
investorContact.Contact.ContactAddresses.Add(cntAddr);
deepBlueInvestor.InvestorContacts.Add(investorContact);
}
#endregion
dbInvestors.Add(deepBlueInvestor);
RecordsConvertedSuccessfully++;
}
catch (Exception ex) {
Errors.Add(new KeyValuePair<C7_20tblLPPaymentInstructions, Exception>(investor, ex));
}
}
}
return dbInvestors;
}
private static List<KeyValuePair<C5_11tblDealOriginationDirects, string>> ImportUnderlyingDirect(CookieCollection cookies, BlueEntities context, C5_10tblDealOrigination blueAssetInDeal, int targetDealId, DealDetailModel deepBlueDealDetail, int fundID)
{
List<KeyValuePair<C5_11tblDealOriginationDirects, string>> failedDirects = new List<KeyValuePair<C5_11tblDealOriginationDirects, string>>();
// This is a direct
//string issuerName = blueAsset.Fund;
List<C5_11tblDealOriginationDirects> blueSecurities = context.C5_11tblDealOriginationDirects.Where(x => x.Direct.Equals(blueAssetInDeal.Fund, StringComparison.OrdinalIgnoreCase)).Where(x => x.DealNo == blueAssetInDeal.DealNo).ToList();
if (blueSecurities.Count > 0) {
foreach (C5_11tblDealOriginationDirects blueSecurity in blueSecurities) {
// Equity from blue
C4_20tblStockTable blueStock = context.C4_20tblStockTable.Where(x => x.StockSymbol.Equals(blueSecurity.StockSymbol, StringComparison.OrdinalIgnoreCase)).FirstOrDefault();
// Find the direct corresponding to the security
List<AutoCompleteListExtend> equitiesAndFIs = GetDeepBlueEquitiesAndFIs(cookies);
string searchLabel = string.Format("{0}>>Equity>>{1}", blueStock.Company, blueStock.StockSymbol);
AutoCompleteListExtend equity = equitiesAndFIs.Where(x => x.otherid == (int)DeepBlue.Models.Deal.Enums.SecurityType.Equity).Where(x => x.value.Equals(blueStock.Company, StringComparison.OrdinalIgnoreCase)).Where(x => x.label.Equals(searchLabel, StringComparison.OrdinalIgnoreCase)).FirstOrDefault();
if (equity == null) {
string errorMsg = "Unable to find an equity with issuer:" + blueStock.Company + ", and label:" + searchLabel;
Util.WriteError(errorMsg);
failedDirects.Add(new KeyValuePair<C5_11tblDealOriginationDirects, string>(blueSecurity, errorMsg));
}
else {
// Create a DealUnderlyingDirect record
string resp = string.Empty;
// Make sure the direct/FI is already not part of the deal
DealUnderlyingDirectModel direct = deepBlueDealDetail.DealUnderlyingDirects.Where(x => x.SecurityId == equity.otherid2).Where(x => x.SecurityTypeId == (int)DeepBlue.Models.Deal.Enums.SecurityType.Equity).FirstOrDefault();
if (direct == null) {
int? dealUnderlyingDirectId = CreateDealUnderlyingDirect(cookies, blueSecurity, blueAssetInDeal, targetDealId, equity.otherid2, equity.id, fundID, out resp);
if (!dealUnderlyingDirectId.HasValue || dealUnderlyingDirectId.Value <= 0) {
string errorMsg = string.Format("Unable to import underlying direct {0}, response from server:{1}", blueSecurity.Direct, resp);
Util.WriteError(errorMsg);
failedDirects.Add(new KeyValuePair<C5_11tblDealOriginationDirects, string>(blueSecurity, errorMsg));
}
else {
string newEntry = "New DealUnderlyingDirectID:" + dealUnderlyingDirectId;
Util.WriteNewEntry(newEntry);
messageLog.AppendLine(newEntry);
}
}
else {
Util.Log(direct.SecurityId + " is already part of this deal.");
}
}
}
}
else {
string warningMsg = string.Format("C5_10tblDealOrigination.Fund(which is direct): {0} doesnt have any securities (no records in C5_11tblDealOriginationDirects. C5_11tblDealOriginationDirects.Where(x => x.Direct.Equals(blueAssetInDeal.Fund)).Where(x => x.DealNo == blueAssetInDeal.DealNo) yielded NO results) ", blueAssetInDeal.Fund);
Util.WriteWarning(warningMsg);
messageLog.AppendLine(warningMsg);
}
return failedDirects;
}
public static void ImportPostRecordDateCashDistribution(CookieCollection cookies)
{
List<C1_30tblPostRecordDateTransactions> postRecordDateTransactions = new List<C1_30tblPostRecordDateTransactions>();
using (BlueEntities context = new BlueEntities()) {
postRecordDateTransactions = context.C1_30tblPostRecordDateTransactions.Where(x => x.TransactionType.Contains("cash")).ToList();
}
List<KeyValuePair<C1_30tblPostRecordDateTransactions, Exception>> prcdErrors = new List<KeyValuePair<C1_30tblPostRecordDateTransactions, Exception>>();
if (postRecordDateTransactions.Count > 0) {
string resp = string.Empty;
prcdErrors = CreatePostRecordCashDistribution(cookies, postRecordDateTransactions, out resp);
}
LogErrors(prcdErrors);
}
public static List<string> GetPurchaseTypeFromBlue()
{
using (BlueEntities context = new BlueEntities()) {
return context.C6_15tblAmberbrookDealInfo.Select(x => x.PurchaseType).Distinct().ToList();
}
}
private static List<UnderlyingFundCashDistribution> ConvertFromBlueToDeepBlue(CookieCollection cookies)
{
Util.Log("Fetching Underlying Fund Cash Distribution from Blue............");
List<UnderlyingFundCashDistribution> cashDists = new List<UnderlyingFundCashDistribution>();
using (BlueEntities context = new BlueEntities()) {
// C1_20tblCallsToAmberbrook has deals also in the same table. We will group by (FundNo, Fund, NoticeDate) to get unique Capital Call (which should correspond to UnderlyingFundCapitalCall).
var ufCashDists = from blueCC in context.C1_10tblDistToAmberbrookCash
group blueCC by
new { blueCC.AmberbrookFundNo, blueCC.Fund, blueCC.NoticeDate, blueCC.Proceeds } into g
select g;
TotalImportRecords = 0;
RecordsImportedSuccessfully = 0;
messageLog.Append("");
foreach (var ufCD in ufCashDists) {
TotalImportRecords++;
List<C1_10tblDistToAmberbrookCash> blueCashDistsByDeal = context.C1_10tblDistToAmberbrookCash
.Where(x => x.NoticeDate == ufCD.Key.NoticeDate)
.Where(x => x.Fund == ufCD.Key.Fund)
.Where(x => x.AmberbrookFundNo == ufCD.Key.AmberbrookFundNo)
.Where(x => x.Proceeds == ufCD.Key.Proceeds)
.ToList();
string msg = string.Format("#{0} Getting cash dist .AmbFund#: {1}, Fund: {2}, NoticeDate: {3}, Total deals in this distribution:{4} ", TotalImportRecords, ufCD.Key.AmberbrookFundNo, ufCD.Key.Fund, ufCD.Key.NoticeDate, blueCashDistsByDeal.Count);
Util.Log(msg);
messageLog.Append(msg);
messageLog.Append(Environment.NewLine);
C1_10tblDistToAmberbrookCash firstBlueCashDist = blueCashDistsByDeal.First();
string cdResp = string.Empty;
UnderlyingFundCashDistribution cd = GetCashDistributionFromBlue(firstBlueCashDist, (decimal)blueCashDistsByDeal.Sum(x => x.Proceeds), context, cookies, out cdResp);
bool error = false;
if (cd != null) {
messageLog.Append(string.Format("UnderlyingFundCashDistribution level(C1_10tblDistToAmberbrookCash => UnderlyingFundCashDistribution): BlueTransactionID:{0}, BlueAMBFund#:{1} => DeepBlueFundId:{2}, BlueUF:{3} => DeepBlueUF:{4}", firstBlueCashDist.TransactionID, firstBlueCashDist.AmberbrookFundNo, cd.FundID, firstBlueCashDist.Fund, cd.UnderlyingFundID));
messageLog.Append(Environment.NewLine);
// UnderlyingFundCashDistributionLineItem
foreach (C1_10tblDistToAmberbrookCash blueCashDist in blueCashDistsByDeal) {
try {
string resp = string.Empty;
CashDistribution lineItem = GetCashDistributionLineItemFromBlue(blueCashDist, cd.UnderlyingFundID, cd.FundID, cookies, out resp);
if (lineItem != null) {
messageLog.Append(string.Format("Line Item(C1_10tblDistToAmberbrookCash => Cash Distribution) level: BlueTransactionID:{0}, BlueDeal#:{1} => DeepBlueDealId:{2}, BlueProceeds:{3} => DeepBlueAmount:{4}, BlueReceivedDate:{5} => DeepBlueDistributionDate:{6}", blueCashDist.TransactionID, blueCashDist.AmberbrookDealNo, lineItem.DealID, blueCashDist.Proceeds, lineItem.Amount, blueCashDist.Received, lineItem.DistributionDate));
messageLog.Append(Environment.NewLine);
cd.CashDistributions.Add(lineItem);
}
else {
error = true;
Errors.Add(new KeyValuePair<C1_10tblDistToAmberbrookCash, Exception>(blueCashDist, new Exception(resp)));
Util.WriteError(resp);
}
}
catch (Exception ex) {
error = true;
Errors.Add(new KeyValuePair<C1_10tblDistToAmberbrookCash, Exception>(blueCashDist, ex));
Util.WriteError("ConvertBlueToDeepBlue() " + ex);
}
}
}
else {
error = true;
string errMsg = string.Format("ConvertBlueToDeepBlue() Failed to convert. AmberbrookFundNo: {0}, Fund: {1}, NoticeDate: {2}, Error:{3}", ufCD.Key.AmberbrookFundNo, ufCD.Key.Fund, ufCD.Key.NoticeDate, cdResp);
Errors.Add(new KeyValuePair<C1_10tblDistToAmberbrookCash, Exception>(firstBlueCashDist, new Exception(errMsg)));
Util.WriteError("ConvertBlueToDeepBlue() " + errMsg);
}
if (!error) {
cashDists.Add(cd);
RecordsImportedSuccessfully++;
}
}
}
Util.Log(string.Format("End fetching records from blue. Total Records: {0}, Records Converted successfully: {1}", TotalImportRecords, RecordsImportedSuccessfully));
return cashDists;
}
public static void ImportDeals(CookieCollection cookies)
{
ContactsList = GetDealContactsFromDeepBlue(cookies);
Util.Log("Fetching Directs from Blue............");
using (BlueEntities context = new BlueEntities()) {
List<C6_15tblAmberbrookDealInfo> blueDeals = context.C6_15tblAmberbrookDealInfo.ToList();
foreach (C6_15tblAmberbrookDealInfo blueDeal in blueDeals) {
try {
bool success = true;
string errorMsg = string.Empty;
TotalImportRecords++;
Util.Log("<======================Importing record#" + TotalImportRecords + "======================>");
string amberBrookFundNumber = blueDeal.AmberbrookFundNo;
C6_10AmberbrookFundInfo amberBrookFund = context.C6_10AmberbrookFundInfo.Where(x => x.AmberbrookFundNo.Equals(amberBrookFundNumber)).FirstOrDefault();
if (amberBrookFund != null) {
// try to find the amber brook fund from DeepBlue
List<DeepBlue.Models.Entity.Fund> ufs = FundImport.GetFunds(cookies);
DeepBlue.Models.Entity.Fund fund = ufs.Where(x => x.FundName.Equals(amberBrookFund.AmberbrookFundName, StringComparison.OrdinalIgnoreCase)).FirstOrDefault();
//List<DeepBlue.Models.Deal.UnderlyingFundListModel> ufs = UnderlyingFundImport.GetUnderlyingFunds(cookies);
//UnderlyingFundListModel ufListModel = ufs.Where(x => x.FundName.Equals(amberBrookFundName, StringComparison.OrdinalIgnoreCase)).FirstOrDefault();
if (fund != null) {
// Find a deal.. Add to the deal if it already exists, create new deal otherwise
// Get all the deals from DeepBlue. We need to do this every time, as if you add a new deal, you want it to be fetched back
List<DeepBlue.Models.Deal.DealListModel> deals = DealImport.GetDeals(cookies, null);
// Get the DeepBlue deal with the same Amberbrook Fund and DealName as from blue
// The following line is replaced:
//DeepBlue.Models.Deal.DealListModel deal = deals.Where(x => x.DealName.Equals(blueDeal.DealName)).Where(x=>x.FundName.Equals(amberBrookFund.AmberbrookFundName, StringComparison.OrdinalIgnoreCase)).FirstOrDefault();
DeepBlue.Models.Deal.DealListModel deal = null;
// BY:
// Get the deals for AMB Fund
List<Deal> dls = GetDealsInFund(cookies, fund.FundID);
if (dls.Count > 0) {
Deal targetDeal = dls.Where(x => x.DealName.Equals(blueDeal.DealName)).SingleOrDefault();
if (targetDeal != null) {
deal = new DealListModel() { DealId = targetDeal.DealID, DealName = targetDeal.DealName };
}
}
int? dealId = null;
if (deal == null) {
// Create a new deal
int? purchaseType = null;
PurchaseType pType = Globals.PurchaseTypes.Where(x => x.Name.Equals(blueDeal.PurchaseType, StringComparison.OrdinalIgnoreCase)).FirstOrDefault();
if (pType != null) {
purchaseType = pType.PurchaseTypeID;
}
else {
string errMsg = "cannot find purchaseType: " + blueDeal.PurchaseType;
Util.WriteError(errMsg);
messageLog.AppendLine(errMsg);
}
string resp = string.Empty;
dealId = CreateNewDeal(cookies, blueDeal, fund.FundID, purchaseType, out resp);
if (dealId.HasValue && dealId.Value > 0) {
deal = new DealListModel() { DealId = dealId.Value, DealName = blueDeal.DealName };
string msg = "New Deal:" + dealId;
Util.WriteNewEntry(msg);
messageLog.AppendLine(msg);
}
else {
string errMsg = "cannot create deal. Resp: " + resp;
Util.WriteError(errMsg);
messageLog.AppendLine(errMsg);
ImportErrors.Add(new KeyValuePair<C6_15tblAmberbrookDealInfo, Exception>(blueDeal, new Exception()));
continue;
}
}
else {
dealId = deal.DealId;
Util.WriteWarning("Deal aready exists. DealID:" + dealId);
}
if (deal != null) {
bool importAssets = true;
bool importExpenses = true;
bool importSellerInfo = true;
bool importContact = true;
string error = string.Empty;
string err = string.Empty;
string resp = string.Empty;
string seperatorFormat = "---------------------------{0}--------------------------";
if (importAssets) {
// Now we have got the deal, lets import all the Directs and Underlying Funds
Util.Log(string.Format(seperatorFormat, "importing assets in deal"));
error = ImportAssetInDeal(cookies, context, blueDeal, amberBrookFund, deal, fund.FundID);
if (!string.IsNullOrEmpty(error)) {
success = false;
}
}
else {
Util.WriteWarning(string.Format(seperatorFormat, "Skipping importing assets in deal"));
}
DealDetailModel deepBlueDealDetail = GetDeal(cookies, deal.DealId);
if (importExpenses) {
Util.Log(string.Format(seperatorFormat, "importing expenses in deal"));
err = ImportDealExpenses(cookies, context, blueDeal, deepBlueDealDetail);
if (!string.IsNullOrEmpty(err)) {
Util.WriteError(err);
}
error += err;
if (!string.IsNullOrEmpty(error)) {
success = false;
}
}
else {
Util.WriteWarning(string.Format(seperatorFormat, "skipping importing expenses in deal"));
}
if (importSellerInfo) {
Util.Log(string.Format(seperatorFormat, "importing seller in deal"));
int? dealSellerId = ImportDealSellerInfo(cookies, blueDeal, deepBlueDealDetail, out resp);
// if (!dealSellerId.HasValue) {
// TODO
if (!string.IsNullOrEmpty(resp)) {// means there is error. Currently /Deal/CreateSelerInfo doesnt return the new ID. ASK Prasanna to correct this
err = "Failed to import deal seller for amb fund#:" + blueDeal.AmberbrookFundNo + " ,deal#:" + blueDeal.DealNo + ", error:" + resp;
Util.WriteError(err);
error += err;
success = false;
}
else {
Util.WriteNewEntry("New DealSellerID:" + dealSellerId);
}
}
else {
Util.WriteWarning(string.Format(seperatorFormat, "skipping importing seller in deal"));
}
if (importContact) {
resp = string.Empty;
Util.Log(string.Format(seperatorFormat, "importing contact in deal"));
int? dealContactId = ImportDealContact(cookies, blueDeal, deepBlueDealDetail, out resp);
if (!dealContactId.HasValue) {
err = "Failed to import deal contact for amb fund#:" + blueDeal.AmberbrookFundNo + " ,deal#:" + blueDeal.DealNo + ", error:" + resp;
Util.WriteError(err);
error += err;
success = false;
}
else {
Util.Log("Imported Contact for the deal....");
}
}
else {
Util.WriteWarning(string.Format(seperatorFormat, "skipping importing contact in deal"));
}
if (!success) {
ImportErrors.Add(new KeyValuePair<C6_15tblAmberbrookDealInfo, Exception>(blueDeal, new Exception(error)));
}
}
else {
success = false;
errorMsg = string.Format("Could not create/find DeepBlue deal with DealName:{0} and FundName:{1}", blueDeal.DealName, amberBrookFund.AmberbrookFundName);
Util.WriteError(errorMsg);
}
}
else {
// Log this.. Make sure the underlying fund is already created in DeepBlue
success = false;
errorMsg = "Could not find AMB Fund from DeepBlue:" + amberBrookFund.AmberbrookFundName;
Util.WriteError(errorMsg);
messageLog.AppendLine(errorMsg);
}
}
else {
// Log it
success = false;
errorMsg = "Could not find AMB Fund from Blue with Fund#:" + amberBrookFundNumber;
Util.WriteError(errorMsg);
messageLog.AppendLine(errorMsg);
}
if (success) {
RecordsImportedSuccessfully++;
}
else {
ImportErrors.Add(new KeyValuePair<C6_15tblAmberbrookDealInfo, Exception>(blueDeal, new Exception(errorMsg)));
}
}
catch (Exception ex) {
ImportErrors.Add(new KeyValuePair<C6_15tblAmberbrookDealInfo, Exception>(blueDeal, ex));
Util.WriteError("ConvertBlueToDeepBlue() " + ex);
}
}
}
Util.Log(string.Format("End fetching records from blue. Total Records: {0}, Records Converted successfully: {1}", TotalImportRecords, RecordsImportedSuccessfully));
LogErrors(ImportErrors);
LogMessages();
}
public static List<string> GetReportingTypesFromBlue()
{
using (BlueEntities context = new BlueEntities()) {
return context.C8_50tblReportingType.Select(x => x.ReportingType).ToList();
}
}