1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
|
const db = require("../models");
const citizens = db.citizens;
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
from citizens c
join village_master v
on c.village_id = v.village_id
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) =>{
return citizens.destroy({
where: { citizen_id }
})
};
exports.editCitizen = (citizen_id, address, mobile_num, dob, marital_status) => {
return citizens.update({
address, mobile_num, dob, marital_status
}, {
where: {
citizen_id
}
});
};
//Check Citizen exists or not
exports.checkCitizenId = (citizen_id) => {
return citizens.findOne({
where: {
citizen_id
}
}).then(
citizen_id => {
if (citizen_id) {
return true;
}
return false;
}
)
}
// Get total number of male and female citizens
exports.findGenderDistribution = () => {
// group by the 'gender' column
/**
* This code is equivalent to the following SQL query:
* select count(gender), gender from citizens group by gender ;
*/
return citizens.findAll({
group: ["gender"],
attributes: ["gender", [db.sequelize.fn("COUNT", "gender"), "genderCount"]],
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) => {
return citizens.create({
citizen_id, first_name, last_name, address, mobile_num, dob, gender, marital_status, disabled, disbaled_percentage, caste, village_id
});
};
|
