ASE Home Page Products Download Purchase Support About ASE
ChartDirector Support
 Forum HomeForum Home   SearchSearch

Message ListMessage List     Post MessagePost Message

  Multi-layer gantt error passing data
Posted by Randy on Mar-26-2014 08:40
| Reply | Monitor
I am using CD ver 5.1 for ColdFusion. I am following the help documentation to pass the results of query
columns to two individual arrays for startdate and enddate. As long as my data source does not have a
null value in a field, everything works great. However, when I have a null field I get the following error:

Tried to call "addBoxLayer(double[], double[], int, java.lang.String)", but cannot convert argument 2 to the
desired type.?

So I created a query of queries that adds a column (enddate modified) with the following logic: if the
enddate is null, add 30days to the startdate, else keep the enddate. The column is set as date (I've tried
every variant of date types). When I pass the new column, which now has no nulls, I still get the error
above.

If I go back to the source data and put a date in the empty fields so that every record has a date, no
problems, no errors. So my question is when I use the dateadd("d", 30, startdate)  to alleviate nulls in
enddate, what is changing in the array structure that  issues the error above?

The conversion of the query columns to an array is coded like this:

<cfset chartstartdates = queryname["columnname"].toarray()]>
and similiar for chartenddates

which as I stated works fine as long as there is no null in any record of the column or I haven't tried to
handle the null with a claculation. Any help is greatly appreciated.

TIA,
Randy

  Re: Multi-layer gantt error passing data
Posted by Randy on Mar-26-2014 22:48
| Reply | Monitor
Peter,
So after some research in the forum I have added the following to my code prior to CD starting:

<!---Code added to manipulate data type --->

<cfscript>
for(i = 1; i LE ArrayLen(vRequiredResponseDate); i = i + 1)
     vRequiredResponseDate[i] = LSParseNumber(vRequiredResponseDate[i]);
</cfscript>

And I get the error:
{ts'2013-01-0900:00:00'} must be interpretable as a valid number in the current locale.

I have tried every function I know to format the date: CreateDate, DateFormat, ParseDateTime, etc.

I am sure this is very simple but I am at a complete loss. A kick in the right direction would be appreciated.

Here is my full code in its current iteration:

<cfset twelvemonthsAgo = DateAdd("m",-12,Now())>

<cfquery datasource="QA" dbtype="ODBC" name="CARAccessforGraph">
SELECT CAR_LOG.CAR_YEAR, CAR_LOG.CAR_NO, CAR_LOG.Issue_Date, CAR_LOG.Response_Date, CAR_LOG.Due_Date, CAR_LOG.Closing_Date, CAR_LOG.Cat
FROM CAR_LOG
WHERE CAR_LOG.Closing_Date IS NULL OR CAR_LOG.Issue_Date >= #twelvemonthsago#
GROUP BY CAR_LOG.CAR_YEAR, CAR_LOG.CAR_NO, CAR_LOG.Issue_Date, CAR_LOG.Response_Date, CAR_LOG.Due_Date, CAR_LOG.Closing_Date, CAR_LOG.Cat
ORDER BY Issue_Date;
</cfquery>

<!---Get the minimum date from the query CARAccessforGraph for use in the charting function --->
<cfquery dbtype="query" name="minCARDate">
SELECT min(Issue_Date) as BEGINCHARTDATE from CARAccessforGraph
</cfquery>

<!---Get the maximum date from the query CARAccessforGraph for use in the charting function --->
<cfquery dbtype="query" name="maxCARDATE">
SELECT max(Due_Date) as ENDCHARTDATE from CARAccessforGraph
</cfquery>

<!---Loops through query to Concatenate CAR_Year and CAR_NO and add column of data to "CARNUMBER" --->
<cfset temp ="">
<cfset mylist = "">

<cfoutput query="CARAccessforGraph">
<!---Determine CARNUMBER Variable --->
<cfset temp = #NumberFormat(CARAccessforGraph.CAR_YEAR, "0000")#&"-"&#NumberFormat(CARAccessforGraph.CAR_NO,"000")#>
<cfset mylist = ListAppend(mylist, temp)>
</cfoutput>

<cfset ArrayUpdate = ListtoArray(mylist,",",TRUE)>
<cfset queryAddColumn(CARAccessforGraph, "CARNUMBER", "varChar", ArrayUpdate)>

<!---Loops through query to analyze EmptyStrings and build list and array to add column of data to "RequiredResponseDate" --->
<cfset temp ="">
<cfset mylist = "">

<cfoutput query="CARAccessforGraph">
<!---Determine RequiredResponseDate Variable ---> <!---Current Code Represents latest attempt to force Date Object --->
<cfif CARAccessforGraph.Cat EQ 1 >
<cfset temp = #DATEADD("w", 3, CARAccessforGraph.Issue_Date)#>
<cfelseif CARAccessforGraph.Cat EQ 2 >
<cfset temp = #DATEADD("w", 3, CARAccessforGraph.Issue_Date)#>
<cfelse>
<cfset temp = #DATEADD("w", 5, CARAccessforGraph.Issue_Date)#>
</cfif>
<!---<cfset temp = #DateFormat(temp, "short" )#>--->
<!---<cfset temp = #LSParseDateTime(temp)#>--->
<cfset mylist = ListAppend(mylist, temp)>
</cfoutput>

<cfset ArrayUpdate = ListtoArray(mylist,",",TRUE)>
<cfset queryAddColumn(CARAccessforGraph, "RequiredResponseDate", "Date", ArrayUpdate)>

<cfdump var="#CARAccessforGraph#"> <!---Once I am able to get the RequiredResponseDate to pass correctly will need to code other Dates for Charting --->

<!---The following sets up variables for use by ChartDirector --->

<cfset vlabels = CARAccessforGraph["CARNUMBER"].toArray()>
<cfset vIssueDate = CARAccessforGraph["ISSUE_DATE"].toArray()>
<cfset vRequiredResponseDate = CARAccessforGraph["RequiredResponseDate"].toArray()> <!---This passess an array that is not compatiable with the data conversion of ChartDirector, which expects a Date Object --->

<!---Code added to manipulate data type --->

<cfscript>
for(i = 1; i LE ArrayLen(vRequiredResponseDate); i = i + 1)
     vRequiredResponseDate[i] = LSParseNumber(vRequiredResponseDate[i]);
</cfscript>

<cfdump var="#vrequiredResponsedate#">

<!---Below is the code from ChartDirector Ver 5.1 that requires modification to work with CAR data --->
<cfscript>

chartingStartdate = minCARDate.BeginChartDate;
chartingEnddate = DateAdd("d", 30, maxCARDate.EndChartDate);

// ChartDirector for ColdFusion API Access Point
cd = CreateObject("java", "ChartDirector.CFChart");

// A utility to allow us to create arrays with data in one line of code
function Array() {
    var result = ArrayNew(1);
    var i = 0;
    for (i = 1; i LTE ArrayLen(arguments); i = i + 1)
        result[i] = arguments[i];
    return result;
}

// the names of the tasks
labels = vlabels;


// the planned start dates and end dates for the tasks
startDate = vIssueDate;
endDate = vRequiredResponseDate;

// the actual start dates and end dates for the tasks up to now
actualStartDate = Array(CreateDate(2004, 8, 16), CreateDate(2004, 8, 27), CreateDate(
    2004, 9, 9), CreateDate(2004, 9, 18), CreateDate(2004, 9, 22));
actualEndDate = Array(CreateDate(2004, 8, 27), CreateDate(2004, 9, 9), CreateDate(
    2004, 9, 27), CreateDate(2004, 10, 2), CreateDate(2004, 10, 8));

// Create a XYChart object of size 1000 x 1000 pixels. Set background color to light
// green (ccffcc) with 1 pixel 3D border effect.
c = cd.XYChart(1000, 1000, "0xccffcc", "0x000000", 1);


/* Temp code added for troubleshooting*/
temp = "";
for(i = 1; i LE ArrayLen(endDate); i = i + 1)
     temp = temp & "[" & endDate[i] & "] ";
c.addTitle(temp, "arial.ttf", 8);













// Add a title to the chart using 15 points Times Bold Itatic font, with white
// (ffffff) text on a dark green (0x6000) background
/*c.addTitle("Open CARs and 12 month History", "Times New Roman Bold Italic", 15,
    "0xffffff").setBackground("0x006000");*/

// Set the plotarea at (140, 55) and of size 840 x 920 pixels. Use alternative
// white/grey background. Enable both horizontal and vertical grids by setting their
// colors to grey (c0c0c0). Set vertical major grid (represents month boundaries) 2
// pixels in width
c.setPlotArea(140, 55, 840, 920, "0xffffff", "0xeeeeee", cd.LineColor, "0xc0c0c0",
    "0xc0c0c0").setGridWidth(2, 1, 1, 1);

// swap the x and y axes to create a horziontal box-whisker chart
c.swapXY();

// Set the y-axis scale to be date scale from Aug 16, 2004 to Nov 22, 2004, with
// ticks every 7 days (1 week)
// Original Code: c.yAxis().setDateScale(CreateDate(2004, 8, 16), CreateDate(2004, 11, 22), 86400 * 7);
/* Insert of my code to establish chart start and end dates*/
c.yAxis().setDateScale(ChartingStartDate, ChartingEndDate, 86400 * 7);


// Add a red (ff0000) dash line to represent the current day
c.yAxis().addMark(Now(), c.dashLineColor("0xff0000", cd.DashLine));

// Set multi-style axis label formatting. Month labels are in Arial Bold font in "mmm
// d" format. Weekly labels just show the day of month and use minor tick (by using
// '-' as first character of format string).
c.yAxis().setMultiFormat(cd.StartOfMonthFilter(), "<*font=Arial Bold*>{value|mmm d}",
    cd.StartOfDayFilter(), "-{value|d}");

// Set the y-axis to shown on the top (right + swapXY = top)
c.setYAxisOnRight();

// Set the labels on the x axis
c.xAxis().setLabels(labels);

// Reverse the x-axis scale so that it points downwards.
c.xAxis().setReverse();

// Set the horizontal ticks and grid lines to be between the bars
c.xAxis().setTickOffset(0.5);

// Use blue (0000aa) as the color for the planned schedule
plannedColor = "0x0000aa";

// Use a red hash pattern as the color for the actual dates. The pattern is created
// as a 4 x 4 bitmap defined in memory as an array of colors.
actualColor = c.patternColor(Array("0xffffff", "0xffffff", "0xffffff", "0xff0000",
    "0xffffff", "0xffffff", "0xff0000", "0xffffff", "0xffffff", "0xff0000",
    "0xffffff", "0xffffff", "0xff0000", "0xffffff", "0xffffff", "0xffffff"), 4);

// Add a box whisker layer to represent the actual dates. We add the actual dates
// layer first, so it will be the top layer.
actualLayer = c.addBoxLayer(actualStartDate, actualEndDate, actualColor, "Actual");

// Set the bar height to 8 pixels so they will not block the bottom bar
actualLayer.setDataWidth(8);

// Add a box-whisker layer to represent the Required Response date frame
/*c.addBoxLayer(startDate, endDate, plannedColor, "Required Response").setBorderColor(
    cd.SameAsMainColor);*/

// Add a legend box on the top right corner (595, 60) of the plot area with 8 pt
// Arial Bold font. Use a semi-transparent grey (80808080) background.
b = c.addLegend(595, 60, False, "Arial Bold", 8);
b.setAlignment(cd.TopRight);
b.setBackground("0x80808080", -1, 2);

// Output the chart
chart1URL = c.makeSession(GetPageContext(), "chart1");

// Include tool tip for the chart
imageMap1 = c.getHTMLImageMap("", "",
    "title='{xLabel} ({dataSetName}): {top|mmm dd, yyyy} to {bottom|mmm dd, yyyy}'");

</cfscript>
<html>
<body style="margin:5px 0px 0px 5px">
<div style="font-size:18pt; font-family:verdana; font-weight:bold">
    Multi-Layer Gantt Chart
</div>
<hr style="border:solid 1px #000080" />
<cfoutput>

<img src="getchart.cfm?#chart1URL#" usemap="##map1" border="0" />
<map name="map1">#imageMap1#</map>
</cfoutput>
</body>
</html>

  Re: Multi-layer gantt error passing data
Posted by Randy on Mar-27-2014 01:16
| Reply | Monitor
DUH!!!!! LSParseDateTime not LSParseNumber! STUPID! STUPID! STUPID! STUPID!

Figured it out

  Re: Multi-layer gantt error passing data
Posted by Peter Kwan on Mar-27-2014 01:34
| Reply | Monitor
Hi Randy,

You mentioned that you have tried "every function I know to format the date" and it still
produces errors. It probably means that the vRequiredResponseDate[i] is not a date that
is compatible with ColdFusion. (The formatting functions you use are ColdFusion functions
and the error is produced by ColdFusion.) If vRequiredResponseDate[i] is not a date that
is compatible with ColdFusion, it is unlikely to be usable with ChartDirector.

From your code, it seems you tried to compute the vRequiredResponseDate using the
following method:

(a) Compute a date from CARAccessforGraph.Issue_Date. This should be OK so long as
CARAccessforGraph.Issue_Date is valid.

(b) Convert the date in (a) into a text string

(c) Append the text string to a long text string using ListAppend

(d) Split the long text string back to an array of text string using ListtoArray

(e) Add the array of text string to the query as a new column

(f) Read the new column back and convert it to an array

It seems your code performs a lot of conversions - from date to text string to array to
query column and then to another array. There can be issues in any of the conversions.
Why not just create the array directly?


<cfset vRequiredResponseDate=Array(1)>   <!--- create the array directly --->

<cfset i=1>
<cfoutput query="CARAccessforGraph">
<cfif CARAccessforGraph.Cat EQ 1 >
<cfset vRequiredResponseDate[i] = DATEADD("w", 3,
CARAccessforGraph.Issue_Date)>
<cfelseif CARAccessforGraph.Cat EQ 2 >
<cfset vRequiredResponseDate[i] = DATEADD("w", 3,
CARAccessforGraph.Issue_Date)>
<cfelse>
<cfset vRequiredResponseDate[i] = DATEADD("w", 5,
CARAccessforGraph.Issue_Date)>
</cfif>
<cfset i = i + 1>
</cfoutput>


In the above code, the date is never converted to text strings (no #...# in the code).
The array is created directly, not from a text string. This avoids conversion issues,
improves performance and the code is shorter too.

Hope this can help.

Regards
Peter Kwan