Untitled

const fs = require('fs');
const path = require('path');
const mysql = require('mysql2');
const firebird = require('node-firebird');

const mysqlConnectionConfig = {
    host: 'localhost',
    user: 'root',
    password: 'Aeondev555666',
    database: 'hl7_data'
};

const firebirdConnectionConfig = {
    host: 'localhost',
    port: 3050,
    database: 'C:/Users/andre/Desktop/HDveter_2024.FDB',
    user: 'SYSDBA',
    password: 'masterkey'
};

let mysqlConnection;

function connectToMySQL() {
    mysqlConnection = mysql.createConnection(mysqlConnectionConfig);

    mysqlConnection.connect(function(err) {
        if (err) {
            console.error('Error connecting to MySQL database:', err);
            throw err;
        } else {
            console.log('\x1b[32m Connected to MySQL database\x1b[0m');
            mysqlConnection.query('SELECT 1', function (error, results, fields) {
                if (error) {
                    console.error('Error executing query to check MySQL connection:', error);
                    mysqlConnection.state = 'disconnected'; // Update connection state if there's an error
                } else {
                    mysqlConnection.state = 'authenticated'; // Update connection state if the query succeeds
                }
                console.log('MySQL connection state after query:', mysqlConnection.state);
            });
        }
    });
}


function disconnectFromMySQL() {
    mysqlConnection.end(function(err) {
        if (err) {
            console.error('Error disconnecting from MySQL database:', err);
            throw err;
        } else {
            console.log('\x1b[32m Disconnected from MySQL database\x1b[0m');
        }
    });
}

function connectToFirebird() {
    return new Promise((resolve, reject) => {
        firebird.attach(firebirdConnectionConfig, (err, db) => {
            if (err) {
                reject(err);
            } else {
                console.log('\x1b[32m Connected to Firebird database\x1b[0m');
                resolve(db);
            }
        });
    });
}



let processedFiles = new Set();

const directoryPath = "C:/Users/andre/Desktop/fileDir";
const processedDirPath = "C:/Users/andre/Desktop/fileDir/processed";
const nocompletedDirPath = "C:/Users/andre/Desktop/fileDir/nocompleted";
const wasInDatabaseDirPath = "C:/Users/andre/Desktop/fileDir/wasInDatabase";

function parseHL7Message(hl7Message) {
    const segments = hl7Message.split('\r');
    const result = {
        patientId: null,
        obxValues: {},
        timestampOperation: null // Added to store Unix timestamp
    };

    segments.forEach(segment => {
        const fields = segment.split('|');
        const segmentName = fields[0];
        if (segmentName === 'MSH') {
            const timeString = fields[6];
            const year = parseInt(timeString.substring(0, 4), 10);
            const month = parseInt(timeString.substring(4, 6), 10) - 1; // Months are zero-based in JavaScript
            const day = parseInt(timeString.substring(6, 8), 10);
            const hour = parseInt(timeString.substring(8, 10), 10);
            const minute = parseInt(timeString.substring(10, 12), 10);
            const second = parseInt(timeString.substring(12, 14), 10);

            // Construct a new Date object in UTC
            const date = new Date(Date.UTC(year, month, day, hour, minute, second));

            // Convert Date object to Unix timestamp
            result.timestampOperation = Math.floor(date.getTime() / 1000); // Divide by 1000 to convert milliseconds to seconds
            console.log("Unix timestamp:", result.timestampOperation);
        } else if (segmentName === 'SPM') {
            result.patientId = fields[2];
        } else if (segmentName === 'OBX') {
            const obxNumber = fields[1];
            const obxValue1 = fields[5]
            const obxValue2 = fields[7];
            const obxName = fields[3];
            const obxUnit = fields[6].replace(/\^\^UCUM$/, '');

            const obxValues = fields[7].split('-');
            const obxMinValue = obxValues[0];
            const obxMaxValue = obxValues[1];

            function compareValues() {
                const numericObxValue1 = parseFloat(obxValue1);
                const numericObxMinValue = parseFloat(obxMinValue);
                const numericObxMaxValue = parseFloat(obxMaxValue);

                if (!isNaN(numericObxValue1) && !isNaN(numericObxMinValue) && !isNaN(numericObxMaxValue)) {
                    if (numericObxValue1 >= numericObxMinValue && numericObxValue1 <= numericObxMaxValue) {
                        return `Values are good for ${obxNumber}`;
                    } else {
                        return `Values are not within the range for ${obxNumber}`;
                    }
                } else {
                    return `Invalid numeric values for comparison in ${obxNumber}`;
                }
            }

            const nameParts = obxName.split('^');
            const obxShortName = (nameParts[1] || '').trim();
            const obxComponentCode = (nameParts[2] || '').trim();
            const obxFullName = obxComponentCode ? `${obxShortName}/${obxComponentCode}` : obxShortName;

            if (!result.obxValues[obxNumber]) {
                result.obxValues[obxNumber] = {
                    operation_name: obxFullName,
                    // recommended_values: obxValue1,
                    patient_value: obxValue1,
                    compared_value: compareValues(),
                    min_value: obxMinValue, // Adding min_value
                    max_value: obxMaxValue,  // Adding max_value
                    units: obxUnit,
                };
            }
        }
    });

    return result;
}

function createPatientOperationsTable() {
    if (mysqlConnection.state === 'disconnected') {
        console.error('Connection is not open. Cannot execute query.');
        return;
    }

    const createTableQuery = `CREATE TABLE IF NOT EXISTS patient_operations (
        ID INT AUTO_INCREMENT PRIMARY KEY,
        patientId VARCHAR(255),
        operation_name VARCHAR(255),
        min_value VARCHAR(255), 
        max_value VARCHAR(255),
        patient_value VARCHAR(255),
        units VARCHAR(255),
        compared_value VARCHAR(255),
        timestampOperation BIGINT,
        currentUnixTime BIGINT,
        destination VARCHAR(255)
    )`;

    mysqlConnection.query(createTableQuery, (err, result) => {
        if (err) {
            console.error('Error creating patient_operations table:', err);
            return;
        }
        console.log('Table patient_operations created or already exists');
    });
}

function createKlientiTable() {
    if (mysqlConnection.state === 'disconnected') {
        console.error('Connection is not open. Cannot execute query.');
        return;
    }

    const createTableQuery = `CREATE TABLE IF NOT EXISTS klienti (
        ID_ADR VARCHAR(255),
        TELEF VARCHAR(255),
        EMAIL VARCHAR(255),
        NAZKL VARCHAR(255)
    )`;

    mysqlConnection.query(createTableQuery, (err, result) => {
        if (err) {
            console.error('Error creating klienti table:', err);
            return;
        }
        console.log('Table klienti created or already exists');
    });
}


function createPacientiTable(patientId) {
    if (mysqlConnection.state === 'disconnected') {
        console.error('Connection is not open. Cannot execute query.');
        return;
    }

    const createTableQuery = `CREATE TABLE IF NOT EXISTS pacienti (
        ID INT AUTO_INCREMENT PRIMARY KEY,
        id_kln INT,
        patientId VARCHAR(255),
        ID_DRP INT,
        Meno VARCHAR(255)
    )`;

    mysqlConnection.query(createTableQuery, (err, result) => {
        if (err) {
            console.error('Error creating pacienti table:', err);
            return;
        }
        console.log('Table pacienti created or already exists');

        // Add ID_DRP column to pacienti table
        addID_DRPColumnToPacientiTable(patientId);
    });
}


function getPatientID_KLN(patientId) {
    return new Promise((resolve, reject) => {
        const query = "SELECT id_kln FROM pacienti WHERE patientId = ?";
        mysqlConnection.query(query, [patientId], (err, result) => {
            if (err) {
                console.error('Error getting id_kln from pacienti:', err);
                reject(err);
            }
            if (result.length > 0) {
                const id_kln = result[0].id_kln;
                console.log(`id_kln for patientId ${patientId} is ${id_kln}`);
                resolve(id_kln);
            } else {
                console.log(`No id_kln found for patientId ${patientId}`);
                resolve(null); // Resolve with null if no id_kln is found
            }
        });
    });
}



function insertDataIntoPatientOperations(patientId, obxValues, timestampOperation, currentUnixTime, filePath) {
    if (mysqlConnection.state === 'disconnected') {
        console.error('Connection is not open. Cannot execute query.');
        return;
    }

    const insertQuery = `INSERT INTO patient_operations (patientId, operation_name, patient_value, compared_value,units, timestampOperation, currentUnixTime, destination, min_value, max_value) VALUES ?`;

    const destinationPath = path.join(processedDirPath, path.basename(filePath)); // Construct the destination path

    const values = Object.values(obxValues).map(obx => [
        patientId,
        obx.operation_name,
        obx.patient_value,
        obx.compared_value,
        obx.units,
        timestampOperation,
        currentUnixTime,
        destinationPath,
        obx.min_value, // Inserting min_value
        obx.max_value // Inserting max_value
    ]);

    mysqlConnection.query(insertQuery, [values], (err, result) => {
        if (err) {
            console.error(`Error inserting data into patient_operations table for patient ${patientId}:`, err);
            return;
        }
        console.log(`Data inserted into patient_operations table for patient ${patientId} \x1b[32m successfully.\x1b[0m`);

        // Move the file to the processed directory once insertion is done
        moveFileToProcessedDirectory(filePath);
    });
}

function insertDataIntoPacientiTable(patientId, ID_KLN, ID_DRP, Meno) {
    if (mysqlConnection.state === 'disconnected') {
        console.error('Connection is not open. Cannot execute query.');
        return;
    }

    const insertQuery = `INSERT INTO Pacienti (id_kln, patientId, ID_DRP, Meno) VALUES (?, ?, ?, ?)`;

    mysqlConnection.query(insertQuery, [ID_KLN, patientId, ID_DRP, Meno], (err, result) => {
        if (err) {
            console.error(`Error inserting data into Pacienti table for patientId ${patientId}:`, err);
            return;
        }
        console.log(`Data inserted into Pacienti table for patientId ${patientId} successfully.`);


        // Now call getPatientID_KLN after the insertion is completed

        getPatientID_KLN(patientId)
            .then(id_kln => {
                if (id_kln) {
                    // Proceed with further operations if id_kln is retrieved successfully
                    fetchDataFromFirebird(id_kln);
                } else {
                    console.log(`No id_kln found for patientId ${patientId}`);
                }
            })
            .catch(err => console.error('Error fetching id_kln:', err));
    });
}

function addID_DRPColumnToPacientiTable(patientId) {
    if (mysqlConnection.state === 'disconnected') {
        console.error('Connection is not open. Cannot execute query.');
        return;
    }

    const checkColumnQuery = `SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'pacienti' AND COLUMN_NAME = 'ID_DRP'`;

    mysqlConnection.query(checkColumnQuery, (err, result) => {
        if (err) {
            console.error('Error checking column existence in pacienti table:', err);
            return;
        }

        if (result.length === 0) {
            // Column does not exist, proceed with adding it
            const alterTableQuery = `ALTER TABLE pacienti ADD COLUMN ID_DRP INT`;

            mysqlConnection.query(alterTableQuery, (err, result) => {
                if (err) {
                    console.error('Error adding ID_DRP column to pacienti table:', err);
                    return;
                }
                console.log('Column ID_DRP added to pacienti table successfully.');

                // Retrieve ID_KLN and ID_DRP from Firebird and insert into pacienti table
                getDataFromFirebird(patientId, db);
            });
        } else {
            console.log('Column ID_DRP already exists in pacienti table.');
        }
    });
}

function getDataFromFirebird(patientId, db) {
    return new Promise((resolve, reject) => {
        const query = `SELECT ID_KLN, ID_DRP, MENO FROM VET_PACIEN WHERE ID_PAC = ?`;
        const params = [patientId];

        db.query(query, params, (err, result) => {
            if (err) {
                console.error('Error retrieving data from Firebird database:', err);
                reject(err); // Reject the promise if there's an error
                return;
            }

            try {
                const ID_KLN = result[0].ID_KLN;
                const ID_DRP = result[0].ID_DRP;
                const MENO = result[0].MENO;
                console.log(`Retrieved ID_KLN ${ID_KLN}, ID_DRP ${ID_DRP}, and MENO ${MENO} for patientId ${patientId} from Firebird database`);
                insertDataIntoPacientiTable(patientId, ID_KLN, ID_DRP, MENO);
                resolve(ID_KLN); // Resolve the promise with id_kln if everything is successful
            } catch (error) {
                console.error('Error processing retrieved data:', error);
                reject(error); // Reject the promise if there's an error
            }
        });
    });
}


function createTypZvierataTable() {
    if (mysqlConnection.state === 'disconnected') {
        console.error('Connection is not open. Cannot execute query.');
        return;
    }

    const createTableQuery = `CREATE TABLE IF NOT EXISTS typ_zvierata (
        ID_DRP INT PRIMARY KEY,
        NAZOV VARCHAR(255)
    )`;

    mysqlConnection.query(createTableQuery, (err, result) => {
        if (err) {
            console.error('Error creating typ_zvierata table:', err);
            return;
        }
        console.log('Table typ_zvierata created or already exists');

        // If the table was created or already exists, proceed with inserting data
    });
}


function compareAndInsertNewData(db) {
    // Запрос для извлечения данных из Firebird
    const firebirdQuery = `SELECT ID_DRP, NAZOV FROM VET_DRUPA`;

    db.query(firebirdQuery, (err, firebirdResult) => {
        if (err) {
            console.error('Error retrieving data from Firebird table VET_DRUPA:', err);
            return;
        }

        // Запрос для извлечения данных из MySQL
        const mysqlQuery = `SELECT ID_DRP FROM typ_zvierata`;

        mysqlConnection.query(mysqlQuery, (err, mysqlResult) => {
            if (err) {
                console.error('Error retrieving data from MySQL table typ_zvierata:', err);
                return;
            }

            // Создание множества для быстрого поиска существующих ID_DRP в MySQL
            const existingIds = new Set(mysqlResult.map(row => row.ID_DRP));

            // Фильтрация новых данных, которые отсутствуют в MySQL
            const newData = firebirdResult.filter(row => !existingIds.has(row.ID_DRP));

            // Вставка только новых данных в MySQL
            if (newData.length > 0) {
                const values = newData.map(row => [row.ID_DRP, row.NAZOV]);
                const insertQuery = `INSERT INTO typ_zvierata (ID_DRP, NAZOV) VALUES ?`;

                mysqlConnection.query(insertQuery, [values], (err, result) => {
                    if (err) {
                        console.error('Error inserting new data into typ_zvierata table:', err);
                        return;
                    }
                    console.log(`${result.affectedRows} new data inserted into typ_zvierata table successfully.`);
                });
            } else {
                console.log('No new data to insert into typ_zvierata table.');
            }
        });
    });
}



function moveFileToProcessedDirectory(filePath) {
    const destinationPath = path.join(processedDirPath, path.basename(filePath));

    // Check if the file exists at the source path
    if (!fs.existsSync(filePath)) {
        console.error('Error moving file to processed directory: File does not exist at the source path');
        return;
    }

    fs.rename(filePath, destinationPath, (err) => {
        if (err) {
            console.error('Error moving file to processed directory:', err);
        } else {
            console.log('\x1b[32mFile moved successfully to processed directory\x1b[0m');

            // Remove the file path from the processedFiles set
            processedFiles.delete(filePath);
        }
    });
}
function moveFileToNoCompletedDirectory(filePath) {
    const destinationPath = path.join(nocompletedDirPath, path.basename(filePath));

    fs.rename(filePath, destinationPath, (err) => {
        if (err) {
            console.error('Error moving file to nocompleted directory:', err);
        } else {
            console.log('\x1b[32mFile moved successfully to nocompleted directory\x1b[0m');

            // Remove the file path from the processedFiles set
            processedFiles.delete(filePath);
        }
    });
}

function moveFileToWasInDatabaseDirectory(filePath) {
    const destinationPath = path.join(wasInDatabaseDirPath, path.basename(filePath));

    fs.rename(filePath, destinationPath, (err) => {
        if (err) {
            console.error('Error moving file to wasInDatabase directory:', err);
        } else {
            console.log('\x1b[32mFile moved successfully to wasInDatabase directory\x1b[0m');
        }
    });
}

function checkDataExistence(extractedData, filePath, db) {
    if (mysqlConnection.state === 'disconnected') {
        console.error('Connection is not open. Cannot execute query.');
        return;
    }

    const { patientId, obxValues, timestampOperation } = extractedData;

    const query = "SELECT COUNT(*) AS cnt FROM patient_operations WHERE patientId = ? AND timestampOperation = ?";
    const values = [patientId, timestampOperation];

    mysqlConnection.query(query, values, (err, data) => {
        if (err) {
            console.error('Error checking data existence:', err);
            moveFileToNoCompletedDirectory(filePath); // Move file to nocompleted directory on error
            return;
        }

        try {
            const rowCount = data[0].cnt;

            if (rowCount > 0) {
                console.log('Data already exists in the database. Moving file to "wasInDatabase" directory.');
                moveFileToWasInDatabaseDirectory(filePath); // Move file to wasInDatabase directory
            } else {
                console.log('Data does not exist in the database. Proceeding with insertion.');
                insertDataIntoPatientOperations(patientId, obxValues, timestampOperation, Date.now(), filePath);
                // Retrieve ID_KLN, ID_DRP, and Meno from Firebird and insert into Pacienti table
                getDataFromFirebird(patientId, db)
                    .then(() => {
                    })
                    .catch(error => {
                        console.error('Error getting data from Firebird:', error);
                        moveFileToNoCompletedDirectory(filePath); // Move file to nocompleted directory on error
                    });
            }
        } catch (error) {
            console.error('Error processing data check:', error);
            moveFileToNoCompletedDirectory(filePath); // Move file to nocompleted directory on error
        }
    });
}

function processFile(file, db) {
    const filePath = path.join(directoryPath, file);

    fs.stat(filePath, (err, stats) => {
        if (err) {
            console.error(`Error getting file stats for "${file}":`, err);
            return;
        }

        const isFile = stats.isFile();
        const isDirectory = stats.isDirectory();

        if (isFile) {
            try {
                const hl7Message = fs.readFileSync(filePath, 'utf-8');
                const extractedData = parseHL7Message(hl7Message);

                // Checking if patientID consists only of numbers
                if (extractedData && /^\d+$/.test(extractedData.patientId)) {
                    console.log('Patient ID: \x1b[32m', extractedData.patientId + '\x1b[0m');

                    createPatientOperationsTable(); // Ensure patient_operations table exists
                    createPacientiTable(extractedData.patientId); // Pass patientId to createPacientiTable
                    

                    checkDataExistence(extractedData, filePath, db); // Check if data already exists in the database
                } else {
                    console.warn(`Skipping file "${file}" as 'patientId' is not a number.`);
                    moveFileToNoCompletedDirectory(filePath); // Move file to nocompleted directory
                }

                processedFiles.add(filePath);
            } catch (err) {
                console.error(`Error processing file "${file}":`, err);
            }
        }
    });
}


function insertDataIntoKlienti(id_kln, TELEF, EMAIL, NAZKL) {
    if (mysqlConnection.state === 'disconnected') {
        console.error('Connection is not open. Cannot execute query.');
        return;
    }

    // Check if EMAIL is null, if so, replace it with "Nepouživa"
    const emailValue = EMAIL ? EMAIL : 'Nepouživa';

    const insertQuery = `INSERT INTO klienti (ID_ADR, TELEF, EMAIL, NAZKL) VALUES (?, ?, ?, ?)`;

    mysqlConnection.query(insertQuery, [id_kln, TELEF, emailValue, NAZKL], (err, result) => {
        if (err) {
            console.error(`Error inserting data into klienti table for id_kln ${id_kln}:`, err);
            return;
        }
        console.log(`Data inserted into klienti table for id_kln ${id_kln} successfully.`);
    });
}

function fetchDataFromFirebird(id_kln) {
    return connectToFirebird()
        .then(db => {
            const query = `SELECT TELEF, EMAIL, NAZKL FROM UCO_ADRES WHERE ID_ADR = ?`;
            const params = [id_kln];
            return new Promise((resolve, reject) => {
                db.query(query, params, (err, result) => {
                    if (err) {
                        console.error('Error fetching data from Firebird:', err);
                        reject(err);
                    } else {
                        resolve(result);
                    }
                });
            });
        })
        .then(result => {
            if (result.length > 0) {
                const { TELEF, EMAIL, NAZKL } = result[0];
                console.log(`Fetched TELEF: ${TELEF}, EMAIL: ${EMAIL}, NAZKL: ${NAZKL}`);
                // Now call a function to insert this data into MySQL
                insertDataIntoKlienti(id_kln, TELEF, EMAIL, NAZKL);
            } else {
                console.log(`No data found in Firebird for id_kln ${id_kln}`);
                // Handle the case when no data is found
            }
        })
        .catch(err => console.error('Error connecting to Firebird:', err));
}


function processDirectory(db) {
    fs.readdir(directoryPath, (err, files) => {
        if (err) {
            console.error('Error reading directory:', err);
            return;
        }

        const hl7Files = files.filter(file => file.endsWith('.hl7'));
        if (hl7Files.length === 0) {
            console.log('No hl7 files were found during checkout');
            return;
        }

        hl7Files.forEach(file => {
            processFile(file, db);
        });
    });
}

let db;

// Start MySQL connection
connectToMySQL();

// Start Firebird connection
connectToFirebird()
    .then(database => {
        db = database;
        console.log(`Watching directory: ${directoryPath}`);
        processDirectory(db);
        createTypZvierataTable();
        createKlientiTable();
        compareAndInsertNewData(database);
    })  
    .catch(err => console.error('Error connecting to Firebird:', err));

// Interval to periodically check for new files in the directory
setInterval(() => {
    if (mysqlConnection && mysqlConnection.state === 'authenticated') {
        processDirectory(db); // Process directory at regular intervals if MySQL connection is authenticated
    }
}, 5000); // Adjust interval as needed