dy-house/src/main/java/com/leeroa/dydb/zhaoshang/service/impl/QueryLogServiceImpl.java

package com.leeroa.dydb.zhaoshang.service.impl;

import com.google.common.cache.Cache;
import com.google.common.cache.CacheBuilder;
import com.leeroa.base.parameter.service.ParameterContainer;
import com.leeroa.base.utils.sql.SQLUtils;
import com.leeroa.base.utils.sql.TemplateUtils;
import com.leeroa.cache.ConfigCache;
import com.leeroa.core.index.domain.IndexWeightDetail;
import com.leeroa.core.index.utils.IndexWeightUtils;
import com.leeroa.dydb.datasource.lianjia.domain.DataLianjia;
import com.leeroa.dydb.datasource.lianjia.domain.DataLianjiaUp;
import com.leeroa.dydb.datasource.loanorder.domain.DataLoanOrder;
import com.leeroa.dydb.datasource.utils.HouseUtils;
import com.leeroa.dydb.price.domain.CommunityPrice;
import com.leeroa.dydb.price.domain.CommunityQuery;
import com.leeroa.dydb.utils.DoubleUtils;
import com.leeroa.dydb.zhaoshang.bo.QueryLogBo;
import com.leeroa.dydb.zhaoshang.dao.QueryLogDao;
import com.leeroa.dydb.zhaoshang.domain.QueryLog;
import com.leeroa.dydb.zhaoshang.service.QueryLogService;
import com.leeroa.dydb.zhaoshang.vo.QueryLogVo;
import com.michael.core.beans.BeanWrapBuilder;
import com.michael.core.beans.BeanWrapCallback;
import com.michael.core.hibernate.HibernateUtils;
import com.michael.core.hibernate.validator.ValidatorUtils;
import com.michael.core.pager.PageVo;
import com.michael.utils.SnowflakeID;
import com.michael.utils.date.DateUtils;
import com.michael.utils.gson.GsonUtils;
import com.michael.utils.string.StringUtils;
import lombok.extern.slf4j.Slf4j;
import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.TimeUnit;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @author Michael
 */
@Slf4j
@Service("queryLogService")
public class QueryLogServiceImpl implements QueryLogService, BeanWrapCallback<QueryLog, QueryLogVo> {
    @Resource
    private QueryLogDao queryLogDao;

    private Cache<String, Double> rateCache = CacheBuilder.newBuilder()
            .expireAfterWrite(1, TimeUnit.HOURS).build();

    @Override
    public String save(QueryLog queryLog) {
        validate(queryLog);
        String id = queryLogDao.save(queryLog);
        return id;
    }

    @Override
    public void update(QueryLog queryLog) {
        validate(queryLog);
        queryLogDao.update(queryLog);
    }

    private void validate(QueryLog queryLog) {
        ValidatorUtils.validate(queryLog);
    }

    @Override
    public PageVo pageQuery(QueryLogBo bo) {
        PageVo vo = new PageVo();
        Long total = queryLogDao.getTotal(bo);
        vo.setTotal(total);
        if (total == null || total == 0) {
            return vo;
        }
        List<QueryLog> queryLogList = queryLogDao.pageQuery(bo);
        List<QueryLogVo> vos = BeanWrapBuilder.newInstance()
                .setCallback(this)
                .wrapList(queryLogList, QueryLogVo.class);
        vo.setData(vos);
        return vo;
    }


    @Override
    public QueryLogVo findById(String id) {
        QueryLog queryLog = queryLogDao.findById(id);
        return BeanWrapBuilder.newInstance()
                .wrap(queryLog, QueryLogVo.class);
    }

    @Override
    public void deleteByIds(String[] ids) {
        if (ids == null || ids.length == 0) {
            return;
        }
        for (String id : ids) {
            queryLogDao.deleteById(id);
        }
    }

    @Override
    public List<QueryLogVo> query(QueryLogBo bo) {
        List<QueryLog> queryLogList = queryLogDao.query(bo);
        List<QueryLogVo> vos = BeanWrapBuilder.newInstance()
                .setCallback(this)
                .wrapList(queryLogList, QueryLogVo.class);
        return vos;
    }

    @Override
    public String getHouseNameByAddress(String address) {
        final Session session = HibernateUtils.getSession();

        String areaName = HouseUtils.getArea(address);
        // 这里不考虑非成都城区的情况
//        if (areaName != null && !HouseUtils.AREA_CHENGDU.contains(areaName)) {
//            areaName = null;
//        }
        // 获得街道号
//        String streetName = HouseUtils.getStreet(address);
//        String houseName = null;
//        // 如果没有解析到街道，则直接尝试解析小区名称。
//        if (streetName == null) {
//            houseName = HouseUtils.getHouseName(address);
//            if (houseName != null) {
//                return houseName;
//            }
//        }
//        // 这里表示没有解析出来街道，也没有解析出来小区名称
//        if (streetName == null) {
//            log.warn("地址解析失败：{}", address);
//            return null;
//        }
        //先解析小区名称
        String houseName =HouseUtils.getHouseName(address);
        String streetName = null;
        // 如果没有小区名称。再解析地址
        if (houseName != null && !"".equals(houseName)) {
            return houseName;
        }

        streetName = HouseUtils.getStreet(address);
        if (streetName == null) {
            log.warn("地址解析失败：{}", address);
            return null;
        }

                // 查询价值库中的地址。并给定区域（如果有）
        Criteria cpCriteria = session.createCriteria(CommunityPrice.class)
                .setProjection(Projections.property("houses"))
                .add(Restrictions.like("address", streetName, MatchMode.ANYWHERE));
        addAreaRestrictions(cpCriteria, areaName);
        houseName = (String) cpCriteria
                .addOrder(Order.desc("priceUpdateTime"))
                .setMaxResults(1).uniqueResult();
        if (houseName != null) {
            return houseName;
        }

        // 查询个贷中的地址。并给定区域（如果有）
        Criteria criteria = session.createCriteria(DataLoanOrder.class)
                .setProjection(Projections.property("houses"))
                .add(Restrictions.like("address", streetName, MatchMode.ANYWHERE));
        addAreaRestrictions(criteria, areaName);
        houseName = (String) criteria
                .addOrder(Order.desc("priceDate"))
                .setMaxResults(1).uniqueResult();
        if (houseName != null) {
            return houseName;
        }

        // 尝试直接解析一下小区名称
        houseName = HouseUtils.getHouseName(address);
        return houseName;
    }

    public Double getAvgPriceByRoad(String road) {
        final Session session = HibernateUtils.getSession();

        // 查询价值库中的地址。并给定区域（如果有）
        return (Double) session.createCriteria(CommunityPrice.class)
                .setProjection(Projections.avg("price"))
                .add(Restrictions.like("address", road, MatchMode.ANYWHERE))
                .uniqueResult();
    }

    @Override
    public Double getPriceByHouseName(String houseName) {
        final Session session = HibernateUtils.getSession();
        CommunityPrice communityPrice = (CommunityPrice) session.createCriteria(CommunityPrice.class)
                .add(Restrictions.eq("houses", houseName))
                .addOrder(Order.desc("priceUpdateTime"))
                .setMaxResults(1)
                .uniqueResult();
        if (communityPrice != null) {
            return communityPrice.getPrice();
        }
        return null;
    }

    private void addAreaRestrictions(Criteria criteria, String areaName) {
        if (areaName == null) {
            return;
        }
        // 历史名称的特殊处理
        if (StringUtils.include(areaName, "双流区", "天府新区", "双流县")) {
            criteria.add(Restrictions.in("areaName", "双流区", "天府新区", "双流县"));
        } else if (StringUtils.include(areaName, "高新区", "武侯区")) {
            criteria.add(Restrictions.in("areaName", "高新区", "武侯区"));
        } else if (StringUtils.include(areaName, "郫都区", "郫县")) {
            criteria.add(Restrictions.in("areaName", "郫都区", "郫县"));
        } else {
            criteria.add(Restrictions.eq("areaName", areaName));
        }
    }

    @Override
    public CommunityQuery queryByZhaoShang(QueryLog ql) {
        // 保存搜索记录
        save(ql);

        final Session session = HibernateUtils.getSession();
        Date now = DateUtils.getDayBegin(new Date());

        CommunityQuery cq = new CommunityQuery();
        // 案例均价、最大值、最小值、案例数量、12个月走势
        cq.setProvinceName(HouseUtils.getProvince(ql.getAddress()));
        cq.setCityName(HouseUtils.getCity(ql.getAddress()));
        cq.setHouseName(ql.getName());
        cq.setAddress(ql.getAddress());
        // 评估状态
        cq.setStatus("Y");

        // 房屋用途写死(001住宅，002公寓，026车库）
        cq.setUsage("1002001");
        // 询价时间
        cq.setQueryTime(DateUtils.formatDatetime(now));

        String address = ql.getAddress();
        String houseName = ql.getName();

        // 获取城市，如果不是成都市的，则返回
        String cityName = HouseUtils.getCity(address);
//        if (cityName != null && !cityName.contains("成都")) {
//            log.info("招商查询，非成都地区，直接返回：{}", address);
//            cq.setStatus("N");
//            cq.setRemark(CommunityQu
//            cq.setHouseName("");ery.ERROR_TYPE_NOT_LOCAL);
//            return cq;

 //       }
        // 获取区县，如果不是成都市的，也返回
        String areaName = HouseUtils.getArea(address);
//        if (StringUtils.isNotEmpty(areaName) && HouseUtils.AREA_OTHER.contains(areaName)) {
//            log.info("招商查询，非成都地区，直接返回：{}", address);
//            cq.setStatus("N");
//            cq.setHouseName("");
//            cq.setRemark(CommunityQuery.ERROR_TYPE_NOT_LOCAL);
//            return cq;
//        }

        // 如果没有传递小区名称，则通过地址查询一次个贷数据
        if (StringUtils.isEmpty(houseName)) {
            houseName = getHouseNameByAddress(address);
        }
        final Double area = Double.parseDouble(ql.getBuildingArea());
        // 没有解析出来小区
        if (houseName == null) {
            String road = HouseUtils.getRoad(address);
            if (road == null) {
                cq.setStatus("N");
                cq.setHouseName("");
                cq.setRemark(CommunityQuery.ERROR_TYPE_PARSE_FAIL);
                return cq;
            }
            // 通过道路号，再查询一次
            Double price = getAvgPriceByRoad(road);
            if (price == null) {
                cq.setStatus("N");
                cq.setHouseName("");
                cq.setRemark(CommunityQuery.ERROR_TYPE_PARSE_FAIL);
                return cq;
            }
            // 调用价格系数进行一次处理
            price = getRatePrice(price);
            cq.setAreaName(HouseUtils.getChengDuArea(address));
            cq.setHouseName(road);
            cq.setCommunityName(road);
            cq.setPrice(price);
            cq.setMinPrice(price);
            cq.setMaxPrice(price);
            cq.setPrice(price);
            cq.setHouseAvgPrice(price);

            // 案例数量
            cq.setHouseCases(1);
            // 案例总价（原始数据是按照万元计算）
            cq.setMoney(price * area);
            cq.setBuildArea(area);
            cq.setHouseAvgPrice(price);
            cq.setCommunityAvgPrice(price);
            cq.setRemark(CommunityQuery.MATCH_TYPE_STREET);
            return cq;
        }


        // 根据小区名称获取小区的数据
        CommunityPrice communityPrice = null;
        while (true) {
//            communityPrice = (CommunityPrice) session.createCriteria(CommunityPrice.class)
//                    .add(Restrictions.eq("houses", houseName))
//                    .add(Restrictions.eq("cityName",cityName))
//                    .add(Restrictions.eq("areaName",areaName))
//                    .addOrder(Order.desc("priceUpdateTime"))
//                    .setMaxResults(1)
//                    .uniqueResult();
            Criteria criteria = session.createCriteria(CommunityPrice.class);
            criteria.add(Restrictions.eq("houses", houseName));
            if (StringUtils.isNotEmpty(cityName)){
                criteria.add(Restrictions.eq("cityName",cityName));
            }
            if (StringUtils.isNotEmpty(areaName)){
                criteria.add(Restrictions.eq("areaName",areaName));
            }
            criteria.addOrder(Order.desc("priceUpdateTime"));
            criteria.setMaxResults(1);
            communityPrice = (CommunityPrice) criteria.uniqueResult();
            if (communityPrice != null) {
                break;
            }

            // 如果是小区名称中包含几期xx区这种数据，则移出过后再查询一次
            Pattern pattern = Pattern.compile("(.+)[一二三四五六七八九十\\w]+[期区]");
            Matcher matcher = pattern.matcher(houseName);
            if (matcher.find()) {
                houseName = matcher.group(1);
                cq.setRemark(CommunityQuery.MATCH_TYPE_LIKE);
                continue;
            }

            // 为了提高命中率，按照名称再模糊匹配一次
//            communityPrice = (CommunityPrice) session.createCriteria(CommunityPrice.class)
//                    .add(Restrictions.like("houses", houseName, MatchMode.START))
//                    .add(Restrictions.eq("cityName",cityName))
//                    .add(Restrictions.eq("areaName",areaName))
//                    .setMaxResults(1)
//                    .addOrder(Order.asc("houses"))
//                    .uniqueResult();

            Criteria criteria1 = session.createCriteria(CommunityPrice.class);
            criteria1.add(Restrictions.like("houses", houseName, MatchMode.START));
            if (StringUtils.isNotEmpty(cityName)){
                criteria1.add(Restrictions.eq("cityName",cityName));
            }
            if (StringUtils.isNotEmpty(areaName)){
                criteria1.add(Restrictions.eq("areaName",areaName));
            }
            criteria1.addOrder(Order.desc("priceUpdateTime"));
            criteria1.setMaxResults(1);
            communityPrice = (CommunityPrice) criteria1.uniqueResult();

            cq.setRemark(CommunityQuery.MATCH_TYPE_LIKE);
            break;
        }
        cq.setCommunityName(houseName);
        cq.setHouseName(houseName);

        log.info("查询小区数据 : {} => {}", houseName, GsonUtils.toJson(communityPrice));

        // 基本上不存在查询不出来的情况
        if (communityPrice != null) {
            cq.setCaseId(communityPrice.getId());
            cq.setProvinceName(communityPrice.getProvinceName());
            cq.setCityName(communityPrice.getCityName());
            // 行政区
            cq.setAreaName(communityPrice.getAreaName());
            // 设置经纬度
            if (StringUtils.isNotEmpty(communityPrice.getLatitude())) {
                cq.setBaiduLat(Double.parseDouble(communityPrice.getLatitude()));
            }
            if (StringUtils.isNotEmpty(communityPrice.getLongitude())) {
                cq.setBaiduLng(Double.parseDouble(communityPrice.getLongitude()));
            }
            String buildYear = communityPrice.getBuildYear();
            if (StringUtils.isEmpty(buildYear)) {
                // 从链家的数据里取一次建成年份
                buildYear = (String) session.createCriteria(DataLianjia.class)
                        .setProjection(Projections.property("buildYear"))
                        .add(Restrictions.eq("houses", houseName))
                        .add(Restrictions.isNotNull("buildYear"))
                        .setMaxResults(1)
                        .uniqueResult();
            }
            if (StringUtils.isEmpty(buildYear)) {
                // 如果依旧没有年份，则从挂牌数据中取一次
                buildYear = (String) session.createCriteria(DataLianjiaUp.class)
                        .setProjection(Projections.property("buildYear"))
                        .add(Restrictions.eq("houses", houseName))
                        .add(Restrictions.isNotNull("buildYear"))
                        .setMaxResults(1)
                        .uniqueResult();
            }
            if (StringUtils.isEmpty(buildYear)) {
                // 如果依旧没有年份，则写死一个2000
                buildYear = "2020";
            }
            cq.setBuildYear(buildYear);
            cq.setId(communityPrice.getId() + "_" + SnowflakeID.getInstance().next());
            cq.setCommunityAlias(communityPrice.getHouses());
            // 如果地址中包含成都，则表示是全路径的地址
            if (communityPrice.getAddress() != null && communityPrice.getAddress().contains("成都")) {
//                address = communityPrice.getAddress().split(",")[0];
            }

            // 建筑面积
            cq.setBuildArea(area);
            // 最小单价
            // 最大单价
            // 均价
            Double price = getRatePrice(communityPrice.getPrice());
            // 获得参数化过后的价格

            cq.setMinPrice(price);
            cq.setMaxPrice(price);
            cq.setPrice(price);
            cq.setHouseAvgPrice(price);
            cq.setMngPrice(communityPrice.getMngPrice());
            // 案例数量
            cq.setHouseCases(1);

            // 案例总价（原始数据是按照万元计算）
            cq.setMoney(price * area);

            // 物业费
            cq.setMngPrice(communityPrice.getMngPrice());

            // 总套数
            cq.setTotalHouse(communityPrice.getHouseQuantity());
        } else {
//            cq.setRemark(CommunityQuery.ERROR_TYPE_NO_DATA);
//            cq.setStatus("N");
            //再通过地址模糊匹配
//            communityPrice = (CommunityPrice) session.createCriteria(CommunityPrice.class)
//                    .add(Restrictions.like("address", houseName))
//                    .add(Restrictions.eq("cityName",cityName))
//                    .add(Restrictions.eq("areaName",areaName))
//                    .addOrder(Order.desc("priceUpdateTime"))
//                    .setMaxResults(1)
//                    .uniqueResult();

            Criteria criteria = session.createCriteria(CommunityPrice.class);
            criteria.add(Restrictions.like("address", houseName,MatchMode.ANYWHERE));
            if (StringUtils.isNotEmpty(cityName)){
                criteria.add(Restrictions.eq("cityName",cityName));
            }
            if (StringUtils.isNotEmpty(areaName)){
                criteria.add(Restrictions.eq("areaName",areaName));
            }
            criteria.addOrder(Order.desc("priceUpdateTime"));
            criteria.setMaxResults(1);
            communityPrice = (CommunityPrice) criteria.uniqueResult();

            if (communityPrice == null){
                cq.setRemark(CommunityQuery.ERROR_TYPE_NO_DATA);
                cq.setStatus("N");
            }else {
                houseName = communityPrice.getHouses();
                cq.setCommunityName(houseName);
                cq.setHouseName(houseName);
                cq.setAreaName(communityPrice.getAreaName());
                cq.setCityName(communityPrice.getCityName());
                Double price = getRatePrice(communityPrice.getPrice());
                cq.setPrice(price);
                cq.setMinPrice(price);
                cq.setMaxPrice(price);
                cq.setBuildArea(area);
                cq.setMoney(price * area);
                cq.setMngPrice(communityPrice.getMngPrice());
                // 总套数
                cq.setTotalHouse(communityPrice.getHouseQuantity());
            }
        }

        cq.setAddress(address);
        // 根据地址解析出街道
        cq.setStreetName(HouseUtils.getStreet(address));

        // 根据地址解析出门牌号
        Optional<String> doorNOOp = HouseUtils.getHouseNO(address);
        doorNOOp.ifPresent(cq::setDoorName);

        // 根据地址解析出楼栋
        Optional<String> buildOp = HouseUtils.getBuildingName(address);
        buildOp.ifPresent(cq::setBuildingName);
        // 根据地址解析出房号
        Optional<String> houseNOOp = HouseUtils.getHouseNO(address);
        houseNOOp.ifPresent(cq::setHouseName);

        // 趋势类型
        cq.setTrendType("CONST");
        // 查询12个月的房价
        // 获得开始时间和截至时间
        Date startDate = DateUtils.addMonth(now, -12);
        Map<String, Object> params = new HashMap<>();
        params.put("startDate", startDate);
        params.put("endDate", now);
        params.put("houses", houseName);
        String sql = TemplateUtils.getTemplate("fmt/ql.fmt", params, ConfigCache.isDebug());
        List<Map<String, Object>> data = SQLUtils.listMap(sql, params);
        List<Map<String, Object>> trendData = new ArrayList<>();
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM");
        Double totalAvg = 0d;
        Double prevAvgPrice = 0d;
        for (int i = 0; i < 12; i++) {
            Date current = DateUtils.addMonth(startDate, i);
            String dt = dateFormat.format(current);
            // 要拼接的格式为:时间,单价,总价
            // 从查询结果中找出对应的月份数据
            Map<String, Object> innerData = new HashMap<>();

            for (Map<String, Object> map : data) {
                String month = (String) map.get("dt");
                if (StringUtils.equals(month, dt)) {
                    innerData.put("EVAL_DATE", dt);

                    Double avgPrice = (Double) map.get("avgPrice");
                    prevAvgPrice = avgPrice;
                    totalAvg += avgPrice;
                    // 设置最小值
                    Double minPrice = (Double) map.get("minPrice");
                    if (minPrice < cq.getMinPrice()) {
                        cq.setMinPrice(minPrice);
                    }
                    // 设置最大值
                    Double maxPrice = (Double) map.get("maxPrice");
                    if (maxPrice > cq.getMaxPrice()) {
                        cq.setMaxPrice(maxPrice);
                    }
                    // 设置当前月份的均价
                    Double money = avgPrice * area;
                    innerData.put("UNIT_PRICE", avgPrice);
                    innerData.put("TOTAL_PRICE", money);
                    break;
                }
            }
            // 如果没有匹配到，则取上一个月月份的数据
            if (innerData.isEmpty()) {
                innerData.put("EVAL_DATE", dt);
                innerData.put("UNIT_PRICE", prevAvgPrice);
                innerData.put("TOTAL_PRICE", prevAvgPrice * area);
            }
            trendData.add(innerData);
        }
        // 再次设置总的均价
        if (data.size() > 0) {
            cq.setHouseAvgPrice(totalAvg / data.size());
        }

        cq.setTrendData(trendData);
        return cq;
    }

    private double getRatePrice(Double price) {
        if (price == null || price == 0) {
            return 0;
        }
        String key = "招商价格浮动比例";
        Double rate = rateCache.getIfPresent(key);
        if (rate == null) {
            IndexWeightDetail iwd = IndexWeightUtils.getIndex("其他配置", "招商价格浮动比例");
            if (iwd != null && StringUtils.isNotEmpty(iwd.getValues()) && iwd.getValues().matches("\\d+(\\.\\d+)?")) {
                rate = DoubleUtils.parse(iwd.getValues());
            } else {
                rate = 0.0;
            }
            rateCache.put(key, rate);
        }
        return price * rate * 0.01;
    }

    @Override
    public void doCallback(QueryLog queryLog, QueryLogVo vo) {
        ParameterContainer container = ParameterContainer.getInstance();

    }
}