summaryrefslogtreecommitdiff
path: root/javascript/freq.js
diff options
context:
space:
mode:
Diffstat (limited to 'javascript/freq.js')
-rw-r--r--javascript/freq.js129
1 files changed, 54 insertions, 75 deletions
diff --git a/javascript/freq.js b/javascript/freq.js
index bf52008..75c862e 100644
--- a/javascript/freq.js
+++ b/javascript/freq.js
@@ -9,22 +9,19 @@
* #3 Supporting functions
* #4 Output functions
* #5 Entry-point function main()
- *
- * Requirements
- * npm install dotenv --save
- *
*/
// #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
-require('dotenv').config({ path: '../auth/.env' })
+
// #2
@@ -35,26 +32,25 @@ require('dotenv').config({ path: '../auth/.env' })
// google-storage parametres
// --- -- -- - - -
const projectId = 'pythia-251711';
-const keyFilename = '../auth/pythia-251711-047e3d5e6608.json';
+// const keyFilename = '../auth/pythia-251711-047e3d5e6608.json';
// temp file of frequencies (json)
-const temp_file = '../results/freq.json';
-
+const temp_file = os.tmpdir() + '/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: process.env.DB_HOST,
- user: process.env.DB_USER,
- password: process.env.DB_PASS,
- database: process.env.DB_NAME
-});
+// 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"
+});
// MAIN OUTPUT OF THE SCRIPT
// arrays that need to be constructed and filled with data
@@ -82,13 +78,9 @@ var freq_ = [];
function extract_freq(obj) {
obj.forEach( rec => {
- var description = rec.product_description;
- var fq = rec.FREQuency;
- var pid = rec.eys_code;
-
freq_.push({
- fq: fq,
- id: pid
+ fq: rec.FREQuency,
+ id: rec.eys_code
});
});
}
@@ -100,7 +92,7 @@ function extract_freq(obj) {
// Save Local
// -----------------------------------------------------------------------------
-function save_local(jsonArray, fileName) {
+async function save_local(jsonArray, fileName) {
let fileStr = JSON.stringify(jsonArray); // convert json to string
// write string to file
@@ -109,7 +101,8 @@ function save_local(jsonArray, fileName) {
console.log("An error occured while writing keywords.json");
return console.log(err);
}
- console.log("JSON file has been saved.");
+ console.log("JSON file", fileName, "has been saved.");
+
});
return true;
@@ -120,7 +113,7 @@ function save_local(jsonArray, fileName) {
// -----------------------------------------------------------------------------
async function upload_file( bucketName, srcFilePath, trgFilePath ) {
// Creates a client
- const storage = new Storage({projectId, keyFilename});
+ const storage = new Storage();
try {
await storage.bucket(bucketName).upload(srcFilePath, {
@@ -138,7 +131,6 @@ async function upload_file( bucketName, srcFilePath, trgFilePath ) {
}
-
// #5
// MAIN function (exposed function to run the whole proccess)
////////////////////////////////////////////////////////////////////////////////
@@ -146,9 +138,13 @@ async function upload_file( bucketName, srcFilePath, trgFilePath ) {
/** main()
*
- * the entry-point function
+ * nodejs's exported function
+ * used as entry-point function (in case of Google Cloud function)
*/
-var main = () => {
+
+exports.main = () => { // google cloud function entry point
+
+ console.log('temporary file:', temp_file);
// connect;
// get records to proccess;
@@ -156,60 +152,43 @@ var main = () => {
// save dictionary;
// end script;
// -----------------------------------------------------------------------------
- con.connect(function(err) {
- // connect;
+
+ // SQL to get needed data
+ var sql = "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%'\
+ GROUP BY pl.product_id\
+ ORDER BY FREQuency DESC\
+ LIMIT 50000";
+
+ console.log('Frequencies SQL:', sql)
+
+ // query sql
+ con.query(sql, function (err, result) {
if (err) throw err;
- console.log("Connected!");
-
- // SQL to get needed data
- var sql = "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%'\
- 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
+ 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.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(); // 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
generated by cgit v1.2.3 (git 2.39.1) at 2026-09-22 12:04:56 +0000