<?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.warehouse.qrcode.mapper.QrCodeMapper">
    
	<sql id="qrCodeColumns">
		a.id AS "id",
		a.goods_info_id AS "goodsInfo.id",
		a.profit_warehousing_info_id AS "profitWarehousingInfo.id",
		a.code AS "code",
		a.come_flag AS "comeFlag",
		a.state AS "state",
		a.url AS "url",
		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",

		type.id AS "goodsInfo.type.id",
		type.code AS "goodsInfo.type.code",
		type.name AS "goodsInfo.type.name",
		type.amount AS "goodsInfo.type.amount",
		type.unit AS "goodsInfo.type.unit",

		type2.id AS "profitWarehousingInfo.type.id",
		type2.code AS "profitWarehousingInfo.type.code",
		type2.name AS "profitWarehousingInfo.type.name",
		type2.amount AS "profitWarehousingInfo.type.amount",
		type2.unit AS "profitWarehousingInfo.type.unit",

		pwi.NAME AS "profitWarehousingInfo.name",
		pwi.marking AS "profitWarehousingInfo.marking",
		pwi.amount AS "profitWarehousingInfo.amount",
		pwi.profit_warehousing_id AS "profitWarehousingInfo.profitWarehousing.id",
		pw.number AS "profitWarehousingInfo.profitWarehousing.number",

		gi.NAME AS "goodsInfo.name",
		gi.model AS "goodsInfo.model",
		gi.amount AS "goodsInfo.amount",
		gi.goods_id AS "goodsInfo.goods.id",
		g.batch_num AS "goodsInfo.goods.batchNum"


	</sql>
	
	<sql id="qrCodeJoins">
		LEFT JOIN t_wh_goods_info gi ON a.goods_info_id = gi.id
		LEFT JOIN t_wh_profit_warehousing_info pwi ON a.profit_warehousing_info_id = pwi.id
		LEFT JOIN t_wh_material_type type ON type.id = gi.type_id
		LEFT JOIN t_wh_material_type type2 ON type2.id = pwi.type
		LEFT JOIN t_wh_goods g ON g.id = gi.goods_id
		LEFT JOIN t_wh_profit_warehousing pw ON pwi.profit_warehousing_id = pw.id
	</sql>
	
    
	<select id="get" resultType="QrCode" >
		SELECT 
			<include refid="qrCodeColumns"/>
		FROM t_wh_qr a
		<include refid="qrCodeJoins"/>
		WHERE a.id = #{id}
	</select>
	
	<select id="findList" resultType="QrCode" >
		SELECT 
			<include refid="qrCodeColumns"/>
		FROM t_wh_qr a
		<include refid="qrCodeJoins"/>
		<where>
			a.del_flag = #{DEL_FLAG_NORMAL}
			${dataScope}
			<if test="code != null and code != ''">
				AND a.code like concat('%',#{code},'%')
			</if>
			<if test="goodsInfo != null and goodsInfo.goods != null and goodsInfo.goods.batchNum != null and goodsInfo.goods.batchNum != ''">
				AND g.batch_num = #{goodsInfo.goods.batchNum}
			</if>
			<if test="goodsInfo != null and goodsInfo.name != null and goodsInfo.name != ''">
				AND gi.name  like concat('%',#{goodsInfo.name},'%')
			</if>
			<if test="goodsInfo != null and goodsInfo.type != null and goodsInfo.type.id != null and goodsInfo.type.id != ''">
				AND gi.type_id = #{goodsInfo.type.id}
			</if>
			<if test="state != null and state != ''">
				AND a.state = #{state}
			</if>
			<if test="goodsInfo != null and goodsInfo.modelList != null and goodsInfo.modelList.size() != 0">
				AND
				<foreach collection="goodsInfo.modelList" item="model" index="index" separator=" AND" open="(" close=")">
					gi.model like concat('%',#{model},'%')
				</foreach>
			</if>
		</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="QrCode" >
		SELECT 
			<include refid="qrCodeColumns"/>
		FROM t_wh_qr a
		<include refid="qrCodeJoins"/>
		<where>
			a.del_flag = #{DEL_FLAG_NORMAL}
			${dataScope}
		</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 t_wh_qr(
			id,
			goods_info_id,
			profit_warehousing_info_id,
			come_flag,
			code,
			url,
			state,
			create_by,
			create_date,
			update_by,
			update_date,
			remarks,
			del_flag
		) VALUES (
			#{id},
			#{goodsInfo.id},
			#{profitWarehousingInfo.id},
			#{comeFlag},
			#{code},
			#{url},
			#{state},
			#{createBy.id},
			#{createDate},
			#{updateBy.id},
			#{updateDate},
			#{remarks},
			#{delFlag}
		)
	</insert>
	
	<update id="update">
		UPDATE t_wh_qr SET 	
			goods_info_id = #{goodsInfo.id},
			profit_warehousing_info_id = #{profitWarehousingInfo.id},
			come_flag = #{comeFlag},
			code = #{code},
			url = #{url},
			state = #{state},
			update_by = #{updateBy.id},
			update_date = #{updateDate},
			remarks = #{remarks}
		WHERE id = #{id}
	</update>
	
	
	<!--物理删除-->
	<update id="delete">
		DELETE FROM t_wh_qr
		WHERE id = #{id}
	</update>
	
	<!--逻辑删除-->
	<update id="deleteByLogic">
		UPDATE t_wh_qr SET 
			del_flag = #{DEL_FLAG_DELETE}
		WHERE id = #{id}
	</update>
	
	
	<!-- 根据实体名称和字段名称和字段值获取唯一记录 -->
	<select id="findUniqueByProperty" resultType="QrCode" statementType="STATEMENT">
		select * FROM t_wh_qr  where ${propertyName} = '${value}'
	</select>

	<!--根据货品明细删除-->
	<update id="deleteByGoodInfoId" parameterType="String">
		DELETE FROM t_wh_qr
		WHERE goods_info_id = #{infoId} and state = #{state}
	</update>

	<select id="findByGoodsInfoId" resultType="QrCode">
		SELECT
		<include refid="qrCodeColumns"/>
		FROM t_wh_qr a
		<include refid="qrCodeJoins"/>
		<where>
			a.del_flag = #{DEL_FLAG_NORMAL}
			<if test="goodsInfo.id != null and goodsInfo.id != ''">
				AND a.goods_info_id = #{goodsInfo.id}
			</if>
			<if test="state != null and state != ''">
				AND a.state = #{state}
			</if>
		</where>
	</select>

	<select id="findByPcTypeModel" resultType="QrCode" >
		SELECT
		a.id AS "id",
		a.goods_info_id AS "goodsInfo.id",
		a.code AS "code",
		a.url AS "url",
		a.state AS "state",
		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",

		type.id AS "goodsInfo.type.id",
		type.code AS "goodsInfo.type.code",
		type.name AS "goodsInfo.type.name",
		type.amount AS "goodsInfo.type.amount",
		type.unit AS "goodsInfo.type.unit",

		gi.NAME AS "goodsInfo.name",
		gi.model AS "goodsInfo.model",
		gi.amount AS "goodsInfo.amount",
		gi.num AS "goodsInfo.num",
		gi.goods_id AS "goodsInfo.goods.id"
		FROM t_wh_qr a
		LEFT JOIN t_wh_goods_info gi ON a.goods_info_id = gi.id
		LEFT JOIN t_wh_material_type type ON type.id = gi.type_id
		LEFT JOIN t_wh_goods g ON g.id = gi.goods_id
		<where>
			a.del_flag = #{DEL_FLAG_NORMAL}
			<if test="goodsInfo.goods.batchNum != null and goodsInfo.goods.batchNum != ''">
				AND g.batch_num = #{goodsInfo.goods.batchNum}
			</if>
			<if test="goodsInfo.type.id != null and goodsInfo.type.id != ''">
				AND gi.type_id = #{goodsInfo.type.id}
			</if>
			<if test="state != null and state != ''">
				AND a.state = #{state}
			</if>
			<if test="goodsInfo.modelList != null and goodsInfo.modelList.size() != 0">
				AND
				<foreach collection="goodsInfo.modelList" item="model" index="index" separator=" AND" open="(" close=")">
					gi.model like concat('%',#{model},'%')
				</foreach>
			</if>
		</where>
		<choose>
			<when test="page !=null and page.orderBy != null and page.orderBy != ''">
				ORDER BY ${page.orderBy}
			</when>
			<otherwise>
				ORDER BY a.code ASC
			</otherwise>
		</choose>
	</select>
</mapper>