exports.getBeneficiaries = (limit, offset) => {

10 files changed, 535 insertions, 27 deletions

diff --git a/interface/controllers/citizens.controller.js b/interface/controllers/citizens.controller.js
index 3bec103..b611fad 100644
--- a/interface/controllers/citizens.controller.js
+++ b/interface/controllers/citizens.controller.js
@@ -15,6 +15,38 @@ exports.findXCitizens = (limit, offset) => {
   return sequelize.query(query, { type: QueryTypes.SELECT })
 };
 
+exports.getBeneficiaries = (limit, offset) => {
+  const query = `select distinct c.citizen_id,c.first_name , c.last_name ,c.gender ,cs.job_type ,
+  case when pm.schem_name is not null then 'Benifitted with Pension ' else 'Na' end as pension_schem, 
+  coalesce (sum (pt.pen_amount),0) as pension_amount_benifetted ,
+  case when em.citizen_id is not null then 'Offered Education schem' else 'NA' end as education_schem ,
+  coalesce (sum(em.amount) ,0) as education_amount_benifetted,
+  case when at2.crop_seaon  is not null then 'Benifitted with agriculture schem' else 'Na' end as agri_schem,
+  coalesce (sum(at2.amt_remitted) ,0) as agri_amount_benifetted,
+  case when lt.citizen_id  is not null then 'Benifitted with LPG ' else 'Na' end as lpg_schem,
+  case when nm.nregs_id is not null then 'Benifitted with Nregs' else 'Na' end as Nregs_schem,
+  coalesce (sum(nt.amount_remitted) ,0) as nregs_amount_benifetted,
+  case when ht.citizen_id  is not  null then 'Benifitted with health schem' else 'Na' end as Health_schem,
+  coalesce (sum(ht.amount_charged), 0) as health_amount_benifitted,
+  coalesce (sum (pt.pen_amount),0)+coalesce (sum(em.amount) ,0)+
+  coalesce (sum(at2.amt_remitted) ,0)+coalesce (sum(lt.amount_remitted) ,0)+ coalesce (sum(ht.amount_charged), 0) as total_amount_benifitted
+  from citizens c 
+  left join civil_supplies cs ON (c.citizen_id = cs.citizen_id)
+  left join pension_transaction pt on (cs.citizen_id = pt.citizen_id)
+  left join pension_master pm  on (pt.pension_schem_id = pm.pension_schem_id)
+  left join education_master em on (em.citizen_id = cs.citizen_id )
+  left join agri_trasaction at2 on (at2.citizen_id=cs.citizen_id)
+  left join lpg_transaction lt on (lt.citizen_id= c.citizen_id)
+  left join nregs_master nm on (nm.citizen_id = cs.citizen_id)
+  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 })
+
+} 
+
 exports.getCountOfCitizens = () => {
   const query = `select count(*) as count from citizens;`;
   return sequelize.query(query, { type: QueryTypes.SELECT })
@@ -76,4 +108,7 @@ exports.addNewCitizen = (citizen_id, first_name, last_name, address, mobile_num,
   return citizens.create({
     citizen_id, first_name, last_name, address, mobile_num, dob, gender, marital_status, disabled, disbaled_percentage, caste, village_id
   });
-};
\ No newline at end of file
+};
+
+
+
diff --git a/interface/controllers/dashboard.controller.js b/interface/controllers/dashboard.controller.js
new file mode 100644
index 0000000..c049df5
--- /dev/null
+++ b/interface/controllers/dashboard.controller.js
@@ -0,0 +1,75 @@
+const db = require("../models");
+
+
+// Gender distribution
+
+exports.genderDist = () => {
+    const query = `select gender, count(gender) As gender_dist
+                    from citizens
+                    group by gender`
+    
+    return db.sequelize.query(query, { type: db.sequelize.QueryTypes.SELECT })
+}
+
+// Caste
+exports.casteDist = () => {
+    const query = `select caste, count(caste) as caste_dist from citizens 
+                    group by caste`
+
+    return db.sequelize.query(query, { type: db.sequelize.QueryTypes.SELECT })
+}
+
+// Marital Status
+exports.maritalDist = () => {
+    const query = `select marital_status, count(marital_status) as marital_dist from citizens 
+                    group by marital_status`
+
+    return db.sequelize.query(query, { type: db.sequelize.QueryTypes.SELECT })
+}
+
+// Age Distribution
+exports.ageDist = () => {
+    const query = `select Age_Dist, sum(Age_count) from (
+        select *, case when age < 3  then 'Infants' 
+                    when (age > 3 AND age < 13) then 'Children'
+                    when (age > 13 AND age < 18) then 'Teenagers'
+                    when (age > 18 AND age < 25) then 'Young Adults'
+                    when (age > 25 AND age < 60) then 'Adults'
+                    else 'Seniors'
+                    end as Age_Dist
+        from (
+        select Age, count(Age) as Age_count from (
+        select date_part('year',age(dob)) as Age from citizens
+            ) A
+            group by Age
+            ) B
+            ) cc
+            group by Age_Dist`
+
+    return db.sequelize.query(query, { type: db.sequelize.QueryTypes.SELECT })
+}
+
+// citizens by District
+exports.citizensByDistrict = () => {
+    const query = `select dm.district_name, count(dm.district_id) as district_dist from citizens cs
+                    join village_master vm
+                    on cs.village_id = vm.village_id
+                    join mandal_master mm
+                    on mm.mandal_id = vm.mandal_id
+                    join district_master dm
+                    on dm.district_id  = mm.district_id
+                    group by dm.district_id`
+
+    return db.sequelize.query(query, { type: db.sequelize.QueryTypes.SELECT })
+}
+
+// Disable percentage
+exports.disablePercentage = () => {
+    const query = `select disbaled_percentage, count(disbaled_percentage) as disable_count_perc_dist 
+                    from citizens
+                    group by disbaled_percentage
+                    order by disbaled_percentage;`
+
+    return db.sequelize.query(query, { type: db.sequelize.QueryTypes.SELECT })
+}
+
diff --git a/interface/models/aggriculture_transaction.js b/interface/models/aggriculture_transaction.js
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/interface/models/aggriculture_transaction.js
diff --git a/interface/models/users.model.js b/interface/models/users.model.js
index df8b276..e6162d8 100644
--- a/interface/models/users.model.js
+++ b/interface/models/users.model.js
@@ -17,4 +17,4 @@ module.exports = (Sequelize, sequelize) => {
         }
     });
     return Users;
-}
\ No newline at end of file
+}
diff --git a/interface/public/beneficiaries.js b/interface/public/beneficiaries.js
new file mode 100644
index 0000000..4db118e
--- /dev/null
+++ b/interface/public/beneficiaries.js
@@ -0,0 +1,87 @@
+// 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", `/beneficiaries?page=${page - 1}&limit=${limit}`);
+} else {
+  pageLeft.addClass("disabled");
+}
+if (page < numberOfPages) {
+  pageRight.removeClass("disabled");
+  pageRight.attr("href", `/beneficiaries?page=${page + 1}&limit=${limit}`);
+} else {
+  pageRight.addClass("disabled");
+}
+
+function redirectToLimit(limit) {
+  window.location.href = `/beneficiaries?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", `/beneficiaries?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", `/beneficiaries?page=1&limit=${limit}`);
+      firstPage.setAttribute("id", `page1`);
+      firstPage.classList.add("item");
+      pageLeft.after(dots);
+      pageLeft.after(firstPage);
+    }
+
+    // add last page
+    if (page != numberOfPages) {
+      const lastPage = document.createElement("a");
+      lastPage.innerHTML = numberOfPages;
+      lastPage.setAttribute("href", `/beneficiaries?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", `/beneficiaries?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");
+}
\ No newline at end of file
diff --git a/interface/routes/index.js b/interface/routes/index.js
index c75c2c0..fd4a0d5 100644
--- a/interface/routes/index.js
+++ b/interface/routes/index.js
@@ -1,6 +1,7 @@
 const express = require("express");
 const router = express.Router();
-const citizensController = require("../controllers/citizens.controller");
+const dashboardController = require('../controllers/dashboard.controller');
+const citizensController = require('../controllers/citizens.controller');
 const api = require('./api');
 const citizensAPI = require('./api/citizens');
 const geographyAPI = require('./api/geography');
@@ -12,11 +13,17 @@ router.use('/api/geography', geographyAPI);
 
 
 router.get('/', (req, res) => {
-    Promise.all([citizensController.findGenderDistribution()]).then(results => {
-        const [genderDistribution] = results;
+    Promise.all([dashboardController.genderDist(), dashboardController.ageDist(), dashboardController.casteDist(),
+    dashboardController.maritalDist(), dashboardController.disablePercentage(), dashboardController.citizensByDistrict()]).then(results => {
+        const [genderDist, ageDist, casteDist, maritalDist, disableDist, citizenDist] = results;
         res.render('index', {
             title: 'Home Page',
-            genderDistribution
+            genderDist,
+            ageDist,
+            casteDist,
+            maritalDist,
+            disableDist,
+            citizenDist
         });
     });
 });
@@ -45,6 +52,19 @@ router.get("/addUser", (req, res) => {
     }
 );
 
+router.get("/beneficiaries", (req, res) => {
+    const limit = parseInt(req.query.limit, 10) || 100;
+    const page = req.query.page ? (req.query.page - 1) * limit : 0;
+    Promise.all([citizensController.getBeneficiaries(limit, page), citizensController.getCountOfCitizens()]).then(results => {
+        const [beneficiaries, count] = results;        
+        res.render('beneficiaries', {
+            title: 'Beneficiaries',
+            beneficiaries,
+            count: count[0].count,
+        });
+    });
+});
+
 
 // export the router
 module.exports = router;
diff --git a/interface/views/beneficiaries.ejs b/interface/views/beneficiaries.ejs
new file mode 100644
index 0000000..544b84c
--- /dev/null
+++ b/interface/views/beneficiaries.ejs
@@ -0,0 +1,113 @@
+<!DOCTYPE html>
+<html lang="en">
+  <head>
+    <%- include('partials/head') %>
+    <style>
+      .ui.menu {
+        margin-top: 0;
+      }
+      .scrollY {
+        max-height: calc(100vh - 8rem);
+        margin-top: 1rem;
+        overflow-y: scroll;
+      }
+      .ui.table thead tr:first-child > th {
+        position: sticky !important;
+        top: 0;
+        z-index: 2;
+      }
+      .ui.table tfoot tr:first-child > th {
+        position: sticky !important;
+        bottom: 0;
+        z-index: 2;
+      }
+    </style>
+  </head>
+
+  <body>
+    <%- include('partials/navbar') %>
+
+    <!-- Drop down for selecting limit -->
+
+    <div class="ui container">
+      <div class="ui floating labeled icon dropdown button">
+        <i class="list icon"></i>
+        <span class="text">Items</span>
+        <div class="menu">
+          <div class="item" onclick="redirectToLimit(100)">100</div>
+          <div class="item" onclick="redirectToLimit(250)">250</div>
+          <div class="item" onclick="redirectToLimit(500)">500</div>
+          <div class="item" onclick="redirectToLimit(1000)">1000</div>
+        </div>
+      </div>
+    </div>
+
+    <div class="scrollY">
+      <table class="ui selectable table">
+        <thead>
+          <tr>
+            <th>First Name</th>
+            <th>Last Name</th>
+            <th>Gender</th>
+            <th>Job type</th>
+            <th>Pension Scheme</th>
+            <th>Pension Amount</th>
+            <th>Education Scheme</th>
+            <th>Education Amount benifetted</th>
+            <th>Agriculture Scheme</th>
+            <th>Agriculture Amount benifetted</th>
+            <th>LPG Scheme</th>
+            <th>NREGS Scheme</th>
+            <th>NREGS Amount benifetted</th>
+            <th>Health scheme</th>
+            <th>Health Amount Benifitted</th>
+            <th>Total Amount Benifitted</th>
+          </tr>
+        </thead>
+        <tbody>
+          <% for(var i=0; i < beneficiaries.length; i++) { %>
+          <tr>
+            <td><%= beneficiaries[i].first_name %></td>
+            <td><%= beneficiaries[i].last_name %></td>
+            <td><%= beneficiaries[i].gender %></td>
+            <td><%= beneficiaries[i].job_type %></td>
+            <td><%= beneficiaries[i].pension_schem %></td>
+            <td><%= beneficiaries[i].pension_amount_benifetted %></td>
+            <td><%= beneficiaries[i].education_schem %></td>
+            <td><%= beneficiaries[i].education_amount_benifetted %></td>
+            <td><%= beneficiaries[i].agri_schem %></td>
+            <td><%= beneficiaries[i].agri_amount_benifetted %></td>
+            <td><%= beneficiaries[i].lpg_schem %></td>
+            <td><%= beneficiaries[i].nregs_schem %></td>
+            <td><%= beneficiaries[i].nregs_amount_benifetted %></td>
+            <td><%= beneficiaries[i].health_schem %></td>
+            <td><%= beneficiaries[i].health_amount_benifitted %></td>
+            <td><%= beneficiaries[i].total_amount_benifitted %></td>
+          </tr>
+          <% } %>
+        </tbody>
+        <!-- Display a pagination -->
+        <tfoot>
+          <tr>
+            <th colspan="16">
+              <div class="ui right floated pagination menu">
+                <a class="icon item" id="pageLeft">
+                  <i class="left chevron disabled icon"></i>
+                </a>
+
+                <a class="icon item" id="pageRight">
+                  <i class="right chevron disabled icon"></i>
+                </a>
+              </div>
+            </th>
+          </tr>
+        </tfoot>
+      </table>
+    </div>
+  </body>
+  <%- include('partials/scripts') %>
+  <script>
+    const count = "<%= count %>";
+  </script>
+  <script src="/beneficiaries.js"></script>
+</html>
diff --git a/interface/views/index.ejs b/interface/views/index.ejs
index ffd77e6..eebd413 100644
--- a/interface/views/index.ejs
+++ b/interface/views/index.ejs
@@ -39,27 +39,24 @@
     </div>
     <%- include('partials/navbar') %>
     <div class="ui main container segment">
-      <div class="ui cards">
-        <div class="card">
-          <div class="content">
-            <div class="header">Citizens</div>
-            <div class="description">
-              <pre>
-                Gender Statistics:<br>
-                =====================<br>
-                Male: <%= genderDistribution[0].genderCount %><br>
-                Female: <%= genderDistribution[1].genderCount %><br>
-                ---------------------<br>
-                Total: <%= genderDistribution[0].genderCount + genderDistribution[1].genderCount %>
-              </pre>
-              Provides the details of all the citizens in the database
-            </div>
-          </div>
-          <div class="extra content">
-            <div class="ui basic green button" onclick="route('citizens')">
-              View Data
-            </div>
-          </div>
+      <div class="ui three column grid">
+        <div class="column">
+          <div class="ui cards" id="genderdist"></div>
+        </div>
+        <div class="column">
+          <div class="ui cards" id="agedist"></div>
+        </div>
+        <div class="column">
+          <div class="ui cards" id="castedist"></div>
+        </div>
+        <div class="column">
+          <div class="ui cards" id="maritaldist"></div>
+        </div>
+        <div class="column">
+          <div class="ui cards" id="disabledist"></div>
+        </div>
+        <div class="column">
+          <div class="ui cards" id="citizendist"></div>
         </div>
       </div>
     </div>
@@ -69,4 +66,183 @@
     $(".login.modal").modal("attach events", ".loginButton", "show");
   </script>
   <script src="/login.js"></script>
+
+  <script>
+    function generateColors(n) {
+      var colors = [];
+      for (var i = 0; i < n; i++) {
+        colors.push(
+          "rgb(" +
+            Math.floor(Math.random() * 256) +
+            "," +
+            Math.floor(Math.random() * 256) +
+            "," +
+            Math.floor(Math.random() * 256) +
+            ")"
+        );
+      }
+      return colors;
+    }
+
+    const genderDist = JSON.parse(`<%- JSON.stringify(genderDist) %>`);
+
+    var trace1 = {
+      x: ["Male", "Female"],
+      y: [genderDist[0].gender_dist, genderDist[1].gender_dist],
+      type: "bar",
+      marker: {
+        color: generateColors(2),
+      },
+    };
+    var data = [trace1];
+    var layout = {
+      title: "Gender Distibution of all Citizens",
+      font: { size: 12 },
+    };
+    var config = { responsive: true };
+    Plotly.newPlot("genderdist", data, layout, config);
+
+    const ageDist = JSON.parse(`<%- JSON.stringify(ageDist) %>`);
+
+    var x = [];
+    var y = [];
+    for (var i = 0; i < ageDist.length; i++) {
+      x.push(ageDist[i].age_dist);
+      y.push(ageDist[i].sum);
+    }
+    var trace2 = {
+      x: x,
+      y: y,
+      type: "bar",
+      marker: {
+        color: generateColors(x.length),
+      },
+    };
+    var data = [trace2];
+    var layout = {
+      title: "Age Distibution of all Citizens",
+      font: { size: 12 },
+    };
+    var config = { responsive: true };
+    Plotly.newPlot("agedist", data, layout, config);
+
+    // caste dist
+    const casteDist = JSON.parse(`<%- JSON.stringify(casteDist) %>`);
+
+    var x = [];
+    var y = [];
+    for (var i = 0; i < casteDist.length; i++) {
+      x.push(casteDist[i].caste);
+      y.push(casteDist[i].caste_dist);
+    }
+    // plot pie chart
+    var trace3 = {
+      labels: x,
+      values: y,
+      type: "pie",
+      marker: {
+        color: generateColors(x.length),
+      },
+    };
+    var data = [trace3];
+    var layout = {
+      title: "Caste Distibution of all Citizens",
+      font: { size: 12 },
+    };
+    var config = { responsive: true };
+    Plotly.newPlot("castedist", data, layout, config);
+
+    // marital dist
+    const maritalDist = JSON.parse(`<%- JSON.stringify(maritalDist) %>`);
+
+    
+    var x = [];
+    var y = [];
+    for (var i = 0; i < maritalDist.length; i++) {
+      x.push(maritalDist[i].marital_status);
+      y.push(maritalDist[i].marital_dist);
+    }
+    // plot pie chart
+    var trace4 = {
+      labels: x,
+      values: y,
+      type: "pie",
+      marker: {
+        color: generateColors(x.length),
+      },
+    };
+    var data = [trace4];
+    var layout = {
+      title: "Marital Status Distibution of all Citizens",
+      font: { size: 12 },
+    };
+    var config = { responsive: true };
+    Plotly.newPlot("maritaldist", data, layout, config);
+
+    // disabled dist
+    const disabledDist = JSON.parse(`<%- JSON.stringify(disableDist) %>`);
+
+    var x = [];
+    var y = [];
+    for (var i = 0; i < disabledDist.length; i++) {
+      x.push(disabledDist[i].disable_count_perc_dist);
+      y.push(disabledDist[i].disbaled_percentage);
+    }
+    // plot Donut Chart
+    var trace5 = {
+      labels: y,
+      values: x,
+      type: "pie",
+      marker: {
+        color: generateColors(x.length),
+      },
+      hole: 0.4,
+    };
+    var data = [trace5];
+    var layout = {
+      title: "Disabled Distibution of all Citizens",
+      font: { size: 12 },
+    };
+    var config = { responsive: true };
+    Plotly.newPlot("disabledist", data, layout, config);
+
+    // citizen dist
+
+    function generateBubbleSize(n) {
+      var sizes = [];
+      for (var i = 0; i < n.length; i++) {
+        sizes.push(Math.random() * 100);
+      }
+      return sizes;
+    }
+
+    const citizenDist = JSON.parse(`<%- JSON.stringify(citizenDist) %>`);
+
+    var x = [];
+    var y = [];
+    for (var i = 0; i < citizenDist.length; i++) {
+      x.push(citizenDist[i].district_name);
+      y.push(citizenDist[i].district_dist);
+    }
+
+    // plot bubble chart 
+    var trace6 = {
+      x: x,
+      y: y,
+      mode: "markers",
+      marker: {
+        color: generateColors(x.length),
+        size: generateBubbleSize(y),
+      },
+    };
+    var data = [trace6];
+    var layout = {
+      title: "Citizen Distibution of all Citizens",
+      font: { size: 12 },
+    };
+    var config = { responsive: true };
+    Plotly.newPlot("citizendist", data, layout, config);
+
+
+  </script>
 </html>
diff --git a/interface/views/partials/navbar.ejs b/interface/views/partials/navbar.ejs
index 967cbcb..30289d2 100644
--- a/interface/views/partials/navbar.ejs
+++ b/interface/views/partials/navbar.ejs
@@ -43,6 +43,7 @@
     </div>
   </div>
 
+  <a class="item" onclick="route('beneficiaries')"> Scheme Wise Beneficiaries </a>
   <a class="item" onclick="route('addUser')"> Add User </a>
 
   <div class="right menu">
diff --git a/interface/views/partials/scripts.ejs b/interface/views/partials/scripts.ejs
index 0055b8e..1f0e375 100644
--- a/interface/views/partials/scripts.ejs
+++ b/interface/views/partials/scripts.ejs
@@ -7,6 +7,7 @@
 <script src="/verify.js"></script>
 <script src="/router.js"></script>
 <script src="/logout.js"></script>
+<script src='https://cdn.plot.ly/plotly-2.11.1.min.js'></script>
 <script>
   $(".ui.dropdown").dropdown();
 </script>
\ No newline at end of file