View Code - 20260722

loom_salary_netpay.php | 20260722_120325.php Initial Domain Snapshot

<?php
/**
 * Loom Salary – FINAL NET total (SAFE VERSION)
 */

/* =========================
   SAFE detect_date_col
========================= */
if (!function_exists('detect_date_col')) {
    function detect_date_col(PDO $pdo, string $table): string {
        foreach (['entry_date','date','created_at','production_date'] as $c) {
            $st = $pdo->prepare("
              SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
              WHERE TABLE_SCHEMA = DATABASE()
                AND TABLE_NAME = ?
                AND COLUMN_NAME = ?
            ");
            $st->execute([$table, $c]);
            if ($st->fetchColumn()) return $c;
        }
        throw new RuntimeException("No date column found in $table");
    }
}

/* =========================
   MAIN FUNCTION (SAFE)
========================= */
if (!function_exists('get_loom_salary_total')) {
function get_loom_salary_total(
    PDO $pdo,
    int $company_id,
    string $from,
    string $to,
    int $khata_id = 0,
    int $machine_id = 0
): float {

/* ---- detect date column ---- */
    $date_col = detect_date_col($pdo, 'production_entry');

/* ---- quality rates ---- */
    $QUAL_RATE = [];
    $st = $pdo->prepare("
        SELECT quality_id, rate
        FROM quality_rates
        WHERE company_id = ?
    ");
    $st->execute([$company_id]);
    foreach ($st as $r) {
        $QUAL_RATE[(int)$r['quality_id']] = (float)$r['rate'];
    }

/* ---- production ---- */
    $MTR = [];

$sql = "
      SELECT quality_id, lines_json
      FROM production_entry
      WHERE company_id = ?
        AND $date_col BETWEEN ? AND ?
        ".($khata_id ? "AND khata_id = ?" : "")."
        ".($machine_id ? "AND machine_id = ?" : "")."
    ";

$params = [$company_id, $from.' 00:00:00', $to.' 23:59:59'];
    if ($khata_id)   $params[] = $khata_id;
    if ($machine_id) $params[] = $machine_id;

$st = $pdo->prepare($sql);
    $st->execute($params);

foreach ($st as $row) {
        $qid = (int)$row['quality_id'];
        if ($qid <= 0) continue;

$lines = json_decode($row['lines_json'] ?? '', true);
        if (!is_array($lines)) continue;

foreach ($lines as $li) {
            $kid = (int)($li['karigar_id'] ?? 0);
            $mtr = (float)($li['meter'] ?? 0);
            if ($kid <= 0 || $mtr <= 0) continue;

$MTR[$kid][$qid] = ($MTR[$kid][$qid] ?? 0) + $mtr;
        }
    }

/* ---- date columns ---- */
    $extra_date = detect_date_col($pdo, 'karigar_extra');
    $ded_date   = detect_date_col($pdo, 'karigar_deduction');
    $adv_date   = detect_date_col($pdo, 'karigar_advance');

/* ---- extra / deduction / advance ---- */
    $EXTRA = $DEDUCT = $ADV = [];

foreach (array_keys($MTR) as $kid) {

$st = $pdo->prepare("
          SELECT COALESCE(SUM(amount),0)
          FROM karigar_extra
          WHERE company_id = ?
            AND karigar_id = ?
            AND $extra_date BETWEEN ? AND ?
        ");
        $st->execute([$company_id, $kid, $from, $to]);
        $EXTRA[$kid] = (float)$st->fetchColumn();

$st = $pdo->prepare("
          SELECT COALESCE(SUM(amount),0)
          FROM karigar_deduction
          WHERE company_id = ?
            AND karigar_id = ?
            AND $ded_date BETWEEN ? AND ?
        ");
        $st->execute([$company_id, $kid, $from, $to]);
        $DEDUCT[$kid] = (float)$st->fetchColumn();

$st = $pdo->prepare("
          SELECT COALESCE(SUM(amount),0)
          FROM karigar_advance
          WHERE company_id = ?
            AND karigar_id = ?
            AND $adv_date BETWEEN ? AND ?
        ");
        $st->execute([$company_id, $kid, $from, $to]);
        $ADV[$kid] = (float)$st->fetchColumn();
    }

/* ---- FINAL NET ---- */
    $grand_net = 0.0;

foreach ($MTR as $kid => $qs) {

$gross = 0.0;

foreach ($qs as $qid => $mtr) {
            $gross += $mtr * ($QUAL_RATE[$qid] ?? 0);
        }

$net = $gross
             + ($EXTRA[$kid] ?? 0)
             - ($DEDUCT[$kid] ?? 0)
             - ($ADV[$kid] ?? 0);

$grand_net += $net;
    }

return round($grand_net, 2);
}
}