From ef7e7f9ee49f9077ed9a33ca715fc904d8f0153f Mon Sep 17 00:00:00 2001 From: Bobby Date: Wed, 4 May 2022 06:31:36 -0400 Subject: working full text search --- interface/controllers/citizens.controller.js | 103 +++++++++++++++++---------- interface/public/citizens.js | 25 ++++--- interface/routes/index.js | 20 ++++++ interface/server.js | 2 +- interface/views/citizens.ejs | 9 ++- interface/views/partials/navbar.ejs | 2 +- interface/views/partials/scripts.ejs | 8 +++ 7 files changed, 117 insertions(+), 52 deletions(-) diff --git a/interface/controllers/citizens.controller.js b/interface/controllers/citizens.controller.js index b611fad..ae0204e 100644 --- a/interface/controllers/citizens.controller.js +++ b/interface/controllers/citizens.controller.js @@ -1,9 +1,8 @@ const db = require("../models"); const citizens = db.citizens; -const {QueryTypes} = require('sequelize'); +const { QueryTypes } = require("sequelize"); const { sequelize } = require("../models"); - // Retrieve all citizens from the database. Limit the number of citizens returned to 10. 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 @@ -12,7 +11,7 @@ exports.findXCitizens = (limit, offset) => { on c.village_id = v.village_id order by citizen_id limit ${limit} offset ${offset};`; - return sequelize.query(query, { type: QueryTypes.SELECT }) + return sequelize.query(query, { type: QueryTypes.SELECT }); }; exports.getBeneficiaries = (limit, offset) => { @@ -41,50 +40,44 @@ exports.getBeneficiaries = (limit, offset) => { left join nregs_transaction nt on (nm.nregs_id=nt.nregs_id) left join hospital_transaction ht on (cs.citizen_id = ht.citizen_id) group by c.citizen_id,c.first_name , c.last_name ,c.gender - ,cs.job_type ,pension_schem,education_schem,agri_schem, lpg_schem,Nregs_schem,Health_schem limit ${limit} offset ${offset};` - - return sequelize.query(query, { type: QueryTypes.SELECT }) + ,cs.job_type ,pension_schem,education_schem,agri_schem, lpg_schem,Nregs_schem,Health_schem 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 }) -} - + return sequelize.query(query, { type: QueryTypes.SELECT }); +}; -exports.deleteCitizenbyId = (citizen_id) =>{ - return citizens.destroy({ - where: { citizen_id } - }) +exports.deleteCitizenbyId = (citizen_id) => { + const query = `delete from citizens where citizen_id = ${citizen_id};`; + return sequelize.query(query, { type: QueryTypes.DELETE }); }; -exports.editCitizen = (citizen_id, address, mobile_num, dob, marital_status) => { - return citizens.update({ - address, mobile_num, dob, marital_status - }, { - where: { - citizen_id - } - }); +exports.editCitizen = ( + citizen_id, + address, + mobile_num, + dob, + marital_status +) => { + const query = `update citizens set address = '${address}', mobile_num = '${mobile_num}', dob = '${dob}', marital_status = '${marital_status}' where citizen_id = ${citizen_id};`; + return sequelize.query(query, { type: QueryTypes.UPDATE }); }; //Check Citizen exists or not exports.checkCitizenId = (citizen_id) => { - return citizens.findOne({ - where: { - citizen_id - } - }).then( - citizen_id => { + const query = `select * from citizens where citizen_id = ${citizen_id};`; + return sequelize + .query(query, { type: QueryTypes.SELECT }) + .then((citizen_id) => { if (citizen_id) { return true; } return false; - } - ) -} - + }); +}; // Get total number of male and female citizens exports.findGenderDistribution = () => { @@ -98,17 +91,53 @@ exports.findGenderDistribution = () => { return citizens.findAll({ group: ["gender"], attributes: ["gender", [db.sequelize.fn("COUNT", "gender"), "genderCount"]], - raw: true + raw: true, }); }; - // add new citizen -exports.addNewCitizen = (citizen_id, first_name, last_name, address, mobile_num, dob, gender, marital_status, disabled, disbaled_percentage, caste, village_id) => { +exports.addNewCitizen = ( + citizen_id, + first_name, + last_name, + address, + mobile_num, + dob, + gender, + marital_status, + disabled, + disbaled_percentage, + caste, + village_id +) => { return citizens.create({ - citizen_id, first_name, last_name, address, mobile_num, dob, gender, marital_status, disabled, disbaled_percentage, caste, village_id + citizen_id, + first_name, + last_name, + address, + mobile_num, + dob, + gender, + marital_status, + disabled, + disbaled_percentage, + caste, + village_id, }); }; +exports.searchCitizens = (query, limit, offset) => { + const searchQuery = `SELECT * FROM citizens JOIN village_master on + citizens.village_id = village_master.village_id + WHERE to_tsvector(f_concat_ws(' ', first_name, last_name)) + @@ plainto_tsquery('${query}') limit ${limit} offset ${offset};`; + return sequelize.query(searchQuery, { type: QueryTypes.SELECT }); +} - +exports.countSearchedCitizens = (query) => { + const searchQuery = `SELECT count(*) FROM citizens JOIN village_master + on citizens.village_id = village_master.village_id + WHERE to_tsvector(f_concat_ws(' ', first_name, last_name)) + @@ plainto_tsquery('${query}');`; + return sequelize.query(searchQuery, { type: QueryTypes.SELECT }); +} diff --git a/interface/public/citizens.js b/interface/public/citizens.js index 8313765..ea00aa5 100644 --- a/interface/public/citizens.js +++ b/interface/public/citizens.js @@ -12,25 +12,26 @@ const queryString = window.location.search; const urlParams = new URLSearchParams(queryString); const page = urlParams.get("page") || 1; const limit = urlParams.get("limit") || 10; +const query = urlParams.get("query") || ""; 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=${parseInt(page) - 1}&limit=${limit}`); + pageLeft.attr("href", `${window.location.pathname}?page=${parseInt(page) - 1}&limit=${limit}&query=${query}`); } else { pageLeft.addClass("disabled"); } if (page < numberOfPages) { pageRight.removeClass("disabled"); - pageRight.attr("href", `/citizens?page=${parseInt(page) + 1}&limit=${limit}`); + pageRight.attr("href", `${window.location.pathname}?page=${parseInt(page) + 1}&limit=${limit}&query=${query}`); } else { pageRight.addClass("disabled"); } function redirectToLimit(limit) { - window.location.href = `/citizens?page=1&limit=${limit}`; + window.location.href = `${window.location.pathname}?page=1&limit=${limit}&query=${query}`; } addPageNumbers(numberOfPages); @@ -49,7 +50,7 @@ function addPageNumbers(numberOfPages) { 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("href", `${window.location.pathname}?page=${i}&limit=${limit}&query=${query}`); linkElement.setAttribute("id", `page${i}`); linkElement.classList.add("item"); pageRight.before(linkElement); @@ -64,7 +65,7 @@ function addPageNumbers(numberOfPages) { if (page > 3) { const firstPage = document.createElement("a"); firstPage.innerHTML = 1; - firstPage.setAttribute("href", `/citizens?page=1&limit=${limit}`); + firstPage.setAttribute("href", `${window.location.pathname}?page=1&limit=${limit}&query=${query}`); firstPage.setAttribute("id", `page1`); firstPage.classList.add("item"); pageLeft.after(dots); @@ -75,7 +76,7 @@ function addPageNumbers(numberOfPages) { if (page != numberOfPages) { const lastPage = document.createElement("a"); lastPage.innerHTML = numberOfPages; - lastPage.setAttribute("href", `/citizens?page=${numberOfPages}&limit=${limit}`); + lastPage.setAttribute("href", `${window.location.pathname}?page=${numberOfPages}&limit=${limit}&query=${query}`); lastPage.classList.add("item"); pageRight.before(lastPage); } @@ -85,14 +86,16 @@ function addPageNumbers(numberOfPages) { // Insert before pageRight const linkElement = document.createElement("a"); linkElement.innerHTML = i; - linkElement.setAttribute("href", `/citizens?page=${i}&limit=${limit}`); + linkElement.setAttribute("href", `${window.location.pathname}?page=${i}&limit=${limit}&query=${query}`); linkElement.setAttribute("id", `page${i}`); linkElement.classList.add("item"); pageRight.before(linkElement); } } - const currentPageElement = document.getElementById(`page${page}`); - currentPageElement.classList.add("active"); + const currentPageElement = document.getElementById(`page${page}&query=${query}`); + try { + currentPageElement.classList.add("active"); + } catch (error) {} } function editCitizensRecord(citizen) { @@ -119,7 +122,7 @@ function editCitizen(event) { citizen_id: $("#citizen_id").html(), }; $.ajax({ - url: "/api/citizens/edit", + url: "/api${window.location.pathname}/edit", type: "POST", data: data, success: function (response) { @@ -133,7 +136,7 @@ function editCitizen(event) { function deleteCitizenRecord(citizen) { citizen_id = JSON.parse(citizen).citizen_id; $.ajax({ - url: "/api/citizens/delete", + url: "/api${window.location.pathname}/delete", type: "POST", data: { citizen_id }, success: function (response) { diff --git a/interface/routes/index.js b/interface/routes/index.js index 5b3ad05..1068941 100644 --- a/interface/routes/index.js +++ b/interface/routes/index.js @@ -61,6 +61,26 @@ router.get("/citizens", (req, res) => { }); }); +router.get("/search", (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; + const query = req.query.query; + + // Get the citizens from the database + Promise.all([ + citizensController.searchCitizens(query, limit, page), + citizensController.countSearchedCitizens(query), + ]).then((results) => { + const [citizens, count] = results; + res.render("citizens", { + title: `Search results for "${query}"`, + citizens, + count: count[0].count, + }); + }); +}); + router.get("/addUser", (req, res) => { res.render("addUser", { title: "Add User", diff --git a/interface/server.js b/interface/server.js index 4173d2f..5736d4c 100644 --- a/interface/server.js +++ b/interface/server.js @@ -2,7 +2,7 @@ const express = require("express"); const app = express(); const cors = require("cors"); const db = require("./models"); -db.sequelize.sync(); +// db.sequelize.sync(); app.use(cors()); app.use(express.json()); diff --git a/interface/views/citizens.ejs b/interface/views/citizens.ejs index 2cb4ecf..5513bc3 100644 --- a/interface/views/citizens.ejs +++ b/interface/views/citizens.ejs @@ -72,7 +72,7 @@ <%- include('partials/navbar') %> - + <% if(citizens.length){ %>
@@ -85,7 +85,11 @@
- + <% } %> + <% if(!citizens.length){ %> +

No Data Found

+ <% } %> + <% if(citizens.length){ %>
@@ -154,6 +158,7 @@
+ <% } %> <%- include('partials/scripts') %> \ No newline at end of file -- cgit v1.2.3