备忘:一个ibatis应用的重要sql-mapping.xml部分代码
总共有一万多行
代码如下(注释已被本人删除):
- <?
- xml version="1.0" encoding="utf-8" ?>
- <sqlMap namespace="dp5" xmlns="http://ibatis.apache.org/mapping" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
- <cacheModels>
- <!-- 需要SqlMap.config配置 cacheModelsEnabled-->
- <cacheModel id="db-base-cache" implementation="LRU" readOnly="true" serialize="false">
- <flushInterval hours="24"/>
- <!--<flushOnExecute statement="insertProduct"/>-->
- <property name="Type" value="STRONG"/>
- <property name="CacheSize" value="10000"/>
- </cacheModel>
- </cacheModels>
- <alias>
- <typeAlias alias="MenuDTOAlias" type="DPSystem5Biz.DTO.MenuDTO, DPSystem5Biz" />
- <typeAlias alias="DP5UserDTOAlias" type="DPSystem5Biz.DTO.DP5UserDTO, DPSystem5Biz" />
- <typeAlias alias="RoleDTOAlias" type="DPSystem5Biz.DTO.RoleDTO, DPSystem5Biz" />
- <typeAlias alias="SmsInBasicDTOAlias" type="DPSystem5Biz.DTO.SmsInBasicDTO, DPSystem5Biz" />
- <typeAlias alias="ProvinceDTOAlias" type="DPSystem5Biz.DTO.ProvinceDTO, DPSystem5Biz" />
- <typeAlias alias="CityDTOAlias" type="DPSystem5Biz.DTO.CityDTO, DPSystem5Biz" />
- <typeAlias alias="FilialeStoreDTOAlias" type="DPSystem5Biz.DTO.FilialeStoreDTO, DPSystem5Biz" />
- <typeAlias alias="BaseDataDTOAlias" type="DPSystem5Biz.DTO.BaseDataDTO, DPSystem5Biz" />
- <typeAlias alias="BaseData2DTOAlias" type="DPSystem5Biz.DTO.BaseData2DTO, DPSystem5Biz" />
- <typeAlias alias="MemberDTOAlias" type="DPSystem5Biz.DTO.MemberDTO, DPSystem5Biz" />
- <typeAlias alias="ProductCodingDTOAlias" type="DPSystem5Biz.DTO.ProductCodingDTO, DPSystem5Biz" />
- <typeAlias alias="ProductDTOAlias" type="DPSystem5Biz.DTO.ProductDTO, DPSystem5Biz" />
- <typeAlias alias="TaskDTOAlias" type="DPSystem5Biz.DTO.TaskDTO, DPSystem5Biz" />
- <typeAlias alias="DistributionEntryDTOAlias" type="DPSystem5Biz.DTO.DistributionEntryDTO, DPSystem5Biz" />
- <typeAlias alias="PriceEntryDTOAlias" type="DPSystem5Biz.DTO.PriceEntryDTO, DPSystem5Biz" />
- <typeAlias alias="SmsSurveyEntryDTOAlias" type="DPSystem5Biz.DTO.SmsSurveyEntryDTO, DPSystem5Biz" />
- <typeAlias alias="SmsSurveyDTOAlias" type="DPSystem5Biz.DTO.SmsSurveyDTO, DPSystem5Biz" />
- <typeAlias alias="RetailerDTOAlias" type="DPSystem5Biz.DTO.RetailerDTO, DPSystem5Biz" />
- <typeAlias alias="VRetailerDTOAlias" type="DPSystem5Biz.DTO.VRetailerDTO, DPSystem5Biz" />
- <typeAlias alias="StoreDTOAlias" type="DPSystem5Biz.DTO.StoreDTO, DPSystem5Biz" />
- <typeAlias alias="AgentDTOAlias" type="DPSystem5Biz.DTO.AgentDTO, DPSystem5Biz" />
- <typeAlias alias="FilialeDTOAlias" type="DPSystem5Biz.DTO.FilialeDTO, DPSystem5Biz" />
- <typeAlias alias="StoreAppraisalDTOAlias" type="DPSystem5Biz.DTO.StoreAppraisalDTO, DPSystem5Biz" />
- <typeAlias alias="AddressListDTOAlias" type="DPSystem5Biz.DTO.AddressListDTO, DPSystem5Biz" />
- .......
- <resultMap id="VProductStockDTOMap" class="VProductStockDTOAlias">
- <result property="PDId" column="PDId" />
- <result property="Province" column="Province" />
- <result property="City" column="City" />
- <result property="StoreName" column="StoreName" />
- <result property="Remark1" column="Remark1" />
- <result property="RetailerName" column="RetailerName" />
- <result property="FilialeName" column="FilialeName" />
- <result property="AgentName" column="AgentName" />
- <result property="Sku" column="Sku" />
- <result property="Category" column="Category" />
- <result property="Series" column="Series" />
- <result property="Code" column="Code" />
- <result property="Stock" column="Stock" />
- <result property="RecvTime" column="RecvTime" />
- </resultMap>
- <resultMap id="VProductStock1DTOMap" class="VProductStock1DTOAlias">
- <result property="PDId" column="PDId" />
- <result property="Province" column="Province" />
- <result property="City" column="City" />
- <result property="StoreName" column="StoreName" />
- <result property="Remark1" column="Remark1" />
- <result property="RetailerName" column="RetailerName" />
- <result property="Sku" column="Sku" />
- <result property="Category" column="Category" />
- <result property="Series" column="Series" />
- <result property="Code" column="Code" />
- <result property="Stock" column="Stock" />
- <result property="RecvTime" column="RecvTime" />
- </resultMap>
- <resultMap id="SurveyStatusMap" class="BaseDataDTOAlias">
- <result property="Code" column="Id" />
- <result property="Description" column="Remark" />
- </resultMap>
- <resultMap id="SmsChannelMap" class="BaseDataDTOAlias">
- <result property="Code" column="Id" />
- <result property="Description" column="Remark" />
- </resultMap>
- <resultMap id="TaskProcFlagMap" class="BaseDataDTOAlias">
- <result property="Code" column="Id" />
- <result property="Description" column="Remark" />
- </resultMap>
- <resultMap id="RetailerLevelMap" class="BaseDataDTOAlias">
- <result property="Code" column="Id" />
- <result property="Description" column="Remark" />
- </resultMap>
- <resultMap id="StoreLevelMap" class="BaseDataDTOAlias">
- <result property="Code" column="Id" />
- <result property="Description" column="Remark" />
- </resultMap>
- <resultMap id="BusinessScopeMap" class="BaseDataDTOAlias">
- <result property="Code" column="Id" />
- <result property="Description" column="Remark" />
- </resultMap>
- <resultMap id="SupplyChannelMap" class="BaseDataDTOAlias">
- <result property="Code" column="Id" />
- <result property="Description" column="Remark" />
- </resultMap>
- <resultMap id="StoreStatusMap" class="BaseDataDTOAlias">
- <result property="Code" column="Id" />
- <result property="Description" column="Remark" />
- </resultMap>
- <resultMap id="MemberStatusMap" class="BaseDataDTOAlias">
- <result property="Code" column="Id" />
- <result property="Description" column="Remark" />
- </resultMap>
- <resultMap id="EducationMap" class="BaseDataDTOAlias">
- <result property="Code" column="Id" />
- <result property="Description" column="Remark" />
- </resultMap>
- <resultMap id="SexMap" class="BaseDataDTOAlias">
- <result property="Code" column="Id" />
- <result property="Description" column="Remark" />
- </resultMap>
- ......
- <resultMap id="ProductDTOMap" class="ProductDTOAlias">
- <result property="SkuId" column="SkuId" />
- <result property="Sku" column="Sku" />
- <result property="Point" column="Point" />
- <result property="Length" column="Length" />
- <result property="Category" column="Category" />
- <result property="Series" column="Series" />
- <result property="FactoryPrice" column="FactoryPrice" />
- <result property="RetailPrice" column="RetailPrice" />
- <result property="Remark1" column="Remark1" />
- <result property="Remark2" column="Remark2" />
- <result property="Remark3" column="Remark3" />
- <result property="JoinTime" column="JoinTime" />
- <result property="Opr" column="Operator" />
- </resultMap>
- <resultMap id="DP5UserDTOMap" class="DP5UserDTOAlias">
- <result property="UserName" column="UserName" />
- <result property="RealName" column="RealName" />
- <result property="Mobile" column="Mobile" />
- <result property="Password" column="Password" />
- <result property="Corp.CorpId" column="CorpId" />
- <result property="Corp.CorpName" column="CorpName" />
- <result property="Corp.SmsType.Code" column ="SmsTypeId" />
- <result property="Role.RoleId" column="RoleId" />
- <result property="Enabled" column="Enabled" />
- <result property="Remark" column="Remark" />
- <result property="JoinTime" column="JoinTime" />
- <result property="RegionId" column="RegionId" />
- </resultMap>
- <resultMap id="RegionUserDTOMap" class="DP5UserDTOAlias">
- <result property="UserName" column="UserName" />
- <result property="RealName" column="RealName" />
- <result property="Mobile" column="Mobile" />
- <result property="Password" column="Password" />
- <result property="Corp.CorpId" column="CorpId" />
- <result property="Corp.CorpName" column="CorpName" />
- <result property="Corp.SmsType.Code" column ="SmsTypeId" />
- <result property="Role.RoleId" column="RoleId" />
- <result property="Enabled" column="Enabled" />
- <result property="Remark" column="Remark" />
- <result property="Level.Code" column="LevelId" />
- <result property="RegionId" column="RegionId" />
- </resultMap>
- <resultMap id="CorpAccountsMap" class="DP5UserDTOAlias">
- <result property="UserName" column="UserName" />
- <result property="RealName" column="RealName" />
- <result property="Mobile" column="Mobile" />
- <result property="Enabled" column="Enabled" />
- <result property="Remark" column="Remark" />
- <result property="JoinTime" column="JoinTime" />
- </resultMap>
- <resultMap id="RoleDTOMap" class="RoleDTOAlias">
- <result property="RoleId" column="RoleId" />
- <result property="RoleName" column="RoleName" />
- <result property="RoleType.Code" column="RoleTypeId" />
- <result property="Remark" column="Remark" />
- </resultMap>
- ...........
- <select id="selectShopAssistantInfo1" resultClass="VShopAssistantInfoDTOAlias" parameterClass="DictionaryAlias" >
- select
- a.Mobile,
- c.UserName,
- b.Remark as Status,
- a.Remark1 as PromotorRemark1,
- f.District as Province,
- e.District as City,
- d.StoreName,
- g.Remark1,
- j.RetailerName,
- <!--
- v.RegionName as Region,
- h.FilialeName,
- i.AgentName,
- -->
- a.AuditTime,
- (select convert(varchar(50),sum(Distribution)) from DP_DistributionEntries where Mobile=a.Mobile and CorpId=a.CorpId
- <![CDATA[
- and RecvTime>=DATEADD(wk,DATEDIFF(wk,0,getdate()),0)
- and RecvTime<getdate()
- ]]> ) as [WeekSales],
- (select convert(varchar(50),sum(Distribution)) from DP_DistributionEntries where Mobile=a.Mobile and CorpId=a.CorpId
- <![CDATA[
- and RecvTime>=DATEADD(mm,DATEDIFF(mm,0,getdate()),0)
- and RecvTime<getdate()
- ]]> ) as [MonthSales],
- (select convert(varchar(50),sum(Distribution)) from DP_DistributionEntries where Mobile=a.Mobile and CorpId=a.CorpId) as [TotalSales]
- from DP_Members a
- left outer join DP_MemberStore k on k.Mobile=a.Mobile and k.CorpId=a.CorpId and k.SmsTypeId=a.SmsTypeId
- left outer join DP_MemberStatus_TBL b on b.Id=a.Status
- left outer join DP_Mobiles c on c.Mobile=a.Mobile
- left outer join DP_Stores d on d.StoreId=k.StoreId
- left outer join DP_Cities e on e.Id=d.CityId
- left outer join DP_Cities f on f.Id=e.ParentId
- left outer join DP_StoreAppraisal g on g.StoreId=k.StoreId and g.CorpId=a.CorpId
- left outer join DP_Retailers j on j.RetailerId=d.RetailerId
- <!--
- left outer join DP_Filiales h on h.FilialeId=g.FilialeId and h.CorpId=a.CorpId
- left outer join DP_Agents i on i.AgentId=g.AgentId and i.CorpId=a.CorpId
- left outer join DP_Regions v on v.CorpId=a.CorpId and v.RegionId=h.RegionId
- -->
- where a.CorpId=#corpId#
- <dynamic >
- <isNotEmpty property="mobile" prepend="and">
- a.Mobile like #mobile#
- </isNotEmpty>
- <isNotEmpty property="userName" prepend="and">
- c.UserName like #userName#
- </isNotEmpty>
- <isNotEmpty property="status" prepend="and">
- b.Id = #status#
- </isNotEmpty>
- <isNotEmpty property="promotorRemark1" prepend="and">
- a.Remark1 like #promotorRemark1#
- </isNotEmpty>
- <isNotEmpty property="province" prepend="and">
- f.Id in ($province$)
- </isNotEmpty>
- <isNotEmpty property="city" prepend="and">
- e.Id in ($city$)
- </isNotEmpty>
- <isNotEmpty property="storeName" prepend="and">
- e.StoreName like #storeName#
- </isNotEmpty>
- <isNotEmpty property="remark1" prepend="and">
- g.Remark1 like #remark1#
- </isNotEmpty>
- <isNotEmpty property="cBegin" prepend="and">
- <![CDATA[
- a.AuditTime >= #cBegin#
- ]]>
- </isNotEmpty>
- <isNotEmpty property="cEnd" prepend="and">
- <![CDATA[
- a.AuditTime < #cEnd#
- ]]>
- </isNotEmpty>
- </dynamic >
- order by a.Status desc
- </select>
- <select id="selectShopAssistantInfo2" resultClass="VShopAssistantInfoDTOAlias" parameterClass="DictionaryAlias" >
- select
- a.Mobile,
- c.UserName,
- b.Remark as Status,
- a.Remark1 as PromotorRemark1,
- f.District as Province,
- e.District as City,
- d.StoreName,
- g.Remark1,
- j.RetailerName,
- <!--
- v.RegionName as Region,
- h.FilialeName,
- i.AgentName,
- -->
- a.AuditTime,
- (select convert(varchar(50),sum(Distribution)) from DP_DistributionEntries where Mobile=a.Mobile and CorpId=a.CorpId
- <![CDATA[
- and RecvTime>=DATEADD(wk,DATEDIFF(wk,0,getdate()),0)
- and RecvTime<getdate()
- ]]> ) as [WeekSales],
- (select convert(varchar(50),sum(Distribution)) from DP_DistributionEntries where Mobile=a.Mobile and CorpId=a.CorpId
- <![CDATA[
- and RecvTime>=DATEADD(mm,DATEDIFF(mm,0,getdate()),0)
- and RecvTime<getdate()
- ]]> ) as [MonthSales],
- (select convert(varchar(50),sum(Distribution)) from DP_DistributionEntries where Mobile=a.Mobile and CorpId=a.CorpId) as [TotalSales]
- from DP_Members a
- left outer join DP_MemberStore k on k.Mobile=a.Mobile and k.CorpId=a.CorpId and k.SmsTypeId=a.SmsTypeId
- left outer join DP_MemberStatus_TBL b on b.Id=a.Status
- left outer join DP_Mobiles c on c.Mobile=a.Mobile
- left outer join DP_Stores d on d.StoreId=k.StoreId
- left outer join DP_Cities e on e.Id=d.CityId
- left outer join DP_Cities f on f.Id=e.ParentId
- left outer join DP_StoreAppraisal g on g.StoreId=k.StoreId and g.CorpId=a.CorpId
- left outer join DP_Retailers j on j.RetailerId=d.RetailerId
- <!--
- left outer join DP_Filiales h on h.FilialeId=g.FilialeId and h.CorpId=a.CorpId
- left outer join DP_Agents i on i.AgentId=g.AgentId and i.CorpId=a.CorpId
- left outer join DP_Regions v on v.CorpId=a.CorpId and v.RegionId=h.RegionId
- -->
- where a.CorpId=#corpId#
- <dynamic >
- <isNotEmpty property="mobile" prepend="and">
- a.Mobile like #mobile#
- </isNotEmpty>
- <isNotEmpty property="userName" prepend="and">
- c.UserName like #userName#
- </isNotEmpty>
- <isNotEmpty property="status" prepend="and">
- b.Id = #status#
- </isNotEmpty>
- <isNotEmpty property="promotorRemark1" prepend="and">
- a.Remark1 like #promotorRemark1#
- </isNotEmpty>
- <isNotEmpty property="province" prepend="and">
- f.Id in ($province$)
- </isNotEmpty>
- <isNotEmpty property="city" prepend="and">
- e.Id in ($city$)
- </isNotEmpty>
- <isNotEmpty property="storeName" prepend="and">
- e.StoreName like #storeName#
- </isNotEmpty>
- <isNotEmpty property="remark1" prepend="and">
- g.Remark1 like #remark1#
- </isNotEmpty>
- <isNotEmpty property="cBegin" prepend="and">
- <![CDATA[
- a.AuditTime >= #cBegin#
- ]]>
- </isNotEmpty>
- <isNotEmpty property="cEnd" prepend="and">
- <![CDATA[
- a.AuditTime < #cEnd#
- ]]>
- </isNotEmpty>
- <isNotEmpty property="rgId" prepend="and">
- k.StoreId in
- (
- select StoreId
- from V_RegionStore
- where CorpId=#corpId#
- and RegionId=#rgId#
- and LevelId=#levelId#
- )
- </isNotEmpty>
- </dynamic >
- order by a.Status desc
- </select>
- .........
- <select id="selectStoreAbnormalStock3" resultClass="VStoreAbnormalStockDTOAlias" parameterClass="DictionaryAlias" >
- SELECT
- AA.StoreId,
- AA.Province,
- AA.City,
- AA.StoreName,
- AA.Remark1,
- AA.RetailerName,
- AA.Status,
- AA.Address,
- AA.RegionName AS Region,
- AA.FilialeName,
- AA.AgentName,
- BB.DistributionCodes,
- CC.StockOutsCodes,
- AA.StockOutsCount
- FROM
- (
- SELECT d.StoreId, f.District AS Province, e.District AS City, d.StoreName, g.Remark1, l.RetailerName, k.Remark AS Status, d.Address, v.RegionName,h.FilialeName,
- i.AgentName, COUNT(x.PDId) AS StockOutsCount
- FROM DP_StockEntries AS x LEFT OUTER JOIN
- DP_StoreStock AS y ON y.PDId = x.PDId LEFT OUTER JOIN
- DP_Mobiles AS b ON b.Mobile = x.Mobile LEFT OUTER JOIN
- DP_Members AS c ON c.Mobile = x.Mobile AND c.CorpId = x.CorpId LEFT OUTER JOIN
- DP_Stores AS d ON d.StoreId = y.StoreId LEFT OUTER JOIN
- DP_Cities AS e ON e.ID = d.CityId LEFT OUTER JOIN
- DP_Cities AS f ON f.ID = e.ParentId LEFT OUTER JOIN
- DP_StoreAppraisal AS g ON g.StoreId = d.StoreId AND g.CorpId = x.CorpId LEFT OUTER JOIN
- DP_Filiales AS h ON h.FilialeId = g.FilialeId LEFT OUTER JOIN
- DP_Agents AS i ON i.AgentId = g.AgentId LEFT OUTER JOIN
- DP_ProductCoding AS j ON j.Code = x.Code AND j.CorpId = x.CorpId LEFT OUTER JOIN
- DP_StoreStatus_TBL AS k ON k.Id = g.Status LEFT OUTER JOIN
- DP_Retailers AS l ON l.RetailerId = d.RetailerId LEFT OUTER JOIN
- DP_Regions v ON v.RegionId=h.RegionId and v.CorpId=h.CorpId
- where StoreName is not null
- and x.CorpId=#corpId#
- <dynamic >
- <isNotEmpty property="storeName" prepend="and">
- d.StoreName like #storeName#
- </isNotEmpty>
- <isNotEmpty property="retailerName" prepend="and">
- l.RetailerName like #retailerName#
- </isNotEmpty>
- <isNotEmpty property="region" prepend="and">
- v.RegionId = #region#
- </isNotEmpty>
- <isNotEmpty property="status" prepend="and">
- g.Status = #status#
- </isNotEmpty>
- <isNotEmpty property="remark1" prepend="and">
- g.Remark1 like #remark1#
- </isNotEmpty>
- <isNotEmpty property="province" prepend="and">
- e.ParentId in ($province$)
- </isNotEmpty>
- <isNotEmpty property="city" prepend="and">
- e.Id in ($city$)
- </isNotEmpty>
- <isNotEmpty property="filiale" prepend="and">
- h.FilialeId = #filiale#
- </isNotEmpty>
- <isNotEmpty property="agentName" prepend="and">
- i.AgentName like #agentName#
- </isNotEmpty>
- <isNotEmpty property="rgId" prepend="and">
- y.StoreId in
- (
- select StoreId
- from V_RegionStore
- where CorpId=#corpId#
- and RegionId=#rgId#
- and LevelId=#levelId#
- )
- </isNotEmpty>
- </dynamic>
- <dynamic >
- <isNotEmpty property="cBegin" prepend="and">
- <![CDATA[
- x.RecvTime >= #cBegin#
- ]]>
- </isNotEmpty>
- <isNotEmpty property="cEnd" prepend="and">
- <![CDATA[
- x.RecvTime <= #cEnd#
- ]]>
- </isNotEmpty>
- </dynamic>
- GROUP BY d.StoreId, f.District, e.District, d.StoreName, g.Remark1, l.RetailerName, k.Remark, d.Address, h.FilialeName, i.AgentName,v.RegionName)
- AS AA LEFT OUTER JOIN
- (SELECT DP_StoreDistribution.StoreId, COUNT(DISTINCT DP_DistributionEntries.Code) AS DistributionCodes
- FROM DP_DistributionEntries,DP_StoreDistribution
- WHERE DP_DistributionEntries.PDId = DP_StoreDistribution.PDId
- <dynamic >
- <isNotEmpty property="cBegin" prepend="and">
- <![CDATA[
- DP_DistributionEntries.RecvTime >= DATEADD(day,-30,#cEnd#)
- ]]>
- </isNotEmpty>
- <isNotEmpty property="cEnd" prepend="and">
- <![CDATA[
- DP_DistributionEntries.RecvTime <= #cEnd#
- ]]>
- </isNotEmpty>
- </dynamic>
- GROUP BY DP_StoreDistribution.StoreId) AS BB ON AA.StoreId = BB.StoreId LEFT OUTER JOIN
- (SELECT DP_StoreStock.StoreId, COUNT(DISTINCT DP_StockEntries.Code) AS StockOutsCodes
- FROM DP_StockEntries,DP_StoreStock
- WHERE DP_StockEntries.PDId = DP_StoreStock.PDId
- <dynamic >
- <isNotEmpty property="cBegin" prepend="and">
- <![CDATA[
- DP_StockEntries.RecvTime >= #cBegin#
- ]]>
- </isNotEmpty>
- <isNotEmpty property="cEnd" prepend="and">
- <![CDATA[
- DP_StockEntries.RecvTime <= #cEnd#
- ]]>
- </isNotEmpty>
- </dynamic>
- GROUP BY DP_StoreStock.StoreId) AS CC ON AA.StoreId = CC.StoreId
- </select>
- .......