sql案例六

需求

查找符合查询条件的 冷链报价单/高标报价单/冷链合同/高标合同单据，且该单据有K2日志记录，且K2的发起类型BSID in(冷链报价/高标报价/高标合同/冷链合同) [不抓提前进场和batch数据] [如果是高标合同注意BOID转换]
按业务类型，战区，园区，单据号，审批完成时间排序
查询条件：业务类型（高标报价/冷链报价/高标合同/冷链合同），单据号，战区，单据创建时间起，单据创建时间止
|列表数据 | 特殊字段说明|
| ——- | ——- |
|K2返回意见 |日志.状态|
|K2返回人员 |日志.人员 |
|K2返回人员名称 |日志.人员 对应的名称|
|审批开始时间 |当前单据，上一步的审批结束时间 如果无上一步，则空|
|审批结束时间| 日志.创建时间 年月日时分秒|
|K2返回说明1|日志.岗位|
|K2返回说明2|日志.说明|
|审批时长 |结束-开始，如果有空，则空|
|天数时 |保留2位小数|
|时长时 |保留2位小数|

使用帆软报表进行动态sql
计算天数和时长数使用的帆软公式 例如：IF(OR(ISNULL(K2), K2 = “”), “”, ROUND((DATETONUMBER(L2) - DATETONUMBER(K2)) / (1000 60 60 * 24), 2))
人员名称使用的是帆软的数据字典

-- 查询所有符合条件的单据，使用子查询获取上一审批步骤的时间
SELECT 
    t.业务类型,
    t.园区,
    t.单据号,
    t.单据创建时间,
    t.单据开始日期,
    t.单据结束日期,
    t.客户名称,
    t.日志表状态,
    t.人员,
    -- 通过子查询获取上一审批步骤的结束时间作为本步骤的开始时间
    (
        SELECT k2.createtime 
        FROM k2_log k2 
        WHERE k2.boid = t.关联ID 
            AND k2.createtime < t.审批结束时间
        ORDER BY k2.createtime DESC 
        LIMIT 1
    ) AS 审批开始时间,
    t.审批结束时间,
    t.岗位,
    t.说明,
		t.K2流程号,
		t2.area_name 区域名称
FROM (

    -- 冷链报价单
    SELECT 
        '冷链报价' AS 业务类型,
        b.mall_code AS 园区编号,
				b.mall_name AS 园区,
        b.quotation_no AS 单据号,
        b.create_time AS 单据创建时间,
        b.lease_start_date AS 单据开始日期,
        b.lease_end_date AS 单据结束日期,
        b.company_name AS 客户名称,
        k.OPE_TYPE AS 日志表状态,
        k.OPE_USER_ADACCOUNT AS 人员,
        k.createtime AS 审批结束时间,
        k.INFO AS 岗位,
        k.Comment AS 说明,
        k.boid AS 关联ID,
				k.iProcInst_ID as K2流程号
    FROM bs_cold_quotation_base b
    INNER JOIN k2_log k ON b.quotation_no = k.boid
    WHERE
		${if(len(bType) != 0 && FIND("冷链报价",bType)<=0,"1=2","1=1")} 
		${if(len(no) == 0,"","AND b.quotation_no = '"+no+"'")}
	  ${if(len(startTime) == 0,"","AND date_format(b.create_time,'%Y-%m-%d') >= '"+startTime+"'")}
	  ${if(len(endTime) == 0,"","AND date_format(b.create_time,'%Y-%m-%d') <= '"+endTime+"'")}
		and k.bsid IN ('WLIBP_DM_COMPANY_MODIFY', 'WLIBP_IM_QUOTATION', 'WLIBP_IM_SIGNCONTRACT', 'WLIBP_IM_SIGN_COLD_CONTRACT')
    GROUP BY b.quotation_no, b.mall_name, b.create_time, b.company_name,
             k.OPE_TYPE, k.OPE_USER_ADACCOUNT, k.createtime, k.INFO, k.Comment, k.boid
    
    UNION ALL
    
    -- 高标报价单
    SELECT 
        '高标报价' AS 业务类型,
				b.mall_code AS 园区编号,
        b.mall_name AS 园区,
        b.quotation_no AS 单据号,
        b.create_time AS 单据创建时间,
        MIN(l.lease_start_date) AS 单据开始日期,
        MAX(l.lease_end_date) AS 单据结束日期,
        b.company_name AS 客户名称,
        k.OPE_TYPE AS 日志表状态,
        k.OPE_USER_ADACCOUNT AS 人员,
        k.createtime AS 审批结束时间,
        k.INFO AS 岗位,
        k.Comment AS 说明,
        k.boid AS 关联ID,
				k.iProcInst_ID as K2流程号
    FROM bs_quotation_base b
    LEFT JOIN bs_quotation_lease_term_detail l ON b.quotation_no = l.quotation_no
    INNER JOIN k2_log k ON b.quotation_no = k.boid
    WHERE
		${if(len(bType) != 0 && FIND("高标报价",bType)<=0,"1=2","1=1")} 
		${if(len(no) == 0,"","AND b.quotation_no = '"+no+"'")}
		${if(len(startTime) == 0,"","AND date_format(b.create_time,'%Y-%m-%d') >= '"+startTime+"'")}
	  ${if(len(endTime) == 0,"","AND date_format(b.create_time,'%Y-%m-%d') <= '"+endTime+"'")}
		and b.is_del = 0 and l.is_del= 0 
		and k.bsid IN ('WLIBP_DM_COMPANY_MODIFY', 'WLIBP_IM_QUOTATION', 'WLIBP_IM_SIGNCONTRACT', 'WLIBP_IM_SIGN_COLD_CONTRACT')
    GROUP BY b.quotation_no, b.mall_name, b.create_time, b.company_name,
             k.OPE_TYPE, k.OPE_USER_ADACCOUNT, k.createtime, k.INFO, k.Comment, k.boid
    
    UNION ALL
    
    -- 冷链合同
    SELECT 
        '冷链合同' AS 业务类型,
				c.mall_code AS 园区编号,
        c.mall_name AS 园区,
        c.cold_cont_no AS 单据号,
        c.create_time AS 单据创建时间,
        c.lease_start_date AS 单据开始日期,
        c.lease_end_date AS 单据结束日期,
        c.company_name AS 客户名称,
        k.OPE_TYPE AS 日志表状态,
        k.OPE_USER_ADACCOUNT AS 人员,
        k.createtime AS 审批结束时间,
        k.INFO AS 岗位,
        k.Comment AS 说明,
        k.boid AS 关联ID,
				k.iProcInst_ID as K2流程号
    FROM bs_data_cold_cont c
    INNER JOIN k2_log k ON c.cold_cont_no = k.boid
    WHERE
		${if(len(bType) != 0 && FIND("冷链合同",bType)<=0,"1=2","1=1")} 
		${if(len(no) == 0,"","AND c.cold_cont_no = '"+no+"'")}
		${if(len(startTime) == 0,"","AND date_format(c.create_time,'%Y-%m-%d') >= '"+startTime+"'")}
	  ${if(len(endTime) == 0,"","AND date_format(c.create_time,'%Y-%m-%d') <= '"+endTime+"'")}
		and c.is_del = 0 and k.bsid = "WLIBP_IM_SIGN_COLD_CONTRACT"
		
    UNION ALL
    
    -- 高标合同
    SELECT 
        '高标合同' AS 业务类型,
				b.mall_code AS 园区编号,
        h.project_name AS 园区,
        h.no AS 单据号,
        e.create_time AS 单据创建时间,
        h.cont_start AS 单据开始日期,
        h.cont_end AS 单据结束日期,
        h.merchant_name AS 客户名称,
        k.OPE_TYPE AS 日志表状态,
        k.OPE_USER_ADACCOUNT AS 人员,
        k.createtime AS 审批结束时间,
        k.INFO AS 岗位,
        k.Comment AS 说明,
        k.boid AS 关联ID,
				k.iProcInst_ID as K2流程号
    FROM bs_data_header_h h
		left join bs_mall b on h.project_id = b.id and b.is_del=0
		left join bs_data_cont_extend e on h.no = e.cont_no and e.is_del = 0
    INNER JOIN k2_log k ON h.instance_id = k.boid
    WHERE 
		${if(len(bType) != 0 && FIND("高标合同",bType)<=0,"1=2","1=1")} 
		${if(len(no) == 0,"","AND h.no = '"+no+"'")}
		${if(len(startTime) == 0,"","AND date_format(e.create_time,'%Y-%m-%d') >= '"+startTime+"'")}
	  ${if(len(endTime) == 0,"","AND date_format(e.create_time,'%Y-%m-%d') <= '"+endTime+"'")}
		and k.bsid = "WLIBP_IM_SIGNCONTRACT"
) AS t left join bs_mall_area t2 on t.园区编号 = t2.mall_code
WHERE t.单据开始日期 IS NOT NULL  -- 确保有有效的单据日期
${if(len(areaName) == 0,"","and t2.area_name like '%"+areaName+"%'")}
ORDER BY t.业务类型, t.园区, t.单据号, t.审批结束时间 DESC;