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 FROM customer_master"; 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 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 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) => { 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_on= '" + updatedDate + "' , mob_no= '" + req.body.mob_no + "' 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({ 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.updateCustomerDetails = async (req, res) => { const validationResp = await validation.updateCustomerDetailscheck(req); const updatedDate = await getcurrentDate(); if (validationResp.status) { try { const query = "UPDATE customer_master SET customer_id = '" + req.body.customer_id + "', updated_on= '" + updatedDate + "' where customer_master= '" + req.body.contact_master + "'"; 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(validationResp) } } 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 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 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 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,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("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 + "',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}'`; 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; }