<resultMap type="com.dny.website.entity.AllianceBusiness" id="ABMap">
<id column="aid" property="alliance_id"/>
<result column="name" property="name"/>
<result column="gender" property="gender"/>
<result column="telephone" property="telephone"/>
<result column="wechat" property="wechat"/>
<result column=" province" property=" province"/>
<result column="city" property="city"/>
<result column="area" property="area"/>
<result column="detailed_address" property="detailed_address"/>
<result column="store_area" property="store_area"/>
<result column=" product" property=" product"/>
<result column="come_round" property="come_round"/>
<result column="come_round_time" property="come_round_time"/>
<result column="come_round_number" property="come_round_number"/>
<result column="remark" property="remark"/>
<result column="star" property="star"/>
<result column="signing" property="signing"/>
<result column="earnest" property="earnest"/>
<result column="earnest_time" property="earnest_time"/>
<result column="aggregate_investment" property="aggregate_investment"/>
<result column=" payment_remark" property=" payment_remark"/>
<result column="client_principal" property="client_principal"/>
<result column="treaters" property="treaters"/>
<result column="source" property="source"/>
<result column="summary" property="summary"/>
<result column="mimTime" property="mimTime"/>
<result column="maxTime" property="maxTime"/>
<!-- 谈判内容 一对多 -->
<collection property="ABCList" ofType="com.dny.website.entity.AllianceBusinessCommunicate" column="aid" select="selectAllABC">
<result column="communicate_time" property="communicate_time"/>
<result column="content" property="content"/>
</collection>
<!-- 回款金额 一对多 -->
<collection property="ABRMList" ofType="com.dny.website.entity.AllianceBusinessReturnedMoney"
column="{aid=aid,minTime=minTime,maxTime=maxTime}" select="selectAllABRM">
<result column="degree" property="degree"/>
<result column="time" property="time"/>
<result column="degree_money" property="degree_money"/>
</collection>
</resultMap>
<!-- 查询所有加盟商信息 -->
<select id="selectAllAB" resultMap="ABMap">
SELECT
#{minTime} as minTime,
#{maxTime} as maxTime,
ab.alliance_id as aid,
ab.NAME,
ab.gender,
ab.telephone,
ab.wechat,
ab.province,
ab.city,
ab.area,
ab.detailed_address,
ab.store_area,
ab.product,
ab.come_round,
ab.come_round_time,
ab.come_round_number,
ab.remark,
ab.star,
ab.signing,
ab.earnest,
ab.earnest_time,
ab.aggregate_investment,
ab.payment_remark,
ab.client_principal,
ab.treaters,
ab.source,
(case when aa.sum_money is null then ab.earnest else aa.sum_money+ab.earnest end) as summary
FROM
alliance_business AS ab left join (
SELECT
allianceId,
sum( degree_money ) as sum_money
FROM
`alliance_business_returned_money`
GROUP BY
allianceId
) as aa on 1 and ab.alliance_id=aa.allianceId
<where>
ab.client_principal is not null
and ab.client_principal != ''
<if test="come_round !=null">
and ab.come_round=#{come_round}
</if>
<if test="star !=null">
and ab.star=#{star}
</if>
<if test="signing !=null">
and ab.signing=#{signing}
</if>
<if test="client_principal !=null and client_principal!=''">
and ab.client_principal=#{client_principal}
</if>
<if test="treaters !=null and treaters !=''">
and ab.treaters like CONCAT("%",#{treaters},"%")
</if>
<if test="startTime !=null and startTime !=''">
and ab.earnest_time>=CONCAT('','${startTime}',' 00:00:00')
</if>
<if test="endTime !=null and endTime!=''">
and ab.earnest_time <= CONCAT('','${endTime}',' 23:59:59')
</if>
<if test="minTime !=null and minTime!=''">
and ab.come_round_time>=CONCAT('','${minTime}',' 00:00:00')
</if>
<if test="maxTime !=null and maxTime!=''">
and ab.come_round_time <= CONCAT('','${maxTime}',' 23:59:59')
</if>
<if test="source !=null and source!=''">
and ab.source=#{source}
</if>
<if test="like !=null and like!=''">
and concat(IFNULL(ab.name,''),IFNULL(ab.telephone,''),IFNULL(ab.wechat,''),IFNULL(ab.province,''),IFNULL(ab.city,''),IFNULL(ab.area,'')) like concat("%",#{like},"%")
</if>
</where>
ORDER BY
ab.star DESC,
ab.input_time DESC
</select>
<!-- 查询谈判内容 -->
<select id="selectAllABC" resultType="com.dny.website.entity.AllianceBusinessCommunicate">
SELECT
abc.id,
abc.communicate_time,
abc.content
FROM
alliance_business_communicate AS abc
<where>
abc.allianceId=#{aid}
</where>
ORDER BY
abc.communicate_time desc
</select>
<!-- 查询回款情况 -->
<select id="selectAllABRM" resultType="com.dny.website.entity.AllianceBusinessReturnedMoney">
SELECT
abrm.returned_money_id,
abrm.degree,
abrm.time,
abrm.degree_money
FROM
alliance_business_returned_money AS abrm
<where>
abrm.allianceId=#{aid}
<if test="minTime !=null and minTime!=''">
and abrm.time>=CONCAT('',#{minTime,jdbcType=VARCHAR},' 00:00:00')
</if>
<if test="maxTime !=null and maxTime!=''">
and abrm.time <= CONCAT('',#{maxTime,jdbcType=VARCHAR},' 23:59:59')
</if>
</where>
ORDER BY
abrm.time desc
</select>
一对多查询子查询条件 非主查询公有
最新推荐文章于 2024-04-03 07:05:48 发布