This topic is locked

Presentation of hierarchies in the "list" option

9/1/2018 8:24:27 AM
PHPRunner Tips and Tricks
fhumanes author

I have been studying previous messages and I have not seen how you solve the hierarchical presentation of records in the "LIST" view.
I present to you how I have solved "my problem" and if you find a better method, I beg you to indicate it to me.
Presentation of the problem:

  • I use open source tools for project management (DotProject and Web2Project).
  • The reports and data extraction are not what we require and that is why we use PHPRunner to exploit the information of these applications, plus our own data that we manage in the projects.
  • I need to present the Tasks within the project ordered by their hierarchy and the planned start date of the same.


Solution:

  • I need to add the following information to the task information:

    . Presentation order within the Project.

    . Level of the Task hierarchy.

    . If the task has child tasks or not.
  • It is also necessary that the system is efficient, that is, that it does not have much cost in the processing of the information.
    Work description:
  • I create 1 auxiliary table to complete the data of the Tasks that later I will join the data of the Tasks through a JOIN.
  • I create 1 auxiliary table to see if it is necessary to refresh the auxiliary data added.


The attached code is added in the LIST event of the "tasks" table. The event is "BeforeQueryList"
CODE PHP:

<?php

global $conn;

// Extracción de la MasterKey

$temp_clave=$strWhereClause;

$pos1=stripos($temp_clave, 'task_project=');

$pos2=stripos(substr($temp_clave,$pos1), ' ');

$clave= substr($temp_clave,$pos1+13,$pos2-13);

// Control para ver si hay que actualizar las tablas auxiliares de ordenación de Tareas

$sql="SELECT count(project_id) Actualizado

FROM project_last_update C join (

select task_project, max(task_updated) task_updated from tasks where task_project= $clave group by 1 ) t

on (project_id = task_project and task_updated <= project_last_update )";

$resql = db_query($sql,$conn);

$row = db_fetch_array($resql);

$Actualizado=$row['Actualizado'];

if ( $Actualizado == 0 ) {

// Crear tablas auxiliares de Ordenación

$sql = "SELECT task_id,if (task_parent = task_id,'',task_parent) task_parent , task_name, task_project, task_start_date, task_end_date

FROM tasks Where task_project = $clave order by task_start_date";

$results = array();

if ($resql = db_query($sql,$conn)) {

/* obtener array asociativo */

while ($row = db_fetch_array($resql)) {

$results[] = $row;

}

}

// Crea ARRAY con la Jerarquía de los datos

function convertToHierarchy($results, $idField='task_id', $parentIdField='task_parent', $childrenField='children') {

$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;

}

// Transforma el ARRAY de la jerarquía en un ARRAY plano y añade información de Orden, Nivel y si es Registro Maestro ( tiene registros dependientes)

function makeOneLevelArray($in, &$out, $level, &$num_record) {

foreach ($in as $v) {

$num_record++;

$out[$num_record] = $v;

$out[$num_record]['Orden'] = $num_record;

$out[$num_record]['Nivel'] = $level;

if (!empty($v['children'])) {

$out[$num_record]['Maestro'] = 1;

unset($out[$num_record]['children']); // eliminamos Hijos de matriz de salida

makeOneLevelArray($v['children'], $out, $level+1, $num_record );

} else {

$out[$num_record]['Maestro'] = 0;

}

}

}

$Jerarquia = convertToHierarchy($results);

$taskArray = array();

$level=1;

$num_record = 0;

makeOneLevelArray($Jerarquia, $taskArray, $level, $num_record);

$sql="delete from task_order where task_project = $clave";

db_query($sql,$conn); // Borrado de los registros de "Orden" anteriores

$sql = "INSERT INTO task_order (task_id, task_project, task_orden, task_level, task_master) VALUES ";

foreach ($taskArray as $v) {

$task_id = $v['task_id'];

$task_project = $v['task_project'];

$task_orden = $v['Orden'];

$task_level = $v['Nivel'];

$task_master = $v['Maestro'];

$sql .="($task_id, $task_project, $task_orden, $task_level, $task_master),";

}

$sql=substr($sql, 0, -1); // Quitar la última coma

db_query($sql,$conn); // Insert de todos los registros ordenados

$sql="delete from project_last_update where project_id = $clave";

db_query($sql,$conn); // Borrado del registro control de actualizaciones

$sql="INSERT INTO project_last_update (project_id, project_last_update)

select task_project, max(task_updated) from tasks where task_project= $clave group by 1";

db_query($sql,$conn); // actualización del egistro control de actualizaciones de tasks

}

?>


More information at BLOG
If you need any information contact me through [email="fernandohumanes@gmail.com"]email[/email]

fhumanes author 9/6/2018

Hello
I have seen that some of us, in addition to having tasks in a certain order requires a graphic representation and that there are doubts in the example / tutorial that is offered for the JSGantt Javascript function.


I have expanded and published the example to see if it can help any of you.
Create GANTT.



<?php

global $conn;

$keyMaster=$data['project_id'];

$trunk1="<link rel=\"stylesheet\" type=\"text/css\" href=\"jsgantt/jsgantt.css\" />

<script language=\"javascript\" src=\"jsgantt/jsgantt.js\"></script>

<div style=\"position:relative\" class=\"gantt\" id=\"GanttChartDIV\"></div>

<script>

var g = new JSGantt.GanttChart(document.getElementById('GanttChartDIV'), 'week');

if (g.getDivId() != null) {

g.setCaptionType('Complete'); // Set to Show Caption (None,Caption,Resource,Duration,Complete)

g.setQuarterColWidth(36);

g.setDateTaskDisplayFormat('day dd month yyyy'); // Shown in tool tip box

g.setDayMajorDateDisplayFormat('mon yyyy - Week ww') // Set format to display dates in the \"Major\" header of the \"Day\" view

g.setWeekMinorDateDisplayFormat('dd mon') // Set format to display dates in the \"Minor\" header of the \"Week\" view

g.setShowTaskInfoLink(1); // Show link in tool tip (0/1)

g.setShowEndWeekDate(0); // Show/Hide the date for the last day of the week in header for daily view (1/0)

g.setUseSingleCell(10000); // Set the threshold at which we will only use one cell per table row (0 disables). Helps with rendering performance for large charts.

g.setFormatArr('Day', 'Week', 'Month', 'Quarter'); // Even with setUseSingleCell using Hour format on such a large chart can cause issues in some browsers

// Language

g.addLang('es',{'january': 'Enero',

'february': 'Febrero',

'march': 'Marzo',

'april': 'Abril',

'maylong': 'Mayo',

'june': 'Junio',

'july': 'Julio',

'august': 'Agosto',

'september': 'Septiembre',

'october':'Octubre',

'november':'Noviembre',

'december':'Diciembre',

'jan':'Ene',

'feb':'Feb',

'mar':'Mar',

'apr':'Abr',

'may':'May',

'jun':'Jun',

'jul':'Jul',

'aug':'Ago',

'sep':'Sep',

'oct':'Oct',

'nov':'Nov',

'dec':'Dic',

'sunday':'Domingo',

'monday':'Lunes',

'tuesday':'Martes',

'wednesday':'Miércoles',

'thursday':'Jueves',

'friday':'Viernes',

'saturday':'Sábado',

'sun':' Dom',

'mon':' Lun',

'tue':' Mar',

'wed':' Mie',

'thu':' Jue',

'fri':' Vie',

'sat':' Sab',

'resource':'Recurso',

'duration':'Duración',

'comp':'% Completado',

'completion':'Terminado',

'startdate':'Inicio',

'enddate':'Fin',

'moreinfo':'+información',

'notes':'Notas',

'format':'Formato',

'hour':'Hora',

'day':'Dia',

'week':'Semana',

'month':'Mes',

'quarter':'Trimestre',

'hours':'Horas',

'days':'Días',

'weeks':'Semanas',

'months':'Meses',

'quarters':'Trimestres',

'hr':'Hr',

'dy':'D',

'wk':'Sem',

'mth':'Mes',

'qtr':'Trim',

'hrs':'Hrs',

'dys':'Dias',

'wks':'Sems',

'mths':'Meses',

'qtrs':'Trims'});

g.setLang('es');

// Parameters (pID, pName, pStart, pEnd, pStyle, pLink (unused) pMile, pRes,

";

$trunk2='';

// Read Task the Project

$sql="SELECT O.task_level, O.task_master,O.task_orden,t.task_id,t.task_name,

if (t.task_parent=t.task_id,0 ,t.task_parent) task_parent,

t.task_milestone,

t.task_project,t.task_owner,

DATE_FORMAT(t.task_start_date,'%Y-%m-%d') task_start_date,

t.task_duration,t.task_duration_type,t.task_hours_worked,

DATE_FORMAT(t.task_end_date,'%Y-%m-%d') task_end_date,

t.task_status,t.task_priority,t.task_percent_complete,t.task_description,t.task_target_budget,

t.task_related_url,t.task_creator,t.task_order,t.task_client_publish,t.task_dynamic,t.task_access,t.task_notify,

t.task_departments,t.task_contacts,t.task_custom,t.task_type,t.task_updator,t.task_created,t.task_updated,t.task_dep_reset_dates,

t.task_represents_project,t.task_allow_other_user_tasklogs

FROM tasks AS t

INNER JOIN task_order AS O ON t.task_id = O.task_id and t.task_project = $keyMaster

ORDER BY O.task_orden";

$resql = db_query($sql,$conn);

while ($row = db_fetch_array($resql)) {

$task_id = $row['task_id'];

$task_name = $row['task_name'];

$task_start_date = $row['task_start_date'];

$task_end_date = $row['task_end_date'];

$task_color = 'gtaskblue';

if ($row['task_master'] == 1){

$task_color = 'ggroupblack';

}

$task_link = $row['task_related_url'];

$task_mile = $row['task_milestone'];

$task_resource = '';

$task_completion= $row['task_percent_complete'];

$task_group = $row['task_master'];

$task_parent = $row['task_parent'];

$task_open = '1';

$task_depende = '';

$task_caption = '';

$trunk2.="g.AddTaskItem(new JSGantt.TaskItem(

$task_id,'$task_name','$task_start_date','$task_end_date','$task_color','$task_link',$task_mile, '$task_resource',$task_completion,$task_group,$task_parent,$task_open,'$task_depende','$task_caption','',g));";;

}

$trunk3="

g.Draw();

g.DrawDependencies();

}

else

{

alert(\"not defined\");

}

</script>";

$value=$trunk1.$trunk2.$trunk3;

?>


It is published in BLOG
For any questions, please, communicate it to me through [email="fernandohumanes@gmail.com"]email[/email].
Regards,

fernando