<?php

/**
 * Class that operate on table 'save'. Database Mysql.
 *
 * @author: http://phpdao.com
 * @date: 2012-08-07 11:25
 */
class SaveMySqlExtDAO extends SaveMySqlDAO {

    public function insertFirstSave($save, $savedetails) {
        $sql = 'INSERT INTO save (saveid, savename, savecurrentvalue, savedetails, savedate, conditions, userid,treeId) VALUES (?, ?, ?, ?, ?, ?, ?,?)';
        $sqlQuery = new SqlQuery($sql);


        $sqlQuery->setNumber($save->saveid);
        $sqlQuery->set($save->savename);
        $sqlQuery->set($save->savecurrentvalue);
        $sqlQuery->setString2($savedetails);
        $sqlQuery->set($save->savedate);
        $sqlQuery->setNumber($save->conditions);
        $sqlQuery->setNumber($save->userid);
        $sqlQuery->setNumber((int) $save->treeId);

        $this->executeInsert($sqlQuery);
        return $id;
    }

    public function queryAllWithOrder() {
        $sql = 'SELECT * FROM save order by saveid desc';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function updateDele($save) {
        $sql = 'UPDATE save SET savedate = ?, conditions = ?, userid = ? WHERE saveid = ?';
        $sqlQuery = new SqlQuery($sql);

        $sqlQuery->set($save->savedate);
        $sqlQuery->setNumber($save->conditions);
        $sqlQuery->setNumber($save->userid);

        $sqlQuery->setNumber($save->saveid);
        return $this->executeUpdate($sqlQuery);
    }

    public function updateSaveValue($save) {
        $sql = 'UPDATE save SET  savecurrentvalue = ?,  userid = ? WHERE saveid = ?';
        $sqlQuery = new SqlQuery($sql);

        $sqlQuery->set($save->savecurrentvalue);
        $sqlQuery->setNumber($save->userid);

        $sqlQuery->setNumber($save->saveid);
        return $this->executeUpdate($sqlQuery);
    }

    public function queryWithConditionExpectId($saveid) {
        $sql = 'SELECT save.*,currency.otherconversionFactor,currency.conversionFactor,currency.name as currencyName
                FROM save
                left join currency on currency.id = save.currencyId
                where save.conditions = 0 and save.saveid != ' . $saveid . '';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function getSaveTotalCurrentValue() {
        $sql = 'SELECT SUM(savecurrentvalue) FROM save where conditions = 0 ';
        $sqlQuery = new SqlQuery($sql);
        return $this->QuerySingleResult($sqlQuery);
    }

    public function getTotalSaves() {
        $sql = 'SELECT SUM(savecurrentvalue)
			FROM save
			WHERE conditions=0
			';
        $sqlQuery = new SqlQuery($sql);
        return $this->QuerySingleResult($sqlQuery);
    }

    #############################################################

    //fatma
    ##for many users updating same cell
    //    public function updateinDBDirectly($amount, $saveid) {
    //        $sql = "UPDATE save SET
    //                `savecurrentvalue` = @max_limbs := `savecurrentvalue`+$amount
    //                WHERE `saveid`='$saveid' LIMIT 1;
    //                SELECT @max_limbs as savecurrentvalue;";
    //        $sqlQuery = new SqlQuery($sql);
    ////        return $this->executeUpdate($sqlQuery);
    //        return $this->QuerySingleResult($sqlQuery);
    //    }

    public function loadForUpdateEx($id) {
        $sql = 'SELECT save.* ,currency.conversionFactor
                FROM save
                left join currency on currency.id = save.currencyId
                WHERE saveid = ? FOR UPDATE';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($id);
        return $this->getRow($sqlQuery);
    }

    public function loadEx($id) {
        $sql = 'SELECT save.* ,currency.conversionFactor
                FROM save
                left join currency on currency.id = save.currencyId
                WHERE saveid = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($id);
        return $this->getRow($sqlQuery);
    }

    #############################################################

    public function queryAllEX($savename, $queryString = '') {
        $sql = 'SELECT *
                FROM save
                where conditions=0 and savename like  "%' . $savename . '%" ' . $queryString . '
                order by saveid desc';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllEX2($queryString = '') {
        $sql = 'SELECT save.*,currency.name as currencyName,currency.otherconversionFactor,currency.conversionFactor,ROUND((save.savecurrentvalue/currency.conversionFactor),2) as savecurrentvalueInMainCurrency
                FROM save
                join currency on currency.id = save.currencyId
                where save.conditions=0 ' . $queryString . '
                ORDER BY sortby DESC, savecurrentvalueInMainCurrency DESC';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllEXOne2($queryString = '') {
        $sql = 'SELECT save.*,currency.name as currencyName,currency.otherconversionFactor,currency.conversionFactor,ROUND((save.savecurrentvalue/currency.conversionFactor),2) as savecurrentvalueInMainCurrency
                FROM save
                join currency on currency.id = save.currencyId
                where save.conditions=0 ' . $queryString . '
                ORDER BY sortby DESC, savecurrentvalueInMainCurrency DESC';
        $sqlQuery = new SqlQuery($sql);
        return $this->getRow($sqlQuery);
    }

    public function queryWithConditionWithQueryString($queryString) {
        $sql = 'SELECT save.*,currency.otherconversionFactor,currency.conversionFactor,currency.name as currencyName
                FROM save
                left join currency on currency.id = save.currencyId
                where save.conditions = 0' . $queryString;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function markSaveAsNOTInUse($saveid = 0) {
        $sql = 'UPDATE save SET  inUse = 0 where saveid = ' . $saveid;
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

    public function createSaveInUseSP() {

        $_SESSION['stop_checking_result'] = 1;

        $sql = 'DROP PROCEDURE IF EXISTS saveInUse;';
        $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 saveInUse(IN id INT, OUT saveidis INT, OUT savecurrentvalueis FLOAT, OUT currencyIdis INT, OUT treeIdis INT, OUT conversionFactoris FLOAT)
BEGIN
  DECLARE in_use_flag INT DEFAULT 1;
  SELECT inUse FROM save WHERE saveid = id INTO in_use_flag;

  IF in_use_flag = 0 THEN
    UPDATE save SET inUse = 1 WHERE saveid = id;
    SELECT saveid, savecurrentvalue, currencyId, treeId, currency.conversionFactor INTO saveidis, savecurrentvalueis, currencyIdis, treeIdis, conversionFactoris
    FROM save
    LEFT JOIN currency ON currency.id = save.currencyId
    WHERE saveid = id;
  ELSE
    SELECT -10, -10, -10, -10, -10 INTO saveidis, savecurrentvalueis, currencyIdis, treeIdis, conversionFactoris;
  END IF;
END


';
        $sqlQuery = new SqlQuery($sql);
        $affected_rows = $this->executeUpdate($sqlQuery);
        unset($_SESSION['stop_checking_result']);
        return $affected_rows;
    }

    public function callSaveInUseSP($saveid) {
        if ($saveid > 0) {
            $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 saveInUse($saveid,@saveidis,@savecurrentvalueis,@currencyIdis,@treeIdis,@conversionFactoris)";

            $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 @saveidis=-10,@savecurrentvalueis=-10,@currencyIdis=-10,@treeIdis=-10,@conversionFactoris=-10") || !$mysqli->query($callSql)) {
                echo "CALL failed: (" . $mysqli->errno . ") " . $mysqli->error;
            }

            if (!($res = $mysqli->query("SELECT @saveidis as saveid,@savecurrentvalueis as savecurrentvalue,@currencyIdis as currencyId,@treeIdis as treeId,@conversionFactoris as conversionFactor"))) {
                echo "Fetch failed: (" . $mysqli->errno . ") " . $mysqli->error;
            }
            $row = $res->fetch_assoc();
            $rowAsObj = new stdClass();
            foreach ($row as $key => $value) {
                $rowAsObj->$key = $value;
            }
            return $rowAsObj;
        }
    }

}