<?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.caimei.www.mapper.ProductDao">
	<select id="getProductDetailById" resultType="com.caimei.www.pojo.page.ProductDetail">
		select
			p.productID as id,
			p.`name` as `name`,
			p.mainImage as image,
			p.price1 as price,
			p.unit as unit,
			p.price1TextFlag as priceFlag,

			p.tags as tags,
			p.productRemarks as remarks,
			p.validFlag as validFlag,
			p.stock as stock,
			p.productCode as productCode,
			p.minBuyNumber as minBuyNumber,
			p.step as step,
			p.recommendType as recommendType,

			d.detailInfo as detailInfo,
			d.orderInfo as orderInfo,
			d.serviceInfo as serviceInfo,

			p.brandID as brandId,
			br.name as brandName,
			p.tinyTypeID as tinyTypeId,
			t.name as tinyTypeText,
			t.smallTypeID as smallTypeId,
			s.name as smallTypeText,
			s.bigTypeID as bigTypeId,
			b.name as bigTypeText,

			p.shopID as shopId,
			sh.name as shopTitle,
			sh.businessScope as businessScope,
			sh.authorizationCertificateImage as license,
			CONCAT(pr.name, ci.name) as shopAddress


		from product p

		left join productdetailinfo d on p.productID=d.productID
		left join cm_brand as br on p.brandID = br.id

        left join tinytype as t on p.tinyTypeID = t.tinyTypeID
        left join smalltype as s on t.smallTypeID = s.smallTypeID
        left join bigtype as b on s.bigTypeID = b.bigTypeID

        left join shop as sh on p.shopID = sh.shopID
        left join town as tn on sh.townId = tn.townID
        left join city as ci on tn.cityID = ci.cityID
        left join province as pr on ci.provinceID = pr.provinceID

		where p.productID = #{productId}
		and p.validFlag in (2,3)
	</select>
	<select id="getProductRecommendsById" resultType="com.caimei.www.pojo.page.ProductList">
        select
			p.productID as id,
			p.`name` as `name`,
			p.mainImage as image
        from product as p
        left join cm_product_recommend as pr on pr.recommendProductID = p.productID
        where
            p.productID = #{productId} and validFlag  = 2
        order by pr.sort desc
	</select>
	<select id="getAutoProductRecommends" resultType="com.caimei.www.pojo.page.ProductList">
        select
			p.productID as id,
			p.`name` as `name`,
			p.mainImage as image
        from product as p
        where
			p.validFlag  = 2 and
			p.tinyTypeID = (select tinyTypeID from product as p1 where p1.productID = #{productId})
        order by p.sellNumber desc limit 0,7
	</select>
	<select id="getProductParameters" resultType="com.caimei.www.pojo.page.Parameter">
		select id as id,
		productId as parentId,
		paramsName as `name`,
		paramsContent as content,
		delFlag as type
		from cm_product_related_parameters
		where productId = #{productId} and delFlag='0'
	</select>
	<select id="getProductDetailImages" resultType="java.lang.String">
		select image
		from productimage
		where productID = #{productId}
		order by mainFlag desc ,sortIndex is null,sortIndex
	</select>
    <select id="getPriceItemById" resultType="com.caimei.www.pojo.order.CartItem">
		select
			p.productID as productId,
			p.price1 as price,
			p.unit as unit,
			p.step as step,
			p.minBuyNumber as min,
			p.price1TextFlag as priceFlag,
			p.ladderPriceFlag as ladderFlag,
			p.validFlag as validFlag,
			p.stock as stock
		from product p
		where p.productID = #{productId}
	</select>


</mapper>
