<?php
class ModelAffiliateAffiliate extends Model {
	public function addAffiliate($data) {
      	$this->db->query("INSERT INTO " . DB_PREFIX . "affiliate SET firstname = '" . $this->db->escape($data['firstname']) . "', lastname = '" . $this->db->escape($data['lastname']) . "', email = '" . $this->db->escape($data['email']) . "', telephone = '" . $this->db->escape($data['telephone']) . "', fax = '" . $this->db->escape($data['fax']) . "', password = '" . $this->db->escape(md5($data['password'])) . "', company = '" . $this->db->escape($data['company']) . "', address_1 = '" . $this->db->escape($data['address_1']) . "', address_2 = '" . $this->db->escape($data['address_2']) . "', city = '" . $this->db->escape($data['city']) . "', postcode = '" . $this->db->escape($data['postcode']) . "', country_id = '" . (int)$data['country_id'] . "', zone_id = '" . (int)$data['zone_id'] . "', code = '" . $this->db->escape(uniqid()) . "', commission = '" . (float)$this->config->get('config_commission') . "', tax = '" . $this->db->escape($data['tax']) . "', payment = '" . $this->db->escape($data['payment']) . "', cheque = '" . $this->db->escape($data['cheque']) . "', paypal = '" . $this->db->escape($data['paypal']) . "', bank_name = '" . $this->db->escape($data['bank_name']) . "', bank_branch_number = '" . $this->db->escape($data['bank_branch_number']) . "', bank_swift_code = '" . $this->db->escape($data['bank_swift_code']) . "', bank_account_name = '" . $this->db->escape($data['bank_account_name']) . "', bank_account_number = '" . $this->db->escape($data['bank_account_number']) . "', status = '1', date_added = NOW()");

		if($this->config->get('config_affiliatepoints')){
			$affiliate_id = $this->db->getLastId();
			$this->db->query(" UPDATE " . DB_PREFIX . "affiliate set affiliate_group_id= ". $this->config->get('config_affiliate_group') . " where affiliate_id = ".$affiliate_id );
			if($this->config->get('config_payperaffiliate_enable')){
				$this->db->query("INSERT INTO " . DB_PREFIX . "affiliate_transaction  set transaction_type=6,amount=  " .  $this->config->get('config_payperaffiliate') . ", Payment_Status = 0 , affiliate_id = '" . $affiliate_id . "' , description = '". mysql_real_escape_string($this->config->get('config_payperaffiliate_descr')) . "' , date_added = now()" );
			}
		}
		$this->language->load('mail/affiliate');
		
		$subject = sprintf($this->language->get('text_subject'), $this->config->get('config_name'));
		
		$message  = sprintf($this->language->get('text_welcome'), $this->config->get('config_name')) . "\n\n";
		$message .= $this->language->get('text_approval') . "\n";
		$message .= $this->url->link('affiliate/login', '', 'SSL') . "\n\n";
		$message .= $this->language->get('text_services') . "\n\n";
		$message .= $this->language->get('text_thanks') . "\n";
		$message .= $this->config->get('config_name');
		
		$mail = new Mail();
		$mail->protocol = $this->config->get('config_mail_protocol');
		$mail->parameter = $this->config->get('config_mail_parameter');
		$mail->hostname = $this->config->get('config_smtp_host');
		$mail->username = $this->config->get('config_smtp_username');
		$mail->password = $this->config->get('config_smtp_password');
		$mail->port = $this->config->get('config_smtp_port');
		$mail->timeout = $this->config->get('config_smtp_timeout');				
		$mail->setTo($this->request->post['email']);
		$mail->setFrom($this->config->get('config_email'));
		$mail->setSender($this->config->get('config_name'));
		$mail->setSubject($subject);
		$mail->setText($message);
		$mail->send();
	}
	public function getInformation($affinformation_id) {
		$query = $this->db->query("SELECT DISTINCT * FROM " . DB_PREFIX . "affinformation i LEFT JOIN " . DB_PREFIX . "affinformation_description id ON (i.affinformation_id = id.affinformation_id) LEFT JOIN " . DB_PREFIX . "affinformation_to_store i2s ON (i.affinformation_id = i2s.affinformation_id) WHERE i.affinformation_id = '" . (int)$affinformation_id . "' AND id.language_id = '" . (int)$this->config->get('config_language_id') . "' AND i2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND i.status = '1'");
	
		return $query->row;
	}
	public function getInformations() {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "affinformation i LEFT JOIN " . DB_PREFIX . "affinformation_description id ON (i.affinformation_id = id.affinformation_id) LEFT JOIN " . DB_PREFIX . "affinformation_to_store i2s ON (i.affinformation_id = i2s.affinformation_id) WHERE id.language_id = '" . (int)$this->config->get('config_language_id') . "' AND i2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND i.status = '1' ORDER BY i.sort_order, LCASE(id.title) ASC");

		return $query->rows;
	}
	
	public function modifyBanner($affiliate_id,$newBanner)
	{
		$this->db->query("UPDATE " . DB_PREFIX . "banners SET banner_url = '" . $newBanner . "', status=0  WHERE affiliate_id = '" . $affiliate_id . "'");
	}

	public function getBannerURL($affiliate_id)
	{
		$this->db->query("SELECT banner_url FROM " . DB_PREFIX . "banners WHERE affiliate_id = '" . $affiliate_id . "' and status=0");
		
	}
	public function getImagebanner()
	{
		$query = $this->db->query("SELECT image FROM `affiliate_banner_image` WHERE `affiliate_banner_id`=13");
		
		return $query->rows;
		
	}
	public function getFlashbanner()
	{
		$query = $this->db->query("SELECT image FROM `affiliate_banner_image` WHERE `affiliate_banner_id`=16");
		
		return $query->rows;
		
	}
	public function getTextbanner()
	{
		$query = $this->db->query("SELECT title FROM `affiliate_banner_image_description` WHERE `affiliate_banner_id`=15");
		
		return $query->rows;
		
	}
	
	public function editAffiliate($data) {
		$this->db->query("UPDATE " . DB_PREFIX . "affiliate SET firstname = '" . $this->db->escape($data['firstname']) . "', lastname = '" . $this->db->escape($data['lastname']) . "', email = '" . $this->db->escape($data['email']) . "', telephone = '" . $this->db->escape($data['telephone']) . "', fax = '" . $this->db->escape($data['fax']) . "', company = '" . $this->db->escape($data['company']) . "', address_1 = '" . $this->db->escape($data['address_1']) . "', address_2 = '" . $this->db->escape($data['address_2']) . "', city = '" . $this->db->escape($data['city']) . "', postcode = '" . $this->db->escape($data['postcode']) . "', country_id = '" . (int)$data['country_id'] . "', zone_id = '" . (int)$data['zone_id'] . "' WHERE affiliate_id = '" . (int)$this->affiliate->getId() . "'");
	}

	public function editPayment($data) {
      	$this->db->query("UPDATE " . DB_PREFIX . "affiliate SET tax = '" . $this->db->escape($data['tax']) . "', payment = '" . $this->db->escape($data['payment']) . "', cheque = '" . $this->db->escape($data['cheque']) . "', paypal = '" . $this->db->escape($data['paypal']) . "', bank_name = '" . $this->db->escape($data['bank_name']) . "', bank_branch_number = '" . $this->db->escape($data['bank_branch_number']) . "', bank_swift_code = '" . $this->db->escape($data['bank_swift_code']) . "', bank_account_name = '" . $this->db->escape($data['bank_account_name']) . "', bank_account_number = '" . $this->db->escape($data['bank_account_number']) . "' WHERE affiliate_id = '" . (int)$this->affiliate->getId() . "'");
	}
	
	public function editPassword($email, $password) {
      	$this->db->query("UPDATE " . DB_PREFIX . "affiliate SET password = '" . $this->db->escape(md5($password)) . "' WHERE email = '" . $this->db->escape($email) . "'");
	}
				
	public function getAffiliate($affiliate_id) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "affiliate WHERE affiliate_id = '" . (int)$affiliate_id . "'");
		
		return $query->row;
	}
	
	public function getAffiliateByCode($code) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "affiliate WHERE code = '" . $this->db->escape($code) . "'");
		
		return $query->row;
	}
	
	public function getAffiliateByCustomer($code) {
		$query = $this->db->query("SELECT a.* FROM " . DB_PREFIX . "affiliate a WHERE a.affiliate_id = (select affiliate_id from referrals where customer_id='" . $this->db->escape($code) . "')");
		
		return $query->row;
	}
	public function getAffiliateBalance($affiliate_id) {
		$query = $this->db->query("SELECT sum(amount) as total FROM " . DB_PREFIX . "affiliate_transaction a WHERE a.affiliate_id = " . $affiliate_id );
		
		return $query->row['total'];
	}

	public function getAffiliateId($code) {
		$query = $this->db->query("SELECT affiliate_id FROM " . DB_PREFIX . "affiliate WHERE code = '" . $this->db->escape($code) . "'");
		if($query->num_rows){			
			return $query->row['affiliate_id'];
		}else{
			return 0;
		}
	}
	
	public function getCustomerDetails($affiliate_id) {
		$query = $this->db->query("SELECT b.* FROM " . DB_PREFIX . "affcustomers a , " . DB_PREFIX . "customer b WHERE a.customer_id = b.customer_id and affiliate_id = '" . (int)$affiliate_id . "'");
		if($query->num_rows){
			return $query->row['firstname'].', '.$query->row['lastname'];
		}else{
			return '';
		}
	}
	
	public function transferPoints($affiliate_id,$amount,$descr,$points,$amount) {

		$query = $this->db->query("insert into " . DB_PREFIX . "customer_reward (customer_id,description,points,date_added) select customer_id ,'". mysql_real_escape_string($descr) ."', ". $points  .",now() from affcustomers where affiliate_id = '" . $affiliate_id . "'");

		$query = $this->db->query("insert into " . DB_PREFIX . "affiliate_transaction (affiliate_id,description,amount,date_added) values (". $affiliate_id ." ,'". mysql_real_escape_string($descr) ."', ". $amount * -1  .",now())");

	}
	
	public function updateCustomer($affiliate_id,$customer_email) {
		$query = $this->db->query("insert into " . DB_PREFIX . "affcustomers(affiliate_id,customer_id) select ".$affiliate_id." ,customer_id from " . DB_PREFIX . "customer where email = '" . $customer_email . "'");
	}
	
	public function createCustomer($affiliate_id) {
		$query = $this->db->query("insert into " . DB_PREFIX . "customer(store_id,firstname,lastname,email,telephone,fax,password,newsletter,customer_group_id,ip,status,approved,date_added) select 0,firstname,lastname,email,telephone,fax,password,newsletter,'".  $this->config->get('config_customer_group_id') ."',ip,status,approved,date_added from " . DB_PREFIX . "affiliate where affiliate_id=3 " );

		$customer_id = $this->db->getLastId();
		
		$query = $this->db->query("insert into " . DB_PREFIX .  "address(customer_id,firstname,lastname,company,address_1,	address_2,	city,	postcode,country_id,zone_id ) select '".$customer_id . "', firstname,lastname,company,address_1,	address_2,	city,	postcode,country_id,zone_id from " . DB_PREFIX . "affiliate where affiliate_id=3");
		
		$address_id = $this->db->getLastId();

		$query = $this->db->query("UPDATE " . DB_PREFIX . "customer set address_id = '". $address_id ."' where customer_id= '" . $customer_id . "'");
		
		$query = $this->db->query("insert into " . DB_PREFIX . "affcustomers set customer_id = '". $customer_id ."',  affiliate_id= " . $affiliate_id );
		
		return $customer_id;
		
	}
		
			
	public function getTotalAffiliatesByEmail($email) {
		$query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "affiliate WHERE email = '" . $this->db->escape($email) . "'");
		
		return $query->row['total'];
	}
	public function AddUrlCount($affiliate_id)
	{
		$query11 = $this->db->query("select clickcount,payclickdescr,payclickcount,payclickcount_enable from " . DB_PREFIX . "affiliate_group a," . DB_PREFIX . "affiliate b where a.affiliate_group_id=b.affiliate_group_id and b.affiliate_id= '" . $affiliate_id . "'");
        if(isset($query11->row['payclickcount_enable']) && $query11->row['payclickcount_enable'])
		{
			if($this->config->get('config_affiliate_ipaddress')){
				$query12 = $this->db->query("select affiliate_id,ipaddress from " . DB_PREFIX . "affiliate_url a where  a.affiliate_id= '" . $affiliate_id . "' and ipaddress = '" . $_SERVER['REMOTE_ADDR'] . "'");
				if($query12->num_rows==0)
				{
					
						$this->db->query("insert into " . DB_PREFIX . "affiliate_url SET ipaddress = '" . $_SERVER['REMOTE_ADDR'] . "' , affiliate_id = '" . $affiliate_id . "',date = now() ");
						$query13 = $this->db->query("select affiliate_id from " . DB_PREFIX . "affiliate_transaction a where  a.affiliate_id= '" . $affiliate_id . "' and transaction_type = 1 and Payment_Status = 0 ");
						if($query13->num_rows==0){
							$this->db->query("insert into " . DB_PREFIX . "affiliate_transaction SET   UrlCount = 1 , transaction_type = 1 ,date_added = NOW(),amount=  1/" . $query11->row['clickcount'] . "*" . $query11->row['payclickcount'] . ", Payment_Status = 0 , affiliate_id = '" . $affiliate_id . "' , description = '" . mysql_real_escape_string($query11->row['payclickdescr']) . "'");
						}else{
							$this->db->query("UPDATE " . DB_PREFIX . "affiliate_transaction SET UrlCount = UrlCount + 1,date_added = NOW(), amount=  UrlCount/" . $query11->row['clickcount'] . "*" . $query11->row['payclickcount'] . " WHERE affiliate_id = '" . $affiliate_id . "' and Payment_Status=0 and transaction_type = 1");
						}
				}
			}else{
				$query13 = $this->db->query("select affiliate_id from " . DB_PREFIX . "affiliate_transaction a where  a.affiliate_id= '" . $affiliate_id . "' and transaction_type = 1 and Payment_Status = 0 ");
				if($query13->num_rows==0){
					$this->db->query("insert into " . DB_PREFIX . "affiliate_transaction SET   UrlCount = 1 , transaction_type = 1,date_added = NOW(),amount=  1/" . $query11->row['clickcount'] . "*" . $query11->row['payclickcount'] . ", Payment_Status = 0 , affiliate_id = '" . $affiliate_id . "' , description = '" . mysql_real_escape_string($query11->row['payclickdescr']) . "'");
				}else{
					$this->db->query("UPDATE " . DB_PREFIX . "affiliate_transaction SET UrlCount = UrlCount + 1,date_added = NOW(), amount=  UrlCount/" . $query11->row['clickcount'] . "*" . $query11->row['payclickcount'] . " WHERE affiliate_id = '" . $affiliate_id . "' and Payment_Status=0 and transaction_type = 1");
				}
			}		
        }
	}
	
	public function addReferral($data)
	{
	        $this->db->query("INSERT INTO " . DB_PREFIX . "referrals SET affiliate_id = '" . (int)$this->session->data['referral_id'] . "',customer_id = '" . $this->customer->getId() . "', status = '1'");
	   	    

	}
}
?>