package com.sysu.admin.controller.aland;

import com.mchange.v2.sql.SqlUtils;
import com.sysu.admin.controller.city.CityRepository;
import com.sysu.admin.controller.city.DistrictRepository;
import com.sysu.admin.controller.city.DistrictService;
import com.sysu.admin.controller.city.TownService;
import com.sysu.admin.site.CommonVo;
import com.sysu.admin.support.base.BaseService;
import com.sysu.admin.support.cfg.CacheCfg;
import com.sysu.admin.utils.GenericsUtil;
import com.sysu.admin.utils.MySimpleDateFormat;
import com.sysu.admin.utils.SqlUtil;
import com.sysu.admin.utils.TextUtil;
import com.xiesx.fastboot.base.pagination.PaginationHelper;
import com.xiesx.fastboot.base.pagination.PaginationResult;
import com.xiesx.fastboot.base.pagination.PaginationVo;
import com.xiesx.fastboot.base.result.BaseResult;
import com.xiesx.fastboot.core.jpa.JpaPlusRepository;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.stereotype.Service;

import java.util.*;

@Service("LandService2")
public class LandService extends BaseService<Land,Long> {
    @Autowired
    LandRepository landRepository;
    @Autowired
    TownService townService;
    @Autowired
    DistrictService districtService;
    @Autowired
    DistrictRepository districtRepository;
    @Autowired
    CityRepository cityRepository;

    /**
     * 农业地块统计
     * @param districtCode
     * @param city
     * @return
     */
    @Cacheable(key = "#districtCode+#city+#month", value = CacheCfg.LandService_getLandAreaStat)
    public List<TownStatLand> getLandAreaStat(Integer month, String districtCode, String city){
        Double percent = getPercent(month);
        List<Object[]> res = null;
        Map<Integer,String> namesMap = null;
        if(districtCode != null){
            namesMap = townService.getNames(districtCode);
            res = landRepository.statTownParcelTypeAll(districtCode);
            return getLandAreaStat(res,percent,namesMap);
        }
        if(city != null){
            namesMap = districtService.getNames(city);
            res = landRepository.statDistrictParcelTypeAll(city.substring(0,4) + "%");
            return getLandAreaStat(res,percent,namesMap);
        }
        throw new RuntimeException("district 和 city 至少需要一个");
    }
    public List<TownStatLand> getLandAreaStat(List<Object[]> res , Double percent, Map<Integer,String> namesMap){
        List<TownStatLand> townStatList = new ArrayList<>(30);
        TownStatLand sumStat = new TownStatLand();
        sumStat.setTownName("汇总信息");
        townStatList.add(sumStat);
        Integer currentTownId = null;
        TownStatLand townStat = null;

        HashMap<String,LandAreaStat > sumMap = new HashMap<>();

        for(Object[] o : res){
            if(currentTownId == null || !o[0].toString().equals(currentTownId.toString())){
                townStat = new TownStatLand();
                townStat.setList(new ArrayList<>());
                currentTownId = Integer.valueOf(o[0].toString());
                townStat.setTownId(currentTownId);
                townStat.setTownName(namesMap.get(currentTownId));
                townStatList.add(townStat);
            }

            LandAreaStat bean = new LandAreaStat();
            bean.setParcelType(Integer.valueOf(o[1].toString()));
            bean.setColor(LandAreaStat.castColor(bean.getParcelType()));
            bean.setName(LandAreaStat.castName(bean.getParcelType()));
            Double area = (Double)o[2];
            bean.setArea((area + area / 2) / 1000);
            bean.setArea(bean.getArea() * percent);
            bean.setCount(Integer.valueOf(o[3].toString()));
            townStat.getList().add(bean);
            //汇总信息
            LandAreaStat sum = sumMap.get(bean.getParcelType());
            if(sum == null){
                sum = new LandAreaStat(bean.getParcelType(), bean.getName(), bean.getColor());
                sumMap.put(bean.getParcelType().toString(), sum);
            }
            sum.setArea(sum.getArea() + bean.getArea());
            sum.setCount(sum.getCount() + bean.getCount());
        }

        //组装汇总List
        LandAreaStat[] sumArrays = new LandAreaStat[sumMap.size()];
        for(String key : sumMap.keySet()){
            sumArrays[Integer.valueOf(key)] = sumMap.get(key);
        }
        sumStat.setList(Arrays.asList(sumArrays));
        return townStatList;
    }

    /**
     * 根据月份得到一个1到 1.5的系数
     * @param month
     * @return
     */
    public Double getPercent(Integer month){
        if(month == null){
            return 1.0;
        }
        Double percent = Double.parseDouble("1." + month % 5);
        return percent;
    }

    /**
     * 种植类型统计
     * @return
     */
    @Cacheable(key = "#districtCode+#city+#month", value = CacheCfg.LandService_getCropAreaStat)
    public List<TownStatCrop> getCropAreaStat(Integer month, String districtCode, String city){
        Double percent = getPercent(month);
        List<Object[]> res = null;
        Map<Integer,String> namesMap = null;
        if(districtCode != null){
            namesMap = townService.getNames(districtCode);
            res = landRepository.statTownCropTypeAll(districtCode);
            return getCropAreaStat(res,percent,namesMap);
        }
        if(city != null){
            namesMap = districtService.getNames(city);
            res = landRepository.statDistrictCropTypeAll(city.substring(0,4) + "%");
            return getCropAreaStat(res,percent,namesMap);
        }
        throw new RuntimeException("district 和 city 至少需要一个");
    }

    public List<TownStatCrop> getCropAreaStat(List<Object[]> res ,Double percent, Map<Integer,String> namesMap){
        List<TownStatCrop> townStatList = new ArrayList<>(30);
        TownStatCrop sumStat = new TownStatCrop();
        sumStat.setTownName("汇总信息");
        townStatList.add(sumStat);
        Integer currentTownId = null;
        TownStatCrop townStat = null;

        HashMap<String,CropAreaStat > sumMap = new HashMap<>();

        for(Object[] o : res){
            if(currentTownId == null || !o[0].toString().equals(currentTownId.toString())){
                townStat = new TownStatCrop();
                townStat.setList(new ArrayList<>());
                currentTownId = Integer.valueOf(o[0].toString());
                townStat.setTownId(currentTownId);
                townStat.setTownName(namesMap.get(currentTownId));
                townStatList.add(townStat);
            }

            CropAreaStat bean = new CropAreaStat();
            bean.setCropType(o[1].toString());
            bean.setColor(CropAreaStat.castColor(bean.getCropType()));
            bean.setName(CropAreaStat.castName(bean.getCropType()));
            Double area = (Double)o[2];
            bean.setArea((area + area / 2) / 1000);
            bean.setArea(bean.getArea() * percent);
            bean.setCount(Integer.valueOf(o[3].toString()));
            townStat.getList().add(bean);
            //汇总信息
            CropAreaStat sum = sumMap.get(bean.getCropType());
            if(sum == null){
                sum = new CropAreaStat(bean.getCropType(), bean.getName(), bean.getColor());
                sumMap.put(bean.getCropType(), sum);
            }
            sum.setArea(sum.getArea() + bean.getArea());
            sum.setCount(sum.getCount() + bean.getCount());
        }

        //组装汇总List
        CropAreaStat[] sumArrays = new CropAreaStat[sumMap.size()];
        for(String key : sumMap.keySet()){
            sumArrays[CropAreaStat.getCropTypeIndex(key)] = sumMap.get(key);
        }
        sumStat.setList(Arrays.asList(sumArrays));
        return townStatList;
    }

    /**
     * 土壤信息统计
     * @param districtCode 县code
     * @param city 城市code
     * @param type 统计类型 ph, toc, c, _2n, p, k
     * @return
     */
    @Cacheable(key = "#districtCode+#city+#type", value = CacheCfg.LandService_getStatSoil)
    public List<TownStatSoil> getStatSoil(String districtCode, String city, String type){
        List<Object[]> res = null;
        Map<Integer,String> namesMap = null;
        String appendWhere = "";
        String groupField = "";
        if(StringUtils.isNotBlank(districtCode)){
            namesMap = townService.getNames(districtCode);
            appendWhere += " and district_code = '" +districtCode+"'";
            groupField += "town_id";
        }else if(StringUtils.isNotBlank(city)){
            namesMap = districtService.getNames(city);
            appendWhere += " and district_code like '" + city.substring(0,4) + "%'";
            groupField += "district_code";
        }
        if(namesMap == null){
            throw new RuntimeException("district 和 city 至少需要一个");
        }
        String[] fields = StatItemData.typeMap.get(type);
        String sql = "select " + groupField + ", "+fields[1]+", sum(area) from leizhou_land where "+groupField+" is not null "+appendWhere+" group by "+groupField+","+fields[1];

        //最大值最小值平均值
        String sql2 = "select min("+fields[0]+"),avg("+fields[0]+"), max("+fields[0]+") from leizhou_land where "+groupField+" is not null" + appendWhere;

        //城市数据的分组统计信息
        res = mEntityManager.createNativeQuery(sql).getResultList();

        //最小值最大值平均值的统计信息
        Object[] objects = (Object[])mEntityManager.createNativeQuery(sql2).getSingleResult();

        List<TownStatSoil> list = getStatSoil(res, namesMap, type);
        TownStatSoil townStatSoilSum = new TownStatSoil();
        townStatSoilSum.setTownName("汇总信息");
        List<StatSoil> sumItemList = new ArrayList<>(3);
        townStatSoilSum.setList(sumItemList);
        sumItemList.add(new StatSoil("最小值",Double.valueOf(objects[0].toString())));
        sumItemList.add(new StatSoil("平均值",Double.valueOf(objects[1].toString())));
        sumItemList.add(new StatSoil("最大值",Double.valueOf(objects[2].toString())));
        list.set(0, townStatSoilSum);
        return list;
    }

    public List<TownStatSoil> getStatSoil(List<Object[]> res, Map<Integer,String> namesMap, String type){
        List<TownStatSoil> townStatSoilList = new ArrayList<>(30);
        townStatSoilList.add(null);
        Integer currentTownId = null;//当前城市id
        TownStatSoil townStatSoil = null;//当前城市对象
        for(Object[] o : res){
            Double area = Double.valueOf(o[2].toString());
            Integer level = Integer.valueOf(o[1].toString());
            Integer townId = Integer.valueOf(o[0].toString());
            if(currentTownId == null || !townId.equals(currentTownId)){
                townStatSoil = new TownStatSoil();
                townStatSoil.setList(new ArrayList<>());
                currentTownId = townId;
                townStatSoil.setTownId(currentTownId);
                townStatSoil.setTownName(namesMap.get(currentTownId));
                townStatSoilList.add(townStatSoil);
            }
            StatSoil bean = new StatSoil();
            bean.setValue(area);
            bean.setColor(StatItemData.get(type, "Colors")[level]);
            bean.setName(StatItemData.get(type, "Names")[level]);
            bean.setValue((area + area / 2) / 1000);
            townStatSoil.getList().add(bean);
        }
        return townStatSoilList;
    }

    @Cacheable(key = "#districtCode+#city+#cropType", value = CacheCfg.LandService_getEstimate)
    public List<StatEstimate> getEstimate(String districtCode, String city, String cropType){
        List<Object[]> res = null;
        Map<Integer,String> namesMap = null;
        if(StringUtils.isNotBlank(districtCode)){
            namesMap = townService.getNames(districtCode);
            res = landRepository.statTownEstimate(districtCode, cropType);
            return getEstimate(res, namesMap);
        }
        if(StringUtils.isNotBlank(city)){
            namesMap = districtService.getNames(city);
            res = landRepository.statDistrictEstimate(city.substring(0,4) + "%", cropType);
            return getEstimate(res, namesMap);
        }
        throw new RuntimeException("district 和 city 至少需要一个");
    }

    public List<StatEstimate> getEstimate(List<Object[]> res, Map<Integer,String> namesMap){
        List<StatEstimate> list = new ArrayList<>();
        for(Object[] objects : res){
            StatEstimate statEstimate = new StatEstimate();
            statEstimate.setTownId(Integer.valueOf(objects[0].toString()));
            statEstimate.setTownName(namesMap.get(statEstimate.getTownId()));
            statEstimate.setValue(Double.valueOf(objects[1].toString()));
            Double area =Double.valueOf(objects[2].toString());
            statEstimate.setArea((area + area / 2) / 1000);
            list.add(statEstimate);
        }
        return list;
    }



    public AllLandStatVo allLandStat(CommonVo commonVo){
        String wkt = null;
        if(commonVo.getTown() != null) {
            wkt = townService.findWktById(commonVo.getTown());
        }else if(StringUtils.isNotBlank(commonVo.getDistrict())) {
            wkt = districtRepository.findWktByCode(commonVo.getDistrict());
        }
        StringBuffer sql = new StringBuffer(100);
        String col = "count(1) as all_count, sum(area) as sum_area, sum(dan_chan * area) as zong_chan ,avg(toc) as avg_toc,avg(ph) as avg_ph,avg(c) as avg_c,avg(_2n) as avg_2n,avg(p) as avg_p,avg(k) as avg_k";
        sql.append("select "+col+" from  leizhou_land where 1=1");
        StringBuffer append = appendWhere(commonVo, wkt);
        sql.append(append);
        AllLandStatVo allLandStatVo = (AllLandStatVo)mEntityManager.createNativeQuery(sql.toString(), AllLandStatVo.class).getSingleResult();
        return allLandStatVo;
    }


    public PaginationResult list(CommonVo commonVo,PaginationVo page){
        String wkt = null;
        if(commonVo.getTown() != null) {
             wkt = townService.findWktById(commonVo.getTown());
        }else if(StringUtils.isNotBlank(commonVo.getDistrict())) {
             wkt = districtRepository.findWktByCode(commonVo.getDistrict());
        }
        List<Land> data = null;
        int count;
        int start = page.getLimit() * page.getPage();
        StringBuffer countSql = new StringBuffer(100);
        StringBuffer sql = new StringBuffer(100);
        countSql.append("select count(1) from  leizhou_land where 1=1");
        sql.append("select * from  leizhou_land where 1=1");
        StringBuffer append = appendWhere(commonVo, wkt);
        sql.append(append);
        countSql.append(append);

        sql.append(" limit ").append(page.getLimit()).append(" OFFSET ").append(start);
        count = Integer.valueOf(mEntityManager.createNativeQuery(countSql.toString()).getSingleResult().toString());
        data = mEntityManager.createNativeQuery(sql.toString(), Land.class).getResultList();
        return PaginationHelper.create(data, count, page.page);
    }

    public StringBuffer appendWhere(CommonVo commonVo, String wkt){

        StringBuffer sql = new StringBuffer(100);
        if(wkt != null){
            sql.append(" and St_within(geom,st_geomfromtext('");
            sql.append(wkt).append("',4326))");
        }
        if(StringUtils.isNotBlank(commonVo.getCrop_type())) {
            sql.append(" and crop_type = '").append(commonVo.getCrop_type()).append("'");
        }
        if(commonVo.getParcel_type() != null) {
            sql.append(" and parcel_type = ").append(commonVo.getParcel_type());
        }
        sql.append(SqlUtil.ArrayToInSql(" and ", "parcel_type", commonVo.getParcel_types()));
        sql.append(SqlUtil.ArrayToInSql(" and ", "crop_type", commonVo.getCrop_types()));

        sql.append(intervalWhere("crop_sure", commonVo.getSure()));
        sql.append(intervalWhere("dan_chan", commonVo.getDanchan()));
        sql.append(intervalWhere("toc", commonVo.getToc()));
        sql.append(intervalWhere("ph", commonVo.getPh()));
        sql.append(intervalWhere("c", commonVo.getC()));
        sql.append(intervalWhere("_2n", commonVo.get_2n()));
        sql.append(intervalWhere("p", commonVo.getP()));
        sql.append(intervalWhere("k", commonVo.getK()));

        return sql;
    }

    private StringBuffer intervalWhere(String col, Double[] values){
        StringBuffer res = new StringBuffer(40);
        if(ArrayUtils.isNotEmpty(values)){
            res.append(" and "+col+" >= ").append(values[0]);
            if(values.length > 1)
                res.append(" and "+col+" <= ").append(values[1]);
        }
        return res;
    }


    public Land findByPoint(Double[] point){
        return landRepository.findByPoint("Point("+point[0]+" "+point[1]+")");
    }

    @Override
    public JpaPlusRepository<Land, Long> r() {
        return landRepository;
    }
}


















