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

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

//Programsetting
require_once('../models/dao/ProgramsettingsDAO.class.php');
require_once('../models/dto/Programsetting.class.php');
require_once('../models/mysql/ProgramsettingsMySqlDAO.class.php');
require_once('../models/mysql/ext/ProgramsettingsMySqlExtDAO.class.php');
//Client
require_once('../models/dao/ClientDAO.class.php');
require_once('../models/dto/Client.class.php');
require_once('../models/mysql/ClientMySqlDAO.class.php');
require_once('../models/mysql/ext/ClientMySqlExtDAO.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 :- gt4setting

  ======================== */
//Programsetting
$programSetting = new Programsetting();
$programSettingDAO = new ProgramsettingsMySqlDAO();
$programSettingEX = new ProgramsettingsMySqlExtDAO();
//Client
$client = new Client();
$clientDAO = new ClientMySqlDAO();
$clientEX = new ClientMySqlExtDAO();

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

//include_once("../public/auth.php");
$smarty->display("header.html");





//check and use the condetion that suite this action
if (empty($do)) {
    ## first do db stuff
    try {
        // note need to excute sql with its order no line excutes before other
        $sqlQuery = array();
        //note drop "clientdebtchangetemp" means lose clientdebtcalc
        //$sqlQuery[] = 'drop table if exists clienttemp;';
        //note drop "clientdebtchangetemp" means rework do it only if necessary
        //$sqlQuery[] = 'drop table if exists clientdebtchangetemp;';
        //make sure tables do exist
        $sqlQuery[] = 'CREATE TABLE IF NOT EXISTS `clienttemp` (
                        `clientid` int(11) NOT NULL AUTO_INCREMENT,
                        `clientdebtcalc` float NOT NULL,
                        PRIMARY KEY (`clientid`)
                      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;';
        $sqlQuery[] = 'CREATE TABLE IF NOT EXISTS `clientdebtchangetemp` (
                        `clientdebtchangeid` int(11) NOT NULL,
                        `clientid` int(11) NOT NULL,
                        `clientdebtchangebefore` float NOT NULL,
                        `clientdebtchangeamount` float NOT NULL,
                        `clientdebtchangetype` int(11) NOT NULL,
                        `clientdebtchangeafter` float NOT NULL,
                        `tablename` varchar(256) NOT NULL
                      ) ENGINE=InnoDB DEFAULT CHARSET=latin1;';
        //insert data into clienttemp -- this is done every time to get new debt
        $sqlQuery[] = 'INSERT IGNORE INTO clienttemp
                       SELECT clientid,0 FROM client where clientid !=1';

        ### execute these queries till now and reset array of sqlQuery
        foreach ($sqlQuery as $sql) {
            $programSettingEX->runSqlQuery($sql);
        }
        $sqlQuery = array();

        //if error ignore
        try {
            $programSettingEX->runSqlQuery('ALTER TABLE clientdebtchangetemp DROP COLUMN id;');
        } catch (Exception $exc) {

        }



        $last_clientdebtchangeid_inTemp = (int) $programSettingEX->runSqlQueryGetSingleResult('select clientdebtchangeid from clientdebtchangetemp order by clientdebtchangeid desc limit 1');
        //before insert data remove column id as it will make error in "INSERT INTO clientdebtchangetemp ... " statment
        //insert data into it.... note insert only new rows
        $sqlQuery[] = 'INSERT INTO clientdebtchangetemp
                       SELECT clientdebtchangeid,clientid,clientdebtchangebefore,clientdebtchangeamount,clientdebtchangetype,clientdebtchangeafter,tablename
                       FROM clientdebtchange
                       where del = 0 and tablename != "clientDeficitController.php" and clientid !=1 and clientdebtchangeid > ' . $last_clientdebtchangeid_inTemp . ';';
        //make sure table structure has id column
        $sqlQuery[] = 'ALTER TABLE clientdebtchangetemp ADD id INT PRIMARY KEY AUTO_INCREMENT FIRST;';
        //reverse the clientdebtchangetype for new rows only as clientPayedDeptController.php type is entered in reverse way
        $sqlQuery[] = 'UPDATE clientdebtchangetemp
                       SET clientdebtchangetype = CASE clientdebtchangetype WHEN 0 THEN 1 WHEN 1 THEN 0 ELSE clientdebtchangetype END
                       where tablename="clientPayedDeptController.php" and id > ' . $last_clientdebtchangeid_inTemp . ';';
        $sqlQuery[] = 'DROP PROCEDURE IF EXISTS ROWPERROW;';

        //create procedure goes row by row to re calc debtbefore and after for each row in clientdebtchangetemp
        $sqlQuery[] = ' CREATE PROCEDURE ROWPERROW(IN i INT)
                        BEGIN
                        DECLARE n INT DEFAULT 0; -- n for total rows to loop through
                        -- DECLARE i INT DEFAULT 1; -- i for current row id
                        DECLARE debtbefore INT DEFAULT 0; -- save debt before from previos row in it
                        DECLARE clientdebtchangetypeinrow INT DEFAULT 0; -- save current type
                        DECLARE clientidinrow INT DEFAULT 0; -- save clientid
                        SELECT COUNT(*) FROM clientdebtchangetemp INTO n; -- no of rows of the table needed in while
                        WHILE i <= n DO
                                -- save client id of row and reset debtbefore
                                SELECT clientid FROM clientdebtchangetemp where id=i INTO clientidinrow;
                                SET debtbefore=0;
                                -- get debtbefore and save it in the row
                                SELECT clientdebtchangeafter FROM clientdebtchangetemp where id<i and clientid = clientidinrow order by id desc limit 1  INTO debtbefore;
                                update clientdebtchangetemp set clientdebtchangebefore  = debtbefore where id=i;

                                -- get clientdebtchangetype to calc clientdebtchangeafter with + for type=0 and - for type=1
                                SELECT clientdebtchangetype FROM clientdebtchangetemp where id=i INTO clientdebtchangetypeinrow;
                                IF clientdebtchangetypeinrow = 1 THEN
                                   update clientdebtchangetemp set clientdebtchangeafter  = clientdebtchangebefore - clientdebtchangeamount where id = i;
                                ELSE
                                   update clientdebtchangetemp set clientdebtchangeafter  = clientdebtchangebefore + clientdebtchangeamount where id = i;
                                END IF;
                                -- save new clc debt in clienttemp
                                update clienttemp set clientdebtcalc  = (select clientdebtchangeafter from clientdebtchangetemp where id=i) where clientid=clientidinrow;

                                -- move to next i
                                SET i = i + 1;
                        END WHILE;
                        End;
                        ;;
                        ';
        //now execute the stored procedure by name and pass no of row to start recalculating debtbefore and after
        $sqlQuery[] = 'CALL ROWPERROW(' . $last_clientdebtchangeid_inTemp . ');';
        //$sqlQuery[] = 'DROP PROCEDURE IF EXISTS ROWPERROW;';
        ### execute these queries till now and reset array of sqlQuery
        foreach ($sqlQuery as $sql) {
            $programSettingEX->runSqlQuery($sql);
        }
        $sqlQuery = array();



        //header("location:?do=sucess");
    } catch (Exception $e) {
        echo $e;
        die();
        //header("location:?do=error");
    }
    $shownData = $clientEX->getClientsDebtAfterRevision();
    $smarty->assign('shownData', $shownData);

    $youtubes = $youtubeLinkDAO->queryAll();
    $smarty->assign("youtubes", $youtubes);
    //now show report data clients with diffrent debts
    $smarty->display("clientdebtmessdiscovererview/clients.html");
} elseif ($do == "show") {
    echo 'Try again Later';
} elseif ($do == "sucess") {
    $smarty->display("succes.html");
} elseif ($do == "error") {
    $smarty->display("error.html");
}

//here the global templates
//$smarty->assign('customValidation', 1);

$smarty->display("footer.html");
/* ===============================
  function in this CONTROLLER
  ================================ */
?>