<?php
/**
 * This model holds the functions related to paying  affiliate commissions
 * @author Tania Noor
 *
 */

class Pay_Commission_model extends CI_Model
{

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

    /**
    * @author Tania Noor
    * @abstract This function return affiliate wise commission
    * @param $paymentPreference int 1 for PayPal, 2 for Check
    * @Edit version PWC v1.2
    */
    function getAffiliateWiseCommission($admin_user_id, $paymentPreference,$conditions = '',$itemPerPage)
    {

        if($itemPerPage)
        {
            $item_per_page = $itemPerPage;
        }
        else
        {
            $item_per_page = $this->config->item('rows_per_page');
        }
        $payment_preference = '(a.payment_preference = '.$paymentPreference.' OR a.payment_preference = 3 )';

        $sql = $this->payCommissionSQL($admin_user_id, $payment_preference ,$conditions, $paymentPreference);

        $_SESSION['commission_report_sql']= $sql;
        $sql = '('.$sql.') AS abc';
        $this->load->library ( 'rapyd' );
        $this->rapyd->load('datagrid');
        $grid = new DataGrid('', $sql);

        $grid->per_page = $item_per_page;
        $grid->column_orderby('Affiliate Name', 'aff_name','aff_name','align="left"');
        $grid->column('Affiliate ID', 'affiliate_id','align=\'left\' width=200px');
        $grid->column_orderby('Commission Due', getCurrencySymbol().'<#commission#>','commission','align=\'left\'');
        $grid->column_detail('', 'Pay','administration/admin_pay_commission/showCommissionDetails/'.$paymentPreference.'/<#admin_affiliate_row_id#>/<#commission_type_value#>', 'align=\'left\' width=40px', false , '',true,'');

        $grid->build();

        return $grid->output;
    }

    /**
    * @desc sql for pay commission module
    * @author Mohammad Sajjad Hossain
    */

    function payCommissionSQL($admin_user_id, $payment_preference, $conditions = '', $paymentPreference)
    {
       $sql = "(SELECT  a.admin_affiliate_row_id,
                        a.affiliate_name as aff_name,
                        a.affiliate_id,
                        a.affiliate_paypal_email as aff_paypal_email,
                        a.affiliate_name_on_check as aff_name_on_check,
                        a.admin_user_id,
                        \"Sales\" as commission_type,
                        ".$this->config->item('sales_commission')." as commission_type_value,
                        CONCAT(a.admin_affiliate_row_id,'+',a.affiliate_paypal_email) as selectValPaypal,
                        CONCAT(a.admin_affiliate_row_id,'+',a.affiliate_name_on_check) as selectValCheck,
                        CONCAT(a.affiliate_name_on_check,'_','1') as bankName,
                        CONCAT(a.affiliate_name_on_check,'_','2') as checkNo,
                        CASE a.payment_preference
                            WHEN 1 THEN 'PayPal'
                            WHEN 2 THEN 'Check'
                            WHEN 3 THEN 'Check or PayPal'
                        END AS payment_preference,
                        ROUND(IFNULL(
                            (SELECT SUM(sales_commission)
                                FROM tbl_admin_sales_commission
                                WHERE admin_affiliate_row_id = a.admin_affiliate_row_id
                                AND admin_user_id = $admin_user_id
                                AND payment_status = 0
                                AND is_cancelled  = 0), 0.0000
                        ),2) AS commission
                FROM
                tbl_admin_affiliate a
                WHERE a.admin_user_id = $admin_user_id";

        if($conditions)
        {
            $sql .= ' AND ' . $conditions;
        }

        $sql .= "
                AND (
                        a.admin_affiliate_row_id
                        NOT IN (
                        SELECT tpc.affiliate_row_id
                        FROM tbl_admin_pay_commission_temp tpc
                        WHERE tpc.admin_user_id=$admin_user_id
                        AND tpc.payment_type=$paymentPreference
                         AND tpc.commission_type=".$this->config->item('sales_commission').")
                        )

                AND (
                    (SELECT SUM(sales_commission)
                                FROM tbl_admin_sales_commission
                                WHERE admin_affiliate_row_id = a.admin_affiliate_row_id
                                AND admin_user_id = $admin_user_id
                                AND payment_status = 0
                                AND is_cancelled  = 0) > 0)
                GROUP BY a.admin_affiliate_row_id,
                         a.affiliate_name,
                         a.affiliate_id,
                         payment_preference,
                         commission )";

        $sql .= " ORDER BY commission DESC, aff_name ASC";
        return $sql;
    }

    function totalNonSalesCommission($admin_user_id, $affiliate_row_id)
    {
        $sql =" SELECT COUNT(nsc.non_sales_commision_count_row_id) AS total_count,
                ROUND(SUM(
                        IFNULL(nsc.due_impression_commission,0.00)
                      + IFNULL(nsc.due_click_commission,0.00)
                      + IFNULL(nsc.due_lead_commission,0.00)
                      ), 2) AS total_commission,
                ROUND(SUM(
                        IFNULL(nsc.due_impression_commission,0.00)
                      ), 2) AS imp_commission,
                ROUND(SUM(
                        IFNULL(nsc.due_click_commission,0.00)
                      ), 2) AS clk_commission,
                ROUND(SUM(
                        IFNULL(nsc.due_lead_commission,0.00)
                      ), 2) AS lead_commission
                FROM tbl_non_sales_commission_count nsc
                WHERE nsc.admin_user_id = $admin_user_id
                AND nsc.affiliate_row_id=$affiliate_row_id
                 GROUP BY nsc.affiliate_row_id
                ";
       $result = $this->db->query($sql);
       return $result->row_array();
    }

    function getAffiliateNonSalesCommissionDetails($admin_user_id, $affiliate_row_id, $offset, $purpose=0)
    {
        $sql=" SELECT nsc.non_sales_commision_count_row_id,
                      nsc.affiliate_row_id,
                      nsc.merchant_aff_program_row_id,
                      ROUND( IFNULL(nsc.due_impression_commission,0.00), 2 ) AS total_imp_commission ,
                      ROUND( IFNULL(nsc.due_click_commission,0.00), 2 ) AS total_clk_commission ,
                      ROUND( IFNULL(nsc.due_lead_commission,0.00), 2 )  AS total_lead_commission,
                      rates. aff_program
                FROM tbl_non_sales_commission_count nsc
                INNER JOIN
                (
                    SELECT ma. program_name AS  aff_program ,
                            ma.merchant_aff_program_row_id AS aff_program_id
                     FROM tbl_merchant_aff_program ma
                     WHERE ma.admin_user_id = $admin_user_id

                ) rates
                ON rates.aff_program_id = nsc.merchant_aff_program_row_id
                WHERE nsc.admin_user_id = $admin_user_id
                AND nsc.affiliate_row_id=$affiliate_row_id
                AND (ROUND( IFNULL(nsc.due_impression_commission,0.00), 2 )>0.00
                OR ROUND( IFNULL(nsc.due_click_commission,0.00), 2 )>0.00
                OR ROUND( IFNULL(nsc.due_lead_commission,0.00), 2 )>0.00)
                 GROUP BY nsc.non_sales_commision_count_row_id
                 ORDER BY total_lead_commission DESC, total_clk_commission DESC, total_imp_commission DESC";

       if(!$purpose)
       {
            $sql .= "  LIMIT ".$offset.", ".$this->config->item('rows_per_page')."   ";
       }

       $result = $this->db->query($sql);
       return $result->result_array();
    }

    function adjustNonSalesCommissionByAffiliateRowId($admin_user_id, $affiliate_row_id, $commissionAmount)
    {
        if(empty($admin_user_id) && empty($affiliate_row_id))
        {
            return false;
        }

        $totalCommission = $this->totalNonSalesCommission($admin_user_id, $affiliate_row_id);
        $adjustedCommission = $commissionAmount;
        $returnArray = array();
        if($totalCommission['total_commission'] == $commissionAmount)
        {
            //update all non sales commission fields for that affiliate
            $sql = 'UPDATE tbl_non_sales_commission_count
                     SET paid_impression_commission=due_impression_commission,
                         paid_click_commission=due_click_commission,
                         paid_lead_commission=due_lead_commission,
                         due_impression_commission=0.00,
                         due_click_commission=0.00,
                         due_lead_commission=0.00
                     WHERE admin_user_id= ' . $admin_user_id . '
                     AND affiliate_row_id= ' . $affiliate_row_id;
            $this->db->query($sql);
            $returnArray['imp_commission'] = $totalCommission['imp_commission'];
            $returnArray['clk_commission'] = $totalCommission['clk_commission'];
            $returnArray['lead_commission'] = $totalCommission['lead_commission'];
            return $returnArray;

        }
        else
        {
            $commissionArray = $this->getAffiliateNonSalesCommissionDetails($admin_user_id, $affiliate_row_id, 0, 1);
            foreach($commissionArray as $aCommission)
            {
                $updatedCommission = array();
                $updatedCommission['non_sales_commision_count_row_id'] =$aCommission['non_sales_commision_count_row_id'];

                $totalNonSalesCommission = $aCommission['total_lead_commission']+$aCommission['total_clk_commission']+$aCommission['total_imp_commission'];

                if(($totalNonSalesCommission < $adjustedCommission) && $adjustedCommission > 0)
                {
                    $updatedCommission['due_impression_commission'] =0.00;
                    $updatedCommission['due_click_commission'] =0.00;
                    $updatedCommission['due_lead_commission'] =0.00;
                    $updatedCommission['paid_impression_commission'] = $aCommission['total_imp_commission'];
                    $updatedCommission['paid_click_commission'] = $aCommission['total_clk_commission'];
                    $updatedCommission['paid_lead_commission'] = $aCommission['total_lead_commission'];
                    $returnArray['imp_commission'] += $aCommission['total_imp_commission'];
                    $returnArray['clk_commission'] += $aCommission['total_clk_commission'];
                    $returnArray['lead_commission'] += $aCommission['total_lead_commission'];
                    $adjustedCommission -= $totalNonSalesCommission;
                }
                else
                {
                    if(($aCommission['total_lead_commission'] - $adjustedCommission)>=0 && $adjustedCommission > 0)
                    {
                        $returnArray['lead_commission'] +=  $adjustedCommission;
                        $updatedCommission['due_lead_commission'] = $aCommission['total_lead_commission'] - $adjustedCommission;
                        $updatedCommission['paid_lead_commission'] = $adjustedCommission;
                        $adjustedCommission = 0;
                    }
                    else
                    {
                        if($aCommission['total_lead_commission']<$adjustedCommission && $adjustedCommission > 0)
                        {
                            $returnArray['lead_commission'] +=  $aCommission['total_lead_commission'];
                            $updatedCommission['due_lead_commission'] = 0.00;
                            $updatedCommission['paid_lead_commission'] = $aCommission['total_lead_commission'];
                        }
                        $adjustedCommission -= $aCommission['total_lead_commission'] ;
                    }

                    if(($aCommission['total_clk_commission'] - $adjustedCommission)>=0 && $adjustedCommission > 0)
                    {
                        $returnArray['clk_commission'] +=  $adjustedCommission;
                        $updatedCommission['due_click_commission'] = $aCommission['total_clk_commission'] - $adjustedCommission;
                        $updatedCommission['paid_click_commission'] = $adjustedCommission;
                        $adjustedCommission = 0;
                    }
                    else
                    {
                        if($aCommission['total_clk_commission']<$adjustedCommission && $adjustedCommission > 0)
                        {
                            $returnArray['clk_commission'] +=  $aCommission['total_clk_commission'];
                            $updatedCommission['due_click_commission'] = 0.00;
                            $updatedCommission['paid_click_commission'] = $aCommission['total_clk_commission'];
                        }
                        $adjustedCommission -= $aCommission['total_clk_commission'] ;
                    }

                    if(($aCommission['total_imp_commission'] - $adjustedCommission)>=0 && $adjustedCommission > 0)
                    {
                        $returnArray['imp_commission'] +=  $adjustedCommission;
                        $updatedCommission['due_impression_commission'] = $aCommission['total_imp_commission'] - $adjustedCommission;
                        $updatedCommission['paid_impression_commission'] = $adjustedCommission;
                        $adjustedCommission = 0;
                    }
                    else
                    {
                        if($aCommission['total_imp_commission']<$adjustedCommission && $adjustedCommission > 0)
                        {
                            $returnArray['imp_commission'] +=  $aCommission['total_imp_commission'];
                            $updatedCommission['due_impression_commission'] = 0.00;
                            $updatedCommission['paid_impression_commission'] = $aCommission['total_imp_commission'];
                        }
                        $adjustedCommission -= $aCommission['total_imp_commission'] ;
                    }

                }
                if($adjustedCommission <= 0)
                {
                    $this->db->where('non_sales_commision_count_row_id', $updatedCommission['non_sales_commision_count_row_id']);
                    $this->db->update('tbl_non_sales_commission_count', $updatedCommission);
                    break;
                }
                else
                {
                    $this->db->where('non_sales_commision_count_row_id', $updatedCommission['non_sales_commision_count_row_id']);
                    $this->db->update('tbl_non_sales_commission_count', $updatedCommission);
                }
            }
            return $returnArray;
        }
    }

     /**
    * @desc total sales commission for an affiliate
    * @param $affiliate_row_id int
    */
    function totalSalesCommission($admin_affiliate_row_id)
    {
       $sql = 'SELECT COUNT(sc.sales_commission) AS total_rows,IFNULL(SUM(sc.sales_commission), 0) AS total_commission
                FROM tbl_admin_sales_commission sc
                WHERE sc.admin_affiliate_row_id = ' . $admin_affiliate_row_id . '
                AND (sc.payment_status = 0 or sc.payment_status = 2)
                AND sc.is_cancelled =0
                AND sc.sales_commission > 0';

        $res = $this->db->query($sql);
        $result = $res->row_array();
        return $result;
    }

    function totalSalesCommissionOnlyToPay($admin_affiliate_row_id)
    {
       $sql = 'SELECT COUNT(sc.sales_commission) AS total_rows,IFNULL(SUM(sc.sales_commission), 0) AS total_commission
                FROM tbl_admin_sales_commission sc
                WHERE sc.admin_affiliate_row_id = ' . $admin_affiliate_row_id . '
                AND sc.payment_status = 0
                AND sc.is_cancelled =0
                AND sc.sales_commission > 0';

        $res = $this->db->query($sql);
        $result = $res->row()->total_commission;
        return $result;
    }



     /**
    * @desc list of unpaid sales commission for an affiliate
    * @param $affiliate_row_id int
    * @param $startIndex int
    * @param $rowPerPage int
    */
    function getAffiliateSalesCommissionDetails($admin_affiliate_row_id, $startIndex, $rowPerPage, $order_by_string, $order_type_default)
    {
     $sql = 'SELECT  sc.admin_sales_commission_row_id,sc.sales_commission,DATE_FORMAT(sc.created_on,"%m-%d-%Y") AS created_on,sc.payment_status,
                m.first_name,
                m.last_name,
                m.merchant_id
             FROM tbl_admin_sales_commission sc
             INNER JOIN tbl_merchant m ON m.merchant_row_id = sc.merchant_row_id
             WHERE sc.admin_affiliate_row_id = ' . $admin_affiliate_row_id . '
             AND (sc.payment_status = 0 or sc.payment_status = 2)
             AND sc.is_cancelled =0
             AND sc.sales_commission > 0
             ORDER BY '.$order_by_string .' '. $order_type_default.'
             LIMIT ' . $startIndex . ', ' . $rowPerPage;
        $res = $this->db->query($sql);
        $result = $res->result_array();
        return $result;
    }



    function saveAffiliateTempList($data)
    {

       $selectedAffiliate = $this->getSelectedAffiliateCommissionInfo($data['admin_user_id'], $data['affiliate_row_id'], $data['payment_type'], $data['commission_type']);


        if($selectedAffiliate)
        {

           if($data['commission_amount'] > 0.00)
            {
                $this->db->where('admin_user_id', $data['admin_user_id']);
                $this->db->where('affiliate_row_id', $data['affiliate_row_id']);
                $this->db->where('payment_type', $data['payment_type']);
                $this->db->where('commission_type', $data['commission_type']);
                $this->db->update('tbl_admin_pay_commission_temp', $data);
            }
            else
            {
                $this->deleteSelectedAffiliate($data['admin_user_id'], $data['affiliate_row_id'], $data['payment_type'], $data['commission_type']);
            }
        }
        else
        {
            if($data['commission_amount'] > 0.00)
            {
                $data['created_on'] = getCurrentDateTimeForDB();
                $this->db->insert('tbl_admin_pay_commission_temp',$data);
            }
        }

    }

    function getAffiliateListToPay($admin_user_id, $paymentPreference, $offset, $rowPerPage)
    {
        $limitCls = 'LIMIT '.$offset.', '.$rowPerPage;

       $sql = 'SELECT a.admin_affiliate_row_id,
                       a.affiliate_name,
                       a.affiliate_id,
                       a.affiliate_company,
                       tpc.commission_type,
                       CONCAT("'.getCurrencySymbol().'",tpc.commission_amount) as commission_amount,
                       CONCAT("'.getCurrencySymbol().'",tpc.refund_amount) as refund_amount,
                       (tpc.commission_amount-tpc.refund_amount) as net_amount
                       FROM tbl_admin_pay_commission_temp tpc
                       INNER JOIN tbl_admin_affiliate a
                       ON tpc.affiliate_row_id = a.admin_affiliate_row_id
                       WHERE  tpc.admin_user_id = '.$admin_user_id
                       .' AND tpc.payment_type = '. $paymentPreference
                       .' ORDER BY a.affiliate_name ' .$limitCls                    ;

        $res = $this->db->query($sql);
        $result = $res->result_array();
        return $result;

    }

    function getSelectedAffiliateCommissionInfo($admin_user_id, $admin_affiliate_row_id)
    {
        $this->db->select('*');
        $this->db->from('tbl_admin_pay_commission_temp');
        $this->db->where('admin_user_id', $admin_user_id);
        $this->db->where('affiliate_row_id', $admin_affiliate_row_id);
        $result = $this->db->get();
        return $result->row_array();

    }

    function deleteSelectedAffiliate($admin_user_id, $affiliate_row_id, $paymentPreference, $commissionType)
    {
        if($admin_user_id && $affiliate_row_id)
        {
            $this->db->where('admin_user_id', $admin_user_id);
            $this->db->where('affiliate_row_id', $affiliate_row_id);
            $this->db->where('payment_type', $paymentPreference);
            $this->db->where('commission_type', $commissionType);
            $this->db->delete('tbl_admin_pay_commission_temp');
        }
    }

    function deleteAllSelectedAffiliates($admin_user_id, $paymentPreference='')
    {
        if($admin_user_id)
        {
            $this->db->where('admin_user_id', $admin_user_id);
            $this->db->delete('tbl_admin_pay_commission_temp');
        }
    }

    function getTotalPayableCommissionInfo($admin_user_id, $paymentPreference)
    {
         $sql = 'SELECT COUNT(tpc.affiliate_row_id) as total_count,
                        SUM(tpc.commission_amount-tpc.refund_amount) as total_amount
                       FROM tbl_admin_pay_commission_temp tpc
                       WHERE  tpc.admin_user_id = '.$admin_user_id
                       .' AND tpc.payment_type = '. $paymentPreference
                       .' GROUP BY tpc.admin_user_id';

        $res = $this->db->query($sql);
        $result = $res->result_array();
        return $result;
    }

    function getAllSelectedAffiliatePaymentInfo($admin_user_id, $paymentPreference)
    {
        $sql = 'SELECT a.admin_affiliate_row_id,
                       a.affiliate_name,
                       a.affiliate_email,
                       a.affiliate_name_on_check,
                       a.affiliate_paypal_email,
                       a.affiliate_id,
                       a.affiliate_company,
                       tpc.commission_type,
                       tpc.commission_type,
                       tpc.commission_amount,
                       tpc.refund_amount,
                       (tpc.commission_amount-tpc.refund_amount) as net_amount,
                       tpc.commission_details
                       FROM tbl_admin_pay_commission_temp tpc
                       INNER JOIN tbl_admin_affiliate a
                       ON tpc.affiliate_row_id = a.admin_affiliate_row_id
                       WHERE  tpc.admin_user_id = '.$admin_user_id
                       .' AND tpc.payment_type = '. $paymentPreference
                       .' ORDER BY a.affiliate_name ' ;
        $res = $this->db->query($sql);
        $result = $res->result_array();
        return $result;
    }

    function savePaidSalesCommissionInfo($saveData)
    {

        $saveData['created_on'] = getCurrentDateTimeForDB();
        $this->db->insert('tbl_admin_paycommission',$saveData);
        return $this->db->insert_id();
    }

    function updatePayCommission($data)
    {
        if($data['pay_commission_row_id'])
        {
            $this->db->where('pay_commission_row_id', $data['pay_commission_row_id']);
            return $this->db->update('tbl_paycommission', $data);
        }
    }



    /**
    *@author Shafiul Alam
    *@modified_by Mohammad Sajjad Hossain
    *@desc this finciton update sales commission payment status by order row id
    */
    function updateSalesCommissionPaymentStatus($salesCommissionId, $adjustedCommission=0, $changeCommission=0)
    {
        if($salesCommissionId)
        {
            $this->db->set('payment_status ',1);
            $this->db->set('paid_on ',getCurrentDateTimeForDB());
            $this->db->where('admin_sales_commission_row_id', $salesCommissionId);
            $this->db->update('tbl_admin_sales_commission');
        }
    }


    function savePaidSalesCommissionSummaryInfo($saveData)
    {
        $saveData['paid_on'] = getCurrentDateTimeForDB();
        $this->db->insert('tbl_admin_pay_commission_summary',$saveData);
        return $this->db->insert_id();
    }

    function getAllPaidAffiliates($admin_user_id, $paymentPreference, $admin_pay_commission_summary_row_id)
    {
        $sql = $this->getPayCommissionSummarySQL($admin_user_id, $paymentPreference, $admin_pay_commission_summary_row_id);
        $res = $this->db->query($sql);
        $result = $res->result_array();
        return $result;
    }

    function getPaymentSummaryInfoByRowId($admin_user_id, $admin_pay_commission_summary_row_id)
    {
        $sql = "SELECT
                pcs.pay_commission_summary_row_id,
                pcs.paid_commission_amount as total_amount,
                DATE_FORMAT(pcs.paid_on,'" . $this->config->item('show_datetime_fmt') . "') AS payment_date
                FROM tbl_admin_pay_commission_summary pcs
                WHERE admin_user_id=$admin_user_id
                AND pay_commission_summary_row_id=$admin_pay_commission_summary_row_id";
        $res = $this->db->query($sql);
        $result = $res->row_array();
        return $result;
    }

    function getPaidCommissionSummaryForCSV($admin_user_id, $paymentPreference, $admin_pay_commission_summary_row_id)
    {

        $sql = $this->getPayCommissionSummarySQL($admin_user_id, $paymentPreference, $admin_pay_commission_summary_row_id,1);
        return $this->db->query($sql);
    }

    function getPayCommissionSummarySQL($admin_user_id, $paymentPreference, $admin_pay_commission_summary_row_id, $purpose=0)
    {
        if($purpose==1)
        {
            $address = 'CONCAT("\"",CONCAT_WS(",",
                                      NULLIF(a.affiliate_company,""),
                                      CONCAT("Attn:",IF(a.affiliate_name_on_check = "" OR ISNULL(a.affiliate_name_on_check), a.affiliate_name, a.affiliate_name_on_check)),
                                      NULLIF(a.affiliate_address1,""),
                                      NULLIF(a.affiliate_address2,""),
                                      NULLIF(a.affiliate_city,""),
                                      NULLIF(a.affiliate_state,""),
                                      NULLIF(a.affiliate_zip,""),
                                      NULLIF(a.affiliate_country,"")
                                      ),"\"")
                        AS "Payment Information", a.affiliate_paypal_email AS "Paypal Information"';
            $affiliateName = 'CONCAT("\"",a.affiliate_name, "\"") As "Affiliate Name"';
            $affiliateCompany = 'CONCAT("\"",a.affiliate_company, "\"") As "Company Name"';
        }
        else
        {
            $address = '(CASE pc.payment_type
                       WHEN 1 THEN a.affiliate_paypal_email
                       ELSE CONCAT_WS("<br/>",
                                      NULLIF(a.affiliate_company,""),
                                      CONCAT("Attn:", IF(a.affiliate_name_on_check = "" OR ISNULL(a.affiliate_name_on_check), a.affiliate_name, a.affiliate_name_on_check)),
                                      NULLIF(a.affiliate_address1,""),
                                      NULLIF(a.affiliate_address2,""),
                                      NULLIF(CONCAT_WS(",",
                                                NULLIF(a.affiliate_city,""),
                                                NULLIF(a.affiliate_state,""),
                                                NULLIF(a.affiliate_zip,"")
                                                ), ""),
                                      NULLIF(a.affiliate_country,"")
                                      )
                       END ) AS "Payment Information", a.affiliate_paypal_email AS "Paypal Information"';
            $affiliateName = 'a.affiliate_name AS "Affiliate Name" , pay_commission_row_id';
            $affiliateCompany = 'a.affiliate_company AS "Company Name"';
        }

       $sql = 'SELECT '.$affiliateName.',
                        a.affiliate_id AS "Affiliate ID",
                      '.$affiliateCompany.',

                       CONCAT("'.getCurrencySymbol().'",(CASE (pc.sales_commission=0.00 OR ISNULL(pc.sales_commission) )
                       WHEN 0 THEN ROUND(IFNULL(sales_commission, 0.00), 2)
                       END) ) AS "Commission Amount",'.$address.'
                       FROM tbl_admin_paycommission pc
                       INNER JOIN tbl_admin_affiliate a
                       ON pc.admin_affiliate_row_id=a.admin_affiliate_row_id
                       WHERE  pc.admin_user_id = '.$admin_user_id
                       .' AND pc.pay_commission_summary_row_id = '. $admin_pay_commission_summary_row_id
                       .' AND pc.payment_type = '. $paymentPreference
                       .' ORDER BY a.affiliate_name ' ;

        return $sql;
    }




    function getDateConditions($data)
    {
        if($data['date_range'] == 30)
        {
            $conditions = ' DATEDIFF(DATE(NOW()), DATE(sc.created_on)) >= 30  ';
            $inner = ' DATEDIFF(DATE(NOW()), DATE(isc.created_on)) >= 30  ';
        }
        else if($data['date_range'] == 60)
        {
            $conditions = ' DATEDIFF(DATE(NOW()), DATE(sc.created_on)) >= 60  ';
            $inner = ' DATEDIFF(DATE(NOW()), DATE(isc.created_on)) >= 60  ';
        }
        else if($data['date_range'] == 90)
        {
            $conditions = ' DATEDIFF(DATE(NOW()), DATE(sc.created_on)) >= 90  ';
            $inner = ' DATEDIFF(DATE(NOW()), DATE(isc.created_on)) >= 90  ';
        }
        else
        {
            if($data['start_date'] && $data['end_date'])
            {
                $conditions = ' DATE(sc.created_on) BETWEEN \'' . $data['start_date'] . '\'  AND  \'' . $data['end_date'].'\'';
                $inner = ' DATE(isc.created_on) BETWEEN \'' . $data['start_date'] . '\'  AND  \'' . $data['end_date'].'\'';
            }
            else if($data['start_date'])
            {
                $conditions = ' DATE(sc.created_on) >= \'' . $data['start_date'].'\'';
                $inner = ' DATE(isc.created_on) >= \'' . $data['start_date'].'\'';
            }
            else if($data['end_date'])
            {
                $conditions = ' DATE(sc.created_on) <= \'' . $data['end_date'].'\'';
                $inner = ' DATE(isc.created_on) <= \'' . $data['end_date'].'\'';
            }
        }
        $dateCond = array();
        $dateCond[0] = $conditions;
        $dateCond[1] = $inner;
        return $dateCond;
    }



    function getNotEligibleAffiliateList($admin_user_id, $paymentPreference)
    {
        $sql = 'SELECT COUNT(tpc.affiliate_row_id) as cnt
                FROM tbl_admin_pay_commission_temp tpc
                WHERE (tpc.commission_amount-tpc.refund_amount) <0.00
                       AND  tpc.admin_user_id = '.$admin_user_id
                       .' AND tpc.payment_type = '. $paymentPreference           ;

        $res = $this->db->query($sql);
        $result = $res->row_array();
        return $result['cnt'];
    }

    function getAffiliateCommissionInvoice($admin_user_id, $pay_commission_row_id)
    {
        $sql = ' SELECT a.affiliate_name,
                       a.affiliate_id AS "Affiliate ID",
                       a.affiliate_row_id,
                       CONCAT_WS("<br/>",
                                      IFNULL(NULLIF(a.affiliate_company,""),a.affiliate_name),
                                      NULLIF(a.affiliate_address1,""),
                                      NULLIF(a.affiliate_address2,""),
                                      NULLIF(CONCAT_WS(",",
                                                NULLIF(a.affiliate_city,""),
                                                NULLIF(a.affiliate_state,""),
                                                NULLIF(a.affiliate_zip,"")
                                                ), ""),
                                      NULLIF(a.affiliate_country,"")
                                      ) AS "Payment Information",
                       a.affiliate_paypal_email,
                       a.vat_registration,
                       IFNULL(IFNULL(a.affiliate_work_phone, a.affiliate_home_phone), "") AS "affiliate_phone",
                       (CASE (pc.sales_commission=0.00 OR ISNULL(pc.sales_commission))
                       WHEN 1 THEN 1
                       ELSE 2
                       END) AS "Commission Type",
                       pc.vat_rate,
                       (CASE (pc.sales_commission=0.00 OR ISNULL(pc.sales_commission) )
                       WHEN 0 THEN ROUND(IFNULL(sales_commission, 0.00), 2)
                       ELSE ROUND(IFNULL(pc.non_sales_commission, 0.00), 2)
                       END) AS "Commission Amount",
                       (CASE (pc.sales_commission=0.00 OR ISNULL(pc.sales_commission) )
                       WHEN 0 THEN ROUND(IFNULL(refund_sales_commission, 0.00), 2)
                       ELSE 0.00
                       END) AS "Refunded Amount",
                       (CASE (pc.sales_commission=0.00 OR ISNULL(pc.sales_commission) )
                       WHEN 0 THEN ROUND(IFNULL((sales_commission-refund_sales_commission), 0.00), 2)
                       ELSE ROUND(IFNULL(pc.non_sales_commission, 0.00), 2)
                       END) AS "Paid Amount",
                       DAY(pc.created_on) as paymnet_date_day,
                       MONTH(pc.created_on) as paymnet_date_month,
                       YEAR(pc.created_on) as paymnet_date_year,
                       DATE_FORMAT(pc.created_on, "%D %M %Y") AS "payment_date"
                       FROM tbl_paycommission pc
                       INNER JOIN tbl_admin_affiliate a
                       ON pc.affiliate_row_id=a.affiliate_row_id
                       WHERE  pc.admin_user_id = '.$admin_user_id
                       .' AND pc.pay_commission_row_id = '. $pay_commission_row_id;
        $query = $this->db->query($sql);
        $result = $query->row_array();
        $result['vat_registration'] = decrypt($result['vat_registration']);
        return $result;
    }

    function refund ($refundIds)
    {
       $refundIds =implode (',',$refundIds);
       $sql = 'UPDATE tbl_admin_sales_commission SET payment_status = 2,paid_on = "'. getCurrentDateTimeForDB() .'" WHERE admin_sales_commission_row_id IN ('.$refundIds.') AND payment_status!=2';
       $query = $this->db->query($sql);

       return true;
    }
}
?>