<?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');
//Clientdebtchange
require_once('../models/dao/ClientdebtchangeDAO.class.php');
require_once('../models/dto/Clientdebtchange.class.php');
require_once('../models/mysql/ClientdebtchangeMySqlDAO.class.php');
require_once('../models/mysql/ext/ClientdebtchangeMySqlExtDAO.class.php');
//Sellbill
require_once('../models/dao/SellbillDAO.class.php');
require_once('../models/dto/Sellbill.class.php');
require_once('../models/mysql/SellbillMySqlDAO.class.php');
require_once('../models/mysql/ext/SellbillMySqlExtDAO.class.php');
//Sellbilldetail
require_once('../models/dao/SellbilldetailDAO.class.php');
require_once('../models/dto/Sellbilldetail.class.php');
require_once('../models/mysql/SellbilldetailMySqlDAO.class.php');
require_once('../models/mysql/ext/SellbilldetailMySqlExtDAO.class.php');
//Returnsellbill
require_once('../models/dao/ReturnsellbillDAO.class.php');
require_once('../models/dto/Returnsellbill.class.php');
require_once('../models/mysql/ReturnsellbillMySqlDAO.class.php');
require_once('../models/mysql/ext/ReturnsellbillMySqlExtDAO.class.php');
//Returnsellbilldetail
require_once('../models/dao/ReturnsellbilldetailDAO.class.php');
require_once('../models/dto/Returnsellbilldetail.class.php');
require_once('../models/mysql/ReturnsellbilldetailMySqlDAO.class.php');
require_once('../models/mysql/ext/ReturnsellbilldetailMySqlExtDAO.class.php');
//Sellbillandrutern
require_once('../models/dao/SellbillandruternDAO.class.php');
require_once('../models/dto/Sellbillandrutern.class.php');
require_once('../models/mysql/SellbillandruternMySqlDAO.class.php');
require_once('../models/mysql/ext/SellbillandruternMySqlExtDAO.class.php');
//Sellandruternbilldetail
require_once('../models/dao/SellandruternbilldetailDAO.class.php');
require_once('../models/dto/Sellandruternbilldetail.class.php');
require_once('../models/mysql/SellandruternbilldetailMySqlDAO.class.php');
require_once('../models/mysql/ext/SellandruternbilldetailMySqlExtDAO.class.php');

//Clientarea
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');


//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');


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

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

  Controller Name :- clientReportsController

  OPERTATION in Controller

  1- تقرير مديونية عميل
  ======================== */

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

//here goes the instances and general variables
//Client
$client = new Client();
$clientDAO = new ClientMySqlDAO();
$clientExt = new ClientMySqlExtDAO();
//Clientdebtchange
$clientDeptChange = new Clientdebtchange();
$clientDeptChangeDAO = new ClientdebtchangeMySqlDAO();
$clientDeptChangeExt = new ClientdebtchangeMySqlExtDAO();
//Sellbill
$sellBill = new Sellbill();
$sellBillDAO = new SellbillMySqlDAO();
$sellBillExt = new SellbillMySqlExtDAO();

//Returnsellbill
$returnSellBill = new Returnsellbill();
$returnSellBillDAO = new ReturnsellbillMySqlDAO();
$returnSellBillExt = new ReturnsellbillMySqlExtDAO();
//Sellbillandrutern
$mySellbillandrutern = new Sellbillandrutern();
$mySellbillandruternRecord = new SellbillandruternMySqlDAO();
$mySellbillandruternEx = new SellbillandruternMySqlExtDAO();
//Sellbilldetail
$mySellbilldetail = new Sellbilldetail();
$mySellbilldetailRecord = new SellbilldetailMySqlDAO();
$mySellbilldetailEx = new SellbilldetailMySqlExtDAO();
//Returnsellbilldetail
$myReturnsellbilldetail = new Returnsellbilldetail();
$myReturnsellbilldetailRecord = new ReturnsellbilldetailMySqlDAO();
$myReturnsellbilldetailEx = new ReturnsellbilldetailMySqlExtDAO();
//Sellandruternbilldetail
$mySellandruternbilldetail = new Sellandruternbilldetail();
$mySellandruternbilldetailRecord = new SellandruternbilldetailMySqlDAO();
$mySellandruternbilldetailEx = new SellandruternbilldetailMySqlExtDAO();

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

//Clientarea
$Clientarea = new Clientarea();
$ClientareaDAO = new ClientareaMySqlDAO();
$ClientareaEX = new ClientareaMySqlExtDAO();

//bill
$bills = new Bill();
$billsDAO = new BillsMySqlDAO();
$billsEX = new BillsMySqlExtDAO();


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

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

//billsReturn
$billsReturn = new Billsreturn();
$billsReturnDAO = new BillsreturnMySqlDAO();
$billsReturnEX = new BillsreturnMySqlExtDAO();

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

//check and use the condition that suite this action



if (empty($do)) {
    include_once("../public/authentication.php");
    $existData = 'no';
    $governmentData = $governmentDAO->queryAll();
    $smarty->assign("governmentData", $governmentData);

    $youtubes = $youtubeLinkDAO->queryAll();
    $smarty->assign("youtubes", $youtubes);

    $allClient = $clientDAO->queryAll();
    $smarty->assign("allClient", $allClient);

    $allclientareadrop = $ClientareaDAO->queryAll();
    $smarty->assign("allclientareadrop", $allclientareadrop);

    global $queryString;

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

    $queryString = ' AND';
    $clientareaid = $_POST["clientareaid"];
    $startDate = $_POST["from"];
    $endDate = $_POST["to"];
    $governmentId = $_POST['governmentid'];

    if (isset($clientareaid) && $clientareaid != '-1') {
        $queryString .= ' clientarea.ID = ' . $clientareaid . ' AND';
    }

    //explode string on space character to remove last AND occurence
    $arr = explode(' ', $queryString);
    //print_r($arr);
    if (isset($arr) && count($arr) > 0) {
        $lastWord = end($arr);


        if ($lastWord == 'AND') {//remove it
            array_pop($arr);
            //form the string again
            $queryString = implode(' ', $arr);
        }

//        print("<br>queryString: " . $queryString . "<br>");
    }
    if (empty($governmentId)) {
        $governmentId = -2;
        $clientareaid = -1;
    }
    // load the clientare for one government and convert it to an array
    if (!empty($governmentId) && $governmentId != '-2' && $clientareaid != '-1') {
        $clientsSelected = $governAreaDAO->queryByClientareaid($clientareaid);
        $governmentSingleData = $governmentDAO->load($governmentId);
        $smarty->assign("dataSelected", $clientsSelected);
        $smarty->assign("governmentSingleData", $governmentSingleData);
        $queryString = ' WHERE  government.governmetid =  ' . $governmentId . ' and clientarea.id  = ' . $clientareaid;
        $clientShowData = $clientExt->getClientsAndGovernmentAndArea($queryString);
    } elseif (!empty($governmentId) && $governmentId != '-2' && $clientareaid == '-1') {
        // select government from governarea
        $governmentSelected = $governAreaDAO->queryByGovernmentid($governmentId);
        $governmentSingleData = $governmentDAO->load($governmentId);
        $smarty->assign("governmentSingleData", $governmentSingleData);
        $smarty->assign("dataSelected", $governmentSelected);
        $queryString = ' WHERE  government.governmetid =  ' . $governmentId;
        $clientShowData = $clientExt->getClientsAndGovernmentAndArea($queryString);
    } elseif (!empty($governmentId) && $governmentId == '-2' && $clientareaid == '-1') {
        // select government from governarea
        $governmentSelected = $governAreaDAO->queryAll();
        //    $smarty->assign("governareaSelected", $governmentSelected);

        $smarty->assign("dataSelected", $governmentSelected);
        $content_arr = array();
        $queryString = ' where client.clientdebt != 0 ';
        $clientShowData = $clientExt->getClientsAndGovernmentAndArea($queryString);
        $governmentShowData = $clientExt->getClientsAndGovernmentAndArea($queryString);
        foreach ($governmentShowData as $value) {
            if (in_array($value->governmetid, $content_arr)) {

            } else {
                array_push($content_arr, $value->governmetid);
            }
        }
        $smarty->assign("content_arr", $content_arr);
    }



    $count_arr = array();
    $ii = 1;
    $totalsum = 0;

    $sum = 0;



    if (!empty($clientShowData)) {

        $datequery = " and clientdebtchange.del = 0 ";
        $billdatequery = " and bills.deleted = 0  ";
        $billsReturnQuery = " and billsreturn.deleted = 0  ";
        $sellBillsQuery = " and sellbill.conditions = 0 ";
        $returnSellBillsQuery = " and returnsellbill.conditions = 0 ";
        $sellbillAndRuternQuery = " and sellbillandrutern.conditions = 0 ";
        $billsQuery = " and bills.deleted = 0  ";


        // print("<br>queryString: ".$queryString."<br>");
        if (count($clientShowData) > 0) {
            foreach ($clientShowData as $myclientShowData) {
                $clientid = $myclientShowData->clientid;
                $paid_negative = 0; //  المدفوع فى حالة ان الصافى بالسالب
                $paid_positive = 0; // المدفوع فى حالة ان الصافى بالموجب
                $paid = 0; // محموع كل المدفوع
                $paid_clientDept = 0; // المدفوع فى ايصال سداد عميل
                $paid_bill = 0; // المدفوع فى فاتورة البصريات
                $net_total = 0; // محموع صافى المبيعات فى فواتير البيع
                $net_bill = 0; // محموع الصافى فى فواتير البصريات
                $total_discounts = 0; // مجموع الخصومات
                $total_gens = 0; // مجموع المبيعات
                //emaaaaaaaaan
                $depositPaid = 0;

                $tablename = 'clientController.php';

                $myClientData = $clientDeptChangeExt->selectListClientIdext($clientid, $datequery);
                $myClientBill = $billsEX->selectListClientIdext($clientid, $billdatequery);
                $myClientDept = $clientDeptChangeExt->selectClientIdByClientControllerext($clientid, $tablename, $datequery);
                $myClientBillReturn = $billsEX->selectListFromBillsReturnext($clientid, $billdatequery);
                $myClientDiscountAndTotalSellbills = $sellBillExt->orderClientsWithSellbillsSells2($clientid, $sellBillsQuery);
                $myClientDiscountAndTotalSellbillsAndReturn = $mySellbillandruternEx->orderClientsWithSellAndReturnSellbillsSells2($clientid, $sellbillAndRuternQuery);
                $myClientDiscountAndTotalReturnSellBill = $returnSellBillExt->orderClientsWithReturnSellbillsSells2($clientid, $returnSellBillsQuery);
                $myClientDiscountAndTotalBills = $billsEX->orderClientsWithBillsSells2($clientid, $billsQuery);
                $myClientDiscountAndTotalBillsReturn = $billsReturnEX->orderClientsWithBillsReturnSells2($clientid, $billsReturnQuery);

                $arrSize = count($myClientData) - 1;
                $arrBill = count($myClientBill) - 1;


                for ($i = 0; $i <= $arrSize; $i++) {
                    if ($i == 0) {
                        $myclientShowData->lastdate = $myClientData[$i]->clientdebtchangedate;
                    }
                    /* المدفوعات فى فواتير البيع  */

                    // لو الصافى بالسالب = الصافى + المتبقى
                    if (($myClientData[$i]->tablename == 'sellbillController.php' || $myClientData[$i]->tablename == 'sellbillandruternController.php' || $myClientData[$i]->tablename == 'returnsellbillController.php') && $myClientData[$i]->totalOperationCost < 0) {
                        //  $paid_negative = $myClientData[$i]->totalOperationCost + $myClientData[$i]->clientdebtchangeamount + $paid_negative;
                    }

                    // لو الصافى بالموجب = الصافى - المتبقى
                    if (($myClientData[$i]->tablename == 'sellbillController.php' || $myClientData[$i]->tablename == 'sellbillandruternController.php' || $myClientData[$i]->tablename == 'returnsellbillController.php') && $myClientData[$i]->totalOperationCost >= 0) {
                        //  $paid_positive = $myClientData[$i]->totalOperationCost - $myClientData[$i]->clientdebtchangeamount + $paid_positive;
                    }

                    // المدفوعات
                    if (($myClientData[$i]->tablename == 'sellbillController.php' || $myClientData[$i]->tablename == 'sellbillandruternController.php' || $myClientData[$i]->tablename == 'returnsellbillController.php' || $myClientData[$i]->tablename == 'depositcheckController.php' || $myClientData[$i]->tablename == 'datedCheckedController.php' || $myClientData[$i]->tablename == 'kempialaController.php')) {
                        if ($myClientData[$i]->clientdebtchangetype == 1) {
                            $myClientData[$i]->clientdebtchangeamount = $myClientData[$i]->clientdebtchangeamount * -1;
                        }


                        $paid_positive = $myClientData[$i]->clientdebtchangeamount + $paid_positive;

                        if ($myClientData[$i]->tablename == 'depositcheckController.php' && $myClientData[$i]->del == 0) {
                            $depositPaid = abs($myClientData[$i]->clientdebtchangeamount) + $depositPaid;
                        }
                    }

                    // المدفوع فى ايصال سداد عميل
                    if ($myClientData[$i]->tablename == 'clientPayedDeptController.php') {
                        //
                        $paid_clientDept = $myClientData[$i]->clientdebtchangeamount + $paid_clientDept;
                    }

                    /* نهاية المدفوعات فى فواتير البيع  */

                    /* الصافى فى فواتير البيع  */

                    if ($myClientData[$i]->tablename == 'sellbillController.php' || $myClientData[$i]->tablename == 'sellbillandruternController.php' || $myClientData[$i]->tablename == 'returnsellbillController.php') {
                        if ($myClientData[$i]->clientdebtchangetype == 1) {
                            $myClientData[$i]->totalOperationCost = $myClientData[$i]->totalOperationCost * -1;
                        }
                        $net_total = $myClientData[$i]->totalOperationCost + $net_total;
                    }

                    /* نهاية صافى المبيعات فى فواتير البيع  */
                }
                for ($j = 0; $j <= $arrBill; $j++) {
                    /* المدفوعات فى فواتير البصريات  */
                    $paid_bill = $myClientBill[$j]->finalnetbillvalue - $myClientBill[$j]->waitvalue - $myClientBillReturn[$j]->returnvalue + $paid_bill;
                    /* نهاية المدفوعات فى فواتير البصريات  */

                    /* الصافى فى فواتير البصريات  */
                    $net_bill = $myClientBill[$j]->finalnetbillvalue - $myClientBillReturn[$j]->returnprice + $net_bill;
                    /* نهاية صافى المبيعات فى فواتير البيع  */
                }


                $clientMainDept = $myClientDept->clientdebtchangeafter;
                $Net = $net_total + $net_bill;
//                print_r('<pre>');
//                print_r('$paid_negative = ' . $paid_negative);
//                print_r('<br>$paid_positive = ' . $paid_positive);
//                print_r('<br>$paid_clientDept = ' . $paid_clientDept);
//                print_r('<br>$paid_bill = ' . $paid_bill);
//
//                print_r('<pre>');
//
//                print_r('<br>$paid before  = ' . $paid);
                $paid = $paid_negative + $paid_positive + $paid_clientDept + $paid_bill;
                $balance = $clientMainDept - ( $paid - $Net);
                //print_r('<br>$paid after  = ' . $paid);

                $total_gens = ( $myClientDiscountAndTotalSellbills[0]->allGenTotal + $myClientDiscountAndTotalSellbillsAndReturn[0]->allGenTotalSellAndReturn + $myClientDiscountAndTotalBills[0]->totalNet ) - $myClientDiscountAndTotalReturnSellBill[0]->allGenTotalReturn;
                $total_discounts = $myClientDiscountAndTotalSellbills[0]->totaldiscount + $myClientDiscountAndTotalBills[0]->totaldiscount + $myClientDiscountAndTotalSellbillsAndReturn[0]->totaldiscount - ( $myClientDiscountAndTotalReturnSellBill[0]->totaldiscount + $myClientDiscountAndTotalBillsReturn[0]->clientreceivevalue);

//                if ($myClientDiscountAndTotalSellbills[0]->sellbillclientid == 14) {
//                    print_r('<pre> ');
//                    print_r($myClientDiscountAndTotalSellbills[0]);
//                    print_r('<pre>');
//                }
                //# اجمالى المدفوعات
                $total_payed = ( $paid_clientDept + $myClientDiscountAndTotalSellbills[0]->sellbilltotalAllPayed + $myClientDiscountAndTotalSellbillsAndReturn[0]->sellbillandruternPayedTotal ) - $myClientDiscountAndTotalReturnSellBill[0]->returnsellbilltotalpayed;


                $myclientShowData->myGenSum = $Net;
                $myclientShowData->myPaySum = $total_payed + $depositPaid;
                $myclientShowData->rondomtxt = $balance;
                $myclientShowData->myDiscountSum = $total_discounts;
                $myclientShowData->myTotalbillSum = $total_gens;




//                print_r('clientid = [ ' . $clientid . ' ] , Sub = [ ' . $paid_negative . ' ] , Post = [ ' . $paid_positive . ' ] , clientDept = [ ' . $paid_clientDept . ' ] , [ Bill = [ '
//                        . $paid_bill . ' ] , Total-Paid = [ ' . $paid . ' ] , Total-Net = [ ' . $net_total . ' ] , Net-Bill = [ ' . $net_bill . ' ] , Net = [ ' . $Net
//                        . ' , Balance = [ ' . $balance . ' ] <br>');


                /* الاجمالى */
                $sum = $balance + $sum;

                /* الاجمالى */
            }


            $existData = 'yes';

            /* Start Sum For One Area */
            $singleArea_arr = array();
            $singleGovernment_arr = array();
            $sumAllAreas = array();
            $myDiscountSumAllAreas = array();
            $myGenSumAllAreas = array();
            $myPaySumAllAreas = array();
            $clientdebtAllAreas = array();
            $sumDept = array(); // مجموع كل المديونية  بالمحافظة الواحدة
            $myPaySumSumGovernment = array(); // مجموع كل المدفوعات  بالمحافظة الواحدة
            $myTotalbillSumGovernment = array(); // مجموع كل المبيعات  بالمحافظة الواحدة
            $myDiscountSumGovernment = array(); // مجموع كل الخصومات  بالمحافظة الواحدة
            $myGenSumGovernment = array(); // مجموع كل الارباح  بالمحافظة الواحدة

            foreach ($clientShowData as $value) {

                if (in_array($value->clientareaid, $singleArea_arr)) {
                    $sumAllAreas[$value->clientareaid] = $sumAllAreas[$value->clientareaid] + $value->myTotalbillSum;
                    $myDiscountSumAllAreas[$value->clientareaid] = $myDiscountSumAllAreas[$value->clientareaid] + $value->myDiscountSum;
                    $myGenSumAllAreas[$value->clientareaid] = $myGenSumAllAreas[$value->clientareaid] + $value->myGenSum;
                    $myPaySumAllAreas[$value->clientareaid] = $myPaySumAllAreas[$value->clientareaid] + $value->myPaySum;
                    $clientdebtAllAreas[$value->clientareaid] = $clientdebtAllAreas[$value->clientareaid] + $value->clientdebt;
                } else {
                    array_push($singleArea_arr, $value->clientareaid);
                    $sumAllAreas[$value->clientareaid] = $value->myTotalbillSum;
                    $myDiscountSumAllAreas[$value->clientareaid] = $value->myDiscountSum;
                    $myGenSumAllAreas[$value->clientareaid] = $value->myGenSum;
                    $myPaySumAllAreas[$value->clientareaid] = $value->myPaySum;
                    $clientdebtAllAreas[$value->clientareaid] = $value->clientdebt;
                }

                if (in_array($value->governmetid, $singleGovernment_arr)) {
                    $sumDept[$value->governmetid] = $sumDept[$value->governmetid] + $value->clientdebt;
                    $myTotalbillSumGovernment[$value->governmetid] = $myTotalbillSumGovernment[$value->governmetid] + $value->myTotalbillSum;
                    $myDiscountSumGovernment[$value->governmetid] = $myDiscountSumGovernment[$value->governmetid] + $value->myDiscountSum;
                    $myGenSumGovernment[$value->governmetid] = $myGenSumGovernment[$value->governmetid] + $value->myGenSum;
                    $myPaySumSumGovernment[$value->governmetid] = $myPaySumSumGovernment[$value->governmetid] + $value->myPaySum;
                } else {
                    array_push($singleGovernment_arr, $value->governmetid);
                    $sumDept[$value->governmetid] = $value->clientdebt;
                    $myTotalbillSumGovernment[$value->governmetid] = $value->myTotalbillSum;
                    $myDiscountSumGovernment[$value->governmetid] = $value->myDiscountSum;
                    $myGenSumGovernment[$value->governmetid] = $value->myGenSum;
                    $myPaySumSumGovernment[$value->governmetid] = $value->myPaySum;
                }
            }



            $smarty->assign("sumAllAreas", $sumAllAreas);
            $smarty->assign("myDiscountSumAllAreas", $myDiscountSumAllAreas);
            $smarty->assign("myGenSumAllAreas", $myGenSumAllAreas);
            $smarty->assign("myPaySumAllAreas", $myPaySumAllAreas);
            $smarty->assign("clientdebtAllAreas", $clientdebtAllAreas);

            /* End Sum For One Area */
            $smarty->assign("myPaySumSumGovernment", $myPaySumSumGovernment);
            $smarty->assign("myTotalbillSumGovernment", $myTotalbillSumGovernment);
            $smarty->assign("myDiscountSumGovernment", $myDiscountSumGovernment);
            $smarty->assign("myGenSumGovernment", $myGenSumGovernment);
            $smarty->assign("sumDept", $sumDept);

            $smarty->assign("checkDisplay", $checkDisplay);
            $smarty->assign('clientShowData' . $ii . '', $clientShowData);
            array_push($count_arr, $clientShowData);
            $smarty->assign('sum' . $ii, $sum);
            $totalsum += $sum;
            $ii++;
        }
    }

    //* الاجمالى العام *//
    $totalmyTotalbill = 0;
    $totalmyPaySum = 0;
    $totalmyDiscount = 0;
    $totalmyGenSum = 0;
    $totalsumDept = 0;
    foreach ($myTotalbillSumGovernment as $value) {
        $totalmyTotalbill += $value;
    }
    foreach ($myPaySumSumGovernment as $value) {
        $totalmyPaySum += $value;
    }
    foreach ($myDiscountSumGovernment as $value) {
        $totalmyDiscount += $value;
    }
    foreach ($myGenSumGovernment as $value) {
        $totalmyGenSum += $value;
    }
    foreach ($sumDept as $value) {
        $totalsumDept += $value;
    }

    $smarty->assign("totalmyTotalbill", $totalmyTotalbill);
    $smarty->assign("totalmyPaySum", $totalmyPaySum);
    $smarty->assign("totalmyDiscount", $totalmyDiscount);
    $smarty->assign("totalmyGenSum", $totalmyGenSum);
    $smarty->assign("totalsumDept", $totalsumDept);


    $myId = 19;
    $smarty->assign("allclientarea", $allclientarea);
    $smarty->assign("totalsum", $totalsum);
    $smarty->assign("today", $today);
    $smarty->assign("existData", $existData);

    $smarty->display("clientReportsview/allbyclientareabyallgovernmen.html");
} elseif ($do == "allbyclientarea") { // تقرير لعميل معين
    include_once("../public/authentication.php");
    $existData = 'no';
    $governmentData = $governmentDAO->queryAll();
    $smarty->assign("governmentData", $governmentData);

    $allClient = $clientDAO->queryAll();
    $smarty->assign("allClient", $allClient);

    $allclientareadrop = $ClientareaDAO->queryAll();
    $smarty->assign("allclientareadrop", $allclientareadrop);

    global $queryString;

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

    $queryString = ' AND';
    $clientareaid = $_POST["clientareaid"];
    $startDate = $_POST["from"];
    $endDate = $_POST["to"];
    $governmentId = $_POST['governmentid'];
    $showDebitOnly = $_POST['showDebit'];
    //
    $searchDate = $_POST["searchDate"];
    if (empty($searchDate)) {
        $searchDate = 'sysDate';
    }
    $smarty->assign('searchDate', $searchDate);

    if (isset($clientareaid) && $clientareaid != '-1') {
        $queryString .= ' clientarea.ID = ' . $clientareaid . ' AND';
    }

    if (isset($showDebitOnly)) {
        $queryString .= ' client.clientdebt != 0 AND';
    }


    //explode string on space character to remove last AND occurence
    $arr = explode(' ', $queryString);
    //print_r($arr);
    if (isset($arr) && count($arr) > 0) {
        $lastWord = end($arr);


        if ($lastWord == 'AND') {//remove it
            array_pop($arr);
            //form the string again
            $queryString = implode(' ', $arr);
        }

        // print("<br>queryString: " . $queryString . "<br>");
    }
    // load the clientare for one government and convert it to an array
    if (!empty($governmentId) && $governmentId != '-2' && $clientareaid != '-1') {
        // select clientarea from governarea
        $clientsSelected = $governAreaDAO->queryByClientareaid($clientareaid);
        $governmentSingleData = $governmentDAO->load($governmentId);
        $smarty->assign("dataSelected", $clientsSelected);
        $smarty->assign("governmentSingleData", $governmentSingleData);
        $queryString = ' WHERE  government.governmetid =  ' . $governmentId . ' and clientarea.id  = ' . $clientareaid;
        if ($showDebitOnly == 1)
            $queryString .= ' and client.clientdebt != 0 ';
        $clientShowData = $clientExt->getClientsAndGovernmentAndArea($queryString);
    } elseif (!empty($governmentId) && $governmentId != '-2' && $clientareaid == '-1') {
        // select government from governarea
        $governmentSelected = $governAreaDAO->queryByGovernmentid($governmentId);
        $governmentSingleData = $governmentDAO->load($governmentId);
        $smarty->assign("governmentSingleData", $governmentSingleData);
        $smarty->assign("dataSelected", $governmentSelected);
        $queryString = ' WHERE  government.governmetid =  ' . $governmentId;
        if ($showDebitOnly == 1)
            $queryString .= ' and client.clientdebt != 0 ';
        $clientShowData = $clientExt->getClientsAndGovernmentAndArea($queryString);
    } elseif (!empty($governmentId) && $governmentId == '-2' && $clientareaid == '-1') {
        // select government from governarea


        $governmentSelected = $governAreaDAO->queryAll();
        //    $smarty->assign("governareaSelected", $governmentSelected);

        $smarty->assign("dataSelected", $governmentSelected);
        $content_arr = array();
        $queryString = ' ';
        if ($showDebitOnly == 1)
            $queryString = 'where client.clientdebt != 0 ';

        $clientShowData = $clientExt->getClientsAndGovernmentAndArea($queryString);
        $governmentShowData = $clientExt->getClientsAndGovernmentAndArea($queryString);
        foreach ($governmentShowData as $value) {
            if (in_array($value->governmetid, $content_arr)) {

            } else {
                array_push($content_arr, $value->governmetid);
            }
        }
        $smarty->assign("content_arr", $content_arr);
    }



    $count_arr = array();
    $ii = 1;
    $totalsum = 0;

    $sum = 0;



    if (!empty($clientShowData)) {
        if (!$_POST) {
            $startDate = date("Y-m-d");
            $endDate = date("Y-m-d");
            if ($searchDate == "sysDate") {
                $datequery = ' and clientdebtchange.del = 0 AND clientdebtchange.clientdebtchangedate >= ("' . $startDate . ' 00:00:00" ) AND clientdebtchange.clientdebtchangedate <= ("' . $endDate . ' 23:59:59") ';
            } elseif ($searchDate == "userSelectedDate") {
                $datequery = ' and clientdebtchange.del = 0 AND clientdebtchange.userSelectedDate >= ("' . $startDate . ' 00:00:00" ) AND clientdebtchange.userSelectedDate <= ("' . $endDate . ' 23:59:59") ';
            }
            $billdatequery = ' and bills.deleted = 0  AND  bills.billdate >=  ("' . $startDate . ' 00:00:00" ) AND bills.billdate <= ("' . $endDate . ' 23:59:59")';
            $sellBillsQuery = ' and sellbill.conditions = 0 AND sellbill.sellbilldate >= ("' . $startDate . ' 00:00:00" ) AND sellbill.sellbilldate <= ("' . $endDate . ' 23:59:59")';
            $sellbillAndRuternQuery = ' and sellbillandrutern.conditions = 0 AND sellbillandrutern.sellbilldate >= ("' . $startDate . ' 00:00:00" ) AND sellbillandrutern.sellbilldate <= ("' . $endDate . ' 23:59:59")';
            $returnSellBillsQuery = ' and returnsellbill.conditions = 0 AND returnsellbill.returnsellbilldate >= ("' . $startDate . ' 00:00:00" ) AND returnsellbill.returnsellbilldate <= ("' . $endDate . ' 23:59:59")';
            $billsQuery = ' and bills.deleted = 0  AND bills.receivedate >= ("' . $startDate . ' 00:00:00" ) AND bills.receivedate <= ("' . $endDate . ' 23:59:59")';
            $billsReturnQuery = ' and billsreturn.deleted = 0 AND billsreturn.date >= ("' . $startDate . '" ) AND billsreturn.date <= ("' . $endDate . '")';
        } elseif ((isset($clientareaid) && $clientareaid == '-1' && !empty($startDate) && !empty($endDate)) || (isset($clientareaid) && $clientareaid != '-1' && !empty($startDate) && !empty($endDate))) {
            $sellBillsQuery = ' and sellbill.conditions = 0 AND sellbill.sellbilldate >= ("' . $startDate . ' 00:00:00" ) AND sellbill.sellbilldate <= ("' . $endDate . ' 23:59:59")';
            if ($searchDate == "sysDate") {
                $datequery = ' and clientdebtchange.del = 0 AND clientdebtchange.clientdebtchangedate >= ("' . $startDate . ' 00:00:00" ) AND clientdebtchange.clientdebtchangedate <= ("' . $endDate . ' 23:59:59") ';
            } elseif ($searchDate == "userSelectedDate") {
                $datequery = ' and clientdebtchange.del = 0 AND clientdebtchange.userSelectedDate >= ("' . $startDate . ' 00:00:00" ) AND clientdebtchange.userSelectedDate <= ("' . $endDate . ' 23:59:59") ';
            }
            $billdatequery = ' and bills.deleted = 0  AND  bills.billdate >=  ("' . $startDate . ' 00:00:00" ) AND bills.billdate <= ("' . $endDate . ' 23:59:59")';
            $sellbillAndRuternQuery = ' and sellbillandrutern.conditions = 0 AND sellbillandrutern.sellbilldate >= ("' . $startDate . ' 00:00:00" ) AND sellbillandrutern.sellbilldate <= ("' . $endDate . ' 23:59:59")';
            $returnSellBillsQuery = ' and returnsellbill.conditions = 0 AND returnsellbill.returnsellbilldate >= ("' . $startDate . ' 00:00:00" ) AND returnsellbill.returnsellbilldate <= ("' . $endDate . ' 23:59:59")';
            $billsQuery = ' and bills.deleted = 0  AND bills.receivedate >= ("' . $startDate . ' 00:00:00" ) AND bills.receivedate <= ("' . $endDate . ' 23:59:59")';
            $billsReturnQuery = ' and billsreturn.deleted = 0 AND billsreturn.date >= ("' . $startDate . '" ) AND billsreturn.date <= ("' . $endDate . '")';
        } else {

            $datequery = " and clientdebtchange.del = 0 ";
            $billdatequery = " and bills.deleted = 0  ";
            $billsReturnQuery = " and billsreturn.deleted = 0  ";
            $sellBillsQuery = " and sellbill.conditions = 0 ";
            $returnSellBillsQuery = " and returnsellbill.conditions = 0 ";
            $sellbillAndRuternQuery = " and sellbillandrutern.conditions = 0 ";
            $billsQuery = " and bills.deleted = 0  ";
        }


        // print("<br>queryString: ".$queryString."<br>");
        if (count($clientShowData) > 0) {
            foreach ($clientShowData as $myclientShowData) {
                $clientid = $myclientShowData->clientid;
                $paid_negative = 0; //  المدفوع فى حالة ان الصافى بالسالب
                $paid_positive = 0; // المدفوع فى حالة ان الصافى بالموجب
                $paid = 0; // محموع كل المدفوع
                $paid_clientDept = 0; // المدفوع فى ايصال سداد عميل
                $paid_bill = 0; // المدفوع فى فاتورة البصريات
                $net_total = 0; // محموع صافى المبيعات فى فواتير البيع
                $net_bill = 0; // محموع الصافى فى فواتير البصريات
                $total_discounts = 0; // مجموع الخصومات
                $total_gens = 0; // مجموع المبيعات
                //eman
                $depositPaid = 0;

                $tablename = 'clientController.php';

                $myClientData = $clientDeptChangeExt->selectListClientIdext($clientid, $datequery);
                $myClientBill = $billsEX->selectListClientIdext($clientid, $billdatequery);
                $myClientDept = $clientDeptChangeExt->selectClientIdByClientControllerext($clientid, $tablename, $datequery);
                $myClientBillReturn = $billsEX->selectListFromBillsReturnext($clientid, $billdatequery);
                $myClientDiscountAndTotalSellbills = $sellBillExt->orderClientsWithSellbillsSells2($clientid, $sellBillsQuery);
                $myClientDiscountAndTotalSellbillsAndReturn = $mySellbillandruternEx->orderClientsWithSellAndReturnSellbillsSells2($clientid, $sellbillAndRuternQuery);
                $myClientDiscountAndTotalReturnSellBill = $returnSellBillExt->orderClientsWithReturnSellbillsSells2($clientid, $returnSellBillsQuery);
                $myClientDiscountAndTotalBills = $billsEX->orderClientsWithBillsSells2($clientid, $billsQuery);
                $myClientDiscountAndTotalBillsReturn = $billsReturnEX->orderClientsWithBillsReturnSells2($clientid, $billsReturnQuery);

                $arrSize = count($myClientData) - 1;
                $arrBill = count($myClientBill) - 1;


                for ($i = 0; $i <= $arrSize; $i++) {
                    if ($i == 0) {
                        $myclientShowData->lastdate = $myClientData[$i]->clientdebtchangedate;
                    }
                    /* المدفوعات فى فواتير البيع  */

                    // لو الصافى بالسالب = الصافى + المتبقى
                    if (($myClientData[$i]->tablename == 'sellbillController.php' || $myClientData[$i]->tablename == 'sellbillandruternController.php' || $myClientData[$i]->tablename == 'returnsellbillController.php') && $myClientData[$i]->totalOperationCost < 0) {
                        //  $paid_negative = $myClientData[$i]->totalOperationCost + $myClientData[$i]->clientdebtchangeamount + $paid_negative;
                    }

                    // لو الصافى بالموجب = الصافى - المتبقى
                    if (($myClientData[$i]->tablename == 'sellbillController.php' || $myClientData[$i]->tablename == 'sellbillandruternController.php' || $myClientData[$i]->tablename == 'returnsellbillController.php') && $myClientData[$i]->totalOperationCost >= 0) {
                        //  $paid_positive = $myClientData[$i]->totalOperationCost - $myClientData[$i]->clientdebtchangeamount + $paid_positive;
                    }

                    // المدفوعات
                    if (($myClientData[$i]->tablename == 'sellbillController.php' || $myClientData[$i]->tablename == 'sellbillandruternController.php' || $myClientData[$i]->tablename == 'returnsellbillController.php' || $myClientData[$i]->tablename == 'depositcheckController.php' || $myClientData[$i]->tablename == 'datedCheckedController.php' || $myClientData[$i]->tablename == 'kempialaController.php')) {
                        if ($myClientData[$i]->clientdebtchangetype == 1) {
                            $myClientData[$i]->clientdebtchangeamount = $myClientData[$i]->clientdebtchangeamount * -1;
                        }


                        $paid_positive = $myClientData[$i]->clientdebtchangeamount + $paid_positive;

                        if ($myClientData[$i]->tablename == 'depositcheckController.php' && $myClientData[$i]->del == 0) {
                            $depositPaid = abs($myClientData[$i]->clientdebtchangeamount) + $depositPaid;
                        }
                    }
                    // المدفوع فى ايصال سداد عميل
                    if ($myClientData[$i]->tablename == 'clientPayedDeptController.php') {
                        //
                        $paid_clientDept = $myClientData[$i]->clientdebtchangeamount + $paid_clientDept;
                    }

                    /* نهاية المدفوعات فى فواتير البيع  */

                    /* الصافى فى فواتير البيع  */

                    if ($myClientData[$i]->tablename == 'sellbillController.php' || $myClientData[$i]->tablename == 'sellbillandruternController.php' || $myClientData[$i]->tablename == 'returnsellbillController.php') {
                        if ($myClientData[$i]->clientdebtchangetype == 1) {
                            $myClientData[$i]->totalOperationCost = $myClientData[$i]->totalOperationCost * -1;
                        }
                        $net_total = $myClientData[$i]->totalOperationCost + $net_total;
                    }
                    /* نهاية صافى المبيعات فى فواتير البيع  */
                }
                for ($j = 0; $j <= $arrBill; $j++) {
                    /* المدفوعات فى فواتير البصريات  */
                    $paid_bill = $myClientBill[$j]->finalnetbillvalue - $myClientBill[$j]->waitvalue - $myClientBillReturn[$j]->returnvalue + $paid_bill;
                    /* نهاية المدفوعات فى فواتير البصريات  */

                    /* الصافى فى فواتير البصريات  */
                    $net_bill = $myClientBill[$j]->finalnetbillvalue - $myClientBillReturn[$j]->returnprice + $net_bill;
                    /* نهاية صافى المبيعات فى فواتير البيع  */
                }


                $clientMainDept = $myClientDept->clientdebtchangeafter;
                $Net = $net_total + $net_bill;
//                print_r('<pre>');
//                print_r('$paid_negative = ' . $paid_negative);
//                print_r('<br>$paid_positive = ' . $paid_positive);
//                print_r('<br>$paid_clientDept = ' . $paid_clientDept);
//                print_r('<br>$paid_bill = ' . $paid_bill);
//
//                print_r('<pre>');
//
//                print_r('<br>$paid before  = ' . $paid);
                $paid = $paid_negative + $paid_positive + $paid_clientDept + $paid_bill;
                $balance = $clientMainDept - ( $paid - $Net);
                //print_r('<br>$paid after  = ' . $paid);

                $total_gens = ( $myClientDiscountAndTotalSellbills[0]->allGenTotal + $myClientDiscountAndTotalSellbillsAndReturn[0]->allGenTotalSellAndReturn + $myClientDiscountAndTotalBills[0]->totalNet ) - $myClientDiscountAndTotalReturnSellBill[0]->allGenTotalReturn;
                $total_discounts = $myClientDiscountAndTotalSellbills[0]->totaldiscount + $myClientDiscountAndTotalBills[0]->totaldiscount + $myClientDiscountAndTotalSellbillsAndReturn[0]->totaldiscount - ( $myClientDiscountAndTotalReturnSellBill[0]->totaldiscount + $myClientDiscountAndTotalBillsReturn[0]->clientreceivevalue);

//                if ($myClientDiscountAndTotalSellbills[0]->sellbillclientid == 14) {
//                    print_r('<pre> ');
//                    print_r($myClientDiscountAndTotalSellbills[0]);
//                    print_r('<pre>');
//                }
                //# اجمالى المدفوعات
                $total_payed = ( $paid_clientDept + $myClientDiscountAndTotalSellbills[0]->sellbilltotalAllPayed + $myClientDiscountAndTotalSellbillsAndReturn[0]->sellbillandruternPayedTotal ) - $myClientDiscountAndTotalReturnSellBill[0]->returnsellbilltotalpayed;



                $myclientShowData->myGenSum = $Net;
                $myclientShowData->myPaySum = $total_payed + $depositPaid;
                $myclientShowData->rondomtxt = $balance;
                $myclientShowData->myDiscountSum = $total_discounts;
                $myclientShowData->myTotalbillSum = $total_gens;




//                print_r('clientid = [ ' . $clientid . ' ] , Sub = [ ' . $paid_negative . ' ] , Post = [ ' . $paid_positive . ' ] , clientDept = [ ' . $paid_clientDept . ' ] , [ Bill = [ '
//                        . $paid_bill . ' ] , Total-Paid = [ ' . $paid . ' ] , Total-Net = [ ' . $net_total . ' ] , Net-Bill = [ ' . $net_bill . ' ] , Net = [ ' . $Net
//                        . ' , Balance = [ ' . $balance . ' ] <br>');


                /* الاجمالى */
                $sum = $balance + $sum;

                /* الاجمالى */
            }


            $existData = 'yes';

            /* Start Sum For One Area */
            $singleArea_arr = array();
            $singleGovernment_arr = array();
            $sumAllAreas = array();
            $myDiscountSumAllAreas = array();
            $myGenSumAllAreas = array();
            $myPaySumAllAreas = array();
            $clientdebtAllAreas = array();
            $sumDept = array(); // مجموع كل المديونية  بالمحافظة الواحدة
            $myPaySumSumGovernment = array(); // مجموع كل المدفوعات  بالمحافظة الواحدة
            $myTotalbillSumGovernment = array(); // مجموع كل المبيعات  بالمحافظة الواحدة
            $myDiscountSumGovernment = array(); // مجموع كل الخصومات  بالمحافظة الواحدة
            $myGenSumGovernment = array(); // مجموع كل الارباح  بالمحافظة الواحدة

            foreach ($clientShowData as $value) {

                if (in_array($value->clientareaid, $singleArea_arr)) {
                    $sumAllAreas[$value->clientareaid] = $sumAllAreas[$value->clientareaid] + $value->myTotalbillSum;
                    $myDiscountSumAllAreas[$value->clientareaid] = $myDiscountSumAllAreas[$value->clientareaid] + $value->myDiscountSum;
                    $myGenSumAllAreas[$value->clientareaid] = $myGenSumAllAreas[$value->clientareaid] + $value->myGenSum;
                    $myPaySumAllAreas[$value->clientareaid] = $myPaySumAllAreas[$value->clientareaid] + $value->myPaySum;
                    $clientdebtAllAreas[$value->clientareaid] = $clientdebtAllAreas[$value->clientareaid] + $value->clientdebt;
                } else {
                    array_push($singleArea_arr, $value->clientareaid);
                    $sumAllAreas[$value->clientareaid] = $value->myTotalbillSum;
                    $myDiscountSumAllAreas[$value->clientareaid] = $value->myDiscountSum;
                    $myGenSumAllAreas[$value->clientareaid] = $value->myGenSum;
                    $myPaySumAllAreas[$value->clientareaid] = $value->myPaySum;
                    $clientdebtAllAreas[$value->clientareaid] = $value->clientdebt;
                }

                if (in_array($value->governmetid, $singleGovernment_arr)) {
                    $sumDept[$value->governmetid] = $sumDept[$value->governmetid] + $value->clientdebt;
                    $myTotalbillSumGovernment[$value->governmetid] = $myTotalbillSumGovernment[$value->governmetid] + $value->myTotalbillSum;
                    $myDiscountSumGovernment[$value->governmetid] = $myDiscountSumGovernment[$value->governmetid] + $value->myDiscountSum;
                    $myGenSumGovernment[$value->governmetid] = $myGenSumGovernment[$value->governmetid] + $value->myGenSum;
                    $myPaySumSumGovernment[$value->governmetid] = $myPaySumSumGovernment[$value->governmetid] + $value->myPaySum;
                } else {
                    array_push($singleGovernment_arr, $value->governmetid);
                    $sumDept[$value->governmetid] = $value->clientdebt;
                    $myTotalbillSumGovernment[$value->governmetid] = $value->myTotalbillSum;
                    $myDiscountSumGovernment[$value->governmetid] = $value->myDiscountSum;
                    $myGenSumGovernment[$value->governmetid] = $value->myGenSum;
                    $myPaySumSumGovernment[$value->governmetid] = $value->myPaySum;
                }
            }



            $smarty->assign("sumAllAreas", $sumAllAreas);
            $smarty->assign("myDiscountSumAllAreas", $myDiscountSumAllAreas);
            $smarty->assign("myGenSumAllAreas", $myGenSumAllAreas);
            $smarty->assign("myPaySumAllAreas", $myPaySumAllAreas);
            $smarty->assign("clientdebtAllAreas", $clientdebtAllAreas);

            /* End Sum For One Area */
            $smarty->assign("myPaySumSumGovernment", $myPaySumSumGovernment);
            $smarty->assign("myTotalbillSumGovernment", $myTotalbillSumGovernment);
            $smarty->assign("myDiscountSumGovernment", $myDiscountSumGovernment);
            $smarty->assign("myGenSumGovernment", $myGenSumGovernment);
            $smarty->assign("sumDept", $sumDept);

            $smarty->assign("checkDisplay", $checkDisplay);
            $smarty->assign('clientShowData' . $ii . '', $clientShowData);
            array_push($count_arr, $clientShowData);
            $smarty->assign('sum' . $ii, $sum);
            $totalsum += $sum;
            $ii++;
        }
    }

    //* الاجمالى العام *//
    $totalmyTotalbill = 0;
    $totalmyPaySum = 0;
    $totalmyDiscount = 0;
    $totalmyGenSum = 0;
    $totalsumDept = 0;
    foreach ($myTotalbillSumGovernment as $value) {
        $totalmyTotalbill += $value;
    }
    foreach ($myPaySumSumGovernment as $value) {
        $totalmyPaySum += $value;
    }
    foreach ($myDiscountSumGovernment as $value) {
        $totalmyDiscount += $value;
    }
    foreach ($myGenSumGovernment as $value) {
        $totalmyGenSum += $value;
    }
    foreach ($sumDept as $value) {
        $totalsumDept += $value;
    }

    $smarty->assign("totalmyTotalbill", $totalmyTotalbill);
    $smarty->assign("totalmyPaySum", $totalmyPaySum);
    $smarty->assign("totalmyDiscount", $totalmyDiscount);
    $smarty->assign("totalmyGenSum", $totalmyGenSum);
    $smarty->assign("totalsumDept", $totalsumDept);


    $myId = 19;
    $smarty->assign("allclientarea", $allclientarea);
    $smarty->assign("totalsum", $totalsum);
    $smarty->assign("today", $today);
    $smarty->assign("existData", $existData);

    if (!empty($governmentId) && $governmentId == '-2' && $clientareaid == '-1') {
        $smarty->display("clientReportsview/allbyclientareabyallgovernmen.html");
    } else {
        $smarty->display("clientReportsview/allbyclientareabygovernment.html");
    }
}


$smarty->assign("customClientArea", 1);

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

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

function getClientData() {
    global $clientDAO;
    global $clientExt;

    $alldata = $clientExt->queryAllsup();
    return $alldata;
}

function clientShow($clientid, $startDate, $endDate) {


    global $clientDeptChangeExt;
    global $sellBillDAO;
    global $returnSellBillDAO;
    global $smarty;
    global $mySellbillandruternRecord;

    $shownData = $clientDeptChangeExt->queryByClientIdAndDate($clientid, $startDate, $endDate);

    foreach ($shownData as $data) {
        if ($data->clientdebtchangemodelid != -1) {

            // إجمالي الفاتورة و إجمالى المستحق من فاتورة المبيعات
            if ($data->tablename == "sellbillController.php") {
                //print("d");
                $data->link = "sellbillController.php?do=editprint&id=" . $data->clientdebtchangemodelid;
                $sellBillData = $sellBillDAO->load($data->clientdebtchangemodelid);
                $data->totalbill = $sellBillData->sellbilltotalbill; // إجمالي الفاتورة
                $data->finalbill = $returnSellBillData->sellbillaftertotalbill; //إجمالى المستحق
                $data->totalOperationCost = $sellBillData->sellbillaftertotalbill;
            } else if ($data->tablename == "returnsellbillController.php") {
                $data->link = "returnsellbillController.php?do=editprint&id=" . $data->clientdebtchangemodelid;
                $returnSellBillData = $returnSellBillDAO->load($data->clientdebtchangemodelid);
                $data->totalbill = $returnSellBillData->returnsellbilltotalbill; // إجمالي الفاتورة
                $data->finalbill = $returnSellBillData->returnsellbillaftertotalbill; //إجمالى المستحق
                $data->totalOperationCost = $returnSellBillData->returnsellbillaftertotalbill;
            } else if ($data->tablename == "sellbillandruternController.php") {
                $data->link = "sellbillandruternController.php?do=editprint&id=" . $data->clientdebtchangemodelid;

                $sellAndReturnBillData = $mySellbillandruternRecord->load($data->clientdebtchangemodelid);
                $data->totalOperationCost = $sellAndReturnBillData->sellbillaftertotalbill;
            } else if ($data->tablename == "kempialaController.php") {
                $data->link = "kempialaController.php?do=editprint&id=" . $data->clientdebtchangemodelid;
            } else if ($data->tablename == "depositcheckController.php") {
                $data->link = "depositcheckController.php?do=editprint&id=" . $data->clientdebtchangemodelid;
            } else if ($data->tablename == "clientController.php") {
                $data->link = "clientController.php?do=editprint&id=" . $data->clientdebtchangemodelid;
            }
            //else
            //{
            ///print($data->tablename." ");
            //}
        } else {
            $data->link = "#";
        }
    }
    // assign db results to the template
    $smarty->assign('shownData', $shownData);


    $bure = $_POST["bure"];
    global $clientDeptChangeExt;
    global $sellBillDAO;
    global $returnSellBillDAO;
    global $smarty;
    global $mySellbillandruternRecord;
    $queryString = ' WHERE';


    if (isset($clientid) && $clientid != '-1') {
        /* $clientnamedata=$clientDAO->load($clientname);
          $message=" اسم العميل ".$clientnamedata->clientname;
          $smarty->assign('message',$message); */

        $queryString .= ' clientdebtchange.clientid =' . $clientid . ' AND';
    }

    if (!empty($startDate) && !empty($endDate)) {

        $message = " تقرير عميل من تاريخ  " . $startDate . "  الى تاريخ" . $endDate;
        $smarty->assign('message', $message);

        $queryString .= ' clientdebtchange.clientdebtchangedate >= "' . $startDate . '" AND clientdebtchange.clientdebtchangedate <= "' . $endDate . '" AND';
    }

    $arr = explode(' ', $queryString);
    if (isset($arr) && count($arr) > 0) {
        $lastWord = end($arr);
        if ($lastWord == 'AND') {//remove it
            array_pop($arr);
            //form the string again
            $queryString = implode(' ', $arr);
        } else if ($lastWord == 'WHERE') {//remove it
            array_pop($arr);
            $queryString = ' ';
        }
        //print("<br>queryString: ".$queryString."<br>");
    }

    $shownData = $clientDeptChangeExt->queryByClientIdAndDate($queryString);

    //print_r($shownData);

    foreach ($shownData as $data) {
        if ($data->clientdebtchangemodelid != -1) {

            // إجمالي الفاتورة و إجمالى المستحق من فاتورة المبيعات
            if ($data->tablename == "sellbillController.php") {
                //print("d");
                $data->link = "sellbillController.php?do=showDetail&sellbillid=" . $data->clientdebtchangemodelid;
                $sellBillData = $sellBillDAO->load($data->clientdebtchangemodelid);
                $data->totalbill = $sellBillData->sellbilltotalbill; // إجمالي الفاتورة
                $data->finalbill = $returnSellBillData->sellbillaftertotalbill; //إجمالى المستحق
                $data->totalOperationCost = $sellBillData->sellbillaftertotalbill;
            } else if ($data->tablename == "returnsellbillController.php") {
                $data->link = "returnsellbillController.php?do=showDetail&returnsellbillid=" . $data->clientdebtchangemodelid;
                $returnSellBillData = $returnSellBillDAO->load($data->clientdebtchangemodelid);
                $data->totalbill = $returnSellBillData->returnsellbilltotalbill; // إجمالي الفاتورة
                $data->finalbill = $returnSellBillData->returnsellbillaftertotalbill; //إجمالى المستحق
                $data->totalOperationCost = $returnSellBillData->returnsellbillaftertotalbill;
            } else if ($data->tablename == "sellbillandruternController.php") {
                $data->link = "sellbillandruternController.php?do=showDetail&sellbillid=" . $data->clientdebtchangemodelid;

                $sellAndReturnBillData = $mySellbillandruternRecord->load($data->clientdebtchangemodelid);
                $data->totalOperationCost = $sellAndReturnBillData->sellbillaftertotalbill;
            } else if ($data->tablename == "kempialaController.php") {
                $data->link = "kempialaController.php?do=editprint&id=" . $data->clientdebtchangemodelid;
            } else if ($data->tablename == "depositcheckController.php") {
                $data->link = "depositcheckController.php?do=editprint&id=" . $data->clientdebtchangemodelid;
            } else if ($data->tablename == "clientPayedDeptController.php") {
                $data->link = "clientPayedDeptController.php?do=editprint&id=" . $data->clientdebtchangeid;
            } else if ($data->tablename == "clientController.php") {
                $data->link = "clientController.php?do=editprint&id=" . $data->clientdebtchangemodelid;
            } else if ($data->tablename == "billreceiptController.php") {
                $data->link = "billreceiptController.php?do=editprint&id=" . $data->clientdebtchangemodelid;
            }
            //else
            //{
            ///print($data->tablename." ");
            //}
        } else {
            $data->link = "#";
        }
    }


    if ($bure == "1") {

        $smarty->assign('burex', $bure);
        foreach ($shownData as $datax) {
            $finalstring = $datax->finalstring;

            //	print_r($finalstring.'<br>');
            $type = $datax->clientdebtchangetype;
            $clientdebtchangeid = $datax->clientdebtchangeid;

            if ($type == 1) {

                $otherfinal = substr_replace($finalstring, '0', -1);

                foreach ($shownData as $xx) {

                    if ($xx->finalstring == $otherfinal) {
                        $xx->clientdebtchangeid = -1;
                        $datax->clientdebtchangeid = -1;
                    }
                }
            }
        }



        $ii = 0;
        //select all data from storemovement
        $startvalue = 0;
        $startbefore = 0;
        $startafter = 0;
        foreach ($shownData as $mov) {


            if ($bure == "1" && $mov->clientdebtchangeid > 0) {


                if ($ii == 0) {

                    $startvalue = $mov->clientdebtchangeamount;
                    $mov->clientdebtchangeamount = $startvalue;
                    $startbefore = $mov->clientdebtchangebefore;
                    $mov->clientdebtchangebefore = $startbefore;
                    $startafter = $mov->clientdebtchangeafter;
                    $mov->clientdebtchangeafter = $startafter;
                } else {

                    $startvalue = $mov->clientdebtchangeamount;
                    $mov->clientdebtchangeafter = $startbefore;

                    if ($mov->clientdebtchangetype == "1") {

                        //print_r('in if<br>');
                        $mov->clientdebtchangebefore = $startbefore + $startvalue;
                        $startbefore = $startbefore + $startvalue;
                    } else {
                        //print_r('in else if<br>');
                        //print_r('startafter'.$startbefore .'<br>');
                        //print_r('startvalue'.$startbefore .'<br>');
                        //	print_r($startafter - $startvalue .'<br>');
                        $mov->clientdebtchangebefore = $startbefore - $startvalue;
                        $startbefore = $startbefore - $startvalue;
                    }


                    //new product name with all path
                }





                $ii++;
            }
        }
    }





    // assign db results to the template
    $smarty->assign('shownData', $shownData);
}

function showAllOperations($clientid, $startDate, $endDate) {
    global $clientDeptChangeExt;
    global $sellBillDAO;
    global $returnSellBillDAO;
    global $smarty;
    global $mySellbillandruternRecord;

    //فواتير المبيعات
    //from sellbill table and sellbillandreturn table
    $sellbillData = showSellBillsByClientAndDate($clientid, $startDate, $endDate);

    //فواتير مردودات المبيعات
    //from returnsellbill table and sellbillandreturn table
    $returnsellbillData = showReturnSellBillsByClientAndDate($clientid, $startDate, $endDate);

    //get from clientdeptchange table
    $clientShowData = $clientDeptChangeExt->queryByClientIdAndDate2($clientid, $startDate, $endDate);
    if (count($clientShowData) > 0) {
        //print_r($clientShowData);

        foreach ($clientShowData as $c) {//when the payment is in the bill must select from the sel bill
            if ($c->tablename == "sellbillController.php") {
                $alldata = $sellBillDAO->load($c->clientdebtchangemodelid);

                $c->clientdebtchangeamount = $alldata->sellbilltotalpayed;
                $totalPrice += $alldata->sellbilltotalpayed;
            } else {

                $clientdebtchangetype = $c->clientdebtchangetype;
                //print_r($clientdebtchangetype.'<br>');
                if ($clientdebtchangetype == 0) {
                    //$totalPrice += $c->sellbilltotalpayed;
                    $totalPrice += $c->clientdebtchangeamount;
                } else {
                    $totalPrice -= $c->clientdebtchangeamount;
                    //$totalPrice -= $c->sellbilltotalpayed;
                }
                //$totalPrice += $c->clientdebtchangeamount;
            }
        }
        // print_r($clientShowData);
    }

    // assign db results to the template
    $smarty->assign('sellbillData', $sellbillData);
    $smarty->assign('returnsellbillData', $returnsellbillData);
    $smarty->assign('clientShowData', $clientShowData);
    $smarty->assign('totalPrice', $totalPrice);
}

function showSellBillsByClientAndDate($clientid, $startDate, $endDate) {
    global $sellBillExt;
    global $mySellbillandruternEx;
    global $mySellbilldetailEx;
    global $mySellandruternbilldetailEx;
    global $smarty;

    $totalBills = 0;
    $totalQTY = 0;

    $sellbillData = $sellBillExt->queryWithClientIdAndDate($clientid, $startDate, $endDate);
    //get products number in sell bills
    if (count($sellbillData) > 0) {
        foreach ($sellbillData as $bill) {
            $totalQty1 = $mySellbilldetailEx->countProductInSellBill($bill->sellbillid);
            $bill->sellQuantity = $totalQty1;
            $totalQTY += $totalQty1;
            $totalBills += $bill->sellbillaftertotalbill;
        }
    }


    //get sell bills from sellbillandrutern table
    $sellbillandruternData = $mySellbillandruternEx->querySellBillsOnlyWithClientIdAndDate($clientid, $startDate, $endDate);
    //get products number in sellbillandrutern
    if (count($sellbillandruternData) > 0) {
        foreach ($sellbillandruternData as $bill) {

            $bill->userid = -1;
            $sellbilldiscounttype = $bill->sellbilldiscounttype;
            $sellbilldiscount = $bill->sellbilldiscount;
            $sellbillprice;
            if ($sellbilldiscounttype == 1) {
                $sellbillprice = ($bill->sellbillprice) - ($bill->sellbilldiscount);
            } else {

                $sellbillprice = ($bill->sellbillprice) - ((($bill->sellbillprice) * ($bill->sellbilldiscount)) / 100);
            }
            $bill->sellbillprice = $sellbillprice;
            //print_r($bill);
            $totalQty2 = $mySellandruternbilldetailEx->countProductInSellBill($bill->sellbillid);
            $bill->sellQuantity = $totalQty2;
            $totalQTY += $totalQty2;
            $totalBills += $sellbillprice; //(($bill->sellbillprice)-($bill->sellbilldiscount));
        }
    }

    $sellbillData = array_merge($sellbillData, $sellbillandruternData);

    //print_r($sellbillData);
    $smarty->assign("selltotalQTY", $totalQTY);
    $smarty->assign("selltotalBills", $totalBills);
    return $sellbillData;
}

function showReturnSellBillsByClientAndDate($clientid, $startDate, $endDate) {
    global $returnSellBillExt;
    global $mySellbillandruternEx;
    global $myReturnsellbilldetailEx;
    global $mySellandruternbilldetailEx;
    global $smarty;

    $totalBills = 0;
    $totalQTY = 0;


    $returnsellbillData = $returnSellBillExt->queryWithClinetIdAndDate($clientid, $startDate, $endDate);
    //get products number in returnsellbill
    if (count($returnsellbillData) > 0) {
        foreach ($returnsellbillData as $bill) {
            $totalQty1 = $myReturnsellbilldetailEx->countProductInSellBill($bill->returnsellbillid);
            $bill->returnsellQuantity = $totalQty1;
            $totalQTY += $totalQty1;
            $totalBills += $bill->returnsellbillaftertotalbill;
        }
    }


    //get return sell bills from sellbillandrutern table
    //$sellbillandruternData =$mySellbillandruternEx->querySellBillsOnlyWithClientIdAndDate($clientid, $startDate,$endDate);
    $sellbillandruternData = $mySellbillandruternEx->queryReturnSellBillsOnlyWithClientIdAndDate($clientid, $startDate, $endDate);

    //print_r($sellbillandruternData);
    if (count($sellbillandruternData) > 0) {
        foreach ($sellbillandruternData as $sell) {

            $sell->userid = -1;

            $returnsellbillprice = ($sell->returnsellbillprice);


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

            $totalBills += $returnsellbillprice; //$sell->sellbillaftertotalbill;


            $sell->returnsellbillid = $sell->sellbillid;
            $sell->returnsellbillserial = $sell->sellbillserial;
            $sell->returnsellbillaftertotalbill = $sell->sellbillaftertotalbill;
            $sell->returnsellbilldate = $sell->sellbilldate;
            $totalQty2 = $mySellandruternbilldetailEx->countProductInReturnSellBill($sell->sellbillid);
            $sell->sellQuantity = $totalQty2;
            $totalQTY += $totalQty2;
        }
    }
    $returnsellbillData = array_merge($returnsellbillData, $sellbillandruternData);

    $smarty->assign("returntotalQTY", $totalQTY);
    $smarty->assign("returntotalBills", $totalBills);

    return $returnsellbillData;
}
?>