/*****************************************************************************
*
* Filename         - $Workfile:   calc_stats.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 calc_stats.
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1998
*
*****************************************************************************/

/*
 * calc_stats stored procedure
 * This procedure will generate statistics about each user's
 * loading operations between the supplied start and end times.
 * The statistics are recorded in the stats table.
 * It is designed to be called once per hour and will generate
 * stats records for the given date and hour, removing any which
 * already exist.
 */

PROMPT create or replace procedure calc_stats

create or replace procedure calc_stats (
		start_time  in date,
		end_time    in date
)
as
	norm_bag_count      number;
	crew_bag_count      number;
	gate_bag_count      number;
	rush_bag_count      number;
	mail_bag_count      number;
	dipl_bag_count      number;
	misc_bag_count      number;
	first_pass          number;

	sample_date         date;
	sample_hour         char(2);
	login_name          tagtrak_users.loginname%TYPE;
	old_login_name      tagtrak_users.loginname%TYPE;
	flt_no              flight.flight_no%TYPE;
	old_flt_no          flight.flight_no%TYPE;
	flt_date            flight.flight_date%TYPE;
	old_flt_date        flight.flight_date%TYPE;
	bag_excep           bag.bag_exception%TYPE;
	bag_src             bag.bag_source_indicator%TYPE;
	old_bag_src         bag.bag_source_indicator%TYPE;
  depcode             site_info.airport_code%type;

	cursor get_stats_details_csr is
		select
			u.loginname,
			f.flight_no,
			f.flight_date,
			b.bag_exception,
			b.bag_source_indicator
		from
			bag_trace     bt,
			flight        f,
			tagtrak_users u,
			bag           b
		where
			bt.bag_trace_time between start_time and end_time    and
			bt.trace_action IN ('LOAD','LOAD WHERE','MWS_LOAD')  and	-- UAS/FALCP/CA/00699
			b.loaded_status = 'Y'                                and
			( bt.error_id   = 0 or bt.error_id is null )         and
			f.flight_id     = bt.flight_id_scan                  and
			u.user_id       = bt.user_id                         and
			b.pax_id        = bt.pax_id                          and
			b.bag_tag3      = bt.bag_tag3                        and
			((NVL(b.bag_tag2a,'?') = NVL(bt.bag_tag2a,'!')) or
			 (NVL(b.bag_tag2b,'?') = NVL(bt.bag_tag2b,'!')))
		order by
			u.loginname, f.flight_no, f.flight_date, b.bag_source_indicator,
			b.bag_exception;
			

begin

	/* Initialise Variables */
	norm_bag_count := 0;
	crew_bag_count := 0;
	gate_bag_count := 0;
	rush_bag_count := 0;
	mail_bag_count := 0;
	dipl_bag_count := 0;
	misc_bag_count := 0;
	first_pass     := 0;
	old_login_name := '';
	old_flt_no     := '';
	old_flt_date   := '';
	bag_src        := '';
	old_bag_src    := '';


	/*
	 * The end time must be equal to or later than the start time.
	 */
	if (start_time > end_time) then
		raise_application_error(-20035,'One or more non-valid parameters given');
	end if;

	/*
	 * We can derive the date and hour values from the
	 * supplied start_time value.
	 */
	sample_date := start_time;
	sample_hour := to_char(start_time,'HH24');

	/*
	 * This procedure is supposed to be called only once per hour,
	 * but to cope with emergency situations we will remove any stats
	 * generated by a previous call this hour.
	 */
	delete from
		stats
	where
		trunc(date_time) = trunc(sample_date) and
		hour = sample_hour;


	/* Loop to calculate the statistics */
	begin

		open get_stats_details_csr;

		loop

			fetch get_stats_details_csr into
				login_name,
				flt_no,
				flt_date,
				bag_excep,
				bag_src;
   
        depcode := multi_site.get_loginname_loc(login_name);

			exit when get_stats_details_csr%notfound;

			/* Check if its the first time in the loop, if it is, then set */
			/* up the relevant variables.                                  */
			if (first_pass = 0) then
				first_pass := 1;

				/* Reinitialise all variables for a new batch */
				norm_bag_count := 0;
				crew_bag_count := 0;
				gate_bag_count := 0;
				rush_bag_count := 0;
				mail_bag_count := 0;
				dipl_bag_count := 0;
				misc_bag_count := 0;

				old_login_name := login_name;
				old_flt_no     := flt_no;
				old_flt_date   := flt_date;
				old_bag_src    := bag_src;
			end if;

			if (login_name != old_login_name or
					flt_no     != old_flt_no     or
					flt_date   != old_flt_date   or
					bag_src    != old_bag_src)  then
				/* Right, insert a record into the stats table */
				insert into
					stats (
						date_time,
						hour,
						login_name,
						flight_no,
						flight_date,
						bag_source,
            airport_code,
						normal,
						gate,
						crew,
						rush,
						mail,
						dipl,
						misc
					) values (
						sample_date,
						sample_hour,
						old_login_name,
						old_flt_no,
						old_flt_date,
						old_bag_src,
            depcode,
						norm_bag_count,
						gate_bag_count,
						crew_bag_count,
						rush_bag_count,
						mail_bag_count,
						dipl_bag_count,
						misc_bag_count
					);

				/* Reinitialise all variables for a new batch */
				norm_bag_count := 0;
				crew_bag_count := 0;
				gate_bag_count := 0;
				rush_bag_count := 0;
				mail_bag_count := 0;
				dipl_bag_count := 0;
				misc_bag_count := 0;

				old_login_name := login_name;
				old_flt_no     := flt_no;
				old_flt_date   := flt_date;
				old_bag_src    := bag_src;
			end if;

			/* Work out the bag count exceptions */
			if (bag_excep = 'RUSH') then
				rush_bag_count := rush_bag_count + 1;
			elsif (bag_excep = 'CREW') then
				crew_bag_count := crew_bag_count + 1;
			elsif (bag_excep = 'GATE') then
				gate_bag_count := gate_bag_count + 1;
			elsif (bag_excep = 'MAIL') then
				mail_bag_count := mail_bag_count + 1;
			elsif (bag_excep = 'DIPL') then
				dipl_bag_count := dipl_bag_count + 1;
			elsif (nvl(bag_excep, '-') = '-') then
				norm_bag_count := norm_bag_count + 1;
			else
				misc_bag_count := misc_bag_count + 1;
			end if;

		end loop;
		close get_stats_details_csr;
	end;

	/* Cater for the last record */
	if (first_pass = 1) then
		/* Right, insert a record into the stats table */
		insert into
			stats (
				date_time,
				hour,
				login_name,
				flight_no,
				flight_date,
				bag_source,
        airport_code,
				normal,
				gate,
				crew,
				rush,
				mail,
				dipl,
				misc
			) values (
				sample_date,
				sample_hour,
				old_login_name,
				old_flt_no,
				old_flt_date,
				old_bag_src,
        depcode,
				norm_bag_count,
				gate_bag_count,
				crew_bag_count,
				rush_bag_count,
				mail_bag_count,
				dipl_bag_count,
				misc_bag_count
			);
	end if;

end calc_stats;
/
