<?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");
//Transaction
include_once("../models/sql/Transaction.class.php");

//here the db files that include in the file
include("../public/include_dao.php");

//Accountstree
require_once('../models/dao/AccountstreeDAO.class.php');
require_once('../models/dto/Accountstree.class.php');
require_once('../models/mysql/AccountstreeMySqlDAO.class.php');
require_once('../models/mysql/ext/AccountstreeMySqlExtDAO.class.php');
//Accountstreesetting
require_once('../models/dao/AccountstreesettingDAO.class.php');
require_once('../models/dto/Accountstreesetting.class.php');
require_once('../models/mysql/AccountstreesettingMySqlDAO.class.php');
require_once('../models/mysql/ext/AccountstreesettingMySqlExtDAO.class.php');

require_once('../models/dao/YoutubeLinkDAO.class.php');
require_once('../models/dto/YoutubeLink.class.php');
require_once('../models/mysql/YoutubeLinkMySqlDAO.class.php');
require_once('../models/mysql/ext/YoutubeLinkMySqlExtDAO.class.php');

//get the do the action
$do = $_GET['do'];
$langs = $_SESSION['erp_lang'];
include_once("../views/languages/$langs/success.php");
include_once("../views/languages/$langs/error.php");
/* ======================

  Controller Name :- accountstree

  OPERTATION in Controller

  1- display add form, add
  2- add in tbl
  3- display show and tempdelete
  4- edit
  5- update

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

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

//here goes the instances and general variables
//Transaction
$transactions = new Transaction();

//Accountstree
$accountsTree = new Accountstree();
$accountsTreeDAO = new AccountstreeMySqlDAO();
$accountsTreeEX = new AccountstreeMySqlExtDAO();
//Accountstreesetting
$accountsTreeSetting = new Accountstreesetting();
$accountsTreeSettingDAO = new AccountstreesettingMySqlDAO();
$accountsTreeSettingEX = new AccountstreesettingMySqlExtDAO();

$youtubeLink = new YoutubeLink();
$youtubeLinkDAO = new YoutubeLinkMySqlDAO();
$youtubeLinkEX = new YoutubeLinkMySqlExtDAO();

$today = date("Y-m-d");


//check and use the condition that suite this action
if (empty($do)) {
    //here the permssion check
    include_once("../public/authentication.php");

    $from = filter_input(INPUT_POST, 'from');
    $to = filter_input(INPUT_POST, 'to');
    $msg = '';
    if (!isset($from) || empty($from)) {
        $from = date('Y-m-01');
    }
    if (!isset($to) || empty($to)) {
        $to = date('Y-m-t');
    }
    $msg .= "<b>من تاريخ:</b>  " . $from . " ";
    $msg .= " <b>الى تاريخ:</b>  " . $to . " ";
    $smarty->assign("msg", $msg);

    $months = getMonthsBetweenTwoDates($from, $to, 'asc');
    $smarty->assign("months", $months);
    $monthsKV = array();
    foreach ($months as $month) {
        $monthsKV[$month]['debt'] = 0;
        $monthsKV[$month]['credit'] = 0;
    }
    ##
    // here the function that do the action
    $totalDebtor = 0;
    $totalCreditor = 0;
    $balanceArray = array();
    $sum = $totalsArr = $monthsKV; //array();
    $firstPeriodDebt = $firstPeriodCredit = 0;
    //    $maxNoOfLevels = 1;
//    getMaxNoOfLevels(0, 1);
//    $levelSetting = array();
    ##make no of levels fixed =6 "now we dont count tree levels"
    $maxNoOfLevels = 6;
    for ($i = 1; $i <= $maxNoOfLevels; $i++) {
        $level = $accountsTreeSettingDAO->queryByLevelno($i);
        if (count($level) < 1) {
            $accountsTreeSetting->levelno = $i;
            $accountsTreeSetting->nooffields = 1;
            $id = $accountsTreeSettingDAO->insert($accountsTreeSetting);
            $level = $accountsTreeSettingDAO->load($id);
        } else {
            $level = $level[0];
        }
        //array_push($levelSetting, $level);
        $levelSetting[$i] = $level;
    }

    display_children(0, 0, 0, 0);  //display the tree
    $smarty->assign("totalDebtor", $totalDebtor);
    $smarty->assign("totalCreditor", $totalCreditor);
    $smarty->assign("balanceArray", $balanceArray);
    $smarty->assign("totalsArr", $totalsArr);

    $youtubes = $youtubeLinkDAO->queryAll();
    $smarty->assign("youtubes", $youtubes);
    //here the smarty templates
    $smarty->display("monthlytrialbalanceview/add.html");
} elseif ($do == "sucess") {
    //here the smarty templates
    $smarty->display("succes.html");
} elseif ($do == "error") {
    //here the smarty templates
    $smarty->display("error.html");
}
//here the global templates
$smarty->display("footer.html");

/* ===============================
  function in this CONTROLLER
  ================================ */

function display_children($parent, $level, $itr, $itr2) {//$itr is itr without zeros  , $itr2 wit zeros
    global $accountsTreeDAO;
    global $accountsTreeEX;
    global $accountsTreeSettingDAO;
    global $outputString;
    global $sum;
    global $levelSetting;
    global $maxNoOfLevels;

    global $balanceArray;
    global $totalDebtor;
    global $totalCreditor;
    global $monthsKV, $totalsArr;
    global $firstPeriodDebt, $firstPeriodCredit;

    //note: url of tree ##http://jsfiddle.net/jhfrench/GpdgF/
    // retrieve all children of $parent
    $result = $accountsTreeEX->queryByParentExt($parent);
    if (count($result) > 0) {
        // display each child
        $i = 1;
        foreach ($result as $type) {
            //generte laying order  "use a method from the two coming"
            $preDigits = '';
            if (strlen($i) != $levelSetting[($level + 1)]->nooffields) {
                $noDigitsLeft = $levelSetting[($level + 1)]->nooffields - strlen($i);
                for ($j = 0; $j < $noDigitsLeft; $j++) {
                    $preDigits .= '0';
                }
            }

            if ($itr == 0) {
                $nodeName = $preDigits . $i; //. $type->id;
            } else {
                $nodeName = $itr . $preDigits . $i; //. $type->id;
            }
            //fill remaining with zeros to the right // optional
            $nodeNameWithZeros = $nodeName;
//            for ($ii = ($level + 2); $ii <= $maxNoOfLevels; $ii++) {
//                for ($j = 0; $j < $levelSetting[$ii]->nooffields; $j++) {
//                    $nodeNameWithZeros .= '0';
//                }
//            }
            //----------------------------------------------------------------
            //make sure node name is saved in db for searching purpose
            if ($nodeNameWithZeros !== $type->layingOrder) {
                R::exec('update accountstree set layingOrder =' . $nodeNameWithZeros . ' where id= ' . $type->id);
//                $type->layingOrder = $nodeNameWithZeros;
//                $accountsTreeDAO->update($type);
            }
            //
//            exportToExcel($type, $level);
            //
            $preString = '';
            for ($index = 0; $index < $level; $index++) {
                $preString .= '&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;';
            }
            $type->customName = $preString . '<strong>' . $nodeName . '</strong> ' . $type->name;

//            if ($type->theValue > 0) {
//                $totalDebtor += $type->theValue;
//            }
//            if ($type->theValue < 0) {
//                $totalCreditor += $type->theValue;
//            }
            //check for expenses for this category
            $childDataCount = R::getCell('select count(*) as c from accountstree where parent = ' . $type->id);
            if ($childDataCount > 0) {
                $sum = $monthsKV; //array();
                getTheTrueValue($type->id, $sum);
                $type->theValue = $sum;
                $type->islastLeaf = 0;

                ##
                $firstPeriodDebt = $firstPeriodCredit = 0;
                getFirstPeriodValOfParent($type->id);

                $type->firstPeriodDebtor = $firstPeriodDebt;
                $type->firstPeriodCreditor = $firstPeriodCredit;
            } else {
                $sum = $monthsKV; //array();
                getTheTrueValueLastLeaf($type->id, $sum);
                list($type->firstPeriodDebtor, $type->firstPeriodCreditor) = getFirstPeriodValLastLeaf($type->id);
                $type->theValue = $sum;
                $type->islastLeaf = 1;
                foreach ($totalsArr as $month => $arr) {
                    $totalsArr[$month]['debt'] += (float) $sum[$month]['debt'];
                    $totalsArr[$month]['credit'] += (float) $sum[$month]['credit'];
                }
            }
            //get debt,credit val of sum as they are hidden
            if (($_COOKIE['showClientsAtTree'] == 0 && ($type->id == 58 || $type->id == 60)) || ($_COOKIE['showSuppliersAtTree'] == 0 && ($type->id == 81 || $type->id == 87))) {
                ##
                $firstPeriodDebt = $firstPeriodCredit = 0;
                getFirstPeriodValOfParent($type->id);
                $type->firstPeriodDebtor = $firstPeriodDebt;
                $type->firstPeriodCreditor = $firstPeriodCredit;
            }
            $i++;
//            var_dump($type);
            array_push($balanceArray, $type);

            if (($_COOKIE['showClientsAtTree'] == 0 && ($type->id == 58 || $type->id == 60)) || ($_COOKIE['showSuppliersAtTree'] == 0 && ($type->id == 81 || $type->id == 87))) {
                //do noting.
            } else if ($childDataCount > 0) {
                display_children($type->id, $level + 1, $nodeName, $nodeNameWithZeros);
            }
        }
    }


    return $outputString;
}

function getTheTrueValue($parent, $sum) {
    global $accountsTreeEX;
    global $sum;
    global $from, $to;

    if (($_COOKIE['showClientsAtTree'] == 0 && ($parent == 58 || $parent == 60)) || ($_COOKIE['showSuppliersAtTree'] == 0 && ($parent == 81 || $parent == 87))) {

        list($debtor, $creditor) = getDailyEntrySumByParentAndDate($parent, $from, $to);
        foreach ($sum as $month => $arr) {
            $sum[$month]['debt'] += (float) $debtor[$month]['debtorVal'];
            $sum[$month]['credit'] += (float) $creditor[$month]['creditorVal'];
        }
//        $lastNodeSum = (float) $accountsTreeEX->getLastNodeSumById($parent);
//        $sum += $lastNodeSum;
//    } else if ($_COOKIE['calTreeNodes'] == 1) {
    } else {
        $result = R::getAll('select id,theValue from accountstree where parent = ' . $parent);
        foreach ($result as $type) {
            list($debtor, $creditor) = getDailyEntrySumByAccountIdAndDate($type['id'], $from, $to);
            foreach ($sum as $month => $arr) {
                $sum[$month]['debt'] += (float) $debtor[$month]['debtorVal'];
                $sum[$month]['credit'] += (float) $creditor[$month]['creditorVal'];
            }
            //$sum += $type['theValue'];
            getTheTrueValue($type['id'], $sum);
        }
    }
}

function getTheTrueValueLastLeaf($accountId, $sum) {
    global $sum;
    global $from, $to;

    list($debtor, $creditor) = getDailyEntrySumByAccountIdAndDate($accountId, $from, $to);
    foreach ($sum as $month => $arr) {
        $sum[$month]['debt'] += (float) $debtor[$month]['debtorVal'];
        $sum[$month]['credit'] += (float) $creditor[$month]['creditorVal'];
    }
}

function getFirstPeriodValLastLeaf($accountId) {
    global $from;

    list($debtor, $creditor) = getFirstPeriodValUsingDailyEntry($accountId, $from);
    return array($debtor, $creditor);
}

function getFirstPeriodValLastLeafByParent($accountId) {
    global $from;

    list($debtor, $creditor) = getFirstPeriodValUsingDailyEntryByParent($accountId, $from);
    return array($debtor, $creditor);
}

function getFirstPeriodValOfParent($accountId) {
    global $from, $firstPeriodDebt, $firstPeriodCredit;

    $parent = $accountId;
    if (($_COOKIE['showClientsAtTree'] == 0 && ($parent == 58 || $parent == 60)) || ($_COOKIE['showSuppliersAtTree'] == 0 && ($parent == 81 || $parent == 87))) {
        list($debtor, $creditor) = getFirstPeriodValLastLeafByParent($parent, $from);
        $firstPeriodDebt += $debtor;
        $firstPeriodCredit += $creditor;
    } else {
        $result = R::getAll('select id,theValue from accountstree where parent = ' . $parent);
        foreach ($result as $type) {
            list($debtor, $creditor) = getFirstPeriodValUsingDailyEntry($type['id'], $from);
            $firstPeriodDebt += $debtor;
            $firstPeriodCredit += $creditor;
            getFirstPeriodValOfParent($type['id']);
        }
    }
}

function getMonthsBetweenTwoDates($from, $to, $order = 'asc') {
    $ret = array();
    if ($from < $to) {
        $current = $from;
        $ret[] = date("Y-m", strtotime($current));
        while ($current < $to) {
            $next = date("Y-m-d", strtotime("+1 month", strtotime($current)));
            if ($next < $to) {
                $ret[] = date("Y-m", strtotime($next));
            }
            $current = $next;
        }
    }

    ##ret
    if ($order == 'asc') {
        return $ret;
    } else {
        return array_reverse($ret);
    }
}

function getDailyEntrySumByParentAndDate($parent, $from, $to) {
    $debtor = R::getAll('select DATE_FORMAT(thedate, "%Y-%m") AS thedate,sum(value) as debtorVal
                        from dailyentry
                        join dailyentrydebtor on dailyentrydebtor.dailyentryid = dailyentry.id
                        join accountstree on accountstree.id=dailyentrydebtor.accountstreeid
                        where `condition`=0 and parent=' . $parent . ' and thedate >= "' . $from . '" and thedate <= "' . $to . '"
                        group by MONTH(thedate),YEAR(thedate)'); // order by thedate desc

    $creditor = R::getAll('select DATE_FORMAT(thedate, "%Y-%m") AS thedate,sum(value) as creditorVal
                        from dailyentry
                        join dailyentrycreditor on dailyentrycreditor.dailyentryid = dailyentry.id
                        join accountstree on accountstree.id=dailyentrycreditor.accountstreeid
                        where `condition`=0 and parent=' . $parent . ' and thedate >= "' . $from . '" and thedate <= "' . $to . '"
                        group by MONTH(thedate),YEAR(thedate)'); // order by thedate desc

    $debtorArr = customArrayIndexOne($debtor, 'thedate');
    $creditorArr = customArrayIndexOne($creditor, 'thedate');
    return array($debtorArr, $creditorArr);
}

function getDailyEntrySumByAccountIdAndDate($accountId, $from, $to) {
    $debtor = R::getAll('select DATE_FORMAT(thedate, "%Y-%m") AS thedate,sum(value) as debtorVal
                        from dailyentry
                        join dailyentrydebtor on dailyentrydebtor.dailyentryid = dailyentry.id
                        where `condition`=0 and accountstreeid=' . $accountId . ' and thedate >= "' . $from . '" and thedate <= "' . $to . '"
                        group by MONTH(thedate),YEAR(thedate)'); // order by thedate desc

    $creditor = R::getAll('select DATE_FORMAT(thedate, "%Y-%m") AS thedate,sum(value) as creditorVal
                        from dailyentry
                        join dailyentrycreditor on dailyentrycreditor.dailyentryid = dailyentry.id
                        where `condition`=0 and accountstreeid=' . $accountId . ' and thedate >= "' . $from . '" and thedate <= "' . $to . '"
                        group by MONTH(thedate),YEAR(thedate)'); // order by thedate desc

    $debtorArr = customArrayIndexOne($debtor, 'thedate');
    $creditorArr = customArrayIndexOne($creditor, 'thedate');
    return array($debtorArr, $creditorArr);
}

function getFirstPeriodValUsingDailyEntry($accountId, $from) {
    $debtor = R::getCell('select sum(value) as debtorVal
                        from dailyentry
                        join dailyentrydebtor on dailyentrydebtor.dailyentryid = dailyentry.id
                        where `condition`=0 and accountstreeid=' . $accountId . ' and thedate < "' . $from . '" ');

    $creditor = R::getCell('select sum(value) as creditorVal
                        from dailyentry
                        join dailyentrycreditor on dailyentrycreditor.dailyentryid = dailyentry.id
                        where `condition`=0 and accountstreeid=' . $accountId . ' and thedate < "' . $from . '" ');

    return array($debtor, $creditor);
}

function getFirstPeriodValUsingDailyEntryByParent($accountId, $from) {
    $debtor = R::getCell('select sum(value) as debtorVal
                        from dailyentry
                        join dailyentrydebtor on dailyentrydebtor.dailyentryid = dailyentry.id
                        join accountstree on accountstree.id=dailyentrydebtor.accountstreeid
                        where `condition`=0 and parent=' . $accountId . ' and thedate < "' . $from . '" ');

    $creditor = R::getCell('select sum(value) as creditorVal
                        from dailyentry
                        join dailyentrycreditor on dailyentrycreditor.dailyentryid = dailyentry.id
                        join accountstree on accountstree.id=dailyentrycreditor.accountstreeid
                        where `condition`=0 and parent=' . $accountId . ' and thedate < "' . $from . '" ');

    return array($debtor, $creditor);
}

?>