<?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="revenueStatistics">

    <!-- 清空表 -->

    <update id="truncateTable" >

        truncate table ${tablename}

    </update>

    <!-- 新建并复制表 -->

    <update id="copyToHisTable" >

        CREATE TABLE revenue_statistics_res_his AS SELECT * FROM revenue_statistics_res

    </update>

    <!-- 删除表 -->

    <update id="dropHisTable" >

        drop table revenue_statistics_res_his

    </update>

    <!--查询当前收入最大账期-->

    <select id="getMaxAccountDay" resultType="String">

          SELECT MAX(ACCOUNT_DAY) FROM ct_lan_node_inc

    </select>

    <!--查询所有行业列表-->

    <select id="countRevenueNum" resultType="int">

          select count(1) from revenue_statistics_res

    </select>

    <!--查询所有行业列表-->

    <select id="queryAllIndusty" resultType="java.util.Map">

          select INDUSTRY_TYPE_ID,

               PAR_INDUSTRY_TYPE_ID,

               INDUSTRY_TYPE_GRADE,

               INDUSTRY_TYPE_CODE,

               INDUSTRY_TYPE_NAME

          from industry_type

    </select>

    <!--查询二级市（本地网）-->

    <select id="querySubCity" resultType="java.util.Map">

          select COMMON_REGION_ID,REGION_NAME,REGION_NBR,PAR_REGION_ID from common_region WHERE PAR_REGION_ID = #{REGION_ID}

    </select>

    <!--查询序列-->

    <select id="queryRevenueEQ" resultType="String">

          SELECT SEQ_REVENUE_STATISTICS_RES.NEXTVAL FROM DUAL

    </select>

    <!--查询各省客户总数-->

    <select id="queryTotalNum" parameterType="java.util.Map" resultType="java.util.Map">

          SELECT CUST_TOTAL_ID,REGION_ID,CUST_TOTAL FROM PROVI_CUST_TOTAL

    </select>

    <!--查询全部客户数-->

    <select id="queryAllTotal" parameterType="java.util.Map" resultType="int">

          SELECT sum(CUST_TOTAL) count from provi_cust_total

    </select>

    <!--查询各省客户总数-->

    <select id="queryRegionNbr" parameterType="java.util.Map" resultType="java.util.Map">

          SELECT COMMON_REGION_ID,REGION_NAME,REGION_NBR FROM COMMON_REGION WHERE COMMON_REGION_ID = #{COMMON_REGION_ID}

    </select>

    <select id="queryAllIncome" parameterType="String" resultType="String">

          ${sql2}

    </select>

    <!-- 查询合规收入，改为根据CUST_CP_MERGE表中的PARTY判断 -->

    <select id="queryIncome" parameterType="java.util.Map" resultType="java.util.Map">

          SELECT /*+PARALLEL(12)*/

               COUNT(e.CUST_ID) CUSTNUM, COUNT(DISTINCT(m.PARTY_ID)) PARTYNUM,

             SUM(TY_1) TY1

         FROM EDA_CUST_INC e, CUST_CP_MERGE m

         WHERE 1 = 1

           and e.CUST_ID = m.CUST_ID

           AND e.STD_LATN_CD = m.STD_LATN_CD

           and m.PARTY_ID IS NOT NULL

           AND UPPER(m.PARTY_ID) != 'NULL'

           <if test="STD_PRVNCE_CD != null and STD_PRVNCE_CD != ''">

             AND   e.STD_PRVNCE_CD = #{STD_PRVNCE_CD}

           </if>

           <if test="STD_LATN_CD != null and STD_LATN_CD != ''">

             AND   e.STD_LATN_CD = #{STD_LATN_CD}

           </if>

            <choose>

                    <when  test="PROD_TYPE != null and PROD_TYPE != ''">

                        AND   e.PROD_TYPE = #{PROD_TYPE}

                    </when>

                    <otherwise>

                        AND   e.PROD_TYPE is null

                    </otherwise>

             </choose>

              <choose>

                    <when  test='IDENTITY_TYPE == "-1"'>

                        AND  not EXISTS (SELECT 1

                                              FROM PARTY P

                                             WHERE m.PARTY_ID = p.PARTY_ID)

                    </when>

                    <when  test='IDENTITY_TYPE == "-2"'>

                        AND EXISTS (SELECT 1

                                          FROM PARTY P

                                         WHERE m.PARTY_ID = p.PARTY_ID

                                               AND p.IDENTITY_TYPE is null)

                    </when>

                    <otherwise>

                       AND EXISTS (SELECT 1

                                      FROM PARTY P

                                     WHERE m.PARTY_ID = p.PARTY_ID

                                           AND p.IDENTITY_TYPE = #{IDENTITY_TYPE})

                    </otherwise>

             </choose>

             <choose>

                    <when  test='INDUSTRY_TYPE_ID == "-1"'>

                          and not exists (select 1

                                              from party_org po

                                             where m.party_id = po.party_id)

                    </when>

                    <when  test='INDUSTRY_TYPE_ID == "-2"'>

                         and exists (select 1

                                          from party_org po

                                         where m.party_id = po.party_id

                                               and po.INDUSTRY_TYPE_ID is null)

                    </when>

                    <otherwise>

                       and exists (select 1

                                          from party_org po

                                         where m.party_id = po.party_id

                                               and po.INDUSTRY_TYPE_ID = #{INDUSTRY_TYPE_ID})

                    </otherwise>

             </choose>

    </select>

<

    <!-- 插入统计结果表 -->

    <insert id="insertRevenue2" parameterType="com.tydic.jtcrm.revenueStatistics.vo.RevenueStatistics">

        INSERT INTO REVENUE_STATISTICS_RES

                 ( ID,

                   PROVINCE_REGION_ID,

                   CITY_REGION_ID,

                   REGION_NAME,

                   REGION_GRADE,

                   PROD_TYPE,

                   INDUSTRY_TYPE_ID,

                   INDUSTRY_TYPE_CODE,

                   INDUSTRY_TYPE_NAME,

                   PAR_INDUSTRY_TYPE_ID,

                   INDUSTRY_TYPE_GRADE,

                   IDENTITY_TYPE,

                   IN_DATE,

                   UP_DATE,

                   STATUS_CD,

                   REMARK,

                   CUST_TYPE,

                   ALL_CUST_NUM,

                   ALL_INCOME,

                   AUDIT_CUST_NUM,

                   AUDIT_CUST_PARTY_NUM,

                   TY_1,

                   TY_2,

                   TY_3,

                   TY_4,

                   TY_5,

                   TY_6,

                   TY_7,

                   TY_8,

                   TY_9,

                   TY_10,

                   TY_11,

                   TY_12,

                   LY_1,

                   LY_2,

                   LY_3,

                   LY_4,

                   LY_5,

                   LY_6,

                   LY_7,

                   LY_8,

                   LY_9,

                   LY_10,

                   LY_11,

                   LY_12)

            VALUES

                  (SEQ_REVENUE_STATISTICS_RES.NEXTVAL,

                <choose>

                    <when test="PROVINCE_REGION_ID != null and PROVINCE_REGION_ID != ''">

                        #{PROVINCE_REGION_ID},

                    </when>

                    <otherwise>

                        null,

                    </otherwise>

                </choose>

                <choose>

                    <when test="CITY_REGION_ID != null and CITY_REGION_ID != ''">

                        #{CITY_REGION_ID},

                    </when>

                    <otherwise>

                        null,

                    </otherwise>

                </choose>

                <choose>

                    <when test="REGION_NAME != null and REGION_NAME != ''">

                        #{REGION_NAME},

                    </when>

                    <otherwise>

                        null,

                    </otherwise>

                </choose>

                <choose>

                    <when test="REGION_GRADE != null and REGION_GRADE != ''">

                        #{REGION_GRADE},

                    </when>

                    <otherwise>

                        null,

                    </otherwise>

                </choose>

                <choose>

                    <when test="PROD_TYPE != null and PROD_TYPE != ''">

                      #{PROD_TYPE},

                    </when>

                    <otherwise>

                        null,

                    </otherwise>

               </choose>

               <choose>

                    <when test="INDUSTRY_TYPE_ID!= null and INDUSTRY_TYPE_ID!= ''">

                     #{INDUSTRY_TYPE_ID},

                    </when>

                    <otherwise>

                        null,

                    </otherwise>

               </choose>

               <choose>

                    <when test="INDUSTRY_TYPE_CODE!= null and INDUSTRY_TYPE_CODE!= ''">

                     #{INDUSTRY_TYPE_CODE},

                    </when>

                    <otherwise>

                        null,

                    </otherwise>

               </choose>

               <choose>

                    <when test="INDUSTRY_TYPE_NAME!= null and INDUSTRY_TYPE_NAME!= ''">

                     #{INDUSTRY_TYPE_NAME},

                    </when>

                    <otherwise>

                        null,

                    </otherwise>

               </choose>

                <choose>

                    <when test="PAR_INDUSTRY_TYPE_ID != null and PAR_INDUSTRY_TYPE_ID != ''">

                      #{PAR_INDUSTRY_TYPE_ID},

                    </when>

                    <otherwise>

                        null,

                    </otherwise>

               </choose>

               <choose>

                    <when test="INDUSTRY_TYPE_GRADE!= null and INDUSTRY_TYPE_GRADE != ''">

                     #{INDUSTRY_TYPE_GRADE},

                    </when>

                    <otherwise>

                        null,

                    </otherwise>

               </choose>

               <choose>

                    <when test="IDENTITY_TYPE != null and IDENTITY_TYPE != ''">

                     #{IDENTITY_TYPE},

                    </when>

                    <otherwise>

                        null,

                    </otherwise>

               </choose>

               sysdate,

               sysdate,

                <choose>

                    <when test="STATUS_CD != null and STATUS_CD != ''">

                    #{STATUS_CD},

                    </when>

                    <otherwise>

                        null,

                    </otherwise>

               </choose>

                <choose>

                    <when test="REMARK != null and REMARK != ''">

                     #{REMARK},

                    </when>

                    <otherwise>

                        null,

                    </otherwise>

               </choose>

                <choose>

                    <when test="CUST_TYPE != null and CUST_TYPE != ''">

                     #{CUST_TYPE},

                    </when>

                    <otherwise>

                        null,

                    </otherwise>

               </choose>

                #{ALL_CUST_NUM},

                <choose>

                    <when test="ALL_INCOME != null and ALL_INCOME != ''">

                     #{ALL_INCOME},

                    </when>

                    <otherwise>

                        null,

                    </otherwise>

               </choose>

                <choose>

                    <when test="AUDIT_CUST_NUM != null and AUDIT_CUST_NUM != ''">

                     #{AUDIT_CUST_NUM},

                    </when>

                    <otherwise>

                        null,

                    </otherwise>

               </choose>

                <choose>

                    <when test="AUDIT_CUST_PARTY_NUM != null and AUDIT_CUST_PARTY_NUM != ''">

                     #{AUDIT_CUST_PARTY_NUM},

                    </when>

                    <otherwise>

                        null,

                    </otherwise>

               </choose>

                #{TY_1},

                #{TY_2},

                #{TY_3},

                #{TY_4},

                #{TY_5},

                #{TY_6},

                #{TY_7},

                #{TY_8},

                #{TY_9},

                #{TY_10},

                #{TY_11},

                #{TY_12},

                #{LY_1},

                #{LY_2},

                #{LY_3},

                #{LY_4},

                #{LY_5},

                #{LY_6},

                #{LY_7},

                #{LY_8},

                #{LY_9},

                #{LY_10},

                #{LY_11},

                #{LY_12}

            )

    </insert>

<select id="callRevenue" parameterMap="fMap" statementType="CALLABLE" >

          CALL PRO_REVENUE_SI(?,?,?,?,?,?,?,?,?,?,?,?,?)

</select>

<!-- l_p_cd             in Varchar2,

            l_c_cd             in Varchar2,

            l_prod_type        in Varchar2,

            l_identity_type    in Varchar2,

            l_industry_type_id in Varchar2,

            l_p_id            in Varchar2,

            l_c_id            in Varchar2,

            l_region_name     in Varchar2,

            l_industry_code   in Varchar2,

            l_industry_name   in Varchar2,

            l_par_industry_id in Varchar2,

            l_industry_grade  in Varchar2,

            return_val out Varchar2

            ) re -->

 <parameterMap type="java.util.Map" id="fMap">

         <parameter property="l_p_cd" mode="IN" jdbcType="VARCHAR"/>

         <parameter property="l_c_cd" mode="IN" jdbcType="VARCHAR"/>

         <parameter property="l_prod_type" mode="IN" jdbcType="VARCHAR"/>

         <parameter property="l_identity_type" mode="IN" jdbcType="VARCHAR"/>

         <parameter property="l_industry_type_id" mode="IN" jdbcType="VARCHAR"/>

         <parameter property="l_p_id" mode="IN" jdbcType="VARCHAR"/>

         <parameter property="l_c_id" mode="IN" jdbcType="VARCHAR"/>

         <parameter property="l_region_name" mode="IN" jdbcType="VARCHAR"/>

         <parameter property="l_industry_code" mode="IN" jdbcType="VARCHAR"/>

         <parameter property="l_industry_name" mode="IN" jdbcType="VARCHAR"/>

         <parameter property="l_par_industry_id" mode="IN" jdbcType="VARCHAR"/>

         <parameter property="l_industry_grade" mode="IN" jdbcType="VARCHAR"/>

         <parameter property="return_val" mode="OUT" jdbcType="VARCHAR"/>

</parameterMap>

</mapper>

最新文章

  1. TesseractOCR
  2. Win10添加简体中文美式键盘的方法
  3. Spring MVC 学习笔记 data binding
  4. Python的16个“坑”
  5. POJ 1737 统计有n个顶点的连通图有多少个 （带标号）
  6. PHPRPC for PHP
  7. URAL 1018 (金典树形DP)
  8. 项目Beta冲刺预热
  9. Java-IO之RandomAccessFile
  10. 【学习笔记】JS知识点整理
  11. lucene搜索之高级查询
  12. shell脚本使用技巧5--字符分隔
  13. Javascript 将一个句子中的单词首字母转成大写
  14. datatables插件提示Cannot reinitialise DataTable的解决办法
  15. 矩阵最优路线DP
  16. 关于log4j:WARN No appenders could be found for logger (org.apache.hadoop.metrics2.lib.MutableMetricsFactory).的问题
  17. sqlmap自动注入
  18. Chrome控制台格式化输出
  19. PAT甲题题解-1075. PAT Judge (25)-排序
  20. mysql通过“延迟关联”进行limit分页查询优化的一个实例

热门文章

  1. 前端学习笔记--js概述与基础语法、变量、数据类型、运算符与表达式
  2. 2019-2020-1 20199312《Linux内核原理与分析》第七周作业
  3. 指数基金介绍专栏（8）：国企指数（H股指数）详细介绍，最新资料解析，看这一篇就够了
  4. xml---基础了解
  5. S1_搭建分布式OpenStack集群_01 准备虚拟机
  6. 博客系统的使用（typecho、WordPress等等）
  7. RookeyFrame 迁移 线下Model 新增属性 迁移 到数据库
  8. python 路径引用问题
  9. Note_4.1
  10. 【CSP模拟赛】奇怪的队列（树状数组 &amp;二分&amp;贪心）