<?php 
class ModelAccountAllcoowned 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 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_all_details($customer_id) { 
	
		$sql = "SELECT c.image as cimg,b.sketch_id,b.image as bimg FROM " . DB_PREFIX . "`order` x, " . DB_PREFIX . "`order_product` a, " . DB_PREFIX . "sketches_master b," . DB_PREFIX . "product c where a.product_id=b.product_id and x.order_id=a.order_id and x.customer_id='" . $customer_id . "' and a.product_id=c.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 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 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'];
	}
}
?>