api/src/main/resources/sqlScript/视图信息.sql

DROP VIEW IF EXISTS student_data;
CREATE VIEW student_data AS
SELECT t1.credential_number AS credentialNumber,t2.student_id AS studentId,t3.candidate_number AS candidateNumber,t1.name,t15.name AS gender,
t1.mobile,t1.email,t2.as_name AS asName,t2.en_name AS enName,t2.py_name AS pyName,
t2.former_name AS formerName,t9.name AS credentialType,t1.birth_date AS birthDate,t10.name AS birthType,t11.name AS nation,
t12.name AS bloodType,t13.name AS health,t14.name AS maritalState,t16.name AS politicalState,
t17.name AS nationality,t18.name AS chineseType,t19.name AS houseHoldType,t20.name AS provinces,
t21.name AS city,t22.name AS district,t23.name AS houseProvinces, t24.name AS houseCity,t25.name AS houseDistrict,
t2.belongs_police AS belongsPolice,t26.name AS birthProvinces, t27.name AS birthCity,t28.name AS birthDistrict,t2.house_hold_address AS houseHoldAddress,
t29.name AS residenceType,t2.train_interval AS trainInterval,REPLACE(REPLACE(t2.is_migrate_children,0,'否'),1,'是') AS isMigrateChildren,
REPLACE(REPLACE(t2.is_floating_population,0,'否'),1,'是') AS isFloatingPopulation,t2.height ,t2.weight ,t2.vision ,t2.specialty ,
t6.qq_msn AS qqMsn,t6.wechat,t6.user_page AS userPage,t3.roll_number AS rollNumber,t3.archives_number AS archivesNumber,
t3.enrollment_date AS enrollmentDate,t31.name AS enrollmentType,t8.name AS gradeName,t32.name AS enrollType,t30.name AS majorSetName,
t3.study_year AS studyYear,t7.name AS className,t33.name AS studentSource,t34.name AS studentType,t35.name AS archivesStatus,
t36.name AS learnStatus,t37.name AS stduyStatus,t38.name AS chooseStatus,t39.name AS fosterType,t40.name AS recruitType,
t41.name AS recruitTarget,t42.name AS highestEducation,t3.graduated_university AS graduatedUniversity,t3.graduated_score AS graduatedScore,
t3.examinee_number AS examineeNumber,t3.ticket_number AS ticketNumber,t3.admission_scores AS admissionScores,t3.remark,
REPLACE(REPLACE(t5.is_indemnify,0,'否'),1,'是') AS isIndemnify,REPLACE(REPLACE(t5.is_stipend,0,'否'),1,'是') AS isStipend,t5.stipend_stand AS stipendStand,
t5.stipend_number AS stipendNumber,REPLACE(REPLACE(t5.is_free,0,'否'),1,'是') AS isFree,REPLACE(REPLACE(t5.is_filing_card,0,'否'),1,'是') AS isFilingCard,
t4.zip_code AS zipCode,t4.telephone,t4.contact,t4.population,
t4.address, t4.income_source AS incomeSource,t4.income_number AS incomeNumber, t4.railway_station AS railwayStation,t1.id,t3.grade_id AS gradeId,t3.class_id AS classId,
t2.belongs_police AS belongsPolices,t43.name AS teacherName,t45.room_name AS roomName,t44.bed_number AS bedNumber FROM xjr_user t1
INNER JOIN base_student t2 ON t1.id = t2.user_id
LEFT JOIN base_student_school_roll t3 ON t3.user_id = t1.id
LEFT JOIN base_student_family t4 ON t4.user_id = t1.id AND t4.delete_mark = 0
LEFT JOIN base_student_subsidize t5 ON t5.user_id = t1.id
LEFT JOIN base_student_contact t6 ON t6.id = t1.id
LEFT JOIN base_class t7 ON t7.id = t3.class_id
LEFT JOIN base_grade t8 ON t8.id = t3.grade_id
LEFT JOIN xjr_dictionary_detail t9 ON t1.credential_type = t9.code
LEFT JOIN xjr_dictionary_detail t10 ON t2.birth_type = t10.code
LEFT JOIN xjr_dictionary_detail t11 ON t2.nation = t11.code
LEFT JOIN xjr_dictionary_detail t12 ON t2.blood_type = t12.code
LEFT JOIN xjr_dictionary_detail t13 ON t2.health = t13.code
LEFT JOIN xjr_dictionary_detail t14 ON t2.marital_state = t14.code
LEFT JOIN xjr_dictionary_detail t15 ON t1.gender = t15.code
LEFT JOIN xjr_dictionary_detail t16 ON t2.political_state = t16.code
LEFT JOIN xjr_dictionary_detail t17 ON t2.nationality = t17.code
LEFT JOIN xjr_dictionary_detail t18 ON t2.chinese_type = t18.code
LEFT JOIN xjr_dictionary_detail t19 ON t2.house_hold_type = t19.code
LEFT JOIN xjr_area t20 ON t2.provinces = t20.code
LEFT JOIN xjr_area t21 ON t2.city = t21.code
LEFT JOIN xjr_area t22 ON t2.district = t22.code
LEFT JOIN xjr_area t23 ON t2.house_provinces = t23.code
LEFT JOIN xjr_area t24 ON t2.house_city = t24.code
LEFT JOIN xjr_area t25 ON t2.house_district = t25.code
LEFT JOIN xjr_area t26 ON t2.birth_provinces = t26.code
LEFT JOIN xjr_area t27 ON t2.birth_city = t27.code
LEFT JOIN xjr_area t28 ON t2.birth_district = t28.code
LEFT JOIN xjr_dictionary_detail t29 ON t2.residence_type = t29.code
LEFT JOIN xjr_dictionary_detail t31 ON t3.enrollment_type = t31.code
LEFT JOIN xjr_dictionary_detail t32 ON t3.enroll_type = t32.code
LEFT JOIN base_major_set t30 ON t3.major_set_id = t30.id
LEFT JOIN xjr_dictionary_detail t33 ON t3.student_source = t33.code
LEFT JOIN xjr_dictionary_detail t34 ON t3.student_type = t34.code
LEFT JOIN xjr_dictionary_detail t35 ON t3.archives_status = t35.code
LEFT JOIN xjr_dictionary_detail t36 ON t3.learn_status = t36.code
LEFT JOIN xjr_dictionary_detail t37 ON t3.stduy_status = t37.code
LEFT JOIN xjr_dictionary_detail t38 ON t3.choose_status = t38.code
LEFT JOIN xjr_dictionary_detail t39 ON t3.foster_type = t39.code
LEFT JOIN xjr_dictionary_detail t40 ON t3.recruit_type = t40.code
LEFT JOIN xjr_dictionary_detail t41 ON t3.recruit_target = t41.code
LEFT JOIN xjr_dictionary_detail t42 ON t3.highest_education = t42.code
LEFT JOIN xjr_user t43 ON t43.id = t7.teacher_id
LEFT JOIN room_bed t44 ON t44.student_user_id = t1.id
LEFT JOIN room t45 ON t45.id = t44.room_id
WHERE t1.delete_mark = 0 
AND t1.delete_mark = 0 
AND t2.delete_mark = 0 
AND t3.delete_mark = 0
AND t5.delete_mark = 0
AND t3.archives_status = 'FB2901';


-- 添加学生数据的数据源
INSERT INTO data_expert_source(id,create_user_id,create_date,delete_mark,enabled_mark,sort_code,NAME,view_name,source_type)
VALUE(1713871531, 1000000000000000000, NOW(),0,1,1, '学生数据','student_data','student');


-- 学生数据 -> 基础信息
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1640966400, 1000000000000000000, NOW(),0,1, 1713871531,'credentialNumber','证件号码','基础信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1640966401, 1000000000000000000, NOW(),0,1, 1713871531,'studentId','学号','基础信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1640966402, 1000000000000000000, NOW(),0,1, 1713871531,'candidateNumber','考号','基础信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1640966403, 1000000000000000000, NOW(),0,1, 1713871531,'name','姓名','基础信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1640966404, 1000000000000000000, NOW(),0,1, 1713871531,'gender','性别','基础信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1640966405, 1000000000000000000, NOW(),0,1, 1713871531,'mobile','手机号','基础信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1640966406, 1000000000000000000, NOW(),0,1, 1713871531,'email','邮箱','基础信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1640966407, 1000000000000000000, NOW(),0,1, 1713871531,'asName','别名','基础信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1640966408, 1000000000000000000, NOW(),0,1, 1713871531,'enName','英文名','基础信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1640966409, 1000000000000000000, NOW(),0,1, 1713871531,'pyName','姓名拼音','基础信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1640966410, 1000000000000000000, NOW(),0,1, 1713871531,'formerName','曾用名','基础信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1640966411, 1000000000000000000, NOW(),0,1, 1713871531,'credentialType','证件类型','基础信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1640966412, 1000000000000000000, NOW(),0,1, 1713871531,'birthDate','出生日期','基础信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1640966413, 1000000000000000000, NOW(),0,1, 1713871531,'birthType','生日类型','基础信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1640966414, 1000000000000000000, NOW(),0,1, 1713871531,'nation','民族','基础信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1640966415, 1000000000000000000, NOW(),0,1, 1713871531,'bloodType','血型','基础信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1640966416, 1000000000000000000, NOW(),0,1, 1713871531,'health','健康状况','基础信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1640966417, 1000000000000000000, NOW(),0,1, 1713871531,'maritalState','婚姻状况','基础信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1640966418, 1000000000000000000, NOW(),0,1, 1713871531,'politicalState','政治面貌','基础信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1640966419, 1000000000000000000, NOW(),0,1, 1713871531,'nationality','国籍地区','基础信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1640966420, 1000000000000000000, NOW(),0,1, 1713871531,'chineseType','港澳台侨外','基础信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1640966421, 1000000000000000000, NOW(),0,1, 1713871531,'houseHoldType','户口类别','基础信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1640966422, 1000000000000000000, NOW(),0,1, 1713871531,'provinces','省','基础信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1640966423, 1000000000000000000, NOW(),0,1, 1713871531,'city','市','基础信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1640966424, 1000000000000000000, NOW(),0,1, 1713871531,'district','区/县','基础信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1640966425, 1000000000000000000, NOW(),0,1, 1713871531,'houseProvinces','户口所在地省','基础信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1640966426, 1000000000000000000, NOW(),0,1, 1713871531,'houseCity','户口所在地市','基础信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1640966427, 1000000000000000000, NOW(),0,1, 1713871531,'houseDistrict','户口所在地区/县','基础信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1640966428, 1000000000000000000, NOW(),0,1, 1713871531,'belongsPolices','户口所属派出所','基础信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1640966429, 1000000000000000000, NOW(),0,1, 1713871531,'birthProvinces','出生所在地省','基础信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1640966430, 1000000000000000000, NOW(),0,1, 1713871531,'birthCity','出生所在地市','基础信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1640966431, 1000000000000000000, NOW(),0,1, 1713871531,'birthDistrict','出生所在地/区县','基础信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1640966432, 1000000000000000000, NOW(),0,1, 1713871531,'houseHoldAddress','户籍地址','基础信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1640966433, 1000000000000000000, NOW(),0,1, 1713871531,'residenceType','学生居住地类型','基础信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1640966434, 1000000000000000000, NOW(),0,1, 1713871531,'trainInterval','乘火车区间','基础信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1640966435, 1000000000000000000, NOW(),0,1, 1713871531,'isMigrateChildren','是否随迁子女','基础信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1640966436, 1000000000000000000, NOW(),0,1, 1713871531,'isFloatingPopulation','是否流动人口','基础信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1640966437, 1000000000000000000, NOW(),0,1, 1713871531,'height','身高','基础信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1640966438, 1000000000000000000, NOW(),0,1, 1713871531,'weight','体重','基础信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1640966439, 1000000000000000000, NOW(),0,1, 1713871531,'vision','视力','基础信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1640966440, 1000000000000000000, NOW(),0,1, 1713871531,'specialty','特长','基础信息');

-- 学生数据 -> 联系信息
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872048, 1000000000000000000, NOW(),0,1, 1713871531,'qqMsn','QQ&MSN','联系信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872049, 1000000000000000000, NOW(),0,1, 1713871531,'wechat','微信号','联系信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872050, 1000000000000000000, NOW(),0,1, 1713871531,'userPage','个人主页','联系信息');

-- 学生数据 -> 学籍信息
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872051, 1000000000000000000, NOW(),0,1, 1713871531,'rollNumber','学籍号','学籍信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872163, 1000000000000000000, NOW(),0,1, 1713871531,'archivesNumber','学生档案编号','学籍信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872164, 1000000000000000000, NOW(),0,1, 1713871531,'enrollmentDate','入学年月','学籍信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872165, 1000000000000000000, NOW(),0,1, 1713871531,'enrollmentType','入学方式','学籍信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872166, 1000000000000000000, NOW(),0,1, 1713871531,'gradeName','入学年级','学籍信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872167, 1000000000000000000, NOW(),0,1, 1713871531,'enrollType','入学招生类型','学籍信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872168, 1000000000000000000, NOW(),0,1, 1713871531,'majorSetName','在读专业方向','学籍信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872169, 1000000000000000000, NOW(),0,1, 1713871531,'studyYear','学制','学籍信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872170, 1000000000000000000, NOW(),0,1, 1713871531,'className','班级','学籍信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872171, 1000000000000000000, NOW(),0,1, 1713871531,'studentSource','学生来源','学籍信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872172, 1000000000000000000, NOW(),0,1, 1713871531,'studentType','学生类别','学籍信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872173, 1000000000000000000, NOW(),0,1, 1713871531,'archivesStatus','学籍状态','学籍信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872174, 1000000000000000000, NOW(),0,1, 1713871531,'learnStatus','学习形式','学籍信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872175, 1000000000000000000, NOW(),0,1, 1713871531,'stduyStatus','就读方式','学籍信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872176, 1000000000000000000, NOW(),0,1, 1713871531,'chooseStatus','分流状态','学籍信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872177, 1000000000000000000, NOW(),0,1, 1713871531,'fosterType','分段培养方式','学籍信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872178, 1000000000000000000, NOW(),0,1, 1713871531,'recruitType','招生类型','学籍信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872179, 1000000000000000000, NOW(),0,1, 1713871531,'recruitTarget','招生对象','学籍信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872180, 1000000000000000000, NOW(),0,1, 1713871531,'highestEducation','入学前最高学历','学籍信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872181, 1000000000000000000, NOW(),0,1, 1713871531,'graduatedUniversity','入学前毕业院校','学籍信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872182, 1000000000000000000, NOW(),0,1, 1713871531,'graduatedScore','入学前毕业成绩','学籍信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872183, 1000000000000000000, NOW(),0,1, 1713871531,'examineeNumber','入学考试考生号','学籍信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872184, 1000000000000000000, NOW(),0,1, 1713871531,'ticketNumber','入学考试准考证号','学籍信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872185, 1000000000000000000, NOW(),0,1, 1713871531,'admissionScores','入学考试成绩','学籍信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872186, 1000000000000000000, NOW(),0,1, 1713871531,'remark','备注','学籍信息');

-- 学生数据 -> 资助信息
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872254, 1000000000000000000, NOW(),0,1, 1713871531,'isIndemnify','是否低保户','资助信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872255, 1000000000000000000, NOW(),0,1, 1713871531,'isStipend','是否享受国家助学金','资助信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872256, 1000000000000000000, NOW(),0,1, 1713871531,'stipendStand','助学金发放标准（元）','资助信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872257, 1000000000000000000, NOW(),0,1, 1713871531,'stipendNumber','学生资助卡号','资助信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872258, 1000000000000000000, NOW(),0,1, 1713871531,'isFree','是否免学费','资助信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872259, 1000000000000000000, NOW(),0,1, 1713871531,'isFilingCard','是否建档立卡','资助信息');

-- 学生数据 -> 家庭信息
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872317, 1000000000000000000, NOW(),0,1, 1713871531,'zipCode','家庭邮编','家庭信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872318, 1000000000000000000, NOW(),0,1, 1713871531,'telephone','家庭电话','家庭信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872319, 1000000000000000000, NOW(),0,1, 1713871531,'contact','联系人姓名','家庭信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872320, 1000000000000000000, NOW(),0,1, 1713871531,'population','家庭人口','家庭信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872321, 1000000000000000000, NOW(),0,1, 1713871531,'address','家庭地址','家庭信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872322, 1000000000000000000, NOW(),0,1, 1713871531,'incomeSource','家庭主要收入来源','家庭信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872323, 1000000000000000000, NOW(),0,1, 1713871531,'incomeNumber','家庭月收入（元）','家庭信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872324, 1000000000000000000, NOW(),0,1, 1713871531,'railwayStation','离家最近火车站','家庭信息');



-- 班级总人数、男生人数、女生人数、住读人数、走读人数

-- 收费视图
DROP VIEW IF EXISTS fee_data;
CREATE VIEW fee_data AS 
SELECT    
x1.name AS teacherName,t3.name AS classname,t6.name AS baseSemesterCn,
t.studentcode AS studentId,t.feeobjname AS NAME,t.specname,t.enteryear,
t.leaveyear,t.personalid,t.address,t.telephone,t.state,t.sex,t.factar,t.factrecarmny,t.refundmny,
(CASE WHEN t.qfje > 0 THEN t.qfje ELSE 0 END) AS qfje,
(CASE WHEN t.qfje < 0 THEN ABS(t.qfje) ELSE 0 END) AS ktje,
t.jfzt,t.feeitemname,t.ratetypename,t.resourcename,t.quartername,t.pksfxxytb AS id,
t6.id AS semesterId,t3.id AS classIds
  FROM pb_v_xsxxsfytb  t    
LEFT JOIN xjr_user t1 ON (t1.credential_number = t.personalid) 
LEFT JOIN base_student_school_roll t2 ON (t2.user_id = t1.id) 
LEFT JOIN base_class t3 ON (t3.id = t2.class_id) 
LEFT JOIN base_student t4 ON (t4.user_id = t1.id) 
LEFT JOIN pb_semester_config t5 ON (t5.beltcode = t.beltcode) 
LEFT JOIN base_semester t6 ON (t6.id = t5.base_semester_id) 
LEFT JOIN xjr_user x1 ON x1.id = t3.teacher_id;

INSERT INTO data_expert_source(id,create_user_id,create_date,delete_mark,enabled_mark,sort_code,NAME,view_name,source_type)
VALUE(1713871532, 1000000000000000000, NOW(),0,1,1, '财务数据','fee_data','fee');

INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872325, 1000000000000000000, NOW(),0,1, 1713871532,'teacherName','班主任名称','所有信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872326, 1000000000000000000, NOW(),0,1, 1713871532,'classname','班级名称','所有信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872327, 1000000000000000000, NOW(),0,1, 1713871532,'baseSemesterCn','学期','所有信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872328, 1000000000000000000, NOW(),0,1, 1713871532,'studentId','学号','所有信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872329, 1000000000000000000, NOW(),0,1, 1713871532,'name','姓名','所有信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872330, 1000000000000000000, NOW(),0,1, 1713871532,'specname','所属专业名称','所有信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872331, 1000000000000000000, NOW(),0,1, 1713871532,'enteryear','入学年份','所有信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872332, 1000000000000000000, NOW(),0,1, 1713871532,'state','学籍状态','所有信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872333, 1000000000000000000, NOW(),0,1, 1713871532,'sex','性别','所有信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872334, 1000000000000000000, NOW(),0,1, 1713871532,'factar','应收','所有信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872335, 1000000000000000000, NOW(),0,1, 1713871532,'factrecarmny','实收','所有信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872336, 1000000000000000000, NOW(),0,1, 1713871532,'refundmny','缴费状态','所有信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872337, 1000000000000000000, NOW(),0,1, 1713871532,'qfje','欠费金额','所有信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872338, 1000000000000000000, NOW(),0,1, 1713871532,'ktje','可退金额','所有信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872339, 1000000000000000000, NOW(),0,1, 1713871532,'jfzt','缴费状态','所有信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872340, 1000000000000000000, NOW(),0,1, 1713871532,'feeitemname','项目','所有信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872341, 1000000000000000000, NOW(),0,1, 1713871532,'ratetypename','学制','所有信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872342, 1000000000000000000, NOW(),0,1, 1713871532,'resourcename','贫困类型','所有信息');
INSERT INTO data_expert_source_field(id,create_user_id,create_date,delete_mark,enabled_mark,data_expert_source_id,field_name,show_name,data_group) VALUE(1713872343, 1000000000000000000, NOW(),0,1, 1713871532,'quartername','就读方式','所有信息');