path: root/python/products-src-mysql-v2.py

## 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