<?php
/*This class contains all functions related to affiliate sign up, get affiliate list*/
  class Admin_affiliate_signup_model extends CI_Model
{

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

    //insert a new affiliate into the system/db
    function saveAdminAffiliate($data)
    {
        if($data['affiliate_tax_id'])
        {
            $data['affiliate_tax_id'] = encrypt($data['affiliate_tax_id']);
        }
        if($data['admin_affiliate_row_id'])
        {
            $this->db->where('admin_affiliate_row_id', $data['admin_affiliate_row_id']);
            $this->db->update('tbl_admin_affiliate', $data);
            return $data['admin_affiliate_row_id'];
        }
        else
        {
            $this->db->set('created_on', getCurrentDateTimeForDB());
            $this->db->insert('tbl_admin_affiliate', $data);
            $admin_affiliate_row_id = $this->db->insert_id();
            /////// start code to get admin's affiliate id //////////
            $affiliate_id_prefix = 'AFF';
            $this->load->library('Application_Utility');
            $affiliate_id = $this->application_utility->generateId($affiliate_id_prefix , $admin_affiliate_row_id);
            /////// end code to get admin's affiliate id ////////////
            $this->db->set('affiliate_id',$affiliate_id);
            $this->db->where('admin_affiliate_row_id',$admin_affiliate_row_id);
            $this->db->update('tbl_admin_affiliate');
            return $admin_affiliate_row_id;
        }
    }

    function admin_affiliate_emailAddressExists($affiliate_email , $admin_user_id)
    {
        $sql = "SELECT count(*) AS CNTENTRY
                FROM tbl_admin_affiliate
                WHERE admin_user_id = '{$admin_user_id}'
                AND affiliate_email = '{$affiliate_email}'";
                $query = $this->db->query($sql);
                $result = $query->row($query);
                $count = $result->CNTENTRY;
                if($count > 0)
                    return true;
                else
                    return false;
    }

     function showManageAdminAffiliate($admin_user_id ,$qs = '')
    {
        $datetime_fmt = $this->config->item('show_datetime_fmt');
        $sql = '(SELECT affiliate_name,
                affiliate_company,
                affiliate_id,
                admin_affiliate_row_id,
                admin_user_id,
                affiliate_email,DATE_FORMAT(created_on,\''.$datetime_fmt.'\') AS created_on,
                referrer_id,
                join_status,
                (CASE
                WHEN join_status = \'' . $this->config->item('aff_pending_num') . '\' THEN \'' . $this->config->item('aff_pending_num_text') . '\'
                WHEN join_status = \''.$this->config->item('aff_accepted_num').'\' THEN \''.$this->config->item('aff_accepted_text').'\'
                WHEN join_status = \''.$this->config->item('aff_declined_num').'\' THEN \''.$this->config->item('aff_declined_text').'\'
                WHEN join_status = \''.$this->config->item('aff_inactive_num').'\' THEN \''.$this->config->item('aff_inactive_text').'\'
                END) AS event_status
        FROM tbl_admin_affiliate
        WHERE tbl_admin_affiliate.admin_user_id = ' . $admin_user_id . ' AND (join_status = 2 OR join_status = 4)  '.$qs. ') AS abc';

        $this->load->library ( 'rapyd' );
        $this->rapyd->load('datagrid');
        $grid = new DataGrid('', $sql);
        $grid->per_page = 25;
        $grid->column_orderby('Affiliate Id', 'affiliate_id','admin_affiliate_row_id', 'align=\'left\'');
        $grid->column_detail('Affiliate Name', 'affiliate_name', 'administration/admin_affiliate_signup/addAdminAffiliate/<#admin_affiliate_row_id#>', 'align="left"',true,'class="block_page"');
        $grid->column_orderby('Company', 'affiliate_company', 'affiliate_company', 'align=\'left\'');
        $grid->column_orderby('Email', 'affiliate_email', 'affiliate_email', 'align=\'left\'');
        $grid->column_orderby('Date Entered', 'created_on','created_on', 'align=\'left\'');
        $grid->column_orderby('Event Status', 'event_status', 'event_status', 'align=\'left\' width="80px"');
        $grid->build();
        return $grid->output;
    }
    function getAdminAffiliateDetailsByAffRowId($admin_affiliate_row_id)
    {
        $this->db->select('*');
        $this->db->from('tbl_admin_affiliate');
        $this->db->where('admin_affiliate_row_id',$admin_affiliate_row_id);
        $res = $this->db->get();
        $result = $res->row_array();
        if(is_array($result))
        {
            if(trim($result['affiliate_tax_id']))
            {
                $result['affiliate_tax_id'] = decrypt($result['affiliate_tax_id']);
            }
        }
        return $result;
    }

    function showUnApprovedAffiliate($admin_user_id, $queryString)
    {
        $sql = '(SELECT affiliate_name,affiliate_company,admin_affiliate_row_id,affiliate_id,admin_user_id,affiliate_email,DATE_FORMAT(created_on,\'' .  $_SESSION['date_format'] . '\') AS created_on,referrer_id,join_status,

        (CASE WHEN join_status=\'' . $this->config->item('aff_pending_num') . '\'  THEN \'' . $this->config->item('aff_pending_num_text') . '\'
        WHEN join_status=\'' . $this->config->item('aff_accepted_num') . '\'  THEN \'' . $this->config->item('aff_accepted_text') . '\'
        WHEN join_status=\'' . $this->config->item('aff_declined_num') . '\'  THEN \'' . $this->config->item('aff_declined_text') . '\'
        WHEN join_status=\'' . $this->config->item('aff_inactive_num') . '\'  THEN \'' . $this->config->item('aff_inactive_text') . '\'  END) as event_status
        FROM tbl_admin_affiliate
        WHERE tbl_admin_affiliate.admin_user_id =' . $admin_user_id . ' '.$queryString.' ( join_status=1 OR join_status=4)) AS abc';

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

        $grid->showFeaturedBox = true;
        $grid->currentPageNo = $this->config->item('orderCheck');

        $grid->order_by('admin_affiliate_row_id', 'desc');

        $grid->per_page = $this->config->item('rows_per_page');

        $grid->column('Select','admin_affiliate_row_id','admin_affiliate_row_id');

        $grid->column_orderby('Affiliate Id', 'affiliate_id','affiliate_id', 'align=\'left\'');
        $grid->column_detail('Affiliate name', 'affiliate_name', 'administration/admin_affiliate_signup/addAdminAffiliate/<#admin_affiliate_row_id#>', 'align=\'left\'');
        $grid->column_orderby('Company', 'affiliate_company', 'affiliate_company', 'align=\'left\'');
        $grid->column_orderby('Email', 'affiliate_email', 'affiliate_email', 'align=\'left\'');
        $grid->column_orderby('Date Entered', 'created_on','created_on', 'align=\'left\'');
        $grid->column_orderby('Referrer ID', 'referrer_id', 'referrer_id', 'align=\'left\'');
        $grid->column_orderby('Event Status', 'event_status', 'event_status', 'align=\'left\'');

        $grid->build();
        return $grid->output;
    }

    function activateAdminAffiliate($admin_affiliate_row_id)
    {
        $data['join_status']=$this->config->item('aff_accepted_num');
        $this->db->where('admin_affiliate_row_id',$admin_affiliate_row_id);

        $this->db->update('tbl_admin_affiliate',$data);
    }

    function declineAdminAffiliate($admin_affiliate_row_id)
    {
        $data['join_status']=$this->config->item('aff_declined_num');
        $this->db->where('admin_affiliate_row_id', $admin_affiliate_row_id);

        $this->db->update('tbl_admin_affiliate',$data);
    }

    function getAdminAffDetailsByEncAffRowId($enc_admin_affiliate_row_id)
    {
        $sql = "SELECT * FROM tbl_admin_affiliate WHERE MD5(admin_affiliate_row_id)='".$enc_admin_affiliate_row_id."'";
        $resource = $this->db->query($sql);
        $result = $resource->row_array();
        return $result;
    }

    function checkAdminAffiliateStatus($admin_affiliate_row_id)
    {
        $this->db->select('join_status');
        $this->db->from('tbl_admin_affiliate');
        $this->db->where('admin_affiliate_row_id',$admin_affiliate_row_id);
        $res = $this->db->get();
        $result = $res->row_array();
        return $result['join_status'];

    }
     function adminAffiliateLoginCheck($data)
    {
        $sql = "SELECT * FROM tbl_admin_affiliate
                WHERE admin_user_id = {$data['admin_user_id']}
                AND affiliate_email = '{$data['user_name']}'
                AND affiliate_password = '{$data['user_password']}'";

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

    }

	function checkExistanceOfPasswordForUserId($data)
	{
		$this->db->select('*');
		$this->db->from('tbl_admin_affiliate');
		$this->db->where('affiliate_email',$data['affiliate_username']);
		$this->db->where('admin_affiliate_row_id',$data['admin_affiliate_row_id']);
		$this->db->where('affiliate_password',$data['affiliate_password_post']);
		$res = $this->db->get();
		return  $res->row_array();
	}
	function updateLoginInformation($data)
	{
		$this->db->set('affiliate_password',$data['newPassword']);
		$this->db->where('admin_affiliate_row_id',$data['admin_affiliate_row_id']);
		$this->db->update('tbl_admin_affiliate');
	}

    function getAffiliates($fields = null)
    {
        $select = $fields ? implode(',', $fields) : ' * ';
        $sql = "SELECT {$select} FROM tbl_admin_affiliate WHERE admin_user_id = 1 AND (join_status = 2 OR join_status = 4) ORDER BY affiliate_name ";
        $query = $this->db->query($sql);
        return $query->result_array();
    }

    function getAssignedAffiliate($merchant_row_id)
    {
        $sql = "SELECT tbl_admin_affiliate.* FROM tbl_admin_affiliate INNER JOIN tbl_merchant WHERE tbl_admin_affiliate.admin_affiliate_row_id =tbl_merchant.merchant_referrer_id
                     AND tbl_merchant.merchant_row_id = $merchant_row_id";

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

    function changeAffiliate($merchant_row_id,$admin_affiliate_row_id)
    {
        if($merchant_row_id)
        {
        $sql = "UPDATE tbl_merchant SET merchant_referrer_id = $admin_affiliate_row_id WHERE merchant_row_id = $merchant_row_id";
        $query = $this->db->query($sql);

        }
    }

    function getCommissionByMerchantRowId($merchant_row_id)
    {
        $sql = "(SELECT A.admin_sales_commission_row_id,
                        B.affiliate_name,
                        B.affiliate_id,
                        A.admin_affiliate_row_id,
                        CONCAT('$', '', A.sales_commission) AS commission,
                        A.merchant_row_id,
                        (CASE WHEN is_recurring = 1  THEN 'YES' ELSE 'NO' END) as recurring_status,
                        A.created_on FROM
                    tbl_admin_sales_commission AS A JOIN
                    tbl_admin_affiliate AS B ON(A.admin_affiliate_row_id = B.admin_affiliate_row_id) WHERE merchant_row_id = {$merchant_row_id}) AS abc";

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

        $grid->column('Affiliate Name', 'affiliate_name', 'align=\'left\'');
        $grid->column('Affiliate ID', 'affiliate_id', 'align=\'center\'');
        $grid->column('Commission', 'commission', 'align=\'center\'');
        $grid->column('Recurring', 'recurring_status', 'align=\'center\'');
        $grid->column('Added On', 'created_on', 'align=\'center\'');
        $grid->column_detail('','Edit','administration/merchant/addCommission/<#merchant_row_id#>/<#admin_sales_commission_row_id#>', 'align=\'center\'', false, 'class="submodal-500-300"', true, '');
        $grid->order_by('created_on', 'desc');
        $grid->build();
        return $grid->output;
    }

    function saveAffiliateCommission($info)
    {
        $sql = "SELECT CONCAT(B.affiliate_id, ' -- ', B.affiliate_name) AS affiliate
                       FROM tbl_admin_sales_commission AS A JOIN
                       tbl_admin_affiliate AS B ON(A.admin_affiliate_row_id = B.admin_affiliate_row_id)
                       WHERE A.merchant_row_id = {$info['merchant_row_id']} AND
                       A.admin_affiliate_row_id != {$info['admin_affiliate_row_id']}";
        $query = $this->db->query($sql);
        $result = $query->row_array();
        if($result)
        {
            exception("This merchant is already assigned to " . $result['affiliate']);
            return false;
        }

        if($info['is_recurring'])
        {
            $sql = "UPDATE tbl_admin_sales_commission set is_recurring = 0 WHERE merchant_row_id = " . $info['merchant_row_id'];
            $this->db->query($sql);
        }

        if($info['admin_sales_commission_row_id'])
        {
            $this->db->where('admin_sales_commission_row_id',$info['admin_sales_commission_row_id']);
            return $this->db->update('tbl_admin_sales_commission', $info);
        }
        else
        {
            $info['created_on'] = date('Y-m-d H:i:s');
            return $this->db->insert('tbl_admin_sales_commission', $info);
        }
    }

    function getCommissionByRowId($admin_sales_commission_row_id)
    {
        $sql = "SELECT * FROM tbl_admin_sales_commission WHERE admin_sales_commission_row_id = {$admin_sales_commission_row_id}";
        $query = $this->db->query($sql);
        return $query->row_array();
    }

    function getAffiliateRecurringCommissionSetup($merchant_row_id)
    {
        $sql = "SELECT *FROM tbl_admin_sales_commission WHERE merchant_row_id = {$merchant_row_id} AND is_recurring = 1";
        $query = $this->db->query($sql);
        return $query->row_array();
    }

    function getAffiliateByEmail($email)
    {
        $sql = "SELECT admin_affiliate_row_id FROM tbl_admin_affiliate WHERE affiliate_email ='{$email}'";
        $query = $this->db->query($sql);
        return $query->row_array();
    }

	function getNumberOfMerachant($admin_affiliate_row_id)
	{
		if($admin_affiliate_row_id)
		{
            $sql = "SELECT COUNT(DISTINCT(merchant_row_id)) AS total_merchant FROM tbl_merchant WHERE merchant_referrer_id = $admin_affiliate_row_id
            AND is_expires = '0' AND active_status = 1";
     	    $query = $this->db->query($sql);
            return $query->row()->total_merchant;
		}

	}

    function saveAffiliateCommissionTemp ($info)
    {
    $this->db->set('admin_affiliate_row_id', $info['admin_affiliate_row_id']);
    $this->db->set('merchant_row_id', $info['merchant_row_id']);
    $this->db->set('sales_commission', $info['sales_commission']);
    $this->db->set('payment_id', $info['payment_id']);
    $this->db->set('created_on', $info['created_on']);
    return $this->db->insert('tbl_admin_sales_commission_temp');

    }
    
    /*
    *@Anjuman | D: 04242012 (mmddyyyy) 
    *Update W9 file field as it has been deleted
    */
    function getCustomAffiliateInfo($affiliate_row_id, $fields = '*')
    {                                              
        $affiliate_row_id = (int) $affiliate_row_id;

        if($affiliate_row_id)
        {
            $sql = 'SELECT ' . $fields . '
                    FROM tbl_admin_affiliate
                    WHERE admin_affiliate_row_id = ' . $affiliate_row_id;

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

        return false;
    }
    
    function updateW9FileField($admin_affiliate_row_id,$data)
    {                                                         
        $this->db->where('admin_affiliate_row_id',$admin_affiliate_row_id);
        $this->db->set('w9_file', '');
        $this->db->update('tbl_admin_affiliate');
        return true;
    }   

}
?>
