<?php

$do = isset($_GET['do']) ? $_GET['do'] : "";
$ajaxDoArr = array("getUsers");

if (!in_array($do, $ajaxDoArr)) {
    //the global file operation
    include("../public/impOpreation.php");
}

//global varable
global $showoutside;

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



//here the db files that include in the file
// GOES HERE ....................
include("../public/include_dao.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');

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


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

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


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


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

$langs = $_SESSION['erp_lang'];
include_once("../views/languages/$langs/success.php");
include_once("../views/languages/$langs/error.php");

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

  Controller Name :- userCTRL

  OPERTATION in Controller

  1-display add form
  2- add in user tble
  3-display sucess or error
  4-display show form
  5-delete from user tbl
  6-edit in user data

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

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



//here goes the instances and general variables
//user
$myUser = new User();
$myUserRecord = new UserMySqlDAO();
$myUserEx = new UserMySqlExtDAO();

//Returnsellbill
$Returnsellbill = new Returnsellbill();
$ReturnsellbillDAO = new ReturnsellbillMySqlDAO();
$ReturnsellbillEX = new ReturnsellbillMySqlExtDAO();


//Sellbillandrutern
$Sellbillandrutern = new Sellbillandrutern();
$SellbillandruternDAO = new SellbillandruternMySqlDAO();
$SellbillandruternEX = new SellbillandruternMySqlExtDAO();


//Sellbill
$Sellbill = new Sellbill();
$SellbillDAO = new SellbillMySqlDAO();
$SellbillEX = new SellbillMySqlExtDAO();

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

//check and use the condetion that suite this action
if (empty($do)) {
    include_once("../public/authentication.php");
    header("location:?do=show");
} elseif ($do == "show") {
    include_once("../public/authentication.php");

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

    $allsellier = loadseller();
    $smarty->assign('allsellier', $allsellier);
    showAll();

    $smarty->display("userratereportview/show.html");
} elseif ($do == "showWithPriceTypes") {
    include_once("../public/authentication.php");
    $youtubes = $youtubeLinkDAO->queryAll();
    $smarty->assign("youtubes", $youtubes);

    $allsellier = loadseller();
    $smarty->assign('allsellier', $allsellier);

    showAllWithpricesTypes();

    $smarty->display("userratereportview/showWithPriceTypes.html");
} elseif ($do == "getUsers") {
    $sellersOnly = $_POST['sellersOnly'];
    if ($sellersOnly == 1) {
        $allsellier = loadseller();
    } else {
        $allsellier = $myUserRecord->queryByConditions(0);
    }
    $smarty->assign('allsellier', $allsellier);
    $smarty->display("userratereportview/usersData.html");
} elseif ($do == "sucess") {
    //here the smarty templates
    $smarty->display("succes.html");
} elseif ($do == "error") {
    //here the smarty templates
    $smarty->display("error.html");
}
$smarty->assign("customUser", 1);
//here the global templates
if (!in_array($do, $ajaxDoArr)) {
    $smarty->display("footer.html");
}

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

// show the form
function showAll() {
    //to use the variable out side the funcion

    global $myUserRecord;
    global $myUserEx;

    global $SellbillandruternEX;
    global $SellbillEX;
    global $ReturnsellbillEX;

    global $smarty;

    $sellerid = $_POST["sellerid"];
    $startDate = $_POST["startDate"];
    $endDate = $_POST["endDate"];
    $priceType = filter_input(INPUT_POST, 'priceType');
    if ($priceType == -1) {
        $priceType = '0,1,2';
    }
    $totalPiecesNo = 0;
    #1
    if ((!$sellerid || $sellerid == -1) && !$startDate && !$endDate) {
        $totalsell = 0;
        $totalReturnsell = 0;
        $totalSellbillandrutern = 0;

        $userData = $myUserEx->queryWithLevel(3);
        $usersum = (count($userData));
        $awardrate = 0;





        // $selldata = $SellbillEX->queryAllforrateallWithoutSeller();
        $selldata = R::getAll("SELECT sellbilldate,user.username,sellbill.userid,sellbillid,sellbillclientid,sellbill.sellbillaftertotalbill ,sellQuantity,sellerid FROM `sellbill` LEFT JOIN user ON sellbill.sellerid = user.userid WHERE DATE(sellbilldate) = CURRENT_DATE() and sellbill.conditions = 0");

        ///////////////////////////////////////////////////////////
        // get all id
        list($billIDs, $clientIDs, $userIDs, $sellerIDs) = [[], [], [], []];
        foreach ($selldata as $value) {
            $billIDs[] = $value['sellbillid'];
            $clientIDs[] = $value['sellbillclientid'];
            $userIDs[] = $value['userid'];
            $sellerIDs[] = $value['sellerid'];
        }

        //get unique id
        $billIDs = !empty($billIDs) ? array_unique($billIDs) : [0];
        $clientIDs = !empty($clientIDs) ? array_unique($clientIDs) : [0];
        $userIDs = !empty($userIDs) ? array_unique($userIDs) : [0];
        $sellerIDs = !empty($sellerIDs) ? array_unique($sellerIDs) : [0];



        // print_r($clientIDs);
        //getclientName && apply search with client type to clients of bills
        if ($clientIDs != [0]) {
            $clientTypeSql = '';
            // if ($typeIdOfClient > 0)
            // $clientTypeSql = ' and typeclientid = ' . $typeIdOfClient;
            // print_r('select clientid,clientnam  from client where clientid in(' . implode(',', $clientIDs) . ') ');

            $clientDataArr = R::getAll('select clientid,clientname  from client where clientid in(' . implode(',', $clientIDs) . ') ');
            $clientDataArr = customArrayIndexOne($clientDataArr, 'clientid');

            $sellerDataArr = R::getAll('select userid,awardrate from user where userid in(' . implode(',', $sellerIDs) . ') ' . $userQueryString);
            $sellerDataArr = customArrayIndexOne($sellerDataArr, 'userid');

            $sellBillDataTemp = array();
            foreach ($selldata as $value) {
                //$found_index = searchForId($value['clientid'], $clientDataArr, 'clientid');
                if ($clientDataArr[$value['sellbillclientid']] !== null) {
                    $value['clientname'] = $clientDataArr[$value['sellbillclientid']]['clientname'];
                    $value['awardrate'] = $sellerDataArr[$value['sellerid']]['awardrate'];
                    $sellBillDataTemp[] = $value;
                }
            }

            $selldata = $sellBillDataTemp;
        }
        ///////////////////////////////////////////////////////////
        //  print_r($selldata);
        //from key value pair to std class
        $sellBillDataTemp = array();
        foreach ($selldata as $value) {
            $obj = new stdClass();
            foreach ($value as $key1 => $value1) {
                $obj->$key1 = $value1;
            }
            $sellBillDataTemp[] = $obj;
        }
        $selldata = $sellBillDataTemp;

        ##########
        $totalBillsWithSeller = 0;
        $totalAwardValue = 0;
        $totalAwardPer = 0;
        $billWithSellerCount = 0;
        foreach ($selldata as $myselldata) {
            if ($myselldata->sellerid > 0) {
                $totalAwardValue += $myselldata->sellbillaftertotalbill * ($myselldata->awardrate / 100);
                $totalAwardPer += $myselldata->awardrate;
                $totalBillsWithSeller += $myselldata->sellbillaftertotalbill;
                $billWithSellerCount++;
            }
            $totalsell += $myselldata->sellbillaftertotalbill;
            $totalPiecesNo += $myselldata->sellQuantity;
        }


        $Returnselldata = R::getAll('SELECT DISTINCT returnsellbill.*, user.username, client.clientname, returnsellbilldetail.pricetype ,user.awardrate
            FROM `returnsellbill`
            LEFT JOIN user ON returnsellbill.sellerid = user.userid
            LEFT JOIN client ON returnsellbill.returnsellbillclientid = client.clientid
            JOIN returnsellbilldetail ON returnsellbilldetail.returnsellbillid = returnsellbill.returnsellbillid
            WHERE  DATE(returnsellbill.returnsellbilldate) = CURRENT_DATE() and returnsellbill.conditions = 0');
        //$ReturnsellbillEX->queryAllforrateallWithoutSeller('returnsellbill.returnsellbilldate', 'CURRENT_DATE()');
        foreach ($Returnselldata as $myReturnselldata) {
            //print_r($myReturnselldata->returnsellb illaftertotalbill.'hhhhhhhhhhhhh<br>');
            if ($myReturnselldata['sellerid'] > 0) {
                $totalAwardValue -= $myReturnselldata['returnsellbillaftertotalbill'] * ($myReturnselldata['awardrate'] / 100);
                $totalAwardPer -= $myReturnselldata['awardrate'];
                $totalBillsWithSeller -= $myReturnselldata['returnsellbillaftertotalbill'];
                $billWithSellerCount--;
            }
            $totalReturnsell += $myReturnselldata['returnsellbillaftertotalbill'];
            $totalPiecesNo -= $myReturnselldata['returnsellQuantity'];
        }


        $Sellbillandruterndata = R::getAll('SELECT DISTINCT sellbillandrutern.*, user.username, client.clientname, sellandruternbilldetail.pricetype ,user.awardrate
            FROM `sellbillandrutern`
            LEFT JOIN user ON sellbillandrutern.`sellerid` = user.userid
            LEFT JOIN client ON sellbillandrutern.sellbillclientid = client.clientid
            JOIN sellandruternbilldetail ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
            WHERE DATE(sellbillandrutern.sellbilldate) = CURRENT_DATE() and sellbillandrutern.conditions =0');
        //$SellbillandruternEX->queryAllforrateallWithoutSeller();
        foreach ($Sellbillandruterndata as $mySellbillandruterndata) {

            if ($mySellbillandruterndata['sellerid'] > 0) {
                $totalAwardValue += $mySellbillandruterndata['sellbillaftertotalbill'] * ($mySellbillandruterndata['awardrate'] / 100);
                $totalAwardPer += $mySellbillandruterndata['awardrate'];
                $totalBillsWithSeller += $mySellbillandruterndata['sellbillaftertotalbill'];
                $billWithSellerCount++;
            }
            $totalSellbillandrutern += $mySellbillandruterndata['sellbillaftertotalbill'];
            $totalPiecesNo += ($mySellbillandruterndata['sellQuantity'] - $mySellbillandruterndata['returnsellQuantity']);
        }


        /* print_r('totalsell'.$totalsell.'<br>');
          print_r('totalReturnsell'.$totalReturnsell.'<br>');
          print_r('totalSellbillandrutern'.$totalSellbillandrutern.'<br>'); */
        foreach ($userData as $myuserData) {
            $sellerid = $myuserData->userid;
            $userData = $myUserRecord->load($sellerid);

            //print_r($userData);
            $awardrate += $userData->awardrate;

            // assign your db results to the template
        }
        //print_r('awardrate'.$awardrate.'<br>');
        //print_r('userData'.count($userData).'<br>');
        $awardrate = $awardrate / $usersum;

        $maeanAwardPer = round(($totalAwardPer / $billWithSellerCount), 2);
        $message = "تقرير نسبه كل  الموظفين لهذا الشهر ";
        $smarty->assign('message', $message);

        $total = ($totalsell - $totalReturnsell) + $totalSellbillandrutern;
        $final = ($awardrate / 100) * $total;
        $smarty->assign('final', $totalAwardValue); //$final
        $smarty->assign('total', $total);
        $smarty->assign('awardrate', $maeanAwardPer); //$awardrate
        $smarty->assign('totalAwardValue', $totalAwardValue);
        $smarty->assign('totalBillsWithSeller', $totalBillsWithSeller);
        $smarty->assign('selldata', $selldata);
        $smarty->assign('Returnselldata', $Returnselldata);
        $smarty->assign('Sellbillandruterndata', $Sellbillandruterndata);
        $smarty->assign('totalPiecesNo', $totalPiecesNo);
    }
    #2
    elseif ($sellerid != '-1' && isset($sellerid) && $sellerid != '0' && $startDate != '' && isset($startDate) && $endDate != '' && isset($endDate)) {
        $totalsell = 0;
        $totalReturnsell = 0;
        $totalSellbillandrutern = 0;

        $selldata = $SellbillEX->queryAllforrateWithSellerNameDistinct($sellerid, $startDate, $endDate . ' 23:59:59', $priceType);
        foreach ($selldata as $myselldata) {


            $totalsell += $myselldata->sellbillaftertotalbill;
            $totalPiecesNo += $myselldata->sellQuantity;
        }
        $Returnselldata = $ReturnsellbillEX->queryAllforrateWithSellerName($sellerid, $startDate, $endDate . ' 23:59:59', $priceType);
        foreach ($Returnselldata as $myReturnselldata) {
            //print_r($myReturnselldata->returnsellbillaftertotalbill.'hhhhhhhhhhhhh<br>');
            $totalReturnsell += $myReturnselldata->returnsellbillaftertotalbill;
            $totalPiecesNo -= $myReturnselldata->returnsellQuantity;
        }

        $Sellbillandruterndata = $SellbillandruternEX->queryAllforrateWithSellerName($sellerid, $startDate, $endDate . ' 23:59:59', $priceType);
        foreach ($Sellbillandruterndata as $mySellbillandruterndata) {


            $totalSellbillandrutern += $mySellbillandruterndata->sellbillaftertotalbill;
            $totalPiecesNo += ($mySellbillandruterndata->sellQuantity - $mySellbillandruterndata->returnsellQuantity);
        }

        /*  print_r('totalsell'.$totalsell.'<br>');
          print_r('totalReturnsell'.$totalReturnsell.'<br>');
          print_r('totalSellbillandrutern'.$totalSellbillandrutern.'<br>'); */

        $userData = $myUserRecord->load($sellerid);
        $message = "تقرير نسبه موظف من تاريخ :" . $startDate . " الى تاريخ : " . $endDate . " للموظف :" . $userData->employeename;
        $smarty->assign('message', $message);
        //print_r($userData);
        $awardrate = $userData->awardrate;

        $total = ($totalsell - $totalReturnsell) + $totalSellbillandrutern;

        $final = ($awardrate / 100) * $total;

        // assign your db results to the template
        $smarty->assign('final', $final);
        $smarty->assign('total', $total);
        $smarty->assign('awardrate', $awardrate);
        $smarty->assign('selldata', $selldata);
        $smarty->assign('Returnselldata', $Returnselldata);
        $smarty->assign('Sellbillandruterndata', $Sellbillandruterndata);
        $smarty->assign('totalPiecesNo', $totalPiecesNo);
    }
    #3
    elseif ($sellerid != '-1' && isset($sellerid) && $sellerid == '0' && $startDate != '' && isset($startDate) && $endDate != '' && isset($endDate)) {
        $totalsell = 0;
        $totalReturnsell = 0;
        $totalSellbillandrutern = 0;

        $userData = $myUserEx->queryWithLevel(3);
        $usersum = (count($userData));
        $awardrate = 0;




        $selldata = $SellbillEX->queryAllforrateallWithSellerName($startDate, $endDate . ' 23:59:59', $priceType);
        foreach ($selldata as $myselldata) {


            $totalsell += $myselldata->sellbillaftertotalbill;
            $totalPiecesNo += $myselldata->sellQuantity;
        }

        $Returnselldata = $ReturnsellbillEX->queryAllforrateallWithSellerName($startDate, $endDate . ' 23:59:59', $priceType);
        foreach ($Returnselldata as $myReturnselldata) {
            //print_r($myReturnselldata->returnsellbillaftertotalbill.'hhhhhhhhhhhhh<br>');
            $totalReturnsell += $myReturnselldata->returnsellbillaftertotalbill;
            $totalPiecesNo -= $myReturnselldata->returnsellQuantity;
        }


        $Sellbillandruterndata = $SellbillandruternEX->queryAllforrateallWithSellerName($startDate, $endDate . ' 23:59:59', $priceType);
        foreach ($Sellbillandruterndata as $mySellbillandruterndata) {


            $totalSellbillandrutern += $mySellbillandruterndata->sellbillaftertotalbill;
            $totalPiecesNo += ($mySellbillandruterndata->sellQuantity - $mySellbillandruterndata->returnsellQuantity);
        }


        /* print_r('totalsell'.$totalsell.'<br>');
          print_r('totalReturnsell'.$totalReturnsell.'<br>');
          print_r('totalSellbillandrutern'.$totalSellbillandrutern.'<br>'); */
        foreach ($userData as $myuserData) {
            $sellerid = $myuserData->userid;
            $userData = $myUserRecord->load($sellerid);

            //print_r($userData);
            $awardrate += $userData->awardrate;

            // assign your db results to the template
        }
        //print_r('awardrate'.$awardrate.'<br>');
        //print_r('userData'.count($userData).'<br>');

        $awardrate = $awardrate / $usersum;
        $message = "تقرير نسبه كل  الموظفين من تاريخ :" . $startDate . " الى تاريخ : " . $endDate . "  ";
        $smarty->assign('message', $message);

        $total = ($totalsell - $totalReturnsell) + $totalSellbillandrutern;

        $final = ($awardrate / 100) * $total;
        $smarty->assign('final', $final);
        $smarty->assign('total', $total);
        $smarty->assign('awardrate', $awardrate);
        $smarty->assign('selldata', $selldata);
        $smarty->assign('Returnselldata', $Returnselldata);
        $smarty->assign('Sellbillandruterndata', $Sellbillandruterndata);
        $smarty->assign('totalPiecesNo', $totalPiecesNo);
    }
    #4
    elseif ($sellerid != '-1' && isset($sellerid) && $sellerid != '0' && !$startDate && !$endDate) {
    $startDate = $endDate = date('Y-m-d');

        $totalsell = 0;
        $totalReturnsell = 0;
        $totalSellbillandrutern = 0;

        $selldata = $SellbillEX->queryAllforrateWithSellerNameDistinct($sellerid, $startDate, $endDate . ' 23:59:59', $priceType);
        foreach ($selldata as $myselldata) {


            $totalsell += $myselldata->sellbillaftertotalbill;
            $totalPiecesNo += $myselldata->sellQuantity;
        }
        $Returnselldata = $ReturnsellbillEX->queryAllforrateWithSellerName($sellerid, $startDate, $endDate . ' 23:59:59', $priceType);
        foreach ($Returnselldata as $myReturnselldata) {
            //print_r($myReturnselldata->returnsellbillaftertotalbill.'hhhhhhhhhhhhh<br>');
            $totalReturnsell += $myReturnselldata->returnsellbillaftertotalbill;
            $totalPiecesNo -= $myReturnselldata->returnsellQuantity;
        }

        $Sellbillandruterndata = $SellbillandruternEX->queryAllforrateWithSellerName($sellerid, $startDate, $endDate . ' 23:59:59', $priceType);
        foreach ($Sellbillandruterndata as $mySellbillandruterndata) {


            $totalSellbillandrutern += $mySellbillandruterndata->sellbillaftertotalbill;
            $totalPiecesNo += ($mySellbillandruterndata->sellQuantity - $mySellbillandruterndata->returnsellQuantity);
        }

        /* print_r('totalsell'.$totalsell.'<br>');
          print_r('totalReturnsell'.$totalReturnsell.'<br>');
          print_r('totalSellbillandrutern'.$totalSellbillandrutern.'<br>'); */

        $userData = $myUserRecord->load($sellerid);
        $message = "تقرير نسبه موظف من تاريخ :" . $startDate . " الى تاريخ : " . $endDate . " للموظف :" . $userData->employeename;
        $smarty->assign('message', $message);
        //print_r($userData);
        $awardrate = $userData->awardrate;

        $total = ($totalsell - $totalReturnsell) + $totalSellbillandrutern;

        $final = ($awardrate / 100) * $total;

        // assign your db results to the template
        $smarty->assign('final', $final);
        $smarty->assign('total', $total);
        $smarty->assign('awardrate', $awardrate);
        $smarty->assign('selldata', $selldata);
        $smarty->assign('Returnselldata', $Returnselldata);
        $smarty->assign('Sellbillandruterndata', $Sellbillandruterndata);
        $smarty->assign('totalPiecesNo', $totalPiecesNo);
    }
}

function showAllWithpricesTypes() {
    //to use the variable out side the funcion

    global $myUserRecord;
    global $myUserEx;

    global $SellbillandruternEX;
    global $SellbillEX;
    global $ReturnsellbillEX;

    global $smarty;

    $sellerid = $_POST["sellerid"];
    $startDate = $_POST["startDate"];
    $endDate = $_POST["endDate"];
    $sellersOnly = $_POST["sellersOnly"];

    $queryString = ' ';
    $queryStringRet = ' ';

    $message = "تقرير نسبه موظف ";

    if ($startDate) {
        $queryString .= ' AND sellbilldate >= "' . $startDate . '" ';
        $queryStringRet .= ' AND returnsellbilldate >= "' . $startDate . '" ';
        $message .= " من تاريخ :" . $startDate;
    }

    if ($endDate) {
        $queryString .= ' AND sellbilldate <= "' . $endDate . '" ';
        $queryStringRet .= ' AND returnsellbilldate <= "' . $endDate . '" ';
        $message .= " الى تاريخ : " . $endDate;
    }

    if ($sellerid && $sellerid != -1) {
        $smarty->assign('OneSeller', 1);

        $queryString .= ' AND sellerid = ' . $sellerid;
        $queryStringRet .= ' AND sellerid = ' . $sellerid;
        $userName = R::getCell('select employeename from user where userId = ' . $sellerid);
        $message .= " للموظف " . $userName;
    }

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

    if ($sellersOnly == 1) {
        $queryString .= ' AND sellerid != 0';
        $queryStringRet .= ' AND sellerid != 0';
    }

    $totalsell = 0;
    $totalRates = 0;

    $dataArr = [];

    $selldata = R::getAll('SELECT DISTINCT sellbill.*, user.employeename,user.awardrate,user.awardrategomla,user.awardratehalf FROM `sellbill`
        LEFT JOIN user ON sellbill.sellerid = user.userid
        JOIN sellbilldetail ON sellbilldetail.sellbillid = sellbill.sellbillid
        WHERE sellbill.conditions = 0 and pricetype in(0,1,2) ' . $queryString . '');

    foreach ($selldata as $myselldata) {
        $pricetype = $myselldata['pricetype'];

        if (!array_key_exists($myselldata['sellerid'], $dataArr))
            $dataArr[$myselldata['sellerid']] = ["sellerName" => $myselldata['employeename'], 'totalGomla' => 0, 'totalhalf' => 0, 'totalUnit' => 0, 'rateGomla' => 0, 'ratehalf' => 0, 'rateUnit' => 0, 'totalBill' => $myselldata['sellbillaftertotalbill'], 'totalrate' => 0];
        else {
            $dataArr[$myselldata['sellerid']]['totalBill'] = $dataArr[$myselldata['sellerid']]['totalBill'] + $myselldata['sellbillaftertotalbill'];
        }

        if ($pricetype == 0) {
            $billRate = $myselldata['sellbillaftertotalbill'] * $myselldata['awardrategomla'] / 100;
            $dataArr[$myselldata['sellerid']]['totalGomla'] = $dataArr[$myselldata['sellerid']]['totalGomla'] + $myselldata['sellbillaftertotalbill'];
            $dataArr[$myselldata['sellerid']]['rateGomla'] = $dataArr[$myselldata['sellerid']]['rateGomla'] + $billRate;
        }

        if ($pricetype == 2) {
            $billRate = $myselldata['sellbillaftertotalbill'] * $myselldata['awardratehalf'] / 100;
            $dataArr[$myselldata['sellerid']]['totalhalf'] = $dataArr[$myselldata['sellerid']]['totalhalf'] + $myselldata['sellbillaftertotalbill'];
            $dataArr[$myselldata['sellerid']]['ratehalf'] = $dataArr[$myselldata['sellerid']]['ratehalf'] + $billRate;
        }

        if ($pricetype == 1) {
            $billRate = $myselldata['sellbillaftertotalbill'] * $myselldata['awardrate'] / 100;
            $dataArr[$myselldata['sellerid']]['totalUnit'] = $dataArr[$myselldata['sellerid']]['totalUnit'] + $myselldata['sellbillaftertotalbill'];
            $dataArr[$myselldata['sellerid']]['rateUnit'] = $dataArr[$myselldata['sellerid']]['rateUnit'] + $billRate;
        }

        $dataArr[$myselldata['sellerid']]['totalrate'] = $dataArr[$myselldata['sellerid']]['totalrate'] + $billRate;

        $totalsell += $myselldata['sellbillaftertotalbill'];
        $totalRates += $billRate;
    }

    $Returnselldata = R::getAll('SELECT DISTINCT  returnsellbill.*, user.employeename,user.awardrate,user.awardrategomla,user.awardratehalf FROM `returnsellbill`
            LEFT JOIN user ON returnsellbill.sellerid = user.userid
            JOIN returnsellbilldetail ON returnsellbilldetail.returnsellbillid = returnsellbill.returnsellbillid
            WHERE returnsellbill.conditions = 0 and pricetype in(0,1,2) ' . $queryStringRet . ' ');
    foreach ($Returnselldata as $myReturnselldata) {
        $pricetype = $myReturnselldata['pricetype'];

        if (!array_key_exists($myReturnselldata['sellerid'], $dataArr))
            $dataArr[$myReturnselldata['sellerid']] = ["sellerName" => $myReturnselldata['employeename'], 'totalGomla' => 0, 'totalhalf' => 0, 'totalUnit' => 0, 'rateGomla' => 0, 'ratehalf' => 0, 'rateUnit' => 0, 'totalBill' => $myReturnselldata['returnsellbillaftertotalbill'] * -1, 'totalrate' => 0];
        else {
            $dataArr[$myReturnselldata['sellerid']]['totalBill'] = $dataArr[$myReturnselldata['sellerid']]['totalBill'] - $myReturnselldata['returnsellbillaftertotalbill'];
        }

        if ($pricetype == 0) {
            $billRate = $myReturnselldata['returnsellbillaftertotalbill'] * $myReturnselldata['awardrategomla'] / 100;
            $dataArr[$myReturnselldata['sellerid']]['totalGomla'] = $dataArr[$myReturnselldata['sellerid']]['totalGomla'] - $myReturnselldata['returnsellbillaftertotalbill'];
            $dataArr[$myReturnselldata['sellerid']]['rateGomla'] = $dataArr[$myReturnselldata['sellerid']]['rateGomla'] - $billRate;
        }

        if ($pricetype == 2) {
            $billRate = $myReturnselldata['returnsellbillaftertotalbill'] * $myReturnselldata['awardratehalf'] / 100;
            $dataArr[$myReturnselldata['sellerid']]['totalhalf'] = $dataArr[$myReturnselldata['sellerid']]['totalhalf'] - $myReturnselldata['returnsellbillaftertotalbill'];
            $dataArr[$myReturnselldata['sellerid']]['ratehalf'] = $dataArr[$myReturnselldata['sellerid']]['ratehalf'] - $billRate;
        }

        if ($pricetype == 1) {
            $billRate = $myReturnselldata['returnsellbillaftertotalbill'] * $myReturnselldata['awardrate'] / 100;
            $dataArr[$myReturnselldata['sellerid']]['totalUnit'] = $dataArr[$myReturnselldata['sellerid']]['totalUnit'] - $myReturnselldata['returnsellbillaftertotalbill'];
            $dataArr[$myReturnselldata['sellerid']]['rateUnit'] = $dataArr[$myReturnselldata['sellerid']]['rateUnit'] - $billRate;
        }
        $dataArr[$myReturnselldata['sellerid']]['totalrate'] = $dataArr[$myReturnselldata['sellerid']]['totalrate'] - $billRate;

        $totalsell -= $myReturnselldata['returnsellbillaftertotalbill'];
        $totalRates -= $billRate;
    }

    $Sellbillandruterndata = R::getAll('SELECT DISTINCT sellbillandrutern.*, user.employeename,user.awardrate,user.awardrategomla,user.awardratehalf FROM `sellbillandrutern`
    LEFT JOIN user ON sellbillandrutern.`sellerid` = user.userid
    JOIN sellandruternbilldetail ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
    WHERE sellbillandrutern.conditions = 0 and pricetype in(0,1,2) ' . $queryString . ' ');

    foreach ($Sellbillandruterndata as $mysellRetdata) {
        $pricetype = $mysellRetdata['pricetype'];

        if (!array_key_exists($mysellRetdata['sellerid'], $dataArr))
            $dataArr[$mysellRetdata['sellerid']] = ["sellerName" => $mysellRetdata['employeename'], 'totalGomla' => 0, 'totalhalf' => 0, 'totalUnit' => 0, 'rateGomla' => 0, 'ratehalf' => 0, 'rateUnit' => 0, 'totalBill' => $mysellRetdata['sellbillaftertotalbill'], 'totalrate' => 0];
        else {
            $dataArr[$mysellRetdata['sellerid']]['totalBill'] = $dataArr[$mysellRetdata['sellerid']]['totalBill'] + $mysellRetdata['sellbillaftertotalbill'];
        }

        if ($pricetype == 0) {
            $billRate = $mysellRetdata['sellbillaftertotalbill'] * $mysellRetdata['awardrategomla'] / 100;
            $dataArr[$mysellRetdata['sellerid']]['totalGomla'] = $dataArr[$mysellRetdata['sellerid']]['totalGomla'] + $mysellRetdata['sellbillaftertotalbill'];
            $dataArr[$mysellRetdata['sellerid']]['rateGomla'] = $dataArr[$mysellRetdata['sellerid']]['rateGomla'] + $billRate;
        }

        if ($pricetype == 2) {
            $billRate = $mysellRetdata['sellbillaftertotalbill'] * $mysellRetdata['awardratehalf'] / 100;
            $dataArr[$mysellRetdata['sellerid']]['totalhalf'] = $dataArr[$mysellRetdata['sellerid']]['totalhalf'] + $mysellRetdata['sellbillaftertotalbill'];
            $dataArr[$mysellRetdata['sellerid']]['ratehalf'] = $dataArr[$mysellRetdata['sellerid']]['ratehalf'] + $billRate;
        }

        if ($pricetype == 1) {
            $billRate = $mysellRetdata['sellbillaftertotalbill'] * $mysellRetdata['awardrate'] / 100;
            $dataArr[$mysellRetdata['sellerid']]['totalUnit'] = $dataArr[$mysellRetdata['sellerid']]['totalUnit'] + $mysellRetdata['sellbillaftertotalbill'];
            $dataArr[$mysellRetdata['sellerid']]['rateUnit'] = $dataArr[$mysellRetdata['sellerid']]['rateUnit'] + $billRate;
        }
        $dataArr[$mysellRetdata['sellerid']]['totalrate'] = $dataArr[$mysellRetdata['sellerid']]['totalrate'] + $billRate;

        $totalsell += $mysellRetdata['sellbillaftertotalbill'];
        $totalRates += $billRate;
    }

    $smarty->assign('totalsell', $totalsell);
    $smarty->assign('totalRates', $totalRates);
    $smarty->assign('dataArr', $dataArr);
}

// select from user tbl
function loadseller() {

    //to use the variable out side the funcion
    global $myUserEx;

    // select all data from user
    $userData = $myUserEx->queryWithLevel(3);

    return $userData;
}
