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

2 files changed, 111 insertions, 1 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 })
+}
+