<?php session_start();
include ('../inc/dbcon.php');
include ('../inc/functions.php');
include ('../inc/cartfunctions.php');
include ('../inc/repfunctions.php');

function check_exsal($emid,$smonth,$syear,$key,$cl=''){
global $con;
$sql="SELECT COALESCE ((SELECT ".$key." FROM tbl_salarysheet WHERE empid='$emid' AND year='$syear' AND month='$smonth'),0) AS rdata";	
$result = mysqli_query($con,$sql) or die(mysqli_error($con));
$row = mysqli_fetch_array($result);
if($cl!=''){
if($row['rdata']>0){
return $row['rdata'];    
}else{
return $cl;    
}    
}else{    
return $row['rdata'];
}
}

function get_leaveday($empid,$dstart,$smonth,$key=''){
global $con;
$lday=0;
$id='';    
$sql="SELECT * FROM tbl_leaverequest WHERE empid='$empid' AND (CAST(lfrom AS DATE) BETWEEN '$dstart' AND '$smonth' OR CAST(lto AS DATE) BETWEEN '$dstart' AND '$smonth') AND status='1'";	
$result = mysqli_query($con,$sql) or die(mysqli_error($con));
if($result->num_rows > 0){
while ($row=mysqli_fetch_array($result)){
if($row['lto']<=$smonth){
if($row['lfrom']>=$dstart){    
$lday+=$row['ldays'];
}else{
$date1=date_create($dstart);
$date2=date_create($row['lto']);
$diff=date_diff($date1,$date2);
$lday+= intval(($diff->format("%R%a"))+1);     
}
}else{
$date1=date_create($row['lfrom']);
$date2=date_create($smonth);
$diff=date_diff($date1,$date2);
$lday+= intval(($diff->format("%R%a"))+1);    
}    
}   
}else{
$lday=0;   
}
return $lday;     
}

if(isset($_POST['delsal'])){
$id=$_POST['delsal'];
$sql="DELETE FROM tbl_salarysheet WHERE id='$id'";
$results = mysqli_query($con, $sql);
$efid=mysqli_affected_rows($con);
if($efid>0){
echo 'Emplyee Salary Delete Sucess!!!';    
}else{
echo 'Emplyee Salary Delete Fail!!!';     
}    
exit;    
}

if(isset($_POST['employe'])){
if(isset($_POST['branch']) ){
$wbrid = $_POST['branch'];
$year = $_POST['year'];
$month = $_POST['month'];    
$output = '';

$dstart = $year.'-'.sprintf("%02d", $month).'-'.'01';   
$smonth = $year.'-'.sprintf("%02d", $month).'-'.cal_days_in_month(CAL_GREGORIAN,$month,$year);    
    
if($wbrid!=''){
$sql = "SELECT tbl_employe.id,tbl_employe.name,tbl_employe.salary,tbl_employe.job,tbl_employe.status,tbl_designation.name AS design FROM tbl_employe LEFT JOIN tbl_designation ON tbl_designation.id=tbl_employe.desid WHERE tbl_employe.wbrid='".$wbrid."' AND tbl_employe.job <= '$smonth' AND  tbl_employe.status='1' ORDER BY tbl_employe.name ASC";	
}    
$results = mysqli_query($con, $sql);

while ($rowcu=mysqli_fetch_array($results)){    
if(get_leaveday($rowcu['id'],$dstart,$smonth)>0){
$lapid=0;    
}else{
$lapid='';    
}
$output .='<tr>';  
$output .= '<td class="center">'.count_id().'</td>';
$output .= '<td>'.$rowcu['name'].'<input type="hidden" name="data['.$rowcu['id'].'][empid]" value="'.$rowcu['id'].'" class="form-control" autocomplete="off">'.'<input type="hidden" name="data['.$rowcu['id'].'][joindate]" value="'.$rowcu['job'].'" class="form-control" autocomplete="off">'.'<input type="hidden" name="data['.$rowcu['id'].'][salary]" value="'.$rowcu['salary'].'" class="form-control" autocomplete="off">'.'</td>';
$output .= '<td>'.$rowcu['design'].'</td>';
$output .= '<td>'.$rowcu['salary'].'</td>';
if(check_exsal($rowcu['id'],$month,$year,'pstatus')>0){
$output .= '<td><input type="number" name="data['.$rowcu['id'].'][absent]" value="'.check_exsal($rowcu['id'],$month,$year,'absent').'" class="form-control" autocomplete="off" readonly></td>';
$output .= '<td><input type="number" name="data['.$rowcu['id'].'][late]" value="'.check_exsal($rowcu['id'],$month,$year,'late').'" class="form-control" autocomplete="off" readonly></td>';    
$output .= '<td><input type="number" name="data['.$rowcu['id'].'][leave]" value="'.check_exsal($rowcu['id'],$month,$year,'leaves').'" class="form-control" autocomplete="off" readonly></td>';
$output .= '<td><input type="number" name="data['.$rowcu['id'].'][pday]" value="'.check_exsal($rowcu['id'],$month,$year,'pday').'" step="any" class="form-control" autocomplete="off" readonly></td>';
$output .= '<td><input type="number" name="data['.$rowcu['id'].'][pamo]" value="'.check_exsal($rowcu['id'],$month,$year,'pamo').'" class="form-control" autocomplete="off" readonly></td>';    	
}else{    
$output .= '<td><input type="number" name="data['.$rowcu['id'].'][absent]" value="'.check_exsal($rowcu['id'],$month,$year,'absent').'" class="form-control" autocomplete="off"></td>';
$output .= '<td><input type="number" name="data['.$rowcu['id'].'][late]" value="'.check_exsal($rowcu['id'],$month,$year,'late').'" class="form-control" autocomplete="off"></td>';    
$output .= '<td><input type="hidden" name="data['.$rowcu['id'].'][lapid]" value="'.$lapid.'" class="form-control" autocomplete="off"><input type="number" name="data['.$rowcu['id'].'][leave]" value="'.check_exsal($rowcu['id'],$month,$year,'leaves',get_leaveday($rowcu['id'],$dstart,$smonth)).'" class="form-control" autocomplete="off" readonly></td>';
$output .= '<td><input type="number" name="data['.$rowcu['id'].'][pday]" value="'.check_exsal($rowcu['id'],$month,$year,'pday').'" step="any" class="form-control" autocomplete="off"></td>';
$output .= '<td><input type="number" name="data['.$rowcu['id'].'][pamo]" value="'.check_exsal($rowcu['id'],$month,$year,'pamo').'" class="form-control" autocomplete="off"></td>';    
}
$output .='</tr>';  
}
}
echo $output;   
exit; 
}

if(isset($_POST['salary'])){   
if(isset($_POST['branch']) ){
$year = $_POST['year'];
$month = $_POST['month'];    
$wbrid = $_POST['branch'];
$output = '';
    
$queirys = "SELECT tbl_salarysheet.id,tbl_employe.id AS eid,tbl_employe.name,tbl_employe.salary,tbl_employe.job,tbl_designation.name AS design,tbl_salarysheet.absamo,tbl_salarysheet.lateamo,tbl_salarysheet.levamo,tbl_salarysheet.pdayamo,tbl_salarysheet.pamo,tbl_salarysheet.tsalary,tbl_salarysheet.pstatus,tbl_salarysheet.paid FROM tbl_employe LEFT JOIN tbl_designation ON tbl_designation.id=tbl_employe.desid LEFT JOIN tbl_salarysheet ON tbl_salarysheet.empid=tbl_employe.id WHERE tbl_salarysheet.month='".$month."' AND tbl_salarysheet.year='".$year."' AND tbl_employe.wbrid='".$wbrid."' ORDER BY tbl_employe.id ASC";

$results = mysqli_query($con, $queirys);

while ($rowcu=mysqli_fetch_array($results)){    

$output .='<tr>';   
$output .= '<td class="center">'.count_id().'</td>';
$output .= '<td>'.$rowcu['name'].'<input type="hidden" name="data['.$rowcu['id'].'][id]" value="'.$rowcu['id'].'" class="form-control" autocomplete="off">'.'</td>';
$output .= '<td>'.$rowcu['design'].'</td>';
$output .= '<td>'.$rowcu['salary'].'</td>';    
$output .= '<td>'.$rowcu['absamo'].'</td>'; 
$output .= '<td>'.$rowcu['lateamo'].'</td>'; 
$output .= '<td>'.$rowcu['levamo'].'</td>'; 
$output .= '<td>'.$rowcu['pdayamo'].'</td>'; 
$output .= '<td>'.$rowcu['pamo'].'</td>'; 
$output .= '<td>'.$rowcu['tsalary'].'</td>';
$output .= '<td nowrap="" style="text-align:center;">';
$output .= '<a class="btn btn-flat bg-purple details-invoice" href="#" id="inv_'.$rowcu['id'].'_'.$rowcu['eid'].'"><i class="fa fa-eye cat-child"></i></a>';    
$output .= '<a class="btn btn-flat bg-purple" href="#" onclick="remove_item(\'DL_'.$rowcu['id'].'\')"><i class="fa fa-trash"></i></a>';
$output .= '</td>';				
$output .='</tr>';  
}
}
echo $output;   
exit; 
}