View Code - 20260920

pissing_recon.php | 20260920_164915.php Initial Domain Snapshot

<?php
// pissing_recon.php - adapted from your pissing_salary_report logic to accept JSON input
require_once __DIR__ . '/../../auth/page_acl.php';
require_once __DIR__ . '/recon_lib.php';
$ctx = page_require_access('expense_recon');
$pdo = $ctx['pdo'] ?? null;
header('Content-Type: application/json; charset=utf-8');
if (!($pdo instanceof PDO)) { echo json_encode(['error'=>'pdo_missing']); exit; }

// read payload from php://input or from helper
$raw = $GLOBALS['__recon_payload'] ?? file_get_contents('php://input');
$in = json_decode($raw, true);
if (!is_array($in)) { echo json_encode(['error'=>'invalid_json']); exit; }

$company_id = (int)($in['company_id'] ?? $ctx['company_id'] ?? 0);
$start = $in['pay_period_start'] ?? null;
$end = $in['pay_period_end'] ?? null;
$bank_rows = $in['bank_rows'] ?? [];

if (!$start || !$end) {
    echo json_encode(['error'=>'missing_period']); exit;
}

// Use your pissing aggregation logic (the SQL you had) to produce pissing totals by pissing_type/quality
// Try to reuse schema detection as in your pissing_salary_report, but simplified here:

// detect columns and date column - minimal approach: assume table pissing_entry exists with date column 'entry_date' or 'date' or 'created_at'
function table_has_col_simple(PDO $pdo, $table, $col) {
    $st = $pdo->prepare("SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :t AND COLUMN_NAME = :c");
    $st->execute([':t'=>$table,':c'=>$col]);
    return (bool)$st->fetchColumn();
}
$date_col = 'entry_date';
if (!table_has_col_simple($pdo,'pissing_entry','entry_date')) {
    if (table_has_col_simple($pdo,'pissing_entry','date')) $date_col='date';
    elseif (table_has_col_simple($pdo,'pissing_entry','created_at')) $date_col='created_at';
}

// qty column
$qty_col = table_has_col_simple($pdo,'pissing_entry','qty') ? 'qty' : (table_has_col_simple($pdo,'pissing_entry','quantity') ? 'quantity' : null);

// type column (id or text)
$has_type_id = table_has_col_simple($pdo,'pissing_entry','pissing_type_id');
$type_txt_col = table_has_col_simple($pdo,'pissing_entry','pissing_type') ? 'pissing_type' : (table_has_col_simple($pdo,'pissing_entry','quality') ? 'quality' : null);

// build agg SQL
if ($qty_col) $qty_expr = "SUM(pe.`$qty_col`)";
else $qty_expr = "COUNT(*)";

if ($has_type_id) {
    $agg_key_sel = "pe.pissing_type_id AS tkey_id";
    $agg_group = "tkey_id";
    $join_on = "pq.id = agg.tkey_id";
    $pq_key_expr = "CONVERT(pq.quality USING utf8mb4) COLLATE utf8mb4_unicode_ci";
    $select_quality = "pq.quality";
} else {
    $agg_key_sel = "CONVERT(pe.`$type_txt_col` USING utf8mb4) COLLATE utf8mb4_unicode_ci AS tkey_txt";
    $agg_group = "tkey_txt";
    $join_on = "agg.tkey_txt = (CONVERT(pq.quality USING utf8mb4) COLLATE utf8mb4_unicode_ci)";
    $pq_key_expr = "CONVERT(pq.quality USING utf8mb4) COLLATE utf8mb4_unicode_ci";
    $select_quality = "pe.`$type_txt_col`";
}

$sql = "
WITH agg AS (
  SELECT
    $agg_key_sel,
    $qty_expr AS qty
  FROM pissing_entry pe
  WHERE pe.company_id = :cid
    AND pe.$date_col >= :from AND pe.$date_col <= :to
  GROUP BY $agg_group
)
SELECT
  $pq_key_expr AS pissing_type,
  pq.rate,
  agg.qty,
  agg.qty * pq.rate AS amount
FROM agg
LEFT JOIN pissing_quality pq
  ON ($join_on) AND pq.company_id = :cid2 AND pq.is_active = 1
ORDER BY pissing_type ASC
";

$st = $pdo->prepare($sql);
$params = [':cid'=>$company_id, ':cid2'=>$company_id, ':from'=>$start.' 00:00:00', ':to'=>$end.' 23:59:59'];
$st->execute($params);
$p_rows = $st->fetchAll(PDO::FETCH_ASSOC);

// convert pissing rows to dept-like totals: assume pissing types map to 'Rapier' or 'Loom' based on machine (we cannot know automatically)
// Heuristic: if pissing_type contains 'rapier' -> Rapier, else Loom
$deptTotals = [];
foreach ($p_rows as $r) {
    $ptype = $r['pissing_type'] ?? 'Unknown';
    $amount = (float)$r['amount'];
    $key = (stripos($ptype,'rapier') !== false) ? 'Rapier' : 'Loom';
    $deptTotals[$key] = ($deptTotals[$key] ?? 0) + $amount;
}
$grandTotal = array_sum($deptTotals);

// Now match bank_rows to deptTotals using same pattern as semi
require_once __DIR__ . '/recon_lib.php';
$alloc_rules = [
    'folding'=>['repair_pct'=>30,'loom_pct'=>70],
    'dept_map' => ['Rapier'=>'rapier_expense','Loom'=>'loom_expense'],
    'default_to'=>'loom_expense'
];

$results = [];
foreach ($bank_rows as $b) {
    $bAmt = (float)($b['amount'] ?? 0);
    $matched=false; $match_details=[]; $suggested_allocs=[];
    // exact dept match
    foreach ($deptTotals as $dept=>$dAmt) {
        if (abs($dAmt - $bAmt) <= 0.5) {
            $match_details[]=['reason'=>'matched_dept_exact','dept'=>$dept,'dept_amount'=>round($dAmt,2)];
            $suggested_allocs = array_merge($suggested_allocs, allocate_dept_amount_generic($dept,$dAmt,$alloc_rules));
            $matched=true; break;
        }
    }
    if (!$matched && abs($grandTotal - $bAmt) <= 0.5) {
        foreach ($deptTotals as $dept=>$dAmt) $suggested_allocs = array_merge($suggested_allocs, allocate_dept_amount_generic($dept,$dAmt,$alloc_rules));
        $match_details[]=['reason'=>'matched_grand_total'];
        $matched=true;
    }
    if (!$matched && !empty($b['narration'])) {
        foreach ($deptTotals as $dept=>$dAmt) {
            if (stripos($b['narration'],$dept)!==false) {
                $amt = min($dAmt,$bAmt);
                $suggested_allocs = array_merge($suggested_allocs, allocate_dept_amount_generic($dept,$amt,$alloc_rules));
                $match_details[]=['reason'=>'narration_hint','dept'=>$dept];
                $matched=true;
            }
        }
    }
    if (!$matched && $grandTotal>0) {
        foreach ($deptTotals as $dept=>$dAmt) {
            $share = ($dAmt/$grandTotal) * $bAmt;
            $suggested_allocs = array_merge($suggested_allocs, allocate_dept_amount_generic($dept,$share,$alloc_rules));
        }
        $match_details[]=['reason'=>'proportional_fallback'];
        $matched=true;
    }
    $id = persist_recon_result($pdo, $b, $match_details, $suggested_allocs, $company_id, $ctx['user']['id'], 'pissing');
    $results[]=['id'=>$id,'bank_row'=>$b,'matched'=>$matched,'match'=>$match_details,'alloc'=>$suggested_allocs];
}

echo json_encode(['company_id'=>$company_id,'start'=>$start,'end'=>$end,'dept_totals'=>$deptTotals,'grand_total'=>$grandTotal,'results'=>$results], JSON_PRETTY_PRINT|JSON_UNESCAPED_UNICODE);
exit;