استعرض مصدر مزامنة البيانات بين قواعد البيانات

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

==== الاستعلام الرئيسي: جلب بيانات الطلاب من جداول بيانات الطلاب الأساسية: ====
<syntaxhighlight lang="XQuery">
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;
</syntaxhighlight>

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

==== الاستعلام الثاني: جلب بيانات تسجيل المواد للطلاب: ====
<syntaxhighlight lang="XQuery">
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;
</syntaxhighlight>

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

==== الاستعلام الثالث: جلب بيانات الحالات الفصلية للطلاب: ====
<syntaxhighlight lang="XQuery">
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;
</syntaxhighlight>

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

==== الاستعلام الرابع: جلب الجدول الفصلي لكافة المواد: ====
<syntaxhighlight lang="XQuery">
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;
</syntaxhighlight>

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

==== الاستعلام الخامس: جلب بيانات الإرشاد الأكاديمي: ====
<syntaxhighlight lang="XQuery">
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;
</syntaxhighlight>

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

=== مزامنة البيانات المخزنة إلى نظام قسم القبول والتسجيل: ===
يتم ذلك عبر صفحة الاستيراد عبر الرابط: 

https://www.aou.im/epcms/admin/index.php/module/aou/import/