/// <summary>
/// Get the type of RFS
/// 0 => Rayons
/// 1 => Familles
/// 2 => Ss_famil
/// </summary>
/// <param name="type"></param>
/// <returns>The RFS matching the type, empty string if type out of range</returns>
private static string GetRFSType(RFS.TYPE type)
{
switch (type)
{
case TYPE.RAYON: return(RAYONS);
case TYPE.FAMILLE: return(FAMILLES);
case TYPE.SSFAMIL: return(SS_FAMIL);
default: return(String.Empty);
}
}
/// <summary>
/// Return the name of the rfs by its id and type
/// Type:
/// 1 => Rayon
/// 2 => Famille
/// 3 => Sous-famille
/// </summary>
/// <param name="type"></param>
/// <param name="id"></param>
/// <returns></returns>
public static string GetRFSNameById(RFS.TYPE t, string id, SqlConnection connection)
{
string trimId = id.TrimEnd();
string sql = "select nom from {0} where ID = '{1}'";
string type = GetRFSType(t);
if (type != String.Empty)
{
string sqlCommand = String.Format(sql, type, trimId);
DataTable dt = DBContextFactory.DBContext.Query(sqlCommand);
if (dt.Rows.Count > 0)
{
return(Convert.ToString(dt.Rows[0]["nom"]).TrimEnd());
}
}
return(String.Empty);
}
/// <summary>
/// Synchronize RFS with Nop's categories
/// Sync E-Commerce first (access to everyone)
/// Then Sync Restricted access
/// </summary>
/// <note>RFS to excludes are to add in appconfig file</note>
/// <param name="niveau"></param>
/// <returns></returns>
private bool Sync(RFS.TYPE niveau)
{
//Get Rayons From Mercator
using (SqlConnection connection = new SqlConnection(dataSettings.DataConnectionString))
{
IEnumerable <Category> categories = new List <Category>();
//map mercator's ids with nop commerce's existing ids
IDictionary <string, int> mapMIdNopId = GetMapIdNopId();
categories = GetRFSAsCategories(connection, mapMIdNopId, niveau);
//ID Mercator (url) + id category
//rayonID => urlRecordID
//rayonID + "SLUG" => catID
//var existingUrlRecordsSlugAndCatId = GetExistingUrlRecords(categories);
List <int> categoriesToKeep = new List <int>();
//ADD/EDIT RAYONS
foreach (Category category in categories)
{
try
{
//Format to JSON
JObject json = ParserJSon.ParseRFSToNopCategory(category);
int catId = 0;
//ADD CATEGORY TO NOP
if (mapMIdNopId.ContainsKey(category.MercatorId))
{
try
{
catId = mapMIdNopId[category.MercatorId];
//Send via PATCH/PUT (existing ones)
WebService.Patch(urlBuilder.Id(catId).BuildQuery(), json.ToString());
//Activate url (in case it was deactivated)
JObject urlActivate = new JObject();
urlActivate.Add("IsActive", true);
JObject urls = JObject.Parse(WebService.Get(new UrlBuilder("UrlRecord").And().FilterEq("EntityId", catId).FilterEq("EntityName", ENTITY).BuildQuery()));
var urlsValues = urls["value"].ToArray();
if (urlsValues.Count() > 0)
{
WebService.Patch(new UrlBuilder("UrlRecord").Id((int)urlsValues[0]["Id"]).BuildQuery(), urlActivate.ToString());
}
}
catch (Exception e)
{
Program.log("Exception lors de la mise à jour de la categorie : " + category.Name.TrimEnd());
Program.log("NopId de la catégorie :" + catId);
Program.log(e.Message);
Program.log(e.StackTrace);
}
}
else
{
//Send via POST (new ones)
string result = WebService.Post(ENTITY, json.ToString());
JObject newCat = JObject.Parse(result);
catId = (int)newCat["Id"];
//ADD URL RECORD
JObject urlJson = ParserJSon.GetUrlRecordJson(ENTITY, catId, UrlsSyncer.BuildCategoryUrl(category));
WebService.Post(WebApiEntities.URL_RECORD, urlJson.ToString());
//ADD GENERIC ATTRIBUTE MERCATOR ID
JObject genericAttributeJSon = ParserJSon.GetGenericAttributeJSon(catId, ENTITY, KEY_MERCATORID, category.MercatorId);
WebService.Post(WebApiEntities.GENERIC_ATTRIBUTE, genericAttributeJSon.ToString());
Console.WriteLine(category.Name + " inserted");
}
//Store cat to keep
categoriesToKeep.Add(catId);
}
catch (Exception e)
{
Program.log(String.Format("Error syncing category : {0}.", category.Name));
Program.log(e.Message);
Program.log(e.StackTrace);
}
}
//Delete categories that aren't in Mercator
List <int> existings = new List <int>();
List <JToken> values = niveau == RFS.TYPE.RAYON ? JObject.Parse(WebService.Get(urlBuilder.And().FilterEq("Published", true).FilterEq("ParentCategoryId", 0).BuildQuery()))["value"].ToList()
: (niveau == RFS.TYPE.FAMILLE ? GetNopFamilles() : GetNopSousFamilles());
foreach (JToken v in values)
{
if (!(bool)v["Deleted"])
{
existings.Add((int)v["Id"]);
}
}
var toDelete = existings.Except(categoriesToKeep);
toDelete = toDelete.ToList();
JObject deletedJson = new JObject();
deletedJson.Add("Deleted", true);
JObject urlDeactivated = new JObject();
urlDeactivated.Add("IsActive", false);
foreach (int i in toDelete)
{
//Deactivate the deleted category and delete corresponding urlRecord
//Instead of published : false => Delete : false ou vraiment la supprimer ?
//WebService.Patch(wsCategoryMapping + "(" + s + ")", "{\"Published\":false}");
JObject urls = JObject.Parse(WebService.Get(new UrlBuilder("UrlRecord").And().FilterEq("EntityId", i).FilterEq("EntityName", "Category").BuildQuery()));
var urlsValues = urls["value"].ToArray();
//WebService.Patch("UrlRecord("+urlsValues[0]["Id"].ToString()+")","{\"IsActive\":false}");
if (urlsValues.Count() > 0)
{
Program.log(i + " deleted");
//deleteResult = WebService.Delete(String.Format(WebServiceUrls.URL_RECORD_ID, (int)urlsValues[0]["Id"]));
WebService.Patch(new UrlBuilder("UrlRecord").Id((int)urlsValues[0]["Id"]).BuildQuery(), urlDeactivated.ToString());
}
//deleteResult = WebService.Delete(String.Format(WebServiceUrls.CATEGORY_ID, i));
WebService.Patch(urlBuilder.Id(i).BuildQuery(), deletedJson.ToString());
}
}
return(true);
}
/// <summary>
/// Get the RFS to be synced from Mercator and transorm them into Categories
/// </summary>
/// <param name="connection"></param>
/// <param name="MercIdNopId"></param>
/// <param name="niveau"></param>
/// <returns></returns>
private static IEnumerable <Category> GetRFSAsCategories(SqlConnection connection, IDictionary <string, int> MercIdNopId, RFS.TYPE niveau)
{
connection.Open();
string sqlRayon = @"SELECT id, nom FROM RAYONS WHERE id in (SELECT distinct(s_id_rayon) FROM stock where S_WEB = 1 and S_SOMMEIL = 0 and S_ID_RAYON <> '' and S_MODELE <> '')";
string sqlFam = @"SELECT id, nom, id_rayon FROM FAMILLES WHERE id in (SELECT distinct(s_id_famil) FROM stock where S_WEB = 1 and S_SOMMEIL = 0 and S_ID_RAYON <> '' and S_MODELE <> '')";
string sqlSsFam = @"SELECT id, nom, id_famille FROM SS_FAMIL WHERE id in (SELECT distinct(s_id_ssfam) FROM stock where S_WEB = 1 and S_SOMMEIL = 0 and S_ID_RAYON <> '' and S_MODELE <> '')";
string sql = "";
switch (niveau)
{
case RFS.TYPE.RAYON: sql = sqlRayon; break;
case RFS.TYPE.FAMILLE: sql = sqlFam; break;
case RFS.TYPE.SSFAMIL: sql = sqlSsFam; break;
default: return(null);
}
List <Category> catList = new List <Category>();
using (SqlCommand sqlCommand = new SqlCommand(sql, connection))
using (SqlDataReader reader = sqlCommand.ExecuteReader())
{
try
{
while (reader.Read())
{
int parentCategoryId = 0;
bool exists = false;
if (niveau == RFS.TYPE.FAMILLE)
{
exists = MercIdNopId.TryGetValue(reader["id_rayon"].ToString().Trim(), out parentCategoryId);
}
else if (niveau == RFS.TYPE.SSFAMIL)
{
exists = MercIdNopId.TryGetValue(reader["id_famille"].ToString().Trim(), out parentCategoryId);
}
Category cat = new Category(
reader["nom"].ToString().Trim(),
parentCategoryId,
reader["id"].ToString().Trim()
);
if (!exists && niveau != RFS.TYPE.RAYON)
{
Program.log("Clé non trouvée.");
Program.log("Parent de la catégorie: " + cat.Name + " non trouvé");
}
catList.Add(cat);
}
}
catch (Exception e)
{
Program.log(e.Message);
Program.log(e.StackTrace);
}
finally
{
connection.Close();
}
return(catList);
}
}
