private List <ReportInfo> GetInstiutionReportList(int ProductId)
{
List <ReportInfo> reportInfos = new List <ReportInfo>();
IEnumerable <IDataRecord> records = _dbWrapper.QueryDataRecord(cmd =>
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = SqlConstants.READ_REPORT_LIST;
DatabaseWrapperHelper.AddInStringParameter(cmd, "INSTITUTION_ID", _context.Identity.InstitutionId);
DatabaseWrapperHelper.AddInIntParameter(cmd, "PRODUCT_ID", ProductId.ToString());
}, _context.Identity.InstitutionId);
foreach (var row in records)
{
ReportInfo reportInfo = new ReportInfo();
reportInfo.UiSeq = row["INST_REPORT_SEQ"] is DBNull ? 0 : Convert.ToInt32(row["INST_REPORT_SEQ"]);
reportInfo.PrintSeq = row["INST_PRINT_SEQ"] is DBNull ? 0 : Convert.ToInt32(row["INST_PRINT_SEQ"]);
reportInfo.UsageCode = row["INST_USAGE_CODE"].ToString();
reportInfo.TypeId = row["REPORT_TYPE_ID"] is DBNull ? 0 : Convert.ToInt32(row["REPORT_TYPE_ID"]);
reportInfo.TypeName = row["REPORT_TYPE_NAME"].ToString();
reportInfo.ReportId = row["REPORT_ID"] is DBNull ? 0 : Convert.ToInt32(row["REPORT_ID"]);
reportInfo.ReportName = row["REPORT_NAME"].ToString();
reportInfo.WorkflowTypeId = row["WORKFLOW_TYPE_ID"] is DBNull ? 0 : Convert.ToInt32(row["WORKFLOW_TYPE_ID"]);
reportInfos.Add(reportInfo);
}
return(reportInfos);
}
public SearchResult SearchSecurities(string searchString)
{
SearchResult retList = new SearchResult {
HouseholdList = null, ProposalsList = null, secList = new List <SecBasicData>()
};
List <string> searchStrings = searchString.ToUpper().Split(',').ToList <string>();
string sql = SqlConstants.SearchSecurities;
sql = _dbWrapper.BuildSqlInClauseQuery(searchStrings, ":REPLACE_SEARCH_KEYS", sql);
IEnumerable <IDataRecord> records = _dbWrapper.QueryDataRecord(cmd =>
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
_dbWrapper.BuildParamInClauseQuery(searchStrings, "REPLACE_SEARCH_KEYS", cmd);
}, _context.Identity.InstitutionId);
foreach (var row in records)
{
SecBasicData secDataInfo = new SecBasicData();
secDataInfo.SecID = row["SEC_ID"].ToString();;
secDataInfo.SecType = row["SECURITY_TYPE"].ToString();
secDataInfo.Name = row["SECURITY_NAME"].ToString();
secDataInfo.CusipNum = row["CUSIP"].ToString();
secDataInfo.MFID = row["TA_ID"].ToString();
secDataInfo.Ticker = row["SYMBOL"].ToString();
secDataInfo.SecNo = row["SEC_NO"].ToString();
secDataInfo.CogID = row["COG_ID"].ToString();
//if (useSpecialFactsheetHandling && secDataInfo.SecType == "MA")
//{
// if (!string.IsNullOrEmpty(row["PERSON_ID"].ToString(); FACTSHEET_FILE_NAME)))
// {
// secDataInfo.DisplayFactSheetLink = true;
// }
//}
//else
// if (!string.IsNullOrEmpty(secDataInfo.CogID)) secDataInfo.DisplayFactSheetLink = true;
secDataInfo.Price = row["LAST_CLOSE_PRICE"].ToString();
secDataInfo.PriceFactor = row["PRICE_FACTOR"].ToString();
secDataInfo.SubType = row["SUB_TYPE"].ToString();
retList.secList.Add(secDataInfo);
}
return(retList);
}
public List <Household> SearchAllContacts(SearchParameters searchParameters)
{
List <Household> resultList = new List <Household>();
IEnumerable <IDataRecord> records = _dbWrapper.QueryDataRecord(cmd =>
{
cmd.CommandType = CommandType.StoredProcedure;
if (searchParameters.Searchby == SearchBy.ACCOUNTNUMBER)
{
cmd.CommandText = SqlConstants.GET_ALL_CONTACTSBY_ACCOUNTNO;
DatabaseWrapperHelper.AddInStringParameter(cmd, "A_VACCTNO", HandleSpecialCharacters(searchParameters.AccountNumber));
}
else if (searchParameters.Searchby == SearchBy.REPCODE)
{
cmd.CommandText = SqlConstants.GET_ALL_CONTACTSBY_REPCODE;
DatabaseWrapperHelper.AddInStringParameter(cmd, "A_VREPCODE", HandleSpecialCharacters(searchParameters.RepCode));
}
else //default SearchBy DEMOGRAPHICS
{
cmd.CommandText = SqlConstants.GET_ALL_CONTACTSBY_DEMOGRAPHICS;
SetDemographicParameters(cmd, searchParameters);
}
//CommonParameters
SetCommonParameters(cmd, searchParameters);
//Output Parameters
DatabaseWrapperHelper.AddOutCursorParameter(cmd, "A_RCCONTACTLIST");
DatabaseWrapperHelper.AddOutIntParameter(cmd, "A_UTSTATUS");
DatabaseWrapperHelper.AddOutStringParameter(cmd, "A_UTSTATUSMSG", 2000);
}, _context.Identity.InstitutionId);
foreach (var row in records)
{
Household result = new Household();
result.HouseholdID = row["HOUSEHOLD_ID"].ToString();
if (!String.IsNullOrEmpty(result.HouseholdID) && Convert.ToInt32(result.HouseholdID) > 0 && !resultList.Any(p => p.HouseholdID == result.HouseholdID))
{
result.Type = row["TYPE"].ToString();
//Get Contact Details
result.Persons = GetContactsForHousehold(result.HouseholdID);
resultList.Add(result);
}
}
return(resultList);
}
public Broker GetBrokerInfo()
{
Broker broker = new Broker();
try {
IEnumerable <IDataRecord> records = _dbWrapper.QueryDataRecord(cmd =>
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = SqlConstants.READBROKERINFO_SQL;
DatabaseWrapperHelper.AddInIntParameter(cmd, "broker_id", _context.Identity.BrokerId);
}, _context.Identity.InstitutionId);
foreach (var row in records)
{
broker.BrokerId = _context.Identity.BrokerId;
broker.Suffix = row["DESIGNATION_SUFFIX"].ToString();
broker.Clearing_firm_name = row["CLRG_COUNTRY_NAME"].ToString();
broker.Firm_country_name = row["FIRM_COUNTRY_NAME"].ToString();
broker.Sub_Firm_No = row["SUB_NO"].ToString();
broker.Primary_state = row["PRIMARY_STATE"].ToString();
broker.Affiliation = row["AFFILIATION"].ToString();
broker.Clearing_firm_name = row["CLRG_FIRM_NAME"].ToString();
broker.Firm_name = row["FIRM_NAME"].ToString();
broker.User_name = row["USERNAME"].ToString();
broker.Title = row["BROKER_TITLE"].ToString();
// broker.Sub_Firm_Num = row["SUB_FIRM_NUM"].ToString();
broker.InstitutionId = row["INSTITUTION_ID"].ToString();
//broker.SUB_ID = row["SUB_ID"].ToString();
broker.Name = row["BROKER_NAME"].ToString();
broker.USERNAME = row["USERNAME"].ToString();
broker.BrokerId = row["BROKER_ID"].ToString();
broker.Email = row["FIRM_EMAIL"].ToString();
broker.FirmPhone = row["FIRM_PHONE"].ToString();
broker.contactInfoList = new System.Collections.ArrayList();
ContactDetails firmContact = new ContactDetails();
firmContact.Contact_type = "firm";
firmContact.Phone = row["FIRM_PHONE"].ToString();
firmContact.Fax = row["FIRM_FAX"].ToString();
firmContact.Email = row["FIRM_EMAIL"].ToString();
broker.contactInfoList.Add(firmContact);
ContactDetails clrgFirmContact = new ContactDetails();
clrgFirmContact.Contact_type = "clearing_firm";
clrgFirmContact.Phone = row["CLRG_FIRM_PHONE"].ToString();
clrgFirmContact.Fax = row["CLRG_FIRM_FAX"].ToString();
clrgFirmContact.Email = row["CLRG_FIRM_EMAIL"].ToString();
broker.contactInfoList.Add(clrgFirmContact);
broker.addressInfoList = new System.Collections.ArrayList();
Address firmAddress = new Address();
firmAddress.Address_Type = AddressType.firm;
firmAddress.AddressLine1 = row["FIRM_ADDR1"].ToString();
firmAddress.AddressLine2 = row["FIRM_ADDR2"].ToString();
firmAddress.City = row["FIRM_CITY"].ToString();
firmAddress.State = row["FIRM_STATE"].ToString();
firmAddress.Country = row["FIRM_COUNTRY"].ToString();
firmAddress.ZipCode = row["FIRM_POSTAL_CODE"].ToString();
broker.addressInfoList.Add(firmAddress);
Address clrgFirmAddress = new Address();
clrgFirmAddress.Address_Type = AddressType.clearing_firm;
clrgFirmAddress.AddressLine1 = row["CLRG_FIRM_ADDR1"].ToString();
clrgFirmAddress.AddressLine2 = row["CLRG_FIRM_ADDR2"].ToString();
clrgFirmAddress.City = row["CLRG_FIRM_CITY"].ToString();
clrgFirmAddress.State = row["CLRG_FIRM_STATE"].ToString();
clrgFirmAddress.Country = row["FIRM_COUNTRY"].ToString();
clrgFirmAddress.ZipCode = row["CLRG_FIRM_COUNTRY"].ToString();
broker.addressInfoList.Add(clrgFirmAddress);
}
}
catch (Exception ex)
{
throw ex;
}
return(broker);
}
