<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.huazheng.project.mssql.mapper.CrmMapper">
    
    <select id="selectSalesContractProcessNew" parameterType="SalesContractProcess" resultType="SalesContractProcess">
		select top 20
			a.id, a.xiaoShouDDBH, a.xuFangGSID, a.xuFangGS, a.gongFangGSt, a.xiaoShouBMt, a.shouHuoGSID, a.shouHuoGS,
			a.fuKuanTJt, a.caiGouDDBH, a.dangQianXYED, a.yvQiK, a.dengJi, a.biZhongt, a.yongJin, a.zongHeMLL,
			a.SYS_PROCESSNAME as sysProcessname,
			a.SYS_INCIDENT as sysIncident, a.ziShiYBv, a.sys_updatetime
		from "Biz_销售合同流程" a
		LEFT JOIN "Biz_销售合同流程_MX" b on a.SYS_INCIDENT = b.SYS_INCIDENT
		where a.id &gt; #{id} and b.id is not null
		group BY a.id, a.xiaoShouDDBH, a.xuFangGSID, a.xuFangGS, a.gongFangGSt, a.xiaoShouBMt, a.shouHuoGSID, a.shouHuoGS,
			a.fuKuanTJt, a.caiGouDDBH, a.dangQianXYED, a.yvQiK, a.dengJi, a.biZhongt, a.yongJin, a.zongHeMLL,
			a.SYS_PROCESSNAME, a.SYS_INCIDENT, a.ziShiYBv, a.sys_updatetime
    </select>
    <select id="selectSalesForecastProcessNew" parameterType="SalesForecastProcess" resultType="SalesForecastProcess">
		select top 20 
			a.id, a.shenQingR, a.sys_Incstatus as sysIncstatus, a.sys_Processname as sysProcessname, a.sys_Incident as sysIncident, a.shenQingRBM, a.yuCeLXt
			, a.kaiShiSJ as kaiShiSJs, a.jieShuSJ as jieShuSJs, a.shenQingRLN, a.sys_updatetime
		from "Biz_销售预测流程" a
			left join "Biz_销售预测流程_MX" b on a.SYS_INCIDENT = b.SYS_INCIDENT
		where a.id &gt; #{id} and b.id is not null
		GROUP BY a.id, a.shenQingR, a.sys_Incstatus, a.sys_Processname, a.sys_Incident, a.shenQingRBM, 
		a.yuCeLXt, a.kaiShiSJ, a.jieShuSJ, a.shenQingRLN, a.sys_updatetime
    </select>
    <select id="selectSampleApplicationProcessNew" parameterType="SampleApplicationProcess" resultType="SampleApplicationProcess">
		select top 20 
			id,dingDanLXv,dingDanLXt,sYS_INCIDENT,yeWuYID,xuFangGS,xuFangGSID,
			yangPinLXv,yangPinLXt,pCBCS,chanPinYY,jiFuBZ,sYS_UPDATETIME,concat(pCBCS,'层/',chanPinYY) as pCBCS2chanPinYY,ziSHiYBv,sys_incstatus,xiaoShouBMv,sys_starttime
		from "Biz_样品申请流程"
		where id &gt; #{id}
    </select>
    <select id="selectSalesInquiryProcessNew" parameterType="SalesInquiryProcess" resultType="SalesInquiryProcess">
		select top 20 
			id,sys_processname,sys_incident,sys_starttime,sys_updatetime,sys_incstatus,kehubm,bibiev
		from "Biz_销售询报价流程"
		where id &gt; #{id}
    </select>
    <select id="selectSampleClosingProcessNew" parameterType="SampleClosingProcess" resultType="SampleClosingProcess">
		select top 20 
			id,sYS_INCIDENT,yangPinSQSLH,sYS_INCSTATUS,sys_updatetime,sys_starttime, CASE
			 WHEN yangPinSQSLH = '' THEN null 
			 ELSE yangPinSQSLH
			 END AS yangPinSQSLH1
		from "Biz_样品结案流程"
		where id &gt; #{id}
    </select>
    <!-- 新增流程中关联的两个表 -->
    <select id="selectSampleApplicationProcessMingXiNew" parameterType="SampleApplicationProcessMingXi" resultType="SampleApplicationProcessMingXi">
		select top 20 
			id,sYS_INCIDENT,uLPH,wuLiaoMS,shuLiang,hangXiangMH, hangXiangMH as hangxiangmh1,ShiFouLC
		from "Biz_样品申请流程_MingXi"
		where id &gt; #{id}
    </select>
    <select id="selectSalesInquiryProcessMXNew" parameterType="SalesInquiryProcessMX" resultType="SalesInquiryProcessMX">
		select top 20 
			id,sys_incident,wuliaoh,chicun,chengben,baojiamll, biaozhunml
		from "Biz_销售询报价流程_MX"
		where id &gt; #{id}
    </select>
    <select id="selectSampleClosingProcessMingXiNew" parameterType="SampleClosingProcessMingXi" resultType="SampleClosingProcessMingXi">
		select top 20 
			id,sYS_INCIDENT,shiFouTGv,shiFouTGt,hangxiangmh,shuliang
		from "Biz_样品结案流程_MingXi"
		where id &gt; #{id}
    </select>
    <!-- process中的关联查询 -->
    <select id="selectSalesContractProcessMX" parameterType="SalesContractProcess" resultType="SalesContractProcessMX">
    	select id, chengben, SYS_INCIDENT as sysIncident, zongjia, maolibz, wuliaoh, convert(varchar(50),Round(hetongmll,2)) + '%' as hetongmll
    	from "Biz_销售合同流程_MX" 
    	where SYS_INCIDENT = #{sysIncident}
    </select>
    <select id="selectSalesForecastProcessMX" parameterType="SalesForecastProcess" resultType="SalesForecastProcessMX">
    	select id, wuLiaoH, wuLiaoMS, keHuMC, jiHuaJDL, mingXiMingC, sys_Incident as sysIncident
    	from "Biz_销售预测流程_MX" 
    	where SYS_INCIDENT = #{sysIncident}
    </select>
    
    
    <!-- 增量的方法 -->
    <select id="selectSalesContractProcessCheckByUpdate" parameterType="SalesContractProcess" resultType="SalesContractProcess">
		select top 20
			a.id, a.xiaoShouDDBH, a.xuFangGSID, a.xuFangGS, a.gongFangGSt, a.xiaoShouBMt, a.shouHuoGSID, a.shouHuoGS,
			a.fuKuanTJt, a.caiGouDDBH, a.dangQianXYED, a.yvQiK, a.dengJi, a.biZhongt, a.yongJin, a.zongHeMLL,
			a.SYS_PROCESSNAME as sysProcessname,
			a.SYS_INCIDENT as sysIncident, a.ziShiYBv, a.sys_updatetime
		from "Biz_销售合同流程" a
		LEFT JOIN "Biz_销售合同流程_MX" b on a.SYS_INCIDENT = b.SYS_INCIDENT
		where a.id &gt; #{id} and b.id is not null 
			and a.sys_updatetime is not null and CONVERT(varchar(10),a.sys_updatetime,120) = CONVERT(varchar(10),GETDATE(),120)
		group BY a.id, a.xiaoShouDDBH, a.xuFangGSID, a.xuFangGS, a.gongFangGSt, a.xiaoShouBMt, a.shouHuoGSID, a.shouHuoGS,
			a.fuKuanTJt, a.caiGouDDBH, a.dangQianXYED, a.yvQiK, a.dengJi, a.biZhongt, a.yongJin, a.zongHeMLL,
			a.SYS_PROCESSNAME, a.SYS_INCIDENT, a.ziShiYBv, a.sys_updatetime
    </select>
    <select id="selectSalesForecastProcessCheckByUpdate" parameterType="SalesForecastProcess" resultType="SalesForecastProcess">
    	select top 20 
			a.id, a.shenQingR, a.sys_Incstatus as sysIncstatus, a.sys_Processname as sysProcessname, a.sys_Incident as sysIncident, a.shenQingRBM, a.yuCeLXt
			, a.kaiShiSJ as kaiShiSJs, a.jieShuSJ as jieShuSJs, a.shenQingRLN, a.sys_updatetime
		from "Biz_销售预测流程" a
			left join "Biz_销售预测流程_MX" b on a.SYS_INCIDENT = b.SYS_INCIDENT
		where a.id &gt; #{id} and b.id is not null 
			and a.sys_updatetime is not null and CONVERT(varchar(10),a.sys_updatetime,120) = CONVERT(varchar(10),GETDATE(),120)
		GROUP BY a.id, a.shenQingR, a.sys_Incstatus, a.sys_Processname, a.sys_Incident, a.shenQingRBM, 
		a.yuCeLXt, a.kaiShiSJ, a.jieShuSJ, a.shenQingRLN, a.sys_updatetime
    </select>
    <select id="selectSampleApplicationProcessCheckByUpdate" parameterType="SampleApplicationProcess" resultType="SampleApplicationProcess">
    	select top 20 
			id,dingDanLXv,dingDanLXt,sYS_INCIDENT,yeWuYID,xuFangGS,xuFangGSID,
			yangPinLXv,yangPinLXt,pCBCS,chanPinYY,jiFuBZ,sYS_UPDATETIME,concat(pCBCS,'层/',chanPinYY) as pCBCS2chanPinYY,ziSHiYBv,sys_incstatus,xiaoShouBMv,sys_starttime
		from "Biz_样品申请流程"
		where id &gt; #{id} 
			and sys_updatetime is not null and CONVERT(varchar(10),sys_updatetime,120) = CONVERT(varchar(10),GETDATE(),120)
    </select>
    <select id="selectSalesInquiryProcessCheckByUpdate" parameterType="SalesInquiryProcess" resultType="SalesInquiryProcess">
		select top 20 
			id,sys_processname,sys_incident,sys_starttime,sys_updatetime,sys_incstatus,kehubm,bibiev
		from "Biz_销售询报价流程"
		where id &gt; #{id} 
			and sys_updatetime is not null and CONVERT(varchar(10),sys_updatetime,120) = CONVERT(varchar(10),GETDATE(),120)
    </select>
    <select id="selectSampleClosingProcessCheckByUpdate" parameterType="SampleClosingProcess" resultType="SampleClosingProcess">
    	select top 20 
			id,sYS_INCIDENT,yangPinSQSLH,sYS_INCSTATUS,sys_updatetime,sys_starttime, CASE
			 WHEN yangPinSQSLH = '' THEN null 
			 ELSE yangPinSQSLH
			 END AS yangPinSQSLH1
		from "Biz_样品结案流程"
		where id &gt; #{id} 
			and sys_updatetime is not null and CONVERT(varchar(10),sys_updatetime,120) = CONVERT(varchar(10),GETDATE(),120)
    </select>
    
    
    <select id="cascadeSalesContractProcessMXBySalesContractProcess" parameterType="SalesContractProcess" resultType="SalesContractProcessMX">
    	select top 20 
    		id, chengben, SYS_INCIDENT as sysIncident, zongjia, maolibz, wuliaoh, convert(varchar(50),Round(hetongmll,2)) + '%' as hetongmll
    	from "Biz_销售合同流程_MX" 
    	where SYS_INCIDENT = #{sysIncident}
    </select>
	<select id="cascadeSalesForecastProcessMXBySalesForecastProcess" parameterType="SalesForecastProcess" resultType="SalesForecastProcessMX">
		select top 20 
			id, wuLiaoH, wuLiaoMS, keHuMC, jiHuaJDL, mingXiMingC, sys_Incident as sysIncident
    	from "Biz_销售预测流程_MX" 
    	where SYS_INCIDENT = #{sysIncident}
    </select>
    <select id="cascadeSampleApplicationProcessMingXiBySampleApplicationProcess" parameterType="SampleApplicationProcess" resultType="SampleApplicationProcessMingXi">
    	select top 20 
			id,sYS_INCIDENT,uLPH,wuLiaoMS,shuLiang,hangXiangMH, hangXiangMH as hangxiangmh1,ShiFouLC
		from "Biz_样品申请流程_MingXi"
		where SYS_INCIDENT = #{sYS_INCIDENT}
    </select>
    <select id="cascadeSalesInquiryProcessMXBySalesInquiryProcess" parameterType="SalesInquiryProcess" resultType="SalesInquiryProcessMX">
    	select top 20 
			id,sys_incident,wuliaoh,chicun,chengben,baojiamll, biaozhunml
		from "Biz_销售询报价流程_MX"
		where sys_incident = #{sys_incident}
    </select>
    <select id="cascadeSampleClosingProcessMingXiBySampleClosingProcess" parameterType="SampleClosingProcess" resultType="SampleClosingProcessMingXi">
    	select top 20 
			id,sYS_INCIDENT,shiFouTGv,shiFouTGt,hangxiangmh,shuliang
		from "Biz_样品结案流程_MingXi"
		where SYS_INCIDENT = #{sYS_INCIDENT}
    </select>





    <!-- 按主键查询 -->
	<select id="selectSalesContractProcessById" parameterType="SalesContractProcess" resultType="SalesContractProcess">
    	select 
			id, xiaoShouDDBH, xuFangGSID, xuFangGS, gongFangGSt, xiaoShouBMt, shouHuoGSID, shouHuoGS, 
			fuKuanTJt, caiGouDDBH, dangQianXYED, yvQiK, dengJi, biZhongt, yongJin, zongHeMLL, 
			SYS_PROCESSNAME as sysProcessname, 
			SYS_INCIDENT as sysIncident, sys_updatetime, ziShiYBv
    	from "Biz_销售合同流程" 
    	where id = #{id}
    </select>
	<select id="selectSalesForecastProcessById" parameterType="SalesForecastProcess" resultType="SalesForecastProcess">
    	select id, shenQingR, sys_Incstatus as sysIncstatus, sys_Processname as sysProcessname, sys_Incident as sysIncident, shenQingRBM, yuCeLXt
    	,kaiShiSJ as kaiShiSJs,jieShuSJ as jieShuSJs, shenQingRLN, sys_updatetime
    	from "Biz_销售预测流程" 
    	where id = #{id}
    </select>
	<select id="selectSampleApplicationProcessById" parameterType="SampleApplicationProcess" resultType="SampleApplicationProcess">
    	select id,dingDanLXv,dingDanLXt,sYS_INCIDENT,yeWuYID,xuFangGS,xuFangGSID,
    		yangPinLXv,yangPinLXt,pCBCS,chanPinYY,jiFuBZ,sYS_UPDATETIME,concat(pCBCS,'层/',chanPinYY) as pCBCS2chanPinYY,ziSHiYBv,sys_incstatus,xiaoShouBMv,sys_starttime
    	from "Biz_样品申请流程" 
    	where id = #{id}
    </select>
    <select id="selectSalesInquiryProcessById" parameterType="SalesInquiryProcess" resultType="SalesInquiryProcess">
    	select id,sys_processname,sys_incident,sys_starttime,sys_updatetime,sys_incstatus,kehubm,bibiev
    	from "Biz_销售询报价流程" 
    	where id = #{id}
    </select>
    <select id="selectSampleClosingProcessById" parameterType="SampleClosingProcess" resultType="SampleClosingProcess">
    	select id,sYS_INCIDENT,yangPinSQSLH,sYS_INCSTATUS,sys_updatetime,sys_starttime, CASE
		 WHEN yangPinSQSLH = '' THEN null 
		 ELSE yangPinSQSLH
		 END AS yangPinSQSLH1
    	from "Biz_样品结案流程" 
    	where id = #{id}
    </select>
	
    <select id="selectSampleApplicationProcessMingXiById" parameterType="SampleApplicationProcessMingXi" resultType="SampleApplicationProcessMingXi">
    	select id,sYS_INCIDENT,uLPH,wuLiaoMS,shuLiang,hangXiangMH, hangXiangMH as hangxiangmh1,ShiFouLC
    	from "Biz_样品申请流程_MingXi" 
    	where id = #{id}
    </select>
   <select id="selectSalesInquiryProcessMXById" parameterType="SalesInquiryProcessMX" resultType="SalesInquiryProcessMX">
    	select id,sys_incident,wuliaoh,chicun,chengben,baojiamll, biaozhunml
		from "Biz_销售询报价流程_MX"
    	where id = #{id}
    </select>
    <select id="selectSampleClosingProcessMingXiById" parameterType="SampleClosingProcessMingXi" resultType="SampleClosingProcessMingXi">
    	select id,sYS_INCIDENT,shiFouTGv,shiFouTGt,hangxiangmh,shuliang
    	from "Biz_样品结案流程_MingXi" 
    	where id = #{id}
    </select>
    
    <select id="selectSalesContractProcessMXById" parameterType="SalesContractProcessMX" resultType="SalesContractProcessMX">
    	select id, chengben, SYS_INCIDENT as sysIncident, zongjia, maolibz, wuliaoh, convert(varchar(50),Round(hetongmll,2)) + '%' as hetongmll
    	from "Biz_销售合同流程_MX" 
    	where id = #{id}
    </select>
	<select id="selectSalesForecastProcessMXById" parameterType="SalesForecastProcessMX" resultType="SalesForecastProcessMX">
    	select 
	    	id, wuLiaoH, wuLiaoMS, keHuMC, jiHuaJDL, mingXiMingC, sys_Incident as sysIncident
    	from "Biz_销售预测流程_MX" 
    	where id = #{id}
    </select>
    
</mapper>
