
var sqldb = require('../config/dbconnect');
var dbutil = require(appRoot + '/utils/dbutils');
var moment = require('moment');
var request = require('request');
//User Authentication
// exports.gettingLoggedIn = function(usr_nm,pwd,callback) {

//     var cntxtDtls = "in gettingLoggedIn";

//     var QRY_TO_EXEC = `SELECT * from usr_lst_t where usr_nm='${usr_nm}' and pswd='${pwd}'`;
//     //console.log(QRY_TO_EXEC);
//     if(callback && typeof callback == "function") {
//         dbutil.execQuery(sqldb.MySQLConPool,QRY_TO_EXEC,cntxtDtls,function(err,results) {
//             callback(err,results);
//             return;
//         });
//     }
//     else
//        return dbutil.execQuery(sqldb.MySQLConPool,QRY_TO_EXEC,cntxtDtls);
// };

 

exports.newuserloginMdl = function (data, callback) {
    var cntxtDtls = "in newuserloginMdl";
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
//     var QRY_TO_EXEC = `SELECT usr_address,usr_continent,usr_country,usr_district,usr_email,u.usr_id,usr_nm,usr_password,usr_ph,usr_state,DATE_FORMAT(pc.expiry_date,"%Y-%m-%d") as chapterend,DATE_FORMAT(m.membership_end_date,"%Y-%m-%d")  as memberend,DATE_FORMAT(u.membership_enddate,"%Y-%m-%d") as parentend,DATE_FORMAT(m.membership_start_date,"%Y-%m-%d") as parentstart,DATE_FORMAT(u.membership_startdate,"%Y-%m-%d") as memberstart,DATE_FORMAT(pc.start_date,"%Y-%m-%d")  as chapterstart,membership_type_indicator,premium_payment_indicator,purchase_chapter_id,pc.member_ship_id,chapter_id,chapter_nm,membership_id,membership_name from (SELECT * from usr_dtl_t where d_in=0) u
// LEFT join (SELECT * from hscodes_purchase_chapter_dtl_t where d_in=0) pc on pc.usr_id=u.usr_id
// LEFT join (SELECT * from usr_membership_dtl_t where d_in=0)  m on m.usr_id=u.usr_id
// where u.usr_email='${data.usr_ph}' and  usr_password='${data.usr_password}'; `;

    var QRY_TO_EXEC = `SELECT 	mail_verification_ind,std_cd,salutation,last_nm,middle_nm,gender,DATE_FORMAT(u.usr_i_ts,"%Y-%m-%d") as signupdate,usr_img,std_cd,mail_verification_ind,usr_address,organisation,usr_continent,usr_country,usr_district,usr_email,u.usr_id,usr_nm,usr_password,usr_ph,usr_state,DATE_FORMAT(pc.expiry_date,"%Y-%m-%d") as chapterend,DATE_FORMAT(m.membership_end_date,"%Y-%m-%d")  as memberend,DATE_FORMAT(u.membership_enddate,"%Y-%m-%d") as parentend,DATE_FORMAT(m.membership_start_date,"%Y-%m-%d") as parentstart,DATE_FORMAT(u.membership_startdate,"%Y-%m-%d") as memberstart,DATE_FORMAT(pc.start_date,"%Y-%m-%d")  as chapterstart,membership_type_indicator,premium_payment_indicator,purchase_chapter_id,pc.member_ship_id as package_indicator,pc.chapter_id,pc.chapter_nm,membership_id,membership_name,section_id,c.country_nm from (SELECT * from usr_dtl_t where d_in=0) u 
    LEFT join (SELECT * from usr_membership_dtl_t where d_in=0)  m on m.usr_id=u.usr_id    
    LEFT join (SELECT * from hscodes_purchase_chapter_dtl_t where d_in=0) pc on pc.parent_membership_id=m.membership_id
    left join chapter_dtl_t as cd on cd.chapter_id =pc.chapter_id
    join country_dtl_t as c on c.country_id = u.usr_country
    where u.usr_email='${data.usr_ph}' and  usr_password='${data.usr_password}'  GROUP BY purchase_chapter_id; `;
    
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            console.log(err);
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};

exports.newgetUsrloginCtrlMdl = function (usr, callback) {
    var cntxtDtls = "in newgetUsrloginCtrlMdl";
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var QRY_TO_EXEC = `SELECT e.department_id,e.officer_id,e.roll_usr_id,e.fl_nm,e.usr_level,e.phn,r.menu_id,m.menu_nm,m.menu_state_icon,m.menu_state_nm,m.menu_img_url from menu_usr_rel_tbl as r 
    join menu_tbl as m on r.menu_id=m.menu_id
    join roll_usr_reg_t as e on r.usr_id = e.roll_usr_id where r.usr_id ='${usr}' and r.d_in <> 1 order BY m.menu_order;`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};

exports.chkusrloginMdl = function (usr, callback) {
    var cntxtDtls = "in chkusrloginMdl";
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var QRY_TO_EXEC = `SELECT u.* ,c.country_email,c.continent_email from usr_dtl_t u
    join country_dtl_t c on c.country_id=u.usr_country
    where u.usr_email='${usr.usr_email}' or u.usr_ph='${usr.usr_ph}';`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};

exports.newusersignupMdl = function (data, callback) {
    console.log(data);
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in newusersignupMdl"; 
    var QRY_TO_EXEC = `INSERT INTO usr_dtl_t(usr_nm,salutation,gender,last_nm,middle_nm,usr_ph,usr_email,usr_address,usr_password,usr_continent,usr_country,usr_state,usr_pincode,std_cd,referred_by,organisation,lat,lng,d_in,usr_i_ts,bestknown) values ('${data.usr_nm}','${data.salutation}','${data.gender}','${data.last_nm}','${data.middle_nm}','${data.usr_ph}','${data.usr_email}','${data.usr_address}','${data.usr_password}','${data.usr_continent}','${data.usr_country}','${data.usr_state}','${data.usr_pincode}','${data.isd_ph_code}','${data.referred_by}','${data.organisation}','${data.lat}','${data.lng}','0','${date}','${data.bestknown}');`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};

exports.deleteUser = function(data, callback){
    var QRY_TO_EXEC = `DELETE FROM usr_dtl_t WHERE usr_email='${data.usr_email}'`;
    console.log(QRY_TO_EXEC);
    var cntxtDtls = 'in deleteUser';
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            console.log(err);
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};

exports.continentsMdl = function (data, callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in continentsMdl"; 
    var QRY_TO_EXEC = `SELECT continent_id, continent_nm FROM continent_dtl_t where d_in=0;`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};

exports.countriesMdl = function (data, callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in countriesMdl"; 
    var QRY_TO_EXEC = `SELECT country_id,country_cd,country_nm,isd_ph_code FROM country_dtl_t where continent_id='${data.continent_id}' and d_in=0;`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};



exports.sectionsMdl = function (data, callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in sectionsMdl"; 
    var QRY_TO_EXEC = `SELECT section_id,section_nm,chapter_display_nm,section_img,section_display_nm FROM section_dtl_t where d_in=0;`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};


exports.chaptersMdl = function (data, callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in chaptersMdl"; 
    if(data.section_id==0){
       var QRY_TO_EXEC = `SELECT chapter_id,chapter_cmpr_id,chapter_nm,section_id,chapter_img FROM chapter_dtl_t where  d_in=0;`;  
    }else{
      var QRY_TO_EXEC = `SELECT chapter_id,chapter_cmpr_id,chapter_nm,section_id,chapter_img FROM chapter_dtl_t where section_id='${data.section_id}' and d_in=0;`;   
    }
   
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};


exports.leadcategoriesMdl = function (data, callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in leadcategoriesMdl"; 
    var QRY_TO_EXEC = `SELECT * from lead_ctgry_dtl_t where d_in=0;`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};


exports.currenciesMdl = function (data, callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in currenciesMdl"; 
    var QRY_TO_EXEC = `SELECT * from currency_dtl_t where d_in=0;`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};



exports.postleaddataMdl = function (imageupload,data, callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in postleaddataMdl"; 
    var agency_uid='';
    if(data.refferd_by==1 || data.refferd_by==3){
        agency_uid=`0`
    }else{
         agency_uid=`${data.agency_u_id}`;
    }
    var QRY_TO_EXEC = `INSERT INTO lead_dtl_t (lead_display_id,lead_type,lead_category,lead_chapter,lead_hscode,lead_hscode_desc,description,lead_quantity,lead_unit_of_measurement,lead_currency,lead_unit_price,lead_continent,lead_country,loading_port_country,loading_port_type,loading_port,destination_port_country,destination_port_type,destination_port,lead_price_model,lead_expiry_date,lead_certifying_agency,lead_remarks,special_instruction,lead_business_address,upload1,d_in,latitude,longitude,i_ts,created_by,refferd_by,agency_u_id) VALUES('${data.lead_display_id}','${data.lead_type}','${data.lead_category}','${data.lead_chapter}','${data.lead_hscode}','${data.lead_hscode_desc}','${data.description}','${data.lead_quantity}','${data.lead_unit_of_measurement}','${data.lead_currency}','${data.lead_unit_price}','${data.lead_continent}','${data.lead_country}','${data.loading_port_country}','${data.loading_port_type}','${data.loading_port}','${data.destination_port_country}','${data.destination_port_type}','${data.destination_port}','${data.lead_price_model}','${data.lead_expiry_date}','${data.lead_certifying_agency}','${data.lead_remarks}','${data.special_instruction}','${data.lead_business_address}','${imageupload}',0,'${data.latitude}','${data.longitude}','${date}','${data.user_id}','${data.refferd_by}','${agency_uid}');`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};

exports.postleaddataMblMdl = function (data, callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in postleaddataMblMdl"; 
    var agency_uid='';
    if(data.refferd_by==1 || data.refferd_by==3){
        agency_uid=`0`
    }else{
         agency_uid=`${data.agency_u_id}`;
    }
    var QRY_TO_EXEC = `INSERT INTO lead_dtl_t (lead_display_id,lead_type,lead_category,lead_chapter,lead_hscode,lead_hscode_desc,description,lead_quantity,lead_unit_of_measurement,lead_currency,lead_unit_price,lead_continent,lead_country,loading_port_country,loading_port_type,loading_port,destination_port_country,destination_port_type,destination_port,lead_price_model,lead_expiry_date,lead_certifying_agency,lead_remarks,special_instruction,lead_business_address,d_in,i_ts,refferd_by,agency_u_id,created_by) VALUES('${data.lead_display_id}','${data.lead_type}','${data.lead_category}','${data.lead_chapter}','${data.lead_hscode}','${data.lead_hscode_desc}','${data.description}','${data.lead_quantity}','${data.lead_unit_of_measurement}','${data.lead_currency}','${data.lead_unit_price}','${data.lead_continent}','${data.lead_country}','${data.loading_port_country}','${data.loading_port_type}','${data.loading_port}','${data.destination_port_country}','${data.destination_port_type}','${data.destination_port}','${data.lead_price_model}','${data.lead_expiry_date}','${data.lead_certifying_agency}','${data.lead_remarks}','${data.special_instruction}','${data.lead_business_address}',0,'${date}','${data.refferd_by}','${agency_uid}','${data.created_by}');`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};

exports.updatepostleaddataMblimageMdl = function (imageupload, insertId,img_ind, data,img_cnt, callback) {
    console.log(imageupload, insertId,img_ind,img_cnt)
		var img_url = '';
		if (img_ind == 0) {
			img_url = ` upload1 = '${imageupload}'`;
		} else if (img_ind == 1) {
			img_url = `upload2 = '${imageupload}'`;
		} else if (img_ind == 2) {
			img_url = `upload3 = '${imageupload}'`;
		}
		else if (img_ind == 3) {
			img_url = `upload4 = '${imageupload}'`;
		}
	
		var cntxtDtls = "in updatepostleaddataMblimageMdl";
		var QRY_TO_EXEC = `update lead_dtl_t set ${img_url} where lead_id= '${insertId}'; `;
		console.log(QRY_TO_EXEC);
		if (callback && typeof callback == "function")
			dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
	
				callback(err, results);
				return;
			});
		else
			return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
	
	};


exports.allcountriesMdl = function (data, callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in allcountriesMdl"; 
    var QRY_TO_EXEC = `SELECT country_id,country_cd,country_nm,isd_ph_code,region_id,continent_id FROM country_dtl_t where  d_in=0;`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};


exports.statesMdl = function (data, callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in statesMdl"; 
    var QRY_TO_EXEC = `SELECT state_id,state_nm FROM hscodes_state_dtl_t where country_id='${data.country_id}' and  d_in=0;`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};


exports.postagencydataMdl = function (data, callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in postagencydataMdl"; 
    var QRY_TO_EXEC = `INSERT INTO hscodes_agents_dtl_t (name,mobile,email,qualification,curreuntly_pursuing,nationality,countri,state,planning_at_continent,planning_at_country,planning_at_state,referal_program_benefits,languages_speak,languages_write,languages_read,area_brand_ambassador,exports_impports_bckgrnd,sales_promotional_activities,know_abous_us,areas_think_useful,d_in,i_ts) VALUES('${data.name}','${data.mobile}','${data.email}','${data.qualification}','${data.curreuntly_pursuing}','${data.nationality}','${data.usr_country}','${data.state}','${data.planning_at_continent}','${data.planning_at_country}','${data.planning_at_state}','${data.referal_program_benefits}','${data.languages_speak}','${data.languages_write}','${data.languages_read}','${data.area_brand_ambassador}','${data.exports_impports_bckgrnd}',
    '${data.sales_promotional_activities}','${data.know_abous_us}','${data.areas_think_useful}',0,'${date}');`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};



exports.getagencydataMdl = function (data,agency_id, callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in getagencydataMdl"; 
    var QRY_TO_EXEC = `select a.*,country_cd,country_email,continent_email from hscodes_agents_dtl_t a
join country_dtl_t c on c.country_id=a.countri where a.d_in=0 and agency_id='${agency_id}';`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};


exports.updateagencydataMdl = function (data,agency_unique_id,agency_id, callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in updateagencydataMdl"; 
    var QRY_TO_EXEC = `update hscodes_agents_dtl_t set agency_unique_id='${agency_unique_id}' where agency_id='${agency_id}';`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};



exports.postfranchisedataMdl = function (data, callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in postfranchisedataMdl"; 
    var QRY_TO_EXEC = `INSERT INTO hscodes_franchise_dtl_t (country_id,state_id,company_nm,business_background,contact_person,contact,email_id,kind_of_business,website,ownership_type,insfra_description,willing_to_setup_infra,annual_turnover,remarks,franchise_currency,d_in,i_ts,created_by,usercountry,franchise_saltation,franchise_gender) VALUES('${data.country_id}','${data.state_id}','${data.company_nm}','${data.business_background}','${data.contact_person}','${data.contact}','${data.email_id}','${data.kind_of_business}','${data.website}','${data.ownership_type}','${data.insfra_description}','${data.willing_to_setup_infra}','${data.annual_turnover}','${data.remarks}','${data.franchise_currency}',0,'${date}','${data.user_id}','${data.usr_country}','${data.salutation}','${data.gender}');`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};

exports.getfranchisedataMdl = function (data,franchise_id, callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in getfranchisedataMdl"; 
    var QRY_TO_EXEC = `select a.*,country_cd,country_email,continent_email from hscodes_franchise_dtl_t a
join country_dtl_t c on c.country_id=a.country_id where a.d_in=0 and franchise_id='${franchise_id}';`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};


exports.updatefranchisedataMdl = function (data,franchise_unique_id,franchise_id, callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in updatefranchisedataMdl"; 
    var QRY_TO_EXEC = `update hscodes_franchise_dtl_t set franchise_unique_id='${franchise_unique_id}' where franchise_id='${franchise_id}';`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};



exports.getporttypemdl = function (data, callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in getporttypemdl"; 
    var QRY_TO_EXEC = ` SELECT transport_in,CASE
    WHEN transport_in=1 THEN 'AIR' 
    WHEN transport_in=2 THEN 'SEA' 
    WHEN transport_in=3 THEN 'ROAD'  
     WHEN transport_in=4 THEN 'RAIL'  
    ELSE 'ASWP'
 END as status from hscodes_transport_dtl_t where country_id='${data.country_id}' and d_in=0  group by transport_in ;`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};


exports.selectedportsMdl = function (data, callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in selectedportsMdl"; 
    var QRY_TO_EXEC = ` SELECT icao_cd,iata_cd,trnsport_nm,transport_id  from hscodes_transport_dtl_t where country_id='${data.country_id}' and transport_in='${data.transport_in}' and d_in=0;`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};


exports.getcountryhscodestableMdl = function (data, callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in getcountryhscodestableMdl"; 
    var QRY_TO_EXEC = ` SELECT  table_nm from hscodes_dtl_t where country_id='${data.country_id}' and d_in=0;`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};

exports.hscodesdataMdl = function (table_nm,data, callback) { 
    
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in hscodesdataMdl"; 
    var QRY_TO_EXEC = ` SELECT tl,tldesc  from ${table_nm} where tl like '${data.chapter_id}%' and d_in=0;`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};

exports.getsearchdataMdl = function (table_nm,data, callback) {      
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in getsearchdataMdl"; 
    var QRY_TO_EXEC = ` SELECT *,left(tl,2) as chapter  from ${table_nm} c
left join (select fav_hscode_id,favourite_id from hscodes_favourites_dtl_t where usr_id='${data.usr_id}' and d_in=0) f on f.fav_hscode_id=c.hscode_id 
LEFT join (SELECT * from hscodes_chat_dtl_t where chat_usr_id='${data.usr_id}' and chat_country_id='${data.country_id}' and d_in=0 ) chat on chat.chat_hscode=c.tl
where tl like '%${data.searchdata}%' or tldesc like '%${data.searchdata}%' or product_description like '%${data.searchdata}%' or hs4desc like '%${data.searchdata}%' or hs6desc like '%${data.searchdata}%' and c.d_in=0;`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};


exports.check_franchiseMdl = function (data, callback) {  
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in check_franchiseMdl"; 
    var QRY_TO_EXEC = ` SELECT * from hscodes_franchise_dtl_t where country_id='${data.country_id}' and state_id='${data.state_id}' and d_in=0 and approve_in=1;`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};

exports.dontknowhscodeMdl = function (table_nm,data, callback) {      
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in dontknowhscodeMdl"; 
    var QRY_TO_EXEC = ` SELECT s.section_id,s.section_nm,chapter_id,chapter_cmpr_id,chapter_nm,tl,hscode_id,h.tldesc from section_dtl_t s
    join chapter_dtl_t c on c.section_id=s.section_id
    join ${table_nm} h on h.product=c.chapter_id where tl='${data.hscode}' and h.d_in=0`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};


exports.membershipdtls_descriptionMdl = function (callback) {      
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in membershipdtls_descriptionMdl"; 
    var QRY_TO_EXEC = `select * from hscodes_membership_desc_dtl_t where d_in=0`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};
exports.currencies_cdMdl = function (data, callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in currencies_cdMdl";
    var QRY_TO_EXEC = `SELECT * FROM currency_dtl_t where country_id='${data.country_id}' and d_in=0;`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};

exports.postmembershipdetailsMdl = function (data, callback) {      
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in postmembershipdetailsMdl"; 
    var QRY_TO_EXEC = `insert into usr_membership_dtl_t (usr_id,member_ship_id,membership_name,membership_start_date,membership_end_date,usr_credits,i_ts,d_in,ammount,usd_ammount,payment_id) values('${data.usr_id}','${data.membership_id}','${data.membership_type}','${data.start_dt}','${data.expiry_dt}','${data.usr_credits}','${date}',0,'${data.ammount}','${data.usd_ammount}','${data.payment_id}')`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};



exports.postchaptersmembershipdetailsMdl = function (parent_membership_id, postData, callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var QRY_TO_EXEC = '';
    var QRY_SUB = '';
    var cntxtDtls = "in postchaptersmembershipdetailsMdl"; 
    for (var v = 0; v < postData.chapterselectionArray.length; v++) {
     
        var QRY_SUB = `insert into hscodes_purchase_chapter_dtl_t (parent_membership_id,member_ship_id,chapter_id,chapter_nm,start_date,expiry_date,usr_id,i_ts,d_in) VALUES('${parent_membership_id}','${postData.membership_id}','${postData.chapterselectionArray[v].chapter_id}','${postData.chapterselectionArray[v].chapter_nm}','${postData.start_dt}','${postData.expiry_dt}','${postData.usr_id}' ,'${date}','0');`;
        QRY_TO_EXEC = QRY_TO_EXEC + QRY_SUB;
    }
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function") {
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    }
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};

exports.postmembershipdetailsinusrtblMdl = function (data, callback) {      
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in postmembershipdetailsinusrtblMdl"; 
    var QRY_TO_EXEC = `update usr_dtl_t set premium_payment_indicator=1,membership_type_indicator=1,membership_startdate='${data.start_dt}',membership_enddate='${data.expiry_dt}' where usr_id='${data.usr_id}'; `;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};


exports.postmembershiprestrictiondetailsMdl = function (parent_membership_id,data, callback) {    
    var condition='';
 
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
      var QRY_TO_EXEC = '';
    var QRY_SUB = '';
    var cntxtDtls = "in postmembershiprestrictiondetailsMdl"; 
    var cnt=1;
    for (var v = 0; v < data.selectedrestrictionarray.length; v++) {
           if(data.membership_id==1){
         var condition= `${data.selectedrestrictionarray[v].platinum}`
    }else  if(data.membership_id==2){
         var condition=`${data.selectedrestrictionarray[v].gold}`
    }else  if(data.membership_id==3){
         var condition=`${data.selectedrestrictionarray[v].silver}`
    }
          var QRY_SUB = `insert into hscodes_limit_restriction_dtl_t (usr_id,usr_purchase_membership_id,type_description,max_limit_count,subscription_st_dt,subscription_end_dt,i_ts,lead_ctgry_restrict_type_id) values('${data.usr_id}','${parent_membership_id}','${data.selectedrestrictionarray[v].description}','${condition}','${data.start_dt}','${data.expiry_dt}','${date}','${cnt}');`;
     
     cnt++;
        QRY_TO_EXEC = QRY_TO_EXEC + QRY_SUB;
    }
    
   console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};


exports.getusercreditsdeatils = function (data, callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in getusercreditsdeatils";
    var QRY_TO_EXEC = `SELECT usr_id,total_credits  FROM usr_dtl_t where usr_id='${data.usr_id}' and d_in=0; `;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};

exports.updateurcreditsMdl = function (data, updatecredits, callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in updateurcreditsMdl";
    var QRY_TO_EXEC = `update usr_dtl_t set total_credits='${updatecredits}' where usr_id='${data.usr_id}'; `;

    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};


// postmembershipdetails



exports.getmembershipdtlsMdl = function (data, callback) {      
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in getmembershipdtlsMdl"; 
    var QRY_TO_EXEC = `select pc.*,u.*,DATE_FORMAT(u.i_ts,"%d-%m-%Y %h:%i %p") as upgraded_on,total_credits,usr.* from usr_membership_dtl_t u 
    left join hscodes_purchase_chapter_dtl_t pc on pc.parent_membership_id = u.membership_id
join usr_dtl_t usr on usr.usr_id=u.usr_id where usr.d_in=0 and  u.usr_id='${data.user_id}' order by membership_id desc; `;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};



exports.addfovouritesMdl = function (data, callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in addfovouritesMdl"; 
    var QRY_TO_EXEC = `insert into hscodes_favourites_dtl_t(usr_id,fav_hscode_id,country_id,d_in,i_ts) values('${data.usr_id}','${data.fav_hscode_id}','${data.country_id}','0','${date}'); `;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};

exports.removefovouritesMdl = function (data, callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in removefovouritesMdl"; 
    var QRY_TO_EXEC = `update hscodes_favourites_dtl_t set d_in=1 where favourite_id='${data.favourite_id}' and usr_id='${data.usr_id}'; `;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};



exports.getfavouritesMdl = function (table_nm,data, callback) {      
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in getfavouritesMdl"; 
//     var QRY_TO_EXEC = ` SELECT *,left(tl,2) as chapter  from ${table_nm} c
// join (select fav_hscode_id,favourite_id from hscodes_favourites_dtl_t where usr_id='${data.usr_id}' and d_in=0) f on f.fav_hscode_id=c.hscode_id where  c.d_in=0;`;
    var QRY_TO_EXEC = ` SELECT *,left(tl,2) as chapter  from ${table_nm} c
join (select fav_hscode_id,favourite_id from hscodes_favourites_dtl_t where usr_id='${data.usr_id}' and country_id='${data.country_id}' and d_in=0) f on f.fav_hscode_id=c.hscode_id 
LEFT join (SELECT * from hscodes_chat_dtl_t where chat_usr_id='${data.usr_id}' and chat_country_id='${data.country_id}' and d_in=0 ) chat on chat.chat_hscode=c.tl
where  c.d_in=0;`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};



exports.getleadcntchaptersMdl = function (data, callback) {      
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in getleadcntchaptersMdl"; 
    if(data.section_id==0){
        var QRY_TO_EXEC = ` SELECT chapter_img,chapter_id,chapter_cmpr_id,chapter_nm,section_id,SUM(CASE WHEN l.lead_type in (1,3) and date(l.lead_expiry_date)>curdate() THEN 1 else 0 End) as buylead,SUM(CASE WHEN l.lead_type in (2,4) and date(l.lead_expiry_date)>curdate() THEN 1 else 0 End) as selllead from chapter_dtl_t c 
left join (SELECT * from lead_dtl_t where date(lead_expiry_date)>curdate()  and d_in=0  and approve_in=1) l on l.lead_chapter=c.chapter_id group by chapter_id `;
    }else{
      var QRY_TO_EXEC = ` SELECT chapter_img,chapter_id,chapter_cmpr_id,chapter_nm,section_id,SUM(CASE WHEN l.lead_type in (1,3) and date(l.lead_expiry_date)>curdate() THEN 1 else 0 End) as buylead,SUM(CASE WHEN l.lead_type in (2,4) and date(l.lead_expiry_date)>curdate() THEN 1 else 0 End) as selllead from chapter_dtl_t c 
left join (SELECT * from lead_dtl_t where date(lead_expiry_date)>curdate() and d_in=0  and approve_in=1) l on l.lead_chapter=c.chapter_id where section_id='${data.section_id}' group by chapter_id`;  
    }
    //  and lead_country='${data.country_id}'
    
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};

exports.getremainderleaddetailsMdl = function (data, callback) {      
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in getremainderleaddetailsMdl";  
        var QRY_TO_EXEC = ` SELECT * FROM lead_dtl_t where lead_id in (select remainder_lead_id from hscodes_lead_remainder_dtl_t where remainder_usr_id='${data.usr_id}' and date(remainder_date)>=curdate()); `;
    
    
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};

//     t.transport_in as loadingporttype,   trans.transport_in as destinationporttype,
exports.getleaddetailsMdl = function (data, callback) {      
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in getleaddetailsMdl"; 
    var QRY_TO_EXEC = ` SELECT sec.section_nm,pc.purchase_chapter_id,cb.id as credit_debit_id,l.*,usr.*,lead_ctgry_nm,chat.chat_id,CASE
    WHEN t.transport_in=1 THEN 'AIR' 
    WHEN t.transport_in=2 THEN 'SEA' 
    WHEN t.transport_in=3 THEN 'ROAD'  
     WHEN t.transport_in=4 THEN 'RAIL'  
    ELSE 'ASWP'
 END as loadingporttype,CASE
    WHEN trans.transport_in=1 THEN 'AIR' 
    WHEN trans.transport_in=2 THEN 'SEA' 
    WHEN trans.transport_in=3 THEN 'ROAD'  
     WHEN trans.transport_in=4 THEN 'RAIL'  
    ELSE 'ASWP'
 END as destinationporttype,
                        c.chapter_id,chapter_cmpr_id,
                        c.chapter_nm,
                        currency_cd,
                        currency_nm,
                        continent_nm,
                        
                        
                        ctry.country_cd,
                        ctry.country_nm,
                        ctry.country_region, 
                        loadctry.country_nm as loadingcountry,
                        destctry.country_nm as destinationcountry,
                        
                    
                        
                        
                        tlp.transport_id as loadingtransport_id,
                        tlp.icao_cd as loadingicao_cd,
                        tlp.iata_cd as loadingiata_cd,
                        tlp.trnsport_nm as loadingtrnsport_nm,
                        tlp.city_nm as loadingcity_nm,
                        
                        
                        
                        tdp.transport_id as desttransport_id,
                        tdp.icao_cd as desticao_cd,
                        tdp.iata_cd as destiata_cd,
                        tdp.trnsport_nm as desttrnsport_nm,
                        tdp.city_nm as destcity_nm from lead_dtl_t l
                        join lead_ctgry_dtl_t cat on cat.lead_ctgry_id=l.lead_type
                        join section_dtl_t sec on sec.section_id=l.lead_category
                        join chapter_dtl_t c on c.chapter_id=l.lead_chapter
                        
                        join currency_dtl_t cur on cur.currency_id=l.lead_currency
                        join continent_dtl_t cont on cont.continent_id=l.lead_continent
                        
                        join country_dtl_t ctry on ctry.country_id=l.lead_country
                        join country_dtl_t loadctry on loadctry.country_id=l.loading_port_country
                        join country_dtl_t destctry on destctry.country_id=l.destination_port_country
                        
                        join (SELECT * from hscodes_transport_dtl_t group by transport_in) t on t.transport_in=l.loading_port_type
                        join (SELECT * from hscodes_transport_dtl_t group by transport_in) trans on trans.transport_in=l.destination_port_type
                        
                        
                        join hscodes_transport_dtl_t tlp on tlp.transport_id=l.loading_port
                        join hscodes_transport_dtl_t tdp on tdp.transport_id=l.destination_port
                        left join (SELECT * from credits_debit_t where usr_id='${data.usr_id}') as cb on cb.lead_id=l.lead_id
                        left join usr_dtl_t usr on usr.usr_id=l.created_by
                        left join (SELECT * FROM hscodes_purchase_chapter_dtl_t where date(expiry_date)>curdate() and usr_id='${data.usr_id}') as pc on pc.chapter_id=l.lead_chapter
                        left join (SELECT * from hscodes_chat_dtl_t where chat_usr_id='${data.usr_id}' and chat_country_id='${data.country_id}' and d_in=0 ) chat on chat.chat_hscode=l.lead_hscode
                        
                        where date(lead_expiry_date)>curdate()  and lead_chapter in (${data.chapter_id}) and l.d_in=0 and l.approve_in=1 ;`;
    console.log(QRY_TO_EXEC);
    // and lead_country='${data.country_id}'
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};
exports.getagentleadsMdl = function (data, callback) {      
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in getagentleadsMdl"; 
    var QRY_TO_EXEC = ` SELECT sec.section_nm,l.*,usr.*,lead_ctgry_nm,CASE
    WHEN t.transport_in=1 THEN 'AIR' 
    WHEN t.transport_in=2 THEN 'SEA' 
    WHEN t.transport_in=3 THEN 'ROAD'  
     WHEN t.transport_in=4 THEN 'RAIL'  
    ELSE 'ASWP'
 END as loadingporttype,CASE
    WHEN trans.transport_in=1 THEN 'AIR' 
    WHEN trans.transport_in=2 THEN 'SEA' 
    WHEN trans.transport_in=3 THEN 'ROAD'  
     WHEN trans.transport_in=4 THEN 'RAIL'  
    ELSE 'ASWP'
 END as destinationporttype,
                        c.chapter_id,chapter_cmpr_id,
                        c.chapter_nm,
                        currency_cd,
                        currency_nm,
                        continent_nm,
                        
                        
                        ctry.country_cd,
                        ctry.country_nm,
                        ctry.country_region, 
                        loadctry.country_nm as loadingcountry,
                        destctry.country_nm as destinationcountry,
                        
                    
                        
                        
                        tlp.transport_id as loadingtransport_id,
                        tlp.icao_cd as loadingicao_cd,
                        tlp.iata_cd as loadingiata_cd,
                        tlp.trnsport_nm as loadingtrnsport_nm,
                        tlp.city_nm as loadingcity_nm,
                        
                        
                        
                        tdp.transport_id as desttransport_id,
                        tdp.icao_cd as desticao_cd,
                        tdp.iata_cd as destiata_cd,
                        tdp.trnsport_nm as desttrnsport_nm,
                        tdp.city_nm as destcity_nm from lead_dtl_t l
                        join lead_ctgry_dtl_t cat on cat.lead_ctgry_id=l.lead_type
                        join section_dtl_t sec on sec.section_id=l.lead_category
                        join chapter_dtl_t c on c.chapter_id=l.lead_chapter
                        
                        join currency_dtl_t cur on cur.currency_id=l.lead_currency
                        join continent_dtl_t cont on cont.continent_id=l.lead_continent
                        
                        join country_dtl_t ctry on ctry.country_id=l.lead_country
                        join country_dtl_t loadctry on loadctry.country_id=l.loading_port_country
                        join country_dtl_t destctry on destctry.country_id=l.destination_port_country
                        
                        join (SELECT * from hscodes_transport_dtl_t group by transport_in) t on t.transport_in=l.loading_port_type
                        join (SELECT * from hscodes_transport_dtl_t group by transport_in) trans on trans.transport_in=l.destination_port_type
                        
                        
                        join hscodes_transport_dtl_t tlp on tlp.transport_id=l.loading_port
                        join hscodes_transport_dtl_t tdp on tdp.transport_id=l.destination_port
                        left join usr_dtl_t usr on usr.usr_id=l.created_by  
                        where date(lead_expiry_date)>curdate()  and lead_chapter in (${data.chapter_id}) and l.d_in=0 and l.approve_in=1 and usr.referred_by='${data.usr_id}';`;
    console.log(QRY_TO_EXEC);
    // and lead_country='${data.country_id}' usr.referred_by='${data.usr_id}'
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};

exports.getfranchiseleaddetailsMdl = function (data, callback) {      
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in getfranchiseleaddetailsMdl"; 
    var QRY_TO_EXEC = ` SELECT sec.section_nm,l.*,usr.*,lead_ctgry_nm,CASE
    WHEN t.transport_in=1 THEN 'AIR' 
    WHEN t.transport_in=2 THEN 'SEA' 
    WHEN t.transport_in=3 THEN 'ROAD'  
     WHEN t.transport_in=4 THEN 'RAIL'  
    ELSE 'ASWP'
 END as loadingporttype,CASE
    WHEN trans.transport_in=1 THEN 'AIR' 
    WHEN trans.transport_in=2 THEN 'SEA' 
    WHEN trans.transport_in=3 THEN 'ROAD'  
     WHEN trans.transport_in=4 THEN 'RAIL'  
    ELSE 'ASWP'
 END as destinationporttype,
                        c.chapter_id,chapter_cmpr_id,
                        c.chapter_nm,
                        currency_cd,
                        currency_nm,
                        continent_nm,
                        
                        
                        ctry.country_cd,
                        ctry.country_nm,
                        ctry.country_region, 
                        loadctry.country_nm as loadingcountry,
                        destctry.country_nm as destinationcountry,
                        
                    
                        
                        
                        tlp.transport_id as loadingtransport_id,
                        tlp.icao_cd as loadingicao_cd,
                        tlp.iata_cd as loadingiata_cd,
                        tlp.trnsport_nm as loadingtrnsport_nm,
                        tlp.city_nm as loadingcity_nm,
                        
                        
                        
                        tdp.transport_id as desttransport_id,
                        tdp.icao_cd as desticao_cd,
                        tdp.iata_cd as destiata_cd,
                        tdp.trnsport_nm as desttrnsport_nm,
                        tdp.city_nm as destcity_nm from lead_dtl_t l
                        join lead_ctgry_dtl_t cat on cat.lead_ctgry_id=l.lead_type
                        join section_dtl_t sec on sec.section_id=l.lead_category
                        join chapter_dtl_t c on c.chapter_id=l.lead_chapter
                        
                        join currency_dtl_t cur on cur.currency_id=l.lead_currency
                        join continent_dtl_t cont on cont.continent_id=l.lead_continent
                        
                        join country_dtl_t ctry on ctry.country_id=l.lead_country
                        join country_dtl_t loadctry on loadctry.country_id=l.loading_port_country
                        join country_dtl_t destctry on destctry.country_id=l.destination_port_country
                        
                        join (SELECT * from hscodes_transport_dtl_t group by transport_in) t on t.transport_in=l.loading_port_type
                        join (SELECT * from hscodes_transport_dtl_t group by transport_in) trans on trans.transport_in=l.destination_port_type
                        
                        
                        join hscodes_transport_dtl_t tlp on tlp.transport_id=l.loading_port
                        join hscodes_transport_dtl_t tdp on tdp.transport_id=l.destination_port
                        left join usr_dtl_t usr on usr.usr_id=l.created_by
                        left join hscodes_dshbrd_usr_dtl_t dash on dash.country_id=usr.usr_country
                        
                        where date(lead_expiry_date)>curdate()  and lead_chapter in (${data.chapter_id}) and l.d_in=0 and l.approve_in=1 and dash.country_id='${data.country_id}';`;
    console.log(QRY_TO_EXEC);
    // and lead_country='${data.country_id}' usr.referred_by='${data.usr_id}'
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};

exports.postrequestquoteMdl = function (data,callback) {
    console.log(data)
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in postrequestquoteMdl";
    var QRY_TO_EXEC = `insert into 	hscodes_request_quote_dtl_t (lead_id,required_quantity,unit_of_measurement,currency_id,usr_id,d_in,i_ts,country_id,reqst_sender_id,unit_price) values('${data.lead_id}','${data.quantity}','${data.unit_of_measurement}','${data.currency}','${data.usr_id}',0 ,'${date}','${data.country}','${data.reqst_sender_id}','${data.unit_price}')`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function") {
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    }
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};



exports.getrequestquoteMdl = function (data,callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in getrequestquoteMdl";
    var QRY_TO_EXEC = `select * from hscodes_request_quote_dtl_t  where d_in=0 and usr_id='${data.usr_id}';`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function") {
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    }
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};



exports.addhscodeMdl = function (data,callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in addhscodeMdl";
    var QRY_TO_EXEC = `insert into add_hscodes_dtl_t(hscode,country_id,	product_name,description,usr_id,d_in,i_ts,usr_continent,usr_state,usr_country,name,mobile,email,address,organisation,url) 
    values('${data.hscode}','${data.country_id}','${data.product_name}','${data.description}','${data.usr_id}',0 ,'${date}','${data.usr_continent}','${data.usr_state}','${data.usr_country}','${data.name}','${data.mobile}','${data.email}','${data.address}','${data.organisation}','${data.url}');`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function") {
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    }
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};


exports.generateleaddisplayIdMdl = function (callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in generateleaddisplayIdMdl";
    var QRY_TO_EXEC = `SELECT lead_id,chapter_cmpr_id,chapter_id,country_cd,lead_ctgry_cd,usr_email,usr_nm FROM lead_dtl_t l
join lead_ctgry_dtl_t c on c.lead_ctgry_id=l.lead_type
join country_dtl_t cntry on cntry.country_id=l.lead_country
join chapter_dtl_t chapter on chapter.chapter_id=l.lead_chapter
join usr_dtl_t u on u.usr_id=l.created_by where lead_display_id='undefined';`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function") {
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    }
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};


exports.newgenerateleaddisplayIdMdl = function (insertId,callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in newgenerateleaddisplayIdMdl";
    var QRY_TO_EXEC = `SELECT lead_id,chapter_cmpr_id,chapter_id,country_cd,lead_ctgry_cd,usr_email,usr_nm,country_email,continent_email FROM lead_dtl_t l
join lead_ctgry_dtl_t c on c.lead_ctgry_id=l.lead_type
join country_dtl_t cntry on cntry.country_id=l.lead_country
join chapter_dtl_t chapter on chapter.chapter_id=l.lead_chapter
join usr_dtl_t u on u.usr_id=l.created_by where lead_id='${insertId}';`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function") {
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    }
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};

exports.updategenerateleaddisplayIdMdl = function (mdl,lead_id,callback) {
     console.log(mdl)
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in updategenerateleaddisplayIdMdl";
    var QRY_TO_EXEC = `update lead_dtl_t set lead_display_id='${mdl}'  where lead_id='${lead_id}';`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function") {
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    }
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};


exports.getslidersMdl = function (data,callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in getslidersMdl";
    // var QRY_TO_EXEC = `select lead_id,description,lead_type,lead_display_id,lead_hscode,lead_expiry_date,i_ts from lead_dtl_t where lead_country='${data.country_id}' and approve_in=1 and lead_expiry_date> curdate() ORDER by lead_id DESC`;
    var QRY_TO_EXEC = `select lead_id,description,lead_type,lead_display_id,lead_hscode,lead_expiry_date,l.i_ts,purchase_chapter_id,lead_chapter from lead_dtl_t l
left join (SELECT * FROM hscodes_purchase_chapter_dtl_t where date(expiry_date)>curdate() and usr_id='${data.user_id}') as pc on pc.chapter_id=l.lead_chapter
where approve_in=1 and lead_expiry_date> curdate() ORDER by lead_id DESC;`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function") {
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    }
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};

























  //dashboard api start
// exports.getallusersMdl = function (data,callback) {
//     var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
//     var cntxtDtls = "in getallusersMdl";
//  if(data.country_id==0){
//       var QRY_TO_EXEC = `select um.membership_start_date,um.membership_end_date,um.membership_name,c.continent_nm,cntry.country_nm,s.state_nm,u.* from usr_dtl_t u
//       left join continent_dtl_t c on c.continent_id=u.usr_continent
// LEFT join country_dtl_t cntry on cntry.country_id=u.usr_country
// left join hscodes_state_dtl_t s on s.state_id=u.usr_state
// left join (select *
//               from usr_membership_dtl_t order by member_ship_id desc as q
//               inner join
//               (
//                 SELECT max(membership_id) as id FROM usr_membership_dtl_t where curdate() <date(membership_end_date) order by member_ship_id desc
//                 GROUP by usr_id 
//               ) m
//                 on q.membership_id = m.id) um on um.usr_id=u.usr_id where u.d_in=0 order by u.usr_id desc;`;  
//     }else{
//         var QRY_TO_EXEC = `select um.membership_start_date,um.membership_end_date,um.membership_name,c.continent_nm,cntry.country_nm,s.state_nm,u.* from usr_dtl_t u
//         left join continent_dtl_t c on c.continent_id=u.usr_continent
// LEFT join country_dtl_t cntry on cntry.country_id=u.usr_country
// left join hscodes_state_dtl_t s on s.state_id=u.usr_state
// left join (select *
//               from usr_membership_dtl_t as q
//               inner join
//               (
//                 SELECT max(membership_id) as id FROM usr_membership_dtl_t where curdate() <date(membership_end_date)
//                 GROUP by usr_id 
//               ) m
//                 on q.membership_id = m.id) um on um.usr_id=u.usr_id where u.d_in=0 and usr_country='${data.country_id}' order by u.usr_id desc;`;
//     }
    
//     console.log(QRY_TO_EXEC);
//     if (callback && typeof callback == "function") {
//         dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
//             callback(err, results);
//             return;
//         });
//     }
//     else
//         return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
// };


exports.deleteuserMdl = function (data,callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in deleteuserMdl"; 
    if(data.indicator==0){
       var QRY_TO_EXEC = `update usr_dtl_t set d_in=1, d_ts='${date}', deleted_by='${data.deleted_by}' where usr_id='${data.usr_id}';`;          
    }else if (data.indicator==1){
              var QRY_TO_EXEC = `update hscodes_agents_dtl_t set d_in=1, d_ts='${date}' where agency_id='${data.agency_id.agency_id}';`;   
    }else if (data.indicator==2){
              var QRY_TO_EXEC = `update hscodes_franchise_dtl_t set d_in=1, d_ts='${date}' where franchise_id='${data.franchise_id.franchise_id}';`;   
    }else if (data.indicator==3){
              var QRY_TO_EXEC = `update lead_dtl_t set d_in=1, d_ts='${date}' where lead_id='${data.lead_id}';`;   
    }
    
    

    
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function") {
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    }
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};



// exports.getallagenciesMdl = function (data,callback) {
//     var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
//     var cntxtDtls = "in getallagenciesMdl"; 
//       if(data.indicator==1){
//   if(data.country_id==0){
//              var QRY_TO_EXEC = `SELECT a.*,c.country_nm as nativecountry,s.state_nm as nativestate,pc.country_nm as planningcountry,ps.state_nm as planningstate,pcon.continent_nm as planningcontinent FROM hscodes_agents_dtl_t a
// join country_dtl_t c on c.country_id=a.nationality
// join hscodes_state_dtl_t s on s.state_id=a.state


// join continent_dtl_t pcon on pcon.continent_id=a.planning_at_continent
// join country_dtl_t pc on pc.country_id=a.planning_at_country
// join hscodes_state_dtl_t ps on ps.state_id=a.planning_at_state where a.d_in=0;`;
//   }else{
//             var QRY_TO_EXEC = `SELECT a.*,c.country_nm as nativecountry,s.state_nm as nativestate,pc.country_nm as planningcountry,ps.state_nm as planningstate,pcon.continent_nm as planningcontinent FROM hscodes_agents_dtl_t a
// join country_dtl_t c on c.country_id=a.nationality
// join hscodes_state_dtl_t s on s.state_id=a.state


// join continent_dtl_t pcon on pcon.continent_id=a.planning_at_continent
// join country_dtl_t pc on pc.country_id=a.planning_at_country
// join hscodes_state_dtl_t ps on ps.state_id=a.planning_at_state where a.d_in=0 and a.nationality='${data.country_id}';`;
//   }
//       }else if(data.indicator==2){
//              if(data.country_id==0){
//              var QRY_TO_EXEC = `SELECT a.*,c.country_nm as nativecountry,s.state_nm as nativestate FROM hscodes_franchise_dtl_t a
// join country_dtl_t c on c.country_id=a.country_id
// join hscodes_state_dtl_t s on s.state_id=a.state_id  where a.d_in=0;`;
//   }else{
//             var QRY_TO_EXEC = `SELECT a.*,c.country_nm as nativecountry,s.state_nm as nativestate FROM hscodes_franchise_dtl_t a
// join country_dtl_t c on c.country_id=a.country_id
// join hscodes_state_dtl_t s on s.state_id=a.state_id  where a.d_in=0 and a.country_id='${data.country_id}';`;
//   }
//       }else if(data.indicator==3){
//              if(data.country_id==0){
//              var QRY_TO_EXEC = `SELECT l.*,lead_ctgry_nm,
//                         chapter_id,
//                         chapter_nm,
//                         currency_cd,
//                         currency_nm,
//                         continent_nm,
                        
                        
//                         ctry.country_cd,
//                         ctry.country_nm,
//                         ctry.country_region, 
//                         loadctry.country_nm as loadingcountry,
//                         destctry.country_nm as destinationcountry,
                        
//                         t.transport_in as loadingporttype,
//                         trans.transport_in as destinationporttype,
                        
                        
//                         tlp.transport_id as loadingtransport_id,
//                         tlp.icao_cd as loadingicao_cd,
//                         tlp.iata_cd as loadingiata_cd,
//                         tlp.trnsport_nm as loadingtrnsport_nm,
//                         tlp.city_nm as loadingcity_nm,
                        
                        
                        
//                         tdp.transport_id as desttransport_id,
//                         tdp.icao_cd as desticao_cd,
//                         tdp.iata_cd as destiata_cd,
//                         tdp.trnsport_nm as desttrnsport_nm,
//                         tdp.city_nm as destcity_nm from lead_dtl_t l
//                         join lead_ctgry_dtl_t cat on cat.lead_ctgry_id=l.lead_type
//                         join chapter_dtl_t c on c.chapter_id=l.lead_chapter
                        
//                         join currency_dtl_t cur on cur.currency_id=l.lead_currency
//                         join continent_dtl_t cont on cont.continent_id=l.lead_continent
                        
//                         join country_dtl_t ctry on ctry.country_id=l.lead_country
//                         join country_dtl_t loadctry on loadctry.country_id=l.loading_port_country
//                         join country_dtl_t destctry on destctry.country_id=l.destination_port_country
                        
//                         join (SELECT * from hscodes_transport_dtl_t group by transport_in) t on t.transport_in=l.loading_port_type
//                         join (SELECT * from hscodes_transport_dtl_t group by transport_in) trans on trans.transport_in=l.destination_port_type
                        
                        
//                         join hscodes_transport_dtl_t tlp on tlp.transport_id=l.loading_port
//                         join hscodes_transport_dtl_t tdp on tdp.transport_id=l.destination_port
                        
                        
//                         where date(lead_expiry_date)>curdate()  and l.d_in=0;`;
//   }else{
//             var QRY_TO_EXEC = `SELECT l.*,lead_ctgry_nm,
//                         chapter_id,chapter_cmpr_id,
//                         chapter_nm,
//                         currency_cd,
//                         currency_nm,
//                         continent_nm,
                        
                        
//                         ctry.country_cd,
//                         ctry.country_nm,
//                         ctry.country_region, 
//                         loadctry.country_nm as loadingcountry,
//                         destctry.country_nm as destinationcountry,
                        
//                         t.transport_in as loadingporttype,
//                         trans.transport_in as destinationporttype,
                        
                        
//                         tlp.transport_id as loadingtransport_id,
//                         tlp.icao_cd as loadingicao_cd,
//                         tlp.iata_cd as loadingiata_cd,
//                         tlp.trnsport_nm as loadingtrnsport_nm,
//                         tlp.city_nm as loadingcity_nm,
                        
                        
                        
//                         tdp.transport_id as desttransport_id,
//                         tdp.icao_cd as desticao_cd,
//                         tdp.iata_cd as destiata_cd,
//                         tdp.trnsport_nm as desttrnsport_nm,
//                         tdp.city_nm as destcity_nm from lead_dtl_t l
//                         join lead_ctgry_dtl_t cat on cat.lead_ctgry_id=l.lead_type
//                         join chapter_dtl_t c on c.chapter_id=l.lead_chapter
                        
//                         join currency_dtl_t cur on cur.currency_id=l.lead_currency
//                         join continent_dtl_t cont on cont.continent_id=l.lead_continent
                        
//                         join country_dtl_t ctry on ctry.country_id=l.lead_country
//                         join country_dtl_t loadctry on loadctry.country_id=l.loading_port_country
//                         join country_dtl_t destctry on destctry.country_id=l.destination_port_country
                        
//                         join (SELECT * from hscodes_transport_dtl_t group by transport_in) t on t.transport_in=l.loading_port_type
//                         join (SELECT * from hscodes_transport_dtl_t group by transport_in) trans on trans.transport_in=l.destination_port_type
                        
                        
//                         join hscodes_transport_dtl_t tlp on tlp.transport_id=l.loading_port
//                         join hscodes_transport_dtl_t tdp on tdp.transport_id=l.destination_port
                        
                        
//                         where date(lead_expiry_date)>curdate() and lead_country='${data.country_id}' and l.d_in=0;`;
//   }
          
//       }
    
//     console.log(QRY_TO_EXEC);
//     if (callback && typeof callback == "function") {
//         dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
//             callback(err, results);
//             return;
//         });
//     }
//     else
//         return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
// };



//dashboard api start
exports.getallusersMdl = function (data,callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in getallusersMdl";
    if(data.country_id==0){
      var QRY_TO_EXEC = `select * from usr_dtl_t u
        left join continent_dtl_t c on c.continent_id=u.usr_continent
LEFT join country_dtl_t cntry on cntry.country_id=u.usr_country
left join hscodes_state_dtl_t s on s.state_id=u.usr_state where u.d_in=0 and 	mail_verification_ind=1;SELECT * FROM usr_membership_dtl_t where curdate() <date(membership_end_date) order by member_ship_id desc;`;  
    }else{
        var QRY_TO_EXEC = `select * from usr_dtl_t u
        left join continent_dtl_t c on c.continent_id=u.usr_continent
LEFT join country_dtl_t cntry on cntry.country_id=u.usr_country
left join hscodes_state_dtl_t s on s.state_id=u.usr_state where u.d_in=0 and usr_country='${data.country_id}' and 	mail_verification_ind=1;SELECT um.* FROM usr_membership_dtl_t um 
join usr_dtl_t u on u.usr_id=um.usr_id where curdate() <date(membership_end_date) and u.d_in=0 and usr_country='${data.country_id}' order by member_ship_id desc;`;
    }
    
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function") {
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    }
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};


exports.deleteuserMdl = function (data,callback) {
    console.log(data)
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in deleteuserMdl"; 
    if(data.indicator==0){
       var QRY_TO_EXEC = `update usr_dtl_t set d_in=1, d_ts='${date}', deleted_by='${data.deleted_by}' where usr_id='${data.usr_id}';`;    
    }else if (data.indicator==1){
              var QRY_TO_EXEC = `update hscodes_agents_dtl_t set d_in=1, d_ts='${date}' where agency_id='${data.agency_id.agency_id}';`;   
    }else if (data.indicator==2){
              var QRY_TO_EXEC = `update hscodes_franchise_dtl_t set d_in=1, d_ts='${date}' where franchise_id='${data.franchise_id.franchise_id}';`;   
    }else if (data.indicator==3){
              var QRY_TO_EXEC = `update lead_dtl_t set d_in=1, d_ts='${date}' where lead_id='${data.lead_id}';`;   
    }
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function") {
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    }
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};


exports.deletecreatedusrMdl = function (data,callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in deletecreatedusrMdl";  
     if(data.indicator==1){
              var QRY_TO_EXEC = `update hscodes_dshbrd_usr_dtl_t set d_in=1, d_ts='${date}' where request_id='${data.agency_id.agency_id}' and variation_indicator='${data.variation_indicator}';`; 
     }else if(data.indicator==2){
            var QRY_TO_EXEC = `update hscodes_dshbrd_usr_dtl_t set d_in=1, d_ts='${date}' where request_id='${data.franchise_id.franchise_id}' and variation_indicator='${data.variation_indicator}';`; 
     } 
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function") {
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    }
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};



exports.getallagenciesMdl = function (data,callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in getallagenciesMdl"; 
      if(data.indicator==1){
   if(data.country_id==0){
             var QRY_TO_EXEC = `SELECT dsh.u_id,a.*,left(s.state_nm,2) as scode,c.country_cd as ccode,c.country_nm as nativecountry,s.state_nm as nativestate,pc.country_nm as planningcountry,ps.state_nm as planningstate,pcon.continent_nm as planningcontinent ,c.country_email,c.continent_email FROM hscodes_agents_dtl_t a
join country_dtl_t c on c.country_id=a.countri
join hscodes_state_dtl_t s on s.state_id=a.state
left join (select * from hscodes_dshbrd_usr_dtl_t where variation_indicator=1 and d_in=0) dsh on dsh.request_id=a.agency_id

join continent_dtl_t pcon on pcon.continent_id=a.planning_at_continent
join country_dtl_t pc on pc.country_id=a.planning_at_country
join hscodes_state_dtl_t ps on ps.state_id=a.planning_at_state where a.d_in=0 order by agency_id desc;`;
   }else{
            var QRY_TO_EXEC = `SELECT dsh.u_id,a.*,left(s.state_nm,2) as scode,c.country_cd as ccode,c.country_nm as nativecountry,s.state_nm as nativestate,pc.country_nm as planningcountry,ps.state_nm as planningstate,pcon.continent_nm as planningcontinent ,c.country_email,c.continent_email FROM hscodes_agents_dtl_t a
join country_dtl_t c on c.country_id=a.countri
join hscodes_state_dtl_t s on s.state_id=a.state


join continent_dtl_t pcon on pcon.continent_id=a.planning_at_continent
join country_dtl_t pc on pc.country_id=a.planning_at_country
join hscodes_state_dtl_t ps on ps.state_id=a.planning_at_state
left join (select * from hscodes_dshbrd_usr_dtl_t where variation_indicator=1 and d_in=0) dsh on dsh.request_id=a.agency_id
where a.d_in=0 and a.nationality='${data.country_id}' order by agency_id desc;`;
   }
      }else if(data.indicator==2){
             if(data.country_id==0){
             var QRY_TO_EXEC = `SELECT dsh.u_id,a.*,c.country_nm as nativecountry,cnt.continent_id,s.state_nm as nativestate ,c.country_email,c.continent_email,cnt.country_nm as usercountry_nm,cnt.isd_ph_code as std_cd FROM hscodes_franchise_dtl_t a
join country_dtl_t c on c.country_id=a.country_id
join country_dtl_t cnt on cnt.country_id=a.usercountry
join hscodes_state_dtl_t s on s.state_id=a.state_id
left join (select * from hscodes_dshbrd_usr_dtl_t where variation_indicator=2 and d_in=0) dsh on dsh.request_id=a.franchise_id where a.d_in=0 order by franchise_id desc;`;
   }else{
            var QRY_TO_EXEC = `SELECT dsh.u_id,a.*,c.country_nm as nativecountry,cnt.continent_id,s.state_nm as nativestate ,c.country_email,c.continent_email,cnt.country_nm as usercountry_nm,cnt.isd_ph_code as std_cd FROM hscodes_franchise_dtl_t a
join country_dtl_t c on c.country_id=a.country_id
join hscodes_state_dtl_t s on s.state_id=a.state_id
join country_dtl_t cnt on cnt.country_id=a.usercountry
left join (select * from hscodes_dshbrd_usr_dtl_t where variation_indicator=2 and d_in=0) dsh on dsh.request_id=a.franchise_id
where a.d_in=0 and a.country_id='${data.country_id}' order by franchise_id desc;`;
   }
      }else if(data.indicator==3){
             if(data.country_id==0){
             var QRY_TO_EXEC = `SELECT usr.*,l.*,lead_ctgry_nm,DATE_FORMAT( l.lead_expiry_date,"%Y-%m-%d") as edit_lead_expiry_date,
                        chapter_id,chapter_cmpr_id,cntnt.continent_nm as usr_continent_nm,uc.country_nm as usr_country_nm,st.state_nm as usr_state_nm,
                        chapter_nm,
                        currency_cd,
                        currency_nm,
                        cont.continent_nm,
                        
                        
                        ctry.country_cd,
                        ctry.country_nm,
                         ctry.country_email,ctry.continent_email,
                        ctry.country_region, 
                        loadctry.country_nm as loadingcountry,
                        destctry.country_nm as destinationcountry,
                        
                        t.transport_in as loadingporttype,
                        trans.transport_in as destinationporttype,
                        
                        
                        tlp.transport_id as loadingtransport_id,
                        tlp.icao_cd as loadingicao_cd,
                        tlp.iata_cd as loadingiata_cd,
                        tlp.trnsport_nm as loadingtrnsport_nm,
                        tlp.city_nm as loadingcity_nm,
                        CASE
    WHEN tlp.transport_in=1 THEN 'AIR' 
    WHEN tlp.transport_in=2 THEN 'SEA' 
    WHEN tlp.transport_in=3 THEN 'ROAD'  
     WHEN tlp.transport_in=4 THEN 'RAIL'  
    ELSE 'ASWP'
 END as loadingporttypestring,CASE
    WHEN tdp.transport_in=1 THEN 'AIR' 
    WHEN tdp.transport_in=2 THEN 'SEA' 
    WHEN tdp.transport_in=3 THEN 'ROAD'  
     WHEN tdp.transport_in=4 THEN 'RAIL'  
    ELSE 'ASWP'
 END as destinationporttypestring,
                        
                        
                        
                        tdp.transport_id as desttransport_id,
                        tdp.icao_cd as desticao_cd,
                        tdp.iata_cd as destiata_cd,
                        tdp.trnsport_nm as desttrnsport_nm,
                        tdp.city_nm as destcity_nm from lead_dtl_t l
                        join lead_ctgry_dtl_t cat on cat.lead_ctgry_id=l.lead_type
                        join chapter_dtl_t c on c.chapter_id=l.lead_chapter
                        
                        join currency_dtl_t cur on cur.currency_id=l.lead_currency
                        join continent_dtl_t cont on cont.continent_id=l.lead_continent
                        
                        join country_dtl_t ctry on ctry.country_id=l.lead_country
                        join country_dtl_t loadctry on loadctry.country_id=l.loading_port_country
                        join country_dtl_t destctry on destctry.country_id=l.destination_port_country
                        
                        join (SELECT * from hscodes_transport_dtl_t group by transport_in) t on t.transport_in=l.loading_port_type
                        join (SELECT * from hscodes_transport_dtl_t group by transport_in) trans on trans.transport_in=l.destination_port_type
                        
                        
                        join hscodes_transport_dtl_t tlp on tlp.transport_id=l.loading_port
                        join hscodes_transport_dtl_t tdp on tdp.transport_id=l.destination_port
                        join usr_dtl_t usr on usr.usr_id=l.created_by
                            join continent_dtl_t cntnt on cntnt.continent_id=usr.usr_continent
                        join country_dtl_t uc on uc.country_id=usr.usr_country
                        join hscodes_state_dtl_t st on st.state_id=usr.usr_state
                        
                        
                        where date(lead_expiry_date)>curdate()  and l.d_in=0  order by l.i_ts desc;`;
   }else{
            var QRY_TO_EXEC = `SELECT usr.*,l.*,lead_ctgry_nm,DATE_FORMAT( l.lead_expiry_date,"%Y-%m-%d") as edit_lead_expiry_date,
                        chapter_id,chapter_cmpr_id,cntnt.continent_nm as usr_continent_nm,uc.country_nm as usr_country_nm,st.state_nm as usr_state_nm,
                        chapter_nm,
                        currency_cd,
                        currency_nm,
                        cont.continent_nm,
                        
                        
                        ctry.country_cd,
                        ctry.country_nm,
                        ctry.country_email,ctry.continent_email,
                        ctry.country_region, 
                        loadctry.country_nm as loadingcountry,
                        destctry.country_nm as destinationcountry,
                        
                        t.transport_in as loadingporttype,
                        trans.transport_in as destinationporttype,
                         CASE
    WHEN tlp.transport_in=1 THEN 'AIR' 
    WHEN tlp.transport_in=2 THEN 'SEA' 
    WHEN tlp.transport_in=3 THEN 'ROAD'  
     WHEN tlp.transport_in=4 THEN 'RAIL'  
    ELSE 'ASWP'
 END as loadingporttypestring,CASE
    WHEN tdp.transport_in=1 THEN 'AIR' 
    WHEN tdp.transport_in=2 THEN 'SEA' 
    WHEN tdp.transport_in=3 THEN 'ROAD'  
     WHEN tdp.transport_in=4 THEN 'RAIL'  
    ELSE 'ASWP'
 END as destinationporttypestring,
                        
                        tlp.transport_id as loadingtransport_id,
                        tlp.icao_cd as loadingicao_cd,
                        tlp.iata_cd as loadingiata_cd,
                        tlp.trnsport_nm as loadingtrnsport_nm,
                        tlp.city_nm as loadingcity_nm,
                        
                        
                        
                        tdp.transport_id as desttransport_id,
                        tdp.icao_cd as desticao_cd,
                        tdp.iata_cd as destiata_cd,
                        tdp.trnsport_nm as desttrnsport_nm,
                        tdp.city_nm as destcity_nm from lead_dtl_t l
                        join lead_ctgry_dtl_t cat on cat.lead_ctgry_id=l.lead_type
                        join chapter_dtl_t c on c.chapter_id=l.lead_chapter
                        
                        join currency_dtl_t cur on cur.currency_id=l.lead_currency
                        join continent_dtl_t cont on cont.continent_id=l.lead_continent
                        
                        join country_dtl_t ctry on ctry.country_id=l.lead_country
                        join country_dtl_t loadctry on loadctry.country_id=l.loading_port_country
                        join country_dtl_t destctry on destctry.country_id=l.destination_port_country
                        
                        join (SELECT * from hscodes_transport_dtl_t group by transport_in) t on t.transport_in=l.loading_port_type
                        join (SELECT * from hscodes_transport_dtl_t group by transport_in) trans on trans.transport_in=l.destination_port_type
                        
                        
                        join hscodes_transport_dtl_t tlp on tlp.transport_id=l.loading_port
                        join hscodes_transport_dtl_t tdp on tdp.transport_id=l.destination_port
                        
                         join usr_dtl_t usr on usr.usr_id=l.created_by
                             join continent_dtl_t cntnt on cntnt.continent_id=usr.usr_continent
                        join country_dtl_t uc on uc.country_id=usr.usr_country
                        join hscodes_state_dtl_t st on st.state_id=usr.usr_state
                        where date(lead_expiry_date)>curdate() and lead_country='${data.country_id}' and l.d_in=0 order by l.i_ts desc;`;
   }
          
      }
    
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function") {
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    }
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};



exports.postapprovedataMdl = function (data, callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in postapprovedataMdl";
    if (data.indicator == 1) {
        var QRY_TO_EXEC = `update hscodes_agents_dtl_t set approve_in=1,agency_user_id='${data.agency_user_id}', u_ts='${date}' where agency_id='${data.agency_id.agency_id}';`;   
    }else if (data.indicator==2){
        var QRY_TO_EXEC = `update hscodes_franchise_dtl_t set approve_in=1, u_ts='${date}' where franchise_id='${data.franchise_id.franchise_id}';`;  
    } else if (data.indicator == 3) {
        var QRY_TO_EXEC = `update lead_dtl_t set approve_in=1, u_ts='${date}' where lead_id='${data.lead_id}'`;
    }
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function") {
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    }
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};


exports.createdashboardloginMdl = function (data, callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in createdashboardloginMdl";
    if (data.indicator == 1) {
        var QRY_TO_EXEC = `insert into hscodes_dshbrd_usr_dtl_t(uname,upass,request_id,variation_indicator,i_ts,continent_id,country_id,role) values('${data.agency_user_id}','${data.password}','${data.agency_id.agency_id}',1,'${date}','${data.agency_id.planning_at_continent}','${data.agency_id.planning_at_country}','AGENT')`;   
    }else if (data.indicator == 2) {
        var QRY_TO_EXEC = `insert into hscodes_dshbrd_usr_dtl_t(uname,upass,country_id,request_id,variation_indicator,i_ts,role,continent_id) values('${data.franchise_id.franchise_unique_id}','${data.franchise_id.franchise_unique_id}','${data.franchise_id.country_id}','${data.franchise_id.franchise_id}',2,'${date}','FRANCHISE','${data.franchise_id.continent_id}');`;   
    }
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function") {
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    }
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};

//dashboard api end

//agent api start



exports.userDataMdl = function (data,img,c_imgs, callback) {
      var usr_img = ''; 
       var cmpny_img = ''; 
    if(img=='0'){
        var usr_img =``;
    }else{
       var usr_img =`, usr_img = '${img}'`; 
    }
     if(c_imgs=='0'){
        var cmpny_img =``;
    }else{
       var cmpny_img =`, c_img = '${c_imgs}'`; 
    }
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in selectedportsMdl"; 
    var QRY_TO_EXEC = ` UPDATE usr_dtl_t SET usr_nm = '${data.c_name}',usr_address = '${data.c_address}',  usr_email = '${data.c_mail}',  usr_ph = '${data.c_numb}',c_export = '${data.c_export}', c_import = '${data.c_import}' ${cmpny_img} ${usr_img}  where usr_id  = '${data.usr_id}'`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC);
};



// Faizullah google-auth api start

exports.registerUserMdl  = function (data, callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in selectedportsMdl"; 
    var QRY_TO_EXEC = `insert into hscodes_dshbrd_usr_dtl_t (uname,upass) values('${data.uname}','${data.upass}')`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};

exports.getagentmobileloginMdl  = function (data, callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in getagentmobileloginMdl"; 
    var QRY_TO_EXEC = `select * from hscodes_dshbrd_usr_dtl_t where uname='${data.usr_ph}' and upass='${data.usr_password}' and variation_indicator=1; `;
    // and upass='${data.upass}'
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};

exports.loginUserMdl  = function (data, callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in selectedportsMdl"; 
    var QRY_TO_EXEC = `select * from hscodes_dshbrd_usr_dtl_t where uname='${data.uname}' `;
    // and upass='${data.upass}'
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};
exports.deltfaMdl = function (data, callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in selectedportsMdl"; 
    var QRY_TO_EXEC = `select * from hscodes_dshbrd_usr_dtl_t`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls); 
};
exports.gettfaMdl = function (data, callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in selectedportsMdl"; 
    var QRY_TO_EXEC = `select * from hscodes_dshbrd_usr_dtl_t where u_id='${data.u_id}'`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};

exports.setTFAData  = function (data,object, callback) {
    console.log(object);
    const columns = Object.keys(object);
    const values = Object.values(object);
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in selectedportsMdl"; 
    var QRY_TO_EXEC = ` update hscodes_dshbrd_usr_dtl_t set tfa = ' ${ JSON.stringify(object) }' where u_id='${data.u_id}'`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};

// Faizullah Google-auth api ends
  
  
  
// exports.userDataMdl = function (data, img, imgs, callback) {
//     var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
//     var cntxtDtls = "in selectedportsMdl";
//     var QRY_TO_EXEC = ` UPDATE usr_dtl_t SET usr_nm = '${data.c_name}',usr_address = '${data.c_address}',  usr_email = '${data.c_mail}',  usr_ph = '${data.c_numb}',c_export = '${data.c_export}', c_import = '${data.c_import}', c_img = '${img}', usr_img = '${imgs}'  where usr_id  = '${data.usr_id}'`;
//     if (callback && typeof callback == "function")
//         dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
//             callback(err, results);
//             return;
//         });
//     else
//         return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC);
// };

exports.userprofiledataMdl = function (data, callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in userprofiledataMdl";
    var QRY_TO_EXEC = `SELECT * FROM usr_dtl_t where usr_id='${data.usr_id}' and d_in=0;`;
    if (callback && typeof callback == "function") {
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    }
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};
exports.getviewpackagedeatilsMdl = function (data, callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in getviewpackagedeatilsMdl";
    var QRY_TO_EXEC = `SELECT * FROM usr_membership_dtl_t where usr_id='${data.usr_id}' and d_in=0 ORDER BY membership_id DESC;`;
    if (callback && typeof callback == "function") {
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    }
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};

exports.getusersectionsdataMdl = function (data, callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in getusersectionsdataMdl";
    var QRY_TO_EXEC = `SELECT pc.*, sd.section_id as home_sec_id FROM hscodes_purchase_chapter_dtl_t as pc
    join chapter_dtl_t as sd on pc.chapter_id = sd.chapter_id where usr_id='${data.user_id}' and pc.expiry_date>curdate() GROUP BY pc.chapter_id;`;
    if (callback && typeof callback == "function") {
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    }
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};

exports.updateprofileImageMdl = function (imageupload, data, callback) {
    var cntxtDtls = "in updateprofileImageMdl";
    var QRY_TO_EXEC = `update usr_dtl_t set usr_img= '${imageupload}' where usr_id= '${data.usr_id}'; `;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {

            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};

exports.getinsertviewleaddeatils = function (data, leaddata, callback) {
    console.log(leaddata);
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in getinsertviewleaddeatils";
    var QRY_TO_EXEC = `insert into credits_debit_t(usr_id,lead_id,lead_type,lead_country_id,debit_credits,i_ts,category_type) values('${data.usr_id}','${leaddata.lead_id}','${leaddata.lead_type}','${leaddata.lead_country}','1','${date}','LEAD');update hscodes_limit_restriction_dtl_t set user_usage_cnt=(user_usage_cnt*1)+1 where lead_limit_id='${data.finalrestrictdata[0].lead_limit_id}';`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function") {
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    }
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};

exports.getsubscriptiondataMdl = function (data, callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in getsubscriptiondataMdl";
    var QRY_TO_EXEC = `SELECT * FROM usr_membership_dtl_t where usr_id='${data.usr_id}' and d_in=0 ORDER BY membership_id desc;`;
    if (callback && typeof callback == "function") {
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    }
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};
exports.getusingleadsMdl = function (data, callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in getusingleadsMdl";
    // var QRY_TO_EXEC = `SELECT c.*, l.lead_display_id FROM  credits_debit_t as c
    // join lead_dtl_t as l on c.lead_id = l.lead_id where c.usr_id='${data.usr_id}' and c.lead_type='${data.lead_type}'  and lead_country_id='${data.country_id}' and c.d_in=0 ORDER BY id desc;`;
    var QRY_TO_EXEC =`SELECT c.*, l.lead_display_id,lc.lead_ctgry_nm from (SELECT * FROM credits_debit_t where usr_id='${data.usr_id}') c
join (SELECT * from lead_dtl_t) l on l.lead_id=c.lead_id 
join lead_ctgry_dtl_t lc on lc.lead_ctgry_id=c.lead_type where c.d_in=0 order by id DESC;`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function") {
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    }
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};


exports.gethscodesdataMdl = function (data, callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in gethscodesdataMdl";
    var QRY_TO_EXEC = `select a.*,c.country_nm,cntry.country_nm as nativecountry,continent_nm,CASE
    WHEN approve_in=0 THEN 'Pending'  
    ELSE 'Approved'
 END as status ,c.country_email,c.continent_email from add_hscodes_dtl_t a
join country_dtl_t c on c.country_id=a.country_id 
join country_dtl_t cntry on cntry.country_id=a.usr_country 
join continent_dtl_t cont on cont.continent_id=a.usr_continent


where a.d_in=0;`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function") {
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    }
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};


exports.getagencydetailsMdl = function (data, callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in getagencydetailsMdl";
    if(data.indicator==2){
     var QRY_TO_EXEC = `SELECT d.*  FROM hscodes_dshbrd_usr_dtl_t d  where d.d_in=0 and variation_indicator='1'  ORDER BY u_id desc;`;    
    }else if(data.indicator==3){
     var QRY_TO_EXEC = `SELECT d.*  FROM hscodes_dshbrd_usr_dtl_t d  where d.d_in=0 and variation_indicator='0'  ORDER BY u_id desc;`;    
    }else if(data.indicator==4){
     var QRY_TO_EXEC = `SELECT d.*  FROM hscodes_dshbrd_usr_dtl_t d  where d.d_in=0 and variation_indicator='2'  ORDER BY u_id desc;`;    
    }
   
    if (callback && typeof callback == "function") {
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    }
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};


exports.getbuyerdetailsdataMdl = function (data, callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in getbuyerdetailsdataMdl";
      var QRY_TO_EXEC = `SELECT * from lead_dtl_t l
                        join usr_dtl_t u on u.usr_id=l.created_by
                        where date(lead_expiry_date)>curdate() and  l.lead_hscode='${data.hscode}' and l.d_in=0 order by l.i_ts desc;`;
                          console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function") {
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    }
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};
// lead_country='${data.country_id}' and


exports.gethsrmdaatdetailsMdl = function (data, callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in gethsrmdaatdetailsMdl";
    if(data.ind==0){
        var QRY_TO_EXEC =`SELECT * from usr_membership_dtl_t where usr_id='${data.usr_id}' order by i_ts DESC;`;
    }else if(data.ind==1){
         var QRY_TO_EXEC =`SELECT c.*, l.lead_display_id,lc.lead_ctgry_nm from (SELECT * FROM credits_debit_t where usr_id='${data.usr_id}') c
join (SELECT * from lead_dtl_t) l on l.lead_id=c.lead_id 
join lead_ctgry_dtl_t lc on lc.lead_ctgry_id=c.lead_type where c.d_in=0 order by id DESC;`;
 }else if(data.ind==2){
        var QRY_TO_EXEC = `SELECT * FROM hscodes_rfq_dtl_t where d_in=0 and rfqusr_id='${data.usr_id}' ORDER BY rfq_unique_id DESC;select * from hscodes_freight_quotation_dtl_t where rq_usr_id='${data.usr_id}' and d_in=0 order by request_quotation_id desc`; 
    }else if(data.ind==3){
        var QRY_TO_EXEC = `SELECT *,DATE_FORMAT(h.i_ts, '%d-%b-%Y') as postedtime FROM hscodes_request_quote_dtl_t as h 
join lead_dtl_t as l on l.lead_id=h.lead_id
join usr_dtl_t u on u.usr_id=h.usr_id 
join currency_dtl_t c on c.currency_id=h.currency_id
 where h.reqst_sender_id='${data.usr_id}' and h.d_in=0  ORDER BY request_quote_id desc;`; 
    }else if(data.ind==4){
        var QRY_TO_EXEC = `SELECT *,DATE_FORMAT(h.i_ts, '%d-%b-%Y') as postedtime FROM hscodes_request_quote_dtl_t as h 
join lead_dtl_t as l on l.lead_id=h.lead_id
join usr_dtl_t u on u.usr_id=h.reqst_sender_id 
join currency_dtl_t c on c.currency_id=h.currency_id
 where h.usr_id='${data.usr_id}' and h.d_in=0  ORDER BY request_quote_id desc;`; 
    }else if(data.ind==5){
        var QRY_TO_EXEC = `SELECT * FROM hscodes_rfq_dtl_t where d_in=0 ORDER BY rfq_unique_id DESC;select * from hscodes_freight_quotation_dtl_t where d_in=0 order by request_quotation_id desc;`; 
    }
   console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function") {
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    }
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};



exports.getchatgroupdataMdl = function (table_nm,data, callback) {
    // console.log(data.chapter_id.toString().length);
    // if(data.indicator==1 || data.indicator==3 || data.indicator==5){
    // if(data.chapter_id.toString().length>=2){
    //     data.chapter_id=data.chapter_id;
    // }else{
    //      data.chapter_id='0' + data.chapter_id;
    // }
    //   console.log(data.chapter_id);
    // }
  
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in getchatgroupdataMdl"; 
    // if(data.chapter_id)
    if(data.indicator==1){
      var QRY_TO_EXEC = `SELECT * FROM ${table_nm} where tl='${data.tl}' and product='${data.chapter_id}' and group_nm!='0' ;`;   
    // var QRY_TO_EXEC =`SELECT * FROM ${table_nm} t
// left join (select * from hscodes_chat_dtl_t where chat_country_id='${data.country_id}' and d_in=0) c on c. where t.tl='${data.tl}' and t.product='${data.chapter_id}' and group_nm!='0';`;
    }else if(data.indicator==2){
             var QRY_TO_EXEC = `update ${table_nm} set group_nm='${data.group_nm}'  where tl='${data.tl}';`;    
    } else if(data.indicator==3){
             var QRY_TO_EXEC = `select * from hscodes_chat_dtl_t where chat_chapter_id='${data.chapter_id}' and chat_country_id='${data.country_id}' and chat_usr_id='${data.usr_id}' and d_in=0 group by chat_hscode;`;    
    }  else if(data.indicator==4){
             var QRY_TO_EXEC = `update hscodes_chat_dtl_t set d_in=1,d_ts='${date}' where chat_id='${data.chat_id}';`;    
    }    else if(data.indicator==5){
             var QRY_TO_EXEC = `SELECT hscode_id,tl FROM ${table_nm} where  product='${data.chapter_id}' and group_nm!='0' ;`;    
    } 
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function") {
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    }
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};


exports.chatgroupdtlMdl = function (data, callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in chatgroupdtlMdl";  
      var QRY_TO_EXEC = `insert into hscodes_chat_dtl_t (chat_chapter_id,chat_group_nm,chat_hscode,chat_country_id,chat_usr_id,i_ts) values('${data.chapter_id}','${data.group_nm}','${data.tl}','${data.country_id}','${data.usr_id}','${date}');`;    
      console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function") {
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    }
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};

exports.checkchatgroupdtlMdl = function (data, callback) {
    console.log(data);
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in checkchatgroupdtlMdl";  
      var QRY_TO_EXEC = `select * from hscodes_chat_dtl_t where chat_chapter_id='${data.chapter_id}' and chat_group_nm='${data.group_nm}' and chat_hscode='${data.tl}' and chat_country_id='${data.country_id}' and chat_usr_id='${data.usr_id}' and d_in=0;`;    
      console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function") {
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    }
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};

//04/12/2021
exports.getCountryidmdl = function (data, callback) {
    console.log(data);
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in getCountryidmdl";
    // var QRY_TO_EXEC = `SELECT * FROM country_dtl_t WHERE country_nm LIKE '${data.country}' and d_in=0`;
       var QRY_TO_EXEC = `SELECT * FROM country_dtl_t WHERE country_cd LIKE '${data.country}' and d_in=0`;
        console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function") {
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    }
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};
exports.getCountryidwebMdl = function (data, callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in getCountryidwebMdl";
    var QRY_TO_EXEC = `SELECT * FROM country_dtl_t WHERE country_cd LIKE '${data.countryCode}' and d_in=0`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function") {
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    }
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};


exports.checkingverificationmailMdl = function (id, callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in checkingverificationmailMdl";
    var QRY_TO_EXEC = `select * from usr_dtl_t where mail_verification_ind=1 and 	usr_id='${id}'`;
    if (callback && typeof callback == "function") {
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    }
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};
exports.verificationmailMdl = function (id, callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in verificationmailMdl";
    var QRY_TO_EXEC = `update usr_dtl_t set mail_verification_ind=1 where 	usr_id='${id}'`;
    if (callback && typeof callback == "function") {
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    }
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};

exports.updatefreemembershipdetails = function (id, callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss"); 
    var currentDate = moment(date);
    var Expirydate = moment(currentDate).add(365, 'd').utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss"); 
    console.log(Expirydate); 
    var cntxtDtls = "in updatefreemembershipdetails";
    var QRY_TO_EXEC = `insert into usr_membership_dtl_t (usr_id,member_ship_id,membership_name,membership_start_date,membership_end_date,usr_credits,i_ts,d_in,ammount,usd_ammount) values('${id}',4,'Free','${date}','${Expirydate}',0,'${date}',0,0,'0')`;
  console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function") {
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    }
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};


exports.submitipdetailsMdl = function (data, callback) {
    console.log(data);
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in submitipdetailsMdl";
    var QRY_TO_EXEC = `insert into hscodes_usrlogin_audit_dtl_t (usr_id,ip_address,i_ts,d_in) values('${data.user_data.usr_id}','${data.ip_address}','${date}',0)`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function") {
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    }
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};


exports.bulkusersuploadMdl = function (data,postObj,  callback) {
	var cntxtDtls = "in bulkusersuploadMdl";
	var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
	
    
	    if (postObj.usr_nm) {
        var usr_nm = postObj.usr_nm.replace(/&/g, "").replace(/\\/g, "\\ ").replace(/⋆/g, "*").replace(/β/g, "").replace(/>/g, "").replace(/</g, "").replace(/"/g, "").replace(/‘/g, "").replace(/'/g, "").replace(/`/g, "").replace(/=/g, "=").replace(/≥/gi, "").replace(/≤/gi, "").replace(/₹/gi, "Rs. ");
    } else {
        var usr_nm = postObj.usr_nm;
    }
    if (postObj.usr_ph) {
        var usr_ph = postObj.usr_ph.replace(/&/g, "").replace(/\\/g, "\\ ").replace(/⋆/g, "*").replace(/β/g, "").replace(/>/g, "").replace(/</g, "").replace(/"/g, "").replace(/‘/g, "").replace(/'/g, "").replace(/`/g, "").replace(/=/g, "=").replace(/≥/gi, "").replace(/≤/gi, "").replace(/₹/gi, "Rs. ");
    } else {
        var usr_ph = postObj.usr_ph;
    }
    if (postObj.usr_email) {
        var usr_email = postObj.usr_email.replace(/&/g, "").replace(/\\/g, "\\ ").replace(/⋆/g, "*").replace(/β/g, "").replace(/>/g, "").replace(/</g, "").replace(/"/g, "").replace(/‘/g, "").replace(/'/g, "").replace(/`/g, "").replace(/=/g, "=").replace(/≥/gi, "").replace(/≤/gi, "").replace(/₹/gi, "Rs. ");
    } else {
        var usr_email = postObj.usr_email;
    }
    if (postObj.usr_address) {
        var usr_address = postObj.usr_address.replace(/&/g, "").replace(/\\/g, "\\ ").replace(/⋆/g, "*").replace(/β/g, "").replace(/>/g, "").replace(/</g, "").replace(/"/g, "").replace(/‘/g, "").replace(/'/g, "").replace(/`/g, "").replace(/=/g, "=").replace(/≥/gi, "").replace(/≤/gi, "").replace(/₹/gi, "Rs. ");
    } else {
        var usr_address = postObj.usr_address;
    }
    if (postObj.usr_password) {
        var usr_password = postObj.usr_password.replace(/&/g, "").replace(/\\/g, "\\ ").replace(/⋆/g, "*").replace(/β/g, "").replace(/>/g, "").replace(/</g, "").replace(/"/g, "").replace(/‘/g, "").replace(/'/g, "").replace(/`/g, "").replace(/=/g, "=").replace(/≥/gi, "").replace(/≤/gi, "").replace(/₹/gi, "Rs. ");
    } else {
        var usr_password = postObj.usr_password;
    }
    if (postObj.continent_n) {
        var continent_n = postObj.continent_n.replace(/&/g, "").replace(/\\/g, "\\ ").replace(/⋆/g, "*").replace(/β/g, "").replace(/>/g, "").replace(/</g, "").replace(/"/g, "").replace(/‘/g, "").replace(/'/g, "").replace(/`/g, "").replace(/=/g, "=").replace(/≥/gi, "").replace(/≤/gi, "").replace(/₹/gi, "Rs. ");
    } else {
        var continent_n = postObj.continent_n;
    }if (postObj.country_n) {
        var country_n = postObj.country_n.replace(/&/g, "").replace(/\\/g, "\\ ").replace(/⋆/g, "*").replace(/β/g, "").replace(/>/g, "").replace(/</g, "").replace(/"/g, "").replace(/‘/g, "").replace(/'/g, "").replace(/`/g, "").replace(/=/g, "=").replace(/≥/gi, "").replace(/≤/gi, "").replace(/₹/gi, "Rs. ");
    } else {
        var country_n = postObj.country_n;
    }
            var QRY_TO_EXEC = `insert into usr_dtl_t(usr_nm,usr_ph,usr_email,usr_address,usr_password,continent_n,country_n,referred_by,usr_i_ts)
            values('${postObj.usr_nm}','${postObj.usr_ph}','${postObj.usr_email}','${postObj.usr_address}','${postObj.usr_password}','${postObj.continent_n}',
            '${postObj.country_n}','${data.usr_id}','${date}');`
	
    console.log(QRY_TO_EXEC);
	if (callback && typeof callback == "function")
		dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
			callback(err, results);
			return;
		});
	else
		return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};


exports.updtcontinentIdMdl = function ( callback) {
	var cntxtDtls = "in updtcontinentIdMdl";
	var QRY_TO_EXEC = `update usr_dtl_t as u 
    join  continent_dtl_t as con on u.continent_n = con.continent_nm  
    set usr_continent=con.continent_id where u.continent_n = con.continent_nm ;`;
	  console.log(34,QRY_TO_EXEC);
	if (callback && typeof callback == "function") {
		dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
			callback(err, results);
			return;
		});
	}
	else
		return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
}
// 
exports.updtcountryIdMdl = function ( callback) {  
	var cntxtDtls = "in updtcountryIdMdl";
    var QRY_TO_EXEC = `update usr_dtl_t as u 
    join  country_dtl_t as con on u.country_n = con.country_nm  
    set usr_country=con.country_id where u.country_n = con.country_nm ;`;
	  console.log(65,QRY_TO_EXEC);
	if (callback && typeof callback == "function") {
		dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
			callback(err, results);
			return;
		});
	}
	else
		return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
}


exports.getagentbasedusersMdl = function (data, callback) {  
	var cntxtDtls = "in getagentbasedusersMdl";
 if(data.country_id==0){
      var QRY_TO_EXEC = `select * from usr_dtl_t u
        left join continent_dtl_t c on c.continent_id=u.usr_continent
LEFT join country_dtl_t cntry on cntry.country_id=u.usr_country
left join hscodes_state_dtl_t s on s.state_id=u.usr_state where u.d_in=0 and referred_by='${data.usr_id}' and mail_verification_ind=1;SELECT * FROM usr_membership_dtl_t where curdate() <date(membership_end_date) order by member_ship_id desc`;   
    }else{
        var QRY_TO_EXEC = `select * from usr_dtl_t u
        left join continent_dtl_t c on c.continent_id=u.usr_continent
LEFT join country_dtl_t cntry on cntry.country_id=u.usr_country
left join hscodes_state_dtl_t s on s.state_id=u.usr_state where u.d_in=0 and usr_country='${data.country_id}' and referred_by='${data.usr_id}' and 	mail_verification_ind=1;SELECT um.* FROM usr_membership_dtl_t um 
join usr_dtl_t u on u.usr_id=um.usr_id where curdate() <date(membership_end_date) and u.d_in=0 and usr_country='${data.country_id}' order by member_ship_id desc;`;
    }
	  console.log(65,QRY_TO_EXEC);
	if (callback && typeof callback == "function") {
		dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
			callback(err, results);
			return;
		});
	}
	else
		return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
}

//09/12/2021

exports.updatepublicplayeridMdl = function (data, callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in updatepublicplayeridMdl";
    var QRY_TO_EXEC = `update usr_dtl_t set player_id='${data.player_id}' where usr_id='${data.user_id}'`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function") {
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    }
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};
exports.getuserpalerId = function (id, callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in getuserpalerId";
    var QRY_TO_EXEC = `SELECT u.*,c.country_email,c.continent_email  FROM usr_dtl_t u
    join country_dtl_t c on c.country_id=u.usr_country
    
    WHERE u.usr_id = '${id}'`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function") {
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    }
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};

exports.getpurchasechapterdetailsMdl = function (id, callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in getpurchasechapterdetailsMdl";
    var QRY_TO_EXEC = `SELECT hs.*, player_id FROM hscodes_purchase_chapter_dtl_t as hs
                        join usr_dtl_t as u on u.usr_id=hs.usr_id
                        where hs.chapter_id='${id}' GROUP BY hs.usr_id`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function") {
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    }
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};

exports.postAfterSuccessMdl = function (data,callback) { 
  var details = data.details;
  var cntxtDtls = "in postAfterSuccessMdl";
    var QRY_TO_EXEC = `insert into initiatemeeting_tb (attendee_pwd,meeting_id,meeting_name,moderator_pwd,recording,status,url) values('${details.attendee_pwd}','${details.meeting_id}',
  '${details.meeting_name}','${details.moderator_pwd}','${details.recording}','${data.status}','${data.url}')`;
  //   console.log(119,QRY_TO_EXEC);
  if (callback && typeof callback == "function") {
    dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
      callback(err, results);
      return;
    });
  }
  else
    return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
}


exports.getAdminInitiatiteMeetingDltsMdl = function (callback) { 
  var cntxtDtls = "in getAdminInitiatiteMeetingDltsMdl";
    var QRY_TO_EXEC = `select * from initiatemeeting_tb where d_in=0 order by id desc Limit 1 ;`;
    console.log(119,QRY_TO_EXEC);
  if (callback && typeof callback == "function") {
    dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
      callback(err, results);
      return;
    });
  }
  else
    return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
}

// getAllInitiatedMeetingsMdl
exports.getAllInitiatedMeetingsMdl = function (callback) { 
  var cntxtDtls = "in getAllInitiatedMeetingsMdl";
    var QRY_TO_EXEC = `select * from initiatemeeting_tb where d_in=0 order by id desc  ;`;
    console.log(119,QRY_TO_EXEC);
  if (callback && typeof callback == "function") {
    dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
      callback(err, results);
      return;
    });
  }
  else
    return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
}


exports.postremainderleaddataMdl = function (data,callback) { 
     var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
  var cntxtDtls = "in postremainderleaddataMdl";
    var QRY_TO_EXEC = `insert into hscodes_lead_remainder_dtl_t(remainder_lead_id,remainder_date,remainder_lead_expiry_dt,remainder_usr_id,d_in,i_ts) values('${data.remainder_lead_id}','${data.remainder_date}','${data.remainder_lead_expiry_dt}','${data.remainder_usr_id}',0,'${date}');`;
    console.log(119,QRY_TO_EXEC);
  if (callback && typeof callback == "function") {
    dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
      callback(err, results);
      return;
    });
  }
  else
    return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
}


exports.postpolicydataMdl = function (data,callback) { 
     var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
  var cntxtDtls = "in postpolicydataMdl";
    var QRY_TO_EXEC = `insert into hscodes_policies_suggest_dtl_t(policyname,policyemail,policymobile,policycountry_nm,policy_hscode_id,policy_country_id,policy_subject,policy_url,user_id,d_in,i_ts,variat_indicator,policy_hscode_no) values('${data.policyname}','${data.policyemail}','${data.policymobile}','${data.policycountry_nm}','${data.policy_hscode_id}','${data.policy_country_id}','${data.policy_subject}','${data.policy_url}','${data.user_id}',0,'${date}','${data.indicator}','${data.policy_hscode_no}');`;
    console.log(119,QRY_TO_EXEC);
  if (callback && typeof callback == "function") {
    dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
      callback(err, results);
      return;
    });
  }
  else
    return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
}



exports.postrfqdataMdl = function (data,callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in postrfqdataMdl";
    var QRY_TO_EXEC = `insert into 	hscodes_rfq_dtl_t (rfqcurrency,rfqname,rfqemail,rfqmobile,rfqcountry_nm,rfqhscode_id,rfqquantity,rfqunit_of_measurement,rfqunit_price,rfqusr_id,d_in,i_ts,rfqcountry_id,rfq_hscode_no) values('${data.rfqcurrency}','${data.rfqname}','${data.rfqemail}','${data.rfqmobile}','${data.rfqcountry_nm}','${data.rfqhscode_id}','${data.rfqquantity}','${data.rfqunit_of_measurement}','${data.rfqunit_price}','${data.rfqusr_id}',0 ,'${date}','${data.rfqcountry_id}','${data.rfq_hscode_no}');update hscodes_limit_restriction_dtl_t set user_usage_cnt=(user_usage_cnt*1)+1 where lead_limit_id='${data.finalrestrictdata[0].lead_limit_id}';`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function") {
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    }
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};


exports.getuniqueidrfqdataMdl = function (data,insertId,callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in getuniqueidrfqdataMdl";
    var QRY_TO_EXEC = `SELECT r.*,country_cd,country_email,continent_email from hscodes_rfq_dtl_t r join country_dtl_t c on c.country_id=r.rfqcountry_id where r.d_in=0 and rfq_unique_id='0' and rfq_id='${insertId}';`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function") {
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    }
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};


exports.updateuniqueidrfqdataMdl = function (data,unique_id,lastinsertid,callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in updateuniqueidrfqdataMdl";
    var QRY_TO_EXEC = `update hscodes_rfq_dtl_t  set rfq_unique_id='${unique_id}' where rfq_id='${lastinsertid}';insert into credits_debit_t(usr_id,lead_id,lead_type,lead_country_id,debit_credits,i_ts,category_type) values('${data.rfqusr_id}','${lastinsertid}','${data.rfq_hscode_no}','${data.rfqcountry_id}','1','${date}','RFQ')`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function") {
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    }
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};

exports.getrfqusercreditsdeatils = function (data, callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in getusercreditsdeatils";
    var QRY_TO_EXEC = `SELECT usr_id,total_credits  FROM usr_dtl_t where usr_id='${data.rfqusr_id}' and d_in=0; `;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};

exports.updaterfqurcreditsMdl = function (data, updatecredits, callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in updateurcreditsMdl";
    var QRY_TO_EXEC = `update usr_dtl_t set total_credits='${updatecredits}' where usr_id='${data.rfqusr_id}'; `;

    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};


exports.getpolicyurldataMdl = function (table_nm,data,callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in getpolicyurldataMdl";
    console.log(data);
      if(data.indicator==1){
    if(data.country_id==0){
    var QRY_TO_EXEC = `SELECT p.*,c.country_nm,DATE_FORMAT( p.i_ts,"%d-%m-%Y %T") as inserted_time,CASE
    WHEN p.variat_indicator=1 THEN 'export' 
    WHEN p.variat_indicator=2 THEN 'import'
    WHEN p.variat_indicator=3 THEN 'local'
    ELSE 'None'
 END as type FROM hscodes_policies_suggest_dtl_t p
join country_dtl_t c on c.country_id=p.policy_country_id where p.d_in=0;`;
    }else {
       var QRY_TO_EXEC = `SELECT p.*,c.country_nm,DATE_FORMAT( p.i_ts,"%d-%m-%Y %T") as inserted_time,CASE
    WHEN p.variat_indicator=1 THEN 'export' 
    WHEN p.variat_indicator=2 THEN 'import'
    WHEN p.variat_indicator=3 THEN 'local'
    ELSE 'None'
 END as type FROM hscodes_policies_suggest_dtl_t p
join country_dtl_t c on c.country_id=p.policy_country_id where p.d_in=0 and  policy_country_id='${data.country_id}';`;     
    }
      }else if(data.indicator==2){
         var QRY_TO_EXEC = `update hscodes_policies_suggest_dtl_t set d_in=1, d_ts='${date}' where policy_id='${data.policy_id}'`;   
      }else if(data.indicator==3){
         var QRY_TO_EXEC = `select * from ${table_nm} where hscode_id='${data.hscode_id}';`;   
      }else if(data.indicator==4){
          var QRY_TO_EXEC = `update ${table_nm} set ${data.type}_policies='${data.policy_url}' where hscode_id='${data.policy_hscode_id}';`; 
      }
     console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function") {
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    }
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};



exports.updatepolicyurldataMdl = function (data,callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in updatepolicyurldataMdl";
    var QRY_TO_EXEC = `update hscodes_policies_suggest_dtl_t set approve_in=1, u_ts='${date}' where policy_id='${data.policy_id}';`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function") {
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    }
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};

 
exports.getrfqreuesteddataMdl = function (table_nm,data,callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in getrfqreuesteddataMdl";
    if(data.indicator==1){
    if(data.country_id==0){
    var QRY_TO_EXEC = `SELECT p.*,c.country_nm,DATE_FORMAT( p.i_ts,"%d-%m-%Y %T") as inserted_time,CASE
    WHEN p.rfq_approve_in=0 THEN 'Pending'
    ELSE 'Approved'
 END as status FROM hscodes_rfq_dtl_t p
join country_dtl_t c on c.country_id=p.rfqcountry_id where p.d_in=0`;
    }else {
       var QRY_TO_EXEC = `SELECT p.*,c.country_nm,DATE_FORMAT( p.i_ts,"%d-%m-%Y %T") as inserted_time,CASE
    WHEN p.rfq_approve_in=0 THEN 'Pending'
    ELSE 'Approved'
 END as status FROM hscodes_rfq_dtl_t p
join country_dtl_t c on c.country_id=p.rfqcountry_id where p.d_in=0 and  rfqcountry_id='${data.country_id}';`;     
    }
      }else if(data.indicator==2){
         var QRY_TO_EXEC = `update hscodes_rfq_dtl_t set d_in=1, d_ts='${date}' where rfq_id='${data.rfq_id}'`;   
      }else if(data.indicator==3){
         var QRY_TO_EXEC = `select * from ${table_nm} where hscode_id='${data.rfqhscode_id}';`;   
      }else if(data.indicator==4){
          var QRY_TO_EXEC = `update ${table_nm} set ${data.type}_policies='${data.policy_url}' where rfq_id='${data.rfq_id}';`; 
      }
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function") {
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    }
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};



exports.getadminaccountsdataMdl = function (data,callback) {
    var yeardate = moment().utcOffset("+05:30").format("YYYY");
    var monthdate = moment().utcOffset("+05:30").format("YYYY-MM");
    var cntxtDtls = "in getadminaccountsdataMdl";
    if(data.indicator==1){
      var QRY_TO_EXEC = `SELECT SUM(CASE WHEN member_ship_id=1 THEN usd_ammount else 0 End) as platinum,
SUM(CASE WHEN member_ship_id=2 THEN usd_ammount else 0 End) as gold,
SUM(CASE WHEN member_ship_id=3 THEN usd_ammount else 0 End) as silver,
sum(usd_ammount) as totalusdammount,YEAR(i_ts) as year FROM usr_membership_dtl_t where d_in=0 group by YEAR(i_ts);`;   
    }else if(data.indicator==2){
      var QRY_TO_EXEC = `SELECT SUM(CASE WHEN member_ship_id=1 THEN usd_ammount else 0 End) as platinum,
SUM(CASE WHEN member_ship_id=2 THEN usd_ammount else 0 End) as gold,
SUM(CASE WHEN member_ship_id=3 THEN usd_ammount else 0 End) as silver,
sum(usd_ammount) as totalusdammount,CONCAT(YEAR(i_ts),'-', month(i_ts)) as year FROM usr_membership_dtl_t where d_in=0 group by month(i_ts),YEAR(i_ts);`;   
    }else if(data.indicator==3){
      var QRY_TO_EXEC = `SELECT SUM(CASE WHEN member_ship_id=1 THEN usd_ammount else 0 End) as platinum,
SUM(CASE WHEN member_ship_id=2 THEN usd_ammount else 0 End) as gold,
SUM(CASE WHEN member_ship_id=3 THEN usd_ammount else 0 End) as silver,
sum(usd_ammount) as totalusdammount,${yeardate} as year FROM usr_membership_dtl_t where d_in=0 and date(i_ts) like '%${yeardate}%'`;   
    }else if(data.indicator==4){
      var QRY_TO_EXEC = `SELECT SUM(CASE WHEN member_ship_id=1 THEN usd_ammount else 0 End) as platinum,
SUM(CASE WHEN member_ship_id=2 THEN usd_ammount else 0 End) as gold,
SUM(CASE WHEN member_ship_id=3 THEN usd_ammount else 0 End) as silver,
sum(usd_ammount) as totalusdammount,CONCAT('${monthdate}') as year FROM usr_membership_dtl_t where d_in=0 and date(i_ts) like '%${monthdate}%'`;   
    }else if(data.indicator==5){
      var QRY_TO_EXEC = `SELECT country_nm,SUM(CASE WHEN member_ship_id=1 THEN usd_ammount else 0 End) as platinum,
SUM(CASE WHEN member_ship_id=2 THEN usd_ammount else 0 End) as gold,
SUM(CASE WHEN member_ship_id=3 THEN usd_ammount else 0 End) as silver,
sum(usd_ammount) as totalusdammount,YEAR(m.i_ts) as year FROM usr_membership_dtl_t m
join usr_dtl_t u on u.usr_id=m.usr_id
join country_dtl_t c on c.country_id=u.usr_country
where m.d_in=0 group by u.usr_country,year(m.i_ts);`;   
    }else if(data.indicator==6){
      var QRY_TO_EXEC = `SELECT country_nm,SUM(CASE WHEN member_ship_id=1 THEN usd_ammount else 0 End) as platinum,
SUM(CASE WHEN member_ship_id=2 THEN usd_ammount else 0 End) as gold,
SUM(CASE WHEN member_ship_id=3 THEN usd_ammount else 0 End) as silver,
sum(usd_ammount) as totalusdammount,CONCAT(YEAR(m.i_ts),'-', month(m.i_ts)) as year FROM usr_membership_dtl_t m 
join usr_dtl_t u on u.usr_id=m.usr_id
join country_dtl_t c on c.country_id=u.usr_country where m.d_in=0 group by u.usr_country,month(m.i_ts),YEAR(m.i_ts);`;   
    }else if(data.indicator==7){
      var QRY_TO_EXEC = `SELECT country_nm,SUM(CASE WHEN member_ship_id=1 THEN usd_ammount else 0 End) as platinum,
SUM(CASE WHEN member_ship_id=2 THEN usd_ammount else 0 End) as gold,
SUM(CASE WHEN member_ship_id=3 THEN usd_ammount else 0 End) as silver,
sum(usd_ammount) as totalusdammount,${yeardate} as year FROM usr_membership_dtl_t m
join usr_dtl_t u on u.usr_id=m.usr_id
join country_dtl_t c on c.country_id=u.usr_country where m.d_in=0 and date(m.i_ts) like '%${yeardate}%' group by u.usr_country;`;   
    }else if(data.indicator==8){
      var QRY_TO_EXEC = `SELECT country_nm,SUM(CASE WHEN member_ship_id=1 THEN usd_ammount else 0 End) as platinum,
SUM(CASE WHEN member_ship_id=2 THEN usd_ammount else 0 End) as gold,
SUM(CASE WHEN member_ship_id=3 THEN usd_ammount else 0 End) as silver,
sum(usd_ammount) as totalusdammount,CONCAT('${monthdate}') as year FROM usr_membership_dtl_t m 
join usr_dtl_t u on u.usr_id=m.usr_id
join country_dtl_t c on c.country_id=u.usr_country where m.d_in=0 and date(m.i_ts) like '%${monthdate}%'  group by u.usr_country;`;   
    }
   
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function") {
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    }
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};


exports.getagentaccountsdataMdl = function (data,callback) {
    var yeardate = moment().utcOffset("+05:30").format("YYYY");
    var monthdate = moment().utcOffset("+05:30").format("YYYY-MM");
    var cntxtDtls = "in getagentaccountsdataMdl";
    if(data.indicator==1){
      var QRY_TO_EXEC = `SELECT SUM(CASE WHEN member_ship_id=1 THEN usd_ammount else 0 End) as platinum,
SUM(CASE WHEN member_ship_id=2 THEN usd_ammount else 0 End) as gold,
SUM(CASE WHEN member_ship_id=3 THEN usd_ammount else 0 End) as silver,
sum(usd_ammount) as totalusdammount,YEAR(i_ts) as year FROM usr_membership_dtl_t m
join (select usr_id from usr_dtl_t where d_in=0 and mail_verification_ind=1 and referred_by='${data.user_id}') u on u.usr_id=m.usr_id

where d_in=0 group by YEAR(i_ts);`;   
    }else if(data.indicator==2){
      var QRY_TO_EXEC = `SELECT SUM(CASE WHEN member_ship_id=1 THEN usd_ammount else 0 End) as platinum,
SUM(CASE WHEN member_ship_id=2 THEN usd_ammount else 0 End) as gold,
SUM(CASE WHEN member_ship_id=3 THEN usd_ammount else 0 End) as silver,
sum(usd_ammount) as totalusdammount,CONCAT(YEAR(i_ts),'-', month(i_ts)) as year FROM usr_membership_dtl_t m
join (select usr_id from usr_dtl_t where d_in=0 and mail_verification_ind=1 and referred_by='${data.user_id}') u on u.usr_id=m.usr_id
      
      where m.d_in=0 group by month(i_ts),YEAR(i_ts);;`;   
    }else if(data.indicator==3){
      var QRY_TO_EXEC = `   SELECT SUM(CASE WHEN member_ship_id=1 THEN usd_ammount else 0 End) as platinum,
SUM(CASE WHEN member_ship_id=2 THEN usd_ammount else 0 End) as gold,
SUM(CASE WHEN member_ship_id=3 THEN usd_ammount else 0 End) as silver,
sum(usd_ammount) as totalusdammount,${yeardate} as year FROM usr_membership_dtl_t m
join (select usr_id from usr_dtl_t where d_in=0 and mail_verification_ind=1 and referred_by='${data.user_id}') u on u.usr_id=m.usr_id
where d_in=0 and date(i_ts) like '%${yeardate}%';`;   
    }else if(data.indicator==4){
      var QRY_TO_EXEC = `SELECT SUM(CASE WHEN member_ship_id=1 THEN usd_ammount else 0 End) as platinum,
SUM(CASE WHEN member_ship_id=2 THEN usd_ammount else 0 End) as gold,
SUM(CASE WHEN member_ship_id=3 THEN usd_ammount else 0 End) as silver,
sum(usd_ammount) as totalusdammount,CONCAT('${monthdate}') as year FROM usr_membership_dtl_t m
join (select usr_id from usr_dtl_t where d_in=0 and mail_verification_ind=1 and referred_by='${data.user_id}') u on u.usr_id=m.usr_id
where d_in=0 and date(i_ts) like '%${monthdate}%';`;   
    }else if(data.indicator==5){
      var QRY_TO_EXEC = `SELECT country_nm,SUM(CASE WHEN member_ship_id=1 THEN usd_ammount else 0 End) as platinum,
SUM(CASE WHEN member_ship_id=2 THEN usd_ammount else 0 End) as gold,
SUM(CASE WHEN member_ship_id=3 THEN usd_ammount else 0 End) as silver,
sum(usd_ammount) as totalusdammount,YEAR(m.i_ts) as year FROM usr_membership_dtl_t m
join (select * from usr_dtl_t where d_in=0 and mail_verification_ind=1 and referred_by='${data.user_id}') u on u.usr_id=m.usr_id
join country_dtl_t c on c.country_id=u.usr_country

where m.d_in=0 group by u.usr_country,year(m.i_ts);`;   
    }else if(data.indicator==6){
      var QRY_TO_EXEC = `SELECT country_nm,SUM(CASE WHEN member_ship_id=1 THEN usd_ammount else 0 End) as platinum,
SUM(CASE WHEN member_ship_id=2 THEN usd_ammount else 0 End) as gold,
SUM(CASE WHEN member_ship_id=3 THEN usd_ammount else 0 End) as silver,
sum(usd_ammount) as totalusdammount,CONCAT(YEAR(m.i_ts),'-', month(m.i_ts)) as year FROM usr_membership_dtl_t m 
join (select * from usr_dtl_t where d_in=0 and mail_verification_ind=1 and referred_by='${data.user_id}') u on u.usr_id=m.usr_id
join country_dtl_t c on c.country_id=u.usr_country where m.d_in=0 group by u.usr_country,month(m.i_ts),YEAR(m.i_ts);`;   
    }else if(data.indicator==7){
      var QRY_TO_EXEC = `SELECT country_nm,SUM(CASE WHEN member_ship_id=1 THEN usd_ammount else 0 End) as platinum,
SUM(CASE WHEN member_ship_id=2 THEN usd_ammount else 0 End) as gold,
SUM(CASE WHEN member_ship_id=3 THEN usd_ammount else 0 End) as silver,
sum(usd_ammount) as totalusdammount,${yeardate} as year FROM usr_membership_dtl_t m
join (select * from usr_dtl_t where d_in=0 and mail_verification_ind=1 and referred_by='${data.user_id}') u on u.usr_id=m.usr_id
join country_dtl_t c on c.country_id=u.usr_country where m.d_in=0 and date(m.i_ts) like '%${yeardate}%' group by u.usr_country;`;   
    }else if(data.indicator==8){
      var QRY_TO_EXEC = `SELECT country_nm,SUM(CASE WHEN member_ship_id=1 THEN usd_ammount else 0 End) as platinum,
SUM(CASE WHEN member_ship_id=2 THEN usd_ammount else 0 End) as gold,
SUM(CASE WHEN member_ship_id=3 THEN usd_ammount else 0 End) as silver,
sum(usd_ammount) as totalusdammount,CONCAT('${monthdate}') as year FROM usr_membership_dtl_t m 
join (select * from usr_dtl_t where d_in=0 and mail_verification_ind=1 and referred_by='${data.user_id}') u on u.usr_id=m.usr_id
join country_dtl_t c on c.country_id=u.usr_country where m.d_in=0 and date(m.i_ts) like '%${monthdate}%'  group by u.usr_country;`;   
    }
   
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function") {
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    }
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};


exports.getagentusersdataMdl = function (data,callback) {
    var yeardate = moment().utcOffset("+05:30").format("YYYY");
    var monthdate = moment().utcOffset("+05:30").format("YYYY-MM");
    var cntxtDtls = "in getagentusersdataMdl";
    if(data.indicator==1){
      var QRY_TO_EXEC = `SELECT count(DISTINCT u.usr_id) as totalusers,SUM(CASE WHEN member_ship_id=1 THEN 1 else 0 End) as platinum,
SUM(CASE WHEN member_ship_id=2 THEN 1 else 0 End) as gold,
SUM(CASE WHEN member_ship_id=3 THEN 1 else 0 End) as silver,
SUM(CASE WHEN member_ship_id=4 THEN 1 else 0 End) as free,
sum(usd_ammount) as totalusdammount,YEAR(i_ts) as year FROM usr_membership_dtl_t m
join (select usr_id from usr_dtl_t where d_in=0 and mail_verification_ind=1 and referred_by='${data.user_id}') u on u.usr_id=m.usr_id

where d_in=0 group by YEAR(i_ts);`;   
    }else if(data.indicator==2){
      var QRY_TO_EXEC = `SELECT count(DISTINCT u.usr_id) as totalusers,SUM(CASE WHEN member_ship_id=1 THEN 1 else 0 End) as platinum,
SUM(CASE WHEN member_ship_id=2 THEN 1 else 0 End) as gold,
SUM(CASE WHEN member_ship_id=3 THEN 1 else 0 End) as silver,
SUM(CASE WHEN member_ship_id=4 THEN 1 else 0 End) as free,
sum(usd_ammount) as totalusdammount,CONCAT(YEAR(i_ts),'-', month(i_ts)) as year FROM usr_membership_dtl_t m
join (select usr_id from usr_dtl_t where d_in=0 and mail_verification_ind=1 and referred_by='${data.user_id}') u on u.usr_id=m.usr_id
      
      where m.d_in=0 group by month(i_ts),YEAR(i_ts);;`;   
    }else if(data.indicator==3){
      var QRY_TO_EXEC = `   SELECT count(DISTINCT u.usr_id) as totalusers,SUM(CASE WHEN member_ship_id=1 THEN 1 else 0 End) as platinum,
SUM(CASE WHEN member_ship_id=2 THEN 1 else 0 End) as gold,
SUM(CASE WHEN member_ship_id=3 THEN 1 else 0 End) as silver,
SUM(CASE WHEN member_ship_id=4 THEN 1 else 0 End) as free,
sum(usd_ammount) as totalusdammount,${yeardate} as year FROM usr_membership_dtl_t m
join (select usr_id from usr_dtl_t where d_in=0 and mail_verification_ind=1 and referred_by='${data.user_id}') u on u.usr_id=m.usr_id
where d_in=0 and date(i_ts) like '%${yeardate}%';`;   
    }else if(data.indicator==4){
      var QRY_TO_EXEC = `SELECT count(DISTINCT u.usr_id) as totalusers,SUM(CASE WHEN member_ship_id=1 THEN 1 else 0 End) as platinum,
SUM(CASE WHEN member_ship_id=2 THEN 1 else 0 End) as gold,
SUM(CASE WHEN member_ship_id=3 THEN 1 else 0 End) as silver,
SUM(CASE WHEN member_ship_id=4 THEN 1 else 0 End) as free,
sum(usd_ammount) as totalusdammount,CONCAT('${monthdate}') as year FROM usr_membership_dtl_t m
join (select usr_id from usr_dtl_t where d_in=0 and mail_verification_ind=1 and referred_by='${data.user_id}') u on u.usr_id=m.usr_id
where d_in=0 and date(i_ts) like '%${monthdate}%';`;   
    }else if(data.indicator==5){
      var QRY_TO_EXEC = `SELECT count(DISTINCT u.usr_id) as totalusers,country_nm,SUM(CASE WHEN member_ship_id=1 THEN 1 else 0 End) as platinum,
SUM(CASE WHEN member_ship_id=2 THEN 1 else 0 End) as gold,
SUM(CASE WHEN member_ship_id=3 THEN 1 else 0 End) as silver, 
SUM(CASE WHEN member_ship_id=4 THEN 1 else 0 End) as free,
sum(usd_ammount) as totalusdammount,YEAR(m.i_ts) as year FROM usr_membership_dtl_t m
join (select * from usr_dtl_t where d_in=0 and mail_verification_ind=1 and referred_by='${data.user_id}') u on u.usr_id=m.usr_id
join country_dtl_t c on c.country_id=u.usr_country

where m.d_in=0 group by u.usr_country,year(m.i_ts);`;   
    }else if(data.indicator==6){
      var QRY_TO_EXEC = `SELECT count(DISTINCT u.usr_id) as totalusers,country_nm,SUM(CASE WHEN member_ship_id=1 THEN 1 else 0 End) as platinum,
SUM(CASE WHEN member_ship_id=2 THEN 1 else 0 End) as gold,
SUM(CASE WHEN member_ship_id=3 THEN 1 else 0 End) as silver, 
SUM(CASE WHEN member_ship_id=4 THEN 1 else 0 End) as free,
sum(usd_ammount) as totalusdammount,CONCAT(YEAR(m.i_ts),'-', month(m.i_ts)) as year FROM usr_membership_dtl_t m 
join (select * from usr_dtl_t where d_in=0 and mail_verification_ind=1 and referred_by='${data.user_id}') u on u.usr_id=m.usr_id
join country_dtl_t c on c.country_id=u.usr_country where m.d_in=0 group by u.usr_country,month(m.i_ts),YEAR(m.i_ts);`;   
    }else if(data.indicator==7){
      var QRY_TO_EXEC = `SELECT count(DISTINCT u.usr_id) as totalusers,country_nm,SUM(CASE WHEN member_ship_id=1 THEN 1 else 0 End) as platinum,
SUM(CASE WHEN member_ship_id=2 THEN 1 else 0 End) as gold,
SUM(CASE WHEN member_ship_id=3 THEN 1 else 0 End) as silver,
SUM(CASE WHEN member_ship_id=4 THEN 1 else 0 End) as free,
sum(usd_ammount) as totalusdammount,${yeardate} as year FROM usr_membership_dtl_t m
join (select * from usr_dtl_t where d_in=0 and mail_verification_ind=1 and referred_by='${data.user_id}') u on u.usr_id=m.usr_id
join country_dtl_t c on c.country_id=u.usr_country where m.d_in=0 and date(m.i_ts) like '%${yeardate}%' group by u.usr_country;`;   
    }else if(data.indicator==8){
      var QRY_TO_EXEC = `SELECT count(DISTINCT u.usr_id) as totalusers,country_nm,SUM(CASE WHEN member_ship_id=1 THEN 1 else 0 End) as platinum,
SUM(CASE WHEN member_ship_id=2 THEN 1 else 0 End) as gold,
SUM(CASE WHEN member_ship_id=3 THEN 1 else 0 End) as silver,
SUM(CASE WHEN member_ship_id=4 THEN 1 else 0 End) as free,
sum(usd_ammount) as totalusdammount,CONCAT('${monthdate}') as year FROM usr_membership_dtl_t m 
join (select * from usr_dtl_t where d_in=0 and mail_verification_ind=1 and referred_by='${data.user_id}') u on u.usr_id=m.usr_id
join country_dtl_t c on c.country_id=u.usr_country where m.d_in=0 and date(m.i_ts) like '%${monthdate}%'  group by u.usr_country;`;   
    }
   
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function") {
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    }
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};


exports.postwelcomemessageMdl = function (data,callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in postwelcomemessageMdl";
    if(data.indicator==1){
     var QRY_TO_EXEC = `insert into hscodes_welcomemsg_dtl_t (title,message,created_by,d_in,i_ts) values('${data.title}','${data.message}','${data.created_by}',0,'${date}');`;
     }else if(data.indicator==2){
       var QRY_TO_EXEC = `select * from hscodes_welcomemsg_dtl_t where d_in=0;`; 
    }else if(data.indicator==3){
       var QRY_TO_EXEC = `update hscodes_welcomemsg_dtl_t set d_in=1, d_ts='${date}' where welcomemsg_id='${data.welcomemsg_id}';`; 
    } 
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function") {
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    }
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};


exports.flagsdataMdl = function (data,callback) {
    console.log(data);
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in flagsdataMdl";
    if(data.country_name=='0'){
     var QRY_TO_EXEC = `SELECT * from hscodes_flag_dtl_t where country_code!='0' and d_in=0;`; 
     } else{
         var QRY_TO_EXEC = `SELECT * from hscodes_flag_dtl_t where country_code!='0' and d_in=0 and country_name like '${data.country_name}%';`; 
     }
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function") {
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    }
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};

exports.serviceproviderMdl = function (data,callback) {
    console.log(data);
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in serviceproviderMdl";
    if(data.indicator==1){
     var QRY_TO_EXEC = `SELECT * from hscodes_services_dtl_t where d_in=0;`; 
     } else  if(data.indicator==2){
         var QRY_TO_EXEC = `SELECT * from hscodes_serviceprovider_dtl_t hs
         join country_dtl_t c on c.country_id=hs.provider_country_id
         where provider_country_id='${data.country_id}' and parent_service_id in (${data.service_id}) ORDER BY RAND ( ) LIMIT 100  ;`; 
     } else  if(data.indicator==3){
         var QRY_TO_EXEC = `insert into hscodes_freight_quotation_dtl_t(rq_name,rq_mobile,rq_email,rq_organistation,rq_service,rq_country,rq_type,rq_addl_msg,rq_service_provider_country,rq_usr_id,d_in,i_ts)  values('${data.rfqname}','${data.rfqmobile}','${data.rfqemail}','${data.rfqorganisation}','${data.assignedservice}','${data.rfqcountry_nm}','${data.type}','${data.addl_msg}','${data.assignedcountry}','${data.rfqusr_id}',0,'${date}');`; 
     }
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function") {
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    }
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};


exports.getfreightquotationMdl = function (data,InsertId, callback) { 
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in getfreightquotationMdl"; 
    var QRY_TO_EXEC = ` select * from hscodes_freight_quotation_dtl_t f
    join country_dtl_t c on c.country_nm=f.rq_country where request_quotation_id='${InsertId}' and f.d_in=0;`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC);
};

exports.updtfreightquotationMdl = function (data,rfqreferral,InsertId, callback) { 
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in updtfreightquotationMdl"; 
    var QRY_TO_EXEC = ` update hscodes_freight_quotation_dtl_t  set rq_referral='${rfqreferral}' where request_quotation_id='${InsertId}'`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC);
};
exports.posteditprofileMdl = function (data,img,imgs, callback) {
    var usr_img = ''; 
    if(imgs=='0'){
        var usr_img =``;
    }else{
       var usr_img =`, usr_img = '${imgs}'`; 
    }
    	
    // console.log(usr_img);
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in posteditprofileMdl"; 
    var QRY_TO_EXEC = ` UPDATE usr_dtl_t SET usr_nm = '${data.usr_nm}',usr_address = '${data.usr_address}',  usr_email = '${data.usr_email}',  usr_ph = '${data.usr_ph}',c_export = '${data.c_export}', c_import = '${data.c_import}' ${usr_img}   where usr_id  = '${data.user_id}'`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC);
};


exports.postholdeddataMdl = function (data,callback) {
    console.log(data);
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in postholdeddataMdl"; 
  if(data.indicator==0){
    //   var QRY_TO_EXEC = `update usr_dtl_t set d_in=1, d_ts='${date}', deleted_by='${data.deleted_by}' where usr_id='${data.usr_id}';`;          
    }else if (data.indicator==1){
              var QRY_TO_EXEC = `update hscodes_agents_dtl_t set approve_in=2, u_ts='${date}' where agency_id='${data.agency_id.agency_id}';`;   
    }else if (data.indicator==2){
              var QRY_TO_EXEC = `update hscodes_franchise_dtl_t set approve_in=2, u_ts='${date}' where franchise_id='${data.franchise_id.franchise_id}';`;   
    }else if (data.indicator==3){
            //   var QRY_TO_EXEC = `update lead_dtl_t set d_in=1, d_ts='${date}' where lead_id='${data.lead_id}';`;   
    }
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC);
};



exports.postsupportdataMdl = function (img,data, callback) {
    console.log(data);
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in postsupportdataMdl"; 
    var QRY_TO_EXEC = ` insert into hscodes_support_dtl_t (category,module,priority,description,reportedby,contact,created_by,ticket_url,d_in,i_ts) values('${data.category}','${data.module}','${data.priority}','${data.description}','${data.reportedby}','${data.contact}','${data.created_by}','${img}',0,'${date}');`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC);
};


exports.getsupportdataMdl = function (data, callback) {
    console.log(data);
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in getsupportdataMdl"; 
    if(data.indicator==1){
       var QRY_TO_EXEC = ` select *,CASE
    WHEN status_in=0 THEN 'Pending'
    WHEN status_in=1 THEN 'Work On Progress'
    WHEN status_in=2 THEN 'Completed'
    
    ELSE 'Not Resolved'
END as status from hscodes_support_dtl_t  where d_in=0 order by suppoort_id desc;`; 
    }else if(data.indicator==2){
       var QRY_TO_EXEC = ` select DATE_FORMAT(i_ts,"%Y-%m-%d") as date,count(*) as totalcount, SUM(CASE WHEN status_in = 1 THEN 1 else 0 End) as workonprogress,
SUM(CASE WHEN status_in = 2 THEN 1 else 0 End) as Completed,
SUM(CASE WHEN status_in = 0 THEN 1 else 0 End) as Pending,status_in,count(*) as totalcount from hscodes_support_dtl_t  where d_in=0 group by date`;  
    }else if(data.indicator==3){
       var QRY_TO_EXEC = ` select DATE_FORMAT(i_ts,"%Y-%m-%d") as date,count(*) as totalcount, SUM(CASE WHEN category = 'Admin' THEN 1 else 0 End) as Admin,
SUM(CASE WHEN category = 'Agent' THEN 1 else 0 End) as Agent,
SUM(CASE WHEN category = 'Franchise' THEN 1 else 0 End) as Franchise,
SUM(CASE WHEN category = 'Website' THEN 1 else 0 End) as Website,
SUM(CASE WHEN category = 'Android' THEN 1 else 0 End) as Android,
SUM(CASE WHEN category = 'IOS' THEN 1 else 0 End) as IOS,status_in,count(*) as totalcount,CASE
    WHEN status_in=0 THEN 'Pending'
    WHEN status_in=1 THEN 'Work On Progress'
    WHEN status_in=2 THEN 'Completed'
    
    ELSE 'Not Resolved'
END as status from hscodes_support_dtl_t  where d_in=0 group by status_in;`; 
    }
   
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC);
};



exports.postenquiryMdl = function (data, callback) {
    console.log(data);
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in postenquiryMdl"; 
    var QRY_TO_EXEC = ` insert into hscodes_enquiry_dtl_t (email,mobile,address,subject,message,d_in,i_ts) values('${data.email}','${data.mobile}','${data.address}','${data.subject}','${data.message}',0,'${date}');`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC);
};



exports.getaddhscodeMdl = function (data,insertid, callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in getaddhscodeMdl"; 
    var QRY_TO_EXEC = `SELECT * FROM add_hscodes_dtl_t a join country_dtl_t c on c.country_id=a.country_id where add_hscode_id='${insertid}';`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};
 
exports.updateaddhscodedataMdl = function (data,add_hscode_unique_id,add_hscode_id, callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in updateaddhscodedataMdl"; 
    var QRY_TO_EXEC = `update add_hscodes_dtl_t set add_hscode_unique_id='${add_hscode_unique_id}' where add_hscode_id='${add_hscode_id}';`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};



exports.certifyingagenciesMdl = function (data, callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in certifyingagenciesMdl"; 
    if(data.indicator==1){
      var QRY_TO_EXEC = `select * from hscodes_lead_certifying_agency_dtl_t where d_in=0;`;   
    }else if(data.indicator==2){
      var QRY_TO_EXEC = `insert into hscodes_lead_certifying_agency_dtl_t(lead_certifying_agency_name,lead_certifying_agency_mobile,lead_certifying_agency_email,lead_certifying_agency_country,posted_usr_id) values('${data.rfqname}','${data.rfqmobile}','${data.rfqemail}','${data.rfqcountry_nm}','${data.rfqusr_id}');`;   
    }
   
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};


exports.removechatgroupfromchatMdl = function (data, callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in removechatgroupfromchatMdl";  
      var QRY_TO_EXEC = `update hscodes_chat_dtl_t set d_in=1,d_ts='${date}' where chat_group_nm='${data.hscode}' and chat_hscode='${data.hscode}' and chat_chapter_id='${data.chapter_id}' and chat_country_id='${data.country_id}' and chat_usr_id='${data.usr_id}'`;   
     
   
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};



exports.getrestrictiondataMdl = function (data, callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in getrestrictiondataMdl";  
      var QRY_TO_EXEC = `SELECT * FROM hscodes_limit_restriction_dtl_t where date(subscription_end_dt)>curdate() and user_usage_cnt<max_limit_count and usr_id='${data.usr_id}'`;   
     
   
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};

exports.aprvaddedhscodesMdl = function (table_nm,data, callback) { 
    
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in aprvaddedhscodesMdl"; 
    // SELECT tl,tldesc  from
    var QRY_TO_EXEC = `INSERT INTO  ${table_nm} (tl,tldesc,product_description,i_ts) values('${data.hscode}','${data.description}','${data.product_name}','${date}')`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};

exports.chkaprvaddedhscodesMdl = function (table_nm,data, callback) { 
    
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in chkaprvaddedhscodesMdl"; 
    // SELECT tl,tldesc  from
    var QRY_TO_EXEC = `select * from ${table_nm} where tl='${data.hscode}';`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};


exports.updateaddhscodeMdl = function (data, callback) { 
    
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in updateaddhscodeMdl"; 
    // SELECT tl,tldesc  from
    var QRY_TO_EXEC = `update add_hscodes_dtl_t set approve_in=1, u_ts='${date}' where add_hscode_id='${data.add_hscode_id}';`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};


exports.approverfqMdl = function (data, callback) { 
    
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in approverfqMdl"; 
    // SELECT tl,tldesc  from
    var QRY_TO_EXEC = `update hscodes_rfq_dtl_t set rfq_approve_in=1, u_ts='${date}' where rfq_id='${data.rfq_id}';`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};

exports.getfrqreuesteddataMdl = function (data,callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in getfrqreuesteddataMdl";
    if(data.indicator==1){
    if(data.country_id==0){
    var QRY_TO_EXEC = `SELECT p.*,DATE_FORMAT( p.i_ts,"%d-%m-%Y %T") as inserted_time,CASE WHEN p.frq_approve_in=0 THEN 'Pending' ELSE 'Approved' END as status ,c.country_email,c.continent_email FROM hscodes_freight_quotation_dtl_t p 
        join country_dtl_t c on c.country_nm=p.rq_service_provider_country
    where p.d_in=0;`;
    }else {
       var QRY_TO_EXEC = `SELECT p.*,DATE_FORMAT( p.i_ts,"%d-%m-%Y %T") as inserted_time,CASE WHEN p.frq_approve_in=0 THEN 'Pending' ELSE 'Approved' END as status,c.country_email,c.continent_email FROM hscodes_freight_quotation_dtl_t p 
       join country_dtl_t c on c.country_nm=p.rq_service_provider_country
       where p.d_in=0 and  rq_service_provider_country='${data.country_id}';`;     
    }
      }else if(data.indicator==2){
         var QRY_TO_EXEC = `update hscodes_freight_quotation_dtl_t set d_in=1, d_ts='${date}' where request_quotation_id='${data.request_quotation_id}'`;   
      } else if(data.indicator==3){
         var QRY_TO_EXEC = `update hscodes_freight_quotation_dtl_t set frq_approve_in=1, u_ts='${date}' where request_quotation_id='${data.request_quotation_id}'`;   
      } 
      
      
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function") {
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    }
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};


exports.getemailfrqreuesteddataMdl = function (data, callback) { 
    
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in getemailfrqreuesteddataMdl"; 
    // SELECT tl,tldesc  from
    var QRY_TO_EXEC = `select u.usr_email,c.country_email,c.continent_email from usr_dtl_t u
    join country_dtl_t c on c.country_id=u.usr_country
join (select *
              from usr_membership_dtl_t as q
              inner join
              (
                SELECT max(membership_id) as id FROM usr_membership_dtl_t where curdate() <date(membership_end_date)
                GROUP by usr_id 
              ) m
                on q.membership_id = m.id) um on um.usr_id=u.usr_id where u.d_in=0 order by u.usr_id desc;`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};



exports.editsubscriptionplanMdl = function ( postData, callback) {
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var QRY_TO_EXEC = '';
    var QRY_SUB = '';
    var cntxtDtls = "in editsubscriptionplanMdl"; 
    for (var v = 0; v < postData.length; v++) { 
     
        var QRY_SUB = `update hscodes_membership_desc_dtl_t set silver='${postData[v].silver}',gold='${postData[v].gold}',platinum='${postData[v].platinum}' where description_id='${postData[v].description_id}';`;
        QRY_TO_EXEC = QRY_TO_EXEC + QRY_SUB;
    }
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function") {
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    }
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};


exports.getenquirydataMdl = function (data, callback) { 
    
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in getenquirydataMdl"; 
    // SELECT tl,tldesc  from
    var QRY_TO_EXEC = `select * from hscodes_enquiry_dtl_t where d_in=0;`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};


exports.usreditedbyadminMdl = function (data, callback) { 
    
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in usreditedbyadminMdl"; 
    // SELECT tl,tldesc  from
    var QRY_TO_EXEC = `update usr_dtl_t set usr_nm='${data.usr_nm}',salutation='${data.salutation}',gender='${data.gender}',last_nm='${data.last_nm}',middle_nm='${data.middle_nm}',usr_ph='${data.usr_ph}',usr_email='${data.usr_email}',usr_password='${data.usr_password}',usr_address='${data.usr_address}',usr_continent='${data.usr_continent}',usr_country='${data.usr_country}',usr_state='${data.usr_state}',usr_pincode='${data.usr_pincode}',	std_cd='${data.isd_ph_code}',organisation='${data.organisation}',edited_by='${data.edited_by}',e_ts='${date}',usr_star_rating='${data.usr_star_rating}' where usr_id='${data.usr_id}';`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};



exports.getagentpayoutdataMdl = function (data, callback) { 
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var endOfMonth   = moment(data.indicator).clone().endOf('month').format('YYYY-MM-DD');
    var datecondition='';
    if(data.indicator=='0'){
      var datecondition = `curdate()`;  
    }else{
       var datecondition = `date('${endOfMonth}')` ;
    }
    
    
    var cntxtDtls = "in getagentpayoutdataMdl"; 
    // SELECT tl,tldesc  from
    var QRY_TO_EXEC = `SELECT m.* FROM usr_membership_dtl_t m
join usr_dtl_t u on u.usr_id=m.usr_id where u.referred_by='${data.usr_id}' and ${datecondition}<date(membership_end_date) and member_ship_id!=4;`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};


exports.getfranchisepayoutdataMdl = function (data, callback) { 
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var endOfMonth   = moment(data.indicator).clone().endOf('month').format('YYYY-MM-DD');
    var datecondition='';
    if(data.indicator=='0'){
      var datecondition = `curdate()`;  
    }else{
       var datecondition = `date('${endOfMonth}')` ;
    }
    
    
    var cntxtDtls = "in getfranchisepayoutdataMdl"; 
    // SELECT tl,tldesc  from
    var QRY_TO_EXEC = `SELECT m.* FROM usr_membership_dtl_t m
join usr_dtl_t u on u.usr_id=m.usr_id
join hscodes_dshbrd_usr_dtl_t dsh on dsh.u_id=u.referred_by
where dsh.country_id='${data.country_id}' and ${datecondition}<date(membership_end_date) and member_ship_id!=4;`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};



exports.getfranchiseaccountsdataMdl = function (data, callback) { 
       var yeardate = moment().utcOffset("+05:30").format("YYYY");
    var monthdate = moment().utcOffset("+05:30").format("YYYY-MM"); 
    
    var cntxtDtls = "in getfranchiseaccountsdataMdl"; 
    // SELECT tl,tldesc  from
if(data.indicator==1){
      var QRY_TO_EXEC = `SELECT SUM(CASE WHEN member_ship_id=1 THEN usd_ammount else 0 End) as platinum,
SUM(CASE WHEN member_ship_id=2 THEN usd_ammount else 0 End) as gold,
SUM(CASE WHEN member_ship_id=3 THEN usd_ammount else 0 End) as silver,
sum(usd_ammount) as totalusdammount,YEAR(i_ts) as year FROM usr_membership_dtl_t m
join (select usr_id,referred_by from usr_dtl_t where d_in=0 and mail_verification_ind=1) u on u.usr_id=m.usr_id
join (SELECT u_id,country_id FROM hscodes_dshbrd_usr_dtl_t where d_in=0) dsh on dsh.u_id=u.referred_by

where d_in=0 and dsh.country_id='${data.country_id}' group by YEAR(i_ts);`;   
    }else if(data.indicator==2){
      var QRY_TO_EXEC = `SELECT SUM(CASE WHEN member_ship_id=1 THEN usd_ammount else 0 End) as platinum,
SUM(CASE WHEN member_ship_id=2 THEN usd_ammount else 0 End) as gold,
SUM(CASE WHEN member_ship_id=3 THEN usd_ammount else 0 End) as silver,
sum(usd_ammount) as totalusdammount,CONCAT(YEAR(i_ts),'-', month(i_ts)) as year FROM usr_membership_dtl_t m
join (select usr_id,referred_by from usr_dtl_t where d_in=0 and mail_verification_ind=1) u on u.usr_id=m.usr_id
join (SELECT u_id,country_id FROM hscodes_dshbrd_usr_dtl_t where d_in=0) dsh on dsh.u_id=u.referred_by
      
      where m.d_in=0 and dsh.country_id='${data.country_id}' group by month(i_ts),YEAR(i_ts);`;   
    }else if(data.indicator==3){
      var QRY_TO_EXEC = `   SELECT SUM(CASE WHEN member_ship_id=1 THEN usd_ammount else 0 End) as platinum,
SUM(CASE WHEN member_ship_id=2 THEN usd_ammount else 0 End) as gold,
SUM(CASE WHEN member_ship_id=3 THEN usd_ammount else 0 End) as silver,
sum(usd_ammount) as totalusdammount,${yeardate} as year FROM usr_membership_dtl_t m
join (select usr_id,referred_by from usr_dtl_t where d_in=0 and mail_verification_ind=1) u on u.usr_id=m.usr_id
join (SELECT u_id,country_id FROM hscodes_dshbrd_usr_dtl_t where d_in=0) dsh on dsh.u_id=u.referred_by
where d_in=0 and dsh.country_id='${data.country_id}' and date(i_ts) like '%${yeardate}%';`;   
    }else if(data.indicator==4){
      var QRY_TO_EXEC = `SELECT SUM(CASE WHEN member_ship_id=1 THEN usd_ammount else 0 End) as platinum,
SUM(CASE WHEN member_ship_id=2 THEN usd_ammount else 0 End) as gold,
SUM(CASE WHEN member_ship_id=3 THEN usd_ammount else 0 End) as silver,
sum(usd_ammount) as totalusdammount,CONCAT('${monthdate}') as year FROM usr_membership_dtl_t m
join (select usr_id,referred_by from usr_dtl_t where d_in=0 and mail_verification_ind=1 ) u on u.usr_id=m.usr_id
join (SELECT u_id,country_id FROM hscodes_dshbrd_usr_dtl_t where d_in=0) dsh on dsh.u_id=u.referred_by
where d_in=0 and dsh.country_id='${data.country_id}' and date(i_ts) like '%${monthdate}%';`;   
    }else if(data.indicator==5){
      var QRY_TO_EXEC = `SELECT country_nm,SUM(CASE WHEN member_ship_id=1 THEN usd_ammount else 0 End) as platinum,
SUM(CASE WHEN member_ship_id=2 THEN usd_ammount else 0 End) as gold,
SUM(CASE WHEN member_ship_id=3 THEN usd_ammount else 0 End) as silver,
sum(usd_ammount) as totalusdammount,YEAR(m.i_ts) as year FROM usr_membership_dtl_t m
join (select * from usr_dtl_t where d_in=0 and mail_verification_ind=1 ) u on u.usr_id=m.usr_id
join country_dtl_t c on c.country_id=u.usr_country
join (SELECT u_id,country_id FROM hscodes_dshbrd_usr_dtl_t where d_in=0) dsh on dsh.u_id=u.referred_by

where m.d_in=0 and dsh.country_id='${data.country_id}' group by u.usr_country,year(m.i_ts);`;   
    }else if(data.indicator==6){
      var QRY_TO_EXEC = `SELECT country_nm,SUM(CASE WHEN member_ship_id=1 THEN usd_ammount else 0 End) as platinum,
SUM(CASE WHEN member_ship_id=2 THEN usd_ammount else 0 End) as gold,
SUM(CASE WHEN member_ship_id=3 THEN usd_ammount else 0 End) as silver,
sum(usd_ammount) as totalusdammount,CONCAT(YEAR(m.i_ts),'-', month(m.i_ts)) as year FROM usr_membership_dtl_t m 
join (select * from usr_dtl_t where d_in=0 and mail_verification_ind=1) u on u.usr_id=m.usr_id
join country_dtl_t c on c.country_id=u.usr_country 
join (SELECT u_id,country_id FROM hscodes_dshbrd_usr_dtl_t where d_in=0) dsh on dsh.u_id=u.referred_by
where m.d_in=0 and dsh.country_id='${data.country_id}' group by u.usr_country,month(m.i_ts),YEAR(m.i_ts);`;   
    }else if(data.indicator==7){
      var QRY_TO_EXEC = `SELECT country_nm,SUM(CASE WHEN member_ship_id=1 THEN usd_ammount else 0 End) as platinum,
SUM(CASE WHEN member_ship_id=2 THEN usd_ammount else 0 End) as gold,
SUM(CASE WHEN member_ship_id=3 THEN usd_ammount else 0 End) as silver,
sum(usd_ammount) as totalusdammount,${yeardate} as year FROM usr_membership_dtl_t m
join (select * from usr_dtl_t where d_in=0 and mail_verification_ind=1) u on u.usr_id=m.usr_id
join country_dtl_t c on c.country_id=u.usr_country 
join (SELECT u_id,country_id FROM hscodes_dshbrd_usr_dtl_t where d_in=0) dsh on dsh.u_id=u.referred_by
where m.d_in=0 and dsh.country_id='${data.country_id}' and date(m.i_ts) like '%${yeardate}%' group by u.usr_country;`;   
    }else if(data.indicator==8){
      var QRY_TO_EXEC = `SELECT country_nm,SUM(CASE WHEN member_ship_id=1 THEN usd_ammount else 0 End) as platinum,
SUM(CASE WHEN member_ship_id=2 THEN usd_ammount else 0 End) as gold,
SUM(CASE WHEN member_ship_id=3 THEN usd_ammount else 0 End) as silver,
sum(usd_ammount) as totalusdammount,CONCAT('${monthdate}') as year FROM usr_membership_dtl_t m 
join (select * from usr_dtl_t where d_in=0 and mail_verification_ind=1) u on u.usr_id=m.usr_id
join country_dtl_t c on c.country_id=u.usr_country 
join (SELECT u_id,country_id FROM hscodes_dshbrd_usr_dtl_t where d_in=0) dsh on dsh.u_id=u.referred_by
where m.d_in=0 and dsh.country_id='${data.country_id}' and date(m.i_ts) like '%${monthdate}%'  group by u.usr_country;`;   
    };
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};


exports.getagentslabcommdataMdl = function (data, callback) { 
    
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in getagentslabcommdataMdl"; 
    // SELECT tl,tldesc  from
    if(data.indicator==1){
 var QRY_TO_EXEC = `SELECT * FROM hscodes_agent_slab_comm_dtl_t where d_in=0;`;  
    }else{
        
  var QRY_TO_EXEC = '';
    var QRY_SUB = ''; 
    for (var v = 0; v < data.obj.length; v++) { 
     
        var QRY_SUB = `update hscodes_agent_slab_comm_dtl_t set agent_sub_cnt_1='${data.obj[v].agent_sub_cnt_1}',agent_sub_cnt_2='${data.obj[v].agent_sub_cnt_2}',agent_slab_commision='${data.obj[v].agent_slab_commision}',u_ts='${date}' where agent_target_sub_id='${data.obj[v].agent_target_sub_id}';`;
        QRY_TO_EXEC = QRY_TO_EXEC + QRY_SUB;
    }
    }
    
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};


exports.agenteditbyadminMdl = function (data, callback) { 
    console.log(data);
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in agenteditbyadminMdl"; 
    // SELECT tl,tldesc  from
    var QRY_TO_EXEC = `update hscodes_agents_dtl_t set name='${data.name}',mobile='${data.mobile}',email='${data.email}',qualification='${data.qualification}',curreuntly_pursuing='${data.curreuntly_pursuing}',nationality='${data.nationality}',countri='${data.countri}',state='${data.state}',planning_at_continent='${data.planning_at_continent}',planning_at_country='${data.planning_at_country}',planning_at_state='${data.planning_at_state}',referal_program_benefits='${data.referal_program_benefits}',languages_speak='${data.languages_speak}',languages_write='${data.languages_write}',languages_read='${data.languages_read}',area_brand_ambassador='${data.area_brand_ambassador}',exports_impports_bckgrnd='${data.exports_impports_bckgrnd}',sales_promotional_activities='${data.sales_promotional_activities}',know_abous_us='${data.know_abous_us}',areas_think_useful='${data.areas_think_useful}',gndr='${data.gndr}',edit_by='${data.edit_by}',e_ts='${date}' where agency_id='${data.agency_id}';`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};


exports.franchiseeditbyadmin2Mdl = function (data, callback) { 
    console.log(data);
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in franchiseeditbyadmin2Mdl"; 
    // SELECT tl,tldesc  from
    var QRY_TO_EXEC = `update hscodes_franchise_dtl_t set company_nm='${data.company_nm}',business_background='${data.business_background}',contact_person='${data.usr_nm}',franchise_saltation='${data.salutation}',contact='${data.contact}',email_id='${data.email_id}',kind_of_business='${data.kind_of_business}',website='${data.website}',ownership_type='${data.ownership_type}',insfra_description='${data.insfra_description}',willing_to_setup_infra='${data.willing_to_setup_infra}',annual_turnover='${data.annual_turnover}',franchise_currency='${data.franchise_currency}',remarks='${data.remarks}',franchise_gender='${data.gender}',fr_edit_by='${data.edit_by}',e_ts='${date}' where franchise_id='${data.franchise_id}';`;
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};


exports.getfranchiseusersMdl = function (data, callback) { 
    console.log(data);
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in getfranchiseusersMdl"; 
 if(data.country_id==0){
      var QRY_TO_EXEC = `select * from usr_dtl_t u
        left join continent_dtl_t c on c.continent_id=u.usr_continent
LEFT join country_dtl_t cntry on cntry.country_id=u.usr_country
left join hscodes_state_dtl_t s on s.state_id=u.usr_state where u.d_in=0 and referred_by='${data.usr_id}' and mail_verification_ind=1;SELECT * FROM usr_membership_dtl_t where curdate() <date(membership_end_date) order by member_ship_id desc`;   
    }else{
        var QRY_TO_EXEC = `select * from usr_dtl_t u
        left join continent_dtl_t c on c.continent_id=u.usr_continent
LEFT join country_dtl_t cntry on cntry.country_id=u.usr_country
left join hscodes_state_dtl_t s on s.state_id=u.usr_state where u.d_in=0 and usr_country='${data.country_id}' and 	mail_verification_ind=1;SELECT um.* FROM usr_membership_dtl_t um 
join usr_dtl_t u on u.usr_id=um.usr_id where curdate() <date(membership_end_date) and u.d_in=0 and usr_country='${data.country_id}' order by member_ship_id desc;`;
    }
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};


exports.geteximdataMdl = function (data, callback) { 
    console.log(data);
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in geteximdataMdl"; 
 if(data.indicator==1){
      var QRY_TO_EXEC = `select t.*,country_nm from hscodes_top_exports_imports_dtl_t t
      join country_dtl_t c on c.country_id=t.country_indicator
      where t.d_in=0 and product_type='${data.type}' and country_indicator='${data.country_id}';`;   
    }else if(data.indicator==2){
      var QRY_TO_EXEC = `select t.*,country_nm from hscodes_top_exports_imports_dtl_t t
      join country_dtl_t c on c.country_id=t.country_indicator
      where t.d_in=0 and product_type='Import' and country_indicator='${data.country_id}';select t.*,country_nm from hscodes_top_exports_imports_dtl_t t
      join country_dtl_t c on c.country_id=t.country_indicator
      where t.d_in=0 and product_type='Export' and country_indicator='${data.country_id}';`;   
    }
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};



exports.postappdemovideosMdl = function (data, callback) { 
    console.log(data);
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in postappdemovideosMdl"; 
 if(data.indicator==1){
      var QRY_TO_EXEC = `insert into hscodes_appdemo_video_dtl_t(video_heading,video_url,d_in,i_ts) values('${data.video_heading}','${data.video_url}',0,'${date}');`;   
    }else if(data.indicator==2){
      var QRY_TO_EXEC = `select * from hscodes_appdemo_video_dtl_t where d_in=0;`;   
    }else if(data.indicator==3){
      var QRY_TO_EXEC = `update hscodes_appdemo_video_dtl_t set d_in=1,d_ts='${date}' where d_in=0;`;   
    }
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};


exports.createinternalloginsMdl = function (data, callback) { 
    console.log(data);
    var condition='';
    var countrycondition='';
    if(data.role=='CENTRAL ADMIN'){
        condition=`0,0`;
    }else if (data.role=='CONTINENT ADMIN'){
         condition=`'${data.usr_continent}',0`;
    }else if(data.role=='COUNTRY ADMIN'){
          condition=`'${data.usr_continent}','${data.usr_country}'`;
    }else if(data.role=='FRANCHISE'){
         condition=`'${data.usr_continent}','${data.usr_country}'`;
    }
    
    if(data.country_id==0){
         countrycondition=``;
    }else{
         countrycondition=` and d.country_id='${data.country_id}' `;
    }
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in createinternalloginsMdl";  
    if(data.indicator==1){
     var QRY_TO_EXEC = `insert into hscodes_dshbrd_usr_dtl_t(uname,upass,i_ts,continent_id,country_id,role,login_created_by) values('${data.usr_email}','${data.usr_email}','${date}',${condition},'${data.role}','${data.created_by}');`;      
    }else  if(data.indicator==2){
     var QRY_TO_EXEC = `select d.*,continent_nm,country_nm from hscodes_dshbrd_usr_dtl_t d
     join continent_dtl_t con on con.continent_id=d.continent_id
     join country_dtl_t c on c.country_id=d.country_id where d.d_in=0 ${countrycondition};`;      
    }else  if(data.indicator==2){
     var QRY_TO_EXEC = `update hscodes_dshbrd_usr_dtl_t set d_in=1,d_ts='${date}' where u_id='${data.u_id}';`;      
    }
      
 
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};



exports.bulkemailsenderMdl = function (data, callback) { 
    console.log("venki",data);
    var rolecndtn='';
    var leftjoincndtn='';
     var continentcndtn='';
      var countrycndtn='';
      var dshcontinentcndtn='';
      var dshcountrycndtn='';
       var statecndtn='';
        var chaptercndtn='';
        
    if(data.usr_continent=='0' || data.usr_continent=='' || data.usr_continent==undefined || data.usr_continent=='undefined'){
        continentcndtn=``;
    }else{
         continentcndtn=` and u.usr_continent='${data.usr_continent}' `;
         dshcontinentcndtn=` and continent_id='${data.usr_continent}' `;
    }
    if (data.usr_country=='0' || data.usr_country=='' || data.usr_country==undefined || data.usr_country=='undefined'){
         countrycndtn=``;
    }else {
        countrycndtn=` and u.usr_country='${data.usr_country}' `;
        dshcountrycndtn=` and country_id='${data.usr_country}' `;
    } 
    if (data.usr_state=='0' || data.usr_state=='' || data.usr_state==undefined || data.usr_state=='undefined'){
         statecndtn=``;
    }else {
        statecndtn=` and u.usr_state='${data.usr_state}' `;
    }
     if (data.chapter=='0' || data.chapter=='' || data.chapter==undefined || data.chapter=='undefined'){
         chaptercndtn=``;
         leftjoincndtn=`left `;
         chapterexpirycndtn=``;
    }else {
        chaptercndtn=` and p.chapter_id='${data.chapter}'  `;
        chapterexpirycndtn=` and date(expiry_Date)>curdate() `;
    }
    
     
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in bulkemailsenderMdl"; 
 if(data.indicator==1){
     if(data.role=='USER'){
      var QRY_TO_EXEC = `SELECT u.usr_id,usr_email,usr_nm from usr_dtl_t u
${leftjoincndtn} join hscodes_purchase_chapter_dtl_t p on p.usr_id=u.usr_id
where u.d_in=0 ${continentcndtn} ${countrycndtn}  ${statecndtn} ${chaptercndtn} ${chapterexpirycndtn}  group by u.usr_id;`; 
}else{
       var QRY_TO_EXEC = `SELECT u_id as usr_id,uname as usr_email,uname as usr_nm FROM hscodes_dshbrd_usr_dtl_t where d_in=0 and role='${data.role}'  ${dshcontinentcndtn} ${dshcountrycndtn};`; 
}
    }else if(data.indicator==2){
      var QRY_TO_EXEC = `select * from hscodes_appdemo_video_dtl_t where d_in=0;`;   
    }else if(data.indicator==3){
      var QRY_TO_EXEC = `update hscodes_appdemo_video_dtl_t set d_in=1,d_ts='${date}' where d_in=0;`;   
    }
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};


exports.bannerdataMdl = function (data, callback) { 
    console.log(data); 
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in bannerdataMdl";  
    if(data.indicator==1){
         var QRY_TO_EXEC = `select * from hscodes_banner_dtl_t where d_in=0 order by banner_id desc ;`;      
    }else if(data.indicator==2){
         var QRY_TO_EXEC = `insert into hscodes_banner_dtl_t (banner_nm,i_ts,d_in) values ('${data.message}','${date}',0);`;      
    }else if(data.indicator==3){
         var QRY_TO_EXEC = `update hscodes_banner_dtl_t set d_in=1 , d_ts='${date}' where banner_id='${data.banner_id}';`;      
    }else if(data.indicator==4){
         var QRY_TO_EXEC = `select * from hscodes_version_dtl_t where d_in=0;`;      
    } 
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};


exports.postleademailauthenticationMdl = function (data,otp, callback) { 
    console.log(data); 
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in postleademailauthenticationMdl";  
    if(data.indicator==1){
         var QRY_TO_EXEC = `update usr_dtl_t set otp='${otp}' where usr_id='${data.usr_id}' ;`;      
    }else if(data.indicator==2){
         var QRY_TO_EXEC = `select * from usr_dtl_t where otp='${data.otp}' and usr_id='${data.usr_id}' and d_in=0;`;      
    }
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};


exports.leadeditedbyadminMdl = function (data,img, callback) { 
    console.log(data); 
    var date = moment().utcOffset("+05:30").format("YYYY-MM-DD HH:mm:ss");
    var cntxtDtls = "in leadeditedbyadminMdl";   
         var QRY_TO_EXEC = `update lead_dtl_t set lead_type='${data.lead_type}',lead_category='${data.lead_category}',lead_chapter='${data.lead_chapter}',lead_hscode='${data.lead_hscode}',lead_hscode_desc='${data.lead_hscode_desc}',description='${data.description}',lead_quantity='${data.lead_quantity}',lead_unit_of_measurement='${data.lead_unit_of_measurement}',lead_currency='${data.lead_currency}',lead_unit_price='${data.lead_unit_price}',lead_continent='${data.lead_continent}',lead_country='${data.lead_country}',loading_port_country='${data.loading_port_country}',loading_port_type='${data.loading_port_type}',loading_port='${data.loading_port}',destination_port_country='${data.destination_port_country}',destination_port_type='${data.destination_port_type}',destination_port='${data.destination_port}',lead_price_model='${data.lead_price_model}',lead_expiry_date='${data.lead_expiry_date}',lead_certifying_agency='${data.lead_certifying_agency}',lead_remarks='${data.lead_remarks}',special_instruction='${data.special_instruction}',lead_business_address='${data.lead_business_address}',upload1='${img}',lead_edited_by='${data.lead_edited_by}',e_ts='${date}' where lead_id='${data.lead_id}';`;      
    console.log(QRY_TO_EXEC);
    if (callback && typeof callback == "function")
        dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls, function (err, results) {
            callback(err, results);
            return;
        });
    else
        return dbutil.execQuery(sqldb.MySQLConPool, QRY_TO_EXEC, cntxtDtls);
};
