<?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");
//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/ClientareaDAO.class.php');
require_once('../models/dto/Clientarea.class.php');
require_once('../models/mysql/ClientareaMySqlDAO.class.php');
require_once('../models/mysql/ext/ClientareaMySqlExtDAO.class.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');

//Government
require_once('../models/dao/GovernmentDAO.class.php');
require_once('../models/dto/Government.class.php');
require_once('../models/mysql/GovernmentMySqlDAO.class.php');
require_once('../models/mysql/ext/GovernmentMySqlExtDAO.class.php');


//GovernArea
require_once('../models/dao/GoverareaDAO.class.php');
require_once('../models/dto/Goverarea.class.php');
require_once('../models/mysql/GoverareaMySqlDAO.class.php');
require_once('../models/mysql/ext/GoverareaMySqlExtDAO.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 :- clientController

  OPERTATION in Controller

  1- display add form
  2- add in client tbl
  3- display show and tempdelete
  4- edit client
  5- update client
  5.1- update client tbl with clientid

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

//here the global templates
if ($do != "showallajax") {
    $smarty->display("header.html");
}

//here goes the instances and general variables
//Clientarea
$Clientarea = new Clientarea();
$ClientareaDAO = new ClientareaMySqlDAO();
$ClientareaEX = new ClientareaMySqlExtDAO();
//Client
$client = new Client();
$clientDAO = new ClientMySqlDAO();
$clientExt = new ClientMySqlExtDAO();

//Programsetting
$Programsetting = new Programsetting();
$ProgramsettingDAO = new ProgramsettingsMySqlDAO();
$ProgramsettingEX = new ProgramsettingsMySqlExtDAO();

// Government
$government = new Government();
$governmentDAO = new GovernmentMySqlDAO();
$governmentExt = new GovernmentMySqlExtDAO();

//Clientdebtchange
$clientDeptChange = new Clientdebtchange();
$clientDeptChangeDAO = new ClientdebtchangeMySqlDAO();
$clientDeptChangeExt = new ClientdebtchangeMySqlExtDAO();

// GovernArea
$governArea = new Goverarea();
$governAreaDAO = new GoverareaMySqlDAO();
$governAreaExt = new GoverareaMySqlExtDAO();

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

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

$Programsettingdata = $ProgramsettingDAO->load(1);
$smarty->assign("Programsettingdata", $Programsettingdata);

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

//    $shownData = $clientDeptChangeExt->queryAllBysum();
//    var_dump($shownData);
//    $sum = 0;
//    foreach ($shownData as $data) {
//        if ($data->del != 1) {
//            $sum += $data->clientdebtchangeamount;
//        }
//    }
    $youtubes = $youtubeLinkDAO->queryAll();
    $smarty->assign("youtubes", $youtubes);
//    var_dump($sum);
////    $smarty->assign('sum', $sum);
//    $smarty->assign('shownData', $shownData);

    $smarty->display("clientdebtview/show.html");
} elseif ($do == "showallajax") {
    showallajax();
} 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
if ($do != "showallajax") {
    $smarty->display("footer.html");
}

//-- show AJAX -----------------------------------------------------------//
/* function Name : showallajax .
  Description :send data using json to datatable.
 */
function showallajax() {
    global $smarty;
    global $clientExt;
    global $clientDeptChangeExt;

    $sIndexColumn = "client.clientid";

    /* DB table to use */
    $sTable = "client
left join clientarea on clientarea.id=client.clientareaid
left join clientdebtchange on (clientdebtchange.`clientid` = client.clientid and `clientdebtchangeid`
                               in (
                                   select max(`clientdebtchangeid`) from clientdebtchange where clientdebtchange.tablename IN ('clientPayedDeptController.php' ,'kempialaController.php','depositcheckController.php') and del = 0 group by `clientid`
                               )

                              )
left join (
    SELECT sellbillclientid,max(sellbilldate) as lastSellBillDate
    FROM sellbill
	where conditions = 0
    group by sellbillclientid
    ) as qsellbill on qsellbill.sellbillclientid = client.clientid
left join (
    SELECT sellbillclientid,max(sellbilldate) as lastSellBillAndRuternDate
    FROM sellbillandrutern
	where conditions = 0
    group by sellbillclientid
    ) as qsellbillandrutern on qsellbillandrutern.sellbillclientid = client.clientid
left join (
    SELECT returnsellbillclientid,max(returnsellbilldate) as lastReturnSellBillDate
    FROM returnsellbill
	where conditions = 0
    group by returnsellbillclientid
    ) as qreturnsellbill on qreturnsellbill.returnsellbillclientid = client.clientid
    left join user as deligate on deligate.userid = client.delegateid
";

    $aColumns = array('client.clientid', 'client.clientname', 'client.clientdebt', 'client.clientphone', 'client.clientmobile', 'clientarea.name as areaName', 'clientdebtchange.clientdebtchangedate as lastClientDebtChangeDate'
        , 'clientdebtchange.clientdebtchangeamount', 'clientdebtchange.tablename', 'qsellbill.lastSellBillDate', 'qreturnsellbill.lastReturnSellBillDate', 'qsellbillandrutern.lastSellBillAndRuternDate', 'deligate.employeename');

// Indexed column (used for fast and accurate table cardinality)

    /*
     * Paging
     */
    $sLimit = "";
    if (isset($_POST['start']) && $_POST['length'] != '-1') {
        $sLimit = "LIMIT " . intval($_POST['start']) . ", " .
                intval($_POST['length']);
    }


    $sColumns = array('client.clientid', 'client.clientname', 'client.clientdebt', 'client.clientphone', 'client.clientmobile', 'clientarea.name', 'clientdebtchange.clientdebtchangedate'
        , 'clientdebtchange.clientdebtchangeamount', 'clientdebtchange.tablename', 'qsellbill.lastSellBillDate', 'qreturnsellbill.lastReturnSellBillDate', 'qsellbillandrutern.lastSellBillAndRuternDate', 'deligate.employeename');
    /*
     * Ordering
     */
    $sOrder = "";

    if (isset($_POST['order'][0])) {
        $sOrder = "ORDER BY ";
        for ($i = 0; $i < intval($_POST['columns']); $i++) {

            $orderByColumnIndex = $_POST['order'][0]['column'];
            if ($orderByColumnIndex == 5) {// it is clientarea.name as areaName
                $orderByColumn = "name";
            } else {
                $orderByColumn = $sColumns[intval($_POST['columns'][$orderByColumnIndex]['data'])];
            }

            if ($orderByColumnIndex == 6) {// it is clientdebtchange.clientdebtchangedate as lastClientDebtChangeDate
                $orderByColumn = "clientdebtchangedate";
            } else {
                $orderByColumn = $sColumns[intval($_POST['columns'][$orderByColumnIndex]['data'])];
            }

            $sOrder .= $orderByColumn . "  " . ($_POST['order'][$i]['dir'] === 'asc' ? 'asc' : 'desc') . ", ";
        }
        $sOrder = substr_replace($sOrder, "", -2);
        if ($sOrder == "ORDER BY ") {
            $sOrder = "ORDER BY client.clientname";
        }
    } else {
        $sOrder = "ORDER BY client.clientname";
    }
    /*
     * Filtering
     * NOTE this does not match the built-in DataTables filtering which does it
     * word by word on any field. It's possible to do here, but concerned about efficiency
     * on very large tables, and MySQL's regex functionality is very limited
     */
// where del =0 and aqarstatus =0 and updatestatus in(1,2,3,4) and complete=1
    $sWhere = '';
    if (isset($_POST['search']['value']) && $_POST['search']['value'] != "") {
        $sWhere = " where  (";
        for ($i = 0; $i < count($sColumns); $i++) {
            if ($sColumns[$i] == "clientdebtchange.clientdebtchangedate" || $sColumns[$i] == "qsellbill.lastSellBillDate" || $sColumns[$i] == "qreturnsellbill.lastReturnSellBillDate" || $sColumns[$i] == "qsellbillandrutern.lastSellBillAndRuternDate" || $sColumns[$i] == "qclientdebtchange.clientdebtchangeamount") {
//to get numbers and english chars only
##only alpha numeric is allowed . space not allowed
                $search = preg_replace('/[^#A-Za-z0-9]/', '', $_POST['search']['value']); // Removes special chars.
                if (!empty($search)) {
                    $sWhere .= $sColumns[$i] . " LIKE '%" . $search . "%' OR ";
                }
            } else {
                $sWhere .= $sColumns[$i] . " LIKE '%" . $_POST['search']['value'] . "%' OR ";
            }
        }
        $sWhere = substr_replace($sWhere, "", -3);
        $sWhere .= ") and client.conditions = 0 and client.clientdebt != 0";
    } else {
        $sWhere .= "where client.conditions = 0 and client.clientdebt != 0";
    }

    $sQuery = "SELECT " . str_replace(" , ", " ", implode(", ", $aColumns)) . " FROM  $sTable $sWhere $sOrder $sLimit ";
//    echo $sQuery;
    $rResult = $clientExt->runSelectQuery($sQuery);
    /* Data set length after filtering */
    $sQuery = "SELECT COUNT(" . $sIndexColumn . ") as clientid  FROM  $sTable $sWhere $sOrder "; //$sOrder

    $rResultFilterTotal = $clientExt->runSelectQuery($sQuery);

    foreach ($rResultFilterTotal as $oneis) {
        $countis = $oneis->clientid;
    }
    $iFilteredTotal = $countis;
    /* Total data set length */
    $sQuery = "SELECT COUNT(" . $sIndexColumn . ") as clientid FROM  $sTable";
    $rResultTotal = $clientExt->runSelectQuery($sQuery);

    foreach ($rResultTotal as $oneis) {
        $countit = $oneis->clientid;
    }
//$aResultTotal = mysql_fetch_array($rResultTotal);
    $iTotal = $countit;

    $output = array(
        "draw" => intval(filter_input(INPUT_POST, 'sEcho')), //$_REQUEST['draw'], //intval(filter_input(INPUT_POST, 'sEcho')),
        "recordsTotal" => intval($iTotal),
        "recordsFiltered" => intval($iFilteredTotal),
        "totals" => array(),
        "data" => array()
    );
    $rowno = 1;
    $sellQuantity = 0; //$piecesCount
    $plussum = 0;
    $minussum = 0;
    foreach ($rResult as $aRow) {

        if ($aRow->tablename == "depositcheckController.php") {
            $aRow->rondomtxt = "ايداع";
        } elseif ($aRow->tablename == "kempialaController.php") {
            $aRow->rondomtxt = "كمبيالات";
        } elseif ($aRow->tablename == "clientPayedDeptController.php") {
            $aRow->rondomtxt = "تحصيل";
        }

        $shownData = $clientDeptChangeExt->clientdeptchangeaz($aRow->clientid);

        foreach ($shownData as $data) {
            if ($data->clientdebtchangeafter > 0) {
                $plussum += $data->clientdebtchangeafter;
            } else {
                $minussum += $data->clientdebtchangeafter;
            }
        }
//        var_dump($aRow);
        $row = array();
        for ($i = 0; $i < count($aColumns); $i++) {
            switch ($aColumns[$i]) {
                case "client.clientid" :
                    $row[] = $rowno; //$aRow->$aColumns[0];
                    break;
                case "client.clientname" :
                    $row[] = $aRow->clientname;
                    break;
                case "client.clientdebt" :
                    $row[] = $aRow->clientdebt;
                    break;
                case "client.clientphone" :
                    $row[] = $aRow->clientphone;
                    break;
                case "client.clientmobile" :
                    $row[] = $aRow->clientmobile;
                    break;
                case "clientarea.name as areaName" :
                    $row[] = $aRow->areaName;
                    break;
                case "clientdebtchange.clientdebtchangedate as lastClientDebtChangeDate" :
                    $row[] = $aRow->lastClientDebtChangeDate;
                    break;
                case "clientdebtchange.clientdebtchangeamount" :
                    $row[] = $aRow->clientdebtchangeamount;
                    break;
                case "clientdebtchange.tablename" :
                    $row[] = $aRow->rondomtxt;
                    break;
                case "qsellbill.lastSellBillDate" :
                    $row[] = $aRow->lastSellBillDate;
                    break;
                case "qreturnsellbill.lastReturnSellBillDate" :
                    $row[] = $aRow->lastReturnSellBillDate;
                    break;
                case "qsellbillandrutern.lastSellBillAndRuternDate" :
                    $row[] = $aRow->lastSellBillAndRuternDate;
                    break;
                case "deligate.employeename" :
                    $row[] = $aRow->employeename;
                    break;


                default :
                    break;
            }
        }

//        if ($aRow->conditions == 0) {
//            $sellQuantity += $aRow->sellQuantity;
//        }
        $output['data'][] = $row;
        $rowno++;
    }


    $output['totals']["plussum"] = $plussum;
    $output['totals']["minussum"] = $minussum;
//    var_dump($output);
    echo json_encode($output, JSON_UNESCAPED_SLASHES);
}

?>