package com.engine.custom.corn.util;
import com.icbc.api.internal.apache.http.impl.cookie.S;
import weaver.conn.RecordSet;
import weaver.conn.RecordSetDataSource;
import weaver.file.Prop;
import weaver.general.BaseBean;
import weaver.general.Util;

import java.time.DayOfWeek;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;

public class ReportUtil {

    private static final BaseBean bb = new BaseBean();

    /**
     * 获取今年发送的消息总数
     * @return
     */
    public static int getMsgCount(){
        try {
            String dateFormat = "yyyy-MM-dd HH:mm:ss.SSS";
            String[] startEndOfYear = getStartEndOfYear(2023,dateFormat);
            System.out.println("Start of Year: " + startEndOfYear[0]);
            System.out.println("End of Year: " + startEndOfYear[1]);
            RecordSetDataSource rs = new RecordSetDataSource("emp_msg");
            String getMsgCountSql = "select count(1) cnt from HISTORYMSG where DATETIME >= '"+ startEndOfYear[0] +"' and DATETIME <= '"+ startEndOfYear[1]+"'";
            writeLog("getMsgCount=="+getMsgCountSql);
            rs.execute(getMsgCountSql);
            rs.next();
            return rs.getInt("cnt");
        }catch (Exception e){
            e.printStackTrace();
            return 0;
        }
    }

    /**
     * 根据传入的格式化字符串获取当前年份的开始时间和结束时间
     * @param dateFormat
     * @return
     */
    public static String[] getStartEndOfYear(int year, String dateFormat) {
        LocalDateTime startOfYear = LocalDateTime.of(year, 1, 1, 0, 0, 0, 0);
        LocalDateTime endOfYear = LocalDateTime.of(year, 12, 31, 23, 59, 59, 999000000);

        DateTimeFormatter formatter = DateTimeFormatter.ofPattern(dateFormat);
        String start = startOfYear.format(formatter);
        String end = endOfYear.format(formatter);

        return new String[]{start, end};
    }



    /**
     * 获取今年共享文档数量
     * @return
     */
    public static int getShareDOCCount(){
        try {
            String dateFormat = "yyyy-MM-dd";
            String[] startEndOfYear = getStartEndOfYear(2023,dateFormat);
            System.out.println("Start of Year: " + startEndOfYear[0]);
            System.out.println("End of Year: " + startEndOfYear[1]);
            RecordSet rs = new RecordSet();
            String getMsgCountSql = "select count(1) cnt from DOCDETAIL where seccategory = 98 and doccreatedate >= '"+startEndOfYear[0]+"' and doccreatedate <= '"+startEndOfYear[1]+"'";
            rs.execute(getMsgCountSql);
            writeLog("getShareDOCCount=="+getMsgCountSql);
            rs.next();
            return rs.getInt("cnt");
        }catch (Exception e){
            e.printStackTrace();
            return 0;
        }

    }


    /**
     * 获取今年指定用户的共享文档数量
     * @return
     */
    public static int getShareDOCCountbyUserid(RecordSet rs , String userid){
        try {
            String dateFormat = "yyyy-MM-dd";
            String[] startEndOfYear = getStartEndOfYear(2023,dateFormat);
            System.out.println("Start of Year: " + startEndOfYear[0]);
            System.out.println("End of Year: " + startEndOfYear[1]);
            String getMsgCountSql = "select count(1) cnt from DOCDETAIL where seccategory = 98 and doccreatedate >= '"+startEndOfYear[0]+"' and doccreatedate <= '"+startEndOfYear[1]+"' and doccreaterid = "+userid ;
            rs.execute(getMsgCountSql);
            writeLog("getShareDOCCountbyUserid=="+getMsgCountSql);
            rs.next();
            return rs.getInt("cnt");
        }catch (Exception e){
            e.printStackTrace();
            return 0;
        }

    }


    /**
     * 获取今年会议数量
     * @return
     */
    public static int getMeetCount(){
        try {
            String dateFormat = "yyyy-MM-dd HH:mm";
            String[] startEndOfYear = getStartEndOfYear(2023,dateFormat);
            System.out.println("Start of Year: " + startEndOfYear[0]);
            System.out.println("End of Year: " + startEndOfYear[1]);
            RecordSet rs = new RecordSet();
            String getMsgCountSql = "select count(1) cnt from meeting_videolist where  VIDEOMTBEGINDATE >= '"+startEndOfYear[0]+"' and VIDEOMTBEGINDATE <= '"+startEndOfYear[1]+"'";
            rs.execute(getMsgCountSql);
            writeLog("getMeetCount=="+getMsgCountSql);
            rs.next();
            return rs.getInt("cnt");
        }catch (Exception e){
            e.printStackTrace();
            return 0;
        }

    }


    /**
     * 获取今年会议数量
     * @return
     */
    public static int getMeetCountByUserid(RecordSet rs , String userid){
        try {
            String dateFormat = "yyyy-MM-dd HH:mm";
            String[] startEndOfYear = getStartEndOfYear(2023,dateFormat);
            System.out.println("Start of Year: " + startEndOfYear[0]);
            System.out.println("End of Year: " + startEndOfYear[1]);
            String getMsgCountSql = "select count(1) cnt from meeting_videolist where  VIDEOMTBEGINDATE >= '"+startEndOfYear[0]+"' and VIDEOMTBEGINDATE <= '"+startEndOfYear[1]+"' and VIDEOMTCREATER = "+userid;
            rs.execute(getMsgCountSql);
            rs.next();
            writeLog("getMeetCountByUserid=="+getMsgCountSql);
            return rs.getInt("cnt");
        }catch (Exception e){
            e.printStackTrace();
            return 0;
        }

    }

    /**
     * 获取最早办理公文日期
     * @param rs
     * @param userid
     * @return
     */
    public static String getLastOfficialWfDate(RecordSet rs , String userid ,String OfficialWfid){
        try {
            String getMsgCountSql = "select min(OPERATEDATE) minDate from WORKFLOW_REQUESTLOG where OPERATOR = "+userid+" and  workflowid in ( "+OfficialWfid+" )";
            rs.execute(getMsgCountSql);
            rs.next();
            writeLog("getLastOfficialWfDate=="+getMsgCountSql);
            return rs.getString("minDate");
        }catch (Exception e){
            e.printStackTrace();
            return "0";
        }

    }


    /**
     * 获取今年办理公文数
     * @param rs
     * @param userid
     * @return
     */
    public static int getYearOfficialWfCount(RecordSet rs , String userid ,String OfficialWfid){
        try {
            String dateFormat = "yyyy-MM-dd";
            String[] startEndOfYear = getStartEndOfYear(2023,dateFormat);
            System.out.println("Start of Year: " + startEndOfYear[0]);
            System.out.println("End of Year: " + startEndOfYear[1]);
            String getMsgCountSql = "select count(1) cnt from WORKFLOW_REQUESTLOG where OPERATOR = "+userid+" and  workflowid in ( "+OfficialWfid+" ) " +
                    " and OPERATEDATE >= '"+startEndOfYear[0]+"' and OPERATEDATE <= '"+startEndOfYear[1]+"'";
            rs.execute(getMsgCountSql);
            rs.next();
            writeLog("getYearOfficialWfCount=="+getMsgCountSql);
            return rs.getInt("cnt");
        }catch (Exception e){
            e.printStackTrace();
            return 0;
        }

    }


    /**
     * 获取节假日办理公文数
     * @param rs
     * @param userid
     * @return
     */
    public static int getHolidayOfficialWfCount(RecordSet rs , String userid ,String OfficialWfid ,String holidayStr){
        try {
            String getMsgCountSql = "select count(1) cnt from WORKFLOW_REQUESTLOG where OPERATOR = "+userid+" and  workflowid in ( "+OfficialWfid+" ) and OPERATEDATE in ( "+holidayStr+" )";
            rs.execute(getMsgCountSql);
            writeLog("getHolidayOfficialWfCount=="+getMsgCountSql);
            rs.next();
            return rs.getInt("cnt");
        }catch (Exception e){
            e.printStackTrace();
            return 0;
        }

    }

    /**
     * 获取公文办理超越数
     * @param rs
     * @param userid
     * @return
     */
    public static int getOfficialWfCountPercentage(RecordSet rs , String userid ,String OfficialWfid ,String holidayStr,int total){
        try {
            String dateFormat = "yyyy-MM-dd";
            String[] startEndOfYear = getStartEndOfYear(2023,dateFormat);
            System.out.println("Start of Year: " + startEndOfYear[0]);
            System.out.println("End of Year: " + startEndOfYear[1]);
            String getMsgCountSql = "select rank from  (select * ,  RANK() OVER (ORDER BY cnt DESC) AS rank from" +
                    " (select OPERATOR, count(1) cnt from  WORKFLOW_REQUESTLOG" +
                    " where WORKFLOWID in ("+ OfficialWfid +") and OPERATEDATE >= '"+startEndOfYear[0]+"' and OPERATEDATE <= '"+startEndOfYear[1]+"'  group by OPERATOR)) a  where a.OPERATOR = "+ userid;
            rs.execute(getMsgCountSql);
            writeLog("getOfficialWfCountPercentage=="+getMsgCountSql);
            rs.next();
            int rank = Util.getIntValue(rs.getInt("rank"));
            if (0 == rank || -1 == rank){
                return 0;
            }
            return (int)(100.0 * (total - rank) / (total - 1));
        }catch (Exception e){
            e.printStackTrace();
            return 0;
        }
    }


    /**
     * 获取最晚公文办理时间的日期
     * @param rs
     * @param userid
     * @return
     */
    public static String getOfficialWflastTimeDate(RecordSet rs , String userid , String OfficialWfid ){
        try {
            String dateFormat = "yyyy-MM-dd";
            String[] startEndOfYear = getStartEndOfYear(2023,dateFormat);
            System.out.println("Start of Year: " + startEndOfYear[0]);
            System.out.println("End of Year: " + startEndOfYear[1]);
            String getMsgCountSql = "SELECT * FROM ( " +
                    "         SELECT * FROM WORKFLOW_REQUESTLOG where OPERATETIME <= '05:00:00' and WORKFLOWID in ("+ OfficialWfid +") " +
                    "  and OPERATEDATE >= '"+startEndOfYear[0]+"' and OPERATEDATE <= '"+startEndOfYear[1]+"'"+
                    "  and OPERATOR = "+ userid +
                    "         ORDER BY OPERATETIME DESC" +
                    "     )WHERE ROWNUM = 1";
            rs.execute(getMsgCountSql);
            writeLog("getOfficialWflastTimeDate=="+getMsgCountSql);
            rs.next();
            int counts = rs.getCounts();
            if (counts != 0){
                return rs.getString("OPERATEDATE")+" "+rs.getString("OPERATETIME");
            }else {
                getMsgCountSql = "SELECT * FROM ( " +
                        "         SELECT * FROM WORKFLOW_REQUESTLOG where  WORKFLOWID in ("+ OfficialWfid +") " +
                        "  and OPERATEDATE >= '"+startEndOfYear[0]+"' and OPERATEDATE <= '"+startEndOfYear[1]+"'"+
                        "  and OPERATOR = "+ userid +
                        "         ORDER BY OPERATETIME DESC" +
                        "     )WHERE ROWNUM = 1";
                rs.execute(getMsgCountSql);
                writeLog("getOfficialWflastTimeDate2=="+getMsgCountSql);
                rs.next();
                return rs.getString("OPERATEDATE")+" "+rs.getString("OPERATETIME");
            }
        }catch (Exception e){
            e.printStackTrace();
            return "";
        }

    }

    //获取登录天数
    public static int getLoginDayCount(RecordSet rs , String userid  ){
        try {
            String dateFormat = "yyyy-MM-dd";
            String[] startEndOfYear = getStartEndOfYear(2023,dateFormat);
            System.out.println("Start of Year: " + startEndOfYear[0]);
            System.out.println("End of Year: " + startEndOfYear[1]);
            String getMsgCountSql = "select count(*) cnt  from ( " +
                    "    select OPERATEDATE from hrmsysmaintenancelog where RELATEDID = " + userid + ""+
                    "  and OPERATEDATE >= '"+startEndOfYear[0]+"' and OPERATEDATE <= '"+startEndOfYear[1]+"'"+
                    "    group by OPERATEDATE)";
            rs.execute(getMsgCountSql);
            writeLog("getLoginDayCount=="+getMsgCountSql);
            rs.next();
            return rs.getInt("cnt")*2;
        }catch (Exception e){
            e.printStackTrace();
            return 0;
        }

    }

    //获取统筹全局角色的人员,返回,分割的字符串
    public static String getRoleMember(RecordSet rs ){
        try {
            String getMsgCountSql = "select RESOURCEID from  hrmrolemembers mem left join hrmroles role on mem.ROLEID = role.ID where role.ROLESMARK = '统筹全局'";
            rs.execute(getMsgCountSql);
            writeLog("getRoleMember=="+getMsgCountSql);
            ArrayList<String> roleMember = new ArrayList<>();
            while (rs.next()){
                roleMember.add(Util.null2String(rs.getString("RESOURCEID")));
            }
            return String.join(",",roleMember);
        }catch (Exception e){
            e.printStackTrace();
            return "";
        }

    }


    /**
     * 获取今年事务审批数
     * @param rs
     * @param userid
     * @return
     */
    public static int getYearAffairsWfCount(RecordSet rs , String userid ,String AffairsWfid){

        try {
            String dateFormat = "yyyy-MM-dd";
            String[] startEndOfYear = getStartEndOfYear(2023,dateFormat);
            System.out.println("Start of Year: " + startEndOfYear[0]);
            System.out.println("End of Year: " + startEndOfYear[1]);
            String getMsgCountSql = "select count(1) cnt from WORKFLOW_REQUESTLOG where OPERATOR = "+userid+" and  workflowid in ( "+AffairsWfid+" ) " +
                    " and OPERATEDATE >= '"+startEndOfYear[0]+"' and OPERATEDATE <= '"+startEndOfYear[1]+"'";
            rs.execute(getMsgCountSql);
            rs.next();
            writeLog("getYearOfficialWfCount=="+getMsgCountSql);
            return rs.getInt("cnt");
        }catch (Exception e){
            e.printStackTrace();
            return 0;
        }

    }






    public static int getMsgCountByUserid(RecordSetDataSource rs,String userid){
        try {
            String dateFormat = "yyyy-MM-dd HH:mm:ss.SSS";
            String[] startEndOfYear = getStartEndOfYear(2023,dateFormat);
            System.out.println("Start of Year: " + startEndOfYear[0]);
            System.out.println("End of Year: " + startEndOfYear[1]);

            String getMsgCountSql = "select count(distinct TARGETID) cnt from HISTORYMSG where DATETIME >= '"+ startEndOfYear[0] +"' and DATETIME <= '"+ startEndOfYear[1]+"' and fromuserid = '"+ userid+"'";
            writeLog("getMsgCount=="+getMsgCountSql);
            rs.execute(getMsgCountSql);
            rs.next();
            return rs.getInt("cnt");
        }catch (Exception e){
            e.printStackTrace();
            return 0;
        }

    }


    public static int getMsgPeopleCountByUserid(RecordSetDataSource rs,String userid){
        try {
            String dateFormat = "yyyy-MM-dd HH:mm:ss.SSS";
            String[] startEndOfYear = getStartEndOfYear(2023,dateFormat);
            System.out.println("Start of Year: " + startEndOfYear[0]);
            System.out.println("End of Year: " + startEndOfYear[1]);
            String getMsgCountSql = "select count(distinct TARGETID) cnt from HISTORYMSG where TARGETTYPE = 1 and DATETIME >= '"+ startEndOfYear[0] +"' and DATETIME <= '"+ startEndOfYear[1]+"' and fromuserid = '"+ userid+"'";
            writeLog("getMsgCount=="+getMsgCountSql);
            rs.execute(getMsgCountSql);
            rs.next();
            return rs.getInt("cnt");
        }catch (Exception e){
            e.printStackTrace();
            return 0;
        }

    }

    public static int getMsgGangHsienCountByUserid(RecordSetDataSource rs,String userid){
        try {
            String dateFormat = "yyyy-MM-dd HH:mm:ss.SSS";
            String[] startEndOfYear = getStartEndOfYear(2023,dateFormat);
            System.out.println("Start of Year: " + startEndOfYear[0]);
            System.out.println("End of Year: " + startEndOfYear[1]);
            String getMsgCountSql = "select count(distinct TARGETID) cnt from HISTORYMSG where TARGETTYPE = 2 and DATETIME >= '"+ startEndOfYear[0] +"' and DATETIME <= '"+ startEndOfYear[1]+"' and fromuserid = '"+ userid+"'";
            writeLog("getMsgCount=="+getMsgCountSql);
            rs.execute(getMsgCountSql);
            rs.next();
            return rs.getInt("cnt");
        }catch (Exception e){
            e.printStackTrace();
            return 0;
        }

    }

    /**
     *
     * 商旅个人使用次数
     * @param rs
     *
     * 
     * @param userid
     * @param Wfid
     * @return
     */
    public static int getBusinessWfCount(RecordSet rs , String userid ,String Wfid){
        try {
            String dateFormat = "yyyy-MM-dd";
            String[] startEndOfYear = getStartEndOfYear(2023,dateFormat);
            System.out.println("Start of Year: " + startEndOfYear[0]);
            System.out.println("End of Year: " + startEndOfYear[1]);
            String getMsgCountSql = "select count(1) cnt from workflow_requestbase where creater = "+userid+" and  workflowid in ( "+Wfid+" ) " +
                    " and createdate >= '"+startEndOfYear[0]+"' and createdate <= '"+startEndOfYear[1]+"'";
            rs.execute(getMsgCountSql);
            rs.next();
            writeLog("getBusinessWfCount=="+getMsgCountSql);
            return rs.getInt("cnt");
        }catch (Exception e){
            e.printStackTrace();
            return 0;
        }

    }


    /**
     * 获取指定年的休息日(双休情况下)
     * @param year
     * @return
     */
    public static List<String> getAllWeekendsOfYear(int year) {
        //将本年的周六周日加到list中
        List<String> weekendDates = new ArrayList<>();
        LocalDate date = LocalDate.of(year, 1, 1);
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
        while (date.getYear() == year) {
            if (date.getDayOfWeek() == DayOfWeek.SATURDAY || date.getDayOfWeek() == DayOfWeek.SUNDAY) {
                weekendDates.add(date.format(formatter));
            }
            date = date.plusDays(1);
        }
        //查询库里的节假日设置
        RecordSet recordSet = new RecordSet();
        // String groupid = Util.null2String(Prop.getPropValue("year_report_set", "groupid"),"24") ;
        String groupid = "1" ;
        recordSet.executeQuery("select changeType ,holidayDate from KQ_HolidaySet where groupid = ?",groupid);
        //去掉调配工作日,加上公众假日和调配休息日
        List<String> WorkDates = new ArrayList<>();
        List<String> holidayDates = new ArrayList<>();
        while (recordSet.next()){
            String changeType = recordSet.getString("changeType");
            String holidayDate = recordSet.getString("holidayDate");
            if ("2".equals(changeType)){
                WorkDates.add(holidayDate);
            }else if("1".equals(changeType) || "3".equals(changeType)){
                holidayDates.add(holidayDate);
            }
        }
        weekendDates.removeAll(WorkDates);
        HashSet<String> set = new HashSet<>();
        set.addAll(weekendDates);
        set.addAll(holidayDates);
        return new ArrayList<>(set);
    }


    public static String getAffairsWfid(RecordSet rs) {
        try {
            String swspwftype = Prop.getPropValue("yearReport", "swspwftype");
            rs.executeQuery("select id  from workflow_base where workflowtype = ?" ,swspwftype);
            ArrayList<String> wfids = new ArrayList<>();
            while (rs.next()){
                wfids.add(com.wbi.util.Util.null2String(rs.getString("id")));
            }
            return String.join(",",wfids);
        }catch (Exception e){
            e.printStackTrace();
            return "";
        }

    }

    public static void main(String[] args) {
        // String[] dates = getStartEndOfYear(2023, "yyyy-MM-dd");
        // System.out.println("Start of the year: " + dates[0]);
        // System.out.println("End of the year: " + dates[1]);
        // System.out.println(Util.getIntValue(null));
        HashMap<Object, Object> sixthMap = new HashMap<>();
        LocalDate date = LocalDate.parse("2023-10-25", DateTimeFormatter.ofPattern("yyyy-MM-dd"));
        sixthMap.put("dycclgwrqMM",date.getMonthValue()+"");
        sixthMap.put("dycclgwrqDD",date.getDayOfMonth()+"");
    }

    public static void writeLog(String log) {
        bb.writeLog(Thread.currentThread().getName()+"==>ReportUtil:"+log);
    }
}
