<?PHP
// Par : Ahmed ZERZIH
//Reoleweb
//
//Cette classe gère les opération sur une base de donnée sql
class MicrosoftDB
// ------------------------------------------------------------------------- //
{
    public  $link_id = null;
    public $result;
    public $rows;
    public $data;
    public String $host;
    public String $db;
    public String $user;
    public String $pass;
    public String $verbose;
    public String $usedConnection;
    public $conn = null;
    public $echo;
    function __construct($host, $db, $user, $pass, $verbose, $echo = true)
    {
        $this->host = $host;
        $this->db = $db;
        $this->user = $user;
        $this->pass = $pass;
        $this->verbose = $verbose;
        $this->echo = $echo;
        $this->connect();
        sqlsrv_configure("WarningsReturnAsErrors", 1);
    }


    function connect()
    {
        $this->usedConnection = "odbc";

        if ($this->usedConnection == "odbc") {
            $driver = "Driver={ODBC Driver 18 for SQL Server};Server=$this->host;Database=$this->db;TrustServerCertificate=Yes;Encrypt=Optional;";
            $user = $this->user;
            $password = $this->pass;

            try {
                $this->conn = odbc_connect($driver, $user, $password);

                if (!$this->conn) {
                    exit("❌ Connection Failed: " . odbc_errormsg());
                } else {
                    //echo "driver = " . $driver . "<br>";
                    echo "✅ Connected using DSN 'Opera40'.";

                    $sql = "SELECT * 
                        FROM vsOperation 
                        LEFT JOIN vsRequete ON vsRequete.Cle_vsRequete = vsOperation.Cle_requete 
                        LEFT JOIN vsUsager ON vsUsager.Cle_vsUsager = vsRequete.Cle_usager 
                        WHERE dossier='0567171';";

                    try {
                        $result = odbc_exec($this->conn, $sql);

                        if (!$result) {
                            exit("Query Failed: " . odbc_errormsg($this->conn));
                        }

                        // Fetch and display results
                        echo "<table border='1' cellpadding='5' cellspacing='0' style='border-collapse: collapse;'>";

                        // Get column names
                        $colCount = odbc_num_fields($result);
                        echo "<thead><tr>";
                        for ($i = 1; $i <= $colCount; $i++) {
                            echo "<th>" . odbc_field_name($result, $i) . "</th>";
                        }
                        echo "</tr></thead>";

                        // Display rows
                        echo "<tbody>";
                        while ($row = odbc_fetch_array($result)) {
                            echo "<tr>";
                            foreach ($row as $value) {
                                echo "<td>" . htmlspecialchars($value) . "</td>";
                            }
                            echo "</tr>";
                        }
                        echo "</tbody>";
                        echo "</table>";

                        // Close the connection
                        odbc_close($this->conn);
                    } catch (Exception $e) {
                        // Handle exception
                        echo "Error: " . $e->getMessage();
                    }
                }
            } catch (Exception $e) {
                // Handle exception
                echo "Error: " . $e->getMessage();
            }
        } else {

            $database = $this->host . "/" . $this->db;
            $connectionInfo = array("UID" => $this->user, "PWD" => $this->pass, "Database" => $this->db,  "Encrypt" => "Optional", "TrustServerCertificate" => "Yes");

            $this->conn = sqlsrv_connect($this->host, $connectionInfo);
            //echo "connect";
            if ($this->conn === false) {
                echo "<span style='color: red;'>Connection to '$this->db' failed</span><br/>";
                die(print_r(sqlsrv_errors(), true));
            } else {
                echo "Connected to '$database' with user '$this->user' (Microsoft SQL Server)<br/>";
            }

            exit;
            if ($this->link_id) {

                //echo "verbos";
                if ($this->verbose)
                    echo "Connected to '$database' with user '$this->user' (Microsoft SQL Server)<br/>";
            } else
                echo "<span style='color: red;'>Connection to '$database' failed</span><br/>";
        }

        /*$database = $this->host."/".$this->db;
            $connectionInfo = array("Database"=>$this->db, "UID"=>$this->user, "PWD"=>$this->pass, 'CharacterSet'=>'UTF-8');
            $this->link_id = sqlsrv_connect($this->host, $connectionInfo);
            //echo "connect";
            if ($this->link_id)
            {
				
			//echo "verbos";
               if ($this->verbose)
                    echo "Connected to '$database' with user '$this->user' (Microsoft SQL Server)<br/>";
            }
            else
                if($this->echo)
                    echo "<span style='color: red;'>Connection to '$database' failed</span><br/>";*/
    }


    function close()
    {
        if ($this->usedConnection == "odbc" && $this->conn && is_resource($this->conn)) {
            odbc_close($this->conn);
        } else if ($this->conn && is_resource($this->conn)) {
            sqlsrv_close($this->link_id);
            if ($this->verbose)
                echo "Closing '$this->db'<br/>";
        }
    }


    function exec($sql)
    {
        unset($this->data);
        $this->rows = 0;
        $errors = "";
        $this->result = sqlsrv_query($this->link_id, $sql);

        if ($this->result === false) {
            $errors = sqlsrv_errors();
            $this->rows = -1;
        } else {
            switch (strtoupper(substr($sql, 0, 6))) {
                case "SELECT":
                    while ($data_row = sqlsrv_fetch_array($this->result, SQLSRV_FETCH_ASSOC)) {
                        $this->data[] = $data_row;
                        $this->rows += 1;
                    }
                    //case "INSERT|UPDATE|DELETE" sqlsrv_rows_affected?
                default:
                    break;
            }
        }

        if ($this->verbose) {
            echo "($this->rows) $sql<br/>";
            if ($errors != null) {
                foreach ($errors as $error) {
                    echo "<span style='color: red;'>$error[message]</span><br/>";
                }
            }
        }

        return $this->rows;
    }

    function __destruct()
    {
        $this->close();
    }
}
