def update(self): # Suppression des anciens liens avec le format + type de conditionnement query = "MATCH (p:PackageType)-[r1]-(o)-[r2]-(f:Format) WHERE id(o) = " + str(self.id) + " " query += "DELETE r1, r2" get_graph().cypher.execute(query) # Création liens avec le nouveau format + nouveau type de conditionnement query = "MATCH (o), (f), (p), (v) WHERE id(o) = " + str(self.id) + " AND id(f) = " + str(self.format.id) + " " query += "AND id(v) = " + str(self.vintage.id) + " " query += "AND id(p) = " + str(self.package.id) + " MERGE (p)<-[:REL_HAS]-(o)-[:REL_HAS]->(f) SET o.Regie = \"" + self.regie + "\", " query += "o.Comment = \"" + self.comment + "\", v.ReleasePrice = " + str(self.release_price) + ", v.SellingPrice = " + str(self.selling_price) query += ", o.Package = \"" + str(self.package.package) + "\"" get_graph().cypher.execute(query)
def loads(self): query = "MATCH (o:Offer)-[]-(p:Partner) " query += " WHERE id(o) = " + self.id + " OPTIONAL MATCH (o)-[]-(f:Files) RETURN o AS offer, f AS files, id(f) AS fileId, p AS partner" result = get_graph().cypher.execute(query) if result.__len__() != 0: self.partner = result[0].partner.properties["Name"] self.partner_odoo_id = result[0].partner.properties["IdOdoo"] self.line_ok = result[0].offer.properties["LineOK"] self.line_ko = result[0].offer.properties["LineKO"] self.best_price = result[0].offer.properties["BestPrice"] self.offer_date = result[0].offer.properties["DateOffer"] self.spot_date = result[0].offer.properties["EndSpotDate"] self.is_spot = result[0].offer.properties["IsSpot"] self.is_to_process = result[0].offer.properties["IsToProcess"] self.is_loaded = result[0].offer.properties["IsLoaded"] self.is_producer = result[0].offer.properties["IsProducer"] self.is_active = result[0].offer.properties["IsActive"] self.is_deleting = result[0].offer.properties["IsDeleting"] self.disable_date = result[0].offer.properties["DisableDate"] self.is_new_price = result[0].offer.properties["IsNewPrice"] if self.is_new_price == False: self.file_name = result[0].files.properties["Name"] self.file_path = result[0].files.properties["Path"] self.file_id = result[0].fileId else: self.file_name = "Aucun" self.file_path = "" self.file_id = "" else: _logger().error("Error: no Offer with an ID of " + self.id + " found.")
def exist(type): try: query = "MATCH (p:PackageType) WHERE lower(p.Name) = lower(\"" + type + "\") RETURN p" result = get_graph().cypher.execute(query) return len(result) > 0 except Exception: print traceback.format_exc()
def get_list_without_accent_less(node): query = "MATCH (s:" + node + "Synonyme)-[]-(n) WHERE HAS(s.NameWithoutAccent) = false RETURN s ORDER BY n.Name" result = get_graph().cypher.execute(query) tab = [] for it in result: tab.append(Synonyme(it.s)) return tab
def loads(self): query = "MATCH (o:Offer)-[]-(p:Partner) " query += " WHERE id(o) = " + self.id + " OPTIONAL MATCH (o)-[]-(f:Files) RETURN o AS offer, f AS files, id(f) AS fileId, p AS partner" result = get_graph().cypher.execute(query) if result.__len__() != 0: self.partner = result[0].partner.properties["Name"] self.partner_odoo_id = result[0].partner.properties["IdOdoo"] self.line_ok = result[0].offer.properties["LineOK"] self.line_ko = result[0].offer.properties["LineKO"] self.best_price = result[0].offer.properties["BestPrice"] self.offer_date = result[0].offer.properties["DateOffer"] self.spot_date = result[0].offer.properties["EndSpotDate"] self.is_spot = result[0].offer.properties["IsSpot"] self.is_to_process = result[0].offer.properties["IsToProcess"] self.is_loaded = result[0].offer.properties["IsLoaded"] self.is_producer = result[0].offer.properties["IsProducer"] self.is_active = result[0].offer.properties["IsActive"] self.is_deleting = result[0].offer.properties["IsDeleting"] self.disable_date = result[0].offer.properties["DisableDate"] self.is_new_price = result[0].offer.properties["IsNewPrice"] if self.is_new_price == False: self.file_name = result[0].files.properties["Name"] self.file_path = result[0].files.properties["Path"] self.file_id = result[0].fileId else: self.file_name = "Aucun" self.file_path = "" self.file_id = "" else: _logger().error("Error: no Offer with an ID of " + self.id + " found.")
def get_package_list(): tab = [] query = "MATCH (p:PackageType) RETURN p.Name AS package" result = get_graph().cypher.execute(query) for it in result: tab.append(Package(it.package)) return tab
def get_list_without_accent_less(node): query = "MATCH (s:" + node + "Synonyme)-[]-(n) WHERE HAS(s.NameWithoutAccent) = false RETURN s ORDER BY n.Name" result = get_graph().cypher.execute(query) tab = [] for it in result: tab.append(Synonyme(it.s)) return tab
def create(self): if not self.is_new_price: file_path = os.path.dirname(self.file_name) file_name = os.path.basename(self.file_name) file_ok = os.path.splitext(file_name)[0] + "-OK.csv" file_ko = os.path.splitext(file_name)[0] + "-KO.csv" query = "MATCH (soc:Company) WHERE lower(soc.Name) = lower(\"" + self.company + "\") MERGE (p:Partner {IdOdoo: \"" + str( self.partner_odoo_id ) + "\"}) SET p.Name = \"" + self.partner + "\" " query += "CREATE (o:Offer { LineOK: 0, LineKO: 0, BestPrice: 0, DateOffer: \"" + self.offer_date + "\", LastUpdate: \"" + self.offer_date + "\", IsLoaded: false, IsActive: false, IsDeleting: false, " if self.is_spot: query += "IsSpot: true, IsToProcess: false," \ " EndSpotDate: \"" + self.spot_date + "\", " else: query += " IsSpot: false, IsToProcess: true, " if self.is_new_price: query += " IsNewPrice: true, " else: query += " IsNewPrice: false, " if self.is_producer: query += " IsProducer: true }) " else: query += " IsProducer: false }) " if not self.is_new_price: query += "CREATE (o)-[:REL_HAS]->(f:Files {Path: \"" + file_path + "\", Name: \"" + file_name + "\", NameOK: \"" + file_ok + "\", NameKO: \"" + file_ko + "\"})" query += "CREATE (o)-[:REL_HAS]->(p) CREATE (o)-[:REL_UPDATE_PARTNER]->(soc) RETURN id(o)" result = get_graph().cypher.execute(query) if result.__len__() != 0: self.id = result[0].__getattribute__("id(o)") else: self.id = -1 if self.id is not str: self.id = str(self.id) return
def create(self): if not self.is_new_price: file_path = os.path.dirname(self.file_name) file_name = os.path.basename(self.file_name) file_ok = os.path.splitext(file_name)[0] + "-OK.csv" file_ko = os.path.splitext(file_name)[0] + "-KO.csv" query = "MATCH (soc:Company) WHERE lower(soc.Name) = lower(\"" + self.company + "\") MERGE (p:Partner {IdOdoo: \"" + str( self.partner_odoo_id) + "\"}) SET p.Name = \"" + self.partner + "\" " query += "CREATE (o:Offer { LineOK: 0, LineKO: 0, BestPrice: 0, DateOffer: \"" + self.offer_date + "\", LastUpdate: \"" + self.offer_date + "\", IsLoaded: false, IsActive: false, IsDeleting: false, " if self.is_spot: query += "IsSpot: true, IsToProcess: false," \ " EndSpotDate: \"" + self.spot_date + "\", " else: query += " IsSpot: false, IsToProcess: true, " if self.is_new_price: query += " IsNewPrice: true, " else: query += " IsNewPrice: false, " if self.is_producer: query += " IsProducer: true }) " else: query += " IsProducer: false }) " if not self.is_new_price: query += "CREATE (o)-[:REL_HAS]->(f:Files {Path: \"" + file_path + "\", Name: \"" + file_name + "\", NameOK: \"" + file_ok + "\", NameKO: \"" + file_ko + "\"})" query += "CREATE (o)-[:REL_HAS]->(p) CREATE (o)-[:REL_UPDATE_PARTNER]->(soc) RETURN id(o)" result = get_graph().cypher.execute(query) if result.__len__() != 0: self.id = result[0].__getattribute__("id(o)") else: self.id = -1 if self.id is not str: self.id = str(self.id) return
def exist(self): if self.name == "-": return False query = "MATCH (fs:FormatSynonyme)-[]-(f:Format) WHERE lower(fs.Name) = lower(\"" + self.name + "\") RETURN f.Name AS format LIMIT 1" result = get_graph().cypher.execute(query) if len(result): return True return False
def get_list_full(node): where = "" query = "MATCH (s:" + node + "Synonyme)-[]-(n) RETURN s ORDER BY n.Name" result = get_graph().cypher.execute(query) tab = [] for it in result: tab.append(Synonyme(it.s, True)) return tab
def get_list_full(node): where = "" query = "MATCH (s:" + node + "Synonyme)-[]-(n) RETURN s ORDER BY n.Name" result = get_graph().cypher.execute(query) tab = [] for it in result: tab.append(Synonyme(it.s, True)) return tab
def ko_to_ok_line(self, id_ko, offer_line): query = "MATCH (w:Wine)-[]-(c:Color), (w)-[]-(a:Appelation),(ko:OfferLineKO), (o:Offer), (f:Format), (pT:PackageType) " query += "WHERE id(o) = " + str(self.id) + " and lower(w.Name) = lower(\"" + offer_line.wine.name query += "\") and lower(c.Name) = lower(\"" + offer_line.wine.color + "\") and lower(a.Name) = lower(\"" + offer_line.wine.appellation + "\") " query += "AND f.Name = \"" + offer_line.wine.format + "\" AND lower(pT.Name) = lower(\"" + offer_line.wine.package_type + "\") AND id(ko) = " + id_ko query += " SET ko.IsArchived = true" query += " SET o.LineKO = (o.LineKO - 1)" self.add_line_ok(offer_line) return get_graph().cypher.execute(query)
def import_csv_ko(self, file_path): home = expanduser('~') file_path = "file://" + home + "/" + file_path query = "LOAD CSV WITH HEADERS FROM \"" + file_path + "\" AS csvLine FIELDTERMINATOR ';' " query += "MATCH (o:Offer) WHERE id(o) = " + str(self.id) + " " query += "CREATE (ko:OfferLineKO {Quantity: csvLine.quantite, Format: csvLine.format, PackageType: csvLine.type_conditionnement, " \ "Vintage: csvLine.millesime, Wine: csvLine.vin, Price: csvLine.prix, Package: csvLine.conditionnement, " \ "Color: csvLine.couleur, Appelation: csvLine.appelation, IsArchived: false, Regie: csvLine.regie, Comment: csvLine.commentaire})-[:REL_HAS]->(o)" return get_graph().cypher.execute(query)
def update(self): #Suppression des liens query = "MATCH (v:Vintage)-[r1]-(w:Wine)-[r2]-(a:Appelation) " query += ", (w)-[r3]->(c:Color), (w:Wine)-[r4]->(n:Winery) " query += " WHERE id(w) = " + str(self.vin_id) + " " query += "DELETE r2, r3, r4" get_graph().cypher.execute(query) #Création des nouveaux liens + set sur les nouvelles données query = "MATCH (w), (a), (c), (n) WHERE id(w) = " + str(self.vin_id) + " " query += "AND id(a) = " + str(self.appellation_id) + " " query += "AND id(c) = " + str(self.couleur_id) + " " query += "AND id(n) = " + str(self.winery_id) + " " query += "MERGE ((w)-[:REL_HAS]->(a)) MERGE ((w)-[:REL_HAS]->(c)) MERGE ((w)-[:REL_HAS]->(n)) " query += "SET w.Name = \"" + self.wine + "\" " query += "SET w.NameWithoutAccent = \"" + self.name_without_accent + "\"" query += "SET w.RefWine = \"" + self.ref_wine + "\"" get_graph().cypher.execute(query)
def import_csv_ko(self, file_path): home = expanduser('~') file_path = "file://" + home + "/" + file_path query = "LOAD CSV WITH HEADERS FROM \"" + file_path + "\" AS csvLine FIELDTERMINATOR ';' " query += "MATCH (o:Offer) WHERE id(o) = " + str(self.id) + " " query += "CREATE (ko:OfferLineKO {Quantity: csvLine.quantite, Format: csvLine.format, PackageType: csvLine.type_conditionnement, " \ "Vintage: csvLine.millesime, Wine: csvLine.vin, Price: csvLine.prix, Package: csvLine.conditionnement, " \ "Color: csvLine.couleur, Appelation: csvLine.appelation, IsArchived: false, Regie: csvLine.regie, Comment: csvLine.commentaire})-[:REL_HAS]->(o)" return get_graph().cypher.execute(query)
def update(self): # Suppression des anciens liens avec le format + type de conditionnement query = "MATCH (p:PackageType)-[r1]-(o)-[r2]-(f:Format) WHERE id(o) = " + str( self.id) + " " query += "DELETE r1, r2" get_graph().cypher.execute(query) # Création liens avec le nouveau format + nouveau type de conditionnement query = "MATCH (o), (f), (p), (v) WHERE id(o) = " + str( self.id) + " AND id(f) = " + str(self.format.id) + " " query += "AND id(v) = " + str(self.vintage.id) + " " query += "AND id(p) = " + str( self.package.id ) + " MERGE (p)<-[:REL_HAS]-(o)-[:REL_HAS]->(f) SET o.Regie = \"" + self.regie + "\", " query += "o.Comment = \"" + self.comment + "\", v.ReleasePrice = " + str( self.release_price) + ", v.SellingPrice = " + str( self.selling_price) query += ", o.Package = \"" + str(self.package.package) + "\"" get_graph().cypher.execute(query)
def add_line_ok(self, offer_line): query = "MATCH (w:Wine)-[:REL_HAS]-(c:Color), (w)-[r3:REL_HAS]-(a:Appelation), (o:Offer), (f:Format), (pT:PackageType) " query += "WHERE id(o) = " + str(self.id) + " and lower(w.Name) = lower(\"" + offer_line.wine.name query += "\") and lower(c.Name) = lower(\"" + offer_line.wine.color + "\") and lower(a.Name) = lower(\"" + offer_line.wine.appellation + "\") " query += "AND f.Name = \"" + offer_line.wine.format + "\" AND lower(pT.Name) = lower(\"" + offer_line.wine.package_type + "\") " query += " SET o.LineOK = (o.LineOK + 1) " query += "MERGE (v:Vintage {Year: " + offer_line.wine.vintage + "})-[:REL_HAS]->(w) CREATE (ok:OfferLineOK {Quantity: \"" + offer_line.quantity + "\"" query += ", Price: \"" + offer_line.price + "\", IsBestPrice: false, IsActive: true, LastUpdate: o.LastUpdate, Package: \"" + offer_line.wine.package + "\", Regie: \"" + offer_line.regie + "\", Comment: \"" + offer_line.comment + "\"})-[:REL_HAS]->(v) " query += "CREATE (ok)-[:REL_HAS]->(f) CREATE (ok)-[:REL_HAS]->(pT) CREATE (ok)-[:REL_HAS]->(o)" return get_graph().cypher.execute(query)
def get_format_from_synonyme(synonyme): try: query = "MATCH (fs:FormatSynonyme)-[]-(f:Format) WHERE lower(fs.Name) = lower(\"" + synonyme + "\") RETURN f.Name AS format LIMIT 1" result = get_graph().cypher.execute(query) if len(result) != 0: return Format(result[0].format) return None except Exception: print traceback.format_exc() return None
def ko_to_ok_line(self, id_ko, offer_line): query = "MATCH (w:Wine)-[]-(c:Color), (w)-[]-(a:Appelation),(ko:OfferLineKO), (o:Offer), (f:Format), (pT:PackageType) " query += "WHERE id(o) = " + str( self.id) + " and lower(w.Name) = lower(\"" + offer_line.wine.name query += "\") and lower(c.Name) = lower(\"" + offer_line.wine.color + "\") and lower(a.Name) = lower(\"" + offer_line.wine.appellation + "\") " query += "AND f.Name = \"" + offer_line.wine.format + "\" AND lower(pT.Name) = lower(\"" + offer_line.wine.package_type + "\") AND id(ko) = " + id_ko query += " SET ko.IsArchived = true" query += " SET o.LineKO = (o.LineKO - 1)" self.add_line_ok(offer_line) return get_graph().cypher.execute(query)
def update(self): #Suppression des liens query = "MATCH (v:Vintage)-[r1]-(w:Wine)-[r2]-(a:Appelation) " query += ", (w)-[r3]->(c:Color), (w:Wine)-[r4]->(n:Winery) " query += " WHERE id(w) = " + str(self.vin_id) + " " query += "DELETE r2, r3, r4" get_graph().cypher.execute(query) #Création des nouveaux liens + set sur les nouvelles données query = "MATCH (w), (a), (c), (n) WHERE id(w) = " + str( self.vin_id) + " " query += "AND id(a) = " + str(self.appellation_id) + " " query += "AND id(c) = " + str(self.couleur_id) + " " query += "AND id(n) = " + str(self.winery_id) + " " query += "MERGE ((w)-[:REL_HAS]->(a)) MERGE ((w)-[:REL_HAS]->(c)) MERGE ((w)-[:REL_HAS]->(n)) " query += "SET w.Name = \"" + self.wine + "\" " query += "SET w.NameWithoutAccent = \"" + self.name_without_accent + "\"" query += "SET w.RefWine = \"" + self.ref_wine + "\"" get_graph().cypher.execute(query)
def get_format_from_synonyme(synonyme): try: query = "MATCH (fs:FormatSynonyme)-[]-(f:Format) WHERE lower(fs.Name) = lower(\"" + synonyme + "\") RETURN f.Name AS format LIMIT 1" result = get_graph().cypher.execute(query) if len(result) != 0: return Format(result[0].format) return None except Exception: print traceback.format_exc() return None
def add_line_ok(self, offer_line): query = "MATCH (w:Wine)-[:REL_HAS]-(c:Color), (w)-[r3:REL_HAS]-(a:Appelation), (o:Offer), (f:Format), (pT:PackageType) " query += "WHERE id(o) = " + str( self.id) + " and lower(w.Name) = lower(\"" + offer_line.wine.name query += "\") and lower(c.Name) = lower(\"" + offer_line.wine.color + "\") and lower(a.Name) = lower(\"" + offer_line.wine.appellation + "\") " query += "AND f.Name = \"" + offer_line.wine.format + "\" AND lower(pT.Name) = lower(\"" + offer_line.wine.package_type + "\") " query += " SET o.LineOK = (o.LineOK + 1) " query += "MERGE (v:Vintage {Year: " + offer_line.wine.vintage + "})-[:REL_HAS]->(w) CREATE (ok:OfferLineOK {Quantity: \"" + offer_line.quantity + "\"" query += ", Price: \"" + offer_line.price + "\", IsBestPrice: false, IsActive: true, LastUpdate: o.LastUpdate, Package: \"" + offer_line.wine.package + "\", Regie: \"" + offer_line.regie + "\", Comment: \"" + offer_line.comment + "\"})-[:REL_HAS]->(v) " query += "CREATE (ok)-[:REL_HAS]->(f) CREATE (ok)-[:REL_HAS]->(pT) CREATE (ok)-[:REL_HAS]->(o)" return get_graph().cypher.execute(query)
def import_csv_ok(self, file_path): home = expanduser('~') file_path = "file://" + home + "/" + file_path query = "LOAD CSV WITH HEADERS FROM \"" + file_path + "\" AS csvLine FIELDTERMINATOR ';' " query += "MATCH (w:Wine)-[:REL_HAS]-(c:Color), (w)-[r3:REL_HAS]-(a:Appelation), (o:Offer), (f:Format), (pT:PackageType) " query += "WHERE lower(w.Name) = lower(csvLine.vin) and lower(c.Name) = lower(csvLine.couleur) and " query += "lower(a.Name) = lower(csvLine.appelation) and id(o) = " + str(self.id) + " " query += "AND f.Name = csvLine.format and lower(pT.Name) = lower(csvLine.type_conditionnement) " query += "MERGE (v:Vintage {Year: toInt(csvLine.millesime)})-[:REL_HAS]->(w) " query += "CREATE (ok:OfferLineOK {Quantity: csvLine.quantite, Price: csvLine.prix, IsBestPrice: false, IsActive: true, LastUpdate: \"" + self.offer_date + "\", Package: csvLine.conditionnement, Regie: csvLine.regie, Comment: csvLine.commentaire})-[:REL_HAS]->(v) " query += "CREATE (ok)-[:REL_HAS]->(f) CREATE (ok)-[:REL_HAS]->(pT) CREATE (ok)-[:REL_HAS]->(o)" return get_graph().cypher.execute(query)
def remove(self): graph = get_graph() query = "MATCH (o:Offer) WHERE id(o)=" + self.id + " SET o.IsDeleting = true RETURN o" response = graph.cypher.execute(query) if len(response) == 0: return response self.update_best_price() # query = "MATCH (o:Offer)-[r:REL_HAS]-(ok:OfferLineOK)-[r1:REL_HAS]-(v:Vintage)-[r3]-(), " # query += "(bw:Company)-[r5]-(o)-[r6:REL_HAS]-(p:Partner), (o)-[r7]-(ko:OfferLineKO), (pt:PackageType)-[r8]-(ok)-[r9]-(form:Format) WHERE id(o) = " + self.id + " OPTIONAL MATCH (f:Files)-[r4:REL_HAS]-(o)" # query += " DELETE f,r4,o,r,ok,r1,v,r3,r5,r6,r7,ko,r8,r9" query = "MATCH (o:Offer)-[r]-() where id(o) = " + self.id + " delete o,r" # query = "MATCH (o:Offer)-[r]-(:Partner), (ko:OfferLineKO)-[r0]-(o)-[r1]-(ok:OfferLineOK)-[r2]-(), (:Company)-[r3]-(o)-[r4]-(f:Files) WHERE id(o) = " + 9989 + " DELETE o,r,ko,r0,r1,ok,r2,r3,r4,f" return graph.cypher.execute(query)
def _get_offers_from_company(page, where): try: offers = [] company = http.request.env['res.company'].search([])[0].name query = "MATCH (p:Partner)-[]-(o:Offer)-[r]-(c:Company) WHERE " + where + " AND lower(c.Name) = lower(\"" + company + "\") OPTIONAL MATCH (o)-[]-(f:Files) RETURN id(o) AS id, id(f) AS file_id ORDER BY o.DateOffer DESC SKIP " + str((page - 1) * OFFERS_PER_PAGE) + " LIMIT " + str(OFFERS_PER_PAGE) result = get_graph().cypher.execute(query) if len(result) != 0: for it in result: offers.append(Offer(str(it.id))) response = {'offers': offers, 'current_page': page} query = "MATCH (p:Partner)-[]-(o:Offer)-[r]-(c:Company) WHERE " + where + " AND lower(c.Name) = lower(\"" + company + "\") OPTIONAL MATCH (o)-[]-(f:Files) RETURN COUNT(o) AS count" count = get_graph().cypher.execute(query).one if count % OFFERS_PER_PAGE == 0: response['page_count'] = count / OFFERS_PER_PAGE else: response['page_count'] = count / OFFERS_PER_PAGE + 1 return response except Exception as e: print traceback.format_exc() return
def remove(self): graph = get_graph() query = "MATCH (o:Offer) WHERE id(o)=" + self.id + " SET o.IsDeleting = true RETURN o" response = graph.cypher.execute(query) if len(response) == 0: return response self.update_best_price() # query = "MATCH (o:Offer)-[r:REL_HAS]-(ok:OfferLineOK)-[r1:REL_HAS]-(v:Vintage)-[r3]-(), " # query += "(bw:Company)-[r5]-(o)-[r6:REL_HAS]-(p:Partner), (o)-[r7]-(ko:OfferLineKO), (pt:PackageType)-[r8]-(ok)-[r9]-(form:Format) WHERE id(o) = " + self.id + " OPTIONAL MATCH (f:Files)-[r4:REL_HAS]-(o)" # query += " DELETE f,r4,o,r,ok,r1,v,r3,r5,r6,r7,ko,r8,r9" query = "MATCH (o:Offer)-[r]-() where id(o) = " + self.id + " delete o,r" # query = "MATCH (o:Offer)-[r]-(:Partner), (ko:OfferLineKO)-[r0]-(o)-[r1]-(ok:OfferLineOK)-[r2]-(), (:Company)-[r3]-(o)-[r4]-(f:Files) WHERE id(o) = " + 9989 + " DELETE o,r,ko,r0,r1,ok,r2,r3,r4,f" return graph.cypher.execute(query)
def import_csv_ok(self, file_path): home = expanduser('~') file_path = "file://" + home + "/" + file_path query = "LOAD CSV WITH HEADERS FROM \"" + file_path + "\" AS csvLine FIELDTERMINATOR ';' " query += "MATCH (w:Wine)-[:REL_HAS]-(c:Color), (w)-[r3:REL_HAS]-(a:Appelation), (o:Offer), (f:Format), (pT:PackageType) " query += "WHERE lower(w.Name) = lower(csvLine.vin) and lower(c.Name) = lower(csvLine.couleur) and " query += "lower(a.Name) = lower(csvLine.appelation) and id(o) = " + str( self.id) + " " query += "AND f.Name = csvLine.format and lower(pT.Name) = lower(csvLine.type_conditionnement) " query += "MERGE (v:Vintage {Year: toInt(csvLine.millesime)})-[:REL_HAS]->(w) " query += "CREATE (ok:OfferLineOK {Quantity: csvLine.quantite, Price: csvLine.prix, IsBestPrice: false, IsActive: true, LastUpdate: \"" + self.offer_date + "\", Package: csvLine.conditionnement, Regie: csvLine.regie, Comment: csvLine.commentaire})-[:REL_HAS]->(v) " query += "CREATE (ok)-[:REL_HAS]->(f) CREATE (ok)-[:REL_HAS]->(pT) CREATE (ok)-[:REL_HAS]->(o)" return get_graph().cypher.execute(query)
def remove(self): try: graph = get_graph() query = "MATCH (ko:OfferLineKO)-[r]-(o:Offer) WHERE id(ko) = " + self.id + " DELETE ko,r RETURN id(o)" result = graph.cypher.execute(query) if len(result) != 0: offer_id = result[0].__getattribute__('id(o)') offer = Offer(str(offer_id)) line_ok_number = offer.get_nb_line_ok() line_ko_number = offer.get_nb_line_ko() offer.set_lineok_lineko(line_ok_number, line_ko_number) except Exception as e: return Response(Response.ERROR_STATUS, traceback.format_exc()) return Response(Response.SUCCESS_STATUS, Response.SUCCESS_MSG, len(result))
def check_best_price(): offers = [] try: company = http.request.env['res.company'].search([])[0].name query = "MATCH (c:Company)-[]-(o:Offer)-[r]-(p:Partner) WHERE lower(c.Name) = lower(\"" + company + "\") AND lower(p.Name) = lower(\"" + partner + "\") RETURN id(o)" result = get_graph().cypher.execute(query) if len(result) != 0: for it in result: id = str(it.__getattribute__("id(o)")) offers.append(Offer(id)) return offers except Exception as e: print traceback.format_exc() return offers
def update_best_price(self): try: company = http.request.env['res.company'].search([])[0].name query = "MATCH (cp:Company)-[]-(o:Offer)-[]-(ok:OfferLineOK)-[]-(v:Vintage)-[]-(w:Wine)-[]-(c:Color), " \ "(w)-[]-(a:Appelation), (f:Format)-[]-(ok)-[]-(pt:PackageType) WHERE id(o) = " \ "" + self.id + " AND lower(cp.Name) = lower(\"" + company + "\") " \ "RETURN w.Name AS name, a.Name AS appellation, c.Name AS color, v.Year AS vintage, " \ "f.Name AS format, pt.Name AS package_type, ok.Package AS package" result = get_graph().cypher.execute(query) for it in result: wine = Wine(it.__dict__) check_best_price(wine, company) except Exception: _logger().error(traceback.format_exc())
def check_best_price(): offers = [] try: company = http.request.env['res.company'].search([])[0].name query = "MATCH (c:Company)-[]-(o:Offer)-[r]-(p:Partner) WHERE lower(c.Name) = lower(\"" + company + "\") AND lower(p.Name) = lower(\"" + partner + "\") RETURN id(o)" result = get_graph().cypher.execute(query) if len(result) != 0: for it in result: id = str(it.__getattribute__("id(o)")) offers.append(Offer(id)) return offers except Exception as e: print traceback.format_exc() return offers
def _get_offers_from_company(page, where): try: offers = [] company = http.request.env['res.company'].search([])[0].name query = "MATCH (p:Partner)-[]-(o:Offer)-[r]-(c:Company) WHERE " + where + " AND lower(c.Name) = lower(\"" + company + "\") OPTIONAL MATCH (o)-[]-(f:Files) RETURN id(o) AS id, id(f) AS file_id ORDER BY o.DateOffer DESC SKIP " + str( (page - 1) * OFFERS_PER_PAGE) + " LIMIT " + str(OFFERS_PER_PAGE) result = get_graph().cypher.execute(query) if len(result) != 0: for it in result: offers.append(Offer(str(it.id))) response = {'offers': offers, 'current_page': page} query = "MATCH (p:Partner)-[]-(o:Offer)-[r]-(c:Company) WHERE " + where + " AND lower(c.Name) = lower(\"" + company + "\") OPTIONAL MATCH (o)-[]-(f:Files) RETURN COUNT(o) AS count" count = get_graph().cypher.execute(query).one if count % OFFERS_PER_PAGE == 0: response['page_count'] = count / OFFERS_PER_PAGE else: response['page_count'] = count / OFFERS_PER_PAGE + 1 return response except Exception as e: print traceback.format_exc() return
def update_best_price(self): try: company = http.request.env['res.company'].search([])[0].name query = "MATCH (cp:Company)-[]-(o:Offer)-[]-(ok:OfferLineOK)-[]-(v:Vintage)-[]-(w:Wine)-[]-(c:Color), " \ "(w)-[]-(a:Appelation), (f:Format)-[]-(ok)-[]-(pt:PackageType) WHERE id(o) = " \ "" + self.id + " AND lower(cp.Name) = lower(\"" + company + "\") " \ "RETURN w.Name AS name, a.Name AS appellation, c.Name AS color, v.Year AS vintage, " \ "f.Name AS format, pt.Name AS package_type, ok.Package AS package" result = get_graph().cypher.execute(query) for it in result: wine = Wine(it.__dict__) check_best_price(wine, company) except Exception: _logger().error(traceback.format_exc())
def remove(self): try: graph = get_graph() query = "MATCH (ko:OfferLineKO)-[r]-(o:Offer) WHERE id(ko) = " + self.id + " DELETE ko,r RETURN id(o)" result = graph.cypher.execute(query) if len(result) != 0: offer_id = result[0].__getattribute__('id(o)') offer = Offer(str(offer_id)) line_ok_number = offer.get_nb_line_ok() line_ko_number = offer.get_nb_line_ko() offer.set_lineok_lineko(line_ok_number, line_ko_number) except Exception as e: return Response(Response.ERROR_STATUS, traceback.format_exc()) return Response(Response.SUCCESS_STATUS, Response.SUCCESS_MSG, len(result))
def get_list(node, syn_name=None, linked_id=None): where = "" if syn_name is not None and linked_id is None: where = "s.Name =~ \"(?i).*" + syn_name + ".*\" AND " elif syn_name is None and linked_id is not None: where = "id(n) = " + str(linked_id) + " AND " elif syn_name is not None and linked_id is not None: where = "id(n) = " + str(linked_id) + " AND s.Name =~ \"(?i).*" + syn_name + ".*\" AND " where += "lower(s.Name) <> lower(n.Name) " query = "MATCH (s:" + node + "Synonyme)-[]-(n) WHERE " + where + "RETURN s ORDER BY n.Name" result = get_graph().cypher.execute(query) tab = [] for it in result: tab.append(Synonyme(it.s, True)) return tab
def get_list(node, syn_name=None, linked_id=None): where = "" if syn_name is not None and linked_id is None: where = "s.Name =~ \"(?i).*" + syn_name + ".*\" AND " elif syn_name is None and linked_id is not None: where = "id(n) = " + str(linked_id) + " AND " elif syn_name is not None and linked_id is not None: where = "id(n) = " + str( linked_id) + " AND s.Name =~ \"(?i).*" + syn_name + ".*\" AND " where += "lower(s.Name) <> lower(n.Name) " query = "MATCH (s:" + node + "Synonyme)-[]-(n) WHERE " + where + "RETURN s ORDER BY n.Name" result = get_graph().cypher.execute(query) tab = [] for it in result: tab.append(Synonyme(it.s, True)) return tab
def get_offers_from_partner(partner, filter=None): offers = [] try: company = http.request.env['res.company'].search([])[0].name if filter is not None: filter = "AND o.IsActive = true AND o.IsSpot = false " else: filter = "" query = "MATCH (c:Company)-[]-(o:Offer)-[r]-(p:Partner) WHERE lower(c.Name) = lower(\"" + company + "\") AND lower(p.Name) = lower(\"" + partner + "\") " query += filter + "RETURN id(o)" result = get_graph().cypher.execute(query) if len(result) != 0: for it in result: id = str(it.__getattribute__("id(o)")) offers.append(Offer(id)) return offers except Exception as e: _logger().error(traceback.format_exc()) return offers
def get_offers_from_partner(partner, filter=None): offers = [] try: company = http.request.env['res.company'].search([])[0].name if filter is not None: filter = "AND o.IsActive = true AND o.IsSpot = false " else: filter = "" query = "MATCH (c:Company)-[]-(o:Offer)-[r]-(p:Partner) WHERE lower(c.Name) = lower(\"" + company + "\") AND lower(p.Name) = lower(\"" + partner + "\") " query += filter + "RETURN id(o)" result = get_graph().cypher.execute(query) if len(result) != 0: for it in result: id = str(it.__getattribute__("id(o)")) offers.append(Offer(id)) return offers except Exception as e: _logger().error(traceback.format_exc()) return offers
def export_xls_ok(self): try: company = http.request.env['res.company'].search([])[0].name query = "MATCH (part:Partner)-[]-(o:Offer)-[]-(ok:OfferLineOK)-[]-(v:Vintage)-[]-(w:Wine)-[]-(a:Appelation), " query += "(ok)-[]-(f:Format),(ok)-[]-(pt:PackageType),(w)-[]->(c:Color),(o)-[:REL_UPDATE_PARTNER]-(soc:Company), " query += "(o)-[]-(file:Files) " query += "WHERE soc.Name = \"" + company + "\" AND id(o) = " + self.id + " " query += "RETURN ok AS ok, v AS v, w AS w, a AS a, f AS f, pt AS pt, c AS c, file AS file" result = get_graph().cypher.execute(query) home = expanduser("~") + "/" file_name = os.path.basename(result[0].file.properties["Name"]) file_name = os.path.splitext(file_name)[0] + "-LISTE-LIGNE-OK.xls" file_path = result[0].file.properties["Path"] file_path = file_path + "/" + file_name export_ok_file = open(home + file_path, "w") export_ok_obj = ParsingXls(export_ok_file) # PUSH COLUMN LABEL HERE export_ok_obj.append_line([ "Vin", "Appellation", "Couleur", "Millesime", "Prix", "Quantite", "Format", "Conditionnement", "Regie" ]) for it in result: obj = [] obj.append(it.w.properties["Name"]) obj.append(it.a.properties["Name"]) obj.append(it.c.properties["Name"]) obj.append(str(int(it.v.properties["Year"]))) obj.append(it.ok.properties["Price"].replace(".", ",", 1)) obj.append(it.ok.properties["Quantity"]) obj.append(it.f.properties["Name"]) obj.append(it.pt.properties["Name"] + it.ok.properties["Package"]) obj.append(it.ok.properties["Regie"]) export_ok_obj.append_line(obj) export_ok_obj.book.save(export_ok_file) export_ok_file.close() return file_path except Exception as e: print traceback.format_exc()
def export_xls_ok(self): try: company = http.request.env['res.company'].search([])[0].name query = "MATCH (part:Partner)-[]-(o:Offer)-[]-(ok:OfferLineOK)-[]-(v:Vintage)-[]-(w:Wine)-[]-(a:Appelation), " query += "(ok)-[]-(f:Format),(ok)-[]-(pt:PackageType),(w)-[]->(c:Color),(o)-[:REL_UPDATE_PARTNER]-(soc:Company), " query += "(o)-[]-(file:Files) " query += "WHERE soc.Name = \"" + company + "\" AND id(o) = " + self.id + " " query += "RETURN ok AS ok, v AS v, w AS w, a AS a, f AS f, pt AS pt, c AS c, file AS file" result = get_graph().cypher.execute(query) home = expanduser("~") + "/" file_name = os.path.basename(result[0].file.properties["Name"]) file_name = os.path.splitext(file_name)[0] + "-LISTE-LIGNE-OK.xls" file_path = result[0].file.properties["Path"] file_path = file_path + "/" + file_name export_ok_file = open(home + file_path, "w") export_ok_obj = ParsingXls(export_ok_file) # PUSH COLUMN LABEL HERE export_ok_obj.append_line(["Vin", "Appellation", "Couleur", "Millesime", "Prix", "Quantite", "Format", "Conditionnement", "Regie"]) for it in result: obj = [] obj.append(it.w.properties["Name"]) obj.append(it.a.properties["Name"]) obj.append(it.c.properties["Name"]) obj.append(str(int(it.v.properties["Year"]))) obj.append(it.ok.properties["Price"].replace(".", ",", 1)) obj.append(it.ok.properties["Quantity"]) obj.append(it.f.properties["Name"]) obj.append(it.pt.properties["Name"] + it.ok.properties["Package"]) obj.append(it.ok.properties["Regie"]) export_ok_obj.append_line(obj) export_ok_obj.book.save(export_ok_file) export_ok_file.close() return file_path except Exception as e: print traceback.format_exc()
def logical_remove(self): query = "MATCH (n) WHERE id(n) = " + str(self.id) + " SET n.IsDeleted = true" get_graph().cypher.execute(query)
def get_partner(self): query = "MATCH (o:Offer)-[r]-(p:Partner) WHERE id(o) = " + self.id + " RETURN p.Name" result = get_graph().cypher.execute(query) if len(result) != 0: return result[0].__getattribute__("p.Name") return ""
def get_partner(self): query = "MATCH (o:Offer)-[r]-(p:Partner) WHERE id(o) = " + self.id + " RETURN p.Name" result = get_graph().cypher.execute(query) if len(result) != 0: return result[0].__getattribute__("p.Name") return ""
def set_is_active(self, is_active): if is_active is not str: is_active = str(is_active) query = "MATCH (o:Offer) WHERE id(o) = " + self.id + " SET o.IsActive = " + is_active + " RETURN o" result = get_graph().cypher.execute(query) return result
def get_nb_line_ko(self): query = "MATCH (o:Offer)-[r]-(ko:OfferLineKO) WHERE id(o) = " + self.id + " AND ko.IsArchived = false RETURN COUNT(ko)" result = get_graph().cypher.execute(query) if len(result) != 0: return result[0].__getattribute__('COUNT(ko)') return 0
def publish(self): query = "MATCH (o:Offer) WHERE id(o) = " + self.id + " SET o.IsToProcess = false, o.IsActive = true" return get_graph().cypher.execute(query)
def fusion(self): #On "Marque" les vintages du vin cible pour préparer le Merge des OfferLineOk query = "MATCH (v:Vintage)-[r1]-(w:Wine) " query += "WHERE id(w) in [" + str(self.vin_cible_id) + "] " query += "SET v.Status = \"cible\"" #query += "RETURN v, w, ws " get_graph().cypher.execute(query) #On Crée une relations entre le vin source et le vin cible afin de pouvoir récupérer les id une fois fusionnés query = "MATCH (w:Wine) " query += "WHERE id(w) in [" + str(self.vin_cible_id) + ", " + str(self.vin_source_id) + "] " query += "WITH w " query += "START n=node(" + str(self.vin_cible_id) + "), m=node(" + str(self.vin_source_id) + ") " query += "MERGE (n)<-[:IS_FUSION]-(m) " query += "SET m.IsFusion = " + str(self.vin_cible_id) + " " get_graph().cypher.execute(query) #On MERGE les nouveaux liens query = "MATCH (v:Vintage), (w:Wine), (ws:WineSynonyme) " query += "WHERE id(w) in [" + str(self.vin_cible_id) + "] " query += "AND id(v) in " + str(self.vin_source_vintage_id) + " " query += "AND id(ws) in " + str(self.vin_source_synonyme_id) + " " query += "MERGE ((v)-[r:REL_HAS]->(w))" query += "MERGE ((ws)-[:REL_HAS]->(w))" #query += "RETURN v, w, ws " get_graph().cypher.execute(query) #On met une propriété IsDelete = TRUE sur les anciennes relations query = "MATCH (v:Vintage)-[r1]-(w:Wine)-[r2]-(ws:WineSynonyme) " query += "WHERE id(w) in [" + str(self.vin_source_id) + "] " query += "AND id(v) in " + str(self.vin_source_vintage_id) + " " query += "AND id(ws) in " + str(self.vin_source_synonyme_id) + " " query += "SET r1.IsDelete = TRUE " query += "SET r2.IsDelete = TRUE " query += "SET w.IsDelete = TRUE " #query += "RETURN v, w, ws " get_graph().cypher.execute(query) #Fusion des OfferLineOK sur les vintage qui n'ont pas été MERGE query = "MATCH (w)-[r1]-(v) " query += "WHERE id(w) in [" + str(self.vin_cible_id) + "] AND v.Year in " + str(self.vin_source_vintage) + " " query += "WITH v " query += "ORDER BY v.Status " query += "WITH v.Year AS year, collect(v) AS vintage, count(*) AS cnt " #query += "WHERE cnt > 0 " query += "WITH head(vintage) AS first, tail(vintage) AS rest " #query += "LIMIT 10000 " query += "UNWIND rest AS to_delete " query += "MATCH (to_delete)<-[r:REL_HAS]-(oflo:OfferLineOK) " query += "SET r.IsDelete = TRUE " query += "MERGE (first)<-[r2:REL_HAS]-(oflo) " query += "SET r2.Status = \"New\" " #query += "RETURN count(*); " get_graph().cypher.execute(query) #SET de la propriété IsDelete = TRUE sur les vintage en doublons query = "MATCH (w:Wine)-[r1]-(v:Vintage) " query += "WHERE id(w) in [" + str(self.vin_cible_id) + "] " query += "WITH v.Year as Year, count(v.Year) as cntYear, collect(v) as vintage " query += "WHERE cntYear > 1 " query += "UNWIND vintage as to_delete " query += "MATCH (to_delete)-[]-(w) " query += "WHERE id(w) in [" + str(self.vin_source_id) + "] " query += "SET to_delete.IsDelete = TRUE " #query += "RETURN Year, cntYear, to_delete " get_graph().cypher.execute(query)
def get_nb_line_ok(self): query = "MATCH (o:Offer)-[r]-(ok:OfferLineOK) WHERE id(o) = " + self.id + " RETURN COUNT(ok)" result = get_graph().cypher.execute(query) if len(result) != 0: return result[0].__getattribute__('COUNT(ok)') return 0
def set_lineok_lineko(self, line_ok, line_ko): query = "MATCH (o:Offer) WHERE id(o) = " + self.id + " SET o.LineOK = " + str( line_ok) + ", o.LineKO = " + str(line_ko) + " RETURN o" rewrite(("http", "0.0.0.0", 7474), ("http", "52.48.147.125", 7474)) get_graph().cypher.execute(query)
def set_is_loaded(self, boolean): query = "MATCH (o:Offer) where id(o) = " + self.id + " SET o.IsLoaded = " + str( boolean).lower() + " RETURN o" rewrite(("http", "0.0.0.0", 7474), ("http", "52.48.147.125", 7474)) get_graph().cypher.execute(query)
def set_is_active(self, is_active): if is_active is not str: is_active = str(is_active) query = "MATCH (o:Offer) WHERE id(o) = " + self.id + " SET o.IsActive = " + is_active + " RETURN o" result = get_graph().cypher.execute(query) return result
def create(self, node): query = "MATCH (n) where id(n) = " + self.linked_node["id"] + " MERGE (s:" + node + "Synonyme{Name: \"" + self.synonyme["Name"] + "\"})-[:REL_HAS]->(n) RETURN s" result = get_graph().cypher.execute(query) self.id = result[0].s._id
def get_linked_node(self): query = "MATCH (s)-[]-(n) WHERE id(s) = " + str(self.id) + " RETURN n" result = get_graph().cypher.execute(query) return result[0].n
def delete(self): query = "MATCH (n)-[r]-() WHERE id(n) = " + str(self.id) + " DELETE n,r" get_graph().cypher.execute(query)
def create_name_without_accent(self): name_without_accent = unicodedata.normalize('NFKD', self.synonyme["Name"]).encode('ASCII', 'ignore') query = "MATCH (s) WHERE id(s) = " + str(self.id) + " SET s.NameWithoutAccent = \"" + name_without_accent + "\" RETURN s" get_graph().cypher.execute(query)