This topic is locked

TIP-Code and method to create Organizational Chart

4/3/2012 10:08:10 PM
PHPRunner Tips and Tricks
C
chuckbower author

Hello everyone, a while back I posted a question. How to create ORGANIZATIONAL CHARTS. Well, no responses, but I figured out how to use a Google API to do just this within an event! Here it is! I hope it can be helpful to others. I know my code is NOT pretty, but it works.
First, I created a separate table to contain my data for the chart. I called it OrgChart. OrgChart contains a level number, internal id for employee, internal id for supervisor, full name for employee, full name for supervisor, and the employee's position. Once you look at the code, it will probably make sense.
To do this, I write an html file. First, some of the html is written. Then i do a quick SQL and wipe any data that presently resides in my OrgChart table. Then I write some additional html, with column headings, then do a query with multiple joins for each level of employee to supervisor (I think I go 6 or 7 levels). Then I simply fetch the data, calculate the level number, and insert the data into my OrgChart table. Then I query the OrgChart table, ordering by level, and write a row for each employee in Googles API format. Then I write the rest of the html needed for Google. Then close the file (the html) and redirect to the html file to display. I set this up in the menu to load a new window, so as to leave my existing app open in the browser window it is in.
Here it is, hopefully it makes sense:
global $conn;
($ftemp = fopen("OrgChartDisplay.html", 'w') ) or die("Could not open OC HTML");
fwrite($ftemp, "<html> <head> <center> "); fwrite($ftemp, "

");

fwrite($ftemp, '<img src="images/HPSHeader2.jpg"> ');fwrite($ftemp, "

");

fwrite($ftemp, "<p> <strong>");fwrite($ftemp, "

");

fwrite($ftemp, $_SESSION["ctcompanyname"] );fwrite($ftemp, "

");

fwrite($ftemp, "<p> <strong>");fwrite($ftemp, "

");

fwrite($ftemp, date("m/d/y g:i:s a", time()) );fwrite($ftemp, "

</strong> </center> <p> ");
$sql="delete from OrgChart";

CustomQuery($sql);
fwrite($ftemp, "<script type='text/javascript' src='https://www.google.com/jsapi'></script>; ");fwrite($ftemp, "

");

fwrite($ftemp, "<script type='text/javascript'> ");fwrite($ftemp, "

");

fwrite($ftemp, "google.load('visualization', '1', {packages:['orgchart']}); ");fwrite($ftemp, "

");

fwrite($ftemp, "google.setonloadCallback(drawChart); ");fwrite($ftemp, "

");

fwrite($ftemp, "function drawChart() { ");fwrite($ftemp, "

");

fwrite($ftemp, "var data = new google.visualization.DataTable(); ");fwrite($ftemp, "

");

fwrite($ftemp, "data.addColumn('string', 'Name'); ");fwrite($ftemp, "

");

fwrite($ftemp, "data.addColumn('string', 'Manager'); ");fwrite($ftemp, "

");

fwrite($ftemp, "data.addColumn('string', 'ToolTip'); ");fwrite($ftemp, "

");

fwrite($ftemp, "data.addRows([ ");fwrite($ftemp, "

");
$sql="SELECT EmployeesUsers.eusupviid, EmployeesUsers.euiid, EmployeesUsers.eufullname,

EmployeesUsers.euposition, EmployeesUsers.euactive,

EmployeesUsers1.eusupviid AS eusupviid1, EmployeesUsers1.euiid AS euiid1,

EmployeesUsers1.eufullname AS eufullname1,

EmployeesUsers2.eusupviid AS eusupviid2, EmployeesUsers2.euiid AS euiid2,

EmployeesUsers3.eusupviid AS eusupviid3, EmployeesUsers3.euiid AS euiid3,

EmployeesUsers4.eusupviid AS eusupviid4, EmployeesUsers4.euiid AS euiid4,

EmployeesUsers5.eusupviid AS eusupviid5, EmployeesUsers5.euiid AS euiid5,

EmployeesUsers6.eusupviid AS eusupviid6, EmployeesUsers6.euiid AS euiid6,

EmployeesUsers7.eusupviid AS eusupviid7, EmployeesUsers7.euiid AS euiid7,

PositionMaster.pmposition

FROM EmployeesUsers

INNER JOIN PositionMaster ON EmployeesUsers.eupmiid = PositionMaster.pmiid

INNER JOIN EmployeesUsers AS EmployeesUsers1 ON EmployeesUsers.eusupviid = EmployeesUsers1.euiid

INNER JOIN EmployeesUsers AS EmployeesUsers2 ON EmployeesUsers1.eusupviid = EmployeesUsers2.euiid

INNER JOIN EmployeesUsers AS EmployeesUsers3 ON EmployeesUsers2.eusupviid = EmployeesUsers3.euiid

INNER JOIN EmployeesUsers AS EmployeesUsers4 ON EmployeesUsers3.eusupviid = EmployeesUsers4.euiid

INNER JOIN EmployeesUsers AS EmployeesUsers5 ON EmployeesUsers4.eusupviid = EmployeesUsers5.euiid

INNER JOIN EmployeesUsers AS EmployeesUsers6 ON EmployeesUsers5.eusupviid = EmployeesUsers6.euiid

INNER JOIN EmployeesUsers AS EmployeesUsers7 ON EmployeesUsers6.eusupviid = EmployeesUsers7.euiid

WHERE (EmployeesUsers.euactive = 'Y') AND (EmployeesUsers.eugroup <> 'admin') ";

$rs=CustomQuery($sql);

while ($data=db_fetch_array($rs))

{ $lctr=1;

if ($data["euiid"] <> $data["euiid1"]) $lctr=$lctr+1;

if ($data["euiid1"] <> $data["euiid2"]) $lctr=$lctr+1;

if ($data["euiid2"] <> $data["euiid3"]) $lctr=$lctr+1;

if ($data["euiid3"] <> $data["euiid4"]) $lctr=$lctr+1;

if ($data["euiid4"] <> $data["euiid5"]) $lctr=$lctr+1;

if ($data["euiid5"] <> $data["euiid6"]) $lctr=$lctr+1;

if ($data["euiid6"] <> $data["euiid7"]) $lctr=$lctr+1;

$strSQLInsert = "insert into OrgChart (oclevel, oceuiid, oceusupviid, oceufullname, oceusupvname, oceuposition) values ($lctr, '" . $data["euiid"] . "', '" . $data["eusupviid"] . "', '" . $data["eufullname"] . "', '" . $data["eufullname1"] . "', '" . $data["pmposition"] . "' )";

db_exec($strSQLInsert,$conn);

}
$lctr=0;

$sql="SELECT oclevel, oceuiid, oceusupviid, oceufullname, oceusupvname, oceuposition FROM OrgChart

ORDER BY oclevel, oceusupvname, oceufullname ";

$rs=CustomQuery($sql);

while ($data=db_fetch_array($rs))

{ if ($lctr>0) fwrite($ftemp, ",

");

fwrite($ftemp, "[{v:'");

fwrite($ftemp, $data["oceufullname"]);

fwrite($ftemp, "', f:'");

fwrite($ftemp, $data["oceufullname"]);

fwrite($ftemp, " - ");

fwrite($ftemp, $data["oceuposition"]);

fwrite($ftemp, "'}, '");

fwrite($ftemp, $data["oceusupvname"]);

fwrite($ftemp, "', '");

fwrite($ftemp, $data["oceuposition"]);

fwrite($ftemp, "'] ");

$lctr=$lctr+1;

}

fwrite($ftemp, "

]);

");

fwrite($ftemp, "var chart = new google.visualization.OrgChart(document.getElementById('chart_div')); ");

fwrite($ftemp, "

");

fwrite($ftemp, "chart.draw(data, {allowHtml:true}); } ");

fwrite($ftemp, "

");

fwrite($ftemp, "</script> </head> <body> <div id='chart_div'> </div> </body> </html> ");

fwrite($ftemp, "

");
fclose($ftemp);
//** Redirect to another page ****

header("Location: --rbegin--OrgChartDisplay.html--rend--");

exit();

K
kadidarm 7/6/2012

hi, looks interesting. Do you have a website where we can preview your work?

L
laonian 7/6/2012

Thanks for sharing. What event page is the code implemented?

C
chuckbower author 7/6/2012



hi, looks interesting. Do you have a website where we can preview your work?


Kris,
No, as this is a piece of commercial, live software, I do not. However, you can view a couple of screen shots at this link on our business website:
HPS Screen Shots
Chuck

C
chuckbower author 7/6/2012



Thanks for sharing. What event page is the code implemented?


Alshine,
I implemented this simply in a list page, event List Page - Before Process. Then by throwing it to another window, once the chart is viewed and the window closed, the main menu is still displayed.
Chuck