/* 
 * Click nbfs://nbhost/SystemFileSystem/Templates/Licenses/license-default.txt to change this license
 * Click nbfs://nbhost/SystemFileSystem/Templates/ClientSide/javascript.js to edit this template
 */

var express = require('express');
var router = express.Router();
var session;

//DATE_FORMAT(tf.project_start_date, "%d %b %Y") AS start_date,DATE_FORMAT(tf.project_estimate_completion_date, "%d %b %Y") AS estimate_date,DATE_FORMAT(tf.project_end_date, "%d %b %Y") AS end_date

/* GET home page. */
var all_taluka_list = "";
router.get('/', function (req, res, next) {

    session = req.session;
    console.log('sess.username ', session.username);
    if (session.username) {
        var username = session.username;
        var User_language = session.User_language;
        var username = session.username;
        var dept_id = session.dept_id;
        var user_type = session.user_type;
        console.log('user_type ', user_type);
        req.getConnection(function (err, connection) {
            if (err)
            {
                return console.log(err.message);
            } else
            {
//                all_taluka_list="";
//                if (user_type == 'ADMIN') {
//                    get_taluka_data(req, res);
//                    var query = connection.query("select count(id) as cnt_id FROM tbl_project where flag=1", function (err, rows)
//                    {
//                        if (err)
//                            console.log("Error Selecting : %s ", err);
//                        console.log('rows ' + rows[0]['cnt_id']);
//                        var total_project = rows[0]['cnt_id'];
//                        var query = connection.query("select count(id) as cnt_a_id FROM tbl_project where project_status='Working' and flag=1", function (err, rows)
//                        {
//                            if (err)
//                                console.log("Error Selecting : %s ", err);
//                            console.log('rows ' + rows[0]['cnt_a_id']);
//                            var total_Working_project = rows[0]['cnt_a_id'];
//                            var query = connection.query("select count(id) as cnt_accident FROM tbl_project where project_status='Complete' and flag=1", function (err, rows)
//                            {
//                                if (err)
//                                    console.log("Error Selecting : %s ", err);
//                                console.log('rows ' + rows[0]['cnt_accident']);
//                                var total_Complete_project = rows[0]['cnt_accident'];
//
//                                var query = connection.query("SELECT COUNT(tp.id) as cnt,tps.sector_name FROM `tbl_project` as tp left join tbl_project_sector as tps on tps.id=tp.project_sector_id GROUP BY tp.project_sector_id", function (err, project_sector)
//                                {
//                                    if (err)
//                                        console.log("Error Selecting : %s ", err);
//                                    var query = connection.query('select tf.*,DATE_FORMAT(tf.project_start_date, "%d %b %Y") AS start_date,DATE_FORMAT(tf.project_estimate_completion_date, "%d %b %Y") AS estimate_date,DATE_FORMAT(tf.project_end_date, "%d %b %Y") AS end_date,td1.district,tt.taluka,tv.village as village_name,to1.officer_name,tps.sector_name,tyy.yojana_year FROM tbl_project as tf left join tbl_district as td1 on td1.id=tf.district_id left join tbl_taluka as tt on tt.id=tf.taluka_id left join tbl_officer as to1 on to1.id=tf.officer_id left join tbl_project_sector as tps on tps.id=tf.project_sector_id left join tbl_yojana_year as tyy on tyy.id=tf.project_year_id LEFT JOIN tbl_village as tv on tv.id=tf.village_id WHERE tf.flag=1 ORDER by tf.id desc limit 20', function (err, rows)
//                                    {
//                                        if (err)
//                                            console.log("Error Selecting : %s ", err);
//                                        
//                                        console.log('in all_taluka_list ');
//                                        console.log(all_taluka_list);
//                                        res.render('user_dashboard', {title: 'Express', total_project: total_project, 'total_Working_project': total_Working_project, 'total_Complete_project': total_Complete_project, 'rows': rows, 'project_sector': project_sector, 'User_language': User_language, 'username': username, 'user_type': user_type, 'all_taluka_list': all_taluka_list});
//                                        res.end;
//                                    });
//                                });
//                            });
//                        });
//                    });
//
//                } else if (user_type == 'MAIN_USER' || user_type == 'SITE_USER') {
//
//                    var query = connection.query("SELECT count(td.id) as cnt_id FROM `tbl_project` as tp left JOIN tbl_project_sector as tps on tps.id=tp.project_sector_id left join tbl_department as td on td.id=tps.dept_id WHERE td.id=? and td.flag=1", [dept_id], function (err, rows)
//                    {
//                        if (err)
//                            console.log("Error Selecting : %s ", err);
//                        console.log('rows ' + rows[0]['cnt_id']);
//                        var total_project = rows[0]['cnt_id'];
//                        var query = connection.query("SELECT count(td.id) as cnt_a_id FROM `tbl_project` as tp left JOIN tbl_project_sector as tps on tps.id=tp.project_sector_id left join tbl_department as td on td.id=tps.dept_id WHERE td.id=? and project_status='Working' and td.flag=1", [dept_id], function (err, rows)
//                        {
//                            if (err)
//                                console.log("Error Selecting : %s ", err);
//                            console.log('rows ' + rows[0]['cnt_a_id']);
//                            var total_Working_project = rows[0]['cnt_a_id'];
//                            var query = connection.query("SELECT count(td.id) as cnt_accident FROM `tbl_project` as tp left JOIN tbl_project_sector as tps on tps.id=tp.project_sector_id left join tbl_department as td on td.id=tps.dept_id WHERE td.id=? and project_status='Complete' and td.flag=1", [dept_id], function (err, rows)
//                            {
//                                if (err)
//                                    console.log("Error Selecting : %s ", err);
//                                console.log('rows ' + rows[0]['cnt_accident']);
//                                var total_Complete_project = rows[0]['cnt_accident'];
//
//                                var query = connection.query("SELECT COUNT(tp.id) as cnt,tps.sector_name FROM `tbl_project` as tp left join tbl_project_sector as tps on tps.id=tp.project_sector_id  left join tbl_department as td on td.id=tps.dept_id where td.id=? GROUP BY tp.project_sector_id", [dept_id], function (err, project_sector)
//                                {
//                                    if (err)
//                                        console.log("Error Selecting : %s ", err);
//                                    var query = connection.query('select tf.*,DATE_FORMAT(tf.project_start_date, "%d %b %Y") AS start_date,DATE_FORMAT(tf.project_estimate_completion_date, "%d %b %Y") AS estimate_date,DATE_FORMAT(tf.project_end_date, "%d %b %Y") AS end_date,td1.district,tt.taluka,tv.village as village_name,to1.officer_name,tps.sector_name,tyy.yojana_year FROM tbl_project as tf left join tbl_district as td1 on td1.id=tf.district_id left join tbl_taluka as tt on tt.id=tf.taluka_id left join tbl_officer as to1 on to1.id=tf.officer_id left join tbl_project_sector as tps on tps.id=tf.project_sector_id left join tbl_department as td on td.id=tps.dept_id left join tbl_yojana_year as tyy on tyy.id=tf.project_year_id LEFT JOIN tbl_village as tv on tv.id=tf.village_id WHERE tf.flag=1 and td.id=? ORDER by tf.id desc limit 20', [dept_id], function (err, rows)
//                                    {
//                                        if (err)
//                                            console.log("Error Selecting : %s ", err);
////                                        console.log('rows ' + rows[0]['cnt_public_user']);
//                                        res.render('user_dashboard', {title: 'Express', total_project: total_project, 'total_Working_project': total_Working_project, 'total_Complete_project': total_Complete_project, 'rows': rows, 'project_sector': project_sector, 'User_language': User_language, 'username': username, 'user_type': user_type});
//                                        res.end;
//                                    });
//                                });
//                            });
//                        });
//                    });
//
//                } else if (user_type == 'SITE_USER') {
//
//                }


                res.render('user_dashboard', {title: 'Express', total_project: 0, 'total_Working_project': 0, 'total_Complete_project': 0, 'rows': 0, 'project_sector': 0, 'User_language': User_language, 'username': username, 'user_type': user_type});
                res.end;
            }
        });
    } else
    {
        return res.redirect('/');
    }
});

async function get_taluka_data(req, res) {

    for (var i = 1; i < 9; i++) {
        req.getConnection(function (err, connection) {
            if (err)
            {
                return console.log(err.message);
            } else
            {
                var query = connection.query('SELECT COUNT(tt.id) as cnt,tp.* FROM `tbl_taluka` as tt LEFT join tbl_project as tp on tp.taluka_id=tt.id WHERE tt.flag=1 and tt.id=?', [i], function (err, rows_data)
                {
                    if (err)
                        console.log("Error Selecting : %s ", err);
                    console.log(query.sql);
                    console.log('rows_data ' + rows_data[0]['cnt']);
                    if (rows_data.length == '1')
                    {
                        var id_is = rows_data[0]['id'];
                        console.log('id_is ' + id_is);
                        if (id_is == null) {
                            console.log(' if id_is ' + id_is);
                            all_taluka_list += ",0";
                        } else {
                            console.log(' else id_is ' + id_is);
                            if (all_taluka_list == "") {
                                all_taluka_list = rows_data[0]['cnt'];
                            } else {
                                all_taluka_list += "," + rows_data[0]['cnt'];
                            }
                        }

                    } else if (rows_data.length > 1) {
                        if (all_taluka_list == "") {
                            all_taluka_list = rows_data[0]['cnt'];
                        } else {
                            all_taluka_list += "," + rows_data[0]['cnt'];
                        }
                    } else {
                        if (all_taluka_list == "") {
                            all_taluka_list = "0";
                        } else {
                            all_taluka_list += ",0";
                        }
                    }
                });
            }
        });
//        await waitforme(100);
    }
    console.log('all_taluka_list');
    console.log(all_taluka_list);
}

async function get_taluka_data_department(req, res) {

    for (var i = 1; i < 9; i++) {
        req.getConnection(function (err, connection) {
            if (err)
            {
                return console.log(err.message);
            } else
            {
//                SELECT COUNT(tt.id) as cnt,tp.* FROM `tbl_taluka` as tt LEFT join tbl_project as tp on tp.taluka_id=tt.id LEFT join tbl_project_sector as tps on tps.id=tp.project_sector_id WHERE tt.flag=1 and tt.id=1 and tps.dept_id=2
                var query = connection.query('SELECT COUNT(tt.id) as cnt,tp.* FROM `tbl_taluka` as tt LEFT join tbl_project as tp on tp.taluka_id=tt.id WHERE tt.flag=1 and tt.id=?', [i], function (err, rows_data)
                {
                    if (err)
                        console.log("Error Selecting : %s ", err);
                    console.log(query.sql);
                    console.log('rows_data ' + rows_data[0]['cnt']);
                    if (rows_data.length == '1')
                    {
                        var id_is = rows_data[0]['id'];
                        console.log('id_is ' + id_is);
                        if (id_is == null) {
                            console.log(' if id_is ' + id_is);
                            all_taluka_list += ",0";
                        } else {
                            console.log(' else id_is ' + id_is);
                            if (all_taluka_list == "") {
                                all_taluka_list = rows_data[0]['cnt'];
                            } else {
                                all_taluka_list += "," + rows_data[0]['cnt'];
                            }
                        }

                    } else if (rows_data.length > 1) {
                        if (all_taluka_list == "") {
                            all_taluka_list = rows_data[0]['cnt'];
                        } else {
                            all_taluka_list += "," + rows_data[0]['cnt'];
                        }
                    } else {
                        if (all_taluka_list == "") {
                            all_taluka_list = "0";
                        } else {
                            all_taluka_list += ",0";
                        }
                    }
                });
            }
        });
//        await waitforme(100);
    }
    console.log('all_taluka_list');
    console.log(all_taluka_list);
}

function waitforme(milisec) {
    return new Promise(resolve => {
        setTimeout(() => {
            resolve('');
        }, milisec);
    });
}

router.logout = function (req, res) {
    req.session.destroy(function (err) {
        if (err) {
            return console.log(err);
        }
        return res.redirect('/');
    });
};

router.registration = function (req, res) {
    req.getConnection(function (err, connection) {
        if (err)
        {
            return console.log(err.message);
        } else
        {
            var query = connection.query('select * FROM tbl_department WHERE flag=1 ORDER by id asc', function (err, department)
            {
                if (err)
                    console.log("Error Selecting : %s ", err);
                res.render('registration', {title: 'Express', department: department});
                res.end;
            });
        }
//        return res.render('registration', {title: 'Express'});
    });
}

router.change_pasword = function (req, res) {
    session = req.session;
//    console.log('sess.username ',session.username);
    if (session.username) {
        var User_language = session.User_language;
        var username = session.username;
        var dept_id = session.dept_id;
        var user_type = session.user_type;

        return res.render('change_pasword', {title: 'Express', 'User_language': User_language, 'username': username, 'user_type': user_type});
    } else
    {
        return res.redirect('/');
    }
};
router.change_admin_password = function (req, res) {
    var input = JSON.parse(JSON.stringify(req.body));
    req.getConnection(function (err, connection) {
        if (err)
        {
            return console.log(err.message);
        } else
        {
            var username = input.username;
            var old_password = input.old_password;
            var new_password = input.new_password;
            var confirm_password = input.confirm_password;
            console.log('username ', username);
//            console.log('passsword ', password);

            var query = connection.query("SELECT * FROM tbl_login_admin WHERE username='" + username + "' and password='" + old_password + "'", function (err, user_data)
            {
                if (err)
                    console.log("Error inserting : %s ", err);
                console.log('user_data ', user_data);
                if (user_data != '')
                {
                    var data = {
                        password: new_password
                    };
                    var query = connection.query("UPDATE tbl_login_admin set ? WHERE username = ? ", [data, username], function (err, rows)
                    {
                        if (err)
                            console.log("Error Selecting : %s ", err);
                        res.writeHead(200, {'Content-Type': 'text/plain'});
                        res.end('LOGIN');
                        res.end;
                    });
                } else
                {
                    res.writeHead(200, {'Content-Type': 'text/plain'});
                    res.end('NOT_LOGIN');
                    res.end;
                }
            });
//            
        }
    });
};
router.set_session_user_language = function (req, res, next) {
    session = req.session;
    console.log('sess.username ', session.username);
    if (session.username) {
        req.getConnection(function (err, connection) {
            var input = JSON.parse(JSON.stringify(req.body));
            var User_language = input.User_language;
            var username = session.username;
            console.log('User_language ', User_language);
//            session = req.session;
            session.User_language = User_language;
            console.log('session.User_language ', session.User_language);
            res.send(session.User_language);
            res.end;
        });
    } else
    {
        return res.redirect('/');
    }
}

router.user_registration = function (req, res) {
    var input = JSON.parse(JSON.stringify(req.body));
    req.getConnection(function (err, connection) {
        if (err)
        {
            return console.log(err.message);
        } else
        {
            var fullname = input.fullname;
            var email = input.email;
            var select_usertype = input.select_usertype;
            var username = input.username;
            var password = input.password;
            var dept_id = input.dept_id;
            console.log('username ', username);
            console.log('passsword ', password);
            const ts = new Date().toLocaleString('en-US', {
                timeZone: 'Asia/Calcutta'
            });
//            let ts = Date.now();
            let date_ob = new Date(ts);
            let date = date_ob.getDate();
            let month = date_ob.getMonth() + 1;
            let year = date_ob.getFullYear();
            if (month == '1') {
                month = "01";
            } else if (month == '2') {
                month = "02";
            } else if (month == '3') {
                month = "03";
            } else if (month == '4') {
                month = "04";
            } else if (month == '5') {
                month = "05";
            } else if (month == '6') {
                month = "06";
            } else if (month == '7') {
                month = "07";
            } else if (month == '8') {
                month = "08";
            } else if (month == '9') {
                month = "09";
            }

            if (date == '1') {
                date = "01";
            } else if (date == '2') {
                date = "02";
            } else if (date == '3') {
                date = "03";
            } else if (date == '4') {
                date = "04";
            } else if (date == '5') {
                date = "05";
            } else if (date == '6') {
                date = "06";
            } else if (date == '7') {
                date = "07";
            } else if (date == '8') {
                date = "08";
            } else if (date == '9') {
                date = "09";
            }

// current hours
            let hours = date_ob.getHours();
// current minutes
            let minutes = date_ob.getMinutes();
// current seconds
            let seconds = date_ob.getSeconds();
            console.log(year + "-" + month + "-" + date + " " + hours + ":" + minutes + ":" + seconds);
            var today_dttime = year + "-" + month + "-" + date + " " + hours + ":" + minutes + ":" + seconds;
            var today_dt = year + "-" + month + "-" + date;
            var Project_status = "";
            var project_start_date = input.project_start_date;
            console.log('date_ob ' + date_ob);
            console.log('project_start_date ' + project_start_date);
            console.log('today_dt ' + today_dt);
            if (today_dt == project_start_date) {
                Project_status = 'Working';
            } else {
                Project_status = '';
            }
            console.log('Project_status ' + Project_status);
            var data = {
                fullname: input.fullname,
                email: input.email,
                dept_id: dept_id,
                user_type: input.select_usertype,
                username: input.username,
                password: input.password,
                created_date: today_dttime
            };
            console.log('Data ', data);
            var query = connection.query("INSERT INTO tbl_login_admin set ? ", data, function (err, rows)
            {
                if (err)
                    console.log("Error inserting : %s ", err);
                console.log('Data insert successfully');
                console.log('Farmer id ' + rows.insertId);
                project_id = rows.insertId;
                var new_data = {
                    username: input.username,
                    activity_desc: "New User Registration.",
                    activity_type: "User_Registration",
                    activity_id: project_id,
                    activity_date: today_dttime
                };
                var query = connection.query("INSERT INTO tbl_user_activity set ? ", new_data, function (err, rows1)
                {
                    if (err)
                        console.log("Error inserting : %s ", err);
                    console.log('Data insert successfully');
                    if (rows1 != '')
                    {
                        session = req.session;
//                        session.username = username;
                        session.User_language = "ENGLISH";
//                        console.log('sess.username ', session.username);
                        res.writeHead(200, {'Content-Type': 'text/plain'});
                        res.end('REGISTRATION');
                        res.end;
                    } else
                    {
                        res.writeHead(200, {'Content-Type': 'text/plain'});
                        res.end('NOT_REGI');
                        res.end;
                    }
                });
            });
        }
    });
};
router.check_login = function (req, res) {
    var input = JSON.parse(JSON.stringify(req.body));
    req.getConnection(function (err, connection) {
        if (err)
        {
            return console.log(err.message);
        } else
        {
            var username = input.username;
            var password = input.password;
            console.log('username ', username);
            console.log('passsword ', password);
            var query = connection.query("SELECT * FROM tbl_login_admin WHERE username='" + username + "' and password='" + password + "'", function (err, user_data)
            {
                if (err)
                    console.log("Error inserting : %s ", err);
                console.log('user_data ', user_data);
                if (user_data != '')
                {
                    session = req.session;
                    session.username = username;
                    var dept_id = user_data[0].dept_id;
                    var user_type = user_data[0].user_type;

                    session.dept_id = dept_id;
                    session.user_type = user_type;
                    session.User_language = "ENGLISH";
                    console.log('sess.username ', session.username, ' dept_id ', dept_id, ' user_type ', user_type);
                    res.writeHead(200, {'Content-Type': 'text/plain'});
                    res.end('LOGIN');
                    res.end;
                } else
                {
                    res.writeHead(200, {'Content-Type': 'text/plain'});
                    res.end('NOT_LOGIN');
                    res.end;
                }
            });
        }
    });
};

router.check_username = function (req, res) {
    var input = JSON.parse(JSON.stringify(req.body));
    req.getConnection(function (err, connection) {
        if (err)
        {
            return console.log(err.message);
        } else
        {
            var username = input.username;
            console.log('username ', username);
            var query = connection.query("SELECT * FROM tbl_login_admin WHERE username='" + username + "'", function (err, user_data)
            {
                if (err)
                    console.log("Error inserting : %s ", err);
                console.log('user_data ', user_data);
                if (user_data != '')
                {
//                    session = req.session;
//                    session.username = username;
//                    session.User_language = "ENGLISH";
//                    console.log('sess.username ', session.username);
                    res.writeHead(200, {'Content-Type': 'text/plain'});
                    res.end('Exist');
                    res.end;
                } else
                {
                    res.writeHead(200, {'Content-Type': 'text/plain'});
                    res.end('Not_Exist');
                    res.end;
                }
            });
        }
    });
};

router.check_email = function (req, res) {
    var input = JSON.parse(JSON.stringify(req.body));
    req.getConnection(function (err, connection) {
        if (err)
        {
            return console.log(err.message);
        } else
        {
            var email = input.email;
            console.log('email ', email);
            var query = connection.query("SELECT * FROM tbl_login_admin WHERE email='" + email + "'", function (err, user_data)
            {
                if (err)
                    console.log("Error inserting : %s ", err);
                console.log('user_data ', user_data);
                if (user_data != '')
                {
//                    session = req.session;
//                    session.username = username;
//                    session.User_language = "ENGLISH";
//                    console.log('sess.username ', session.username);
                    res.writeHead(200, {'Content-Type': 'text/plain'});
                    res.end('Exist');
                    res.end;
                } else
                {
                    res.writeHead(200, {'Content-Type': 'text/plain'});
                    res.end('Not_Exist');
                    res.end;
                }
            });
        }
    });
};

router.import_excel_file = function (req, res, next) {
    session = req.session;
    console.log('sess.username ', session.username);
    if (session.username) {
        var User_language = session.User_language;
        var username = session.username;

        res.render('import', {title: 'Express', data: "", 'User_language': User_language, 'username': username});
        res.end;
    } else
    {
        return res.redirect('/');
    }
};

router.view_village_summary_detail = function (req, res, next) {
    session = req.session;
    console.log('sess.username ', session.username);
    if (session.username) {
        var User_language = session.User_language;
        var username = session.username;
//        var dept_id = session.dept_id;
//        var user_type = session.user_type;

        req.getConnection(function (err, connection) {

            var query = connection.query('select * FROM tbl_district WHERE flag=1 ORDER by district_id asc', function (err, district)
            {
                if (err)
                    console.log("Error Selecting : %s ", err);
                res.render('view_village_summary_detail', {title: 'Express', district: district, 'User_language': User_language, 'username': username});
                res.end;
            });
        });
    } else
    {
        return res.redirect('/');
    }
};


router.select_taluka = function (req, res) {
//    var id= JSON.stringify(req.body.id);
    var id = JSON.parse(JSON.stringify(req.body.id));
    console.log('id ' + id);
    var User_language = session.User_language;
    var username = session.username;
    req.getConnection(function (err, connection) {
        var query1 = connection.query('SELECT * FROM tbl_taluka WHERE flag=1 and district_id  = ?', [id], function (err, rows)
        {
            if (err)
                console.log("Error Selecting : %s ", err);
//            console.log(rows);
            res.render('select_taluka_dropdown', {page_title: "Menu sub category Info", data: rows, 'User_language': User_language, 'username': username});
        });
    });
};
router.select_village = function (req, res) {
//    var id= JSON.stringify(req.body.id);
    var User_language = session.User_language;
    var username = session.username;
    var id = JSON.parse(JSON.stringify(req.body.id));
    console.log('id ' + id);
    req.getConnection(function (err, connection) {
        var query1 = connection.query('SELECT * FROM tbl_village WHERE flag=1 and taluka_id  = ?', [id], function (err, rows)
        {
            if (err)
                console.log("Error Selecting : %s ", err);
            console.log(rows);
            res.render('select_village_dropdown', {page_title: "Village Info", data: rows, 'User_language': User_language, 'username': username});
        });
    });
};


const util = require('util');

router.view_village_summary = async function (req, res, next) {
    const session = req.session;
    console.log('sess.username ', session.username);

    if (!session.username) {
        return res.redirect('/');
    }

    try {
        const input = JSON.parse(JSON.stringify(req.body));
        const {select_district, select_taluka, select_village, check_mode} = input;
        const {User_language, username} = session;

        const connection = await new Promise((resolve, reject) => {
            req.getConnection((err, conn) => {
                if (err)
                    reject(err);
                else
                    resolve(conn);
            });
        });

        const query = util.promisify(connection.query).bind(connection);

        const village_list = await query(
                'SELECT name as village_name FROM `tbl_village` WHERE taluka_id=? ORDER BY village_id ASC',
                [select_taluka]
                );

        const family_list = await query(
                `SELECT td.name as district, tt.name as taluka_name, tv.name as village_name,tv.revenue_village_name,
tv.gram_panchayat_name, tf.name as faliya_name, tf.pvtg_name, tf1.* FROM tbl_families tf1 
LEFT JOIN tbl_faliya tf ON tf1.faliya_id = tf.faliya_id 
LEFT JOIN tbl_village tv ON tf.village_id = tv.village_id 
LEFT JOIN tbl_taluka tt ON tv.taluka_id = tt.taluka_id 
LEFT JOIN tbl_district td ON tt.district_id = td.district_id 
WHERE tv.taluka_id = ? ORDER BY family_id ASC`,
                [select_taluka]
                );

        const selectedIds = family_list.map(f => f.family_id);
        if (selectedIds.length === 0) {
            return res.render('village_summary_view', {
                page_title: "Standard Info",
                result: [],
                village_list,
                family_list,
                User_language,
                username
            });
        }

        const placeholders = selectedIds.map(() => '?').join(',');
        const family_members_query = `
            SELECT td.name as district, tt.name as taluka_name, tv.name as village_name,tf.name as faliya_name, tf.pvtg_name, tf1.*
            FROM tbl_family_members tf1
            LEFT JOIN tbl_families ttf ON tf1.family_id = ttf.family_id
            LEFT JOIN tbl_faliya tf ON ttf.faliya_id = tf.faliya_id
            LEFT JOIN tbl_village tv ON tf.village_id = tv.village_id
            LEFT JOIN tbl_taluka tt ON tv.taluka_id = tt.taluka_id
            LEFT JOIN tbl_district td ON tt.district_id = td.district_id
            WHERE tf1.family_id IN (${placeholders})
        `;

        const family_members = await query(family_members_query, selectedIds);

        const extendedFamilyList = [...family_list, ...family_members];

        let result = [];
        let district_name, taluka_name;

        village_list.forEach(village => {
            const matchedFamilies = extendedFamilyList.filter(fam => fam.village_name === village.village_name);
            if (matchedFamilies.length === 0)
                return;

            district_name = matchedFamilies[0]?.district || "";
            taluka_name = matchedFamilies[0]?.taluka_name || "";
            const faliya_name = matchedFamilies[0]?.faliya_name || "";
            const pvtg_name = matchedFamilies[0]?.pvtg_name || "";
            const revenue_village_name = matchedFamilies[0]?.revenue_village_name || "";
            const gram_panchayat_name = matchedFamilies[0]?.gram_panchayat_name || "";

            const total_population = matchedFamilies[0]?.total_population || 0;
//            const pvtgs_hhs = matchedFamilies[0]?.pvtgs_hhs || 0;
            const pvtgs_hhs = matchedFamilies.filter(f =>
                f.hasOwnProperty('total_members') &&
                        f.total_members !== null &&
                        f.total_members !== '' &&
                        !isNaN(f.total_members)
            ).length;

            const pvtg_population = matchedFamilies.reduce((sum, fam) => sum + (fam.total_members || 0), 0);

            const kachcha_house_count = matchedFamilies.filter(f => Number(f.house_kachcha) === 1).length;
            const no_ration_card_count = matchedFamilies.filter(f => Number(f.ration_card_yes) === 0).length;

//            const no_aadhaar_card_count = matchedFamilies.filter(f => Number(f.has_aadhaar_card) === 0).length;
            const no_aadhaar_card_count = matchedFamilies.filter(f =>
                f.has_aadhaar_card !== '-' &&
                        f.has_aadhaar_card !== '' &&
                        Number(f.has_aadhaar_card) === 0
            ).length;


//            const no_pmjay_card_count = matchedFamilies.filter(f => Number(f.has_pmjay_card) === 0).length;
            const no_pmjay_card_count = matchedFamilies.filter(f =>
                f.hasOwnProperty('has_pmjay_card') &&
                        f.has_pmjay_card !== null &&
                        f.has_pmjay_card !== '' &&
                        f.has_pmjay_card !== '-' &&
                        Number(f.has_pmjay_card) === 0
            ).length;

            const no_electricity_available_count = matchedFamilies.filter(f => Number(f.electricity_available) === 0).length;

//            const no_mgnrega_job_card_yes_count = matchedFamilies.filter(f => Number(f.mgnrega_job_card_yes) === 0).length;
            const no_mgnrega_job_card_yes_count = matchedFamilies.filter(f =>
                f.hasOwnProperty('mgnrega_job_card_yes') &&
                        f.mgnrega_job_card_yes !== '' &&
                        f.mgnrega_job_card_yes !== '-' &&
                        f.mgnrega_job_card_yes !== null &&
                        Number(f.mgnrega_job_card_yes) === 0
            ).length;


//            const no_toilet_count = matchedFamilies.filter(f => Number(f.toilet) === 0).length;
            const no_toilet_count = matchedFamilies.filter(f =>
                f.hasOwnProperty('toilet') &&
                        f.toilet !== '' &&
                        f.toilet !== null &&
                        f.toilet !== '-' &&
                        Number(f.toilet) === 0
            ).length;

            const poor_water_source_count = matchedFamilies.filter(f =>
                ["નળ-પાણી નથી આવતુ", "નદી (ના)", "ના"].includes(f.water_source?.trim())
            ).length;
//            const has_no_jandhan_account_count = matchedFamilies.filter(f => Number(f.has_jandhan_account) === 0).length;
            const has_no_jandhan_account_count = matchedFamilies.filter(f =>
                f.hasOwnProperty('has_jandhan_account') &&
                        f.has_jandhan_account !== '' &&
                        f.has_jandhan_account !== null &&
                        f.has_jandhan_account !== '-' &&
                        Number(f.has_jandhan_account) === 0
            ).length;

//            const has_no_caste_certificate_count = matchedFamilies.filter(f => Number(f.has_caste_certificate) === 0).length;
            const has_no_caste_certificate_count = matchedFamilies.filter(f =>
                f.hasOwnProperty('has_caste_certificate') &&
                        f.has_caste_certificate !== '' &&
                        f.has_caste_certificate !== null &&
                        f.has_caste_certificate !== '-' &&
                        Number(f.has_caste_certificate) === 0
            ).length;


            const has_no_ujjwala_yojana_count = matchedFamilies.filter(f =>
                f.hasOwnProperty('has_ujjwala_yojana') && Number(f.has_ujjwala_yojana) === 0
            ).length;

            const has_pm_kisan_yojana_count = matchedFamilies.filter(f =>
                f.hasOwnProperty('has_pm_kisan_yojana') &&
                        f.has_pm_kisan_yojana !== '' &&
                        f.has_pm_kisan_yojana !== null &&
                        f.has_pm_kisan_yojana !== '-' &&
                        Number(f.has_pm_kisan_yojana) === 0
            ).length;

            result.push({
                district: district_name,
                taluka_name: taluka_name,
                revenue_village_name: revenue_village_name,
                gram_panchayat_name: gram_panchayat_name,
                village_name: village.village_name,
                faliya_name: faliya_name,
                pvtg_name: pvtg_name,
                total_population,
                pvtgs_hhs,
                pvtg_population,
                kachcha_house_count,
                no_ration_card_count,
                no_aadhaar_card_count,
                no_pmjay_card_count,
                no_electricity_available_count,
                no_mgnrega_job_card_yes_count,
                no_toilet_count,
                poor_water_source_count,
                has_no_jandhan_account_count,
                has_no_caste_certificate_count,
                has_no_ujjwala_yojana_count,
                has_pm_kisan_yojana_count
            });
        });

        console.log("✅ Final Village Summary:", result);

        const total = {
            village_name: '',
            total_population: 0,
            pvtgs_hhs: 0,
            pvtg_population: 0,
            kachcha_house_count: 0,
            no_ration_card_count: 0,
            no_aadhaar_card_count: 0,
            no_pmjay_card_count: 0,
            no_electricity_available_count: 0,
            no_mgnrega_job_card_yes_count: 0,
            no_toilet_count: 0,
            poor_water_source_count: 0,
            has_no_jandhan_account_count: 0,
            has_no_caste_certificate_count: 0,
            has_no_ujjwala_yojana_count: 0,
            has_pm_kisan_yojana_count: 0
        };
        // 👇 Loop over each village row and add to total
        result.forEach(row => {
            total.total_population += row.total_population || 0;
            total.pvtgs_hhs += row.pvtgs_hhs || 0;
            total.pvtg_population += row.pvtg_population || 0;
            total.kachcha_house_count += row.kachcha_house_count || 0;
            total.no_ration_card_count += row.no_ration_card_count || 0;
            total.no_aadhaar_card_count += row.no_aadhaar_card_count || 0;
            total.no_pmjay_card_count += row.no_pmjay_card_count || 0;
            total.no_electricity_available_count += row.no_electricity_available_count || 0;
            total.no_mgnrega_job_card_yes_count += row.no_mgnrega_job_card_yes_count || 0;
            total.no_toilet_count += row.no_toilet_count || 0;
            total.poor_water_source_count += row.poor_water_source_count || 0;
            total.has_no_jandhan_account_count += row.has_no_jandhan_account_count || 0;
            total.has_no_caste_certificate_count += row.has_no_caste_certificate_count || 0;
            total.has_no_ujjwala_yojana_count += row.has_no_ujjwala_yojana_count || 0;
            total.has_pm_kisan_yojana_count += row.has_pm_kisan_yojana_count || 0;
        });

        let result_total = [total];
// 👇 Append total row to result array
//        result.push(total);
        console.log("✅ result_total", result_total);

        res.render('village_summary_view', {
            page_title: "Standard Info",
            result, result_total, district_name, taluka_name,
            village_list,
            family_list,
            User_language,
            username
        });

    } catch (err) {
        console.error("🚫 Error in view_village_summary:", err);
        res.status(500).send("Internal Server Error");
    }
};


//router.view_village_summary = function (req, res, next) {
//    session = req.session;
//    console.log('sess.username ', session.username);
//    if (session.username) {
//        var input = JSON.parse(JSON.stringify(req.body));
//        var User_language = session.User_language;
//        var username = session.username;
//        var dept_id = session.dept_id;
//        var user_type = session.user_type;
////    console.log('input ',input);
//        req.getConnection(function (err, connection) {
//            if (err)
//            {
//                return console.log(err.message);
//            } else
//            {
//                var data = {
//                    select_district: input.select_district,
//                    select_taluka: input.select_taluka,
//                    village_id: input.select_village,
//                    check_mode: input.check_mode
//                };
//                var district_id = input.select_district;
//                var taluka_id = input.select_taluka;
//                var village_id = input.select_village;
//                var check_mode = input.check_mode;
//                var query = connection.query('SELECT name as village_name FROM `tbl_village` WHERE taluka_id=? order by village_id asc ', [taluka_id], function (err, village_list)
//                {
//                    if (err)
//                        console.log("Error Selecting : %s ", err);
////                    console.log(query.sql);
//                    console.log('village_list ' + village_list);
//                    var query = connection.query('SELECT td.name as district,tt.name as taluka_name,tv.name as village_name,tf.name as faliya_name,tf.pvtg_name,tf1.* FROM `tbl_families` as tf1 LEFT join tbl_faliya as tf on tf1.faliya_id=tf.faliya_id LEFT join tbl_village as tv on tf.village_id=tv.village_id LEFT join tbl_taluka as tt on tv.taluka_id=tt.taluka_id LEFT join tbl_district as td on tt.district_id=td.district_id WHERE tv.taluka_id=? order by family_id asc', [taluka_id], function (err, family_list)
//                    {
//                        if (err)
//                            console.log("Error Selecting : %s ", err);
//                        console.log('family_list ' + family_list);
//
//                        // 1. Get all family_ids
//                        const selectedIds = family_list.map(f => f.family_id);
//
//// 2. Build query
//                        const placeholders = selectedIds.map(() => '?').join(',');
//                        const query = `SELECT td.name as district,tt.name as taluka_name,tv.name as village_name,tf.name as faliya_name,tf.pvtg_name,tf1.* FROM tbl_family_members as tf1 LEFT join tbl_families as ttf on tf1.family_id=ttf.family_id LEFT join tbl_faliya as tf on ttf.faliya_id=tf.faliya_id LEFT join tbl_village as tv on tf.village_id=tv.village_id LEFT join tbl_taluka as tt on tv.taluka_id=tt.taluka_id LEFT join tbl_district as td on tt.district_id=td.district_id WHERE tf1.family_id IN (${placeholders})`;
//
//                        let result = [];
//// 3. Execute query
//                        connection.query(query, selectedIds, (err, results) => {
//                            if (err) {
//                                console.error('MySQL error:', err);
//                                return;
//                            }
//
//                            const selectedFamilies = results;
//
//                            // 4. Append MySQL records as new entries to family_list (without modifying originals)
//                            const extendedFamilyList = [...family_list, ...selectedFamilies];
//
//                            console.log('✅ Final Extended Family List:', extendedFamilyList.length);
////                            console.log('✅ Final Extended Family List:', extendedFamilyList);
//
////                            console.log('✅ Final Extended Family List Data:', extendedFamilyList[751]);
////                            console.log('✅ Final Extended Family List:', extendedFamilyList[711]);
//                            // Use extendedFamilyList for further processing
//
//                            extendedFamilyList.forEach((item, index) => {
////                                console.log(`Record ${index + 1}:`, item);
//                            });
//
//                            
//
//                            village_list.forEach(village => {
//                                // 🔁 Use extendedFamilyList instead of family_list
//                                const matchedFamilies = extendedFamilyList.filter(fam => fam.village_name === village.village_name);
//
//                                if (matchedFamilies.length > 0) {
//                                    const total_male = matchedFamilies.reduce((sum, fam) => sum + (fam.male_members || 0), 0);
//                                    const count = matchedFamilies.length;
//                                    const total_population = matchedFamilies[0]?.total_population || 0;
//                                    const pvtgs_hhs = matchedFamilies[0]?.pvtgs_hhs || 0;
//                                    const pvtgs_population = matchedFamilies.reduce((sum, fam) => sum + (fam.total_members || 0), 0);
//
////                                    const kachcha_count = matchedFamilies.filter(fam => fam.house_kachcha === 1).length;
//                                    const kachcha_count = matchedFamilies.filter(fam =>
//                                        Number(fam.house_kachcha) === 1
//                                    ).length;
//
////                                    const no_ration_card_count = matchedFamilies.filter(fam => fam.ration_card_yes === 0).length;
//                                    const no_ration_card_count = matchedFamilies.filter(fam =>
//                                        Number(fam.ration_card_yes) === 0
//                                    ).length;
//
////                                    const no_aadhaar_card_count = matchedFamilies.filter(fam => fam.has_aadhaar_card === 0).length;
////                                    const no_pmjay_card_count = matchedFamilies.filter(fam => fam.has_pmjay_card === 0).length;
////                                    const no_electricity_available_count = matchedFamilies.filter(fam => fam.electricity_available === 0).length;
////                                    const no_mgnrega_job_card_yes_count = matchedFamilies.filter(fam => fam.mgnrega_job_card_yes === 0).length;
////                                    const no_toilet_count = matchedFamilies.filter(fam => fam.toilet === 0).length;
////
////                                    const has_jandhan_account = matchedFamilies.filter(fam => fam.has_jandhan_account === 0).length;
////                                    const has_caste_certificate = matchedFamilies.filter(fam => fam.has_caste_certificate === 0).length;
//
//                                    const no_aadhaar_card_count = matchedFamilies.filter(fam => Number(fam.has_aadhaar_card) === 0).length;
//                                    const no_pmjay_card_count = matchedFamilies.filter(fam => Number(fam.has_pmjay_card) === 0).length;
//                                    const no_electricity_available_count = matchedFamilies.filter(fam => Number(fam.electricity_available) === 0).length;
//                                    const no_mgnrega_job_card_yes_count = matchedFamilies.filter(fam => Number(fam.mgnrega_job_card_yes) === 0).length;
//                                    const no_toilet_count = matchedFamilies.filter(fam => Number(fam.toilet) === 0).length;
//
//                                    const no_jandhan_account_count = matchedFamilies.filter(fam => Number(fam.has_jandhan_account) === 0).length;
//                                    const no_caste_certificate_count = matchedFamilies.filter(fam => Number(fam.has_caste_certificate) === 0).length;
//
//
////                                    const has_ujjwala_yojana = matchedFamilies.filter(fam => fam.has_ujjwala_yojana === 0).length;
//                                    const has_ujjwala_yojana = matchedFamilies.filter(fam =>
//                                        fam.hasOwnProperty('has_ujjwala_yojana') && fam.has_ujjwala_yojana == 0
//                                    ).length;
//
//                                    const poor_water_source_count = matchedFamilies.filter(fam =>
//                                        ["નળ-પાણી નથી આવતુ", "નદી (ના)", "ના"].includes(fam.water_source)
//                                    ).length;
//
//                                    result.push({
//                                        village_name: village.village_name,
////                                        total_male: total_male,
////                                        count: count,
//                                        total_population: total_population,
//                                        pvtgs_hhs: pvtgs_hhs,
//                                        pvtg_population: pvtgs_population,
//                                        kachcha_house_count: kachcha_count,
//                                        no_ration_card_count: no_ration_card_count,
//                                        no_aadhaar_card_count: no_aadhaar_card_count,
//                                        no_pmjay_card_count: no_pmjay_card_count,
//                                        no_electricity_available_count: no_electricity_available_count,
//                                        no_mgnrega_job_card_yes_count: no_mgnrega_job_card_yes_count,
//                                        no_toilet_count: no_toilet_count,
//                                        poor_water_source_count: poor_water_source_count,
//                                        has_no_jandhan_account_count: no_jandhan_account_count,
//                                        has_no_caste_certificate_count: no_caste_certificate_count,
//                                        has_no_ujjwala_yojana_count: has_ujjwala_yojana
//                                    });
//                                }
//                            });
//
//                            console.log("✅ Final Village Summary:", result);
//
//                        });
//
//
//
//
////                        let result = [];
////                        village_list.forEach(village => {
////                            // Get matching families for this village
////                            const matchedFamilies = family_list.filter(fam => fam.village_name === village.village_name);
////                            // Only count if at least 1 family exists for this village
////                            if (matchedFamilies.length > 0) {
////
////                                const total_male = matchedFamilies.reduce((sum, fam) => sum + (fam.male_members || 0), 0);
////                                const count = matchedFamilies.length;
////                                const total_population = matchedFamilies[0]?.total_population || 0;
////                                const pvtgs_hhs = matchedFamilies[0]?.pvtgs_hhs || 0;
////                                const pvtgs_population = matchedFamilies.reduce((sum, fam) => sum + (fam.total_members || 0), 0);
////                                const kachcha_count = matchedFamilies.filter(fam => fam.house_kachcha === 1).length;
////
////                                const no_ration_card_count = matchedFamilies.filter(fam => fam.ration_card_yes === 0).length;
////
////                                const no_aadhaar_card_count = matchedFamilies.filter(fam => fam.has_aadhaar_card === 0).length;
////                                const no_pmjay_card_count = matchedFamilies.filter(fam => fam.has_pmjay_card === 0).length;
////                                const no_electricity_available_count = matchedFamilies.filter(fam => fam.electricity_available === 0).length;
////
////                                const no_mgnrega_job_card_yes_count = matchedFamilies.filter(fam => fam.mgnrega_job_card_yes === 0).length;
////                                const no_toilet_count = matchedFamilies.filter(fam => fam.toilet === 0).length;
////
////                                // ✅ Count families where water_source is one of the "not available" types
////                                const poor_water_source_count = matchedFamilies.filter(fam =>
////                                    ["નળ-પાણી નથી આવતુ", "નદી (ના)", "ના"].includes(fam.water_source)
////                                ).length;
////
////                                result.push({
////                                    village_name: village.village_name,
////                                    total_male: total_male,
////                                    count: count,
////                                    total_population: total_population,
////                                    pvtgs_hhs: pvtgs_hhs,
////                                    pvtg_population: pvtgs_population,
////                                    kachcha_house_count: kachcha_count,
////                                    no_ration_card_count: no_ration_card_count,
////                                    no_aadhaar_card_count: no_aadhaar_card_count,
////                                    no_pmjay_card_count: no_pmjay_card_count,
////                                    no_electricity_available_count: no_electricity_available_count,
////                                    no_mgnrega_job_card_yes_count: no_mgnrega_job_card_yes_count,
////                                    no_toilet_count: no_toilet_count,
////                                    poor_water_source_count: poor_water_source_count
////                                });
////                            }
////                        });
//                        console.log("result ",result);
//                        res.render('village_summary_view', {page_title: "Standard Info",'result':result, village_list: village_list, 'family_list': family_list, 'User_language': User_language, 'username': username});
//                        res.end;
//                    });
//                });
//            }
//        });
//    } else
//    {
//        return res.redirect('/');
//    }
//};
module.exports = router;

