Loop not writing correct values

Question

I have a table that contains all the employees in a company (hr_employees). In addition to this table, is another table that has all their start dates and end dates, plus which type of contract they have (hr_employees_status).

With the data I get, I write a 1 or a 0 (1 permanent based, 0 other) to an excel spreadsheet, colour coded as determined by the type of contract (1 = black, 0 = blue, green, grey or red), in each month (from May 2005 up to present). How I determine what should go in which cell is by a case clause, looking at the current status in the hr_employee_status table.

It loops fine, and does 99% of what it should correctly. The only issue is that, as soon as an employee moves over from a temporary contract to a permanent basis, it sometimes doesn't write the correct value to a cell.

I have changed the code a bit to only pull month and year from the database (and set the day to 01), out of hopes that, since the month and year is all that is needed to write to a cell, it would write the correct data in the correct cell. But to no avail.

I don't know what code to post, so here is the loop that determines the type of contract and writes it to the excel sheet:

switch ($dates['status']) {
case '0':
    $color = 'blue';
    $cellcontent="0";
    break;
case '1':
    $color = 23;
    $cellcontent="0";
    break;
case '2':
    $color = 'black';
    $cellcontent="1";
    break;
case '3':
    $color = 'green';
    $cellcontent="0";
    break;
case '4':
    $color = 'red';
    if(mysql_num_rows($query) > 2)
        $cellcontent = "0";
    else {
        $cellcontent="1";
    }
    break;
}
if($s['state'] == '4')
    $color = 'red';
$format_content =& $workbook->addFormat(array('align' => 'center', 'size' => 11, 'numformat' => '@', 'fontfamily' => 'Calibri', 'left' => 1, 'bottom' => 1, 'right' => 1, 'color' => $color));
for($f = $start_at; $f <= $start_at+$count; $f++) {
    $totalmonths = $totalmonths + $cellcontent;
    $worksheet->write($k,15+$f,$cellcontent,$format_content);
}

$date is the results as received from the DB. $start_at is the date the employee started with a contract type (and thus determines which month to mark). $count is the difference between the start date, and the end date (amount of months).

I would like to know WHY does it not start at the correct date when switching form a temporary contract, to a permanent one.

If any other info is required, please let me know.

EDIT 1: - Response to feedback from DaveRandom and Oleg

@DaveRandom: hr_employees looks like this:

------------------------------------------
|employee_id|name|surname|otherinfo|state|
|1          |Foo |Bar    | ******* |  1  |
|2          |Ben |Smith  | ******* |  1  |
------------------------------------------

hr_employees_status looks like this:

------------------------------------------
|employee_id|from_date |to_date   |status|
|1          |2006-07-12|2009-08-11|  0   |
|1          |2009-08-12|0000-00-00|  1   |
|2          |2009-07-01|0000-00-00|  1   |
------------------------------------------

And then the excel sheet will output

Name      Surname     Start Date     *dates*  June-06  July06  ->- July09 Aug09 Sep09
Foo       Bar         2006-07-12              *empty*     0    ->-   0      1     1
Ben       Smith       2009-07-01              *empty*  *empty* ->-   1      1     1

@Oleg

$start_at is as follows:

round(((substr($dates['from_date'],0,4) * 12) + substr($dates['from_date'],5,2)) - ((substr($start_date,0,4) * 12) + substr($start_date,5,2))) + 1;
// $dates is a loop through the hr_employee_status table

See above. $dates is a loop through the hr_employee_status table. I'm 100% sure that status is in there.
Please explain type juggling? All the values in status is varchar (and don't ask me why, looking at it now, it seems like a stupid thing to do...)

Can you show example data from DB and the current/desired spreadsheet output? — DaveRandom, May 21 '12 at 08:12
*"sometimes doesn't write the correct value to a cell"* What does it write instead? Also, any errors/warnings in your logs? — Oleg, May 21 '12 at 15:22
Type juggling is basically automatic variable type conversion. For example, when comparing a String to an Integer - the String is automatically converted to Integer. Thus, in PHP: `var_dump(0 == "a"); // true`. — Oleg, May 21 '12 at 15:26
"sometimes doesn't write the correct value to a cell" means it writes a '0' instead of the '1'. Like let's say the person moves over from a contract to permanent in june 2012, then june 2012 will be marked with a '0' instead of a '1'. — Bird87 ZA, May 22 '12 at 05:44

score 0 · Accepted Answer · answered May 21 '12 at 08:27

We are missing some crucial part of your code, so until you provide more details (could you show us where you define $start_at for example?) I can only guess...

You ask "why does it not start at the correct date" but you never provide the code where $start_at is defined. It could be useful to us.
Why are you switching $dates['status']? Are you sure it's in $dates?
The switch statement compares values after type juggling...

Example from php.net:

switch ("a") {
case 0:
    echo "0";
    break;
case "a": // never reached because "a" is already matched with 0
    echo "a";
    break;
}

This might be your case where the permanent contract status 1 is never reached in your switch statement because of incorrect type of $dates['status'] making it stop at case '0':.

Try using var_dump() to see the exact types and values of variables you're operating on in your code.

Loop not writing correct values

1 Answers1