View Code - 20260721

semi_fill_from_report.php | 20260721_154033.php Initial Bulk Import

<?php
/* =============================================================================
   File: /erp/semi_fill_from_report.php
   Purpose: Build bank upload sheet by joining semi_monthly_salary_report -> employee_bank_data
            Outputs XLSX (default) or TSV (&fmt=csv). Filter by month/year/half.
   Usage: /erp/semi_fill_from_report.php?month=9&year=2025&half=1-15
   ============================================================================ */
error_reporting(E_ALL); ini_set('display_errors', 1);

require_once __DIR__ . '/modules/auth/page_acl.php';
$ctx = page_require_access('semi_monthly_salary_export');
$company_id = (int)($ctx['company_id'] ?? 0);
$user_id    = (int)($ctx['user']['id'] ?? 0);
$pdo        = $ctx['pdo'] ?? null;
if (!$pdo) { require_once __DIR__ . '/core/db.php'; }

/* ---------------- Helpers (small) ---------------- */
function table_exists(PDO $pdo, string $t): bool {
  try{ $st=$pdo->prepare("SELECT 1 FROM information_schema.tables WHERE table_schema=DATABASE() AND table_name=?");
       $st->execute([$t]); return (bool)$st->fetchColumn(); }catch(Throwable $e){ return false; }
}
function xlsx_col_name(int $n): string { $s=''; while($n>0){ $m=($n-1)%26; $s=chr(65+$m).$s; $n=(int)(($n-$m-1)/26); } return $s; }
function xlsx_xml_escape(string $s): string { return htmlspecialchars($s, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8'); }
function xlsx_build_sheet_xml(array $rows): string {
  $xml = '<?xml version="1.0" encoding="UTF-8"?>'
       . '<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" '
       . 'xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"><sheetData>';
  $r=0; foreach ($rows as $row){ $r++; $xml.='<row r="'.$r.'">'; $c=0;
    foreach($row as $val){ $c++; $addr=xlsx_col_name($c).$r;
      $xml .= '<c r="'.$addr.'" t="inlineStr"><is><t>'.xlsx_xml_escape((string)$val).'</t></is></c>';
    }
    $xml.='</row>';
  }
  return $xml.'</sheetData></worksheet>';
}
function xlsx_zip_build(string $sheetName, array $rows): string {
  $sheetName = $sheetName!==''? mb_substr($sheetName,0,31) : 'Sheet1';
  $tmp = tempnam(sys_get_temp_dir(),'xlsx_'); if (file_exists($tmp)) unlink($tmp);
  $sheetXml = xlsx_build_sheet_xml($rows); $coreDate=gmdate('Y-m-d\TH:i:s\Z');
  $content_types ='<?xml version="1.0" encoding="UTF-8"?>
<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">
  <Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>
  <Default Extension="xml" ContentType="application/xml"/>
  <Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>
  <Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>
  <Override PartName="/docProps/core.xml" ContentType="application/vnd.openxmlformats-package.core-properties+xml"/>
  <Override PartName="/docProps/app.xml" ContentType="application/vnd.openxmlformats-officedocument.extended-properties+xml"/>
  <Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>
</Types>';
  $rels ='<?xml version="1.0" encoding="UTF-8"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
  <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>
  <Relationship Id="rId2" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" Target="docProps/core.xml"/>
  <Relationship Id="rId3" Type="http://schemas.openxmlformats.org/package/2006/relationships/extended-properties" Target="docProps/app.xml"/>
</Relationships>';
  $app ='<?xml version="1.0" encoding="UTF-8"?>
<Properties xmlns="http://schemas.openxmlformats.org/officeDocument/extended-properties" xmlns:vt="http://schemas.openxmlformats.org/officeDocument/docPropsVTypes">
  <Application>ERP</Application>
</Properties>';
  $core ='<?xml version="1.0" encoding="UTF-8"?>
<cp:coreProperties xmlns:cp="http://schemas.openxmlformats.org/package/2006/metadata/core-properties" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:dcterms="http://purl.org/dc/terms/" xmlns:dcmitype="http://purl.org/dc/dcmitype/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <dc:creator>ERP</dc:creator><cp:lastModifiedBy>ERP</cp:lastModifiedBy>
  <dcterms:created xsi:type="dcterms:W3CDTF">'.$coreDate.'</dcterms:created>
  <dcterms:modified xsi:type="dcterms:W3CDTF">'.$coreDate.'</dcterms:modified>
</cp:coreProperties>';
  $workbook ='<?xml version="1.0" encoding="UTF-8"?>
<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
  <sheets><sheet name="'.xlsx_xml_escape($sheetName).'" sheetId="1" r:id="rId1"/></sheets>
</workbook>';
  $wb_rels ='<?xml version="1.0" encoding="UTF-8"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
  <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/>
  <Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/>
</Relationships>';
  $styles ='<?xml version="1.0" encoding="UTF-8"?>
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <fonts count="1"><font><sz val="11"/><color theme="1"/><name val="Calibri"/><family val="2"/></font></fonts>
  <fills count="1"><fill><patternFill patternType="none"/></fill></fills>
  <borders count="1"><border><left/><right/><top/><bottom/><diagonal/></border></borders>
  <cellStyleXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0"/></cellStyleXfs>
  <cellXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/></cellXfs>
</styleSheet>';
  $zip = new ZipArchive();
  if (!$zip->open($tmp, ZipArchive::CREATE)) { throw new RuntimeException('Zip open failed'); }
  $zip->addFromString('[Content_Types].xml', $content_types);
  $zip->addFromString('_rels/.rels', $rels);
  $zip->addFromString('docProps/app.xml', $app);
  $zip->addFromString('docProps/core.xml', $core);
  $zip->addFromString('xl/workbook.xml', $workbook);
  $zip->addFromString('xl/_rels/workbook.xml.rels', $wb_rels);
  $zip->addFromString('xl/styles.xml', $styles);
  $zip->addFromString('xl/worksheets/sheet1.xml', $sheetXml);
  $zip->close();
  return $tmp;
}

/* ---------------- Input filters ---------------- */
$month = (int)($_GET['month'] ?? date('n'));
$year  = (int)($_GET['year'] ?? date('Y'));
$half  = $_GET['half'] ?? '1-15';
if ($half==='1-15') {
  $period_start = date('Y-m-d', strtotime("$year-$month-01"));
  $period_end   = date('Y-m-d', strtotime("$year-$month-15"));
} else {
  $period_start = date('Y-m-d', strtotime("$year-$month-16"));
  $period_end   = date('Y-m-d', strtotime("$year-$month-01 +1 month -1 day"));
}

/* ---------------- Check tables exist ---------------- */
$T = 'semi_monthly_salary_report';
$B = 'employee_bank_data';
if (!table_exists($pdo,$T)) { http_response_code(500); exit("Table {$T} not found."); }
if (!table_exists($pdo,$B)) { http_response_code(500); exit("Table {$B} not found."); }

/* ---------------- Query: join report -> bank data ---------------- */
/* We select only rows that have a bank account present in employee_bank_data.
   If you want to export also rows without bank detail, remove the JOIN and use LEFT JOIN. */
$sql = "
  SELECT r.employee_id, r.employee_name, r.net_pay AS amount,
         b.beneficiary_ac_number AS account_no,
         COALESCE(b.beneficiary_name, r.employee_name) AS beneficiary_name,
         b.ifsc_code AS ifsc,
         b.mobile, b.email, r.period_label, r.month_key
  FROM `$T` r
  INNER JOIN `$B` b ON b.company_id = r.company_id AND b.employee_id = r.employee_id
  WHERE r.company_id = :cid
    AND r.month_key BETWEEN :ps AND :pe
    AND r.period_label = :pl
  ORDER BY r.employee_id
";
$st = $pdo->prepare($sql);
$st->execute([':cid'=>$company_id, ':ps'=>$period_start, ':pe'=>$period_end, ':pl'=>$half]);
$rows = $st->fetchAll(PDO::FETCH_ASSOC);
if (empty($rows)) { http_response_code(400); exit('No rows to export (no matching bank-data rows for this period).'); }

/* ---------------- Build export rows ---------------- */
$headers = [
  'Debit A/c Number',
  'Beneficiary A/c Number',
  'Beneficiary Name',
  'Amount',
  'Payment Type (Mandatory for all types of payments)',
  'Payment date',
  'IFSC Code',
  'Beneficiary Mobile No.',
  'Beneficiary email-id',
  'Bene Address 1',
  'Bene Address 2',
  'Bene Address 3',
  'Bene Address 4',
  'Add detail 1',
  'Add detail 2',
  'Add detail 3',
  'Add detail 4',
  'Add detail 5',
  'Remarks',
  'Credit Narration',
];

$rows_for = [];
$rows_for[] = $headers;

/* company debit account helper */
function get_company_debit_ac(PDO $pdo, int $company_id): string {
  if (!table_exists($pdo,'company_bank_accounts')) return '';
  $cols = table_cols($pdo,'company_bank_accounts');
  foreach (['debit_ac_no','debit_account_no','debit_account','bank_account_no','account_no','ac_no'] as $c){
    if (isset($cols[$c])){
      try{ $st=$pdo->prepare("SELECT $c FROM company_bank_accounts WHERE company_id=? ORDER BY id DESC LIMIT 1");
           $st->execute([$company_id]); return trim((string)$st->fetchColumn());
      }catch(Throwable $e){ return ''; }
    }
  } return '';
}
$debit_ac = get_company_debit_ac($pdo,$company_id);
$payDate = date('Y-m-d'); $payDateFmt = date('d-M-Y', strtotime($payDate));
$narration = trim(($half!=='' ? $half.' - ' : '').date('M-Y', strtotime($payDate)));

foreach ($rows as $r){
  $account = trim((string)($r['account_no'] ?? ''));
  if ($account === '') continue; // skip if no account (change to include by LEFT JOIN if you want)
  $bname = trim((string)($r['beneficiary_name'] ?? ''));
  $ifsc  = strtoupper(trim((string)($r['ifsc'] ?? '')));
  $mobile= trim((string)($r['mobile'] ?? ''));
  $email = trim((string)($r['email'] ?? ''));
  $amt   = number_format((float)($r['amount'] ?? 0), 0, '.', '');

$line = array_fill(0, count($headers), '');
  $line[0] = $debit_ac;
  $line[1] = $account;
  $line[2] = $bname;
  $line[3] = $amt;
  $line[4] = (strpos($ifsc,'ICIC0')===0)?'I':'N';
  $line[5] = $payDateFmt;
  $line[6] = $ifsc;
  $line[7] = $mobile;
  $line[8] = $email;
  $line[18] = ''; // remarks (if you want set from DB)
  $line[19] = $narration;

$rows_for[] = $line;
}

/* -------------- Stream file -------------- */
$fmt = strtolower($_GET['fmt'] ?? 'xlsx');
$sheetName = "semi_bank_fill_".date('dmY_His');
$fileName = $sheetName . ($fmt==='xlsx' ? '.xlsx' : '.tsv');

if ($fmt === 'xlsx') {
  $xlsxPath = xlsx_zip_build($sheetName, $rows_for);
  header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
  header('Content-Disposition: attachment; filename="'.$fileName.'"');
  header('Content-Length: '.filesize($xlsxPath));
  readfile($xlsxPath); @unlink($xlsxPath); exit;
}

/* TSV fallback */
$out = fopen('php://temp','w+'); fwrite($out, "\xEF\xBB\xBF");
foreach ($rows_for as $row){ fwrite($out, implode("\t", array_map('strval',$row))."\r\n"); }
rewind($out);
header('Content-Type: text/tab-separated-values; charset=UTF-8');
header('Content-Disposition: attachment; filename="'.$fileName.'"');
fpassthru($out); fclose($out); exit;