<?php
/*This class contains all functions related to admin affiliate programs*/
class Admin_Affiliate_Reports_Model extends CI_Model
{

    //constructor
    public function __construct()
    {
        parent::__construct();
    }

    function getAllAffiliatesSalesReports($admin_user_id)
    {
        if(empty($admin_user_id))
        {
            return '<div style="width: 100%;" class="rounded_border">
                        <div class="top"><span class="round_left s4X4"></span><span class="round_right s4X4"></span></div>
                        <div class="body">
                            <table cellspacing="0" cellpadding="0" border="0" class="round_table">
                                <tr>
                                    <th align="left">Affiliate ID</th>
                                    <th align="left">Affiliate Name</th>
                                    <th align="left">Subscription Type</th>
                                    <th align="left">Sales ' . $_SESSION['currency_symbol'] . '</th>
                                </tr>
                                <tr>
                                    <td colspan="4">No record found</td>
                                </tr>
                            </table>
                        </div>
                    </div>';
        }

        $sql1 =$this->getAdminAffiliateReportSql($admin_user_id);
        $sql = '('. $sql1 . ') as abc' ;

        $this->load->library ( 'rapyd' );
        $this->rapyd->load('datagrid');
        $grid = new DataGrid('', $sql);

        $grid->per_page = $this->config->item('rows_per_page');
        $grid->column('Affiliate ID', 'affiliate_id','align="left"');
        $grid->column_orderby('Affiliate Name', 'affiliate_name', 'affiliate_name', 'align="left"');
        $grid->column_orderby('Sales ' . $_SESSION['currency_symbol'], 'affiliate_sales_commission', 'affiliate_sales_commission', 'align="left"');
        $grid->build();

        return $grid->output;
    }

    function getAdminAffiliateReportSql($admin_user_id, $cond='')
    {
        $sql = 'SELECT ad.admin_affiliate_row_id,
                        ad.affiliate_id,
                        ad.affiliate_name,
                        ROUND(IFNULL(
                            (SELECT SUM(sales_commission)
                                FROM tbl_admin_sales_commission
                                WHERE admin_affiliate_row_id = ad.admin_affiliate_row_id
                                AND admin_user_id = ' . $admin_user_id . '
                                AND payment_status = 0
                                AND is_cancelled  = 0), 0.0000
                        ), 2) AS affiliate_sales_commission
                        FROM tbl_admin_affiliate ad
                        WHERE ad.admin_user_id = ' . $admin_user_id . '
                AND ad.join_status = 2
                ' . $cond . '
                GROUP BY ad.admin_affiliate_row_id,
                         ad.affiliate_name,
                         ad.affiliate_id,
                         affiliate_sales_commission
                ORDER BY affiliate_sales_commission DESC';
        return $sql;

    }
    function packageSalesCommissionReport($admin_affiliate_row_id , $admin_user_id, $condition)
    {
        $admin_affiliate_row_id = (int) $admin_affiliate_row_id;
        $admin_user_id = (int) $admin_user_id;

		if(empty($admin_affiliate_row_id) || empty($admin_user_id))
        {
            return '<div style="width: 100%;" class="rounded_border">
                        <div class="top"><span class="round_left s4X4"></span><span class="round_right s4X4"></span></div>
                        <div class="body">
                            <table cellspacing="0" cellpadding="0" border="0" class="round_table">
                                <tr>
                                    <th align="left">Date</th>
                                    <th align="left">Merchant Name</th>
                                    <th align="left">Package Name</th>
                                    <th align="left">Subscription Type</th>
                                    <th align="left">Sales Commission</th>
                                    <th align="left">Date Paid</th>
                                </tr>
                                <tr>
                                    <td colspan="6">No record found</td>
                                </tr>
                            </table>
                        </div>
                    </div>';
        }

       $sql = 'SELECT DATE_FORMAT(sc.created_on,\'' .'%Y-%m-%d'. '\') AS order_date,
                       p.package_name,
                       CASE sc.subscription_type
                            WHEN \'0\' THEN \'Trial\'
                            WHEN \'' . $this->config->item('monthly_subcription') . '\' THEN  \'Monthly\'
                            WHEN \'' . $this->config->item('annual_subcription') . '\' THEN  \'Annual\'
                       END AS subscription_type,
                       CASE sc.payment_status
                            WHEN \'0\' THEN \'Pending\'
                            ELSE DATE_FORMAT(sc.paid_on,\'' .'%M-%d, %y'. '\')
                       END AS paid_on,
                       CASE sc.payment_status
                       WHEN \'2\' THEN \'Refunded\'
                       ELSE sc.sales_commission
                       END AS sales_commission,
                       CONCAT(m.first_name,m.last_name)as merchant_name
                FROM tbl_admin_sales_commission sc
                INNER JOIN tbl_package p ON p.package_id = sc.package_id
                INNER JOIN tbl_merchant m ON m.merchant_row_id = sc.merchant_row_id
                WHERE sc.admin_user_id = '.$admin_user_id.'
                AND sc.admin_affiliate_row_id = '.$admin_affiliate_row_id.
                $condition.'
                AND sc.is_cancelled = 0';

       $sql = '('. $sql . ') as abc' ;

        $this->load->library ( 'rapyd' );
        $this->rapyd->load('datagrid');
        $grid = new DataGrid('', $sql);
        $grid->order_by('order_date', 'desc');
        $grid->per_page = 25;
        $grid->column_orderby('Date', 'order_date','order_date','align=\'left\'','style="text-align:left;"');
        $grid->column_orderby('Merchant Name', 'merchant_name','merchant_name','style="text-align:left;"');
        $grid->column('Package Name', 'package_name','style="text-align:left;"');
        $grid->column('Subscription Type', 'subscription_type','style="text-align:left;"');
        $grid->column('Sales Commission', 'sales_commission','style="text-align:left;"');
        $grid->column('Date Paid', 'paid_on','style="text-align:left;"');
        $grid->build();
        $output = $grid->output;
        return $output;
    }
}
?>