<?xml version="1.0" encoding="utf-8" ?> <sqlMap namespace="GoodDetail" xmlns="http://ibatis.apache.org/mapping" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <resultMaps> <!--店铺商品列表实体类映射--> <resultMap id="ShopSaleGoodsListVMMap" class="Teld.EC.Mall.Goods.SPI.ShopSaleGoodsListVM"> <result property="ID" column="Good_ID"/> <result property="Name" column="Good_Name"/> <result property="GoodsState" column="Good_State"/> <result property="TypeID" column="Good_Type" /> <result property="ShopName" column="GS_Name"/> <result property="ShopState" column="GS_State"/> <result property="GbpvList" column="GoodsID=Good_ID,ShopID=Good_ShopID,CityID=CityID,ShowType=ShowType,SaleType=SaleType,CombineType=CombineType" select="GetShopGoodsGbpvListForInclude"/> </resultMap> </resultMaps> <statements> <!--查询店铺可售的商品列表--> <select id="GetShopGoodsGbpvList" resultMap="ShopSaleGoodsListVMMap" parameterClass="Teld.EC.Mall.Goods.SPI.GetShopGoodsGbpvInfoListDto"> select * from ( select ROW_NUMBER() Over(order by Good_Order desc) as row_num, Good_ID,Good_Type,Good_Name,Good_State, Good_Order,Good_RootTypeID,Good_ShopID ,'' GS_Name,'' GS_State,#ShowType# ShowType,#SaleType# SaleType,#CombineType# CombineType,isnull(#CityID#,'') CityID from goodtable a with(nolock) inner join goodDetailTable b with(nolock) on GDI_ID=Good_DefaultGDIID and Good_SaleType='1' and Good_State='2' and b.DelFlag='0' and a.DelFlag='0' where Good_ShopID=#ShopID# and Good_SaleType='1' and Good_State='2' and a.Delflag='0' <isNotEmpty prepend="and" property="GoodsTypeArr"> Good_Type in <iterate open=" (" close=") " conjunction="," property="GoodsTypeArr" > #GoodsTypeArr[]# </iterate> </isNotEmpty> and exists (select 1 from goodPropertyTable with(nolock) where GBPV_GoodID=Good_ID <isNotEmpty prepend=" " property="SaleType"> and GBPV_SaleType=#SaleType# </isNotEmpty> <isNotEmpty prepend=" " property="ShowType"> and GBPV_ShowType=#ShowType# </isNotEmpty> <isNotEmpty prepend=" " property="CombineType"> and GBPV_CombineType=#CombineType# </isNotEmpty> <isNotEmpty prepend=" " property="IsNeedExchange"> and GBPV_IsNeedExchange=#IsNeedExchange# </isNotEmpty> and DelFlag='0' and GBPV_State='2' ) ) f where row_num between (#Page#-1)*#Rows#+1 and (#Page#-1)*#Rows#+#Rows# </select> <!--查询店铺的商品详情列表--> <select id="GetShopGoodsGbpvListForInclude" resultClass="Teld.EC.Mall.Goods.SPI.ShopGoodsGbpvInfoVM" > select GDI_ID ID,GDI_GoodID GoodsID,GDI_ShopID ShopID,GDI_GBPVID GbpvID,GDI_Title Title,GDI_ImgURL ImgURL,GBPV_SKUGroupName SKUGroupName, case when GDI_GetGoodsType='2' or GDI_GetGoodsType='3' then isnull((select top 1 GIBT_Price from goodInfoTable with(nolock) where GIBT_GbpvID=GDI_GBPVID and delflag='0' and GIBT_EndDate>=getdate() and GIBT_Price>0 and ( GIBT_CityID=#CityID# or GIBT_CityID='*') order by GIBT_StartDate asc ),0) else isnull((select top 1 GP_Price from pricetable c with(nolock) where GP_GBPVID = Gbpv_ID and GP_ShopID=Gbpv_ShopID and c.DelFlag='0'),0) end Price from goodDetailTable b with(nolock) inner join goodPropertyTable j with(nolock) on GBPV_ID=GDI_GBPVID <isNotEmpty prepend=" " property="SaleType"> and GBPV_SaleType=#SaleType# </isNotEmpty> <isNotEmpty prepend=" " property="ShowType"> and GBPV_ShowType=#ShowType# </isNotEmpty> <isNotEmpty prepend=" " property="CombineType"> and GBPV_CombineType=#CombineType# </isNotEmpty> and GBPV_State='2' and j.DelFlag='0' where GDI_GoodID=#GoodsID# and GDI_ShopID=#ShopID# and b.DelFlag='0' and (GDI_EndValidityDate>getdate() or GDI_EndValidityDate is null) order by GBPV_Sort </select> <!--新增商品标签、城市、价格、库存、图片、介绍、售后、反选城市信息--> <update id="CreateGoodsParams" parameterClass="Teld.EC.Mall.Goods.SPI.CreateGoodsDto"> update goodTagTable set DelFlag='1',LastModifier=#LastModifier#,LastModifyTime=#LastModifyTime# where GTS_GoodsID=#GoodsID# and GTS_GbpvID=#GbpvID# and GTS_ShopID=#ShopID# <isNotEmpty prepend=" " property="ExistsTagList"> and GTS_ID not in <iterate open="(" close=")" conjunction="," property="ExistsTagList" > #ExistsTagList[].ID# </iterate> </isNotEmpty> and DelFlag='0' <isNotEmpty prepend=" " property="ExistsTagList"> <iterate open=" " close=" " conjunction=";" property="ExistsTagList" > update goodTagTable set GTS_TagID=#ExistsTagList[].TagID# ,GTS_CityID=#ExistsTagList[].CityID# ,GTS_Sort=#ExistsTagList[].Sort# ,LastModifier=#LastModifier# ,LastModifyTime=#LastModifyTime# where GTS_ID=#ExistsTagList[].ID# and GTS_GbpvID=#ExistsTagList[].GbpvID# and GTS_ShopID=#ExistsTagList[].ShopID# and DelFlag='0' </iterate> </isNotEmpty> <isNotEmpty prepend=" " property="NotExistsTagList"> <iterate open=" " close=" " conjunction=";" property="NotExistsTagList" > insert into goodTagTable (GTS_ID,GTS_GoodsID,GTS_GbpvID,GTS_ShopID,GTS_CityID,GTS_TagID,GTS_Sort,DelFlag,Creator,CreateTime,LastModifier,LastModifyTime) values(#NotExistsTagList[].ID#,#NotExistsTagList[].GoodsID#,#NotExistsTagList[].GbpvID#,#NotExistsTagList[].ShopID#,#NotExistsTagList[].CityID#,#NotExistsTagList[].TagID#,#NotExistsTagList[].Sort#,0,#Creator#,#CreateTime#,#LastModifier#,#LastModifyTime#) </iterate> </isNotEmpty> <include refid="UpdateGoodsTimeInfo"></include> </update> <!--更新商品销售日期信息--> <sql id="UpdateGoodsTimeInfo"> update goodInfoTable set DelFlag='1',LastModifier=#LastModifier#,LastModifyTime=#LastModifyTime# where GIBT_GoodsID=#GoodsID# and GIBT_GbpvID=#GbpvID# and GIBT_ShopID=#ShopID# <isNotEmpty prepend=" " property="ExistsSaleDaysInfoList"> and GIBT_ID not in <iterate open="(" close=")" conjunction="," property="ExistsSaleDaysInfoList" > #ExistsSaleDaysInfoList[].ID# </iterate> </isNotEmpty> and DelFlag='0' <isNotEmpty prepend=" " property="ExistsSaleDaysInfoList"> <iterate open=" " close=" " conjunction=";" property="ExistsSaleDaysInfoList" > update goodInfoTable set GIBT_MarketPrice=#ExistsSaleDaysInfoList[].MarketPrice# ,GIBT_Price=#ExistsSaleDaysInfoList[].Price# ,GIBT_Inventory=#ExistsSaleDaysInfoList[].Inventory# ,GIBT_StartDate=#ExistsSaleDaysInfoList[].StartDate# ,GIBT_EndDate=#ExistsSaleDaysInfoList[].EndDate# ,GIBT_CityID=#ExistsSaleDaysInfoList[].CityID# ,LastModifier=#LastModifier#,LastModifyTime=#LastModifyTime# where GIBT_ID=#ExistsSaleDaysInfoList[].ID# and GIBT_GbpvID=#ExistsSaleDaysInfoList[].GbpvID# and GIBT_ShopID=#ExistsSaleDaysInfoList[].ShopID# and DelFlag='0' </iterate> </isNotEmpty> <isNotEmpty prepend=" " property="NotExistsSaleDaysInfoList"> <iterate open=" " close=" " conjunction=";" property="NotExistsSaleDaysInfoList" > insert into goodInfoTable (GIBT_ID,GIBT_ShopID,GIBT_GoodsID,GIBT_GbpvID,GIBT_CityID,GIBT_Price,GIBT_MarketPrice,GIBT_Inventory,GIBT_StartDate,GIBT_EndDate,DelFlag,Creator,CreateTime,LastModifier,LastModifyTime) values(#NotExistsSaleDaysInfoList[].ID#,#NotExistsSaleDaysInfoList[].ShopID#,#NotExistsSaleDaysInfoList[].GoodsID#,#NotExistsSaleDaysInfoList[].GbpvID#,#NotExistsSaleDaysInfoList[].CityID#,#NotExistsSaleDaysInfoList[].Price#,#NotExistsSaleDaysInfoList[].MarketPrice#,#NotExistsSaleDaysInfoList[].Inventory#,#NotExistsSaleDaysInfoList[].StartDate#,#NotExistsSaleDaysInfoList[].EndDate#,0,#Creator#,#CreateTime#,#LastModifier#,#LastModifyTime#) </iterate> </isNotEmpty> </sql> </statements> </sqlMap>