public List<MonthReportVO> ywsjtj(String startTime, String endTime, String area) {
// 考虑登录的用户的权限
Map session = ActionContext.getContext().getSession();
Org org = (Org) session.get("org");
String areaStr = "( ";
// 如果页面没选择县区，则默认自己辖区
if (area == null || "".equals(area)) {
area = org.getRegionCode();
areaStr = " h.collection_country like '" + area + "%')";
} else {
String[] str = area.split(" ");
for (String stri : str) {
areaStr += " or h.collection_country like '" + stri + "%'";
}
areaStr = areaStr.replaceFirst("or", " ") + " )";
}
// 考虑时间的精确度
// 环比时间
String lsmStartTime = "";
String lsmEndTime = "";
// 同比时间
String lsyStartTime = TimeUtil.operateMonthToString2(startTime, 12, "minus");
String lsyEndTime = TimeUtil.operateMonthToString2(endTime, 12, "minus");
int month = 0;
if (startTime != null && startTime.length() > 7) {
int dayCha = 0;
// 环比时间
dayCha = dayCha(startTime, endTime) + 1;
lsmStartTime = jsDay(startTime, dayCha);
lsmEndTime = jsDay(endTime, dayCha);
} else {
month = monthCha(startTime, endTime);
lsmStartTime = TimeUtil.operateMonthToString2(startTime, month + 1, "minus");
lsmEndTime = TimeUtil.operateMonthToString2(endTime, month + 1, "minus");
}
String nyTiaoJian = "";
String nytoc = "";
if (startTime.length() > 7) {
nyTiaoJian =
" to_char(p.PROPERTY_DATE,'yyyy-mm-dd') between '"
+ startTime
+ "' and '"
+ endTime
+ "' )";
} else {
nyTiaoJian =
" to_char(p.PROPERTY_DATE,'yyyy-mm') between '" + startTime + "' and '" + endTime + "' )";
}
// 所有权登记
String syqdjSql =
"select nvl(sum(h.hs_build_area_forecase),0)/10000 as area,nvl(count(h.his_hs_no),0) as taoshu ,'1' as ny from m_house_info h where "
+ areaStr
+ " and exists (select 1 from t_property_info p where h.his_hs_no=p.hs_no and p.ac_cat_biz "
+ "in ('18', '24', '29', '30') and h.collection_city=p.collection_city and h.collection_country=p.collection_country and "
+ nyTiaoJian;
// 抵押权登记
String dydjSql =
"select nvl(sum(h.hs_build_area_forecase),0)/10000 as area,nvl(count(h.his_hs_no),0) as taoshu ,'1' as ny from m_house_info h where "
+ areaStr
+ " and exists (select 1 from t_mortgag_info p where h.his_hs_no=p.hs_no and h.collection_city=p.collection_city and h.collection_country=p.collection_country and"
+ nyTiaoJian.replace("p.PROPERTY_DATE", "p.register_date")
+ " ";
// 地役权登记
String dyudjSql =
"select nvl(sum(h.hs_build_area_forecase),0)/10000 as area,nvl(count(h.his_hs_no),0) as taoshu ,'1' as ny from m_house_info h where "
+ areaStr
+ " and exists (select 1 from t_property_info p where h.his_hs_no=p.hs_no and p.ac_cat_biz "
+ "= '47' and h.collection_city=p.collection_city and h.collection_country=p.collection_country and "
+ nyTiaoJian;
// 预告登记
String ygdjSql =
"select nvl(sum(h.hs_build_area_forecase),0)/10000 as area,nvl(count(h.his_hs_no),0) as taoshu ,'1' as ny from m_house_info h where "
+ areaStr
+ " and exists (select 1 from t_property_info p where h.his_hs_no=p.hs_no and p.ac_cat_biz "
+ "= '51' and h.collection_city=p.collection_city and h.collection_country=p.collection_country and "
+ nyTiaoJian;
// 其他登记
String qtdjSql =
"select nvl(sum(h.hs_build_area_forecase),0)/10000 as area,nvl(count(h.his_hs_no),0) as taoshu ,'1' as ny from m_house_info h where "
+ areaStr
+ " and exists (select 1 from t_property_info p where h.his_hs_no=p.hs_no and p.ac_cat_biz "
+ "= '99' and h.collection_city=p.collection_city and h.collection_country=p.collection_country and "
+ nyTiaoJian;
List<MonthReportVO> syqList = oldHouseSellDao.queryywShuJu(syqdjSql);
List<MonthReportVO> hbsyqList =
oldHouseSellDao.queryywShuJu(
syqdjSql.replace(startTime, lsmStartTime).replace(endTime, lsmEndTime));
List<MonthReportVO> tbsyqList =
oldHouseSellDao.queryywShuJu(
syqdjSql.replace(startTime, lsyStartTime).replace(endTime, lsyEndTime));
dlistThb(syqList, hbsyqList, tbsyqList);
List<MonthReportVO> dyList = oldHouseSellDao.queryywShuJu(dydjSql);
List<MonthReportVO> hbdyList =
oldHouseSellDao.queryywShuJu(
dydjSql.replace(startTime, lsmStartTime).replace(endTime, lsmEndTime));
List<MonthReportVO> tbdyList =
oldHouseSellDao.queryywShuJu(
dydjSql.replace(startTime, lsyStartTime).replace(endTime, lsyEndTime));
dlistThb(dyList, hbdyList, tbdyList);
List<MonthReportVO> dyuList = oldHouseSellDao.queryywShuJu(dyudjSql);
List<MonthReportVO> hbdyuList =
oldHouseSellDao.queryywShuJu(
dyudjSql.replace(startTime, lsmStartTime).replace(endTime, lsmEndTime));
List<MonthReportVO> tbdyuList =
oldHouseSellDao.queryywShuJu(
dyudjSql.replace(startTime, lsyStartTime).replace(endTime, lsyEndTime));
dlistThb(dyuList, hbdyuList, tbdyuList);
List<MonthReportVO> ygList = oldHouseSellDao.queryywShuJu(ygdjSql);
List<MonthReportVO> hbygList =
oldHouseSellDao.queryywShuJu(
ygdjSql.replace(startTime, lsmStartTime).replace(endTime, lsmEndTime));
List<MonthReportVO> tbygList =
oldHouseSellDao.queryywShuJu(
ygdjSql.replace(startTime, lsyStartTime).replace(endTime, lsyEndTime));
dlistThb(ygList, hbygList, tbygList);
List<MonthReportVO> qtList = oldHouseSellDao.queryywShuJu(qtdjSql);
List<MonthReportVO> hbqtList =
oldHouseSellDao.queryywShuJu(
qtdjSql.replace(startTime, lsmStartTime).replace(endTime, lsmEndTime));
List<MonthReportVO> tbqtList =
oldHouseSellDao.queryywShuJu(
qtdjSql.replace(startTime, lsyStartTime).replace(endTime, lsyEndTime));
dlistThb(qtList, hbqtList, tbqtList);
// 计算合计
List<MonthReportVO> hjList = new ArrayList<MonthReportVO>();
List<MonthReportVO> tbhjList = new ArrayList<MonthReportVO>();
List<MonthReportVO> hbhjList = new ArrayList<MonthReportVO>();
jshj(hjList, syqList, dyList, dyuList, ygList, qtList);
jshj(tbhjList, tbsyqList, tbdyList, tbdyuList, tbygList, tbqtList);
jshj(hbhjList, hbsyqList, hbdyList, hbdyuList, hbygList, hbqtList);
// 合计的同环比
dlistThb(hjList, tbhjList, hbhjList);
// 计算占比
jszb(hjList, syqList);
jszb(hjList, dyList);
jszb(hjList, dyuList);
jszb(hjList, ygList);
jszb(hjList, qtList);
List<MonthReportVO> resultList = new ArrayList<MonthReportVO>();
// 数据计算完毕，准备前台显示
for (int i = 0; i < syqList.size(); i++) {
MonthReportVO avo = new MonthReportVO();
avo.setNm(double2String(syqList.get(i).getArea()));
avo.setVx("syq0");
resultList.add(avo);
MonthReportVO ahvo = new MonthReportVO();
ahvo.setNm(syqList.get(i).getHbm());
ahvo.setVx("syq1");
resultList.add(ahvo);
MonthReportVO atvo = new MonthReportVO();
atvo.setNm(syqList.get(i).getTbm());
atvo.setVx("syq2");
resultList.add(atvo);
MonthReportVO azvo = new MonthReportVO();
azvo.setNm(syqList.get(i).getZbm());
azvo.setVx("syq3");
resultList.add(azvo);
MonthReportVO tvo = new MonthReportVO();
tvo.setNm(double2String(syqList.get(i).getTaoshu()));
tvo.setVx("syq4");
resultList.add(tvo);
MonthReportVO thvo = new MonthReportVO();
thvo.setNm(syqList.get(i).getHbt());
thvo.setVx("syq5");
resultList.add(thvo);
MonthReportVO ttvo = new MonthReportVO();
ttvo.setNm(syqList.get(i).getTbt());
ttvo.setVx("syq6");
resultList.add(ttvo);
MonthReportVO ztvo = new MonthReportVO();
ztvo.setNm(syqList.get(i).getZbt());
ztvo.setVx("syq7");
resultList.add(ztvo);
}
for (int i = 0; i < dyList.size(); i++) {
MonthReportVO avo = new MonthReportVO();
avo.setNm(double2String(dyList.get(i).getArea()));
avo.setVx("dy0");
resultList.add(avo);
MonthReportVO ahvo = new MonthReportVO();
ahvo.setNm(dyList.get(i).getHbm());
ahvo.setVx("dy1");
resultList.add(ahvo);
MonthReportVO atvo = new MonthReportVO();
atvo.setNm(dyList.get(i).getTbm());
atvo.setVx("dy2");
resultList.add(atvo);
MonthReportVO azvo = new MonthReportVO();
azvo.setNm(dyList.get(i).getZbm());
azvo.setVx("dy3");
resultList.add(azvo);
MonthReportVO tvo = new MonthReportVO();
tvo.setNm(double2String(dyList.get(i).getTaoshu()));
tvo.setVx("dy4");
resultList.add(tvo);
MonthReportVO thvo = new MonthReportVO();
thvo.setNm(dyList.get(i).getHbt());
thvo.setVx("dy5");
resultList.add(thvo);
MonthReportVO ttvo = new MonthReportVO();
ttvo.setNm(dyList.get(i).getTbt());
ttvo.setVx("dy6");
resultList.add(ttvo);
MonthReportVO ztvo = new MonthReportVO();
ztvo.setNm(dyList.get(i).getZbt());
ztvo.setVx("dy7");
resultList.add(ztvo);
}
for (int i = 0; i < dyuList.size(); i++) {
MonthReportVO avo = new MonthReportVO();
avo.setNm(double2String(dyuList.get(i).getArea()));
avo.setVx("dyu0");
resultList.add(avo);
MonthReportVO ahvo = new MonthReportVO();
ahvo.setNm(dyuList.get(i).getHbm());
ahvo.setVx("dyu1");
resultList.add(ahvo);
MonthReportVO atvo = new MonthReportVO();
atvo.setNm(dyuList.get(i).getTbm());
atvo.setVx("dyu2");
resultList.add(atvo);
MonthReportVO azvo = new MonthReportVO();
azvo.setNm(dyuList.get(i).getZbm());
azvo.setVx("dyu3");
resultList.add(azvo);
MonthReportVO tvo = new MonthReportVO();
tvo.setNm(double2String(dyuList.get(i).getTaoshu()));
tvo.setVx("dyu4");
resultList.add(tvo);
MonthReportVO thvo = new MonthReportVO();
thvo.setNm(dyuList.get(i).getHbt());
thvo.setVx("dyu5");
resultList.add(thvo);
MonthReportVO ttvo = new MonthReportVO();
ttvo.setNm(dyuList.get(i).getTbt());
ttvo.setVx("dyu6");
resultList.add(ttvo);
MonthReportVO ztvo = new MonthReportVO();
ztvo.setNm(dyuList.get(i).getZbt());
ztvo.setVx("dyu7");
resultList.add(ztvo);
}
for (int i = 0; i < ygList.size(); i++) {
MonthReportVO avo = new MonthReportVO();
avo.setNm(double2String(ygList.get(i).getArea()));
avo.setVx("yg0");
resultList.add(avo);
MonthReportVO ahvo = new MonthReportVO();
ahvo.setNm(ygList.get(i).getHbm());
ahvo.setVx("yg1");
resultList.add(ahvo);
MonthReportVO atvo = new MonthReportVO();
atvo.setNm(ygList.get(i).getTbm());
atvo.setVx("yg2");
resultList.add(atvo);
MonthReportVO azvo = new MonthReportVO();
azvo.setNm(ygList.get(i).getZbm());
azvo.setVx("yg3");
resultList.add(azvo);
MonthReportVO tvo = new MonthReportVO();
tvo.setNm(double2String(ygList.get(i).getTaoshu()));
tvo.setVx("yg4");
resultList.add(tvo);
MonthReportVO thvo = new MonthReportVO();
thvo.setNm(ygList.get(i).getHbt());
thvo.setVx("yg5");
resultList.add(thvo);
MonthReportVO ttvo = new MonthReportVO();
ttvo.setNm(ygList.get(i).getTbt());
ttvo.setVx("yg6");
resultList.add(ttvo);
MonthReportVO ztvo = new MonthReportVO();
ztvo.setNm(ygList.get(i).getZbt());
ztvo.setVx("yg7");
resultList.add(ztvo);
}
for (int i = 0; i < qtList.size(); i++) {
MonthReportVO avo = new MonthReportVO();
avo.setNm(double2String(qtList.get(i).getArea()));
avo.setVx("qt0");
resultList.add(avo);
MonthReportVO ahvo = new MonthReportVO();
ahvo.setNm(qtList.get(i).getHbm());
ahvo.setVx("qt1");
resultList.add(ahvo);
MonthReportVO atvo = new MonthReportVO();
atvo.setNm(qtList.get(i).getTbm());
atvo.setVx("qt2");
resultList.add(atvo);
MonthReportVO azvo = new MonthReportVO();
azvo.setNm(qtList.get(i).getZbm());
azvo.setVx("qt3");
resultList.add(azvo);
MonthReportVO tvo = new MonthReportVO();
tvo.setNm(double2String(qtList.get(i).getTaoshu()));
tvo.setVx("qt4");
resultList.add(tvo);
MonthReportVO thvo = new MonthReportVO();
thvo.setNm(qtList.get(i).getHbt());
thvo.setVx("qt5");
resultList.add(thvo);
MonthReportVO ttvo = new MonthReportVO();
ttvo.setNm(qtList.get(i).getTbt());
ttvo.setVx("qt6");
resultList.add(ttvo);
MonthReportVO ztvo = new MonthReportVO();
ztvo.setNm(qtList.get(i).getZbt());
ztvo.setVx("qt7");
resultList.add(ztvo);
}
for (int i = 0; i < hjList.size(); i++) {
MonthReportVO avo = new MonthReportVO();
avo.setNm(double2String(hjList.get(i).getArea()));
avo.setVx("hj0");
resultList.add(avo);
MonthReportVO ahvo = new MonthReportVO();
ahvo.setNm(hjList.get(i).getHbm());
ahvo.setVx("hj1");
resultList.add(ahvo);
MonthReportVO atvo = new MonthReportVO();
atvo.setNm(hjList.get(i).getTbm());
atvo.setVx("hj2");
resultList.add(atvo);
MonthReportVO azvo = new MonthReportVO();
azvo.setNm(hjList.get(i).getZbm());
azvo.setVx("hj3");
resultList.add(azvo);
MonthReportVO tvo = new MonthReportVO();
tvo.setNm(double2String(hjList.get(i).getTaoshu()));
tvo.setVx("hj4");
resultList.add(tvo);
MonthReportVO thvo = new MonthReportVO();
thvo.setNm(hjList.get(i).getHbt());
thvo.setVx("hj5");
resultList.add(thvo);
MonthReportVO ttvo = new MonthReportVO();
ttvo.setNm(hjList.get(i).getTbt());
ttvo.setVx("hj6");
resultList.add(ttvo);
MonthReportVO ztvo = new MonthReportVO();
ztvo.setNm(hjList.get(i).getZbt());
ztvo.setVx("hj7");
resultList.add(ztvo);
}
return resultList;
}
/**
* 根据开始时间和结束时间统计交易信息
*
* @param startTime
* @param endTime
* @return
*/
public List<MonthReportVO> totalMoneyQueryList(
String startTime, String endTime, String area, String fwlb) {
// String fwlbStr="";
// if(fwlb ==null || "".equals(fwlb)){
// fwlbStr = " ";
// }else if("31".equals(fwlb)){
// fwlbStr = " and h.hs_use not like '1%' and h.hs_use not like '3%' ";
// }else {
// fwlbStr = " and h.hs_use like '"+fwlb+"%'";
// }
String fwlbStr = "";
if (fwlb == null || "".equals(fwlb)) {
fwlbStr = " ";
} else if ("31".equals(fwlb)) {
fwlbStr = " and h.hs_use not like '1%' and h.hs_use not like '3%' ";
} else {
fwlbStr = "and ( ";
String[] str = fwlb.split(" ");
for (String stri : str) {
String newStr = "";
if ("31".equals(stri)) {
newStr = " or ( h.hs_use not like '1%' and h.hs_use not like '3%') ";
} else {
newStr = " or h.hs_use like '" + stri + "%'";
}
fwlbStr += newStr;
}
fwlbStr = fwlbStr.replaceFirst("or", " ") + " )";
// fwlbStr = " and h.hs_use like '"+fwlb+"%'";
}
// 考虑时间的精确度
// 环比时间
String lsmStartTime = "";
String lsmEndTime = "";
// 同比时间
String lsyStartTime = TimeUtil.operateMonthToString2(startTime, 12, "minus");
String lsyEndTime = TimeUtil.operateMonthToString2(endTime, 12, "minus");
int month = 0;
if (startTime != null && startTime.length() > 7) {
int dayCha = 0;
// 环比时间
dayCha = dayCha(startTime, endTime) + 1;
lsmStartTime = jsDay(startTime, dayCha);
lsmEndTime = jsDay(endTime, dayCha);
} else {
lsmStartTime = TimeUtil.operateMonthToString2(startTime, 1, "minus");
lsmEndTime = TimeUtil.operateMonthToString2(endTime, 1, "minus");
month = monthCha(startTime, endTime);
}
// 返回结果
List<MonthReportVO> resultList = new ArrayList<MonthReportVO>();
// List<List> list = new ArrayList<List>();
// 考虑登录的用户的权限
// Map session = ActionContext.getContext().getSession();
// Org org = (Org) session.get("org");
// // 如果页面没选择县区，则默认自己辖区
// if(area==null || "".equals(area)){
// area=org.getOrgCode();
// }
Map session = ActionContext.getContext().getSession();
String areaArray[];
String regionStr = "";
Org org = (Org) session.get("org");
if (area == null || "".equals(area)) {
area = org.getRegionCode();
regionStr = " t.collection_country like '" + area + "%'";
} else {
areaArray = area.split(" ");
regionStr = " (";
for (String s : areaArray) {
regionStr += " or t.collection_country= '" + s + "'";
}
regionStr = regionStr.replaceFirst("or", " ") + ")";
}
String nyTiaoJian = "";
String nytoc = "";
if (startTime.length() > 7) {
nyTiaoJian =
" to_char(t.BOOKING_DATE,'yyyy-mm-dd') between '"
+ startTime
+ "' and '"
+ endTime
+ "' )tr";
nytoc = ",'" + startTime + "' as ny";
} else {
nyTiaoJian =
" to_char(t.BOOKING_DATE,'yyyy-mm') between '"
+ startTime
+ "' and '"
+ endTime
+ "' )tr group by to_char(tr.BOOKING_DATE,'yyyy-mm')";
nytoc = ",nvl(to_char(tr.BOOKING_DATE,'yyyy-mm'),'') as ny";
}
String bqsql =
"select nvl(sum(tr.PRICE),0)/10000 as jiner,nvl(sum(tr.AREA),0)/10000 as area, nvl(count(tr.his_hs_no),0) as taoshu"
+ nytoc
+ " from ( select distinct t.HIS_HS_NO,t.AREA,t.PRICE,t.BOOKING_DATE from T_STOCK_CONTRACT t,M_HOUSE_INFO h where t.VALID_FLG='1' and h.HIS_HS_NO=t.HIS_HS_NO and t.HIS_HS_NO = t.hs_no "
+ fwlbStr
+ " and "
+ regionStr
+ " and "
+ nyTiaoJian;
// 查询对应的数据
List<MonthReportVO> bqList = oldHouseSellDao.queryShuJu(bqsql);
String hbsql = bqsql.replace(startTime, lsmStartTime).replace(endTime, lsmEndTime);
if (startTime != null && startTime.length() > 7) {
hbsql.replaceFirst(lsmStartTime, startTime);
}
List<MonthReportVO> hbList = oldHouseSellDao.queryShuJu(hbsql);
String tbsql = bqsql.replace(startTime, lsyStartTime).replace(endTime, lsyEndTime);
if (startTime != null && startTime.length() > 7) {
hbsql.replaceFirst(lsyStartTime, startTime);
}
List<MonthReportVO> tbList = oldHouseSellDao.queryShuJu(tbsql);
// 没有的月份补0；按月分组的
if (startTime != null && startTime.length() < 9) {
// 为没有数据的月份补0
bu0(bqList, month, startTime);
bu0(hbList, month, lsmStartTime);
bu0(tbList, month, lsyStartTime);
}
// 计算价格
averageprice(bqList);
averageprice(hbList);
averageprice(tbList);
// 计算同环比
hBJiSuan(bqList, hbList);
tBJiSuan(bqList, tbList);
// 所有数据已经计算结束，考虑前台显示前台坐标的形式为名称+日期
for (int i = 0; i < bqList.size(); i++) {
// 套数
MonthReportVO vo = new MonthReportVO();
vo.setNm(String.valueOf(bqList.get(i).getTaoshu()));
vo.setVx("Taoshu" + bqList.get(i).getNy());
MonthReportVO vo1 = new MonthReportVO();
vo1.setNm(bqList.get(i).getTbt());
vo1.setVx("tbTaoshu" + bqList.get(i).getNy());
MonthReportVO vo2 = new MonthReportVO();
vo2.setNm(bqList.get(i).getHbt());
vo2.setVx("hbTaoshu" + bqList.get(i).getNy());
resultList.add(vo);
resultList.add(vo1);
resultList.add(vo2);
// 面积
MonthReportVO mvo = new MonthReportVO();
mvo.setNm(double2String(bqList.get(i).getArea()));
mvo.setVx("Area" + bqList.get(i).getNy());
MonthReportVO mvo1 = new MonthReportVO();
mvo1.setNm(bqList.get(i).getTbm());
mvo1.setVx("tbArea" + bqList.get(i).getNy());
MonthReportVO mvo2 = new MonthReportVO();
mvo2.setNm(bqList.get(i).getHbm());
mvo2.setVx("hbArea" + bqList.get(i).getNy());
resultList.add(mvo);
resultList.add(mvo1);
resultList.add(mvo2);
// 金额
MonthReportVO jvo = new MonthReportVO();
jvo.setNm(double2String(bqList.get(i).getJiner()));
jvo.setVx("Money" + bqList.get(i).getNy());
MonthReportVO jvo1 = new MonthReportVO();
jvo1.setNm(bqList.get(i).getTbj());
jvo1.setVx("tbMoney" + bqList.get(i).getNy());
MonthReportVO jvo2 = new MonthReportVO();
jvo2.setNm(bqList.get(i).getHbj());
jvo2.setVx("hbMoney" + bqList.get(i).getNy());
resultList.add(jvo);
resultList.add(jvo1);
resultList.add(jvo2);
// 均价
MonthReportVO pvo = new MonthReportVO();
pvo.setNm(bqList.get(i).getPrice());
pvo.setVx("Junjia" + bqList.get(i).getNy());
MonthReportVO pvo1 = new MonthReportVO();
pvo1.setNm(bqList.get(i).getTbp());
pvo1.setVx("tbJunjia" + bqList.get(i).getNy());
MonthReportVO pvo2 = new MonthReportVO();
pvo2.setNm(bqList.get(i).getHbp());
pvo2.setVx("hbJunjia" + bqList.get(i).getNy());
resultList.add(pvo);
resultList.add(pvo1);
resultList.add(pvo2);
}
return resultList;
}