<?php
/****************************************
/*  Author 	: Kvvaradha
/*  Module 	: Extended HRM
/*  E-mail 	: admin@kvcodes.com
/*  Version : 1.0
/*  Http 	: www.kvcodes.com
*****************************************/

function DeleteEmplSalary($salid){
	begin_transaction();
	$myrow = GetRow('kv_empl_salary', array('id' => $salid)); 

	$sql = "UPDATE ".TB_PREF."kv_empl_attendance SET inactive = 0 WHERE empl_id = ".db_escape($myrow['empl_id'])." AND a_date >=".(db_escape($myrow['from_date']))." AND a_date <=".db_escape($myrow['to_date']);
	db_query($sql, _("The Attendance could not be updated"));	
	if($myrow['GL'] != 0)
	Update('gl_trans', array('type' => ST_EMPLOYEE_SALARY, 'type_no' => $salid), array('amount' => 0));

	$myall = GetAll('kv_empl_loan_repayment', array('pay_slip_id' => $salid)); 
	foreach($myall as $my){
		$periods_paid = GetSingleValue('kv_empl_loan', 'periods_paid', array('id' => $my['loan_id']));
		$re_periods = ($periods_paid - $my['periods_paid']); 
		if($my['loan_id'] != '')
		Update('kv_empl_loan', array('id' => $my['loan_id']), array('periods_paid' => $re_periods, 'status' => 0));
		if($my['id'] != '')
			Delete('kv_empl_loan_repayment', array('id' => $my['id']));
	}
	if($myrow['adv_sal'] != 0)
		Update('kv_empl_salary_advance', array('payslip_id' => $salid), array('inactive' => 0));
	Delete('kv_empl_salary_advance', array('payslip_id_adv' => $salid , 'inactive' => 0));
		
	// $del_sal = Delete('kv_empl_salary', array('id' => $salid));
Update('kv_empl_salary', array('id' => $salid), array('net_pay' => 0, 'year' => 0 , 'month' => 0));
	commit_transaction();
}
//-----------------------------------------------------------------------------
function get_empl_sal_details($id, $month, $yr, $dim=0, $dim2=0){

	if(db_has_employee_payslip($id, $month, $yr)){
		$sql = "SELECT * FROM ".TB_PREF."kv_empl_salary WHERE empl_id=".db_escape($id)." AND month=".db_escape((int)$month)." AND year=".db_escape($yr);
		if($dim > 0 )
			$sql .=" AND dimension=".db_escape($dim);
		if($dim2 > 0 )
			$sql .=" AND dimension2 = ".db_escape($dim2);
	}else{
		$sql = "SELECT * FROM ".TB_PREF."kv_empl_job WHERE empl_id=".db_escape($id);
	}
	$result = db_query($sql, _("could not get bonus type"));
	
	return db_fetch($result);
}

function get_empl_encashment_details($id, $month, $yr){

	if(db_has_employee_leave_encashment($id, $month, $yr)){
		$sql = "SELECT * FROM ".TB_PREF."kv_empl_leave_encashment WHERE empl_id=".db_escape($id)." AND year=".db_escape($yr);
		if($month > 0 )
			$sql .= " AND month = ".db_escape($month);
	}else{
		$sql = "SELECT * FROM ".TB_PREF."kv_empl_job WHERE empl_id=".db_escape($id);
	}	
	$result = db_query($sql, _("could not get bonus type"));	
	return db_fetch($result);
}
function kv_get_allowance_transactions($rep, $month, $year){
	$sql = "SELECT id, date, empl_id, `{$rep}` FROM ".TB_PREF."kv_empl_salary WHERE month=".db_escape($month)." AND year=".db_escape($year);
	return $result = db_query($sql, _("could not get bonus type"));
	
	//return db_fetch($result);
}

function db_has_employee_payslip($empl_id, $month, $year){
	return check_empty_result("SELECT COUNT(*) FROM ".TB_PREF."kv_empl_salary WHERE empl_id=".db_escape($empl_id)." AND month=".db_escape((int)$month)." AND year=".db_escape($year));
}

function db_has_employee_leave_encashment($empl_id,  $month, $year){
	return check_empty_result("SELECT COUNT(*) FROM ".TB_PREF."kv_empl_leave_encashment WHERE empl_id=".db_escape($empl_id)." AND year=".db_escape($year)." AND month=".db_escape($month));
}

function db_has_some_entry_to_this_allowance($rep, $month, $year){
	return check_empty_result("SELECT IF(`{$rep}` >0, 1, 0) FROM ".TB_PREF."kv_empl_salary WHERE month=".db_escape($month)." AND year=".db_escape($year));
}

function db_has_sal_for_selected_dept($dept_id, $month, $year){
	$sql = "SELECT empl_id FROM ".TB_PREF."kv_empl_salary WHERE month=".db_escape($month)." AND year=".db_escape($year);
	$salaried_employes = $selected_empl_list= array();
	$ret = db_query($sql, _("Can't get empl attendance"));	
	while($cont = db_fetch($ret)) 
		$salaried_employes[] = $cont;
	$salaried_employees = array_values($salaried_employes);

	$get_employees_list = get_empl_ids_from_dept_id($dept_id);

	//display_notification(json_encode($salaried_employees).'---'. json_encode($get_employees_list));
	
	if(empty($salaried_employees) || empty($get_employees_list)){
		return 0;
	}else{
		$selected_empl_list = array_intersect($salaried_employees, $get_employees_list);

		if(empty($selected_empl_list)){
			return 0;
		}else {
			return $selected_empl_list;
		}
	}	
}

function get_empl_name_dept($id) {
	$sql = "SELECT CONCAT_WS(' ', empl.empl_firstname, empl.empl_lastname) as name, dept.department as deptment, empl.email FROM ".TB_PREF."kv_empl_info as empl, ".TB_PREF."kv_empl_job as dept WHERE empl.empl_id=".db_escape($id)." AND dept.empl_id=".db_escape($id);
	$result = db_query($sql, _("could not get bonus type"));
	return db_fetch($result);
}

function get_empl_gross_pay($id) {
	$sql = "SELECT	gross 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 isset($row[0]) ? $row[0] : false;
}

//--------------------------------------------------------------------------------------------
function get_emply_salary($id, $year=null, $dimension=0, $dimension2=0) {

	$sql = "SELECT * FROM ".TB_PREF."kv_empl_salary WHERE empl_id=".db_escape($id)." AND net_pay <> -1 AND dimension = ".db_escape($dimension)." AND dimension2 = ".db_escape($dimension2);

	if($year)
		$sql .= ' AND year='.db_escape($year);
	$salaries = array();
	$ret = db_query($sql, _("Can't get empl attendance"));
	if(db_num_rows($ret) > 0 ){
		while($cont = db_fetch($ret)) 
			$salaries[] = $cont;
	}
	return $salaries;
}

function db_has_sal_for_selected_Date($empl_id, $month, $year){
	return check_empty_result("SELECT COUNT(*) FROM ".TB_PREF."kv_empl_salary WHERE empl_id=".db_escape($empl_id)." AND month=".db_escape($month)." AND year=".db_escape($year));
}

function get_employee_sal_history($empl_id , $year) { 
	$sql = "SELECT * FROM ".TB_PREF."kv_empl_salary WHERE empl_id=".db_escape($empl_id)." AND year=".db_escape($year);	
	$result = db_query($sql, _("could not get bonus type"));	
	return db_fetch($result);
} 

function get_gross_pay_of_an_employee($empl_id){
	
	$sql = "SELECT 	gross 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);
	if(!empty($row[0]))
		return $row[0];
	else
		return 0;
}

function get_employee_net_pay($empl_id){
	
	$Allowance = get_allowances('Deductions');
	$sum = '';
	while ($single = db_fetch($Allowance)) {	
		$sum .=$single['id']."+";
	}
	$summ = substr($sum, 0, -1);
	$sql = "SELECT SUM(".$summ.") 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);
	if(!empty($row[0]))
		$othr_ded =  $row[0];
	else
		$othr_ded = 0;

	$net_pay = get_gross_pay_of_an_employee($empl_id) - (get_empl_loan_monthly_payment($empl_id, Today()) + $othr_ded); 
	return ($net_pay > 0 ? $net_pay : 0);
}

//-----------------------------------------------------------------------------------------------
function get_empl_bank_acc_details($empl_id){
	$sql="SELECT mod_of_pay, bank_name, acc_no FROM  ".TB_PREF."kv_empl_job WHERE empl_id=".db_escape($empl_id)." LIMIT 1";
	$result = db_query($sql, _("The employee job details could not be updated")); 
	$empl_id_list=array();
	while($get_des=db_fetch($result)){
		$empl_id_list['mod_of_pay']=$get_des['mod_of_pay'];
		$empl_id_list['bank_name']=$get_des['bank_name'];
		$empl_id_list['acc_no']=$get_des['acc_no'];
	}		
	return $empl_id_list;
}


//------------------------------------------------------------------------------
function get_employee_job($employee_id){

	if(is_numeric($employee_id))
		$sql = "SELECT * FROM ".TB_PREF."kv_empl_job WHERE empl_id=".$employee_id;
	else
		$sql = "SELECT * FROM ".TB_PREF."kv_empl_job WHERE empl_id=".db_escape($employee_id);

	$result = db_query($sql, _("could not get employee"));
	return db_fetch($result);		
}

//--------------------------------------------------------------------------------------------
function db_employee_has_job($employee_id){
	$sql = "SELECT COUNT(*) FROM ".TB_PREF."kv_empl_job WHERE empl_id=".db_escape($employee_id);
	
	$result = db_query($sql, _("could not get employee"));
	
	if (db_fetch_row($result)==0) { 
		return false;	
	} else { 
		return true ;
	}
}

function get_empl_ids_from_dept_id($dept_id, $grade=null) {

	$empl_id_list=array();
	$sql= "SELECT empl_id FROM ".TB_PREF."kv_empl_job  WHERE 1 ";
	
	if($dept_id != '') {
		 $sql .= "AND department = ".db_escape($dept_id);
	}
	if($grade != '') {
		 $sql .= "AND grade = ".db_escape($grade);
	} 
	$sql .= " ORDER BY empl_id";
	
	$result = db_query($sql, _("could not get sales type"));

	while($get_des=db_fetch($result))
		$empl_id_list[]=$get_des['empl_id'];
		
	return $empl_id_list;
}


function get_empl_ids_from_dept_id_payroll($dept_id, $grade=null) {

	$empl_id_list=array();
	$sql= "SELECT info.empl_id, info.status, info.date_of_status_change, job.grade FROM ".TB_PREF."kv_empl_job AS job , ".TB_PREF."kv_empl_info AS info WHERE info.empl_id = job.empl_id ";
	
	if($dept_id != '') {
		 $sql .= " AND job.department = ".db_escape($dept_id);
	}
	if($grade != '') {
		 $sql .= " AND job.grade = ".db_escape($grade);
	}
	$sql .= " ORDER BY info.empl_id";
	$result = db_query($sql, _("could not get sales type"));

	while($get_des=db_fetch($result))
		$empl_id_list[]=$get_des;
		
	return $empl_id_list;
}

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

function hrm_get_gl_trans($type, $trans_id, $dimension=0, $dimension2=0, $employee_id=0, $month=0, $year=0){	
	
	$sql = "SELECT gl.*, cm.account_name, IFNULL(refs.reference, '') AS reference, user.real_name, 
			COALESCE(st.tran_date, dt.tran_date, bt.trans_date, grn.delivery_date, gl.tran_date) as doc_date,
			IF(ISNULL(st.supp_reference), '', st.supp_reference) AS supp_reference
	FROM ".TB_PREF."gl_trans as gl
		LEFT JOIN ".TB_PREF."chart_master as cm ON gl.account = cm.account_code
		LEFT JOIN ".TB_PREF."refs as refs ON (gl.type=refs.type AND gl.type_no=refs.id)
		LEFT JOIN ".TB_PREF."audit_trail as audit ON (gl.type=audit.type AND gl.type_no=audit.trans_no AND NOT ISNULL(gl_seq))
		LEFT JOIN ".TB_PREF."users as user ON (audit.user=user.id)
	# all this below just to retrieve doc_date :>
		LEFT JOIN ".TB_PREF."supp_trans st ON gl.type_no=st.trans_no AND st.type=gl.type AND (gl.type!=".ST_JOURNAL." OR gl.person_id=st.supplier_id)
		LEFT JOIN ".TB_PREF."grn_batch grn ON grn.id=gl.type_no AND gl.type=".ST_SUPPRECEIVE." AND gl.person_id=grn.supplier_id
		LEFT JOIN ".TB_PREF."debtor_trans dt ON gl.type_no=dt.trans_no AND dt.type=gl.type AND (gl.type!=".ST_JOURNAL." OR gl.person_id=dt.debtor_no)
		LEFT JOIN ".TB_PREF."bank_trans bt ON bt.type=gl.type AND bt.trans_no=gl.type_no AND bt.amount!=0
			 AND bt.person_type_id=gl.person_type_id AND bt.person_id=gl.person_id
		LEFT JOIN ".TB_PREF."journal j ON j.type=gl.type AND j.trans_no=gl.type_no"

		." WHERE gl.type= ".db_escape($type) 		
		." AND gl.amount <> 0";
		
	if($dimension == 0 && $dimension2 == 0) {
		$sql .= " AND gl.type_no IN (SELECT id FROM ".TB_PREF."kv_empl_salary WHERE empl_id=".db_escape($employee_id)." AND month=".db_escape($month)." AND year=".db_escape($year).")";
	} else {
		$sql .=" AND gl.type_no = ".db_escape($trans_id); 
	}
	if ($dimension > 0)
		$sql .= " AND gl.dimension_id = ".($dimension<0 ? 0 : db_escape($dimension));

	if ($dimension2 > 0)
		$sql .= " AND gl.dimension2_id = ".($dimension2<0 ? 0 : db_escape($dimension2));
	//if($dimension == 0 && $dimension2 == 0)
	//	$sql .=" GROUP BY gl.account";
	$sql .=" ORDER BY tran_date, counter";
	return db_query($sql, _("The gl transactions could not be retrieved"));
}
?>