###
Platform Queries
This document contains all of the SQL queries employed by the 
Robot operator (970924). Elements marked with "$" are scalar 
values provided by the Robot Operator.

KMW
###

--- drCheckDatabasePIN ---
select pin,appl,lang,recruit_date,pin_usage from active_pins 
where pin = $callers_pin

--- drCheckIfPhoneUsed ---
select caller_pin,caller_number from switchboard_stats where 
(caller_pin = $caller_pin and caller_number = $C_ANI)

--- drCheckIfSBUsedToday ---

select caller_pin,callee_pin,call_date from switchboard_stats
where (caller_pin = $caller_pin or callee_pin = $caller_pin)
and trunc(call_date) = trunc(sysdate)

--- drCheckPreAssignedPIN ---

select pin,appl,language,phone_number from registration
where pin = $caller_pin and appl = $c_appl and language = 
$lang

--- drGetCalleeList ---

select PIN,phone from swb_candidates where tel_line = $ph_line

--- drGetTopic ---

select top from rand_topics where top in
(select topic from sid2topic where yn = 0 and sid =
(select sid from call_subjects where pin = $caller_pin)
and exists (select topic from sid2topic where yn = 0 and
sid = (select sid from call_subjects where pin = $callee_pin))
)
order by ind

###
Platform Updates
###

--- deActivateMasterPIN ---

insert into PIN values ($caller_pin,$c_appl,$lang,
(select sysdate from dual),0)

--- deAddRegistrationInfo ---

update registration set appl = $c_appl, language = $lang,
gender = $i_gender,age = $i_age, school = $i_education, 
phone_number = $i_phone, phone_line = $ph_line,reg_date = 
(select to_char(sysdateYY-MM-DD') from dual), reg_time = 
(select to_char(sysdateHH:MI:SS'),where PIN = $caller_pin

--- deAnsweringMachineStat ---

insert into call_stats (caller_pin,appl_s,lang_s,caller_ani,
caller_phone,callee_pin,callee_phone,line,call_status,
call_date_time) values ($caller_pin,$c_appl,$lang,
$ani,$i_calling_from_number,$callee_pin,$i_calling_to_number,
$telephoneline,4,sysdate)

--- deAnsweringMachineSubj ---

update call_subjects cs set cs.skip_until_time = sysdate + 
1/24, cs.last_status = 4  where cs.pin = $callee_pin

--- deCalleeHangupStat ---

insert into CALL_STATS (caller_pin, appl_s, lang_s, 
caller_ani, caller_phone,callee_pin, callee_phone, line, 
call_status, call_date_time) values($CALLER_PIN,$c_appl,
$lang , $ani,$I_CALLING_FROM_NUMBER ,$callee_pin , $I_CALLING_TO_NUMBER , $TelephoneLine ,7, SYSDATE )

--- deCalleeHangupSubj ---

update call_subjects cs    
set cs.skip_until_time = sysdate + 1,         
cs.last_status = 7
where cs.pin =  $callee_pin


--- deCalleeNotAvailableStat ---

insert into CALL_STATS (    caller_pin, appl_s, lang_s, 
caller_ani, caller_phone,    callee_pin, callee_phone, 
line, call_status, call_date_time) values( 
$I_MASTER_PIN ,$c_appl,$lang ,$ani ,           
$I_CALLING_FROM_NUMBER ,$callee_pin , 
$I_CALLING_TO_NUMBER , $TelephoneLine ,2,SYSDATE )

--- deCalleeNotAvailableSubj --- 

update call_subjects cs    
set cs.skip_until_time = sysdate + (1/24), 
cs.last_status = 2  where cs.pin = $callee_pin

--- deCalleeTimeoutStat ---

insert into CALL_STATS (caller_pin, appl_s, lang_s, 
caller_ani, caller_phone, callee_pin, callee_phone, 
line, call_status, call_date_time) values
( 	   $caller_pin ,           $c_appl ,
           $lang ,
           $ani ,         $I_CALLING_FROM_NUMBER ,          
 	   $callee_pin ,  $I_CALLING_TO_NUMBER ,          
	   $TelephoneLine, 9, 
	   SYSDATE 
)


--- deCalleeTimeoutSubj ---
update call_subjects cs    
set cs.skip_until_time = sysdate + 1,         
cs.last_status = 9 where cs.pin =  $callee_pin

--- deCallerHangupStat ---

insert into CALL_STATS (caller_pin, appl_s, lang_s, caller_ani,
caller_phone,callee_pin, callee_phone, line, call_status, 
call_date_time) values
( $caller_pin,           $c_appl ,
           $lang ,
           $ani ,        $I_CALLING_FROM_NUMBER ,          
 	   $callee_pin , $I_CALLING_TO_NUMBER ,          
           $TelephoneLine ,           8, 
	   SYSDATE 
)



--- deCallerHangupSubj ---

update call_subjects cs    
set cs.skip_until_time = sysdate + 1,         
cs.last_status = 8
where cs.pin =  $callee_pin


--- deCallNotAcceptedStat ---

insert into CALL_STATS (caller_pin, appl_s, lang_
s, caller_ani, caller_phone,    callee
_pin, callee_phone, line, call_status, call_date_time) 
values( $caller_pin,           $c_appl ,
        $lang ,	$ani , $I_CALLING_FROM_NUMBER ,          
 	$callee_pin ,  $I_CALLING_TO_NUMBER ,          
	$TelephoneLine , 2, SYSDATE )

--- deCallNotAcceptedSubj ---

update call_subjects cs    
set cs.skip_until_time = sysdate + 1,         
cs.last_status = 2
where cs.pin =  $callee_pin

--- deClearCalleeTopic ---

update sid2topic set yn = 1 where topic = D_TOPIC  
and sid = (select sid from call_subjects where pin = 
$caller_pin  )

--- deClearCallerTopic ---

update sid2topic set yn = 1 where topic = D_TOPIC  
and sid = (select sid from call_subjects where pin = 
$caller_pin  )

--- deDeactivatePIN ---

update call_subjects   
set CALLS_MADE =  (CALLS_MADE + 1),        
last_status = 0' where PIN =   I_MASTER_PIN    
and PROJECT =  C_APPL

--- deFaxModemStat ---
insert into CALL_STATS 
(caller_pin, appl_s, lang_s, caller_ani,
 caller_phone,    callee_pin, callee_phone, line, 
 call_status, call_date_time)
 values( $caller_pin ,$c_appl ,$lang ,$C_ANI ,
$I_CALLING_FROM_NUMBER , $callee_pin ,
$I_CALLING_TO_NUMBER,$TelephoneLine,5, SYSDATE )

--- deFaxModemSubj ---

update call_subjects cs
set cs.skip_until_time = sysdate + 1/24,         
cs.last_status = 5  where cs.pin =  $callee_pin 

--- deIncrementPINUsage ---

update PIN    set PIN_USAGE = (PIN_USAGE + 1)  
where PIN =  $caller_pin or PIN =  $callee_pin 


--- deInsertDualRecordStatInfo ---
insert into CALL_STATS 
(caller_pin, appl_s, lang_s, caller_ani, caller_phone, 
callee_phone,    conn_duration, talk_duration, 
record_duration, line,  call_status, call_date_time)
values ( $caller_pin ,$c_appl , $C_LANGUAGE , $C_ANI ,
$I_CALLING_FROM_NUMBER , $I_CALLING_TO_NUMBER ,           
$C_CALL_DURATION , $C_TALK_DURATION , $C_RECORD_DURATION,
$TelephoneLine ,0, SYSDATE )

--- deInsertSB_DualRecordStatInfo ---

insert into call_stats 
(call_date_time, call_status, appl_s, lang_s, topic,
caller_pin, callee_pin, caller_ani, caller_phone, 
callee_phone, conn_duration, talk_duration, record_duration, 
line)   values
(SYSDATE,$record_status,$c_appl ,$lang ,    
$TOPIC ,$caller_pin ,$callee_pin,$ani ,       
$I_CALLING_FROM_NUMBER ,$I_CALLING_TO_NUMBER ,
$C_CALL_DURATION , $C_TALK_DURATION , $C_RECORD_DURATION , 
$TelephoneLine)


--- deLineWasBusyStat ---

insert into CALL_STATS 
(caller_pin, appl_s, lang_s, caller_ani, caller_phone,    
callee_pin, callee_phone, line, call_status, call_date_time)
values
( $caller_pin ,$c_appl ,$lang , $C_ANI , 
$I_CALLING_FROM_NUMBER , $callee_pin,
$I_CALLING_TO_NUMBER, $TelephoneLine,1, SYSDATE )

--- deLineWasBusySubj ---

update call_subjects cs
set cs.skip_until_time = sysdate + ((1/24)/12),
cs.last_status = 1  where cs.pin =  $callee_pin

--- deReleaseLineCandidates ---
delete from swb_candidates sc    
where sc.tel_line =  TelephoneLine 



--- deSBCallBadStatusStat ---

insert into CALL_STATS 
(caller_pin, callee_pin, appl_s, lang_s, caller_ani, 
caller_phone, callee_phone, line, topic, call_status, 
call_date_time) 
values( $caller_pin,$callee_pin,$c_appl ,$lang ,$ani ,
$I_CALLING_FROM_NUMBER , $I_CALLING_TO_NUMBER , $TelephoneLine,
10,SYSDATE )


--- deSBCallBadStatusSubj --- 
update call_subjects cs
set cs.skip_until_time = sysdate + 1, cs.last_status = 10  
where cs.pin =  $callee_pin 

--- deSBCallNotAnsweredStat ---

insert into CALL_STATS 
(caller_pin, callee_pin, appl_s, lang_s, caller_ani, 
caller_phone, callee_phone, line, call_status, call_date_time) 
values ($caller_pin ,$callee_pin ,$c_appl ,$lang , $C_ANI ,
$I_CALLING_FROM_NUMBER , $I_CALLING_TO_NUMBER , $TelephoneLine,
3,SYSDATE)

--- deSBCallNotAnsweredSubj ---

update call_subjects cs set cs.skip_until_time = 
sysdate + (1/24), cs.last_status = 3 
where cs.pin =  $callee_pin 

--- deSelectLineCandidates ---

insert into swb_candidates (pin,phone,tel_line)select 
cc.pin,cc.phone, $TelephoneLine from available_callees 
cc where rownum <= $T_MAX_SB_CAND and cc.pin not in (select 
st.callee_pin from switchboard_stats st where (st.caller_pin 
= $caller_pin and st.callee_pin = cc.pin) or 
(st.callee_pin =  $caller_pin and st.caller_pin = cc.pin))
and cc.pin <>  $caller_pin 

--- deUpdateSBCalleeStatus ---

update call_subjects set last_status = $record_status, 
skip_until_time = sysdate + 1,calls_recvd = (calls_recvd +1)  
where PIN = $callee_pin 

--- deUpdateSBCallerStatus ---

update call_subjects set calls_made = (calls_made +1),
skip_until_time = sysdate + 1 where PIN = $caller_pin 
