<?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.jeeplus.modules.worksupplier.dao.WorkSupplierDao">
    
	<sql id="workSupplierColumns">
		a.id AS "id",
		a.create_by AS "createBy.id",
		a.create_date AS "createDate",
		a.update_by AS "updateBy.id",
		a.update_date AS "updateDate",
		a.remarks AS "remarks",
		a.del_flag AS "delFlag",
		a.usc_code AS "uscCode",
		a.office_id AS "officeId",
		a.company_id AS "companyId",
		a.name AS "name",
		a.address AS "address",
		a.zip_code AS "zipCode",
		a.deputy AS "deputy",
		a.fax AS "fax",
		a.website AS "website",
		a.regist_address AS "registAddress",
		a.regist_phone AS "registPhone",
		a.bank AS "bank",
		a.bank_number AS "bankNumber",
		a.link_name AS "linkName",
		a.qq AS "qq",
		a.link_phone AS "linkPhone",
		a.link_mobile AS "linkMobile",
		a.email AS "email",
		a.remove_sign AS "removeSign"
	</sql>
	
	<sql id="workSupplierJoins">
		LEFT JOIN sys_office o ON o.id = a.office_id
		LEFT JOIN sys_office s ON s.id = a.company_id
	</sql>
	
    
	<select id="get" resultType="WorkSupplier" >
		SELECT 
			<include refid="workSupplierColumns"/>
		FROM work_supplier a
		<include refid="workSupplierJoins"/>
		WHERE a.id = #{id}
	</select>
	
	<select id="findList" resultType="WorkSupplier" >
		SELECT 
			<include refid="workSupplierColumns"/>
		FROM work_supplier a
		<include refid="workSupplierJoins"/>
		<where>
			a.del_flag = #{DEL_FLAG_NORMAL}
			<if test="companyId != null and companyId != ''">
				AND a.company_id = #{companyId}
			</if>
			<if test="name != null and name != ''">
				AND a.name LIKE 
					<if test="dbName == 'oracle'">'%'||#{name}||'%'</if>
					<if test="dbName == 'mssql'">'%'+#{name}+'%'</if>
					<if test="dbName == 'mysql'">concat('%',#{name},'%')</if>
			</if>
			<if test="deputy != null and deputy != ''">
				AND a.deputy LIKE 
					<if test="dbName == 'oracle'">'%'||#{deputy}||'%'</if>
					<if test="dbName == 'mssql'">'%'+#{deputy}+'%'</if>
					<if test="dbName == 'mysql'">concat('%',#{deputy},'%')</if>
			</if>
			<if test="linkName != null and linkName != ''">
				AND a.link_name LIKE 
					<if test="dbName == 'oracle'">'%'||#{linkName}||'%'</if>
					<if test="dbName == 'mssql'">'%'+#{linkName}+'%'</if>
					<if test="dbName == 'mysql'">concat('%',#{linkName},'%')</if>
			</if>
			<if test="removeSign != null and removeSign != ''">
				AND a.remove_sign = #{removeSign}
			</if>
			<if test="uscCode != null and uscCode != ''">
				AND a.usc_code = #{uscCode}
			</if>

			<if test="beginContractDate != null and endContractDate != null and beginContractDate != '' and endContractDate != ''">
				AND a.create_date BETWEEN #{beginContractDate} AND #{endContractDate}
			</if>
            ${sqlMap.dsf}
		</where>
		<choose>
			<when test="page !=null and page.orderBy != null and page.orderBy != ''">
				ORDER BY ${page.orderBy}
			</when>
			<otherwise>
				ORDER BY a.update_date DESC
			</otherwise>
		</choose>
	</select>
	
	<select id="findAllList" resultType="WorkSupplier" >
		SELECT 
			<include refid="workSupplierColumns"/>
		FROM work_supplier a
		<include refid="workSupplierJoins"/>
		<where>
			a.del_flag = #{DEL_FLAG_NORMAL}
		</where>		
		<choose>
			<when test="page !=null and page.orderBy != null and page.orderBy != ''">
				ORDER BY ${page.orderBy}
			</when>
			<otherwise>
				ORDER BY a.update_date DESC
			</otherwise>
		</choose>
	</select>
	
	<insert id="insert">
		INSERT INTO work_supplier(
			id,
			create_by,
			create_date,
			update_by,
			update_date,
			remarks,
			del_flag,
			usc_code,
			office_id,
			company_id,
			name,
			address,
			zip_code,
			deputy,
			fax,
			website,
			regist_address,
			regist_phone,
			bank,
			bank_number,
			link_name,
			qq,
			link_phone,
			link_mobile,
			email,
			remove_sign
		) VALUES (
			#{id},
			#{createBy.id},
			#{createDate},
			#{updateBy.id},
			#{updateDate},
			#{remarks},
			#{delFlag},
			#{uscCode},
			#{officeId},
			#{companyId},
			#{name},
			#{address},
			#{zipCode},
			#{deputy},
			#{fax},
			#{website},
			#{registAddress},
			#{registPhone},
			#{bank},
			#{bankNumber},
			#{linkName},
			#{qq},
			#{linkPhone},
			#{linkMobile},
			#{email},
			#{removeSign}
		)
	</insert>
	
	<update id="update">
		UPDATE work_supplier SET 	
			update_by = #{updateBy.id},
			update_date = #{updateDate},
			remarks = #{remarks},
			usc_code = #{uscCode},
			office_id = #{officeId},
			company_id = #{companyId},
			name = #{name},
			address = #{address},
			zip_code = #{zipCode},
			deputy = #{deputy},
			fax = #{fax},
			website = #{website},
			regist_address = #{registAddress},
			regist_phone = #{registPhone},
			bank = #{bank},
			bank_number = #{bankNumber},
			link_name = #{linkName},
			qq = #{qq},
			link_phone = #{linkPhone},
			link_mobile = #{linkMobile},
			email = #{email},
			remove_sign = #{removeSign}
		WHERE id = #{id}
	</update>
	
	
	<!--物理删除-->
	<update id="delete">
		DELETE FROM work_supplier
		WHERE id = #{id}
	</update>
	
	<!--逻辑删除-->
	<update id="deleteByLogic">
		UPDATE work_supplier SET 
			del_flag = #{DEL_FLAG_DELETE}
		WHERE id = #{id}
	</update>


	
	<!-- 根据实体名称和字段名称和字段值获取唯一记录 -->
	<select id="findUniqueByProperty" resultType="WorkSupplier" statementType="STATEMENT">
		select * FROM work_supplier  where ${propertyName} = '${value}' and del_flag = 0
	</select>

	<update id="updateSign">
		UPDATE work_supplier SET
		remove_sign = #{removeSign}
		WHERE id = #{id}
	</update>
	<select id="findClientInfoList" resultType="WorkSupplier" >
		SELECT
		a.id,
		a.name,
		a.usc_code as "uscCode",
		b.our_bank as "bank",
		b.bank_number as "bankNumber"
		FROM work_client_bank b left join work_client_info a on a.id=b.client_id
		WHERE a.del_flag = '0'
		and b.client_id in (
		SELECT
		a.id
		FROM work_client_info a
		WHERE a.del_flag = '0'
		<if test="name != null and name != ''">
			AND a.name LIKE
			<if test="dbName == 'oracle'">'%'||#{name}||'%'</if>
			<if test="dbName == 'mssql'">'%'+#{name}+'%'</if>
			<if test="dbName == 'mysql'">concat('%',#{name},'%')</if>
		</if>
		<choose>
			<when test="branchOffice !=null and branchOffice != ''">
				and  a.office_id in (select off.id from sys_office off where off.branch_office=#{branchOffice})
			</when>
			<otherwise>
				and  a.office_id in
				(select off.id from sys_office off where off.parent_ids like concat('%',#{companyId},'%') and off.type=2 and (off.branch_office is null or off.branch_office=''))
			</otherwise>
		</choose>
		)
	</select>
</mapper>