package com.artfess.bpm.chart.impl;

import com.fasterxml.jackson.databind.node.ArrayNode;
import com.fasterxml.jackson.databind.node.ObjectNode;
import com.artfess.base.constants.SQLConst;
import com.artfess.base.util.BeanUtils;
import com.artfess.base.util.JsonUtil;
import com.artfess.base.util.StringUtil;
import com.artfess.bpm.chart.IChart;
import org.springframework.stereotype.Component;

import java.math.BigDecimal;
import java.util.*;

@Component("start_throughput")
public class StartThroughputChart implements IChart {

    @Override
    public String getQuerySql(String flowType, String dimension, String ids, String orgIds, String startTime, String endTime, String orgType, String dbType) {
        StringBuilder sql = new StringBuilder();
        String whereIds = "org".equals(dimension)?ids:orgIds;
        StringBuilder whereSql = new StringBuilder();
        String idCode = "2".equals(orgType)?"CREATE_BY_":"CREATE_ORG_ID_";
        if(SQLConst.DB_ORACLE.equals(dbType)){
            whereSql.append(" AND a.STATUS_ IS NOT NULL AND a.CREATE_TIME_ between to_date('"+startTime+"','YYYY-MM-DD HH24:MI:SS') and to_date('"+endTime+"','YYYY-MM-DD HH24:MI:SS')");
        }else{
            whereSql.append(" AND a.STATUS_ IS NOT NULL AND a.CREATE_TIME_>='"+startTime+"' AND a.CREATE_TIME_<='"+endTime+"' ");
        }
        if(StringUtil.isNotEmpty(whereIds)){
            String[] idArray = whereIds.split(",");
            String idsSql = StringUtil.convertListToSingleQuotesString(new HashSet<String>(Arrays.asList(idArray)));
            if("org".equals(dimension)){
                if("1".equals(flowType)){
                    whereSql.append(" AND a.PROC_DEF_KEY_ IN ("+ idsSql+") ");
                }else if("2".equals(flowType)){
                    whereSql.append(" AND a.TYPE_ID_ IN ("+ idsSql+") ");
                }
            }else {
                whereSql.append(" AND a."+idCode+" IN ("+ idsSql+") ");
            }
        }
        if(StringUtil.isNotEmpty(orgIds) && "org".equals(dimension)){
            String[] orgIdArray = orgIds.split(",");
            boolean isFirst = false;
            for (String id : orgIdArray) {
                if(!isFirst){
                    isFirst = true;
                }else{
                    sql.append(" UNION ");
                }

                String baseSql = getBaseSql(id, flowType, dimension, whereSql.toString(), orgType,dbType);
                sql.append(baseSql);
            }
        }else if(StringUtil.isNotEmpty(ids) && "flow".equals(dimension)){
            String[] idArray = ids.split(",");
            boolean isFirst = false;
            for (String id : idArray) {
                if(!isFirst){
                    isFirst = true;
                }else{
                    sql.append(" UNION ");
                }
                String baseSql = getBaseSql(id, flowType, dimension, whereSql.toString(), orgType, dbType);
                sql.append(baseSql);
            }
        }else{
            String baseSql = getBaseSql("", flowType, dimension, whereSql.toString(), orgType, dbType);
            sql.append(baseSql);
        }
        return sql.toString();
    }

    private String getBaseSql(String orgId, String flowType, String dimension, String whereSql, String orgType, String dbType){
        StringBuilder baseSql = new StringBuilder();
        String idCode = "2".equals(orgType)?"CREATE_BY_":"CREATE_ORG_ID_";
        String orgSql = StringUtil.isNotEmpty(orgId)?" AND a."+idCode+" ='"+orgId+"' ":" ";
        String havingSql = " having count(1) >0 ";
        String groupBySql = " GROUP BY a."+idCode;
        if("flow".equals(dimension)){
            if("1".equals(flowType)){
                orgSql = StringUtil.isNotEmpty(orgId)?" AND a.PROC_DEF_KEY_ ='"+orgId+"' ":" ";
                groupBySql = " GROUP BY a.PROC_DEF_KEY_  ";
            }else{
                orgSql = StringUtil.isNotEmpty(orgId)?" AND a.TYPE_ID_ ='"+orgId+"' ":" ";
                groupBySql = " GROUP BY a.TYPE_ID_ ";
            }
        }
        if(SQLConst.DB_ORACLE.equals(dbType) || SQLConst.DB_POSTGRESQL.equals(dbType) || SQLConst.DB_H2.equals(dbType)){
            havingSql = groupBySql + " having count(1) >0 ";
        }

        //审批中
        baseSql.append(getSelectSql(orgType, flowType, dimension, "running"));
        baseSql.append(" from bpm_pro_inst a WHERE a.STATUS_ in('running','back','backToStart','revoke','revokeToStart') ");
        baseSql.append(orgSql);
        baseSql.append(whereSql);
        baseSql.append(havingSql);

        //驳回/驳回发起人/撤回
        baseSql.append(" UNION ");
        baseSql.append(getSelectSql(orgType, flowType, dimension, "backRevoke"));
        baseSql.append(" from bpm_pro_inst a WHERE a.STATUS_ in('back','backToStart','revoke','revokeToStart') ");
        baseSql.append(orgSql);
        baseSql.append(whereSql);
        baseSql.append(havingSql);

        //废弃/终止
        baseSql.append(" UNION ");
        baseSql.append(getSelectSql(orgType, flowType, dimension, "manualend"));
        baseSql.append(" from bpm_pro_inst a WHERE a.STATUS_ in('manualend') ");
        baseSql.append(orgSql);
        baseSql.append(whereSql);
        baseSql.append(havingSql);

        //审批完成
        baseSql.append(" UNION ");
        baseSql.append(getSelectSql(orgType, flowType, dimension, "end"));
        baseSql.append(" from bpm_pro_inst a WHERE a.STATUS_ ='end' ");
        baseSql.append(orgSql);
        baseSql.append(whereSql);
        baseSql.append(havingSql);

        //工单数量
        baseSql.append(" UNION ");
        baseSql.append(getSelectSql(orgType, flowType, dimension, "instances"));
        baseSql.append(" from bpm_pro_inst a WHERE 1=1 ");
        baseSql.append(orgSql);
        baseSql.append(whereSql);
        baseSql.append(havingSql);

        //运行时长（小时）
        baseSql.append(" UNION ");
        if(SQLConst.DB_ORACLE.equals(dbType)){
            baseSql.append(getHourLongSql_oracle(orgType, flowType, dimension, "hourLong",false));
        }else if(SQLConst.DB_POSTGRESQL.equals(dbType)) {
            baseSql.append(getHourLongSql_postgresql(orgType, flowType, dimension, "hourLong",false));
        }else if(SQLConst.DB_H2.equals(dbType)){
            baseSql.append(getHourLongSql_h2(orgType, flowType, dimension, "hourLong",false));
        }else{
            baseSql.append(getHourLongSql_mysql(orgType, flowType, dimension, "hourLong",false));
        }
        baseSql.append(" from bpm_pro_inst a WHERE 1=1 ");
        baseSql.append(orgSql);
        baseSql.append(whereSql);
        baseSql.append(havingSql);

        //未完成工单
        baseSql.append(" UNION ");
        baseSql.append(getSelectSql(orgType, flowType, dimension, "incomplete"));
        baseSql.append(" from bpm_pro_inst a WHERE a.STATUS_ not in ('end','manualend') ");
        baseSql.append(orgSql);
        baseSql.append(whereSql);
        baseSql.append(havingSql);

        //平均时长（小时）
        baseSql.append(" UNION ");
        //oracle数据库暂未完善
        if(SQLConst.DB_ORACLE.equals(dbType)){
            baseSql.append(getHourLongSql_oracle(orgType, flowType, dimension, "avgLong",true));
        }else if(SQLConst.DB_POSTGRESQL.equals(dbType)) {
            baseSql.append(getHourLongSql_postgresql(orgType, flowType, dimension, "avgLong",true));
        }else if(SQLConst.DB_H2.equals(dbType)){
            baseSql.append(getHourLongSql_h2(orgType, flowType, dimension, "avgLong",true));
        }else{
            baseSql.append(getHourLongSql_mysql(orgType, flowType, dimension, "avgLong",true));
        }
        baseSql.append(" from bpm_pro_inst a WHERE 1=1 ");
        baseSql.append(orgSql);
        baseSql.append(whereSql);
        baseSql.append(havingSql);

        //显示逾期工单
        baseSql.append(" UNION ");
        baseSql.append(getSelectSql(orgType, flowType, dimension, "overtime"));
        //oracle数据库暂未处理
        if(SQLConst.DB_ORACLE.equals(dbType)){
            baseSql.append(" from bpm_pro_inst a WHERE ROUND(((TO_DATE(to_char((CASE a.END_TIME_ WHEN a.END_TIME_ THEN a.END_TIME_ ELSE (select sysdate from dual) END), 'YYYY-MM-DD HH24-MI-SS'), 'YYYY-MM-DD HH24-MI-SS') - TO_DATE(to_char(a.CREATE_TIME_ , 'YYYY-MM-DD HH24-MI-SS'), 'YYYY-MM-DD HH24-MI-SS')) * 24*60  ),1)>24 ");
        }else if(SQLConst.DB_POSTGRESQL.equals(dbType)) {
            baseSql.append(" from bpm_pro_inst a WHERE ROUND(((TO_DATE(to_char((CASE a.END_TIME_ WHEN a.END_TIME_ THEN a.END_TIME_ ELSE now() END), 'YYYY-MM-DD HH24-MI-SS'), 'YYYY-MM-DD HH24-MI-SS') - TO_DATE(to_char(a.CREATE_TIME_ , 'YYYY-MM-DD HH24-MI-SS'), 'YYYY-MM-DD HH24-MI-SS')) * 24*60  ),1)>24 ");
        }else if(SQLConst.DB_H2.equals(dbType)){
            baseSql.append(" from bpm_pro_inst a WHERE TIMESTAMPDIFF(HOUR,a.CREATE_TIME_,(CASE WHEN a.END_TIME_ IS NOT NULL THEN a.END_TIME_ ELSE now() END))>24 ");
        }else{
            baseSql.append(" from bpm_pro_inst a WHERE TIMESTAMPDIFF(HOUR,a.CREATE_TIME_,IF(a.END_TIME_ IS NOT NULL,a.END_TIME_,now()))>24 ");
        }
        baseSql.append(orgSql);
        baseSql.append(whereSql);
        baseSql.append(havingSql);

        //闭单率（%）
        baseSql.append(" UNION ");
        StringBuilder countSql = new StringBuilder();
        countSql.append("select count(1) from bpm_pro_inst a where a.STATUS_ in ('end','manualend') ");
        countSql.append(orgSql);
        countSql.append(whereSql);
        countSql.append(havingSql);
        //暂未对oracle数据库做处理
        if(SQLConst.DB_ORACLE.equals(dbType)){
            baseSql.append(getClosingRate_oracle(orgType, flowType, dimension, "closingRate",countSql.toString()));
        }else{
            baseSql.append(getClosingRate_mysql(orgType, flowType, dimension, "closingRate",countSql.toString()));
        }
        baseSql.append(" from bpm_pro_inst a WHERE 1=1 ");
        baseSql.append(orgSql);
        baseSql.append(whereSql);
        baseSql.append(havingSql);

        return baseSql.toString();
    }

    private String getSelectSql(String orgType,String flowType,String dimension,String status){
        String idCode = "2".equals(orgType)?"CREATE_BY_":"CREATE_ORG_ID_";
        String selectSql = "select count(1) as count,a."+idCode+" as id,'"+status+"' as status ";
        if("flow".equals(dimension)){
            if("1".equals(flowType)){
                selectSql = "select count(1) as count,a.PROC_DEF_KEY_ as id,'"+status+"' as status ";
            }else{
                selectSql = "select count(1) as count,a.TYPE_ID_ as id,'"+status+"' as status ";
            }
        }
        return selectSql;
    }

    private String getHourLongSql_oracle(String orgType,String flowType,String dimension,String status,boolean isRate){
        String idCode = "2".equals(orgType)?"CREATE_BY_":"CREATE_ORG_ID_";
        String rateSql = isRate?"/count(a.ID_)) ":")";
        String selectSql = "select round((SUM(TO_NUMBER((DECODE(END_TIME_,NULL,sysdate,END_TIME_)) -(CREATE_TIME_+0))*24))"+rateSql+" as count,a."+idCode+" as id,'"+status+"' as status ";
        if("flow".equals(dimension)){
            if("1".equals(flowType)){
                selectSql = "select round((SUM(TO_NUMBER((DECODE(END_TIME_,NULL,sysdate,END_TIME_)) -(CREATE_TIME_+0))*24))"+rateSql+" as count,a.PROC_DEF_KEY_ as id,'"+status+"' as status ";
            }else{
                selectSql = "select round((SUM(TO_NUMBER((DECODE(END_TIME_,NULL,sysdate,END_TIME_)) -(CREATE_TIME_+0))*24))"+rateSql+" as count,a.TYPE_ID_ as id,'"+status+"' as status ";
            }
        }
        return selectSql;
    }

    private String getHourLongSql_postgresql(String orgType,String flowType,String dimension,String status,boolean isRate){
        String idCode = "2".equals(orgType)?"CREATE_BY_":"CREATE_ORG_ID_";
        String rateSql = isRate?"/count(a.ID_),1)) ":")";
        String roundSql = isRate?"ROUND(":"";
        String selectSql = "select ("+roundSql+"SUM(ROUND(cast(date_part('MINUTE',(CASE a.END_TIME_ WHEN a.END_TIME_ THEN a.END_TIME_ ELSE now() END)::timestamp- a.create_time_::timestamp)/60 as numeric), 1))"+rateSql+" as count,a."+idCode+" as id,'"+status+"' as status ";
        if("flow".equals(dimension)){
            if("1".equals(flowType)){
                selectSql = "select ("+roundSql+"SUM(ROUND(cast(date_part('MINUTE',(CASE a.END_TIME_ WHEN a.END_TIME_ THEN a.END_TIME_ ELSE now() END)::timestamp- a.create_time_::timestamp)/60 as numeric), 1))"+rateSql+" as count,a.PROC_DEF_KEY_ as id,'"+status+"' as status ";
            }else{
                selectSql = "select ("+roundSql+"SUM(ROUND(cast(date_part('MINUTE',(CASE a.END_TIME_ WHEN a.END_TIME_ THEN a.END_TIME_ ELSE now() END)::timestamp- a.create_time_::timestamp)/60 as numeric), 1))"+rateSql+" as count,a.TYPE_ID_ as id,'"+status+"' as status ";
            }
        }
        return selectSql;
    }

    private String getHourLongSql_mysql(String orgType,String flowType,String dimension,String status,boolean isRate){
        String idCode = "2".equals(orgType)?"CREATE_BY_":"CREATE_ORG_ID_";
        String rateSql = isRate?"/count(a.ID_),1)) ":")";
        String roundSql = isRate?"ROUND(":"";
        String selectSql = "select ("+roundSql+"SUM(ROUND(TIMESTAMPDIFF(MINUTE,a.CREATE_TIME_,if(a.END_TIME_ IS not NULL,a.END_TIME_,now()))/60,1))"+rateSql+" as count,a."+idCode+" as id,'"+status+"' as status ";
        if("flow".equals(dimension)){
            if("1".equals(flowType)){
                selectSql = "select ("+roundSql+"SUM(ROUND(TIMESTAMPDIFF(MINUTE,a.CREATE_TIME_,if(a.END_TIME_ IS not NULL,a.END_TIME_,now()))/60,1))"+rateSql+" as count,a.PROC_DEF_KEY_ as id,'"+status+"' as status ";
            }else{
                selectSql = "select ("+roundSql+"SUM(ROUND(TIMESTAMPDIFF(MINUTE,a.CREATE_TIME_,if(a.END_TIME_ IS not NULL,a.END_TIME_,now()))/60,1))"+rateSql+" as count,a.TYPE_ID_ as id,'"+status+"' as status ";
            }
        }
        return selectSql;
    }

    private String getHourLongSql_h2(String orgType,String flowType,String dimension,String status,boolean isRate){
        String idCode = "2".equals(orgType)?"CREATE_BY_":"CREATE_ORG_ID_";
        String rateSql = isRate?"/count(a.ID_),1)) ":")";
        String roundSql = isRate?"ROUND(":"";
        String selectSql = "select ("+roundSql+"SUM(ROUND(TIMESTAMPDIFF(MINUTE,a.CREATE_TIME_,(CASE WHEN a.END_TIME_ IS NOT NULL THEN a.END_TIME_ ELSE now() END))/60,1))"+rateSql+" as count,a."+idCode+" as id,'"+status+"' as status ";
        if("flow".equals(dimension)){
            if("1".equals(flowType)){
                selectSql = "select ("+roundSql+"SUM(ROUND(TIMESTAMPDIFF(MINUTE,a.CREATE_TIME_,(CASE WHEN a.END_TIME_ IS NOT NULL THEN a.END_TIME_ ELSE now() END))/60,1))"+rateSql+" as count,a.PROC_DEF_KEY_ as id,'"+status+"' as status ";
            }else{
                selectSql = "select ("+roundSql+"SUM(ROUND(TIMESTAMPDIFF(MINUTE,a.CREATE_TIME_,(CASE WHEN a.END_TIME_ IS NOT NULL THEN a.END_TIME_ ELSE now() END))/60,1))"+rateSql+" as count,a.TYPE_ID_ as id,'"+status+"' as status ";
            }
        }
        return selectSql;
    }

    private String getClosingRate_oracle(String orgType,String flowType,String dimension,String status,String countSql){
        String idCode = "2".equals(orgType)?"CREATE_BY_":"CREATE_ORG_ID_";
        String selectSql = "select ROUND((select ("+countSql+") from dual)/count(1)*100) as count,a."+idCode+" as id,'"+status+"' as status ";
        if("flow".equals(dimension)){
            if("1".equals(flowType)){
                selectSql = "select ROUND((select ("+countSql+") from dual)/count(1)*100) as count,a.PROC_DEF_KEY_ as id,'"+status+"' as status ";
            }else{
                selectSql = "select ROUND((select ("+countSql+") from dual)/count(1)*100) as count,a.TYPE_ID_ as id,'"+status+"' as status ";
            }
        }
        return selectSql;
    }

    private String getClosingRate_mysql(String orgType,String flowType,String dimension,String status,String countSql){
        String idCode = "2".equals(orgType)?"CREATE_BY_":"CREATE_ORG_ID_";
        String selectSql = "select ROUND(("+countSql+")/count(1)*100) as count,a."+idCode+" as id,'"+status+"' as status ";
        if("flow".equals(dimension)){
            if("1".equals(flowType)){
                selectSql = "select ROUND(("+countSql+")/count(1)*100) as count,a.PROC_DEF_KEY_ as id,'"+status+"' as status ";
            }else{
                selectSql = "select ROUND(("+countSql+")/count(1)*100) as count,a.TYPE_ID_ as id,'"+status+"' as status ";
            }
        }
        return selectSql;
    }

    @Override
    public String getDesc() {
        return "启动吞吐量";
    }

    @Override
    public List<String> getLegend() {
        return Arrays.asList("审批中","驳回/驳回发起人/撤回","废弃/终止",
                "审批完成","工单数量","运行时长（小时）","未完成工单",
                "平均时长（小时）","逾期工单","闭单率（%）");
    }

    @Override
    public ArrayNode getSeries(List<String> legend, List<Map<String, Object>> selectList, Map<String, String> map) throws Exception {
        ArrayNode array = JsonUtil.getMapper().createArrayNode();
        String defType = "bar";
        for (String le : legend) {
            ObjectNode node = JsonUtil.getMapper().createObjectNode();
            node.put("name",le);
            node.put("type",defType);
            List<Object> data;
            data = getSeriesData(le, selectList, map);
            node.set("data",JsonUtil.toJsonNode(data));
            array.add(node);
        }
        return array;
    }

    private List<Object> getSeriesData(String legend, List<Map<String, Object>> selectList, Map<String, String> map) throws Exception{
        List<Object> data = new ArrayList<Object>();
        String status = getLegendCode(legend);
        for (String key : map.keySet()) {
            double isValue = 0;
            for (Object obj : selectList) {
                if(BeanUtils.isNotEmpty(obj)){
                    ObjectNode oNode = (ObjectNode) JsonUtil.toJsonNode(obj);
                    if(BeanUtils.isNotEmpty(oNode.get("id")) && key.equals(oNode.get("id").asText())
                            && status.equals(oNode.get("status").asText())){
                        double count = BeanUtils.isNotEmpty(oNode.get("count"))?oNode.get("count").asDouble():0;
                        isValue += count;
                    }//处理oracle返回格式大小问题
                    else if(BeanUtils.isNotEmpty(oNode.get("ID")) && key.equals(oNode.get("ID").asText())
                            && status.equals(oNode.get("STATUS").asText())){
                        double count = BeanUtils.isNotEmpty(oNode.get("COUNT"))?oNode.get("COUNT").asDouble():0;
                        isValue += count;
                    }
                }
            }
            data.add(isValue);
        }
        return data;
    }

    private String getLegendCode(String desc) {
        String status = "";
        switch (desc) {
            case "审批中":
                status = "running";
                break;
            case "审批完成":
                status = "end";
                break;
            case "驳回/驳回发起人/撤回":
                status = "backRevoke";
                break;
            case "废弃/终止":
                status = "manualend";
                break;
            case "工单数量":
                status = "instances";
                break;
            case "运行时长（小时）":
                status = "hourLong";
                break;
            case "未完成工单":
                status = "incomplete";
                break;
            case "平均时长（小时）":
                status = "avgLong";
                break;
            case "逾期工单":
                status = "overtime";
                break;
            case "闭单率（%）":
                status = "closingRate";
                break;
        }
        return status;
    }

}