<?php

session_start();
ob_start();
//the global file operation
//include("../public/impOpreation.php");
//global varable
global $showoutside;

//to check if the page from .htacess
//$showoutside = $_GET['sn'];
// get the config file
include_once("../public/config.php");
include_once("dailyentryfun.php");

//here the db files that include in the file
include("../public/include_dao.php");
include("../library/uploadImages.php");

//Client
require_once('../models/dao/ClientDAO.class.php');
require_once('../models/dto/Client.class.php');
require_once('../models/mysql/ClientMySqlDAO.class.php');
require_once('../models/mysql/ext/ClientMySqlExtDAO.class.php');


//Clientdebtchange
require_once('../models/dao/ClientdebtchangeDAO.class.php');
require_once('../models/dto/Clientdebtchange.class.php');
require_once('../models/mysql/ClientdebtchangeMySqlDAO.class.php');
require_once('../models/mysql/ext/ClientdebtchangeMySqlExtDAO.class.php');


//Government
require_once('../models/dao/GovernmentDAO.class.php');
require_once('../models/dto/Government.class.php');
require_once('../models/mysql/GovernmentMySqlDAO.class.php');
require_once('../models/mysql/ext/GovernmentMySqlExtDAO.class.php');

require_once('../models/dao/ClientareaDAO.class.php');
require_once('../models/dto/Clientarea.class.php');
require_once('../models/mysql/ClientareaMySqlDAO.class.php');
require_once('../models/mysql/ext/ClientareaMySqlExtDAO.class.php');


//GovernArea
require_once('../models/dao/GoverareaDAO.class.php');
require_once('../models/dto/Goverarea.class.php');
require_once('../models/mysql/GoverareaMySqlDAO.class.php');
require_once('../models/mysql/ext/GoverareaMySqlExtDAO.class.php');
//Store
require_once('../models/dao/StoreDAO.class.php');
require_once('../models/dto/Store.class.php');
require_once('../models/mysql/StoreMySqlDAO.class.php');
require_once('../models/mysql/ext/StoreMySqlExtDAO.class.php');

//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');
//Tamweenda3mstatus
require_once('../models/dao/Tamweenda3mstatusDAO.class.php');
require_once('../models/dto/Tamweenda3mstatu.class.php');
require_once('../models/mysql/Tamweenda3mstatusMySqlDAO.class.php');
require_once('../models/mysql/ext/Tamweenda3mstatusMySqlExtDAO.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');
//
require_once('../models/dao/TamweenclientdetailDAO.class.php');
require_once('../models/dto/Tamweenclientdetail.class.php');
require_once('../models/mysql/TamweenclientdetailMySqlDAO.class.php');
require_once('../models/mysql/ext/TamweenclientdetailMySqlExtDAO.class.php');
//
require_once('../models/dao/TamweenbillDAO.class.php');
require_once('../models/dto/Tamweenbill.class.php');
require_once('../models/mysql/TamweenbillMySqlDAO.class.php');
require_once('../models/mysql/ext/TamweenbillMySqlExtDAO.class.php');
//
require_once('../models/dao/PlaystationcardDAO.class.php');
require_once('../models/dto/Playstationcard.class.php');
require_once('../models/mysql/PlaystationcardMySqlDAO.class.php');
require_once('../models/mysql/ext/PlaystationcardMySqlExtDAO.class.php');
//Playstationclientcard
require_once('../models/dao/PlaystationclientcardDAO.class.php');
require_once('../models/dto/Playstationclientcard.class.php');
require_once('../models/mysql/PlaystationclientcardMySqlDAO.class.php');
require_once('../models/mysql/ext/PlaystationclientcardMySqlExtDAO.class.php');


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

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

  Controller Name :- clientControllerAjax

  OPERTATION in Controller

  1- search

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

//here goes the instances and general variables
//Client
$client = new Client();
$clientDAO = new ClientMySqlDAO();
$clientExt = new ClientMySqlExtDAO();

//Clientdebtchange
$clientDeptChange = new Clientdebtchange();
$clientDeptChangeDAO = new ClientdebtchangeMySqlDAO();
$clientDeptChangeExt = new ClientdebtchangeMySqlExtDAO();

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


// Clientarea
$ClientareaDAO = new ClientareaMySqlDAO();
eval(base64_decode("ZnVuY3Rpb24gZ2VuZXJhdGVVVUlEKCkgewogICAgJGhvc3RuYW1lID0gZXhwbG9kZSgnY29udHJvbGxlcnMnLCAkX1NFUlZFUlsnU0NSSVBUX1VSSSddKVswXTsgLy9nZXRob3N0bmFtZSgpOwogICAgJGhvc3RuYW1lID0gc3RyX3JlcGxhY2UoYXJyYXkoImh0dHA6Ly8iLCAiaHR0cHM6Ly8iKSwgIiIsICRob3N0bmFtZSk7CgogICAgLy8gR2VuZXJhdGUgYW4gTUQ1IGhhc2ggb2YgdGhlIGlucHV0CiAgICAkZGF0YSA9IG1kNSgkaG9zdG5hbWUpOwogICAgLy8gRm9ybWF0IHRoZSBoYXNoIGFzIGEgVVVJRAogICAgJHV1aWQgPSBzcHJpbnRmKAogICAgICAgICAgICAnJTA4cy0lMDRzLSUwNHgtJTA0eC0lMTJzJywKICAgICAgICAgICAgc3Vic3RyKCRkYXRhLCAwLCA4KSwKICAgICAgICAgICAgc3Vic3RyKCRkYXRhLCA4LCA0KSwKICAgICAgICAgICAgKGhleGRlYyhzdWJzdHIoJGRhdGEsIDEyLCA0KSkgJiAweDBmZmYpIHwgMHg0MDAwLAogICAgICAgICAgICAoaGV4ZGVjKHN1YnN0cigkZGF0YSwgMTYsIDQpKSAmIDB4M2ZmZikgfCAweDgwMDAsCiAgICAgICAgICAgIHN1YnN0cigkZGF0YSwgMjAsIDEyKQogICAgKTsKICAgICR1dWlkID0gc3RyX3JlcGxhY2UoYXJyYXkoJy0nKSwgJycsICR1dWlkKTsKICAgIHJldHVybiAkdXVpZDsKfQ=="));
eval(base64_decode("ZnVuY3Rpb24gc2VyaWFsY2hlY2soKSB7CiAgICBpZiAoc3RydG91cHBlcihzdWJzdHIoUEhQX09TLCAwLCAzKSkgPT09ICdXSU4nKSB7CiAgICAgICAgLy91dWlkIG9mIHdpbmRvd3MKICAgICAgICBleGVjKCdDOlxXaW5kb3dzXFN5c3RlbTMyXHdiZW1cd21pYyBjc3Byb2R1Y3QgZ2V0ICJVVUlEIicsICRtYWNhZGRyZXNzLCAkaXNFeGVjdXRlZCk7CiAgICAgICAgJG1hY2FkZHJlc3MgPSAkbWFjYWRkcmVzc1sxXTsKICAgICAgICAkbWFjID0gc3RyX3JlcGxhY2UoYXJyYXkoJy0nKSwgJycsICRtYWNhZGRyZXNzKTsKICAgIH0gZWxzZSB7Ly9ub3Qgd2luZG93cyB1c2UgbGludXggbWFjCiAgICAgICAgJG1hYyA9IGdlbmVyYXRlVVVJRCgpOwogICAgfQoKICAgICRyYW5kb21fc2FsdCA9IGhhc2goJ3NoYTUxMicsICcxMzA1Jyk7CiAgICAvLyBDcmVhdGUgc2FsdGVkIHBhc3N3b3JkCiAgICAkaGFzaGVkTWFjID0gaGFzaCgnc2hhNTEyJywgJG1hYyAuICRyYW5kb21fc2FsdCk7CiAgICByZXR1cm4gJGhhc2hlZE1hYzsKfQ=="));
// GovernArea
$governArea = new Goverarea();
$governAreaDAO = new GoverareaMySqlDAO();
$governAreaExt = new GoverareaMySqlExtDAO();

//Store
$store = new Store();
$storeDAO = new StoreMySqlDAO();
$storeEX = new StoreMySqlExtDAO();

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

//typeClient
$type = new TypeClient();
$TypeClientDAO = new TypeClientMySqlDAO();
$TypeClientEX = new TypeClientMySqlExtDAO();
//Tamweenclientdetail
$tamweenClientDetail = new Tamweenclientdetail();
$tamweenClientDetailDAO = new TamweenclientdetailMySqlDAO();
$tamweenClientDetailEX = new TamweenclientdetailMySqlExtDAO();
//Tamweenda3mstatus
$tamweenDa3mStatus = new Tamweenda3mstatu();
$tamweenDa3mStatusDAO = new Tamweenda3mstatusMySqlDAO();
$tamweenDa3mStatusEX = new Tamweenda3mstatusMySqlExtDAO();
//Tamweenbill
$tamweenBill = new Tamweenbill();
$tamweenBillDAO = new TamweenbillMySqlDAO();
$tamweenBillEX = new TamweenbillMySqlExtDAO();
//Programsetting
$Programsetting = new Programsetting();
$ProgramsettingDAO = new ProgramsettingsMySqlDAO();
$ProgramsettingEX = new ProgramsettingsMySqlExtDAO();
//
$playstationCard = new Playstationcard();
$playstationCardDAO = new PlaystationcardMySqlDAO();
$playstationCardEX = new PlaystationcardMySqlExtDAO();
//
$playstationClientCard = new Playstationclientcard();
$playstationClientCardDAO = new PlaystationclientcardMySqlDAO();
$playstationClientCardEX = new PlaystationclientcardMySqlExtDAO();
$myUserRecord = new UserMySqlDAO();

$prog = $ProgramsettingDAO->load(1);
$today = date("Y-m-d H:i:s"); //i mean it to be date time for client debt change ,and it will not give error in client table
//check and use the condition that suite this action
if (empty($do)) {
    //here the permssion check


    $clientData = search();
    //here the smarty templates
    $smarty->display("clientview/searchResult.html");
} elseif ($do == "select2ClientsAjax") {
    select2ClientsAjax();
} elseif ($do == "freeClient") {
    $id = (int) $_GET['id'];
    $clientExt->freeClient($id);
    echo 1;
} elseif ($do == "getclienttamweendata") {
    $client_id = $_POST['client'];
    $tamweenClientDetail = $tamweenClientDetailDAO->queryByClientid($client_id);
    $clientData = $clientDAO->load($client_id);
    $monthDays = cal_days_in_month(CAL_GREGORIAN, date('m'), date('Y'));
    $noOfDaysCalc = $monthDays;
    if (date('Y-m') == date('Y-m', strtotime($clientData->addDate))) {
        $noOfDaysCalc = ($monthDays + 1) - (int) (explode('-', $clientData->addDate)[2]); //+1 to have day added in calculation
    }
    $cardShare = 5 * $tamweenClientDetail[0]->noOfPersonsDa3m * $noOfDaysCalc;

    $from = date('Y-m-1');
    $to = date('Y-m-31');
    $cardTakenAmount = (int) $tamweenBillEX->getClientTakenAmountFromTo($client_id, $from, $to);
    $remaining = $cardShare - $cardTakenAmount;

    $tamweenStatusData = $tamweenDa3mStatusEX->queryRowByClientAndMonth($client_id, date('Y-m-1'));
    $ret_arr = array(
        'noOfPersonsDa3m' => $tamweenClientDetail[0]->noOfPersonsDa3m,
        'noOfPersonsTamween' => $tamweenClientDetail[0]->noOfPersonsTamween,
        'cardShare' => $cardShare,
        'cardTakenAmount' => $cardTakenAmount,
        'remaining' => $remaining,
        'tamweenStatus' => (int) $tamweenStatusData->tamweenStatus,
        'userTamween' => $tamweenStatusData->employeename,
        'dateTamween' => $tamweenStatusData->sysdateTamween
    );
    echo json_encode($ret_arr);
} elseif ($do == "checkOtherUniquesIfExist") {
    $id = (int) filter_input(INPUT_POST, 'id');
    $phone = filter_input(INPUT_POST, 'phone');
    $mobile = filter_input(INPUT_POST, 'mobile');
    $cardNum = filter_input(INPUT_POST, 'cardNum');

    $phoneFlag = 1;
    $mobileFlag = 1;
    $cardNumFlag = 1;

    $queryString = '';
    if ($id > 0) {
        $queryString = ' and clientid != ' . $id . ' ';
    }

    if (!empty($phone)) {
        $count = (int) $clientExt->getCountByColumnEqualNotDel("clientphone", $phone, $queryString);
        if ($count == 0)
            $count = (int) $clientExt->getCountByColumnEqualNotDel("clientmobile", $phone, $queryString);
        $phoneFlag = ($count == 0) ? 1 : 0;
    }
    if (!empty($mobile)) {
        $count = (int) $clientExt->getCountByColumnEqualNotDel("clientmobile", $mobile, $queryString);
        if ($count == 0)
            $count = (int) $clientExt->getCountByColumnEqualNotDel("clientphone", $mobile, $queryString);
        $mobileFlag = ($count == 0) ? 1 : 0;
    }
    if (!empty($cardNum)) {
        $cardNumLength = strlen($cardNum);
        if ($cardNumLength != 14) {
            $cardNumFlag = 2;
        } else {
            $count = (int) $tamweenClientDetailEX->getCountByColumnEqual('cardNum', $cardNum, $queryString);
            $cardNumFlag = ($count == 0) ? 1 : 0;
        }
    }
    echo json_encode(array('phoneFlag' => $phoneFlag, 'mobileFlag' => $mobileFlag, 'cardNumFlag' => $cardNumFlag));
} elseif ($do == "addclient") {
    //    try {

    $simplereturn = isset($_GET['simplereturn']) ? $_GET['simplereturn'] : 0;
    $jsonRowRet = isset($_GET['jsonRowRet']) ? $_GET['jsonRowRet'] : 0;
    $returnToselect2Ajax = isset($_POST['returnToselect2Ajax']) ? $_POST['returnToselect2Ajax'] : 0;
    // eval(base64_decode("JHNlcmlhbENhbCA9IHNlcmlhbGNoZWNrKCk7CmlmICgoJHByb2ctPnNlcmlhbCAhPSAkc2VyaWFsQ2FsICYmICRwcm9nLT5zZXJpYWwyICE9ICRzZXJpYWxDYWwpIHx8ICgkX1NFU1NJT05bJ2xpY19kYXRldG8nXSA8IGRhdGUoJ1ktbS1kJykgJiYgZW1wdHkoJF9QT1NUWyJlcnBEQiJdKSkpIHsKICAgIC8vc2VuZCB0aGUgdXNlciB0byB0aGUgbG9nb3V0IHBhZ2UKICAgIGhlYWRlcigibG9jYXRpb246bG9nb3V0LnBocCIpOwogICAgZXhpdCgpOwp9"));
    eval(base64_decode("JHNlcmlhbENhbCA9IHNlcmlhbGNoZWNrKCk7CmlmICgoJHByb2ctPnNlcmlhbCAhPSAkc2VyaWFsQ2FsICYmICRwcm9nLT5zZXJpYWwyICE9ICRzZXJpYWxDYWwgJiYgJHByb2ctPnNlcmlhbDMgIT0gJHNlcmlhbENhbCkgfHwgKCRfU0VTU0lPTlsnbGljX2RhdGV0byddIDwgZGF0ZSgnWS1tLWQnKSAmJiBlbXB0eSgkX1BPU1RbImVycERCIl0pKSkgewogICAgLy9zZW5kIHRoZSB1c2VyIHRvIHRoZSBsb2dvdXQgcGFnZQogICAgaGVhZGVyKCJsb2NhdGlvbjpsb2dvdXQucGhwIik7CiAgICBleGl0KCk7Cn0="));
    eval(base64_decode("aWYgKCFmdW5jdGlvbl9leGlzdHMoJ3NlcmlhbGNoZWNrJykpIHsKICAgIENVUkxfSVQyKGFycmF5KCksICdzZWxsYmlsbEFqYXhDb250cm9sbGVyLnBocD9kbz1jYXRjaCcpOwogICAgaGVhZGVyKCJsb2NhdGlvbjpsb2dvdXQucGhwIik7Cn0="));
    $addData = addClient();
    if ($addData[0] == '0') {
        if ($simplereturn == 1) {
            echo $addData[1]; //id
        } elseif ($jsonRowRet == 1) {
            $clientRowData = $clientDAO->load($addData[1]);
            $clientRowData->clientname = mb_convert_encoding($clientRowData->clientname, 'UTF-8', 'UTF-8');
            $clientRowData->clientphone = mb_convert_encoding($clientRowData->clientphone, 'UTF-8', 'UTF-8');
            $clientRowData->clientmobile = mb_convert_encoding($clientRowData->clientmobile, 'UTF-8', 'UTF-8');
            echo json_encode($clientRowData); //note we use mb_convert_encoding($aRow->$aColumns[$i], 'UTF-8', 'UTF-8'); to correct encoding of error that stop this function
        } elseif ($returnToselect2Ajax == 1) {
            $clientData = $clientDAO->load($addData[1]);
            echo $clientData->clientid . "," . $clientData->clientname;
        } else {
            $clientData = loadClient();
            $smarty->assign("clientData", $clientData);

            $smarty->assign("clientid", $addData[1]);

            $smarty->display("sellbillview/client.html");

            $smarty->assign("customsellbillClient", 1);
            $smarty->assign("faidaScript", 1);
        }
    } else {
        echo $addData[0];
        return $addData;
    }
    //    } catch (Exception $e) {
    //
    //    }
} elseif ($do == "update_client") {

    $clientid = $_POST['client_id'];
    $updated_dept = $_POST['updated_dept'];

    if (!empty($clientid) && !empty($updated_dept)) {

        $rowData = $clientDAO->load($clientid);
        $client->clientcode = $rowData->clientcode;
        $client->clientaddress = $rowData->clientaddress;
        $client->clientdetails = $rowData->clientdetails;
        $client->clientmobile = $rowData->clientmobile;
        $client->clientname = $rowData->clientname;
        $client->clientphone = $rowData->clientphone;
        $client->conditions = $rowData->conditions;
        $client->userid = $rowData->userid;
        $client->clientareaid = $rowData->clientareaid;
        $client->dailyentryid = $rowData->clientareaid;
        $client->branchId = $rowData->branchId;
        $client->rondomtxt = $rowData->rondomtxt;
        $client->clientStoreIds = $rowData->clientStoreIds;
        $client->clientdate = $rowData->$updated_dept;
        $client->clientdebt = $updated_dept;
        $client->clientid = $clientid;

        $clientDAO->update($client);
        echo 1;
    } else {
        echo -1;
    }
} elseif ($do == "addclient2") {
    try {

        $addData = addClient();

        if ($addData[0] == '0') {
            $clientData = loadClient();
            $smarty->assign("clientData", $clientData);

            $smarty->assign("clientid", $addData[1]);

            $smarty->display("sellbillview/client2.html");

            $smarty->assign("customsellbillClient", 1);
        } else {
            echo $addData[0];
            return $addData;
        }
    } catch (Exception $e) {

    }
}
##fatma
elseif ($do == "addclient3") {
    try {

        $addData = addClient();
        if ($addData[0] == '0') {
            $clientData = $clientDAO->load($addData[1]);
            echo $clientData->clientid . "," . $clientData->clientname;
        } else {
            echo $addData[0];
        }
    } catch (Exception $e) {

    }
} elseif ($do == "deleteFinaly") {
    deleteFinaly();
} elseif ($do == "updateFull") {
    update();
} elseif ($do == "getclientdata") {
    $client_id = $_POST['id'];
    $getPsCardVal = (int) $_POST['getPsCardVal'];

    //
    $client_data = $clientDAO->load($client_id);
    $availablePSCardValue = 0;
    if ($getPsCardVal == 1) {
        $availablePSCardValue = getAvailablePSCardValue($client_data->clientid);
    }
    $client_data->availablePSCardValue = $availablePSCardValue;

    echo json_encode($client_data);
} elseif ($do == "getclientdatarfid") {
    $rfid = $_POST['rfid'];
    $getPsCardVal = (int) $_POST['getPsCardVal'];

    //
    $client_data = $clientExt->getClientByRFID($rfid);

    $availablePSCardValue = 0;
    if ($getPsCardVal == 1) {
        $availablePSCardValue = getAvailablePSCardValue($client_data->clientid);
    }
    $client_data->availablePSCardValue = $availablePSCardValue;

    echo json_encode($client_data);
} elseif ($do == "select2clientandarea") {
    $row_array = array();
    $return_arr = array();

    $name = $_GET['term'];
    //print_r($lastLevelCatIDS);
    $clients = $clientExt->getClientsAndAreaSelect2($name);
    if (count($clients) > 0) {
        foreach ($clients as $data) {
            $row_array['id'] = $data->clientid;
            $row_array['text'] = $data->clientname;
            array_push($return_arr, $row_array);
        }
    }
    echo json_encode($return_arr);
} elseif ($do == "typeclient") {
    $searchTerm = $_POST['searchTerm'];
    $clientid = $_POST['clientid'];
    $search = "";
    $return_arr = array();
    if ($clientid > 1) {
        $client = R::getRow("SELECT * FROM  client WHERE conditions = 0 and  clientid = ? limit 1", [$clientid]);
        $search .= ' and typeId in (0' . $client['typeclientid'] . '0) ';
        $allData = R::getAll("SELECT typeId as id, typeName as name
        FROM  typeclient
        WHERE conditions = 0 and  typeName LIKE '%" . $searchTerm . "%' $search limit 50");
        foreach ($allData as $data) {
            $row_array = array();
            $row_array['id'] = $data['id'];
            $row_array['text'] = $data['name'];
            array_push($return_arr, $row_array);
        }
    }
    echo json_encode($return_arr);
} elseif ($do == "getClientPSCards") {
    select2ClientPlaystationCardsAjax();
} elseif ($do == "getPSCards") {
    select2PlaystationCardsAjax();
} elseif ($do == "linkToSupplier") {
    $clientId = $_POST['clientid'];
    $supplierId = $_POST['supplierid'];

    $client = $clientDAO->load($clientId);

    //remove any link to same suppplier//no need it is filtered with select2ajax => only unlinked clients come as choice
    //$clientExt->removeAnyClientLinkForASupplier($supplierId);

    $client->linkedSupplierId = $supplierId;
    $clientDAO->update($client);
}
//## m7md
elseif ($do == "load_client") {
    $client_id = $_POST['client_id'];
    $client_data = $clientDAO->load($client_id);
    $client_name = $client_data->clientname;
    $client_dept = $client_data->clientdebt;
    $client_arr = array('clientname' => $client_name, 'clientdebt' => $client_dept);
    echo json_encode($client_arr);
} elseif ($do == "getclientarea") {
    $searchTerms = $_POST['searchTerms'];
    // $governmentData = $governmentExt->governmentLimit();
    $governmentData = $governmentDAO->queryAll();
    $clientareaData = array();
    foreach ($governmentData as $government) {
        $loadAreaIdsArray = explode(',', $government->governmetid);
        $loadAreaIds = count($loadAreaIdsArray);
        $loadAreaIds = $loadAreaIds - 1;
        $arr = array();
        for ($i = 0; $i <= $loadAreaIds; $i++) {
            $clientareaData2 = $governAreaExt->queryByGovernmentidEX($loadAreaIdsArray[$i], $searchTerms);
            foreach ($clientareaData2 as $value) {
                $areaData = $ClientareaDAO->load($value->clientareaid);
                $arr['id'] = $areaData->id;
                $arr['text'] = $government->governmentname . ' - ' . $areaData->name;
                array_push($clientareaData, $arr);
            }
        }
    }

    echo json_encode($clientareaData);
} elseif ($do == "getclientareaonly") {
    $searchTerms = $_POST['searchTerms'];
    $governmentid = $_POST['governmentid'];


    $clientarea = R::getAll('SELECT clientarea.* FROM `clientarea`
        LEFT JOIN goverarea ON clientarea.id = goverarea.clientareaid
         WHERE goverarea.governmentid = ' . $governmentid);

    $clientareaData = array();
    foreach ($clientarea as $value) {
        $arr = array();
        $arr['id'] = $value['id'];
        $arr['text'] = $value['name'];
        array_push($clientareaData, $arr);
    }



    echo json_encode($clientareaData);
    //    echo $clientareaData;
} elseif ($do == "showallajax") {
    showAllClientsAjax();
}

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

function search() {
    global $clientExt;
    global $smarty;

    $input = $_REQUEST["clients"];
    $clientData = $clientExt->queryByClientNameExt($input);

    $message = "إسم العميل: " . $input;
    $smarty->assign("message", $message);


    // required connect
    $paginate = new SmartyPaginate;
    $paginate->connect();
    // set items per page
    $paginate->setLimit(25);
    // get the all colums number
    $allColums = $clientExt->queryByClientNameExt($input);
    $paginate->setTotal(count($allColums));
    //get the selected data with limitation
    $shownData = $clientExt->queryByClientNameExtLimited($input, $paginate->getCurrentIndex(), $paginate->getLimit());

    $sum;
    foreach ($shownData as $data) {
        if ($data->conditions == 0) {
            $sum += $data->clientdebt;
        }
    }
    $smarty->assign('sum', $sum);


    // assign your db results to the template
    $smarty->assign('shownData', $shownData);
    // assign {$paginate} var
    $paginate->assign($smarty);
    //text that show in navgation
    $paginate->setNextText('التالى');
    $paginate->setPrevText('السابق');
    //add any varaible in url
    $paginate->setUrl('clientControllerAjax.php?clients=' . $input . '&');
}

// add in client tbl
function addClient() {
    global $clientDAO;
    global $clientExt;
    global $client;
    global $clientDeptChange;
    global $clientDeptChangeDAO;
    global $today;

    //Dailyentry
    global $dailyEntry;

    //Dailyentrycreditor دائن
    global $dailyEntryCreditor;

    //Dailyentrydebtor مدين
    global $dailyEntryDebtor;
    global $storeDAO;
    global $TypeClientDAO;
    global $tamweenClientDetailDAO;


    $clientname = $_POST["newclientName"];
    $clientaddress = $_POST["clientaddress"];
    $clientphone = $_POST["clientphone"];
    $clientmobile = $_POST["clientmobile"];
    $clientdebt = (float) $_POST["newclientdebt"];
    $clientdetails = $_POST["clientdetails"];
    $clientareaid = (int) $_POST["clientareaid"];
    $clientcode = $_POST["clientcode"];
    $debtLimit = $_POST["debtLimit"];
    $store_all = 1; //$_POST["store_all"];//temppppppppppp
    $typeClient_all = $_POST["typeClient_all"]; //temppppppppppp
    $priceTypeId = $_POST["priceTypeId"];
    $taxnumber = $_POST["taxnumber"];

    $noOfPersonsTamween = (int) $_POST["noOfPersonsTamween"];
    $noOfPersonsDa3m = (int) $_POST["noOfPersonsDa3m"];
    $cardNum = $_POST["cardNum"];
    $cardPassword = $_POST["cardPassword"];
    $specialDiscount = (int) $_POST["specialDiscount"];
    $specialDiscountVal = (float) $_POST["specialDiscountVal"];
    $clientRFID = $_POST["clientRFID"];
    $clientTypeForTree = (int) $_POST["clientTypeForTree"];
    $delegateid = $_POST["delegateid"];

    $card_number = $_POST["card_number"];
    $handle = new upload($_FILES['file_faida']);
    $file_faida = uploadImages($handle, "../views/default/images/faida", 300, 300);
    //var_dump($file_faida);
    //it is used for insert client from obygy_app
    $obygyPatientId = (int) $_POST["obygyPatientId"];
    if ($obygyPatientId > 0) {
        $clientcode = (int) $_POST['statusno'];
        $priceTypeId = 1;
    }
    $husdandname = $_POST["husdandname"];
    $erpDB = $_POST["erpDB"];
    $sessionDBName = ConnectionProperty::getDatabase();
    if (isset($_SESSION['dbname']) && !empty($_SESSION['dbname'])) {
        $sessionDBName = $_SESSION['dbname'];
    }
    //choose erpDB user has choosen in obygy_app
    if (isset($erpDB) && !empty($erpDB)) {
        $_SESSION['dbname'] = $erpDB;
        $_SESSION['userid'] = 1; //tempppppppppppppppp
        if (empty($clientname)) {
            $clientname = 'obj_app_client_' . $obygyPatientId;
        }

        //$count = (int) $clientDAO->queryByClientname($clientname);
        $count = (int) R::getCell('select count(*) from client where clientname = "' . $clientname . '" ');
        if ($count > 0)
            $clientname .= "_" . $count;
    }

    //echo 'clientdebt'.$clientdebt.'<br>';


    $flag;
    $clientid;

    if (!empty($clientname)) {


        //check if this name already exists
        if (R::getCell('select count(*) from client where clientname = "' . $clientname . '" ') == 0 || ($obygyPatientId > 0 && !empty($husdandname) && R::getCell('select count(*) from client where husbandNameObgy = "' . $husdandname . '" ') == 0)) {

            if (empty($clientcode) || empty(R::getCell('select count(*) from client where clientcode = "' . $clientcode . '" '))) {

                $handle = new upload($_FILES['file']);
                $file = uploadImageswhioutresize($handle, '../upload/clientFile');
                //insert into client
                $client->clientaddress = $clientaddress;
                $client->clientdate = $today;
                $client->clientdebt = $clientdebt;
                $client->delegateid = $delegateid;
                $client->clientdetails = $clientdetails;
                $client->clientmobile = $clientmobile;
                $client->clientname = $clientname;
                $client->taxnumber = $taxnumber;
                $client->clientphone = $clientphone;
                $client->clientRFID = $clientRFID;
                $client->conditions = 0;
                $client->clientareaid = $clientareaid;
                $client->userid = $_SESSION['userid'];
                $client->clientcode = $clientcode;
                $client->debtLimit = $debtLimit;
                $client->clientTypeForTree = $clientTypeForTree;
                $client->dailyentryid = 0;
                $client->priceTypeId = $priceTypeId;
                $client->card_number = $card_number;
                $client->file_faida = $file_faida;
                $client->husbandNameObgy = $husdandname;
                if (isset($store_all) && $store_all == 1) {
                    $client->clientStoreIds = '-10'; //all stores
                } else {
                    $client->clientStoreIds = '';
                    $stores = $storeDAO->queryAll();
                    foreach ($stores as $data) {
                        $selectStore = $_POST["store" . $data->storeId];
                        if (isset($selectStore) && $selectStore == 1) {
                            $client->clientStoreIds .= $data->storeId . ',';
                        }
                    }
                    $client->clientStoreIds = rtrim($client->clientStoreIds, ",");
                }

                if (isset($typeClient_all) && $typeClient_all == 1) {
                    $client->typeclientid = ',-20,'; //all types
                } else {
                    $client->typeclientid = ',';
                    $types = $TypeClientDAO->queryAll();
                    foreach ($types as $data) {
                        $selectType = $_POST["type" . $data->typeId];
                        if (isset($selectType) && $selectType == 1) {
                            $client->typeclientid .= $data->typeId . ',';
                        }
                    }
                    //$client->typeclientid = rtrim($client->typeclientid, ",");
                }


                //                $client->typeclientid = '-20'; //all types
                //                $client->priceTypeId = 0;


                $client->specialDiscount = $specialDiscount;
                $client->specialDiscountVal = $specialDiscountVal;
                $client->file = $file;
                $client->addDate = date('Y-m-d');

                $client->obygyPatientId = $obygyPatientId;
                $clientid = $clientDAO->insert($client);
                $tamweenClientDetail->clientid = $clientid;
                $tamweenClientDetail->noOfPersonsTamween = $noOfPersonsTamween;
                $tamweenClientDetail->noOfPersonsDa3m = $noOfPersonsDa3m;
                $tamweenClientDetail->cardNum = $cardNum;
                $tamweenClientDetail->cardPassword = $cardPassword;
                $tamweenClientDetailDAO->insert($tamweenClientDetail);
                if (!empty($clientid)) {
                    //insert into clientdeptchange
                    $clientDeptChange->clientdebtchangeafter = $clientdebt;
                    $clientDeptChange->clientdebtchangeamount = $clientdebt;
                    $clientDeptChange->totalOperationCost = $clientdebt;
                    $clientDeptChange->clientdebtchangebefore = 0;
                    $clientDeptChange->clientdebtchangedate = $today;
                    $clientDeptChange->clientdebtchangemodelid = $clientid;
                    $clientDeptChange->clientdebtchangetype = 0;
                    $clientDeptChange->clientid = $clientid;
                    $clientDeptChange->comment = "";
                    $clientDeptChange->processname = "إضافة عميل جديد";
                    $clientDeptChange->tablename = "clientController.php";
                    $clientDeptChange->userid = $_SESSION['userid'];
                    $clientDeptChange->dailyentryid = 0;
                    $clientDeptChangeDAO->insert($clientDeptChange);
                }

                if ($clientTypeForTree == 0)
                    $parent = 58;
                elseif ($clientTypeForTree == 1)
                    $parent = 60;
                $treeId = addTreeElement($clientname, $parent, 3, 0, 1, '', 0, 0);


                $dailyEntry->dDateTime = date('Y-m-d H:i:s');
                $dailyEntry->entryComment = 'تم اضافة عميل ' . $clientname;

                $dailyEntryDebtor->value = $clientdebt;
                $dailyEntryDebtor->accountstreeid = $treeId;

                $dailyEntryCreditor->value = $clientdebt;
                $dailyEntryCreditor->accountstreeid = 121;

                $dailyEntryDebtorArray = array();
                $dailyEntryCreditorArray = array();
                array_push($dailyEntryDebtorArray, $dailyEntryDebtor);
                array_push($dailyEntryCreditorArray, $dailyEntryCreditor);

                $returnedData = insertEntery($dailyEntry, $dailyEntryDebtorArray, $dailyEntryCreditorArray, 0, $clientid, "clientController.php?do=edit&id=$clientid");
                $did = $returnedData[1];

                $rowData = $clientDAO->load($clientid);
                $rowData->dailyentryid = $did;
                $rowData->treeId = $treeId;
                $clientDAO->update($rowData);


                $flag = '0';
            } else {
                $flag = '3';
            }
        } else {


            $flag = '2';
        }
    } else {


        $flag = '1';
    }


    //go back to your db
    if (isset($erpDB) && !empty($erpDB)) {
        $_SESSION['dbname'] = $sessionDBName;
        unset($_SESSION['userid']); //tempppppppppppppppp
    }

    return array($flag, $clientid);
}

// add in client tbl
function loadClient() {
    global $clientDAO;

    $clientData = $clientDAO->queryByCondition(0);

    return $clientData;
}

function deleteFinaly() {
    global $clientDAO;

    $id = $_POST["id"];
    $action = $_POST["action"];



    $erpDB = $_POST["erpDB"];
    $sessionDBName = ConnectionProperty::getDatabase();
    if (isset($_SESSION['dbname']) && !empty($_SESSION['dbname'])) {
        $sessionDBName = $_SESSION['dbname'];
    }
    //choose erpDB user has choosen in obygy_app
    if (isset($erpDB) && !empty($erpDB)) {
        $_SESSION['dbname'] = $erpDB;
        $_SESSION['userid'] = 1; //tempppppppppppppppp
    }
    #################################

    reverseEntryWithItsID($action);
    $rowData = $clientDAO->load($id);

    delTreeElement($rowData->clientname);
    $loadData = $clientDAO->delete($id);

    //go back to your db
    if (isset($erpDB) && !empty($erpDB)) {
        $_SESSION['dbname'] = $sessionDBName;
        unset($_SESSION['userid']); //tempppppppppppppppp
    }
}

function update() {
    global $clientDAO;
    global $client;
    global $today;
    global $accountsTreeDAO;
    global $accountsTree;
    global $storeDAO;
    global $clientExt;

    $id = $_POST["id"];

    //$conditions = $_POST['conditions'];
    $clientname = $_POST["newclientName"];
    $clientaddress = $_POST["clientaddress"];
    $clientphone = $_POST["clientphone"];
    $clientmobile = $_POST["clientmobile"];
    $clientdebt = (float) $_POST["newclientdebt"];
    $clientdebt = round($clientdebt, 2);
    $clientdetails = $_POST["clientdetails"];
    $clientareaid = $_POST["clientareaid"];
    $clientTypeForTree = (int) $_POST["clientTypeForTree"];

    $clientcode = $_POST["clientcode"];
    //$dailyentryid = $_POST["dailyentryid"];
    $store_all = $_POST["store_all"];
    $today = date("Y-m-d H:i:s");

    //it is used for insert client from obygy_app
    $obygyPatientId = (int) $_POST["obygyPatientId"];
    if ($obygyPatientId > 0)
        $clientcode = (int) $_POST['statusno'];
    $husdandname = $_POST["husdandname"];
    $erpDB = $_POST["erpDB"];
    $sessionDBName = ConnectionProperty::getDatabase();
    if (isset($_SESSION['dbname']) && !empty($_SESSION['dbname'])) {
        $sessionDBName = $_SESSION['dbname'];
    }
    //choose erpDB user has choosen in obygy_app
    if (isset($erpDB) && !empty($erpDB)) {
        $_SESSION['dbname'] = $erpDB;
        $_SESSION['userid'] = 1; //tempppppppppppppppp
        if (empty($clientname)) {
            $clientname = 'obj_app_client' . $obygyPatientId;
        }
        $client->obygyPatientId = $obygyPatientId;
    }

    //update into client
    $client->clientcode = $clientcode;

    $client->clientid = $id;
    $client->clientaddress = $clientaddress;
    $client->clientdate = $today;
    $client->clientdebt = $clientdebt;
    $client->clientdetails = $clientdetails;
    $client->clientmobile = $clientmobile;
    $client->clientname = $clientname;
    $client->clientphone = $clientphone;
    $client->husbandNameObgy = $husdandname;
    //$client->conditions = $conditions;
    $client->userid = $_SESSION['userid'];
    $client->clientareaid = $clientareaid;
    $client->clientTypeForTree = $clientTypeForTree;
    //$client->dailyentryid = $dailyentryid;
    if (isset($store_all) && $store_all == 1) {
        $client->clientStoreIds = '-10'; //all stores
    } else {
        $client->clientStoreIds = '';
        $stores = $storeDAO->queryAll();
        foreach ($stores as $data) {
            $selectStore = $_POST["store" . $data->storeId];
            if (isset($selectStore) && $selectStore == 1) {
                $client->clientStoreIds .= $data->storeId . ',';
            }
        }
        $client->clientStoreIds = rtrim($client->clientStoreIds, ",");
    }

    $oldClient = $clientDAO->load($id);
    $client->conditions = $conditions = $oldClient->conditions;
    $client->dailyentryid = $dailyentryid = $oldClient->dailyentryid;
    $treeId = $oldClient->treeId;

    if ($clientTypeForTree == 0)
        $parent = 58;
    elseif ($clientTypeForTree == 1)
        $parent = 60;
    if (!isset($treeId) || empty($treeId)) {
        $treeClient = $accountsTreeDAO->load($treeId);
        if (!isset($treeClient->id) || empty($treeClient->id)) {
            $treeId = addTreeElement($oldClient->clientname, 23, 3, 0, 1, '', 0, $oldClient->clientdebt);
        } else {
            $treeId = $treeClient[0]->id;
        }
    }
    $client->treeId = $treeId;

    $oldTree = $accountsTreeDAO->load($treeId);
    $oldTree->customName = $clientname;
    $oldTree->name = $clientname;
    $oldTree->parent = $parent;
    //print_r($oldTree);
    editTreeElement($oldTree);
    //update
    // $clientDAO->update($client);
    //to stop update clientdebt for royal
    $clientExt->updateButNotDept($client);
}

function showAllClientsAjax() {
    global $clientExt;
    global $TypeClientDAO;
    global $ProgramsettingDAO;
    $sIndexColumn = "clientid";

    //'clientdebtCredit'//له
    //'clientdebtDebt'//عليه
    /* DB table to use */
    $sTable = "client
                left join user on user.userid = client.userid
                left join user as delegate on (delegate.userid = client.delegateid and client.delegateid > 0)
                left join user as lasteditone on lasteditone.userid = client.lastEditUser
                left join clientarea on clientarea.id = client.clientareaid
                left join typeclient on client.typeclientid LIKE CONCAT('%', typeclient.typeId, '%')";
    $aColumns = array('clientid', 'clientname', 'clientphone', 'clientmobile', 'clientarea.name as areaName', 'clientdebtCredit', 'clientdebtDebt', 'debtLimit', 'typeclientid', 'user.employeename','delegate.employeename as deligateName','lasteditone.employeename as lastEditUserName','addDate', 'client.conditions', 'client.dailyentryid');
    $aColumnsWithoutAS = array('clientid', 'clientname', 'clientphone', 'clientmobile', 'clientarea.name', 'clientdebt', 'clientdebt', 'debtLimit', 'typeclient.typeName', 'user.employeename','delegate.employeename', 'lasteditone.employeename','addDate', 'client.conditions', 'client.dailyentryid');
    $aColumnsWithout_clientmobile_for_ordering = array('clientid', 'clientname', 'clientphone', 'clientarea.name', 'clientdebt', 'clientdebt', 'debtLimit', 'typeclient.typeName', 'user.employeename','delegate.employeename', 'lasteditone.employeename','addDate', 'client.conditions', 'client.dailyentryid');

    // 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
            $sOrder .= $aColumnsWithout_clientmobile_for_ordering[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") {
            //                //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 ";
            //                }
            //            }
            $sWhere .= $aColumnsWithoutAS[$i] . " LIKE '%" . $_POST['search']['value'] . "%' OR ";
        }
        $sWhere = substr_replace($sWhere, "", -3);
        $sWhere .= ")";
    }
    //-- search ------------------------------------------------------------
    if ($sWhere == '') {
        $queryString = ' WHERE';
    } else {
        $queryString = ' AND';
    }


    $datefrom = $_POST['datefrom'];
    $dateto = $_POST['dateto'];
    $userid = $_POST['userid'];
    $areaid = $_POST['areaid'];
    $typeid = $_POST['typeid'];

    if ($datefrom)
        $queryString .= ' client.addDate >= "' . $datefrom . '" AND';

    if ($dateto)
        $queryString .= ' client.addDate <= "' . $dateto . '" AND';

    if ($userid && $userid != -2)
        $queryString .= ' client.userid = ' . $userid . ' AND';

    if ($areaid && $areaid != -1)
        $queryString .= ' client.clientareaid = ' . $areaid . ' AND';

    if ($typeid && $typeid != -1)
        $queryString .= ' client.typeclientid like "%,' . $typeid . ',%" or typeclientid like "%,-20,%" AND';
    $message = "";
    $del = (int) $_REQUEST['del'];
    if ($del == 1) {
        $queryString .= ' client.conditions = 1 AND';
    } else {
        $queryString .= ' client.conditions = 0 AND';
    }
    $queryString .= ' client.linkedSupplierId = 0 AND';
    ##needed Data
    $Programsetting = $ProgramsettingDAO->load(1);
    ##############
    $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>");
    }


    $sWhere .= $queryString;
    //----------------------------------------------------------------------

    $aColumnsForSelect = str_replace("clientdebtCredit", "clientdebt", $aColumns);
    $aColumnsForSelect = str_replace("clientdebtDebt", "clientdebt", $aColumnsForSelect);
    $sQuery = "SELECT SQL_CALC_FOUND_ROWS distinct " . str_replace(" , ", " ", implode(", ", $aColumnsForSelect)) . " FROM  $sTable $sWhere $sOrder $sLimit ";
    // echo $sQuery;
    $rResult = $clientExt->runSelectQuery($sQuery);

    /* Data set length after filtering */
    $sQuery = "SELECT COUNT( distinct " . $sIndexColumn . ") as clientid  FROM  $sTable $sWhere "; //$sOrder
    // echo $sQuery;
    $rResultFilterTotal = $clientExt->runSelectQuery($sQuery);
    foreach ($rResultFilterTotal as $oneis) {
        $countis = $oneis->clientid;
    }
    $iFilteredTotal = $countis;
    /* Total data set length */
    $sQuery = "SELECT COUNT( distinct " . $sIndexColumn . ") as clientid FROM  $sTable";
    $rResultTotal = $clientExt->runSelectQuery($sQuery);
    $totalDebtNotDel = $clientExt->sumDebtByQueryString('where conditions = 0');
    $totalDebtNotDelPositiveDebtOnly = $clientExt->sumDebtByQueryString('where conditions = 0 and clientdebt > 0');
    $totalDebtNotDelNegativeDebtOnly = $clientExt->sumDebtByQueryString('where conditions = 0 and clientdebt < 0');
    $totalDebtDel = $clientExt->sumDebtByQueryString('where conditions = 1');
    $allTypeClient = $TypeClientDAO->queryAll();

    foreach ($rResultTotal as $oneis) {
        $countit = $oneis->clientid;
    }
    //$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;
    $totalDebtShownNotDel = 0;
    $totalDebtShownDel = 0;
    foreach ($rResult as $aRow) {
        $row = array();
        for ($i = 0; $i < count($aColumns); $i++) {

            switch ($aColumns[$i]) {
                case "clientid":
                    //$row[] = $aRow->$aColumns[0];
                    $row[] = '<input type="checkbox" name="choosedItem[]" id="checked' . $rowno . '" class="choosenItem" value="' . $aRow->clientid . '" />';
                    if ($Programsetting->clientserial == 1) {
                        $row[] = $rowno;
                    }
                    break;
                case "clientname":
                    $aRow->clientname = mb_convert_encoding($aRow->clientname, 'UTF-8', 'UTF-8');
                    $row[] = $aRow->clientname;
                    break;
                case "clientphone":
                    $aRow->clientphone = mb_convert_encoding($aRow->clientphone, 'UTF-8', 'UTF-8');
                    $aRow->clientmobile = mb_convert_encoding($aRow->clientmobile, 'UTF-8', 'UTF-8');
                    $whatsapp = '';
                    if ($aRow->clientphone != "") {
                        $whatsapp = '<a target="_blank" href="https://wa.me/+2' . $aRow->clientphone . '" class="pull-left mgtp3 hidden-print" title="whatsapp"><img src="../views/default/assets/img/whats.jpg" style="height: 22px;width: 26px"></a>';
                    }
                    $row[] = $aRow->clientphone . $whatsapp . "<br/>" . $aRow->clientmobile;
                    break;
                case "clientmobile":
                    //do nothing it is shown with clientphone field
                    break;
                case "clientarea.name as areaName":
                    $row[] = $aRow->areaName;
                    break;
                case "clientdebtCredit": //له
                    if ($aRow->clientdebt < 0) {
                        $row[] = abs($aRow->clientdebt);
                    } else {
                        $row[] = 0;
                    }

                    break;
                case "clientdebtDebt": //عليه
                    if ($aRow->clientdebt > 0) {
                        $row[] = $aRow->clientdebt;
                    } else {
                        $row[] = 0;
                    }
                    break;
                case "debtLimit":
                    $row[] = $aRow->debtLimit;
                    break;
                case "typeclientid":
                    $typeArr = explode(',', $aRow->typeclientid);
                    $typeText = '';
                    if (in_array('-20', $typeArr)) {
                        $typeText = 'كل انواع العملاء';
                    } elseif ($aRow->typeclientid === 0 || $aRow->typeclientid === '') {
                        $typeText = '-';
                    } else {
                        foreach ($allTypeClient as $type) {
                            if (in_array($type->typeId, $typeArr)) {
                                $typeText .= "$type->typeName</br>";
                            }
                        }
                    }
                    $row[] = $typeText;
                    break;
                case "user.employeename":
                    $row[] = $aRow->employeename;
                    break;
                case "delegate.employeename as deligateName":
                        $row[] = $aRow->deligateName;
                        break;
                case "lasteditone.employeename as lastEditUserName":
                    $row[] = $aRow->lastEditUserName;
                    break;
                case "addDate":
                    $row[] = $aRow->addDate;
                    break;
                default:
                    break;
            }
        }
        //$netValues = getNetValues($aRow->clientid);
        // $row[] = ''; //$netValues[0];
        // $row[] = ''; //$netValues[1];
        //
        if ($Programsetting->clientdetail == 0) {
            $row[] = '<a href="clientController.php?do=printdatail&id=' . $aRow->clientid . '" type="button" class="btn btn-default btn-lg editicon" target="_blank"></a>';
        }
        if ($aRow->conditions == 0) {
            $row[] = '<a href="clientController.php?do=tempdelete&id=' . $aRow->clientid . '" type="button" class="btn btn-default btn-lg removeicon"></a>';
        } elseif ($aRow->conditions == 1) {
            $row[] = '<a href="clientController.php?do=returndelete&id=' . $aRow->clientid . '" type="button" class="btn btn-default btn-lg correcticon"></a>';
        }
        //
        $row[] = '<a href="clientController.php?do=edit&id=' . $aRow->clientid . '" type="button" class="btn btn-default btn-lg editicon"></a>';
        $row[] = '<a type="button" data-id="' . $aRow->clientid . '" class="btn btn-default btn-lg freeTheClient correcticon"></a>';
        //
        if ($Programsetting->allowfinsldelete == 0) {
            if ($aRow->clientid == 1) {
                $row[] = '';
            } else {
                //if ($aRow->clientid != 1) {
                $row[] = '<a href="#" name="dd" type="button" class="btn btn-default btn-lg deleteicon conformdel1" data-position="clientController.php?do=deleteFinaly&id=' . $aRow->clientid . '&action=' . $aRow->dailyentryid . '"></a>';
            }
        }

        $row[] = '<a href="dailyentry.php?id=' . $aRow->dailyentryid . '" target="_blank" type="button" class="btn btn-default btn-lg editicon"> </a>';
        $row[] = '<a href="clientReportsController.php?clientid=' . $aRow->clientid . '&find=month" target="_blank" type="button" class="btn btn-default btn-lg gotolinkicon"> </a>';

        if ($aRow->conditions == 0) {
            $totalDebtShownNotDel += $aRow->clientdebt;
        } elseif ($aRow->conditions == 1) {
            $totalDebtShownDel += $aRow->clientdebt;
        }
        $output['data'][] = $row;
        $rowno++;
    }
    $output['totals']["totalDebtShownNotDel"] = $totalDebtShownNotDel;
    $output['totals']["totalDebtShownDel"] = $totalDebtShownDel;
    $output['totals']["totalDebtNotDel"] = $totalDebtNotDel;
    $output['totals']["totalDebtNotDelPositiveDebtOnly"] = $totalDebtNotDelPositiveDebtOnly;
    $output['totals']["totalDebtNotDelNegativeDebtOnly"] = $totalDebtNotDelNegativeDebtOnly;
    $output['totals']["totalDebtDel"] = $totalDebtDel;
    //print_r($output);

    echo json_encode($output, JSON_UNESCAPED_SLASHES); //note we use mb_convert_encoding($aRow->$aColumns[$i], 'UTF-8', 'UTF-8'); to correct encoding of error that stop this function
    //var_dump(json_last_error_msg());//Returns the error string of the last json_encode() or json_decode() call
}

function select2ClientsAjax() {
    global $clientExt;
    global $myUserRecord;
    $userData = $myUserRecord->load($_SESSION['userid']);

    $row_array = array();
    $return_arr = array();

    $name = $_GET['term']; //It could be product name or category name
    $withAddOption = (int) $_GET['withAddOption'];
    $withNakdi = (int) $_GET['withNakdi'];
    $clientIsNotLinkeddToSupp = (int) $_GET['clientIsNotLinkeddToSupp'];
    $oldClientId = (int) $_GET['oldClientId'];
    $limit = 15; //intval($_GET['page_limit']);


    $queryString = '';
    if ($clientIsNotLinkeddToSupp == 1) {
        $queryString .= " and client.linkedSupplierId = 0 ";
        if ($oldClientId > 0) {
            $queryString .= " or clientid = $oldClientId ";
        }
    }

    if ($userData->viewclients == 0) {
        $queryString .= ' AND client.userid =' . $_SESSION['userid'];
    }
    //
    if ($name[0] == "*") { //first char is * search in client code only
        $name = str_replace('*', '', $name);
        $queryString .= ' and conditions = 0 and clientcode LIKE "%' . $name . '%" order by clientid asc limit ' . $limit;
    } elseif ($name[0] == "i") {
        $name = (int) str_replace('i', '', $name);
        $queryString . ' and conditions = 0 and clientid = ' . $name . ' order by clientid asc limit ' . $limit;
    } else {
        $queryString .= ' and conditions = 0 and (clientname LIKE "%' . $name . '%" or clientphone LIKE "%' . $name . '%" or clientmobile LIKE "%' . $name . '%" or clientid LIKE "%' . $name . '%" or clientcode LIKE "%' . $name . '%" or husbandNameObgy LIKE "%' . $name . '%") order by clientid asc limit ' . $limit;
    }


    $clientData = $clientExt->queryAllWithQueryString($queryString);
    if ($withAddOption == 1) {
        $row_array['id'] = "-2";
        $row_array['text'] = "اضافة عميل جديد";
        $row_array['specialDiscount'] = 0;
        $row_array['specialDiscountVal'] = 0;
        array_push($return_arr, $row_array);
    }
    if (count($clientData) > 0) {
        $i = 1;
        foreach ($clientData as $data) {
            if ($data->clientid == 1) {
                if ($withNakdi == 1) {
                    $row_array['id'] = $data->clientid;
                    $row_array['text'] = $data->clientname;
                    $row_array['specialDiscount'] = $data->specialDiscount;
                    $row_array['specialDiscountVal'] = $data->specialDiscountVal;
                    $row_array['postponeDays'] = $data->postponeDays;
                    $row_array['postponeDate'] = /* ($data->postponeDays == 0)? '': */ Date('Y-m-d', strtotime("+$data->postponeDays days"));
                    array_push($return_arr, $row_array);
                }
            } else {
                $row_array['id'] = $data->clientid;
                $row_array['text'] = '';
                if (!empty($data->clientcode))
                    $row_array['text'] .= "$data->clientcode - ";
                $row_array['text'] .= $data->clientname;
                if (!empty($data->husbandNameObgy))
                    $row_array['text'] .= " / $data->husbandNameObgy";
                $row_array['specialDiscount'] = $data->specialDiscount;
                $row_array['specialDiscountVal'] = $data->specialDiscountVal;
                $row_array['postponeDays'] = $data->postponeDays;
                $row_array['postponeDate'] = /* ($data->postponeDays == 0)? '': */ Date('Y-m-d', strtotime("+$data->postponeDays days"));
                $row_array['mobile'] = $data->clientmobile;
                array_push($return_arr, $row_array);
            }


            //if ($withAddOption == 1 && $i == 1) {
            //            if ($withAddOption == 1 && $data->clientid == 1) {
            //                $row_array['id'] = "-2";
            //                $row_array['text'] = "اضافة عميل جديد";
            //                $row_array['specialDiscount'] = 0;
            //                $row_array['specialDiscountVal'] = 0;
            //                array_push($return_arr, $row_array);
            //            }


            $i++;
        }
    }


    //echo $return_arr;
    echo json_encode($return_arr);
}

function getAvailablePSCardValue($clientid) {
    global $playstationClientCardEX;

    $availablePSCardValue = 0;
    $clientPlayStationCardData = $playstationClientCardEX->getClientPSCards($clientid);

    foreach ($clientPlayStationCardData as $data) {
        //        $row_array['id'] = $data->id; //playstationclientcardid
        //        $row_array['text'] = $data->cardName;
        //        $row_array['cardVal'] = $data->packVal - $data->packValConsumed;
        //        $row_array['cardid'] = $data->cardid;
        $availablePSCardValue += $data->packVal - $data->packValConsumed;
    }


    return $availablePSCardValue;
}

function select2ClientPlaystationCardsAjax() {
    global $playstationClientCardEX;

    $row_array = array();
    $return_arr = array();

    $name = $_GET['term']; //It could be product name or category name
    $clientId = $_GET['client'];
    $limit = 15; //intval($_GET['page_limit']);

    $queryString = " limit $limit ";
    $clientPlayStationCardData = $playstationClientCardEX->getClientPSCards($clientId, $queryString);
    if (count($clientPlayStationCardData) > 0) {
        foreach ($clientPlayStationCardData as $data) {
            $row_array['id'] = $data->id; //playstationclientcardid
            $row_array['text'] = $data->cardName;
            $row_array['cardVal'] = $data->packVal - $data->packValConsumed;
            $row_array['cardid'] = $data->cardid;
            array_push($return_arr, $row_array);
        }


        //echo $return_arr;
        echo json_encode($return_arr);
    }
}

function select2PlaystationCardsAjax() {
    global $playstationCardEX;

    $row_array = array();
    $return_arr = array();

    $name = $_GET['term']; //It could be product name or category name
    //$clientId = $_GET['client'];
    $limit = 15; //intval($_GET['page_limit']);

    $queryString = " limit $limit ";
    $playstationCard = $playstationCardEX->queryAllEX($queryString);
    if (count($playstationCard) > 0) {
        foreach ($playstationCard as $data) {
            $row_array['id'] = $data->id;
            $row_array['text'] = $data->name;
            array_push($return_arr, $row_array);
        }
        echo json_encode($return_arr);
    }
}

function CURL_IT2($data_arr, $url) {
    ##preparing session data
    //$projectURL = 'http://' . $_SERVER['HTTP_HOST'] . explode('controllers', $_SERVER['REQUEST_URI'])[0] . 'controllers/';
    $url = 'http://' . $_SERVER['HTTP_HOST'] . explode('controllers', $_SERVER['REQUEST_URI'])[0] . 'controllers/' . $url;

    ##appending necessary data
    $data_arr['curlpost'] = '1';
    $data_arr['sessionlist'] = json_encode($_SESSION);


    // set post fields
    //    $post = [
    //        'data_arr' => json_encode($data_arr), //encode it as it is array
    //    ];
    $post = $data_arr;
    $ch = curl_init($url);
    //
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    curl_setopt($ch, CURLOPT_POSTFIELDS, $post); //
    //Disable CURLOPT_SSL_VERIFYHOST and CURLOPT_SSL_VERIFYPEER by
    //setting them to false.
    curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, false);
    curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
    //curl_setopt($ch, CURLOPT_TIMEOUT, 10); //
    // execute!
    $response = curl_exec($ch);
    if ($response === false) {
        //echo 'Error : ' . curl_error($ch);
    }

    // close the connection, release resources used
    curl_close($ch);
    // do anything you want with your response
    //var_dump($response);
    //print_r($response);
    return $response;
}

class productData {

    public $id = 0;
    public $productName = '';
    public $soldNo = 0;
    public $soldVal = 0;
    public $returnNo = 0;
    public $returnVal = 0;

}

function getNetValues($clientId) {
    global $prog;
    global $returnSellBillDetailEX;
    global $sellAndRuternBillDetailEX;
    global $ProductEX;

    //
    $queryString = " where 1 "; //bills
    $queryStringR = " where 1 "; //billsreturn
    $queryString1 = " where 1 "; //sellbill
    $queryString1R = " where 1 "; //sellbillreturn
    $queryString1SR = " where 1 "; //sellbillandreturn

    if ($clientId) {
        $queryString .= 'and  bills.clientid  = ' . $clientId . ' ';
        $queryStringR .= 'and  bills.clientid  = ' . $clientId . ' ';
        $queryString1 .= 'and  sellbill.sellbillclientid  = ' . $clientId . ' ';
        $queryString1R .= 'and  returnsellbill.returnsellbillclientid  = ' . $clientId . ' ';
        $queryString1SR .= 'and  sellbillandrutern.sellbillclientid  = ' . $clientId . ' ';
    }

    if ($queryString1 == " where 1 ") {
        return;
    }
    $existId = array();
    $allDataArr = array();
    $totalsObj = new productData();
    $flag = 0;

    $sellBillData = R::getAll('SELECT sellbillid,sellbill.sellbillclientid as clientid,sellbill.sellbilltotalbill as parcode,sellbill.sellbillaftertotalbill as note,sellbill.userid
                    ,sellbilldiscount,sellbilldiscounttype
                    FROM sellbill ' . $queryString1 . ' AND conditions = 0');
    $sellBillDataReturn = R::getAll('SELECT returnsellbillid,returnsellbillclientid as clientid,returnsellbill.returnsellbilltotalbill as parcode,returnsellbill.returnsellbillaftertotalbill as note,returnsellbill.userid
                    ,returnsellbilldiscount,returnsellbilldiscounttype
                    FROM returnsellbill ' . $queryString1R . ' AND conditions = 0');
    $sellBillDataSellAndReturn = R::getAll('SELECT sellbillid,sellbillandrutern.sellbillclientid as clientid,sellbillandrutern.sellbilltotalbill as parcode,sellbillandrutern.sellbillaftertotalbill as note,sellbillandrutern.userid
                    ,sellbilldiscount,sellbilldiscounttype
                    FROM sellbillandrutern ' . $queryString1SR . ' AND conditions = 0'); //AND sellbillandrutern.deletedsellid=0//order by sellandruternbilldetail.sellbilldetailproductid


    list($productCatIdsArr, $productIdsArrAll) = [[], []];
    ########################################################################
    ##فاتورة المبيعات
    list($billIDs, $clientIDs) = [[], []];
    foreach ($sellBillData as $value) {
        $billIDs[] = $value['sellbillid'];
        $clientIDs[] = $value['clientid'];
    }
    $billIDs = !empty($billIDs) ? array_unique($billIDs) : [0];
    $clientIDs = !empty($clientIDs) ? array_unique($clientIDs) : [0];

    //getclientName && apply search with client type to clients of bills
    if ($clientIDs != [0]) {
        $clientDataArr = R::getAll('select clientid,clientname from client where clientid in(' . implode(',', $clientIDs) . ') ');
        $clientDataArr = customArrayIndexOne($clientDataArr, 'clientid');
        $sellBillDataTemp = array();
        foreach ($sellBillData as $value) {
            if ($clientDataArr[$value['clientid']] !== null) {
                $value['clientname'] = $clientDataArr[$value['clientid']]['clientname'];
                $sellBillDataTemp[] = $value;
            }
        }

        $sellBillData = $sellBillDataTemp;
    }
    //get billdetails && apply search with productid
    if ($billIDs != [0]) {
        $sqlProducts = '';
        if (!empty($IDSOfProducts))
            $sqlProducts = ' and sellbilldetail.sellbilldetailproductid in(' . $IDSOfProducts . ') ';
        $sellBillDetailData = R::getAll('SELECT sellbillid,sellbilldetailproductid,sellbilldetailquantity,sellbilldetail.productunitid,sellbilldetailtotalprice,productunit.productnumber
                ,buyprice,lastbuyprice,meanbuyprice,lastbuyprice_withDiscount,meanbuyprice_withDiscount
              FROM sellbilldetail
              join productunit on(productunit.productid = sellbilldetail.sellbilldetailproductid and productunit.productunitid=sellbilldetail.productunitid and productunit.conditions = 0)
              where sellbilldetail.sellbillid in(' . implode(',', $billIDs) . ') ' . $sqlProducts . ' AND conditions = 0'); //-- order by sellbilldetail.sellbilldetailproductid
        $sellBillDetailData = customArrayIndexMany($sellBillDetailData, 'sellbillid');
        $sellBillDataTemp = array();
        list($productIDsArr) = [[]];
        foreach ($sellBillData as $value) {
            foreach ($sellBillDetailData[$value['sellbillid']] as $data) {
                foreach ($data as $key1 => $value1) {
                    $value[$key1] = $value1;
                }
                $productIDsArr[] = (int) $value['sellbilldetailproductid'];
                $sellBillDataTemp[] = $value;
            }
        }
        $sellBillData = $sellBillDataTemp;
        $productIdsArrAll = array_merge($productIdsArrAll, $productIDsArr);
        $productIDsArr = !empty($productIDsArr) ? array_unique($productIDsArr) : [0];

        //' . $priceName . '
        $productDataArr = R::getAll('select productId,productName,overAllAveragePrice as generalPrice,productBuyPrice as storeid,product.productCatId,isService
                            from product where productId in(' . implode(',', $productIDsArr) . ') ');
        $productDataArr = customArrayIndexOne($productDataArr, 'productId');
        $sellBillDataTemp = array();
        foreach ($sellBillData as $value) {
            if ($productDataArr[$value['sellbilldetailproductid']] !== null) {
                $productCatIdsArr[] = $productDataArr[$value['sellbilldetailproductid']]['productCatId'];
                foreach ($productDataArr[$value['sellbilldetailproductid']] as $key1 => $value1) {
                    $value[$key1] = $value1;
                }
                $sellBillDataTemp[] = $value;
            }
        }
        $sellBillData = $sellBillDataTemp;
    }

    //from key value pair to std class
    $sellBillDataTemp = array();
    foreach ($sellBillData as $value) {
        $obj = new stdClass();
        foreach ($value as $key1 => $value1) {
            $obj->$key1 = $value1;
        }
        $sellBillDataTemp[] = $obj;
    }
    $sellBillData = $sellBillDataTemp;
    foreach ($sellBillData as $value) {
        if (in_array($value->sellbilldetailproductid, $existId)) {
            $key = array_search($value->sellbilldetailproductid, $existId);
            $myproduct = $allDataArr[$key];
        } else {
            $key = -1;
            $myproduct = new productData();
            $myproduct->id = $value->sellbilldetailproductid;
            array_push($existId, $value->sellbilldetailproductid);
        }

        $myproduct->productName = $value->productName;
        #########get quantity بالقطعة
        $quantity = $value->sellbilldetailquantity;
        $productId = $value->sellbilldetailproductid;
        $productunitId = $value->productunitid;
        $finalquantity = $quantity * $value->productnumber;
        ##############
        $myproduct->soldNo += $finalquantity; //العدد بالقطعة
        ######get discount
        $dicount = 0;

        $dicount = $value->sellbilldiscount;
        if ($value->sellbilldiscounttype == 2) {
            $dicount = ($value->sellbilldiscount / 100) * $value->parcode;
        }
        //$dicount = $value->parcode - $value->note;

        if ($dicount != 0) {
            $theDiscount = ($value->sellbilldetailtotalprice / $value->parcode) * $dicount;
            $theDiscount = round($theDiscount, 2);
        } else {
            $theDiscount = $dicount;
            $theDiscount = round($theDiscount, 2);
        }
        ################
        if ($prog->valueadded == 0) {
            $myproduct->soldVal += $value->sellbilldetailtotalprice - $theDiscount;
        } else {
            $myproduct->soldVal += $value->sellbilldetailtotalprice + $theDiscount;
        }
        if ($key == -1) {
            array_push($allDataArr, $myproduct);
        }
    }
    ########################################################################
    ##فاتورة مردود المبيعات
    list($billIDs, $clientIDs) = [[], []];
    foreach ($sellBillDataReturn as $value) {
        $billIDs[] = $value['returnsellbillid'];
        $clientIDs[] = $value['clientid'];
    }
    $billIDs = !empty($billIDs) ? array_unique($billIDs) : [0];
    $clientIDs = !empty($clientIDs) ? array_unique($clientIDs) : [0];

    if ($clientIDs != [0]) {
        $clientDataArr = R::getAll('select clientid,clientname from client where clientid in(' . implode(',', $clientIDs) . ') ');
        $clientDataArr = customArrayIndexOne($clientDataArr, 'clientid');
        $sellBillDataTemp = array();
        foreach ($sellBillDataReturn as $value) {
            if ($clientDataArr[$value['clientid']] !== null) {
                $value['clientname'] = $clientDataArr[$value['clientid']]['clientname'];
                $sellBillDataTemp[] = $value;
            }
        }

        $sellBillDataReturn = $sellBillDataTemp;
    }
    //get billdetails && apply search with productid
    if ($billIDs != [0]) {
        $sqlProducts = '';
        if (!empty($IDSOfProducts))
            $sqlProducts = ' and returnsellbilldetail.returnsellbilldetailproductid in(' . $IDSOfProducts . ') ';
        $sellBillDetailData = R::getAll('SELECT returnsellbillid,returnsellbilldetailproductid,returnsellbilldetailquantity,returnsellbilldetail.productunitid,returnsellbilldetailtotalprice,productunit.productnumber
                ,buyprice,lastbuyprice,meanbuyprice,lastbuyprice_withDiscount,meanbuyprice_withDiscount
              FROM returnsellbilldetail
              join productunit on(productunit.productid = returnsellbilldetail.returnsellbilldetailproductid and productunit.productunitid=returnsellbilldetail.productunitid and productunit.conditions = 0)
              where returnsellbilldetail.returnsellbillid in(' . implode(',', $billIDs) . ') ' . $sqlProducts . ' AND returnsellbilldetail.conditions = 0'); //-- order by returnsellbilldetail.returnsellbilldetailproductid
        $sellBillDetailData = customArrayIndexMany($sellBillDetailData, 'returnsellbillid');
        $sellBillDataTemp = array();
        list($productIDsArr) = [[]];
        foreach ($sellBillDataReturn as $value) {
            foreach ($sellBillDetailData[$value['returnsellbillid']] as $data) {
                foreach ($data as $key1 => $value1) {
                    $value[$key1] = $value1;
                }
                $productIDsArr[] = (int) $value['returnsellbilldetailproductid'];
                $sellBillDataTemp[] = $value;
            }
        }
        $sellBillDataReturn = $sellBillDataTemp;

        //get data of product tbl
        $productIdsArrAll = array_merge($productIdsArrAll, $productIDsArr);
        $productIDsArr = !empty($productIDsArr) ? array_unique($productIDsArr) : [0];

        //' . $priceName . '
        $productDataArr = R::getAll('select productId,product.productName as productName,product.overAllAveragePrice as generalPrice,product.productBuyPrice as storeid,product.productCatId,isService
                    from product where productId in(' . implode(',', $productIDsArr) . ') ');
        $productDataArr = customArrayIndexOne($productDataArr, 'productId');
        $sellBillDataTemp = array();
        foreach ($sellBillDataReturn as $value) {
            //$found_index = searchForId($value['returnsellbilldetailproductid'], $productDataArr, 'productId');
            if ($productDataArr[$value['returnsellbilldetailproductid']] !== null) {
                $productCatIdsArr[] = $productDataArr[$value['returnsellbilldetailproductid']]['productCatId'];
                foreach ($productDataArr[$value['returnsellbilldetailproductid']] as $key1 => $value1) {
                    $value[$key1] = $value1;
                }
                $sellBillDataTemp[] = $value;
            }
        }
        $sellBillDataReturn = $sellBillDataTemp;
    }

    //from key value pair to std class
    $sellBillDataTemp = array();
    foreach ($sellBillDataReturn as $value) {
        $obj = new stdClass();
        foreach ($value as $key1 => $value1) {
            $obj->$key1 = $value1;
        }
        $sellBillDataTemp[] = $obj;
    }
    $sellBillDataReturn = $sellBillDataTemp;
    foreach ($sellBillDataReturn as $value) {
        if (in_array($value->returnsellbilldetailproductid, $existId)) {
            $key = array_search($value->returnsellbilldetailproductid, $existId);
            $myproduct = $allDataArr[$key];
        } else {
            $key = -1;
            $myproduct = new productData();
            $myproduct->id = $value->returnsellbilldetailproductid;
            array_push($existId, $value->returnsellbilldetailproductid);
        }

        $myproduct->productName = $value->productName;
        #########get quantity بالقطعة
        $quantity = $value->returnsellbilldetailquantity;
        $productId = $value->returnsellbilldetailproductid;
        $productunitId = $value->productunitid;
        $finalquantity = $quantity * $value->productnumber;
        ##############
        $myproduct->returnNo += $finalquantity; //العدد بالقطعة
        ######get discount
        $dicount = 0;
        $dicount = $value->returnsellbilldiscount;
        if ($value->returnsellbilldiscounttype == 2) {
            $dicount = $value->returnsellbilldiscount * $value->parcode;
        }
        if ($dicount != 0) {
            $billpecies = $returnSellBillDetailEX->queryBillNoOfPecies($value->returnsellbillid);
            $billNoOfProduct = $billpecies->note;
            $flag = 1;
        }
        if ($flag == 1) {
            $theDiscount = ($value->returnsellbilldetailtotalprice / $value->parcode) * $dicount;
            $theDiscount = round($theDiscount, 2);
            $flag = 0;
        } else {
            $theDiscount = $dicount;
            $theDiscount = round($theDiscount, 2);
        }
        ################
        if ($prog->valueadded == 0) {
            $myproduct->returnVal += $value->returnsellbilldetailtotalprice - $theDiscount;
        } else {
            $myproduct->returnVal += $value->returnsellbilldetailtotalprice + $theDiscount;
        }
        if ($key == -1) {
            array_push($allDataArr, $myproduct);
        }
    }
    ########################################################################
    ##فاتورة مبيعات و مردود
    list($billIDs, $clientIDs) = [[], []];
    foreach ($sellBillDataSellAndReturn as $value) {
        $billIDs[] = $value['sellbillid'];
        $clientIDs[] = $value['clientid'];
    }
    $billIDs = !empty($billIDs) ? array_unique($billIDs) : [0];
    $clientIDs = !empty($clientIDs) ? array_unique($clientIDs) : [0];

    //getclientName && apply search with client type to clients of bills
    if ($clientIDs != [0]) {
        $clientDataArr = R::getAll('select clientid,clientname from client where clientid in(' . implode(',', $clientIDs) . ') ');
        $clientDataArr = customArrayIndexOne($clientDataArr, 'clientid');
        $sellBillDataTemp = array();
        foreach ($sellBillDataSellAndReturn as $value) {
            if ($clientDataArr[$value['clientid']] !== null) {
                $value['clientname'] = $clientDataArr[$value['clientid']]['clientname'];
                $sellBillDataTemp[] = $value;
            }
        }

        $sellBillDataSellAndReturn = $sellBillDataTemp;
    }
    //get billdetails && apply search with productid
    if ($billIDs != [0]) {
        $sqlProducts = '';
        if (!empty($IDSOfProducts))
            $sqlProducts = ' and sellandruternbilldetail.sellbilldetailproductid in(' . $IDSOfProducts . ') ';
        $sellBillDetailData = R::getAll('SELECT sellbillid,sellbilldetailproductid,sellbilldetailquantity,sellandruternbilldetail.productunitid,sellbilldetailtotalprice,productunit.productnumber
                ,buyprice,lastbuyprice,meanbuyprice,lastbuyprice_withDiscount,meanbuyprice_withDiscount
              FROM sellandruternbilldetail
              join productunit on(productunit.productid = sellandruternbilldetail.sellbilldetailproductid and productunit.productunitid=sellandruternbilldetail.productunitid and productunit.conditions = 0)
              where sellandruternbilldetail.sellbillid in(' . implode(',', $billIDs) . ') ' . $sqlProducts . ' AND conditions = 0'); //-- order by sellandruternbilldetail.sellbilldetailproductid
        $sellBillDetailData = customArrayIndexMany($sellBillDetailData, 'sellbillid');
        $sellBillDataTemp = array();
        list($productIDsArr) = [[]];
        foreach ($sellBillDataSellAndReturn as $value) {
            foreach ($sellBillDetailData[$value['sellbillid']] as $key) {
                $data = $key;
                foreach ($data as $key1 => $value1) {
                    $value[$key1] = $value1;
                }
                $productIDsArr[] = (int) $value['sellbilldetailproductid'];
                $sellBillDataTemp[] = $value;
            }
        }
        $sellBillDataSellAndReturn = $sellBillDataTemp;

        //get data of product tbl
        $productIdsArrAll = array_merge($productIdsArrAll, $productIDsArr);
        $productIDsArr = !empty($productIDsArr) ? array_unique($productIDsArr) : [0];

        //' . $priceName . '
        $productDataArr = R::getAll('select productId,product.productName as productName,product.overAllAveragePrice as generalPrice,product.productBuyPrice as storeid,product.productCatId,isService
                    from product where productId in(' . implode(',', $productIDsArr) . ') ');
        $productDataArr = customArrayIndexOne($productDataArr, 'productId');
        $sellBillDataTemp = array();
        foreach ($sellBillDataSellAndReturn as $value) {
            if ($productDataArr[$value['sellbilldetailproductid']] !== null) {
                $productCatIdsArr[] = $productDataArr[$value['sellbilldetailproductid']]['productCatId'];
                foreach ($productDataArr[$value['sellbilldetailproductid']] as $key1 => $value1) {
                    $value[$key1] = $value1;
                }
                $sellBillDataTemp[] = $value;
            }
        }
        $sellBillDataSellAndReturn = $sellBillDataTemp;
    }

    //from key value pair to std class
    $sellBillDataTemp = array();
    foreach ($sellBillDataSellAndReturn as $value) {
        $obj = new stdClass();
        foreach ($value as $key1 => $value1) {
            $obj->$key1 = $value1;
        }
        $sellBillDataTemp[] = $obj;
    }
    $sellBillDataSellAndReturn = $sellBillDataTemp;
    foreach ($sellBillDataSellAndReturn as $value) {
        if (in_array($value->sellbilldetailproductid, $existId)) {
            $key = array_search($value->sellbilldetailproductid, $existId);
            $myproduct = $allDataArr[$key];
        } else {
            $key = -1;
            $myproduct = new productData();
            $myproduct->id = $value->sellbilldetailproductid;
            array_push($existId, $value->sellbilldetailproductid);
        }

        $myproduct->productName = $value->productName;
        #########get quantity بالقطعة
        $quantity = $value->sellbilldetailquantity;
        $productId = $value->sellbilldetailproductid;
        $productunitId = $value->productunitid;
        $productunitData = loadProductUnitWithProductAndUnit($productId, $productunitId);
        $productnumber = $productunitData->productnumber;
        $finalquantity = $quantity * $productnumber;
        $myproduct->buyPrice = $value->storeid;
        ##############
        ######get discount
        $dicount = 0;
        $billNoOfProduct = 0;
        $dicount = $value->sellbilldiscount;
        if ($value->sellbilldiscounttype == 2) {
            $dicount = ($value->sellbilldiscount / 100) * $value->parcode;
        }
        //$dicount = $value->parcode - $value->note;
        if ($dicount != 0) {
            $billpecies = $sellAndRuternBillDetailEX->queryBillNoOfPecies($value->sellbillid);
            $billNoOfProduct = $billpecies->note;
            $flag = 1;
        }

        if ($flag == 1) {
            $theDiscount = ($value->sellbilldetailtotalprice / $value->parcode) * $dicount;
            $theDiscount = round($theDiscount, 2);
            $flag = 0;
        } else {
            $theDiscount = $dicount;
            $theDiscount = round($theDiscount, 2);
        }

        ################
        if ($value->selltype == 0) {
            $myproduct->soldNo += $finalquantity; //العدد بالقطعة
            $myproduct->soldVal += $value->sellbilldetailtotalprice - $theDiscount;
        } else {
            $myproduct->returnNo += $finalquantity; //العدد بالقطعة
            $myproduct->returnVal += $value->sellbilldetailtotalprice + $theDiscount;
        }
        if ($key == -1) {
            array_push($allDataArr, $myproduct);
        }
    }
    ########################################################################
    #############finally get net , real cost ,net profit
    $productCatIdsArr = !empty($productCatIdsArr) ? array_unique($productCatIdsArr) : [0];
    $productIdsArrAll = !empty($productIdsArrAll) ? array_unique($productIdsArrAll) : [0];
    $productCatDataArr = R::getAll('select productCatId,productCatName from productcat where productCatId in(' . implode(',', $productCatIdsArr) . ') ');
    $productCatDataArr = customArrayIndexOne($productCatDataArr, 'productCatId');
    $myQuery = "";
    if ($_SESSION['searchinonestore'] == 0) {
        if ($_SESSION['storeids'] != 0) {
            $myQuery = ' and storedetail.storeid in (' . $_SESSION['storeids'] . ') ';
        }
    } else {
        $myQuery = " and storedetail.storeid = " . $_SESSION['storeid'];
        ;
    }
    $quantityAndLimitDataArr = R::getAll('SELECT product.productId,product.limitamount,SUM(storedetail.productquantity) AS sumProductQuantity
                    FROM product
                    JOIN storedetail
                    ON storedetail.productid = product.productId
                    WHERE product.productId in(' . implode(',', $productIdsArrAll) . ')
                    ' . $myQuery . '
                    group by storedetail.productid');
    $quantityAndLimitDataArr = customArrayIndexOne($quantityAndLimitDataArr, 'productId');
    foreach ($allDataArr as $data) {
        if ($productCatDataArr[$data->productCatId] !== null) {
            $data->productCatName = $productCatDataArr[$data->productCatId]['productCatName'];
        }
        $data->netNo = $data->soldNo - $data->returnNo;
        $data->netVal = $data->soldVal - $data->returnVal;
        $data->netProfit = $data->netVal - $data->realCost;

        ##currentQuantity
        if ($quantityAndLimitDataArr[$data->id] !== null) {
            $data->currentQuantity = $quantityAndLimitDataArr[$data->id]['sumProductQuantity'];
            $data->limitamount = $quantityAndLimitDataArr[$data->id]['limitamount'];
        }
        $totalsObj->soldNo += $data->soldNo;
        $totalsObj->soldVal += $data->soldVal;
        $totalsObj->returnNo += $data->returnNo;
        $totalsObj->returnVal += $data->returnVal;
        $totalsObj->netNo += $data->netNo;
        $totalsObj->netVal += $data->netVal;
        $totalsObj->realCost += $data->realCost;
        $totalsObj->netProfit += $data->netProfit;
        $totalsObj->currentQuantity += $data->currentQuantity;
    }
    return [$totalsObj->soldVal, $totalsObj->returnVal];
}
