static public void ImportProducts(string market) { sqlRequest sql = new sqlRequest(); string path = ConfigurationManager.AppSettings["ProductSource_Path"].ToString() + market + @"\"; string imagePath = ConfigurationManager.AppSettings["ImageTemp_Path"].ToString() + market + @"\"; string[] columnToIgnore = ConfigurationManager.AppSettings["Ignore_Column"].ToString().Trim().Split(';'); string table_name = "products_" + market; sql.RequestText("TRUNCATE TABLE " + table_name + "_temp"); //-Lire le fichier Excel string[] files = Directory.GetFiles(path); foreach (string file in files) { ManageError.Gestion_Log("Charge file : " + file, null, ManageError.Niveau.Info); FileInfo fInfo = new FileInfo(file); DataTable tmp_table_structure = sql.RequestSelect("SELECT * FROM " + table_name + "_temp LIMIT 0"); if (fInfo.Extension == ".xlsx" || fInfo.Extension == ".xls") { using (Excel excel = new Excel(fInfo.FullName)) { Dictionary <String, bool> urlsTraites = new Dictionary <string, bool>(); //Chercher tous les entetes String colName = String.Empty; //Verification des noms de champs int colCount = excel.ColumnCount(); List <string> columns = new List <string>(); for (int colIndex = 0; colIndex < colCount; ++colIndex) { colName = excel.Cell(colIndex, 0).Trim(); if (colName.Length > 0) { bool find = false; //Verifier le champs,//-La table peut contenir des champs dont le fichier ne dispose pas, mais pas l'inverse foreach (DataColumn tableColNameColumn in tmp_table_structure.Columns) { if (tableColNameColumn.ColumnName.ToString().ToUpper() == colName.ToUpper()) { find = true; break; } } if (find == true) { columns.Add(colName); } else { //Rejeter une exception si on trouve une colonne qui n'existe pas dans la structure de la table + ne peut pas ignoré if (columnToIgnore.Contains(colName) == false) { string errorInfo = "Impossible de trouver la colonne <" + colName + "> dans la table " + table_name + "_temp, Modifer la strucuture de cette table."; ManageError.Gestion_Log(errorInfo, null, ManageError.Niveau.Info); throw new Exception(errorInfo); } } } } //-Inserer dans la table temporaire int row_count = excel.RowCount(); for (int row_index = 1; row_index < row_count; ++row_index) { DataRow tmp_table_row = tmp_table_structure.NewRow(); for (int col_index = 0; col_index < colCount; ++col_index) { string excel_col_name = excel.Cell(col_index, 0); if (columnToIgnore.Contains(excel_col_name)) { continue; } string col_str = columns[col_index]; tmp_table_row[col_str] = excel.Cell(col_index, row_index).Trim(); } string url = tmp_table_row["URL"].ToString(); if (urlsTraites.ContainsKey(url) == false) { tmp_table_structure.Rows.Add(tmp_table_row); urlsTraites[url] = true; } } //Inserer dans la table temporaire sql.BuldInsert(tmp_table_structure, table_name + "_temp"); ManageError.Gestion_Log("Insert " + tmp_table_structure.Rows.Count + " into " + table_name + "_temp", null, ManageError.Niveau.Info); //-Calculer les keywods*/ JArray kw = RefreshKeywordsTree(); RefreshProductKeywords(kw, table_name + "_temp"); ManageError.Gestion_Log("Refresh keywords for " + table_name + "_temp", null, ManageError.Niveau.Info); //Traiter la table temporaire pour inserer dans la table principale sql.StoredProcedure(market + "_HandleTempTable", new List <MySqlParameter>(), 0); ManageError.Gestion_Log("Handle tmp table", null, ManageError.Niveau.Info); //-Calculer les caracteres pour les inserer dans la table des caracteres ProductCaractere pc = new ProductCaractere(); pc.Interpret(market, table_name + "_temp", sql); ManageError.Gestion_Log("Build caracteres list", null, ManageError.Niveau.Info); //-Telecharger + Upload images DataTable products2download = sql.RequestSelect("SELECT * FROM " + table_name + "_temp", 0); using (WebClient client = new WebClient()) { Regex regex = new Regex(@"https?://[^/\s]+/\S+\.(jpg|png|gif)"); String login = System.Configuration.ConfigurationManager.AppSettings["sftp_login"].ToString(); String password = System.Configuration.ConfigurationManager.AppSettings["sftp_password"].ToString(); String adresse = System.Configuration.ConfigurationManager.AppSettings["sftp_adresse"].ToString(); int port = System.Convert.ToInt32(System.Configuration.ConfigurationManager.AppSettings["sftp_port"].ToString()); string basePath = System.Configuration.ConfigurationManager.AppSettings["sftp_basepath"].ToString(); Renci.SshNet.SftpClient oSFTP = new Renci.SshNet.SftpClient(adresse, port, login, password); oSFTP.Connect(); foreach (DataRow product_row in products2download.Rows) { //Creer le dossier string directory_path = imagePath + product_row["Id"].ToString() + @"\"; if (Directory.Exists(imagePath + product_row["Id"].ToString()) == false) { Directory.CreateDirectory(directory_path); } string remoteDir = basePath + "img/" + market + "/" + product_row["Id"].ToString() + "/"; if (oSFTP.Exists(remoteDir) == false) { oSFTP.CreateDirectory(remoteDir); } Dictionary <string, string> finished_images = new Dictionary <string, string>(); String urls = product_row["images"].ToString(); Match match = regex.Match(urls); // int imgCount = 0; while (match.Success) { /*if (imgCount > 0) { * break; * }*/ String imgUrl = match.Value; if (finished_images.Keys.Contains(imgUrl) == false && imgUrl.Contains("50x50.jpg") == false) { Uri uri = new Uri(imgUrl); string filename = (finished_images.Count + 1) + Path.GetExtension(uri.LocalPath); try { /*client.DownloadFile(new Uri(imgUrl), directory_path + filename); * if (File.Exists(directory_path + filename)) * { * FileStream fs = new FileStream(directory_path + filename, FileMode.Open); * oSFTP.UploadFile(fs, remoteDir + filename, true); * fs.Close(); */ finished_images[imgUrl] = "";//(remoteDir + filename); /* File.Delete(directory_path + filename); * imgCount++; * }*/ } catch (Exception e) { ManageError.Gestion_Log("Cannot download image : " + e.Message, null, ManageError.Niveau.Info); } } match = match.NextMatch(); } JObject imgObjet = new JObject(); foreach (string imageurl in finished_images.Keys) { imgObjet[imageurl] = finished_images[imageurl]; } sql.RequestSelect("UPDATE " + table_name + " SET images = @img_json Where Id = @id", new List <MySqlParameter>() { new MySqlParameter("@img_json", WebUtility.HtmlEncode(imgObjet.ToString())), new MySqlParameter("@id", product_row["Id"].ToString()) }); Directory.Delete(directory_path); } oSFTP.Disconnect(); } } } fInfo.Delete(); if (File.Exists(file)) { File.Delete(file); } } }