<?php
/****************************************
/*  Author 	: Kvvaradha
/*  Module 	: Extended HRM
/*  E-mail 	: admin@kvcodes.com
/*  Version : 1.0
/*  Http 	: www.kvcodes.com
*****************************************/
//--------------------------------------------------------------------------------------------
function kv_get_employees_based_on_dept($dept_id, $date=null) {
	if($date== null){
		$date = date('Y-m-d');
	}else
		$date = date2sql($date);
	if($dept_id == 0){		
		$sql = "SELECT DISTINCT empl_id, CONCAT(empl_firstname, ' ' , empl_lastname) AS empl_name FROM ".TB_PREF."kv_empl_info WHERE status=1 ORDER BY empl_id";  		
	} else{
		$sql="SELECT DISTINCT info.empl_id, job.joining, info.status, info.date_of_status_change FROM ".TB_PREF."kv_empl_job job, ".TB_PREF."kv_empl_info info WHERE info.empl_id= job.empl_id AND job.department=".db_escape($dept_id)." ORDER BY info.empl_id";		
	}
	return db_query($sql, _("The employee table is inaccessible"));
}

function kv_get_employees_count_based_on_dept($dept_id) {

	if($dept_id == 0){
		$sql = "SELECT COUNT(empl_id) FROM ".TB_PREF."kv_empl_info";
	} else{
		$sql="SELECT COUNT(empl_id) FROM ".TB_PREF."kv_empl_job WHERE department=".db_escape($dept_id);
	}
	return check_empty_result($sql);
}
function kv_get_employees_based_on_dept_for_attandance($dept_id, $month, $year){
	/*if($date== null){
		$date = date('Y-m-d');
	}else
		$date = date2sql($date);*/
		
	if($dept_id == 0){	
			$sql = "SELECT DISTINCT empl_id FROM ".TB_PREF."kv_empl_info AS info, ".TB_PREF."kv_empl_salary AS salary WHERE info.status=1 AND info.empl_id = salary.empl_id AND salary.month !=".abs($month)." AND salary.year != ".abs($year);  
			
	} else{
		$sql="SELECT DISTINCT info.empl_id, job.joining, info.status, info.date_of_status_change FROM ".TB_PREF."kv_empl_job job, ".TB_PREF."kv_empl_info info, ".TB_PREF."kv_empl_salary AS salary WHERE info.empl_id= job.empl_id AND info.status=1 AND job.department=".db_escape($dept_id)." AND info.empl_id = salary.empl_id AND salary.month !=".abs($month)." AND salary.year != ".abs($year);		
	}
	return db_query($sql, _("The employee table is inaccessible"));
}

function kv_get_empl_name($id, $last=false) {
	if($last)
		$name = "CONCAT(empl_firstname,' ', empl_lastname) AS Name"; 
	else
		$name = "empl_firstname" ; 

	$sql = "SELECT ".$name." FROM ".TB_PREF."kv_empl_info WHERE empl_id=".db_escape($id)."LIMIT 1";

	$result = db_query($sql, _("could not get sales type"));
	$row = db_fetch_row($result);
	return isset($row[0]) ? $row[0] : 0;
}
 
//--------------------------------------------------------------------------------------------
function db_has_employees(){
	return check_empty_result("SELECT COUNT(*) FROM ".TB_PREF."kv_empl_info");
}

function db_has_employee_payslips($empl_id){
	return check_empty_result("SELECT COUNT(*) FROM ".TB_PREF."kv_empl_salary WHERE empl_id= ".db_escape($empl_id)." AND net_pay <> -1 ");
}
//--------------------------------------------------------------------------------------------
function db_has_employee_email($email){
	return check_empty_result("SELECT COUNT(*) FROM ".TB_PREF."kv_empl_info WHERE email=".db_escape($email));
}

function db_has_selected_employee($empl_id){
	return check_empty_result("SELECT COUNT(*) FROM ".TB_PREF."kv_empl_info WHERE empl_id=".db_escape($empl_id));
}
//--------------------------------------------------------------------------------------------
function count_employees(){
	$sql = "SELECT * FROM ".TB_PREF."kv_empl_info";
	$result = db_query($sql, _("could not get employee"));
	return db_num_rows($result);
}


function delete_employee($empl_id){
	$tables_array = array('kv_empl_info', 'kv_empl_job', 'kv_empl_attendancee', 'kv_empl_cv', 'kv_empl_degree', 'kv_empl_experience', 'kv_empl_loan', 'kv_empl_salary', 'kv_empl_training');
	foreach($tables_array as $tabl){
		$sql="DELETE FROM ".TB_PREF."{$tabl} WHERE `empl_id`=".db_escape($empl_id);
		db_query($sql, _("could not delete the selected Employee"));
	}
}
function db_has_auto_empl_id(){
	return check_empty_result("SELECT COUNT(*) FROM ".TB_PREF."kv_empl_option WHERE option_name = 'empl_ref_type' AND option_value = 1");
}

function kv_get_next_empl_id() {
	 $sql = "SELECT option_value FROM ".TB_PREF."kv_empl_option WHERE option_name='next_empl_id' LIMIT 1";
	 $result = db_query($sql, _("could not get sales type"));
	 $row = db_fetch_row($result);
	 return $row[0];
}

function delete_empl_id(){
	$sql="DELETE FROM ".TB_PREF."kv_empl_option WHERE option_name= ='next_empl_id'" ;
	db_query($sql, _("could not delete the selected Employee")); 
}

function kv_add_next_empl_id_new($empl_id){ 
	$option_value = (int)substr($empl_id, 1); 
	$option_value = $option_value+1 ; 
	$option_2 = substr($empl_id, 0,1); 
	$empl_next_id = $option_2.(string)$option_value; 

	$sql_insert = "";				
	db_query($sql_insert, _("The employee could not be added"));	
}
function kv_update_next_empl_id_new($empl_id, $new=false){ 
	$option_value = (int)$empl_id +1;  
	if($new)
		$sql = "INSERT INTO ".TB_PREF."kv_empl_option (option_name,option_value) VALUES ('next_empl_id',$option_value)";
	else
		$sql = "UPDATE ".TB_PREF."kv_empl_option SET option_value=".db_escape($option_value)." WHERE option_name ='next_empl_id' ";
	db_query($sql, _("The employee could not be updated"));
}

function kv_get_empl_desig($id) {
	$sql = "SELECT desig FROM ".TB_PREF."kv_empl_job WHERE empl_id=".db_escape($id)."LIMIT 1";
	$result = db_query($sql, _("could not get sales type"));
	$row = db_fetch_row($result);
	return $row[0];
}

function check_db_has_salary_account($msg){
    if (!check_empty_result("SELECT COUNT(*) FROM ".TB_PREF."kv_empl_option WHERE option_name='salary_account'"))  {
    	display_error($msg, true);
    	end_page();
    	exit;	
    }	
}

function check_db_has_employees($msg){	
    if (!check_empty_result("SELECT COUNT(*) FROM ".TB_PREF."kv_empl_info"))  {
    	display_error($msg, true);
    	end_page();
    	exit;	
    }	
}

function get_employee_join_date($empl_id){
	$sql = "SELECT joining FROM ".TB_PREF."kv_empl_job WHERE empl_id=".db_escape($empl_id)."LIMIT 1";
	$result = db_query($sql, _("could not get sales type"));
	$row = db_fetch_row($result);
	return $row[0];
}

function check_db_has_Departments($msg){
	global $path_to_root;
    if (!check_empty_result("SELECT COUNT(*) FROM ".TB_PREF."kv_empl_departments"))  {
    	display_error($msg, true);
    	end_page();
    	exit;	
    }	
}

function check_db_has_Allowances($msg){
	global $path_to_root;
    if (!check_empty_result("SELECT COUNT(*) FROM ".TB_PREF."kv_empl_allowances"))  {
    	display_error($msg, true);
    	end_page();
    	exit;	
    }	
}

function check_db_has_Loan_types($msg){
	global $path_to_root;
    if (!check_empty_result("SELECT COUNT(*) FROM ".TB_PREF."kv_empl_loan_types"))  {
    	display_error($msg, true);
    	end_page();
    	exit;	
    }	
}

function kv_empl_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 u.id, u.real_name,  u.inactive FROM ".TB_PREF."users AS u WHERE u.id NOT IN(SELECT user_id FROM ".TB_PREF."kv_empl_info WHERE user_id != 0 ) GROUP BY u.user_id";
   
    $mode = get_company_pref('no_user_list');

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

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

    $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'),
        'show_inactive' => $show_inactive,
        'disabled' => $disabled,
        'multi' => $multiple
    ),$class );
    if ($editkey)
        $ret .= add_edit_combo('user');
    return $ret;
} 

function kv_empl_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_empl_user_list($name, $selected_id, $all_option, $submit_on_change, $show_inactive, $editkey,$disabled, '', $multiple);
    echo "</td>\n";
}

function kv_empl_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_empl_user_list($name, $selected_id, $all_option, $submit_on_change,  $show_inactive, $editkey,$disabled,$hide_selectbox_ui, $multiple);
    echo "</td>\n</tr>\n";
} 
?>