!H --------------------------------------------------------------------------
!H
!H Version : 1.0.0 06/12/1998
!H
!H Script : glxlib.inc
!H
!H Purpose : Generic routines used by all Galaxy/Eclipse SQR report programs.
!H This library only contains local procedures that accept parameters
!H by value and return results with parameters passed by reference.
!H A colon (:) in front of the parameter name implies that it is
!H passed by reference.
!H
!H Library : The following procedures are in this library
!H
!H Add-One-Day
!H Add-One-Hour
!H Add-Eight-Hours
!H Build-Group-Tags-Name
!H Establish-Log-Report-Dates
!H First-Of-Month
!H Get-Current-Date-Time
!H Get-Parameters
!H Get-Table-Name
!H Get-Tag-DataType
!H Get-Tag-ID
!H Get-Tag-Info
!H Get-Tag-Name
!H Get-Tag-Snap-Avg
!H Get-Tag-Type
!H Get-Tag-Owner-Info
!H Get-Title
!H Get-Title-From-File
!H Obtain-Tag-Values
!H Print-Log-Current-Data
!H Print-Log-Date-Column
!H Print-Log-Dates
!H Print-Log-Header
!H Print-Log-Tag-Values
!H Process-Log-Tag
!H Select-Average
!H Select-Average-Average
!H Select-Average-Or-Latest
!H Select-Latest
!H Select-Latest-String
!H Select-Material-Code
!H Select-Single
!H Select-Single-TimeBased
!H Subtract-One-Day
!H Subtract-One-Hour
!H Subtract-Eight-Hours
!H Subtract-Seven-Days
!H Truncate-To-Day
!H Truncate-To-Hour
!H Truncate-To-Month
!H
!R Revision History
!R
!R V1.0.0 Created By James Bischoff 06/12/1998
!R
!H --------------------------------------------------------------------------
!A --------------------------------------------------------------------------
!A Report Constants
!A --------------------------------------------------------------------------
#define csv_marker ',' ! Used to separate CSV's
#define custom_rec_len 110 ! Max input file record length
#define custom_rpt_lines 60 ! Max lines in custom report
#define custom_rpt_cols 90 ! Columns in custom report
#define data_mask_cutoff 100.0 ! For choosing data mask
#define data_mask_large 9999999.99 ! For values >= cutoff
#define data_mask_length 10 ! Length of data masks
#define data_mask_small 9999.99999 ! For values < cutoff
#define date_format_length 20 ! Oracle date format length
#define date_format_mask 'MM/DD/YYYY HH24:MI:SS' ! Oracle date format mask
#define log_cols 9999 ! Columns in all logs
#define log_data_col 27 ! Start column for tag data
#define log_data_line 9 ! Start line for tag data
#define log_lines 9999 ! Max lines in all logs
#define log_max_cols 20 ! Max data columns in log
#define log_tag_col 27 ! Start column for tag header
#define log_tag_line 4 ! Start line for tag header
#define log_tag_rows 3 ! Tag desc rows for tag header
#define log_tag_width 12 ! Column width for tag header
#define reliability_marker '?' ! Appended to data if needed
#define seconds_in_day 86400 ! Seconds in one day
#define seconds_in_hour 3600 ! Seconds in one hour
#define snapshot_flag_lib 'CURRENT' ! TAGPARM1 will contain this
! if snapshot data is used.
!A --------------------------------------------------------------------------
!A Procedure : Add-One-Day
!A Purpose : Increment a date by 1 day.
!A --------------------------------------------------------------------------
Begin-Procedure Add-One-Day(
$input_date,
#input_sys_time,
:$next_day,
:#next_sys_time)
Begin-Select
to_char(to_date($input_date,{date_format_mask}) + 1,
{date_format_mask}) &next_day
from dual
End-Select
move &next_day to $next_day
let #next_sys_time = #input_sys_time + {seconds_in_day}
#debug display 'Add-One-Day : ' noline
#debug display $next_day noline
#debug display ' - ' noline
#debug display #next_sys_time
End-Procedure
!A --------------------------------------------------------------------------
!A Procedure : Add-Eight-Hours
!A Purpose : Increment a date by 8 hours.
!A --------------------------------------------------------------------------
Begin-Procedure Add-Eight-Hours(
$input_date,
#input_sys_time,
:$next_hour,
:#next_sys_time)
begin-select
to_char(to_date($input_date,{date_format_mask}) + (1/3),
{date_format_mask}) &next_hour
from dual
end-select
move &next_hour to $next_hour
let #next_sys_time = #input_sys_time + {seconds_in_hour}
#debug display 'Add-Eight-Hours : ' noline
#debug display $next_hour noline
#debug display ' - ' noline
#debug display #next_sys_time
End-Procedure
!A --------------------------------------------------------------------------
!A Procedure : Add-One-Hour
!A Purpose : Increment a date by 1 hour.
!A --------------------------------------------------------------------------
Begin-Procedure Add-One-Hour(
$input_date,
#input_sys_time,
:$next_hour,
:#next_sys_time)
begin-select
to_char(to_date($input_date,{date_format_mask}) + (1/24),
{date_format_mask}) &next_hour
from dual
end-select
move &next_hour to $next_hour
let #next_sys_time = #input_sys_time + {seconds_in_hour}
#debug display 'Add-One-Hour : ' noline
#debug display $next_hour noline
#debug display ' - ' noline
#debug display #next_sys_time
End-Procedure
!A --------------------------------------------------------------------------
!A Procedure : Build-Group-Tags-Name
!A Purpose : Concatenate the unit name with the group tags suffix to form
!A the complete group tags name. Note that the unit name must
!A be parsed to see if it is in the form plan_unit, which may be
!A the case for PAP and LOU.
!A --------------------------------------------------------------------------
Begin-Procedure Build-Group-Tags-Name(
$unit_name,
$group_tags_suffix
:$group_tags_name)
let #underscore_pos = instr($unit_name,'_',1)
if #underscore_pos
let #unit_name_length = length($unit_name)
extract $group_tags_name from $unit_name #underscore_pos #unit_name_length
else
move $unit_name to $group_tags_name
end-if
concat $group_tags_suffix with $group_tags_name
uppercase $group_tags_name
#debug display 'Build-Group-Tags-Name : ' noline
#debug display $group_tags_name
End-Procedure
!A --------------------------------------------------------------------------
!A Procedure : Establish-Log-Report-Dates
!A Purpose : Set up the start and end date/times and sys times that will
!A be used for the standard log report. This procedure simply
!A truncates the start and end date/time and sys times to the
!A hour or day depending on the result type requested. The start
!A and end select dates are incremented for the daily data since
!A the data is time stamped in the database at a few minutes after
!A midnight on the next day, but the logs are to show the data
!A next to the date it represents (not that it was collected).
!A --------------------------------------------------------------------------
Begin-Procedure Establish-Log-Report-Dates(
$result_type,
$start_date_time,
#start_sys_time,
$end_date_time,
#end_sys_time,
:$start_date_time_truncated,
:#start_sys_time_truncated,
:$end_date_time_truncated,
:#end_sys_time_truncated,
:$start_date_select,
:#start_sys_time_select,
:$end_date_select,
:#end_sys_time_select)
#debug display 'Establish-Log-Report-Dates : Start'
if $result_type = 'DAILY'
Do Truncate-To-Day(
$start_date_time,
#start_sys_time,
$start_date_time_truncated,
#start_sys_time_truncated)
Do Truncate-To-Day(
$end_date_time,
#end_sys_time,
$end_date_time_truncated,
#end_sys_time_truncated)
let $start_date_select = $start_date_time_truncated
let #start_sys_time_select = #start_sys_time_truncated
Do Subtract-One-Day(
$end_date_time_truncated,
#end_sys_time_truncated,
$end_date_select,
#end_sys_time_select)
else
Do Truncate-To-Hour(
$start_date_time,
#start_sys_time,
$start_date_time_truncated,
#start_sys_time_truncated)
Do Truncate-To-Hour(
$end_date_time,
#end_sys_time,
$end_date_time_truncated,
#end_sys_time_truncated)
Do Add-One-Hour(
$start_date_time_truncated,
#start_sys_time_truncated,
$start_date_select,
#start_sys_time_select)
let $end_date_select = $end_date_time_truncated
let #end_sys_time_select = #end_sys_time_truncated
end-if
#debug display 'Establish-Log-Report-Dates : End'
End-Procedure
!A --------------------------------------------------------------------------
!A Procedure : First-Of-Month
!A Purpose : Convert date/time (MM/DD/YYYY HH:MM:SS) and sys time to the
!A first of the month.
!A --------------------------------------------------------------------------
Begin-Procedure First-Of-Month(
$input_date,
#input_sys_time,
:$first_of_month_date_time,
:#first_of_month_sys_time)
extract $month from $input_date 0 2
extract $year from $input_date 6 4
concat $month with $first_of_month_date_time
concat '/01/' with $first_of_month_date_time
concat $year with $first_of_month_date_time
concat ' 00:00:00' with $first_of_month_date_time
let #day = to_number(substr($input_date, 4, 2))
let #hour = to_number(substr($input_date, 12, 2))
let #minute = to_number(substr($input_date, 15, 2))
let #second = to_number(substr($input_date, 18, 2))
let #first_of_month_sys_time = #input_sys_time - ({seconds_in_day}*(#day - 1)) - -
({seconds_in_hour} * #hour) - (60 * #minute) - #second
#debug display 'First-Of-Month : ' noline
#debug display $first_of_month_date_time noline
#debug display ' - ' noline
#debug display #first_of_month_sys_time
End-Procedure
!A --------------------------------------------------------------------------
!A Procedure : Get-Current-Date-Time
!A Purpose : Get the current date and time for report headers. This code
!A replaces the use of the Date-Time() function since this
!A function will be eliminated in future releases.
!A --------------------------------------------------------------------------
Begin-Procedure Get-Current-Date-Time(
:$current_date
:$current_time)
Begin-Select
to_char(sysdate, 'DY MON DD YYYY') ¤t_date
to_char(sysdate, 'HH24:MI:SS') ¤t_time
move ¤t_date to $current_date
move ¤t_time to $current_time
from dual
End-Select
#debug display 'Get-Current-Date-Time : ' noline
#debug display $current_date noline
#debug display ' - ' noline
#debug display $current_time
End-Procedure
!A --------------------------------------------------------------------------
!A Procedure : Get-Parameters
!A Purpose : Get the report parameters. These parameters are the same for
!A all Eclipse reports.
!A --------------------------------------------------------------------------
Begin-Procedure Get-Parameters(
:$group_tags_suffix,
:#start_sys_time,
:#end_sys_time,
:$start_date_time,
:$end_date_time,
:$user_name,
:$result_type,
:$unit_name,
:$output_type,
:$reliability_marker,
:$csv_marker)
input $group_tags_suffix
input $start_sys_time
input $end_sys_time
input $start_date
input $start_time
input $end_date
input $end_time
input $user_name
input $result_type
input $unit_name
input $output_type
input $reliability_flag
let #start_sys_time = to_number($start_sys_time)
let #end_sys_time = to_number($end_sys_time)
let $start_date_time = $start_date || ' ' || $start_time
let $end_date_time = $end_date || ' ' || $end_time
uppercase $user_name
uppercase $result_type
uppercase $unit_name
uppercase $output_type
uppercase $reliability_flag
if $reliability_flag = 'Y'
move {reliability_marker} to $reliability_marker
else
move ' ' to $reliability_marker
end-if
if $output_type='CSV'
move {csv_marker} to $csv_marker
else
move ' ' to $csv_marker
end-if
#debug display '----Begin Input Parameters----'
#debug display 'Group Tags Suffix : ' noline
#debug display $group_tags_suffix
#debug display 'Start Systime : ' noline
#debug display #start_sys_time
#debug display 'End Systime : ' noline
#debug display #end_sys_time
#debug display 'Start Date/Time : ' noline
#debug display $start_date_time
#debug display 'End Date/Time : ' noline
#debug display $end_date_time
#debug display 'User Name : ' noline
#debug display $user_name
#debug display 'Result Type : ' noline
#debug display $result_type
#debug display 'Unit Name : ' noline
#debug display $unit_name
#debug display 'Output Type : ' noline
#debug display $output_type
#debug display 'Reliability Marker : ' noline
#debug display $reliability_marker
#debug display 'CSV Marker : ' noline
#debug display $csv_marker
#debug display '----End Input Parameters----'
End-Procedure
!A --------------------------------------------------------------------------
!A Procedure : Get-Table-Name
!A Purpose : Get the data table name for a tag based on the result type.
!A This procedure may be extended to check for the lab collection
!A system name, and return the appropriate table.
!A --------------------------------------------------------------------------
Begin-Procedure Get-Table-Name(
$result_type,
$tag_owner,
:$table_name)
let $table_name = 'HOURLY_RESULT'
if $tag_owner = 'LAB' ! If LAB data is reported HOURLY, then
let $table_name = 'LAB_RESULT' ! retrieve data from table LAB_RESULT
end-if
if $result_type = 'DAILY'
let $table_name = 'DAILY_RESULT'
end-if
if $result_type = 'CURRENT'
let $table_name = 'CURRENT_RESULT'
end-if
if $result_type = 'MONTHLY'
let $table_name = 'MONTHLY_RESULT'
end-if
#debug display 'Get-Table-Name : ' noline
#debug display $table_name
End-Procedure
!A --------------------------------------------------------------------------
!A Procedure : Get-Tag-ID
!A Purpose : Select TAG_ID from the TAG table using the supplied TAGNAME.
!A --------------------------------------------------------------------------
Begin-Procedure Get-Tag-ID(
$tag_name,
:#tag_id)
move 0 to #tag_id
Begin-Select
TAG_ID &tag_id
move &tag_id to #tag_id
from TAG
where TAG_NAME = $tag_name
End-Select
#debug display 'Get-Tag-ID for : ' noline
#debug display $tag_name noline
#debug display ' [TAG_ID=' noline
#debug display #tag_id noline
#debug display ']'
End-Procedure
!A --------------------------------------------------------------------------
!A Procedure : Get-Tag-Info
!A Purpose : Get Eclipse tag attributes.
!A --------------------------------------------------------------------------
Begin-Procedure Get-Tag-Info(
$tagname,
:$tag_owner,
:$description,
:$std_egu,
:#tag_id)
move '' to $tag_owner
move '' to $description
move '' to $std_egu
move 0 to #tag_id
Begin-Select
coll_sys_name &tag_owner_eclipse
REPLACE(tag_desc, ',', ' ') &description_eclipse
result_uom_name &std_egu_eclipse
tag_id &tag_id_eclipse
move &tag_owner_eclipse to $tag_owner
move &description_eclipse to $description
move &std_egu_eclipse to $std_egu
move &tag_id_eclipse to #tag_id
from tag
where tag_name=$tagname
End-Select
#debug display 'Get-Tag-Info for : ' noline
#debug display $tagname
End-Procedure
!A --------------------------------------------------------------------------
!A Procedure : Get-Tag-Name
!A Purpose : Select TAG_NAME from the TAG table using the supplied TAG_ID.
!A --------------------------------------------------------------------------
Begin-Procedure Get-Tag-Name(
#tag_id,
:$tag_name)
move '' to $tag_name
Begin-Select
TAG_NAME &tag_name
move &tag_name to $tag_name
from TAG
where TAG_ID = #tag_id
End-Select
End-Procedure
!A --------------------------------------------------------------------------
!A Procedure : Get-Tag-Snap-Avg
!A Purpose : Select Snap_Avg_Ind from the TAG
!A table using the supplied TAGNAME.
!A --------------------------------------------------------------------------
Begin-Procedure Get-Tag-Snap-Avg(
$tag_name,
:$snap_avg_ind)
move 'A' to $snap_avg_ind
Begin-Select
SNAP_AVG_IND &snap_avg_ind
move &snap_avg_ind to $snap_avg_ind
from TAG
where TAG_NAME = $tag_name
End-Select
#debug display 'Get-Tag-Snap-Avg for : ' noline
#debug display $tag_name noline
#debug display ' [SNAP_AVG_IND = ' noline
#debug display $snap_avg_ind noline
#debug display ']'
End-Procedure
!A --------------------------------------------------------------------------
!A Procedure : Get-Tag-DataType
!A Purpose : Return the DATA_TYPE_ID from the
!A TAG table for the supplied TAGNAME.
!A --------------------------------------------------------------------------
Begin-Procedure Get-Tag-DataType(
$tag_name,
:#data_type_id)
move 0 to #data_type_id
Begin-Select
data_type_id &data_type_id
move &data_type_id to #data_type_id
from TAG
where TAG_NAME = $tag_name
End-Select
#debug display 'Get-Tag-DataType for : ' noline
#debug display $tag_name noline
#debug display ' [Data_Type_ID=' noline
#debug display #data_type_id noline
#debug display ']'
End-Procedure
!A --------------------------------------------------------------------------
!A Procedure : Get-Tag-Owner-Info
!A Purpose : Get tag owner attributes. If the latest flag is Y, then if
!A no data exists between the start and end date/times, the
!A latest value is obtained. If the reliability flag parameter
!A is Y, then if the relaibility value obtained from a data table
!A is less than the reliability limit obtained here, then a ? is
!A appended to the data on the report.
!A --------------------------------------------------------------------------
Begin-Procedure Get-Tag-Owner-Info(
$tag_owner,
:$latest_flag,
:#reliability_limit)
let $latest_flag = 'N'
Begin-Select
history_by_exception_flag &latest_flag
move &latest_flag to $latest_flag
from collection_system
where coll_sys_name = $tag_owner
End-Select
let $parameter_name = $tag_owner || '_RELIABILITY_LIMIT'
let #reliability_limit = 100
Begin-Select
num_value &reliability_limit
move &reliability_limit to #reliability_limit
from eclipse_configuration
where parameter_name=$parameter_name
End-Select
#debug display 'Get-Tag-Owner-Info for : ' noline
#debug display $tag_owner noline
#debug display ' - ' noline
#debug display $latest_flag noline
#debug display ' - ' noline
#debug display #reliability_limit
End-Procedure
!A --------------------------------------------------------------------------
!A Procedure : Get-Title
!A Purpose : Get report title from tag_groups table.
!A --------------------------------------------------------------------------
Begin-Procedure Get-Title(
$group_tags_name,
:$report_title)
let $report_title = ''
Begin-Select
tag_group_desc &report_title
move &report_title to $report_title
From tag_group
where tag_group_name = $group_tags_name
End-Select
#debug display 'Get-Title : ' noline
#debug display $report_title
End-Procedure
!A --------------------------------------------------------------------------
!A Procedure : Get-Title-From-File
!A Purpose : Get report title from custom log/report file. This procedure
!A assumes that the next record to be read contains the title.
!A --------------------------------------------------------------------------
Begin-Procedure Get-Title-From-File(
#input_file,
:$report_title)
let $report_title = ''
read #input_file into $report_title:{custom_rec_len}
#debug display 'Get-Title-From-File : ' noline
#debug display $report_title
End-Procedure
!A --------------------------------------------------------------------------
!A Procedure : Obtain-Tag-Values
!A Purpose : Select the tags values from the appropriate table
!A and for the appropriate tag_timestamp or time period.
!A --------------------------------------------------------------------------
Begin-Procedure Obtain-Tag-Values(
$result_type,
$latest_flag,
$table_name,
$tagname,
#tag_id,
$tagparm1,
$start_date_time,
$end_date_time,
#start_sys_time,
#end_sys_time,
:$sam_date,
:$sam_time,
:#value,
:$string_value,
:#reliability,
:#rows_selected)
#debug display 'Obtain-Tag-Values: Start - ' noline
#debug display $tagname noline
#debug display ' * ' noline
#debug display #tag_id noline
#debug display ' * ' noline
#debug display $result_type noline
#debug display ' * ' noline
#debug display $latest_flag
!A
!A If this tag owner must present a value (i.e. LAB)
!A get the average or latest
!A otherwise
!A if this is a plant wide report running in Galaxy
!A build the table name based on the tag owner (or tagparm2)
!A if the table is a current table
!A get the current value from the current table
!A otherwise
!A if a snapshot should be retrieved (not an average)
!A get the snapshot
!A otherwise
!A get the average
!A handle the reliability for the average
!A
move '' to $string_value
Do Get-Tag-DataType(
$tagname,
#data_type_id)
if #data_type_id = 4 ! If return value is a Character String ...
if $result_type = 'CURRENT'
Do Select-Single-String(
$table_name,
$tagname,
#tag_id,
$string_value,
$sam_date,
$sam_time,
#rows_selected)
else
Do Select-Latest-String(
$table_name,
$tagname,
#tag_id,
$end_date_time,
$string_value)
end-if
else
if $latest_flag = 'Y'
Do Select-Average-Or-Latest(
$table_name,
$tagname,
#tag_id,
$start_date_time,
$end_date_time,
#start_sys_time,
#end_sys_time,
#value,
#reliability)
else
if $result_type = 'CURRENT'
Do Select-Single(
$table_name,
$tagname,
#tag_id,
#value
$sam_date,
$sam_time,
#rows_selected)
else
if $tagparm1 = {snapshot_flag_lib}
Do Select-Latest(
$table_name,
$tagname,
#tag_id,
$end_date_time,
#value)
else
Do Select-Average(
$table_name,
$tagname,
#tag_id,
$start_date_time,
$end_date_time,
#value,
#reliability,
#rows_selected)
if (#reliability < #reliability_limit)
move $reliability_marker to $reliability
end-if
end-if
end-if
end-if
end-if
#debug display 'Obtain-Tag-Values: End - ' noline
#debug display 'start_date_time=' noline
#debug display $start_date_time noline
#debug display ' * ' noline
#debug display 'end_date_time=' noline
#debug display $end_date_time
!additional information for this run
#debug display 'Obtain-Tag-Values: End - ' noline
#debug display 'data_type_id=' noline
#debug display #data_type_id noline
#debug display ' * ' noline
#debug display 'value=' noline
#debug display #value noline
#debug display ' * ' noline
#debug display 'string_value=' noline
#debug display $string_value
End-Procedure
!A --------------------------------------------------------------------------
!A Procedure : Print-Log-Current-Data
!A Purpose : Print the current value at the bottom of a log value column.
!A --------------------------------------------------------------------------
Begin-Procedure Print-Log-Current-Data(
$tag_owner,
$tagname,
#tag_id,
#data_column,
#current_value_line,
$csv_marker)
#debug display 'Print-Log-Current-Data : Start - ' noline
#debug display $tagname
Do Get-Table-Name(
'CURRENT',
$tag_owner,
$current_table_name)
Do Select-Single(
$current_table_name,
$tagname,
#tag_id,
#value,
$current_sam_date,
$current_sam_time,
#rows_selected)
if #rows_selected = 0
let #no_data_column = #data_column + {data_mask_length}
print $csv_marker (#current_value_line,#no_data_column)
print $csv_marker (+1,#no_data_column)
print $csv_marker (+1,#no_data_column)
else
let #data_column = #data_column - 1
print $csv_marker (#current_value_line,#data_column)
print $current_sam_date (0,0)
print $csv_marker (+1,#data_column)
print $current_sam_time (0,0)
let #data_column = #data_column + 1
if abs(#value) >= {data_mask_cutoff}
print #value (+1,#data_column) edit {data_mask_large}
else
print #value (+1,#data_column) edit {data_mask_small}
end-if
print $csv_marker (0,0)
end-if
#debug display 'Print-Log-Current-Data : End - ' noline
#debug display $tagname
End-Procedure
!A --------------------------------------------------------------------------
!A Procedure : Print-Log-Date-Column
!A Purpose : Print the date column for a log, and return the
!A position for the average and current value lines.
!A --------------------------------------------------------------------------
Begin-Procedure Print-Log-Date-Column(
$start_date_time_truncated,
$end_date_time_truncated,
$csv_marker,
$result_type,
$output_type,
:#average_value_line,
:#current_value_line,
:#data_row_cnt)
#debug display 'Print-Log-Date-Column : Start'
let #log_tag_col_start = {log_tag_col} - 1
print $csv_marker ({log_tag_line},{date_format_length})
print $csv_marker ( ,#log_tag_col_start
print $csv_marker (+1,{date_format_length})
print $csv_marker (+1,{date_format_length})
print $csv_marker (+1,{date_format_length})
print 'DATE' (+1,1)
print $csv_marker (0,11)
print 'TIME' (0,12)
Do Print-Log-Dates(
$start_date_time_truncated,
$end_date_time_truncated,
$csv_marker,
$result_type,
$output_type,
#data_row_cnt)
print ' Period Averages ' (+2,1)
print $csv_marker (0,0)
print $csv_marker (0,0)
move #_current-line to #average_value_line
subtract 1 from #average_value_line
print ' Most Current Values' (+2,1)
print $csv_marker (0,25)
move #_current-line to #current_value_line
subtract 1 from #current_value_line
print $csv_marker (+1,10)
print $csv_marker (+1,10)
print $csv_marker (0,0)
#debug display 'Print-Log-Date-Column : End'
End-Procedure
!A --------------------------------------------------------------------------
!A Procedure : Print-Log-Dates
!A Purpose : Print and increment through the actual dates in a
!A log date column.
!A --------------------------------------------------------------------------
Begin-Procedure Print-Log-Dates(
$start_date_time_truncated,
$end_date_time_truncated,
$csv_marker,
$result_type,
$output_type,
:#data_row_cnt)
#debug display 'Print-Log-Dates : Start'
let #increment_count = 1
let $working_date = $start_date_time_truncated
begin-select
to_date($end_date_time_truncated,{date_format_mask}) - -
to_date($start_date_time_truncated,{date_format_mask}) &number_of_increments
from dual
end-select
move &number_of_increments to #number_of_increments
!A
!A The number of increments variable is in days (i.e. 0.5 = 12 hours)
!A If the time increment is supposed to be in hours, then this
!A is multiplied by 24 to convert from fractions of a day to hours.
!A
if $result_type = 'DAILY'
let #max_increments = #number_of_increments
else
let #max_increments = (24 * #number_of_increments)
Do Add-One-Hour(
$working_date,
#working_sys_time,
$working_date,
#working_sys_time)
end-if
let #data_row_cnt = #max_increments
while 1
let $hour = substr($working_date,12,2)
let $date = substr($working_date,1,10)
let $time = substr($working_date,12,8)
if $result_type = 'DAILY'
let $time = '24:00:00'
move '00' to $hour
end-if
if ($hour != '00') and ($output_type != 'CSV') and #increment_count > 1
move ' ' to $date
end-if
print $date (+1,1)
print $csv_marker (0,0)
print $time (0,0)
print $csv_marker (0,0)
add 1 to #increment_count
if #increment_count > #max_increments
break
else
if $result_type = 'DAILY'
Do Add-One-Day(
$working_date,
#working_sys_time,
$working_date,
#working_sys_time)
else
Do Add-One-Hour(
$working_date,
#working_sys_time,
$working_date,
#working_sys_time)
end-if
end-if
end-while
#debug display 'Print-Log-Dates : End'
end-procedure
!A --------------------------------------------------------------------------
!A Procedure : Print-Log-Header
!A Purpose : Print a tag header for a log, and increment the
!A column header position for next time.
!A --------------------------------------------------------------------------
Begin-Procedure Print-Log-Header(
$csv_marker,
$tagname,
$description,
$std_egu,
:#header_column,
:#tagname_column)
let #csvpos = #header_column - 1
let #log_tag_line_start = {log_tag_line} + 1
print $csv_marker (#log_tag_line_start,#csvpos)
print $csv_marker (+1,#csvpos)
print $csv_marker (+1,#csvpos)
print $csv_marker (+1,#csvpos)
if $csv_marker <> ' '
if #tagname_column = 0
let #tagname_column = #header_column
end-if
print $tagname ({log_tag_line},#tagname_column)
print $csv_marker ()
let #tagname_column = #tagname_column + length($tagname) + 1
else
print $tagname ({log_tag_line},#header_column)
end-if
print $description (+1,#header_column) wrap {log_tag_width} {log_tag_rows}
print $std_egu (+1,#header_column)
let #header_column = #header_column + {log_tag_width} + 1
#debug display 'Print-Log-Header : ' noline
#debug display $tagname
End-Procedure
!A --------------------------------------------------------------------------
!A Procedure : Print-Log-Tag-Values
!A Purpose : Print and increment through the values in a log
!A value column. Also print the average row at the bottom.
!A --------------------------------------------------------------------------
Begin-Procedure Print-Log-Tag-Values(
$result_type,
$tagname,
#tag_id,
$table_name,
$start_date_select,
#start_sys_time_select,
$end_date_select,
#end_sys_time_select,
#average_value_line,
#data_column,
$reliability_marker,
#reliability_limit,
$csv_marker,
#data_row_cnt)
#debug display 'Print-Log-Tag-Values : Start - ' noline
#debug display $tagname noline
#debug display ' : Start_Date_Select: ' noline
#debug display $start_date_select noline
#debug display ' : End_Date_Select: ' noline
#debug display $end_date_select noline
#debug display ' * '
let #data_average = 0
let #data_average_sum = 0
let #data_average_count = 0
let #reliability_average = 0
let #reliability_average_sum = 0
let #reliability_average_count = 0
! Incremenet #cnt from 0 to the #data_row_cnt-1 to obtain the
! proper row for displaying the $cvs_marker prior to print the data value
let #cnt = 0
while #cnt < #data_row_cnt
let #data_row = floor(#cnt) + {log_data_line}
print $csv_marker (#data_row,#data_column)
add 1 to #cnt
end-while
Begin-Select
to_number(to_char(tag_timestamp, 'MI')) &tag_timestamp_minute
to_char(tag_timestamp, {date_format_mask}) &tag_timestamp_str
(tag_timestamp - to_date($start_date_select, -
{date_format_mask})) &increment_from_start
tag_value &std_log_value
reliability &std_log_reliability
move &std_log_value to #value
move &std_log_reliability to #reliability
move &increment_from_start to #increment_from_start
move &tag_timestamp_minute to #tag_timestamp_minute
!A
!A The increment from start variable is in days (i.e. 0.5 = 12 hours)
!A If the time increment is supposed to be in hours, then this
!A is multiplied by 24 to convert from fractions of a day to hours.
!A
if #tag_timestamp_minute = 0
if $result_type = 'DAILY'
let #data_row = floor(#increment_from_start) + {log_data_line}
else
let #data_row = floor(24 * #increment_from_start) + {log_data_line}
end-if
if abs(#value) >= {data_mask_cutoff}
print #value (#data_row,#data_column) edit {data_mask_large}
else
print #value (#data_row,#data_column) edit {data_mask_small}
end-if
if #reliability < #reliability_limit
print $reliability_marker (0,0)
end-if
print $csv_marker (0,0)
add #value to #data_average_sum
add 1 to #data_average_count
add #reliability to #reliability_average_sum
add 1 to #reliability_average_count
end-if
from [current_result:$table_name]
where tag_id = #tag_id and
tag_timestamp between
to_date($start_date_select, {date_format_mask}) and
to_date($end_date_select, {date_format_mask})
End-Select
if #data_average_count != 0
let #data_average = #data_average_sum / #data_average_count
end-if
if #reliability_average_count != 0
let #reliability_average = #reliability_average_sum / #reliability_average_count
end-if
if abs(#data_average) >= {data_mask_cutoff}
print #data_average (#average_value_line,#data_column) edit {data_mask_large}
else
print #data_average (#average_value_line,#data_column) edit {data_mask_small}
end-if
if #reliability_average < #reliability_limit
print $reliability_marker (0,0)
end-if
print $csv_marker (0,0)
#debug display 'Print-Log-Tag-Values : End - ' noline
#debug display $tagname
End-Procedure
!A --------------------------------------------------------------------------
!A Procedure : Process-Log-Tag
!A Purpose : Print a column header and data for a tag.
!A --------------------------------------------------------------------------
Begin-Procedure Process-Log-Tag(
$tagname,
$result_type,
$output_type,
$csv_marker,
$start_date_time_truncated,
$end_date_time_truncated,
$start_date_select,
#start_sys_time_select,
$end_date_select,
#end_sys_time_select
:#header_column,
:#data_column,
:#data_column_count)
#debug display 'Process-Log-Tag : Start - ' noline
#debug display $tagname
if $output_type='CSV'
#debug display 'Process-Log-Tag : Output Type is CSV so no new pages'
let #log_max_cols = 9999
else
#debug display 'Process-Log-Tag : Output Type is NOT CSV so make new pages'
let #log_max_cols = {log_max_cols}
end-if
if mod(#data_column_count, #log_max_cols) = 0
#debug display 'Process-Log-Tag : Column count mod is zero'
move {log_data_col} to #data_column
if #data_column_count > 0
#debug display 'Process-Log-Tag : Update global header column with current value'
#debug display 'Process-Log-Tag : Before new page'
move #header_column to #_header_column
new-page
#debug display 'Process-Log-Tag : After new page'
end-if
#debug display 'Process-Log-Tag : Initialize header column'
move {log_tag_col} to #header_column
Do Print-Log-Date-Column(
$start_date_time_truncated,
$end_date_time_truncated,
$csv_marker,
$result_type,
$output_type,
#average_value_line,
#current_value_line,
#data_row_cnt)
end-if
Do Get-Tag-Info(
$tagname,
$tag_owner,
$description,
$std_egu,
#tag_id)
Do Print-Log-Header(
$csv_marker,
$tagname,
$description,
$std_egu,
#header_column,
#tagname_column)
Do Get-Tag-Owner-Info(
$tag_owner,
$latest_flag,
#reliability_limit)
Do Get-Table-Name(
$result_type,
$tag_owner,
$tablename)
Do Print-Log-Tag-Values(
$result_type,
$tagname,
#tag_id,
$tablename,
$start_date_select,
#start_sys_time_select,
$end_date_select,
#end_sys_time_select,
#average_value_line,
#data_column,
$reliability_marker,
#reliability_limit,
$csv_marker,
#data_row_cnt)
Do Print-Log-Current-Data(
$tag_owner,
$tagname,
#tag_id,
#data_column,
#current_value_line,
$csv_marker)
#debug display 'Process-Log-Tag : End - ' noline
#debug display $tagname
End-Procedure
!A --------------------------------------------------------------------------
!A Procedure : Select-Average
!A Purpose : Get the average value for a single tag between the start and
!A end date/times.
!A --------------------------------------------------------------------------
Begin-Procedure Select-Average(
$table_name,
$tagname,
#tag_id,
$start_date_time,
$end_date_time,
:#value,
:#reliability,
:#rows_selected)
#debug display 'Select-Average : Start - ' noline
#debug display $tagname noline
#debug display ' - ' noline
#debug display $start_date_time noline
#debug display ' - ' noline
#debug display $end_date_time
let #rows_selected = 0
let #reliability = 0
Do Get-Tag-Snap-Avg(
$tagname,
$snap_avg_ind)
if $snap_avg_ind = 'A'
Do Select-Average-Average(
$table_name,
$tagname,
#tag_id,
$start_date_time,
$end_date_time,
#value,
#reliability,
#rows_selected)
else
Do Select-Latest(
$table_name,
$tagname,
#tag_id,
$end_date_time,
#value)
let #reliability = 100
let #rows_selected = 1
end-if
#debug display 'Select-Average : End - ' noline
#debug display $tagname
End-Procedure
!A --------------------------------------------------------------------------
!A Procedure : Select-Average-Average
!A Purpose : Get the average value for a single tag between the start and
!A end date/times.
!A --------------------------------------------------------------------------
Begin-Procedure Select-Average-Average(
$table_name,
$tagname,
#tag_id,
$start_date_time,
$end_date_time,
:#value,
:#reliability,
:#rows_selected)
#debug display 'Select-Average-Average : Start - ' noline
#debug display $table_name noline
#debug display ' * ' noline
#debug display $tagname noline
#debug display ' - ' noline
#debug display $start_date_time noline
#debug display ' - ' noline
#debug display $end_date_time
move 0 to #rows_selected
Begin-Select
avg(tag_value) &average_eclipse
avg(reliability) &rlb_eclipse
count(tag_value) &rows_eclipse
move &average_eclipse to #value
move &rlb_eclipse to #reliability
move &rows_eclipse to #rows_selected
From [current_result:$table_name]
where tag_id = #tag_id and
tag_timestamp between
to_date($start_date_time, {date_format_mask}) and
to_date($end_date_time, {date_format_mask})
End-Select
#debug display 'Select-Average-Average : End - ' noline
#debug display $tagname noline
#debug display ' [Count: ' noline
#debug display #rows_selected noline
#debug display ']'
End-Procedure
!A --------------------------------------------------------------------------
!A Procedure : Select-Average-Or-Latest
!A Purpose : Try to get an average value between the start and end
!A date/times for a single tag. If there are no values between
!A these times, then get the latest value prior to the
!A end date/time. Note that the Galaxy version of the latest
!A value is the average value, not the snapshot value. If one
!A is trying to get an average in the first place, then one must
!A want the latest average if the period average is not available.
!A --------------------------------------------------------------------------
Begin-procedure Select-Average-Or-Latest(
$table_name,
$tagname,
#tag_id,
$start_date_time,
$end_date_time,
#start_sys_time,
#end_sys_time,
:#value,
:#reliability)
#debug display 'Select-Average-Or-Latest : Start - ' noline
#debug display $tagname
Do Select-Average(
$table_name,
$tagname,
#tag_id,
$start_date_time,
$end_date_time,
#value,
#reliability,
#rows_selected)
#debug display 'Select-Average-Or-Latest : [Rows Selected=' noline
#debug display #rows_selected noline
#debug display ']'
if #rows_selected = 0
Do Select-Latest(
$table_name,
$tagname,
#tag_id,
$end_date_time,
#value)
let #reliability = 100
end-if
#debug display 'Select-Average-Or-Latest : End - ' noline
#debug display $tagname
End-Procedure
!A --------------------------------------------------------------------------
!A Procedure : Select-Latest
!A Purpose : This procedure gets all values for a tag, and only keeps the
!A latest one that is less than the requested end date.
!A --------------------------------------------------------------------------
Begin-procedure Select-Latest(
$table_name,
$tagname,
#tag_id,
:$end_date_time,
:#value)
#debug display 'Select-Latest : Start - ' noline
#debug display $table_name noline
#debug display ' * ' noline
#debug display $tagname noline
#debug display ' * ' noline
#debug display $end_date_time
move $end_date_time to $returned_date_time
Begin-Select loops=1
!/*+ INDEX_DESC(hourly_result hourly_result_pk1) */
tag_value &latest_eclipse
to_char(tag_timestamp,{date_format_mask}) &tgt
move &tgt to $returned_date_time
from [current_result:$table_name]
where tag_id = #tag_id and
tag_timestamp <= to_date($end_date_time, {date_format_mask})
order by tag_timestamp DESC
End-Select
move &latest_eclipse to #value
move $returned_date_time to $end_date_time
#debug display 'Select-Latest : End - ' noline
#debug display $tagname noline
#debug display ' [' noline
#debug display &tgt noline
#debug display ' * ' noline
#debug display &latest_eclipse noline
#debug display ']'
End-Procedure
!A --------------------------------------------------------------------------
!A Procedure : Select-Latest-String
!A Purpose : This procedure gets all string values for a tag, and only keeps
!A the latest one that is less than the requested end date.
!A --------------------------------------------------------------------------
Begin-procedure Select-Latest-String(
$table_name,
$tagname,
#tag_id,
:$end_date_time,
:$string_value)
#debug display 'Select-Latest-String : Start - ' noline
#debug display $table_name noline
#debug display ' * ' noline
#debug display $tagname noline
#debug display ' * ' noline
#debug display $end_date_time
move $end_date_time to $returned_date_time
Begin-Select loops=1
!/*+ INDEX_DESC(hourly_result hourly_result_pk1) */
string_value &latest_string
to_char(tag_timestamp,{date_format_mask}) &tgt
move &tgt to $returned_date_time
from [current_result:$table_name]
where tag_id = #tag_id and
tag_timestamp <= to_date($end_date_time, {date_format_mask})
order by tag_timestamp DESC
End-Select
move &latest_string to $string_value
move $returned_date_time to $end_date_time
#debug display 'Select-Latest-String : End - ' noline
#debug display $tagname noline
#debug display ' [' noline
#debug display &tgt noline
#debug display ' * ' noline
#debug display &latest_string noline
#debug display ']'
End-Procedure
!A --------------------------------------------------------------------------
!A Procedure : Select-Material_Code
!A Purpose : Select the MATL_CODE field value from MATL_CODE for the
!A DESCRIPTION supplied.
!A --------------------------------------------------------------------------
Begin-Procedure Select-Material-Code(
$matl_description,
:$matl_code)
move '' to $matl_code
Begin-Select
MATL_CODE &matl_code
move &matl_code to $matl_code
from MATL_CODE
where RTRIM(UPPER(DESCRIPTION),' ') = UPPER($matl_description)
End-Select
End-Procedure
!A --------------------------------------------------------------------------
!A Procedure : Select-Single
!A Purpose : Get the latest value for a single tag from the CURRENT_RESULT
!A table regardless of the request date/times.
!A --------------------------------------------------------------------------
Begin-procedure Select-Single(
$table_name,
$tagname,
#tag_id,
:#value
:$sam_date,
:$sam_time,
:#rows_selected)
#debug display 'Select-Single : Start - ' noline
#debug display $table_name noline
#debug display ' * ' noline
#debug display $tagname
let #rows_selected = 0
Begin-Select
tag_value &single_eclipse
to_char(tag_timestamp,'MM/DD/YYYY') &sam_date_eclipse
to_char(tag_timestamp,' HH24:MI:SS') &sam_time_eclipse
move &single_eclipse to #value
move &sam_date_eclipse to $sam_date
move &sam_time_eclipse to $sam_time
add 1 to #rows_selected
From CURRENT_RESULT
where tag_id = #tag_id
End-Select
#debug display 'Select-Single : [Rows Selected=' noline
#debug display #rows_selected noline
#debug display ']'
#debug display 'Select-Single : End - ' noline
#debug display $tagname
End-Procedure
!A --------------------------------------------------------------------------
!A Procedure : Select-Single-String
!A Purpose : Get the latest value for a single tag from the CURRENT_RESULT
!A table regardless of the request date/times.
!A --------------------------------------------------------------------------
Begin-procedure Select-Single-String(
$table_name,
$tagname,
#tag_id,
:$string_value
:$sam_date,
:$sam_time,
:#rows_selected)
#debug display 'Select-Single-String : Start - ' noline
#debug display $table_name noline
#debug display ' * ' noline
#debug display $tagname
let #rows_selected = 0
Begin-Select
string_value &single_string_eclipse
to_char(tag_timestamp,'MM/DD/YYYY') &sam_date_eclipse
to_char(tag_timestamp,' HH24:MI:SS') &sam_time_eclipse
move &single_string_eclipse to $string_value
move &sam_date_eclipse to $sam_date
move &sam_time_eclipse to $sam_time
add 1 to #rows_selected
From CURRENT_RESULT
where tag_id = #tag_id
End-Select
#debug display 'Select-Single-String : [Rows Selected=' noline
#debug display #rows_selected noline
#debug display ']'
#debug display 'Select-Single-String : End - ' noline
#debug display $tagname
End-Procedure
!A --------------------------------------------------------------------------
!A Procedure : Select-Single-TimeBased
!A Purpose : Get the single value for a single tag from the result table
!A specified in $table_name. (i.e. HOURLY_RESULT, DAILY_RESULT)
!A --------------------------------------------------------------------------
Begin-procedure Select-Single-TimeBased(
$table_name,
$tagname,
#tag_id,
:#value
:$string_value
:$sam_date,
:$sam_time,
:#rows_selected)
#debug display 'Select-Single-TimeBased : Start - ' noline
#debug display $table_name noline
#debug display ' * ' noline
#debug display $tagname
let #rows_selected = 0
Begin-Select
tag_value &single_timebased_tag_value
string_value &single_timebased_string_value
to_char(tag_timestamp,'MM/DD/YYYY') &sam_date_eclipse
to_char(tag_timestamp,' HH24:MI:SS') &sam_time_eclipse
move &single_timebased_tag_value to #value
move &single_timebased_string_value to $string_value
move &sam_date_eclipse to $sam_date
move &sam_time_eclipse to $sam_time
add 1 to #rows_selected
From [CURRENT_RESULT:$table_name]
where tag_id = #tag_id and
tag_timestamp = to_date($end_date_time, {date_format_mask})
End-Select
#debug display 'Select-Single-TimeBased : [Rows Selected=' noline
#debug display #rows_selected noline
#debug display ']'
#debug display 'Select-Single-TimeBased : End - ' noline
#debug display $tagname noline
#debug display ' * ' noline
#debug display #value noline
#debug display ' * ' noline
#debug display $string_value
End-Procedure
!A --------------------------------------------------------------------------
!A Procedure : Subtract-One-Day
!A Purpose : Decrement a date by 1 day.
!A --------------------------------------------------------------------------
Begin-Procedure Subtract-One-Day(
$input_date,
#input_sys_time,
:$previous_day,
:#previous_sys_time)
Begin-Select
to_char(to_date($input_date,{date_format_mask}) - 1,
{date_format_mask}) &previous_day
from dual
End-Select
move &previous_day to $previous_day
let #previous_sys_time = #input_sys_time - {seconds_in_day}
#debug display 'Subtract-One-Day : ' noline
#debug display $previous_day noline
#debug display ' - ' noline
#debug display #previous_sys_time
End-Procedure
!A --------------------------------------------------------------------------
!A Procedure : Subtract-Eight-Hours
!A Purpose : Decrement a date by 8 hours.
!A --------------------------------------------------------------------------
Begin-Procedure Subtract-Eight-Hours(
$input_date,
#input_sys_time,
:$previous_hour,
:#previous_sys_time)
Begin-Select
to_char(to_date($input_date,{date_format_mask}) - (1/3),
{date_format_mask}) &previous_hour
from dual
End-Select
move &previous_hour to $previous_hour
let #previous_sys_time = #input_sys_time - {seconds_in_hour}
#debug display 'Subtract-Eight-Hours : ' noline
#debug display $previous_hour noline
#debug display ' - ' noline
#debug display #previous_sys_time
End-Procedure
!A --------------------------------------------------------------------------
!A Procedure : Subtract-One-Hour
!A Purpose : Decrement a date by 1 hour.
!A --------------------------------------------------------------------------
Begin-Procedure Subtract-One-Hour(
$input_date,
#input_sys_time,
:$previous_hour,
:#previous_sys_time)
Begin-Select
to_char(to_date($input_date,{date_format_mask}) - (1/24),
{date_format_mask}) &previous_hour
from dual
End-Select
move &previous_hour to $previous_hour
let #previous_sys_time = #input_sys_time - {seconds_in_hour}
#debug display 'Subtract-One-Hour : ' noline
#debug display $previous_hour noline
#debug display ' - ' noline
#debug display #previous_sys_time
End-Procedure
!A --------------------------------------------------------------------------
!A Procedure : Subtract-Seven-Days
!A Purpose : Decrement a date by 7 day.
!A --------------------------------------------------------------------------
Begin-Procedure Subtract-Seven-Days(
$input_date,
#input_sys_time,
:$previous_week,
:#previous_sys_time)
Begin-Select
to_char(to_date($input_date,{date_format_mask}) - 7,
{date_format_mask}) &previous_week
from dual
End-Select
move &previous_week to $previous_week
let #previous_sys_time = #input_sys_time - ( {seconds_in_day} * 7 )
#debug display 'Subtract-One-Day : ' noline
#debug display $previous_week noline
#debug display ' - ' noline
#debug display #previous_sys_time
End-Procedure
!A --------------------------------------------------------------------------
!A Procedure : Truncate-To-Month
!A Purpose : Truncate date/time to day (MM/01/YYYY 00:00:00), and
!A modify the corresponding sys time.
!A --------------------------------------------------------------------------
Begin-Procedure Truncate-To-Month(
$input_date,
#input_sys_time,
:$truncated_date,
:#truncated_sys_time)
extract $truncated_month from $input_date 0 3
extract $truncated_year from $input_date 5 5
let $truncated_date = $truncated_month || '01' || $truncated_year
let $truncated_date = $truncated_date || ' 00:00:00'
let #month = to_number(substr($input_date, 0, 2))
let #hour = to_number(substr($input_date, 12, 2))
let #minute = to_number(substr($input_date, 15, 2))
let #second = to_number(substr($input_date, 18, 2))
let #truncated_sys_time = #input_sys_time - ({seconds_in_day}*#month) - ({seconds_in_hour}*#hour) - (60*#minute) - #second
#debug display 'Truncate-To-Day : ' noline
#debug display $truncated_date noline
#debug display ' - ' noline
#debug display #truncated_sys_time
End-Procedure
!A --------------------------------------------------------------------------
!A Procedure : Truncate-To-Day
!A Purpose : Truncate date/time to day (MM/DD/YYYY 00:00:00), and
!A modify the corresponding sys time.
!A --------------------------------------------------------------------------
Begin-Procedure Truncate-To-Day(
$input_date,
#input_sys_time,
:$truncated_date,
:#truncated_sys_time)
extract $truncated_date from $input_date 0 11
concat '00:00:00' with $truncated_date
let #hour = to_number(substr($input_date, 12, 2))
let #minute = to_number(substr($input_date, 15, 2))
let #second = to_number(substr($input_date, 18, 2))
let #truncated_sys_time = #input_sys_time - ({seconds_in_hour}*#hour) - (60*#minute) - #second
#debug display 'Truncate-To-Day : ' noline
#debug display $truncated_date noline
#debug display ' - ' noline
#debug display #truncated_sys_time
End-Procedure
!A --------------------------------------------------------------------------
!A Procedure : Truncate-To-Hour
!A Purpose : Truncate date/time to hour (MM/DD/YYYY HH24:00:00), and
!A modify the corresponding sys time.
!A --------------------------------------------------------------------------
Begin-Procedure Truncate-To-Hour(
$input_date,
#input_sys_time,
:$truncated_date,
:#truncated_sys_time)
extract $truncated_date from $input_date 0 14
concat '00:00' with $truncated_date
let #minute = to_number(substr($input_date, 15, 2))
let #second = to_number(substr($input_date, 18, 2))
let #truncated_sys_time = #input_sys_time - (60*#minute) - #second
#debug display 'Truncate-To-Hour : ' noline
#debug display $truncated_date noline
#debug display ' - ' noline
#debug display #truncated_sys_time
End-Procedure
!H --------------------------------------------------------------------------
!H Version : 2.0.0 10/21/1998
!H
!H Script : labsummary.sqr
!H
!H Purpose : This script is invoked by the nxrep UNIX script or the
!H runreport.asp Intranet script.
!H
!H Inputs : group tags suffix Example : 24labsum
!H
!H start sys time Example : 879484500
!H
!H end sys time Example : 879570900
!H
!H start date Example : 11/14/1997
!H
!H start time Example : 00:15:00
!H
!H end date Example : 11/15/1997
!H
!H end time Example : 00:15:00
!H
!H user name Example : bischje
!H
!H data type Example : CURRENT, HOURLY, HOURLYFAST,
!H DAILY, RDV, R3H
!H
!H unit name Example : FCC
!H
!H output type Example : TXT or CSV
!H
!H reliability flag Example : Y or N
!H
!R Revision History
!R
!R V1.0.0 Created By James Bischoff 01/07/1998
!R V2.0.0 Modified By James Bischoff 10/21/1998
!R Used optimizer hints for smokin performance
!R
!H --------------------------------------------------------------------------
!A --------------------------------------------------------------------------
!A Include Files
!A --------------------------------------------------------------------------
#include 'd:\sqrreports\generic\glxlib.inc'
!A --------------------------------------------------------------------------
!A Report Constants
!A --------------------------------------------------------------------------
#define labsum_col_width 8 ! Labsum report data column width
#define labsum_col_start 10 ! Labsum report data start column
#define labsum_cols 300 ! Columns in labsum report
#define labsum_lines 1000 ! Max lines in labsum report
#define labsum_max_comment 100 ! Max comments per stream group
#define labsum_max_tests 2000 ! Max tags in a lab summary report
#define labsum_max_rows 50 ! Max data rows per tag
!A --------------------------------------------------------------------------
!A Procedure : Setup
!A Purpose : Set page size
!A --------------------------------------------------------------------------
Begin-Setup
Page-Size {labsum_lines} {labsum_cols}
End-Setup
!A --------------------------------------------------------------------------
!A Procedure : Report
!A Purpose : Main processing loop. Note that variables that travel through
!A here are become global.
!A --------------------------------------------------------------------------
Begin-Report
Do Get-Parameters( ! Get report command line parameters
$group_tags_name,
#start_sys_time,
#end_sys_time,
$start_date_time,
$end_date_time,
$user_name,
$result_type,
$unit_name,
$output_type,
$reliability_marker,
$csv_marker)
uppercase $group_tags_name ! Required for database select
Do Get-Title( ! Get report header title
$group_tags_name,
$report_title)
Do Get-Current-Date-Time( ! Get the report header date/time
$current_date,
$current_time)
Do Print-Labsum-Header( ! Print report header/title
$report_title,
$user_name,
$current_date,
$current_time,
$end_date_time)
Do Create-Labsum-Header-Array( ! Create arrays needed for report
$group_tags_name,
#tag_count)
Do Process-Labsum-Tag-Group( ! Process tagnames in tag group
#tag_count)
End-Report
!A --------------------------------------------------------------------------
!A Procedure : Create-Labsum-Header-Array
!A Purpose : Create arrays needed for report. The lab_sum_comment array
!A stores comments for 1 set of stream tags. The lab_sum_header
!A array stores all definition data pulled from GROUP_TAGS for
!A all tags in the report group. The lab_sum_data array stores
!A the data rows (by date) for one set of stream tags.
!A
!A Warning: This procedure must physically exist in the program prior to
!A any procedures that reference any of the arrays.
!A --------------------------------------------------------------------------
Begin-Procedure Create-Labsum-Header-Array(
$group_tags_name,
:#tag_count)
let #tag_count = 0 ! lab_sum_header array index
let #test_index = 0 ! Indicates the test print column for the tag
let $old_stream_name = '' ! Used to reset the #test_index to 1 by stream
create-array name=lab_sum_comment -
size={labsum_max_comment} -
field=comment:char
create-array name=lab_sum_header -
size={labsum_max_tests} -
field=tagname:char -
field=tagseq:number -
field=title:char -
field=stream:char -
field=format:char -
field=test_index:number -
field=tag_id:number
create-array name=lab_sum_data -
size={labsum_max_rows} -
field=date:char -
field=sortdate:number -
field=value:char:{labsum_max_tests}
Begin-Select
tagname &labsum_header_tagname_galaxy
tagseq &labsum_header_tagseq_galaxy
tagparm2 &labsum_header_title_galaxy
appname &labsum_header_stream_galaxy
tagparm1 &labsum_header_format_galaxy
if &labsum_header_stream_galaxy <> $old_stream_name
let #test_index = 1
let $old_stream_name = &labsum_header_stream_galaxy
end-if
Do Get-TagId(
&labsum_header_tagname_galaxy,
#Tag_Id)
put &labsum_header_tagname_galaxy -
&labsum_header_tagseq_galaxy -
&labsum_header_title_galaxy -
&labsum_header_stream_galaxy -
&labsum_header_format_galaxy -
#test_index -
#tag_id -
into lab_sum_header(#tag_count)
add 1 to #tag_count
add 1 to #test_index
from group_tags
where grpname = $group_tags_name
order by tagseq
End-Select
End-Procedure
!A --------------------------------------------------------------------------
!A Procedure : Empty-Labsum-Data-Array
!A Purpose : Clear out the old lab_sum_data array values.
!A --------------------------------------------------------------------------
Begin-Procedure Empty-Labsum-Data-Array(
#max_data_rows,
#tag_index)
let #previous_tag_index = #tag_index - 1
get #max_data_cols from lab_sum_header(#previous_tag_index) test_index(0)
let #data_row_index = 0
while #data_row_index <= #max_data_rows
put ' ' into lab_sum_data(#data_row_index) date(0)
put 0 into lab_sum_data(#data_row_index) sortdate(0)
let #data_col_index = 0
while #data_col_index <= #max_data_cols
put ' ' into lab_sum_data(#data_row_index) value(#data_col_index)
add 1 to #data_col_index
end-while
add 1 to #data_row_index
end-while
End-Procedure
!A --------------------------------------------------------------------------
!A Procedure : Get-Next-Labsum-Min-Value
!A Purpose : Get max value from array that is < the ceiling. If none, then
!A return the ceiling.
!A --------------------------------------------------------------------------
Begin-Procedure Get-Next-Labsum-Min-Value(
#max_data_rows,
#ceiling,
#index,
:#max_date,
:#max_index)
let #data_row_index = 0
let #current_max = 0
let #max_date = #ceiling
let #max_index = #index
while #data_row_index < #max_data_rows
get #sort_date from lab_sum_data(#data_row_index) sortdate(0)
if #sort_date < #ceiling
if #sort_date > #current_max
let #current_max = #sort_date
let #current_index = #data_row_index
end-if
end-if
add 1 to #data_row_index
end-while
if #current_max <> 0
let #max_date = #current_max
let #max_index = #current_index
end-if
End-Procedure
!A --------------------------------------------------------------------------
!A Procedure : Get-TagId
!A Purpose : Get a tag's Id.
!A --------------------------------------------------------------------------
Begin-Procedure Get-TagId(
$tag_name,
:#tag_id)
let #tag_id = 0
begin-select
tag_id &tag_id
move &tag_id to #tag_id
from tag
where tag_name = $tag_name
end-select
#debug Display 'Get-TagId - ' noline
#debug Display $tag_name noline
#debug Display ' - ' noline
#debug Display #tag_id
End-Procedure
!A --------------------------------------------------------------------------
!A Procedure : Print-Labsum-Comments
!A Purpose : Print the comments for a stream.
!A --------------------------------------------------------------------------
Begin-Procedure Print-Labsum-Comments(
#comment_count)
if #comment_count > 0
print 'Comments below are for data marked with the appropriate footnote' (+2,1)
end-if
let #index = 0
while #index < #comment_count
get $comment from lab_sum_comment(#index) comment(0)
let #footnote = #index + 1
print '(' (+1,1)
print #footnote (0,0) edit 88
print ') ' (0,0)
print $comment (0,0)
add 1 to #index
end-while
End-Procedure
!A --------------------------------------------------------------------------
!A Procedure : Print-Labsum-Header
!A Purpose : Print the lab summary report header.
!A --------------------------------------------------------------------------
Begin-Procedure Print-Labsum-Header(
$report_title,
$user_name,
$current_date,
$current_time,
$end_date_time)
print 'Current Date : ' (1,1)
print $current_date (0,0)
print ' ' (0,0)
print $current_time (0,0)
print ' User Name : ' (+1,1)
print $user_name (0,0)
print $report_title (+2,30)
End-Procedure
!A --------------------------------------------------------------------------
!A Procedure : Print-Labsum-Stream-Results
!A Purpose : Print the lab summary report data rows in date order.
!A --------------------------------------------------------------------------
Begin-Procedure Print-Labsum-Stream-Results(
#max_data_rows,
#tag_index)
!YYYYMMDDHHMISS Good until the 5 digit year problem
let #ceiling = 99999999999999
let #index = 0
let #previous_tag_index = #tag_index - 1
get #max_tests from lab_sum_header(#previous_tag_index) test_index(0)
while 1
Do Get-Next-Labsum-Min-Value(
#max_data_rows,
#ceiling,
#index,
#next_ceiling,
#next_index)
if #ceiling = #next_ceiling
#debug display ' '
break
end-if
get $sam_date from lab_sum_data(#next_index) date(0)
let $print_date = substr($sam_date, 1, 15)
print $print_date (+1,1)
#debug display ' '
#debug display 'Print-Labsum-Stream-Results : ' noline
#debug display $print_date noline
let #print_col_index = 1
while #print_col_index <= #max_tests
get $value from lab_sum_data(#next_index) value(#print_col_index)
let #print_column = #print_col_index * {labsum_col_width} + {labsum_col_start} - 1
print $value (0,#print_column)
#debug display ' ' noline
#debug display $value noline
add 1 to #print_col_index
end-while
let #ceiling = #next_ceiling
let #index = #next_index
end-while
End-Procedure
!A --------------------------------------------------------------------------
!A Procedure : Process-Labsum-Tag
!A Purpose : Get data for a lab summary report tag and place it in the
!A correct row (by date), and column (by test column index).
!A --------------------------------------------------------------------------
Begin-Procedure Process-Labsum-Tag(
$tag_name,
#tag_id
$tag_format,
#test_column_index,
:#max_data_rows,
:#comment_index)
begin-select loops=3
/*+ INDEX_DESC(lab_result lab_result_pk1) */
to_char(tag_timestamp, 'DD-MON-YY HH24:MI:SS') &labsum_sam_date
tag_value &labsum_value
string_value &labsum_string_value
test_comment &labsum_comment
to_char(tag_timestamp, 'YYYYMMDDHH24MISS') &labsum_sort_date
move &labsum_sam_date to $sam_date
let #sort_date = to_number(&labsum_sort_date)
if isnull(&labsum_value)
let $value = &labsum_string_value
else
let $value = edit(&labsum_value,$tag_format)
end-if
let #storage_data_row = #max_data_rows
let #date_found_flag = 0
let #current_data_row = 0
while #current_data_row < #max_data_rows
get $array_date from lab_sum_data(#current_data_row) date(0)
if $array_date = $sam_date
let #storage_data_row = #current_data_row
let #date_found_flag = 1
break
end-if
add 1 to #current_data_row
end-while
if #date_found_flag = 0
add 1 to #max_data_rows
put $sam_date into lab_sum_data(#storage_data_row) date(0)
put #sort_date into lab_sum_data(#storage_data_row) sortdate(0)
end-if
let #footnote = 0
let $comment_no_space = ltrim(&labsum_comment,' ')
let #blank_flag = length($comment_no_space)
if #blank_flag > 0
let #comment_index_search = 0
let #duplicate_comment_flag = 0
while #comment_index_search < #comment_index
get $comment_test from lab_sum_comment(#comment_index_search) comment(0)
if $comment_test = $comment_no_space
let #duplicate_comment_flag = 1
break
end-if
add 1 to #comment_index_search
end-while
if #duplicate_comment_flag
let #footnote = #comment_index_search + 1
else
put $comment_no_space into lab_sum_comment(#comment_index) comment(0)
add 1 to #comment_index
let #footnote = #comment_index
end-if
let $value = $value || '(' || edit(#footnote, '88') || ')'
end-if
put $value into lab_sum_data(#storage_data_row) value(#test_column_index)
#debug Display 'Process-Labsum-Tag - ' noline
#debug Display $tag_name noline
#debug Display ' - ' noline
#debug Display #tag_id noline
#debug Display ' - ' noline
#debug Display $sam_date noline
#debug Display ' - ' noline
#debug Display $value 888888.99 noline
#debug Display ' - ' noline
#debug display &labsum_comment
#debug Display ' Inserted into row #' noline
#debug Display #storage_data_row 89 noline
#debug Display ' test column #' noline
#debug Display #test_column_index 89
from lab_result
where tag_id = #tag_id
end-select
End-Procedure
!A --------------------------------------------------------------------------
!A Procedure : Process-Labsum-Tag-Group
!A Purpose : Process all tags in the group. The tags are grouped by
!A stream name.
!A --------------------------------------------------------------------------
Begin-Procedure Process-Labsum-Tag-Group(
#tag_count)
while #tag_index < #tag_count
get $tag_name -
#tag_order -
$test_title -
$stream_name -
$tag_format -
#test_column_index -
#tag_id -
from lab_sum_header(#tag_index)
if $stream_name <> $old_stream_name
if #tag_index <> 0
Do Print-Labsum-Stream-Results(
#max_data_rows,
#tag_index)
Do Empty-Labsum-Data-Array(
#max_data_rows,
#tag_index)
Do Print-Labsum-Comments(
#comment_count)
end-if
print $stream_name (+2,1)
print 'DATE TIME' (+1,1)
let #max_data_rows = 0
let #comment_count = 0
let $old_stream_name = $stream_name
end-if
let #print_column = #test_column_index * {labsum_col_width} + {labsum_col_start}
print $test_title (0,#print_column)
Do Process-Labsum-Tag(
$tag_name,
#tag_id
$tag_format,
#test_column_index,
#max_data_rows,
#comment_count)
add 1 to #tag_index
end-while
Do Print-Labsum-Stream-Results(
#max_data_rows,
#tag_index)
Do Print-Labsum-Comments(
#comment_count)
End-Procedure
|
