将查询的行转成列， 还有将查询的结果进去对比日期统计 （附SQLMap的源码）

最新推荐文章于 2022-01-25 11:42:50 发布
最新推荐文章于 2022-01-25 11:42:50 发布
阅读量1k 收藏
点赞数
文章标签： service date join list user sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/GTBGT525/article/details/4501398
版权

<?xml version="1.0" encoding="gb2312"?>
<sqlMap namespace="YGCSM_Global_Entitys" xmlns="http://ibatis.apache.org/mapping" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

 <!-- MAPPED alias -->
 <alias>
  <typeAlias alias="Global_YGCSM_SYS_CUSTOMER_CURR" type="YGCSM.Global.Entitys.SysCustomerCurr, YGCSM.Global.Entitys" />
 </alias>

 <!-- MAPPED resultMaps -->
 <resultMaps>
  <resultMap id="Global_YGCSM_SYS_CUSTOMER_CURR_Result" class="Global_YGCSM_SYS_CUSTOMER_CURR">
   <result property="CUST_NO" column="CUST_NO" />
   <result property="CUST_NAME" column="CUST_NAME" />
   <result property="PROVINCE" column="PROVINCE" />
   <result property="CUST_TYPE1" column="CUST_TYPE1" />
   <result property="CUST_TYPE2" column="CUST_TYPE2" />
   <result property="CUST_ADDR" column="CUST_ADDR" />
   <result property="CUST_POSTCODE" column="CUST_POSTCODE" />
   <result property="CUST_PHONE" column="CUST_PHONE" />
   <result property="CUST_FAX" column="CUST_FAX" />
   <result property="CUST_URL" column="CUST_URL" />
   <result property="SERVICE_NUM" column="SERVICE_NUM" />
   <result property="DEPT_ID" column="DEPT_ID" />
   <result property="SERVICE_PERSON_ID" column="SERVICE_PERSON_ID" />
   <result property="SERVICE_PERSON_TITLE" column="SERVICE_PERSON_TITLE" />
   <result property="CUST_LEVEL" column="CUST_LEVEL" />
   <result property="P_CUSTNO" column="P_CUSTNO" />
   <result property="P_CUST_NAME" column="P_CUST_NAME" />
   <result property="REMARK" column="REMARK" />
   <result property="IS_STOP" column="IS_STOP" />
   <result property="PREV_NO" column="PREV_NO" />
   <result property="CUST_UID" column="CUST_UID" />
   <result property="CUST_CODE" column="CUST_CODE" />
   <result property="CUST_CODE2" column="CUST_CODE2" />
   <result property="CREATE_TIME" column="CREATE_TIME" />
   <result property="MODIFY_TIME" column="MODIFY_TIME" />
   <result property="MODIFY_USER_ID" column="MODIFY_USER_ID" />
   <result property="MODIFY_USER" column="MODIFY_USER" />
   <result property="AUDIT_TIME" column="AUDIT_TIME" />
   <result property="AUDIT_USER" column="AUDIT_USER" />
   <result property="AUDIT_MESS" column="AUDIT_MESS" />
   <result property="STAMP" column="STAMP" />
   <result property="AUDIT_STATE" column="AUDIT_STATE" />
   <result property="HASCHILD" column="HASCHILD" />
  </resultMap>

 </resultMaps>

 <!-- MAPPED statements -->
 <statements>

  <select id="Global_YGCSM_SYS_CUSTOMER_CURR_SelectForDataTableDefault" parameterClass="System.Collections.IDictionary" resultClass="Global_YGCSM_SYS_CUSTOMER_CURR">
   <autoMakePersistSql table="YGCSM_SYS_CUSTOMER_CURR" keys="CUST_NO" notUpdateColumns="" />
   SELECT YGCSM_SYS_CUSTOMER_CURR.CUST_NO,
   YGCSM_SYS_CUSTOMER_CURR.CUST_NAME,
   YGCSM_SYS_CUSTOMER_CURR.PROVINCE,
   YGCSM_SYS_CUSTOMER_CURR.CUST_TYPE1,
   YGCSM_SYS_CUSTOMER_CURR.CUST_TYPE2,
   YGCSM_SYS_CUSTOMER_CURR.CUST_ADDR,
   YGCSM_SYS_CUSTOMER_CURR.CUST_POSTCODE,
   YGCSM_SYS_CUSTOMER_CURR.CUST_PHONE,
   YGCSM_SYS_CUSTOMER_CURR.CUST_FAX,
   YGCSM_SYS_CUSTOMER_CURR.CUST_URL,
   YGCSM_SYS_CUSTOMER_CURR.SERVICE_NUM,
   YGCSM_SYS_CUSTOMER_CURR.DEPT_ID,
   YGCSM_SYS_CUSTOMER_CURR.SERVICE_PERSON_ID,
   YGCSM_SYS_CUSTOMER_CURR.SERVICE_PERSON_TITLE,
   YGCSM_SYS_CUSTOMER_CURR.CUST_LEVEL,
   YGCSM_SYS_CUSTOMER_CURR.P_CUSTNO,
   YGCSM_SYS_CUSTOMER_CURR.P_CUST_NAME,
   YGCSM_SYS_CUSTOMER_CURR.REMARK,
   YGCSM_SYS_CUSTOMER_CURR.IS_STOP,
   YGCSM_SYS_CUSTOMER_CURR.PREV_NO,
   YGCSM_SYS_CUSTOMER_CURR.CUST_UID,
   YGCSM_SYS_CUSTOMER_CURR.CUST_CODE,
   YGCSM_SYS_CUSTOMER_CURR.CUST_CODE2,
   YGCSM_SYS_CUSTOMER_CURR.CREATE_TIME,
   YGCSM_SYS_CUSTOMER_CURR.MODIFY_TIME,
   YGCSM_SYS_CUSTOMER_CURR.MODIFY_USER_ID,
   YGCSM_SYS_CUSTOMER_CURR.MODIFY_USER,
   YGCSM_SYS_CUSTOMER_CURR.AUDIT_TIME,
   YGCSM_SYS_CUSTOMER_CURR.AUDIT_USER,
   YGCSM_SYS_CUSTOMER_CURR.AUDIT_MESS,
   YGCSM_SYS_CUSTOMER_CURR.STAMP,
   YGCSM_SYS_CUSTOMER_CURR.AUDIT_STATE,
   YGCSM_SYS_CUSTOMER_CURR.HASCHILD
   FROM YGCSM_SYS_CUSTOMER_CURR
   <dynamic prepend="WHERE">
    <isNotNull prepend="AND" property="CUST_NO">YGCSM_SYS_CUSTOMER_CURR.CUST_NO =#CUST_NO#</isNotNull>
   </dynamic>
  </select>


  <select id="Global_YGCSM_SYS_CUSTOMER_CURR_SelectForEntityDefault" parameterClass="System.Collections.IDictionary" resultClass="Global_YGCSM_SYS_CUSTOMER_CURR">

   SELECT YGCSM_SYS_CUSTOMER_CURR.CUST_NO,
   YGCSM_SYS_CUSTOMER_CURR.CUST_NAME,
   YGCSM_SYS_CUSTOMER_CURR.PROVINCE,
   YGCSM_SYS_CUSTOMER_CURR.CUST_TYPE1,
   YGCSM_SYS_CUSTOMER_CURR.CUST_TYPE2,
   YGCSM_SYS_CUSTOMER_CURR.CUST_ADDR,
   YGCSM_SYS_CUSTOMER_CURR.CUST_POSTCODE,
   YGCSM_SYS_CUSTOMER_CURR.CUST_PHONE,
   YGCSM_SYS_CUSTOMER_CURR.CUST_FAX,
   YGCSM_SYS_CUSTOMER_CURR.CUST_URL,
   YGCSM_SYS_CUSTOMER_CURR.SERVICE_NUM,
   YGCSM_SYS_CUSTOMER_CURR.DEPT_ID,
   YGCSM_SYS_CUSTOMER_CURR.SERVICE_PERSON_ID,
   YGCSM_SYS_CUSTOMER_CURR.SERVICE_PERSON_TITLE,
   YGCSM_SYS_CUSTOMER_CURR.CUST_LEVEL,
   YGCSM_SYS_CUSTOMER_CURR.P_CUSTNO,
   YGCSM_SYS_CUSTOMER_CURR.P_CUST_NAME,
   YGCSM_SYS_CUSTOMER_CURR.REMARK,
   YGCSM_SYS_CUSTOMER_CURR.IS_STOP,
   YGCSM_SYS_CUSTOMER_CURR.PREV_NO,
   YGCSM_SYS_CUSTOMER_CURR.CUST_UID,
   YGCSM_SYS_CUSTOMER_CURR.CUST_CODE,
   YGCSM_SYS_CUSTOMER_CURR.CUST_CODE2,
   YGCSM_SYS_CUSTOMER_CURR.CREATE_TIME,
   YGCSM_SYS_CUSTOMER_CURR.MODIFY_TIME,
   YGCSM_SYS_CUSTOMER_CURR.MODIFY_USER_ID,
   YGCSM_SYS_CUSTOMER_CURR.MODIFY_USER,
   YGCSM_SYS_CUSTOMER_CURR.AUDIT_TIME,
   YGCSM_SYS_CUSTOMER_CURR.AUDIT_USER,
   YGCSM_SYS_CUSTOMER_CURR.AUDIT_MESS,
   YGCSM_SYS_CUSTOMER_CURR.STAMP,
   YGCSM_SYS_CUSTOMER_CURR.AUDIT_STATE,
   YGCSM_SYS_CUSTOMER_CURR.HASCHILD
   FROM YGCSM_SYS_CUSTOMER_CURR
   <dynamic prepend="WHERE">
    <isNotNull prepend="AND" property="CUST_NO">
     YGCSM_SYS_CUSTOMER_CURR.CUST_NO =#CUST_NO#
    </isNotNull>
   </dynamic>
  </select>

    <!--SELECT   DISTINCT
    CUST_TYPE
    FROM      (
    SELECT    YGCSM_SYS_ENUM_ITEM_YGSL.ITEM_TITLE AS CUST_TYPE
    FROM      YGCSM_SYS_ENUM_ITEM_YGSL
    WHERE     ITEM_TYPE= 11   ) AS bbb
    WHERE     CUST_TYPE IS NOT NULL-->


    <!--查询用户小类（大类）-->
    <select id="SelectForReportTableEnum_YGSL" parameterClass="System.Collections.IDictionary" resultClass="Global_YGCSM_SYS_CUSTOMER_CURR"  notUseDBParameter="true">
      SELECT    YGCSM_SYS_ENUM_ITEM_YGSL.ITEM_TITLE AS CUST_TYPE
      FROM      YGCSM_SYS_ENUM_ITEM_YGSL
      <dynamic prepend="WHERE">
        <isNotNull prepend="AND" property="ITEM_TYPE">ITEM_TYPE =#ITEM_TYPE#</isNotNull>
      </dynamic>
    </select>

    <!--查询客户分级枚举-->
    <select id="SelectForReportTableEnum" parameterClass="System.Collections.IDictionary" resultClass="Global_YGCSM_SYS_CUSTOMER_CURR" notUseDBParameter="true"  >


      SELECT  YGCSM_SYS_ENUM_ITEM.ITEM_TITLE  AS CUST_TYPE
      FROM    YGCSM_SYS_ENUM_ITEM
      <dynamic prepend="WHERE">
        <isNotNull prepend="AND" property="ITEM_TYPE">ITEM_TYPE =#ITEM_TYPE#</isNotNull>
      </dynamic>

    </select>
   

    <!--客户情况列表 （报表） 按集团（用户小类）-->
    <select id="SelectForReportTableGroup" parameterClass="System.Collections.IDictionary" resultClass="Global_YGCSM_SYS_CUSTOMER_CURR" notUseDBParameter="true">
      <autoMakePersistSql table="Global_YGCSM_SYS_CUSTOMER_CURR" keys="CUST_NO" notUpdateColumns="" />
      SELECT    PROVINCE = ( SELECT    YGCSM_SYS_ENUM_ITEM_YGSL.ITEM_TITLE
      FROM      YGCSM_SYS_ENUM_ITEM_YGSL
      WHERE     ITEM_ID = TempCU.PROVINCE
      ),
      CUST_TYPE = ( SELECT  YGCSM_SYS_ENUM_ITEM_YGSL.ITEM_TITLE
      FROM    YGCSM_SYS_ENUM_ITEM_YGSL
      WHERE   ITEM_ID = TempCU.CUST_TYPE2
      )

      from

      (($Global_YGCSM_SYS_CUSTOMER_CURR_SelectForReportTable$))   as   TempCU

    </select>
    <select id="SelectForReportTableCompagesGroup" parameterClass="System.Collections.IDictionary" resultClass="Global_YGCSM_SYS_CUSTOMER_CURR" >
      declare   @sql   varchar(8000)
      set   @sql   =   'select   PROVINCE as 省份,'
      select   @sql   =   @sql   +   'sum(case   CUST_TYPE   when   '''+CUST_TYPE+'''
      then  1   else   0   end)   as   '''+CUST_TYPE+''','
      from   (select   distinct   CUST_TYPE   from    ($SelectForReportTableEnumYgsl$)   as   a  where CUST_TYPE is not Null ) as SelectPROVINCE
      select   @sql   =   left(@sql,len(@sql)-1)   +   '   from    ($SelectForReportTableGroup$) AS TempP   group   by   PROVINCE  '
    
      exec(@sql)
    </select>

 

 


    <!--客户情况列表 （报表） 按客户分级-->
    <select id="SelectForReportTableLevel" parameterClass="System.Collections.IDictionary" resultClass="Global_YGCSM_SYS_CUSTOMER_CURR" notUseDBParameter="true">
      <autoMakePersistSql table="Global_YGCSM_SYS_CUSTOMER_CURR" keys="CUST_NO" notUpdateColumns="" />

      SELECT    PROVINCE = ( SELECT    YGCSM_SYS_ENUM_ITEM_YGSL.ITEM_TITLE
      FROM      YGCSM_SYS_ENUM_ITEM_YGSL
      WHERE     ITEM_ID = TempCU.PROVINCE
      ),
      CUST_TYPE = ( SELECT  YGCSM_SYS_ENUM_ITEM_YGSL.ITEM_TITLE
      FROM    YGCSM_SYS_ENUM_ITEM_YGSL
      WHERE   ITEM_ID = TempCU.CUST_LEVEL
      )

      from

      (($Global_YGCSM_SYS_CUSTOMER_CURR_SelectForReportTable$))   as   TempCU

    </select>


    <select id="SelectForReportTableCompagesLevel" parameterClass="System.Collections.IDictionary" resultClass="Global_YGCSM_SYS_CUSTOMER_CURR" >
      declare   @sql   varchar(8000)
      set   @sql   =   'select   PROVINCE as 省份,'
      select   @sql   =   @sql   +   'sum(case   CUST_TYPE   when   '''+CUST_TYPE+'''
      then  1   else   0   end)   as   '''+CUST_TYPE+''','
      from   (select   distinct   CUST_TYPE   from    ($SelectForReportTableEnum$)   as   a  where CUST_TYPE is not Null ) as SelectPROVINCE
      select   @sql   =   left(@sql,len(@sql)-1)   +   '   from    ($SelectForReportTableLevel$) AS TempP   group   by   PROVINCE  '
   
      exec(@sql)
    </select>
   
   
   
   
 


    <!--客户情况列表 （报表） 按行业-->
    <select id="Global_YGCSM_SYS_CUSTOMER_CURR_SelectForReportTableWay" parameterClass="System.Collections.IDictionary" resultClass="Global_YGCSM_SYS_CUSTOMER_CURR" notUseDBParameter="true">
      <autoMakePersistSql table="Global_YGCSM_SYS_CUSTOMER_CURR" keys="CUST_NO" notUpdateColumns="" />


      SELECT    PROVINCE = ( SELECT    YGCSM_SYS_ENUM_ITEM_YGSL.ITEM_TITLE
      FROM      YGCSM_SYS_ENUM_ITEM_YGSL
      WHERE     ITEM_ID = TempCU.PROVINCE
      ),
      CUST_TYPE = ( SELECT  YGCSM_SYS_ENUM_ITEM_YGSL.ITEM_TITLE
      FROM    YGCSM_SYS_ENUM_ITEM_YGSL
      WHERE   ITEM_ID = TempCU.CUST_TYPE1
      )

      from

      (($Global_YGCSM_SYS_CUSTOMER_CURR_SelectForReportTable$))   as   TempCU

    </select>


    <select id="Global_YGCSM_SYS_CUSTOMER_CURR_SelectForReportTableCompagesWay" parameterClass="System.Collections.IDictionary" resultClass="Global_YGCSM_SYS_CUSTOMER_CURR" >
      declare   @sql   varchar(8000)
      set   @sql   =   'select   PROVINCE as 省份,'
      select   @sql   =   @sql   +   'sum(case   CUST_TYPE   when   '''+CUST_TYPE+'''
      then  1   else   0   end)   as   '''+CUST_TYPE+''','
      from   (select   distinct   CUST_TYPE   from    ($SelectForReportTableEnumYgsl$)   as   a  where CUST_TYPE is not Null ) as SelectPROVINCE
      select   @sql   =   left(@sql,len(@sql)-1)   +   '   from    ($Global_YGCSM_SYS_CUSTOMER_CURR_SelectForReportTableWay$) AS TempP   group   by   PROVINCE  '
     
      exec(@sql)

    </select>


    <!--客户情况列表 （报表）-->
    <select id="Global_YGCSM_SYS_CUSTOMER_CURR_SelectForReportTable" parameterClass="System.Collections.IDictionary" resultClass="Global_YGCSM_SYS_CUSTOMER_CURR" notUseDBParameter="true">
   <autoMakePersistSql table="Global_YGCSM_SYS_CUSTOMER_CURR" keys="CUST_NO" notUpdateColumns="" />


      SELECT Customer.CUST_NO,
      Customer.CUST_NAME,
      Customer.PROVINCE,
      Customer.CUST_TYPE1,
      Customer.CUST_TYPE2,

      Customer.CUST_LEVEL


      FROM YGCSM_SYS_CUSTOMER_CURR Customer

      INNER JOIN
      dbo.YGCSM_SYS_CUST_TO_ORG_CURR B ON Customer.CUST_NO=B.CUST_NO INNER JOIN
      ($CurrentOrgIDSqlQuery$) as DeptSql ON B.ORG_ID=DeptSql.OrgId <!--inner join  dbo.YGCSM_SYS_CUSTOMER_CURR C on C.CUST_NO=Customer.CUST_NO-->

   <dynamic prepend="WHERE">
    <isNotNull prepend="AND" property="CUST_NO">Customer.CUST_NO =#CUST_NO#</isNotNull>
    <isNotNull prepend="AND" property="PROVINCE">
          Customer.PROVINCE= #PROVINCE#<!-- IN (
          SELECT  YGCSM_SYS_ENUM_ITEM_YGSL.ITEM_ID
          FROM    YGCSM_SYS_ENUM_ITEM_YGSL
          WHERE   ITEM_TYPE )-->
        </isNotNull>

        <isNotNull prepend="AND" property="CUST_TYPE1">
          Customer.CUST_TYPE1=#CUST_TYPE1# <!-- IN (
          SELECT  YGCSM_SYS_ENUM_ITEM_YGSL.ITEM_ID
          FROM    YGCSM_SYS_ENUM_ITEM_YGSL
          WHERE   ITEM_TYPE )-->
        </isNotNull>
        <isNotNull prepend="AND" property="CUST_TYPE2">
          Customer.CUST_TYPE2=#CUST_TYPE2# <!-- IN (
          SELECT  YGCSM_SYS_ENUM_ITEM_YGSL.ITEM_ID
          FROM    YGCSM_SYS_ENUM_ITEM_YGSL
          WHERE   ITEM_TYPE )-->
        </isNotNull>
    <isNotNull prepend="AND" property="ORG_ID">
     DeptSql.OrgId =#ORG_ID#
    </isNotNull>
    <isNotNull prepend="AND" property="CUST_LEVEL">
       
          Customer.CUST_LEVEL  = #CUST_LEVEL# <!--IN (
          SELECT  YGCSM_SYS_ENUM_ITEM.ITEM_ID
          FROM    YGCSM_SYS_ENUM_ITEM
          WHERE   ITEM_TYPE)-->

        </isNotNull>
    
       
   </dynamic>
  </select>


  <insert id="Global_YGCSM_SYS_CUSTOMER_CURR_Insert" parameterClass="Global_YGCSM_SYS_CUSTOMER_CURR">
   INSERT INTO YGCSM_SYS_CUSTOMER_CURR(
   CUST_NO,
   CUST_NAME,
   PROVINCE,
   CUST_TYPE1,
   CUST_TYPE2,
   CUST_ADDR,
   CUST_POSTCODE,
   CUST_PHONE,
   CUST_FAX,
   CUST_URL,
   SERVICE_NUM,
   DEPT_ID,
   SERVICE_PERSON_ID,
   SERVICE_PERSON_TITLE,
   CUST_LEVEL,
   P_CUSTNO,
   P_CUST_NAME,
   REMARK,
   IS_STOP,
   PREV_NO,
   CUST_UID,
   CUST_CODE,
   CUST_CODE2,
   CREATE_TIME,
   MODIFY_TIME,
   MODIFY_USER_ID,
   MODIFY_USER,
   AUDIT_TIME,
   AUDIT_USER,
   AUDIT_MESS,
   STAMP,
   AUDIT_STATE,
   HASCHILD
   ) VALUES(
   #CUST_NO#,
   #CUST_NAME#,
   #PROVINCE#,
   #CUST_TYPE1#,
   #CUST_TYPE2#,
   #CUST_ADDR#,
   #CUST_POSTCODE#,
   #CUST_PHONE#,
   #CUST_FAX#,
   #CUST_URL#,
   #SERVICE_NUM#,
   #DEPT_ID#,
   #SERVICE_PERSON_ID#,
   #SERVICE_PERSON_TITLE#,
   #CUST_LEVEL#,
   #P_CUSTNO#,
   #P_CUST_NAME#,
   #REMARK#,
   #IS_STOP#,
   #PREV_NO#,
   #CUST_UID#,
   #CUST_CODE#,
   #CUST_CODE2#,
   #CREATE_TIME#,
   #MODIFY_TIME#,
   #MODIFY_USER_ID#,
   #MODIFY_USER#,
   #AUDIT_TIME#,
   #AUDIT_USER#,
   #AUDIT_MESS#,
   #STAMP#,
   #AUDIT_STATE#,
   #HASCHILD#)
  </insert>


  <update id="Global_YGCSM_SYS_CUSTOMER_CURR_Update" parameterClass="Global_YGCSM_SYS_CUSTOMER_CURR">
   UPDATE YGCSM_SYS_CUSTOMER_CURR
   SET CUST_NO=#CUST_NO#, CUST_NAME=#CUST_NAME#, PROVINCE=#PROVINCE#, CUST_TYPE1=#CUST_TYPE1#, CUST_TYPE2=#CUST_TYPE2#, CUST_ADDR=#CUST_ADDR#, CUST_POSTCODE=#CUST_POSTCODE#, CUST_PHONE=#CUST_PHONE#, CUST_FAX=#CUST_FAX#, CUST_URL=#CUST_URL#, SERVICE_NUM=#SERVICE_NUM#, DEPT_ID=#DEPT_ID#, SERVICE_PERSON_ID=#SERVICE_PERSON_ID#, SERVICE_PERSON_TITLE=#SERVICE_PERSON_TITLE#, CUST_LEVEL=#CUST_LEVEL#, P_CUSTNO=#P_CUSTNO#, P_CUST_NAME=#P_CUST_NAME#, REMARK=#REMARK#, IS_STOP=#IS_STOP#, PREV_NO=#PREV_NO#, CUST_UID=#CUST_UID#, CUST_CODE=#CUST_CODE#, CUST_CODE2=#CUST_CODE2#, CREATE_TIME=#CREATE_TIME#, MODIFY_TIME=#MODIFY_TIME#,MODIFY_USER_ID=#MODIFY_USER_ID#, MODIFY_USER=#MODIFY_USER#, AUDIT_TIME=#AUDIT_TIME#, AUDIT_USER=#AUDIT_USER#, AUDIT_MESS=#AUDIT_MESS#, STAMP=#STAMP#, AUDIT_STATE=#AUDIT_STATE#,HASCHILD=#HASCHILD#
   WHERE CUST_NO  =#CUST_NO#

  </update>


  <delete id="Global_YGCSM_SYS_CUSTOMER_CURR_Delete" parameterClass="System.Collections.IDictionary">
   DELETE FROM  YGCSM_SYS_CUSTOMER_CURR
   WHERE CUST_NO  =#CUST_NO#
  </delete>

 

 


  <!--客户列表（客户资料录入） 默认 主键-->
  <select id="Global_YGCSM_SYS_CUSTOMER_CURR_SelectForCustListImportID" parameterClass="System.Collections.IDictionary" resultClass="Global_YGCSM_SYS_CUSTOMER" notUseDBParameter="true">
   <isNotNull property="OrderByAuditState">
    select top #count# * from
    (
   </isNotNull>
   SELECT A.CUST_NO,A.AUDIT_STATE
   FROM dbo.YGCSM_SYS_CUSTOMER_CURR A
   WHERE A.AUDIT_STATE=0 AND A.MODIFY_USER_ID=#userID#
   <include refid="CustListForImportCondition"></include>
   UNION
   SELECT DISTINCT A.CUST_NO,A.AUDIT_STATE
   FROM dbo.YGCSM_SYS_CUSTOMER_CURR A INNER JOIN
   dbo.YGCSM_SYS_CUST_TO_ORG_CURR B ON A.CUST_NO=B.CUST_NO INNER JOIN
   ($DeptSql$) as DeptSql ON B.ORG_ID=DeptSql.OrgId
   WHERE A.AUDIT_STATE=1 AND A.IS_STOP=0
   <include refid="CustListForImportCondition"></include>
   <isNotNull property="OrderByAuditState">
    ) A order by AUDIT_STATE
   </isNotNull>
  </select>

  <!--客户列表（客户资料录入） 全部，全部 主键-->
  <!--客户列表（客户资料录入） 未停用/已停用，全部 主键-->
  <select id="Global_YGCSM_SYS_CUSTOMER_CURR_SelectForCustListImportID_NoAduit" parameterClass="System.Collections.IDictionary" resultClass="Global_YGCSM_SYS_CUSTOMER" notUseDBParameter="true">
   <isNotNull property="OrderByAuditState">
    select top #count# * from
    (
   </isNotNull>
   SELECT A.CUST_NO,A.AUDIT_STATE
   FROM dbo.YGCSM_SYS_CUSTOMER_CURR A
   WHERE  A.MODIFY_USER_ID=#userID# AND A.AUDIT_STATE=0
   <isNotNull prepend="AND" property="IS_STOP">A.IS_STOP=#IS_STOP# </isNotNull>
   <include refid="CustListForImportCondition"></include>
   UNION
   SELECT DISTINCT A.CUST_NO,A.AUDIT_STATE
   FROM dbo.YGCSM_SYS_CUSTOMER_CURR A INNER JOIN
   dbo.YGCSM_SYS_CUST_TO_ORG_CURR B ON A.CUST_NO=B.CUST_NO INNER JOIN
   ($DeptSql$) as DeptSql ON B.ORG_ID=DeptSql.OrgId
   WHERE A.AUDIT_STATE=1
   <isNotNull prepend="AND" property="IS_STOP">A.IS_STOP=#IS_STOP# </isNotNull>
   <include refid="CustListForImportCondition"></include>
   <isNotNull property="OrderByAuditState">
    ) A order by AUDIT_STATE
   </isNotNull>
  </select>
  <!--1,客户列表（客户资料录入） 未审核,全部  主键-->
  <!--1,客户列表（客户资料录入） 未审核,未停用/已停用  主键-->
  <select id="Global_YGCSM_SYS_CUSTOMER_CURR_SelectForCustListImportID_NoStopNoAudit" parameterClass="System.Collections.IDictionary" resultClass="Global_YGCSM_SYS_CUSTOMER" notUseDBParameter="true">
   <isNotNull property="OrderByAuditState">
    select top #count# * from
    (
   </isNotNull>
   SELECT A.CUST_NO,A.AUDIT_STATE
   FROM dbo.YGCSM_SYS_CUSTOMER_CURR A
   WHERE A.MODIFY_USER_ID=#userID#
   <isNotNull prepend="AND" property="AUDIT_STATE">A.AUDIT_STATE=#AUDIT_STATE# </isNotNull>
   <isNotNull prepend="AND" property="IS_STOP">A.IS_STOP=#IS_STOP# </isNotNull>
   <include refid="CustListForImportCondition"></include>
   <isNotNull property="OrderByAuditState">
    ) A order by AUDIT_STATE
   </isNotNull>
  </select>
  <!--1,客户列表（客户资料录入） 已审核,未停用/已停用  主键-->
  <!--客户列表（客户资料审核） 未审核/已审核/全部，未停用/已停用/全部 主键-->
  <select id="Global_YGCSM_SYS_CUSTOMER_CURR_SelectForCustListImportID_Common" parameterClass="System.Collections.IDictionary" resultClass="Global_YGCSM_SYS_CUSTOMER" notUseDBParameter="true">
   <isNotNull property="OrderByAuditState">
    select top #count# * from
    (
   </isNotNull>
   SELECT DISTINCT A.CUST_NO,A.AUDIT_STATE
   FROM dbo.YGCSM_SYS_CUSTOMER_CURR A INNER JOIN
   dbo.YGCSM_SYS_CUST_TO_ORG_CURR B ON A.CUST_NO=B.CUST_NO INNER JOIN
   ($DeptSql$) as DeptSql ON B.ORG_ID=DeptSql.OrgId
   <dynamic prepend="where">
    <isNotNull property="AUDIT_STATE" prepend="AND">A.AUDIT_STATE=#AUDIT_STATE#</isNotNull>
    <isNotNull property="IS_STOP" prepend="AND">A.IS_STOP=#IS_STOP#</isNotNull>
   </dynamic>
   <include refid="CustListForImportCondition"></include>
   <isNotNull property="OrderByAuditState">
    ) A order by AUDIT_STATE
   </isNotNull>
  </select>

  <!--客户列表（客户资料审核） 默认 主键-->
  <select id="Global_YGCSM_SYS_CUSTOMER_CURR_SelectForCustListAuditID" parameterClass="System.Collections.IDictionary" resultClass="Global_YGCSM_SYS_CUSTOMER" notUseDBParameter="true">
   <isNotNull property="OrderByAuditState">
    select top #count# * from
    (
   </isNotNull>
   SELECT DISTINCT A.CUST_NO,A.AUDIT_STATE
   FROM dbo.YGCSM_SYS_CUSTOMER_CURR A INNER JOIN
   dbo.YGCSM_SYS_CUST_TO_ORG_CURR B ON A.CUST_NO=B.CUST_NO INNER JOIN
   ($DeptSql$) as DeptSql ON B.ORG_ID=DeptSql.OrgId
   WHERE A.IS_STOP=0
   <include refid="CustListForImportCondition"></include>
   <isNotNull property="OrderByAuditState">
    ) A order by AUDIT_STATE
   </isNotNull>
  </select>

 

  <!--2, 客户列表（客户资料录入） 未停用,已审核  主键-->
  <!--
  <select id="Global_YGCSM_SYS_CUSTOMER_CURR_SelectForCustListImportID_NoStopAudit" parameterClass="System.Collections.IDictionary" resultClass="Global_YGCSM_SYS_CUSTOMER" notUseDBParameter="true">
   SELECT DISTINCT A.CUST_NO,A.AUDIT_STATE
   FROM dbo.YGCSM_SYS_CUSTOMER_CURR A INNER JOIN
   dbo.YGCSM_SYS_CUST_TO_ORG_CURR B ON A.CUST_NO=B.CUST_NO INNER JOIN
   ($DeptSql$) as DeptSql ON B.ORG_ID=DeptSql.OrgId
   WHERE A.AUDIT_STATE=1  AND A.IS_STOP=0
   <include refid="CustListForImportCondition"></include>
  </select>
  -->
  <!--3,客户列表（客户资料录入） 已停用  主键-->
  <!--
  <select id="Global_YGCSM_SYS_CUSTOMER_CURR_SelectForCustListImportID_Stop" parameterClass="System.Collections.IDictionary" resultClass="Global_YGCSM_SYS_CUSTOMER" notUseDBParameter="true">
   SELECT DISTINCT A.CUST_NO,A.AUDIT_STATE
   FROM dbo.YGCSM_SYS_CUSTOMER_CURR A INNER JOIN
   dbo.YGCSM_SYS_CUST_TO_ORG_CURR B ON A.CUST_NO=B.CUST_NO INNER JOIN
   ($DeptSql$) as DeptSql ON B.ORG_ID=DeptSql.OrgId
   WHERE A.AUDIT_STATE=1  AND A.IS_STOP=1
   <include refid="CustListForImportCondition"></include>
  </select>-->

 

  <!--1,客户列表（客户资料审核） 未停用，未审核  主键-->
  <!--<select id="Global_YGCSM_SYS_CUSTOMER_CURR_SelectForCustListAuditID_NoStopNoAudit" parameterClass="System.Collections.IDictionary" resultClass="Global_YGCSM_SYS_CUSTOMER" notUseDBParameter="true">
   SELECT DISTINCT A.CUST_NO,A.AUDIT_STATE
   FROM dbo.YGCSM_SYS_CUSTOMER_CURR A INNER JOIN
   dbo.YGCSM_SYS_CUST_TO_ORG_CURR B ON A.CUST_NO=B.CUST_NO INNER JOIN
   ($DeptSql$) as DeptSql ON B.ORG_ID=DeptSql.OrgId
   WHERE A.IS_STOP=0 AND A.AUDIT_STATE=0
   <include refid="CustListForImportCondition"></include>
  </select>-->
  <!--2, 客户列表（客户资料审核） 未停用,已审核  主键    同 客户列表（客户资料录入） 未停用,已审核-->
  <!--3,客户列表（客户资料录入） 已停用  主键  同   客户列表（客户资料录入） 已停用  主键-->

 

  <!--客户列表（客户资料录入） 详细资料-->
  <select id="Global_YGCSM_SYS_CUSTOMER_CURR_SelectForCustListImport" parameterClass="System.Collections.IDictionary" resultClass="Global_YGCSM_SYS_CUSTOMER">
   SELECT
   A.CUST_NO,
   A.CUST_NAME,
   B.ITEM_TITLE AS PROVINCE,
   C.ITEM_TITLE AS CUST_TYPE1,
   D.ITEM_TITLE AS CUST_TYPE2,
   A.CUST_ADDR,
   A.CUST_POSTCODE,
   A.CUST_PHONE,
   A.CUST_FAX,
   A.CUST_URL,
   A.SERVICE_NUM,
   E.ORG_NAME AS DEPT_ID,
   A.SERVICE_PERSON_TITLE,
   F.ITEM_TITLE AS CUST_LEVEL,
   A.P_CUST_NAME,
   CASE WHEN (A.AUDIT_STATE=1) THEN '已审核' ELSE '未审核' END AS AUDIT_STATE,
   A.IS_STOP,
   A.REMARK
   FROM dbo.YGCSM_SYS_CUSTOMER_CURR A
   <isNotNull  property="CoreSql">
    INNER JOIN
    ($CoreSql$) AS KeySql ON A.CUST_NO=KeySql.CUST_NO
   </isNotNull>
   LEFT JOIN
   dbo.YGCSM_SYS_ENUM_ITEM_YGSL B ON A.PROVINCE=B.ITEM_ID LEFT JOIN
   dbo.YGCSM_SYS_ENUM_ITEM_YGSL C ON A.CUST_TYPE1=C.ITEM_ID LEFT JOIN
   dbo.YGCSM_SYS_ENUM_ITEM_YGSL D ON A.CUST_TYPE2=D.ITEM_ID LEFT JOIN
   dbo.EP_ORG_BASE E ON A.DEPT_ID=E.ORG_ID LEFT JOIN
   dbo.YGCSM_SYS_ENUM_ITEM F ON A.CUST_LEVEL=F.ITEM_ID
   order by AUDIT_STATE
  </select>
  <!--客户列表（客户资料录入） 查询条件-->
  <sql id="CustListForImportCondition" >
   <!--<dynamic prepend="WHERE">-->
   <!--<isNotNull prepend="AND" property="IS_STOP">A.IS_STOP =#IS_STOP#</isNotNull>
    <isNotNull prepend="AND" property="AUDIT_STATE">A.AUDIT_STATE =#AUDIT_STATE#</isNotNull>-->
   <isNotNull prepend="AND" property="CUST_NOS">A.CUST_NO IN ($CUST_NOS$)</isNotNull>
   <isNotNull prepend="AND" property="SERVICE_NUM">A.SERVICE_NUM=#SERVICE_NUM#</isNotNull>
   <isNotNull prepend="AND" property="PROVINCE">A.PROVINCE=#PROVINCE#</isNotNull>
   <isNotNull prepend="AND" property="DEPT_ID">A.DEPT_ID=#DEPT_ID#</isNotNull>
   <isNotNull prepend="AND" property="SERVICE_PERSON_ID">A.SERVICE_PERSON_ID=#SERVICE_PERSON_ID#</isNotNull>
   <isNotNull prepend="AND" property="CUST_TYPE1">A.CUST_TYPE1=#CUST_TYPE1#</isNotNull>
   <isNotNull prepend="AND" property="CUST_TYPE2">A.CUST_TYPE2=#CUST_TYPE2#</isNotNull>
   <isNotNull prepend="AND" property="CUST_POSTCODE">A.CUST_POSTCODE like '%$CUST_POSTCODE$%'</isNotNull>
   <isNotNull prepend="AND" property="CUST_FAX">A.CUST_FAX like '%$CUST_FAX$%'</isNotNull>
   <isNotNull prepend="AND" property="CUST_ADDR">A.CUST_ADDR like '%$CUST_ADDR$%'</isNotNull>
   <isNotNull prepend="AND" property="CUST_PHONE">A.CUST_PHONE like '%$CUST_PHONE$%'</isNotNull>
   <isNotNull prepend="AND" property="CUST_LEVELS">A.CUST_LEVEL IN ($CUST_LEVELS$)</isNotNull>
   <!--</dynamic>-->
  </sql>
  <!--客户列表综合查询-->
  <select id="Global_YGCSM_SYS_CUSTOMER_CURR_SelectForCustColligateSearch" parameterClass="System.Collections.IDictionary" resultClass="Global_YGCSM_SYS_CUSTOMER" notUseDBParameter="true">
   SELECT DISTINCT A.CUST_NO
   FROM dbo.YGCSM_SYS_CUSTOMER_CURR A INNER JOIN
   dbo.YGCSM_SYS_CUST_TO_ORG_CURR B ON A.CUST_NO=B.CUST_NO INNER JOIN
   ($DeptSql$) as DeptSql ON B.ORG_ID=DeptSql.OrgId
   <isNotNull property="YGCSM_SYS_LINKMAN">LEFT JOIN dbo.YGCSM_SYS_LINKMAN G ON A.CUST_NO=G.CUST_NO</isNotNull>
   <isNotNull property="YGCSM_SALE_MX"> LEFT JOIN dbo.YGCSM_SALE_MX H ON A.CUST_NO=H.SALECUS_ID</isNotNull>
   <isNotNull property="YGCSM_CUST_AUTH"> LEFT JOIN dbo.YGCSM_CUST_AUTH I ON A.CUST_NO=I.CUST_NO</isNotNull>
   <isNotNull property="YGCSM_SVC_ALLOT_WORK">left join dbo.YGCSM_SVC_ALLOT_WORK allot on A.CUST_NO=allot.CUST_ID</isNotNull>
   <isNotNull property="YGCSM_SVC_LIST">
    left join dbo.YGCSM_SVC_LIST svcList on a.cust_no=svcList.CUST_ID
    <isNotNull property="YGCSM_SVC_LOCALE">
     left join dbo.YGCSM_SVC_LOCALE locale on svcList.SVC_ID=locale.SVC_ID
    </isNotNull>
    <isNotNull property="YGCSM_SVC_NOLOCALE">
     left join dbo.YGCSM_SVC_NOLOCALE nolocale on svcList.SVC_ID=nolocale.SVC_ID
    </isNotNull>
   </isNotNull>
   <isNotNull property="YGCSM_SVC_RETURN"> left join dbo.YGCSM_SVC_RETURN svcReturn on a.cust_no=svcReturn.CUST_ID</isNotNull>
   <include refid="CustColligateSearchCondition"></include>
  </select>
  <!--客户列表综合查询条件-->
  <sql id="CustColligateSearchCondition">
   <dynamic prepend="WHERE">
    <!--客户资料-->
    <isNotNull prepend="AND" property="IS_STOP">A.IS_STOP=#IS_STOP#</isNotNull>
    <isNotNull prepend="AND" property="AUDIT_STATE">A.AUDIT_STATE=#AUDIT_STATE#</isNotNull>
    <isNotNull prepend="AND" property="CUST_NOS">A.CUST_NO IN ($CUST_NOS$)</isNotNull>
    <isNotNull prepend="AND" property="DEPT_ID">A.DEPT_ID=#DEPT_ID#</isNotNull>
    <isNotNull prepend="AND" property="CUST_TYPE1">A.CUST_TYPE1=#CUST_TYPE1#</isNotNull>
    <isNotNull prepend="AND" property="CUST_LEVELS">A.CUST_LEVEL IN ($CUST_LEVELS$)</isNotNull>
    <isNotNull prepend="AND" property="SERVICE_NUM">A.SERVICE_NUM='$SERVICE_NUM$'</isNotNull>
    <isNotNull prepend="AND" property="SERVICE_PERSON_ID">A.SERVICE_PERSON_ID=#SERVICE_PERSON_ID#</isNotNull>
    <isNotNull prepend="AND" property="CUST_TYPE2">A.CUST_TYPE2=#CUST_TYPE2#</isNotNull>
    <isNotNull prepend="AND" property="PROVINCE">A.PROVINCE=#PROVINCE#</isNotNull>
    <isNotNull prepend="AND" property="CUST_POSTCODE">A.CUST_POSTCODE like '%$CUST_POSTCODE$%'</isNotNull>
    <isNotNull prepend="AND" property="CUST_FAX">A.CUST_FAX like '%$CUST_FAX$%'</isNotNull>
    <isNotNull prepend="AND" property="CUST_PHONE">A.CUST_PHONE like '%$CUST_PHONE$%'</isNotNull>
    <isNotNull prepend="AND" property="CUST_ADDR">A.CUST_ADDR like '%$CUST_ADDR$%'</isNotNull>
    <!--联系人-->
    <isNotNull prepend="AND" property="PERSON">G.PERSON like '%$PERSON$%'</isNotNull>
    <isNotNull prepend="AND" property="LINK_DEFAULT">G.LINK_DEFAULT=#LINK_DEFAULT#</isNotNull>
    <isNotNull prepend="AND" property="LINKMAN_DUTY">G.LINKMAN_DUTY LIKE '%$LINKMAN_DUTY$%'</isNotNull>
    <isNotNull prepend="AND" property="PHONE">G.PHONE LIKE '%$PHONE$%'</isNotNull>
    <isNotNull prepend="AND" property="DEPT_NAME">G.DEPT_NAME LIKE '%$DEPT_NAME$%'</isNotNull>
    <isNotNull prepend="AND" property="LINKEMAN_POST">G.LINKEMAN_POST LIKE '%$LINKEMAN_POST$%'</isNotNull>
    <isNotNull prepend="AND" property="MOBILEPHONE">G.MOBILEPHONE LIKE '%$MOBILEPHONE$%'</isNotNull>
    <!--产品查询条件-->
    <isNotNull prepend="AND" property="ORDER_DATE_BEGIN">H.ORDER_DATE <![CDATA[>=]]> #ORDER_DATE_BEGIN#</isNotNull>
    <isNotNull prepend="AND" property="ORDER_DATE_END">H.ORDER_DATE <![CDATA[<=]]> #ORDER_DATE_END#</isNotNull>
    <isNotNull prepend="AND" property="SOFT_TYPE1">H.SOFT_TYPE1=#SOFT_TYPE1#</isNotNull>
    <isNotNull prepend="AND" property="SL_MONEY_MIN">H.SL_MONEY<![CDATA[>=]]>#SL_MONEY_MIN#</isNotNull>
    <isNotNull prepend="AND" property="SL_MONEY_MAX">H.SL_MONEY<![CDATA[<=]]>#SL_MONEY_MAX#</isNotNull>
    <isNotNull prepend="AND" property="PRODUCT_HAS_MAINTAIN">H.SERVICE_FREETIME > 0</isNotNull>
    <isNotNull prepend="AND" property="PRODUCT_NOTHAS_MAINTAIN">H.SERVICE_FREETIME = 0</isNotNull>
    <isNotNull prepend="AND" property="SOFT_TYPE2">H.SOFT_TYPE2=#SOFT_TYPE2#</isNotNull>
    <isNotNull prepend="AND" property="BUSINESS">H.BUSINESS=#BUSINESS#</isNotNull>
    <isNotNull prepend="AND" property="SERVICE_FREETIME_BEGIN">H.SERVICE_FREETIME<![CDATA[>=]]>#SERVICE_FREETIME_BEGIN#</isNotNull>
    <isNotNull prepend="AND" property="SERVICE_FREETIME_END">H.SERVICE_FREETIME<![CDATA[<=]]>#SERVICE_FREETIME_END#</isNotNull>
    <!--产品授权条件-->
    <isNotNull prepend="AND" property="AUTH_IS_STOP">I.IS_STOP=#AUTH_IS_STOP#</isNotNull>
    <isNotNull prepend="AND" property="AUTH_NOTHAS_MAINTAIN">I.AUTH_NO_COUNT = 0</isNotNull>
    <isNotNull prepend="AND" property="AUTH_HAS_MAINTAIN">I.AUTH_NO_COUNT <![CDATA[>]]> 0</isNotNull>
    <isNotNull prepend="AND" property="AUTH_SOFT_TYPE2">I.SOFT_TYPE2=#AUTH_SOFT_TYPE2#</isNotNull>
    <isNotNull prepend="AND" property="SOFTDOG_NO">I.SOFTDOG_NO='$SOFTDOG_NO$'</isNotNull>

    <!--服务活动查询条件-->
    <isNotNull prepend="AND" property="SVC_TYPE">allot.SVC_TYPE=#SVC_TYPE#</isNotNull>
    <!--服务类型-->
    <isNotNull prepend="AND" property="ALLOT_NUM">allot.ALLOT_NUM=#ALLOT_NUM#</isNotNull>
    <!--单据编号-->
    <isNotNull prepend="AND" property="DUTY_DEPT">allot.DUTY_DEPT=#DUTY_DEPT#</isNotNull>
    <!--责任部门-->
    <isNotNull prepend="AND" property="DUTY_ID">allot.DUTY_ID=#DUTY_ID#</isNotNull>
    <!--服务责任人员-->


    <isNotNull prepend="AND" property="MAIN_SOFT">svcList.MAIN_SOFT=#MAIN_SOFT#</isNotNull>
    <!--主要软产品-->
    <isNotNull prepend="AND" property="OTHER_SOFT">svcList.OTHER_SOFT like '%$MAIN_SOFT$%'</isNotNull>
    <!--其他软产品-->
    <isNotNull prepend="AND" property="CUST_LINKMAN">svcList.CUST_LINKMAN=#CUST_LINKMAN#</isNotNull>
    <!--联系人-->
    <isNotNull prepend="AND" property="AUDIT_MAN">svcList.AUDIT_MAN=#AUDIT_MAN#</isNotNull>
    <!--审核人-->
    <isNotNull prepend="AND" property="SVC_MODE">svcList.SVC_MODE=#SVC_MODE#</isNotNull>
    <!--服务方式-->
    <isNotNull prepend="AND" property="NEED_RETURN">svcList.NEED_RETURN=#NEED_RETURN#</isNotNull>
    <!--需要回访-->
    <isNotNull prepend="AND" property="REPOSITORY_Y">svcList.REPOSITORY>0</isNotNull>
    <!--推荐至知识库-->
    <isNotNull prepend="AND" property="REPOSITORY_N">svcList.REPOSITORY<![CDATA[<=]]>0 </isNotNull>
    <!--不推荐至知识库-->

    <isNotNull prepend="AND" property="RETURN_DATE_BEGIN">svcReturn.RETURN_DATE<![CDATA[>=]]>#RETURN_DATE_BEGIN#</isNotNull>
    <!--回访开始日期-->
    <isNotNull prepend="AND" property="RETURN_DATE_END">svcReturn.RETURN_DATE<![CDATA[<=]]>#RETURN_DATE_END#</isNotNull>
    <!--回访结束日期-->
    <isNotNull prepend="AND" property="RETURN_MAN">svcReturn.RETURN_MAN=#RETURN_MAN#</isNotNull>
    <!--回访人-->

    <isNotNull prepend="AND" property="ALLOT_DATE_BEGIN">allot.ALLOT_DATE<![CDATA[>=]]>#ALLOT_DATE_BEGIN#</isNotNull>
    <!--派工开始日期-->
    <isNotNull prepend="AND" property="ALLOT_DATE_END">allot.ALLOT_DATE<![CDATA[<=]]>#ALLOT_DATE_END#</isNotNull>
    <!--派工结束日期-->
    <isNotNull prepend="AND" property="SVC_BEGDATE_BEGIN">svcList.SVC_BEGDATE<![CDATA[>=]]>#SVC_BEGDATE_BEGIN#</isNotNull>
    <!--服务开始日期-->
    <isNotNull prepend="AND" property="SVC_BEGDATE_END">svcList.SVC_BEGDATE<![CDATA[<=]]>#SVC_BEGDATE_END#</isNotNull>
    <!--服务开始日期-->
    <isNotNull prepend="AND" property="SVC_ENDDATE_BEGIN">locale.SVC_ENDDATE<![CDATA[>=]]>#SVC_ENDDATE_BEGIN#</isNotNull>
    <!--服务完成日期-->
    <isNotNull prepend="AND" property="SVC_ENDDATE_END">locale.SVC_ENDDATE<![CDATA[<=]]>#SVC_ENDDATE_END#</isNotNull>
    <!--服务完成日期-->
    <isNotNull prepend="AND" property="CREATOR_ID">allot.CREATOR_ID=#CREATOR_ID#</isNotNull>
    <!--创建人-->
    <isNotNull prepend="AND" property="WENTALONG_ID">allot.WENTALONG_ID like '%$WENTALONG_ID$%'</isNotNull>
    <!--同行服务人员-->
    
    
    <isNotNull prepend="AND" property="BILL_CREATYPE">nolocale.BILL_CREATYPE =#BILL_CREATYPE#</isNotNull>
    <!--单据创建类型-->
   </dynamic>
  </sql>
  <!--获取用户有部门权限的已审核通过的未停用的客户资料-->
  <statement id="Global_YGCSM_SYS_CUSTOMER_CURR_SelectForCustWithUserId"  parameterClass="System.Collections.IDictionary" resultClass="Global_YGCSM_SYS_CUSTOMER">
   exec SP_CUST_GetCustInfoByUserId @userId=#userId#,@auditState=#AUDIT_STATE#,@isStop=#IS_STOP#
  </statement>
  <!--依据指定客户下的用户有部门权限的已审核通过的未停用的客户资料-->
  <statement id="Global_YGCSM_SYS_CUSTOMER_CURR_GetSubCustInfoByUserIdAndPCustNo"  parameterClass="System.Collections.IDictionary" resultClass="Global_YGCSM_SYS_CUSTOMER">
   exec SP_CUST_GetSubCustInfoByUserIdAndPCustNo @userId=#userId#,@pCustNo=#pCustNo#,@auditState=#AUDIT_STATE#,@isStop=#IS_STOP#
  </statement>
  <!--获取指定客户资料-->
  <select id="Global_YGCSM_SYS_CUSTOMER_CURR_GetCustInfoByCustNo" parameterClass="System.Collections.IDictionary" resultClass="Global_YGCSM_SYS_CUSTOMER">
   exec SP_CUST_GetCustInfoByCustNo @custNo=#custNo#
  </select>
  <!--保存新客户-->
  <statement id="Global_YGCSM_SYS_CUSTOMER_CURR_SaveNewCust"  parameterClass="System.Collections.IDictionary">
   exec SP_CUST_SaveNewCust
   @custNo=#CUST_NO#,
   @custName=#CUST_NAME#,
   @province=#PROVINCE#,
   @custType1=#CUST_TYPE1#,
   @custType2=#CUST_TYPE2#,
   @custAddr=#CUST_ADDR#,
   @custPostCode=#CUST_POSTCODE#,
   @custPhone=#CUST_PHONE#,
   @custFax=#CUST_FAX#,
   @custUrl=#CUST_URL#,
   @serviceNum=#SERVICE_NUM#,
   @deptId=#DEPT_ID#,
   @servicePersonId=#SERVICE_PERSON_ID#,
   @servicePersonTitle=#SERVICE_PERSON_TITLE#,
   @custLevel=#CUST_LEVEL#,
   @pCustNo=#P_CUSTNO#,
   @pCustName=#P_CUST_NAME#,
   @remark=#REMARK#,
   @isStop=#IS_STOP#,
   @prevNo=#PREV_NO#,
   @modifyUserId=#MODIFY_USER_ID#,
   @modifyUser=#MODIFY_USER#,
   @hasAuditPerview=#hasAuditPerview#,
   @auditMess=#AUDIT_MESS#
  </statement>

  <!--保存修改了关键信息的客户资料-->
  <statement id="Global_YGCSM_SYS_CUSTOMER_CURR_SaveCustWithModifiedKeyInfo"  parameterClass="System.Collections.IDictionary">
   exec SP_CUST_SaveCustWithModifiedKeyInfo
   @oriCustNo=#ORI_CUST_NO#,
   @custNo=#CUST_NO#,
   @custName=#CUST_NAME#,
   @province=#PROVINCE#,
   @custType1=#CUST_TYPE1#,
   @custType2=#CUST_TYPE2#,
   @custAddr=#CUST_ADDR#,
   @custPostCode=#CUST_POSTCODE#,
   @custPhone=#CUST_PHONE#,
   @custFax=#CUST_FAX#,
   @custUrl=#CUST_URL#,
   @serviceNum=#SERVICE_NUM#,
   @deptId=#DEPT_ID#,
   @servicePersonId=#SERVICE_PERSON_ID#,
   @servicePersonTitle=#SERVICE_PERSON_TITLE#,
   @custLevel=#CUST_LEVEL#,
   @pCustNo=#P_CUSTNO#,
   @pCustName=#P_CUST_NAME#,
   @remark=#REMARK#,
   @isStop=#IS_STOP#,
   @prevNo=#PREV_NO#,
   @modifyUserId=#MODIFY_USER_ID#,
   @modifyUser=#MODIFY_USER#,
   @hasAuditPerview=#hasAuditPerview#,
   @oriPCustNo=#ORI_P_CUSTNO#,
   @auditMess=#AUDIT_MESS#
  </statement>


  <!--保存未修改关键信息的客户资料-->
  <statement id="Global_YGCSM_SYS_CUSTOMER_CURR_SaveCustWithNotModifiedKeyInfo"  parameterClass="System.Collections.IDictionary">
   exec SP_CUST_SaveCustWithNotModifiedKeyInfo
   @custNo=#CUST_NO#,
   @custName=#CUST_NAME#,
   @province=#PROVINCE#,
   @custType1=#CUST_TYPE1#,
   @custType2=#CUST_TYPE2#,
   @custAddr=#CUST_ADDR#,
   @custPostCode=#CUST_POSTCODE#,
   @custPhone=#CUST_PHONE#,
   @custFax=#CUST_FAX#,
   @custUrl=#CUST_URL#,
   @serviceNum=#SERVICE_NUM#,
   @deptId=#DEPT_ID#,
   @servicePersonId=#SERVICE_PERSON_ID#,
   @servicePersonTitle=#SERVICE_PERSON_TITLE#,
   @custLevel=#CUST_LEVEL#,
   @pCustNo=#P_CUSTNO#,
   @pCustName=#P_CUST_NAME#,
   @remark=#REMARK#,
   @isStop=#IS_STOP#,
   @prevNo=#PREV_NO#,
   @modifyUserId=#MODIFY_USER_ID#,
   @modifyUser=#MODIFY_USER#,
   @hasAuditPerview=#hasAuditPerview#,
   @auditMess=#AUDIT_MESS#
  </statement>
  <!--检查是否存在同名客户-->
  <statement id="Global_YGCSM_SYS_CUSTOMER_CURR_CheckCustNameExistsOrNot"  parameterClass="System.Collections.IDictionary">
   exec SP_CUST_CheckCustNameExistsOrNot @custName=#CUST_NAME#,@custNo=#CUST_NO#
  </statement>
  <!--判断客户是否已使用-->
  <statement id="Global_YGCSM_SYS_CUSTOMER_CURR_JudgeCustUsed"  parameterClass="System.Collections.IDictionary">
   exec SP_CUST_JudgeCustUsed @custNo=#CUST_NO#,@usedCount=#USED_COUNT#
  </statement>
  <!--获取客户所有曾使用名-->
  <statement id="Global_YGCSM_SYS_CUSTOMER_CURR_GetAllUsedCustName"  parameterClass="System.Collections.IDictionary">
   exec SP_CUST_GetAllUsedCustName @custNo=#CUST_NO#
  </statement>
  <!--客户更名保存操作-->
  <statement id="Global_YGCSM_SYS_CUSTOMER_CURR_SaveCustWithChangedCustName"  parameterClass="System.Collections.IDictionary">
   exec SP_CUST_SaveCustWithChangedCustName
   @custNo=#CUST_NO#,
   @newCustNo=#NEW_CUST_NO#,
   @newCustName=#NEW_CUST_NAME#,
   @modifyUserId=#MODIFY_USER_ID#,
   @modifyUserTitle=#MODIFY_USER_TITLE#,
   @hasAuditPerview=#HAS_AUDITPERVIEW#,
   @auditMess=#AUDIT_MESS#
  </statement>
  <!--删除指定客户-->
  <statement id="Global_YGCSM_SYS_CUSTOMER_CURR_DeleteCust"  parameterClass="System.Collections.IDictionary">
   exec SP_CUST_DeleteCust @custNo=#CUST_NO#
  </statement>
  <!--保存停用客户-->
  <statement id="Global_YGCSM_SYS_CUSTOMER_CURR_SaveStopCust"  parameterClass="System.Collections.IDictionary">
   exec SP_CUST_SaveStopCust
   @custNo=#CUST_NO#,
   @isStop=#IS_STOP#,
   @modifyUserId =#MODIFY_USERID#,
   @modifyUser=#MODIFT_USER#,
   @hasAuditPerview=#HAS_PERVIEW#,
   @auditMess=#AUDIT_MESS#
  </statement>
  <!--保存客户驳回信息-->
  <statement id="Global_YGCSM_SYS_CUSTOMER_CURR_SaveRejectCust"  parameterClass="System.Collections.IDictionary">
   exec SP_CUST_SaveRejectCust
   @custNo=#CUST_NO#,
   @auditUserTitle=#AUDIT_USER#,
   @auditMess=#AUDIT_MESS#
  </statement>
  <!--获取指定客户负责人邮箱-->
  <statement id="Global_YGCSM_SYS_CUSTOMER_CURR_GetUserEmailForCustNo"  parameterClass="System.Collections.IDictionary">
   exec SP_CUST_GetUserEmailForCustNo @custNo=#CUST_NO#
  </statement>
  <!--查看客户详细信息-->
  <statement id="Global_YGCSM_SYS_CUSTOMER_CURR_GetCustDetailByCustNo"  parameterClass="System.Collections.IDictionary">
   exec SP_CUST_GetCustDetailByCustNo @custNo=#custNo#
  </statement>
  <!--客户综合查看报表 与客户相关的所有信息-->
  <statement id="Global_YGCSM_SYS_CUSTOMER_CURR_GetAllInfoForCustReport"  parameterClass="System.Collections.IDictionary">
   exec SP_CUST_GetCustAllInfoForCustReport @custNo=#custNo#,@locale=#locale#,@nolocale=#nolocale#
  </statement>
 </statements>

</sqlMap>

 

 

 

2，

<?xml version="1.0" encoding="gb2312"?>
<sqlMap namespace="YGCSM_Global_Entitys" xmlns="http://ibatis.apache.org/mapping" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

 <!-- MAPPED alias -->
 <alias>
  <typeAlias alias="Global_YGCSM_SALE_MX" type="YGCSM.Global.Entitys.SaleMx, YGCSM.Global.Entitys" />
 </alias>

 <!-- MAPPED resultMaps -->
 <resultMaps>
  <resultMap id="Global_YGCSM_SALE_MX_Result" class="Global_YGCSM_SALE_MX">
   <result property="ORDER_MXNO" column="ORDER_MXNO" />
   <result property="ORDER_NO" column="ORDER_NO" />
   <result property="CUST_NO" column="CUST_NO" />
   <result property="SOFT_TYPE1" column="SOFT_TYPE1" />
   <result property="SOFT_TYPE1_TITLE" column="SOFT_TYPE1_TITLE" />
   <result property="SOFT_TYPE2" column="SOFT_TYPE2" />
   <result property="SOFT_TYPE2_TITILE" column="SOFT_TYPE2_TITILE" />
   <result property="VERSION_TYPE" column="VERSION_TYPE" />
   <result property="VERSION_TYPE_TITLE" column="VERSION_TYPE_TITLE" />
   <result property="LINK_COUNT" column="LINK_COUNT" />
   <result property="SL_MONEY" column="SL_MONEY" />
   <result property="ORIG_SOFT_TYPE2" column="ORIG_SOFT_TYPE2" />
   <result property="ORIG_SOFT_TYPE2_TITIEL" column="ORIG_SOFT_TYPE2_TITIEL" />
   <result property="ISDEL" column="ISDEL" />
   <result property="SALECUS_ID" column="SALECUS_ID" />
   <result property="SL_COUNT" column="SL_COUNT" />
   <result property="BUSINESS" column="BUSINESS" />
   <result property="BUSINESS_NAME" column="BUSINESS_NAME" />
   <result property="ORDER_DATE" column="ORDER_DATE" />
   <result property="SERVICE_FREETIME" column="SERVICE_FREETIME" />
   <result property="REMARK_SALE" column="REMARK_SALE" />
   <result property="SERVICE_NUM" column="SERVICE_NUM" />
   <result property="FREETIME_REMARKED" column="FREETIME_REMARKED" />
   <result property="SERVICE_TIME" column="SERVICE_TIME" />
   <result property="SERVICETIME_REMARKED" column="SERVICETIME_REMARKED" />
   <result property="REMARK_JC" column="REMARK_JC" />
   <result property="SALE_TYPE" column="SALE_TYPE" />
   <result property="PREV_NO" column="PREV_NO" />
  </resultMap>

 </resultMaps>

 <!-- MAPPED statements -->
 <statements>

  <select id="Global_YGCSM_SALE_MX_SelectForDataTableDefault" parameterClass="System.Collections.IDictionary" resultClass="Global_YGCSM_SALE_MX">
   <autoMakePersistSql table="YGCSM_SALE_MX" keys="ORDER_MXNO" notUpdateColumns="" />
   SELECT YGCSM_SALE_MX.ORDER_MXNO,
   YGCSM_SALE_MX.ORDER_NO,
   YGCSM_SALE_MX.CUST_NO,
   YGCSM_SALE_MX.SOFT_TYPE1,
   YGCSM_SALE_MX.SOFT_TYPE1_TITLE,
   YGCSM_SALE_MX.SOFT_TYPE2,
   YGCSM_SALE_MX.SOFT_TYPE2_TITILE,
   YGCSM_SALE_MX.VERSION_TYPE,
   YGCSM_SALE_MX.VERSION_TYPE_TITLE,
   YGCSM_SALE_MX.LINK_COUNT,
   YGCSM_SALE_MX.SL_MONEY,
   YGCSM_SALE_MX.ORIG_SOFT_TYPE2,
   YGCSM_SALE_MX.ORIG_SOFT_TYPE2_TITIEL,
   YGCSM_SALE_MX.ISDEL,
   YGCSM_SALE_MX.SALECUS_ID,
   YGCSM_SALE_MX.SL_COUNT,
   YGCSM_SALE_MX.BUSINESS,
   YGCSM_SALE_MX.BUSINESS_NAME,
   YGCSM_SALE_MX.ORDER_DATE,
   YGCSM_SALE_MX.SERVICE_FREETIME,
   YGCSM_SALE_MX.REMARK_SALE,
   YGCSM_SALE_MX.SERVICE_NUM,
   YGCSM_SALE_MX.FREETIME_REMARKED,
   YGCSM_SALE_MX.SERVICE_TIME,
   YGCSM_SALE_MX.SERVICETIME_REMARKED,
   YGCSM_SALE_MX.REMARK_JC,
   YGCSM_SALE_MX.SALE_TYPE,
   YGCSM_SALE_MX.PREV_NO

   FROM YGCSM_SALE_MX
   <dynamic prepend="WHERE">
    <isNotNull prepend="AND" property="ORDER_MXNO">YGCSM_SALE_MX.ORDER_MXNO =#ORDER_MXNO#</isNotNull>
   </dynamic>
  </select>


  <select id="Global_YGCSM_SALE_MX_SelectForEntityDefault" parameterClass="System.Collections.IDictionary" resultClass="Global_YGCSM_SALE_MX" notUseDBParameter="true">

   SELECT YGCSM_SALE_MX.ORDER_MXNO,
   YGCSM_SALE_MX.ORDER_NO,
   YGCSM_SALE_MX.CUST_NO,
   YGCSM_SALE_MX.SOFT_TYPE1,
   YGCSM_SALE_MX.SOFT_TYPE1_TITLE,
   YGCSM_SALE_MX.SOFT_TYPE2,
   YGCSM_SALE_MX.SOFT_TYPE2_TITILE,
   YGCSM_SALE_MX.VERSION_TYPE,
   YGCSM_SALE_MX.VERSION_TYPE_TITLE,
   YGCSM_SALE_MX.LINK_COUNT,
   YGCSM_SALE_MX.SL_MONEY,
   YGCSM_SALE_MX.ORIG_SOFT_TYPE2,
   YGCSM_SALE_MX.ORIG_SOFT_TYPE2_TITIEL,
   YGCSM_SALE_MX.ISDEL,
   YGCSM_SALE_MX.SALECUS_ID,
   YGCSM_SALE_MX.SL_COUNT,
   YGCSM_SALE_MX.BUSINESS,
   YGCSM_SALE_MX.BUSINESS_NAME,
   YGCSM_SALE_MX.ORDER_DATE,
   YGCSM_SALE_MX.SERVICE_FREETIME,
   YGCSM_SALE_MX.REMARK_SALE,
   YGCSM_SALE_MX.SERVICE_NUM,
   YGCSM_SALE_MX.FREETIME_REMARKED,
   YGCSM_SALE_MX.SERVICE_TIME,
   YGCSM_SALE_MX.SERVICETIME_REMARKED,
   YGCSM_SALE_MX.REMARK_JC,
   YGCSM_SALE_MX.SALE_TYPE,
   YGCSM_SALE_MX.PREV_NO

   FROM YGCSM_SALE_MX
   <dynamic prepend="WHERE">
    <isNotNull prepend="AND" property="ORDER_MXNO">
     YGCSM_SALE_MX.ORDER_MXNO =#ORDER_MXNO#
    </isNotNull>
   </dynamic>
  </select>


  <!-- 作为分页的来查询的几个主要几段 -->
  <select id="Global_YGCSM_SALE_MX_SelectForEntityPageList" parameterClass="System.Collections.IDictionary" resultClass="Global_YGCSM_SALE_MX" notUseDBParameter="true">
   SELECT DISTINCT sale.ORDER_MXNO, sale.SALECUS_ID,sale.SALE_TYPE,sale.ORDER_DATE

   FROM YGCSM_SALE_MX sale INNER JOIN
   dbo.YGCSM_SYS_CUST_TO_ORG_CURR B ON sale.SALECUS_ID=B.CUST_NO INNER JOIN
   ($CurrentOrgIDSqlQuery$) as DeptSql ON B.ORG_ID=DeptSql.OrgId
   <dynamic prepend="WHERE">

    <isNotNull prepend="AND" property="ORDER_MXNO">
     sale.ORDER_MXNO =#ORDER_MXNO#
    </isNotNull>
    <isNotNull prepend="AND" property="ISDEL">
     sale.ISDEL =#ISDEL#
    </isNotNull>
    <isNotNull prepend="AND" property="SALE_TYPE">
     sale.SALE_TYPE =#SALE_TYPE#
    </isNotNull>

    <isNotNull prepend="AND" property="CUST_NO">
     sale.CUST_NO =#CUST_NO#
    </isNotNull>
    <isNotNull prepend="AND" property="BUSINESS_NAME">
     sale.BUSINESS_NAME =#BUSINESS_NAME#
    </isNotNull>
    <isNotNull prepend="AND" property="SOFT_TYPE2_TITILE">
     sale.SOFT_TYPE2_TITILE =#SOFT_TYPE2_TITILE#
    </isNotNull>
    <isNotNull prepend="AND" property="ORDER_DATE1">
     sale.ORDER_DATE<![CDATA[>=]]>#ORDER_DATE1#
     <!--Sale.ORDER_DATE BETWEEN #START_DATE# AND #END_DATE#-->
    </isNotNull>
    <isNotNull prepend="AND" property="ORDER_DATE2">
     sale.ORDER_DATE<![CDATA[<=]]>#ORDER_DATE2#
     <!--Sale.ORDER_DATE BETWEEN #START_DATE# AND #END_DATE#-->
    </isNotNull>
    <isNotNull prepend="AND" property="SL_MONEY1">
     sale.SL_MONEY<![CDATA[>=]]>#SL_MONEY1#
     <!--Sale.ORDER_DATE BETWEEN #START_DATE# AND #END_DATE#-->
    </isNotNull>
    <isNotNull prepend="AND" property="SL_MONEY2">
     sale.SL_MONEY<![CDATA[<=]]>#SL_MONEY2#
     <!--Sale.ORDER_DATE BETWEEN #START_DATE# AND #END_DATE#-->
    </isNotNull>
    <isNotNull prepend="AND" property="SERVICE_FREETIMEDATE1">
     sale.SERVICE_FREETIME<![CDATA[>=]]>#SERVICE_FREETIMEDATE1#
     <!--Sale.ORDER_DATE BETWEEN #START_DATE# AND #END_DATE#-->
    </isNotNull>
    <isNotNull prepend="AND" property="SERVICE_FREETIMEDATE2">
     sale.SERVICE_FREETIME<![CDATA[<=]]>#SERVICE_FREETIMEDATE2#
     <!--Sale.ORDER_DATE BETWEEN #START_DATE# AND #END_DATE#-->
    </isNotNull>

    <isNotNull prepend="AND" property="SERVICE_FREETIME">
     sale.SERVICE_FREETIME= #SERVICE_FREETIME#
    </isNotNull>
    <isNotNull prepend="AND" property="SERVICE_FREETIMENot">
     sale.SERVICE_FREETIME<![CDATA[<>]]> #SERVICE_FREETIMENot#
    </isNotNull>
    <isNotNull prepend="AND" property="SYSISTOP">
     sale.SALECUS_ID IN (SELECT CUST_NO from YGCSM_SYS_CUSTOMER_CURR WHERE YGCSM_SYS_CUSTOMER_CURR.IS_STOP=#SYSISTOP#)
    </isNotNull>
    <isNotNull prepend="AND" property="SERVICE_TIME">
     sale.SERVICE_TIME=#SERVICE_TIME#
    </isNotNull>
    <isNotNull prepend="AND" property="SERVICE_TIMENOT">
     sale.SERVICE_TIME<![CDATA[<>]]>#SERVICE_TIMENOT#
     <!--Sale.ORDER_DATE BETWEEN #START_DATE# AND #END_DATE#-->
    </isNotNull>
    <isNotNull prepend="AND" property="SERVICE_NUM">
     sale.SERVICE_NUM='$SERVICE_NUM$'
    </isNotNull>
    <isNotNull prepend="AND" property="ORIG_SOFT_TYPE2_TITIEL">
     sale.ORIG_SOFT_TYPE2_TITIEL='$ORIG_SOFT_TYPE2_TITIEL$'
    </isNotNull>
    <isNotNull prepend="AND" property="SERVICE_TIMEDATE1">
     sale.SERVICE_TIME<![CDATA[>=]]>#SERVICE_TIMEDATE1#
     <!--Sale.ORDER_DATE BETWEEN #START_DATE# AND #END_DATE#-->
    </isNotNull>
    <isNotNull prepend="AND" property="SERVICE_TIMEDATE2">
     sale.SERVICE_TIME<![CDATA[<=]]>#SERVICE_TIMEDATE2#
     <!--Sale.ORDER_DATE BETWEEN #START_DATE# AND #END_DATE#-->
    </isNotNull>

   </dynamic>
  </select>

 

  <select id="Global_YGCSM_SALE_MX_SelectOverdueList" parameterClass="System.Collections.IDictionary" resultClass="Global_YGCSM_SALE_MX" notUseDBParameter="true">

   <!--SELECT  YGCSM_SALE_MX.CUST_NO, SALECUS_ID,SERVICE_FREETIME,YGCSM_SYS_LINKMAN.EMAIL   from  YGCSM_SALE_MX INNER JOIN EP_QX_USER ON YGCSM_SALE_MX.SALECUS_ID=YGCSM_SYS_LINKMAN.CUST_NO-->
   SELECT  (SELECT SERVICE_PERSON_ID FROM YGCSM_SYS_CUSTOMER_CURR WHERE  YGCSM_SYS_CUSTOMER_CURR.CUST_NO=YGCSM_SALE_MX.SALECUS_ID) AS ServiceID, YGCSM_SALE_MX.CUST_NO, SALECUS_ID,SERVICE_FREETIME,EP_QX_USER.EMAIL   from  YGCSM_SALE_MX INNER JOIN EP_QX_USER ON EP_QX_USER.USERID IN (SELECT SERVICE_PERSON_ID FROM YGCSM_SYS_CUSTOMER_CURR WHERE  YGCSM_SYS_CUSTOMER_CURR.CUST_NO=YGCSM_SALE_MX.SALECUS_ID)
   <dynamic prepend="WHERE">
    <isNotNull prepend="AND" property="FREETIME_REMARKED">
     YGCSM_SALE_MX.FREETIME_REMARKED =#FREETIME_REMARKED#
    </isNotNull>
    <isNotNull prepend="AND" property="SALE_TYPE">
     YGCSM_SALE_MX.SALE_TYPE =#SALE_TYPE#
    </isNotNull>
    <isNotNull prepend="AND" property="SERVICE_FREETIMEDATE1">
     YGCSM_SALE_MX.SERVICE_FREETIME<![CDATA[>=]]>#SERVICE_FREETIMEDATE1#
     <!--Sale.ORDER_DATE BETWEEN #START_DATE# AND #END_DATE#-->
    </isNotNull>
    <isNotNull prepend="AND" property="SERVICE_FREETIMEDATE2">
     YGCSM_SALE_MX.SERVICE_FREETIME<![CDATA[<=]]>#SERVICE_FREETIMEDATE2#
     <!--Sale.ORDER_DATE BETWEEN #START_DATE# AND #END_DATE#-->
    </isNotNull>


        <isNotNull prepend="AND" property="SERVICETIME_REMARKED">
          YGCSM_SALE_MX.SERVICETIME_REMARKED='$SERVICETIME_REMARKED$'
        </isNotNull>
        <isNotNull prepend="AND" property="SERVICE_TIMEDATE1">
          YGCSM_SALE_MX.SERVICE_TIME<![CDATA[>=]]>#SERVICE_TIMEDATE1#
          <!--Sale.ORDER_DATE BETWEEN #START_DATE# AND #END_DATE#-->
        </isNotNull>
        <isNotNull prepend="AND" property="SERVICE_TIMEDATE2">
          YGCSM_SALE_MX.SERVICE_TIME<![CDATA[<=]]>#SERVICE_TIMEDATE2#
          <!--Sale.ORDER_DATE BETWEEN #START_DATE# AND #END_DATE#-->
        </isNotNull>

 

      </dynamic>

  </select>

 

 


  <select id="Global_YGCSM_SALE_MX_SelectForEntityList" parameterClass="System.Collections.IDictionary" resultClass="Global_YGCSM_SALE_MX" notUseDBParameter="true">

      SELECT
      Sale.ORDER_MXNO,
      ORDER_NO,
      CUST_NO,
      SOFT_TYPE1,
      SOFT_TYPE1_TITLE,
      SOFT_TYPE2,
      SOFT_TYPE2_TITILE,
      VERSION_TYPE,
      VERSION_TYPE_TITLE,
      LINK_COUNT,
      SL_MONEY,
      ORIG_SOFT_TYPE2,
      ORIG_SOFT_TYPE2_TITIEL,
      ISDEL,
      SALECUS_ID,
      SL_COUNT,
      BUSINESS,
      BUSINESS_NAME,
      Sale.ORDER_DATE,
      CASE SERVICE_FREETIME WHEN 0 THEN NULL ELSE SERVICE_FREETIME END AS SERVICE_FREETIME  ,
      REMARK_SALE,
      SERVICE_NUM,
      FREETIME_REMARKED,
      CASE SERVICE_TIME WHEN 0 THEN NULL ELSE SERVICE_TIME END AS SERVICE_TIME  ,
      SERVICETIME_REMARKED,
      REMARK_JC,
      Sale.SALE_TYPE,
      PREV_NO,
      SATE=
      CASE isNull(SERVICE_FREETIME,'0')
      WHEN '0' THEN '待维护'
      ELSE '已维护'
      END,

      SERVICESATE= CASE isNull(SERVICE_TIME,'0')
      WHEN '0' THEN '待维护'
      ELSE '已维护'
      END


      FROM YGCSM_SALE_MX Sale

      INNER JOIN ($ListSoftwareProductPageSqlQuery$) AS PageSql ON Sale.ORDER_MXNO = PageSql.ORDER_MXNO AND Sale.SALE_TYPE=PageSql.SALE_TYPE Order By Sale.ORDER_DATE DESC

      <dynamic prepend="WHERE">
    <isNotNull prepend="AND" property="ORDER_MXNO">
     Sale.ORDER_MXNO =#ORDER_MXNO#
    </isNotNull>
    <isNotNull prepend="AND" property="SALE_TYPE">
     Sale.SALE_TYPE =#SALE_TYPE#
    </isNotNull>
    <isNotNull prepend="AND" property="CUST_NO">
     Sale.CUST_NO =#CUST_NO#
    </isNotNull>
    <isNotNull prepend="AND" property="SOFT_TYPE2_TITILE">
     Sale.SOFT_TYPE2_TITILE =#SOFT_TYPE2_TITILE#
    </isNotNull>
    <isNotNull prepend="AND" property="ORDER_DATE1">
     Sale.ORDER_DATE<![CDATA[>=]]>#ORDER_DATE1#
     <!--Sale.ORDER_DATE BETWEEN #START_DATE# AND #END_DATE#-->
    </isNotNull>
    <isNotNull prepend="AND" property="ORDER_DATE2">
     Sale.ORDER_DATE<![CDATA[<=]]>#ORDER_DATE2#
     <!--Sale.ORDER_DATE BETWEEN #START_DATE# AND #END_DATE#-->
    </isNotNull>
    <isNotNull prepend="AND" property="SATE">
     Sale.SATE= #SATE#
    </isNotNull>
   </dynamic>
  </select>


  
    <!--服务产品报表-->

    <select id="Global_YGCSM_SALE_MX_GetReportList" parameterClass="System.Collections.IDictionary" resultClass="Global_YGCSM_SALE_MX" notUseDBParameter="true">

 

      SELECT  ORIG_SOFT_TYPE2_TITIEL, MAX(Maxyear) AS Maxyear,SOFT_TYPE2_TITILE

      FROM
      (
      SELECT
      ORDER_MXNO,
      SOFT_TYPE2_TITILE,
      ORIG_SOFT_TYPE2_TITIEL,
      SERVICE_TIME,
      SERVICE_FREETIME,
      MAX( ( CAST( SUBSTRING(CAST(#FieldName# AS VARCHAR), 0, 5)AS INT )
      )) AS MaxYear
    
      FROM dbo.YGCSM_SALE_MX 

      <dynamic prepend="WHERE">

        <isNotNull prepend="AND" property="SALE_TYPE">
          SALE_TYPE in (#SALE_TYPE#)
        </isNotNull>

        <isNotNull prepend="AND" property="SOFT_TYPE2_TITILE">
          SOFT_TYPE2_TITILE ='$SOFT_TYPE2_TITILE$'
        </isNotNull>

        <isNotNull prepend="AND" property="ORIG_SOFT_TYPE2_TITIEL">
          ORIG_SOFT_TYPE2_TITIEL ='$ORIG_SOFT_TYPE2_TITIEL$'
        </isNotNull>
       
        <isNotNull prepend="AND" property="SERVICE_TIME">
          SERVICE_TIME IS NOT NULL
          AND SERVICE_TIME<![CDATA[<>]]>0
        </isNotNull>

        <isNotNull prepend="AND" property="SERVICE_FREETIME">
          SERVICE_FREETIME IS NOT NULL
          AND SERVICE_FREETIME<![CDATA[<>]]>0
        </isNotNull>

      </dynamic>
  
      GROUP  BY ORIG_SOFT_TYPE2_TITIEL,ORDER_MXNO,SERVICE_TIME , SERVICE_FREETIME,SOFT_TYPE2_TITILE
      ) AS temptable  GROUP BY ORIG_SOFT_TYPE2_TITIEL,SOFT_TYPE2_TITILE

 


    </select>
   
  <select id="Global_YGCSM_SALE_MX_SelectReportList" parameterClass="System.Collections.IDictionary" resultClass="Global_YGCSM_SALE_MX" notUseDBParameter="true">
      SELECT
      DISTINCT
      Sale.ORDER_MXNO,
      Sale.CUST_NO,
      Sale.ORDER_NO,
      Sale.SOFT_TYPE2,
      Sale.SOFT_TYPE2_TITILE,
      Sale.SERVICE_FREETIME,
      Sale.FREETIME_REMARKED,
      Sale.SERVICE_TIME,
      Sale.SERVICETIME_REMARKED,
      Sale.ORIG_SOFT_TYPE2_TITIEL

      <isNotNull  property="DateTimeInt">
        ,
           BecomeDue = CASE WHEN CAST(DATEDIFF(mm,'$DateTimeInt$',STR(Sale.#FieldName#) ) AS INT) <![CDATA[<=]]>0
                                  AND CAST(DATEDIFF(dd,'$DateTimeInt$',STR(Sale.#FieldName#) ) AS INT) <![CDATA[<=]]>0
                             THEN 1
                             ELSE NULL
                        END,
                     
            InaMonth = CASE WHEN
            CAST(DATEDIFF(dd,'$DateTimeInt$',STR(Sale.#FieldName#) ) AS INT)<![CDATA[>]]>0 AND
            CAST(DATEDIFF(dd,'$DateTimeInt$',STR(Sale.#FieldName#) ) AS INT)<![CDATA[<]]>(32-DAY(CAST('$DateTimeInt$' AS DATETIME )+32-DAY(CAST('$DateTimeInt$' AS DATETIME ))))
        THEN 1
        ELSE NULL
        END,
        InThreeMonth = CASE WHEN CAST(DATEDIFF(dd,'$DateTimeInt$',STR(Sale.#FieldName#) ) AS INT)<![CDATA[>]]>(32-DAY(CAST('$DateTimeInt$' AS DATETIME )+32-DAY(CAST('$DateTimeInt$' AS DATETIME ))))
        AND  CAST(DATEDIFF(mm,'$DateTimeInt$',STR(Sale.#FieldName#) ) AS INT)<![CDATA[<=]]>3
        THEN 1
        ELSE NULL
        END,
        ThreeMonthUp = CASE WHEN CAST(DATEDIFF(mm,'$DateTimeInt$',STR(Sale.#FieldName#) ) AS INT)  <![CDATA[>]]> 3
        THEN 1
        ELSE NULL
        END


      </isNotNull>
      FROM YGCSM_SALE_MX Sale
      INNER JOIN
      dbo.YGCSM_SYS_CUST_TO_ORG_CURR B ON Sale.SALECUS_ID=B.CUST_NO INNER JOIN
      ($CurrentOrgIDSqlQuery$) as DeptSql ON B.ORG_ID=DeptSql.OrgId inner join  dbo.YGCSM_SYS_CUSTOMER_CURR C on C.CUST_NO=Sale.SALECUS_ID
      INNER JOIN dbo.YGCSM_SYS_MXTYPE_SOFT ON  UsedForStat=1 AND MXTYPE=sale.SALE_TYPE
      INNER JOIN ($SelectReportList$)  AS inTables
      ON   CAST( SUBSTRING(CAST(Sale.#FieldName# AS VARCHAR), 0, 5)AS INT )= inTables.Maxyear
      AND Sale.SOFT_TYPE2_TITILE=inTables.SOFT_TYPE2_TITILE
      <dynamic prepend="WHERE">

    <isNotNull prepend="AND" property="PROVINCE">
     C.PROVINCE =#PROVINCE#
    </isNotNull>

    <isNotNull prepend="AND" property="ORG_ID">
     DeptSql.OrgId =#ORG_ID#
    </isNotNull>

    <isNotNull prepend="AND" property="ISDEL">
     Sale.ISDEL =#ISDEL#
    </isNotNull>
    <!--<isNotNull prepend="AND" property="SALE_TYPE">
     Sale.SALE_TYPE in (#SALE_TYPE#)
    </isNotNull>

 

    <isNotNull prepend="AND" property="SOFT_TYPE2_TITILE">
     Sale.SOFT_TYPE2_TITILE ='$SOFT_TYPE2_TITILE$'
    </isNotNull>-->

        <isNotNull prepend="AND" property="ORIG_SOFT_TYPE2_TITIEL">
          Sale.ORIG_SOFT_TYPE2_TITIEL ='$ORIG_SOFT_TYPE2_TITIEL$'
        </isNotNull>


        <isNotNull prepend="AND" property="SERVICE_TIMENO">
          Sale.SERVICE_TIME<![CDATA[<>]]> '$SERVICE_TIME$'
        </isNotNull>

        <isNotNull prepend="AND" property="SYSISTOP">
          Sale.SALECUS_ID IN (SELECT CUST_NO from YGCSM_SYS_CUSTOMER_CURR WHERE YGCSM_SYS_CUSTOMER_CURR.IS_STOP=#SYSISTOP#)
        </isNotNull>
   </dynamic>
      Order By Sale.ORDER_NO, Sale.#FieldName# DESC
    </select>

 


  <select id="Global_YGCSM_SALE_MX_SelectForEntityMinValue" parameterClass="System.Collections.IDictionary" resultClass="Global_YGCSM_SALE_MX" notUseDBParameter="true">

   SELECT
   Min(SERVICE_FREETIME)
   FROM YGCSM_SALE_MX
   <dynamic prepend="WHERE">
    <isNotNull prepend="AND" property="ORDER_MXNO">
     Sale.ORDER_MXNO =#ORDER_MXNO#

    </isNotNull>
    <isNotNull prepend="AND" property="SERVICE_FREETIME">

     SERVICE_FREETIME <![CDATA[<>]]>#SERVICE_FREETIME#
    </isNotNull>
   </dynamic>
  </select>

  <select id="Global_YGCSM_SALE_MX_SelectTimeValue" parameterClass="System.Collections.IDictionary" resultClass="Global_YGCSM_SALE_MX" notUseDBParameter="true">

   SELECT getDate()
   <dynamic prepend="WHERE">
    <isNotNull prepend="AND" property="NoPamper">

    </isNotNull>

   </dynamic>
  </select>


  <select id="Global_YGCSM_SALE_MX_SelectORIGSOFT" parameterClass="System.Collections.IDictionary" resultClass="Global_YGCSM_SALE_MX" notUseDBParameter="true">

   EXEC SP_CUST_SelectOrigSoft
   <dynamic prepend="WHERE">
    <isNotNull prepend="AND" property="NoPamper">

    </isNotNull>

   </dynamic>
  </select>

 

  <insert id="Global_YGCSM_SALE_MX_Insert" parameterClass="Global_YGCSM_SALE_MX">
   INSERT INTO YGCSM_SALE_MX(
   ORDER_MXNO,
   ORDER_NO,
   CUST_NO,
   SOFT_TYPE1,
   SOFT_TYPE1_TITLE,
   SOFT_TYPE2,
   SOFT_TYPE2_TITILE,
   VERSION_TYPE,
   VERSION_TYPE_TITLE,
   LINK_COUNT,
   SL_MONEY,
   ORIG_SOFT_TYPE2,
   ORIG_SOFT_TYPE2_TITIEL,
   ISDEL,
   SALECUS_ID,
   SL_COUNT,
   BUSINESS,
   BUSINESS_NAME,
   ORDER_DATE,
   SERVICE_FREETIME,
   REMARK_SALE,
   SERVICE_NUM,
   FREETIME_REMARKED,
   SERVICE_TIME,
   SERVICETIME_REMARKED,
   REMARK_JC,
   SALE_TYPE,
   PREV_NO
   ) VALUES(
   #ORDER_MXNO#,
   #ORDER_NO#,
   #CUST_NO#,
   #SOFT_TYPE1#,
   #SOFT_TYPE1_TITLE#,
   #SOFT_TYPE2#,
   #SOFT_TYPE2_TITILE#,
   #VERSION_TYPE#,
   #VERSION_TYPE_TITLE#,
   #LINK_COUNT#,
   #SL_MONEY#,
   #ORIG_SOFT_TYPE2#,
   #ORIG_SOFT_TYPE2_TITIEL#,
   #ISDEL#,
   #SALECUS_ID#,
   #SL_COUNT#,
   #BUSINESS#,
   #BUSINESS_NAME#,
   #ORDER_DATE#,
   #SERVICE_FREETIME#,
   #REMARK_SALE#,
   #SERVICE_NUM#,
   #FREETIME_REMARKED#,
   #SERVICE_TIME#,
   #SERVICETIME_REMARKED#,
   #REMARK_JC#,
   #SALE_TYPE#,
   #PREV_NO# )
  </insert>


  <update id="Global_YGCSM_SALE_MX_SingleUpdate" parameterClass="Global_YGCSM_SALE_MX" notUseDBParameter="true">
   UPDATE YGCSM_SALE_MX
   SET
   REMARK_SALE=#REMARK_SALE#,
   SERVICE_FREETIME=#SERVICE_FREETIME#
   WHERE ORDER_MXNO  =#ORDER_MXNO#

  </update>
  <update id="Global_YGCSM_SALE_MX_IntegrationUpdate" parameterClass="Global_YGCSM_SALE_MX" notUseDBParameter="true">
   UPDATE YGCSM_SALE_MX
   SET
   REMARK_SALE=#REMARK_SALE#

   WHERE ORDER_MXNO  =#ORDER_MXNO#

  </update>


  <update id="Global_YGCSM_SALE_MX_SingleServiceUpdate" parameterClass="Global_YGCSM_SALE_MX" notUseDBParameter="true">
   UPDATE YGCSM_SALE_MX
   SET
   REMARK_SALE=#REMARK_SALE#,
   SERVICE_TIME=#SERVICE_TIME#,
   SERVICE_NUM=#SERVICE_NUM#
   WHERE ORDER_MXNO  =#ORDER_MXNO#

  </update>

  <update id="Global_YGCSM_SALE_MX_Update" parameterClass="Global_YGCSM_SALE_MX">
   UPDATE YGCSM_SALE_MX
   SET ORDER_MXNO=#ORDER_MXNO#, ORDER_NO=#ORDER_NO#, CUST_NO=#CUST_NO#, SOFT_TYPE1=#SOFT_TYPE1#, SOFT_TYPE1_TITLE=#SOFT_TYPE1_TITLE#, SOFT_TYPE2=#SOFT_TYPE2#, SOFT_TYPE2_TITILE=#SOFT_TYPE2_TITILE#, VERSION_TYPE=#VERSION_TYPE#, VERSION_TYPE_TITLE=#VERSION_TYPE_TITLE#, LINK_COUNT=#LINK_COUNT#, SL_MONEY=#SL_MONEY#, ORIG_SOFT_TYPE2=#ORIG_SOFT_TYPE2#, ORIG_SOFT_TYPE2_TITIEL=#ORIG_SOFT_TYPE2_TITIEL#, ISDEL=#ISDEL#, SALECUS_ID=#SALECUS_ID#, SL_COUNT=#SL_COUNT#, BUSINESS=#BUSINESS#, BUSINESS_NAME=#BUSINESS_NAME#, ORDER_DATE=#ORDER_DATE#, SERVICE_FREETIME=#SERVICE_FREETIME#, REMARK_SALE=#REMARK_SALE#, SERVICE_NUM=#SERVICE_NUM#, FREETIME_REMARKED=#FREETIME_REMARKED#, SERVICE_TIME=#SERVICE_TIME#, SERVICETIME_REMARKED=#SERVICETIME_REMARKED#, REMARK_JC=#REMARK_JC#, SALE_TYPE=#SALE_TYPE#, PREV_NO=#PREV_NO#
   WHERE ORDER_MXNO  =#ORDER_MXNO#

  </update>


  <delete id="Global_YGCSM_SALE_MX_Delete" parameterClass="System.Collections.IDictionary">
   DELETE FROM  YGCSM_SALE_MX
   WHERE ORDER_MXNO  =#ORDER_MXNO#

  </delete>
  <!--客户产品购买情况汇总表 begin-->
  <select id="CustomersPurchaseSummaryReport" parameterClass="System.Collections.IDictionary" resultClass="Global_YGCSM_SALE_MX" >
   SELECT
   cust.cust_name,
   <isNotNull property="SOFT_TYPE2List">
    <iterate open="" close=""  property="SOFT_TYPE2List" conjunction=",">
     SUM(S$SOFT_TYPE2List[]$) AS S$SOFT_TYPE2List[]$
    </iterate>
   </isNotNull>
   <!--<isNotNull property="SOFT_TYPE2List"> 暂不需要行合计
    <iterate open="(" close=")"  property="SOFT_TYPE2List" conjunction="+">
     SUM(S$SOFT_TYPE2List[]$)
    </iterate>
    AS S_TOTAL
   </isNotNull>-->
   FROM ($SelectForCustomersPurchaseSummaryReport$) A
   inner join YGCSM_SYS_CUSTOMER cust on a.prev_no=cust.prev_no
   where cust.isdel=0
   group by cust_name
  </select>
  <select id="SelectForCustomersPurchaseSummaryReport" parameterClass="System.Collections.IDictionary" resultClass="Global_YGCSM_SALE_MX" notUseDBParameter="true">
   SELECT
   <isNotNull property="SOFT_TYPE2S">
    <iterate open="" close=","  property="SOFT_TYPE2List" conjunction=",">
     CASE MX.SOFT_TYPE2_NEW  WHEN  #SOFT_TYPE2List[]# THEN MX.SL_COUNT ELSE 0
     END AS S$SOFT_TYPE2List[]$
    </iterate>

   </isNotNull>
   cust.prev_no
   FROM
   ($SelectForCustomersPurchaseSummaryReport_MX$) MX
   inner join dbo.YGCSM_SYS_CUSTOMER  cust on  MX.SALECUS_ID = cust.cust_no

   <isNotNull property="CUST_NOS">
    <!--INNER JOIN
    (
    SELECT  CUST_NO FROM dbo.YGCSM_SYS_CUSTOMER WHERE EXISTS
    (SELECT CUST_NO,PREV_NO FROM dbo.YGCSM_SYS_CUSTOMER_CURR WHERE CUST_NO IN $CUST_NOS$
    AND YGCSM_SYS_CUSTOMER.PREV_NO=YGCSM_SYS_CUSTOMER_CURR.PREV_NO)
    ) CUST  ON MX.SALECUS_ID=CUST.CUST_NO-->
    inner join (select prev_no,cust_no from dbo.YGCSM_SYS_CUSTOMER where cust_no IN ($CUST_NOS$)) newCust  on cust.prev_no=newCust.prev_no
   </isNotNull>
   <isNotNull property="DEPT_ID">
    INNER JOIN dbo.YGCSM_SYS_CUST_TO_ORG CUSTORG ON CUST.CUST_NO=CUSTORG.CUST_NO
   </isNotNull>
   <dynamic  prepend="WHERE">    
    <isNotNull prepend="AND" property="PROVINCE">CUST.PROVINCE=#PROVINCE#</isNotNull>
    <isNotNull prepend="AND" property="DEPT_ID">CUSTORG.ORG_ID=#DEPT_ID#</isNotNull>
   </dynamic>
  </select>
  <select id="SelectForCustomersPurchaseSummaryReport_MX" parameterClass="System.Collections.IDictionary" resultClass="Global_YGCSM_SALE_MX" notUseDBParameter="true">
   SELECT A.SALECUS_ID,
   A.SOFT_TYPE2,
   D.ITEM_ID AS SOFT_TYPE2_NEW,
   A.SL_COUNT
   FROM dbo.YGCSM_SALE_MX A
   left join (select aa.ITEM_ID AS ITEM_ID,bb.item_id AS SOFT_TYPE2 from dbo.YGCSM_SYS_ENUM_ITEM_YGSL aa
   inner join YGCSM_SYS_ENUM_ITEM_YGSL bb on aa.prev_no=bb.prev_no where aa.isdel!=2
   ) D on
   A.SOFT_TYPE2=D.SOFT_TYPE2
   <dynamic  prepend="WHERE">
    <isNotNull prepend="AND" property="DATE_BEGIN">A.ORDER_DATE<![CDATA[>=]]>#DATE_BEGIN#</isNotNull>
    <isNotNull prepend="AND" property="DATE_END">A.ORDER_DATE<![CDATA[<=]]>#DATE_END#</isNotNull>
    <isNotNull prepend="and" property="SOFT_TYPE2S">
     D.ITEM_ID IN ($SOFT_TYPE2S$)
    </isNotNull>
   </dynamic>
  </select>
  <!--客户产品购买情况汇总表 end-->

 

  <!--地区产品购买情况汇总表 begin-->
  <select id="RegionalProductPurchasesReport" parameterClass="System.Collections.IDictionary" resultClass="Global_YGCSM_SALE_MX" >
   SELECT
   item.item_title as province,
   <isNotNull property="SOFT_TYPE2List">
    <iterate open="" close=""  property="SOFT_TYPE2List" conjunction=",">
     SUM(S$SOFT_TYPE2List[]$) AS S$SOFT_TYPE2List[]$
    </iterate>
   </isNotNull>
   <!--<isNotNull property="SOFT_TYPE2List"> 暂不需要行合计
    <iterate open="(" close=")"  property="SOFT_TYPE2List" conjunction="+">
     SUM(S$SOFT_TYPE2List[]$)
    </iterate>
    AS S_TOTAL
   </isNotNull>-->
   FROM ($SelectForRegionalProductPurchasesReport$) A
   inner join YGCSM_SYS_CUSTOMER cust on a.prev_no=cust.prev_no
   left join YGCSM_SYS_ENUM_ITEM_YGSL item on cust.province=item.item_id
   where cust.isdel=0
   group by item.item_title
  </select>
  <select id="SelectForRegionalProductPurchasesReport" parameterClass="System.Collections.IDictionary" resultClass="Global_YGCSM_SALE_MX" notUseDBParameter="true">
   SELECT
   <isNotNull property="SOFT_TYPE2S">
    <iterate open="" close=","  property="SOFT_TYPE2List" conjunction=",">
     CASE MX.SOFT_TYPE2_NEW  WHEN  #SOFT_TYPE2List[]# THEN MX.SL_COUNT ELSE 0
     END AS S$SOFT_TYPE2List[]$
    </iterate>

   </isNotNull>
   cust.prev_no
   FROM
   ($SelectForRegionalProductPurchasesReport_MX$) MX
   inner join dbo.YGCSM_SYS_CUSTOMER  cust on  MX.SALECUS_ID = cust.cust_no
   <isNotNull property="DEPT_ID">
    INNER JOIN dbo.YGCSM_SYS_CUST_TO_ORG CUSTORG ON CUST.CUST_NO=CUSTORG.CUST_NO
   </isNotNull>
   <dynamic  prepend="WHERE">
    <isNotNull prepend="AND" property="PROVINCE">CUST.PROVINCE=#PROVINCE#</isNotNull>
    <isNotNull prepend="AND" property="DEPT_ID">CUSTORG.ORG_ID=#DEPT_ID#</isNotNull>
   </dynamic>
  </select>
  <select id="SelectForRegionalProductPurchasesReport_MX" parameterClass="System.Collections.IDictionary" resultClass="Global_YGCSM_SALE_MX" notUseDBParameter="true">
   SELECT A.SALECUS_ID,
   A.SOFT_TYPE2,
   D.ITEM_ID AS SOFT_TYPE2_NEW,
   A.SL_COUNT
   FROM dbo.YGCSM_SALE_MX A
   left join (select aa.ITEM_ID AS ITEM_ID,bb.item_id AS SOFT_TYPE2 from dbo.YGCSM_SYS_ENUM_ITEM_YGSL aa
   inner join YGCSM_SYS_ENUM_ITEM_YGSL bb on aa.prev_no=bb.prev_no where aa.isdel!=2
   ) D on
   A.SOFT_TYPE2=D.SOFT_TYPE2
   <dynamic  prepend="WHERE">
    <isNotNull prepend="AND" property="DATE_BEGIN">A.ORDER_DATE<![CDATA[>=]]>#DATE_BEGIN#</isNotNull>
    <isNotNull prepend="AND" property="DATE_END">A.ORDER_DATE<![CDATA[<=]]>#DATE_END#</isNotNull>
    <isNotNull prepend="and" property="SOFT_TYPE2S">
     D.ITEM_ID IN ($SOFT_TYPE2S$)
    </isNotNull>
   </dynamic>
  </select>
  <!--地区产品购买情况汇总表 end-->


  <!--某省客户类型购买情况汇总表 begin-->
<select id="CustType2ProductPurchasesReport" parameterClass="System.Collections.IDictionary" resultClass="Global_YGCSM_SALE_MX" >
 SELECT
 item.item_title as custType2,
 <isNotNull property="SOFT_TYPE2List">
  <iterate open="" close=""  property="SOFT_TYPE2List" conjunction=",">
   SUM(S$SOFT_TYPE2List[]$) AS S$SOFT_TYPE2List[]$
  </iterate>
 </isNotNull>
 <!--<isNotNull property="SOFT_TYPE2List"> 暂不需要行合计
  <iterate open="(" close=")"  property="SOFT_TYPE2List" conjunction="+">
   SUM(S$SOFT_TYPE2List[]$)
  </iterate>
  AS S_TOTAL
 </isNotNull>-->
 FROM ($SelectForCustType2ProductPurchasesReport$) A
 inner join YGCSM_SYS_CUSTOMER cust on a.prev_no=cust.prev_no
 left join YGCSM_SYS_ENUM_ITEM_YGSL item on cust.cust_type2=item.item_id
 where cust.isdel=0
 group by item.item_title
</select>
<select id="SelectForCustType2ProductPurchasesReport" parameterClass="System.Collections.IDictionary" resultClass="Global_YGCSM_SALE_MX" notUseDBParameter="true">
 SELECT
 <isNotNull property="SOFT_TYPE2S">
  <iterate open="" close=","  property="SOFT_TYPE2List" conjunction=",">
   CASE MX.SOFT_TYPE2_NEW  WHEN  #SOFT_TYPE2List[]# THEN MX.SL_COUNT ELSE 0
   END AS S$SOFT_TYPE2List[]$
  </iterate>

 </isNotNull>
 cust.prev_no
 FROM
 ($SelectForCustType2ProductPurchasesReport_MX$) MX
 inner join dbo.YGCSM_SYS_CUSTOMER  cust on  MX.SALECUS_ID = cust.cust_no
 <isNotNull property="DEPT_ID">
  INNER JOIN dbo.YGCSM_SYS_CUST_TO_ORG CUSTORG ON CUST.CUST_NO=CUSTORG.CUST_NO
 </isNotNull>
 <dynamic  prepend="WHERE">
  <isNotNull prepend="AND" property="PROVINCE">CUST.PROVINCE=#PROVINCE#</isNotNull>
  <isNotNull prepend="AND" property="DEPT_ID">CUSTORG.ORG_ID=#DEPT_ID#</isNotNull>
 </dynamic>
</select>
<select id="SelectForCustType2ProductPurchasesReport_MX" parameterClass="System.Collections.IDictionary" resultClass="Global_YGCSM_SALE_MX" notUseDBParameter="true">
 SELECT A.SALECUS_ID,
 A.SOFT_TYPE2,
 D.ITEM_ID AS SOFT_TYPE2_NEW,
 A.SL_COUNT
 FROM dbo.YGCSM_SALE_MX A
 left join (select aa.ITEM_ID AS ITEM_ID,bb.item_id AS SOFT_TYPE2 from dbo.YGCSM_SYS_ENUM_ITEM_YGSL aa
 inner join YGCSM_SYS_ENUM_ITEM_YGSL bb on aa.prev_no=bb.prev_no where aa.isdel!=2
 ) D on
 A.SOFT_TYPE2=D.SOFT_TYPE2
 <dynamic  prepend="WHERE">
  <isNotNull prepend="AND" property="DATE_BEGIN">A.ORDER_DATE<![CDATA[>=]]>#DATE_BEGIN#</isNotNull>
  <isNotNull prepend="AND" property="DATE_END">A.ORDER_DATE<![CDATA[<=]]>#DATE_END#</isNotNull>
  <isNotNull prepend="and" property="SOFT_TYPE2S">
   D.ITEM_ID IN ($SOFT_TYPE2S$)
  </isNotNull>
 </dynamic>
</select>
<!--某省客户类型购买情况汇总表 end-->


  <!--获取指定客户的产品信息-->
  <statement id="Global_YGCSM_SALE_MX_GetProductForCustPageData"  parameterClass="System.Collections.IDictionary">
   exec SP_CUST_GetProductByCustNoPageData @custNo=#CustNo#,@pageSize=#PageSize#,@pageIndex=#PageIndex# ,@softType=#SoftType#
  </statement>
 </statements>

 

</sqlMap>

 

GTBGT525
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
mysql mapper设置当前日期_mybatis中:mapper.xml中日期在sql语句里的比较方法
weixin_39962199的博客
02-01 3620
项目案例-------------------------->parameterType="java.util.Map">select c.XM,dd.JZCS,dd.CISID from DC_PATIENT c,(select count(d.CISID)JZCS,d.CISID from DC1001 d whered.JZYS=#{code}and d.JZKSSJ>=#...
Sqlmap该如何使用？
Cairo_A的博客
04-26 282
伪静态：主要是为了隐藏传递的参数名，伪静态只是一种URL重写的手段，既然能接受参数输入，所以并不能防止注入。目前来看，防止注入的最有效的方法就是使用LINQ。B: 基于Boolean的盲注（Boolean based blind）（-r：让sqlmap加载post请求，-p：指定注入用的参数）T: 基于时间的盲注（time based blind）U: 联合查询（union query based）Q: 内联查询（inlin queries）S: 栈查询（stack queries）
Spring Mybatis日期比较（一）------datetime类型
Odd Man
04-11 1万+
前言 大家可能有着不同的数据表设计思路，有些人喜欢使用时间戳，而有些人喜欢用datetime。下面说一下datetime类型的表字段，在做大小比较时所支持的数据类型。 正文 类型一 java.util.Date类型，此类型可以直接传入到mapper类中域datetime类型字段进行普通的大小比较。 Date date = new Date(); 类型二 java.lang.S...
mybatis中的时间比较
热门推荐
g_blue_wind的博客
10-28 5万+
1、查询时间段的sql语句(时间类型为datetime或date)(数据库中的时间类型)： &lt;![CDATA[   and DATE_FORMAT(CREATE_TIME , '%Y-%m-%d')&gt;=  DATE_FORMAT(#{beginTime}, '%Y-%m-%d')   ]]&gt;       （mysql） %M 月名字(January……December)  %...
sqlmap源码
11-30
它的源码解析将带我们深入了解如何构建这样的安全工具，以及它背后的攻击和防御原理。 首先，sqlmap的核心功能是通过自动化的方式探测和利用SQL注入漏洞。SQL注入是一种常见的网络安全威胁，攻击者可以通过在输入...
java开发基于SQLmap的SQL注入工具源码.zip
06-01
基于SQLmap的SQL注入工具源码.。基于SQLmap，使用Java开发 安装教程 安装JDK（需要有javafx） 安装Python 安装SQLmap 基于SQLmap的SQL注入工具源码.。基于SQLmap，使用Java开发 安装教程 安装JDK（需要有javafx）...
sqlmap 源码
02-02
sqlmap的源码，可以作为代码规范的参考，编程思想也很不错。
sqlmap专用将链接转换为Cookie注入用的代码
01-29
本工具为sqlmap专用，用于将普通注入链接转换为SQLmap里的Cookie注入链接，已经开源，代码和使用帮助全都在压缩包里 程序举例： 普通链接：http://www.xxx.com/TeachView.asp?id=21 转换为 sqlmap.py -u ...
ibatis2mybatisConverter:将 sqlMap xmls 从 iBatis 2 迁移到 Mybatis 3
06-15
2. **结果映射（Result Maps）**：在 iBatis 2 中，结果映射定义了如何将数据库查询结果映射到 Java 对象。在 Mybatis 3 中，结果映射保持了相似的概念，但提供了更多灵活性，如嵌套结果映射和关联映射。 3. **参数...
ibatis 日期格式比较
光阴迷客的知识库
07-13 3401
**环境** 数据库：Oracle 11g 编程语言：Java 持久层框架：IBatis**背景** 数据库表A中有date型字段 create_date，存储的是日期格式是年月日时分秒：YYYY/MM/DD HH:MM:SS， 然后我我想通过java传参，按照日期区间（日期区间假设为begin_date,end_date）进行比较**实现方式** 先将参数格式化 SimpleDateFo
Mysql中date类型字段在sqlmap中的用法
ciweiju8049的博客
12-05 309
字符串转日期： 应该使用str_to_date函数将传入的字符串类型的参数转为date类型的数据，然后与数据库字段进行比较，如下： REGISTER_DATE = str_to_date(#registerDat...
sqlmap工具基本使用（检测sql注入）
m0_37570494的博客
01-25 6197
sqlmap的用户手册： sqlmap是python2进行运行的，如果要直接使用，需要把sqlmap设置到环境变量中： sqlmap主要用于sql注入方面的异常检测 Mysql数据库 1、先检测是否可以注入,先判断是否可以正常注入： sqlmap -u url -v 3 里面有个注意的点url后面必须是可注入的参数?id=之类的，否则url检测会有问题 对于某些今天url可以在/a/b*.html尝试加入*号来进行此路径内的检测 2、爆库 sqlmap.py -u http://...
如何把从数据库中查询出的结果保存为List<Map>
自强不息 拼搏进取
12-05 8942
看到好多人问如何把从数据库中查询出的结果保存为List 今天研究了一下，如果有说的不对的请给予指正。 ResultSet 是如何获得的在这就不再阐述了，网上一搜一堆。 当我们获得ResultSet rs 时可以转化为ResultSetMetaData对象。 ResultSetMetaData可用于获取关于 ResultSet 对象中列的类型和属性信息的对象。 看下边例子 //获
利用burpsite和sqlmap进行post注入
Assassin
06-05 4871
尝试用burpsite和sqlmap进行自动post注入，目标（一道题目）http://120.24.86.145:8002/chengjidan/第一步 用burpsite抓包并保存成txt第二步 用sqlmap语句sqlmap -r 保存的txt文件 -p 需要注入的变量然后我们的注入语句为python sqlmap.py -r C:\Users\Assassin\Desktop\inp
sqlmap的简单查询
hclimg的博客
05-03 5709
sqlmap是一个自动化的sql注入工具，主要功能是扫描、发现并利用给定的url的sql注入漏洞，还内置了很多的绕过插件。sqlmap采用了五种sql注入技术： 基于布尔类型的盲注，即可以根据返回页面判断条件真假的注入。 基于时间的盲注，即不能根据页面返回的内容判断任何的信息，要用条件语句查看时间延迟语句是否已执行（即页面返回时间是否增加）来判断。 基于报错注入，即页面会返回错误信...
sqlmap源码分析
最新发布
11-26
为了更好地理解 sqlmap 的源码，我们可以按照以下步骤进行分析： 1. 阅读 sqlmap 的文档，了解其基本原理和使用方法。 2. 阅读 sqlmap 的源码，了解其整体结构和模块划分。 3. 重点分析 sqlmap 的核心模块，包括...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值