; Diane Conner, 10 June 2005
;
;  This file contains queries developed by the Cassini Project
;  that were found to be useful during the mission.
;
;##################################################################
;
;  Load Leapseconds file
load leapseconds leap;
;
;  Load Sequence S10 science plan file
load ek S10_CIMSSSUPa.bep;
;
;  Load sequence S10 command file
load ek S10_cmd_fa.bes;
;
;  Load sequence S10 status file
load ek S10_status_fa.bes;
;
;  Load sequence S10 ISS noise file
load ek S10_36noise_a.bes;
;
;  Load command database file
load ek 11A.bdb;
;
;##################################################################
; Set the format to be tab delimited
set format delimited;
;
; Set the page width to be 132
set page width 132;
;
; Warn if more than 500 rows can be returned
set deluge warning 500;
;
set default time format YYYY DOY HR:MN:SC
;#################################################################
; Alias some columns and tables

define obs CASSINI_SP_OBSERVATION;

define req CASSINI_SP_REQUEST;

define req2obs CASSINI_SP_REQ_OBS;

define cmddict CASSINI_CMD_DICT;

define cmd CASSINI_CMD;

define cmd_note CASSINI_NOTE;

define status CASSINI_STATUS;

define noise CASSINI_NOISE_EVENTS;

define note CASSINI_NOTE;
;
;######################################################################
; STORED PROCEDURES
;######################################################################
;
; SHOW_NOISE
;
; Show all ISS mechanical noise events
define show_noise

select begin_time,
       end_time,
       command_exec_time,
       event
from noise
  order by begin_time;

;
;######################################################################
;
; SHOW_NOISE_TIME
;
; Show ISS noise events for a specified time period
define show_noise_time

select begin_time,
       end_time,
       command_exec_time,
       event
from noise
     where begin_time >=  START_TIME? and
           end_time <= STOP_TIME?
  order by begin_time;
;
;######################################################################
;
; SHOW_STATUS
;
; Show all subsystem status
define show_status

select begin_time,
       end_time,
       subsystem,
       parameter_name,
       parameter_value
from status
  order by begin_time,subsystem,parameter_name;
;
;######################################################################
;
;  SHOW_OBS         
;
; Science plan requests and their associated observation science objective
;
define show_obs

SELECT begin_time,
   end_time,
       r.sequence,
     r.request_id,
   request_title,
  r.req_description,
      o.obs_description,
        o.science_objective
from req r, obs o, req2obs e
       where r.request_id eq e.request_id
      and o.observation_id eq e.observation_id;
;
;######################################################################
;
;  SHOW_POINT
;
; Spacecraft pointing agreements as part of the science plan
;
define show_point

select request_id,
        sequence,
       begin_time,
     end_time, 
      primary_pointing,
       secondary_pointing,
     pointing_agreement,
     request_title,
  req_description
from req
    where primary_pointing NE " " or secondary_pointing NE " "
  order by begin_time;

;######################################################################
;
; SHOW_DICT        
;
; The command dictionary description for a specific command
;
define show_dict
select command,
      description,
    dictionary_version,
     parameter_index,
        parameter_name,
parameter_range,
        parameter_type,
subsystem
from cmddict where command = COMMAND?;

;##################################################################
;
; SHOW_COMMAND  
;
; Display occurrences of a specified command
;
define show_command

select time,
        command,
        request_id,
     parameter_name,
chr_value
from cmd where command = COMMAND?
  order by time;

;##################################################################
;
; SHOW_COMMAND_WITH_DESC 
;
; Display occurrences of a specific command and the command description.
;
define show_command_with_desc

select a.time,
    a.command,
      b.description
from cmd a, cmddict b
    where a.command = b.command
      and a.parameter_index = b.parameter_index
      and a.parameter_index eq 0 and b.parameter_index eq 0
      and a.command = COMMAND_NAME?
  order by time;
;
;##################################################################
;
;  SHOW_PARAMETERS_WITH_DESC 
;
; Display occurrences of a specific command and the command
; and parameter descriptions
; Use caution when only displaying some values.  Sampling
; will hide relevant adjacent records.
;
;

define show_parameters_with_desc

select a.time,
a.command,
      a.parameter_name,
       a.chr_value,
    b.description
from cmd a, cmddict b
    where a.command = b.command
      and a.parameter_index = b.parameter_index
      and a.command = COMMAND_NAME?
  order by time,  a.command, a.parameter_index;
;
;######################################################################
;
; SHOW_COMMANDS_TIME  
;
; Display commands within specified time range
; and displays them ordered by time.
; You must quote time values and include T. 
;      For example:  "2005-265T",
;                or  "2005-265T05:20:13"
;
define show_commands_time

select time,
command
from cmd
    where parameter_index eq 0 and time between
      START_TIME? and END_TIME?
  order by time;

;##################################################################
;
;  SHOW_SUBSYSTEM_COMMANDS  
;
; Display commands for a specified subsystem and their parameter values
;
define show_subsystem_commands

select a.time,
      b.subsystem,
    a.command,
        a.parameter_name,
     a.parameter_index,
      a.chr_value,
        b.description
from cmd a, cmddict b
  where (b.subsystem = SUBSYSTEM?
and a.command = b.command
       and a.parameter_index = b.parameter_index)
      order by a.time, a.command,a.parameter_index;
;
;##################################################################
;
; SHOW_SUBSYSTEM_COMMANDS_TIME
;
; Display commands for a specified subsystem and time range
; with their parameter values

define show_subsystem_commands_time

select a.time,
      b.subsystem,
    a.command,
        a.parameter_name,
     a.parameter_index,
      a.chr_value
from cmd a, cmddict b
  where
time between START_TIME?  and END_TIME?
and (b.subsystem = SUBSYSTEM?
   and a.command = b.command
       and a.parameter_index = b.parameter_index)
  order by a.time, a.command,a.parameter_index;
;
;##################################################################
;
;  SHOW_SCIENCE_COMMANDS 
;
; Display all science commands and their descriptions. 

define show_science_commands

select  a.time,
      b.subsystem,
    a.command,
        b.description
from cmd a, cmddict b
  where a.command eq b.command
      and a.parameter_index eq 0 and b.parameter_index eq 0
   and ( b.subsystem eq 'CIRS'
             or b.subsystem eq 'ISS'
        or b.subsystem eq 'UVIS'
                or b.subsystem eq 'VIMS'
                or b.subsystem eq 'RADAR'
               or b.subsystem eq 'RSS'
        or b.subsystem eq 'CAPS'
                or b.subsystem eq 'CDA'
        or b.subsystem eq 'INMS'
                or b.subsystem eq 'MAG'
        or b.subsystem eq 'MIMI'
                or b.subsystem eq 'RPWS')
  order by a.time;
;
;##################################################################
;
;  SHOW_SCIENCE_STATUS  displays status of all science subsystems
;                       for all time periods covered by CASSINI_CMD
;                       based data tables.
;
define show_science_status

select begin_time,
          end_time,
          subsystem,
          parameter_name,
          parameter_value
from status
     where subsystem eq 'CIRS' or
           subsystem eq 'ISS' or
           subsystem eq 'UVIS' or
           subsystem eq 'VIMS' or
           subsystem eq 'RADAR' or
           subsystem eq 'RSS' or
           subsystem eq 'CAPS' or
           subsystem eq 'CDA' or
           subsystem eq 'INMS' or
           subsystem eq 'MAG' or
           subsystem eq 'MIMI' or
           subsystem eq 'RPWS'
order by subsystem, begin_time, parameter_name;

;##################################################################
;
;  SHOW_SCIENCE_STATUS  displays status of all science subsystems
;                       for all time periods covered by CASSINI_CMD
;                       based data tables.
;
define show_science_status_subsystem

select begin_time,
          end_time,
          subsystem,
          parameter_name,
          parameter_value
from status
     where subsystem eq SUBSYSTEM?;
;order by begin_time, parameter_name;

;
;##################################################################
;
;  SHOW_STATUS_AT_TIME  displays status of the specified subsystem
;                       through before given time.
;
define show_status_at_time


select begin_time,
          end_time,
          subsystem,
          parameter_name,
          parameter_value
from status

     where  subsystem eq SUBSYSTEM?
            and (begin_time <= TIME1? and end_time >= TIME2? )
order by parameter_name;


;
;
;  SHOW_NOTES
;
; Display all command notes
;
define show_notes

select time,
       request_id,
       chr_value
from note
     where attribute_name = "NOTE_TEXT"
     and chr_value !="SEQ"
 order by time, attribute_index;

;#################################################################
;
; SHOW_TLM_MODE
;
; Display telemetry mode
;
define show_tlm_mode

select begin_time,
       end_time,
       parameter_value
from status
     where parameter_name = "CHG_SC_TM_IMM"
  order by begin_time;

;################################################################
;
; SHOW_OPMODE
;
; Display power operations modes
;
define show_opmode

select s.begin_time,
       s.end_time,
       s.parameter_value,
       n.request_id,
       n.chr_value
from status s, cmd_note n
    where s.parameter_name = "Mode"
   and s.begin_time = n.time 
        and n.request_id like "ENGR*"
order by s.begin_time;


;################################################################
;
; SHOW_TARGET
;
; Display the primary target
;
define show_target

select begin_time,
       end_time,
       parameter_value
from status
    where subsystem="AACS" and parameter_name="TARGET"
       or parameter_name ="BODY_VEC"      
 order by begin_time;

;################################################################
;
; SHOW_RWA
;
; Display when the spacecraft is using reaction wheels or thrusters
; and what the deadband is.
;
define show_rwa

select begin_time,
       end_time,
       parameter_name,
       parameter_value
from status
     where subsystem="AACS" and parameter_name like "DEADBAND*"
           or parameter_value like "HOME_BASE*"
 order by begin_time,end_time;

;################################################################
;
; SHOW_CMT_PARAM
;
; Display pointing constraint information
;
define show_cmt_param

select begin_time,
       end_time,
       parameter_name,
       parameter_value
from status
    where  parameter_name = "CONSTRAINT"
       and parameter_value like "*POS_X*"
       or parameter_value like "*NEG_Y*"
       or parameter_value like "*POS_X_SUN*"
       or parameter_value like "*NEG_Y_SUN*"
order by begin_time;