<?php 
class ModelAccountCoowned extends Model {

	public function get_main_image($sketch_id) { 
	
		$sql = "SELECT image,sketch_id FROM " . DB_PREFIX . "sketches_master WHERE sketch_id = '" . $sketch_id . "'";
		
		$query = $this->db->query($sql);
		
		return $query->rows;	
	}

	public function get_product_details($sketch_id) { 
	
		$sql = "SELECT distinct p.viewed,p.product_id,pd.name,p.image FROM " . DB_PREFIX . "product p , product_description pd WHERE p.sketch_id = '" . $sketch_id . "' and p.product_id = pd.product_id";
		
		$query = $this->db->query($sql);
		
		return $query->rows;	
	}

	public function get_des_prof_img($des_img_id) { 
	
		$sql = "SELECT image,designer_id,CONCAT(firstname,' ',lastname) as name FROM " . DB_PREFIX . "designer WHERE designer_id = '" . $des_img_id . "'";
		
		$query = $this->db->query($sql);
		
		return $query->rows;	
	}

	public function get_total_customer($product_id_sold) { 
	
		$sql = "SELECT distinct a.customer_id FROM " . DB_PREFIX . "`order` a , " . DB_PREFIX . "`order_product` ap WHERE product_id = '" . $product_id_sold . "' and a.order_id = ap.order_id";
		
		$query = $this->db->query($sql);
		
		return $query->rows;	
	}

	public function get_designer_profile($sketch_id) { 
	
		$query = $this->db->query("SELECT designer_id FROM " . DB_PREFIX . "sketches_master WHERE sketch_id = '" . $sketch_id . "'");
		
		return $query->row['designer_id'];	
	}

	public function get_customer_name($customer_id) { 
	
		$query = $this->db->query("SELECT firstname FROM " . DB_PREFIX . "customer WHERE customer_id = '" . $customer_id . "'");
		
		if (isset($query->row['firstname']))
		{
		return $query->row['firstname'];	
		}
	}

	public function get_product_sketch_id($sketch_id) { 
	
		$query = $this->db->query("SELECT product_id FROM " . DB_PREFIX . "product WHERE sketch_id = '" . $sketch_id . "'");
		
		return $query->row['product_id'];	
	}

	public function get_product_sold_details($product_id_sold) { 
		$query = $this->db->query("SELECT count(name) as prodcount FROM " . DB_PREFIX . "order_product WHERE product_id = '" . $product_id_sold . "'");
		
		return $query->row['prodcount'];	
	}


	public function addCustomer($data) {
      	$this->db->query("INSERT INTO " . DB_PREFIX . "customer SET store_id = '" . (int)$this->config->get('config_store_id') . "', firstname = '" . $this->db->escape($data['firstname']) . "', lastname = '" . $this->db->escape($data['lastname']) . "', email = '" . $this->db->escape($data['email']) . "', password = '" . $this->db->escape(md5($data['password'])) . "', newsletter = '" . (isset($data['newsletter']) ? (int)$data['newsletter'] : 0) . "', customer_group_id = '" . (int)$this->config->get('config_customer_group_id') . "', status = '1', date_added = NOW()");
      	
		$customer_id = $this->db->getLastId();
			
      	$this->db->query("INSERT INTO " . DB_PREFIX . "address SET customer_id = '" . (int)$customer_id . "', firstname = '" . $this->db->escape($data['firstname']) . "', lastname = '" . $this->db->escape($data['lastname']) . "',  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'] . "'");
		
		$address_id = $this->db->getLastId();

      	$this->db->query("UPDATE " . DB_PREFIX . "customer SET address_id = '" . (int)$address_id . "' WHERE customer_id = '" . (int)$customer_id . "'");
		
		if (!$this->config->get('config_customer_approval')) {
			$this->db->query("UPDATE " . DB_PREFIX . "customer SET approved = '1' WHERE customer_id = '" . (int)$customer_id . "'");
		}	
		
		//  Affiliate System -- Add on Starts Here		
		   if (isset($this->request->cookie['tracking']))
		   {
				$this->load->model('affiliate/affiliate');

				if(isset($this->session->data['referral_id'])){
					$referral_id=$this->session->data['referral_id'];
				}else{
					$referral_id=$this->model_affiliate_affiliate->getAffiliateId($this->request->cookie['tracking']);
				}
				$this->db->query("INSERT INTO " . DB_PREFIX . "referrals SET affiliate_id = '" . (int)$referral_id . "',customer_id = '" . (int)$customer_id . "', status = '1'");
				$query11 = $this->db->query("select paypercust,paypercust_enable from " . DB_PREFIX . "affiliate_group a," . DB_PREFIX . "affiliate b where a.affiliate_group_id=b.affiliate_group_id and b.affiliate_id= '" . (int)$referral_id . "'");
				if(isset($query11->row['paypercust_enable']) && $query11->row['paypercust_enable'])
				{
					$this->db->query("insert into " . DB_PREFIX . "affiliate_transaction SET  amount = " . $query11->row['paypercust'] . " , date_added = now() , transaction_type = 4 , Payment_Status = 0 , affiliate_id = '" . (int)$referral_id . "' , description = '" . $this->config->get('config_paypercust_descr') . "Customer Name: " . $this->db->escape($data['firstname']) . " , " . $this->db->escape($data['lastname'])  . " ; Customer ID : " . $customer_id . "'");
				}							
		   }
    //  Affiliate System -- Add on Ends Here

		
		$this->language->load('mail/customer');
		
		$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";
		
		if (!$this->config->get('config_customer_approval')) {
			$message .= $this->language->get('text_login') . "\n";
		} else {
			$message .= $this->language->get('text_approval') . "\n";
		}
		
		$message .= $this->url->link('account/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($data['email']);
		$mail->setFrom($this->config->get('config_email'));
		$mail->setSender($this->config->get('config_name'));
		$mail->setSubject($subject);
		$mail->setText($message);
		$mail->send();
		
		// Send to main admin email if new account email is enabled
		if ($this->config->get('config_account_mail')) {
			$mail->setTo($this->config->get('config_email'));
			$mail->send();
			
			// Send to additional alert emails if new account email is enabled
			$emails = explode(',', $this->config->get('config_alert_emails'));
			
			foreach ($emails as $email) {
				if (strlen($email) > 0 && preg_match('/^[^\@]+@.*\.[a-z]{2,6}$/i', $email)) {
					$mail->setTo($email);
					$mail->send();
				}
			}
		}
	}
	
	public function editCustomer($data) {
		$this->db->query("UPDATE " . DB_PREFIX . "customer SET firstname = '" . $this->db->escape($data['firstname']) . "', lastname = '" . $this->db->escape($data['lastname']) . "',newsletter = '" . $this->db->escape($data['newsletter']) . "', password = '" . $this->db->escape(md5($data['password'])) . "', email = '" . $this->db->escape($data['email']) . "' WHERE customer_id = '" . (int)$this->customer->getId() . "'");
	}
	
	public function add_sketch_content($data) {
			$customer = $this->customer->getid();
			$this->db->query("INSERT INTO " . DB_PREFIX . "sketch_comments SET sketch_assignment_id = '" . $this->db->escape($data['sketch_assignment_id']) . "',is_customer = 1,comment = '" . $this->db->escape($data['comment']) . "', name = '" . $this->db->escape($data['name']) . "',customer_designer_id = '".$customer."' ,creation_date_time = NOW()");
	}
	
	public function getcustomername($customer_id) {
		$query = $this->db->query("select CONCAT(firstname,' ',lastname) as name from customer where customer_id= '" . (int)$customer_id . "'");
		
		return $query->row['name']; 
	} 

	public function editPassword($email, $password) {
      	$this->db->query("UPDATE " . DB_PREFIX . "customer SET password = '" . $this->db->escape(md5($password)) . "' WHERE email = '" . $this->db->escape($email) . "'");
	}

	public function editNewsletter($newsletter) {
		$this->db->query("UPDATE " . DB_PREFIX . "customer SET newsletter = '" . (int)$newsletter . "' WHERE customer_id = '" . (int)$this->customer->getId() . "'");
	}
					
	public function getCustomer($customer_id) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "customer WHERE customer_id = '" . (int)$customer_id . "'");
		
		return $query->row;
	}
					
	public function getCustomer2($customer_id) {
		$query = $this->db->query("SELECT firstname FROM " . DB_PREFIX . "customer WHERE customer_id = '" . (int)$customer_id . "'");
		
		return $query->row['firstname'];
	}
	
	public function getCustomerByToken($token) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "customer WHERE token = '" . $this->db->escape($token) . "' AND token != ''");
		
		$this->db->query("UPDATE " . DB_PREFIX . "customer SET token = ''");
		
		return $query->row;
	}
		
	public function getCustomers($data = array()) {
		$sql = "SELECT *, CONCAT(c.firstname, ' ', c.lastname) AS name, cg.name AS customer_group FROM " . DB_PREFIX . "customer c LEFT JOIN " . DB_PREFIX . "customer_group cg ON (c.customer_group_id = cg.customer_group_id) ";

		$implode = array();
		
		if (isset($data['filter_name']) && !is_null($data['filter_name'])) {
			$implode[] = "LCASE(CONCAT(c.firstname, ' ', c.lastname)) LIKE '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "%'";
		}
		
		if (isset($data['filter_email']) && !is_null($data['filter_email'])) {
			$implode[] = "c.email = '" . $this->db->escape($data['filter_email']) . "'";
		}
		
		if (isset($data['filter_customer_group_id']) && !is_null($data['filter_customer_group_id'])) {
			$implode[] = "cg.customer_group_id = '" . $this->db->escape($data['filter_customer_group_id']) . "'";
		}	
		
		if (isset($data['filter_status']) && !is_null($data['filter_status'])) {
			$implode[] = "c.status = '" . (int)$data['filter_status'] . "'";
		}	
		
		if (isset($data['filter_approved']) && !is_null($data['filter_approved'])) {
			$implode[] = "c.approved = '" . (int)$data['filter_approved'] . "'";
		}	
			
		if (isset($data['filter_ip']) && !is_null($data['filter_ip'])) {
			$implode[] = "c.customer_id IN (SELECT customer_id FROM " . DB_PREFIX . "customer_ip WHERE ip = '" . $this->db->escape($data['filter_ip']) . "')";
		}	
				
		if (isset($data['filter_date_added']) && !is_null($data['filter_date_added'])) {
			$implode[] = "DATE(c.date_added) = DATE('" . $this->db->escape($data['filter_date_added']) . "')";
		}
		
		if ($implode) {
			$sql .= " WHERE " . implode(" AND ", $implode);
		}
		
		$sort_data = array(
			'name',
			'c.email',
			'customer_group',
			'c.status',
			'c.ip',
			'c.date_added'
		);	
			
		if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
			$sql .= " ORDER BY " . $data['sort'];	
		} else {
			$sql .= " ORDER BY name";	
		}
			
		if (isset($data['order']) && ($data['order'] == 'DESC')) {
			$sql .= " DESC";
		} else {
			$sql .= " ASC";
		}
		
		if (isset($data['start']) || isset($data['limit'])) {
			if ($data['start'] < 0) {
				$data['start'] = 0;
			}			 

			if ($data['limit'] < 1) {
				$data['limit'] = 20;
			}	
			
			$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
		}		
		
		$query = $this->db->query($sql);
		
		return $query->rows;	
	}
		
	public function getsketchesmaster() {
		if(isset($this->request->get['rating']))
		{
		$sql = "SELECT sm.designer_id,sm.image,sa.sketch_assignment_id,sa.contest_id,sa.rating,sa.winner,sa.eliminated,sa.withdrawn,sa.reported,sa.sketch_id,sa.sketch_assignment_id from " . DB_PREFIX . "sketches_master sm, " . DB_PREFIX . "contest_sketch_assignment sa where sm.sketch_id = sa.sketch_id  ORDER BY sa.rating DESC ";
		} else { 
		$sql = "SELECT sm.designer_id,sm.image,sa.sketch_assignment_id,sa.contest_id,sa.rating,sa.winner,sa.eliminated,sa.withdrawn,sa.reported,sa.sketch_id,sa.sketch_assignment_id from " . DB_PREFIX . "sketches_master sm, " . DB_PREFIX . "contest_sketch_assignment sa where sm.sketch_id = sa.sketch_id";
		}
		
		$query = $this->db->query($sql);
		
		return $query->rows;	
	}
		
	public function get_designer_prof_pics() {
		$query0 = "SELECT sketch_id FROM " . DB_PREFIX . "contest_sketch_assignment";	
		$query0_res = mysql_query($query0); 	
		 if (mysql_num_rows($query0_res) > 0)
		 {
			while ( $query0_info = mysql_fetch_array($query0_res)) 
				{
					$query0_sketch_id = $query0_info['sketch_id'];
					$query1 = $this->db->query("SELECT d.image as profileimg,d.designer_id,sm.image from " . DB_PREFIX . "designer d," . DB_PREFIX . "sketches_master sm WHERE d.designer_id=sm.designer_id and sm.sketch_id ='" . $query0_sketch_id . "' ");				
					$output_data = $query1->rows;				
				}				
				return $output_data;
		}
		}  
		
		
	public function getsketchid() {
	
		$sql = "SELECT a.rating,a.winner,a.eliminated,a.withdrawn,c.designer_id, c.image as designerimage, b.image as sketchimage FROM `contest_sketch_assignment` a, sketches_master b, designer c WHERE a.Sketch_Id = b.Sketch_Id AND b.designer_id = c.designer_id ORDER BY designer_id";
		
		$query = $this->db->query($sql);
		
		return $query->rows;	
	}
	
	public function getsinglesketches($sketch_id) { 
	
		$sql = "SELECT designer_id , image FROM " . DB_PREFIX . "sketches_master WHERE sketch_id = '" . $sketch_id . "'";
		
		$query = $this->db->query($sql);
		
		return $query->rows;	
	}
	
	public function get_feedback_comments($sketch_id) { 
	
		$sql = "SELECT name , comment, creation_date_time, is_customer FROM " . DB_PREFIX . "sketch_comments WHERE sketch_assignment_id = '" . $sketch_id . "' ORDER BY creation_date_time DESC";
		
		$query = $this->db->query($sql);
		
		return $query->rows;	
	}
	
	public function get_sketch_assg_id($sketch_id) { 
	
		$query = $this->db->query("SELECT sketch_assignment_id FROM " . DB_PREFIX . "contest_sketch_assignment WHERE sketch_id = '" . $sketch_id . "'");
		
		return $query->row['sketch_assignment_id'];	
	}
		
		
	public function getdesign_through_sketchid($sketch_id) {
	
		$query = $this->db->query("SELECT distinct d.image as profileimg from " . DB_PREFIX . "designer d," . DB_PREFIX . "sketches_master sm WHERE d.designer_id=sm.designer_id and sm.sketch_id ='" . $sketch_id . "' ");
		
	//	$query = $this->db->query($sql);
		
		return $query->row['profileimg'];
	}
		
		
	public function getdesign_through_sketchid2($sketch_id) {
	
		$query = $this->db->query("SELECT sm.image from " . DB_PREFIX . "designer d," . DB_PREFIX . "sketches_master sm WHERE d.designer_id=sm.designer_id and sm.sketch_id ='" . $sketch_id . "' ");
		
	//	$query = $this->db->query($sql);
		
		return $query->row['image'];
	}
		
		
		
	/*	
public function get_designer_prof_pics() {
		$product_attribute_group_data = array();
		
		$product_attribute_group_query = $this->db->query("SELECT sketch_id FROM " . DB_PREFIX . "contest_sketch_assignment");
		
		foreach ($product_attribute_group_query->rows as $product_attribute_group) {
		
			$cc = $product_attribute_group['sketch_id'];
			$product_attribute_data = array();
			
			$product_attribute_query = $this->db->query("SELECT d.image as profileimg,d.designer_id,sm.image from " . DB_PREFIX . "designer d," . DB_PREFIX . "sketches_master sm WHERE d.designer_id=sm.designer_id and sm.sketch_id ='" . $cc . "' ");
			
			foreach ($product_attribute_query->rows as $product_attribute) {
				$product_attribute_data[] = array(
					'profileimg' => $product_attribute['profileimg'],
					'image'         => $product_attribute['image'],
					'designer_id'         => $product_attribute['designer_id']		 	
				);
			}
			
			$product_attribute_group_data[] = array(
				'profileimg' => $product_attribute_group['profileimg'],
				'image'               => $product_attribute_group['image'],
				'designer_id'          => $product_attribute_data
			);			
		}
			return $product_attribute_group_data;
			} */
		
		
		
	
	public function getdesignername($designer_id) {
		$query = $this->db->query("select CONCAT(firstname,' ',lastname) as name from designer where designer_id= '" . (int)$designer_id . "'");
		
		return $query->row['name']; 
	} 	
		
	
	public function getdesigneremail($designer_id) {
		$query = $this->db->query("select email from designer where designer_id= '" . (int)$designer_id . "'");
		
		return $query->row['email']; 
	} 
		
		
	public function getTotalCustomersByEmail($email) {
		$query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "customer WHERE email = '" . $this->db->escape($email) . "'");
		
		return $query->row['total'];
	}
}
?>