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