<?php
/**************************************************************************/
/*     AUTHOR         :  PHP Dev Team, iFactor Solutions, India           */
/*     PROGRAMMER     :  SMA                                              */
/*     SCRIPT NAME    :  affiliateReferral_details.php                    */
/*     CREATED ON     :  19/AUG/2009                                      */
/*     LAST MODIFIED  :  20/AUG/2025 (by modernization)                   */
/*     DESCRIPTION    :  Detailed Referral Report for an Affiliate        */
/**************************************************************************/

require_once "db_connect.php"; // assumes $mysqli is defined here

$affiliate = isset($_REQUEST['affiliate']) ? intval($_REQUEST['affiliate']) : 0;
$From = isset($_REQUEST['From']) ? trim($_REQUEST['From']) : '';
$To = isset($_REQUEST['To']) ? trim($_REQUEST['To']) : '';

/******** validation ***************************************************/
if (!$partners->is_date_Mysql($From) || !$partners->is_date_Mysql($To) || empty($affiliate)) {
    $msg = "Invalid Request";
    ?>
    <table width="85%" align="center">
        <tr>
            <td height="40" colspan="2" class="error" align="center" valign="middle"><?= htmlspecialchars($msg) ?></td>
        </tr>
    </table>
    <?php
} else {
    $From_esc = $mysqli->real_escape_string($From);
    $To_esc = $mysqli->real_escape_string($To);

    $sql_referral = "
        SELECT COUNT(subsale_id) AS Cnt, 
               SUM(subsale_amount) AS Sum, 
               affiliate_company, 
               affiliate_id 
        FROM partners_transaction_subsale 
        JOIN partners_affiliate 
          ON affiliate_id = subsale_childaffiliateid 
        WHERE subsale_affiliateid = $affiliate 
          AND subsale_date >= '$From_esc' 
          AND subsale_date <= '$To_esc' 
        GROUP BY subsale_childaffiliateid
    ";

    $res_referral = $mysqli->query($sql_referral);

    if ($res_referral && $res_referral->num_rows > 0) {
        $sql_aff = "SELECT affiliate_company FROM partners_affiliate WHERE affiliate_id = $affiliate LIMIT 1";
        $res_aff = $mysqli->query($sql_aff);
        $affiliateName = "Affiliate";
        if ($res_aff && $res_aff->num_rows > 0) {
            list($affiliateName) = $res_aff->fetch_row();
        }
        ?>
        <script type="text/javascript">
            function help(pageurl) {
                window.open(pageurl, 'new', 'height=450,width=450,scrollbars=yes');
            }
        </script>

        <p align="center">
            <b>Referral Commission Report for Affiliate <?= htmlspecialchars($affiliateName) ?> from <?= htmlspecialchars($From) ?> to <?= htmlspecialchars($To) ?></b>
        </p>

        <table border="0" cellpadding="0" cellspacing="0" width="75%" class="tablebdr" align="center">
            <tr>
                <td width="45%" class="tdhead">&nbsp;<b>Affiliates</b></td>
                <td width="18%" class="tdhead"><b>Referral Sales</b></td>
                <td width="22%" class="tdhead"><b>Commission Earned</b></td>
            </tr>
            <?php
            $total = 0;
            while ($row = $res_referral->fetch_object()) {
                $total += $row->Sum;
                ?>
                <tr>
                    <td height="25">&nbsp;
                        <a href="#" onclick="help('viewprofile_affiliate.php?id=<?= (int)$row->affiliate_id ?>');">
                            <?= htmlspecialchars($row->affiliate_company) ?>
                        </a>
                    </td>
                    <td height="25"><?= (int)$row->Cnt ?></td>
                    <td height="25">$<?= number_format((float)$row->Sum, 2) ?></td>
                </tr>
                <?php
            }
            ?>
            <tr>
                <td colspan="2" align="left" class="tdhead">&nbsp;<b>Total Commission</b></td>
                <td colspan="2" align="left" class="tdhead"><b>$<?= number_format($total, 2) ?></b></td>
            </tr>
        </table>
        <?php
    } else {
        ?>
        <table width="85%" align="center">
            <tr>
                <td height="40" colspan="2" class="error" align="center" valign="middle">No Records Found</td>
            </tr>
        </table>
        <?php
    }
}
?>

<br/>
<p align="center">
    <input type="button" name="Back" value="Back" onclick="javascript: history.go(-1);" />
</p>
<br/>
