<?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.elephant.framework.galaxy.aroad.module.info.mapper.InfoInformationsMapper">

  <resultMap id="infoInformationsMap" type="com.elephant.framework.galaxy.aroad.module.info.entity.InfoInformations">
        <id property="infoId" column="info_id"/>
        <result property="templateId" column="template_id"/>
        <result property="infoTitle" column="info_title"/>
        <result property="infoContent" column="info_content"/>
        <result property="infoRemark" column="info_remark"/>
        <result property="reporterId" column="reporter_id"/>
        <result property="reportingTime" column="reporting_time"/>
  </resultMap>

    <resultMap id="infoMap" type="com.elephant.framework.galaxy.aroad.module.info.dto.InfoDto">
        <id property="infoId" column="info_id"/>
        <result property="templateId" column="template_id"/>
        <result property="infoTitle" column="info_title"/>
        <result property="infoContent" column="info_content"/>
        <result property="infoRemark" column="info_remark"/>
        <result property="reporterId" column="reporter_id"/>
        <result property="reporter" column="reporter"/>
        <result property="reportingTime" column="reporting_time"/>
        <result property="templateName" column="template_name"/>
        <result property="templateType" column="template_type"/>
        <result property="receiverId" column="receiver_id"/>
        <result property="receiverName" column="receiver_name"/>
    </resultMap>


  <insert id="saveInformations" useGeneratedKeys="true" keyProperty="infoId">
    insert into
      info_informations(template_id, info_title,info_content,info_remark,reporter_id,reporter,reporting_time)
    values
      (#{templateId}, #{infoTitle},#{infoContent},#{infoRemark},#{reporterId},#{reporter},#{reportingTime})
  </insert>

  <!--报给我的信息-->
  <select id="getReportedToMe" resultMap="infoMap">
      select
        i.info_id,i.template_id,i.info_title,i.info_content,i.info_remark,i.reporter_id,i.reporter,i.reporting_time,
        t.template_name,t.template_type,
        r.receiver_id,r.receiver_name
      from info_templates t
      right join
        info_informations i
      on t.template_id = i.template_id
      inner join
        info_receivers r
      on i.info_id = r.info_id
      where r.receiver_id = #{receiverId}
      order by i.reporting_time desc
  </select>

  <!--我上报的信息-->
  <select id="getIReported" resultMap="infoMap">
    SELECT
	i.template_id,
	i.info_title,
	i.info_content,
	i.info_remark,
	i.reporter_id,
	i.reporter,
	i.reporting_time,
	T.template_name,
	T.template_type,
	r.info_id,
	r.receiver_name
	from
	info_templates T RIGHT JOIN info_informations i	ON T.template_id = i.template_id
	INNER join
	(SELECT
	i.info_id,
	string_agg(r.receiver_name, ',') AS receiver_name
FROM
	info_templates
	T RIGHT JOIN info_informations i ON T.template_id = i.template_id
	INNER JOIN info_receivers r ON i.info_id = r.info_id
WHERE
	i.reporter_id = #{reporterId}
GROUP BY
	i.info_id
ORDER BY
	i.reporting_time DESC) r ON i.info_id = r.info_id
  </select>

<!--    &#45;&#45;       select-->
<!--    &#45;&#45;         i.info_id,i.template_id,i.info_title,i.info_content,i.info_remark,i.reporter_id,i.reporter,i.reporting_time,-->
<!--    &#45;&#45;         t.template_name,t.template_type,-->
<!--    &#45;&#45;         r.receiver_id,group_concat(r.receiver_name) as receiver_name-->
<!--    &#45;&#45;       from info_templates t-->
<!--    &#45;&#45;       right join-->
<!--    &#45;&#45;         info_informations i-->
<!--    &#45;&#45;       on t.template_id = i.template_id-->
<!--    &#45;&#45;       inner join-->
<!--    &#45;&#45;         info_receivers r-->
<!--    &#45;&#45;       on i.info_id = r.info_id-->
<!--    &#45;&#45;       where i.reporter_id = #{reporterId}-->
<!--    &#45;&#45;       group by i.info_id-->
<!--    &#45;&#45;       order by i.reporting_time desc-->
</mapper>
