Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- PROCEDURE insert_from_studis_to_tvarkis(semester IN VARCHAR2)
- IS
- --array1 apex_application_global.vc_arr2;
- s SYS_REFCURSOR;
- r tvarkis.tv_types.trans_regis_to_tvarkis_by_rg_r;
- -- Kintamieji skirti savaičių išrūšiavimui
- weeks_with_commas VARCHAR2(100);
- v_array1 apex_application_global.vc_arr2;
- week_id apex_application_global.vc_arr2;
- empty_table apex_application_global.vc_arr2;
- time_date apex_application_global.vc_arr2;
- -- Kintamieji skirti laiko sutvarkytmui
- first_time_start VARCHAR2(20);
- first_time_end VARCHAR2(20);
- second_time_start VARCHAR2(20);
- second_time_end VARCHAR2(20);
- time_count NUMBER(2);
- -- Pastato kintamasis
- pastatas VARCHAR2(40);
- room_id VARCHAR2(40);
- room_name VARCHAR2(40);
- n NUMBER(10);
- second_count NUMBER(10);
- lecture_type VARCHAR2(20);
- -- Counters for inserted, same, errors.
- same NUMBER(20) := 0;
- new_inserted NUMBER(20) := 0;
- failed NUMBER(20) := 0;
- BEGIN
- s := tvarkis.tv_studis_to_tvarkis.get_info_from_studis(p_semester_in => 'FO0060');
- LOOP FETCH s INTO r;
- EXIT WHEN s%NOTFOUND;
- -- Getting lecture type at right format
- CASE regexp_replace( r.lecture_type , '[[:space:]]*','')
- WHEN 'LECT'
- THEN lecture_type := 'DT001';
- WHEN 'ND'
- THEN lecture_type := 'DT005';
- WHEN 'LAB'
- THEN lecture_type := 'DT002';
- WHEN 'SEM'
- THEN lecture_type := 'DT003';
- WHEN 'PRAT'
- THEN lecture_type := 'DT004';
- END CASE;
- -- Getting lecture room_id
- CASE r.building
- WHEN 'ŠEV 31'
- THEN pastatas := 'SEV31';
- WHEN 'STU39'
- THEN pastatas:= 'STU39';
- WHEN 'DAU 27'
- THEN pastatas := 'DAU27';
- WHEN 'VYT 71'
- THEN pastatas := 'VYT71';
- WHEN 'PUT 23'
- THEN pastatas := 'PUT23';
- WHEN 'MUI 7'
- THEN pastatas := 'MUI7';
- WHEN 'ČEP 5'
- THEN pastatas := 'CEP5';
- WHEN 'GIM 7'
- THEN pastatas := 'GIM7';
- WHEN 'JON 66'
- THEN pastatas := 'JON66';
- WHEN 'VIL 8'
- THEN pastatas := 'VIL8';
- WHEN 'GED 44'
- THEN pastatas := 'GED44';
- WHEN 'DON 52'
- THEN pastatas := 'DON52';
- WHEN 'CR'
- THEN pastatas := 'DAU28';
- WHEN 'Neauditorine'
- THEN pastatas:= 'AC';
- WHEN 'ZIL 2'
- THEN pastatas := 'BS:laborat1';
- WHEN 'ZIL 6'
- THEN pastatas := 'BS:įstaiga';
- WHEN 'RAP 7'
- THEN pastatas := 'RAP7';
- WHEN 'STU 13'
- THEN pastatas := 'STU13';
- WHEN 'STU 15'
- THEN pastatas := 'STU15';
- WHEN 'STU 15a'
- THEN pastatas := 'STU15a';
- WHEN 'STU 15b'
- THEN pastatas := 'STU15b';
- WHEN 'STU 9'
- THEN pastatas := 'STU9';
- WHEN 'UNI 10'
- THEN pastatas := 'UNI10';
- WHEN 'VIL 8'
- THEN pastatas := 'VIL8';
- WHEN 'ŽŪ CR'
- THEN pastatas := 'ŽŪCR';
- WHEN 'DAUK 28'
- THEN pastatas := 'DAUK28';
- WHEN 'DON 60'
- THEN pastatas := 'DON60';
- WHEN 'LA 4'
- THEN pastatas := 'LA4';
- WHEN 'LA 53'
- THEN pastatas := 'LA53';
- WHEN 'OZE 18'
- THEN pastatas := 'OZE18';
- WHEN 'VLN 29'
- THEN pastatas := 'VLN29';
- ELSE
- pastatas := 'nera atitikimo';
- END CASE;
- -- room_name := REGEXP_SUBSTR(r.room, '([0-9]{2})\w+');
- room_name := TRIM(r.room);
- -- Kelianat neauditorine: pakeiciam room name ir room_id Selecte LIKE vietoj =
- -- room_name := 'Neauditorinė';
- -- IF LENGTH(room_name) = 2 THEN
- -- IF room_name = 'Neauditorinė' THEN
- -- GET ROOM_ID
- BEGIN
- SELECT DISTINCT pt.id INTO room_id FROM luadm.pp_pastatai pst
- JOIN luadm.pp_patalpos pt ON pt.pastatai_id = pst.id
- JOIN luadm.pp_pertvaros pr ON pr.patalpos_id = pt.id
- WHERE pt.nr = room_name /*LIKE '%' || room_name || '%'*/
- AND pst.kodas = pastatas
- AND pr.busena IN ('PD0101')
- AND pr.tipas IN ('PD0202', 'PD0203', 'PD0204', 'PD0205','PD0206')
- FETCH FIRST 1 ROWS ONLY;
- --AND pt.nr = pr.nr;
- EXCEPTION
- WHEN NO_DATA_FOUND THEN
- BEGIN
- SELECT DISTINCT pt.id INTO room_id FROM luadm.pp_pastatai pst
- JOIN luadm.pp_patalpos pt ON pt.pastatai_id = pst.id
- JOIN luadm.pp_pertvaros pr ON pr.patalpos_id = pt.id
- WHERE pt.nr = room_name /*LIKE '%' || room_name || '%'*/
- AND pst.kodas = pastatas
- AND pr.busena = 'PD0101'
- AND pr.tipas IN ('PD0202', 'PD0203', 'PD0204', 'PD0205','PD0206')
- FETCH FIRST 1 ROWS ONLY;
- EXCEPTION
- WHEN NO_DATA_FOUND THEN
- room_id := NULL;
- END;
- END;
- IF room_id IS NOT NULL THEN
- -- Išvalyti table
- week_id := empty_table;
- -- Clean string from unnecesery symbols and leave clean week symbos with commas
- weeks_with_commas := regexp_replace( regexp_replace( r.week_day , '[[:space:]]*',''),'[^\A-Z]',',' );
- -- Split week string to array
- v_array1 := apex_util.string_to_table( weeks_with_commas, ',' );
- -- Savaitės dienų pavertimas tvarkis sistemos reikalavimus atitinkančiu formatu.
- FOR i IN 1..v_array1.COUNT LOOP
- CASE v_array1(i)
- WHEN 'I'
- THEN week_id(i) := 'W0001';
- time_date(i) := '2019-09-02';
- WHEN 'II'
- THEN week_id(i) := 'W0002';
- time_date(i) := '2019-09-03';
- WHEN 'III'
- THEN week_id(i) := 'W0003';
- time_date(i) := '2019-09-04';
- WHEN 'IV'
- THEN week_id(i) := 'W0004';
- time_date(i) := '2019-09-05';
- WHEN 'V'
- THEN week_id(i) := 'W0005';
- time_date(i) := '2019-09-06';
- WHEN 'VI'
- THEN week_id(i) := 'W0006';
- time_date(i) := '2019-09-07';
- WHEN 'VII'
- THEN week_id(i) := 'W0007';
- time_date(i) := '2019-09-08';
- ELSE
- week_id(i) := 'netiko';
- time_date(i) := 'netiko';
- END CASE;
- END LOOP;
- FOR m IN 1..week_id.COUNT LOOP
- --htp.p(week_id(m));
- -- NULL all time holders
- first_time_start := NULL;
- first_time_end := NULL;
- second_time_start := NULL;
- second_time_end := NULL;
- -- Time Formating
- IF r.lecture_time IS NOT NULL THEN
- -- GET time formating counter ( 2 - start and end of one element, 4 - 2 elements )
- time_count := REGEXP_COUNT( REGEXP_REPLACE( r.lecture_time , '[[:space:]]*','') , '([0-9]{1,2})(.)([0-9]{1,2})' );
- IF time_count = 2 THEN
- first_time_start := REGEXP_SUBSTR( REGEXP_REPLACE( r.lecture_time , '[[:space:]]*','') , '([0-9]{1,2})(.)([0-9]{1,2})' , 1 , 1 );
- first_time_end := REGEXP_SUBSTR( REGEXP_REPLACE( r.lecture_time , '[[:space:]]*','') , '([0-9]{1,2})(.)([0-9]{1,2})' , 1 , 2 );
- ELSIF time_count = 4 THEN
- first_time_start := REGEXP_SUBSTR( REGEXP_REPLACE( r.lecture_time , '[[:space:]]*','') , '([0-9]{1,2})(.)([0-9]{1,2})' , 1 , 1 );
- first_time_end := REGEXP_SUBSTR( REGEXP_REPLACE( r.lecture_time , '[[:space:]]*','') , '([0-9]{1,2})(.)([0-9]{1,2})' , 1 , 2 );
- second_time_start := REGEXP_SUBSTR( REGEXP_REPLACE( r.lecture_time , '[[:space:]]*','') , '([0-9]{1,2})(.)([0-9]{1,2})' , 1 , 3 );
- second_time_end := REGEXP_SUBSTR( REGEXP_REPLACE( r.lecture_time , '[[:space:]]*','') , '([0-9]{1,2})(.)([0-9]{1,2})' , 1 , 4 );
- END IF;
- IF week_id(m) != 'netiko' THEN
- --htp.p('');
- -- Check what length time is ( 15:00 or 8:00 ) and depending on that adds attributes
- IF LENGTH(first_time_start) = 5 THEN
- first_time_start := time_date(m) || 'T' || REGEXP_REPLACE( first_time_start , '[^\0-9]' , ':' ) || ':00';
- ELSE
- first_time_start := time_date(m) || 'T0' || REGEXP_REPLACE( first_time_start , '[^\0-9]' , ':' ) || ':00';
- END IF;
- IF LENGTH(first_time_end) = 5 THEN
- first_time_end := time_date(m) || 'T' || REGEXP_REPLACE( first_time_end , '[^\0-9]' , ':' ) || ':00';
- ELSE
- first_time_end := time_date(m) || 'T0' || REGEXP_REPLACE( first_time_end , '[^\0-9]' , ':' ) || ':00';
- END IF;
- IF second_time_start IS NOT NULL THEN
- IF LENGTH(second_time_start) = 5 THEN
- second_time_start := time_date(m) || 'T' || REGEXP_REPLACE( second_time_start , '[^\0-9]' , ':' ) || ':00';
- ELSE
- second_time_start := time_date(m) || 'T0' || REGEXP_REPLACE( second_time_start , '[^\0-9]' , ':' ) || ':00';
- END IF;
- END IF;
- IF second_time_end IS NOT NULL THEN
- IF LENGTH(second_time_end) = 5 THEN
- second_time_end := time_date(m) || 'T' || REGEXP_REPLACE( second_time_end , '[^\0-9]' , ':' ) || ':00';
- ELSE
- second_time_end := time_date(m) || 'T0' || REGEXP_REPLACE( second_time_end , '[^\0-9]' , ':' ) || ':00';
- END IF;
- END IF;
- /*-------------------------------------------------------------------------------------------------------------------------------------*/
- /*------------------------------------------------ INSERTION PART --------------------------------------------------------------------*/
- /*-------------------------------------------------------------------------------------------------------------------------------------*/
- /*htp.p('SELECT count(tr.id) INTO n FROM tvarkis.timetable_records tr
- WHERE tr.time_date = '''||TO_DATE( time_date(m) , 'YYYY-MM-DD' )||''' AND
- tr.time_from = '''||first_time_start||''' AND
- tr.time_until = '''||first_time_end||''' AND
- tr.week_id = '''||week_id(m)||''' AND
- tr.lecture_room_id = '''||room_id||''' AND
- tr.dalykas_id = '''||r.dalyko_id||''' AND
- tr.subject_type_id = '''||lecture_type||''' AND
- tr.semestr_tkods = '''||semester||''' AND
- tr.destytoj_cilveks_ckods = '''||r.lecturer_id||''' AND
- tr.srauto_id = '''||r.srauto_id||''' AND
- tr.rg_id = '''||r.rg_id||''' AND
- tr.time_inserted < '''||TO_DATE( '2017-02-28' , 'YYYY-MM-DD')||'''');*/
- -- Check if tvark object already exists
- SELECT COUNT(tr.id) INTO n FROM tvarkis.timetable_records tr
- WHERE tr.time_date = TO_DATE( time_date(m) , 'YYYY-MM-DD' ) AND
- tr.time_from = first_time_start AND
- tr.time_until = first_time_end AND
- tr.week_id = week_id(m) AND
- tr.lecture_room_id = room_id AND
- tr.dalykas_id = r.dalyko_id AND
- tr.subject_type_id = lecture_type AND
- tr.semestr_tkods = semester AND
- tr.destytoj_cilveks_ckods = r.lecturer_id AND
- tr.srauto_id = r.srauto_id AND
- tr.rg_id = r.rg_id AND
- tr.student_id = r.student_id AND
- (tr.STATUS IS NULL OR tr.STATUS = 'NEW') AND
- tr.time_created < TO_DATE( '2019-09-02' , 'YYYY-MM-DD');
- /*htp.br();
- htp.p(n);
- htp.br();
- htp.p(' , Student - '||r.student_id|| ' , Srauto ID - ' ||r.srauto_id );
- htp.br();
- htp.p(TO_DATE( time_date(m) , 'YYYY/MM/DD' ) || ' - ' || first_time_start || ' - ' || first_time_end || ' - ' || week_id(m));
- htp.p (' - ' ||room_id || ' - ' ||r.dalyko_id || ' - ' ||lecture_type || ' - ' ||semester || ' - ' ||r.lecturer_id || ' - ' ||r.student_id );
- htp.p (' - ' || r.lecturer_id2 || ' - ' ||r.lecturer_id3 || ' - ' ||r.lecturer_id4 || ' - ' ||r.lecturer_id5 || ' - ' ||r.lecturer_id6
- || ' - ' ||r.lecturer_id7 || ' - ' ||r.lecturer_id8 || ' - ' ||r.srauto_id || ' - ' ||r.rg_id || ' - ' ||r.lang || ' - ' ||r.lecture_group
- || ' - ' ||r.nuotolinis);
- htp.br();*/
- IF n > 0 THEN
- BEGIN
- UPDATE tvarkis.timetable_records tv
- SET tv.STATUS = 'DELETED'
- WHERE tv.time_from = first_time_start AND
- tv.time_until = first_time_end AND
- tv.week_id = week_id(m) AND
- tv.lecture_room_id = room_id AND
- tv.dalykas_id = r.dalyko_id AND
- tv.subject_type_id = lecture_type AND
- tv.semestr_tkods = semester AND
- tv.destytoj_cilveks_ckods = r.lecturer_id AND
- tv.srauto_id = r.srauto_id AND
- tv.rg_id = r.rg_id AND
- tv.time_created < TO_DATE( '2019-09-02' , 'YYYY-MM-DD');
- COMMIT;
- EXCEPTION WHEN OTHERS THEN
- htp.p('Ivyko Rollback su UPDATE, Srauto ID -> ' || r.srauto_id);
- ROLLBACK;
- END;
- --htp.p('turejo padaryti srauto ID -> ' ||r.srauto_id|| ' DELETED.');
- --RAISE_APPLICATION_ERROR(-20000, 'Test failed'); -- not enough
- n := 0;
- SELECT COUNT(tr.id) INTO n FROM tvarkis.timetable_records tr
- WHERE tr.time_date = TO_DATE( time_date(m) , 'YYYY-MM-DD' ) AND
- tr.time_from = first_time_start AND
- tr.time_until = first_time_end AND
- tr.week_id = week_id(m) AND
- tr.lecture_room_id = room_id AND
- tr.dalykas_id = r.dalyko_id AND
- tr.subject_type_id = lecture_type AND
- tr.semestr_tkods = semester AND
- tr.student_id = r.student_id AND
- tr.destytoj_cilveks_ckods = r.lecturer_id AND
- tr.srauto_id = r.srauto_id AND
- tr.rg_id = r.rg_id AND
- (tr.STATUS IS NULL OR tr.STATUS = 'NEW');
- IF n = 0 THEN
- INSERT INTO tvarkis.timetable_records (
- time_date,time_from,time_until,week_id,lecture_room_id,dalykas_id,
- subject_type_id, semestr_tkods,destytoj_cilveks_ckods,student_id,
- destytoj_cilveks_ckods_2,destytoj_cilveks_ckods_3,destytoj_cilveks_ckods_4,
- destytoj_cilveks_ckods_5,destytoj_cilveks_ckods_6,destytoj_cilveks_ckods_7,
- destytoj_cilveks_ckods_8,srauto_id,rg_id,lang,dalykas_group,nuotolinis,
- STATUS,time_created)
- VALUES (
- TO_DATE( time_date(m) , 'YYYY/MM/DD' ) ,first_time_start,first_time_end,
- week_id(m),room_id,r.dalyko_id,lecture_type,semester,r.lecturer_id,
- r.student_id,r.lecturer_id2,r.lecturer_id3,r.lecturer_id4,r.lecturer_id5,
- r.lecturer_id6,r.lecturer_id7,r.lecturer_id8,r.srauto_id,r.rg_id, r.lang,
- r.lecture_group,r.nuotolinis,NULL,SYSDATE);
- COMMIT;
- new_inserted := new_inserted + 1;
- END IF;
- ELSE
- SELECT COUNT(tr.id) INTO n FROM tvarkis.timetable_records tr
- WHERE tr.time_date = TO_DATE( time_date(m) , 'YYYY-MM-DD' ) AND
- tr.time_from = first_time_start AND
- tr.time_until = first_time_end AND
- tr.week_id = week_id(m) AND
- tr.lecture_room_id = room_id AND
- tr.dalykas_id = r.dalyko_id AND
- tr.subject_type_id = lecture_type AND
- tr.semestr_tkods = semester AND
- tr.student_id = r.student_id AND
- tr.destytoj_cilveks_ckods = r.lecturer_id AND
- tr.srauto_id = r.srauto_id AND
- tr.rg_id = r.rg_id AND
- (tr.STATUS IS NULL OR tr.STATUS = 'NEW');
- IF n = 0 THEN
- INSERT INTO tvarkis.timetable_records (
- time_date,time_from,time_until,week_id,lecture_room_id,dalykas_id,
- subject_type_id, semestr_tkods,destytoj_cilveks_ckods,student_id,
- destytoj_cilveks_ckods_2,destytoj_cilveks_ckods_3,destytoj_cilveks_ckods_4,
- destytoj_cilveks_ckods_5,destytoj_cilveks_ckods_6,destytoj_cilveks_ckods_7,
- destytoj_cilveks_ckods_8,srauto_id,rg_id,lang,dalykas_group,nuotolinis,
- STATUS,time_created)
- VALUES (
- TO_DATE( time_date(m) , 'YYYY/MM/DD' ) ,first_time_start,first_time_end,
- week_id(m),room_id,r.dalyko_id,lecture_type,semester,r.lecturer_id,
- r.student_id,r.lecturer_id2,r.lecturer_id3,r.lecturer_id4,r.lecturer_id5,
- r.lecturer_id6,r.lecturer_id7,r.lecturer_id8,r.srauto_id,r.rg_id, r.lang,
- r.lecture_group,r.nuotolinis,NULL,SYSDATE);
- COMMIT;
- new_inserted := new_inserted + 1;
- END IF;
- END IF;
- /*------------------------------------------------*/
- /*----- SECOND TIME -------------*/
- IF second_time_start IS NOT NULL THEN
- SELECT COUNT(tr.id) INTO second_count FROM tvarkis.timetable_records tr
- WHERE tr.time_date = TO_DATE( time_date(m) , 'YYYY-MM-DD' ) AND
- tr.time_from = second_time_start AND
- tr.time_until = second_time_end AND
- tr.week_id = week_id(m) AND
- tr.lecture_room_id = room_id AND
- tr.dalykas_id = r.dalyko_id AND
- tr.subject_type_id = lecture_type AND
- tr.semestr_tkods = semester AND
- tr.destytoj_cilveks_ckods = r.lecturer_id AND
- tr.student_id = r.student_id AND
- tr.srauto_id = r.srauto_id AND
- tr.rg_id = r.rg_id AND
- (tr.STATUS IS NULL OR tr.STATUS = 'NEW') AND
- tr.time_created < TO_DATE( '2019-09-02' , 'YYYY-MM-DD');
- IF second_count > 0 THEN
- BEGIN
- UPDATE TVARKIS.Timetable_Records tv2
- SET tv2.STATUS = 'DELETED'
- WHERE tv2.time_from = second_time_start AND
- tv2.time_until = second_time_end AND
- tv2.week_id = week_id(m) AND
- tv2.lecture_room_id = room_id AND
- tv2.dalykas_id = r.dalyko_id AND
- tv2.subject_type_id = lecture_type AND
- tv2.semestr_tkods = semester AND
- tv2.destytoj_cilveks_ckods = r.lecturer_id AND
- tv2.srauto_id = r.srauto_id AND
- tv2.rg_id = r.rg_id AND
- tv2.time_created < TO_DATE( '2019-09-02' , 'YYYY-MM-DD');
- COMMIT;
- EXCEPTION WHEN OTHERS THEN
- htp.p('Ivyko Rollback su UPDATE, Srauto ID -> ' || r.srauto_id);
- ROLLBACK;
- END;
- --htp.p('turejo padaryti srauto ID -> ' ||r.srauto_id|| ' DELETED.');
- --RAISE_APPLICATION_ERROR(-20000, 'Test failed');
- SELECT COUNT(tr.id) INTO second_count FROM tvarkis.timetable_records tr
- WHERE tr.time_date = TO_DATE( time_date(m) , 'YYYY-MM-DD' ) AND
- tr.time_from = second_time_start AND
- tr.time_until = second_time_end AND
- tr.week_id = week_id(m) AND
- tr.lecture_room_id = room_id AND
- tr.dalykas_id = r.dalyko_id AND
- tr.subject_type_id = lecture_type AND
- tr.semestr_tkods = semester AND
- tr.destytoj_cilveks_ckods = r.lecturer_id AND
- tr.student_id = r.student_id AND
- tr.srauto_id = r.srauto_id AND
- tr.rg_id = r.rg_id AND
- (tr.STATUS IS NULL OR tr.STATUS = 'NEW');
- IF second_count = 0 THEN
- INSERT INTO tvarkis.timetable_records (
- time_date,time_from,time_until,week_id,lecture_room_id,dalykas_id,
- subject_type_id, semestr_tkods,destytoj_cilveks_ckods,student_id,
- destytoj_cilveks_ckods_2,destytoj_cilveks_ckods_3,destytoj_cilveks_ckods_4,
- destytoj_cilveks_ckods_5,destytoj_cilveks_ckods_6,destytoj_cilveks_ckods_7,
- destytoj_cilveks_ckods_8,srauto_id,rg_id,lang,dalykas_group,nuotolinis,
- STATUS,time_created)
- VALUES (
- TO_DATE( time_date(m) , 'YYYY/MM/DD' ) ,second_time_start,second_time_end,
- week_id(m),room_id,r.dalyko_id,lecture_type,semester,r.lecturer_id,
- r.student_id,r.lecturer_id2,r.lecturer_id3,r.lecturer_id4,
- r.lecturer_id5,r.lecturer_id6,r.lecturer_id7,r.lecturer_id8,
- r.srauto_id,r.rg_id,r.lang,r.lecture_group,r.nuotolinis,
- NULL,SYSDATE);
- COMMIT;
- new_inserted := new_inserted + 1;
- END IF;
- ELSE
- SELECT COUNT(tr.id) INTO second_count FROM tvarkis.timetable_records tr
- WHERE tr.time_date = TO_DATE( time_date(m) , 'YYYY-MM-DD' ) AND
- tr.time_from = second_time_start AND
- tr.time_until = second_time_end AND
- tr.week_id = week_id(m) AND
- tr.lecture_room_id = room_id AND
- tr.dalykas_id = r.dalyko_id AND
- tr.subject_type_id = lecture_type AND
- tr.semestr_tkods = semester AND
- tr.destytoj_cilveks_ckods = r.lecturer_id AND
- tr.student_id = r.student_id AND
- tr.srauto_id = r.srauto_id AND
- tr.rg_id = r.rg_id AND
- (tr.STATUS IS NULL OR tr.STATUS = 'NEW');
- IF second_count = 0 THEN
- INSERT INTO tvarkis.timetable_records (
- time_date,time_from,time_until,week_id,lecture_room_id,dalykas_id,
- subject_type_id, semestr_tkods,destytoj_cilveks_ckods,student_id,
- destytoj_cilveks_ckods_2,destytoj_cilveks_ckods_3,destytoj_cilveks_ckods_4,
- destytoj_cilveks_ckods_5,destytoj_cilveks_ckods_6,destytoj_cilveks_ckods_7,
- destytoj_cilveks_ckods_8,srauto_id,rg_id,lang,dalykas_group,nuotolinis,
- STATUS,time_created)
- VALUES (
- TO_DATE( time_date(m) , 'YYYY/MM/DD' ) ,second_time_start,second_time_end,
- week_id(m),room_id,r.dalyko_id,lecture_type,semester,r.lecturer_id,
- r.student_id,r.lecturer_id2,r.lecturer_id3,r.lecturer_id4,
- r.lecturer_id5,r.lecturer_id6,r.lecturer_id7,r.lecturer_id8,
- r.srauto_id,r.rg_id,r.lang,r.lecture_group,r.nuotolinis,
- NULL,SYSDATE);
- COMMIT;
- new_inserted := new_inserted + 1;
- END IF;
- END IF;
- END IF;
- END IF;
- ELSE
- htp.p('LECTURE TIME IS NULL . Nepavyko ikelti sito : Srauto ID ->'||r.srauto_id||' , Student ID -> '||r.student_id||', Time -> '||r.lecture_time||', Room -> '||r.room||', Building ->'||r.building);
- htp.br();
- END IF;
- END LOOP;
- ELSE
- failed := failed + 1;
- htp.p('NERADO AUDITORIJOS ATITIKMENS . Nepavyko ikelti sito : Srauto ID ->'||r.srauto_id||' , Student ID -> '||r.student_id||', Time -> '||r.lecture_time||', Room -> '||r.room||', Building ->'||r.building);
- htp.br();
- END IF;
- /* ELSE
- failed := failed + 1;
- htp.p('DAUGIAU NEI VIENA AUDITORIJA SK != 3 . Nepavyko ikelti sito : Srauto ID ->'||r.srauto_id||' , Student ID -> '||r.student_id||', Time -> '||r.lecture_time||', Room -> '||r.room||', Building ->'||r.building);
- htp.br();
- END IF; */
- END LOOP;
- htp.p('Jau prieš tai įkelti ( dublikatai ) - ' || same || '<br>Naujai įkelti objektai - ' ||new_inserted);
- htp.br('Nepavyko įkelti : '|| failed);
- END insert_from_studis_to_tvarkis;
- -- CURSOR FOR PICKING DATA FROM STUDIS TO TVARKIS
- FUNCTION get_info_from_studis (p_semester_in IN tvarkis.timetable_records.semestr_tkods%TYPE)
- RETURN TV_types.w_cursor AS
- RESULT TV_types.w_cursor;
- BEGIN
- BEGIN
- OPEN RESULT FOR
- SELECT DISTINCT '02/09/2019' AS DATETIME,
- k.sraut AS SRAUTO_ID,
- str.str_time AS TIME_DATE,
- str.str_day AS TIME_FROM_AND_UNTIL,
- str.str_room AS ROOM,
- str.str_building AS BUILDING,
- k.kurs_kkods AS DALYKAS_ID,
- str.str_lecture_type AS PASKAITOS_TYPE,
- k.tips_macg AS SEMESTER,
- k.stud_studkods AS STUDENT,
- reg.rg_short_name AS RG_ID,
- 'lt' AS LANG,
- krs.piezimes AS DALYKAS_GROUP,
- 'ne' AS NUOTOLINES_TYPE,
- (tvarkis.tv_api.get_cilveks_by_darbinieks(darbinieks_kods => str.str_lecturer_id)) AS DESTYTOJ_1 ,
- (tvarkis.tv_api.get_cilveks_by_darbinieks(darbinieks_kods => str.str_lect_id02)) AS DESTYTOJ_2 ,
- (tvarkis.tv_api.get_cilveks_by_darbinieks(darbinieks_kods => str.str_lect_id03)) AS DESTYTOJ_3 ,
- (tvarkis.tv_api.get_cilveks_by_darbinieks(darbinieks_kods => str.str_lect_id04)) AS DESTYTOJ_4 ,
- (tvarkis.tv_api.get_cilveks_by_darbinieks(darbinieks_kods => str.str_lect_id05)) AS DESTYTOJ_5 ,
- (tvarkis.tv_api.get_cilveks_by_darbinieks(darbinieks_kods => str.str_lect_id06)) AS DESTYTOJ_6 ,
- (tvarkis.tv_api.get_cilveks_by_darbinieks(darbinieks_kods => str.str_lect_id07)) AS DESTYTOJ_7 ,
- (tvarkis.tv_api.get_cilveks_by_darbinieks(darbinieks_kods => str.str_lect_id08)) AS DESTYTOJ_8 ,
- NULL AS str_comment
- FROM luadm.karto k
- JOIN regis.rg_streams str ON str.str_id = k.sraut
- JOIN luadm.kurss krs ON krs.kkods = k.kurs_kkods
- JOIN regis.rg_subjects sbj ON sbj.sub_id = str.str_parent
- JOIN regis.rg_registration reg ON reg.rg_id = sbj.sub_registration
- WHERE k.tips_macg = p_semester_in AND
- reg.rg_short_name IN ('2019RR') AND
- k.sraut IS NOT NULL AND
- str.str_time IS NOT NULL AND
- str.str_day IS NOT NULL AND
- str.str_room IS NOT NULL AND
- str.str_building IS NOT NULL AND
- k.kurs_kkods IS NOT NULL AND
- str.str_lecture_type IS NOT NULL AND
- k.stud_studkods IS NOT NULL AND
- str.str_lecturer_id IS NOT NULL AND
- krs.piezimes IS NOT NULL
- --and str.str_building in ('VIL 8')
- AND str.str_building IN ('RAP 7','STU 13','STU 15','STU 15a','STU 15b','STU 9','UNI 10','ŽŪ CR',
- 'JON 66','VIL 8','GED 44','DON 52','CR','ZIL 2','ZIL 6')
- AND str.str_day != 'intensyvus'
- -- AND str.str_location_id IN ('2485')
- -- AND str.str_room IN ('204')
- --AND str.str_building = 'GIM 7'
- -- AND str.str_building IN ('STU39','ŠEV 31')
- -- AND str.str_id IN (78743)
- -- AND str.str_id not in ('70329','71743','70298','70531','70767','71489','71034','71744','71956','71960','70297','70769')
- -- AND str.str_room = '318'
- -- Specific Courses
- --AND krs.kkods_ieks IN ('SOC4013')
- -- 'POP6006','MDM1004','KIN0312','FILN4008','FILN4011','POLN2002','POD5019','MLS5005',' SOC4013')
- --AND rownum < 5
- ORDER BY k.sraut;
- EXCEPTION
- WHEN OTHERS THEN
- RESULT := NULL;
- RETURN RESULT;
- END;
- RETURN RESULT;
- END get_info_from_studis;
- PROCEDURE dublicate_TV_objects (p_semester_in IN tvarkis.timetable_records.semestr_tkods%TYPE)
- IS
- time_from tvarkis.sem_time.time_from%TYPE;
- time_until tvarkis.sem_time.time_until%TYPE;
- r tvarkis.timetable_records%ROWTYPE;
- s SYS_REFCURSOR;
- skip NUMBER DEFAULT 3;
- addWeek NUMBER;
- exist NUMBER;
- exist2 NUMBER;
- startas_plus_weeks VARCHAR2(20);
- end_plus_weeks VARCHAR2(20);
- counter NUMBER;
- txt VARCHAR2(32000);
- ret VARCHAR2(80);
- sraut NUMBER :=0;
- BEGIN
- skip := 0;
- addWeek := 0;
- SELECT s.time_from , s.time_until
- INTO time_from , time_until
- FROM tvarkis.sem_time s
- WHERE s.semester = p_semester_in AND s.is_active = '1';
- s := tvarkis.tv_cursors.get_tvark_objects_by_sem_rg_id(p_semester_in => p_semester_in);
- counter := 0;
- LOOP FETCH s INTO r;
- EXIT WHEN s%NOTFOUND;
- counter := counter + 1;
- htp.p(counter);
- htp.br();
- -- Per nauja nustatome kintamuosius, kurie sustabdo LOOP ciklą.
- addWeek := 0;
- skip := 0;
- IF sraut = 0 THEN
- SELECT COUNT(tr.student_id) INTO sraut FROM tvarkis.timetable_records tr WHERE tr.srauto_id = r.srauto_id;
- ELSE
- sraut := sraut - 1;
- END IF;
- LOOP
- EXIT WHEN skip = 1;
- addWeek := addWeek + 1; -- Vis prideda po savaitę -- buvo 7
- IF time_until >= r.time_date+addWeek THEN
- startas_plus_weeks := REPLACE(to_char(to_date(REPLACE(r.time_from, 'T' , ' '),'yyyy-mm-dd HH24:MI:SS')+addWeek, 'YYYY-MM-DD HH24:MI:SS'), ' ' , 'T');
- end_plus_weeks := REPLACE(to_char(to_date(REPLACE(r.time_until, 'T' , ' '),'yyyy-mm-dd HH24:MI:SS')+addWeek, 'YYYY-MM-DD HH24:MI:SS'), ' ' , 'T');
- exist := 0;
- SELECT COUNT(tr.id) INTO exist FROM tvarkis.timetable_records tr WHERE
- tr.time_date = r.time_date+addWeek AND
- tr.time_from = startas_plus_weeks AND
- tr.time_until = end_plus_weeks AND
- tr.week_id = r.week_id AND
- tr.lecture_room_id = r.lecture_room_id AND
- tr.dalykas_id = r.dalykas_id AND
- tr.subject_type_id = r.subject_type_id AND
- tr.semestr_tkods = r.semestr_tkods AND
- tr.destytoj_cilveks_ckods = r.destytoj_cilveks_ckods AND
- tr.student_id = r.student_id AND
- tr.srauto_id = r.srauto_id AND
- tr.rg_id = r.rg_id AND
- tr.lang = r.lang AND
- tr.dalykas_group = r.dalykas_group AND
- tr.nuotolinis = r.nuotolinis AND
- tr.STATUS IS NULL;
- htp.p('Exists -> '||exist);
- IF exist = 0 THEN -- Patikrina ar nera tokio iraso duomenu bazeje ir jei nera, insertina naujus irasus
- BEGIN
- INSERT INTO tvarkis.timetable_records
- (TIME_DATE,
- TIME_FROM,
- TIME_UNTIL,
- WEEK_ID,
- LECTURE_ROOM_ID,
- DALYKAS_ID,
- SUBJECT_TYPE_ID,
- SEMESTR_TKODS,
- DESTYTOJ_CILVEKS_CKODS,
- STUDENT_ID,
- DESTYTOJ_CILVEKS_CKODS_2,
- DESTYTOJ_CILVEKS_CKODS_3,
- DESTYTOJ_CILVEKS_CKODS_4,
- DESTYTOJ_CILVEKS_CKODS_5,
- DESTYTOJ_CILVEKS_CKODS_6,
- DESTYTOJ_CILVEKS_CKODS_7,
- DESTYTOJ_CILVEKS_CKODS_8,
- SRAUTO_ID,
- RG_ID,
- LANG,
- DALYKAS_GROUP,
- NUOTOLINIS,
- ADD_B_GROUP,
- STATUS,
- INTENSIVE_COURSE,
- TIME_CREATED
- )
- VALUES
- (r.time_date+addWeek,
- startas_plus_weeks,
- end_plus_weeks,
- r.week_id,
- r.lecture_room_id,
- r.dalykas_id,
- r.subject_type_id,
- r.semestr_tkods,
- r.destytoj_cilveks_ckods,
- r.student_id,
- r.destytoj_cilveks_ckods_2,
- r.destytoj_cilveks_ckods_3,
- r.destytoj_cilveks_ckods_4,
- r.destytoj_cilveks_ckods_5,
- r.destytoj_cilveks_ckods_6,
- r.destytoj_cilveks_ckods_7,
- r.destytoj_cilveks_ckods_8,
- r.srauto_id,
- r.rg_id,
- r.lang,
- r.dalykas_group,
- r.nuotolinis,
- r.add_b_group,
- r.STATUS,
- r.intensive_course,
- SYSDATE
- );
- COMMIT;
- EXCEPTION
- WHEN OTHERS THEN
- htp.p(SYS.LOGIN_USER ||' , '|| SYS.INSTANCE_NUM ||' , '|| SYS.DATABASE_NAME ||' , '|| DBMS_UTILITY.FORMAT_ERROR_STACK);
- htp.br();
- htp.p('Nepavyko padubliuoti šio įrašo su Srauto ID : '|| r.srauto_id ||' , Laikas : '||startas_plus_weeks||' , '||end_plus_weeks);
- htp.br();
- ROLLBACK;
- END;
- END IF;
- ELSE
- skip := 1;
- END IF;
- END LOOP;
- END LOOP;
- CLOSE s;
- END dublicate_TV_objects;
- FUNCTION get_tvark_objects_by_sem_rg_id ( p_semester_in IN tvarkis.timetable_records.semestr_tkods%TYPE)
- RETURN TV_types.w_cursor AS
- RESULT TV_types.w_cursor;
- BEGIN
- BEGIN
- OPEN RESULT FOR
- SELECT t.*
- FROM tvarkis.timetable_records t
- JOIN luadm.pp_patalpos pt ON pt.id = t.lecture_room_id
- WHERE t.semestr_tkods = p_semester_in
- AND t.STATUS IS NULL
- AND time_date BETWEEN TO_DATE('2019-09-02', 'YYYY-mm-dd') AND TO_DATE('2019-09-06', 'YYYY-mm-dd')
- AND t.intensive_course IS NULL;
- EXCEPTION
- WHEN OTHERS THEN
- RESULT := NULL;
- RETURN RESULT;
- END;
- RETURN RESULT;
- END get_tvark_objects_by_sem_rg_id;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement