iamberry-app-international/iamberry-app-international-service/.svn/pristine/b7/b75518a8c690559d15a3bbdce3594e131c74c2ef.svn-base

<?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">
<!-- 命名，每一个映射对象不一样
	namespace:必须与对应的接口全类名一致
 -->
<mapper namespace="com.iamberry.app.mapper.MilkMapper">

	<sql id="milkAttribute">
		id,
		volume,
		controller,
		stars,
		comments,
		brand,
		powder_info,
		machine_name,
		machine,
		user,
		timestamp_
	</sql>
	
	<sql id="powderAttribute">
		id,
		powder_number,
		spoon_picture,
		bar_code,
		brand,
		series,
		level,
		version_,
		age_range,
		weight_per_spoon,
		milk_volume_per_spoon,
		powder_ratio,
		density,
		type,
		status_,
		created_on
	</sql>
	
	<resultMap type="MilkMakingRecord" id="milkMap">
		<id property="id" column="id"/>
		<result property="volume" column="volume"/>
		<result property="controller" column="controller"/>
		<result property="stars" column="stars"/>
		<result property="comments" column="comments"/>
		<result property="brand" column="brand"/>
		<result property="powder_info" column="powder_info"/>
		<result property="machine_name" column="machine_name"/>
		<result property="machine" column="machine"/>
		<result property="user" column="user"/>
		<result property="timestamp_" column="timestamp_"/>
	</resultMap>
	
	<resultMap type="MilkPowderInfo" id="powderMap">
		<id property="id" column="id"/>
		<result property="powder_number" column="powder_number"/>
		<result property="spoon_picture" column="spoon_picture"/>
		<result property="bar_code" column="bar_code"/>
		<result property="brand" column="brand"/>
		<result property="series" column="series"/>
		<result property="level" column="level"/>
		<result property="version_" column="version_"/>
		<result property="age_range" column="age_range"/>
		<result property="weight_per_spoon" column="weight_per_spoon"/>
		<result property="milk_volume_per_spoon" column="milk_volume_per_spoon"/>
		<result property="powder_ratio" column="powder_ratio"/>
		<result property="density" column="density"/>
		<result property="type" column="type"/>
		<result property="status_" column="status_"/>
		<result property="created_on" column="created_on"/>
	</resultMap>
	
	<resultMap type="com.iamberry.app.core.dto.MilkInfoDTO" id="milkInfoDtoMap">
		<id property="id" column="id"/>
		<result property="series" column="series"/>
		<result property="level" column="level"/>
		<result property="type" column="type"/>
		<result property="powder_ratio" column="powder_ratio"/>
		<result property="weight_per_spoon" column="weight_per_spoon"/>
	</resultMap>
	
	<!-- 添加信息到milk_making_record表中 -->
	<insert id="insertMilkMakingRecord" parameterType="MilkMakingRecord" useGeneratedKeys="true" keyProperty="id">
		INSERT INTO MILK_MAKING_RECORD
		(VOLUME,CONTROLLER,STARS,COMMENTS,BRAND,POWDER_INFO,MACHINE_NAME,MACHINE,USER,TIMESTAMP_) 
		VALUES
		(#{volume},#{controller},#{stars},#{comments},#{brand},#{powder_info},#{machine_name},#{machine},#{user},NOW())
	</insert>
	
	<!-- 根据id修改MILK_MAKING_RECORD的start和comments属性 -->
	<update id="updateMilkMakingRecord">
		UPDATE MILK_MAKING_RECORD SET STARS = #{0}, COMMENTS = #{1} WHERE ID = #{2}
	</update>
	
	<!-- 查询冲奶机冲奶时间和冲奶量 -->
	<select id="selectMilkMakingRecords" resultType="java.util.HashMap">
		SELECT 
			DATE_FORMAT(timestamp_, #{4}) AS #{3}, 
			SUM(volume) AS volume 
		FROM 
			milk_making_record 
		WHERE 
			<![CDATA[timestamp_ >= #{0}]]> 
		AND 
			<![CDATA[timestamp_ <= #{1}]]> 
		AND 
			controller = #{2} 
		AND 
			user = #{5} 
		AND 
			machine = #{6} 
		GROUP BY #{3}
	</select>
	
	<!-- 查询冲奶机冲奶时间和冲奶量 -->
	<select id="selectMachineMilkMakingRecords" resultType="java.util.HashMap">
		SELECT 
			DATE_FORMAT(timestamp_, #{4}) AS #{3}, 
			SUM(volume) AS volume 
		FROM 
			milk_making_record 
		WHERE 
			<![CDATA[timestamp_ >= #{0}]]> 
		AND 
			<![CDATA[timestamp_ <= #{1}]]> 
		AND 
			controller = #{2} 
		AND 
			machine = #{5} 
		GROUP BY #{3}
	</select>
	
	<!-- 根据id查询MILK_MAKING_RECORD所有信息 -->
	<select id="selectMilkMakingRecord" parameterType="java.lang.Long" resultMap="milkMap">
		SELECT <include refid="milkAttribute" /> FROM MILK_MAKING_RECORD WHERE ID = #{0}
	</select>
	
	<!-- 根据user查询所有冲奶信息 -->
	<select id="getMilkPowderByUser" parameterType="java.lang.Long" resultMap="milkMap">
		SELECT <include refid="milkAttribute" /> FROM MILK_MAKING_RECORD WHERE USER = #{0}
	</select>
	
	<!-- 根据user查询总冲奶量和次数 -->
	<select id="getMilkPowderSumByUser" parameterType="java.lang.Long" resultType="com.iamberry.app.core.dto.RecordCountInfoDTO">
		SELECT COUNT(1) count,SUM(VOLUME) total FROM MILK_MAKING_RECORD WHERE USER = #{0} 
	</select>
	
	<!-- 将奶粉信息插入到奶粉信息表中 -->
	<insert id="insertMilkPowderInfo" parameterType="MilkPowderInfo" useGeneratedKeys="true" keyProperty="id">
		INSERT INTO MILK_POWDER_INFO
			(POWDER_NUMBER,SPOON_PICTURE,BAR_CODE,BRAND,SERIES,LEVEL,VERSION_,AGE_RANGE,
			WEIGHT_PER_SPOON,MILK_VOLUME_PER_SPOON,POWDER_RATIO,DENSITY,TYPE,STATUS_,CREATED_ON) 
		VALUES
			(#{powder_number},#{spoon_picture},#{bar_code},#{brand},#{series},#{level},#{version_},#{age_range},#{weight_per_spoon},
			#{milk_volume_per_spoon},#{powder_ratio},#{density},#{type},#{status_},NOW())
	</insert>
	
	<!-- 通过条形码查询奶粉信息 -->
	<select id="selectMilkPowderInfo" parameterType="java.lang.String" resultMap="powderMap">
		SELECT <include refid="powderAttribute" /> FROM MILK_POWDER_INFO WHERE BAR_CODE = #{0} 
	</select>
	
	<!-- 根据品牌查询奶粉信息的系列 -->
	<select id="selectSeries" parameterType="java.lang.String" resultType="java.lang.String">
		SELECT 
			series 
		FROM 
			MILK_POWDER_INFO 
		WHERE 
			BRAND LIKE #{brand} 
		AND 
			UPPER(STATUS_) = 'APPROVED' 
		ORDER BY CONVERT(SERIES USING GBK) ASC
	</select>
	
	<!-- 根据系列查询奶粉信息的段数 -->
	<select id="selectLevel" parameterType="java.lang.String" resultType="java.lang.String">
		SELECT level FROM MILK_POWDER_INFO WHERE SERIES LIKE #{0} ORDER BY CONVERT(LEVEL USING GBK) ASC 
	</select>
	
	<!-- 根据奶粉品牌获取奶粉，详细信息 -->
	<select id="selectMilkInfoBybrand" parameterType="java.lang.String" resultMap="milkInfoDtoMap">
		SELECT 
			series,
			level,
			type,
			id,
			powder_ratio,
			weight_per_spoon 
		FROM 
			MILK_POWDER_INFO 
		WHERE 
			BRAND LIKE #{0} 
		AND 
			UPPER(STATUS_) = 'APPROVED' 
		ORDER BY CONVERT(SERIES USING GBK) ASC
	</select>
	
	<!-- 根据系列查询奶粉信息 -->
	<select id="selectMilkInfo" parameterType="java.lang.String" resultMap="milkInfoDtoMap">
		SELECT 
			level,type,id,powder_ratio,weight_per_spoon 
		FROM 
			MILK_POWDER_INFO 
		WHERE 
			SERIES LIKE #{0} 
		ORDER BY CONVERT(LEVEL USING GBK) ASC
	</select>
	
	<!-- 根据系列查询奶粉类型 -->
	<select id="selectMilkType" parameterType="java.lang.String" resultType="java.lang.String">
		SELECT type FROM MILK_POWDER_INFO WHERE SERIES LIKE #{0} ORDER BY CONVERT(LEVEL USING GBK) ASC
	</select>
	
	<!-- 根据奶粉系列查询奶粉密度 -->
	<select id="selectMilkDensity" parameterType="java.lang.String" resultType="java.lang.String">
		SELECT density FROM MILK_POWDER_INFO WHERE SERIES LIKE #{0} ORDER BY CONVERT(LEVEL USING GBK) ASC
	</select>
	
	<!-- 根据系列查询奶粉比例 -->
	<select id="selectMilkRatio" parameterType="java.lang.String" resultType="java.lang.String">
		SELECT powder_ratio FROM MILK_POWDER_INFO WHERE SERIES LIKE #{0} ORDER BY CONVERT(LEVEL USING GBK) ASC
	</select>
	
	<!-- 根据系列查询奶粉id -->
	<select id="selectMilkId" parameterType="java.lang.String" resultType="java.lang.Long">
		SELECT id FROM MILK_POWDER_INFO WHERE SERIES LIKE #{0} ORDER BY CONVERT(LEVEL USING GBK) ASC
	</select>
	
	<!-- 根据系列查询奶粉的重量 -->
	<select id="selectMilkWeightPerSpoon" parameterType="java.lang.String" resultType="java.lang.String">
		SELECT weight_per_spoon FROM MILK_POWDER_INFO WHERE SERIES LIKE #{0} ORDER BY CONVERT(LEVEL USING GBK) ASC
	</select>
	
	<!-- 查询奶粉品牌 -->
	<select id="selectMilkPowderBrand" resultType="java.lang.String">
		SELECT brand FROM MILK_POWDER_INFO WHERE STATUS_ = 'approved' GROUP BY BRAND
	</select>
	
	<!-- 根据系列段数品牌获取奶粉信息 -->
	<select id="selectMilkInfoByBrandSeriesLevel" resultMap="powderMap">
		SELECT <include refid="powderAttribute" /> FROM MILK_POWDER_INFO WHERE BRAND = #{0} AND SERIES = #{1} AND LEVEL = #{2} 
	</select>
	
	<!-- 查询冲奶记录的冲奶时间 -->
	<select id="selectLastRecordDate" resultType="java.util.Date">
		SELECT timestamp_ FROM MILK_MAKING_RECORD WHERE CONTROLLER = 'machine' ORDER BY ID DESC LIMIT 1 
	</select>
	
	<!-- 根据用户id查询冲奶机机器码 -->
	<select id="getMyMachines" parameterType="java.lang.Long" resultType="java.lang.String">
		SELECT machine_mac FROM MACHINE WHERE OWNER = #{0}
	</select>
	
	<!-- 将信息插入到冲奶记录表中 -->
	<insert id="insertRecordData" parameterType="MilkMakingRecord">
		INSERT INTO milk_making_record (volume, controller, machine, timestamp_, user) VALUE (#{volume}, 'machine', #{machine}, #{timestamp_}, #{user})
	</insert>
	
	<!-- milk_images表中添加数据 -->
	<insert id="insertMilkImages" parameterType="MilkImages" useGeneratedKeys="true" keyProperty="image_id">
		INSERT INTO MILK_IMAGES
		(
			IMAGE_MILK_ID,IMAGE_FRONT,IMAGE_BAR,IMAGE_FEED
		) 
		VALUES 
		(
			#{image_Milk_ID},#{image_Front},#{image_Bar},#{image_Feed}
		)
	</insert>
</mapper>