DROP VIEW CHGT.V_PHJG_INFO_JKSJY;

CREATE OR REPLACE VIEW CHGT.V_PHJG_INFO_JKSJY
AS (
    /*
    监控数据源
    
    项目名称：XMMC, 申请用地单位名称：SQRMC, 法人：FRDBXM, 法人办公电话：FRDBDHHM, 法人移动电话：
    单位地址：TXDZ, 联系人电话：LXDH, 电子邮件：EMAIL, 邮政编码：YZBM
    
    合同ID或决定书ID：ID,     合同或决定书编号：HTBH, 电子监管号：DZJGH, 批准书文号: PZSWH, 供应方式：GDYWLX
    坐落：TDZL, 行政区域：XZQY, 供地面积：GDMJ, 金额：JE, 签定日期：QDRQ

    约定交地时间：YDJDSJ, 约定开工时间：YDKGSJ, 约定竣工时间：YDJGSJ
    变更交地时间：BGJDSJ, 变更开工时间：BGKGSJ, 变更竣工时间：BGJGSJ
    实际交地时间：SJJDSJ, 实际开工时间：SJKGSJ, 实际竣工时间：SJJGSJ
    复核验收时间：FHYSSJ
    
    容积率最小值：RJLZXZ
    容积率最大值：RJLZDZ
    绿化率最小值：LHLZXZ
    绿化率最大值：LHLZDZ
    建筑密度最小值：JZMDZXZ
    建筑密度最大值：JZMDZDZ
    约定开发投资额：YDKFTZE
    投资强度：TZQD
    建筑总面积：JZZMJ
    建筑限高最小值：JZXGZXZ
    建筑限高最大值：JZXGZDZ
    土地用途：TDYTBM
    
    当前阶段：DQJD
    是否疑似：SFYS
    疑似类别：YSLB
    */
    --第一层
    SELECT 
        项目名称 xmmc, 申请用地单位名称 SQRMC, 法人 FRDBXM, 法人办公电话 FRDBDHHM, 法人移动电话 SJHM,
        单位地址 TXDZ, 联系人电话 LXDH, 电子邮件 EMAIL, 邮政编码 YZBM,
        
        合同ID ID, 编号 HTBH, 电子监管号 DZJGH, 批准书文号 PZSWH, 
        CASE 
        WHEN 供应方式 IS NULL THEN '国有土地公开出让'
        WHEN 供应方式 = '1' THEN '国有土地公开出让'
        WHEN 供应方式 = '2' THEN '国有土地协议出让'
        WHEN 供应方式 = '3' THEN '国有土地划拨'
        END GDYWLX,
        坐落 TDZL, 行政区域 XZQY, 供地面积 CRMJ, 金额 CRJE, 签订日期 HTQDRQ,
        
        约定交地时间 YDJDSJ, 约定开工时间 YDKGSJ, 约定竣工时间 YDJGSJ,
        BGJDSJ, BGKGSJ, BGJGSJ,
        SJJDSJ, SJKGSJ, SJJGSJ,
        FHYSSJ, 
        
        容积率最小值 RJLZXZ, 容积率最大值 RJLZDZ, 绿化率最小值 LHLZXZ, 绿化率最大值 LHLZDZ,
        建筑密度最小值 JZMDZXZ, 建筑密度最大值 JZMDZDZ, 约定开发投资额 YDKFTZE, 投资强度 TZQD,
        建筑总面积 JZZMJ, 建筑限高最小值 JZXGZXZ, 建筑限高最大值 JZXGZDZ, 土地用途 TDYT,
        
        CASE
            WHEN SJJDSJ IS NULL  THEN '交地阶段'
            WHEN SJKGSJ IS NULL  THEN '开工阶段'
            WHEN SJJGSJ IS NULL  THEN '建设及竣工阶段'
            WHEN FHYSSJ IS NULL  THEN '复核验收阶段' 
        END DQJD, NULL SFYS, NULL YSLB
    FROM 
    (
        --第二层
        SELECT * FROM
        (
            --第三层
            SELECT 
                xm.XMMC 项目名称, sqr.SQRMC 申请用地单位名称, sqr.FRDBXM 法人, sqr.FRDBDHHM 法人办公电话, sqr.SJHM 法人移动电话, 
                sqr.TXDZ 单位地址, sqr.LXDH 联系人电话, sqr.EMAIL 电子邮件, sqr.YZBM 邮政编码,
                
                ht.ID 合同ID, ht.HTBH 编号, ht.DZJGH 电子监管号, pzs.PZSWH 批准书文号, gd.GDYWLX 供应方式,
                zd.TDZL 坐落, qy.XZQY 行政区域, zd.CRMJ 供地面积, crj.CRJE 金额, qtxx.HTQDRQ 签订日期,
            
                zd.YDJDSJ 约定交地时间, yd.YDDGSJ 约定开工时间, yd.YDJGSJ 约定竣工时间,
                
                yd.RJLZXZ 容积率最小值, yd.RJLZDZ 容积率最大值, yd.LHLZXZ 绿化率最小值, yd.LHLZDZ 绿化率最大值,
                yd.JZMDZXZ 建筑密度最小值, yd.JZMDZDZ 建筑密度最大值, yd.YDKFTZE 约定开发投资额, yd.TZQD 投资强度,
                yd.JZZMJ 建筑总面积, yd.JZXGZXZ 建筑限高最小值, yd.JZXGZDZ 建筑限高最大值, tdyt.NAME 土地用途
            FROM YDGD_INFO_GDHT ht
                JOIN YDGL_INFO_XM xm ON xm.ID = ht.XMID
                JOIN BIZ_INFO_SQR sqr ON xm.dwid = sqr.ID
                JOIN YDGD_INFO_GDHTCRZD zd ON ht.ID = zd.GDHTID
                JOIN YDGD_INFO_GDHTCRJZFYD crj ON ht.ID = crj.GDHTID
                JOIN YDGD_INFO_GDHTQTXX qtxx ON qtxx.GDHTID = ht.ID
                JOIN YDGD_INFO_GDHTTDKFLYYJSYD yd ON ht.ID = yd.GDHTID
                LEFT JOIN
                (
                    SELECT qy.GDHTID, wmsys.wm_concat(xzqh.NAME) XZQY FROM YDGD_INFO_GDHTZDSZQY qy
                        JOIN BIZ_DICT_XZQH xzqh ON qy.SZXZQ = xzqh.CODE
                    GROUP BY qy.GDHTID
                ) qy ON qy.GDHTID = ht.ID
                LEFT JOIN BIZ_DICT_TDYT tdyt ON tdyt.CODE = zd.ZYTDYTBM
                LEFT JOIN YDGD_INFO_JSYDGD gd ON gd.GDHTID = ht.ID
                LEFT JOIN YDGD_INFO_JSYDPZS pzs ON gd.JSYDPZSID = pzs.ID
            WHERE ht.GDHTZT != -1
            UNION all
            SELECT 
                xm.XMMC 项目名称, sqr.SQRMC 申请用地单位名称, sqr.FRDBXM 法人, sqr.FRD