مزامنة البيانات بين قواعد البيانات

بعد التأكد من تحديث قاعدة البيانات الرئيسية (Oracle) عبر ملف السجل في مسار قاعدة البيانات، يتم تشغيل SQL Developer والاتصال بقاعدة البيانات وتشغيل الاستعلامات أدناه.

الاستعلام الرئيسي: جلب بيانات الطلاب من جداول بيانات الطلاب الأساسية:

ACCEPT iacademic_year PROMPT 'Enter academic year and semester in this format: 20172';
select sp.STD_NO, 
Initcap(trim(sp.E_FIRST_NAME))||' '||Initcap(trim(sp.E_SECOND_NAME))||' '||Initcap(trim(sp.E_THIRD_NAME))||' '||Initcap(trim(sp.E_FAMILY_NAME))  as STD_FULL_NAME,
Initcap(trim(sp.A_FIRST_NAME))||' '||Initcap(trim(sp.A_SECOND_NAME))||' '||Initcap(trim(sp.A_THIRD_NAME))||' '||Initcap(trim(sp.A_FAMILY_NAME)) as STD_FULL_NAME_AR, 
SP.MOBILE_NO,SP.HOME_TEL,
UF.FAC_FOREIGN_NAME as Track_EN,
UF.FAC_NATIVE_NAME as TRACK_AR,
UP.PROG_FOREIGN_NAME as Programe_Name_EN,
UP.PROG_NATIVE_NAME as Programe_Name_AR,
SP.ACCEPT_YEAR as BEGIN_YEAR,
sp.accept_sem as BEGIN_SEM,
'' AS LAST_STUD_YEAR,''
'' AS LAST_STUD_SEM,''
'' AS current_status,''
substr(branch_omn.GET_STD_GRADUYEAR(sp.std_no),1,4) as GRADUATE_YEAR,
substr(branch_omn.GET_STD_GRADUYEAR(sp.std_no),5) as GRADUATE_SEM,
BRANCH_OMN.GET_STD_REGCOURSES(sp.std_no,substr(&iacademic_year, 1,4),substr(&iacademic_year, 5)) as Registered_Courses,
BRANCH_OMN.GET_STD_regcoursesHRS(sp.std_no,substr(&iacademic_year, 1,4),substr(&iacademic_year, 5)) as curr_regd_hrs,
BRANCH_OMN.Get_Std_Plan_REQ_Hours_REM(sp.std_no) as left_hours,
branch_omn.GET_STD_COMPLETEDCRS(sp.std_no) as completed_courses,
case when BRANCH_OMN.Get_Std_Plan_REQ_Hours_REM(sp.std_no)<=0 then '' else branch_omn.GET_STD_LEFTCRS(sp.std_no) end as left_courses,''
SP.OUVS_CODE, SP.NATIONAL_NO,
'' as incomp_courses,BRANCH_OMN.GET_STD_TOT_AVG(sp.std_no) as GPA,''
SP.STUDY_NATURE, SP.BIRTH_DATE , SP.GENDER, 
(select STUDY_STATUS from std_status_infos where STD_NO = sp.std_no AND ACADEMIC_YEAR||ACADEMIC_SEM = (select max(ACADEMIC_YEAR||ACADEMIC_SEM) from std_status_infos where STD_NO = sp.std_no)) as study_status,
BRANCH_OMN.get_std_WAR_no (sp.STD_NO) as WARNING_NO, sp.NAT_CODE, NAT.NAT_FOREIGN_DESC AS nationality_en, NAT.NAT_NATIVE_DESC AS nationality_ar,
GET_SPONSOR_TYPE(sp.std_no,substr(&iacademic_year, 1,4),substr(&iacademic_year, 5)) as sponsored,
sp.STD_MAIL, sp.COUNTRY_CODE, sp.CITY_CODE, ADR.CITY_NATIVE_NAME AS city_ar, ADR.CITY_FOREIGN_NAME AS city_en , sp.HOME_ADDRESS, SP.accept_nature,
STUDENT_MANDATORY_CREDIT_HOURS(std_no) AS Mandatory_CH, student_elective_credit_hours(std_no) AS Elective_CH, 
STUDENT_FINISHED_CREDIT_HOURS(std_no, 1) as "Finished MCH", STUDENT_FINISHED_CREDIT_HOURS(std_no, 2)  as "Finished ECH", sp.USER_PASSWORD as sispsw, sp.EMAIL,
E_FIRST_NAME as firstname_en, E_SECOND_NAME as second_name_en, E_THIRD_NAME as third_name_en, E_FAMILY_NAME as lastname_en,
A_FIRST_NAME as firstname_ar, A_SECOND_NAME as second_name_ar, A_THIRD_NAME as third_name_ar, A_FAMILY_NAME as lastname_ar,
SP.ADM_YEAR as ADM_YEAR,
sp.ADM_SEM as ADM_SEM
from std_personal_infos sp 
INNER join HQ.UNV_PROGRAMS up ON sp.fac_code||sp.dept_code||sp.degree_code||sp.prog_code=up.fac_code||up.dept_code||up.degree_code||up.prog_code
INNER JOIN hq.unv_faculties uf ON sp.fac_code=uf.fac_code
LEFT JOIN HQ.UNV_NATIONALITIES NAT ON sp.NAT_CODE = NAT.COUNTRY_CODE 
LEFT JOIN UNV_CITIES ADR ON (ADR.CITY_CODE = sp.CITY_CODE AND ADR.COUNTRY_CODE = sp.COUNTRY_CODE )
ORDER BY sp.STD_NO ASC;

يتم تصدير الملف بصيغة Excel

الاستعلام الثاني: جلب بيانات تسجيل المواد للطلاب:

SELECT RGS_REG_DETAILS.STD_NO, RGS_REG_DETAILS.REG_YEAR, RGS_REG_DETAILS.REG_SEM, RGS_REG_DETAILS.COURSE_CODE, RGS_REG_DETAILS.COURSE_PART, RGS_REG_DETAILS.TOTAL_TMAS, RGS_REG_DETAILS.TOTAL_EXAMS, RGS_REG_DETAILS.TOTAL_FINALS, HQ.UNV_GRADES.GRADE_DESC_FOREIGN AS GRADE, RGS_REG_DETAILS.COURSE_STATUS,
RGS_REG_DETAILS.CLASS_NO, to_char(RGS_REG_DETAILS.CREATION_DATE, 'YYYY-MM-DD HH24:MI:SS') as CREATION_DATE, RGS_REG_DETAILS.CREATED_BY, to_char(RGS_REG_DETAILS.UPDATE_DATE, 'YYYY-MM-DD HH24:MI:SS') as UPDATE_DATE, RGS_REG_DETAILS.UPDATED_BY
FROM RGS_REG_DETAILS LEFT JOIN HQ.UNV_GRADES ON (RGS_REG_DETAILS.GRADE_YEAR = HQ.UNV_GRADES.ISSUE_YEAR AND RGS_REG_DETAILS.GRADE_SEM = HQ.UNV_GRADES.ISSUE_SEM AND RGS_REG_DETAILS.GRADE_CODE = HQ.UNV_GRADES.GRADE_CODE) ORDER BY RGS_REG_DETAILS.CREATION_DATE ASC;

يتم تصدير الملف بصيغة Insert

الاستعلام الثالث: جلب بيانات الحالات الفصلية للطلاب:

SELECT STD_NO, ACADEMIC_YEAR, ACADEMIC_SEM, STUDY_STATUS, REG_STATUS, FIN_STATUS, REG_COUNTER, TOT_GPA, TOT_HOURS FROM std_status_infos ORDER BY ACADEMIC_YEAR ASC, ACADEMIC_SEM ASC;

يتم تصدير الملف بصيغة Insert

الاستعلام الرابع: جلب الجدول الفصلي لكافة المواد:

SELECT SCH_TIMETABLE.SCH_YEAR, SCH_TIMETABLE.SCH_SEM, SCH_TIMETABLE.COURSE_CODE, SCH_TIMETABLE.CLASS_NO, SCH_TIMETABLE.COURSE_PART, 
UNV_HALLS.HALL_NATIVE_NAME AS "HALL_AR", UNV_HALLS.HALL_FOREIGN_NAME AS "HALL_EN", SCH_PERIODS.FREQUENCY, SCH_PERIODS.START_WEEKS,  
SCH_DAYS.DAY_CODE, SCH_DAYS.DAY_NATIVE_NAME, SCH_DAYS.DAY_FOREIGN_NAME, SCH_TIMES.START_TIME, SCH_TIMES.END_TIME
FROM SCH_TIMETABLE 
INNER JOIN UNV_HALLS ON SCH_TIMETABLE.HALL_NO =  UNV_HALLS.HALL_NO
INNER JOIN SCH_PERIODS ON SCH_TIMETABLE.PERIOD_CODE = SCH_PERIODS.PERIOD_CODE 
INNER JOIN SCH_PERIOD_DETAILS ON SCH_PERIODS.PERIOD_CODE = SCH_PERIOD_DETAILS.PERIOD_CODE 
INNER JOIN SCH_TIMES ON SCH_PERIOD_DETAILS.TIME_CODE = SCH_TIMES.TIME_CODE 
INNER JOIN SCH_DAYS ON SCH_PERIOD_DETAILS.DAY_CODE = SCH_DAYS.DAY_CODE 
ORDER BY SCH_TIMETABLE.SCH_YEAR||SCH_TIMETABLE.SCH_SEM ASC;

يتم تصدير الملف بصيغة Insert

====الاستعلام الخامس: جلب بيانات الإرشاد الأكاديمي: ====

SELECT SYS_EMPLOYEES.EMP_NO AS adv_no, STD_NO as collegeid, SYS_EMPLOYEES.EMP_NATIVE_NAME AS adv_name_ar, 
SYS_EMPLOYEES.EMP_FOREIGN_NAME AS adv_name_en, STD_ADVISOR.REG_YEAR as year, STD_ADVISOR.REG_SEM semester 
FROM STD_ADVISOR INNER JOIN SYS_EMPLOYEES ON STD_ADVISOR.EMP_NO = SYS_EMPLOYEES.EMP_NO;

يتم تصدير الملف بصيغة Excel