Esempio n. 1
0
        public static void Execute(string[] args, Dictionary <string, string> config, string[] exchanges, int indexes_exchange_id)
        {
            DateTime startDateInitial = DateTime.MinValue;
            DateTime endDateInitial   = DateTime.MaxValue;
            string   clientFolder     = null;
            string   processedFolder  = null;
            string   rejectedFolder   = null;
            string   ftpServerUrl     = null;
            string   ftpUserName      = null;
            string   ftpPassword      = null;
            bool     importPrices     = false;
            bool     importIndexes    = false;
            string   doDeleteFiles    = null;


            clientFolder    = config[Constants.SourceDir];
            processedFolder = config[Constants.ProcessedDir];
            rejectedFolder  = config[Constants.RejectedDir];
            ftpServerUrl    = config[Constants.EODPricesFTPServerURL];
            ftpUserName     = config[Constants.EODPricesFTPUserName];
            ftpPassword     = config[Constants.EODPricesFTPPassword];
            doDeleteFiles   = config[Constants.DoDeleteFiles];

            using (SqlConnection conn = new SqlConnection(config[Constants.DB_ConnectionString]))
            {
                Logger logger = null;

                conn.Open();

                try
                {
                    if (args[0].ToLower() == Constants.Daily)
                    {
                        logger           = new Logger(conn, Constants.EODPricesImport);
                        startDateInitial = DateTime.Today.AddDays(-1);
                        endDateInitial   = DateTime.Today;
                    }
                    else if (args[0].ToLower() == Constants.Last)
                    {
                        logger = new Logger(conn, Constants.EODPricesImport);
                    }
                    else
                    {
                        logger           = new Logger(conn, Constants.EODHistPricesImport);
                        startDateInitial = DateTime.ParseExact(args[0], Constants.DateParmsFormat, System.Globalization.CultureInfo.CurrentCulture);
                        endDateInitial   = DateTime.ParseExact(args[1], Constants.DateParmsFormat, System.Globalization.CultureInfo.CurrentCulture);
                    }

                    foreach (string arg in args)
                    {
                        if (arg.ToLower() == Constants.Prices)
                        {
                            importPrices = true;
                        }
                        if (arg.ToLower() == Constants.Indeces)
                        {
                            importIndexes = true;
                        }
                    }
                    if (importPrices == false && importIndexes == false)
                    {
                        throw new Exception("Specify prices or indexes import");
                    }
                }
                catch (Exception ex)
                {
                    logger.WriteLog(Constants.Failure, null, DateTime.MinValue, null, "Accept command-line parameters", 0, ex);
                    return;
                }

                string[] files;

                try
                {
                    FtpWebRequest requestd = (FtpWebRequest)WebRequest.Create(ftpServerUrl);
                    requestd.KeepAlive   = false;
                    requestd.Method      = WebRequestMethods.Ftp.ListDirectory;
                    requestd.Credentials = new NetworkCredential(ftpUserName, ftpPassword);
                    FtpWebResponse respd       = (FtpWebResponse)requestd.GetResponse();
                    Stream         respdStream = respd.GetResponseStream();
                    StreamReader   readerd     = new StreamReader(respdStream);
                    files = readerd.ReadToEnd().Split(new string[] { "\r\n", "\n" }, StringSplitOptions.RemoveEmptyEntries);
                    readerd.Close();
                    respd.Close();

                    logger.WriteLog(Constants.Success, clientFolder, DateTime.MinValue, null, "List of directory receiving", 0, null);
                }
                catch (Exception ex)
                {
                    logger.WriteLog(Constants.Failure, clientFolder, DateTime.MinValue, null, "List of directory receiving", 0, ex);
                    return;
                }

                foreach (string exchange in exchanges)
                {
                    DateTime      startDate     = startDateInitial;
                    DateTime      endDate       = endDateInitial;
                    List <string> selectedFiles = new List <string>();

                    // Reset date range based on files selected
                    if (args[0].ToLower() == Constants.Last)
                    {
                        DateTime lastDate = DateTime.MinValue;
                        foreach (string filename in files)
                        {
                            if (CheckFilename(filename, exchanges) == false)
                            {
                                continue;
                            }
                            if ((GetExchangeFromFilename(filename) == exchange) && (GetDateFromFilename(filename) > lastDate))
                            {
                                lastDate = GetDateFromFilename(filename);
                            }
                        }

                        if (lastDate == DateTime.MinValue)
                        {
                            continue;
                        }

                        startDate = lastDate.AddDays(-1);
                        endDate   = lastDate;
                    }

                    // Build list of file NAMES for each EXCHANGE separately
                    foreach (string filename in files)
                    {
                        if (CheckFilename(filename, exchanges) == false)
                        {
                            continue;
                        }
                        if ((GetExchangeFromFilename(filename) == exchange) && (GetDateFromFilename(filename) >= startDate) && (GetDateFromFilename(filename) <= endDate))
                        {
                            selectedFiles.Add(filename);
                        }
                    }


                    //////---------------  OR  --- (For running with files already in 'clientFolder')--(TEMPORARY, DO NOT DELETE!!!)---------
                    ////if (Directory.Exists(clientFolder))
                    ////{
                    ////    //Directory.Delete(tempFolder, true);
                    ////    string[] exchangeFiles = Directory.GetFiles(clientFolder);
                    ////    for (int jj = 0; jj < exchangeFiles.Length; jj++)
                    ////    {
                    ////        if (exchangeFiles[jj].Contains(exchange))
                    ////            selectedFiles.Add(exchangeFiles[jj].Substring(exchangeFiles[jj].LastIndexOf("\\")+1));
                    ////    }
                    ////}


                    string sp = null;

                    // DELETE prices in given price range, prior to writing new ones
                    try
                    {
                        if (importPrices == true)
                        {
                            sp = Constants.importEODCleanDataForEODImport;
                            SqlCommand cleanDataSP = new SqlCommand(string.Empty, conn);
                            cleanDataSP.CommandType    = System.Data.CommandType.StoredProcedure;
                            cleanDataSP.CommandText    = sp;
                            cleanDataSP.CommandTimeout = Constants.SPsTimeout;
                            cleanDataSP.Parameters.Add("@exchange", SqlDbType.VarChar, 20).Value  = exchange;   // is it Symbol???
                            cleanDataSP.Parameters.Add("@dates_seq", SqlDbType.VarChar, 20).Value = "RANGE";
                            cleanDataSP.Parameters.Add("@start_date", SqlDbType.DateTime).Value   = startDate;
                            cleanDataSP.Parameters.Add("@end_date", SqlDbType.DateTime).Value     = endDate;
                            cleanDataSP.ExecuteNonQuery();
                            logger.WriteLog(Constants.Success, exchange, DateTime.MinValue, sp, "Succesfull table data cleaning", 0, null);
                        }

                        if (importIndexes == true)
                        {
                            sp = Constants.importCleanDataForEODIndexesImport;
                            SqlCommand cleanDataSP = new SqlCommand(string.Empty, conn);
                            cleanDataSP.CommandType    = System.Data.CommandType.StoredProcedure;
                            cleanDataSP.CommandText    = sp;
                            cleanDataSP.CommandTimeout = Constants.SPsTimeout;
                            cleanDataSP.Parameters.Add("@id_exchange", SqlDbType.Int).Value       = indexes_exchange_id;
                            cleanDataSP.Parameters.Add("@dates_seq", SqlDbType.VarChar, 20).Value = "RANGE";
                            cleanDataSP.Parameters.Add("@start_date", SqlDbType.DateTime).Value   = startDate;
                            cleanDataSP.Parameters.Add("@end_date", SqlDbType.DateTime).Value     = endDate;
                            cleanDataSP.ExecuteNonQuery();
                            logger.WriteLog(Constants.Success, exchange, DateTime.MinValue, sp, "Succesfull table data cleaning", 0, null);
                        }
                    }
                    catch (Exception ex)
                    {
                        logger.WriteLog(Constants.Failure, exchange, DateTime.MinValue, sp, "Unsuccessfull table data cleaning", 0, ex);
                        continue;
                    }

                    EODFilenamesComparer filenameComparer = new EODFilenamesComparer();
                    selectedFiles.Sort(filenameComparer);

                    foreach (string filename in selectedFiles)
                    {
                        // Download text files to 'clientFolder' via FTP
                        try
                        {
                            string        fullUrl      = ftpServerUrl + "/" + filename;
                            string        fullFilename = clientFolder + "\\" + filename;
                            FtpWebRequest requestf     = (FtpWebRequest)WebRequest.Create(fullUrl);
                            requestf.KeepAlive   = false;
                            requestf.Method      = WebRequestMethods.Ftp.DownloadFile;
                            requestf.Credentials = new NetworkCredential(ftpUserName, ftpPassword);
                            FtpWebResponse respf       = (FtpWebResponse)requestf.GetResponse();
                            Stream         respfStream = respf.GetResponseStream();
                            StreamReader   readerf     = new StreamReader(respfStream);
                            string         fileContent = readerf.ReadToEnd();
                            readerf.Close();
                            respf.Close();
                            if (!Directory.Exists(clientFolder))
                            {
                                Directory.CreateDirectory(clientFolder);
                            }
                            File.WriteAllText(fullFilename, fileContent);

                            logger.WriteLog(Constants.Success, filename, GetDateFromFilename(filename), null, "Succesfull FTP csv file import", 1, null);
                        }
                        catch (Exception ex)
                        {
                            logger.WriteLog(Constants.Error, filename, GetDateFromFilename(filename), null, "Unsuccessfull FTP csv file import", 0, ex);
                            continue;
                        }

                        string spi = null;
                        try
                        {
                            if (importPrices == true)
                            {
                                spi = Constants.importEODImport;
                                SqlCommand importSP = new SqlCommand(string.Empty, conn);
                                importSP.CommandType = System.Data.CommandType.StoredProcedure;
                                importSP.CommandText = spi;



                                importSP.CommandTimeout = Constants.SPsTimeout;
                                DataTable dt = GetPriceData(clientFolder + "\\" + filename);
                                importSP.Parameters.Add("@EOD_file_data", SqlDbType.Structured).Value = dt; //clientFolder + "\\" + filename;
                                importSP.Parameters.Add("@exchange", SqlDbType.VarChar, 20).Value     = GetExchangeFromFilename(filename);
                                importSP.Parameters.Add("@date", SqlDbType.DateTime).Value            = GetDateFromFilename(filename);
                                importSP.ExecuteNonQuery();
                                logger.WriteLog(Constants.Success, filename, GetDateFromFilename(filename), spi, "Succesfull security prices data import", 1, null);
                            }

                            if (importIndexes == true)
                            {
                                spi = Constants.importEODIndexes;
                                SqlCommand importSP = new SqlCommand(string.Empty, conn);
                                importSP.CommandType    = System.Data.CommandType.StoredProcedure;
                                importSP.CommandText    = spi;
                                importSP.CommandTimeout = Constants.SPsTimeout;
                                DataTable dt = GetPriceData(clientFolder + "\\" + filename);
                                importSP.Parameters.Add("@EOD_file_data", SqlDbType.Structured).Value = dt;
                                importSP.Parameters.Add("@exg_code", SqlDbType.Int).Value             = indexes_exchange_id;
                                importSP.Parameters.Add("@date", SqlDbType.DateTime).Value            = GetDateFromFilename(filename);
                                importSP.ExecuteNonQuery();
                                logger.WriteLog(Constants.Success, filename, GetDateFromFilename(filename), spi, "Succesfull index prices data import", 1, null);
                            }

                            spi = null;
                            if (doDeleteFiles == "0")
                            {
                                MoveFile(clientFolder + "\\" + filename, processedFolder + "\\" + filename);
                            }
                        }
                        catch (Exception ex)
                        {
                            if (doDeleteFiles == "0")
                            {
                                MoveFile(clientFolder + "\\" + filename, rejectedFolder + "\\" + filename);
                            }

                            logger.WriteLog(Constants.Error, filename, GetDateFromFilename(filename), spi, "Unsuccesfull security/index prices table data import", 0, ex);
                        }
                        //TODO: Delete file in ClientFolder
                        if (doDeleteFiles == "1")
                        {
                            File.Delete(clientFolder + "\\" + filename);
                        }
                    }

                    // TODO: Update tblSel_StockExchanges.LastUpdate for proper Exchange for Today
                    if (importIndexes == true)
                    {
                        GeneralFunctions.SetLastUpdateDate(conn, indexes_exchange_id);      //LR    Q1: do we have to update it if not successfull ??
                    }
                    if (importPrices == true)
                    {
                        GeneralFunctions.SetLastUpdateDate(conn, exchange);                 //LR    Q1: do we have to update it if not successfull ??
                    }
                }// foreach(string exchange............)
            }
        }
Esempio n. 2
0
        public static void Execute(string[] args, Dictionary <string, string> config, int[] exchangeID, List <string> secWithDividOrSplit, bool isTASE = false)
        {
            string clientFolder      = null;
            string processedFolder   = null;
            string rejectedFolder    = null;
            string ftpServerUrlstart = null;
            //string ftpServerUrl = null;
            string ftpUserName     = null;
            string ftpPassword     = null;
            string doDeleteFiles   = null;
            int    number_of_pages = 2;
            double shekToAgorot;

            string   intriMediaURL;
            DateTime importDate = DateTime.Today.AddDays(-1);   // For 'Daily...' import
            DateTime startDate  = importDate;
            DateTime endDate    = importDate;

            clientFolder      = config[Constants.SourceDir];
            processedFolder   = config[Constants.ProcessedDir];
            rejectedFolder    = config[Constants.RejectedDir];
            ftpServerUrlstart = config[Constants.IntrinioPricesFTPServerURL];
            //ftpServerUrl = string.Format("{0}{1}&{2}", ftpServerUrlstart, "AAPL", config[Constants.QuandlAPIkey]);
            ftpUserName = config[Constants.IntrinioSecuritiesFTPUserName];
            ftpPassword = config[Constants.IntrinioSecuritiesFTPPassword];
            string fullUrl;

            doDeleteFiles = config[Constants.DoDeleteFiles];

            if (isTASE)
            {
                // TASE daily prices
                intriMediaURL   = @"https://api.intrinio.com/prices/exchange.csv?identifier=^XTAE&price_date={0}&page_number={1}";
                number_of_pages = 1;
                shekToAgorot    = 100.0;
            }
            else
            {
                // Daily(yesterday) or for date periods
                intriMediaURL   = @"https://api.intrinio.com/prices/exchange.csv?identifier=^USCOMP&price_date={0}&page_number={1}";
                number_of_pages = 2;
                shekToAgorot    = 1.0;
            }

            using (SqlConnection conn = new SqlConnection(config[Constants.DB_ConnectionString_Quandl].ToString()))
            {
                conn.Open();
                Logger logger = new Logger(conn, Constants.IntrinioPricesImport);
                string sp     = null;

                string filename    = null;
                string fileContent = null;
                ////////string intri_symbol;
                ////////string id_exchange;
                List <string> listSymbols             = new List <string>();
                DataTable     importedSecuritiesTable = getPricesTableStructure();

                // Define if Daily or Date range
                if (args[0].ToLower() != Constants.Daily)
                {
                    startDate = DateTime.ParseExact(args[0], Constants.DateParmsFormat, System.Globalization.CultureInfo.CurrentCulture);
                    endDate   = DateTime.ParseExact(args[1], Constants.DateParmsFormat, System.Globalization.CultureInfo.CurrentCulture);
                }

                List <DateTime> listImportDates = GetDateRange(startDate, endDate);

                //////// Get securities
                //////string query = string.Format("SELECT  idStockExchange, intrinioSymbol FROM tblSel_StockExchanges where idStockExchange in ({0})", string.Join(", ", exchangeID));
                //////DataTable dtIntriSymbol = getDBTable(query, conn);

                //////for (int i = 0; i < dtIntriSymbol.Rows.Count; i++)
                //////{
                //////    id_exchange = dtIntriSymbol.Rows[i]["idStockExchange"].ToString();
                //////    intri_symbol = dtIntriSymbol.Rows[i]["intrinioSymbol"].ToString();

                //      OR

                // In case of date range we have list of dates
                for (int i = 0; i < listImportDates.Count; i++)
                {
                    importDate = listImportDates[i];

                    var stopwatch = new Stopwatch();
                    stopwatch.Start();

                    listSymbols.Clear();    // In case of date range - have to clear the list for each date
                    importedSecuritiesTable.Rows.Clear();

                    for (int ll = 1; ll <= number_of_pages; ll++)
                    {
                        try
                        {
                            // Daily and for date periods
                            fullUrl = string.Format(intriMediaURL, importDate.ToString("yyyy-MM-dd"), ll);

                            filename = string.Format("INTRINIO_Prices_page_{0}_{1}.txt", ll, ((isTASE) ? "TASE" : "USA"));
                            string         fullFilename = clientFolder + "\\" + filename;
                            HttpWebRequest requestf     = (HttpWebRequest)WebRequest.Create(fullUrl);
                            requestf.KeepAlive   = false;
                            requestf.Method      = WebRequestMethods.File.DownloadFile;
                            requestf.Credentials = new NetworkCredential(ftpUserName, ftpPassword);
                            HttpWebResponse respf       = (HttpWebResponse)requestf.GetResponse();
                            Stream          respfStream = respf.GetResponseStream();
                            StreamReader    readerf     = new StreamReader(respfStream);
                            fileContent = readerf.ReadToEnd();
                            readerf.Close();
                            respf.Close();
                            if (!Directory.Exists(clientFolder))
                            {
                                Directory.CreateDirectory(clientFolder);
                            }
                            File.WriteAllText(fullFilename, fileContent);

                            //logger.WriteLog(Constants.Success, filename, DateTime.MinValue, null, "Succesfull Intrinio prices FTP file import - " + sec_symbol, 1, null);
                        }
                        catch (Exception ex)
                        {
                            logger.WriteLog(Constants.Failure, filename, DateTime.MinValue, null, string.Format("Unsuccesfull Intrinio {0} prices FTP file import for {1}", ((isTASE) ? "TASE" : "USA"), importDate.ToString("dd-MM-yyyy")), 0, ex);
                            return; // skip to next security
                        }

                        try
                        {
                            string[] securityRecords = fileContent.Split(new string[] { "\n", "\r\n" }, StringSplitOptions.RemoveEmptyEntries);
                            int      j = 0;
                            // loop by imported securities
                            foreach (string securityRec in securityRecords)
                            {
                                j++;
                                if (j == 1 || j == 2)
                                {
                                    continue;                     // skip lines with field titles
                                }
                                DataRow importedSecuruty = importedSecuritiesTable.NewRow();
                                if (getPriceRow(importedSecuruty, securityRec, shekToAgorot))
                                {
                                    if (!listSymbols.Contains(importedSecuruty["symbol"].ToString().ToUpper()))
                                    {
                                        // Add security for price import
                                        importedSecuritiesTable.Rows.Add(importedSecuruty);
                                        importedSecuritiesTable.AcceptChanges();

                                        // Build a list of symbols not to have duplicate entries for the same date, which causes problems in 'Insert...' in SP
                                        listSymbols.Add(importedSecuruty["symbol"].ToString().ToUpper());

                                        // Build a list of securities with dividents or/and splits
                                        if (importDate == DateTime.Today.AddDays(-1))
                                        {
                                            if (Convert.ToDouble(importedSecuruty["dividend"]) != 0)
                                            {
                                                secWithDividOrSplit.Add(importedSecuruty["symbol"].ToString().ToUpper());
                                            }
                                            else if (Convert.ToDouble(importedSecuruty["split"]) != 1)
                                            {
                                                secWithDividOrSplit.Add(importedSecuruty["symbol"].ToString().ToUpper());
                                            }
                                        }
                                    }
                                }
                            }
                        }
                        catch (Exception ex)
                        {
                            logger.WriteLog(Constants.Failure, filename, DateTime.MinValue, sp, "Unsuccesfull Intrinio prices table creation", 0, ex);
                        }

                        if (importedSecuritiesTable.Rows.Count < 1)
                        {
                            continue; // no need to process 2nd page if the first was empty - exit 'For ll ...' loop
                        }
                    }// For ll... for 2 pages of prices

                    if (importedSecuritiesTable.Rows.Count < 1)
                    {
                        continue; // go to the next date in case of date range - exit 'For i ...' loop
                    }
                    try
                    {
                        sp = Constants.importIntrinioPrices;    // "importIntrinioPrices";
                        SqlCommand securityImportCommand = new SqlCommand(String.Empty, conn);
                        securityImportCommand.CommandType    = System.Data.CommandType.StoredProcedure;
                        securityImportCommand.CommandText    = sp;
                        securityImportCommand.CommandTimeout = Constants.SPsTimeout;
                        securityImportCommand.Parameters.Add("@imported_prices", SqlDbType.Structured).Value = importedSecuritiesTable;
                        securityImportCommand.Parameters.Add("@exchanges_list", SqlDbType.VarChar).Value     = string.Join(", ", exchangeID); // id_exchange; //TODO: CHANGE LATER when know what to do with symbols in 2 exchanges
                        securityImportCommand.Parameters.Add("@is_historical", SqlDbType.Int).Value          = 0;
                        //securityImportCommand.Parameters.Add("@symbol", SqlDbType.VarChar).Value = sec_symbol;  // importedSecuritiesTable.Rows[0]["symbol"];
                        //securityImportCommand.Parameters.Add("@id_security", SqlDbType.VarChar).Value = id_Security;    // importedSecuritiesTable.Rows[0]["symbol"];
                        //securityImportCommand.Parameters.Add("@start_date", SqlDbType.DateTime).Value = Convert.ToDateTime(importedSecuritiesTable.Rows[0]["date"]);
                        //securityImportCommand.Parameters.Add("@end_date", SqlDbType.DateTime).Value = Convert.ToDateTime(importedSecuritiesTable.Rows[importedSecuritiesTable.Rows.Count - 1]["date"]);
                        securityImportCommand.ExecuteNonQuery();

                        string sourceFileFullName    = clientFolder + "\\" + filename;
                        string processedFileFullName = processedFolder + "\\" + filename;

                        if (doDeleteFiles == "0")
                        {
                            File.WriteAllText(processedFileFullName, fileContent);
                        }

                        File.Delete(sourceFileFullName);

                        stopwatch.Stop();
                        //elapsed_time = stopwatch.ElapsedMilliseconds;

                        logger.WriteLog(Constants.Success, filename, DateTime.MinValue, sp, string.Format("Succesfull Intrinio {0} prices import for {1}", ((isTASE) ? "TASE" : "USA"), importDate.ToString("dd-MM-yyyy")), 1, null);
                        //logger.WriteLog(Constants.Success, filename, DateTime.MinValue, sp, string.Format("Succesfull Intrinio prices import - {0} ms for {1}", stopwatch.ElapsedMilliseconds.ToString(), importDate.ToString("dd-MM-yyyy")), 1, null);

                        if (args[0].ToLower() == Constants.Daily)
                        {
                            for (int xx = 0; xx < exchangeID.Length; xx++)
                            {
                                GeneralFunctions.SetLastUpdateDate(conn, exchangeID[xx]);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        logger.WriteLog(Constants.Failure, filename, DateTime.MinValue, sp, string.Format("Unsuccesfull Intrinio {0} prices import for {1}", ((isTASE) ? "TASE" : "USA"), importDate.ToString("dd-MM-yyyy")), 0, ex);
                    }
                }// For i... for date range

                //logger.WriteLog(Constants.Success, filename, DateTime.MinValue, sp, string.Format("Succesfull Intrinio {0} prices import - {1} ms", ((whichPrices == Constants.Prices) ? "daily" : "historical"), stopwatch.ElapsedMilliseconds.ToString()), 1, null);
            }// using
        }