Untitled

mail@pastecode.io avatar
unknown
javascript
18 days ago
10 kB
3
Indexable
Never
const express = require('express');
const mysql = require('mysql2');
const path = require('path');

const app = express();

// Your MySQL connection configuration
const connectionConfig = {
    host: 'localhost',
    user: 'root',
    password: 'Aeondev555666',
    database: 'hl7_data',
    sql_mode:'ONLY_FULL_GROUP_BY',
};

// Create a MySQL connection pool
const pool = mysql.createPool(connectionConfig);

// Define the directory where your HTML and JS files are located
const templatePath = 'c:/Users/andre/Desktop/web/corona-free-dark-bootstrap-admin-template-1.0.0';

// Serve static files (CSS, JavaScript, images) from the 'assets' directory
app.use(express.static(path.join(templatePath, 'assets')));

// Serve the index.html file
app.get('/index.html', (req, res) => {
    res.sendFile(path.join(templatePath, 'template', 'index.html'));
});

app.get('/patient-data.html', (req, res) => {
    res.sendFile(path.join(templatePath, 'template', 'patient-data.html'));
});

app.get('/assets/css/style.css', (req, res) => {
    res.sendFile(path.join(templatePath, 'template', 'assets', 'css', 'style.css'));
});

// Serve other static files like CSS, JS, fonts, images, etc.
app.use('/assets/fonts', express.static(path.join(templatePath, 'template', 'assets', 'fonts')));
app.use('/assets/images', express.static(path.join(templatePath, 'template', 'assets', 'images')));
app.use('/assets/js', express.static(path.join(templatePath, 'template', 'assets', 'js')));
app.use('/assets/scss', express.static(path.join(templatePath, 'template', 'assets', 'scss')));
app.use('/assets/vendors', express.static(path.join(templatePath, 'template', 'assets', 'vendors')));

// Route to handle data requests from frontend with pagination, item count, and search
app.get('/grouped-data', (req, res) => {
    // Set SQL mode to ONLY_FULL_GROUP_BY
    const setSqlModeQuery = 'SET SESSION sql_mode = (SELECT REPLACE(@@sql_mode, "ONLY_FULL_GROUP_BY", ""))';

    pool.query(setSqlModeQuery, (sqlModeError) => {
        if (sqlModeError) {
            console.error('Error setting SQL mode:', sqlModeError);
            res.status(500).json({ error: 'Internal server error' });
            return;
        }

        // Proceed with constructing the main SQL query after setting the mode
        constructMainQuery();
    });

    function constructMainQuery() {
    
    

    

    // Extract query parameters
    const { search, NAZKL, patientID, timestamp, page, limit } = req.query;

    // Pagination parameters
    const pageNumber = parseInt(page) || 1; // Default to page 1 if not provided
    const pageSize = parseInt(limit) || 10; // Default page size to 10 if not provided
    const offset = (pageNumber - 1) * pageSize;

    // Construct the main SQL query based on filters and pagination parameters
    let query = `
        SELECT 
            po.patientId, 
            po.operation_name AS operationName, 
            po.min_value AS minValue, 
            po.max_value AS \`maxValue\`, 
            po.patient_value AS patientValue, 
            po.compared_value AS comparedValue, 
            po.units, 
            po.timestampOperation AS timestamp, 
            p.Meno,
            k.NAZKL,
            COUNT(*) AS groupCount
        FROM 
            patient_operations po 
        INNER JOIN 
            pacienti p ON po.patientId = p.patientID
        INNER JOIN 
            klienti k ON p.id_kln = k.ID_ADR
    `;
    let queryParams = [];

    // Add NAZKL filter to the query if NAZKL parameter is provided
    if (NAZKL) {
        query += ' WHERE k.NAZKL LIKE ?';
        queryParams.push(`%${NAZKL}%`);
    }

    // Add timestamp filter to the query if timestamp parameter is provided
    if (timestamp) {
        query += (NAZKL ? ' AND' : ' WHERE') + ' po.timestampOperation = ?';
        queryParams.push(timestamp);
    }

    // Add search filter to the query if search parameter is provided
    if (search) {
        query += ' WHERE po.patientId LIKE ? OR po.timestampOperation LIKE ? OR p.Meno LIKE ? OR k.NAZKL LIKE ?';
        queryParams.push(`%${search}%`, `%${search}%`, `%${search}%`, `%${search}%`); // Use wildcard to match any substring
    }

    // Add NAZKL filter to the query if NAZKL parameter is provided
    if (NAZKL) {
        query += (search ? ' AND' : ' WHERE') + ' k.NAZKL LIKE ?';
        queryParams.push(`%${NAZKL}%`);
    }

    // Add patientID filter to the query if patientID parameter is provided
    if (patientID) {
        query += (search || NAZKL ? ' AND' : ' WHERE') + ' po.patientId = ?';
        queryParams.push(patientID);
    }

    // Add timestamp filter to the query if timestamp parameter is provided
    if (timestamp) {
        query += (search || NAZKL || patientID ? ' AND' : ' WHERE') + ' po.timestampOperation = ?';
        queryParams.push(timestamp);
    }

    // Construct the count query to get the total count of grouped records
    let countQuery = `
        SELECT COUNT(*) AS totalCount
        FROM (
            SELECT COUNT(*) AS groupCount
            FROM 
                patient_operations po 
            INNER JOIN 
                pacienti p ON po.patientId = p.patientID
            INNER JOIN 
                klienti k ON p.id_kln = k.ID_ADR
    `;

    if (search) {
        countQuery += ' WHERE po.patientId LIKE ? OR po.timestampOperation LIKE ? OR p.Meno LIKE ? OR k.NAZKL LIKE ?';
    }
    if (NAZKL) {
        countQuery += (search ? ' AND' : ' WHERE') + ' k.NAZKL LIKE ?';
    }
    if (patientID) {
        countQuery += (search || NAZKL ? ' AND' : ' WHERE') + ' po.patientId = ?';
    }
    if (timestamp) {
        countQuery += (search || NAZKL || patientID ? ' AND' : ' WHERE') + ' po.timestampOperation = ?';
    }

    // Close the subquery and add pagination limits
    query += `
        GROUP BY k.NAZKL, po.timestampOperation
        LIMIT ? OFFSET ?
    `;
    queryParams.push(pageSize, offset);

    // Close the subquery for count query and execute it
    countQuery += `
        GROUP BY k.NAZKL, po.timestampOperation
    ) AS subQuery
    `;

    // Execute the main SQL query to fetch data
    pool.query(query, queryParams, (error, results) => {
        if (error) {
            console.error('Error querying MySQL:', error);
            res.status(500).json({ error: 'Internal server error' });
            return;
        }

        // Execute count query to get totalResults
        pool.query(countQuery, queryParams, (countError, countResult) => {
            if (countError) {
                console.error('Error querying MySQL for count:', countError);
                res.status(500).json({ error: 'Internal server error' });
                return;
            }
            const totalResults = countResult[0].totalCount;
            // Send the data and totalResults count back to the frontend
            res.json({ data: results, totalResults });
        });
    });
    }
});


app.get('/patient-data/:patientID', (req, res) => {
    const { patientID } = req.params;
    const { search, NAZKL, timestamp, page, limit } = req.query;

    // Pagination parameters
    const pageNumber = parseInt(page) || 1; // Default to page 1 if not provided
    const pageSize = parseInt(limit) || 10; // Default page size to 10 if not provided
    const offset = (pageNumber - 1) * pageSize;

    // Construct the SQL query based on filters and pagination parameters
    let query = `
        SELECT 
            po.patientId, 
            po.operation_name AS operationName, 
            po.min_value AS minValue, 
            po.max_value AS \`maxValue\`, 
            po.patient_value AS patientValue, 
            po.compared_value AS comparedValue, 
            po.units, 
            po.timestampOperation AS timestamp, 
            p.Meno,
            k.NAZKL
        FROM 
            patient_operations po 
        INNER JOIN 
            pacienti p ON po.patientId = p.patientID
        INNER JOIN 
            klienti k ON p.id_kln = k.ID_ADR
        WHERE 
            po.patientId = ?
    `;
    let queryParams = [patientID];

    let countQuery = `
        SELECT COUNT(*) AS totalCount
        FROM 
            patient_operations po 
        INNER JOIN 
            pacienti p 
        ON 
            po.patientId = p.patientID
        INNER JOIN 
            klienti k 
        ON 
            p.id_kln = k.ID_ADR
        WHERE 
            po.patientId = ?
    `;

    // Add search filter to the query if search parameter is provided
    if (search) {
        query += ' AND (po.operation_name LIKE ? OR po.timestampOperation LIKE ? OR p.Meno LIKE ? OR k.NAZKL LIKE ?)';
        queryParams.push(`%${search}%`, `%${search}%`, `%${search}%`, `%${search}%`); // Use wildcard to match any substring
    }

    // Add NAZKL filter to the query if NAZKL parameter is provided
    if (NAZKL) {
        query += ' AND k.NAZKL LIKE ?';
        queryParams.push(`%${NAZKL}%`);
    }

    // Add timestamp filter to the query if timestamp parameter is provided
    if (timestamp) {
        query += ' AND po.timestampOperation = ?';
        queryParams.push(timestamp);
    }

    

    // Add pagination limits to the query
    query += ' LIMIT ? OFFSET ?';
    queryParams.push(pageSize, offset);

    

    // Execute the SQL query to fetch data
    pool.query(query, queryParams, (error, results) => {
        if (error) {
            console.error('Error querying MySQL:', error);
            res.status(500).json({ error: 'Internal server error' });
            return;
        }

        // Execute count query to get totalResults
        pool.query(countQuery, [patientID], (countError, countResult) => {
            if (countError) {
                console.error('Error querying MySQL for count:', countError);
                res.status(500).json({ error: 'Internal server error' });
                return;
            }
            const totalResults = countResult[0].totalCount;
            // Send the data and totalResults count back to the frontend
            res.json({ data: results, totalResults });
        });
    });
});




// Start the server
const PORT = process.env.PORT || 3500;
app.listen(PORT, () => {
    console.log(`Server listening on port ${PORT}`);
});
Leave a Comment