|
 Adding 10 year average line. |
| Posted by Paul Huffman on Nov-16-2018 01:13 |
|
I had a request to add a 10 year average to some of my charts. Using PHP and MySQL queries for charts of counts by date. http://www.ykfp.org/php/lyletrap/lylesteelheadurl.php?year=2015
Are there any examples of this in the chart director docs? Looking at my sources now, trying to decide if I should create the average data array with a SQL query or calculate the ten year average in PHP. |
| Re: Adding 10 year average line. |
| Posted by Paul Huffman on Nov-16-2018 08:22 |
|
I guess the query wasn't as hard as I thought. DISTINCT isn't needed but GROUP BY is. This SQL statement got me the results I was looking for. Now to implement in the charting script.
SELECT
MONTH (chkDt) AS MONTH,
DAY (chkDt) AS DAY,
AVG(WildSthd)
FROM
tblFishPassLyle
WHERE
YEAR (chkDt) > 2006
AND YEAR (chkDt) < 2018
GROUP BY
MONTH(chkDt),
DAY (chkDt) |
| Re: Adding 10 year average line. |
| Posted by Peter Kwan on Nov-16-2018 17:58 |
|
Hi Paul,
From your chart, it looks like you have some arrays of values from 05/01/2015 to 04/30/ 2016. For your average line, you can create an array of the same size. For each record in your query (which includes 3 columns for the month, day and avg), you can use your own code to determine the position should of that column in the array. For example, if the month is 5 and the day is 1, then you know it should be the first element of the day. If the month is 12 and day is 27, then it should be in the position that corresponds to 12/27/2015. In this way, you can get your avg data aligned properly with your other data.
Hope this can help.
Regards
Peter Kwan |
| Re: Adding 10 year average line. |
| Posted by Paul Huffman on Jan-10-2019 08:45 |
|
My script selects a date range like "$SQLstatement = "Select Unix_timestamp(ChkDt), WildSthd, HatchSthd, uknSthd From tblFishPassLyle
where ChkDt > '$year-05-01' and ChkDt < '$yearnext-04-30'
order by ChkDt";"
so I wonder if I should add something to my average query so it also starts at >05-01 and ends at < 04-30 . |
| Re: Adding 10 year average line. |
| Posted by Peter Kwan on Jan-11-2019 01:58 |
|
Hi Paul,
Have you tried to use the same "WHERE clause" in your average query, that is, replace:
WHERE
YEAR (chkDt) > 2006
AND YEAR (chkDt) < 2018
with
WHERE
ChkDt > '$year-05-01' and
ChkDt < '$yearnext-04-30'
*** Note ***: Your condition "ChkDt > '$year-05-01' and ChkDt < '$yearnext-04-30'" may not work for some databases or database tables because of the date format. I assume you have already tested the query and it works for your database.
Regrads
Peter Kwan |
| Re: Adding 10 year average line. |
| Posted by Paul Huffman on Jan-11-2019 08:57 |
|
I looked at that logic and I don't think that's what I want. The current year's data data goes from > 2018-05-01 to < 2019-04-30, so I get $year from a url argument,
so
$year = $_GET["year"];
$yearprior = $year - 1;
$yearnext = $year + 1;
.
$SQLstatement = "Select Unix_timestamp(ChkDt), WildSthd, HatchSthd, uknSthd From tblFishPassLyle
where ChkDt > '$year-05-01' and ChkDt < '$yearnext-04-30'
order by ChkDt";
but the ten year daily average from 2006 to 2018 would be from
2006-05-01 to 2007-04-30
2007-05-01 to 2008-04-30
...
...
2018-05-01 to 2019-04-30
But wait, the ten year average spans the whole year, 01-01 to 12-31. I guess my confusion is how to align the ten year average query with the current year query that starts with >05-01.
Been testing the ten year average query with a MySQL tool Navicat, and it looks like the date format of ChkDt is not a problem:https://www.dropbox.com/s/oqye29e7yqk6lx0/querytest.PNG?dl=0 |
| Re: Adding 10 year average line. |
| Posted by Peter Kwan on Jan-11-2019 14:13 |
|
Hi Paul,
Actually, I am not too sure what is ten year daily average means. From your existing query, I assume it should contain 366 records for the 366 days of the year. For each day (say Jan 1, xxxx), the value is the average of the Jan 1, xxxx values for the 10 years.
You may note that it is impossible to align this series with 2018-05-01 to 2019-04-30. It is because it is impossible to align 366 days with 365 days. You must either insert an non-existent date 2019-02-29 in last year's data to make it 366 days, or you must ignore the xxxx-02-29 in your ten year daily average (eg. by using a WHERE clause to skip that date) to align with a non-leap year.
If you want the date range to be from 2008-05-01 to 2018-04-30, which is 10 years, then the WHERE clause is:
where ChkDt > '2008-05-01' and ChkDt < '2018-04-30'
If you think the above query obtain the correct data for each day of month, but the ordering of the rows are incorrect (you want the xxxx-05-01 to be the first row, and xxxx-04-30 to be the last row), there are two methods:
(a) Please add an ORDER BY statement in the query to order the rows so your requirement. For example:
ORDER BY MOD(MONTH(chkDt) + 8, 12) * 100 + DAY(chkDt)
or
(b) Use PHP to rearrange the arrays so that 05-01-xxxx comes first.
Regards
Peter Kwan |
| Re: Adding 10 year average line. |
| Posted by Peter Kwan on Jan-11-2019 14:15 |
|
Hi Paul,
For the example ORDER BY clause, it should be:
ORDER BY MOD(MONTH(chkDt) + 7, 12) * 100 + DAY(chkDt)
Regrads
Peter Kwan |
| Re: Adding 10 year average line. |
| Posted by Paul Huffman on Jan-12-2019 01:56 |
|
| Or since my ten year average is from the fixed interval 2008 to 2018, I could set up the ten year averages by cut and past into a CSV file or a list in PHP or a separate MySQL table, and make an extra file including 2/29 for when the current year is a leap year, rather than coming up with the most eloquent query. |
| Re: Adding 10 year average line. |
| Posted by Paul Huffman on Jan-12-2019 03:42 |
|
Except the query is almost working. I can easily exclude 2/29 for running the ten year average with the current year not a leap year, but I didn't come up with 365 days, I came up with 351 days. I looked into this and I have some dates like 1/01/ on which the trap was never checked because it is a holiday. Will this query go into an array that will correctly get aligned with the current year? Or will I have to make the query create a row for every possible date in a year?
https://www.dropbox.com/s/v4hjfa0i1nrfgll/NoLeapDay.PNG?dl=0 |
| Re: Adding 10 year average line. |
| Posted by Paul Huffman on Jan-12-2019 04:31 |
|
| Do I need to look up the trick I have done before where I create a table with all possible dates in a year, then relate my data to this table to get query results that are the missing dates filled in so the 10 year average set and the current year set have the same number of days? Or does Chart Director align the two data sets to the x axis based on the month/day? |
| Re: Adding 10 year average line. |
| Posted by Peter Kwan on Jan-12-2019 08:06 |
|
Hi Paul Huffman,
ChartDirector aligns the two data sets based on the x-coordinate of the data points.
I am not sure what coordinate system you are using. ChartDirector supports using numbers, or "timestamps" expressed as clock seconds elapsed since Jan 1, 0001 00:00:00. See:
https://www.advsofteng.com/doc/cdphp.htm#dateformat.htm
Note that a date/time in human language is unlikely to PHP or most programming language. For example, "2018-02-03", "Jan 7, 2019" or "二零一四年二月三日" are not date/time in programming. They are treated as text strings and can be displayed on the x-axis as labels for human reading.
If your code provides the date/time for the data points (using Layer.setXData), then the data points will be aligned based on the date/time. If no x-coordinate is provided, the x-coordinates are assumed to be the array index. So the lines will be aligned point by point based on the array index.
The Axis.setLabels provides the labels to the x-axis. As there is no x-coordinate, so the x-coordinate for the labels are also assumed to be the array index. That means each point will be aligned with a corresponding label.
If your code does use Layer.setXData to provide the x-coordinates, you may use Axis.setDateScale to specify the x-axis scale and labels. If you not specify the x-axis scale, ChartDirector will auto-scale and label the x-axis, just like the y-axis is auto-scaled and labeled by ChartDirector.
For your case, there are two methods:
(a) Use Layer.setXData to set the x-coordinates for all the lines.
or
(b) Create an array of 366 elements (for the 366 days) and initialize them to NoValue. Copy the data from your database query to the correct position in the array. Do this for all your lines, and they will be aligned. (Once you write the code to do it for 1 data point, you can use a loop to do it for all data points of all the lines. So it is not difficult in practice.)
Regards
Peter Kwan |
| Re: Adding 10 year average line. |
| Posted by Peter Kwan on Jan-12-2019 08:13 |
|
Hi Paul,
I write the last message without reading your latest message. Now I read it again, I found that you have already written the code to distribute the data into a fixed 365 or 366 days array. In this case, you just need to use the same method for your 10 year average data and all the lines will be consistent.
Regards
Peter Kwan |
| Re: Adding 10 year average line. |
| Posted by Paul Huffman on Jan-24-2019 03:15 |
|
Again, I am confused about the alignment of the current year data and the ten year average.
I am reading in the current year data with a query:
$SQLstatement = "Select Unix_timestamp(ChkDt), WildSthd, HatchSthd, uknSthd
From
tblFishPassLyle
where ChkDt > '$year-05-01' and ChkDt < '$yearnext-04-30'
order by ChkDt";
I use Unix_timestamp here so I can later use chartTime2 from ChartDirector.
Currently, my working query that reads in a ten year average is:
$SQLstatement = "SELECT
MONTH (chkDt) AS MONTH,
DAY (chkDt) AS DAY,
AVG(WildSthd),
AVG(HatchSthd),
AVG(uknSthd)
FROM
tblFishPassLyle
WHERE ChkDt > '2008-05-01' and ChkDt < '2018-04-30'
and NOT (Month(chkDt) = 2 and DAY(chkDt) = 29)
GROUP BY
MONTH(chkDt),
DAY (chkDt)
ORDER BY MOD(MONTH(chkDt) + 7, 12) * 100 + DAY(chkDt)";
When I read my result row items into the average count arrays, do I also need to construct a date variable out of current $year variable, MONTH, DAY and convert with Unix_Timestamp? |
| Re: Adding 10 year average line. |
| Posted by Paul Huffman on Jan-24-2019 05:59 |
|
| Oh, I bet I can use $dateav[$count] = chartTime($year, $row[0], $row[1]); |
| Re: Adding 10 year average line. |
| Posted by Peter Kwan on Jan-24-2019 23:29 |
|
Hi Paul Huffman,
Remember to use $yearnext if the $month <= 4. If $yearnext is not a leap year, you may want to modify the SQL to filter out month=2 and day=29.
Regards
Peter Kwan |
| Re: Adding 10 year average line. |
| Posted by Paul Huffman on Jan-25-2019 01:12 |
|
| Yes, that's what I'm thinking. Thanks. |
| Re: Adding 10 year average line. |
| Posted by Paul Huffman on Feb-16-2019 03:03 |
|
I have a logical problem and a style problem with my chart now.
The logic problem: the ten year average data are only getting plotted out to the right as far as the last date the trap was counted this current year. Or the last time the crew updated the count table. That is because the 10 year average data is getting plotted against this season's dates: $layer->setXData($date);
When I tried to plot the 10 average data against the dates retrieved by the ten year query, $layer->setXData($dateav); the Jan 1 to May 31 data gets drawn to the left of the plot frame because the 10 year average query is in month/day order.
Do I need to fill up an array with dates from '$year-05-01' and ChkDt < '$yearnext-04-30' to use in setXData for this ten year average data?
The style problem is that the ten year average data is a lot more up and down than I expected. The line plot looks very confused. I have played around with colors, but it didn't help much. How can I make the ten year average data look distinct and maybe in the background the current year data? Perhaps bars for the ten year average? Thick, diffuse line symbols? Smooth the ten year average data by a three day running average?
screen shot of a recent version: https://www.dropbox.com/s/1ecssaghbhwtak7/steehead10yr.PNG?dl=0 |
| Re: Adding 10 year average line. |
| Posted by Peter Kwan on Feb-16-2019 14:18 |
|
Hi Paul,
For the 10 year average data, the x-coordinates should reflect what you want the line to appear. If you want the line to appear from '$year-05-01' to '$yearnext-04-30', then your x-coordinates should from '$year-05-01' to '$yearnext-04-30'.
I am not sure the code you are using. According to earlier posts, the average data is not in month/day order. It is "ORDER BY MOD(MONTH(chkDt) + 7, 12) * 100 + DAY(chkDt)". Also, in one of my response, I mentioned "Remember to use $yearnext if the $month <= 4". So the date from Jan 1 to Apr 30 should be on the right side of the chart, as it is for $yearnext.
For the chart style, you may consider to use an area chart for the ten year average and put it at the back of the chart. See:
https://www.advsofteng.com/doc/cdphp.htm#arealine.htm
Hope this can help.
Regards
Peter Kwan |
| Re: Adding 10 year average line. |
| Posted by Paul Huffman on Feb-20-2019 01:41 |
|
I forgot that I had that code to handle $yearnext in my script, but commented out.
But when I uncomment the lines, I get an empty chart, no errors. Tried to turn off chart to print out array contents but still get blank. Am I not understanding how to use if else?
$dateav = array();
while ($row = mysql_fetch_row ($result)) {
if ($row[0]) <= 4 {
$dateav[$count] = chartTime($yearnext, $row[0], $row[1]);
} else {
$dateav[$count] = chartTime($year, $row[0], $row[1]);
} |
| Re: Adding 10 year average line. |
| Posted by Paul Huffman on Feb-20-2019 04:40 |
|
Found it. That should be
if ($row[0] <= 4) { |
| Re: Adding 10 year average line. |
| Posted by Paul Huffman on Feb-20-2019 06:12 |
|
If I play around with colors and transparency, maybe the area chart can tell the story. Shows some promise.
https://www.dropbox.com/s/6a3mhqxbyhlkrb4/sthdArea.PNG?dl=0
You mentioned that the area layer might be put into the background. What controls the drawing order? The order a layer is defined in the script? or is it controlled by the layer number, like $layer0, $layer1, $layer2 ? |
| Re: Adding 10 year average line. |
| Posted by Peter Kwan on Feb-20-2019 14:06 |
|
Hi Paul,
By default, the layer that is added first will stay on top. So if the area layer is to be put at the back, it should be added last.
You can also use Layer.MoveFront/Layer.MoveBack to move a layer in front of or behind another layer.
Hope this can help.
Regards
Peter Kwan |
| Re: Adding 10 year average line. |
| Posted by Paul Huffman on Feb-21-2019 02:49 |
|
I discovered that downloading the compiled HTML version of the docs makes it easier to search.
I tried setZOrder but couldn't make it work. Maybe that is because it is for box objects. It is nice to know that the draw order is in the order that the layers are produced by the script and the first year is drawn on top. I don't know how I got away with naming all my layers $layer. From the examples, it seems like best practice is to name them like $layer0, $layer1, $layer2, etc.
I am going to keep playing around with colors and transparency, legend placement, and maybe take out the alternating blue bands across the plot area.
Currently, my code looks like this:
<?php
require_once("/home/ykfpdata/ykfp.org/php/ChartDirector/lib/phpchartdir.php");
#require_once("/home/ykfpdata/ykfp.org/php/ChartDirector/lib/phpchartdir.php");
#require_once("phpchartdir.php");
#
# lylesteelhead1415.php
# PHP script to read in steelhead from a new MySql database table tblLatPassage and plot a line
# chart of steehead hatchery and wild counts counts.
# Paul Huffman, 1/28/2008
# modified 2/12/08 to read data from table tblFishPassLyle
# modified 6/02/08 by ph from lylesteelhead0803.php to set the date axis May 1 to April 30
# modified 4/14/09 by ph from lylesteelhead0804.php to chart 09-2010 data
# modified 4/14/10 by ph from lylesteelhead09.php to chart 2010-11 data
# modified 2/4/10 by ph from lylesteelhead2010.php to chart 2011-12 data
# modified 7/10/12 by ph from lylesteelhead2011.php to chart 2012-13 data
# modified 7/10/12 by ph from lylesteelhead2011.php to chart 2013-14 data
# modified 4/24/14 by ph from lylesteelhead1314.php to chart 2014-15 data
# modified 9/16/2015 by ph to add unknown clip steelhead.
# modified 1/27/2016 by ph to improve the look of the upper right text
# modified from lylesteelheadurl.php 2/2019 to add a ten year average dataset fot AD present and absent
#
#Create a SQL statement for steelhead for the season $year to $yearnext
#
$year = $_GET["year"];
$yearprior = $year - 1;
$yearnext = $year + 1;
$SQLstatement = "Select Unix_timestamp(ChkDt), WildSthd, HatchSthd, uknSthd From tblFishPassLyle
where ChkDt > '$year-05-01' and ChkDt < '$yearnext-04-30'
order by ChkDt";
#echo $SQLstatement ;
#
#Read in the data into arrays
#
@ $db = mysql_connect( "lyleadulttrap.ykfp.org", "******", "*********", 'lyleadulttrap');
if (mysqli_connect_error())
{
echo 'Error: Could not connect to database.';
exit;
}
$result = mysql_db_query("lyleadulttrap", $SQLstatement);
$num_results = $result->num_rows;
$count = 0;
$wsteelheadtotal = 0;
$hsteelheadtotal = 0;
$uknsteelheadtotal = 0;
$wsteelhead = array();
$hsteelhead = array();
$uknsteelhead = array();
$date = array();
while ($row = mysql_fetch_row ($result)) {
$date[$count] = $row[0];
$wsteelhead[$count] = $row[1];
$hsteelhead[$count] = $row[2];
$uknsteelhead[$count] = $row[3];
#$temp[$count] = $row[2];
if ($wsteelhead[$count] === null) {
$wsteelhead[$count] = NoValue;
}
if ($hsteelhead[$count] === null) {
$hsteelhead[$count] = NoValue;
}
if ($uknsteelhead[$count] === null) {
$uknsteelhead[$count] = NoValue;
}
$wsteelheadtotal = $wsteelheadtotal + $wsteelhead[$count];
$hsteelheadtotal = $hsteelheadtotal + $hsteelhead[$count];
$uknsteelheadtotal = $uknsteelheadtotal + $uknsteelhead[$count];
# print " $date[$count], $wsteelhead[$count], $hsteelhead[$count], <br>n";
#print "$row[2], $hsteelhead[$count], $temp[$count] <br>n";
++$count;
}
if ($wsteelheadtotal === NoValue) {
$wsteelheadtotal = 0;
}
if ($hsteelheadtotal === NoValue) {
$hsteelheadtotal = 0;
}
if ($uknsteelheadtotal === NoValue) {
$uknsteelheadtotal = 0;
}
mysql_free_result($result);
#Now get the data for the ten year average, excluding 2-29.
$SQLstatement = "SELECT
MONTH (chkDt) AS MONTH,
DAY (chkDt) AS DAY,
AVG(WildSthd),
AVG(HatchSthd),
AVG(uknSthd)
FROM
tblFishPassLyle
WHERE ChkDt >= '2008-05-01' and ChkDt <= '2018-04-30'
and NOT (Month(chkDt) = 2 and DAY(chkDt) = 29)
GROUP BY
MONTH(chkDt),
DAY (chkDt)
ORDER BY MOD(MONTH(chkDt) + 7, 12) * 100 + DAY(chkDt)";
#echo $SQLstatement ;
#
#Read in the data into arrays
#
@ $db = mysql_connect( "lyleadulttrap.ykfp.org", "*****", "********", 'lyleadulttrap');
if (mysqli_connect_error())
{
echo 'Error: Could not connect to database.';
exit;
}
$result = mysql_db_query("lyleadulttrap", $SQLstatement);
$num_results = $result->num_rows;
$count = 0;
$wsteelheadtotalav = 0;
$hsteelheadtotalav = 0;
$uknsteelheadtotalav = 0;
$wsteelheadav = array();
$hsteelheadav = array();
$uknsteelheadav = array();
$dateav = array();
while ($row = mysql_fetch_row ($result)) {
if ($row[0] <= 4) {
$dateav[$count] = chartTime($yearnext, $row[0], $row[1]);
} else {
$dateav[$count] = chartTime($year, $row[0], $row[1]);
}
$wsteelheadav[$count] = $row[2];
$hsteelheadav[$count] = $row[3];
$uknsteelheadav[$count] = $row[4];
#$temp[$count] = $row[2];
if ($wsteelheadav[$count] === null) {
$wsteelheadav[$count] = NoValue;
}
if ($hsteelheadav[$count] === null) {
$hsteelheadav[$count] = NoValue;
}
if ($uknsteelheadav[$count] === null) {
$uknsteelheadav[$count] = NoValue;
}
$wsteelheadtotalav = $wsteelheadtotalav + $wsteelheadav[$count];
$hsteelheadtotalav = $hsteelheadtotalav + $hsteelheadav[$count];
$uknsteelheadtotalav = $uknsteelheadtotalav + $uknsteelheadav[$count];
$arrayOfYMD = array_map(getChartYMD, $dateav);
#print "$dateav[$count], $wsteelheadav[$count], $hsteelheadav[$count], <br>n";
#print "$arrayOfYMD[$count], $wsteelheadav[$count], $hsteelheadav[$count], <br>n";
#print "$row[2], $hsteelhead[$count], $temp[$count] <br>n";
++$count;
}
if ($wsteelheadtotalav === NoValue) {
$wsteelheadtotalav = 0;
}
if ($hsteelheadtotalav === NoValue) {
$hsteelheadtotalav = 0;
}
if ($uknsteelheadtotalav === NoValue) {
$uknsteelheadtotalav = 0;
}
mysql_free_result($result);
#Now that the data is in arrays, we can start to draw the chart
#using ChartDirector
#
#Create a XYChart of size 600 pixels x 240 pixels
$c = new XYChart(920, 500);
$c->setDropShadow();
#Set the chart background to pale blue (0xffffc0) with a 2 pixel 3D border
$c->setBackground(metalColor(0x00ccff, 0), 0x00ccff,4);
#Set the plotarea size. Set background
#color to white (0xffffff). Enable both horizontal and vertical grids by
#setting their colors to light grey (0xc0c0c0)
$c->setPlotArea(60, 50, 820, 400, 0xccffff, 0xffffff, 0xc0c0c0, 0xc0c0c0);
#Add a title to the chart
$title = $c->addTitle("Adult steelhead counts at Lyle Falls Trap for $year - $yearnext", "timesbi.ttf");
$title->setBackground(0xffff00);
#Add a legend box at the top of the plotarea
#$legend = $c->addLegend(70, 30, 0, "arialbd.ttf", 8);
#$legend = $c->addLegend(70, 25, 0, "arialbd.ttf", 8);
$legend = $c->addLegend2(70, 30, 3, "arialbd.ttf", 8);
$legend->setBackground(Transparent, Transparent);
#$legend->setBorderColor(Transparent, 1);
#Add a line chart layer for wild steelhead
$layer = $c->addLineLayer2();
$dataSet = $layer->addDataSet($wsteelhead, -1, " Ad Present Steelhead");
$dataSet ->setDataSymbol(SquareSymbol, 4, -1, -1);
$layer->setBorderColor(Transparent, 1);
$date = array_map(chartTime2, $date);
$layer->setXData($date);
#Add a line chart layer for the hatchery steelhead
$layer = $c->addLineLayer2();
$dataSet = $layer->addDataSet($hsteelhead, 0x0099FF, "Ad Clipped Steelhead");
$dataSet ->setDataSymbol(CircleSymbol, 5, -1, -1);
$layer->setBorderColor(Transparent, 1);
#$date = array_map(chartTime2, $date);
$layer->setXData($date);
#Add a line chart layer for the steelhead that were not checked for AD clip
$layer = $c->addLineLayer2();
$dataSet = $layer->addDataSet($uknsteelhead, 0xFF9900, "Ad Unknown Steelhead");
$dataSet ->setDataSymbol(CircleSymbol, 5, -1, -1);
$layer->setBorderColor(Transparent, 1);
$layer->setXData($date);
#Add area chart layer for the 10 year average hatchery steelhead
#using semi-transparent fill color
$layer = $c->addAreaLayer();
$dataSet = $layer->addDataSet($hsteelheadav, 0x7fFFDAB9, " 10 Yr. Av. Ad Clipped Steelhead");
#$dataSet->setZOrder(ChartBackZ);
#$dataSet ->setDataSymbol(CircleSymbol, 5, -1, -1);
$layer->setBorderColor(Transparent, 1);
#$date = array_map(chartTime2, $date);
$layer->setXData($dateav);
#Add a area chart layer for the 10 year average wild steelhead
$layer = $c->addAreaLayer();
$dataSet = $layer->addDataSet($wsteelheadav, 0xAF99cc00, " 10 Yr. Av. Ad Present Steelhead");
#$dataSet ->setDataSymbol(CircleSymbol, 5, -1, -1);
$layer->setBorderColor(Transparent, 1);
#$date = array_map(chartTime2, $date);
$layer->setXData($dateav);
#Set the x-axis width to 2 pixels
$c->xAxis->setWidth(2);
$c->xAxis->setLabelFormat("{value|mm/dd}");
$c->xAxis->setTickDensity(3);
$c->xAxis()->setDateScale(chartTime($year, 5,1), chartTime($yearnext, 4, 31), 14 * 86400);
#Set the y axis title
$c->yAxis->setTitle("Counts");
#Set the y-axis width to 2 pixels
$c->yAxis->setWidth(2);
$c->yAxis->setAutoScale(0, 0);
#
#Add a table for test in upper right
$table=$c->addTable(620,60,TopLeft,3,6);
# Use Arial Bold as the font for the first row
$StyleObj = $table->getStyle();
$StyleObj->setFontStyle("arialbd.ttf");
$StyleObj->setFontSize(10);
$colStyleObj = $table->getColStyle(2);
$colStyleObj->setAlignment(Right);
$colStyleObj = $table->getColStyle(1);
$colStyleObj->setAlignment(Left);
$StyleObj->setBackground(Transparent, Transparent);
#add text to table
$table->setText(1,2,"Hatchery Steelhead this year:");
$table->setText(2,2,$hsteelheadtotal);
$table->setText(1,3,"Wild Steelhead this year:");
$table->setText(2,3,$wsteelheadtotal);
$table->setText(1,4,"AD Unknown Steelhead this year:");
$table->setText(2,4,$uknsteelheadtotal);
#Output the chart
header("Content-type: image/png");
print($c->makeChart2(PNG));
?> |
| Re: Adding 10 year average line. |
| Posted by Paul Huffman on Feb-28-2019 06:25 |
|
I thought it would be easy to extend this chart for the Chinook run. In a Chinook chart I could run the X axis from Jan. 1 to Dec 31, so I wouldn't need the complicated ORDER BY part to the query. But, for Chinook, I would also have adults as well as immature males, jacks, for both hatchery and wild fish, so four data sets of ten year averages to add to the chart. We decided to combine the jacks with the adults for the ten year average to clean up the chart.
I tried code like this to get the ten year averages to hatchery and wild adults and jacks, adding the jacks and adults together.
#Now get the data for the ten year average, excluding 2-29. Adapted from lylesteelheadavar.php
$SQLstatement = "SELECT
MONTH (chkDt) AS MONTH,
DAY (chkDt) AS DAY,
AVG(APchkA),
AVG(APchkJ),
AVG(ACchkA),
AVG(ACchkJ),
AVG(uknChk)
FROM
tblFishPassLyle
WHERE ChkDt >= '2008-01-01' and ChkDt <= '2018-12-31'
and NOT (Month(chkDt) = 2 and DAY(chkDt) = 29)
GROUP BY
MONTH(chkDt),
DAY (chkDt)";
#echo $SQLstatement;
#
#Read in the data into arrays
#
@ $db = mysql_connect( "lyleadulttrap.ykfp.org", "****", "****", 'lyleadulttrap');
if (mysqli_connect_error())
{
echo 'Error: Could not connect to database.';
exit;
}
$result = mysql_db_query("lyleadulttrap", $SQLstatement);
$num_results = $result->num_rows;
#print $num_results;
#print_r $result;
$count = 0;
$wChktotalav = 0;
$hChktotalav = 0;
$uknChktotalav = 0;
$wChkav = array();
$hChkav = array();
$uknChkav = array();
$dateav = array();
$APChkAav = array();
$APChkJav = array();
while ($row = mysql_fetch_row ($result)) {
$dateav[$count] = chartTime($year, $row[0], $row[1]);
/*$wChkav[$count] = $row[2] + $row[3];
$hChkav[$count] = $row[4] + $row[5};
$uknChkav[$count] = $row[6];*/
$APChkAav[$count] = $row[2];
$APChkJav[$count] = $row[3];
$wChkav[$count] = $APChkAav[$count] + $APChkJav[$count];
$ACChkAav[$count] = $row[4];
$ACChkJav[$count] = $row[5];
$hChkav[$count] = $ACChkAav[$count] + $ACChkJav[$count];
#$temp[$count] = $row[2];
if ($wChkav[$count] === null) {
$wChkav[$count] = NoValue;
}
if ($hChkav[$count] === null) {
$hChkav[$count] = NoValue;
}
if ($uknChkav[$count] === null) {
$uknChkav[$count] = NoValue;
}
$wChktotalav = $wChktotalav + $wChkav[$count];
$hChktotalav = $hChktotalav + $hChkav[$count];
$uknChktotalav = $uknChktotalav + $uknChkav[$count];
$arrayOfYMD = array_map(getChartYMD, $dateav);
#print "$dateav[$count], $wChkav[$count], $hChkav[$count], <br>n";
#print "$arrayOfYMD[$count], $wsteelheadav[$count], $hsteelheadav[$count], <br>n";
#print "$row[2], $hsteelhead[$count], $temp[$count] <br>n";
++$count;
}
if ($wChktotalav === NoValue) {
$wChktotalav = 0;
}
if ($hChktotalav === NoValue) {
$hChktotalav = 0;
}
if ($uknChktotalav === NoValue) {
$uknChktotalav = 0;
}
mysql_free_result($result);
But I would get no output and no error messages when trying to add together two row items and assign them to an array as in the commented out lines like:
$wChkav[$count] = $row[2] + $row[3];
I had a hard time tracking this error down, because even when I commented out the lines printing the PNG, and uncommenting my debugging print and echo lines, I would get no output and no errors. I commented out big sections of the code until I started to get debugging output, then narrowed the commented out sections until I got the above section commented out. I didn't know that this construction wouldn't work.
I had to rethink the logic at this point, to make arrays for the average adult count and the average jack count, assign them to result elements in separate lines, then add the two together like:
$APChkAav[$count] = $row[2];
$APChkJav[$count] = $row[3];
$wChkav[$count] = $APChkAav[$count] + $APChkJav[$count];
Then I got worried that this was incorrect, that a sum of two averages in not the same as the averages of the sums on each day. But I think I will be alright because any time the trap was checked, we would put in a count for both adults and jacks even if it were 0, so the number of lines going into both averages should be the same. |
| Re: Adding 10 year average line. |
| Posted by Peter Kwan on Feb-28-2019 22:16 |
|
Hi Paul,
If by "get no output and no errors", you mean you are just getting a blank white page with nothing at all, one common reason is because the code contains PHP error. By default, PHP would not output the error message to the browser to avoid the end users seeing the error message, so you would just see a blank screen. Have you configured PHP to display the error messages? This is by setting display_errors = On and error_reporting = E_ALL in "php.ini", or by adding the following lines of code to the top of the page:
ini_set('display_errors',1);
error_reporting(E_ALL);
Regards
Peter Kwan |
|
Forum Home
Search
Message List
Post Message