Author Topic: How to Add Gantt Charts (JSGantt) to Feng Office  (Read 250427 times)

allenlook

  • Volunteer Moderator
  • Sr. Member
  • ****
  • Posts: 312
    • MSN Messenger - sii_lookal@hotmail.com
    • View Profile
    • SI Group, Inc.
How to Add Gantt Charts (JSGantt) to Feng Office
« on: February 25, 2010, 01:40:43 pm »
NOTE:  If you're just coming here to explore adding Gantt charts to your Feng Office installation, you only need to read the first two posts in this thread to install the latest code, with the latest instructions.  All the other pages of posts are discussion about improvements and bugs that are then fixed ASAP and posted back to these first two posts...  There may be some idiosyncrasies between MySQL or PHP installations that can't be resolved however, and the additional pages of posts might help you in that regard.

Let's begin...

As you know, we need a way to make Gantt charts from Feng Office, and we looked for several weeks at different avenues.  Google Visualizations, FlashCharts, java scripts, etc.  All had severe limitations - not printable, not feature-rich enough, required non open-source or non-free software, etc.

We finally settled on integrating the free jsGantt javascript into Feng Office.  It requires the creation of a few views in MySQL which we will provide, and that is about it.  The jsGantt feature does not have to be loaded into a tab in Feng Office if you do not wish to do so, but we opted for it as it gave a more "one-stop-shop" feeling for our users.

We have made what we consider minor improvements to jsGantt, but they are not material to its function, and it will work just fine in its base form if you download it from jsGantt.com.  The version referenced here is 1.2, and it looks like this:



There is a data-selection form which appears on the Feng Office tab and which allows you to select one or more workspaces, and/or one or more users to determine what will be charted.  You may also optionally specify a start and end date for the tasks that will be included on the chart, whether to inherit colors from the workspaces, and whether or not to display percent complete, duration, start/end date, and resource for each task.

Tasks in personal workspaces are never included.

Clicking on a task or milestone that is assigned to someone will also open a new e-mail address to that person, and with the task name as the subject.

Step one, if you decide you want to integrate the Gantt chart onto a tab, will be to follow the steps oulined in the "Hello World" posts in this forum and elsewhere, to give the Gantt chart a place to live within Feng Office. Otherwise, you may just as easily reference the Gantt chart as part of the same or even a separate website from Feng Office, it only needs access to your Feng Office database.

I've outlined the steps I took to integrate the tab in a message later in this thread, which you can access here - http://forums.fengoffice.com/index.php?topic=3826.msg14312#msg14312.
« Last Edit: April 30, 2010, 02:21:56 pm by allenlook »
I am a volunteer moderator.  Any statements, opinions or observations I contribute are solely mine and are not necessarily shared by the makers of Feng Office.

allenlook

  • Volunteer Moderator
  • Sr. Member
  • ****
  • Posts: 312
    • MSN Messenger - sii_lookal@hotmail.com
    • View Profile
    • SI Group, Inc.
Re: How to Add JSGantt to Feng Office
« Reply #1 on: February 25, 2010, 01:41:34 pm »
The next step would be to download the source for jsGantt from jsGantt.com if you wish, but I have also included a full copy of our source code for your reference here and I recommend that you use it.  We have made just a few style tweaks that you might find useful, such as being able to hide the data format selector in print preview mode, and we have added the ability to suppress blank windows and to avoid opening unassigned tasks.  The choice is ultimately yours.

As our copy of jsGantt is integrated with Feng Office, we opted to place the code for it in /fengoffice/application/views/lab, because the name of the tab it lives on right now is "Labs" (we're in beta).  I am including all the files in a zip attachment, called feng_gantt_xx.x.zip, which you will be able to see below if you are logged in to the forum.  There are only five files, including the SQL statements needed to set up the database.  If you want to include the Gantt chart on its own tab as we did, the instructions are included later in this thread (on page 5) and you can jump there by clicking here.

Once you decide where to put your files, you will need to open the index.php file and type in your settings for your MySQL database host, database name, user name and password.  Search for the string GIMMEGANTT to jump right to that section of the file.  You have two options - either you can put your settings right into the index.php file, or you can refer to an include file one level above called "chartglobal.php".  Instructions are in the index.php file.  If you didn't use the default table prefix "og_" when you installed Feng Office, now would be the time to search and replace "og_" here in the index.php file with the prefix you actually used.

OK, that should be it for file editing.  Now let's set up the database to work correctly - you have to create some views, tables, procedures and functions.  It sounds like a lot, but there is a SQL program to do it all for you.

The views will select the workspaces, the milestones, the tasks and subtasks, then union them all together into one view - your web page only has to deal with that one view, the others are "behind the scenes workhorses".   Everything will be alphabetized and sorted correctly by alphabetical, then start/end date order as appropriate.

If you are upgrading from an earlier version of feng_gantt, you may wish to drop all the related objects first, as they may be orphaned by 10.0.  There are several objects we no longer use.  All feng_gantt objects start with "my_", so if you do not have any other applications co-mingled with Feng Office, you may be able to safely drop all objects beginning with "my_" - as always, take a backup and use caution if you are not familiar with MySQL.

In your favorite MySQL manager, execute the SQL statements in the file feng_gantt_xx.x.sql (which was included with your html/php files, and which has the version number in place of the xx.x).  That will create the necessary views and functions, all of which are prefixed with "my_" to keep them separate from your "og_" objects.  The SQL statements assume that your database name is "fengoffice_dev", but if it is not, you should do a global search and replace of "fengoffice_dev" with your actual database name.

These functions and views do NOT write anything to your database, they only read from it, so you don't have to worry about them messing up your Feng Office installation.  In fact, if you preferred to install them into their own separate database you could do that as well, as long as you remember to reference your actual Feng Office database in the SQL statements.

Once those views are created and working, your web page should be able to execute.  Bring up the index.php file in your browser of choice (Firefox and IE have been tested - FF is much faster for some reason and does better layout), choose the options you want on your form, and then click 'Submit' to view your Gantt chart.

You will have to option to view the chart in a printer-friendly window (without the form showing) and you can also create a new second (or third) window so you can compare more than one Gantt chart side by side.

NOTE: AS UPDATES AND IMPROVEMENTS ARE MADE, THE FILE BELOW WILL ALWAYS BE UPDATED TO REFLECT THE LATEST VERSION SO YOU DO NOT HAVE TO READ THE ENTIRE THREAD.  USE AT YOUR OWN RISK - I ASSUME NO RESPONSIBILITY FOR YOUR HARDWARE, SOFTWARE OR DATA.

« Last Edit: June 04, 2010, 08:51:50 am by allenlook »
I am a volunteer moderator.  Any statements, opinions or observations I contribute are solely mine and are not necessarily shared by the makers of Feng Office.

allenlook

  • Volunteer Moderator
  • Sr. Member
  • ****
  • Posts: 312
    • MSN Messenger - sii_lookal@hotmail.com
    • View Profile
    • SI Group, Inc.
Re: How to Add JSGantt to Feng Office
« Reply #2 on: February 25, 2010, 01:42:21 pm »
Did you run it all and nothing happened?  Here are some troubleshooting tips:

1.  If you ran everything but the "Labs" tab is blank, you have a problem with the integration of the tab into Feng Office, and not with the gantt functionality most likely.  You can try surfing to the gantt page separately (remember it doesn't have to be on a tab in Feng Office, it works just fine standalone) by typing your fengoffice installation's address into your server followed by /application/views/lab/index.php, so it might look like http://fengoffice.ourcompany.com/application/views/lab/index.php.  If the page works when accessed directly, then you know there's something awry with your tab integration code.

2.  If the selection form comes up but is blank, or only fills in partially, then you have a MySQL security error most likely, or you didn't rename the tables correctly during the installation.  Also remember that Personal workspaces will not show up - it's our attempt at "security".  You can use the "View Source" feature of your browser to look at the actual HTML source code of the page, and look way down at the bottom.  Chances are, the MySQL error is posted there.  Remember that the tab is encapsulated in an <iframe>, and you may have to use Firefox's "This Frame" / "View Frame Source" to see the actual feng_gantt code.

3.  If you made valid selections and no chart appears at the bottom, there's most likely trouble with the data.  Any issues with the data (invalid parentage between tasks, incorrect root, etc.) will cause the gantt chart to not display.  This isn't common however, and we can usually help in this thread.

4.  If you got funny characters in your data, or some of the fields that should contain accents and other special characters are blank, your encoding is incorrect for your data.  Try changing the "Char. Set:" option on the selection form to see if you can improve the data's display.

I think that is everything.  I will keep this message as a placeholder to answer additional questions.

There are a few remaining problems or things I would like to polish.

1.  The ability to print preview better would be awesome.  As this uses many nested DIVs, Firefox especially has trouble with it.  For now I have it working so you can get as many pages vertically as you need, but horizontally you are limited to one. UPDATE: You can print any size chart to PDF with a PDF printer driver such as CutePDF or PDFCreator, and then manipulate it with PDF programs.  Not perfect, but it is the only way to work around the bug in FF/IE.
2. I would like to merge the CSS styles to more closely match Feng Office, but I am not a CSS guru. Done.
3.  I would like to get % Complete working. UPDATE: % Complete is now working to the extent that Feng Office computes % Complete as the number of total tasks/sub-tasks completed, and not really as the amount of time/total time completed.
4.  I would like to get Dependents working.
5.  JSGantt doesn't do layout very nicely in Firefox, the start date and end date get hidden if you have really long task names.  I may need the JSGantt guys to fix that.  UPDATE:  I have added the ability to truncate project and task names at a certain number of characters, and this helps the layout tremendously, while still giving enough of the label to be helpful.  50 seems to be a good number.
6.  Have the Gantt chart reflect what workspace is chosen in Feng Office instead of needing a separate selector.  I have decided against this because I'm quite often printing two or more separate workspaces and Feng Office of course only allows you to select one at a time.
« Last Edit: April 30, 2010, 04:55:25 pm by allenlook »
I am a volunteer moderator.  Any statements, opinions or observations I contribute are solely mine and are not necessarily shared by the makers of Feng Office.

fernandog

  • Jr. Member
  • **
  • Posts: 66
    • View Profile
Re: How to Add Gantt Charts (JSGantt) to Feng Office
« Reply #3 on: February 25, 2010, 06:44:35 pm »
When i try to run the first statement from the sql file, i get a syntax error
Anyone tried this?

allenlook

  • Volunteer Moderator
  • Sr. Member
  • ****
  • Posts: 312
    • MSN Messenger - sii_lookal@hotmail.com
    • View Profile
    • SI Group, Inc.
Re: How to Add Gantt Charts (JSGantt) to Feng Office
« Reply #4 on: February 25, 2010, 07:31:57 pm »
My apologies, MySQL snuck a couple of "utf" codes on the end of the SQL statement, simply delete those last two words on the end of the SQL, and I will update the SQL file now.

EDIT: file is now updated, and I tested that the SQL statement runs fine.
« Last Edit: February 25, 2010, 07:39:13 pm by allenlook »
I am a volunteer moderator.  Any statements, opinions or observations I contribute are solely mine and are not necessarily shared by the makers of Feng Office.

allenlook

  • Volunteer Moderator
  • Sr. Member
  • ****
  • Posts: 312
    • MSN Messenger - sii_lookal@hotmail.com
    • View Profile
    • SI Group, Inc.
Re: How to Add Gantt Charts (JSGantt) to Feng Office
« Reply #5 on: February 25, 2010, 10:30:51 pm »
I believe I've made some improvements to the file.  It now gives 10 rows for selection for those of you with many workspaces, and the workspaces are no longer in alphabetical order, but rather are indented in WBS order.  I will work on re-alphabetizing them by level tomorrow.

NOTE: Again, I wish I knew how Feng Office does it - they obviously do the same thing I need for the workspace tree on the left, but there's no documentation that I'm aware of?

In addition, I believe indenting is preserved even if you choose workspaces far down the tree, which may have been problematic for some.

I will post the file into the thread above as all changes are reflected there.
« Last Edit: February 26, 2010, 02:41:35 pm by allenlook »
I am a volunteer moderator.  Any statements, opinions or observations I contribute are solely mine and are not necessarily shared by the makers of Feng Office.

Pet

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 638
  • Always mining for solutions!
    • View Profile
    • The Bet!
Re: How to Add Gantt Charts (JSGantt) to Feng Office
« Reply #6 on: February 25, 2010, 11:10:48 pm »
I don't like Gantts myself, but really well done, congrats on doing good things for the community allenlook!
Support OpenGoo - Sponsor a Feature! | Follow me on Twitter | OG Support Chat | Did you turn debugging on?

Henry

  • Full Member
  • ***
  • Posts: 159
    • View Profile
Re: How to Add Gantt Charts (JSGantt) to Feng Office
« Reply #7 on: February 26, 2010, 04:54:39 am »
hello allenlook,

If i execute this sql-code, i see this error.

CREATE VIEW `my_taskparents` ...

#1305 - FUNCTION fengoffice.my_GetMileStone does not exist

Thanks for your work.

Henry

allenlook

  • Volunteer Moderator
  • Sr. Member
  • ****
  • Posts: 312
    • MSN Messenger - sii_lookal@hotmail.com
    • View Profile
    • SI Group, Inc.
Re: How to Add Gantt Charts (JSGantt) to Feng Office
« Reply #8 on: February 26, 2010, 11:12:09 am »
My apologies (again) - there are actually two functions that must be declared for the views to work.  If I knew how to write a MySQL script I would just package it all up and you could click it once instead of all these different posts - but again I will update the original post so the files are there for people reading the thread anew.

UPDATE: Original post is now updated as "feng_gantt_2.0."

Here is the SQL for the two functions.

=================================

DELIMITER $$

DROP FUNCTION IF EXISTS `fengoffice`.`my_GetMileStone`$$

CREATE DEFINER=`root`@`localhost` FUNCTION `my_GetMileStone`(s CHAR(20)) RETURNS char(50) CHARSET latin1
RETURN (select pWBS from fengoffice.my_milestones where pID=CONCAT(5555555,s))$$

DELIMITER ;

================================================

DELIMITER $$

DROP FUNCTION IF EXISTS `fengoffice`.`my_GetWorkSpace`$$

CREATE DEFINER=`root`@`localhost` FUNCTION `my_GetWorkSpace`(s CHAR(20)) RETURNS char(50) CHARSET latin1
RETURN (select pWBS from fengoffice.my_workspaces where pID=s)$$

DELIMITER ;
« Last Edit: February 26, 2010, 11:22:09 am by allenlook »
I am a volunteer moderator.  Any statements, opinions or observations I contribute are solely mine and are not necessarily shared by the makers of Feng Office.

fernandog

  • Jr. Member
  • **
  • Posts: 66
    • View Profile
Re: How to Add Gantt Charts (JSGantt) to Feng Office
« Reply #9 on: February 26, 2010, 12:04:37 pm »
Hi allen,

Thank you for all your work.

I successfully ran all the commands in the sql file, but my webpage doesnt seem to be working stil. For one, i dont see a submit button, and i dont see the workspaces either.

Has anyone else been able to check this out?

allenlook

  • Volunteer Moderator
  • Sr. Member
  • ****
  • Posts: 312
    • MSN Messenger - sii_lookal@hotmail.com
    • View Profile
    • SI Group, Inc.
Re: How to Add Gantt Charts (JSGantt) to Feng Office
« Reply #10 on: February 26, 2010, 12:27:39 pm »
Did you enter your MySQL details in the index.php file?
I am a volunteer moderator.  Any statements, opinions or observations I contribute are solely mine and are not necessarily shared by the makers of Feng Office.

fernandog

  • Jr. Member
  • **
  • Posts: 66
    • View Profile
Re: How to Add Gantt Charts (JSGantt) to Feng Office
« Reply #11 on: February 26, 2010, 01:10:48 pm »
I did.

I am using opengoo 1.5.2, could that have anything to do with it?

attached the screenshot of what I get

allenlook

  • Volunteer Moderator
  • Sr. Member
  • ****
  • Posts: 312
    • MSN Messenger - sii_lookal@hotmail.com
    • View Profile
    • SI Group, Inc.
Re: How to Add Gantt Charts (JSGantt) to Feng Office
« Reply #12 on: February 26, 2010, 02:10:48 pm »
Perhaps you have no resources in your system and the code is dying there?
I am a volunteer moderator.  Any statements, opinions or observations I contribute are solely mine and are not necessarily shared by the makers of Feng Office.

allenlook

  • Volunteer Moderator
  • Sr. Member
  • ****
  • Posts: 312
    • MSN Messenger - sii_lookal@hotmail.com
    • View Profile
    • SI Group, Inc.
Re: How to Add Gantt Charts (JSGantt) to Feng Office
« Reply #13 on: February 26, 2010, 02:43:11 pm »
Additional improvements - to be incorporated above.

This time, we are making sure all workspaces are alphabetized within their WBS level, and they are indented properly for easy identification.

This requires one more function, a change to my_workspaces (view), and a tweak to index.php, all of which have been included in the original files above.
« Last Edit: February 26, 2010, 06:27:26 pm by allenlook »
I am a volunteer moderator.  Any statements, opinions or observations I contribute are solely mine and are not necessarily shared by the makers of Feng Office.

allenlook

  • Volunteer Moderator
  • Sr. Member
  • ****
  • Posts: 312
    • MSN Messenger - sii_lookal@hotmail.com
    • View Profile
    • SI Group, Inc.
Re: How to Add Gantt Charts (JSGantt) to Feng Office
« Reply #14 on: February 26, 2010, 04:43:47 pm »
Additional improvements - the data selection form is now "sticky", in that values you select in the multi-select drop-downs or that you type into the data fields are kept through form submissions.

The dates are also now validated and must be valid dates or they will be reset to NULL.

Original posted file has been consolidated to one file, instructions have been updated and original post has been updated accordingly.
I am a volunteer moderator.  Any statements, opinions or observations I contribute are solely mine and are not necessarily shared by the makers of Feng Office.