/*****************************************************************************
*
* Filename         - $Workfile:   stats_last_hour.sql  $
*
*                     Name              Date      Version     CR Number
* Author           - See revision history below.
* Last Update      - See revision history below.
*
* Project Name     - TAGTRAK(Oracle database)
*
* Source Code
* Identifier       - 0/3/300/8010/112
*
* Design Spec
* Section          - 
*
* Contents         - Stored procedure stats_last_hour
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1998
*
*****************************************************************************/
/****************************************************************************
* @(#)$Workfile:   stats_last_hour.sql  $  $Revision:   1.2  $
*
* Revision History
*
* Note that user tagtrak is many users. For these users the user
* name is entered as the last line in the change description.
*
* $Log:   /pvcsarch/db_ora/db/source/live/procedures/stats_last_hour.svl  $
* 
*    Rev 1.2   20 Sep 1999 14:42:08   tagtrak
*  
* 
*    Rev 1.1   02 Apr 1998 19:38:08   tagtrak
* OR:
* CR:
* REV:
* Initial Issue
* See label for concession
* 
*    Rev 1.0   26 Feb 1998 10:22:56   tagtrak
* OR:
* CR:
* REV:
* Initial core product version
*
****************************************************************************/

/*
 * stats_last_hour stored procedure
 * Generate loading stats for the LAST WHOLE hour preceding the
 * current time - eg at 10:02 this will be stats for 09:00:00 - 09:59:59
 * If a non-null parameter is given , this will be treated as the current
 * time for the purposes of the stats generation, allowing catch-up of
 * missed stats if required.
 */

PROMPT create or replace procedure stats_last_hour

create or replace procedure stats_last_hour (
	target_time   in date,
  airport_code  in varchar2
)

as
	generation_time date;
	start_time date;
	end_time date;

begin

	/*
	 * Use either the current time, or the parameter value if given.
	 */
	if ( target_time is null ) then
		generation_time := sysdate + get_offset.airport_code(airport_code);
	else
		generation_time := target_time;
	end if;

	/*
	 * Start time is:
	 * Generation time truncated to hour, minus one hour.
	 */
	start_time := trunc(generation_time,'HH24') - 1/24;

	/*
	 * End time is:
	 * Generation time truncated to hour, minus one second.
	 */
	end_time := trunc(generation_time,'HH24') - 1/86400;

	/*
	 * Calculate and record stats.
	 */

	calc_stats( start_time, end_time);

	/*
	 * Record this generation in the log table.
	 */
	add_log_rec(
		'STATS FOR AIRPORT' ||' '||airport_code,
		'Generated with start time '||
		to_char(start_time, 'dd-Mon-yyyy hh24:mi:ss')||
		' and end time '||
		to_char(end_time, 'dd-Mon-yyyy hh24:mi:ss'),
		NULL
	);

end;
/
