"use strict";
Object.defineProperty(exports, "__esModule", { value: true });
exports.CommonClass = void 0;
const APIBase_1 = require("./APIBase");
const Session_1 = require("../lib/model/Session");
const AppRoute_1 = require("../lib/AppRoute");
const _406_1 = require("../lib/view/406");
const RawQuery_1 = require("../lib/model/RawQuery");
const RawView_1 = require("../lib/view/RawView");
const RawNonQuery_1 = require("../lib/model/RawNonQuery");
const ModelCsvUpload_1 = require("../lib/model/ModelCsvUpload");
const fs = require('fs');
const path = require('path');
class CommonClass extends AppRoute_1.AppRoute {
    constructor() {
        super();
    }
    /**
     * To filter the CDR report
     */
    getCdrFilter(req, res, next) {
        let qdata = req.query.type;
        var sdata = JSON.parse(qdata);
        let session = new Session_1.SessionManagment(req, res, next);
        session.GetSession((error, sessdata) => {
            if (error == 1) {
                let idaccount = typeof sdata.iduser != 'undefined' ? sdata.iduser : 0;
                let iQry = "", iQN1 = 0, iQN = 0, cond = "";
                iQry = "SELECT san,cost, dialed_number, caller_id_number, direction, duration, billsec, hangup_cause, progress_mediamsec, CONVERT_TZ(start_stamp, '" + sessdata.server_time + "', '" + sessdata.client_time + "') start_stamp, CONVERT_TZ(answer_stamp, '" + sessdata.server_time + "', '" + sessdata.client_time + "') answer_stamp, CONVERT_TZ(end_stamp, '" + sessdata.server_time + "', '" + sessdata.client_time + "') end_stamp, CONVERT_TZ(progress_media_stamp, '" + sessdata.server_time + "', '" + sessdata.client_time + "') progress_media_stamp FROM `cdr` \n";
                if (sessdata.type == 1) {
                    if (idaccount) {
                        iQry += "WHERE iduser IN (SELECT iduser FROM `user` WHERE idaccount=(SELECT idaccount FROM `user` WHERE iduser='" + idaccount + "')) ";
                        iQN1++;
                    }
                }
                else if (sessdata.type == 2) {
                    iQry += "WHERE iduser IN (SELECT iduser FROM `user` WHERE idaccount=(SELECT idaccount FROM `user` WHERE iduser='" + sessdata.iduser + "'))";
                    iQN1++;
                }
                else if (sessdata.type == 22) {
                    iQry += "WHERE iduser=" + sessdata.iduser + " ";
                    iQN1++;
                }
                /**
                 * Other parameters
                 */
                if (sdata.from_date != undefined && sdata.from_date && !sdata.to_date) {
                    cond += "AND start_stamp>=CONVERT_TZ('" + sdata.from_date + " 00:00:00', '" + sessdata.client_time + "', '" + sessdata.server_time + "') ";
                    iQN++;
                }
                if (sdata.to_date != undefined && sdata.to_date && !sdata.from_date) {
                    cond += "AND start_stamp<=CONVERT_TZ('" + sdata.to_date + " 23:59:59', '" + sessdata.client_time + "', '" + sessdata.server_time + "') ";
                    iQN++;
                }
                if (sdata.from_date && sdata.to_date) {
                    cond += "AND start_stamp>=CONVERT_TZ('" + sdata.from_date + " 00:00:00', '" + sessdata.client_time + "', '" + sessdata.server_time + "') AND start_stamp<=CONVERT_TZ('" + sdata.to_date + " 23:59:59', '" + sessdata.client_time + "', '" + sessdata.server_time + "') ";
                    iQN++;
                }
                if (sdata.from_number != undefined && sdata.from_number) {
                    cond += "AND caller_id_number LIKE '%" + sdata.from_number + "' ";
                    iQN++;
                }
                if (sdata.to_number != undefined && sdata.to_number) {
                    cond += "AND dialed_number LIKE '%" + sdata.to_number + "' ";
                    iQN++;
                }
                if (sdata.direction != undefined && sdata.direction) {
                    cond += "AND direction='" + sdata.direction + "' ";
                    iQN++;
                }
                if (iQN1 > 0) {
                    if (iQN > 0) {
                        iQry += cond;
                    }
                    iQry = iQry.slice(0, -1) + "\n";
                }
                else {
                    if (iQN > 0) {
                        iQry += "WHERE " + cond.substr(4, cond.length);
                        iQry = iQry.slice(0, -1) + "\n";
                    }
                }
                iQry += "ORDER BY `start_stamp` DESC LIMIT 2000;";
                let obj = new RawNonQuery_1.ModelRawNonQuery(req, res);
                obj.nonqrysql = iQry;
                obj.prepare();
                obj.execute((_error, result) => {
                    if (_error == 1) {
                        let objv = new RawView_1.RawView(res);
                        objv.prepare({
                            data: result,
                            message: "CDR Fetched Successfully.",
                            status: 200
                        });
                        objv.execute();
                    }
                    else {
                        let objv = new RawView_1.RawView(res);
                        objv.prepare({
                            data: result,
                            message: "Something Went Wrong, Try Again!",
                            status: 502
                        });
                        objv.execute();
                    }
                });
            }
            else {
                let objv = new _406_1.Res406(res);
                objv.prepare("No session data there");
                objv.execute();
            }
        });
    }
    summeryCdrForm(req, res, next) {
        let sdata = req.query;
        let data = JSON.parse(sdata.type);
        let condition = "";
        let Type = "";
        let session = new Session_1.SessionManagment(req, res, next);
        session.GetSession((error, sessdata) => {
            if (error == 1) {
                let obj2 = new RawQuery_1.ModelRawQuery(req, res);
                if (sessdata.type == 1) {
                    Type = "";
                }
                else {
                    Type = "AND `iduser`='" + sessdata.iduser + "'";
                }
                if (data.from_date && data.to_date) {
                    condition += "DATE_FORMAT(start_stamp,'%Y-%m-%d') BETWEEN '" + data.from_date + "' AND '" + data.to_date + "'" + Type + "";
                }
                let Qry;
                let Qry1;
                let Qry2;
                Qry1 = "(SELECT DATE_FORMAT(start_stamp,'%Y-%m-%d')`date`, direction,COUNT(*)Total_calls,SUM(CASE WHEN hangup_cause='NORMAL_CLEARING' THEN  1 ELSE 0 END) AS 'success', SUM(CASE WHEN hangup_cause='CALL_REJECTED' THEN 1 ELSE 0 END )AS 'failed', SUM(cost)`total_cost`,SUM(duration) duration FROM cdr WHERE direction='inbound' AND " + condition + " GROUP BY DATE_FORMAT(start_stamp,'%Y-%m-%d'))";
                Qry2 = "(SELECT DATE_FORMAT(start_stamp,'%Y-%m-%d')`date`,direction,COUNT(*)Total_calls,SUM(CASE WHEN hangup_cause='NORMAL_CLEARING' THEN 1 ELSE 0 END) AS 'success', SUM(CASE WHEN hangup_cause='CALL_REJECTED' THEN 1 ELSE 0 END )AS 'failed', SUM(cost)`total_cost`,SUM(duration) duration FROM cdr WHERE direction='outbound' AND " + condition + " GROUP BY DATE_FORMAT(start_stamp,'%Y-%m-%d'))";
                if (data.directionSummery == "Inbound") {
                    Qry = Qry1;
                }
                if (data.directionSummery == "Outbound") {
                    Qry = Qry2;
                }
                if (data.directionSummery == '' || data.directionSummery == null || data.directionSummery == undefined || data.directionSummery == "Both") {
                    Qry = Qry1 + " UNION " + Qry2;
                }
                obj2.qrysql = Qry;
                obj2.prepare();
                obj2.execute((_error, result) => {
                    if (_error == 1) {
                        let objv = new RawView_1.RawView(res);
                        objv.prepare({ message: "Data Fetched Successfully!", data: result, status: 200 });
                        objv.execute();
                    }
                    else {
                        let objv = new RawView_1.RawView(res);
                        objv.prepare({ message: "Something Went Wrong With Data Fetch!", status: 502 });
                        objv.execute();
                    }
                });
            }
            else {
                let objv = new RawView_1.RawView(res);
                objv.prepare({ message: "Invalid Session Trying to Access", status: 401 });
                objv.execute();
            }
        });
    }
    /**
     * To filter the MDR report
     */
    getMDRFilter(req, res, next) {
        let qdata = req.query.type;
        var sdata = JSON.parse(qdata);
        let session = new Session_1.SessionManagment(req, res, next);
        session.GetSession((error, sessdata) => {
            if (error == 1) {
                let idaccount = typeof sdata.iduser != 'undefined' ? sdata.iduser : 0;
                let iQry = "", iQN1 = 0, iQN = 0, cond = "";
                iQry = "SELECT `idmdr`, direction, msg_count, from_number, to_number, MT_rates, MO_rates, MT_surcharge, `status`, message_delivered_to_operator, message_accepted_by_signalmash, delivery_receipt_received_by_singnalmash, delivery_receipt_delivered, mobility";
                if (sessdata.type == 1) {
                    iQry += ",messsage_body";
                }
                iQry += ", ocn, lata, CONVERT_TZ(created_at, '" + sessdata.server_time + "', '" + sessdata.client_time + "') created_at, (\n";
                iQry += "\tmsg_count*IFNULL(MO_rates,0)+msg_count*IFNULL(MT_rates,0)+msg_count*IFNULL(MT_surcharge,0)+msg_count*IFNULL(MO_surcharge,0)\n";
                iQry += ")`cost` FROM `mdr`\n";
                if (sessdata.type == 1) {
                    if (idaccount) {
                        iQry += "WHERE idaccount=" + idaccount + " ";
                        iQN1++;
                    }
                }
                else if (sessdata.type == 2) {
                    iQry += "WHERE idaccount=" + sessdata.idaccount + " ";
                    iQN1++;
                }
                else if (sessdata.type == 22) {
                    iQry += "WHERE iduser=" + sessdata.iduser + " ";
                    iQN1++;
                }
                /**
                 * Other parameters
                 */
                if (sdata.from_date != undefined && sdata.from_date) {
                    cond += "AND created_at>=CONVERT_TZ('" + sdata.from_date + " 00:00:00', '" + sessdata.client_time + "', '" + sessdata.server_time + "') ";
                    iQN++;
                }
                if (sdata.to_date != undefined && sdata.to_date) {
                    cond += "AND created_at<=CONVERT_TZ('" + sdata.to_date + " 23:59:59', '" + sessdata.client_time + "', '" + sessdata.server_time + "') ";
                    iQN++;
                }
                if (sdata.from_number != undefined && sdata.from_number) {
                    cond += "AND from_number='" + sdata.from_number + "' ";
                    iQN++;
                }
                if (sdata.to_number != undefined && sdata.to_number) {
                    cond += "AND to_number='" + sdata.to_number + "' ";
                    iQN++;
                }
                if (sdata.direction != undefined && sdata.direction) {
                    cond += "AND direction='" + sdata.direction + "' ";
                    iQN++;
                }
                if (iQN1 > 0) {
                    if (iQN > 0) {
                        iQry += cond;
                    }
                    iQry = iQry.slice(0, -1) + "\n";
                }
                else {
                    if (iQN > 0) {
                        iQry += "WHERE " + cond.substr(4, cond.length);
                        iQry = iQry.slice(0, -1) + "\n";
                    }
                }
                iQry += "ORDER BY `created_at` DESC;";
                let obj = new RawNonQuery_1.ModelRawNonQuery(req, res);
                obj.nonqrysql = iQry;
                // let condition: any = "";
                // if (sdata.from_date && sdata.to_date) {
                // 	condition += '`created_at` BETWEEN "' + sdata.from_date + ' 00:00:00" AND "' + sdata.to_date + ' 23:59:59" ';
                // }
                // if (sdata.direction) {
                // 	condition += 'AND `direction`="' + sdata.direction + '" ';
                // }
                // if (sdata.to_number) {
                // 	condition += 'AND `to_number`="' + sdata.to_number + '" ';
                // }
                // if (sdata.from_number) {
                // 	condition += ' AND `from_number`="' + sdata.from_number + '"';
                // }
                // console.log(condition);
                // if (sessdata.type == 1) {
                // 	if (idaccount) {
                // 		obj.nonqrysql = "SELECT *, (msg_count*IFNULL(MO_rates,0)+msg_count*IFNULL(MT_rates,0)+msg_count*IFNULL(MT_surcharge,0)+msg_count*IFNULL(MO_surcharge,0)) `cost` FROM `mdr` WHERE `idaccount`='" + idaccount + "' AND " + condition + " ORDER BY STR_TO_DATE(`created_at`,'%Y-%m-%d') DESC";
                // 	}
                // 	else {
                // 		obj.nonqrysql = "SELECT *, (msg_count*IFNULL(MO_rates,0)+msg_count*IFNULL(MT_rates,0)+msg_count*IFNULL(MT_surcharge,0)+msg_count*IFNULL(MO_surcharge,0)) `cost` FROM `mdr` WHERE " + condition + " ORDER BY STR_TO_DATE(`created_at`,'%Y-%m-%d') DESC LIMIT 20";
                // 	}
                // }
                // else if (sessdata.type == 2) {
                // 	obj.nonqrysql = "SELECT *, (msg_count*IFNULL(MO_rates,0)+msg_count*IFNULL(MT_rates,0)+msg_count*IFNULL(MT_surcharge,0)+msg_count*IFNULL(MO_surcharge,0)) `cost` FROM `mdr` WHERE `idaccount`='" + sessdata.idaccount + "' AND " + condition + " ORDER BY STR_TO_DATE(`created_at`,'%Y-%m-%d') DESC";
                // }
                // else if (sessdata.type == 22) {
                // 	obj.nonqrysql = "SELECT *, (msg_count*IFNULL(MO_rates,0)+msg_count*IFNULL(MT_rates,0)+msg_count*IFNULL(MT_surcharge,0)+msg_count*IFNULL(MO_surcharge,0)) `cost` FROM `mdr` WHERE `iduser`='" + sessdata.iduser + "' AND " + condition + " ORDER BY STR_TO_DATE(`created_at`,'%Y-%m-%d') DESC";
                // }
                obj.prepare();
                obj.execute((_error, result) => {
                    if (_error == 1) {
                        let objv = new RawView_1.RawView(res);
                        objv.prepare({
                            data: result,
                            message: "MDR Fetched Successfully.",
                            status: 200
                        });
                        objv.execute();
                    }
                    else {
                        let objv = new RawView_1.RawView(res);
                        objv.prepare({
                            data: result,
                            message: "Something Went Wrong, Try Again!",
                            status: 502
                        });
                        objv.execute();
                    }
                });
            }
            else {
                let objv = new _406_1.Res406(res);
                objv.prepare(session);
                objv.execute();
            }
        });
    }
    summeryMDRFilter(req, res, next) {
        let sdata = req.query;
        let data = JSON.parse(sdata.type);
        let condition = "";
        let Type = "";
        let session = new Session_1.SessionManagment(req, res, next);
        session.GetSession((error, sessdata) => {
            if (error == 1) {
                let obj2 = new RawQuery_1.ModelRawQuery(req, res);
                if (sessdata.type == 1) {
                    Type = "";
                }
                else {
                    Type = "AND `iduser`='" + sessdata.iduser + "'";
                }
                if (data.from_date && data.to_date) {
                    condition += "DATE_FORMAT(created_at,'%Y-%m-%d') BETWEEN '" + data.from_date + "' AND '" + data.to_date + "' " + Type + " ";
                }
                if (data.directionSummery == "Inbound") {
                    condition += "AND direction='inbound' ";
                }
                if (data.directionSummery == "Outbound") {
                    condition += "AND direction='Outbound' ";
                }
                if (data.directionSummery == "Both") {
                    condition += "";
                }
                let Qry;
                Qry = "SELECT DATE_FORMAT(created_at,'%Y-%m-%d')`date`, direction , CASE WHEN direction='inbound' THEN COUNT(messsage_body) WHEN direction='outbound' THEN COUNT(messsage_body)ELSE 0 END`total_message` ,CASE direction WHEN'outbound' THEN SUM(MT_rates) WHEN 'inbound' THEN SUM(MT_rates) END `total_cost`,`STATUS` FROM mdr WHERE " + condition + " GROUP BY direction,DATE_FORMAT(created_at,'%Y-%m-%d')";
                obj2.qrysql = Qry;
                obj2.prepare();
                obj2.execute((_error, result) => {
                    if (_error == 1) {
                        let objv = new RawView_1.RawView(res);
                        objv.prepare({ message: "Data Fetched Successfully!", data: result, status: 200 });
                        objv.execute();
                    }
                    else {
                        let objv = new RawView_1.RawView(res);
                        objv.prepare({ message: "Something Went Wrong With Data Fetch!", status: 502 });
                        objv.execute();
                    }
                });
            }
            else {
                let objv = new RawView_1.RawView(res);
                objv.prepare({ message: "Invalid Session Trying to Access", status: 401 });
                objv.execute();
            }
        });
    }
    CsvInvoice(req, res, next) {
        let sdata = req.query;
        let session = new Session_1.SessionManagment(req, res, next);
        session.GetSession((err, sessdata) => {
            if (err == 1) {
                let obj = new RawQuery_1.ModelRawQuery(req, res);
                if (sdata.selectedUser != 'null' && sdata.selectedUser) {
                    obj.qrysql = "Call admin_label ('','" + sdata.selectedUser + "', '" + sdata.fromDate + "', '" + sdata.toDate + "')";
                }
                else {
                    obj.qrysql = "Call admin_label ('1','1', '" + sdata.fromDate + "', '" + sdata.toDate + "')";
                }
                obj.prepare();
                obj.execute((_error, result) => {
                    if (_error == 1) {
                        let objv = new RawView_1.RawView(res);
                        objv.prepare({ message: "Invoice Fetched!", data: result, status: 200 });
                        objv.execute();
                    }
                    else {
                        let objv = new RawView_1.RawView(res);
                        objv.prepare({ message: "Something Went Wrong With Connection!", status: 502 });
                        objv.execute();
                    }
                });
            }
            else {
                let objv = new _406_1.Res406(res);
                objv.prepare(session);
                objv.execute();
            }
        });
    }
    billingInvoice(req, res, next) {
        let session = new Session_1.SessionManagment(req, res, next);
        session.GetSession((err, sessdata) => {
            if (err == 1) {
                let obj = new RawQuery_1.ModelRawQuery(req, res);
                obj.qrysql = "SELECT *,TRUNCATE(tax,4) AS tax, TRUNCATE(total_amount,4) AS total_amount,TRUNCATE((`total_amount`+tax),4)AS grand_total, DATE_FORMAT(created_at, '%m-%d-%Y') FROM `invoice_monthly` WHERE idinvoice='" + req.query.idinvoice + "' ORDER BY created_at ASC";
                obj.prepare();
                obj.execute((_error, result) => {
                    if (_error == 1) {
                        let objv = new RawView_1.RawView(res);
                        objv.prepare({ message: "Invoice Fetched!", data: result, status: 200 });
                        objv.execute();
                    }
                    else {
                        let objv = new RawView_1.RawView(res);
                        objv.prepare({ message: "Something Went Wrong With Connection!", status: 502 });
                        objv.execute();
                    }
                });
            }
            else {
                let objv = new _406_1.Res406(res);
                objv.prepare(session);
                objv.execute();
            }
        });
    }
    invoiceBillingAddress(req, res, next) {
        let qdata = req.query;
        let session = new Session_1.SessionManagment(req, res, next);
        session.GetSession((sessErr, sessData) => {
            if (sessErr == 1) {
                let obj = new RawQuery_1.ModelRawQuery(req, res);
                //obj.qrysql = "SELECT san,customer_name, address1, address2, city, state,postal_code, country, phone, email FROM invoice WHERE idaccount='" + qdata.idaccount + "'";
                obj.qrysql = "SELECT san,customer_name, address1, address2, city, state,postal_code, country, phone, email FROM invoice WHERE idinvoice='" + qdata.idinvoice + "'";
                obj.prepare();
                obj.execute((_error, result) => {
                    if (_error == 1) {
                        let objv = new RawView_1.RawView(res);
                        objv.prepare({ message: "Billing Address Fetched!", data: result, status: 200 });
                        objv.execute();
                    }
                    else {
                        let objv = new RawView_1.RawView(res);
                        objv.prepare({ message: "Something Went Wrong With Connection!", status: 502 });
                        objv.execute();
                    }
                });
            }
            else {
                let objv = new _406_1.Res406(res);
                objv.prepare(session);
                objv.execute();
            }
        });
    }
    // montholyInvoice(req: Request, res: Response, next: NextFunction) {
    // 	let sdata = req.query;
    // 	console.log(sdata);
    // 	let condition: any = "";
    // 	let session = new SessionManagment(req, res, next);
    // 	session.GetSession((sessError: any, sessdata) => {
    // 		if (sessError == 1) {
    // 			let obj = new ModelRawQuery(req, res);
    // 			if (sessdata.type == 1) {
    // 				if (sdata.selectedUser || (sdata.toDate && sdata.fromDate)) {
    // 					if (sdata.fromDate && sdata.toDate && !sdata.selectedUser) {
    // 						condition += 'ins.`created_at` BETWEEN "' + sdata.fromDate + ' 00:00:00" AND "' + sdata.toDate + ' 23:59:59"';
    // 					}
    // 					if (sdata.selectedUser && !(sdata.toDate && sdata.fromDate)) {
    // 						condition += 'ins.`idaccount`="' + sdata.selectedUser + '"';
    // 					}
    // 					if (sdata.selectedUser && sdata.fromDate && sdata.toDate) {
    // 						condition += 'ins.created_at BETWEEN "' + sdata.fromDate + ' 00:00:00" AND "' + sdata.toDate + ' 23:59:59" AND ins.`idaccount`="' + sdata.selectedUser + '"';
    // 					}
    // 					obj.qrysql = "SELECT `ins`.`san`,`ins`.`idinvoice`,`ins`.`idaccount`, `ins`.`invoice_number`, ins.customer_name, DATE_FORMAT(`ins`.`created_at`, '%m-%d-%Y')`created_at`, `inm`.`total_amount` FROM `invoice` `ins` INNER JOIN `invoice_monthly` `inm` ON `ins`.`idinvoice`=`inm`.`idinvoice`  WHERE " + condition + " GROUP BY `ins`.`idinvoice` ORDER BY DATE_FORMAT(`ins`.`created_at`, '%Y-%m') ASC";
    // 				}
    // 				else {
    // 					obj.qrysql = "SELECT `ins`.`san`, `ins`.`idinvoice`,`ins`.`idaccount`, `ins`.`invoice_number`,  ins.customer_name,DATE_FORMAT(`ins`.`created_at`, '%m-%d-%Y')`created_at`, `inm`.`total_amount` FROM `invoice` `ins`INNER JOIN `invoice_monthly` `inm` ON `ins`.`idinvoice`=`inm`.`idinvoice`GROUP BY `ins`.`idinvoice` ORDER BY DATE_FORMAT(`ins`.`created_at`, '%Y-%m') ASC";
    // 				}
    // 			}
    // 			else {
    // 				obj.qrysql = "SELECT `ins`.`san`, `ins`.`idinvoice`,`ins`.`idaccount`, `ins`.`invoice_number`,  ins.customer_name, DATE_FORMAT(`ins`.`created_at`, '%m-%d-%Y')`created_at`, `inm`.`total_amount` FROM `invoice` `ins` INNER JOIN `invoice_monthly` `inm` ON `ins`.`idinvoice`=`inm`.`idinvoice` WHERE `ins`.`idaccount`='" + sessdata.idaccount + "' GROUP BY `ins`.`idinvoice` ORDER BY DATE_FORMAT(`ins`.`created_at`, '%Y-%m') ASC"
    // 			}
    // 			obj.prepare();
    // 			obj.execute((err: any, result: any) => {
    // 				if (err == 1) {
    // 					let objv = new RawView(res);
    // 					objv.prepare({ message: "Invoice Fetched Successfully!", status: 200, data: result });
    // 					objv.execute();
    // 				}
    // 				else {
    // 					let objv = new RawView(res);
    // 					objv.prepare({ message: "Something Went Wrong With Connection!", status: 502 });
    // 					objv.execute();
    // 				}
    // 			});
    // 		}
    // 		else {
    // 			let objv = new RawView(res);
    // 			objv.prepare({ message: "Invalid Authorization!", status: 401 });
    // 			objv.execute();
    // 		}
    // 	});
    // }
    montholyInvoice(req, res, next) {
        let sdata = req.query;
        let condition = "";
        let session = new Session_1.SessionManagment(req, res, next);
        session.GetSession((sessError, sessdata) => {
            if (sessError == 1) {
                let obj = new RawQuery_1.ModelRawQuery(req, res);
                if (sessdata.type == 1) {
                    if (sdata.selectedUser || (sdata.toDate && sdata.fromDate)) {
                        if (sdata.fromDate && sdata.toDate && !sdata.selectedUser) {
                            condition += 'ins.`created_at` BETWEEN "' + sdata.fromDate + ' 00:00:00" AND "' + sdata.toDate + ' 23:59:59"';
                        }
                        if (sdata.selectedUser && !(sdata.toDate && sdata.fromDate)) {
                            condition += 'ins.`idaccount`="' + sdata.selectedUser + '"';
                        }
                        if (sdata.selectedUser && sdata.fromDate && sdata.toDate) {
                            condition += 'ins.created_at BETWEEN "' + sdata.fromDate + ' 00:00:00" AND "' + sdata.toDate + ' 23:59:59" AND ins.`idaccount`="' + sdata.selectedUser + '"';
                        }
                        obj.qrysql = "SELECT `ins`.`san`,`ins`.`idinvoice`,`ins`.`idaccount`, `ins`.`invoice_number`, ins.customer_name, DATE_FORMAT(`ins`.`created_at`, '%m-%d-%Y')`created_at`, `inm`.`total_amount`,`inm`.`tax` FROM `invoice` `ins` INNER JOIN `invoice_monthly` `inm` ON `ins`.`idinvoice`=`inm`.`idinvoice`  WHERE " + condition + " GROUP BY `ins`.`idinvoice` ORDER BY DATE_FORMAT(`ins`.`created_at`, '%Y-%m') ASC";
                    }
                    else {
                        obj.qrysql = "SELECT `ins`.`san`, `ins`.`idinvoice`,`ins`.`idaccount`, `ins`.`invoice_number`,  ins.customer_name,DATE_FORMAT(`ins`.`created_at`, '%m-%d-%Y')`created_at`, `inm`.`total_amount`,`inm`.`tax` FROM `invoice` `ins`INNER JOIN `invoice_monthly` `inm` ON `ins`.`idinvoice`=`inm`.`idinvoice`GROUP BY `ins`.`idinvoice` ORDER BY DATE_FORMAT(`ins`.`created_at`, '%Y-%m') ASC";
                    }
                }
                else {
                    obj.qrysql = "SELECT `ins`.`san`, `ins`.`idinvoice`,`ins`.`idaccount`, `ins`.`invoice_number`,  ins.customer_name, DATE_FORMAT(`ins`.`created_at`, '%m-%d-%Y')`created_at`, `inm`.`total_amount`, `inm`.`tax` FROM `invoice` `ins` INNER JOIN `invoice_monthly` `inm` ON `ins`.`idinvoice`=`inm`.`idinvoice` WHERE `ins`.`idaccount`='" + sessdata.idaccount + "' GROUP BY `ins`.`idinvoice` ORDER BY DATE_FORMAT(`ins`.`created_at`, '%Y-%m') ASC";
                }
                obj.prepare();
                obj.execute((err, result) => {
                    if (err == 1) {
                        let objv = new RawView_1.RawView(res);
                        objv.prepare({ message: "Invoice Fetched Successfully!", status: 200, data: result });
                        objv.execute();
                    }
                    else {
                        let objv = new RawView_1.RawView(res);
                        objv.prepare({ message: "Something Went Wrong With Connection!", status: 502 });
                        objv.execute();
                    }
                });
            }
            else {
                let objv = new RawView_1.RawView(res);
                objv.prepare({ message: "Invalid Authorization!", status: 401 });
                objv.execute();
            }
        });
    }
    searchOperators(req, res, next) {
        let searchData = req.query;
        let session = new Session_1.SessionManagment(req, res, next);
        session.GetSession((err, sessdata) => {
            if (err == 1) {
                let obj = new RawQuery_1.ModelRawQuery(req, res);
                obj.qrysql = "SELECT id, name FROM `operator`";
                obj.prepare();
                obj.execute((_error, result) => {
                    if (_error == 1) {
                        let objv = new RawView_1.RawView(res);
                        objv.prepare({ message: "Data Filtered!", data: result, status: 200 });
                        objv.execute();
                    }
                    else {
                        let objv = new RawView_1.RawView(res);
                        objv.prepare({ message: "Something Went Wrong With Connection!", status: 200 });
                        objv.execute();
                    }
                });
            }
            else {
                let objv = new _406_1.Res406(res);
                objv.prepare(session);
                objv.execute();
            }
        });
    }
    operatorFromLergInfo(req, res, next) {
        let session = new Session_1.SessionManagment(req, res, next);
        session.GetSession((err, sessdata) => {
            if (err == 1) {
                let obj = new RawQuery_1.ModelRawQuery(req, res);
                //  REPLACE(REPLACE(co_name_spec_c,'-',' '),'_',' ') co_name_spec_c
                obj.qrysql = "SELECT `idlerg_operator`, `operator` FROM `lerg_operator` WHERE `category`='" + req.query.category + "';";
                obj.prepare();
                obj.execute((_error, result) => {
                    if (_error == 1) {
                        let objv = new RawView_1.RawView(res);
                        objv.prepare({ message: "Operators Fetched Successfully!", data: result, status: 200 });
                        objv.execute();
                    }
                    else {
                        let objv = new RawView_1.RawView(res);
                        objv.prepare({ message: "Something Went Wrong With Connection!", status: 502 });
                        objv.execute();
                    }
                });
            }
            else {
                let objv = new _406_1.Res406(res);
                objv.prepare(session);
                objv.execute();
            }
        });
    }
    categoryFromLergInfo(req, res, next) {
        let session = new Session_1.SessionManagment(req, res, next);
        session.GetSession((err, sessdata) => {
            if (err == 1) {
                let obj = new RawQuery_1.ModelRawQuery(req, res);
                obj.qrysql = "SELECT `idlerg_operator`,`category` FROM `lerg_operator` GROUP BY `category`";
                obj.prepare();
                obj.execute((_error, result) => {
                    if (_error == 1) {
                        let objv = new RawView_1.RawView(res);
                        objv.prepare({ message: "Operators Fetched Successfully!", data: result, status: 200 });
                        objv.execute();
                    }
                    else {
                        let objv = new RawView_1.RawView(res);
                        objv.prepare({ message: "Something Went Wrong With Connection!", status: 502 });
                        objv.execute();
                    }
                });
            }
            else {
                let objv = new _406_1.Res406(res);
                objv.prepare(session);
                objv.execute();
            }
        });
    }
    Lerg_Info(req, res, next) {
        let session = new Session_1.SessionManagment(req, res, next);
        session.GetSession((err, sessdata) => {
            if (err == 1) {
                let obj = new RawQuery_1.ModelRawQuery(req, res);
                obj.qrysql = "SELECT * FROM `lerg_info` GROUP BY `co_name_spec_c`";
                obj.prepare();
                obj.execute((_error, result) => {
                    if (_error == 1) {
                        let objv = new RawView_1.RawView(res);
                        objv.prepare({ message: "Operators Fetched Successfully!", data: result, status: 200 });
                        objv.execute();
                    }
                    else {
                        let objv = new RawView_1.RawView(res);
                        objv.prepare({ message: "Something Went Wrong With Connection!", status: 502 });
                        objv.execute();
                    }
                });
            }
            else {
                let objv = new _406_1.Res406(res);
                objv.prepare(session);
                objv.execute();
            }
        });
    }
    uploadLergInfo(req, res, next) {
        let Upload = new ModelCsvUpload_1.ModelCsvUpload(req, res);
        let udata = Upload.fileUpload();
        let session = new Session_1.SessionManagment(req, res, next);
        session.GetSession((errsess, sessdata) => {
            if (errsess == 1) {
                var currentdate = new Date();
                var datetime = currentdate.getDate() + "" + (currentdate.getMonth() + 1) + "" + currentdate.getFullYear() + "" + currentdate.getHours() + "" + currentdate.getMinutes() + "" + currentdate.getSeconds();
                // copy table
                let obj0 = new RawNonQuery_1.ModelRawNonQuery(req, res);
                obj0.nonqrysql = "CREATE TABLE lerg_info_" + datetime + "( SELECT * FROM lerg_info)";
                obj0.prepare();
                obj0.execute((errcopy, respcopy) => {
                    if (errcopy == 1) {
                        let obj1 = new RawNonQuery_1.ModelRawNonQuery(req, res);
                        obj1.nonqrysql = "TRUNCATE TABLE `lerg_info`;";
                        obj1.prepare();
                        obj1.execute((err, resp) => {
                            if (err == 1) {
                                let obj = new RawNonQuery_1.ModelRawNonQuery(req, res);
                                obj.nonqrysql = "LOAD DATA LOCAL INFILE '" + udata[1] + udata[0] + "' INTO TABLE `lerg_info` FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (@col1, @col2, @col3, @col4, @col5, @col6, @col7, @col8, @col9,@col10, @col11, @col12) SET `npa`=@col1, `nxx`=@col2, `block`=@col3, `ocn`=@col4, `lata`=@col5, `state`=@col6, `co_name_spec_c`=@col7, `category`=@col8, `lergdate`=@col9, `region`=@col10, `lata_name`=@col11, `country`=@col12";
                                obj.prepare();
                                obj.execute((_error, result) => {
                                    if (errsess == 1) {
                                        let obj0 = new RawNonQuery_1.ModelRawNonQuery(req, res);
                                        obj0.nonqrysql = "UPDATE `lerg_info` SET co_name_spec_c=REPLACE(co_name_spec_c,',','-')";
                                        obj0.prepare();
                                        obj0.execute((errcopy, respcopy) => {
                                            if (errsess == 1) {
                                                let obj0 = new RawNonQuery_1.ModelRawNonQuery(req, res);
                                                obj0.nonqrysql = "UPDATE `lerg_info` SET co_name_spec_c=REPLACE(co_name_spec_c,' ','_')";
                                                obj0.prepare();
                                                obj0.execute((errcopy, respcopy) => {
                                                    if (_error == 1) {
                                                        let obj1 = new RawNonQuery_1.ModelRawNonQuery(req, res);
                                                        obj1.nonqrysql = "INSERT INTO upload (`idaccount`,`filename`, `destination_path`, `filetype`, `type`) VALUES ('" + sessdata.idaccount + "', '" + req.file.filename + "', '" + udata[1] + udata[0] + "', '" + req.file.mimetype + "', 'Lerg Info')";
                                                        obj1.prepare();
                                                        obj1.execute((__error, _result) => {
                                                            if (__error == 1) {
                                                                let objv = new RawView_1.RawView(res);
                                                                objv.prepare({ message: "Lerg Info Upload Successfully!", data: result, status: 201 });
                                                                objv.execute();
                                                            }
                                                            else {
                                                                let objv = new RawView_1.RawView(res);
                                                                objv.prepare({ message: "Something Went Wrong With File!", status: 502 });
                                                                objv.execute();
                                                            }
                                                        });
                                                    }
                                                    else {
                                                        let objv = new RawView_1.RawView(res);
                                                        objv.prepare({ message: "Something Went Wrong With Connection!", status: 502 });
                                                        objv.execute();
                                                    }
                                                });
                                            }
                                            else {
                                                let objv = new RawView_1.RawView(res);
                                                objv.prepare({ message: "Table not Updated!", status: 502 });
                                                objv.execute();
                                            }
                                        });
                                    }
                                    else {
                                        let objv = new RawView_1.RawView(res);
                                        objv.prepare({ message: "Table not Updated!", status: 502 });
                                        objv.execute();
                                    }
                                });
                            }
                            else {
                                let objv = new RawView_1.RawView(res);
                                objv.prepare({ message: "Table Not Truncated!", status: 502 });
                                objv.execute();
                            }
                        });
                    }
                    else {
                        let objv = new RawView_1.RawView(res);
                        objv.prepare({ message: "Table Not Created! ", status: 502 });
                        objv.execute();
                    }
                });
            }
            else {
                let objv = new RawView_1.RawView(res);
                objv.prepare({ message: "Invalid Session!", status: 402 });
                objv.execute();
            }
        });
    }
    setOperatorMapping(req, res, next) {
        let session = new Session_1.SessionManagment(req, res, next);
        session.GetSession((error, sessdata) => {
            let operator1 = req.body.operator1;
            let data = req.body.operator2;
            let category = req.body.category[0];
            if (error == 1) {
                let tmpqurty = '';
                tmpqurty = "";
                let obj = new RawNonQuery_1.ModelRawNonQuery(req, res);
                data.forEach((element) => {
                    obj.nonqrysql = "INSERT INTO `operator_mapping` (`idoperator`,`idlerg_operator`, `category`,`key`) VALUES ('" + operator1.id + "', '" + element.idlerg_operator + "', '" + category.category + "','" + sessdata.idaccount + '-' + operator1.id + '-' + element.idlerg_operator + "') ON DUPLICATE KEY UPDATE `idoperator`='" + operator1.id + "', `idlerg_operator`='" + element.idlerg_operator + "', `category`='" + category.category + "',`key`='" + sessdata.idaccount + "-" + operator1.id + "-" + element.idlerg_operator + "'";
                    obj.prepare();
                    obj.execute((error, result) => {
                    });
                });
                // var ne :any= tmpqurty.slice(0, -1);
                // console.log(ne);
                // obj.nonqrysql =tmpqurty.slice(0, -1);
                // 
                // obj.prepare();
                // obj.execute((error: any, result: any) => {
                // if(error == 1){
                let objv = new RawView_1.RawView(res);
                objv.prepare({ message: "Operator Mapping Successfully!" });
                objv.execute();
                // }
                // else{
                // 	let objv = new RawView(res);
                // 	objv.prepare({ message: "Somthing Went Wrong!" });
                // 	objv.execute();	
                // }
                // });	
            }
            else {
                let objv = new _406_1.Res406(res);
                objv.prepare(session);
                objv.execute();
            }
        });
    }
    getOperatorMapping(req, res, next) {
        let session = new Session_1.SessionManagment(req, res, next);
        session.GetSession((err, sessdata) => {
            if (err == 1) {
                let obj = new RawQuery_1.ModelRawQuery(req, res);
                obj.qrysql = "SELECT CONCAT('\"name\":\"',`name`, '\",\"data\": [',IFNULL(`data`,''),']') `name` FROM (SELECT GROUP_CONCAT(CONCAT('{\"lerg\":\"',lo.operator,'\",\"type\":\"',om.category,'\", \"id\":\"',om.id_operator,'\"}')) `data`, o.name FROM `lerg_operator` lo JOIN `operator_mapping` om ON lo.idlerg_operator=om.idlerg_operator RIGHT JOIN operator o ON o.id=om.idoperator GROUP BY idoperator, o.id) tbl";
                obj.prepare();
                obj.execute((_error, result) => {
                    if (_error == 1) {
                        let objv = new RawView_1.RawView(res);
                        objv.prepare({ message: "Operator Mapping Fetched!", status: 200, data: result });
                        objv.execute();
                    }
                    else {
                        let objv = new RawView_1.RawView(res);
                        objv.prepare({ message: "Something Went Wrong with DB!", status: 502 });
                        objv.execute();
                    }
                });
            }
            else {
                let objv = new _406_1.Res406(res);
                objv.prepare(session);
                objv.execute();
            }
        });
    }
    deleteMapping(req, res, next) {
        let session = new Session_1.SessionManagment(req, res, next);
        session.GetSession((err, sessdata) => {
            if (err == 1) {
                let obj = new RawNonQuery_1.ModelRawNonQuery(req, res);
                obj.nonqrysql = "DELETE FROM `operator_mapping` WHERE `id_operator`='" + req.query.operator + "'";
                obj.prepare();
                obj.execute((_error, result) => {
                    if (_error == 1) {
                        let objv = new RawView_1.RawView(res);
                        objv.prepare({ message: "Operator Mapping Deleted!", data: result, status: 201 });
                        objv.execute();
                    }
                    else {
                        let objv = new RawView_1.RawView(res);
                        objv.prepare({ message: "Something Went Wrong With DB!", status: 503 });
                        objv.execute();
                    }
                });
            }
            else {
                let objv = new _406_1.Res406(res);
                objv.prepare(session);
                objv.execute();
            }
        });
    }
    uploadZone(req, res, next) {
        var filePath;
        if (req.file) {
            const fileName = req.file.filename;
            filePath = 'http://localhost:4219' + '/uploads/' + fileName;
            //   filePath = "https://signalmash.crmdialer.us:4263"+'/uploads/' + fileName;
        }
        let session = new Session_1.SessionManagment(req, res, next);
        session.GetSession((err, sessdata) => {
            if (err == 1) {
                let obj1 = new RawNonQuery_1.ModelRawNonQuery(req, res);
                obj1.nonqrysql = "TRUNCATE TABLE `zones`;";
                obj1.prepare();
                obj1.execute((err, ress) => {
                    if (err == 1) {
                        let obj = new RawNonQuery_1.ModelRawNonQuery(req, res);
                        obj.nonqrysql = "LOAD DATA LOCAL INFILE '" + req.file.path + "' INTO TABLE `zones` FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (@col1, @col2) SET `npanxx`=@col1, `zone`=@col2";
                        obj.prepare();
                        obj.execute((_error, result) => {
                            if (_error == 1) {
                                let obj1 = new RawNonQuery_1.ModelRawNonQuery(req, res);
                                obj1.nonqrysql = "INSERT INTO upload (`idaccount`,`filename`, `destination_path`, `filetype`, `type`) VALUES ('" + sessdata.idaccount + "', '" + req.file.filename + "', '" + req.file.path + "', '" + req.file.mimetype + "', 'Zone CSV')";
                                obj1.prepare();
                                obj1.execute((__error, _result) => {
                                    if (__error == 1) {
                                        let objv = new RawView_1.RawView(res);
                                        objv.prepare({ message: "ZONE Data Upload Successfully!", data: result, status: 201 });
                                        objv.execute();
                                    }
                                    else {
                                        let objv = new RawView_1.RawView(res);
                                        objv.prepare({ message: "Something Went Wrong With File!", status: 502 });
                                        objv.execute();
                                    }
                                });
                            }
                            else {
                                let objv = new RawView_1.RawView(res);
                                objv.prepare({ message: "Something Went Wrong With File!", status: 502 });
                                objv.execute();
                            }
                        });
                    }
                    else {
                        let objv = new RawView_1.RawView(res);
                        objv.prepare({ message: "its DB error!", status: 502 });
                        objv.execute();
                    }
                });
            }
            else {
                let objv = new RawView_1.RawView(res);
                objv.prepare({ message: "Invalid Session trying to access", status: 401 });
                objv.execute();
            }
        });
    }
    getZones(req, res, next) {
        let session = new Session_1.SessionManagment(req, res, next);
        session.GetSession((sessErr, sessData) => {
            if (sessErr == 1) {
                let obj = new RawQuery_1.ModelRawQuery(req, res);
                obj.qrysql = "SELECT * FROM zones";
                obj.prepare();
                obj.execute((err, result) => {
                    if (err == 1) {
                        let objv = new RawView_1.RawView(res);
                        objv.prepare({ message: "Zone Fetched Successfully!", status: 200, data: result });
                        objv.execute();
                    }
                    else {
                        let objv = new RawView_1.RawView(res);
                        objv.prepare({ message: "Something went wrong With Connection", status: 502 });
                        objv.execute();
                    }
                });
            }
            else {
                let objv = new RawView_1.RawView(res);
                objv.prepare({ message: "Invalid Session trying to access", status: 401 });
                objv.execute();
            }
        });
    }
    ganarateInvoice(req, res, next) {
        let sdata = req.body;
        let session = new Session_1.SessionManagment(req, res, next);
        session.GetSession((err, sessdata) => {
            if (err == 1) {
                let obj = new RawNonQuery_1.ModelRawNonQuery(req, res);
                obj.nonqrysql = "iNSERT INTO `invoice` (`idaccount`, `invoice_number`) VALUES ('" + sessdata.idaccount + "', '" + sdata.invoiceNumber + "')";
                obj.prepare();
                obj.execute((_error, result) => {
                    if (_error == 1) {
                        let objv = new RawView_1.RawView(res);
                        objv.prepare({ message: "Inovoice Generate Successfully!", data: result, status: 201 });
                        objv.execute();
                    }
                    else {
                        let objv = new RawView_1.RawView(res);
                        objv.prepare({ message: "Something Went Wrong With Connection!", status: 502 });
                        objv.execute();
                    }
                });
            }
            else {
                let objv = new RawView_1.RawView(res);
                objv.prepare({ message: "Invalid Session trying to access", status: 401 });
                objv.execute();
            }
        });
        // });
    }
    tMobileReportList(req, res, next) {
        let session = new Session_1.SessionManagment(req, res, next);
        session.GetSession((sessErr, sessData) => {
            if (sessErr == 1) {
                let obj = new RawQuery_1.ModelRawQuery(req, res);
                obj.qrysql = "SELECT type , DATE_FORMAT(`date`, '%Y-%m-%d %H:%i:%s')`date`,`month` FROM `tmobile` WHERE `idaccount` = '" + sessData.idaccount + "'";
                obj.prepare();
                obj.execute((_error, result) => {
                    if (_error == 1) {
                        let objv = new RawView_1.RawView(res);
                        objv.prepare({ message: "T_Mobile List Fetch Successfully!", data: result, status: 200 });
                        objv.execute();
                    }
                    else {
                        let objv = new RawView_1.RawView(res);
                        objv.prepare({ message: "Something Went Wrong With Connection!", status: 502 });
                        objv.execute();
                    }
                });
            }
            else {
                let objv = new RawView_1.RawView(res);
                objv.prepare({ message: "Invalid Session trying to access", status: 401 });
                objv.execute();
            }
        });
    }
    tMobileReport(req, res, next) {
        let qData = req.query;
        let session = new Session_1.SessionManagment(req, res, next);
        session.GetSession((sessErr, sessData) => {
            if (sessErr == 1) {
                let obj = new RawNonQuery_1.ModelRawNonQuery(req, res);
                obj.nonqrysql = "CALL tmobile_monthly_report(" + qData.month + ")";
                obj.prepare();
                obj.execute((_error, result) => {
                    if (_error == 1) {
                        let objv = new RawView_1.RawView(res);
                        objv.prepare({ message: "T_Mobile List Fetch Successfully!", data: result, status: 200 });
                        objv.execute();
                    }
                    else {
                        let objv = new RawView_1.RawView(res);
                        objv.prepare({ message: "Something Went Wrong With Connection!", status: 502 });
                        objv.execute();
                    }
                });
            }
            else {
                let objv = new RawView_1.RawView(res);
                objv.prepare({ message: "Invalid Session trying to access", status: 401 });
                objv.execute();
            }
        });
    }
    demo(req, res, next) {
        let array = [0, 1, 2, 3, 4];
        array.forEach(ele => {
            res.write("go");
        });
        let obj = new RawView_1.RawView(res);
        obj.prepare({ respone: "hello", status: "working" });
        obj.execute();
    }
    /**
    * To import the DIDs using csv
    */
    uploadDID(req, res, next) {
        var sdata = JSON.parse(req.body.uploadDid);
        let user = sdata.user.split('-');
        let iduser = user[0];
        let idaccount = user[1];
        let session = new Session_1.SessionManagment(req, res, next);
        session.GetSession((error, sessdata) => {
            if (error == 1) {
                let objfile = new ModelCsvUpload_1.ModelCsvUpload(req, res);
                let fdata = objfile.fileUpload();
                let firstrow1 = fs.readFileSync(req.file.path).toString().split('\n');
                let firstrow = firstrow1[0].split('\r');
                console.log(firstrow);
                let str = "";
                // console.log(firstrow[0]);
                if (!firstrow[0].match(/[a-z]/)) {
                    console.log("LETTER FOUND");
                    str = '';
                }
                else {
                    console.log("LETTER NOT FOUND");
                    str = 'IGNORE 1 LINES';
                }
                let str_array = [];
                str_array = firstrow[0].split(',');
                for (var i = 0; i < str_array.length; i++) {
                    // Trim the excess whitespace.
                    str_array[i] = str_array[i].replace(/^\s*/, "").replace(/\s*$/, "").replace(/\"/g, "").trim();
                }
                //remove uploaded temp file from disk.
                /* fs.unlink(fdata[2], (err: any) => {
                    if (err) throw err;
                }); */
                let rate = new APIBase_1.APIBase;
                rate.getRate(req, res, sdata.idtariff, "message", sdata.numbertype, (err, voice_rate) => {
                    let did_rates = Array.isArray(voice_rate) && voice_rate.length > 0 ? voice_rate[0].rate : 0;
                    if (err == 1 && did_rates > 0) {
                        let obj = new RawNonQuery_1.ModelRawNonQuery(req, res);
                        let filelname = req.file.originalname.replace(/"/g, '\\"').replace(/'/g, "\\'");
                        fdata[0] = fdata[0].replace(/"/g, '\\"').replace(/'/g, "\\'");
                        obj.nonqrysql = "INSERT INTO `upload_did` (`uploaded_file`, `did_count`, `iduser`, `idaccount`, `description`, `campaign`, `connection`, `provider`) VALUES ('" + fdata[0] + "', '0', '" + iduser + "', '" + idaccount + "','" + sdata.description + "', '" + sdata.campaign + "', '" + sdata.connection + "', '" + sdata.provider + "')";
                        obj.prepare();
                        obj.execute((error, result) => {
                            if (error == 1) {
                                let lastid = result.insertId;
                                let iQry = "LOAD DATA LOCAL INFILE'" + fdata[1] + fdata[0] + "' IGNORE INTO TABLE `did_buy` CHARACTER SET UTF8\n";
                                //iQry += 'OPTIONALLY ENCLOSED BY '+'"'+'\n'
                                //iQry += "OPTIONALLY ENCLOSED BY " + "'" + '"' + "'"+"\n"
                                iQry += "FIELDS TERMINATED BY ','\n";
                                iQry += "ENCLOSED BY ' '\n";
                                iQry += "ESCAPED BY ' '\n",
                                    iQry += "LINES TERMINATED BY '\n' IGNORE 1 LINES\n";
                                iQry += "(@col1)\n";
                                iQry += "SET `did`=@col1, `iduser`='" + iduser + "', `account_id`='" + idaccount + "', `provider`='" + sdata.provider + "', `sms_provider`='" + sdata.sms_provider + "',`sinch_status`='0', `camp_registry`='" + sdata.campaign + "', `connection_key`='" + sdata.connection + "', `inbound_key`='" + sdata.inbound + "', `outbound_key`='" + sdata.outbound + "', `rates`='" + did_rates + "', `type`='" + sdata.numbertype + "', `npa`=SUBSTRING(SUBSTR(REPLACE(REPLACE((@col1),' ',''),'\r',''),-10),1,3), `nxx`=SUBSTRING(REPLACE(REPLACE((@col1),' ',''),'\r',''),-7,3), `disconnect_did_status`='1', `id_upload`='" + lastid + "', `status`='completed', `osr_status`='';";
                                let obj = new RawNonQuery_1.ModelRawNonQuery(req, res);
                                obj.nonqrysql = iQry;
                                obj.prepare();
                                obj.execute((error, result) => {
                                    if (error == 1) {
                                        let objlc = new RawQuery_1.ModelRawQuery(req, res);
                                        objlc.qrysql = "SELECT COUNT(*) `did` FROM `did_buy` WHERE id_upload='" + lastid + "'";
                                        objlc.prepare();
                                        objlc.execute((errorC, listcount) => {
                                            if (errorC == 1) {
                                                let objlc2 = new RawNonQuery_1.ModelRawNonQuery(req, res);
                                                objlc2.nonqrysql = "UPDATE `upload_did` SET `did_count`='" + listcount[0].did + "' WHERE `idupload`='" + lastid + "'";
                                                objlc2.prepare();
                                                objlc2.execute((errU, result9) => {
                                                    if (errU == 1) {
                                                        let objp = new RawQuery_1.ModelRawQuery(req, res);
                                                        objp.qrysql = "SELECT t.proration FROM `user`u JOIN `tariff`t ON u.idtariff=t.idtariff WHERE iduser= '" + iduser + "'";
                                                        objp.prepare();
                                                        objp.execute((proration_error, proration_success) => {
                                                            if (proration_error == 1) {
                                                                let proration_status = proration_success[0].proration;
                                                                let dateObj = new Date();
                                                                let day = dateObj.getUTCDate();
                                                                let nDate = new Date();
                                                                // lDate //Total days of month
                                                                let lDate = (new Date(nDate.getFullYear(), (nDate.getMonth() + 1), 0)).getDate();
                                                                let totalDays = (lDate - day) + 1;
                                                                let ProrateSingle = proration_status == "true" ? (totalDays * did_rates) / lDate : did_rates;
                                                                let prorate = ProrateSingle * listcount[0].did;
                                                                console.log("Prorate::", prorate);
                                                                if (prorate > 0) {
                                                                    let objlc2 = new RawNonQuery_1.ModelRawNonQuery(req, res);
                                                                    objlc2.nonqrysql = "UPDATE `wallet` SET `balance`=`balance`-'" + prorate + "' WHERE idaccount='" + idaccount + "'";
                                                                    objlc2.prepare();
                                                                    objlc2.execute((errUy, result9) => {
                                                                        let iQry = "INSERT INTO `wallet_recharge_log` SET ";
                                                                        iQry += "`idaccount`=" + idaccount + ", ";
                                                                        iQry += "`amount`='-" + prorate + "', ";
                                                                        iQry += "`purpose`='Wallet amount successfully updated for bulk DID import', ";
                                                                        iQry += "`status`=1;";
                                                                        let objWL = new RawNonQuery_1.ModelRawNonQuery(req, res);
                                                                        objWL.nonqrysql = iQry;
                                                                        objWL.prepare();
                                                                        objWL.execute((error, result) => {
                                                                            console.log("Transaction response::", result);
                                                                        });
                                                                        let objlc2 = new RawQuery_1.ModelRawQuery(req, res);
                                                                        objlc2.qrysql = "SELECT `did`, `order_id`, `npa`, `nxx`, `state`, `ratecenter` FROM `did_buy` WHERE id_upload=" + lastid + ";";
                                                                        objlc2.prepare();
                                                                        objlc2.execute((error, result) => {
                                                                            let DIDRecords = Array.isArray(result) && result.length > 0 ? result : [];
                                                                            let DI = 0;
                                                                            iQry = "";
                                                                            for (let x of DIDRecords) {
                                                                                iQry += "INSERT INTO `did_log` (`account_id`, `user_id`, `status`, `did`, `provider`, `order_id`, `iduser`, `disconnect_did_status`, `npa`, `nxx`, `state`, `ratecenter`, `rates`, `idapi`, `pro_rates`, `type`) VALUES(" + idaccount + ", " + iduser + ", 'completed','" + x.did + "', '" + sdata.provider + "', '" + x.order_id + "','" + iduser + "', '1', '" + x.npa + "', '" + x.nxx + "', '" + x.state + "', '" + x.ratecenter + "', '" + voice_rate[0].rate + "', '" + sessdata.idapi + "', '" + ProrateSingle + "', '" + sdata.numbertype + "');\n";
                                                                                DI++;
                                                                            }
                                                                            if (DI > 0) {
                                                                                let objlc2 = new RawNonQuery_1.ModelRawNonQuery(req, res);
                                                                                objlc2.nonqrysql = iQry;
                                                                                objlc2.prepare();
                                                                                objlc2.execute((error, result) => {
                                                                                    let objv = new RawView_1.RawView(res);
                                                                                    objv.prepare({
                                                                                        message: "Did Upload Successfully!",
                                                                                        status: 201
                                                                                    });
                                                                                    objv.execute();
                                                                                });
                                                                            }
                                                                            else {
                                                                                let objv = new RawView_1.RawView(res);
                                                                                objv.prepare({
                                                                                    message: "DID did not uploaded something went wrong!",
                                                                                    status: 502
                                                                                });
                                                                                objv.execute();
                                                                            }
                                                                        });
                                                                    });
                                                                }
                                                                else {
                                                                    let iQry = "INSERT INTO `wallet_recharge_log` SET ";
                                                                    iQry += "`idaccount`=" + idaccount + ", ";
                                                                    iQry += "`amount`='-" + prorate + "', ";
                                                                    iQry += "`purpose`='Billing prorate can't be 0 or less then 0 during DID upload', ";
                                                                    iQry += "`status`=2;";
                                                                    let objWL = new RawNonQuery_1.ModelRawNonQuery(req, res);
                                                                    objWL.nonqrysql = iQry;
                                                                    objWL.prepare();
                                                                    objWL.execute((error, result) => {
                                                                        console.log("Transaction response::", result);
                                                                    });
                                                                    let objv = new RawView_1.RawView(res);
                                                                    objv.prepare({
                                                                        message: "Billing prorate can't be 0 or less then 0!",
                                                                        status: 502
                                                                    });
                                                                    objv.execute();
                                                                }
                                                            }
                                                            else {
                                                                let obj = new RawView_1.RawView(res);
                                                                obj.prepare({
                                                                    error_code: 0,
                                                                    status: 502,
                                                                    message: "Something went wrong with get proration!"
                                                                });
                                                                obj.execute();
                                                            }
                                                        });
                                                    }
                                                    else {
                                                        let objv = new RawView_1.RawView(res);
                                                        objv.prepare({
                                                            message: "Data Not Found From Type!",
                                                            status: 502,
                                                            error: result9
                                                        });
                                                        objv.execute();
                                                    }
                                                });
                                            }
                                            else {
                                                let objv = new RawView_1.RawView(res);
                                                objv.prepare({
                                                    message: "Data Not Found From Type!",
                                                    status: 502,
                                                    error: listcount
                                                });
                                                objv.execute();
                                            }
                                        });
                                    }
                                    else {
                                        let objv = new RawView_1.RawView(res);
                                        objv.prepare({
                                            message: "Data Not Found From Type!",
                                            status: 502,
                                            error: error
                                        });
                                        objv.execute();
                                    }
                                });
                            }
                            else {
                                let objv = new RawView_1.RawView(res);
                                objv.prepare({
                                    message: "Something Went Wrong With Connection!",
                                    status: 502,
                                    error: result
                                });
                                objv.execute();
                            }
                        });
                    }
                    else {
                        let objv = new RawView_1.RawView(res);
                        objv.prepare({
                            message: "DID rates did not found!",
                            status: 502,
                        });
                        objv.execute();
                    }
                });
            }
            else {
                let objv = new _406_1.Res406(res);
                objv.prepare(session);
                objv.execute();
            }
        });
    }
    /**
     * To get the customer's campaigns
     */
    getCampaign(req, res, next) {
        let sdata = req.query;
        console.log("dataaaaaaaaaaaaaaaa", sdata);
        let session = new Session_1.SessionManagment(req, res, next);
        session.GetSession((error, sessdata) => {
            if (error == 1) {
                let obj = new RawQuery_1.ModelRawQuery(req, res);
                obj.qrysql = "SELECT * FROM `campaign` where `iduser` = '" + sdata.type + "' AND `deleted`= 0 GROUP BY campaign_key";
                obj.prepare();
                obj.execute((_error, result) => {
                    if (_error == 1) {
                        let objv = new RawView_1.RawView(res);
                        objv.prepare({
                            data: result,
                            message: "Campaign Fetched Successfully!",
                            status: 200
                        });
                        objv.execute();
                    }
                    else {
                        let objv = new RawView_1.RawView(res);
                        objv.prepare({
                            message: "Something Went Wrong With Get Campaign List!",
                            status: 502,
                            error: result
                        });
                        objv.execute();
                    }
                });
            }
            else {
                let objv = new _406_1.Res406(res);
                objv.prepare(session);
                objv.execute();
            }
        });
    }
    /**
     * To get the customer's connections
     */
    GetConnections(req, res, next) {
        let sdata = req.query;
        let session = new Session_1.SessionManagment(req, res, next);
        session.GetSession((error, sessdata) => {
            if (error == 1) {
                let obj = new RawQuery_1.ModelRawQuery(req, res);
                obj.qrysql = "SELECT idendpoint, connection_key, name connection FROM `endpoint` WHERE `iduser` = '" + sdata.type + "' GROUP BY connection_key;";
                obj.prepare();
                obj.execute((_error, result) => {
                    if (_error == 1) {
                        let objv = new RawView_1.RawView(res);
                        objv.prepare({
                            data: result,
                            message: "Campaign Fetched Successfully!",
                            status: 200
                        });
                        objv.execute();
                    }
                    else {
                        let objv = new RawView_1.RawView(res);
                        objv.prepare({
                            message: "Something Went Wrong With Connection!",
                            status: 502,
                            error: result
                        });
                        objv.execute();
                    }
                });
            }
            else {
                let objv = new _406_1.Res406(res);
                objv.prepare(session);
                objv.execute();
            }
        });
    }
    getInvoiceTax(req, res, next) {
        let sdata = req.query;
        let session = new Session_1.SessionManagment(req, res, next);
        session.GetSession((error, sessdata) => {
            if (error == 1) {
                let obj = new RawQuery_1.ModelRawQuery(req, res);
                obj.qrysql = "SELECT * FROM `invoice_tax` WHERE `idaccount` =  '" + sdata.idaccount + "' AND `idinvoice` = '" + sdata.idinvoice + "'";
                obj.prepare();
                obj.execute((_error, result) => {
                    if (_error == 1) {
                        let objv = new RawView_1.RawView(res);
                        objv.prepare({
                            data: result,
                            message: "Campaign Fetched Successfully!",
                            status: 200
                        });
                        objv.execute();
                    }
                    else {
                        let objv = new RawView_1.RawView(res);
                        objv.prepare({
                            message: "Something Went Wrong With Connection!",
                            status: 502,
                            error: result
                        });
                        objv.execute();
                    }
                });
            }
            else {
                let objv = new _406_1.Res406(res);
                objv.prepare(session);
                objv.execute();
            }
        });
    }
    getMMSMDR(req, res, next) {
        let qdata = req.query.type;
        var sdata = JSON.parse(qdata);
        let session = new Session_1.SessionManagment(req, res, next);
        session.GetSession((error, sessdata) => {
            if (error == 1) {
                let idaccount = typeof sdata.iduser != 'undefined' ? sdata.iduser : 0;
                let iQry = "", iQN1 = 0, iQN = 0, cond = "";
                iQry = "SELECT `idmdr`,direction, msg_count, from_number, to_number, MT_rates, MO_rates, MT_surcharge, `status`, message_delivered_to_operator, message_accepted_by_signalmash, delivery_receipt_received_by_singnalmash, delivery_receipt_delivered, mobility";
                if (sessdata.type == 1) {
                    iQry += ",messsage_body";
                }
                iQry += ", ocn, lata, cost, CONVERT_TZ(created_at, '" + sessdata.server_time + "', '" + sessdata.client_time + "') created_at FROM `mms_mdr`\n";
                if (sessdata.type == 1) {
                    if (idaccount) {
                        iQry += "WHERE idaccount=" + idaccount + " ";
                        iQN1++;
                    }
                }
                else if (sessdata.type == 2) {
                    iQry += "WHERE idaccount=" + sessdata.idaccount + " ";
                    iQN1++;
                }
                else if (sessdata.type == 22) {
                    iQry += "WHERE iduser=" + sessdata.iduser + " ";
                    iQN1++;
                }
                /**
                 * Other parameters
                 */
                if (sdata.from_date != undefined && sdata.from_date) {
                    cond += "AND created_at>=CONVERT_TZ('" + sdata.from_date + " 00:00:00', '" + sessdata.client_time + "', '" + sessdata.server_time + "') ";
                    iQN++;
                }
                if (sdata.to_date != undefined && sdata.to_date) {
                    cond += "AND created_at<=CONVERT_TZ('" + sdata.to_date + " 23:59:59', '" + sessdata.client_time + "', '" + sessdata.server_time + "') ";
                    iQN++;
                }
                if (sdata.from_number != undefined && sdata.from_number) {
                    cond += "AND from_number='" + sdata.from_number + "' ";
                    iQN++;
                }
                if (sdata.to_number != undefined && sdata.to_number) {
                    cond += "AND to_number='" + sdata.to_number + "' ";
                    iQN++;
                }
                if (sdata.direction != undefined && sdata.direction) {
                    cond += "AND direction='" + sdata.direction + "' ";
                    iQN++;
                }
                if (iQN1 > 0) {
                    if (iQN > 0) {
                        iQry += cond;
                    }
                    iQry = iQry.slice(0, -1) + "\n";
                }
                else {
                    if (iQN > 0) {
                        iQry += "WHERE " + cond.substr(4, cond.length);
                        iQry = iQry.slice(0, -1) + "\n";
                    }
                }
                iQry += "ORDER BY `created_at` DESC;";
                let obj = new RawNonQuery_1.ModelRawNonQuery(req, res);
                obj.nonqrysql = iQry;
                obj.prepare();
                obj.execute((_error, result) => {
                    if (_error == 1) {
                        let objv = new RawView_1.RawView(res);
                        objv.prepare({
                            data: result,
                            message: "MMS MDR Fetched Successfully.",
                            status: 200
                        });
                        objv.execute();
                    }
                    else {
                        let objv = new RawView_1.RawView(res);
                        objv.prepare({
                            data: result,
                            message: "Something Went Wrong, Try Again!",
                            status: 502
                        });
                        objv.execute();
                    }
                });
            }
            else {
                let objv = new _406_1.Res406(res);
                objv.prepare(session);
                objv.execute();
            }
        });
    }
    summeryMMSMDR(req, res, next) {
        let sdata = req.query;
        let data = JSON.parse(sdata.type);
        let condition = "";
        let Type = "";
        let session = new Session_1.SessionManagment(req, res, next);
        session.GetSession((error, sessdata) => {
            if (error == 1) {
                let obj2 = new RawQuery_1.ModelRawQuery(req, res);
                if (sessdata.type == 1) {
                    Type = "";
                }
                else {
                    Type = "AND `iduser`='" + sessdata.iduser + "'";
                }
                if (data.from_date && data.to_date) {
                    condition += "DATE_FORMAT(created_at,'%Y-%m-%d') BETWEEN '" + data.from_date + "' AND '" + data.to_date + "' " + Type + " ";
                }
                if (data.directionSummery == "Inbound") {
                    condition += "AND direction='inbound' ";
                }
                if (data.directionSummery == "Outbound") {
                    condition += "AND direction='Outbound' ";
                }
                if (data.directionSummery == "Both") {
                    condition += "";
                }
                let Qry;
                Qry = "SELECT `idmdr`, DATE_FORMAT(created_at,'%Y-%m-%d')`date`, direction , CASE WHEN direction='inbound' THEN COUNT(messsage_body) WHEN direction='outbound' THEN COUNT(messsage_body)ELSE 0 END`total_message` ,CASE direction WHEN'outbound' THEN SUM(MT_rates) WHEN 'inbound'	THEN SUM(MT_rates) END `total_cost`,`STATUS` FROM mms_mdr  WHERE " + condition + " GROUP BY direction,DATE_FORMAT(created_at,'%Y-%m-%d')";
                obj2.qrysql = Qry;
                obj2.prepare();
                obj2.execute((_error, result) => {
                    if (_error == 1) {
                        let objv = new RawView_1.RawView(res);
                        objv.prepare({ message: "Data Fetched Successfully!", data: result, status: 200 });
                        objv.execute();
                    }
                    else {
                        let objv = new RawView_1.RawView(res);
                        objv.prepare({ message: "Something Went Wrong With Data Fetch!", status: 502 });
                        objv.execute();
                    }
                });
            }
            else {
                let objv = new RawView_1.RawView(res);
                objv.prepare({ message: "Invalid Session Trying to Access", status: 401 });
                objv.execute();
            }
        });
    }
}
exports.CommonClass = CommonClass;
