working full text search

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') %>
 
     <!-- Drop down for selecting limit -->
-
+    <% if(citizens.length){ %>
     <div class="ui container">
       <div class="ui floating labeled icon dropdown button">
         <i class="list icon"></i>
@@ -85,7 +85,11 @@
         </div>
       </div>
     </div>
-
+    <% } %>
+    <% if(!citizens.length){ %>
+      <h1 style="text-align: center;">No Data Found</h1>
+    <% } %>
+    <% if(citizens.length){ %>
     <div class="scrollY">
       <table class="ui selectable table">
         <thead>
@@ -154,6 +158,7 @@
         </tfoot>
       </table>
     </div>
+    <% } %>
   </body>
   <%- include('partials/scripts') %>
   <script>
diff --git a/interface/views/partials/navbar.ejs b/interface/views/partials/navbar.ejs
index 69abbc6..9585454 100644
--- a/interface/views/partials/navbar.ejs
+++ b/interface/views/partials/navbar.ejs
@@ -64,7 +64,7 @@
   <div class="right menu">
     <div class="item">
       <div class="ui icon input">
-        <input type="text" placeholder="Search..." />
+        <input type="text" placeholder="Search..." id="searchInput"/>
         <i class="search link icon"></i>
       </div>
     </div>
diff --git a/interface/views/partials/scripts.ejs b/interface/views/partials/scripts.ejs
index 1f0e375..85051fa 100644
--- a/interface/views/partials/scripts.ejs
+++ b/interface/views/partials/scripts.ejs
@@ -10,4 +10,12 @@
 <script src='https://cdn.plot.ly/plotly-2.11.1.min.js'></script>
 <script>
   $(".ui.dropdown").dropdown();
+  $(document).ready(function() {
+    // listen for return key press on search input
+    $("#searchInput").keyup(function(event) {
+      if (event.keyCode === 13 && $("#searchInput").val() !== "") {
+        route("/search?query=" + $("#searchInput").val());
+      }
+    });
+  });
 </script>
\ No newline at end of file