<?php 
    session_start();
    if(!$_SESSION['id']){
        header('location:index.php');
    }
      require_once '../system/conn.php';
?>
<!doctype html>
<html lang="en" class="semi-dark">
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
	<link href="assets/css/pace.min.css" rel="stylesheet" />
	<script src="assets/js/pace.min.js"></script>
    <link href="assets/plugins/perfect-scrollbar/css/perfect-scrollbar.css" rel="stylesheet" />
    <link href="assets/plugins/simplebar/css/simplebar.css" rel="stylesheet" />
    <link href="assets/plugins/metismenu/css/metisMenu.min.css" rel="stylesheet" />
    <link href="assets/plugins/datatable/css/dataTables.bootstrap5.min.css" rel="stylesheet" />
    <link href="assets/css/bootstrap.min.css" rel="stylesheet">
    <link href="assets/css/bootstrap-extended.css" rel="stylesheet">
    <link href="assets/css/style.css" rel="stylesheet">
    <link href="assets/css/icons.css" rel="stylesheet">
	<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap-icons@1.7.2/font/bootstrap-icons.css">
    <link href="https://fonts.googleapis.com/css2?family=Roboto:wght@400;500&display=swap" rel="stylesheet">
    <link href="assets/css/dark-theme.css" rel="stylesheet" />
    <link href="assets/css/semi-dark.css" rel="stylesheet" />
    <link href="assets/css/header-colors.css" rel="stylesheet" />
    <title>Employee Wise Report</title>
  </head>
  <body>
    <div class="wrapper">
    <?php include'system/header.php'?>
	    <div class="page-content-wrapper">
         <div class="page-content">
          <?php
          if(isset($_POST['submit'])){
            extract($_POST);
            $fromyear=$_POST['fromyear'];
            $frommonth=$_POST['frommonth'];
            $toyear=$_POST['toyear'];
            $tomonth=$_POST['tomonth'];
            $ename=$_POST['ename'];
          ?>
          <div class="page-breadcrumb d-none d-sm-flex align-items-center mb-3">
            <div class="breadcrumb-title pe-3 text-center">
                Employee Wise Report (<?php echo $fromyear."-".$frommonth." to ".$toyear."-".$tomonth; ?>)<br>
            </div>
            <div class="ms-auto">
                <a href="excel-employee-report.php?fy=<?php echo $fromyear;?>&fm=<?php echo $frommonth;?>&ty=<?php echo $toyear;?>&tm=<?php echo $tomonth;?>&enm=<?php echo $ename;?>"><button type="button" class="btn btn-primary px-5">Excel Download</button></a>
            </div>
          </div>
		  <hr/>
		  <div class="text-center" style="margin-bottom: 20px;">
			<?php
              $stmt1 = $conn->prepare(" SELECT * FROM `emp_details` where e_id='".$ename."' limit 1");
              $stmt1->execute();
              $sql1 = $stmt1->fetchAll();
              foreach($sql1 as $row1) 
            { ?>
			    <h3>UTKAL TEAM CARE PVT LTD</h3><br>
                Plot No-777 Saheed Nagar, "Virayatan"<br>
                Bhubaneswar-751007<br>
                PAN NO- <?php echo $row1['pan_card']; ?> <br>
                GSTNO- 21AABCU5974F1ZE<br>
                CIN: U45202OR2013PTC016985<br>
                UAN: <?php echo $row1['uan_no']; ?><br>
                
                <b><?php echo $row1['e_name']; ?> (<?php echo $row1['designation']; ?>)/<?php echo $row1['e_id']; ?></b>
            <?php } ?>
            <br>
            Ledger Account<br>
          </div>
		  <div class="card">
			<div class="card-body">
			  <div class="table-responsive">
				<table id="example2" class="table table-striped table-bordered">
					<thead>
						<tr>
						    <th>SR No</th>
                            <th>Month - Year</th>
                            <th>Paid Days</th>
                            <th>Branch</th>
                            <th>Basic</th>
                            <th>HRA</th>
                            <th>Conv</th>
                            <th>Med</th>
                            <th>Gross</th>
                            <th>PF</th>
                            <th>Esic</th>
                            <th>Staff Adv.</th>
                            <th>PT</th>
                            <th>Tot Ded.</th>
                            <th>Net Pay</th>
                            <th>OT</th>
                            <th>Adv Taken</th>
                            <th>Tot</th>
                            
                            <!--<th>Month - Year</th>
                            <th>Particulars</th>
                            <th>Vch Type</th>
                            <th>Debit</th>
                            <th>Credit</th>-->
						</tr>
					</thead>
					<tbody>
					  <?php
                        $cnt = 1;
                        $stmt1 = $conn->prepare(" SELECT * FROM `prv_emp_salary` WHERE `month` BETWEEN '".$frommonth."' and '".$tomonth."' AND `year` BETWEEN '".$fromyear."' and '".$toyear."' and `emp_id`='".$ename."'  and `block`='' and `cheque`='' and `ac_no`!='' ");
                        //print_r($stmt1);exit;
                        $stmt1->execute();
                        $sql = $stmt1->fetchAll(); 
                        foreach($sql as $row) 
                      { ?>
                        <?/*php ?><tr>
                            <td rowspan=3><?php echo $row['month']."-".$row['year'];?></td>
                            <td>Salary(Basic)</td>
                            <td>Journal</td>
                            <td><?php echo $row['basic_pay'];?></td>
                            <td rowspan=3><?php echo $row['net_pay'];?></td>
                        </tr>
                        <tr>
                            <td>Employee's Contribution(P.F)</td>
                            <td>Journal</td>
                            <td><?php echo $row['pf'];?></td>
                        </tr>
                        <tr>
                            <td>Employee's Contribution(E.S.I.C)</td>
                            <td>Journal</td>
                            <td><?php echo $row['esic'];?></td>
                        </tr>
                        <?php */ ?>
                        <tr>
                            <td><?php echo $cnt;?></td>
                            <td><?php echo $row['month']."-".$row['year'];?></td>
                            <td><?php echo $row['attendance'];?></td>
                            <td>
                              <?php
                                $stmt = $conn->prepare(" SELECT name FROM `branch` where id='".$row['branch']."' ");
                                $stmt->execute();
                                $sql2 = $stmt->fetchAll(); 
                                foreach($sql2 as $row2) 
                                { 
                                    echo $row2['name']."<br>";  
                                }
                              ?>
                            </td>
                            <td><?php echo $row['basic_pay'];?></td>
                            <td><?php echo $row['hra'];?></td>
                            <td><?php echo $row['convyance'];?></td>
                            <td><?php echo $row['medical'];?></td>
                            <td><?php echo $row['gross_pay'];?></td>
                            <td><?php echo $row['pf'];?></td>
                            <td><?php echo $row['esic'];?></td>
                            <td><?php echo $row['advance'];?></td>
                            <td><?php echo $row['tax'];?></td>
                            <td><?php echo $row['deduction'];?></td>
                            <td><?php echo $net_pay=$row['net_pay'];?></td> 
                            <td>
                                <?php
                                  $sth1 = $conn->prepare("SELECT * FROM `ot_details` WHERE `month`='".$row['month']."' and `year`='".$row['year']."' and `emp_id`= '".$ename."' ");
                                  //print_r($sth);exit;
                                  $sth1->execute();
                                  $rowl1 = $sth1->fetch(PDO::FETCH_ASSOC); 
                                    if($rowl1['amount']){
                                     echo $ot=$rowl1['amount'];
                                    } else {
                                     echo $ot=0;  
                                    }
                                ?>
                            </td> 
                            <td>
                                <?php
                                  $fromdate = $fromyear.'-'.$frommonth.'-01';
                                  $todate = $toyear.'-'.$tomonth.'-01';
                                  
                                  $todate1 = $row['year']."-".$row['month']."-01";
                                  
                                  $sth = $conn->prepare("SELECT * FROM `advance` WHERE status= 1 AND `date` BETWEEN '".$fromdate."' and '".$todate."' and `e_id`= '".$ename."' ");
                                  //print_r($sth);exit;
                                  $sth->execute();
                                  $rowl = $sth->fetch(PDO::FETCH_ASSOC); 
                                  if($todate1==$rowl['date']){
                                     echo $adv_taken=$rowl['amount'];
                                  }else{
                                     echo $adv_taken=0;
                                  }
                                ?>
                            </td> 
                            <td><?php echo $tot=$net_pay+$ot+$adv_taken;?></td> 
                        </tr>
                      <?php $cnt=$cnt+1; }?>
					</tbody>
					<tfoot>
						<tr>
						    <th>SR No</th>
                            <th>Month - Year</th>
                            <th>Paid Days</th>
                            <th>Branch</th>
                            <th>Basic</th>
                            <th>HRA</th>
                            <th>Conv</th>
                            <th>Med</th>
                            <th>Gross</th>
                            <th>PF</th>
                            <th>Esic</th>
                            <th>Staff Adv.</th>
                            <th>PT</th>
                            <th>Tot Ded.</th>
                            <th>Net Pay</th>
                            <th>OT</th>
                            <th>Adv Taken</th>
                            <th>Tot</th>
						</tr>
					</tfoot>
				</table>
			  </div>
			</div>
		  </div>
          <?php } ?>

        </div>
        <!-- end page content-->
      </div>
<?php include'system/footer.php'?>