diff options
Diffstat (limited to 'javascript/freq-nonull.js')
| -rw-r--r-- | javascript/freq-nonull.js | 210 |
1 files changed, 210 insertions, 0 deletions
diff --git a/javascript/freq-nonull.js b/javascript/freq-nonull.js new file mode 100644 index 0000000..d92519c --- /dev/null +++ b/javascript/freq-nonull.js @@ -0,0 +1,210 @@ +/** 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() + * + * NOTE: + * first you need to initiate connection to the g-cloud-sql server, ex: + * $ ./cloud_sql_proxy -instances=pythia-251711:europe-west4:pythia-db-eu=tcp:3306 -credential_file=cloudsqlproxy.json + * + */ + +// #1 +// REQUIREMENTS +//////////////////////////////////////////////////////////////////////////////// + + +var mysql = require('mysql'); +const fs = require('fs'); +const os = require('os'); +const {Storage} = require('@google-cloud/storage'); // import Google Cloud client library + + +console.log('dependensies initialized'); + +// #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 = os.tmpdir() + '/freq.json'; +const temp_file = '../results/freq-nonull.json'; + + +// db connection parametres +// (keep the one that suits your environment; comment out the other) +// ----------------------------------------------------------------------------- + + +// while on Google Cloud Functions +var con = mysql.createConnection({ // ** check + // socketPath: "/cloudsql/pythia-251711:europe-west4:pythia-db-eu", + // user: "pythia_services", + // password: process.env.DB_PASSWORD, + // database: "pythia_db" + socketPath: "127.0.0.1", + user: "pythia_db_user_dev", + password: 'VnEP0eysjiXDHcfM', + database: "dev_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: } + * @var id (int): product's eys code + * @var fq (int): (order) frerquency + */ +var freq_ = []; + + + +// #3 +// SUPPORTING FUNCTIONS +//////////////////////////////////////////////////////////////////////////////// + + + +/** extract frequencies + * + * @param obj (array): array of products + */ +function extract_freq(obj) { + + obj.forEach( rec => { + freq_.push({ + fq: rec.FREQuency, + id: rec.eys_code + }); + }); +} + + +// #4 +// OUTPUT FUNCTIONS +//////////////////////////////////////////////////////////////////////////////// + +// Save Local +// ----------------------------------------------------------------------------- +async function save_local(jsonArray, fileName) { + let fileStr = JSON.stringify(jsonArray); // convert json to string + + // write string to file + fs.writeFileSync(fileName, fileStr, 'utf8', (err) => { + if (err) { + console.log("An error occured while writing keywords.json"); + return console.log(err); + } + console.log("JSON file", fileName, "has been saved."); + + }); + + return true; +} + + +// Save to Google Cloud Storage +// ----------------------------------------------------------------------------- +async function upload_file( bucketName, srcFilePath, trgFilePath ) { + // Creates a client + const storage = new Storage(); + + try { + await storage.bucket(bucketName).upload(srcFilePath, { + destination: trgFilePath, + gzip: true, // serve compressed + metadata: { // cache for 8 hours + cacheControl: 'public, max-age=60' // production set: 28800 + } + }); + console.log(`${srcFilePath} uploaded to ${bucketName}`); + } + catch(err) { + console.error('ERROR:', err); + } +} + + +// #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) + */ + +var main = () => { // google cloud function entry point + + console.log('temporary file:', temp_file); + + // connect; + // get records to proccess; + // call main proccess function; + // save dictionary; + // end script; + // ----------------------------------------------------------------------------- + + // SQL to get needed data + var sql = `SELECT * FROM ( + SELECT count(pl.eys_code)*5+SUM(dop.quantity) 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%' AND FREQuency IS NOT NULL + GROUP BY pl.product_id + ORDER BY FREQuency DESC + LIMIT 30000 ) X + WHERE X.FREQuency IS NOT NULL`; + + console.log('Frequencies SQL:', sql) + + // 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 + .then( () => { + upload_file('pythia-files', temp_file, 'uploads/json/freq-nonull.json') + .then( () => { + console.log('Results saved! Exiting...'); + }); + }); + + }); +} + +main();
\ No newline at end of file |