<?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.ejweb.modules.report.dao.ReportDao">

    <sql id="reportColumns">
        r.id AS "id",
        r.report_project AS "reportProject",
        r.report_city AS "reportCity",
        r.report_time AS "reportTime",
        r.report_content AS "reportContent",
        r.report_person_name AS "reportPersonName",
        r.report_person_tel AS "reportPersonTel",
        r.report_person_email AS "reportPersonEmail",
        r.report_source AS "reportSource",
        r.report_status AS "reportStatus",
        r.supplement_company AS "supplementCompany",
        r.supplement_department AS "supplementDepartment",
        r.supplement_informant AS "supplementInformant",
        r.supplement_title AS "supplementTitle",
        ct.name AS "transferDepartment",
        r.supplement_project AS "supplementProject",
        r.supplement_content AS "supplementContent",
        r.deal_person_name AS "dealPersonName",
        r.deal_result AS "dealResult",
        r.exchange_before_user AS "exchangeBeforeUser",
        r.exchange_after_user AS "exchangeAfterUser",
        r.exchange_type AS "exchangeType",
        r.create_by AS "createBy",
        r.create_date AS "createDate",
        r.update_by AS "updateBy",
        r.update_date AS "updateDate",
        r.oa_name AS "oaname",
        r.report_ip AS "reportIp",
        r.ip_city AS "ipCity",
        r.is_send_email AS "isSendEmail",
        su.area AS "oaarea",
        r.report_participants AS "participants",
        r.report_insider AS "insider",
        r.report_suggestion AS "suggestion",
        r.report_Date AS "reportDate",
        r.report_site AS "site",
        r.report_qq AS "qq",
        r.report_weixin AS "weixin",
        r.report_ifClue AS "ifClue" ,
        r.supplement_category AS "supplementCategory",
        r.supplement_type AS "supplementType"



    </sql>

    <sql id="reportJoins">
        LEFT JOIN ct_bbtc_report_attachment ra ON r.id = ra.report_id
    </sql>

    <select id="get" parameterType="ReportEntity" resultType="ReportEntity">
        SELECT
        <include refid="reportColumns"/>
        ,su.name AS "transferName"
        ,b.label AS "supplementGroup"
        ,b2.label AS "transferGroup"
        ,cr2.name AS "transferArea"
        ,cr.name AS "supplementArea"
        FROM ct_bbtc_report r
        LEFT JOIN ct_bbtc_sys_user su ON r.exchange_after_user = su.id
        LEFT JOIN ct_bbtc_region CR ON r.supplement_area = CR.id
        LEFT JOIN  ct_bbtc_bus_type CT ON r.transfer_department = CT.id
        LEFT JOIN ct_bbtc_sys_dict b on b.id = r.supplement_group
        LEFT JOIN ct_bbtc_sys_dict b2 on b2.id = r.transferGroup
        LEFT JOIN ct_bbtc_region CR2 ON r.transferArea = CR2.id

       /* LEFT JOIN ct_bbtc_bus_type CT
        ON R.supplement_group = CT.supplement_group
        LEFT JOIN ct_bbtc_bus_category CC
        ON R.supplement_type = CC.type*/
        WHERE r.id = #{id}
    </select>

    <select id="findList" parameterType="ReportEntity" resultType="ReportEntity">
        SELECT
        <include refid="reportColumns"/>
        ,su.name AS "transferName"
        ,b.label AS "supplementGroup"
        ,b2.label AS "transferGroup"
        ,cr2.name AS "transferArea"
        ,cr.name AS "supplementArea"
        FROM CT_BBTC_REPORT R
        LEFT JOIN CT_BBTC_SYS_USER SU
        ON R.create_by = SU.ID
        LEFT JOIN CT_BBTC_SYS_USER2ROLE UR
        ON UR.USER_ID = SU.ID
        LEFT JOIN CT_BBTC_SYS_ROLE RL
        ON RL.ID = UR.ROLE_ID
        LEFT JOIN ct_bbtc_sys_dict b on b.id = r.supplement_group
        LEFT JOIN ct_bbtc_region CR  ON r.supplement_area = CR.id
        LEFT JOIN  ct_bbtc_bus_type CT
        ON r.transfer_department = CT.id
        LEFT JOIN ct_bbtc_sys_dict b2 on b2.id = r.transferGroup
        LEFT JOIN ct_bbtc_region CR2 ON r.transferArea = CR2.id
        <where>
            1 = 1
            <if test="isAdmin == null and isFzAdmin == null">
                AND r.exchange_after_user = #{exchangeAfterUser} AND r.create_by != #{exchangeAfterUser}
            </if>
            <if test="isAdmin != null">
                AND (su.role_id='1'or su.role_id='2')
            </if>
            <if test="isFzAdmin != null">
                AND ((R.EXCHANGE_BEFORE_USER = #{exchangeBeforeUser} OR
                R.EXCHANGE_AFTER_USER = #{exchangeBeforeUser}
                OR R.CREATE_BY = #{exchangeBeforeUser})) AND SU.AREA IS NULL
            </if>
            <if test="reportStatus != null and reportStatus != ''">
                AND r.report_status = #{reportStatus}
            </if>
            <if test="supplementTitle != null and supplementTitle != ''">
                AND r.supplement_title LIKE
                <if test="dbName == 'oracle'">'%'||#{supplementTitle}||'%'</if>
                <if test="dbName == 'mssql'">'%'+#{supplementTitle}+'%'</if>
                <if test="dbName == 'mysql'">CONCAT('%', #{supplementTitle}, '%')</if>
            </if>
            <if test="reportProject != null and reportProject != ''">
                AND (r.report_project LIKE
                <if test="dbName == 'oracle'">'%'||#{reportProject}||'%'</if>
                <if test="dbName == 'mssql'">'%'+#{reportProject}+'%'</if>
                <if test="dbName == 'mysql'">CONCAT('%', #{reportProject}, '%')</if>
                OR r.supplement_department LIKE
                <if test="dbName == 'oracle'">'%'||#{reportProject}||'%'</if>
                <if test="dbName == 'mssql'">'%'+#{reportProject}+'%'</if>
                <if test="dbName == 'mysql'">CONCAT('%', #{reportProject}, '%')</if>
                )
            </if>
            <if test="reportTimeFrom != null and reportTimeFrom != ''">
                AND r.report_time &gt;= #{reportTimeFrom}
            </if>
            <if test="reportTimeTo != null and reportTimeTo != ''">
                AND r.report_time &lt;= #{reportTimeTo}|| ' 23:59:59'
            </if>
            <if test="reportPersonName != null and reportPersonName != ''">
                AND r.report_person_name LIKE
                <if test="dbName == 'oracle'">'%'||#{reportPersonName}||'%'</if>
                <if test="dbName == 'mssql'">'%'+#{reportPersonName}+'%'</if>
                <if test="dbName == 'mysql'">CONCAT('%', #{reportPersonName}, '%')</if>
            </if>
            <if test="reportPersonTel != null and reportPersonTel != ''">
                AND r.report_person_tel LIKE
                <if test="dbName == 'oracle'">'%'||#{reportPersonTel}||'%'</if>
                <if test="dbName == 'mssql'">'%'+#{reportPersonTel}+'%'</if>
                <if test="dbName == 'mysql'">CONCAT('%', #{reportPersonTel}, '%')</if>
            </if>
            <if test="supplementType != null and supplementType != ''">
                AND r.supplement_type LIKE
                <if test="dbName == 'oracle'">'%'||#{supplementType}||'%'</if>
                <if test="dbName == 'mssql'">'%'+#{supplementType}+'%'</if>
                <if test="dbName == 'mysql'">CONCAT('%', #{supplementType}, '%')</if>
            </if>
            <if test="dealResult != null and dealResult != ''">
                AND r.deal_result = #{dealResult}

            </if>
            <if test="supplementInformant != null and supplementInformant != ''">
                AND r.supplement_informant LIKE
                <if test="dbName == 'oracle'">'%'||#{supplementInformant}||'%'</if>
                <if test="dbName == 'mssql'">'%'+#{supplementInformant}+'%'</if>
                <if test="dbName == 'mysql'">CONCAT('%', #{supplementInformant}, '%')</if>
            </if>
            <if test="supplementGroup != null and supplementGroup != ''">
                AND r.supplement_group = #{supplementGroup}
            </if>
            <if test="supplementArea != null and supplementArea != ''">
                AND r.supplement_area = #{supplementArea}
            </if>
            <if test="supplementCategory != null and supplementCategory != ''">
                AND r.supplement_category LIKE
                <if test="dbName == 'oracle'">'%'||#{supplementCategory}||'%'</if>
                <if test="dbName == 'mssql'">'%'+#{supplementCategory}+'%'</if>
                <if test="dbName == 'mysql'">CONCAT('%', #{supplementCategory}, '%')</if>
            </if>

            <if test="transferGroup != null and transferGroup != ''">
                AND r.transferGroup = #{transferGroup}
            </if>

              <if test="transferArea != null and transferArea != ''">
                AND r.transferArea = #{transferArea}
            </if>

            <if test="transferDepartment != null and transferDepartment != ''">
                AND r.transfer_department LIKE
                <if test="dbName == 'oracle'">'%'||#{transferDepartment}||'%'</if>
                <if test="dbName == 'mssql'">'%'+#{transferDepartment}+'%'</if>
                <if test="dbName == 'mysql'">CONCAT('%', #{transferDepartment}, '%')</if>
            </if>

            <if test="reportSource != null and reportSource != ''">
                AND r.report_source = #{reportSource}
            </if>
            <if test="selectExchangeAfterUser != null and selectExchangeAfterUser != ''">
                AND r.exchange_after_user = #{selectExchangeAfterUser}
            </if>
            <if test="oaname != null and oaname != ''">
                AND r.oa_name = #{oaname}
            </if>
            <choose>
                <when test="flage != null and flage != ''">
                    AND r.deal_result = 7
                </when>
                <otherwise>
                    AND (  r.deal_result != 7  OR r.deal_result IS NULL )
                </otherwise>
            </choose>
        </where>
        ORDER BY r.update_date DESC
    </select>

    <select id="findListByCheck" parameterType="ReportEntity" resultType="ReportEntity">
        SELECT
        <include refid="reportColumns"/>
        ,su.name AS "transferName"
        ,b.label AS "supplementGroup"
        ,b2.label AS "transferGroup"
        ,cr2.name AS "transferArea"
        ,cr.name AS "supplementArea"
        FROM CT_BBTC_REPORT R
        LEFT JOIN CT_BBTC_SYS_USER SU
        ON R.CREATE_BY = SU.ID
        LEFT JOIN CT_BBTC_SYS_USER2ROLE UR
        ON UR.USER_ID = SU.ID
        LEFT JOIN CT_BBTC_SYS_ROLE RL
        ON RL.ID = UR.ROLE_ID
        LEFT JOIN ct_bbtc_sys_dict b on b.id = r.supplement_group
        LEFT JOIN ct_bbtc_region CR
        ON r.supplement_area = CR.id
        LEFT JOIN  ct_bbtc_bus_type CT
        ON r.transfer_department = CT.id
        LEFT JOIN ct_bbtc_sys_dict b2 on b2.id = r.transferGroup
        LEFT JOIN ct_bbtc_region CR2 ON r.transferArea = CR2.id
        <where>
            1 = 1
            <if test="supplementType != null and supplementType != ''">
                AND r.supplement_type LIKE
                <if test="dbName == 'oracle'">'%'||#{supplementType}||'%'</if>
                <if test="dbName == 'mssql'">'%'+#{supplementType}+'%'</if>
                <if test="dbName == 'mysql'">CONCAT('%', #{supplementType}, '%')</if>
            </if>
            <if test="supplementGroup != null and supplementGroup != ''">
                AND r.supplement_group = #{supplementGroup}
            </if>
            <if test="supplementArea != null and supplementArea != ''">
                AND r.supplement_area = #{supplementArea}
            </if>
            <if test="supplementCategory != null and supplementCategory != ''">
                AND r.supplement_category LIKE
                <if test="dbName == 'oracle'">'%'||#{supplementCategory}||'%'</if>
                <if test="dbName == 'mssql'">'%'+#{supplementCategory}+'%'</if>
                <if test="dbName == 'mysql'">CONCAT('%', #{supplementCategory}, '%')</if>
            </if>
            <if test="transferGroup != null and transferGroup != ''">
                AND r.transferGroup = #{transferGroup}
            </if>
             <if test="transferArea != null and transferArea != ''">
                AND r.transferArea = #{transferArea}
            </if>

            <if test="transferDepartment != null and transferDepartment != ''">
                AND r.transfer_department LIKE
                <if test="dbName == 'oracle'">'%'||#{transferDepartment}||'%'</if>
                <if test="dbName == 'mssql'">'%'+#{transferDepartment}+'%'</if>
                <if test="dbName == 'mysql'">CONCAT('%', #{transferDepartment}, '%')</if>
            </if>
        </where>
    </select>

    <select id="findQyList" parameterType="ReportEntity" resultType="ReportEntity">
        SELECT
        <include refid="reportColumns"/>
        ,su.name AS "transferName"
        ,b.label AS "supplementGroup"
        ,b2.label AS "transferGroup"
        ,cr2.name AS "transferArea"
        ,cr.name AS "supplementArea"

        FROM ct_bbtc_report r
        LEFT JOIN ct_bbtc_sys_user su ON r.create_by = su.id
        LEFT JOIN CT_BBTC_SYS_USER2ROLE UR ON UR.USER_ID = su.ID
        LEFT JOIN CT_BBTC_SYS_ROLE RL
        ON RL.ID = UR.ROLE_ID
        LEFT JOIN ct_bbtc_sys_dict b on b.id = r.supplement_group
        LEFT JOIN ct_bbtc_region CR
        ON r.supplement_area = CR.id
        LEFT JOIN  ct_bbtc_bus_type CT
        ON r.transfer_department = CT.id
        LEFT JOIN ct_bbtc_sys_dict b2 on b2.id = r.transferGroup
        LEFT JOIN ct_bbtc_region CR2 ON r.transferArea = CR2.id
       /* LEFT JOIN ct_bbtc_bus_type CT
        ON R.supplement_group = CT.supplement_group
        LEFT JOIN ct_bbtc_bus_category CC
        ON R.supplement_type = CC.type*/
        <where>
            1 = 1
            <if test="isAdmin == null and isFzAdmin == null">
                AND r.exchange_after_user = #{exchangeAfterUser} AND r.create_by = #{exchangeAfterUser}
            </if>
            <if test="isAdmin != null">
                /*
                AND su.area IN ('North China',
                'BeiJing',
                'ShangHai',
                'SouthWest',
                'SouthEast',
                'Central China',
                'GuangShen',
                'Hainan',
                'Group',
                'PropertyGroup')
                */
                AND su.area IS NOT NULL
            </if>
            <if test="isFzAdmin != null">
                AND r.Exchange_After_User = #{exchangeBeforeUser}
                AND r.create_by != #{exchangeBeforeUser}
               /*
               AND su.area IN ('North China',
                'BeiJing',
                'ShangHai',
                'SouthWest',
                'SouthEast',
                'Central China',
                'GuangShen',
                'Hainan',
                'Group',
                'PropertyGroup')
                */
                AND su.area IS NOT NULL
            </if>
            <if test="reportStatus != null and reportStatus != ''">
                AND r.report_status = #{reportStatus}
            </if>
            <if test="supplementTitle != null and supplementTitle != ''">
                AND r.supplement_title LIKE
                <if test="dbName == 'oracle'">'%'||#{supplementTitle}||'%'</if>
                <if test="dbName == 'mssql'">'%'+#{supplementTitle}+'%'</if>
                <if test="dbName == 'mysql'">CONCAT('%', #{supplementTitle}, '%')</if>
            </if>
            <if test="reportProject != null and reportProject != ''">
                AND (r.report_project LIKE
                <if test="dbName == 'oracle'">'%'||#{reportProject}||'%'</if>
                <if test="dbName == 'mssql'">'%'+#{reportProject}+'%'</if>
                <if test="dbName == 'mysql'">CONCAT('%', #{reportProject}, '%')</if>
                OR r.supplement_department LIKE
                <if test="dbName == 'oracle'">'%'||#{reportProject}||'%'</if>
                <if test="dbName == 'mssql'">'%'+#{reportProject}+'%'</if>
                <if test="dbName == 'mysql'">CONCAT('%', #{reportProject}, '%')</if>
                )
            </if>
            <if test="reportTimeFrom != null and reportTimeFrom != ''">
                AND r.report_time &gt;= #{reportTimeFrom}
            </if>
            <if test="reportTimeTo != null and reportTimeTo != ''">
                AND r.report_time &lt;= #{reportTimeTo}|| ' 23:59:59'
            </if>
            <if test="reportPersonName != null and reportPersonName != ''">
                AND r.report_person_name LIKE
                <if test="dbName == 'oracle'">'%'||#{reportPersonName}||'%'</if>
                <if test="dbName == 'mssql'">'%'+#{reportPersonName}+'%'</if>
                <if test="dbName == 'mysql'">CONCAT('%', #{reportPersonName}, '%')</if>
            </if>
            <if test="reportPersonTel != null and reportPersonTel != ''">
                AND r.report_person_tel LIKE
                <if test="dbName == 'oracle'">'%'||#{reportPersonTel}||'%'</if>
                <if test="dbName == 'mssql'">'%'+#{reportPersonTel}+'%'</if>
                <if test="dbName == 'mysql'">CONCAT('%', #{reportPersonTel}, '%')</if>
            </if>
            <if test="supplementType != null and supplementType != ''">
                AND r.supplement_type LIKE
                <if test="dbName == 'oracle'">'%'||#{supplementType}||'%'</if>
                <if test="dbName == 'mssql'">'%'+#{supplementType}+'%'</if>
                <if test="dbName == 'mysql'">CONCAT('%', #{supplementType}, '%')</if>
            </if>
            <if test="dealResult != null and dealResult != ''">
                AND r.deal_result = #{dealResult}

            </if>
            <if test="supplementInformant != null and supplementInformant != ''">
                AND r.supplement_informant LIKE
                <if test="dbName == 'oracle'">'%'||#{supplementInformant}||'%'</if>
                <if test="dbName == 'mssql'">'%'+#{supplementInformant}+'%'</if>
                <if test="dbName == 'mysql'">CONCAT('%', #{supplementInformant}, '%')</if>
            </if>
            <if test="supplementGroup != null and supplementGroup != ''">
                AND r.supplement_group = #{supplementGroup}
            </if>
            <if test="supplementArea != null and supplementArea != ''">
                AND r.supplement_area = #{supplementArea}
            </if>
            <if test="supplementCategory != null and supplementCategory != ''">
                AND r.supplement_category = #{supplementCategory}
            </if>
            <if test="supplementCategory != null and supplementCategory != ''">
                AND r.supplement_category LIKE
                <if test="dbName == 'oracle'">'%'||#{supplementCategory}||'%'</if>
                <if test="dbName == 'mssql'">'%'+#{supplementCategory}+'%'</if>
                <if test="dbName == 'mysql'">CONCAT('%', #{supplementCategory}, '%')</if>
            </if>
            <if test="transferGroup != null and transferGroup != ''">
                AND r.transferGroup = #{transferGroup}
            </if>
              <if test="transferArea != null and transferArea != ''">
                AND r.transferArea = #{transferArea}
            </if>

            <if test="transferDepartment != null and transferDepartment != ''">
                AND r.transfer_department LIKE
                <if test="dbName == 'oracle'">'%'||#{transferDepartment}||'%'</if>
                <if test="dbName == 'mssql'">'%'+#{transferDepartment}+'%'</if>
                <if test="dbName == 'mysql'">CONCAT('%', #{transferDepartment}, '%')</if>
            </if>
            <if test="reportSource != null and reportSource != ''">
                AND r.report_source = #{reportSource}
            </if>
            <if test="selectExchangeAfterUser != null and selectExchangeAfterUser != ''">
                AND r.exchange_after_user = #{selectExchangeAfterUser}
            </if>
            <if test="oaname != null and oaname != ''">
                AND r.oa_name = #{oaname}
            </if>
            <choose>
                <when test="flage != null and flage != ''">
                    AND r.deal_result = 7
                </when>
                <otherwise>
                    AND (  r.deal_result != 7  OR r.deal_result IS NULL )
                </otherwise>
            </choose>
        </where>
        ORDER BY r.update_date DESC
    </select>

    <select id="findAllList" parameterType="ReportEntity" resultType="ReportEntity">
        SELECT
        <include refid="reportColumns"/>
        ,b.label AS "supplementGroup"
        ,b2.label AS "transferGroup"
        ,cr2.name AS "transferArea"
        ,cr.name AS "supplementArea"
        FROM ct_bbtc_report r
        LEFT JOIN ct_bbtc_region CR
        ON r.supplement_area = CR.id
        LEFT JOIN  ct_bbtc_bus_type CT
        ON r.transfer_department = CT.id
        LEFT JOIN ct_bbtc_sys_dict b on b.id = r.supplement_group
        LEFT JOIN ct_bbtc_sys_dict b2 on b2.id = r.transferGroup
        LEFT JOIN ct_bbtc_region CR2 ON r.transferArea = CR2.id
       /* LEFT JOIN ct_bbtc_bus_type CT
        ON R.supplement_group = CT.supplement_group
        LEFT JOIN ct_bbtc_bus_category CC
        ON R.supplement_type = CC.type*/
        <choose>
            <when test="page !=null and page.orderBy != null and page.orderBy != ''">
                ORDER BY ${page.orderBy}
            </when>
            <otherwise>
                ORDER BY r.update_date DESC
            </otherwise>
        </choose>
    </select>

    <select id="findRole" parameterType="User" resultType="String">
        SELECT
        sr.name AS "roleName"
        FROM ct_bbtc_sys_role sr
        LEFT JOIN ct_bbtc_sys_user2role sur ON sr.id = sur.role_id
        LEFT JOIN ct_bbtc_sys_user su ON su.id = sur.user_id
        WHERE su.id = #{id}
    </select>

    <select id="getAttachmentInfo" resultType="ReportAttachmentEntity">
        SELECT
        ra.attachment_name,
        ra.attachment_path
        FROM ct_bbtc_report_attachment ra
        WHERE ra.report_id = #{reportId}
        AND ra.attachment_type = #{attachmentType}
    </select>

    <insert id="addReport">
        INSERT INTO ct_bbtc_report(
        id,
        report_project,
        report_city,
        report_time,
        report_content,
        report_person_name,
        report_person_tel,
        report_person_email,
        report_source,
        report_status,
        supplement_informant,
        supplement_title,
        exchange_before_user,
        exchange_after_user ,
        exchange_type,
        create_by,
        create_date,
        update_by,
        update_date,
        oa_name,
        report_ip,
        ip_city,
        report_participants,
        report_insider,
        report_suggestion,
        report_Date,
        report_site,
        report_qq,
        report_weixin

        ) VALUES (
        #{id},
        #{reportProject},
        #{reportCity},
        #{reportTime},
        #{reportContent},
        #{reportPersonName},
        #{reportPersonTel},
        #{reportPersonEmail},
        #{reportSource},
        #{reportStatus},
        #{supplementInformant},
        #{supplementTitle},
        #{exchangeBeforeUser},
        #{exchangeAfterUser},
        #{exchangeType},
        #{createBy.id},
        #{createDate},
        #{updateBy.id},
        #{updateDate},
        #{oaname},
        #{reportIp},
        #{ipCity},
        #{participants},
        #{insider},
        #{suggestion},
        #{reportDate},
        #{site},
        #{qq},
        #{weixin}
        )
    </insert>

    <insert id="addReportAttachment">
        INSERT INTO ct_bbtc_report_attachment(
        id,
        report_id,
        attachment_name,
        attachment_path,
        attachment_size,
        attachment_type
        ) VALUES (
        #{id},
        #{reportId},
        #{attachmentName},
        #{attachmentPath},
        #{attachmentSize},
        #{attachmentType}
        )
    </insert>

    <update id="addTrack">
        UPDATE ct_bbtc_report SET
        supplement_informant = #{supplementInformant},
        supplement_type = #{supplementType},
        transferGroup=#{transferGroup},
        transferArea=#{transferArea},
        transfer_department=#{transferDepartment},
        supplement_area = #{supplementArea},
        supplement_project = #{supplementProject},
        supplement_content = #{supplementContent},
        deal_person_name = #{dealPersonName},
        exchange_before_user = #{exchangeBeforeUser},
        exchange_after_user = #{exchangeAfterUser},
        exchange_type = #{exchangeType},
        report_ifClue = #{ifClue},
        deal_result = #{dealResult},
        update_by = #{updateBy.id},
        update_date = #{updateDate},
        supplement_group=#{supplementGroup},
        supplement_category=#{supplementCategory}
        <if test="transTime != null ">
            ,transTime=#{transTime}
        </if>
        WHERE id = #{id}
    </update>

    <update id="updateReportStatus">
        UPDATE ct_bbtc_report SET
        report_status = #{reportStatus}
        WHERE id = #{id}
    </update>


    <select id="findUser" parameterType="User" resultType="User">
        SELECT
        su.id AS "id",
        su.no AS "no",
        su.name AS "name",
        su.login_name AS "loginName",
        su.mobile AS "mobile",
        so1.name AS "company.name",
        so2.name AS "office.name",
        sp.position_name AS "position.name",
        sr.name AS "role.name",
        <if test="dbName == 'oracle'">NVL (sac. NAME, NULL) || '-' || NVL (sao. NAME, NULL) AS "devision"</if>
        <if test="dbName == 'mysql'">CONCAT(IFNULL(sac.name,""),"-",IFNULL(sao.name,"")) AS "devision"</if>
        FROM
        ct_bbtc_sys_user su
        LEFT JOIN ct_bbtc_sys_office so1 ON so1.id = su.company_id
        LEFT JOIN ct_bbtc_sys_office so2 ON so2.id = su.office_id
        LEFT JOIN ct_bbtc_sys_user2position sup ON sup.user_id = su.id
        LEFT JOIN ct_bbtc_sys_position sp ON sp.id = sup.position_id
        LEFT JOIN ct_bbtc_sys_user2role sur ON su.id = sur.user_id
        LEFT JOIN ct_bbtc_sys_role sr ON sr.id = sur.role_id
        LEFT JOIN ct_bbtc_sys_area sac ON sac.id = so1.area_id
        LEFT JOIN ct_bbtc_sys_area sao ON sao.id = so2.area_id
        <where>
            1 = 1 AND su.del_flag = 0
            <if test="no != null and no != ''">
                AND su.no LIKE
                <if test="dbName == 'oracle'">'%'||#{no}||'%'</if>
                <if test="dbName == 'mssql'">'%'+#{no}+'%'</if>
                <if test="dbName == 'mysql'">CONCAT('%', #{no}, '%')</if>
            </if>
            <if test="name != null and name != ''">
                AND su.name LIKE
                <if test="dbName == 'oracle'">'%'||#{name}||'%'</if>
                <if test="dbName == 'mssql'">'%'+#{name}+'%'</if>
                <if test="dbName == 'mysql'">CONCAT('%', #{name}, '%')</if>
            </if>
            <if test="mobile != null and mobile != ''">
                AND su.mobile LIKE
                <if test="dbName == 'oracle'">'%'||#{mobile}||'%'</if>
                <if test="dbName == 'mssql'">'%'+#{mobile}+'%'</if>
                <if test="dbName == 'mysql'">CONCAT('%', #{mobile}, '%')</if>
            </if>
            <if test="company != null and company.id != null and company.id != ''">
                AND so1.id = #{company.id}
            </if>
            <if test="office != null and office.id != null and office.id != ''">
                AND so2.id = #{office.id}
            </if>
            <if test="position != null and position.name != null and position.name != ''">
                AND sp.position_name = #{position.name}
            </if>
            <if test="loginName != null and loginName != ''">
                AND su.login_name LIKE
                <if test="dbName == 'oracle'">'%'||#{loginName}||'%'</if>
                <if test="dbName == 'mssql'">'%'+#{loginName}+'%'</if>
                <if test="dbName == 'mysql'">CONCAT('%', #{loginName}, '%')</if>
            </if>
        </where>
        ORDER BY su.update_date DESC
    </select>

    <select id="getOfficeList" parameterType="Office" resultType="Office">
        SELECT
        so.id AS "id",
        <if test="dbName == 'oracle'">so.name||'('||(SELECT s.name FROM ct_bbtc_sys_office s WHERE s.id =
            so.parent_id)||')' AS "name"
        </if>
        <if test="dbName == 'mysql'">CONCAT(so.name,'(',(SELECT s.name FROM ct_bbtc_sys_office s WHERE s.id =
            so.parent_id),')') AS "name"
        </if>
        FROM ct_bbtc_sys_office so
        WHERE so.type = '2'
    </select>

    <select id="getCompanyList" resultType="Office">
        SELECT
        so.id AS "id",
        so.name AS "name"
        FROM ct_bbtc_sys_office so
        WHERE so.type = '1'
    </select>

    <select id="getPositionList" resultType="Position">
        SELECT
        position_code AS "id",
        position_name AS "positionName"
        FROM ct_bbtc_sys_position
    </select>

    <insert id="addReportNotice">
        INSERT INTO ct_bbtc_report_notice(
        id,
        report_id,
        title,
        send_time,
        send_from_id,
        send_to_id,
        status,
        create_by,
        create_date,
        update_by,
        update_date
        ) VALUES (
        #{id},
        #{reportId},
        #{title},
        #{sendTime},
        #{sendFromId},
        #{sendToId},
        #{status},
        #{createBy},
        #{createDate},
        #{updateBy},
        #{updateDate}
        )
    </insert>

    <update id="updateReportNotice">
        UPDATE ct_bbtc_report_notice SET
        id = #{id},
        report_id = #{reportId},
        title = #{title},
        send_time = #{sendTime},
        send_from_id = #{sendFromId},
        send_to_id = #{sendToId},
        status = #{status},
        create_by = #{createBy},
        create_date = #{createDate},
        update_by = #{updateBy},
        update_date = #{updateDate}
        WHERE report_id = #{reportId}
    </update>

    <select id="findReportNotice" resultType="com.ejweb.modules.workbench.entity.ReportNotice">
        SELECT * FROM ct_bbtc_report_notice rn
        LEFT JOIN ct_bbtc_report r ON rn.report_id = r.id
        WHERE rn.report_id = #{id}
        AND r.exchange_type = #{exchangeType}
    </select>

    <select id="findAdmin" parameterType="Office" resultType="String">
        SELECT
        su.id
        FROM
        ct_bbtc_sys_user su
        LEFT JOIN ct_bbtc_sys_user2role sur ON su.id = sur.user_id
        LEFT JOIN ct_bbtc_sys_role sr ON sr.id = sur.role_id
        WHERE
        sr.name = '系统管理员'
        AND SU.DEL_FLAG = '0'
    </select>

    <insert id="addRecord">
        INSERT INTO ct_bbtc_report_exchange_his(
        id,
        report_id,
        old_user_id,
        new_user_id,
        exchange_type,
        exchange_time
        ) VALUES (
        #{id},
        #{reportId},
        #{oldUserId},
        #{newUserId},
        #{exchangeType},
        #{exchangeTime}
        )
    </insert>

    <select id="getReportStatus" parameterType="com.ejweb.modules.workbench.Bean.WorkbenchBean"
            resultType="java.util.HashMap">
        select REPORT_STATUS,COUNT(REPORT_STATUS)
        FROM ct_bbtc_report r
      /*  LEFT JOIN ct_bbtc_region CR
        ON R.supplement_group = CR.supplement_group*/

        WHERE 1=1
        <if test="isAdmin != null">
            AND exchange_after_user = #{isAdmin}
        </if>
        <if test="startDate != null">
            <if test="dbName == 'oracle'">AND create_date >= to_date(#{startDate}, 'yyyy-MM-dd')</if>
            <if test="dbName == 'mysql'">AND DATE(create_date) >= #{startDate}</if>
        </if>
        <if test="endDate != null">
            <if test="dbName == 'oracle'">AND create_date &lt;= to_date( #{endDate}|| ' 23:59:59', 'yyyy-MM-dd
                hh24:mi:ss')
            </if>
            <if test="dbName == 'mysql'">AND DATE(create_date) &lt;= #{endDate}</if>
        </if>
        <if test="supplementGroup != null and supplementGroup != ''">
            AND r.supplement_group = #{supplementGroup}
        </if>

        <if test="area != null and area !=''">
            AND r.supplement_area=#{area}
        </if>
        GROUP BY report_status
    </select>

    <select id="getReportResult" parameterType="com.ejweb.modules.workbench.Bean.WorkbenchBean"
            resultType="java.util.HashMap">
        select DEAL_RESULT,COUNT(DEAL_RESULT)
        FROM ct_bbtc_report r
      /*  LEFT JOIN ct_bbtc_region CR
        ON R.supplement_group = CR.supplement_group*/
        WHERE 1 = 1
        <if test="isAdmin != null">
            AND exchange_after_user = #{isAdmin}
        </if>
        <if test="startDate != null">
            <if test="dbName == 'oracle'">AND create_date >= to_date(#{startDate}, 'yyyy-MM-dd')</if>
            <if test="dbName == 'mysql'">AND DATE(create_date) >= #{startDate}</if>
        </if>
        <if test="endDate != null">
            <if test="dbName == 'oracle'">AND create_date &lt;= to_date( #{endDate}|| ' 23:59:59', 'yyyy-MM-dd
                hh24:mi:ss')
            </if>
            <if test="dbName == 'mysql'">AND DATE(create_date) &lt;= #{endDate}</if>
        </if>
        <if test="supplementGroup != null and supplementGroup != ''">
            AND r.supplement_group = #{supplementGroup}
        </if>

        <if test="area != null and area !=''">
            AND r.supplement_area=#{area}
        </if>
        GROUP BY deal_result
    </select>

    <select id="getTotal" parameterType="com.ejweb.modules.workbench.Bean.WorkbenchBean" resultType="java.util.HashMap">
        select report_source,COUNT(report_source)
        FROM ct_bbtc_report r
       /* LEFT JOIN ct_bbtc_region CR
        ON R.supplement_group = CR.supplement_group*/
        WHERE 1=1
        <if test="isAdmin != null">
            AND exchange_after_user = #{isAdmin}
        </if>
        <if test="startDate != null">
            <if test="dbName == 'oracle'">AND create_date >= to_date(#{startDate}, 'yyyy-MM-dd')</if>
            <if test="dbName == 'mysql'">AND DATE(create_date) >= #{startDate}</if>
        </if>
        <if test="endDate != null">
            <if test="dbName == 'oracle'">AND create_date &lt;= to_date( #{endDate}|| ' 23:59:59', 'yyyy-MM-dd
                hh24:mi:ss')
            </if>
            <if test="dbName == 'mysql'">AND DATE(create_date) &lt;= #{endDate}</if>
        </if>
        <if test="supplementGroup != null and supplementGroup != ''">
            AND r.supplement_group = #{supplementGroup}
        </if>

        <if test="area != null and area !=''">
            AND r.supplement_area=#{area}
        </if>
        GROUP BY report_source
    </select>

    <select id="getReportType" parameterType="com.ejweb.modules.workbench.Bean.WorkbenchBean"
            resultType="java.util.HashMap">
        SELECT
        SUM(CASE WHEN supplement_type||',' LIKE '1,%' OR supplement_type||',' LIKE '%,1,%' THEN 1 ELSE 0 END) AS
        "sale",
        SUM(CASE WHEN supplement_type||',' LIKE '2,%' OR supplement_type||',' LIKE '%,2,%' THEN 1 ELSE 0 END) AS
        "project",
        SUM(CASE WHEN supplement_type||',' LIKE '3,%' OR supplement_type||',' LIKE '%,3,%' THEN 1 ELSE 0 END) AS
        "cost",
        SUM(CASE WHEN supplement_type||',' LIKE '4,%' OR supplement_type||',' LIKE '%,4,%' THEN 1 ELSE 0 END) AS
        "forMining",
        SUM(CASE WHEN supplement_type||',' LIKE '5,%' OR supplement_type||',' LIKE '%,5,%' THEN 1 ELSE 0 END) AS
        "finance",
        SUM(CASE WHEN supplement_type||',' LIKE '6,%' OR supplement_type||',' LIKE '%,6,%' THEN 1 ELSE 0 END) AS
        "forensic",
        SUM(CASE WHEN supplement_type||',' LIKE '7,%' OR supplement_type||',' LIKE '%,7,%' THEN 1 ELSE 0 END) AS
        "administration",
        SUM(CASE WHEN supplement_type||',' LIKE '8,%' OR supplement_type||',' LIKE '%,8,%' THEN 1 ELSE 0 END) AS
        "manpower",
        SUM(CASE WHEN supplement_type||',' LIKE '9,%' OR supplement_type||',' LIKE '%,9,%' THEN 1 ELSE 0 END) AS
        "development",
        SUM(CASE WHEN supplement_type||',' LIKE '10,%' OR supplement_type||',' LIKE '%,10,%' THEN 1 ELSE 0 END) AS
        "rAndD",
        SUM(CASE WHEN supplement_type||',' LIKE '11,%' OR supplement_type||',' LIKE '%,11,%' THEN 1 ELSE 0 END) AS
        "iAndD",
        SUM(CASE WHEN supplement_type||',' LIKE '12,%' OR supplement_type||',' LIKE '%,12,%' THEN 1 ELSE 0 END) AS
        "Other",
        SUM(CASE WHEN supplement_type||',' LIKE '13,%' OR supplement_type||',' LIKE '%,13,%' THEN 1 ELSE 0 END) AS
        "propertyManagement",
        SUM(CASE WHEN supplement_type||',' LIKE '14,%' OR supplement_type||',' LIKE '%,14,%' THEN 1 ELSE 0 END) AS
        "hotelManagement",
        SUM(CASE WHEN supplement_type||',' LIKE '15,%' OR supplement_type||',' LIKE '%,15,%' THEN 1 ELSE 0 END) AS
        "businessManagement"
        FROM ct_bbtc_report r
      /*  LEFT JOIN ct_bbtc_region CR
        ON R.supplement_group = CR.supplement_group*/
        WHERE 1=1
        <if test="isAdmin != null">
            AND exchange_after_user = #{isAdmin}
        </if>
        <if test="startDate != null">
            <if test="dbName == 'oracle'">AND create_date >= to_date(#{startDate}, 'yyyy-MM-dd')</if>
            <if test="dbName == 'mysql'">AND DATE(create_date) >= #{startDate}</if>
        </if>
        <if test="endDate != null">
            <if test="dbName == 'oracle'">AND create_date &lt;= to_date( #{endDate}|| ' 23:59:59', 'yyyy-MM-dd
                hh24:mi:ss')
            </if>
            <if test="dbName == 'mysql'">AND DATE(create_date) &lt;= #{endDate}</if>
        </if>
        <if test="supplementGroup != null and supplementGroup != ''">
            AND r.supplement_group = #{supplementGroup}
        </if>

        <if test="area != null and area !=''">
            AND r.supplement_area=#{area}
        </if>
    </select>

    <select id="getRepList" parameterType="com.ejweb.modules.workbench.Bean.WorkbenchBean" resultType="com.ejweb.modules.report.entity.ReportEntity">
        SELECT * FROM(
        SELECT
        r.id AS "id",
        r.report_project AS "reportProject",
        r.report_city AS "reportCity",
        r.report_time AS "reportTime",
        r.report_content AS "reportContent",
        r.report_person_name AS "reportPersonName",
        r.report_person_tel AS "reportPersonTel",
        r.report_person_email AS "reportPersonEmail",
        r.report_source AS "reportSource",
        r.report_status AS "reportStatus",
        r.supplement_company AS "supplementCompany",
        r.supplement_department AS "supplementDepartment",
        r.supplement_informant AS "supplementInformant",
        r.supplement_title AS "supplementTitle",
        r.supplement_type AS "supplementType",
        r.supplement_category AS "supplementCategory",
        r.supplement_project AS "supplementProject",
        r.supplement_content AS "supplementContent",
        r.deal_person_name AS "dealPersonName",
        r.deal_result AS "dealResult",
        r.exchange_before_user AS "exchangeBeforeUser",
        r.exchange_after_user AS "exchangeAfterUser",
        r.exchange_type AS "exchangeType",
        r.create_by AS "createBy",
        r.create_date AS "createDate",
        r.update_by AS "updateBy",
        r.update_date AS "updateDate",
        cr.name AS "supplementArea",
        b.label AS "supplementGroup",
        b2.label AS "transferGroup",
        cr2.name AS "transferArea"

        FROM ct_bbtc_report r
        LEFT JOIN ct_bbtc_region cr ON r.supplement_area = cr.id
        LEFT JOIN ct_bbtc_region cr2 ON r.transferArea = cr2.id
        LEFT JOIN ct_bbtc_sys_dict b on b.id = r.supplement_group
        LEFT JOIN ct_bbtc_sys_dict b2 on b2.id = r.transferGroup

        <where>
         r.report_status = #{processStatus}
        <if test="isAdmin != null">
            AND r.exchange_after_user = #{isAdmin}
        </if>
        <if test="startDate != null">
            <if test="dbName == 'oracle'">AND r.create_date >= to_date(#{startDate}, 'yyyy-MM-dd')</if>
            <if test="dbName == 'mysql'">AND DATE(r.create_date) >= #{startDate}</if>
        </if>
        <if test="endDate != null">
            <if test="dbName == 'oracle'">AND r.create_date &lt;= to_date( #{endDate}|| ' 23:59:59', 'yyyy-MM-dd
                hh24:mi:ss')
            </if>
            <if test="dbName == 'mysql'">AND DATE(r.create_date) &lt;= #{endDate}</if>
        </if>

         <if test="supplementGroup != null and supplementGroup != ''">
            AND r.supplement_group = #{supplementGroup}
         </if>

        <if test="area != null and area !=''">
            AND r.supplement_area=#{area}
        </if>

        </where>

        order by r.create_date desc
        )  w
        <if test="dbName == 'oracle'">WHERE   rownum &lt;= 10</if>
        <if test="dbName == 'mysql'">   limit 0,10</if>
    </select>

    <select id="getChooseReportList" parameterType="com.ejweb.modules.report.entity.ReportEntity"
            resultType="com.ejweb.modules.report.entity.ReportEntity">
        SELECT
        r.id AS "id",
        r.report_project AS "reportProject",
        r.report_time AS "reportTime",
        r.report_person_name AS "reportPersonName",
        r.report_person_tel AS "reportPersonTel",
        r.report_status AS "reportStatus",
        r.supplement_title AS "supplementTitle",
        r.supplement_type AS "supplementType",
        b.label AS "supplementGroup",
        b2.label AS "transferGroup",
        r.supplement_category AS "supplementCategory",
        r.supplement_department AS "supplementDepartment",
        r.supplement_informant AS "supplementInformant",
        r.deal_person_name AS "dealPersonName",
        r.deal_result AS "dealResult",
        r.exchange_type AS "exchangeType",
        su.name AS "transferName"
        ,cr.name AS "supplementArea"
        ,cr2.name AS "transferArea"


        FROM ct_bbtc_report r
        LEFT JOIN ct_bbtc_sys_user su ON r.exchange_after_user = su.id
        LEFT JOIN ct_bbtc_region CR ON r.supplement_area = CR.id
        LEFT JOIN  ct_bbtc_bus_type CT ON r.transfer_department = CT.id
        LEFT JOIN ct_bbtc_sys_dict b on b.id = r.supplement_group
        LEFT JOIN ct_bbtc_region cr2 ON r.transferArea = cr2.id
        LEFT JOIN ct_bbtc_sys_dict b2 on b2.id = r.transferGroup
        <where>
            1 = 1
            <if test="exchangeAfterUser != null and exchangeAfterUser != ''">
                AND r.exchange_after_user = #{exchangeAfterUser}
            </if>
          <!--  <if test="dealPersonName != null and dealPersonName != ''">
                AND r.deal_person_name LIKE
                '%${dealPersonName}%'
                /* CONCAT('%', #{dealPersonName}, '%')*/
                '%'#{dealPersonName}'%'
            </if>-->
            <if test="dealPersonName != null and dealPersonName != ''">
                AND r.deal_person_name LIKE
                <if test="dbName == 'oracle'">'%'||#{dealPersonName}||'%'</if>
                <if test="dbName == 'mssql'">'%'+#{dealPersonName}+'%'</if>
                <if test="dbName == 'mysql'">CONCAT('%', #{dealPersonName}, '%')</if>
            </if>
        </where>
        ORDER BY r.update_date DESC
    </select>

    <update id="updateExchangeUser">
        UPDATE ct_bbtc_report SET
        exchange_after_user = #{exchangeAfterUser},
        exchange_type = #{exchangeType},
        update_by = #{updateBy.id},
        update_date = #{updateDate}
        WHERE id = #{id}
    </update>

    <select id="findExchangeUser" resultType="String">
        SELECT c.EXCHANGE_AFTER_USER FROM CT_BBTC_REPORT c WHERE c.ID = #{id}
    </select>

    <update id="updateIsSendEmail">
        UPDATE ct_bbtc_report SET
        is_send_email = '1'
        WHERE id = #{reportId}
    </update>

    <update id="updateConclusion">
        UPDATE ct_bbtc_report SET
        report_status= '3',
        deal_result = #{dealResult},
        <if test="transferDepartment =='6'">
            transfer_department = #{transferDepartment}
        </if>
        <if test="transferDepartment !='6'">
            transfer_department = ''
        </if>
        WHERE id = #{id}
    </update>

    <update id="againUpdateConclusion">
        UPDATE ct_bbtc_report SET
        report_status= '1'
        WHERE id = #{id}
    </update>

    <update id="cluesUpdateConclusion">
        UPDATE ct_bbtc_report SET
        report_status= '4'
        WHERE id = #{id}
    </update>

    <select id="getTotalNum" parameterType="com.ejweb.modules.workbench.Bean.WorkbenchBean" resultType="Integer">
        SELECT count(*) AS "totalCount"
        FROM ct_bbtc_report  r
        WHERE 1 = 1
        <if test="isAdmin != null">
            AND exchange_after_user = #{isAdmin}
        </if>
        <if test="startDate != null">
            <if test="dbName == 'oracle'">AND create_date >= to_date(#{startDate}, 'yyyy-MM-dd')</if>
            <if test="dbName == 'mysql'">AND DATE(create_date) >= #{startDate}</if>
        </if>
        <if test="endDate != null">
            <if test="dbName == 'oracle'">AND create_date &lt;= to_date( #{endDate}|| ' 23:59:59', 'yyyy-MM-dd
                hh24:mi:ss')
            </if>
            <if test="dbName == 'mysql'">AND DATE(create_date) &lt;= #{endDate}</if>
        </if>
        <if test="supplementGroup != null and supplementGroup != ''">
            AND r.supplement_group = #{supplementGroup}
        </if>

        <if test="area != null and area !=''">
            AND r.supplement_area=#{area}
        </if>
    </select>

    <delete id="deleteConclusion">
        delete from ct_bbtc_report
        where id = #{id}
    </delete>

    <delete id="deleteNotice">
        delete from CT_BBTC_REPORT_NOTICE
        where report_id = #{id}
    </delete>


    <select id="selectExchangeAfterUser" parameterType="ReportEntity" resultType="String">
        SELECT
        su.name
        FROM
        ct_bbtc_sys_user su
        WHERE
        su.id=#{report}
    </select>


    <select id="findAllUserList" resultType="User">
        SELECT id AS ID,
        id AS value,
        name AS label
        from ct_bbtc_sys_user
        where del_flag = 0
    </select>

  <update id="updateReportByClueTime"  parameterType="com.ejweb.modules.report.entity.ReportEntity"  >
       UPDATE ct_bbtc_report SET
        report_status= '3',
        deal_result='2'
        where
       report_status != '3' and
       deal_result !='2'
      <if test="dbName == 'oracle'"> and  TRANSTIME >= to_date(#{cuurentTime}, 'yyyy-MM-dd')  AND TRANSTIME &lt;= to_date( #{cuurentTime}|| ' 23:59:59', 'yyyy-MM-dd
          hh24:mi:ss')
      </if>
      <if test="dbName == 'mysql'"> and DATE(TRANSTIME) >= #{cuurentTime}  AND DATE(TRANSTIME) &lt;= #{cuurentTime}</if>
  </update>

</mapper>