[OPT-CVS] opt/calendar/timesheets/reports banked_hours_summary.php,NONE,1.1

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 454-5900

Update of /cvsroot/outreach/opt/calendar/timesheets/reports
In directory sc8-pr-cvs1:/tmp/cvs-serv4499/reports

Added Files:
	banked_hours_summary.php 
Log Message:
Added functionality to track banked hours (if using "Time Off in Lieu of OT" as a Timesheet Non-project Reason).

--- NEW FILE: banked_hours_summary.php ---
<?
#
# Displays a quick time summary for a persons banked time over a time period (default to calendar year).
#

include "../../../include/OPT_lib.php";
auth_open();

?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" "http://www.w3.org/TR/REC-html40/loose.dtd">
<HTML>
<HEAD>
<TITLE><? echo(__("Calendar")); ?></TITLE>
<LINK REL=STYLESHEET TYPE="text/css" HREF="../../../include/opt_css.php">
<? echo $_OPT["head_xtra"]; ?>
</HEAD>

<?
mainBody("../../images"); 

$reasons = explode("|", get_setting("timesheet_nonproj_reasons"));

for ($i=0; $i<sizeOf($reasons); $i++)
{
  if ("Time Off in Lieu of OT" === $reasons[$i])
  {
    $disp_ok = 1;
  }
}

if (!$disp_ok)
{
  OPT_error("You are not authorized to view this page.");
  exit();
}
else
{
  if (!$person)
  {   // PM's and payroll contacts can see other people's info, but regular users just see themselves
    $person = $CRM_user;
  }
  else
  {
    $person = abs($person);
  }
  $fullname = getvalue("people", "CONCAT(fname,' ',lname)", $person);
  make_main_title(__("Banked Hours Summary for $fullname"),"../../../images");

  ?>
  <script language="JavaScript" src="../../absences/absence_create/ts_picker.js"></script>
  <form style="margin:0;" method="POST" action="banked_hours_summary.php">
  <?
  startTables();
  ?>
  <tr>
  <td class='regular' align='center' bgcolor='<?echo($OPT_col_dk);?>' colspan='2'>
  <i>Please set the dates in the form below to see a quick banked hours time summary for the period.</i></td>
  </tr>

  <tr>
  <td class='regular' bgcolor='<?echo($OPT_col_md);?>' width="36%" align='right'>
  <b>Report Period: </b><div class='small'></div></td>

  <td class='regular' bgcolor='<?echo($OPT_col_lt);?>' align='left' valign="middle">

  <?

  if (!$start_date)
  {
    $sql="select value from user_settings where person = $CRM_user and type='banked_hours_display' and name='start_date'";
    $result=CRM_run_query($sql);    
    if (OPT_num_rows($result) == 1)
    {
      $row=OPT_fetch_row($result);
      $start_date = $row[0];
    }
    else if (!$start_date)
    {
      $start_date = date("Y-01-01");
    }
  }
  ?><input type="text" name="start_date" size="12" value="<? echo($start_date)?>"> 
  <a href="javascript:show_calendar('document.forms[0].start_date', document.forms[0].start_date.value);">
  <img src="../../../images/cal.gif" width="16" height="16" border="0" alt="<? echo(__("Click Here to Pick a Start Date")); ?>">
  </a>
  &nbsp;&nbsp;&nbsp;
  through
  &nbsp;&nbsp;&nbsp;
  <?
  if (!$end_date)
  {
    $sql="select value from user_settings where person = $CRM_user and type='banked_hours_display' and name='end_date'";
    $result=CRM_run_query($sql);
    if (OPT_num_rows($result) == 1)
    {
      $row=OPT_fetch_row($result);
      $end_date = $row[0];
    }
    else if (!$end_date)
    {
      $end_date = date("Y-12-31");
    }
  }
  ?>
  <input type="text" name="end_date" size="12" value="<? echo($end_date)?>"> 
  <a href="javascript:show_calendar('document.forms[0].end_date', document.forms[0].end_date.value);">
  <img src="../../../images/cal.gif" width="16" height="16" border="0" alt="<? echo(__("Click Here to Pick a End Date")); ?>">
  </a></td>
  </tr>

  <? 
  echo("<tr>");
  echo("<td class='regular' align='center' bgcolor='$OPT_col_dk' colspan='2'>");
  ?>

  <input type="submit" name="show_report" value="Display Report"></td>
  </tr>

  <?
  endTables();
  ?>
  <input type='hidden' name='person' value='<?echo($person)?>'>
  </form>

  <?

  if ($show_report)
  {
    echo("<br />");

    // Ensure that we have both a start and end date and groups
    if (!$start_date || !$end_date || !$person)
    {
      OPT_error("Please provide a valid time period, and user.");
      exit();
    } 

    // Dates must make sense
    if (strtotime($start_date) > strtotime($end_date))
    {
      OPT_error(__("The start date can not be later than the end date!"));
      exit();
    } 

    // Save the form submission parameters with a single query
    crm_run_query("delete from user_settings where person = $CRM_user and type='banked_hours_display'");
    $sql =  "insert into user_settings (person, type, name, value) values ";
    $sql .= "($CRM_user, 'banked_hours_display', 'start_date', '".OPT_gpc_slashes($start_date)."'),";
    $sql .= "($CRM_user, 'banked_hours_display', 'end_date', '".OPT_gpc_slashes($end_date)."')";
    CRM_run_query($sql);  // save user's preferences...
    startTables();

    $tot_hours = calcTotalHours($start_date, $end_date, $person);
    $tot_days = $tot_hours/8.0;
    $tot_hours_off_OT=calcHoursOffOT($start_date, $end_date, $person);
    $tot_days_off_OT = $tot_hours_off_OT/8.0;
    $hours_should_work = calcHoursPerPeriod($start_date, $end_date, $start_date, $end_date);
    $days_should_work = $hours_should_work/8.0;
    $tot_worked_hours = $tot_hours - $tot_hours_off_OT;
    $tot_worked_days = $tot_worked_hours/8.0;
    $banked_hours = $tot_worked_hours - $hours_should_work;
    $banked_days = $banked_hours/8.0;

    if (($tot_hours == 0)  &&  ($tot_hours_off_OT == 0) && ($tot_worked_hours == 0))
    {
      echo("<tr><td class='attention' align='center' bgcolor='$OPT_col_lt' colspan='5'>No timesheet entries found.</td></tr>");
    }
    else
    {   
      echo("<tr>");
      echo("<td class='regular' align='center' bgcolor='$OPT_col_md'><b>Banked Hours Summary</b></td>");
      echo("<td class='regular' align='center' bgcolor='$OPT_col_md'><b>Hours</b></td>");
      echo("<td class='regular' align='center' bgcolor='$OPT_col_md'><b>Days</b></td>");
      echo("</tr>");

      echo("<tr>");
      echo("<td class='regular' align='left' bgcolor='$OPT_col_dk'><b>Total Time Recorded in Timesheet</b></td>");
      echo("<td class='regular' align='center' bgcolor='$OPT_col_dk'><b>".sprintf("%.1f",$tot_hours)."</b></td>");
      echo("<td class='regular' align='center' bgcolor='$OPT_col_dk'><b>".sprintf("%.1f",$tot_days)."</b></td>");
      echo("</tr>");

      echo("<tr>");
      echo("<td class='regular' align='left' bgcolor='$OPT_col_dk'><b>Time recorded in Timesheet as Time Off in Lieu of OT</b></td>");
      echo("<td class='regular' align='center' bgcolor='$OPT_col_dk'><b>".sprintf("%.1f",$tot_hours_off_OT)."</b></td>");
      echo("<td class='regular' align='center' bgcolor='$OPT_col_dk'><b>".sprintf("%.1f",$tot_days_off_OT)."</b></td>");    
      echo("</tr>");

      echo("<tr>");
      echo("<td class='regular' align='left' bgcolor='$OPT_col_dk'><b>Total Time you should have worked in the time period</b></td>");
      echo("<td class='regular' align='center' bgcolor='$OPT_col_dk'><b>".sprintf("%.1f",$hours_should_work)."</b></td>");
      echo("<td class='regular' align='center' bgcolor='$OPT_col_dk'><b>".sprintf("%.1f",$days_should_work)."</b></td>");    
      echo("</tr>");

      echo("<tr>");
      echo("<td class='regular' align='left' bgcolor='$OPT_col_dk'><b>Total Time Recorded minus Time Off in Lieu of OT</b></td>");
      echo("<td class='regular' align='center' bgcolor='$OPT_col_dk'><b>".sprintf("%.1f",$tot_worked_hours)."</b></td>");
      echo("<td class='regular' align='center' bgcolor='$OPT_col_dk'><b>".sprintf("%.1f",$tot_worked_days)."</b></td>");    
      echo("</tr>");

      echo("<tr>");
      echo("<td class='regular' align='left' bgcolor='$OPT_col_md'><b>Banked Time</b></td>");
      if ($banked_hours < 0)
      {
	$abs_banked_hours = abs($banked_hours);
	$abs_banked_days = abs($banked_days);
	echo("<td class='attention' align='center' bgcolor='$OPT_col_md'>".sprintf("%.1f",$abs_banked_hours)."</td>");
	echo("<td class='attention' align='center' bgcolor='$OPT_col_md'>".sprintf("%.1f",$abs_banked_days)."</td>");      
      }
      else
      {
	echo("<td class='att_green' align='center' bgcolor='$OPT_col_md'>".sprintf("%.1f",$banked_hours)."</td>");
	echo("<td class='att_green' align='center' bgcolor='$OPT_col_md'>".sprintf("%.1f",$banked_days)."</td>");      
      }
      echo("</tr>");  
    }

    //  endTables();

    //echo("</br>");
    //startTables();
    echo("<tr>");
    echo("<td colspan='3' class='regular' align='center' bgcolor='$OPT_col_lt'><i>If the banked time is in red, you have to make up that much time.</i></td>");
    echo("</tr>");
    echo("<tr>");
    echo("<td colspan='3' class='regular' align='center' bgcolor='$OPT_col_lt'><i>If the banked time is in green, you have that much time banked already.</i></td>");
    echo("</tr>");
    endTables();
  }  // end of if show_report

  auth_close();
}
  ?>
  </form>
  </BODY>
  </HTML>

<? // Utility methods

// Show all the hours recorded for tasks on this day by this person
function calcTotalHours($start_date, $end_date, $person)
{

  // Now get hours worked on tasks
  $sql="select SUM(TIME_TO_SEC(tph.worked))/3600
        from task_progress_history as tph, task as t, projects as p
        where tph.taskID=t.id and tph.worked > 0 and p.id = t.ProjectID
              and tph.person=$person and tph.date >= '$start_date'
              and DATE_ADD('$end_date', INTERVAL 1 DAY) > tph.date";

  $result=CRM_run_query($sql);
  while ($row=OPT_fetch_row($result))
  {
    $hours += $row[0];
  }

  // Also include all miscellaneous time for this person/day (reason = -1 for misc)
  $sql="select SUM(worked) from misc_time where person=$person and day >= '$start_date'
        and day <  DATE_ADD('$end_date', INTERVAL 1 DAY)";

  $misc_result=CRM_run_query($sql);
  while ($misc_row=OPT_fetch_row($misc_result))
  {
    $hours += $misc_row[0];
  }

  // Now get all absences that fall inside this time period as well
  // Absences are normally extended periods of time...sometimes may be a few hours too
  $sql="select id, DATE_FORMAT(start_date, '%Y-%m-%d %H:%i'), DATE_FORMAT(end_date, '%Y-%m-%d %H:%i'), 
               notes from absences 
        where person = ".abs($person). " and end_date > '".OPT_gpc_slashes($start_date)."' 
        and start_date <= DATE_ADD('".OPT_gpc_slashes($end_date)."', INTERVAL 1 DAY) 
        and count_on_timesheet = 1 order by start_date";
  $absence_result = CRM_run_query($sql);
  $absence_row = OPT_fetch_row($absence_result);

  while ($absence_row)
  {
    if ($absence_row)
    {
      $absence_hours = calcHoursPerPeriod($absence_row[1], $absence_row[2], 
					  $start_date, $end_date);
      $total_absence_hours += $absence_hours;
    }

    $absence_row = OPT_fetch_row($absence_result);
  }
  $hours += $total_absence_hours;

  return($hours);
}

// Show all the hours recorded for non_proj time on this day by this person
function calcHoursOffOT($start_date, $end_date, $person)
{
  global $reasons;

  // Now get the time that has to be taken off for Time Off In Lieu Of OT
  for ($i=0; $i<sizeOf($reasons); $i++)
  {
    if ("Time Off in Lieu of OT" === $reasons[$i])
    {
      $time_off = $i;
    }
  }

  // First get sum of non proj time entered on timesheets
  $sql="select sum(worked) from misc_time where person = $person and '$start_date' <= day 
          and day < DATE_ADD('$end_date', INTERVAL 1 DAY) and reason = $time_off";
  $result = CRM_run_query($sql);
  $row = OPT_fetch_row($result);
  $OT_time_off += $row[0];

  // We loop on each day from start to end calcing hours along the way
  $start = strtotime($start_date);
  $end = strtotime($end_date+86400);
  for ($date=$start; $date < $end; $date+=86400)
  {
    $weekday = date("w", $date);
    if (($weekday != 0) && ($weekday != 6))
    {
      $sql="select UNIX_TIMESTAMP(start_date), UNIX_TIMESTAMP(end_date)
                      from absences where person=$person and start_date <= 
                      DATE_ADD(FROM_UNIXTIME($date), INTERVAL 1 DAY) and end_date > FROM_UNIXTIME($date)  
                      and reason=$time_off and count_on_timesheet = 1";
      $result=CRM_run_query($sql);
      while($row=OPT_fetch_row($result))
      {
	// Need to figure out how many hours to count
	$strdate=date("Y-m-d", $date);
                $noon = strtotime("$strdate 12:00:00");
	$onepm = strtotime("$strdate 13:00:00");
	$eightam = strtotime("$strdate 08:00:00");
	$fivepm = strtotime("$strdate 17:00:00");
	if ($row[0] < $eightam)
	{
	  $row[0] = $eightam;
	}
	if ($row[1] > $fivepm)
	{
	  $row[1] = $fivepm;
	}
	$secs=$row[1] - $row[0];

	if (($row[0] <= $noon) && ($row[1] > $onepm))
	{
	  $secs -= 60*60; // minus one hour for lunch
	}
	if ($secs > (8 * 60 *60))
	{
	  $secs = 8 * 60 * 60;
	}
	$OT_time_off += ($secs/3600); 
      }
    }
  }
  return($OT_time_off);
}

// Calculates how many days are spent during an absence that falls within a 
// time period (i.e. 1 year)
function calcHoursPerPeriod($abs_start, $abs_end, $period_start, $period_end)
{
  // Determine when the absence starts within this period
  if (strtotime($abs_start) > strtotime($period_start))
  {
    $start = strtotime($abs_start);
  }
  else
  {
    $start = strtotime($period_start);
  }
  $loop_start = strtotime(date("Y-m-d", $start));

  // Determine when the absence ends within this period
  if (strtotime($abs_end) < strtotime($period_end))
  {
    $end = strtotime($abs_end);
  }
  else
  {
    $end = strtotime($period_end);
  }
  $loop_end = strtotime(date("Y-m-d", $end)) + 86400;
  // Now loop for each day counting hours
  for ($date=$loop_start; $date < $loop_end; $date+=86400)
  {
    $weekday = date("w", $date);
    if (($weekday != 0) && ($weekday != 6))
    {
      // Need to figure out how many hours to count
      $strdate=date("Y-m-d", $date);
      $noon = strtotime("$strdate 12:00:00");
      $onepm = strtotime("$strdate 13:00:00");
      $eightam = strtotime("$strdate 08:00:00");
      $fivepm = strtotime("$strdate 17:00:00");
      if ($start < $eightam)
      {
	$tmp_start = $eightam;
      }
      else
      {
	$tmp_start = $start;
      }

      if ($loop_end > $fivepm)
      {
	$tmp_end = $fivepm;
      }
      else
      {
	$tmp_end = $loop_end;
      }

      $secs=$tmp_end - $tmp_start;

      if (($tmp_start <= $noon) && ($tmp_end > $onepm))
      {
	$secs -= 60*60; // minus one hour for lunch
      }

      if ($secs > (8 * 60 *60))
      {
	$secs = 8 * 60 * 60;
      }

      $abs_hours += ($secs/3600);
    }
  }

  //echo("Returning $abs_hours from the function</br>");
  return ($abs_hours);
}

?>