diff options
26 files changed, 6267 insertions, 871 deletions
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 @@ +<!DOCTYPE html> +<html lang="en"> + <head> + <meta charset="utf-8"> + </head> + <body> + <div id="result"></div> + </body> + <script> + + +function intersect(arr1, arr2) { + var result = arr1.filter(function(n) { return arr2.indexOf(n) !== -1; }); + return result; +} + +var a = [] +var b = [] +var k = [] +var l = [] +var m = [] + +var minitems = 75 +var maxitems = 150; +var maxnumber = 500; + +/* --- +var a = [2, 4, 6, 8, 10, 12, 14]; +var b = [3, 6, 9, 12, 15]; +console.log( intersect(a,b) ); --- */ + +start = new Date(); + +for(j=0 ; j < 10000; j++) { + a = []; + b = []; + for (i=0 ; i< (Math.floor(Math.random() * (maxitems-minitems)) + minitems) ; i++ ) { + x = Math.floor(Math.random() * maxnumber+1); + if (!a.includes(x)) + a.push(x); + } + + for (i=0 ; i< (Math.floor(Math.random() * (maxitems-minitems)) + minitems) ; i++ ) { + x = Math.floor(Math.random() * maxnumber+1); + if (!b.includes(x)) + b.push(x); + } + // m = intersect(a, b); + m = a.filter(value => b.includes(value)); +} +console.log(m); + +end = new Date(); + +console.log(start, end, end-start); +document.getElementById("result").innerHTML = (end-start) +"ms"; + + + + </script> +</html>
\ 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 @@ +<!DOCTYPE html> +<html lang="en"> + <head> + <meta charset="utf-8"> + </head> + <body> + <div id="result"></div> + </body> + + + <script> +// arrays for kb-format (utf/EL-Gr to ascii translation) +var ORiGiNal = 'ςερτυθιοπασδφγηξκλζχψωβνμΕΡΤΥΘΙΟΠΑΣΔΦΓΗΞΚΛΖΧΨΩΒΝΜάέήίόύώϊϋΆΈΉΊΌΎΏΪΫQWERTYUIOPASDFGHJKLZXCVBNMqwertyuiopasdfghjklzxcvbnm0123456789-'.split(''); +var kbKeyZed = 'sertyuiopasdfghjklzxcvbnmertyuiopasdfghjklzxcvbnmaehioyviyaehioyviyqwertyuiopasdfghjklzxcvbnmqwertyuiopasdfghjklzxcvbnm0123456789-'.split(''); +const map = new Map(); +for (i = 0 ; i < ORiGiNal.length; i++) map.set(ORiGiNal[i], kbKeyZed[i]); + +function kb_trans(str) { + str = str.replace('\'',''); + var out = '' + for (var i=0 ; i< str.length; i++) { out += map.get(str[i]); }; + // for (ch of str) { out += map.get(ch); }; + return out; +} + +/* --- +function kb_trans(s) { + var charArr = s.split('') + var i + var out = '' + charArr.forEach( el => { + i = 0 + exist = -1 + ORiGiNal.forEach( ori => { + if (ori == el) { + exist = i; + } + i++; + }) + out += (exist == -1) ? el : kbKeyZed[exist]; + }); + out = out.replace('\'',''); + out = out.replace('-',''); + return out; +} +--- */ + +list = [ "καλαμπόκι-διαβητικών", "cocacola-zero", "γιουβαρλάκια", "WELCOME", "σφενδόνα", "σκλαβενίτης", 'bonora', 'kris-κρις-παπαδοπούλου', "τηλεφώνημα", "χωρίς-αλάτι" ]; +/* --- +start = new Date(); +for (i=0 ; i<1000000 ; i++) { + tmp = kb_trans(list[i%10]); +} +end = new Date(); +console.log(start, end, end-start); +document.getElementById("result").innerHTML = (end-start) +"ms"; +--- */ + + +start = new Date(); +for (i=0; i< 100000000 ; i++ ) { a = 0; } +t0 = new Date(); +for (i=0; i< 100000000 ; i += 1 ) { a = 0; } +end = new Date(); +console.log('++ :', t0-start); +console.log('+= :', end-t0); + + +someObj = [ + { w: 'alfa', l: [ 1, 2, 3, 4, 5, 6, 7 ] }, + { w: 'beta', l: [ 5, 4, 3, 2, 1] }, + { w: 'pi', l: [ 3, 1, 4, 1, 5, 1, 9 ] } +] + +function get_me(a, b) { + var _track = [] + someObj.forEach( it => { + _track.push(it.w); + + if (it.w == a) { + + it.l.forEach( i => { + _track.push(i.toString()); + if (i == b) { + _track.push('done!'); + return true; + } + + + }); + return true; + } + + }); + return _track; +} + + +function find_me(a, b) { + var _track = [] + someObj.find( it => { + _track.push(it.w); + + if (it.w == a) { + + it.l.forEach( i => { + _track.push(i.toString()); + if (i == b) { + _track.push('done!'); + return true; + } + + + }); + return true; + } + + }); + return _track; +} +get_me('beta',3); + + + </script> + +</html>
\ 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 @@ -<!DOCTYPE html> -<html lang="en"> - <head> - <meta charset="utf-8"> - - <style> -body { font-family: 'Cantarell', Helvetica, Arial, sans-serif; } - -.twitter-typeahead { width: 87% ;} -.typeahead, .tt-query, .tt-hint { - width: 100%; height: 30px; - padding: 8px 12px; outline: none; - font-size: 20px; line-height: 30px; - border: 2px solid #ccc; border-radius: 8px; - -webkit-border-radius: 8px; - -moz-border-radius: 8px; -} -.tt-menu { - width: 100%; margin: 12px 0; padding: 8px 0; - background-color: #fff; - border: 1px solid #ccc; border-radius: 8px; - -webkit-border-radius: 8px; - -moz-border-radius: 8px; - -webkit-box-shadow: 0 5px 10px rgba(0,0,0,.2); - -moz-box-shadow: 0 5px 10px rgba(0,0,0,.2); - box-shadow: 0 5px 10px rgba(0,0,0,.2); -} -.tt-suggestion { padding: 3px 20px; line-height: 24px; font-size: 18px; } -.tt-suggestion span { padding-left: 16px; font-size: 14px; color: #777; float: right; } -.tt-cursor { background: #ddd; } -.tt-highlight { font-weight: normal; color: #777; } - -#selections { width: 87%; padding-top: 40px; } -#selections div { padding: 4px 40px; line-height: 24px; font-size: 18px; color: #666; } -#selections div span { padding-left: 16px; font-size: 14px; color: #999; float: right; } - </style> - - <!-- js labraries --> - <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script> - <script src="https://cdnjs.cloudflare.com/ajax/libs/corejs-typeahead/1.2.1/bloodhound.min.js"></script> - <script src="https://cdnjs.cloudflare.com/ajax/libs/corejs-typeahead/1.2.1/typeahead.jquery.min.js"></script> - </head> - <body> - - <div id="the-basics"> - <input class="typeahead" id="tagsInput" type="text" placeholder="try me!"> - </div> - - <div id="selections"> - </div> - - </body> - <script> - -// arrays for kb-format (utf/EL-Gr to ascii translation) -var ORiGiN = 'ςερτυθιοπασδφγηξκλζχψωβνμΕΡΤΥΘΙΟΠΑΣΔΦΓΗΞΚΛΖΧΨΩΒΝΜάέήίόύώϊϋΆΈΉΊΌΎΏΪΫQWERTYUIOPASDFGHJKLZXCVBNM'.split(''); -var kbKeyZ = 'sertyuiopasdfghjklzxcvbnmertyuiopasdfghjklzxcvbnmaehioyviyaehioyviyqwertyuiopasdfghjklzxcvbnm'.split(''); - -// convert string to kb-format -function kb_trans(s) { - var charArr = s.split('') - var i - var out = '' - charArr.forEach( el => { - i = 0 - exist = -1 - ORiGiN.forEach( ori => { - if (ori == el) { - exist = i; - } - i++; - }) - out += (exist == -1) ? el : kbKeyZ[exist]; - }); - return out; -} - -// public data objects -var products; -var everyProduct; - -function loadData() { - const xhttp = new XMLHttpRequest(); - xhttp.onload = function() { - products = JSON.parse(this.responseText); - } - xhttp.open("GET", "results/keywords-v3.json"); - xhttp.send(); -} -loadData(); - -function loadData2() { - const xhttp = new XMLHttpRequest(); - xhttp.onload = function() { - everyProduct = JSON.parse(this.responseText); - } - xhttp.open("GET", "results/products.json"); - xhttp.send(); -} -loadData2(); - -// bigram fuzzy match -// --- credit: https://dirask.com/posts/JavaScript-check-words-similarity-fuzzy-compare-with-bigrams-paola1 -const createBigram = word => { - const input = word.toLowerCase(); - const vector = []; - for (let i = 0; i < input.length; ++i) { - vector.push(input.slice(i, i + 2)); - } - return vector; -}; -const checkSimilarity = (a, b) => { - if (a.length > 0 && b.length > 0) { - const aBigram = createBigram(a); - const bBigram = createBigram(b); - let hits = 0; - for (let x = 0; x < aBigram.length; ++x) { - for (let y = 0; y < bBigram.length; ++y) { - if (aBigram[x] === bBigram[y]) { - hits += 1; - } - } - } - if (hits > 0) { - const union = aBigram.length + bBigram.length; - return (2.0 * hits) / union; - } - } - return 0; -}; -var bi_1st = .6; // bigram minimum match score for 1st word -var bi_2nd = .8; // bigram minimum match score for 2nd word - -// on document ready code ///////////////////////////////////////////////////// -$(document).ready(function() { - - // suggestions engine //////////////////////////////////////////////////// - // --- - function suggestions_engine(qOrig) { - var results = []; // suggestions to respond - var proList = []; // list of products (for all suggestions) - var commonL = []; // list of common products (for multiple suggestions) - var possibleNext = []; // list of possible next suggestions - - var root, last; - - // clean q(uery) string from symbols and multiple spaces - var q = qOrig.replace('+',' ').replace('.',' ') - .replace(' ',' ') - .replace(' ',' '); - - var qAr = q.split(' '); // split to words - - if (qAr.length == 1) { // suggest 1st word //////////////////////// - var kbq = kb_trans(q) - // regex match all possible suggestions; (in kb-format) - substrRegex = new RegExp( kbq, 'i'); // match q anywhere - products.forEach( it => { - if ( (substrRegex.test(it.kb)) - || (checkSimilarity(it.kb, kbq) > bi_1st) ) { - results.push(it); - } - }); - } - - if (qAr.length == 2) { // suggest 2nd word /////////////////////////// - root = qAr[0].trim(); - kbroot = kb_trans(root); - - substrRegex = new RegExp( kb_trans(qAr[1]), 'i'); - - products.forEach( it => { // loop through suggestions - if (it.kb == kbroot ) { // match 1st suggestion - it.c.forEach ( wo => { // regex match linked words - if ( (substrRegex.test(wo.kb)) - || (checkSimilarity(wo.kb, kbroot) > bi_2nd) ) { - results.push({ - w: root +' '+ wo.w, - f: 100 - }); - proList = proList.concat(wo.p) - } - }); - } - }); - } - - if (qAr.length > 2) { - root = qAr.shift(); // get out the first item of qAr - last = qAr.pop(); // get out the lase item of qAr - // now qAr includes only the items after root and before last; - // so qAr includes all already selected suggestions (but root) - - var kbqAr = []; // array of selected suggestions in kb-format - qAr.forEach( w => { kbqAr.push(kb_trans(w)); }) - - // kb-translate the root/last keys - kbroot = kb_trans(root); - kblast = kb_trans(last); - - substrRegex = new RegExp( kblast, 'i'); // construct regex for mathing - - products.forEach( it => { - if (it.kb == kbroot ) { // find root - - // calculate list of common items/products (commonL) - // for selected suggestions - // --- - is1stOcc = true; // 1st occurance flag - it.c.forEach ( swo => { - if (kbqAr.includes( swo.kb )) { - // swo is one of the already selected suggestions - // so... - // update the commonL(ist) - if (is1stOcc) { - commonL = swo.p; - is1stOcc = false; - } - else { - // list ot common products - // = intersection of (so-far) commonL and swo.p - commonL = commonL.filter(value => swo.p.includes(value)); - } - } - else { // if swo is not already selected - // then it is a possible next suggestion - possibleNext.push(swo); - } - }); - // console.log('commonL:', commonL) - - possibleNext.forEach( poss => { // for tthe possible next suggestions - // if word matches regex - // and list of word's products has commons with commonL - // then it is a valid next suggestion - if (substrRegex.test(poss.kb)) { - // check intersection of commonL and suggestion's product-lists - tempL = commonL.filter(value => poss.p.includes(value)); - if (tempL.length) { - results.push({ - w: root +' '+ qAr.join(' ') +' '+ poss.w, - f: 100 - }); - // update proList too - proList = proList.concat(tempL) - } - } - }); - } - - }); - } - - - if ((qAr.length != 1) && (proList.length < 13)) { - // get unique product ids - let unique = proList.filter((item, i, ar) => ar.indexOf(item) === i); - // credit: https://stackoverflow.com/questions/11246758/ - - results = []; - unique.forEach( pr => { - everyProduct.forEach( pi => { - if (pi.id == pr) - results.push(pi); - }) - }); - } - return results; - } - - var isuggest = function(qOrig, list) { - var results = suggestions_engine(qOrig); - if (results.length == 0) { - var qAr = qOrig.trim().split(' '); - qAr.pop(); // remove last word - results = suggestions_engine(qAr.join(' ')); - } - list(results); - } - - // setup suggestions search/input control - // --- - const $tagsInput = $('#tagsInput') - $tagsInput.typeahead( - { - hint: true, - highlight: true, - minLength: 1 - }, - { - limit: 12, - name: 'products', - displayKey: 'w', - source: isuggest, - templates: { - suggestion: function(data) { - // console.log(data.w); - if (data.id) - return '<div>'+ data.w + '<span>' + data.id + '</span></div>'; - return '<div>'+ data.w +'</div>'; - } - } - } - ) - .bind("typeahead:selected", function(obj, datum, name) { - console.log(datum); - if (datum.hasOwnProperty('id')) { - // final product selected; do whatever ... - // ex. add to selection list - $('#selections').append('<div>'+ datum.w + '<span>' + datum.id + '</span></div>'); - - // then reset search control - $('.typeahead').typeahead('val','').trigger('blur') - .trigger("query"); - setTimeout(() => { $('.typeahead').focus(); }, 100); - } - else { - $('.typeahead').typeahead('val','').trigger('blur'); - $('.typeahead').typeahead('val', datum.w +' ') - .trigger("query"); - // give some time to the engine to calculate results - // then fire focus again... - setTimeout(() => { $('.typeahead').focus(); }, 100); - } - }) - .bind("typeahead:cursorchange", function(obj, data) { - // console.log(obj, data); - // var dt = new Date(); - // console.log('triggered cursorchange /'+dt); - }); - - - -}); - </script> -</html> 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 @@ -<!DOCTYPE html> -<html lang="en"> - <head> - <meta charset="utf-8"> - - <style> -body { font-family: 'Cantarell', Helvetica, Arial, sans-serif; margin: 2em; } - -.twitter-typeahead { width: 87%; } -.typeahead, .tt-query, .tt-hint { - width: 100%; height: 30px; - padding: 8px 12px; outline: none; - font-size: 20px; line-height: 30px; - border: 2px solid #ccc; border-radius: 8px; - -webkit-border-radius: 8px; - -moz-border-radius: 8px; -} -.tt-menu { - width: 100%; margin: 12px 0; padding: 8px 0; - background-color: #fff; - border: 1px solid #ccc; border-radius: 8px; - -webkit-border-radius: 8px; - -moz-border-radius: 8px; - -webkit-box-shadow: 0 5px 10px rgba(0,0,0,.2); - -moz-box-shadow: 0 5px 10px rgba(0,0,0,.2); - box-shadow: 0 5px 10px rgba(0,0,0,.2); -} -.tt-suggestion { padding: 3px 20px; line-height: 24px; font-size: 18px; } -.tt-suggestion:hover { cursor: pointer; } -.tt-suggestion span { padding-left: 16px; font-size: 14px; color: #777; float: right; } -.tt-cursor { background: #ddd; } -.tt-highlight { font-weight: normal; color: #777; } -.tt-hint { color: #9598; } - -#selections { width: 87%; padding-top: 40px; } -#selections div { padding: 4px 40px; line-height: 24px; font-size: 18px; color: #666; } -#selections div span { padding-left: 16px; font-size: 14px; color: #999; float: right; } -.-info- { font-size: 12px !important; color: #959 !important; line-height: 14px !important; font-family: 'JetBrains Mono NL', Consolas, Monaco, monospace, fixed !important; } -.-info- b { font-weight: 900;} - </style> - - <!-- js labraries --> - <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script> - <script src="https://cdnjs.cloudflare.com/ajax/libs/corejs-typeahead/1.2.1/bloodhound.min.js"></script> - <script src="https://cdnjs.cloudflare.com/ajax/libs/corejs-typeahead/1.2.1/typeahead.jquery.min.js"></script> - </head> - <body> - - <div id="the-basics"> - <input class="typeahead" id="tagsInput" type="text" placeholder="try me!"> - </div> - - <div id="selections"> - </div> - - </body> - <script> - - -// PUBLIC VARIABLES //////////////////////////////////////////////////////////// - -var kwlinks; // keyword links (word-connections) -var products; // all products - -var trackSearch = []; // searching analytics - -// setup options -var sgLimit = 12; // limit suggestions -var bi_1st = .65; // bigram minimum match score for 1st word -var bi_2nd = .85; // bigram minimum match score for 2nd word - - -// SUPLAMENARY FUNCTIONS /////////////////////////////////////////////////////// - -// arrays for kb-format (utf/EL-Gr to ascii translation) -var ORiGiN = 'ςερτυθιοπασδφγηξκλζχψωβνμΕΡΤΥΘΙΟΠΑΣΔΦΓΗΞΚΛΖΧΨΩΒΝΜάέήίόύώϊϋΆΈΉΊΌΎΏΪΫQWERTYUIOPASDFGHJKLZXCVBNM'.split(''); -var kbKeyZ = 'sertyuiopasdfghjklzxcvbnmertyuiopasdfghjklzxcvbnmaehioyviyaehioyviyqwertyuiopasdfghjklzxcvbnm'.split(''); - -// convert string to kb-format -// --- -function kb_trans(s) { - var charArr = s.split('') - var i - var out = '' - charArr.forEach( el => { - i = 0 - exist = -1 - ORiGiN.forEach( ori => { - if (ori == el) { - exist = i; - } - i++; - }) - out += (exist == -1) ? el : kbKeyZ[exist]; - }); - return out; -} - - -function loadData() { - const xhttp = new XMLHttpRequest(); - xhttp.onload = function() { - kwlinks = JSON.parse(this.responseText); - } - xhttp.open("GET", "results/keywords-v3.json"); - xhttp.send(); -} -loadData(); - -function loadData2() { - const xhttp = new XMLHttpRequest(); - xhttp.onload = function() { - products = JSON.parse(this.responseText); - } - xhttp.open("GET", "results/products.json"); - xhttp.send(); -} -loadData2(); - -// bigram fuzzy match -// --- credit: https://dirask.com/posts/JavaScript-check-words-similarity-fuzzy-compare-with-bigrams-paola1 -const createBigram = word => { - const input = word.toLowerCase(); - const vector = []; - for (let i = 0; i < input.length; ++i) { - vector.push(input.slice(i, i + 2)); - } - return vector; -}; -const checkSimilarity = (a, b) => { - if (a.length > 0 && b.length > 0) { - const aBigram = createBigram(a); - const bBigram = createBigram(b); - let hits = 0; - for (let x = 0; x < aBigram.length; ++x) { - for (let y = 0; y < bBigram.length; ++y) { - if (aBigram[x] === bBigram[y]) { - hits += 1; - } - } - } - if (hits > 0) { - const union = aBigram.length + bBigram.length; - return (2.0 * hits) / union; - } - } - return 0; -}; - - - -function echo_tracking() { - var actions = []; - var c; - var chs = 0; // number of characters pressed; - var uis = 0; // number of UI actions used (arrows, enters etc.) - var countingStarted = false; // flag - trackSearch.forEach(e => { - switch(e.v) { - // use of ui actions - case 'ArrowDown' : c = '↓'; uis++; break; - case 'ArrowUp' : c = '↑'; uis++; break; - case 'Enter' : c = '↲ '; uis++; break; - case 'ArrowLeft' : c = '←'; uis++; break; - case 'ArrowRight': c = '→'; uis++; break; - case ' ' : c = '· '; uis++; break; - // ignored keys - case 'Alt' : c = 'Alt'; break; - case 'Control' : c = 'Ctrl'; break; - case 'Escape' : c = 'Esc'; break; - case 'Shift' : c = 'Shft'; break; - case 'Home' : c = 'Home'; break; - case 'End' : c = 'End'; break; - // backspace (user's typing errors) - case 'Backspace' : c = 'BkSp'; break; - case 'Delete' : c = 'Del'; break; - // actual typed characters - default: - if (e.v.length == 1) { - c = '<b><u>'+ e.v +'</u></b>'; - chs++; - } - else { // some non important key; no counter increased - c = e.v; // just record the key - } - } - actions.push(c); - }); - - return actions.join(',') +' (<u>'+ chs +' chs</u>, '+ uis +' uis)'; -} - - -// on document ready code /////////////////////////////////////////////////////// -$(document).ready(function() { - - // suggestions engine ////////////////////////////////////////////////////// - // --- - function suggestions_engine(qOrig) { - var results = []; // suggestions to respond - var proList = []; // list of products (for all suggestions) - var commonL = []; // list of common products (for multiple suggestions) - var possibleNext = []; // list of possible next suggestions - - var root, last; - - // clean q(uery) string from symbols and multiple spaces - var q = qOrig.replace('+',' ').replace('.',' ') - .replace(' ',' ') - .replace(' ',' '); - - var qAr = q.split(' '); // split to words - - if (qAr.length == 1) { // suggest 1st word //////////////////////// - var kbq = kb_trans(q) - // regex match all possible suggestions; (in kb-format) - substrRegex = new RegExp( kbq, 'i'); // match q anywhere - kwlinks.forEach( it => { - if ( (substrRegex.test(it.kb)) - || (checkSimilarity(it.kb, kbq) > bi_1st) ) { - results.push(it); - } - }); - } - - if (qAr.length == 2) { // suggest 2nd word /////////////////////////// - root = qAr[0].trim(); - kbroot = kb_trans(root); - - substrRegex = new RegExp( kb_trans(qAr[1]), 'i'); - - kwlinks.forEach( it => { // loop through suggestions - if (it.kb == kbroot ) { // match 1st suggestion - it.c.forEach ( wo => { // regex match linked words - if ( (substrRegex.test(wo.kb)) - || (checkSimilarity(wo.kb, kbroot) > bi_2nd) ) { - results.push({ - w: root +' '+ wo.w, - f: 100 - }); - proList = proList.concat(wo.p) - } - }); - } - }); - } - - if (qAr.length > 2) { - root = qAr.shift(); // get out the first item of qAr - last = qAr.pop(); // get out the lase item of qAr - // now qAr includes only the items after root and before last; - // so qAr includes all already selected suggestions (but root) - - var kbqAr = []; // array of selected suggestions in kb-format - qAr.forEach( w => { kbqAr.push(kb_trans(w)); }) - - // kb-translate the root/last keys - kbroot = kb_trans(root); - kblast = kb_trans(last); - - substrRegex = new RegExp( kblast, 'i'); // construct regex for mathing - - kwlinks.forEach( it => { - if (it.kb == kbroot ) { // find root - - // calculate list of common items/products (commonL) - // for selected suggestions - // --- - is1stOcc = true; // 1st occurance flag - it.c.forEach ( swo => { - if (kbqAr.includes( swo.kb )) { - // swo is one of the already selected suggestions - // so... - // update the commonL(ist) - if (is1stOcc) { - commonL = swo.p; - is1stOcc = false; - } - else { - // list ot common products - // = intersection of (so-far) commonL and swo.p - commonL = commonL.filter(value => swo.p.includes(value)); - } - } - else { // if swo is not already selected - // then it is a possible next suggestion - possibleNext.push(swo); - } - }); - // console.log('commonL:', commonL) - - possibleNext.forEach( poss => { // for tthe possible next suggestions - // if word matches regex - // and list of word's products has commons with commonL - // then it is a valid next suggestion - if (substrRegex.test(poss.kb)) { - // check intersection of commonL and suggestion's product-lists - tempL = commonL.filter(value => poss.p.includes(value)); - if (tempL.length) { - results.push({ - w: root +' '+ qAr.join(' ') +' '+ poss.w, - f: 100 - }); - // update proList too - proList = proList.concat(tempL) - } - } - }); - } - - }); - } - - - if ( (qAr.length != 1) && (proList.length < (sgLimit +1)) ) { - // get unique product ids - let unique = proList.filter((item, i, ar) => ar.indexOf(item) === i); - // credit: https://stackoverflow.com/questions/11246758/ - - results = []; - unique.forEach( pr => { - products.forEach( pi => { - if (pi.id == pr) - results.push(pi); - }) - }); - } - return results; - } - - // request suggestions procedure - // args... - // qOrig: original query string - // list: artay structure to host results - // --- - var isuggest = function(qOrig, list) { - var results = suggestions_engine(qOrig); - - // if no results... - // request again after removing last (key)word - if (results.length == 0) { - var qAr = qOrig.trim().split(' '); - qAr.pop(); // remove last word - results = suggestions_engine(qAr.join(' ')); - } - - list(results); - } - - // setup suggestions search/input control - // --- - const $tagsInput = $('#tagsInput') - $tagsInput.typeahead( - { - hint: true, - highlight: true, - minLength: 1 - }, - { - limit: sgLimit, - name: 'kwlinks', - displayKey: 'w', - source: isuggest, - templates: { - suggestion: function(data) { - // console.log(data.w); - if (data.id) - return '<div>'+ data.w + '<span>' + data.id + '</span></div>'; - return '<div>'+ data.w +'<span>+</span></div>'; - } - } - } - ) - .bind("typeahead:selected", function(obj, datum, name) { - // console.log(datum); - if (datum.hasOwnProperty('id')) { - // final product selected; do whatever ... - // ex. add to selection list - $('#selections').append('<div>'+ datum.w + '<span>' + datum.id + '</span></div>'); - - // then reset search control - $('.typeahead').typeahead('val','').trigger('blur') - .trigger("query"); - setTimeout(() => { $('.typeahead').focus(); }, 100); - - // finaly save tracking info; - // $('#selections').append('<div class="-info-">'+ JSON.stringify(trackSearch) +'</div>'); - $('#selections').append('<div class="-info-">'+ echo_tracking() +'</div>'); - trackSearch.length = 0; // ... and reset info to be ready for nextsearch - } - else { - $('.typeahead').typeahead('val','').trigger('blur'); - $('.typeahead').typeahead('val', datum.w +' ') - .trigger("query"); - // give some time to the engine to calculate results - // then fire focus again... - setTimeout(() => { $('.typeahead').focus(); }, 100); - } - trackSearch.push({ - e: 'key', - v: 'Enter', - i: $('#tagsInput').val() - }); - }) - .bind("typeahead:cursorchange", function(obj, data) { - // console.log(obj, data); - // var dt = new Date(); - // console.log('triggered cursorchange /'+dt); - }); - - // TRACK user search attempt /////////////////////////////////////////////// - $('.typeahead').on('keyup', function(e) { - trackSearch.push({ - e: 'key', - v: e.key, - i: $('#tagsInput').val() - }); - }); - -}); - </script> -</html> 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 @@ <meta charset="utf-8"> <style> -body { font-family: 'Cantarell', Helvetica, Arial, sans-serif; } +body { font-family: 'Cantarell', Helvetica, Arial, sans-serif; margin: 2em; } -.twitter-typeahead { width: 87% ;} +.twitter-typeahead { width: 87%; } .typeahead, .tt-query, .tt-hint { width: 100%; height: 30px; padding: 8px 12px; outline: none; @@ -26,9 +26,20 @@ body { font-family: 'Cantarell', Helvetica, Arial, sans-serif; } box-shadow: 0 5px 10px rgba(0,0,0,.2); } .tt-suggestion { padding: 3px 20px; line-height: 24px; font-size: 18px; } +.tt-suggestion:hover { cursor: pointer; } .tt-suggestion span { padding-left: 16px; font-size: 14px; color: #777; float: right; } .tt-cursor { background: #ddd; } .tt-highlight { font-weight: normal; color: #777; } +.tt-hint { color: #9598; } + +#selections { width: 87%; padding-top: 40px; } +#selections div { padding: 4px 40px; line-height: 24px; font-size: 18px; color: #666; } +#selections div span { padding-left: 16px; font-size: 14px; color: #999; float: right; } +.-info- { font-size: 12px !important; color: #959 !important; + font-family: 'JetBrains Mono NL', Consolas, Monaco, monospace, fixed !important; + line-height: 14px !important; border-bottom: 1px solid #ddda; +} +.-info- b { font-weight: 900; } </style> <!-- js labraries --> @@ -42,55 +53,73 @@ body { font-family: 'Cantarell', Helvetica, Arial, sans-serif; } <input class="typeahead" id="tagsInput" type="text" placeholder="try me!"> </div> + <div id="selections"> + </div> + </body> <script> -// arrays for kb-format (utf/EL-Gr to ascii translation) -var ORiGiN = 'ςερτυθιοπασδφγηξκλζχψωβνμΕΡΤΥΘΙΟΠΑΣΔΦΓΗΞΚΛΖΧΨΩΒΝΜάέήίόύώϊϋΆΈΉΊΌΎΏΪΫQWERTYUIOPASDFGHJKLZXCVBNM'.split(''); -var kbKeyZ = 'sertyuiopasdfghjklzxcvbnmertyuiopasdfghjklzxcvbnmaehioyviyaehioyviyqwertyuiopasdfghjklzxcvbnm'.split(''); -// convert string to kb-format -function kb_trans(s) { - var charArr = s.split('') - var i +// PUBLIC VARIABLES //////////////////////////////////////////////////////////// + +var _kwlinks; // keyword links (word-connections; imported via ajax-get) +var _products; // all products (imported via ajax-get) + +var trackSearch = []; // searching analytics + +// setup options +var maxResults = 24; // limit suggestions +var blendProds = 4; // minimum final-produncts to blend with next-word suggestions +var allowFuzzy = false; // enable|disable fuzzy search +var bi1stScore = .65; // bigram minimum match score for 1st word +var bi2ndScore = .85; // bigram minimum match score for 2nd word + + +var ignoredKeys_kb = []; // keywords to ignore (in kb-format) +['μας με σε για του της των από στο στον &'].split(' ').forEach(w => { ignoredKbs.push(kb_trans(w)); }); + + +// SUPLAMENARY FUNCTIONS /////////////////////////////////////////////////////// + +// arrays for kb-format (utf/EL-Gr to ascii translation) +var ORiGiNal = 'ςερτυθιοπασδφγηξκλζχψωβνμΕΡΤΥΘΙΟΠΑΣΔΦΓΗΞΚΛΖΧΨΩΒΝΜάέήίόύώϊϋΆΈΉΊΌΎΏΪΫQWERTYUIOPASDFGHJKLZXCVBNMqwertyuiopasdfghjklzxcvbnm0123456789-'.split(''); +var kbKeyZed = 'sertyuiopasdfghjklzxcvbnmertyuiopasdfghjklzxcvbnmaehioyviyaehioyviyqwertyuiopasdfghjklzxcvbnmqwertyuiopasdfghjklzxcvbnm0123456789-'.split(''); +const map = new Map(); +for (var i=0; i<ORiGiNal.length; i++) map.set(ORiGiNal[i], kbKeyZed[i]); +// function kb_trans +// translates string to keyboard-latin keys; +// [map]'s implementation is 40x faster than [for]'s +function kb_trans(str) { + str = str.replace('\'',''); var out = '' - charArr.forEach( el => { - i = 0 - exist = -1 - ORiGiN.forEach( ori => { - if (ori == el) { - exist = i; - } - i++; - }) - out += (exist == -1) ? el : kbKeyZ[exist]; - }); + for (var i=0 ; i< str.length; i++) out += map.get(str[i]); return out; } -// public data objects -var products; -var everyProduct; - -function loadData() { - const xhttp = new XMLHttpRequest(); - xhttp.onload = function() { - products = JSON.parse(this.responseText); - } - xhttp.open("GET", "results/keywords-v3.json"); - xhttp.send(); -} -loadData(); - -function loadData2() { - const xhttp = new XMLHttpRequest(); - xhttp.onload = function() { - everyProduct = JSON.parse(this.responseText); - } - xhttp.open("GET", "results/products.json"); - xhttp.send(); + + +// pure JS ajax GET request; return data as JSON +// no fancy things like UTF8; if needed use base64 +// --- +function ajax_get(url, callback) { + var xmlhttp = new XMLHttpRequest(); + xmlhttp.onreadystatechange = function() { + if (xmlhttp.readyState == 4 && xmlhttp.status == 200) { + + try { + var data = JSON.parse(xmlhttp.responseText); + } catch(err) { + console.log(err.message + " in " + xmlhttp.responseText); + return; + } + callback(data); + } + }; + + xmlhttp.open("GET", url, true); + xmlhttp.send(); } -loadData2(); + // bigram fuzzy match // --- credit: https://dirask.com/posts/JavaScript-check-words-similarity-fuzzy-compare-with-bigrams-paola1 @@ -98,11 +127,13 @@ const createBigram = word => { const input = word.toLowerCase(); const vector = []; for (let i = 0; i < input.length; ++i) { - vector.push(input.slice(i, i + 2)); + vector.push(input.slice(i, i + 2)); } return vector; }; const checkSimilarity = (a, b) => { + if (!allowFuzzy) return false; + if (a.length > 0 && b.length > 0) { const aBigram = createBigram(a); const bBigram = createBigram(b); @@ -112,27 +143,110 @@ const checkSimilarity = (a, b) => { if (aBigram[x] === bBigram[y]) { hits += 1; } - } + } } if (hits > 0) { - const union = aBigram.length + bBigram.length; - return (2.0 * hits) / union; + const union = aBigram.length + bBigram.length; + return (2.0 * hits) / union; } } return 0; }; -var biMMS = .6; // bigram minimum match score -// on document ready code ///////////////////////////////////////////////////// + +// compact print of search-tracking +// --- +function echo_tracking() { + var actions = []; + var c; + var chs = 0; // number of characters pressed; + var uis = 0; // number of UI actions used (arrows, enters etc.) + var countingStarted = false; // flag + trackSearch.forEach(e => { + switch(e.v) { + // use of ui actions + case 'ArrowDown' : c = '↓'; uis++; break; + case 'ArrowUp' : c = '↑'; uis++; break; + case 'Enter' : c = '↲ '; uis++; break; + case 'ArrowLeft' : c = '←'; uis++; break; + case 'ArrowRight': c = '→'; uis++; break; + case ' ' : c = '· '; uis++; break; + // ignored keys + case 'Alt' : c = 'Alt'; break; + case 'Control' : c = 'Ctrl'; break; + case 'Escape' : c = 'Esc'; break; + case 'Shift' : c = 'Shft'; break; + case 'Home' : c = 'Home'; break; + case 'End' : c = 'End'; break; + // backspace (user's typing errors) + case 'Backspace' : c = 'BkSp'; break; + case 'Delete' : c = 'Del'; break; + // actual typed characters + default: + if (e.v.length == 1) { + c = '<b><u>'+ e.v +'</u></b>'; + chs++; + } + else { // some non important key; no counter increased + c = e.v; // just record the key + } + } + actions.push(c); + }); + + return actions.join(',') +' (<u>'+ chs +' chs</u>, '+ uis +' uis)'; +} + + +// workline object to track status of async svents +// --- +var workline = { + + trackerJL : 0, + set jsonLoaded(x) { + this.trackerJL = x; + + // fire event on certain values + if (x == 2) { + console.log('All streams loaded'); + + // code to execute + // ... + + } + }, + get jsonLoaded() { return this.trackerJL; } + +}; + + + +// LOAD DATA /////////////////////////////////////////////////////////////////// + +ajax_get('results/keywords-v3.json', function(data) { + _kwlinks = data; + workline.jsonLoaded++; console.log('keywords loaded'); +}); + +ajax_get('results/products.json', function(data) { + _products = data; + workline.jsonLoaded++; console.log('products loaded') +}); + + + + + +// on document ready code /////////////////////////////////////////////////////// $(document).ready(function() { - // suggestions engine //////////////////////////////////////////////////// + // suggestions engine ////////////////////////////////////////////////////// // --- - var isuggest = function(qOrig, list) { - var results = []; // suggestions to respond - var proList = []; // list of products (for all suggestions) - var commonL = []; // list of common products (for multiple suggestions) - var possibleNext = []; // list of possible next suggestions + function suggestions_engine(qOrig) { + var results = []; // suggestions to respond + var proList = []; // list of products (for all suggestions) + var commonL = []; // list of common products (for multiple suggestions) + var possibleNext = []; // list of possible next suggestions var root, last; @@ -140,34 +254,43 @@ $(document).ready(function() { var q = qOrig.replace('+',' ').replace('.',' ') .replace(' ',' ') .replace(' ',' '); - // split to words - var qAr = q.split(' '); - // console.log(qAr); + + var qAr = q.split(' '); // split to words if (qAr.length == 1) { // suggest 1st word //////////////////////// - var kbq = kb_trans(q) - // regex match all possible suggestions; (in kb-format) - substrRegex = new RegExp( kbq, 'i'); // match q anywhere - products.forEach( it => { - if ( (substrRegex.test(it.kb)) - || (checkSimilarity(it.kb, kbq) > biMMS) ) { - // console.log(it.kb, kbq, checkSimilarity(it.kb, kbq)); + var kbq = kb_trans(q) + + // ### (-) regex match all possible suggestions; (in kb-format) + // ### (-) substrRegex = new RegExp( kbq, 'i'); // match q anywhere + _kwlinks.forEach( it => { + // ### (-) if ( (substrRegex.test(it.kb)) + if ( (it.kb.indexOf(kbq) !== -1) + || (checkSimilarity(it.kb, kbq) > bi1stScore) ) { + // a match found results.push(it); + // also get possible products + // (but only if products are less max-results) + if (proList.length < maxResults + 2) { + it.c.forEach( wo => { proList = proList.concat(wo.p); }); + // keep unique products in the list + proList = proList.filter((item, i, ar) => ar.indexOf(item) === i); + } } }); } - if (qAr.length == 2) { // suggest 2nd word /////////////////////////// + if (qAr.length == 2) { // suggest 2nd word /////////////////////////// root = qAr[0].trim(); kbroot = kb_trans(root); - substrRegex = new RegExp( kb_trans(qAr[1]), 'i'); + // ### (-) substrRegex = new RegExp( kb_trans(qAr[1]), 'i'); - products.forEach( it => { // loop through suggestions - if (it.kb == kbroot ) { // match 1st suggestion - it.c.forEach ( wo => { // regex match linked words - if ( (substrRegex.test(wo.kb)) - || (checkSimilarity(wo.kb, kbroot) > biMMS) ) { + _kwlinks.forEach( it => { // loop through suggestions + if (it.kb == kbroot ) { // match 1st suggestion + it.c.forEach ( wo => { // regex match linked words + // ### (-) if ( (substrRegex.test(wo.kb)) + if ( (wo.kb.indexOf(qAr[1]) !== -1) + || (checkSimilarity(wo.kb, kbroot) > bi2ndScore) ) { results.push({ w: root +' '+ wo.w, f: 100 @@ -180,21 +303,22 @@ $(document).ready(function() { } if (qAr.length > 2) { - root = qAr.shift(); // get out the first item of qAr + root = qAr.shift(); // get out the first item of tempQAr last = qAr.pop(); // get out the lase item of qAr // now qAr includes only the items after root and before last; // so qAr includes all already selected suggestions (but root) + // prepare/cache kb-formats for any key we may need + // --- var kbqAr = []; // array of selected suggestions in kb-format qAr.forEach( w => { kbqAr.push(kb_trans(w)); }) - - // kb-translate the root/last keys + // kb-translate the root/last keys too kbroot = kb_trans(root); kblast = kb_trans(last); - substrRegex = new RegExp( kblast, 'i'); // construct regex for mathing + // ### (-) substrRegex = new RegExp( kblast, 'i'); // construct regex for mathing - products.forEach( it => { + _kwlinks.forEach( it => { if (it.kb == kbroot ) { // find root // calculate list of common items/products (commonL) @@ -202,18 +326,19 @@ $(document).ready(function() { // --- is1stOcc = true; // 1st occurance flag it.c.forEach ( swo => { - if (kbqAr.includes( swo.kb )) { + if (kbqAr.indexOf( swo.kb ) !== -1) { // swo is one of the already selected suggestions // so... // update the commonL(ist) if (is1stOcc) { - commonL = swo.p; + commonL = swo.p; // init list (on 1st occurance) is1stOcc = false; } else { - // list ot common products + // caclulate list of common products // = intersection of (so-far) commonL and swo.p - commonL = commonL.filter(value => swo.p.includes(value)); + // commonL = commonL.filter(value => swo.p.includes(value)); + commonL = commonL.filter(function(n) { return swo.p.indexOf(n) !== -1; }); } } else { // if swo is not already selected @@ -221,13 +346,12 @@ $(document).ready(function() { possibleNext.push(swo); } }); - // console.log('commonL:', commonL) possibleNext.forEach( poss => { // for tthe possible next suggestions // if word matches regex // and list of word's products has commons with commonL // then it is a valid next suggestion - if (substrRegex.test(poss.kb)) { + if (poss.kb.indexOf(kblast) !== -1 ) { // check intersection of commonL and suggestion's product-lists tempL = commonL.filter(value => poss.p.includes(value)); if (tempL.length) { @@ -245,55 +369,97 @@ $(document).ready(function() { }); } + // calculate unique products + let unique = proList.filter((item, i, ar) => ar.indexOf(item) === i); + // credit: https://stackoverflow.com/questions/11246758/ - if ((qAr.length != 1) && (proList.length < 13)) { - // get unique product ids - let unique = proList.filter((item, i, ar) => ar.indexOf(item) === i); - // credit: https://stackoverflow.com/questions/11246758/ - + // console.log(q, qAr, qAr.length, proList.length); + + if (unique.length < (maxResults +1)) { results = []; unique.forEach( pr => { - everyProduct.forEach( pi => { + _products.forEach( pi => { if (pi.id == pr) results.push(pi); }) }); } + return results; + } + + // request suggestions procedure + // args... + // qOrig: original query string + // list: artay structure to host results + // --- + var isuggest = function(qOrig, list) { + var results = suggestions_engine(qOrig); + + // if no results... + // request again after removing last (key)word + if (results.length == 0) { + var qAr = qOrig.trim().split(' '); + qAr.pop(); // remove last word + results = suggestions_engine(qAr.join(' ')); + } + list(results); } - // setup suggestions search/input control - // --- + // setup suggestions search/input control + // --- const $tagsInput = $('#tagsInput') $tagsInput.typeahead( { hint: true, highlight: true, - minLength: 0 + minLength: 1 }, { - limit: 12, - name: 'products', + limit: maxResults, + name: 'kwlinks', displayKey: 'w', source: isuggest, - templates: { - suggestion: function(data) { - console.log(data.w); - if (data.id) - return '<div>'+ data.w + '<span>' + data.id + '</span></div>'; - return '<div>'+ data.w +'</div>'; - } - } + templates: { + suggestion: function(data) { + // console.log(data.w); + if (data.id) + return '<div>'+ data.w + '<span>' + data.id + '</span></div>'; + return '<div>'+ data.w +'<span>+</span></div>'; + } + } } ) .bind("typeahead:selected", function(obj, datum, name) { - $('.typeahead').typeahead('val','').trigger('blur'); - $('.typeahead').typeahead('val', datum.w +' ') + // console.log(datum); + if (datum.hasOwnProperty('id')) { + // final product selected; do whatever ... + // ex. add to selection list + $('#selections').append('<div>'+ datum.w + '<span>' + datum.id + '</span></div>'); + + // then reset search control + $('.typeahead').typeahead('val','').trigger('blur') .trigger("query"); - // give some time to the engine to calculate results - // then fire focus again... - setTimeout(() => { $('.typeahead').focus(); }, 100); + setTimeout(() => { $('.typeahead').focus(); }, 100); + + // finaly save tracking info; + $('#selections').append('<div class="-info-">'+ echo_tracking() +'</div>'); + trackSearch.length = 0; // ... and reset info to be ready for nextsearch + } + else { + $('.typeahead').typeahead('val','').trigger('blur'); + $('.typeahead').typeahead('val', datum.w +' ') + .trigger("query"); + // give some time to the engine to calculate results + // then fire focus again... + setTimeout(() => { $('.typeahead').focus(); }, 100); + } + trackSearch.push({ + e: 'key', + v: 'Enter', + i: $('#tagsInput').val() + }); }) .bind("typeahead:cursorchange", function(obj, data) { // console.log(obj, data); @@ -301,8 +467,27 @@ $(document).ready(function() { // console.log('triggered cursorchange /'+dt); }); + // TRACK user search attempt /////////////////////////////////////////////// + $('.typeahead').on('keyup', function(e) { + trackSearch.push({ + e: 'key', + v: e.key, + i: $('#tagsInput').val() + }); + }); + +}); + + + + +// also check (for suearching benchmarks) +// --- +// https://www.measurethat.net/Benchmarks/Show/13675/0/regextest-vs-stringincludes-vs-stringmatch +// https://stackoverflow.com/questions/5296268/fastest-way-to-check-a-string-contain-another-substring-in-javascript +// https://stackoverflow.com/questions/40387106/string-startwith-vs-regex +// https://www.measurethat.net/Benchmarks/Show/4797/1/js-regex-vs-startswith-vs-indexof -}); - </script> -</html> + </script> +</html>
\ 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 @@ +<!DOCTYPE html> +<html lang="en"> + <head> + <meta charset="utf-8"> + + <style> +body { font-family: 'Cantarell', Helvetica, Arial, sans-serif; margin: 2em; } + +.twitter-typeahead { width: 87%; } +.typeahead, .tt-query, .tt-hint { + width: 100%; height: 30px; + padding: 8px 12px; outline: none; + font-size: 20px; line-height: 30px; + border: 2px solid #ccc; border-radius: 8px; + -webkit-border-radius: 8px; + -moz-border-radius: 8px; +} +.tt-menu { + width: 100%; margin: 12px 0; padding: 8px 0; + background-color: #fff; + border: 1px solid #ccc; border-radius: 8px; + -webkit-border-radius: 8px; + -moz-border-radius: 8px; + -webkit-box-shadow: 0 5px 10px rgba(0,0,0,.2); + -moz-box-shadow: 0 5px 10px rgba(0,0,0,.2); + box-shadow: 0 5px 10px rgba(0,0,0,.2); +} +.tt-suggestion { padding: 3px 20px; line-height: 24px; font-size: 18px; } +.tt-suggestion:hover { cursor: pointer; } +.tt-suggestion span { padding-left: 16px; font-size: 14px; color: #777; float: right; } +.tt-cursor { background: #ddd; } +.tt-highlight { font-weight: normal; color: #777; } +.tt-hint { color: #9598; } + +#selections { width: 87%; padding-top: 40px; } +#selections div { padding: 4px 40px; line-height: 24px; font-size: 18px; color: #666; } +#selections div span { padding-left: 16px; font-size: 14px; color: #999; float: right; } +.-info- { font-size: 12px !important; color: #959 !important; + font-family: 'JetBrains Mono NL', Consolas, Monaco, monospace, fixed !important; + line-height: 14px !important; border-bottom: 1px solid #ddda; +} +.-info- b { font-weight: 900; } + </style> + + <!-- js labraries --> + <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script> + <script src="https://cdnjs.cloudflare.com/ajax/libs/corejs-typeahead/1.2.1/bloodhound.min.js"></script> + <script src="https://cdnjs.cloudflare.com/ajax/libs/corejs-typeahead/1.2.1/typeahead.jquery.min.js"></script> + </head> + <body> + + <div id="the-basics"> + <input class="typeahead" id="tagsInput" type="text" placeholder="try me!"> + </div> + + <div id="selections"> + </div> + + </body> + <script> + + +// PUBLIC VARIABLES //////////////////////////////////////////////////////////// + +var _kwlinks; // keyword links (word-connections; imported via ajax-get) +var _products; // all products (imported via ajax-get) + +var trackSearch = []; // searching analytics + +// setup options +var _maxResults = 24; // limit suggestions +var _blendProds = 4; // minimum final-produncts to blend with next-word suggestions +var _timeout_ms = 100; // time (in ms) for the search engine to find matches (before rendering) +var _allowFuzzy = true; // enable|disable fuzzy search +var _fuzzyLimit = .5; // minimum bigram score for being considered a match +var _Ngram_base = 2 // number of N in Ngram spliting algorithm + + + +// SUPPLEMENTARY FUNCTIONS /////////////////////////////////////////////////////// + +// arrays for kb-format (utf/EL-Gr to ascii translation) +var ORiGiNal = 'ςερτυθιοπασδφγηξκλζχψωβνμΕΡΤΥΘΙΟΠΑΣΔΦΓΗΞΚΛΖΧΨΩΒΝΜάέήίόύώϊΐϋΆΈΉΊΌΎΏΪΫQWERTYUIOPASDFGHJKLZXCVBNMqwertyuiopasdfghjklzxcvbnm0123456789- '.split(''); +var kbKeyZed = 'sertyuiopasdfghjklzxcvbnmertyuiopasdfghjklzxcvbnmaehioyviiyaehioyviyqwertyuiopasdfghjklzxcvbnmqwertyuiopasdfghjklzxcvbnm0123456789- '.split(''); +const map = new Map(); +for (var i=0; i<ORiGiNal.length; i++) map.set(ORiGiNal[i], kbKeyZed[i]); +// function kb_trans +// translates string to keyboard-latin keys; +// [map]'s implementation is 40x faster than [for]'s +function kb_trans(str) { + str = str.replace('\'',''); + var out = '' + for (var i=0 ; i< str.length; i++) out += map.get(str[i]); + return out; +} + +var ignoredKeys_kb = []; // keywords to ignore (in kb-format) +'μας με σε για του της των από στο στον &'.split(' ').forEach(w => { ignoredKeys_kb.push(kb_trans(w)); }); + + + +// pure JS ajax GET request; return data as JSON +// no fancy things like UTF8; if needed use base64 +// --- +function ajax_get(url, callback) { + var xmlhttp = new XMLHttpRequest(); + xmlhttp.onreadystatechange = function() { + if (xmlhttp.readyState == 4 && xmlhttp.status == 200) { + + try { + var data = JSON.parse(xmlhttp.responseText); + } catch(err) { + console.log(err.message + " in " + xmlhttp.responseText); + return; + } + callback(data); + } + }; + + xmlhttp.open("GET", url, true); + xmlhttp.send(); +} + + +// Ngram fuzzy match +// --- +const createNgram = (word, n) => { // Ngram creation + if (word.length <3) return word; + const vector = []; + for (let i = 0; i < word.length-n+1; ++i) { + vector.push(word.slice(i, i + n)); + } + return vector; +}; +const checkSimilarity = (a, b) => { // Ngram match score + if (!_allowFuzzy) return 0; + + if (a.length > 0 && b.length > 0) { + const aNgram = createNgram(a, _Ngram_base); + const bNgram = createNgram(b, _Ngram_base); + let hits = 0; + for (let x = 0; x < aNgram.length; ++x) { + for (let y = 0; y < bNgram.length; ++y) { + if (aNgram[x] === bNgram[y]) { + hits += 1; + } + } + } + if (hits > 0) { + const union = aNgram.length + bNgram.length; + return (2.0 * hits) / union; + } + } + return 0; +}; + + +// Search Endine's match functions --------------------------------------------- + + +// check if a searching string -> query (string/latin in kb-format) +// matches an item of the array of synonyms -> chkArr (array of utf-8/strings) +// + option to use fyzzy (bigram) match +// *** return: +// matched string (utf-8) --or-- false (if not matched) +// --- +function check_match( query, chkArr, fuzzy = false ) { + var result = ''; + var found = false; + chkArr.forEach( chk => { + if (!found) { + chk_kb = kb_trans(chk); + if ( (chk_kb.indexOf( query ) !== -1) || (fuzzy && (checkSimilarity(chk_kb, query) > _fuzzyLimit)) ) { + found = true; + result = chk; + } + } + }); + return found ? result : false; +} + + +// check if a searching string -> query (string/latin in kb-format) +// matches exactly an item of the array of synonyms -> chkArr (array of utf-8/strings) +// *** return: true|false +// --- +function is_exact_match( query, chkArr ) { + found = false; + chkArr.forEach( w => { if (kb_trans(w) == query) found = true }); + return found; +} + + +// check if at-least-ONE item from an array of query-words -> qArr (array of string/latin in kb-format) +// matches any item of an array of synonyms -> chkArr (array of utf-8/strings) +// *** return: true|false +// --- +function match_one( qArr, chkArr ) { + found = false; + qArr.forEach( query => { + chkArr.forEach( w => { if (kb_trans(w) == query) found = true }); + }); + return found; +} + + +// compact print of search-tracking +// --- +function echo_tracking() { + var actions = []; + var c; + var chs = 0; // number of characters pressed; + var uis = 0; // number of UI actions used (arrows, enters etc.) + var countingStarted = false; // flag + trackSearch.forEach(e => { + switch(e.v) { + // use of ui actions + case 'ArrowDown' : c = '↓'; uis++; break; + case 'ArrowUp' : c = '↑'; uis++; break; + case 'Enter' : c = '↲ '; uis++; break; + case 'ArrowLeft' : c = '←'; uis++; break; + case 'ArrowRight': c = '→'; uis++; break; + case ' ' : c = '· '; uis++; break; + // ignored keys + case 'Alt' : c = 'Alt'; break; + case 'Control' : c = 'Ctrl'; break; + case 'Escape' : c = 'Esc'; break; + case 'Shift' : c = 'Shft'; break; + case 'Home' : c = 'Home'; break; + case 'End' : c = 'End'; break; + // backspace (user's typing errors) + case 'Backspace' : c = 'BkSp'; break; + case 'Delete' : c = 'Del'; break; + // actual typed characters + default: + if (e.v.length == 1) { + c = '<b><u>'+ e.v +'</u></b>'; + chs++; + } + else { // some non important key; no counter increased + c = e.v; // just record the key + } + } + actions.push(c); + }); + + return actions.join(',') +' (<u>'+ chs +' chs</u>, '+ uis +' uis)'; +} + + +// workline object to track status of async svents +// --- +var workline = { + + trackerJL : 0, + set jsonLoaded(x) { + this.trackerJL = x; + + // fire event on certain values + if (x == 2) { + console.log('All streams loaded'); + + // code to execute + // ... + + } + }, + get jsonLoaded() { return this.trackerJL; } + +}; + + + +// LOAD DATA /////////////////////////////////////////////////////////////////// + +ajax_get('results/keywords.json', function(data) { + _kwlinks = data; + workline.jsonLoaded++; console.log('keywords loaded'); +}); + +ajax_get('results/products.json', function(data) { + _products = data; + workline.jsonLoaded++; console.log('products loaded') +}); + + +// on document ready code /////////////////////////////////////////////////////// +$(document).ready(function() { + + // suggestions engine ////////////////////////////////////////////////////// + // --- + function suggestions_engine(qOrig) { + var results = []; // suggestions to respond + var proList = []; // list of products (for all suggestions) + var commonL = []; // list of common products (for multiple suggestions) + var possibleNext = []; // list of possible next suggestions + + var root, last; + + // clean q(uery) string from symbols and multiple spaces + var q = qOrig.replace('+',' ').replace('.',' ') + .replace(' ',' ') + .replace(' ',' '); + + var qAr = q.split(' '); // split to words + + if (qAr.length == 1) { // suggest 1st word //////////////////////// + var kbq = kb_trans(q) + + // loop through root-words + // ... to match all possible suggestions; + _kwlinks.forEach( it => { + chk = check_match(kbq, it.w, true); + + if ( chk != false ) { + results.push({ w: chk }); + + // ... also keep possible products in a list + it.c.forEach( wo => { // for every Word-Link-Node + if (proList.length < _maxResults + 1) { + // concatenate this-suggestion's product sub-list (wo.p) + // to all-suggestions posiible products-list (proList) + proList = proList.concat(wo.p); + // keep unique products in the products-list + proList = proList.filter((item, i, ar) => ar.indexOf(item) === i); + } + }); + } + + }); + } + + if (qAr.length == 2) { // suggest 2nd word /////////////////////////// + root = qAr[0]; + last = qAr[1]; + kbroot = kb_trans(root); + kblast = kb_trans(last); + + _kwlinks.forEach( it => { // locate the ... + if (is_exact_match(kbroot, it.w)) { // exact match of root-word + + it.c.forEach ( wo => { // loop the word-links ... + chk = check_match(kblast, wo.w, true); + if ( chk !== false ) { // if a match is found + results.push({ // keep suggestion + w: root +' '+ chk, + f: wo.f + }); + if (proList.length < _maxResults + 1) { // plus... + proList = proList.concat(wo.p); // keep products-list + proList = proList.filter((item, i, ar) => ar.indexOf(item) === i); + } + } + }); + + } + }); + } + + if (qAr.length > 2) { // suggest N-th word (N>2) ///////////////// + root = qAr.shift(); // isolate first item of qAr + last = qAr.pop(); // isolate last item too + // now qAr includes only the items after root and before last; + // so qAr includes all already selected suggestions (but root) + + // prepare/cache kb-formated string for any key we're going to use + var kb_qAr = []; // array of selected suggestions in kb-format + qAr.forEach( w => { kb_qAr.push(kb_trans(w)); }) + kbroot = kb_trans(root); + kblast = kb_trans(last); + + _kwlinks.forEach( it => { + if (is_exact_match(kbroot, it.w)) { // locate root-word + + // calculate list of common items/products (commonL) + // for selected suggestions + is1stOcc = true; // 1st occurance flag + + it.c.forEach ( swo => { + if (match_one(kb_qAr, swo.w)) { + // swo is one of the already selected suggestions + // so... update the common-(products)-L(ist) + if (is1stOcc) { + commonL = swo.p; // init list (on 1st occurance) + is1stOcc = false; + } + else { + // caclulate list of common products + // = intersection of (so-far) commonL and swo.p + // commonL = commonL.filter(value => swo.p.includes(value)); + commonL = commonL.filter(function(n) { return swo.p.indexOf(n) !== -1; }); + } + } + else { // if swo is not already selected + // then This is a possible NEXT suggestion + possibleNext.push(swo); + } + }); + + // Now that we have all the possible next suggestions + // we'll match them with the last word of the query + + possibleNext.forEach( poss => { // for tthe possible next suggestions + // if last-word-of-query matches possible word(s) + // and list of word's products has commons with commonL + // then it is a valid next suggestion + chk = check_match(kblast, poss.w, true); + if (chk !== false) { + // check intersection of commonL and suggestion's product-lists + tempL = commonL.filter(value => poss.p.includes(value)); + if (tempL.length > 0) { + results.push({ + w: root +' '+ qAr.join(' ') +' '+ chk, + f: poss.f + }); + // update proList too + proList = proList.concat(tempL); + } + } + }); + // NOTE: test before enable + // return false; // escape from forEach + } + + }); + } + + // calculate unique products + let unique = proList.filter((item, i, ar) => ar.indexOf(item) === i); + // credit: https://stackoverflow.com/questions/11246758/ + + // console.log(q, qAr, qAr.length, proList.length); + + if (unique.length < (_maxResults +1)) { + results = []; + unique.forEach( pr => { + _products.forEach( pi => { + if (pi.id == pr) + results.push(pi); + }) + }); + } + + return results; + } + + // simple, fast search products by numeric code property + // --- + function search_by_code( num ) { + const str = num.toString(); + results = []; + + _products.forEach(p => { + if (results.length > _maxResults) return false; + + if ( (p.bp+'-'+p.bc).indexOf(str) !== -1 ) { + results.push(p) + } + }); + return results; + } + + // suggestions caller (router function) + // arguments: + // * qOrig : original query string + // ** list : callback array structure to host results + // --- + var isuggest = function(qOrig, list) { + var results; + + // if query seems to be some king of 'code/id' + if (qOrig.length>2 && qOrig.match(/^[0-9]+$/) != null) { + results = search_by_code(qOrig); + } + else { + // string match procedure with suggestions engine + results = suggestions_engine(qOrig); + + // depricated: + // if no results request again after removing last (key)word + ////// if (results.length == 0) { + ////// console.log('no results found;', qOrig.split(' ')); + ////// // var qAr = qOrig.trim().split(' '); + ////// // qAr.pop(); // remove last word + ////// // results = suggestions_engine(qAr.join(' ')); + ////// } + } + list(results); + } + + // setup suggestions search/input control + // --- + const $tagsInput = $('#tagsInput') + $tagsInput.typeahead( + { + hint: true, + highlight: true, + minLength: 1 + }, + { + limit: _maxResults, + name: 'kwlinks', + displayKey: 'w', + source: isuggest, + templates: { + suggestion: function(data) { + // console.log(data.w); + if (data.id) + return '<div>'+ data.w + '<span>' + data.bp +'-'+ data.bc + '</span></div>'; + return '<div>'+ data.w +'<span>+</span></div>'; + } + } + } + ) + .bind("typeahead:selected", function(obj, datum, name) { + // console.log(datum); + if (datum.hasOwnProperty('id')) { + // final product selected; do whatever ... + // ex. add to selection list + $('#selections').append('<div>'+ datum.w + '<span>' + datum.id + '</span></div>'); + + // then reset search control + $('.typeahead').typeahead('val','').trigger('blur') + .trigger("query"); + setTimeout(() => { $('.typeahead').focus(); }, _timeout_ms); + + // finaly save tracking info; + $('#selections').append('<div class="-info-">'+ echo_tracking() +'</div>'); + trackSearch.length = 0; // ... and reset info to be ready for nextsearch + } + else { + $('.typeahead').typeahead('val','').trigger('blur'); + $('.typeahead').typeahead('val', datum.w +' ') + .trigger("query"); + // give some time to the engine to calculate results + // then fire focus again... + setTimeout(() => { $('.typeahead').focus(); }, _timeout_ms); + } + trackSearch.push({ + e: 'key', + v: 'Enter', + i: $('#tagsInput').val() + }); + }) + .bind("typeahead:cursorchange", function(obj, data) { + // console.log(obj, data); + // var dt = new Date(); + // console.log('triggered cursorchange /'+dt); + }); + + // TRACK user search attempt /////////////////////////////////////////////// + $('.typeahead').on('keyup', function(e) { + trackSearch.push({ + e: 'key', + v: e.key, + i: $('#tagsInput').val() + }); + }); + +}); + + +// NOTE: +// there is a strong chance that list-intersection and list-concatenation algos +// can be optimized further (for CPU and RAM usage); check the following sources ... +// --- +// https://javascript.plainenglish.io/algorithms-101-includes-vs-indexof-in-javascript-7f1b4af04127 +// https://www.measurethat.net/Benchmarks/Show/8221/0/array-indexof-vs-includes-vs-some +// https://www.measurethat.net/Benchmarks/Show/4223/0/array-concat-vs-spread-operator-vs-push +// HINT: +// --- +// array.some() seems to be much faster than array.includes() and array.indexOf() +// also array.push(list) seems to be an awesome alternative vs concat and Set + + + </script> +</html> + +<!-- + oneliner tests + +totl = 0 +_products.forEach(p => { if (kb_trans(p.w).indexOf(kb_trans("xvris")) !=-1) totl++; }) + +totl = 0 +_products.forEach(p => { if (kb_trans(p.w).indexOf(kb_trans("eisagvghs")) !=-1) totl++; }) + +_products.forEach(p => { if (kb_trans(p.w).indexOf(kb_trans("dvro")) !=-1) totl++; }) +_products.forEach(p => { if (kb_trans(p.w).indexOf(kb_trans("geysh")) !=-1) totl++; }) +_products.forEach(p => { if (kb_trans(p.w).indexOf(kb_trans("xvris prosuhkh zaxarhs")) !=-1) totl++; }) + + +χωρίς +εισαγωγής +δώρο +γεύση +γεύσεις +φέτες +Χωρίς-Γλουτένη +Χωρίς-Ζάχαρη +Χωρίς-Αλάτι +Χωρίς-Λακτόζη +Χωρίς-Συντηρητικά +Χωρίς-Αλκοόλ +Χωρίς-Kαφεϊνη +Χωρίς-Γλυκάνισο +Χωρίς-Ανθρακικό +Υψηλής-Παστερίωσης +Ολες-τις-Χρήσεις +Ολικής-Άλεσης +Ολικής-Aλέσεως +Ολικής +Γαϊδούρας +Γαϊδάρου +Ρούχων +Πιάτων +πλύσεις +Πλυντηρίου +Φύλλων +Γάλακτος +Χρήσης +Τύπου +Ολλανδίας +Απορριμμάτων +Medium +Μαλλιά +Μαλλιών +Γενικής +Plus +Classic +Έκπληξη +Μάνης +Ελάτου +Άγριων +Βοτάνων +Λακωνίας + +--> + +<!-- + +-->
\ 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<ORiGiNal.length; i++) map.set(ORiGiNal[i], kbKeyZed[i]); + + +// synonyms +// ----------------------------------------------------------------------------- +var synonyms = []; // groups of synonyms +var synonym_kbs = []; // cache kb-formats for performance +var synonyms_Originals = [ + 'μπίρα μπύρα μπίρες μπύρες', + 'αυγά αβγά αυγό', + 'σίκαλης σικάλεως', + 'ξηρά ξερά', + 'ρολό ρολλό', + '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', + 'Το-Μάννα Μάννα', + 'Κράνμπερι Κράνμπερις', + 'Κρήτης Κρητικό', + 'Πέννες Πένες', + 'Μακαρόνια Σπαγγέτι Σπαγγετίνι Σπαγγετόνι', + 'Καρτέλλα Καρτέλα Καρτέλλες' +] +synonyms_Originals.forEach( grp => { + 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<ORiGiNal.length; i++) map.set(ORiGiNal[i], kbKeyZed[i]); + + +// synonyms +// ----------------------------------------------------------------------------- +var synonyms = []; // groups of synonyms +var synonym_kbs = []; // cache kb-formats for performance +var synonyms_Originals = [ + 'μπίρα μπύρα μπίρες μπύρες', + 'αυγά αβγά αυγό', + 'σίκαλης σικάλεως', + 'ξηρά ξερά', + 'ρολό ρολλό', + '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', + 'Το-Μάννα Μάννα', + 'Κράνμπερι Κράνμπερις', + 'Κρήτης Κρητικό', + 'Πέννες Πένες', + 'Μακαρόνια Σπαγγέτι Σπαγγετίνι Σπαγγετόνι', + 'Καρτέλλα Καρτέλα Καρτέλλες' +] +synonyms_Originals.forEach( grp => { + 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<ORiGiNal.length; i++) map.set(ORiGiNal[i], kbKeyZed[i]); + + +// synonyms +// ----------------------------------------------------------------------------- +var synonyms = []; // groups of synonyms +var synonym_kbs = []; // cache kb-formats for performance +var synonyms_Originals = [ + 'μπίρα μπύρα μπίρες μπύρες', + 'αυγά αβγά αυγό', + 'σίκαλης σικάλεως', + 'ξηρά ξερά', + 'ρολό ρολλό', + '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', + 'Το-Μάννα Μάννα', + 'Κράνμπερι Κράνμπερις', + 'Κρήτης Κρητικό', + 'Πέννες Πένες', + 'Μακαρόνια Σπαγγέτι Σπαγγετίνι Σπαγγετόνι', + 'Καρτέλλα Καρτέλα Καρτέλλες' +] +synonyms_Originals.forEach( grp => { + 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/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/code-examples.py b/python/code-examples.py index d8d0042..d8d0042 100644 --- a/code-examples.py +++ b/python/code-examples.py 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/products-dictionary.py b/python/products-dictionary.py index 1f35a67..1f35a67 100644 --- a/products-dictionary.py +++ b/python/products-dictionary.py 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/products-dict-v3.py b/python/read-brands.py index 6f37de9..2bbc767 100644 --- a/products-dict-v3.py +++ b/python/read-brands.py @@ -29,11 +29,11 @@ def domeFloat(x) : def cleanText(x) : - removeList = [ ' με ', ' σε ', ' για ', ' του ', ' της ', ' των ', ' από ', ' ΜΕ ', ' ΣΕ ', ' ΓΙΑ ', ' ΑΠΟ ', '&', '.', ',', '!', '(', ')', '[', ']', '\'', '\"' ] + removeOriginals = 'Μας με σε για του της των από ΜΕ ΣΕ ΓΙΑ στο στον Στο από e g h k m n o p s x'.split(' ') - for r in removeList : + for r in removeOriginals : x = x.replace(r, ' ') - + x.replace(' ', ' ') # remove spare spaces x.replace(' ', ' ') x.replace(' ', ' ') @@ -267,4 +267,4 @@ 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) + 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 |