<?php
ob_start();

#-------------------------------------------------------------------------------
# Admin Panel Link Report
#-------------------------------------------------------------------------------

include_once '../includes/constants.php';
include_once '../includes/functions.php';
include_once '../includes/session.php';
include_once '../includes/allstripslashes.php';

$partners = new partners;
$partners->connection($host, $user, $pass, $db);
$conn = $partners->conn; // assuming 'conn' is the mysqli object

$cfrom      = trim($_POST['txtfrom']);
$cto        = trim($_POST['txtto']);
$sub        = trim($_POST['sub']);
$programs   = trim($_POST['programs']);
$MERCHANTID = trim($_POST['merchants']);
$_SESSION['LINKS'] = "";

if ($MERCHANTID === 'All') {
    $MERCHANTID = 0;
}

if (!$partners->is_date($cfrom) || !$partners->is_date($cto)) {
    $Err = "Please Enter Valid Date";
    header("location:index.php?Act=link_report&programs=$programs&merchants=$MERCHANTID&msg=$Err");
    exit;
}

$From = $partners->date2mysql($cfrom);
$To   = $partners->date2mysql($cto);

switch ($programs) {
    case 'AllPgms':
        $sql = "SELECT * FROM partners_program";
        break;
    case 'All':
        $sql = "SELECT * FROM partners_program WHERE program_merchantid = ?";
        break;
    default:
        $sql = "SELECT * FROM partners_program WHERE program_id = ?";
        break;
}

$stmt = $conn->prepare($sql);
if ($programs !== 'AllPgms') {
    $stmt->bind_param("i", $programs === 'All' ? $MERCHANTID : $programs);
}
$stmt->execute();
$result = $stmt->get_result();

while ($row = $result->fetch_object()) {
    $pgmid = $row->program_id;

    $types = [
        ['table' => 'partners_banner', 'col' => 'banner_programid', 'prefix' => 'B', 'id' => 'banner_id'],
        ['table' => 'partners_text',   'col' => 'text_programid',   'prefix' => 'T', 'id' => 'text_id'],
        ['table' => 'partners_popup',  'col' => 'popup_programid',  'prefix' => 'P', 'id' => 'popup_id'],
        ['table' => 'partners_flash',  'col' => 'flash_programid',  'prefix' => 'F', 'id' => 'flash_id'],
        ['table' => 'partners_html',   'col' => 'html_programid',   'prefix' => 'H', 'id' => 'html_id']
    ];

    foreach ($types as $type) {
        $stmt2 = $conn->prepare("SELECT * FROM {$type['table']} WHERE {$type['col']} = ?");
        $stmt2->bind_param("i", $pgmid);
        $stmt2->execute();
        $ads = $stmt2->get_result();
        while ($ad = $ads->fetch_object()) {
            $linkid = $type['prefix'] . $ad->{$type['id']};
            $total = get($conn, $From, $To, $linkid);
            $_SESSION['LINKS'] .= "^" . $total;
        }
        $stmt2->close();
    }
}

header("location:index.php?Act=link_report&sub=$sub&i=1&txtto=$cto&txtfrom=$cfrom&programs=$programs&merchants=$MERCHANTID");
exit;

function get($conn, $From, $To, $linkid)
{
    $click = $lead = $sale = $nclick = $nlead = $nsale = 0;
    $pendingamnt = $approvedamnt = $paidamnt = $rejectedamnt = $impression = 0;

    // Clicks
    $stmt = $conn->prepare("SELECT transaction_amttobepaid, transaction_admin_amount FROM partners_transaction WHERE transaction_dateoftransaction BETWEEN ? AND ? AND transaction_type='click' AND transaction_linkid=?");
    $stmt->bind_param("sss", $From, $To, $linkid);
    $stmt->execute();
    $result = $stmt->get_result();
    $nclick += $result->num_rows;
    while ($row = $result->fetch_object()) {
        $click += $row->transaction_amttobepaid + $row->transaction_admin_amount;
    }
    $stmt->close();

    // Leads
    $stmt = $conn->prepare("SELECT transaction_amttobepaid, transaction_admin_amount FROM partners_transaction WHERE transaction_dateoftransaction BETWEEN ? AND ? AND transaction_type='lead' AND transaction_linkid=?");
    $stmt->bind_param("sss", $From, $To, $linkid);
    $stmt->execute();
    $result = $stmt->get_result();
    $nlead += $result->num_rows;
    while ($row = $result->fetch_object()) {
        $lead += $row->transaction_amttobepaid + $row->transaction_admin_amount;
    }
    $stmt->close();

    // Sales
    $stmt = $conn->prepare("SELECT * FROM partners_transaction WHERE transaction_dateoftransaction BETWEEN ? AND ? AND transaction_type='sale' AND transaction_linkid=?");
    $stmt->bind_param("sss", $From, $To, $linkid);
    $stmt->execute();
    $result = $stmt->get_result();
    $nsale += $result->num_rows;

    while ($row = $result->fetch_object()) {
        $transactionId = $row->transaction_id;
        $recur = $row->transaction_recur;

        if ($recur == '1') {
            $stmt2 = $conn->prepare("SELECT recur_id FROM partners_recur WHERE recur_transactionid = ?");
            $stmt2->bind_param("i", $transactionId);
            $stmt2->execute();
            $res2 = $stmt2->get_result();
            if ($rec = $res2->fetch_object()) {
                $recurId = $rec->recur_id;
                $stmt3 = $conn->prepare("SELECT recurpayments_amount FROM partners_recurpayments WHERE recurpayments_recurid = ?");
                $stmt3->bind_param("i", $recurId);
                $stmt3->execute();
                $res3 = $stmt3->get_result();
                if ($rp = $res3->fetch_object()) {
                    $sale += $rp->recurpayments_amount;
                }
                $stmt3->close();
            }
            $stmt2->close();
        } else {
            $sale += $row->transaction_amttobepaid;
        }
        $sale += $row->transaction_admin_amount;
    }
    $stmt->close();

    // Approved
    $stmt = $conn->prepare("SELECT * FROM partners_transaction WHERE transaction_dateoftransaction BETWEEN ? AND ? AND transaction_status='approved' AND transaction_linkid=?");
    $stmt->bind_param("sss", $From, $To, $linkid);
    $stmt->execute();
    $result4 = $stmt->get_result();

    while ($row1 = $result4->fetch_object()) {
        $transactionId = $row1->transaction_id;
        $recur = $row1->transaction_recur;

        if ($recur == '1') {
            $stmt2 = $conn->prepare("SELECT recur_id FROM partners_recur WHERE recur_transactionid = ?");
            $stmt2->bind_param("i", $transactionId);
            $stmt2->execute();
            $res2 = $stmt2->get_result();
            if ($rec = $res2->fetch_object()) {
                $recurId = $rec->recur_id;
                $stmt3 = $conn->prepare("SELECT recurpayments_amount FROM partners_recurpayments WHERE recurpayments_recurid = ? AND recurpayments_status = 'approved'");
                $stmt3->bind_param("i", $recurId);
                $stmt3->execute();
                $res3 = $stmt3->get_result();
                if ($rp = $res3->fetch_object()) {
                    $approvedamnt += $rp->recurpayments_amount;
                }
                $stmt3->close();
            }
            $stmt2->close();
        } else {
            $approvedamnt += $row1->transaction_amttobepaid + $row1->transaction_admin_amount;
        }
    }
    $stmt->close();

    // Just returning sale here, can change based on expected return value
    return $sale;
}

						        $approvedamnt = $row1->transaction_amttobepaid + $approvedamnt;
        $approvedamnt = $row1->transaction_admin_amount + $approvedamnt;
    } // end while


    // Paid Amount (including recurring payments)
    $sql = "SELECT * FROM partners_transaction WHERE transaction_dateoftransaction BETWEEN ? AND ? AND transaction_linkid = ?";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("ssi", $From, $To, $linkid);
    $stmt->execute();
    $result4 = $stmt->get_result();
    while ($row1 = $result4->fetch_object()) {
        $transactionId = $row1->transaction_id;
        $recur = $row1->transaction_recur;

        if ($recur == '1') {
            $sqlRecur = "SELECT * FROM partners_recur WHERE recur_transactionid = ?";
            $stmtRecur = $conn->prepare($sqlRecur);
            $stmtRecur->bind_param("i", $transactionId);
            $stmtRecur->execute();
            $resRecur = $stmtRecur->get_result();
            if ($resRecur->num_rows > 0) {
                $rowRecur = $resRecur->fetch_object();
                $recurId = $rowRecur->recur_id;

                $sqlRecurPay = "SELECT * FROM partners_recurpayments WHERE recurpayments_recurid = ?";
                $stmtRecurPay = $conn->prepare($sqlRecurPay);
                $stmtRecurPay->bind_param("i", $recurId);
                $stmtRecurPay->execute();
                $resRecurPay = $stmtRecurPay->get_result();
                if ($resRecurPay->num_rows > 0) {
                    $rowRecurPay = $resRecurPay->fetch_object();
                    $paidamnt = $rowRecurPay->recurpayments_amount + $paidamnt;
                }
            }
        } else {
            $paidamnt = $row1->transaction_amountpaid + $paidamnt;
        }

        $paidamnt = $row1->transaction_adminpaid + $paidamnt;
    }

    // Rejected Amount (normal)
    $sql = "SELECT * FROM partners_transaction WHERE transaction_dateoftransaction BETWEEN ? AND ? AND transaction_status='reversed' AND transaction_linkid=?";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("ssi", $From, $To, $linkid);
    $stmt->execute();
    $result4 = $stmt->get_result();
    while ($row1 = $result4->fetch_object()) {
        $rejectedamnt = $row1->transaction_amttobepaid + $rejectedamnt;
    }

    // Rejected Amount (recurring)
    $sql_rev = "SELECT recurpayments_amount FROM partners_transaction 
        JOIN partners_recur ON recur_transactionid = transaction_id
        JOIN partners_recurpayments ON recurpayments_recurid = recur_id
        WHERE transaction_linkid = ? AND transaction_dateoftransaction BETWEEN ? AND ? 
        AND recurpayments_status = 'reversed'";
    $stmt = $conn->prepare($sql_rev);
    $stmt->bind_param("iss", $linkid, $From, $To);
    $stmt->execute();
    $res_rev = $stmt->get_result();
    while ($row_rev = $res_rev->fetch_object()) {
        $rejectedamnt = $row_rev->recurpayments_amount + $rejectedamnt;
    }

    // Pending Amount
    $sql = "SELECT * FROM partners_transaction WHERE transaction_dateoftransaction BETWEEN ? AND ? AND transaction_status='pending' AND transaction_linkid=?";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("ssi", $From, $To, $linkid);
    $stmt->execute();
    $result4 = $stmt->get_result();
    while ($row1 = $result4->fetch_object()) {
        $transactionId = $row1->transaction_id;
        $recur = $row1->transaction_recur;

        if ($recur == '1') {
            $sqlRecur = "SELECT * FROM partners_recur WHERE recur_transactionid = ?";
            $stmtRecur = $conn->prepare($sqlRecur);
            $stmtRecur->bind_param("i", $transactionId);
            $stmtRecur->execute();
            $resRecur = $stmtRecur->get_result();
            if ($resRecur->num_rows > 0) {
                $rowRecur = $resRecur->fetch_object();
                $recurId = $rowRecur->recur_id;

                $sqlRecurPay = "SELECT * FROM partners_recurpayments WHERE recurpayments_recurid = ? AND recurpayments_status = 'pending'";
                $stmtRecurPay = $conn->prepare($sqlRecurPay);
                $stmtRecurPay->bind_param("i", $recurId);
                $stmtRecurPay->execute();
                $resRecurPay = $stmtRecurPay->get_result();
                if ($resRecurPay->num_rows > 0) {
                    $rowRecurPay = $resRecurPay->fetch_object();
                    $pendingamnt = $rowRecurPay->recurpayments_amount + $pendingamnt;
                }
            }
        } else {
            $pendingamnt = $row1->transaction_amttobepaid + $pendingamnt;
        }

        $pendingamnt = $row1->transaction_admin_amount + $pendingamnt;
    }

    // Impression Amount
    $sql = "SELECT * FROM partners_transaction WHERE transaction_dateoftransaction BETWEEN ? AND ? AND transaction_type='impression' AND transaction_linkid=?";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("ssi", $From, $To, $linkid);
    $stmt->execute();
    $result = $stmt->get_result();
    while ($row = $result->fetch_object()) {
        $imp_amt = $row->transaction_amttobepaid + $row->transaction_admin_amount;
        $impression = $imp_amt + $impression;
    }

    $total = $click . "~" . $nclick . "~" . $lead . "~" . $nlead . "~" . $sale . "~" . $nsale . "~" . $linkid . "~" . $approvedamnt . "~" . $pendingamnt . "~" . $paidamnt . "~" . $rejectedamnt . "~" . $impression;
    return $total;
}
?>
