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) {
        //避免触发时当前时间已过了设置的打卡提醒时间，所以需要用between and 且增加10分钟作为宽限时间
        //上班打卡提醒触发时间期间：在AttentionTimeForCheck 和CheckInDateTime之间
        //下班打卡提醒触发时间期间：因为AttentionTimeForCheck 和CheckInDateTime是相同时间，所以CheckInDateTime+10分钟，作为触发时间期间
        //log.info("workTime:" + workTime);
        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"));
            //log.info("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,telephone from t180252 " +
                    "  where '" + dataTime + "' between AttentionTimeForCheck and  DATEADD(mi,10,CheckInDateTime) \n" +
                    "  and isnull(RemainTimes,0)>0 ";
             //log.info("执行打卡提醒sql-1:" + 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,telephone from t180252 " +
                    "  where getdate() between AttentionTimeForCheck and  DATEADD(mi,10,CheckInDateTime) \n" +
                    "  and isnull(RemainTimes,0)>0 ";
            //log.info("执行打卡提醒sql-2:" + sql);
            return this.jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(T180252.class));
        }
    }

    @Override

    public Integer updateT180252(List<String> seqList,String dbid) {
        String sql="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;";
        log.info("updateT180252:"+sql+"["+dbid+"]");
        return this.jdbcTemplate.queryForObject(sql, Integer.class);
    }
}