package com.sysu.admin.controller.aland;

import com.xiesx.fastboot.core.jpa.JpaPlusRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

public interface LandRepository extends JpaPlusRepository<Land, Long> {

    @Query(value = "select town_id,crop_type,sum(area),count(id) from  leizhou_land where district_code = ?1 and town_id is not null  group by town_id,crop_type",nativeQuery = true)
    List<Object[]> statTownCropTypeAll(String districtCode);

    @Query(value = "select district_code,crop_type,sum(area),count(id) from  leizhou_land where district_code is not null and district_code like ?1  group by district_code,crop_type",nativeQuery = true)
    List<Object[]> statDistrictCropTypeAll(String likeCity);

    @Query(value = "select town_id,parcel_type,sum(area),count(id) from leizhou_land where district_code = ?1 and town_id is not null group by town_id,parcel_type",nativeQuery = true)
    List<Object[]> statTownParcelTypeAll(String district);

    @Query(value = "select district_code,parcel_type,sum(area),count(id) from leizhou_land where district_code is not null and district_code like ?1 group by district_code,parcel_type",nativeQuery = true)
    List<Object[]> statDistrictParcelTypeAll(String likeCity);

    @Query(value = "select district_code, sum(area * dan_chan),sum(area) as areasum from leizhou_land where district_code is not null and district_code like ?1 and crop_type = ?2 group by district_code",nativeQuery = true)
    List<Object[]> statDistrictEstimate(String likeCity, String crop_type);

    @Query(value = "select town_id,sum(area * dan_chan),sum(area) as areasum from  leizhou_land where district_code = ?1 and town_id is not null and crop_type = ?2 group by town_id",nativeQuery = true)
    List<Object[]> statTownEstimate(String districtCode, String crop_type);




    @Query(value = "select new Land(geom) from Land")
    List<Land> findAllGeom();

    @Query(value = "SELECT * FROM leizhou_land where St_within(st_geomfromtext(?1,4326), geom) limit 1", nativeQuery = true)
    Land findByPoint(String wkt);

}
