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)
