<?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" autocomplete='off' > 
                        <div class="row-form clearfix">
                            
                                <div class="span3">        
                                   <label>Report Date</label>
                                   <input type="text" name="date" class="datetimepicker2" required/>
                               </div>      
                                                                                                    
                            <div class="span2"> 
                            <label>&nbsp;</label>
                            <input type="submit" name="okey" value="Search"/>
                            </div>
                            </div>                       
                   </form>
                   </div>  
                </div>
              <?php 
              if(isset($_REQUEST['okey']))
              {
                 $rpt_date   = date("Y-m-d",strtotime($_POST['date']));
            ?>         
<div class="span12" style='margin-top: 10px;margin-left: 0px;'>                    
        <div class="head clearfix">
            <div class="isw-grid"></div>
            <h1> Today Company Wide Report</h1>                                             
        </div>
        <div class="block-fluid" id="print_div">
        <h4 align="center">Today Work Report</h4>  
        <table id="example" class="table" cellspacing="0" width="100%" border="1">
            <thead>
            <tr>
            <th colspan="8" style="text-align: center;background: #ffd6d6;">RENE DELHI</th> 
            </tr>
            <tr>
                <th>State Name</th>
                <th>Total Member</th>
                <th>Today Working Member</th>
                <th>Persent</th>
                <th>Half Day</th>
                <th>Absent</th>
                <th>Leave</th>
                <th>Missing</th>                         
            </tr>
            </thead>
            <tbody>
            <?php 
            mysql_close();
            mysql_connect("localhost","rudraanshgroup_rene_dl","India@ks");
            mysql_select_db("rudraanshgroup_rene_dl");
            $sqlDL = mysql_query("SELECT * FROM `tbl_emp_records` WHERE `status`='0' group by `state`")or die(mysql_error());                       
            while($sqlDLData = mysql_fetch_array($sqlDL))
            {
                $working_m  = 0;
                $sqlDLState = mysql_query("SELECT * FROM `tbl_emp_records` WHERE `status`='0' and `state`='".$sqlDLData['state']."'")or die(mysql_error());
                
                $sqlDLAT    = mysql_query(" SELECT SUM(IF(`mark` ='1', `mark`, 0)) AS PresentTotal,
                SUM(IF(`mark` ='2', `mark`, 0)) AS OffTotal,
                SUM(IF(`mark` ='3', `mark`, 0)) AS LeaveTotal,
                SUM(IF(`mark` ='4', `mark`, 0)) AS AbsentTotal,
                SUM(IF(`mark` ='5', `mark`, 0)) AS WeeklyTotal,
                SUM(IF(`mark` ='6', `mark`, 0)) AS HalfTotal
                 FROM `tbl_attendence` WHERE `date`='".$rpt_date."' and `state`='".$sqlDLData['state']."' ")or die(mysql_error());
                $sqlDLATData = mysql_fetch_array($sqlDLAT);

                $missing     = mysql_num_rows($sqlDLState) - ($sqlDLATData['PresentTotal']+$sqlDLATData['AbsentTotal']+$sqlDLATData['LeaveTotal']);
                
            ?>
            <tr>
                <td><?php echo $sqlDLData['state'];?></td>
                <td><?php echo mysql_num_rows($sqlDLState);?></td>
                <td style="text-align:center;"><?php echo $sqlDLATData['PresentTotal']+$sqlDLATData['HalfTotal'];?></td>
                <td style="text-align:center;"><?php echo $sqlDLATData['PresentTotal'];?></td>
                <td style="text-align:center;"><?php echo $sqlDLATData['AbsentTotal'];?></td>
                <td style="text-align:center;"><?php echo $sqlDLATData['LeaveTotal'];?></td>
                <td style="text-align:center;"><?php echo $sqlDLATData['HalfTotal'];?></td>
                <td style="text-align:center;"><?php echo $missing;?></td>
            </tr>
            <?php }?>
            </tbody>
            
            <thead>
            <tr>
            <th colspan="8" style="text-align: center;background: #ffd6d6;">RENE JAIPUR</th> 
            </tr>
            <tr>
                <th>State Name</th>
                <th>Total Member</th>
                <th>Today Working Member</th>
                <th>Persent</th>
                <th>Half Day</th>
                <th>Absent</th>
                <th>Leave</th>
                <th>Missing</th>                         
            </tr>
            </thead>
            <tbody>
            <?php 
            mysql_close();
            mysql_connect("localhost","rudraanshgroup_rene_jpr","India@ks");
            mysql_select_db("rudraanshgroup_rene_jpr");
            $sqlDL = mysql_query("SELECT * FROM `tbl_emp_records` WHERE `status`='0' group by `state`")or die(mysql_error());                       
            while($sqlDLData = mysql_fetch_array($sqlDL))
            {
                $working_m  = 0;
                $sqlDLState = mysql_query("SELECT * FROM `tbl_emp_records` WHERE `status`='0' and `state`='".$sqlDLData['state']."'")or die(mysql_error());
                
                $sqlDLAT    = mysql_query(" SELECT SUM(IF(`mark` ='1', `mark`, 0)) AS PresentTotal,
                SUM(IF(`mark` ='2', `mark`, 0)) AS OffTotal,
                SUM(IF(`mark` ='3', `mark`, 0)) AS LeaveTotal,
                SUM(IF(`mark` ='4', `mark`, 0)) AS AbsentTotal,
                SUM(IF(`mark` ='5', `mark`, 0)) AS WeeklyTotal,
                SUM(IF(`mark` ='6', `mark`, 0)) AS HalfTotal
                 FROM `tbl_attendence` WHERE `date`='".$rpt_date."' and `state`='".$sqlDLData['state']."' ")or die(mysql_error());
                $sqlDLATData = mysql_fetch_array($sqlDLAT);

                $missing     = mysql_num_rows($sqlDLState) - ($sqlDLATData['PresentTotal']+$sqlDLATData['AbsentTotal']+$sqlDLATData['LeaveTotal']);
                
            ?>
            <tr>
                <td><?php echo $sqlDLData['state'];?></td>
                <td><?php echo mysql_num_rows($sqlDLState);?></td>
                <td style="text-align:center;"><?php echo $sqlDLATData['PresentTotal']+$sqlDLATData['HalfTotal'];?></td>
                <td style="text-align:center;"><?php echo $sqlDLATData['PresentTotal'];?></td>
                <td style="text-align:center;"><?php echo $sqlDLATData['AbsentTotal'];?></td>
                <td style="text-align:center;"><?php echo $sqlDLATData['LeaveTotal'];?></td>
                <td style="text-align:center;"><?php echo $sqlDLATData['HalfTotal'];?></td>
                <td style="text-align:center;"><?php echo $missing;?></td>
            </tr>
            <?php }
            mysql_close();
            ?>
            </tbody>
            
            <thead>
            <tr>
            <th colspan="8" style="text-align: center;background: #ffd6d6;">RENE OFFSITE</th> 
            </tr>
            <tr>
                <th>State Name</th>
                <th>Total Member</th>
                <th>Today Working Member</th>
                <th>Persent</th>
                <th>Half Day</th>
                <th>Absent</th>
                <th>Leave</th>
                <th>Missing</th>                         
            </tr>
            </thead>
            <tbody>
            <?php 
            mysql_close();
            mysql_connect("localhost","rudraanshgroup_rene_offsite","India@ks");
            mysql_select_db("rudraanshgroup_rene_offsite");
            $sqlDL = mysql_query("SELECT * FROM `tbl_emp_records` WHERE `status`='0' group by `state`")or die(mysql_error());                       
            while($sqlDLData = mysql_fetch_array($sqlDL))
            {
                $working_m  = 0;
                $sqlDLState = mysql_query("SELECT * FROM `tbl_emp_records` WHERE `status`='0' and `state`='".$sqlDLData['state']."'")or die(mysql_error());
                
                $sqlDLAT    = mysql_query(" SELECT SUM(IF(`mark` ='1', `mark`, 0)) AS PresentTotal,
                SUM(IF(`mark` ='2', `mark`, 0)) AS OffTotal,
                SUM(IF(`mark` ='3', `mark`, 0)) AS LeaveTotal,
                SUM(IF(`mark` ='4', `mark`, 0)) AS AbsentTotal,
                SUM(IF(`mark` ='5', `mark`, 0)) AS WeeklyTotal,
                SUM(IF(`mark` ='6', `mark`, 0)) AS HalfTotal
                 FROM `tbl_attendence` WHERE `date`='".$rpt_date."' and `state`='".$sqlDLData['state']."' ")or die(mysql_error());
                $sqlDLATData = mysql_fetch_array($sqlDLAT);

                $missing     = mysql_num_rows($sqlDLState) - ($sqlDLATData['PresentTotal']+$sqlDLATData['AbsentTotal']+$sqlDLATData['LeaveTotal']);
                
            ?>
            <tr>
                <td><?php echo $sqlDLData['state'];?></td>
                <td><?php echo mysql_num_rows($sqlDLState);?></td>
                <td style="text-align:center;"><?php echo $sqlDLATData['PresentTotal']+$sqlDLATData['HalfTotal'];?></td>
                <td style="text-align:center;"><?php echo $sqlDLATData['PresentTotal'];?></td>
                <td style="text-align:center;"><?php echo $sqlDLATData['AbsentTotal'];?></td>
                <td style="text-align:center;"><?php echo $sqlDLATData['LeaveTotal'];?></td>
                <td style="text-align:center;"><?php echo $sqlDLATData['HalfTotal'];?></td>
                <td style="text-align:center;"><?php echo $missing;?></td>
            </tr>
            <?php }
            mysql_close();
            ?>
            </tbody>
            <thead>
            <tr>
            <th colspan="8" style="text-align: center;background: #ffd6d6;">RUDRAANSH</th> 
            </tr>
            <tr>
                <th>State Name</th>
                <th>Total Member</th>
                <th>Today Working Member</th>
                <th>Persent</th>
                <th>Half Day</th>
                <th>Absent</th>
                <th>Leave</th>
                <th>Missing</th>                         
            </tr>
            </thead>
            <tbody>
            <?php 
            mysql_close();
            mysql_connect("localhost","rudraanshgroup_rudransh","India@ks");
            mysql_select_db("rudraanshgroup_rudransh");
            $sqlDL = mysql_query("SELECT * FROM `tbl_emp_records` WHERE `status`='0' group by `state`")or die(mysql_error());                       
            while($sqlDLData = mysql_fetch_array($sqlDL))
            {
                $working_m  = 0;
                $sqlDLState = mysql_query("SELECT * FROM `tbl_emp_records` WHERE `status`='0' and `state`='".$sqlDLData['state']."'")or die(mysql_error());
                
                $sqlDLAT    = mysql_query("SELECT SUM(IF(`mark` ='1', `mark`, 0)) AS PresentTotal,
                SUM(IF(`mark` ='2', `mark`, 0)) AS OffTotal,
                SUM(IF(`mark` ='3', `mark`, 0)) AS LeaveTotal,
                SUM(IF(`mark` ='4', `mark`, 0)) AS AbsentTotal,
                SUM(IF(`mark` ='5', `mark`, 0)) AS WeeklyTotal,
                SUM(IF(`mark` ='6', `mark`, 0)) AS HalfTotal
                 FROM `tbl_attendence` WHERE `date`='".$rpt_date."' and `state`='".$sqlDLData['state']."'")or die(mysql_error());
                $sqlDLATData = mysql_fetch_array($sqlDLAT);

                $missing     = mysql_num_rows($sqlDLState) - ($sqlDLATData['PresentTotal']+($sqlDLATData['AbsentTotal']/4)+($sqlDLATData['LeaveTotal']/3));
                
            ?>
            <tr>
                <td><?php echo $sqlDLData['state'];?></td>
                <td><?php echo mysql_num_rows($sqlDLState);?></td>
                <td style="text-align:center;"><?php echo $sqlDLATData['PresentTotal']+($sqlDLATData['HalfTotal']/6);?></td>
                <td style="text-align:center;"><?php echo $sqlDLATData['PresentTotal'];?></td>
                <td style="text-align:center;"><?php echo $sqlDLATData['HalfTotal']/6;?></td>
                <td style="text-align:center;"><?php echo $sqlDLATData['AbsentTotal']/4;?></td>
                <td style="text-align:center;"><?php echo $sqlDLATData['LeaveTotal']/3;?></td>
                
                <td style="text-align:center;"><?php echo $missing;?></td>
            </tr>
            <?php }
            mysql_close();
            ?>
            </tbody>
            
            <thead>
            <tr>
            <th colspan="8" style="text-align: center;background: #ffd6d6;">REVA JAIPUR</th> 
            </tr>
            <tr>
                <th>State Name</th>
                <th>Total Member</th>
                <th>Today Working Member</th>
                <th>Persent</th>
                <th>Half Day</th>
                <th>Absent</th>
                <th>Leave</th>
                <th>Missing</th>                         
            </tr>
            </thead>
            <tbody>
            <?php 
            mysql_close();
            mysql_connect("localhost","rudraanshgroup_reva_jpr","India@ks");
            mysql_select_db("rudraanshgroup_reva_jpr");
            $sqlDL = mysql_query("SELECT * FROM `tbl_emp_records` WHERE `status`='0' group by `state`")or die(mysql_error());                       
            while($sqlDLData = mysql_fetch_array($sqlDL))
            {
                $working_m  = 0;
                $sqlDLState = mysql_query("SELECT * FROM `tbl_emp_records` WHERE `status`='0' and `state`='".$sqlDLData['state']."'")or die(mysql_error());
                
                $sqlDLAT    = mysql_query(" SELECT SUM(IF(`mark` ='1', `mark`, 0)) AS PresentTotal,
                SUM(IF(`mark` ='2', `mark`, 0)) AS OffTotal,
                SUM(IF(`mark` ='3', `mark`, 0)) AS LeaveTotal,
                SUM(IF(`mark` ='4', `mark`, 0)) AS AbsentTotal,
                SUM(IF(`mark` ='5', `mark`, 0)) AS WeeklyTotal,
                SUM(IF(`mark` ='6', `mark`, 0)) AS HalfTotal
                 FROM `tbl_attendence` WHERE `date`='".$rpt_date."' and `state`='".$sqlDLData['state']."' ")or die(mysql_error());
                $sqlDLATData = mysql_fetch_array($sqlDLAT);

                $missing     = mysql_num_rows($sqlDLState) - ($sqlDLATData['PresentTotal']+$sqlDLATData['AbsentTotal']+$sqlDLATData['LeaveTotal']);
                
            ?>
            <tr>
                <td><?php echo $sqlDLData['state'];?></td>
                <td><?php echo mysql_num_rows($sqlDLState);?></td>
                <td style="text-align:center;"><?php echo $sqlDLATData['PresentTotal']+$sqlDLATData['HalfTotal'];?></td>
                <td style="text-align:center;"><?php echo $sqlDLATData['PresentTotal'];?></td>
                <td style="text-align:center;"><?php echo $sqlDLATData['AbsentTotal'];?></td>
                <td style="text-align:center;"><?php echo $sqlDLATData['LeaveTotal'];?></td>
                <td style="text-align:center;"><?php echo $sqlDLATData['HalfTotal'];?></td>
                <td style="text-align:center;"><?php echo $missing;?></td>
            </tr>
            <?php }
            mysql_close();
            ?>
            </tbody>
        </table>
</div>
</div>
<?php }?>