<?php

//the global file operation
include("../public/impOpreation.php");

//global varable
global $showoutside;

//to check if the page from .htacess
//$showoutside = $_GET['sn'];
// get the config file
include_once("../public/config.php");
include("../public/include_dao.php");
//Newdbname
require_once('../models/dao/NewdbnameDAO.class.php');
require_once('../models/dto/Newdbname.class.php');
require_once('../models/mysql/NewdbnameMySqlDAO.class.php');
require_once('../models/mysql/ext/NewdbnameMySqlExtDAO.class.php');

//Usergroup
require_once('../models/dao/UsergroupDAO.class.php');
require_once('../models/dto/Usergroup.class.php');
require_once('../models/mysql/UsergroupMySqlDAO.class.php');
require_once('../models/mysql/ext/UsergroupMySqlExtDAO.class.php');

//Newdbname
$newDbName = new Newdbname();
$newDbNameDAO = new NewdbnameMySqlDAO();
$newDbNameExt = new NewdbnameMySqlExtDAO();
//Usergroup
$myUserGroupRecord = new UsergroupMySqlDAO();

//get the do the action
$do = $_GET['do'];

/* ======================

  Controller Name :- archiveController

  OPERTATION in Controller

  1- empty db
  2- archive
  3- restore archive

  ======================== */

//here the global templates
$smarty->display("header.html");

if ($do == "emptydata") {
    //here the permssion check
    include_once("../public/authentication.php");

    $smarty->display("archiveview/empty.html");
} elseif ($do == "emptying") {
    include_once("../public/authentication.php");
    try {
        // here the function that do the action
        emptying();
        header("location:?do=sucess");
    } catch (Exception $e) {
        header("location:?do=error");
    }
} elseif ($do == "archive") {
    include_once("../public/authentication.php");
    $smarty->assign("archiveScripts", 1);
    $smarty->display("archiveview/archive.html");
} elseif ($do == "newDB") {
    include_once("../public/authentication.php");
    try {
        // here the function that do the action
        createNewDB();
        //header("location:?do=sucess");
        //destroy sessions to enforce user to login with the new db
        session_destroy();
        //show login page
        header("location:index.php");
    } catch (Exception $e) {
        header("location:?do=error");
    }
} elseif ($do == "changDB" || $do == "show") {
    include_once("../public/authentication.php");
    $databaseData = loadDatabases();
    $smarty->assign("databaseData", $databaseData);

    $smarty->display("archiveview/changeDB.html");
} elseif ($do == "restoreDB") {
    restoreDB();

    //get start page from usergroup by $_SESSION['usergroupid']
    $userGroupData = $myUserGroupRecord->load($_SESSION['usergroupid']);
    //print_r($userGroupData);
    $startPage = $userGroupData->startpage;

    //send the user to the start page
    header("location:" . $startPage . "");
} elseif ($do == "dbType") {
    //change database type(MYISAM, InnoDB)
    $smarty->display("archiveview/dbType.html");
} elseif ($do == "alterdb") {
    //change database type(MYISAM, InnoDB)
    alterDB();
} elseif ($do == "sucess") {
    $smarty->display("succes.html");
} elseif ($do == "error") {
    $smarty->display("error.html");
}

//here the global templates
$smarty->display("footer.html");

//////////////////////////////////////////////

function emptying() {

    run_sql_file("pro1.sql");
}

function run_sql_file($location) {
    if ($_SESSION['dbname'] != "" && isset($_SESSION['dbname'])) {
        $database = $_SESSION['dbname'];
    } else {
        $database = "storesdb";
    }

    $con = mysql_connect("localhost", "root", "123456");
    mysql_select_db($database, $con);
    $charset = mysql_client_encoding($con);
    mysql_query("SET NAMES 'utf8'");
    mysql_query('SET CHARACTER_SET utf8');

    //load file
    $commands = file_get_contents($location);

    //delete comments
    $lines = explode("\n", $commands);
    $commands = '';
    foreach ($lines as $line) {
        $line = trim($line);
        if ($line && !strpos($line, '--')) {
            $commands .= $line . "\n";
        }
    }

    //convert to array
    $commands = explode(";", $commands);

    $total = $success = 0;
    foreach ($commands as $command) {

        if (trim($command)) {
            //print_r($command."<br>");
            if (mysql_query($command)) {
                $success +=1;
                $total += 1;
            }
        }
    }

    //return number of successful queries and total number of queries found
    return array(
        "success" => $success,
        "total" => $total
    );
}

function createNewDB() {
    global $newDbName;
    global $newDbNameDAO;

    if ($_SESSION['dbname'] != "" && isset($_SESSION['dbname'])) {
        $database = $_SESSION['dbname'];
    } else {
        $database = "storesdb";
    }
    $hostname_config = "localhost";
    $database_config = $database;
    $username_config = "root";
    $password_config = "123456";


    //*** Start Transaction ***//
    mysql_query("START TRANSACTION");
    mysql_query("BEGIN");

    $newName = $_POST['dbName'];
    //$filename = 'archive/'.$newName.'-'.time().'.sql';
    //array of tables which we wish to backup
    //الجداول الثابتة
    $stableTables = array("billproperty", "billname", "billsettings", "capital", "properties", "relusergroupproperties", "user", "usergroup", "programsettings", "newdbbackup", "newdbname", "store", "save", "sparepartstore");

    //الجداول المتغيرة
    $variableTables = array("accountmovement", "clientdebtchange", "movementmanage", "savedaily", "supplierdebtchange", "transfermoney", "policy", "producttemp", "producttempunit", "sparepartstorereport", "storereport", "partnerwithdrawal", "productrecieve", "storemovement", "buyandruternbill", "buyandruternbilldetail", "buybill", "buybilldetail", "returnbuybill", "returnbuybilldetail", "returnsellbill", "returnsellbilldetail", "sellandruternbilldetail", "sellbill", "sellbillandrutern", "sellbilldetail", "storedetail", "product", "productcat", "productunit", "productcatunit", "unit", "bank", "bankaccount", "checkcontroller", "checkdeposit", "cashtransfer", "checkwithdrawal", "assets", "assetscat", "representativecompany", "shippercompanies", "partner", "kempiala", "sparepart", "sparepartstoredetails", "expenses", "expensestype", "productserial", "productsetting", "client", "supplier", "menuurl", "employee", "salaryreport", "employeepersonnel", "soldserialproduct", "notics");
    //

    $choosedItemArr = $_POST['choosedItem'];
    //print_r($choosedItemArr);
    if (count($choosedItemArr) > 0) {
        $i = 1;
        foreach ($choosedItemArr as $option) {
            if ($option == 'product') {
                array_push($stableTables, "product", "productcat", "productunit", "productcatunit", "unit", "productsetting", "storedetail", "productserial", "soldserialproduct");
                // remove the elements with values
                $variableTables = array_merge(array_diff($variableTables, array("product", "productcat", "productunit", "productcatunit", "unit", "productsetting", "storedetail", "productserial", "soldserialproduct")));
            } else if ($option == 'unit') {
                array_push($stableTables, "unit");
                // remove the elements with values
                $variableTables = array_merge(array_diff($variableTables, array("unit")));
            } else if ($option == 'productUnit') {//وحدات قياس المنتج
                array_push($stableTables, "productunit", "unit");
                // remove the elements with values
                $variableTables = array_merge(array_diff($variableTables, array("productunit", "unit")));
            } else if ($option == 'productCat') {
                array_push($stableTables, "productcat");
                // remove the elements with values
                $variableTables = array_merge(array_diff($variableTables, array("productcat")));
            }
//
            else if ($option == 'employee') {
                array_push($stableTables, "employee", "salaryreport", "employeepersonnel");
                // remove the elements with values
                $variableTables = array_merge(array_diff($variableTables, array("employee", "salaryreport", "employeepersonnel")));
            } else if ($option == 'salaryreport') {
                array_push($stableTables, "salaryreport");
                // remove the elements with values
                $variableTables = array_merge(array_diff($variableTables, array("salaryreport")));
            } else if ($option == 'employeepersonnel') {
                array_push($stableTables, "employeepersonnel");
                // remove the elements with values
                $variableTables = array_merge(array_diff($variableTables, array("employeepersonnel")));
            } else if ($option == 'soldserialproduct') {
                array_push($stableTables, "soldserialproduct");
                // remove the elements with values
                $variableTables = array_merge(array_diff($variableTables, array("soldserialproduct")));
            }
//
            else if ($option == 'catUnit') {
                array_push($stableTables, "productcat", "productcatunit", "unit");
                // remove the elements with values
                $variableTables = array_merge(array_diff($variableTables, array("productcat", "productcatunit", "unit")));
            } else if ($option == 'assetCat') {
                array_push($stableTables, "assetscat");
                // remove the elements with values
                $variableTables = array_merge(array_diff($variableTables, array("assetscat")));
            } else if ($option == 'asset') {
                array_push($stableTables, "assets", "assetscat");
                // remove the elements with values
                $variableTables = array_merge(array_diff($variableTables, array("assets", "assetscat")));
            } else if ($option == 'bank') {
                array_push($stableTables, "bank");
                // remove the elements with values
                $variableTables = array_merge(array_diff($variableTables, array("bank")));
            } else if ($option == 'bankaccount') {
                array_push($stableTables, "bank", "bankaccount");
                // remove the elements with values
                $variableTables = array_merge(array_diff($variableTables, array("bank", "bankaccount")));
            } else if ($option == 'check') {
                array_push($stableTables, "bank", "bankaccount", "checkcontroller");
                // remove the elements with values
                $variableTables = array_merge(array_diff($variableTables, array("bank", "bankaccount", "checkcontroller")));
            } else if ($option == 'checkMovement') {
                array_push($stableTables, "bank", "bankaccount", "checkcontroller", "checkdeposit", "checkwithdrawal", "client", "supplier");
                // remove the elements with values
                $variableTables = array_merge(array_diff($variableTables, array("bank", "bankaccount", "checkcontroller", "checkdeposit", "checkwithdrawal", "client", "supplier")));
            } else if ($option == 'cashTransfer') {
                array_push($stableTables, "bank", "bankaccount", "cashTransfer");
                // remove the elements with values
                $variableTables = array_merge(array_diff($variableTables, array("bank", "bankaccount", "cashTransfer")));
            } else if ($option == 'shippingCompany') {
                array_push($stableTables, "shippercompanies");
                // remove the elements with values
                $variableTables = array_merge(array_diff($variableTables, array("shippercompanies")));
            } else if ($option == 'representative') {
                array_push($stableTables, "representativecompany", "shippercompanies");
                // remove the elements with values
                $variableTables = array_merge(array_diff($variableTables, array("representativecompany", "shippercompanies")));
            } else if ($option == 'partner') {
                array_push($stableTables, "partner");
                // remove the elements with values
                $variableTables = array_merge(array_diff($variableTables, array("partner")));
            } else if ($option == 'kempiala') {
                array_push($stableTables, "kempiala", "client", "supplier");
                // remove the elements with values
                $variableTables = array_merge(array_diff($variableTables, array("kempiala", "client", "supplier")));
            } else if ($option == 'client') {
                array_push($stableTables, "client");
                // remove the elements with values
                $variableTables = array_merge(array_diff($variableTables, array("client")));
            } else if ($option == 'supplier') {
                array_push($stableTables, "supplier");
                // remove the elements with values
                $variableTables = array_merge(array_diff($variableTables, array("supplier")));
            } else if ($option == 'sparePart') {
                array_push($stableTables, "sparepart", "sparepartstoredetails");
                // remove the elements with values
                $variableTables = array_merge(array_diff($variableTables, array("sparepart", "sparepartstoredetails")));
            } else if ($option == 'expenseType') {
                array_push($stableTables, "expensestype");
                // remove the elements with values
                $variableTables = array_merge(array_diff($variableTables, array("expensestype")));
            } else if ($option == 'expense') {
                array_push($stableTables, "expenses", "expensestype");
                // remove the elements with values
                $variableTables = array_merge(array_diff($variableTables, array("expenses", "expensestype")));
            } else if ($option == 'menu') {
                array_push($stableTables, "menuurl");
                // remove the elements with values
                $variableTables = array_merge(array_diff($variableTables, array("menuurl")));
            } else if ($option == 'clientdebtchange') {
                array_push($stableTables, "client", "clientdebtchange");
                // remove the elements with values
                $variableTables = array_merge(array_diff($variableTables, array("client", "clientdebtchange")));
            } else if ($option == 'supplierdebtchange') {
                array_push($stableTables, "supplier", "supplierdebtchange");
                // remove the elements with values
                $variableTables = array_merge(array_diff($variableTables, array("supplier", "supplierdebtchange")));
            }
        }
    }

    $stableTables = array_unique($stableTables);
    //
    $returnQueries1 = export_stable_tables($hostname_config, $username_config, $password_config, $database_config, $stableTables);
    //insert data to programsettings
    //$select_prog_setting = mysql_query("SELECT * from programsettings");

    $returnQueries1 = str_replace($database_config, $newName, $returnQueries1);
    //echo $returnQueries1."<br>";

    $returnQueries2 = export_variable_tables($hostname_config, $username_config, $password_config, $database_config, $variableTables);
    $returnQueries2 = str_replace($database_config, $newName, $returnQueries2);

    $newName = $newName . '_' . date("dmY") . '_' . date("his"); //enter db name with date

    $newConnection = mysql_connect($hostname_config, $username_config, $password_config);

    $createNewDB = mysql_query("CREATE DATABASE " . $newName);

    if ($createNewDB) { //&& $select_prog_setting
        mysql_query("COMMIT");
        //connect to the new db
        mysql_select_db($newName, $newConnection);

        mysql_query("SET NAMES 'utf8'");
        mysql_query("SET CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci'");

        // Temporary variable, used to store current query
        $templine1 = '';
        $templine2 = '';
        foreach (preg_split("/((\r?\n)|(\r\n?))/", $returnQueries1) as $line) {
            //	echo $line."<br>";
            // Skip it if it's a comment
            if (substr($line, 0, 2) == '--' || $line == '')
                continue;

            // Add this line to the current segment
            $templine1 .= $line;
            // If it has a semicolon at the end, it's the end of the query
            if (substr(trim($line), -1, 1) == ';') {
                // Perform the query
                //echo htmlentities($templine1, ENT_COMPAT, 'UTF-8')."<br>";
                //echo utf8_decode($templine1). "<hr>";
                //mysql_query("SET NAMES 'utf8'");
                $executeReturnQueries1 = mysql_query($templine1) or print('Error performing query \'<strong>' . $templine . '\': ' . mysql_error() . '<br /><br />');
                if ($executeReturnQueries1) {
                    mysql_query("COMMIT");
                } else {
                    mysql_query("ROLLBACK");
                }
                // Reset temp variable to empty
                $templine1 = '';
            }
        }

        //echo $returnQueries1;
        foreach (preg_split("/((\r?\n)|(\r\n?))/", $returnQueries2) as $line) {
            // Skip it if it's a comment
            if (substr($line, 0, 2) == '--' || $line == '')
                continue;

            // Add this line to the current segment
            $templine2 .= $line;
            // If it has a semicolon at the end, it's the end of the query
            if (substr(trim($line), -1, 1) == ';') {
                // Perform the query
                //mysql_query("SET NAMES 'utf8'");
                $executeReturnQueries2 = mysql_query($templine2) or print('Error performing query \'<strong>' . $templine . '\': ' . mysql_error() . '<br /><br />');
                if ($executeReturnQueries2) {
                    mysql_query("COMMIT");
                } else {
                    mysql_query("ROLLBACK");
                }
                // Reset temp variable to empty
                $templine2 = '';
            }
        }

        //put new database name into file
        file_put_contents("../views/default/archiveview/dbNames.txt", "," . $newName, FILE_APPEND | LOCK_EX);

        $newDbName->dbname = $newName;
        $newDbNameDAO->insert($newDbName);
    } else {
        mysql_query("ROLLBACK");
        echo "حدث خطأ";
    }

    /*     * ************** excute stable tables' queries ******************* */
    //echo $executeReturnQueries1;
    //$executeReturnQueries1 = mysql_query($returnQueries1) or die(mysql_query());
    //echo $returnQueries2;
    //$executeReturnQueries2 = mysql_query($returnQueries2)or die(mysql_query());
}

function export_stable_tables($host, $user, $pass, $name, $tables = '*') {
    $link = mysql_connect($host, $user, $pass);
    mysql_select_db($name, $link);

    //to select arabic characters with no problems
    mysql_query("SET NAMES 'utf8'");
    mysql_query("SET CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci'");

    //get all of the tables
    if ($tables == '*') {
        $tables = array();
        $result = mysql_query('SHOW TABLES');
        while ($row = mysql_fetch_row($result)) {
            $tables[] = $row[0];
        }
    } else {
        $tables = is_array($tables) ? $tables : explode(',', $tables);
    }

    //cycle through
    foreach ($tables as $table) {
        /*         * *****  Export tables to .sql file ***** */
        $result = mysql_query('SELECT * FROM ' . $table);
        $num_fields = mysql_num_fields($result);

        //$return.= 'DROP TABLE IF EXISTS '.$table.'; \n\n';
        $row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE ' . $table));
        $return.= "\n\n" . $row2[1] . ";\n\n";

        for ($i = 0; $i < $num_fields; $i++) {
            while ($row = mysql_fetch_row($result)) {
                $return.= ' INSERT INTO ' . $table . ' VALUES(';
                for ($j = 0; $j < $num_fields; $j++) {
                    $row[$j] = addslashes($row[$j]);
                    $row[$j] = ereg_replace("\n", "\\n", $row[$j]);
                    if (isset($row[$j])) {
                        $return.= '"' . $row[$j] . '"';
                    } else {
                        $return.= '""';
                    }
                    if ($j < ($num_fields - 1)) {
                        $return.= ',';
                    }
                }
                $return.= ");\n";
            }
        }
        $return.="\n\n\n";
        //	if($table == "programsettings")
        //		echo $return;
    }
    //
    return $return;
}

function export_variable_tables($host, $user, $pass, $name, $tables = '*') {
    $link = mysql_connect($host, $user, $pass);
    mysql_select_db($name, $link);

    //to select arabic characters with no problems
    mysql_query("SET NAMES 'utf8'");
    mysql_query("SET CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci'");


    //get all of the tables
    if ($tables == '*') {
        $tables = array();
        $result = mysql_query('SHOW TABLES');
        while ($row = mysql_fetch_row($result)) {
            $tables[] = $row[0];
        }
    } else {
        $tables = is_array($tables) ? $tables : explode(',', $tables);
    }

    //cycle through
    foreach ($tables as $table) {
        /*         * *****  Export tables to .sql file ***** */
        $result = mysql_query('SELECT * FROM ' . $table);
        $num_fields = mysql_num_fields($result);

        // $return.= 'DROP TABLE IF EXISTS '.$table.'; \n\n';
        $row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE ' . $table));
        $return.= "\n\n" . $row2[1] . ";\n\n";
        $return.="\n\n\n";
    }
    return $return;
}

function loadDatabases() {
    global $newDbNameDAO;

    $databaseData = $newDbNameDAO->queryAll();

    return $databaseData;
}

function restoreDB() {
    global $newDbNameDAO;

    $newdbnameId = $_POST['newdbnameid'];

    //if($newdbnameId == -2)
//	{
//		unset($_SESSION['dbname']);
//	}
//	else
//	{
    if (!empty($newdbnameId)) {
        $dbData = $newDbNameDAO->load($newdbnameId);
        $_SESSION['dbname'] = $dbData->dbname;
    }
//	}
}

function getTableEngine() {

}

function alterDB() {
    global $smarty;

    $engineType = $_POST['type'];

    if ($_SESSION['dbname'] != "" && isset($_SESSION['dbname'])) {
        $database = $_SESSION['dbname'];
    } else {
        $database = "storesdb";
    }
    $hostname_config = "localhost";
    $database_config = $database;
    $username_config = "root";
    $password_config = "123456";

    //*** Start Transaction ***//
    mysql_query("START TRANSACTION");
    mysql_query("BEGIN");

    $dbConnection = mysql_connect($hostname_config, $username_config, $password_config);
    //connect to db
    mysql_select_db($database_config, $dbConnection);

    $sql = "SHOW tables";
    $rs = mysql_query($sql) or die('Error: ' . mysql_error());

    if ($rs) {
        //tables array
        $tablesArray = array("buyandruternbill", "buyandruternbilldetail", "buybill", "buybilldetail", "product", "productcat", "productsetting", "productunit", "returnbuybill", "returnbuybilldetail", "returnsellbill", "returnsellbilldetail", "savedaily", "sellbill", "sellbillandrutern", "sellbilldetail", "storedetail", "storemovement", "storereport", "supplierdebtchange", "movementmanage");
        foreach ($tablesArray as $tbl) {
            if (isset($engineType) && $engineType == 1) {
                $sql = mysql_query("ALTER TABLE " . $tbl . "  ENGINE=MYISAM") or die('Error: ' . mysql_error());
            } else if (isset($engineType) && $engineType == 2) {
                $sql = mysql_query("ALTER TABLE " . $tbl . "  ENGINE=InnoDB") or die('Error: ' . mysql_error());
            }
            mysql_query("COMMIT");
        }

        $url = "archiveController.php?do=dbType";
        $smarty->assign('urldirect', $url);
        $note = "تم تحويل النظام بنجاح";
        $smarty->assign('msgnote', $note);
        $smarty->display("notes2.html");
    } else {
        mysql_query("ROLLBACK");
        echo "حدث خطأ";
    }
}

?>