<%@ page import="weaver.conn.RecordSet" %>
<%@ page import="com.alibaba.fastjson.JSONObject" %>
<%@ page import="weaver.general.Util" %>
<%@ page import="org.apache.commons.lang3.StringUtils" %>
<%@ page import="weaver.hrm.HrmUserVarify" %>
<%@ page import="weaver.hrm.User" %>
<%@ page import="java.util.*" %>
<%@ page language="java" contentType="text/html; charset=UTF-8" %>
<%

    User user = HrmUserVarify.getUser(request, response);
    if(user==null) {
        response.sendRedirect("/login/Login.jsp");
        return;
    }

    Map<String,String> signMap = new HashMap<String,String>();
    signMap.put("总体集成","ztjc");
    signMap.put("系统咨询","xtzx");
    signMap.put("软件开发","rjkf");
    signMap.put("安防监控","afjk");
    signMap.put("运行维护","yxwh");
    signMap.put("数据恢复","sjhf");
    signMap.put("工程监理","gcjl");


    Map<String,Integer> countMap = new HashMap<String,Integer>();
    countMap.put("jj_ztjc",0);
    countMap.put("jj_xtzx",0);
    countMap.put("jj_rjkf",0);
    countMap.put("jj_afjk",0);
    countMap.put("jj_yxwh",0);
    countMap.put("jj_sjhf",0);
    countMap.put("jj_gcjl",0);

    countMap.put("yj_ztjc",0);
    countMap.put("yj_xtzx",0);
    countMap.put("yj_rjkf",0);
    countMap.put("yj_afjk",0);
    countMap.put("yj_yxwh",0);
    countMap.put("yj_sjhf",0);
    countMap.put("yj_gcjl",0);

    String startDate = request.getParameter("startDate");
    String endDate = request.getParameter("endDate");

    String zzlx = "0";
    RecordSet rs = new RecordSet();
    List<Map<String,Object>> cityList = new ArrayList<Map<String,Object>>();
    String sql =" select selectvalue,selectname \n" +
                "   from workflow_selectitem \n" +
                "   where fieldid in(\n" +
                "       select id from workflow_billfield \n" +
                "       where billid in( select id from workflow_bill where tablename='uf_dwjbxx' ) and fieldname='szcs' \n" +
                "       and detailtable  is null\n" +
                " ) ";
    rs.executeQuery(sql);
    while (rs.next()){
        String selectvalue = rs.getString("selectvalue");
        String selectname = rs.getString("selectname");
        Map<String,Object> cityMap = new HashMap<String,Object>();
        cityMap.put("key","City"+selectvalue);
        cityMap.put("title",selectname);
        cityList.add(cityMap);
    }


    String selectnames = "";
    Iterator<Map.Entry<String,String>> iterator = signMap.entrySet().iterator();
    while (iterator.hasNext()){
        Map.Entry<String,String> entry = iterator.next();
        String key = entry.getKey();
        selectnames += StringUtils.isBlank(selectnames) ? "'" + key + "'" : ",'" + key + "'";
    }

    List<Map<String,String>> typeList = new ArrayList<Map<String,String>>();
    sql =   " select selectvalue,selectname \n" +
            " from workflow_selectitem\n" +
            " where fieldid in(\n" +
            "       select id from workflow_billfield \n" +
            "       where billid in( select id from workflow_bill where tablename='uf_bmzzzsxx' ) and fieldname='ywzl' \n" +
            "       and detailtable  is null\n" +
            " ) \n" +
            " AND selectname in("+selectnames+")" ;

    rs.executeQuery(sql);
    while (rs.next()){
        String selectvalue = Util.null2String(rs.getString("selectvalue"));
        String selectname = Util.null2String(rs.getString("selectname"));

        Map<String,String> typeMap = new HashMap<String,String>();
        typeMap.put("selectname",selectname);
        typeMap.put("selectvalue",selectvalue);
        String selectType = signMap.get(selectname);
        typeMap.put("selecttype",selectType);
        typeList.add(typeMap);
    }

    List<Map<String,String>> djList = new ArrayList<Map<String,String>>();
    sql =   " select selectvalue,selectname \n" +
            "      from workflow_selectitem\n" +
            "    where fieldid in(\n" +
            "    select id from workflow_billfield \n" +
            "    where billid in( select id from workflow_bill where tablename='uf_bmzzzsxx' ) \n" +
            "    and fieldname='zzdj' \n" +
            "    and detailtable  is null\n" +
            "  ) \n" +
            "  and selectname in('甲级','乙级') ";
    rs.executeQuery(sql);
    while (rs.next()){
        String selectvalue = Util.null2String(rs.getString("selectvalue"));
        String selectname = Util.null2String(rs.getString("selectname"));

        Map<String,String> djMap = new HashMap<String,String>();
        djMap.put("selectvalue",selectvalue);
        djMap.put("selectname",selectname);
        String type = "";
        if("甲级".equals(selectname)){
            type = "jj";
        }else if("乙级".equals(selectname)){
            type = "yj";
        }

        djMap.put("rowtype",type);
        djList.add(djMap);
    }

    String where = "";
    if(StringUtils.isNotBlank(startDate)){
        where += " and k.fzrq >= '"+startDate+"' ";
    }

    if(StringUtils.isNotBlank(endDate)){
        where += " and k.fzrq <= '"+endDate+"' ";
    }


    for(int i=0;i<cityList.size();i++){
        Map<String,Object> cityMap = cityList.get(i);

        String cityKey = Util.null2String(cityMap.get("key")).replace("City","");

        for(int p=0;p<djList.size();p++){

            Map<String,String> djMap = djList.get(p);
            String rowtype = djMap.get("rowtype");
            String djvalue = djMap.get("selectvalue");

            for(int k=0;k<typeList.size();k++){
                Map<String,String> typeMap = typeList.get(k);
                String selectvalue = typeMap.get("selectvalue");
                String selecttype = typeMap.get("selecttype");
                int count = 0;
                if(StringUtils.isNotBlank(selectvalue)){

                    sql = " select k.id \n" +
                            "   from uf_dwjbxx t\n" +
                            "   inner join uf_bmzzzsxx k on k.dwmc = t.id\n" +
                            "   where k.zszt=0" +
                            "   and k.zzdj = " + djvalue +
                            "   and instr(','||k.ywzl||',',',"+selectvalue+",')>0 \n" +
                            "   and k.zzlx = " + zzlx +
                            "   and t.szcs="+cityKey + where  ;

                    rs.executeQuery(sql);
                    while (rs.next()){
                        count++;
                    }
                    int total = countMap.get(rowtype+"_"+selecttype);
                    total += count;
                    countMap.put(selecttype,total);
                }
                cityMap.put(rowtype+"_"+selecttype,count);
            }
        }
    }

    for(int i=0;i<cityList.size();i++){
        Map<String,Object> cityMap = cityList.get(i);
        String cityKey = Util.null2String(cityMap.get("key")).replace("City","");

        List<Map<String,Object>> districtList = new ArrayList<Map<String,Object>>();

        if(StringUtils.isNotBlank(cityKey)){
            int cityKey_ = Util.getIntValue(cityKey)+1;
            sql = "select id,qm,yb from uf_qjsj where sss="+cityKey_;
            rs.executeQuery(sql);
            while (rs.next()){
                String id = Util.null2String(rs.getString("id"));
                String qm = Util.null2String(rs.getString("qm"));

                Map<String,Object> districtMap = new HashMap<String,Object>();
                districtMap.put("key","District"+id);
                districtMap.put("title",qm);
                districtList.add(districtMap);
            }
        }


        for(int k=0;k<districtList.size();k++){
            Map<String,Object> districtMap = districtList.get(k);
            String districtKey = Util.null2String(districtMap.get("key")).replace("District","");

            for(int p=0;p<djList.size();p++) {

                Map<String, String> djMap = djList.get(p);
                String rowtype = djMap.get("rowtype");
                String djvalue = djMap.get("selectvalue");


                for(int n=0;n<typeList.size();n++){
                    Map<String,String> typeMap = typeList.get(n);
                    String selectvalue = typeMap.get("selectvalue");
                    String selecttype = typeMap.get("selecttype");

                    int count = 0;

                    if(StringUtils.isNotBlank(selectvalue)){
                        sql = " select k.id \n" +
                                "    from uf_dwjbxx t\n" +
                                "   inner join uf_bmzzzsxx k on k.dwmc = t.id\n" +
                                "   where k.zszt=0 \n" +
                                "   and k.zzdj = " + djvalue +
                                "   and instr(','||k.ywzl||',',',"+selectvalue+",')>0 \n" +
                                "   and k.zzlx = " + zzlx +
                                "   and t.szqx= " + districtKey + where  ;

                        rs.executeQuery(sql);
                        while (rs.next()){
                            count++;
                        }
                    }
                    districtMap.put(rowtype+"_"+selecttype,count);
                }
            }
        }

        cityMap.put("subList",districtList);
    }

    Map<String,Object> totalMap = new HashMap<String,Object>();
    totalMap.put("key","total");
    totalMap.put("title","合计");

    cityList.add(totalMap);

    JSONObject dataJson = new JSONObject();
    dataJson.put("data",cityList);

%>

<%=dataJson.toJSONString() %>

