<?php 
  session_start();
  if(empty($_SESSION['branch']) || $_SESSION['branch'] == ''){
    header("Location: index.php");
    die();
  }
  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'])){
            $fromyear  = $_POST['fromyear'];
            $frommonth = str_pad($_POST['frommonth'], 2, '0', STR_PAD_LEFT);
            $toyear    = $_POST['toyear'];
            $tomonth   = str_pad($_POST['tomonth'], 2, '0', STR_PAD_LEFT);
            $ename     = $_POST['ename'];
            $branch    = $_SESSION['branch'];

            // Comparison Period (e.g. 202405)
            $startPeriod = $fromyear . $frommonth; 
            $endPeriod   = $toyear . $tomonth;
          ?>

          <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 "$frommonth-$fromyear to $tomonth-$toyear"; ?>)<br>
            </div>
            <div class="ms-auto">
                <a href="excel-employee-report.php?fy=<?=$fromyear?>&fm=<?=$frommonth?>&ty=<?=$toyear?>&tm=<?=$tomonth?>&enm=<?=$ename?>">
                    <button type="button" class="btn btn-primary px-5">Excel Download</button>
                </a>
            </div>
          </div>
          <hr/>

                <div class="text-center" style="margin-bottom: 25px;">
            <?php
              $stmtEmp = $conn->prepare("SELECT * FROM `emp_details` WHERE e_id = ? AND branch = ? LIMIT 1");
              $stmtEmp->execute([$ename, $branch]);
              $empDetails = $stmtEmp->fetch(PDO::FETCH_ASSOC);

              if($empDetails) { 
                  $stmtBr = $conn->prepare("SELECT name FROM `branch` WHERE id = ?");
                  $stmtBr->execute([$empDetails['branch']]);
                  $brName = $stmtBr->fetchColumn();
            ?>
                <h2 style="color: #0056b3; font-weight: 800; letter-spacing: 0.5px; margin-bottom: 5px;">UTKAL TEAM CARE PVT LTD</h2>
                
                <div style="color: #555; font-weight: 500;">
                    Plot No-777 Saheed Nagar, "Virayatan", Bhubaneswar-751007
                </div>
                
                <div style="font-size: 13px; margin-top: 5px; color: #444;">
                    <strong>GST:</strong> 21AABCU5974F1ZE &nbsp;|&nbsp; 
                    <strong>CIN:</strong> U45202OR2013PTC016985 &nbsp;|&nbsp; 
                </div>

                <h4 style="margin-top: 15px; color: #0056b3; font-weight: 700;">
                    Branch: <span style="color: #333;"><?php echo $brName; ?></span>
                </h4>

                <div class="mt-3 p-2" style="background: #eef5ff; border: 2px solid #0056b3; border-radius: 6px; display: inline-block; padding-left: 30px; padding-right: 30px;">
                    <div style="font-size: 22px; font-weight: 800; color: #000; text-transform: uppercase; line-height: 1.2;">
                        <?php echo $empDetails['e_name']; ?>
                    </div>
                    
                    <div style="font-size: 18px; margin-top: 5px;">
                         <span style="color: #333; font-weight: 700;">Designation : </span>
                        <span style="color: #0056b3; font-weight: 700;">
                            <?php echo $empDetails['designation']; ?>
                        </span> &nbsp;|&nbsp;
                        <span style="color: #333; font-weight: 700;"> Emplyoee ID : </span>
                        <span style="color: #d63384; font-weight: 700; font-size: 19px;"> <?php echo $empDetails['e_id']; ?>
                        </span> &nbsp;|&nbsp;
                         <span style="color: #333; font-weight: 700;">UAN No : </span>
                        <span style="color: #0056b3; font-weight: 700;">
                            <?php echo $empDetails['uan_no']; ?>
                        </span>
    
                    </div>
                </div>

            <?php } ?>
            <br>
            <div style="margin-top: 15px; font-weight: bold; text-transform: uppercase; color: #555; letter-spacing: 1px;">
                Ledger Account Report
            </div>
          </div>

          <div class="card">
            <div class="card-body">
              <div class="table-responsive">
                <table id="example2" class="table table-striped table-bordered table-hover">
                    <thead class="table-light">
                        <tr>
                            <th>SR No</th>
                            <th>Month - Year</th>
                            <th>Paid Days</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>Total Payout</th>
                        </tr>
                    </thead>
                    <tbody>
                      <?php
                        $cnt = 1;

                        // 1. SALARY QUERY (With Branch Filter)
                        $sql = "
                            SELECT 
                                'old' as source, month, year, attendance, 
                                basic_pay, hra, convyance, medical, gross_pay, 
                                pf, esic, advance, tax, deduction, net_pay, ac_no 
                            FROM prv_emp_salary 
                            WHERE emp_id = ? AND branch = ? 
                              AND CAST(CONCAT(TRIM(year), LPAD(TRIM(month), 2, '0')) AS UNSIGNED) BETWEEN ? AND ?

                            UNION ALL

                            SELECT 
                                'current' as source, month, year, attendance, 
                                basic_pay, hra, convyance, medical, gross_pay, 
                                pf, esic, advance, tax, deduction, net_pay, ac_no 
                            FROM emp_salary 
                            WHERE emp_id = ? AND branch = ? 
                              AND CAST(CONCAT(TRIM(year), LPAD(TRIM(month), 2, '0')) AS UNSIGNED) BETWEEN ? AND ?

                            ORDER BY year ASC, CAST(month AS UNSIGNED) ASC
                        ";

                        $stmt = $conn->prepare($sql);
                        $params = [$ename, $branch, $startPeriod, $endPeriod, $ename, $branch, $startPeriod, $endPeriod];
                        
                        try {
                            $stmt->execute($params);
                            $records = $stmt->fetchAll(PDO::FETCH_ASSOC);
                        } catch(Exception $e) {
                            echo "<tr><td colspan='17' class='text-danger'>Error: " . $e->getMessage() . "</td></tr>";
                            $records = [];
                        }

                        foreach($records as $row) {
                            
                            // 2. OT QUERY (With Branch Filter)
                            $otTable = ($row['source'] == 'old') ? 'prv_ot_details' : 'ot_details';
                            $stmtOT = $conn->prepare("SELECT amount FROM `$otTable` WHERE month = ? AND year = ? AND emp_id = ? AND branch = ?");
                            $stmtOT->execute([$row['month'], $row['year'], $ename, $branch]);
                            $otAmount = $stmtOT->fetchColumn(); 
                            $ot = $otAmount ? $otAmount : 0;

                            // 3. ADVANCE QUERY (With Branch Filter)
                            // Assuming advance table has 'branch' column. If not, remove 'AND branch = ?'
                            $cleanMonth = str_pad($row['month'], 2, '0', STR_PAD_LEFT); 
                            $checkDate = $row['year']."-".$cleanMonth."-01";
                            
                            $stmtAdv = $conn->prepare("SELECT amount FROM `advance` WHERE status=1 AND `date` = ? AND `e_id` = ?"); // Removed branch filter from here temporarily if column missing, add 'AND branch=?' if exists.
                            // Based on your previous requests, I will add it back to be strictly branch based IF schema supports it.
                            // Since you asked "insert branch scope to all queries", I am adding it below. 
                            // If this fails, your advance table is missing the 'branch' column.
                            
                            $stmtAdv = $conn->prepare("SELECT amount FROM `advance` WHERE status=1 AND `date` = ? AND `e_id` = ? AND branch = ?");
                            $stmtAdv->execute([$checkDate, $ename, $branch]);
                            $advTaken = $stmtAdv->fetchColumn();
                            $adv = $advTaken ? $advTaken : 0;

                            // 4. TOTAL PAYOUT CALCULATION
                            $totalPayout = $row['net_pay'] + $ot + $adv;
                            $monthName = date('M', mktime(0, 0, 0, (int)$row['month'], 10));
                      ?>
                        <tr>
                            <td><?php echo $cnt;?></td>
                            <td><?php echo $monthName . "-" . $row['year'];?></td>
                            <td><?php echo $row['attendance'];?></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 class="fw-bold"><?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 class="text-primary fw-bold"><?php echo $row['net_pay'];?></td>
                            
                            <td><?php echo $ot; ?></td>
                            <td><?php echo $adv; ?></td> 
                            
                            <td class="text-success fw-bold"><?php echo number_format((float)$totalPayout, 2); ?></td> 
                        </tr>
                      <?php 
                          $cnt++; 
                        } 
                        
                        if(count($records) == 0) {
                             echo "<tr><td colspan='17' class='text-center text-danger'>No records found for Employee ID: $ename in period $startPeriod to $endPeriod (Branch: $branch).</td></tr>";
                        }
                      ?>
                    </tbody>
                    <tfoot>
                        <tr>
                            <th>SR No</th>
                            <th>Month - Year</th>
                            <th>Paid Days</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>Total Payout</th>
                        </tr>
                    </tfoot>
                </table>
              </div>
            </div>
          </div>
          <?php } ?>

        </div>
      </div>
      <?php include 'system/footer.php' ?>
    </div>
  </body>
</html>