<?php
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');


//Dated Checked
require_once('../models/dao/DatedcheckedDAO.class.php');
require_once('../models/dto/Datedchecked.class.php');
require_once('../models/mysql/DatedcheckedMySqlDAO.class.php');
require_once('../models/mysql/ext/DatedcheckedMySqlExtDAO.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');

//Checkdeposit
require_once('../models/dao/CheckdepositDAO.class.php');
require_once('../models/dto/Checkdeposit.class.php');
require_once('../models/mysql/CheckdepositMySqlDAO.class.php');
require_once('../models/mysql/ext/CheckdepositMySqlExtDAO.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');


//Bill
require_once('../models/dao/BillsDAO.class.php');
require_once('../models/dto/Bill.class.php');
require_once('../models/mysql/BillsMySqlDAO.class.php');
require_once('../models/mysql/ext/BillsMySqlExtDAO.class.php');

//Save
require_once('../models/dao/SaveDAO.class.php');
require_once('../models/dto/Save.class.php');
require_once('../models/mysql/SaveMySqlDAO.class.php');
require_once('../models/mysql/ext/SaveMySqlExtDAO.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');

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



//Supplier
require_once('../models/dao/SupplierDAO.class.php');
require_once('../models/dto/Supplier.class.php');
require_once('../models/mysql/SupplierMySqlDAO.class.php');
require_once('../models/mysql/ext/SupplierMySqlExtDAO.class.php');
//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();

//Checkdeposit
$Checkdeposit = new Checkdeposit();
$CheckdepositDAO = new CheckdepositMySqlDAO();
$CheckdepositEX = new CheckdepositMySqlExtDAO();

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


//datedChecked
$datedChecked = new Datedchecked();
$datedCheckedDAO = new DatedcheckedMySqlDAO();
$datedCheckedEX = new DatedcheckedMySqlExtDAO();

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

//bill
$billsReturn = new Billsreturn();
$billsReturnDAO = new BillsreturnMySqlDAO();
$billsReturnEX = new BillsreturnMySqlExtDAO();
//Clientarea
$Clientarea = new Clientarea();
$ClientareaDAO = new ClientareaMySqlDAO();
$ClientareaEX = new ClientareaMySqlExtDAO();

//Save
$save = new Save();
$saveDAO = new SaveMySqlDAO();
$saveExt = new SaveMySqlExtDAO();

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

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


$supplier = new Supplier();
$supplierDAO = new SupplierMySqlDAO();
$supplierExt = new SupplierMySqlExtDAO();

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

//check and use the condition that suite this action
$ProgramsettingDAO = new ProgramsettingsMySqlDAO();

$programsettingsdata = $ProgramsettingDAO->load(1);
$smarty->assign('programsettingsdata', $programsettingsdata);


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

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

    $clientid = $_REQUEST['clientid'];
    $find = $_REQUEST['find'];
    if ($find == "month") {
        $_REQUEST['from'] = date("Y-m-01");
        $_REQUEST['to'] = date('Y-m-t');
    }
    $startDate = $_REQUEST['from'];
    $endDate = $_REQUEST['to'];
    $order = (int) $_REQUEST['order'];
    $smarty->assign("clientid", $clientid);
    if (!empty($clientid) && $clientid != '-1' ) {//|| (!empty($startDate) && !empty($endDate))
        $messageData = $clientDAO->load($clientid);
        $message = "تقرير العميل : " . $messageData->clientname;
        $message .= "<br>التاريخ من: " . $startDate . " إلى: " . $endDate;
        if ($_POST["matchOnly"] == 1)
            $message .= '<br> مطابقات العميل فقط';
        $smarty->assign("message", $message);
        $smarty->assign("clientDebt", $messageData->clientdebt);

        clientShow($clientid, $startDate, $endDate, $order);
    }

    $youtubes = $youtubeLinkDAO->queryAll();
    $smarty->assign("youtubes", $youtubes);
    $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);

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

    $clientid = $_REQUEST['clientid'];

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

    if (!empty($clientid) && $clientid != '-1' || !empty($startDate) && !empty($endDate) && $startDate != "" && $endDate != "") {
        $messageData = $clientDAO->load($clientid);
        $smarty->assign("clientDebt", $messageData->clientdebt);
        $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);

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

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

    if (!empty($startDate) && !empty($endDate)) {
        $startDate = $startDate . ' 00:00:00';
        $endDate = $endDate . ' 23:59:59';
    }


    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);
    } else if (!empty($clientid) && $clientid != '-1' && empty($startDate) && empty($endDate) && $startDate == "" && $endDate == "") {
        $messageData = $clientDAO->load($clientid);
        $message = "إسم العميل: " . $messageData->clientname;
        $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, $order) {
    global $CheckdepositEX;
    global $clientDeptChangeExt;
    global $sellBillDAO;
    global $returnSellBillDAO;
    global $smarty;
    global $mySellbillandruternRecord;
    global $billsDAO;
    global $myUserRecord;
    global $datedCheckedDAO;
    global $supplierDAO;

    $bure = $_POST["bure"];
    $matchOnly = $_POST["matchOnly"];
    $userData = $myUserRecord->load($_SESSION['userid']);

    $queryString = ' WHERE ';

    if ($userData->viewclients == 0) {
        $queryString .= ' client.userid =' . $_SESSION['userid'] . ' AND';
    }
    $searchDate = $_POST["searchDate"];
    if (empty($searchDate)) {
        $searchDate = 'sysDate';
    }
    $smarty->assign('searchDate', $searchDate);
    if (!empty($startDate))
        $startDate .= " 00:00:00";
    if (!empty($endDate))
        $endDate .= " 23:59:59";

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

    if ($searchDate == "sysDate") {
        if (!empty($startDate) && !empty($endDate)) {

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

            $queryString .= ' clientdebtchange.clientdebtchangedate >= "' . $startDate . '" AND clientdebtchange.clientdebtchangedate <= "' . $endDate . '" AND';
        }
    } elseif ($searchDate == "userSelectedDate") {
        if (!empty($startDate) && !empty($endDate)) {

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

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


    if ($matchOnly == 1) {
        $queryString .= ' clientdebtchange.tablename = "customerqccountmatching" 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>");
    }

    if ($order == 1) {
        if ($searchDate == "sysDate") {
            $orderBy = " order by clientdebtchangedate desc ";
        } elseif ($searchDate == "userSelectedDate") {
            $orderBy = " order by userSelectedDate desc ";
        }
    } else if ($order == 2) {
        $orderBy = " order by clientdebtchangeid asc ";
    } else if ($order == 3) {
        if ($searchDate == "sysDate") {
            $orderBy = " order by clientdebtchangedate asc ";
        } elseif ($searchDate == "userSelectedDate") {
            $orderBy = " order by userSelectedDate asc ";
        }
    } else {
        $orderBy = " order by clientdebtchangeid desc ";
    }

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

    //print_r($shownData);

    foreach ($shownData as $data) {
        if ($data->clientdebtchangetype == 0) {
            $total = $total + $data->clientdebtchangeamount;
        } else {

            $total = $total - $data->clientdebtchangeamount;
        }

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

            $data->datedCheckeddone = 0;
            $data->suppliername = '';
            if ($data->tablename == "datedCheckedController.php") {
                $data->link = "datedCheckedController.php?do=details&id=" . $data->clientdebtchangemodelid;
                $datedChecked = $datedCheckedDAO->load($data->clientdebtchangemodelid);
                $data->datedCheckeddone = $datedChecked->done;
                if ($datedChecked->endorsePersonId) {
                    $supplier = $supplierDAO->load($datedChecked->endorsePersonId);
                    $data->suppliername = $supplier->suppliername;
                }

                // إجمالي الفاتورة و إجمالى المستحق من فاتورة المبيعات
            } else 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->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 == "bills.php") {
                $data->link = "bills.php?do=details&id=" . $data->clientdebtchangemodelid;
                $BillsData = $billsDAO->load($data->clientdebtchangemodelid);
                $data->totalbill = $BillsData->productstotalprice; // إجمالي الفاتورة
                $data->finalbill = $BillsData->finalnetbillvalue; //إجمالى المستحق
                $data->totalOperationCost = $BillsData->finalnetbillvalue;
            } else if ($data->tablename == "billsreturn.php") {
                $data->link = "billsreturn.php?do=details&id=" . $data->clientdebtchangemodelid;
                $BillsData = $billsDAO->load($data->clientdebtchangemodelid);
                $data->totalbill = $BillsData->productstotalprice; // إجمالي الفاتورة
                $data->finalbill = $BillsData->finalnetbillvalue; //إجمالى المستحق
                $data->totalOperationCost = $BillsData->finalnetbillvalue;
            } else if ($data->tablename == "transferclientccountsController.php") {
                $data->link = "transferclientccountsController.php?do=edit&transferid=" . $data->clientdebtchangemodelid;
                $transferData = R::getRow('select clientidfrom,clientidto from transferclientaccounts where transferid=' . $data->clientdebtchangemodelid);
                $clientfrom = R::getCell('select clientname from client where clientid=' . $transferData['clientidfrom']);
                $clientto = R::getCell('select clientname from client where clientid=' . $transferData['clientidto']);
                $data->suppliername = "من/ $clientfrom - الى/ $clientto";
            } 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;
                $id = $data->clientdebtchangemodelid;
                $checkdata = $CheckdepositEX->loadEX($id);
                $data->bankname = $checkdata->bankname;
                $data->accountname = $checkdata->accountname;
            } else if ($data->tablename == "clientPayedDeptController.php") {
                $data->link = "clientPayedDeptController.php?do=editprint&id=" . $data->clientdebtchangeid;
            } else if ($data->tablename == "clientPayedDeptReportController.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 if ($data->tablename == "rb_kashf.php") {
                $data->link = "rb_kashf.php?do=editprint&id=" . $data->clientdebtchangemodelid;
            } else if ($data->tablename == "premiumController.php") {
                $data->link = "premiumController.php?do=details&id=" . $data->clientdebtchangemodelid;
            } else if ($data->tablename == "policyController.php") {
                $data->link = "policyController.php?do=edit&policyId=" . $data->clientdebtchangemodelid;
            } else if ($data->tablename == "dailyentry.php") {
                $data->link = "dailyentry.php?id=" . $data->clientdebtchangemodelid;
            }
            //else
            //{
            ///print($data->tablename." ");
            //}
        } else {
            if ($data->tablename == "clientDeficitController.php") {
                $data->link = "clientDeficitController.php?do=editprint&id=" . $data->clientdebtchangeid;
            } else
                $data->link = "#";
        }
    }



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

    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) {
                $mov->processname = str_replace("(معدله)", "", $mov->processname);

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

    //    var_dump($shownData);
}

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




    global $clientDeptChangeExt;
    global $sellBillDAO;

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

    global $returnSellBillDAO;
    global $smarty;
    global $mySellbillandruternRecord;

    global $saveDAO;

    global $myUserRecord;


    $bure = $_POST['bure'];

    $queryString = ' AND';

    $userData = $myUserRecord->load($_SESSION['userid']);
    if ($userData->viewbills == 0) {
        $queryString .= ' user.userid =' . $_SESSION['userid'] . ' AND';
    } else if ($userData->viewbills == 2) {
        $queryString .= ' user.usergroupid =' . $_SESSION['usergroupid'] . ' 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);
    $shownData = $clientDeptChangeExt->clientShowAll($queryString);

    //print_r($shownData);

    $i = 1;
    $allPcsPlus = 0;
    $allPcsMinus = 0;
    $allPayed = 0;
    $allTax = 0;
    $allPayedDept = 0;

    $showArr = array();
    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);

            $saveData = $saveDAO->load($sellBillData->sellbillsaveid);
            $data->savename = $saveData->savename;

            $data->sellbilltotalpayed = $sellBillData->sellbilltotalpayed; // إجمالي الفاتورة
            $data->sellbillfinalbill = $sellBillData->sellbillfinalbill; ////الباقى
            $discount = 0;
            if ($sellBillData->sellbilldiscount > 0) {
                if ($sellBillData->sellbilldiscounttype == 1)
                    $discount = $sellBillData->sellbilldiscount;
                else {
                    $discount = $sellBillData->sellbilltotalbill * $sellBillData->sellbilldiscount / 100;
                }
            }

            $data->tax = ($sellBillData->sellbilltotalbill - $discount) * $sellBillData->tax / 100;
            $allTax += $data->tax;


            $myselldata = $mySellbilldetailEx->queryWithSellBillId($data->clientdebtchangemodelid);
            foreach ($myselldata as $sellData) {
                if (!$bure || ($bure == 1 && $sellBillData->conditions == 0)) {
                    $allPayed += $sellData->sellbilldetailtotalprice;
                    $allPayedDept += $sellData->sellbilltotalpayed;
                    $allPcsPlus += $sellData->sellbilldetailquantity;
                }
            }
            $smarty->assign('myselldata' . $i, $myselldata);

            if ($sellBillData->conditions == 0) {
                $data->mysellData = $myselldata;
                array_push($showArr, $data);
            }

            //print_r($myselldata);
            $i++;
        } else if ($data->tablename == "returnsellbillController.php") {
            $data->link = "returnsellbillController.php?do=showDetail&returnsellbillid=" . $data->clientdebtchangemodelid;
            $returnSellBillData = $returnSellBillDAO->load($data->clientdebtchangemodelid);

            $saveData = $saveDAO->load($returnSellBillData->returnsellbillsaveid);
            $data->savename = $saveData->savename;

            $data->sellbilltotalpayed = $returnSellBillData->returnsellbilltotalpayed; // إجمالي الفاتورة
            $data->sellbillfinalbill = $returnSellBillData->returnsellbillfinalbill; ////الباقى
            if ($returnSellBillData->returnsellbilldiscount > 0) {
                if ($returnSellBillData->retursellbilldiscounttype == 1)
                    $discount = $returnSellBillData->returnsellbilldiscount;
                else {
                    $discount = $returnSellBillData->returnsellbilltotalbill * $returnSellBillData->returnsellbilldiscount / 100;
                }
            }

            $tax = ($returnSellBillData->returnsellbilltotalbill - $discount) * $returnSellBillData->tax / 100;
            $data->tax = $tax * -1;
            $allTax += $data->tax;
            $myselldata = $myReturnsellbilldetailEx->queryWithReturnsellbillId($data->clientdebtchangemodelid);
            foreach ($myselldata as $sellData) {
                if (!$bure || ($bure == 1 && $returnSellBillData->conditions == 0)) {
                    $allPcsMinus += $sellData->returnsellbilldetailquantity;
                    $allPayed -= $sellData->returnsellbilldetailtotalprice;
                    $allPayedDept -= $sellData->returnsellbilltotalpayed;
                }
            }
            $smarty->assign('myselldata' . $i, $myselldata);


            if ($returnSellBillData->conditions == 0) {
                $data->mysellData = $myselldata;
                array_push($showArr, $data);
            }

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

            $sellAndReturnBillData = $mySellbillandruternRecord->load($data->clientdebtchangemodelid);

            $saveData = $saveDAO->load($sellAndReturnBillData->sellbillsaveid);
            $data->savename = $saveData->savename;

            $data->sellbilltotalpayed = $sellAndReturnBillData->sellbilltotalpayed; // إجمالي الفاتورة
            $data->sellbillfinalbill = $sellAndReturnBillData->sellbillfinalbill; ////الباقى

            $myselldata = $mySellandruternbilldetailEx->queryWithSellBillIdwithoutSellType($data->clientdebtchangemodelid);
            foreach ($myselldata as $sellData) {
                if (!$bure || ($bure == 1 && $sellAndReturnBillData->conditions == 0)) {
                    if ($sellData->selltype != 1) {
                        $allPayed += $sellData->sellbilldetailtotalprice;
                        $allPayedDept += $sellData->sellbilltotalpayed;
                        $allPcsPlus += $sellData->sellbilldetailquantity;
                    } else {
                        $allPayed -= $sellData->sellbilldetailtotalprice;
                        $allPayedDept -= $sellData->sellbilltotalpayed;
                        $allPcsMinus += $sellData->sellbilldetailquantity;
                    }
                }
            }

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


            if ($sellAndReturnBillData->conditions == 0) {
                $data->mysellData = $myselldata;
                array_push($showArr, $data);
            }
            $i++;
        } elseif ($data->tablename != "sellbillController.php" && $data->tablename != "returnsellbillController.php" && $data->tablename != "sellbillandruternController.php") {
            //unset($data->mysellData);
            $data->sellbilltotalpayed = $data->clientdebtchangeamount; // التغير
            $smarty->assign('myselldata' . $i, array());
            array_push($showArr, $data);
            $i++;
            if($data->tablename == "clientPayedDeptController.php" || $data->tablename == "clientPayedDeptSellBillsController.php")
                $allPayedDept += $data->clientdebtchangeamount;
            if($data->tablename == "clientPayedDeptReturnSellBillsController.php")
                $allPayedDept -= $data->clientdebtchangeamount;
        }
    }

    $smarty->assign('allPayedDept', $allPayedDept);
    $smarty->assign('allPayed', $allPayed);
    $smarty->assign('allPcsPlus', $allPcsPlus);
    $smarty->assign('allPcsMinus', $allPcsMinus);
    $smarty->assign('allTax', $allTax);


    // assign db results to the template
    if ($bure == 1) {
        $smarty->assign('shownData', $showArr);
    } else {
        $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;
    global $CheckdepositEX;


    //فواتير المبيعات
    //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
    if (!empty($startDate) && !empty($endDate) && $startDate != "" && $endDate != "") {
        $cliDate = '  DATE(clientdebtchange.clientdebtchangedate) >=  "' . $startDate . '"  AND DATE(clientdebtchange.clientdebtchangedate) <= "' . $endDate . '" AND ';
    }
    $clientShowData = $clientDeptChangeExt->queryByClientIdAndDate3($clientid, $cliDate);
    if (count($clientShowData) > 0) {
        //print_r($clientShowData);

        foreach ($clientShowData as $c) { //when the payment is in the bill must select from the sel bill
            //            print_r('<pre>');
            //            print_r($c);
            //            print_r('<pre>');
            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 ($c->tablename != 'clientController.php') {
                    if ($clientdebtchangetype == 0) {
                        //$totalPrice += $c->sellbilltotalpayed;
                        $totalPrice += $c->clientdebtchangeamount;
                    } else {
                        $totalPrice -= $c->clientdebtchangeamount;
                        //$totalPrice -= $c->sellbilltotalpayed;
                    }
                } else {
                    $clientControllerVal = $c->clientdebtchangeamount;
                }

                //$totalPrice += $c->clientdebtchangeamount;
            }

            //print_r('<br>$totalPrice = ' . $totalPrice . '  process name =  ' . $c->processname);
            if ($c->tablename == "depositcheckController.php") {
                $id = $c->clientdebtchangemodelid;
                $checkdata = $CheckdepositEX->loadEX($id);
                $c->bankname = $checkdata->bankname;
                $c->accountname = $checkdata->accountname;
            }
        }

        //print_r($clientShowData);
    }

    //# جمع دفعة فى فاتورة مبيعات على اجمالى الدفعات
    foreach ($sellbillData as $data) {
        if ($data->sellbilltotalpayed != 0 && $data->conditions == 0) {
            $totalPrice -= $data->sellbilltotalpayed;
        }
    }

    //# جمع دفعة فى فاتورة المبيعات والمرتجعات على اجمالى الدفعات
    foreach ($returnsellbillData[1] as $data) {
        if ($data->sellbilltotalpayed != 0 && $data->conditions == 0) {
            $totalPrice -= $data->sellbilltotalpayed;
        }
    }

    //print_r('$clientControllerVal = ' . $clientControllerVal);
    // assign db results to the template
    $smarty->assign('clientControllerVal', $clientControllerVal);

    $smarty->assign('sellbillData', $sellbillData);
    $smarty->assign('returnsellbillData', $returnsellbillData[0]);
    $smarty->assign('sellBillAndReturnData', $returnsellbillData[1]);

    $smarty->assign('clientShowData', $clientShowData);
    $smarty->assign('totalPrice', $totalPrice);
}

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

    $bure = $_POST['bure'];

    $totalBills = 0;
    $totalQTY = 0;

    if (!empty($startDate) && !empty($endDate) && $startDate != "" && $endDate != "") {
        $sellBillDate = ' sellbill.sellbilldate  >= "' . $startDate . '" and sellbill.sellbilldate <= "' . $endDate . '" AND ';
        $sellBillReData = 'sellbillandrutern.sellbilldate  >= "' . $startDate . '" and sellbillandrutern.sellbilldate <= "' . $endDate . '" and ';
    }
    if ($bure == 1) {
        $sellbillData = $sellBillExt->queryWithClientIdAndDate2($clientid, $sellBillDate);
    } else {
        $sellbillData = $sellBillExt->queryWithClientIdAndDateExt($clientid, $sellBillDate);
    }
    //get products number in sell bills
    if (count($sellbillData) > 0) {
        foreach ($sellbillData as $bill) {
            $totalQty1 = $mySellbilldetailEx->countProductInSellBill($bill->sellbillid);
            $bill->sellQuantity = $totalQty1;
            if ($bill->conditions == 0) {
                $totalQTY += $totalQty1;
                $totalBills += $bill->sellbillaftertotalbill;
            } /* else {
              $totalQTY -= $totalQty1;
              $totalBills -= $bill->sellbillaftertotalbill;
              } */
        }
    }


    //get sell bills from sellbillandrutern table
    if ($bure == 1) {
        $sellbillandruternData = $mySellbillandruternEx->querySellBillsOnlyWithClientIdAndDate2($clientid, $sellBillReData);
    } else {
        $sellbillandruternData = $mySellbillandruternEx->querySellBillsOnlyWithClientIdAndDateExt($clientid, $sellBillReData);
    }
    //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);
            if ($bure == 1) {
                $totalQty2 = $mySellandruternbilldetailEx->countProductInSellBill($bill->sellbillid);
            } else {
                $totalQty2 = $mySellandruternbilldetailEx->countProductInSellBillExt($bill->sellbillid);
            }
            $bill->sellQuantity = $totalQty2;

            if ($bill->conditions == 0) {
                $totalQTY += $totalQty2;
                $totalBills += $sellbillprice; //(($bill->sellbillprice)-($bill->sellbilldiscount));
            } /* else {
              $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;

    $bure = $_POST['bure'];

    if (!empty($startDate) && !empty($endDate) && $startDate != "" && $endDate != "") {
        $returnSellBillDate = ' returnsellbill.returnsellbilldate  >= "' . $startDate . '" and returnsellbill.returnsellbilldate <= "' . $endDate . '" AND ';
        $selbillandruternReData = '  sellbillandrutern.sellbilldate  >= "' . $startDate . '" and sellbillandrutern.sellbilldate <= "' . $endDate . '" and ';
    }
    if ($bure == 1) {
        $returnsellbillData = $returnSellBillExt->queryWithClinetIdAndDate2($clientid, $returnSellBillDate);
    } else {
        $returnsellbillData = $returnSellBillExt->queryWithClinetIdAndDateExt($clientid, $returnSellBillDate);
    }
    //get products number in returnsellbill
    if (count($returnsellbillData) > 0) {
        foreach ($returnsellbillData as $bill) {
            $totalQty1 = $myReturnsellbilldetailEx->countProductInSellBill($bill->returnsellbillid);
            $bill->returnsellQuantity = $totalQty1;
            if ($bill->conditions == 0) {
                $totalQTY += $totalQty1;
                $totalBills += $bill->returnsellbillaftertotalbill;
            }
            /* else {
              $totalQTY -= $totalQty1;
              $totalBills -= $bill->returnsellbillaftertotalbill;
              } */
        }
    }


    //get return sell bills from sellbillandrutern table
    //$sellbillandruternData =$mySellbillandruternEx->querySellBillsOnlyWithClientIdAndDate($clientid, $startDate,$endDate);
    if ($bure == 1) {
        $sellbillandruternData = $mySellbillandruternEx->queryReturnSellBillsOnlyWithClientIdAndDate2($clientid, $selbillandruternReData);
    } else {
        $sellbillandruternData = $mySellbillandruternEx->queryReturnSellBillsOnlyWithClientIdAndDateExt($clientid, $selbillandruternReData);
    }

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

            $sell->userid = -1;

            $returnsellbillprice = ($sell->returnsellbillprice);


            /////////////////////
            if ($sell->conditions == 0) {
                $totalBills += $returnsellbillprice; //$sell->sellbillaftertotalbill;
            }
            /* else {
              $totalBills -= $returnsellbillprice;
              } */

            $sell->returnsellbillid = $sell->sellbillid;
            $sell->returnsellbillserial = $sell->sellbillserial;
            $sell->returnsellbillaftertotalbill = $sell->sellbillaftertotalbill;
            $sell->returnsellbilldate = $sell->sellbilldate;
            $totalQty2 = $mySellandruternbilldetailEx->countProductInReturnSellBill($sell->sellbillid);
            $sell->sellQuantity = $totalQty2;
            if ($sell->conditions == 0) {
                $totalQTY += $totalQty2;
            }
        }
    }

    $returnsellbillData = array_merge($returnsellbillData, $sellbillandruternData);
    //$returnsellbillData = sortByDate('asc', $returnsellbillData);
    usort($returnsellbillData, function ($a, $b) {
        //a-b now it is asc
        return strtotime($a->returnsellbilldate) - strtotime($b->returnsellbilldate);
    });
    $returnsellbill = array($returnsellbillData, $sellbillandruternData);

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

    return $returnsellbill;
}
