<?php

require_once __DIR__."/allfunctions.php";
$con = mysqli_connect(get_dbserver(),get_dbuser(),get_dbpassword(),get_dbname());


$index = array();
$values  = array();

$idtest = 1;

$strSQL1 = "select  id,
       name,
       parent_id,
       amount
from    (select * from test
        order by parent_id, id) products_sorted,
       (select @pv := '".$idtest."') initialisation
where   find_in_set(parent_id, @pv)
and     length(@pv := concat(@pv, ',', id)) ";
        $sqlQuery1 = mysqli_query($con,$strSQL1);
        //$numRows = mysqli_num_rows($sqlQuery);
        while($rows = mysqli_fetch_array($sqlQuery1,MYSQLI_ASSOC)) {
          //$val = array($rows['id'] => $rows['parent_id']);
          $val[] = $rows;
         // $a = array("id"=>$rows['id'], "name"=>$rows['name'], "parent_id"=>'0', "amount"=>$rows['amount']);
          //$val[] = array_column($rows, 'name', 'parent_id');
           //var_dump($a);
           //array_push($val,$a);
          //var_dump($val);
          //$val[] = array_column($rows, 'name', 'parent_id');
           //var_dump($val);
        }
        //$strSQL1 = "SELECT id,name,parent_id,amount  FROM test where  id=".$idtest."  ";
          $strSQL1 = "SELECT id,name,parent_id,amount  FROM test where  parent_id=1";
        $sqlQuery2 = mysqli_query($con,$strSQL1);
        //$numRows = mysqli_num_rows($sqlQuery);
        while($rows = mysqli_fetch_array($sqlQuery2,MYSQLI_ASSOC)) {
          //$val = array($rows['id'] => $rows['parent_id']);


          $a = array("id"=>$rows['id'], "name"=>$rows['name'], "parent_id"=>'0', "amount"=>$rows['amount']);
          //$val[] = array_column($rows, 'name', 'parent_id');
           //var_dump($a);
           array_push($val,$a);
          // print_r($val);
        //   var_dump($val);
           // code...

        }
        //$a = array("id"=>"2", "name"=>"Robert", "parent_id"=>"0", "amount"=>"50");

        mysqli_close($con);


     //JSON Creation
     $result = array();
     $tree = array();
     foreach($val as $item){
          if(!isset($tree[$item['id']])) $tree[$item['id']] = array();
          $tree[$item['id']] = array_merge($tree[$item['id']],$item);
          if(!isset($tree[$item['parent_id']])) $tree[$item['parent_id']] = array();
          if(!isset($tree[$item['parent_id']]['children'])) $tree[$item['parent_id']]['children'] = array();
          $tree[$item['parent_id']]['children'][] = &$tree[$item['id']];
     }
     if(empty($tree))
     {
       $result = "";
     }
     else {

       $result = $tree[0]['children'];
     }
     unset($tree);


function Tablebuild2(array $elements, array $branch, $parentId=0) {
    // group elements by parents if it does not comes on the parameters

    if (empty($branch)) {
        $branch = array();

        foreach ($elements as $element) {
            $branch[$element["parent_id"]][$element["name"]] = $element;
        }
    }

    // echo the childs referenced by the parentId parameter
    if (isset($branch[$parentId])) {
         //echo '<tbody>';
        //echo'<tr>';
        $total = 0;
        foreach ($branch[$parentId] as $keyBranch => $itemBranch) {



           $parent_name = getRecord('name','test','id ='.$itemBranch['parent_id']);
           $agentID = getRecordbyID('id','test','name ="'.$itemBranch['name'].'"');
           $getlevel = getRecord('commisionsoldby','sales','soldby ='.$agentID);
           $amount= (int)$itemBranch['amount'];
           //echo $amount;
           $total = $total + $amount;
           $datecreated = getRecord('datecreated','sales','soldby ='.$parentId);

            echo'<tr>';
            //echo '<td> <b data-toggle="modal" class="trhistory" data-target="#historymodal">'.$datecreated.'</b></td>';
            echo '<td visible="hidden"><b data-toggle="modal" class="trhistory" data-target="#historymodal">'.$datecreated.'</b></td>';
            echo '<td><b data-toggle="modal" class="trhistory" data-target="#historymodal">'.$itemBranch['name'].'</b></td>';
            //$agent_id =  1;
            //$agentnamelevel1 = GetAgentLevelFromParent($agent_id,"lev1");
            //if($agentnamelevel1!="" && $agentnamelevel1=$itemBranch['name'])
            //{
             // echo $agentname;
               $comission = 0;
               //$agentID = getRecord('id','test','name ='.$itemBranch['name']);
               //echo $getlevel;
               if($getlevel == "Level 1")
               {
                 $comission = GetDataUsingSQL("select
                        sum(level1) as level1
                from    (select * from sales
                         order by soldby) products_sorted,
                        (select @pv := '".$agentID."') initialisation
                where   find_in_set(soldby, @pv)
                and     length(@pv := concat(@pv, ',', boughtby))","level1");
                 //$comission = 50;
               }
               elseif($getlevel == "Level 2")
               {
                 $comission = GetDataUsingSQL("select
                        sum(level2) as level2
                from    (select * from sales
                         order by soldby) products_sorted,
                        (select @pv := '".$agentID."') initialisation
                where   find_in_set(soldby, @pv)
                and     length(@pv := concat(@pv, ',', boughtby))","level2");
                 //$comission = 20;
               }
               elseif($getlevel == "Level 3")
               {
                 $comission = GetDataUsingSQL("select
                        sum(level3) as level3
                from    (select * from sales
                         order by soldby) products_sorted,
                        (select @pv := '".$agentID."') initialisation
                where   find_in_set(soldby, @pv)
                and     length(@pv := concat(@pv, ',', boughtby))","level3");
                 //$comission = 15;
               }
               elseif($getlevel == "Level 4")
               {
                 $comission = GetDataUsingSQL("select
                        sum(level4) as level4
                from    (select * from sales
                         order by soldby) products_sorted,
                        (select @pv := '".$agentID."') initialisation
                where   find_in_set(soldby, @pv)
                and     length(@pv := concat(@pv, ',', boughtby))","level4");
                 //$comission = 10;
               }
               elseif($getlevel == "Level 5")
               {
                 $comission = GetDataUsingSQL("select
                        sum(level5) as level5
                from    (select * from sales
                         order by soldby) products_sorted,
                        (select @pv := '".$agentID."') initialisation
                where   find_in_set(soldby, @pv)
                and     length(@pv := concat(@pv, ',', boughtby))","level5");
                 //$comission = 5;
               }

               //$qty = getRecord('sum(quantity)','sales','soldby ='.$parentId);
               $productID = getRecord('productID','sales','soldby ='.$parentId);
               $productname = getRecordbyID('name','products','id ="'.$productID.'"');
               echo '<td> <b data-toggle="modal" class="trhistory" data-target="#historymodal">'. $parent_name.'</b></td>';
               //echo '<td> <b>'. $qty.'</b></td>';
               //echo '<td> <b>'. $comission.'</b></td>';
               $total =  (int)$comission;
               //$total = (int)$comission;
               echo '<td> <b data-toggle="modal" class="trhistory" data-target="#historymodal">'. $total. ' PHP</b> </td>';
               //echo '<td> <b>Product: '. $productname.' Quantity: '. $qty.' Comission: '. $comission.'</b></td>';
            //}
              //echo '<td> <b>'. $itemBranch['amount'].'</b></td>';
            //echo '<td> <b>'. $itemBranch['amount'].'</b></td>';
            //echo '<li> Amount: '. array_sum(intval($itemBranch['amount']));
              // iterate with the actual Id to check if this record have childs
              Tablebuild($elements, $branch, $itemBranch["id"]);
              echo '</tr>';
             }


        //echo '</tr>';
        //echo '</tbody>';
    }

}

         echo'<table class="table table-bordered userDataTable" id="userDataTable" width="100%" cellspacing="0">';
         echo'<thead>';
         echo'<th>Date</th>';
         echo'<th>Name</th>';
         echo'<th>Referred by</th>';
         echo'<th>Comission</th>';
         echo'</thead>';
         echo'</tbody>';
         echo Tablebuild2($val, array());
         echo'</tbody>';
         echo'</table>';

 ?>
