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