public static void ACTION_PROCESS_BulkCopy_FACTSET_MODELE_CLASSIFICATION_2(string date, string filepath)
{
DataSet ds = new DataSet("DATA_FACTSET");
MSDBIntegration s = new MSDBIntegration(destinationConnection: ENV);
OpenXMLDataAdapter adapter = new OpenXMLDataAdapter(filepath, "Modele Classification", "B1", "V926", true);
adapter.AddColumnMapping("DATA_FACTSET", "MXEU", "MXEU", System.Type.GetType("System.Double"));
adapter.AddColumnMapping("DATA_FACTSET", "MXUSLC", "MXUSLC", System.Type.GetType("System.Double"));
adapter.AddColumnMapping("DATA_FACTSET", "MXEM", "MXEM", System.Type.GetType("System.Double"));
adapter.AddColumnMapping("DATA_FACTSET", "MXEUM", "MXEUM", System.Type.GetType("System.Double"));
adapter.AddColumnMapping("DATA_FACTSET", "MXFR", "MXFR", System.Type.GetType("System.Double"));
adapter.AddColumnMapping("DATA_FACTSET", "6100001", "6100001", System.Type.GetType("System.Double"));
adapter.AddColumnMapping("DATA_FACTSET", "6100004", "6100004", System.Type.GetType("System.Double"));
adapter.AddColumnMapping("DATA_FACTSET", "6100030", "6100030", System.Type.GetType("System.Double"));
adapter.AddColumnMapping("DATA_FACTSET", "6100033", "6100033", System.Type.GetType("System.Double"));
adapter.AddColumnMapping("DATA_FACTSET", "6100063", "6100063", System.Type.GetType("System.Double"));
adapter.AddColumnMapping("DATA_FACTSET", "AVEURO", "AVEURO", System.Type.GetType("System.Double"));
adapter.AddColumnMapping("DATA_FACTSET", "AVEUROPE", "AVEUROPE", System.Type.GetType("System.Double"));
adapter.AddColumnMapping("DATA_FACTSET", "6100026", "6100026", System.Type.GetType("System.Double"));
adapter.AddColumnMapping("DATA_FACTSET", "6100062", "6100062", System.Type.GetType("System.Double"));
adapter.AddColumnMapping("DATA_FACTSET", "6100002", "6100002", System.Type.GetType("System.Double"));
adapter.AddColumnMapping("DATA_FACTSET", "6100024", "6100024", System.Type.GetType("System.Double"));
adapter.FillColumnWithValue("DATE", date);
adapter.Fill(ds);
DataTable dt = ds.Tables["DATA_FACTSET"];
s.bulkcopyData(dt);
}
public void OpenXMLReadValues_PortefeuilleModele()
{
DataSet ds = new DataSet("PTF_MODELE");
SQLCopy.MSDBIntegration s = new SQLCopy.MSDBIntegration(destinationConnection: "FGA_RW");
/* using (var reader = new OpenXMLDataReader(new StreamReader(@"Portefeuille modèle.xlsm"), null, true, "modele_€", "J7", "R94",null))
* {
*
* OpenXMLDataAdapter adapter = new OpenXMLDataAdapter(reader);
*/
string model_path1 = @"G:\,FGA Front Office\02_Gestion_Actions\01_MODELES\MODELE GARP\Portefeuille modèle.xlsm";
string model_path2 = @"C:\DATA\Portefeuille modèle_20131125.xlsm";
string model_path3 = @"C:\DATA\Portefeuille modèle.xlsm";
OpenXMLDataAdapter adapter = new OpenXMLDataAdapter(model_path1, "modele_€", "J7", "R94", true);
//ITableMapping mapping = adapter.TableMappings.Add("Table", "PTF_FGA");
//mapping.ColumnMappings.Add("CLOSE", "close");
//mapping.ColumnMappings.Add("INDEX", "ponderationIndice");
adapter.AddColumnMapping("PTF_FGA", "COMPANY", "Libelle_Titre");
adapter.AddColumnMapping("PTF_FGA", "QUANTITY", "quantite", System.Type.GetType("System.Double"));
adapter.AddColumnMapping("PTF_FGA", "€CLOSE", "coursclose", System.Type.GetType("System.Double"));
adapter.AddColumnMapping("PTF_FGA", "CRNCY", "Devise_Titre");
adapter.FillColumnWithCell("Dateinventaire", "Q5", System.Type.GetType("System.DateTime"));
adapter.FillColumnWithCell("Libelle_PtfSource", "G3");
adapter.FillColumnWithValue("Groupe", "MODEL_ACT");
adapter.FillColumnWithValue("Compte", "ACT001");
adapter.FillColumnWithValue("ISIN_Ptf", "MODEL_ACT001");
adapter.FillColumnWithValue("Coupon_Couru", 0d);
adapter.FillColumnWithValue("Dateintegration", DateTime.Now);
adapter.Fill(ds);
DataTable dt = ds.Tables["PTF_FGA"];
// conversion de colonne pour mettre dans la BDD
// DataColumn.Expression pour remplir des colonnes
//dt.Columns.Add("quantite", System.Type.GetType("System.Double"), "CONVERT( quantiteSource, System.Double )");
//dt.Columns.Add("coursclose", System.Type.GetType("System.Double"), "CONVERT( courscloseSource, System.Double )");
dt.Columns.Add("Libelle_Ptf", System.Type.GetType("System.String"), "TRIM(Libelle_PtfSource)");
dt.Columns.Add("Valeur_Boursiere", System.Type.GetType("System.Double"), "quantite*coursclose");
dt.Columns.Add("code_Titre", System.Type.GetType("System.String"), "TICKER + ' EQUITY'");
//dt.Columns.Add("Dateinventaire", System.Type.GetType("System.DateTime"), "CONVERT(DateinventaireSource,System.DateTime)");
s.bulkcopyData(dt);
}
public static void ACTION_PROCESS_BulkCopy_IMPORT_ISR_3(string filepath)
{
DataSet ds = new DataSet("ISR_NOTE");
MSDBIntegration s = new MSDBIntegration(destinationConnection: ENV);
OpenXMLDataAdapter adapter = new OpenXMLDataAdapter(filepath, "ISR", "A2", "F3000", true);
adapter.AddColumnMapping("ISR_NOTE", "Note Actions", "Note Actions", System.Type.GetType("System.Double"));
adapter.AddColumnMapping("ISR_NOTE", "Note Credit", "Note Credit", System.Type.GetType("System.Double"));
adapter.AddColumnMapping("ISR_NOTE", "date ", "DATE", System.Type.GetType("System.DateTime"));
adapter.AddColumnMapping("ISR_NOTE", "Name", "NAME");
adapter.Fill(ds);
DataTable dt = ds.Tables["ISR_NOTE"];
s.bulkcopyData(dt);
}
public void ACTION_PROCESS_BulkCopy_IMPORT_ISR_3()
{
string filepath2 = @"G:\,FGA ISR\Notation Fédéris\NotationISRbase.xlsx";
string filepath1 = @"G:\,FGA Soft\INPUT\ACTION\ISR\NotationISRbase.xlsx";
DataSet ds = new DataSet("ISR_NOTE");
SQLCopy.MSDBIntegration s = new SQLCopy.MSDBIntegration(destinationConnection: "FGA_RW");
OpenXMLDataAdapter adapter = new OpenXMLDataAdapter(filepath2, "ISR", "A2", "F1782", true);
adapter.AddColumnMapping("ISR_NOTE", "Note Actions", "Note Actions", System.Type.GetType("System.Double"));
adapter.AddColumnMapping("ISR_NOTE", "Note Credit", "Note Credit", System.Type.GetType("System.Double"));
adapter.AddColumnMapping("ISR_NOTE", "date ", "DATE", System.Type.GetType("System.DateTime"));
adapter.AddColumnMapping("ISR_NOTE", "Name", "NAME");
adapter.Fill(ds);
DataTable dt = ds.Tables["ISR_NOTE"];
s.bulkcopyData(dt);
}
public void OpenXMLReadValues_TestGetColumRowNumbers()
{
double[] i = OpenXMLDataAdapter.GetColumnRowXY("A255");
Assert.AreEqual(i[0], 1);
Assert.AreEqual(i[1], 255);
i = OpenXMLDataAdapter.GetColumnRowXY("IV99999");
Assert.AreEqual(i[0], 256);
Assert.AreEqual(i[1], 99999);
i = OpenXMLDataAdapter.GetColumnRowXY("ZZ1");
Assert.AreEqual(i[0], 702);
Assert.AreEqual(i[1], 1);
i = OpenXMLDataAdapter.GetColumnRowXY("AAA1");
Assert.AreEqual(i[0], 703);
Assert.AreEqual(i[1], 1);
i = OpenXMLDataAdapter.GetColumnRowXY("BAC1");
Assert.AreEqual(i[0], 1381);
Assert.AreEqual(i[1], 1);
i = OpenXMLDataAdapter.GetColumnRowXY("XFD1048576");
Assert.AreEqual(i[0], 16384);
Assert.AreEqual(i[1], 1048576);
}
public void ACTION_PROCESS_BulkCopy_FACTSET_MODELE_CLASSIFICATION_2()
{
string date = "10/02/2014";
//string filepath1 = @"C:\FGA_SOFT\DEVELOPPEMENT\PROJET\FGA_Soft_Front\Front\INPUT\ACTION\FACTSET\Modele_Classification.xlsx";
string filepath2 = @"G:\,FGA Front Office\02_Gestion_Actions\00_BASE\Base 2.0\Modele_Classification.xlsx";
DataSet ds = new DataSet("DATA_FACTSET");
SQLCopy.MSDBIntegration s = new SQLCopy.MSDBIntegration(destinationConnection: "FGA_RW");
OpenXMLDataAdapter adapter = new OpenXMLDataAdapter(filepath2, "Modele Classification", "B1", "V926", true);
adapter.AddColumnMapping("DATA_FACTSET", "MXEU", "MXEU", System.Type.GetType("System.Double"));
adapter.AddColumnMapping("DATA_FACTSET", "MXUSLC", "MXUSLC", System.Type.GetType("System.Double"));
adapter.AddColumnMapping("DATA_FACTSET", "MXEM", "MXEM", System.Type.GetType("System.Double"));
adapter.AddColumnMapping("DATA_FACTSET", "MXEUM", "MXEUM", System.Type.GetType("System.Double"));
adapter.AddColumnMapping("DATA_FACTSET", "MXFR", "MXFR", System.Type.GetType("System.Double"));
adapter.AddColumnMapping("DATA_FACTSET", "6100001", "6100001", System.Type.GetType("System.Double"));
adapter.AddColumnMapping("DATA_FACTSET", "6100004", "6100004", System.Type.GetType("System.Double"));
adapter.AddColumnMapping("DATA_FACTSET", "6100030", "6100030", System.Type.GetType("System.Double"));
adapter.AddColumnMapping("DATA_FACTSET", "6100033", "6100033", System.Type.GetType("System.Double"));
adapter.AddColumnMapping("DATA_FACTSET", "6100063", "6100063", System.Type.GetType("System.Double"));
adapter.AddColumnMapping("DATA_FACTSET", "AVEURO", "AVEURO", System.Type.GetType("System.Double"));
adapter.AddColumnMapping("DATA_FACTSET", "AVEUROPE", "AVEUROPE", System.Type.GetType("System.Double"));
adapter.AddColumnMapping("DATA_FACTSET", "6100026", "6100026", System.Type.GetType("System.Double"));
adapter.AddColumnMapping("DATA_FACTSET", "6100062", "6100062", System.Type.GetType("System.Double"));
adapter.AddColumnMapping("DATA_FACTSET", "6100002", "6100002", System.Type.GetType("System.Double"));
adapter.AddColumnMapping("DATA_FACTSET", "6100024", "6100024", System.Type.GetType("System.Double"));
adapter.FillColumnWithValue("DATE", date);
adapter.Fill(ds);
DataTable dt = ds.Tables["DATA_FACTSET"];
s.bulkcopyData(dt);
}
// TODO: A TESTER
public static void ACTION_PROCESS_BulkCopy_FACTSET_TICKER_CONV()
{
string date = "17/01/2014";
string filepath1 = @"G:\,FGA Front Office\02_Gestion_Actions\00_BASE\Base 2.0\TickerConversion.xlsx";
string filepath2 = @"C:\TickerConversion.xlsx";
DataSet ds = new DataSet("ACT_TICKER_CONVERSION");
MSDBIntegration s = new MSDBIntegration(destinationConnection: "FGA_PREPROD_RW");
OpenXMLDataAdapter adapter = new OpenXMLDataAdapter(filepath2, "Feuil1", "B3", "F51", true);
adapter.AddColumnMapping("ACT_TICKER_CONVERSION", "ISIN", "ISIN");
adapter.AddColumnMapping("ACT_TICKER_CONVERSION", "TICKER", "TICKER");
adapter.AddColumnMapping("ACT_TICKER_CONVERSION", "BBG", "BBG");
adapter.AddColumnMapping("ACT_TICKER_CONVERSION", "EXCH_F", "EXCH_B");
adapter.Fill(ds);
DataTable dt = ds.Tables["ACT_TICKER_CONVERSION"];
s.bulkcopyData(dt);
}
