File: /home/mostafedeg/public_html/erp/models/mysql/ext/ProductserialMySqlExtDAO.class.php
<?php
/**
* Class that operate on table 'productserial'. Database Mysql.
*
* @author: http://phpdao.com
* @date: 2013-04-06 10:57
*/
class ProductserialMySqlExtDAO extends ProductserialMySqlDAO {
public function getbuybilldetail($buybillid) {
$sql = 'SELECT * FROM productserial
where billid = ' . $buybillid . ' ';
$sqlQuery = new SqlQuery($sql);
//$sqlQuery->setNumber($value);
//print_r($sqlQuery);
return $this->getList($sqlQuery);
}
public function queryAllByQueryString($queryString = '') {
$sql = 'SELECT *
FROM productserial
where 1 ' . $queryString . '';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryLastWithProductId($productid) {
$sql = 'SELECT * FROM productserial where productid =' . $productid . '
order by productserailid desc limit 1';
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
public function queryLastWithProductIdAndDon($productid, $don) {
$sql = 'SELECT * FROM productserial where productid =' . $productid . '
and don = ' . $don . ' and del = 0
order by productserailid desc limit 1';
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
public function queryLastWithProductIdAndDonExt($productid) {
$sql = 'SELECT * FROM productserial where productid =' . $productid . '
and don > 0 and del = 0
limit 1';
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
public function getProductAvailableParcodes($productid, $sizeid = 0, $colorId = 0, $storeId = 0) {
$sql = 'SELECT * FROM productserial where productid =' . $productid . ' and sizeId = ' . $sizeid . ' and colorId=' . $colorId . ' and storeid=' . $storeId . '
and don > 0 and del = 0';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryLastWithProductIdAndSerial($productid, $parcode, $queryString = '') {
$sql = 'SELECT * FROM productserial
where productid =' . $productid . '
AND serialnumber = "' . $parcode . '" ' . $queryString . '
and del = 0 order by productserailid asc'; //and don>0
//print_r($sql.'<br>');
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
public function queryWithProductIdandbillid($productid, $billid, $type, $buybilldetailid) {
// $sql = 'SELECT productserial.*,soldserialproduct.quantity FROM productserial
// left join soldserialproduct
// on soldserialproduct.productserailid=productserial.productserailid
//
// where productid =' . $productid . '
// and billid=' . $billid . ' and type=' . $type . ' and buybilldetailid=' . $buybilldetailid . '
//
// order by productserial.productserailid '; //group by soldserialproduct.productserailid
$sql = 'SELECT productserial.*,soldserialproduct.quantity
FROM productserial
left join (
select productserailid,sum(soldserialproduct.quantity) as quantity
from soldserialproduct
where del=0
group by soldserialproduct.productserailid
) as soldserialproduct on soldserialproduct.productserailid=productserial.productserailid
where productid =' . $productid . '
and billid=' . $billid . ' and type=' . $type . ' and buybilldetailid=' . $buybilldetailid . '
order by productserial.productserailid '; //
//print_r($sql . "<br>");
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function deleteBybillid($billid, $type, $queryString = '') {
$sql = 'DELETE FROM productserial WHERE billid = ' . $billid . '
and type =' . $type . ' ' . $queryString . '
';
$sqlQuery = new SqlQuery($sql);
//print_r($sql);
return $this->executeUpdate($sqlQuery);
}
public function deleteByQuerySting($queryString = '') {
$sql = 'DELETE FROM productserial WHERE 1 ' . $queryString . '';
$sqlQuery = new SqlQuery($sql);
return $this->executeUpdate($sqlQuery);
}
public function updateDelBybillid($billid, $type, $del, $queryString = '') {
$sql = 'update productserial set del= ' . $del . ' WHERE billid = ' . $billid . '
and type =' . $type . ' ' . $queryString . '
';
$sqlQuery = new SqlQuery($sql);
//print_r($sql);
return $this->executeUpdate($sqlQuery);
}
public function queryWithbillid($billid, $type) {
$sql = 'SELECT productserial.*,product.productName,product.productSellUnitPrice FROM productserial
join product
on product.productId=productserial.productid
where billid=' . $billid . ' and productserial.type=' . $type . '
order by productserial.productserailid ';
//print_r($sql."<br>");
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryallEx() {
$sql = 'SELECT productserial.*,product.productName,product.productSellUnitPrice FROM productserial
join product
on product.productId=productserial.productid
order by productserial.productserailid ';
//print_r($sql."<br>");
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryallExbyid($productserailid) {
$sql = 'SELECT productserial.*,product.productName, product.productSellUnitPrice
, (soldserialproduct.sellbillid) as soldsellbillid, soldserialproduct.sellbilltype ,soldserialproduct.quantity, product.productCatId
FROM productserial
join product
on product.productId=productserial.productid
left join soldserialproduct
on soldserialproduct.productserailid=productserial.productserailid
where productserial.productserailid=' . $productserailid . '
order by productserial.productserailid ';
//print_r($sql."<br><br><br><br>");
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryallExbyidExt($productserailid) {
$sql = 'SELECT productserial.*,product.productName,product.productSellUnitPrice FROM productserial
JOIN product
ON product.productId = productserial.productid
WHERE productserial.productserailid = ' . $productserailid . '
ORDER BY productserial.productserailid ';
//print_r($sql."<br><br><br>");
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
public function updatedel($productserialid, $del) {
$sql = 'UPDATE productserial SET del = ' . $del . '
WHERE productserailid = ' . $productserialid . '';
$sqlQuery = new SqlQuery($sql);
return $this->executeUpdate($sqlQuery);
}
public function delByBillid($billid) {
$sql = 'UPDATE productserial SET del = 1
WHERE billid = ' . $billid . '';
$sqlQuery = new SqlQuery($sql);
return $this->executeUpdate($sqlQuery);
}
public function queryallExbydiff($condation, $num) {
$sql = 'SELECT productserial.*,(DATEDIFF(
productserial.`enddate` , productserial.`startdate`)
) AS different,product.productName,product.productSellUnitPrice
,(soldserialproduct.sellbillid) as soldsellbillid,soldserialproduct.sellbilltype,soldserialproduct.quantity
FROM productserial
left join soldserialproduct
on soldserialproduct.productserailid=productserial.productserailid
join product
on product.productId=productserial.productid
where (DATEDIFF(
productserial.`enddate` , productserial.`startdate`
) ) ' . $condation . ' ' . $num . '
order by productserial.productserailid ';
//print_r($sql."<br>");
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryallExbydifflimted($start, $end, $condation, $num) {
$sql = 'SELECT productserial.*,DATEDIFF(
productserial.`enddate`, productserial.`startdate`
) AS different,product.productName,product.productSellUnitPrice FROM productserial
join product
on product.productId=productserial.productid
where DATEDIFF(
productserial.`enddate` , productserial.`startdate`
) ' . $condation . ' ' . $num . '
order by productserial.productserailid desc
limit ' . $start . ', ' . $end . '';
//print_r($sql."<br>");
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryallExbydiffExt($condation, $num) {
$sql = 'SELECT productserial.*, DATEDIFF(productserial.`enddate`, productserial.`startdate`) AS different, product.productName, product.productSellUnitPrice, product.productCatId FROM productserial
join product
on product.productId = productserial.productid
where DATEDIFF(productserial.`enddate`, productserial.`startdate`) ' . $condation . ' ' . $num . '
order by productserial.productserailid desc';
//print_r($sql."<br>");
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryallExbydiffExt2($condation, $num, $queryString) {
$sql = 'SELECT productserial.*, DATEDIFF(productserial.`enddate`,NOW()) AS different, product.productName, product.productSellUnitPrice, product.productCatId FROM productserial
join product
on product.productId = productserial.productid
where DATEDIFF(productserial.`enddate`,NOW()) ' . $condation . ' ' . $num . '
' . $queryString . '
order by productserial.productserailid desc';
// print_r($sql . "<br>");
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function updateDon($productserial) {
$sql = 'UPDATE productserial SET don = ? WHERE productserailid = ?';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($productserial->don);
$sqlQuery->setNumber($productserial->productserailid);
return $this->executeUpdate($sqlQuery);
}
public function updateDonExt($productserial) {
$sql = 'UPDATE productserial SET don = ?, sellbillid = ? WHERE productserailid = ?';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($productserial->don);
$sqlQuery->setNumber($productserial->sellbillid);
$sqlQuery->setNumber($productserial->productserailid);
return $this->executeUpdate($sqlQuery);
}
public function getProductQuantity($productserailId) {
$sql = 'SELECT productserial.don
FROM productserial
WHERE productserailid = ' . $productserailId . '';
$sqlQuery = new SqlQuery($sql);
//print_r('<br>'.$sql);
return $this->querySingleResult($sqlQuery);
}
public function queryAllbyserial($serialnumber) {
$sql = 'SELECT * FROM productserial where serialnumber=' . $serialnumber . '
';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryallisdSerial($productid, $parcode, $queryString = '') {
$sql = 'SELECT * FROM productserial
where productid =' . $productid . '
AND serialnumber = "' . $parcode . '" ' . $queryString . '
AND del = 0';
//print_r($sql);
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
public function queryAllbybillandtype($billid, $type) {
$sql = 'SELECT * FROM `productserial` WHERE
`productserailid` in
(SELECT `productserailid` FROM `soldserialproduct` )
and `billid`= ' . $billid . ' and `type`=' . $type . ' ';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryallbuyandsellbill($productid, $serialnumber) {
$sql = 'SELECT productserial. * , sellbill.sellbillid, sellbill.sellbilldate,
sellbillandrutern.sellbillid, sellbillandrutern.sellbilldate,
buyandruternbill.buybilldate, buybill.buybilldate, buyandruternbill.buybillid, buybill.buybillid
,product.productName,product.expireDate
FROM productserial
join product
on productserial.productid=product.productId
LEFT JOIN sellbill ON sellbill.sellbillid = productserial.`sellbillid`
LEFT JOIN sellbillandrutern ON sellbillandrutern.sellbillid = productserial.`sellbillid`
LEFT JOIN buybill ON buybill.buybillid = productserial.`billid`
LEFT JOIN buyandruternbill ON buyandruternbill.buybillid = productserial.`billid`
WHERE productserial.productid =' . $productid . '
and productserial.serialnumber =' . $serialnumber . '
and productserial.del =0
ORDER BY productserial.productserailid ';
//print_r("<pre>".$sql);
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
public function queryBydel($value) {
$sql = 'SELECT * FROM productserial WHERE del = ?';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($value);
return $this->getList($sqlQuery);
}
public function loadserial($id) {
$sql = 'SELECT * FROM productserial WHERE productserailid = ?
and del =0';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->set($id);
return $this->getRow($sqlQuery);
}
public function getProductIdWithItsSerial($value) {
$sql = 'SELECT productid FROM productserial WHERE serialnumber = ?';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->set($value);
return $this->querySingleResult($sqlQuery);
}
public function updateStoreByQueryString($storeid, $moreUpdateColums = '', $queryString = '') {
$sql = 'UPDATE productserial SET storeid = ' . $storeid . ' ' . $moreUpdateColums . '
WHERE 1 ' . $queryString . '';
$sqlQuery = new SqlQuery($sql);
return $this->executeUpdate($sqlQuery);
}
public function queryByQueryString($queryString = '') {
$sql = 'SELECT productserial.* ,storeName,productCatName,productName,product.productDescription,size.name as sizeName,color.name as colorName,product.productSellAllPrice,product.productSellUnitPrice ,product.productSellHalfPrice
FROM productserial
join product on productserial.productid=product.productId
join productcat on product.productCatId=productcat.productCatId
join store on productserial.storeid=store.storeId
left join sizecolor as size on size.id = productserial.sizeid
left join sizecolor as color on color.id = productserial.colorid
where don > 0 and del = 0 ' . $queryString;
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryByQueryStringSimple($queryString = '') {
$sql = 'SELECT productserial.* ,storeName
FROM productserial
join store on productserial.storeid=store.storeId
where don > 0 and del = 0 ' . $queryString;
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryByserialnumber_Store_ProductAndExpireDateNotDel($serialnumber, $productid, $sizeid, $colorid, $storeid, $enddate) {
$sql = 'SELECT * FROM productserial WHERE serialnumber = "' . $serialnumber . '" and productid =' . $productid . ' and sizeid =' . $sizeid . ' and colorid =' . $colorid . ' and storeid=' . $storeid . ' and enddate ="' . $enddate . '" and del= 0';
//echo $sql;
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
}
?>