Code tree reorganized; older implemenatations act as a start point

1 files changed, 428 insertions, 0 deletions

diff --git a/python/products-dict-v5.py b/python/products-dict-v5.py
new file mode 100644
index 0000000..329a212
--- /dev/null
+++ b/python/products-dict-v5.py
@@ -0,0 +1,428 @@
+## PRODUCTS DICTIONARY
+# for eShop
+# //////////////////////////////////////////////////////////////////////////////
+
+## LIBRARIES
+# //////////////////////////////////////////////////////////////////////////////
+
+# import pandas as pd               # pandas for excel reading
+import mysql.connector as mysql     # mysql connector
+import re       # regex
+import json     # json
+import os.path  # ...
+import sys
+
+
+
+## LOCAL FUNCTIONS
+# //////////////////////////////////////////////////////////////////////////////
+
+
+# do-me-INTeger
+# ---
+def domeInt(x) :
+    if isinstance(x, str) :     # if string
+        return int(x.strip())
+    if isinstance(x, float) :   # if float
+        return round(x)
+    return x      # otherwise is int already
+
+
+# do-me-Float
+# ---
+def domeFloat(x) :
+    if isinstance(x, str) :
+        return float(x.strip())
+    else :
+        return x + 0.00     # make sure that result is float
+
+
+## Clean Text ...
+# -> removes some general/neutral words and symbols
+# -> ignores some in-line characters 
+# -> also strips spare spaces
+# function is applied onto the full title/description
+# ---
+def cleanText(x) :
+    ignoreList = '" ( ) [ ]'.split(' ')
+
+    for r in ignoreList :
+        x = x.replace(r, ' ')
+       
+    x = x.replace('    ', ' ')      # remove spare spaces
+    x = x.replace('   ', ' ')
+    x = x.replace('  ', ' ')
+
+    return x
+
+
+# isSignificant
+# decides if the term is significant to be indexed;
+# a term is significant if does not contain digit-chars
+# ---
+def isSignificant(x) :
+    # fisrts exclude some notable exceptions (mostly brands)
+    if x in ['7UP', '3ΑΛΦΑ'] :
+        return True
+
+    return not bool(re.match("\S*\d+\S*", x))
+
+
+
+def kbLatinString( txt ) :
+    maTable = txt.maketrans(
+        "ςερτυθιοπασδφγηξκλζχψωβνμΕΡΤΥΘΙΟΠΑΣΔΦΓΗΞΚΛΖΧΨΩΒΝΜάέήίόύώϊϋΆΈΉΊΌΎΏΪΫQWERTYUIOPASDFGHJKLZXCVBNM",
+        "sertyuiopasdfghjklzxcvbnmertyuiopasdfghjklzxcvbnmaehioyviyaehioyviyqwertyuiopasdfghjklzxcvbnm"
+    )
+    
+    txt = txt.replace('\'', '')
+    txt = txt.replace('-', '')
+    txt = txt.replace(' ', '')
+
+    return txt.translate(maTable).lower()
+
+
+# set root-keyQ: w (if not exist)
+# update frequency: f
+# into list: l
+# NOTE:     in this version,
+# comparison is based on the *keyboard* format
+## ---
+def rootKey ( w, f, l ) :
+    keyExists = False
+    kbW = kbLatinString(w)
+
+    for it in l :
+        if it['kb'] == kbW :
+            keyExists = True
+            it['f'] += f
+            if w not in it['alt'] :
+                it['alt'].append(w)
+    
+    if keyExists == False :
+        l.append({
+            'w' : w,
+            'f' : f,
+            'alt' : [ w ],
+            'kb' : kbW,
+            'c' : []
+        })
+
+
+
+# connect keys: a , b
+# of product with id: i
+# with frequency: f
+# into list: l
+## ---
+def connectKeys( a, b, i, f, l ) :
+    kbA = kbLatinString(a)
+    kbB = kbLatinString(b)
+
+    if kbA == kbB :
+        return False    ## exclude just-in-case
+    
+    for it in l :
+        if it['kb'] == kbA :
+            
+            # found: a;
+            # lets update the connection to: b
+            bExists = False
+
+            for jt in it['c'] :
+                if jt['kb'] == kbLatinString(b) :
+                    bExists = True
+                    # update the connection's data
+                    jt['f'] += f
+                    jt['p'].append(i)
+            
+            if bExists == False :
+                # create connection with word: b
+                it['c'].append({
+                    'w': b,
+                    'kb': kbLatinString(b),
+                    'f': f,
+                    'p': [ i ]
+                })
+
+
+## let mysql to return valid strings
+## (otherwise it returns strings with missed characters)
+# credit: https://stackoverflow.com/a/68784172
+# analytical credit: https://stackoverflow.com/questions/27566078/
+def get_data_from_db(cursor, sql):
+    output = []
+    cursor.execute(sql)
+    row = cursor.fetchone()
+    while row is not None:
+        row_to_return = row.decode('utf-8') if isinstance(row, bytearray) else row
+        output.append(row_to_return)
+        row = cursor.fetchone()
+    
+    return output
+
+
+
+## LOCAL CONSTANTS
+# //////////////////////////////////////////////////////////////////////////////
+
+_COL = {
+    # -- main info
+    'freq'  : 0,    # frequency (based on recent orders)
+    'pid'   : 1,    # product id
+    'brand' : 2,    # brand
+    'barcd' : 3,    # barcode
+    'sklcd' : 4,
+    'eyscd' : 5,
+    'descr' : 6,    # product description
+    'sap2'  : 7     # SAP category level-2 id
+}
+
+
+
+## PREPADE (build) exception objects
+# //////////////////////////////////////////////////////////////////////////////
+
+
+# --- list of words to exclude from keywords
+# applied in a per-word base (after spliting description to words)
+removeList = []
+removeOriginals = 'μας με σε για του της των από στο στον από & r s ft l τ e g h k m n o p s x'.split(' ')
+for it in removeOriginals :
+    removeList.append(kbLatinString(it))
+
+"""
+# --- list of linked-words
+linkedWords = []
+linkedWordOriginals = [
+    'Χωρίς-Γλουτένη',
+    'Χωρίς-Ζάχαρη',
+    'Χωρίς-Αλάτι',
+    'Χωρίς-Λακτόζη',
+    'Χωρίς-Συντηρητικά',
+    'Χωρίς-Αλκοόλ',
+    'Υψηλής-Παστερίωσης',
+    'Ολικής-Άλεσης',
+    'Χαρτί-Υγείας',
+    'Χαρτί-Κουζίνας',
+    'Μπάρες-Δημητριακών',
+    'Φυσικός-Χυμός'
+    'Μπαρμα-Στάθης',
+    'Coca-Cola'
+    'Aς-Μαγειρέψουμε',
+    'ΚΡΙΣ-ΚΡΙΣ',
+    'ΚΡΙ-ΚΡΙ',
+    'ΕΛ-ΓΚΡΕΚΟ',
+    'FREE-STEP',
+    'EL-SABOR',
+    'LE-PETIT-MARSEILLAIS',
+    'ΧΡΥΣΑ-ΑΥΓΑ',
+    'DOUWE-EGBERTS',
+    'ΕΝ-ΕΛΛΑΔΙ',
+    'SPIN-SPAN',
+    'CRETA-FARMS'
+]
+for it in linkedWordOriginals :
+    linkedWords.append(kbLatinString(it))
+
+
+
+synonyms = []
+synonymOriginals = [
+    'μπίρα, μπύρα, μπίρες, μπύρες',
+    'αυγά, αβγά, αυγό, αβγό',
+    'σίκαλης, σικάλεως',
+    'ξηρά, ξερά',
+    'ρολό, ρολλό'
+    'coca-cola, cocacola, coke',
+    'χαρτί-υγείας, ρολό-υγείας, χαρτί-τουαλέτας',
+    'χαρτί-κουζίνας, ρολό-κουζίνας',
+    'μπίρα, μπίρες',
+    'οινος, κρασι',
+    'ΚΑΤΣΕΛΗΣ, ΚΑΤΣΕΛΗ',
+    'DR-OETKER, OETKER'
+]
+for it in synonymOriginals :
+    synonyms.append(kbLatinString(it))
+"""
+
+
+
+## Read data
+# //////////////////////////////////////////////////////////////////////////////
+
+## # enter your 
+## HOST = "mariadb"              # server IP address/domain name
+## DATABASE = "emarket_laravel"      # database name
+## USER = "emarket_laravel"
+## PASSWORD = ""
+## 
+## # connect to MySQL server
+## _dbc = mysql.connect(
+##         host=HOST,
+##         database=DATABASE,
+##         user=USER,
+##         password=PASSWORD,
+##         use_unicode=True,
+##         charset='utf8'
+##     )
+## print("Connected to:", _dbc.get_server_info())
+## 
+## # execute SQL to get all data you need
+## crs = _dbc.cursor()
+## query = '''
+##     SELECT p.product_title, p.SKU , p.FriendlyUrl as `seoUrl`,
+##         c.FullFriendlyUrl as `path`
+##     FROM products p
+##     LEFT JOIN category_product cp ON cp.product_id = p.id
+##     LEFT JOIN categories c ON c.id = cp.category_id
+##     WHERE p.isActive = 1 AND p.Published = 1 AND p.IsCurrentlyActive = 1
+##         AND c.isActive AND c.IsCurrentlyActive = 1;
+## '''
+## results_ = get_data_from_db(crs, query)
+
+
+# enter your 
+HOST = "127.0.0.1"              # server IP address/domain name
+DATABASE = "emarket_laravel_dev"      # database name
+USER = "emarket_laravel"
+PASSWORD = "SzvRYl4Y0XU9JXVc"
+DB_SOCKET='/cloudsql/pythia-251711:europe-west4:pythia-db-eu'
+
+# connect to MySQL server
+_dbc = mysql.connect(
+        host=HOST,
+        database=DATABASE,
+        user=USER,
+        password=PASSWORD,
+        use_unicode=True,
+        charset='utf8'
+    )
+print("Connected to:", _dbc.get_server_info())
+
+sys.exit()
+
+# execute SQL to get all data you need
+crs = _dbc.cursor()
+query = '''
+    SELECT count(pl.eys_code) as FREQuency,
+        pl.product_id as product_id,
+        pb.brand_name,
+        pl.barcode, pl.skl_code, pl.eys_code,
+        IF( pd.description IS NOT NULL , pd.description , pl.product_description) AS product_description
+    FROM product_list as pl
+    LEFT JOIN delivery_orders_products AS dop ON dop.product_id = pl.eys_code
+    LEFT JOIN delivery_orders AS do ON dop.order_id = do.id
+    LEFT JOIN product_list AS replacement ON dop.replacement_for = replacement.eys_code
+    LEFT JOIN product_details AS pd ON pl.eys_code = pd.eys_code
+    INNER JOIN product_brands pb ON pl.brand_id = pb.id
+    WHERE pl.active = 1 AND pl.sap_code IS NOT NULL
+    GROUP BY pl.product_id
+    ORDER BY FREQuency DESC
+'''
+results_ = get_data_from_db(crs, query)
+
+
+
+# --- Lists to fill
+keywords_ = []     # all data
+minilist_ = []
+products_ = []
+
+## keywords format:
+##   [
+##     {
+##        w : 'fresh',
+##      alt : [ 'Fresh', 'FRESH', 'fresh' ]
+##       kb :
+##        f : 150,
+##        c : [
+##              {  w : 'milk',   f : 150 , p : [122, 254, 907] },
+##              {  w : 'juice',  f : 50  , p : [254, 351] }
+##        ]
+##     },
+##     {...},
+##     ...
+##   ]
+## --- index:
+## w : word (str/utf-8)
+## f : frequency (int)
+## c : combos / connections (list of objects)
+## p : list of product-ids found in specific words-combination (list of int)
+## alt : list of alternative writtings (list of str/utf-8)
+## kb: *keyboard* writting (str/latin-ascii)
+
+
+## LOOP through the rows to pre-proccess all products
+## ---
+for row in results_ :
+
+    description = row[_COL['product_title']]        # product description
+    pid  = domeInt( row[_COL['SKU']] )      # product-id
+    fq   = domeInt( 1 )     # frequency
+    # url  = '/'+ row[_COL['path']] +'/'+ row[_COL['seoUrl']]     # product url
+
+    # setup product
+    # ---
+    products_.append({
+        't' : description,
+        'i' : pid,
+        'f' : fq
+        # 'u' : url
+    })
+
+    # TODO:
+    # identify brands
+    # then ...
+
+    description = cleanText(description)        # clean description string before spliting
+
+    keys = []                                   # list of product's key(word)s
+    words = description.split()                 # split to words
+    for w in words :
+        if kbLatinString(w) not in removeList:      # if not in removeList            
+            if isSignificant(w) :                   # and if significant
+                keys.append(w)                      # keep it
+
+
+    # print(pid, description, words, keys)
+
+    # append words (and their combos) to the list
+    for w in keys :
+        rootKey( w, fq, keywords_ )
+        for w2 in keys :
+            if w2 != w and isSignificant(w2) :
+                connectKeys( w, w2, pid, fq, keywords_ )
+
+
+## SORT keywords
+# //////////////////////////////////////////////////////////////////////////////
+
+# --- sort childs of each key (per frequency, desc)
+for it in keywords_ :
+    it['c'].sort(key=lambda x: x['f'], reverse=True)
+
+
+# --- sort root keys
+keywords_.sort(key=lambda x: x['f'], reverse=True)
+
+# --- create mini list based on the sorted keywords_
+for it in keywords_ :
+    minilist_.append({
+        'w' : it['w'],
+        'f' : it['f'],
+        'kb': it['kb']
+    })
+
+
+## OUTPUT final data to a json-format file
+# //////////////////////////////////////////////////////////////////////////////
+
+with open("results/keywords-v5.json", "w", encoding="utf-8") as outfile :
+    data = json.dump(keywords_, outfile, sort_keys=False, indent=3, ensure_ascii=False)
+
+with open("results/minilist-v5.json", "w", encoding="utf-8") as outfile :
+    data = json.dump(minilist_, outfile, sort_keys=False, indent=3, ensure_ascii=False)
+
+with open("results/products-v5.json", "w", encoding="utf-8") as outfile :
+    data = json.dump(products_, outfile, sort_keys=False, indent=3, ensure_ascii=False)
\ No newline at end of file