<?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.bonus.bracelet.mapper.BaseProjectMapper">

	<resultMap type="com.bonus.common.entity.bracelet.vo.BaseProject" id="BaseProjectResult">
		<id     property="projectId"        column="id"       />
		<result property="projectDepartName"      column="depart_name"     />
		<result property="projectHeadName"      column="head_name"     />
		<result property="contactInformation"        column="contact_information"        />
		<result property="createTime"    column="create_time"   />
		<result property="updateTime"    column="update_time"   />
		<result property="remarks"      column="remarks"     />
		<result property="updateBy"      column="update_user"     />
		<result property="createBy"      column="create_user"     />
		<result property="delFlag"    column="del_flag"   />
		<result property="appnum"    column="appnum"   />
	</resultMap>

	<select id="selectProjectList" parameterType="com.bonus.common.entity.bracelet.vo.BaseProject" resultMap="BaseProjectResult">
		select a.id, depart_name, head_name, contact_information,count(b.source_id) as appnum, remarks,a.create_user,a.update_user
		from tb_project_depart a
		left join sys_file_source b on a.id = b.source_id and b.source_type = 5 and b.del_flag = 0
		<where>
			a.del_flag = 0
			<if test="projectDepartName != null and projectDepartName != ''">
			and	INSTR(depart_name,#{projectDepartName}) > 0
			</if>
			<if test="projectHeadName != null and projectHeadName != ''">
			and	INSTR(head_name,#{projectHeadName}) > 0
			</if>
		</where>
		group by a.id
	</select>

	<select id="selectProjectById" parameterType="int" resultMap="BaseProjectResult">
		select a.id, depart_name, head_name, contact_information, remarks
		from tb_project_depart a
		where a.id = #{projectId}
	</select>

	<select id="selectProjectFile" parameterType="int" resultType="com.bonus.common.entity.file.ResourceFileVo">
		select 	  sfs.file_path AS filePath, sfs.id AS fileId
		from tb_project_depart a
		left join sys_file_source sfs on a.id = sfs.source_id and sfs.del_flag = 0
		where  sfs.source_type = 5 and a.id = #{projectId}
	</select>

	<select id="checkProjectDeptNameUnique"  resultMap="BaseProjectResult">
		select a.id, depart_name, head_name, contact_information,count(b.source_id) as appnum, remarks
		from tb_project_depart a
		left join sys_file_source b on a.id = b.source_id and b.del_flag = 0
		where depart_name = #{projectDepartName} and a.id != #{projectId}
		group by a.id
	</select>

	<select id="checkProjectDeptNameUniqueAdd"  resultMap="BaseProjectResult">
		select a.id, depart_name, head_name, contact_information,count(b.source_id) as appnum, remarks
		from tb_project_depart a
		left join sys_file_source b on a.id = b.source_id and b.del_flag = 0
		where depart_name = #{projectDepartName}
		group by a.id
	</select>

	<insert id="insertProject" parameterType="com.bonus.common.entity.bracelet.vo.BaseProject" useGeneratedKeys="true" keyProperty="projectId">
		insert into tb_project_depart(

		<if test="projectDepartName != null and projectDepartName != ''">depart_name,</if>
		<if test="projectHeadName != null and projectHeadName != ''">head_name,</if>
		<if test="contactInformation != null and contactInformation != ''">contact_information,</if>
		<if test="remarks != null and remarks != ''">remarks,</if>
		<if test="createBy != null and createBy != ''">create_user,</if>
		create_time,del_flag
		)values(
		<if test="projectId != null and projectId != 0">#{projectId},</if>
		<if test="projectDepartName != null and projectDepartName != ''">#{projectDepartName},</if>
		<if test="projectHeadName != null and projectHeadName != ''">#{projectHeadName},</if>
		<if test="contactInformation != null and contactInformation != ''">#{contactInformation},</if>
		<if test="remarks != null and remarks != ''">#{remarks},</if>
		<if test="createBy != null and createBy != ''">#{createBy},</if>
		sysdate(),0
		)
	</insert>

	<update id="updateProject" parameterType="com.bonus.common.entity.bracelet.vo.BaseProject">
		update tb_project_depart
		<set>
			<if test="projectDepartName != null and projectDepartName != ''">depart_name = #{projectDepartName},</if>
			<if test="projectHeadName != null and projectHeadName != ''">head_name = #{projectHeadName},</if>
			<if test="contactInformation != null and contactInformation != ''">contact_information = #{contactInformation},</if>
			<if test="remarks != null">remarks = #{remarks},</if>
			<if test="updateBy != null and updateBy != ''">update_user = #{updateBy},</if>
			update_time = sysdate()
		</set>
		where id = #{projectId}
	</update>

	<update id="deleteProjectByIds" parameterType="int">
		update tb_project_depart
			set	del_flag = 1
		where id = #{projectId}
	</update>

	<update id="deleteFile" parameterType="int">
		update sys_file_source
		set	del_flag = 1
		where source_id = #{projectId} and del_flag = 0
	</update>

	<update id="deleteFileId" parameterType="String">
		update sys_file_source
		set	del_flag = 1
		where file_path = #{filePath}
	</update>

	<select id="selectFilePathById" parameterType="int" resultType="java.lang.String">
		select file_path
		from sys_file_source
		where source_id = #{projectId} and del_flag = 0
	</select>

	<select id="getProjectSomeLists" resultMap="BaseProjectResult">
		select a.id, depart_name, head_name, contact_information,count(b.source_id) as appnum, remarks,a.create_user,a.update_user
		from tb_project_depart a
		left join sys_file_source b on a.id = b.source_id and b.source_type = 5 and b.del_flag = 0
		where
			a.del_flag = 0
			and a.id in
			<foreach collection="array" item="id" index="index" open="(" separator="," close=")">
				#{id}
			</foreach>
		group by a.id
	</select>
</mapper> 