api/src/main/resources/sqlScript/学生异动视图.sql

-- 正式环境视图
DROP VIEW IF EXISTS cse_feeobj_change1;
CREATE VIEW cse_feeobj_change1 AS
SELECT DISTINCT '1001' AS Pk_corp,NULL AS remarks,t1.user_id AS pkfeeobj,t5.code AS classcode,t6.code AS pkspec,
IF(t1.change_type = 'student_type',t1.before_data,NULL) AS resourcecode, 
IF(t1.change_type = 'stduy_status',t1.before_data,NULL) AS quartercode,t8.code AS deptcode,
NULL AS enteryear, NULL AS leaveyear,NULL AS ratetypecode,
IF(t1.change_type = 'archives_status',t1.before_data,NULL) AS state,
t11.code AS newclasscode,t12.code AS newpkspec,
IF(t1.change_type = 'student_type',t1.after_data,NULL) AS newresourcecode, 
IF(t1.change_type = 'stduy_status',t1.after_data,NULL) AS newquartercode,
t14.code AS newdeptcode,NULL AS newenteryear, NULL AS newleaveyear,NULL AS newratetypecode,
IF(t1.change_type = 'archives_status',t1.after_data,NULL) AS newstate,
t16.name AS operator,t1.create_date AS opertordate,t1.id AS pkfeeobjchange,t2.credential_number AS Personalid,t2.name AS Feeobjname,
t17.student_id AS Studentcode,NULL AS biztype,NULL AS delflag,NULL AS updateflag
FROM student_change_record t1
INNER JOIN xjr_user t2 ON t1.user_id = t2.id
LEFT JOIN base_student_school_roll t3 ON t1.user_id = t3.user_id
LEFT JOIN base_class t5 ON t1.before_data_code = t5.id AND t1.change_type = 'change_class'
LEFT JOIN base_major_set t6 ON t5.major_set_id = t6.id
LEFT JOIN xjr_department t8 ON t5.org_id = t8.id AND t1.change_type = 'change_class'
LEFT JOIN base_class t11 ON t1.after_data_code = t11.id AND t1.change_type = 'change_class'
LEFT JOIN base_major_set t12 ON t11.major_set_id = t12.id
LEFT JOIN xjr_department t14 ON t11.org_id = t14.id AND t1.change_type = 'change_class'
LEFT JOIN xjr_user t16 ON t16.id = t1.create_user_id
INNER JOIN base_student t17 ON t1.user_id = t17.user_id
WHERE t1.delete_mark = 0 AND t1.enabled_mark = 1 ;


-- 中台数据表
CREATE TABLE `test_cse_feeobj_change1`(
           `Pk_corp`  CHAR(4) DEFAULT NULL,
         `remarks`  VARCHAR(255) DEFAULT NULL,
         `pkfeeobj`  VARCHAR(40)  DEFAULT NULL,
         `classcode`  VARCHAR(20)  DEFAULT NULL,
         `pkspec`  VARCHAR(40) DEFAULT NULL,
         `resourcecode`  VARCHAR(30) DEFAULT NULL,
         `quartercode`  VARCHAR(30) DEFAULT NULL,
         `deptcode`  VARCHAR(30) DEFAULT NULL,
         `enteryear`  VARCHAR(10) DEFAULT NULL,
         `leaveyear`  VARCHAR(10) DEFAULT NULL,
         `ratetypecode`  VARCHAR(30) DEFAULT NULL,
         `state`  VARCHAR(50) DEFAULT NULL,
         `newclasscode`  VARCHAR(40) DEFAULT NULL,
         `newpkspec`  VARCHAR(40) DEFAULT NULL,
         `newresourcecode`  VARCHAR(30) DEFAULT NULL,
         `newquartercode`  VARCHAR(30) DEFAULT NULL,
         `newdeptcode`  VARCHAR(40) DEFAULT NULL,
         `newenteryear`  VARCHAR(10) DEFAULT NULL,
         `newleaveyear`  VARCHAR(10) DEFAULT NULL,
         `newratetypecode`  VARCHAR(30) DEFAULT NULL,
         `newstate`  VARCHAR(10) DEFAULT NULL,
         `operator`  VARCHAR(20) DEFAULT NULL,
         `opertordate`  CHAR(30) DEFAULT NULL,
         `pkfeeobjchange`  VARCHAR(40) DEFAULT NULL,
         `Personalid`  VARCHAR(20) DEFAULT NULL,
         `Feeobjname`  VARCHAR(60) DEFAULT NULL,
         `Studentcode`  VARCHAR(30) DEFAULT NULL,
         `biztype`  VARCHAR(10) DEFAULT NULL,
         `delflag`  VARCHAR(10) DEFAULT NULL,
         `updateflag`  CHAR(1) DEFAULT NULL
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='VIEW';