实现考勤成功页面持久层SQL语句设计.pdfVIP

4-28实现考勤成功页面（持久层）

140.143.132.225:8000/project-1/doc-76

编写TbCheckinDao.xml中的SQL语句，查询员工当天签到情况、员工请考勤日期总数

1.selectid=searchTodayCheckinparameterType=intresultType=HashMap

2.SELECT

3.u.name,

4.u.photo,

5.d.dept_nameASdeptName,

6.ck.address,

7.CASE

8.WHENck.status=1THEN正常

9.WHENck.status=2THEN

10.ENDASstatus,

11.CASE

12.WHENck.risk=1THEN低风险

13.WHENck.status=2THEN中风险

14.WHENck.status=3THEN高风险

15.ENDASrisk,

16.DATE_FORMAT(ck.create_time,%H:%i)AScheckinTime,

17.ck.date

18.FROMtb_userASu

19.LEFTJOINtb_deptASdONu.dept_id=d.id

20.LEFTJOINtb_checkinASckONu.id=ck.user_idANDck.date=CURRENT_DATE

21.WHEREu.id=#{userId}ANDu.status=1

22./select

23.selectid=searchCheckinDaysparameterType=intresultType=long

24.SELECTCOUNT(*)AScount

25.FROMtb_checkin

26.WHEREuser_id=#{userId}

27./select

28.selectid=searchWeekCheckinparameterType=HashMapresultType=HashMap

29.SELECT

30.CAST(dateASCHAR)ASdate,

31.IF(status=1,正常,)ASstatus

32.FROMtb_checkin

33.WHEREuser_id=#{userId}ANDdateWEEN#{startDate}AND#{endDate}

34./select

编写TbCheckinDao.java接口中的抽象方法。

1.@Mapper

2.publicinterfaceTbCheckinDao{

3.……

4.publicHashMapsearchTodayCheckin(intuserId);

5.publiclongsearchCheckinDays(intuserId);

6.publicArrayListHashMapsearchWeekCheckin(HashMapparam);

7.}

编写TbHolidaysDao.xml文件，添加查询语句。

4‑28实现考勤成功页面（持久层）

140.143.132.225:8000/project‑1/doc‑76

编