本文的目的是：探讨数据仓库的必要性

复盘数字回款项目

数字回款项目数据层面核心是出“应收指标、回款指标”，在ERP系统已有一份存储过程里面包含了“认购、签约、回款”指标的背景下，数字回款项目表面看应该比较简单。但开发过程的实际情况并非如此：

第一阶段：开发PC应收报表数据——问题：影响生产库，需要协同明源才能更新脚本
当时为了快速出报表，完全基于生产只读库开发。遇到的问题是，开发好的视图/存储过程等需要在ERP生产库里面新增，然后同步到只读库。协同慢且影响了生产库。由于PC报表不需要秒开所以并没有落实体表只读库也算能用。

第二阶段：开发移动端的回款分析数据——问题：mysql性能远低于sqlserver，严重影响调试进展
移动端必须要秒开数据，需要落成实体表，不能再继续在生产的sqlserver只读库上进行开发，迁移到mysql上开发。首先遇到的问题是性能问题，同样的数据mysql的性能比sqlserver的性能低很多。跑一份数据需要大约1-2小时，为了保障数据不遗漏，每天晚上都需要专人盯着执行检查。每修改一次sql口径，需要等1小时才能得到结果。后来经多方协助通过一个特殊的mysql语法才解决了性能问题。但由于mysql经常掉索引，导致整个运行也不稳定。查错和开发的效率极其低下。

第三阶段：新增“认购指标”和调整“草签回款口径”——问题：没有明细数据，sql复杂对数极其困难
由于缺乏完整的口径文档，我们需要从erp的存储过程分析口径，2000行的sql只读库不支持with as 临时表层层嵌套，导致理解sql异常困难。核对数据不能到明细，只能到楼栋级，口径的核对完全靠猜测尝试房源才能一点一点进行。

问题总结起来：开发效率极低，对人员技术能力依赖很高，数据核对极其困难。

数据仓库的意义

以下总结主要对比常见的服务生产系统的数据库mysql和服务数据消费的数仓阿里云ADB PG版。

数据消费与数据生产

生产系统最需要操作【单条数据】的“增删改查”，比如：录入一份合同，一个客户，修改一个房源，删除一个客户，基本都是在操作单条记录，少量的表关联。需要处理并发录入的场景。
消费系统最需要操作【大量数据】的“查”，比如：当年销售完成就需要一年的数据，回款分析需要认购-签约-应收-回款多个业务环节的所有数据。基本都是在操作万级百万级条记录，很多表关联。需要处理大量读取数据的场景。

当数据在几兆几百兆的时候，关联表很少的时候，mysql和adb差异不大。一旦数据超过百万条1G以上差异就很明显了。阳光城应收1G-225万条，实收2.7G-423万条。

打个比方：轿车和公车，当乘客只有3个人的时候，轿车和公车差异不大，但是当乘客有20人时，轿车就玩不转了。

数据仓库的典型特性

我们从实际开发效率的视角看数仓的一些典型特征

第1个特征：性能强大，adb开发效率成本于mysql
我们挑选了体积接近的两个表分别做了sum，虽然不能完全说明问题，但是也略见一斑。mysql跑了近400秒（该指标不稳定，有时候只需要80秒），但adb只花了毫秒级。
图1：mysql

图2：3个节点的adb

这种性能主要体现在调试环节的效率，因为一个指标的开发，口径需要反复测试修改，完善，每执行一次数据出来才能核对，缓慢的性能严重影响了开发效率。

第2个特征：不易受限个人开发能力
举例：本次回款有一个口径需要按回款时间进行叠加。如下图：

这个需求本身在数仓里面有标准的函数支持，但是mysql5需要一个高级的写法才能实现。如下代码：

set @ljtoal=0,@temp ='';
SELECT 
    saleguid,
    GetDate,
    amount,
    ItemType,
#    @ljtoal:=@ljtoal + IFNULL(amount, 0) AS ljtoal,
    @ljtoal:= case when @temp = saleguid then @ljtoal + IFNULL(amount, 0)  else IFNULL(amount, 0) end as ljtoal,
 @temp := saleguid tmp
FROM
biraw.ods_my304_s_getin 
where saleguid is not null
order by saleguid,GetDate asc
;

第3个特征：Schema非常适合数据分层管理
mysql的表完全混在一起

adb的表通过schema可对数据进行清晰的分层管理

第4个特征：物化视图实现前端秒响应，且大幅降低etl的工作量
移动端应用几乎都是需要秒反应的，要做到这一点最常见的做法就是把结果写成实体表，但是实体表需要建模和etl调度。但是物化视图可以跳过建模过程，既解决了性能问题，又不增加etl工作量。最新的adb还支持实时的物化视图。

第5个特征：通过外部表支持OSS文本数据
可以通过外部表实现对文本数据的访问，可以用来做备份，一些不常用的数据可以放到oss，比如：快照数据。

数据构建规范的意义

本次项目新增认购指标和修改草签回款口径，数据核对异常困难。因为erp的存储过程粒度是到“楼栋业态”但真正的口径分析数据查错是一定需要到最细颗粒度的。不遵循数据仓库构建规范的表核心存在三个问题：

• 难以复用，已有指标，在新的场景需要使用时，难以复用；
• 维度-限定-指标的构建不实现解耦的情况下，每一个sql都是独特的，运维难度很高，严重依赖个人；

所以数据仓库的背后是“技术+规范”，技术主要解决的是物理效率问题，规范主要解决“复用和易维护“的问题。

数仓对效率成本的影响

阳光城效率的实际情况是，同期我们做了两个差不多规模企业的项目，全局投入的资源水平接近，另外一个客户已经上线了七八个应用，阳光城的回款分析数据仍然在核对过程中。基础平台已经严重影响了服务效率。过程中浪费了工程师大量的时间：进行sql分析和优化，等待执行结果，人肉查错等低效任务。
当然我们并不限定adb，只是adb在我们过去实践过程中不到5万块每年的费用，所带来的效率提升是比oracle，GP等数据仓库都明显。

附件：数据核对难点示例
第一步：导出回款分析数据和erp存储过程数据通过分期关联对比差异找出分期

如果差异的分期比较少则快，分期多则麻烦。

第二步：查询erp报表界面定位楼栋

第三步：猜测可能的房源，核心问题在这里，有的能猜，有的不容易看出来

附件：ERP日报存储过程

SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
GO
ALTER PROC [dbo].[Rpt_s_FZD05_HZXM_zh_test1_Op_testbx]
   ( @projguid VARCHAR(MAX) ,---分期GUID                    
     @begDate DATETIME ,---开始时间                    
     @endDate DATETIME ---截至时间   
     )
AS /*拆分ProjGUID*/
BEGIN
   SELECT   *
   INTO     #Proj
   FROM     ( SELECT *
              FROM   f_ProjGUID(@projguid , ',') ) moko;
    /* 初始化组织架构 */
   SELECT   *
   INTO     #BU
   FROM     ( SELECT a.* ,
                     ISNULL(b.CheckRate , 0) AS CheckRate ,
                     ISNULL(b.RightsRate , 0) AS RightsRate ,
                     ISNULL(b.StockRate , 0) AS StockRate ,
                     ISNULL(ProjectState , '') 项目状态 ,
                     ISNULL(ProjectLevel , '') 项目等级 ,
                     ISNULL(sCity , '') 城市 ,
                     ISNULL(sProjectMan , '') 项目营销负责人 ,
                     ISNULL(sRegionCityMan , '') '区域_城市营销负责人' ,
                     ISNULL(sRegionMan , '') 大区营销负责人 ,
                     ISNULL(sIsCooper , '') 是否合作项目 ,
                     ISNULL(sPartners , '') 合作方 ,
                     ISNULL(sIsMark , '') 是否营销操盘 ,
                     ISNULL(IsOperate , '') 是否我司操盘 ,
                     ISNULL(Parallelmethod , '') 是否并表
              FROM   vw_s_BusinessProject a WITH ( NOLOCK )
              INNER JOIN p_Project b WITH ( NOLOCK ) ON a.FQProjGUID = b.ProjGUID
              LEFT JOIN s_ProjectState_Qy ON a.ProjGUID = s_ProjectState_Qy.ProjectGUID
              LEFT JOIN s_ProjectState_Xm ON a.ProjGUID = s_ProjectState_Xm.ProjectGUID
              WHERE  a.BUName NOT LIKE '%作废%'
                     AND b.ProjGUID IN ( SELECT *
                                         FROM   #Proj ) ) moko;
        --无交易数据
     --   SELECT  r.RoomGUID ,
     --           0 AS flag
     --   INTO    #RoomGUID
     --   FROM    dbo.p_Room r WITH ( NOLOCK )
     --           INNER JOIN #Proj p ON r.ProjGUID = p.projGUID
     --           LEFT JOIN dbo.s_Order so WITH ( NOLOCK ) ON so.RoomGUID = r.RoomGUID
     --           LEFT JOIN s_Contract sc ON sc.RoomGUID = r.RoomGUID
     --   WHERE   so.OrderGUID IS  NULL
     --           AND sc.ContractGUID IS  NULL;      
     --   --退房数据        
     --   INSERT  INTO #RoomGUID
     --           ( RoomGUID ,
     --             flag
     --           )
     --           SELECT DISTINCT
     --                   a.RoomGUID ,
     --                   0
     --           FROM    s_SaleHSData a WITH ( NOLOCK )
     --                   INNER JOIN #Proj p ON a.ProjGUID = p.projGUID
     --                   LEFT JOIN #RoomGUID ON #RoomGUID.RoomGUID = a.RoomGUID
     --           WHERE   #RoomGUID.RoomGUID IS NULL
     --           GROUP BY a.RoomGUID
     --           HAVING  SUM(a.Ts) = 0;
           ----有交易数据且交易日期在区间范围内
     --   INSERT  INTO #RoomGUID
     --           ( RoomGUID ,
     --             flag
     --           )
     --           SELECT DISTINCT
     --                   a.RoomGUID ,
     --                   1
     --           FROM    s_SaleHSData a WITH ( NOLOCK )
     --                   INNER JOIN #BU p ON a.ProjGUID = p.FQProjGUID
     --           WHERE   QsDate >= @mindate
     --                   AND Ts <> 0; 
   SELECT  DISTINCT
            RoomGUID ,
            OrderGUID
   INTO     #RgRoomGUID
   FROM     s_Order WITH ( NOLOCK )
   INNER JOIN #Proj p WITH ( NOLOCK ) ON p.projGUID = s_Order.ProjGUID
   WHERE    CAST(QSDate AS DATE) <= @endDate
            AND ( Status = '激活'
            OR CAST(CloseDate AS DATE) > @endDate );
   INSERT   INTO #RgRoomGUID
            ( RoomGUID ,
              OrderGUID )
   SELECT  DISTINCT
            RoomGUID ,
            ContractGUID
        --INTO    #QYRoomGUID
   FROM     s_Contract WITH ( NOLOCK )
   INNER JOIN #Proj WITH ( NOLOCK ) ON #Proj.projGUID = s_Contract.ProjGUID
   WHERE    CAST(QSDate AS DATE) <= @endDate
            AND ( Status = '激活'
            OR CAST(CloseDate AS DATE) > @endDate );
        --SELECT  a.ProjGUID AS FQProjGUID ,
        --        b.BldGUID ,
        --        b.BldName ,
        --        a.TotalDj ,
        --        a.BldArea ,
        --        a.RoomGUID ,
        --        b.YszDate ,
        --        a.Status ,
        --        a.IsZiChiStatus ,
        --        a.BProductTypeCode ,
        --        t2.OrderGUID ,
        --        t3.ContractGUID
        --INTO    #tmpYszRoomDate
        --FROM    p_Room a WITH ( NOLOCK )
        --        INNER JOIN #Proj p ON p.projGUID = a.ProjGUID
        --        INNER JOIN p_Building b WITH ( NOLOCK ) ON a.BldGUID = b.BldGUID
        --        LEFT JOIN s_Order t2 WITH ( NOLOCK ) ON a.RoomGUID = t2.RoomGUID
        --                                                AND t2.QSDate <= @endDate
        --                                                AND ( t2.Status = '激活'
        --                                                      OR t2.CloseDate > @endDate
        --                                                    )
        --        LEFT JOIN s_Contract t3 WITH ( NOLOCK ) ON a.RoomGUID = t3.RoomGUID
        --                                                   AND t3.QSDate <= @endDate
        --                                                   AND ( t3.Status = '激活'
        --                                                      OR t3.CloseDate > @endDate
        --                                                      );
        --WHERE   b.YszDate IS NOT NULL;
        --CREATE NONCLUSTERED INDEX [Ix_tmpYszRoomDate_IsZiChiStatus]
        --ON #tmpYszRoomDate ([IsZiChiStatus],YszDate)
        --INCLUDE ([TotalDj],[RoomGUID],BldGUID);
        --SELECT  a.ProjGUID ,
        --        a.CjTotal ,
        --        a.RoomGUID
        --INTO    #vw_s_JHRGDate
        --FROM    dbo.s_Order a WITH ( NOLOCK )
        --        INNER  JOIN #Proj b ON b.projGUID = a.ProjGUID
        --WHERE   a.Status = '激活'; 
        --SELECT  a.ProjGUID ,
        --        a.RoomGUID ,
        --        a.HtTotal ,
        --        a.ContractGUID ,
        --        ISNULL(a.SjBcTotal, 0) sjbctotal ,
        --        a.IsCQ
        --INTO    #vw_s_JHQYDate
        --FROM    dbo.s_Contract a WITH ( NOLOCK )
        --        INNER JOIN #Proj b ON b.projGUID = a.ProjGUID
        --WHERE   a.Status = '激活'; 
    /* 计算货值信息 */
   SELECT  --ROW_NUMBER() OVER ( PARTITION BY a.ProjGUID ORDER BY a.BldGUID ) sn ,
            a.ProjGUID AS FQProjGUID ,
            a.BldGUID ,
            CAST('' AS VARCHAR(100)) AS BldName ,
            CAST('' AS VARCHAR(50)) FirstLevelName ,
            CAST('' AS VARCHAR(50)) BProductTypeShortName ,
            a.BProductTypeCode ,
            CAST('' AS DATE) YszDate ,
            CAST('' AS DATE) OpeningDate ,
            SUM(CASE WHEN a.TotalDj <> 0 THEN 1
                END) AS HzCount ,
            SUM(CASE WHEN a.TotalDj <> 0 THEN a.BldArea
                END) AS HzArea ,
            SUM(CASE WHEN a.TotalDj <> 0
                          AND a.Status NOT IN ( '认购' , '签约' , '草签' ) THEN a.TotalDj
                     ELSE 0
                END) + SUM(CASE WHEN a.TotalDj <> 0
                                     AND a.Status = '认购' THEN c.CjTotal
                                ELSE 0
                           END) + SUM(CASE WHEN a.TotalDj <> 0
                                                AND a.Status IN ( '签约' , '草签' ) THEN d.HtTotal + ISNULL(d.SjBcTotal , 0)
                                           ELSE 0
                                      END) AS HzTotal ,
            SUM(CASE WHEN a.TotalDj <> 0
                          AND ( a.Status <> '签约'
                          OR ISNULL(d.IsCQ , 0) = 1 ) THEN 1
                END) AS WsCount ,
            SUM(CASE WHEN a.TotalDj <> 0
                          AND ( a.Status <> '签约'
                          OR ISNULL(d.IsCQ , 0) = 1 ) THEN a.BldArea
                END) AS WsArea ,
            SUM(CASE WHEN a.TotalDj <> 0
                          AND a.Status NOT IN ( '认购' , '签约' ) THEN a.TotalDj
                     ELSE 0
                END) + SUM(CASE WHEN a.TotalDj <> 0
                                     AND a.Status = '认购' THEN c.CjTotal
                                ELSE 0
                           END) + SUM(CASE WHEN a.TotalDj <> 0
                                                AND a.Status = '签约'
                                                AND ISNULL(d.IsCQ , 0) = 1 THEN d.HtTotal
                                           ELSE 0
                                      END) AS WsTotalDj
   INTO     #Hz
   FROM     dbo.p_Room a WITH ( NOLOCK )
   INNER JOIN #Proj p WITH ( NOLOCK ) ON p.projGUID = a.ProjGUID
                --JOIN p_Building g WITH ( NOLOCK ) ON a.BldGUID = g.BldGUID
   LEFT JOIN dbo.s_Order c WITH ( NOLOCK ) ON c.RoomGUID = a.RoomGUID
                                              AND c.Status = '激活'
   LEFT JOIN s_Contract d WITH ( NOLOCK ) ON d.RoomGUID = a.RoomGUID
                                             AND d.Status = '激活'
   LEFT JOIN p_BuildProductType F WITH ( NOLOCK ) ON a.BProductTypeCode = F.BProductTypeCode
   GROUP BY a.ProjGUID ,
            a.BldGUID ,
            a.BProductTypeCode;
                --F.FirstLevelName ,
                --F.BProductTypeShortName ,
                --F.BProductTypeCode ,
                --g.YszDate ,
                --g.OpeningDate;
   UPDATE   a
   SET      a.BldName = b.BldName ,
            a.YszDate = b.YszDate ,
            a.OpeningDate = b.OpeningDate ,
            a.FirstLevelName = F.FirstLevelName ,
            a.BProductTypeShortName = F.BProductTypeShortName
   FROM     #Hz a
   LEFT JOIN dbo.p_Building b WITH ( NOLOCK ) ON b.BldGUID = a.BldGUID
   LEFT JOIN p_BuildProductType F WITH ( NOLOCK ) ON a.BProductTypeCode = F.BProductTypeCode;
        --DROP TABLE #vw_s_JHQYDate;
        --DROP TABLE #vw_s_JHRGDate;
    /* 合作项目信息 */
   SELECT   projGUID ,
            my.ParamValue ishzxm
   INTO     #hzxm1
   FROM     myBizParamOption my WITH ( NOLOCK )
   INNER JOIN #Proj WITH ( NOLOCK ) ON projGUID = my.ScopeGUID
   WHERE    my.ParamName = 's_CooperateProject';
   DECLARE @chuE DATE;
   SET @chuE = DATEADD(d , 1 - DATEPART(d , @endDate) , @endDate);
    --非期初数据
   SELECT   A.BUGUID ,
            B.OrgGUID ,
           --供货
            ISNULL(SUM(CASE WHEN B.Day BETWEEN @begDate AND @endDate THEN B.SupplyTs
                            ELSE 0
                       END) , 0) SupplyTs ,
            ISNULL(SUM(CASE WHEN B.Day BETWEEN @begDate AND @endDate THEN B.SupplyArea
                            ELSE 0
                       END) , 0) SupplyArea ,
            ISNULL(SUM(CASE WHEN B.Day BETWEEN @begDate AND @endDate THEN B.SupplyAmount
                            ELSE 0
                       END) , 0) SupplyAmount ,
           --当期净认购
            ISNULL(SUM(CASE WHEN B.Day BETWEEN @begDate AND @endDate THEN B.OrderTs
                            ELSE 0
                       END) , 0) OrderTs ,
            ISNULL(SUM(CASE WHEN B.Day BETWEEN @begDate AND @endDate THEN B.OrderArea
                            ELSE 0
                       END) , 0) OrderArea ,
            ISNULL(SUM(CASE WHEN B.Day BETWEEN @begDate AND @endDate THEN B.OrderAmount
                            ELSE 0
                       END) , 0) AS OrderAmount ,
           --当期签约
            ISNULL(SUM(CASE WHEN B.Day BETWEEN @begDate AND @endDate THEN B.SignTs
                            ELSE 0
                       END) , 0) SignTs ,
            ISNULL(SUM(CASE WHEN B.Day BETWEEN @begDate AND @endDate THEN B.SignArea
                            ELSE 0
                       END) , 0) SignArea ,
            ISNULL(SUM(CASE WHEN B.Day BETWEEN @begDate AND @endDate THEN B.SignAmount
                            ELSE 0
                       END) , 0) AS SignAmount ,
           --当期回款
            ISNULL(SUM(CASE WHEN B.Day BETWEEN @begDate AND @endDate THEN B.RePayAmount
                            ELSE 0
                       END) , 0) RePayAmount ,
           --累计回款
            ISNULL(SUM(CASE WHEN B.Day <= @endDate THEN B.RePayAmount
                            ELSE 0
                       END) , 0) LjRePayAmount ,
           --累计净认购
            ISNULL(SUM(CASE WHEN B.Day <= @endDate THEN B.OrderTs
                            ELSE 0
                       END) , 0) LjOrderTs ,
            ISNULL(SUM(CASE WHEN B.Day <= @endDate THEN B.OrderArea
                            ELSE 0
                       END) , 0) LjOrderArea ,
            ISNULL(SUM(CASE WHEN B.Day <= @endDate THEN B.OrderAmount
                            ELSE 0
                       END) , 0) AS LjOrderAmount ,
           --累计签约
            ISNULL(SUM(CASE WHEN B.Day <= @endDate THEN B.SignTs
                            ELSE 0
                       END) , 0) LjSignTs ,
            ISNULL(SUM(CASE WHEN B.Day <= @endDate THEN B.SignArea
                            ELSE 0
                       END) , 0) LjSignArea ,
            ISNULL(SUM(CASE WHEN B.Day <= @endDate THEN B.SignAmount
                            ELSE 0
                       END) , 0) AS LjSignAmount ,
           --已推未售        
            ISNULL(SUM(CASE WHEN B.Day BETWEEN @chuE AND @endDate THEN B.SupplyTs
                            ELSE 0
                       END) , 0) - ISNULL(SUM(CASE WHEN B.Day BETWEEN @chuE AND @endDate THEN B.OrderTs
                                                   ELSE 0
                                              END) , 0) AS PushNoSaleTs ,
            ISNULL(SUM(CASE WHEN B.Day BETWEEN @chuE AND @endDate THEN B.SupplyArea
                            ELSE 0
                       END) , 0) - ISNULL(SUM(CASE WHEN B.Day BETWEEN @chuE AND @endDate THEN B.OrderArea
                                                   ELSE 0
                                              END) , 0) AS PushNoSaleArea ,
            ISNULL(SUM(CASE WHEN B.Day BETWEEN @chuE AND @endDate THEN B.SupplyAmount
                            ELSE 0
                       END) , 0) - ISNULL(SUM(CASE WHEN B.Day BETWEEN @chuE AND @endDate THEN B.OrderAmount
                                                   ELSE 0
                                              END) , 0) AS PushNoSaleAmount ,
           --已认未签
            ISNULL(SUM(CASE WHEN B.Day BETWEEN @chuE AND @endDate THEN B.OrderTs
                            ELSE 0
                       END) , 0) - ISNULL(SUM(CASE WHEN B.Day BETWEEN @chuE AND @endDate THEN B.SignTs
                                                   ELSE 0
                                              END) , 0) AS OrderNoSignTs ,
            ISNULL(SUM(CASE WHEN B.Day BETWEEN @chuE AND @endDate THEN B.OrderArea
                            ELSE 0
                       END) , 0) - ISNULL(SUM(CASE WHEN B.Day BETWEEN @chuE AND @endDate THEN B.SignArea
                                                   ELSE 0
                                              END) , 0) AS OrderNoSignArea ,
            ISNULL(SUM(CASE WHEN B.Day BETWEEN @chuE AND @endDate THEN B.OrderAmount
                            ELSE 0
                       END) , 0) - ISNULL(SUM(CASE WHEN B.Day BETWEEN @chuE AND @endDate THEN B.SignAmount
                                                   ELSE 0
                                              END) , 0) AS OrderNoSignAmount
   INTO     #BudgetCooperate
   FROM     s_Budget A WITH ( NOLOCK )
   INNER JOIN s_BudgetCooperate B WITH ( NOLOCK ) ON B.BudgetGUID = A.BudgetGUID
   INNER JOIN #Proj p WITH ( NOLOCK ) ON p.projGUID = B.OrgGUID
   WHERE    A.BudgetTypeGUID = '合作预算'
   GROUP BY A.BUGUID ,
            B.OrgGUID;
    --期初数据
   SELECT   A.BUGUID ,
            B.OrgGUID ,
            A.Year ,
            B.Month ,
           --已推未售
            SUM(B.PushNoSaleTs) AS PushNoSaleTs ,
            SUM(B.PushNoSaleArea) AS PushNoSaleArea ,
            SUM(B.PushNoSaleAmount) AS PushNoSaleAmount ,
           --已认未签
            SUM(B.OrderNoSignTs) AS OrderNoSignTs ,
            SUM(B.OrderNoSignArea) AS OrderNoSignArea ,
            SUM(B.OrderNoSignAmount) AS OrderNoSignAmount
   INTO     #BudgetCooperateQc
   FROM     s_Budget A WITH ( NOLOCK )
   INNER JOIN s_BudgetCooperateQc B WITH ( NOLOCK ) ON B.BudgetGUID = A.BudgetGUID
   INNER JOIN #Proj p WITH ( NOLOCK ) ON p.projGUID = B.OrgGUID
   WHERE    A.BudgetTypeGUID = '合作预算'
            AND A.Year = YEAR(@endDate)
            AND B.Month = MONTH(@endDate)
   GROUP BY A.BUGUID ,
            B.OrgGUID ,
            A.Year ,
            B.Month;
   SELECT   A.BUGUID ,
            A.FQProjGUID ,
           --供货
            B.SupplyTs ,
            B.SupplyArea ,
            B.SupplyAmount ,
           --当期认购
            B.OrderTs ,
            B.OrderArea ,
            CASE WHEN ISNULL(A.CheckRate , 0) = 0 THEN 0
                 WHEN ISNULL(B.OrderArea , 0) = 0 THEN 0
                 ELSE B.OrderAmount * 100 / A.CheckRate / B.OrderArea
            END AS OrderAveragePrice ,
            CASE WHEN ISNULL(A.CheckRate , 0) = 0 THEN 0
                 ELSE B.OrderAmount * 100 / A.CheckRate
            END AS OrderAmount ,
           --当期签约
            B.SignTs ,
            B.SignArea ,
            CASE WHEN ISNULL(A.CheckRate , 0) = 0 THEN 0
                 WHEN ISNULL(B.SignArea , 0) = 0 THEN 0
                 ELSE B.SignAmount * 100 / A.CheckRate / B.SignArea
            END AS SignAveragePrice ,
            CASE WHEN ISNULL(A.CheckRate , 0) = 0 THEN 0
                 ELSE B.SignAmount * 100 / A.CheckRate
            END AS SignAmount ,
           --累计认购
            B.LjOrderTs ,
            B.LjOrderArea ,
            CASE WHEN ISNULL(A.CheckRate , 0) = 0 THEN 0
                 WHEN ISNULL(B.LjOrderArea , 0) = 0 THEN 0
                 ELSE B.LjOrderAmount * 100 / A.CheckRate / B.LjOrderArea
            END AS LjOrderAveragePrice ,
            CASE WHEN ISNULL(A.CheckRate , 0) = 0 THEN 0
                 ELSE B.LjOrderAmount * 100 / A.CheckRate
            END AS LjOrderAmount ,
           --累计签约
            B.LjSignTs ,
            B.LjSignArea ,
            CASE WHEN ISNULL(A.CheckRate , 0) = 0 THEN 0
                 WHEN ISNULL(B.LjSignArea , 0) = 0 THEN 0
                 ELSE B.LjSignAmount * 100 / A.CheckRate / B.LjSignArea
            END AS LjSignAveragePrice ,
            CASE WHEN ISNULL(A.CheckRate , 0) = 0 THEN 0
                 ELSE B.LjSignAmount * 100 / A.CheckRate
            END AS LjSignAmount ,
            B.RePayAmount ,--当期回款
            B.LjRePayAmount ,--累计回款
           --已推未售
            ISNULL(C.PushNoSaleTs , 0) + ISNULL(B.PushNoSaleTs , 0) AS PushNoSaleTs ,
            ISNULL(C.PushNoSaleArea , 0) + ISNULL(B.PushNoSaleArea , 0) AS PushNoSaleArea ,
            ISNULL(C.PushNoSaleAmount , 0) + ISNULL(B.PushNoSaleAmount , 0) AS PushNoSaleYj ,
            CASE WHEN ISNULL(A.CheckRate , 0) = 0 THEN 0
                 ELSE ( ISNULL(B.PushNoSaleAmount , 0) + ISNULL(C.PushNoSaleAmount , 0) ) * 100 / A.CheckRate
            END AS PushNoSaleAmount ,
            ( ISNULL(B.PushNoSaleAmount , 0) + ISNULL(C.PushNoSaleAmount , 0) ) * 100 / NULLIF(A.CheckRate , 0) / NULLIF(ISNULL(C.PushNoSaleArea , 0)
                                                                                                                         + ISNULL(B.PushNoSaleArea , 0) , 0) AS PushNoSaleAveragePrice ,
           --已认未签
            ISNULL(B.OrderNoSignTs , 0) + ISNULL(C.OrderNoSignTs , 0) AS OrderNoSignTs ,
            ISNULL(B.OrderNoSignArea , 0) + ISNULL(C.OrderNoSignArea , 0) AS OrderNoSignArea ,
            ISNULL(B.OrderNoSignAmount , 0) + ISNULL(C.OrderNoSignAmount , 0) AS OrderNoSignYj ,
            CASE WHEN ISNULL(A.CheckRate , 0) = 0 THEN 0
                 ELSE ( ISNULL(B.OrderNoSignAmount , 0) + ISNULL(C.OrderNoSignAmount , 0) ) * 100 / A.CheckRate
            END AS OrderNoSignAmount ,
            ( ISNULL(B.OrderNoSignAmount , 0) + ISNULL(C.OrderNoSignAmount , 0) ) * 100 / NULLIF(A.CheckRate , 0) / NULLIF(ISNULL(B.OrderNoSignArea , 0)
                                                                                                                           + ISNULL(C.OrderNoSignArea , 0) , 0) AS OrderNoSignAveragePrice
   INTO     #HZXM
   FROM     #BU A
   LEFT JOIN #BudgetCooperate B WITH ( NOLOCK ) ON B.BUGUID = A.BUGUID
                                                   AND B.OrgGUID = A.FQProjGUID
   LEFT JOIN #BudgetCooperateQc C WITH ( NOLOCK ) ON C.BUGUID = A.BUGUID
                                                     AND C.OrgGUID = A.FQProjGUID;
   SELECT   T2.RoomGUID
   INTO     #Attach_room
   FROM     s_OCAttachRoom T2 WITH ( NOLOCK )
   INNER JOIN dbo.#RgRoomGUID T1 WITH ( NOLOCK ) ON T1.OrderGUID = T2.SaleGUID;
        --UNION ALL
        --SELECT  T2.RoomGUID
        --FROM    s_OCAttachRoom T2 WITH ( NOLOCK )
        --        INNER JOIN #QYRoomGUID T1 WITH ( NOLOCK ) ON T1.ContractGUID = T2.SaleGUID;
        --SELECT  a.BldGUID ,
        --        a.YszDate
        --INTO    #p_Building20190628
        --FROM    p_Building a WITH ( NOLOCK )
        --        INNER JOIN #Proj P ON P.projGUID = a.ProjGUID
        --WHERE   IsBld = 1
        --        AND a.YszDate IS NOT NULL;
    --SELECT  a.ProjGUID FQProjGUID ,
    --        b.BldGUID ,
    --        a.RoomGUID ,
    --        a.BProductTypeCode ,
            --b.YszDate ,
            --a.BldArea ,
            --a.TotalDj ,
            --a.IsZiChiStatus
    --INTO    #p_Room20190628
    --FROM    p_Room a WITH ( NOLOCK )
    --        JOIN #p_Building20190628 b ON a.BldGUID = b.BldGUID;
        --SELECT  b.ProjGUID ,
        --        b.BldGUID ,
        --        b.TotalDj ,
        --        b.BProductTypeCode ,
        --        b.YszDate ,
        --        b.BldArea ,
        --        b.TotalDj ,
        --        b.IsZiChiStatus
        --INTO    #p_Room20190628
        --FROM    #RoomGUID a
        --        INNER  JOIN dbo.p_Room b ON b.RoomGUID = a.RoomGUID
        --        JOIN #p_Building20190628 bld ON b.BldGUID = bld.BldGUID;
        --SELECT DISTINCT
        --        t1.RoomGUID
        --INTO    #TMRoom
        --FROM    dbo.p_Room t1 WITH ( NOLOCK )
        --        LEFT JOIN #RgRoomGUID t2 WITH ( NOLOCK ) ON t2.RoomGUID = t1.RoomGUID
        --        LEFT JOIN #QYRoomGUID t3 ON t1.RoomGUID = t3.RoomGUID
        --WHERE   t2.OrderGUID IS NULL
        --        AND t3.ContractGUID IS NULL
        --        AND ISNULL(t1.TotalDj, 0) = 0
        --        AND t1.IsZiChiStatus = 0;
    /* 草签信息 */
        --SELECT  t2.BldGUID ,
        --        t2.BProductTypeCode
        --   --当期
        --        ,
        --        SUM(CASE WHEN B.TradeType = '签约'
        --                      AND B.CloseReason IN ( '转草签', '草签退房' )
        --                      AND B.QsDate BETWEEN @begDate AND @endDate
        --                 THEN B.Ts
        --                 ELSE 0
        --            END) AS cqts ,
        --        SUM(CASE WHEN B.TradeType = '签约'
        --                      AND B.CloseReason IN ( '转草签', '草签退房' )
        --                      AND B.QsDate BETWEEN @begDate AND @endDate
        --                 THEN B.BldArea
        --                 ELSE 0
        --            END) AS cqarea ,
        --        SUM(CASE WHEN B.TradeType = '签约'
        --                      AND B.CloseReason IN ( '转草签', '草签退房' )
        --                      AND B.QsDate BETWEEN @begDate AND @endDate
        --                 THEN B.RmbAmount
        --                 ELSE 0
        --            END) AS cqtotal ,
        ----SUM(CASE WHEN B.TradeType = '签约'
        ----              AND B.CloseReason = '草签退房'
        ----              AND B.QsDate BETWEEN @begDate AND @endDate THEN B.Ts
        ----         ELSE 0
        ----    END) AS cqtfts ,
        ----SUM(CASE WHEN B.TradeType = '签约'
        ----              AND B.CloseReason = '草签退房'
        ----              AND B.QsDate BETWEEN @begDate AND @endDate
        ----         THEN B.BldArea
        ----         ELSE 0
        ----    END) AS cqtfarea ,
        ----SUM(CASE WHEN B.TradeType = '签约'
        ----              AND B.CloseReason = '草签退房'
        ----              AND B.QsDate BETWEEN @begDate AND @endDate
        ----         THEN B.RmbAmount
        ----         ELSE 0
        ----    END) AS cqtftotal
        --   --从推盘日起 累计
        ----,
        --        SUM(CASE WHEN B.TradeType = '签约'
        --                      AND B.CloseReason IN ( '转草签', '草签退房' )
        --                      AND B.QsDate <= @endDate THEN B.Ts
        --                 ELSE 0
        --            END) AS cqts_all ,
        --        SUM(CASE WHEN B.TradeType = '签约'
        --                      AND B.CloseReason IN ( '转草签', '草签退房' )
        --                      AND B.QsDate <= @endDate THEN B.BldArea
        --                 ELSE 0
        --            END) AS cqarea_all ,
        --        SUM(CASE WHEN B.TradeType = '签约'
        --                      AND B.CloseReason IN ( '转草签', '草签退房' )
        --                      AND B.QsDate <= @endDate THEN B.RmbAmount
        --                 ELSE 0
        --            END) AS cqtotal_all
        --  -- 从推盘日起 目前净草签
        --        ,
        --        SUM(CASE WHEN B.TradeType = '签约'
        --                      AND B.CloseReason <> '面积补差'
        --                 -- AND B.CloseReason LIKE '%草签%'
        --                 -- AND B.CloseReason IN( '转草签','特批折扣','草签转签约','草签退房')--/2020.2.19调整/
        --                      AND B.QsDate <= @endDate THEN B.Ts
        --                 ELSE 0
        --            END) AS cqjhts_all ,
        --        SUM(CASE WHEN B.TradeType = '签约'
        --                      AND B.CloseReason <> '面积补差'
        --                 -- AND B.CloseReason LIKE '%草签%'
        --                 -- AND B.CloseReason IN( '转草签','特批折扣','草签转签约','草签退房')--/2020.2.19调整/
        --                      AND B.QsDate <= @endDate THEN B.BldArea
        --                 ELSE 0
        --            END) AS cqjharea_all ,
        --        SUM(CASE WHEN B.TradeType = '签约'
        --                      AND B.CloseReason <> '面积补差'
        --                  --AND B.CloseReason LIKE '%草签%'
        --                  --AND B.CloseReason IN( '转草签','特批折扣','草签转签约','草签退房')--/2020.2.19调整/
        --                      AND B.QsDate <= @endDate THEN B.RmbAmount
        --                 ELSE 0
        --            END) AS cqjhtotal_all
        --   --累计草签退房
        ----SUM(CASE WHEN B.TradeType = '签约'
        ----              AND B.CloseReason = '草签退房'
        ----              AND B.QsDate <= @endDate THEN B.Ts
        ----         ELSE 0
        ----    END) AS cqtfts_all ,
        ----SUM(CASE WHEN B.TradeType = '签约'
        ----              AND B.CloseReason = '草签退房'
        ----              AND B.QsDate <= @endDate THEN B.BldArea
        ----         ELSE 0
        ----    END) AS cqtfarea_all ,
        ----SUM(CASE WHEN B.TradeType = '签约'
        ----              AND B.CloseReason = '草签退房'
        ----              AND B.QsDate <= @endDate THEN B.RmbAmount
        ----         ELSE 0
        ----    END) AS cqtftotal_all
        --INTO    #cq
        --FROM    dbo.s_Contract WITH ( NOLOCK )
        --        INNER JOIN #Proj p ON p.projGUID = s_Contract.ProjGUID
        --        INNER JOIN p_Room t2 WITH ( NOLOCK ) ON s_Contract.RoomGUID = t2.RoomGUID
        --        INNER JOIN dbo.s_SaleHSData B WITH ( NOLOCK ) ON s_Contract.TradeGUID = B.TradeGUID
        --                                                      AND ContractGUID = B.SaleGUID
        --WHERE   B.TradeType = '签约'
        --        AND IsCQ = 1
        --GROUP BY t2.BldGUID ,
        --        t2.BProductTypeCode;
    /* 草签业绩 */
   DECLARE @bdate DATE ,
      @edate DATE;
   SELECT   @bdate = DATEADD(DAY , -1 , @begDate) ,
            @edate = @endDate;
   PRINT @bdate;
   PRINT @edate;
   SELECT   ProjectGUID ,
            s_SpecialTransactionRoom.RoomGUID ,
            CASE WHEN ISNULL(s_Order.QSDate , '2020-10-01') >= '2020-10-01'
                      AND CAST(s_Order.QSDate AS DATE) BETWEEN CAST(StartDate AS DATE)
                                                       AND     CAST(s_SpecialTransaction.EndDate AS DATE) THEN 1
                 ELSE 0
            END AS bxflag ,
            s_Order.TradeGUID ,
            StartDate ,
            s_SpecialTransaction.EndDate ,
            ProgrammeName,QSDate
   INTO     #c
   FROM     dbo.s_SpecialTransaction
   INNER JOIN dbo.s_SpecialTransactionRoom ON s_SpecialTransactionRoom.SpecialTransactionGuid = s_SpecialTransaction.SpecialTransactionGUID
   INNER JOIN #Proj ON ProjGUID = ProjectGUID
   LEFT JOIN s_Trade ON s_Trade.RoomGUID = s_SpecialTransactionRoom.RoomGUID
   LEFT JOIN s_Order ON s_Order.OrderGUID = RGOrderGUID
   WHERE    ApproveState = '已审批';
   SELECT   aa.SaleGUID ,
            SUM(CASE WHEN ( ItemName = '银行按揭'
                          OR ItemName = '%装修款按揭%'--20200820调整新增装修款按揭
                          OR ItemName = '车位信用卡分期'
                          OR ItemName = '精装修贷款' )
                          AND CAST(GetDate AS DATE) BETWEEN @begDate AND @endDate THEN aa.RmbAmount
                     ELSE 0
                END) AS AjHkTotal ,
            SUM(CASE WHEN ItemName = '公积金'
                          AND CAST(GetDate AS DATE) BETWEEN @begDate AND @endDate THEN aa.RmbAmount
                     ELSE 0
                END) AS GjjHkTotal ,
            SUM(CASE WHEN ItemType = '非贷款类房款'
                          AND ItemName <> '补差款'
                          AND CAST(GetDate AS DATE) BETWEEN @begDate AND @endDate THEN aa.RmbAmount
                     ELSE 0
                END) AS HkTotal ,
            SUM(CASE WHEN ( aa.ItemType = '非贷款类房款'
                          AND ItemName = '补差款' )
                          AND CAST(GetDate AS DATE) BETWEEN @begDate AND @endDate THEN aa.RmbAmount
                     ELSE 0
                END) AS bckTotal ,
            SUM(CASE WHEN ( ItemName = '银行按揭'
                          OR ItemName = '%装修款按揭%'--20200820调整新增装修款按揭
                          OR ItemName = '车位信用卡分期'
                          OR ItemName = '精装修贷款' )
                          AND CAST(GetDate AS DATE) <= @endDate THEN aa.RmbAmount
                     ELSE 0
                END) AS ljAjHkTotal ,
            SUM(CASE WHEN ( aa.ItemType = '非贷款类房款'
                          AND ItemName = '补差款' )
                          AND CAST(GetDate AS DATE) <= @endDate THEN aa.RmbAmount
                     ELSE 0
                END) AS ljbckTotal ,
            SUM(CASE WHEN ItemName = '公积金'
                          AND CAST(GetDate AS DATE) <= @endDate THEN aa.RmbAmount
                     ELSE 0
                END) AS ljGjjHkTotal ,
            SUM(CASE WHEN ItemType = '非贷款类房款'
                          AND ItemName <> '补差款'
                          AND CAST(GetDate AS DATE) <= @endDate THEN aa.RmbAmount
                     ELSE 0
                END) AS ljHkTotal ,
            SUM(CASE WHEN CAST(GetDate AS DATE) <= @bdate
                          AND ( t02.TradeStatus = '激活'
                          OR ISNULL(t3.CloseDate , t4.CloseDate) > @bdate ) THEN aa.Amount
                     ELSE 0
                END) AS getamount_begin ,
            SUM(CASE WHEN CAST(GetDate AS DATE) <= @edate
                          AND ( t02.TradeStatus = '激活'
                          OR ISNULL(t3.CloseDate , t4.CloseDate) > @edate ) THEN aa.Amount
                     ELSE 0
                END) AS getamount_end ,
            --SUM(CASE WHEN bxflag = 1
            --              AND GetDate BETWEEN @begDate AND @endDate THEN aa.Amount
            --         ELSE 0
            --    END) AS getamount_bx ,
            --SUM(CASE WHEN bxflag = 1
            --              AND GetDate <= @endDate THEN aa.Amount
            --         ELSE 0
            --    END) AS getamount_bx_1
            SUM(CASE WHEN bxflag = 1
                          AND CAST(GetDate AS DATE) BETWEEN @begDate AND @endDate THEN aa.Amount
                     ELSE 0
                END) AS getamount_bx ,
            SUM(CASE WHEN bxflag = 1
                          AND CAST(GetDate AS DATE) <= @endDate THEN aa.Amount
                     ELSE 0
                END) AS getamount_bx_1 ,
            SUM(CASE WHEN bxflag = 1
                          AND CAST(GetDate AS DATE) < @begDate THEN aa.Amount
                     ELSE 0
                END) AS getamount_bx_begin
   INTO     #getintemp
   FROM     s_Getin aa WITH ( NOLOCK )
   INNER JOIN #BU P WITH ( NOLOCK ) ON P.FQProjGUID = aa.ProjGUID
   INNER JOIN dbo.s_Trade t02 WITH ( NOLOCK ) ON aa.SaleGUID = t02.TradeGUID
   LEFT JOIN s_Contract t3 WITH ( NOLOCK ) ON t02.ContractGUID = t3.ContractGUID
   LEFT JOIN s_Order t4 WITH ( NOLOCK ) ON t02.RGOrderGUID = t4.OrderGUID
   LEFT JOIN #c ON #c.TradeGUID = t02.TradeGUID
   WHERE    aa.ItemType IN ( '贷款类房款' , '非贷款类房款' )
            AND ISNULL(aa.Status , '') <> '作废'
   GROUP BY aa.SaleGUID;
 --select top 100 * from  #getintemp WHERE SaleGUID='2EFEA685-314B-EB11-B392-005056834EAD'
   SELECT DISTINCT
            s_Order.TradeGUID ,
            Roominfo ,
            #c.RoomGUID ,
            BldGUID ,
            CONVERT(VARCHAR(100) , ExecDate , 23) ExecDate
   INTO     #BG
   FROM     #c
   INNER JOIN dbo.s_SaleModiApply ON s_SaleModiApply.RoomGUID = #c.RoomGUID
   INNER JOIN #BU p ON p.FQProjGUID = s_SaleModiApply.ProjGUID
   INNER JOIN p_Room ON p_Room.RoomGUID = #c.RoomGUID
   INNER  JOIN s_Order ON SaleGUID = OrderGUID
   WHERE    ApplyType = '增减权益人'
            AND CAST(ExecDate AS DATE) <= @endDate
            AND bxflag = 1
   UNION
   SELECT DISTINCT
            s_Contract.TradeGUID ,
            Roominfo ,
            #c.RoomGUID ,
            BldGUID ,
            CONVERT(VARCHAR(100) , ExecDate , 23) ExecDate
   FROM     #c
   INNER  JOIN dbo.s_Contract ON s_Contract.RoomGUID = #c.RoomGUID
   INNER JOIN dbo.s_SaleModiApply ON SaleGUID = ContractGUID
   INNER JOIN p_Room ON p_Room.RoomGUID = #c.RoomGUID
   INNER JOIN #BU p ON p.FQProjGUID = s_SaleModiApply.ProjGUID
   WHERE    ApplyType = '增减权益人'
            AND CAST(ExecDate AS DATE) <= @endDate
            AND bxflag = 1;
   SELECT   CASE WHEN ISNULL(QSDate , '2020-10-01') >= '2020-10-01'
                      AND CAST(QSDate AS DATE) BETWEEN CAST(StartDate AS DATE) AND CAST(EndDate AS DATE)
                      AND ExecDate >= @endDate THEN 1
                 ELSE 0
            END AS bxflag ,
            #c.TradeGUID ,
            #c.RoomGUID ,
            ProjectGUID ,
            ProgrammeName ,
            StartDate ,
            EndDate
   INTO     #d
   FROM     #c
   LEFT JOIN #BG ON #BG.TradeGUID = #c.TradeGUID;
   SELECT   a.BldGUID ,
            a.BProductTypeCode ,
            ISNULL(SUM(CASE WHEN bx.TradeGUID IS NOT NULL
                                 AND ExecDate <= @endDate
                                 AND CAST(b.QsDate AS DATE) <= @endDate THEN b.RmbAmount
                            WHEN bx.TradeGUID IS NOT NULL
                                 AND ExecDate > @endDate
                                 AND CAST(b.QsDate AS DATE) <= @endDate THEN ABS(hk.getamount_bx_1) * b.Ts
                            WHEN bx.TradeGUID IS   NULL
                                 AND CAST(b.QsDate AS DATE) <= @endDate THEN ABS(hk.getamount_bx_1) * b.Ts
                            ELSE 0
                       END) , 0) - ISNULL(SUM(CASE WHEN bx.TradeGUID IS NOT NULL
                                                        AND ExecDate <= @begDate
                                                        AND CAST(b.QsDate AS DATE) < @begDate THEN b.RmbAmount
                                                   WHEN bx.TradeGUID IS NOT NULL
                                                        AND ExecDate > @begDate
                                                        AND CAST(b.QsDate AS DATE) < @begDate THEN ABS(hk.getamount_bx_begin) * b.Ts
                                                   WHEN bx.TradeGUID IS   NULL
                                                        AND CAST(b.QsDate AS DATE) < @begDate THEN ABS(hk.getamount_bx_begin) * b.Ts
                                                   ELSE 0
                                              END) , 0) AS RgTotal ,
            SUM(CASE WHEN bx.TradeGUID IS NOT NULL THEN 1
                     ELSE 0
                END) AS bgstatus ,
            SUM(CASE WHEN CAST(b.QsDate AS DATE) BETWEEN @begDate AND @endDate THEN b.Ts
                     ELSE 0
                END) ts ,
            SUM(CASE WHEN CAST(b.QsDate AS DATE) BETWEEN @begDate AND @endDate THEN b.BldArea
                     ELSE 0
                END) area ,
            ISNULL(SUM(CASE WHEN bx.TradeGUID IS NOT NULL
                                 AND ExecDate <= @endDate
                                 AND CAST(b.QsDate AS DATE) <= @endDate THEN b.RmbAmount
                            WHEN bx.TradeGUID IS NOT NULL
                                 AND ExecDate > @endDate
                                 AND CAST(b.QsDate AS DATE) <= @endDate THEN ABS(hk.getamount_bx_1) * b.Ts
                            WHEN bx.TradeGUID IS   NULL
                                 AND CAST(b.QsDate AS DATE) <= @endDate THEN ABS(hk.getamount_bx_1) * b.Ts
                            ELSE 0
                       END) , 0) rgtotal_lj ,
            SUM(CASE WHEN CAST(b.QsDate AS DATE) <= @endDate THEN b.Ts
                     ELSE 0
                END) ts_lj ,
            SUM(CASE WHEN CAST(b.QsDate AS DATE) <= @endDate THEN b.BldArea
                     ELSE 0
                END) area_lj ,
            SUM(CASE WHEN b.TradeType = '签约'
                          AND c.IsCQ = 0
                          AND CAST(b.QsDate AS DATE) BETWEEN @begDate AND @endDate THEN b.Ts
                     ELSE 0
                END) AS QyCount ,
            SUM(ISNULL(CASE WHEN b.TradeType = '签约'
                                 AND c.IsCQ = 0 THEN CASE WHEN bx.TradeGUID IS NOT NULL
                                                               AND ExecDate <= @endDate
                                                               AND CAST(b.QsDate AS DATE) <= @endDate THEN b.RmbAmount
                                                          WHEN bx.TradeGUID IS NOT NULL
                                                               AND ExecDate > @endDate
                                                               AND CAST(b.QsDate AS DATE) <= @endDate THEN ABS(hk.getamount_bx_1) * b.Ts
                                                          WHEN bx.TradeGUID IS   NULL
                                                               AND CAST(b.QsDate AS DATE) <= @endDate THEN ABS(hk.getamount_bx_1) * b.Ts
                                                          ELSE 0
                                                     END
                            ELSE 0
                       END , 0)) - SUM(ISNULL(CASE WHEN b.TradeType = '签约'
                                                        AND c.IsCQ = 0
                                                   THEN CASE WHEN bx.TradeGUID IS NOT NULL
                                                                  AND ExecDate <= @begDate
                                                                  AND CAST(b.QsDate AS DATE) <= @begDate THEN b.RmbAmount
                                                             WHEN bx.TradeGUID IS NOT NULL
                                                                  AND ExecDate > @begDate
                                                                  AND CAST(b.QsDate AS DATE) <= @begDate THEN ABS(hk.getamount_bx_begin) * b.Ts
                                                             WHEN bx.TradeGUID IS   NULL
                                                                  AND CAST(b.QsDate AS DATE) <= @begDate THEN ABS(hk.getamount_bx_begin) * b.Ts
                                                             ELSE 0
                                                        END
                                                   ELSE 0
                                              END , 0)) AS QyTotal ,
            SUM(CASE WHEN b.TradeType = '签约'
                          AND c.IsCQ = 0
                          AND CAST(b.QsDate AS DATE) BETWEEN @begDate AND @endDate THEN b.BldArea
                     ELSE 0
                END) AS QyArea ,
            SUM(CASE WHEN b.TradeType = '签约'
                          AND c.IsCQ = 0
                          AND CAST(b.QsDate AS DATE) <= @endDate THEN b.Ts
                     ELSE 0
                END) AS QyCount_lj ,
            SUM(ISNULL(CASE WHEN b.TradeType = '签约'
                                 AND c.IsCQ = 0 THEN CASE WHEN bx.TradeGUID IS NOT NULL
                                                               AND ExecDate <= @endDate
                                                               AND CAST(b.QsDate AS DATE) <= @endDate THEN b.RmbAmount
                                                          WHEN bx.TradeGUID IS NOT NULL
                                                               AND ExecDate > @endDate
                                                               AND CAST(b.QsDate AS DATE) <= @endDate THEN ABS(hk.getamount_bx_1) * b.Ts
                                                          WHEN bx.TradeGUID IS   NULL
                                                               AND CAST(b.QsDate AS DATE) <= @endDate THEN ABS(hk.getamount_bx_1) * b.Ts
                                                          ELSE 0
                                                     END
                            ELSE 0
                       END , 0)) AS QyTotal_lj ,
            SUM(CASE WHEN b.TradeType = '签约'
                          AND c.IsCQ = 0
                          AND CAST(b.QsDate AS DATE) <= @endDate THEN b.BldArea
                     ELSE 0
                END) AS QyArea_lj ,
                --草签信息
            SUM(CASE WHEN b.TradeType = '签约'
                          AND c.IsCQ = 1
                                  -- AND b.CloseReason IN ( '转草签' , '草签退房' )
                          AND CAST(b.QsDate AS DATE) BETWEEN @begDate AND @endDate THEN b.Ts
                     ELSE 0
                END) AS cqts ,
            SUM(CASE WHEN b.TradeType = '签约'
                          AND c.IsCQ = 1
                                  -- AND b.CloseReason IN ( '转草签' , '草签退房' )
                          AND CAST(b.QsDate AS DATE) BETWEEN @begDate AND @endDate THEN b.BldArea
                     ELSE 0
                END) AS cqarea ,
            SUM(ISNULL(CASE WHEN b.TradeType = '签约'
                                 AND c.IsCQ = 1 THEN CASE WHEN bx.TradeGUID IS NOT NULL
                                                               AND ExecDate <= @endDate
                                                               AND CAST(b.QsDate AS DATE) <= @endDate THEN b.RmbAmount
                                                          WHEN bx.TradeGUID IS NOT NULL
                                                               AND ExecDate > @endDate
                                                               AND CAST(b.QsDate AS DATE) <= @endDate THEN ABS(hk.getamount_bx_1) * b.Ts
                                                          WHEN bx.TradeGUID IS   NULL
                                                               AND CAST(b.QsDate AS DATE) <= @endDate THEN ABS(hk.getamount_bx_1) * b.Ts
                                                          ELSE 0
                                                     END
                            ELSE 0
                       END , 0)) - SUM(ISNULL(CASE WHEN b.TradeType = '签约'
                                                        AND c.IsCQ = 1
                                                   THEN CASE WHEN bx.TradeGUID IS NOT NULL
                                                                  AND ExecDate <= @begDate
                                                                  AND CAST(b.QsDate AS DATE) <= @begDate THEN b.RmbAmount
                                                             WHEN bx.TradeGUID IS NOT NULL
                                                                  AND ExecDate > @begDate
                                                                  AND CAST(b.QsDate AS DATE) <= @begDate THEN ABS(hk.getamount_bx_begin) * b.Ts
                                                             WHEN bx.TradeGUID IS   NULL
                                                                  AND CAST(b.QsDate AS DATE) <= @begDate THEN ABS(hk.getamount_bx_begin) * b.Ts
                                                             ELSE 0
                                                        END
                                                   ELSE 0
                                              END , 0)) AS cqtotal ,
            SUM(CASE WHEN b.TradeType = '签约'
                          AND c.IsCQ = 1
                                   --AND b.CloseReason IN ( '转草签' , '草签退房' )
                          AND CAST(b.QsDate AS DATE) <= @endDate THEN b.Ts
                     ELSE 0
                END) AS cqts_all ,
            SUM(CASE WHEN b.TradeType = '签约'
                          AND c.IsCQ = 1
                                   --AND b.CloseReason IN ( '转草签' , '草签退房' )
                          AND CAST(b.QsDate AS DATE) <= @endDate THEN b.BldArea
                     ELSE 0
                END) AS cqarea_all ,
            SUM(CASE WHEN b.TradeType = '签约'
                          AND c.IsCQ = 1 THEN CASE WHEN bx.TradeGUID IS NOT NULL
                                                        AND CAST(b.QsDate AS DATE) <= @endDate THEN b.RmbAmount
                                                   WHEN bx.TradeGUID IS   NULL
                                                        AND CAST(b.QsDate AS DATE) <= @endDate THEN ABS(hk.getamount_bx) * b.Ts
                                                   ELSE 0
                                              END
                     ELSE 0
                END) AS cqtotal_all ,
            SUM(CASE WHEN b.TradeType = '认购'
                          AND CAST(b.QsDate AS DATE) <= @endDate THEN b.Ts
                     ELSE 0
                END) AS LjRgJhCount ,
            SUM(CASE WHEN b.TradeType = '认购'
                          AND CAST(b.QsDate AS DATE) <= @endDate THEN b.BldArea
                     ELSE 0
                END) AS LjRgJhArea ,
            SUM(CASE WHEN b.TradeType = '认购'
                          AND CAST(b.QsDate AS DATE) <= @endDate THEN CASE WHEN bx.TradeGUID IS NOT NULL
                                                                                AND ExecDate <= @endDate
                                                                                AND CAST(b.QsDate AS DATE) <= @endDate THEN b.RmbAmount
                                                                           WHEN bx.TradeGUID IS NOT NULL
                                                                                AND ExecDate > @endDate
                                                                                AND CAST(b.QsDate AS DATE) <= @endDate THEN ABS(hk.getamount_bx_1) * b.Ts
                                                                           WHEN bx.TradeGUID IS   NULL
                                                                                AND CAST(b.QsDate AS DATE) <= @endDate THEN ABS(hk.getamount_bx_1) * b.Ts
                                                                           ELSE 0
                                                                      END
                     ELSE 0
                END) AS LjRgJhTotal
   INTO     #bx
   FROM     s_SaleHSData b WITH ( NOLOCK )
   INNER JOIN #BU p ON p.FQProjGUID = b.ProjGUID
   INNER JOIN p_Room a WITH ( NOLOCK ) ON a.RoomGUID = b.RoomGUID
   LEFT JOIN s_Contract c WITH ( NOLOCK ) ON b.SaleGUID = c.ContractGUID
   LEFT JOIN #BG bx ON bx.TradeGUID = b.TradeGUID
   LEFT JOIN #getintemp hk ON hk.SaleGUID = b.TradeGUID
   WHERE    CAST(b.QsDate AS DATE) <= @endDate
            AND b.TradeGUID IN ( SELECT   TradeGUID
                                 FROM     #d
                                 WHERE    bxflag = 1
                                          AND TradeGUID IS NOT NULL )
   GROUP BY a.BldGUID ,
            a.BProductTypeCode;  
       --  SELECT   * from  #bx
    /* 初始化交易信息 */
   SELECT   *
   INTO     #Trade
   FROM     ( SELECT a.BldGUID ,
                     a.BProductTypeCode ,
                   --期间净认购                  
                     SUM(CASE WHEN CAST(b.QsDate AS DATE) BETWEEN @begDate AND @endDate THEN b.Ts
                              ELSE 0
                         END) AS RgCount ,
                     SUM(CASE WHEN CAST(b.QsDate AS DATE) BETWEEN @begDate AND @endDate THEN b.BldArea
                              ELSE 0
                         END) AS RgArea ,
                     SUM(CASE WHEN CAST(b.QsDate AS DATE) BETWEEN @begDate AND @endDate THEN b.RmbAmount
                              ELSE 0
                         END) AS RgTotal
                            --SUM(CASE WHEN s_Order.CustomerType = '包销'
                            --              AND bx.TradeGUID IS NOT NULL
                            --              AND CAST(b.QsDate AS DATE)   BETWEEN @begDate AND @endDate
                            --         THEN b.RmbAmount
                            --         WHEN s_Order.CustomerType = '包销'
                            --              AND bx.TradeGUID IS   NULL
                            --              AND CAST(b.QsDate AS DATE)   BETWEEN @begDate AND @endDate
                            --         THEN  #getintemp.getamount_bx
                            --         WHEN CAST(b.QsDate AS DATE)   BETWEEN @begDate AND @endDate
                            --         THEN b.RmbAmount
                            --         ELSE 0
                            --    END) AS RgTotal
                   --从推盘日起 累计净认购
                     ,
                     SUM(CASE WHEN CAST(b.QsDate AS DATE) <= @endDate THEN b.Ts
                              ELSE 0
                         END) AS LjRgCount ,
                     SUM(CASE WHEN CAST(b.QsDate AS DATE) <= @endDate THEN b.BldArea
                              ELSE 0
                         END) AS LjRgArea ,
                     SUM(CASE WHEN CAST(b.QsDate AS DATE) <= @endDate THEN b.RmbAmount
                              ELSE 0
                         END) AS LjRgTotal
                   --从推盘日起 累计激活认购（累计认购未签约）
                     ,
                     SUM(CASE WHEN b.TradeType = '认购'
                                   AND CAST(b.QsDate AS DATE) <= @endDate THEN b.Ts
                              ELSE 0
                         END) AS LjRgJhCount ,
                     SUM(CASE WHEN b.TradeType = '认购'
                                   AND CAST(b.QsDate AS DATE) <= @endDate THEN b.BldArea
                              ELSE 0
                         END) AS LjRgJhArea ,
                     SUM(CASE WHEN b.TradeType = '认购'
                                   AND CAST(b.QsDate AS DATE) <= @endDate THEN b.RmbAmount
                              ELSE 0
                         END) AS LjRgJhTotal
                   --当期激活签约                
                     ,
                   -- SUM(CASE WHEN b.TradeType = '签约'
                   --               AND b.CloseReason = '转签约'
                   --               AND b.Ts = 1
                   --               AND CAST(b.QsDate AS DATE)   BETWEEN @begDate AND @endDate
                   --          THEN b.Ts
                   --          ELSE 0
                   --     END) AS QyHtCount ,
                   -- SUM(CASE WHEN b.TradeType = '签约'
                   --               AND b.CloseReason = '转签约'
                   --               AND b.Ts = 1
                   --               AND CAST(b.QsDate AS DATE)   BETWEEN @begDate AND @endDate
                   --          THEN b.RmbAmount
                   --          ELSE 0
                   --     END) AS QyHtTotal ,
                   -- SUM(CASE WHEN b.TradeType = '签约'
                   --               AND b.CloseReason = '转签约'
                   --               AND b.Ts = 1
                   --               AND CAST(b.QsDate AS DATE)   BETWEEN @begDate AND @endDate
                   --          THEN b.BldArea
                   --          ELSE 0
                   --     END) AS QyHtArea
                   ----期间净签约                  
                   -- ,
                     SUM(CASE WHEN b.TradeType = '签约'
                                         -- AND b.CloseReason NOT LIKE '%草签%'
                                 --  AND b.CloseReason NOT IN ( '草签退房' , '草签转签约' , '转草签' )
                                   AND IsCQ = 0
                                   AND CAST(b.QsDate AS DATE) BETWEEN @begDate AND @endDate THEN b.Ts
                              ELSE 0
                         END) AS QyCount ,
                     SUM(CASE WHEN b.TradeType = '签约'
                                          --AND b.CloseReason NOT LIKE '%草签%'
                                  -- AND b.CloseReason NOT IN ( '草签退房' , '草签转签约' , '转草签' )
                                   AND IsCQ = 0
                                   AND CAST(b.QsDate AS DATE) BETWEEN @begDate AND @endDate THEN b.RmbAmount
                              ELSE 0
                         END) AS QyTotal ,
                     SUM(CASE WHEN b.TradeType = '签约'
                                          --AND b.CloseReason NOT LIKE '%草签%'
                                   --AND b.CloseReason NOT IN ( '草签退房' , '草签转签约' , '转草签' )
                                   AND IsCQ = 0
                                   AND CAST(b.QsDate AS DATE) BETWEEN @begDate AND @endDate THEN b.BldArea
                              ELSE 0
                         END) AS QyArea
                   --从推盘日期起累计净签约                  
                     ,
                     SUM(CASE WHEN b.TradeType = '签约'
                                          --AND b.CloseReason NOT LIKE '%草签%'
                                  -- AND b.CloseReason NOT IN ( '草签退房' , '草签转签约' , '转草签' )
                                   AND IsCQ = 0
                                   AND CAST(b.QsDate AS DATE) <= @endDate THEN b.Ts
                              ELSE 0
                         END) AS LjQyCount ,
                     SUM(CASE WHEN b.TradeType = '签约'
                                          --AND b.CloseReason NOT LIKE '%草签%'
                                  -- AND b.CloseReason NOT IN ( '草签退房' , '草签转签约' , '转草签' )
                                   AND IsCQ = 0
                                   AND CAST(b.QsDate AS DATE) <= @endDate THEN b.RmbAmount
                              ELSE 0
                         END) AS LjQyTotal ,
                     SUM(CASE WHEN b.TradeType = '签约'
                                          --AND b.CloseReason NOT LIKE '%草签%'
                                  -- AND b.CloseReason NOT IN ( '草签退房' , '草签转签约' , '转草签' )
                                   AND IsCQ = 0
                                   AND CAST(b.QsDate AS DATE) <= @endDate THEN b.BldArea
                              ELSE 0
                         END) AS LjQyArea
                   --期间签约退房                  
                   -- ,
                   -- SUM(CASE WHEN b.TradeType = '签约'
                   --               AND b.CloseReason = '退房'
                   --               AND CAST(b.QsDate AS DATE)   BETWEEN @begDate AND @endDate
                   --          THEN ABS(b.Ts)
                   --          ELSE 0
                   --     END) AS QyTfCount ,
                   -- SUM(CASE WHEN b.TradeType = '签约'
                   --               AND b.CloseReason = '退房'
                   --               AND CAST(b.QsDate AS DATE)   BETWEEN @begDate AND @endDate
                   --          THEN ABS(b.RmbAmount)
                   --          ELSE 0
                   --     END) AS QyTfTotal ,
                   -- SUM(CASE WHEN b.TradeType = '签约'
                   --               AND b.CloseReason = '退房'
                   --               AND CAST(b.QsDate AS DATE)   BETWEEN @begDate AND @endDate
                   --          THEN ABS(b.BldArea)
                   --          ELSE 0
                   --     END) AS QyTfArea
                   ----累计签约退房                  
                   -- ,
                   -- SUM(CASE WHEN b.TradeType = '签约'
                   --               AND b.CloseReason = '退房'
                   --               AND CAST(b.QsDate AS DATE)   <= @endDate THEN ABS(b.Ts)
                   --          ELSE 0
                   --     END) AS LjQyTfCount ,
                   -- SUM(CASE WHEN b.TradeType = '签约'
                   --               AND b.CloseReason = '退房'
                   --               AND CAST(b.QsDate AS DATE)   <= @endDate
                   --          THEN ABS(b.RmbAmount)
                   --          ELSE 0
                   --     END) AS LjQyTfTotal ,
                   -- SUM(CASE WHEN b.TradeType = '签约'
                   --               AND b.CloseReason = '退房'
                   --               AND CAST(b.QsDate AS DATE)   <= @endDate THEN ABS(b.BldArea)
                   --          ELSE 0
                   --     END) AS LjQyTfArea
                   --草签信息
                     ,
                     SUM(CASE WHEN b.TradeType = '签约'
                                   AND c.IsCQ = 1
                                  -- AND b.CloseReason IN ( '转草签' , '草签退房' )
                                   AND CAST(b.QsDate AS DATE) BETWEEN @begDate AND @endDate THEN b.Ts
                              ELSE 0
                         END) AS cqts ,
                     SUM(CASE WHEN b.TradeType = '签约'
                                   AND c.IsCQ = 1
                                  -- AND b.CloseReason IN ( '转草签' , '草签退房' )
                                   AND CAST(b.QsDate AS DATE) BETWEEN @begDate AND @endDate THEN b.BldArea
                              ELSE 0
                         END) AS cqarea ,
                     SUM(CASE WHEN b.TradeType = '签约'
                                   AND c.IsCQ = 1
                                  -- AND b.CloseReason IN ( '转草签' , '草签退房' )
                                   AND CAST(b.QsDate AS DATE) BETWEEN @begDate AND @endDate THEN b.RmbAmount
                              ELSE 0
                         END) AS cqtotal ,
                     SUM(CASE WHEN b.TradeType = '签约'
                                   AND c.IsCQ = 1
                                   --AND b.CloseReason IN ( '转草签' , '草签退房' )
                                   AND CAST(b.QsDate AS DATE) <= @endDate THEN b.Ts
                              ELSE 0
                         END) AS cqts_all ,
                     SUM(CASE WHEN b.TradeType = '签约'
                                   AND c.IsCQ = 1
                                   --AND b.CloseReason IN ( '转草签' , '草签退房' )
                                   AND CAST(b.QsDate AS DATE) <= @endDate THEN b.BldArea
                              ELSE 0
                         END) AS cqarea_all ,
                     SUM(CASE WHEN b.TradeType = '签约'
                                   AND c.IsCQ = 1
                                  -- AND b.CloseReason IN ( '转草签' , '草签退房' )
                                   AND CAST(b.QsDate AS DATE) <= @endDate THEN b.RmbAmount
                              ELSE 0
                         END) AS cqtotal_all
          -- 从推盘日起 目前净草签
                     ,
                     SUM(CASE WHEN b.TradeType = '签约'
                                   AND c.IsCQ = 1
                                   AND b.CloseReason <> '面积补差'
                                   AND CAST(b.QsDate AS DATE) <= @endDate THEN b.Ts
                              ELSE 0
                         END) AS cqjhts_all ,
                     SUM(CASE WHEN b.TradeType = '签约'
                                   AND c.IsCQ = 1
                                   AND b.CloseReason <> '面积补差'
                                   AND CAST(b.QsDate AS DATE) <= @endDate THEN b.BldArea
                              ELSE 0
                         END) AS cqjharea_all ,
                     SUM(CASE WHEN b.TradeType = '签约'
                                   AND c.IsCQ = 1
                                   AND b.CloseReason <> '面积补差'
                                   AND CAST(b.QsDate AS DATE) <= @endDate THEN b.RmbAmount
                              ELSE 0
                         END) AS cqjhtotal_all
              FROM   s_SaleHSData b WITH ( NOLOCK )
              INNER JOIN #Proj p ON p.projGUID = b.ProjGUID
              INNER JOIN p_Room a WITH ( NOLOCK ) ON a.RoomGUID = b.RoomGUID
              LEFT JOIN s_Contract c WITH ( NOLOCK ) ON b.SaleGUID = c.ContractGUID
              WHERE  CAST(b.QsDate AS DATE) <= @endDate
                     AND b.TradeGUID NOT IN ( SELECT  TradeGUID
                                              FROM    #d
                                              WHERE   bxflag = 1
                                                      AND TradeGUID IS NOT NULL )
              GROUP BY a.BldGUID ,
                     a.BProductTypeCode ) moko;
    /* 初始化交易信息 */
   SELECT   *
   INTO     #Trade_fbx
   FROM     ( SELECT a.BldGUID ,
                     a.BProductTypeCode ,
                   --期间净认购                  
                     SUM(CASE WHEN CAST(b.QsDate AS DATE) BETWEEN @begDate AND @endDate THEN b.Ts
                              ELSE 0
                         END) AS RgCount ,
                     SUM(CASE WHEN CAST(b.QsDate AS DATE) BETWEEN @begDate AND @endDate THEN b.BldArea
                              ELSE 0
                         END) AS RgArea ,
                     SUM(CASE WHEN CAST(b.QsDate AS DATE) BETWEEN @begDate AND @endDate THEN b.RmbAmount
                              ELSE 0
                         END) AS RgTotal
                   --从推盘日起 累计净认购
                     ,
                     SUM(CASE WHEN CAST(b.QsDate AS DATE) <= @endDate THEN b.Ts
                              ELSE 0
                         END) AS LjRgCount ,
                     SUM(CASE WHEN CAST(b.QsDate AS DATE) <= @endDate THEN b.BldArea
                              ELSE 0
                         END) AS LjRgArea ,
                     SUM(CASE WHEN CAST(b.QsDate AS DATE) <= @endDate THEN b.RmbAmount
                              ELSE 0
                         END) AS LjRgTotal
                   --从推盘日起 累计激活认购（累计认购未签约）
                     ,
                     SUM(CASE WHEN b.TradeType = '认购'
                                   AND CAST(b.QsDate AS DATE) <= @endDate THEN b.Ts
                              ELSE 0
                         END) AS LjRgJhCount ,
                     SUM(CASE WHEN b.TradeType = '认购'
                                   AND CAST(b.QsDate AS DATE) <= @endDate THEN b.BldArea
                              ELSE 0
                         END) AS LjRgJhArea ,
                     SUM(CASE WHEN b.TradeType = '认购'
                                   AND CAST(b.QsDate AS DATE) <= @endDate THEN b.RmbAmount
                              ELSE 0
                         END) AS LjRgJhTotal ,
                     SUM(CASE WHEN b.TradeType = '签约'
                                         -- AND b.CloseReason NOT LIKE '%草签%'
                                 --  AND b.CloseReason NOT IN ( '草签退房' , '草签转签约' , '转草签' )
                                   AND IsCQ = 0
                                   AND CAST(b.QsDate AS DATE) BETWEEN @begDate AND @endDate THEN b.Ts
                              ELSE 0
                         END) AS QyCount ,
                     SUM(CASE WHEN b.TradeType = '签约'
                                          --AND b.CloseReason NOT LIKE '%草签%'
                                  -- AND b.CloseReason NOT IN ( '草签退房' , '草签转签约' , '转草签' )
                                   AND IsCQ = 0
                                   AND CAST(b.QsDate AS DATE) BETWEEN @begDate AND @endDate THEN b.RmbAmount
                              ELSE 0
                         END) AS QyTotal ,
                     SUM(CASE WHEN b.TradeType = '签约'
                                          --AND b.CloseReason NOT LIKE '%草签%'
                                   --AND b.CloseReason NOT IN ( '草签退房' , '草签转签约' , '转草签' )
                                   AND IsCQ = 0
                                   AND CAST(b.QsDate AS DATE) BETWEEN @begDate AND @endDate THEN b.BldArea
                              ELSE 0
                         END) AS QyArea
                   --从推盘日期起累计净签约                  
                     ,
                     SUM(CASE WHEN b.TradeType = '签约'
                                          --AND b.CloseReason NOT LIKE '%草签%'
                                  -- AND b.CloseReason NOT IN ( '草签退房' , '草签转签约' , '转草签' )
                                   AND IsCQ = 0
                                   AND CAST(b.QsDate AS DATE) <= @endDate THEN b.Ts
                              ELSE 0
                         END) AS LjQyCount ,
                     SUM(CASE WHEN b.TradeType = '签约'
                                          --AND b.CloseReason NOT LIKE '%草签%'
                                  -- AND b.CloseReason NOT IN ( '草签退房' , '草签转签约' , '转草签' )
                                   AND IsCQ = 0
                                   AND CAST(b.QsDate AS DATE) <= @endDate THEN b.RmbAmount
                              ELSE 0
                         END) AS LjQyTotal ,
                     SUM(CASE WHEN b.TradeType = '签约'
                                          --AND b.CloseReason NOT LIKE '%草签%'
                                  -- AND b.CloseReason NOT IN ( '草签退房' , '草签转签约' , '转草签' )
                                   AND IsCQ = 0
                                   AND CAST(b.QsDate AS DATE) <= @endDate THEN b.BldArea
                              ELSE 0
                         END) AS LjQyArea ,
                     SUM(CASE WHEN b.TradeType = '签约'
                                   AND c.IsCQ = 1
                                  -- AND b.CloseReason IN ( '转草签' , '草签退房' )
                                   AND CAST(b.QsDate AS DATE) BETWEEN @begDate AND @endDate THEN b.Ts
                              ELSE 0
                         END) AS cqts ,
                     SUM(CASE WHEN b.TradeType = '签约'
                                   AND c.IsCQ = 1
                                  -- AND b.CloseReason IN ( '转草签' , '草签退房' )
                                   AND CAST(b.QsDate AS DATE) BETWEEN @begDate AND @endDate THEN b.BldArea
                              ELSE 0
                         END) AS cqarea ,
                     SUM(CASE WHEN b.TradeType = '签约'
                                   AND c.IsCQ = 1
                                  -- AND b.CloseReason IN ( '转草签' , '草签退房' )
                                   AND CAST(b.QsDate AS DATE) BETWEEN @begDate AND @endDate THEN b.RmbAmount
                              ELSE 0
                         END) AS cqtotal ,
                     SUM(CASE WHEN b.TradeType = '签约'
                                   AND c.IsCQ = 1
                                   --AND b.CloseReason IN ( '转草签' , '草签退房' )
                                   AND CAST(b.QsDate AS DATE) <= @endDate THEN b.Ts
                              ELSE 0
                         END) AS cqts_all ,
                     SUM(CASE WHEN b.TradeType = '签约'
                                   AND c.IsCQ = 1
                                   --AND b.CloseReason IN ( '转草签' , '草签退房' )
                                   AND CAST(b.QsDate AS DATE) <= @endDate THEN b.BldArea
                              ELSE 0
                         END) AS cqarea_all ,
                     SUM(CASE WHEN b.TradeType = '签约'
                                   AND c.IsCQ = 1
                                  -- AND b.CloseReason IN ( '转草签' , '草签退房' )
                                   AND CAST(b.QsDate AS DATE) <= @endDate THEN b.RmbAmount
                              ELSE 0
                         END) AS cqtotal_all
          -- 从推盘日起 目前净草签
                     ,
                     SUM(CASE WHEN b.TradeType = '签约'
                                   AND c.IsCQ = 1
                                   AND b.CloseReason <> '面积补差'
                                   AND CAST(b.QsDate AS DATE) <= @endDate THEN b.Ts
                              ELSE 0
                         END) AS cqjhts_all ,
                     SUM(CASE WHEN b.TradeType = '签约'
                                   AND c.IsCQ = 1
                                   AND b.CloseReason <> '面积补差'
                                   AND CAST(b.QsDate AS DATE) <= @endDate THEN b.BldArea
                              ELSE 0
                         END) AS cqjharea_all ,
                     SUM(CASE WHEN b.TradeType = '签约'
                                   AND c.IsCQ = 1
                                   AND b.CloseReason <> '面积补差'
                                   AND CAST(b.QsDate AS DATE) <= @endDate THEN b.RmbAmount
                              ELSE 0
                         END) AS cqjhtotal_all
              FROM   s_SaleHSData b WITH ( NOLOCK )
              INNER JOIN #Proj p ON p.projGUID = b.ProjGUID
              INNER JOIN p_Room a WITH ( NOLOCK ) ON a.RoomGUID = b.RoomGUID
              LEFT JOIN s_Contract c WITH ( NOLOCK ) ON b.SaleGUID = c.ContractGUID
              WHERE  CAST(b.QsDate AS DATE) <= @endDate
              GROUP BY a.BldGUID ,
                     a.BProductTypeCode ) moko;
                  -- select   * from  #Trade
    --已开未售回溯(签约口径)  
   SELECT   T1.ProjGUID AS FQProjGUID ,
            T1.BldGUID ,
            T1.BProductTypeCode ,
            SUM(CASE WHEN CAST(bld.YszDate AS DATE) <= @endDate
                              --AND t2.OrderGUID IS NULL
                              --AND t3.ContractGUID IS NULL 
                          THEN 1
                     ELSE 0
                END) AS RgWsCount ,
            SUM(CASE WHEN CAST(bld.YszDate AS DATE) <= @endDate
                              --AND t2.OrderGUID IS NULL
                              --AND t3.ContractGUID IS NULL 
                          THEN T1.BldArea
                     ELSE 0
                END) AS RgWsArea ,
            SUM(CASE WHEN CAST(bld.YszDate AS DATE) <= @endDate
                             -- AND t2.OrderGUID IS NULL
                             -- AND t3.ContractGUID IS NULL 
                          THEN T1.TotalDj
                     ELSE 0
                END) AS RgWsTotalDj
           --已开未售(签约口径)未定价
            ,
            SUM(CASE WHEN --t2.OrderGUID IS NULL
                              --AND t3.ContractGUID IS NULL
                              --AND 
                          ISNULL(T1.TotalDj , 0) = 0 THEN 1
                     ELSE 0
                END) AS RgWsCount_wdj ,
            SUM(CASE WHEN --t2.OrderGUID IS NULL
                              --AND t3.ContractGUID IS NULL
                              --AND
                          ISNULL(T1.TotalDj , 0) = 0 THEN T1.BldArea
                     ELSE 0
                END) AS RgWsArea_wdj
           --已开未售(签约口径)已定价
            ,
            SUM(CASE WHEN --t2.OrderGUID IS NULL
                              --AND t3.ContractGUID IS NULL
                              --AND 
                          ISNULL(T1.TotalDj , 0) <> 0 THEN 1
                     ELSE 0
                END) AS RgWsCount_ydj ,
            SUM(CASE WHEN --t2.OrderGUID IS NULL
                              --AND t3.ContractGUID IS NULL
                              --AND 
                          ISNULL(T1.TotalDj , 0) <> 0 THEN T1.BldArea
                     ELSE 0
                END) AS RgWsArea_ydj ,
            SUM(CASE WHEN  -- t2.OrderGUID IS NULL
                             -- AND t3.ContractGUID IS NULL
                              --AND
                          ISNULL(T1.TotalDj , 0) <> 0 THEN T1.TotalDj
                     ELSE 0
                END) AS RgWsTotalDj_ydj
   INTO     #ykwshs
   FROM     dbo.p_Room T1 WITH ( NOLOCK )
   INNER JOIN dbo.p_Building bld WITH ( NOLOCK ) ON bld.BldGUID = T1.BldGUID
   INNER JOIN #Proj p WITH ( NOLOCK ) ON p.projGUID = T1.ProjGUID
   LEFT JOIN #Attach_room b WITH ( NOLOCK ) ON b.RoomGUID = T1.RoomGUID
   LEFT JOIN #RgRoomGUID t2 WITH ( NOLOCK ) ON T1.RoomGUID = t2.RoomGUID
                --LEFT JOIN #QYRoomGUID t3 WITH ( NOLOCK ) ON T1.RoomGUID = t3.RoomGUID
   WHERE    b.RoomGUID IS NULL
            AND T1.IsZiChiStatus = 0
            AND bld.YszDate IS NOT NULL
            AND t2.OrderGUID IS NULL
                --AND t3.ContractGUID IS  NULL
   GROUP BY T1.ProjGUID ,
            T1.BldGUID ,
            T1.BProductTypeCode;
   SELECT   t01.ProjGUID ,
            t02.BProductTypeCode ,
            t04.PayformName ,
            MAX(t01.PayPercent) AS PayPercent ,
            MAX(t01.Amount) AS Amount ,
            CAST(t01.BeginDate AS DATE) BeginDate ,
            CAST(t01.EndDate AS DATE) EndDate
   INTO     #tmp
   FROM     s_CqControlSet t01 WITH ( NOLOCK )
   INNER JOIN #Proj p WITH ( NOLOCK ) ON p.projGUID = t01.ProjGUID
   INNER JOIN s_CqControlSet2ProdType t02 WITH ( NOLOCK ) ON t01.CqControlSetGUID = t02.CqControlSetGUID
   INNER JOIN s_CqControlSet2PayForm t03 WITH ( NOLOCK ) ON t01.CqControlSetGUID = t03.CqControlSetGUID
   INNER JOIN s_PayForm t04 WITH ( NOLOCK ) ON t03.PayFormGUID = t04.PayFormGUID
   GROUP BY t01.ProjGUID ,
            t02.BProductTypeCode ,
            t04.PayformName ,
            t01.BeginDate ,
            t01.EndDate;
   SELECT   *
   INTO     #temp1
   FROM     ( SELECT ROW_NUMBER() OVER ( PARTITION BY t0.RoomGUID , t0.TradeGUID ORDER BY IsCQ DESC , HtTotal  , Status DESC ) AS num ,
                     CloseDate ,
                     st.TradeGUID ,
                     CASE WHEN CAST(t0.CqBBDate AS DATE) IS NOT NULL THEN CAST(t0.CqBBDate AS DATE)
                          ELSE CAST(t0.CqQsDate AS DATE)
                     END AS CqQsDate ,
                     CAST(QSDate AS DATE) QSDate ,
                     t0.RoomGUID ,
                     PayformName ,
                     IsCQ ,
                     HtTotal ,
                     Status
              FROM   s_Contract t0 WITH ( NOLOCK )
              INNER JOIN #Proj p WITH ( NOLOCK ) ON p.projGUID = t0.ProjGUID
              INNER JOIN s_Trade st WITH ( NOLOCK ) ON st.TradeGUID = t0.TradeGUID ) p
   WHERE    p.num = 1;
   SELECT   ProjGUID ,
            BldGUID ,
            BProductTypeCode ,
            yeji_end - yeji_begin yeji_between ,
            yeji_end yeji_end
   INTO     #tempcq
   FROM     ( SELECT t1.ProjGUID ,
                     t1.BldGUID ,
                     t1.BProductTypeCode ,
                     SUM(CASE WHEN t2.CloseDate <= @bdate
                                   OR t2.Status IS NULL THEN 0
                              WHEN t2.CqQsDate <= @bdate
                                   AND ( t2.QSDate > @bdate
                                   OR t2.IsCQ = 1 ) THEN CASE WHEN ( t3.getamount_begin * 100 / t2.HtTotal < t4.PayPercent
                                                                   OR t3.getamount_begin < t4.Amount
                                                                   OR t4.PayPercent IS NULL ) THEN 0
                                                              ELSE t3.getamount_begin * pp.CheckRate / 100
                                                         END
                              WHEN t2.QSDate <= @bdate THEN 0
                              ELSE NULL
                         END) AS yeji_begin ,
                     SUM(CASE WHEN t21.CloseDate <= @edate
                                   OR t21.Status IS NULL THEN 0
                              WHEN t21.CqQsDate <= @edate
                                   AND ( t21.QSDate > @edate
                                   OR t21.IsCQ = 1 ) THEN CASE WHEN ( t3.getamount_end * 100 / t21.HtTotal < t41.PayPercent
                                                                    OR t3.getamount_end < t41.Amount
                                                                    OR t41.PayPercent IS NULL ) THEN 0
                                                               ELSE t3.getamount_end * pp.CheckRate / 100
                                                          END
                              WHEN t21.QSDate <= @edate THEN 0
                              ELSE NULL
                         END) AS yeji_end
              FROM   p_Room t1 WITH ( NOLOCK )
              INNER JOIN #BU pp ON pp.FQProjGUID = t1.ProjGUID
              LEFT JOIN s_Trade st WITH ( NOLOCK ) ON st.RoomGUID = t1.RoomGUID
                   --起始日期时最新的签约      
              LEFT JOIN ( SELECT *
                          FROM   #temp1 t0
                          WHERE  CASE WHEN t0.CqQsDate <= @bdate
                                           AND ( t0.QSDate > @bdate
                                           OR t0.IsCQ = 1 ) THEN CqQsDate
                                      ELSE t0.QSDate
                                 END <= @bdate ) t2 ON t1.RoomGUID = t2.RoomGUID
                                                       AND st.TradeGUID = t2.TradeGUID
                   --截止日期时最新的签约      
              LEFT JOIN ( SELECT *
                          FROM   #temp1 t0
                          WHERE  CASE WHEN t0.CqQsDate <= @edate
                                           AND ( t0.QSDate > @edate
                                           OR t0.IsCQ = 1 ) THEN ( CqQsDate )
                                      ELSE t0.QSDate
                                 END <= @edate ) t21 ON t1.RoomGUID = t21.RoomGUID
                                                        AND st.TradeGUID = t21.TradeGUID
                   --累计回款      
              LEFT JOIN #getintemp t3 ON st.TradeGUID = t3.SaleGUID
                   --开始时间点生效的考核线      
              LEFT JOIN ( SELECT *
                          FROM   #tmp t01 WITH ( NOLOCK )
                          WHERE  t01.BeginDate <= @bdate
                                 AND ( t01.EndDate IS NULL
                                 OR t01.EndDate >= @bdate ) ) t4 ON t1.ProjGUID = t4.ProjGUID
                                                                    AND t1.BProductTypeCode = t4.BProductTypeCode
                                                                    AND t2.PayformName = t4.PayformName
                   --截止时间点生效的考核线      
              LEFT JOIN ( SELECT *
                          FROM   #tmp t01 WITH ( NOLOCK )
                          WHERE  t01.BeginDate <= @edate
                                 AND ( t01.EndDate IS NULL
                                 OR t01.EndDate >= @edate ) ) t41 ON t1.ProjGUID = t41.ProjGUID
                                                                     AND t1.BProductTypeCode = t41.BProductTypeCode
                                                                     AND t21.PayformName = t41.PayformName
              WHERE  st.TradeGUID NOT IN ( SELECT  TradeGUID
                                           FROM    #c
                                           WHERE   TradeGUID IS NOT NULL
                                                   AND bxflag = 1 )
              GROUP BY t1.ProjGUID ,
                     t1.BldGUID ,
                     t1.BProductTypeCode ) t; 
   SELECT   ProjGUID ,
            t.BldGUID ,
            BProductTypeCode ,
            SUM(CASE WHEN #BG.TradeGUID IS NULL THEN 0
                     ELSE yeji_end - yeji_begin
                END) yeji_between ,
            SUM(CASE WHEN #BG.TradeGUID IS NULL THEN 0
                     ELSE yeji_end
                END) yeji_end
   INTO     #tempcq_bx
   FROM     ( SELECT t1.ProjGUID ,
                     t1.BldGUID ,
                     t1.BProductTypeCode ,
                     t1.RoomGUID ,
                     st.TradeGUID ,
                     SUM(CASE WHEN t2.CloseDate <= @bdate
                                   OR t2.Status IS NULL THEN 0
                              WHEN t2.CqQsDate <= @bdate
                                   AND ( t2.QSDate > @bdate
                                   OR t2.IsCQ = 1 ) THEN CASE WHEN ( t3.getamount_begin * 100 / t2.HtTotal < t4.PayPercent
                                                                   OR t3.getamount_begin < t4.Amount
                                                                   OR t4.PayPercent IS NULL ) THEN 0
                                                              ELSE t3.getamount_begin * pp.CheckRate / 100
                                                         END
                              WHEN t2.QSDate <= @bdate THEN 0
                              ELSE NULL
                         END) AS yeji_begin ,
                     SUM(CASE WHEN t21.CloseDate <= @edate
                                   OR t21.Status IS NULL THEN 0
                              WHEN t21.CqQsDate <= @edate
                                   AND ( t21.QSDate > @edate
                                   OR t21.IsCQ = 1 ) THEN CASE WHEN ( t3.getamount_end * 100 / t21.HtTotal < t41.PayPercent
                                                                    OR t3.getamount_end < t41.Amount
                                                                    OR t41.PayPercent IS NULL ) THEN 0
                                                               ELSE t3.getamount_end * pp.CheckRate / 100
                                                          END
                              WHEN t21.QSDate <= @edate THEN 0
                              ELSE NULL
                         END) AS yeji_end
              FROM   p_Room t1 WITH ( NOLOCK )
              INNER JOIN #BU pp ON pp.FQProjGUID = t1.ProjGUID
              LEFT JOIN s_Trade st WITH ( NOLOCK ) ON st.RoomGUID = t1.RoomGUID
                   --起始日期时最新的签约      
              LEFT JOIN ( SELECT *
                          FROM   #temp1 t0
                          WHERE  CASE WHEN t0.CqQsDate <= @bdate
                                           AND ( t0.QSDate > @bdate
                                           OR t0.IsCQ = 1 ) THEN CqQsDate
                                      ELSE t0.QSDate
                                 END <= @bdate ) t2 ON t1.RoomGUID = t2.RoomGUID
                                                       AND st.TradeGUID = t2.TradeGUID
                   --截止日期时最新的签约      
              LEFT JOIN ( SELECT *
                          FROM   #temp1 t0
                          WHERE  CASE WHEN t0.CqQsDate <= @edate
                                           AND ( t0.QSDate > @edate
                                           OR t0.IsCQ = 1 ) THEN ( CqQsDate )
                                      ELSE t0.QSDate
                                 END <= @edate ) t21 ON t1.RoomGUID = t21.RoomGUID
                                                        AND st.TradeGUID = t21.TradeGUID
                   --累计回款      
              LEFT JOIN #getintemp t3 ON st.TradeGUID = t3.SaleGUID
                   --开始时间点生效的考核线      
              LEFT JOIN ( SELECT *
                          FROM   #tmp t01 WITH ( NOLOCK )
                          WHERE  t01.BeginDate <= @bdate
                                 AND ( t01.EndDate IS NULL
                                 OR t01.EndDate >= @bdate ) ) t4 ON t1.ProjGUID = t4.ProjGUID
                                                                    AND t1.BProductTypeCode = t4.BProductTypeCode
                                                                    AND t2.PayformName = t4.PayformName
                   --截止时间点生效的考核线      
              LEFT JOIN ( SELECT *
                          FROM   #tmp t01 WITH ( NOLOCK )
                          WHERE  t01.BeginDate <= @edate
                                 AND ( t01.EndDate IS NULL
                                 OR t01.EndDate >= @edate ) ) t41 ON t1.ProjGUID = t41.ProjGUID
                                                                     AND t1.BProductTypeCode = t41.BProductTypeCode
                                                                     AND t21.PayformName = t41.PayformName
              WHERE  st.TradeGUID IN ( SELECT   TradeGUID
                                       FROM     #c
                                       WHERE    TradeGUID IS NOT NULL
                                                AND bxflag = 1 )
              GROUP BY t1.ProjGUID ,
                     t1.BldGUID ,
                     t1.BProductTypeCode ,
                     t1.RoomGUID ,
                     st.TradeGUID ) t
   LEFT JOIN #BG ON #BG.TradeGUID = t.TradeGUID
   GROUP BY t.ProjGUID ,
            t.BldGUID ,
            t.BProductTypeCode; 
 -- select   * from  #tempcq_bx
--( SELECT    a.ProjGUID ,
--                                        a.BldGUID ,
--                                        b.TradeGUID ,
--                                        a.BProductTypeCode
--                              FROM      dbo.s_Trade b WITH ( NOLOCK )
--                                        INNER JOIN #Proj p ON p.projGUID = b.ProjGUID
--                                        INNER JOIN dbo.p_Room a ON b.RoomGUID = a.RoomGUID
--                                        LEFT JOIN s_OCAttachRoom fs WITH ( NOLOCK ) ON fs.RoomGUID = a.RoomGUID
--                              WHERE     a.BProductTypeCode IS NOT NULL
--                                        AND fs.RoomGUID IS NULL
--                              GROUP BY  a.ProjGUID ,
--                                        a.BldGUID ,
--                                        b.TradeGUID ,
--                                        a.BProductTypeCode
--                            ) 
    /*初始化回款信息*/
   SELECT   *
   INTO     #getin
   FROM     ( SELECT a.ProjGUID ,
                     a.BldGUID ,
                     a.BProductTypeCode ,
                     ISNULL(SUM(c.AjHkTotal) , 0) AS '银行按揭' ,
                     ISNULL(SUM(c.GjjHkTotal) , 0) AS '公积金' ,
                     ISNULL(SUM(c.HkTotal) , 0) AS '现场收款' ,
                     ISNULL(SUM(c.ljAjHkTotal) , 0) AS 'lj银行按揭' ,
                     ISNULL(SUM(c.ljGjjHkTotal) , 0) AS 'lj公积金' ,
                     ISNULL(SUM(c.ljHkTotal) , 0) AS 'lj现场收款' ,
                     ISNULL(SUM(c.bckTotal) , 0) AS '补差款' ,
                     ISNULL(SUM(c.ljbckTotal) , 0) AS 'lj补差款'
              FROM   dbo.s_Trade b WITH ( NOLOCK )
              INNER JOIN #Proj p WITH ( NOLOCK ) ON p.projGUID = b.ProjGUID
              INNER JOIN dbo.p_Room a WITH ( NOLOCK ) ON b.RoomGUID = a.RoomGUID
              LEFT JOIN #getintemp c WITH ( NOLOCK ) ON c.SaleGUID = b.TradeGUID
              LEFT JOIN s_OCAttachRoom fs WITH ( NOLOCK ) ON fs.RoomGUID = a.RoomGUID
              WHERE  a.BProductTypeCode IS NOT NULL
                     AND fs.RoomGUID IS NULL
              GROUP BY a.ProjGUID ,
                     a.BldGUID ,
                     a.BProductTypeCode ) moko;
    /* 投模信息 */
   SELECT   T1.BldGUID ,
            T1.BldName ,
            T1.ProjGUID ,
            T3.BProductTypeCode ,
            T1.TMJSource ,
            T1.TMAvgPrice ,
            SUM(ISNULL(ISNULL(T3.BldArea , T2.BuildArea) * T1.TMAvgPrice , 0)) AS TMTotalPrice ,
            SUM(ISNULL(ISNULL(T3.BldArea , T2.BuildArea) * T1.TMAvgPrice * T4.CheckRate / 100 , 0)) AS TMCheckPrice
   INTO     #TM
   FROM     p_Room T3 WITH ( NOLOCK )
   INNER   JOIN p_Building T1 WITH ( NOLOCK ) ON T1.BldGUID = T3.BldGUID
   INNER JOIN cb_Product T2 WITH ( NOLOCK ) ON T1.ProductGUID = T2.ProductGUID
   INNER JOIN p_Project T4 WITH ( NOLOCK ) ON T3.ProjGUID = T4.ProjGUID
   LEFT JOIN #RgRoomGUID t2r WITH ( NOLOCK ) ON t2r.RoomGUID = T3.RoomGUID
               -- LEFT JOIN #QYRoomGUID t3q ON t3q.RoomGUID = T3.RoomGUID
   WHERE    T1.TMJSource IS NOT NULL
            AND t2r.OrderGUID IS NULL
              --  AND t3q.ContractGUID IS NULL
            AND ISNULL(T3.TotalDj , 0) = 0
            AND T3.IsZiChiStatus = 0
   GROUP BY T1.BldGUID ,
            T1.BldName ,
            T1.ProjGUID ,
            T3.BProductTypeCode ,
            T1.TMJSource ,
            T1.TMAvgPrice;
    /* 返回结果 */
   SELECT   a.BUName 公司名称 ,
            a.ProjName 项目名称 ,
            a.FQProjName 分期名称 ,
            a.CheckRate 考核比例 ,
            a.RightsRate 权益比例 ,
            a.StockRate 股权比例 ,
            项目状态 ,
            项目等级 ,
            城市 ,
            项目营销负责人 ,
            [区域_城市营销负责人] ,
            大区营销负责人 ,
            是否合作项目 ,
            合作方 ,
            是否营销操盘 ,
            是否我司操盘 ,
            是否并表 ,
            b.BldGUID BldGUID ,
            b.BldName 楼栋 ,
            b.FirstLevelName AS 产品类型 ,
            b.BProductTypeShortName AS 末级产品类型 ,
            b.YszDate AS 取得预售许可证日期 ,
            b.OpeningDate AS 开盘日期 ,
            CASE WHEN j.ishzxm = 1 THEN '未启用'
                 ELSE '启用'
            END AS 是否未启用我司销售系统 ,
           --可售量
            ISNULL(b.HzCount , 0) 可售套数 ,
            ISNULL(b.HzArea , 0) 可售面积 ,
            ISNULL(b.HzTotal , 0) 可售总价 ,
           --当期净认购
            ISNULL(k.RgCount , 0) 当期认购套数 ,
            ISNULL(k.RgArea , 0) 当期认购面积 ,
            ISNULL(CASE WHEN ISNULL(k.RgArea , 0) = 0 THEN 0
                        ELSE ( ISNULL(k.RgTotal , 0) ) / ( ISNULL(k.RgArea , 0) )
                   END , 0) 当期认购均价 ,
            ISNULL(k.RgTotal , 0) 当期认购金额 ,
            ISNULL(c.RgTotal , 0) + ISNULL(#bx.RgTotal , 0) 当期认购金额_含包销 ,
           --当期草签  
            k.cqts AS 当期草签套数 ,
            k.cqarea AS 当期草签面积 ,
            k.cqtotal AS 当期草签金额 ,
            ISNULL(g.yeji_between , 0) + ISNULL(g_bx.yeji_between , 0) AS 当期草签业绩 ,
           --当期净签约
            ISNULL(k.QyCount , 0) 当期净签约套数 ,
            ISNULL(k.QyArea , 0) 当期净签约面积 ,
            ISNULL(k.QyTotal , 0) 当期净签约金额 ,
            ISNULL(c.QyTotal , 0) + ISNULL(#bx.QyTotal , 0) 当期净签约金额_含包销 ,
           ---回款  
           --当期回款                    
            ISNULL(d.银行按揭 , 0) * a.CheckRate / 100 AS 当期银行按揭 ,
            ISNULL(d.公积金 , 0) * a.CheckRate / 100 AS 当期公积金 ,
            ISNULL(d.现场收款 , 0) * a.CheckRate / 100 AS 当期现场收款 ,
            ISNULL(d.补差款 , 0) * a.CheckRate / 100 AS 当期补差款 ,
            ( ISNULL(d.银行按揭 , 0) + ISNULL(d.公积金 , 0) + ISNULL(d.现场收款 , 0) + ISNULL(d.补差款 , 0) ) * a.CheckRate / 100 AS 当期合计 ,
            0 合作项目当期合计 ,
           --累计净认购
            ISNULL(k.LjRgCount , 0) 累计认购套数 ,
            ISNULL(k.LjRgArea , 0) 累计认购面积 ,
            ( CASE WHEN ISNULL(k.LjRgArea , 0) = 0 THEN 0
                   ELSE ( ISNULL(k.LjRgTotal , 0) ) / ( ISNULL(k.LjRgArea , 0) )
              END ) 累计认购均价 ,
            ISNULL(k.LjRgTotal , 0) 累计认购金额 ,
            ISNULL(c.LjRgTotal , 0) + ISNULL(#bx.rgtotal_lj , 0) 累计认购金额_含包销 ,
           ---累计净草签
            k.cqts_all AS 累计草签套数 ,
            k.cqarea_all AS 累计草签面积 ,
            k.cqtotal_all AS 累计草签金额 ,
           --累计签约      
            ISNULL(k.LjQyCount , 0) 累计签约套数 ,
            ISNULL(k.LjQyArea , 0) 累计签约面积 ,
            ( CASE WHEN ISNULL(k.LjQyArea , 0) = 0 THEN 0
                   ELSE ( ISNULL(k.LjQyTotal , 0) ) / ( ISNULL(k.LjQyArea , 0) )
              END ) 累计签约均价 ,
            ISNULL(k.LjQyTotal , 0) 累计签约金额 ,
            ISNULL(c.LjQyTotal , 0) + ISNULL(#bx.QyTotal_lj , 0) 累计签约金额_含包销 ,
           --累计回款                    
            ISNULL(d.lj银行按揭 , 0) * a.CheckRate / 100 AS 累计银行按揭 ,
            ISNULL(d.lj公积金 , 0) * a.CheckRate / 100 AS 累计公积金 ,
            ISNULL(d.lj现场收款 , 0) * a.CheckRate / 100 AS 累计现场收款 ,
            ISNULL(d.lj补差款 , 0) * a.CheckRate / 100 AS 累计补差款 ,
            ( ISNULL(d.lj银行按揭 , 0) + ISNULL(d.lj公积金 , 0) + ISNULL(d.lj现场收款 , 0) + ISNULL(d.lj补差款 , 0) ) * a.CheckRate / 100 累计合计 ,
            0 合作项目累计合计 ,
           --累计认购未签约               
            ISNULL(k.LjRgJhCount , 0) 累计认购未签约套数 ,
            ISNULL(k.LjRgJhArea , 0) 累计认购未签约面积 ,
            ( CASE WHEN ISNULL(k.LjRgJhArea , 0) = 0 THEN 0
                   ELSE ( ISNULL(k.LjRgJhTotal , 0) ) / ( ISNULL(k.LjRgJhArea , 0) )
              END ) 累计认购未签约均价 ,
            ISNULL(k.LjRgJhTotal , 0) 累计认购未签约金额 ,
            ISNULL(c.LjRgJhTotal , 0) + ISNULL(#bx.LjRgJhTotal , 0) 累计认购未签约金额_含包销 ,
           --当时           
            k.cqjhtotal_all AS 当时草签金额 ,
            k.cqjhts_all AS 当时草签套数 ,
            k.cqjharea_all AS 当时草签面积 ,
            ISNULL(g.yeji_end , 0) + ISNULL(g_bx.yeji_end , 0) AS 累计草签业绩 ,
           --项目未售                  
            ISNULL(e.RgWsCount_wdj , 0) 未售套数_未定价 ,
            ISNULL(e.RgWsArea_wdj , 0) 未售面积_未定价 ,
            ISNULL(e.RgWsCount_ydj , 0) 未售套数_已定价 ,
            ISNULL(e.RgWsArea_ydj , 0) 未售面积_已定价 ,
            ISNULL(e.RgWsTotalDj_ydj , 0) 未售金额_已定价 ,
           --投模          
            h.TMJSource 投模价来源 ,
            ISNULL(h.TMAvgPrice , 0) 投模均价 ,
            ISNULL(h.TMTotalPrice , 0) 投模总金额 ,
            ISNULL(h.TMCheckPrice , 0) 投模考核金额 ,
            @begDate AS PZDATE_BEGIN ,
            @endDate AS PZDATE_END ,
            a.ProjGUID
   FROM     #BU a WITH ( NOLOCK )
   LEFT JOIN #Hz b WITH ( NOLOCK ) ON b.FQProjGUID = a.FQProjGUID
   LEFT JOIN #Trade c WITH ( NOLOCK ) ON c.BldGUID = b.BldGUID
                                         AND b.BProductTypeCode = c.BProductTypeCode
   LEFT JOIN #bx ON #bx.BProductTypeCode = b.BProductTypeCode
                    AND #bx.BldGUID = b.BldGUID
   LEFT JOIN #getin d ON d.ProjGUID = a.FQProjGUID
                         AND d.BldGUID = b.BldGUID
                         AND b.BProductTypeCode = d.BProductTypeCode
   LEFT JOIN #ykwshs e WITH ( NOLOCK ) ON b.FQProjGUID = e.FQProjGUID
                                          AND b.BldGUID = e.BldGUID
                                          AND b.BProductTypeCode = e.BProductTypeCode
                --LEFT JOIN #cq f WITH ( NOLOCK ) ON b.BldGUID = f.BldGUID
                --                                   AND b.BProductTypeCode = f.BProductTypeCode
   LEFT JOIN #tempcq g WITH ( NOLOCK ) ON b.BldGUID = g.BldGUID
                                          AND b.BProductTypeCode = g.BProductTypeCode
   LEFT JOIN #tempcq_bx g_bx WITH ( NOLOCK ) ON b.BldGUID = g_bx.BldGUID
                                                AND b.BProductTypeCode = g_bx.BProductTypeCode
   LEFT JOIN #TM h WITH ( NOLOCK ) ON h.ProjGUID = a.FQProjGUID
                                      AND h.BldGUID = b.BldGUID
                                      AND h.BProductTypeCode = b.BProductTypeCode
   LEFT JOIN #hzxm1 j ON j.ProjGUID = a.FQProjGUID
   LEFT JOIN #Trade_fbx k ON k.BldGUID = b.BldGUID
                             AND b.BProductTypeCode = k.BProductTypeCode
   WHERE    j.ishzxm = 0
   UNION ALL
   SELECT   a.BUName 公司名称 ,
            a.ProjName 项目名称 ,
            a.FQProjName 分期名称 ,
            a.CheckRate 考核比例 ,
            a.RightsRate 权益比例 ,
            a.StockRate 股权比例 ,
            项目状态 ,
            项目等级 ,
            城市 ,
            项目营销负责人 ,
            [区域_城市营销负责人] ,
            大区营销负责人 ,
            是否合作项目 ,
            合作方 ,
            是否营销操盘 ,
            是否我司操盘 ,
            是否并表 ,
            NULL BldGUID ,
            '' 楼栋 ,
            NULL AS 产品类型 ,
            NULL AS 末级产品类型 ,
            NULL AS 取得预售许可证日期 ,
            NULL AS 开盘日期 ,
            CASE WHEN j.ishzxm = 1 THEN '未启用'
                 ELSE '启用'
            END AS 是否未启用我司销售系统 ,
           --可售量
            0 可售套数 ,
            0 可售面积 ,
            0 可售总价 ,
           --当期净认购
            ISNULL(I.OrderTs , 0) 当期认购套数 ,
            ISNULL(I.OrderArea , 0) 当期认购面积 ,
            ISNULL(I.OrderAveragePrice , 0) 当期认购均价 ,
            ISNULL(I.OrderAmount , 0) 当期认购金额 ,
            ISNULL(I.OrderAmount , 0) 当期认购金额_含包销 ,
           --当期草签 
            0 AS 当期草签套数 ,
            0 AS 当期草签面积 ,
            0 AS 当期草签金额 ,
            0 AS 当期草签业绩 ,
           --当期净签约
            ISNULL(I.SignTs , 0) 当期净签约套数 ,
            ISNULL(I.SignArea , 0) 当期净签约面积 ,
            ISNULL(I.SignAmount , 0) 当期净签约金额 ,
            ISNULL(I.SignAmount , 0) 当期净签约金额_含包销 ,
           ---回款  
           --当期回款                    
            0 AS 当期银行按揭 ,
            0 AS 当期公积金 ,
            0 AS 当期现场收款 ,
            0 AS 当期补差款 ,
            I.RePayAmount AS 当期合计 ,
            I.RePayAmount 合作项目当期合计 ,
           --累计净认购
            ISNULL(I.LjOrderTs , 0) 累计认购套数 ,
            ISNULL(I.LjOrderArea , 0) 累计认购面积 ,
            ISNULL(I.LjOrderAveragePrice , 0) 累计认购均价 ,
            ISNULL(I.LjOrderAmount , 0) 累计认购金额 ,
            ISNULL(I.LjOrderAmount , 0) 累计认购金额_含包销 ,
           ---累计净草签
            0 AS 累计草签套数 ,
            0 AS 累计草签面积 ,
            0 AS 累计草签金额 ,
           --累计签约      
            ISNULL(I.LjSignTs , 0) 累计签约套数 ,
            ISNULL(I.LjSignArea , 0) 累计签约面积 ,
            ISNULL(I.LjSignAveragePrice , 0) 累计签约均价 ,
            ISNULL(I.LjSignAmount , 0) 累计签约金额 ,
            ISNULL(I.LjSignAmount , 0) 累计签约金额_含包销 ,
           --累计回款                    
            0 AS 累计银行按揭 ,
            0 AS 累计公积金 ,
            0 AS 累计现场收款 ,
            0 AS 累计补差款 ,
            I.LjRePayAmount 累计合计 ,
            I.LjRePayAmount 合作项目累计合计 ,
           --累计认购未签约               
            ISNULL(I.OrderNoSignTs , 0) 累计认购未签约套数 ,
            ISNULL(I.OrderNoSignArea , 0) 累计认购未签约面积 ,
            ( CASE WHEN ISNULL(I.OrderNoSignArea , 0) = 0 THEN 0
                   ELSE I.OrderNoSignAmount / I.OrderNoSignArea
              END ) 累计认购未签约均价 ,
            ISNULL(I.OrderNoSignAmount , 0) 累计认购未签约金额 ,
            ISNULL(I.OrderNoSignAmount , 0) 累计认购未签约金额_含包销 ,
           --当时           
            0 AS 当时草签金额 ,
            0 AS 当时草签套数 ,
            0 AS 当时草签面积 ,
            0 AS 累计草签业绩 ,
           --项目未售                  
            0 未售套数_未定价 ,
            0 未售面积_未定价 ,
            ISNULL(I.PushNoSaleTs , 0) 未售套数_已定价 ,
            ISNULL(I.PushNoSaleArea , 0) 未售面积_已定价 ,
            ISNULL(I.PushNoSaleAmount , 0) 未售金额_已定价 ,
           --投模          
            NULL 投模价来源 ,
            0 投模均价 ,
            0 投模总金额 ,
            0 投模考核金额 ,
            @begDate AS PZDATE_BEGIN ,
            @endDate AS PZDATE_END ,
            a.ProjGUID
   FROM     #BU a WITH ( NOLOCK )
   LEFT JOIN #hzxm1 j ON j.ProjGUID = a.FQProjGUID
   LEFT JOIN #HZXM I WITH ( NOLOCK ) ON I.BUGUID = a.BUGUID
                                        AND I.FQProjGUID = a.FQProjGUID
   WHERE    j.ishzxm = 1;
 --select top 100 * from  #getintemp WHERE SaleGUID='A7638027-684A-EB11-B392-005056834EAD'
   --SELECT   * from  #tempcq_bx
 --SELECT   SUM(getamount_end),SUM(getamount_begin) FROM  #getintemp 
   DROP TABLE #BU, #Hz, #Trade, #ykwshs, #Attach_room, #tempcq, #getin, #hzxm1, #HZXM; 
END;