DVR-Backend/controllers/dvrReportController.js

const db = require("../services/dbconnection");
const validation = require("../controllers/validation")
const moment = require("moment");
const logger = require('../utils/logger');

module.exports.createContactEntry = async (req, res) => {
    const validationResp = await validation.createContactentrycheck(req);
    const createdate = new Date();

    if (validationResp.status) {
        try {
            var stage_id;
            if (req.body.email_id == null || req.body.mobile_no == null) {
                //|| req.body.department == null || req.body.designation == null) {
                stage_id = 2;
                console.log(stage_id);

            } else {
                stage_id = 1;
            }

            const querycount = "select COUNT(*) as count from contact_master where first_name='" + req.body.first_name + "' and last_name='" + req.body.last_name + "' and mobile_number ='" + req.body.mobile_no + "'";
            console.log("querycount", querycount);
            const querycountresult = await db.executequery(querycount);

            console.log("querycountresult", querycountresult);

            console.log("querycountresult1", querycountresult[0].count);

            if (querycountresult[0].count > 0) {
                console.log("duplicate entry present");

                res.send({
                    status: true,
                    data: [{
                        message: "",
                        createStatus: "1",
                        exception: {
                            errorid: "400",
                            errormessage: "Duplicate Entry: This contact alrady exists in the database."
                        }
                    }],
                    error: ""

                })

            } else {
                console.log("new contact details")
                const query = "INSERT INTO contact_master(first_name,last_name,mobile_number,email_id,designation,department,function_area,created_by,created_on,customer_id,stage_id,mob_no)" +
                    "VALUES(?,?,?,?,?,?,?,?,?,?,?,?)";
                const values = [req.body.first_name, req.body.last_name, req.body.mobile_no, req.body.email_id, req.body.designation, req.body.department, req.body.function, req.body.created_by, createdate, req.body.contact_for, stage_id, req.body.mob_no]
                const res1 = await db.executevaluesquery(query, values);

                logger.info('log to file', res);


                if (res1) {

                    const query = "select contact_id,customer_id,first_name,last_name  from contact_master order by contact_id desc limit 1";
                    const queryresult = await db.executequery(query);

                    console.log("queryresult", queryresult);

                    res.send({
                        status: true,
                        data: [{
                            message: "Contact Entry created Successfully ",
                            createStatus: "0",
                            lastinsertedData: queryresult[0].first_name + " " + queryresult[0].last_name,
                            exception: {
                                errorid: "",
                                errormessage: "No Errors"
                            }
                        }],
                        error: " "


                    })
                } else {
                    res.send({
                        status: true,
                        data: [{
                            message: "",
                            createStatus: "1",
                            exception: {
                                errorid: "404",
                                errormessage: "Bad request"
                            }
                        }],
                        error: ""

                    })
                }

            }


        }
        catch (err) {
            console.log(err)
            res.send({
                status: true,
                data: [{
                    message: "",
                    createStatus: "1",
                    exception: {
                        errorid: "404",
                        errormessage: "Bad request"
                    }
                }],
                error: err
            })
        }
    } else {
        res.send(validationResp)
    }

}


module.exports.getdvrDetails = async (req, res) => {
    console.log("in dvr details for one id");
    const validationResp = await validation.getdvrDetailscheck(req);
    if (validationResp.status) {
        try {
            const query = "SELECT d.dvr_id,d.date_of_visit,d.customer_name,d.contact_name,d.physical_meeting,d.message,s.stage_desc,d.stage_id,d.created_on,d.nextstep,d.second_contact,d.objective,d.est_Date,d.estvalue,d.func,d.person FROM dvr_report d JOIN stage_master s ON d.stage_id=s.stage_id WHERE d.dvr_id = '" + req.query.dvrid + "' and d.delete_id = 0";
            console.log("query", query)
            const queryresult = await db.executequery(query);
            if (queryresult.length > 0) {
                res.send({
                    status: true,
                    data: [
                        {
                            getdvrDataStatus: '0',
                            mesasge: "Data Found",
                            visit_date: queryresult[0].date_of_visit,
                            customer_name: queryresult[0].customer_name,
                            contact_name: queryresult[0].contact_name,
                            mesasge: queryresult[0].message,
                            physical_metting: queryresult[0].physical_meeting,
                            stage_desc: queryresult[0].stage_desc,
                            stage_id: queryresult[0].stage_id,
                            created_on: queryresult[0].created_on,
                            nextstep: queryresult[0].nextstep,
                            second_contact:queryresult[0].second_contact,
                            objective:queryresult[0].objective,
                            est_Date:queryresult[0].est_Date,
                            deal:queryresult[0].estvalue,
                            func:queryresult[0].func,
                            person:queryresult[0].person

                        }
                    ],
                    errors: ""

                })

            } else {
                res.send({
                    status: false,
                    data: [
                        {
                            getdvrDataStatus: '1',
                            mesasge: "Data not Found"
                        }
                    ],
                    errors: ""


                })
            }
        }
        catch (err) {
            console.log(err)
            res.send({
                status: true, data: []
            })
        }
    } else {
        res.send(validationResp)
    }
}


module.exports.getselectionData = async (req, res) => {
    try {
        var customerData = [];
        var contactData = [];
        var stageData = [];
        var personData = [];
        var funcDetails = [];


        // const query = "SELECT customer_id,customer_name,customer_status FROM customer_master";
        const query = "SELECT c.customer_id, c.customer_name, c.customer_status, u.first_name, u.last_name, u.user_id FROM customer_master c JOIN customer_user_mapping cu ON c.customer_id = cu.customer_id JOIN user_master u ON cu.user_id = u.user_id;"
        const queryresult = await db.executequery(query);

        if (queryresult.length > 0) {
            for (var i = 0; i < queryresult.length; i++) {
                customerData.push(queryresult[i])
            }

        }

        const query1 = "SELECT contact_id,first_name,last_name FROM contact_master order by first_name";
        const queryresult1 = await db.executequery(query1);

        if (queryresult1.length > 0) {
            for (var i = 0; i < queryresult1.length; i++) {
                contactData.push(queryresult1[i])
            }
        }

        const query2 = "SELECT stage_id,stage_desc FROM stage_master";
        const queryresult2 = await db.executequery(query2);

        if (queryresult2.length > 0) {
            for (var i = 0; i < queryresult2.length; i++) {
                stageData.push(queryresult2[i])
            }
        }

        // const query3 = "SELECT person_id,person_name FROM person_details";
        const query3 = "SELECT id_fun_person as person_id, person_name FROM function_person_details";
        const queryresult3 = await db.executequery(query3);

        if (queryresult3.length > 0) {
            for (var i = 0; i < queryresult3.length; i++) {
                personData.push(queryresult3[i])
            }
        }

        // const query4 = "SELECT func_id,func_name FROM function_details";
        const query4 = "SELECT fun_id as func_id, fun_name as func_name FROM function_details";
        const queryresult4 = await db.executequery(query4);

        if (queryresult4.length > 0) {
            for (var i = 0; i < queryresult4.length; i++) {
                funcDetails.push(queryresult4[i])
            }
        }


        res.send({
            status: true,
            data: [{
                customerData: customerData
            }, {
                contactData: contactData
            }, {
                stageData: stageData
            },
            {
                personData:personData 
            },
            {
                funcDetails: funcDetails
            }
            ],
            error: ""
        })


    }
    catch (err) {
        console.log(err)
        res.send({
            status: true, data: [], error: err
        })
    }
}


module.exports.getdashboarddetails = async (req, res) => {
    try {
        var login_id = req.query.login_id;
        var sale_person = req.query.salePerson;
        var customer = req.query.customer;
        var customer_id = req.query.customerid;
        var rol_des;
        var dvrlist = [];
        var rolData;
        var rolData1;
        var dates, firstdate, seconddate;

        var user_id_for_mapp;
        var emai_id;

        const findRole = "call SP_roleidentify1('" + login_id + "')";
        const roleidentify = await db.executequery(findRole);
        rolData = roleidentify[0];

        console.log("findRole", findRole);
        rolData.forEach(function (item) {
            console.log(item.role_des);
            rolData1 = item.role_des;
            user_id_for_mapp = item.user_id11;
        });

        //console.log("item",item);
        rol_des = rolData1;

        console.log("user_id_for_mapp", user_id_for_mapp)

        if (rol_des == 'admin') {
            //admin
            console.log("req.query.searchfiltertype", req.query.searchfiltertype);
            if (req.query.searchfiltertype) {
                if (req.query.searchfiltertype.length > 1) {
                    //date seach filter
                    console.log("in ifffffffffffffffffffffffffffff");
                    var searchfilter = req.query.searchfiltertype;
                    console.log("searchfilter", searchfilter);
                    if (searchfilter == "undefineddate") {
                        console.log("date not pass");
                        firstdate = '';
                        seconddate = '';

                    } else {
                        dates = searchfilter.split("-");
                        console.log("dates", dates)

                        firstdate = dates[0] + ' 00:00:00';
                        seconddate = dates[1] + ' 23:59:59';
                    }



                    var querydvr;
                    var countquery;

                    console.log("firstdate", firstdate);
                    console.log("seconddate", seconddate);
                    console.log("ooooooooooooo", sale_person);
                    if (sale_person != undefined && sale_person != 'undefined' && sale_person != 'all') {
                        var data = sale_person.split(" ");
                        console.log("data----->", data)

                        console.log("need to find sale person email id for cound data");

                        var query = "select user_id,login_id from user_master where first_name like '%" + data[0] + "%' and  last_name like '%" + data[1] + "%'";
                        const res_user = await db.executequery(query);
                        console.log("res_user", res_user);
                        console.log("res_user----------->", res_user[0].login_id)
                        emai_id = res_user[0].login_id;

                    }

                    console.log("customer", customer, "customer_id", customer_id, " email_id-->", emai_id);

                    if (sale_person != undefined && customer == undefined && searchfilter == 'undefineddate' && sale_person != 'all') {
                        console.log("only for sale_person");
                        querydvr = "select  dvr_id, date_of_visit,customer_name,contact_name,message,physical_meeting,sale_person,nextstep,second_contact,objective,est_Date,estvalue,func,person,stage_id from dvr_report where sale_person like '%" + sale_person + "%' and delete_id=0 order by DATE_FORMAT(date_of_visit, '%Y%m%d') desc";
                        countquery = "call SP_CountDashboard_admin ('" + firstdate + "','" + seconddate + "','" + emai_id + "','" + '' + "','" + '' + "')";

                    } else if (sale_person == undefined && customer != undefined && searchfilter == 'undefineddate' && sale_person != 'all' && customer != 'all') {
                        console.log("only for customer");
                        querydvr = "select  dvr_id, date_of_visit,customer_name,contact_name,message,physical_meeting,sale_person,nextstep,second_contact,objective,est_Date,estvalue,func,person,stage_id from dvr_report where customer_name like '%" + customer + "%' and delete_id=0 order by DATE_FORMAT(date_of_visit, '%Y%m%d') desc";
                        countquery = "call SP_CountDashboard_admin ('" + firstdate + "','" + seconddate + "','" + '' + "','" + customer + "','" + customer_id + "')";

                    } else if (sale_person != undefined && customer != undefined && customer_id != undefined && sale_person != 'all') {
                        console.log("for sales_person and Customer");
                        querydvr = "select  dvr_id, date_of_visit,customer_name,contact_name,message,physical_meeting,sale_person,nextstep,second_contact,objective,est_Date,estvalue,func,person,stage_id from dvr_report where customer_name like '%" + customer + "%' and sale_person like '%" + sale_person + "%' and delete_id=0 order by DATE_FORMAT(date_of_visit, '%Y%m%d') desc";
                        countquery = "call SP_CountDashboard_admin ('" + firstdate + "','" + seconddate + "','" + emai_id + "','" + customer + "','" + customer_id + "')";

                    } else if (sale_person != 'all' && firstdate != undefined && seconddate != undefined && sale_person != undefined) {
                        console.log("date and sale_person");
                        querydvr = "select  dvr_id, date_of_visit,customer_name,contact_name,message,physical_meeting,sale_person,nextstep,second_contact,objective,est_Date,estvalue,func,person,stage_id from dvr_report where date_of_visit between'" + firstdate + "' and '" + seconddate + "' and  sale_person like '%" + sale_person + "%' and delete_id=0 order by DATE_FORMAT(date_of_visit, '%Y%m%d') desc";
                        countquery = "call SP_CountDashboard_admin ('" + firstdate + "','" + seconddate + "','" + emai_id + "','" + '' + "','" + '' + "')";


                    } else if (customer != undefined && firstdate != undefined && seconddate != undefined && sale_person == undefined) {
                        console.log("for date and customer");
                        querydvr = "select  dvr_id, date_of_visit,customer_name,contact_name,message,physical_meeting,sale_person,nextstep,second_contact,objective,est_Date,estvalue,func,person,stage_id from dvr_report where date_of_visit between'" + firstdate + "' and '" + seconddate + "' and customer_name like '%" + customer + "%' and delete_id=0 order by DATE_FORMAT(date_of_visit, '%Y%m%d') desc";
                        countquery = "call SP_CountDashboard_admin ('" + firstdate + "','" + seconddate + "','" + '' + "','" + customer + "','" + customer_id + "')";

                    } else if (customer != undefined && firstdate != undefined && seconddate != undefined && sale_person != undefined) {
                        console.log("all filters");
                        querydvr = "select  dvr_id, date_of_visit,customer_name,contact_name,message,physical_meeting,sale_person,nextstep,second_contact,objective,est_Date,estvalue,func,person,stage_id from dvr_report where date_of_visit between'" + firstdate + "' and '" + seconddate + "' and customer_name like '%" + customer + "%' and  sale_person like '%" + sale_person + "%' and delete_id=0 order by DATE_FORMAT(date_of_visit, '%Y%m%d') desc";
                        countquery = "call SP_CountDashboard_admin ('" + firstdate + "','" + seconddate + "','" + emai_id + "','" + customer + "','" + customer_id + "')";
                    }
                    else {
                        console.log("only for date");
                        querydvr = "select  dvr_id, date_of_visit,customer_name,contact_name,message,physical_meeting,sale_person,nextstep,second_contact,objective,est_Date,estvalue,func,person,stage_id from dvr_report where date_of_visit between'" + firstdate + "' and '" + seconddate + "' and delete_id=0 order by DATE_FORMAT(date_of_visit, '%Y%m%d') desc";
                        countquery = "call SP_CountDashboard_admin ('" + firstdate + "','" + seconddate + "','" + '' + "','" + '' + "','" + '' + "')";

                    }


                    // if (sale_person == undefined && customer == undefined && searchfilter != "undefineddate" && sale_person == 'undefined') {
                    //     console.log("only for date")
                    //     querydvr = "select  dvr_id, date_of_visit,customer_name,contact_name,message,physical_meeting,sale_person from dvr_report where date_of_visit between'" + firstdate + "' and '" + seconddate + "' and delete_id=0 order by DATE_FORMAT(date_of_visit, '%Y%m%d') desc";

                    //     countquery = "call SP_CountDashboard_admin ('" + firstdate + "','" + seconddate + "','" + '' + "','" + '' + "','" + '' + "')";

                    // } else if (sale_person != undefined && searchfilter == "undefineddate" && customer == undefined && sale_person !='undefined') {
                    //     console.log("for sale_peron only")
                    //     querydvr = "select  dvr_id, date_of_visit,customer_name,contact_name,message,physical_meeting,sale_person from dvr_report where sale_person like '%" + sale_person + "%' and delete_id=0 order by DATE_FORMAT(date_of_visit, '%Y%m%d') desc";
                    //     countquery = "call SP_CountDashboard_admin ('" + firstdate + "','" + seconddate + "','" + emai_id + "','" + '' + "','" + '' + "')";

                    // }else if(customer != undefined && customer_id !=undefined && searchfilter == "undefineddate" && sale_person !=undefined && sale_person != 'undefined'){
                    //     console.log("for customer only");
                    //     querydvr = "select  dvr_id, date_of_visit,customer_name,contact_name,message,physical_meeting,sale_person from dvr_report where customer_name like '%" + customer + "%' and delete_id=0 order by DATE_FORMAT(date_of_visit, '%Y%m%d') desc";
                    //     countquery = "call SP_CountDashboard_admin ('" + firstdate + "','" + seconddate + "','" + '' + "','" + customer + "','" + customer_id + "')";

                    // }else if(customer !=undefined && sale_person !='undefined' && sale_person !=undefined && searchfilter == "undefineddate"){
                    //    console.log("for customer and sale_person")
                    //    querydvr = "select  dvr_id, date_of_visit,customer_name,contact_name,message,physical_meeting,sale_person from dvr_report where customer_name like '%" + customer + "%' and sale_person like '%" + sale_person + "%' and delete_id=0 order by DATE_FORMAT(date_of_visit, '%Y%m%d') desc";
                    //    countquery = "call SP_CountDashboard_admin ('" + firstdate + "','" + seconddate + "','" + emai_id + "','" + customer + "','" + customer_id + "')";

                    // }else if(customer !=undefined && searchfilter != "undefineddate" && sale_person ==undefined){
                    //     console.log("customer and date filter");
                    //     querydvr = "select  dvr_id, date_of_visit,customer_name,contact_name,message,physical_meeting,sale_person from dvr_report where date_of_visit between'" + firstdate + "' and '" + seconddate + "' and customer_name like '%" + customer + "%' and delete_id=0 order by DATE_FORMAT(date_of_visit, '%Y%m%d') desc";
                    //     countquery = "call SP_CountDashboard_admin ('" + firstdate + "','" + seconddate + "','" + '' + "','" + customer + "','" + customer_id + "')";

                    // }else if(customer !=undefined && searchfilter != "undefineddate" && sale_person !=undefined){
                    //     console.log("customer, sale person and date")
                    //     querydvr = "select  dvr_id, date_of_visit,customer_name,contact_name,message,physical_meeting,sale_person from dvr_report where date_of_visit between'" + firstdate + "' and '" + seconddate + "' and customer_name like '%" + customer + "%' and  sale_person like '%" + sale_person + "%' and delete_id=0 order by DATE_FORMAT(date_of_visit, '%Y%m%d') desc";
                    //     countquery = "call SP_CountDashboard_admin ('" + firstdate + "','" + seconddate + "','" + emai_id + "','" + customer + "','" + customer_id + "')";

                    // }
                    // else{
                    //     console.log("for date and sale_person filters");
                    //     querydvr = "select  dvr_id, date_of_visit,customer_name,contact_name,message,physical_meeting,sale_person from dvr_report where date_of_visit between'" + firstdate + "' and '" + seconddate + "' and  sale_person like '%" + sale_person + "%' and delete_id=0 order by DATE_FORMAT(date_of_visit, '%Y%m%d') desc";
                    //     countquery = "call SP_CountDashboard_admin ('" + firstdate + "','" + seconddate + "','" + emai_id + "','" + '' + "','" + '' + "')";

                    // }
                    console.log(querydvr);
                    const queryresult = await db.executequery(querydvr);

                    console.log("firstdate------>", firstdate, "seconddate-------->", seconddate)
                    if (firstdate == undefined && seconddate == undefined) {

                    } else {
                        //date is request of api
                    }




                    console.log("countquery=========>", countquery)
                    const queryresult1 = await db.executequery(countquery);
                    console.log("queryresult1========>", queryresult1)
                    var countdata = queryresult1[0];
                    console.log("countdata", countdata, queryresult.length);
                    for (var i = 0; i < queryresult.length; i++) {
                        //  console.log(queryresult[i]);
                        dvrlist.push(queryresult[i])
                    }

                    if (queryresult.length > 0) {


                        res.send({
                            status: true,
                            data: [{
                                getdvrDataStatus: '0',
                                mesasge: "Data Found",
                                countdata: [{
                                    no_of_visit: (countdata == undefined ? queryresult.length : countdata[0].allcount),
                                    no_of_completed: (countdata == undefined ? 0 : countdata[0].completed),
                                    no_of_incompleted: (countdata == undefined ? 0 : countdata[0].incompleted),
                                    no_of_contact: (countdata == undefined ? 0 : countdata[0].contactcount)

                                }],
                                dvrlist,
                            }],
                            error: " "

                        })


                    } else {

                        res.send({
                            status: false,
                            data: [{
                                getdvrDataStatus: '1',
                                mesasge: "Data Not Found"
                            }],
                            error: " "

                        })

                    }


                } else {

                    //last week, month,year searchfilter

                    console.log("in admin else part")
                    if (req.query.searchfiltertype == '0') {
                        //for last week
                        var dvrlist = [];
                        const lastweek = await getLastWeeksDate();
                        const currentDate = await getcurrentDate();
                        console.log(lastweek)
                        if (sale_person == undefined) {
                            querydvr = "select  dvr_id, date_of_visit,customer_name,contact_name,message,physical_meeting,sale_person,nextstep,second_contact,objective,est_Date,estvalue,func,person,stage_id from dvr_report where date_of_visit between'" + lastweek + "' and '" + currentDate + "' and delete_id=0 order by DATE_FORMAT(date_of_visit, '%Y%m%d') desc";

                        } else {
                            querydvr = "select  dvr_id, date_of_visit,customer_name,contact_name,message,physical_meeting,sale_person,nextstep,second_contact,objective,est_Date,estvalue,func,person,stage_id from dvr_report where date_of_visit between'" + lastweek + "' and '" + currentDate + "' and  contact_name like '%" + sale_person + "%'  and delete_id=0 order by DATE_FORMAT(date_of_visit, '%Y%m%d') desc";
                        }

                        const countquery = "call SP_CountDashboard ('" + lastweek + "','" + currentDate + "','" + '' + "')";
                        const queryresult = await db.executequery(querydvr);

                        console.log("countquery", countquery);
                        const queryresult1 = await db.executequery(countquery);
                        var countdata = queryresult1[0];

                        for (var i = 0; i < queryresult.length; i++) {
                            // console.log(queryresult[i]);
                            dvrlist.push(queryresult[i])
                        }

                        if (queryresult.length > 0) {



                            res.send({
                                status: true,
                                data: [{
                                    getdvrDataStatus: '0',
                                    mesasge: "Data Found",
                                    countdata: [{
                                        no_of_visit: countdata[0].allcount,
                                        no_of_completed: countdata[0].completed,
                                        no_of_incompleted: countdata[0].incompleted,
                                        no_of_contact: countdata[0].contactcount
                                    }],
                                    dvrlist,
                                }],
                                error: " "

                            })


                        } else {

                            res.send({
                                status: false,
                                data: [{
                                    getdvrDataStatus: '1',
                                    mesasge: "Data Not Found"
                                }],
                                error: " "

                            })

                        }


                    } else if (req.query.searchfiltertype == '2') {
                        //for last year
                        var dvrlist = [];
                        const lastyeardate = await getLastYearDate();
                        const currentDate = await getcurrentDate();

                        if (sale_person == undefined) {
                            querydvr = "select  dvr_id, date_of_visit,customer_name,contact_name,message,physical_meeting,sale_person,nextstep,second_contact,objective,est_Date,estvalue,func,person,stage_id from dvr_report where date_of_visit between'" + lastyeardate + "' and '" + currentDate + "' and delete_id=0  order by DATE_FORMAT(date_of_visit, '%Y%m%d') desc";

                        } else {
                            querydvr = "select  dvr_id, date_of_visit,customer_name,contact_name,message,physical_meeting,sale_person,nextstep,second_contact,objective,est_Date,estvalue,func,person,stage_id from dvr_report where date_of_visit between'" + lastyeardate + "' and '" + currentDate + "' and  contact_name like '%" + sale_person + "%'  and delete_id=0 order by DATE_FORMAT(date_of_visit, '%Y%m%d') desc ";
                        }

                        console.log("lastyeardate", lastyeardate, " ", currentDate);
                        const countquery = "call SP_CountDashboard ('" + lastyeardate + "' , '" + currentDate + "','" + '' + "')";
                        const queryresult = await db.executequery(querydvr);
                        console.log("querydvr-------->", querydvr);

                        const queryresult1 = await db.executequery(countquery);
                        var countdata = queryresult1[0];
                        for (var i = 0; i < queryresult.length; i++) {
                            //  console.log(queryresult[i]);
                            dvrlist.push(queryresult[i])
                        }

                        if (queryresult.length > 0) {
                            // res.send({
                            //     getdvrDataStatus: '0',
                            //     mesasge: "Data Found",
                            //     countdata: [{
                            //         no_of_visit: countdata[0].allcount,
                            //         no_of_completed: countdata[0].completed,
                            //         no_of_incompleted: countdata[0].incompleted,
                            //         no_of_contact: countdata[0].contactcount
                            //     }],
                            //     dvrlist


                            // })

                            res.send({
                                status: true,
                                data: [{
                                    getdvrDataStatus: '0',
                                    mesasge: "Data Found",
                                    countdata: [{
                                        no_of_visit: countdata[0].allcount,
                                        no_of_completed: countdata[0].completed,
                                        no_of_incompleted: countdata[0].incompleted,
                                        no_of_contact: countdata[0].contactcount
                                    }],
                                    dvrlist,
                                }],
                                error: " "

                            })


                        } else {
                            // res.send({
                            //     getdvrDataStatus: '1',
                            //     mesasge: "Data Not Found"
                            // })

                            res.send({
                                status: false,
                                data: [{
                                    getdvrDataStatus: '1',
                                    mesasge: "Data Not Found"
                                }],
                                error: " "

                            })

                        }

                    } else {
                        //by default for last month

                        console.log("normal month");
                        var dvrlist = [];
                        const lastmonthDate = await getLastMonthDate();
                        const currentDate = await getcurrentDate();
                        console.log("lastmonthDate", lastmonthDate, "currentDate", currentDate);

                        if (sale_person == undefined) {
                            querydvr = "select  dvr_id, date_of_visit,customer_name,contact_name,message,physical_meeting,sale_person,nextstep,second_contact,objective,est_Date,estvalue,func,person,stage_id from dvr_report where date_of_visit between'" + lastmonthDate + "' and '" + currentDate + "' and delete_id=0 order by DATE_FORMAT(date_of_visit, '%Y%m%d') desc";

                        }
                        else {
                            querydvr = "select  dvr_id, date_of_visit,customer_name,contact_name,message,physical_meeting,sale_person,nextstep,second_contact,objective,est_Date,estvalue,func,person,stage_id from dvr_report where date_of_visit between'" + lastmonthDate + "' and '" + currentDate + "' and  contact_name like '%" + sale_person + "%'  and delete_id=0 order by DATE_FORMAT(date_of_visit, '%Y%m%d') desc";
                        }

                        const countquery = "call SP_CountDashboard ('" + lastmonthDate + "' , '" + currentDate + "','" + '' + "')";
                        const queryresult = await db.executequery(querydvr);


                        const queryresult1 = await db.executequery(countquery);
                        var countdata = queryresult1[0];


                        for (var i = 0; i < queryresult.length; i++) {
                            dvrlist.push(queryresult[i])
                        }

                        if (queryresult.length > 0) {
                            res.send({
                                status: true,
                                data: [{
                                    getdvrDataStatus: '0',
                                    mesasge: "Data Found",
                                    countdata: [{
                                        no_of_visit: countdata[0].allcount,
                                        no_of_completed: countdata[0].completed,
                                        no_of_incompleted: countdata[0].incompleted,
                                        no_of_contact: countdata[0].contactcount
                                    }],
                                    dvrlist,
                                }],
                                error: " "

                            })

                        } else {
                            // res.send({
                            //     getdvrDataStatus: '1',
                            //     mesasge: "Data Not Found"
                            // })

                            res.send({
                                status: false,
                                data: [{
                                    getdvrDataStatus: '1',
                                    mesasge: "Data Not Found"
                                }],
                                error: " "

                            })

                        }

                    }


                }
            } else {
                var dvrlist = [];
                console.log("in else latest condition")
                const lastmonthDate = await getLastMonthDate();
                const currentDate = await getcurrentDate();
                console.log("lastmonthDate ", lastmonthDate, "  currentDate", currentDate)
                if (sale_person == undefined) {
                    querydvr = "select  dvr_id, date_of_visit,customer_name,contact_name,message,physical_meeting,sale_person,nextstep,second_contact,objective,est_Date,estvalue,func,person,stage_id from dvr_report where date_of_visit between'" + lastmonthDate + "' and '" + currentDate + "' and delete_id=0 order by DATE_FORMAT(date_of_visit, '%Y%m%d') desc";

                } else {
                    console.log("by default value82829293839398")
                    querydvr = "select  dvr_id, date_of_visit,customer_name,contact_name,message,physical_meeting,sale_person,nextstep,second_contact,objective,est_Date,estvalue,func,person,stage_id from dvr_report where date_of_visit between'" + lastmonthDate + "' and '" + currentDate + "' and  contact_name like '%" + sale_person + "%' and delete_id=0 order by DATE_FORMAT(date_of_visit, '%Y%m%d') desc ";
                }
                const countquery = "call SP_CountDashboard ('" + lastmonthDate + "' , '" + currentDate + "' ,'" + '' + "')";

                console.log("countquery", countquery);
                console.log("querydvr", querydvr)
                const queryresult1 = await db.executequery(countquery);
                var countdata = queryresult1[0];

                console.log("countdata", countdata);
                const queryresult = await db.executequery(querydvr);

                for (var i = 0; i < queryresult.length; i++) {
                    // console.log(queryresult[i]);
                    dvrlist.push(queryresult[i])
                }

                // if (dvrlist.length > 0) {
                //     for (var i = 0; i < dvrlist.length; i++) {

                //     }

                // }





                if (queryresult.length > 0) {

                    res.send({
                        status: true,
                        data: [{
                            getdvrDataStatus: '0',
                            mesasge: "Data Found",
                            countdata: [{
                                no_of_visit: countdata[0].allcount,
                                no_of_completed: countdata[0].completed,
                                no_of_incompleted: countdata[0].incompleted,
                                no_of_contact: countdata[0].contactcount
                            }],
                            dvrlist,
                        }],
                        error: " "

                    })

                } else {
                    res.send({
                        status: false,
                        data: [{
                            getdvrDataStatus: '1',
                            mesasge: "Data Not Found"
                        }],
                        error: " "

                    })

                }

            }

        } else {
            //other user
            console.log("in other users");
            var getcustomer_id = [];
            var getcustomer_name = [];


            if (user_id_for_mapp) {
                const query = "select customer_id,user_id from customer_user_mapping where user_id=" + user_id_for_mapp;
                const queryresult_id = await db.executequery(query);
                console.log("queryresult", queryresult_id);
                for (var i = 0; i < queryresult_id.length; i++) {
                    getcustomer_id.push(queryresult_id[i].customer_id);
                }
                console.log(getcustomer_id);
            }

            if (req.query.searchfiltertype) {
                console.log("in searchfiltertype ");
                if (req.query.searchfiltertype.length > 1) {
                    console.log("datafliter");
                    //date seach filter
                    var searchfilter = req.query.searchfiltertype;
                    const dates = searchfilter.split("-");
                    console.log("dates", dates)

                    const firstdate = dates[0] + ' 00:00:01';
                    const seconddate = dates[1] + ' 23:59:59';

                    console.log(firstdate);
                    console.log(seconddate);

                    if (getcustomer_id.length > 0) {
                        console.log("data present in custmer mapping table");
                        const getcust_name = "select customer_id,customer_name from customer_master where customer_id in(" + getcustomer_id + ")";
                        const getqueryresult1 = await db.executequery(getcust_name);
                        console.log("getcust_name", getcust_name)
                        console.log("getcust_name============>", getqueryresult1);
                        for (var i = 0; i < getqueryresult1.length; i++) {
                            getcustomer_name.push(getqueryresult1[i].customer_name);
                        }
                        console.log("getcustomer_name", getcustomer_name);

                        if (getcustomer_name) {
                            for (var i = 0; i < getcustomer_name.length; i++) {
                                const query = "select  dvr_id, date_of_visit,customer_name,contact_name,message,physical_meeting,sale_person,nextstep,second_contact,objective,est_Date,estvalue,func,person,stage_id from dvr_report where date_of_visit between'" + firstdate + "' and '" + seconddate + "'  and  customer_name in ('" + getcustomer_name[i] + "')  and delete_id=0 order by DATE_FORMAT(date_of_visit, '%Y%m%d') desc";
                                dvrlist[i] = await db.executequery(query);

                            }

                            var plano = dvrlist.reduce((acc, el) => acc.concat(el), []);
                            //console.log("plano",plano)


                            dvrlist = plano;
                            console.log("finalResult=================>", dvrlist);

                            const countquery = "call SP_CountDashboard ('" + firstdate + "' , '" + seconddate + "','" + login_id + "')";
                            const queryresult1 = await db.executequery(countquery);
                            console.log("queryresult1", countquery);
                            var countdata = queryresult1[0];


                            if (dvrlist.length > 0) {
                                res.send({
                                    status: true,
                                    data: [{
                                        getdvrDataStatus: '0',
                                        mesasge: "Data Found",
                                        countdata: [{
                                            no_of_visit: countdata[0].allcount,
                                            no_of_completed: countdata[0].completed,
                                            no_of_incompleted: countdata[0].incompleted,
                                            no_of_contact: countdata[0].contactcount
                                        }],
                                        dvrlist
                                    }],
                                    error: " "


                                })

                            } else {
                                res.send({
                                    status: false,
                                    data: [{
                                        getdvrDataStatus: '1',
                                        mesasge: "Data Not Found"
                                    }],
                                    error: " "

                                })

                            }


                        }


                    } else {
                        console.log("data not present in custmer mapping table");
                        res.send({
                            status: false,
                            data: [{
                                getdvrDataStatus: '1',
                                mesasge: "Data Not Found"
                            }],
                            error: " "

                        })

                    }


                    // const query = "select  dvr_id, date_of_visit,customer_name,contact_name,message,physical_meeting,sale_person from dvr_report where date_of_visit between'" + firstdate + "' and '" + seconddate + "'  and  login_id='" + login_id + "' order by DATE_FORMAT(date_of_visit, '%Y%m%d') desc";
                    // const countquery = "call SP_CountDashboard ('" + firstdate + "' , '" + seconddate + + "','" + login_id + "')";
                    // const queryresult = await db.executequery(query);

                    // const queryresult1 = await db.executequery(countquery);
                    // var countdata = queryresult1[0];
                    // for (var i = 0; i < queryresult.length; i++) {
                    //     console.log(queryresult[i]);
                    //     dvrlist.push(queryresult[i])
                    // }

                    // if (queryresult.length > 0) {
                    //     res.send({
                    //         status: true,
                    //         data: [{
                    //             getdvrDataStatus: '0',
                    //             mesasge: "Data Found",
                    //             countdata: [{
                    //                 no_of_visit: countdata[0].allcount,
                    //                 no_of_completed: countdata[0].completed,
                    //                 no_of_incompleted: countdata[0].incompleted,
                    //                 no_of_contact: countdata[0].contactcount
                    //             }],
                    //             dvrlist,
                    //         }],
                    //         error: " "

                    //     })


                    // } else {
                    //     res.send({
                    //         status: false,
                    //         data: [{
                    //             getdvrDataStatus: '1',
                    //             mesasge: "Data Not Found"
                    //         }],
                    //         error: " "

                    //     })

                    // }


                } else {

                    //last week, month,year searchfilter

                    if (req.query.searchfiltertype == '0') {
                        //for last week
                        var dvrlist = [];
                        const lastweek = await getLastWeeksDate();
                        const currentDate = await getcurrentDate();

                        if (getcustomer_id.length > 0) {
                            console.log("data present in custmer mapping table");
                            const getcust_name = "select customer_id,customer_name from customer_master where customer_id in(" + getcustomer_id + ")";
                            const getqueryresult1 = await db.executequery(getcust_name);
                            console.log("getcust_name", getcust_name)
                            console.log("getcust_name============>", getqueryresult1);
                            for (var i = 0; i < getqueryresult1.length; i++) {
                                getcustomer_name.push(getqueryresult1[i].customer_name);
                            }
                            console.log("getcustomer_name", getcustomer_name);

                            if (getcustomer_name) {
                                for (var i = 0; i < getcustomer_name.length; i++) {
                                    const query = "select  dvr_id, date_of_visit,customer_name,contact_name,message,physical_meeting,sale_person,nextstep,second_contact,objective,est_Date,estvalue,func,person,stage_id from dvr_report where date_of_visit between'" + lastweek + "' and '" + currentDate + "'  and  customer_name in ('" + getcustomer_name[i] + "')  and delete_id=0 order by DATE_FORMAT(date_of_visit, '%Y%m%d') desc";
                                    dvrlist[i] = await db.executequery(query);

                                }


                                var plano = dvrlist.reduce((acc, el) => acc.concat(el), []);
                                //console.log("plano",plano)


                                dvrlist = plano;
                                console.log("finalResult=================>", dvrlist);




                                const countquery = "call SP_CountDashboard ('" + lastweek + "' , '" + currentDate + "','" + login_id + "')";
                                const queryresult1 = await db.executequery(countquery);
                                console.log("queryresult1", countquery);
                                var countdata = queryresult1[0];


                                if (dvrlist.length > 0) {
                                    res.send({
                                        status: true,
                                        data: [{
                                            getdvrDataStatus: '0',
                                            mesasge: "Data Found",
                                            countdata: [{
                                                no_of_visit: countdata[0].allcount,
                                                no_of_completed: countdata[0].completed,
                                                no_of_incompleted: countdata[0].incompleted,
                                                no_of_contact: countdata[0].contactcount
                                            }],
                                            dvrlist
                                        }],
                                        error: " "


                                    })

                                } else {
                                    res.send({
                                        status: false,
                                        data: [{
                                            getdvrDataStatus: '1',
                                            mesasge: "Data Not Found"
                                        }],
                                        error: " "

                                    })

                                }


                            }


                        } else {
                            console.log("data not present in custmer mapping table");
                            res.send({
                                status: false,
                                data: [{
                                    getdvrDataStatus: '1',
                                    mesasge: "Data Not Found"
                                }],
                                error: " "

                            })

                        }

                        // const query = "select  dvr_id, date_of_visit,customer_name,contact_name,message,physical_meeting,sale_person from dvr_report where date_of_visit between'" + lastweek + "' and '" + currentDate + "'  and  login_id=" + login_id + "' order by DATE_FORMAT(date_of_visit, '%Y%m%d') desc";
                        // const countquery = "call SP_CountDashboard ('" + lastweek + "' , '" + currentDate + "','" + login_id + "')";
                        // const queryresult = await db.executequery(query);

                        // const queryresult1 = await db.executequery(countquery);
                        // var countdata = queryresult1[0];

                        // for (var i = 0; i < queryresult.length; i++) {
                        //     console.log(queryresult[i]);
                        //     dvrlist.push(queryresult[i])
                        // }

                        // if (queryresult.length > 0) {
                        //     res.send({
                        //         status: true,
                        //         data: [{
                        //             getdvrDataStatus: '0',
                        //             mesasge: "Data Found",
                        //             countdata: [{
                        //                 no_of_visit: countdata[0].allcount,
                        //                 no_of_completed: countdata[0].completed,
                        //                 no_of_incompleted: countdata[0].incompleted,
                        //                 no_of_contact: countdata[0].contactcount
                        //             }],
                        //             dvrlist
                        //         }],
                        //         error: " "


                        //     })


                        // } else {
                        //     res.send({
                        //         status: false,
                        //         data: [{
                        //             getdvrDataStatus: '1',
                        //             mesasge: "Data Not Found"
                        //         }],
                        //         error: " "

                        //     })

                        // }


                    } else if (req.query.searchfiltertype == '2') {
                        //for last year
                        var dvrlist = [];
                        const lastyeardate = await getLastYearDate();
                        const currentDate = await getcurrentDate();

                        if (getcustomer_id.length > 0) {
                            console.log("data present in custmer mapping table");
                            const getcust_name = "select customer_id,customer_name from customer_master where customer_id in(" + getcustomer_id + ")";
                            const getqueryresult1 = await db.executequery(getcust_name);
                            console.log("getcust_name", getcust_name)
                            console.log("getcust_name============>", getqueryresult1);
                            for (var i = 0; i < getqueryresult1.length; i++) {
                                getcustomer_name.push(getqueryresult1[i].customer_name);
                            }
                            console.log("getcustomer_name", getcustomer_name);

                            if (getcustomer_name) {
                                for (var i = 0; i < getcustomer_name.length; i++) {
                                    const query = "select  dvr_id, date_of_visit,customer_name,contact_name,message,physical_meeting,sale_person,nextstep,second_contact,objective,est_Date,estvalue,func,person,stage_id from dvr_report where date_of_visit between'" + lastyeardate + "' and '" + currentDate + "'  and  customer_name in ('" + getcustomer_name[i] + "')  and delete_id=0 order by DATE_FORMAT(date_of_visit, '%Y%m%d') desc";
                                    dvrlist[i] = await db.executequery(query);

                                }


                                var plano = dvrlist.reduce((acc, el) => acc.concat(el), []);
                                // console.log("plano",plano)


                                dvrlist = plano;
                                console.log("finalResult=================>", dvrlist);


                                const countquery = "call SP_CountDashboard ('" + lastyeardate + "' , '" + currentDate + "','" + login_id + "')";
                                const queryresult1 = await db.executequery(countquery);
                                console.log("queryresult1", countquery);
                                var countdata = queryresult1[0];


                                if (dvrlist.length > 0) {
                                    res.send({
                                        status: true,
                                        data: [{
                                            getdvrDataStatus: '0',
                                            mesasge: "Data Found",
                                            countdata: [{
                                                no_of_visit: countdata[0].allcount,
                                                no_of_completed: countdata[0].completed,
                                                no_of_incompleted: countdata[0].incompleted,
                                                no_of_contact: countdata[0].contactcount
                                            }],
                                            dvrlist
                                        }],
                                        error: " "


                                    })

                                } else {
                                    res.send({
                                        status: false,
                                        data: [{
                                            getdvrDataStatus: '1',
                                            mesasge: "Data Not Found"
                                        }],
                                        error: " "

                                    })

                                }


                            }


                        } else {
                            console.log("data not present in custmer mapping table");
                            res.send({
                                status: false,
                                data: [{
                                    getdvrDataStatus: '1',
                                    mesasge: "Data Not Found"
                                }],
                                error: " "

                            })

                        }

                        // const query = "select  dvr_id, date_of_visit,customer_name,contact_name,message,physical_meeting,sale_person from dvr_report where date_of_visit between'" + lastyeardate + "' and '" + currentDate + "'  and  login_id=" + login_id + "' order by DATE_FORMAT(date_of_visit, '%Y%m%d') desc";
                        // const countquery = "call SP_CountDashboard ('" + lastyeardate + "' , '" + currentDate + "','" + login_id + "')";
                        // const queryresult = await db.executequery(query);

                        // const queryresult1 = await db.executequery(countquery);
                        // var countdata = queryresult1[0];

                        // for (var i = 0; i < queryresult.length; i++) {
                        //     console.log(queryresult[i]);
                        //     dvrlist.push(queryresult[i])
                        // }

                        // if (queryresult.length > 0) {
                        //     res.send({
                        //         status: true,
                        //         data: [{
                        //             getdvrDataStatus: '0',
                        //             mesasge: "Data Found",
                        //             countdata: [{
                        //                 no_of_visit: countdata[0].allcount,
                        //                 no_of_completed: countdata[0].completed,
                        //                 no_of_incompleted: countdata[0].incompleted,
                        //                 no_of_contact: countdata[0].contactcount
                        //             }],
                        //             dvrlist
                        //         }],
                        //         error: " "



                        //     })


                        // } else {
                        //     res.send({
                        //         status: false,
                        //         data: [{
                        //             getdvrDataStatus: '1',
                        //             mesasge: "Data Not Found"
                        //         }],
                        //         error: " "

                        //     })

                        // }

                    } else {
                        //by default for last month

                        console.log("users by last month", login_id);
                        var dvrlist = [];
                        const lastmonthDate = await getLastMonthDate();
                        const currentDate = await getcurrentDate();



                        if (getcustomer_id.length > 0) {
                            console.log("data present in custmer mapping table");
                            const getcust_name = "select customer_id,customer_name from customer_master where customer_id in(" + getcustomer_id + ")";
                            const getqueryresult1 = await db.executequery(getcust_name);
                            console.log("getcust_name", getcust_name)
                            console.log("getcust_name============>", getqueryresult1);
                            for (var i = 0; i < getqueryresult1.length; i++) {
                                getcustomer_name.push(getqueryresult1[i].customer_name);
                            }
                            console.log("getcustomer_name", getcustomer_name);

                            if (getcustomer_name) {
                                for (var i = 0; i < getcustomer_name.length; i++) {
                                    const query = "select  dvr_id, date_of_visit,customer_name,contact_name,message,physical_meeting,sale_person,nextstep,second_contact,objective,est_Date,estvalue,func,person,stage_id from dvr_report where date_of_visit between'" + lastmonthDate + "' and '" + currentDate + "'  and  customer_name in ('" + getcustomer_name[i] + "')  and delete_id=0 order by DATE_FORMAT(date_of_visit, '%Y%m%d') desc";
                                    dvrlist[i] = await db.executequery(query);

                                }


                                var plano = dvrlist.reduce((acc, el) => acc.concat(el), []);
                                //console.log("plano",plano)


                                dvrlist = plano;
                                console.log("finalResult=================>", dvrlist);


                                const countquery = "call SP_CountDashboard ('" + lastmonthDate + "' , '" + currentDate + "','" + login_id + "')";
                                const queryresult1 = await db.executequery(countquery);
                                console.log("queryresult1", countquery);
                                var countdata = queryresult1[0];


                                if (dvrlist.length > 0) {
                                    res.send({
                                        status: true,
                                        data: [{
                                            getdvrDataStatus: '0',
                                            mesasge: "Data Found",
                                            countdata: [{
                                                no_of_visit: countdata[0].allcount,
                                                no_of_completed: countdata[0].completed,
                                                no_of_incompleted: countdata[0].incompleted,
                                                no_of_contact: countdata[0].contactcount
                                            }],
                                            dvrlist
                                        }],
                                        error: " "


                                    })

                                } else {
                                    res.send({
                                        status: false,
                                        data: [{
                                            getdvrDataStatus: '1',
                                            mesasge: "Data Not Found"
                                        }],
                                        error: " "

                                    })

                                }


                            }


                        } else {
                            console.log("data not present in custmer mapping table");
                            res.send({
                                status: false,
                                data: [{
                                    getdvrDataStatus: '1',
                                    mesasge: "Data Not Found"
                                }],
                                error: " "

                            })

                        }


                        // const query = "select  dvr_id, date_of_visit,customer_name,contact_name,message,physical_meeting,sale_person from dvr_report where date_of_visit between'" + lastmonthDate + "' and '" + currentDate + "'  and  login_id=" + login_id + "' order by DATE_FORMAT(date_of_visit, '%Y%m%d') desc";
                        // const countquery = "call SP_CountDashboard ('" + lastmonthDate + "' , '" + currentDate + "','" + login_id + "')";
                        // const queryresult = await db.executequery(query);

                        // const queryresult1 = await db.executequery(countquery);
                        // var countdata = queryresult1[0];

                        // for (var i = 0; i < queryresult.length; i++) {
                        //     console.log(queryresult[i]);
                        //     dvrlist.push(queryresult[i])
                        // }

                        // if (queryresult.length > 0) {
                        //     console.log("dvrlist12345", dvrlist);
                        //     res.send({
                        //         status: true,
                        //         data: [{
                        //             getdvrDataStatus: 0,
                        //             mesasge: "Data Found",
                        //             countdata: [{
                        //                 no_of_visit: countdata[0].allcount,
                        //                 no_of_completed: countdata[0].completed,
                        //                 no_of_incompleted: countdata[0].incompleted,
                        //                 no_of_contact: countdata[0].contactcount
                        //             }],
                        //             dvrlist
                        //         }],
                        //         error: " "


                        //     })

                        // } else {
                        //     res.send({
                        //         status: false,
                        //         data: [{
                        //             getdvrDataStatus: '1',
                        //             mesasge: "Data Not Found"
                        //         }],
                        //         error: " "

                        //     })

                        // }

                    }


                }
            } else {

                console.log("in else part of bydefult");
                var dvrlist = [];

                const lastmonthDate = await getLastMonthDate();
                const currentDate = await getcurrentDate();

                console.log("lastmonthDate", lastmonthDate, "currentDate", currentDate);

                if (getcustomer_id.length > 0) {
                    console.log("data present in custmer mapping table");
                    const getcust_name = "select customer_id,customer_name from customer_master where customer_id in(" + getcustomer_id + ")";
                    const getqueryresult1 = await db.executequery(getcust_name);
                    console.log("getcust_name", getcust_name)
                    console.log("getcust_name============>", getqueryresult1);
                    for (var i = 0; i < getqueryresult1.length; i++) {
                        getcustomer_name.push(getqueryresult1[i].customer_name);
                    }
                    console.log("getcustomer_name", getcustomer_name);

                    if (getcustomer_name) {
                        for (var i = 0; i < getcustomer_name.length; i++) {
                            const query = "select  dvr_id, date_of_visit,customer_name,contact_name,message,physical_meeting,sale_person,nextstep,second_contact,objective,est_Date,estvalue,func,person,stage_id from dvr_report where date_of_visit between'" + lastmonthDate + "' and '" + currentDate + "'  and  customer_name in ('" + getcustomer_name[i] + "')  and delete_id=0 order by DATE_FORMAT(date_of_visit, '%Y%m%d') desc";
                            dvrlist[i] = await db.executequery(query);

                        }

                        var plano = dvrlist.reduce((acc, el) => acc.concat(el), []);
                        //console.log("plano",plano)


                        dvrlist = plano;
                        console.log("finalResult=================>", dvrlist);



                        const countquery = "call SP_CountDashboard ('" + lastmonthDate + "' , '" + currentDate + "','" + login_id + "')";
                        const queryresult1 = await db.executequery(countquery);
                        console.log("queryresult1", countquery);
                        var countdata = queryresult1[0];


                        if (dvrlist.length > 0) {
                            res.send({
                                status: true,
                                data: [{
                                    getdvrDataStatus: '0',
                                    mesasge: "Data Found",
                                    countdata: [{
                                        no_of_visit: countdata[0].allcount,
                                        no_of_completed: countdata[0].completed,
                                        no_of_incompleted: countdata[0].incompleted,
                                        no_of_contact: countdata[0].contactcount
                                    }],
                                    dvrlist
                                }],
                                error: " "


                            })

                        } else {
                            res.send({
                                status: false,
                                data: [{
                                    getdvrDataStatus: '1',
                                    mesasge: "Data Not Found"
                                }],
                                error: " "

                            })

                        }


                    }


                } else {
                    console.log("data not present in custmer mapping table");
                    res.send({
                        status: false,
                        data: [{
                            getdvrDataStatus: '1',
                            mesasge: "Data Not Found"
                        }],
                        error: " "

                    })

                }



            }

        }
    }
    catch (err) {
        console.log(err)
        res.send({
            status: true,
            data: [],
            error: err,
            getdvrDataStatus: '1',
        })
    }
}




module.exports.createDvrEntry = async (req, res) => {
    const validationResp = await validation.createDvrentrycheck(req);
    const createdate = new Date();

    if (validationResp.status) {
        try {

            console.log(" req.body.login_id", req.body.created_by);
            const date_of_visit = new Date(req.body.visit_date);
            //const date_of_visit=req.body.visit_date;
            console.log("estvalue:::::::::::::::::::::::::::::::::::::::::::", req.body)
            const query = "INSERT INTO dvr_report(date_of_visit,customer_name,contact_name,physical_meeting,message,created_by,created_on,stage_id,login_id,sale_person,delete_id,nextstep,second_contact,objective,est_Date,estvalue,func,person)" +
                "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            const values = [date_of_visit, req.body.customer_name, req.body.contact_name, req.body.physical_metting, req.body.message, req.body.created_by, createdate, req.body.stage_id, req.body.created_by, req.body.sale_person, 0, req.body.nextstep, req.body.second_contact, req.body.objective, req.body.estDate, req.body.deal,req.body.func,req.body.person]

            // console.log("query",query);
            //console.log("values",values);
            const res1 = await db.executevaluesquery(query, values);
            console.log("res1", res1);
            if (res1) {
                res.send({
                    status: true,
                    data: [{
                        message: "Daily visit Entry created Successfully.",
                        createDvrStatus: "0",
                        exception: {
                            errorid: "",
                            errormessage: "No Errors"
                        }
                    }],
                    errors: ""


                })
            } else {
                res.send({
                    status: true,
                    data: [{
                        message: "",
                        createDvrStatus: "1",
                        exception: {
                            errorid: "404",
                            errormessage: "Bad request"
                        }
                    }],
                    errors: ""

                })
            }
        }
        catch (err) {
            console.log(err)
            res.send({
                status: false,
                data: [{
                    message: "",
                    createDvrStatus: "1",
                    exception: {
                        errorid: "404",
                        errormessage: "Bad request"
                    }
                }],
                errors: ""

            })
        }
    } else {
        res.send(validationResp)
    }

}



module.exports.updatedvrDetails = async (req, res) => {
    const validationResp = await validation.updatedvrDetailscheck(req);
    const updatedDate = await getcurrentDate();

    if (validationResp.status) {
        try {
            const query = "UPDATE dvr_report SET date_of_visit = '" + req.body.visit_date + "' ,customer_name= '" + req.body.customer_name + "',contact_name= '" + req.body.contact_name + "',message= '" + req.body.message + "' ,physical_meeting= '" + req.body.physical_metting + "' ,updated_by= '" + req.body.updated_by + "', updated_on= '" + updatedDate + "', stage_id= '" + req.body.stage_id + "' , nextstep= '" + req.body.nextstep + "', second_contact= '" + req.body.second_contact + "', objective= '" + req.body.objective + "', estDate= '" + req.body.estDate + "', deal= '" + req.body.deal + "',func= '" + req.body.func + "',person= '" + req.body.person + "' where dvr_id= '" + req.body.dvr_id + "'";
            console.log("query", query);
            const queryresult = await db.executequery(query);
            console.log("queryresult", queryresult);

            if (queryresult) {
                res.send({
                    status: true,
                    data: [
                        {
                            getdvrDataStatus: '0',
                            message: "Daliy visit Report Entry updated suceesfully."
                        }
                    ],
                    errors: " "

                })

            } else {
                res.send({
                    status: true,
                    data: [{
                        getdvrDataStatus: '1',
                        message: "Daliy visit Report Entry not updated. Please try again after some time!"
                    }],
                    errors: ""


                })
            }
        }
        catch (err) {
            console.log(err)
            res.send({
                status: true, data: [], errors: err
            })
        }
    } else {
        res.send(validationResp)
    }
}



module.exports.createCustomerentry = async (req, res) => {
    const validationResp = await validation.createCustomerentrycheck(req);
    const createdate = new Date();
    if (validationResp.status) {
        try {
            const query = "INSERT INTO customer_master(customer_name,created_by,created_on)" +
                "VALUES(?,?,?)";
            const values = [req.body.customer_name, req.body.created_by, createdate]
            const res3 = await db.executevaluesquery(query, values);
            //console.log("res1", res1);
            if (res3) {
                res.send({
                    message: "Customer Entry created Successfully ",
                    createcustomerStatus: "0",
                    exception: {
                        errorid: "",
                        errormessage: "No Errors"
                    }

                })
            } else {
                res.send({
                    message: "",
                    createStatus: "1",
                    exception: {
                        errorid: "404",
                        errormessage: "Bad request"
                    }
                })
            }
        }
        catch (err) {
            console.log(err)
            res.send({
                status: true, data: []
            })
        }
    } else {
        res.send(validationResp)
    }

}

module.exports.updateContactDetails = async (req, res) => {
    console.log(req);
    const validationResp = await validation.updateContactDetailscheck(req);
    const updatedDate = await getcurrentDate();

    if (validationResp.status) {
        try {
            const query = "UPDATE contact_master SET customer_id= '" + req.body.customer_id + "',first_name= '" + req.body.first_name + "',last_name= '" + req.body.last_name + "' ,mobile_number= '" + req.body.mobile_number + "' ,email_id= '" + req.body.email_Id + "', designation= '" + req.body.designation + "', department= '" + req.body.department + "', updated_by= '" + req.body.updated_by + "', updated_on= '" + updatedDate + "' , mob_no= '" + req.body.mob_no + "'  where contact_id= '" + req.body.contact_id + "'";
            console.log("query", query);
            const queryresult = await db.executequery(query);
            console.log("queryresult", queryresult);

            if (queryresult) {
                res.send({
                    updateContactStatus: '0',
                    mesasge: "Contact Entry updated suceesfully."
                })

            } else {
                res.send({
                    updateContactStatus: '1',
                    mesasge: "Contact Entry not updated."

                })
            }
        }
        catch (err) {
            console.log(err)
            res.send({
                status: true, data: []
            })
        }
    } else {
        res.send(validationResp)
    }
}

module.exports.deleteContactDetails = async (req, res) => {
    console.log(req);
    const validationResp = await validation.deleteContactDetailscheck(req);

    if (validationResp.status) {
        try {
            const query1 = "UPDATE contact_master SET updated_by='" + req.body.updated_by + "' WHERE contact_id = '" + req.body.contact_id +"';";
            console.log("query", query1);
            const query2 = "DELETE FROM contact_master WHERE contact_id = '" + req.body.contact_id +"';";
            const queryresult1 = await db.executequery(query1);
            const queryresult = await db.executequery(query2);
            console.log("query", query2);
            console.log("queryresult1", queryresult1);
            console.log("queryresult2", queryresult);

            if (queryresult) {
                res.send({
                    deleteContactStatus: '0',
                    mesasge: "Contact Entry deleted suceesfully."
                })

            } else {
                res.send({
                    deleteContactStatus: '1',
                    mesasge: "Contact Entry not deleted."

                })
            }
        }
        catch (err) {
            console.log(err)
            res.send({
                status: true, data: []
            })
        }
    } else {
        res.send(validationResp)
    }
}

module.exports.updateCustomerDetails = async (req, res) => {
    const validationResp = await validation.updateCustomerDetailscheck(req);
    const updatedDate = await getcurrentDate();
    console.log(req.body);
    if (validationResp.status) {
        if (req.body.sale_person) {
            var saleData = req.body.sale_person.split(" ");
            const query0 =  "select user_id from user_master where first_name= '" + saleData[0] + "' and last_name= '" + saleData[1] + "'";
            console.log("query", query0);
            const query0result = await db.executequery(query0);
            try {
                if (query0result[0].user_id != req.body.old_sale_person) {
                    const query1 = "UPDATE customer_user_mapping set user_id =" + query0result[0].user_id + ", updated_by = '" + req.body.updated_by + "', updated_on = '" + updatedDate + "' where customer_id = " + req.body.customer_id + " and user_id =" + req.body.old_sale_person + ";";
                    console.log("query", query1);
                    const query1result = await db.executequery(query1);
                    console.log(query1result);
                }
                else {
                console.log("Same User ID found. " + query0result[0].user_id + " == " + req.body.old_sale_person);
                }
                const query = "UPDATE customer_master SET customer_name = '" + req.body.customer_name + "', customer_status= '" + req.body.status + "', updated_on= '" + updatedDate + "', updated_by= '" + req.body.updated_by + "'  where customer_id= '" + req.body.customer_id + "'";
                console.log("query", query);
                const queryresult = await db.executequery(query);
                console.log("queryresult", queryresult);

                if (queryresult) {
                    res.send({
                        updateCustomerStatus: '0',
                        mesasge: "Customer Entry updated suceesfully."
                    })

                } else {
                    res.send({
                        updateCustomerStatus: '1',
                        mesasge: "Customer Entry not updated."

                    })
                }
            }
            catch (err) {
                console.log(err)
                res.send({
                    status: true, data: []
                })
            }
        }
        else {
            {
                res.send({
                    updateCustomerStatus: '1',
                    mesasge: "Sale Person not selected."
                })
            }
        }
    
    } else {
        res.send(validationResp)
        }
    }


module.exports.addCustomerUserMapping = async (req, res) => {
    const createdate = new Date();
    const query = "select customer_id, user_id from customer_user_mapping where customer_id = " + req.body.customer_id + " and user_id = " + req.body.user_id + ";";
    const queryresult = await db.executequery(query);
    console.log(queryresult);

    if (queryresult.length<1) {
        console.log("INSERTING");

        const finalquery = "INSERT INTO customer_user_mapping(customer_id,user_id,created_by,created_on)" +
                                "VALUES(?,?,?,?)";
        const values = [req.body.customer_id, req.body.user_id, req.body.created_by, createdate]
        const res3 = await db.executevaluesquery(finalquery, values);
        // console.log("res3", res3);
        if (res3) {
            res.send({
                status: true,
                data: [{
                    addCustomerMappingStatus: "0",
                    message: "Customer Mapping Entry created Successfully!",
                    lastinsertedData: req.body.customer_id,
                        exception: {
                                        errorid: "",
                                        errormessage: "No Errors"
                                    }
                        }],
                    error: " "

                })

            } else {
                res.send({
                    status: false,
                    data: [{
                        message: "Customer Mapping Entry  Not created",
                        addCustomerMappingStatus: "1",
                        exception: {
                                        errorid: "404",
                                        errormessage: "Bad request"
                                    }
                }],
                error: ""
            })
        }

    }

    else {
            res.send({
            status: false,
            data: [{
                message: "Customer Mapping Entry Already Present",
                addCustomerMappingStatus: "1",
                exception: {
                    errorid: "404",
                    errormessage: "User Already mapped to customer"
                    }
                }],
                error: ""
            })
    }
}


module.exports.getlastestdvr = async (req, res) => {
    try {
        var insertedRecord = [];

        var startdate = new Date();
        var enddate = new Date();
        const format2 = "YYYY-MM-DD";
        startdate = moment(startdate).format(format2) + ' 00:00:01';
        enddate = moment(enddate).format(format2) + ' 23:59:59';


        var person_role = req.query.role;
        var login_id = req.query.login_id;
        console.log("login_id", login_id);

        if (person_role == 'admin') {
            //admin show all data
            const query = "SELECT d.dvr_id,d.date_of_visit,d.customer_name,d.contact_name,d.physical_meeting,d.message,s.stage_desc,d.stage_id,d.nextstep,d.second_contact,d.objective,d.est_Date,d.estvalue,d.func,d.person FROM dvr_report d JOIN stage_master s ON d.stage_id=s.stage_id where  d.created_on between '" + startdate + "' and '" + enddate + "' and d.delete_id=0 order by d.date_of_visit desc";
            const queryresult = await db.executequery(query);
            console.log("query", query);

            for (var i = 0; i < queryresult.length; i++) {
                console.log(queryresult[i]);
                insertedRecord.push(queryresult[i])
            }

            //console.log("res1", res1);
            if (queryresult.length > 0) {
                res.send({
                    status: true,
                    data: [{
                        dvrData: insertedRecord
                    }],
                    error: ""
                })



            } else {
                res.send({
                    status: true,
                    data: [{
                        getdvrDataStatus: '1',
                        mesasge: "Data Not Found"
                    }],
                    error: ""

                })

            }

        } else {
            // other users 
            console.log("other users");
            const query = "SELECT d.dvr_id,d.date_of_visit,d.customer_name,d.contact_name,d.physical_meeting,d.message,s.stage_desc,d.stage_id,d.nextstep,d.second_contact,d.objective,d.est_Date,d.estvalue,d.func,d.person FROM dvr_report d JOIN stage_master s ON d.stage_id=s.stage_id where d.login_id='" + login_id + "' and d.created_on between '" + startdate + "' and '" + enddate + "' and d.delete_id=0 order by d.date_of_visit desc";
            const queryresult = await db.executequery(query);
            console.log("query", query);

            for (var i = 0; i < queryresult.length; i++) {
                console.log(queryresult[i]);
                insertedRecord.push(queryresult[i])
            }

            console.log("queryresult", queryresult);
            if (queryresult.length > 0) {
                res.send({
                    status: true,
                    data: [{
                        dvrData: insertedRecord
                    }],
                    error: ""
                })



            } else {
                res.send({
                    status: true,
                    data: [{
                        getdvrDataStatus: '1',
                        mesasge: "Data Not Found"
                    }],
                    error: ""

                })

            }

        }




    }
    catch (err) {
        console.log(err)
        res.send({
            status: true,
            data: [],
            getdvrDataStatus: '1',
        })
    }
}

module.exports.getcontactDetails = async (req, res) => {
    try {
        var insertedRecord = [];
        var startdate = new Date();
        var enddate = new Date();
        const format2 = "YYYY-MM-DD";
        startdate = moment(startdate).format(format2) + ' 00:00:01';
        enddate = moment(enddate).format(format2) + ' 23:59:59';
        var person_role = req.query.role;
        var login_id = req.query.login_id;


        if (person_role == 'admin') {
            //admin show all data
            const query = "SELECT first_name,last_name,mobile_number,email_id,designation,department FROM contact_master where created_on between '" + startdate + "' and '" + enddate + "' order by contact_id desc";
            const queryresult = await db.executequery(query);
            console.log("query", query);

            for (var i = 0; i < queryresult.length; i++) {
                // console.log(queryresult[i]);
                insertedRecord.push(queryresult[i])
            }
            if (queryresult.length > 0) {
                res.send({
                    status: true,
                    data: [{
                        conrtactData: insertedRecord
                    }],
                    error: ""
                })



            } else {
                res.send({
                    status: true,
                    data: [{
                        getdvrDataStatus: '1',
                        mesasge: "Data Not Found"
                    }],
                    error: ""

                })

            }

        } else {
            //others users
            const query = "SELECT first_name,last_name,mobile_number,email_id,designation,department FROM contact_master where created_by= '" + login_id + "' and created_on between '" + startdate + "' and '" + enddate + "' order by contact_id desc";
            const queryresult = await db.executequery(query);
            console.log("query", query);

            for (var i = 0; i < queryresult.length; i++) {
                // console.log(queryresult[i]);
                insertedRecord.push(queryresult[i])
            }
            if (queryresult.length > 0) {
                res.send({
                    status: true,
                    data: [{
                        conrtactData: insertedRecord
                    }],
                    error: ""
                })



            } else {
                res.send({
                    status: true,
                    data: [{
                        getdvrDataStatus: '1',
                        mesasge: "Data Not Found"
                    }],
                    error: ""

                })

            }
        }






    } catch (err) {
        console.log(err)
        res.send({
            status: true,
            data: [],
            getdvrDataStatus: '1',
        })
    }
}

module.exports.getsalePersonList = async (req, res) => {
    console.log("in getsalePersonList");

    try {
        var salePerson = [];
        var full_name = [];
        const query = "SELECT user_id,first_name,last_name from user_master;";
        const queryresult = await db.executequery(query);

        if (queryresult.length > 0) {
            for (var i = 0; i < queryresult.length; i++) {
                console.log("queryresult", queryresult[i])
                salePerson.push(queryresult[i]);
            }

        }

        console.log("salePerson", salePerson);
        res.send({
            status: true,
            data: [{
                salePerson: salePerson
            }],
            error: ""
        })

    } catch (err) {
        console.log(err)
        res.send({
            status: true, data: [], error: err
        })
    }


}


module.exports.getCustcontactDetails = async (req, res) => {
    try {
        var contactdetails = [];
        var customer_id = req.query.customer_id;
        var check_api = req.query.passid;

        var passing_id;
        console.log("customer_id", customer_id);
        console.log("check api", check_api);

        if (check_api == undefined) {
            console.log("pass customer name");

            const query = "SELECT customer_id,customer_name FROM customer_master where customer_name=  '" + customer_id + "' ";
            const queryresult = await db.executequery(query);
            // console.log("queryresult", queryresult);

            console.log("result------>", queryresult[0].customer_id);
            passing_id = queryresult[0].customer_id;


            // const query_id = "SELECT first_name,last_name,mobile_number,email_id,designation,department,function_area,mob_no,stage_id FROM contact_master where customer_id=  '" + passing_id + "' order by first_name";
            const query_id = "SELECT contact_id,customer_name,first_name,last_name,mobile_number,email_id,designation,department,function_area,mob_no,stage_id FROM contact_master JOIN customer_master ON contact_master.customer_id=customer_master.customer_id where contact_master.customer_id= '" + passing_id + "' order by first_name;"
            const queryresult_id = await db.executequery(query_id);
            console.log("query", query);

            for (var i = 0; i < queryresult_id.length; i++) {
                // console.log(queryresult_id[i]);
                contactdetails.push(queryresult_id[i])
            }
            if (queryresult.length > 0) {
                res.send({
                    status: true,
                    data: [{
                        conrtactData: contactdetails
                    }],
                    error: ""
                })



            } else {
                res.send({
                    status: true,
                    data: [{
                        getdvrDataStatus: '1',
                        mesasge: "Data Not Found"
                    }],
                    error: ""

                })

            }

        } else {
            console.log("pass number");

            if (customer_id == 0) {
                console.log("getting all contact data");
                // const query = "SELECT first_name,last_name,mobile_number,email_id,designation,department,function_area,mob_no,stage_id FROM contact_master order by first_name";
                const query = "SELECT contact_id,customer_name,first_name,last_name,mobile_number,email_id,designation,department,function_area,mob_no,stage_id FROM contact_master JOIN customer_master ON contact_master.customer_id=customer_master.customer_id order by first_name;"

                const queryresult = await db.executequery(query);
                console.log("query", query);

                for (var i = 0; i < queryresult.length; i++) {
                    // console.log(queryresult[i]);
                    contactdetails.push(queryresult[i])
                }
                if (queryresult.length > 0) {
                    res.send({
                        status: true,
                        data: [{
                            conrtactData: contactdetails
                        }],
                        error: ""
                    })



                } else {
                    res.send({
                        status: true,
                        data: [{
                            getdvrDataStatus: '1',
                            mesasge: "Data Not Found"
                        }],
                        error: ""

                    })

                }

            } else {
                console.log("getting only specific customer contact data");
                // const query = "SELECT first_name,last_name,mobile_number,email_id,designation,department,function_area,mob_no,stage_id FROM contact_master where customer_id=  '" + customer_id + "' order by first_name";
                const query = "SELECT contact_id,customer_name,first_name,last_name,mobile_number,email_id,designation,department,function_area,mob_no,stage_id FROM contact_master JOIN customer_master ON contact_master.customer_id=customer_master.customer_id where contact_master.customer_id= '" + customer_id + "' order by first_name;"

                const queryresult = await db.executequery(query);
                console.log("query", query);

                for (var i = 0; i < queryresult.length; i++) {
                    // console.log(queryresult[i]);
                    contactdetails.push(queryresult[i])
                }
                if (queryresult.length > 0) {
                    res.send({
                        status: true,
                        data: [{
                            conrtactData: contactdetails
                        }],
                        error: ""
                    })



                } else {
                    res.send({
                        status: true,
                        data: [{
                            getdvrDataStatus: '1',
                            mesasge: "Data Not Found"
                        }],
                        error: ""

                    })

                }
            }



        }


    } catch (err) {
        console.log(err)
        res.send({
            status: true,
            data: [],
            getdvrDataStatus: '1',
        })
    }
}



module.exports.createUpdateCustomerentry = async (req, res) => {
    const validationResp = await validation.createCustomerentrycheck(req);
    const createdate = new Date();

    if (validationResp.status) {
        try {

            if (req.body.action == 'insert') {
                console.log("customer added");
                const checkdub = "select * from customer_master where customer_name='" + req.body.customer_name + "'";
                const checkqueryresult = await db.executequery(checkdub);
                console.log("checkqueryresult", checkqueryresult.length)
                if (checkqueryresult.length > 0) {
                    //allready all present
                    res.send({
                        status: false,
                        data: [],
                        errors: "Customer Entry allready Exit ! Please Enter Correct Customer Name"
                    })


                } else {
                    //data not is present

                    const query = "INSERT INTO customer_master(customer_name,customer_status,created_by,created_on)" +
                        "VALUES(?,?,?,?)";
                    const values = [req.body.customer_name, req.body.status, req.body.created_by, createdate]
                    const res3 = await db.executevaluesquery(query, values);
                    // console.log("response", res3);
                    if (res3) {

                        const query = "select customer_id from customer_master order by customer_id desc limit 1";
                        const queryresult = await db.executequery(query);
                        console.log("query result----->", queryresult);

                        console.log("query result----->", queryresult[0].customer_id);
                        if (queryresult) {
                            // const query = "select customer_id from customer_master order by customer_id desc limit 1";
                            // const queryresult = await db.executequery(query);
                            var sale_person = req.body.sale_person;
                            var name = sale_person.split(" ");
                            console.log("name", name);
                            var first_name = name[0];
                            var last_name = name[1];
                            console.log(first_name, last_name);

                            const queryuserID = "SELECT user_id FROM user_master where first_name='" + first_name + "' and last_name= '" + last_name + "'";
                            console.log("queryuserID", queryuserID);
                            const queryresultID = await db.executequery(queryuserID);

                            console.log("queryresultID", queryresultID);


                            const finalquery = "INSERT INTO customer_user_mapping(customer_id,user_id,created_by,created_on)" +
                                "VALUES(?,?,?,?)";
                            const values = [queryresult[0].customer_id, queryresultID[0].user_id, req.body.created_by, createdate]
                            const res3 = await db.executevaluesquery(finalquery, values);
                            // console.log("res3", res3);
                            if (res3) {
                                res.send({

                                    status: true,
                                    data: [{
                                        createcustomerStatus: "0",
                                        message: "Customer Entry created Successfully!",
                                        lastinsertedData: req.body.customer_name,
                                        exception: {
                                            errorid: "",
                                            errormessage: "No Errors"
                                        }
                                    }],
                                    error: " "


                                })

                            } else {
                                res.send({
                                    status: false,
                                    data: [{
                                        message: "Customer Entry  Not created",
                                        createcustomerStatus: "1",
                                        exception: {
                                            errorid: "404",
                                            errormessage: "Bad request"
                                        }
                                    }],
                                    error: ""

                                })
                            }

                        }
                    } else {
                        res.send({
                            status: true,
                            data: [],
                            errors: "Bad Request"

                        })

                    }


                }



            } else {
                console.log("customer update");

                const query = "UPDATE customer_master SET customer_name= '" + req.body.customer_name + "', customer_status= '" + req.body.customer_status + "', updated_on= '" + createdate + "' ,updated_by= '" + req.body.created_by + "' where customer_id= '" + req.body.customer_id + "'";
                console.log("query", query);
                const queryresult = await db.executequery(query);
                console.log("queryresult", queryresult);
                //dates = searchfilter.split("-");


                var saleData = req.body.sale_person.split(" ");
                console.log("saleData");

                //select user_id from user_master where first_name= "" and last_name="";
                if (queryresult) {
                    const getsaleperson = "select user_id from user_master where first_name= '" + saleData[0] + "' and last_name= '" + saleData[1] + "'";
                    const getqueryresult = await db.executequery(getsaleperson);

                    if (getqueryresult) {
                        const checkdupli = "select * from customer_user_mapping where user_id=" + getqueryresult[0].user_id + " and customer_id= '" + req.body.customer_id + "' order by idcustCont_mapp desc limit 1";
                        const checkexcutequer = await db.executequery(checkdupli);


                        if (checkexcutequer[0].user_id == getqueryresult[0].user_id) {
                            //same user for customer update
                            console.log("same user for customer update");
                            console.log("database date", formatDateAsPer(checkexcutequer[0].effectivedate_to));
                            console.log("req body", req.body.effectivedate_to)

                            if (req.body.effectivedate_to.length > 0) {
                                if (req.body.effectivedate_from > req.body.effectivedate_to) {
                                    res.send({
                                        status: false,
                                        data: [],
                                        errors: "Please enter correct Effective From Date! Make sure Efffective From Date is less than Effective To Date"
                                    })
                                } else {
                                    const query = "INSERT INTO customer_user_mapping(customer_id,user_id,effiectivedate_from,effectivedate_to,created_by,created_on)" +
                                        "VALUES(?,?,?,?,?,?)";
                                    const values = [req.body.customer_id, getqueryresult[0].user_id, req.body.effectivedate_from, req.body.effectivedate_to.length > 0 ? req.body.effectivedate_to : null, req.body.created_by, createdate]
                                    const res3 = await db.executevaluesquery(query, values);
                                    console.log("response--------->", res3);

                                    if (res3) {
                                        res.send({

                                            status: true,
                                            data: [{
                                                message: "Customer Entry Update Successfully!",
                                                createcustomerStatus: "0",
                                                lastinsertedData: req.body.customer_name,
                                                exception: {
                                                    errorid: "",
                                                    errormessage: "No Errors"
                                                }
                                            }],
                                            error: " "


                                        })

                                    } else {
                                        res.send({
                                            status: true,
                                            data: [{
                                                message: "Customer Entry Not Updated.",
                                                createStatus: "1",
                                                exception: {
                                                    errorid: "404",
                                                    errormessage: "Bad request"
                                                }
                                            }],
                                            error: ""

                                        })
                                    }

                                }
                            } else {
                                //same user id  for effective different date.
                                const query = "INSERT INTO customer_user_mapping(customer_id,user_id,effiectivedate_from,effectivedate_to,created_by,created_on)" +
                                    "VALUES(?,?,?,?,?,?)";
                                const values = [req.body.customer_id, getqueryresult[0].user_id, req.body.effectivedate_from, req.body.effectivedate_to.length > 0 ? req.body.effectivedate_to : null, req.body.created_by, createdate]
                                const res3 = await db.executevaluesquery(query, values);
                                console.log("response--------->", res3);

                                if (res3) {
                                    res.send({

                                        status: true,
                                        data: [{
                                            message: "Customer Entry Update Successfully!",
                                            createcustomerStatus: "0",
                                            lastinsertedData: req.body.customer_name,
                                            exception: {
                                                errorid: "",
                                                errormessage: "No Errors"
                                            }
                                        }],
                                        error: " "


                                    })

                                } else {
                                    res.send({
                                        status: true,
                                        data: [{
                                            message: "Customer Entry Not Updated.",
                                            createStatus: "1",
                                            exception: {
                                                errorid: "404",
                                                errormessage: "Bad request"
                                            }
                                        }],
                                        error: ""

                                    })
                                }

                            }


                        } else {
                            //other user for customer update
                            if (req.body.effectivedate_to.length > 0 && checkexcutequer[0].effectivedate_to.length > 0) {
                                if (req.body.effectivedate_to == formatDateAsPer(checkexcutequer[0].effectivedate_to)) {
                                    res.send({
                                        status: false,
                                        data: [],
                                        errors: "Please enter correct Effective To Date are same for other sales Person "
                                    })
                                }

                            } else if (req.body.effectivedate_to.length > 0) {
                                if (req.body.effectivedate_from > req.body.effectivedate_to) {
                                    res.send({
                                        status: false,
                                        data: [],
                                        errors: "Please enter correct Effective From Date! Make sure Efffective From Date is less than Effective To Date"
                                    })
                                } else if (req.body.effectivedate_from == req.body.effectivedate_to) {

                                    res.send({
                                        status: false,
                                        data: [],
                                        errors: "Please enter correct Effective From  and Effection To Date ! Both Dates are same"
                                    })
                                } else {
                                    const query = "INSERT INTO customer_user_mapping(customer_id,user_id,effiectivedate_from,effectivedate_to,created_by,created_on)" +
                                        "VALUES(?,?,?,?,?,?)";
                                    const values = [req.body.customer_id, getqueryresult[0].user_id, req.body.effectivedate_from, req.body.effectivedate_to.length > 0 ? req.body.effectivedate_to : null, req.body.created_by, createdate]
                                    const res3 = await db.executevaluesquery(query, values);
                                    console.log("response--------->", res3);
                                    if (res3) {
                                        res.send({

                                            status: true,
                                            data: [{
                                                message: "Customer Entry Update Successfully!",
                                                createcustomerStatus: "0",
                                                lastinsertedData: req.body.customer_name,
                                                exception: {
                                                    errorid: "",
                                                    errormessage: "No Errors"
                                                }
                                            }],
                                            error: " "


                                        })

                                    } else {
                                        res.send({
                                            status: true,
                                            data: [{
                                                message: "Customer Entry  Not Updated",
                                                createStatus: "1",
                                                exception: {
                                                    errorid: "404",
                                                    errormessage: "Bad request"
                                                }
                                            }],
                                            error: ""

                                        })
                                    }

                                }

                            } else {
                                //all ok
                                const query = "INSERT INTO customer_user_mapping(customer_id,user_id,effiectivedate_from,effectivedate_to,created_by,created_on)" +
                                    "VALUES(?,?,?,?,?,?)";
                                const values = [req.body.customer_id, getqueryresult[0].user_id, req.body.effectivedate_from, req.body.effectivedate_to.length > 0 ? req.body.effectivedate_to : null, req.body.created_by, createdate]
                                const res3 = await db.executevaluesquery(query, values);
                                console.log("response--------->", res3);
                                if (res3) {
                                    res.send({

                                        status: true,
                                        data: [{
                                            message: "Customer Entry Update Successfully!",
                                            createcustomerStatus: "0",
                                            lastinsertedData: req.body.customer_name,
                                            exception: {
                                                errorid: "",
                                                errormessage: "No Errors"
                                            }
                                        }],
                                        error: " "


                                    })

                                } else {
                                    res.send({
                                        status: true,
                                        data: [{
                                            message: "Customer Entry  Not Updated",
                                            createStatus: "1",
                                            exception: {
                                                errorid: "404",
                                                errormessage: "Bad request"
                                            }
                                        }],
                                        error: ""

                                    })
                                }

                            }

                        }

                    }
                }


            }


        }
        catch (err) {
            console.log(err)
            res.send({
                status: true, data: [],
                errors: err
            })
        }
    } else {
        res.send(validationResp)
    }

}


module.exports.deletedvrEntry = async (req, res) => {
    try {
        //DELETE FROM table_name WHERE condition;

        console.log("req.body", req.body);
        const updatedDate = await getcurrentDate();

        const query = "UPDATE dvr_report SET delete_id = 1,updated_by= '" + req.body.created_by + "', updated_on= '" + updatedDate + "' where dvr_id= " + req.body.dvr_id + "";
        const checkqueryresult = await db.executequery(query);
        console.log("checkqueryresult", checkqueryresult)
        console.log("eeeeeeee", checkqueryresult.affectedRows);
        if (checkqueryresult) {
            //entry not deleted
            res.send({
                status: true,
                data: [{
                    message: "DVR Entry Deleted Successfully!",
                    deleteStatus: "0"
                }],
                error: ""


            })
        } else {
            //entry deleted
            res.send({
                status: true,
                data: [{
                    message: "DVR Entry Not Deleted.",
                    deleteStatus: "1",
                    exception: {
                        errorid: "404",
                        errormessage: "Bad request"
                    }
                }],
                error: ""
            })
        }

    } catch (err) {
        console.log(err)
        res.send({
            status: true,
            data: [],
            getdvrDataStatus: '1',
        })
    }
}



// module.exports.mappingCustomerDetails = async (req, res) => {
//     // Validate the request
//     const validationResp = await validation.mappingCustomerDetailsCheck(req);

//     if (validationResp.status) {
//         try {
//             // Extract salesperson's name
//             const sale_person = req.body.sale_person;
//             const name = sale_person.split(" ");
//             const first_name = name[0];
//             const last_name = name[1];
//             console.log("Extracted names:", first_name, last_name);

//             // SQL query to get user_id from user_master
//             const queryUserID = "SELECT user_id FROM user_master WHERE first_name='" + first_name + "' AND last_name='" + last_name + "'";
//             console.log("queryUserID", queryUserID);
//             const queryResultID = await db.executequery(queryUserID);
//             console.log("User ID query result:", queryResultID);

//             if (queryResultID.length > 0) {
//                 res.send({
//                     status: true,
//                     data: {
//                         user_id: queryResultID[0].user_id
//                     },
//                     error: null
//                 });
//             } else {
//                 res.send({
//                     status: false,
//                     data: [],
//                     errors: "No user found with the specified name."
//                 });
//             }
//         } catch (err) {
//             console.error("Error occurred:", err);
//             res.send({
//                 status: false,
//                 data: [],
//                 errors: "An error occurred while fetching the user ID."
//             });
//         }
//     } else {
//         res.send(validationResp);
//     }
// }

module.exports.mappingCustomerDetails = async (req, res) => {
    // Validate the request
    console.log("sale_", req.query.sale_person)
    const validationResp = await validation.mappingCustomerDetailsCheck(req);

    if (validationResp.status) {
        try {
            const sale_person = req.query.sale_person;
            const name = sale_person.split(" ");
            const first_name = name[0];
            const last_name = name[1];
            console.log("Extracted names:", first_name, last_name);

            // SQL query to get user_id from user_master
            const queryUserID = "SELECT user_id FROM user_master WHERE first_name='" + first_name + "' AND last_name='" + last_name + "'";
            console.log("queryUserID", queryUserID);
            const queryResultID = await db.executequery(queryUserID);
            console.log("User ID query result:", queryResultID);

            if (queryResultID.length > 0) {
                const userId = queryResultID[0].user_id;

                // SQL query to get customer details associated with the user_id
                const queryCustomerDetails = `
                    SELECT c.customer_id, c.customer_name, u.user_id 
                    FROM customer_master c 
                    JOIN customer_user_mapping u ON c.customer_id = u.customer_id 
                    WHERE u.user_id = '${userId}'`;
                console.log("queryCustomerDetails", queryCustomerDetails);
                const customerDetailsResult = await db.executequery(queryCustomerDetails);
                console.log("Customer details result:", customerDetailsResult);

                if (customerDetailsResult.length > 0) {
                    res.send({
                        status: true,
                        data: customerDetailsResult,
                        error: null
                    });
                } else {
                    res.send({
                        status: false,
                        data: [],
                        errors: "No customer details found for the specified salesperson."
                    });
                }
            } else {
                res.send({
                    status: false,
                    data: [],
                    errors: "No user found with the specified name."
                });
            }
        } catch (err) {
            console.error("Error occurred:", err);
            res.send({
                status: false,
                data: [],
                errors: "An error occurred while fetching the customer details."
            });
        }
    } else {
        res.send(validationResp);
    }
}

module.exports.mappingFuncIDToPersonNames = async (req, res) => {
    // Validate the request
    console.log("func_id_", req.query.func_id);
    const validationResp = await validation.mappingFuncIDToPersonNamesCheck(req);

    if (validationResp.status) {
        try {
            const func_id = req.query.func_id;
            
            // SQL query to get all persons mapped to the given func_id
            // const queryFunctionPersonMapping = `
            //     SELECT f.func_id, p.person_name
            //     FROM dvr_local.function_person_mapping fp
            //     JOIN dvr_local.function_details f ON f.func_id = fp.func_id
            //     JOIN dvr_local.person_details p ON p.person_id = fp.person_id
            //     WHERE fp.func_id = '${func_id}'`;
            const queryFunctionPersonMapping = `
                SELECT f.fun_id, p.person_name
                FROM function_person_mapping fp
                JOIN function_details f ON f.fun_id = fp.fun_id
                JOIN function_person_details p ON p.id_fun_person = fp.id_fun_person
                WHERE fp.fun_id = ${func_id};`

            console.log("queryFunctionPersonMapping", queryFunctionPersonMapping);

            // Use parameterized query to avoid SQL injection
            const queryResult = await db.executequery(queryFunctionPersonMapping, [func_id]);
            console.log("Function-person mapping query result:", queryResult);

            if (queryResult.length > 0) {
                res.send({
                    status: true,
                    data: queryResult,
                    error: null
                });
            } else {
                res.send({
                    status: false,
                    data: [],
                    errors: "No persons are mapped to the specified function ID."
                });
            }
        } catch (err) {
            console.error("Error occurred:", err);
            res.send({
                status: false,
                data: [],
                errors: "An error occurred while fetching the person details for the specified function ID."
            });
        }
    } else {
        res.send(validationResp);
    }
};



function getLastWeeksDate() {
    const now = new Date();
    const format2 = "YYYY-MM-DD";
    const date = new Date(now.getTime() - 7 * 24 * 60 * 60 * 1000);
    // console.log("date", date);

    const dateTime2 = moment(date).format(format2) + ' 00:00:00';

    //  console.log("date------>", dateTime2);
    return dateTime2;

}

function getcurrentDate() {
    const now = new Date();
    const format2 = "YYYY-MM-DD";
    const dateTime2 = moment(now).format(format2) + ' 23:59:59';

    // console.log("date------>", dateTime2);
    return dateTime2;
}


function getLastMonthDate() {
    const now = new Date();
    const format2 = "YYYY-MM-DD";

    const date = new Date(now.getFullYear(), now.getMonth() - 1, 1);
    // console.log("date", date);
    const dateTime2 = moment(date).format(format2) + ' 00:00:00';


    //console.log("date------>", dateTime2);
    return dateTime2;

}

function getLastYearDate() {
    const now = new Date();
    const format2 = "YYYY-MM-DD";

    const date = new Date(now.setFullYear(now.getFullYear() - 1));
    // console.log("date", date);
    const dateTime2 = moment(date).format(format2) + ' 00:00:00';


    //console.log("date------>", dateTime2);
    return dateTime2;
}