Code tree reorganized; older implemenatations act as a start point

1 files changed, 623 insertions, 0 deletions

diff --git a/python/products-src-mysql-v2.py b/python/products-src-mysql-v2.py
new file mode 100644
index 0000000..034da0d
--- /dev/null
+++ b/python/products-src-mysql-v2.py
@@ -0,0 +1,623 @@
+## 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)
+
+
+## kbLatinString ...
+# -> translate/re-wrrite string using latin-characters
+# -> function is used used anywhere
+def kbLatinString( txt ) :
+    maTable = txt.maketrans(
+        "ςερτυθιοπασδφγηξκλζχψωβνμΕΡΤΥΘΙΟΠΑΣΔΦΓΗΞΚΛΖΧΨΩΒΝΜάέήίόύώϊΐϋΆΈΉΊΌΎΏΪΫQWERTYUIOPASDFGHJKLZXCVBNM",
+        "sertyuiopasdfghjklzxcvbnmertyuiopasdfghjklzxcvbnmaehioyviiyaehioyviyqwertyuiopasdfghjklzxcvbnm"
+    )
+    txt = txt.replace('\'', '')
+    return txt.translate(maTable).lower()
+
+
+# letters-only translation to key-pressed characters (latin)
+# this minimized version of kbLatinString is used in markLink()
+# ---
+def kbLatinLetter( txt ) :
+    maTable = txt.maketrans(
+        "ςερτυθιοπασδφγηξκλζχψωβνμΕΡΤΥΘΙΟΠΑΣΔΦΓΗΞΚΛΖΧΨΩΒΝΜάέήίόύώϊΐϋΆΈΉΊΌΎΏΪΫ",
+        "sertyuiopasdfghjklzxcvbnmertyuiopasdfghjklzxcvbnmaehioyviiyaehioyviy"
+    )
+    return txt.translate(maTable).lower()
+
+
+# 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 (.)
+# ---
+significantExceptios = '7UP 3ΑΛΦΑ 17 3Π 7DAYS K2R'.split(' ')
+def isSignificant(x) :
+    # fisrts exclude some notable exceptions (mostly brands)
+    if x in significantExceptios :
+        return True
+
+    return not bool( re.match("\S*\d+\S*", x) )
+
+
+# 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
+
+
+## replaces
+# do all replaces in place
+# --- (preproccessing)
+replaces = []
+replaceSource = [
+    '3 ΑΛΦΑ ;3ΑΛΦΑ ',
+    'HEAD & SHOULDERS ;HEAD&SHOULDERS ',
+    'W.K Kellogg ; ',
+    'ΦΙΛΕΤ ;Φιλέτο ',
+    'ΕΝΕΛΛΑΔ ;Εν-Ελλάδι ',
+    'ΓΑΛΟΠΟΥΛ ;Γαλοπούλα ',
+    '7 DAYS ;7DAYS ',
+    'ΜΠΑΡΜΠΑ ΣΤΑΘΗ ;ΜΠΑΡΜΠΑ-ΣΤΑΘΗΣ '
+]
+for it in replaceSource :
+    st = it.split(';')
+    replaces.append({ 'src': st[0], 'trg': st[1] })
+
+def do_replaces(w) :
+    for it in replaces :
+        w = w.replace(it['src'], it['trg'])
+    return w
+
+
+## main preproccess function for product descriptions
+# ---
+def preprocessEdit(w) :
+    w = do_replaces(w)
+    # ... do other things if needed
+    # then ...
+    return w
+    
+
+## 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',
+###     'ΟΛΙΚΗΣ 'ΑΛΕΣΗΣ; Ολικής Άλεσης',
+###     'Χωρίς προσθήκη ζάχαρης; Χωρίς-Ζάχαρη',
+###     'ΚΑΠΝ.CRETA-FARMS; ΚΑΠΝΙΣΤΗ CRETA-FARMS'
+### ]
+
+
+# --- list of linked-words
+linkedWords = [
+    'Χωρίς-Γλουτένη',
+    'Χωρίς-Ζάχαρη',
+    'Χωρίς-Αλάτι',
+    'Χωρίς-Λακτόζη',
+    'Χωρίς-Συντηρητικά',
+    'Χωρίς-Αλκοόλ',
+    'Χωρίς-Kαφεϊνη',
+    'Χωρίς-Γλυκάνισο',
+    'Χωρίς-Ανθρακικό',
+    'Υψηλής-Παστερίωσης',
+    'Ολικής-Άλεσης',
+    'Ολικής-Aλέσεως',
+    'Χαρτί-Υγείας',
+    'ρολό-υγείας',
+    'χαρτί-τουαλέτας',
+    'Χαρτί-Κουζίνας',
+    'Μπάρες-Δημητριακών',
+    'Μπαρμπα-Στάθης',
+    'COCA-COLA'
+    'Aς-Μαγειρέψουμε',
+    'ΚΡΙΣ-ΚΡΙΣ',
+    'ΚΡΙ-ΚΡΙ',
+    'ΕΛ-ΓΚΡΕΚΟ',
+    'FREE-STEP',
+    'EL-SABOR',
+    'LE-PETIT-MARSEILLAIS',
+    'DOUWE-EGBERTS',
+    'ΕΝ-ΕΛΛΑΔΙ',
+    'SPIN-SPAN',
+    'CRETA-FARMS',
+    'CRETA-FARM',
+    'NES-CAFE',
+    'Ολες-τις-Χρήσεις',
+    'Το-Μάννα',
+    'Χωρίς-προσθήκη-ζάχαρης'
+]
+
+
+
+# mark linked words (connect them with a dash)
+# return new text after "all-links" are marked
+# ---
+def markLinkedWords(text) :
+    for lw in linkedWords :
+        text = markLink( lw, text )
+    return text
+
+
+## handle words that can never be the first word on a search
+noRootKeywords = []
+noRoot = [
+    'χωρίς',
+    'εισαγωγής',
+    'δώρο',
+    'γεύση',
+    'γεύσεις',
+    'φέτες',
+    'Χωρίς-Γλουτένη',
+    'Χωρίς-Ζάχαρη',
+    'Χωρίς-Αλάτι',
+    'Χωρίς-Λακτόζη',
+    'Χωρίς-Συντηρητικά',
+    'Χωρίς-Αλκοόλ',
+    'Χωρίς-Kαφεϊνη',
+    'Χωρίς-Γλυκάνισο',
+    'Χωρίς-Ανθρακικό',
+    'Υψηλής-Παστερίωσης',
+    'Ολες-τις-Χρήσεις',
+    'Ολικής-Άλεσης',
+    'Ολικής-Aλέσεως',
+    'Ολικής',
+    'Γαϊδούρας',
+    'Γαϊδάρου',
+    'Ρούχων',
+    'Πιάτων',
+    'πλύσεις',
+    'Πλυντηρίου',
+    'Φύλλων',
+    'Γάλακτος',
+    'Χρήσης',
+    'Τύπου',
+    'Ολλανδίας',
+    'Απορριμμάτων',
+    'Medium',
+    'Μαλλιά',
+    'Μαλλιών',
+    'Γενικής',
+    'Plus',
+    'Classic',
+    'Έκπληξη',
+    'Μάνης',
+    'Ελάτου',
+    'Άγριων',
+    'Βοτάνων',
+    'Λακωνίας',
+    'ΠΑΡΑΓΓΕΛΙΩΝ'
+]
+for w in noRoot :
+    noRootKeywords.append(kbLatinString(w))
+
+
+## PREPARE (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 PETIT-MARSEILLAIS',
+    'Γαϊδούρας Γαϊδάρου',
+    'ΚΑΛΟΓΕΡΑΚΗΣ ΚΑΛΟΓΕΡΑΚΗ',
+    'ΚΑΪΔΑΝΤΖΗΣ ΚΑΪΔΑΝΤΖΗ',
+    'ΥΦΑΝΤΗΣ ΥΦΑΝΤΗ',
+    'ΣΥΝΑΓΡΙΔΑ ΣΥΝΑΓΡΙΔΕΣ',
+    'Ντομάτα Ντομάτας',
+    'Ελαφρύ Ελαφρά Light',
+    'Εγχώρια Εγχώριες Ελληνικό Ελληνική Ελληνικά',
+    'τριμμένη τριμμένο',
+    'Τόνος Τόνου',
+    'Κριθαρένια κρίθινα',
+    'Χωρίς-Kαφεϊνη Decaffeine',
+    'Το-Μάννα Μάννα',
+    'Κράνμπερι Κράνμπερις',
+    'Κρήτης Κρητικό',
+    'Πέννες Πένες',
+    'Μακαρόνια Σπαγγέτι Σπαγγετίνι Σπαγγετόνι',
+    'Καρτέλλα Καρτέλα Καρτέλλες'
+]
+
+
+## Read data
+# //////////////////////////////////////////////////////////////////////////////
+
+
+## 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
+    'bpcs'  : 7,    # bpcs_code
+    'img'   : 8     # product's image file-name
+}
+
+# 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,
+        pl.bpcs_code,
+        pd.image_path
+    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_read = 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
+    ## depricate: pid  = domeInt( row[_COL['pid']] )      # product-id
+    fq   = 0 if None else domeInt( row[_COL['freq']] )     # frequency
+    barcode  = 0 if None else domeInt( row[_COL['barcd']] )
+    sklcode  = 0 if None else domeInt( row[_COL['sklcd']] )
+    eyscode  = 0 if None else domeInt( row[_COL['eyscd']] )
+    bpcs  = 0 if None else domeInt( row[_COL['bpcs']] )
+    img   = row[_COL['img']]
+
+    pid = eyscode   # actual product id (pid) it the eys_code
+
+    # edit descriptions
+    description = preprocessEdit(description)
+
+    # setup product
+    # ---
+    products_.append({
+        'w'  : description,
+        'id' : eyscode,
+        'f'  : fq,
+        'bc' : barcode,
+        'sc' : sklcode,
+        'bp' : bpcs,
+        'i'  : img
+    })
+
+
+    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)
+
+        # update root word frequency (if w CAN be a root word)
+        if kbLatinLetter(w) not in noRootKeywords :
+            rootKey( wl, fq, keywords_ )
+
+            ## if no other keyword in description add a dummy one
+            # so preserve reference to the final product
+            if len(keys) == 1 :
+                connectKeys( wl, ['*'], pid, fq, keywords_ )
+
+        for w2 in keys :
+            if w2 != w :
+                w2syns = synonymKeys(w2)
+                connectKeys( wl, w2syns, pid, fq, keywords_ )
+
+
+
+# --- remove 'kb' keywords
+for ki in keywords_ :
+    del ki['kb']            # kb not needed (kb_translation in js is really fast)
+
+
+## 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)
+
+
+
+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, ensure_ascii=False)
+
+with open("results/products.json", "w", encoding="utf-8") as outfile :
+    data = json.dump(products_, outfile, sort_keys=False, ensure_ascii=False, separators=(',', ':'))
+
+
+t_end = datetime.datetime.now()
+
+print(records_counter, 'products proccessed')
+print('execution time:', (t_end - t0_))
+print('read.n.parse sources:', (t_read - t0_))
+print('proccessing products:', (t_main - t_read))
+
+
+
+## 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
+
+
+# ΠΑΝΤΕΛΟΝΙ ΑΝΔ ΦΟΥΤ ΑΝ ΣΤΑ ΠΡΑΣ XXXL
+# MAYBELLINECONCEALERAGEREWBLMEDIUM
\ No newline at end of file