File: /home/mostafedeg/public_html/erp/models/mysql/ext/SupplierMySqlExtDAO.class.php
<?php
/**
* Class that operate on table 'supplier'. Database Mysql.
*
* @author: http://phpdao.com
* @date: 2013-04-06 10:57
*/
class SupplierMySqlExtDAO extends SupplierMySqlDAO
{
public function insertFirstSupplier($supplier)
{
$sql = 'INSERT INTO supplier (supplierid, suppliername, supplieraddress, supplierphone, suppliercurrentDebt, supplierdetails, conditions, userid, supplierdate) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($supplier->supplierid);
$sqlQuery->set($supplier->suppliername);
$sqlQuery->set($supplier->supplieraddress);
$sqlQuery->set($supplier->supplierphone);
$sqlQuery->set($supplier->suppliercurrentDebt);
$sqlQuery->setString2($supplier->supplierdetails);
$sqlQuery->setNumber($supplier->conditions);
$sqlQuery->setNumber($supplier->userid);
$sqlQuery->set($supplier->supplierdate);
$this->executeInsert($sqlQuery);
return $id;
}
public function loadExt($id)
{
$sql = 'SELECT * FROM supplier WHERE supplierid = ?';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($id);
return $this->getList($sqlQuery);
}
public function updatedept($supplier)
{
$sql = 'UPDATE supplier SET suppliercurrentDebt = ?,suppliercurrentDebtInCurrency= ? , userid = ?, supplierdate = ? WHERE supplierid = ?';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->set($supplier->suppliercurrentDebt);
$sqlQuery->set($supplier->suppliercurrentDebtInCurrency);
$sqlQuery->setNumber($supplier->userid);
$sqlQuery->set($supplier->supplierdate);
$sqlQuery->setNumber($supplier->supplierid);
return $this->executeUpdate($sqlQuery);
}
public function updateSuppDept($supid, $Supdebt)
{
$sql = 'UPDATE supplier SET suppliercurrentDebt = "' . $Supdebt . '"
WHERE supplierid = "' . $supid . '"';
$sqlQuery = new SqlQuery($sql);
//print_r($sqlQuery);
return $this->executeUpdate($sqlQuery);
}
public function updateSuppliersDeptInTermsOfTheirCurrency()
{
$sql = 'UPDATE supplier
join currency on currency.id=supplier.sullpierCurrencyid
SET suppliercurrentDebt = suppliercurrentDebtInCurrency/conversionFactor';
$sqlQuery = new SqlQuery($sql);
return $this->executeUpdate($sqlQuery);
}
//pagination
public function queryAllWithLimit($startpoint, $perpage)
{
$sql = 'SELECT * FROM supplier
ORDER BY supplierid ASC
limit ' . $startpoint . ',' . $perpage . '';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllForShow()
{
$sql = 'SELECT supplier.* ,currency.name currencyName
FROM supplier
left join currency on currency.id=supplier.sullpierCurrencyid
where supplier.conditions != 2
ORDER BY supplierid ASC';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllsup()
{
$sql = 'SELECT * FROM supplier WHERE conditions=0 ORDER BY supplierdate DESC ,supplierid DESC ';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function querysuppliercurrentDebt($id)
{
$sql = 'SELECT suppliercurrentDebt
from supplier
where supplierid= "' . $id . '"';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
/*
* Update record in table to make conditions field = 1 to mark deleted status.
*/
public function deletetemp($supplierid)
{
$sql = 'UPDATE supplier SET conditions = 1
WHERE supplierid =' . $supplierid . ' ';
$sqlQuery = new SqlQuery($sql);
return $this->executeUpdate($sqlQuery);
}
public function returndelete($supplierid)
{
$sql = 'UPDATE supplier SET conditions = 0
WHERE supplierid =' . $supplierid . ' ';
$sqlQuery = new SqlQuery($sql);
return $this->executeUpdate($sqlQuery);
}
public function loadSupplierBySupplierdebtchangeId($supplierdebtchangeid)
{
$sql = 'SELECT suppliername
FROM supplier, supplierdebtchange
WHERE supplierdebtchangeid = ?
AND supplier.supplierid = supplierdebtchange.supplierid';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($supplierdebtchangeid);
return $this->getRow($sqlQuery);
}
public function getSuppliersTotalDept()
{
$sql = 'SELECT SUM(suppliercurrentDebt)
FROM supplier
WHERE conditions = 0';
$sqlQuery = new SqlQuery($sql);
return $this->QuerySingleResult($sqlQuery);
}
public function getSuppliernamefrombuybill($buybiilid)
{
$sql = 'SELECT supplier.suppliername,supplier.supplierid
FROM `supplier`
LEFT JOIN buybill ON buybill.buybillsupplierid = supplier.`supplierid`
LEFT JOIN buyandruternbill ON buyandruternbill.buybillsupplierid = supplier.`supplierid`
WHERE buybill.buybillid =' . $buybiilid . '
OR buyandruternbill.buybillid =' . $buybiilid . '';
//print_r("<br>".$sql);
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
public function getAutoIncrementValue($DBName)
{
$sql = 'SELECT `AUTO_INCREMENT`
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = "' . $DBName . '"
AND TABLE_NAME = "supplier" ';
$sqlQuery = new SqlQuery($sql);
return $this->querySingleResult($sqlQuery);
}
public function checkCategory($category)
{
$sql = 'SELECT * FROM supplier WHERE conditions=0 and category_id LIKE "%' . $category . '%"';
// echo $sql;
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function markSupplierAsNOTInUse($supplierid = 0)
{
$sql = 'UPDATE supplier SET inUse = 0 where supplierid = ' . $supplierid;
$sqlQuery = new SqlQuery($sql);
return $this->executeUpdate($sqlQuery);
}
public function createSupplierInUseSP()
{
$_SESSION['stop_checking_result'] = 1;
$sql = 'DROP PROCEDURE IF EXISTS supplierInUse;';
$sqlQuery = new SqlQuery($sql);
$this->executeUpdate($sqlQuery);
//create procedure goes row by row to re calc debtbefore and after for each row in clientdebtchangetemp
$sql = '
CREATE PROCEDURE supplierInUse(IN id INT,OUT debtis VARCHAR(256),OUT nameis VARCHAR(256),OUT dailyid INT,OUT treeIdis VARCHAR(256))
BEGIN
DECLARE in_use_flag INT DEFAULT 1;
SELECT inUse FROM supplier WHERE supplierid = id INTO in_use_flag;
IF in_use_flag = 0 THEN
update supplier set inUse=1 where supplierid = id;
SELECT suppliercurrentDebt,suppliername,dailyentryid,treeId INTO debtis,nameis,dailyid,treeIdis FROM supplier WHERE supplierid = id ;
ELSE
SELECT "in_use","in_use","in_use","in_use" INTO debtis,nameis,dailyid,treeIdis;
END IF;
END
';
$sqlQuery = new SqlQuery($sql);
$affected_rows = $this->executeUpdate($sqlQuery);
unset($_SESSION['stop_checking_result']);
return $affected_rows;
}
public function callSupplierInUseSP($supplierid)
{
if ($supplierid > 1) {
$mysqli = new mysqli(ConnectionProperty::getHost(), ConnectionProperty::getUser(), ConnectionProperty::getPassword(), ConnectionProperty::getDatabase());
if ($mysqli->connect_errno) {
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
##checks
$callSql = "CALL supplierInUse($supplierid,@supplierdebt,@suppliername,@dailyentryid,@treeIdis)";
$myfile = fopen("backup/" . date("Y-m-d") . ".txt", "a+") or die("Unable to open file!");
$txt = $callSql . ";\r\n";
fwrite($myfile, $txt);
fclose($myfile);
if (!$mysqli->query("SET @supplierdebt='in_use',@suppliername='in_use',@dailyentryid='in_use',@treeIdis='in_use'") || !$mysqli->query($callSql)) {
echo "CALL failed: (" . $mysqli->errno . ") " . $mysqli->error;
}
if (!($res = $mysqli->query("SELECT @supplierdebt as suppliercurrentDebt,@suppliername as suppliername,@dailyentryid as dailyentryid,@treeIdis as treeId"))) {
echo "Fetch failed: (" . $mysqli->errno . ") " . $mysqli->error;
}
$row = $res->fetch_assoc();
$rowAsObj = new stdClass();
foreach ($row as $key => $value) {
$rowAsObj->$key = $value;
}
return $rowAsObj;
}
}
//pagination
public function queryAllWithQueryString($queryString)
{
$sql = 'SELECT SQL_CACHE *
FROM supplier
where 1 ' . $queryString;
//echo $sql;
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function getSupplierByType($category)
{
$sql = 'SELECT * FROM supplier WHERE conditions = 0 and FIND_IN_SET("'.$category.'", typesupplier_id)';// LIKE "%' . $category . '%"';
// echo $sql;
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllsupWithDebt()
{
$sql = 'SELECT * FROM supplier WHERE conditions = 0 and suppliercurrentDebt != 0';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllForShowWithCondition()
{
$sql = 'SELECT supplier.* ,currency.name currencyName
FROM supplier
left join currency on currency.id=supplier.sullpierCurrencyid
where supplier.conditions = 0
ORDER BY supplierid ASC';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function deletetempWithName($supplierid, $name)
{
$sql = 'UPDATE supplier SET conditions = 1, suppliername = "' . $name . '"
WHERE supplierid =' . $supplierid . ' ';
$sqlQuery = new SqlQuery($sql);
return $this->executeUpdate($sqlQuery);
}
public function returndeleteWithName($supplierid, $name)
{
$sql = 'UPDATE supplier SET conditions = 0, suppliername = "' . $name . '"
WHERE supplierid =' . $supplierid . ' ';
$sqlQuery = new SqlQuery($sql);
return $this->executeUpdate($sqlQuery);
}
public function deleteFinallyWithName($supplierid, $name)
{
$sql = 'UPDATE supplier SET conditions = 2, suppliername = "' . $name . '"
WHERE supplierid =' . $supplierid . ' ';
$sqlQuery = new SqlQuery($sql);
return $this->executeUpdate($sqlQuery);
}
}