<?php
session_start();
$servername = "localhost";
$servername = "3.7.255.145";
$username = "tarun";
$password = "Tarun!123";
$dbname = "vowsls";
$connect = new mysqli($servername, $username, $password, $dbname);



$dat1='2023-02-26';
$dat2='2023-03-25';
$branch='VTP';


$sql="select eb_id,eb_no,worker_name,attdate,replace(GROUP_CONCAT(atttype),',','') atttypes from (
select da.eb_id,wm.eb_no,wm.worker_name , attendance_date attdate,attendance_type atttype 
from daily_attendance da 
join worker_master wm on da.eb_id =wm.eb_id 
join branch_master bm on wm.branch_id =bm.branch_id 
where attendance_date between '$dat1' and '$dat2'
and da.is_active =1 and bm.branch_name like '%$branch%'
union ALL 
select lt.eb_id ,wm.eb_no,wm.worker_name ,ltd.leave_date attdate,'W' atttype  from leave_tran_details ltd
join leave_transactions lt on ltd.ltran_id =lt.leave_transaction_id 
join worker_master wm on lt.eb_id =wm.eb_id 
join branch_master bm on wm.branch_id =bm.branch_id 
where ltd.leave_date between '$dat1' and '$dat2' and bm.branch_name like '%$branch%'
union ALL 
select thht.eb_id ,wm.eb_no,wm.worker_name , hm.holiday_date attdate, 'H' atttype from tbl_hrms_holiday_transactions thht 
join holiday_master hm on thht.holiday_id =hm.id 
join worker_master wm on thht.eb_id =wm.eb_id 
join branch_master bm on wm.branch_id =bm.branch_id 
where hm.holiday_date between '$dat1' and '$dat2' and bm.branch_name like '%$branch%'
) g group by eb_id,eb_no,worker_name,attdate order by eb_id,attdate
 ";
 
 
 
$sql=	"select
		eb_id,
		eb_no,
		worker_name,
		attdate,
		replace(GROUP_CONCAT(atttype), ',', '') atttypes,
 	   sum(othrs) othrs
		from
		(
		select
			da.eb_id,
			wm.eb_no,
			wm.worker_name ,
			attendance_date attdate,
			'P' atttype,0 othrs
		from
			daily_attendance da
		join worker_master wm on
			da.eb_id = wm.eb_id
		join branch_master bm on
			wm.branch_id = bm.branch_id
		where
			attendance_date between '$dat1' and '$dat2'
			and da.is_active = 1 and da.attendance_type='R'
			and bm.branch_name like '%$branch%'
	union ALL
		select
			da.eb_id,
			wm.eb_no,
			wm.worker_name ,
			attendance_date attdate,
			'' atttype,da.working_hours othrs
		from
			daily_attendance da
		join worker_master wm on
			da.eb_id = wm.eb_id
		join branch_master bm on
			wm.branch_id = bm.branch_id
		where
			attendance_date between '$dat1' and '$dat2'
			and da.is_active = 1 and da.attendance_type='O'
			and bm.branch_name like '%$branch%'
	union ALL
		select
			lt.eb_id ,
			wm.eb_no,
			wm.worker_name ,
			ltd.leave_date attdate,
			'W' atttype,0 othrs
		from
			leave_tran_details ltd
		join leave_transactions lt on
			ltd.ltran_id = lt.leave_transaction_id
		join worker_master wm on
			lt.eb_id = wm.eb_id
		join branch_master bm on
			wm.branch_id = bm.branch_id
		where
			ltd.leave_date between '$dat1' and '$dat2'
			and bm.branch_name like '%$branch%'
	union ALL
		select
			thht.eb_id ,
			wm.eb_no,
			wm.worker_name ,
			hm.holiday_date attdate,
			'H' atttype,0 othrs
		from
			tbl_hrms_holiday_transactions thht
		join holiday_master hm on
			thht.holiday_id = hm.id
		join worker_master wm on
			thht.eb_id = wm.eb_id
		join branch_master bm on
			wm.branch_id = bm.branch_id
		where
			hm.holiday_date between '$dat1' and '$dat2'
			and bm.branch_name like '%$branch%' ) g
	group by
		eb_id,
		eb_no,
		worker_name,
		attdate order by eb_no,attdate";
 

$date1=date("Y-m-d", strtotime($dat1) );
$date2=date("Y-m-d", strtotime($dat2) );


$dt1=date_create($date1);
$dt2=date_create($date2);
$diff=date_diff($dt1,$dt2);
$dfm=$diff->format("%a")+1;

echo $dfm;

$date3=$dt1;
$date4=$dt2;
$date5=$dt1;


//echo $diff;
//echo $diff->format("%R%a days");
//$df1=$diff->format("%a");
 // $active_sheet->setCellValue('B2', $df1);

$x=1;

$query="select eb_no,worker_name,COUNT(IF(  instr(atttypes,'P')>0    ,1, NULL)) 'Present',
       COUNT(IF(instr(atttypes,'W')>0,1, NULL)) 'WO',
       COUNT(IF(instr(atttypes,'H')>0,1, NULL)) 'H',
       count(*) 'Total',$dfm-count(*) 'Absent'
";

$query="select eb_no,worker_name,";
while($date3 <= $date4) {
	
$diff=date_diff($date3,$date4);
$df=$diff->format("%a");

	$string = $date3->format('Y-m-d');
	$day=substr($string,8,2);
	$month=substr($string,5,2);
	$dm=$day.'/'.$month;

$query=$query."MAX(CASE WHEN attdate = '$string' THEN atttypes ELSE 'A' END) AS '$dm',";


//echo $dm;



$date3=date_add($date3,date_interval_create_from_date_string("1 days"));


//echo $sql;

 
}

$query=rtrim($query)."
	COUNT(IF( instr(atttypes, 'R')>0 , 1, NULL)) 'Present',
	COUNT(IF(instr(atttypes, 'W')>0, 1, NULL)) 'WO',
	COUNT(IF(instr(atttypes, 'H')>0, 1, NULL)) 'H',
	count(*) 'Total',".
	$dfm."-count(*) 'Absent',
    sum(othrs) othrs";

$query=rtrim($query, ", ");
$query=$query." from ( " .$sql .") h group by eb_no,worker_name";

echo $query;