Code tree reorganized; older implemenatations act as a start point

1 files changed, 535 insertions, 0 deletions

diff --git a/python/products-src-mysql.py b/python/products-src-mysql.py
new file mode 100644
index 0000000..07bc7d3
--- /dev/null
+++ b/python/products-src-mysql.py
@@ -0,0 +1,535 @@
+## 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 datetime
+
+t0_ = datetime.datetime.now()
+
+## 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.replace('  ', ' ')     # one lase (just in case)
+
+
+# isSignificant
+# decides if the term is significant to be indexed;
+# a term is significant if does not contain digit-chars [0-9], comma (,) or period (.)
+# ---
+def isSignificant(x) :
+    # fisrts exclude some notable exceptions (mostly brands)
+    if x in ['7UP', '3ΑΛΦΑ', '17'] :
+        return True
+
+    return not bool( re.match("\S*\d+\S*", x) )
+
+
+def kbLatinString( txt ) :
+    maTable = txt.maketrans(
+        "ςερτυθιοπασδφγηξκλζχψωβνμΕΡΤΥΘΙΟΠΑΣΔΦΓΗΞΚΛΖΧΨΩΒΝΜάέήίόύώϊϋΆΈΉΊΌΎΏΪΫQWERTYUIOPASDFGHJKLZXCVBNM",
+        "sertyuiopasdfghjklzxcvbnmertyuiopasdfghjklzxcvbnmaehioyviyaehioyviyqwertyuiopasdfghjklzxcvbnm"
+    )
+    txt = txt.replace('\'', '')
+    return txt.translate(maTable).lower()
+
+
+# check if word: w
+# … has synonyms; return list of synonyms
+# ---
+def synonymKeys(w) :
+    w_kb = kbLatinString(w)
+    syns = [ w ]
+    found = False
+    # check if has synonyms
+    for group in synonyms :
+        possibles = group.split()  
+        for wi in possibles :
+            if kbLatinString(wi) == w_kb :
+                syns = possibles
+                found = True
+                break
+        if found :
+            break
+    return syns
+
+
+# set root-keyword: wl (if not exist)
+# update frequency: f
+# into list: l
+# NOTE:
+# * wl is a list of synonym-words
+# ** comparison is based on the *keyboard* format
+## ---
+def rootKey ( wl, f, l ) :
+    keyExists = False
+    w_kb = kbLatinString(wl[0])     # cache kb format
+
+    # check if exists in root keys already
+    # NOTE: you only need to check the 1st word of synonyms-list
+    for it in l :
+        if it['kb'] == w_kb :
+            keyExists = True
+            it['f'] += f
+            break
+    
+    # if not exists, append keyword
+    if keyExists == False :
+        l.append({
+            'w' : wl,
+            'kb' : w_kb,
+            'f' : f,
+            'c' : []
+        })
+
+
+# connect keys: a , b (each one is a list of synonmyms)
+# of product with id: i
+# with frequency: f
+# into list: l
+## ---
+def connectKeys( a, b, i, f, l ) :
+    kbA = kbLatinString(a[0])
+    kbB = kbLatinString(b[0])
+
+    if kbA == kbB :
+        return False        ## exclude just-in-case
+    
+    for it in l :
+        if it['kb'] == kbA :
+            # found: a;
+
+            # let's update connection to: b
+            bExists = False
+            for jt in it['c'] :
+                if jt['kb'] == kbB :
+                    bExists = True
+                    # update the connection's data
+                    jt['f'] += f
+                    jt['p'].append(i)
+                    break
+            
+            if bExists == False :
+                # create connection with word: b
+                it['c'].append({
+                    'w': b,
+                    'kb': kbB,
+                    'f': f,
+                    'p': [ i ]
+                })
+            break
+
+
+## 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
+
+
+
+# letters-only translation to key-pressed characters (latin)
+# ---
+def kbLatinLetter( txt ) :
+    maTable = txt.maketrans(
+        "ςερτυθιοπασδφγηξκλζχψωβνμΕΡΤΥΘΙΟΠΑΣΔΦΓΗΞΚΛΖΧΨΩΒΝΜάέήίόύώϊϋΆΈΉΊΌΎΏΪΫ",
+        "sertyuiopasdfghjklzxcvbnmertyuiopasdfghjklzxcvbnmaehioyviyaehioyviy"
+    )
+    return txt.translate(maTable).lower()
+
+
+## mark a link to a text
+# conecting them with a dash/minus character
+# ---
+def markLink(lws, text) :
+    text_kb = kbLatinLetter(text.replace(' ', '-'))
+    lws_kb  = kbLatinLetter(lws)
+    try:
+        index_l = text_kb.lower().index(lws_kb.lower())
+    except:
+        return text
+    else:
+        return text[:index_l] + lws + text[index_l + len(lws):]
+
+
+### # --- list of normalized word combinations
+### replaceWords = [
+###     'HEAD & SHOULDERS; HEAD&SOULDERS',
+###     'ΟΛΙΚΗΣ 'ΑΛΕΣΗΣ; Ολικής Άλεσης',
+###     'Χωρίς προσθήκη ζάχαρης; Χωρίς-Ζάχαρη'
+### ]
+
+
+# --- list of linked-words
+linkedWords = [
+    'Χωρίς-Γλουτένη',
+    'Χωρίς-Ζάχαρη',
+    'Χωρίς-Αλάτι',
+    'Χωρίς-Λακτόζη',
+    'Χωρίς-Συντηρητικά',
+    'Χωρίς-Αλκοόλ',
+    'Υψηλής-Παστερίωσης',
+    'Ολικής-Άλεσης',
+    'Ολικής-Aλέσεως',
+    'Χαρτί-Υγείας',
+    'ρολό-υγείας',
+    'χαρτί-τουαλέτας',
+    'Χαρτί-Κουζίνας',
+    'Μπάρες-Δημητριακών',
+    'Μπαρμα-Στάθης',
+    'Coca-Cola'
+    'Aς-Μαγειρέψουμε',
+    'ΚΡΙΣ-ΚΡΙΣ',
+    'ΚΡΙ-ΚΡΙ',
+    'ΕΛ-ΓΚΡΕΚΟ',
+    'FREE-STEP',
+    'EL-SABOR',
+    'LE-PETIT-MARSEILLAIS',
+    'DOUWE-EGBERTS',
+    'ΕΝ-ΕΛΛΑΔΙ',
+    'SPIN-SPAN',
+    'CRETA-FARMS',
+    'CRETA-FARM',
+    'NES-CAFE'
+]
+
+
+# text after "all-links" marked
+# ---
+def markLinkedWords(text) :
+    for lw in linkedWords :
+        text = markLink( lw, text )
+    
+    return text
+
+
+
+## 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 (or build) exception objects
+# //////////////////////////////////////////////////////////////////////////////
+
+
+# --- list of words to exclude from keywords
+# NOTE:
+# APPLIED in 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 synonyms
+# in fact
+synonyms = [
+    'μπίρα μπύρα μπίρες μπύρες',
+    'αυγά αβγά αυγό αβγό',
+    'σίκαλης σικάλεως',
+    'ξηρά ξερά',
+    'ρολό ρολλό',
+    'coca-cola cocacola coke',
+    'χαρτί-υγείας ρολό-υγείας χαρτί-τουαλέτας',
+    'χαρτί-κουζίνας ρολό-κουζίνας',
+    'οινος κρασι',
+    'ΚΑΤΣΕΛΗΣ ΚΑΤΣΕΛΗ',
+    'DR-OETKER OETKER',
+    'DR.BECKMANN BECKMANN',
+    'NES-CAFE NESCAFE',
+    'Ολικής-Άλεσης Ολικής-Aλέσεως Ολικής',
+    'τσίπουρο ρακή',
+    'Βρώμη Βρώμης',
+    'Φράουλα Φράουλες Φράουλας',
+    'Μαλλιά Μαλλιών',
+    'Κέικ, Cake',
+    'CRETA-FARMS CRETA-FARM',
+    'MARSEILLAIS LE-PETIT-MARSEILLAIS',
+    'Γαϊδούρας Γαϊδάρου',
+    'ΚΑΛΟΓΕΡΑΚΗΣ ΚΑΛΟΓΕΡΑΚΗ',
+    'ΚΑΪΔΑΝΤΖΗΣ ΚΑΪΔΑΝΤΖΗ',
+    'ΥΦΑΝΤΗΣ ΥΦΑΝΤΗ',
+    'ΣΥΝΑΓΡΙΔΑ ΣΥΝΑΓΡΙΔΕΣ'
+]
+
+
+## Read data
+# //////////////////////////////////////////////////////////////////////////////
+
+# enter your 
+HOST = "127.0.0.1"              # server IP address/domain name
+DATABASE = "dev_pythia_db"      # database name
+USER = "pythia_db_user_dev"
+PASSWORD = "VnEP0eysjiXDHcfM"
+
+# 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 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
+    LEFT JOIN product_brands pb ON pl.brand_id = pb.id
+    WHERE pl.active = 1 AND pl.sap_code IS NOT NULL AND pl.product_category_sap_4 NOT LIKE '72%'
+    GROUP BY pl.product_id
+    ORDER BY FREQuency DESC
+'''
+results_ = get_data_from_db(crs, query)
+
+t_db = datetime.datetime.now()
+
+
+# --- Lists to fill
+keywords_ = []     # all data; main exported object
+minilist_ = []
+products_ = []
+
+## keywords format:
+## [
+##    {
+##       w : [ word, word-synonym, ... ],
+##      kb : = kbLatinString(word)
+##       f : 150,
+##       c : [
+##             { w: ['fish', 'fishes'],  f: 150,  p: [122, 254, 907] },
+##             { w: ['juice'],           f: 50,   p: [254, 351] }
+##       ]
+##    },
+##    ...
+## ]
+##
+## --- index:
+## w : words / list of synonyms (str/utf-8)
+# kb : ascii-latin-keypoard format of first item of "w" list
+## f : frequency (int)
+## c : combos / connections (list of objects)
+## p : list of product-ids found in specific words-combination (list of int)
+
+
+records_counter = 0
+## LOOP through the rows to pre-proccess all products
+## ---
+for row in results_ :
+    records_counter += 1
+
+    description = row[_COL['descr']]        # product description
+    pid  = domeInt( row[_COL['pid']] )      # product-id
+    fq   = domeInt( row[_COL['freq']] )     # frequency
+
+    # setup product
+    # ---
+    products_.append({
+        'w'  : description,
+        'id' : pid,
+        'f'  : fq
+    })
+
+    # TODO:
+    # identify brands
+    # then ...
+
+    description = cleanText(description)        # clean description string before spliting
+
+    description = markLinkedWords(description)  # ...
+
+    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)
+    print(pid, keys)
+
+    # append words (and their combos) to the list
+    for w in keys :
+        wl = synonymKeys(w)
+        rootKey( wl, fq, keywords_ )
+        for w2 in keys :
+            if w2 != w :
+                w2syns = synonymKeys(w2)
+                connectKeys( wl, w2syns, 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)
+
+
+
+
+## alternative formats to test ------------------------------------------- START
+
+with open("results/keywords-full.json", "w", encoding="utf-8") as outfile :
+    data = json.dump(keywords_, outfile, sort_keys=False, indent=2, ensure_ascii=False)
+
+keyhashes_ = []
+hashedkeys_ = []
+
+# --- remove 'kb' keywords
+for ki in keywords_ :
+    h = ki['kb']
+    keyhashes_.append({ h : ki['w'] })
+    conns = []
+    del ki['kb']
+    for ci in ki['c'] :
+        conns.append({
+            'h' : ci['kb'],
+            'f' : ci['f'],
+            'p' : ci['p']
+        })
+        del ci['kb']
+    hashedkeys_.append({
+        'h' : h,
+        'f' : ci['f'],
+        'c' : conns
+    })
+
+with open("results/hashes.json", "w", encoding="utf-8") as outfile :
+    data = json.dump(keyhashes_, outfile, sort_keys=False, indent=2, ensure_ascii=False)
+
+with open("results/hashedkeys.json", "w", encoding="utf-8") as outfile :
+    data = json.dump(hashedkeys_, outfile, sort_keys=False, indent=2, ensure_ascii=False)
+
+# --- create mini list based on the sorted keywords_
+### for it in keywords_ :
+###     minilist_.append({
+###         'w' : it['w'],
+###         'f' : it['f'],
+###         'kb': it['kb']
+###     })
+###
+### with open("results/minilist.json", "w", encoding="utf-8") as outfile :
+###     data = json.dump(minilist_, outfile, sort_keys=False, indent=3, ensure_ascii=False)
+
+
+## alternative formats to test --------------------------------------------- END
+
+
+
+
+t_main = datetime.datetime.now()
+print('Proccessing ended; saving results in json format ...')
+
+
+## OUTPUT final data to a json-format file
+# //////////////////////////////////////////////////////////////////////////////
+
+with open("results/keywords.json", "w", encoding="utf-8") as outfile :
+    data = json.dump(keywords_, outfile, sort_keys=False, indent=2, ensure_ascii=False)
+
+with open("results/products.json", "w", encoding="utf-8") as outfile :
+    data = json.dump(products_, outfile, sort_keys=False, indent=2, ensure_ascii=False)
+
+
+t_end = datetime.datetime.now()
+
+
+print(records_counter, 'products proccessed')
+print('execution time:', (t_end - t0_))
+print('from which ... database:', (t_db - t0_))
+print('... records proccessing:', (t_main - t_db))
+
+
+
+## NOTE:
+## prepare cloud-sql-proxy
+## ---
+## * install cloud-sql-proxy
+##   : sudo wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O /usr/local/cloud_sql_proxy
+##   : sudo chmod +x /usr/local/cloud_sql_proxy
+##
+## * prepare/export/publish/copy credentials ...
+##   : sudo cp /some/path/to/cloudsqlproxy.json /usr/local
+##
+## * finaly run the database instance
+##   : /usr/local/cloud_sql_proxy -instances=pythia-251711:europe-west4:pythia-db-eu=tcp:3306 -credential_file=cloudsqlproxy.json
+##
+## after installation only the last command needs to run before connecting to the cloud-sql