<?php

$do = isset($_GET['do']) ? $_GET['do'] : "";
$ajaxDoArr = array("showallajaxsell", "showallajaxsellCash", "showallajaxsellBook", "showallajaxret", "showallajaxretCash", "showallajaxretBook", "reviewUnReview", "print", "reviewhistory");
if (!in_array($do, $ajaxDoArr)) {
    //the global file operation
    include("../public/impOpreation.php");
}

// get the config file
include_once("../public/config.php");
include_once("dailyentryfun.php");
//here the db files that include in the file
include("../public/include_dao.php");


//here the db files that include in the file
include("reportfunctions.php");
include_once("../library/num_to_ar.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');
//Usergroup
require_once('../models/dao/UsergroupDAO.class.php');
require_once('../models/dto/Usergroup.class.php');
require_once('../models/mysql/UsergroupMySqlDAO.class.php');
require_once('../models/mysql/ext/UsergroupMySqlExtDAO.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');


//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');
//Billsproduct
require_once('../models/dao/BillsproductsDAO.class.php');
require_once('../models/dto/Billsproduct.class.php');
require_once('../models/mysql/BillsproductsMySqlDAO.class.php');
require_once('../models/mysql/ext/BillsproductsMySqlExtDAO.class.php');
//
require_once('../models/dao/BillsreturnDAO.class.php');
require_once('../models/dto/Billsreturn.class.php');
require_once('../models/mysql/BillsreturnMySqlDAO.class.php');
require_once('../models/mysql/ext/BillsreturnMySqlExtDAO.class.php');

require_once('../models/dao/BillsreturnproductsDAO.class.php');
require_once('../models/dto/Billsreturnproduct.class.php');
require_once('../models/mysql/BillsreturnproductsMySqlDAO.class.php');
require_once('../models/mysql/ext/BillsreturnproductsMySqlExtDAO.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');

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

//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');
//Returnsellbillreview
require_once('../models/dao/ReturnsellbillreviewDAO.class.php');
require_once('../models/dto/Returnsellbillreview.class.php');
require_once('../models/mysql/ReturnsellbillreviewMySqlDAO.class.php');
require_once('../models/mysql/ext/ReturnsellbillreviewMySqlExtDAO.class.php');
//Sellbillandreturnreview
require_once('../models/dao/SellbillandreturnreviewDAO.class.php');
require_once('../models/dto/Sellbillandreturnreview.class.php');
require_once('../models/mysql/SellbillandreturnreviewMySqlDAO.class.php');
require_once('../models/mysql/ext/SellbillandreturnreviewMySqlExtDAO.class.php');
//Sellbillreview
require_once('../models/dao/SellbillreviewDAO.class.php');
require_once('../models/dto/Sellbillreview.class.php');
require_once('../models/mysql/SellbillreviewMySqlDAO.class.php');
require_once('../models/mysql/ext/SellbillreviewMySqlExtDAO.class.php');
//typeClient
require_once('../models/dao/TypeClientDAO.class.php');
require_once('../models/dto/TypeClient.class.php');
require_once('../models/mysql/TypeClientMySqlDAO.class.php');
require_once('../models/mysql/ext/TypeClientMySqlExtDAO.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');

//$do = $_GET['do'];

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

  Controller Name :- storedetailCTRL تقرير مخزون اول مدة

  OPERTATION in Controller

  1-load save data
  2-display show form

  ======================== */
//here the global templates
if (!in_array($do, $ajaxDoArr)) {
    $smarty->display("header.html");
}
//here goes the instances and general variables
//user
$user = new User();
$userDAO = new UserMySqlDAO();
$userEX = new UserMySqlExtDAO();

//Usergroup
$Usergroup = new Usergroup();
$UsergroupDAO = new UsergroupMySqlDAO();
$UsergroupEX = new UsergroupMySqlExtDAO();

//Sellbill
$sellBill = new Sellbill();
$sellBillDAO = new SellbillMySqlDAO();
$sellBillEX = new SellbillMySqlExtDAO();
//Sellbilldetail
$sellbilldetail = new Sellbilldetail();
$sellbilldetailDAO = new SellbilldetailMySqlDAO();
$sellbilldetailEX = new SellbilldetailMySqlExtDAO();
//Returnsellbill
$returnSellBill = new Returnsellbill();
$returnSellBillDAO = new ReturnsellbillMySqlDAO();
$returnSellBillEX = new ReturnsellbillMySqlExtDAO();
//Returnsellbilldetail
$returnSellBillDetail = new Returnsellbilldetail();
$returnSellBillDetailDAO = new ReturnsellbilldetailMySqlDAO();
$returnSellBillDetailEX = new ReturnsellbilldetailMySqlExtDAO();
//Sellandruternbill
$sellAndRuternBill = new Sellbillandrutern();
$sellAndRuternBillDAO = new SellbillandruternMySqlDAO();
$sellAndRuternBillEX = new SellbillandruternMySqlExtDAO();
//Sellandruternbilldetail
$sellAndRuternBillDetail = new Sellandruternbilldetail();
$sellAndRuternBillDetailDAO = new SellandruternbilldetailMySqlDAO();
$sellAndRuternBillDetailEX = new SellandruternbilldetailMySqlExtDAO();
##################################
//bill
$bills = new Bill();
$billsDAO = new BillsMySqlDAO();
$billsEX = new BillsMySqlExtDAO();

//billsproducts
$billsProducts = new Billsproduct();
$billsProductsDAO = new BillsproductsMySqlDAO();
$billsProductsEX = new BillsproductsMySqlExtDAO();

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

//billsproducts
$billsReturnProducts = new Billsreturnproduct();
$billsReturnProductsDAO = new BillsreturnproductsMySqlDAO();
$billsReturnProductsEX = new BillsreturnproductsMySqlExtDAO();

///////////////product///////////////
$ProductDAO = new ProductMySqlDAO();
$Product = new Product();
$ProductEX = new ProductMySqlExtDAO();


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

//Client
$client = new Client();
$clientDAO = new ClientMySqlDAO();
$clientEX = new ClientMySqlExtDAO();

$mySaveRecord = new SaveMySqlDAO();
$SaveExt = new SaveMySqlExtDAO();

//Programsetting
$ProgramsettingDAO = new ProgramsettingsMySqlDAO();

//Sellbillreview
$sellBillReview = new Sellbillreview();
$sellBillReviewDAO = new SellbillreviewMySqlDAO();
$sellBillReviewEX = new SellbillreviewMySqlExtDAO();
//Returnsellbillreview
$returnSellBillReview = new Returnsellbillreview();
$returnSellBillReviewDAO = new ReturnsellbillreviewMySqlDAO();
$returnSellBillReviewEX = new ReturnsellbillreviewMySqlExtDAO();
//Sellbillandreturnreview
$sellBillAndReturnReview = new Sellbillandreturnreview();
$sellBillAndReturnReviewDAO = new SellbillandreturnreviewMySqlDAO();
$sellBillAndReturnReviewEX = new SellbillandreturnreviewMySqlExtDAO();
//
//typeClient
$type = new TypeClient();
$typeClientDAO = new TypeClientMySqlDAO();
$typeClientEX = new TypeClientMySqlExtDAO();
//Clientdebtchange
$clientDeptChange = new Clientdebtchange();
$clientDeptChangeDAO = new ClientdebtchangeMySqlDAO();
$clientDeptChangeExt = new ClientdebtchangeMySqlExtDAO();

//Dailyentry
$dailyEntry = new Dailyentry();
$dailyEntryDAO = new DailyentryMySqlDAO();
$dailyEntryEX = new DailyentryMySqlExtDAO();

//Dailyentrycreditor دائن
$dailyEntryCreditor = new Dailyentrycreditor();
$dailyEntryCreditorDAO = new DailyentrycreditorMySqlDAO();
$dailyEntryCreditorEX = new DailyentrycreditorMySqlExtDAO();

//Dailyentrydebtor مدين
$dailyEntryDebtor = new Dailyentrydebtor();
$dailyEntryDebtorDAO = new DailyentrydebtorMySqlDAO();
$dailyEntryDebtorEX = new DailyentrydebtorMySqlExtDAO();

//Accountstree
$accountsTree = new Accountstree();
$accountsTreeDAO = new AccountstreeMySqlDAO();
$accountsTreeEX = new AccountstreeMySqlExtDAO();

//savedaily
$Savedaily = new Savedaily();
$SavedailyDAO = new SavedailyMySqlDAO();
$SavedailyExt = new SavedailyMySqlExtDAO();

//bankaccount
$bankAccount = new Bankaccount();
$bankAccountDAO = new BankaccountMySqlDAO();
$bankAccountEX = new BankaccountMySqlExtDAO();

//Account Movement
$accountMoveDAO = new AccountmovementMySqlDAO;
$accountMove = new Accountmovement;

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

$Programsettingdata = $Programsetting = $ProgramsettingDAO->load(1);
$smarty->assign("Programsettingdata", $Programsettingdata);
$Usergroupdata = $UsergroupDAO->load($_SESSION['usergroupid']);
$smarty->assign("Usergroupdata", $Usergroupdata);
//check and use the condition that suite this action
if (empty($do)) {
    //here the permission check
    include_once("../public/authentication.php");

    ##########################################################################
    //prepare search tools
    ##clients
    //    $clients = $clientDAO->queryByCondition(0);
    //    $smarty->assign("clients", $clients);

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

    $user = $userDAO->load($_SESSION['userid']);
    $smarty->assign("user", $user);
    if ($user->userstoreid == 0) {
        $storeData = loadStore();
        $smarty->assign("storeData", $storeData);
    }
    if ($user->branchId == 0) {
        $branchData = loadBranch();
        $smarty->assign("branchData", $branchData);
    }
    $sellerData = loadseller();
    $smarty->assign("sellerData", $sellerData);
    $userData = loadusers();
    $smarty->assign("userData", $userData);
    ###########################################################################
    //search
    $day = filter_input(INPUT_POST, 'day');
    if (isset($day) && !empty($day)) {
        $datefrom = $dateto = $day;
        $storeId = filter_input(INPUT_POST, 'storeIdS');
        $branchId = filter_input(INPUT_POST, 'branchIdS');
        $sellerid = filter_input(INPUT_POST, 'selleridS');
        $userid = filter_input(INPUT_POST, 'useridS');

        $clientid = filter_input(INPUT_POST, 'clientidS');
        $billid = filter_input(INPUT_POST, 'billidS');
        $billserial = filter_input(INPUT_POST, 'billserialS');
        $billtype = filter_input(INPUT_POST, 'billtypeS');
        $obgyBillType = filter_input(INPUT_POST, 'obgyBillTypeS');

        $searchtype = 0;
    } else {
        $datefrom = filter_input(INPUT_POST, 'datefrom');
        $dateto = filter_input(INPUT_POST, 'dateto');

        $storeId = filter_input(INPUT_POST, 'storeId');
        $branchId = filter_input(INPUT_POST, 'branchId');
        $sellerid = filter_input(INPUT_POST, 'sellerid');
        $userid = filter_input(INPUT_POST, 'userid');

        $clientid = filter_input(INPUT_POST, 'clientid');
        $billid = filter_input(INPUT_POST, 'billid');
        $billserial = filter_input(INPUT_POST, 'billserial');
        $billtype = filter_input(INPUT_POST, 'billtype');
        $obgyBillType = filter_input(INPUT_POST, 'obgyBillType');

        $searchtype = filter_input(INPUT_POST, 'searchtype');
        if (!isset($searchtype) || empty($searchtype)) {
            $searchtype = 0;
        }
    }

    if (isset($clientid) && $clientid == 0) {
        $clientid = '';
        foreach ($clients as $value) {
            $clientid .= $value->clientid . ",";
        }
        $clientid = rtrim($clientid, ",");
    }
    $queryString = " where 1 "; //bills
    $queryStringR = " where 1 "; //billsreturn
    $queryString1 = " where 1 "; //sellbill
    $queryString1R = " where 1 "; //sellbillreturn
    $queryString1SR = " where 1 "; //sellbillandreturn
    ##date
    if (isset($datefrom) && !empty($datefrom) && isset($dateto) && !empty($dateto) && $dateto < $datefrom) {
        $datefrom = $dateto = $today;
        $queryString .= 'and  date(bills.billdate)  = "' . $today . '" ';
        $queryStringR .= 'and  date(billsreturn.`date`)  = "' . $today . '" ';
        $queryString1 .= 'and  date(sellbill.sellbilldate)  = "' . $today . '" ';
        $queryString1R .= 'and  date(returnsellbill.returnsellbilldate)  = "' . $today . '" ';
        $queryString1SR .= 'and  date(sellbillandrutern.sellbilldate)  = "' . $today . '" ';
    } else {
        if (isset($datefrom) && !empty($datefrom)) {
            $queryString .= 'and  date(bills.billdate)  >= "' . $datefrom . '" ';
            $queryStringR .= 'and  date(billsreturn.`date`)  >= "' . $datefrom . '" ';
            $queryString1 .= 'and  date(sellbill.sellbilldate)  >= "' . $datefrom . '" ';
            $queryString1R .= 'and  date(returnsellbill.returnsellbilldate)  >= "' . $datefrom . '" ';
            $queryString1SR .= 'and  date(sellbillandrutern.sellbilldate)  >= "' . $datefrom . '" ';
        }
        if (isset($dateto) && !empty($dateto)) {
            $queryString .= 'and  date(bills.billdate)  <= "' . $dateto . '" ';
            $queryStringR .= 'and   date(billsreturn.`date`)  <= "' . $dateto . '" ';
            $queryString1 .= 'and  date(sellbill.sellbilldate)  <= "' . $dateto . '" ';
            $queryString1R .= 'and  date(returnsellbill.returnsellbilldate)  <= "' . $dateto . '" ';
            $queryString1SR .= 'and  date(sellbillandrutern.sellbilldate)  <= "' . $dateto . '" ';
        }
    }
    ##storeId
    if (isset($storeId) && !empty($storeId) && $storeId != -1) {
        if ($user->userstoreid == 0) {
            //$queryString .= 'and  bills.sellbillstoreid  = ' . $storeId . ' ';
            $queryString1 .= 'and  sellbill.sellbillstoreid  = ' . $storeId . ' ';
            $queryString1R .= 'and  returnsellbill.returnsellbillstoreid  = ' . $storeId . ' ';
            $queryString1SR .= 'and  sellbillandrutern.sellbillstoreid  = ' . $storeId . ' ';
        } else {
            //$queryString .= 'and  bills.sellbillstoreid  = ' . $user->userstoreid . ' ';
            $queryString1 .= 'and  sellbill.sellbillstoreid  = ' . $user->userstoreid . ' ';
            $queryString1R .= 'and  returnsellbill.returnsellbillstoreid  = ' . $user->userstoreid . ' ';
            $queryString1SR .= 'and  sellbillandrutern.sellbillstoreid  = ' . $user->userstoreid . ' ';
        }
    }
    ##branch
    if (isset($branchId) && !empty($branchId) && $branchId != -1) {
        if ($user->branchId == 0) {
            $queryString .= 'and  bills.branchid  = ' . $branchId . ' ';
            $queryStringR .= 'and  billsreturn.branchid  = ' . $branchId . ' ';
            //$queryString1 .= 'and  sellbill.sellbilldate  = ' . $branchId . ' ';
        } else {
            $queryString .= 'and  bills.branchid  = ' . $user->branchId . ' ';
            $queryStringR .= 'and  billsreturn.branchid  = ' . $user->branchId . ' ';
            //$queryString1 .= 'and  sellbill.sellbilldate  = ' . $user->branchId . ' ';
        }
    }
    ##sellerid
    if (isset($sellerid) && !empty($sellerid) && $sellerid != -1) {
        //$queryString .= 'and  bills.billdate  = "' . $dateto . '" ';
        $queryString1 .= 'and  sellbill.sellerid  = "' . $sellerid . '" ';
        $queryString1R .= 'and  returnsellbill.sellerid  = "' . $sellerid . '" ';
        $queryString1SR .= 'and  sellbillandrutern.sellerid  = "' . $sellerid . '" ';
    }
    ##userid
    if (isset($userid) && !empty($userid) && $userid != -1) {
        $queryString .= 'and  bills.userid  = ' . $userid . ' ';
        $queryStringR .= 'and  billsreturn.userid  = ' . $userid . ' ';
        $queryString1 .= 'and  sellbill.userid  = ' . $userid . ' ';
        $queryString1R .= 'and  returnsellbill.userid  = ' . $userid . ' ';
        $queryString1SR .= 'and  sellbillandrutern.userid  = ' . $userid . ' ';
    }

    ##clientid
    if (isset($clientid) && !empty($clientid) && $clientid != -1) {
        $queryString .= 'and  bills.clientid  IN ( ' . $clientid . ') ';
        $queryStringR .= 'and  bills.clientid  IN ( ' . $clientid . ') ';
        $queryString1 .= 'and  sellbill.sellbillclientid  IN ( ' . $clientid . ') ';
        $queryString1R .= 'and  returnsellbill.returnsellbillclientid  IN ( ' . $clientid . ') ';
        $queryString1SR .= 'and  sellbillandrutern.sellbillclientid  IN ( ' . $clientid . ') ';
    }
    ##billid
    if (isset($billid) && !empty($billid) && $billid != -1) {
        $queryString .= 'and  bills.id  = ' . $billid . ' ';
        $queryStringR .= 'and  billsreturn.id  = ' . $billid . ' ';
        $queryString1 .= 'and  sellbill.sellbillid  = ' . $billid . ' ';
        $queryString1R .= 'and  returnsellbill.returnsellbillid  = ' . $billid . ' ';
        $queryString1SR .= 'and  sellbillandrutern.sellbillid  = ' . $billid . ' ';
    }
    ##billserial
    if (isset($billserial) && !empty($billserial) && $billserial != -1) {
        $queryString .= 'and  bills.billno  = "' . $billserial . '" ';
        $queryStringR .= 'and  bills.billno  = "' . $billserial . '" ';
        $queryString1 .= 'and  sellbill.sellbillserial  = "' . $billserial . '" ';
        $queryString1R .= 'and  returnsellbill.returnsellbillserial  = "' . $billserial . '" ';
        $queryString1SR .= 'and  sellbillandrutern.sellbillserial  = "' . $billserial . '" ';
    }

    if (isset($billtype) && !empty($billtype) && $billtype != -1) {
        switch ($billtype) {
            case 1: //مبيعات فقط
                $queryString = "stop"; //bills
                $queryStringR = "stop"; //billsreturn
                $queryString1 .= " "; //sellbill
                $queryString1R = "stop"; //sellbillreturn
                $queryString1SR = "stop"; //sellbillandreturn
                break;
            case 2: //مرتجعات فقط
                $queryString = "stop"; //bills
                $queryStringR = "stop"; //billsreturn
                $queryString1 = "stop"; //sellbill
                $queryString1R .= " "; //sellbillreturn
                $queryString1SR = "stop"; //sellbillandreturn
                break;
            case 3: //مبيعات و مرتجعات
                $queryString = "stop"; //bills
                $queryStringR = "stop"; //billsreturn
                $queryString1 = "stop"; //sellbill
                $queryString1R = "stop"; //sellbillreturn
                $queryString1SR .= " "; //sellbillandreturn
                break;
            case 4: //مبيعات بصريات
                $queryString .= " "; //bills
                $queryStringR = "stop"; //billsreturn
                $queryString1 = "stop"; //sellbill
                $queryString1R = "stop"; //sellbillreturn
                $queryString1SR = "stop"; //sellbillandreturn
                break;
            case 5: //مرتجعات بصريات
                $queryString = "stop"; //bills
                $queryStringR .= " "; //billsreturn
                $queryString1 = "stop"; //sellbill
                $queryString1R = "stop"; //sellbillreturn
                $queryString1SR = "stop"; //sellbillandreturn
                break;

            default:
                break;
        }
    }
    if (isset($obgyBillType) && $obgyBillType > 0) {
        $queryString1 .= 'and  sellbill.obgyBillType  = ' . $obgyBillType . ' ';
        $queryString1R .= 'and  returnsellbill.obgyBillType  = ' . $obgyBillType . ' ';
    }
    $smarty->assign("storeIdS", $storeId);
    $smarty->assign("branchIdS", $branchId);
    $smarty->assign("selleridS", $sellerid);
    $smarty->assign("useridS", $userid);
    $smarty->assign("clientidS", $clientid);
    $smarty->assign("billidS", $billid);
    $smarty->assign("billserialS", $billserial);
    $smarty->assign("billtypeS", $billtype);
    $smarty->assign("obgyBillTypeS", $obgyBillType);


    getData($queryString, $queryString1, $queryStringR, $queryString1R, $queryString1SR, $searchtype, $datefrom, $dateto, $sellerid);

    $smarty->display("salesreportbillsview/show.html");
} elseif ($do == "showbills") {
    ##########################################################################
    //prepare search tools
    ##clients
    //    $clients = $clientDAO->queryByCondition(0);
    //    $smarty->assign("clientData", $clients);

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

    $user = $userDAO->load($_SESSION['userid']);
    $smarty->assign("user", $user);
    $usergroup = $UsergroupDAO->load($_SESSION['usergroupid']);
    $smarty->assign("usergroup", $usergroup);
    //    if ($user->userstoreid == 0) {
    $storeData = loadStore();
    $smarty->assign("stores", $storeData);
    //    }
    if ($user->branchId == 0) {
        $branchData = loadBranch();
        $smarty->assign("branchData", $branchData);
    }
    $sellerData = loadseller();
    $smarty->assign("sellerData", $sellerData);
    $userData = loadusers();
    $smarty->assign("sellerinsertData", $userData);
    $saves = $mySaveRecord->queryAll();
    $smarty->assign("saves", $saves);
    ###########################################################################
    $smarty->display("salesreportbillsview/showBills.html");
} elseif ($do == "billReservation") {
    $futureEndDate = date('Y-m-d', strtotime("+2 days"));
    //display the converted time ##reportsPlusHours
    /* if (isset($Programsettingdata->reportsPlusHours) && !empty($Programsettingdata->reportsPlusHours)) {
      $reportsPlusHours = $Programsettingdata->reportsPlusHours + 24; //24 to get the end of the day and add search hours to it
      $futureEndDate = date('Y-m-d H:i:s', strtotime('+' . $reportsPlusHours . ' hour +0 minutes', strtotime($futureEndDate)));
      } else {
      $futureEndDate = $futureEndDate . ' 23:59:59';
      } */
    $youtubes = $youtubeLinkDAO->queryAll();
    $smarty->assign("youtubes", $youtubes);


    $query = ' where billReservation = 1 and billReservationDate <= "' . $futureEndDate . '" ';

    $allSellBill = $sellBillEX->queryAllGeneral($query);
    $allSellBillAndReturn = $sellAndRuternBillEX->queryAllGeneral($query);
    $allReturnSellBill = $returnSellBillEX->queryAllGeneral_f($query);
    $smarty->assign("allSellBill", $allSellBill);
    $smarty->assign("allSellBillAndReturn", $allSellBillAndReturn);
    $smarty->assign("allReturnSellBill", $allReturnSellBill);
    $smarty->display("salesreportbillsview/billReservation.html");
} elseif ($do == "reviewbills") {
    ##########################################################################
    //prepare search tools
    ##clients
//    $clients = $clientDAO->queryByCondition(0);
//    $smarty->assign("clientData", $clients);
    $typeClient = $typeClientDAO->queryAll();
    $smarty->assign("typeClient", $typeClient);

    $user = $userDAO->load($_SESSION['userid']);
    $smarty->assign("user", $user);
    $usergroup = $UsergroupDAO->load($_SESSION['usergroupid']);
    $usergroup->reviewBillsAllowedTables = explode(",", $usergroup->reviewBillsAllowedTables);
    $smarty->assign("usergroup", $usergroup);
    //    if ($user->userstoreid == 0) {
    $storeData = loadStore();
    $smarty->assign("stores", $storeData);
    //    }
    if ($user->branchId == 0) {
        $branchData = loadBranch();
        $smarty->assign("branchData", $branchData);
    }
    $sellerData = loadseller();
    $smarty->assign("sellerData", $sellerData);
    $userData = loadusers();
    $smarty->assign("sellerinsertData", $userData);

    if ($user->searchinonesave == 0) {
        if ($user->saveids == 0) {
            $saves = $mySaveRecord->queryByConditions(0);
        } else {
            $queryString = ' and saveid in (' . $user->saveids . ')';
            $saves = $mySaveEx->queryWithConditionWithQueryString($queryString);
        }
    } else {
        $queryString = ' and saveid in (' . $user->saveid . ')';
        $saves = $mySaveEx->queryWithConditionWithQueryString($queryString);
    }

    $visaAccounts = R::getAll('SELECT bankaccount.*, bank.bankname
			 FROM bankaccount JOIN bank
			 ON bankaccount.bankid = bank.bankid
			 where bank.conditions = 0 and bankaccount.conditions = 0 and bankaccount.visa = 1
			  order by bankaccount.bankid asc');
    $smarty->assign("visaAccounts", $visaAccounts);
    /* -- get today value -------------------- */
    $today = date('Y-m-d');
    $yesterday = date("Y-m-d", strtotime('-1 days'));
    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
        $endToday = date('Y-m-d H:i:s', strtotime('+' . $reportsPlusHours . ' hour +0 minutes', strtotime($today)));
        $startToday = date('Y-m-d H:i:s', strtotime('+' . $Programsetting->reportsPlusHours . ' hour +0 minutes', strtotime($today)));
        //
        $endYesterday = date('Y-m-d H:i:s', strtotime('+' . $reportsPlusHours . ' hour +0 minutes', strtotime($yesterday)));
        $startYesterday = date('Y-m-d H:i:s', strtotime('+' . $Programsetting->reportsPlusHours . ' hour +0 minutes', strtotime($yesterday)));
    } else {
        $endToday = $today . ' 23:59:59';
        $startToday = $today . " 00:00:00";
        //
        $endYesterday = $today . ' 23:59:59';
        $startYesterday = $today . " 00:00:00";
    }
    if (date('H') < $Programsetting->reportsPlusHours) {
        $startDate = $startYesterday;
        $endDate = $endYesterday;
    } else {
        $startDate = $startToday;
        $endDate = $endToday;
    }
    ##############
    $smarty->assign("startDateTime", $startDate);
    $smarty->assign("endDateTime", $endDate);
    $smarty->assign("startDate", date('Y-m-d'));
    $smarty->assign("endDate", date('Y-m-d'));
    /* -- END OF get today value -------------------- */


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



    $smarty->assign("saves", $saves);
    ###########################################################################
    $smarty->display("salesreportbillsview/reviewBills.html");
} elseif ($do == "showallajaxsell") {
    showallajaxsell();
} elseif ($do == "showallajaxsellCash") {
    showallajaxsellCash();
} elseif ($do == "showallajaxsellBook") {
    showallajaxsellBook();
} elseif ($do == "showallajaxret") {
    showallajaxret();
} elseif ($do == "showallajaxretCash") {
    showallajaxretCash();
} elseif ($do == "showallajaxretBook") {
    showallajaxretBook();
} elseif ($do == "printReceipt") {
    #
    $billId = (int) $_GET['bill'];
    $type = (int) $_GET['type']; //0 sell , 1 ret
    $moneyPayed = (float) $_GET['m'];
    $choice = ($_GET['ch'] == "all") ? "all" : "current"; //choice of payment print
    $oldPayments = (float) $_GET['op'];
    if ($choice == "all") {
        $moneyPayed += $oldPayments;
    }
    $serviceMoneyPaid = (float) $_GET['s'];
    $totalpaid = $moneyPayed + $serviceMoneyPaid;
    $ar_number = new convert_ar($totalpaid, "male");
    $totalWritten = $ar_number->convert_number();
    $totalWritten .= ' ' . $Programsettingdata->currancy . ' فقط لاغير';
    $smarty->assign('totalWritten', $totalWritten);

    ##get client name
    if ($type == 0) {
        $clientid = R::getCell('select sellbillclientid from sellbill where sellbillid=' . $billId);
    } elseif ($type == 1) {
        $clientid = R::getCell('select returnsellbillclientid from returnsellbill where returnsellbillid=' . $billId);
    }

    if ($Programsettingdata->laterNakdiClientId == $clientid) {
        $clientName = "نقدا";
    } else {
        $clientName = R::getCell('select clientname from client where clientid=' . $clientid);
    }

    $smarty->assign("billId", $billId);
    $smarty->assign("moneyPayed", $moneyPayed);
    $smarty->assign("serviceMoneyPaid", $serviceMoneyPaid);
    $smarty->assign("type", $type);
    $smarty->assign("clientName", $clientName);
    ###
    $todayDate = date('Y-m-d');
    //    $todayTime = date('H:i:s');
    $todayTime = date('h:i A', strtotime(date('Y-m-d H:i:s')));
    $smarty->assign("todayDate", $todayDate);
    $smarty->assign("todayTime", $todayTime);


    $smarty->display("salesreportbillsview/printReceipt.html");
} elseif ($do == "reviewUnReview") {
    $review = (int) filter_input(INPUT_POST, 'review'); //coming with 0=> its new,1=>reviwed,3=>reviwed then canceled,4=>reviwed then canceled then reviwed
    $idsSell = filter_input(INPUT_POST, 'idsSell');
    $idsRet = filter_input(INPUT_POST, 'idsRet');
    $password = filter_input(INPUT_POST, 'pass');

    $paydebt = (int) filter_input(INPUT_POST, 'paydebt'); //its flag => 1 means client will pay dept
    $oneClientId = (int) filter_input(INPUT_POST, 'oneClientId'); //the client paying debt
    $paysaveid = (float) filter_input(INPUT_POST, 'paysaveid');
    $payed = (float) filter_input(INPUT_POST, 'payed');
    $payaccountid = (float) filter_input(INPUT_POST, 'payaccountid');
    $visapayed = (float) filter_input(INPUT_POST, 'visapayed');
    $reviewComment = filter_input(INPUT_POST, 'comment');

    $paymentId = 0;
    /* //get table name to insert data in
      $tableName = '';
      switch ($billtype) {
      case "sell":
      $tableName = 'sellbillreview';
      break;
      case "retsell":
      $tableName = 'returnsellbillreview';
      break;
      case "sellAndRet":
      $tableName = 'sellbillandreturnreview';
      break;
      } */
    //
    $userHasPrivilage = 1;
    if ($review == 1) {
        if ($Programsettingdata->unReviewBills == 0 && $Programsettingdata->unReviewBillsPass == $password) {
            //$userHasPrivilage = 1;
        } else {
            $userHasPrivilage = 0;
        }
    }

    if ($userHasPrivilage == 1) {
        //        //$review is current review. we are changing it now
        //        if ($review == 0) {//it is now not reviewd,so review it
        //            $review = 1;
        //        } elseif ($review == 1) {//it is now reviewd,so mark as cancel review
        //            $review = 3;
        //        }
        //prepare id
        $idsSell = rtrim($idsSell, ',');
        $idsRet = rtrim($idsRet, ',');
        $idsSell = explode(',', $idsSell);
        $idsRet = explode(',', $idsRet);
        if (count($idsSell) > 0 || count($idsRet) > 0) {
            ##1- mark review or unreview
            //
            $tableName = 'sellbillreview';
            foreach ($idsSell as $billid) {
                if (!empty((int) $billid)) {
                    $obj->billid = (int) $billid;
                    $obj->reviewType = $review;
                    $obj->reviewComment = $reviewComment;
                    $obj->reviewuserid = $_SESSION['userid'];
                    $obj->sysdate = date("Y-m-d H:i:s");
                    $obj->del = 0;
                    $reviewid = $sellBillReviewEX->insertEX($tableName, $obj);
                    //$sellBillEX->updateLastReview($obj->reviewType, $obj->reviewuserid, $obj->sysdate, $obj->billid);
                    $sellBillEX->updateLastReviewAutoHandle($obj->reviewuserid, $obj->sysdate, $obj->billid);
                }
            }
            //
            $tableName = 'returnsellbillreview';
            foreach ($idsRet as $billid) {
                if (!empty((int) $billid)) {
                    $obj->billid = (int) $billid;
                    $obj->reviewType = $review;
                    $obj->reviewComment = $reviewComment;
                    $obj->reviewuserid = $_SESSION['userid'];
                    $obj->sysdate = date("Y-m-d H:i:s");
                    $obj->del = 0;
                    $reviewid = $sellBillReviewEX->insertEX($tableName, $obj);
                    //$returnSellBillEX->updateLastReview($obj->reviewType, $obj->reviewuserid, $obj->sysdate, $obj->billid);
                    $returnSellBillEX->updateLastReviewAutoHandle($obj->reviewuserid, $obj->sysdate, $obj->billid);
                }
            }
            #2- do payment
            if ($paydebt == 1 && $oneClientId > 0 && ($payed > 0 || $visapayed > 0)) {
                $add_data = payClientDept($oneClientId, $payed, $visapayed, $paysaveid, $payaccountid);
                $paymentId = $add_data[1];
            }
            echo "1,$paymentId";
        } else {
            echo -1;
        }
    } else {
        echo -2; //wrong password for unreview bill
    }
} elseif ($do == "print") {
    $clientdebtchangeId = isset($_GET['payment']) ? (int) $_GET['payment'] : 0;
    if ($clientdebtchangeId > 0) {
        $smarty->assign("clientdebtchangeId", $clientdebtchangeId);
        $clientsData = $clientDAO->queryByCondition(0);
        $smarty->assign("clientsData", $clientsData);
        $clientdebtchangeData = $clientDeptChangeDAO->load($clientdebtchangeId);
        $smarty->assign("clientdebtchangeData", $clientdebtchangeData);
    }


    $logoImageSrc = "../views/default/images/upload/$Programsettingdata->logo";
    $userData = $userDAO->load($_SESSION['userid']);
    $smarty->assign("userData", $userData);
    $smarty->assign("now", date('Y-m-d H:i:s'));
    if ($userData->branchId != 0 && $userData->branchId != -1) {
        $branchData = $BranchDAO->load($userData->branchId);
        if (isset($branchData->logo) && !empty($branchData->logo) && $branchData->logo != "." && file_exists("../views/default/images/branch_image/$branchData->logo")) {
            $logoImageSrc = "../views/default/images/branch_image/$branchData->logo";
        }
        //$smarty->assign("branchData", $branchData);
    }
    $smarty->assign("logoImageSrc", $logoImageSrc);

    if ($clientdebtchangeId > 0) {
        $smarty->display("salesreportbillsview/printPayment.html");
    } else {
        $smarty->display("salesreportbillsview/print.html");
    }
} elseif ($do == "reviewhistory") {
    $id = (int) filter_input(INPUT_POST, 'id');
    $type = filter_input(INPUT_POST, 'type');
    if ($id > 0) {
        switch ($type) {
            case 'sell':
                $result = $sellBillReviewEX->queryByBillidDetailedOrdered($id);

                break;
            case 'ret':
                $result = $returnSellBillReviewEX->queryByBillidDetailedOrdered($id);

                break;

            default:
                break;
        }
        $smarty->assign('result', $result);
        $smarty->display("salesreportbillsview/reviewhistory.html");
    }
}
#
elseif ($do == "laterNakdiPayment" || $do == "laterNakdiPaymentSell" || $do == "laterNakdiPaymentRet") {
    ##########################################################################
    //prepare search tools
    $user = $userDAO->load($_SESSION['userid']);
    $smarty->assign("user", $user);
    $defUserSaveName = R::getCell('select savename from save where saveid = ' . (int) $user->saveid);
    $smarty->assign("defUserSaveName", $defUserSaveName);
    $storeData = loadStore();
    $smarty->assign("stores", $storeData);

    if ($user->branchId == 0) {
        $branchData = loadBranch();
        $smarty->assign("branchData", $branchData);
    }
    $sellerData = loadseller();
    $smarty->assign("sellerData", $sellerData);
    $saves = $mySaveRecord->queryAll();

    /* -- get today value -------------------- */
    $today = date('Y-m-d');
    $yesterday = date("Y-m-d", strtotime('-1 days'));
    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
        $endToday = date('Y-m-d H:i:s', strtotime('+' . $reportsPlusHours . ' hour +0 minutes', strtotime($today)));
        $startToday = date('Y-m-d H:i:s', strtotime('+' . $Programsetting->reportsPlusHours . ' hour +0 minutes', strtotime($today)));
        //
        $endYesterday = date('Y-m-d H:i:s', strtotime('+' . $reportsPlusHours . ' hour +0 minutes', strtotime($yesterday)));
        $startYesterday = date('Y-m-d H:i:s', strtotime('+' . $Programsetting->reportsPlusHours . ' hour +0 minutes', strtotime($yesterday)));
    } else {
        $endToday = $today . ' 23:59:59';
        $startToday = $today . " 00:00:00";
        //
        $endYesterday = $today . ' 23:59:59';
        $startYesterday = $today . " 00:00:00";
    }
    if (date('H') < $Programsetting->reportsPlusHours) {
        $startDate = $startYesterday;
        $endDate = $endYesterday;
    } else {
        $startDate = $startToday;
        $endDate = $endToday;
    }
    ##############
    $smarty->assign("startDateTime", $startDate);
    $smarty->assign("endDateTime", $endDate);
    $smarty->assign("startDate", date('Y-m-d'));
    $smarty->assign("endDate", date('Y-m-d'));
    /* -- END OF get today value -------------------- */


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



    $smarty->assign("saves", $saves);
    $smarty->assign("do", $do);
    ###########################################################################
    $smarty->display("salesreportbillsview/laterNakdiPayment.html");
}

//here the global templates
if (!in_array($do, $ajaxDoArr)) {
    $smarty->display("footer.html");
}

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

function getData($queryString, $queryString1, $queryStringR, $queryString1R, $queryString1SR, $searchtype, $datefrom, $dateto, $sellerid) {
    global $billsProductsEX;
    global $billsReturnProductsEX;
    global $sellbilldetailEX;
    global $returnSellBillDetailEX;
    global $sellAndRuternBillDetailEX;
    global $ProductDAO;
    global $ProductEX;
    global $billsEX;
    global $billsReturnEX;
    global $sellBillEX;
    global $returnSellBillEX;
    global $sellAndRuternBillEX;
    global $smarty;
    global $billsDAO;
    global $ProgramsettingDAO;

    class billData {

        public $billId;
        public $billserial = "";
        public $billno;
        public $totalbill = 0;
        public $noOfpieces = 0;
        public $datetime;
        public $billdiscount = 0;
        public $networkDiscount = 0;
        public $billProfit = 0;
        public $clientName;
        public $sellerName;
        public $userName;
        public $billURL;
        public $billtype;

    }

    $Programsettingdata = $ProgramsettingDAO->load(1);

    $allDataArr = array();
    $totalsObj = new billData();

    $today = date("Y-m-d");
    ##if no search make it today
    if ($queryString == " where 1 ") {
        $queryString .= 'and  date(bills.billdate)  = "' . $today . '" ';
    }
    if ($queryStringR == " where 1 ") {
        $queryStringR .= 'and  date(billsreturn.`date`)  = "' . $today . '" ';
    }
    if ($queryString1 == " where 1 ") {
        $queryString1 .= 'and  sellbill.sellbilldate  = "' . $today . '" ';
    }
    if ($queryString1R == " where 1 ") {
        $queryString1R .= 'and  returnsellbill.returnsellbilldate  = "' . $today . '" ';
    }
    if ($queryString1SR == " where 1 ") {
        $queryString1SR .= 'and  sellbillandrutern.sellbilldate  = "' . $today . '" ';
    }
    ############################################################################
    ##run queries
    $billsData;
    $billsDataReturn;
    $sellBillData;
    $sellBillDataReturn;
    $sellBillDataSellAndReturn;
    if ($queryString != "stop") {
        $billsData = $billsEX->queryAllGeneral($queryString);
    }
    if ($queryStringR != "stop") {
        $billsDataReturn = $billsReturnEX->queryAllGeneral($queryStringR);
    }
    if ($queryString1 != "stop") {

        //$sellBillData = $sellBillEX->queryAllGeneral($queryString1);

        $sellBillData = $sellBillEX->queryAllGeneral_1($queryString1, $sellerid);
    }
    if ($queryString1R != "stop") {
        $sellBillDataReturn = $returnSellBillEX->queryAllGeneral($queryString1R);
    }
    if ($queryString1SR != "stop") {
        $sellBillDataSellAndReturn = $sellAndRuternBillEX->queryAllGeneral($queryString1SR);
    }
    $resultsCount = count($billsData) + count($billsDataReturn) + count($sellBillData) + count($sellBillDataReturn) + count($sellBillDataSellAndReturn);
    $smarty->assign("resultsCount", $resultsCount);


    if ($resultsCount > 0) {

        ##مبيعات البصريات
        foreach ($billsData as $value) {
            $myBill = new billData();
            $myBill->billtype = "مبيعات بصريات";
            $myBill->billId = $value->id;
            $myBill->billno = $value->id;
            $myBill->billserial = $value->billno;
            $myBill->totalbill = $value->finalnetbillvalue;
            $myBill->datetime = $value->billdate;
            $myBill->clientName = $value->clientname;
            $myBill->userName = $value->username;
            $myBill->sellerName = "";
            $myBill->billURL = "bills.php?do=details&id=" . $myBill->billId;

            $myBill->billdiscount = $value->discountvalue;
            $myBill->networkDiscount = 0;
            if ($value->card == 1) {
                if ($value->paymentnetworkid == 4) {
                    if ($searchtype == 1) {
                        //مدى
                        $madaData = $billsEX->queryTotalNetworkReportMadaSimple($value->billdate);
                        if ($madaData->totalCarry < 5000)
                            $myBill->networkDiscount = (7 * $madaData->totalCarry) / 1000;
                        else
                            $myBill->networkDiscount = 40;
                    }
                } else {
                    $myBill->networkDiscount = ($value->cardvalue * $value->netdiscountpercent) / 100;
                }
            }

            $billDetail = $billsProductsEX->queryAllGeneral(" and billid=" . $myBill->billId . " ");

            $productsCost = 0;
            $myBill->noOfpieces = 0;
            foreach ($billDetail as $mybillDetail) {
                $myBill->noOfpieces += $mybillDetail->productno;
                $productsCost += ($mybillDetail->deleted * $mybillDetail->productno);
            }

            $myBill->billProfit = ($value->netbillvalue - $value->discountvalue) - $productsCost;


            array_push($allDataArr, $myBill);
        }
        ##مرتجعات البصريات
        foreach ($billsDataReturn as $value) {
            $myBill = new billData();
            $billData = $billsDAO->load($value->id);
            $myBill->billtype = "مرتجعات بصريات";
            $myBill->billId = $value->id;
            $myBill->billno = $value->id;
            $myBill->billserial = $billData->billno;

            $myBill->datetime = $value->date;
            $myBill->clientName = $value->clientname;
            $myBill->userName = $value->username;
            $myBill->sellerName = "";
            $myBill->billURL = "billsreturn.php?do=details&id=" . $myBill->billId;

            $myBill->billdiscount = $value->discountvalue;
            $myBill->networkDiscount = 0;

            $billDetail = $billsReturnProductsEX->queryAllGeneral(" and returnbillid=" . $myBill->billId . " ");
            $myBill->totalbill = $value->returnedprice;
            $productsCost = 0;
            $myBill->noOfpieces = 0;
            foreach ($billDetail as $mybillDetail) {
                $myBill->noOfpieces += $mybillDetail->productno;
                $productsCost += ($mybillDetail->deleted * $mybillDetail->productno);
            }

            $myBill->billProfit = abs($value->returnedprice - $productsCost) * -1;



            array_push($allDataArr, $myBill);
        }
        ##فاتورة المبيعات
        foreach ($sellBillData as $value) {
            $myBill = new billData();
            $myBill->billtype = "فاتورة المبيعات";
            $myBill->billId = $value->sellbillid;
            $myBill->billno = $value->sellbillid;
            $myBill->billserial = $value->sellbillserial;
            $myBill->totalbill = $value->sellbilltotalbill;
            $myBill->datetime = $value->sellbilldate;
            $myBill->clientName = $value->clientname;
            $myBill->userName = $value->employeename;
            $myBill->sellerName = $value->useremployeename;
            $myBill->billURL = "sellbillController.php?do=showDetail&sellbillid=" . $myBill->billId;

            $myBill->billdiscount = 0;
            if ($value->sellbilldiscount > 0) {
                if ($value->sellbilldiscounttype == 1) {
                    $myBill->billdiscount = $value->sellbilldiscount;
                } elseif ($value->sellbilldiscounttype == 2) {
                    $myBill->billdiscount = $value->sellbilltotalbill - $value->sellbillaftertotalbill;
                }
            }
            $myBill->networkDiscount = 0;

            $billDetail = $sellbilldetailEX->queryAllGeneral(" and sellbilldetail.sellbillid=" . $myBill->billId . " ");

            $productsCost = 0;
            $myBill->noOfpieces = 0;
            foreach ($billDetail as $mybillDetail) {

                #########get quantity بالقطعة
                $quantity = $mybillDetail->sellbilldetailquantity;
                $productId = $mybillDetail->sellbilldetailproductid;
                $productunitId = $mybillDetail->productunitid;
                $productunitData = loadProductUnitWithProductAndUnit($productId, $productunitId);
                $productnumber = $productunitData->productnumber;
                $finalquantity = $quantity * $productnumber;
                $myproduct->buyPrice = $value->storeid;

                switch ($Programsettingdata->Profitevaluation) {
                    case "first":
                        $buyprice = (float) $mybillDetail->buyprice;
                        break;
                    case "last":
                        $buyprice = (float) $mybillDetail->lastbuyprice;
                        break;
                    case "mean":
                        $buyprice = (float) $mybillDetail->meanbuyprice;
                        break;
                    case "last_discount":
                        $buyprice = (float) $mybillDetail->lastbuyprice_withDiscount;
                        break;
                    case "mean_discount":
                        $buyprice = (float) $mybillDetail->meanbuyprice_withDiscount;
                        break;
                    case "generalPrice":
                        $buyprice = (float) $mybillDetail->generalPrice;
                        break;
                    case "tax":
                        $buyprice = (float) $mybillDetail->lastbuyprice_withTax;
                        break;
                    case "mean_tax":
                        $buyprice = (float) $mybillDetail->meanbuyprice_withTax;
                        break;
                }
                ##############
                $myBill->noOfpieces += $finalquantity;
                //                $productsCost += $mybillDetail->storeid * $finalquantity;
                $productsCost += $buyprice * $finalquantity;
            }

            $myBill->billProfit = $value->sellbillaftertotalbill - $productsCost;

            array_push($allDataArr, $myBill);
        }
        ##فاتورة مردود المبيعات
        foreach ($sellBillDataReturn as $value) {
            $myBill = new billData();
            $myBill->billtype = "فاتورة مردود المبيعات";
            $myBill->billId = $value->returnsellbillid;
            $myBill->billno = $value->returnsellbillid;
            $myBill->billserial = $value->returnsellbillserial;
            $myBill->totalbill = $value->returnsellbilltotalbill;
            $myBill->datetime = $value->returnsellbilldate;
            $myBill->clientName = $value->clientname;
            $myBill->userName = $value->employeename;
            $myBill->sellerName = $value->useremployeename;
            $myBill->billURL = "returnsellbillController.php?do=showDetail&returnsellbillid=" . $myBill->billId;

            $myBill->billdiscount = 0;
            if ($value->returnsellbilldiscount > 0) {
                if ($value->sellbilldiscounttype == 1) {
                    $myBill->billdiscount = $value->returnsellbilldiscount;
                } elseif ($value->sellbilldiscounttype == 2) {
                    $myBill->billdiscount = $value->returnsellbilltotalbill - $value->returnsellbillaftertotalbill;
                }
            }
            $myBill->networkDiscount = 0;

            $billDetail = $returnSellBillDetailEX->queryAllGeneral(" and returnsellbilldetail.returnsellbillid=" . $myBill->billId . " ");

            $productsCost = 0;
            $myBill->noOfpieces = 0;
            foreach ($billDetail as $mybillDetail) {

                #########get quantity بالقطعة
                $quantity = $mybillDetail->returnsellbilldetailquantity;
                $productId = $mybillDetail->returnsellbilldetailproductid;
                $productunitId = $mybillDetail->productunitid;
                $productunitData = loadProductUnitWithProductAndUnit($productId, $productunitId);
                $productnumber = $productunitData->productnumber;
                $finalquantity = $quantity * $productnumber;
                $myproduct->buyPrice = $value->storeid;

                switch ($Programsettingdata->Profitevaluation) {
                    case "first":
                        $buyprice = (float) $mybillDetail->buyprice;
                        break;
                    case "last":
                        $buyprice = (float) $mybillDetail->lastbuyprice;
                        break;
                    case "mean":
                        $buyprice = (float) $mybillDetail->meanbuyprice;
                        break;
                    case "last_discount":
                        $buyprice = (float) $mybillDetail->lastbuyprice_withDiscount;
                        break;
                    case "mean_discount":
                        $buyprice = (float) $mybillDetail->meanbuyprice_withDiscount;
                        break;
                    case "generalPrice":
                        $buyprice = (float) $mybillDetail->generalPrice;
                        break;
                    case "tax":
                        $buyprice = (float) $mybillDetail->lastbuyprice_withTax;
                        break;
                    case "mean_tax":
                        $buyprice = (float) $mybillDetail->meanbuyprice_withTax;
                        break;
                }
                ##############
                $myBill->noOfpieces += $finalquantity;
                //                $productsCost += $mybillDetail->storeid * $finalquantity;
                $productsCost += $buyprice * $finalquantity;
            }

            $myBill->billProfit = $value->returnsellbillaftertotalbill - $productsCost;


            array_push($allDataArr, $myBill);
        }
        ##فاتورة مبيعات و مردود
        foreach ($sellBillDataSellAndReturn as $value) {
            $myBill = new billData();
            $myBill->billtype = "فاتورة مبيعات و مردود";
            $myBill->billId = $value->sellbillid;
            $myBill->billno = $value->sellbillid;
            $myBill->billserial = $value->sellbillserial;
            $myBill->totalbill = $value->sellbilltotalbill;
            $myBill->datetime = $value->sellbilldate;
            $myBill->clientName = $value->clientname;
            $myBill->userName = $value->employeename;
            $myBill->sellerName = $value->useremployeename;
            $myBill->billURL = "sellbillandruternController.php?do=showDetail&sellbillid=" . $myBill->billId;

            $myBill->billdiscount = 0;
            if ($value->sellbilldiscount > 0) {
                if ($value->sellbilldiscounttype == 1) {
                    $myBill->billdiscount = $value->sellbilldiscount;
                } elseif ($value->sellbilldiscounttype == 2) {
                    $myBill->billdiscount = $value->sellbilltotalbill - $value->sellbillaftertotalbill;
                }
            }
            $myBill->networkDiscount = 0;

            $billDetail = $sellAndRuternBillDetailEX->queryAllGeneral(" and sellandruternbilldetail.sellbillid=" . $myBill->billId . " ");

            $productsCost = 0;
            $myBill->noOfpieces = 0;
            foreach ($billDetail as $mybillDetail) {

                #########get quantity بالقطعة
                $quantity = $mybillDetail->sellbilldetailquantity;
                $productId = $mybillDetail->sellbilldetailproductid;
                $productunitId = $mybillDetail->productunitid;
                $productunitData = loadProductUnitWithProductAndUnit($productId, $productunitId);
                $productnumber = $productunitData->productnumber;
                $finalquantity = $quantity * $productnumber;
                ##############

                switch ($Programsettingdata->Profitevaluation) {
                    case "first":
                        $buyprice = (float) $mybillDetail->buyprice;
                        break;
                    case "last":
                        $buyprice = (float) $mybillDetail->lastbuyprice;
                        break;
                    case "mean":
                        $buyprice = (float) $mybillDetail->meanbuyprice;
                        break;
                    case "last_discount":
                        $buyprice = (float) $mybillDetail->lastbuyprice_withDiscount;
                        break;
                    case "mean_discount":
                        $buyprice = (float) $mybillDetail->meanbuyprice_withDiscount;
                        break;
                    case "generalPrice":
                        $buyprice = (float) $mybillDetail->generalPrice;
                        break;
                    case "tax":
                        $buyprice = (float) $mybillDetail->lastbuyprice_withTax;
                        break;
                    case "mean_tax":
                        $buyprice = (float) $mybillDetail->meanbuyprice_withTax;
                        break;
                }

                if ($value->selltype == 0) {
                    $myBill->noOfpieces += $finalquantity;
                    $productsCost += abs($buyprice * $finalquantity);
                } else {
                    $myBill->noOfpieces -= $finalquantity;
                    $productsCost -= abs($buyprice * $finalquantity) * -1;
                }
            }

            $myBill->billProfit = $value->sellbillaftertotalbill - $productsCost;


            array_push($allDataArr, $myBill);
        }
        #############finally get net , real cost ,net profit
        foreach ($allDataArr as $data) {
            $totalsObj->totalbill += $data->totalbill;
            $totalsObj->noOfpieces += $data->noOfpieces;
            $totalsObj->billdiscount += $data->billdiscount;
            $totalsObj->networkDiscount += $data->networkDiscount;
            $totalsObj->billProfit += $data->billProfit;
        }
        $smarty->assign("totalsObj", $totalsObj);

        if ($searchtype == 1) {
            $allDataArr = unifyDate($allDataArr, $datefrom, $dateto);
        }
    }


    $smarty->assign("searchtype", $searchtype);
    $smarty->assign("allDataArr", $allDataArr);
}

function unifyDate($allDataArr, $datefrom, $dateto) {
    $myArr = array();
    $existDate = array();
    foreach ($allDataArr as $value) {
        if (in_array($value->datetime, $existDate)) {
            $key = array_search($value->datetime, $existDate);
            $myObj = $myArr[$key];

            $myObj->totalbill += $value->totalbill;
            $myObj->billdiscount += $value->billdiscount;
            $myObj->noOfpieces += $value->noOfpieces;
            $myObj->networkDiscount += $value->networkDiscount;
            $myObj->billProfit += $value->billProfit;
            //$myObj->billURL .= '<a href="' . $value->billURL . '" target="_blank" style="color: blue;margin-right:5px"><u>' . $value->billId . '</u></a>';
        } else {
            array_push($existDate, $value->datetime);
            //$value->billURL = '<a href="' . $value->billURL . '" target="_blank" style="color: blue;margin-right:5px"><u>' . $value->billId . '</u></a>';
            array_push($myArr, $value);
        }
    }
    $allDataArr = $myArr;
    return $allDataArr;
}

/* ---------------------------------- */

function showallajaxsell() {
    global $sellBillEX;
    global $ProgramsettingDAO;
    global $UsergroupDAO;

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

    $sIndexColumn = "sellbillid";

    /* DB table to use */
    //stable what is after from in sql statment
    ##this was right statment but slow
    $sTable = " sellbill
                    LEFT JOIN client ON sellbill.sellbillclientid = client.clientid
                    LEFT JOIN user ON user.userid = sellbill.lastReviewUser
                    LEFT JOIN user billuser ON billuser.userid = sellbill.userid";

    //    $sTable = " sellbill
    //                LEFT JOIN client ON sellbill.sellbillclientid = client.clientid
    //                left join sellbillreview on (sellbillreview.billid = sellbill.sellbillid and sellbillreview.id in(
    //                    SELECT  MAX(id)
    //                    FROM sellbillreview
    //                    GROUP BY billid
    //                ))";
    ##this was giving wrong data
    //    $sTable = " sellbill
    //                LEFT JOIN client ON sellbill.sellbillclientid = client.clientid
    //                left join (
    //                    SELECT  sellbillreview.*,MAX(id) as lastrecordid
    //                    FROM sellbillreview
    //                    GROUP BY billid
    //                ) as lastreview on (lastreview.billid = sellbill.sellbillid and lastreview.id = lastrecordid)";
    $aColumns = array('sellbillid', 'clientname', 'sellbillaftertotalbill', 'sellbilltotalpayed', 'sellbilldate', 'billuser.employeename as useremployeename', 'lastReviewDateTime', 'user.employeename', 'lastReviewType', 'tempclientName', 'sellbillclientid');

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

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

    /*
     * Ordering
     */
    $sOrder = "";

    if (isset($_POST['order'][0])) {
        $sOrder = "ORDER BY  ";
        for ($i = 0; $i < intval($_POST['columns']); $i++) {
            $orderByColumnIndex = $_POST['order'][0]['column'] - 1; //this - 1 is because first column is not counted as it is checkbox
            if ($aColumns[$orderByColumnIndex] == "billuser.employeename as useremployeename") {
                $sOrder .= "billuser.employeename
                " . ($_POST['order'][$i]['dir'] === 'asc' ? 'asc' : 'desc') . ", ";
            } else {
                $sOrder .= $aColumns[intval($_POST['columns'][$orderByColumnIndex]['data'])] . "
                " . ($_POST['order'][$i]['dir'] === 'asc' ? 'asc' : 'desc') . ", ";
            }
        }
        $sOrder = substr_replace($sOrder, "", -2);
        if ($sOrder == "ORDER BY ") {
            $sOrder = "";
        }
    }

    /*
     * Filtering
     * NOTE this does not match the built-in DataTables filtering which does it
     * word by word on any field. It's possible to do here, but concerned about efficiency
     * on very large tables, and MySQL's regex functionality is very limited
     */
    // where del =0 and aqarstatus =0 and updatestatus in(1,2,3,4) and complete=1
    $sWhere = '';
    if (isset($_POST['search']['value']) && $_POST['search']['value'] != "") {
        $sWhere = " where  (";
        for ($i = 0; $i < count($aColumns); $i++) {
            if ($aColumns[$i] == "sellbilldate" || $aColumns[$i] == "lastReviewDateTime") {
                //to get numbers and english chars only
                ##only alpha numeric is allowed . space not allowed
                $search = preg_replace('/[^#A-Za-z0-9]/', '', $_POST['search']['value']); // Removes special chars.
                if (!empty($search)) {
                    $sWhere .= $aColumns[$i] . " LIKE '%" . $search . "%' OR ";
                }
            } else if ($aColumns[$i] == "billuser.employeename as useremployeename") {
                $sWhere .= " billuser.employeename LIKE '%" . $_POST['search']['value'] . "%' OR ";
            } else {
                $sWhere .= $aColumns[$i] . " LIKE '%" . $_POST['search']['value'] . "%' OR ";
            }
        }
        $sWhere = substr_replace($sWhere, "", -3);
        $sWhere .= ")";
    }
    //-- search ------------------------------------------------------------
    if ($sWhere == '') {
        $queryString = ' WHERE';
    } else {
        $queryString = ' AND';
    }

    $message = "";
    $reviewType = $_REQUEST['reviewType'];
    $startDate = $_REQUEST['from'];
    $endDate = $_REQUEST['to'];
    $tabDateGuide = $_REQUEST['tabDateGuide'];
    $sellbillSerial = $_REQUEST['sellbillserial'];
    $sellbillId = $_REQUEST['sellbillid'];
    $clientId = (int) $_REQUEST['clientid'];
    $sellerid = $_REQUEST['sellerid'];
    $selleruserid = $_REQUEST['selleruserid'];
    $storeid = $_REQUEST['storeid'];
    //    $pricetype = $_REQUEST['pricetype'];
    $saveid = $_REQUEST['saveid'];
    $tempclientName = $_REQUEST['tempclientName'];
    $cashOnlyNotClient = (int) $_REQUEST['cashOnlyNotClient']; //1 for nakdi "clientid=1",0 for normal clients "clientid > 1",-1 for all kinds "no conditions"
    $typeClient = (int) $_REQUEST['typeClient'];
    $laterNakdiPayment = (int) $_REQUEST['laterNakdiPayment'];

    $queryString .= ' sellbill.conditions = 0 AND'; ///
    if (isset($reviewType)) {
        if ($reviewType == 0 || $reviewType == 3) {
            $queryString .= ' lastReviewType in (0,3) AND';
        } else {
            $queryString .= ' lastReviewType in (1,4) AND';
        }
    }
    if ($laterNakdiPayment == 1) {
        $userIds = R::getCell('select laterNakdiUserIds from user where userid=' . $_SESSION['userid']);
        if ($userIds != 0)
            $queryString .= ' sellbill.userid in(' . $userIds . ') AND';
    }

    /////
    if ($cashOnlyNotClient == 0) { //ignore -1 case
        $queryString .= ' sellbill.sellbillclientid != 1 AND';
    } elseif ($cashOnlyNotClient == 1) {
        $queryString .= ' sellbill.sellbillclientid = 1 AND';
    }
    if (isset($clientId) && $clientId > 0) {
        $queryString .= ' sellbill.sellbillclientid = ' . $clientId . ' AND';
    }
    ##
    if ($laterNakdiPayment == 1) { ///for eng.sherif i think for one user @reports laterNakdiPayment , laterNakdiPaymentSell , laterNakdiPaymentRet
        $queryString .= ' client.typeclientid not like "%,1,%" AND';
    } else if (isset($typeClient) && $typeClient != -1) {
        $queryString .= ' (client.typeclientid like "%,' . $typeClient . ',%" or client.typeclientid = ",-20," or client.typeclientid = "," or client.typeclientid = "") AND';
    }
    ##
    if (isset($sellerid) && $sellerid != "-1") {
        $queryString .= ' sellbill.sellerid = ' . $sellerid . ' AND';
    }
    if (isset($selleruserid) && $selleruserid != "-1") {
        $queryString .= ' sellbill.userid = ' . $selleruserid . ' AND';
    }
    if (isset($sellbillSerial) && $sellbillSerial != "") {
        $queryString .= ' sellbill.sellbillserial = "' . $sellbillSerial . '" AND';
    }
    if (isset($sellbillId) && $sellbillId != "") {
        $queryString .= ' sellbill.sellbillid = ' . $sellbillId . ' AND';
    }
    if (isset($storeid) && $storeid != "-1") {
        $queryString .= ' sellbill.sellbillstoreid = ' . $storeid . ' AND';
    }
    //    if (isset($pricetype) && $pricetype != "-1") {
    //        $queryString .= ' pricetype = ' . $pricetype . ' AND';
    //    }
    if (isset($saveid) && $saveid != "-1") {
        $queryString .= ' sellbill.sellbillsaveid = ' . $saveid . ' AND';
    }
    if (isset($tempclientName) && $tempclientName != "") {
        $queryString .= ' sellbill.tempclientName like "%' . $tempclientName . '%" AND';
    }
    ##needed Data
    $Programsetting = $ProgramsettingDAO->load(1);
    //$usergroup = $UsergroupDAO->load($_SESSION['usergroupid']);
    ##############
    ##fatma
    $search = $_REQUEST['searchDate'];
    if ($search == "null" || empty($search)) {
        $search = "dateOnly";
    }
    /* if (isset($tabDateGuide) && !empty($tabDateGuide)) {
      //display the converted time ##reportsPlusHours
      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
      $endDateToday = date('Y-m-d H:i:s', strtotime('+' . $reportsPlusHours . ' hour +0 minutes', strtotime($today)));
      $startDateToday = date('Y-m-d H:i:s', strtotime('+' . $Programsetting->reportsPlusHours . ' hour +0 minutes', strtotime($today)));
      } else {
      $endDateToday = $endDateToday . ' 23:59:59';
      $startDateToday = $startDateToday . " 00:00:00";
      }
      //}
      if ($tabDateGuide == "today") {
      $queryString .= ' sellbill.sellbilldate >= "' . $startDateToday . '" and sellbill.sellbilldate <= "' . $endDateToday . '" AND';
      } elseif ($tabDateGuide == "beforeToday") {
      $queryString .= ' sellbill.sellbilldate < "' . $startDateToday . '" AND';
      }
      } */
    if (isset($startDate) && $startDate != "" && isset($endDate) && $endDate != "") { //&& $tabDateGuide != "today"
        if ($search == "dateOnly") {
            //display the converted time ##reportsPlusHours
            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 {
                $endDate = $endDate . ' 23:59:59';
                $startDate = $startDate . " 00:00:00";
            }
        }
        $queryString .= ' sellbill.sellbilldate >= "' . $startDate . '" and sellbill.sellbilldate <= "' . $endDate . '" AND';
    }


    $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);
        }
        if ($lastWord == 'WHERE') { //remove it
            array_pop($arr);
            //form the string again
            $queryString = implode(' ', $arr);
        }

        //print("<br>queryString: ".$queryString."<br>");
    }

    $totalBills = 0;

    if ($queryString == "") {
        //display the converted time ##reportsPlusHours
        ################
        $today = date('Y-m-d');
        $yesterday = date("Y-m-d", strtotime('-1 days'));
        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
            $endToday = date('Y-m-d H:i:s', strtotime('+' . $reportsPlusHours . ' hour +0 minutes', strtotime($today)));
            $startToday = date('Y-m-d H:i:s', strtotime('+' . $Programsetting->reportsPlusHours . ' hour +0 minutes', strtotime($today)));
            //
            $endYesterday = date('Y-m-d H:i:s', strtotime('+' . $reportsPlusHours . ' hour +0 minutes', strtotime($yesterday)));
            $startYesterday = date('Y-m-d H:i:s', strtotime('+' . $Programsetting->reportsPlusHours . ' hour +0 minutes', strtotime($yesterday)));
        } else {
            $endToday = $today . ' 23:59:59';
            $startToday = $today . " 00:00:00";
            //
            $endYesterday = $today . ' 23:59:59';
            $startYesterday = $today . " 00:00:00";
        }
        if (date('H') < $Programsetting->reportsPlusHours) {
            $startDate = $startYesterday;
            $endDate = $endYesterday;
        } else {
            $startDate = $startToday;
            $endDate = $endToday;
        }
        ##############
        if ($sWhere == '') {
            $queryString = ' WHERE';
        } else {
            $queryString = ' AND';
        }
        $queryString .= ' sellbill.sellbilldate >= "' . $startDate . '" and sellbill.sellbilldate <=  "' . $endDate . '" ';
    }
    $sWhere .= $queryString;
    //----------------------------------------------------------------------


    $sQuery = "SELECT SQL_CALC_FOUND_ROWS " . str_replace(" , ", " ", implode(", ", $aColumns)) . " FROM  $sTable $sWhere $sOrder $sLimit ";
    //echo $sQuery;
    $rResult = $sellBillEX->runSelectQuery($sQuery);

    /* Data set length after filtering */
    $sQuery = "SELECT COUNT(" . $sIndexColumn . ") as sellbillid  FROM  $sTable $sWhere "; //$sOrder
    $rResultFilterTotal = $sellBillEX->runSelectQuery($sQuery);
    foreach ($rResultFilterTotal as $oneis) {
        $countis = $oneis->sellbillid;
    }
    $iFilteredTotal = $countis;
    /* Total data set length */
//    $sQuery = "SELECT COUNT(" . $sIndexColumn . ") as sellbillid FROM  $sTable";
//    $rResultTotal = $sellBillEX->runSelectQuery($sQuery);
//
//
//    foreach ($rResultTotal as $oneis) {
//        $countit = $oneis->sellbillid;
//    }
//    //$aResultTotal = mysql_fetch_array($rResultTotal);
//    $iTotal = $countit;

    $output = array(
        "draw" => intval(filter_input(INPUT_POST, 'sEcho')), //$_REQUEST['draw'], //intval(filter_input(INPUT_POST, 'sEcho')),
        "recordsTotal" => intval($iTotal),
        "recordsFiltered" => intval($iFilteredTotal),
        "totals" => array(),
        "data" => array()
    );
    $rowno = 1;
    //    $sellQuantity = 0; //$piecesCount
    //    $sellbilltotalpayed = 0; //payedTotal
    //    $sellbillfinalbill = 0; //remainTotal
    $sellbillaftertotalbill = 0; //totalBills//totalBills2
    ##payments on bills
    $billIDs = array();
    foreach ($rResult as $aRow) {
        $billIDs[] = $aRow->$aColumns[0];
    }
    $billIDs = !empty($billIDs) ? array_unique($billIDs) : [0];
    $paymentDataArr = R::getAll('SELECT billid, sum(clientdebtchangeamount) as clientdebtchangeamount
                FROM clientdebtchange where tablename = "clientPayedDeptSellBillsController.php" and billid in(' . implode(',', $billIDs) . ') group by billid');
    $paymentDataArr = customArrayIndexOne($paymentDataArr, 'billid');
    foreach ($rResult as $aRow) {
        $row = array();
        ##4-bill Pays data
        $paymentsOnBill = $paymentDataArr[$aRow->$aColumns[0]]['clientdebtchangeamount'];
        $payed = $paymentsOnBill + $aRow->sellbilltotalpayed;
        $waitVal = $aRow->sellbillaftertotalbill - $payed;
//        if ($waitVal > 0) {
        for ($i = 0; $i < count($aColumns); $i++) {
            switch ($aColumns[$i]) {
                case "sellbillid":
                    //                    if (isset($aRow->newSellbillidAfterEdit) && $aRow->newSellbillidAfterEdit > 0) { //<!--this bill is deleted for editing-->
                    //                        $class = "danger";
                    //                    } elseif ($aRow->conditions != 0) {
                    //                        $class = "dark";
                    //                    } elseif ($aRow->conditions == 0) {
                    //                        $class = "";
                    //                    }
                    //$row[] = "<span classOftr='" . $class . "'>$rowno</span>";
                    //$row[] = $aRow->$aColumns[0];
                    //lastReviewType

                    $class = "";
                    if ($aRow->lastReviewType == 3 || $aRow->lastReviewType == 4) {
                        $class = "danger";
                    }
                    $row[] = '<a class="' . $class . '" href="sellbillController.php?do=showDetail&sellbillid=' . $aRow->$aColumns[0] . '" target="_blank">' . $aRow->$aColumns[0] . '</a>';
                    break;
                case "sellbillaftertotalbill":
                    $row[] = $aRow->$aColumns[$i];
                    break;
                case "sellbilltotalpayed":
                    //if ($laterNakdiPayment != 1) {
                    $row[] = $payed; //$aRow->$aColumns[$i];
                    // $row[] = $waitVal;
                    //}
                    break;
                case "sellbilldate":
                    $row[] = $aRow->$aColumns[$i];
                    break;
                case "billuser.employeename as useremployeename":
                    $row[] = $aRow->useremployeename;
                    break;
                case "clientname":
                    if ($aRow->sellbillclientid == 1) {
                        $aRow->$aColumns[$i] = "نقدي";
                        $aRow->$aColumns[$i] .= "($aRow->tempclientName)";
                    }

                    if ($aRow->$aColumns[$i] == '') {
                        $aRow->$aColumns[$i] = "$aRow->tempclientName";
                    }
                    //
                    $row[] = $aRow->$aColumns[$i];
                    break;
                case "user.employeename":
                    if ($aRow->lastReviewType == 1 || $aRow->lastReviewType == 4) { //reviewed so give him Review details
                        $row[] = $aRow->employeename;
                    }
                    break;
                case "lastReviewDateTime":
                    if ($aRow->lastReviewType == 1 || $aRow->lastReviewType == 4) { //reviewed so give him Review details
                        $row[] = $aRow->$aColumns[$i];
                    }
                    break;
                case "lastReviewType":
                    if ($laterNakdiPayment == 1) {
                        $row[] = '<input type="text" class="form-control positivefloat" id="serviceMoney' . $aRow->$aColumns[0] . '" value="" data-id="' . $aRow->$aColumns[0] . '"/>';
                        $row[] = '<input type="text" class="form-control positivefloat bill_pay" value="' . $waitVal . '" data-id="' . $aRow->$aColumns[0] . '" data-wait="' . $waitVal . '" data-payed="' . $payed . '"/>
                                      <p id="moneyReq' . $aRow->$aColumns[0] . '" style="color: #CC3333;border: 1px solid #CC3333;width: 10px;height:15px;display:none">*</p>';
                        $row[] = '<input type="radio"  class="paytype"  data-id="' . $aRow->$aColumns[0] . '" id="paytype_' . $aRow->$aColumns[0] . '"  name="paytype_' . $aRow->$aColumns[0] . '" value="save" checked="checked"> خزنة
                                  <input type="radio"  class="paytype"  data-id="' . $aRow->$aColumns[0] . '" id="paytype_' . $aRow->$aColumns[0] . '"  name="paytype_' . $aRow->$aColumns[0] . '" value="bank"> بنك
                                  <div id="save_Div_' . $aRow->$aColumns[0] . '">
                                      <input type="hidden" id="save_' . $aRow->$aColumns[0] . '" name="save_' . $aRow->$aColumns[0] . '"  class="wdt50 save"/>
                                          <p id="saveReq' . $aRow->$aColumns[0] . '" style="color: #CC3333;border: 1px solid #CC3333;width: 10px;height:15px;display:none">*</p>
                                  </div>
                                  <div id="bank_Div_' . $aRow->$aColumns[0] . '" style="display:none">
                                      <input type="hidden" id="bankAccount_' . $aRow->$aColumns[0] . '" name="bankAccount_' . $aRow->$aColumns[0] . '"  class="wdt50 bankAccount"/>
                                          <p id="bankReq' . $aRow->$aColumns[0] . '" style="color: #CC3333;border: 1px solid #CC3333;width: 10px;height:15px;display:none">*</p>
                                  </div>';
                        $row[] = '<button type="button" class="btn btn-success save_payment" data-id="' . $aRow->$aColumns[0] . '" data-ret="0">سداد</button>';
                    } else {
                        $name = $class = '';
                        if ($aRow->lastReviewType == 1 || $aRow->lastReviewType == 4) { //reviewed so give him Review details
                            //$row[] = '<a data-billid="' . $aRow->sellbillid . '" data-billtype="sell" type="button" class="btn btn-default btn-lg removeicon cancelreview"></a>';
                            $name = $class = 'choosedItemSellReviewd';
                        } else {
                            //$row[] = '<a data-billid="' . $aRow->sellbillid . '" data-billtype="sell" type="button" class="btn btn-default btn-lg correcticon review"></a>';
                            $name = $class = 'choosedItemSellNotReviewd';
                        }
                        //
                        $btn = '';
                        if ($aRow->lastReviewType == 3 || $aRow->lastReviewType == 4) { //has history
                            $btn .= '<a class="btn btn-default bookicon reviewInfo" style="display: inline !important;width:8%;text-align: center !important;padding: 5px 13px !important;margin: 5px;" data-id="' . $aRow->sellbillid . '" data-type="sell"></a>';
                        }
                        $btn .= '<input type="checkbox" name="' . $name . '[]" class="' . $class . '" value="' . $aRow->sellbillid . '" data-total="' . $aRow->sellbillaftertotalbill . '" data-client="' . $aRow->clientname . '" data-cid="' . $aRow->sellbillclientid . '"/>';
                        $row[] = $btn;
                    }
                    break;
                default:
                    break;
            }
        }


        if ($aRow->conditions == 0) {
            //            $sellQuantity += $aRow->sellQuantity;
            //            $sellbilltotalpayed += $aRow->sellbilltotalpayed;
            //            $sellbillfinalbill += $aRow->sellbillfinalbill;
            $sellbillaftertotalbill += $aRow->sellbillaftertotalbill;
        }

        $output['data'][] = $row;
        $rowno++;
//        }
    }
    //    $output['totals']["piecesCount"] = $sellQuantity;
    //    $output['totals']["payedTotal"] = $sellbilltotalpayed;
    //    $output['totals']["remainTotal"] = $sellbillfinalbill;
    $output['totals']["totalBills"] = $sellbillaftertotalbill;

    //print_r($output);
    echo json_encode($output, JSON_UNESCAPED_SLASHES);
}

function showallajaxsellCash() {
    global $sellBillEX;
    global $ProgramsettingDAO;
    global $UsergroupDAO;

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

    $sIndexColumn = "sellbillid";

    /* DB table to use */
    //stable what is after from in sql statment
    ##this was right statment but slow
    $sTable = " sellbill
                    LEFT JOIN client ON sellbill.sellbillclientid = client.clientid
                    LEFT JOIN user ON user.userid = sellbill.lastReviewUser
                    LEFT JOIN user billuser ON billuser.userid = sellbill.userid";

    //    $sTable = " sellbill
    //                LEFT JOIN client ON sellbill.sellbillclientid = client.clientid
    //                left join sellbillreview on (sellbillreview.billid = sellbill.sellbillid and sellbillreview.id in(
    //                    SELECT  MAX(id)
    //                    FROM sellbillreview
    //                    GROUP BY billid
    //                ))";
    ##this was giving wrong data
    //    $sTable = " sellbill
    //                LEFT JOIN client ON sellbill.sellbillclientid = client.clientid
    //                left join (
    //                    SELECT  sellbillreview.*,MAX(id) as lastrecordid
    //                    FROM sellbillreview
    //                    GROUP BY billid
    //                ) as lastreview on (lastreview.billid = sellbill.sellbillid and lastreview.id = lastrecordid)";
    $aColumns = array('sellbillid', 'clientname', 'sellbillaftertotalbill', 'sellbilltotalpayed', 'sellbilldate', 'billuser.employeename as useremployeename', 'lastReviewDateTime', 'user.employeename', 'lastReviewType', 'tempclientName', 'sellbillclientid');

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

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

    /*
     * Ordering
     */
    $sOrder = "";

    if (isset($_POST['order'][0])) {
        $sOrder = "ORDER BY  ";
        for ($i = 0; $i < intval($_POST['columns']); $i++) {
            $orderByColumnIndex = $_POST['order'][0]['column'] - 1; //this - 1 is because first column is not counted as it is checkbox
            if ($aColumns[$orderByColumnIndex] == "billuser.employeename as useremployeename") {
                $sOrder .= "billuser.employeename
                " . ($_POST['order'][$i]['dir'] === 'asc' ? 'asc' : 'desc') . ", ";
            } else {
                $sOrder .= $aColumns[intval($_POST['columns'][$orderByColumnIndex]['data'])] . "
                " . ($_POST['order'][$i]['dir'] === 'asc' ? 'asc' : 'desc') . ", ";
            }
        }
        $sOrder = substr_replace($sOrder, "", -2);
        if ($sOrder == "ORDER BY ") {
            $sOrder = "";
        }
    }

    /*
     * Filtering
     * NOTE this does not match the built-in DataTables filtering which does it
     * word by word on any field. It's possible to do here, but concerned about efficiency
     * on very large tables, and MySQL's regex functionality is very limited
     */
    // where del =0 and aqarstatus =0 and updatestatus in(1,2,3,4) and complete=1
    $sWhere = '';
    if (isset($_POST['search']['value']) && $_POST['search']['value'] != "") {
        $sWhere = " where  (";
        for ($i = 0; $i < count($aColumns); $i++) {
            if ($aColumns[$i] == "sellbilldate" || $aColumns[$i] == "lastReviewDateTime") {
                //to get numbers and english chars only
                ##only alpha numeric is allowed . space not allowed
                $search = preg_replace('/[^#A-Za-z0-9]/', '', $_POST['search']['value']); // Removes special chars.
                if (!empty($search)) {
                    $sWhere .= $aColumns[$i] . " LIKE '%" . $search . "%' OR ";
                }
            } else if ($aColumns[$i] == "billuser.employeename as useremployeename") {
                $sWhere .= " billuser.employeename LIKE '%" . $_POST['search']['value'] . "%' OR ";
            } else {
                $sWhere .= $aColumns[$i] . " LIKE '%" . $_POST['search']['value'] . "%' OR ";
            }
        }
        $sWhere = substr_replace($sWhere, "", -3);
        $sWhere .= ")";
    }
    //-- search ------------------------------------------------------------
    if ($sWhere == '') {
        $queryString = ' WHERE';
    } else {
        $queryString = ' AND';
    }

    $message = "";
    $reviewType = $_REQUEST['reviewType'];
    $startDate = $_REQUEST['from'];
    $endDate = $_REQUEST['to'];
    $tabDateGuide = $_REQUEST['tabDateGuide'];
    $sellbillSerial = $_REQUEST['sellbillserial'];
    $sellbillId = $_REQUEST['sellbillid'];
    $clientId = (int) $_REQUEST['clientid'];
    $sellerid = $_REQUEST['sellerid'];
    $selleruserid = $_REQUEST['selleruserid'];
    $storeid = $_REQUEST['storeid'];
    //    $pricetype = $_REQUEST['pricetype'];
    $saveid = $_REQUEST['saveid'];
    $tempclientName = $_REQUEST['tempclientName'];
    $cashOnlyNotClient = (int) $_REQUEST['cashOnlyNotClient']; //1 for nakdi "clientid=1",0 for normal clients "clientid > 1",-1 for all kinds "no conditions"
    $typeClient = (int) $_REQUEST['typeClient'];
    $laterNakdiPayment = (int) $_REQUEST['laterNakdiPayment'];

    $queryString .= ' sellbill.conditions = 0 AND'; ///
    if (isset($reviewType)) {
        if ($reviewType == 0 || $reviewType == 3) {
            $queryString .= ' lastReviewType in (0,3) AND';
        } else {
            $queryString .= ' lastReviewType in (1,4) AND';
        }
    }
    if ($laterNakdiPayment == 1) {
        $userIds = R::getCell('select laterNakdiUserIds from user where userid=' . $_SESSION['userid']);
        if ($userIds != 0)
            $queryString .= ' sellbill.userid in(' . $userIds . ') AND';
    }

    /////
    if ($cashOnlyNotClient == 0) { //ignore -1 case
        $queryString .= ' sellbill.sellbillclientid != 1 AND';
    } elseif ($cashOnlyNotClient == 1) {
        $queryString .= ' sellbill.sellbillclientid = 1 AND';
    }
    if (isset($clientId) && $clientId > 0) {
        $queryString .= ' sellbill.sellbillclientid = ' . $clientId . ' AND';
    }
    ##
    if ($laterNakdiPayment == 1) { ///for eng.sherif i think for one user @reports laterNakdiPayment , laterNakdiPaymentSell , laterNakdiPaymentRet
        $queryString .= ' client.typeclientid not like "%,1,%" AND';
    } else if (isset($typeClient) && $typeClient != -1) {
        $queryString .= ' (client.typeclientid like "%,' . $typeClient . ',%" or client.typeclientid = ",-20," or client.typeclientid = "," or client.typeclientid = "") AND';
    }
    ##
    if (isset($sellerid) && $sellerid != "-1") {
        $queryString .= ' sellbill.sellerid = ' . $sellerid . ' AND';
    }
    if (isset($selleruserid) && $selleruserid != "-1") {
        $queryString .= ' sellbill.userid = ' . $selleruserid . ' AND';
    }
    if (isset($sellbillSerial) && $sellbillSerial != "") {
        $queryString .= ' sellbill.sellbillserial = "' . $sellbillSerial . '" AND';
    }
    if (isset($sellbillId) && $sellbillId != "") {
        $queryString .= ' sellbill.sellbillid = ' . $sellbillId . ' AND';
    }
    if (isset($storeid) && $storeid != "-1") {
        $queryString .= ' sellbill.sellbillstoreid = ' . $storeid . ' AND';
    }
    //    if (isset($pricetype) && $pricetype != "-1") {
    //        $queryString .= ' pricetype = ' . $pricetype . ' AND';
    //    }
    if (isset($saveid) && $saveid != "-1") {
        $queryString .= ' sellbill.sellbillsaveid = ' . $saveid . ' AND';
    }
    if (isset($tempclientName) && $tempclientName != "") {
        $queryString .= ' sellbill.tempclientName like "%' . $tempclientName . '%" AND';
    }
    ##needed Data
    $Programsetting = $ProgramsettingDAO->load(1);
    //$usergroup = $UsergroupDAO->load($_SESSION['usergroupid']);
    ##############
    ##fatma
    $search = $_REQUEST['searchDate'];
    if ($search == "null" || empty($search)) {
        $search = "dateOnly";
    }
    /* if (isset($tabDateGuide) && !empty($tabDateGuide)) {
      //display the converted time ##reportsPlusHours
      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
      $endDateToday = date('Y-m-d H:i:s', strtotime('+' . $reportsPlusHours . ' hour +0 minutes', strtotime($today)));
      $startDateToday = date('Y-m-d H:i:s', strtotime('+' . $Programsetting->reportsPlusHours . ' hour +0 minutes', strtotime($today)));
      } else {
      $endDateToday = $endDateToday . ' 23:59:59';
      $startDateToday = $startDateToday . " 00:00:00";
      }
      //}
      if ($tabDateGuide == "today") {
      $queryString .= ' sellbill.sellbilldate >= "' . $startDateToday . '" and sellbill.sellbilldate <= "' . $endDateToday . '" AND';
      } elseif ($tabDateGuide == "beforeToday") {
      $queryString .= ' sellbill.sellbilldate < "' . $startDateToday . '" AND';
      }
      } */
    if (isset($startDate) && $startDate != "" && isset($endDate) && $endDate != "") { //&& $tabDateGuide != "today"
        if ($search == "dateOnly") {
            //display the converted time ##reportsPlusHours
            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 {
                $endDate = $endDate . ' 23:59:59';
                $startDate = $startDate . " 00:00:00";
            }
        }
        $queryString .= ' sellbill.sellbilldate >= "' . $startDate . '" and sellbill.sellbilldate <= "' . $endDate . '" AND';
    }


    $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);
        }
        if ($lastWord == 'WHERE') { //remove it
            array_pop($arr);
            //form the string again
            $queryString = implode(' ', $arr);
        }

        //print("<br>queryString: ".$queryString."<br>");
    }

    $totalBills = 0;

    if ($queryString == "") {
        //display the converted time ##reportsPlusHours
        ################
        $today = date('Y-m-d');
        $yesterday = date("Y-m-d", strtotime('-1 days'));
        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
            $endToday = date('Y-m-d H:i:s', strtotime('+' . $reportsPlusHours . ' hour +0 minutes', strtotime($today)));
            $startToday = date('Y-m-d H:i:s', strtotime('+' . $Programsetting->reportsPlusHours . ' hour +0 minutes', strtotime($today)));
            //
            $endYesterday = date('Y-m-d H:i:s', strtotime('+' . $reportsPlusHours . ' hour +0 minutes', strtotime($yesterday)));
            $startYesterday = date('Y-m-d H:i:s', strtotime('+' . $Programsetting->reportsPlusHours . ' hour +0 minutes', strtotime($yesterday)));
        } else {
            $endToday = $today . ' 23:59:59';
            $startToday = $today . " 00:00:00";
            //
            $endYesterday = $today . ' 23:59:59';
            $startYesterday = $today . " 00:00:00";
        }
        if (date('H') < $Programsetting->reportsPlusHours) {
            $startDate = $startYesterday;
            $endDate = $endYesterday;
        } else {
            $startDate = $startToday;
            $endDate = $endToday;
        }
        ##############
        if ($sWhere == '') {
            $queryString = ' WHERE';
        } else {
            $queryString = ' AND';
        }
        $queryString .= ' sellbill.sellbilldate >= "' . $startDate . '" and sellbill.sellbilldate <=  "' . $endDate . '" ';
    }
    $sWhere .= $queryString;
    //----------------------------------------------------------------------

    if ($sWhere == '') {
        $sWhere = ' WHERE sellbilltotalpayed > 0 ';
    } else {
        $sWhere .= ' AND sellbilltotalpayed > 0 ';
    }

    $sQuery = "SELECT SQL_CALC_FOUND_ROWS " . str_replace(" , ", " ", implode(", ", $aColumns)) . " FROM  $sTable $sWhere $sOrder $sLimit ";
    //echo $sQuery;
    $rResult = $sellBillEX->runSelectQuery($sQuery);

    /* Data set length after filtering */
    $sQuery = "SELECT COUNT(" . $sIndexColumn . ") as sellbillid  FROM  $sTable $sWhere "; //$sOrder
    $rResultFilterTotal = $sellBillEX->runSelectQuery($sQuery);
    foreach ($rResultFilterTotal as $oneis) {
        $countis = $oneis->sellbillid;
    }
    $iFilteredTotal = $countis;
    /* Total data set length */
//    $sQuery = "SELECT COUNT(" . $sIndexColumn . ") as sellbillid FROM  $sTable";
//    $rResultTotal = $sellBillEX->runSelectQuery($sQuery);
//
//
//    foreach ($rResultTotal as $oneis) {
//        $countit = $oneis->sellbillid;
//    }
//    //$aResultTotal = mysql_fetch_array($rResultTotal);
//    $iTotal = $countit;

    $output = array(
        "draw" => intval(filter_input(INPUT_POST, 'sEcho')), //$_REQUEST['draw'], //intval(filter_input(INPUT_POST, 'sEcho')),
        "recordsTotal" => intval($iTotal),
        "recordsFiltered" => intval($iFilteredTotal),
        "totals" => array(),
        "data" => array()
    );
    $rowno = 1;
    //    $sellQuantity = 0; //$piecesCount
    //    $sellbilltotalpayed = 0; //payedTotal
    //    $sellbillfinalbill = 0; //remainTotal
    $sellbillaftertotalbill = 0; //totalBills//totalBills2
    ##payments on bills
    $billIDs = array();
    foreach ($rResult as $aRow) {
        $billIDs[] = $aRow->$aColumns[0];
    }
    $billIDs = !empty($billIDs) ? array_unique($billIDs) : [0];
    $paymentDataArr = R::getAll('SELECT billid, sum(clientdebtchangeamount) as clientdebtchangeamount
                FROM clientdebtchange where tablename = "clientPayedDeptSellBillsController.php" and billid in(' . implode(',', $billIDs) . ') group by billid');
    $paymentDataArr = customArrayIndexOne($paymentDataArr, 'billid');
    foreach ($rResult as $aRow) {
        $row = array();
        ##4-bill Pays data
        $paymentsOnBill = $paymentDataArr[$aRow->$aColumns[0]]['clientdebtchangeamount'];
        $payed = $paymentsOnBill + $aRow->sellbilltotalpayed;
        $waitVal = $aRow->sellbillaftertotalbill - $payed;
//        if ($waitVal > 0) {
        for ($i = 0; $i < count($aColumns); $i++) {
            switch ($aColumns[$i]) {
                case "sellbillid":
                    //                    if (isset($aRow->newSellbillidAfterEdit) && $aRow->newSellbillidAfterEdit > 0) { //<!--this bill is deleted for editing-->
                    //                        $class = "danger";
                    //                    } elseif ($aRow->conditions != 0) {
                    //                        $class = "dark";
                    //                    } elseif ($aRow->conditions == 0) {
                    //                        $class = "";
                    //                    }
                    //$row[] = "<span classOftr='" . $class . "'>$rowno</span>";
                    //$row[] = $aRow->$aColumns[0];
                    //lastReviewType

                    $class = "";
                    if ($aRow->lastReviewType == 3 || $aRow->lastReviewType == 4) {
                        $class = "danger";
                    }
                    $row[] = '<a class="' . $class . '" href="sellbillController.php?do=showDetail&sellbillid=' . $aRow->$aColumns[0] . '" target="_blank">' . $aRow->$aColumns[0] . '</a>';
                    break;
                case "sellbillaftertotalbill":
                    $row[] = $aRow->$aColumns[$i];
                    break;
                case "sellbilltotalpayed":
                    //if ($laterNakdiPayment != 1) {
                    $row[] = $payed; //$aRow->$aColumns[$i];
                    // $row[] = $waitVal;
                    //}
                    break;
                case "sellbilldate":
                    $row[] = $aRow->$aColumns[$i];
                    break;
                case "billuser.employeename as useremployeename":
                    $row[] = $aRow->useremployeename;
                    break;
                case "clientname":
                    if ($aRow->sellbillclientid == 1) {
                        $aRow->$aColumns[$i] = "نقدي";
                        $aRow->$aColumns[$i] .= "($aRow->tempclientName)";
                    }

                    if ($aRow->$aColumns[$i] == '') {
                        $aRow->$aColumns[$i] = "$aRow->tempclientName";
                    }
                    //
                    $row[] = $aRow->$aColumns[$i];
                    break;
                case "user.employeename":
                    if ($aRow->lastReviewType == 1 || $aRow->lastReviewType == 4) { //reviewed so give him Review details
                        $row[] = $aRow->employeename;
                    }
                    break;
                case "lastReviewDateTime":
                    if ($aRow->lastReviewType == 1 || $aRow->lastReviewType == 4) { //reviewed so give him Review details
                        $row[] = $aRow->$aColumns[$i];
                    }
                    break;
                case "lastReviewType":
                    if ($laterNakdiPayment == 1) {
                        $row[] = '<input type="text" class="form-control positivefloat" id="serviceMoney' . $aRow->$aColumns[0] . '" value="" data-id="' . $aRow->$aColumns[0] . '"/>';
                        $row[] = '<input type="text" class="form-control positivefloat bill_pay" value="' . $waitVal . '" data-id="' . $aRow->$aColumns[0] . '" data-wait="' . $waitVal . '" data-payed="' . $payed . '"/>
                                      <p id="moneyReq' . $aRow->$aColumns[0] . '" style="color: #CC3333;border: 1px solid #CC3333;width: 10px;height:15px;display:none">*</p>';
                        $row[] = '<input type="radio"  class="paytype"  data-id="' . $aRow->$aColumns[0] . '" id="paytype_' . $aRow->$aColumns[0] . '"  name="paytype_' . $aRow->$aColumns[0] . '" value="save" checked="checked"> خزنة
                                  <input type="radio"  class="paytype"  data-id="' . $aRow->$aColumns[0] . '" id="paytype_' . $aRow->$aColumns[0] . '"  name="paytype_' . $aRow->$aColumns[0] . '" value="bank"> بنك
                                  <div id="save_Div_' . $aRow->$aColumns[0] . '">
                                      <input type="hidden" id="save_' . $aRow->$aColumns[0] . '" name="save_' . $aRow->$aColumns[0] . '"  class="wdt50 save"/>
                                          <p id="saveReq' . $aRow->$aColumns[0] . '" style="color: #CC3333;border: 1px solid #CC3333;width: 10px;height:15px;display:none">*</p>
                                  </div>
                                  <div id="bank_Div_' . $aRow->$aColumns[0] . '" style="display:none">
                                      <input type="hidden" id="bankAccount_' . $aRow->$aColumns[0] . '" name="bankAccount_' . $aRow->$aColumns[0] . '"  class="wdt50 bankAccount"/>
                                          <p id="bankReq' . $aRow->$aColumns[0] . '" style="color: #CC3333;border: 1px solid #CC3333;width: 10px;height:15px;display:none">*</p>
                                  </div>';
                        $row[] = '<button type="button" class="btn btn-success save_payment" data-id="' . $aRow->$aColumns[0] . '" data-ret="0">سداد</button>';
                    } else {
                        $name = $class = '';
                        if ($aRow->lastReviewType == 1 || $aRow->lastReviewType == 4) { //reviewed so give him Review details
                            //$row[] = '<a data-billid="' . $aRow->sellbillid . '" data-billtype="sell" type="button" class="btn btn-default btn-lg removeicon cancelreview"></a>';
                            $name = $class = 'choosedItemSellReviewd';
                        } else {
                            //$row[] = '<a data-billid="' . $aRow->sellbillid . '" data-billtype="sell" type="button" class="btn btn-default btn-lg correcticon review"></a>';
                            $name = $class = 'choosedItemSellNotReviewd';
                        }
                        //
                        $btn = '';
                        if ($aRow->lastReviewType == 3 || $aRow->lastReviewType == 4) { //has history
                            $btn .= '<a class="btn btn-default bookicon reviewInfo" style="display: inline !important;width:8%;text-align: center !important;padding: 5px 13px !important;margin: 5px;" data-id="' . $aRow->sellbillid . '" data-type="sell"></a>';
                        }
                        $btn .= '<input type="checkbox" name="' . $name . '[]" class="' . $class . '" value="' . $aRow->sellbillid . '" data-total="' . $aRow->sellbillaftertotalbill . '" data-client="' . $aRow->clientname . '" data-cid="' . $aRow->sellbillclientid . '"/>';
                        $row[] = $btn;
                    }
                    break;
                default:
                    break;
            }
        }


        if ($aRow->conditions == 0) {
            //            $sellQuantity += $aRow->sellQuantity;
            //            $sellbilltotalpayed += $aRow->sellbilltotalpayed;
            //            $sellbillfinalbill += $aRow->sellbillfinalbill;
            $sellbillaftertotalbill += $aRow->sellbillaftertotalbill;
        }

        $output['data'][] = $row;
        $rowno++;
//        }
    }
    //    $output['totals']["piecesCount"] = $sellQuantity;
    //    $output['totals']["payedTotal"] = $sellbilltotalpayed;
    //    $output['totals']["remainTotal"] = $sellbillfinalbill;
    $output['totals']["totalBills"] = $sellbillaftertotalbill;

    //print_r($output);
    echo json_encode($output, JSON_UNESCAPED_SLASHES);
}

function showallajaxsellBook() {
    global $sellBillEX;
    global $ProgramsettingDAO;
    global $UsergroupDAO;

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

    $sIndexColumn = "sellbillid";

    /* DB table to use */
    //stable what is after from in sql statment
    ##this was right statment but slow
    $sTable = " sellbill
                    LEFT JOIN client ON sellbill.sellbillclientid = client.clientid
                    LEFT JOIN user ON user.userid = sellbill.lastReviewUser
                    LEFT JOIN user billuser ON billuser.userid = sellbill.userid";

    //    $sTable = " sellbill
    //                LEFT JOIN client ON sellbill.sellbillclientid = client.clientid
    //                left join sellbillreview on (sellbillreview.billid = sellbill.sellbillid and sellbillreview.id in(
    //                    SELECT  MAX(id)
    //                    FROM sellbillreview
    //                    GROUP BY billid
    //                ))";
    ##this was giving wrong data
    //    $sTable = " sellbill
    //                LEFT JOIN client ON sellbill.sellbillclientid = client.clientid
    //                left join (
    //                    SELECT  sellbillreview.*,MAX(id) as lastrecordid
    //                    FROM sellbillreview
    //                    GROUP BY billid
    //                ) as lastreview on (lastreview.billid = sellbill.sellbillid and lastreview.id = lastrecordid)";
    $aColumns = array('sellbillid', 'clientname', 'sellbillaftertotalbill', 'sellbilltotalpayed', 'sellbilldate', 'billuser.employeename as useremployeename', 'lastReviewDateTime', 'user.employeename', 'lastReviewType', 'tempclientName', 'sellbillclientid');

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

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

    /*
     * Ordering
     */
    $sOrder = "";

    if (isset($_POST['order'][0])) {
        $sOrder = "ORDER BY  ";
        for ($i = 0; $i < intval($_POST['columns']); $i++) {
            $orderByColumnIndex = $_POST['order'][0]['column'] - 1; //this - 1 is because first column is not counted as it is checkbox
            if ($aColumns[$orderByColumnIndex] == "billuser.employeename as useremployeename") {
                $sOrder .= "billuser.employeename
                " . ($_POST['order'][$i]['dir'] === 'asc' ? 'asc' : 'desc') . ", ";
            } else {
                $sOrder .= $aColumns[intval($_POST['columns'][$orderByColumnIndex]['data'])] . "
                " . ($_POST['order'][$i]['dir'] === 'asc' ? 'asc' : 'desc') . ", ";
            }
        }
        $sOrder = substr_replace($sOrder, "", -2);
        if ($sOrder == "ORDER BY ") {
            $sOrder = "";
        }
    }

    /*
     * Filtering
     * NOTE this does not match the built-in DataTables filtering which does it
     * word by word on any field. It's possible to do here, but concerned about efficiency
     * on very large tables, and MySQL's regex functionality is very limited
     */
    // where del =0 and aqarstatus =0 and updatestatus in(1,2,3,4) and complete=1
    $sWhere = '';
    if (isset($_POST['search']['value']) && $_POST['search']['value'] != "") {
        $sWhere = " where  (";
        for ($i = 0; $i < count($aColumns); $i++) {
            if ($aColumns[$i] == "sellbilldate" || $aColumns[$i] == "lastReviewDateTime") {
                //to get numbers and english chars only
                ##only alpha numeric is allowed . space not allowed
                $search = preg_replace('/[^#A-Za-z0-9]/', '', $_POST['search']['value']); // Removes special chars.
                if (!empty($search)) {
                    $sWhere .= $aColumns[$i] . " LIKE '%" . $search . "%' OR ";
                }
            } else if ($aColumns[$i] == "billuser.employeename as useremployeename") {
                $sWhere .= " billuser.employeename LIKE '%" . $_POST['search']['value'] . "%' OR ";
            } else {
                $sWhere .= $aColumns[$i] . " LIKE '%" . $_POST['search']['value'] . "%' OR ";
            }
        }
        $sWhere = substr_replace($sWhere, "", -3);
        $sWhere .= ")";
    }
    //-- search ------------------------------------------------------------
    if ($sWhere == '') {
        $queryString = ' WHERE';
    } else {
        $queryString = ' AND';
    }

    $message = "";
    $reviewType = $_REQUEST['reviewType'];
    $startDate = $_REQUEST['from'];
    $endDate = $_REQUEST['to'];
    $tabDateGuide = $_REQUEST['tabDateGuide'];
    $sellbillSerial = $_REQUEST['sellbillserial'];
    $sellbillId = $_REQUEST['sellbillid'];
    $clientId = (int) $_REQUEST['clientid'];
    $sellerid = $_REQUEST['sellerid'];
    $selleruserid = $_REQUEST['selleruserid'];
    $storeid = $_REQUEST['storeid'];
    //    $pricetype = $_REQUEST['pricetype'];
    $saveid = $_REQUEST['saveid'];
    $tempclientName = $_REQUEST['tempclientName'];
    $cashOnlyNotClient = (int) $_REQUEST['cashOnlyNotClient']; //1 for nakdi "clientid=1",0 for normal clients "clientid > 1",-1 for all kinds "no conditions"
    $typeClient = (int) $_REQUEST['typeClient'];
    $laterNakdiPayment = (int) $_REQUEST['laterNakdiPayment'];

    $queryString .= ' sellbill.conditions = 0 AND'; ///
    if (isset($reviewType)) {
        if ($reviewType == 0 || $reviewType == 3) {
            $queryString .= ' lastReviewType in (0,3) AND';
        } else {
            $queryString .= ' lastReviewType in (1,4) AND';
        }
    }
    if ($laterNakdiPayment == 1) {
        $userIds = R::getCell('select laterNakdiUserIds from user where userid=' . $_SESSION['userid']);
        if ($userIds != 0)
            $queryString .= ' sellbill.userid in(' . $userIds . ') AND';
    }

    /////
    if ($cashOnlyNotClient == 0) { //ignore -1 case
        $queryString .= ' sellbill.sellbillclientid != 1 AND';
    } elseif ($cashOnlyNotClient == 1) {
        $queryString .= ' sellbill.sellbillclientid = 1 AND';
    }
    if (isset($clientId) && $clientId > 0) {
        $queryString .= ' sellbill.sellbillclientid = ' . $clientId . ' AND';
    }
    ##
    if ($laterNakdiPayment == 1) { ///for eng.sherif i think for one user @reports laterNakdiPayment , laterNakdiPaymentSell , laterNakdiPaymentRet
        $queryString .= ' client.typeclientid not like "%,1,%" AND';
    } else if (isset($typeClient) && $typeClient != -1) {
        $queryString .= ' (client.typeclientid like "%,' . $typeClient . ',%" or client.typeclientid = ",-20," or client.typeclientid = "," or client.typeclientid = "") AND';
    }
    ##
    if (isset($sellerid) && $sellerid != "-1") {
        $queryString .= ' sellbill.sellerid = ' . $sellerid . ' AND';
    }
    if (isset($selleruserid) && $selleruserid != "-1") {
        $queryString .= ' sellbill.userid = ' . $selleruserid . ' AND';
    }
    if (isset($sellbillSerial) && $sellbillSerial != "") {
        $queryString .= ' sellbill.sellbillserial = "' . $sellbillSerial . '" AND';
    }
    if (isset($sellbillId) && $sellbillId != "") {
        $queryString .= ' sellbill.sellbillid = ' . $sellbillId . ' AND';
    }
    if (isset($storeid) && $storeid != "-1") {
        $queryString .= ' sellbill.sellbillstoreid = ' . $storeid . ' AND';
    }
    //    if (isset($pricetype) && $pricetype != "-1") {
    //        $queryString .= ' pricetype = ' . $pricetype . ' AND';
    //    }
    if (isset($saveid) && $saveid != "-1") {
        $queryString .= ' sellbill.sellbillsaveid = ' . $saveid . ' AND';
    }
    if (isset($tempclientName) && $tempclientName != "") {
        $queryString .= ' sellbill.tempclientName like "%' . $tempclientName . '%" AND';
    }
    ##needed Data
    $Programsetting = $ProgramsettingDAO->load(1);
    //$usergroup = $UsergroupDAO->load($_SESSION['usergroupid']);
    ##############
    ##fatma
    $search = $_REQUEST['searchDate'];
    if ($search == "null" || empty($search)) {
        $search = "dateOnly";
    }
    /* if (isset($tabDateGuide) && !empty($tabDateGuide)) {
      //display the converted time ##reportsPlusHours
      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
      $endDateToday = date('Y-m-d H:i:s', strtotime('+' . $reportsPlusHours . ' hour +0 minutes', strtotime($today)));
      $startDateToday = date('Y-m-d H:i:s', strtotime('+' . $Programsetting->reportsPlusHours . ' hour +0 minutes', strtotime($today)));
      } else {
      $endDateToday = $endDateToday . ' 23:59:59';
      $startDateToday = $startDateToday . " 00:00:00";
      }
      //}
      if ($tabDateGuide == "today") {
      $queryString .= ' sellbill.sellbilldate >= "' . $startDateToday . '" and sellbill.sellbilldate <= "' . $endDateToday . '" AND';
      } elseif ($tabDateGuide == "beforeToday") {
      $queryString .= ' sellbill.sellbilldate < "' . $startDateToday . '" AND';
      }
      } */
    if (isset($startDate) && $startDate != "" && isset($endDate) && $endDate != "") { //&& $tabDateGuide != "today"
        if ($search == "dateOnly") {
            //display the converted time ##reportsPlusHours
            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 {
                $endDate = $endDate . ' 23:59:59';
                $startDate = $startDate . " 00:00:00";
            }
        }
        $queryString .= ' sellbill.sellbilldate >= "' . $startDate . '" and sellbill.sellbilldate <= "' . $endDate . '" AND';
    }


    $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);
        }
        if ($lastWord == 'WHERE') { //remove it
            array_pop($arr);
            //form the string again
            $queryString = implode(' ', $arr);
        }

        //print("<br>queryString: ".$queryString."<br>");
    }

    $totalBills = 0;

    if ($queryString == "") {
        //display the converted time ##reportsPlusHours
        ################
        $today = date('Y-m-d');
        $yesterday = date("Y-m-d", strtotime('-1 days'));
        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
            $endToday = date('Y-m-d H:i:s', strtotime('+' . $reportsPlusHours . ' hour +0 minutes', strtotime($today)));
            $startToday = date('Y-m-d H:i:s', strtotime('+' . $Programsetting->reportsPlusHours . ' hour +0 minutes', strtotime($today)));
            //
            $endYesterday = date('Y-m-d H:i:s', strtotime('+' . $reportsPlusHours . ' hour +0 minutes', strtotime($yesterday)));
            $startYesterday = date('Y-m-d H:i:s', strtotime('+' . $Programsetting->reportsPlusHours . ' hour +0 minutes', strtotime($yesterday)));
        } else {
            $endToday = $today . ' 23:59:59';
            $startToday = $today . " 00:00:00";
            //
            $endYesterday = $today . ' 23:59:59';
            $startYesterday = $today . " 00:00:00";
        }
        if (date('H') < $Programsetting->reportsPlusHours) {
            $startDate = $startYesterday;
            $endDate = $endYesterday;
        } else {
            $startDate = $startToday;
            $endDate = $endToday;
        }
        ##############
        if ($sWhere == '') {
            $queryString = ' WHERE';
        } else {
            $queryString = ' AND';
        }
        $queryString .= ' sellbill.sellbilldate >= "' . $startDate . '" and sellbill.sellbilldate <=  "' . $endDate . '" ';
    }
    $sWhere .= $queryString;
    //----------------------------------------------------------------------
    if ($sWhere == '') {
        $sWhere = ' WHERE sellbilltotalpayed <= 0 ';
    } else {
        $sWhere .= ' AND sellbilltotalpayed <= 0 ';
    }

    $sQuery = "SELECT SQL_CALC_FOUND_ROWS " . str_replace(" , ", " ", implode(", ", $aColumns)) . " FROM  $sTable $sWhere $sOrder $sLimit ";
    //echo $sQuery;
    $rResult = $sellBillEX->runSelectQuery($sQuery);

    /* Data set length after filtering */
    $sQuery = "SELECT COUNT(" . $sIndexColumn . ") as sellbillid  FROM  $sTable $sWhere "; //$sOrder
    $rResultFilterTotal = $sellBillEX->runSelectQuery($sQuery);
    foreach ($rResultFilterTotal as $oneis) {
        $countis = $oneis->sellbillid;
    }
    $iFilteredTotal = $countis;
    /* Total data set length */
//    $sQuery = "SELECT COUNT(" . $sIndexColumn . ") as sellbillid FROM  $sTable";
//    $rResultTotal = $sellBillEX->runSelectQuery($sQuery);
//
//
//    foreach ($rResultTotal as $oneis) {
//        $countit = $oneis->sellbillid;
//    }
//    //$aResultTotal = mysql_fetch_array($rResultTotal);
//    $iTotal = $countit;
    $output = array(
        "draw" => intval(filter_input(INPUT_POST, 'sEcho')), //$_REQUEST['draw'], //intval(filter_input(INPUT_POST, 'sEcho')),
        "recordsTotal" => intval($iTotal),
        "recordsFiltered" => intval($iFilteredTotal),
        "totals" => array(),
        "data" => array()
    );
    $rowno = 1;
    //    $sellQuantity = 0; //$piecesCount
    //    $sellbilltotalpayed = 0; //payedTotal
    //    $sellbillfinalbill = 0; //remainTotal
    $sellbillaftertotalbill = 0; //totalBills//totalBills2
    ##payments on bills
    $billIDs = array();
    foreach ($rResult as $aRow) {
        $billIDs[] = $aRow->$aColumns[0];
    }
    $billIDs = !empty($billIDs) ? array_unique($billIDs) : [0];
    $paymentDataArr = R::getAll('SELECT billid, sum(clientdebtchangeamount) as clientdebtchangeamount
                FROM clientdebtchange where tablename = "clientPayedDeptSellBillsController.php" and billid in(' . implode(',', $billIDs) . ') group by billid');
    $paymentDataArr = customArrayIndexOne($paymentDataArr, 'billid');
    foreach ($rResult as $aRow) {
        $row = array();
        ##4-bill Pays datashowallajaxretBook
        $paymentsOnBill = $paymentDataArr[$aRow->$aColumns[0]]['clientdebtchangeamount'];
        $payed = $paymentsOnBill + $aRow->sellbilltotalpayed;
        $waitVal = $aRow->sellbillaftertotalbill - $payed;
        if ($waitVal > 0) {
            for ($i = 0; $i < count($aColumns); $i++) {
                switch ($aColumns[$i]) {
                    case "sellbillid":
                        //                    if (isset($aRow->newSellbillidAfterEdit) && $aRow->newSellbillidAfterEdit > 0) { //<!--this bill is deleted for editing-->
                        //                        $class = "danger";
                        //                    } elseif ($aRow->conditions != 0) {
                        //                        $class = "dark";
                        //                    } elseif ($aRow->conditions == 0) {
                        //                        $class = "";
                        //                    }
                        //$row[] = "<span classOftr='" . $class . "'>$rowno</span>";
                        //$row[] = $aRow->$aColumns[0];
                        //lastReviewType

                        $class = "";
                        if ($aRow->lastReviewType == 3 || $aRow->lastReviewType == 4) {
                            $class = "danger";
                        }
                        $row[] = '<a class="' . $class . '" href="sellbillController.php?do=showDetail&sellbillid=' . $aRow->$aColumns[0] . '" target="_blank">' . $aRow->$aColumns[0] . '</a>';
                        break;
                    case "sellbillaftertotalbill":
                        $row[] = $aRow->$aColumns[$i];
                        break;
                    case "sellbilltotalpayed":
                        //if ($laterNakdiPayment != 1) {
                        $row[] = $payed; //$aRow->$aColumns[$i];
                        // $row[] = $waitVal;
                        //}
                        break;
                    case "sellbilldate":
                        $row[] = $aRow->$aColumns[$i];
                        break;
                    case "billuser.employeename as useremployeename":
                        $row[] = $aRow->useremployeename;
                        break;
                    case "clientname":
                        if ($aRow->sellbillclientid == 1) {
                            $aRow->$aColumns[$i] = "نقدي";
                            $aRow->$aColumns[$i] .= "($aRow->tempclientName)";
                        }

                        if ($aRow->$aColumns[$i] == '') {
                            $aRow->$aColumns[$i] = "$aRow->tempclientName";
                        }
                        //
                        $row[] = $aRow->$aColumns[$i];
                        break;
                    case "user.employeename":
                        if ($aRow->lastReviewType == 1 || $aRow->lastReviewType == 4) { //reviewed so give him Review details
                            $row[] = $aRow->employeename;
                        }
                        break;
                    case "lastReviewDateTime":
                        if ($aRow->lastReviewType == 1 || $aRow->lastReviewType == 4) { //reviewed so give him Review details
                            $row[] = $aRow->$aColumns[$i];
                        }
                        break;
                    case "lastReviewType":
                        if ($laterNakdiPayment == 1) {
                            $row[] = '<input type="text" class="form-control positivefloat" id="serviceMoney' . $aRow->$aColumns[0] . '" value="" data-id="' . $aRow->$aColumns[0] . '"/>';
                            $row[] = '<input type="text" class="form-control positivefloat bill_pay" value="' . $waitVal . '" data-id="' . $aRow->$aColumns[0] . '" data-wait="' . $waitVal . '" data-payed="' . $payed . '"/>
                                      <p id="moneyReq' . $aRow->$aColumns[0] . '" style="color: #CC3333;border: 1px solid #CC3333;width: 10px;height:15px;display:none">*</p>';
                            $row[] = '<input type="radio"  class="paytype"  data-id="' . $aRow->$aColumns[0] . '" id="paytype_' . $aRow->$aColumns[0] . '"  name="paytype_' . $aRow->$aColumns[0] . '" value="save" checked="checked"> خزنة
                                  <input type="radio"  class="paytype"  data-id="' . $aRow->$aColumns[0] . '" id="paytype_' . $aRow->$aColumns[0] . '"  name="paytype_' . $aRow->$aColumns[0] . '" value="bank"> بنك
                                  <div id="save_Div_' . $aRow->$aColumns[0] . '">
                                      <input type="hidden" id="save_' . $aRow->$aColumns[0] . '" name="save_' . $aRow->$aColumns[0] . '"  class="wdt50 save"/>
                                          <p id="saveReq' . $aRow->$aColumns[0] . '" style="color: #CC3333;border: 1px solid #CC3333;width: 10px;height:15px;display:none">*</p>
                                  </div>
                                  <div id="bank_Div_' . $aRow->$aColumns[0] . '" style="display:none">
                                      <input type="hidden" id="bankAccount_' . $aRow->$aColumns[0] . '" name="bankAccount_' . $aRow->$aColumns[0] . '"  class="wdt50 bankAccount"/>
                                          <p id="bankReq' . $aRow->$aColumns[0] . '" style="color: #CC3333;border: 1px solid #CC3333;width: 10px;height:15px;display:none">*</p>
                                  </div>';
                            $row[] = '<button type="button" class="btn btn-success save_payment" data-id="' . $aRow->$aColumns[0] . '" data-ret="0">سداد</button>';
                        } else {
                            $name = $class = '';
                            if ($aRow->lastReviewType == 1 || $aRow->lastReviewType == 4) { //reviewed so give him Review details
                                //$row[] = '<a data-billid="' . $aRow->sellbillid . '" data-billtype="sell" type="button" class="btn btn-default btn-lg removeicon cancelreview"></a>';
                                $name = $class = 'choosedItemSellReviewd';
                            } else {
                                //$row[] = '<a data-billid="' . $aRow->sellbillid . '" data-billtype="sell" type="button" class="btn btn-default btn-lg correcticon review"></a>';
                                $name = $class = 'choosedItemSellNotReviewd';
                            }
                            //
                            $btn = '';
                            if ($aRow->lastReviewType == 3 || $aRow->lastReviewType == 4) { //has history
                                $btn .= '<a class="btn btn-default bookicon reviewInfo" style="display: inline !important;width:8%;text-align: center !important;padding: 5px 13px !important;margin: 5px;" data-id="' . $aRow->sellbillid . '" data-type="sell"></a>';
                            }
                            $btn .= '<input type="checkbox" name="' . $name . '[]" class="' . $class . '" value="' . $aRow->sellbillid . '" data-total="' . $aRow->sellbillaftertotalbill . '" data-client="' . $aRow->clientname . '" data-cid="' . $aRow->sellbillclientid . '"/>';
                            $row[] = $btn;
                        }
                        break;
                    default:
                        break;
                }
            }


            if ($aRow->conditions == 0) {
                //            $sellQuantity += $aRow->sellQuantity;
                //            $sellbilltotalpayed += $aRow->sellbilltotalpayed;
                //            $sellbillfinalbill += $aRow->sellbillfinalbill;
                $sellbillaftertotalbill += $aRow->sellbillaftertotalbill;
            }

            $output['data'][] = $row;
            $rowno++;
        }
    }
    //    $output['totals']["piecesCount"] = $sellQuantity;
    //    $output['totals']["payedTotal"] = $sellbilltotalpayed;
    //    $output['totals']["remainTotal"] = $sellbillfinalbill;
    $output['totals']["totalBills"] = $sellbillaftertotalbill;

    //print_r($output);
    echo json_encode($output, JSON_UNESCAPED_SLASHES);
}

function showallajaxret() {
    global $returnSellBillEX;
    global $ProgramsettingDAO;
    global $UsergroupDAO;
    $today = date('Y-m-d');

    $sIndexColumn = "returnsellbillid";

    /* DB table to use */
    //stable what is after from in sql statment
    $sTable = " returnsellbill
                LEFT JOIN client ON returnsellbill.returnsellbillclientid = client.clientid
                LEFT JOIN user ON user.userid = returnsellbill.lastReviewUser
                LEFT JOIN user billuser ON billuser.userid = returnsellbill.userid";
    ##this was right statment but slow
    //    $sTable = " returnsellbill
    //            LEFT JOIN client ON returnsellbill.returnsellbillclientid = client.clientid
    //            left join returnsellbillreview on (returnsellbillreview.billid = returnsellbill.returnsellbillid and returnsellbillreview.id in(
    //                SELECT  MAX(id)
    //                FROM returnsellbillreview
    //                GROUP BY billid
    //
    ##this was giving wrong data
    //    $sTable = " returnsellbill
    //            LEFT JOIN client ON returnsellbill.returnsellbillclientid = client.clientid
    //            left join (
    //                SELECT  returnsellbillreview.*,MAX(id) lastrecordid
    //                FROM returnsellbillreview
    //                GROUP BY billid
    //            ) as lastreview on (lastreview.billid = returnsellbill.returnsellbillid and lastreview.id = lastrecordid)";
    $aColumns = array('returnsellbillid', 'clientname', 'returnsellbillaftertotalbill', 'returnsellbilltotalpayed', 'returnsellbilldate', 'billuser.employeename as useremployeename', 'lastReviewDateTime', 'user.employeename', 'lastReviewType', 'tempclientName', 'returnsellbillclientid');

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

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

    /*
     * Ordering
     */
    $sOrder = "";

    if (isset($_POST['order'][0])) {
        $sOrder = "ORDER BY  ";
        for ($i = 0; $i < intval($_POST['columns']); $i++) {
            $orderByColumnIndex = $_POST['order'][0]['column'] - 1; //this - 1 is because first column is not counted as it is checkbox
            if ($aColumns[$orderByColumnIndex] == "billuser.employeename as useremployeename") {
                $sOrder .= "billuser.employeename
                " . ($_POST['order'][$i]['dir'] === 'asc' ? 'asc' : 'desc') . ", ";
            } else {
                $sOrder .= $aColumns[intval($_POST['columns'][$orderByColumnIndex]['data'])] . "
                " . ($_POST['order'][$i]['dir'] === 'asc' ? 'asc' : 'desc') . ", ";
            }
        }
        $sOrder = substr_replace($sOrder, "", -2);
        if ($sOrder == "ORDER BY ") {
            $sOrder = "";
        }
    }

    /*
     * Filtering
     * NOTE this does not match the built-in DataTables filtering which does it
     * word by word on any field. It's possible to do here, but concerned about efficiency
     * on very large tables, and MySQL's regex functionality is very limited
     */
    // where del =0 and aqarstatus =0 and updatestatus in(1,2,3,4) and complete=1
    $sWhere = '';
    if (isset($_POST['search']['value']) && $_POST['search']['value'] != "") {
        $sWhere = " where  (";
        for ($i = 0; $i < count($aColumns); $i++) {
            if ($aColumns[$i] == "returnsellbilldate" || $aColumns[$i] == "lastReviewDateTime") {
                //to get numbers and english chars only
                ##only alpha numeric is allowed . space not allowed
                $search = preg_replace('/[^#A-Za-z0-9]/', '', $_POST['search']['value']); // Removes special chars.
                if (!empty($search)) {
                    $sWhere .= $aColumns[$i] . " LIKE '%" . $search . "%' OR ";
                }
            } else if ($aColumns[$i] == "billuser.employeename as useremployeename") {
                $sWhere .= " billuser.employeename LIKE '%" . $_POST['search']['value'] . "%' OR ";
            } else {
                $sWhere .= $aColumns[$i] . " LIKE '%" . $_POST['search']['value'] . "%' OR ";
            }
        }
        $sWhere = substr_replace($sWhere, "", -3);
        $sWhere .= ")";
    }
    //-- search ------------------------------------------------------------
    if ($sWhere == '') {
        $queryString = ' WHERE';
    } else {
        $queryString = ' AND';
    }
    $message = "";
    $reviewType = $_REQUEST['reviewType'];
    $startDate = $_REQUEST['from'];
    $endDate = $_REQUEST['to'];
    $tabDateGuide = $_REQUEST['tabDateGuide'];
    $sellbillSerial = $_REQUEST['sellbillserial'];
    $sellbillId = $_REQUEST['sellbillid']; //$_REQUEST['returnsellbillid'];
    $clientId = (int) $_REQUEST['clientid'];
    $sellerid = $_REQUEST['sellerid'];
    $selleruserid = $_REQUEST['selleruserid'];
    $storeid = $_REQUEST['storeid'];
    $saveid = $_REQUEST['saveid'];
    $tempclientName = $_REQUEST['tempclientName'];
    //    $pricetype = $_REQUEST['pricetype'];
    $cashOnlyNotClient = (int) $_REQUEST['cashOnlyNotClient']; //1 for nakdi "clientid=1",0 for normal clients "clientid > 1",-1 for all kinds "no conditions"
    $typeClient = (int) $_REQUEST['typeClient'];
    $laterNakdiPayment = (int) $_REQUEST['laterNakdiPayment'];

    $search = $_REQUEST['searchDate'];
    if ($search == "null" || empty($search)) {
        $search = "dateOnly";
    }
    ##needed Data
    $Programsetting = $ProgramsettingDAO->load(1);
    //$usergroup = $UsergroupDAO->load($_SESSION['usergroupid']);
    ############
    $queryString .= ' returnsellbill.conditions = 0 AND'; ///
    if (isset($reviewType)) {
        if ($reviewType == 0) {
            $queryString .= ' lastReviewType in (0,3) AND';
        } else {
            $queryString .= ' lastReviewType in (1,4) AND';
        }
    }
    if ($laterNakdiPayment == 1) {
        $userIds = R::getCell('select laterNakdiUserIds from user where userid=' . $_SESSION['userid']);
        if ($userIds != 0)
            $queryString .= ' returnsellbill.userid in(' . $userIds . ') AND';
    }
    ////
    if ($cashOnlyNotClient == 0) { //ignore -1 case
        $queryString .= ' returnsellbillclientid != 1 AND';
    } elseif ($cashOnlyNotClient == 1) {
        $queryString .= ' returnsellbillclientid = 1 AND';
    }

    if (isset($clientId) && $clientId > 0) {
        $queryString .= ' returnsellbillclientid = "' . $clientId . '" AND';
    }
    ##
    if ($laterNakdiPayment == 1) { ///for eng.sherif i think for one user @reports laterNakdiPayment , laterNakdiPaymentSell , laterNakdiPaymentRet
        $queryString .= ' client.typeclientid not like "%,1,%" AND';
    } else if (isset($typeClient) && $typeClient != -1) {
        $queryString .= ' (client.typeclientid like "%,' . $typeClient . ',%" or client.typeclientid = ",-20," or client.typeclientid = "," or client.typeclientid = "") AND';
    }
    ##
    if (isset($sellerid) && $sellerid != "-1") {
        $queryString .= ' sellerid = ' . $sellerid . ' AND';
    }
    if (isset($selleruserid) && $selleruserid != "-1") {
        $queryString .= ' returnsellbill.userid = ' . $selleruserid . ' AND';
    }
    if (isset($sellbillSerial) && $sellbillSerial != "" && $sellbillSerial != '-1') {
        $queryString .= ' returnsellbillserial = "' . $sellbillSerial . '" AND';
    }
    if (isset($sellbillId) && $sellbillId != "") {
        $queryString .= ' returnsellbillid = ' . $sellbillId . ' AND';
    }
    if (isset($storeid) && $storeid != "-1") {
        $queryString .= ' returnsellbillstoreid = ' . $storeid . ' AND';
    }
    if (isset($saveid) && $saveid != "-1") {
        $queryString .= ' returnsellbillsaveid = ' . $saveid . ' AND';
    }
    if (isset($tempclientName) && $tempclientName != "") {
        $queryString .= ' returnsellbill.tempclientName like "%' . $tempclientName . '%" AND';
    }
    ##fatma
    /* if (isset($tabDateGuide) && !empty($tabDateGuide)) {
      //display the converted time ##reportsPlusHours
      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
      $endDateToday = date('Y-m-d H:i:s', strtotime('+' . $reportsPlusHours . ' hour +0 minutes', strtotime($today)));
      $startDateToday = date('Y-m-d H:i:s', strtotime('+' . $Programsetting->reportsPlusHours . ' hour +0 minutes', strtotime($today)));
      } else {
      $endDateToday = $endDateToday . ' 23:59:59';
      $startDateToday = $startDateToday . " 00:00:00";
      }
      //}
      if ($tabDateGuide == "today") {
      $queryString .= ' returnsellbilldate >= "' . $startDateToday . '" and  returnsellbilldate <= "' . $endDateToday . '" AND';
      } elseif ($tabDateGuide == "beforeToday") {
      $queryString .= ' returnsellbilldate < "' . $startDateToday . '" AND';
      }
      } */
    if (isset($startDate) && $startDate != "" && isset($endDate) && $endDate != "") { // && $tabDateGuide != "today"
        //display the converted time ##reportsPlusHours
        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 {
            $endDate = $endDate . ' 23:59:59';
            $startDate = $startDate . " 00:00:00";
        }
        $queryString .= ' returnsellbilldate >= "' . $startDate . '" and  returnsellbilldate <= "' . $endDate . '" AND';
    }
    //    if (isset($pricetype) && $pricetype != "-1") {
    //        $queryString .= ' pricetype = ' . $pricetype . ' AND';
    //    }
    ##############
    ##fatma

    $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);
        }
        if ($lastWord == 'WHERE') { //remove it
            array_pop($arr);
            //form the string again
            $queryString = implode(' ', $arr);
        }

        //print("<br>queryString: ".$queryString."<br>");
    }

    $totalBills = 0;

    if ($queryString == "") {
        $today = date("Y-m-d");
        //display the converted time ##reportsPlusHours
        $Programsetting = $ProgramsettingDAO->load(1);
        ################
        $today = date('Y-m-d');
        $yesterday = date("Y-m-d", strtotime('-1 days'));
        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
            $endToday = date('Y-m-d H:i:s', strtotime('+' . $reportsPlusHours . ' hour +0 minutes', strtotime($today)));
            $startToday = date('Y-m-d H:i:s', strtotime('+' . $Programsetting->reportsPlusHours . ' hour +0 minutes', strtotime($today)));
            //
            $endYesterday = date('Y-m-d H:i:s', strtotime('+' . $reportsPlusHours . ' hour +0 minutes', strtotime($yesterday)));
            $startYesterday = date('Y-m-d H:i:s', strtotime('+' . $Programsetting->reportsPlusHours . ' hour +0 minutes', strtotime($yesterday)));
        } else {
            $endToday = $today . ' 23:59:59';
            $startToday = $today . " 00:00:00";
            //
            $endYesterday = $today . ' 23:59:59';
            $startYesterday = $today . " 00:00:00";
        }
        if (date('H') < $Programsetting->reportsPlusHours) {
            $startDate = $startYesterday;
            $endDate = $endYesterday;
        } else {
            $startDate = $startToday;
            $endDate = $endToday;
        }
        ##############
        if ($sWhere == '') {
            $queryString = ' WHERE';
        } else {
            $queryString = ' AND';
        }
        $queryString .= ' returnsellbilldate >= "' . $startDate . '" and  returnsellbilldate <= "' . $endDate . '" ';
    }
    $sWhere .= $queryString;
    //----------------------------------------------------------------------


    $sQuery = "SELECT SQL_CALC_FOUND_ROWS " . str_replace(" , ", " ", implode(", ", $aColumns)) . " FROM  $sTable $sWhere $sOrder $sLimit ";
    //echo $sQuery;
    $rResult = $returnSellBillEX->runSelectQuery($sQuery);

    /* Data set length after filtering */
    $sQuery = "SELECT COUNT(" . $sIndexColumn . ") as returnsellbillid  FROM  $sTable $sWhere "; //$sOrder
    $rResultFilterTotal = $returnSellBillEX->runSelectQuery($sQuery);
    foreach ($rResultFilterTotal as $oneis) {
        $countis = $oneis->returnsellbillid;
    }
    $iFilteredTotal = $countis;
    /* Total data set length */
//    $sQuery = "SELECT COUNT(" . $sIndexColumn . ") as returnsellbillid FROM  $sTable";
//    $rResultTotal = $returnSellBillEX->runSelectQuery($sQuery);
//
//
//    foreach ($rResultTotal as $oneis) {
//        $countit = $oneis->returnsellbillid;
//    }
//    //$aResultTotal = mysql_fetch_array($rResultTotal);
//    $iTotal = $countit;

    $output = array(
        "draw" => intval(filter_input(INPUT_POST, 'sEcho')), //$_REQUEST['draw'], //intval(filter_input(INPUT_POST, 'sEcho')),
        "recordsTotal" => intval($iTotal),
        "recordsFiltered" => intval($iFilteredTotal),
        "totals" => array(),
        "data" => array()
    );
    $rowno = 1;
    //    $sellQuantity = 0; //$piecesCount
    //    $sellbilltotalpayed = 0; //payedTotal
    //    $sellbillfinalbill = 0; //remainTotal
    $sellbillaftertotalbill = 0; //totalBills//totalBills2
    ##payments on bills
    $billIDs = array();
    foreach ($rResult as $aRow) {
        $billIDs[] = $aRow->$aColumns[0];
    }
    $billIDs = !empty($billIDs) ? array_unique($billIDs) : [0];
    $paymentDataArr = R::getAll('SELECT billid, sum(clientdebtchangeamount) as clientdebtchangeamount
                FROM clientdebtchange where tablename = "clientPayedDeptReturnSellBillsController.php" and billid in(' . implode(',', $billIDs) . ') group by billid');
    $paymentDataArr = customArrayIndexOne($paymentDataArr, 'billid');
    foreach ($rResult as $aRow) {
        $row = array();
        $paymentsOnBill = $paymentDataArr[$aRow->$aColumns[0]]['clientdebtchangeamount'];
        $payed = $paymentsOnBill + $aRow->returnsellbilltotalpayed;
        $waitVal = $aRow->returnsellbillaftertotalbill - $payed;
//        if ($waitVal > 0) {
        for ($i = 0; $i < count($aColumns); $i++) {
            switch ($aColumns[$i]) {
                case "returnsellbillid":
                    //                    if (isset($aRow->newSellbillidAfterEdit) && $aRow->newSellbillidAfterEdit > 0) { //<!--this bill is deleted for editing-->
                    //                        $class = "danger";
                    //                    } elseif ($aRow->conditions != 0) {
                    //                        $class = "dark";
                    //                    } elseif ($aRow->conditions == 0) {
                    //                        $class = "";
                    //                    }
                    //                    $row[] = "<span classOftr='" . $class . "'>$rowno</span>";
                    //                    $row[] = $aRow->$aColumns[$i];
                    $class = "";
                    if ($aRow->lastReviewType == 3 || $aRow->lastReviewType == 4) {
                        $class = "danger";
                    }
                    $row[] = '<a class="' . $class . '" href="returnsellbillController.php?do=showDetail&returnsellbillid=' . $aRow->$aColumns[0] . '" target="_blank">' . $aRow->$aColumns[0] . '</a>';
                    break;
                case "returnsellbillaftertotalbill":
                    $row[] = $aRow->$aColumns[$i];
                    break;
                case "returnsellbilltotalpayed":
                    if ($laterNakdiPayment != 1) {
                        $row[] = $payed; //$aRow->$aColumns[$i];
                        //                            $row[] = $waitVal;
                    }
                    break;
                case "returnsellbilldate":
                    $row[] = $aRow->$aColumns[$i];
                    break;
                case "billuser.employeename as useremployeename":
                    $row[] = $aRow->useremployeename;
                    break;
                case "clientname":
                    if ($aRow->returnsellbillclientid == 1) {
                        $aRow->$aColumns[$i] = "نقدي";
                        $aRow->$aColumns[$i] .= "($aRow->tempclientName)";
                    }
                    if ($aRow->$aColumns[$i] == '') {
                        $aRow->$aColumns[$i] = "$aRow->tempclientName";
                    }
                    //
                    $row[] = $aRow->$aColumns[$i];
                    break;
                case "employeename":
                    //if ($cashOnlyNotClient == -1) {
                    $row[] = $aRow->$aColumns[$i];
                    //}
                    break;
                case "user.employeename":
                    if ($aRow->lastReviewType == 1 || $aRow->lastReviewType == 4) { //reviewed so give him Review details
                        $row[] = $aRow->employeename;
                    }
                    break;
                case "lastReviewDateTime":
                    if ($aRow->lastReviewType == 1 || $aRow->lastReviewType == 4) { //reviewed so give him Review details
                        $row[] = $aRow->$aColumns[$i];
                    }
                    break;
                case "lastReviewType":
                    if ($laterNakdiPayment == 1) {
                        $row[] = '<input type="hidden" class="form-control positivefloat bill_pay_ret" value="' . $waitVal . '" data-id="' . $aRow->$aColumns[0] . '" data-ret="1" data-wait="' . $waitVal . '"/>'
                                . ' <p id="moneyReq' . $aRow->$aColumns[0] . '" style="color: #CC3333;border: 1px solid #CC3333;width: 10px;height:15px;display:none">*</p>'
                                . '<input type="radio"  class="paytype"  data-id="' . $aRow->$aColumns[0] . '" id="paytype_' . $aRow->$aColumns[0] . '"  name="paytype_' . $aRow->$aColumns[0] . '" value="save" checked="checked"> خزنة
                                  <input type="radio"  class="paytype"  data-id="' . $aRow->$aColumns[0] . '" id="paytype_' . $aRow->$aColumns[0] . '"  name="paytype_' . $aRow->$aColumns[0] . '" value="bank"> بنك
                                  <div id="save_Div_' . $aRow->$aColumns[0] . '">
                                      <input type="hidden" id="save_' . $aRow->$aColumns[0] . '" name="save_' . $aRow->$aColumns[0] . '"  class="wdt50 save"/>
                                          <p id="saveReq' . $aRow->$aColumns[0] . '" style="color: #CC3333;border: 1px solid #CC3333;width: 10px;height:15px;display:none">*</p>
                                  </div>
                                  <div id="bank_Div_' . $aRow->$aColumns[0] . '" style="display:none">
                                      <input type="hidden" id="bankAccount_' . $aRow->$aColumns[0] . '" name="bankAccount_' . $aRow->$aColumns[0] . '"  class="wdt50 bankAccount"/>
                                          <p id="bankReq' . $aRow->$aColumns[0] . '" style="color: #CC3333;border: 1px solid #CC3333;width: 10px;height:15px;display:none">*</p>
                                  </div>';
                        $row[] = '<button type="button" class="btn btn-success save_payment" data-id="' . $aRow->$aColumns[0] . '" data-ret="1">سداد</button>';
                    } else {
                        $name = $class = '';
                        if ($aRow->lastReviewType == 1 || $aRow->lastReviewType == 4) { //reviewed so give him Review details
                            //                        $row[] = '<a data-billid="' . $aRow->returnsellbillid . '" data-billtype="ret" type="button" class="btn btn-default btn-lg removeicon cancelreview"></a>';
                            $name = $class = 'choosedItemRetReviewd';
                        } else {
                            //                        $row[] = '<a data-billid="' . $aRow->returnsellbillid . '" data-billtype="ret" type="button" class="btn btn-default btn-lg correcticon review"></a>';
                            $name = $class = 'choosedItemRetNotReviewd';
                        }



                        //
                        $btn = '';
                        if ($aRow->lastReviewType == 3 || $aRow->lastReviewType == 4) { //has history
                            $btn .= '<a class="btn btn-default bookicon reviewInfo" style="display: inline !important;width:8%;text-align: center !important;padding: 5px 13px !important;margin: 5px;" data-id="' . $aRow->returnsellbillid . '" data-type="ret"></a>';
                        }
                        $btn .= '<input type="checkbox" name="' . $name . '[]" class="' . $class . '" value="' . $aRow->returnsellbillid . '" data-total="' . $aRow->returnsellbillaftertotalbill . '" data-client="' . $aRow->clientname . '" data-cid="' . $aRow->returnsellbillclientid . '"/>';
                        $row[] = $btn;
                    }
                    break;
                default:
                    break;
            }
        }


        // '10 returnsellQuantity', '11 returnsellbilltotalpayed', '12 returnsellbillfinalbill', '13 newSellbillidAfterEdit', '14 conditions', '15 dailyentryid');
        if ($aRow->conditions == 0) { //not del not the old edit of bill
            //            $sellQuantity += $aRow->returnsellQuantity;
            //            $sellbilltotalpayed += $aRow->returnsellbilltotalpayed;
            //            $sellbillfinalbill += $aRow->returnsellbillfinalbill;
            $sellbillaftertotalbill += $aRow->returnsellbillaftertotalbill;
        }
        $output['data'][] = $row;
        $rowno++;
//        }
    }
    //    $output['totals']["piecesCount"] = $sellQuantity;
    //    $output['totals']["payedTotal"] = $sellbilltotalpayed;
    //    $output['totals']["remainTotal"] = $sellbillfinalbill;
    $output['totals']["totalBills"] = $sellbillaftertotalbill;

    //print_r($output);
    echo json_encode($output, JSON_UNESCAPED_SLASHES);
}

function showallajaxretCash() {
    global $returnSellBillEX;
    global $ProgramsettingDAO;
    global $UsergroupDAO;
    $today = date('Y-m-d');

    $sIndexColumn = "returnsellbillid";

    /* DB table to use */
    //stable what is after from in sql statment
    $sTable = " returnsellbill
                LEFT JOIN client ON returnsellbill.returnsellbillclientid = client.clientid
                LEFT JOIN user ON user.userid = returnsellbill.lastReviewUser
                LEFT JOIN user billuser ON billuser.userid = returnsellbill.userid";
    ##this was right statment but slow
    //    $sTable = " returnsellbill
    //            LEFT JOIN client ON returnsellbill.returnsellbillclientid = client.clientid
    //            left join returnsellbillreview on (returnsellbillreview.billid = returnsellbill.returnsellbillid and returnsellbillreview.id in(
    //                SELECT  MAX(id)
    //                FROM returnsellbillreview
    //                GROUP BY billid
    //
    ##this was giving wrong data
    //    $sTable = " returnsellbill
    //            LEFT JOIN client ON returnsellbill.returnsellbillclientid = client.clientid
    //            left join (
    //                SELECT  returnsellbillreview.*,MAX(id) lastrecordid
    //                FROM returnsellbillreview
    //                GROUP BY billid
    //            ) as lastreview on (lastreview.billid = returnsellbill.returnsellbillid and lastreview.id = lastrecordid)";
    $aColumns = array('returnsellbillid', 'clientname', 'returnsellbillaftertotalbill', 'returnsellbilltotalpayed', 'returnsellbilldate', 'billuser.employeename as useremployeename', 'lastReviewDateTime', 'user.employeename', 'lastReviewType', 'tempclientName', 'returnsellbillclientid');

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

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

    /*
     * Ordering
     */
    $sOrder = "";

    if (isset($_POST['order'][0])) {
        $sOrder = "ORDER BY  ";
        for ($i = 0; $i < intval($_POST['columns']); $i++) {
            $orderByColumnIndex = $_POST['order'][0]['column'] - 1; //this - 1 is because first column is not counted as it is checkbox
            if ($aColumns[$orderByColumnIndex] == "billuser.employeename as useremployeename") {
                $sOrder .= "billuser.employeename
                " . ($_POST['order'][$i]['dir'] === 'asc' ? 'asc' : 'desc') . ", ";
            } else {
                $sOrder .= $aColumns[intval($_POST['columns'][$orderByColumnIndex]['data'])] . "
                " . ($_POST['order'][$i]['dir'] === 'asc' ? 'asc' : 'desc') . ", ";
            }
        }
        $sOrder = substr_replace($sOrder, "", -2);
        if ($sOrder == "ORDER BY ") {
            $sOrder = "";
        }
    }

    /*
     * Filtering
     * NOTE this does not match the built-in DataTables filtering which does it
     * word by word on any field. It's possible to do here, but concerned about efficiency
     * on very large tables, and MySQL's regex functionality is very limited
     */
    // where del =0 and aqarstatus =0 and updatestatus in(1,2,3,4) and complete=1
    $sWhere = '';
    if (isset($_POST['search']['value']) && $_POST['search']['value'] != "") {
        $sWhere = " where  (";
        for ($i = 0; $i < count($aColumns); $i++) {
            if ($aColumns[$i] == "returnsellbilldate" || $aColumns[$i] == "lastReviewDateTime") {
                //to get numbers and english chars only
                ##only alpha numeric is allowed . space not allowed
                $search = preg_replace('/[^#A-Za-z0-9]/', '', $_POST['search']['value']); // Removes special chars.
                if (!empty($search)) {
                    $sWhere .= $aColumns[$i] . " LIKE '%" . $search . "%' OR ";
                }
            } else if ($aColumns[$i] == "billuser.employeename as useremployeename") {
                $sWhere .= " billuser.employeename LIKE '%" . $_POST['search']['value'] . "%' OR ";
            } else {
                $sWhere .= $aColumns[$i] . " LIKE '%" . $_POST['search']['value'] . "%' OR ";
            }
        }
        $sWhere = substr_replace($sWhere, "", -3);
        $sWhere .= ")";
    }
    //-- search ------------------------------------------------------------
    if ($sWhere == '') {
        $queryString = ' WHERE';
    } else {
        $queryString = ' AND';
    }
    $message = "";
    $reviewType = $_REQUEST['reviewType'];
    $startDate = $_REQUEST['from'];
    $endDate = $_REQUEST['to'];
    $tabDateGuide = $_REQUEST['tabDateGuide'];
    $sellbillSerial = $_REQUEST['sellbillserial'];
    $sellbillId = $_REQUEST['sellbillid']; //$_REQUEST['returnsellbillid'];
    $clientId = (int) $_REQUEST['clientid'];
    $sellerid = $_REQUEST['sellerid'];
    $selleruserid = $_REQUEST['selleruserid'];
    $storeid = $_REQUEST['storeid'];
    $saveid = $_REQUEST['saveid'];
    $tempclientName = $_REQUEST['tempclientName'];
    //    $pricetype = $_REQUEST['pricetype'];
    $cashOnlyNotClient = (int) $_REQUEST['cashOnlyNotClient']; //1 for nakdi "clientid=1",0 for normal clients "clientid > 1",-1 for all kinds "no conditions"
    $typeClient = (int) $_REQUEST['typeClient'];
    $laterNakdiPayment = (int) $_REQUEST['laterNakdiPayment'];

    $search = $_REQUEST['searchDate'];
    if ($search == "null" || empty($search)) {
        $search = "dateOnly";
    }
    ##needed Data
    $Programsetting = $ProgramsettingDAO->load(1);
    //$usergroup = $UsergroupDAO->load($_SESSION['usergroupid']);
    ############
    $queryString .= ' returnsellbill.conditions = 0 AND'; ///
    if (isset($reviewType)) {
        if ($reviewType == 0) {
            $queryString .= ' lastReviewType in (0,3) AND';
        } else {
            $queryString .= ' lastReviewType in (1,4) AND';
        }
    }
    if ($laterNakdiPayment == 1) {
        $userIds = R::getCell('select laterNakdiUserIds from user where userid=' . $_SESSION['userid']);
        if ($userIds != 0)
            $queryString .= ' returnsellbill.userid in(' . $userIds . ') AND';
    }
    ////
    if ($cashOnlyNotClient == 0) { //ignore -1 case
        $queryString .= ' returnsellbillclientid != 1 AND';
    } elseif ($cashOnlyNotClient == 1) {
        $queryString .= ' returnsellbillclientid = 1 AND';
    }

    if (isset($clientId) && $clientId > 0) {
        $queryString .= ' returnsellbillclientid = "' . $clientId . '" AND';
    }
    ##
    if ($laterNakdiPayment == 1) { ///for eng.sherif i think for one user @reports laterNakdiPayment , laterNakdiPaymentSell , laterNakdiPaymentRet
        $queryString .= ' client.typeclientid not like "%,1,%" AND';
    } else if (isset($typeClient) && $typeClient != -1) {
        $queryString .= ' (client.typeclientid like "%,' . $typeClient . ',%" or client.typeclientid = ",-20," or client.typeclientid = "," or client.typeclientid = "") AND';
    }
    ##
    if (isset($sellerid) && $sellerid != "-1") {
        $queryString .= ' sellerid = ' . $sellerid . ' AND';
    }
    if (isset($selleruserid) && $selleruserid != "-1") {
        $queryString .= ' returnsellbill.userid = ' . $selleruserid . ' AND';
    }
    if (isset($sellbillSerial) && $sellbillSerial != "" && $sellbillSerial != '-1') {
        $queryString .= ' returnsellbillserial = "' . $sellbillSerial . '" AND';
    }
    if (isset($sellbillId) && $sellbillId != "") {
        $queryString .= ' returnsellbillid = ' . $sellbillId . ' AND';
    }
    if (isset($storeid) && $storeid != "-1") {
        $queryString .= ' returnsellbillstoreid = ' . $storeid . ' AND';
    }
    if (isset($saveid) && $saveid != "-1") {
        $queryString .= ' returnsellbillsaveid = ' . $saveid . ' AND';
    }
    if (isset($tempclientName) && $tempclientName != "") {
        $queryString .= ' returnsellbill.tempclientName like "%' . $tempclientName . '%" AND';
    }
    ##fatma
    /* if (isset($tabDateGuide) && !empty($tabDateGuide)) {
      //display the converted time ##reportsPlusHours
      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
      $endDateToday = date('Y-m-d H:i:s', strtotime('+' . $reportsPlusHours . ' hour +0 minutes', strtotime($today)));
      $startDateToday = date('Y-m-d H:i:s', strtotime('+' . $Programsetting->reportsPlusHours . ' hour +0 minutes', strtotime($today)));
      } else {
      $endDateToday = $endDateToday . ' 23:59:59';
      $startDateToday = $startDateToday . " 00:00:00";
      }
      //}
      if ($tabDateGuide == "today") {
      $queryString .= ' returnsellbilldate >= "' . $startDateToday . '" and  returnsellbilldate <= "' . $endDateToday . '" AND';
      } elseif ($tabDateGuide == "beforeToday") {
      $queryString .= ' returnsellbilldate < "' . $startDateToday . '" AND';
      }
      } */
    if (isset($startDate) && $startDate != "" && isset($endDate) && $endDate != "") { // && $tabDateGuide != "today"
        //display the converted time ##reportsPlusHours
        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 {
            $endDate = $endDate . ' 23:59:59';
            $startDate = $startDate . " 00:00:00";
        }
        $queryString .= ' returnsellbilldate >= "' . $startDate . '" and  returnsellbilldate <= "' . $endDate . '" AND';
    }
    //    if (isset($pricetype) && $pricetype != "-1") {
    //        $queryString .= ' pricetype = ' . $pricetype . ' AND';
    //    }
    ##############
    ##fatma

    $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);
        }
        if ($lastWord == 'WHERE') { //remove it
            array_pop($arr);
            //form the string again
            $queryString = implode(' ', $arr);
        }

        //print("<br>queryString: ".$queryString."<br>");
    }

    $totalBills = 0;

    if ($queryString == "") {
        $today = date("Y-m-d");
        //display the converted time ##reportsPlusHours
        $Programsetting = $ProgramsettingDAO->load(1);
        ################
        $today = date('Y-m-d');
        $yesterday = date("Y-m-d", strtotime('-1 days'));
        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
            $endToday = date('Y-m-d H:i:s', strtotime('+' . $reportsPlusHours . ' hour +0 minutes', strtotime($today)));
            $startToday = date('Y-m-d H:i:s', strtotime('+' . $Programsetting->reportsPlusHours . ' hour +0 minutes', strtotime($today)));
            //
            $endYesterday = date('Y-m-d H:i:s', strtotime('+' . $reportsPlusHours . ' hour +0 minutes', strtotime($yesterday)));
            $startYesterday = date('Y-m-d H:i:s', strtotime('+' . $Programsetting->reportsPlusHours . ' hour +0 minutes', strtotime($yesterday)));
        } else {
            $endToday = $today . ' 23:59:59';
            $startToday = $today . " 00:00:00";
            //
            $endYesterday = $today . ' 23:59:59';
            $startYesterday = $today . " 00:00:00";
        }
        if (date('H') < $Programsetting->reportsPlusHours) {
            $startDate = $startYesterday;
            $endDate = $endYesterday;
        } else {
            $startDate = $startToday;
            $endDate = $endToday;
        }
        ##############
        if ($sWhere == '') {
            $queryString = ' WHERE';
        } else {
            $queryString = ' AND';
        }
        $queryString .= ' returnsellbilldate >= "' . $startDate . '" and  returnsellbilldate <= "' . $endDate . '" ';
    }
    $sWhere .= $queryString;
    //----------------------------------------------------------------------
    if ($sWhere == '') {
        $sWhere = ' WHERE returnsellbilltotalpayed > 0 ';
    } else {
        $sWhere .= ' AND returnsellbilltotalpayed > 0 ';
    }

    $sQuery = "SELECT SQL_CALC_FOUND_ROWS " . str_replace(" , ", " ", implode(", ", $aColumns)) . " FROM  $sTable $sWhere $sOrder $sLimit ";
    //echo $sQuery;
    $rResult = $returnSellBillEX->runSelectQuery($sQuery);

    /* Data set length after filtering */
    $sQuery = "SELECT COUNT(" . $sIndexColumn . ") as returnsellbillid  FROM  $sTable $sWhere "; //$sOrder
    $rResultFilterTotal = $returnSellBillEX->runSelectQuery($sQuery);
    foreach ($rResultFilterTotal as $oneis) {
        $countis = $oneis->returnsellbillid;
    }
    $iFilteredTotal = $countis;
    /* Total data set length */
//    $sQuery = "SELECT COUNT(" . $sIndexColumn . ") as returnsellbillid FROM  $sTable";
//    $rResultTotal = $returnSellBillEX->runSelectQuery($sQuery);
//
//
//    foreach ($rResultTotal as $oneis) {
//        $countit = $oneis->returnsellbillid;
//    }
//    //$aResultTotal = mysql_fetch_array($rResultTotal);
//    $iTotal = $countit;

    $output = array(
        "draw" => intval(filter_input(INPUT_POST, 'sEcho')), //$_REQUEST['draw'], //intval(filter_input(INPUT_POST, 'sEcho')),
        "recordsTotal" => intval($iTotal),
        "recordsFiltered" => intval($iFilteredTotal),
        "totals" => array(),
        "data" => array()
    );
    $rowno = 1;
    //    $sellQuantity = 0; //$piecesCount
    //    $sellbilltotalpayed = 0; //payedTotal
    //    $sellbillfinalbill = 0; //remainTotal
    $sellbillaftertotalbill = 0; //totalBills//totalBills2
    ##payments on bills
    $billIDs = array();
    foreach ($rResult as $aRow) {
        $billIDs[] = $aRow->$aColumns[0];
    }
    $billIDs = !empty($billIDs) ? array_unique($billIDs) : [0];
    $paymentDataArr = R::getAll('SELECT billid, sum(clientdebtchangeamount) as clientdebtchangeamount
                FROM clientdebtchange where tablename = "clientPayedDeptReturnSellBillsController.php" and billid in(' . implode(',', $billIDs) . ') group by billid');
    $paymentDataArr = customArrayIndexOne($paymentDataArr, 'billid');
    foreach ($rResult as $aRow) {
        $row = array();
        $paymentsOnBill = $paymentDataArr[$aRow->$aColumns[0]]['clientdebtchangeamount'];
        $payed = $paymentsOnBill + $aRow->returnsellbilltotalpayed;
        $waitVal = $aRow->returnsellbillaftertotalbill - $payed;
//        if ($waitVal > 0) {
        for ($i = 0; $i < count($aColumns); $i++) {
            switch ($aColumns[$i]) {
                case "returnsellbillid":
                    //                    if (isset($aRow->newSellbillidAfterEdit) && $aRow->newSellbillidAfterEdit > 0) { //<!--this bill is deleted for editing-->
                    //                        $class = "danger";
                    //                    } elseif ($aRow->conditions != 0) {
                    //                        $class = "dark";
                    //                    } elseif ($aRow->conditions == 0) {
                    //                        $class = "";
                    //                    }
                    //                    $row[] = "<span classOftr='" . $class . "'>$rowno</span>";
                    //                    $row[] = $aRow->$aColumns[$i];
                    $class = "";
                    if ($aRow->lastReviewType == 3 || $aRow->lastReviewType == 4) {
                        $class = "danger";
                    }
                    $row[] = '<a class="' . $class . '" href="returnsellbillController.php?do=showDetail&returnsellbillid=' . $aRow->$aColumns[0] . '" target="_blank">' . $aRow->$aColumns[0] . '</a>';
                    break;
                case "returnsellbillaftertotalbill":
                    $row[] = $aRow->$aColumns[$i];
                    break;
                case "returnsellbilltotalpayed":
                    if ($laterNakdiPayment != 1) {
                        $row[] = $payed; //$aRow->$aColumns[$i];
                        //                            $row[] = $waitVal;
                    }
                    break;
                case "returnsellbilldate":
                    $row[] = $aRow->$aColumns[$i];
                    break;
                case "billuser.employeename as useremployeename":
                    $row[] = $aRow->useremployeename;
                    break;
                case "clientname":
                    if ($aRow->returnsellbillclientid == 1) {
                        $aRow->$aColumns[$i] = "نقدي";
                        $aRow->$aColumns[$i] .= "($aRow->tempclientName)";
                    }
                    if ($aRow->$aColumns[$i] == '') {
                        $aRow->$aColumns[$i] = "$aRow->tempclientName";
                    }
                    //
                    $row[] = $aRow->$aColumns[$i];
                    break;
                case "employeename":
                    //if ($cashOnlyNotClient == -1) {
                    $row[] = $aRow->$aColumns[$i];
                    //}
                    break;
                case "user.employeename":
                    if ($aRow->lastReviewType == 1 || $aRow->lastReviewType == 4) { //reviewed so give him Review details
                        $row[] = $aRow->employeename;
                    }
                    break;
                case "lastReviewDateTime":
                    if ($aRow->lastReviewType == 1 || $aRow->lastReviewType == 4) { //reviewed so give him Review details
                        $row[] = $aRow->$aColumns[$i];
                    }
                    break;
                case "lastReviewType":
                    if ($laterNakdiPayment == 1) {
                        $row[] = '<input type="hidden" class="form-control positivefloat bill_pay_ret" value="' . $waitVal . '" data-id="' . $aRow->$aColumns[0] . '" data-ret="1" data-wait="' . $waitVal . '"/>'
                                . ' <p id="moneyReq' . $aRow->$aColumns[0] . '" style="color: #CC3333;border: 1px solid #CC3333;width: 10px;height:15px;display:none">*</p>'
                                . '<input type="radio"  class="paytype"  data-id="' . $aRow->$aColumns[0] . '" id="paytype_' . $aRow->$aColumns[0] . '"  name="paytype_' . $aRow->$aColumns[0] . '" value="save" checked="checked"> خزنة
                                  <input type="radio"  class="paytype"  data-id="' . $aRow->$aColumns[0] . '" id="paytype_' . $aRow->$aColumns[0] . '"  name="paytype_' . $aRow->$aColumns[0] . '" value="bank"> بنك
                                  <div id="save_Div_' . $aRow->$aColumns[0] . '">
                                      <input type="hidden" id="save_' . $aRow->$aColumns[0] . '" name="save_' . $aRow->$aColumns[0] . '"  class="wdt50 save"/>
                                          <p id="saveReq' . $aRow->$aColumns[0] . '" style="color: #CC3333;border: 1px solid #CC3333;width: 10px;height:15px;display:none">*</p>
                                  </div>
                                  <div id="bank_Div_' . $aRow->$aColumns[0] . '" style="display:none">
                                      <input type="hidden" id="bankAccount_' . $aRow->$aColumns[0] . '" name="bankAccount_' . $aRow->$aColumns[0] . '"  class="wdt50 bankAccount"/>
                                          <p id="bankReq' . $aRow->$aColumns[0] . '" style="color: #CC3333;border: 1px solid #CC3333;width: 10px;height:15px;display:none">*</p>
                                  </div>';
                        $row[] = '<button type="button" class="btn btn-success save_payment" data-id="' . $aRow->$aColumns[0] . '" data-ret="1">سداد</button>';
                    } else {
                        $name = $class = '';
                        if ($aRow->lastReviewType == 1 || $aRow->lastReviewType == 4) { //reviewed so give him Review details
                            //                        $row[] = '<a data-billid="' . $aRow->returnsellbillid . '" data-billtype="ret" type="button" class="btn btn-default btn-lg removeicon cancelreview"></a>';
                            $name = $class = 'choosedItemRetReviewd';
                        } else {
                            //                        $row[] = '<a data-billid="' . $aRow->returnsellbillid . '" data-billtype="ret" type="button" class="btn btn-default btn-lg correcticon review"></a>';
                            $name = $class = 'choosedItemRetNotReviewd';
                        }



                        //
                        $btn = '';
                        if ($aRow->lastReviewType == 3 || $aRow->lastReviewType == 4) { //has history
                            $btn .= '<a class="btn btn-default bookicon reviewInfo" style="display: inline !important;width:8%;text-align: center !important;padding: 5px 13px !important;margin: 5px;" data-id="' . $aRow->returnsellbillid . '" data-type="ret"></a>';
                        }
                        $btn .= '<input type="checkbox" name="' . $name . '[]" class="' . $class . '" value="' . $aRow->returnsellbillid . '" data-total="' . $aRow->returnsellbillaftertotalbill . '" data-client="' . $aRow->clientname . '" data-cid="' . $aRow->returnsellbillclientid . '"/>';
                        $row[] = $btn;
                    }
                    break;
                default:
                    break;
            }
        }


        // '10 returnsellQuantity', '11 returnsellbilltotalpayed', '12 returnsellbillfinalbill', '13 newSellbillidAfterEdit', '14 conditions', '15 dailyentryid');
        if ($aRow->conditions == 0) { //not del not the old edit of bill
            //            $sellQuantity += $aRow->returnsellQuantity;
            //            $sellbilltotalpayed += $aRow->returnsellbilltotalpayed;
            //            $sellbillfinalbill += $aRow->returnsellbillfinalbill;
            $sellbillaftertotalbill += $aRow->returnsellbillaftertotalbill;
        }
        $output['data'][] = $row;
        $rowno++;
//        }
    }
    //    $output['totals']["piecesCount"] = $sellQuantity;
    //    $output['totals']["payedTotal"] = $sellbilltotalpayed;
    //    $output['totals']["remainTotal"] = $sellbillfinalbill;
    $output['totals']["totalBills"] = $sellbillaftertotalbill;

    //print_r($output);
    echo json_encode($output, JSON_UNESCAPED_SLASHES);
}

function showallajaxretBook() {
    global $returnSellBillEX;
    global $ProgramsettingDAO;
    global $UsergroupDAO;
    $today = date('Y-m-d');

    $sIndexColumn = "returnsellbillid";

    /* DB table to use */
    //stable what is after from in sql statment
    $sTable = " returnsellbill
                LEFT JOIN client ON returnsellbill.returnsellbillclientid = client.clientid
                LEFT JOIN user ON user.userid = returnsellbill.lastReviewUser
                LEFT JOIN user billuser ON billuser.userid = returnsellbill.userid";
    ##this was right statment but slow
    //    $sTable = " returnsellbill
    //            LEFT JOIN client ON returnsellbill.returnsellbillclientid = client.clientid
    //            left join returnsellbillreview on (returnsellbillreview.billid = returnsellbill.returnsellbillid and returnsellbillreview.id in(
    //                SELECT  MAX(id)
    //                FROM returnsellbillreview
    //                GROUP BY billid
    //
    ##this was giving wrong data
    //    $sTable = " returnsellbill
    //            LEFT JOIN client ON returnsellbill.returnsellbillclientid = client.clientid
    //            left join (
    //                SELECT  returnsellbillreview.*,MAX(id) lastrecordid
    //                FROM returnsellbillreview
    //                GROUP BY billid
    //            ) as lastreview on (lastreview.billid = returnsellbill.returnsellbillid and lastreview.id = lastrecordid)";
    $aColumns = array('returnsellbillid', 'clientname', 'returnsellbillaftertotalbill', 'returnsellbilltotalpayed', 'returnsellbilldate', 'billuser.employeename as useremployeename', 'lastReviewDateTime', 'user.employeename', 'lastReviewType', 'tempclientName', 'returnsellbillclientid');

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

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

    /*
     * Ordering
     */
    $sOrder = "";

    if (isset($_POST['order'][0])) {
        $sOrder = "ORDER BY  ";
        for ($i = 0; $i < intval($_POST['columns']); $i++) {
            $orderByColumnIndex = $_POST['order'][0]['column'] - 1; //this - 1 is because first column is not counted as it is checkbox
            if ($aColumns[$orderByColumnIndex] == "billuser.employeename as useremployeename") {
                $sOrder .= "billuser.employeename
                " . ($_POST['order'][$i]['dir'] === 'asc' ? 'asc' : 'desc') . ", ";
            } else {
                $sOrder .= $aColumns[intval($_POST['columns'][$orderByColumnIndex]['data'])] . "
                " . ($_POST['order'][$i]['dir'] === 'asc' ? 'asc' : 'desc') . ", ";
            }
        }
        $sOrder = substr_replace($sOrder, "", -2);
        if ($sOrder == "ORDER BY ") {
            $sOrder = "";
        }
    }

    /*
     * Filtering
     * NOTE this does not match the built-in DataTables filtering which does it
     * word by word on any field. It's possible to do here, but concerned about efficiency
     * on very large tables, and MySQL's regex functionality is very limited
     */
    // where del =0 and aqarstatus =0 and updatestatus in(1,2,3,4) and complete=1
    $sWhere = '';
    if (isset($_POST['search']['value']) && $_POST['search']['value'] != "") {
        $sWhere = " where  (";
        for ($i = 0; $i < count($aColumns); $i++) {
            if ($aColumns[$i] == "returnsellbilldate" || $aColumns[$i] == "lastReviewDateTime") {
                //to get numbers and english chars only
                ##only alpha numeric is allowed . space not allowed
                $search = preg_replace('/[^#A-Za-z0-9]/', '', $_POST['search']['value']); // Removes special chars.
                if (!empty($search)) {
                    $sWhere .= $aColumns[$i] . " LIKE '%" . $search . "%' OR ";
                }
            } else if ($aColumns[$i] == "billuser.employeename as useremployeename") {
                $sWhere .= " billuser.employeename LIKE '%" . $_POST['search']['value'] . "%' OR ";
            } else {
                $sWhere .= $aColumns[$i] . " LIKE '%" . $_POST['search']['value'] . "%' OR ";
            }
        }
        $sWhere = substr_replace($sWhere, "", -3);
        $sWhere .= ")";
    }
    //-- search ------------------------------------------------------------
    if ($sWhere == '') {
        $queryString = ' WHERE';
    } else {
        $queryString = ' AND';
    }
    $message = "";
    $reviewType = $_REQUEST['reviewType'];
    $startDate = $_REQUEST['from'];
    $endDate = $_REQUEST['to'];
    $tabDateGuide = $_REQUEST['tabDateGuide'];
    $sellbillSerial = $_REQUEST['sellbillserial'];
    $sellbillId = $_REQUEST['sellbillid']; //$_REQUEST['returnsellbillid'];
    $clientId = (int) $_REQUEST['clientid'];
    $sellerid = $_REQUEST['sellerid'];
    $selleruserid = $_REQUEST['selleruserid'];
    $storeid = $_REQUEST['storeid'];
    $saveid = $_REQUEST['saveid'];
    $tempclientName = $_REQUEST['tempclientName'];
    //    $pricetype = $_REQUEST['pricetype'];
    $cashOnlyNotClient = (int) $_REQUEST['cashOnlyNotClient']; //1 for nakdi "clientid=1",0 for normal clients "clientid > 1",-1 for all kinds "no conditions"
    $typeClient = (int) $_REQUEST['typeClient'];
    $laterNakdiPayment = (int) $_REQUEST['laterNakdiPayment'];

    $search = $_REQUEST['searchDate'];
    if ($search == "null" || empty($search)) {
        $search = "dateOnly";
    }
    ##needed Data
    $Programsetting = $ProgramsettingDAO->load(1);
    //$usergroup = $UsergroupDAO->load($_SESSION['usergroupid']);
    ############
    $queryString .= ' returnsellbill.conditions = 0 AND'; ///
    if (isset($reviewType)) {
        if ($reviewType == 0) {
            $queryString .= ' lastReviewType in (0,3) AND';
        } else {
            $queryString .= ' lastReviewType in (1,4) AND';
        }
    }
    if ($laterNakdiPayment == 1) {
        $userIds = R::getCell('select laterNakdiUserIds from user where userid=' . $_SESSION['userid']);
        if ($userIds != 0)
            $queryString .= ' returnsellbill.userid in(' . $userIds . ') AND';
    }
    ////
    if ($cashOnlyNotClient == 0) { //ignore -1 case
        $queryString .= ' returnsellbillclientid != 1 AND';
    } elseif ($cashOnlyNotClient == 1) {
        $queryString .= ' returnsellbillclientid = 1 AND';
    }

    if (isset($clientId) && $clientId > 0) {
        $queryString .= ' returnsellbillclientid = "' . $clientId . '" AND';
    }
    ##
    if ($laterNakdiPayment == 1) { ///for eng.sherif i think for one user @reports laterNakdiPayment , laterNakdiPaymentSell , laterNakdiPaymentRet
        $queryString .= ' client.typeclientid not like "%,1,%" AND';
    } else if (isset($typeClient) && $typeClient != -1) {
        $queryString .= ' (client.typeclientid like "%,' . $typeClient . ',%" or client.typeclientid = ",-20," or client.typeclientid = "," or client.typeclientid = "") AND';
    }
    ##
    if (isset($sellerid) && $sellerid != "-1") {
        $queryString .= ' sellerid = ' . $sellerid . ' AND';
    }
    if (isset($selleruserid) && $selleruserid != "-1") {
        $queryString .= ' returnsellbill.userid = ' . $selleruserid . ' AND';
    }
    if (isset($sellbillSerial) && $sellbillSerial != "" && $sellbillSerial != '-1') {
        $queryString .= ' returnsellbillserial = "' . $sellbillSerial . '" AND';
    }
    if (isset($sellbillId) && $sellbillId != "") {
        $queryString .= ' returnsellbillid = ' . $sellbillId . ' AND';
    }
    if (isset($storeid) && $storeid != "-1") {
        $queryString .= ' returnsellbillstoreid = ' . $storeid . ' AND';
    }
    if (isset($saveid) && $saveid != "-1") {
        $queryString .= ' returnsellbillsaveid = ' . $saveid . ' AND';
    }
    if (isset($tempclientName) && $tempclientName != "") {
        $queryString .= ' returnsellbill.tempclientName like "%' . $tempclientName . '%" AND';
    }
    ##fatma
    /* if (isset($tabDateGuide) && !empty($tabDateGuide)) {
      //display the converted time ##reportsPlusHours
      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
      $endDateToday = date('Y-m-d H:i:s', strtotime('+' . $reportsPlusHours . ' hour +0 minutes', strtotime($today)));
      $startDateToday = date('Y-m-d H:i:s', strtotime('+' . $Programsetting->reportsPlusHours . ' hour +0 minutes', strtotime($today)));
      } else {
      $endDateToday = $endDateToday . ' 23:59:59';
      $startDateToday = $startDateToday . " 00:00:00";
      }
      //}
      if ($tabDateGuide == "today") {
      $queryString .= ' returnsellbilldate >= "' . $startDateToday . '" and  returnsellbilldate <= "' . $endDateToday . '" AND';
      } elseif ($tabDateGuide == "beforeToday") {
      $queryString .= ' returnsellbilldate < "' . $startDateToday . '" AND';
      }
      } */
    if (isset($startDate) && $startDate != "" && isset($endDate) && $endDate != "") { // && $tabDateGuide != "today"
        //display the converted time ##reportsPlusHours
        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 {
            $endDate = $endDate . ' 23:59:59';
            $startDate = $startDate . " 00:00:00";
        }
        $queryString .= ' returnsellbilldate >= "' . $startDate . '" and  returnsellbilldate <= "' . $endDate . '" AND';
    }
    //    if (isset($pricetype) && $pricetype != "-1") {
    //        $queryString .= ' pricetype = ' . $pricetype . ' AND';
    //    }
    ##############
    ##fatma

    $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);
        }
        if ($lastWord == 'WHERE') { //remove it
            array_pop($arr);
            //form the string again
            $queryString = implode(' ', $arr);
        }

        //print("<br>queryString: ".$queryString."<br>");
    }

    $totalBills = 0;

    if ($queryString == "") {
        $today = date("Y-m-d");
        //display the converted time ##reportsPlusHours
        $Programsetting = $ProgramsettingDAO->load(1);
        ################
        $today = date('Y-m-d');
        $yesterday = date("Y-m-d", strtotime('-1 days'));
        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
            $endToday = date('Y-m-d H:i:s', strtotime('+' . $reportsPlusHours . ' hour +0 minutes', strtotime($today)));
            $startToday = date('Y-m-d H:i:s', strtotime('+' . $Programsetting->reportsPlusHours . ' hour +0 minutes', strtotime($today)));
            //
            $endYesterday = date('Y-m-d H:i:s', strtotime('+' . $reportsPlusHours . ' hour +0 minutes', strtotime($yesterday)));
            $startYesterday = date('Y-m-d H:i:s', strtotime('+' . $Programsetting->reportsPlusHours . ' hour +0 minutes', strtotime($yesterday)));
        } else {
            $endToday = $today . ' 23:59:59';
            $startToday = $today . " 00:00:00";
            //
            $endYesterday = $today . ' 23:59:59';
            $startYesterday = $today . " 00:00:00";
        }
        if (date('H') < $Programsetting->reportsPlusHours) {
            $startDate = $startYesterday;
            $endDate = $endYesterday;
        } else {
            $startDate = $startToday;
            $endDate = $endToday;
        }
        ##############
        if ($sWhere == '') {
            $queryString = ' WHERE';
        } else {
            $queryString = ' AND';
        }
        $queryString .= ' returnsellbilldate >= "' . $startDate . '" and  returnsellbilldate <= "' . $endDate . '" ';
    }
    $sWhere .= $queryString;
    //----------------------------------------------------------------------
    if ($sWhere == '') {
        $sWhere = ' WHERE returnsellbilltotalpayed <= 0 ';
    } else {
        $sWhere .= ' AND returnsellbilltotalpayed <= 0 ';
    }

    $sQuery = "SELECT SQL_CALC_FOUND_ROWS " . str_replace(" , ", " ", implode(", ", $aColumns)) . " FROM  $sTable $sWhere $sOrder $sLimit ";
    //echo $sQuery;
    $rResult = $returnSellBillEX->runSelectQuery($sQuery);

    /* Data set length after filtering */
    $sQuery = "SELECT COUNT(" . $sIndexColumn . ") as returnsellbillid  FROM  $sTable $sWhere "; //$sOrder
    $rResultFilterTotal = $returnSellBillEX->runSelectQuery($sQuery);
    foreach ($rResultFilterTotal as $oneis) {
        $countis = $oneis->returnsellbillid;
    }
    $iFilteredTotal = $countis;
    /* Total data set length */
//    $sQuery = "SELECT COUNT(" . $sIndexColumn . ") as returnsellbillid FROM  $sTable";
//    $rResultTotal = $returnSellBillEX->runSelectQuery($sQuery);
//
//
//    foreach ($rResultTotal as $oneis) {
//        $countit = $oneis->returnsellbillid;
//    }
//    //$aResultTotal = mysql_fetch_array($rResultTotal);
//    $iTotal = $countit;

    $output = array(
        "draw" => intval(filter_input(INPUT_POST, 'sEcho')), //$_REQUEST['draw'], //intval(filter_input(INPUT_POST, 'sEcho')),
        "recordsTotal" => intval($iTotal),
        "recordsFiltered" => intval($iFilteredTotal),
        "totals" => array(),
        "data" => array()
    );
    $rowno = 1;
    //    $sellQuantity = 0; //$piecesCount
    //    $sellbilltotalpayed = 0; //payedTotal
    //    $sellbillfinalbill = 0; //remainTotal
    $sellbillaftertotalbill = 0; //totalBills//totalBills2
    ##payments on bills
    $billIDs = array();
    foreach ($rResult as $aRow) {
        $billIDs[] = $aRow->$aColumns[0];
    }
    $billIDs = !empty($billIDs) ? array_unique($billIDs) : [0];
    $paymentDataArr = R::getAll('SELECT billid, sum(clientdebtchangeamount) as clientdebtchangeamount
                FROM clientdebtchange where tablename = "clientPayedDeptReturnSellBillsController.php" and billid in(' . implode(',', $billIDs) . ') group by billid');
    $paymentDataArr = customArrayIndexOne($paymentDataArr, 'billid');
    foreach ($rResult as $aRow) {
        $row = array();
        $paymentsOnBill = $paymentDataArr[$aRow->$aColumns[0]]['clientdebtchangeamount'];
        $payed = $paymentsOnBill + $aRow->returnsellbilltotalpayed;
        $waitVal = $aRow->returnsellbillaftertotalbill - $payed;
        if ($waitVal > 0) {
            for ($i = 0; $i < count($aColumns); $i++) {
                switch ($aColumns[$i]) {
                    case "returnsellbillid":
                        //                    if (isset($aRow->newSellbillidAfterEdit) && $aRow->newSellbillidAfterEdit > 0) { //<!--this bill is deleted for editing-->
                        //                        $class = "danger";
                        //                    } elseif ($aRow->conditions != 0) {
                        //                        $class = "dark";
                        //                    } elseif ($aRow->conditions == 0) {
                        //                        $class = "";
                        //                    }
                        //                    $row[] = "<span classOftr='" . $class . "'>$rowno</span>";
                        //                    $row[] = $aRow->$aColumns[$i];
                        $class = "";
                        if ($aRow->lastReviewType == 3 || $aRow->lastReviewType == 4) {
                            $class = "danger";
                        }
                        $row[] = '<a class="' . $class . '" href="returnsellbillController.php?do=showDetail&returnsellbillid=' . $aRow->$aColumns[0] . '" target="_blank">' . $aRow->$aColumns[0] . '</a>';
                        break;
                    case "returnsellbillaftertotalbill":
                        $row[] = $aRow->$aColumns[$i];
                        break;
                    case "returnsellbilltotalpayed":
                        if ($laterNakdiPayment != 1) {
                            $row[] = $payed; //$aRow->$aColumns[$i];
                            //                            $row[] = $waitVal;
                        }
                        break;
                    case "returnsellbilldate":
                        $row[] = $aRow->$aColumns[$i];
                        break;
                    case "billuser.employeename as useremployeename":
                        $row[] = $aRow->useremployeename;
                        break;
                    case "clientname":
                        if ($aRow->returnsellbillclientid == 1) {
                            $aRow->$aColumns[$i] = "نقدي";
                            $aRow->$aColumns[$i] .= "($aRow->tempclientName)";
                        }
                        if ($aRow->$aColumns[$i] == '') {
                            $aRow->$aColumns[$i] = "$aRow->tempclientName";
                        }
                        //
                        $row[] = $aRow->$aColumns[$i];
                        break;
                    case "employeename":
                        //if ($cashOnlyNotClient == -1) {
                        $row[] = $aRow->$aColumns[$i];
                        //}
                        break;
                    case "user.employeename":
                        if ($aRow->lastReviewType == 1 || $aRow->lastReviewType == 4) { //reviewed so give him Review details
                            $row[] = $aRow->employeename;
                        }
                        break;
                    case "lastReviewDateTime":
                        if ($aRow->lastReviewType == 1 || $aRow->lastReviewType == 4) { //reviewed so give him Review details
                            $row[] = $aRow->$aColumns[$i];
                        }
                        break;
                    case "lastReviewType":
                        if ($laterNakdiPayment == 1) {
                            $row[] = '<input type="hidden" class="form-control positivefloat bill_pay_ret" value="' . $waitVal . '" data-id="' . $aRow->$aColumns[0] . '" data-ret="1" data-wait="' . $waitVal . '"/>'
                                    . ' <p id="moneyReq' . $aRow->$aColumns[0] . '" style="color: #CC3333;border: 1px solid #CC3333;width: 10px;height:15px;display:none">*</p>'
                                    . '<input type="radio"  class="paytype"  data-id="' . $aRow->$aColumns[0] . '" id="paytype_' . $aRow->$aColumns[0] . '"  name="paytype_' . $aRow->$aColumns[0] . '" value="save" checked="checked"> خزنة
                                  <input type="radio"  class="paytype"  data-id="' . $aRow->$aColumns[0] . '" id="paytype_' . $aRow->$aColumns[0] . '"  name="paytype_' . $aRow->$aColumns[0] . '" value="bank"> بنك
                                  <div id="save_Div_' . $aRow->$aColumns[0] . '">
                                      <input type="hidden" id="save_' . $aRow->$aColumns[0] . '" name="save_' . $aRow->$aColumns[0] . '"  class="wdt50 save"/>
                                          <p id="saveReq' . $aRow->$aColumns[0] . '" style="color: #CC3333;border: 1px solid #CC3333;width: 10px;height:15px;display:none">*</p>
                                  </div>
                                  <div id="bank_Div_' . $aRow->$aColumns[0] . '" style="display:none">
                                      <input type="hidden" id="bankAccount_' . $aRow->$aColumns[0] . '" name="bankAccount_' . $aRow->$aColumns[0] . '"  class="wdt50 bankAccount"/>
                                          <p id="bankReq' . $aRow->$aColumns[0] . '" style="color: #CC3333;border: 1px solid #CC3333;width: 10px;height:15px;display:none">*</p>
                                  </div>';
                            $row[] = '<button type="button" class="btn btn-success save_payment" data-id="' . $aRow->$aColumns[0] . '" data-ret="1">سداد</button>';
                        } else {
                            $name = $class = '';
                            if ($aRow->lastReviewType == 1 || $aRow->lastReviewType == 4) { //reviewed so give him Review details
                                //                        $row[] = '<a data-billid="' . $aRow->returnsellbillid . '" data-billtype="ret" type="button" class="btn btn-default btn-lg removeicon cancelreview"></a>';
                                $name = $class = 'choosedItemRetReviewd';
                            } else {
                                //                        $row[] = '<a data-billid="' . $aRow->returnsellbillid . '" data-billtype="ret" type="button" class="btn btn-default btn-lg correcticon review"></a>';
                                $name = $class = 'choosedItemRetNotReviewd';
                            }



                            //
                            $btn = '';
                            if ($aRow->lastReviewType == 3 || $aRow->lastReviewType == 4) { //has history
                                $btn .= '<a class="btn btn-default bookicon reviewInfo" style="display: inline !important;width:8%;text-align: center !important;padding: 5px 13px !important;margin: 5px;" data-id="' . $aRow->returnsellbillid . '" data-type="ret"></a>';
                            }
                            $btn .= '<input type="checkbox" name="' . $name . '[]" class="' . $class . '" value="' . $aRow->returnsellbillid . '" data-total="' . $aRow->returnsellbillaftertotalbill . '" data-client="' . $aRow->clientname . '" data-cid="' . $aRow->returnsellbillclientid . '"/>';
                            $row[] = $btn;
                        }
                        break;
                    default:
                        break;
                }
            }


            // '10 returnsellQuantity', '11 returnsellbilltotalpayed', '12 returnsellbillfinalbill', '13 newSellbillidAfterEdit', '14 conditions', '15 dailyentryid');
            if ($aRow->conditions == 0) { //not del not the old edit of bill
                //            $sellQuantity += $aRow->returnsellQuantity;
                //            $sellbilltotalpayed += $aRow->returnsellbilltotalpayed;
                //            $sellbillfinalbill += $aRow->returnsellbillfinalbill;
                $sellbillaftertotalbill += $aRow->returnsellbillaftertotalbill;
            }
            $output['data'][] = $row;
            $rowno++;
        }
    }
    //    $output['totals']["piecesCount"] = $sellQuantity;
    //    $output['totals']["payedTotal"] = $sellbilltotalpayed;
    //    $output['totals']["remainTotal"] = $sellbillfinalbill;
    $output['totals']["totalBills"] = $sellbillaftertotalbill;

    //print_r($output);
    echo json_encode($output, JSON_UNESCAPED_SLASHES);
}

function payClientDept($clientid, $payedDept, $visapayed = 0, $paysaveid = '', $payaccountid = '') {
    global $clientEX;
    global $clientDAO;
    global $client;
    global $clientDeptChange;
    global $clientDeptChangeDAO;
    global $today;

    //Dailyentry
    global $dailyEntry;
    global $dailyEntryDAO;

    //Dailyentrycreditor دائن
    global $dailyEntryCreditor;
    global $dailyEntryCreditorDAO;

    //Dailyentrydebtor مدين
    global $dailyEntryDebtor;
    global $dailyEntryDebtorDAO;

    global $accountsTreeDAO;
    global $accountsTree;

    //save
    global $mySaveRecord;

    global $bankAccountEX;
    global $bankAccountDAO;
    global $bankAccount;

    global $clientDeptChangeExt;

    //Transaction
    $mytransactions = new Transaction();
    try {
        $currentDate = date('Y-m-d H:i:s'); //$today;
        $debtBefore; //= $_POST["debtBefore"];
        $load_last_row = $clientDeptChangeExt->getlastInGeneral();
        $voucher = (int) $load_last_row->clientdebtchangeid + 1;
        //$voucher = $_POST["voucher"];
        $comment = ''; //$_POST["comment"];
        if ($paysaveid && $paysaveid > 0)
            $saveid = $paysaveid;
        else
            $saveid = $_SESSION['saveid']; //$_POST["saveid"];
        if (isset($saveid) && !empty($saveid) && isset($clientid) && $clientid != '-1') {
            if (isset($clientid) && $clientid != '-1') {
                $clientdataSP = getClientDataFromClientInUseSP($clientid);
                $client = $clientDAO->load($clientid);
                $debtBefore = $clientdataSP->clientdebt;
                //update current dept in client tbl
                $debtAfter = $client->clientdebt - ($payedDept + $visapayed);
                $client->clientdebt = $debtAfter;
                $client->userid = $_SESSION['userid'];
                $client->clientdate = $today;
                $client->clientid = $clientid;

                $clientEX->updatedept($client);

                markClientAsNOTInUse($clientid);
                //insert into supplierdeptchange tbl
                $clientDeptChange->comment = $comment;
                $processname = "سداد ديون عميل";
                if ($visapayed != 0 && $payaccountid > 0) {
                    $processname .= " ( نقدى :$payedDept, فيزا :$visapayed )";
                }

                $clientDeptChange->processname = $processname;
                $clientDeptChange->clientdebtchangeafter = $debtAfter;
                $clientDeptChange->clientdebtchangeamount = ($payedDept + $visapayed);
                $clientDeptChange->clientdebtchangebefore = $debtBefore;
                $clientDeptChange->clientdebtchangedate = $currentDate; //$today;
                $clientDeptChange->clientdebtchangemodelid = $voucher;
                $clientDeptChange->clientdebtchangetype = 1;
                $clientDeptChange->tablename = "clientPayedDeptController.php";
                $clientDeptChange->userid = $_SESSION['userid'];
                $clientDeptChange->clientid = $clientid;
                $clientDeptChange->dailyentryid = 0;

                //Insert
                $clientdebtchangeId = $clientDeptChangeDAO->insert($clientDeptChange);

                //update save's current value
                //get saveVsalue before and saveValue after plus payedDept
                $valueData = getSaveValueAndPlus($payedDept, $saveid);

                //  print_r($valueData);
                if (count($valueData) > 0 && $payedDept > 0) {
                    $saveId = $valueData[0];
                    $saveValuebefore = $valueData[1];
                    $saveValueafter = $valueData[2];

                    //update save value after payedDept
                    updateSave($saveId, $saveValueafter);
                    //insert into savedaily tbl
                    insertSavedaily($saveValuebefore, $payedDept, 0, $saveId, "سداد ديون عميل", $clientdebtchangeId, $saveValueafter, "clientPayedDeptController.php");
                }

                if ($visapayed != 0 && $payaccountid > 0) {
                    $visaAccountData = $bankAccountDAO->load($payaccountid);
                    $visaDisPerVal = $visaAccountData->visaDisPer * .01 * $visapayed;
                    $accountBefore = $visaAccountData->accountbeginingbalance;
                    $visaAccountData->accountbeginingbalance = $accountBefore + $visapayed - $visaDisPerVal;
                    $visaAccountData->accountdate = date('Y-m-d');
                    $visaAccountData->accountid = $payaccountid;
                    $bankAccountEX->updateacount($visaAccountData);

                    //Insert in accountmovement table the payment
                    $afterVal = $accountBefore + $visapayed;
                    insertBankAccountMovement($payaccountid, $visaAccountData->bankid, $accountBefore, $visapayed, $afterVal, 0, "سداد ديون عميل", "clientPayedDeptController.php", $clientdebtchangeId, $clientid);

                    //Insert in accountmovement table the disount per
                    $afterVal_AfterDisc = $afterVal - $visaDisPerVal;
                    insertBankAccountMovement($payaccountid, $visaAccountData->bankid, $afterVal, $visaDisPerVal, $afterVal_AfterDisc, 1, "نسبة خصم الفيزا من سداد ديون عميل", "clientPayedDeptController.php", $clientdebtchangeId, $clientid);
                }
            }
            $clientData = $clientDAO->load($clientid);

            $dailyEntry->entryComment = 'اضافة ايصال سداد للعميل ' . $clientData->clientname;

            // start save
            $dailyEntryDebtorArray = array();


            if (count($valueData) > 0 && $payedDept > 0) {
                $idTreeSave = R::getCell('select treeId from save where saveid=' . $saveid);
                $dailyEntryDebtor = new stdClass();
                $dailyEntryDebtor->value = abs($payedDept);
                $dailyEntryDebtor->accountstreeid = $idTreeSave;
                array_push($dailyEntryDebtorArray, $dailyEntryDebtor);
            }

            if ($visapayed != 0 && $payaccountid > 0) {
                $idTreeBank = R::getCell('select treeId from bankaccount where accountid=' . $payaccountid);
                $dailyEntryDebtor = new stdClass();
                $dailyEntryDebtor->value = $visapayed;
                $dailyEntryDebtor->accountstreeid = $idTreeBank;
                array_push($dailyEntryDebtorArray, $dailyEntryDebtor);
            }
            /// end save
            //
            /// start L 7sab L da2non
            $dailyEntryCreditorArray = array();
            $dailyEntryCreditor->value = $payedDept + $visapayed;
            $oldClient = $clientDAO->load($clientid);
            $treeId = $oldClient->treeId;
            // print_r($treeId . 'aaaaaaaaaaaaa');
            $dailyEntryCreditor->accountstreeid = $treeId;
            /// end L 7sab L da2non
            array_push($dailyEntryCreditorArray, $dailyEntryCreditor);

            $returnedData = insertEntery($dailyEntry, $dailyEntryDebtorArray, $dailyEntryCreditorArray, 1);
            $did = $returnedData[1];

            $rowData = $clientDeptChangeDAO->load($clientdebtchangeId);

            $clientDeptChange->comment = $rowData->comment;
            $clientDeptChange->processname = $rowData->processname;
            $clientDeptChange->clientdebtchangeafter = $rowData->clientdebtchangeafter;
            $clientDeptChange->clientdebtchangeamount = $rowData->clientdebtchangeamount;
            $clientDeptChange->clientdebtchangebefore = $rowData->clientdebtchangebefore;
            $clientDeptChange->clientdebtchangedate = $rowData->clientdebtchangedate;
            $clientDeptChange->clientdebtchangemodelid = $rowData->clientdebtchangemodelid;
            $clientDeptChange->clientdebtchangetype = $rowData->clientdebtchangetype;
            $clientDeptChange->tablename = $rowData->tablename;
            $clientDeptChange->userid = $rowData->userid;
            $clientDeptChange->clientid = $rowData->clientid;
            $clientDeptChange->dailyentryid = $did;
            $clientDeptChangeDAO->update($clientDeptChange);

            $add_print = filter_input(INPUT_POST, 'add_print');

            $data_arr = array($add_print, $clientdebtchangeId);
        }


        $mytransactions->commit();
    } catch (Exception $ex) {
        $mytransactions->rollback();
    }
    return $data_arr;
}

// get initialvalue and plus from save tbl
function getSaveValueAndPlus($savevaluechanged, $saveid) {
    //to use the variable out side the funcion
    global $SaveExt;

    $saveData = $SaveExt->loadForUpdateEx($saveid);
    $saveId = $saveData->saveid;
    $saveValuebefore = $saveData->savecurrentvalue;

    $saveValueafter = $saveValuebefore + $savevaluechanged;

    return array($saveId, $saveValuebefore, $saveValueafter);
}

// get initialvalue and subtract from save tbl
function getSaveValueAndSubtract($savevaluechanged, $saveid) {
    //to use the variable out side the funcion
    global $SaveExt;

    $saveData = $SaveExt->loadForUpdateEx($saveid);
    $saveId = $saveData->saveid;
    $saveValuebefore = $saveData->savecurrentvalue;

    $saveValueafter = $saveValuebefore - $savevaluechanged;

    return array($saveId, $saveValuebefore, $saveValueafter);
}

// update Save tbl
function updateSave($saveid, $savevalueafter) {
    global $SaveExt;

    //update savecurrentvalue in Save tbl
    $Save->savecurrentvalue = $savevalueafter;
    $Save->userid = $_SESSION['userid'];
    $Save->saveid = $saveid;

    $SaveExt->updateSaveValue($Save);
}

// insert in Savedaily tbl
function insertSavedaily($savedailysavebefore, $savedailychangeamount, $savedailychangetype, $saveid, $processname, $savedailymodelid, $savedailysaveafter, $tablename) {

    //to use the variable out side the funcion
    global $Savedaily;
    global $SavedailyDAO;

    //insert in Savedaily tbl
    $Savedaily->savedailydate = date("Y-m-d H:i:s");
    $Savedaily->userid = $_SESSION['userid'];
    $Savedaily->savedailysavebefore = $savedailysavebefore;
    $Savedaily->savedailychangeamount = $savedailychangeamount;
    $Savedaily->savedailychangetype = $savedailychangetype;
    $Savedaily->saveid = $saveid;
    $Savedaily->processname = $processname;
    $Savedaily->savedailymodelid = $savedailymodelid;
    $Savedaily->savedailysaveafter = $savedailysaveafter;
    $Savedaily->tablename = $tablename;

    $SavedailyDAO->insert($Savedaily);
}

function insertBankAccountMovement($accountId, $bankId, $beforeVal, $amount, $afterVal, $type, $comment, $tablename, $modelId, $clientId) {
    global $accountMove;
    global $accountMoveDAO;

    if ($amount != 0) {
        $accountMove->accountmovementbefore = $beforeVal;
        $accountMove->accountid = $accountId;
        $accountMove->bankid = $bankId;
        $accountMove->accountmovementamount = $amount;
        $accountMove->accountmovementafter = $afterVal;
        $accountMove->accountmovementtype = $type;
        $accountMove->processname = $comment;
        $accountMove->tablename = $tablename;
        $accountMove->clientid = $clientId;
        $accountMove->accountmovementmodelid = $modelId;
        $accountMove->accountmovementdate = date('Y-m-d H:i:s');
        $accountMove->userid = $_SESSION['userid'];
        $accountMoveDAO->insert($accountMove);
    }
}

function getClientDataFromClientInUseSP($clientid) {
    global $clientEX;
    global $clientDAO;
    if ($clientid == 1) {
        $client_data = $clientDAO->load($clientid);
    } elseif ($clientid > 1) {
        $noOfTries = 0;
        $client_data = $clientEX->callClientInUseSP($clientid);
        while ($client_data->clientdebt == 'in_use') {
            sleep(1);
            $noOfTries++;
            if ($noOfTries > 15) { // mean 15 sec as sleep is 1 sec her
                //markClientAsNOTInUse -- using redbeans to instantly execute in database as we are in transaction now "means affecting row is done @end of execution or comit"
                liveBackupComment("-- force free client=$clientid with redbean");
                R::exec('UPDATE client SET  inUse = 0 where clientid = ' . $clientid);
            }
            $client_data = $clientEX->callClientInUseSP($clientid);
        }
    }
    return $client_data;
}

function markClientAsNOTInUse($clientid) { //mark as not in use "free him to be able to change debt again"
    global $clientEX;
    if ($clientid == 1) {
        //do no thing. we didnot mark it as in use at begining
    } else {
        $clientEX->markClientAsNOTInUse($clientid);
    }
}

function liveBackupComment($txt) {
    $myfile = fopen("backup/" . date("Y-m-d") . ".txt", "a+") or die("Unable to open file!");
    $toWrite = "\r\n-- ----------------------------------------------------------------------------------------------------\r\n";
    $toWrite .= $txt;
    $toWrite .= "\r\n-- ----------------------------------------------------------------------------------------------------\r\n\r\n";
    fwrite($myfile, $toWrite);
}
