<?php

//error_reporting(E_ERROR);
//the class which has connection data
require_once('../models/sql/ConnectionProperty.class.php');
$notExcuted = "";
$oneCommand = "";

$noOfsuccess = 0;
$noOfDuplicateColumnName = 0;
$noOfExcutedWithError = 0;
$noOfUnknownColumn = 0;
#################################################
//check and use the condition that suite this action
$dbhost = ConnectionProperty::getHost();
$dbuser = ConnectionProperty::getUser();
$dbpassword = ConnectionProperty::getPassword();
$dbname = ConnectionProperty::getDatabase();


$handle = fopen("../__db/tableEdit.sql", "r");
echo "<span style='color:yellowgreen'> read file done start executing... </span>";
if ($handle) {
    while (($line = fgets($handle)) !== false) {
        // process the line read.
        $firstTowChars = substr($line, 0, 2);
        if ($firstTowChars == "/*" || $firstTowChars == "##" || $firstTowChars == "//") {

        } else {
            $line = trim($line);
            if (!empty($line)) {
                $lastChars = substr($line, -1);
                $oneCommand.=$line;
                if ($lastChars == ";") {
                    runCommand($dbhost, $dbuser, $dbpassword, $dbname, $oneCommand);
                    $oneCommand = "";
                }
            }
        }
    }

    fclose($handle);
    echo "<span style='color:green'> DONE. </span><br/>";
    print_r("<pre>");
    echo "<div style='border:1px solid green;margin-right:60%'><span style='color:green'> no of queries excuted successfully : " . $noOfsuccess . " </span><br/>";
    echo "<span style='color:#f57c00'> no of queries excuted with Duplicate Column Name error : " . $noOfDuplicateColumnName . " </span><br/>";
    echo "<span style='color:#f57c00'> no of queries excuted with Unknown Column error : " . $noOfUnknownColumn . " </span><br/>";
    echo "<span style='color:red'> no of queries gave errors : " . $noOfExcutedWithError . " </span><br/></div>";
    print_r("</pre>");
} else {
    // error opening the file.
}

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

function runCommand($dbhost, $dbuser, $dbpassword, $dbname, $oneCommand) {
    global $noOfsuccess;
    global $noOfDuplicateColumnName;
    global $noOfUnknownColumn;
    global $noOfExcutedWithError;
    // Create connection
    $conn = new mysqli($dbhost, $dbuser, $dbpassword, $dbname);

    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }
    $sql = $oneCommand;
    if ($conn->query($sql) === TRUE) {
        //echo "success";
        $noOfsuccess++;
    } else {
        //echo "Error: " . $conn->error . "<br/>";
        $errorMessage = $conn->error;
        if (stripos($errorMessage, "Duplicate column name") !== false) {
            //now  it is dublicate column doenot matter
            $noOfDuplicateColumnName++;
        } elseif (stripos($errorMessage, "Unknown column") !== false) {
            $noOfUnknownColumn++;
        } else {
            $noOfExcutedWithError++;
            //show error
            print_r("<pre>");
            print_r($sql . "<br/><span style='color:red'>Error: " . $errorMessage . "</sapn><hr>");
            print_r("</pre>");
        }
    }
}

?>