예제 #1
0
        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();
                            }
                        });
                    });
                });
            }
        }
예제 #2
0
        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();
                        }
                    });
                }
            });
        }
예제 #3
0
        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();
                        }
                    });
                }
            });
        }
예제 #4
0
        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();
                        }
                    });
                }
            });
        }