Hello all,
Once again I turn to the experts here for some help. Been trying various versions of code to get what I want, but no luck. I'm working on a page I didn't originally create and I want to keep as much of the original code as I can. So I have this code:
$sql = "select idnum, lname, fname, location from employees
where
temp <> '1' and
(
idnum in ($idnumstring) AND (
idnum in (select idnum from CurrentLDAP) or
idnum in (select idnum from shifts
where intime >= '$start' and outtime <= '$stop'))
)
and salary = 'hourly'
order by lname, fname";
$employee = Perform_Select_Query ( $sql, Return_Configuration () );
print $employee[0][error];
$i=0;
foreach ( $employee as $k => $v ) {
if ( !$employee[$k][idnum] ) { break; }
# build array of idnums
$employees[$i] = $employee[$k][idnum];
# hash last and first names by idnum for later use
$lname[ $employee[$k][idnum] ] = $employee[$k][lname];
$fname[ $employee[$k][idnum] ] = $employee[$k][fname];
$emplocation[ $employee[$k][idnum] ] = $employee[$k][location];
$i++;
}
# get list of all offices (sites)
$sql = "select sitename from sites";
$site = Perform_Select_Query ( $sql, Return_Configuration () );
print $site[0][error];
$i=0;
foreach ( $site as $k => $v ) {
if ( !$site[$k][sitename] ) { break; }
$sites[$i] = $site[$k][sitename];
$i++;
}
foreach ($employees as $person) {
# new, much more efficient sql query
$regular_approved=0;
$regular_unapproved=0;
$ot_approved=0;
$ot_unapproved=0;
$dot_approved=0;
$dot_unapproved=0;
$pto_approved=0;
$pto_unapproved=0;
$holiday_approved=0;
$holiday_unapproved=0;
$disability_approved=0;
$disability_unapproved=0;
$maternity_approved=0;
$maternity_unapproved=0;
$juryduty_approved=0;
$juryduty_unapproved=0;
$bereavement_approved=0;
$bereavement_unapproved=0;
$fmla_approved=0;
$fmla_unapproved=0;
$sql = "select
intime,
outtime,
location,
shifttype,
approved
from shifts
where
idnum = '$person'
and intime >= '$start'
and outtime <= '$stop'
and outtime is not null
and outtime <> 0";
$shiftInfo = Perform_Select_Query ( $sql, Return_Configuration () );
print $shiftInfo[0][error];
The code after that just calculates the different shifts/types etc.
What I would like to do is also display some information from the "sites" table in my table that the user sees. The "location" column in the employee table is related to the "sitename" column in the sites table. So for instance the display data is like so and it works perfectly:
# display data
$myDataRows .= "<tr><td>$emplocation[$person]</td><td>$person</td><td>$lname[$person]</td><td>$fname[$person]</td><td>Dept.</td><td>BERE</td><td>$bereavement_approved</td></tr>"
."<tr><td>$emplocation[$person]</td><td>$person</td><td>$lname[$person]</td><td>$fname[$person]</td><td>Dept.</td><td>DISAB</td><td>$disability_approved</td></tr>"
."<tr><td>$emplocation[$person]</td><td>$person</td><td>$lname[$person]</td><td>$fname[$person]</td><td>Dept.</td><td>DOUBLE</td><td>$dot_approved</td></tr>"
."<tr><td>$emplocation[$person]</td><td>$person</td><td>$lname[$person]</td><td>$fname[$person]</td><td>Dept.</td><td>FIXSAL</td><td>$ot_approved</td></tr>"
."<tr><td>$emplocation[$person]</td><td>$person</td><td>$lname[$person]</td><td>$fname[$person]</td><td>Dept.</td><td>HOLI</td><td>$holiday_approved</td></tr>"
."<tr><td>$emplocation[$person]</td><td>$person</td><td>$lname[$person]</td><td>$fname[$person]</td><td>Dept.</td><td>HOUR</td><td>$regular_approved</td></tr>"
."<tr><td>$emplocation[$person]</td><td>$person</td><td>$lname[$person]</td><td>$fname[$person]</td><td>Dept.</td><td>JURY</td><td>$juryduty_approved</td></tr>"
."<tr><td>$emplocation[$person]</td><td>$person</td><td>$lname[$person]</td><td>$fname[$person]</td><td>Dept.</td><td>OVER</td><td>$ot_approved</td></tr>"
."<tr><td>$emplocation[$person]</td><td>$person</td><td>$lname[$person]</td><td>$fname[$person]</td><td>Dept.</td><td>VACN</td><td>$pto_approved</td></tr>";
In the displayed data I need "Dept." to actually be the dept column from the sites table based on $emplocation[$person] value. This value is the location column from the employee.
Hopefully I explained it correctly cause I think I'm confused...haha
I understand if this is too much to ask with all that code, but I thought I'd give it a try before I pull anymore hair out.
Thanks in advance,
Twitch
