From 88138d708c32feef615521050466d620e5ec6c2c Mon Sep 17 00:00:00 2001 From: Geo Halkiadakis Date: Thu, 23 Mar 2023 15:03:17 +0200 Subject: linkeysearch: 2 step implementation; prepare scripts to run as Google Cloud Funcctions --- javascript/freq.js | 213 ++++++++++++++++++++++++++++++++--------------------- 1 file changed, 131 insertions(+), 82 deletions(-) (limited to 'javascript/freq.js') diff --git a/javascript/freq.js b/javascript/freq.js index 5f740b7..0ac7779 100644 --- a/javascript/freq.js +++ b/javascript/freq.js @@ -1,6 +1,21 @@ -// requirements +/** freq.js + * + * this script extracts products' order-frequency + * ----------------------------------------------------------------------------- + * + * Contents: + * #1 Requirements + * #2 Personalized constants and parametres + * #3 Supporting functions + * #4 Output functions + * #5 Entry-point function main() + */ + +// #1 +// REQUIREMENTS //////////////////////////////////////////////////////////////////////////////// + var mysql = require('mysql'); const fs = require('fs'); @@ -9,16 +24,26 @@ const {Storage} = require('@google-cloud/storage'); // import Google Cloud c -// (setup/personalize) constants + +// #2 +// SETUP PERSONALIZED CONSTANTS AND PARAMETRES //////////////////////////////////////////////////////////////////////////////// + // google-storage parametres // --- -- -- - - - const projectId = 'pythia-251711'; const keyFilename = '../auth/pythia-251711-047e3d5e6608.json'; +// temp file of frequencies (json) +const temp_file = '../results/freq.json'; + + // db connection parametres +// (keep the one that suits your environment; comment out the other) // ----------------------------------------------------------------------------- + +// while on local machine using cloud_sql_proxy var con = mysql.createConnection({ host: "127.0.0.1", user: "pythia_db_user_dev", @@ -26,109 +51,54 @@ var con = mysql.createConnection({ database: "dev_pythia_db" }); +// // while on Google Cloud Functions +// var con = mysql.createConnection({ +// socketPath: "/cloudsql/pythia-251711:europe-west4:pythia-db-eu", +// user: "pythia_services", +// password: process.env.DB_PASSWORD, +// database: "pythia_db" +// }); // MAIN OUTPUT OF THE SCRIPT // arrays that need to be constructed and filled with data // ----------------------------------------------------------------------------- /** array of product frequencies - * array of obj:{ id, fq } + * array of obj: { id: , fq: } * @var id (int): product's eys code * @var fq (int): (order) frerquency */ var freq_ = []; -/** array of products - * array of obj: { id, w } - * - * @var id (int): product's eys_code - * @var w (string): descripion - */ -var prods_ = []; // products array -// connect; -// get records to proccess; -// call main proccess function; -// save dictionary; -// end script; -// ----------------------------------------------------------------------------- -con.connect(function(err) { - // connect; - if (err) throw err; - console.log("Connected!"); - - // SQL to get needed data - var sql = "SELECT count(pl.eys_code) as FREQuency,\ - pl.product_id as product_id,\ - pb.brand_name,\ - pl.barcode, pl.skl_code, pl.eys_code,\ - IF( pd.description IS NOT NULL , pd.description , pl.product_description ) AS product_description,\ - pl.bpcs_code,\ - pd.image_path\ - FROM product_list as pl\ - LEFT JOIN delivery_orders_products AS dop ON dop.product_id = pl.eys_code\ - LEFT JOIN delivery_orders AS do ON dop.order_id = do.id\ - LEFT JOIN product_list AS replacement ON dop.replacement_for = replacement.eys_code\ - LEFT JOIN product_details AS pd ON pl.eys_code = pd.eys_code\ - LEFT JOIN product_brands pb ON pl.brand_id = pb.id\ - WHERE pl.active = 1 AND pl.sap_code IS NOT NULL AND pl.product_category_sap_4 NOT LIKE '72%'\ - GROUP BY pl.product_id\ - ORDER BY FREQuency DESC\ - LIMIT 60000"; - - // query sql - con.query(sql, function (err, result) { - if (err) throw err; - console.log('Records from database received!') - - do_proccess(result); // proccess - console.log('Keywords proccesed!') - - save_local(freq_, '../results/freq.json'); // save frequencies - save_local(prods_, '../results/prods.json'); // save product (descriptionb per id) - 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 - }); -}); +// #3 +// SUPPORTING FUNCTIONS +//////////////////////////////////////////////////////////////////////////////// -// main proccess -// ----------------------------------------------------------------------------- -function do_proccess(obj) { - // console.log(JSON.stringify(obj, null, 2)); - var freq = []; +/** extract frequencies + * + * @param obj (array): array of products + */ +function extract_freq(obj) { obj.forEach( rec => { var description = rec.product_description; var fq = rec.FREQuency; - var pid = rec.eys_code; - - - - - prods_.push({ - id: pid, - w: description - }); + var pid = rec.eys_code; freq_.push({ fq: fq, id: pid }); - }); // main proccessing finished; - + }); } -// save proccesses +// #4 +// OUTPUT FUNCTIONS //////////////////////////////////////////////////////////////////////////////// // Save Local @@ -144,16 +114,16 @@ function save_local(jsonArray, filePath) { } console.log("JSON file has been saved."); }); -} + return true; +} -// Save to Cloud Storage -// ----------------------------------------------------------------------------- +// Save to Google Cloud Storage +// ----------------------------------------------------------------------------- async function upload_file( bucketName, srcFilePath, trgFilePath ) { // Creates a client - const storage = new Storage({projectId, keyFilename}); try { @@ -169,4 +139,83 @@ async function upload_file( bucketName, srcFilePath, trgFilePath ) { catch(err) { console.error('ERROR:', err); } -} \ No newline at end of file +} + + + +// #5 +// MAIN function (exposed function to run the whole proccess) +//////////////////////////////////////////////////////////////////////////////// + + +/** main() + * + * nodejs's exported function + * used as entry-point function (in case of Google Cloud function) + */ + +// exports.main = () => { // google cloud function entry point +var main = () => { + + // connect; + // get records to proccess; + // call main proccess function; + // save dictionary; + // end script; + // ----------------------------------------------------------------------------- + con.connect(function(err) { + // connect; + if (err) throw err; + console.log("Connected!"); + + // SQL to get needed data + var sql = "SELECT count(pl.eys_code) as FREQuency,\ + pl.product_id as product_id,\ + pb.brand_name,\ + pl.barcode, pl.skl_code, pl.eys_code,\ + IF( pd.description IS NOT NULL , pd.description , pl.product_description ) AS product_description,\ + pl.bpcs_code,\ + pd.image_path\ + FROM product_list as pl\ + LEFT JOIN delivery_orders_products AS dop ON dop.product_id = pl.eys_code\ + LEFT JOIN delivery_orders AS do ON dop.order_id = do.id\ + LEFT JOIN product_list AS replacement ON dop.replacement_for = replacement.eys_code\ + LEFT JOIN product_details AS pd ON pl.eys_code = pd.eys_code\ + LEFT JOIN product_brands pb ON pl.brand_id = pb.id\ + WHERE pl.active = 1 AND pl.sap_code IS NOT NULL AND pl.product_category_sap_4 NOT LIKE '72%'\ + GROUP BY pl.product_id\ + ORDER BY FREQuency DESC\ + LIMIT 60000"; + + // query sql + con.query(sql, function (err, result) { + if (err) throw err; + console.log('Records from database received!') + + extract_freq(result); // proccess + console.log('Keywords proccesed!') + + save_local(freq_, temp_file); // save frequencies + + upload_file('pythia-files', temp_file, 'uploads/json/freq.json') + .then( () => { + console.log('Results saved! Exiting...'); + process.exit(1); + }); + + /// // 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 + }); + }); +} + +// NOTE: +// if running the script directry you need to call the main function +// if running through google cloud-functions you do NOT need to call main() +// (you need define main() as the entry-point function instead) +main(); \ No newline at end of file -- cgit v1.2.3