<?php
date_default_timezone_set('Asia/Calcutta');
$out_date		= date("Y-m-d");
if(isset($_POST['okey']) && !empty($_POST['okey']))
{
	
$_SESSION['att_month']          = $_POST['month'];
$_SESSION['att_year']           = $_POST['year'];
$_SESSION['att_emp_name']       = $_POST['emp_name'];	
$_SESSION['att_state']     		= $_POST['state'];	
}
function getSundays($y,$m)
{ 
	$date = "$y-$m-01";
	$first_day = date('N',strtotime($date));
	$first_day = 7 - $first_day + 1;
	$last_day =  date('t',strtotime($date));
	$days = array();
	for($i=$first_day; $i<=$last_day; $i=$i+7 )
	{
		$days[] = $i;
	}
	return  $days; 
}

function attend_type($id)
{
	$sql_st	= mysql_query("SELECT `symbol` FROM `tbl_attend_type` where `id`='$id' ")or die(mysql_error());
	$row_st	= mysql_fetch_array($sql_st);
	return $row_st['symbol'];
}
?>
<script src="https://cdn.datatables.net/fixedcolumns/3.2.4/js/dataTables.fixedColumns.min.js"></script>

  <style>
  .table1 td{vertical-align: middle;text-align: center;}
  .color_1{background:#0C0 !important;color:#000 !important;}
  .color_2{background:#F00 !important;color:#fff !important;}
  .color_3{background:#33F !important;color:#FFF !important;}
  .color_4{background:#F39 !important;color:#FFF !important;}
  .color_5{background:#306 !important;color:#FFF !important;}
  .color_6{background:#90F !important;color:#FFF !important;}
  .color_7{background:#FF6 !important;color:#000 !important;}
  </style>
         
                <div class="span12" style="margin-left:0px;">
                    <div class="head clearfix">
                        <div class="isw-ok"></div>
                        <h1>Staff Salary Report</h1>                        
                    </div>                    
                    <div class="block-fluid">  
   					   <form  method="POST" enctype="multipart/form-data" action="" > 
						<div class="row-form clearfix">
                            <div class="span1">        
                                   <label>Month</label>		
                              	    <select name="month">
                                        <option value="">Month</option>
                                        <option value="01" <?php if(isset($_SESSION['att_month']) && $_SESSION['att_month']=='01') { echo "selected";}  ?>>Jan</option>
                                        <option value="02" <?php if(isset($_SESSION['att_month']) && $_SESSION['att_month']=='02') { echo "selected";}  ?>>Feb</option>
                                        <option value="03" <?php if(isset($_SESSION['att_month']) && $_SESSION['att_month']=='03') { echo "selected";}  ?>>Mar</option>
                                        <option value="04" <?php if(isset($_SESSION['att_month']) && $_SESSION['att_month']=='04') { echo "selected";}  ?>>Apr</option>
                                        <option value="05" <?php if(isset($_SESSION['att_month']) && $_SESSION['att_month']=='05') { echo "selected";}  ?>>May</option>
                                        <option value="06" <?php if(isset($_SESSION['att_month']) && $_SESSION['att_month']=='06') { echo "selected";}  ?>>Jun</option>
                                        <option value="07" <?php if(isset($_SESSION['att_month']) && $_SESSION['att_month']=='07') { echo "selected";}  ?>>Jul</option>
                                        <option value="08" <?php if(isset($_SESSION['att_month']) && $_SESSION['att_month']=='08') { echo "selected";}  ?>>Aug</option>
                                        <option value="09" <?php if(isset($_SESSION['att_month']) && $_SESSION['att_month']=='09') { echo "selected";}  ?>>Sep</option>
                                        <option value="10" <?php if(isset($_SESSION['att_month']) && $_SESSION['att_month']=='10') { echo "selected";}  ?>>Oct</option>
                                        <option value="11" <?php if(isset($_SESSION['att_month']) && $_SESSION['att_month']=='11') { echo "selected";}  ?>>Nov</option>
                                        <option value="12" <?php if(isset($_SESSION['att_month']) && $_SESSION['att_month']=='12') { echo "selected";}  ?>>Dec</option>
                                   </select>
                               </div>
                            <div class="span1">        
                                   <label>Year</label>		
                              	 <select name="year">
                                        <option value="">Year</option>
                                        <?php 
										for($i=2010;$i<2056;$i++){?>
                                        
                                        <option value="<?php echo $i;?>" <?php if(isset($_SESSION['att_year']) && $_SESSION['att_year']==$i) { echo "selected";}  ?> ><?php echo $i;?></option>
                                        <?php } ?>
                                   </select>
                               </div>      
                            <div class="span3">
                        <label>State</label>
                        <select name="state" onchange="get_staff_name(this.value);" required>
                        <?php
                        if($_SESSION['userid']==4)
                        {
                            $st		= array();
                            $sql_st	= mysql_query("SELECT distinct `state` FROM `tbl_emp_records` ORDER BY `state` ASC ")or die(mysql_error());
                            while($row_st= mysql_fetch_array($sql_st))
                            {
                                array_push($st,$row_st['state']);
                            }
                        }
                        else
                        {
                            $st	  = explode('&',$_SESSION['post']);
                        }
                        foreach($st as $key)
                        {
                            $type = '';
                            if(isset($_SESSION['att_state'])&&($_SESSION['att_state']==$key)){ $type ='selected';}
                            echo "<option value='$key' $type>$key</option>";
                        }
                        ?>
                        </select>
                    </div>                                                                           
                            <div class="span3">        
                            <label>Employee Name</label>
                               <select name="emp_name" id="emp_name" required >
                               <option value='ALL'>ALL</option>
                               <?php
							   if(isset($_SESSION['att_state']))
								{
									
									$sql_st	= mysql_query("SELECT `emp_name`,`id` FROM `tbl_emp_records` where `state`='".$_SESSION['att_state']."' and `status`='0' ORDER BY `emp_name` ASC ")or die(mysql_error());
									while($row_st= mysql_fetch_array($sql_st))
									{
									?>
                                    <option value="<?php echo $row_st['id'];?>" <?php if($row_st['id']==$_SESSION['att_emp_name']){echo "selected";}?>><?php echo $row_st['emp_name'];?></option>
                                    <?php	
									}
								}
								?>
	                           </select>
                           </div>
                            <div class="span2"> 
                            <label>&nbsp;</label>
                     		<input type="submit" name="okey" value="Search"/>
                            </div>
                            </div>                       
                   </form>
                   </div>  
                </div>
              <?php 
			  if(isset($_REQUEST['okey']))
			  {
				  unset($_SESSION['att_emp_name']);unset($_SESSION['att_year']);unset($_SESSION['att_month']);
				  /// days calculate in a month
				     $month		= $_SESSION['att_month']	= $_REQUEST['month'];
				     $year		= $_SESSION['att_year']		= $_REQUEST['year'];
				  	 $d		 	= cal_days_in_month(CAL_GREGORIAN,$month,$year);
					 $sun_arr   = getSundays($year,$month);
					 $sunday_dts_arr= array();
					 foreach($sun_arr as $key)
					 {
						array_push($sunday_dts_arr,$year."-".$month."-".$key);	 
					 }
					 $sunday_dts= implode("','",$sunday_dts_arr);
					 $sundays	= $sundays_1    = count($sun_arr);
					 $from      = date("Y-m-d",strtotime("01-$month-$year"));
					 $to	   	= date("Y-m-d",strtotime("$d-$month-$year"));
 				  	 $_SESSION['att_emp_name']  =  $emp_name 	= $_REQUEST['emp_name'];
					 $_SESSION['att_state']     =  $state	 	= $_REQUEST['state'];
			?>         
<div class="span12" style='margin-top: 10px;margin-left: 0px;'>                    
        <div class="head clearfix">
            <div class="isw-grid"></div>
            <h1> Staff Salary Report 
            &nbsp;&nbsp;&nbsp;&nbsp;<a href="pages/excel/Examples/cal_salary_excel_new.php?d=<?php echo $d;?>" target="_blank" style="color:#fff;" title="Download In Excel">Download in Excel</a></h1>                                             
        </div>
        <div class="block-fluid" id="print_div">
        <h4 align="center">RUNDRANSH CONSGINORS SALARY REPORT</h4>  
        <p style="font-size:13px;"><span style="float:left;"><strong>Month-Year: <?php echo $month ; ?>-<?php echo $year ; ?></strong></span></p>
        <table id="example" class="table" cellspacing="0" width="100%" border="1">
            <thead>
            <tr>
                <th width="15%">State</th>
                <th width="15%">Staff Name</th>
                <th width="15%">ASM</th>
                <th width="5%">Days in Month</th>
                <th width="5%">Sundays</th>
                <th width="5%">Holidays</th>
                <th width="5%">Working Days Available</th>
                <th width="5%">Leave</th>
                <th width="5%">Balance Working Days</th>
                <th width="5%">Meeting / Training / Conf</th>
                <th width="5%">Sundays Worked</th> 
                <th width="5%">Holidays Worked</th> 
                <th width="5%">Days Retailed</th>
                <th width="5%">Basic Salary </th>
                <th width="5%">DA</th>
                <th width="5%">TA</th> 
                <th width="5%">Phone</th> 
                <th width="5%">Travel Exp.</th> 
                <th width="5%">Salary</th> 
                <th width="5%">DA</th> 
                <th width="5%">TA</th> 
                <th width="5%">Phone</th> 
                <th width="5%">Travel Exp.</th> 
                <th width="5%">Total Payable Amount</th>  
                <th width="5%">P.F. Basic</th>  
                <th width="5%">P.F. Salary</th>     
                <th width="5%">P.F. Amt</th>          
                <th width="5%">ESI Amt</th>
                <th width="5%">(P.F +ESI) Amt</th>  
                <th width="5%">Final Salary</th>     
            </tr>
            </thead>
            <tbody>
            <?php
			$pf_qry			= mysql_query("SELECT * FROM `tbl_rates` where `id`='1' ")or die(mysql_error());
			$pf_data		= mysql_fetch_array($pf_qry);
			$pf_rate		= $pf_data['pf_rate'];
			$esi_rate		= $pf_data['esi_rate'];
            if($emp_name=='ALL') 
            {
              $sql			= mysql_query(" SELECT distinct `id`,`emp_name`,`salary_rate`,`da_rate`,`phone_rate`,`ta_rate`,`travel_exp`,`state`,`asm` FROM `tbl_emp_records` WHERE `status`='0' and `state`='$state' order by `emp_name` ASC")or die(mysql_error());
            }
            else
            {
              $sql			= mysql_query(" SELECT distinct `id`,`emp_name`,`salary_rate`,`da_rate`,`phone_rate`,`ta_rate` ,`travel_exp`,`state`,`asm` FROM `tbl_emp_records` WHERE `status`='0' and `state`='$state' and `id`='$emp_name' ")or die(mysql_error());
            }
            while($row			= mysql_fetch_array($sql))
            {
				$off_in_month   	= 0;$lea_in_month = 0;$wor_in_month = 0;$per_in_month	= 0;$b_wr_in_month = 0;	$su_w_in_month = 0;$ho_w_in_month	= 0;$days_retailed	= 0;$basic_sal		= 0;$da_sal			= 0;$phone_sal		= 0;$ta_sal			= 0.00;$misc_sal		= 0.0;$salary	= 0;$da_final_sal = 0;$ta_final_sal = 0.00;$ph_final_sal = 0;$misc_final_sal = 0;$final_salary = 0;$pf_salary = 0;$pf_amt = 0;$esi_amt = 0;$pf_esi_amt = 0;$last_salary = 0;
				$emp_id				= $row['id'];
				///Seprate Calculate
				/*$sep_qry			= mysql_query(" select max(`date`) from `tbl_attendence` where `emp_id`='$emp_id' and `state`='$state' and `mark`='7' and `date` between '$from' and '$to' ")or die(mysql_error());
				$sep_data   		= mysql_fetch_array($sep_qry);
				if(isset($sep_data['max(`date`)']))
				{
					$start 		= new DateTime($from);
					$end 		= new DateTime($sep_data['max(`date`)']);
					$days 		= $start->diff($end, true)->days;					
					$sundays_1 	= intval($days / 7) + ($start->format('N') + $days % 7 >= 7);
				}
				///Weekly Off Calculate
				$w_off_qry		= mysql_query(" select count(*) from `tbl_attendence` where `emp_id`='$emp_id' and `state`='$state' and `mark`='5' and `date` between '$from' and '$to' ")or die(mysql_error());
				$w_off_data   	= mysql_fetch_array($w_off_qry);
				$w_off_in_month	= $w_off_data['count(*)'];*/
				///Holidays Calculate
				$off_qry		= mysql_query(" select count(*) from `tbl_attendence` where `emp_id`='$emp_id' and `state`='$state' and `mark`='2' and `date` between '$from' and '$to' ")or die(mysql_error());
				$off_data   	= mysql_fetch_array($off_qry);
				$off_in_month	= $off_data['count(*)'];
				///working days
				$wor_in_month	= ($d-$sundays-$off_in_month);
				///Leave Calculate
				$lea_qry		= mysql_query(" select count(*) from `tbl_attendence` where `emp_id`='$emp_id' and `state`='$state' and `mark` IN('3','4') and `date` between '$from' and '$to' ")or die(mysql_error());
				$lea_data   	= mysql_fetch_array($lea_qry);
				$lea_in_month	= $lea_data['count(*)'];
				///Persent Calculate
				$per_qry		= mysql_query(" select count(*) from `tbl_attendence` where `emp_id`='$emp_id' and `state`='$state' and `mark`='1' and `date` between '$from' and '$to' and `date` NOT IN ('$sunday_dts')")or die(mysql_error());
				$per_data   	= mysql_fetch_array($per_qry);
				$per_in_month	= $per_data['count(*)'];
				///Sunday Worked Calculate
				$sun_w_qry		= mysql_query(" select count(*) from `tbl_attendence` where `emp_id`='$emp_id' and `state`='$state' and `mark`='1' and `date` IN ('$sunday_dts') ")or die(mysql_error());
				$sun_w_data		= mysql_fetch_array($sun_w_qry);
				$su_w_in_month  = $sun_w_data['count(*)'];
				//// Balance Workin Days in month
				$b_wr_in_month	= $per_in_month;// - $lea_in_month);
				/////Holidays Working Days
				///Days Retailed
				$extra_dy		= ($lea_in_month + $sundays)-$sundays;				
				$days_retailed	= ($d-$extra_dy)+$su_w_in_month;
				///basic salary
				$basic_sal		= $row['salary_rate'];
				$da_sal			= $row['da_rate'];
				$phone_sal		= $row['phone_rate'];
				$ta_sal			= $row['ta_rate'];
				$misc_sal		= $row['travel_exp'];
				/////Main Calculations
				$a              = ($b_wr_in_month+$su_w_in_month);
				$salary			= ($basic_sal/$d) * $days_retailed;
				if($salary>$basic_sal)
				{
					$salary     = $basic_sal;
				}
				$da_final_sal	= $a*$da_sal;
				if($da_final_sal>2500)
				{
					$da_final_sal = 2500;	
				}
				$ph_final_sal	= ($phone_sal/$d)*$a;
				if($ph_final_sal>500)
				{
					$ph_final_sal = 500;	
				}
				$ta_final_sal	= ($ta_sal)*$a;
				if($ta_final_sal>2000)
				{
					$ta_final_sal = 2000;	
				}				
				$misc_final_sal	= ($misc_sal);
				$final_salary   = ($salary+$da_final_sal+$ta_final_sal+$ph_final_sal+$misc_final_sal);
				///PF ESI CALCULATIONS
				$pf_salary      = (5500/$d) * $days_retailed;
				$pf_amt         = ($pf_salary*$pf_rate)/100;
				$esi_amt		= ($final_salary*$esi_rate)/100;
				$pf_esi_amt		= $pf_amt + $esi_amt ;
				$last_salary	= $final_salary - $pf_esi_amt ;
			?>
            <tr>
            	<td><?php echo $row['state'];?></td>
                <td><?php echo $row['emp_name'];?></td>
                <td><?php echo $row['asm'];?></td>
                <td style="text-align:center;"><?php echo $d;?></td>
                <td style="text-align:center;"><?php echo $sundays;?></td>
                <td style="text-align:center;"><?php echo $off_in_month;?></td>
                <td style="text-align:center;"><?php echo $wor_in_month;?></td>
                <td style="text-align:center;"><?php echo $lea_in_month;?></td>
                <td style="text-align:center;"><?php echo $b_wr_in_month;?></td>
                <td style="text-align:center;">0</td>
                <td style="text-align:center;"><?php echo $su_w_in_month;?></td>
                <td style="text-align:center;"><?php echo $ho_w_in_month;?></td>
                <td style="text-align:center;"><?php echo $days_retailed;?></td>
                <td style="text-align:center;"><?php echo round($basic_sal);?></td>
                <td style="text-align:center;"><?php echo round($da_sal);?></td>
                <td style="text-align:center;"><?php echo round($ta_sal);?></td>
                <td style="text-align:center;"><?php echo round($phone_sal);?></td>
                <td style="text-align:center;"><?php echo round($misc_sal);?></td>
                <td style="text-align:center;"><?php echo round($salary);?></td>
                <td style="text-align:center;"><?php echo round($da_final_sal);?></td>
                <td style="text-align:center;"><?php echo round($ta_final_sal);?></td>
                <td style="text-align:center;"><?php echo round($ph_final_sal);?></td>
                <td style="text-align:center;"><?php echo round($misc_final_sal);?></td>
                <td style="text-align:center;"><?php echo round($final_salary);?></td>
                <td style="text-align:center;"><?php echo round(5500.00);?></td>
                <td style="text-align:center;"><?php echo round($pf_salary);?></td>
                <td style="text-align:center;"><?php echo round($pf_amt);?></td>
                <td style="text-align:center;"><?php echo round($esi_amt);?></td>
                <td style="text-align:center;"><?php echo round($pf_esi_amt);?></td>
                <td style="text-align:center;"><?php echo round($last_salary);?></td>
            </tr>
            <?php	
			}
            ?>
            </tbody>
        </table>
</div>
</div>
<?php }?>
<script>
function get_staff_name(state)
{
	if (state == '') {
		alert("Please Select State !!!");
		return;
	} else {
		var xmlhttp = new XMLHttpRequest();
		xmlhttp.onreadystatechange = function() {
			if (xmlhttp.readyState == 4 && xmlhttp.status == 200) {
			document.getElementById("emp_name").innerHTML=xmlhttp.responseText;
				//alert(xmlhttp.responseText);
			}
		}
		xmlhttp.open("GET","ajax/get_staff_name.php?state="+state,true);
		xmlhttp.send();
	}
}

$(document).ready(function() {
    var table = $('#example').DataTable( {
        scrollY:        "500px",
        scrollX:        true,
        scrollCollapse: true,
        paging:         false,
        fixedColumns:   {
            leftColumns: 2
        }
    } );
} );

function print_div(print_div)
{
var newstr = document.getElementById("print_div").innerHTML;
var oldstr = document.body.innerHTML;
document.body.innerHTML = newstr;
window.print();
document.body.innerHTML = oldstr;
return false;
}
function popup(emp_id,M,Y)
{
 var url	= 'pages/attendece/popup.php?emp_id='+emp_id+"&M="+M+"&Y="+Y;	
 var width  = 950;
 var height = 600;
 var left   = (screen.width  - width)/2;
 var top    = (screen.height - height)/2;
 var params = 'width='+width+', height='+height;
 params += ', top='+top+', left='+left;
 params += ', directories=no';
 params += ', location=no';
 params += ', menubar=no';
 params += ', resizable=no';
 params += ', scrollbars=yes';
 params += ', status=no';
 params += ', toolbar=no';
 newwin22=window.open(url,'windowname22', params);
 if (window.focus) {newwin22.focus()}
}
</script>