<?php
include '../../sample-wp/wp-config.php';

$GRP_ONLINE_TRANSACTIONS = 'grp_online_txn_main';
$GRP_AGENTS = 'grp_agents';

## Read value
$draw = $_POST['draw'];
$row = $_POST['start'];
$rowperpage = $_POST['length']; // Rows display per page
$columnIndex = $_POST['order'][0]['column']; // Column index
$columnName = $_POST['columns'][$columnIndex]['data']; // Column name
$columnSortOrder = $_POST['order'][0]['dir']; // asc or desc
$searchValue = $_POST['search']['value']; // Search value
$franchiseID = $_REQUEST['fID'];

## Search 
$searchQuery = " ";
if ($searchValue != '') {
    $searchQuery = " AND (o.txn_type LIKE '%" . $searchValue . "%' OR a.name LIKE '%" . $searchValue . "%' OR a.agent_code LIKE '%" . $searchValue . "%' OR o.transaction_id LIKE '%" . $searchValue . "%' OR o.amount LIKE '%" . $searchValue . "%' )";
}

## Total number of records without filtering
$records = $wpdb->get_row("SELECT COUNT(o.id) AS allcount FROM $GRP_ONLINE_TRANSACTIONS AS o LEFT JOIN $GRP_AGENTS AS a ON o.agent_id=a.agent_id WHERE a.mf_id='$franchiseID'");
$totalRecords = $records->allcount;

## Total number of records with filtering
$records = $wpdb->get_row("SELECT COUNT(id) AS allcount FROM $GRP_ONLINE_TRANSACTIONS AS o LEFT JOIN $GRP_AGENTS AS a ON o.agent_id=a.agent_id WHERE a.mf_id='$franchiseID' $searchQuery");
$totalRecordwithFilter = $records->allcount;

## Fetch records
$tranxnQuery = "SELECT o.id, o.txn_type, o.agent_id, o.amount, o.payment_gateway, o.transaction_id, o.payment_txn_id, o.payment_order_id, o.txn_status, o.txn_msg, o.txn_date, a.name, a.agent_code FROM $GRP_ONLINE_TRANSACTIONS AS o LEFT JOIN $GRP_AGENTS AS a ON o.agent_id=a.agent_id WHERE a.mf_id='$franchiseID' $searchQuery ORDER BY o.txn_date $columnSortOrder LIMIT $row, $rowperpage";
$txnRecords = $wpdb->get_results($tranxnQuery);

$data = array();
    $i = $row;
    foreach ($txnRecords as $txnrow) {
    $i++;  
    // Format the transaction date
    $transactionDate = date('d M Y', strtotime($txnrow->txn_date));
    $data[] = array(
        "id" => $i,
        "agentname" => $txnrow->name,
        "agentcode" => $txnrow->agent_code,
        "amount" => $txnrow->amount,
        "gateway" => $txnrow->payment_gateway,
        "transaction" => $txnrow->transaction_id,
        "transactiondate" => $transactionDate
    );  
}

$response = array(
    "draw" => intval($draw),
    "iTotalRecords" => $totalRecords,
    "iTotalDisplayRecords" => $totalRecordwithFilter,
    "aaData" => $data
);
echo json_encode($response);

?>