From c50d4c645cd3c04204106c4f9f026e5910afa3d5 Mon Sep 17 00:00:00 2001 From: Geo Halkiadakis Date: Fri, 17 Mar 2023 13:30:46 +0200 Subject: Code tree reorganized; older implemenatations act as a start point --- code-examples.py | 24 -- html/inter.html | 61 +++ html/map.html | 126 +++++++ html/search-v2.html | 336 ----------------- html/search-v3.html | 422 --------------------- html/search.html | 403 ++++++++++++++------ html/search2.html | 642 ++++++++++++++++++++++++++++++++ javascript/node-sql-opt.js | 522 ++++++++++++++++++++++++++ javascript/node-sql.js | 490 ++++++++++++++++++++++++ javascript/nodeJs-keywords-GFunction.js | 540 +++++++++++++++++++++++++++ javascript/nodetest.js | 3 + javascript/test-cdn.js | 24 ++ javascript/test-sort.js | 75 ++++ package.json | 5 + products-dict-v3.py | 270 -------------- products-dictionary.py | 228 ------------ python/check-linked.py | 300 +++++++++++++++ python/code-examples.py | 24 ++ python/products-dict-v3.py | 338 +++++++++++++++++ python/products-dict-v4.py | 383 +++++++++++++++++++ python/products-dict-v5.py | 428 +++++++++++++++++++++ python/products-dictionary.py | 228 ++++++++++++ python/products-src-json.py | 581 +++++++++++++++++++++++++++++ python/products-src-mysql-v2.py | 623 +++++++++++++++++++++++++++++++ python/products-src-mysql.py | 535 ++++++++++++++++++++++++++ python/read-brands.py | 270 ++++++++++++++ python/readmysql.py | 121 ++++++ python/test.py | 104 ++++++ workline.md | 68 ++++ 29 files changed, 6785 insertions(+), 1389 deletions(-) delete mode 100644 code-examples.py create mode 100644 html/inter.html create mode 100644 html/map.html delete mode 100644 html/search-v2.html delete mode 100644 html/search-v3.html create mode 100644 html/search2.html create mode 100644 javascript/node-sql-opt.js create mode 100644 javascript/node-sql.js create mode 100644 javascript/nodeJs-keywords-GFunction.js create mode 100755 javascript/nodetest.js create mode 100644 javascript/test-cdn.js create mode 100644 javascript/test-sort.js create mode 100644 package.json delete mode 100644 products-dict-v3.py delete mode 100644 products-dictionary.py create mode 100644 python/check-linked.py create mode 100644 python/code-examples.py create mode 100644 python/products-dict-v3.py create mode 100644 python/products-dict-v4.py create mode 100644 python/products-dict-v5.py create mode 100644 python/products-dictionary.py create mode 100644 python/products-src-json.py create mode 100644 python/products-src-mysql-v2.py create mode 100644 python/products-src-mysql.py create mode 100644 python/read-brands.py create mode 100644 python/readmysql.py create mode 100644 python/test.py create mode 100644 workline.md diff --git a/code-examples.py b/code-examples.py deleted file mode 100644 index d8d0042..0000000 --- a/code-examples.py +++ /dev/null @@ -1,24 +0,0 @@ -# test -a = 1 -b = 4 - -def addto(x, l) : - l.append({ - "n" : x, - "c": [] - }) - for it in l : - if it["n"] == 4 : - subl = it["c"] - subl.append(x) - it['c'] = subl - - -malist = [] - -malist.append({ "n" : a }) -print(malist) - -addto(b, malist) -addto(b, malist) -print(malist) diff --git a/html/inter.html b/html/inter.html new file mode 100644 index 0000000..fed1bd4 --- /dev/null +++ b/html/inter.html @@ -0,0 +1,61 @@ + + + + + + +

+ + + \ No newline at end of file diff --git a/html/map.html b/html/map.html new file mode 100644 index 0000000..6be59fe --- /dev/null +++ b/html/map.html @@ -0,0 +1,126 @@ + + + + + + +

+ + + + + + \ No newline at end of file diff --git a/html/search-v2.html b/html/search-v2.html deleted file mode 100644 index 100565b..0000000 --- a/html/search-v2.html +++ /dev/null @@ -1,336 +0,0 @@ - - - - - - - - - - - - - - -

- -

- - - - diff --git a/html/search-v3.html b/html/search-v3.html deleted file mode 100644 index 35e29ca..0000000 --- a/html/search-v3.html +++ /dev/null @@ -1,422 +0,0 @@ - - - - - - - - - - - - - - -

- -

- - - - diff --git a/html/search.html b/html/search.html index 2a12941..5c5e64b 100644 --- a/html/search.html +++ b/html/search.html @@ -4,9 +4,9 @@ @@ -42,55 +53,73 @@ body { font-family: 'Cantarell', Helvetica, Arial, sans-serif; } +

+ - + + \ No newline at end of file diff --git a/html/search2.html b/html/search2.html new file mode 100644 index 0000000..bb88958 --- /dev/null +++ b/html/search2.html @@ -0,0 +1,642 @@ + + + + + + + + + + + + + + +

+ +

+ + + + + + + + \ No newline at end of file diff --git a/javascript/node-sql-opt.js b/javascript/node-sql-opt.js new file mode 100644 index 0000000..6462115 --- /dev/null +++ b/javascript/node-sql-opt.js @@ -0,0 +1,522 @@ +// requirements +//////////////////////////////////////////////////////////////////////////////// + +var mysql = require('mysql'); + +const fs = require('fs'); + +const os = require('os'); + + +// preloaded data +//////////////////////////////////////////////////////////////////////////////// + +// any-character to keyboard-latin mapping +// ----------------------------------------------------------------------------- +var ORiGiNal = 'ςερτυθιοπασδφγηξκλζχψωβνμΕΡΤΥΘΙΟΠΑΣΔΦΓΗΞΚΛΖΧΨΩΒΝΜάέήίόύώϊΐϋΆΈΉΊΌΎΏΪΫQWERTYUIOPASDFGHJKLZXCVBNMqwertyuiopasdfghjklzxcvbnm0123456789- '.split(''); +var kbKeyZed = 'sertyuiopasdfghjklzxcvbnmertyuiopasdfghjklzxcvbnmaehioyviiyaehioyviyqwertyuiopasdfghjklzxcvbnmqwertyuiopasdfghjklzxcvbnm0123456789- '.split(''); +const map = new Map(); +for (var i=0; i { + synonyms.push( grp.split(' ') ); + synonym_kbs.push( kb_trans(grp).split(' ') ); +}) + +// significant terms +// ----------------------------------------------------------------------------- +significantExceptios = '7UP 3ΑΛΦΑ 17 3Π 7DAYS K2R'.split(' ') + + +// replaces (correcting descriptions) +// ----------------------------------------------------------------------------- +replaces = []; +replaceSource = [ + '3 ΑΛΦΑ ;3ΑΛΦΑ ', + 'HEAD & SHOULDERS ;HEAD&SHOULDERS ', + 'W.K Kellogg ; ', + 'ΦΙΛΕΤ ;Φιλέτο ', + 'ΕΝΕΛΛΑΔ ;Εν-Ελλάδι ', + 'ΓΑΛΟΠΟΥΛ ;Γαλοπούλα ', + '7 DAYS ;7DAYS ', + 'ΜΠΑΡΜΠΑ ΣΤΑΘΗ ;ΜΠΑΡΜΠΑ-ΣΤΑΘΗΣ ' +] +replaceSource.forEach( it => { + st = it.split(';'); + replaces.push({ src: st[0], trg: st[1] }); +}); + + +// words that shall not be searched first +// ----------------------------------------------------------------------------- +var noRootKeywords = []; +noRoot = [ + 'χωρίς', + 'εισαγωγής', + 'δώρο', + 'γεύση', + 'γεύσεις', + 'φέτες', + 'Χωρίς-Γλουτένη', + 'Χωρίς-Ζάχαρη', + 'Χωρίς-Αλάτι', + 'Χωρίς-Λακτόζη', + 'Χωρίς-Συντηρητικά', + 'Χωρίς-Αλκοόλ', + 'Χωρίς-Kαφεϊνη', + 'Χωρίς-Γλυκάνισο', + 'Χωρίς-Ανθρακικό', + 'Υψηλής-Παστερίωσης', + 'Ολες-τις-Χρήσεις', + 'Ολικής-Άλεσης', + 'Ολικής-Aλέσεως', + 'Ολικής', + 'Γαϊδούρας', + 'Γαϊδάρου', + 'Ρούχων', + 'Πιάτων', + 'πλύσεις', + 'Πλυντηρίου', + 'Φύλλων', + 'Γάλακτος', + 'Χρήσης', + 'Τύπου', + 'Ολλανδίας', + 'Απορριμμάτων', + 'Medium', + 'Μαλλιά', + 'Μαλλιών', + 'Γενικής', + 'Plus', + 'Classic', + 'Έκπληξη', + 'Μάνης', + 'Ελάτου', + 'Άγριων', + 'Βοτάνων', + 'Λακωνίας', + 'ΠΑΡΑΓΓΕΛΙΩΝ' +] +noRoot.forEach( w => { noRootKeywords.push(kb_trans(w)); }); + + +// 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', + 'Ολες-τις-Χρήσεις', + 'Το-Μάννα', + 'Χωρίς-προσθήκη-ζάχαρης' +] + + +// 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(' ') +removeOriginals.forEach( w => { removeList.push( kb_trans(w)); }); + + + + + + +// ready to get main data to preccess +//////////////////////////////////////////////////////////////////////////////// + +// db connection parametres +// ----------------------------------------------------------------------------- +var con = mysql.createConnection({ + host: "127.0.0.1", + user: "pythia_db_user_dev", + password: "VnEP0eysjiXDHcfM", + database: "dev_pythia_db" +}); + + +// keyword links (word-links dictionary; array of objects) +// ----------------------------------------------------------------------------- +var kwlinks_ = []; ////////////////////////////// MAIN OUTPUT OF THE SCRIPT + + +// connect; +// get records to proccess; +// call main proccess function; +// save dictionary; +// end script; +// ----------------------------------------------------------------------------- +con.connect(function(err) { + // connect; + if (err) throw err; + console.log("Connected!"); + + var sql = "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 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"; + + // query sql + con.query(sql, function (err, result) { + + if (err) throw err; + console.log('Records from database received!'); + + do_proccess(result); // proccess + console.log('Keywords proccesed!'); + + save_keywords(); // save local file + upload_file('pythia-files', 'results/keywords.json', 'uploads/orders/keywords.json') + .then( () => { + const used = process.memoryUsage(); // echo memory stats + for (let key in used) { + console.log(`${key} ${Math.round(used[key] / 1024 / 1024 * 100) / 100} MB`); + } + }) + .then( () => { + process.exit(1); // exit + }); + + }); +}); + + +// main proccess +// ----------------------------------------------------------------------------- +function do_proccess(obj) { + + obj.forEach( rec => { + var description = preproccess_text( rec.product_description); + var fq = rec.FREQuency; + var pid = rec.eys_code; + var wl; + var keys = []; + var words = description.split(' '); + + // filter words; keep only significant + words.forEach( w => { + if (removeList.indexOf(kb_trans(w)) == -1) // if not excluded + if (is_significant(w)) // and significant + keys.push(w); // add it to keys + }); + // console.log(pid, description, keys); + + keys.forEach( w => { + // if key CAN be a root word (not a no-Root-keyword) + if (noRootKeywords.indexOf(kb_trans(w)) === -1) { + wl = synonym_keys(w); + + root_key(wl, fq); // update root-node + + // if key is the only in the list of product's keywords + // connect it with a dummy key (to preserve the reference to the product) + if (keys.length == 1) connect_keys(wl, ['*'], pid, fq); + + // connect root word-list (wl) with all the other product's keywords (w2) + keys.forEach( w2 => { + if (w2 != w) { + var w2syns = synonym_keys(w2); + connect_keys( wl, w2syns, pid, fq); + } + }); + } + + }); + + }); // main proccessing finished; + + // post proccess + // ------------------------------------------------------------------------- + + // remove cached keys from final array + kwlinks_.forEach( ro => { + delete ro.kb; + ro.c.forEach( ch => { delete ch.kb; }); + }); + + // sort root and child nodes by frequency descanding + kwlinks_.forEach( it => { + it.c = it.c.sort((a, b) => b.f - a.f ); + }); + kwlinks_ = kwlinks_.sort((a, b) => b.f - a.f ); +} + + +// save proccess +// ----------------------------------------------------------------------------- +function save_keywords() { + let jsonStr = JSON.stringify(kwlinks_); + // console.log(jsonStr); + + fs.writeFileSync("results/keywords.json", jsonStr, 'utf8', (err) => { + if (err) { + console.log("An error occured while writing keywords.json"); + return console.log(err); + } + console.log("JSON file has been saved."); + }); +} + + + +// Google Cloud Functions +//////////////////////////////////////////////////////////////////////////////// + + +const {Storage} = require('@google-cloud/storage'); // import Google Cloud client library +async function upload_file( bucketName, srcFilePath, trgFilePath ) { + // Creates a client + const projectId = 'pythia-251711'; + const keyFilename = '/home/geo/pythia-api/auth/pythia-251711-047e3d5e6608.json'; + const storage = new Storage({projectId, keyFilename}); + + try { + await storage.bucket(bucketName).upload(srcFilePath, { + destination: trgFilePath, + gzip: true, // serve compressed + metadata: { // cache for 8 hours + cacheControl: 'public, max-age=60' // 28800 + } + }); + console.log(`${srcFilePath} uploaded to ${bucketName}`); + } + catch(err) { + console.error('ERROR:', err); + } +} + +// functions for linking words in keywords dictionary +//////////////////////////////////////////////////////////////////////////////// + +// set root-keyword: wl (if not exist) +// update frequency: f +// NOTE: +// * wl is a list of synonym-words +// ** comparison is based on the *keyboard* format +// --- +function root_key ( wl, f ) { + var wkb = kb_trans(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 (i=0; i< kwlinks_.length ; i++) { + if (kwlinks_[i].kb == wkb) { + kwlinks_[i].f += f; + return true; + } + } + // if not exists, append keyword + kwlinks_.push({ + w : wl, + kb : wkb, + f : f, + c : [] + }); + return true; +} + +// connect keys: a , b (each one is a list of synonmyms) +// of product with id: id +// with frequency: f +// --- +function connect_keys( a, b, id, f ) { + var kbA = kb_trans(a[0]); + var kbB = kb_trans(b[0]); + var bExists = false; + + if (kbA == kbB) return false; // exclude just-in-case + + for (i=0; i< kwlinks_.length ; i++) { + if (kwlinks_[i].kb == kbA) { // found: a; + // update connection to: b + bExists = false; + for (j=0 ; j < kwlinks_[i].c.length ; j++) { + if (kwlinks_[i].c[j].kb == kbB) { + bExists = true; + // update the connection's data + kwlinks_[i].c[j].f += f; + kwlinks_[i].c[j].p.push(id) + break; + } + } + // if connection not exist, init a new one + if (bExists == false) { + // create connection with: b + kwlinks_[i].c.push({ + w : b, + kb : kbB, + f : f, + p : [ i ] + }); + } + return true; + } + } +} + +// other supplementary functions +//////////////////////////////////////////////////////////////////////////////// + + +// kb_trans translates string to keyboard-latin keys; +// --- +function kb_trans(str) { + str = str.replaceAll('\'',''); + var out = ''; + for (var i=0 ; i< str.length; i++) out += map.get(str[i]); + return out; +} + +// clean text trims some characters (+.') and internal multiple-spaces +// --- +function clean_text(txt) { + return txt.replaceAll('+',' ').replaceAll('.',' ') + .replaceAll(' ',' ') + .replaceAll(' ',' '); +} + +// check if term is significant +// (if not, the term will be excluded from keywords dicionary) +// --- +function is_significant(str) { + if (str == '') return false; + if (significantExceptios.indexOf(str) !== -1) return true; + return !(/\d/.test(str)); +} + +// check if word: w +// ...has synonyms; return list of synonyms +// --- +function synonym_keys(w) { + w_kb = kb_trans(w); + for ( i=0; i < synonym_kbs.length ; i++) { + if (synonym_kbs[i].indexOf(w_kb) !== -1) + return synonyms[i]; + } + return [ w ]; +} + +// edit common mistakes +// with suggested replaces +function do_replaces(str) { + replaces.forEach( it => { str = str.replaceAll(it.src, it.trg); }); + return str; +} + +// preproccess description +// --- +function preproccess_text(str) { + str = do_replaces(str); + str = clean_text(str); + str = mark_linked_words(str); + return str; +} + + +// mark linked words (connect them with a dash) +// return new text after "all-links" are marked +// --- +function mark_linked_words(txt) { + linkedWords.forEach( lw => { txt = mark_link( lw, txt ); }); + return txt +} + +// mark a link (lws) to a text (source) +// conecting them with a dash/minus character +// --- +function mark_link(lws, source) { + var src_kb = kb_trans(source.replaceAll(' ', '-')); // convert to kb-formats to compare + var lws_kb = kb_trans(lws.replaceAll(' ', '-')); + var _left = src_kb.toLowerCase().indexOf(lws_kb.toLowerCase()); // get left-position of match + if (_left !== -1 ) { // if match, contruct new text injecting the link + return source.slice(0, _left) + lws + source.slice(_left + lws.length); + } + else return source; +} diff --git a/javascript/node-sql.js b/javascript/node-sql.js new file mode 100644 index 0000000..8db56c4 --- /dev/null +++ b/javascript/node-sql.js @@ -0,0 +1,490 @@ +// requirements +//////////////////////////////////////////////////////////////////////////////// + +var mysql = require('mysql'); + +const fs = require('fs'); + + +// preloaded data +//////////////////////////////////////////////////////////////////////////////// + +// any-character to keyboard-latin mapping +// ----------------------------------------------------------------------------- +var ORiGiNal = 'ςερτυθιοπασδφγηξκλζχψωβνμΕΡΤΥΘΙΟΠΑΣΔΦΓΗΞΚΛΖΧΨΩΒΝΜάέήίόύώϊΐϋΆΈΉΊΌΎΏΪΫQWERTYUIOPASDFGHJKLZXCVBNMqwertyuiopasdfghjklzxcvbnm0123456789- '.split(''); +var kbKeyZed = 'sertyuiopasdfghjklzxcvbnmertyuiopasdfghjklzxcvbnmaehioyviiyaehioyviyqwertyuiopasdfghjklzxcvbnmqwertyuiopasdfghjklzxcvbnm0123456789- '.split(''); +const map = new Map(); +for (var i=0; i { + synonyms.push( grp.split(' ') ); + synonym_kbs.push( kb_trans(grp).split(' ') ); +}) + +// significant terms +// ----------------------------------------------------------------------------- +significantExceptios = '7UP 3ΑΛΦΑ 17 3Π 7DAYS K2R'.split(' ') + + +// replaces (correcting descriptions) +// ----------------------------------------------------------------------------- +replaces = []; +replaceSource = [ + '3 ΑΛΦΑ ;3ΑΛΦΑ ', + 'HEAD & SHOULDERS ;HEAD&SHOULDERS ', + 'W.K Kellogg ; ', + 'ΦΙΛΕΤ ;Φιλέτο ', + 'ΕΝΕΛΛΑΔ ;Εν-Ελλάδι ', + 'ΓΑΛΟΠΟΥΛ ;Γαλοπούλα ', + '7 DAYS ;7DAYS ', + 'ΜΠΑΡΜΠΑ ΣΤΑΘΗ ;ΜΠΑΡΜΠΑ-ΣΤΑΘΗΣ ' +] +replaceSource.forEach( it => { + st = it.split(';'); + replaces.push({ src: st[0], trg: st[1] }); +}); + + +// words that shall not be searched first +// ----------------------------------------------------------------------------- +var noRootKeywords = []; +noRoot = [ + 'χωρίς', + 'εισαγωγής', + 'δώρο', + 'γεύση', + 'γεύσεις', + 'φέτες', + 'Χωρίς-Γλουτένη', + 'Χωρίς-Ζάχαρη', + 'Χωρίς-Αλάτι', + 'Χωρίς-Λακτόζη', + 'Χωρίς-Συντηρητικά', + 'Χωρίς-Αλκοόλ', + 'Χωρίς-Kαφεϊνη', + 'Χωρίς-Γλυκάνισο', + 'Χωρίς-Ανθρακικό', + 'Υψηλής-Παστερίωσης', + 'Ολες-τις-Χρήσεις', + 'Ολικής-Άλεσης', + 'Ολικής-Aλέσεως', + 'Ολικής', + 'Γαϊδούρας', + 'Γαϊδάρου', + 'Ρούχων', + 'Πιάτων', + 'πλύσεις', + 'Πλυντηρίου', + 'Φύλλων', + 'Γάλακτος', + 'Χρήσης', + 'Τύπου', + 'Ολλανδίας', + 'Απορριμμάτων', + 'Medium', + 'Μαλλιά', + 'Μαλλιών', + 'Γενικής', + 'Plus', + 'Classic', + 'Έκπληξη', + 'Μάνης', + 'Ελάτου', + 'Άγριων', + 'Βοτάνων', + 'Λακωνίας', + 'ΠΑΡΑΓΓΕΛΙΩΝ' +] +noRoot.forEach( w => { noRootKeywords.push(kb_trans(w)); }); + + +// 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', + 'Ολες-τις-Χρήσεις', + 'Το-Μάννα', + 'Χωρίς-προσθήκη-ζάχαρης' +] + + +// 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(' ') +removeOriginals.forEach( w => { removeList.push( kb_trans(w)); }); + + + + + + +// ready to get main data to preccess +//////////////////////////////////////////////////////////////////////////////// + +// db connection parametres +// ----------------------------------------------------------------------------- +var con = mysql.createConnection({ + host: "127.0.0.1", + user: "pythia_db_user_dev", + password: "VnEP0eysjiXDHcfM", + database: "dev_pythia_db" +}); + + +// keyword links (word-links dictionary; array of objects) +// ----------------------------------------------------------------------------- +var kwlinks_ = []; ////////////////////////////// MAIN OUTPUT OF THE SCRIPT + + +// connect; +// get records to proccess; +// call main proccess function; +// save dictionary; +// end script; +// ----------------------------------------------------------------------------- +con.connect(function(err) { + // connect; + if (err) throw err; + console.log("Connected!"); + + var sql = "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"; + + // query sql + con.query(sql, function (err, result) { + if (err) throw err; + console.log('Records from database received!') + + do_proccess(result); // proccess + console.log('Keywords proccesed!') + + save_keywords(); // save + console.log('Results saved! Exiting.') + + // echo memory stats + const used = process.memoryUsage(); + for (let key in used) { + console.log(`${key} ${Math.round(used[key] / 1024 / 1024 * 100) / 100} MB`); + } + process.exit(1); // exit + }); +}); + + +// main proccess +// ----------------------------------------------------------------------------- +function do_proccess(obj) { + // console.log(JSON.stringify(obj, null, 2)); + + obj.forEach( rec => { + var description = preproccess_text( rec.product_description); + var fq = rec.FREQuency; + var pid = rec.eys_code; + + var keys = []; + var words = description.split(' '); + + // filter words; keep only significant + words.forEach( w => { + if (removeList.indexOf(kb_trans(w)) == -1) // if not excluded + if (is_significant(w)) // and significant + keys.push(w); // add it to keys + }); + // console.log(pid, description, keys); + + keys.forEach( w => { + var wl = synonym_keys(w); + + // if key CAN be a root word (not a no-Root-keyword) update root-node + if (noRootKeywords.indexOf(kb_trans(w) == -1)) root_key(wl, fq); + + // if key is the only in the list of product's keywords + // connect it with a dummy key (to preserve the reference to the product) + if (keys.length == 1) connect_keys(wl, ['*'], pid, fq); + + // connect w with all the other product's keywords + keys.forEach( w2 => { + if (w2 != w) { + var w2syns = synonym_keys(w2); + connect_keys( wl, w2syns, pid, fq); + } + }); + + }); + + }); // main proccessing finished; + + // post proccess + // ------------------------------------------------------------------------- + + // remove cached keys from final array + kwlinks_.forEach( ro => { + delete ro.kb; + ro.c.forEach( ch => { delete ch.kb; }); + }); + + // sort root and child nodes by frequency descanding + kwlinks_.forEach( it => { + it.c = it.c.sort((a, b) => b.f - a.f ); + }); + kwlinks_ = kwlinks_.sort((a, b) => b.f - a.f ); +} + + +// save proccess +// ----------------------------------------------------------------------------- +function save_keywords() { + let jsonStr = JSON.stringify(kwlinks_); + // console.log(jsonStr); + + fs.writeFileSync("results/keywords.json", jsonStr, 'utf8', (err) => { + if (err) { + console.log("An error occured while writing keywords.json"); + return console.log(err); + } + console.log("JSON file has been saved."); + }); +} + + +// functions for linking words in keywords dictionary +//////////////////////////////////////////////////////////////////////////////// + +// set root-keyword: wl (if not exist) +// update frequency: f +// NOTE: +// * wl is a list of synonym-words +// ** comparison is based on the *keyboard* format +// --- +function root_key ( wl, f ) { + var keyExists = false + var wkb = kb_trans(wl[0]) // cache kb format + + // check if exists in root keys already + // NOTE: you only need to check the 1st word of synonyms-list + kwlinks_.forEach( it => { + if (it.kb == wkb) { + keyExists = true; + it.f += f; + } + }); + // if not exists, append keyword + if (keyExists == false) { + kwlinks_.push({ + w : wl, + kb : wkb, + f : f, + c : [] + }) + } +} + +// connect keys: a , b (each one is a list of synonmyms) +// of product with id: i +// with frequency: f +// --- +function connect_keys( a, b, i, f ) { + var kbA = kb_trans(a[0]); + var kbB = kb_trans(b[0]); + var bExists = false; + + if (kbA == kbB) return false; // exclude just-in-case + + kwlinks_.forEach( it => { + if (it.kb == kbA) { // found: a; + // update connection to: b + bExists = false; + it.c.forEach( jt => { + if (jt.kb == kbB) { + bExists = true; + // update the connection's data + jt.f += f + jt.p.push(i) + } + }); + // if connection not exist, init a new one + if (bExists == false) { + // create connection with: b + it.c.push({ + w : b, + kb : kbB, + f : f, + p : [ i ] + }); + } + } + }); +} + +// other supplementary functions +//////////////////////////////////////////////////////////////////////////////// + + +// kb_trans translates string to keyboard-latin keys; +// --- +function kb_trans(str) { + str = str.replace('\'',''); + var out = ''; + for (var i=0 ; i< str.length; i++) out += map.get(str[i]); + return out; +} + +// clean text trims some characters (+.') and internal multiple-spaces +// --- +function clean_text(txt) { + return txt.replace('+',' ').replace('.',' ') + .replace(' ',' ') + .replace(' ',' '); +} + +// check if term is significant +// (if not, the term will be excluded from keywords dicionary) +// --- +function is_significant(str) { + if (significantExceptios.indexOf(str) !== -1) + return true; + return !(/\d/.test(str)); +} + +// check if word: w +// ...has synonyms; return list of synonyms +// --- +function synonym_keys(w) { + w_kb = kb_trans(w); + for(i=0; i < synonym_kbs.length ; i++) { + if (synonym_kbs.indexOf(w_kb) !== -1) + return synonyms[i]; + } + return [ w ]; +} + +// edit common mistakes +// with suggested replaces +function do_replaces(str) { + replaces.forEach( it => { str = str.replace(it.src, it.trg); }); + return str; +} + +// preproccess description +// --- +function preproccess_text(str) { + str = do_replaces(str); + str = clean_text(str); + str = mark_linked_words(str); + return str; +} + + +// mark linked words (connect them with a dash) +// return new text after "all-links" are marked +// --- +function mark_linked_words(txt) { + linkedWords.forEach( lw => { txt = mark_link( lw, txt ); }); + return txt +} + +// mark a link (lws) to a text (source) +// conecting them with a dash/minus character +// --- +function mark_link(lws, source) { + var src_kb = kb_trans(source.replace(' ', '-')); + var lws_kb = kb_trans(lws.replace(' ', '-')); + var _left = src_kb.toLowerCase().indexOf(lws_kb.toLowerCase()); + if (_left !== -1 ) { + return source.slice(0, _left) + lws + source.slice(_left + lws.length); + } + else return source; +} diff --git a/javascript/nodeJs-keywords-GFunction.js b/javascript/nodeJs-keywords-GFunction.js new file mode 100644 index 0000000..5509750 --- /dev/null +++ b/javascript/nodeJs-keywords-GFunction.js @@ -0,0 +1,540 @@ +// requirements +//////////////////////////////////////////////////////////////////////////////// + +var mysql = require('mysql'); + +const fs = require('fs'); + +const os = require('os'); + + +// preloaded data +//////////////////////////////////////////////////////////////////////////////// + +// any-character to keyboard-latin mapping +// ----------------------------------------------------------------------------- +var ORiGiNal = 'ςερτυθιοπασδφγηξκλζχψωβνμΕΡΤΥΘΙΟΠΑΣΔΦΓΗΞΚΛΖΧΨΩΒΝΜάέήίόύώϊΐϋΆΈΉΊΌΎΏΪΫQWERTYUIOPASDFGHJKLZXCVBNMqwertyuiopasdfghjklzxcvbnm0123456789- '.split(''); +var kbKeyZed = 'sertyuiopasdfghjklzxcvbnmertyuiopasdfghjklzxcvbnmaehioyviiyaehioyviyqwertyuiopasdfghjklzxcvbnmqwertyuiopasdfghjklzxcvbnm0123456789- '.split(''); +const map = new Map(); +for (var i=0; i { + synonyms.push( grp.split(' ') ); + synonym_kbs.push( kb_trans(grp).split(' ') ); +}) + +// significant terms +// ----------------------------------------------------------------------------- +significantExceptios = '7UP 3ΑΛΦΑ 17 3Π 7DAYS K2R'.split(' ') + + +// replaces (correcting descriptions) +// ----------------------------------------------------------------------------- +replaces = []; +replaceSource = [ + '3 ΑΛΦΑ ;3ΑΛΦΑ ', + 'HEAD & SHOULDERS ;HEAD&SHOULDERS ', + 'W.K Kellogg ; ', + 'ΦΙΛΕΤ ;Φιλέτο ', + 'ΕΝΕΛΛΑΔ ;Εν-Ελλάδι ', + 'ΓΑΛΟΠΟΥΛ ;Γαλοπούλα ', + '7 DAYS ;7DAYS ', + 'ΜΠΑΡΜΠΑ ΣΤΑΘΗ ;ΜΠΑΡΜΠΑ-ΣΤΑΘΗΣ ' +] +replaceSource.forEach( it => { + st = it.split(';'); + replaces.push({ src: st[0], trg: st[1] }); +}); + + +// words that shall not be searched first +// ----------------------------------------------------------------------------- +var noRootKeywords = []; +noRoot = [ + 'χωρίς', + 'εισαγωγής', + 'δώρο', + 'γεύση', + 'γεύσεις', + 'φέτες', + 'Χωρίς-Γλουτένη', + 'Χωρίς-Ζάχαρη', + 'Χωρίς-Αλάτι', + 'Χωρίς-Λακτόζη', + 'Χωρίς-Συντηρητικά', + 'Χωρίς-Αλκοόλ', + 'Χωρίς-Kαφεϊνη', + 'Χωρίς-Γλυκάνισο', + 'Χωρίς-Ανθρακικό', + 'Υψηλής-Παστερίωσης', + 'Ολες-τις-Χρήσεις', + 'Ολικής-Άλεσης', + 'Ολικής-Aλέσεως', + 'Ολικής', + 'Γαϊδούρας', + 'Γαϊδάρου', + 'Ρούχων', + 'Πιάτων', + 'πλύσεις', + 'Πλυντηρίου', + 'Φύλλων', + 'Γάλακτος', + 'Χρήσης', + 'Τύπου', + 'Ολλανδίας', + 'Απορριμμάτων', + 'Medium', + 'Μαλλιά', + 'Μαλλιών', + 'Γενικής', + 'Plus', + 'Classic', + 'Έκπληξη', + 'Μάνης', + 'Ελάτου', + 'Άγριων', + 'Βοτάνων', + 'Λακωνίας', + 'ΠΑΡΑΓΓΕΛΙΩΝ' +] +noRoot.forEach( w => { noRootKeywords.push(kb_trans(w)); }); + + +// 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', + 'Ολες-τις-Χρήσεις', + 'Το-Μάννα', + 'Χωρίς-προσθήκη-ζάχαρης' +] + + +// 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(' ') +removeOriginals.forEach( w => { removeList.push( kb_trans(w)); }); + + + + + + +// ready to get main data to preccess +//////////////////////////////////////////////////////////////////////////////// + +// db connection parametres +// ----------------------------------------------------------------------------- +var con = mysql.createConnection({ + // host: "/cloudsql/pythia-251711:europe-west4:pythia-db-eu", + socketPath: "/cloudsql/pythia-251711:europe-west4:pythia-db-eu", + user: "pythia_services", + password: process.env.DB_PASSWORD, + database: "pythia_db" +}); + + + + + +// keyword links (word-links dictionary; array of objects) +// ----------------------------------------------------------------------------- +var kwlinks_ = []; ////////////////////////////// MAIN OUTPUT OF THE SCRIPT + + +// connect; +// get records to proccess; +// call main proccess function; +// save dictionary; +// end script; +// ----------------------------------------------------------------------------- + +exports.main = () => { + + con.connect(function(err) { + // connect; + if (err) throw err; + console.log("Connected!"); + + var sql = "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 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"; + + // query sql + con.query(sql, function (err, result) { + if (err) throw err; + console.log('Records from database received!') + + do_proccess(result); // proccess + console.log('Keywords proccesed!') + + save_keywords(); // save + upload_file('pythia-files', os.tmpdir()+'/keywords.json', 'uploads/orders/keywords.json'); + console.log('Results saved! Exiting.') + + // echo memory stats + const used = process.memoryUsage(); + for (let key in used) { + console.log(`${key} ${Math.round(used[key] / 1024 / 1024 * 100) / 100} MB`); + } + }); + }); +} + + + +// Google Cloud Functions +//////////////////////////////////////////////////////////////////////////////// +function upload_file( bucketName, filePath, destFileName ) { + // [START storage_upload_file] + + // Sample code + // const bucketName = 'your-unique-bucket-name'; // The ID of your GCS bucket + // const filePath = 'path/to/your/file'; // The path to your file to upload + // const destFileName = 'your-new-file-name'; // The new ID for your GCS file + + // Imports the Google Cloud client library + const {Storage} = require('@google-cloud/storage'); + + // Creates a client + const storage = new Storage(); + + async function uploadFile() { + await storage.bucket(bucketName).upload(filePath, { + destination: destFileName, + gzip: true, // serve compressed + metadata: { // cache for 8 hours + cacheControl: 'public, max-age=28800', + } + }); + + console.log(`${filePath} uploaded to ${bucketName}`); + } + + uploadFile().catch(console.error); + // [END storage_upload_file] +} + + + + + +// main proccess +// ----------------------------------------------------------------------------- +function do_proccess(obj) { + // console.log(JSON.stringify(obj, null, 2)); + + obj.forEach( rec => { + var description = preproccess_text( rec.product_description); + var fq = rec.FREQuency; + var pid = rec.eys_code; + + var keys = []; + var words = description.split(' '); + + // filter words; keep only significant + words.forEach( w => { + if (removeList.indexOf(kb_trans(w)) == -1) // if not excluded + if (is_significant(w)) // and significant + keys.push(w); // add it to keys + }); + // console.log(pid, description, keys); + + keys.forEach( w => { + var wl = synonym_keys(w); + + // if key CAN be a root word (not a no-Root-keyword) update root-node + if (noRootKeywords.indexOf(kb_trans(w)) == -1) { + + root_key(wl, fq); // update root keyword stats + + // if key is the only in the list of product's keywords + // connect it with a dummy key (to preserve the reference to the product) + if (keys.length == 1) connect_keys(wl, ['*'], pid, fq); + + // connect w with all the other product's keywords + keys.forEach( w2 => { + if (w2 != w) { + var w2syns = synonym_keys(w2); + connect_keys( wl, w2syns, pid, fq); + } + }); + } + + }); + + }); // main proccessing finished; + + // post proccess + // ------------------------------------------------------------------------- + + // remove cached keys from final array + kwlinks_.forEach( ro => { + delete ro.kb; + ro.c.forEach( ch => { delete ch.kb; }); + }); + + // sort root and child nodes by frequency descanding + kwlinks_.forEach( it => { + it.c = it.c.sort((a, b) => b.f - a.f ); + }); + kwlinks_ = kwlinks_.sort((a, b) => b.f - a.f ); +} + + +// save proccess +// ----------------------------------------------------------------------------- +function save_keywords() { + let jsonStr = JSON.stringify(kwlinks_); + // console.log(jsonStr); + + fs.writeFileSync(os.tmpdir() + "/keywords.json", jsonStr, 'utf8', (err) => { + if (err) { + console.log("An error occured while writing keywords.json"); + return console.log(err); + } + console.log("JSON file has been saved."); + }); +} + + +// functions for linking words in keywords dictionary +//////////////////////////////////////////////////////////////////////////////// + +// set root-keyword: wl (if not exist) +// update frequency: f +// * wl is a list of synonym-words +// ** comparison is based on the *keyboard* format +// --- +function root_key ( wl, f ) { + var wkb = kb_trans(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 (i=0; i< kwlinks_.length ; i++) { + if (kwlinks_[i].kb == wkb) { + kwlinks_[i].f += f; + return true; + } + } + // if not exists, append keyword + kwlinks_.push({ + w : wl, + kb : wkb, + f : f, + c : [] + }); + return true; +} + + +// connect keys: a , b (each one is a list of synonmyms) +// of product with id: i +// with frequency: f +// --- +function connect_keys( a, b, id, f ) { + var kbA = kb_trans(a[0]); + var kbB = kb_trans(b[0]); + var bExists = false; + + if (kbA == kbB) return false; // exclude just-in-case + + for (i=0; i< kwlinks_.length ; i++) { + if (kwlinks_[i].kb == kbA) { // found: a; + // update connection to: b + bExists = false; + for (j=0 ; j < kwlinks_[i].c.length ; j++) { + if (kwlinks_[i].c[j].kb == kbB) { + bExists = true; + // update the connection's data + kwlinks_[i].c[j].f += f; + kwlinks_[i].c[j].p.push(id) + break; + } + } + // if connection not exist, init a new one + if (bExists == false) { + // create connection with: b + kwlinks_[i].c.push({ + w : b, + kb : kbB, + f : f, + p : [ i ] + }); + } + return true; + } + } +} + + +// other supplementary functions +//////////////////////////////////////////////////////////////////////////////// + + +// kb_trans translates string to keyboard-latin keys; +// --- +function kb_trans(str) { + str = str.replace('\'',''); + var out = ''; + for (var i=0 ; i< str.length; i++) out += map.get(str[i]); + return out; +} + +// clean text trims some characters (+.') and internal multiple-spaces +// --- +function clean_text(txt) { + return txt.replace('+',' ').replace('.',' ') + .replace(' ',' ') + .replace(' ',' '); +} + +// check if term is significant +// (if not, the term will be excluded from keywords dicionary) +// --- +function is_significant(str) { + if (str == '') return false; + if (significantExceptios.indexOf(str) !== -1) return true; + return !(/\d/.test(str)); +} + +// check if word: w +// ...has synonyms; return list of synonyms +// --- +function synonym_keys(w) { + w_kb = kb_trans(w); + for (i=0 ; i < synonym_kbs.length ; i++) { + if (synonym_kbs.indexOf(w_kb) !== -1) + return synonyms[i]; + } + return [ w ]; +} + +// edit common mistakes +// with suggested replaces +function do_replaces(str) { + replaces.forEach( it => { str = str.replace(it.src, it.trg); }); + return str; +} + +// preproccess description +// --- +function preproccess_text(str) { + str = do_replaces(str); + str = clean_text(str); + str = mark_linked_words(str); + return str; +} + + +// mark linked words (connect them with a dash) +// return new text after "all-links" are marked +// --- +function mark_linked_words(txt) { + linkedWords.forEach( lw => { txt = mark_link( lw, txt ); }); + return txt +} + +// mark a link (lws) to a text (source) +// conecting them with a dash/minus character +// --- +function mark_link(lws, source) { + var src_kb = kb_trans(source.replace(' ', '-')); + var lws_kb = kb_trans(lws.replace(' ', '-')); + var _left = src_kb.toLowerCase().indexOf(lws_kb.toLowerCase()) + if (_left !== -1 ) { + return source.slice(0, _left) + lws + source.slice(_left + lws.length); + } + else return source; +} + diff --git a/javascript/nodetest.js b/javascript/nodetest.js new file mode 100755 index 0000000..0670113 --- /dev/null +++ b/javascript/nodetest.js @@ -0,0 +1,3 @@ +#!/usr/bin/node +console.log('ok?'); + diff --git a/javascript/test-cdn.js b/javascript/test-cdn.js new file mode 100644 index 0000000..b72fcee --- /dev/null +++ b/javascript/test-cdn.js @@ -0,0 +1,24 @@ +// Imports the Google Cloud client library. +const {Storage} = require('@google-cloud/storage'); + +// Instantiates a client. Explicitly use service account credentials by +// specifying the private key file. All clients in google-cloud-node have this +// helper, see https://github.com/GoogleCloudPlatform/google-cloud-node/blob/master/docs/authentication.md +const projectId = 'pythia-251711'; +const keyFilename = '/home/geo/pythia-api/auth/pythia-251711-047e3d5e6608.json'; +const storage = new Storage({projectId, keyFilename}); + +// Makes an authenticated API request. +async function listBuckets() { + try { + const [buckets] = await storage.getBuckets(); + + console.log('Buckets:'); + buckets.forEach(bucket => { + console.log(bucket.name); + }); + } catch (err) { + console.error('ERROR:', err); + } +} +listBuckets(); \ No newline at end of file diff --git a/javascript/test-sort.js b/javascript/test-sort.js new file mode 100644 index 0000000..5d6c6ef --- /dev/null +++ b/javascript/test-sort.js @@ -0,0 +1,75 @@ +const fs = require('fs'); + +var keys = [ + { + w: 'ok', + f: 200, + c: [ + { w: 'one', f: 100 }, + { w: 'two', f: 200 }, + { w: 'three', f: 300 }, + { w: 'four', f: 400 } + ] + }, + { + w: 'nope', + f: 150, + c: [ + { w: 'one', f: 1000 }, + { w: 'two', f: 200 }, + { w: 'three', f: 30 }, + { w: 'four', f: 4 } + ] + }, + { + w: 'maybe', + f: 300, + c: [ + { w: 'one', f: 3 }, + { w: 'two', f: 3 }, + { w: 'three', f: 5 }, + { w: 'four', f: 4 } + ] + } +]; + +keys.forEach( it => { + it.c = it.c.sort((a, b) => b.f - a.f ); +}); +keys = keys.sort((a, b) => b.f - a.f ); + +console.log(JSON.stringify(keys, undefined, 2)); + + +let rawdata = fs.readFileSync('results/keywords.json'); +let _keywords = JSON.parse(rawdata); + +function get_root(x) { + var response; + _keywords.forEach( o => { if ( o.w[0] == x[0] ) response = o; }) + return response; +} + +function find_root(x) { + return _keywords.find( o => o.w[0] == x[0]); +} + +function for_root(x) { + for(j=0; j<_keywords.length; j++) + if (_keywords[j].w[0] == x[0]) return _keywords[j]; + return false; +} +// _keywords.forEach( it => { +// obj = get_root(it.w); +// }) + +o1 = get_root(['Επιφάνειες']); +o2 = find_root(['Επιφάνειες']); +o3 = for_root(['Επιφάνειες']); + +console.log(JSON.stringify(o1)); +console.log(JSON.stringify(o2)); +console.log(JSON.stringify(o3)); +// for(i=0; i<100000; i++) o1 = get_root(['Επιφάνειες']); +// for(i=0; i<1000000; i++) o1 = find_root(['Επιφάνειες']); +for(i=0; i<1000000; i++) o1 = for_root(['Επιφάνειες']); \ No newline at end of file diff --git a/package.json b/package.json new file mode 100644 index 0000000..49ed5f9 --- /dev/null +++ b/package.json @@ -0,0 +1,5 @@ +{ + "dependencies": { + "mysql": "^2.18.1" + } +} diff --git a/products-dict-v3.py b/products-dict-v3.py deleted file mode 100644 index 6f37de9..0000000 --- a/products-dict-v3.py +++ /dev/null @@ -1,270 +0,0 @@ -## LIBRARIES -# ////////////////////////////////////////////////////////////////////////////// - -import pandas as pd # pandas for excel reading -import re # regex -import json # json -import os.path # ... - - -## 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 - - -def cleanText(x) : - removeList = [ ' με ', ' σε ', ' για ', ' του ', ' της ', ' των ', ' από ', ' ΜΕ ', ' ΣΕ ', ' ΓΙΑ ', ' ΑΠΟ ', '&', '.', ',', '!', '(', ')', '[', ']', '\'', '\"' ] - - for r in removeList : - x = x.replace(r, ' ') - - x.replace(' ', ' ') # remove spare spaces - x.replace(' ', ' ') - x.replace(' ', ' ') - - return x - - -# function 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 - if x in ['7UP', '3ΑΛΦΑ'] : - return True - - return not bool(re.match("\S*\d+\S*", x)) - - -def kbLatinString( txt ) : - maTable = txt.maketrans( - "ςερτυθιοπασδφγηξκλζχψωβνμΕΡΤΥΘΙΟΠΑΣΔΦΓΗΞΚΛΖΧΨΩΒΝΜάέήίόύώϊϋΆΈΉΊΌΎΏΪΫ", - "sertyuiopasdfghjklzxcvbnmertyuiopasdfghjklzxcvbnmaehioyviyaehioyviy" - ) - 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 ) : - if a == b : - return False ## exclude just-in-case - - for it in l : - if it['w'] == a : - - # 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 ] - }) - - - -## 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 -} - - - - -## SET SOURCE and EXPORT FileNames -# ------------------------------------------------------------------------------ -# location of excel file -loc = "./data/PRODucts2search-wBrands.xlsx" - -print("default filename:", loc) -newXLfile = input("input other Excel filename [enter to keep default]: ") - -if newXLfile != "" and os.path.exists(newXLfile): - loc = newXLfile -else : - print(newXLfile, "is not a file; default is kept;") - -## baseEXPORTname = input("Base export name: ") - - - - -## Read data -# ////////////////////////////////////////////////////////////////////////////// - -df = pd.read_excel(loc) # read data from excel file - -rows = df.iterrows() # set rows list - - -# --- 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) - - -# --- temporary variables (initialize) - -## LOOP through the rows to pre-proccess all products -## --- -for idx, row in rows : - - description = row[_COL['descr']].strip() # 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 sescription string - words = description.strip().split() # split to words - - # identify significant words - keys = [] - for w in words : - if isSignificant(w) : - keys.append(w) - - print(pid, 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-v3.json", "w", encoding="utf-8") as outfile : - data = json.dump(keywords_, outfile, sort_keys=False, indent=3, ensure_ascii=False) - -with open("results/minilist-v3.json", "w", encoding="utf-8") as outfile : - data = json.dump(minilist_, outfile, sort_keys=False, indent=3, ensure_ascii=False) - -with open("results/products.json", "w", encoding="utf-8") as outfile : - data = json.dump(products_, outfile, sort_keys=False, indent=3, ensure_ascii=False) diff --git a/products-dictionary.py b/products-dictionary.py deleted file mode 100644 index 1f35a67..0000000 --- a/products-dictionary.py +++ /dev/null @@ -1,228 +0,0 @@ -## LIBRARIES -# ////////////////////////////////////////////////////////////////////////////// - -import pandas as pd # pandas for excel reading -import re # regex -import json # json -import os.path # ... - - -## 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 - - -def cleanText(x) : - removeList = [ ' με ', ' σε ', ' για ', ' του ', ' της ', ' των ', ' από ', '&', '.', ',', '!', '(', ')', '[', ']', '\'', '\"' ] - - for r in removeList : - x = x.replace(r, ' ') - - x.replace(' ', ' ') # remove spare spaces - x.replace(' ', ' ') - x.replace(' ', ' ') - - return x - - -def isSignificant(x) : - # is significant if words has no digit-characters - return not bool(re.match("\S*\d+\S*", x)) - - - -# set root-keyQ: w (if not exist) -# update frequency: f -# into list: l -## --- -def rootKey ( w, f, l ) : - keyExists = False - for it in l : - if it['w'] == w : - keyExists = True - it['f'] += f - - if keyExists == False : - l.append({ - 'w' : w, - 'f' : f, - 'c' : [] - }) - - - -# connect keys: a , b -# of product with id: i -# with frequency: f -# into list: l -## --- -def connectKeys( a, b, i, f, l ) : - if a == b : - return False ## exclude just-in-case - - for it in l : - if it['w'] == a : - - # word a found; - # lets update the connection to: b - bExists = False - - for jt in it['c'] : - if jt['w'] == 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, - 'f': f, - 'p': [ i ] - }) - - - - - -## 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 -} - - - - -## SET SOURCE and EXPORT FileNames -# ------------------------------------------------------------------------------ -# location of excel file -loc = "./data/PRODucts2search-wBrands.xlsx" - -print("default filename:", loc) -newXLfile = input("input other Excel filename [enter to keep default]: ") - -if newXLfile != "" and os.path.exists(newXLfile): - loc = newXLfile -else : - print(newXLfile, "is not a file; default is kept;") - -## baseEXPORTname = input("Base export name: ") - - - -## Read data -# ////////////////////////////////////////////////////////////////////////////// - -df = pd.read_excel(loc) # read data from excel file - -rows = df.iterrows() # set rows list - - -# --- Lists to fill -keywords_ = [] # all data -minilist_ = [] - -## keywords format: -## [ -## { -## w : 'fresh', -## f : 150, -## c : [ -## { w : 'milk', f : 150 , p : [122, 254, 907] }, -## { w : 'juice', f : 50 , p : [254, 351] } -## ] -## }, -## {...}, -## ... -## ] -## --- index: -## w : word -## f : frequency -## c : combos / connections -## p : list of product-ids with this combo - -# --- temporary variables (initialize) - - -## LOOP through the rows to pre-proccess all products -## --- -for idx, row in rows : - - description = row[_COL['descr']].strip() # product description - pid = domeInt( row[_COL['pid']] ) # product-id - fq = domeInt( row[_COL['freq']] ) # frequency - - # TODO: - # identify brands - # then ... - - description = cleanText(description) # clean sescription string - words = description.strip().upper().split() # split to words - ## words = [w.strip('.,!;()[]') for w in words] # clean strings - - # identify significant words - keys = [] - for w in words : - if isSignificant(w) : - keys.append(w) - - print(pid, keys) - # append words (and their combos) to the list - for w in keys : - rootKey( w, fq, keywords_ ) - for w2 in keys : - if w2 != w : - 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( it['w'] ) - - -## OUTPUT final data to a json-format file -# ////////////////////////////////////////////////////////////////////////////// - -with open("results/keywords-v2.json", "w", encoding="utf-8") as outfile : - data = json.dump(keywords_, outfile, sort_keys=False, indent=3, ensure_ascii=False) - -with open("results/minilist.json", "w", encoding="utf-8") as outfile : - data = json.dump(minilist_, outfile, sort_keys=False, indent=3, ensure_ascii=False) diff --git a/python/check-linked.py b/python/check-linked.py new file mode 100644 index 0000000..2492ac5 --- /dev/null +++ b/python/check-linked.py @@ -0,0 +1,300 @@ +## LIBRARIES +# ////////////////////////////////////////////////////////////////////////////// + +# import pandas as pd # pandas for excel reading +import re # regex +import json # json +import os.path # ... +import datetime + +t0_ = datetime.datetime.now() + + +## LOCAL FUNCTIONS +# ////////////////////////////////////////////////////////////////////////////// + + +## 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", + "sertyuiopasdfghjklzxcvbnmertyuiopasdfghjklzxcvbnmaehioyviiyaehioyviyqwertyuiopasdfghjklzxcvbnm" + ) + txt = txt.replace('\'', '') + return txt.translate(maTable).lower() + + + + +# letters-only translation to key-pressed characters (latin) +# --- +def kbLatinLetter( txt ) : + maTable = txt.maketrans( + "ςερτυθιοπασδφγηξκλζχψωβνμΕΡΤΥΘΙΟΠΑΣΔΦΓΗΞΚΛΖΧΨΩΒΝΜάέήίόύώϊΐϋΆΈΉΊΌΎΏΪΫ", + "sertyuiopasdfghjklzxcvbnmertyuiopasdfghjklzxcvbnmaehioyviiyaehioyviy" + ) + 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 = [ + 'Χωρίς-Γλουτένη', + 'Χωρίς-Ζάχαρη', + 'Χωρίς-Αλάτι', + 'Χωρίς-Λακτόζη', + 'Χωρίς-Συντηρητικά', + 'Χωρίς-Αλκοόλ', + 'Χωρίς-Kαφεϊνη', + 'Χωρίς-Γλυκάνισο', + 'Χωρίς-Ανθρακικό', + 'Χωρίς-Προσθήκη' + 'Υψηλής-Παστερίωσης', + 'Ολικής-Άλεσης', + 'Ολικής-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 + + +## PREPADE (or build) exception objects +# ////////////////////////////////////////////////////////////////////////////// + + +# --- list of possible combos to check +_2check_kb = [] +_2check = ['με', 'σε', 'για', 'όλες', 'χωρίς' ] +for it in _2check : + _2check_kb.append(kbLatinString(it)) + +linkedWordsFound = [ + { 'w' : 'se', 'links' : [] }, + { 'w' : 'oles-tis', 'links' : [] }, + { 'w' : 'xvris', 'links' : [] } +] + +def recordLink( parent, child, id ) : + if parent != '' : + for it in linkedWordsFound : + if parent == it['w'] : + is_a_new_combo = True + for li in it['links'] : + if li['w'] == child : + li['p'].append(id) + li['c'] += 1 + is_a_new_combo = False + break + if is_a_new_combo : + it['links'].append({ 'w': child, 'p': [ id ], 'c': 1 }) + + + +# --- 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', + 'Γαϊδούρας Γαϊδάρου', + 'ΚΑΛΟΓΕΡΑΚΗΣ ΚΑΛΟΓΕΡΑΚΗ', + 'ΚΑΪΔΑΝΤΖΗΣ ΚΑΪΔΑΝΤΖΗ', + 'ΥΦΑΝΤΗΣ ΥΦΑΝΤΗ', + 'ΣΥΝΑΓΡΙΔΑ ΣΥΝΑΓΡΙΔΕΣ', + 'Ντομάτα Ντομάτας', + 'Ελαφρύ Ελαφρά Light', + 'Εγχώρια Ελληνικό Ελληνικά', + 'τριμμένη τριμμένο', + 'Τόνος Τόνου', + 'Κριθαρένια κρίθινα' +] + + +## Read data +# ////////////////////////////////////////////////////////////////////////////// + + +_file = open ('data/eshop-products.json', "r") # JSON source file +results_ = json.loads(_file.read()) # Reading from file +_file.close() # Closing file + + +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['Title'] # product description + pid = row['ID'] # product-id + fq = row['freq'] # frequency + + description = cleanText(description) # clean description string before spliting + + description = markLinkedWords(description) # ... + + keys = description.split() # split to words = keys + + is_combo_key = False + combo_key = '' + + # append words (and their combos) to the list + for w in keys : + + if kbLatinString(w) in _2check_kb : + combo_key = w + is_combo_key = True + else : + if is_combo_key : + recordLink(kbLatinString(combo_key), kbLatinString(w), pid) + is_combo_key = False + combo_key = '' + +# print(linkedWordsFound) + +# PRINT RESULTS +# --- +for ri in linkedWordsFound : + print('---', ri['w'], ':', len(ri['links'])) + + subtotal = 0 + for li in ri['links'] : + subtotal += li['c'] + + for li in ri['links'] : + ## if li['c'] > 10 or li['c']/subtotal > .2 : + print( ri['w'], li['w'], ' : ', li['c'], ' (', int(li['c']*100/subtotal), '%)' ) diff --git a/python/code-examples.py b/python/code-examples.py new file mode 100644 index 0000000..d8d0042 --- /dev/null +++ b/python/code-examples.py @@ -0,0 +1,24 @@ +# test +a = 1 +b = 4 + +def addto(x, l) : + l.append({ + "n" : x, + "c": [] + }) + for it in l : + if it["n"] == 4 : + subl = it["c"] + subl.append(x) + it['c'] = subl + + +malist = [] + +malist.append({ "n" : a }) +print(malist) + +addto(b, malist) +addto(b, malist) +print(malist) diff --git a/python/products-dict-v3.py b/python/products-dict-v3.py new file mode 100644 index 0000000..a9b2c97 --- /dev/null +++ b/python/products-dict-v3.py @@ -0,0 +1,338 @@ +## LIBRARIES +# ////////////////////////////////////////////////////////////////////////////// + +import pandas as pd # pandas for excel reading +import re # regex +import json # json +import os.path # ... + + +## 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 removeList : + 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( + "ςερτυθιοπασδφγηξκλζχψωβνμΕΡΤΥΘΙΟΠΑΣΔΦΓΗΞΚΛΖΧΨΩΒΝΜάέήίόύώϊϋΆΈΉΊΌΎΏΪΫ", + "sertyuiopasdfghjklzxcvbnmertyuiopasdfghjklzxcvbnmaehioyviyaehioyviy" + ) + + 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 ] + }) + + + +## 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 = 'Μας με σε για του της των από ΜΕ ΣΕ ΓΙΑ στο στον Στο από 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' +] +for it in linkedWordOriginals : + linkedWords.append(kbLatinString(it)) + + +synonyms = [] +synonymOriginals = [ + 'μπίρα, μπύρα, μπίρες, μπύρες', + 'αυγά, αβγά, αυγό, αβγό', + 'σίκαλης, σικάλεως', + 'ξηρά, ξερά', + 'ρολό, ρολλό' + 'coca-cola, cocacola, coke', + 'χαρτί-υγείας, ρολό-υγείας, χαρτί-τουαλέτας', + 'χαρτί-κουζίνας, ρολό-κουζίνας', + 'μπίρα, μπίρες', + 'αυγά, αυγό' +] +for it in synonymOriginals : + synonyms.append(kbLatinString(it)) +""" + + +## SET SOURCE and EXPORT FileNames +# ------------------------------------------------------------------------------ +# location of excel file +loc = "./data/PRODucts2search-wBrands.xlsx" + +print("default filename:", loc) +newXLfile = input("input other Excel filename [enter to keep default]: ") + +if newXLfile != "" and os.path.exists(newXLfile): + loc = newXLfile +else : + print(newXLfile, "is not a file; default is kept;") + +## baseEXPORTname = input("Base export name: ") + + + + +## Read data +# ////////////////////////////////////////////////////////////////////////////// + +df = pd.read_excel(loc) # read data from excel file + +rows = df.iterrows() # set rows list + + +# --- 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) + + +# --- temporary variables (initialize) + +## LOOP through the rows to pre-proccess all products +## --- +for idx, row in rows : + + 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 sescription string + words = description.strip().split() # split to words + + # identify significant words + keys = [] + for w in words : + if isSignificant(w) : + keys.append(w) + + 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-v3.json", "w", encoding="utf-8") as outfile : + data = json.dump(keywords_, outfile, sort_keys=False, indent=3, ensure_ascii=False) + +with open("results/minilist-v3.json", "w", encoding="utf-8") as outfile : + data = json.dump(minilist_, outfile, sort_keys=False, indent=3, ensure_ascii=False) + +with open("results/products.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 diff --git a/python/products-dict-v4.py b/python/products-dict-v4.py new file mode 100644 index 0000000..72a68d2 --- /dev/null +++ b/python/products-dict-v4.py @@ -0,0 +1,383 @@ +## 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 # ... + + +## 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 = "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 + 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['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 + + 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-v3.json", "w", encoding="utf-8") as outfile : + data = json.dump(keywords_, outfile, sort_keys=False, indent=3, ensure_ascii=False) + +with open("results/minilist-v3.json", "w", encoding="utf-8") as outfile : + data = json.dump(minilist_, outfile, sort_keys=False, indent=3, ensure_ascii=False) + +with open("results/products.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 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 diff --git a/python/products-dictionary.py b/python/products-dictionary.py new file mode 100644 index 0000000..1f35a67 --- /dev/null +++ b/python/products-dictionary.py @@ -0,0 +1,228 @@ +## LIBRARIES +# ////////////////////////////////////////////////////////////////////////////// + +import pandas as pd # pandas for excel reading +import re # regex +import json # json +import os.path # ... + + +## 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 + + +def cleanText(x) : + removeList = [ ' με ', ' σε ', ' για ', ' του ', ' της ', ' των ', ' από ', '&', '.', ',', '!', '(', ')', '[', ']', '\'', '\"' ] + + for r in removeList : + x = x.replace(r, ' ') + + x.replace(' ', ' ') # remove spare spaces + x.replace(' ', ' ') + x.replace(' ', ' ') + + return x + + +def isSignificant(x) : + # is significant if words has no digit-characters + return not bool(re.match("\S*\d+\S*", x)) + + + +# set root-keyQ: w (if not exist) +# update frequency: f +# into list: l +## --- +def rootKey ( w, f, l ) : + keyExists = False + for it in l : + if it['w'] == w : + keyExists = True + it['f'] += f + + if keyExists == False : + l.append({ + 'w' : w, + 'f' : f, + 'c' : [] + }) + + + +# connect keys: a , b +# of product with id: i +# with frequency: f +# into list: l +## --- +def connectKeys( a, b, i, f, l ) : + if a == b : + return False ## exclude just-in-case + + for it in l : + if it['w'] == a : + + # word a found; + # lets update the connection to: b + bExists = False + + for jt in it['c'] : + if jt['w'] == 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, + 'f': f, + 'p': [ i ] + }) + + + + + +## 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 +} + + + + +## SET SOURCE and EXPORT FileNames +# ------------------------------------------------------------------------------ +# location of excel file +loc = "./data/PRODucts2search-wBrands.xlsx" + +print("default filename:", loc) +newXLfile = input("input other Excel filename [enter to keep default]: ") + +if newXLfile != "" and os.path.exists(newXLfile): + loc = newXLfile +else : + print(newXLfile, "is not a file; default is kept;") + +## baseEXPORTname = input("Base export name: ") + + + +## Read data +# ////////////////////////////////////////////////////////////////////////////// + +df = pd.read_excel(loc) # read data from excel file + +rows = df.iterrows() # set rows list + + +# --- Lists to fill +keywords_ = [] # all data +minilist_ = [] + +## keywords format: +## [ +## { +## w : 'fresh', +## f : 150, +## c : [ +## { w : 'milk', f : 150 , p : [122, 254, 907] }, +## { w : 'juice', f : 50 , p : [254, 351] } +## ] +## }, +## {...}, +## ... +## ] +## --- index: +## w : word +## f : frequency +## c : combos / connections +## p : list of product-ids with this combo + +# --- temporary variables (initialize) + + +## LOOP through the rows to pre-proccess all products +## --- +for idx, row in rows : + + description = row[_COL['descr']].strip() # product description + pid = domeInt( row[_COL['pid']] ) # product-id + fq = domeInt( row[_COL['freq']] ) # frequency + + # TODO: + # identify brands + # then ... + + description = cleanText(description) # clean sescription string + words = description.strip().upper().split() # split to words + ## words = [w.strip('.,!;()[]') for w in words] # clean strings + + # identify significant words + keys = [] + for w in words : + if isSignificant(w) : + keys.append(w) + + print(pid, keys) + # append words (and their combos) to the list + for w in keys : + rootKey( w, fq, keywords_ ) + for w2 in keys : + if w2 != w : + 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( it['w'] ) + + +## OUTPUT final data to a json-format file +# ////////////////////////////////////////////////////////////////////////////// + +with open("results/keywords-v2.json", "w", encoding="utf-8") as outfile : + data = json.dump(keywords_, outfile, sort_keys=False, indent=3, ensure_ascii=False) + +with open("results/minilist.json", "w", encoding="utf-8") as outfile : + data = json.dump(minilist_, outfile, sort_keys=False, indent=3, ensure_ascii=False) diff --git a/python/products-src-json.py b/python/products-src-json.py new file mode 100644 index 0000000..9e3da8a --- /dev/null +++ b/python/products-src-json.py @@ -0,0 +1,581 @@ +## LIBRARIES +# ////////////////////////////////////////////////////////////////////////////// + +# import pandas as pd # pandas for excel reading +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Π'.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 ; ', + 'ΦΙΛΕΤ ;Φιλέτο ', + 'ΕΝΕΛΛΑΔ ;Εν-Ελλάδι ', + 'ΓΑΛΟΠΟΥΛ ;Γαλοπούλα ', + ' ΓΥΝ.; ΓΥΝ ', + +] +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', + 'Γαϊδούρας Γαϊδάρου', + 'ΚΑΛΟΓΕΡΑΚΗΣ ΚΑΛΟΓΕΡΑΚΗ', + 'ΚΑΪΔΑΝΤΖΗΣ ΚΑΪΔΑΝΤΖΗ', + 'ΥΦΑΝΤΗΣ ΥΦΑΝΤΗ', + 'ΣΥΝΑΓΡΙΔΑ ΣΥΝΑΓΡΙΔΕΣ', + 'Ντομάτα Ντομάτας', + 'Ελαφρύ Ελαφρά Light', + 'Εγχώρια Εγχώριες Ελληνικό Ελληνική Ελληνικά', + 'τριμμένη τριμμένο', + 'Τόνος Τόνου', + 'Κριθαρένια κρίθινα', + 'Χωρίς-Kαφεϊνη Decaffeine', + 'Το-Μάννα Μάννα', + 'Κράνμπερι Κράνμπερις' +] + + +## Read data +# ////////////////////////////////////////////////////////////////////////////// + + +_file = open ('data/eshop-products.json', "r") # JSON source file +results_ = json.loads(_file.read()) # Reading from file +_file.close() # Closing file + + +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['Title'] # product description + pid = row['ID'] # product-id + fq = row['freq'] # frequency + + # edit descriptions + description = preprocessEdit(description) + + # 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) + + # update root word frequency (if w CAN be a root word) + if kbLatinLetter(w) not in noRootKeywords : + 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, 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) + + +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)) 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 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 diff --git a/python/read-brands.py b/python/read-brands.py new file mode 100644 index 0000000..2bbc767 --- /dev/null +++ b/python/read-brands.py @@ -0,0 +1,270 @@ +## LIBRARIES +# ////////////////////////////////////////////////////////////////////////////// + +import pandas as pd # pandas for excel reading +import re # regex +import json # json +import os.path # ... + + +## 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 + + +def cleanText(x) : + removeOriginals = 'Μας με σε για του της των από ΜΕ ΣΕ ΓΙΑ στο στον Στο από e g h k m n o p s x'.split(' ') + + for r in removeOriginals : + x = x.replace(r, ' ') + + x.replace(' ', ' ') # remove spare spaces + x.replace(' ', ' ') + x.replace(' ', ' ') + + return x + + +# function 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 + if x in ['7UP', '3ΑΛΦΑ'] : + return True + + return not bool(re.match("\S*\d+\S*", x)) + + +def kbLatinString( txt ) : + maTable = txt.maketrans( + "ςερτυθιοπασδφγηξκλζχψωβνμΕΡΤΥΘΙΟΠΑΣΔΦΓΗΞΚΛΖΧΨΩΒΝΜάέήίόύώϊϋΆΈΉΊΌΎΏΪΫ", + "sertyuiopasdfghjklzxcvbnmertyuiopasdfghjklzxcvbnmaehioyviyaehioyviy" + ) + 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 ) : + if a == b : + return False ## exclude just-in-case + + for it in l : + if it['w'] == a : + + # 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 ] + }) + + + +## 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 +} + + + + +## SET SOURCE and EXPORT FileNames +# ------------------------------------------------------------------------------ +# location of excel file +loc = "./data/PRODucts2search-wBrands.xlsx" + +print("default filename:", loc) +newXLfile = input("input other Excel filename [enter to keep default]: ") + +if newXLfile != "" and os.path.exists(newXLfile): + loc = newXLfile +else : + print(newXLfile, "is not a file; default is kept;") + +## baseEXPORTname = input("Base export name: ") + + + + +## Read data +# ////////////////////////////////////////////////////////////////////////////// + +df = pd.read_excel(loc) # read data from excel file + +rows = df.iterrows() # set rows list + + +# --- 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) + + +# --- temporary variables (initialize) + +## LOOP through the rows to pre-proccess all products +## --- +for idx, row in rows : + + description = row[_COL['descr']].strip() # 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 sescription string + words = description.strip().split() # split to words + + # identify significant words + keys = [] + for w in words : + if isSignificant(w) : + keys.append(w) + + print(pid, 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-v3.json", "w", encoding="utf-8") as outfile : + data = json.dump(keywords_, outfile, sort_keys=False, indent=3, ensure_ascii=False) + +with open("results/minilist-v3.json", "w", encoding="utf-8") as outfile : + data = json.dump(minilist_, outfile, sort_keys=False, indent=3, ensure_ascii=False) + +with open("results/products.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 diff --git a/python/readmysql.py b/python/readmysql.py new file mode 100644 index 0000000..a3cd31f --- /dev/null +++ b/python/readmysql.py @@ -0,0 +1,121 @@ +## pip3 install mysql-connector-python +import mysql.connector as mysql + +# enter your server IP address/domain name +HOST = "127.0.0.1" # or "domain.com" +# database name, if you want just to connect to MySQL server, leave it empty +DATABASE = "dev_pythia_db" +# this is the user you create +USER = "pythia_db_user_dev" ## "pythia_db_user_dev@cloudsqlproxy~35.203.252.44" +# user password +PASSWORD = "VnEP0eysjiXDHcfM" +# connect to MySQL server +_dbc = mysql.connect(host=HOST, database=DATABASE, user=USER, password=PASSWORD) +print("Connected to:", _dbc.get_server_info()) +# enter your code here! + + +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 + + + +cursor_ = _dbc.cursor() +#### cursor_.execute(''' +#### SELECT count(dop.order_id) as FREQuency, +#### dop.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 delivery_orders_products AS dop +#### LEFT JOIN delivery_orders AS do ON dop.order_id = do.id +#### LEFT JOIN product_list AS pl ON dop.product_id = pl.eys_code +#### 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 +#### GROUP BY dop.product_id +#### ORDER BY FREQuency DESC +#### ''') +#### +#### results = cursor_.fetchall() +#### +#### for rec in results : +#### print(rec) + + +sqlq = ''' + SELECT count(dop.order_id) as FREQuency, + dop.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 delivery_orders_products AS dop + LEFT JOIN delivery_orders AS do ON dop.order_id = do.id + LEFT JOIN product_list AS pl ON dop.product_id = pl.eys_code + 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 + GROUP BY dop.product_id + ORDER BY FREQuency DESC +''' + + +results = get_data_from_db(cursor_, sqlq) + +for r in results : + if r[1] in [1176465, 1430400, 1220273] : + print(r[1], r[6].split()) + + + + + + + + + + + + + + +## 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 + +## Queries +## --- +''' +-- ORDERS PER PRODUCT +SELECT count(dop.order_id) as FREQuency, + dop.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, + pcs4.description AS productGroup +FROM delivery_orders_products AS dop +LEFT JOIN delivery_orders AS do ON dop.order_id = do.id +LEFT JOIN product_list AS pl ON dop.product_id = pl.eys_code +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_categories_sap_4 pcs4 ON pcs4.id = pl.product_category_sap_4 +INNER JOIN product_brands pb ON pl.brand_id = pb.id +GROUP BY dop.product_id +ORDER BY FREQuency DESC +''' diff --git a/python/test.py b/python/test.py new file mode 100644 index 0000000..4c5a361 --- /dev/null +++ b/python/test.py @@ -0,0 +1,104 @@ +# 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):] + + +# text after "all-links" marked +# --- +def linksMarked(text) : + allinked = [ + 'Χωρίς-Ζάχαρη', + 'COCA-COLA', + 'Χωρίς-Αλάτι' + ] + for lw in allinked : + text = markLink( lw, text ) + + return text + + +# example +# --- +products = [ + "Μπάρες δημητριακών Nestle χωρίς ζάχαρη 2+1 δώρο", + "Coca Cola Zero χωρίς ζάχαρη 300ml", + "Καφές ΠΑΠΑΓΑΛΟΣ ΛΟΥΜΙΔΗΣ 100gr Κλασσικός", + "Μουσακάς μερίδα 300gr χωρίς αλάτι", + "Bic Metal ξυραφάκια 8+2 δώρο" +] + + +synonyms = [] +synonymOriginals = [ + 'μπίρα μπύρα μπίρες μπύρες', + 'αυγά αβγά αυγό αβγό', + 'σίκαλης σικάλεως', + 'ξηρά ξερά', + 'ρολό ρολλό' + 'coca-cola cocacola coke', + 'χαρτί-υγείας ρολό-υγείας χαρτί-τουαλέτας', + 'χαρτί-κουζίνας ρολό-κουζίνας', + 'οινος κρασι', + 'ΚΑΤΣΕΛΗΣ ΚΑΤΣΕΛΗ', + 'DR-OETKER OETKER', + 'DR.BECKMANN BECKMANN', + 'NES-CAFE NESCAFE', + 'Ολικής-Άλεσης Ολικής-Aλέσεως', + 'τσίπουρο ρακή' +] +for group in synonymOriginals : + words = group.split() + syns = [] + for w in words : # for every word in group of synonyms + w_kb = kbLatinLetter(w) + exist = False + for s in syns : # check if synonym exists + if s[1] == w_kb : + exist = True + if not exist : # if not: append it + #### syns.append({ + #### 'w' : w, + #### 'kb' : w_kb + #### }) + syns.append([ w, w_kb ]) + synonyms.append(syns) + +for p in products : + print( linksMarked(p) ) + +import json + +print(json.dumps(synonyms, ensure_ascii=False)) + + +import datetime + +start = datetime.datetime.now() + +malist = [ "καλαμπόκι-διαβητικών", "cocacola-zero", "γιουβαρλάκια", "WELCOME", "σφενδόνα", "σκλαβενίτης", 'bonora', 'kris-κρις-παπαδοπούλου', "τηλεφώνημα", "χωρίς-αλάτι" ] + +for i in range(1, 1000000) : + for w in malist : + tmp = kbLatinLetter(malist[i%10]) + +end = datetime.datetime.now() +print('execution time:', (end-start), 's') \ No newline at end of file diff --git a/workline.md b/workline.md new file mode 100644 index 0000000..83c401e --- /dev/null +++ b/workline.md @@ -0,0 +1,68 @@ +# WORKLINE + +## identidy BRANDS + +LAY's +L'Oreal +COCA-COLA +M&M's + + + +## Clean text + +* remove general/neutral words and symbols +* ignore some in-line characters +* also remove spare spaces + + + +## identify linked-words and common word-combos + +ex. + +* Χωρίς-Γλουτένη +* Χωρίς-Ζάχαρη +* Χωρίς-Αλάτι +* Χωρίς-Λακτόζη +* Χωρίς-Συντηρητικά +* Υψηλής-Παστερίωσης +* Ολικής-Άλεσης +* Χαρτί-Υγείας +* Χαρτί-Κοουζίνας +* Μπάρες-Δημητριακών + +may need to use an no-brake space +(Unicode: U+00A0 * HTML-code: * CSS-code: \00A0 * Entity: * Block: Latin-1) + +* can be identidied via dictinary/list of cases +* or via statistical analysis + + + +## handle synonyms + +ex. +* αυγά : αβγά, αυγό, αβγό +* μπίρα : μπύρα, μπίρες, μπύρες +* χαρτί : ρολό, ρολλό +* χαρτί-υγείας : ρολλό υγείας, ρολό τουαλέτας, χαρτί τουαλέτας + +* shall be declared via dictinary/list of cases + + + +## identify non-first words +words that should not be suggested in first place + +ex. + +χωρίς , μεγάλη , άλεσης , γλουτένη , παστερίωσης, τύπου , υγείας , δώρο , λακτόζη , κουζίνας , +καθαρισμού , φύλλων , Δημητριακών , ανθρακικό , γάλακτος , κυματιστά + +* can be identidied via dictinary/list of cases +* or via statistical analysis + + + +## blend next-word and final-product suggestions -- cgit v1.2.3