select distinct stu.*,

dbo.GetClassNameByStudentCode(stu.Code) as ClassName,

dbo.GetCourseNameByStudentCode(stu.Code) as CourseName,

dbo.GetLinkmanByStudentId(stu.Id) as Linkman,

dbo.GetContactByStudentId(stu.Id) as Contact,

case when svr.Linkman is not NULL then svr.Linkman else dbo.GetLinkmanByStudentId(stu.Id) end as VisitLinkman,

case when svr.Contact is not NULL then svr.Contact else dbo.GetContactByStudentId(stu.Id) end as VisitContact,case when u1.Sname is not NULL and u1.Sname<>'' then u1.Sname else u1.Username end as VisitFollowUserName,

CONVERT(CHAR(10), svr.FollowTime, 23) as VisitLastFollowTime,


scl.Name as SchoolAreaName,
svr.ContentMemo as VisitContentMemo,
dict1.Name as StudentStatusName

from FM_Student stu 

left join FM_StudentClass sc on stu.Code=sc.Code

left join FM_Class cls on cls.ClassCode=sc.ClassCode

left join FM_Course cou on cou.Id=cls.CourseId

left join FM_CourseSubject cs on cs.Id=cou.CourseSubjectId

left join FM_StudentContact cc on cc.StudentId=stu.Id

left join FM_SchoolArea scl on scl.Id=stu.SchoolAreaId

left join FM_StudentVisitRecord svr on svr.Id=

(select top 1 svr1.Id from FM_Student stu1

join FM_StudentVisitRecord svr1

on stu1.Code=svr1.Code and stu1.Id=stu.Id order by svr1.Id desc)

left join Sys_User u1 on u1.Id=svr.FollowUserID

left join Sys_Dict dict1 on dict1.Type='{1}' and dict1.Code=stu.StudentStatus

where stu.DelFlg<>{0}

select distinct stu.*,

scl.Name as SchoolAreaName,

dbo.GetClassNameByStudentCode(stu.Code) as ClassName,

dbo.GetCourseNameByStudentCode(stu.Code) as CourseName,

dbo.GetLinkmanByStudentId(stu.Id) as Linkman,

dbo.GetContactByStudentId(stu.Id) as Contact,

case when svr.Linkman is not NULL then svr.Linkman else dbo.GetLinkmanByStudentId(stu.Id) end as VisitLinkman,

case when svr.Contact is not NULL then svr.Contact else dbo.GetContactByStudentId(stu.Id) end as VisitContact,

svr.ContentMemo as VisitContentMemo,

case when u1.Sname is not NULL and u1.Sname<>'' then u1.Sname else u1.Username end as VisitFollowUserName,

CONVERT(CHAR(10), svr.FollowTime, 23) as VisitLastFollowTime,

dict1.Name as StudentStatusName,

((select sum(pf.PayAmount)

from FM_PayFlow pf

join FM_Student stu1 on pf.CustomerCode=stu1.Code and stu1.Code=stu.Code) -

(select sum(psf.PayAmount)

from FM_PayStepFlow psf

join FM_PayFlow pf1 on psf.PayFlowId=pf1.Id

join FM_Student stu1 on pf1.CustomerCode=stu1.Code and stu1.Code=stu.Code))  as ArrearageAmount --欠费金额

from FM_Student stu 

left join FM_StudentClass sc on stu.Code=sc.Code

left join FM_Class cls on cls.ClassCode=sc.ClassCode

left join FM_Course cou on cou.Id=cls.CourseId

left join FM_CourseSubject cs on cs.Id=cou.CourseSubjectId

left join FM_StudentContact cc on cc.StudentId=stu.Id

left join FM_SchoolArea scl on scl.Id=stu.SchoolAreaId

left join FM_StudentVisitRecord svr on svr.Id=

(select top 1 svr1.Id from FM_Student stu1

join FM_StudentVisitRecord svr1

on stu1.Code=svr1.Code and stu1.Id=stu.Id order by svr1.Id desc)

left join Sys_User u1 on u1.Id=svr.FollowUserID

left join Sys_Dict dict1 on dict1.Type='{1}' and dict1.Code=stu.StudentStatus

where stu.DelFlg<>{0} and (sc.Status='{2}' or sc.Status is NULL

        and

        (select sum(pf.PayAmount)

        from FM_PayFlow pf

        join FM_Student stu1 on pf.CustomerCode=stu1.Code and stu1.Code=stu.Code) >

        (select sum(psf.PayAmount)

        from FM_PayStepFlow psf

        join FM_PayFlow pf1 on psf.PayFlowId=pf1.Id

        join FM_Student stu1 on pf1.CustomerCode=stu1.Code and stu1.Code=stu.Code)