Backend Express

// server.js
import "dotenv/config";
import express from "express";
import route from "./routing/route.js";

const app = express();
const port = process.env.PORT || 7200;

app.use(express.json());
app.use(express.urlencoded({ extended: true }));

app.use(route);

app.listen(port, () => {
  console.log(`Server running on port ${port}`);
});

// package.json
{
  "name": "latbackend",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "type": "module",
  "scripts": {
    "start": "nodemon server",
    "test": "echo \"Error: no test specified\" && exit 1",
    "genModelDB": "sequelize-auto -o ./model2 -d Eproduct -h localhost -v true -l esm -u postgres -p 5432 -x alvin -e postgres"
  },
  "keywords": [],
  "author": "",
  "license": "ISC",
  "dependencies": {
    "bcrypt": "^5.1.1",
    "dotenv": "^16.3.1",
    "express": "^4.18.2",
    "jsonwebtoken": "^9.0.2",
    "pg": "^8.11.3",
    "sequelize": "^6.33.0",
    "sequelize-auto": "^0.8.8"
  },
  "devDependencies": {
    "nodemon": "^3.0.1"
  }
}

// errorhandling.js
const errorHandling = (data = "", code, message) => {
  return {
    data: data,
    code: code,
    message: message,
  };
};

export { errorHandling };

// init-models.js
import { Sequelize } from "sequelize";

const sequelize = new Sequelize(
  process.env.DB_NM,
  process.env.DB_USER,
  process.env.DB_PASSWORD,
  {
    dialect: "postgres",
    pool: {
      max: 5,
      min: 0,
      acquire: 30000,
      idle: 10000,
    },
  }
);

if (sequelize) {
  setTimeout(() => {
    console.log("Database Connected!");
  }, 1000);
}

const models = initModels(sequelize);
export default models;
export { sequelize };

// order.js
import { errorHandling } from "../helper/errorhandling.js"
import { sequelize } from "../model/init-models.js"

const insertorder = async (req, res) => {
    try {
        const {dt_order, dt_orderdetail} = req.body
        const query = `call insertorder('[${JSON.stringify(dt_order)}]', '${JSON.stringify(dt_orderdetail)}')`
        const result = await sequelize.query(query)
        res.send(errorHandling(result, 200, "Sukses"))
    } catch (error) {
        res.send(errorHandling(400, error.message))
    }
}

const updateorder = async (req, res) => {
    try {
        const {dt_order, dt_orderdetail} = req.body
        console.log(dt_order)
        console.log(dt_orderdetail)
        const query = `call updateorder('[${JSON.stringify(dt_order)}]', '${JSON.stringify(dt_orderdetail)}')`
        const result = await sequelize.query(query)
        res.send(errorHandling(result, 200, "Sukses Update"))
    } catch (error) {
        res.send(errorHandling(400, error.message))
    }
}

export { insertorder, updateorder }

// user.js
import bcrypt from "bcrypt";
import { errorHandling } from "../helper/errorhandling.js";
import models, { sequelize } from "../model/init-models.js";
import jwt from "jsonwebtoken";

const listuser = async (req, res) => {
  try {
    const result = await models.users.findAll(); // select * from users
    res.send(errorHandling(result, 200, "Sukses"));
  } catch (error) {
    res.send(errorHandling(400, error.message));
  }
};

const createuser = async (req, res) => {
  try {
    const { usr, pswd } = req.body;
    const userExists = await models.users.findOne({ where: { username: usr } });
    if (userExists) {
      res.status(400);
      throw new Error(`Username ${usr} already exists`);
    }
    const salt = bcrypt.genSaltSync(10);
    const passhash = bcrypt.hashSync(pswd, salt);
    const result = await models.users.create(
      {
        username: usr,
        password: passhash,
      },
      { returning: true }
    );

    res.send(errorHandling(result, 200, "Sukses Create"));
  } catch (error) {
    res.send(errorHandling(error.message));
  }
};

const updateuser = async (req, res) => {
  try {
    const salt = bcrypt.genSaltSync(10);
    const passhash = bcrypt.hashSync(req.body.pswd, salt);
    const result = await models.users.update(
      {
        username: req.body.usr,
        password: passhash,
      },
      {
        where: { id: req.params.id },
        returning: true,
      }
    );
    res.send(errorHandling(result, 200, "Sukses Update"));
  } catch (error) {
    res.send(errorHandling(400, error.message));
  }
};

const deleteuser = (req, res) => {
  try {
    const result = models.users.destroy({
      where: { id: req.params.id },
      returning: true,
    });
    res.send(errorHandling(result, 200, "Sukses Delete"));
  } catch (error) {
    res.send(errorHandling(400, error.message));
  }
};

const getusercustomer = async (req, res) => {
  try {
    // untuk menampilkan left join
    // const result = await models.users.findAndCountAll({
    //   include: 'customers'
    // })

    // untuk menampilkan inner join
    // const result = await models.users.findAndCountAll({
    //   include: { model: models.customer, as: "customers", required: true },
    // });

    // menampilkan atribut yg digunakan
    // const result = await models.users.findAndCountAll({
    //   include: [
    //     {
    //       model: models.customer,
    //       as: "customers",
    //       required: true,
    //       attributes: ["first_name", "last_name"],
    //     },
    //     {
    //       model: models.orders,
    //       as: "orders",
    //       include: { model: models.order_detail, as: "order_details" },
    //       atributes: ["username"],
    //     },
    //   ],
    // });

    //show all details
    const result = await models.users.findAll({
      include: { all: true, nested: true },
    });
    res.send(errorHandling(result, 200, "Sukses"));
  } catch (error) {
    res.send(errorHandling(400, error.message));
  }
};

//syntax sql
const getview = async (req, res) => {
  try {
    const query = `select * from data_eproduct`;
    const result = await sequelize.query(query);
    res.send(errorHandling(result, 200, "Sukses"));
  } catch (error) {
    res.send(errorHandling(400, error.message));
  }
};

//using view
const viewmodel = async (req, res) => {
  try {
    const result = await models.data_eproduct.findAll();
    res.send(errorHandling(result, 200, "Sukses"));
  } catch (error) {
    res.send(errorHandling(400, error.message));
  }
};

//pagination
const datacur = async (req, res) => {
  try {
    const query = `select * from datacursor(${req.params.lim},${req.params.page})`;
    const result = await sequelize.query(query);
    res.send(errorHandling(result[0], 200, "Sukses"));
  } catch (error) {
    res.send(errorHandling(400, error.message));
  }
};

//search
const searchuname = async (req, res) => {
  try {
    const query = `select * from searchuname('${req.params.usr}')`;
    const result = await sequelize.query(query);
    res.send(errorHandling(result[0], 200, "Sukses"));
  } catch (error) {
    res.send(errorHandling(400, error.message));
  }
};

//method login
const login = async (req, res) => {
  try {
    const { usr, pswd } = req.body;

    const user = await models.users.findOne({ where: { username: usr } });
    if (!user) {
      res.send(errorHandling("Username tidak ditemukan", 400));
    } else {
      const matchPassword = bcrypt.compareSync(pswd, user.password);
      if (matchPassword) {
        const token = jwt.sign(
          { username: user.username, createdat: user.createdat },
          process.env.SECRET_KEY, { expiresIn: "10s" }
        );
        res.send(errorHandling(token, 200, "Sukses"));
      } else {
        res.send(errorHandling(400, "Password salah"));
      }
    }
  } catch (error) {
    res.send(errorHandling(400, error.message));
  }
};

//method cek authorize
const checkToken = async (req, res, next) => {
  try {
    const token = req.headers.authorization;
    if (!token) {
      res.send(errorHandling(200, "Not authorized"));
    } else {
      const verify = jwt.verify(token, process.env.SECRET_KEY);
      if (verify) {
        next();
      }
    }
  } catch (error) {
    res.send(errorHandling(400, error.message));
  }
};

export { listuser, createuser, updateuser, deleteuser, getusercustomer, getview, viewmodel, datacur, searchuname, login, checkToken };

// route.js
import { Router } from "express";
import { home } from "../controller/home.js";
import { listuser, createuser, updateuser, deleteuser, getusercustomer, getview, viewmodel, datacur, searchuname, login, checkToken } from "../controller/user.js";
import { insertorder, updateorder } from "../controller/order.js";

const router = Router();

router.get("/", home);
router.get("/user", checkToken, listuser);

router.post("/user", createuser);

router.put("/user/:id", updateuser);
router.delete("/user/:id", deleteuser);

router.post("/insertorder", insertorder);
router.put("/updateorder", updateorder);

router.delete("/insertorder/:id");

router.get("/getusercustomer", getusercustomer);
router.get("/getdataeproduct", getview);
router.get("/viewmodel", viewmodel);
router.get("/datacur/:lim/:page", datacur);
router.get("/searchuname/:usr", searchuname);

router.post("/login", login);

export default router;