<?php

/**
 * Class that operate on table 'availableparcode'. Database Mysql.
 *
 * @author: http://phpdao.com
 * @date: 2020-02-04 15:18
 */
include '../../sql/ConnectionProperty.class.php';

class AvailableparcodeMySqlExtDAO extends AvailableparcodeMySqlDAO {

    public function insertBulk($insert_sql) {
        $sql = $insert_sql;
        $sqlQuery = new SqlQuery($sql);
        $affected_rows = $this->executeUpdate($sqlQuery);
        return $affected_rows;
    }

//    public function getRowsNo() {
//        $sql = 'SELECT COUNT(id) FROM availableparcode';
//        $sqlQuery = new SqlQuery($sql);
//        return $this->querySingleResult($sqlQuery);
//    }

    public function getTableRows($DBName) {
        $sql = 'SELECT `TABLE_ROWS`
                FROM  INFORMATION_SCHEMA.TABLES
                WHERE TABLE_SCHEMA = "' . $DBName . '"
                AND   TABLE_NAME   = "availableparcode" ';

        $sqlQuery = new SqlQuery($sql); //
        return $this->querySingleResult($sqlQuery);
    }

    public function INFILE($file) {
        $sql = "LOAD DATA INFILE 'http://localhost/erp/avalableParcodes/erp2016_fatma.txt' INTO TABLE availableparcode";
        echo $sql;
        $sqlQuery = new SqlQuery($sql); //
        return $this->querySingleResult($sqlQuery);
    }

    ////////////////////////////////////////
    public function createGenerateParcodesSP() {

        $_SESSION['stop_checking_result'] = 1;

        $sql = 'DROP PROCEDURE IF EXISTS generateParcodes;';
        $sqlQuery = new SqlQuery($sql);
        $this->executeUpdate($sqlQuery);

        //create procedure
        $sql = '
    create procedure generateParcodes (in num int)
    begin
        declare numLen int default CHAR_LENGTH(num);
        declare i int default 0;

        ALTER TABLE availableparcode ENGINE = MyISAM;

        while i <= num do
        insert into availableparcode (value) values (lpad(i, numLen, 0));
        set i = i + 1;
        end while;

        ALTER TABLE availableparcode ENGINE = InnoDB;
    end

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

    public function callGenerateParcodesSP($noOfDigits) {
        if ($noOfDigits > 0) {


            $biggestParcode = '';
            for ($i = 1; $i <= (int) $noOfDigits; $i++) {
                $biggestParcode .= '9';
            }
            $biggestParcode = (int) $biggestParcode;


            $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 generateParcodes ($biggestParcode)";

            $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($callSql)) {
                echo "CALL failed: (" . $mysqli->errno . ") " . $mysqli->error;
            }

//            if (!($res = $mysqli->query("SELECT @clientdebt as clientdebt,@clientname as clientname"))) {
//                echo "Fetch failed: (" . $mysqli->errno . ") " . $mysqli->error;
//            }
//            $row = $res->fetch_assoc();
//            $rowAsObj = new stdClass();
//            foreach ($row as $key => $value) {
//                $rowAsObj->$key = $value;
//            }
//            return $rowAsObj;
        }
    }

    public function deleteUsedParcodesInproductTable() {
        $sql = 'DELETE availableparcode FROM availableparcode
                join product on product.parcode = availableparcode.value';
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

    public function getAvailableParcodeValue() {
        //get rows no
        $DBName = ConnectionProperty::getDatabase();
        $sql = 'SELECT TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES
                WHERE TABLE_SCHEMA = "' . $DBName . '" AND TABLE_NAME = "availableparcode"';
        $sqlQuery = new SqlQuery($sql); //
        $rows = (int) $this->querySingleResult($sqlQuery);
        //

        $randonRow = rand(1, $rows);
        $sql = "select value from availableparcode limit $randonRow,1";
        //$sql = 'SELECT value from availableparcode ORDER BY RAND() LIMIT 1';
        $sqlQuery = new SqlQuery($sql); //
        return $this->querySingleResult($sqlQuery);
    }

    public function getFirstRow() {
        $sql = 'SELECT * from availableparcode LIMIT 1';
        $sqlQuery = new SqlQuery($sql); //
        return $this->getRow($sqlQuery);
    }

    public function truncate() {
        $sql = 'TRUNCATE availableparcode';
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

}

?>