private void run7()
{
SqlHelper _sqlHelper = new SqlHelper();
var flist = new List <string> {
"CRNCY", "INT_ACC", "ID_ISIN", "SECURITY_NAME", "SHORT_NAME", "MATURITY"
};
var sb = new StringBuilder();
sb.AppendLine("if object_id ('tempdb..pBloomRates7') is not null drop table tempdb..pBloomRates7 create table tempdb..pBloomRates7 (SecurityID int, ISIN varchar(50), Date smalldatetime");
var sbu1 = new StringBuilder();
var sbu2 = new StringBuilder();
foreach (var fe in flist)
{
sb.AppendFormat(",{0} varchar(50)", fe);
sbu1.AppendFormat(",{0}", fe);
sbu2.AppendFormat(",@{0}", fe);
}
sb.Append(", primary key (SecurityID, ISIN, Date))");
string sqlu = String.Format("insert tempdb..pBloomRates7(SecurityID,ISIN,Date{0}) values (@SecurityID,@ISIN,@Date{1})", sbu1, sbu2);
Blpapi blpapi = new Blpapi();
if (blpapi.Connect())
{
_sqlHelper.GetConnection().Using(c =>
{
c.Command(sb.ToString()).ExecuteNonQuery();
c.Command("exec up_avgGetBlmRateSec1")
.ExecuteReader()
.ReadAll(r =>
{
System.Console.WriteLine("{0} {1:dd-MM-yy}", r.GetString(1), r.GetDateTime(2));
blpapi.sendRefDataRequestList(new List <string> {
r.GetString(1)
}, flist, string.Format("SETTLE_DT={0:yyyyMMdd};EQY_FUND_CRNCY=USD", r.GetDateTime(2)), e =>
{
if (e.HasElement("securityError"))
{
Element securityError = e.GetElement("securityError");
System.Console.WriteLine(string.Format("Ticker \"{0}\" - responseError", r.GetString(1)));
SqlCommand cmd = c.Command(sqlu).AddParameter("@SecurityID", r.GetInt32(0)).AddParameter("@ISIN", r.GetString(1)).AddParameter("@Date", r.GetDateTime(2));
foreach (var fe in flist)
{
cmd.AddParameter("@" + fe, "Err");
}
cmd.ExecuteNonQuery();
}
else
{
System.Console.WriteLine(string.Format("Ticker \"{0}\"", e.GetElementAsString("security")));
Element fields = e.GetElement("fieldData");
SqlCommand cmd = c.Command(sqlu)
.AddParameter("@SecurityID", r.GetInt32(0))
.AddParameter("@ISIN", r.GetString(1))
.AddParameter("@Date", r.GetDateTime(2));
foreach (var fe in flist)
{
cmd.AddParameter("@" + fe, (fields.HasElement(fe) ? (object)fields.GetElementAsString(fe) : DBNull.Value));
}
cmd.ExecuteNonQuery();
}
});
});
});
}
}
private void run3()
{
SqlHelper _sqlHelper = new SqlHelper();
var list = new List <int>();
var slist = new List <string>();
var flist = new List <string> {
"RTG_MOODY", "RTG_FITCH", "RTG_SP", "RTG_SP_OUTLOOK", "RTG_SP_LT_FC_ISSUER_CREDIT", "RTG_SP_LT_LC_ISSUER_CREDIT", "RTG_FITCH_OUTLOOK", "RTG_FITCH_LT_ISSUER_DEFAULT", "RTG_FITCH_SHORT_TERM", "RTG_MDY_OUTLOOK", "RTG_MDY_FC_CURR_ISSUER_RATING", "RTG_MDY_LC_CURR_ISSUER_RATING", "RTG_MDY_SEN_UNSECURED_DEBT", "RTG_EXPERT_RA_ISSUER_CRDT_RTG", "RTG_FITCH_LT_FC_DEBT", "RTG_FITCH_LT_LC_DEBT", "RTG_FITCH_ST_LC_ISSUER_DEFAULT", "RTG_MOODY_LONG_TERM", "RTG_MDY_LT_CORP_FAMILY", "RTG_FITCH_LONG", "RTG_FITCH_LT_LC_ISSUER_DEFAULT"
};
var sb = new StringBuilder();
sb.AppendLine("if object_id ('tempdb..pBloomRating') is not null drop table tempdb..pBloomRating create table tempdb..pBloomRating (SecurityID int primary key, ISIN varchar(50)");
var sbu1 = new StringBuilder();
var sbu2 = new StringBuilder();
foreach (var fe in flist)
{
sb.AppendFormat(",{0} varchar(50)", fe);
sbu1.AppendFormat(",{0}", fe);
sbu2.AppendFormat(",@{0}", fe);
}
sb.Append(")");
string sqlu = String.Format("insert tempdb..pBloomRating(SecurityID,ISIN{0}) values (@SecurityID,@ISIN{1})", sbu1, sbu2);
_sqlHelper.GetConnection().Using(c =>
{
c.Command(sb.ToString()).ExecuteNonQuery();
var cmd1 = c.Command("exec up_avgGetBlmRatingSec null");
cmd1.CommandTimeout = 600;
cmd1.ExecuteReader()
.ReadAll(r =>
{
list.Add(r.GetInt32(0));
slist.Add(r.GetString(1));
System.Console.WriteLine(string.Format("Tiker={0}", r.GetString(1)));
});
Blpapi blpapi = new Blpapi();
if (blpapi.Connect())
{
blpapi.sendRefDataRequestList(slist, flist, "", e =>
{
string ticker = e.GetElementAsString("security");
int rowIndex = e.GetElementAsInt32("sequenceNumber");
if (e.HasElement("securityError"))
{
Element securityError = e.GetElement("securityError");
System.Console.WriteLine(string.Format("Ticker \"{0}\" - responseError", ticker));
SqlCommand cmd = c.Command(sqlu)
.AddParameter("@SecurityID", list[rowIndex])
.AddParameter("@ISIN", ticker);
foreach (var fe in flist)
{
cmd.AddParameter("@" + fe, "Err");
}
cmd.ExecuteNonQuery();
}
else
{
Element fields = e.GetElement("fieldData");
System.Console.WriteLine(string.Format("Ticker \"{0}\"", ticker));
SqlCommand cmd = c.Command(sqlu)
.AddParameter("@SecurityID", list[rowIndex])
.AddParameter("@ISIN", ticker);
foreach (var fe in flist)
{
cmd.AddParameter("@" + fe, (fields.HasElement(fe) ? (object)fields.GetElementAsString(fe) : DBNull.Value));
}
cmd.ExecuteNonQuery();
}
});
}
});
}
private void run4(DateTime dt)
{
SqlHelper _sqlHelper = new SqlHelper();
var list = new List <int>();
var slist = new List <string>();
var flist = new List <string> {
"CRNCY", "CPN_TYP", "DAY_CNT_DES", "CPN_FREQ", "NXT_CALL_DT", "ANNOUNCE_DT", "INT_ACC", "INT_ACC_DT", "FIRST_SETTLE_DT", "AMT_ISSUED", "PAR_AMT", "MARKET_STATUS", "NAME", /*"GICS_SECTOR_NAME", "GICS_SECTOR",*/ "ICB_SUPERSECTOR_NAME", "ICB_SUPERSECTOR_NUM", "GICS_INDUSTRY_NAME", "GICS_INDUSTRY", "ADR_SH_PER_ADR", "ADR_UNDL_TICKER", "ID_ISIN", "SECURITY_NAME", "SHORT_NAME", "id_sedol1", "MATURITY", "CBBT_PX_BID", "CBBT_PX_ASK", "EXCH_CODE", "DVD_CRNCY", "VOLUME_AVG_10D", "EQY_PRIM_EXCH_SHRT", "EQY_PRIM_SECURITY_COMP_EXCH", "CNTRY_OF_RISK", "CNTRY_OF_DOMICILE", "EQY_PRIM_SECURITY_PRIM_EXCH"
};
var sb = new StringBuilder();
sb.AppendLine("if object_id ('tempdb..pBloomRates') is not null drop table tempdb..pBloomRates create table tempdb..pBloomRates (SecurityID int, ISIN varchar(50), Date smalldatetime");
var sbu1 = new StringBuilder();
var sbu2 = new StringBuilder();
foreach (var fe in flist)
{
sb.AppendFormat(",{0} varchar(50)", fe);
sbu1.AppendFormat(",{0}", fe);
sbu2.AppendFormat(",@{0}", fe);
}
sb.Append(",primary key(SecurityID, ISIN))");
string sqlu = String.Format("insert tempdb..pBloomRates(SecurityID,ISIN,Date{0}) values (@SecurityID,@ISIN,@Date{1})", sbu1, sbu2);
_sqlHelper.GetConnection().Using(c =>
{
c.Command(sb.ToString()).ExecuteNonQuery();
c.Command("exec up_avgGetBlmRateSec")
.ExecuteReader()
.ReadAll(r =>
{
if (!r.GetString(3).Contains("BGN"))
{
list.Add(r.GetInt32(0));
slist.Add(r.GetString(1));
}
});
Blpapi blpapi = new Blpapi();
if (blpapi.Connect())
{
//DateTime dt = DateTime.Today.AddDays(DateTime.Today.DayOfWeek == DayOfWeek.Sunday ? -2 : DateTime.Today.DayOfWeek == DayOfWeek.Monday ? -3 : -1);
//DateTime dt = DateTime.Today.AddDays(-1);
blpapi.sendRefDataRequestList(slist, flist,
string.Format("SETTLE_DT={0:yyyyMMdd}", dt),
e =>
{
string ticker = e.GetElementAsString("security");
int rowIndex = e.GetElementAsInt32("sequenceNumber");
if (e.HasElement("securityError"))
{
Element securityError = e.GetElement("securityError");
System.Console.WriteLine(string.Format("Ticker \"{0}\" - responseError", ticker));
SqlCommand cmd = c.Command(sqlu)
.AddParameter("@SecurityID", list[rowIndex])
.AddParameter("@ISIN", ticker)
.AddParameter("@Date", dt);
foreach (var fe in flist)
{
cmd.AddParameter("@" + fe, "Err");
}
cmd.ExecuteNonQuery();
}
else
{
Element fields = e.GetElement("fieldData");
System.Console.WriteLine(string.Format("Ticker \"{0}\"", ticker));
SqlCommand cmd = c.Command(sqlu)
.AddParameter("@SecurityID", list[rowIndex])
.AddParameter("@ISIN", ticker)
.AddParameter("@Date", dt);
foreach (var fe in flist)
{
cmd.AddParameter("@" + fe, (fields.HasElement(fe) ? (object)fields.GetElementAsString(fe) : DBNull.Value));
}
cmd.ExecuteNonQuery();
}
});
}
});
}
private void run1()
{
SqlHelper _sqlHelper = new SqlHelper();
var list = new List <int>();
var slist = new List <string>();
var flist = new List <string> {
"FIRST_CPN_DT", "FIRST_SETTLE_DT", "ISSUE_DT"
};
string sql =
"select s.SecurityID, rtrim(ltrim(s.Number))+' Corp' ISIN, '' Override " +
"from tSecurity s " +
"where s.SecurityID in (select SecurityID from tSecuritySecurityGroup where SecurityGroupID in (9, 10, 15, 846)) " +
"and (s.DateEnd > GETDATE() or (s.DateEnd = '19000101' and s.SecurityID > 98000)) " +
"and s.Number <> '' ";
var sb = new StringBuilder();
sb.AppendLine("if object_id ('tempdb..pSecSettleDate') is not null drop table tempdb..pSecSettleDate create table tempdb..pSecSettleDate (SecurityID int primary key, ISIN varchar(50)");
var sbu1 = new StringBuilder();
var sbu2 = new StringBuilder();
foreach (var fe in flist)
{
sb.AppendFormat(",{0} smalldatetime", fe);
sbu1.AppendFormat(",{0}", fe);
sbu2.AppendFormat(",@{0}", fe);
}
sb.Append(")");
string sqlu = String.Format("insert tempdb..pSecSettleDate(SecurityID,ISIN{0}) values (@SecurityID,@ISIN{1})", sbu1, sbu2);
//string sqlu = "insert pSecSettleDate (SecurityID, SDate) values (@SecurityID, @SDate)";
_sqlHelper.GetConnection().Using(c =>
{
c.Command(sb.ToString()).ExecuteNonQuery();
c.Command(sql)
.ExecuteReader()
.ReadAll(r =>
{
list.Add(r.GetInt32(0));
slist.Add(r.GetString(1));
});
Blpapi blpapi = new Blpapi();
if (blpapi.Connect())
{
blpapi.sendRefDataRequestList(slist, flist, "", e =>
{
string ticker = e.GetElementAsString("security");
int rowIndex = e.GetElementAsInt32("sequenceNumber");
if (e.HasElement("securityError"))
{
Element securityError = e.GetElement("securityError");
System.Console.WriteLine(string.Format("Ticker \"{0}\" - responseError", ticker));
return;
}
else
{
Element fields = e.GetElement("fieldData");
SqlCommand cmd = c.Command(sqlu).AddParameter("@SecurityID", list[rowIndex]).AddParameter("@ISIN", ticker);
foreach (var fe in flist)
{
cmd.AddParameter("@" + fe, (fields.HasElement(fe) ? (object)fields.GetElementAsString(fe) : DBNull.Value));
}
cmd.ExecuteNonQuery();
}
});
}
});
}