`

汇总统计(部门),涉及结果集转横向(功能样板博客)

汇总统计(部门)涉及结果集转横向时间够用 
阅读更多
[size=medium]
主要按照MVC的顺序,+mapper.xml文件.
业务逻辑,三表连接查询 并按照map<String<Map<String,Object>显示.
重难点,sql语句的编写. 
ViewPersonAction 
package com.myland.jp.adminx.viewPerson.action;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.commons.lang3.StringUtils;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

import com.myland.framework.base.BaseAction;
import com.myland.framework.mybatis.query.Condition;
import com.myland.framework.mybatis.query.CxxString;
import com.myland.framework.mybatis.query.QueryCriterion;
import com.myland.framework.mybatis.query.QueryRulesCreator;
import com.myland.framework.util.collections.MapUtil;
import com.myland.framework.util.time.DateFormatUtil;
import com.myland.framework.util.time.DateUtilConst;
import com.myland.jp.adminx.login.action.AdminLoginAction;
import com.myland.jp.common.service.AgentService;
import com.myland.jp.common.service.CatTypeService;
import com.myland.jp.common.service.DeptService;
import com.myland.jp.common.service.PersonPlanService;
import com.myland.jp.common.service.PlanService;
import com.myland.jp.common.service.ViewPersonPersonPlanService;
import com.myland.pojo.Dept;
import com.myland.pojo.Plan;
import com.myland.view.ViewPersonPersonPlan;

/**
 * 查询学员信息
 * 
 * @author shelly
 * @email sheqian@anjia365.com
 * @create 2014年10月28日上午9:59:09
 */
public class ViewPersonAction extends BaseAction {

    /**
     * 序列号
     */
    private static final long serialVersionUID = 4895270342168140569L;

    /**
     * 日志工具类
     */
    private static final Logger log = LogManager.getLogger(AdminLoginAction.class);

    /**
     * 学员信息实体类
     */
    private ViewPersonPersonPlan viewPerson;

    /**
     * 学员信息列表
     */
    private List<Map> viewList;

    /**
     * 学员信息业务逻辑处理类
     */
    private ViewPersonPersonPlanService viewPersonPersonPlanService;

    /**
     * 标准查询
     */
    private QueryCriterion qc;

    /**
     * 代理商查询学员列表页脚
     */
    private String bar;

    /**
     * 组织者查询学员列表页脚
     */
    private String dbar;

    /**
     * 数据权限Map
     */
    private Map<String, String> resDatasMap;

    /**
     * 代理商对应的权限
     */
    private String resDatas_agent;

    /**
     * 组织者对应的权限
     */
    private String resDatas_dept;

    /**
     * 代理商列表
     */
    private List agentList;

    /**
     * 组织者列表
     */
    private List<Dept> deptList;

    /**
     * 代理商业务逻辑处理类
     */
    private AgentService agentService;

    /**
     * 组织者业务逻辑处理类
     */
    private DeptService deptService;
    
    /**
     * 类别体系的.
     */
    private CatTypeService catTypeService;

    /**
     * 课程计划的,由组织者进行制定.
     */
    private PlanService planService;
    //-----------------service end----------------------------------------------
    /**
     * 代理商Id
     */
    private String agentId;

    /**
     * 组织者Id
     * 用于下拉框 查询接收的值.
     */
    private String deptId;

    /**
     * 用于显示下级明细的市的id
     */
    private String deptId_Param;

    private String licenseType;
    
    /**
     * 页面上用于显示的,不可用.
     */
    private String catTypeId;

    /**
     * 放课程id的.
     */
    private String planId;

    private String cityIds;

    private List<Map<String, Map<String, Object>>> viewCountList; //放弃的 B

    private PersonPlanService personPlanService;

    private List<Map<String, Object>> viewCountList_map;

    /**
     * 当前的组织者权限对应的课程.
     */
    private List<Plan> planList = new ArrayList<Plan>();

    /**
     * 开始时间
     */
    private Date begintime;
    
    /**
     * 结束时间
     */
    private Date endtime;
    
    
    
    // -------------------------------------------------------------------------

    @Override
    public String execute() throws Exception {
        // TODO Auto-generated method stub
        this.initViewPersonPerosnPlan();
        return "agentSuccess";
    }

    /**
     * 代理商学员信息初始化
     * 
     * @author shelly
     * @email sheqian@anjia365.com
     * @create 2014年10月28日上午9:59:09
     */
    public void initViewPersonPerosnPlan() {
        // 从session中获取学习计划的数据权限Map并找到学习计划的部门信息访问权限，进行控制
        resDatasMap = (Map<String, String>) getSession().getAttribute("resDatas");
        if (MapUtil.isNotEmpty(resDatasMap)) {
            resDatas_agent = resDatasMap.get("AGENT_DATA");
        } else {
            return;
        }

        // 查询数据权限范围内的代理商
        agentList = agentService.getAgentByIds(resDatas_agent);
        // 分页查询
        qc = this.getQueryCriterion(new QueryRulesCreator() {

            @Override
            public String createKeyFlds() {
                // TODO Auto-generated method stub
                return null;
            }

            @Override
            public Map<String, Condition> createConditions() {
                // TODO Auto-generated method stub
                Map<String, Condition> conditions = new HashMap<String, Condition>();
                if (viewPerson != null) {
                    String card = viewPerson.getPIdCard();
                    String realName = viewPerson.getPRealName();

                    if (!"".equals(card) && card != null) {
                        conditions.put("pIdCard", new CxxString(card));
                    }

                    if (!"".equals(realName) && realName != null) {
                        conditions.put("pRealName", new CxxString(realName));
                    }
                    if (!"all".equals(agentId) && agentId != null) {
                        conditions.put("agentId", new CxxString(agentId));
                    }
                }
                if (resDatas_agent != null) {
                    conditions.put("ids", new CxxString(resDatas_agent));
                } else {
                    conditions.put("ids", new CxxString("-1"));
                }
                return conditions;
            }
        });

        // qc.getPagination().setPageSize(3);
        qc.getPagination().setShowType("001");
        viewList = viewPersonPersonPlanService.getViewByCondition(qc);
        bar = qc.getPagination().toBar();
    }

    /**
     * 组织者学员信息初始化
     * 
     * @author shelly
     * @email sheqian@anjia365.com
     * @create 2014年10月28日上午9:59:09
     */
    @SuppressWarnings("unchecked")
    public String initViewPersonPerosnPlanForDept() {
        // 从session中获取学习计划的数据权限Map并找到学习计划的部门信息访问权限，进行控制
        resDatasMap = (Map<String, String>) getSession().getAttribute("resDatas");
        if (MapUtil.isNotEmpty(resDatasMap)) {
            resDatas_dept = resDatasMap.get("ORGANIZER_DATA");
        } else {
            return null;
        }
        // 查询数据权限内的组织者
        deptList = deptService.selectDeptByIDs(resDatas_dept);
        // 分页查询
        qc = this.getQueryCriterion(new QueryRulesCreator() {

            @Override
            public String createKeyFlds() {
                // TODO Auto-generated method stub
                return null;
            }

            @Override
            public Map<String, Condition> createConditions() {
                // TODO Auto-generated method stub
                Map<String, Condition> conditions = new HashMap<String, Condition>();
                if(deptId_Param!=null&&!"".equals(deptId_Param)){//传单个的,如南京的等.
                    conditions.put("deptId", new CxxString(deptId_Param));
                }
                if (viewPerson != null) {
                    String card = viewPerson.getPIdCard();
                    String realName = viewPerson.getPRealName();

                    if (!"".equals(card) && card != null) {
                        conditions.put("pIdCard", new CxxString(card));
                    } 

                    if (!"".equals(realName) && realName != null) {
                        conditions.put("pRealName", new CxxString(realName));
                    }

                    if (!"all".equals(deptId) && deptId != null) {
                        conditions.put("deptId", new CxxString(deptId));
                    }
                    
                }
                if (resDatas_dept != null) {  
                    conditions.put("ids", new CxxString(resDatas_dept));
                } else {
                    conditions.put("ids", new CxxString("-1"));
                }
                return conditions;
            }
        });

        // qc.getPagination().setPageSize(3);
        qc.getPagination().setShowType("002");
        viewList = viewPersonPersonPlanService.getViewByConditionForDept(qc);
        dbar = qc.getPagination().toBar();
        return "deptSuccess";
    }

    /**
     * 统计汇总初始化. 初始时候, 
     * 查询使用
     * 
     * @author lengzl
     * @create 2014年12月16日 下午8:46:04
     * @return
     */
    @SuppressWarnings("unchecked")
    public String initViewPersonSummaryForDept() {
        // 从session中获取学习计划的数据权限Map并找到学习计划的部门信息访问权限，进行控制
        resDatasMap = (Map<String, String>) getSession().getAttribute("resDatas"); // user,resData,
        if (MapUtil.isNotEmpty(resDatasMap)) {
            resDatas_dept = resDatasMap.get("ORGANIZER_DATA");
        } else {
            return "noRight";  
        }
        // 查询数据权限内的组织者的所有 市★) 的 信息.
        deptList = deptService.selectDeptAllByIDs(resDatas_dept);
        if(resDatas_dept==null){ //没有赋予权限.
            return "noRight"; 
        }
        planList = planService.getPlanByDepts(deptList);
        
        String jsonTree = catTypeService.getCatTypeJson();
        getRequest().setAttribute("jsonTree", jsonTree);
        
        /*
         //分页
        QueryCriterion qc = getQueryCriterion(new QueryRulesCreator() {
            
            @Override
            public String createKeyFlds() {
                // TODO Auto-generated method stub
                return null;
            }
            
            @Override
            public Map<String, Condition> createConditions() {
                Map<String, Condition> conditions= new HashMap<String, Condition>();
                if(StringUtils.isNotBlank(deptId)){
                    conditions.put("deptId", new CxxString("deptId"));
                }
                
                if(licenseType!=null&&!"".equals(licenseType)){
                    conditions.put("licenseType", new CxxString("licenseType"));
                }
                if(StringUtils.isNotBlank(planId)){
                    conditions.put("planId",new CxxString("planId"));
                }
                //获取开始,结束时间.  //注意加ISO.
                if(begintime!=null){ //Sun Dec 20 00:00:00 CST 2009 页面的个数. 时分秒没办法精确.
                    String beginTimeStr = DateFormatUtil.getFormatValueByPattern(begintime, DateUtilConst.FMT_24H_ISO_yyyyMMddHHmmss);
                    conditions.put("beginTimeStr",new CxxString("beginTimeStr"));
                }
                if(endtime!=null){
                    String endTimeStr = DateFormatUtil.getFormatValueByPattern(endtime, DateUtilConst.FMT_24H_ISO_yyyyMMddHHmmss);
                    conditions.put("endTimeStr",new CxxString("endTimeStr"));
                }
                
                return conditions;
            }
        });
        */
        
        //原来是para参数.
        Map<String, Object> para = new HashMap<String, Object>();
//        如果类别体系查询条件不为空，则添加类别体系的查询条件
        if(StringUtils.isNotBlank(deptId)){ //deptId不为null点击查询的时候. 不需要遍历.
            //否则:即使 全部的查询也需要遍历.
            para.put("deptId", deptId);
        }
        if(licenseType!=null&&!"".equals(licenseType)){
            para.put("licenseType", licenseType);
        }
        if(StringUtils.isNotBlank(planId)){
            para.put("planId", planId);
        }
      //获取开始,结束时间.  //注意不加ISO.
        if(begintime!=null){ //Sun Dec 20 00:00:00 CST 2009 页面的个数. 时分秒没办法精确.
            String beginTimeStr = DateFormatUtil.getFormatValueByPattern(begintime, DateUtilConst.FMT_24H_ISO_yyyyMMddHHmmss);
            para.put("beginTimeStr", beginTimeStr);
        }
        if(endtime!=null){
            String endTimeStr = DateFormatUtil.getFormatValueByPattern(endtime, DateUtilConst.FMT_24H_ISO_yyyyMMddHHmmss);
            para.put("endTimeStr", endTimeStr);
        }
        //重点. ★)
        viewCountList = personPlanService.getViewCountByConditionForDept(para, deptList);
        
        
        
        for (Map<String, Map<String,Object>> result : viewCountList) {
            System.out.println("=======================================================");
            for (Entry<String,Map<String,Object>> entry : result.entrySet()) {
                System.out.println("地市ID:"+entry.getKey());
                for (Entry<String, Object> et : entry.getValue().entrySet()) {
                    System.out.println("类型:"+et.getKey()+"   数量:"+et.getValue());
                }
            }
            System.out.println("=======================================================");
        }
        
        //way2:简化后的.
//        viewCountList_map = personPlanService.getViewCountListByConditionForDept(para, deptList);
        /* 遍历显示 Map的.
        for(Map<String, Map<String, Object>> result : viewCountList){
            System.out.println("===================================================");
            for(Entry<String, Map<String, Object>> entry : result.entrySet()){
                System.out.println("地市ID:"+entry.getKey());
                for(Entry<String, Object> et : entry.getValue().entrySet()){
                    System.out.println("类型："+et.getKey()+"        数量:"+et.getValue());
                }
            }
            System.out.println("===================================================");
        }
        */
        
        return "list";
    }

    /**
     * 下级明细
     * @author lengzl
     * @create 2014年12月18日 下午5:27:11
     * @return
     */
    @SuppressWarnings("unchecked")
    public String subDetial() {
        // 从session中获取学习计划的数据权限Map并找到学习计划的部门信息访问权限，进行控制
        resDatasMap = (Map<String, String>) getSession().getAttribute("resDatas"); // user,resData,
        if (MapUtil.isNotEmpty(resDatasMap)) {
            resDatas_dept = resDatasMap.get("ORGANIZER_DATA");
        } else {
            return null;
        }
        // 查询数据权限内的组织者的所有 区★) 的 信息.
        String jsonTree = catTypeService.getCatTypeJson();
        getRequest().setAttribute("jsonTree", jsonTree);
        
        deptList = deptService.selectSubDeptByIDs(resDatas_dept,deptId_Param);
        if(deptList==null||deptList.size()==0){  //没有下级的部门了.
            return "subList";
        }
        
        planList = planService.getPlanByDepts(deptList);
        
        
        Map<String, Object> para = new HashMap<String, Object>();
        //如果类别体系查询条件不为空，则添加类别体系的查询条件
        if(licenseType!=null&&!"".equals(licenseType)){
            para.put("licenseType", licenseType);
        }
        if(StringUtils.isNotBlank(planId)){
            para.put("planId", planId);
        }
        para.put("deptId", deptId_Param);  // 注意和下拉框的区别.

        viewCountList_map = personPlanService.getViewCountByConditionForDept_sub(para, deptList);
        
        return "subList";
    }

    // -------------------get/set区----------------------------------------------------

    /**
     * @return viewPerson
     */
    public ViewPersonPersonPlan getViewPerson() {
        return viewPerson;
    }

    /**
     * @param viewPerson
     */
    public void setViewPerson(ViewPersonPersonPlan viewPerson) {
        this.viewPerson = viewPerson;
    }

    /**
     * @return viewList
     */
    public List<Map> getViewList() {
        return viewList;
    }

    /**
     * @param viewList
     */
    public void setViewList(List<Map> viewList) {
        this.viewList = viewList;
    }

    /**
     * @return
     */
    public QueryCriterion getQc() {
        return qc;
    }

    /**
     * @param qc
     */
    public void setQc(QueryCriterion qc) {
        this.qc = qc;
    }

    /**
     * @return bar
     */
    public String getBar() {
        return bar;
    }

    /**
     * @param bar
     */
    public void setBar(String bar) {
        this.bar = bar;
    }

    /**
     * @return dbar
     */
    public String getDbar() {
        return dbar;
    }

    public void setDbar(String dbar) {
        this.dbar = dbar;
    }

    /**
     * @param viewPersonPersonPlanService
     */
    public void setViewPersonPersonPlanService(ViewPersonPersonPlanService viewPersonPersonPlanService) {
        this.viewPersonPersonPlanService = viewPersonPersonPlanService;
    }

    /**
     * @return agentList
     */
    public List getAgentList() {
        return agentList;
    }

    /**
     * @param agentList
     */
    public void setAgentList(List agentList) {
        this.agentList = agentList;
    }

    /**
     * @return deptList
     */
    public List<Dept> getDeptList() {
        return deptList;
    }

    /**
     * @param deptList
     */
    public void setDeptList(List<Dept> deptList) {
        this.deptList = deptList;
    }

    /**
     * @return agentId
     */
    public String getAgentId() {
        return agentId;
    }

    /**
     * @param agentId
     */
    public void setAgentId(String agentId) {
        this.agentId = agentId;
    }

    /**
     * @return deptId
     */
    public String getDeptId() {
        return deptId;
    }

    /**
     * @param deptId
     */
    public void setDeptId(String deptId) {
        this.deptId = deptId;
    }

    /**
     * @param agentService
     */
    public void setAgentService(AgentService agentService) {
        this.agentService = agentService;
    }

    /**
     * @param deptService
     */
    public void setDeptService(DeptService deptService) {
        this.deptService = deptService;
    }

    public String getLicenseType() {
        return licenseType;
    }

    public void setLicenseType(String licenseType) {
        this.licenseType = licenseType;
    }

    public String getPlanId() {
        return planId;
    }

    public void setPlanId(String planId) {
        this.planId = planId;
    }

    public String getCityIds() {
        return cityIds;
    }

    public void setCityIds(String cityIds) {
        this.cityIds = cityIds;
    }

    public void setPersonPlanService(PersonPlanService personPlanService) {
        this.personPlanService = personPlanService;
    }

    public List<Map<String, Map<String, Object>>> getViewCountList() {
        return viewCountList;
    }

    public void setViewCountList(List<Map<String, Map<String, Object>>> viewCountList) {
        this.viewCountList = viewCountList;
    }

    public String getDeptId_Param() {
        return deptId_Param;
    }

    public void setDeptId_Param(String deptId_Param) {
        this.deptId_Param = deptId_Param;
    }

    
    public List<Map<String, Object>> getViewCountList_map() {
        return viewCountList_map;
    }

    
    public void setViewCountList_map(List<Map<String, Object>> viewCountList_map) {
        this.viewCountList_map = viewCountList_map;
    }

    
    public void setCatTypeService(CatTypeService catTypeService) {
        this.catTypeService = catTypeService;
    }

    
    public String getCatTypeId() {
        return catTypeId;
    }

    
    public void setCatTypeId(String catTypeId) {
        this.catTypeId = catTypeId;
    }

    
    public List<Plan> getPlanList() {
        return planList;
    }

    
    public void setPlanList(List<Plan> planList) {
        this.planList = planList;
    }

    
    public void setPlanService(PlanService planService) {
        this.planService = planService;
    }

    
    public Date getBegintime() {
        return begintime;
    }

    
    public void setBegintime(Date begintime) {
        this.begintime = begintime;
    }

    
    public Date getEndtime() {
        return endtime;
    }

    
    public void setEndtime(Date endtime) {
        this.endtime = endtime;
    }

}


2. deptViewSummary.jsp 显示汇总统计的. 
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ include file="/common/common.jsp" %> <!-- 引入常用标签库,含有s的. -->

<%@ taglib prefix="s" uri="/struts-tags" %>    
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>分类汇总</title>

<!-- 树形图专属 3个js+2个css -->
<script type="text/javascript" src="${pageContext.request.contextPath}/js/adminx/catType.js"></script>
<script type="text/javascript" src="${pageContext.request.contextPath}/js/common/ztree/jquery.ztree.core-3.5.js"></script>
<script type="text/javascript" src="${pageContext.request.contextPath}/jsp/demo/ztreeNodes.js"></script>
<link href="${pageContext.request.contextPath}/css/common/zTreeStyle/zTreeStyle.css" rel="stylesheet" type="text/css" />
<link href="${pageContext.request.contextPath}/css/common/zTreeStyle/demo.css" rel="stylesheet" type="text/css" />

<%--时间控件部分 --%>
<script type="text/javascript" src="${pageContext.request.contextPath}/js/common/My97DatePicker/WdatePicker.js"></script>
<link rel="stylesheet" type="text/css" href="${pageContext.request.contextPath}/js/common/My97DatePicker/skin/WdatePicker.css">

<%--北京方面制定的样式 添加. --%>
<script type="text/javascript" src="${pageContext.request.contextPath}/js/common/jquery/jquery-1.7.2.js"></script>
<script type="text/javascript" src="${pageContext.request.contextPath}/js/common/myLand.js"></script><!-- 这里有对ajax 数据的一些处理-->
<%--分页用CSS --%>
<link href="${pageContext.request.contextPath}/css/adminx/common_style.css" rel="stylesheet" type="text/css">
<script type="text/javascript" src="${pageContext.request.contextPath}/js/webcustomer/pagination.js"></script>

<link href="${pageContext.request.contextPath}/css/adminx/jquery.fancybox-1.3.4.css" rel="stylesheet" type="text/css" media="screen"></link>
<link href="${pageContext.request.contextPath}/css/adminx/basic_layout.css" rel="stylesheet" type="text/css">

<script type="text/javascript" src="${pageContext.request.contextPath}/js/adminx/commonAll.js"></script>
<script type="text/javascript" src="${pageContext.request.contextPath}/js/adminx/fancybox/jquery.fancybox-1.3.4.js"></script>
<script type="text/javascript" src="${pageContext.request.contextPath}/js/adminx/fancybox/jquery.fancybox-1.3.4.pack.js"></script>
<script type="text/javascript">

	var zNodes = ${jsonTree};
	
	$(document).ready(function(){ //doc的ready事件(fucntion方法)
		$.fn.zTree.init($("#treeDemo"),setting,zNodes);
	});
	
	
	/*
	subDetail(${vc.deptId })
	下级明细
	*/
	function subDetail(id){
		$("#deptId_Param").attr("value",id);
		//alert($("#fm_s").attr("action"));
		$("#fm_s").attr("action","${pageContext.request.contextPath}/adminx/deptViewSummary_subDetial.do");  // deptId_Param=+id post,get都不行 传参,得不到这个.>=3次.★$)
		
     	$("#fm_s").submit();
	}
	
	/*
	学员明细的
	*/
	function personDetail(id){
		$("#deptId_Param").attr("value",id);
		$("#fm_s").attr("action","${pageContext.request.contextPath}/adminx/deptViewPerson.do");
		$("#fm_s").submit();
	}

</script>
</head>
<body>
	<br>
<!--  form 标签选择器,样式整体的.-->
<form id="fm" name ="fm" action="${pageContext.request.contextPath}/adminx/deptViewSummary.do" method="post"> <!--  -->
<div id="container"><!-- 总外容器 含两部分,头部条件,下部分列表显示的-->
	<div class="ui_content"> <!-- 1.头部 溢出隐藏. -->
		<div class="ui_text_indent">
				<%--整体盒子分 三大部分.头部 搜索提示 --%>
				<div id="box_border"> <%--头部的css --%>
					<div id="box_top">汇总统计&nbsp;&nbsp;--搜索</div>
					<div id="box_center">
						组织者:<s:select list="deptList" listValue="name" listKey="id"  headerKey="" headerValue="全部" id="deptId" name="deptId" cssClass="ui_select01"></s:select>
						<%--headKey对应第一个的value. 不写为""喽. --%>
						课程:<s:select list="planList" listValue="name" listKey="id" headerKey="" headerValue="全部" id="planId" name="planId" cssClass="ui_select01"></s:select>
						时间段:
						<s:textfield  id="begintime" name="begintime" cssClass="ui_input_txt02" onfocus="WdatePicker({lang:'zh-cn',dateFmt:'yyyy-MM-dd HH:mm:ss'})"></s:textfield>
						至<s:textfield  id="endtime" name="endtime"  cssClass="ui_input_txt02"  onfocus="WdatePicker({lang:'zh-cn',dateFmt:'yyyy-MM-dd HH:mm:ss'})"></s:textfield>
						<input type="submit" value="查询" class="ui_input_btn01" /> 	
						<input type="button" value="导出" class="ui_input_btn01" />
					</div> <!-- 搜索条件 end -->
					<div id="box_bottom">
							
							<!--<input type="button" value="新增" class="ui_input_btn01" id="addBtn" /> 
							<input type="button" value="删除" class="ui_input_btn01" onClick="batchDel();" /> 
							<input type="button" value="导入" class="ui_input_btn01" id="importBtn" />
							<input type="button" value="导出" class="ui_input_btn01" onClick="exportExcel();" />
							-->
					</div>
					
					<!-- 树状图部分: -->
					<div id="tablestyle" style="width:700px;">
						<p>类别体系:</p> <%--样式需要照搬 catType.js的里的 showMenu(也是.--%>
						<div class="menuContent">
							<div class="zTreeDemoBackground left">
								<s:textfield id="catTypeId" name="catTypeId" readonly="readonly" style="width:120px;" onclick="showMenu();return false;"></s:textfield>
								<s:hidden name="licenseType" id="licenseType"></s:hidden>
							</div>
						</div>
						<div id="menuContent" class="menuContent" style="display:none;position:absolute;">
							<ul id="treeDemo" class="ztree" style="margin-top: 0;width:160px;"></ul>
						</div>
					</div> <!-- 树 end -->
				</div>
		</div><!-- 头部 end -->
	</div><!-- 1.头部 溢出隐藏. end -->


<%--中部 显示具体数据区 --%>
	<div class="ui_content">
		<div class="ui_tb">
			<table class="table" cellspacing="0" cellpadding="0" width="100%" align="center" border="0">
				<s:if test="viewCountList!=null&&viewCountList.size()!=0">  
				<tr>
					<th>省市</th>
					<th>注册人数</th>
					<th>学习人数</th>
					<th>完成人数</th>
					<th colspan="2" align="center">操作</th>
				</tr>
				
				<c:forEach items="${viewCountList }" var="vc"> <!-- items ★$ item×-->
				<tr>
				<%--键的获取更简洁 B--%>
				<c:forEach items="${vc }" var="item">
					<td>
						<myland:cache key="${item.key }" cacheType="_DEPT"></myland:cache> <%--1.★ 显示键值 部门.--%>
					</td>
					<c:set var="status" value="${item.value }" scope="page"></c:set>	<%--2.★ 内层循环遍历Map,先赋值. --%>
				</c:forEach>
				<td>
					<c:choose>
						<c:when test="${status['99'] == null }">
							0
						</c:when>
						<c:otherwise>
							${status['99'] }
						</c:otherwise>
					</c:choose>
				</td>
				<td>
					<c:choose>
						<c:when test="${status['3'] == null }">
							0
						</c:when>
						<c:otherwise>
							${status['3'] }
						</c:otherwise>
					</c:choose>
				</td>
				<td>
					<c:choose>
						<c:when test="${status['4'] == null}" >
							0
						</c:when>
						<c:otherwise>
							${status['4'] }
						</c:otherwise>
					</c:choose>
				</td>
				
				<%-- <c:forEach items="${vc}" var="item">  
						<td>
							<myland:cache key="${item.key}" cacheType="_DEPT" /> <!-- 什么情况使用缓存 dept-->
						</td> 
						<c:set var="stauts" value="${item.value}" scope="page"></c:set>
					</c:forEach> --%>
					
				<%-- 原来的 遍历 B. 
					<td>
						<c:choose>
							<c:when test="${stauts['1'] == null}">
								0
							</c:when>
							<c:otherwise>
								${stauts["1"]}
							</c:otherwise>
						</c:choose>
					</td> 
					<td>
						<c:choose>
							<c:when test="${stauts['3'] == null}">
								0
							</c:when>
							<c:otherwise>
								${stauts["3"]}
							</c:otherwise>
						</c:choose>
					</td>
					<td>
						<c:choose>
							<c:when test="${stauts['4'] == null}">
								0
							</c:when>
							<c:otherwise>
								${stauts["4"]}
							</c:otherwise>
						</c:choose>
					</td> --%>
				
					
					
					<%-- New版本. **)
					<td> <!-- 显示市的值. -->
						<myland:cache key="${vc.deptId_status }" cacheType="_DEPT"></myland:cache>
					</td>
					<td>
						<c:choose>
							<c:when test="${vc['99']==null }">
								0
							</c:when>
						
							<c:otherwise>
								${vc["99"] }
							</c:otherwise>
						</c:choose>
					</td>
					<td>
						<c:choose>
							<c:when test="${vc['3']==null }">
								0
							</c:when>
						
							<c:otherwise>
								${vc["3"] }
							</c:otherwise>
						</c:choose>
					</td>
					<td>
						<c:choose>
							<c:when test="${vc['4']==null }">
								0
							</c:when>
						
							<c:otherwise>
								${vc["4"] }
							</c:otherwise>
						</c:choose>
					</td>
					 --%>
					
					
					<td>
					<span class="chakan">
						<a href="javascript:subDetail('${vc.deptId_status }')" >下级明细</a>
						
						<a href="javascript:personDetail('${vc.deptId_status }')">学员明细</a>
					</span>						
					</td>
				</tr>
			</c:forEach>
			
				<%-- 显示分页的部分--%>
				<tr>
					<td colspan="18" align="center">${bar }</td>
				</tr>
			</s:if>
			<s:else>
				sorry,没有数据.
			</s:else>
			</table>
		</div> <%--中部 end --%>	
	</div><%--ui_content end --%>

</div><!-- container end -->
</form>
	<!-- 专用于提交的fm, 只是传递哪些参数? -->
	<form  name="fm_s" id="fm_s" action="${pageContext.request.contextPath}/adminx/deptViewSummary_subDetial.do" method="post">
		<s:hidden name="deptId_Param" id="deptId_Param"></s:hidden>  <%--B.这种得到的是ids带,的String <input type="hidden" name="deptId_Param" value="${vc.deptId_status }"/> --%>
	</form>
</body>
</html>


3. struts-adminx.xml 
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE struts PUBLIC
    "-//Apache Software Foundation//DTD Struts Configuration 2.1.7//EN"
    "http://struts.apache.org/dtds/struts-2.1.7.dtd">
<struts>
<!-- 去Struts2默认标签样式的. -->
<constant name="struts.ui.theme" value="simple" />
<constant name="struts.ui.templateDir" value="template" />
<constant name="struts.ui.templateSuffix" value="ftl" />

    <!-- 后台管理员action配置 -->
    <package name="adminx" extends="default" namespace="/adminx">
    	<!-- 后台管理员拦截器队列 -->
    	<interceptors>
    		<interceptor-stack name="adminxInterceptorStack">
    			<interceptor-ref name="myLandInterceptorStack"></interceptor-ref>
    		</interceptor-stack>
    	</interceptors>
    	<default-interceptor-ref name="adminxInterceptorStack"/>
    	
    	<!-- 后台用户登录action -->
    	<action name="adminLogin" class="com.myland.jp.adminx.login.action.AdminLoginAction">
    		<result name="success">/adminx/login/adminLogin.jsp</result>
    		<result name="home">/adminx/home/home.jsp</result>
    	</action>
    	
    	<!-- 后台考题管理action -->
    	<action name="examAction" class="com.myland.jp.adminx.exam.action.ExamAction">
    		<result name="success">/adminx/exam/examList.jsp</result>
    		<result name="addexam">/adminx/exam/examAdd.jsp</result>
    		<result name="editexam">/adminx/exam/examUpdate.jsp</result>
    		<result name="batchAddExam">/adminx/exam/examBatchAdd.jsp</result>
    	</action>
    	<action name="examQueryAction" class="com.myland.jp.adminx.exam.action.ExamAction" method="examQueryList">
    		<result name="examList">/adminx/exam/examQueryList.jsp</result>
    	</action>
    	<!-- 后台试卷管理action -->
    	<action name="testPaperAction" class="com.myland.jp.adminx.testpaper.action.TestPaperAction">
    		<result name="success">/adminx/testpaper/testPaperList.jsp</result>
    		<result name="addtestpaper">/adminx/testpaper/testPaperAdd.jsp</result>
    		<result name="forupdatetestpaper">/adminx/testpaper/testPaperUpdate.jsp</result>
    	</action>
    	<action name="testPaperQueryAction" class="com.myland.jp.adminx.testpaper.action.TestPaperAction" method="testPaperQueryList">
    		<result name="testPaperList">/adminx/testpaper/testPaperQueryList.jsp</result>
    	</action>
    	
    	<!-- 学习计划 -->
    	<action name="plan" class="com.myland.jp.adminx.plan.action.PlanAction">
    		<result name="success">/adminx/plan/planList.jsp</result>
    		<result name="conditionSearch">/adminx/plan/planList.jsp</result>
    		<result name="planNode">/adminx/plan/planList.jsp</result>
    		<result name="addPlan">/adminx/plan/planAdd.jsp</result>
    		<result name="modifyPlan">/adminx/plan/planModify.jsp</result>
    	</action>
    	<!-- 学习计划节点 -->
    	<action name="planNode" class="com.myland.jp.adminx.planNode.action.PlanNodeAction">
    		<result name="success">/adminx/plan/planNodeList.jsp</result>
    		<result name="planList" type="redirect">/adminx/plan.do</result>
    		<result name="planDetail">/adminx/plan/planNodeAdd.jsp</result>
    		<result name="planNodeInterface">/adminx/plan/interfaceList.jsp</result>
    		<result name="planNodeJsMethod">/adminx/plan/jsPlanNode.jsp</result>
    		<result name="orderPlanNode">/adminx/plan/orderPlanNode.jsp</result>
    	</action>
    	<action name="planNodeCourse" class="com.myland.jp.adminx.planNode.action.PlanNodeAction" method="showPlanCourse">
    		<result name="planNodeCourse">/adminx/plan/courseList.jsp</result>
    	</action>
    	<action name="planNodeExam" class="com.myland.jp.adminx.planNode.action.PlanNodeAction" method="showPlanExam">
    		<result name="planNodeExam">/adminx/plan/examList.jsp</result>
    	</action>
    	<!-- 代理商 -->
    	<action name="agent" class="com.myland.jp.adminx.agent.action.AgentAction">
    		<result name="success">/adminx/agent/agentList.jsp</result>
    		<result name="addAgent">/adminx/agent/agentAdd.jsp</result>
    		<result name="modifyAgent">/adminx/agent/agentModify.jsp</result>
    		<result name="detailAgent">/adminx/agent/agentDetail.jsp</result>
    		<result name="binded">/adminx/agent/bindedPlanAgent.jsp</result>
    	</action>
    	<!--获取学习计划  -->
    	<action name="bindAgent"  class="com.myland.jp.adminx.agent.action.AgentAction" method="bindAgent">
    		<result name="bindAgent">/adminx/agent/bindAgent.jsp</result>
    	</action>
    	<!-- 已绑定学习计划 -->
    	<action name="bindedPlan"  class="com.myland.jp.adminx.agent.action.AgentAction" method="bindedPlan">
    		<result name="binded">/adminx/agent/bindedPlanAgent.jsp</result>
    	</action>
    	<!-- 供应商 -->
    	<action name="cont" class="com.myland.jp.adminx.cont.action.ContAction">
    		<result name="success">/adminx/cont/contList.jsp</result>
    		<result name="addAgent">/adminx/cont/contAdd.jsp</result>
    		<result name="modifyAgent">/adminx/cont/contModify.jsp</result>
    		<result name="detailAgent">/adminx/cont/contDetail.jsp</result>
    	</action>
    	<!-- 账户信息 -->
    	<action name="payAccnt" class="com.myland.jp.adminx.payAccnt.action.PayAccntAction">
    		<result name="success">/adminx/payAccnt/payAccntList.jsp</result>
    		<result name="addPayAccnt">/adminx/payAccnt/payAccntAdd.jsp</result>
    		<result name="modifyPayAccnt">/adminx/payAccnt/payAccntModify.jsp</result>
    	</action>
    	
    	<action name="addPayAccntAction" class="com.myland.jp.adminx.payAccnt.action.PayAccntAction" method="addPayAccnt">
    		<result name="addPayAccnt">/adminx/payAccnt/payAccntAdd.jsp</result>
    	</action>
    	
    	<action name="agentList" class="com.myland.jp.adminx.payAccnt.action.PayAccntAction" method="loadAgentList">
    		<result name="agentList">/adminx/payAccnt/agentList.jsp</result>
    	</action>
    	<action name="contList" class="com.myland.jp.adminx.payAccnt.action.PayAccntAction" method="loadContList">
    		<result name="contList">/adminx/payAccnt/contList.jsp</result>
    	</action>
    	
    	<!-- 代理商学员查询 -->
    	<action name="agentViewPerson" class="com.myland.jp.adminx.viewPerson.action.ViewPersonAction">
    		<result name="agentSuccess">/adminx/viewPerson/agentViewPersonList.jsp</result>
    	</action>
    	
    	<!-- 组织者学员查询 -->
    	<action name="deptViewPerson" class="com.myland.jp.adminx.viewPerson.action.ViewPersonAction" method="initViewPersonPerosnPlanForDept">
    		<result name="deptSuccess">/adminx/viewPerson/deptViewPersonList.jsp</result>
    	</action>
    	<!--(★ /jpv2/WebContent/adminx/statisticalSummary/deptViewSummary.jsp 组织者 统计汇总. 针对 personPlan的.-->
    	<action name="deptViewSummary" class="com.myland.jp.adminx.viewPerson.action.ViewPersonAction" method="initViewPersonSummaryForDept">
    		<result name="list">/adminx/statisticalSummary/deptViewSummary.jsp</result>
    		<result name="noRight">/adminx/statisticalSummary/promptFile.jsp</result>
    		
    	</action>
    	<!-- 下级明细,也要分页的. -->
    	<action name="deptViewSummary_subDetial" class="com.myland.jp.adminx.viewPerson.action.ViewPersonAction" method="subDetial"> 
    		<result name="subList">/adminx/statisticalSummary/deptViewSummary.jsp</result>
    	</action>
    	
    	
    	<!-- 汇总统计 end -->
    	
    	<!-- 银行信息 -->
    	<action name="payBank" class="com.myland.jp.adminx.payBank.action.PayBankAction">
    		<result name="success">/adminx/payBank/payBankList.jsp</result>
    		<result name="addPayBank">/adminx/payBank/payBankAdd.jsp</result>
    		<result name="modifyPayBank">/adminx/payBank/payBankModify.jsp</result>
    	</action>
    	<!-- 代理商充值 -->
    	<action name="agentChange" class="com.myland.jp.adminx.agentChange.action.AgentChangeAction">
    		<result name="success">/adminx/agentChange/agentList.jsp</result>
    		<result name="modifyAgent">/adminx/agentChange/agentModify.jsp</result>
    		<result name="angetChange">/adminx/agentChange/agentChangeList.jsp</result>
    		<result name="modifyAngetChagne">/adminx/agentChange/agentChangeModify.jsp</result>
    		<result name="payInvoice">/adminx/agentChange/agentInvoiceModify.jsp</result>
    		<result name="agentChangeConfirm">/adminx/agentChange/agentChangeConfirm.jsp</result>
    	</action>
    	<action name="agentChangeLoad" class="com.myland.jp.adminx.agentChange.action.AgentChangeAction" method="loadAgentChange">
    		<result name="angetChange">/adminx/agentChange/agentChangeList.jsp</result>
    	</action>
    	<action name="addAgentChangeAction" class="com.myland.jp.adminx.agentChange.action.AgentChangeAction" method="addAgentChange">
    		<result name="addAgentChange">/adminx/agentChange/agentChangeAdd.jsp</result>
    	</action>
    	<action name="agentChangeAccnt" class="com.myland.jp.adminx.agentChange.action.AgentChangeAction" method="initPayAccntList">
    		<result name="payAccntList">/adminx/agentChange/payAccntList.jsp</result>
    	</action>
    	<action name="agentChangeBank" class="com.myland.jp.adminx.agentChange.action.AgentChangeAction" method="initPayBankList">
    		<result name="payBankList">/adminx/agentChange/payBankList.jsp</result>
    	</action>
    	<!-- 确认 -->
    	<action name="agentChangeConfirmAction" class="com.myland.jp.adminx.agentChange.action.AgentChangeAction" method="agentChangeConfirm">
    		<result name="agentChagneConfirm">/adminx/agentChange/agentChangeConfirm.jsp</result>
    	</action>
    	<!-- 部门操作 -->
    	<action name="deptAction" class="com.myland.jp.adminx.dept.action.DeptAction">
    		<result name="success">/adminx/dept/deptList.jsp</result>
    		<result name="open">/adminx/dept/deptUpdate.jsp</result>
    		<result name="add" type="redirect">/adminx/deptAction.do?conditions=keep</result>
    		<result name="del" type="redirect">/adminx/deptAction.do?conditions=keep</result>
    		<result name="update" type="redirect">/adminx/deptAction.do?conditions=keep</result>
    	</action>
    	
    	<!-- 角色操作action -->
    	<action name="roleAction" class="com.myland.jp.adminx.role.action.RoleAction">
    		<result name="success">/adminx/role/roleList.jsp</result>
    		<result name="add" type="redirect">/adminx/roleAction.do?conditions=keep</result>
    		<result name="open">/adminx/role/roleUpdate.jsp</result>
    		<result name="update" type="redirect">/adminx/roleAction.do?conditions=keep</result>
    		<result name="del" type="redirect">/adminx/roleAction.do?conditions=keep</result>
    		<result name="detail">/adminx/role/roleDetail.jsp</result>
    	</action>
    	
    	<!-- 系统用户操作action begin -->
    	<action name="userAction" class="com.myland.jp.adminx.user.action.UserAction">
    		<result name="add" type="redirect">/adminx/getUsersInfo.do?conditions=keep</result>
    		<result name="view">/adminx/user/userView.jsp</result>
    		<result name="open">/adminx/user/userUpdate.jsp</result>
    		<result name="update" type="redirect">/adminx/getUsersInfo.do?conditions=keep</result>
    		<result name="del" type="redirect">/adminx/getUsersInfo.do?conditions=keep</result>
    	</action>
    	
    	<action name="getUsersInfo" class="com.myland.jp.adminx.user.action.UserAction" method="getUsersInfo">
    		<result name="list">/adminx/user/userList.jsp</result>
    	</action>
    	
    	<action name="openUserAdd" class="com.myland.jp.adminx.user.action.UserAction" method="openUserAdd">
    		<result name="openUserAdd">/adminx/user/userAdd.jsp</result>
    	</action>
    	<!-- 系统用户操作action end -->
    	
    	<!-- 数据权限操作action -->
    	<action name="resDataAction" class="com.myland.jp.adminx.resdata.action.ResDataAction">
    		<result name="open">/adminx/resdata/resdataDetail.jsp</result>
    		<result>/adminx/resdata/resdataDetail.jsp</result>
    	</action>
    	
    	 <!-- 方法名直接指定action的方法 ok-->
    	 
    	<!-- 安全性,使用method,防止动态调用 为了以后  注释不要写到里面-->
    	<action name="simpleHelp" class="com.myland.jp.adminx.resdata.action.SimpleHelpAction" method="simpleHelp">
    		<result name="success">/adminx/resdata/simpleHelp.jsp</result>
    	</action>
    	<!-- 请求 一样.帮助页面 左边的请求 故意不写 与方法名相同,保证权限的. -->
    	<action name="searchData" class="com.myland.jp.adminx.resdata.action.SimpleHelpAction" method="resDataSearch">
    		<result name="list">/adminx/resdata/resDataList.jsp</result>
    	</action>
    	
    	<!-- 数据权限操作 end -->
    	
    	
    	
    	<!-- 后台订单相关操作 -->
    	<action name="queryPayOrderAction" class="com.myland.jp.adminx.pay.action.PayOrderAdminAction"  method="queryPayOrderInfoByPage">
    		<result name="payOrderFlowList">/adminx/pay/payOrderListAdminx.jsp</result>
    		<result name="payOrdersFlowList">/adminx/pay/payOrdersListAdminx.jsp</result>
    		<result name="payResult">/web/pay/alipayapi.jsp</result>
    		<result name="payResults">/web/pay/alipayapi.jsp</result>
    	</action>
    	
    	<action name="queryPayOrdersInfoByPage" class="com.myland.jp.adminx.pay.action.PayOrderAdminAction"  method="queryPayOrdersInfoByPage">
    		<result name="payOrdersFlowList">/adminx/pay/payOrdersListAdminx.jsp</result>
    	</action>
    	
    	<action name="examVersionAction" class="com.myland.jp.adminx.exam.action.ExamVersionAction">
    		<result name="success">/adminx/exam/appExamVersion.jsp</result>
    	</action>
    	
    	<!-- 类别体系 begin -->
		<action name="catTypeAction" class="com.myland.jp.adminx.catType.action.CatTypeAction">
			<result>/adminx/catType/catTypeList.jsp</result>
			<result name="insertCatType">/adminx/catType/catTypeAdd.jsp</result>
			<result name="updateCatType">/adminx/catType/catTypeUpdate.jsp</result>
		</action>
    	<!-- 类别体系end -->
    	
    	<!-- 章节管理 begin -->
		<action name="chapterAction" class="com.myland.jp.adminx.chapter.action.ChapterAction">
			<result name="success">/adminx/chapter/chapterParentList.jsp</result>
			<result name="insertChapterParent">/adminx/chapter/chapterParentAdd.jsp</result>
			<result name="updateChapterParent">/adminx/chapter/chapterParentUpdate.jsp</result>
			<result name="toWorks">/adminx/chapter/works.jsp</result>
			<result name="chapterTopList">/adminx/chapter/chapterTopList.jsp</result>
			<result name="chapterTree">/adminx/chapter/chapterTree.jsp</result>
			<result name="chapterList">/adminx/chapter/chapterList.jsp</result>
			<result name="insertChapter">/adminx/chapter/chapterAdd.jsp</result>
			<result name="updateChapter">/adminx/chapter/chapterUpdate.jsp</result>
		</action>
		<action name="chapterQueryAction" class="com.myland.jp.adminx.chapter.action.ChapterAction" method="chapterQueryList">
			<result name="chapterParentList">/adminx/chapter/chapterQueryList.jsp</result>
		</action>
    	<!-- 章节管理end -->
    	
    	<!-- 章节考题管理 begin -->
		<action name="chapterExamAction" class="com.myland.jp.adminx.chapter.action.ChapterExamAction">
			<result name="success">/adminx/chapter/examHelp.jsp</result>
		</action>
		
		<action name="examListAction" class="com.myland.jp.adminx.chapter.action.ChapterExamAction" method="findExamList">
			<result name="examList">/adminx/chapter/examList.jsp</result>
		</action>
		
    	<!-- 章节考题管理end -->
    	
    	<!-- 课程管理 begin -->
		<action name="courseAction" class="com.myland.jp.adminx.course.action.CourseAction">
			<result name="success">/adminx/course/courseList.jsp</result>
			<result name="insertCourse">/adminx/course/courseAdd.jsp</result>
			<result name="updateCourse">/adminx/course/courseUpdate.jsp</result>
		</action>
		<action name="courseQueryAction" class="com.myland.jp.adminx.course.action.CourseAction" method="courseQueryList">
			<result name="success">/adminx/course/courseQueryList.jsp</result>
		</action>
    	<!-- 课程管理end -->
    	
    	<!-- 课程配置章节begin -->
    	<action name="coursePowerAction" class="com.myland.jp.adminx.course.action.CoursePowerAction">
    		<result name="coursePower">/adminx/course/coursePower.jsp</result>
    		<result name="chapterSelList">/adminx/course/chapterSelectList.jsp</result>
    		<result name="insert" type="redirect">/adminx/chapterSelListAction.do?id=${id}</result>
    		<result name="del" type="redirect">/adminx/chapterSelListAction.do?id=${id}</result>
    	</action>
    	
    	<action name="chapterListAction" class="com.myland.jp.adminx.course.action.CoursePowerAction" method="findChapterList">
    		<result name="chapterList">/adminx/course/chapterList.jsp</result>
    	</action>
    	
    	<action name="chapterSelListAction" class="com.myland.jp.adminx.course.action.CoursePowerAction" method="findSelectChapterList">
    		<result name="chapterSelList">/adminx/course/chapterSelectList.jsp</result>
    	</action>
    	
    	<action name="delCourseChapter" class="com.myland.jp.adminx.course.action.CoursePowerAction" method="delCourseChapter">
    		<result name="chapterSelList">/adminx/course/chapterSelectList.jsp</result>
    	</action>
    	<!-- 课程配置章节end -->
    	
    	<!-- 发票管理 st--><!-- 针对代理商的操作 不可放里面?★$30min-->
    	<action name="payInvoiceAdmin" class="com.myland.jp.adminx.pay.action.PayInvoiceAdminAction">
    		<result name="payInvoiceList">/adminx/pay/payInvoiceListAdminx.jsp</result>
    		<result name="payInvoice" type="chain">
    			<param name="method">queryInvoice</param>
    			<param name="actionName">payInvoiceAdmin</param> 
    			<param name="namespace">/adminx</param> 
    		</result><!-- /adminx/pay/payInvoiceListAdminx_agent.jsp -->
    		<result name="payInvoice_agent" type="chain">
    			<param name="method">queryInvoice_agent</param>
    			<param name="actionName">payInvoiceAdmin_agent</param>
    			<param name="namespace">/adminx</param>
    		</result>
    	</action>
    	<action name="payInvoiceConfirmAdmin" class="com.myland.jp.adminx.pay.action.PayInvoiceAdminAction" method="queryInvoice">
    		<result name="payInvoiceList">/adminx/pay/payInvoiceListAdminx.jsp</result>
    	</action>
    	<!-- 分页不能动态 ! -->
    	<action name="payInvoiceAdmin_agent" class="com.myland.jp.adminx.pay.action.PayInvoiceAdminAction" method="queryInvoice_agent">
    		<result>/adminx/pay/payInvoiceListAdminx_agent.jsp</result>
    		<result name="payInvoiceList_agent">/adminx/pay/payInvoiceListAdminx_agent.jsp</result> <!-- 专门用来分页显示的. -->
    	</action>
    	<!-- 发票管理 end -->
    	
    	<!-- 字典维护 begin -->
    	<action name="dicAction" class="com.myland.jp.adminx.dic.action.DicAction">
    		<result name="success">/adminx/dic/dicMain.jsp</result> <!-- 默认的不想用,配上 -->
    	</action>
    	<!-- 分页一定不可动态代理 -->
    	<action name="queryCodeByTypeAction" class="com.myland.jp.adminx.dic.action.DicAction" method="queryCodeByType">
            <result name="list">/adminx/dic/dicList.jsp</result>
        </action>
    	<!-- 字典维护 end -->
    </package>
    
</struts>


4. PersonPlanMapper.xml 
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.myland.pojo.PersonPlanMapper" >
  <resultMap id="BaseResultMap" type="com.myland.pojo.PersonPlan" >
    <!--
      WARNING - @mbggenerated
      This element is automatically generated by MyBatis Generator, do not modify.
      This element was generated on Wed Sep 24 09:51:51 CST 2014.
    -->
    <id column="id" property="id" jdbcType="BIGINT" />
    <result column="person_id" property="personId" jdbcType="BIGINT" />
    <result column="plan_id" property="planId" jdbcType="BIGINT" />
    <result column="service_uint" property="serviceUint" jdbcType="VARCHAR" />
    <result column="agent_id" property="agentId" jdbcType="BIGINT" />
    <result column="dept_id" property="deptId" jdbcType="BIGINT" />
    <result column="dept_pids" property="deptPids" jdbcType="VARCHAR"/>
    <result column="plan_status" property="planStatus" jdbcType="CHAR" />
    <result column="agent_code" property="agentCode" jdbcType="VARCHAR" />
    <result column="add_time" property="addTime" jdbcType="TIMESTAMP" />
    <result column="add_type" property="addType" jdbcType="CHAR" />
    <result column="license_type" property="licenseType" jdbcType="BIGINT" />
    <result column="plan_type" property="planType" jdbcType="CHAR" />
    <result column="register_face_image" property="registerFaceImage" jdbcType="VARCHAR" />
    <result column="start_date" property="startDate" jdbcType="DATE" />
    <result column="end_date" property="endDate" jdbcType="DATE" />
    <result column="target_study_time" property="targetStudyTime" jdbcType="INTEGER" />
    <result column="sum_study_time" property="sumStudyTime" jdbcType="INTEGER" />
    <result column="last_plan_code" property="lastPlanCode" jdbcType="BIGINT" />
    <result column="last_date" property="lastDate" jdbcType="DATE" />
    <result column="last_cource_id" property="lastCourceId" jdbcType="BIGINT" />
    <result column="last_chapter_id" property="lastChapterId" jdbcType="BIGINT" />
    <result column="last_ware_id" property="lastWareId" jdbcType="VARCHAR" />
    <result column="day_study_time" property="dayStudyTime" jdbcType="INTEGER" />
    <result column="study_count" property="studyCount" jdbcType="INTEGER" />
    <result column="ware_count" property="wareCount" jdbcType="INTEGER" />
  </resultMap>
  <sql id="Base_Column_List" >
    <!--
      WARNING - @mbggenerated
      This element is automatically generated by MyBatis Generator, do not modify.
      This element was generated on Wed Sep 24 09:51:51 CST 2014.
    -->
    id, person_id, plan_id, service_uint, agent_id, dept_id, plan_status, agent_code, 
    add_time, add_type, license_type, plan_type, register_face_image, start_date, end_date, 
    target_study_time, sum_study_time, last_plan_code, last_date, last_cource_id,last_chapter_id,
    last_ware_id,day_study_time, study_count, ware_count
  </sql>
  <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Long" >
    <!--
      WARNING - @mbggenerated
      This element is automatically generated by MyBatis Generator, do not modify.
      This element was generated on Wed Sep 24 09:51:51 CST 2014.
    -->
    select 
    <include refid="Base_Column_List" />,
    ware_count as wareCount
    from t_person_plan
    where id = #{id,jdbcType=BIGINT}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Long" >
    <!--
      WARNING - @mbggenerated
      This element is automatically generated by MyBatis Generator, do not modify.
      This element was generated on Wed Sep 24 09:51:51 CST 2014.
    -->
    delete from t_person_plan
    where id = #{id,jdbcType=BIGINT}
  </delete>
  <insert id="insert" parameterType="com.myland.pojo.PersonPlan" >
    <!--
      WARNING - @mbggenerated
      This element is automatically generated by MyBatis Generator, do not modify.
      This element was generated on Wed Sep 24 09:51:51 CST 2014.
    -->
    insert into t_person_plan (id, person_id, plan_id, 
      service_uint, agent_id, dept_id, 
      plan_status, agent_code, add_time, 
      add_type, license_type, plan_type, 
      register_face_image, start_date, end_date, 
      target_study_time, sum_study_time, last_plan_code, 
      last_date, last_cource_id, last_chapter_id, last_ware_id, 
      day_study_time, study_count, ware_count
      )
    values (#{id,jdbcType=BIGINT}, #{personId,jdbcType=BIGINT}, #{planId,jdbcType=BIGINT}, 
      #{serviceUint,jdbcType=VARCHAR}, #{agentId,jdbcType=BIGINT}, #{deptId,jdbcType=BIGINT}, 
      #{planStatus,jdbcType=CHAR}, #{agentCode,jdbcType=VARCHAR}, #{addTime,jdbcType=TIMESTAMP}, 
      #{addType,jdbcType=CHAR}, #{licenseType,jdbcType=BIGINT}, #{planType,jdbcType=CHAR}, 
      #{registerFaceImage,jdbcType=VARCHAR}, #{startDate,jdbcType=DATE}, #{endDate,jdbcType=DATE}, 
      #{targetStudyTime,jdbcType=INTEGER}, #{sumStudyTime,jdbcType=INTEGER}, #{lastPlanCode,jdbcType=BIGINT}, 
      #{lastDate,jdbcType=DATE}, #{lastCourceId,jdbcType=BIGINT},#{lastChapterId,jdbcType=BIGINT},#{lastWareId,jdbcType=VARCHAR}, 
      #{dayStudyTime,jdbcType=INTEGER}, #{studyCount,jdbcType=INTEGER}, #{wareCount,jdbcType=INTEGER}
      )
  </insert>
  <insert id="insertSelective" parameterType="com.myland.pojo.PersonPlan" >
    <!--
      WARNING - @mbggenerated
      This element is automatically generated by MyBatis Generator, do not modify.
      This element was generated on Wed Sep 24 09:51:51 CST 2014.
    -->
    insert into t_person_plan
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        id,
      </if>
      <if test="personId != null" >
        person_id,
      </if>
      <if test="planId != null" >
        plan_id,
      </if>
      <if test="serviceUint != null" >
        service_uint,
      </if>
      <if test="agentId != null" >
        agent_id,
      </if>
      <if test="deptId != null" >
        dept_id,
      </if>
      <if test="planStatus != null" >
        plan_status,
      </if>
      <if test="agentCode != null" >
        agent_code,
      </if>
      <if test="addTime != null" >
        add_time,
      </if>
      <if test="addType != null" >
        add_type,
      </if>
      <if test="licenseType != null" >
        license_type,
      </if>
      <if test="planType != null" >
        plan_type,
      </if>
      <if test="registerFaceImage != null" >
        register_face_image,
      </if>
      <if test="startDate != null" >
        start_date,
      </if>
      <if test="endDate != null" >
        end_date,
      </if>
      <if test="targetStudyTime != null" >
        target_study_time,
      </if>
      <if test="sumStudyTime != null" >
        sum_study_time,
      </if>
      <if test="lastPlanCode != null" >
        last_plan_code,
      </if>
      <if test="lastDate != null" >
        last_date,
      </if>
      <if test="lastCourceId != null" >
        last_cource_id,
      </if>
      <if test="lastChapterId != null" >
        last_chapter_id,
      </if>
      <if test="lastWareId != null" >
        last_ware_id,
      </if>
      <if test="dayStudyTime != null" >
        day_study_time,
      </if>
      <if test="studyCount != null" >
        study_count,
      </if>
      <if test="wareCount != null" >
        ware_count,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        #{id,jdbcType=BIGINT},
      </if>
      <if test="personId != null" >
        #{personId,jdbcType=BIGINT},
      </if>
      <if test="planId != null" >
        #{planId,jdbcType=BIGINT},
      </if>
      <if test="serviceUint != null" >
        #{serviceUint,jdbcType=VARCHAR},
      </if>
      <if test="agentId != null" >
        #{agentId,jdbcType=BIGINT},
      </if>
      <if test="deptId != null" >
        #{deptId,jdbcType=BIGINT},
      </if>
      <if test="planStatus != null" >
        #{planStatus,jdbcType=CHAR},
      </if>
      <if test="agentCode != null" >
        #{agentCode,jdbcType=VARCHAR},
      </if>
      <if test="addTime != null" >
        #{addTime,jdbcType=TIMESTAMP},
      </if>
      <if test="addType != null" >
        #{addType,jdbcType=CHAR},
      </if>
      <if test="licenseType != null" >
        #{licenseType,jdbcType=BIGINT},
      </if>
      <if test="planType != null" >
        #{planType,jdbcType=CHAR},
      </if>
      <if test="registerFaceImage != null" >
        #{registerFaceImage,jdbcType=VARCHAR},
      </if>
      <if test="startDate != null" >
        #{startDate,jdbcType=DATE},
      </if>
      <if test="endDate != null" >
        #{endDate,jdbcType=DATE},
      </if>
      <if test="targetStudyTime != null" >
        #{targetStudyTime,jdbcType=INTEGER},
      </if>
      <if test="sumStudyTime != null" >
        #{sumStudyTime,jdbcType=INTEGER},
      </if>
      <if test="lastPlanCode != null" >
        #{lastPlanCode,jdbcType=BIGINT},
      </if>
      <if test="lastDate != null" >
        #{lastDate,jdbcType=DATE},
      </if>
      <if test="lastCourceId != null" >
        #{lastCourceId,jdbcType=BIGINT},
      </if>
      <if test="lastChapterId != null" >
        #{lastChapterId,jdbcType=BIGINT},
      </if>
      <if test="lastWareId != null" >
        #{lastWareId,jdbcType=VARCHAR},
      </if>
      <if test="dayStudyTime != null" >
        #{dayStudyTime,jdbcType=INTEGER},
      </if>
      <if test="studyCount != null" >
        #{studyCount,jdbcType=INTEGER},
      </if>
      <if test="wareCount != null" >
        #{wareCount,jdbcType=INTEGER},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.myland.pojo.PersonPlan" >
    <!--
      WARNING - @mbggenerated
      This element is automatically generated by MyBatis Generator, do not modify.
      This element was generated on Wed Sep 24 09:51:51 CST 2014.
    -->
    update t_person_plan
    <set >
      <if test="personId != null" >
        person_id = #{personId,jdbcType=BIGINT},
      </if>
      <if test="planId != null" >
        plan_id = #{planId,jdbcType=BIGINT},
      </if>
      <if test="serviceUint != null" >
        service_uint = #{serviceUint,jdbcType=VARCHAR},
      </if>
      <if test="agentId != null" >
        agent_id = #{agentId,jdbcType=BIGINT},
      </if>
      <if test="deptId != null" >
        dept_id = #{deptId,jdbcType=BIGINT},
      </if>
      <if test="planStatus != null" >
        plan_status = #{planStatus,jdbcType=CHAR},
      </if>
      <if test="agentCode != null" >
        agent_code = #{agentCode,jdbcType=VARCHAR},
      </if>
      <if test="addTime != null" >
        add_time = #{addTime,jdbcType=TIMESTAMP},
      </if>
      <if test="addType != null" >
        add_type = #{addType,jdbcType=CHAR},
      </if>
      <if test="licenseType != null" >
        license_type = #{licenseType,jdbcType=BIGINT},
      </if>
      <if test="planType != null" >
        plan_type = #{planType,jdbcType=CHAR},
      </if>
      <if test="registerFaceImage != null" >
        register_face_image = #{registerFaceImage,jdbcType=VARCHAR},
      </if>
      <if test="startDate != null" >
        start_date = #{startDate,jdbcType=DATE},
      </if>
      <if test="endDate != null" >
        end_date = #{endDate,jdbcType=DATE},
      </if>
      <if test="targetStudyTime != null" >
        target_study_time = #{targetStudyTime,jdbcType=INTEGER},
      </if>
      <if test="sumStudyTime != null" >
        sum_study_time = #{sumStudyTime,jdbcType=INTEGER},
      </if>
      <if test="lastPlanCode != null" >
        last_plan_code = #{lastPlanCode,jdbcType=BIGINT},
      </if>
      <if test="lastDate != null" >
        last_date = #{lastDate,jdbcType=DATE},
      </if>
      <if test="lastCourceId != null" >
        last_cource_id = #{lastCourceId,jdbcType=BIGINT},
      </if>
      <if test="lastChapterId != null" >
        last_chapter_id = #{lastChapterId,jdbcType=BIGINT},
      </if>
      <if test="lastWareId != null" >
        last_ware_id = #{lastWareId,jdbcType=VARCHAR},
      </if>
      <if test="dayStudyTime != null" >
        day_study_time = #{dayStudyTime,jdbcType=INTEGER},
      </if>
      <if test="studyCount != null" >
        study_count = #{studyCount,jdbcType=INTEGER},
      </if>
      <if test="wareCount != null" >
        ware_count = #{wareCount,jdbcType=INTEGER},
      </if>
    </set>
    where id = #{id,jdbcType=BIGINT}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.myland.pojo.PersonPlan" >
    <!--
      WARNING - @mbggenerated
      This element is automatically generated by MyBatis Generator, do not modify.
      This element was generated on Wed Sep 24 09:51:51 CST 2014.
    -->
    update t_person_plan
    set person_id = #{personId,jdbcType=BIGINT},
      plan_id = #{planId,jdbcType=BIGINT},
      service_uint = #{serviceUint,jdbcType=VARCHAR},
      agent_id = #{agentId,jdbcType=BIGINT},
      dept_id = #{deptId,jdbcType=BIGINT},
      plan_status = #{planStatus,jdbcType=CHAR},
      agent_code = #{agentCode,jdbcType=VARCHAR},
      add_time = #{addTime,jdbcType=TIMESTAMP},
      add_type = #{addType,jdbcType=CHAR},
      license_type = #{licenseType,jdbcType=BIGINT},
      plan_type = #{planType,jdbcType=CHAR},
      register_face_image = #{registerFaceImage,jdbcType=VARCHAR},
      start_date = #{startDate,jdbcType=DATE},
      end_date = #{endDate,jdbcType=DATE},
      target_study_time = #{targetStudyTime,jdbcType=INTEGER},
      sum_study_time = #{sumStudyTime,jdbcType=INTEGER},
      last_plan_code = #{lastPlanCode,jdbcType=BIGINT},
      last_date = #{lastDate,jdbcType=DATE},
      last_cource_id = #{lastCourceId,jdbcType=BIGINT},
      last_chapter_id = #{lastChapterId,jdbcType=BIGINT},
      last_ware_id = #{lastWareId,jdbcType=VARCHAR},
      day_study_time = #{dayStudyTime,jdbcType=INTEGER},
      study_count = #{studyCount,jdbcType=INTEGER},
      ware_count = #{wareCount,jdbcType=INTEGER}
    where id = #{id,jdbcType=BIGINT}
  </update>
  
  <!-- =================================自定义sql========================================== -->
   <!-- 查询全部数据 -->
   <select id="selectAllPersonPlans" resultMap="BaseResultMap" parameterType="com.myland.pojo.PersonPlan" >
    select 
    <include refid="Base_Column_List" />
  	from t_person_plan where  1=1
  	<if test="id != null" >
       and id = #{id}
    </if>
    <if test="personId != null" >
       and person_id = #{personId}
    </if>
    <if test="planId != null" >
       and plan_id = #{planId}
    </if>
    <if test="serviceUint != null" >
       and  service_uint like concat(concat('%',#{serviceUint}),'%')
    </if>
    <if test="agentId != null" >
       and agent_id = #{agentId}
    </if>
    <if test="deptId != null" >
       and dept_id = #{deptId}
    </if>
    <if test="planStatus != null" >
       and plan_status = #{planStatus}
    </if>
    <if test="agentCode != null" >
       and agent_code = #{agentCode}
    </if>
    <if test="addTime != null" >
       and add_time = #{addTime}
    </if>
    <if test="addType != null" >
       and add_type = #{addType}
    </if>
    <if test="licenseType != null" >
       and license_type = #{licenseType}
    </if>
    <if test="planType != null" >
       and plan_type = #{planType}
    </if>
    <if test="registerFaceImage != null" >
       and register_face_image = #{registerFaceImage}
    </if>
    <if test="startDate != null" >
       and start_date = #{startDate}
    </if>
    <if test="endDate != null" >
       and end_date = #{endDate}
    </if>
    <if test="targetStudyTime != null" >
       and target_study_time = #{targetStudyTime}
    </if>
    <if test="sumStudyTime != null" >
       and sum_study_time = #{sumStudyTime}
    </if>
    <if test="lastPlanCode != null" >
       and last_plan_code = #{lastPlanCode}
    </if>
    <if test="lastDate != null" >
       and last_date = #{lastDate}
    </if>
    <if test="lastCourceId != null" >
       and last_cource_id = #{lastCourceId}
    </if>
    <if test="lastChapterId != null" >
       and last_chapter_id = #{lastChapterId}
    </if>
    <if test="lastWareId != null" >
       and last_ware_id = #{lastWareId}
    </if>
    <if test="dayStudyTime != null" >
       and day_study_time = #{dayStudyTime}
    </if>
    <if test="studyCount != null" >
       and study_count = #{studyCount}
    </if>
    <if test="wareCount != null" >
       and ware_count = #{wareCount}
    </if>
  </select>
  
  <!-- 通过personId和类别获取对应的学习计划集合 ★)-->
  <select id="selectPersonPlansByPerson" parameterType="map" resultType="map">
	select 
		personPlan.id as personPlanId,
		personPlan.dept_id as deptId,
    	personPlan.agent_id as agentId,
    	personPlan.plan_status as planStatus,
    	personPlan.start_date as startDate,
    	personPlan.end_date as endDate,
    	personPlan.target_study_time as targetStudyTime,
    	personPlan.sum_study_time as sumStudyTime,
    	personPlan.ware_count as currentWareCount,
    	personPlan.last_plan_code as lastPlanCode,
    	personPlan.last_ware_id as lastWareId,
    	personPlan.last_chapter_id as lastChapterId,
    	personPlan.last_cource_id as lastCourseId,
    	plan.id as planId,
    	plan.name as name,
    	plan.plan_type as planType,
    	plan.license_type as licenseType,
    	plan.notes as notes,
    	plan.price as price,
    	plan.show_image as showImage,
    	plan.ware_count as totalWareCount,
    	catType.title as licenseTypeName,
    	plan.face_time as faceTime,
    	plan.is_face AS isFace
  	from t_person_plan personPlan,t_plan plan,t_cat_type catType where personPlan.plan_id = plan.id
  	and catType.id = plan.license_type
    <if test="personId != null" >
       and personPlan.person_id = #{personId}
    </if>
    <if test="licenseTypes != null" >
    	and personPlan.license_type in (${licenseTypes})
    </if> 
    <if test="planStatus != null" >
    	and personPlan.plan_status in (${planStatus})
    </if>  
        order by find_in_set(plan_status,'3,1,2,0,4,9') 
</select>

 <!-- 通过personId和类别获取对应的学习计划集合 -->
  <select id="selectCount" parameterType="map" resultType="map">
	select 
    	personPlan.plan_status as planStatus,
    	count(*) as count
  	from t_person_plan personPlan,t_plan plan,t_cat_type catType where personPlan.plan_id = plan.id
  	and catType.id = plan.license_type
    <if test="personId != null" >
       and personPlan.person_id = #{personId}
    </if>
    <if test="licenseTypes != null" >
    	and personPlan.license_type in (${licenseTypes})
    </if> 
    <if test="planStatus != null" >
    	and personPlan.plan_status =#{planStatus}
    </if>  
        order by find_in_set(plan_status,'3,2,1,4,0,9') 
</select>

<!-- 查出 按照市 进行分组的各种状态的 统计 ★)
resultType 使用什么.

整体分三个部分.
1. 查出南京市的统计.
2. 查出南京市下的选择的区的统计.
3. 联合一下.二者的数据. 
按照学习状态分组
320100
查出 市名,状态,总数。

时间处理. way1. 在数据库中进行格式的转换.  way2:直接在action中进行转换
<![CDATA[   and DATE_FORMAT(tr.summary_date, '%Y-%m-%d')>=  DATE_FORMAT(#{pojo.begintime}, '%Y-%m-%d')   ]]>

-->
<select id="selectCountByDept" parameterType="map" resultType="map">
SELECT
	tmpOut.planStatus planStatus,
	tmpOut.personCount personCount from
(
	
		SELECT
			tmp.plan_status planStatus,
			count(1) personCount
		FROM
			(
				SELECT
					id,
					dept_id,
					person_id,
					dept_pids,
					99 AS plan_status,
					t_person_plan.license_type,
					t_person_plan.plan_id,
					t_person_plan.add_time
				FROM
					t_person_plan
				WHERE
					dept_id = #{deptId}
				UNION
					SELECT
						id,
						person_id,
						dept_id,
						dept_pids,
						99,
						t_person_plan.license_type,
						t_person_plan.plan_id,
						t_person_plan.add_time
					FROM
						t_person_plan
					WHERE
						dept_pids LIKE CONCAT(
							(
								SELECT
									CONCAT(pids, id, ',')
								FROM
									t_dept
								WHERE
									id = #{deptId}
							),
							'%'
						)
			) tmp
		WHERE
			1 = 1
		<if test="licenseType!=null"> <!-- 很多类型,有时候会显示指定的类型.只是指定某种类型吧. 2.-->
	 	and tmp.license_type = #{licenseType}
		</if>
		<if test="planId !=null">
			and tmp.plan_id = #{planId}
		</if>
		<if test="beginTimeStr!=null and beginTimeStr!=''"> <!-- Cau:这种方法成功后,页面直接使用String防止时间 试一下. -->
			<![CDATA[ and date_format(add_time,'%Y-%m-%d %T')>= date_format(#{beginTimeStr},'%Y-%m-%d %T')]]>	
		</if>
		<if test="endTimeStr!=null and endTimeStr!=''">
			<![CDATA[ and date_format(add_time,'%Y-%m-%d %T')<= date_format(#{endTimeStr},'%Y-%m-%d %T')]]>
		</if>			
		AND tmp.plan_status <![CDATA[<>]]> 9

UNION

		SELECT
			tmp2.plan_status planStatus,
			count(1) personCount
		FROM
			(
				SELECT
					id,
					dept_id,
					person_id,
					dept_pids,
					t_person_plan.plan_status,
					t_person_plan.license_type,
					t_person_plan.plan_id,
					t_person_plan.add_time
				FROM
					t_person_plan
				WHERE
					dept_id = #{deptId}
				UNION
					SELECT
						id,
						person_id,
						dept_id,
						dept_pids,
						t_person_plan.plan_status,
						t_person_plan.license_type,
						t_person_plan.plan_id,
						t_person_plan.add_time
					FROM
						t_person_plan
					WHERE
						dept_pids LIKE CONCAT(
							(
								SELECT
									CONCAT(pids, id, ',')
								FROM
									t_dept
								WHERE
									id = #{deptId}
							),
							'%'
						)
			) tmp2
		WHERE
			1 = 1
		<if test="licenseType!=null"> <!-- 很多类型,有时候会显示指定的类型.只是指定某种类型吧. 2.-->
	 		and tmp2.license_type = #{licenseType}
		</if>
		<if test="planId !=null">
			and tmp2.plan_id = #{planId}
		</if>
		<if test="beginTimeStr!=null and beginTimeStr!=''"> <!-- Cau:这种方法成功后,页面直接使用String防止时间 试一下. -->
			<![CDATA[ and date_format(add_time,'%Y-%m-%d %T')>= date_format(#{beginTimeStr},'%Y-%m-%d %T')]]>	
		</if>
		<if test="endTimeStr!=null and endTimeStr!=''">
			<![CDATA[ and date_format(add_time,'%Y-%m-%d %T')<= date_format(#{endTimeStr},'%Y-%m-%d %T')]]>
		</if>			
		AND tmp2.plan_status IN (3, 4)
		GROUP BY
			tmp2.plan_status
) tmpOut
ORDER BY field(tmpOut.planStatus, 99, 3, 4);
</select>


<!-- 下级明细,传一个市区的id可. 就是加上了区的.  -->
<select id="selectCountByCountry" parameterType="map" resultType="map">
SELECT
	tmpOut.planStatus planStatus,
	tmpOut.personCount personCount,tmpOut.deptId deptId from
(
	
		SELECT
			tmp.plan_status planStatus,tmp.dept_id deptId,
			count(1) personCount
		FROM
			(
				SELECT
					id,
					dept_id,
					person_id,
					dept_pids,
					99 AS plan_status,
					t_person_plan.license_type,
					t_person_plan.plan_id,
					t_person_plan.add_time
				FROM
					t_person_plan
				WHERE
					dept_id = #{deptId}
				UNION
					SELECT
						id,
						person_id,
						dept_id,
						dept_pids,
						99,
						t_person_plan.license_type,
						t_person_plan.plan_id,
						t_person_plan.add_time
					FROM
						t_person_plan
					WHERE
						dept_pids LIKE CONCAT(
							(
								SELECT
									CONCAT(pids, id, ',')
								FROM
									t_dept
								WHERE
									id = #{deptId}
							),
							'%'
						)
			) tmp
		WHERE
			1 = 1
		<if test="licenseType!=null"> <!-- 很多类型,有时候会显示指定的类型.只是指定某种类型吧. 2.-->
	 		and tmp.license_type = #{licenseType}
		</if>
		<if test="planId !=null">
			and tmp.plan_id = #{planId}
		</if>
		<if test="beginTimeStr!=null and beginTimeStr!=''"> <!-- Cau:这种方法成功后,页面直接使用String防止时间 试一下. -->
			<![CDATA[ and date_format(add_time,'%Y-%m-%d %T')>= date_format(#{beginTimeStr},'%Y-%m-%d %T')]]>	
		</if>
		<if test="endTimeStr!=null and endTimeStr!=''">
			<![CDATA[ and date_format(add_time,'%Y-%m-%d %T')<= date_format(#{endTimeStr},'%Y-%m-%d %T')]]>
		</if>			
		AND tmp.plan_status <![CDATA[<>]]> 9
		GROUP BY
			tmp.dept_id

UNION

		SELECT
			tmp2.plan_status planStatus,tmp2.dept_id deptId,
			count(1) personCount
		FROM
			(
				SELECT
					id,
					dept_id,
					person_id,
					dept_pids,
					t_person_plan.plan_status,
					t_person_plan.license_type,
					t_person_plan.plan_id,
					t_person_plan.add_time
				FROM
					t_person_plan
				WHERE
					dept_id = #{deptId}
				UNION
					SELECT
						id,
						person_id,
						dept_id,
						dept_pids,
						t_person_plan.plan_status,
						t_person_plan.license_type,
						t_person_plan.plan_id,
						t_person_plan.add_time
					FROM
						t_person_plan
					WHERE
						dept_pids LIKE CONCAT(
							(
								SELECT
									CONCAT(pids, id, ',')
								FROM
									t_dept
								WHERE
									id = #{deptId}
							),
							'%'
						)
			) tmp2
		WHERE
			1 = 1
		<if test="licenseType!=null"> <!-- 很多类型,有时候会显示指定的类型.只是指定某种类型吧. 2.-->
	 		and tmp2.license_type = #{licenseType}
		</if>
		<if test="planId !=null">
			and tmp2.plan_id = #{planId}
		</if>
		<if test="beginTimeStr!=null and beginTimeStr!=''"> <!-- Cau:这种方法成功后,页面直接使用String防止时间 试一下. -->
			<![CDATA[ and date_format(add_time,'%Y-%m-%d %T')>= date_format(#{beginTimeStr},'%Y-%m-%d %T')]]>	
		</if>
		<if test="endTimeStr!=null and endTimeStr!=''">
			<![CDATA[ and date_format(add_time,'%Y-%m-%d %T')<= date_format(#{endTimeStr},'%Y-%m-%d %T')]]>
		</if>			
		AND tmp2.plan_status IN (3, 4)
		GROUP BY
			dept_id,tmp2.plan_status
) tmpOut
ORDER BY deptId,field(tmpOut.planStatus, 99, 3, 4);
</select> <!-- /*按照原名排序就行 ★*/ -->

<!-- 新版  废掉-->
<select id="selectCountByCountry2" parameterType="map" resultType="map">
SELECT
	tmpOut.planStatus planStatus,
	tmpOut.personCount personCount from
(
	
		SELECT
			tmp.plan_status planStatus,tmp.dept_id deptId,
			count(1) personCount
		FROM
			(
				SELECT
					id,
					dept_id,
					person_id,
					dept_pids,
					99 AS plan_status,
					t_person_plan.license_type,
					t_person_plan.plan_id,
					t_person_plan.add_time
				FROM
					t_person_plan
				WHERE
					dept_id = #{deptId}
				UNION
					SELECT
						id,
						person_id,
						dept_id,
						dept_pids,
						99,
						t_person_plan.license_type,
						t_person_plan.plan_id,
						t_person_plan.add_time
					FROM
						t_person_plan
					WHERE
						dept_pids LIKE CONCAT(
							(
								SELECT
									CONCAT(pids, id, ',')
								FROM
									t_dept
								WHERE
									id = #{deptId}
							),
							'%'
						)
			) tmp
		WHERE
			1 = 1
		<if test="licenseType!=null"> <!-- 很多类型,有时候会显示指定的类型.只是指定某种类型吧. 2.-->
	 	and tmp.license_type = #{licenseType}
		</if>
		<if test="planId !=null">
			and tmp.plan_id = #{planId}
		</if>
		<if test="beginTimeStr!=null and beginTimeStr!=''"> <!-- Cau:这种方法成功后,页面直接使用String防止时间 试一下. -->
			<![CDATA[ and date_format(add_time,'%Y-%m-%d %T')>= date_format(#{beginTimeStr},'%Y-%m-%d %T')]]>	
		</if>
		<if test="endTimeStr!=null and endTimeStr!=''">
			<![CDATA[ and date_format(add_time,'%Y-%m-%d %T')<= date_format(#{endTimeStr},'%Y-%m-%d %T')]]>
		</if>			
		AND tmp.plan_status <![CDATA[<>]]> 9

UNION

		SELECT
			tmp2.plan_status planStatus,tmp2.dept_id deptId
			count(1) personCount
		FROM
			(
				SELECT
					id,
					dept_id,
					person_id,
					dept_pids,
					t_person_plan.plan_status,
					t_person_plan.license_type,
					t_person_plan.plan_id,
					t_person_plan.add_time
				FROM
					t_person_plan
				WHERE
					dept_id = #{deptId}
				UNION
					SELECT
						id,
						person_id,
						dept_id,
						dept_pids,
						t_person_plan.plan_status,
						t_person_plan.license_type,
						t_person_plan.plan_id,
						t_person_plan.add_time
					FROM
						t_person_plan
					WHERE
						dept_pids LIKE CONCAT(
							(
								SELECT
									CONCAT(pids, id, ',')
								FROM
									t_dept
								WHERE
									id = #{deptId}
							),
							'%'
						)
			) tmp2
		WHERE
			1 = 1
		<if test="licenseType!=null"> <!-- 很多类型,有时候会显示指定的类型.只是指定某种类型吧. 2.-->
	 		and tmp2.license_type = #{licenseType}
		</if>
		<if test="planId !=null">
			and tmp2.plan_id = #{planId}
		</if>
		<if test="beginTimeStr!=null and beginTimeStr!=''"> <!-- Cau:这种方法成功后,页面直接使用String防止时间 试一下. -->
			<![CDATA[ and date_format(add_time,'%Y-%m-%d %T')>= date_format(#{beginTimeStr},'%Y-%m-%d %T')]]>	
		</if>
		<if test="endTimeStr!=null and endTimeStr!=''">
			<![CDATA[ and date_format(add_time,'%Y-%m-%d %T')<= date_format(#{endTimeStr},'%Y-%m-%d %T')]]>
		</if>			
		AND tmp2.plan_status IN (3, 4)
		GROUP BY
			tmp2.plan_status
) tmpOut
ORDER BY field(tmpOut.planStatus, 99, 3, 4);
</select> <!-- /*按照原名排序就行 ★*/ -->


</mapper>


[/size]
分享到：
| 数据权限的控制 SSH,jdk7 阶段性总结. - ...
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

Magicbox
Global site tag (gtag.js) - Google Analytics