path: root/javascript/freq.js

/** 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 {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 = '../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",
    password: "VnEP0eysjiXDHcfM",
    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: }
 * @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, filePath) {
    let fileStr = JSON.stringify(jsonArray);    // convert json to string
    
    // write string to file
    fs.writeFileSync(filePath, 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
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();