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("handle_throughput")
public class HandleThroughputChart 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 opinion.STATUS_ IS NOT NULL AND opinion.COMPLETE_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 opinion.STATUS_ IS NOT NULL AND opinion.COMPLETE_TIME_>='"+startTime+"' AND opinion.COMPLETE_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 inst.PROC_DEF_KEY_ IN ("+ idsSql+") ");
                }else if("2".equals(flowType)){
                    whereSql.append(" AND inst.TYPE_ID_ IN ("+ idsSql+") ");
                }
            }else {
                if(!"2".equals(orgType)) {
                    idCode = "ORG_ID_";
                }else {
                    idCode = "AUDITOR_";
                }
                whereSql.append(" AND opinion."+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 orgSql = getOrgSql(flowType, orgType, orgId, dimension);
        String havingSql = " having count(1) >0 ";
        if(SQLConst.DB_ORACLE.equals(dbType) || SQLConst.DB_POSTGRESQL.equals(dbType)){
            String idCode = "2".equals(orgType)?"AUDITOR_":"ORG_ID_";
            String groupBySql = " GROUP BY opinion."+idCode;
            if("flow".equals(dimension)){
                if("1".equals(flowType)){
                    groupBySql = " GROUP BY inst.PROC_DEF_KEY_  ";
                }else{
                    groupBySql = " GROUP BY inst.TYPE_ID_ ";
                }
            }
            havingSql = groupBySql + " having count(1) >0 ";
        }
        //审批
        StringBuilder agreeSql = new StringBuilder();
        agreeSql.append(getApprovalSelectSql(flowType, orgType, dimension, "bpm_check_opinion","agree"));
        agreeSql.append(" and opinion.STATUS_ in('transAgree','oppose','transOppose','back','backToStart','revoker','revoker_to_start','agree') ");
        agreeSql.append(orgSql);
        agreeSql.append(whereSql);
        agreeSql.append(havingSql);
        agreeSql.append(" UNION ");
        agreeSql.append(getApprovalSelectSql(flowType, orgType, dimension, "bpm_check_opinion_hi","agree"));
        agreeSql.append(" and opinion.STATUS_ in('transAgree','oppose','transOppose','back','backToStart','revoker','revoker_to_start','agree') ");
        agreeSql.append(orgSql);
        agreeSql.append(whereSql);
        agreeSql.append(havingSql);
        baseSql.append(" select SUM(a.count) as count,a.id as id,'agree' as status from ( ");
        baseSql.append(agreeSql.toString());
        baseSql.append(" ) a ");
        if(SQLConst.DB_ORACLE.equals(dbType) || SQLConst.DB_POSTGRESQL.equals(dbType)){
            baseSql.append(" GROUP BY a.id ");
        }
        //沟通、转办、征询、抄送
        String[] status = new String[]{"start_commu","deliverto","inqu","copyto"};
        for (String statu : status) {
            baseSql.append(" UNION ");
            StringBuilder otherSql = new StringBuilder();
            otherSql.append(getApprovalSelectSql(flowType, orgType, dimension, "bpm_check_opinion_hi",statu));
            otherSql.append(" and opinion.STATUS_ ='"+statu+"' ");
            otherSql.append(orgSql);
            otherSql.append(whereSql);
            otherSql.append(havingSql);
            otherSql.append(" UNION ");
            otherSql.append(getApprovalSelectSql(flowType, orgType, dimension, "bpm_check_opinion",statu));
            otherSql.append(" and opinion.STATUS_ ='"+statu+"' ");
            otherSql.append(orgSql);
            otherSql.append(whereSql);
            otherSql.append(havingSql);
            baseSql.append(" select SUM(a.count) as count,a.id as id,'"+statu+"' as status from ( ");
            baseSql.append(otherSql.toString());
            baseSql.append(" ) a ");
            if(SQLConst.DB_ORACLE.equals(dbType) || SQLConst.DB_POSTGRESQL.equals(dbType)){
                baseSql.append(" GROUP BY a.id ");
            }
        }
        return baseSql.toString();
    }

    private String getOrgSql(String flowType,String orgType,String orgId,String dimension){
        String idCode = "2".equals(orgType)?"AUDITOR_":"ORG_ID_";
        String orgSql = StringUtil.isNotEmpty(orgId)?" AND opinion."+idCode+" ='"+orgId+"' ":" ";
        if("flow".equals(dimension)){
            if("1".equals(flowType)){
                orgSql = StringUtil.isNotEmpty(orgId)?" AND inst.PROC_DEF_KEY_ ='"+orgId+"' ":" ";
            }else{
                orgSql = StringUtil.isNotEmpty(orgId)?" AND inst.TYPE_ID_ ='"+orgId+"' ":" ";
            }
        }
        return orgSql;
    }

    private String getApprovalSelectSql(String flowType,String orgType,String dimension,String table,String status){
        String idCode = "2".equals(orgType)?"AUDITOR_":"ORG_ID_";
        String selectSql = "select count(1) as count,opinion."+idCode+" as id,'"+status+"' as status FROM "+table+" opinion LEFT JOIN bpm_pro_inst inst on opinion.PROC_INST_ID_=inst.ID_ where opinion."+idCode+" is not null ";
        if("flow".equals(dimension)){
            if("1".equals(flowType)){
                selectSql = "select count(1) as count,inst.PROC_DEF_KEY_ as id,'"+status+"' as status FROM "+table+" opinion LEFT JOIN bpm_pro_inst inst on opinion.PROC_INST_ID_=inst.ID_ where 1=1 ";
            }else{
                selectSql = "select count(1) as count,inst.TYPE_ID_ as id,'"+status+"' as status FROM "+table+" opinion LEFT JOIN bpm_pro_inst inst on opinion.PROC_INST_ID_=inst.ID_ where 1=1 ";
            }
        }
        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 le, List<Map<String, Object>> selectList, Map<String, String> map) throws Exception{
        List<Object> data = new ArrayList<Object>();
        String status = getLegendCode(le);
        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 = "agree";
                break;
            case "沟通次数":
                status = "start_commu";
                break;
            case "征询次数":
                status = "inqu";
                break;
            case "转办次数":
                status = "deliverto";
                break;
            case "传阅次数":
                status = "copyto";
                break;
        }
        return status;
    }
}