<?php
class ModelValuersValuer extends Model {
	public function addvaluer($data) {
      	$this->db->query("INSERT INTO " . DB_PREFIX . "valuer SET store_id = '" . (int)$this->config->get('config_store_id') . "', log_title = '" . $this->db->escape($data['log_title']) . "', firstname = '" . $this->db->escape($data['firstname']) . "', lastname = '" . $this->db->escape($data['lastname']) . "', email = '" . $this->db->escape($data['email']) . "', telephone = '" . $this->db->escape($data['telephone']) . "', password = '" . $this->db->escape(md5($data['password'])) . "', newsletter = '" . (isset($data['newsletter']) ? (int)$data['newsletter'] : 0) . "', valuer_group_id = '" . (int)$this->config->get('config_valuer_group_id') . "', status = '1', date_added = NOW()");
      	
		$valuer_id = $this->db->getLastId();
			
      	$this->db->query("INSERT INTO " . DB_PREFIX . "valuer_address SET valuer_id = '" . (int)$valuer_id . "', firstname = '" . $this->db->escape($data['firstname']) . "', lastname = '" . $this->db->escape($data['lastname']) . "', company = '" . $this->db->escape($data['company']) . "', address_1 = '" . $this->db->escape($data['address_1']) . "', 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 . "valuer SET address_id = '" . (int)$address_id . "' WHERE valuer_id = '" . (int)$valuer_id . "'");
		
		if (!$this->config->get('config_valuer_approval')) {
			$this->db->query("UPDATE " . DB_PREFIX . "valuer SET approved = '1' WHERE valuer_id = '" . (int)$valuer_id . "'");
		}	
		
		$this->language->load('mail/valuer');
		
		$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_valuer_approval')) {
			$message .= $this->language->get('text_login') . "\n";
		} else {
			$message .= $this->language->get('text_approval') . "\n";
		}
		
		$message .= $this->url->link('valuers/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 editvaluer($data) {
	if($data['image']) {
			if(strlen($data['image']) > 50)
			{
			$image = $this->db->query("SELECT image from " . DB_PREFIX . "valuer WHERE valuer_id = '" . (int)$this->valuer->getId() . "'"); 
			$image = $image->row['image'];
			} else {
			$image = 'valuers/'.$this->valuer->getId().'/'.substr($data['image'],6);
			}
	} else { 
			$image = $this->db->query("SELECT image from " . DB_PREFIX . "valuer WHERE valuer_id = '" . (int)$this->valuer->getId() . "'"); 
			
			if(isset($image->row['image'])) { $image = $image->row['image']; } else { $image ='no_image.jpg'; }
	} 
		if($data['password']) {
		$this->db->query("UPDATE " . DB_PREFIX . "valuer SET image = '" . $image . "',firstname = '" . $this->db->escape($data['firstname']) . "',payment = '" . (isset($data['payment']) ? $this->db->escape($data['payment']) : 0) . "',lastname = '" . $this->db->escape($data['lastname']) . "',gender = '" . $this->db->escape($data['gender']) . "', lang = '" . (isset($data['lang']) ? (int)$data['lang'] : 0) . "', lang1 = '" . (isset($data['lang1']) ? (int)$data['lang1'] : 0) . "',lang2 = '" . (isset($data['lang2']) ? (int)$data['lang2'] : 0) . "',banktransfer = '" . (isset($data['banktransfer']) ? $this->db->escape($data['banktransfer']) : 0) . "', cheque = '" . (isset($data['cheque']) ? $this->db->escape($data['cheque']) : 0) . "', paypal = '" . (isset($data['paypal']) ? $this->db->escape($data['paypal']) : 0) . "', moneybrookers = '" . (isset($data['moneybrookers']) ? $this->db->escape($data['moneybrookers']) : 0) . "', dispname = '" . $this->db->escape($data['dispname']) . "', qualification = '" . $this->db->escape($data['qualification']) . "',certificate = '" . $this->db->escape($data['certificate']) . "', about_yourself = '" . $this->db->escape($data['about_yourself']) . "', paypalemail = '" . $this->db->escape($data['paypalemail']) . "', 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']) . "' , newsletter = '" . $this->db->escape($data['newsletter']) . "', password = '" . $this->db->escape(md5($data['password'])) . "', email = '" . $this->db->escape($data['email']) . "' WHERE valuer_id = '" . (int)$this->valuer->getId() . "'");
		} else {
		$this->db->query("UPDATE " . DB_PREFIX . "valuer SET image = '" . $image . "',firstname = '" . $this->db->escape($data['firstname']) . "',payment = '" . (isset($data['payment']) ? $this->db->escape($data['payment']) : 0) . "',lastname = '" . $this->db->escape($data['lastname']) . "',gender = '" . $this->db->escape($data['gender']) . "', lang = '" . (isset($data['lang']) ? (int)$data['lang'] : 0) . "', lang1 = '" . (isset($data['lang1']) ? (int)$data['lang1'] : 0) . "',lang2 = '" . (isset($data['lang2']) ? (int)$data['lang2'] : 0) . "',banktransfer = '" . (isset($data['banktransfer']) ? $this->db->escape($data['banktransfer']) : 0) . "', cheque = '" . (isset($data['cheque']) ? $this->db->escape($data['cheque']) : 0) . "', paypal = '" . (isset($data['paypal']) ? $this->db->escape($data['paypal']) : 0) . "', moneybrookers = '" . (isset($data['moneybrookers']) ? $this->db->escape($data['moneybrookers']) : 0) . "', dispname = '" . $this->db->escape($data['dispname']) . "', qualification = '" . $this->db->escape($data['qualification']) . "',certificate = '" . $this->db->escape($data['certificate']) . "', about_yourself = '" . $this->db->escape($data['about_yourself']) . "', paypalemail = '" . $this->db->escape($data['paypalemail']) . "', 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']) . "' , newsletter = '" . $this->db->escape($data['newsletter']) . "', email = '" . $this->db->escape($data['email']) . "' WHERE valuer_id = '" . (int)$this->valuer->getId() . "'");
		}
	}
	
	public function add_sketch_content($data) {
			$designer = $this->designer->getid();
			$this->db->query("INSERT INTO " . DB_PREFIX . "sketch_comments SET sketch_assignment_id = '" . $this->db->escape($data['sketch_assignment_id']) . "', is_customer = 2 , comment = '" . $this->db->escape($data['comment']) . "', name = '" . $this->db->escape($data['name']) . "',customer_designer_id = '".$designer."' ,creation_date_time = NOW()");
	}
	
	public function getdesignername($designer_id) {
		$query = $this->db->query("select dispname as name from designer where designer_id= '" . (int)$designer_id . "'");
		
		return $query->row['name']; 
	} 
	public function getdesignerDispname($designer_id) { 
		$query = $this->db->query("select dispname from designer where designer_id= '" . (int)$designer_id . "'");
		
		return $query->row['dispname']; 
	} 
	public function GetHname($contest_id) {
		$query = $this->db->query("select name from contest_master where contest_id= '" . (int)$contest_id . "'");
		
		return $query->row['name']; 
	} 
	
	public function get_valuer_content() {
		$query = $this->db->query("select description from " . DB_PREFIX . "information_description where information_id=32");
		
		return $query->row['description']; 
	} 
	
	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 passing_valuer_id($valuer_id) { 
	
		$sql = "SELECT sketch_id , product_id , image FROM " . DB_PREFIX . "sketches_master WHERE valuer_id = '" . $valuer_id . "'";
		
		$query = $this->db->query($sql);
		
		return $query->rows;	
	} 
	
	public function get_valuer_profiledatas() { 
	
		$sql = "SELECT * FROM " . DB_PREFIX . "valuer where status=1";
		
		$query = $this->db->query($sql);
		
		return $query->rows;	
	}
	
	public function passing_id($designer_id) { 
	
		$sql = "SELECT sm.sketch_id , sm.product_id , sm.image FROM " . DB_PREFIX . "sketches_master sm," . DB_PREFIX . "contest_sketch_assignment cs WHERE  cs.sketch_id=sm.sketch_id AND sm.designer_id = '" . $designer_id . "'";
		
		$query = $this->db->query($sql);
		
		return $query->rows;	
	}
	public function passing_id1($designer_id,$contest_id) { 
	
		$sql = "SELECT sm.sketch_id , sm.product_id , sm.image FROM " . DB_PREFIX . "sketches_master sm," . DB_PREFIX . "contest_sketch_assignment cs WHERE  cs.sketch_id=sm.sketch_id AND sm.designer_id = '" . $designer_id . "' AND cs.contest_id = '" . $contest_id . "'";
		
		$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 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 editPassword($email, $password) {
      	$this->db->query("UPDATE " . DB_PREFIX . "valuer SET password = '" . $this->db->escape(md5($password)) . "' WHERE email = '" . $this->db->escape($email) . "'");
	}

	public function editNewsletter($newsletter) {
		$this->db->query("UPDATE " . DB_PREFIX . "valuer SET newsletter = '" . (int)$newsletter . "' WHERE valuer_id = '" . (int)$this->valuer->getId() . "'");
	}
					
	public function getvaluer($valuer_id) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "valuer WHERE valuer_id = '" . (int)$valuer_id . "'");
		
		return $query->row;
	}
	
	public function getvaluerByToken($token) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "valuer WHERE token = '" . $this->db->escape($token) . "' AND token != ''");
		
		$this->db->query("UPDATE " . DB_PREFIX . "valuer SET token = ''");
		
		return $query->row;
	}
		
	public function getvaluers($data = array()) {
		$sql = "SELECT *, CONCAT(c.firstname, ' ', c.lastname) AS name, cg.name AS valuer_group FROM " . DB_PREFIX . "valuer c LEFT JOIN " . DB_PREFIX . "valuer_group cg ON (c.valuer_group_id = cg.valuer_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_valuer_group_id']) && !is_null($data['filter_valuer_group_id'])) {
			$implode[] = "cg.valuer_group_id = '" . $this->db->escape($data['filter_valuer_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.valuer_id IN (SELECT valuer_id FROM " . DB_PREFIX . "valuer_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',
			'valuer_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 getTotalvaluersByEmail($email) {
		$query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "valuer WHERE email = '" . $this->db->escape($email) . "'");
		
		return $query->row['total'];
	}
	
		
	public function getsketchesmaster($con_id) {
		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  and sa.contest_id='" . $con_id . "' AND sm.designer_id ='" . (int)$this->designer->getId() . "' 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 and sa.contest_id='" . $con_id . "' AND sm.designer_id ='" . (int)$this->designer->getId() . "'";
		}
		
		$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($con_id) {
	
		$sql = "SELECT b.sketch_id,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 AND a.contest_id ='" . $con_id . "' AND c.designer_id ='" . (int)$this->designer->getId() . "' ORDER BY designer_id";
		
		$query = $this->db->query($sql);
		
		return $query->rows;	
	}
	public function getsketchidnot($con_id) {
	
		$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 AND a.contest_id ='" . $con_id . "' AND c.designer_id !='" . (int)$this->designer->getId() . "' ORDER BY designer_id";
		
		$query = $this->db->query($sql);
		
		return $query->rows;	
	}
	
		
	public function getsketchidnew2($con_id) {
	
		$sql = "SELECT c.designer_id, c.image as designerimage FROM sketches_master b, designer c WHERE b.designer_id = c.designer_id AND b.sketch_id ='" . $con_id . "'";
		
		$query = $this->db->query($sql);
		
		return $query->row['designerimage'];	
	}
	
		
	public function getsketchidnew3($con_id) {
	
		$sql = "SELECT c.designer_id, c.image as designerimage FROM sketches_master b, designer c WHERE b.designer_id = c.designer_id AND b.sketch_id ='" . $con_id . "'";
		
		$query = $this->db->query($sql);
		
		return $query->row['designer_id'];	
	}
	
		
	public function getting_designer_newdetails($con_id) {
	
		$sql = "SELECT c.designer_id, c.dispname, c.lang, c.lang1, c.lang2, c.about_yourself FROM sketches_master b, designer c WHERE b.designer_id = c.designer_id AND b.sketch_id ='" . $con_id . "'";
		
		$query = $this->db->query($sql);
		
		return $query->rows;	
	}
	 
	
	public function getting_designer_newimages($desgnn_id) { 
	
		$sql = "SELECT image FROM " . DB_PREFIX . "sketches_master WHERE designer_id = '" . $desgnn_id . "'";
		
		$query = $this->db->query($sql);
		
		return $query->rows;	
	}
	
	public function get_contest_entries($contest_id) { 
	
		$query = $this->db->query("SELECT count(*) as total FROM " . DB_PREFIX . "contest_sketch_assignment WHERE contest_id = '" . $contest_id . "'");
		
		return $query->row['total'];		
	}
	// select count(*) from sketch_comments sc,contest_sketch_assignment csa where sc.sketch_assignment_id=csa.sketch_assignment_id and csa.contest_id=101
	public function get_contest_feedback($contest_id) { 
	
		$query = $this->db->query("select count(*) as totalfeedback from " . DB_PREFIX . "sketch_comments sc," . DB_PREFIX . "contest_sketch_assignment csa WHERE sc.sketch_assignment_id=csa.sketch_assignment_id and csa.contest_id= '" . $contest_id . "' and sc.is_customer=1");
		
		return $query->row['totalfeedback'];		
	}
	 
	 
		
	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'];
	}
		
}
?>