<?php

class ModelExtensionCouponForTracking extends Model {

	public function getAffiliates($filter=false) {
		$q = "SELECT c.customer_id AS id, concat(c.firstname, ' ', c.lastname, '(', c.email , ')') AS n FROM " . DB_PREFIX . "customer_affiliate ca, " . DB_PREFIX . "customer c WHERE c.customer_id = ca.customer_id AND ca.`status`='1'";		
		if($filter) {
			$filter = $this->db->escape(strtolower($filter));
			$q .= " AND (LOWER(c.firstname) LIKE '" . $filter . "%' or LOWER(c.lastname) LIKE '" . $filter . "%' OR LOWER(c.email) LIKE '" . $filter . "%' OR LOWER(c.email) LIKE '%@" . $filter . "%') ";
		}
		$q .= " ORDER BY CONCAT(c.firstname, ' ', c.lastname)";
		$res = $this->db->query($q);
		$out = array();
		foreach($res->rows as $r) {
			$out[strval($r['id'])] = $r['n'];
		}
		return $out;	
	}
	
	public function getMarketing($filter=false) {
		$q = "SELECT marketing_id AS id, `name` AS n from " . DB_PREFIX . "marketing WHERE 1=1";
		if($filter) {
			$filter = $this->db->escape(strtolower($filter));
			$q .= " AND LOWER(`name`) LIKE '%" . $filter . "%'";
		}
		$q .= " ORDER BY n";
		$res = $this->db->query($q);
		$out = array();
		foreach($res->rows as $r) {
			$out[strval($r['id'])] = $r['n'];
		}
		return $out;	
	}
	
	
	public function getCouponAssoc($coupon_id) {
		$res = $this->db->query("SELECT ca.ctype, a.customer_id, a.status, m.marketing_id FROM " . DB_PREFIX . "coupon_for_tracking ca LEFT JOIN " . DB_PREFIX . "customer_affiliate a ON a.customer_id = ca.customer_id LEFT JOIN " . DB_PREFIX . "marketing m ON m.marketing_id = ca.marketing_id WHERE ca.coupon_id = '" . (int)$coupon_id . "'");
		if($res->num_rows < 1 || ($res->row['ctype'] == 'a' && (!$res->row['customer_id'] || !$res->row['status'])) || ($res->row['ctype'] == 'm' && !$res->row['marketing_id'])) return '0';		
		return ($res->row['ctype'] == 'a' ? 'a_' . $res->row['customer_id'] : 'm_' . $res->row['marketing_id']);
	}
	
	public function edit($coupon_id, $assoc = false) {
		if(!preg_match("/^\d+$/", $coupon_id)) return;
		if(!$assoc) {
			$this->db->query("DELETE FROM " . DB_PREFIX . "coupon_for_tracking WHERE coupon_id='" . (int)$coupon_id . "'");
			return;
		}
		if(!preg_match("/^[am]_\d+$/", $assoc)) return;
		list($ctype, $id) = explode('_', $assoc);
		if($ctype == 'a') {
			$customer_id = intval($id);
			$marketing_id = 0;
		} else {
			$customer_id = 0;
			$marketing_id = intval($id);
		}
		$this->db->query("INSERT INTO " . DB_PREFIX . "coupon_for_tracking (coupon_id, customer_id, marketing_id, ctype) VALUES ('" . (int)$coupon_id . "', '" . $customer_id . "', '" . $marketing_id . "', '" . $ctype . "') ON DUPLICATE KEY UPDATE customer_id = '" . $customer_id . "', marketing_id = '" . $marketing_id . "', ctype = '" . $ctype . "'");
	}

	public function install() {
		$this->db->query("CREATE TABLE if not exists " . DB_PREFIX . "coupon_for_tracking (
			coupon_id int(11) unsigned not null default '0',
			customer_id int(11) unsigned not null default '0',
			marketing_id int(11) unsigned not null default '0', 
			ctype enum('a','m') not null default 'a', 		
			primary key (coupon_id)		
		) ENGINE=MyISAM");
	}

}
