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............)
}
}
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
}