fj-dc/.svn/pristine/84/846d30735a6fedbb4e17c6b25b5984ae13ed0da9.svn-base

<?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="cn.com.goldenwater.dcproj.dao.BisInspAnzeStaticsDao">

    <select id="insurerUnderwritingCount" resultType="Map" parameterType="Integer">
        SELECT B.INSURANCE_COMPANY, COUNT(*) AS CNT
        FROM ATT_PROJECT_INSURANCE A
                 JOIN ATT_PROJECT_INSURANCE_RECORD B ON A.ID = B.PROJECT_ID
        WHERE B.INSURANCE_DATE &gt;= DATE '${year1}-01-01'
          AND B.INSURANCE_DATE &lt; DATE '${year2}-01-01'
        GROUP BY B.INSURANCE_COMPANY
        ORDER BY B.INSURANCE_COMPANY
    </select>

    <select id="anzeCompletion" resultType="Map" parameterType="Integer">
        SELECT A.AD_CODE,
               A.AD_NAME,
               COUNT(DISTINCT A.ID)         AS PROJECT_CNT,
               COUNT(DISTINCT B.PROJECT_ID) AS UNDERWRITING_CNT,
               COUNT(D.OBJ_ID)              AS COMPLETION_CNT
        FROM ATT_PROJECT_INSURANCE A
                 LEFT JOIN (SELECT PROJECT_ID
                            FROM ATT_PROJECT_INSURANCE_RECORD
                            WHERE INSURANCE_DATE &gt;= DATE '${year1}-01-01'
                              AND INSURANCE_DATE &lt; DATE '${year2}-01-01'
                            GROUP BY PROJECT_ID) B ON A.ID = B.PROJECT_ID
                 LEFT JOIN BIS_INSP_ALL_OBJ C ON A.ID = C.CODE
                 LEFT JOIN (SELECT OBJ_ID
                            FROM BIS_INSP_ANZE_RGSTR
                            WHERE STATE IN ('2', '3')) D ON C.OBJ_ID = D.OBJ_ID
        where A.BUSINESS_TYPE = '1'
        GROUP BY A.AD_CODE, A.AD_NAME
        ORDER BY A.AD_CODE
    </select>

<select id="sixmeCompletion" resultType="Map" parameterType="Integer">
        SELECT A.AD_CODE,
               A.AD_NAME,
               COUNT(DISTINCT A.ID)         AS PROJECT_CNT,
               COUNT(R.RECORD_ID)              AS COMPLETION_CNT
        FROM ATT_PROJECT_INSURANCE A
                 LEFT JOIN BIS_INSP_ALL_OBJ C ON A.ID = C.CODE
                 LEFT JOIN BIS_INSP_SIXME_RGSTR D ON C.OBJ_ID = D.OBJ_ID
                 LEFT JOIN (SELECT ID AS RECORD_ID, RGSTR_ID
                            FROM BIS_INSP_SIXME_RECORD
                            WHERE STATE IN ('1')
                              AND INTM &gt;= DATE '${year1}-01-01'
                              AND INTM &lt; DATE '${year2}-01-01') R ON D.ID = R.RGSTR_ID
        where A.BUSINESS_TYPE = '2'
        GROUP BY A.AD_CODE, A.AD_NAME
        ORDER BY A.AD_CODE
    </select>

</mapper>