20170626报表核对

CITY

SELECT
    d.*
FROM
    myBusinessUnit d
WHERE
    1 = 1
AND d.BUcode LIKE 'SZAREA_%'
AND LEN(d.NamePath) = 16

 

05 认购情况统计表

SELECT
    SUBSTRING (
        p_BuildProductType.BProductTypeName,
        0,
        (
            CASE
            WHEN CHARINDEX(
                '-',
                p_BuildProductType.BProductTypeName
            ) = 0 THEN
                len(
                    p_BuildProductType.BProductTypeName
                ) + 1
            ELSE
                CHARINDEX(
                    '-',
                    p_BuildProductType.BProductTypeName
                )
            END
        )
    ) AS producttype1,
    p_Project.ProjName,
    p_Project.ProjCode,
    es_order.QsDate,
    build1.BldName AS YName,
    p_Building.BldName,
    p_Building.ParentCode,
    p_Building.BldCode,
    p_FloorPlan.FloorNo,
    p_Room.Unit,
    p_Room.Room,
    s_contract.CstCircs AS 客户情况,
    p_Room.Floor,
    p_Room.ysxkzdate,
    p_BuildProductType.BProductTypeName,
    p_Room.RoomStru,
    p_Room.Huxing,
    es_order.BldArea AS ysBldArea,
    es_order.TnArea AS ysTnArea,
    p_Room.ScBldArea,
    p_Room.ScTnArea,
    es_order.Total,
    es_order.Price,
    es_order.CjTotal,
    es_order.BldCjPrice,
    es_order.Bz,
    es_order.PayFormName,
    es_order.DiscntValue,
    CAST (
        es_order.DiscntRemark AS VARCHAR (300)
    ) AS 折扣说明,
    p_Room.CstName,
    es_order.Ywy,
    isnull(e.MobileTel, '') + ' ' + isnull(e.HomeTel, '') + ' ' + isnull(e.OfficeTel, '') AS '客户电话',
    (
        SELECT
            TOP 1 e_myUser.DepartmentName
        FROM
            s_OC2Sale
        LEFT JOIN e_myUser ON e_myUser.userguid = s_OC2Sale.userguid
        WHERE
            s_OC2Sale.saleguid = es_order.orderguid
    ) AS '所属公司',
    i.httype,
    (
        CASE
        WHEN isnull(s_Contract.IsZxkbrht, 0) = 1 THEN
            '是'
        ELSE
            '否'
        END
    ) AS IsZxkbrht,
    es_order.zxTotal
FROM
    es_order
INNER JOIN p_Room ON es_order.RoomGUID = p_Room.RoomGUID
INNER JOIN p_Project ON es_order.ProjGUID = p_Project.ProjGUID
INNER JOIN p_Building ON p_Room.BldGUID = p_Building.BldGUID
LEFT JOIN p_Building build1 ON p_Building.ParentCode = build1.ParentCode + '.' + build1.BldCode
AND p_Building.ProjGUID = build1.ProjGUID
LEFT JOIN s_Trade d ON es_order.tradeguid = d.tradeguid
LEFT JOIN dbo.s_Trade2Cst Cst1 ON d.TradeGUID = Cst1.TradeGUID
AND Cst1.CstNum = 1
LEFT JOIN dbo.p_Customer e ON Cst1.CstGUID = e.CstGUID
LEFT JOIN s_Contract ON es_order.TradeGUID = s_Contract.TradeGUID
AND s_Contract.Status = '激活'
AND es_order.CloseReason = '转签约'
LEFT JOIN p_BuildProductType ON p_Room.BProductTypeCode = p_BuildProductType.BProductTypeCode
LEFT JOIN p_FloorPlan ON p_Room.BldGUID = p_FloorPlan.BldGUID
AND p_Room.Floor = p_FloorPlan.Floor
LEFT JOIN s_trade i ON es_order.tradeguid = i.tradeguid
WHERE
    (1 = 1)
AND (
    es_order.OrderType = '认购'
)
AND (
    es_order.Status = '激活'
    OR s_Contract.Status = '激活'
) --AND p_Project.ProjName IN (: var_projname)
AND es_order.BUGUID = 'F6B010E6-5C72-48E3-865F-FF354C6CF7CF'
AND CONVERT (
    VARCHAR (10),
    es_Order.QsDate,
    121
) >= '2017-06-01'
AND CONVERT (
    VARCHAR (10),
    es_Order.QsDate,
    121
) <= '2017-06-18'
ORDER BY
    es_Order.QsDate DESC,
    p_Project.ProjCode,
    p_Building.ParentCode,
    p_Building.Bldcode,
    p_FloorPlan.FloorNo,
    p_Room.Room

　　06 认购未签约 明细

SELECT
	CASE
WHEN charindex('-', c.BProductTypeName) > 0 THEN
	LEFT (
		c.BProductTypeName,
		charindex('-', c.BProductTypeName) - 1
	)
ELSE
	c.BProductTypeName
END AS 一级产品类型,
 d.projname AS 项目名称,
 f.bldname AS 项目分区,
 (
	CASE
	WHEN isnull(e.htbldname, '') <> '' THEN
		e.htbldname
	ELSE
		e.bldname
	END
) AS 楼栋名称,
 b.Unit AS 单元,
 b.HtRoom AS 房号,
 c.BProductTypeName AS 产品类型全称,
 b.huxing AS 户型,
 b.roomstru AS 房间类型,
 b.roomcode AS 房间编码,
 a.qsdate AS 认购日期,
 b.YsxkzDate AS 取得预售许可证日期,
 a.YjQyDate AS '预计签约日期',
--sc.qsdate as 签约日期,
 b.cstname AS 业主姓名,
 a.cstcircs AS 客户情况,
 round(a.total, 0) AS 标准总价,
 round(a.price, 2) AS 标准单价,
 round(a.rmbcjtotal, 0) AS 成交总价,
 isnull(
	(
		SELECT
			SUM (s_getin.RmbAmount)
		FROM
			s_getin
		WHERE
			s_getin.saleGUID = a.tradeGUID
		AND isnull(s_getin.status, '') <> '作废'
		GROUP BY
			s_getin.saleGUID
	),
	0
) AS 实收金额,
 round(a.bldcjprice, 2) AS 建筑成交单价,
 round(a.bldarea, 2) AS 建筑面积,
 round(a.tnarea, 2) AS 套内面积,
 round(b.ScBldArea, 2) AS 建筑实测面积,
 round(b.ScTnArea, 2) AS 套内实测面积,
 a.bz AS 币种,
 a.payformname AS 付款方式名称,
 round(a.DiscntValue, 2) AS 最终折扣,
 a.DiscntRemark AS 折扣说明,
 a.ywy AS 业务员,
 (
	SELECT
		TOP 1 e_myUser.DepartmentName
	FROM
		s_OC2Sale
	LEFT JOIN e_myUser ON e_myUser.userguid = s_OC2Sale.userguid
	WHERE
		s_OC2Sale.saleguid = a.orderguid
) AS '所属公司',
 replace(
	replace(
		isnull(h.mobileTel, '') + '/' + isnull(h.homeTel, '') + '/' + isnull(h.officeTel, '') + '/',
		'///',
		''
	),
	'//',
	'/'
) AS 业主联系电话,
 h.address 业主通讯地址,
 i.HtType AS 合同类别,
 a.AuditBy AS 审核人,
 j.projname AS '父级项目',
 (
	CASE
	WHEN isnull(a.IsZxkbrht, 0) = 1 THEN
		'是'
	ELSE
		'否'
	END
) AS IsZxkbrht,
 a.zxTotal
FROM
	s_order a
LEFT JOIN p_room b ON b.roomguid = a.roomguid
LEFT JOIN p_BuildProductType c ON c.BProductTypeCode = b.BProductTypeCode
LEFT JOIN p_project d ON d.projguid = a.projguid
LEFT JOIN p_building e ON e.bldguid = b.bldguid
LEFT JOIN p_building f ON f.parentCode + '.' + f.bldCode = e.ParentCode
LEFT JOIN s_trade2cst g ON g.tradeguid = a.tradeguid
AND g.cstnum = 1
LEFT JOIN p_customer h ON h.cstguid = g.cstguid
LEFT JOIN s_trade i ON a.tradeguid = i.tradeguid
LEFT JOIN p_project j ON j.projcode = d.parentcode
--LEFT JOIN s_Contract sc on sc.tradeguid = a.tradeguid
WHERE
	isnull(a.status, '') = '激活'
AND isnull(a.orderType, '') IN ('认购')
---AND d.projname IN (: var_projname)
AND a.BUGUID = 'F6B010E6-5C72-48E3-865F-FF354C6CF7CF'
--AND d.projname NOT IN ('深圳威登别墅-威登别墅','测试项目')
AND CONVERT (
	VARCHAR (10),
	a.QsDate,
	121
) >= '2017-06-01'
AND CONVERT (
	VARCHAR (10),
	a.QsDate,
	121
) <= '2017-06-25'
ORDER BY
a.QsDate,
	d.projname,
	f.bldname,
	e.bldname,
	b.Unit,
	b.room

　　07 已签约 明细

SELECT
	CASE
WHEN charindex('-', c.BProductTypeName) > 0 THEN
	LEFT (
		c.BProductTypeName,
		charindex('-', c.BProductTypeName) - 1
	)
ELSE
	c.BProductTypeName
END AS 一级产品类型,
 d.projname AS 项目名称,
 f.bldname AS 项目分区,
 (
	CASE
	WHEN isnull(e.htbldname, '') <> '' THEN
		e.htbldname
	ELSE
		e.bldname
	END
) AS 楼栋名称,
 b.Unit AS 单元,
 b.HtRoom AS 房号,
 b.CqfzGetDate AS '分证取得日期',
 b.CqfzNum AS '产权分证号',
 c.BProductTypeName AS 产品类型全称,
 b.huxing AS 户型,
 b.roomstru AS 房间类型,
 b.RoomCode AS 房间编码,
 a.ZxBz AS 装修标准,
 b.YsxkzDate AS 取得预售许可证日期,
 i.rgdate AS 认购日期,
 a.qsdate AS 签约日期,
 a.jfdate AS 约定交房日期,
 b.cstname AS 业主姓名,
 a.CstCircs AS 客户情况,
 round(a.total, 0) AS 标准总价,
 round(a.price, 2) AS 标准单价,
 round(a.rmbhttotal, 0) AS 成交总价,
 round(a.bldcjprice, 2) AS 建筑成交单价,
 round(a.bldarea, 2) AS 建筑面积,
 round(a.tnarea, 2) AS 套内面积,
 round(b.ScBldArea, 2) AS 建筑实测面积,
 round(b.ScTnArea, 2) AS 套内实测面积,
 a.bz AS 币种,
 a.payformname AS 付款方式名称,
 round(a.DiscntValue, 2) AS 最终折扣,
 CAST (
	a.DiscntRemark AS VARCHAR (1000)
) AS 折扣说明,
 a.ywy AS 业务员,
 a.ContractNO AS 合同编号,
 (
	SELECT
		TOP 1 e_myUser.DepartmentName
	FROM
		s_OC2Sale
	LEFT JOIN e_myUser ON e_myUser.userguid = s_OC2Sale.userguid
	WHERE
		s_OC2Sale.saleguid = a.contractguid
) AS '所属公司',
 replace(
	replace(
		isnull(h.mobileTel, '') + '/' + isnull(h.homeTel, '') + '/' + isnull(h.officeTel, '') + '/',
		'///',
		''
	),
	'//',
	'/'
) AS 业主联系电话,
 h.cardid,
 h.address,
 i.httype AS 合同类别,
 DATEDIFF(DAY, i.rgdate, a.qsdate),
 j.CognizeAve,
 a.HTLQDate AS 合同领取时间,
 a.AuditBy AS 合同审核人,
 (
	SELECT
		s.AuditBy
	FROM
		s_Order s
	WHERE
		s.TradeGUID = a.TradeGUID
	AND s.CloseReason = '转签约'
) AS 定单审核人,
 a.BaDate AS 合同备案日期,
 a.BaSjDate AS 备案送件日期,
 ss.HtBackDate AS 贷款合同日期,
 k.projname AS '父级项目',
 (
	CASE
	WHEN isnull(IsZxkbrht, 0) = 1 THEN
		'是'
	ELSE
		'否'
	END
) AS IsZxkbrht,
 a.zxTotal,
 a.rmbhttotal - isnull(ssk.ss, 0) AS 欠款
FROM
	s_contract a
LEFT JOIN p_room b ON b.roomguid = a.roomguid
LEFT JOIN p_BuildProductType c ON c.BProductTypeCode = b.BProductTypeCode
LEFT JOIN p_project d ON d.projguid = a.projguid
LEFT JOIN p_building e ON e.bldguid = b.bldguid
LEFT JOIN p_building f ON f.parentCode + '.' + f.bldCode = e.ParentCode
LEFT JOIN s_trade2cst g ON g.tradeguid = a.tradeguid
AND g.cstnum = 1
LEFT JOIN p_customer h ON h.cstguid = g.cstguid
LEFT JOIN s_trade i ON i.tradeguid = a.tradeguid
LEFT JOIN s_Opportunity j ON i.OppGUID = j.OppGUID
LEFT JOIN s_SaleService ss ON a.contractguid = ss.contractguid
AND ss.serviceItem = '按揭贷款'
LEFT JOIN p_project k ON k.projcode = d.parentcode
LEFT JOIN (
	SELECT
		SaleGUID,
		SUM (amount) AS ss
	FROM
		s_getin
	WHERE
		isnull(Status, '') <> '作废'
	GROUP BY
		SaleGUID
) ssk ON ssk.SaleGUID = a.TradeGUID
WHERE 1=1
and
	isnull(a.status, '') = '激活'
--AND d.projname IN (: var_projname)
AND d.projguid NOT IN (
	'4490BC91-B4DD-4743-9A0C-BA89C92737F9',
	'B662B05A-3827-4CDE-95F4-CB8BA3910804'
)
AND d.projname NOT IN ('测试项目')
AND a.BUGUID = 'F6B010E6-5C72-48E3-865F-FF354C6CF7CF'
--AND d.projname NOT IN ('深圳威登别墅-威登别墅','测试项目')
AND CONVERT (
	VARCHAR (10),
	a.QsDate,
	121
) >= '2017-06-01'
AND CONVERT (
	VARCHAR (10),
	a.QsDate,
	121
) <= '2017-06-18'
ORDER BY
a.QsDate,
	d.projname,
	f.bldname,
	e.bldname,
	b.Unit,
	b.room