/// <summary>
/// Methode pour lire l object dans le répertoire XML
/// Impossible d utilisr WindowsBase System.IO.Packaging car ZIP n'est pas implémenté dans toutes ses versions
/// </summary>
private Object GetPackageClassInZIP(string rootPath, Type xsdClass, string zipFile, string fileName)
{
InfoLogger.Info("StreamOpen '" + zipFile + "', search '" + fileName + "' with XSD '" + xsdClass + "'");
XmlSerializer x = new XmlSerializer(xsdClass);
StreamReader reader = null;
ZipArchive package = new ZipArchive(rootPath + @"\" + zipFile, FileAccess.Read);
try
{
foreach (ZipArchiveFile part in package.Files)
{
if (fileName.Equals(part.Name))
{
reader = part.OpenText();
// the class object has only one field : a List object
Object datas = x.Deserialize(reader);
return(datas);
}
InfoLogger.Info("Extracted " + part.Name);
}
}
finally
{
if (reader != null)
{
reader.Dispose();
}
x = null;
package.Close();
package = null;
}
InfoLogger.Info("GetPackageClassInZIP END");
return(null);
}
/// <summary>
/// Methode pour lire l object dans le répertoire XML
/// Impossible d utilisr WindowsBase System.IO.Packaging car ZIP n'est pas implémenté dans toutes ses versions
/// </summary>
private Object[] GetAllPackageClassesInZIPFile(string rootPath, XSDSpec[] xsdSpec, string zipFile)
{
InfoLogger.Info("StreamOpen '" + zipFile + "', search all files nb='" + xsdSpec.Length);
Object[] output = new Object[xsdSpec.Length];
int i = 0;
Dictionary <string, XmlSerializer> xsd = new Dictionary <string, XmlSerializer>(xsdSpec.Length);
foreach (XSDSpec xsdS in xsdSpec)
{
xsd.Add(xsdS.xsdFile, new XmlSerializer(xsdS.xsdClass));
}
ZipArchive package = new ZipArchive(rootPath + @"\" + zipFile, FileAccess.Read);
foreach (ZipArchiveFile part in package.Files)
{
StreamReader sr = part.OpenText();
using (XmlTextReader xmlReader = new XmlTextReader(sr))
{
while (xmlReader.Read())
{
//looking for a XML element like this
//xsi:schemaLocation="http://sample.com/sample/contentExchange sample.0.6.2.xsd "
string schemaLocation;
if (xmlReader.NodeType.Equals(XmlNodeType.Element) &&
(schemaLocation = xmlReader.GetAttribute("xsi:schemaLocation")) != null)
{
InfoLogger.Info(schemaLocation);
// make a search for the corresponding XSD Class
foreach (XSDSpec xsdS in xsdSpec)
{
if (schemaLocation.EndsWith(xsdS.xsdFile))
{
XmlSerializer x = xsd[xsdS.xsdFile];
// the class object has only one field : a List object
Object datas = x.Deserialize(xmlReader);
output[i++] = datas;
break;
}
}
break;
}
}
}
InfoLogger.Info("Extracted " + part.Name);
}
return(output);
}
/// <summary>
///Remplit la table destination en mergeant avec le contenu du fichier csv fournie
///
/// Voir en MS SQL 2012 , pour utiliser la nouvelle instruction MERGE
///
/// </summary>
/// <returns></returns>
public void BulkUpsertCsv(string CsvFileName, DatabaseTable targetTableName, string csvFileEncoding = "UTF8", string dateFormat = "DMY", int connectionTimeout = -1)
{
Encoding enc = null;
switch (csvFileEncoding)
{
case "UTF8": enc = Encoding.UTF8; break;
case "ASCII": enc = Encoding.ASCII; break;
case "BigEndianUnicode": enc = Encoding.BigEndianUnicode; break;
case "Unicode": enc = Encoding.Unicode; break;
case "UTF32": enc = Encoding.UTF32; break;
case "UTF7": enc = Encoding.UTF7; break;
default: enc = Encoding.Default; break;
}
;
DatabaseTable usingTableName = new DatabaseTable(targetTableName.schema, "#" + targetTableName.table);
List <string> columns = new List <string>();
using (var reader = new CsvReader(new StreamReader(CsvFileName, enc), true, ';'))
{
try
{
DestinationConnection.Open();
CsvDataAdapter adapter = new CsvDataAdapter(reader);
int nbTobeMergedRows = adapter.Fill(DestinationConnection, usingTableName);
InfoLogger.Info(String.Format("MERGE Table: {0}, using File {1} : nb rows: {2}", targetTableName, CsvFileName, nbTobeMergedRows));
this.BulkUpsert(targetTableName, usingTableName, dateFormat, connectionTimeout);
}
catch (Exception e)
{
throw e;
}
finally
{
DestinationConnection.Close();
}
}
}
/// <summary>
/// BulkUpsert (update and insert) ou BulkMerge
///Remplit la table destination en mergeant avec le contenu du fichier csv fournie
///
/// Execute un UPDATE et ensuite un INSERT sur la connection Target
///
/// Voir en MS SQL 2012 , pour utiliser la nouvelle instruction MERGE
///
/// </summary>
/// <returns></returns>
public void BulkUpsert(DatabaseTable targetTableName, DatabaseTable usingTableName, string dateFormat = "DMY", int connection_timeout = -1)
{
try
{
DestinationConnection.Open();
string tmpUpdatedPKs = "#updatedPK";
int nbUpdatedRows = DestinationConnection.Update(targetTableName, usingTableName, updated_PKs_Table: tmpUpdatedPKs, date_format: dateFormat);
int nbInsertedNewRow = DestinationConnection.Insert(targetTableName, usingTableName, excluded_PKs_Table: tmpUpdatedPKs);
InfoLogger.Info(String.Format("MERGE Table: {0}, using {1} : updatedRows: {2}, new Rows:{3} ", targetTableName, usingTableName, nbUpdatedRows, nbInsertedNewRow));
}
catch (Exception e)
{
throw e;
}
finally
{
DestinationConnection.Close();
}
}
/// <summary>
/// PROGRAMME EXECUTE
/// Parametres: fichier_cours_xls et/ou fichier_prix_xls pour les extractions en cours devises et prix des composants
/// msci_files_path pour le chemin vers les fichiers sources (par defaut:G:\,FGA Systèmes\PRODUCTION\FTP\INDICES\MSCI\)
/// date pour la date utilisée
/// liste_exclusion_prix pour une liste des tickers bloomberg à exclure
/// </summary>
/// <param name="args"></param>
public void Execute(Arguments CommandLine)
{
string[] inutile = CommandLine.Intercept(new string[] { "fichier_cours_csv", "fichier_cours_xls", "fichier_devises_csv", "fichier_devises_xls", "date", "msci_files_path", "liste_exclusion_prix" });
// afficher les parametres passés et inutiles
// prendre ceux qui commencent par @xxx ou #xxx qui représentent les variables
if (inutile.Length > 0)
{
if (InfoLogger.IsInfoEnabled)
{
string liste = "(";
foreach (string s in inutile)
{
if (!s.StartsWith("@") && !s.StartsWith("#"))
{
liste += s + " ";
}
}
liste += ")";
if (liste.Length > 2)
{
InfoLogger.Info("Les parametres suivants ne sont pas exploitees: " + liste);
}
}
}
DataSet DS_fichier_cours = null;
DataSet DS_fichier_devises = null;
//------------------------------------------------------------------------------------------
if (CommandLine["date"] != null)
{
if (CommandLine["msci_files_path"] != null)
{
this.MSCI_Settings = this.GetPathSettings(CommandLine["date"], CommandLine["msci_files_path"]);
}
else
{
this.MSCI_Settings = this.GetPathSettings(CommandLine["date"]);
}
// Soit extraction des cours MSCI
if ((CommandLine["fichier_cours_xls"] != null) || (CommandLine["fichier_cours_csv"] != null))
{
if (CommandLine["liste_exclusion_prix"] != null)
{
string [] excluded = CommandLine["liste_exclusion_prix"].Split(new char[] { ',', ';', ':' });
this.excluded_security_ticker = new HashSet <string>(excluded);
}
DS_fichier_cours = this.Extract_ZIPFile_MSCI_Indexes_Daily_Security();
}
if (CommandLine["fichier_cours_xls"] != null)
{
string outputFileName = this.ParameterFileNameWithDateTime(CommandLine["fichier_cours_xls"], this.MSCI_Settings.RUNNING_TIME);
ExcelFile.CreateWorkbook(DS_fichier_cours, outputFileName);
}
if (CommandLine["fichier_cours_csv"] != null)
{
string outputFileName = this.ParameterFileNameWithDateTime(CommandLine["fichier_cours_csv"], this.MSCI_Settings.RUNNING_TIME);
CSVFile.WriteToCSV(DS_fichier_cours, outputFileName);
}
// Soit extraction des devises MSCI
if ((CommandLine["fichier_devises_xls"] != null) || (CommandLine["fichier_devises_csv"] != null))
{
DS_fichier_devises = this.Extract_ZIPFile_MSCI_Indexes_Daily_ForexRate();
}
if (CommandLine["fichier_devises_xls"] != null)
{
string outputFileName = this.ParameterFileNameWithDateTime(CommandLine["fichier_devises_xls"], this.MSCI_Settings.RUNNING_TIME);
ExcelFile.CreateWorkbook(DS_fichier_devises, outputFileName);
}
if (CommandLine["fichier_devises_csv"] != null)
{
string outputFileName = this.ParameterFileNameWithDateTime(CommandLine["fichier_devises_csv"], this.MSCI_Settings.RUNNING_TIME);
CSVFile.WriteToCSV(DS_fichier_devises, outputFileName);
}
}
}
public void Execute(Arguments CommandLine)
{
string[] inutile = CommandLine.Intercept(new string[] { "dateStart", "dateEnd", "step1", "step2", "step3", "step4", "step5", "calculate", "factsetPath", "modelClassificationPath", "notationISR", "actPtfAlimSQSLrequest", "env" });
// afficher les parametres passés et inutiles
// prendre ceux qui commencent par @xxx ou #xxx qui représentent les variables
if (inutile.Length > 0)
{
if (InfoLogger.IsInfoEnabled)
{
string liste = "(";
foreach (string s in inutile)
{
if (!s.StartsWith("@") && !s.StartsWith("#"))
{
liste += s + " ";
}
}
liste += ")";
if (liste.Length > 2)
{
InfoLogger.Info("Les parametres suivants ne sont pas exploitees: " + liste);
}
}
}
//------------------------------------------------------------------------------------------
if (CommandLine["env"] != null)
{
ENV = CommandLine["env"];
}
//------------------------------------------------------------------------------------------
// 2 configurations: dateStart et dateEnd au format JJ/MM/AAAA , donc toutes les dates comprises entre Start et End, sauf les WE
// dateStart et dateEnd au format MM/AAAA , donc toutes les fins de mois comprises entre Start et End
//------------------------------------------------------------------------------------------
DateTime dStart, dEnd, dEom;
bool eom_config = false;
DateTime.TryParse(CommandLine["dateStart"], out dStart);
if (!DateTime.TryParse(CommandLine["dateEnd"], out dEnd))
{
dEnd = dStart;
}
if (CommandLine["dateStart"].Length <= 7)
{
eom_config = true;
if (CommandLine["dateEnd"].Length <= 7)
{
// take the next 1rst day of month
dEnd = dEnd.AddMonths(1);
if (dEnd.DayOfWeek == DayOfWeek.Saturday)
{
dEnd = dEnd.AddDays(2);
}
else if (dEnd.DayOfWeek == DayOfWeek.Sunday)
{
dEnd = dEnd.AddDays(1);
}
}
}
dEom = dStart;
for (DateTime dateOfData = dStart; dateOfData <= dEnd; dateOfData = dateOfData.AddDays(1))
{
InfoLogger.Info("Donnees du " + dateOfData + " en cours: " + DateTime.Now.ToString());
if (dateOfData.DayOfWeek == DayOfWeek.Saturday || dateOfData.DayOfWeek == DayOfWeek.Sunday)
{
InfoLogger.Info("La date est un WE. Pas d integration " + dateOfData);
continue;
}
if (eom_config)
{
// if the date end of month is the previous month,
if ((dateOfData.Month == 1 && dEom.Month == 12) || dateOfData.Month > dEom.Month)
{
try
{
ACTION_PROCESS(dEom, CommandLine);
}
catch (DirectoryNotFoundException e)
{
InfoLogger.Error("File not found ... continue", e);
}
}
}
else
{
ACTION_PROCESS(dateOfData, CommandLine);
}
dEom = dateOfData;
}
}
public static void ACTION_PROCESS(DateTime d, Arguments CommandLine)
{
string date = d.ToString("dd/MM/yyyy");
InfoLogger.Error("PROCESSING DATE" + date);
//------------------------------------------------------------------------------------------
if (CommandLine["histo"] != null)
{
string root = CommandLine["factsetPath"] ?? @"\\vill1\Partage\TQA\Datas Factset pour Guillaume";
string filepath = root + @"\" + CommandLine["histo"];
ACTION_PROCESS_BulkCopy_DATA_FACTSET_DATA_1(filepath);
}
//------------------------------------------------------------------------------------------
if (CommandLine["step1"] != null)
{
string root = CommandLine["factsetPath"] ?? @"\\vill1\Partage\,FGA MarketData\FACTSET";
string filepath = root + @"\" + d.ToString("yyyyMM") + @"\base_" + d.ToString("yyyyMMdd") + @".csv";
ACTION_PROCESS_BulkCopy_DATA_FACTSET_DATA_1(filepath);
}
//------------------------------------------------------------------------------------------
if (CommandLine["step2"] != null)
{
string filepath = CommandLine["modelClassificationPath"] ?? @"\\vill1\Partage\,FGA Front Office\02_Gestion_Actions\00_BASE\Base 2.0\Modele_Classification.xlsx";
ACTION_PROCESS_BulkCopy_FACTSET_MODELE_CLASSIFICATION_2(date, filepath);
}
//------------------------------------------------------------------------------------------
if (CommandLine["step3"] != null)
{
string filepath = CommandLine["notationISR"] ?? @"\\vill1\Partage\,FGA ISR\Notation Fédéris\NotationISRbase.xlsx";
try
{
ACTION_PROCESS_BulkCopy_IMPORT_ISR_3(filepath);
}
catch (System.Data.SqlClient.SqlException sqle)
{
if (sqle.ErrorCode == 2627) // violation Primary Key
{
InfoLogger.Info("Data already inserted");
}
InfoLogger.Error("Problem on " + filepath, sqle);
}
catch (Exception e)
{
InfoLogger.Error("Problem on " + filepath, e);
}
}
//------------------------------------------------------------------------------------------
if (CommandLine["step4"] != null)
{
string filePath = CommandLine["actPtfAlimSQSLrequest"] ?? @"\\vill1\Partage\,FGA Soft\SQL_SCRIPTS\AUTOMATE\GESTION_ACTION\FCP_Action_BaseTitresDirects.sql";
ACTION_PROCESS_BulkCopy_Import_ACT_PTF_BaseTitreDirects_4(filePath, date);
}
if (CommandLine["step5"] != null)
{
ACTION_PROCESS_BulkCopy_Enrich_5(date);
}
if (CommandLine["calculate"] != null)
{
int to;
if (!Int32.TryParse(CommandLine["timeout"], out to))
{
to = 60 * 60 * 3; // 3 heures par defaut
}
ACTION_PROCESS_BulkCopy_Calculate_6(date, to);
}
}
public static void Info(string message)
{
InfoLogger.Info(message);
}
public void Execute(Arguments CommandLine)
{
string[] inutile = CommandLine.Intercept(new string[] { "dateStart", "dateEnd", "msci", "iboxx", "barclays", "env", "ROOT_PATH", "INDEX", "INDEX_UNIVERSE" });
// afficher les parametres passés et inutiles
// prendre ceux qui commencent par @xxx ou #xxx qui représentent les variables
if (inutile.Length > 0)
{
if (InfoLogger.IsInfoEnabled)
{
string liste = "(";
foreach (string s in inutile)
{
if (!s.StartsWith("@") && !s.StartsWith("#"))
{
liste += s + " ";
}
}
liste += ")";
if (liste.Length > 2)
{
InfoLogger.Info("Les parametres suivants ne sont pas exploitees: " + liste);
}
}
}
//------------------------------------------------------------------------------------------
if (CommandLine["env"] != null)
{
ENV = CommandLine["env"];
}
else
{
ENV = "PREPROD";
}
//------------------------------------------------------------------------------------------
if (CommandLine["msci"] != null)
{
if (CommandLine["dateStart"] != null)
{
DateTime d1;
if (DateTime.TryParse(CommandLine["dateStart"], out d1))
{
DateTime d2;
DateTime.TryParse(CommandLine["dateEnd"], out d2);
//string root = CommandLine["factsetPath"] ?? @"\\vill1\Partage\,FGA MarketData\FACTSET";
MSCIIndexFile f = new MSCIIndexFile(ENV);
f.ExecuteIndexFileIntegration(d1, d2);
}
}
}
//------------------------------------------------------------------------------------------
if (CommandLine["iboxx"] != null)
{
if (CommandLine["dateStart"] != null)
{
DateTime d1;
if (DateTime.TryParse(CommandLine["dateStart"], out d1))
{
DateTime d2;
DateTime.TryParse(CommandLine["dateEnd"], out d2);
//string root = CommandLine["factsetPath"] ?? @"\\vill1\Partage\,FGA MarketData\FACTSET";
iBoxxIndexFile f = new iBoxxIndexFile(ENV);
f.ExecuteIndexFileIntegration(d1, d2);
}
}
}
//------------------------------------------------------------------------------------------
if (CommandLine["barclays"] != null)
{
if (CommandLine["dateStart"] != null)
{
DateTime d1;
if (DateTime.TryParse(CommandLine["dateStart"], out d1))
{
DateTime d2;
DateTime.TryParse(CommandLine["dateEnd"], out d2);
String root_path = CommandLine["ROOT_PATH"] ?? BarclaysIndexFile.INDEX_PATH;
//string root = CommandLine["factsetPath"] ?? @"\\vill1\Partage\,FGA MarketData\FACTSET";
BarclaysIndexFile f = new BarclaysIndexFile(ENV);
f.ExecuteIndexFileIntegration(d1, d2, new object[] { root_path, CommandLine["INDEX_UNIVERSE"], CommandLine["INDEX"] });
}
}
}
//------------------------------------------------------------------------------------------
}
