<?php
/****************************************
/*  Author  : Kvvaradha
/*  Module  : Kvcodes CRM
/*  E-mail  : admin@kvcodes.com
/*  Version : 1.1
/*  Http    : www.kvcodes.com
*****************************************/
set_ext_domain('modules/KvcodesCRM');
/* $myFile = '../themes/'.$theme_folder.'/style.css';
$lines = file($myFile);//file in to an array
$theme_name = 'Theme Name';
$theme_n = array_filter($lines, function($var) use ($theme_name){ return preg_match("/\b$theme_name\b/i", $var); }); */
include_once($path_to_root . "/modules/KvcodesCRM/includes/kvcodes.inc");
//include_once($path_to_root . "/modules/KvcodesCRM/includes/class.mail.inc");
//include_once($path_to_root . "/modules/KvcodesCRM/includes/kv_email.inc");
include_once($path_to_root . "/modules/KvcodesCRM/includes/custom_function.inc");
include_once($path_to_root . "/modules/KvcodesCRM/includes/tax_calc.inc");
include_once($path_to_root . "/modules/KvcodesCRM/includes/ui/crm_simple_curd_class.inc");
include_once($path_to_root . "/includes/types.inc");
/*
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");

use PHPMailer\PHPMailer\PHPMailer;
use PHPMailer\PHPMailer\SMTP;
use PHPMailer\PHPMailer\Exception;
*/
include_once($path_to_root . "/modules/KvcodesCRM/includes/import_export.inc");


// document inheritance
$crm_document_child_types = array(
        ST_SALESESTIMATE => ST_SALESORDER,
        ST_SALESPROPOSAL => ST_SALESORDER,
);
 if(!defined('ST_LEADS'))
    define('ST_LEADS', 43);
 if(!defined('ST_OPPORTUNITIES'))
    define('ST_OPPORTUNITIES',44);
 if(!defined('ST_TASKS'))
    define('ST_TASKS', 45);
 if(!defined('ST_PROJECT'))
    define('ST_PROJECT', 46);
 if(!defined('ST_EMPLOYEE'))
    define('ST_EMPLOYEE', 99);
if(!defined('ST_CUSTOMER_BRANCH'))
    define('ST_CUSTOMER_BRANCH', 47);


global $systypes_array; 

$systypes_array[ST_EMPLOYEE] =  'Staff';
$systypes_array[ST_CUSTOMER] =  'Customer';
$systypes_array[ST_SUPPLIER] =  'Supplier';
$systypes_array[ST_LEADS] =  'Leads';
$systypes_array[ST_OPPORTUNITIES] =  'Opportunities';
$systypes_array[ST_TASKS] =  'Tasks';
$systypes_array[ST_PROJECT] =  'Projects';


function crm_get_child_type($type)
{
    global $crm_document_child_types;
    return isset($crm_document_child_types[$type]) ? $crm_document_child_types[$type] : 0;
}

function crm_get_parent_type($type)
{
    global $crm_document_child_types;
    $child = array_search($type, $crm_document_child_types);
    return $child ? $child : 0;
}

global $crm_priorities_list, $billing_types ; 

$billing_types = array( 1 => _("Fixed"), 2 => _("Project Hours"),  3 => _("Task Hours") );

//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 (isset($details) && is_array($details) && $details['user'] && $details['pass'] && $details['host'] && $details['port'] && $details['type']) {               
            $mail->Host   = $details['host'];
            $mail->Hostname   = $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->Hostname     = $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 {
           // display_error("phpmail");
            //Server settings
            $mail->SMTPDebug = $details['enable_SMTP_debugging']; // debugging: 1 = errors and messages, 2 = messages only
           // $mail->SMTPDebug = SMTP::DEBUG_SERVER;                      // Enable verbose debug output
            $mail->SMTPAuth = true;
            $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;                     
            
         //   display_error($message.$sender);
            if($mail->send())
                $sent++;
        } catch (Exception $e) {
            display_error("Message could not be sent. Mailer Error: {$mail->ErrorInfo}");           
        }
        
       $res = Insert('email_log', array('name' => $to['name'], 'user_id' => $_SESSION["wa_current_user"]->user, 'email' => $to['email'], 'slug' => $template['slug'], 'subject' => $subject, 'status' =>$sent));
       //display_error($res);
        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 render_email_content($message, $signature) {
    ob_start();
    echo $message;
    return ob_get_contents();
}
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';
    $ticket_url = $fa_url.'/modules/KvcodesCRM/tickets/ticket_replies.php?t_id=';
    $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 15 :  // 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('{ticket_url}',(isset($ticket_url) ? $ticket_url : ''),$template['description']);
        // break ; 



            $template['description'] = str_replace('{customer_name}',$template['customer_name'],$template['description']);
            $template['description'] = str_replace('{subject}',$template['subject'],$template['description']);
            $template['description'] = str_replace('{ticket_message}',(isset($template['ticket_message']) ? $template['ticket_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('{ticket_subject}',$template['ticket_subject'],$template['description']); 
            $template['description'] = str_replace('{ticket_department}',$template['ticket_department'],$template['description']); 
            $template['description'] = str_replace('{ticket_priority}',$template['ticket_priority'],$template['description']); 

          //  $template['description'] = str_replace('{t_id}',(isset($template['ticket_id']) ? $template['ticket_id'] : ''),$template['description']);
            $template['description'] = str_replace('{ticket_url}',(isset($ticket_url) ? $ticket_url.$template['ticket_id'] : ''),$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 seoUrl($string, $pageID=null) {
    $string = trim($string, ' ');
    //Lower case everything
    $string = strtolower($string);
    //Make alphanumeric (removes all other characters)
    $string = preg_replace("/[^a-z0-9_\s-]/", "", $string);
    //Clean up multiple dashes or whitespaces
    $string = preg_replace("/[\s-]+/", " ", $string);
    //Convert whitespaces and underscore to dash
    $string = preg_replace("/[\s_]/", "-", $string);

    $page_exist = GetSingleValue('kv_crm_email_templates', 'id', array('slug' => $string)); 
    if($page_exist > 0 && $pageID != null && $page_exist != $pageID)   
        $string = $string.'-2';
    
    return $string;
}

function kv_crm_get_branch_contacts($branch_code, $action=null, $customer_id=null ){
    $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 "
            .TB_PREF."crm_persons p,"
            .TB_PREF."crm_contacts r
        WHERE r.person_id=p.id AND ((r.type='cust_branch' 
            AND r.entity_id=".db_escape($branch_code).')';
    if($customer_id) {
        $sql .= " OR (r.type='customer' AND r.entity_id=".db_escape($customer_id).")";
    }
    $sql .= ')';
    
    if ($action)
        $sql .= ' AND (r.action='.db_escape($action).')';
    
    $res = db_query($sql, "can't retrieve branch contacts");

    $results = array();
    while($contact = db_fetch($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 CrmGetCustomerContact($customer_id) {
    $sql = "SELECT t.*, p.*, r.id as contact_id FROM ".TB_PREF."crm_persons p,".TB_PREF."crm_categories t, ".TB_PREF."crm_contacts r WHERE r.type=t.type AND r.action=t.action AND r.person_id=p.id AND t.type='customer' AND r.entity_id=".db_escape($customer_id)." ORDER BY contact_id LIMIT 1";

    $res = db_query($sql, "can't retrieve branch contacts");

    $results = array();
    if($contact = db_fetch($res))
        $results = $contact;
    return $results;
}

function kvcodes_crm_backup_hrmtables(){
    
    global $db_connections; 

    $prefix = $db_connections[$_SESSION["wa_current_user"]->company]["tbpref"]; 

    $tables = array();
    $result = db_query('SHOW TABLES');
	$end_char_count = strlen($prefix);	
    while($row = db_fetch_row($result)) {
        if(strpos($row[0], $prefix.'kv_crm') !== false)
            $tables[] = substr($row[0], $end_char_count);
    }

   // print_r($tables);
    //exit;
    $return = "# MySQL dump of database '".$db_connections[$_SESSION["wa_current_user"]->company]["name"]."' on host '".$db_connections[$_SESSION["wa_current_user"]->company]["host"]."'
# Backup Date and Time: ".date('Y-m-d h:i a')."
# Module name : Kvcodes CRM
# Module Author : Kvvaradha
# http://www.kvcodes.com
# Company: ".$db_connections[$_SESSION["wa_current_user"]->company]["name"]."
# User : ".$_SESSION["wa_current_user"]->name."
# Compatibility: ".get_company_pref('version_id')."

SET SQL_MODE='';

# Table Backups ".$db_connections[$_SESSION["wa_current_user"]->company]["dbname"]."

#------------------------------------------------------------------------------------
    ";

    //cycle through
    foreach($tables as $table)  {

        $table_exist = db_query("SHOW TABLES LIKE '".$prefix.$table."'");
        $tbl_ext = db_num_rows($table_exist) > 0;
        
        if($tbl_ext){
            $result = db_query('SELECT * FROM '.$prefix.$table);
            $num_fields = db_num_fields($result);
            
            $return.= "### Structure of table `".$table."` ### \n\n DROP TABLE IF EXISTS 0_".$table.";";
            $row2 = db_fetch_row(db_query('SHOW CREATE TABLE '.$prefix.$table));
            $return.= "\n\n".$row2[1].";\n\n";
            $kv = 0; 
            for ($i = 0; $i < $num_fields; $i++) {
                while($row = db_fetch_row($result)){
                    if($kv == 0)
                        $return .= 'INSERT INTO 0_'.$table.' VALUES(';
                    else
                        $return .= "\n (";
                    for($j=0; $j < $num_fields; $j++) {
                        $row[$j] = addslashes($row[$j]);
                        $row[$j] = preg_replace("#\n#", "\\n", $row[$j]);
                        if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }
                        if ($j < ($num_fields-1)) { $return.= ','; }
                    }
                    if($kv == 100){
                        $return .= "); \n";
                        $kv = 0;
                    }
                    else{
                        $return .= "),";
                        $kv++; 
                    }                    
                }
            }
            if(substr($return , -1) == ',')
                $return = substr($return, 0,-1).';';
            $return.="\n\n # -------------------------------------------------------------------------------------\n\n";
        }        
    }

    $return = str_replace($prefix, '0_', $return);

    //save file
    $handle = fopen(dirname(dirname(__FILE__)).'/backups/kvcodescrm-'.time().'.sql','w+');
    fwrite($handle,$return);
    fclose($handle);
}


function crm_get_next_trans_no ($trans_type){
    
    $sql = "SELECT MAX(`order_no`) as last_no FROM ".TB_PREF."kv_crm_sales_orders WHERE `trans_type`=".db_escape($trans_type);

    $result = db_query($sql,"The next transaction number for $trans_type could not be retrieved");
    $myrow = db_fetch_row($result);

    return $myrow[0] + 1;
}

//-----------------------------------------------------------------------------------
if(!function_exists('kv_radio')) {
	function kv_radio($label, $name, $value, $selected=null, $submit_on_change=false, $disabled=''){
		if (!isset($selected))
			$selected = get_post($name) == $value;

		if ($submit_on_change === true)
			$submit_on_change = 
				"JsHttpRequest.request(\"_{$name}_update\", this.form);";

		return "<input type='radio' {$disabled} name=$name value='$value' ".($selected ? "checked":'')
			.($submit_on_change ? " onclick='$submit_on_change'" : '')
			.">".($label ? $label : '');
	}
}

if(!function_exists('kv_radio_row')) {
	function kv_radio_row($label, $name, $value, $selected=null, $submit_on_change=false, $disabled=''){		
		echo '<tr> <td>'.kv_radio($label, $name, $value, $selected, $submit_on_change, $disabled).'</td></tr>';
	}
}


//--------------------------------------------------------------------------------------
// Common functions
//--------------------------------------------------------------------------------------
$crm_salutation = array(
    1 => _('Mr.'),  
    2 => _('Ms.'), 
    3 => _('Mrs.'), 
    4 => _('Dr.'), 
    5 => _('Prof.'), 
    6 => _('Other.'), 
);
function  kv_crm_salutation_list_row($label, $name, $selected_id=null, $submit_on_change=false) {
    global $crm_salutation;
        
    echo "<tr><td class='label'>$label</td><td>";
    $options = array(
        'select_submit'=> $submit_on_change
    );
    echo array_selector($name, $selected_id, $crm_salutation, $options);
    echo "</td></tr>\n";
}

function get_user_name($id) {   
    $sql = "SELECT real_name FROM ".TB_PREF."users WHERE id=".db_escape($id);
    $result = db_query($sql, "could not get credit name");
    $row = db_fetch_row($result);
    return $row[0];
}

function get_cust_status($id) { 
    $sql = "SELECT status FROM ".TB_PREF."status WHERE status_type=3 AND status_id=".db_escape($id);
    $result = db_query($sql, "could not get credit name");
    $row = db_fetch_row($result);
    return $row[0];
}

function sa_get_currencies(){
    $currency=array();
    $sql = "SELECT curr_abrev,currency FROM ".TB_PREF."currencies";
    $result = db_query($sql, "could not get credit name");
    while($person = db_fetch($result)) {
        $currency[] = $person;
    }
    return $currency;
}

function sa_get_tax_group(){
    $tax=array();
    $sql = "SELECT id,name FROM ".TB_PREF."tax_groups";
    $result = db_query($sql, "could not get credit name");
    while($person = db_fetch($result)) {
        $tax[] = $person;
    }
    return $tax;
}

function sa_get_sale_group(){
    $group=array();
    $sql = "SELECT id,description FROM ".TB_PREF."groups";
    $result = db_query($sql, "could not get credit name");
    while($person = db_fetch($result)) {
        $group[] = $person;
    }
    return $group;
}

function sa_get_shippers_list(){
    $shipper=array();
    $sql = "SELECT shipper_id,shipper_name FROM ".TB_PREF."shippers";
    $result = db_query($sql, "could not get credit name");
    while($person = db_fetch($result)) {
        $shipper[] = $person;
    }
    return $shipper;
}

function db_has_leads(){
    return check_empty_result("SELECT COUNT(*) FROM ".TB_PREF."kv_crm_leads");
}
function db_has_projects(){
    return check_empty_result("SELECT COUNT(*) FROM ".TB_PREF."kv_crm_projects");
}

function db_has_tasks(){
    return check_empty_result("SELECT COUNT(*) FROM ".TB_PREF."kv_crm_tasks");
}

function db_has_opportunities(){
    return check_empty_result("SELECT COUNT(*) FROM ".TB_PREF."kv_crm_opportunities");
}

// ----------------------------------------------------------------

$crm_attachment_types_list =  array( ST_LEADS => _("Leads"), ST_OPPORTUNITIES => _("Opportunities"), ST_PROJECT => _("Projects"), ST_TASKS => _("Tasks"), ST_CUSTOMER => _("Customers"), ST_SUPPLIER => _("Suppliers"));
function  crm_attachment_types_list($name, $selected_id=null,  $all_option=false, $submit_on_change=false) {
    global $crm_attachment_types_list;
        
    $options = array(
        'spec_option' => $all_option===true ?  _("All Types") : $all_option,
        'select_submit'=> $submit_on_change
    );
    return array_selector($name, $selected_id, $crm_attachment_types_list, $options);   
}
function crm_attachment_types_list_cells($label, $name, $lead_id=null, $all_option=false, $submit_on_change=false){
    if ($label != null)
        echo "<td>$label</td>\n";
    echo "<td nowrap>";
    echo crm_attachment_types_list($name, $lead_id, $all_option, $submit_on_change);
    echo "</td>\n";
}

function crm_attachment_types_list_row($label, $name, $lead_id=null,  $all_option=false, $submit_on_change=false){
    echo "<tr><td class='label'>$label</td>";
    crm_attachment_types_list_cells(null, $name, $lead_id, $all_option, $submit_on_change);
    echo "</tr>\n";
}

// ----------------------------------------------------------------
$crm_task_types_list =  array( ST_LEADS=> _("Leads"), ST_PROJECT => _("Projects"), ST_CUSTOMER => _("Customers"), ST_SUPPLIER => _("Suppliers"));
function  crm_task_types_list($name, $selected_id=null,  $all_option=false, $submit_on_change=false, $addtask=false, $user=false, $export = false) {
    global $crm_task_types_list;
    if($addtask){
        $crm_task_types_list[5] = _("Tasks");
        $crm_task_types_list[6] = _("Opportunities");
    }
    
    if($user == 'customer') {
        if($addtask){
           unset($crm_task_types_list[6]);
           unset($crm_task_types_list[5]);
        }
       unset($crm_task_types_list[4]);
       unset($crm_task_types_list[1]);
    } 
    if($export){
        unset($crm_task_types_list[4]);
        unset($crm_task_types_list[3]);
    }

    $options = array(
        'spec_option' => $all_option===true ?  _("All Types") : $all_option,
        'select_submit'=> $submit_on_change
    );
    return array_selector($name, $selected_id, $crm_task_types_list, $options);   
}
function crm_task_types_list_cells($label, $name, $lead_id=null, $all_option=false, $submit_on_change=false, $addtask=false, $user=false, $export = false){
    if ($label != null)
        echo "<td>$label</td>\n";
    echo "<td nowrap>";
    echo crm_task_types_list($name, $lead_id, $all_option, $submit_on_change, $addtask,$user, $export);
    echo "</td>\n";
}

function crm_task_types_list_row($label, $name, $lead_id=null,  $all_option=false, $submit_on_change=false, $addtask=false, $user=false, $export = false){
    echo "<tr><td class='label'>$label</td>";
    crm_task_types_list_cells(null, $name, $lead_id, $all_option, $submit_on_change, $addtask, $user, $export );
    echo "</tr>\n";
}

// ----------------------------------------------------------------
$crm_priorities_list =  array( 1=> _("Low"), 2 => _("Medium"), 3 => _("High"), 4 => _("Ugrent"));
function  crm_priorities_list($name, $selected_id=null,  $all_option=false, $submit_on_change=false) {
    global $crm_priorities_list;
        
    $options = array(
        'spec_option' => $all_option===true ?  _("All Types") : $all_option,
        'select_submit'=> $submit_on_change
    );
    return array_selector($name, $selected_id, $crm_priorities_list, $options);   
}
function crm_priorities_list_cells($label, $name, $lead_id=null, $all_option=false, $submit_on_change=false){
    if ($label != null)
        echo "<td>$label</td>\n";
    echo "<td nowrap>";
    echo crm_priorities_list($name, $lead_id, $all_option, $submit_on_change);
    echo "</td>\n";
}

function crm_priorities_list_row($label, $name, $lead_id=null,  $all_option=false, $submit_on_change=false){
    echo "<tr><td class='label'>$label</td>";
    crm_priorities_list_cells(null, $name, $lead_id, $all_option, $submit_on_change);
    echo "</tr>\n";
}
// ----------------------------------------------------------------
function lead_list($name, $lead_id=null, $all_option=false, $submit_on_change=false, $show_inactive=false, $editkey = false, $search=true){
    global $all_items;

    $sql = "SELECT lead_id, CONCAT(`first_name`, ' ', `last_name`) AS name  FROM ".TB_PREF."kv_crm_leads WHERE 1 ";
   
    if(!$show_inactive)
        $sql .= "AND is_converted=0";
    $master_role = GetSingleValue('kv_crm_option', 'option_value', array('option_name' => 'master_role'));
    if($master_role != $_SESSION['wa_current_user']->access)
        $sql .= ' AND (added_by='.$_SESSION['wa_current_user']->user.' OR managed_by = '.$_SESSION['wa_current_user']->user.')';
    if ($editkey)
        set_editor('lead', $name, $editkey);
    $mode = 0;
    
    $ret = combo_input($name, $lead_id, $sql, 'lead_id', 'name',
    array_merge(
      array(
        'format' => '_format_crm',
            'spec_option' => $all_option===true ?  _("All Leads") : $all_option,
            'spec_id' => ALL_TEXT,
            'search_box' => $search,
            'search' => array("lead_id", "name"),
            'search_submit' => get_company_pref('no_item_list')!=0,
            'size'=>20,
            'select_submit'=> $submit_on_change,
           // 'category' => 2,
            'order' => array( 'lead_id', 'name'),
            'editlink' => $editkey ? add_edit_combo('item') : false,
            'editable' => false,
            'max' => 255
          )) );
    if ($editkey)
        $ret .= add_edit_combo('item');     
    return $ret;
      
}

function _format_crm($row){
    return (user_show_codes() ?  ($row[0] . "&nbsp;-&nbsp;") : "") . $row[1];
}

function lead_list_cells($label, $name, $lead_id=null, $all_option=false, $submit_on_change=false, $all=false, $editkey = false,  $search=false){
    if ($label != null)
        echo "<td>$label</td>\n";
    echo "<td nowrap>";
    echo lead_list($name, $lead_id, $all_option, $submit_on_change, $all, $editkey,$search);
    echo "</td>\n";
}

function lead_list_row($label, $name, $lead_id=null, $all_option=false, $submit_on_change=false, $all=false, $editkey = false,$search=false){
    echo "<tr><td class='label'>$label</td>";
    lead_list_cells(null, $name, $lead_id, $all_option, $submit_on_change, $all, $editkey, $search);
    echo "</tr>\n";
}

// ----------------------------------------------------------------
function opportunity_list($name, $opportunity_id=null, $all_option=false, $submit_on_change=false, $show_inactive=false, $editkey = false, $search=true){
    global $all_items;

    $sql = "SELECT opportunity_id, opportunity_name  FROM ".TB_PREF."kv_crm_opportunities WHERE 1 ";
   
    //if($show_all)
       // $sql .= "AND status=1";

    $master_role = GetSingleValue('kv_crm_option', 'option_value', array('option_name' => 'master_role'));
    if($master_role != $_SESSION['wa_current_user']->access)
        $sql .= ' AND (assign_to='.$_SESSION['wa_current_user']->user.')';

    if ($editkey)
        set_editor('opportunity', $name, $editkey);
    $mode = 0;
    
    $ret = combo_input($name, $opportunity_id, $sql, 'opportunity_id', 'opportunity_name',
    array_merge(
      array(
        'format' => '_format_crm',
            'spec_option' => $all_option===true ?  _("All Opportunities") : $all_option,
            'spec_id' => ALL_TEXT,
            'search_box' => $search,
            'search' => array("opportunity_id", "opportunity_name"),
            'search_submit' => get_company_pref('no_item_list')!=0,
            'size'=>20,
            'select_submit'=> $submit_on_change,
            //'category' => 2,
            'order' => array( 'opportunity_id', 'opportunity_name'),
            'editlink' => $editkey ? add_edit_combo('item') : false,
            'editable' => false,
            'max' => 255
          )) );
    if ($editkey)
        $ret .= add_edit_combo('item');     
    return $ret;      
}

function opportunity_list_cells($label, $name, $opportunity_id=null, $all_option=false, $submit_on_change=false, $all=false, $editkey = false,  $search=false){
    if ($label != null)
        echo "<td>$label</td>\n";
    echo "<td nowrap>";
    echo opportunity_list($name, $opportunity_id, $all_option, $submit_on_change, array('cells'=>true, 'show_inactive'=>$all), $editkey,$search);
    echo "</td>\n";
}

function opportunity_list_row($label, $name, $opportunity_id=null, $all_option=false, $submit_on_change=false, $all=false, $editkey = false,$search=false){
    echo "<tr><td class='label'>$label</td>";
    opportunity_list_cells(null, $name, $opportunity_id, $all_option, $submit_on_change, $all, $editkey, $search);
    echo "</tr>\n";
}

// ----------------------------------------------------------------
function projects_list($name, $project_id=null, $all_option=false, $submit_on_change=false, $show_inactive=false, $editkey = false, $search=true, $customer=false, $billing=false, $completed=false){
    global $all_items;

    $sql = "SELECT id, name  FROM ".TB_PREF."kv_crm_projects WHERE 1 ";
   
    if($customer)
        $sql .= "AND clientid=".$customer;
    if($billing)
        $where = array('billed = 0');
    else
        $where = array();
	if($completed)
		$sql .=" AND (status= 5 OR progress=100) ";
    if ($editkey)
        set_editor('kv_crm_projects', $name, $editkey);
    $mode = 0;
    
    $ret = combo_input($name, $project_id, $sql, 'id', 'name',
    array_merge(
      array(
        'format' => '_format_crm',
            'spec_option' => $all_option===true ?  _("All Projects") : $all_option,
            'spec_id' => ALL_TEXT,
            'search_box' => $search,
            'search' => array("id", "name"),
            'search_submit' => get_company_pref('no_item_list')!=0,
            'size'=>20,
            'where' => $where, 
            'select_submit'=> $submit_on_change,
            //'category' => 2,
            'order' => array( 'id', 'name'),
            'editlink' => $editkey ? add_edit_combo('item') : false,
            'editable' => false,
            'max' => 255
          )) );
    if ($editkey)
        $ret .= add_edit_combo('item');     
    return $ret;      
}

function projects_list_cells($label, $name, $project_id=null, $all_option=false, $submit_on_change=false, $all=false, $editkey = false,  $search=false, $customer=false, $billing=false, $completed=false){
    if ($label != null)
        echo "<td>$label</td>\n";
    echo "<td nowrap>";
    echo projects_list($name, $project_id, $all_option, $submit_on_change, array('cells'=>true, 'show_inactive'=>$all), $editkey,$search, $customer, $billing, $completed);
    echo "</td>\n";
}

function projects_list_row($label, $name, $project_id=null, $all_option=false, $submit_on_change=false, $all=false, $editkey = false,$search=false, $customer=false, $billing=false, $completed=false){
    echo "<tr><td class='label'>$label</td> <td nowrap>";
    echo  projects_list($name, $project_id, $all_option, $submit_on_change, $all, $editkey, $search, $customer, $billing, $completed);    
    echo "</td></tr>\n";
}
// ----------------------------------------------------------------
function completed_tasks_list($name, $task_id=null, $all_option=false, $submit_on_change=false, $show_inactive=false, $editkey = false, $search=true, $project_id=false, $selected_task_ids=false){
    global $all_items;

    $sql = "SELECT id, name  FROM ".TB_PREF."kv_crm_tasks WHERE type = 2 AND status = 5 AND billable=1 AND billed=0 ";
   
	if($selected_task_ids){
		$task_ids = implode(',', array_map('intval', $selected_task_ids));
		$sql .= "AND id NOT IN (".$task_ids.") ";
	}
    if($project_id)
        $sql .= "AND type_id=".db_escape($project_id);
    
    if ($editkey)
        set_editor('task', $name, $editkey);
    $mode = 0;
    //display_error($sql);
    $ret = combo_input($name, $task_id, $sql, 'id', 'name',
    array_merge(
      array(
        'format' => '_format_crm',
            'spec_option' => $all_option===true ? _("All Tasks") : $all_option,
            'spec_id' => ALL_TEXT,
            'search_box' => $search,
            'search' => array("id", "name"),
            'search_submit' => get_company_pref('no_item_list')!=0,
            'size'=>20,
            'select_submit'=> $submit_on_change,
            //'category' => 2,
            'order' => array( 'id', 'name'),
            'editlink' => $editkey ? add_edit_combo('item') : false,
            'editable' => false,
            'max' => 255
          )) );
    if ($editkey)
        $ret .= add_edit_combo('item');     
    return $ret;      
}

function completed_tasks_list_cells($label, $name, $task_id=null, $all_option=false, $submit_on_change=false, $all=false, $editkey = false,$search=false, $project_id =false,$selected_task_ids = false){
    echo "<td class='label'>$label</td><td>";
    echo completed_tasks_list( $name, $task_id, $all_option, $submit_on_change, $all, $editkey, $search, $project_id, $selected_task_ids);
    if(get_post($name)== 0 || get_post($name) == '')
        echo '<br>'.text_input('stock_id_text', null, 45, 150);
    echo "</td>\n";
}


// ----------------------------------------------------------------
function tasks_list($name, $task_id=null, $all_option=false, $submit_on_change=false, $show_inactive=false, $editkey = false, $search=true){
    global $all_items;

    $sql = "SELECT id, name  FROM ".TB_PREF."kv_crm_tasks WHERE 1 ";
   
    //if($show_all)
       // $sql .= "AND status=1";

    if ($editkey)
        set_editor('task', $name, $editkey);
    $mode = 0;
    
    $ret = combo_input($name, $task_id, $sql, 'id', 'name',
    array_merge(
      array(
        'format' => '_format_crm',
            'spec_option' => $all_option===true ?  _("All Tasks") : $all_option,
            'spec_id' => ALL_TEXT,
            'search_box' => $search,
            'search' => array("id", "name"),
            'search_submit' => get_company_pref('no_item_list')!=0,
            'size'=>20,
            'select_submit'=> $submit_on_change,
            //'category' => 2,
            'order' => array( 'id', 'name'),
            'editlink' => $editkey ? add_edit_combo('item') : false,
            'editable' => false,
            'max' => 255
          )) );
    if ($editkey)
        $ret .= add_edit_combo('item');     
    return $ret;      
}

function tasks_list_cells($label, $name, $task_id=null, $all_option=false, $submit_on_change=false, $all=false, $editkey = false,  $search=false){
    if ($label != null)
        echo "<td>$label</td>\n";
    echo "<td nowrap>";
    echo tasks_list($name, $task_id, $all_option, $submit_on_change, array('cells'=>true, 'show_inactive'=>$all), $editkey,$search);
    echo "</td>\n";
}

function tasks_list_row($label, $name, $task_id=null, $all_option=false, $submit_on_change=false, $all=false, $editkey = false,$search=false){
    echo "<tr><td class='label'>$label</td>";
    tasks_list_cells(null, $name, $task_id, $all_option, $submit_on_change, $all, $editkey, $search);
    echo "</tr>\n";
}

//-------------------------------------------------------------------------------
function crm_country_list($name, $selected_id=null){

    $sql = "SELECT id, local_name FROM ".TB_PREF."kv_crm_country";
    return combo_input($name, $selected_id, $sql, 'id', 'local_name', array());
}

function crm_country_list_cells($label, $name, $selected_id=null){
    if ($label != null)
        echo "<td>$label</td>\n";
    echo "<td>";
    echo crm_country_list($name, $selected_id);
    echo "</td>\n";
}

function crm_country_list_row($label, $name, $selected_id=null){
    echo "<tr><td class='label'>$label</td>";
    crm_country_list_cells(null, $name, $selected_id);
    echo "</tr>\n";
}

function kv_user_list($name, $selected_id=null, $spec_option=false, $submit_on_change=false, $show_inactive=false, $editkey = false, $disabled=false,$class='', $multiple=false){
    global $all_items;

    $sql = "SELECT id, real_name,  inactive FROM ".TB_PREF."users ";

    $mode = get_company_pref('no_user_list');

   $user = $_SESSION['wa_current_user']->user;

    if ($editkey)
        set_editor('user', $name, $editkey);

    $ret = combo_input($name,  $user, $sql, 'id', 'real_name',
    array(
        'order' => array('real_name'),
        'search_box' => $mode!=0,
        'type' => 1,
        'size' => 20,
        'spec_option' => $spec_option === true ? _("All Users") : $spec_option,
        'spec_id' => $all_items,
        'select_submit'=> $submit_on_change,
        'async' => false,
        'sel_hint' => $mode ? _('Press Space tab to filter by name fragment; F2 - entry new user') :
        _('Select User'),
        'show_inactive' => $show_inactive,
        'disabled' => $disabled,
        'multi' => $multiple
    ),$class );
    if ($editkey)
        $ret .= add_edit_combo('user');
    return $ret;
} 

function kv_user_list_cells($label, $name, $selected_id=null, $all_option=false, $submit_on_change=false, $show_inactive=false, $editkey = false,$disabled=false, $multiple =false){
    if ($label != null)
        echo "<td>$label</td>\n";
    echo "<td nowrap>";
    echo kv_user_list($name, $selected_id, $all_option, $submit_on_change, $show_inactive, $editkey,$disabled, '', $multiple);
    echo "</td>\n";
}

function kv_user_list_row($label, $name, $selected_id=null, $all_option = false, $submit_on_change=false, $show_inactive=false, $editkey = false,$disabled=false, $hide_selectbox_ui='', $multiple=false){
    global $path_to_root;

    echo "<tr><td>$label</td><td nowrap>";
    echo kv_user_list($name, $selected_id, $all_option, $submit_on_change,  $show_inactive, $editkey,$disabled,$hide_selectbox_ui, $multiple);
    echo "</td>\n</tr>\n";
} 

function kv_unassigned_staffs($name, $selected_id=null, $spec_option=false, $submit_on_change=false, $project_id = 0, $multiple=false){
    global $all_items;

    $sql = "SELECT usr.id, real_name, usr.inactive FROM ".TB_PREF."users AS usr WHERE usr.id NOT IN (SELECT user_id FROM ".TB_PREF."debtors_master WHERE user_id > 0 ) AND usr.id NOT IN (SELECT user_id FROM ".TB_PREF."suppliers WHERE user_id > 0 ) ";
    if($project_id > 0)
        $sql .=" AND project_id=".$project_id;

    $sql .=" GROUP BY usr.id ";
    $mode = get_company_pref('no_user_list');

    $ret = combo_input($name, $selected_id, $sql, 'id', 'real_name',
    array(
        'order' => array('real_name'),
        'search_box' => $mode!=0,
        'type' => 1,
        'size' => 20,
        'spec_option' => $spec_option === true ? _("All Users") : $spec_option,
        'spec_id' => $all_items,
        'select_submit'=> $submit_on_change,
        'async' => false,
        'sel_hint' => $mode ? _('Press Space tab to filter by name fragment; F2 - entry new user') :
        _('Select User'),         
        'multi' => $multiple
    ),'' );
    
    return $ret;
} 

function kv_unassigned_staffs_cells($label, $name, $selected_id=null, $all_option=false, $submit_on_change=false, $project_id=0, $multiple =false){
    if ($label != null)
        echo "<td>$label</td>\n";
    echo "<td nowrap>";
    echo kv_unassigned_staffs($name, $selected_id, $all_option, $submit_on_change, $project_id, $multiple);
    echo "</td>\n";
}

function kv_unassigned_staffs_row($label, $name, $selected_id=null, $all_option = false, $submit_on_change=false, $project_id=0, $multiple=false){
    global $path_to_root;

    echo "<tr><td>$label</td><td nowrap>";
    echo kv_unassigned_staffs($name, $selected_id, $all_option, $submit_on_change,  $project_id, $multiple);
    echo "</td>\n</tr>\n";
}


//------------------------------------------------------------------------------------------------------------
function kv_project_members($name, $selected_id=null, $spec_option=false, $submit_on_change=false, $project_id = 0, $multiple=false){
    global $all_items;

    $sql = "SELECT usr.id, real_name, inactive FROM ".TB_PREF."users AS usr INNER JOIN ".TB_PREF."kv_crm_projectmembers AS member ON  member.staff_id=usr.id ";
    if($project_id > 0)
        $sql .=" WHERE project_id=".$project_id;
    $mode = get_company_pref('no_user_list');

    $ret = combo_input($name, $selected_id, $sql, 'id', 'real_name',
    array(
        'order' => array('real_name'),
        'search_box' => $mode!=0,
        'type' => 1,
        'size' => 20,
        'spec_option' => $spec_option === true ? _("All Users") : $spec_option,
        'spec_id' => $all_items,
        'select_submit'=> $submit_on_change,
        'async' => false,
        'sel_hint' => $mode ? _('Press Space tab to filter by name fragment; F2 - entry new user') :
        _('Select User'),
       
        
        'multi' => $multiple
    ),'' );
    
    return $ret;
} 

function kv_project_members_cells($label, $name, $selected_id=null, $all_option=false, $submit_on_change=false, $project_id=0, $multiple =false){
    if ($label != null)
        echo "<td>$label</td>\n";
    echo "<td nowrap>";
    echo kv_project_members($name, $selected_id, $all_option, $submit_on_change, $project_id, $multiple);
    echo "</td>\n";
}

function kv_project_members_row($label, $name, $selected_id=null, $all_option = false, $submit_on_change=false, $project_id=0, $multiple=false){
    global $path_to_root;

    echo "<tr><td>$label</td><td nowrap>";
    echo kv_project_members($name, $selected_id, $all_option, $submit_on_change,  $project_id, $multiple);
    echo "</td>\n</tr>\n";
}

function kv_contact_list($name, $selected_id=null, $spec_option=false, $submit_on_change=false, $show_inactive=false, $editkey = false, $disabled=false,$class='',$contact_type='customer'){
    global $all_items;   

    if(get_post('cust_id')){
        $sql = "SELECT p.id, p.name, r.id as contact_id FROM ".TB_PREF."crm_persons p, ".TB_PREF."crm_categories t, ".TB_PREF."crm_contacts r WHERE r.type=t.type AND r.action=t.action AND r.person_id=p.id AND t.type='".$contact_type."' AND r.entity_id='".get_post('cust_id')."'";
    } else
         $sql = "SELECT id, name,  inactive FROM ".TB_PREF."crm_persons ";

    $mode = get_company_pref('no_user_list');

    if ($editkey)
        set_editor('user', $name, $editkey);
    $ret = combo_input($name, $selected_id, $sql, 'id', 'name',
    array(
        'order' => array('contact_id'),
        'search_box' => $mode!=0,
        'type' => 1,
        'size' => 20,
        'spec_option' => $spec_option === true ? _("All Users") : $spec_option,
        'spec_id' => $all_items,
        'select_submit'=> $submit_on_change,
        'async' => false,
        'sel_hint' => $mode ? _('Press Space tab to filter by name fragment; F2 - entry new user') :
        _('Select User'),
        'show_inactive' => $show_inactive,
        'disabled' => $disabled
    ),$class );
    if ($editkey)
        $ret .= add_edit_combo('user');
    return $ret; 
}

function kv_contact_list_cells($label, $name, $selected_id=null, $all_option=false, $submit_on_change=false, $show_inactive=false, $editkey = false,$disabled=false){
    if ($label != null)
        echo "<td>$label</td>\n";
    echo "<td nowrap>";
    echo kv_contact_list($name, $selected_id, $all_option, $submit_on_change, $show_inactive, $editkey,$disabled);
    echo "</td>\n";
}

function kv_contact_list_row($label, $name, $selected_id=null, $all_option = false, $submit_on_change=false, $show_inactive=false, $editkey = false,$disabled=false, $hide_selectbox_ui=''){
    global $path_to_root;

    echo "<tr><td>$label</td><td nowrap>";
    echo kv_contact_list($name, $selected_id, $all_option, $submit_on_change,   $show_inactive, $editkey,$disabled,$hide_selectbox_ui);
    echo "</td>\n</tr>\n";
} 
//crm filter
function kv_common_status($name, $selected_id=null, $spec_option=true, $submit_on_change=false, $status_type=0, $class=''){

    $sql = "SELECT status_id, status FROM ".TB_PREF."kv_crm_status"; //  WHERE status_type=".$status_type;  

    $ret = combo_input($name, $selected_id, $sql, 'status_id', 'status',
    array(
        'order' => array('status'),
        'search_box' => false,
        'type' => 1,
        'size' => 20,
        'where'=> array('status_type='.$status_type),
        'spec_option' => $spec_option === true ? _(" Select ") : $spec_option,
        'spec_id' => 0,
        'select_submit'=> $submit_on_change,
        'async' => false    
    ),$class );
    
    return $ret; 
}

function kv_common_status_cells($label, $name, $selected_id=null, $all_option=false, $submit_on_change=false, $status_type=0,   $class=''){
    if ($label != null)
        echo "<td>$label</td>\n";
    echo "<td nowrap>";
    echo kv_common_status($name, $selected_id, $all_option, $submit_on_change, $status_type, $class);
    echo "</td>\n";
}

function kv_common_status_row($label, $name, $selected_id=null, $all_option = false, $submit_on_change=false, $status_type=0,   $class=''){
    global $path_to_root;

    echo "<tr><td>$label</td><td nowrap>";
    echo kv_common_status($name, $selected_id, $all_option, $submit_on_change, $status_type, $class);
    echo "</td>\n</tr>\n";
} 


// For  types common function..
function kv_common_type( $name, $selected_id=null, $spec_option = false, $submit_on_change=false, $type_id=0,   $class=''){
    global $path_to_root;
    $sql = "SELECT id2, description FROM ".TB_PREF."kv_crm_picklist_values"; 

    $ret = combo_input($name, $selected_id, $sql, 'id2', 'description',
    array(
        'order' => array('description'),
        'search_box' => false,
        'type' => 1,
        'where'=> array('picklist_id='.$type_id),
        'size' => 20,
        'spec_option' => $spec_option === true ? _(" Select ") : $spec_option,
        'spec_id' => 0,
        'select_submit'=> $submit_on_change,
        'async' => false    
    ),$class ); 

    return $ret;
}

function kv_common_type_cells($label, $name, $selected_id=null, $all_option=false, $submit_on_change=false, $type_id=0, $class=''){
    if ($label != null)
        echo "<td>$label</td>\n";
    echo "<td nowrap>";
    echo kv_common_type( $name, $selected_id, $all_option, $submit_on_change, $type_id, $class);
    echo "</td>\n";
}


function kv_common_type_row($label, $name, $selected_id=null, $all_option = false, $submit_on_change=false, $type_id=0, $class=''){
    global $path_to_root;

    echo "<tr><td>$label</td><td nowrap>";
    echo kv_common_type($name, $selected_id, $all_option, $submit_on_change, $type_id, $class);
    echo "</td>\n</tr>\n";
} 


//------------------------------------------------------------------------------------
function crm_picklist_type($name, $selected_id=null, $all_option=false,$submit_on_change=false, $text_inpt=false){
    global $path_to_root;
    $sql = "SELECT id, description FROM ".TB_PREF."kv_crm_picklist ";

    return combo_input($name, $selected_id, $sql, 'id', 'description',
    array(
        'order' => 'description',
        'format' => null,
        'spec_option' => _("Select Type"),
        'select_submit'=> $submit_on_change,
        'async' => false
    ) ).($text_inpt? ' or '.text_input($name.'_', null ).'  <button type="submit" class="ajaxsubmit" name="RemovePick" value="'.$_POST[$name].'" title="Delete"><img src="'.$path_to_root.'/themes/default/images/delete.gif" style="vertical-align:middle;width:12px;height:12px;border:0;"></button>' : '');
}
function crm_picklist_type_cells($label, $name, $selected_id=null, $all_option=false, $submit_on_change=false, $text_inpt=false){
    if ($label != null)
        echo "<td>$label</td>\n";
    echo "<td nowrap>";
    echo crm_picklist_type( $name, $selected_id, $all_option, $submit_on_change, $text_inpt);
    echo "</td>\n";
}

function crm_picklist_type_row($label, $name, $selected_id=null, $all_option = false, $submit_on_change=false, $text_inpt=false){
    global $path_to_root;

    echo "<tr><td>$label</td><td nowrap>";
    echo crm_picklist_type_cells(null, $name, $selected_id, $all_option, $submit_on_change, $text_inpt);
    echo "</td>\n</tr>\n";
} 

//--------------------------------------------------------------------------------
/*  Billing Type Drop-down   */ 

function billing_type_list($name, $selected_id=null,  $submit_on_change=false, $disabled=false){
    global $billing_types; 

    return array_selector($name, $selected_id, $billing_types, 
        array( 
            'select_submit'=> $submit_on_change,
            'async' => false,
            'disabled' => $disabled ) ); // FIX?
}

function billing_type_list_cells($label, $name, $selected_id=null, $all_option=false, $submit_on_change=false, $text_inpt=false){
    if ($label != null)
        echo "<td>$label</td>\n";
    //echo "<td nowrap>";
    echo billing_type_list( $name, $selected_id, $submit_on_change, $all_option);
    //echo "</td>\n";
}

function billing_type_list_row($label, $name, $selected_id=null, $all_option = false, $submit_on_change=false, $text_inpt=false){
    global $path_to_root;

    echo "<tr><td>$label</td><td nowrap>";
    echo billing_type_list_cells(null, $name, $selected_id, $all_option, $submit_on_change, $text_inpt);
    echo "</td>\n</tr>\n";
} 

//--------------------------------------------------------------------------------
/*  Numbers Drop-down   */ 
function kv_crm_number_list_row($label, $name, $selected_id=null, $from = 0, $to = 10, $submit_on_change=false) {
    $kv_crm_number = array();
    for($from; $from <= $to; $from++){
        $kv_crm_number[$from] = $from;
    }
    echo "<tr><td class='label'>$label</td><td>";
    $options = array(
        'select_submit'=> $submit_on_change
    );
    echo array_selector($name, $selected_id, $kv_crm_number, $options);
    echo "</td></tr>\n";
}

//--------------------------------------------------------------------------------
/*  Customer Leads Type Drop down   */ 
function kv_crm_customer_lead_list_row($label, $name, $selected_id=null, $submit_on_change=false) {
    $kv_crm_cust_lead = array( 1 => _("Leads"), 2 => _("Customers"));
   
    echo "<tr><td class='label'>$label</td><td>";
    $options = array(
        'select_submit'=> $submit_on_change
    );
    echo array_selector($name, $selected_id, $kv_crm_cust_lead, $options);
    echo "</td></tr>\n";
}

//--------------------------------------------------------------------------------
/*  Estimates/Proposals Type Drop down   */ 
function types_list_cells($label, $name, $selected_id=null, $submit_on_change=false, $discard2 =false) {
    $kv_crm_types = array( 30 => _("Orders"), 32 => _("Quotation"), 33 => _("Estimate"), 34 => _("Proposal"));
    if($discard2){
        array_remove($kv_crm_types, 1);
        array_remove($kv_crm_types, 0);
    }
    echo "<td class='label'>$label</td><td>";
    $options = array(
        'select_submit'=> $submit_on_change
    );
    echo array_selector($name, $selected_id, $kv_crm_types, $options);


    echo "</td>\n";
}

//--------------------------------------------------------------------------------
function crm_submenu_view($title, $type, $number, $id=null)
{
    display_note(crm_get_trans_view_str($type, $number, $title, false, 'viewlink', $id), 0, 1);
}

function crm_submenu_print($title, $type, $number, $id=null, $email=0, $extra=0)
{
    display_note(crm_print_document_link($number, $title, true, $type, false, 'printlink', $id, $email, $extra), 0, 1);
}

//--------------------------------------------------------------------------------------

function crm_get_trans_view_str($type, $trans_no, $label="", $icon=false, 
    $class='', $id='')
{
    $view_str = crm_get_customer_trans_view_str($type, $trans_no, $label, $icon, $class, $id);
    if ($view_str != null)
        return $view_str;

    $view_str = crm_get_supplier_trans_view_str($type, $trans_no, $label, $icon, $class, $id);
    if ($view_str != null)
        return $view_str;

    $view_str = get_banking_trans_view_str($type, $trans_no, $label, $icon, $class, $id);
    if ($view_str != null)
        return $view_str;

    $view_str = get_inventory_trans_view_str($type, $trans_no, $label, $icon, $class, $id);
    if ($view_str != null)
        return $view_str;

    $view_str = get_manufacturing_trans_view_str($type, $trans_no, $label, $icon, $class, $id);
    if ($view_str != null)
        return $view_str;

    $view_str = get_dimensions_trans_view_str($type, $trans_no, $label, $icon, $class, $id);
    if ($view_str != null)
        return $view_str;

    $view_str = get_journal_trans_view_str($type, $trans_no, $label, $icon, $class, $id);
    if ($view_str != null)
        return $view_str;

    return null;
}

function crm_get_customer_trans_view_str($type, $trans_no, $label="", $icon=false,  $class='', $id=''){

    if($type == ST_SALESPROPOSAL || $type == ST_SALESESTIMATE || $type == ST_PROJECTINVOICE ){
        $viewer = "modules/KvcodesCRM/inquires/sales_view.php";    
    } else {
        $viewer = "modules/KvcodesCRM/view/";

        if ($type == ST_SALESINVOICE)
            $viewer .= "view_invoice.php";
        elseif ($type == ST_CUSTCREDIT)
            $viewer .= "view_credit.php";
        elseif ($type == ST_CUSTPAYMENT)
            $viewer .= "view_receipt.php";
        elseif ($type == ST_CUSTDELIVERY)
            $viewer .= "view_dispatch.php";
        elseif ($type == ST_SALESORDER || $type == ST_SALESQUOTE)
            $viewer .= "view_sales_order.php";
        else
            return null;
    }
  if(!is_array($trans_no)) $trans_no = array($trans_no);

  $lbl = $label;
  $preview_str = '';

  foreach($trans_no as $trans) {
    if ($label == "")
        $lbl = $trans;
    if($preview_str!='') $preview_str .= ',';

    $preview_str .= viewer_link($lbl, $viewer."?trans_no=$trans&trans_type=$type", 
        $class, $id, $icon);

  }
  return $preview_str;
}


function crm_get_supplier_trans_view_str($type, $trans_no, $label="", $icon=false,  $class='', $id=''){
    $viewer = "modules/KvcodesCRM/view/";
    if ($type == ST_PURCHORDER)
        $viewer .= "view_po.php";
    elseif ($type == ST_SUPPINVOICE)
        $viewer .= "view_supp_invoice.php";
    elseif ($type == ST_SUPPCREDIT)
        $viewer .= "view_supp_credit.php";
    elseif ($type == ST_SUPPAYMENT)
        $viewer .= "view_supp_payment.php";
    elseif ($type == ST_SUPPRECEIVE)
        $viewer .= "view_grn.php";
    else
        return null;
    $viewer .= "?trans_no=$trans_no";

    if ($label == "")
        $label = $trans_no;

    return viewer_link($label, $viewer, $class, $id,  $icon);
}

function crm_print_document_link($doc_no, $link_text, $link=true, $type_no=33, $icon=false, $class='printlink', $id='', $email=0, $extra=0){
    global $path_to_root;
    include_once($path_to_root . "/includes/types.inc");

    $url = $path_to_root.'/reporting/prn_redirect.php?';
    $def_orientation = (user_def_print_orientation() == 1 ? 1 : 0);

    switch ($type_no)   {
        case ST_SALESPROPOSAL :
            $rep = 121;
            // from, to, currency, email, comments, orientation
            $ar = array(
                'PARAM_0' => $doc_no, 
                'PARAM_1' => $doc_no, 
                'PARAM_2' => '', 
                'PARAM_3' => $email, 
                'PARAM_4' => '',
                'PARAM_5' => $def_orientation);
            break;       
        case ST_SALESESTIMATE :
            $rep = 120;
            // from, to, email, packing slip, comments, orientation
             $ar = array(
                'PARAM_0' => $doc_no, 
                'PARAM_1' => $doc_no, 
                'PARAM_2' => '', 
                'PARAM_3' => $email, 
                'PARAM_4' => '',
                'PARAM_5' => $def_orientation);
            break;  
		case ST_PROJECTINVOICE :
            $rep = 122;
            // from, to, email, packing slip, comments, orientation
             $ar = array(
                'PARAM_0' => $doc_no, 
                'PARAM_1' => $doc_no, 
                'PARAM_2' => '', 
                'PARAM_3' => $email, 
                'PARAM_4' => '',
                'PARAM_5' => $def_orientation);
            break;  
			
        default:
            return null;
    }

    return print_link($link_text, $rep, $ar, $url, $icon, $class, $id);
}

function crm_trans_editor_link($type, $trans_no)
{
    global $path_to_root;

    $editor_url = array(       
        ST_SALESORDER => "/sales/sales_order_entry.php?ModifyOrderNumber=%d",
        ST_SALESQUOTE => "/sales/sales_order_entry.php?ModifyQuotationNumber=%d",
        ST_SALESESTIMATE => "/modules/KvcodesCRM/estimates.php?ModifyEstimate=%d",
        ST_SALESPROPOSAL => "/modules/KvcodesCRM/estimates.php?ModifyProposal=%d",
    
    );

    return !isset($editor_url[$type]) ? '' :
        (is_closed_trans($type, $trans_no) ? set_icon(ICON_CLOSED, _('Closed')) :
            pager_link(_("Edit"), sprintf($editor_url[$type], $trans_no, $type), ICON_EDIT));
}

//--------------------------------------------------------------------------------
/*  Estimates/Proposals Type Drop down   */ 
function crm_smtp_type_list_cells($label, $name, $selected_id=null, $submit_on_change=false) {
    $kv_crm_types = array( 'none' => 'None', 'tls'=> 'TLS', 'ssl' => 'SSL');
   
    echo "<td class='label'>$label</td><td>";
    $options = array(
        'select_submit'=> $submit_on_change
    );
    echo array_selector($name, $selected_id, $kv_crm_types, $options);


    echo "</td>\n";
}

//--------------------------------------------------------------------------------
/*  CRM Total Types Drop down   */ 
function crm_total_types_list_cells($label, $name, $selected_id=null, $submit_on_change=false) {
    $crm_total_types = array( 1 => _("Leads"),
        2 => _("Projects"),
        3 => _("Customers"),
        4 => _("Suppliers"),
        5 => _("Tasks"),
        6 => _("Opportunities"),
        7 => _("Invoice"),
        33 => _("Estimates"), 
        34 => _("Proposals"),  
        //10 => _("Credit Note"),
        11 => _("Staff"),
        12 => _("Comments"),
        // 13 => _("Attachments"),
         15 => _("Tickets")  
        );
   
    echo "<td class='label'>$label</td><td>";
    $options = array(
        'select_submit'=> $submit_on_change
    );
    echo array_selector($name, $selected_id, $crm_total_types, $options);

    echo "</td>\n";
}

function kv_crm_textarea_cells($label, $name, $value, $cols, $rows, $title = null, $params="")
{
    global $Ajax;

    default_focus($name);
    if ($label != null)
        echo "<td $params>$label</td>\n";
    if ($value == null)
        $value = (!isset($_POST[$name]) ? "" : $_POST[$name]);
    echo "<td><textarea name='$name' id='$name' style='width: 500px; height: 100px;' cols='$cols' rows='$rows'"
    .($title ? " title='$title'" : '')
    .">$value</textarea></td>\n";
    $Ajax->addUpdate($name, $name, $value);
}

function kv_crm_textarea_row($label, $name, $value, $cols, $rows, $title=null, $params="")
{
    echo "<tr><td class='label'>$label</td>";
    kv_crm_textarea_cells(null, $name, $value, $cols, $rows, $title, $params);
    echo "</tr>\n";
}

function GetNextPickNumber($pick_id){
    return GetSingleValue('kv_crm_picklist_values', 'id2', array('picklist_id' => $pick_id), array('id' => 'desc'))+1;
}
function get_project_gl_code($project_id) {
    /*Gets the GL Codes relevant to the item account  */
    $sql = "SELECT mb_flag, inventory_account, cogs_account, adjustment_account, sales_account, wip_account, dimension_id, dimension2_id FROM
        ".TB_PREF."kv_crm_projects WHERE id = ".db_escape($project_id);

    $get = db_query($sql,"retreive stock gl code");
    return db_fetch($get);
}

//----------------------------------------------------------------------------------------

function get_crm_customer_trans($trans_id, $trans_type, $customer_id=null)
{
    global $SysPrefs;

    $sql = "SELECT trans.*,"
        ."ov_amount+ov_gst+ov_freight+ov_freight_tax+ov_discount AS Total,"
        ."cust.name AS DebtorName, cust.address, "
        ."cust.curr_code, "
        ."cust.tax_id,
        trans.prep_amount>0 as prepaid,"
        ."com.memo_";

    if ($trans_type == ST_CUSTPAYMENT || $trans_type == ST_BANKDEPOSIT) {
        // it's a payment so also get the bank account
        $sql .= ",bank_act,".TB_PREF."bank_accounts.bank_name, ".TB_PREF."bank_accounts.bank_account_name,
            ".TB_PREF."bank_accounts.account_type AS BankTransType,
            ".TB_PREF."bank_accounts.bank_curr_code,
            ".TB_PREF."bank_trans.amount as bank_amount";
    }

    if ($trans_type == ST_SALESINVOICE || $trans_type == ST_CUSTCREDIT || $trans_type == ST_CUSTDELIVERY) {
        // it's an invoice so also get the shipper and salestype
        $sql .= ", ".TB_PREF."shippers.shipper_name, "
        .TB_PREF."sales_types.sales_type, "
        .TB_PREF."sales_types.tax_included, "
        ."branch.*, "
        ."cust.discount, "
        .TB_PREF."tax_groups.name AS tax_group_name, "
        .TB_PREF."tax_groups.id AS tax_group_id ";
    }

    if ($trans_type == ST_JOURNAL) {
        $sql .= ", branch.*";
    }

    $sql .= " FROM ".TB_PREF."kv_crm_sales_orders trans
                    LEFT JOIN ".TB_PREF."comments com ON trans.type=com.type AND trans.trans_no=com.id
                    LEFT JOIN ".TB_PREF."shippers ON ".TB_PREF."shippers.shipper_id=trans.ship_via, 
                    ".TB_PREF."debtors_master cust";

    if ($trans_type == ST_CUSTPAYMENT || $trans_type == ST_BANKDEPOSIT) {
        // it's a payment so also get the bank account
        $sql .= ", ".TB_PREF."bank_trans, ".TB_PREF."bank_accounts";
    }

    if ($trans_type == ST_PROJECTINVOICE) {
        // it's an invoice so also get the shipper, salestypes
        $sql .= ", ".TB_PREF."sales_types, "
        .TB_PREF."cust_branch branch, "
        .TB_PREF."tax_groups ";
    }

    if ($trans_type == ST_JOURNAL) {
        $sql .= ", ".TB_PREF."cust_branch branch ";
    }

    $sql .= " WHERE trans.trans_no=".db_escape($trans_id)."
        AND trans.type=".db_escape($trans_type)."
        AND trans.debtor_no=cust.debtor_no";

    if (isset($customer_id))
        $sql .= " AND trans.debtor_no=".db_escape($customer_id);

    if ($trans_type == ST_CUSTPAYMENT || $trans_type == ST_BANKDEPOSIT) {
        // it's a payment so also get the bank account
        $sql .= " AND ".TB_PREF."bank_trans.trans_no =".db_escape($trans_id)."
            AND ".TB_PREF."bank_trans.type=$trans_type
            AND ".TB_PREF."bank_trans.amount != 0
            AND ".TB_PREF."bank_accounts.id=".TB_PREF."bank_trans.bank_act ";
    }
    if ($trans_type == ST_PROJECTINVOICE ) {
        // it's an invoice so also get the shipper
        $sql .= " AND ".TB_PREF."sales_types.id = trans.tpe
            AND branch.branch_code = trans.branch_code
            AND branch.tax_group_id = ".TB_PREF."tax_groups.id ";
    }
    if ($trans_type == ST_JOURNAL) {
        $sql .= " AND branch.branch_code = trans.branch_code ";
    }

    $result = db_query($sql, "Cannot retreive a debtor transaction");

    if (db_num_rows($result) == 0) {
        // can't return nothing
        if ($SysPrefs->go_debug)
            display_backtrace();
        display_db_error("no debtor trans found for given params", $sql, true);
        exit;
    }
    if (db_num_rows($result) > 1) {
        // can't return multiple
        if($SysPrefs->go_debug)
            display_backtrace();
        display_db_error("duplicate debtor transactions found for given params", $sql, true);
        exit;
    }

    $row = db_fetch($result);
    return $row;
}


function get_project($project_id)
{
    $sql = "SELECT project.*, taxtype.name AS tax_type_name
        FROM ".TB_PREF."kv_crm_projects project,"
            .TB_PREF."item_tax_types taxtype
        WHERE taxtype.id=project.tax_type_id
        AND project.id=".db_escape($project_id);
    $result = db_query($sql,"an project could not be retreived");

    return db_fetch_assoc($result);
}


function crm_key_in_foreign_table($id, $tables, $stdkey, $conditions=[]){

    if (!is_array($tables))
        $tables = array($tables);

    $sqls = array();
    foreach ($tables as $tbl => $key) {
        if (is_numeric($tbl)) {
            $tbl = $key;
            $key = $stdkey;
            
         } //elseif(is_array($tbl)){
        //     $kv = 0 ;
        //     $key ='';
        //     foreach($tbl as $tble_name => $keys){
        //         if($kv > 0 )
        //             $key .= ' AND ';
        //         $tbl = $tble_name;
        //         $key .= $keys[0].' = '.$keys[1];    
        //         $kv++;       
        //     }
        //      $sqls[] = "(SELECT COUNT(*) as cnt FROM `".TB_PREF."$tbl` WHERE ".$key.")\n";
        // }
        if(is_array($conditions) && !empty($conditions))
            $condition = 'AND '.$conditions[0].' = '.$conditions[1];
        else
            $condition = '';

        $sqls[] = "(SELECT COUNT(*) as cnt FROM `".TB_PREF."$tbl` WHERE `$key`=".db_escape($id)." ".$condition.")\n";
    }

    $sql = "SELECT sum(cnt) FROM (". implode(' UNION ', $sqls).") as counts";

    $result = db_query($sql, "check relations for tables failed");
    $count =  db_fetch($result);

    return $count[0];
}


function kv_unassigned_users_for_contact($name, $selected_id=null, $spec_option=false, $submit_on_change=false, $project_id = 0, $multiple=false){
    global $all_items;

    $sql = "SELECT usr.id, real_name, usr.inactive FROM ".TB_PREF."users AS usr WHERE usr.id NOT IN (SELECT user_id FROM ".TB_PREF."crm_persons WHERE user_id > 0 )";
    if($project_id > 0)
        $sql .=" AND project_id=".$project_id;

    $sql .=" GROUP BY usr.id ";
    $mode = get_company_pref('no_user_list');

    $ret = combo_input($name, $selected_id, $sql, 'id', 'real_name',
    array(
        'order' => array('real_name'),
        'search_box' => $mode!=0,
        'type' => 1,
        'size' => 20,
        'spec_option' => $spec_option === true ? _("All Users") : $spec_option,
        'spec_id' => $all_items,
        'select_submit'=> $submit_on_change,
        'async' => false,
        'sel_hint' => $mode ? _('Press Space tab to filter by name fragment; F2 - entry new user') :
        _('Select User'),         
        'multi' => $multiple
    ),'' );
    
    return $ret;
} 

function kv_unassigned_users_for_contact_cells($label, $name, $selected_id=null, $all_option=false, $submit_on_change=false, $project_id=0, $multiple =false){
    if ($label != null)
        echo "<td>$label</td>\n";
    echo "<td nowrap>";
    echo kv_unassigned_users_for_contact($name, $selected_id, $all_option, $submit_on_change, $project_id, $multiple);
    echo "</td>\n";
}

function kv_unassigned_users_for_contact_row($label, $name, $selected_id=null, $all_option = false, $submit_on_change=false, $project_id=0, $multiple=false){
    global $path_to_root;

    echo "<tr><td>$label</td><td nowrap>";
    echo kv_unassigned_users_for_contact($name, $selected_id, $all_option, $submit_on_change,  $project_id, $multiple);
    echo "</td>\n</tr>\n";
}

?>