<?php
/**
 * Class that operate on table 'm_comreceiptdetail'. Database Mysql.
 *
 * @author: http://phpdao.com
 * @date: 2014-09-07 16:06
 */
class MComreceiptdetailMySqlExtDAO extends MComreceiptdetailMySqlDAO{

	public function getInitialCostByOurserialEX($value){
		$sql = 'SELECT m_comreceiptdetail.initialcost 
				FROM m_comreceiptdetail
				WHERE ourserial = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->set($value);
		return $this->querySingleResult($sqlQuery);
	}
	
	public function getdiscountByOurserialEX($value){
		$sql = 'SELECT discount 
				FROM m_comreceiptdetail
				WHERE ourserial = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->set($value);
		return $this->querySingleResult($sqlQuery);
	}
	
	public function getProblemAndcommentByOurserialEX($value){
		$sql = 'SELECT problem,comment 
				FROM m_comreceiptdetail
				WHERE ourserial = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->set($value);
		//print_r($sqlQuery);
		return $this->getRow($sqlQuery);
	}
	
	public function updateCurrentStage($currentstage,$id){
		$sql = 'UPDATE m_comreceiptdetail SET  currentstage = ? WHERE id = ?';
		$sqlQuery = new SqlQuery($sql);
		
		$sqlQuery->setNumber($currentstage);
		$sqlQuery->setNumber($id);
		return $this->executeUpdate($sqlQuery);
	}
	
	public function updateCurrentStageUsingOurSerial($currentstage,$ourserial){
		$sql = 'UPDATE m_comreceiptdetail SET  currentstage = ? WHERE ourserial = ?';
		$sqlQuery = new SqlQuery($sql);
		
		$sqlQuery->setNumber($currentstage);
		$sqlQuery->set($ourserial);
		return $this->executeUpdate($sqlQuery);
	}
	
	public function updatediscountByOurSerial($discount,$ourserial){
		$sql = 'UPDATE m_comreceiptdetail SET  discount = ? WHERE ourserial = ?';
		$sqlQuery = new SqlQuery($sql);
		
		$sqlQuery->setNumber($discount);
		$sqlQuery->set($ourserial);
		return $this->executeUpdate($sqlQuery);
	}
	
	public function queryallrecept($id,$type){
		$sql = 'SELECT m_comreceiptdetail.* ,product.productName , 
		supplier.suppliername,m_producthistory.stageFrom,m_producthistory.stageTo,m_producthistory.cost
		,m_producthistory.status ,m_stage.title
		FROM m_comreceiptdetail join product
		on product.productid=m_comreceiptdetail.productid LEFT JOIN supplier
		ON m_comreceiptdetail.supplierid=supplier.supplierid
		join  m_producthistory
		ON m_comreceiptdetail.ourserial= m_producthistory.ourSerial
		join  m_stage
		ON  m_stage.id= m_comreceiptdetail.currentstage
		 WHERE m_comreceiptdetail.receiptid  = '.$id.' 
		 and  m_comreceiptdetail.granttype = '.$type.' 
		 and  m_comreceiptdetail.del = 0 
		 group by m_comreceiptdetail.id
		';
		
		//print_r("<br>".$sql);
		$sqlQuery = new SqlQuery($sql);
		return $this->getList($sqlQuery);
	}
	
	public function getProductsInpreparationStage($branchId){
		$sql = 'SELECT id,receiptid,ourserial,productName,suppliername,productserial
				FROM m_comreceiptdetail 
				JOIN product ON product.productId=m_comreceiptdetail.productId
				JOIN supplier ON supplier.supplierid=m_comreceiptdetail.supplierid
				WHERE currentstage = 4
				AND m_comreceiptdetail.del =0
				AND  m_comreceiptdetail.branchid='.$branchId.' ';
		$sqlQuery = new SqlQuery($sql);
		//print_r($sql);
		return $this->getList($sqlQuery);
	}
	
	public function getProductsInInsuranceStage(){
		$sql = 'SELECT id,receiptid,ourserial,productName,suppliername,productserial
				FROM m_comreceiptdetail 
				JOIN product ON product.productId=m_comreceiptdetail.productId
				JOIN supplier ON supplier.supplierid=m_comreceiptdetail.supplierid
				WHERE currentstage = 10
				AND m_comreceiptdetail.del =0';
		$sqlQuery = new SqlQuery($sql);
		return $this->getList($sqlQuery);
	}
	
	public function getProductsToGo2($supplierId,$branchid){
		$sql = 'SELECT id,receiptid,ourserial,productName,suppliername,productserial
				FROM m_comreceiptdetail 
				JOIN product ON product.productId=m_comreceiptdetail.productId
				JOIN supplier ON supplier.supplierid=m_comreceiptdetail.supplierid
				WHERE currentstage = 7
				AND supplier.supplierid = '.$supplierId.'
				AND m_comreceiptdetail.branchid = '.$branchid.'
				AND m_comreceiptdetail.del =0';
		$sqlQuery = new SqlQuery($sql);
		//print_r($sql);
		return $this->getList($sqlQuery);
	}
	
	public function getProductsToGo3($supplierId,$branchid){
		$sql = 'SELECT id,receiptid,ourserial,productName,suppliername,productserial
				FROM m_comreceiptdetail 
				JOIN product ON product.productId=m_comreceiptdetail.productId
				JOIN supplier ON supplier.supplierid=m_comreceiptdetail.supplierid
				WHERE currentstage = 10
				AND supplier.supplierid = '.$supplierId.'
				AND m_comreceiptdetail.branchid = '.$branchid.'
				AND m_comreceiptdetail.del =0';
		$sqlQuery = new SqlQuery($sql);
		//print_r($sql);
		return $this->getList($sqlQuery);
	}
	
	public function getProductsToGo(){
		$sql = 'SELECT id,receiptid,ourserial,productName,suppliername,productserial
				FROM m_comreceiptdetail 
				JOIN product ON product.productId=m_comreceiptdetail.productId
				JOIN supplier ON supplier.supplierid=m_comreceiptdetail.supplierid
				WHERE currentstage = 7
				AND m_comreceiptdetail.del =0';
		$sqlQuery = new SqlQuery($sql);
		return $this->getList($sqlQuery);
	}
	
	public function getSupplierVSNoOfProducts($branchid){
		$sql = 'SELECT suppliername,m_comreceiptdetail.supplierid, count( m_comreceiptdetail.ourserial ) as myCount
				FROM m_comreceiptdetail
				JOIN supplier ON supplier.supplierid = m_comreceiptdetail.supplierid
				WHERE m_comreceiptdetail.currentstage = 7
				AND m_comreceiptdetail.branchid = '.$branchid.'
				GROUP BY m_comreceiptdetail.supplierid';
				//print_r($sql);
		$sqlQuery = new SqlQuery($sql);
		return $this->getList($sqlQuery);
	}
	
	public function getSupplierVSNoOfProducts2($branchid){
		$sql = 'SELECT suppliername,m_comreceiptdetail.supplierid, count( m_comreceiptdetail.ourserial ) as myCount
				FROM m_comreceiptdetail
				JOIN supplier ON supplier.supplierid = m_comreceiptdetail.supplierid
				WHERE m_comreceiptdetail.currentstage = 10
				AND m_comreceiptdetail.branchid = '.$branchid.'
				GROUP BY m_comreceiptdetail.supplierid';
		$sqlQuery = new SqlQuery($sql);
		return $this->getList($sqlQuery);
	}
	
	
	public function getDataOfSelectedProductsInpreparationStage($IDs){
		$sql = 'SELECT id,receiptid,ourserial,productName,supplierid
				FROM m_comreceiptdetail 
				JOIN product ON product.productId=m_comreceiptdetail.productId
				WHERE id IN ( '.$IDs.' )';
		$sqlQuery = new SqlQuery($sql);
		return $this->getList($sqlQuery);
	}
	
	public function loadcase($id){
		$sql = "SELECT * FROM  m_comreceiptdetail
				WHERE ourserial LIKE '".$id."%' order by ourserial desc";

		$sqlQuery = new SqlQuery($sql);
		
		return $this->getRow($sqlQuery);
	}
	
	public function updatedel($del,$id){
		$sql = 'UPDATE m_comreceiptdetail SET  del = '.$del.'  WHERE  ourserial = "'.$id.'" ';
		
		$sqlQuery = new SqlQuery($sql);
		return $this->executeUpdate($sqlQuery);
	}
	
	
	public function updatedetail($mComreceiptdetail){
		$sql = 'UPDATE m_comreceiptdetail SET   productserial = ?, productId = ?, ourserial = ?, granttype = ?, sellbillid = ?, sellbilldate = ?, buybillid = ?, buybilldate = ?, insduration = ?, insstatus = ?, problem = ?, attach = ?, comment = ?, shipcost = ?, initialcost = ?, supplierid = ?, currentstage = ?, branchid = ?, del = ? WHERE receiptid = ?';
		$sqlQuery = new SqlQuery($sql);
		
		$sqlQuery->set($mComreceiptdetail->productserial);
		$sqlQuery->setNumber($mComreceiptdetail->productId);
		$sqlQuery->set($mComreceiptdetail->ourserial);
		$sqlQuery->setNumber($mComreceiptdetail->granttype);
		$sqlQuery->setNumber($mComreceiptdetail->sellbillid);
		$sqlQuery->set($mComreceiptdetail->sellbilldate);
		$sqlQuery->setNumber($mComreceiptdetail->buybillid);
		$sqlQuery->set($mComreceiptdetail->buybilldate);
		$sqlQuery->setNumber($mComreceiptdetail->insduration);
		$sqlQuery->setNumber($mComreceiptdetail->insstatus);
		$sqlQuery->setString2($mComreceiptdetail->problem);
		$sqlQuery->setString2($mComreceiptdetail->attach);
		$sqlQuery->setString2($mComreceiptdetail->comment);
		$sqlQuery->set($mComreceiptdetail->shipcost);
		$sqlQuery->set($mComreceiptdetail->initialcost);
		$sqlQuery->setNumber($mComreceiptdetail->supplierid);
		$sqlQuery->setNumber($mComreceiptdetail->currentstage);
		$sqlQuery->setNumber($mComreceiptdetail->branchid);
		$sqlQuery->setNumber($mComreceiptdetail->del);

		$sqlQuery->setNumber($mComreceiptdetail->receiptid);
		return $this->executeUpdate($sqlQuery);
	}

	public function updatedetaildel($del,$id){
		$sql = 'UPDATE m_comreceiptdetail SET  del = '.$del.'  WHERE  receiptid = "'.$id.'" ';
		
		$sqlQuery = new SqlQuery($sql);
		return $this->executeUpdate($sqlQuery);
	}
	
	public function loadserail($id){
		$sql = 'SELECT * FROM m_comreceiptdetail WHERE ourserial = ?
		and del =0';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->set($id);
		return $this->getRow($sqlQuery);
	}

	public function loaddata($id,$granttype){
		$sql = 'SELECT * FROM m_comreceiptdetail WHERE id = '.$id.'  and granttype = '.$granttype.'';
		$sqlQuery = new SqlQuery($sql);
		//print_r("<br>".$sql);
		return $this->getRow($sqlQuery);
	}
	
	public function getCurrentStages($value){
		$sql = 'SELECT currentstage FROM m_comreceiptdetail WHERE receiptid = "'.$value.'"';
		$sqlQuery = new SqlQuery($sql);
		return $this->getList($sqlQuery);
	}
	
	public function getReceiptId($value){
		$sql = 'SELECT receiptid FROM m_comreceiptdetail WHERE ourserial = ? LIMIT 0 , 1';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->set($value);
		return $this->querySingleResult($sqlQuery);
	}
	
	
	public function loadfordelete($ourserial){
		$sql = 'SELECT m_comreceiptdetail.`id` , m_producthistory.productHistoryId, m_check.checkId
					FROM `m_comreceiptdetail`
					JOIN m_check ON m_check.ourSerial = m_comreceiptdetail.`ourserial`
					JOIN m_producthistory ON m_producthistory.ourSerial = m_comreceiptdetail.`ourserial`
					WHERE m_comreceiptdetail.`ourserial` = "'.$ourserial.'"';
		
		//print_r("<br>".$sql);
		$sqlQuery = new SqlQuery($sql);
	
		return $this->getRow($sqlQuery);
	}

		
		
		public function updatedetaildelx($mComreceiptdetail){
		$sql = 'UPDATE m_comreceiptdetail SET  branchid = ?, del = ? WHERE id = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->setNumber($mComreceiptdetail->branchid);
		$sqlQuery->setNumber($mComreceiptdetail->del);

		$sqlQuery->setNumber($mComreceiptdetail->id);
		return $this->executeUpdate($sqlQuery);
	}
	
	public function getSerialsByBranchEX($branchId){
		$sql = 'SELECT m_comreceiptdetail.ourserial
					   
				FROM m_comreceiptdetail
				JOIN m_comreceipt ON m_comreceiptdetail.receiptid = m_comreceipt.id
				
				WHERE m_comreceiptdetail.branchid = ?
				AND m_comreceipt.status = 1
				AND m_comreceiptdetail.currentstage = 11 
				';
				
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->setNumber($branchId);
		//print_r($sqlQuery);
		return $this->getList($sqlQuery);
	}
	
	public function getRecitSerialsByBranchEX($branchId){
		$sql = 'SELECT DISTINCT m_comreceipt.receiptserial
					   
				FROM m_comreceiptdetail
				JOIN m_comreceipt ON m_comreceiptdetail.receiptid = m_comreceipt.id
				
				WHERE m_comreceiptdetail.branchid = ?
				AND m_comreceipt.status = 1
				AND m_comreceiptdetail.currentstage = 11 
				';
				
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->setNumber($branchId);
		//print_r($sqlQuery);
		return $this->getList($sqlQuery);
	}
	
	public function getClientsByBranchEX($branchId){
		$sql = 'SELECT DISTINCT client.clientname,client.clientid
					   
				FROM m_comreceiptdetail
				JOIN m_comreceipt ON m_comreceiptdetail.receiptid = m_comreceipt.id
				
				JOIN client ON client.clientid = m_comreceipt.clientid
				
				WHERE m_comreceiptdetail.branchid = ?
				AND m_comreceipt.status = 1
				AND m_comreceiptdetail.currentstage = 11 
				';
				
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->setNumber($branchId);
		//print_r($sqlQuery);
		return $this->getList($sqlQuery);
	}
	
	public function queryByBranchInDetailEX3($queryString){
		$sql = 'SELECT m_comreceiptdetail.productserial,product.productName,m_comreceiptdetail.ourserial
				,m_comreceiptdetail.granttype, m_comreceiptdetail.id,client.clientname,m_comreceipt.receiptserial
					   
				FROM m_comreceiptdetail
				JOIN m_comreceipt ON m_comreceiptdetail.receiptid = m_comreceipt.id
				
				JOIN product ON product.productId = m_comreceiptdetail.productId
				JOIN client ON client.clientid = m_comreceipt.clientid
				
				WHERE m_comreceipt.status = 1
				AND m_comreceiptdetail.currentstage = 11 
				'.$queryString.'
				';
				
		$sqlQuery = new SqlQuery($sql);
		//print_r($sql);
		return $this->getList($sqlQuery);
	}
	
	public function getSerialsWithRectId($value,$query){
		$sql = 'SELECT ourserial 
				FROM m_comreceiptdetail 
				WHERE receiptid = ?
				AND del=0 '.$query.'';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->setNumber($value);
		//print_r($sqlQuery);
		//print_r('</br>');
		return $this->getList($sqlQuery);
	}
	
	public function getSerialsWithproductserial($value,$query){
		$sql = 'SELECT ourserial FROM m_comreceiptdetail 
		WHERE  productserial = ?
		AND del=0 '.$query.'';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->setNumber($value);
		return $this->getList($sqlQuery);
	}
	
	public function getSerialsWithClientId($value,$query){
		$sql = 'SELECT ourserial 
		FROM m_comreceiptdetail 
		JOIN m_comreceipt ON m_comreceiptdetail.receiptid = m_comreceipt.id 
		WHERE m_comreceipt.clientid = ?
		AND m_comreceiptdetail.del=0 '.$query.'';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->setNumber($value);
		return $this->getList($sqlQuery);
	}
	
	public function getserialnumber2($name){
		$sql = 'SELECT DISTINCT productserial AS value , productserial AS label FROM m_comreceiptdetail
		WHERE productserial LIKE "%'.$name.'%" 
		AND del=0 And currentstage != 11';
		$sqlQuery = new SqlQuery($sql);
		return $this->getList($sqlQuery);
	}
	
	public function getserialnumber3($name){
		$sql = 'SELECT ourserial AS value , ourserial AS label FROM m_comreceiptdetail
		WHERE ourserial LIKE "%'.$name.'%" 
		AND del=0 And currentstage != 11';
		$sqlQuery = new SqlQuery($sql);
		return $this->getList($sqlQuery);
	}
	public function getGeneralSearchData2($ourserial){
		$sql = 'SELECT m_comreceiptdetail.productserial,m_comreceipt.receiptserial,product.productName
				,m_comreceiptdetail.granttype,client.clientname,m_stage.title,suppliername 
					   
				FROM m_comreceiptdetail
				JOIN m_comreceipt ON m_comreceiptdetail.receiptid = m_comreceipt.id 
				JOIN m_stage ON m_stage.id= m_comreceiptdetail.currentstage 

				JOIN product ON product.productId = m_comreceiptdetail.productId
				JOIN client ON client.clientid = m_comreceipt.clientid
				JOIN supplier ON supplier.supplierid=m_comreceiptdetail.supplierid
				
				WHERE m_comreceiptdetail.ourserial = "'.$ourserial.'"
				';
				
		$sqlQuery = new SqlQuery($sql);
		//print_r($sql);
		return $this->getRow($sqlQuery);
	}
	
	
	public function queryallserialproduct(){
		$sql = 'SELECT DISTINCT (productserial) AS productserial
					FROM m_comreceiptdetail
					where del=0';

		$sqlQuery = new SqlQuery($sql);
		return $this->getList($sqlQuery);
	}
}
?>