<?php
class Recurring_Payment_model extends CI_Model
{

	public function __construct()
	{
		parent::__construct();
		$this->load->config('paypal_config');
	}

    function getChargeableRecurringOrders()
    {
        $standardGateways = $this->config->item('allowed_payment_gateways_standard');
        $standardGateways = implode(',', array_keys($standardGateways));
        $ignoreMerchants = implode(',', $this->config->item('test_accounts'));

        $sql = 'SELECT  ro.recurring_orders_id,
                        ro.merchant_row_id,
                        ro.product_row_id,
                        ro.product_quantity,
                        ro.order_row_id,
                        ro.order_details_id,
                        ro.customer_id,
                        ro.recurring_price,
                        ro.recurring_shipping_charge,
                        ro.recurring_sales_tax,
                        ro.recurring_logic,
                        ro.recurring_cycle,
                        ro.recurring_count,
                        ro.recurring_pattern,
                        ro.recurring_start_date,
                        ro.recurring_end_date,
                        ro.recurring_day_of_week,
                        ro.recurring_day_of_month,
                        ro.recurring_week_of_month,
                        ro.recurring_monthly_option,
                        ro.recurring_start_duration,
                        ro.date_of_next_charge,
                        ro.date_of_last_charge,
                        ro.successfull_attempted,
                        ro.failure_attempted,
                        ro.payment_gateway,
                        ro.shipping_address_line1,
                        ro.shipping_address_line2,
                        ro.shipping_customer_city,
                        ro.shipping_customer_state,
                        ro.shipping_current_status,
                        ro.shipping_company_name,
                        ro.shipping_name,
                        ro.shipping_customer_zip,
                        ro.shipping_country,
                        ro.sales_comission_manual,
                        ro.profile_type,
                        od.product_description
                  FROM tbl_recurring_orders ro
                  INNER JOIN tbl_order_details od ON od.order_details_id = ro.order_details_id
                  INNER JOIN tbl_payment_details pd ON ro.order_row_id = pd.order_row_id
                                                    AND (pd.recurring_orders_id IS NULL OR pd.recurring_orders_id = 0)
                  INNER JOIN tbl_merchant m ON m.merchant_row_id = ro.merchant_row_id
                                            AND m.active_status = 1
                                            AND m.is_expires = \'0\'
                  INNER JOIN tbl_gateway_setup gs ON gs.merchant_row_id = ro.merchant_row_id
                                                AND gs.real_time_gateway > 0
                                                AND gs.real_time_gateway != ' . $this->config->item('payment_gateway_paypal_website_payment_pro') . '
                  WHERE ro.active_status = 1';

        if($ignoreMerchants)
        {
            $sql .= ' AND ro.merchant_row_id NOT IN (' . $ignoreMerchants . ')';
            //$sql .= ' AND ro.merchant_row_id IN (' . $ignoreMerchants . ')';
        }

        $sql .= ' AND ro.is_declined = 0
                  AND NOT (ro.recurring_logic = 1 && ro.recurring_pattern = 0)
                  AND ro.is_finished = 0
                  AND ro.payment_gateway = 2
                  AND ro.deletion_status = 0
                  AND pd.payment_gateway NOT IN (' . $standardGateways . ')
                  AND (ro.recurring_count = 0 OR ro.recurring_count <> ro.successfull_attempted)
                  AND (
                        (ro.failure_attempted = 0 AND DATE(ro.date_of_next_charge) <= CURDATE())
                        OR
                        (ro.failure_attempted = 1 AND IFNULL(DATEDIFF(NOW(), ro.date_of_next_charge), 0) >= 1)
                        OR
                        (ro.failure_attempted = 2 AND IFNULL(DATEDIFF(NOW(), ro.date_of_next_charge), 0) >= 7)
                        OR
                        (ro.failure_attempted = 3 AND IFNULL(DATEDIFF(NOW(), ro.date_of_next_charge), 0) >= 21)
                    )
                  GROUP BY ro.customer_id ORDER BY ro.recurring_orders_id DESC
                  LIMIT 100';

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

    function getPayPalProRecurringOrders()
    {
        $sql = 'SELECT  rp.recurring_orders_id,
                        rp.payment_gateway,
                        rp.profile_id,
                        ro.merchant_row_id,
                        ro.product_row_id,
                        ro.product_quantity,
                        ro.order_row_id,
                        ro.order_details_id,
                        ro.customer_id,
                        ro.recurring_price,
                        ro.recurring_cycle,
                        ro.recurring_count,
                        ro.recurring_start_duration,
                        ro.date_of_next_charge,
                        ro.date_of_last_charge,
                        ro.successfull_attempted,
                        ro.failure_attempted,
                        ro.payment_gateway AS gateway_type,
                        ro.shipping_address_line1,
                        ro.shipping_address_line2,
                        ro.shipping_customer_city,
                        ro.shipping_customer_state,
                        ro.shipping_current_status,
                        ro.shipping_company_name,
                        ro.shipping_name,
                        ro.shipping_customer_zip,
                        ro.shipping_country
                  FROM tbl_recurring_profile rp
                  INNER JOIN tbl_recurring_orders ro ON rp.recurring_orders_id = ro.recurring_orders_id
                  INNER JOIN tbl_gateway_real_time AS rg ON(rg.real_time_gateway = rp.payment_gateway AND ro.merchant_row_id = rg.merchant_row_id)
                  WHERE (rp.payment_gateway = ' . $this->config->item('payment_gateway_paypal_website_payment_pro') . ' OR rp.payment_gateway = ' . $this->config->item('payment_gateway_paypal') . ' )
                  AND rp.success = 1
                  AND ro.active_status = 1
                  AND ro.is_declined = 0
                  AND ro.is_finished = 0
                  AND ro.deletion_status!=1
                  AND (ro.recurring_count = 0 OR ro.recurring_count <> ro.successfull_attempted)
                  AND DATE(ro.date_of_next_charge) <= CURDATE()
                  ORDER BY ro.recurring_orders_id DESC LIMIT 100';
        $query = $this->db->query($sql);
        return $query->result();
    }

	function getCurrentListing ()
	{
		$this->db->select('*');
		$this->db->where('is_finished', 0);
		$this->db->where('is_declined', 0);
		$this->db->where('active_status', 1);
		$this->db->where('deletion_status', 0);
		#$this->db->where('successfull_attempted >',0);
		$this->db->from('tbl_recurring_orders');
		$resultSet = $this->db->get();
		#$userList = $resultSet->row();
		return $resultSet->result();
	}

	function getCustomerId ($order_details_id)
	{
		$this->db->select('customer_id');
		$this->db->from('tbl_order ord, tbl_order_details ord_det');
		$this->db->where('ord_det.order_details_id', $order_details_id);
		$this->db->where('ord_det.order_details_id=ord.order_id');
		$resultSet = $this->db->get();
		$result = $resultSet->row();
		return $result->customer_id;
	}

	function getMerchantRowId ($order_details_id)
	{
		$this->db->select('merchant_row_id');
		$this->db->from('tbl_order ord, tbl_order_details ord_det');
		$this->db->where('ord_det.order_details_id', $order_details_id);
		$this->db->where('ord_det.order_details_id=ord.order_id');
		$resultSet = $this->db->get();
		$result = $resultSet->row();
		return $result->merchant_row_id;
	}

	function recurringPaymentUpdate ($recurringData)
	{
		$this->db->where('recurring_orders_id', $recurringData['recurring_orders_id']);
		$this->db->update('tbl_recurring_orders', $recurringData);
	}

	function savePaymentDetails ($paymentDetails)
	{
		$this->db->insert('tbl_payment_details', $paymentDetails);
		return $this->db->insert_id();
	}

    function getPaymentDetailsByOrderRowId($orderRowId, $merchantRowId, $customerId = 0)
    {
        $sql = "SELECT *
                FROM tbl_payment_details
                WHERE order_row_id = $orderRowId
                AND merchant_row_id = $merchantRowId";

        if($customerId)
        {
            $sql .= " AND customer_id = $customerId ";
        }

        $sql .= " AND recurring_orders_id = 0";

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

    function updatePaymentDetails($data)
    {
        if($data['payment_id'])
        {
            $this->db->where('payment_id', $data['payment_id']);
            $this->db->update('tbl_payment_details', $data);
        }
    }

    function saveRecurringCommissionPayment($paymentDetails)
    {
        $this->db->insert('tbl_recurring_commission_payment', $paymentDetails);
        return $this->db->insert_id();
    }

	function saveRecurringPayment($recurringData)
	{
		$this->db->insert('tbl_recurring_transaction_history', $recurringData);
        return $this->db->insert_id();
	}

	function getTransactionStatus($recurring_orders_id)
	{
		$this->db->select('ro.is_declined');
		$this->db->select('rth.successfull_attempted');
		$this->db->from('tbl_recurring_transaction_history rth,tbl_recurring_orders ro');
		$this->db->where('rth.recurring_orders_id', $recurring_orders_id);
		$this->db->where('rth.recurring_orders_id=ro.recurring_orders_id');
		$resultSet = $this->db->get();
		return $resultSet->result();
	}

	function stopRecurring($recurring_orders_id)
	{
		$this->db->set('is_declined', 1);
        $this->db->set('failure_attempted', 0);
		$this->db->where('recurring_orders_id', $recurring_orders_id);
		return $this->db->update('tbl_recurring_orders');
	}

    function holdRecurring($recurringOrdersId)
    {
        $this->db->set('active_status', '0');
        $this->db->where('recurring_orders_id', $recurringOrdersId);
        return $this->db->update('tbl_recurring_orders');
    }

	/**
	 * @author Jilani Jidni
	 *
	 * return a list of merchant with billing information for payment
	 *
	 * @bug-solved 21-11-2008
	 * Add <b>no_of_fail_transaction</b> on select query
	 *
	 * @return List
	 */
	function getPayableMerchantLists($merchantId = 0)
	{
      if($merchantId)
         $where = " AND m.merchant_row_id = {$merchantId} ";  
      $sql = "SELECT  m.merchant_row_id,
                        m.merchant_id,
                        m.first_name,
                        m.last_name,
                        m.last_transaction_date,
                        m.last_due_date,
                        m.next_charge_date,
                        m.subscription_type,
                        m.package_id,
                        m.no_of_fail_transaction,
                        m.merchant_referrer_id,
                        mb.billing_name,
                        mb.billing_email,
                        mb.billing_phone,
                        mb.billing_company,
                        mb.billing_address,
                        mb.billing_country,
                        mb.billing_state,
                        mb.billing_city,
                        CASE mb.billing_zip_code
                        WHEN ''
                        THEN mb.billing_postal_code
                        ELSE mb.billing_zip_code
                        END AS billing_zip_code,
                        mb.card_type,
                        mb.card_number,
                        mb.card_exp_month,
                        mb.card_exp_year,
                        mb.card_holder_contactname,
                        mb.card_type_1,
                        mb.card_number_1,
                        mb.card_exp_month_1,
                        mb.card_exp_year_1,
                        mb.card_holder_contactname_1,
                        m.signup_amount
        FROM tbl_merchant m
        INNER JOIN tbl_merchant_billing mb ON mb.merchant_row_id = m.merchant_row_id
        WHERE m.active_status = 1
        AND m.is_expires = '0'
        AND m.is_trial != '1'
        AND m.package_id NOT IN(" . implode(',', $this->config->item('friend_family_package_ids')) . ")
        AND DATE(next_charge_date) <= CURDATE()" . $where;
		$resultSet = $this->db->query($sql);

		return $resultSet->result_array();
	}

    
    /**
     * @author Md. Alauddin Husain(alauddinkuet@gmail.com)
     *
     * return a list of Annual merchant with billing information for payment 
     *
     * @return List
     */
    function getCRMPayableAnnualMerchants()
    {
      $sql = "SELECT  m.merchant_row_id,
                        m.merchant_id,
                        m.first_name,
                        m.last_name,
                        m.last_transaction_date,
                        m.last_due_date,
                        m.next_charge_date,
                        m.subscription_type,
                        m.package_id,
                        m.no_of_fail_transaction,
                        m.merchant_referrer_id,
                        mb.billing_name,
                        mb.billing_email,
                        mb.billing_phone,
                        mb.billing_company,
                        mb.billing_address,
                        mb.billing_country,
                        mb.billing_state,
                        mb.billing_city,
                        CASE mb.billing_zip_code
                        WHEN ''
                        THEN mb.billing_postal_code
                        ELSE mb.billing_zip_code
                        END AS billing_zip_code,
                        mb.card_type,
                        mb.card_number,
                        mb.card_exp_month,
                        mb.card_exp_year,
                        mb.card_holder_contactname,
                        mb.card_type_1,
                        mb.card_number_1,
                        mb.card_exp_month_1,
                        mb.card_exp_year_1,
                        mb.card_holder_contactname_1,
                        m.signup_amount
        FROM tbl_merchant m
        INNER JOIN tbl_merchant_billing mb ON mb.merchant_row_id = m.merchant_row_id
        WHERE m.active_status = 1
        AND m.is_expires = '0'
        AND m.is_trial != '1'
        AND m.subscription_type = " . $this->config->item('annual_subcription') . " 
        AND ( m.package_id IN( " . implode(',', $this->config->item('platinum_elite_professional_ids')) . ") AND m.package_id NOT IN(" . implode(',', $this->config->item('friend_family_package_ids')) . "))
        AND DATE(crm_next_charge_date) <= CURDATE()";
        $resultSet = $this->db->query($sql);
        return $resultSet->result_array();
    }
    
    
    function updateAffiliateCommissionTemp($admin_sales_commission_temp_row_id)
    {
        $sql="UPDATE tbl_admin_sales_commission_temp
        SET last_due_date = '". date('Y-m-d H:i:s') ."', next_charge_date = ADDDATE('".date('Y-m-d H:i:s')."' , INTERVAL 30 DAY)
        where admin_sales_commission_temp_row_id = $admin_sales_commission_temp_row_id";

        $this->db->query($sql);
    }


    function getPayableAffiliateLists()
    {
        // we will provide commission  12 times at best.  if creation date is jan-1,2012. then we will allow it to retrieve commission jan-1,feb-1...dec-1. this 12 months.
        // so creation date + 1year= jan-1,2013 we will not provide commission . so jan-1,2012 + 360 days= dec 26 2012
        // can be the last date where we will allow providing commission.

        $sql = "SELECT  sc.*,m.package_id,m.subscription_type,p.annual_price
        FROM tbl_admin_sales_commission_temp sc
        INNER JOIN tbl_merchant m  ON sc.merchant_row_id = m.merchant_row_id
        INNER JOIN tbl_package p ON m.package_id = p.package_id
        WHERE m.active_status = 1
        AND m.is_expires = '0'
        AND m.is_trial != '1'
        AND m.package_id != " . $this->config->item('friend_family_package') . "
        AND sc.next_charge_date <= CURDATE()
        AND ADDDATE(sc.created_on,INTERVAL 360 DAY) > sc.next_charge_date";
        $resultSet = $this->db->query($sql);
        return $resultSet->result_array();
    }


	function updateNumberOfFailTransaction($merchantDetails)
	{
		if($merchantDetails['no_of_fail_transaction']==2)
		{
			$this->db->set('is_expires','1');
			$this->db->set('no_of_fail_transaction', 0);
            $this->db->set('expired_date', date('Y-m-d H:i:s'));
		}
		else
		{
			$this->db->set('no_of_fail_transaction', $merchantDetails['no_of_fail_transaction']+1);
		}
		$this->db->where('merchant_row_id',$merchantDetails['merchant_row_id']);
		$this->db->update('tbl_merchant');
	}

	function updateTransactionDate($merchantDetails)
	{
        $interval = $merchantDetails['subscription_type'] == $this->config->item('monthly_subcription') ? 28 : 365;
        $sql = 'UPDATE tbl_merchant
                SET last_transaction_date = ' . $this->db->escape($merchantDetails['last_transaction_date']) . ',
                    last_due_date = next_charge_date,
                    no_of_fail_transaction = 0,
                    next_charge_date = ADDDATE(next_charge_date, INTERVAL ' . $interval . ' DAY)
                WHERE merchant_row_id = ' . $merchantDetails['merchant_row_id'];
        $this->db->query($sql);
	}

    function updateCRMChargeTransactionDate($merchantDetails)
    {
        $sql = 'UPDATE tbl_merchant
                SET last_transaction_date = ' . $this->db->escape($merchantDetails['last_transaction_date']) . ',
                    last_due_date = next_charge_date,
                    no_of_fail_transaction = 0,
                    crm_next_charge_date = ADDDATE(crm_next_charge_date, INTERVAL 28 DAY)
                WHERE merchant_row_id = ' . $merchantDetails['merchant_row_id'];
        $this->db->query($sql);
    }

	/**
	 * @author Shafiul Alam
	 * This function return admin basic information
	 * * @return unknown
	 * @version PWC v1.2
	 */
	function getAdminBasicInfo()
	{
		$this->db->select('*');
		$this->db->from('tbl_admin_basic_setup');
		$this->db->where('admin_user_id', 1);
		$resultSet = $this->db->get();
		$result = $resultSet->row();
		return $result;
	}
	/**
	 * @author Shafiul Alam
	 * This function return recurring commission information
	 * * @return unknown
	 * @version PWC v1.2
	 */

	function getRecurringCommissionInfo($merchant_row_id,$month_year)
	{
		$this->db->select('*');
		$this->db->from(' tbl_recurring_commission ');
		$this->db->where('merchant_row_id',$merchant_row_id);
		$this->db->where('month_year',$month_year);
		$resultSet = $this->db->get();
		$result = $resultSet->row();
		return $result;
	}

    function getRecurringCommissionInfoById($recurring_commission_id)
    {
        $this->db->select('*');
        $this->db->from(' tbl_recurring_commission ');
        $this->db->where('recurring_commission_id',$recurring_commission_id);
        $resultSet = $this->db->get();
        $result = $resultSet->row();
        return $result;
    }

	/**
	 * @author Shafiul Alam
	 * This function update recurring commission information
	 * * @return unknown
	 * @version PWC v1.2
	 */
	function updateRecurringCommission($recurringCommissionData)
	{
        if(strstr($recurringCommissionData['recurring_commission_id'], '|'))
        {
            $recurringCommissionIds = explode('|', $recurringCommissionData['recurring_commission_id']);

            foreach($recurringCommissionIds as $recurringCommissionId)
            {
                $recurringCommissionData['recurring_commission_id'] = $recurringCommissionId;
                $this->db->where('recurring_commission_id',$recurringCommissionData['recurring_commission_id']);
                $this->db->update('tbl_recurring_commission', $recurringCommissionData);
            }
        }
        else
        {

		    $this->db->where('recurring_commission_id',$recurringCommissionData['recurring_commission_id']);
		    $this->db->update('tbl_recurring_commission', $recurringCommissionData);
        }
	}

	/**
	 * @author Shafiul Alam
	 * This function save recurring commission information
	 * * @return unknown
	 * @version PWC v1.2
	 */
	function saveRecurringCommission($recurringCommissionData)
	{
		$this->db->insert('tbl_recurring_commission', $recurringCommissionData);
		return $this->db->insert_id();
	}

    function isExistsPaymentDetails($customer_id,$orderRowId)
    {
        $this->db->select('count(*) as cns');
		$this->db->from('tbl_payment_details ');
		$this->db->where('customer_id', $customer_id);
        $this->db->where('order_row_id', $orderRowId);
		$recordSet = $this->db->get();
		$ip_count = $recordSet->row();
		if ($ip_count->cns > 0)
		{
			return true;
		}
		else
		{
			return false;
		}
    }

    function getPaymentGatewayUsed($customerId, $orderRowId)
    {
        $this->db->select('payment_gateway');
        $this->db->from('tbl_payment_details');
        $this->db->where('customer_id', $customerId);
        $this->db->where('order_row_id', $orderRowId);
        $recordSet = $this->db->get();
        return $recordSet->row();
    }

    function getRecurringCommissionableMerchants($adminBasicSetup)
    {
        //Recurring billing started from 15th Sept
        $sql = 'SELECT  mrc.merchant_row_id,
                        GROUP_CONCAT(mrc.recurring_commission_id SEPARATOR \'|\') AS recurring_commission_ids,
                        SUM(mrc.charge_per_transaction) AS total_amount,
                        COUNT(mrc.recurring_commission_id) AS successful_transaction
                FROM tbl_recurring_commission mrc
                INNER JOIN tbl_merchant m ON m.merchant_row_id = mrc.merchant_row_id
                WHERE mrc.is_charged != 1
                AND mrc.created_on > \'2010-09-15\'
                AND m.package_id = 1
                GROUP BY mrc.merchant_row_id
                HAVING total_amount >= ' . $adminBasicSetup->recurring_commission_minimum_charge;
        $resultSet = $this->db->query($sql);
		$result = $resultSet->result_array();
		return $result;
    }

    function updateRecurringCommissionChargeStatus($recurring_commission_id, $status)
    {
        $this->db->set('is_charged', $status);
        $this->db->where('recurring_commission_id', $recurring_commission_id);
        $this->db->update('tbl_recurring_commission');
    }

    function lastTransactionTimeDifference($recurring_orders_id)
    {
        $sql = "SELECT IFNULL(DATEDIFF(NOW(), date_of_next_charge), 0) AS time_diff
                FROM tbl_recurring_orders
                WHERE recurring_orders_id = $recurring_orders_id";

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

        if($row)
        {
            return isset($row->time_diff) ? $row->time_diff : 0;
        }
        return 0;
    }

    function getRecurringCommissionPaymentById($commissionPaymentId)
    {
        $sql = "SELECT *,
                       DATE_FORMAT(date_of_transaction,'".$this->config->item('show_datetime_fmt')."') AS charged_on
                FROM tbl_recurring_commission_payment
                WHERE payment_id = $commissionPaymentId";

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

    function getRecurringCouponDiscount($order_row_id, $recurring_orders_id, $recurring_shipping_charge = 0, $updateUsageCount = true)
    {
        $couponDiscount = 0.00;
        $getInstance = &get_instance();
        $getInstance->load->model('products/Coupon_model', 'Coupon_model', TRUE);
        $couponDetails = $getInstance->Coupon_model->getCouponDetailsByOrderId($order_row_id);

        if($couponDetails)
        {
           foreach($couponDetails as $aCoupon)
           {
               $thisCouponDiscount = 0.00;
                if($aCoupon['recurring_coupon_applied'])
                {
                    //coupon has been applied to this order and recurring coupon discount is allowed
                    $thisCouponDiscount = $this->checkRecurringCouponCondition($recurring_orders_id, $order_row_id, $aCoupon);

                    if($thisCouponDiscount > 0.00 && $updateUsageCount)
                    {
                        $this->db->query('UPDATE tbl_coupon SET coupon_applied = coupon_applied+1 WHERE coupon_row_id = '.$aCoupon['coupon_row_id']);
                    }
                    $couponDiscount += $thisCouponDiscount;
                }
                else
                {
                    $couponDiscount += 0.00;
                }
            }

        }

        return $couponDiscount;
    }

    function checkRecurringCouponCondition($recurring_orders_id, $order_row_id, $couponInfo)
    {
        //get recurring order price
        $recurringDetails = $this->getRecurringOrderDetails($recurring_orders_id, $order_row_id);

        $currentDate = getFormattedDate(date($this->config->item('coupon_datetime_fmt')));
        $couponStartDate = getFormattedDate($couponInfo['start_date']);
        $couponEndDate = getFormattedDate($couponInfo['end_date']);
        $productTotal = $recurringDetails['recurring_price'] + $recurringDetails['recurring_shipping_charge'] + $recurringDetails['recurring_sales_tax'];

        $getInstance = &get_instance();
        $getInstance->load->model('carts/Shopping_cart_model', 'shopping_cart_model', TRUE);

        //**************check coupon availability - start (By Tania Noor)*************//
        //get temporary order coupon count for this coupon
        $tempCouponCount = $getInstance->shopping_cart_model->getTemporaryCouponCount($couponInfo['coupon_row_id']);
        $couponCount = $couponInfo['coupon_applied'] + $tempCouponCount;

        //**************check coupon availability - end (By Tania Noor)*************//

        $returnDiscount = 0.00;

        if($getInstance->shopping_cart_model->dateDiff('-',$currentDate , $couponStartDate))
        {
           $returnDiscount = 0.00;
        }
        else if (($couponEndDate!='00-00-0000')&&($getInstance->shopping_cart_model->dateDiff('-',$couponEndDate,$currentDate)))
        {
            $returnDiscount = 0.00;
        }
        else if($productTotal < $couponInfo['minimum_purchase'])
        {
            $returnDiscount = 0.00;
        }
        else if($couponInfo['max_available'] <= $couponCount && $couponInfo['max_available'])
        {
            $returnDiscount =  0.00;
        }
        else
        {
            $returnDiscount = $this->chargeRecurringCoupon($recurringDetails, $couponInfo);
        }
        return $returnDiscount;
    }

    function getRecurringOrderDetails($recurring_orders_id, $order_row_id)
    {
        $this->db->select('*');
        $this->db->from(' tbl_recurring_orders ');
        $this->db->where('recurring_orders_id',$recurring_orders_id);
        $this->db->where('order_row_id',$order_row_id);
        $resultSet = $this->db->get();
        return $resultSet->row_array();
    }

    function chargeRecurringCoupon($recurringDetails, $couponInfo)
    {
        $getInstanceOfCart = &get_instance();
        $getInstanceOfCart->load->model('products/Coupon_model', 'Coupon_model', TRUE);

        $discount_amount = 0;
        $data['error_message'] = ' ';
        $data['discount_type'] = $couponInfo['discount_type'];

        $coupon_row_id = $couponInfo['coupon_row_id'];
        $coupon_type = $couponInfo['coupon_type'];

        $coupnDetailsArray = $getInstanceOfCart->Coupon_model->getCouponDetails($coupon_row_id);
        foreach($coupnDetailsArray as $item)
        {
            if($item['is_bundle'])
                $supported_bundle_row_id_array[] = $item['related_row_id'];
            else
                $coupon_supported_row_id_array[] = $item['related_row_id'];
        }

        switch($coupon_type)
        {
            case $this->config->item('categoryType_coupon'):
                $getInstanceOfCart->load->model('products/Product_model', 'product_model', TRUE);
                $product = $getInstanceOfCart->product_model->getProduct($recurringDetails['product_row_id'], $couponInfo['merchant_row_id'], array('category_id'));
                if(in_array($product['category_id'], $coupon_supported_row_id_array))
                {
                    $discount_amount = $this->calculateRecurringCouponDiscount($recurringDetails, $couponInfo);
                }
                //end of category type coupon
                break;
            case $this->config->item('productType_coupon'):
            case $this->config->item('affiliate_credit_coupon'):
                if(in_array($recurringDetails['product_row_id'], $coupon_supported_row_id_array))
                {
                    $discount_amount = $this->calculateRecurringCouponDiscount($recurringDetails, $couponInfo);
                }
                else if($supported_bundle_row_id_array)
                {
                    $productOrderDetails = $this->checkProductInBundles($recurringDetails['product_row_id'], $recurringDetails['order_row_id']);
                    if($productOrderDetails['bundle_id'] > 0  && in_array($productOrderDetails['bundle_id'], $supported_bundle_row_id_array))
                    {
                        $discount_amount = $this->calculateRecurringCouponDiscount($recurringDetails, $couponInfo);
                    }
                }
                break;
            case $this->config->item('orderType_coupon'):
                $discount_amount = $this->calculateRecurringCouponDiscount($recurringDetails, $couponInfo);
                break;
        }
        return $discount_amount;
    }

    function checkProductInBundles($product_row_id, $order_row_id)
    {
        $sql = " SELECT bundle_id FROM tbl_order_details WHERE order_row_id = {$order_row_id} AND product_row_id={$product_row_id} ORDER BY bundle_id DESC";
        $query = $this->db->query($sql);
        return $query->row_array();
    }

    function calculateRecurringCouponDiscount($recurringDetails, $couponInfo)
    {
        $amount = 0;
        $getInstance = &get_instance();

        switch($couponInfo['discount_type'])
        {
            case $this->config->item('discount_type_percent'):
                $amount = ($recurringDetails['recurring_price'] * $couponInfo['discount_amount'])/100;
                break;

            case $this->config->item('discount_type_fixed'):
                $amount = $couponInfo['discount_amount'];
                break;

            case $this->config->item('discount_type_no_tax'):
                $amount = $recurringDetails['recurring_sales_tax'];
                break;

            case $this->config->item('discount_type_free_shipping'):
                $amount = $recurringDetails['recurring_shipping_charge'];
                break;
        }
        return $amount;
    }

    function getRecurringToSendCCExpireNotification()
    {
        $currDate = date('Y-m-d');
        $sql = 'SELECT  ro.recurring_orders_id,
                        ro.merchant_row_id,
                        ro.product_row_id,
                        ro.product_quantity,
                        ro.order_row_id,
                        ro.order_details_id,
                        ro.customer_id,
                        ro.recurring_price,
                        ro.recurring_shipping_charge,
                        ro.recurring_cycle,
                        ro.recurring_count,
                        ro.recurring_start_duration,
                        ro.date_of_next_charge,
                        ro.date_of_last_charge,
                        ro.successfull_attempted,
                        ro.failure_attempted,
                        ro.payment_gateway,
                        c.customer_id,
                        c.first_name,
                        c.last_name,
                        DATE_FORMAT(ro.date_of_next_charge,\'%Y\') AS next_charge_year,
                        DATE_FORMAT(ro.date_of_next_charge,\'%m\') AS next_charge_month,
                        DATE_FORMAT(ro.date_of_next_charge,\'%d\') AS next_charge_day,
                        o.customer_email,
                        o.card_exp_month,
                        o.card_exp_year,
                        DATE_FORMAT(ro.date_of_next_charge, \'%Y-%m-%d\') AS next_subscription_date,
                        DATEDIFF( DATE_FORMAT(ro.date_of_next_charge, \'%Y-%m-%d\'), \'' . $currDate . '\' ) AS date_diff
                  FROM tbl_recurring_orders ro
                  INNER JOIN tbl_order AS o ON(ro.order_row_id = o.order_row_id)
                  INNER JOIN tbl_gateway_setup gs ON gs.merchant_row_id = ro.merchant_row_id AND gs.real_time_gateway != ' . $this->config->item('payment_gateway_paypal_website_payment_pro') . '
                  INNER JOIN tbl_customer c ON c.customer_id = ro.customer_id                  
                  WHERE ro.active_status = 1
                  AND ro.is_declined = 0
                  AND ro.is_finished = 0
                  AND ro.payment_gateway = 2
                  AND (ro.recurring_count = 0 OR ro.recurring_count <> ro.successfull_attempted)
                  AND (ro.failure_attempted = 0 AND DATE(ro.date_of_next_charge) > CURDATE())
                  AND (
                       DATEDIFF(DATE_FORMAT(ro.date_of_next_charge, \'%Y-%m-%d\'), \'' . $currDate . '\') = 30 OR
                       DATEDIFF(DATE_FORMAT(ro.date_of_next_charge, \'%Y-%m-%d\'), \'' . $currDate . '\') = 15 OR
                       DATEDIFF(DATE_FORMAT(ro.date_of_next_charge, \'%Y-%m-%d\'), \'' . $currDate . '\') = 7 OR
                       DATEDIFF(DATE_FORMAT(ro.date_of_next_charge, \'%Y-%m-%d\'), \'' . $currDate . '\') = 3
                      )
                  AND ro.deletion_status!=1
                  LIMIT 100';

        $query = $this->db->query($sql);
        return $query->result();
    }
    
    function getRecurringPaymentHistory($orderRowId, $recurringOrderId, $dataOfCharge, $fields = '*')
    {
        $sql = "SELECT {$fields} FROM tbl_recurring_transaction_history WHERE 
                order_row_id = " . $orderRowId ." AND                  
                recurring_orders_id = " .  $recurringOrderId . " AND  
                date_of_charge = '" . $dataOfCharge . "'";
        $query = $this->db->query($sql);
        return $query->row_array();        
    }
}
?>