From f1b9662122abed09ccf74b2eda034e7a45730e60 Mon Sep 17 00:00:00 2001 From: Bobby Date: Tue, 3 May 2022 22:04:04 -0400 Subject: citizens pagination --- interface/controllers/citizens.controller.js | 13 ++- interface/public/citizens.js | 95 +++++++++++++++-- interface/routes/api/citizens.js | 6 ++ interface/routes/index.js | 14 ++- interface/views/citizens.ejs | 149 ++++++++++++++++++--------- 5 files changed, 212 insertions(+), 65 deletions(-) diff --git a/interface/controllers/citizens.controller.js b/interface/controllers/citizens.controller.js index 0e45a13..3bec103 100644 --- a/interface/controllers/citizens.controller.js +++ b/interface/controllers/citizens.controller.js @@ -5,23 +5,26 @@ const { sequelize } = require("../models"); // Retrieve all citizens from the database. Limit the number of citizens returned to 10. -exports.findXCitizens = () => { - query = `select c.citizen_id, c.first_name, c.last_name, c.address, c.mobile_num, c.dob, c.gender, c.marital_status, c.village_id, v.village_name +exports.findXCitizens = (limit, offset) => { + const query = `select c.citizen_id, c.first_name, c.last_name, c.address, c.mobile_num, c.dob, c.gender, c.marital_status, c.village_id, v.village_name from citizens c join village_master v on c.village_id = v.village_id - order by citizen_id limit 10;`; + order by citizen_id limit ${limit} offset ${offset};`; return sequelize.query(query, { type: QueryTypes.SELECT }) }; +exports.getCountOfCitizens = () => { + const query = `select count(*) as count from citizens;`; + return sequelize.query(query, { type: QueryTypes.SELECT }) +} -exports.deleteCitizenbyId = (citizen_id) =>{ +exports.deleteCitizenbyId = (citizen_id) =>{ return citizens.destroy({ where: { citizen_id } }) - }; exports.editCitizen = (citizen_id, address, mobile_num, dob, marital_status) => { diff --git a/interface/public/citizens.js b/interface/public/citizens.js index 540bf72..10a226f 100644 --- a/interface/public/citizens.js +++ b/interface/public/citizens.js @@ -7,6 +7,93 @@ $(".ui.form").form({ }, }); +// Get current page and limit query parameter +const queryString = window.location.search; +const urlParams = new URLSearchParams(queryString); +const page = urlParams.get("page") || 1; +const limit = urlParams.get("limit") || 10; +const numberOfPages = Math.ceil(count / limit); +const pagination = $(".pagination"); +const pageLeft = $('#pageLeft'); +const pageRight = $('#pageRight'); +if (page > 1) { + pageLeft.removeClass("disabled"); + pageLeft.attr("href", `/citizens?page=${page - 1}&limit=${limit}`); +} else { + pageLeft.addClass("disabled"); +} +if (page < numberOfPages) { + pageRight.removeClass("disabled"); + pageRight.attr("href", `/citizens?page=${page + 1}&limit=${limit}`); +} else { + pageRight.addClass("disabled"); +} + +function redirectToLimit(limit) { + window.location.href = `/citizens?page=1&limit=${limit}`; +} + +addPageNumbers(numberOfPages); + +function addPageNumbers(numberOfPages) { + // Add page numbers from current page to 2 pages before and 2 pages after, if there are more than 5 pages + if (numberOfPages > 5) { + let startingPoint = page - 2; + if (page < 3) { + startingPoint = 1; + } else if (page > numberOfPages - 2) { + startingPoint = numberOfPages - 4; + } else { + startingPoint = page - 2; + } + for (let i = startingPoint; i < startingPoint + 5; i++) { + const linkElement = document.createElement("a"); + linkElement.innerHTML = i; + linkElement.setAttribute("href", `/citizens?page=${i}&limit=${limit}`); + linkElement.setAttribute("id", `page${i}`); + linkElement.classList.add("item"); + pageRight.before(linkElement); + } + // add dots + const dots = document.createElement("a"); + dots.innerHTML = "..."; + dots.classList.add("item"); + pageRight.before(dots); + + // add first page if page > 3 + if (page > 3) { + const firstPage = document.createElement("a"); + firstPage.innerHTML = 1; + firstPage.setAttribute("href", `/citizens?page=1&limit=${limit}`); + firstPage.setAttribute("id", `page1`); + firstPage.classList.add("item"); + pageRight.before(firstPage); + } + + // add last page + if (page != numberOfPages) { + const lastPage = document.createElement("a"); + lastPage.innerHTML = numberOfPages; + lastPage.setAttribute("href", `/citizens?page=${numberOfPages}&limit=${limit}`); + lastPage.classList.add("item"); + pageRight.before(lastPage); + } + } else { + // add page numbers + for (let i = 1; i <= numberOfPages; i++) { + // Insert before pageRight + const linkElement = document.createElement("a"); + linkElement.innerHTML = i; + linkElement.setAttribute("href", `/citizens?page=${i}&limit=${limit}`); + linkElement.setAttribute("id", `page${i}`); + linkElement.classList.add("item"); + pageRight.before(linkElement); + } + } + const currentPageElement = document.getElementById(`page${page}`); + currentPageElement.classList.add("active"); +} + function editCitizensRecord(citizen) { citizen = JSON.parse(citizen); $(".edit.modal").modal("show"); @@ -14,7 +101,7 @@ function editCitizensRecord(citizen) { $("#mobile_number").val(citizen.mobile_num); $("#dob").val(citizen.dob); $("#marital_status").val(citizen.marital_status); - $('#marital_status').dropdown('set selected', citizen.marital_status); + $("#marital_status").dropdown("set selected", citizen.marital_status); $("#citizen_id").html(citizen.citizen_id); } @@ -51,10 +138,6 @@ function deleteCitizenRecord(citizen) { success: function (response) { console.log(response); location.reload(); - } + }, }); - - } - - diff --git a/interface/routes/api/citizens.js b/interface/routes/api/citizens.js index fc98928..c6c6425 100644 --- a/interface/routes/api/citizens.js +++ b/interface/routes/api/citizens.js @@ -3,6 +3,12 @@ const router = express.Router(); const citizensController = require("../../controllers/citizens.controller"); +router.get('/count', (req, res) => { + citizensController.getCountOfCitizens().then(count => { + res.json({ count }); + }); +}); + router.post("/edit", (req, res) => { const { citizen_id, address, mobile_num, dob, marital_status } = req.body; if (!citizen_id || !address || !mobile_num || !dob || !marital_status) { diff --git a/interface/routes/index.js b/interface/routes/index.js index b99abbf..c75c2c0 100644 --- a/interface/routes/index.js +++ b/interface/routes/index.js @@ -22,11 +22,17 @@ router.get('/', (req, res) => { }); router.get("/citizens", (req, res) => { + // Get the limit and offset from the query string + const limit = parseInt(req.query.limit, 10) || 10; + const page = req.query.page ? (req.query.page - 1) * limit : 0; + // Get the citizens from the database - citizensController.findXCitizens().then(citizens => { - res.render("citizens", { - citizens: citizens, - title: "Citizens Data" + Promise.all([citizensController.findXCitizens(limit, page), citizensController.getCountOfCitizens()]).then(results => { + const [citizens, count] = results; + res.render('citizens', { + title: 'Citizens', + citizens, + count: count[0].count, }); }); }); diff --git a/interface/views/citizens.ejs b/interface/views/citizens.ejs index 2244713..2cb4ecf 100644 --- a/interface/views/citizens.ejs +++ b/interface/views/citizens.ejs @@ -4,10 +4,22 @@ <%- include('partials/head') %> @@ -58,57 +70,94 @@ <%- include('partials/navbar') %> - - - - - - - - - - - - - - - - <% for(var i=0; i < citizens.length; i++) { %> - - - - - - - - - - + + <% } %> + + + + + + + +
First NameLast NameAddressMobile NumberDate of BirthGenderMarital StatusVillage Name
<%= citizens[i].first_name %><%= citizens[i].last_name %><%= citizens[i].address %><%= citizens[i].mobile_num %><%= citizens[i].dob %><%= citizens[i].gender %><%= citizens[i].marital_status %><%= citizens[i].village_name %> -
-
- Edit -
-
- -
-
- Delete + + + +
+
+ + Items +
+
10
+
25
+
50
+
100
+
+
+
+ +
+ + + + + + + + + + + + + + + + <% for(var i=0; i < citizens.length; i++) { %> + + + + + + + + + + - - <% } %> - -
First NameLast NameAddressMobile NumberDate of BirthGenderMarital StatusVillage Name
<%= citizens[i].first_name %><%= citizens[i].last_name %><%= citizens[i].address %><%= citizens[i].mobile_num %><%= citizens[i].dob %><%= citizens[i].gender %><%= citizens[i].marital_status %><%= citizens[i].village_name %> +
+
+ Edit +
+
+ +
+
+ Delete +
- -
+
+ +
+ <%- include('partials/scripts') %> + -- cgit v1.2.3