Sunday, April 13, 2008

Display Gannt Chart in a web page using @Formula





Display schedules in a gannt chart using @formula.

This formula works based on schedule start date, end date and duration values.

These values can be populated using @Dblookup from a view or using lotus script agent. This example uses @DbLookup.



Schedule information input:

- A View with name "Tasks" (you can change the view name in Input_data field formula)

- 1st column Hidden (Schedule_StartDate, Ascending order sorted)

- 2nd column : Task Name ( required)

- 3rd column : Schedule_StartDate (required)

- 4th column : Schedule_EndDate (required)

- 5th column : Duration ( required)

- 6th column : Assigned To (optional)

- 7th column : Document Unique ID (used to display URL href link)



1.Create a form named Gannt Chart or something

2.Create a computed field Input_data or anyname

This field got schedule information input and other reference fields

code:

ViewName:="Tasks";

REM {Input data, add error conditions if required};

FIELD tasknames:=@DbColumn("":"";"":"";ViewName;2);

FIELD taskstartdate:=@DbColumn("":"";"":"";ViewName;3);

FIELD taskenddate:=@DbColumn("":"";"":"";ViewName;4);

FIELD taskduration:=@DbColumn("":"";"":"";ViewName;5);

FIELD resources:=@DbColumn("":"";"":"";ViewName;6);

FIELD docids:=@DbColumn("":"";"":"";ViewName;7);



FIELD DbPath:=@ReplaceSubstring(@Subset(@DbName; -1); " " : "\\"; "+" : "/");



REM {calculate first day of the schedule start date month};

FIELD schedule_startdate:=@ToTime(@Min(taskstartdate));

FIELD schedule_startdate:=@ToTime(@Text(@Month(schedule_startdate))+"/01/"+@Text(@Year(schedule_startdate)));



REM {calculate last day of the schedule end date month};

FIELD schedule_enddate:=@Max(taskenddate);

schedule_enddate:=@ToTime(@Text(@Month(schedule_enddate))+"/01/"+@Text(@Year(schedule_enddate)));

schedule_enddate:=@Adjust(@Adjust(schedule_enddate;0;1;0;0;0;0);0;0;-1;0;0;0);



REM {Total days between firstday and last day of the schedule dates};

FIELD totaldays:=((schedule_enddate-schedule_startdate)/86400)+1;

tmpdate1:=schedule_startdate;



FIELD schedule_months:="";

@While(tmpdate1<= schedule_enddate; tmpdate1:=@Adjust(tmpdate1;0;1;0;0;0;0); @Set("schedule_months";schedule_months:@Text(@Date(@Adjust(tmpdate1;0;0;-1;0;0;0)))) ); schedule_months:=@Trim(schedule_months); FIELD schedule_months:=schedule_months; schedule_months:=@ToTime(schedule_months); schedule_months1:=@Text(@Month(schedule_months))+"/01/"+@Text(@Year(schedule_months)); FIELD fulldays:=@Explode(@ToTime("["+schedule_months1+" - "+@Text(schedule_months)+"]")); REM {Reference fields}; FIELD monthlist:="January":"February":"March":"April":"May":"June":"July":"August":"September":"October":"November":"December"; FIELD weekdays:="S":"M":"T":"W":"T":"F":"S"; ""




3.Create a computed text and enable pass thru HTML code:





schedule_bgcolor:="#FFD39B";

weekend_bgcolor:="#cccccc";



REM {Populate month names};

schedule_months:=@ToTime(schedule_months);

tmp1:="<th colspan="+@Text(@Day(schedule_months))+" align=center>"+@Transform(@Month(schedule_months);"x";monthlist[x])+", "+@Text(@Year(schedule_months))+"</th>";

month_html:="<tr><th rowspan=3 class=\"locked\">Task Name</th>"+@Implode(tmp1)+"</tr>";



REM {Populdate Weekday title};

tmp:=@Date(@ToTime(fulldays));

weekdays1:=@Transform(@Weekday(tmp);"x";weekdays[x]);

weekends:=@Replace(weekdays1;"S";"bgcolor=\""+weekend_bgcolor+"\"");

tmp1:="<th "+weekends+">"+weekdays1+"</th>";

weekday_html:="<tr>"+@Implode(tmp1)+"</tr>";



REM {Populdate days};

tmp:=@Day(@ToTime(fulldays));

tmp2:="<th "+weekends+">"+@Replace(@Text(tmp);"1":"2":"3":"4":"5":"6":"7":"8":"9";"01":"02":"03":"04":"05":"06":"07":"08":"09")+"</th>";

days_html:="<tr>"+@Implode(tmp2)+"</tr>";



REM {Populate Tasks};

html_td:="<td "+weekends+"> </td>";

task_html:=task_html;

@For(n :=1; n<=@Elements(tasknames); n:= n + 1; title:=tasknames[n]; firstday:=((@ToTime(taskstartdate[n])-@ToTime(schedule_startdate))/86400)+1; nodays:=@ToNumber(taskduration[n]); hlinktitle:=@If(@Length(title)>nodays*4;@Left(title;nodays*4)+"..";title);

hlink:="<a href=/"+DbPath+"/0/"+docids[n]+"?opendocument>"+hlinktitle+"</a>";

task_html:= task_html+"<tr><td nowrap class=\"locked\">"+" "+@If(@Length(title)>40;@Right(title;40);title)+"</td>"+@If(firstday>1;@Implode(@Subset(html_td;firstday-1));"")+"<td colspan="+@Text(nodays)+" bgcolor="+schedule_bgcolor+"><center>"+hlink+"</center></td>"+@Implode(@Subset(html_td;(firstday+nodays-1)-totaldays))+"</tr>"

);



"<table cellpadding=0 cellspacing=0>"+month_html+weekday_html+days_html+task_html+"</table>"






4. Adding style to gannt chart (optional)

- this style sheet freezes the column header (month names, days) and Task name.

4.1 Place the gannt chart computed text inside a div tag with id "ViewBody"



<div id="ViewBody">

<computed value>

</div>






4.2. Create a css style sheet page (ie:style.css)



div#ViewBody {

width: 90%;

height: 300px;

overflow: auto;

scrollbar-base-color:#999;

}



div#ViewBody table th {

width: 100px;

position:relative;

top: expression(document.getElementById("ViewBody").scrollTop-1);

z-index: 30;



}



ViewBody th.locked {

font-size: 10px;

color: black;

background-color: lightblue;

border-right: 2px solid silver;

position: relative;

cursor: default;

left: expression(parentNode.parentNode.parentNode.parentNode.scrollLeft-2);

z-index: 40;

}



div#ViewBody td.locked {

font-size: 12px;

color: black;

background-color: #efefef;

border-right: 2px solid silver;

border-bottom: 1px solid silver;

WHITE-SPACE: nowrap;

left: expression(parentNode.parentNode.parentNode.parentNode.scrollLeft-2);

position: relative;

z-index: 10;

}

div#ViewBody td {

font-size: 12px;

color: #c0c0c0;

border-top: #bbbbbb 0px solid; border-right: #bbbbbb 1px solid;

border-bottom: #bbbbbb 0px; border-left: #bbbbbb 1px;

padding: 1px;

}



div#ViewBody th {

font-size: 10px;

color: black;

padding:4px;

text-align: center;

background-color: #efefef;

border-top: #bbbbbb 0px solid; border-right: #bbbbbb 1px solid;

border-bottom: #bbbbbb 1px solid; border-left: #bbbbbb 1px;

}

div#ViewBody a {

font-size: 10px;

TEXT-DECORATION: none

}



4.3. Use this style sheet in Gannt Chart form









GANNT CHART COMPONENT DATABASE DOWNLOAD LINK

Monday, November 12, 2007

Export to Excel on Web

We have seen many ways to export the notes view contents onto an Excel Sheet and this is one of the simplest ways of them. It is a 3 step process as explained below:

Step 1:
Create a hidden view on the database with all the columns included which are required for the Excel Export.

Step 2:
Create a new notes form and key in the information following the syntax below:
Line 1: Type of the query
Line 2: Version
Line 3: URL (http://Server_Name/Database/Excel Export View Name)
Line 4: POST parameters


Optionally, you can include the formatting parameters following these 4 lines as shown in the example below.
Eg:
WEB
1
http://SERVER_NAME/DATABASE/ExportToExcel
Selection=AllTables
Formatting=All
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=True
DisableDateRecognition=True

Save the form as a web query form. (Extension : ".iqy")

For more information on "web query" concept, Click here.

Step 3:
Create a $$ViewTemplate for the export view to preview the view on web. Also, include an action button called "Export to Excel" which has this code beneath:
"Export.iqy?ReadForm"



Preview on web and Excel Export works like a charm!
Screendumps below gives you an idea:

Screen 1: View on web with "Export to Excel" link


Screen 2: On clicking "Export to Excel" link, an excel file opens up with the following message. Click "Open" to proceed.


Screen 3: Excel retrieves data from the database.


Screen 4: Final Report

Tuesday, October 16, 2007

IBM Lotus Domino : Composite Applications


The Lead Manager application in IBM Lotus Notes V8: An overview

Learn how to combine multiple Lotus Notes applications and other technologies to produce a unified interface in which users can conduct their business more effectively and supply innovative business solutions.

Designing composite applications: Component design

This article charts some basic approaches for designing components for composite application development that have maximum reuse. You can use many different strategies to provide the optimum components.

IBM Blog site for Composite applications
Bring information and collaboration into the context of your daily business processes using composite applications
This web log is a joint effort by the key technical architects and user experience professionals to open a direct line of communication with developers about the capabilities of user facing composite applications. The primary focus will be on Lotus Notes/Domino, WebSphere Portal, Lotus Expeditor, and future Lotus products to be named later. We will also be commenting on composite application trends that we see in the broader market. It's important to note that none of us speak officially for IBM, nor can we make product commitments or discuss products that have not been announced.

Two simple tutorials to build NSF based apps with NSF and Eclipse components
Build NSF based composite application Tutorial 1 (PDF)
The first tutorial shows a very simple NSF based composite application with one NSF component and two component views that communicate via property broker between each other.


Build NSF based composite application Tutorial 2 (PDF)
The second tutorial adds another component to the same application. The tutorial shows how this Eclipse component can be built with the Eclipse IDE.