<?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");

$smarty->assign('do', $do);
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();
}

////////////////////////////////////////////////////////////////////////////new archive////////////////////////////////
elseif ($do == "newarchive") {
    include_once("../public/authentication.php");
    $smarty->assign("archiveScripts", 1);
    $smarty->display("archiveview/archive.html");
} elseif ($do == "sucess") {
    $smarty->display("succes.html");
} elseif ($do == "error") {
    $smarty->display("error.html");
}

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

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

function emptying() {

    $emptyType = (int) $_POST['emptyType'];
    $arrKey = 'Tables_in_' . $_SESSION['dbname'];

    ##
    $sql = '';
    if ($emptyType == 0) {// empty all tables
        $sql = 'SHOW TABLES';
    } else if ($emptyType == 1) {//keep settings table only
        $settingsTables = array("programsettings", "billproperty", "billname", "billsettings", "properties", "relusergroupproperties", "user", "usergroup", "menuurl");
        $quotedArray = array_map(function ($value) {
            return '"' . $value . '"';
        }, $settingsTables);

        $sql = 'SHOW TABLES WHERE `' . $arrKey . '` NOT in (' . implode(', ', $quotedArray) . ')';
    }

    $allTables = R::getAll($sql);
    foreach ($allTables as $key => $table) {
        R::exec('TRUNCATE TABLE ' . $table[$arrKey] . ';');
    }
}

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


    //array of tables which we wish to backup
    //الجداول الثابتة
    $stableTables = array("billproperty", "billname", "billsettings", "capital", "properties", "relusergroupproperties", "user", "usergroup", "programsettings", "newdbbackup", "newdbname", "store", "save", "sparepartstore", "menuurl", "branch", "accountstree");
    //tables needed for every key label
    $stableTablesSorted = array('products' => array("product", "productcat", "productunit", "productcatunit", "unit", "productingredients", "productstore", "productsetting", "storedetail", "productserial", "soldserialproduct", "sizecolor", "sizecolorstoredetail", "productserial")
        , 'client' => array("client", "clientarea", "typeclient", "clientdebtchange")
        , 'supplier' => array("supplier", "typesupplier", "supplierdebtchange"));

    $choosedItemArr = $_POST['choosedItem'];
    //print_r($choosedItemArr);
    if (count($choosedItemArr) > 0) {
        $i = 1;
        foreach ($choosedItemArr as $option) {
            switch ($option) {

                ## product
                case 'product':
                    $stableTables = array_merge($stableTables, $stableTablesSorted['products']);
                    break;
                case 'unit':
                    array_push($stableTables, "unit");
                    break;
                case 'productUnit':
                    array_push($stableTables, "productunit", "unit");
                    break;
                case 'productCat':
                    array_push($stableTables, "productcat");
                    break;
                case 'storedetail':
                    array_push($stableTables, "storedetail", "sizecolorstoredetail");
                    break;
                case 'sizecolor':
                    array_push($stableTables, "sizecolor");
                    break;
                case 'soldserialproduct':
                    array_push($stableTables, "soldserialproduct");
                    break;
                case 'productserial':
                    array_push($stableTables, "productserial");
                    break;
                case 'catUnit':
                    array_push($stableTables, "productcat", "productcatunit", "unit");
                    break;

                ##asset
                case 'assetCat':
                    array_push($stableTables, "assetscat");
                    break;
                case 'asset':
                    array_push($stableTables, "assets", "assetscat");
                    break;

                ##check
                case 'bank':
                    array_push($stableTables, "bank");
                    break;
                case 'bankaccount':
                    array_push($stableTables, "bank", "bankaccount");
                    break;
                case 'check':
                    array_push($stableTables, "bank", "bankaccount", "checkcontroller", "datedchecked");
                    break;
                case 'checkMovement':
                    array_push($stableTables, "bank", "bankaccount", "checkcontroller", "datedchecked", "checkdeposit", "checkwithdrawal", "client", "supplier", "accountmovement");
                    break;
                case 'cashTransfer':
                    array_push($stableTables, "bank", "bankaccount", "cashTransfer");
                    break;

                ##shippingCompany
                case 'shippingCompany':
                    array_push($stableTables, "shippercompanies");
                    break;
                case 'representative':
                    array_push($stableTables, "representativecompany", "shippercompanies");
                    break;

                ##partner
                case 'partner':
                    array_push($stableTables, "partner");
                    break;

                ##kempiala
                case 'kempiala':
                    array_push($stableTables, "kempiala", "client", "supplier");
                    break;
                case 'client':
                    array_push($stableTables, "client", "clientarea", "typeclient");
                    break;
                case 'supplier':
                    array_push($stableTables, "supplier", "typesupplier");
                    break;


                ##sparePart
                case 'sparePart':
                    array_push($stableTables, "sparepart", "sparepartstoredetails");
                    break;

                ##expense
                case 'expenseType':
                    array_push($stableTables, "expensestype");
                    break;
                case 'expense':
                    array_push($stableTables, "expenses", "expensestype");
                    break;

                ##menu
                case 'menu':
                    array_push($stableTables, "menuurl");
                    break;

                ##clientdebtchange
                case 'clientdebtchange':
                    array_push($stableTables, "client", "clientdebtchange");
                    break;

                ##supplierdebtchange
                case 'supplierdebtchange':
                    array_push($stableTables, "supplier", "supplierdebtchange");
                    break;

                ##savedaily
                case 'savedaily':
                    array_push($stableTables, "savedaily");
                    break;

                ##employee
                case 'employee':
                    array_push($stableTables, "employee", "employeeattendancesystem", "employeeattendancesystemweek", "salaryreport", "employeepersonnel", "employeegroup", "employeesubgroup", "employeeholidays");
                    break;
                case 'salaryreport':
                    array_push($stableTables, "salaryreport");
                    break;
                case 'employeepersonnel':
                    array_push($stableTables, "employeepersonnel", "salaryreport");
                    break;
                case 'employeeattendancesystem':
                    array_push($stableTables, "employeeattendancesystem", "employeeattendancesystemweek");
                    break;
                case 'employeegroup':
                    array_push($stableTables, "employeegroup", "employeesubgroup");
                    break;
                case 'employeeholidays':
                    array_push($stableTables, "employeeholidays");
                    break;

                ##costcenter
                case 'costcenter':
                    array_push($stableTables, "costcenter", "costcenterdetail");
                    break;

                ##maintenance
                case 'maintenance':
                    array_push($stableTables, "m_check", "m_comreceipt", "m_comreceiptdetail", "m_comreceiptimage", "m_externalmaintenance", "m_finishingpayed", "m_handedreport", "m_initialmaintenance", "m_initialmaintenancedetail", "m_insurance", "m_maintenancereceipt", "m_maintenancereceiptfinish", "m_producthistory", "m_stage", "m_stageproperity");
                    break;


                ##productionexecution
                case 'productionexecution':
                    array_push($stableTables, "productionexecution", "productionexecutionproduct", "productionexecutionoutput", "productionexecutionwaste", "m_externalmaintenance", "m_finishingpayed", "m_handedreport", "m_initialmaintenance", "m_initialmaintenancedetail", "m_insurance", "m_maintenancereceipt", "m_maintenancereceiptfinish", "m_producthistory", "m_stage", "m_stageproperity");
                    break;
                case 'productionrate':
                    array_push($stableTables, "productionrate", "productionrateproduct");
                    break;
                case 'taskorder':
                    array_push($stableTables, "taskorder", "taskorderproducts");
                    break;
            }
        }
    }

    $stableTables = array_unique($stableTables);


    $hostname_config = ConnectionProperty::getHost();
    $username_config = ConnectionProperty::getUser();
    $password_config = ConnectionProperty::getPassword();
    $database_config = ConnectionProperty::getDatabase();




    //*** Start Transaction ***//
    mysql_query("START TRANSACTION");
    mysql_query("BEGIN");
    try {
        $newName = $_POST['dbName'];

        copyNewDBStructureFromOldOneAndFillReqTables($hostname_config, $username_config, $password_config, $database_config, $newName, $stableTables);

//        mysql_query("COMMIT");
        //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);
    } catch (Exception $exc) {
        mysql_query("ROLLBACK");
        echo $exc->getTraceAsString();
    }
}

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", "storemovement_main", "storemovement_details");
        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 "حدث خطأ";
    }
}

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

function copyNewDBStructureFromOldOneAndFillReqTables($hostname, $username, $password, $sourceDatabase, $destinationDatabase, $stableTables) {
    // Create a s mysqli connection for the source database
    $sourceConnection = new mysqli($hostname, $username, $password, $sourceDatabase);

    // Check the source connection
    if ($sourceConnection->connect_error) {
        die('Source Connection failed: ' . $sourceConnection->connect_error);
    }

    // Create the destination database if it doesn't exist
    $createDatabaseQuery = "CREATE DATABASE IF NOT EXISTS $destinationDatabase";
    $sourceConnection->query($createDatabaseQuery);
    // Create a new mysqli connection for the destination database
    $destinationConnection = new mysqli($hostname, $username, $password, $destinationDatabase);

    // Check the destination connection
    if ($destinationConnection->connect_error) {
        die('Destination Connection failed: ' . $destinationConnection->connect_error);
    }

    // Get a list of tables in the source database
    $tablesQuery = "SHOW TABLES";
    $tablesResult = $sourceConnection->query($tablesQuery);

    // Iterate over each table
    while ($tableRow = $tablesResult->fetch_row()) {
        $tableName = $tableRow[0];

        // Create the table in the destination database
        $createTableQuery = "CREATE TABLE $destinationDatabase.$tableName LIKE $sourceDatabase.$tableName";
        $destinationConnection->query($createTableQuery);

        // Copy the data from the source table to the destination table
        if (in_array($tableName, $stableTables)) {
            $copyDataQuery = "INSERT INTO $destinationDatabase.$tableName SELECT * FROM $sourceDatabase.$tableName";
            //var_dump($copyDataQuery);
            $destinationConnection->query($copyDataQuery);
        }
    }

    ##for elemnts removed , remove it from tree
    $tablesInTreeSql = "SELECT c.table_name, c.column_name
    FROM information_schema.columns c
    JOIN information_schema.tables t ON c.table_schema = t.table_schema
    AND c.table_name = t.table_name WHERE c.table_schema = '" . $destinationDatabase . "'
    AND c.column_name LIKE 'tree%' and c.column_name != treeType";
    //AND c.column_name LIKE 'tree_d%'";
    $tablesInTree = $destinationConnection->query($tablesInTreeSql);
    foreach ($tablesInTree as $value) {
        $tableName = $value['table_name'];
        $colName = $value['column_name'];
        //not moving to new db, remove it from tree
        if (!in_array($tableName, $stableTables)) {
            //var_dump("DELETE FROM accountstree WHERE id IN (SELECT $colName FROM $sourceDatabase.$tableName)");
            $destinationConnection->query("DELETE FROM accountstree WHERE id IN (SELECT $colName FROM $sourceDatabase.$tableName)");
        }
    }



    ## some special requirements ###############################################
    $archiveOptionForCheck = (int) $_POST['archiveOptionForCheck'];
    if ($archiveOptionForCheck == 1)
        $destinationConnection->query("update save set savecurrentvalue = 0");

    $archiveOptionForCheck = (int) $_POST['archiveOptionForCheck'];
    if ($archiveOptionForCheck == 1)
        $destinationConnection->query("DELETE FROM datedchecked WHERE done = 1");
    ## do Date Part ###############################################
    $from = $_POST['from'];
    if (isset($from) && !empty($from)) {
        //'SELECT * FROM ' . $table . '  where  ' . $table . '+date > "2014-08-11"'
        $destinationConnection->query("DELETE FROM savedaily where date(savedailydate) < '" . $from . "' ");
        $destinationConnection->query("DELETE FROM clientdebtchange where date(clientdebtchangedate) < '" . $from . "' ");
        $destinationConnection->query("DELETE FROM supplierdebtchange where date(supplierdebtchangedate) < '" . $from . "' ");
        $destinationConnection->query("DELETE FROM salaryreport where date(salaryreportdate) < '" . $from . "' ");
        $destinationConnection->query("DELETE FROM employeepersonnel where date(employeepersonneldate) < '" . $from . "' ");
    }
    ## transfering only some cats and thir products ############################
    $catOptionForCheck = (int) $_POST['catOptionForCheck'];
    if ($catOptionForCheck == 1) {
        ##first prepare ids ,make sure they are ok
        $caiIdsToBeTransfered = $_POST['caiIdsToBeTransfered'];
        // Remove any characters other than positive integers and commas
        $caiIdsToBeTransfered = preg_replace('/[^0-9,]/', '', $caiIdsToBeTransfered);
        // Split the string into an array of numbers
        $caiIdsToBeTransfered = explode(',', $caiIdsToBeTransfered);
        // Remove any empty values from the array
        $caiIdsToBeTransfered = array_filter($caiIdsToBeTransfered);
        // Join the array back into a string, separated by commas
        $caiIdsToBeTransfered = implode(',', $caiIdsToBeTransfered);

        ##now del
        if (!empty($caiIdsToBeTransfered)) {
            $destinationConnection->query("DELETE FROM productcat WHERE productCatId NOT IN ($caiIdsToBeTransfered) AND `productCatParent` NOT IN ($caiIdsToBeTransfered);");
            $destinationConnection->query("DELETE FROM product WHERE productCatId not in (SELECT productCatId from productcat);");
            $destinationConnection->query("DELETE FROM productunit WHERE `productid` not in (SELECT productId from product);");
            $destinationConnection->query("DELETE FROM productcatunit WHERE `productcatid` not in (SELECT productcatid from productcat);");
            $destinationConnection->query("DELETE FROM productingredients WHERE `productId` not in (SELECT productId from product);");
            $destinationConnection->query("DELETE FROM productstore WHERE `productid` not in (SELECT productId from product);");
            $destinationConnection->query("DELETE FROM storedetail WHERE `productid` not in (SELECT productId from product);");
            $destinationConnection->query("DELETE FROM productserial WHERE `productid` not in (SELECT productId from product);");
            $destinationConnection->query("DELETE FROM soldserialproduct WHERE `productserailid` not in (SELECT productserailid from productserial);");
            $destinationConnection->query("DELETE FROM sizecolorstoredetail WHERE `productid` not in (SELECT productId from product);");
            $destinationConnection->query("DELETE FROM productserial WHERE `productid` not in (SELECT productId from product);");
        }
    }
    // Close the connections
    $sourceConnection->close();
    $destinationConnection->close();
}

?>