List <WorkMonthResult> IView_A02BLL.GetMonthWorkData(WorkMonthList model)
{
if (null == model)
{
return(null);
}
List <WorkSQLMonth> list = DBSession.IView_A02DAL.GetMonthWorkData(model);
List <WorkMonthResult> rList = new List <WorkMonthResult>();
if (null == list)
{
return(null);
}
List <int> days = list.Select(s => s.day).Distinct().ToList();
List <WorkSQLMonth> markList = new List <WorkSQLMonth>();
foreach (var day in days)
{
markList = list.Where(s => s.day == day).ToList();
string start = markList[0].hour, end = "";
if (markList.Count > 1 && !markList[0].RowID.Equals(markList[1].RowID))
{
end = markList[1].hour;
}
rList.Add(new WorkMonthResult {
day = day, start_work = start, end_work = end
});
}
return(rList);
}
public object GetWorkMonthList(WorkMonthList model)
{
if (!ModelState.IsValid)
{
return(operateContext.RedirectWebApi(WebResultCode.Exception, GlobalConstant.参数异常.ToString(), false));
}
List <WorkMonthResult> uResult = operateContext.bllSession.View_A02.GetMonthWorkData(model);
if (null == uResult || uResult.Count <= 0)
{
return(operateContext.RedirectWebApi(WebResultCode.Ok, GlobalConstant.数据为空.ToString(), ""));
}
return(operateContext.RedirectWebApi(WebResultCode.Ok, GlobalConstant.数据获取成功.ToString(), uResult));
}
public List <WorkSQLMonth> GetMonthWorkData(WorkMonthList model)
{
sb?.Clear();
sb.Append(string.Format(@"SELECT A.RowID,mark,A0177,day,hour FROM (
SELECT RowID,mark='in',A0177,DAY(A0201) AS day,Datename(HH,A0201)+':'+Datename(MI,A0201) AS HOUR,A0201 FROM
(SELECT row_number() over(partition by T.checkDate order by T.A0201 ASC) as rownum,RowID,A0177,A0201 FROM
(SELECT RowID,A0177,B0001,B0002,A0201,A0202,CONVERT(varchar(100), A0201, 23) AS checkDate FROM dbo.View_A02 WHERE
convert(varchar(7),A0201,120)=@dateStart AND A0177=(SELECT user_identify FROM dbo.T_User WHERE user_guid=@userid)) T )TT
WHERE TT.rownum=1
UNION
SELECT RowID,mark='out',A0177,DAY(A0201) AS DAY,Datename(HH,A0201)+':'+Datename(MI,A0201) AS hour,A0201 FROM
(SELECT row_number() over(partition by T.checkDate order by T.A0201 DESC) as rownum,RowID,A0177,A0201 FROM
(SELECT RowID,A0177,B0001,B0002,A0201,A0202,CONVERT(varchar(100), A0201, 23) AS checkDate FROM dbo.View_A02 WHERE
convert(varchar(7),A0201,120)=@dateStart AND A0177=(SELECT user_identify FROM dbo.T_User WHERE user_guid=@userid)) T )TT
WHERE TT.rownum=1
) A ORDER BY A.A0201;"));
_param?.Clear();
_param.Add("@dateStart", model.work_date);
_param.Add("@userid", model.userid);
DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString(), CommandType.Text, SqlHelper.GetParameters(_param));
return(HCQ2_Common.Data.DataTableHelper.DataTableToIList <WorkSQLMonth>(dt));
}