path: root/python/products-dict-v5.py

## 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
from urllib.request import urlopen



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


##### DEPRICATED
##### read data straight from the database
##
## # 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())
## 
## 
## # 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)


##### NEW
## -----------------------------------------------------------------------------
## get data from endpoint (url)


url = "http://localhost/api/v1/productsSearch"
json_url = urlopen(url)

received = json.loads(json_url.read())

# print(data)
# sys.exit()



# --- 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_ :
for rec in received['data'] :

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


    ## process only products with images
    # ---
    if rec['img'] == 1 :

        description = rec['txt']
        pid = ['id']
        fq = 1    


        # 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("./keywords-v5.json", "w", encoding="utf-8") as outfile :
    data = json.dump(keywords_, outfile, sort_keys=False, indent=3, ensure_ascii=False)

with open("./minilist-v5.json", "w", encoding="utf-8") as outfile :
    data = json.dump(minilist_, outfile, sort_keys=False, indent=3, ensure_ascii=False)

with open("./products-v5.json", "w", encoding="utf-8") as outfile :
    data = json.dump(products_, outfile, sort_keys=False, indent=3, ensure_ascii=False)