public List <Infrastructure.ValueObject.SignInEvent> GetMutipEventData(Infrastructure.DataTransferObject.SignInEventParam param, bool IsAdminRole)
{
if (!string.IsNullOrEmpty(param.CircleKey))
{
//int learningId = cacheService.GetCircle(param.CircleKey).Id;
}
else
{
// 目前活動都是在同一個學習圈內發生，直接以第一個點名活動來判斷是否有管理權限
var eventId = param.EventIds.First();
var objEvent = _uow.EntityRepository <ActRollCall>().GetFirst(x => x.EventId == eventId);
}
// 取得點名活動
var list = signInLogService.GetSignInData(param).ToList();
//有管理權限，統計各狀態數量
if (IsAdminRole)
{
foreach (var data in list)
{
data.sOuterKey = Utility.OuterKeyHelper.GuidToPageToken(data.OuterKey);
var counts = data.Logs.GroupBy(x => x.Status);
var status = Utility.ParaCondition.AttendanceState.Status;
foreach (var s in status)
{
var sum = new Infrastructure.ValueObject.SignStatus {
Name = s.Value, Value = s.Key, Count = 0
};
var log = counts.SingleOrDefault(x => x.Key == s.Key);
if (log != null)
{
sum.Count = log.Count();
}
data.SignInCount.Add(sum);
}
}
}
else
{
foreach (var signIn in list)
{
//非管理者，不顯示手動簽到驗證碼
signIn.SignInPwd = "";
signIn.sOuterKey = Utility.OuterKeyHelper.GuidToPageToken(signIn.OuterKey);
//是否參與該次活動
if (signIn.Logs.FirstOrDefault().Status == Utility.ParaCondition.AttendanceState.NoNeed)
{
signIn.IsNoAuth = true;
}
}
}
return(list);
}
public Infrastructure.ValueObject.SignInEvent GetSignInEvent(Guid eventId, int?memberId = null)
{
var db = _uow.DbContext;
var activityInfo = db.Activitys.FirstOrDefault(t => t.OuterKey == eventId);
if (activityInfo != null)
{
if (activityInfo.CardisShow == true)
{
memberId = memberId.HasValue ? memberId.Value : activityInfo.CreateUser;
var memberInfo = db.Members.Find(memberId);
//取得課內權限
var checkMemberAuth = (from cmr in db.CircleMemberRoleplay
join lr in db.LearningRole on cmr.RoleId equals lr.Id
join lc in db.LearningCircle on cmr.CircleId equals lc.Id
where cmr.MemberId == memberId.Value && lc.LearningOuterKey.ToLower() == activityInfo.ToRoomId.ToLower()
select lr).FirstOrDefault();
//如果沒有課內權限，也不是SmartTA
if (checkMemberAuth == null && memberInfo.RoleName != "3")
{
return(null);
}
//如果沒有課內權限，那一定是smartTA，所以true
var isAdminRole = checkMemberAuth != null ? checkMemberAuth.IsAdminRole : true;
var param = new Infrastructure.DataTransferObject.SignInEventParam()
{
IsAdminRole = isAdminRole, EventIds = new List <Guid>()
{
eventId
}, MemberId = memberId.Value, CircleKey = activityInfo.ToRoomId, NotDeleted = true, Pages = 1, Rows = 20
};
return(GetMutipEventData(param, isAdminRole).FirstOrDefault());
}
else
{
return(null);
}
}
else
{
return(null);
}
}
public IEnumerable <SignInData> GetSignInData(Infrastructure.DataTransferObject.SignInEventParam param)
{
#region //SQL
StringBuilder sql = new StringBuilder();
sql.Append(@"select m.*, Sort=CAST(ROW_NUMBER() OVER(ORDER BY ms.manscore_type,ms.manscore_collno,ms.manscore_grade DESC,ms.manscore_grp,isnull(ms.manscore_subgrp,''),ms.man_no) AS INT)
from
(
select distinct s.Id, a.ToRoomId, a.ModuleKey, OuterKey=s.EventId, CreatorAccount=m.Account, CreatorName=m.Name,CreatorPhoto=m.Photo, a.Created_Utc, a.Updated_Utc, a.Deleted_Utc,a.ActivityDate
, Convert(bit, case when s.id=(SELECT max(id) FROM [dbo].[ActRollCalls] where LearningId=s.LearningId) then 1 else 0 end) as 'IsNewest'
, a.StartDate, a.Duration, ActivityId=a.Id, s.Name, s.SignInKey, s.SignInPwd, StuId=mem.Id, StudId=mem.Account, StudName=mem.Name, StudPhoto=mem.Photo
, case when l.[Status] is null then '-1' else l.[Status] end [Status]
, al.[Status] as LeaveStatus
, al.[EventId] as LeaveEventId
, l.[Time], LogCreator=c.Account, LogUpdateDate=l.Updated_Utc
, a.[Publish_Utc]
from [dbo].[Activitys] a
left outer join [dbo].[ActRollCalls] s on a.OuterKey=s.EventId
left outer join [dbo].[MembersView] m on a.CreateUser=m.Id
left outer join [LearningRoles] AS lr ON lr.LearningId = s.LearningId
left outer join [CircleMemberRoleplays] AS cr ON cr.RoleId=lr.Id
left outer join [LearningAuths] AS la ON cr.RoleId = la.[LearningRoleId]
left outer join [ModuleFuntions] AS mf ON la.[FunctionId] = mf.[Id]
left outer join [dbo].[MembersView] mem on cr.MemberId = mem.Id
left outer join [dbo].[ActRollCallLogs] l on l.RollCallId=s.Id and l.StudId=mem.Id
--left outer join dbo.AttendanceLeaves al on s.LearningId = al.LearningId and al.studid=l.studid and a.ActivityDate >=al.LeaveDate and a.ActivityDate < dateadd(d,1,al.LeaveDate) and al.status !=40
left outer join [dbo].[AttendanceLeaves] al on s.LearningId = al.LearningId and al.studid=l.studid and Convert(varchar(10),a.ActivityDate,111) =Convert(varchar(10),al.LeaveDate,111) and al.status !=40
left outer join [dbo].[Members] c on l.CreateUser=c.Id
where mf.OutSideKey=@moduleFun
and cr.Enable=1 and la.Enable=1 and mf.Enable=1 and a.CardisShow=1");
#endregion
#region // 參數準備
List <object> sqlParam = new List <object>();
// 模組動作代碼
sqlParam.Add(new SqlParameter("@moduleFun", QueryCondition.SignInFunction.Member));
#region //查詢特定點名活動
// 單筆
if (param.EventIds.Count() == 1)
{
sql.Append(" and a.OuterKey=@eventId");
sqlParam.Add(new SqlParameter("@eventId", param.EventIds[0]));
}
// 多筆
else if (param.EventIds.Count() > 1)
{
sql.Append(" and a.OuterKey in (");
for (int i = 0; i < param.EventIds.Count; i++)
{
string pKey = "@p" + i.ToString();
sql.Append(pKey);
sql.Append(",");
sqlParam.Add(new SqlParameter(pKey, param.EventIds[i]));
}
if (sql.ToString().EndsWith(","))
{
sql.Remove(sql.Length - 1, 1);
}
sql.Append(")");
}
#endregion
#region //查詢特定學習圈內的點名活動
if (!string.IsNullOrEmpty(param.CircleKey))
{
sql.Append(" and a.ToRoomId=@circleKey");
sqlParam.Add(new SqlParameter("@circleKey", param.CircleKey));
}
#endregion
#region //是否有權限看所有人的紀錄(只能看自己的記錄)
if (!param.IsAdminRole)
{
sql.Append(" and mem.Id=@memberId");
sqlParam.Add(new SqlParameter("@memberId", param.MemberId));
}
#endregion
#region //查詢結果是否包含已刪除的點名活動
if (param.NotDeleted)
{
sql.Append(" and a.deleted_Utc is null");
}
#endregion
#endregion
sql.Append(" ) m");
sql.Append(" left outer join [ICAN5].[dbo].[ManScore] ms on m.ToRoomId=ms.course_no and m.StudId=ms.man_no");
sql.Append(" where ms.manscore_mark='10'");
var list = _context.Database.SqlQuery <SignInData>(sql.ToString(), sqlParam.ToArray()).ToList();
return(list);
}
