<?php

class ModelExtensionAffClassic extends Model {

	public function install() {
		$q = "CREATE TABLE IF NOT EXISTS `" . DB_PREFIX . "customer_affiliate_transaction` (
  `customer_affiliate_transaction_id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_transaction_id` int(11) NOT NULL DEFAULT '0',
  `customer_id` int(11) NOT NULL,
  `order_id` int(11) NOT NULL,
  `description` text NOT NULL,
  `amount` decimal(15,4) NOT NULL,
  `date_added` datetime NOT NULL,
  PRIMARY KEY (`customer_affiliate_transaction_id`),
  KEY (`customer_id`),
  KEY (`date_added`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci";
		$this->db->query($q);	
	}

	public function addTransaction($customer_id, $description = '', $amount = '', $order_id = 0) {
		if(!$this->config->get('module_aff_classic_commission_table')) {
			$this->load->model('customer/customer');
			$this->model_customer_customer->addTransaction($customer_id, $description, $amount, $order_id);
			return;
		}
		
		$this->db->query("INSERT INTO " . DB_PREFIX . "customer_affiliate_transaction SET customer_id = '" . (int)$customer_id . "', order_id = '" . (int)$order_id . "', description = '" . $this->db->escape($description) . "', amount = '" . (float)$amount . "', date_added = NOW()");
		
		if($amount > 0 && $this->config->get('module_aff_classic_commission_to_credits') && $this->config->get('module_aff_classic_commission_to_credits') > 0) {
			$this->load->model('customer/customer');
			$this->load->language('extension/module/aff_classic');
			$multiplier = $this->config->get('module_aff_classic_transfer_multiplier');
			if(!$multiplier || !preg_match("/^\d+(?:\.\d+)$/", $multiplier)) $multiplier = 1;
			$multiplier = (float)$multiplier;
			$this->model_customer_customer->addTransaction($customer_id, $this->language->get('text_transaction_to_credits'), ($amount * $multiplier), $order_id);
			$this->config->set('_no_affiliate_transaction_mail', true);
			
			$q = "SELECT customer_transaction_id FROM " . DB_PREFIX . "customer_transaction WHERE customer_id = '" . (int)$customer_id . "' AND order_id = '" . (int)$order_id . "' AND amount = '" . (float)$amount . "' ORDER BY customer_transaction_id DESC LIMIT 1";
			$res = $this->db->query($q);
			if($res->num_rows) {
				$c_tr_id = (int)$res->row['customer_transaction_id'];
			} else {
				$c_tr_id = 0;
			}
			$this->db->query("INSERT INTO " . DB_PREFIX . "customer_affiliate_transaction SET customer_transaction_id = '" . $c_tr_id . "', customer_id = '" . (int)$customer_id . "', order_id = '" . (int)$order_id . "', description = '" . $this->db->escape($this->language->get('text_transaction_from_affiliate')) . "', amount = '" . (-((float)$amount)) . "', date_added = NOW()");			
		}
	}

	public function deleteTransaction($order_id) {
		if(!$this->config->get('module_aff_classic_commission_table')) {
			$this->load->model('customer/customer');
			$this->model_customer_customer->deleteTransactionByOrderId($order_id);
		} else {
			$this->deleteCreditsByField('order_id', $order_id);			
			$this->db->query("DELETE FROM " . DB_PREFIX . "customer_affiliate_transaction WHERE order_id = '" . (int)$order_id . "'");
		}
	}
	
	private function deleteCreditsByField($field, $value) {
		$res = $this->db->query("SELECT DISTINCT(customer_transaction_id) AS c_tr_id FROM " . DB_PREFIX . "customer_affiliate_transaction WHERE `" . $field . "` = '" . (int)$value . "' AND customer_transaction_id != '0'");
		if($res->num_rows) {
			$c_tr_ids = array();
			foreach($res->rows as $r) {
				$c_tr_ids[] = (int)$c_tr_id;
			}
			$this->db->query("DELETE FROM " . DB_PREFIX . "customer_transaction WHERE customer_transaction_id IN (" . implode(',', $c_tr_ids) . ")");
		}		
	}
	
	public function deleteTransactionsByCustomerId($customer_id) {
		if($this->config->get('module_aff_classic_status')) {
			$this->deleteCreditsByField('customer_id', $customer_id);
			$this->db->query("DELETE FROM " . DB_PREFIX . "customer_affiliate_transaction WHERE customer_id = '" . (int)$customer_id . "'");
		}
	}

	public function getTransactions($customer_id, $start = 0, $limit = 10) {
		if(!$this->config->get('module_aff_classic_commission_table')) {
			$this->load->model('customer/customer');
			return $this->model_customer_customer->getTransactions($customer_id, $start, $limit);
		}
		
		if ($start < 0) $start = 0;
		if ($limit < 1) $limit = 10;

		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "customer_affiliate_transaction WHERE customer_id = '" . (int)$customer_id . "' ORDER BY date_added DESC LIMIT " . (int)$start . "," . (int)$limit);

		return $query->rows;
	}

	public function getTotalTransactions($customer_id) {
		if(!$this->config->get('module_aff_classic_commission_table')) {
			$this->load->model('customer/customer');
			return $this->model_customer_customer->getTotalTransactions($customer_id);
		}		
		$query = $this->db->query("SELECT COUNT(*) AS total  FROM " . DB_PREFIX . "customer_affiliate_transaction WHERE customer_id = '" . (int)$customer_id . "'");

		return $query->row['total'];
	}

	public function getTransactionTotal($customer_id) {
		if(!$this->config->get('module_aff_classic_commission_table')) {
			$this->load->model('customer/customer');
			return $this->model_customer_customer->getTransactionTotal($customer_id);
		}			
		$query = $this->db->query("SELECT SUM(amount) AS total FROM " . DB_PREFIX . "customer_affiliate_transaction WHERE customer_id = '" . (int)$customer_id . "'");

		return $query->row['total'];
	}

	public function getTotalTransactionsByOrderId($order_id) {
		if(!$this->config->get('module_aff_classic_commission_table')) {
			$this->load->model('customer/customer');
			return $this->model_customer_customer->getTotalTransactionsByOrderId($order_id);
		}		
		$query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "customer_affiliate_transaction WHERE order_id = '" . (int)$order_id . "'");

		return $query->row['total'];
	}
	
	public function getPayoutAccounts($affiliate_ids) {
		if(!is_array($affiliate_ids)) $affiliate_ids = array($affiliate_ids);
		if(sizeof($affiliate_ids) < 1) return array();
		$methods = array('paypal', 'bank', 'cheque');
		$query = "SELECT customer_id AS affiliate_id, payment, cheque, paypal, bank_name, bank_branch_number, bank_swift_code, bank_account_name, bank_account_number FROM " . DB_PREFIX . "customer_affiliate WHERE customer_id in (" . implode(',', $affiliate_ids) . ")";
		$res = $this->db->query($query);
		$out = array();
		foreach($res->rows as $r) {			
			$_id = intval($r['affiliate_id']);
			$_out = array();
			foreach(array('cheque', 'bank_account_name', 'bank_account_number') as $_k) {
				$r[$_k] = trim($r[$_k]);
			}
			if((!in_array($r['payment'], $methods)) || ($r['payment'] == 'paypal' && !filter_var($r['paypal'], FILTER_VALIDATE_EMAIL)) || ($r['payment'] == 'cheque' && !$r['cheque']) || ($r['payment'] == 'bank' && !$r['bank_account_name'] && !$r['bank_account_number'])) {
				$out[$_id] = false;
				continue;
			}
			$_out['method'] = $r['payment'];
			switch($_out['method']) {
				case 'paypal':
					$_out['account'] = $r['paypal'];
					break;
				case 'cheque':
					$_out['account'] = $r['cheque'];
					break;
				case 'bank':
					$_out['account'] = array();
					foreach($r as $_k => $_v) {
						if(strpos($_k, 'bank_') === 0) $_out['account'][substr_replace($_k, '', 0, 5)] = $r[$_k];
					}
					break;				
			}
			$out[$_id] = $_out;
		}
		return $out;
	}	
	
}