1 var telReg = /^[1][3,4,5,6,7,8,9][0-9]{9}$/; //0\d{2,3}-\d{7,8}
2 var telReg1 = /^0\d{2,3}-\d{7,8}$/; //0\d{2,3}-\d{7,8}
3
4
5 --3646
6 USE [operationdb]
7 GO
8
9 /****** Object: StoredProcedure [dbo].[ExportProperty] Script Date: 2019/2/22/周五 12:32:23 ******/
10 SET ANSI_NULLS ON
11 GO
12
13 SET QUOTED_IDENTIFIER ON
14 GO
15
16
17
18
19 -- =============================================
20 -- Author: <hsb>
21 -- Create date: <20180906>
22 -- Description: <物业分期数据>
23 -- =============================================
24 ALTER PROCEDURE [dbo].[ExportReNameProc]
25 --@startTime varchar(100),
26 --@endTime varchar(100)
27 AS
28 BEGIN
29
30 BEGIN
31 --责任盘数据
32 SELECT
33 A.id 分期ID
34 ,B.commName 物业名称
35 ,A.reName 分期名称
36 ,C.houseSeatNum 楼栋数
37 ,T.小区户数 户数
38 ,D.有号码户数 有号码户数
39 ,T.小区户数-D.有号码户数 没号码户数
40 ,Q.deptName 所属区域
41 ,E.HouseNum AB类数
42 ,C.realAddr 楼盘实际地址
43 ,C.propAddr 楼盘产权地址
44 ,F.名称 小区专家
45 ,P.名称 商圈
46 ,C.floatArea 占地面积
47 ,C.buildArea 建筑面积
48 ,C.houseSeatNum 住宅栋数
49 ,C.StoreSeatNum 商业栋数
50 ,C.landDueYear 土地使用年限
51 ,C.landBelong 土地权属
52 ,C.landUseMethod 土地使用方式
53 ,C.landLevel 土地级别
54 ,C.developerCompany 开发商
55 ,C.designCompany 建筑设计公司
56 ,C.buildYear 建成年代
57 ,C.buildCompany 承建公司
58 ,C.HouseDeliveryTime 交房时间
59 ,C.testifyTime 出证时间
60 ,C.openingAvgPrice 开盘均价
61 ,C.assessedPrice 评估价
62 ,C.markeyPrice 市场指导价
63 ,C.plotRatio 容积率
64 ,C.greeningRate 绿化率
65 ,C.merit 小区优点
66 ,C.defect 小区缺点
67 ,C.tradingOwnership 交易权属
68 ,C.isSealing 封闭
69 ,C.truckSpace 车位数
70 ,C.truckSpaceRatio 车位配比
71 ,C.parkingFee 停车费
72 ,C.isShunt 人车分流
73 ,C.parkingIntro 停车管理简介
74 ,C.managerMethod 小区管理方式
75 ,C.contactWay 物业电话
76 ,C.managerCompany 物业公司
77 ,CONVERT(VARCHAR(20),C.managementFee1)+'~'+CONVERT(VARCHAR(20),C.managementFee2) '物业费(区间)'
78 ,C.managerAddr 物业办公地点
79 ,C.managerComIntro 物业公司介绍
80 ,C.managerIntro 小区管理介绍
81 ,H.imgnum 小区图片数
82 ,C.communityIntro 小区介绍
83 ,C.safetyIntro 安全防范简介
84 ,C.assortIntro 小区配套简介
85 ,C.asmosphereIntro 居住氛围简介
86 ,C.environmentIntro 内部环境简介
87 ,C.ownerIntro 业主特征简介
88 ,C.tenantIntro 租客特点简介
89 ,K.名称 学区房
90
91
92 FROM [dbo].[ReNameTbl] A WITH(NOLOCK)
93 LEFT JOIN [dbo].[CommunityNameTbl] B WITH(NOLOCK) on A.commId=B.id and B.del=0
94 LEFT JOIN [dbo].[ReInfoTbl] C WITH(NOLOCK) on A.id=C.reId
95
96 LEFT JOIN (select B.reID,COUNT(1) 有号码户数 from UnitNameTbl A
97 left join HouseInfoTbl B on A.id=B.unitId and B.del=0
98 where A.del=0 and B.id is not null
99 group by B.reID) D on A.id=D.reID
100
101 LEFT JOIN ( select B.reName, COUNT(1) HouseNum from [dbo].[HouseInfoTbl] A
102 LEFT JOIN [dbo].[ReNameTbl] B WITH(NOLOCK) on A.reID=B.id
103 where A.houseRank in(1,2) and B.del=0 and A.del=0
104 group by A.reID,B.reName) E on A.reName=E.reName
105
106 LEFT JOIN (select D.id,SUM(D.楼住户数) 小区户数 from(
107 SELECT B.id,A.floorNum*A.roomNum 楼住户数
108 FROM [dbo].[SeatNameTbl] A
109 INNER JOIN dbo.ReNameTbl B ON A.reId=B.id and B.del=0
110 where A.del=0 ) D
111 group by D.id) T on A.id=T.id
112
113 --==========小区专家====
114 LEFT JOIN (
115 select
116 J.reID,
117 名称 = (
118 stuff((select '，' + CONVERT(VARCHAR(100),perAllInfo) from ( select A.reID,B.perAllInfo from CommReExpertTbl A
119 left join dbo.PerAllInfoTbl B on A.perID=B.perid and B.isDel=0
120 where A.isDel=0
121 ) t where t.reID = J.reID
122 for xml path('')),1,1,'')
123 )
124 from ( select A.reID,B.perAllInfo from CommReExpertTbl A
125 left join dbo.PerAllInfoTbl B on A.perID=B.perid and B.isDel=0
126 where A.isDel=0 ) as J
127 group by J.reID
128 ) F on A.id=F.reID
129
130
131
132
133 LEFT JOIN ( select A.reName,COUNT(A.reName) imgnum from [ReNameTbl] A
134 left join [dbo].[CommImgTbl] B on A.id=B.dataId
135 where B.typeId=2 and B.isDel=0 and A.del=0
136 group by A.reName) H on A.reName=H.reName
137
138 -- --==========学区房=============
139
140 LEFT JOIN (select
141 I.id,
142 名称 = (
143 stuff((select '|' + CONVERT(VARCHAR(200),name)+'('
144 +J.名称
145 +')' from (select A.id,C.name from [ReNameTbl] A
146 left join [dbo].[CommReSchoolTbl] B on A.id=B.reID and B.isDel=0
147 left join [dbo].[FixedSchoolNameTbl] C on B.schoolID=C.id and C.del=0
148 where A.del=0
149 group by A.id,C.name
150 ) t where t.id = I.id
151 for xml path('')),1,1,'')
152 )
153 from (select A.id ,C.name from [ReNameTbl] A
154 left join [dbo].[CommReSchoolTbl] B on A.id=B.reID and B.isDel=0
155 left join [dbo].[FixedSchoolNameTbl] C on B.schoolID=C.id and C.del=0
156 where A.del=0
157 group by A.id,C.name) as I
158 LEFT join (select J.reid,J.名称
159 from(
160 select
161 J.reid,
162 名称 = (
163 stuff((select '，' + CONVERT(VARCHAR(20),seatName) from ( select A.reID,D.seatname from CommReSchoolTbl A
164 left join FixedSchoolNameTbl B on A.schoolID=B.id AND B.del=0
165 left join CommSeatSchoolTbl C on A.id = C.rsID AND C.isDel=0
166 inner join SeatNameTbl D on D.id = C.seatID AND D.del=0
167 where A.isDel=0
168 ) t where t.reid = J.reid
169 for xml path('')),1,1,'')
170 )
171 from ( select A.reID from CommReSchoolTbl A
172 left join FixedSchoolNameTbl B on A.schoolID=B.id AND B.del=0
173 left join CommSeatSchoolTbl C on A.id = C.rsID AND C.isDel=0
174 inner join SeatNameTbl D on D.id = C.seatID AND D.del=0
175 where A.isDel=0 ) as J
176 group by J.reid
177 ) J) J on I.id=J.reID
178 group by I.id,J.名称
179 ) K on A.id=K.id
180
181 ----===========商圈===========
182 LEFT JOIN (
183 select
184 J.reId,
185 名称 = (
186 stuff((select '，' + CONVERT(VARCHAR(200),name) from ( select A.reID, B.name from commrebusinessdisttbl A
187 left join AccountManager..BaseBusinessDistrictTbl B on A.businessDistId=B.id and B.isDel=0
188 where A.isDel=0
189 ) t where t.reId = J.reId
190 for xml path('')),1,1,'')
191 )
192 from ( select A.reID, B.name from commrebusinessdisttbl A
193 left join AccountManager..BaseBusinessDistrictTbl B on A.businessDistId=B.id and B.isDel=0
194 where A.isDel=0 ) J
195 group by J.reId) P on A.id=P.reId
196
197 LEFT JOIN (select B.id,C.deptName from DutyAllotInfoTbl A
198 left join ReNameTbl B ON A.dataId2=B.id and B.del=0
199 left join accountmanager..Base_Department_Tbl C On A.allotId=C.id and C.del=0
200 where A.isAllot=1 AND A.daType=2 and A.isDel=0) Q on A.id=Q.id
201
202 where A.del=0
203
204
205
206
207
208
209 END
210 END
211
212
213
214
215 GO
