/******************************************************************************
   NAME:       load_QUESTIONNAIRE_RESULT.sql
   PURPOSE:    This script will create QUESTIONNAIRE_RESULT records
   ADDITIONAL INFORMATION:

   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        2/12/2013   M. Jacobsen      Initial release

******************************************************************************/
delete from SURVEY_QUESTION_ANSWER;
delete from SURVEY_RESPONSE;
commit;

declare
    v_survey_question_id number;
    v_survey_choice_id   number;
	v_survey_id          number;
	v_line_nbr           number := 0;
	
    cursor c_cursor is
      select visn, facility_id
      from facility;
	 
    cursor c_question (v_survey_id number) is
      select survey_question_id
      from survey s, survey_question q
      where s.survey_id = v_survey_id
      and s.survey_id = q.survey_id;

begin
   dbms_output.enable(null);
   DBMS_RANDOM.SEED(42); -- add seeding for same random numbers.
   DBMS_OUTPUT.put_line (   'Survey Start Time := '|| TO_CHAR (SYSDATE, 'MM/DD/YY hh:mi:ss'));

   for facility_rec in c_cursor
    loop
	    SELECT SURVEY_ID 
		INTO v_survey_id
		FROM (SELECT SURVEY_ID FROM SURVEY ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1;
    
        INSERT INTO SURVEY_RESPONSE (
          SURVEY_RESPONSE_ID, 
          SURVEY_ID, 
          FACILITY_ID, 
          VENDOR_ID, 
          SURVEY_PATIENT_ID, 
          CARE_COORDINATOR_ID,
          REQUEST_ID, 
          RECORDSOURCE_NAMESPACEID, 
          RECORDSOURCE_UNIVERSALID, 
          RECORDSOURCE_UNIVERSTYP,
          PATIENT_IDENTITY, 
          PATIENT_ASSIGNING_FACILITY, 
          PATIENT_ASSIGNING_AUTHORITY, 
          PATIENT_NAME_PREFIX,
          PATIENT_NAME_GIVEN, 
          PATIENT_NAME_FAMILY, 
          PATIENT_NAME_SUFFIX, 
          PATIENT_NAME_TITLE,
          PATIENT_NAME_MIDDLE, 
          VENDOR_MRN, 
          SCHEDULED_DT, 
          COMPLETED_DT, 
          CURRENT_DIALOG_NAME,
          PROGRAM_NAME, 
          DEVICE_NAME, 
          SURVEY_ACCEPTED_STATUS, 
          MENTAL_SCORE, 
          PHYSICAL_SCORE,
          DISENRMENT_DT, 
          INACTIVATION_DT, 
          ACTIVATION_DT, 
          ENROLLMENT_DT, 
          RECORD_CREATION_TM,
          RECORD_CREATION_TM_NBR
        ) VALUES (
          survey_response_seq_id.nextval,
          v_survey_id,
          facility_rec.facility_id,
          ( SELECT VENDOR_ID FROM (SELECT VENDOR_ID FROM VENDOR ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 ),
          ( SELECT SURVEY_PATIENT_ID FROM (SELECT SURVEY_PATIENT_ID FROM SURVEY_PATIENT ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 ),
          ( SELECT CARE_COORDINATOR_ID FROM (SELECT CARE_COORDINATOR_ID FROM CARE_COORDINATOR ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 ),
          DBMS_RANDOM.STRING('A', DBMS_RANDOM.VALUE(1, 256)), 
          DBMS_RANDOM.STRING('A', DBMS_RANDOM.VALUE(1, 256)),
          DBMS_RANDOM.STRING('A', DBMS_RANDOM.VALUE(1, 256)), 
          DBMS_RANDOM.STRING('A', DBMS_RANDOM.VALUE(1, 256)),
          DBMS_RANDOM.STRING('A', DBMS_RANDOM.VALUE(1, 256)), 
          DBMS_RANDOM.STRING('A', DBMS_RANDOM.VALUE(1, 256)),
          DBMS_RANDOM.STRING('A', DBMS_RANDOM.VALUE(1, 256)), 
          DBMS_RANDOM.STRING('A', DBMS_RANDOM.VALUE(1, 4000)),
          DBMS_RANDOM.STRING('A', DBMS_RANDOM.VALUE(1, 4000)), 
          DBMS_RANDOM.STRING('A', DBMS_RANDOM.VALUE(1, 4000)),
          DBMS_RANDOM.STRING('A', DBMS_RANDOM.VALUE(1, 4000)), 
          DBMS_RANDOM.STRING('A', DBMS_RANDOM.VALUE(1, 4000)),
          DBMS_RANDOM.STRING('A', DBMS_RANDOM.VALUE(1, 4000)), 
          DBMS_RANDOM.STRING('A', DBMS_RANDOM.VALUE(1, 30)),
          TO_DATE(TRUNC(DBMS_RANDOM.VALUE(2415020, 2488068)), 'J'), 
          TO_DATE(TRUNC(DBMS_RANDOM.VALUE(2415020, 2488068)), 'J'),
          DBMS_RANDOM.STRING('A', DBMS_RANDOM.VALUE(1, 100)), 
          DBMS_RANDOM.STRING('A', DBMS_RANDOM.VALUE(1, 100)),
          DBMS_RANDOM.STRING('A', DBMS_RANDOM.VALUE(1, 100)), 
          DBMS_RANDOM.STRING('A', DBMS_RANDOM.VALUE(1, 100)),
          DBMS_RANDOM.VALUE(0.00, 2147483647.00), 
          DBMS_RANDOM.VALUE(0.00, 2147483647.00),
          TO_DATE(TRUNC(DBMS_RANDOM.VALUE(2415020, 2488068)), 'J'), 
          TO_DATE(TRUNC(DBMS_RANDOM.VALUE(2415020, 2488068)), 'J'),
          TO_DATE(TRUNC(DBMS_RANDOM.VALUE(2415020, 2488068)), 'J'), 
          TO_DATE(TRUNC(DBMS_RANDOM.VALUE(2415020, 2488068)), 'J'),
          DBMS_RANDOM.STRING('A', DBMS_RANDOM.VALUE(1, 100)), 
          DBMS_RANDOM.VALUE(0.00, 2147483647.00)
        );
		
		open c_question (v_survey_id);
        loop
            fetch c_question into v_survey_question_id;
            exit when c_question%NOTFOUND;
            
            select survey_choice_id 
            into v_survey_choice_id
            from ( select survey_choice_id from survey_choice where survey_question_id = v_survey_question_id order by dbms_random.value ) 
            where rownum = 1;
            
            insert into survey_question_answer ( survey_question_answer_id, survey_response_id, survey_choice_id, line_nbr )
            values ( survey_question_answer_seq_id.nextval, survey_response_seq_id.currval, v_survey_choice_id, v_line_nbr );
            
			v_line_nbr := v_line_nbr + 1;
			
        end loop;
        commit;
        close c_question;
        v_line_nbr := 0;

		
    end loop;
    commit;
   DBMS_OUTPUT.put_line (' ');
   DBMS_OUTPUT.put_line (   'Survey End Time := ' || TO_CHAR (SYSDATE, 'MM/DD/YY hh:mi:ss') );
end;
/