<?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.unis.base.server.dao.DqyxxckDao" >

	<resultMap id="BaseResultMap" type="com.unis.base.common.vo.DqyxxckVO" >
        <result column="qyxz" property="qyxz" jdbcType="VARCHAR" />
        <result column="kddm" property="kddm" jdbcType="VARCHAR" />
        <result column="tyshxydm" property="tyshxydm" jdbcType="VARCHAR" />
        <result column="qymc" property="qymc" jdbcType="VARCHAR" />
		<result column="sslsxzglbm" property="sslsxzglbm" jdbcType="VARCHAR" />
		<result column="person_total" property="personTotal" jdbcType="INTEGER" />
		<result column="cf_num" property="cfNum" jdbcType="INTEGER" />
		<result column="hw_num" property="hwNum" jdbcType="INTEGER" />
		<result column="sjcr" property="sjcr" jdbcType="DECIMAL" />
    </resultMap>

    <sql id="whereIf">
   		<if test="qymc != null and qymc != ''">
			and qy.qymc like '%${qymc,jdbcType=VARCHAR}%'
		</if>
		<if test="sslsxzglbm != null and sslsxzglbm != ''">
			and qy.sslsxzglbm = #{sslsxzglbm}
		</if>
    </sql>
    <select id="getCfQyxxList" parameterType="com.unis.base.common.vo.DqyxxckVO" resultMap="BaseResultMap">
        select qy.tyshxydm, t4.kddm, qy.qymc, ifnull(t4.cf_num, 0) cf_num, ifnull(t4.hw_num, 0) hw_num, t4.sjcr
        from base.tz_slqy qy
        left join (
            select t1.tyshxydm, t1.kddm, count(t1.gfcfdm) cf_num, sum(t1.hwsl) hw_num,
            cast(sum(ifnull(t1.sjcr, 0)) as decimal(17, 3)) sjcr
            from (
                select t2.tyshxydm, t2.kddm, t2.gfcfdm, t2.sjcr, count(t2.cfbh) hwsl from base.d_cfxxsjy t2
                where t2.sfba = '5' and t2.gfcfdm is not null and t2.status = 1
                group by t2.tyshxydm, t2.kddm, t2.gfcfdm, t2.sjcr
            ) t1
            group by t1.tyshxydm, t1.kddm
        ) t4 on t4.tyshxydm = qy.tyshxydm
        left join usercenter.sys_orgainze t5 on qy.qyjgdm = t5.org_code
		<trim prefix="where" prefixOverrides="and|or">
			<include refid="whereIf"/>
            and qy.qyzyyw like '%8%' and t5.state_code = 1
		</trim>
        order by t5.order_by nulls first
    </select>

    <select id="getCfQyxxTotal" resultMap="BaseResultMap" parameterType="com.unis.base.common.vo.DqyxxckVO">
        select sum(cf_num)cf_num, sum(ifnull(hw_num, 0))hw_num, sum(ifnull(sjcr,0)) sjcr
        from (
            select t1.tyshxydm,  count(t1.gfcfdm) cf_num, sum(t1.hwsl) hw_num,
                   cast(sum(ifnull(t1.sjcr, 0)) as decimal(17, 3)) sjcr
            from (
                select t2.tyshxydm, t2.gfcfdm, t2.sjcr, count(t2.cfbh) hwsl from base.d_cfxxsjy t2
                left join base.tz_slqy qy on t2.tyshxydm = qy.tyshxydm
                where t2.status = 1 and if(t2.gfcfdm, null,-1, 1) = 1
                and t2.cfzt = 1 and t2.cfyt in (0, 1)
        <if test="qymc != null and qymc != ''">
            and qy.qymc like '%${qymc,jdbcType=VARCHAR}%'
        </if>
                group by t2.tyshxydm, t2.gfcfdm, t2.sjcr
            ) t1
            group by t1.tyshxydm
        ) x
    </select>

    <select id="getSbQyxxList" parameterType="com.unis.base.common.vo.DqyxxckVO" resultMap="BaseResultMap">
        select
            qy.qymc,
            qy.tyshxydm,
            qy.qyxz,sb.kddm
        from
            d_sbxxsjy sb
            join d_kdxxsjy kd on kd.kddm = sb.kddm
            join tz_slqy qy on kd.tyshxydm = qy.tyshxydm
            <where>
                and sb.status = 1
                <include refid="whereIf"/>
            </where>
        group by
		qy.qymc,
		qy.tyshxydm,
		qy.qyxz,sb.kddm
    </select>

    <select id="getYpQyxxList" parameterType="com.unis.base.common.vo.DqyxxckVO" resultMap="BaseResultMap">
	select
		qy.qymc,
		qy.tyshxydm,
		qy.qyxz,yp.sskmbh
	from
		d_ypxx yp
		join d_kdxxsjy kd on kd.kddm = yp.sskmbh
		join tz_slqy qy on kd.tyshxydm = qy.tyshxydm
		<where>
			and yp.status = 1
			<include refid="whereIf"/>
		</where>
	group by
		qy.qymc,
		qy.tyshxydm,
		qy.qyxz,yp.sskmbh
    </select>

    <select id="getRyQyxxList" parameterType="com.unis.base.common.vo.DqyxxckVO" resultMap="BaseResultMap">
        select
        qy.qymc,
        qy.tyshxydm,
        qy.qyxz,
        ry.kddm,
        count(t1.rybh) person_total
        from d_ryxxsjy ry
        inner join tz_slqy qy on ry.tyshxydm = qy.tyshxydm
        left join d_ryxxsjy t1 on t1.rybh = ry.rybh and t1.qyzt =1
        <where>
            and ry.status = 1
            <include refid="whereIf"/>
        </where>
        group by qy.qymc, qy.tyshxydm, qy.qyxz, ry.kddm
    </select>

    <select id="getRyTotal" parameterType="com.unis.base.common.vo.DqyxxckVO" resultType="java.lang.Integer">
        select count(1) from d_ryxxsjy ry
        inner join tz_slqy qy on ry.tyshxydm = qy.tyshxydm
        <where>
            and ry.status = 1 and ry.qyzt =1
            <include refid="whereIf"/>
        </where>
    </select>

	<select id="getYgQyxxList" parameterType="com.unis.base.common.vo.DqyxxckVO" resultMap="BaseResultMap">
		select
		qy.qymc,
		qy.tyshxydm,
		qy.qyxz,yg.kddm
		from
		d_ygxxsjy yg
		join tz_slqy qy on left(yg.kddm,18) = qy.tyshxydm
		<where>
			and yg.status = 1
			<include refid="whereIf"/>
		</where>
		group by
		qy.qymc,
		qy.tyshxydm,
		qy.qyxz,yg.kddm
	</select>
</mapper>
