<?php
class Query extends Conexion
{
    private $pdo, $con, $sql, $datos;
    public function __construct()
    {
        $this->pdo = new Conexion();
        $this->con = $this->pdo->conect();
    }
    public function select(string $sql)
    {
        $this->sql = $sql;
        $resul = $this->con->prepare($this->sql);
        $resul->execute();
        $data = $resul->fetch(PDO::FETCH_ASSOC);
        return $data;
    }

    public function selectBinds(string $sql,$asoc=PDO::FETCH_ASSOC,$binds=array())
    {
        $this->sql = $sql;
        $resul = $this->con->prepare($this->sql);

        if (count($binds)>0) {
            foreach ($binds as $key => $value) {
                $resul->bindValue($key,'%'.$value.'%', PDO::PARAM_STR);
            }
        }          
        
        $resul->execute();
        $data = $resul->fetch($asoc);
        return $data;
    }
    
    public function selectAll(string $sql)
    {
        $this->sql = $sql;
        $resul = $this->con->prepare($this->sql);
        $resul->execute();
        $data = $resul->fetchAll(PDO::FETCH_ASSOC);
        return $data;
        /*try {
            $this->sql = $sql;
            $resul = $this->con->prepare($this->sql);
            $resul->execute();
            $data = $resul->fetchAll(PDO::FETCH_ASSOC);
            return $data;
        } catch (PDOException $e) {
            echo "Error SQL: " . $e->getMessage();
            echo "<br>Query: " . $sql;
            return [];
        } */       
    }

    public function selectAllBind(string $sql,$asoc=PDO::FETCH_ASSOC,$binds=array())
    {
        $this->sql = $sql;
        $resul = $this->con->prepare($this->sql);

        if (count($binds)>0) {
            foreach ($binds as $key => $value) {
                $resul->bindValue($key,'%'.$value.'%', PDO::PARAM_STR);
            }
        }        

        $resul->execute();
        $data = $resul->fetchAll($asoc);
        return $data;
    }

    public function save(string $sql, array $datos)
    {
        $this->sql = $sql;
        $this->datos = $datos;
        $insert = $this->con->prepare($this->sql);
        $data = $insert->execute($this->datos);
        if ($data) {
            $res = 1;
        } else {
            $res = 0;
        }
        return $res;
    }
    public function insertar(string $sql, array $datos)
    {
        $this->sql = $sql;
        $this->datos = $datos;
        $insert = $this->con->prepare($this->sql);
        $data = $insert->execute($this->datos);
        if ($data) {
            $res = $this->con->lastInsertId();
        } else {
            $res = 0;
        }
        return $res;
    }

    // Function DataTables
    public function getLimitDatatable()
    {
        $sLimit = "";
		if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' ) {
			$sLimit = "LIMIT ".intval( $_GET['iDisplayStart'] ).", ".intval( $_GET['iDisplayLength'] );
		}
        return $sLimit;
    }
    public function getOrderDatatable($columns)
    {
		$sOrder = "";

		if ( isset( $_GET['iSortCol_0'] ) ) {
			$sOrder = "ORDER BY  ";
			for ( $i=0 ; $i<intval( $_GET['iSortingCols'] ) ; $i++ ) {
				if ( $_GET[ 'bSortable_'.intval($_GET['iSortCol_'.$i]) ] == "true" ) {
					$sortDir = (strcasecmp($_GET['sSortDir_'.$i], 'ASC') == 0) ? 'ASC' : 'DESC';
					$sOrder .= "`".$columns[ intval( $_GET['iSortCol_'.$i] ) ]."` ". $sortDir .", ";
				}
			}

			$sOrder = substr_replace( $sOrder, "", -2 );
			if ( $sOrder == "ORDER BY" ) {
				$sOrder = "";
			}
		}
        return $sOrder;
    }

    public function getTotalRecordsDatatable()
    {
        return current($this->select('SELECT FOUND_ROWS()'));
    }

    public function getSearchDatatable($columns)
    {
        $sWhere = "";


		if ( isset($_GET['sSearch']) && $_GET['sSearch'] != "" ) {

			$sWhere = "AND (";

			for ( $i=0 ; $i<count($columns) ; $i++ ) {
				if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" ) {
					$sWhere .= "`".$columns[$i]."` LIKE :search OR ";
				}
			}
			$sWhere = substr_replace( $sWhere, "", -3 );
			$sWhere .= ')';

		}

		for ( $i=0 ; $i<count($columns) ; $i++ ) {

			if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' ) 
            {
				$sWhere .= " AND `".$columns[$i]."` LIKE :search".$i." ";
			}

		}        

        return $sWhere;
    }

    public function getBindsDatatable($columns)
    {
        $binds = array();

		if ( isset($_GET['sSearch']) && $_GET['sSearch'] != "" ) {
            $binds[':search'] = '%'.$_GET['sSearch'].'%';
		}

		for ( $i=0 ; $i<count($columns) ; $i++ ) {
			if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' ) {
                $binds[':search'.$i] = '%'.$_GET['sSearch_'.$i].'%';
			}
		}
        return $binds;
    }    

}
