123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262 |
- 商品房
- 商品房指标
- 商品房、住宅、农房
- 项目数、楼幢数、总套数、总面积、
- 销售套数、销售面积
- 库存套数、库存面积
- 月均销售套数、月均销售面积
- 套数去化周期、面积去化周期
- 户型、面积分析
- 商品房
- 在建在售
- 年度批售
- 商品房、住宅
- 房屋批售、预售许可、现售备案
- 批售套数、批售面积、同比
- 年度销售-年度
- 商品房、住宅
- 销售套数、销售面积、销售金额、销售均价、同比
- 年度销售-月度
- 商品房、住宅
- 批售:套数、面积
- 销售:套数、面积、均价同比、环比、金额、库存、去化周期
- 销售排行
- 年度、月度
- 套数、面积
- 商品房、住宅
- 商品房-在建在售
- 商品房、住宅
- 项目数、楼幢数、总套数、总面积、
- 销售套数、销售面积
- 库存套数、库存面积
- 月均销售套数、月均销售面积
- 套数去化周期、面积去化周期
- 在建在售项目-列表 sfwc <=1
- 在建在售-详情
- 楼栋数、总套数、住宅套数
- from ys_xmxx x , fg_building l , fg_house h
- where
- x.xmbh = l.xmid and l.building_id = h.building_id
- and h.zt = 1
- and x.sfwc <=1 // 在建在售
- and x.xmbh = #{xmbh}// 项目编号
- and h.house_name is not null and h.house_zl is not null
- and x.xmbh not like 'N%'
- and h.house_type not like 'D%'
- <if test="fwyt == 1">
- and h.yt='住宅'
- </if>
- 批售套数、批售面积住宅批售套数、批售面积
- select h.house_id,
- case when s.lb=0 then h.yjzmj else h.jzmj end as fwmj, h.yt, s.lb // . 0预售许可 1现售备案
- from ys_yssq s,fg_house h,ys_xmxx x ,fg_building l
- where
- x.xmbh = l.xmid and l.building_id = h.building_id and s.jhl=h.building_id
- and x.sfwc <=1 and x.xmbh = #{xmbh}// 在建在售、项目编号
- and s.zt=1 and s.pzrq is not null and s.zsh is not null // 批售条件
- and h.house_type not like '%D%' and h.house_name is not null and h.house_zl is not null
- and h.house_id not like '%N%'
- <if test="fwyt == 1">
- and h.yt = '住宅'
- </if>
- 销售套数、销售面积、销售金额
- from ys_htba_sq ht,ys_xmxx x , fg_building l , fg_house h
- where
- x.xmbh = l.xmid and l.building_id = h.building_id and ht.fwid = h.house_id
- and x.sfwc <=1 and x.xmbh = #{xmbh} // 在建在售、项目编号
- and ht.zt >= 1
- and ht.lb in (1,2) // 商品房
- <if test="fwyt == 1">
- and ht.fwyt='住宅'
- </if>
- 在建在售-面积分析
- SELECT
- fw.house_id as fwid,
- CASE WHEN fw.jzmj > 0 THEN fw.jzmj ELSE fw.yjzmj END AS jzmj
- FROM fg_house fw,fg_building lz
- where
- lz.building_id = fw.building_id
- and lz.xmid = #{xmbh} // 项目下的房屋
- and fw.zt = 1
- and fw.house_name is not null and fw.house_zl is not null
- and fw.house_id not like '%N%' and yt = '住宅' and house_type not like '%D%'
- 在建在售-面积库存分析
- select h.house_id as fwid,
- CASE WHEN h.jzmj > 0 THEN h.jzmj ELSE h.yjzmj END AS jzmj
- from fg_house h
- left join fg_building l on l.building_id = h.building_id
- left join (
- // 已销售的房屋
- select h.HOUSE_ID
- from ys_htba_sq ht,fg_building l , fg_house h
- where
- l.building_id = h.building_id and ht.fwid = h.house_id
- and l.xmid = #{xmbh} // 项目下房屋
- and ht.zt >= 1 and ht.lb in (1,2) // 已销售商品房
- and ht.fwyt='住宅'
- GROUP BY h.HOUSE_ID
- ) t on t.HOUSE_ID = h.HOUSE_ID
- where
- h.zt = 1
- and l.xmid = #{xmbh} // 项目下房屋
- and h.house_name is not null and h.house_zl is not null
- and h.house_type not like 'D%'
- and h.house_id not like '%N%'
- and h.yt='住宅'
- and t.HOUSE_ID is null // 关联已销售房屋后 、null表示未销售的房屋
- 在建在售-面积年度分析
- 查询统计数据
- 在建在售-户型分析
- select hxg.zts,
- case when hxg.hx like '一室%' or hxg.hx like '单户%' then 1
- when hxg.hx like '二室%' then 2
- when hxg.hx like '三室%' then 3
- when hxg.hx like '四居%' then 4
- else 0 end as hx // 0 其他
- from (
- select count(h.HOUSE_ID) as zts,h.hx
- from fg_house h,fg_building l
- where
- l.building_id = h.building_id
- and l.xmid = #{xmbh}
- and h.zt = 1
- and h.house_name is not null and h.house_zl is not null
- and h.house_type not like 'D%'
- and h.house_id not like '%N%'
- and h.yt='住宅'
- group by h.hx
- ) hxg
- 在建在售-户型库存分析
- select hxg.zts,
- case when hxg.hx like '一室%' or hxg.hx like '单户%' then 1
- when hxg.hx like '二室%' then 2
- when hxg.hx like '三室%' then 3
- when hxg.hx like '四居%' then 4
- else 0 end as hx
- from (
- // 项目下的房屋关联已销售的房屋 得到未销售的房屋
- select count(h.HOUSE_ID) as zts,h.hx
- from fg_house h
- left join fg_building l on l.building_id = h.building_id
- left join (
- // 获取 项目下 已销售的房屋
- select h.HOUSE_ID
- from ys_htba_sq ht,fg_building l , fg_house h
- where
- l.building_id = h.building_id and ht.fwid = h.house_id
- and l.xmid = #{xmbh}// 获取项目下的房屋
- and ht.zt >= 1 and ht.lb in (1,2)
- and ht.fwyt='住宅'
- GROUP BY h.HOUSE_ID
- ) t on t.HOUSE_ID = h.HOUSE_ID
- where
- h.zt = 1
- and l.xmid = #{xmbh} // 获取项目下的房屋
- and h.house_name is not null and h.house_zl is not null
- and h.house_type not like 'D%'
- and h.house_id not like '%N%'
- and h.yt='住宅'
- and t.HOUSE_ID is null // 未关联销售数据 ——> 得到未销售的房屋
- group by h.hx
- ) hxg
- 在建在售-户型年度分析
- 去统计表中查询该户型5年的销量和占比
- 商品房-个人
- 套数\面积\年龄\区域
- 商品房、住宅
- from ys_htlsb_msr m, ys_htba_sq b
- where m.htbh=b.htbh
- and m.state>=0 and m.lx=2
- and m.lb in (1,2) // 买受人、买受人共有人
- and b.lb in (1,2) // 商品房现售、预售合同
- and b.zt>0 //
- <if test="fwyt == 1">
- and b.fwyt='住宅'
- </if>
- group by m.mxrzjh
- 人均
- 存量房
- 年度销售
- 存量房、住宅
- 套数、面积、均价、同比、金额
- 月度销售
- 存量房、住宅
- 套数、面积、均价、同比、环比、金额
- 分析
- 面积、户型、付款类型
- 套数、面积、占比
- 小区排行
- 年度、月度
- 套数、面积
- 商品房、住宅
- 维修资金
- 资金统计
- 小区数、楼栋数、房屋套数、承办银行数、总余额
- t_zh_fwzh zh ,t_wy_fw fw
- fw. fwlx != 'D'
- 已缴存房屋套数、面积、总金额
- from t_zh_fwzh zh ,t_wy_fw fw
- where zh.fwid=fw.id
- and zh.cjzt=5 //资金状态 0未初交 2业务办理中 5已交存
- and zh.state=1
- and fw.state=1 and fw.zt = 1 and fw. fwlx != 'D'
- 未交存房屋套数、应交总金额;
- from t_zh_fwzh zh ,t_wy_fw fw
- where zh.fwid=fw.id
- and zh.cjzt < 5 //资金状态 0未初交 2业务办理中 5已交存
- and zh.state=1
- and fw.state=1 and fw.zt = 1 and fw. fwlx != 'D'
- 维修总笔数、预算总金额;
- from t_zy_wxxm
- where
- state = 1 and gcjd >=5 //0暂存 1申报 2维修组织 3维修招标 4维修施工 5竣工验收 9完结 -1撤销
- 正在维修项目数、预算总金额;
- from t_zy_wxxm
- where
- state = 1 and gcjd = 4 //0暂存 1申报 2维修组织 3维修招标 4维修施工 5竣工验收 9完结 -1撤销
- 拨付总笔数、拨付总金额;
- from t_zy_bfyw_mx
- where
- bfzt = 5 and cxzt = 0
- and state = 1
- 申请拨付笔数、金额;
- from t_zy_bfyw_mx
- where
- bfzt >= 2 and bfzt <= 4 //拨付状态 0不可拨付 1可申请 2已申请 4可拨付 5已拨付 -1撤销
- and cxzt = 0 //撤销状态 0未撤销 1已撤销
- and state = 1
- 资金归集年度
- 1、初始化数据:firstInit
- 2、wxzj_jcyw_mx:
- ywzt >= 4 //业务状态 1有效 4扣款 5到账
- and state = 1
- and cxzt = 0 //撤销状态 0未撤销 1撤销
- 资金支用年度
- 1、初始化数据:firstInit
- 2、t_zy_bfyw_mx
- bfzt = 5 //拨付状态 0不可拨付 1可申请 2已申请 4可拨付 5已拨付 -1撤销
- and cxzt = 0 //撤销状态 0未撤销 1已撤销
- and state = 1
- 维修支用排行
- from t_zy_bfyw_mx mx, t_zy_wxxm xm, t_wy_xq xq
- where mx.xmid = xm.id
- and xm.xqid = xq.id
- and mx.bfzt = 5 //拨付状态 0不可拨付 1可申请 2已申请 4可拨付 5已拨付 -1撤销
- and mx.cxzt = 0 //撤销状态 0未撤销 1已撤销
- and mx.state = 1
- 维修设备分析
- from t_zy_wxsb sb,t_zy_wxxm xm
- where sb.xmid = xm.id
- and sb.state = 1 and xm.state = 1
- and xm.gcjd >=4 //0暂存 1申报 2维修组织 3维修招标 4维修施工 5竣工验收 9完结 -1撤销
- group by sb.lbid
- 预售资金监管
- 预售资金归集年度分析
- 预售资金拨付年度分析
- 存量房资金托管
- 本年度托管资金
- 托管资金年度分析
|