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

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

function add_employee_attendance($empl_attend, $empl_id, $month, $year, $day, $in, $out,$dept_id){
	$dayy = (int)$day;
	$sql = "INSERT INTO ".TB_PREF."kv_empl_attendancee (empl_id, month, year, `{$dayy}`,dept_id, `{$dayy}vj_in`, `{$dayy}vj_out`) VALUES (".db_escape($empl_id).", ".db_escape($month).", ".db_escape($year).", ".db_escape($empl_attend).",".db_escape($dept_id).", ".db_escape($in).", ".db_escape($out).")";
	db_query($sql, _("The employee could not be added"));
}

//--------------------------------------------------------------------------------------------
function update_employee_attendance($empl_attend, $empl_id, $month, $year, $day, $in, $out){
	$dayy = (int)$day;
	$sql = "UPDATE ".TB_PREF."kv_empl_attendancee SET `{$dayy}` =".db_escape($empl_attend).", `{$dayy}vj_in` =".db_escape($in).", `{$dayy}vj_out` =".db_escape($out)." WHERE month=".db_escape($month)." AND empl_id=".db_escape($empl_id)." AND year=".db_escape($year);
	db_query($sql, _("The employee could not be updated"));
}

//-----------------------------------------------------------------------------------------
function GetEmployeeAvailableLeave($empl_id, $month, $year, $leave_code){
	$avl_leave = GetRow('kv_empl_leave_days', array('empl_id' => $empl_id, 'leave_char' => $leave_code), array('date' => 'DESC'));
	$employee_joining = GetSingleValue('kv_empl_job', 'joining', array('empl_id' => $empl_id));
	$current_fiscal =  get_current_fiscalyear();
	$months_with_years_list = kv_get_months_with_years_in_fiscal_year($year);
 	$ext_year = date("Y", strtotime($months_with_years_list[$month]));

	if(($leave_code == 'AL' || $leave_code == 'SL' ) && (is_date_in_fiscalyear(sql2date($employee_joining)) || is_date_in_fiscalyear(sql2date($avl_leave['date'])))){
		$per_month = number_format(($avl_leave['days']/12),1);
		if(is_date_in_fiscalyear(sql2date($employee_joining)))
			$joining = new DateTime($employee_joining);
		else
			$joining = new DateTime($avl_leave['date']);
		$end_of_fiscal = new DateTime(date2sql(end_fiscalyear()));
		$diff = $end_of_fiscal->diff($joining);		
		$total_this_year_avail_annual_leave = number_format(($per_month*$diff->m));				 	
		//$total_month_avail_annual_leave = number_format(($per_month*$diff->m));				 	
	}else{
		$total_this_year_avail_annual_leave = $avl_leave['days'];
		/*if($leave_code == 'ML')
			$total_this_year_avail_annual_leave = $avl_leave['days'];
		else{
			$per_month = number_format(($avl_leave['days']/12),1);
			$d1 = new DateTime($current_fiscal['begin']);
			$d2 = new DateTime(date('Y-m-d', strtotime($ext_year.'-'.$month.'-01')));
			
			$left_months = (12-($d1->diff($d2)->m)); 
			$total_this_year_avail_annual_leave = $left_months*$per_month;
		}*/
	}

	return $total_this_year_avail_annual_leave;
}

//------------------------------------------------------------------------------------------
function UsedPaidLeaves($empl_id, $year){
	$usedLeaves =  array();
	$sql = "SELECT paid_lop_days FROM ".TB_PREF."kv_empl_salary WHERE empl_id=".$empl_id." AND year =".$year;
	$result = db_query($sql, _("Can't get results for usedpaidleaves"));
	if(db_num_rows($result) > 0 ){
		while($row = db_fetch($result)){
		$data = @unserialize($row['paid_lop_days']);
		if ($data !== false)
	   		$usedLeaves[] = unserialize($row['paid_lop_days']);
		}
	}
	$finalReturn =  array();
	foreach($usedLeaves as $leave){
		foreach($leave as $key => $value){
			$finalReturn[$key] += $value;
		}
	}
	return $finalReturn;
}

//--------------------------------------------------------------------------------------------
function get_employees_attendances($attendance_date, $dept_id){
	//echo date2sql($attendance_date);
	$month = (int)date("m", strtotime(date2sql($attendance_date)));
	$day = date("d", strtotime(date2sql($attendance_date)));
	$year = get_fiscal_year_id_from_date($attendance_date);

	$degrees = array();
	$dayy = (int)$day;
	$sql = "SELECT empl_id, `{$dayy}` , `{$dayy}vj_in`, `{$dayy}vj_out` FROM ".TB_PREF."kv_empl_attendancee WHERE year=".(db_escape($year))." AND month=".(db_escape($month))." AND dept_id=".(db_escape($dept_id));
	$ret = db_query($sql, _("could not retrieve security roles"));
	while($get_des=db_fetch_assoc($ret)){
		$degrees[$get_des['empl_id']] = $get_des[$dayy];	
		$degrees[$get_des['empl_id'].'vj_in'] = $get_des[$dayy.'vj_in'];	
		$degrees[$get_des['empl_id'].'vj_out'] = $get_des[$dayy.'vj_out'];			
	}
	return $degrees;
}

//--------------------------------------------------------------------------------------------
function ChangeAttendanceInactive($empl_id, $from, $to=null){

	if($to == null )
		Update('kv_empl_attendance', ['empl_id' => $empl_id, 'a_date' => date2sql($from)], ['inactive' => 1 ]);
	else {
		$begin = new DateTime(date2sql($from));
		$end = new DateTime(date2sql($to));

		$sql  = " UPDATE ".TB_PREF."kv_empl_attendance SET inactive = 1 WHERE empl_id = ".db_escape($empl_id)." AND a_date >= ".db_escape(date2sql($from))." AND a_date <= ".db_escape(date2sql($to));
		db_query($sql, " Can't change attendate date inactive");
		// $interval = DateInterval::createFromDateString('1 day');
		// $period = new DatePeriod($begin, $interval, $end);

		// foreach ($period as $dt) {
		// 	Update('kv_empl_attendance', ['empl_id' => $empl_id, 'a_date' => $dt->format('Y-m-d')], ['inactive' => 1 ]);
		// }
		// // Update('kv_empl_attendance', ['empl_id' => $empl_id, 'a_date' => $to], ['inactive' => 1 ]);
		// Update('kv_empl_attendance', ['empl_id' => $empl_id, 'a_date' => date2sql($to)], ['inactive' => 1 ]);
	}
}
//--------------------------------------------------------------------------------------------
function GetEmplAttendanceDuration($empl_id, $from = null, $to = null, $month = null, $year = null, $dimension = -1, $dimension2= -1, $terminate_date =null, $weekly_off =false ){
	$hrmsetup = get_hrm_option();

	$payroll_monthly_choice =$hrmsetup['monthly_choice'];
		
	if($month==null){			 
		$month = $_POST['month'];
	}
	if($year==null){			 
		$year = $_POST['year'];
	}
	$months_with_years_list = kv_get_months_with_years_in_fiscal_year($year);
 	$ext_year = date("Y", strtotime($months_with_years_list[(int)get_post('month')]));	

	$sql = "SELECT at.a_date, at.empl_id, at.duration AS Duration, at.ot AS OT, at.sot AS SOT, at.dimension, at.dimension2, info.al/12 AS al , at.code, info.sl/12 AS sl,info.ml/12 AS ml, info.weekly_off AS weekly_off FROM ".TB_PREF."kv_empl_attendance AS at, ".TB_PREF."kv_empl_job AS info WHERE at.empl_id = info.empl_id AND at.empl_id=".db_escape($empl_id);
	$sql0 = " SELECT COUNT(a_date) FROM ".TB_PREF."kv_empl_attendance WHERE empl_id = ".db_escape($empl_id)." AND code = 'P' ";
	if($dimension != -1){
		$sql .=" AND dimension = ".db_escape($dimension);
		$sql0 .=" AND dimension =". db_escape($dimension);
	}	
	if($dimension != -1)
		$sql .=" AND dimension2 = ".db_escape($dimension2);	
	
	if($month > 0 && $year > 0 ){
		if($payroll_monthly_choice == 1){
			$to = end_month(sql2date($months_with_years_list[(int)get_post('month')])); 
			$from = begin_month(sql2date($months_with_years_list[(int)get_post('month')])); 
			$total_days =  date("t", strtotime($ext_year."-".$month."-01"));
		} elseif($payroll_monthly_choice == 2){
			$total_days =  ((int)date("t", strtotime($ext_year."-".$month."-01"))-$hrmsetup['BeginDay'])+$hrmsetup['EndDay'];
			$nxt_mnth =  (($month < 12)? ($month+1) : 1);
			
			$from = sql2date(date('Y-m-d', strtotime($ext_year.'-'.$month.'-'.$hrmsetup['BeginDay'])));
			$to = sql2date(date('Y-m-d', strtotime($ext_year.'-'.$nxt_mnth.'-'.$hrmsetup['EndDay'])));
		}elseif($payroll_monthly_choice == 3){
			$pre_mnth = 0 ;
			$pre_mnth =  (($month > 1)? ($month-1) : 12);
					if($pre_mnth == 12 )
						$prev_ext_year = $ext_year-1;
					else
						$prev_ext_year = $ext_year;

					$total_days =  date("t", strtotime($prev_ext_year."-".$pre_mnth."-01"));
			
			$from = sql2date(date('Y-m-d', strtotime($prev_ext_year.'-'.$pre_mnth.'-'.$hrmsetup['BeginDay'])));
			$to = sql2date(date('Y-m-d', strtotime($ext_year.'-'.$month.'-'.$hrmsetup['EndDay'])));				
		}
	}
	
	if(is_date($from)){
		$sql .=" AND a_date >= ".db_escape(date2sql($from));
		$sql0 .=" AND a_date >= ".db_escape(date2sql($from));
	}
	if(is_date($to)){
		$sql .=" AND a_date <= ".db_escape(date2sql($to));
		$sql0 .=" AND a_date <= ".db_escape(date2sql($to));
	}
	if($terminate_date){
		$sql .=" AND a_date <= ".db_escape(date2sql($terminate_date));
		$sql0 .=" AND a_date <= ".db_escape(date2sql($terminate_date));
	} 
		
	$sql .= " AND !inactive ORDER BY a_date " ;
	$sql0 .= " GROUP BY a_date " ;
	//display_error($sql);
	$ret = db_query($sql, _("could not retrieve attendance"));	
	$final = [];
	$workable_days= $workable_hrs = $days_count = 0 ;
	$shift = GetEmplShiftDetails($empl_id);
	$time = strtotime($shift['EndTime']) - strtotime($shift['BeginTime']);
		//display_error($time.'-'.$shift['EndTime'].'-'.$shift['BeginTime']);
	if($time < 0 ) {
		$attendance_date_plus_1 = date2sql(add_days(Today(), 1));
		$time  = strtotime($attendance_date_plus_1.' '.$shift['EndTime']) - strtotime($shift['BeginTime']); 
	}
	// $datetime1 = new DateTime($from);
	// $datetime2 = new DateTime($to);
	// $days_count = $datetime1->diff($datetime2);
	if(db_num_rows($ret)> 0){
		$Duration = $OT = $SOT = $ml_code = $sl_code = $at_code = $last_date = $off_days = $off_days_marked = 0;
		while($cont = db_fetch_assoc($ret)){
			$Duration  += $cont['Duration'];
			$OT  += $cont['OT'];
			$SOT  += $cont['SOT'];
			$dimension = $cont['dimension'];
			$dimension2 = $cont['dimension2'];
			$al = $cont['al'];
			// $weekly_off = unserialize(base64_decode($cont['weekly_off']));
			if(!$cont['weekly_off'])
			   	$weekly_off = $hrmsetup['weekly_off'];
			else 
				$weekly_off=unserialize(base64_decode($cont['weekly_off']));
			
			if($cont['code'] == 'AL')
				$at_code++;
			if($cont['code'] == 'SL')
				$sl_code++;
			if($cont['code'] == 'ML')
				$ml_code++;
			$day_letters =date("D", strtotime($cont['a_date'])) ;	
			//display_error($day_letters.'__'.$cont['code'].'___'.$cont['a_date']);	
			if(is_array($weekly_off) && !in_array($day_letters, $weekly_off ) ){
				if($cont['code'] == 'P' || $cont['code'] == 'OD')
					$days_count++;
				if($cont['code'] == 'HD'){
					$workable_days += 0.5;
					$days_count += 0.5;
				} else 	
					$workable_days++;
				
				$workable_hrs += $time;				
			} else {
				//display_error($cont['code'].'___'.$cont['a_date']);
				if($cont['code'] == 'A')
					$off_days_marked += $time;
			}
			$last_date = $cont['a_date'];
		}	
		$final[] = array('Duration' => $Duration, 'OT' => $OT, 'SOT' => $SOT, 'dimension' => $dimension, 'dimension2' => $dimension2, 'al' => $at_code, 'sl' => $sl_code, 'ml' => $ml_code,'weekly_off'=>$weekly_off, 'off_days' => $off_days, 'from' => $from, 'to' => $to);	
	
	}
	
if(!empty($final)){
	//	display_error($last_date);
		$begin = new DateTime(date2sql($from));
		$end = new DateTime(date2sql(add_days($to,1)));

		$interval = DateInterval::createFromDateString('1 day');
		$period = new DatePeriod($begin, $interval, $end);

		// 	$kv=0;
		// 	for($kv; $kv<=$total_days; $kv++){
		// 		if($payroll_monthly_choice == 1){
		// 				$day_letters = date("D", strtotime($ext_year."-".$month."-".$kv)) ;
		// 		}elseif($payroll_monthly_choice == 2){
		// 				$day_letters = date("D", strtotime($ext_year."-".$month."-".$kv)) ;
		// 		}elseif($payroll_monthly_choice == 3 && $hrmsetup['BeginDay'] <= $kv){
		// 				$day_letters = date("D", strtotime($prev_ext_year."-".$pre_mnth."-".$kv)) ; 

		// 			$day= date("Y-m-d", strtotime($prev_ext_year."-".$pre_mnth."-".$kv)) ;
		// 				display_error($day);
		// 			}
		// 	if(is_array($weekly_off) && in_array($day_letters, $weekly_off )){
		// 		$off_days += $time;
		// 		// display_error(json_encode($off_days));
		// 	}
		// }

		foreach ($period as $dt) {
			$day_letters =date("D", strtotime($dt->format("Y-m-d")));	
//--------Weekly_off to abs -----------//
			$date_count = date("Y-m-d", strtotime($dt->format("Y-m-d"))) ;
			if(in_array($day_letters, $weekly_off ) ){

				$Weekly_date =date("Y-m-d", strtotime($date_count)) ;
					$off_days += $time;	

			}
		}
			$off_days = ($off_days - $off_days_marked);
//---END-----Weekly_off to abs -----------//
		$total_working_days = get_hrm_option('total_working_days');
		if(!$total_working_days){
		    if($payroll_monthly_choice == 1 || $payroll_monthly_choice == 2 )
		    	$d=cal_days_in_month(CAL_GREGORIAN,$month,$ext_year);
		    elseif($payroll_monthly_choice == 3)
		        $d=cal_days_in_month(CAL_GREGORIAN,$pre_mnth,$ext_year);
		     
			$final[0]['total_hrs'] = $d*($time);
			$final[0]['off_days'] = $off_days;
		
			$final[0]['total_days'] =  $d;

			$final[0]['days_count'] = $days_count;
		} else {
			$final[0]['total_hrs'] = $workable_hrs;
			$final[0]['total_days'] = $workable_days;
			$final[0]['days_count'] = $days_count;			
		}	
		// $res = db_query($sql0, _("Can't get date Count "));
		// if(db_num_rows($res)> 0 )
		// 	$final[0]['days'] = db_num_rows($res);
		// else {
		// 	$final[0]['Duration'] = 0;	_
		 	$final[0]['days'] = $final[0]['days_count'];	
		// }
		return $final;

	}else
		return false;
}

//--------------------------------------------------------------------------------------------
function arun_GetEmplAttendanceDuration($empl_id, $from = null, $to = null, $month = null, $year = null, $dimension = -1, $dimension2= -1, $terminate_date =null, $weekly_off =false ){

	$hrmsetup = get_hrm_option();

	$sql = "SELECT at.empl_id, at.a_date, at.duration AS Duration, at.ot AS OT, at.sot AS SOT, at.dimension, at.dimension2, info.al/12 AS al , at.code, info.sl/12 AS sl, info.weekly_off AS weekly_off FROM ".TB_PREF."kv_empl_attendance AS at, ".TB_PREF."kv_empl_job AS info WHERE at.empl_id = info.empl_id AND at.empl_id=".db_escape($empl_id);
	$sql0 = " SELECT COUNT(a_date) FROM ".TB_PREF."kv_empl_attendance WHERE empl_id = ".db_escape($empl_id)." AND code = 'P' ";
	if($dimension != -1){
		$sql .=" AND dimension = ".db_escape($dimension);
		$sql0 .=" AND dimension =". db_escape($dimension);
	}	
	if($dimension != -1)
		$sql .=" AND dimension2 = ".db_escape($dimension2);	
	
	// if($month > 0 && $year > 0 ){
	// 	$months_with_years_list = kv_get_months_with_years_in_fiscal_year($year);
	// 	$to = end_month(sql2date($months_with_years_list[(int)get_post('month')])); 
	// 	$from = begin_month(sql2date($months_with_years_list[(int)get_post('month')])); 
	// }
	
	// if(is_date($from)){
	// 	$sql .=" AND a_date >= ".db_escape(date2sql($from));
	// 	$sql0 .=" AND a_date >= ".db_escape(date2sql($from));
	// }
	// if(is_date($to)){
	// 	$sql .=" AND a_date <= ".db_escape(date2sql($to));
	// 	$sql0 .=" AND a_date <= ".db_escape(date2sql($to));
	// }
	// if($terminate_date){
	// 	$sql .=" AND a_date <= ".db_escape(date2sql($terminate_date));
	// 	$sql0 .=" AND a_date <= ".db_escape(date2sql($terminate_date));
	// }
	
	//$sql .= " GROUP BY dimension, dimension2 " ;
	// $sql0 .= " AND !inactive GROUP BY a_date " ;
	// //display_error($sql);
	// $ret = db_query($sql, _("could not retrieve attendance"));	
	// $final = [];

	//$options = get_hrm_option('BeginDay');
	//display_error(json_encode($options));
//--------------------------------------

	$payroll_monthly_choice =$hrmsetup['monthly_choice'];
		
	if($month==null){			 
		$month = $_POST['month'];
	}
	if($year==null){			 
		$year = $_POST['year'];
	}
	$months_with_years_list = kv_get_months_with_years_in_fiscal_year($year);
 	$ext_year = date("Y", strtotime($months_with_years_list[(int)get_post('month')]));
	if($payroll_monthly_choice == 1){
		$total_days =  date("t", strtotime($ext_year."-".$month."-01"));
	} elseif($payroll_monthly_choice == 2){
		$total_days =  date("t", strtotime($ext_year."-".$month."-01"));
		if( $hrmsetup['EndDay'] < 31){	
			$kv_end_days = $hrmsetup['EndDay'];		
			$nxt_mnth =  (($month > 1)? ($month+1) : 12);
			}
	}elseif($payroll_monthly_choice == 3){
		if($hrmsetup['BeginDay'] >= 1 && $hrmsetup['BeginDay'] < 31){	
		$kv_begin_day = $hrmsetup['BeginDay'];		
			$pre_mnth =  (($month > 1)? ($month-1) : 12);
			$total_days =  (date("t", strtotime($year."-".$pre_mnth."-01"))- $hrmsetup['BeginDay'])+1;
	}
		if($hrmsetup['EndDay'] < 31){
			$total_days +=$hrmsetup['EndDay'];
			}
		}

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

if ($payroll_monthly_choice == 1) {

					$total_days =  date("t", strtotime($ext_year."-".$month."-01"));					
					$beginDay = date("Y-m-d", strtotime($ext_year."-".$month."-01"));// atten	
					$weekly_off1 = GetSingleValue('kv_empl_job', 'weekly_off', array('empl_id'=>$empl_id));

							$sql = "SELECT * FROM ".TB_PREF."kv_empl_attendance WHERE empl_id=".db_escape($empl_id)." AND a_date >= '".$beginDay."' AND a_date <= '".date("Y-m-t", strtotime($ext_year."-".$month."-01"))."' GROUP BY a_date, dimension ORDER BY a_date ASC";
							$res = db_query($sql, "Can't get attendance");
							$final = array();
							
							while($row2 = db_fetch_assoc($res)){
								$final[$row2['a_date']][$row2['dimension']] = $row2;
							}	
							
							$duration = $OT_hours = $SplOT = 0;
							for($vj=1; $vj <= $total_days; $vj++){							
								if(isset($final[date("Y-m-d", strtotime($ext_year."-".$month."-".$vj))])){
							
								$val = $final[date("Y-m-d", strtotime($ext_year."-".$month."-".$vj))][0];
										$duration += $val['duration'];
										$OT_hours += $val['ot'];
										$SplOT += $val['sot'];
									}
							} 
							$hours = floor($duration / 3600);
							$mins = floor($duration / 60 % 60);
							$workable_hrs = $hours.':'.$mins;
							$final[0]['total_hrs'] = $workable_hrs;


							$ot_h = floor($OT_hours / 3600);
							$ot_min = floor($OT_hours / 60 % 60);
							$OT_hours_mins = $ot_h.':'.$ot_min;
							$final[0]['ot']= $OT_hours_mins; 

							$sot_h = floor($SplOT / 3600);
							$sot_min = floor($SplOT / 60 % 60);
							$SOT_hours_mins = $sot_h.':'.$sot_min; 
							$final[0]['sot'] = $SOT_hours_mins;

}elseif ($payroll_monthly_choice == 2) {

						$kv_begin_days = $hrmsetup['BeginDay'];
					$total_days =  date("t", strtotime($ext_year."-".$month."-01"));					
					$beginDay = date("Y-m-d", strtotime($ext_year."-".$month."-01"));// atten	
					$weekly_off1 = GetSingleValue('kv_empl_job', 'weekly_off', array('empl_id'=>$empl_id));

							$sql = "SELECT * FROM ".TB_PREF."kv_empl_attendance WHERE empl_id=".db_escape($empl_id)." AND a_date >= '".$beginDay."' AND a_date <= '".date("Y-m-t", strtotime($ext_year."-".$nxt_mnth."-01"))."' GROUP BY a_date, dimension ORDER BY a_date ASC";
							$res = db_query($sql, _("Can't get attendance"));
							$final = array();
							
							while($row2 = db_fetch_assoc($res)){
								$final[$row2['a_date']][$row2['dimension']] = $row2;
							}	
							
							$duration = $OT_hours = $SplOT = 0;
							for($vj=$kv_begin_days; $vj <= $total_days; $vj++){							
								if(isset($final[date("Y-m-d", strtotime($ext_year."-".$month."-".$vj))])){
							
								$val = $final[date("Y-m-d", strtotime($ext_year."-".$month."-".$vj))][0];
										$duration += $val['duration'];
										$OT_hours += $val['ot'];
										$SplOT += $val['sot'];
									}
							} 
							if($hrmsetup['EndDay'] < 31){
							for($vj=1; $vj <=$hrmsetup['EndDay']; $vj++){							
								if(isset($final[date("Y-m-d", strtotime($ext_year."-".$nxt_mnth."-".$vj))])){
									
									$val = $final[date("Y-m-d", strtotime($ext_year."-".$nxt_mnth."-".$vj))][0];
										
										$duration += $val['duration'];
										$OT_hours += $val['ot'];
										$SplOT += $val['sot'];
									}
							} 

						}
							$hours = floor($duration / 3600);
							$mins = floor($duration / 60 % 60);
							$workable_hrs = $hours.':'.$mins;
							$final[0]['total_hrs'] = $workable_hrs;

							$ot_h = floor($OT_hours / 3600);
							$ot_min = floor($OT_hours / 60 % 60);
							$OT_hours_mins = $ot_h.':'.$ot_min;
							$final[0]['ot']= $OT_hours_mins; 

							$sot_h = floor($SplOT / 3600);
							$sot_min = floor($SplOT / 60 % 60);
							$SOT_hours_mins = $sot_h.':'.$sot_min; 
							$final[0]['sot'] = $SOT_hours_mins;


		}elseif ($payroll_monthly_choice == 3) {
					$pre_mnth = 0 ;
						$kv_begin_days = $hrmsetup['BeginDay'];
					$pre_mnth =  (($month > 1)? ($month-1) : 12);
					if($pre_mnth == 12 )
							$prev_ext_year = $ext_year-1;
						else
							$prev_ext_year = $ext_year;

					$total_days =  date("t", strtotime($prev_ext_year."-".$pre_mnth."-01"));					
					$beginDay = date("Y-m-d", strtotime($prev_ext_year."-".$pre_mnth."-01"));// atten	
					$weekly_off1 = GetSingleValue('kv_empl_job', 'weekly_off', array('empl_id'=>$empl_id));

							$sql = "SELECT * FROM ".TB_PREF."kv_empl_attendance WHERE empl_id=".db_escape($empl_id)." AND a_date >= '".$beginDay."' AND a_date <= '".date("Y-m-t", strtotime($ext_year."-".$month."-01"))."' GROUP BY a_date, dimension ORDER BY a_date ASC";
							$res = db_query($sql, _("Can't get attendance"));
							$final = array();
							
							while($row2 = db_fetch_assoc($res)){
								$final[$row2['a_date']][$row2['dimension']] = $row2;
							}	
							
							$duration = $OT_hours = $SplOT = 0;
							for($vj=$kv_begin_days; $vj <= $total_days; $vj++){							
								if(isset($final[date("Y-m-d", strtotime($prev_ext_year."-".$pre_mnth."-".$vj))])){
							
								$val = $final[date("Y-m-d", strtotime($prev_ext_year."-".$pre_mnth."-".$vj))][0];
										$duration += $val['duration'];
										$OT_hours += $val['ot'];
										$SplOT += $val['sot'];
									}
							} 
							if($hrmsetup['EndDay'] < 31){
							for($vj=1; $vj <=$hrmsetup['EndDay']; $vj++){							
								if(isset($final[date("Y-m-d", strtotime($ext_year."-".$month."-".$vj))])){
									
									$val = $final[date("Y-m-d", strtotime($ext_year."-".$month."-".$vj))][0];
										
										$duration += $val['duration'];
										$OT_hours += $val['ot'];
										$SplOT += $val['sot'];
									}
							} 
						}
							$hours = floor($duration / 3600);
							$mins = floor($duration / 60 % 60);
							$workable_hrs = $hours.':'.$mins;
							$final[0]['total_hrs'] = $workable_hrs;

							$ot_h = floor($OT_hours / 3600);
							$ot_min = floor($OT_hours / 60 % 60);
							$OT_hours_mins = $ot_h.':'.$ot_min;
							$final[0]['ot']= $OT_hours_mins; 

							$sot_h = floor($SplOT / 3600);
							$sot_min = floor($SplOT / 60 % 60);
							$SOT_hours_mins = $sot_h.':'.$sot_min; 
							$final[0]['sot'] = $SOT_hours_mins;
		}					
		return $final;		
}
//--------------------------------------------------------------------------------------------
function GetEmployeesAttendanceDuration($empl_ids, $from = null, $to = null, $month = null, $year = null, $dimension = -1, $dimension2= -1 ){
	
	$sql = "SELECT empl_id, SUM(duration) AS Duration, SUM(ot) AS OT, dimension, dimension2 FROM ".TB_PREF."kv_empl_attendance WHERE empl_id IN (".$empls_id = implode(',', array_map('intval', $empl_ids)).")";
	
	if($dimension != -1)
		$sql .=" AND dimension = ".db_escape($dimension);
		
	if($dimension != -1)
		$sql .=" AND dimension2 = ".db_escape($dimension2);	
	
	if($month > 0 && $year > 0 ){
		$months_with_years_list = kv_get_months_with_years_in_fiscal_year($year);
		$to = end_month(sql2date($months_with_years_list[get_post('month')])); 
		$from = begin_month(sql2date($months_with_years_list[get_post('month')])); 		
	}
	
	if(is_date($from)){
		$sql .=" AND a_date >= ".db_escape(date2sql($from));
	}
	if(is_date($to)){
		$sql .=" AND a_date <= ".db_escape(date2sql($to));
	}
	
	$sql .= " GROUP BY empl_id, dimension, dimension2 " ;
	$ret = db_query($sql, _("could not retrieve security roles"));
	$final = array();
	while($cont = db_fetch_assoc($ret))
		$final[] = $cont;
	
	if(!empty($final))
		return $final;
	else
		return false;
}
function GetEmplShiftDetails($empl_id){
	$sql ="SELECT * FROM ".TB_PREF."kv_empl_shifts AS shift INNER JOIN ".TB_PREF."kv_empl_job AS job ON job.shift=shift.id AND job.shift > 0 AND job.empl_id = ".db_escape($empl_id);
	
	$res = db_query($sql, _("Cant get shift results"));
	if(db_num_rows($res) > 0 ){
		if($row = db_fetch($res)){
			return $row;
		} else
			$return = false;
	} else
		$return = false;
		
	if($return == false){
		$shift['BeginTime'] = get_hrm_option('BeginTime');
		$shift['EndTime'] = get_hrm_option('EndTime');			
		return $shift;		
	}
}

//--------------------------------------------------------------------------------------------
function get_employee_attendances($attendance_date, $empl_id){
	//echo date2sql($attendance_date);
	$month = (int)date("m", strtotime(date2sql($attendance_date)));
	$day = date("d", strtotime(date2sql($attendance_date)));
	$year = get_fiscal_year_id_from_date($attendance_date);

	$degrees = array();
	$dayy = (int)$day;
	$sql = "SELECT empl_id, `{$dayy}` , `{$dayy}vj_in`, `{$dayy}vj_out` FROM ".TB_PREF."kv_empl_attendancee WHERE year=".(db_escape($year))." AND month=".(db_escape($month))." AND empl_id=".(db_escape($empl_id));
	$ret = db_query($sql, "could not retrieve security roles");
	while($get_des=db_fetch_assoc($ret)){
		$degrees[$get_des['empl_id']] = $get_des[$dayy];	
		$degrees[$get_des['empl_id'].'vj_in'] = $get_des[$dayy.'vj_in'];	
		$degrees[$get_des['empl_id'].'vj_out'] = $get_des[$dayy.'vj_out'];			
	}
	return $degrees;
}

//--------------------------------------------------------------------------------------------
function get_fiscal_year_id_from_date($date){
	$attendance_date = strtotime(date2sql($date));
	$all_fiscal_years  = get_all_fiscalyears();
	$year = 0;	
	while($get_des=db_fetch($all_fiscal_years)){
		$begin = strtotime($get_des['begin']); 
		$end = strtotime($get_des['end']); 
		if( $begin <= $attendance_date && $end >= $attendance_date){
			$year = $get_des['id'];
			break;
		}
		//display_error(date2sql($date).'--'.$attendance_date.'==='.$begin.'--'.$end);
	}
	return $year; 
}

function get_dep_employees_count($dep_id, $attendance_date) {
	
	//$sql= "SELECT COUNT(empl_id) FROM ".TB_PREF."kv_empl_job  WHERE department=".db_escape($dep_id); 
	$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($dep_id); 	
	$result = db_query($sql, _("could not get department employee count"));
	$employees_count = 0;
	while($row=db_fetch($result)){
		if($row['joining'] <= date2sql($attendance_date) && ($row['status'] == 1 || ($row['status']>1 && $row['date_of_status_change'] >= date2sql($attendance_date)))){
			$employees_count++;
		}
	}
	return $employees_count; 
} 

//---------------------------------------------------------------------------------
function get_employee_whole_attendance($empl_id, $year=null){
	$sql = "SELECT * FROM ".TB_PREF."kv_empl_attendance WHERE empl_id=".db_escape($empl_id);
	
	if($year){
		$f_year = GetRow('fiscal_year', array('id' => $year));		
		$sql .=" AND a_date >= ".db_escape($f_year['begin'])." AND a_date <= ".db_escape($f_year['end']);		
	}
	$sql .= ' ORDER BY a_date';
	$attendance = array();
	$ret = db_query($sql, _("Can't get empl attendance"));
	
	while($cont = db_fetch_assoc($ret)) {	
		$day = (int)date('d', strtotime($cont['a_date']));
		$attendance[(int)date('m', strtotime($cont['a_date']))][$day] = $cont['code'];
	}
	return $attendance;
}

/*old functions*/

function add_employees_attendance($a_date, $employees){
		$a_date = date2sql($a_date); 
		$sql = "INSERT INTO ".TB_PREF."kv_empl_attendance (a_date, employees) VALUES (".db_escape($a_date).", ".db_escape(implode(';', $employees)).")";

	db_query($sql, _("The employee could not be added"));
}


//--------------------------------------------------------------------------------------------
function update_employees_attendance($a_date, $employees){
	$a_date = date2sql($a_date); 
	$sql = "UPDATE ".TB_PREF."kv_empl_attendance SET employees=".db_escape(implode(';',$employees))." WHERE a_date=".db_escape($a_date);

	db_query($sql, _("The employee could not be updated"));
}

//--------------------------------------------------------------------------------------------
function db_has_day_attendance($a_date){
	$a_date = date2sql($a_date);
	return check_empty_result("SELECT COUNT(*) FROM ".TB_PREF."kv_empl_attendance WHERE a_date=".db_escape($a_date));
}

function get_empl_attendance_for_month($empl_id,$month, $year) {

 	$leave_Day = 0 ;
	$get_des=GetRow('kv_empl_attendancee', array('empl_id' => $empl_id,'month' => $month, 'year' => $year));
	if($get_des != null || !empty($get_des)){										
		foreach($get_des as $single){
			if($single == 'A')
				$leave_Day += 1;
			if($single == 'HD')
				$leave_Day += 0.5;
		}
	}
	return $leave_Day;
}

function get_employee_attendance_details($empl_id, $start, $end) {

	$start_date = date2sql($start);
	$end_date = date2sql($end);
	$leave_dates=array();
	$sql= "SELECT a_date FROM ".TB_PREF."kv_empl_attendance  WHERE a_date >= ".db_escape($start_date)." AND a_date <= ".db_escape($end_date)." AND employees LIKE '%".$empl_id."%'"; 
	$result = db_query($sql, _("could not get sales type"));

	while($get_des=db_fetch($result))
		$leave_dates[]=$get_des['a_date'];
		
	return $leave_dates;
} 

function get_employees_absent_count($empl_ids_arr, $start, $end) {

	$start_date = date2sql($start);
	$end_date = date2sql($end);
	$leave_dates=array();
	foreach($empl_ids_arr as $empl_id) {
		$sql= "SELECT COUNT(a_date) FROM ".TB_PREF."kv_empl_attendance  WHERE a_date >= ".db_escape($start_date)." AND a_date <= ".db_escape($end_date)." AND employees LIKE '%".$empl_id."%'"; 
		$result = db_query($sql, _("could not get sales type"));

		$get_des=db_fetch($result);
		$leave_dates[] = array( 'empl_id' =>$empl_id, 'days' => $get_des[0]); 
	}
	return $leave_dates;
} 
?>