<?php 
/****************************************
/*  Author  : Kvvaradha
/*  Module  : FA Customization
/*  E-mail  : admin@kvcodes.com
/*  Version : 1.0
/*  Http    : www.kvcodes.com
*****************************************/
$display =  1;

error_reporting( E_CORE_ERROR | E_CORE_WARNING | E_COMPILE_ERROR | E_ERROR | E_WARNING | E_PARSE | E_USER_ERROR | E_USER_WARNING | E_RECOVERABLE_ERROR );
ini_set('display_errors', $display);

global $db, $db_connections, $last_run, $difference_seconds,$suspend_cron,$last_company,$prev_company,$next_run, $path_to_root,$company_id, $display,$inventry_planning_report;

define('FLOAT_COMP_DELTA', 0.004);
if (!extension_loaded('suhosin')) {
    @ini_set('memory_limit', '-1');
}


$path_to_root = $root = "../..";

include_once($root.'/config_db.php');
include_once($root.'/includes/types.inc');
include_once($root.'/includes/sysnames.inc');
include_once($root.'/modules/KvcodesCRM/cron_time.php');
include_once($root.'/modules/KvcodesCRM//includes/kv_email.inc');

define('DB_DUPLICATE_ERROR', 1062);
define('SQL_MODE', ''); // STRICT_ALL_TABLES,NO_ZERO_IN_DATE ?


// <?php 
//     global $last_run, $difference_seconds, $prev_company, $suspend_cron,$last_company;
//     $last_run = 1583567210 ;
//     $difference_seconds =2;
//     $prev_company =-1;
//     $suspend_cron = false;
//     $last_company =-1;

function write_cron_time(){
   global  $last_run, $difference_seconds,$suspend_cron,$last_company,$prev_company,$next_run;
    $file_path =dirname(__FILE__).'/cron_time.php';
    $file =fopen($file_path, 'w');
    $str='<?php 
    global $last_run, $difference_seconds, $prev_company, $suspend_cron,$last_company,$next_run;
    $last_run = '.$last_run.' ;
    $difference_seconds = '.$difference_seconds.';
    $prev_company = '.$prev_company.';
    $suspend_cron ='.$suspend_cron.';
    $last_company ='.$last_company.';
    $next_run ='.$next_run.';';
    fwrite($file, $str);
}

function get_curr_company($prev_company){
    global $db_connections;
    $exit =false;
    foreach ($db_connections as $key => $value) {
        if($exit){
            return $key;
        }
        if($key == $prev_company){    
            $exit =true;
        }
    }
    return false;
}

function get_last_company(){
    global $db_connections;
    foreach ($db_connections as $key => $value) {
        if(!next($db_connections)){
            return $key;
        }
    }
}
if($next_run <=time()){
    

    $last_company = get_last_company();
    if($prev_company ==-1){
        $curr_company=0;
    }
    else{
        $curr_company =get_curr_company($prev_company);      
    }




    if (time() > ($last_run + $difference_seconds) && $next_run<=time() && $curr_company <=$last_company) {
        $connection = $db_connections[$curr_company];
        $db = mysqli_connect($connection["host"], $connection["dbuser"], $connection["dbpassword"], "", 
        		!empty($connection["port"]) ? $connection["port"] : 3306); // default port in mysql is 3306
        
        

        mysqli_select_db($db, $connection["dbname"]);
        echo "next";
         Invoice_overdue_Emails($curr_company);  

         delete_old_email_logs($curr_company); 
         //delete_old_logs_activities($curr_company); 
        

        if($inventry_planning_report){
            Inventry_planning_Email($curr_company);
            $inventry_planning_report =false;
            write_cron_time();
        }     
            
        mysqli_close($db);
        // $last_run =time();
        // $suspend_cron ='true';
        
            
        write_cron_time();

    }
    else{
    echo "wait 5min to next run ->> ".$next_run;
    }
}
else{
    echo "next run ->> ".$next_run;
}

function count_days_in_range($date1, $date2) {

    $date1      = date_create($date1);
    $date2      = date_create($date2);
    $interval   = date_diff($date1, $date2);
    $days       = $interval->days;
    return $days;
}

function delete_old_email_logs($company_id){
    global $db, $db_connections;
    $tbpref = $db_connections[$company_id]['tbpref'];
    $today =date('Y-m-d');
    $old_email_date=date('Y-m-d', strtotime($today. " - 100 days"));
    $sql ="DELETE FROM ".$tbpref."email_log WHERE date_time<'".$old_email_date."'";
    $days=mysqli_query($db ,$sql);
}  
function delete_old_logs_activities($company_id){
    global $db, $db_connections;
    $tbpref = $db_connections[$company_id]['tbpref'];
    $today =date('Y-m-d');
    $old_activity_date=date('Y-m-d', strtotime($today. " - 100 days"));
    $sql ="DELETE FROM ".$tbpref."kv_crm_log_activities WHERE date_time<'".$old_activity_date."'";
    $days=mysqli_query($db ,$sql);
}   
function Inventry_planning_Email($company_id){
 global $path_to_root, $db;
    $_POST['PARAM_0'] =ALL_NUMERIC;
    $_POST['PARAM_1']=ALL_TEXT;
    $_POST['PARAM_2']='';
    $_POST['PARAM_3']=0;
    $_POST['EMAIL']=1;
   include($path_to_root. "/modules/KvcodesCRM/reports/rep302.php");
}
function Invoice_overdue_Emails($company_id){
    global $db, $db_connections,$last_run, $difference_seconds, $prev_company, $suspend_cron,$last_company,$next_run;

    if(time() > ($last_run + $difference_seconds)){
        $tbpref = $db_connections[$company_id]['tbpref'];
        $today =date('Y-m-d');
        $days="SELECT * FROM ".$tbpref."sys_prefs WHERE name ='invoice_overdue_notification_days'";
        $days=mysqli_query($db ,$days);
        if(mysqli_num_rows($days)>0){
            $days = mysqli_fetch_array($days);
            $next_over_due_date =  date('Y-m-d', strtotime($today. " + ".$days['value']." days"));
        }else{
            $next_over_due_date =  date('Y-m-d', strtotime($today. " + 1 days"));
        }   
          $sql = "SELECT debtor.debtor_no FROM ".$tbpref."debtor_trans as trans, ".$tbpref  ."debtors_master as debtor, ".$tbpref."cust_branch as branch WHERE debtor.debtor_no = trans.debtor_no AND trans.branch_code = branch.branch_code AND trans.type = ".ST_SALESINVOICE." AND (trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount - trans.alloc) > ".FLOAT_COMP_DELTA." AND IF(next_over_due='0000-00-00',trans.due_date <'".$today."',next_over_due='".$today."') ORDER BY due_date DESC ";
        $result= mysqli_query($db ,$sql);
        // $result = db_query($sql, "could not get sales type");
        // $result =mysqli_result($result);
        // display_error(json_encode($))
        if(mysqli_num_rows($result)>0){
            $sql1 = "SELECT trans.trans_no, trans.type, trans.reference, trans.due_date, debtor.debtor_no, trans.branch_code, debtor.name,(trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount) AS total FROM ".$tbpref."debtor_trans as trans, ".$tbpref."debtors_master as debtor,".$tbpref."cust_branch as branch WHERE debtor.debtor_no = trans.debtor_no AND trans.branch_code = branch.branch_code AND trans.type = ".ST_SALESINVOICE." AND (trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount - trans.alloc) > ".FLOAT_COMP_DELTA." AND IF(next_over_due='0000-00-00',trans.due_date <'".$today."',next_over_due='".$today."') ORDER BY due_date DESC LIMIT 10";

            $result1 = mysqli_query($db ,$sql1);
       
            if(mysqli_num_rows($result1)>0){
                while ($myrow = mysqli_fetch_array($result1)) {  
                    //send email here 
                    $contacts = kv_crm_get_branch_contacts($myrow['branch_code'], null, $myrow['debtor_no'], $company_id); 
                    $to =  array('name' => $contacts['name'], 'email' => $contacts['email']);
                    $details = array('contact_firstname' => $contacts['name'],'email' => $contacts['email'],'invoice_number' => $myrow['reference'], 'due_date' => date('d-m-Y', strtotime($myrow['due_date'])));

                    $template = array('slug' => 'invoice-overdue-notice',  'details' => $details );
                    if($to['email'] !=''){
                        $result_mail =  kv_mail($to, '', '', "html", array(), null, false, $template);
                        // $result_mail=false;  
                        if($result_mail) {
                            $update ="UPDATE ".$tbpref."debtor_trans SET next_over_due = '".$next_over_due_date."' WHERE trans_no=".$myrow['trans_no']." AND type =".$myrow['type'];
                            mysqli_query($db ,$update); 
                            $log_activity ="Invoice overdue notification email send to ".$contacts['name'].". Reference NO : ".$myrow['reference'];
                            $Insert ="INSERT INTO ".$tbpref."kv_crm_log_activities (type, activity)VALUES (1,'".$log_activity."')";
                            mysqli_query($db ,$Insert);  
                            
                        }   
                    }else{
                         $update ="UPDATE ".$tbpref."debtor_trans SET next_over_due = '".$next_over_due_date."' WHERE trans_no=".$myrow['trans_no']." AND type =".$myrow['type'];
                            mysqli_query($db ,$update); 
                            $log_activity ="Invoice overdue notification email not send due to empty email address!. Reference NO : ".$myrow['reference'];
                            $Insert ="INSERT INTO ".$tbpref."kv_crm_log_activities (type, activity)VALUES (1,'".$log_activity."')";
                            mysqli_query($db ,$Insert);  
                    }
                }
               
            }
            $last_run =time();
        }else{
            $last_run =time();
            $prev_company=$company_id;
            $inventry_planning_report =true;
            if($company_id ==$last_company) {
                $next_run +=86400;
                $prev_company=-1;
                $last_company=-1;
                $inventry_planning_report =true;
            } 
        }
    }
}

function add_log_activity(){

}
function kv_crm_get_branch_contacts($branch_code, $action=null, $customer_id=null, $company_id=0){
    global $db, $db_connections;
    $tbpref = $db_connections[$company_id]['tbpref'];
    $defs = array('cust_branch.'.$action, 
                'customer.'.$action,
                'cust_branch.general',
                'customer.general');

    $sql = "SELECT p.*, r.action, r.type, CONCAT(r.type,'.',r.action) as ext_type 
        FROM "
            .$tbpref."crm_persons p,"
            .$tbpref."crm_contacts r
        WHERE r.person_id=p.id AND ((r.type='cust_branch' 
            AND r.entity_id=".mysqli_real_escape_string($db, $branch_code).')';
    if($customer_id) {
        $sql .= " OR (r.type='customer' AND r.entity_id=".mysqli_real_escape_string($db, $customer_id).")";
    }
    $sql .= ')';
    
    if ($action)
        $sql .= ' AND (r.action='.mysqli_real_escape_string($db, $action).')';
    // echo $sql;
    
    $res = mysqli_query($db, $sql);

    // $results = array($db, $sql);
     if(mysqli_num_rows($res)>0){
    while($contact = mysqli_fetch_array($res))
        $results[] = $contact;
        }
    if ($results) {
        // select first available contact in $defs order
        foreach($defs as $type) {
            if ($n = array_search_value($type, $results, 'ext_type'))
                return $n;
        }
        return null;
    }
    return $results;
}
function array_search_value($needle, $haystack, $valuekey=null)
{
    if (is_array($haystack)) {
        foreach($haystack as $key => $value) {
            $val = isset($valuekey) ? @$value[$valuekey] : $value;
            if ($needle == $val){
                return $value;
            }
        }
    }
    return null;
}
function price_format($number) {
    return number_format($number, 2);
}

/*if(!function_exists('Insert')) {
    function Insert($table_name, $data){

          global $db, $db_connections, $curr_company;
     // echo $curr_company;
    $tbpref = $db_connections[$curr_company]['tbpref'];

        $sql0 = "INSERT INTO ".$tbpref."".$table_name."(";
        $sql1 = " VALUES (";
        foreach($data as $key=>$value){
            $sql0 .= "`".$key."`,";
            if(is_array($value)) { 
                if($value[1] == 'date')             
                    $sql1 .=  mysqli_real_escape_string(date2sql($value[0])).",";
                if($value[1] == 'float')
                    $sql1 .= $value.",";
            }else 
                $sql1 .= mysqli_real_escape_string($value).",";
        }
        $sql0 = substr($sql0, 0, -1).")";
        $sql1 = substr($sql1, 0, -1).")";
        //display_notification($sql0.$sql1);
        mysqli_query($sql0.$sql1, "Could not insert data to table {$table_name}");
        // return  mysqli_insert_id();
    }
}*/
if(!function_exists('Insert')) {
    function Insert($table_name, $data){
        global $db, $db_connections;
        $company_id=0;
        $tbpref = $db_connections[$company_id]['tbpref'];


        $sql0 = "INSERT INTO ".$tbpref.$table_name."(";
        $sql1 = " VALUES (";
        foreach($data as $key=>$value){
            $sql0 .= "`".$key."`,";
            if(is_array($value)) { 
                if($value[1] == 'date')             
                    $sql1 .=  db_escape(date2sql($value[0])).",";
                if($value[1] == 'float')
                    $sql1 .= $value.",";
            }else 
                $sql1 .= db_escape($value).",";
        }
        $sql0 = substr($sql0, 0, -1).")";
        $sql1 = substr($sql1, 0, -1).")";
        //display_notification($sql0.$sql1);
        db_query($sql0.$sql1, "Could not insert data to table {$table_name}");
        return  db_insert_id();
    }
}
//kv email function---------------------------------------
function kv_mail($to, $subject, $message, $type="plain", $attachments=array(), $reply_to=null,$phpmailer=false, $template=false){
    global $path_to_root;
    $disable_all_email = GetSingleValue('kv_crm_option', 'option_value', array('option_name' => 'disable_all_email'));
   
 if($disable_all_email != 1){
    $company = get_company_prefs();
    $user = $_SESSION["wa_current_user"]->name;

     $sender = "<br><br><span>" .$user . "<br>" . $company['coy_name'] . "<br>" . $company['postal_address'] . "<br>" . $company['email'] . "<br></span>" . $company['phone'];

     if(!isset($to['email']) || filter_var($to['email'], FILTER_VALIDATE_EMAIL) == false)
        return false;

    $sent = $try = 0;

    if(file_exists($path_to_root.'/modules/KvcodesCRM/includes/PHPMailer/src/PHPMailer.php')) {
        require_once($path_to_root.'/modules/KvcodesCRM/includes/PHPMailer/src/PHPMailer.php');
        require_once($path_to_root.'/modules/KvcodesCRM/includes/PHPMailer/src/SMTP.php');
        require_once($path_to_root.'/modules/KvcodesCRM/includes/PHPMailer/src/Exception.php');
        $mail = new PHPMailer\PHPMailer\PHPMailer();
        $details = GetSingleValue('kv_crm_option', 'option_value', array('option_name' => 'smtp'));
        if ($details['user'] && $details['pass'] && $details['host'] && $details['port'] && $details['type']) {               
            $mail->Host   = $details['host'];
            $mail->Port     = $details['port'];
            $mail->SMTPSecure  = $details['type'];
            $mail->Username  = $details['user'];
            $mail->Password= $details['pass'];
        } else {
               
            $details = GetSingleValue('sys_prefs', 'value', array('name' => 'smtp'), null, null, '0_');
            if (isset($details) && is_array($details) && $details['user'] && $details['pass'] && $details['host'] && $details['port'] && $details['type']){
                $mail->Host     = $details['host'];
                $mail->Port     = $details['port'];
                $mail->SMTPSecure   = $details['type'];
                $mail->Username  = $details['user'];
                $mail->Password = $details['pass'];
            } else {
                    //display_warning(_("No SMTP Credentials found to send mail"));
                    return false;
            }
        }
        try {
            //Server settings
            $mail->SMTPDebug = 0; // debugging: 1 = errors and messages, 2 = messages only
           // $mail->SMTPDebug = SMTP::DEBUG_SERVER;                      // Enable verbose debug output
            $mail->isSMTP();

            //Recipients           
            $mail->setFrom($_SESSION['wa_current_user']->email, $_SESSION['wa_current_user']->name);
            $mail->addAddress($to['email'], $to['name']);     // Add a recipient  $to['email'], $to['name']
            if($reply_to != null){
                if(is_array($reply_to))
                    $mail->addReplyTo($reply_to['email'], $reply_to['name']);
                else
                    $mail->addReplyTo($reply_to);
            }
           // $mail->addCC('cc@example.com');
            //$mail->addBCC('bcc@example.com');

            // Attachments
            if(!empty($attachments)){
                foreach ($attachments as $key => $value) {   
                    if(!is_numeric($key))    
                        $mail->addAttachment($value, $key);   
                    else
                        $mail->addAttachment($value);   
                }           
            }   
          
            // Content
            $mail->isHTML(true);        

            if($template){
                $template_des = GetRow('kv_crm_email_templates', array('slug' => $template['slug']));
                $tempType = $template_des['type'];
                if(isset($template['doc_no']))
                    $template_des['doc_no'] = $template['doc_no'];
                if(isset($template['details'])){
                    foreach($template['details'] as $key => $detail)
                        $template_des[$key] = $detail;
                }
                $template_des['emailtype'] = $tempType;
                
                $template_final = KvcodesCRM_ContentReplacement($template_des);
                $subject = $template_final['subject'];                
                $message = htmlspecialchars_decode($template_final['description']); 
            }

            $mail->Subject = $subject;
            $mail->Body    = $message. $sender;                     
            

            if($mail->send())
                $sent++;
        } catch (Exception $e) {
            echo "Message could not be sent. Mailer Error: {$mail->ErrorInfo}";           
        }
        
        Insert('email_log', array('name' => $to['name'], 'user_id' => $_SESSION["wa_current_user"]->user, 'email' => $to['email'], 'slug' => $template['slug'], 'subject' => $subject, 'status' =>$sent));

        return $sent;

    } else {        
        $emails = "";
        $mail = new email(str_replace(",", "", $company['coy_name']), $company['email']);        
      
        if(is_array($to)){
            $tomail = $to['email'];
            $to = str_replace(",", "", $to['name'].' ')." <" . $to['email'] . ">";
        } else {
            $to = ' No Name'.' <'. $to.' > ';
             $tomail = $to;
        }
       
        $mail->to($to);
        
        if($template){
            $template_des = GetRow('kv_crm_email_templates', array('slug' => $template['slug']));
            $tempType = $template_des['type'];
            if(isset($template['doc_no']))
                $template_des['doc_no'] = $template['doc_no'];
            if(isset($template['details'])){
                foreach($template['details'] as $key => $detail)
                    $template_des[$key] = $detail;
            }
            $template_des['emailtype'] = $tempType;
            
            $template_final = KvcodesCRM_ContentReplacement($template_des);
            $subject = $template_final['subject'];
            $mail->subject($template_final['subject']);
           
            $message = htmlspecialchars_decode($template_final['description']).$sender; 

            $mail->html($message);

        } else {
            $mail->subject($subject);
            if($type == 'plain')
                $mail->text($message . $sender);
            else
                $mail->html($message . $sender);
        }
        if(!empty($attachments)){
            foreach ($attachments as $key => $value) {
                $mail->attachment($value, $key);
            }           
        }
         //display_error($message);
        $emails .= " " . $tomail;
        if ($mail->send()) 
            $sent++;

        Insert('email_log', array('name' => $to['name'], 'user_id' => $_SESSION["wa_current_user"]->user, 'email' => $to['email'], 'slug' => $template['slug'], 'subject' => $subject, 'status' =>$sent));
       return $sent;
    }     
  }
    return true;       
}


function get_company_prefs(){
     global $db, $db_connections, $curr_company;
     // echo $curr_company;
    $tbpref = $db_connections[$curr_company]['tbpref'];
	$res = mysqli_query($db, "SELECT * FROM ".$tbpref."sys_prefs");
	$final = [];
     if(mysqli_num_rows($res)>0){
	while($row = mysqli_fetch_array($res))
		$final[$row['name']] = $row['value'];
	}
	return $final;
}

function GetRow($table_name, $conditions = null, $order_by = null,  $group_by= null){
      global $db, $db_connections,$curr_company;
    $tbpref = $db_connections[$curr_company]['tbpref'];
        
        $sql0 = "SELECT * FROM ".$tbpref."".$table_name." WHERE 1=1";
       
        if($conditions != null) {
            foreach($conditions as $key=>$value){
                if(is_array($value)) { 
                    $sql0 .= " AND ".$key." ".$value[1]." ". mysqli_real_escape_string($value[0]);
                } else
                    $sql0 .= " AND {$key} = '${value}'";
            }
        }

        if($group_by != null) {
                $sql0 .=" GROUP BY ";
                foreach($group_by as $value){
                    $sql0 .= " ${value}";
                }
        }

        if($order_by != null) {
            $sql0 .=" ORDER BY ";
            foreach($order_by as $key=>$value){
                $sql0 .= " {$key} ${value}";
            }
        }
    	
    	$sql0 .= ' LIMIT 1'; 
        $result = mysqli_query($db, $sql0);
    	$data = mysqli_fetch_array($result);
        
        return $data;
    }


function GetSingleValue($tablename, $column_single, $conditions=null, $order_by = null, $group_by= null, $show_sql=false){
      global $db, $db_connections, $curr_company;
    $tbpref = $db_connections[$curr_company]['tbpref'];
   
        $sql0 = "SELECT ".$column_single." FROM ".$tbpref."".$tablename." WHERE 1=1";
        
        if($conditions){
            foreach($conditions as $key=>$value){
                $sql0 .= " AND {$key} = '${value}'";
            }
        }
        
        if($group_by != null) {
    			$sql0 .=" GROUP BY ";
    			foreach($group_by as $value){
    				$sql0 .= " ${value}";
    			}
    		}

        if($order_by != null) {
            $sql0 .=" ORDER BY ";
            foreach($order_by as $key=>$value){
                $sql0 .= " {$key} ${value}";
            }
        }
        if($show_sql)
           return $sql0;
       //echo $sql0;
        $result = mysqli_query($db, $sql0);
        $row = mysqli_fetch_array($result);

    		$data = @unserialize(base64_decode($row[0]));
    		if ($data !== false) {
    		    return unserialize(base64_decode($row[0]));
    		}else
    			return $row[0];
    }

function KvcodesCRM_ContentReplacement($template){
    global $path_to_root;
    $protocol = strtolower(substr($_SERVER["SERVER_PROTOCOL"],0,5)) == 'https://' ? 'https://' : 'http://';
    $fa_url= $protocol.$_SERVER['SERVER_NAME'].dirname($_SERVER['PHP_SELF']);
    $pos = strpos($fa_url, '/modules/KvcodesCRM');
    $fa_url =  substr($fa_url, 0, $pos);
    $crm_url =  $fa_url.'/index.php?application=crm';
    $task_url =  $fa_url.'/modules/KvcodesCRM/tasks';
    $project_link =  $fa_url.'/modules/KvcodesCRM/projects';
    $lead_link =  $fa_url.'/modules/KvcodesCRM/manage/leads.php';
    $reset_url = $fa_url.'/index.php?reset=1';
    $company = get_company_prefs();
    $user = $_SESSION["wa_current_user"]->name;
    switch($template['emailtype']) {
       case 1 :  //  Leads  
        $template['description'] = str_replace("{lead_assigned}",$template['real_name'],$template['description']);
        $template['description'] = str_replace("{lead_name}",$template['lead_name'],$template['description']);
        $template['description'] = str_replace("{lead_email}",$template['lead_email'],$template['description']);
        $template['description'] = str_replace("{lead_link}",$lead_link,$template['description']);
        $template['description'] = str_replace("{lead_phone}",$template['phone'],$template['description']);
        break;

        case 2 : // Projects

        $template['description'] = str_replace("{project_link}",$project_link,$template['description']);
        $template['description'] = str_replace("{contact_firstname}",$template['name'],$template['description']);
        $template['description'] = str_replace("{contact_lastname}",$template['name2'],$template['description']);
        $template['description'] = str_replace("{staff_firstname}",$template['name'],$template['description']);
        $template['description'] = str_replace("{staff_lastname}",$template['name2'],$template['description']);
        $template['description'] = str_replace("{project_name}",$template['project_name'],$template['description']);
        $template['description'] = str_replace("{project_start_date}",$template['project_start_date'],$template['description']);
        $template['description'] = str_replace("{project_status}",$template['project_status'],$template['description']);
        break ; 

        case 3 :  // Customers
        $template['description'] = str_replace("{contact_firstname}",$template['name'],$template['description']); 
        $template['description'] = str_replace("{contact_lastname}",(isset($template['name2']) ? $template['name2']:''),$template['description']);
        $template['description'] = str_replace("{contact_name}",(isset($template['contact_name']) ? $template['contact_name']:''),$template['description']);
         $template['description'] = str_replace("{email}",$template['email'],$template['description']);
        $template['description'] = str_replace("{phone}",$template['phone'],$template['description']);


        // $template['description'] = str_replace('{login_password}',$template['login_password'],$template['description']);

        break;

        case 4 :  // Suppliers
        $template['description'] = str_replace("{contact_firstname}",$template['name'],$template['description']); 
        $template['description'] = str_replace('{contact_lastname}',$template['name2'],$template['description']);
        break ; 

        case 5 :  // Tasks
         $template['description'] = str_replace("{staff_firstname}",$template['real_name'],$template['description']);
         if(!isset($template['name2']))
            $template['name2'] = '';
         $template['description'] = str_replace("{staff_lastname}",$template['name2'],$template['description']);
         $template['description'] = str_replace("{task_name}",$template['task_name'],$template['description']);
          if(!isset($template['task_startdate']))
            $template['task_startdate'] = '';
          $template['description'] = str_replace("{task_startdate}",$template['task_startdate'],$template['description']);
          if(!isset($template['task_duedate']))
            $template['task_duedate'] = '';
          $template['description'] = str_replace("{task_duedate}",$template['task_duedate'],$template['description']);
          if(!isset($template['task_priority']))
            $template['task_priority'] = '';
          $template['description'] = str_replace("{task_priority}",$template['task_priority'],$template['description']);
          if(!isset($task_url))
            $task_url = '';
          $template['description'] = str_replace("{task_url}",$task_url,$template['description']);
          if(!isset($template['task_status']))
            $template['task_status'] = '';
          $template['description'] = str_replace("{task_status}",$template['task_status'],$template['description']);
          if(!isset($template['task_comment']))
            $template['task_comment'] = '';
           $template['description'] = str_replace("{task_comment}",$template['task_comment'],$template['description']);
         $template['subject'] = str_replace('{task_name}',$template['task_name'],$template['subject']);
        break ; 

        case 6 :  // Opportunities

            $template['description'] = str_replace("{staff_firstname}",$template['real_name'],$template['description']);
         if(!isset($template['name2']))
            $template['name2'] = '';
         $template['description'] = str_replace("{staff_lastname}",$template['name2'],$template['description']);
         $template['description'] = str_replace("{opportunity_name}",$template['opportunity_name'],$template['description']);
         $template['description'] = str_replace("{expected_close_date}",$template['expected_close_date'],$template['description']); 
         $template['description'] = str_replace("{probability}",$template['probability'],$template['description']); 
         $template['subject'] = str_replace('{opportunity_name}',$template['opportunity_name'],$template['subject']);
        break ; 

        case 7 :  // Invoices
           $template['description'] = str_replace("{contact_firstname}",$template['name'],$template['description']); 
           $template['description'] = str_replace('{contact_lastname}',$template['name2'],$template['description']);
           $template['description'] = str_replace('{invoice_number}',(isset($template['reference']) ? $template['reference']: ''),$template['description']);
           $template['description'] = str_replace('{due_date}',(isset($template['due_date']) ? $template['due_date']: ''),$template['description']);
           $template['subject'] = str_replace('{invoice_number}',$template['doc_no'],$template['subject']);
        break ; 

        case 33 :  // Estimates
           $template['description'] = str_replace("{contact_firstname}",$template['name'],$template['description']); 
           $template['description'] = str_replace('{contact_lastname}',$template['name2'],$template['description']);
           $template['description'] = str_replace('{estimate_number}',$template['doc_no'],$template['description']);
           $template['subject'] = str_replace('{estimate_number}',$template['doc_no'],$template['subject']);

        break ; 

        case 34 :  // Proposals
            $template['description'] = str_replace("{contact_firstname}",$template['name'],$template['description']); 
            $template['description'] = str_replace('{contact_lastname}',$template['name2'],$template['description']);
            $template['description'] = str_replace('{proposal_number}',$template['doc_no'],$template['description']);
            $template['description'] = str_replace('{proposal_open_till}',$template['proposal_open_till'],$template['description']);
            $template['description'] = str_replace('{proposal_proposal_to}',$template['proposal_proposal_to'],$template['description']);
            $template['subject'] = str_replace('{proposal_number}',$template['doc_no'],$template['subject']);
        break ; 

        case 10 :  // Credit Note

        break ; 

        case 11 :  // Staff
            $template['description'] = str_replace('{staff_fullname}',(isset($template['real_name'])?$template['real_name']:''),$template['description']);          
            $template['description'] = str_replace('{staff_email}',(isset($template['email']) ? $template['email'] : '' ),$template['description']);          
            $template['description'] = str_replace('{password}',(isset($template['password']) ? $template['password'] : '' ),$template['description']);          
            
            $template['subject'] = str_replace('{month}',(isset($template['month']) ? kv_month_name_by_id($template['month']) : '' ),$template['subject']);  

            $template['description'] = str_replace('{month}',(isset($template['month']) ? kv_month_name_by_id($template['month']) : '' ),$template['description']);          
            $template['description'] = str_replace('{empl_name}',(isset($template['empl_name'])? $template['empl_name']:''),$template['description']);     
            $template['description'] = str_replace('{activate}',(isset($activation_url) ? $activation_url : ''),$template['description']);            
        break ; 

        case 12 :  // Ticket
            $template['description'] = str_replace('{customer_name}',$template['customer_name'],$template['description']);
            $template['description'] = str_replace('{subject}',$template['subject'],$template['description']);
            $template['description'] = str_replace('{message}',(isset($template['message']) ? $template['message'] : ''),$template['description']);
            $template['description'] = str_replace('{comment}',(isset($template['comment'])?$template['comment']:''),$template['description']);   
            $template['description'] = str_replace('{ticket_id}',$template['ticket_id'],$template['description']); 

            $template['description'] = str_replace('{t_id}',(isset($ticket_url) ? $ticket_url : ''),$template['description']);
        break ; 

            case 14 :  //  subscriptions  
        $template['description'] = str_replace("{customer_name}",$template['customer_name'],$template['description']);
        $template['description'] = str_replace("{company_name}",$db_connections[$template['company_id']]['name'],$template['description']);
        $template['description'] = str_replace("{pack_name}",$template['pack_name'],$template['description']);
        $template['description'] = str_replace("{pack_expiration_date}",date('d-m-Y', strtotime($template['to_date'])),$template['description']);

        break;            
        
    } 
    $template['description'] = str_replace("{fa_url}",$fa_url,$template['description']); 
    $template['description'] = str_replace("{coy_name}",$company['coy_name'],$template['description']); 
    $template['description'] = str_replace('{crm_url}',$crm_url,$template['description']);
    $template['description'] = str_replace('{email_signature}',$user,$template['description']);
    
    return $template;  
}


function db_query($sql, $err_msg=null, $show_sql=false)
{
    global $db, $SysPrefs, $sql_queries, $Ajax, $db_connections, $db_last_inserted_id;

    // set current db prefix
    $comp = isset($_SESSION["wa_current_user"]->cur_con) ? $_SESSION["wa_current_user"]->cur_con : 0;
    $cur_prefix = $db_connections[$comp]['tbpref'];
    $sql = str_replace(TB_PREF, $cur_prefix, $sql);

    if ($SysPrefs->show_sql)
    {
        $Ajax->activate('footer_debug');
        $sql_queries .= "<pre>$sql</pre>\n<hr>";
    }
    if($show_sql){
        display_error($sql);
        exit;
    }

    db_profile(); // mysql profiling

    $retry = MAX_DEADLOCK_RETRY;
    do {
        $result = mysqli_query($db, $sql);
        if (mysqli_errno($db) == 1213)  { // deadlock detected
            sleep(1); $retry--;
        } else
            $retry = 0;
    } while ($retry);

    db_profile($sql);

    if($SysPrefs->sql_trail) {
        $db_last_inserted_id = mysqli_insert_id($db);   // preserve in case trail insert is done
        if ($SysPrefs->select_trail || (strstr($sql, 'SELECT') === false)) {
            mysqli_query($db, "INSERT INTO ".$cur_prefix."sql_trail
                (`sql`, `result`, `msg`)
                VALUES(".db_escape($sql).",".($result ? 1 : 0).",
                ".db_escape($err_msg).")");
        }
    }

    if ($err_msg != null || $SysPrefs->go_debug) {
        $exit = $err_msg != null;
        if (function_exists('xdebug_call_file'))
            $err_msg = '<br>At file '.xdebug_call_file().':'.xdebug_call_line().':<br>'.$err_msg;
        check_db_error($err_msg, $sql, $exit);
    }
    return $result;
}

function db_fetch_row($result)
{
    $ret = mysqli_fetch_row($result);
    return ($ret === null ? false : $ret);
}

function db_fetch_assoc($result)
{
    $ret = mysqli_fetch_assoc($result);
    return ($ret === null ? false : $ret);
}

function db_fetch($result)
{
    $ret = mysqli_fetch_array($result);
    return ($ret === null ? false : $ret);
}

function db_seek(&$result,$record)
{
    return mysqli_data_seek($result, $record);
}

function db_free_result($result)
{
    if ($result)
        mysqli_free_result($result);
}

function db_num_rows($result)
{
    return mysqli_num_rows($result);
}

function db_num_fields($result)
{
    return mysqli_num_fields($result);
}

function db_escape($value = "", $nullify = false)
{
    global $db;

    $value = @html_entity_decode($value, ENT_QUOTES, $_SESSION['language']->encoding=='iso-8859-2' ? 'ISO-8859-1' : $_SESSION['language']->encoding);
    $value = html_specials_encode($value);

    //reset default if second parameter is skipped
    $nullify = ($nullify === null) ? (false) : ($nullify);

    //check for null/unset/empty strings
    if ((!isset($value)) || (is_null($value)) || ($value === "")) {
        $value = ($nullify) ? ("NULL") : ("''");
    } else {
        if (is_string($value)) {
            $value = "'" . mysqli_real_escape_string($db, $value) . "'";
            //value is a string and should be quoted; 
        } else if (!is_numeric($value)) {
            //value is not a string nor numeric
            display_error("ERROR: incorrect data type send to sql query");
            echo '<br><br>';
            exit();
        }
    }
    return $value;
}
