<?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);
    }
}
