server.js

const express = require("express");
const parser = require("body-parser");
const app = express();
const path = require("path");
const mysql = require("mysql");
const ExcelJS = require('exceljs');


const host = "127.1.2.3"
const port = 3000;

app.use(parser.json());
app.use(express.static("./"));

var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "",
    database: "company_project"
});

con.connect(function(err) {
    if (err) throw err;
});

//var con = require("./mySqlConnect")

//INSERT	Client send order to company
require('./src/routes/insert.js')(app)
// UPDATE blueprint
// TODO: Check pls
require('./src/routes/updateblueprint.js')(app)
//UPDATE	Admin accept order from client
app.post("/update", (req, res) => {
    var temp = req.body;
    // insert accept table
    con.query(
        `INSERT INTO accept VALUES ('${temp.admin_username}',${temp.order_id})`
    );
    con.query(
        `INSERT INTO quotation VALUES (${temp.order_id},CURDATE(),${temp.quo_price})`
    );
    con.query(
        `UPDATE orders SET order_status = 'Quotation sent' WHERE order_id = ${
          temp.order_id
        }`
    );
});

//UPDATE	Admin update model price
app.post("/updateprice", (req, res) => {


    con.query(
        `UPDATE model SET model_price = ${
          req.body.model_price
        } WHERE model_id = ${req.body.model_id}`
    );

});

//DELETE	Client cancel recieved quotation
app.post("/delete", (req, res) => {
    //ToDo Sun
    con.query(
        `DELETE FROM orders WHERE order_id = ${req.body.order_id}`,
        function(err, result) {
            if (err) throw err;
            console.log("deleted...");
            res.send(JSON.stringify({ redirect: '/delete_order' }));
        }
    );
});


//QUERY	Order list
app.get("/select", (req, res) => {
    //ToDo Sun
    var x = `SELECT O.order_id,O.order_date,O.order_status,C.cus_name FROM orders O,customer_company C WHERE C.cus_id=${
        req.query.cus_id_orders
      } AND C.cus_id=O.cus_id`;
    con.query(x, (err, result) => {
        console.log(JSON.stringify(result));
        res.setHeader("Content-type", "application/json");
        res.send(JSON.stringify(result));
    });
});

app.get("/selectadmin", (req, res) => {
    var x = `SELECT order_id ,order_date, order_status ,cus_name  FROM orders LEFT JOIN customer_company ON customer_company.cus_id = orders.cus_id ;`;
    con.query(x, (err, result) => {
        //console.log(result);
        //res.setHeader("Content-type", "application/json");
        res.send(JSON.stringify(result));
    });
});

app.get("/selectxlsx", (req, res) => {
     // Récupération des données depuis la base de données
    con.query(`SELECT order_id ,order_date, order_status ,cus_name  FROM orders LEFT JOIN customer_company ON customer_company.cus_id = orders.cus_id ;`, (error, results) => {
  if (error) {
    console.error('Erreur :', error);
    res.status(500).send('Erreur lors de la récupération des données');
  } else {
    // Création du fichier Excel avec exceljs
    const wb = new ExcelJS.Workbook();
    const ws = wb.addWorksheet('Sheet1');

    // Ajout des en-têtes
    const headers = Object.keys(results[0]);
    ws.addRow(headers);

    // Ajout des données
    results.forEach(row => {
      ws.addRow(Object.values(row));
    });

    // Envoi du fichier Excel
    res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    res.setHeader('Content-Disposition', 'attachment; filename=exported_data.xlsx');
    wb.xlsx.write(res);
     }
    });
});



//get model_id from related order_id
app.get("/selectorder", (req, res) => {

    //console.log(req.query.orderId);
    var y = `SELECT M.model_name,M.model_id,M.blueprint FROM contain C,model M WHERE C.order_id=${
        req.query.orderId
      } AND C.model_id=M.model_id`;
    //console.log(y);
    con.query(y, (err, result) => {
        console.log(JSON.stringify(result));
        res.setHeader("Content-type", "application/json");
        res.send(JSON.stringify(result));
    });

});

/*
Send HTML file to show on web browser
*/
// First Page
// app.get("/", (req, res) => {
//     console.log(path.join(__dirname + "/test.html"));
//     res.sendFile(path.join(__dirname + "/test.html"));
// });

app.get("/sendorder", (req, res) => {
    console.log(path.join(__dirname + "/html/send_order.html"));
    res.sendFile(path.join(__dirname + "/html/send_order.html"));
});

app.get("/allorder", (req, res) => {
    console.log(path.join(__dirname + "/html/all_order.html"));
    res.sendFile(path.join(__dirname + "/html/all_order.html"));
});
app.get("/confirm_price", (req, res) => {
    console.log(path.join(__dirname + "/html/confirm_price.html"));
    res.sendFile(path.join(__dirname + "/html/confirm_price.html"));
});
app.get("/delete_order", (req, res) => {
    console.log(path.join(__dirname + "/html/delete_order.html"));
    res.sendFile(path.join(__dirname + "/html/delete_order.html"));
});

app.listen(port, host, () => console.log(`Server is running at http://${host}:${port}`));