From adcb57d3316443bf5be6bb86d09f6d9f73b7f002 Mon Sep 17 00:00:00 2001 From: Geo Halkiadakis Date: Tue, 21 Mar 2023 17:42:06 +0200 Subject: linkeysearch: 2 step implementation --- javascript/freq.js | 129 +++++++++++++ javascript/keygen.js | 34 +++- javascript/node-sql.js | 490 ------------------------------------------------- 3 files changed, 162 insertions(+), 491 deletions(-) create mode 100644 javascript/freq.js delete mode 100644 javascript/node-sql.js diff --git a/javascript/freq.js b/javascript/freq.js new file mode 100644 index 0000000..6bf1517 --- /dev/null +++ b/javascript/freq.js @@ -0,0 +1,129 @@ +// requirements +//////////////////////////////////////////////////////////////////////////////// + +var mysql = require('mysql'); + +const fs = require('fs'); + + +// 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" +}); + + +// products' (order-)frequecy array +// ----------------------------------------------------------------------------- +var freq_ = []; //////////////////////////////// MAIN OUTPUT OF THE SCRIPT +var prods_ = []; + + +// 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!"); + + // SQL to get needed data + 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\ + LIMIT 60000"; + + // 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)); + + var freq = []; + + obj.forEach( rec => { + var description = rec.product_description; + var fq = rec.FREQuency; + var pid = rec.eys_code; + + prods_.push({ + id: pid, + w: description + }); + + freq_.push({ + fq: fq, + id: pid + }); + }); // main proccessing finished; + +} + + +// save proccess +// ----------------------------------------------------------------------------- +function save_keywords() { + let fqStr = JSON.stringify(freq_); + + fs.writeFileSync("../results/freq.json", fqStr, 'utf8', (err) => { + if (err) { + console.log("An error occured while writing keywords.json"); + return console.log(err); + } + console.log("JSON file has been saved."); + }); + + + let prodStr = JSON.stringify(prods_); + + fs.writeFileSync("../results/prods.json", prodStr, 'utf8', (err) => { + if (err) { + console.log("An error occured while writing keywords.json"); + return console.log(err); + } + console.log("JSON file has been saved."); + }); +} + diff --git a/javascript/keygen.js b/javascript/keygen.js index cacd317..57b90ee 100644 --- a/javascript/keygen.js +++ b/javascript/keygen.js @@ -6,6 +6,7 @@ const fs = require('fs'); const os = require('os'); const fetch = require('node-fetch'); +const { ECDH } = require('crypto'); // preloaded data @@ -221,6 +222,27 @@ var con = mysql.createConnection({ */ +// read frequency data + +let rawdata = fs.readFileSync('../results/freq.json'); +let fr_ = JSON.parse(rawdata); + + +/** freq + * + * @param id (int): product's eys_code + * @return frequency of product + */ +function freq_of(id) { + fr_.forEach( rec => { + if (rec.id == id) return rec.fq + }); + + return 0; +} + + + let url = "https://emarket-laravel-dlqjpfxz5q-oa.a.run.app/api/v1/productsSearch"; let settings = { method: "Get" }; @@ -235,6 +257,16 @@ fetch(url, settings) var linkeys = do_proccess(json.data); save_local(kwlinks_); + + // check + // echo 100 most frequent + let i = 0; + kwlinks_.forEach(rec => { + if (i<100) { + console.log(i, JSON.stringify(rec.w)) + } + i++; + }) }); @@ -342,8 +374,8 @@ function do_proccess(obj) { if (rec.img != 0) { var description = preproccess_text(rec.txt); - var fq = 1; var pid = rec.id; + var fq = freq_of(pid); var keys = []; var words = description.split(' '); diff --git a/javascript/node-sql.js b/javascript/node-sql.js deleted file mode 100644 index 8db56c4..0000000 --- a/javascript/node-sql.js +++ /dev/null @@ -1,490 +0,0 @@ -// 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; -} -- cgit v1.2.3