File: /home/mostafedeg/public_html/erp/models/mysql/ext/MComreceiptdetailMySqlExtDAO.class.php
<?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);
}
}
?>