update_db_from_xlsx/json_to_db.js

const fs = require('fs');
const path = require('path');
const mysql = require('mysql2/promise');

//Uncomment for Marco Server
// const pool = mysql.createPool({
//     "host": "127.0.0.1",
//     "port": 3306,
//     "user": "rw",
//     "password": "5Xk8Y7Kz6H3DeCXqcZ#Sb%Nz",
//     "ssl": false
//   });

//Uncomment for local test
const pool = mysql.createPool({
    "host": "127.0.0.1",
    "port": 3306,
    "user": "root",
    "password": "password",
    "ssl": false
  });
  
// Function to read JSON files recursively in a directory
function readJsonFiles(directory) {
    const files = fs.readdirSync(directory);

    files.forEach(file => {
        const filePath = path.join(directory, file);
        const stats = fs.statSync(filePath);

        if (stats.isDirectory()) {
            readJsonFiles(filePath);
        } else {
            const fileContent = fs.readFileSync(filePath, 'utf8');
            const jsonData = JSON.parse(fileContent);
            testJsonData(jsonData);
            processJsonFile(filePath);
        }
    });
}


// Function to test JSON data
function testJsonData(data) {
    
    console.log('Key:', data.city_key);
    console.log('Users:');
    const users = data.users || [];
    for (let i = 0; i < users.length; i++) {
        console.log('User', i + 1, ':', users[i]);
    }
    const otherData = { ...data };
    delete otherData.users;
    console.log('Other Data:');
    console.log(otherData);
    console.log('---------------------------');
}

async function processJsonFile(filePath) {
    const data = JSON.parse(fs.readFileSync(filePath, 'utf8'));

    // Assuming data contains 'users' and other properties
    const users = data.users || [];
    const otherData = { ...data };
    delete otherData.users;

    for (const user of users) {
        try {
            const [firstName, lastName, email, phone, department, role] = user;
            let res = await pool.execute('CALL mrnValUser.pCreateUserMRN(?, ?, ?, ?, ?, ?, ?)', [data.city_key, firstName, lastName, email, phone, department, role]);
            if (typeof res === 'undefined') {
                throw new Error("Database connection error");
            }
        } catch (err) {
            console.log(err);
        }
    }

    //TODO: insert city_key into .tMRN_OU
    try {
        const otherDataString = JSON.stringify(otherData);
        //MRN_HWPA for Marco server, mrnValUser on local
        const query = 'INSERT INTO mrnValUser.tMRN_OU (M_DATA, M_OU, M_Flag, M_Name, M_Imprint, M_State) VALUES (?, ?, ?, ?, ?, ?);';
        let res = await pool.execute(query, [otherDataString, data.city_key, "T", "test", "test", "test"]);
        if (typeof res === 'undefined') {
            throw new Error("Database connection error");
        }
        console.log('updated database with otherdata', res[0]);
    } catch (err) {
        console.log(err);
    }
/*
    try {
        const otherDataString = JSON.stringify(otherData);
        const query = 'UPDATE MRN_HWPA.tMRN_OU SET M_Data = ? WHERE M_OU = ?';
        let res = await pool.execute(query, [otherDataString, data.city_key]);
        if (typeof res === 'undefined') {
            throw new Error("Database connection error");
        }
        console.log('updated database with otherdata', res[0]);
    } catch (err) {
        console.log(err);
    }
*/
}

// Start processing JSON files in a directory
readJsonFiles('/home/smnc/Documents/egovc/xlsx_snippet/output');