DROP VIEW IF EXISTS v_cse_book1;
create view v_cse_book1 as
(
with all_stu as (select t.base_semester_id,
                        t.student_user_id
                 from textbook_student_claim t
                 group by t.base_semester_id,
                          t.student_user_id)
select t1.beltcode                                              AS `beltcode`,
       t1.beltname                                              AS `beltname`,
       t5.speccode                                              AS `speccode`,
       t5.specname                                              AS `specname`,
       t6.speccode                                              AS `classcode`,
       t6.specname                                              AS `classname`,
       '教材费'                                                 AS `feeitemname`,
       ifnull((select sum(a.price)
               from textbook_student_claim a
               where a.is_claim = 1
                 and a.base_semester_id = t.base_semester_id
                 and a.student_user_id = t.student_user_id), 0) AS `totalmoney`,
       t7.credential_number                                     AS `Personalid`,
       t7.name                                                  AS `Feeobjname`,
       ''                                                       AS `Remarks`
from all_stu t
         left join pb_semester_config t1 on t1.base_semester_id = t.base_semester_id
         left join base_student_school_roll t2 on t2.user_id = t.student_user_id
         left join base_class t3 on t3.id = t2.class_id
         left join base_major_set t4 on t4.id = t3.major_set_id
         left join pb_cse_spec1 t5 on t5.specname = t4.name
         left join pb_cse_class1 t6 on t6.specname = t3.name
         left join xjr_user t7 on t7.id = t.student_user_id
where t2.delete_mark = 0
  and t3.delete_mark = 0
  and t4.delete_mark = 0
  and t7.delete_mark = 0
    )