linkeysearch: javascript/* scripts deploying as Google Cloud Funcctions (work in progress)

2 files changed, 232 insertions, 12 deletions

diff --git a/javascript/freq.js b/javascript/freq.js
index 0ac7779..19c42f5 100644
--- a/javascript/freq.js
+++ b/javascript/freq.js
@@ -20,6 +20,8 @@ var mysql = require('mysql');
 
 const fs = require('fs');
 
+const os = require('os');
+
 const {Storage} = require('@google-cloud/storage');     // import Google Cloud client library
 
 
@@ -35,8 +37,20 @@ const {Storage} = require('@google-cloud/storage');     // import Google Cloud c
 const projectId = 'pythia-251711';
 const keyFilename = '../auth/pythia-251711-047e3d5e6608.json';
 
+
+// cloud_environment (boolean)
+// set value to:
+// true  : if running on Google Cloud Functions
+// false : if running localy
+// --- -- -- - - -
+const cloud_environment = false;        // ** check
+
+
 // temp file of frequencies (json)
-const temp_file = '../results/freq.json';
+const saves_dir = '../results';         // ** check
+const temp_file = 'freq.json';          // ** check
+
+const local_dir = (cloud_environment) ? os.tmpdir() : saves_dir;
 
 
 // db connection parametres
@@ -44,7 +58,7 @@ const temp_file = '../results/freq.json';
 // -----------------------------------------------------------------------------
 
 // while on local machine using cloud_sql_proxy
-var con = mysql.createConnection({  
+var con = mysql.createConnection({      // ** check
     host: "127.0.0.1",
     user: "pythia_db_user_dev",
     password: "VnEP0eysjiXDHcfM",
@@ -52,7 +66,7 @@ var con = mysql.createConnection({
 });
 
 // // while on Google Cloud Functions
-// var con = mysql.createConnection({  
+// var con = mysql.createConnection({   // ** check
 //     socketPath: "/cloudsql/pythia-251711:europe-west4:pythia-db-eu",
 //     user: "pythia_services",
 //     password: process.env.DB_PASSWORD,
@@ -103,11 +117,11 @@ function extract_freq(obj) {
 
 // Save Local
 // -----------------------------------------------------------------------------
-function save_local(jsonArray, filePath) {
+function save_local(jsonArray, fileName) {
     let fileStr = JSON.stringify(jsonArray);    // convert json to string
     
     // write string to file
-    fs.writeFileSync(filePath, fileStr, 'utf8', (err) => {
+    fs.writeFileSync(local_dir +'/ '+ fileName, fileStr, 'utf8', (err) => {
         if (err) {
             console.log("An error occured while writing keywords.json");
             return console.log(err);
@@ -119,7 +133,6 @@ function save_local(jsonArray, filePath) {
 }
 
 
-
 // Save to Google Cloud Storage
 // -----------------------------------------------------------------------------
 async function upload_file( bucketName, srcFilePath, trgFilePath ) {
@@ -163,10 +176,10 @@ var main = () => {
     // save dictionary;
     // end script;
     // -----------------------------------------------------------------------------
-    con.connect(function(err) {
-        // connect;
-        if (err) throw err;
-        console.log("Connected!");
+    con.connect(function(err) {         // comment this out for 'Cannot enqueue Handshake after already enqueuing' error
+        // connect;                     // comment this out for 'Cannot enqueue Handshake after already enqueuing' error
+        if (err) throw err;             // comment this out for 'Cannot enqueue Handshake after already enqueuing' error
+        console.log("Connected!");      // comment this out for 'Cannot enqueue Handshake after already enqueuing' error
 
         // SQL to get needed data 
         var sql = "SELECT count(pl.eys_code) as FREQuency,\
@@ -211,11 +224,12 @@ var main = () => {
 
             // process.exit(1);        // exit
         });
-    });
+    });             // comment this out for 'Cannot enqueue Handshake after already enqueuing' error
 }
 
 // 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
+// --- -- -- - - -
+main();     // ** check
\ No newline at end of file
diff --git a/javascript/freq_GCF.js b/javascript/freq_GCF.js
new file mode 100644
index 0000000..79a79bc
--- /dev/null
+++ b/javascript/freq_GCF.js
@@ -0,0 +1,206 @@
+/** 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');
+
+const os = require('os');
+
+const {Storage} = require('@google-cloud/storage');     // import Google Cloud client library
+
+
+
+
+// #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';
+
+
+
+// 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"
+});
+
+// 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 => {
+        var description = rec.product_description;
+        var fq  = rec.FREQuency;
+        var pid = rec.eys_code;      
+
+        freq_.push({
+            fq: fq,
+            id: pid
+        });
+    });
+}
+
+
+// #4
+// OUTPUT FUNCTIONS
+////////////////////////////////////////////////////////////////////////////////
+
+// Save Local
+// -----------------------------------------------------------------------------
+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 has been saved.");
+    });
+
+    return true;
+}
+
+
+// Save to Google Cloud Storage
+// -----------------------------------------------------------------------------
+async function upload_file( bucketName, srcFilePath, trgFilePath ) {
+    // Creates a client
+    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'      // 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)
+ */
+
+exports.main = () => {   // google cloud function entry point
+
+    // connect;
+    // get records to proccess;
+    // call main proccess function; 
+    // save dictionary;
+    // end script;
+    // -----------------------------------------------------------------------------
+
+    // 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
+    });
+}
+