DBquery.php

<?php
/**
 * Created by PhpStorm.
 * User: jimmy
 * Date: 29/01/19
 * Time: 16:38
 */

require_once(__DIR__.'/../inc/conf/Conf.php');
require_once(__DIR__.'/../model/Section.php');
require_once(__DIR__.'/../model/Manager.php');
require_once(__DIR__.'/../model/Distribution.php');
require_once(__DIR__.'/../model/Package.php');
require_once(__DIR__.'/../model/Container.php');
require_once(__DIR__.'/../model/Grade.php');
require_once(__DIR__.'/../model/Label.php');
require_once(__DIR__.'/../model/User.php');
require_once(__DIR__.'/../model/ContainerLabel.php');
require_once(__DIR__.'/../model/Config.php');

class DBquery
{

    var $dbh = NULL;

    public function __construct() {
        $this->openConnection();
    }

    /**
     * For open a database connection
     **/
    public function openConnection() {
        $this->dbh = mysqli_connect(Conf::$DB_HOSTNAME, conf::$DB_USERNAME, Conf::$DB_PP, Conf::$DB_NAME);

        if(!$this->dbh){
            die('Could not connect: ' . mysqli_error($this->dbh));
        }

        if (mysqli_connect_errno()) {
            printf("Echec connection : %s\n", mysqli_connect_error());
            exit();
        }
    }

    /**
     * Escapes special characters in a string for use in an SQL statement
     * @param string $value
     * @return string
     */
    public function escape($value) {
        return mysqli_real_escape_string($this->dbh, $value);
    }

    /**
     * Get all Distributions
     * 
     * @return array
     */
    public function getDistributions() {
        $sql = "SELECT * FROM `Distrib` ORDER BY `Distrib`.`name` ASC;";
        $result = mysqli_query($this->dbh, $sql);
        $results = array();
        while($row = mysqli_fetch_assoc($result)){
            $d = new Distribution($row['ID'], $row['name'], $row['version'], $row['manager'], $row['active'], $row['vname']);
            array_push($results, $d);
        }
        return $results;
    }

    /**
     * Get distribution vname with name and version
     * 
     * @param string $name The name of the distribution to find
     * @param string $version The version of the distribution to find
     * 
     * @return string
     */
    public function getVName($name, $version) {
        $sql = "SELECT vname  FROM `Distrib` WHERE active = 1 AND `name`='$name' AND `version`='$version';";
        $result = mysqli_query($this->dbh, $sql);
        while($row = mysqli_fetch_assoc($result)){
            return $row['vname'];
        }
        return "";
    }

    /**
     * Get all active distributions
     * 
     * @return array
     */
    public function getActiveDistributions() {
        $sql = "SELECT * FROM `Distrib` WHERE active = 1 ORDER BY `Distrib`.`name` ASC;";
        $result = mysqli_query($this->dbh, $sql);
        $results = array();
        while($row = mysqli_fetch_assoc($result)){
            $d = new Distribution($row['ID'], $row['name'], $row['version'], $row['manager'], $row['active'], $row['vname']);
            array_push($results, $d);
        }
        return $results;
    }

    /**
     * Get all full name from all active distributions
     * 
     * @return array
     */
    public function getActiveDistributionsFullName() {
        $sql = "SELECT name,version,vname  FROM `Distrib` WHERE active = 1 ORDER BY `Distrib`.`name` ASC;";
        $result = mysqli_query($this->dbh, $sql);
        $results = array();
        while($row = mysqli_fetch_assoc($result)){
            array_push($results, $row['name'].":".$row['version']);
        }
        return $results;
    }

    /**
     * Get all managers
     * 
     * @return array
     */
    public function getManagers() {
        $sql = "SELECT * FROM `Manager`;";
        $result = mysqli_query($this->dbh, $sql);
        $results = array();
        while($row = mysqli_fetch_assoc($result)){
            $m = new Manager($row['ID'], $row['name'], $row['value']);
            array_push($results, $m);
        }
        return $results;
    }

    /**
     * Get all users
     * 
     * @return array
     */
    public function getUsers() {
        $sql = "SELECT * FROM `User`;";
        $result = mysqli_query($this->dbh, $sql);
        $results = array();
        while($row = mysqli_fetch_assoc($result)){
            $u = new User($row['ID'], $row['login'], $row['gradeId']);
            array_push($results, $u);
        }
        return $results;
    }

    /**
     * Get grade with user login
     * 
     * @param string $login The login of ther user
     * 
     * @return array
     */
    public function getGradeWithLogin($login) {
        $sql = "SELECT * FROM `User` WHERE `login` = '$login';";
        $result = mysqli_query($this->dbh, $sql);

        while($row = mysqli_fetch_assoc($result)){
            $u = new User($row['ID'], $row['login'], $row['gradeId']);
            return $this->getGradeWithId($u->gradeId);
        }
  
        return $this->getLowerGrades();
    }

    /**
     * Get all labels for specific grade
     * 
     * @param $string $grade The grade to find the labels
     * 
     * @return array
     */
    public function getLabelsWithGrade($grade) {

        $labels = $this->getLabels();

        $results = array();

        foreach ($labels as $label){ 

            $grd = $this->getGradeWithId($label->gradeId);

            if($grd->level >= $grade->level) {
                array_push($results, $label);
            }

        }

        return $results;
    }

    /**
     * Get all grades
     * 
     * @return array
     */
    public function getGrades() {
        $sql = "SELECT * FROM `Grade` ORDER BY `level` ASC;";
        $result = mysqli_query($this->dbh, $sql);
        $results = array();
        while($row = mysqli_fetch_assoc($result)){
            $m = new Grade($row['ID'], $row['name'], $row['level']);
            array_push($results, $m);
        }
        return $results;
    }

    /**
     * Get all configs
     * 
     * @return array
     */
    public function getConfigs() {
        $sql = "SELECT * FROM `Config` WHERE 1;";
        $result = mysqli_query($this->dbh, $sql);
        $results = array();
        while($row = mysqli_fetch_assoc($result)){
            $cfg = new Config($row['ID'], $row['type'], $row['description'], $row['value'], $row['active']);
            array_push($results, $cfg);
        }
        return $results;  
    }

    /**
     * Gel all active config with type
     * 
     * @param string $type The type of the config
     * 
     * @return array
     */
    public function getActiveConfigsWithType($type) {
        $sql = "SELECT * FROM `Config` WHERE `type`='$type' AND `active` = 1;";
        $result = mysqli_query($this->dbh, $sql);
        $results = array();
        while($row = mysqli_fetch_assoc($result)){
            $cfg = new Config($row['ID'], $row['type'], $row['description'], $row['value'], $row['active']);
            array_push($results, $cfg);
        }
        return $results;  
    }

    /**
     * Get grade with this ID
     * 
     * @param int $ID The ID of the grade
     * 
     * @return Grade
     */
    public function getGradeWithId($ID) {
        $sql = "SELECT * FROM `Grade` WHERE `ID` = '$ID';";
        $result = mysqli_query($this->dbh, $sql);
        $results = array();
        while($row = mysqli_fetch_assoc($result)){
            $m = new Grade($row['ID'], $row['name'], $row['level']);
            return $m;
        }
        return $results;
    }

    /**
     * Get lower grade
     * 
     * @return Grade
     */
    public function getLowerGrades() {
        $sql = "SELECT * FROM `Grade` ORDER BY `level` DESC LIMIT 1;";
        $result = mysqli_query($this->dbh, $sql);
        $results = array();
        while($row = mysqli_fetch_assoc($result)){
            $m = new Grade($row['ID'], $row['name'], $row['level']);
            return $m;
        }
        return $results;
    }

    /**
     * Get max grade
     * 
     * @return Grade
     */
    public function getMaxGrades() {
        $sql = "SELECT * FROM `Grade` ORDER BY `level` ASC LIMIT 1;";
        $result = mysqli_query($this->dbh, $sql);
        $results = array();
        while($row = mysqli_fetch_assoc($result)){
            $m = new Grade($row['ID'], $row['name'], $row['level']);
            return $m;
        }
        return $results;
    }
    
    /**
     * Get all public tags
     * 
     * @return array
     */
    public function getAllPublicTags() {
        $sql = "SELECT tags FROM `Container` WHERE tags <> '' AND visibility = 1;";
        $result = mysqli_query($this->dbh, $sql);
        $results = array();
        while($row = mysqli_fetch_assoc($result)){
            array_push($results, $row['tags']);
        }
        return $results;
    }

    /**
     * Get all containers
     * 
     * @param string $type
     * @param string $author
     * @param array $labels
     * @param array $tags
     * @param string $description 
     * 
     * @return array
     * 
     */
    public function getContainers($type, $author, $labels, $tags, $descriptions) {
        $sql = "SELECT * FROM `Container`";

        if($type == "Singularity" || $type == "Docker") {
            $sql .= " WHERE `type` = '$type'";
        } else {
            $sql .= " WHERE 1";
        }

        if($author != NULL) {
            $sql .= " AND `author` = '$author'";
        }

        if(sizeof($labels) > 0) {
            $subsql = "(SELECT DISTINCT containerId FROM `ContainerLabel` WHERE labelId IN (";

            $index = 0;
            
            foreach ($labels as $label) {
                if($index != 0) {
                    $subsql .= ","; 
                } 

                $subsql .= $label;

                $index++;
            }
            $subsql .= "))";

            $sql .= " AND ID IN " . $subsql;
        }

        if(sizeof($tags) > 0) {
            foreach ($tags as $tag) {
                $sql .= " AND tags LIKE '%".$tag."%'";
            }
        }

        if(sizeof($descriptions) > 0) {
            foreach ($descriptions as $d) {
                $sql .= " AND description LIKE '%".$d."%'";
            }
        }

        $sql .= " ORDER BY `Container`.`date` DESC;";
        
        $result = mysqli_query($this->dbh, $sql);
        $results = array();
        while($row = mysqli_fetch_assoc($result)){
            $cnt = new Container($row['ID'], $row['name'], $row['value'], $row['type'], $row['visibility'], $row['description'], $row['author'], $row['date'], $row['tags']);
            array_push($results, $cnt);
        }
        return $results;
    }

    /**
     * Get container with this id
     * 
     * @param int $id The id of the container
     * 
     * @return Container
     */
    public function getContainerWithId($id) {
        $sql = "SELECT * FROM `Container` WHERE `ID` = '$id';";
        $result = mysqli_query($this->dbh, $sql);
        $results = array();
        while($row = mysqli_fetch_assoc($result)){
            $cnt = new Container($row['ID'], $row['name'], $row['value'], $row['type'], $row['visibility'], $row['description'], $row['author'], $row['date']);
            return $cnt;
        }
        return $results;
    }

    /**
     * Get all labels
     * 
     * @return array
     */
    public function getLabels() {
        $sql = "SELECT * FROM `Label`;";
        $result = mysqli_query($this->dbh, $sql);
        $results = array();
        while($row = mysqli_fetch_assoc($result)){
            $l = new Label($row['ID'], $row['name'], $row['color'], $row['gradeId']);
            array_push($results, $l);
        }
        return $results;
    }

    /**
     * Get label with this id
     * 
     * @param int $id The id of the label
     * 
     * @return Label
     * 
     */
    public function getLabelsWithId($id) {
        $sql = "SELECT * FROM `Label` WHERE `ID` = $id;";
        $result = mysqli_query($this->dbh, $sql);
        $results = array();
        while($row = mysqli_fetch_assoc($result)){
            $l = new Label($row['ID'], $row['name'], $row['color'], $row['gradeId']);
            return $l;
        }
        return $results;
    }

    /**
     * Get all labels for Container
     * 
     * @param int $id The id of the container
     * 
     * @return array
     */
    public function getLabelWithContainerId($id) {
        $sql = "SELECT * FROM `ContainerLabel` WHERE `containerId` = $id";
        $result = mysqli_query($this->dbh, $sql);
        $results = array();
        while($row = mysqli_fetch_assoc($result)){
            $l = $this->getLabelsWithId($row['labelId']);
            array_push($results, $l);
        }
        return $results;
    }

    /**
     * Get all active Section who is parent
     * 
     * @return array
     */
    public function getAllActiveSectionParent() {
        $sql = "SELECT * FROM `Section` WHERE `Section`.`parent` IS NULL AND active = 1 ORDER BY `Section`.`arrangement` ASC;";
        $result = mysqli_query($this->dbh, $sql);
        $results = array();
        while($row = mysqli_fetch_assoc($result)){
            $s = new Section($row['ID'], $row['name'], $row['visual'], $row['active'], $row['color'], $row['arrangement'], $row['parent']);
            array_push($results,$s);
        }
        return $results;
    }

    /**
     * Get all section parent
     * 
     * @return array
     */
    public function getAllSectionParent() {
        $sql = "SELECT * FROM `Section` WHERE `Section`.`parent` IS NULL ORDER BY `Section`.`arrangement` ASC;";
        $result = mysqli_query($this->dbh, $sql);
        $results = array();
        while($row = mysqli_fetch_assoc($result)){
            $s = new Section($row['ID'], $row['name'], $row['visual'], $row['active'], $row['color'], $row['arrangement'], $row['parent']);
            array_push($results,$s);
        }
        return $results;
    }

    /**
     * Get all active Section for specific parent
     * 
     * @param string $parent The name of the parent
     * 
     * @return array
     */
    public function getSectionWhithParent($parent) {
        $sql = "SELECT * FROM `Section` WHERE `Section`.`parent` = '$parent' AND active = 1 ORDER BY `Section`.`arrangement` ASC;";
        $result = mysqli_query($this->dbh, $sql);
        $results = array();
        while($row = mysqli_fetch_assoc($result)){
            array_push($results,array($row['name'], $row['visual'], $row['active'], $row['color']));
        }
        return $results;
    }

    /**
     * 
     * Get all name of the section who have a parent
     * 
     * @return array
     */
    public function getNameSectionWhithParent($parent) {
        $sql = "SELECT name FROM `Section` WHERE `Section`.`parent` = '$parent' AND active = 1 ORDER BY `Section`.`arrangement` ASC;";
        $result = mysqli_query($this->dbh, $sql);
        $results = array();
        while($row = mysqli_fetch_assoc($result)){
            array_push($results,$row['name']);
        }
        return $results;
    }

   /**
     * Get all Section for specific parent
     * 
     * @param string $parent The name of the parent
     * 
     * @return array
     */
    public function getAllSectionWhithParent($parent) {
        $sql = "SELECT * FROM `Section` WHERE `Section`.`parent` = '$parent' ORDER BY `Section`.`arrangement` ASC;";
        $result = mysqli_query($this->dbh, $sql);
        $results = array();
        while($row = mysqli_fetch_assoc($result)){
            $s = new Section($row['ID'], $row['name'], $row['visual'], $row['active'], $row['color'], $row['arrangement'], $row['parent']);
            array_push($results,$s);
        }
        return $results;
    }


    /**
     * Get all action section
     * 
     * @return array
     */
    public function getActiveSection() {
        $sql = "SELECT * FROM `Section` WHERE active = 1 ORDER BY `Section`.`arrangement` ASC;";
        $result = mysqli_query($this->dbh, $sql);
        $results = array();
        while($row = mysqli_fetch_assoc($result)){
            $s = new Section($row['ID'], $row['name'], $row['visual'], $row['active'], $row['color'], $row['arrangement'], $row['parent']);
            array_push($results,$s);
        }
        return $results;
    }

    /**
     * Get all Section
     * 
     * @return array
     */
    public function getSection() {
        $sql = "SELECT * FROM `Section` ORDER BY `Section`.`arrangement` ASC;";
        $result = mysqli_query($this->dbh, $sql);
        $results = array();
        while($row = mysqli_fetch_assoc($result)){
            $s = new Section($row['ID'], $row['name'], $row['visual'], $row['active'], $row['color'], $row['arrangement'], $row['parent']);
            array_push($results,$s);
        }
        return $results;
    }

    /**
     * Get number packages For Distribution
     * 
     * @deprecated
     * 
     * @return int
     */
    public function getNumberPackage($name) {

        $sql = "SELECT COUNT(*) as number FROM `$name` WHERE 1 ";

        $result = mysqli_query($this->dbh, $sql);
        while($row = mysqli_fetch_assoc($result)){
            return $row['number'];
        }
        return 0;
    }

    /**
     * Get all packages
     * 
     * @deprecated
     */
    public function getPackages($distrib, $filter, $patternName, $patternDescription, $limit) {
        if($patternName and $patternDescription) {
            $sql = "SELECT * FROM `$distrib` WHERE `name` LIKE '%$filter%' or `description` LIKE '%$filter%' ORDER BY CASE  WHEN `name` LIKE '$filter' THEN 1 WHEN `name` LIKE '$filter%' THEN 2 WHEN `name` LIKE '%$filter' THEN 4 ELSE 3 END LIMIT $limit;";
        }
        elseif ($patternName) {
            $sql = " SELECT * FROM `$distrib` WHERE `name` LIKE '%$filter%' ORDER BY CASE  WHEN `name` LIKE '$filter' THEN 1 WHEN `name` LIKE '$filter%' THEN 2 WHEN `name` LIKE '%$filter' THEN 4 ELSE 3 END LIMIT $limit;";
        }
        elseif ($patternDescription) {
            $sql = "SELECT * FROM `$distrib` WHERE `description` LIKE '%$filter%' LIMIT $limit;";
        } else {
            return array();
        }

        $result = mysqli_query($this->dbh, $sql);
        $results = array();
        while($row = mysqli_fetch_assoc($result)){
            $package = new Package($row['name'], $row['version'], $row['description'], $row['doc'], $row['other']);
            array_push($results,$package);
        }
        return $results;
    }

    /**
     * Check if table exist in database
     * 
     * @param string $name The name of the table
     * 
     */
    public function checkTableExist($name) {
        $sql = "SELECT count(*) as number FROM information_schema.TABLES WHERE (TABLE_SCHEMA = 'wicopa') AND (TABLE_NAME = '$name')";
        
        $result = mysqli_query($this->dbh, $sql);
        if($result) {

            while($row = mysqli_fetch_assoc($result)){
                return $row['number'];
            }

        } else {
            var_dump(mysqli_error($this->dbh));
            die();
        }
    }

    /**
     * Create ofject in database
     */
    public function create($object) {

        $result = mysqli_query($this->dbh, $object->getInsert());

        if($result) {
            
        } else {
            var_dump(mysqli_error($this->dbh));
            die();
        }
    }


    /**
     * Update ofject in database
     */
    public function update($object) {
        $result = mysqli_query($this->dbh, $object->getUpdate()); 
        if($result) {
            
        } else {
            var_dump(mysqli_error($this->dbh));
            die();
        }
    }


    /**
     * Delete ofject in database
     */
    public function delete($object) {
        $result = mysqli_query($this->dbh, $object->getDelete());
        if($result) {
            
        } else {
            var_dump(mysqli_error($this->dbh));
            die();
        }
    }

    /**
     * Check admin pass
     */
    public function verifyPass($username, $pass) {
        if($username == "admin" && Conf::dbEncodePass($pass)==Conf::$KK) {
            return 1;
        } else {
            # if you want guest admin
            //if($username == "guest" && $pass == "2019mbb") {
            //    return 2;
            //} else {
                return 0;
            //}
        }
    }

    /**
     * Get number docker container
     * 
     * @return int
     */
    public function getNumberDockerContainer() {
        $sql = 'SELECT count(*) as docker FROM `Container` WHERE type = "Docker" AND visibility = 1;';
        $result = mysqli_query($this->dbh, $sql);
        if($result) {

            while($row = mysqli_fetch_assoc($result)){
                return $row['docker'];
            }

        }

        return 0;
    }

    /**
     * Get number singularity container
     * 
     * @return int
     */
    public function getNumberSingularityContainer() {
        $sql = 'SELECT count(*) as singularity FROM `Container` WHERE type = "Singularity" AND visibility = 1';
        $result = mysqli_query($this->dbh, $sql);
        if($result) {

            while($row = mysqli_fetch_assoc($result)){
                return $row['singularity'];
            }

        }

        return 0;
    }




    //LOAD DATA LOCAL INFILE "/home/jimmy/jimmy/web/wicopa/packages/fedora:28/packages.csv" INTO TABLE fedora COLUMNS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"' LINES TERMINATED BY '\n';
}