<?php

//the global file operation
include("../public/impOpreation.php");
include("../library/regenerateSession.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");
//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');
//Storereport
require_once('../models/dao/StorereportDAO.class.php');
require_once('../models/dto/Storereport.class.php');
require_once('../models/mysql/StorereportMySqlDAO.class.php');
require_once('../models/mysql/ext/StorereportMySqlExtDAO.class.php');
//Storedetail
require_once('../models/dao/StoredetailDAO.class.php');
require_once('../models/dto/Storedetail.class.php');
require_once('../models/mysql/StoredetailMySqlDAO.class.php');
require_once('../models/mysql/ext/StoredetailMySqlExtDAO.class.php');
//Store
require_once('../models/dao/StoreDAO.class.php');
require_once('../models/dto/Store.class.php');
require_once('../models/mysql/StoreMySqlDAO.class.php');
require_once('../models/mysql/ext/StoreMySqlExtDAO.class.php');
//Product
require_once('../models/dao/ProductDAO.class.php');
require_once('../models/dto/Product.class.php');
require_once('../models/mysql/ProductMySqlDAO.class.php');
require_once('../models/mysql/ext/ProductMySqlExtDAO.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');
//Savedaily
require_once('../models/dao/SavedailyDAO.class.php');
require_once('../models/dto/Savedaily.class.php');
require_once('../models/mysql/SavedailyMySqlDAO.class.php');
require_once('../models/mysql/ext/SavedailyMySqlExtDAO.class.php');
//Client
require_once('../models/dao/ClientDAO.class.php');
require_once('../models/dto/Client.class.php');
require_once('../models/mysql/ClientMySqlDAO.class.php');
require_once('../models/mysql/ext/ClientMySqlExtDAO.class.php');
//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');
//Billname
require_once('../models/dao/BillnameDAO.class.php');
require_once('../models/dto/Billname.class.php');
require_once('../models/mysql/BillnameMySqlDAO.class.php');
require_once('../models/mysql/ext/BillnameMySqlExtDAO.class.php');
//Billsetting
require_once('../models/dao/BillsettingsDAO.class.php');
require_once('../models/dto/Billsetting.class.php');
require_once('../models/mysql/BillsettingsMySqlDAO.class.php');
require_once('../models/mysql/ext/BillsettingsMySqlExtDAO.class.php');
//User
require_once('../models/dao/UserDAO.class.php');
require_once('../models/dto/User.class.php');
require_once('../models/mysql/UserMySqlDAO.class.php');
require_once('../models/mysql/ext/UserMySqlExtDAO.class.php');
//Productunit
require_once('../models/dao/ProductunitDAO.class.php');
require_once('../models/dto/Productunit.class.php');
require_once('../models/mysql/ProductunitMySqlDAO.class.php');
require_once('../models/mysql/ext/ProductunitMySqlExtDAO.class.php');
//Productcat
require_once('../models/dao/ProductcatDAO.class.php');
require_once('../models/dto/Productcat.class.php');
require_once('../models/mysql/ProductcatMySqlDAO.class.php');
require_once('../models/mysql/ext/ProductcatMySqlExtDAO.class.php');
//Unit
require_once('../models/dao/UnitDAO.class.php');
require_once('../models/dto/Unit.class.php');
require_once('../models/mysql/UnitMySqlDAO.class.php');
require_once('../models/mysql/ext/UnitMySqlExtDAO.class.php');
//Productserial
require_once('../models/dao/ProductserialDAO.class.php');
require_once('../models/dto/Productserial.class.php');
require_once('../models/mysql/ProductserialMySqlDAO.class.php');
require_once('../models/mysql/ext/ProductserialMySqlExtDAO.class.php');
//Programsetting
require_once('../models/dao/ProgramsettingsDAO.class.php');
require_once('../models/dto/Programsetting.class.php');
require_once('../models/mysql/ProgramsettingsMySqlDAO.class.php');
require_once('../models/mysql/ext/ProgramsettingsMySqlExtDAO.class.php');
//Soldserialproduct
require_once('../models/dao/SoldserialproductDAO.class.php');
require_once('../models/dto/Soldserialproduct.class.php');
require_once('../models/mysql/SoldserialproductMySqlDAO.class.php');
require_once('../models/mysql/ext/SoldserialproductMySqlExtDAO.class.php');

require_once('../models/dao/YoutubeLinkDAO.class.php');
require_once('../models/dto/YoutubeLink.class.php');
require_once('../models/mysql/YoutubeLinkMySqlDAO.class.php');
require_once('../models/mysql/ext/YoutubeLinkMySqlExtDAO.class.php');

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

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

  Controller Name :- sellBillReportsController

  OPERTATION in Controller

  1- show sell bills only
  2- search

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

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

//here goes the instances and general variables
//Sellbill
$mySellbill = new Sellbill();
$mySellbillRecord = new SellbillMySqlDAO();
$mySellbillEx = new SellbillMySqlExtDAO();
//Sellbilldetail
$mySellbilldetail = new Sellbilldetail();
$mySellbilldetailRecord = new SellbilldetailMySqlDAO();
$mySellbilldetailEx = new SellbilldetailMySqlExtDAO();
//Returnsellbill
$myReturnsellbill = new Returnsellbill();
$myReturnsellbillRecord = new ReturnsellbillMySqlDAO();
$myReturnsellbillEx = new ReturnsellbillMySqlExtDAO();
//Returnsellbilldetail
$myReturnsellbilldetail = new Returnsellbilldetail();
$myReturnsellbilldetailRecord = new ReturnsellbilldetailMySqlDAO();
$myReturnsellbilldetailEX = new ReturnsellbilldetailMySqlExtDAO();

//Sellbillandrutern
$mySellbillandrutern = new Sellbillandrutern();
$mySellbillandruternRecord = new SellbillandruternMySqlDAO();
$mySellbillandruternEx = new SellbillandruternMySqlExtDAO();
//Sellandruternbilldetail
$mySellandruternbilldetail = new Sellandruternbilldetail();
$mySellandruternbilldetailRecord = new SellandruternbilldetailMySqlDAO();
$mySellandruternbilldetailEx = new SellandruternbilldetailMySqlExtDAO();
//Storereport
$myStorereport = new Storereport();
$myStorereportRecord = new StorereportMySqlDAO();
$myStorereportEx = new StorereportMySqlExtDAO();
//Storedetail
$myStoredetailEx = new StoredetailMySqlExtDAO();
$myStoredetailRecord = new StoredetailMySqlDAO();
//Store
$myStoreRecord = new StoreMySqlDAO();
//Product
$myProductRecord = new ProductMySqlDAO();
$myProductEx = new ProductMySqlExtDAO();
//save
$mySave = new Save();
$mySaveRecord = new SaveMySqlDAO();
$mySaveEx = new SaveMySqlExtDAO();
//savedaily
$mySavedaily = new Savedaily();
$mySavedailyRecord = new SavedailyMySqlDAO();
$mySavedailyEx = new SavedailyMySqlExtDAO();
//Client
$myClientRecord = new ClientMySqlDAO();
$myClient = new Client();
$myClientEx = new ClientMySqlExtDAO();
//Clientdebtchange
$myClientdebtchange = new Clientdebtchange();
$myClientdebtchangeRecord = new ClientdebtchangeMySqlDAO();
//Billname
$myBillnameRecord = new BillnameMySqlDAO();
$myBillnameEx = new BillnameMySqlExtDAO();
//Billsetting
$myBillsettingEx = new BillsettingsMySqlExtDAO();
//User
$myUserEx = new UserMySqlExtDAO();
//Productunit
$myProductunitEx = new ProductunitMySqlExtDAO();
//Productcat
$myProductcatRecord = new ProductcatMySqlDAO();
$myProductcatEx = new ProductcatMySqlExtDAO();
//Unit
$myUnitRecord = new UnitMySqlDAO();
//Productserial
$myProductserial = new Productserial();
$myProductserialEx = new ProductserialMySqlExtDAO();
//Programsetting
$ProgramsettingDAO = new ProgramsettingsMySqlDAO();
$programSettingExt = new ProgramsettingsMySqlExtDAO();
//Soldserialproduct
$soldSerialProduct = new Soldserialproduct();
$soldSerialProductDAO = new SoldserialproductMySqlDAO();
$soldSerialProductExt = new SoldserialproductMySqlExtDAO();

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

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


//check and use the condition that suites this action
if (empty($do) || $do == "show") {
    //here the permission check
    include_once("../public/authentication.php");

    $clientData = loadAllClient();
    $smarty->assign("clientData", $clientData);

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

    //$sellbillSearchData=loadAllSellBill();
    //$smarty->assign("sellbillSearchData",$sellbillSearchData);

    $startDate = $_REQUEST['from'];
    $endDate = $_REQUEST['to'];
    $sellbillSerial = $_REQUEST['sellbillserial'];
    $sellbillId = $_REQUEST['sellbillid'];
    $clientId = $_REQUEST['clientid'];
    $sellbillcomment = $_REQUEST['sellbillcomment'];

    /* if (isset($clientId) && $clientId != "-1") {
      showByClient($clientId);

      $messageData = $myClientRecord->load($clientId);
      $message = "إسم العميل: " . $messageData->clientname;
      $smarty->assign("message", $message);
      } elseif (isset($sellbillSerial) && $sellbillSerial != "") {
      showBySriral($sellbillSerial);

      $message = "رقم الفاتورة: " . $sellbillSerial;
      $smarty->assign("message", $message);
      } elseif (isset($sellbillId) && $sellbillId != "") {
      showBySellbillId($sellbillId);

      $message = "الرقم المتسلسل : " . $sellbillId;
      $smarty->assign("message", $message);
      } elseif (isset($startDate) && $startDate != "" && isset($endDate) && $endDate != "") {
      showByDate($startDate, $endDate);

      $message = "من تاريخ : " . $startDate . " إلى تاريخ : " . $endDate . "";
      $smarty->assign("message", $message);
      } else { */
    //showAll(date("Y-m-d"), date("Y-m-d"));
    showAll();

    //$message = "من تاريخ : ".date("Y-m-d")." إلى تاريخ : ".date("Y-m-d")."";
    //$smarty->assign("message",$message);
    //	}
    // }

    $smarty->display("sellBillReportsview/show.html");
} elseif ($do == "sellandreturnreport") {
    $clientData = loadAllClient();
    $smarty->assign("clientData", $clientData);

    $youtubes = $youtubeLinkDAO->queryAll();
    $smarty->assign("youtubes", $youtubes);
    showAll();
    returnshowAll();
    $smarty->display("sellBillReportsview/sellandreturnreport.html");
} elseif ($do == "sellDetail") {
    include_once("../public/authentication.php");

    $sellbillId = $_GET['sellbillid'];

    $showDetailData = showDetail($sellbillId);

    $smarty->assign('sellbillData', $showDetailData[0]);
    $smarty->assign('sellbilldetailData', $showDetailData[1]);
    $smarty->assign("quantity", $showDetailData[2]);

    if ($showDetailData[0]->billnameid != 0 && isset($showDetailData[0]->billnameid) && $showDetailData[0]->billnameid != "" && !empty($showDetailData[0]->billnameid)) {
        $billsettingsData = loadBillProperty($showDetailData[0]->billnameid);
        $smarty->assign("billsettingsData", $billsettingsData);

        $billnameAddData = $myBillnameRecord->load($showDetailData[0]->billnameid);
        $smarty->assign("billnameAddData", $billnameAddData);
    }
    $Programsettingdata = $ProgramsettingDAO->load(1);
    $smarty->assign("Programsettingdata", $Programsettingdata);

    $smarty->display("sellBillReportsview/sellDetail.html");
} elseif ($do == "sellAndReturnDetail") {
    include_once("../public/authentication.php");

    $sellbillId = $_GET['sellbillid'];

    $showDetailData = showsellAndReturnDetail($sellbillId);
    $smarty->assign('sellbillandruternData', $showDetailData[0]);
    $smarty->assign('sellbilldetailData', $showDetailData[1]);
    $smarty->assign('ruternbilldetailData', $showDetailData[2]);
    $smarty->assign('sellQuantity', $showDetailData[3]);
    $smarty->assign('returnQuantity', $showDetailData[4]);

    $billsettingsData = loadBillProperty($showDetailData[0]->billnameid);
    $smarty->assign("billsettingsData", $billsettingsData);

    $billnameAddData = $myBillnameRecord->load($showDetailData[0]->billnameid);
    $smarty->assign("billnameAddData", $billnameAddData);

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

    //here the smarty templates
    $smarty->display("sellBillReportsview/sellAndReturnDetail.html");
}
$smarty->assign("customValidation", 1);

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

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

//select data from client tbl
function loadAllClient()
{
    global $myClientRecord;
    global $myClientEx;
    global $myUserRecord;
    $userData = $myUserRecord->load($_SESSION['userid']);
    $queryString = '';
    if ($userData->viewclients == 0) {
        $queryString .= ' AND client.userid =' . $_SESSION['userid'];
    }
    $clientData = $myClientEx->queryByCondition($queryString);
    return $clientData;
}

function loadAllSellBill()
{
    global $mySellbillRecord;

    // select all data from sellbill tbl
    $sellbillSearchData = $mySellbillRecord->queryAll();
    return $sellbillSearchData;
}

// select from Billname tbl
function loadBillname()
{
    global $myBillnameEx;

    // select all data from Billname
    $billnameData = $myBillnameEx->queryByType(0);
    return $billnameData;
}

// select from Billname tbl
function loadBillProperty($billnameid)
{
    global $myBillsettingEx;

    // select all data from Billname
    $billsettingsData = $myBillsettingEx->queryWithBillnameId($billnameid);
    return $billsettingsData;
}

//load sellbill detail
function showDetail($sellbillid)
{
    //to use the variable out side the funcion
    global $mySellbillEx;
    global $mySellbilldetailEx;


    //load purchasebillreturn data by id
    $sellbillData = $mySellbillEx->loadSellBillById($sellbillid);

    $sellbilldetailData = $mySellbilldetailEx->queryWithSellBillId($sellbillid);
    $quantity = 0;
    foreach ($sellbilldetailData as $sellbilldetail) {
        $quantity = $quantity + ($sellbilldetail->sellbilldetailquantity);
    }
    return array($sellbillData, $sellbilldetailData, $quantity);
}

function showsellAndReturnDetail($sellbillid)
{
    global $mySellbillandruternEx;
    global $mySellandruternbilldetailEx;

    //load Sellbillandrutern data by id
    $sellbillandruternData = $mySellbillandruternEx->loadSellbillandruternById($sellbillid);

    $sellbilldetailData = $mySellandruternbilldetailEx->queryWithSellBillIdAndSellType($sellbillid, 0);

    $sellQuantity = 0;
    foreach ($sellbilldetailData as $sellbilldetail) {
        $sellQuantity = $sellQuantity + $sellbilldetail->sellbilldetailquantity;
    }

    $ruternbilldetailData = $mySellandruternbilldetailEx->queryWithSellBillIdAndSellType($sellbillid, 1);

    $returnQuantity = 0;
    foreach ($ruternbilldetailData as $ruternbilldetail) {
        $returnQuantity = $returnQuantity + $ruternbilldetail->sellbilldetailquantity;
    }

    return array($sellbillandruternData, $sellbilldetailData, $ruternbilldetailData, $sellQuantity, $returnQuantity);
}

function showAll()
{
    global $mySellbillEx;
    global $mySellbillandruternEx;
    global $myClientRecord;

    global $mySellandruternbilldetailEx;
    global $mySellbilldetailEx;

    global $ProgramsettingDAO;
    global $myUserRecord;

    global $smarty;




    $startDate = $_REQUEST['from'];
    $endDate = $_REQUEST['to'];
    $sellbillSerial = $_REQUEST['sellbillserial'];
    $sellbillId = $_REQUEST['sellbillid'];
    $clientId = $_REQUEST['clientid'];
    $sellbillcomment = $_REQUEST['sellbillcomment'];

    $message = '';
    $queryString = ' WHERE';
    $returnqueryString = ' WHERE';
    $sallandreturnqueryString = ' WHERE';

    if (isset($clientId) && $clientId != '-1') {
        $messageData = $myClientRecord->load($clientId);
        $message = $message . '<br>' . "إسم العميل: " . $messageData->clientname;

        $queryString .= ' sellbillclientid = ' . $clientId . ' AND';
        $returnqueryString .= ' returnsellbill.returnsellbillclientid = ' . $clientId . ' AND';
        $sallandreturnqueryString .= ' sellbillclientid = ' . $clientId . ' AND';
    }



    $userData = $myUserRecord->load($_SESSION['userid']);

    if ($userData->viewbills == 0) {
        $queryString .= ' client.userid =' . $_SESSION['userid'] . ' AND';
        $sallandreturnqueryString .=  ' client.userid =' . $_SESSION['userid'] . ' AND';
    }

    if (isset($sellbillSerial) && $sellbillSerial != '') {

        showBySriral($sellbillSerial);

        $message = $message . '<br>' . "رقم الفاتورة: " . $sellbillSerial;

        $queryString .= ' sellbillSerial = "' . $sellbillSerial . '" AND';
        $returnqueryString .= ' returnsellbill.returnsellbillserial = "' . $sellbillSerial . '" AND';
        $sallandreturnqueryString .=  ' sellbillserial = "' . $sellbillSerial . '" AND';
    }


    if (isset($sellbillcomment) && $sellbillcomment != '') {

        $message .= $message . "<br> التعليق الفاتورة: " . $sellbillcomment;
        $queryString .=  " sellbill.comment LIKE '%" . $sellbillcomment . "%' AND";
        // $returnqueryString .=  " returnsellbill.comment LIKE '%" . $sellbillcomment . "%' AND ";
        $sallandreturnqueryString .=   " sellbillandrutern.comment LIKE '%" . $sellbillcomment . "%' AND";
    }


    if (isset($sellbillId) && $sellbillId != '') {
        showBySellbillId($sellbillId);
        $message = $message . '<br>' . "الرقم المتسلسل : " . $sellbillId;

        $queryString .= ' sellbillid = "' . $sellbillId . '" AND';
        $sallandreturnqueryString .=  ' sellbillid = "' . $sellbillId . '" AND';
    }

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

        //display the converted time ##reportsPlusHours
        $Programsetting = $ProgramsettingDAO->load(1);
        if (isset($Programsetting->reportsPlusHours) && !empty($Programsetting->reportsPlusHours)) {
            $reportsPlusHours = $Programsetting->reportsPlusHours + 24; //24 to get the end of the day and add search hours to it
            $endDate = date('Y-m-d H:i:s', strtotime('+' . $reportsPlusHours . ' hour +0 minutes', strtotime($endDate)));
            $startDate = date('Y-m-d H:i:s', strtotime('+' . $Programsetting->reportsPlusHours . ' hour +0 minutes', strtotime($startDate)));
        } else {
            $endDatePrepared = $endDate . ' 23:59:59';
            $startDate = $startDate . " 00:00:00";
        }
        $message = $message . '<br>' . 'من تاريخ : ' . $startDate . 'إلى تاريخ : ' . $endDate . '';
        $queryString .= ' sellbilldate >= "' . $startDate . '" AND sellbilldate <= "' . $endDate . '" AND';
        $sallandreturnqueryString .=  ' sellbilldate >= "' . $startDate . '" AND sellbilldate <= "' . $endDate . '" AND';
    } elseif (empty($startDate) && empty($endDate) && empty($sellbillSerial) && empty($sellbillid) && !isset($clientId)) {
        $startDate = $endDate = $today = date("Y-m-d");
        //display the converted time ##reportsPlusHours
        $Programsetting = $ProgramsettingDAO->load(1);
        if (isset($Programsetting->reportsPlusHours) && !empty($Programsetting->reportsPlusHours)) {
            $reportsPlusHours = $Programsetting->reportsPlusHours + 24; //24 to get the end of the day and add search hours to it
            $endDate = date('Y-m-d H:i:s', strtotime('+' . $reportsPlusHours . ' hour +0 minutes', strtotime($endDate)));
            $startDate = date('Y-m-d H:i:s', strtotime('+' . $Programsetting->reportsPlusHours . ' hour +0 minutes', strtotime($startDate)));
        } else {
            $endDatePrepared = $endDate . ' 23:59:59';
            $startDate = $startDate . " 00:00:00";
        }

        $message = $message . '<br>' . "من تاريخ : " . $startDate . " إلى تاريخ : " . $endDate . "";

        $queryString .= ' sellbilldate >= "' . $startDate . '" AND sellbilldate <= "' . $endDate . '" AND';
        $sallandreturnqueryString .=  ' sellbilldate >= "' . $startDate . '" AND sellbilldate <= "' . $endDate . '" AND';
    }
    $smarty->assign("message", $message);

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

    $arrsellandRet = explode(' ', $sallandreturnqueryString);
    if (isset($arrsellandRet) && count($arrsellandRet) > 0) {
        $lastWord = end($arrsellandRet);
        if ($lastWord == 'AND') { //remove it
            array_pop($arrsellandRet);
            //form the string again
            $sallandreturnqueryString = implode(' ', $arrsellandRet);
        } else if ($lastWord == 'WHERE') { //remove it
            array_pop($arrsellandRet);
            $sallandreturnqueryString = ' ';
        }
    }





    $totalBills = 0;
    $totalQTY = 0;
    $totaltax = 0;
    $totaldiscount = 0;

    $sellbillData = $mySellbillEx->querybyqueryString($queryString);
    if (count($sellbillData) > 0) {
        foreach ($sellbillData as $sell) {
            if ($sell->conditions == 0) {
                $totalBills += $sell->sellbillaftertotalbill;
                $totalQTY += $sell->sellQuantity;
                $sell->type = "sell";


                //getalldiscount
                $sellbilldiscount = $sell->sellbilldiscount;
                $sellbilldiscounttype = $sell->sellbilldiscounttype; //1 مباشر
                $sellbilltotalbill = $sell->sellbilltotalbill;
                $sellbillaftertotalbill = $sell->sellbillaftertotalbill;
                $tax = $sell->tax;

                $detaildiscount = $mySellbilldetailEx->getsumdiscount($sell->sellbillid);
                if ($sellbilldiscounttype == 1) {


                    //discount
                    $totaldiscount = $totaldiscount + $sellbilldiscount + $detaildiscount;
                    $sell->sellbilldiscount = ($sellbilldiscount + $detaildiscount);




                    //tax
                    $taxvalue = $sellbillaftertotalbill - ($sellbilltotalbill - ($sellbilldiscount));
                    $totaltax = $totaltax + $taxvalue;
                    $sell->tax = $taxvalue;
                } else {
                    //discount
                    $discountvalue = ($sellbilltotalbill / 100) * $sellbilldiscount;
                    $totaldiscount = $totaldiscount + $discountvalue + $detaildiscount;
                    $sell->sellbilldiscount = ($discountvalue + $detaildiscount);

                    //tax
                    $taxvalue = $sellbillaftertotalbill - ($sellbilltotalbill - ($discountvalue));
                    $totaltax = $totaltax + $taxvalue;
                    $sell->tax = $taxvalue;
                }
            }
        }
    }

    //get sell bills from sellbillandrutern table
    $sellbillandruternData = $mySellbillandruternEx->GetSellBillAndReturnUsingQueryString($sallandreturnqueryString);

    if (count($sellbillandruternData) > 0) {
        foreach ($sellbillandruternData as $sell) {
            if ($sell->conditions == 0) {
                $totalBills += $sell->sellbillprice;//sellbillaftertotalbill;
                $totalQTY += $sell->sellQuantity;
                $sell->type = "sellAndReturn";




                //getalldiscount
                $sellbilldiscount = $sell->sellbilldiscount;
                $sellbilldiscounttype = $sell->sellbilldiscounttype; //1 مباشر
                $sellbilltotalbill = $sell->sellbilltotalbill;
                $sellbillaftertotalbill = $sell->sellbillaftertotalbill;
                $tax = $sell->tax;

                $detaildiscount = $mySellandruternbilldetailEx->getsumdiscount($sell->sellbillid, 0);
                if ($sellbilldiscounttype == 1) {
                    //discount
                    $totaldiscount = $totaldiscount + $sellbilldiscount + $detaildiscount;
                    $sell->sellbilldiscount = ($sellbilldiscount + $detaildiscount);


                    //tax
                    $taxvalue = $sellbillaftertotalbill - ($sellbilltotalbill - ($sellbilldiscount));
                    $totaltax = $totaltax + $taxvalue;
                    $sell->tax = $taxvalue;
                } else {
                    //discount
                    $discountvalue = ($sellbilltotalbill / 100) * $sellbilldiscount;
                    $totaldiscount = $totaldiscount + $discountvalue + $detaildiscount;
                    $sell->sellbilldiscount = ($discountvalue + $detaildiscount);
                    //tax
                    $taxvalue = $sellbillaftertotalbill - ($sellbilltotalbill - ($discountvalue));
                    $totaltax = $totaltax + $taxvalue;
                    $sell->tax = $taxvalue;
                }
            }
        }
    }

    $sellbillData = array_merge($sellbillData, $sellbillandruternData);
    // assign your db results to the template
    $smarty->assign('sellbillData', $sellbillData);
    $smarty->assign('totalBills', $totalBills);
    $smarty->assign('totalQTY', $totalQTY);
    $smarty->assign('totaldiscount', $totaldiscount);
    $smarty->assign('totaltax', $totaltax);
}

/*

  function showAll($startDate, $endDate)
  {
  global $mySellbillEx;
  global $mySellbillandruternEx;
  global $smarty;

  $totalBills = 0;
  $totalQTY = 0;

  $sellbillData =$mySellbillEx->queryWithDate($startDate, $endDate);
  if(count($sellbillData) > 0)
  {
  foreach($sellbillData as $sell)
  {
  if($sell->conditions  == 0)
  {
  $totalBills += $sell->sellbillaftertotalbill;
  $totalQTY += $sell->sellQuantity;
  $sell->type = "sell";
  }
  }
  }

  //get sell bills from sellbillandrutern table
  $sellbillandruternData =$mySellbillandruternEx->querySellBillsOnlyWithDate($startDate,$endDate);

  if(count($sellbillandruternData) > 0)
  {
  foreach($sellbillandruternData as $sell)
  {
  if($sell->conditions  == 0)
  {
  $totalBills += $sell->sellbillaftertotalbill;
  $totalQTY += $sell->sellQuantity;
  $sell->type = "sellAndReturn";
  }
  }
  }

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

  // assign your db results to the template
  $smarty->assign('sellbillData', $sellbillData);
  $smarty->assign('totalBills', $totalBills);
  $smarty->assign('totalQTY', $totalQTY);
  }
 */

function returnshowAll()
{
    global $myReturnsellbillEx;
    global $myReturnsellbilldetailEX;
    global $myReturnsellbillEx;

    global $mySellbillandruternEx;
    global $mySellandruternbilldetailEx;
    global $mySellbilldetailEx;
    global $myClientRecord;
    global $ProgramsettingDAO;

    global $smarty;


    $startDate = $_REQUEST['from'];
    $endDate = $_REQUEST['to'];
    $sellbillSerial = $_REQUEST['sellbillserial'];
    $sellbillId = $_REQUEST['sellbillid'];
    $clientId = $_REQUEST['clientid'];


    $message = '';
    $queryString = ' WHERE';
    $returnqueryString = ' WHERE';


    if (isset($clientId) && $clientId != '-1') {
        $messageData = $myClientRecord->load($clientId);
        $message = $message . '<br>' . "إسم العميل: " . $messageData->clientname;

        $queryString .= ' sellbillandrutern.sellbillclientid = ' . $clientId . ' AND';
        $returnqueryString .= ' returnsellbill.returnsellbillclientid = ' . $clientId . ' AND';
    }

    if (isset($sellbillSerial) && $sellbillSerial != '') {

        //showBySriral($sellbillSerial);

        $message = $message . '<br>' . "رقم الفاتورة: " . $sellbillSerial;

        $queryString .= ' sellbillandrutern.sellbillSerial = "' . $sellbillSerial . '" AND';
        $returnqueryString .= ' returnsellbill.returnsellbillserial = "' . $sellbillSerial . '" AND';
    }

    if (isset($sellbillId) && $sellbillId != '') {
        //	showBySellbillId($sellbillId);
        $message = $message . '<br>' . "الرقم المتسلسل : " . $sellbillId;

        $queryString .= ' sellbillandrutern.sellbillid = "' . $sellbillId . '" AND';
        $returnqueryString .= ' returnsellbill.returnsellbillid = ' . $sellbillId . ' AND';
    }

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



        //display the converted time ##reportsPlusHours
        $Programsetting = $ProgramsettingDAO->load(1);
        if (isset($Programsetting->reportsPlusHours) && !empty($Programsetting->reportsPlusHours)) {
            $reportsPlusHours = $Programsetting->reportsPlusHours + 24; //24 to get the end of the day and add search hours to it
            $endDate = date('Y-m-d H:i:s', strtotime('+' . $reportsPlusHours . ' hour +0 minutes', strtotime($endDate)));
            $startDate = date('Y-m-d H:i:s', strtotime('+' . $Programsetting->reportsPlusHours . ' hour +0 minutes', strtotime($startDate)));
        } else {
            $endDatePrepared = $endDate . ' 23:59:59';
            $startDate = $startDate . " 00:00:00";
        }
        $message = $message . '<br>' . 'من تاريخ : ' . $startDate . 'إلى تاريخ : ' . $endDate . '';

        $queryString .= ' sellbillandrutern.sellbilldate >= "' . $startDate . '" AND sellbillandrutern.sellbilldate <= "' . $endDate . '" AND';
        $returnqueryString .= ' returnsellbill.returnsellbilldate >= "' . $startDate . '" AND returnsellbill.returnsellbilldate <= "' . $endDate . '" AND';
    } elseif (empty($startDate) && empty($endDate) && empty($sellbillSerial) && empty($sellbillid) && !isset($clientId)) {
        $startDate = $endDate = $today = date("Y-m-d");
        //display the converted time ##reportsPlusHours
        $Programsetting = $ProgramsettingDAO->load(1);
        if (isset($Programsetting->reportsPlusHours) && !empty($Programsetting->reportsPlusHours)) {
            $reportsPlusHours = $Programsetting->reportsPlusHours + 24; //24 to get the end of the day and add search hours to it
            $endDate = date('Y-m-d H:i:s', strtotime('+' . $reportsPlusHours . ' hour +0 minutes', strtotime($endDate)));
            $startDate = date('Y-m-d H:i:s', strtotime('+' . $Programsetting->reportsPlusHours . ' hour +0 minutes', strtotime($startDate)));
        } else {
            $endDatePrepared = $endDate . ' 23:59:59';
            $startDate = $startDate . " 00:00:00";
        }

        $message = $message . '<br>' . "من تاريخ : " . $startDate . " إلى تاريخ : " . $endDate . "";

        $queryString .= ' sellbillandrutern.sellbilldate >= "' . $startDate . '" AND sellbillandrutern.sellbilldate <= "' . $endDate . '" AND';
        $returnqueryString .= ' returnsellbill.returnsellbilldate >= "' . $startDate . '" AND returnsellbill.returnsellbilldate <= "' . $endDate . '" AND';
        //	print_r($returnqueryString);
    }
    $smarty->assign("message", $message);


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



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




    $totalBills = 0;
    $totalQTY = 0;
    $totaltax = 0;
    $totaldiscount = 0;


    $returnsellbillData = $myReturnsellbillEx->queryallWithqueryString2($returnqueryString);
    if (count($returnsellbillData) > 0) {
        foreach ($returnsellbillData as $sell) {

            if ($sell->conditions == 0) {
                $totalBills += $sell->returnsellbillaftertotalbill;
                $totalQTY += $sell->returnsellQuantity;
                $sell->type = "returnSell";



                //getalldiscount
                $sellbilldiscount = $sell->returnsellbilldiscount;
                $sellbilldiscounttype = $sell->returnsellbilldiscounttype; //1 مباشر
                $sellbilltotalbill = $sell->returnsellbilltotalbill;
                $sellbillaftertotalbill = $sell->returnsellbillaftertotalbill;
                $tax = $sell->tax;

                $detaildiscount = $myReturnsellbilldetailEX->getsumdiscount($sell->returnsellbillid);
                if ($sellbilldiscounttype == 1) {


                    //discount
                    $totaldiscount = $totaldiscount + $sellbilldiscount + $detaildiscount;
                    $sell->returnsellbilldiscount = ($sellbilldiscount + $detaildiscount);




                    //tax
                    $taxvalue = $sellbillaftertotalbill - ($sellbilltotalbill - ($sellbilldiscount));
                    $totaltax = $totaltax + $taxvalue;
                    $sell->tax = $taxvalue;
                } else {
                    //discount
                    $discountvalue = ($sellbilltotalbill / 100) * $sellbilldiscount;
                    $totaldiscount = $totaldiscount + $discountvalue + $detaildiscount;
                    $sell->returnsellbilldiscount = ($discountvalue + $detaildiscount);

                    //tax
                    $taxvalue = $sellbillaftertotalbill - ($sellbilltotalbill - ($discountvalue));
                    $totaltax = $totaltax + $taxvalue;
                    $sell->tax = $taxvalue;
                }
            }
        }
    }

    //get return sell bills from sellbillandrutern table
    $sellbillandruternData = $mySellbillandruternEx->GetSellBillAndReturnUsingQueryString($queryString);
    //print_r($sellbillandruternData);

    if (count($sellbillandruternData) > 0) {
        foreach ($sellbillandruternData as $sell) {
            if ($sell->conditions == 0) {
                $totalBills += $sell->returnsellbillprice; //sellbillaftertotalbill;
                $sell->returnsellbillid = $sell->sellbillid;
                $sell->returnsellbillaftertotalbill = $sell->sellbillaftertotalbill;
                $sell->returnsellbilldate = $sell->sellbilldate;
                $totalQTY += $sell->returnsellQuantity;
                $sell->type = "sellAndReturn";



                //getalldiscount
                $sellbilldiscount = $sell->sellbilldiscount;
                $sellbilldiscounttype = $sell->sellbilldiscounttype; //1 مباشر
                $sellbilltotalbill = $sell->sellbilltotalbill;
                $sellbillaftertotalbill = $sell->sellbillaftertotalbill;
                $tax = $sell->tax;

                $detaildiscount = $mySellandruternbilldetailEx->getsumdiscount($sell->sellbillid, 1);
                if ($sellbilldiscounttype == 1) {
                    //discount
                    $totaldiscount = $totaldiscount + $detaildiscount;
                    $sell->sellbilldiscount = $detaildiscount;


                    //tax
                    $taxvalue = 0; //$sellbillaftertotalbill-($sellbilltotalbill-($sellbilldiscount));
                    $totaltax = $totaltax + $taxvalue;
                    $sell->tax = $taxvalue;
                } else {
                    //discount
                    //$discountvalue=($sellbilltotalbill/100)*$sellbilldiscount;
                    $totaldiscount = $totaldiscount + $detaildiscount;
                    $sell->sellbilldiscount = $detaildiscount;
                    //tax
                    $taxvalue = 0; //$sellbillaftertotalbill-($sellbilltotalbill-($discountvalue));
                    $totaltax = $totaltax + $taxvalue;
                    $sell->tax = $taxvalue;
                }
            }
        }
    }

    $returnsellbillData = array_merge($returnsellbillData, $sellbillandruternData);
    // assign your db results to the template
    $smarty->assign('returnsellbillData', $returnsellbillData);
    $smarty->assign('returntotalBills', $totalBills);
    $smarty->assign('returntotalQTY', $totalQTY);
    $smarty->assign('returntotaltax', $totaltax);
    $smarty->assign('returntotaldiscount', $totaldiscount);
}

function showByClient($clientId)
{
    global $mySellbillEx;
    global $mySellbillandruternEx;
    global $smarty;

    $totalBills = 0;
    $totalQTY = 0;


    //get sell bills from sellbill table
    $sellbillData = $mySellbillEx->queryWithClientId($clientId);
    if (count($sellbillData) > 0) {
        foreach ($sellbillData as $sell) {
            if ($sell->conditions == 0) {
                $totalBills += $sell->sellbillaftertotalbill;
                $totalQTY += $sell->sellQuantity;
                $sell->type = "sell";
            }
        }
    }

    //get sell bills from sellbillandrutern table
    $sellbillandruternData = $mySellbillandruternEx->querySellBillsOnlyWithClientId($clientId);

    if (count($sellbillandruternData) > 0) {
        foreach ($sellbillandruternData as $sell) {
            if ($sell->conditions == 0) {
                $totalBills += $sell->sellbillaftertotalbill;
                $totalQTY += $sell->sellQuantity;
                $sell->type = "sellAndReturn";
            }
        }
    }
    $sellbillData = array_merge($sellbillData, $sellbillandruternData);

    // assign your db results to the template
    $smarty->assign('sellbillData', $sellbillData);
    $smarty->assign('totalBills', $totalBills);
    $smarty->assign('totalQTY', $totalQTY);
}

function showBySriral($serial)
{
    global $mySellbillEx;
    global $mySellbillandruternEx;
    global $smarty;

    $totalBills = 0;
    $totalQTY = 0;

    //get sell bills from sellbill table
    $sellbillData = $mySellbillEx->queryWithSerail($serial);
    if (count($sellbillData) > 0) {
        foreach ($sellbillData as $sell) {
            if ($sell->conditions == 0) {
                $totalBills += $sell->sellbillaftertotalbill;
                $totalQTY += $sell->sellQuantity;
                $sell->type = "sell";
            }
        }
    }

    //get sell bills from sellbillandrutern table
    $sellbillandruternData = $mySellbillandruternEx->querySellBillsOnlyWithSerail($serial);

    if (count($sellbillandruternData) > 0) {
        foreach ($sellbillandruternData as $sell) {
            if ($sell->conditions == 0) {
                $totalBills += $sell->sellbillaftertotalbill;
                $totalQTY += $sell->sellQuantity;
                $sell->type = "sellAndReturn";
            }
        }
    }
    $sellbillData = array_merge($sellbillData, $sellbillandruternData);

    // assign your db results to the template
    $smarty->assign('sellbillData', $sellbillData);
    $smarty->assign('totalBills', $totalBills);
    $smarty->assign('totalQTY', $totalQTY);
}

function showBySellbillId($sellbillId)
{
    global $mySellbillEx;
    global $mySellbillandruternEx;
    global $smarty;

    $totalBills = 0;
    $totalQTY = 0;

    $sellbillData = $mySellbillEx->queryWithSellbillId($sellbillId);
    if (count($sellbillData) > 0) {
        foreach ($sellbillData as $sell) {
            if ($sell->conditions == 0) {
                $totalBills += $sell->sellbillaftertotalbill;
                $totalQTY += $sell->sellQuantity;
                $sell->type = "sell";
            }
        }
    }

    //get sell bills from sellbillandrutern table
    $sellbillandruternData = $mySellbillandruternEx->querySellBillsOnlyWithSellbillid($sellbillId);

    if (count($sellbillandruternData) > 0) {
        foreach ($sellbillandruternData as $sell) {
            if ($sell->conditions == 0) {
                $totalBills += $sell->sellbillaftertotalbill;
                $totalQTY += $sell->sellQuantity;
                $sell->type = "sellAndReturn";
            }
        }
    }
    $sellbillData = array_merge($sellbillData, $sellbillandruternData);
    // assign your db results to the template
    $smarty->assign('sellbillData', $sellbillData);
    $smarty->assign('totalBills', $totalBills);
    $smarty->assign('totalQTY', $totalQTY);
}

function showByDate($startDate, $endDate)
{


    global $mySellbillEx;
    global $mySellbillandruternEx;
    global $smarty;

    $totalBills = 0;
    $totalQTY = 0;

    $sellbillData = $mySellbillEx->queryWithDate($startDate, $endDate);
    if (count($sellbillData) > 0) {
        foreach ($sellbillData as $sell) {
            if ($sell->conditions == 0) {
                $totalBills += $sell->sellbillaftertotalbill;
                $totalQTY += $sell->sellQuantity;
                $sell->type = "sell";
            }
        }
    }

    //get sell bills from sellbillandrutern table
    $sellbillandruternData = $mySellbillandruternEx->querySellBillsOnlyWithDate($startDate, $endDate);

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

            if ($sell->conditions == 0) {
                $totalBills += $sell->sellbillaftertotalbill;
                $totalQTY += $sell->sellQuantity;
                $sell->type = "sellAndReturn";
            }
        }
    }
    $sellbillData = array_merge($sellbillData, $sellbillandruternData);

    // assign your db results to the template
    $smarty->assign('sellbillData', $sellbillData);
    $smarty->assign('totalBills', $totalBills);
    $smarty->assign('totalQTY', $totalQTY);
}
