manjapro_v6/list_data/history_payment_gateway.php

<?php

include '../config/connect.php';

header('Content-Type: application/json');

// =====================
// PARAMETER DATATABLE
// =====================
$draw   = isset($_GET['draw']) ? (int)$_GET['draw'] : 1;
$start  = isset($_GET['start']) ? (int)$_GET['start'] : 0;
$length = isset($_GET['length']) ? (int)$_GET['length'] : 10;

$search = trim($_GET['search']['value'] ?? '');

// =====================
// FILTER
// =====================
$idsv   = $_GET['idsv'] ?? '';
$idmsv  = $_GET['idmsv'] ?? '';
$status = $_GET['status'] ?? 'ALL';

$startDate = $_GET['start_date'] ?? date('Y-m-d');
$endDate   = $_GET['end_date'] ?? date('Y-m-d');

// =====================
// FROM + JOIN
// =====================
$from = "
FROM payment_winpay
INNER JOIN v_tagihan
    ON payment_winpay.id_tagihan = v_tagihan.id
";

// =====================
// BASE WHERE
// =====================
$where = [];
$params = [];

// =====================
// FILTER STATUS
// =====================
if ($status !== 'ALL' && !empty($status)) {

    $where[] = "payment_winpay.status = ?";
    $params[] = $status;
}

// =====================
// FILTER SERVER
// =====================
if (!empty($idsv)) {

    $ids = explode(',', $idsv);
    $ids = array_filter($ids, 'is_numeric');

    if (!empty($ids)) {

        $placeholder = implode(',', array_fill(0, count($ids), '?'));

        $where[] = "v_tagihan.id_data_server IN ($placeholder)";

        foreach ($ids as $id) {
            $params[] = $id;
        }
    }
}

// =====================
// FILTER MARKETING
// =====================
if (!empty($idmsv) && is_numeric($idmsv)) {

    $where[] = "v_tagihan.id_marketing = ?";
    $params[] = $idmsv;
}

// =====================
// FILTER TANGGAL
// =====================
if (!empty($startDate) && !empty($endDate)) {

    $where[] = "DATE(payment_winpay.created_at) BETWEEN ? AND ?";

    $params[] = $startDate;
    $params[] = $endDate;
}

// =====================
// SEARCH
// =====================
if (!empty($search)) {

    $where[] = "(
        v_tagihan.nama LIKE ?
        OR v_tagihan.akun LIKE ?
        OR v_tagihan.nomor_whatsapp LIKE ?
        OR payment_winpay.channel LIKE ?
        OR payment_winpay.status LIKE ?
    )";

    $searchLike = "%{$search}%";

    $params[] = $searchLike;
    $params[] = $searchLike;
    $params[] = $searchLike;
    $params[] = $searchLike;
    $params[] = $searchLike;
}

// =====================
// GABUNG WHERE
// =====================
$whereSql = '';

if (!empty($where)) {
    $whereSql = 'WHERE ' . implode(' AND ', $where);
}

// =====================
// TOTAL FILTERED
// =====================
$sqlFiltered = "
SELECT COUNT(*)
$from
$whereSql
";

$stmt = $pdo->prepare($sqlFiltered);
$stmt->execute($params);

$totalFiltered = (int)$stmt->fetchColumn();

// =====================
// TOTAL TANPA SEARCH
// =====================
$whereNoSearch = [];
$paramsNoSearch = [];

// wajib
$whereNoSearch[] = "v_tagihan.transaction_id IS NOT NULL";

// status
if ($status !== 'ALL' && !empty($status)) {

    $whereNoSearch[] = "payment_winpay.status = ?";
    $paramsNoSearch[] = $status;
}

// server
if (!empty($idsv)) {

    $ids = explode(',', $idsv);
    $ids = array_filter($ids, 'is_numeric');

    if (!empty($ids)) {

        $placeholder = implode(',', array_fill(0, count($ids), '?'));

        $whereNoSearch[] = "v_tagihan.id_data_server IN ($placeholder)";

        foreach ($ids as $id) {
            $paramsNoSearch[] = $id;
        }
    }
}

// marketing
if (!empty($idmsv) && is_numeric($idmsv)) {

    $whereNoSearch[] = "v_tagihan.id_marketing = ?";
    $paramsNoSearch[] = $idmsv;
}

// tanggal
if (!empty($startDate) && !empty($endDate)) {

    $whereNoSearch[] = "DATE(payment_winpay.created_at) BETWEEN ? AND ?";

    $paramsNoSearch[] = $startDate;
    $paramsNoSearch[] = $endDate;
}

$whereNoSearchSql = '';

if (!empty($whereNoSearch)) {
    $whereNoSearchSql = 'WHERE ' . implode(' AND ', $whereNoSearch);
}

$sqlTotal = "
SELECT COUNT(*)
$from
$whereNoSearchSql
";

$stmt = $pdo->prepare($sqlTotal);
$stmt->execute($paramsNoSearch);

$total = (int)$stmt->fetchColumn();

// =====================
// DATA
// =====================
$sql = "
SELECT
    v_tagihan.nama,
    v_tagihan.akun,
    v_tagihan.alamat,
    v_tagihan.keterangan,
    v_tagihan.nama_paket,
    v_tagihan.nama_server,
    v_tagihan.nama_marketing,
    v_tagihan.nomor_whatsapp,

    payment_winpay.id,
    payment_winpay.trx_id,
    payment_winpay.channel,
    payment_winpay.status,
    payment_winpay.created_at,
    payment_winpay.paid_at,

    CAST(payment_winpay.amount AS UNSIGNED) as amount,
    CAST(payment_winpay.fee AS UNSIGNED) as fee,
    CAST(payment_winpay.nett AS UNSIGNED) as nett

$from
$whereSql

ORDER BY payment_winpay.created_at DESC

LIMIT ?, ?
";

$stmt = $pdo->prepare($sql);

// bind semua filter
$bindIndex = 1;

foreach ($params as $param) {

    $stmt->bindValue($bindIndex++, $param);
}

// limit
$stmt->bindValue($bindIndex++, $start, PDO::PARAM_INT);
$stmt->bindValue($bindIndex++, $length, PDO::PARAM_INT);

$stmt->execute();

$data = $stmt->fetchAll(PDO::FETCH_ASSOC);

// =====================
// SUMMARY KHUSUS PAID
// =====================
$whereSummary = [];
$paramsSummary = [];

// wajib
$whereSummary[] = "v_tagihan.transaction_id IS NOT NULL";

// server
if (!empty($idsv)) {

    $ids = explode(',', $idsv);
    $ids = array_filter($ids, 'is_numeric');

    if (!empty($ids)) {

        $placeholder = implode(',', array_fill(0, count($ids), '?'));

        $whereSummary[] = "v_tagihan.id_data_server IN ($placeholder)";

        foreach ($ids as $id) {
            $paramsSummary[] = $id;
        }
    }
}

// marketing
if (!empty($idmsv) && is_numeric($idmsv)) {

    $whereSummary[] = "v_tagihan.id_marketing = ?";
    $paramsSummary[] = $idmsv;
}

// tanggal
if (!empty($startDate) && !empty($endDate)) {

    $whereSummary[] = "DATE(payment_winpay.created_at) BETWEEN ? AND ?";

    $paramsSummary[] = $startDate;
    $paramsSummary[] = $endDate;
}

// khusus PAID
$whereSummary[] = "payment_winpay.status = 'PAID'";

$whereSummarySql = 'WHERE ' . implode(' AND ', $whereSummary);

// =====================
// QUERY SUMMARY
// =====================
$sqlSummary = "
SELECT
    CAST(COALESCE(SUM(payment_winpay.amount),0) AS UNSIGNED) as total_amount,
    CAST(COALESCE(SUM(payment_winpay.fee),0) AS UNSIGNED) as total_fee,
    CAST(COALESCE(SUM(payment_winpay.nett),0) AS UNSIGNED) as total_nett

$from
$whereSummarySql
";

$stmt = $pdo->prepare($sqlSummary);
$stmt->execute($paramsSummary);

$summary = $stmt->fetch(PDO::FETCH_ASSOC);

// =====================
// OUTPUT
// =====================
echo json_encode([
    "draw" => $draw,
    "recordsTotal" => $total,
    "recordsFiltered" => $totalFiltered,
    "data" => $data,

    "summary" => [
        "total_amount" => (int)($summary['total_amount'] ?? 0),
        "total_fee"    => (int)($summary['total_fee'] ?? 0),
        "total_nett"   => (int)($summary['total_nett'] ?? 0)
    ]
]);