Forums: TIP-Code and method to create Organizational Chart - Forums

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

TIP-Code and method to create Organizational Chart

#1 User is offline   Chuck Bower 

  • Member
  • PipPip
  • Group: Members
  • Posts: 20
  • Joined: 20-July 10

Posted 04 April 2012 - 02:08 AM

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();
0

#2 User is offline   krisadidarma 

  • Member
  • PipPip
  • Group: Members
  • Posts: 29
  • Joined: 23-August 08

Posted 06 July 2012 - 04:58 AM

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

#3 User is offline   alshine 

  • Advanced Member
  • PipPipPip
  • Group: Members
  • Posts: 96
  • Joined: 10-July 07

Posted 06 July 2012 - 03:00 PM

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

#4 User is offline   Chuck Bower 

  • Member
  • PipPip
  • Group: Members
  • Posts: 20
  • Joined: 20-July 10

Posted 06 July 2012 - 04:21 PM

View Postkrisadidarma, on 06 July 2012 - 12:58 AM, said:

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
0

#5 User is offline   Chuck Bower 

  • Member
  • PipPip
  • Group: Members
  • Posts: 20
  • Joined: 20-July 10

Posted 06 July 2012 - 04:24 PM

View Postalshine, on 06 July 2012 - 11:00 AM, said:

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
0

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic