<?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');
//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
$sellBillDAO = new SellbillMySqlDAO();
$sellBillExt = new SellbillMySqlExtDAO();

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


//Clientarea
$Clientarea = new Clientarea();
$ClientareaDAO = new ClientareaMySqlDAO();
$ClientareaEX = new ClientareaMySqlExtDAO();
$today = date("Y-m-d");

//check and use the condition that suite this action



if (empty($do) || $do == "show") { // تقرير لعميل معين
    include_once("../public/authentication.php");

    $clientData = getClientData();
    $smarty->assign('clientData', $clientData);

    $clientid = $_REQUEST['clientid'];

    $startDate = $_REQUEST['from'];
    $endDate = $_REQUEST['to'];

    if (!empty($clientid) && $clientid != '-1' || !empty($startDate) && !empty($endDate) && $startDate != "" && $endDate != "") {
        $messageData = $clientDAO->load($clientid);
        $message = "إسم العميل: " . $messageData->clientname;
        $message .= "<br>التاريخ: من: " . $startDate . " إلى: " . $endDate;
        $smarty->assign("message", $message);

        clientShow($clientid, $startDate, $endDate);
    }
    $smarty->display("clientReportsview/clientShow.html");
}



if ($do == "clientShowByArea") {



    include_once("../public/authentication.php");

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

    global $queryString;

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

    $queryString = ' AND';
    $clientareaid = $_REQUEST["clientareaid"];

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

    $allclientarea = $ClientareaEX->queryAllbystring($queryString);
    $i = 1;


    $totalsum = 0;
    foreach ($allclientarea as $myallclientarea) {
        $sum = 0;

        // print_r('name : '.$myallclientarea->name .'<br>');
        $clientShowData = $clientExt->querybyarea($myallclientarea->id);
        if (count($clientShowData) > 0) {
            foreach ($clientShowData as $myclientShowData) {
                $sum = $sum + $myclientShowData->clientdebt;
            }

            $smarty->assign('clientShowData' . $i, $clientShowData);
            $smarty->assign('sum' . $i, $sum);

            $totalsum += $sum;
            $i++;
        }
    }

    $smarty->assign("allclientarea", $allclientarea);

    $smarty->assign("totalsum", $totalsum);
    $smarty->assign("today", $today);
    $smarty->display("clientReportsview/clientShowByArea.html");


    /* include_once("../public/authentication.php");

      $clientArea = getClientArea();
      $smarty->assign('clientArea',$clientArea);

      $id = $_REQUEST['id'];

      $startDate=$_REQUEST['from'];
      $endDate=$_REQUEST['to'];

      if(!empty($id) && $id != '-1' ||  !empty($startDate) && !empty($endDate) && $startDate !="" && $endDate !="")
      {
      $messageData = $ClientareaDAO->load($id);
      $message = "اسم الشركة: ".$messageData->clientname;
      $message .= "<br>التاريخ: من: ".$startDate." إلى: ".$endDate;
      $smarty->assign("message",$message);

      clientShow($id, $startDate, $endDate);
      }

      $smarty->display("clientReportsview/clientShowByArea.html");

     */
}




if ($do == "clientShowforsellonly") { // تقرير لعميل معين
    include_once("../public/authentication.php");

    $clientData = getClientData();
    $smarty->assign('clientData', $clientData);

    $clientid = $_REQUEST['clientid'];

    $startDate = $_REQUEST['from'];
    $endDate = $_REQUEST['to'];

    if (!empty($clientid) && $clientid != '-1' || !empty($startDate) && !empty($endDate) && $startDate != "" && $endDate != "") {
        $messageData = $clientDAO->load($clientid);
        $message = "إسم العميل: " . $messageData->clientname;
        $message .= "<br>التاريخ: من: " . $startDate . " إلى: " . $endDate;
        $smarty->assign("message", $message);

        clientShowforsellonly($clientid, $startDate, $endDate);
    }
    $smarty->display("clientReportsview/clientShowforsellonly.html");
} elseif ($do == "clientShow2") { // تقرير لعميل معين
    include_once("../public/authentication.php");

    $clientData = getClientData();
    $smarty->assign('clientData', $clientData);

    $clientid = $_REQUEST['clientid'];

    $startDate = $_REQUEST['from'];
    $endDate = $_REQUEST['to'];

    if (!empty($clientid) && $clientid != '-1' || !empty($startDate) && !empty($endDate) && $startDate != "" && $endDate != "") {
        $messageData = $clientDAO->load($clientid);
        $message = "إسم العميل: " . $messageData->clientname;
        $message .= "<br>التاريخ: من: " . $startDate . " إلى: " . $endDate;
        $smarty->assign("message", $message);

        clientShow2($clientid, $startDate, $endDate);
    }
    $smarty->display("clientReportsview/clientShow22.html");
} else if ($do == "all") {//تقارير العملاء (الدفعات)
    //عرض فواتير المبيعات والمردودات والعمليات الأخرى للعميل
    include_once("../public/authentication.php");

    $clientData = getClientData();

    $smarty->assign('clientData', $clientData);

    $clientid = $_REQUEST['clientid'];
    $clinetdept = $clientDAO->load($clientid);
    //print_r($clinetdept);
    $smarty->assign('clinetdept', $clinetdept);
    $startDate = $_REQUEST['from'];
    $endDate = $_REQUEST['to'];

    if (!empty($clientid) && $clientid != '-1' && !empty($startDate) && !empty($endDate) && $startDate != "" && $endDate != "") {
        $messageData = $clientDAO->load($clientid);
        $message = "إسم العميل: " . $messageData->clientname;
        $message .= "<br>التاريخ: من: " . $startDate . " إلى: " . $endDate;
        $smarty->assign("message", $message);

        showAllOperations($clientid, $startDate, $endDate);
    }
    $smarty->display("clientReportsview/reportForAll.html");
}
//here the global templates
$smarty->display("footer.html");

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

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

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

/* function getClientArea(){
  global $ClientareaDAO;
  global $ClientareaEX;

  $alldata = $ClientareaDAO->queryAll();
  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 clientShowforsellonly($clientid, $startDate, $endDate) {




    global $clientDeptChangeExt;
    global $sellBillDAO;

    global $mySellandruternbilldetailEx;
    global $mySellbilldetailEx;
    global $myReturnsellbilldetailEx;

    global $returnSellBillDAO;
    global $smarty;
    global $mySellbillandruternRecord;
    $queryString = ' AND';


    if (isset($clientid) && $clientid != '-1') {


        $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->clientShowforsellonly($queryString);

    //print_r($shownData);

    $i = 1;
    foreach ($shownData as $data) {


        // إجمالي الفاتورة و إجمالى المستحق من فاتورة المبيعات
        if ($data->tablename == "sellbillController.php") {
            //print("d");
            $data->link = "sellbillController.php?do=showDetail&sellbillid=" . $data->clientdebtchangemodelid;
            $sellBillData = $sellBillDAO->load($data->clientdebtchangemodelid);
            $data->sellbilltotalpayed = $sellBillData->sellbilltotalpayed; // إجمالي الفاتورة
            $data->sellbillfinalbill = $sellBillData->sellbillfinalbill; ////الباقى

            $myselldata = $mySellbilldetailEx->queryWithSellBillId($data->clientdebtchangemodelid);
            $smarty->assign('myselldata' . $i, $myselldata);


            //print_r($myselldata);
            $i ++;
        } else if ($data->tablename == "returnsellbillController.php") {
            $data->link = "returnsellbillController.php?do=showDetail&returnsellbillid=" . $data->clientdebtchangemodelid;
            $returnSellBillData = $returnSellBillDAO->load($data->clientdebtchangemodelid);
            $data->sellbilltotalpayed = $returnSellBillData->returnsellbilltotalpayed; // إجمالي الفاتورة
            $data->sellbillfinalbill = $returnSellBillData->returnsellbillfinalbill; ////الباقى

            $myselldata = $myReturnsellbilldetailEx->queryWithReturnsellbillId($data->clientdebtchangemodelid);
            $smarty->assign('myselldata' . $i, $myselldata);
            $i ++;
        } else if ($data->tablename == "sellbillandruternController.php") {
            $data->link = "sellbillandruternController.php?do=showDetail&sellbillid=" . $data->clientdebtchangemodelid;

            $sellAndReturnBillData = $mySellbillandruternRecord->load($data->clientdebtchangemodelid);
            $data->sellbilltotalpayed = $sellAndReturnBillData->sellbilltotalpayed; // إجمالي الفاتورة
            $data->sellbillfinalbill = $sellAndReturnBillData->sellbillfinalbill; ////الباقى
            $myselldata = $mySellandruternbilldetailEx->queryWithSellBillIdwithoutSellType($data->clientdebtchangemodelid);
            $smarty->assign('myselldata' . $i, $myselldata);
            $i ++;
        }
    }





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

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





    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->queryByClientIdAndDatenew($queryString);

    /* print_r("<pre>");
      print_r($shownData);
      print_r("</pre>");
     */

    $totalsum = 0;

    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 = $sellBillData->sellbillaftertotalbill; //إجمالى المستحق
                $data->discount = ($sellBillData->sellbilltotalbill - $sellBillData->sellbillaftertotalbill);

                $data->totalOperationCost = $sellBillData->sellbilltotalbill + $data->clientdebtchangebefore;
                $data->discards = 0;
                $data->payedamount = $sellBillData->sellbilltotalpayed;
                $data->clientdebtchangeamount = $sellBillData->sellbilltotalpayed;
                $data->payedamount = $sellBillData->sellbilltotalpayed;
            } else if ($data->tablename == "returnsellbillController.php") {
                $data->link = "returnsellbillController.php?do=showDetail&returnsellbillid=" . $data->clientdebtchangemodelid;
                $returnSellBillData = $returnSellBillDAO->load($data->clientdebtchangemodelid);
                $data->totalbill = 0; // إجمالي الفاتورة
                $data->finalbill = $returnSellBillData->returnsellbillaftertotalbill; //إجمالى المستحق
                $data->totalOperationCost = 0 + $data->clientdebtchangebefore; //إجمالى المستحق
                $data->discards = ($returnSellBillData->returnsellbillaftertotalbill);
                $data->discount = ($returnSellBillData->sellbilltotalbill - $returnSellBillData->sellbillaftertotalbill);

                $data->payedamount = $returnSellBillData->returnsellbilltotalpayed;
            } else if ($data->tablename == "sellbillandruternController.php") {
                $data->link = "sellbillandruternController.php?do=showDetail&sellbillid=" . $data->clientdebtchangemodelid;

                $sellAndReturnBillData = $mySellbillandruternRecord->load($data->clientdebtchangemodelid);
                $data->totalbill = $sellAndReturnBillData->sellbillprice;
                $data->totalOperationCost = $sellAndReturnBillData->sellbillprice + $data->clientdebtchangebefore;
                $data->discount = ($sellAndReturnBillData->sellbilltotalbill - $sellAndReturnBillData->sellbillaftertotalbill);
                $data->discards = ($sellAndReturnBillData->returnsellbillprice);
                $data->payedamount = $sellAndReturnBillData->sellbilltotalpayed;
            } else if ($data->tablename == "kempialaController.php") {
                $data->link = "kempialaController.php?do=editprint&id=" . $data->clientdebtchangemodelid;
                $data->discards = 0;
                $data->totalbill = 0;
                $data->totalOperationCost = $data->clientdebtchangebefore;
                $data->discount = 0;
                $data->payedamount = 0;
            } else if ($data->tablename == "depositcheckController.php") {
                $data->link = "depositcheckController.php?do=editprint&id=" . $data->clientdebtchangemodelid;
                $data->discards = 0;

                $data->totalbill = 0;
                $data->totalOperationCost = $data->clientdebtchangebefore;
                $data->discount = 0;
                $data->payedamount = 0;
            } else if ($data->tablename == "clientPayedDeptController.php") {
                $data->link = "clientPayedDeptController.php?do=editprint&id=" . $data->clientdebtchangeid;

                $data->discards = 0;

                $data->totalbill = 0;
                $data->totalOperationCost = $data->clientdebtchangebefore;
                $data->discount = 0;
                $data->payedamount = 0;
            } else if ($data->tablename == "clientController.php") {
                $data->link = "clientController.php?do=editprint&id=" . $data->clientdebtchangemodelid;
                $data->discards = 0;
                $data->totalOperationCost = $data->clientdebtchangeamount;
                $data->clientdebtchangeamount = 0;
                $data->totalbill = 0;
                $data->payedamount = 0;
            } else if ($data->tablename == "billreceiptController.php") {
                $data->link = "billreceiptController.php?do=editprint&id=" . $data->clientdebtchangemodelid;
                $data->discards = 0;
            } else if ($data->tablename == "clientDeficitController.php") {


                $data->link = "billreceiptController.php?do=editprint&id=" . $data->clientdebtchangemodelid;
                $data->discards = 0;
                $data->totalOperationCost = $data->clientdebtchangeamount;
                $data->clientdebtchangeamount = 0;
                $data->totalbill = 0;
                $data->payedamount = 0;
            } else {
                $data->discards = 0;
                $data->totalOperationCost = $data->clientdebtchangeamount;
                $data->clientdebtchangeamount = 0;
                $data->totalbill = 0;
                $data->payedamount = 0;
            }
        } else {
            $data->link = "#";
            $data->discards = 0;
            $data->totalOperationCost = $data->clientdebtchangeamount;
            $data->clientdebtchangeamount = 0;
            $data->totalbill = 0;
            $data->payedamount = 0;
        }

        $totalsum+=$data->totalOperationCost;
    }


    $smarty->assign('totalsum', $totalsum);

    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;
}
?>