<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//oos.juyouhx.com//DTD Mapper 3.0//EN" "http://oss.juyouhx.com/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.scpyun.platform.standard.jilinsscgsdp.notice">
   
    <insert id="insertData" parameterType="map">
       insert into jl_app_notice
       (id, title, department, context,
        category,sort, del_flag,
       create_by, create_time, update_by, update_time,department_json)
       values
       (
       #{uuid},
       #{title},
       #{department},
       #{context},
        #{category},
       #{sort},
       0,
       #{_user.id},
       now(),
       #{_user.id},
       now(),
        #{json})
   </insert>

    <insert id="insertNoticeAttach" parameterType="map">
        INSERT INTO jl_app_notice_attachment(
            id,
            notice_id,
            url,
            attach_uuid,
            attach_suffix,
            create_time,
            original_name
        )VALUES(
            UUID(),
            #{notice_id},
            #{url},
            #{uuid},
            #{suffix},
            NOW(),
            #{originalFilename}
        )
    </insert>


    <insert id="insertNoticeArea" parameterType="map">
        INSERT INTO `jl_notice_office` (
        `name`,
        `parent_ids`,
        `notice_id`,
        `office_id`,
        `area_id`,
        `level`,
        `is_all`,
        `is_value`,
        `del_flag`,
        `create_by`,
        `create_time`
        ) VALUES (
        #{name},
        #{parentIds},
        #{noticeId},
        #{officeId},
        #{areaId},
        #{type},
        #{isAll},
        #{isValue},
        0,
        #{userId},
        now()
        );
    </insert>

    <update id="clearNoticeAttach" parameterType="map">
        update jl_app_notice_attachment set del_flag='1' where notice_id=#{id}
    </update>
    <select id="getOffieObject" parameterType="string" resultType="map" >
        select
        name name,
        level type,
        area_id areaId,
        office_id officeId,
        is_all isAll,
        parent_ids parentIds,
        is_value isValue
        from jl_notice_office
        where notice_id =#{id}
        and
        del_flag = 0
    </select>
   <update id="updateData" parameterType="map">
       update jl_app_notice
       <set>
           update_by = #{_user.id},
           update_time = now(),
           publish_time = now(),
           <if test="title != null and title != ''">
               title = #{title},
           </if>
           <if test="department != null and department != ''">
               department = #{department},
           </if>
           <if test="context != null and context != ''">
               context = #{context},
           </if>
           <if test="sort != null and sort != ''">
               sort = #{sort},
           </if>
           <if test="publish_status != null and publish_status != ''">
               publish_status = #{publish_status},
           </if>
           <if test="category != null and category != ''">
               category = #{category},
           </if>
           <if test="json != null and json != ''">
               department_json = #{json}
           </if>
       </set>
       <where>
           id = #{id}
       </where>
    </update>

    <update id = 'deleteNoticeArea' parameterType="map">
        delete  from  jl_notice_office  where notice_id=#{id}
    </update>
    <update id="removeData" parameterType="map">
        update jl_app_notice
        <set>
            update_by = #{_user.id},
            update_time = now(),
            del_flag = 1
        </set>
        <where>
            id = #{id}
        </where>
    </update>
    
   	<select id="getObject" parameterType="map" resultType="map">
        select
        an.id id,
        an.title title,
        an.department department,
        an.context context,
        an.sort sort,
        an.category category,
        c.label label,
        an.department_json areaList,
        an.publish_status  publish_status,
        DATE_FORMAT(an.create_time,'%Y-%m-%d %T') create_time,
        an.sort sort
        from jl_app_notice an INNER JOIN sys_dict c on an.category=c.value
        <where>
            AND an.id = #{id}
            AND an.del_flag = 0
        </where>
    </select>
    <select id="selectOneListPage" parameterType="com.scpyun.base.bean.Page" resultType="map">
        select
        an.id id,
        an.title title,
        an.department department,
        an.context context,
        an.sort sort,
        c.label label,
        an.publish_status publish_status,
        DATE_FORMAT(an.create_time,'%Y-%m-%d %T') create_time ,
        an.del_flag del_flag,
        an.sort sort
        from jl_app_notice an
        INNER JOIN sys_dict c on an.category=c.value
        <where>
            c.type='notice_type' and c.parent_id='0'
            AND an.del_flag = 0
            <if test="params.title != null and params.title != ''">
                AND an.title like concat('%',#{params.title},'%')
            </if>
            <if test="params.context != null and params.context != ''">
                AND an.context like concat('%',#{params.context},'%')
            </if>
            <if test="params.department != null and params.department != ''">
                AND an.department = #{params.department}
            </if>
            <if test="params.publish_status != null and params.publish_status != ''">
                AND an.publish_status = #{params.publish_status}
            </if>
            <if test="params.category != null and params.category != ''">
                AND an.category = #{params.category}
            </if>
        </where>
        order by an.create_time desc
    </select>
    <select id="selectListPage" parameterType="com.scpyun.base.bean.Page" resultType="map">
        select
        distinct
        an.id id,
        an.title title,
        su.name department,
        an.context context,
        an.sort sort,
        c.label label,
        an.publish_status publish_status,
        DATE_FORMAT(an.publish_time, '%Y-%m-%d %T') create_time,
        an.del_flag del_flag,
        an.sort sort
        from
        jl_app_notice an
        left join jl_notice_office jo on an.id = jo.notice_id
        left join sys_user su on an.create_by = su.id
        INNER JOIN sys_dict c on an.category = c.value
        where
            c.type='notice_type' and c.parent_id='0'
            and
            jo.del_flag =0
            AND an.del_flag = 0
            and
            (
        jo.area_id = '220000000000'
            <if test="params.cityId != null and params.cityId != ''">
                or jo.area_id =#{params.cityId}
            </if>
            <if test="params.areaId != null and params.areaId != ''">
                or jo.area_id =#{params.areaId}
            </if>
            <if test="params.streetId != null and params.streetId != ''">
                or jo.area_id =#{params.streetId}
            </if>
            <if test="params.company_id != null and params.company_id != ''">
                or jo.area_id =#{params.company_id}
            </if>
            <if test="params._user.company_id != null and params._user_company_id != ''">
                or jo.office_id =#{params._user.company_id}
            </if>
        )
            <if test="params.title != null and params.title != ''">
                AND an.title like concat('%',#{params.title},'%')
            </if>
            <if test="params.context != null and params.context != ''">
                AND an.context like concat('%',#{params.context},'%')
            </if>

            <if test="params.department != null and params.department != ''">
                AND an.department = #{params.department}
            </if>
            <if test="params.publish_status != null and params.publish_status != ''">
                AND an.publish_status = #{params.publish_status}
            </if>
            <if test="params.category != null and params.category != ''">
                AND an.category = #{params.category}
            </if>
        order by an.publish_time desc
    </select>
    
    <select id="selectList" parameterType="map" resultType="map">
        select
        id,
        title,
        department,
        context,
        sort,
        publish_status,
        DATE_FORMAT(create_time,'%Y-%m-%d %T') create_time,
        sort
        from jl_app_notice 
        <where>
        	publish_status='1' AND del_flag = 0
            <if test="title != null and title != ''">
                AND title like concat('%',#{title},'%')
            </if>
            <if test="department != null and department != ''">
                AND department = #{department}
            </if>
            <if test="publish_status != null and publish_status != ''">
                AND publish_status = #{publish_status}
            </if>
        </where>
        order by sort desc,create_time desc
        limit 10
    </select>
</mapper>