Implement a Project Plan and manage activities with Google Spreadsheets

In the past days I added some post about the topic project management: organize a project plan and implement a project plan with Excel. I added also an Excel template that you can download here. In this post I'm going to explain how to implement a project plan using Google Spreadsheets. You can see the example at this link.

Copy and use this spreadsheet

Create a copy of this spreadsheet for your Google Apps account


File structure
My Google Spreadsheets file has two woorksheet WBS (work breakdown structure of the project) and User View (a list with resources to assign to each task.)


Work breakdown structure
In WBS sheet you have the following columns: WBS (task ID), Task (task description), Pred (predecessor), % (percentage of completion), Owner (resource assiged to the task), Role (owner role);


Owner role is calculated looking for the name insert into the column Owner into the sheet User View using VLOOKUP function (vertical lookup), for example:

=vlookup(E5,'user view'!A:B,2,)

...it search the value contained into the cell E5 on the sheet user view in a colum's interval from A to B, and return - if the value in E5 exist in the column A - the result from the column with index 2 (column B).

Sheet WBS has also other columns like star date, finish date, re-planned star date, re-planned finish date, project delay, and a section to assign a variable cost + fixed cost + a manual adjust to each task.

Take a look at the spreadsheet for more info about all columns.


Resources
All resources (task's owner) are located into a second worksheet User view. I have added some basic columns and the hour cost for the resource.


Total Task is calculated automatically from the sheet. I have simply added a function, COUNTIF, that count how time an user si repeted into the sheet WBS

=countif(wbs!E:E,A3)

... it updates the counter if finds in the column E of the sheet WBS a value equal to the value contained into the cell A3.


Excel or Google Spreadsheet?
Sincerly, I found Google Spreedsheets a good application to create spreadsheets with a medium level of complexity, but the "response", for an Excel-addicted, is not the same. In any case it's a useful service and, above all, it's free!

Coldfusion VS PHP part 1

I love Coldfusion. I think, it is the more simple and powerful language to develop web application, but in some cases, expecially when you have to develop a "dynamic" site - let me pass this term... - for a small client, it can't be the better solutions in terms of cost if compared with PHP. This is a first post that evidences some basic differences between two programming languages such as variables and queries for all programmers that, for necessity, have to migrate from Coldfusion to PHP.

Variables
Coldfusion variables are set with <cfset> tag. In PHP, a variable is set inside a <?php ...?> block of code with a "$" char before the var name. For example:

Coldfusion: <cfset myVar = 0>
PHP: <?php $myVar = 0?>

Differences between URL variables:

Coldfusion: #URL.myVar#
PHP: $_GET['myVar'];

Differences between POST variables:

Coldfusion: #POST.myVar#
PHP: $_POST['myVar'];

Execute a query
Coldfusion queries are "beautiful" :) and very simple to define with <cfquery> tag. You have only to specify a datasource name (database you use in your application) and a query name:

<cfquery datasource="myDatasource" name="getUser">
SELECT * FROM USER
</cfquery>

If you use PHP, when you add a query, you have to include in your page all the parameters to connect to database. I suggest to read this post to have more info about this topic. A tipical query in PHP is structured in this way:

<?php
// Connection's Parameters
$db_host="localhost";
$db_name="database_name";
$username="database_username";
$password="database_password";
$db_con=mysql_connect($db_host,$username,$password);
$connection_string=mysql_select_db($db_name);
// Connection
mysql_connect($db_host,$username,$password);
mysql_select_db($db_name);

// Query
$sql = 'SELECT * FROM USER';
$getUser = mysql_query($sql);
?>

How you can see, I defined a variable $sql (with the query SQL code). mysql_query($sql) execute the query.

Query results
After the execution of a query you would show the query results. With coldfusion you ca use <cfouptut> tag and add the "query" parameter to specify what query's result you want to display.

<cfoutput query="getUser">
#name#, #email#, #city#
</cfoutput>

You can also use this dotted code if inside a <cfoutput> code you want to display results from different queries:

<cfoutput>
#getUser.name#, #getUser.email#, #getCity.city#
</cfoutput>

With PHP you have use mysql_fetch_array() method inside a while statement:

<?php
while ($row = mysql_fetch_array($getUser)){
echo $row['name'] . ',' ;
echo $row['email'] . ',' ;
echo $row['city'] . ',' ;
}
?>

I hope this post about this topic can be useful to all PHP/Coldfusion beginner developers. I will add other infos in the next posts.

Project Management: a project plan with Excel (template)

In the previous lesson I explained some general rules about how to organize a project plan structure and implement it with an excel worksheet to manage your projects. I implemented the Excel worksheet that contains two sheets: WBS and HR. WBS is the work breakdown structure of our project and HR contains data about the resources assigned to each task.

Download Project Plan Template (excel)


WBS Sheet: Activity-Task status
Columns A:F contains general information about Activities and Tasks:

Before assigning a resource to a task, you have to add all information about the task's owner into the sheet HR.

HR: Define Project Resources
In this sheet you can add resources and assign them to your project's tasks:


Task Assigned counts the number of task assigned to each resources. It is calculated using COUNTIF() Excel function. This is an example of use of this function:

=COUNTIF(WBS.D:D;B5)

... in other words: "update the counter only if, in the interval of reference, WBS.D:D (column D of the WBS worksheet), find a value qual to the cell's value B5 in the sheet HR".


WBS Sheet: Project Dates
Columns G:I contain informations about activity start-date/finish-date/total days. For each task, you have only add a value for Start Date and Finish Date. Days will be calculated automatically such as difference between columns H - columns G.


WBS Sheet: Re-planned Date
Columns K:Q, contains info about re-planned dates and the project delay.
If you want to re-plan start-date or finish-date of an activity, you have check the column K with an "X" into the cell relative to the task you want to re-plan.


So, you set SD Delay (start day delay - in terms of days) and/or FD Delay (finish day delay - in terms of days) - Column L:M. Re-Start Date, Re Finish Date, Re-Days (replanned days) and Project Delay will be calculated from the sheet.

WBS Sheet: Costs Control
Columns S:X contains informations about cost management. You can specify hours and hour cost for each resource assiged to a task.

You can also add Fixed Cost and an Adjust (flat) to simulate cost's trend.

Download Project Plan Template (excel)

More updates about this topic coming soon :)

Project Management: organize a project plan

This is a first lesson about how to organize a project plan structure to manage a project using Microsoft Excel.

Define the Work Breakdown Structure
What is it a Work Breakdown Structure? We can say it is a hierarchical tree structure useful to organize all necessary task to complete a project. In this example I chose a simplified structure wih only two level (Activity - Task):


An activity is composed from many tasks and every task has some informations about its status, for example: percentage of completion, owner, start date, finish date...
I suggest you not to exaggerate with the levels' structure. A three level plan such as the following is the in many situations:

Level 1. Macro Activity
Level 2. Activity
Level 3. Task

Calculate Start Date, Finish Date and POC of an activity
Let's look an activiy is an group of tasks therefore, all informations about its status depend from the single tasks that compose it.

Start Date of an activity is calculated such as the minimum value of all start date of tasks that compose it.

Start Date Activity = SDAi = min(SDTi)

...where SDTi are the start date of tasks that compose the activity i.

In the same way, Finish Date of an activity is calculated such as the maximum value of all start date of tasks that compose it.

Finish Date Activity = FDAi = max(FDTi)

...where SDTi are the finish date of tasks that compose the activity i.


POC, percentage of completion (%), is calculated such as the average value of all percentage:

POC = POCAi = AVERAGE(POCTi)

...where POCTi are the percentages of completion of tasks that compose the activity i.


Adding a control for replanned task
It can be useful to mantain a trace of original dates, in case you want to re-plan start date or finish date for one or more tasks . In this case, you can add the following columns on your file: Replanned Start Date, Replanned Finish date, Replanned Days (total), Delay (days):


Delay is calculated like the difference of:

Delay = (Replanned Start Date - Start Date) + (Replanned Finish Date - Finish Date)

In the next lesson I will explain how to add a cost control on this plan and how to implement it with Excel.

Simple 5 stars rating script using AJAX and Coldfusion

This lesson explains how to implement a simple 5 stars rating script using Ajax and Coldfusion. The following picture shows the tutorial structure that we will use to realize this script:

Step 1: Define graphic elements (CSS)

save this image and save it into the pic folder of your website.

Now, create a new css file (default.css) into css folder of your website ad copy and paste this code to create two layer div.star_y (star on), div.star_n(star off):

div.star_y{width:10px; height:10px; background:url(../pic/vote_star.gif); float:left;}
div.star_n{width:10px; height:10px; background:url(../pic/vote_star.gif) 0 10px; float:left;}

Step 2: Define graphic elements: (HTML)
Image you want to use this 5 stars rate script to rate posts in your blog. You have a query that return all posts' titles with the current rate (post rate) and show one or more stars "on" in this way:

5 stars rating script using Ajax
post rate 3/5

First, add a link to the css file adding this line of code into <head> page's tag:

<link href="css/default.css" rel="stylesheet" type="text/css" />

... so, copy and paste this code into index.cfm:

//include ajax_framework.js
<script language="javascript" src="ajax_framework.js">
<cfquery datasource="mydatasource"&gt name="getPosts">
SELECT id_post_pk, post_rate, title
FROM MYPOST
</cfquery>
<cfoutput&gt query="getPosts">
#title# <br/>
<cfloop from="1" to="5" index="i"&gt;
<div class="star_<cfif post_rate GE i>y<cfelse>n</cfif>" id="#id_post_pk#_#i#" onmouseover = "javascript:overstar(#id_post_pk#,#i#)" onmouseout = "javascript:outstar(#id_post_pk#,#post_rate#)">
// use a spacer image into the layer
<a href="javascript:ratestar(#id_post_pk#,#i#)">
<img src="pic/spacer.gif"/>
</a><br/>
</cfloop>
post rate: <strong>
<span id="rate_#id_post_pk#">#post_rate#</span>/5
<strong>
</cfoutput>

Step 3: Javascript Functions
Create a new javascript file ajax_framework.js and add the following code.
This is the code of javascript functions overstar() and outstar():

/* star "on" on mouse over */
function overstar(idElement,rate){
for(i=1; i<=5; i++){
if(i <= rate){
document.getElementById(idElement+'_'+i).style.backgroundPositionY = "20px";
} else {
document.getElementById(idElement+'_'+i).style.backgroundPositionY = "10px";
}
}
}
/* star "off" on mouse out */
function outstar(idElement,rate){
if(outstar_check == 0){
rate = rate;
} else {
rate = rateN;
}
for(i=1; i<=5; i++){
if(i<=rate){
document.getElementById(idElement+'_'+i).style.backgroundPositionY = "30px";
} else {
document.getElementById(idElement+'_'+i).style.backgroundPositionY = "10px";
}
}
}

... and this is the code to use AJAX functionality to save vote and calculate the new rate:

var idElementN=0;
var rateN=0;
var nocache=0;
var outstar_check=0;

function rateStar(idElement,rate){ // idElement is the id_post_pk
idElementN=idElement;
rateN=rate;
outstar_check=1;
nocache=Math.random();
http.open('get', 'ratePost.cfm?idElement='+idElement+'&rate='+rateN+ '&nocache='+nocache);
http.onreadystatechange = ratestarReply;
http.send(null);
}
function rateStarReply() {
if(http.readyState == 4){
var response = http.responseText;
document.getElementById('rate'+idElementN).innerHTML = response;
} }

Step 4: Coldfusion code: ratePost.cfm
rateStar() function send data to ratePost.cfm page to calculate the new rate after a vote. So create this page and copy and paste this code:

<cfset element = #URL.idElement#>
<cfset rate = #URL.rate#>
// get total votes and current rate
<cfquery datasource="mydatasource"&gt name="getTotalVotes">
SELECT total_votes, total_value, post_rate
FROM MYPOST
WHERE id_post_pk = #element#
</cfquery>
// calculate new rate
<cfset newRate = (#getTotalVotes.total_value# + #rate#)/(#getTotalVotes.total_votes# + 1)>
// update rate
<cfquery datasource="mydatasource"&gt name="updateRate">
UPDATE MYPOST
SET post_rate = #newRate#,
total_value= (#getTotalVotes.total_value#+#rate#),
total_votes= (#getTotalVotes.total_votes# + 1)
WHERE id_post_pk = #element#
</cfquery>
// return new rate

<cfoutput>#newRate#</cfoutput>

Save all and try it!

Remove HTML tags from input field using Coldfusion and ReReplace() function

If you develop web applications can be useful take a mind some little sagacity to avoid ugly surprises, for example that an user can add and save javascript code into a form that executes undesired actions.

A typical situation
Image you have a form with an input field where a registered user can add/modify his name:

<cfif isDefined('FORM.name')>
<cfquery datasource="myDatasource">
INSERT INTO USER (NAME) values (#FORM.name#)WHERE ID_USER_PK = 1
</cfquery>
</cfif><form action="thisPage.cfm"><input name="name"/> <input type="button" value="save" name="submit"/></form>

... an user in vein of jokes could add and save into the database this code:

<script language="javascript">
alert('Site's webmaster is an idiot!');
</script>

... or a loop like this:

<script language="javascript">
for(i=1; i<2000000; i++){ alert('Site's webmaster is an idiot!');} </script>

So, when an user open the page instead of show the name, the browser executes a javascript code that could cause a little embarrassment for the site's webmaster :)
These are just two banal examples but you can find a lot of situations where is very important to do a check of the input data and remove all HTML tags using this simple Coldfusion function:

ReReplaceNoCase(#inputString#,"<[^>]*>","","ALL")

...where #inputString# is the string you want to clear, and "ALL" repeat the same action for all occurrences. You can specify also what tags will be removed, for example if you want to delete only the content inside <script> tag (because you want mantain some no-dangerous HTML tag like <b>, <strong>, <em>), you have to modify the previous code with the following:

ReReplaceNoCase(#inputString#,"<script>.*</script>", "", "ALL")

Remove HTML tags from the previous example
To solve the problem in the previous example, you have to add just a line of code (in bold) inside your page:

<cfif isDefined('FORM.name')>
// remove all html tags in the input string in this case a FORM variable
<cfset nameNOHTML = ReReplaceNoCase(#FORM.name#,"<[^>]*>","","ALL") />
<cfquery datasource="myDatasource">
INSERT INTO USER (NAME) values (#nameNOHTML#)
WHERE ID_USER_PK = 1
</cfquery>
</cfif>
<form action="thisPage.cfm">
<input name="name"/> <input type="button" value="save" name="submit"/>
</form>

In this way all HMTL tags will be removed from the input data.

Top resources for webmaster

This is a small list of my preferred resources (database, functions, classes, libraries) that I use to deploy web sites. If you have an interesting site about this argument add it with a comment into this list.


Ajax loader
Download this pack with a lot of ajax animated loader like these:



I suggest also http://www.ajaxload.info to create your custom ajax loader on-line

Common Function Library Project (Coldfusion)
The most complete source where you can find a lot of open source user-defined functions to be used in your Coldfusion applications and simplify your work.
http://www.cflib.org/library.cfm


Firebugs
I use it to debug Javascript with Firefox
http://www.getfirebug.com


Adobe Kuler
Create, explore and share color themes for your web site.
http://kuler.adobe.com/


MAMP (mac users)
Install Apache, PHP and MySQL with few clicks under Mac OS X.
http://www.mamp.info


MySQL
No other explenations for the most popular open source database!
http://www.mysql.com


Mootools
A simply to use Object-Oriented Javascript framework compatible with all browser
http://mootools.net


PHP Classes Repository
A repository full of freely available programming classes of objects written in PHP
http://www.phpclasses.org


phpMyAdmin
The most powerful and free MySQL Database administration tool, written in PHP.
http://www.phpmyadmin.net

Statcounter
A free yet reliable invisible web tracker, highly configurable hit counter and real-time detailed web stats.
http://www.statcounter.com

Scriptaculous
The most famous web 2.0 style Javascript libraries.
http://script.aculo.us/