private void run()
{
SqlHelper _sqlHelper = new SqlHelper();
var list = new List <Security>();
string sql = "delete pSecFlow " +
"select s.SecurityID, s.Number+' Corp' ISIN, 'SETTLE_DT='+convert(varchar, sd.FIRST_CPN_DT-1, 112) Override " +
"from tSecurity s " +
"join tempdb..pSecSettleDate sd on sd.SecurityID = s.SecurityID " +
"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 <> '' ";
string sqlu = "insert pSecFlow (SecurityID, FDate, Value1, Value2) values (@SecurityID, @FDate, @Value1, @Value2)";
_sqlHelper.GetConnection().Using(c =>
{
c.Command(sql)
.ExecuteReader()
.ReadAll(r =>
{
list.Add(new Security()
{
SecurityID = r.GetInt32(0), Ticker = r.GetString(1), Override = r.IsDBNull(2) ? "" : r.GetString(2)
});
});
Blpapi blpapi = new Blpapi();
if (blpapi.Connect())
{
foreach (Security s in list)
{
System.Console.WriteLine(string.Format("Tiker={0} ID={1}", s.Ticker, s.SecurityID));
blpapi.sendRefDataBulkRequest(s.SecurityID, s.Ticker, "DES_CASH_FLOW", s.Override, e =>
{
c.Command(sqlu)
.AddParameter("@SecurityID", s.SecurityID)
.AddParameter("@FDate", !e.IsNullValue(0) ? (object)e.GetElement(0).GetValueAsDate().ToSystemDateTime() : DBNull.Value)
.AddParameter("@Value1", !e.IsNullValue(1) ? (object)e.GetElement(1).GetValueAsFloat64() : DBNull.Value)
.AddParameter("@Value2", !e.IsNullValue(2) ? (object)e.GetElement(2).GetValueAsFloat64() : DBNull.Value)
.ExecuteNonQuery();
});
}
}
});
}
private void run9()
{
SqlHelper _sqlHelper = new SqlHelper();
var list = new List <Security>();
string sqlu = "insert pSecDiv (SecurityID, ISIN, DeclaredDate, ExDate, RecordDate, PayableDate, DivAmount, DivFrequency, DivType) values (@SecurityID, @ISIN, @DeclaredDate, @ExDate, @RecordDate, @PayableDate, @DivAmount, @DivFrequency, @DivType)";
_sqlHelper.GetConnection().Using(c =>
{
c.Command("delete pSecDiv").ExecuteNonQuery();
c.Command("up_avgGetBlmSecDiv")
.ExecuteReader()
.ReadAll(r =>
{
list.Add(new Security()
{
SecurityID = r.GetInt32(0), Ticker = r.GetString(1), Override = string.Format("DVD_START_DT={0:yyyyMMdd};DVD_END_DT={1:yyyyMMdd}", DateTime.Today.AddDays(-90), DateTime.Today.AddDays(1))
});
});
Blpapi blpapi = new Blpapi();
if (blpapi.Connect())
{
foreach (Security s in list)
{
System.Console.WriteLine(string.Format("Tiker={0} ID={1}", s.Ticker, s.SecurityID));
blpapi.sendRefDataBulkRequest(s.SecurityID, s.Ticker, "DVD_HIST", s.Override, e =>
{
c.Command(sqlu)
.AddParameter("@SecurityID", s.SecurityID)
.AddParameter("@ISIN", s.Ticker)
.AddParameter("@DeclaredDate", !e.IsNullValue(0) ? (object)e.GetElement(0).GetValueAsDate().ToSystemDateTime() : DBNull.Value)
.AddParameter("@ExDate", !e.IsNullValue(1) ? (object)e.GetElement(1).GetValueAsDate().ToSystemDateTime() : DBNull.Value)
.AddParameter("@RecordDate", !e.IsNullValue(2) ? (object)e.GetElement(2).GetValueAsDate().ToSystemDateTime() : DBNull.Value)
.AddParameter("@PayableDate", !e.IsNullValue(3) ? (object)e.GetElement(3).GetValueAsDate().ToSystemDateTime() : DBNull.Value)
.AddParameter("@DivAmount", !e.IsNullValue(4) ? (object)e.GetElement(4).GetValueAsFloat64() : DBNull.Value)
.AddParameter("@DivFrequency", !e.IsNullValue(5) ? (object)e.GetElement(5).GetValueAsString() : DBNull.Value)
.AddParameter("@DivType", !e.IsNullValue(6) ? (object)e.GetElement(6).GetValueAsString() : DBNull.Value)
.ExecuteNonQuery();
});
}
}
});
}