package com.yc.api.service;
|
|
import com.yc.action.grid.GridUtils;
|
import com.yc.api.bean.attendance.*;
|
import com.yc.service.BaseService;
|
import com.yc.service.impl.DBHelper;
|
import org.apache.commons.lang3.StringUtils;
|
import org.springframework.dao.EmptyResultDataAccessException;
|
import org.springframework.jdbc.core.BeanPropertyRowMapper;
|
import org.springframework.jdbc.core.RowMapper;
|
import org.springframework.stereotype.Service;
|
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
import java.time.LocalDate;
|
import java.time.LocalTime;
|
import java.time.format.DateTimeFormatter;
|
import java.util.List;
|
import java.util.Map;
|
|
@Service
|
public class AttendanceService extends BaseService implements AttendanceServiceIfc {
|
|
@Override
|
|
public Map saveAttendance(AttendanceBean attendanceBean) {
|
String sql = " set nocount on \n"
|
+ "declare @UserCode varchar(50)=?,\n" +
|
" @CheckInType varchar(500)=?,\n" +
|
|
" @CheckInTime bigint=DATEDIFF(s, '19700101', GETDATE()),\n" +
|
" @CheckInDateTime datetime=getdate(),\n" +
|
" @LocationTitle varchar(200)=?,\n" +
|
" @LocationDetail varchar(200)=?,\n" +
|
" @WifiName varchar(100)=?,\n" +
|
" @Notes varchar(500)=?,\n" +
|
" @WifiMac varchar(100)=?,\n" +
|
" @PhotoPath varchar(200)=?,\n" +
|
" @Longitude varchar(100)=?,\n" +
|
" @EquipmentType varchar(100)=?,\n" +
|
" @EquipmentCode varchar(100)=?,\n" +
|
" @EquipmentUnid varchar(100)=?,\n" +
|
" @Latitude varchar(100)=?,\n" +
|
" @InsertTime datetime=getdate(),\n" +
|
" @RulesDocCode varchar(50)=?,\n" +
|
" @crewName varchar(50)=?\n" +
|
" declare @IsCheckInOfPhoto int,@RulesName nvarchar(100) \n" +
|
" if isnull(@CheckInType,'') = '外出打卡' \n" +
|
" begin \n" +
|
" select @IsCheckInOfPhoto = IsCheckInOfPhoto from t180216h \n" +
|
" if isnull( @IsCheckInOfPhoto,0) = 1 and isnull(@PhotoPath,'') = '' \n" +
|
" begin \n" +
|
" raiserror('由于外出打卡启用了【拍照打卡】选项，请先拍照然后再打卡',16,1) \n" +
|
" return \n" +
|
" end \n" +
|
" end \n" +
|
" if isnull(@CheckInType,'') in('上班打卡','下班打卡') \n" +
|
" begin \n" +
|
" select @IsCheckInOfPhoto = IsCheckInOfPhoto,@RulesName = isnull(RulesName,'') from t180210h where DocCode = @RulesDocCode \n" +
|
" if isnull( @IsCheckInOfPhoto,0) = 1 and isnull(@PhotoPath,'') = '' \n" +
|
" begin \n" +
|
" raiserror('由于【%s】启用了【打卡时必须拍照】选项，请先拍照然后再打卡',16,1,@RulesName) \n" +
|
" return \n" +
|
" end \n" +
|
" end \n" +
|
"" +
|
" begin \n"
|
+ " insert into t180251(usercode,\n" +
|
" checkintype,\n" +
|
" checkintime,\n" +
|
" checkindatetime,\n" +
|
" locationtitle,\n" +
|
" locationdetail,\n" +
|
" wifiname,\n" +
|
" notes,\n" +
|
" wifimac,\n" +
|
" photopath,\n" +
|
" longitude,\n" +
|
" EquipmentType,\n" +
|
" EquipmentCode,\n" +
|
" EquipmentUnid,\n" +
|
" latitude,InsertTime,RulesDocCode,RulesName,RulesFormId,RulesFormType) values (@UserCode,@CheckInType,@CheckInTime,@CheckInDateTime," +
|
" @LocationTitle,@LocationDetail,@WifiName,@Notes,@WifiMac,@PhotoPath,@Longitude,@EquipmentType,@EquipmentCode,@EquipmentUnid,@Latitude,@InsertTime,@RulesDocCode,@RulesName,180210,496)\n"+
|
//----增加用户打卡时使用过的设备列表 180253
|
" update a set a.LastDateTime=getdate(),a.UseTimes=isnull(a.UseTimes,0)+1 from t180253 a where UserCode=@UserCode and EquipmentUnid=@EquipmentUnid \n" +
|
" if @@rowcount=0 \n" +
|
" begin \n" +
|
" insert into t180253(usercode,EquipmentUnid,CreateDateTime,LastDateTime,EquipmentType,EquipmentCode,UseTimes) values (@UserCode,@EquipmentUnid,getdate(),getdate(),@EquipmentType,@EquipmentCode,1)\n" +
|
"end \n" +
|
//------
|
" exec p180251V21 @usercode=@UserCode,@CrewName=@crewName,@checkInTime=@CheckInTime \n"//更新打卡状态
|
+ " end \n" +
|
" declare @CheckStatus varchar(200),@AddressStatus varchar(200) \n" +
|
" exec p180251V2 @Usercode=@UserCode,@CrewName=@crewName,@CheckInTime=@CheckInTime,@CheckStatus=@CheckStatus output ,@AddressStatus =@AddressStatus output \n" +
|
" select @CheckInTime as checkInTime ,@CheckStatus as describe,@AddressStatus as location,convert(varchar(5),@CheckInDateTime,114) as time; \n";
|
return this.getSimpleJdbcTemplate().queryForMap(sql,
|
attendanceBean.getUsercode(), attendanceBean.getCheckinType(),
|
attendanceBean.getLocationTitle(), attendanceBean.getLocationDetail(),
|
attendanceBean.getWifiName(), attendanceBean.getNotes(), attendanceBean.getWifiMac(),
|
attendanceBean.getPhotoPath(), attendanceBean.getLongitude(), attendanceBean.getEquipmentType(), attendanceBean.getEquipmentCode(), attendanceBean.getEquipmentUnid(), attendanceBean.getLatitude(), attendanceBean.getRulesDocCode(),attendanceBean.getCrewName());
|
}
|
|
@Override
|
|
public String updateAttendance(AttendanceBean attendanceBean) {
|
StringBuilder sql = new StringBuilder();
|
sql.append(" set nocount on \n declare @CheckInTime bigint=DATEDIFF(s, '19700101', GETDATE()),@CheckInDateTime datetime=getdate() \n update a set a.usercode='" + attendanceBean.getUsercode() + "'");
|
if (attendanceBean.getNotes() != null) {//表示为修改备注
|
sql.append(" ,a.notes='" + attendanceBean.getNotes() + "'");
|
} else {//表示为更新打卡，需要更新时间
|
sql.append(" ,a.checkintime=@CheckInTime,a.checkindatetime=@CheckInDateTime");
|
}
|
if (attendanceBean.getPhotoPath() != null)
|
sql.append(" ,a.photopath='" + attendanceBean.getPhotoPath() + "'");
|
if (attendanceBean.getEquipmentCode() != null)
|
sql.append(" ,a.EquipmentCode='" + attendanceBean.getEquipmentCode() + "'");
|
if (attendanceBean.getEquipmentUnid() != null)
|
sql.append(" ,a.EquipmentUnid='" + attendanceBean.getEquipmentUnid() + "'");
|
//if(attendanceBean.getExceptionType()!=null),由后台计算生成
|
// sql.append(" ,a.ExceptionType='"+attendanceBean.getExceptionType()+"'");
|
if (attendanceBean.getLatitude() != null)
|
sql.append(" ,a.Latitude='" + attendanceBean.getLatitude() + "'");
|
if (attendanceBean.getLocationDetail() != null)
|
sql.append(" ,a.LocationDetail='" + attendanceBean.getLocationDetail() + "'");
|
if (attendanceBean.getLocationTitle() != null)
|
sql.append(" ,a.LocationTitle='" + attendanceBean.getLocationTitle() + "'");
|
if (attendanceBean.getLongitude() != null)
|
sql.append(" ,a.Longitude='" + attendanceBean.getLongitude() + "'");
|
if (attendanceBean.getWifiMac() != null)
|
sql.append(" ,a.WifiMac='" + attendanceBean.getWifiMac() + "'");
|
if (attendanceBean.getWifiName() != null)
|
sql.append(" ,a.WifiName='" + attendanceBean.getWifiName() + "'");
|
sql.append(" from t180251 a where a.usercode='" + attendanceBean.getUsercode() + "' and a.checkInTime=" + attendanceBean.getCheckInTime() + "\n exec p180251V21 @usercode='" + attendanceBean.getUsercode() + "',@CrewName="+(StringUtils.isNotBlank(attendanceBean.getCrewName())?("'"+attendanceBean.getCrewName()+"'"):null)+",@checkInTime=" + (attendanceBean.getNotes() != null ? attendanceBean.getCheckInTime() : "@CheckInTime"));
|
if (attendanceBean.getNotes() == null) {//表示为修改备注
|
sql.append(" \n select convert(varchar(5),@CheckInDateTime,114)+';'+cast(@CheckInTime as varchar(100)) \n");
|
} else {
|
sql.append("\n select 'ok'");
|
}
|
return this.getSimpleJdbcTemplate().queryForObject(sql.toString(), String.class);
|
}
|
|
@Override
|
public List<Sysdict> getSysdictList(int formId, String fieldId) {
|
return this.jdbcTemplate.query("select interValue,dictvalue,memo from _sysdict where dictid=(select ft from gField where formid=? and fieldid=?) order by sequence asc", new RowMapper<Sysdict>() {
|
@Override
|
public Sysdict mapRow(ResultSet resultSet, int i) throws SQLException {
|
Sysdict sysdict = new Sysdict();
|
sysdict.setInterValue(resultSet.getString("interValue"));
|
sysdict.setDictvalue(resultSet.getString("dictvalue"));
|
sysdict.setMemo(resultSet.getString("memo"));
|
return sysdict;
|
}
|
}, formId, fieldId);
|
}
|
|
@Override
|
public List get43List(int formId, String fieldId) {
|
Map<String, Object> map = this.jdbcTemplate.queryForMap("select SqlScript,SqlWhere from gField where formid=? and fieldid=?", formId, fieldId);
|
String where = GridUtils.prossRowSetDataType_String(map, "SqlWhere");
|
String sql = GridUtils.prossRowSetDataType_String(map, "SqlScript");
|
if (!"".equals(where)) {
|
sql = DBHelper.getSqlWhere(sql, true,"","", where);
|
}
|
return this.jdbcTemplate.queryForList(sql);
|
}
|
|
@Override
|
public List<T180225> get180225List() {
|
return this.jdbcTemplate.query("select lateminuteid,lateminutevalue,Memo from t180225 order by lateminuteid asc ", new BeanPropertyRowMapper<>(T180225.class));
|
}
|
|
@Override
|
public List<T180227> get180227List() {
|
return this.jdbcTemplate.query("select LeaveEarlyMinuteId,LeaveEarlyMinuteValue,Memo from t180227 order by LeaveEarlyMinuteId asc ", new BeanPropertyRowMapper<>(T180227.class));
|
}
|
|
@Override
|
public List<T180224> get180224List() {
|
return this.jdbcTemplate.query("select AttentionForCheckInId,AttentionForCheckInValue,Memo from t180224 order by AttentionForCheckInId asc ", new BeanPropertyRowMapper<>(T180224.class));
|
}
|
|
@Override
|
public List<T180226> get180226List() {
|
return this.jdbcTemplate.query("select AttentionForCheckOutId,AttentionForCheckOutValue,Memo from t180226 order by AttentionForCheckOutId asc ", new BeanPropertyRowMapper<>(T180226.class));
|
}
|
|
@Override
|
public List<T180211> get180211List(String docCode) {
|
|
return this.jdbcTemplate.query("select DocCode,docitem,RowId,DutyDays,FlexibleAttendance,LateMinuteID,LeaveEarlyMinuteId,ArrivedEarlyAndLeftEarly,ArrivedLateAndLeftLate,\n" +
|
"LeaveLateAndArriveLate,AllowLeaveLate,AllowLateOnDuty,CheckInTimeLimit,CheckOutTimeLimit,NoCheckInOnDutyDay,\n" +
|
"BreakTime,StartBreakTime,EndBreakTime,ItemMemo,CrewName from t180211h where DocCode=? order by docitem asc ", new BeanPropertyRowMapper<>(T180211.class), docCode);
|
}
|
|
@Override
|
public List<T180213> get180213List(String docCode) {
|
return this.jdbcTemplate.query("select DocCode,docitem,RowId,CheckInLocation,CheckInAddress,CheckInRangeID,Longitude,Latitude,ItemMemo from t180213h where DocCode=? order by docitem asc ", new BeanPropertyRowMapper<>(T180213.class), docCode);
|
}
|
|
@Override
|
public List<Base180213> get180213ListV2(String docCode) {//用在个人规则数据显示
|
return this.jdbcTemplate.query("select CheckInLocation,CheckInAddress,a.CheckInRangeID,Longitude,Latitude, b.CheckInRangeMetre from t180213h a join t180223 b\n" +
|
"on a.CheckInRangeID=b.CheckInRangeId where DocCode=? order by docitem asc ", new BeanPropertyRowMapper<>(Base180213.class), docCode);
|
}
|
|
@Override
|
public List<T180214> get180214List(String docCode) {
|
return this.jdbcTemplate.query("select DocCode,docitem,RowId,WiFiName,WifiBSSID,ItemMemo from t180214h where DocCode=? order by docitem asc ", new BeanPropertyRowMapper<T180214>(T180214.class), docCode);
|
}
|
|
@Override
|
public List<Base180214> get180214ListV2(String docCode) {
|
return this.jdbcTemplate.query("select WiFiName,WifiBSSID from t180214h where DocCode=? order by docitem asc ", new BeanPropertyRowMapper<>(Base180214.class), docCode);
|
}
|
|
@Override
|
public List<T180212> get180212List(String docCode) {
|
return this.jdbcTemplate.query("select DocCode,docitem,RowId,DetailRowId,StartWorkingTime,EndWorkingTime,ItemMemo from t180212h where DocCode=? order by docitem asc ", new BeanPropertyRowMapper<T180212>(T180212.class), docCode);
|
}
|
|
@Override
|
public List<T180215> get180215List(String docCode) {
|
return this.jdbcTemplate.query("select DocCode,docitem,RowId,SpecialType,SpecialDate,StartWorkingTime,EndWorkingTime,Description,ItemMemo from t180215h where DocCode=? order by docitem asc ", new BeanPropertyRowMapper<T180215>(T180215.class), docCode);
|
}
|
|
@Override
|
public List<T180228> get180228List(String docCode) {
|
return this.jdbcTemplate.query("select DocCode,docitem,RowId,DetailRowId,AllowLeaveLate,AllowLateOnDuty,ItemMemo from t180228h where DocCode=? order by docitem asc ", new BeanPropertyRowMapper<>(T180228.class), docCode);
|
}
|
|
private static String sql180210 = " DocCode,RulesType,RulesName,CheckInStaffList,CheckInTime,CheckInLocation,CheckInWiFi,ReportReceiver,OvertimeTypeid,\n" +
|
"isAllowDutyOvertime,StartDutyOverTime,MinDutyOverTime,MaxDutyOverTime,DutyOvertimeBreakDeductionMethod,isAllowNotDutyOvertime,\n" +
|
"MinNotDutyOverTime,MaxNotDutyOverTime,NotDutyOvertimeBreakDeductionMethod,CheckInWhitelist,AttentionForCheckInID,\n" +
|
"AttentionForCheckOutID,SpecialDay,jsSynchronousChineseHolidays,isCheckInOfPhoto,isCheckInOfFace,isNotePhotoForCheckIn,\n" +
|
"OutOfRangeForCheckIn,isAllowReplacementCheckIn,AllowReplacementCheckInTimes,Administrators,Members,isModificationRulesTypeAndTime,\n" +
|
"isModificationCheckInWhitelist,isModificationCheckInLocation,isModificationOvertimeType,isModificationReportReceiver,\n" +
|
"isModificationOthers,expirationDate,effectiveDate,isChooseShiftAttendance,isAllowTransferAdjustment ";
|
|
@Override
|
|
public T180210VO2 getT180210(String userCode, String ccCode) {
|
//对个人来说，只能取时间在生效之后，失效之前的规则
|
String where = " where (getdate()>effectiveDate and GETDATE()< expirationDate) and ('" + userCode + "' in ( select list from getinstr(CheckInStaffList)) or '" + ccCode + "' in ( select list from getinstr(CheckInStaffList)))";
|
String sql = "declare @CheckDateTime datetime =null ,\n" +
|
"\t @CheckInType varchar(200)=null ,@CheckTips varchar(200)=null\n,@WCcheckTips varchar(200)=null,@LastCheckInTime bigint=null,@autoCheck int=null,@status int=null,@assist int=null,@EarlyLeaveAllowed int=null,@CrewNameShow varchar(50)=null \n" +
|
"exec p180251V1 @UserCode='" + userCode + "',@CheckDateTime=@CheckDateTime output,@CheckInType=@CheckInType output,@WCcheckTips=@WCcheckTips output,@CheckTips=@CheckTips output,@LastCheckInTime=@LastCheckInTime output,@autoCheck=@autoCheck output ,@status=@status output,@assist=@assist output,@EarlyLeaveAllowed=@EarlyLeaveAllowed output,@CrewNameShow=@CrewNameShow output \n" +
|
" if exists(select DocCode from t180210h" + where + ") begin \n" +
|
" \n select " + sql180210 + " ,@CheckDateTime as temp_checkDateTime,@CheckInType as temp_checkInType,@CheckTips as temp_checkTips,@WCcheckTips as temp_wCcheckTips,@LastCheckInTime as temp_checkInTime,@autoCheck as temp_autoCheck,@status as temp_status,@assist as temp_assist,@EarlyLeaveAllowed as temp_earlyLeaveAllowed,@CrewNameShow as temp_crewNameShow from t180210h " + where +
|
" \n end " +
|
" \n else" +
|
" \n begin " +
|
" select @CheckDateTime as temp_checkDateTime,@CheckInType as temp_checkInType,@CheckTips as temp_checkTips,@WCcheckTips as temp_wCcheckTips,@LastCheckInTime as temp_checkInTime,@autoCheck as temp_autoCheck,@status as temp_status,@assist as temp_assist,@EarlyLeaveAllowed as temp_earlyLeaveAllowed,@CrewNameShow as temp_crewNameShow " +
|
" \n end";
|
return this.jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(T180210VO2.class));
|
}
|
|
@Override
|
public List<T180210> getT180210List(String where) {
|
|
return this.jdbcTemplate.query("select " + sql180210 + " from t180210h " + where, new BeanPropertyRowMapper<>(T180210.class));
|
}
|
|
@Override
|
public List<AttendanceBean> get180251List(AttendanceBean attendanceBean) {
|
String sql = "select [usercode],[username],[ccname],[checkindatetimestr],[cccode],[checkintime],CONVERT(varchar(12),checkindatetime,108) as [checkindatetime],[rulesname],[checkintype],[exceptiontype],[locationtitle],[locationdetail],[wifiname],[notes],[wifimac],[inserttime],[photopath],[longitude],[latitude],equipmentType,equipmentCode,isSuspectedOfCheckIn,convert(varchar(5),AttendanceRequestTime,114) as AttendanceRequestTime,CheckStatus from dbo.f180250(? ,? ,? ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ) order by ccname asc,username asc,checkindatetime asc";
|
return this.jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(AttendanceBean.class), attendanceBean.getInsertTime(), attendanceBean.getInsertTime(), attendanceBean.getUsercode());
|
}
|
|
@Override
|
|
public String save180210(String sql) {
|
return this.jdbcTemplate.queryForObject(sql, String.class);
|
}
|
|
@Override
|
|
public void updateToDB(String sql) {
|
this.jdbcTemplate.execute(sql);
|
}
|
|
@Override
|
public T180216 getT180216() {
|
return this.jdbcTemplate.queryForObject("select top 1 ReportReceivingTime,\n" +
|
"isCheckInOfPhoto,isCheckInOfFace,isNotePhotoForCheckIn from t180216h ", new BeanPropertyRowMapper<>(T180216.class));
|
}
|
|
@Override
|
public T180217VO getT180217(String userCode) {
|
try {
|
T180217VO t180217VO = this.jdbcTemplate.queryForObject("select isPriorityOutAttendance,\n" +
|
"isQuickAttendanceOnDuty,isQuickAttendanceOffDuty,isRestOffDuty,ReportReceiverForOnDuty,ReportReceiverForGoOut,\n" +
|
"EquipmentType,EquipmentCode,LastAttendanceDate,isAutoReminderWhenLogin from t180217h where usercode=?", new BeanPropertyRowMapper<>(T180217VO.class), userCode);
|
return t180217VO;
|
} catch (EmptyResultDataAccessException e) {
|
return new T180217VO();
|
}
|
}
|
|
|
@Override
|
public List<T180230> get180230List(String docCode) {
|
return this.jdbcTemplate.query("select DocCode,RowId,CycleName,CycleDay,ItemMemo ,CrewName from t180230h where DocCode=? ", new BeanPropertyRowMapper<>(T180230.class), docCode);
|
}
|
|
@Override
|
public List<T180231> get180231List(String docCode) {
|
return this.jdbcTemplate.query("select DocCode,RowId,UserCode,UserName,CrewDate,CrewName,ItemMemo from t180231h where DocCode=? ", new BeanPropertyRowMapper<>(T180231.class), docCode);
|
}
|
|
@Override
|
public List<T180232> get180232List(String docCode) {
|
return this.jdbcTemplate.query("select DocCode,CycleName from t180232h where DocCode=? ", new BeanPropertyRowMapper<>(T180232.class), docCode);
|
}
|
|
@Override
|
public List<T180252> getT180252List(String workTime,int perMinutes) {
|
//上班打卡提醒触发时间期间：在AttentionTimeForCheck 和CheckInDateTime之间
|
//下班打卡提醒触发时间期间：因为AttentionTimeForCheck 和CheckInDateTime是相同时间，所以CheckInDateTime+10分钟，作为触发时间期间
|
if(StringUtils.isNotBlank(workTime)) {
|
LocalTime parse = LocalTime.parse(workTime, DateTimeFormatter.ofPattern("HH:mm"));
|
parse = parse.minusMinutes(perMinutes);//减去指定分钟，因为上班卡可以提前提醒
|
LocalDate localDate = LocalDate.now();
|
String dataTime = localDate.atTime(parse.getHour(), parse.getMinute()).format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
|
//System.out.println("dataTime:" + dataTime);
|
String sql = "set nocount on \n select Seq,RulesDocCode,RulesName,UserCode,UserName,CheckInDateTime,Title,Description,CreateDate,LastSendTime,RemainTimes,TryTimes,ErrorCode,ErrorMsg,AttentionTimeForCheck,AttentionTypeForCheck from t180252 " +
|
" where '" + dataTime + "' between AttentionTimeForCheck and DATEADD(mi,10,CheckInDateTime) \n" +
|
" and isnull(RemainTimes,0)>0 ";
|
// log.info("执行打卡提醒sql:" + sql);
|
//System.out.println("执行打卡提醒sql:" + sql);
|
return this.jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(T180252.class));
|
}else{
|
String sql = "set nocount on \n select Seq,RulesDocCode,RulesName,UserCode,UserName,CheckInDateTime,Title,Description,CreateDate,LastSendTime,RemainTimes,TryTimes,ErrorCode,ErrorMsg,AttentionTimeForCheck,AttentionTypeForCheck from t180252 " +
|
" where getdate() between AttentionTimeForCheck and DATEADD(mi,10,CheckInDateTime) \n" +
|
" and isnull(RemainTimes,0)>0 ";
|
return this.jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(T180252.class));
|
}
|
}
|
|
@Override
|
|
public Integer updateT180252(List<String> seqList) {
|
return this.jdbcTemplate.queryForObject("set nocount on \n update a set a.LastSendTime=getdate(),a.RemainTimes=isnull(a.RemainTimes,1)-1,a.TryTimes=isnull(a.TryTimes,0)+1 from t180252 a where a.seq in (" + String.join(",", seqList) + ") \n select @@rowcount;", Integer.class);
|
}
|
}
|
