/*********************************************************************************************/
/*    These scripts are included with the DNC as timesavers for in-house setup & testing     */
/*********************************************************************************************/
/*************************************************************************************/


/***************************************************************************************************************/
/* Script to insert a userId into the user_profile table and then give all the services to this user, by       */
/* using a cursor of the services.service_id to loop through and assign all services to this user.             */
/***************************************************************************************************************/
DECLARE

  CURSOR service_ids
    IS
      SELECT service_id
	    FROM services
	  ORDER BY service_id;
  v_user VARCHAR2(50) := 'cdx';	  
  v_count NUMBER := 0;
BEGIN
  -- find out if this user exists.
  Select count(*)
    INTO v_count
    FROM user_profiles
   WHERE UPPER(user_id) = UPPER(v_user);
  -- if the user does not exist, insert into the user_profiles table.
  IF v_count <= 0 THEN
    insert into user_profiles (user_id)
    VALUES (v_user);
    commit;
    DBMS_OUTPUT.PUT_LINE('USER inserted into USER_PROFILES table.');
  END IF;


  FOR id in service_ids
  LOOP
    INSERT INTO user_authorization (user_id, service_id)
	VALUES (v_user, id.service_id);  
  END LOOP;
  commit;
  DBMS_OUTPUT.PUT_LINE('USER :'||v_user||': given access to all services.');
    
EXCEPTION WHEN OTHERS THEN
    ROLLBACK;
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
  
END;

commit

/******************************************************************************************/
/* Use the next script to simulate a call from this node to itself.  Take the             */
/* transactionId returned from the DNC client Solicit call and insert it in the place     */
/* holder below	 		  	   	   	   		                     		  	   	   		  */
/******************************************************************************************/
BEGIN
  DBMS_OUTPUT.PUT_LINE('   ');
  INSERT INTO outgoing_solicit
       VALUES ('<place_holder>','GetFacilityByChangeDate','READY',sysdate);
--        VALUES ('f73d7caa-bac6-4508-a15c-53cb5108a7e8','GetFacilityByChangeDate','READY',sysdate);
  commit;
  DBMS_OUTPUT.PUT_LINE('done with INSERT');
  dbms_output.put_line(TO_CHAR(sysdate, 'HH:MI:SS'));
EXCEPTION
  WHEN OTHERS THEN
   	  DBMS_OUTPUT.PUT_LINE(sqlerrm);
END; 


/***************************************************************************************************************/
/* Use the next script to simulate running the scheduler_submitlistener.sql script in Cron job scheduler       */
/***************************************************************************************************************/
DECLARE
BEGIN
  DBMS_OUTPUT.PUT_LINE('  ');
  DBMS_OUTPUT.PUT_LINE('Begin process');
  -- put a stop transaction_id at the end of the solicit job queue.
  DBMS_OUTPUT.PUT_LINE('RUN : scheduler.stop');
  scheduler.stop('solicit_job_queue');
  -- process all the outstanding jobs in the solicit job queue.
  DBMS_OUTPUT.PUT_LINE('RUN : scheduler.run');
  scheduler.run('solicit_job_queue');
  -- put a stop transaction_id at the end of the submit job queue.
  DBMS_OUTPUT.PUT_LINE('RUN : stop_submitlistener');
  stop_submitlistener('submit_job_queue');
  commit;
    DBMS_OUTPUT.PUT_LINE('End process');
  dbms_output.put_line(TO_CHAR(sysdate, 'HH:MI:SS'));  
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(sqlerrm);
	
END;




/***************************************************************************************************************/
/* Query the tables for records.                                                                               */
/* set the v_clear to TRUE if the tables should be cleared out.                                                */
/***************************************************************************************************************/
DECLARE
  v_count NUMBER(4);
  v_clear BOOLEAN := false;
BEGIN

  IF v_clear THEN
     delete from  incoming_solicit;
	 delete from  incoming_solicit_parameters;
	 delete from  incoming_notification;
	 delete from  wdocument;
	 delete from  incoming_download;
	 delete from  Service_tracking_log;
	 delete from  document;
	 delete from  submit_job_queue;
	 delete from  solicit_job_queue;
     delete from  outgoing_solicit;
     delete from  outgoing_solicit_parameters;
	 delete from  idocument;
	 commit;
  END IF;

  -- put an empty line at the being to break up the output...
  dbms_output.put_line('   ');

  SELECT count(*)
    INTO v_count
    FROM incoming_solicit;
  dbms_output.put_line('incoming_solicit :'||v_count);

  SELECT count(*)
    INTO v_count
    FROM incoming_solicit_parameters;
  dbms_output.put_line('incoming_solicit_parameters :'||v_count);
  
  SELECT count(*)
    INTO v_count
    FROM incoming_notification;
  dbms_output.put_line('incoming_notification :'||v_count);
  
  SELECT count(*)
    INTO v_count
    FROM solicit_job_queue;
  dbms_output.put_line('solicit_job_queue :'||v_count);
  
  SELECT count(*)
    INTO v_count
    FROM submit_job_queue;
  dbms_output.put_line('submit_job_queue :'||v_count);
  
  SELECT count(*)
    INTO v_count
    FROM incoming_download;
  dbms_output.put_line('incoming_download :'||v_count);
   
  SELECT count(*)
    INTO v_count
    FROM wdocument;
  dbms_output.put_line('wdocument :'||v_count);
  
  SELECT count(*)
    INTO v_count
    FROM outgoing_solicit;
  dbms_output.put_line('outgoing_solicit :'||v_count);
  
  SELECT count(*)
    INTO v_count
    FROM outgoing_solicit_parameters;
  dbms_output.put_line('outgoing_solicit_parameters :'||v_count);
  
    SELECT count(*)
    INTO v_count
    FROM idocument;
  dbms_output.put_line('idocument :'||v_count);
  
  dbms_output.put_line(TO_CHAR(sysdate, 'HH:MI:SS'));
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(sqlerrm);
	
END;



