<?php
//  Create ARRAY with the data Hierarchy
function convertToHierarchy($results, $idField='user_id', $parentIdField='Boss', $childrenField='') {
      $hierarchy = array(); // -- Stores the final data
      $itemReferences = array(); // -- temporary array, storing references to all items in a single-dimention
      foreach ( $results as $item ) {
            $id       = $item[$idField];
            $parentId = $item[$parentIdField];
            if (isset($itemReferences[$parentId])) { // parent exists
                  $itemReferences[$parentId][$childrenField][$id] = $item; // assign item to parent
                  $itemReferences[$id] =& $itemReferences[$parentId][$childrenField][$id]; // reference parent's item in single-dimentional array
            } elseif (!$parentId || !isset($hierarchy[$parentId])) { // -- parent Id empty or does not exist. Add it to the root
                  $hierarchy[$id] = $item;
                  $itemReferences[$id] =& $hierarchy[$id];
            }
      }
      unset($results, $item, $id, $parentId);
      // -- Run through the root one more time. If any child got added before it's parent, fix it.
      foreach ( $hierarchy as $id => &$item ) {
            $parentId = $item[$parentIdField];
            if ( isset($itemReferences[$parentId] ) ) { // -- parent DOES exist
                  $itemReferences[$parentId][$childrenField][$id] = $item; // -- assign it to the parent's list of children
                  unset($hierarchy[$id]); // -- remove it from the root of the hierarchy
            }
      }
      unset($itemReferences, $id, $item, $parentId);
      return $hierarchy;
} // 

function identation($level) {
    $identation ='';
    for ($i = 1;$i < $level; $i++) {
        $identation .= "\t";
    }
    return $identation;
}
// Print data of record 
function printRow($level, $row) {
    global $datasource;
    $datasource .= identation($level)."{ 'id': '".$row['id_user']."', 'name': '".addslashes($row['name'])."', 'title': '".addslashes($row['category'])."', 'picture': '".addslashes($row['picture'])."'";
};
function printHierarchy ($level, $Hierarchy) {
    global $datasource;

    do {
        $key = key($Hierarchy);
        $value = current($Hierarchy);
        printRow($level+1, $value);
         if ( is_array($value[children])){
             $datasource .= ",\n".identation($level+1)."  'children': [ \n";
             printHierarchy ($level+1, $value[children]);  // Recursive
             $datasource .= "\n".identation($level+1)."  ]";
         }            
        $datasource .= "},\n";     
    } while (next($Hierarchy));
    $datasource = substr($datasource, 0, -2); // Delete last ","
}

global $conn;
// Create auxiliary Sorting tables
$results = array();
$sql = "SELECT id_orgchart_user id_user, ifnull(boss_id,'') id_parent , name, category,  picture FROM orgchart_user";
$resql = DB::Query($sql); 
    /* get associative array */
while ($row = $resql->fetchAssoc()) {
	$fileArray = my_json_decode($row["picture"]);
	$picture = $fileArray[0]["name"];
	if ($picture == NULL) $picture = 'files/no_existe.png';
	$results[] = ['id_user' =>$row['id_user'],'id_parent' =>$row['id_parent'],'name' =>$row['name'], 'category' =>$row['category'],'picture' =>$picture];
	// $results[] = $row;
 }
$Hierarchy = convertToHierarchy($results,'id_user','id_parent','children'); // Create hierarchy from data

unset($results); // Delete variable
$level = 0;

$datasource = '';
global $datasource;

if ( count($Hierarchy) <> 0 ) {
    printHierarchy ($level, $Hierarchy); 
}

?>