View Code - 20260723

salary_report.php | 20260723_000646.php Initial Domain Snapshot

<?php
/* =============================================================================
   FILE : /erp/public/reports/salary_report.php
   TITLE: Warper Salary (Per-TAKA • Quality-wise) — Warper-only (dept_id=22)
   BEHAV: Same mapping as /erp/public/warper_beam_entry.php
          1) FORCE_EMP_ID → 2) session warper_emp_id → 3) users.employee_id join
          4) unique name match (dept 22) → else chooser (session only)
   UI   : Month + Period (1–15 | 16–End). No free date range.
   CALC : Amount = SUM(beam_entry.taka) × beam_qualities.warping_rate (quality-wise)
   NOTE : Forms POST to this file's absolute path to avoid index.php redirect.
   ============================================================================= */

error_reporting(E_ALL);
ini_set('display_errors', 1);

/* ---------------- Constants ---------------- */
$SELF_URL     = '/erp/public/reports/salary_report.php'; // <-- forms will post here
$FORCE_EMP_ID = 0; // e.g., 25 for emergency testing. Keep 0 normally.

/* =================== A) DB bootstrap ===================== */
$pdo = $pdo ?? null;
if (!$pdo instanceof PDO) {
  $ERP_ROOT = dirname(__DIR__, 2); // /erp
  $dbfile   = $ERP_ROOT . '/core/db.php';
  if (is_file($dbfile)) require_once $dbfile;
  if (!$pdo instanceof PDO && isset($GLOBALS['pdo']) && $GLOBALS['pdo'] instanceof PDO) $pdo = $GLOBALS['pdo'];
  if (!$pdo instanceof PDO && defined('DB_HOST')) {
    try {
      $pdo = new PDO(
        'mysql:host='.DB_HOST.';dbname='.DB_NAME.';charset=utf8mb4',
        DB_USER, DB_PASS,
        [ PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE=>PDO::FETCH_ASSOC ]
      );
    } catch (Throwable $e) {}
  }
}
if (!$pdo instanceof PDO) { http_response_code(500); echo 'DB not connected'; exit; }

/* =================== B) Auth ===================== */
$ERP_ROOT = $ERP_ROOT ?? dirname(__DIR__, 2);
$auth     = $ERP_ROOT . '/modules/auth/auth.php';
if (is_file($auth) && !function_exists('require_login')) require_once $auth;
if (function_exists('require_login')) require_login();

$u          = function_exists('auth_user') ? auth_user() : [];
$company_id = (int)($u['company_id'] ?? 0);
$user_id    = (int)($u['id'] ?? 0);
$user_name  = (string)($u['name'] ?? '');
if ($company_id<=0 || $user_id<=0) { http_response_code(401); exit('Unauthorized'); }

/* =================== C) Helpers ===================== */
function h($s){ return htmlspecialchars((string)$s, ENT_QUOTES, 'UTF-8'); }
function postv($k,$d=null){ return isset($_POST[$k]) ? (is_string($_POST[$k])?trim($_POST[$k]):$_POST[$k]) : $d; }
function cols(PDO $pdo,$t){ try{$r=$pdo->query("SHOW COLUMNS FROM `$t`")->fetchAll(PDO::FETCH_ASSOC);$m=[];foreach($r as $c)$m[strtolower($c['Field'])]=1;return $m;}catch(Throwable $e){return[];}}

/* =================== D) Table presence ===================== */
$cem = cols($pdo,'company_employee_master');
$be  = cols($pdo,'beam_entry');
$bq  = cols($pdo,'beam_qualities');
if (!$cem || !$be || !$bq) { http_response_code(500); exit('Required tables missing'); }

/* =================== E) Resolve Warper (dept 22) — SAME flow ===================== */
$emp_id = 0;
if ($FORCE_EMP_ID>0) $emp_id = (int)$FORCE_EMP_ID;
if (!$emp_id && !empty($_SESSION['warper_emp_id'])) $emp_id = (int)$_SESSION['warper_emp_id'];

try{
  if (!$emp_id) {
    $uc = cols($pdo,'users');
    if (isset($uc['employee_id'])) {
      $st=$pdo->prepare("
        SELECT cem.id
        FROM company_employee_master cem
        JOIN users u ON u.company_id=cem.company_id AND u.employee_id=cem.id
        WHERE u.id=? AND u.company_id=? AND cem.is_active=1 AND cem.department_id=22
        LIMIT 1
      ");
      $st->execute([$user_id,$company_id]); $emp_id=(int)$st->fetchColumn();
    }
  }
  if (!$emp_id && $user_name!=='') {
    $st=$pdo->prepare("
      SELECT id
      FROM company_employee_master
      WHERE company_id=? AND is_active=1 AND department_id=22
            AND (name=? OR TRIM(LOWER(name))=TRIM(LOWER(?)))
      ORDER BY id DESC
    ");
    $st->execute([$company_id,$user_name,$user_name]);
    $rows=$st->fetchAll(PDO::FETCH_COLUMN);
    if (count($rows)===1) $emp_id=(int)$rows[0];
  }
} catch(Throwable $e){}

/* If user picked from dropdown, store in session (no DB write) */
if ($_SERVER['REQUEST_METHOD']==='POST' && postv('_action')==='choose_emp') {
  $chosen = (int)postv('emp_choose',0);
  $ok = 0;
  try{
    $st=$pdo->prepare("SELECT id FROM company_employee_master WHERE company_id=? AND id=? AND is_active=1 AND department_id=22");
    $st->execute([$company_id,$chosen]); $ok=(int)$st->fetchColumn();
  }catch(Throwable $e){}
  if ($ok>0) { $_SESSION['warper_emp_id']=$ok; $emp_id=$ok; }
}

/* =================== F) Dept 22 list (for chooser) ===================== */
$dept22=[];
try{
  $st=$pdo->prepare("SELECT id,name FROM company_employee_master WHERE company_id=? AND is_active=1 AND department_id=22 ORDER BY name");
  $st->execute([$company_id]); $dept22=$st->fetchAll(PDO::FETCH_ASSOC);
}catch(Throwable $e){}

/* =================== G) PERIOD (Month + H1/H2) ===================== */
$month  = postv('month', date('Y-m'));                          // yyyy-mm
$period = postv('period', (date('d')<=15 ? 'H1' : 'H2'));       // H1 or H2
if (!preg_match('/^\d{4}-\d{2}$/',$month)) $month = date('Y-m');
$y = (int)substr($month,0,4);
$m = (int)substr($month,5,2);
$lastDay = (int)date('t', strtotime("$month-01"));
if ($period!=='H1' && $period!=='H2') $period = 'H1';
$from = ($period==='H1') ? sprintf('%04d-%02d-01',$y,$m) : sprintf('%04d-%02d-16',$y,$m);
$to   = ($period==='H1') ? sprintf('%04d-%02d-15',$y,$m) : sprintf('%04d-%02d-%02d',$y,$m,$lastDay);

/* =================== H) REPORT (Per-TAKA • Quality-wise for THIS emp only) ===================== */
$lines=[]; $tot_taka=0.0; $tot_amt=0.0; $emp_name='';
if ($emp_id>0) {
  try{
    $st=$pdo->prepare("SELECT name FROM company_employee_master WHERE company_id=? AND id=?");
    $st->execute([$company_id,$emp_id]); $emp_name=(string)$st->fetchColumn();
  }catch(Throwable $e){ $emp_name=''; }

$sql = "
    SELECT
      be.quality_id,
      q.name AS quality_name,
      COALESCE(SUM(CASE
        WHEN be.taka IS NULL OR be.taka='' THEN 0
        ELSE CAST(be.taka AS DECIMAL(12,2))
      END),0) AS total_taka,
      COALESCE(q.warping_rate,0) AS rate_per_taka
    FROM beam_entry be
    LEFT JOIN beam_qualities q
      ON q.id=be.quality_id AND q.company_id=be.company_id
    WHERE be.company_id=:cid
      AND be.employee_id=:eid
      AND be.entry_date BETWEEN :from AND :to
    GROUP BY be.quality_id, q.name, q.warping_rate
    ORDER BY q.name
  ";
  $st=$pdo->prepare($sql);
  $st->execute([':cid'=>$company_id, ':eid'=>$emp_id, ':from'=>$from, ':to'=>$to]);
  $rows=$st->fetchAll(PDO::FETCH_ASSOC);

foreach($rows as $r){
    $taka=(float)$r['total_taka'];
    $rate=(float)$r['rate_per_taka'];
    $amt =$taka*$rate;

$lines[]=[
      'quality_id'   => (int)$r['quality_id'],
      'quality_name' => $r['quality_name'] ?: '—',
      'taka'         => $taka,
      'rate'         => $rate,
      'amount'       => $amt
    ];
    $tot_taka += $taka;
    $tot_amt  += $amt;
  }
}

/* =================== I) CSV export (POST) ===================== */
if (isset($_POST['export']) && $emp_id>0) {
  header('Content-Type: text/csv; charset=utf-8');
  header('Content-Disposition: attachment; filename="warper_salary_'.($emp_name?:'me')."_{$month}_{$period}.csv\"");
  $out = fopen('php://output','w');
  fputcsv($out, ['Month',$month,'Period',$period==='H1'?'1–15':'16–end']);
  fputcsv($out, ['From',$from,'To',$to]);
  fputcsv($out, ['Warper',$emp_name?:$user_name]);
  fputcsv($out, []);
  fputcsv($out, ['Quality','Total Taka','Rate/TAKA','Amount']);
  foreach ($lines as $ln){
    fputcsv($out, [
      $ln['quality_name'],
      number_format($ln['taka'],2,'.',''),
      number_format($ln['rate'],2,'.',''),
      number_format($ln['amount'],2,'.',''),
    ]);
  }
  fputcsv($out, []);
  fputcsv($out, ['TOTAL',
    number_format($tot_taka,2,'.',''),
    '',
    number_format($tot_amt,2,'.',''),
  ]);
  fclose($out); exit;
}

/* =================== J) HTML ===================== */
?>
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>Warper Salary (Per-TAKA)</title>
<meta name="viewport" content="width=device-width,initial-scale=1">
<style>
  :root{--primary:#34A853;--bg:#F5FFF7;--card:#fff;--text:#202124;--muted:#5F6368;--border:#e5e7eb;--r:14px;--shadow:0 8px 20px rgba(0,0,0,.08)}
  *{box-sizing:border-box} body{margin:0;background:#F5FFF7;color:var(--text);font:14px/1.45 system-ui,-apple-system,Segoe UI,Roboto,Arial}
  .wrap{max-width:860px;margin:16px auto;padding:16px}
  .card{background:var(--card);border:1px solid var(--border);border-radius:var(--r);box-shadow:var(--shadow);padding:18px}
  .top{display:flex;justify-content:space-between;align-items:center;margin-bottom:10px}
  a.link{color:#0b57d0;text-decoration:none} a.link:hover{text-decoration:underline}
  .err{margin:10px 0;background:#fff7e6;border:1px solid #ffe2a8;color:#7a5900;padding:8px;border-radius:10px}
  label{display:block;margin:8px 0 6px;font-weight:600;color:#111}
  input,select{width:100%;padding:10px;border:1px solid #d0d7de;border-radius:10px;background:#fff}
  .btn{padding:10px 16px;border:0;border-radius:10px;cursor:pointer;font-weight:600}
  .btn-pri{background:var(--primary);color:#fff} .btn-sec{background:#f1f3f4}
  table{width:100%;border-collapse:collapse;margin-top:14px;background:#fff}
  th,td{padding:10px;border-bottom:1px solid #e5e7eb;text-align:left;font-size:13px} .right{text-align:right}
  .grid{display:grid;gap:14px;grid-template-columns:1fr 1fr 1fr} .grid .full{grid-column:1/-1}
  @media(max-width:640px){.grid{grid-template-columns:1fr}}
</style>
</head>
<body>
<div class="wrap">
  <div class="top">
    <a class="link" href="/erp/public/employee_portal.php">← Dashboard</a>
    <div style="color:#5F6368">Logged in: <?= h($user_name) ?></div>
  </div>

<div class="card">
    <h2 style="margin:0 0 6px">Warper Salary (Per-TAKA)</h2>
    <div style="color:#666;margin-bottom:8px">Rate source: <b>beam_qualities.warping_rate</b> • Department: <b>Warping (22)</b></div>

<?php if($emp_id<=0): ?>
      <div class="err">Aapka login dept-22 (Warper) se link nahi mila. Apna naam select kijiye — yeh sirf session me save hoga (DB write nahi).</div>
      <form method="post" style="display:flex;gap:10px;flex-wrap:wrap;align-items:center" action="<?= $SELF_URL ?>">
        <input type="hidden" name="_action" value="choose_emp">
        <select name="emp_choose" required>
          <option value="">-- Dept 22 employees --</option>
          <?php foreach($dept22 as $e): ?>
            <option value="<?= (int)$e['id'] ?>"><?= h($e['name']) ?> (ID <?= (int)$e['id'] ?>)</option>
          <?php endforeach; ?>
        </select>
        <button class="btn btn-pri" type="submit">Continue</button>
      </form>

<?php else: ?>
      
      <form method="post" class="grid" style="align-items:end;margin-top:8px" action="<?= $SELF_URL ?>">
        <div>
          <label>Month</label>
          <input type="month" name="month" value="<?= h($month) ?>">
        </div>
        <div>
          <label>Period</label>
          <select name="period">
            <option value="H1" <?= $period==='H1'?'selected':'' ?>>1 – 15</option>
            <option value="H2" <?= $period==='H2'?'selected':'' ?>>16 – End</option>
          </select>
        </div>
        <div class="full" style="display:flex;gap:10px">
          <button class="btn btn-pri" type="submit">Refresh</button>
          <button class="btn btn-sec" name="export" value="1" type="submit">Export CSV</button>
          <button class="btn btn-sec" type="button" onclick="window.print()">Print</button>
        </div>
      </form>
    <?php endif; ?>
  </div>

<?php if($emp_id>0): ?>
  <div class="card">
    <h3 style="margin:0 0 8px"><?= h($emp_name ?: $user_name) ?> — <?= h($month) ?> • <?= $period==='H1'?'1–15':'16–end' ?> (<?= h($from) ?> → <?= h($to) ?>)</h3>
    <table>
      <thead>
        <tr>
          <th>#</th>
          <th>Quality</th>
          <th class="right">Total Taka</th>
          <th class="right">Rate/TAKA</th>
          <th class="right">Amount</th>
        </tr>
      </thead>
      <tbody>
        <?php if(empty($lines)): ?>
          <tr><td colspan="5" style="color:#5F6368">No data in this period.</td></tr>
        <?php else: $i=1; foreach($lines as $ln): ?>
          <tr>
            <td><?= $i++ ?></td>
            <td><?= h($ln['quality_name']) ?></td>
            <td class="right"><?= number_format($ln['taka'],2) ?></td>
            <td class="right"><?= number_format($ln['rate'],2) ?></td>
            <td class="right"><b><?= number_format($ln['amount'],2) ?></b></td>
          </tr>
        <?php endforeach; endif; ?>
      </tbody>
      <tfoot>
        <tr>
          <td colspan="2"><b>TOTAL</b></td>
          <td class="right"><?= number_format($tot_taka,2) ?></td>
          <td></td>
          <td class="right"><?= number_format($tot_amt,2) ?></td>
        </tr>
      </tfoot>
    </table>
  </div>
  <?php endif; ?>
</div>
</body>
</html>