<?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.PersonMeetingDao">

    <!-- <insert id="insertOne">
         insert into base:d_meeting (meeting_name, type, start_date, end_date, meeting_place, recipient, project_date,
                                     project_time, project_content, file)
         values (#{meetingName}, #{type}, #{startDate}, #{endDate}, #{meetingPlace}, #{recipient}, #{projectDate},
             #{projectTime}, #{projectContent}, #{file})
     </insert>-->
    <insert id="insertBatch">
        <foreach collection="list" item="obj" separator=";">
            INSERT INTO base.d_train (meeting_name, annual,release_date,publisher,persons,status,fid )
            VALUES
            (
            #{obj.meetingName},
            #{obj.annual},
            #{obj.releaseDate},
            #{obj.publisher},
            #{obj.persons},
            #{obj.status},
            #{obj.fid}
            )
        </foreach>

    </insert>
    <insert id="insertRecord">
        <foreach collection="list" item="obj" separator=";">
            INSERT INTO base.sp_train_feedback (fid,
            main_fid,train_person_id,feedback_content,feedback_status,train_person_name
            ,train_person_dept_code,train_person_dept_name,train_person_org_code,train_person_org_name,create_date,train_name,creator_code,creator,release_status
            )
            VALUES
            (
            #{obj.fid},
            #{obj.mainFid},
            #{obj.trainPersonId},
            #{obj.feedbackContent},
            #{obj.feedbackStatus},
            #{obj.trainPersonName},
            #{obj.trainPersonDeptCode},
            #{obj.trainPersonDeptName},
            #{obj.trainPersonOrgCode},
            #{obj.trainPersonOrgName},
            #{obj.createDate},
            #{obj.trainName},
            #{obj.creatorCode},
            #{obj.creator},
            #{obj.releaseStatus}
            )
        </foreach>

    </insert>
    <insert id="insertPn">
        <foreach collection="list" item="obj" separator=";">
            INSERT INTO base.sp_train_feedback (post,notes )
            VALUES
            (
            #{obj.post},
            #{obj.notes}
            )
        </foreach>
    </insert>
    <insert id="insertPersonRecord">
        <foreach collection="list" item="obj" separator=";">
            INSERT INTO base.d_train_content (train_person_name,feedback_status,feedback_content,creator)
            VALUES
            (
            #{obj.fid},
            #{obj.trainPersonName},
            #{obj.feedbackStatus},
            #{obj.feedbackContent},
            #{obj.userId}
            )
        </foreach>
    </insert>
    <insert id="insertIn">
        <foreach collection="list" item="obj" separator=";">
            INSERT INTO base.d_train_content
            (main_fid,train_person_id,train_person_name,train_person_dept_code,train_person_dept_name,post,notes,train_name,feedback_status,creator)
            VALUES
            (
            #{obj.mainFid},
            #{obj.trainPersonId},
            #{obj.trainPersonName},
            #{obj.trainPersonDeptCode},
            #{obj.trainPersonDeptName},
            #{obj.post},
            #{obj.notes},
            #{obj.trainName},
            #{obj.feedbackStatus},
            #{obj.creator}
            )
        </foreach>

    </insert>
    <insert id="insertFed">
        <foreach collection="list" item="obj" separator=";">
            INSERT INTO base.sp_train_feedback
            (main_fid,train_person_id,train_person_name,train_person_dept_code,train_person_dept_name,post,notes,train_name,feedback_status,creator)
            VALUES
            (
            #{obj.mainFid},
            #{obj.trainPersonId},
            #{obj.trainPersonName},
            #{obj.trainPersonDeptCode},
            #{obj.trainPersonDeptName},
            #{obj.post},
            #{obj.notes},
            #{obj.trainName},
            #{obj.feedbackStatus},
            #{obj.creator}
            )
        </foreach>
    </insert>
    <insert id="insertFin">
        <foreach collection="list" item="obj" separator=";">
            INSERT INTO base.sp_train_feedback
            (main_fid,train_person_id,train_person_name,train_person_dept_code,train_person_dept_name,post,notes,train_name)
            VALUES
            (
            #{obj.mainFid},
            #{obj.trainPersonId},
            #{obj.trainPersonName},
            #{obj.trainPersonDeptCode},
            #{obj.trainPersonDeptName},
            #{obj.post},
            #{obj.notes},
            #{obj.trainName}
            )
        </foreach>
    </insert>
    <update id="updateStatus">
        update base.sp_train_feedback
        set feedback_status = '1' where main_fid=#{mainFid}
    </update>
    <update id="setstatus">
        update base.sp_train_feedback
        set feedback_status = '2' where main_fid=#{fid}
            and train_person_name=#{trainPersonName}
    </update>
    <update id="updateS">
        update base.sp_train_feedback
        set feedback_status = '2' where main_fid=#{mainFid}
            and train_person_name=#{trainPersonName}
    </update>
    <update id="updateStatusP">
        update base.sp_train_feedback
        set feedback_status = '1' where main_fid=#{fid}
            and train_person_name=#{trainPersonName}
    </update>
    <update id="updatePerson">
        update base.d_train_content
        set feedback_status = #{feedbackStatus}, creator_post = #{creatorPost} where main_fid=#{fid}
            and train_person_id=#{userId}
            and creator=#{creator}
    </update>


    <delete id="deleteAll">
        DELETE FROM base.d_train
    </delete>
    <delete id="delectRecord">
       DELETE FROM base.sp_train_feedback
    </delete>
    <delete id="delectOne">
        delete
        from base.sp_train_feedback
        where train_person_name is null
    </delete>
    <delete id="deletePerson">
        delete
        from base.d_feedcontent
        where fid=#{fid}
    </delete>
    <update id="deleteContent">
        update
            base.sp_train_content
        set feedback_status = #{feedbackStatus},creator_post = #{creatorPost} where main_fid=#{fid}
            and train_person_name=#{trainPersonName}
    </update>
    <update id="updateStatusPs">
        update base.sp_train_feedback
        set feed_status = '1' where main_fid=#{fid}
            and train_person_name=#{trainPersonName}
    </update>
    <update id="setstatuss">
        update base.sp_train_feedback
        set feed_status = '2' where main_fid=#{fid}
            and train_person_name=#{trainPersonName}
    </update>
    <update id="fbSjForTrainMain">
        update jdjczfryzz.sp_train_person
        set feedback_status = '0' where main_fid=#{mainFid}
    </update>
    <delete id="deleteContt">
        delete
        from base.sp_train_content
        where train_name=#{trainName}
          and train_person_name=#{trainPersonName}
          and feedback_status='1'
    </delete>
    <delete id="deletePp">
        delete
        from base.sp_train_feedback
        where train_name=#{trainName}
          and train_person_name=#{trainPersonName}
          and feedback_status='1'
    </delete>
    <delete id="deleteFeedbackByMainFidAndDeptCode">
        delete
        from base.sp_train_feedback
        where main_fid=#{mainFid}
          and train_person_dept_code=#{trainPersonDeptCode}
    </delete>
    <delete id="deleteContentByMainFidAndDeptCode">
        delete
        from base.d_train_content
        where main_fid=#{mainFid}
          and train_person_dept_code=#{trainPersonDeptCode}
    </delete>
    <select id="selectAll" resultType="com.unis.base.server.entity.TrainDO">
        select *
        from base.d_meeting
    </select>
    <!--<select id="selectBath" resultType="com.unis.base.common.vo.PersonMeetingVO">
        select *
        from base.sp_train_feedback
        where train_name =#{meetingName}
    </select>-->
    <select id="selectInFo" resultType="com.unis.base.server.entity.TrainDO">
        select sm.train_name meetingName,sm.creator publisher, sm.release_date,sm.fid
        from jdjczfryzz.sp_train_person sp left join jdjczfryzz.sp_train_main sm on sp.main_fid=sm.fid
        <where>
            <if test="trainName!=null and trainName!='' ">
                and sm.train_name like CONCAT('%',#{trainName},'%')
            </if>
            <if test="status!=null and status!='' ">
                and sp.feedback_status=#{status}
            </if>
            and release_status='0' and  train_person_id = #{userId}
        </where>

    </select>
    <select id="selectPerson" resultType="com.unis.base.server.entity.TrainDO">
        select count(sp.train_person_id) persons, sm.train_name meetingName
        from jdjczfryzz.sp_train_person sp left join jdjczfryzz.sp_train_main sm
        on sp.main_fid=sm.fid
        group by sm.train_name
    </select>
    <select id="selectStatus" resultType="com.unis.base.server.entity.TrainDO">
        select distinct feedback_status status, main_fid fid
        from jdjczfryzz.sp_train_person

    </select>
    <select id="selectAllS" resultType="com.unis.base.server.entity.TrainDO">
        select *
        from jdjczfryzz.d_train

    </select>
    <select id="selectRecord" resultType="com.unis.base.server.entity.MeetingRecordDO">
        select p.fid,
               p.main_fid,
               p.train_person_id,
               p.create_date,
               p.feedback_content,
               p.feedback_status,
               m.train_name,
               m.release_status,
               m.creator,
               m.creator_code,
               u.user_name as train_person_name,
               u.dept_code as train_person_dept_code,
               d.dept_name as train_person_dept_name,
               u.org_code  as train_person_org_code,
               o.org_name  as train_person_org_name
        from jdjczfryzz.sp_train_person p
        left join usercenter.sys_user_info u
        on p.train_person_id = u.user_code
            left join usercenter.sys_department d on u.dept_code = d.dept_code
            left join usercenter.sys_orgainze o on u.org_code = o.org_code
            left join jdjczfryzz.sp_train_main m on p.main_fid=m.fid
    </select>
    <select id="selectSoRecords" resultType="com.unis.base.common.vo.ResultRecodrdVO">
        select distinct sp.train_name, sp.release_date annual, sp.create_date, sp.fid
        from jdjczfryzz.sp_train_person sm left join jdjczfryzz.sp_train_main sp
        on sm.main_fid=sp.fid
        <where>
            <if test="trainName!=null and trainName!=''">
                and sp.train_name=#{trainName}
            </if>
            and feedback_status='2'
            and sp.train_name is not null
        </where>

    </select>
    <select id="selectTrain" resultType="com.unis.base.common.vo.ResultRecodrdVO">
        select count(sp.main_fid) persons, sm.train_name
        from jdjczfryzz.sp_train_person sp left join
        jdjczfryzz.sp_train_main sm
        on sm.fid=sp.main_fid
        group by sm.train_name
    </select>
    <select id="selectPersonRecord" resultType="com.unis.base.common.vo.InsertRecordVO">
        select train_person_name,feedback_status,train_person_id,main_fid fid,creator_post
        from base.d_train_content
        <where>
            main_fid=#{fid} and creator=#{userId}
            <if test="trainPersonName!=null and trainPersonName!=''">
                and train_person_name=#{trainPersonName}
            </if>
            <if test="trainPersonDeptName!=null and trainPersonDeptName!=''">
                and train_person_dept_name=#{trainPersonDeptName}
            </if>
        </where>


    </select>
    <select id="selectCountPerson" resultType="com.unis.base.common.vo.CountPersonVO">
        select distinct create_date,train_person_dept_name,train_person_name,post from base.sp_train_feedback
        <where>
            <if test="createDate!=null">
                create_date=#{createDate}
            </if>
            <if test="trainPersonName!=null and trainPersonName!=''">
                train_person_name=#{trainPersonName}
            </if>
        </where>
    </select>
    <select id="selectApplication" resultType="com.unis.base.common.vo.CountPersonVO">
        select count(train_person_id) application, train_person_name
        from base.sp_train_feedback
        group by train_person_name
    </select>
    <select id="selectPost" resultType="com.unis.base.common.vo.CountPersonVO">
        select count(train_person_id) application, train_person_name
        from base.sp_train_feedback
        where feedback_status='2'
        group by train_person_name
    </select>
    <select id="meetingDetialRecodrd" resultType="com.unis.base.common.vo.MeetingRecordVO">
        select *
        from base.d_train_content
        where train_person_dept_code=#{deptId}
    </select>
    <select id="selectJoined" resultType="com.unis.base.common.vo.ResultRecodrdVO">
        select count(sp.train_person_id) joins, sm.fid
        from jdjczfryzz.sp_train_person sp left join
        jdjczfryzz.sp_train_main sm
        on sm.fid=sp.main_fid
        where feedback_status='2'
        group by sm.fid

    </select>
    <select id="selectCreator" resultType="com.unis.base.common.vo.ResultRecodrdVO">
        select fid, creator
        from jdjczfryzz.sp_train_main
    </select>
    <select id="selectDetails" resultType="com.unis.base.server.entity.MeetingRecordDO">
        select fid,
        main_fid,
        train_person_id,
        create_date,
        feedback_content,
        feedback_status,
        train_name,
        train_person_name,
        train_person_dept_code,
        train_person_dept_name,
        train_person_org_code,
        train_person_org_name,
        creator,
        creator_code
        from base.sp_train_feedback
        <where>
            <if test="trainName!=null and trainName!=''">
                and train_name=#{trainName}
            </if>
            <if test="creatorCode!=null and creatorCode!=''">
                and creator_code=#{creatorCode}
            </if>
            <if test="deptCode!=null and deptCode!=''">
                and train_person_dept_code=#{deptCode}
            </if>
        </where>
    </select>
    <select id="selectIn" resultType="com.unis.base.common.vo.CountPersonVO">
        select count(train_person_id) application, train_person_name
        from base.sp_train_feedback
        group by train_person_name
    </select>
    <select id="selectOut" resultType="com.unis.base.common.vo.CountPersonVO">
        select count(train_person_id) train, train_person_name
        from base.sp_train_feedback
        where feedback_status='2'
        group by train_person_name
    </select>
    <select id="selectPp" resultType="com.unis.base.common.vo.RecordVO">
        select *
        from base.sp_train_feedback
        where train_name=#{trainName}
    </select>
    <select id="selectDepp" resultType="java.lang.String">
        select train_person_dept_code
        from base.sp_train_feedback
        where train_person_id=#{trainPersonId}
    </select>
    <select id="selectUser" resultType="java.lang.String">
        select creator
        from base.sp_train_feedback
        where creator_code=#{userId}
    </select>
    <select id="selectAllP" resultType="com.unis.base.common.vo.MeetingRecordVO">
        select *
        from base.sp_train_feedback
        where creator=#{userName}
    </select>
    <select id="selectDapt" resultType="java.lang.String">
        select train_person_dept_code
        from base.sp_train_feedback
        where train_person_id=#{userId}
    </select>
    <select id="selectAllperson" resultType="com.unis.base.common.vo.MeetingRecordVO">
        select *
        from base.sp_train_feedback
        where train_person_dept_code=#{deptId}
    </select>
    <select id="selectDett" resultType="com.unis.base.common.vo.MeetingRecordVO">
        select *
        from base.d_train_content
        <where>
            main_fid=#{mainFid} and feedback_status='2'
            <if test="trainPersonDeptCode!=null and trainPersonDeptCode!=''">
                and train_person_dept_code=#{trainPersonDeptCode}
            </if>
        </where>
    </select>
    <select id="selectAllpp" resultType="com.unis.base.common.vo.MeetingRecordVO">
        select *
        from base.d_train_content
        where main_fid=#{fid}
    </select>
    <select id="selefe" resultType="com.unis.base.common.vo.MainRecordVO">
        select train_person_name, feedback_status, creator_post
        from base.d_train_content
        where main_fid=#{fid}
    </select>
    <select id="selectallus" resultType="com.unis.base.common.vo.MeetingRecordVO">
        select *
        from base.sp_train_feedback
        <where>
            <if test="trainName!=null and trainName!='' ">
                and train_name=#{trainName}
            </if>
            <if test="status!=null and status!='' ">
                and feedback_status=#{status}
            </if>
            and release_status='0'
        </where>
    </select>
    <select id="countContent" resultType="java.lang.Integer">
        select count(1)
        from base.d_train_content
        <where>
            main_fid=#{fid}
            <if test="trainPersonDeptCode!=null and trainPersonDeptCode!=''">
                and train_person_dept_code=#{trainPersonDeptCode}
            </if>
        </where>
    </select>
    <select id="pageInfoForPx" resultType="com.unis.base.common.vo.TrainMainVo">
        select
        remark, creator, fid,
        train_name as trainName,
        train_period as trainPeriod,
        train_location as trainLocation,
        file_id as fileId,
        release_status as releaseStatus,
        release_date as releaseDate,
        meetings_or_training as meetingsOrTraining,
        date_format(create_date, '%Y') as releaseYear
        from jdjczfryzz.sp_train_main m
        <trim prefix="where" prefixOverrides="and|or">
            status = 1
            <if test="train_name != null and train_name!=''">
                and instr(train_name, #{train_name}) > 0
            </if>
            <if test="release_status != null">
                and release_status = #{release_status}
            </if>
            <if test="release_status_arr != null and release_status_arr.length > 0">
                and m.release_status in
                <foreach collection="release_status_arr" item="item" index="index" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            <if test="release_date_begin != null and release_date_begin != '' and release_date_end != null and release_date_end != ''">
                and (release_date between date_format(#{release_date_begin}, '%Y-%m-%d') and date_format(#{release_date_end},
                '%Y-%m-%d'))
            </if>
            <if test="release_year != null and release_year != ''">
                and date_format(create_date, '%Y') = #{release_year}
            </if>
        </trim>
        order by create_date asc  limit 0,1
--         order by create_date asc nulls limit 0,1
    </select>


</mapper>
