I'm currently struggling with my week of year calculations.
Basically I'm designing a report which will break down figures into weekly sales figures.
The script gets the data from MySQL, then loops through the records, adding the various figures to different multi-dimensional array elements.
The user selects a start year/week number, and an end year/week number.
I convert these 2 into unix timestamps with the following function that I found somewhere on here I think:
Code: Select all
function week_timestamp($year, $week_no) {
$offset = date('w', mktime(0, 0, 0, 1, 1, $year));
$offset = ($offset < 5) ? 1-$offset : 8-$offset;
$monday = mktime(0, 0, 0, 1, 1+$offset, $year);
return strtotime('+' . ($week_no - 1) . ' weeks', $monday);
}For the end_stamp, I'm adding 604800 (60s x 60m x 24hours x 7days) to the value returned from the above function.
Because if the user wants to view the figures between week 30 and week 33, I need to be including the figures up to Sunday 23:59 of week 33 in my calculation.
That works and the data I'm returning is correct.
But the simple way I was looping through the MySQL data, meant I was getting a small problem with the report.
If the user ran the report for 2007 week 15 -> week 30 but there was no data for say weeks 28 and 29, I was not having an array element created for weeks 28 and 29.
For a sales report it was misleading because those weeks were just getting missed off the report... for our report it's better to have those rows displayed, but just showing 0 in each column.
So instead of creating the array as I looped through the MySQL results, I thought I'd try and set up the array at the beginning of the script...
Code: Select all
// CREATE BLANK WEEKLY SALES ARRAY
$curr = $start_stamp;
while ($curr < $end_stamp) {
$week_effective = date('Y W', $curr);
$weekly_sales[$week_effective]['usd'] = 0;
$weekly_sales[$week_effective]['eur'] = 0;
$weekly_sales[$week_effective]['gbp'] = 0;
$weekly_sales[$week_effective]['gbp_equiv'] = 0;
$weekly_sales[$week_effective]['geo_usa'] = 0;
$weekly_sales[$week_effective]['geo_row'] = 0;
$weekly_sales[$week_effective]['sales_cat_1'] = 0;
$weekly_sales[$week_effective]['sales_cat_2'] = 0;
$weekly_sales[$week_effective]['sales_cat_3'] = 0;
$weekly_sales[$week_effective]['sales_cat_4'] = 0;
$weekly_sales[$week_effective]['sales_cat_5'] = 0;
$weekly_sales[$week_effective]['sales_cat_6'] = 0;
$weekly_sales[$week_effective]['sales_cat_7'] = 0;
$weekly_sales[$week_effective]['curr'] = $curr;
$curr += 604800;
}Then the key of the 2nd dimension is the particular sales figures from the database.
So the purpose of this was to create my array before looping through my data.
It basically loops through values of curr, which is set to the start timestamp ($start_stamp), then adds 604800 seconds until $curr is >= the end timestamp ($end_stamp).
In theory I should get the array created with every week number between $start_stamp and $end_stamp.
So when I loop through my MySQL data, I just do the following...
Code: Select all
$weekly_sales[$create_stamp]['sales_cat_1'] += $rate_gbp;The calculations and MySQL looping at this point work absolutely perfectly.
Here's the problem I've got...
When users do a report which wraps over a year eg: 2007 week 40 -> 2008 week 5
Then I'm getting an extra array item which corresponds to 2007 week 01!
The reason is because doing
Code: Select all
date('Y W', 1193616000)Can anyone think of a way to solve this?
So when a user does a report that wraps over Dec/Jan, I don't get this 'surplus' row in my array?
Does anyone understand what I'm on about?
Thanks
Ben