USE [toponline]
GO
/****** Object: StoredProcedure [dbo].[procT_StudentStudyRecordGetPagedDataList] Script Date: 2018/6/27 16:48:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date: 2018.05.31 15:42
-- Description: 获取学员学习记录列表,通过学员名称,学员手机号,班级id查询
-- EXEC procT_StudentStudyRecordGetPagedDataList 1,20,'','',0,1,0
-- =============================================
ALTER PROCEDURE [dbo].[procT_StudentStudyRecordGetPagedDataList]
(
@StartIndex INT = 0 ,
@EndIndex INT = 0 ,
@UserName VARCHAR(MAX) = '' ,
@UserPhone VARCHAR(MAX) = '' ,
@ClassId INT = 0 ,
@tid INT = 0 , --机构id
@type INT = 0 -- 查询的类型 不分页-0 分页-1
)
AS
BEGIN
-- 先获取学员的信息以及班级的id
SELECT s.Id ,
s.UserId , -- 学员id
person.Name AS UserName , -- 学员名称
tu.Phone , -- 学员手机号
CASE s.Status
WHEN 0 THEN s.EnrollDate
ELSE s.EditTime
END AS enrollDate , -- 报名时间
CASE s.Status
WHEN 0 THEN s.ClassId
ELSE s.TransferToClassId
END AS ClassId , -- 班级id
CONVERT(NVARCHAR(50), '') AS ClassName ,-- 所属班级名称
CONVERT(DATETIME, '') AS RecentStudyTime , -- 最近学习时间
0 AS LiveSectionCount , -- 看直播小节数量
0 AS LiveSectionDuration , -- 看直播分钟
0 AS VideoSectionCount , -- 看录播小节数量
0 AS VideoSectionDuration , -- 看录播分钟
0 AS FaceTime , -- 面授次数
0 AS FaceDuration -- 面授分钟
INTO #TEMP
FROM T_StudentClass s
INNER JOIN topmember..T_User tu ON tu.Id = s.UserId
INNER JOIN topmember..T_Person person ON person.UserId = tu.Id
INNER JOIN dbo.T_Class cla ON cla.Id = s.ClassId
WHERE 1 = 1
AND s.IsDel = 0
AND s.IsUsed = 1
AND s.Status <> 2
AND ( ( s.Status = 0 )
OR ( s.Status = 1
AND s.TransferToClassId = 0
)
)
AND cla.TrainingInstitutionId = @tid
AND ( ( LEN(@UserName) = 0 )
OR ( tu.UserName LIKE '%' + @UserName + '%' )
)
AND ( ( LEN(@UserPhone) = 0 )
OR ( tu.Phone LIKE '%' + @UserPhone + '%' )
)
AND ( ( @ClassId = 0 )
OR ( ClassId = @ClassId )
);
-- 填充班级名称
UPDATE #TEMP
SET ClassName = c.Name
FROM dbo.T_Class c
WHERE #TEMP.ClassId = c.Id;
-- 填充看录播的情况
UPDATE #TEMP
SET RecentStudyTime = VideoRecord.VideoTime , -- 最近看录播时间
VideoSectionCount = VideoRecord.VideoCount ,-- 看录播小节数量
VideoSectionDuration = VideoRecord.VideoSum -- 看录播时间累计(分钟)
FROM ( SELECT videoHis.UserId AS UserId ,
course.ClassId AS ClassId ,
MAX(ISNULL(videoHis.StartTime, '')) AS VideoTime , -- 最近看录播时间
COUNT(DISTINCT videoHis.CourseSectionId) AS VideoCount ,-- 看录播小节数量
SUM(ISNULL(videoHis.TimeView, 0)) AS VideoSum-- 看录播时间累计
FROM dbo.T_UserVideoHistory videoHis
LEFT JOIN T_StudentClass stuClass ON videoHis.UserId = stuClass.UserId
LEFT JOIN dbo.T_Course course ON course.Id = videoHis.CourseId
LEFT JOIN dbo.T_Class cla ON cla.Id = course.ClassId
WHERE 1 = 1
AND videoHis.IsDel = 0
AND videoHis.IsUsed = 1
AND stuClass.Status <> 2
AND cla.TrainingInstitutionId = @tid
GROUP BY videoHis.UserId ,
course.ClassId
) AS VideoRecord
WHERE 1 = 1
AND VideoRecord.UserId = #TEMP.UserId
AND VideoRecord.ClassId = #TEMP.ClassId;
-- 填充看直播情况
UPDATE #TEMP
SET RecentStudyTime = LiveRecord.LiveDate ,-- 最近看直播时间
LiveSectionCount = LiveRecord.LiveCount ,-- 看直播小节数量
LiveSectionDuration = LiveRecord.LiveSum -- 看直播分钟累计
FROM ( SELECT aLog.UserId AS UserId ,
course.ClassId AS ClassId ,
( SELECT MAX(live1.StartTime) AS LiveDate -- 最近看直播时间
FROM dbo.T_VideoLiveUsers live1
LEFT JOIN dbo.T_CourseSection section1 ON live1.CourseSectionId = section1.Id
LEFT JOIN dbo.T_CourseChapter chapter1 ON chapter1.Id = section1.CourseChapterId
LEFT JOIN dbo.T_Course course1 ON chapter1.CourseId = course1.Id
LEFT JOIN dbo.T_Class cla1 ON cla1.Id = course1.ClassId
WHERE cla1.TrainingInstitutionId = @tid
AND course1.ClassId = course.ClassId
AND live1.UserId = aLog.UserId
) AS LiveDate ,-- 最近看直播时间
COUNT(DISTINCT aLog.SectionId) AS LiveCount ,-- 看直播小节数量
SUM(CEILING(CONVERT (DECIMAL, aLog.Duration) / 60)) AS LiveSum -- 看直播分钟累计
FROM T_UserAttendanceLog aLog
LEFT JOIN dbo.T_Course course ON aLog.CourseId = course.Id
LEFT JOIN dbo.T_Class cla ON cla.Id = course.ClassId
WHERE 1 = 1
AND aLog.IsDel = 0
AND aLog.IsUsed = 1
AND aLog.[Type] = 1
AND cla.TrainingInstitutionId = @tid
GROUP BY aLog.UserId ,
course.ClassId
) AS LiveRecord
WHERE 1 = 1
AND LiveRecord.UserId = #TEMP.UserId
AND LiveRecord.ClassId = #TEMP.ClassId;
-- 填充面授情况
UPDATE #TEMP
SET RecentStudyTime = FaceRecourd.FaceTime ,-- 最近面授时间
FaceTime = FaceRecourd.FaceCount ,-- 面授次数
FaceDuration = FaceRecourd.FaceSum -- 面授分钟累计
FROM ( SELECT fLog.UserId AS UserId ,
course.ClassId AS ClassId ,
MAX(fLog.CreateTime) AS FaceTime ,-- 最近面授时间
COUNT(DISTINCT fLog.SectionId) AS FaceCount ,-- 面授次数
SUM(CEILING(CONVERT (DECIMAL, fLog.Duration) / 60)) AS FaceSum -- 面授分钟累计
FROM T_UserAttendanceLog fLog
LEFT JOIN dbo.T_Course course ON fLog.CourseId = course.Id
LEFT JOIN dbo.T_Class cla ON cla.Id = course.ClassId
WHERE 1 = 1
AND fLog.IsDel = 0
AND fLog.IsUsed = 1
AND fLog.[Type] = 2
AND cla.TrainingInstitutionId = @tid
GROUP BY fLog.UserId ,
course.ClassId
) AS FaceRecourd
WHERE 1 = 1
AND FaceRecourd.UserId = #TEMP.UserId
AND FaceRecourd.ClassId = #TEMP.ClassId;
-- 构造完临时表之后进行查询并分页
IF @type = 1
BEGIN
SELECT *
FROM ( SELECT * ,
ROW_NUMBER() OVER ( ORDER BY RecentStudyTime DESC ) AS Num ,
COUNT(1) OVER ( ) AS TotalNum
FROM #TEMP
WHERE RecentStudyTime > CONVERT(DATETIME, '')
) a
WHERE a.Num BETWEEN @StartIndex AND @EndIndex;
END;
ELSE
BEGIN
SELECT *
FROM #TEMP
WHERE RecentStudyTime > CONVERT(DATETIME, '');
END;
DROP TABLE #TEMP;
SET NOCOUNT ON;
END;
SET ANSI_NULLS ON;
GO
/****** Object: StoredProcedure [dbo].[procT_StudentStudyRecordGetPagedDataList] Script Date: 2018/6/27 16:48:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date: 2018.05.31 15:42
-- Description: 获取学员学习记录列表,通过学员名称,学员手机号,班级id查询
-- EXEC procT_StudentStudyRecordGetPagedDataList 1,20,'','',0,1,0
-- =============================================
ALTER PROCEDURE [dbo].[procT_StudentStudyRecordGetPagedDataList]
(
@StartIndex INT = 0 ,
@EndIndex INT = 0 ,
@UserName VARCHAR(MAX) = '' ,
@UserPhone VARCHAR(MAX) = '' ,
@ClassId INT = 0 ,
@tid INT = 0 , --机构id
@type INT = 0 -- 查询的类型 不分页-0 分页-1
)
AS
BEGIN
-- 先获取学员的信息以及班级的id
SELECT s.Id ,
s.UserId , -- 学员id
person.Name AS UserName , -- 学员名称
tu.Phone , -- 学员手机号
CASE s.Status
WHEN 0 THEN s.EnrollDate
ELSE s.EditTime
END AS enrollDate , -- 报名时间
CASE s.Status
WHEN 0 THEN s.ClassId
ELSE s.TransferToClassId
END AS ClassId , -- 班级id
CONVERT(NVARCHAR(50), '') AS ClassName ,-- 所属班级名称
CONVERT(DATETIME, '') AS RecentStudyTime , -- 最近学习时间
0 AS LiveSectionCount , -- 看直播小节数量
0 AS LiveSectionDuration , -- 看直播分钟
0 AS VideoSectionCount , -- 看录播小节数量
0 AS VideoSectionDuration , -- 看录播分钟
0 AS FaceTime , -- 面授次数
0 AS FaceDuration -- 面授分钟
INTO #TEMP
FROM T_StudentClass s
INNER JOIN topmember..T_User tu ON tu.Id = s.UserId
INNER JOIN topmember..T_Person person ON person.UserId = tu.Id
INNER JOIN dbo.T_Class cla ON cla.Id = s.ClassId
WHERE 1 = 1
AND s.IsDel = 0
AND s.IsUsed = 1
AND s.Status <> 2
AND ( ( s.Status = 0 )
OR ( s.Status = 1
AND s.TransferToClassId = 0
)
)
AND cla.TrainingInstitutionId = @tid
AND ( ( LEN(@UserName) = 0 )
OR ( tu.UserName LIKE '%' + @UserName + '%' )
)
AND ( ( LEN(@UserPhone) = 0 )
OR ( tu.Phone LIKE '%' + @UserPhone + '%' )
)
AND ( ( @ClassId = 0 )
OR ( ClassId = @ClassId )
);
-- 填充班级名称
UPDATE #TEMP
SET ClassName = c.Name
FROM dbo.T_Class c
WHERE #TEMP.ClassId = c.Id;
-- 填充看录播的情况
UPDATE #TEMP
SET RecentStudyTime = VideoRecord.VideoTime , -- 最近看录播时间
VideoSectionCount = VideoRecord.VideoCount ,-- 看录播小节数量
VideoSectionDuration = VideoRecord.VideoSum -- 看录播时间累计(分钟)
FROM ( SELECT videoHis.UserId AS UserId ,
course.ClassId AS ClassId ,
MAX(ISNULL(videoHis.StartTime, '')) AS VideoTime , -- 最近看录播时间
COUNT(DISTINCT videoHis.CourseSectionId) AS VideoCount ,-- 看录播小节数量
SUM(ISNULL(videoHis.TimeView, 0)) AS VideoSum-- 看录播时间累计
FROM dbo.T_UserVideoHistory videoHis
LEFT JOIN T_StudentClass stuClass ON videoHis.UserId = stuClass.UserId
LEFT JOIN dbo.T_Course course ON course.Id = videoHis.CourseId
LEFT JOIN dbo.T_Class cla ON cla.Id = course.ClassId
WHERE 1 = 1
AND videoHis.IsDel = 0
AND videoHis.IsUsed = 1
AND stuClass.Status <> 2
AND cla.TrainingInstitutionId = @tid
GROUP BY videoHis.UserId ,
course.ClassId
) AS VideoRecord
WHERE 1 = 1
AND VideoRecord.UserId = #TEMP.UserId
AND VideoRecord.ClassId = #TEMP.ClassId;
-- 填充看直播情况
UPDATE #TEMP
SET RecentStudyTime = LiveRecord.LiveDate ,-- 最近看直播时间
LiveSectionCount = LiveRecord.LiveCount ,-- 看直播小节数量
LiveSectionDuration = LiveRecord.LiveSum -- 看直播分钟累计
FROM ( SELECT aLog.UserId AS UserId ,
course.ClassId AS ClassId ,
( SELECT MAX(live1.StartTime) AS LiveDate -- 最近看直播时间
FROM dbo.T_VideoLiveUsers live1
LEFT JOIN dbo.T_CourseSection section1 ON live1.CourseSectionId = section1.Id
LEFT JOIN dbo.T_CourseChapter chapter1 ON chapter1.Id = section1.CourseChapterId
LEFT JOIN dbo.T_Course course1 ON chapter1.CourseId = course1.Id
LEFT JOIN dbo.T_Class cla1 ON cla1.Id = course1.ClassId
WHERE cla1.TrainingInstitutionId = @tid
AND course1.ClassId = course.ClassId
AND live1.UserId = aLog.UserId
) AS LiveDate ,-- 最近看直播时间
COUNT(DISTINCT aLog.SectionId) AS LiveCount ,-- 看直播小节数量
SUM(CEILING(CONVERT (DECIMAL, aLog.Duration) / 60)) AS LiveSum -- 看直播分钟累计
FROM T_UserAttendanceLog aLog
LEFT JOIN dbo.T_Course course ON aLog.CourseId = course.Id
LEFT JOIN dbo.T_Class cla ON cla.Id = course.ClassId
WHERE 1 = 1
AND aLog.IsDel = 0
AND aLog.IsUsed = 1
AND aLog.[Type] = 1
AND cla.TrainingInstitutionId = @tid
GROUP BY aLog.UserId ,
course.ClassId
) AS LiveRecord
WHERE 1 = 1
AND LiveRecord.UserId = #TEMP.UserId
AND LiveRecord.ClassId = #TEMP.ClassId;
-- 填充面授情况
UPDATE #TEMP
SET RecentStudyTime = FaceRecourd.FaceTime ,-- 最近面授时间
FaceTime = FaceRecourd.FaceCount ,-- 面授次数
FaceDuration = FaceRecourd.FaceSum -- 面授分钟累计
FROM ( SELECT fLog.UserId AS UserId ,
course.ClassId AS ClassId ,
MAX(fLog.CreateTime) AS FaceTime ,-- 最近面授时间
COUNT(DISTINCT fLog.SectionId) AS FaceCount ,-- 面授次数
SUM(CEILING(CONVERT (DECIMAL, fLog.Duration) / 60)) AS FaceSum -- 面授分钟累计
FROM T_UserAttendanceLog fLog
LEFT JOIN dbo.T_Course course ON fLog.CourseId = course.Id
LEFT JOIN dbo.T_Class cla ON cla.Id = course.ClassId
WHERE 1 = 1
AND fLog.IsDel = 0
AND fLog.IsUsed = 1
AND fLog.[Type] = 2
AND cla.TrainingInstitutionId = @tid
GROUP BY fLog.UserId ,
course.ClassId
) AS FaceRecourd
WHERE 1 = 1
AND FaceRecourd.UserId = #TEMP.UserId
AND FaceRecourd.ClassId = #TEMP.ClassId;
-- 构造完临时表之后进行查询并分页
IF @type = 1
BEGIN
SELECT *
FROM ( SELECT * ,
ROW_NUMBER() OVER ( ORDER BY RecentStudyTime DESC ) AS Num ,
COUNT(1) OVER ( ) AS TotalNum
FROM #TEMP
WHERE RecentStudyTime > CONVERT(DATETIME, '')
) a
WHERE a.Num BETWEEN @StartIndex AND @EndIndex;
END;
ELSE
BEGIN
SELECT *
FROM #TEMP
WHERE RecentStudyTime > CONVERT(DATETIME, '');
END;
DROP TABLE #TEMP;
SET NOCOUNT ON;
END;
SET ANSI_NULLS ON;