<?php
    function getAccountStatusTableRow($db, $userRow, $selected, $userTemplatesOnly=false){
        // Initialize the return
        $result = '';

        try {
            // Determine if this is for templates
            if ($userTemplatesOnly==true){
                // leave this function, because this is only for users
                return $result;
            }
            
            // Start the return with the beginning of the select
            $result = '
                        <tr VALIGN="TOP" align="left">
                            <td height="39" align="right" valign="top">
                                <div>
                                    <strong><label>Account Status:</label></strong> &nbsp; &nbsp;
                                </div>
                            </td>
                            <td height="39" align="left" valign="top">
                                ' . $db->getTypeAsSelectOptions('users', 'AccountStatus', 'accountStatus', $selected, 'CreateAndGotoSearchOptions();', 'text-align:left;', true, '') . '
                            </td>
                        </tr>
                    ';
                
        } catch (Exception $ex) {
            // Assign the output string as to what happened
            return $result;
        }

        // Return whatever we got
        return $result;
    }
    
    function getCompanyUnderUser($db, $userRow, $currentUsersCompany, $userTemplatesOnly=false){
        // Initialize the return
        $result = '';

        try {
            // Init vars
            $selectedText = '';
            $foundOneInList = false;

            // Create the query to run
            $sql='';

            // Determine if we are dealing with a SU of the super company
            if ($userRow["CompanyID"]==1 && 
                ($userRow["UserLevel"]=='SU')
               ){
                // Determine they want user templates only
                $userTemplateSQL='AND u.UserTemplateOnly=1 ';
                if (!$userTemplatesOnly){
                    // Set the user templates to be what we are looking at
                    $userTemplateSQL='AND u.UserTemplateOnly=0 ';
                }
                
                // Create the query to run
                $sql='SELECT DISTINCT c.CompanyID, c.CompanyName ' .
                     ' FROM companies c, users u ' .
                     ' WHERE c.CompanyID = u.CompanyID' .
                     ' ' . $userTemplateSQL .
                     ' ORDER BY CompanyName;';
            }
            else {
                // This is only for super users currently, so exit if not
                return;
            }

            // Query the database for the user 
            $rows = $db->queryDB($sql, $userRow, "users_presentation Form occurance 1");

            // Determine if some rows were returned
            if ($rows == null){
                return $result;
            }
            else{
                // Loop through the returned results
                while ($row=mysqli_fetch_array($rows)){
                    // Re-Init var
                    $selectedText = '';

                    // Determine if this is one selected
                    if ($currentUsersCompany == $row["CompanyID"]){
                        // Set the selected text to be all companies
                        $selectedText = 'selected';

                        // Set the variable
                        $foundOneInList = true;
                    }

                    // Add this option to the select
                    $result=$result . '<option value="' . $row["CompanyID"] . '" ' . $selectedText . '>' . $row["CompanyName"] . '</option>';
                }

                // Determine if there was one that is selected
                if (!$foundOneInList){
                    // Set the selected text to be all companies
                    $selectedText = 'selected';
                }

                // Start the return with the beginning of the select
                $result='<div style="text-align:center; margin-top: 8px;">' .
                        '<select name="companySelected" id="companySelected"  onchange="CreateAndGotoSearchOptions();" style="text-align:left;">' .
                        '<option value="-1" ' . $selectedText . '>All Companies</option>' . $result;

                // End the select
                $result=$result . '</select></div>';
            }
        } catch (Exception $ex) {
            // Assign the output string as to what happened
            return $result;
        }

        // Return whatever we got
        return $result;
    }
    
    function retrieveUsersAsList($db, $userRow, $forWhat, $currentUsersCompany, $accountStatus, $sortNumber, $userTemplatesOnly=false){
        // Init variables used
        $employeesToShowSQL='';
        $companyHeader='';
        $selectedCompanySQL='';
        $selectedAccountStatusSQL='';

        // Set up the sort to normal for now
        $sortSQL = 'u.LastName ASC, u.FirstName ASC';
        
        // Set up the name header to normal for now
        $nameHeaderHTML='
                <div class="cell nameCell" onclick="document.getElementById(\'sortNumber\').value=1; CreateAndGotoSearchOptions();" >
                    <p><b>Last Name</b></p>
                </div>
                <div class="cell nameCell" onclick="document.getElementById(\'sortNumber\').value=5; CreateAndGotoSearchOptions();" >
                    <p><b>First Name</b></p>
                </div>
            ';
        
        // Set the redirect form to update user as user at first
        $updateForm='User';
        
        // Determine if this is a template list
        if ($userTemplatesOnly){
            // Setup the header for templates
            $nameHeaderHTML='
                    <div class="cell templateNameCell" onclick="document.getElementById(\'sortNumber\').value=1; CreateAndGotoSearchOptions();" >
                        <p><b>Template Name</b></p>
                    </div>
                ';
            
            // Set the update form as the user template
            $updateForm='UserTemplate';
            
            // Set up the sort for templates
            $sortSQL = 'u.UserName ASC, u.UserLevel';
            
            // See what the current sort is
            if ($sortNumber!=""){
                // Determine which sort the person wants
                switch ($sortNumber) {
                    case 10:
                        // Sort as user level the most high
                        $sortSQL = 'u.UserLevel, u.UserName';

                        break;
                    default:
                        break;
                }
            }
        }
        else {
            // See what the current sort is
            if ($sortNumber!=""){
                // Determine which sort the person wants
                switch ($sortNumber) {
                    case 5:
                        // Sort as first name the most high
                        $sortSQL = 'u.FirstName ASC, u.LastName ASC, u.UserLevel, c.CompanyName';

                        break;
                    case 10:
                        // Sort as level the most high
                        $sortSQL = 'u.UserLevel, u.FirstName ASC, u.LastName ASC, c.CompanyName';

                        break;
                    case 15:
                        // Sort as company the most high
                        $sortSQL = 'u.CompanyName, u.UserLevel, u.FirstName ASC, u.LastName ASC';

                        break;
                    default:
                        break;
                }
            }
        }
        
        // Determine if the selected company is NOT the first one
        if ($accountStatus != null && $accountStatus!='ALL TYPES'){
            // Add the selected company SQL
            $selectedAccountStatusSQL = 'AND u.AccountStatus="' . $accountStatus . '" ';
        }
            
        // Setup the sql as using templates for the user company
        $sql='SELECT * FROM users u ' .
             'WHERE UserLevel <> "SU" ' .
             'AND UserTemplateOnly=1 '.
             'AND CompanyID = ' . $userRow["CompanyID"] . ' ' .
             $selectedAccountStatusSQL .
             'ORDER BY ' . $sortSQL . ';';

        // Determine they want user templates only
        if (!$userTemplatesOnly){
            // change it for not using templates for the user company
            $sql='SELECT * FROM users u ' .
                 'WHERE UserLevel <> "SU" ' .
                 'AND UserTemplateOnly=0 '.
                 'AND CompanyID = ' . $userRow["CompanyID"] . ' ' .
                 $selectedAccountStatusSQL .
                 'ORDER BY ' . $sortSQL . ';';
        }

        // Determine if this is a super user
        if ($userRow["CompanyID"]==1){
            // Determine if the selected company is NOT the first one
            if ($currentUsersCompany != null && 
                $currentUsersCompany != -1){
                // Add the selected company SQL
                $selectedCompanySQL = 'AND u.CompanyID=' . $currentUsersCompany . ' ';
            }
        }

        // Determine if this user is a SU
        $headerHTML=getAccountStatusTableRow($db, $userRow, $accountStatus, $userTemplatesOnly);
        if (($userRow["UserLevel"]=='SU' ||
             $userRow["UserLevel"]=='EXECUTIVE') && 
            $userRow["CompanyID"]==1){
            // Determine if this is NOT for time card users
            $companyRowHTML='';
            $clockAllOutRowHTML='';
            if ($forWhat==0){
                // Add the company drop down
                $companyRowHTML = '
                        <tr VALIGN="TOP" align="left">
                            <td height="39" align="right" valign="top">
                                <div style="margin-top: 8px;">
                                    <strong><label>Company:</label></strong> &nbsp; &nbsp;
                                </div>
                            </td>
                            <td height="39" align="left" valign="top">
                                ' . getCompanyUnderUser($db, $userRow, $accountStatus, $userTemplatesOnly) . '
                            </td>
                        </tr>
                    ';
            }
            elseif ($forWhat==1){
                // Add the clock all out controls
                $clockAllOutRowHTML='
                            <tr VALIGN="TOP" align="laft">
                                <td height="39" align="right" valign="top">
                                    <strong><label>Clock Out Date/Time:</label></strong> &nbsp; &nbsp;
                                </td>
                                <td height="39" align="left" valign="top" width="45%">
                                    <input name="clockAllOutTime" id="clockAllOutTime" type="datetime-local" class="intext" style="width: 100%;"></input>
                                </td>
                                <td>
                                    <button title="This will clock out all users currently clocked in.  If the time is left blank, then the current time will be used." ' . 
                                           'onclick="javascript:if (confirm(\'Are you sure you want to clock everyone out? This will clock out all users currently clocked in.  If the time is left blank, then the current time will be used.\')) ' .
                                           '{clockOutAllEmployees();}""/>Clock Out All</button>
                                </td>
                            </tr>
                        ';
                
            }
            
            // Add the clock out all HTML
            $headerHTML='
                    <table>
                        ' . $companyRowHTML . $clockAllOutRowHTML . getAccountStatusTableRow($db, $userRow, $accountStatus , $userTemplatesOnly) . ' 
                    </table>
                ';
            
            // determine if super user
            if ($userRow["UserLevel"]=='SU' && !$userTemplatesOnly){
                // Setup a company column header
                $companyHeader = '
                    <div class="cell companyCell" onclick="document.getElementById(\'sortNumber\').value=15; CreateAndGotoSearchOptions();">
                        <p><b>Company</b></p>
                    </div>
                    ';
            }

            // Determine if this is only employees of the main company then create delimiter
            if ($forWhat==1 || $forWhat==2){
                // Create delimiter to only look at the users marked employees
                $employeesToShowSQL=' AND SUEmployee=1 ';
            }

            // Set up the sort for templates
            $sortSQL = 'u.UserName ASC, u.UserLevel';

            // Determine if this is NOT a template only
            if (!$userTemplatesOnly){
                // Sort as company the most high
                $sortSQL = 'c.CompanyName, u.LastName ASC, u.FirstName ASC, u.UserLevel';
            }

            // See what the current sort is
            if ($sortNumber!=""){
                // Determine if this is a template only
                if ($userTemplatesOnly){
                    // Determine which sort the person wants
                    switch ($sortNumber) {
                        case 1: 
                            // Sort as last name the most high
                            $sortSQL = 'u.UserName ASC, u.UserLevel';

                            break;
                        case 10:
                            // Sort as level the most high
                            $sortSQL = 'u.UserLevel, u.UserName';
                            
                            break;
                        default:
                            break;
                    }
                }
                else {
                    // Determine which sort the person wants
                    switch ($sortNumber) {
                        case 1: 
                            // Sort as last name the most high
                            $sortSQL = 'u.LastName ASC, u.FirstName ASC, c.CompanyName, u.UserLevel';

                            break;
                        case 5:
                            // Sort as first name the most high
                            $sortSQL = 'u.FirstName ASC, u.LastName ASC, c.CompanyName, u.UserLevel';

                            break;
                        case 10:
                            // Sort as level the most high
                            $sortSQL = 'u.UserLevel, c.CompanyName, u.FirstName ASC, u.LastName ASC';

                            break;
                        case 15:
                            // Sort as company the most high
                            $sortSQL = 'c.CompanyName, u.LastName ASC, u.FirstName ASC, u.UserLevel';

                            break;
                        default:
                            break;
                    }
                }
            }
        
            // Setup a query to use template before checking the flag
            $sql='SELECT * ' .
                 'FROM users u, companies c ' .
                 'WHERE u.CompanyID=c.CompanyID ' .
                 'AND u.UserTemplateOnly=1 '.
                 $selectedCompanySQL .
                 $selectedAccountStatusSQL .
                 $employeesToShowSQL .
                 'ORDER BY ' . $sortSQL . ';';

            // Check if we are NOT using templates
            if (!$userTemplatesOnly){
                // Setup the query to NOT use a template
                $sql='SELECT * ' .
                     'FROM users u, companies c ' .
                     'WHERE u.CompanyID=c.CompanyID ' .
                     'AND u.UserTemplateOnly=0 '.
                     $selectedCompanySQL .
                     $selectedAccountStatusSQL .
                     $employeesToShowSQL .
                    'ORDER BY ' . $sortSQL . ';';
            }
        }

        
        // Initialize the return
        $result = 
            '<input type="hidden" id="sortNumber" name="sortNumber" value="' . $sortNumber . '"/>' .
            $headerHTML . '
            <li class="ui-state-default ui-state-disabled Table">
                <div class="Heading">' . 
                    $nameHeaderHTML . 
                    '<div class="cell levelCell" onclick="document.getElementById(\'sortNumber\').value=10; CreateAndGotoSearchOptions();" >
                        <p><b>Level</b></p>
                    </div>
                    ' . $companyHeader . '
                    <div class="cell updateCell">
                        <p><b>Edit</b></p>
                    </div>
                </div>
            </li>';

        try {
            // Query the database for the users 
            $rows = $db->queryDB($sql, $userRow, "users_presentation Form occurance 2");

            // Loop through the returned results
            while ($row=mysqli_fetch_array($rows)){
                // Determine what color this row should be
                $color='';
                switch ($row["UserLevel"]){
                    case 'SU':
                        // Set the color to be blueish
                        $color='#708df5';

                        // Break out of this case statement
                        break;
                    case 'EXECUTIVE':
                        // Set the color to be greenish
                        $color='#2bd566';

                        // Break out of this case statement
                        break;
                    case 'MANAGEMENT':
                        // Set the color to be yellowish
                        $color='#f5fd93';

                        // Break out of this case statement
                        break;
                    case 'MID-MANAGEMENT' :
                        // Set the color to be purpleish
                        $color='#8b62cf';

                        // Break out of this case statement
                        break;
                    case 'TYPICAL' :
                        // Set the color to be orangeish
                        $color='#e9a82f';

                        // Break out of this case statement
                        break;
                    default:
                        // Set the color to be redish
                        $color='#ee4462';

                        // Break out of this case statement
                        break;
                }

                // Determine if this is for employee timecards
                if ($forWhat==1){
                    // Set the edit cell to be editable and deleteable
                    $updateCell = '             <td>
                                                    <button type="button" onclick="window.location = \'timecardHistory.php?timecardUserID=' . $row["UserID"] . '\';" title="View Timecards"/>...</button>
                                                </td>';
                }
                else if ($forWhat==2){
                    // Set the edit cell to be editable and deleteable
                    $updateCell = '             <td>
                                                    <button type="button" onclick="window.location = \'bonusHistory.php?timecardUserID=' . $row["UserID"] . '\';" title="View Timecards"/>...</button>
                                                </td>';
                }
                else{
                    // Set the edit cell to be editable and deleteable
                    $updateCell = ' 
                                    <td>
                                        <input type="button" style="font-size:11px;" value="X" title="Remove this."' . 
                                        ' onclick="javascript:if (confirm(\'Are you sure you want to delete this?\n\n' . 
                                        'There may be broken links afterwards.\'))' . 
                                        '{window.location=\'redirectors/delete_user.php?deleteUserID=' . $row["UserID"] . '&uto=' . $userTemplatesOnly . '\';}"/>
                                    </td>
                                    <td>
                                        <input type="button" style="font-size:11px;" value="..." title="Edit this." ' . 
                                        'onclick="javascript:window.location=\'update' . $updateForm . '.php?updateUserID=' . $row["UserID"] . '\';"/>
                                    </td>
                                   ';
                }

                // If this is a super user, add the company name cell
                $companyCell = '';
                if ($userRow["UserLevel"]=='SU' && !$userTemplatesOnly){
                    // populate it
                    $companyCell = 
                        '<div class="cell companyNameCell">
                            <input type="text" maxlength="150" style="resize: none;background-color: #' . $row["CompanyRowColor"] . ';border: 1px solid black;width:15px;height:15px;"></input>' . 
                            '<label style="padding-left: 20px;">' .$row["CompanyName"] . '</label>
                        </div>';
                }
                else {
                    // Now determine if somebody should not be able to edit or delete this field
                    if ((($userRow["UserLevel"]=='EXECUTIVE') && ($row["UserLevel"]=='SU' || $row["UserLevel"]=='EXECUTIVE')) ||
                        (($userRow["UserLevel"]=='MANAGEMENT') && ($row["UserLevel"]=='SU' || $row["UserLevel"]=='EXECUTIVE' || $row["UserLevel"]=='MANAGEMENT')) ||
                        (($userRow["UserLevel"]=='MID-MANAGEMENT') && ($row["UserLevel"]=='SU' || $row["UserLevel"]=='EXECUTIVE' || $row["UserLevel"]=='MANAGEMENT' || $row["UserLevel"]=='MID-MANAGEMENT'))){
                        // Change the tag to not show as editable
                        $updateCell = '             <td>
                                                        UnEditable
                                                    </td>';
                    }

                }

                // Set up the name header to normal for now
                $nameRowHTML='
                        <div class="cell nameCell">
                            <p>' . $row["LastName"] . '</p>
                        </div>
                        <div class="cell nameCell">
                            <p>' . $row["FirstName"] . '</p>
                        </div>
                    ';
                // Determine if this is a template list
                if ($userTemplatesOnly){
                    // Setup the header for templates
                    $nameRowHTML='
                            <div class="cell templateNameCell">
                                <p>' . $row["UserName"] . '</p>
                            </div>
                        ';
                }
        
                // Add this option to the select
                $order=1;
                $result = $result .
                    '<li id="User_' . $order . '" class="ui-state-default Table" >
                        <div class="Row" style="background-color: '. $color . '; border-style: solid; border-color: '. $color . ';">' .
                            $nameRowHTML .
                            '
                            <div class="cell levelCell">
                                <p>' . $row["UserLevel"] . '</p>
                            </div>' .
                            $companyCell .
                            '<div class="cell updateCell">
                                <span>
                                    <table>
                                        <tr>' .
                                        $updateCell .
                                        '</tr>
                                    </table>
                                </span>
                            </div>
                        </div>
                    </li>';
            }

        } catch (Exception $ex) {
            // Assign the output string as to what happened
            return $result;
        }

        // Return whatever we got
        return $result;
    }

    function retrieveSUEmployeesTimeAsList($db, $userRow){
        // Initialize the return
        $result = 
            '<li class="ui-state-default ui-state-disabled Table">
                <div class="Heading">
                    <div class="cell trialCell">
                        <p><b>Type</b></p>
                    </div>
                    <div class="cell nameCell">
                        <p><b>Last Name</b></p>
                    </div>
                    <div class="cell nameCell">
                        <p><b>First Name</b></p>
                    </div>
                    <div class="cell levelCell">
                        <p><b>Employee Type</b></p>
                    </div>
                    <div class="cell dateTimeCell">
                        <p><b>Date/Time</b></p>
                    </div>
                    <div class="cell clockInStatusCell">
                        <p><b>Clock In Status</b></p>
                    </div>
                    <div class="cell durationCell" title="This is the amount of time the user has been in this current status.">
                        <p><b>Status Span</b></p>
                    </div>
                    <div class="cell weekHoursCell" title="This is the current hours worked for the week.">
                        <table align="center">
                            <tr>
                                <td style="font-size: 13px;" align="center" colspan=2><b>Week Hours</b></td>
                            </tr>
                            <tr>
                                <td style="font-size: 11px; border: 1px solid black;" align="center" title="This is the current hours worked for the week." width="50"><b>Week 1</b></td>
                                <td style="font-size: 11px; border: 1px solid black;" align="center" title="This is the current hours worked for the week." width="50"><b>Week 2</b></td>
                            </tr>
                        </table>
                    </div>
                </div>
            </li>';
        try {
            // Create the query to get the the SU company users and clocking status
            $sql=
                'SELECT u.FirstName, u.LastName, u.UserLevel, u.UserID, u.AddDate, u.PayPeriod,
                        lc.StartTime, lc.EndTime, lc.ClockID, lc.CurrentLaborCategoryStartTime,   
                        CURRENT_TIMESTAMP as CurrentDateTime,
                        lcs.Description AS LCDescription, lcs.Name AS LCName, lcs.RowColor AS LCRowColor, lcs.LaborCategoryID, lcs.IsOnTheClock,
                        IF(eb.TrialPeriod IS NULL, 1, eb.TrialPeriod) AS TrialPeriod,
                        (SELECT MAX(PayDateStart) FROM laborclock WHERE UserID = u.UserID) AS MaxPayDateStart
                 FROM users u 
                 INNER JOIN laborclock lc ON lc.UserID = u.UserID 
                 LEFT JOIN employeebenefits eb ON eb.UserID = u.UserID 
                 LEFT JOIN laborcategories lcs ON lc.CurrentLaborCategoryID=lcs.LaborCategoryID
                 WHERE u.SUEmployee = 1 
                 AND   u.UserTemplateOnly=0 
                 AND   u.AccountStatus = "ACTIVE"
                 AND   lc.StartTime = 
                         (SELECT max(StartTime) FROM laborclock WHERE UserID = u.UserID)
                 ORDER BY u.LastName ASC, u.FirstName ASC;';

            // Query the database for the users 
            $rows = $db->queryDB($sql, $userRow, "users_presentation Form occurance 3");

            // Loop through the returned results
            while ($row=mysqli_fetch_array($rows)){
                // Calculate what pay period we should be in. Could be the same.
                $payDateStart = calculateCurrentPaydayStart($row["PayPeriod"], $row["MaxPayDateStart"]);
                
                // Define week hours variables
                $week1Hours=0;
                $week2Hours=0;
                $week1CSS='';
                $week2CSS='';
                $week1Title='User has zero hours the 1st week.';
                $week2Title='User has zero hours the 2st week.';

                // Create query to get 1st week of hours
                $sql='SELECT lc.PayDateStart,
                                 ROUND(SUM((IF(lc.EndTime IS NULL, UNIX_TIMESTAMP(CONVERT_TZ(NOW(), "+00:00", "-01:00")),UNIX_TIMESTAMP(lc.EndTime)) - UNIX_TIMESTAMP(lc.StartTime)))/3600,2) AS FirstWeekHours
                      FROM laborclock lc 
                      LEFT JOIN users u ON lc.UserID=u.UserID 
                      LEFT JOIN laborcategories lcg ON lc.CurrentLaborCategoryID=lcg.LaborCategoryID 
                      WHERE lc.UserID=' . $row["UserID"] . '
                      AND UNIX_TIMESTAMP(lc.StartTime) BETWEEN UNIX_TIMESTAMP("' . $payDateStart->format('Y-m-d H:i:s') . '") and (UNIX_TIMESTAMP("' .$payDateStart->format('Y-m-d H:i:s') . '")+604800)
                      AND lc.PayDateStart="' . $payDateStart->format('Y-m-d H:i:s') . '";';                    
//echo("<br><br>days = " . $sql . "     ");

                // Attempt to get the 1st week OT
                $week1HoursRow1 = $db->queryDB($sql, $userRow, "users_presentation Form occurance 2");

                // determine if the is a last entry in the database
                $week1HoursAssocRow1=null;
                if ($week1HoursRow1!=null){
                    // Attempt to get the week 1 hours row
                    $week1HoursAssocRow1 = $week1HoursRow1->fetch_assoc();

                    // Set the week 1 hours from whatever we got
                    $week1Hours=$week1HoursAssocRow1["FirstWeekHours"];
                    
                    // Determine what we got back from the query
                    if ($week1Hours==null || $week1Hours=='' || $week1Hours<=0){
                        // Set the back ground color to be yellow
                        $week1CSS='background-color:#FBEC5D;';
                        
                        // Set it to be zero
                        $week1Hours='0.0';
                    }
                    else if ($week1Hours>0 && $week1Hours<35){
                        // Set the back ground color to be blue
                        $week1CSS='background-color:#89CFF0;';

                        // Get a readable amount of time for the week to be seen
                        $week1Title='The user has worked ' . intval($week1Hours) . ' hours and ' . round((($week1Hours-intval($week1Hours))*60),2) . ' minutes for the 1st week.';
                    }
                    else if ($week1Hours>=35 && $week1Hours<=40){
                        // Set the back ground color to be green
                        $week1CSS='background-color:#AFE1AF;';

                        // Get a readable amount of time for the week to be seen
                        $week1Title='The user has worked ' . intval($week1Hours) . ' hours and ' . round((($week1Hours-intval($week1Hours))*60),2) . ' minutes for the 1st week.';
                    }
                    else {
                        // This must be over forty.  Turn them hot pink. #FF10F0
                        $week1CSS='background-color:#FF10F0;';

                        // Get a readable amount of time for the week to be seen
                        $week1Title='The user has worked ' . intval($week1Hours) . ' hours and ' . round((($week1Hours-intval($week1Hours))*60),2) . ' minutes for the 1st week.';
                    }
                }

                // Create query to get 2nd week hours
                $sql='SELECT lc.PayDateStart,
                                 ROUND(SUM((IF(lc.EndTime IS NULL, UNIX_TIMESTAMP(CONVERT_TZ(NOW(), "+00:00", "-01:00")),UNIX_TIMESTAMP(lc.EndTime)) - UNIX_TIMESTAMP(lc.StartTime)))/3600,2) AS SecondWeekHours
                      FROM laborclock lc 
                      LEFT JOIN users u ON lc.UserID=u.UserID 
                      LEFT JOIN laborcategories lcg ON lc.CurrentLaborCategoryID=lcg.LaborCategoryID 
                      WHERE lc.UserID=' . $row["UserID"] . '
                      AND UNIX_TIMESTAMP(lc.StartTime) BETWEEN (UNIX_TIMESTAMP("' . $payDateStart->format('Y-m-d H:i:s') . '")+604800) AND (UNIX_TIMESTAMP("' . $payDateStart->format('Y-m-d H:i:s') . '")+(604800*2))
                      AND lc.PayDateStart="' . $payDateStart->format('Y-m-d H:i:s') . '";';                    
//echo("<br><br>days = " . $sql . "     ");

                // Attempt to get the 1st week hours
                $week2HoursRow2 = $db->queryDB($sql, $userRow, "users_presentation Form occurance 3");

                // determine if the is a last entry in the database
                $week2HoursAssocRow2=null;
                if ($week2HoursRow2!=null){
                    // Attempt to get the week 2 hours row
                    $week2HoursAssocRow2 = $week2HoursRow2->fetch_assoc();

                    // Set the week 2 hours from whatever we got
                    $week2Hours=$week2HoursAssocRow2["SecondWeekHours"];
                    
                    // Determine what we got back from the query
                    if ($week2Hours==null || $week2Hours=='' || $week2Hours<=0){
                        // Set the back ground color to be yellow
                        $week2CSS='background-color:#FBEC5D;';
                        
                        // Set it to be zero
                        $week2Hours='0.0';
                    }
                    else if ($week2Hours>0 && $week2Hours<35){
                        // Set the back ground color to be blue
                        $week2CSS='background-color:#89CFF0;';

                        // Get a readable amount of time for the week to be seen
                        $week2Title='The user has worked ' . intval($week2Hours) . ' hours and ' . round((($week2Hours-intval($week2Hours))*60),2) . ' minutes for the 2nd week.';
                    }
                    else if ($week2Hours>=35 && $week2Hours<=40){
                        // Set the back ground color to be green
                        $week2CSS='background-color:#AFE1AF;';

                        // Get a readable amount of time for the week to be seen
                        $week2Title='The user has worked ' . intval($week2Hours) . ' hours and ' . round((($week2Hours-intval($week2Hours))*60),2) . ' minutes for the 2nd week.';
                    }
                    else {
                        // This must be over forty.  Turn them hot pink. #FF10F0
                        $week2CSS='background-color:#FF10F0;';

                        // Get a readable amount of time for the week to be seen
                        $week2Title='The user has worked ' . intval($week2Hours) . ' hours and ' . round((($week2Hours-intval($week2Hours))*60),2) . ' minutes for the 2nd week.';
                    }
                }

                // Set the color to be greenish to start
                $rowColor='#2bd566';
                
                //Init the duration string
                $durationString = '';
                
                // Init the status span title
                $statusSpanTitle='';

                // Set the time cell to be the end time at first
                $timeCell = 'Out At: ' . $row["EndTime"];
                
                // Set the trial cell to be nothing and color green
                $trialCell = '';
                $trialColor = '#00A86B';
                
                // Assume end time at first and convert to PHP datetime
                $dateToUse = new DateTime($row["AddDate"]); 

                // Get the difference between now as interval and decimal 
                // TODO: Minus 1 for the hour difference between server and workplace.
                $dateDiff  = $dateToUse->diff(new DateTime()); 
                $decimalDays = round(($dateDiff->s / 3600 + $dateDiff->i / 60 + 
                               $dateDiff->h/24) + $dateDiff->days, 2);
                
                // Set the time cell as the amount of days this employee has been with the company
                $timeAtCompany = 'This employee has been at the company for ' . round($decimalDays/365,2) . ' years.';
                
                // Determine if this user is a trial user
                if ($row["TrialPeriod"]==1){
                    // Set the field to be checked and color to be yellow
                    $trialCell = '✔';
                    $trialColor = '#FCE205';
                    
                    // Set the time cell as the amount of days this employee has been with the company
                    $timeAtCompany = 'This person has been on the trial period at the company for ' . $decimalDays . ' days.';

                    // Determine if they have been on the trial for more than 90 days
                    if ($decimalDays>90){
                        // Set the color to be a red shade
                        $trialColor = '#FF2800';
                        
                        // Set the time cell as the amount of days this employee has been with the company
                        $timeAtCompany = 'This person has been on the trial period at the company for ' . $decimalDays . 
                                         ' days and is due for a review.';
                    }
                    
                }
               
                // Determine if this use is a SU
                $decimalHours=0;
                if ($row["UserLevel"]=='SU'){
                    // Set the time cell to be ALWAYS
                    $timeCell = 'In At: ALWAYS';

                    // Create the string
                    $durationString = '&infin;';
                }
                Else {
                    // Determine If they are clocked in
                    if ($row["EndTime"]!=null){
                        // Set the color to be redish to start
                        $rowColor='#ee4462';
                    }
                    else {
                        // Set the time cell to be the end time at first
                        $timeCell = 'In At: ' . $row["StartTime"];
                    }
                    
                    // Assume end time at first and convert to PHP datetime
                    $dateToUse = new DateTime($row["CurrentLaborCategoryStartTime"]); 

                    // Get the difference between now as interval and decimal 
                    // TODO: Minus 1 for the hour difference between server and workplace.
                    $dateDiff  = $dateToUse->diff(new DateTime()); 
                    $decimalHours = round($dateDiff->s / 3600 + $dateDiff->i / 60 + 
                                   $dateDiff->h + $dateDiff->days * 24, 2);

                    // Get the days/hour minutes for readability in the hover over.
                    $statusSpanTitle= $dateDiff->format("It has been %a days %H Hours %I Minutes and %S Seconds that this user has been on this status.");
                    
                    // Create the string
                    $durationString = round($decimalHours,2) . ' hrs';
                }
                
                // Set a variable to allow extra CSS for the duration
                $extraClockInClass='';
                
                // Determine if they are on 10-minute break and over 10 minutes
                if ($row["LCName"]=="10 Min Break" && $decimalHours>0.16666){
                    // Start this blinking
                    $extraClockInClass=" waitingForConnection";

                }
                
                // Determine if they are clocked in or not
                $which='Clocked Out';
                if ($row["IsOnTheClock"]==1){
                    // Set them to clocked in
                    $which='Clocked In';
                }

                // Set the clock in status as the lowest until determine that they can modify other users status
                $clockedInStatusHTML='<p class="standardFontSize ' . $extraClockInClass . '">' . $row["LCName"] . '</p>';
                
                // Determine what color this row should be
                $levelCellColor='';
                $employeeType='STANDARD';
                switch ($row["UserLevel"]){
                    case 'SU':
                        // Set the color to be blueish
                        $levelCellColor='#708df5';
                        
                        // Determine if they can modify this user
                        if ($userRow["UserLevel"]=='SU'){
                            // Set the html to allow them to edit it
                            $clockedInStatusHTML='<p class="standardFontSize ' . $extraClockInClass . '"><b>' . getLaborCategoryAsSelectOptions($db, $userRow, $row["LaborCategoryID"], $which, ('background-color: ' . $row["LCRowColor"] . '; border-color: ' . $row["LCRowColor"] . ';'), 'javascript: changeUserClockStatus(' . $row["ClockID"] . ', this.options[this.selectedIndex].value, \'' . session_id() . '\'); location.reload();') . '</b></p>';
                        }
                        
                        // Set the employee type as owner
                        $employeeType='OWNER';

                        // Break out of this case statement
                        break;
                    case 'EXECUTIVE':
                        // Set the color to be greenish
                        $levelCellColor='#2bd566';

                        // Determine if they can modify this user
                        if ($userRow["UserLevel"]=='SU' || 
                            $userRow["UserLevel"]=='EXECUTIVE'){
                            // Set the html to allow them to edit it
                            $clockedInStatusHTML='<p class="standardFontSize ' . $extraClockInClass . '"><b>' . getLaborCategoryAsSelectOptions($db, $userRow, $row["LaborCategoryID"], $which, ('background-color: ' . $row["LCRowColor"] . '; border-color: ' . $row["LCRowColor"] . ';'), 'javascript: changeUserClockStatus(' . $row["ClockID"] . ', this.options[this.selectedIndex].value, \'' . session_id() . '\'); location.reload();') . '</b></p>';
                        }

                        // Set the employee type as executive
                        $employeeType='EXEC';

                        // Break out of this case statement
                        break;
                    case 'MANAGEMENT':
                        // Set the color to be yellowish
                        $levelCellColor='#f5fd93';

                        // Determine if they can modify this user
                        if ($userRow["UserLevel"]=='SU' || 
                            $userRow["UserLevel"]=='EXECUTIVE' ||
                            $userRow["UserLevel"]=='MANAGEMENT'){
                            // Set the html to allow them to edit it
                            $clockedInStatusHTML='<p class="standardFontSize ' . $extraClockInClass . '"><b>' . getLaborCategoryAsSelectOptions($db, $userRow, $row["LaborCategoryID"], $which, ('background-color: ' . $row["LCRowColor"] . '; border-color: ' . $row["LCRowColor"] . ';'), 'javascript: changeUserClockStatus(' . $row["ClockID"] . ', this.options[this.selectedIndex].value, \'' . session_id() . '\'); location.reload();') . '</b></p>';
                        }

                        // Set the employee type as manager
                        $employeeType='MANAGER';

                        // Break out of this case statement
                        break;
                    case 'MID-MANAGEMENT' :
                        // Set the color to be purpleish
                        $levelCellColor='#8b62cf';

                        // Determine if they can modify this user
                        if ($userRow["UserLevel"]=='SU' || 
                            $userRow["UserLevel"]=='EXECUTIVE' ||
                            $userRow["UserLevel"]=='MANAGEMENT' ||
                            $userRow["UserLevel"]=='MID-MANAGEMENT'){
                            // Set the html to allow them to edit it
                            $clockedInStatusHTML='<p class="standardFontSize ' . $extraClockInClass . '"><b>' . getLaborCategoryAsSelectOptions($db, $userRow, $row["LaborCategoryID"], $which, ('background-color: ' . $row["LCRowColor"] . '; border-color: ' . $row["LCRowColor"] . ';'), 'javascript: changeUserClockStatus(' . $row["ClockID"] . ', this.options[this.selectedIndex].value, \'' . session_id() . '\'); location.reload();') . '</b></p>';
                        }

                        // Break out of this case statement
                        break;
                    case 'TYPICAL' :
                        // Set the color to be orangeish
                        $levelCellColor='#e9a82f';

                        // Determine if they can modify this user
                        if ($userRow["UserLevel"]=='SU' || 
                            $userRow["UserLevel"]=='EXECUTIVE' ||
                            $userRow["UserLevel"]=='MANAGEMENT' ||
                            $userRow["UserLevel"]=='MID-MANAGEMENT' ||
                            $userRow["UserLevel"]=='TYPICAL'){
                            // Set the html to allow them to edit it
                            $clockedInStatusHTML='<p class="standardFontSize ' . $extraClockInClass . '"><b>' . getLaborCategoryAsSelectOptions($db, $userRow, $row["LaborCategoryID"], $which, ('background-color: ' . $row["LCRowColor"] . '; border-color: ' . $row["LCRowColor"] . ';'), 'javascript: changeUserClockStatus(' . $row["ClockID"] . ', this.options[this.selectedIndex].value, \'' . session_id() . '\'); location.reload();') . '</b></p>';
                        }

                        // Break out of this case statement
                        break;
                    case 'MINIMAL' :
                        // Set the color to be orangeish
                        $levelCellColor='#c72de6';

                        // Determine if they can modify this user
                        if ($userRow["UserLevel"]=='SU' || 
                            $userRow["UserLevel"]=='EXECUTIVE' ||
                            $userRow["UserLevel"]=='MANAGEMENT' ||
                            $userRow["UserLevel"]=='MID-MANAGEMENT' ||
                            $userRow["UserLevel"]=='TYPICAL' ||
                            $userRow["UserLevel"]=='MINIMAL'){
                            // Set the html to allow them to edit it
                            $clockedInStatusHTML='<p class="standardFontSize ' . $extraClockInClass . '"><b>' . getLaborCategoryAsSelectOptions($db, $userRow, $row["LaborCategoryID"], $which, ('background-color: ' . $row["LCRowColor"] . '; border-color: ' . $row["LCRowColor"] . ';'), 'javascript: changeUserClockStatus(' . $row["ClockID"] . ', this.options[this.selectedIndex].value, \'' . session_id() . '\'); location.reload();') . '</b></p>';
                        }

                        // Break out of this case statement
                        break;
                    case 'TIMECARD ONLY' :
                        // Set the color to be orangeish
                        $levelCellColor='#FF007F';

                        // Determine if they can modify this user
                        if ($userRow["UserLevel"]=='SU' || 
                            $userRow["UserLevel"]=='EXECUTIVE' ||
                            $userRow["UserLevel"]=='MANAGEMENT' ||
                            $userRow["UserLevel"]=='MID-MANAGEMENT' ||
                            $userRow["UserLevel"]=='TYPICAL' ||
                            $userRow["UserLevel"]=='MINIMAL' ||
                            $userRow["UserLevel"]=='TIMECARD ONLY'){
                            // Set the html to allow them to edit it
                            $clockedInStatusHTML='<p class="standardFontSize ' . $extraClockInClass . '"><b>' . getLaborCategoryAsSelectOptions($db, $userRow, $row["LaborCategoryID"], $which, ('background-color: ' . $row["LCRowColor"] . '; border-color: ' . $row["LCRowColor"] . ';'), 'javascript: changeUserClockStatus(' . $row["ClockID"] . ', this.options[this.selectedIndex].value, \'' . session_id() . '\'); location.reload();') . '</b></p>';
                        }

                        // Set the employee type as trial
                        $employeeType='TRIAL';

                        // Break out of this case statement
                        break;
                    default:
                        // Set the color to be redish
                        $levelCellColor='#fc0fc0';

                        // Break out of this case statement
                        break;
                }
                
                // Add this user to the list
                $order=1;
                $result = $result .
                    '<li id="User_' . $order . '" class="ui-state-default Table" >
                        <div class="Row" style="background-color: '. $rowColor . '; border-style: solid; border-color: '. $rowColor . ';">
                            <div class="cell standardFontSize trialCell" style="background-color: '. $trialColor . ';" Title="' . $timeAtCompany . '">
                                <p>' . $trialCell . '</p>
                            </div>
                            <div class="cell standardFontSize nameCell">
                                <p>' . $row["LastName"] . '</p>
                            </div>
                            <div class="cell standardFontSize nameCell">
                                <p>' . $row["FirstName"] . '</p>
                            </div>
                            <div class="cell standardFontSize levelCell" style="background-color: '. $levelCellColor . '; border-style: solid;">
                                <p>' . $employeeType . '</p>
                            </div>
                            <div class="cell standardFontSize dateTimeCell">
                                <p>' . $timeCell . '</p>
                            </div>
                            <div class="cell standardFontSize clockInStatusCell" style="background-color: ' . $row["LCRowColor"] . ';">
                                ' . $clockedInStatusHTML . '
                            </div>
                            <div class="cell standardFontSize durationCell ' . $extraClockInClass . '" Title="'. $statusSpanTitle .'">
                                <p><b>' . $durationString . '</b></p>
                            </div>
                            <div class="cell standardFontSize weekHoursCell">
                                <table align="center">
                                    <tr>
                                        <td style="font-size: 11px; border: 1px solid black;' . $week1CSS . '" align="center" title="' . $week1Title . '" width="50"><b>' . $week1Hours . '</b></td>
                                        <td style="font-size: 11px; border: 1px solid black;' . $week2CSS . '" align="center" title="' . $week2Title . '" width="50"><b>' . $week2Hours . '</b></td>
                                    </tr>
                                </table>
                            </div>
                        </div>
                    </li>';
            }

        } catch (Exception $ex) {
            // Assign the output string as to what happened
            return $result;
        }

        // Return whatever we got
        return $result;
    }
?>
