<?php
/***************************************************************************
 *                                                                          *
 * Copyright (c) 2005 ISPG Technologies (I) Pvt Ltd. All rights reserved.   *
 *                                                                          *
 * This  is  commercial  software,  only  users  who have purchased a valid *
 * license  and  accept  to the terms of the  License Agreement can install *
 * and use this program.                                                    *
 *                                                                          *
 ****************************************************************************
 * PLEASE READ THE FULL TEXT  OF THE SOFTWARE  LICENSE   AGREEMENT  IN  THE *
 * "copyright.txt" FILE PROVIDED WITH THIS DISTRIBUTION PACKAGE.            *
 ****************************************************************************/
//
//$Id: ReferralModel.php, v 1.0 2013/03/14 05:55:11
//  
class ReferralModel extends Model {
    function listUserReferrals($referedBY = 0) {
        $sql = "	
			SELECT
				usrRef.referralStatus,
				usrRef.purchaseStatus,
				usrRef.referalRegActiveDate,
				usr.userID,
				usr.userStatus,
				IF(usrRef.referedTo>0,
					usr.userLastAccessed,usrRef.lastActDate) userLastAccessed,
				IF(usrRef.referedTo>0,
					CONCAT(usr.userFirstName,' ',usr.userLastName),
						CONCAT(ordMas.billingFirstName,' ',ordMas.billingLastName)) AS fullName,
				IF(usrRef.referedTo>0,
					usr.userEmail,
					ordMas.customer_email) AS emailID
			FROM
				" . DB_PREFIX . "users_referrals AS usrRef
			LEFT JOIN
				" . DB_PREFIX . "users usr 
				ON usr.userID=usrRef.referedTo
			LEFT JOIN
				" . DB_PREFIX . "referral_purchases AS refPur
				ON usrRef.referrerID=refPur.referrerID
			LEFT JOIN
				" . DB_PREFIX . "order_masters AS ordMas
				ON refPur.masterOrderID=ordMas.masterOrderID
			WHERE
				referralStatus!='Deleted'";
        if ($referedBY > 0) {
            $sql .= " 
				AND usrRef.referedBY='" . $referedBY . "' ";
        }
        //echo $sql;exit;
        return $this->ObjDatabase->readValuesSlave($sql);
    }
    function UserReferrals($referedBY = 0) {
        $sql = "
				SELECT 
					COUNT( usrRef.referrerID ) AS refCount, 
					SUM( usrRef.referralAmount ) AS TotreferralAmount
				FROM 
					" . DB_PREFIX . "users AS usr
				LEFT JOIN
					" . DB_PREFIX . "users_referrals AS usrRef 
					ON usrRef.referedBY = usr.userID
					AND usrRef.referralStatus = 'Active'";
        if ($referedBY > 0) {
            $sql .= " 
						WHERE
							usr.userStatus != 'Deleted' 
							AND usr.userID='" . $referedBY . "' ";
        }
        $resSelectReferralscount = $this->ObjDatabase->readValue($sql);
        return $resSelectReferralscount;
    }
    public function getReferrerIDbyOrderID($orderID) {
        $sql         = "	SELECT
							usrRef.referrerID

						FROM
							`" . DB_PREFIX . "users_referrals` AS usrRef
						LEFT JOIN
							`" . DB_PREFIX . "referral_purchases` AS refPur ON usrRef.referrerID=refPur.referrerID
						LEFT JOIN
							`" . DB_PREFIX . "order_masters` AS ordMas ON refPur.masterOrderID=ordMas.masterOrderID
						WHERE ordMas.masterOrderID = '" . $orderID . "'
					";
        $resReferrer = $this->ObjDatabase->readValue($sql);
        return $resReferrer['referrerID'];
    }
}