<?php

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of class
 *
 * @author Hoang Bien<hoangbien264@gmail.com>
 */
if (!defined('AFFILIATES_TP')) {
    define('AFFILIATES_TP', 'aff_');
}

class ClassProcessCronjob {

    /**
     * Lay tong kiem duoc tu he thong affiliates
     * @global type $wpdb
     * @param type $customer_id
     * @param type $currency_id
     * @return boolean
     */
    public static function getTotalAmountEarnOfCustomer($customer_id, $currency_id = 'AUD') {
        if (!$customer_id)
            return false;
        global $wpdb;
        $total = 0;
        $strPrefix = $wpdb->prefix . AFFILIATES_TP;
        $query .= "SELECT {$strPrefix}affiliates_users.affiliate_id FROM {$strPrefix}affiliates_users"
                . " LEFT JOIN {$strPrefix}affiliates ON {$strPrefix}affiliates_users.affiliate_id = {$strPrefix}affiliates.affiliate_id"
                . " WHERE {$strPrefix}affiliates_users.user_id = '$customer_id' AND {$strPrefix}affiliates.status = 'active'";

        $affiliate_id = $wpdb->get_var($query);
        if ($affiliate_id > 0) {
            $query = "SELECT SUM(amount) total, currency_id FROM {$strPrefix}referrals WHERE `affiliate_id` = '$affiliate_id' AND `status` = 'accepted'";
            $query .= " GROUP BY currency_id";

            $totals = $wpdb->get_results($query);
            if ($totals) {
                $result = array();
                foreach ($totals as $total) {
                    if (( $total->currency_id !== null ) && ( $total->total !== null )) {
                        $result[$total->currency_id] = $total->total;
                    }
                }
                if ($currency_id != '') {
                    return isset($result[$currency_id]) ? $result[$currency_id] : 0;
                } else {
                    return $result;
                }
            } else {
                return false;
            }
        }
        return false;
    }

    /**
     * Lay tong da su dung cua mot customer
     * @global type $wpdb
     * @param type $customer_id
     * @param type $currency_id
     * @return boolean
     */
    public static function getTotalUsedOfCustomer($customer_id, $currency_id = 'AUD') {
        global $wpdb;
        if (!$customer_id)
            return false;
        $strPrefix = $wpdb->prefix . AFFILIATES_TP;
        $query = "SELECT `amount` FROM {$strPrefix}referrals_used WHERE `user_id` = '$customer_id' AND `currency_id` = '$currency_id' ";
        $result = $wpdb->get_var($query);
        return $result;
    }

    /**
     * Lay tong so tien kiem duoc chua duoc su dung cua customer
     * @param type $customer_id
     * @param type $currency_id
     * @return int
     */
    public static function getTotalRemainOfCustomer($customer_id, $currency_id = 'AUD') {
        if (!$customer_id)
            return 0;
        $totalEarn = self::getTotalAmountEarnOfCustomer($customer_id, $currency_id);
        $totalUsed = self::getTotalUsedOfCustomer($customer_id, $currency_id);
        return round($totalEarn - $totalUsed, 2);
    }

    public static function getCustomerIdByAffiliateId($affiliate_id) {
        global $wpdb;
        $strPrefix = $wpdb->prefix . AFFILIATES_TP;
        $query .= "SELECT {$strPrefix}affiliates_users.user_id FROM {$strPrefix}affiliates_users"
                . " LEFT JOIN {$strPrefix}affiliates ON {$strPrefix}affiliates_users.affiliate_id = {$strPrefix}affiliates.affiliate_id"
                . " WHERE {$strPrefix}affiliates_users.affiliate_id = '$affiliate_id' AND {$strPrefix}affiliates.status = 'active'";
        return $wpdb->get_var($query);
    }

    public static function createCouponFromEarnMoneyInAffiliateSystem($type = 'month') {
        global $user_id_created;
        $listSubscriptions = self::getAllSubscriptionWillNextPayment($type);
        if (!empty($listSubscriptions)) {
            foreach ($listSubscriptions as $key => $item) {
// thuc hien check remove old coupon truoc khi them moi
                global $wpdb;
                $userCouponTable = $wpdb->prefix . 'woocommerce_coupon_affiliates';
                $query = "SELECT * FROM `$userCouponTable` WHERE `subscriptions_id` = '{$item->ID}' AND `user_id` = '{$item->customer_id}' AND `used` = '0'";
                $results = $wpdb->get_results($query);
                if (!empty($results)) {
                    self::removeCouponOfUserFromSubscription($item->ID, $item->customer_id);
                }
                $coupon_id = self::createCouponForUser($item->ID, $item->customer_id, $user_id_created, 'AUD');
                if ($coupon_id > 0) {
                    self::addCouponOfUserToAccountSubscription($item->ID, $item->customer_id, $coupon_id);
                }
            }
        }
    }

    /**
     * Lay tat cac cac subscription se thanh toan trong ngay tiep theo va chua duoc set coupon
     * @global type $wpdb
     * @return type
     */
    public static function getAllSubscriptionWillNextPayment($type = 'month') {
        if ($type == 'hours') {
            echo "Current date time:" . date('Y-m-d H:i:s') . "<br/>";

            $currentDate = strtotime("+10 hours", strtotime(date('Y-m-d H:i:s')));
            $currentDate = date('Y-m-d H:i:s', $currentDate);
//echo get_post_meta(984, '_schedule_next_payment', true) . "<br/>";
            echo "Current date time after add 10 hours:" . $currentDate;
            $currentDate1 = date('Y-m-d H:i:s'); //strtotime(date('Y-m-d H:i:s'));
        } else {
            $currentDate = strtotime("+1 day", strtotime(date('Y-m-d')));
            $currentDate1 = strtotime(date('Y-m-d'));
        }
        global $wpdb;
        $userCouponTable = $wpdb->prefix . 'woocommerce_coupon_affiliates';
        $query = "SELECT p.*, pm1.meta_value as nextdatepayment,pm2.meta_value as enddatepayment,pm3.meta_value as customer_id,cT.* FROM {$wpdb->posts} p" //subscriptions_id
                . " INNER JOIN {$wpdb->postmeta} pm1 ON p.ID = pm1.post_id AND pm1.meta_key = '_schedule_next_payment'"
                . " INNER JOIN {$wpdb->postmeta} pm2 ON p.ID = pm2.post_id AND pm2.meta_key = '_schedule_end'"
                . " INNER JOIN {$wpdb->postmeta} pm3 ON p.ID = pm3.post_id AND pm3.meta_key = '_customer_user'";
        if ($type == 'hours') {
            $query .= " LEFT JOIN `$userCouponTable` cT ON (p.ID = cT.subscriptions_id AND UNIX_TIMESTAMP(DATE_FORMAT(pm1.meta_value,'%Y-%m-%d %T')) <= UNIX_TIMESTAMP(DATE_FORMAT(cT.date_payment,'%Y-%m-%d %T')))";
        } else {
            $query .= " LEFT JOIN `$userCouponTable` cT ON (p.ID = cT.subscriptions_id AND UNIX_TIMESTAMP(DATE_FORMAT(pm1.meta_value,'%Y-%m-%d')) <= UNIX_TIMESTAMP(DATE_FORMAT(cT.date_payment,'%Y-%m-%d')))";
        }
        $query .= " WHERE p.`post_status` = 'wc-active' AND p.`post_type` = 'shop_subscription'";

        if ($type == 'hours') {

            $query .= " AND UNIX_TIMESTAMP(DATE_FORMAT(pm1.meta_value,'%Y-%m-%d %T')) > UNIX_TIMESTAMP('$currentDate1')"
                    . " AND UNIX_TIMESTAMP(DATE_FORMAT(pm1.meta_value,'%Y-%m-%d %T')) <= UNIX_TIMESTAMP('$currentDate')"
                    . " AND (UNIX_TIMESTAMP(DATE_FORMAT(pm2.meta_value,'%Y-%m-%d %T')) > UNIX_TIMESTAMP('$currentDate1') OR pm2.meta_value = '' OR pm2.meta_value IS NULL OR pm2.meta_value = 0)";
        } else {
            $query .= " AND UNIX_TIMESTAMP(DATE_FORMAT(pm1.meta_value,'%Y-%m-%d')) > '$currentDate1'"
                    . " AND UNIX_TIMESTAMP(DATE_FORMAT(pm1.meta_value,'%Y-%m-%d')) <= '$currentDate'"
                    . " AND (UNIX_TIMESTAMP(DATE_FORMAT(pm2.meta_value,'%Y-%m-%d')) > '$currentDate1' OR pm2.meta_value = '' OR pm2.meta_value IS NULL OR pm2.meta_value = 0)";
        }
        $query .= " AND (cT.subscriptions_id IS NULL OR cT.subscriptions_id = 0) LIMIT 0, 2000";
        $results = $wpdb->get_results($query);

        if (!empty($results))
            return $results;
        return array();
    }

    public static function removeCouponInSubscription($type = 'month') {
        $allSubscription = self::getAllSubscriptionNeedRemoveCoupon($type);
        if (!empty($allSubscription)) {
            foreach ($allSubscription as $key => $item) {
                self::removeCouponOfUserFromSubscription($item->ID, $item->customer_id);
            }
        }
    }

    public static function getAllSubscriptionNeedRemoveCoupon($type = 'month') {
//$currentDate = strtotime("-1 day", strtotime(date('Y-m-d H:i:s')));
//$currentDate = strtotime(date('Y-m-d H:i:s'));
        if ($type == 'hours') {
            $currentDate = strtotime("+11 hours", strtotime(date('Y-m-d H:i:s')));
            $currentDate = date('Y-m-d H:i:s', $currentDate);
// $query = ""
//echo get_post_meta(984, '_schedule_next_payment', true) . "<br/>";
//echo $currentDate . "<br/>";
//$currentDate = strtotime("+8 hours", strtotime(date('Y-m-d H:i:s')));
        } else {
            $currentDate = strtotime("+1 day", strtotime(date('Y-m-d')));
        }
        global $wpdb;
        $userCouponTable = $wpdb->prefix . 'woocommerce_coupon_affiliates';
        $tableOderItems = $wpdb->prefix . 'woocommerce_order_items';
        $query = "SELECT p.*, pm1.meta_value as nextdatepayment,pm3.meta_value as customer_id FROM {$wpdb->posts} p"
                . " INNER JOIN {$wpdb->postmeta} pm1 ON p.ID = pm1.post_id AND pm1.meta_key = '_schedule_next_payment'"
                . " INNER JOIN {$wpdb->postmeta} pm3 ON p.ID = pm3.post_id AND pm3.meta_key = '_customer_user'"
                . " INNER JOIN ( SELECT oT.order_id, cT.coupon_id, cT.date_payment,cT.used  FROM $tableOderItems oT"
                . " INNER JOIN {$wpdb->posts} p1 ON (p1.post_title = oT.order_item_name AND p1.post_type = 'shop_coupon')"
                . " INNER JOIN `$userCouponTable` cT ON (p1.ID = cT.coupon_id AND cT.used = '0' )) as tmp1 ON p.ID = tmp1.order_id";
        if ($type == 'hours') {
            $query .= " WHERE UNIX_TIMESTAMP(DATE_FORMAT(pm1.meta_value,'%Y-%m-%d %T')) > UNIX_TIMESTAMP('$currentDate')"
                    . " AND UNIX_TIMESTAMP(DATE_FORMAT(pm1.meta_value,'%Y-%m-%d %T')) > UNIX_TIMESTAMP(DATE_FORMAT(tmp1.date_payment,'%Y-%m-%d %T'))";
        } else {
            $query .= " WHERE UNIX_TIMESTAMP(DATE_FORMAT(pm1.meta_value,'%Y-%m-%d')) > '$currentDate'"
                    . " AND UNIX_TIMESTAMP(DATE_FORMAT(pm1.meta_value,'%Y-%m-%d')) > UNIX_TIMESTAMP(DATE_FORMAT(tmp1.date_payment,'%Y-%m-%d'))";
        }
        $query .= " AND tmp1.coupon_id > 0 LIMIT 0, 2000"; // AND tmp1.used = '0'

        $results = $wpdb->get_results($query);
        if (!empty($results))
            return $results;
        return array();
    }

    public static function updateTotalUsedOfUser($customer_id, $amount, $currency_id = 'AUD', $last_amount) {
        if (!$customer_id || !$amount)
            return false;
        global $wpdb;
        $strPrefix = $wpdb->prefix . AFFILIATES_TP;
        $query = "SELECT id FROM {$strPrefix}referrals_used WHERE `user_id` = '$customer_id' AND `currency_id` = '$currency_id' ";
        $id = $wpdb->get_var($query);
        if ($id > 0) {
//Update amount
            $query = "UPDATE `{$strPrefix}referrals_used` SET `amount`= (`amount` + $amount), `last_amount` = '$last_amount' WHERE `user_id`='$customer_id' AND `currency_id` = '$currency_id';";
        } else {
            $query = "INSERT INTO `{$strPrefix}referrals_used` (`user_id`, `amount`, `currency_id`, `last_amount`) VALUES ('$customer_id', '$amount', '$currency_id', '$last_amount');";
//Insert new recod
        }
        $result = $wpdb->query($query);
        if ($result)
            return true;
        return false;
    }

    public static function createCoupon($data, $user_id_created) {
        if (empty($data))
            return false;

        global $wpdb;

        try {
            if (!isset($data['coupon'])) {
                return false;
//throw new WC_API_Exception('woocommerce_api_missing_coupon_data', sprintf(__('No %1$s data specified to create %1$s', 'woocommerce'), 'coupon'), 400);
            }

//            $data = $data['coupon'];
//
//            // Check if coupon code is specified
//            if (!isset($data['code'])) {
//                return false;
//                //throw new WC_API_Exception('woocommerce_api_missing_coupon_code', sprintf(__('Missing parameter %s', 'woocommerce'), 'code'), 400);
//            }
            $coupon_code = $data['coupon'];
// Check for duplicate coupon codes
            $coupon_found = $wpdb->get_var($wpdb->prepare("
				SELECT $wpdb->posts.ID
				FROM $wpdb->posts
				WHERE $wpdb->posts.post_type = 'shop_coupon'
				AND $wpdb->posts.post_status = 'publish'
				AND $wpdb->posts.post_title = '%s'
			 ", $coupon_code));

            if ($coupon_found) {
                return false;
//throw new WC_API_Exception('woocommerce_api_coupon_code_already_exists', __('The coupon code already exists', 'woocommerce'), 400);
            }

            $defaults = array(
                'type' => 'recurring_fee',
                'amount' => 0,
                'individual_use' => false,
                'product_ids' => array(),
                'exclude_product_ids' => array(),
                'usage_limit' => '',
                'usage_limit_per_user' => '',
                'limit_usage_to_x_items' => '',
                'usage_count' => '',
                'expiry_date' => '',
                'enable_free_shipping' => false,
                'product_category_ids' => array(),
                'exclude_product_category_ids' => array(),
                'exclude_sale_items' => false,
                'minimum_amount' => '',
                'maximum_amount' => '',
                'customer_emails' => array(),
                'description' => ''
            );

            $coupon_data = wp_parse_args($data, $defaults);

// Validate coupon types
            if (!in_array(wc_clean($coupon_data['type']), array_keys(wc_get_coupon_types()))) {
                return false;
//throw new WC_API_Exception('woocommerce_api_invalid_coupon_type', sprintf(__('Invalid coupon type - the coupon type must be any of these: %s', 'woocommerce'), implode(', ', array_keys(wc_get_coupon_types()))), 400);
            }

            $new_coupon = array(
                'post_title' => $coupon_code,
                'post_content' => '',
                'post_status' => 'publish',
                'post_author' => $user_id_created,
                'post_type' => 'shop_coupon',
                'post_excerpt' => $coupon_data['description']
            );

            $id = wp_insert_post($new_coupon, true);

            if (is_wp_error($id)) {
//throw new WC_API_Exception('woocommerce_api_cannot_create_coupon', $id->get_error_message(), 400);
                return false;
            }

// Set coupon meta
            update_post_meta($id, 'discount_type', $coupon_data['type']);
            update_post_meta($id, 'coupon_amount', wc_format_decimal($coupon_data['amount']));
            update_post_meta($id, 'individual_use', ( true === $coupon_data['individual_use'] ) ? 'yes' : 'no' );
            update_post_meta($id, 'product_ids', implode(',', array_filter(array_map('intval', $coupon_data['product_ids']))));
            update_post_meta($id, 'exclude_product_ids', implode(',', array_filter(array_map('intval', $coupon_data['exclude_product_ids']))));
            update_post_meta($id, 'usage_limit', absint($coupon_data['usage_limit']));
            update_post_meta($id, 'usage_limit_per_user', absint($coupon_data['usage_limit_per_user']));
            update_post_meta($id, 'limit_usage_to_x_items', absint($coupon_data['limit_usage_to_x_items']));
            update_post_meta($id, 'usage_count', absint($coupon_data['usage_count']));
            update_post_meta($id, 'expiry_date', self::fgc_get_coupon_expiry_date(wc_clean($coupon_data['expiry_date'])));
            update_post_meta($id, 'free_shipping', ( true === $coupon_data['enable_free_shipping'] ) ? 'yes' : 'no' );
            update_post_meta($id, 'product_categories', array_filter(array_map('intval', $coupon_data['product_category_ids'])));
            update_post_meta($id, 'exclude_product_categories', array_filter(array_map('intval', $coupon_data['exclude_product_category_ids'])));
            update_post_meta($id, 'exclude_sale_items', ( true === $coupon_data['exclude_sale_items'] ) ? 'yes' : 'no' );
            update_post_meta($id, 'minimum_amount', wc_format_decimal($coupon_data['minimum_amount']));
            update_post_meta($id, 'maximum_amount', wc_format_decimal($coupon_data['maximum_amount']));
            update_post_meta($id, 'customer_email', array_filter(array_map('sanitize_email', $coupon_data['customer_emails'])));

// do_action('woocommerce_api_create_coupon', $id, $data);

            return $id; //$this->get_coupon($id);
        } catch (WC_API_Exception $e) {
            return false; //new WP_Error($e->getErrorCode(), $e->getMessage(), array('status' => $e->getCode()));
        }
    }

    public static function fgc_get_coupon_expiry_date($expiry_date) {
        if ('' != $expiry_date) {
            return date('Y-m-d', strtotime($expiry_date));
        }

        return '';
    }

    public static function createCouponForUser($sub_id, $customer_id, $user_create_id, $currency_id = 'AUD') {
        global $wpdb;
        $data['coupon'] = self::renderCouponCode();
        $data['amount'] = self::getAmountForCoupon($sub_id, $customer_id);
//$data['usage_limit'] = 2;
        if (!$data['coupon'] || !$data['amount'])
            return false;

        $coupon_id = self:: createCoupon($data, $user_create_id);

        if ($coupon_id > 0) {
            $userCouponTable = $wpdb->prefix . 'woocommerce_coupon_affiliates';
            $dateCreate = date('Y-m-d H:i:s');
            $query = "INSERT INTO $userCouponTable (`coupon_id`, `user_id`,`subscriptions_id`,`date_create`,`date_payment`,`used`)"
                    . " VALUES ('$coupon_id','$customer_id','0','$dateCreate','0000-00-00 00:00:00','0')";
            if (($result = $wpdb->query($query))) {
                self::updateTotalUsedOfUser($customer_id, $data['amount'], $currency_id, $data['amount']);
                return $coupon_id;
            }
            return false;
        }
        return false;
    }

    public static function renderCouponCode() {
//date('ymdhis');
        return date('His') . self::randDomChar(2) . date('ymd');
    }

    function randDomChar($length = 10) {

        $string = '';
// You can define your own characters here.
        $characters = "ABCDEFHJKLMNPRTVWXYZabcdefghijklmnopqrstuvwxyz";

        for ($p = 0; $p < $length; $p++) {
            $string .= $characters[mt_rand(0, strlen($characters) - 1)];
        }

        return $string;
    }

    public static function getAmountForCoupon($sub_id, $customer_id) {

        $totalRemainOfCustomer = self::getTotalRemainOfCustomer($customer_id);

        $totalSub = self::getTotalOfSubscription($sub_id);

        if ($totalRemainOfCustomer > 0) {
            if ($totalRemainOfCustomer <= $totalSub) {
                return $totalRemainOfCustomer;
            } else {
                return $totalSub;
            }
        }
        return 0;
    }

    public static function getTotalOfSubscription($sub_id) {
        if (!$sub_id)
            return 0;
//        global $wpdb;
//        $tableOderItems = $wpdb->prefix . 'woocommerce_order_items'; //order_item_name, order_item_type,order_id [code, coupon, id]
//        $tableOderItemMeta = $wpdb->prefix . 'woocommerce_order_itemmeta';
//        $query = "SELECT order_item_id FROM $tableOderItems WHERE `order_item_type` = 'line_item' AND `order_id` = '$sub_id'";
//        $resultOrderItemsProduct = $wpdb->get_var($query);
//        if ($resultOrderItemsProduct > 0) {
//            //_line_subtotal
//            $query = "SELECT meta_value FROM $tableOderItemMeta WHERE `meta_key` = '_line_subtotal' AND `order_item_id` = '$resultOrderItemsProduct'";
//            $amount = $wpdb->get_var($query);
//        }
//        if (!$amount) {
        $old_discount = get_post_meta($sub_id, '_cart_discount', true);
        $amount = get_post_meta($sub_id, '_order_total', true);
        $amount = $old_discount + $amount;
// }
        if ($amount > 0) {
            return $amount;
        }
        return 0;
    }

    public static function getCouponNotUseOfUser($customer_id) {
        global $wpdb;
        $userCouponTable = $wpdb->prefix . 'woocommerce_coupon_affiliates';
        $query = "SELECT * FROM $wpdb->posts p "
                . "INNER JOIN `$userCouponTable` cT ON p.ID = cT.coupon_id"
                . " WHERE cT.subscriptions_id = 0 OR cT.subscriptions_id IS NULL";
        $result = $wpdb->get_results($query);
        if (!empty($result))
            return $result[0];
        return false;
    }

    /**
     * Thuc hien add Coupon toi Subscription cua User
     * @global type $wpdb
     * @param type $sub_id
     * @param type $customer_id
     * @param type $coupon_id
     * @return boolean
     */
    public static function addCouponOfUserToAccountSubscription($sub_id, $customer_id, $coupon_id) {
        global $wpdb;
        $coupon_code = $wpdb->get_var("SELECT post_title FROM $wpdb->posts WHERE `ID` = '$coupon_id' AND `post_status` = 'publish' AND `post_type` = 'shop_coupon'");
        self::createLog("Coupon code: $coupon_code");
//$number_apply_coupon = get_post_meta($coupon_id, 'usage_count', true);
        if ($coupon_code != '') {// && $number_apply_coupon < 1
            $tableOderItems = $wpdb->prefix . 'woocommerce_order_items'; //order_item_name, order_item_type,order_id [code, coupon, id]
            $tableOderItemMeta = $wpdb->prefix . 'woocommerce_order_itemmeta'; //order_item_id,meta_key,meta_value [discount_amount, discount_amount_tax]
            $userCouponTable = $wpdb->prefix . 'woocommerce_coupon_affiliates';
            $query = "SELECT * FROM $tableOderItems WHERE `order_item_type` = 'coupon' AND `order_id` = '$sub_id'";
            $resultOrderItems = $wpdb->get_results($query);
            if (!empty($resultOrderItems)) {
                $query = "UPDATE `$tableOderItems` SET `order_item_name` = '$coupon_code'"
                        . " WHERE `order_item_type` = 'coupon' AND `order_id` = '$sub_id'";
            } else {
                $query = "INSERT INTO $tableOderItems (`order_item_name`, `order_item_type`,`order_id`)"
                        . " VALUES ('$coupon_code','coupon','$sub_id')";
            }
            self::createLog("Query Order term: $query");
            $resultInsert = $wpdb->query($query);
            try {

//                if ($resultInsert) {
                $is_update = false;
                $old_discount = 0;
                $order_item_id = $wpdb->get_var("SELECT order_item_id FROM `$tableOderItems` WHERE `order_item_name` = '$coupon_code' AND `order_item_type` = 'coupon' AND `order_id` = '$sub_id'");

                if ($order_item_id > 0) {
                    $discount_amount = get_post_meta($coupon_id, 'coupon_amount', true);
                    $discount_amount_tax = 0; //get_post_meta($coupon_id, 'coupon_amount', true);
                    $date_payment = get_post_meta($sub_id, '_schedule_next_payment', true); //se lay tu subscription
                    $query = "SELECT * FROM $tableOderItemMeta WHERE `order_item_id` = '$order_item_id' AND `meta_key` = 'discount_amount'";
                    $resultItems = $wpdb->get_results($query);

                    if (!empty($resultItems)) {
                        $is_update = true;
                        $old_discount = isset($resultItems[0]->meta_value) ? $resultItems[0]->meta_value : 0;
                        self::createLog("Old discount: $old_discount");
                        $query = "UPDATE `$tableOderItemMeta` SET `meta_value` = '$discount_amount'"
                                . " WHERE `order_item_id` = '$order_item_id' AND `meta_key` = 'discount_amount'";
                    } else {
                        $query = "INSERT INTO $tableOderItemMeta (`order_item_id`, `meta_key`,`meta_value`)"
                                . " VALUES ('$order_item_id','discount_amount','$discount_amount')";
                    }
                    try {
//thuc hien update hoac them moi discount
                        self::createLog("$tableOderItemMeta: $query");
                        $result = $wpdb->query($query);
//line_item
//Danh cho product
                        $query = "SELECT order_item_id FROM $tableOderItems WHERE `order_item_type` = 'line_item' AND `order_id` = '$sub_id'";
                        $resultOrderItemsProduct = $wpdb->get_var($query);
                        if ($resultOrderItemsProduct > 0) {
//_line_subtotal
                            $query = "SELECT meta_value FROM $tableOderItemMeta WHERE `meta_key` = '_line_subtotal' AND `order_item_id` = '$resultOrderItemsProduct'";
                            $subTotal = $wpdb->get_var($query);
                            $newLineTotal = round($subTotal - $discount_amount, 2);
                            $query = "UPDATE `$tableOderItemMeta` SET `meta_value` = '$newLineTotal'"
                                    . " WHERE `order_item_id` = '$resultOrderItemsProduct' AND `meta_key` = '_line_total'";
                            $wpdb->query($query);
                            self::createLog("_line_total: $query");
//_line_total
                        }
//Tong total tong thanh toan
// if ($result) {
                        $query = "UPDATE `$userCouponTable` SET `subscriptions_id` = '$sub_id', `date_payment` = '$date_payment' WHERE `coupon_id` = '$coupon_id' AND `user_id` = '$customer_id'";
                        $wpdb->query($query);
                        self::createLog("New Discount: $discount_amount");
                        update_post_meta($sub_id, '_cart_discount', $discount_amount);
                        update_post_meta($sub_id, '_cart_discount_tax', $discount_amount_tax);
                        $currnentTotal = get_post_meta($sub_id, '_order_total', true);

                        if ($is_update == true && $old_discount > 0) {
                            $newTotal = round(($currnentTotal - (($discount_amount + $discount_amount_tax) - $old_discount)), 2);
                        } else {
                            $newTotal = round(($currnentTotal - ($discount_amount + $discount_amount_tax)), 2);
                        }
                        self::createLog("_order_total: $newTotal");
                        update_post_meta($sub_id, '_order_total', $newTotal);
//}
                    } catch (Exception $exc) {
                        echo $exc->getTraceAsString();
                    }
                    $query = "SELECT * FROM $tableOderItemMeta WHERE `order_item_id` = '$order_item_id' AND `meta_key` = 'discount_amount_tax'";
                    $resultItems = $wpdb->get_results($query);
                    if (!empty($resultItems)) {
                        $query = "UPDATE `$tableOderItemMeta` SET `meta_value` = '$discount_amount_tax'"
                                . " WHERE `order_item_id` = '$order_item_id' AND `meta_key` = 'discount_amount_tax'";
                    } else {
                        $query = "INSERT INTO $tableOderItemMeta (`order_item_id`, `meta_key`,`meta_value`)"
                                . " VALUES ('$order_item_id','discount_amount_tax','$discount_amount_tax')";
                    }
                    $result = $wpdb->query($query);

                    update_post_meta($coupon_id, 'usage_count', 1);
                    update_post_meta($coupon_id, '_used_by', $customer_id);
                    $note_content = "Coupon code [$coupon_code] with " . wc_price($discount_amount) . " was added";
                    self::insert_note_when_add_or_delete_coupon($sub_id, $note_content);
                    echo "Subscription [$sub_id] was added coupon [$coupon_code] success full.<br/>";
                }
                return true;
//                } else {
//                    echo "Coupon [$coupon_code] wasn't added to subscription [$sub_id].<br/>";
//                }
            } catch (Exception $exc) {
                echo $exc->getTraceAsString();
            }
        } else {
            echo "Coupon [$coupon_code] wasn't added to subscription [$sub_id].<br/>";
        }
        return true;
    }

    public static function removeCouponOfUserFromSubscription($sub_id, $customer_id) {
        global $wpdb;
        $tableOderItems = $wpdb->prefix . 'woocommerce_order_items'; //order_item_name, order_item_type,order_id [code, coupon, id]
        $tableOderItemMeta = $wpdb->prefix . 'woocommerce_order_itemmeta'; //order_item_id,meta_key,meta_value [discount_amount, discount_amount_tax]
        $userCouponTable = $wpdb->prefix . 'woocommerce_coupon_affiliates';
        $coupon_id = 0;
//$customer_id = 0;
        $query = "SELECT order_item_id, order_item_name FROM `$tableOderItems` WHERE `order_id` = '$sub_id' AND `order_item_type` = 'coupon'";
        $resultObject = $wpdb->get_results($query);
        self::createLog("Result coupon: \n" . print_r($resultObject, true));
        if (!empty($resultObject)) {
//$oderItemId = $wpdb->get_var($query);
            $oderItemId = isset($resultObject[0]->order_item_id) ? $resultObject[0]->order_item_id : 0;
            $couponCode = isset($resultObject[0]->order_item_name) ? $resultObject[0]->order_item_name : 0;

            $coupon_id = $wpdb->get_var("SELECT ID FROM $wpdb->posts WHERE `post_title` = '$couponCode' AND `post_status` = 'publish' AND `post_type` = 'shop_coupon'");
            if ($oderItemId > 0 && $coupon_id > 0) {
                $query = "UPDATE `$userCouponTable` SET `used` = '1' WHERE `coupon_id` = '$coupon_id' AND `user_id` = '$customer_id'";
                self::createLog("Query update tabe: $query");
                $wpdb->query($query);
//Thuc hien tra lai tong ban dau truoc khi ap dung coupon
                $discount_amount = get_post_meta($sub_id, '_cart_discount', true);
                $discount_amount_tax = get_post_meta($sub_id, '_cart_discount_tax', true);
                update_post_meta($sub_id, '_cart_discount', 0);
                update_post_meta($sub_id, '_cart_discount_tax', 0);
                $currnentTotal = get_post_meta($sub_id, '_order_total', true);
//                echo 'Current:'.$currnentTotal."<br/>";
//                echo 'Dis:'.$discount_amount."<br/>";
//                echo 'YTe:'.($currnentTotal + ($discount_amount + $discount_amount_tax))."<br/>";
                $newTotalUpdate = round(($currnentTotal + ($discount_amount + $discount_amount_tax)), 2);
//echo $newTotalUpdate;
                self::createLog("Total after remove: $newTotalUpdate");
                update_post_meta($sub_id, '_order_total', $newTotalUpdate);

                $query = "SELECT order_item_id FROM $tableOderItems WHERE `order_item_type` = 'line_item' AND `order_id` = '$sub_id'";
                $resultOrderItemsProduct = $wpdb->get_var($query);
                if ($resultOrderItemsProduct > 0) {
//_line_subtotal
                    $query = "SELECT meta_value FROM $tableOderItemMeta WHERE `meta_key` = '_line_total' AND `order_item_id` = '$resultOrderItemsProduct'";
                    $oldLineTotal = $wpdb->get_var($query);
                    $newLineTotal = round($oldLineTotal + $discount_amount, 2);
                    $query = "UPDATE `$tableOderItemMeta` SET `meta_value` = '$newLineTotal'"
                            . " WHERE `order_item_id` = '$resultOrderItemsProduct' AND `meta_key` = '_line_total'";
                    $wpdb->query($query);
//_line_total
                }

                $query = "DELETE FROM $tableOderItemMeta WHERE `order_item_id` = '$oderItemId' AND `meta_key` = 'discount_amount'";
                $wpdb->query($query);
                $query = "DELETE FROM $tableOderItemMeta WHERE `order_item_id` = '$oderItemId' AND `meta_key` = 'discount_amount_tax'";
                $wpdb->query($query);
                $query = "DELETE FROM $tableOderItems WHERE `order_id` = '$sub_id' AND `order_item_type` = 'coupon'"; // AND `order_item_name` = '$couponCode'";
                $wpdb->query($query);
                $note_content = "Coupon code [$couponCode] with " . wc_price($discount_amount) . " was removed";
                self::insert_note_when_add_or_delete_coupon($sub_id, $note_content);
                echo "Coupon [$couponCode] was removed in subscription [$sub_id].<br/>";
            } else {
                echo "Coupon wasn't removed in subscription [$sub_id].<br/>";
            }
        }
        return true;
    }

    function createLog($content) {
        $allowlog = isset($_REQUEST['log']) ? $_REQUEST['log'] : 'no';
        if ($content && $allowlog == 'yes') {

            $pathLog = get_home_path() . "log";
            file_put_contents($pathLog . DIRECTORY_SEPARATOR . 'log_cronjob.txt', date("Y-m-d H:i:s") . ":" . print_r($content, true), FILE_APPEND);
        }
    }

    function insert_note_when_add_or_delete_coupon($sub_id, $content) {
        if (!$sub_id || !$content)
            return falsel;
        $time = current_time('mysql');
        global $user_id_created;
        $userInfo = get_userdata($user_id_created);
        $email = isset($userInfo->user_email) ? $userInfo->user_email : '';

        $data = array(
            'comment_post_ID' => $sub_id,
            'comment_author' => 'WooCommerce',
            'comment_author_email' => $email,
            'comment_author_url' => '',
            'comment_content' => $content,
            'comment_type' => 'order_note',
            'comment_parent' => 0,
            'user_id' => 0,
            'comment_author_IP' => '',
            'comment_agent' => 'WooCommerce', //Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.0.10) Gecko/2009042316 Firefox/3.0.10 (.NET CLR 3.5.30729)
            'comment_date' => $time,
            'comment_approved' => 1,
        );

        return wp_insert_comment($data);
    }

    /**
     * Get amount to create new coupon code
     * @param type $sub_total
     * @param type $customer_id
     * @return int
     */
    public static function getAmountForCouponFirstCheckout($sub_total, $customer_id) {

        $totalRemainOfCustomer = self::getTotalRemainOfCustomer($customer_id);
        if (!$sub_total)
            return 0;
        $totalSub = $sub_total; //self::getTotalOfSubscription($sub_id);

        if ($totalRemainOfCustomer > 0) {
            if ($totalRemainOfCustomer <= $totalSub) {
                return $totalRemainOfCustomer;
            } else {
                return $totalSub;
            }
        }
        return 0;
    }

    /**
     *
     * @param type $sub_total
     * @param type $customer_id
     * @param type $user_create_id
     * @return boolean
     */
    public static function createCouponForCustomerInFirstCheckout($sub_total, $customer_id, $user_create_id, $old_porduct, $old_coupon_code = '', $eixst_aff = false) {
        $amountNeedForCreateCoupon = 0;
        $arrayInfoFeree = array();
        $first_coupon_code = '';
        if ($eixst_aff) {
            global $adminAffiliateID;
            if (in_array($eixst_aff, $adminAffiliateID)) {
                $first_coupon_code = 'firstreferee' . $eixst_aff;
            } else {
                $first_coupon_code = 'firstreferee';
            }
//echo $first_coupon_code."<br/>";
            $arrayInfoFeree = self::getAmountByFirstreFereeCoupon($sub_total, $first_coupon_code);


            $amountNeedForCreateCoupon = isset($arrayInfoFeree['amount']) ? $arrayInfoFeree['amount'] : 0;
//echo $amountNeedForCreateCoupon;
            $sub_total2 = $sub_total - $amountNeedForCreateCoupon;
        } else {
            $sub_total2 = $sub_total;
        }
        $amountCoupon = self::getAmountForCouponFirstCheckout($sub_total2, $customer_id);

        if (!($amountCoupon + $amountNeedForCreateCoupon))
            return false;
        $data['coupon'] = ($old_coupon_code != '') ? $old_coupon_code : self::renderCouponCode();
//$data['amount'] = $amountCoupon;
//$data['usage_limit'] = 2;
        if (!$data['coupon'])
            return false;
        global $wpdb;
        $query = "SELECT ID FROM $wpdb->posts WHERE `post_title` = '{$data['coupon']}' AND `post_type` = 'shop_coupon' AND `post_status` = 'publish' LIMIT 0,1";
        $results = $wpdb->get_results($query);
        if (!empty($results)) {
            $couponObjectID = isset($results[0]->ID) ? $results[0]->ID : 0;
            if ($couponObjectID > 0) {
                $oldCouponInfo = self::getReferralsUserCouponsInfo($data['coupon']);
                $oldFereeAmount = isset($oldCouponInfo->amount_feree) ? $oldCouponInfo->amount_feree : 0;
                $oldAmount1 = get_post_meta($couponObjectID, 'coupon_amount', true);
                $oldAmount = $oldAmount1 - $oldFereeAmount;
                if ($oldAmount == $sub_total2) {
                    return false;
//$coupon_id = $couponObjectID;
                } else {
//Xoa amount coupon cu
                    if (self::subTotalUsedOfUser($customer_id, $oldAmount)) {
                        $amountCoupon = self::getAmountForCouponFirstCheckout($sub_total2, $customer_id);
                        $data['amount'] = $amountCoupon + $amountNeedForCreateCoupon;
//Thuc hien update new amount
                        if (update_post_meta($couponObjectID, 'coupon_amount', $data['amount'])) {
//Cap nhat lai amount moi
                            self::updateTotalUsedOfUserForFirstMembership($customer_id, $amountCoupon);

                            $coupon_id = $couponObjectID;
                        }
                    } else {
                        return false;
                    }
                }
            }
        } else {
            $data['type'] = 'fixed_product';
            $data['amount'] = $amountCoupon + $amountNeedForCreateCoupon;
            if ($amountCoupon == 0) {
                $oldId = isset($arrayInfoFeree['ID']) ? $arrayInfoFeree['ID'] : 0;
                if ($oldId > 0) {
//global $firstreferee;
                    return array('coupon_code_id' => $oldId, 'coupon_code' => $first_coupon_code, 'coupon_amount' => $amountNeedForCreateCoupon, 'amount_feree' => $amountNeedForCreateCoupon, 'sub_total2' => $sub_total2, 'sub_total' => $sub_total, 'old_product' => $old_porduct);
                } else {
                    return false;
                }
            } else {
                $coupon_id = self:: createCoupon($data, $user_create_id);
                if ($coupon_id > 0) {
                    self::updateTotalUsedOfUserForFirstMembership($customer_id, $amountCoupon);
                }
            }
        }
        if ($coupon_id > 0) {
            return array('coupon_code_id' => $coupon_id, 'coupon_code' => $data['coupon'], 'coupon_amount' => $data['amount'], 'amount_feree' => $amountNeedForCreateCoupon, 'sub_total2' => $sub_total2, 'sub_total' => $sub_total, 'old_product' => $old_porduct);
        }
        return false;
    }

    public static function getAmountByFirstreFereeCoupon($sub_total, $coupon_code = 'firstreferee') {
//firstreferee
        global $wpdb;
        $query = "SELECT ID FROM $wpdb->posts WHERE `post_title` = '$coupon_code' AND `post_type` = 'shop_coupon' AND `post_status` = 'publish' LIMIT 0,1";
        $results = $wpdb->get_results($query);
        if (!empty($results)) {
            $couponObjectID = isset($results[0]->ID) ? $results[0]->ID : 0;
            $couponType = get_post_meta($couponObjectID, 'discount_type', true);
            $couponAmount = get_post_meta($couponObjectID, 'coupon_amount', true);
            if ($couponType == 'percent') {
                if (!$sub_total)
                    return array('ID' => $couponObjectID, 'amount' => 0);
                $amountCouponFree = round(($sub_total * $couponAmount) / 100, 2);
            } else {
                $amountCouponFree = $couponAmount;
            }
            return array('ID' => $couponObjectID, 'amount' => $amountCouponFree);
        }
        return array('ID' => 0, 'amount' => 0);
    }

    /**
     * Update amount value coupon
     * @global type $wpdb
     * @param type $coupon_code
     * @param type $amount
     * @return true or false
     */
    public static function updateCoupon($coupon_code, $amount) {
        if (!$coupon_code)
            return false;
        global $wpdb;
        $query = "SELECT ID FROM $wpdb->posts WHERE `post_title` = '$coupon_code' AND `post_type` = 'shop_coupon' AND `post_status` = 'publish' LIMIT 0,1";
        $results = $wpdb->get_results($query);
        if (!empty($results)) {
            $couponObjectID = isset($results[0]->ID) ? $results[0]->ID : 0;
            if ($couponObjectID > 0) {
                return update_post_meta($couponObjectID, 'coupon_amount', $amount);
            }
        }
        return false;
    }

    /**
     *
     * @global type $wpdb
     * @param type $coupon_code
     * @return boolean
     */
    public static function checkExistCouponByCouponCode($coupon_code) {
        global $wpdb;
        $query = "SELECT ID FROM $wpdb->posts WHERE `post_title` = '$coupon_code' AND `post_type` = 'shop_coupon' AND `post_status` = 'publish' LIMIT 0,1";
        $results = $wpdb->get_results($query);
        if (!empty($results))
            return true;
        return false;
    }

    /**
     * Tra lai tien cho khach hang trong truong hop subscription that bai
     * @global type $wpdb
     * @param type $customer_id
     * @param type $amount
     * @param type $currency_id
     * @return boolean
     */
    public static function subTotalUsedOfUser($customer_id, $amount, $currency_id = 'AUD') {
        if (!$amount || !$customer_id)
            return false;
        global $wpdb;
        $strPrefix = $wpdb->prefix . AFFILIATES_TP;
        $query = "SELECT * FROM {$strPrefix}referrals_used WHERE `user_id` = '$customer_id' AND `currency_id` = '$currency_id' ";
        $results = $wpdb->get_results($query);
        if (!empty($results)) {
            $oldAmount = isset($results[0]->amount) ? $results[0]->amount : 0;
            $newAmount = 0;
            if ($oldAmount >= $amount) {
                $newAmount = $oldAmount - $amount;
            }

            if ($newAmount >= 0) {
                $query = "UPDATE `{$strPrefix}referrals_used` SET `amount`= '$newAmount' WHERE `user_id`='$customer_id' AND `currency_id` = '$currency_id';";

                $result = $wpdb->query($query);
                if ($result)
                    return true;
                return false;
            }
        }
        return false;
    }

    public static function updateReferralsUserCoupons($arrayResult) {
        if (empty($arrayResult))
            return false;
        $data = array();
        $data['coupon_id'] = $arrayResult['coupon_code_id'];
        $data['coupon_code'] = $arrayResult['coupon_code'];
        $data['amount'] = $arrayResult['coupon_amount'];
        $data['amount_feree'] = $arrayResult['amount_feree'];
        $data['user_id'] = get_current_user_id();
        $data['date_created'] = date('Y-m-d H:i:s');
        $data['is_success'] = 0;
        global $wpdb;
        $strPrefix = $wpdb->prefix . AFFILIATES_TP;
        $query = "SELECT coupon_id FROM {$strPrefix}referrals_user_coupons WHERE `coupon_code` = '{$data['coupon_code']}' ";
        $id = $wpdb->get_var($query);
        if ($id > 0) {
//Update amount
            $query = "UPDATE `{$strPrefix}referrals_user_coupons` SET `coupon_id` = '{$data['coupon_id']}', `amount`= '{$data['amount']}',`amount_feree`= '{$data['amount_feree']}', `is_success` = '{$data['is_success']}', `date_created` = '{$data['date_created']}' WHERE `coupon_code` = '{$data['coupon_code']}';";
        } else {
            $query = "INSERT INTO `{$strPrefix}referrals_user_coupons` (`coupon_id`, `coupon_code`, `amount`,`amount_feree`, `user_id`,`date_created`,`is_success`) VALUES ('{$data['coupon_id']}', '{$data['coupon_code']}', '{$data['amount']}','{$data['amount_feree']}', '{$data['user_id']}', '{$data['date_created']}', '{$data['is_success']}');";
//Insert new recod
        }
        $result = $wpdb->query($query);
        if ($result)
            return true;
        return false;
    }

    public static function setSuccessItemReferralsUserCoupons($coupon_id) {
        if (!$coupon_id)
            return false;
        unset($_SESSION['data_coupon']);

        global $wpdb;
        $strPrefix = $wpdb->prefix . AFFILIATES_TP;
        $query = "UPDATE `{$strPrefix}referrals_user_coupons` SET `is_success` = '1' WHERE `coupon_id` = '$coupon_id'";
        $result = $wpdb->query($query);
        if ($result)
            return true;
        return false;
    }

    public static function updateTotalUsedOfUserForFirstMembership($customer_id, $amount, $currency_id = 'AUD') {
        if (!$customer_id || !$amount)
            return false;
        global $wpdb;
        $strPrefix = $wpdb->prefix . AFFILIATES_TP;
        $query = "SELECT id FROM {$strPrefix}referrals_used WHERE `user_id` = '$customer_id' AND `currency_id` = '$currency_id' ";
        $id = $wpdb->get_var($query);
        if ($id > 0) {
//Update amount
            $query = "UPDATE `{$strPrefix}referrals_used` SET `amount`= (`amount` + $amount) WHERE `user_id`='$customer_id' AND `currency_id` = '$currency_id';";
        } else {
            $query = "INSERT INTO `{$strPrefix}referrals_used` (`user_id`, `amount`, `currency_id`) VALUES ('$customer_id', '$amount', '$currency_id');";
//Insert new recod
        }
        $result = $wpdb->query($query);
        if ($result)
            return true;
        return false;
    }

    public static function refundAmountForClient() {
        $currentDate = strtotime("-24 hours", strtotime(date('Y-m-d H:i:s')));
        $currentDate = date('Y-m-d H:i:s', $currentDate);
        global $wpdb;
        $strPrefix = $wpdb->prefix . AFFILIATES_TP;
        $query = "SELECT * FROM {$strPrefix}referrals_user_coupons WHERE UNIX_TIMESTAMP(DATE_FORMAT(`date_created`,'%Y-%m-%d %T')) <= UNIX_TIMESTAMP('$currentDate') AND is_success = '0' LIMIT 0,1000";
        $arrayObject = $wpdb->get_results($query);
        if (!empty($arrayObject)) {
            foreach ($arrayObject as $key => $item) {
                $subAmount = $item->amount - $item->amount_feree;
                if (self::subTotalUsedOfUser($item->user_id, $subAmount)) {
                    $query = "DELETE FROM {$strPrefix}referrals_user_coupons WHERE `coupon_id` = '$item->coupon_id'";
                    $wpdb->query($query);
                    $r = wp_delete_post($item->coupon_id, true);
                    echo "User ID: $item->user_id was refunded $item->amount. And coupon code [$item->coupon_code] was deleted<br/>";
                }
            }
        }
    }

    public static function getReferralsUserCouponsInfo($coupon_code) {
        if (!$coupon_code)
            return false;
        global $wpdb;
        $strPrefix = $wpdb->prefix . AFFILIATES_TP;
        $query = "SELECT * FROM `{$strPrefix}referrals_user_coupons` WHERE  `coupon_code` = '$coupon_code'";
        $arrayObject = $wpdb->get_results($query);
        if (!empty($arrayObject)) {
            return $arrayObject[0];
        }
        return false;
    }

    /**
     * Date created: 07 Nov 2016
     */

    /**
     * Author: Phuong An <phuongantt.na@gmail.com>
     *
     * @param type $query
     * @return boolean
     */
    public static function getArrayFromAffiliateDatabase($query) {
        global $wpdb;
        $strPrefix = $wpdb->prefix . AFFILIATES_TP;
        $query = str_replace('{prefix}', $strPrefix, $query);
//        var_dump($query);
        $arrayObject = $wpdb->get_results($query);
        if (!empty($arrayObject)) {
            return $arrayObject;
        }
        return false;
    }

    /**
     * Author: Phuong An <phuongantt.na@gmail.com>
     *
     * @param type $order_id
     * @return boolean
     */
    public static function getAffiliateIdByOrderId($order_id) {
        if (!$order_id)
            return false;
        $strPrefix = '{prefix}';
        $query = "SELECT `{$strPrefix}referrals`.`affiliate_id` FROM `{$strPrefix}referrals`,`{$strPrefix}affiliates_users` WHERE `{$strPrefix}referrals`.`affiliate_id` = `{$strPrefix}affiliates_users`.`affiliate_id` AND `{$strPrefix}referrals`.`post_id` = $order_id";
        $arrayObject = self::getArrayFromAffiliateDatabase($query);
        if ($arrayObject) {
            return $arrayObject[0]->affiliate_id;
        }
        return false;
    }

    /**
     * Author: Phuong An <phuongantt.na@gmail.com>
     *
     * @param type $order_id
     * @return boolean
     */
    public static function getListReferralsByAffiliateID($order_id) {
        if (!$order_id)
            return false;
        $affiliate_id = self::getAffiliateIdByOrderId($order_id);
        if ($affiliate_id) {
            $strPrefix = '{prefix}';
            $query = "SELECT DISTINCT `user_id` FROM `{$strPrefix}referrals` WHERE `affiliate_id` = $affiliate_id AND `user_id` != 0";
            $arrayObject = self::getArrayFromAffiliateDatabase($query);
            if ($arrayObject) {
                return $arrayObject;
            }
            return false;
        }
        return false;
    }

    /**
     * Author: Phuong An <phuongantt.na@gmail.com>
     *
     * @param type $order_id
     * @return boolean
     */
    public static function getAffiliateUserID($order_id) {
        if (!$order_id)
            return false;
        $affiliate_id = self::getAffiliateIdByOrderId($order_id);
        if ($affiliate_id) {
            $strPrefix = '{prefix}';
            $query = "SELECT `user_id` FROM `{$strPrefix}affiliates_users` WHERE `affiliate_id` = $affiliate_id ";
            $arrayObject = self::getArrayFromAffiliateDatabase($query);
            if ($arrayObject) {
                return $arrayObject[0]->user_id;
            }
            return false;
        }
        return false;
    }

}