package cn.com.countrygarden.bi.main.service.dup.impl;

import cn.com.countrygarden.bi.main.dao.dup.DivertStatisticalMapper;
import cn.com.countrygarden.bi.main.model.PageData;
import cn.com.countrygarden.bi.main.model.dup.DivertStatistical;
import cn.com.countrygarden.bi.main.service.dup.DivertStatisticalService;
import cn.com.countrygarden.bi.main.util.DateUtil;
import com.github.pagehelper.PageRowBounds;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import javax.annotation.Resource;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

/**
 * <pre>
 * 。转名统计ServiceImpl
 * </pre>
 * 
 * @author developer developer@meicloud.com
 * @version 1.00.00
 * 
 *          <pre>
 * 修改记录
 *    修改后版本:     修改人：  修改日期:     修改内容:
 * </pre>
 */
@Service
@Transactional
public class DivertStatisticalServiceImpl implements DivertStatisticalService {
	
	
	@Resource
	private DivertStatisticalMapper divertStatisticalMapper;
	@Autowired
	private JdbcTemplate jdbcTemplate;


	private final static Logger logger = LoggerFactory.getLogger(DivertStatisticalServiceImpl.class);

	@SuppressWarnings("null")
	@Override
	public List<DivertStatistical> findDivertStatical(DivertStatistical divertStatistical) {
		String actualPropertyEnd = divertStatistical.getActualPropertyEnd();
		if (StringUtils.hasText(actualPropertyEnd)) {
			// todo 使用工具类
			Date date = DateUtil.parseDate(actualPropertyEnd);
			Calendar cal = Calendar.getInstance();
			cal.setTime(date);
			cal.add(Calendar.DATE, 1);
			Date time = cal.getTime();
			String format = new SimpleDateFormat("yyyy-MM-dd").format(time);
			divertStatistical.setActualPropertyEnd(format);
		}

		String sql = getStringBuilder(divertStatistical).toString();

        List<DivertStatistical> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DivertStatistical.class));

        return list;
	}


/*
	private List<DivertStatistical> getLimit(PageRowBounds pageRowBounds,
												 List<DivertStatistical> commonRegionalRanking) {
		int offset = pageRowBounds.getOffset();
		int limit = pageRowBounds.getLimit();
		int o=offset-1;
		int star=o*limit;

		if(commonRegionalRanking.size()>0){
			int end =commonRegionalRanking.size()-star>limit?star+limit:commonRegionalRanking.size();

			List<DivertStatistical>   list = commonRegionalRanking.subList(star, end==0?star:end);
			return list;
		}else{
			return commonRegionalRanking;
		}
	}



*/


	private StringBuilder getStringBuilder(DivertStatistical divertStatistical) {
		StringBuilder sql=new StringBuilder();
		sql.append("\n" +
				"\tSELECT DISTINCT\n" +
				"\t\ttso.OrganCode as organCode,\n" +
				"\t\ttso.OrganName as organName,\n" +
				"\t\tthc.CommName as commName,\n" +
				"\t\tisnull(r2.RoomCnt,0) as houseQuantity,\n" +
				"\t\tthb.ActualProperty as actualProperty,\n" +
				"\t\tisnull(thr.RoomCnt,0) as receiptsFloor,\n" +
				"\t\tthc.CommID as projectNumber,\n" +
				"\t\tthb.NCSystemNumber,\n" +
				"\t\tROUND(CASE WHEN r2.RoomCnt=0 THEN 0.00\n" +
				"\t\tELSE r1.RoomCnt*1.00/r2.RoomCnt*100 END,2) as dwellRatio,\n" +
				"\t\tisnull(r3.stepLadderHouse,0) stepLadderHouse,\n" +
				"\t\tisnull(r3.elevatorHouse,0) elevatorHouse,\n" +
				"\t\tisnull(r3.autocephalyVilla,0) autocephalyVilla,\n" +
				"\t\tisnull(r3.doubleSpellVilla,0) doubleSpellVilla,\n" +
				"\t\tisnull(r3.fourSpellVilla,0) fourSpellVilla,\n" +
				"\t\tisnull(r3.conjoinedVilla,0) conjoinedVilla,\n" +
				"\t\tisnull(r3.shop,0) shop,\n" +
				"\t\tisnull(r3.parkingSpaces,0) parkingSpaces,\n" +
				"\t\tisnull(r3.other,0) other,\n" +
				"\t\tisnull(r3.park,0) park,\n" +
				"\t\tisnull(r3.writingFloor,0) writingFloor,\n" +
				"\t\tisnull(r3.scenicArea,0) scenicArea,\n" +
				"\t\tisnull(r3.school,0) school,\n" +
				"\t\tisnull(r3.apartment,0) apartment,\n" +
				"\t\tisnull(r3.market,0) market,\n" +
				"\t\tisnull(r3.officebuilding,0) officebuilding,\n" +
				"\t\tisnull(r3.wineshop,0) wineshop,\n" +
				"\t\tisnull(r3.industrialPlants,0) industrialPlants,\n" +
				"\t\tisnull(r3.industryHouse,0) industryHouse,\n" +
				"\t\tisnull(r3.warehouse,0) warehouse,\n" +
				"\t\tisnull(r3.hospital,0) hospital,\n" +
				"\t\tisnull(r3.stadium,0) stadium,\n" +
				"\t\tisnull(r3.library,0) library,\n" +
				"\t\tisnull(r3.exhibitionCenter,0) exhibitionCenter,\n" +
				"\t\tisnull(r3.carpark,0) carpark,\n" +
				"\t\tisnull(r3.racecourse,0) racecourse,\n" +
				"\t\tisnull(r3.golfCourse,0) golfCourse,\n" +
				"\t\tisnull(r3.carGAS,0) carGAS,\n" +
				"\t\tisnull(r3.airport,0) airport,\n" +
				"\t\tisnull(r3.busStation,0) busStation,\n" +
				"\t\tisnull(r3.pier,0) pier,\n" +
				"\t\tisnull(r3.municipalRoads,0) municipalRoads,\n" +

				"\t\tthc.Province province,\n" +
				"\t\tthc.City city,\n" +
				"\t\tthc.Borough  borough,\n" +

				"\t\tisnull(r3.bridge,0) bridge,\n" +
				"\t\tisnull(r3.tunnel,0) tunnel\n" +
				"\n" +
				"\t\tFROM  HM_wygl_new_1853.dbo.Tb_Sys_Organ AS tso\n" +
				"\t\tLEFT JOIN HM_wygl_new_1853.dbo.Tb_HSPR_Community AS thc ON tso.OrganCode= thc.OrganCode\n" +
				"\t\tAND tso.rentid=thc.rentid AND tso.rentid=1853 and thc.rentid=1853"+
				"\t\tAND tso.IsDelete NOT IN (1)\n" +
				"\t\tLEFT JOIN HM_wygl_new_1853.dbo.Tb_HSPR_BasicInfo AS thb ON thb.CommID= thc.CommID\n" +
				"\t\tAND thb.IsDelete NOT IN (1)\n" +
				"\t\tAND thb.rentid=thc.rentid AND thb.rentid=1853\n" +
				"\t\tLEFT JOIN HM_wygl_new_1853.dbo.Tb_HSPR_PlanInfo AS thp ON thp.CommID= thc.CommID\n" +
				"\t\tAND thp.IsDelete NOT IN (1)\n" +
				"\t\tAND thp.rentid=thc.rentid AND thp.rentid=1853\n" +
				"\t\tLEFT JOIN (\n" +
				"\t\tSELECT CommID,COUNT(RoomID) AS RoomCnt\n" +
				"\t\tFROM HM_wygl_new_1853.dbo.Tb_HSPR_Room\n" +
				"\n" +
				"\t\tWHERE rentid=1853 AND ActualSubDate <=GETDATE() AND IsDelete <>1\n" +
				"\t\tGROUP BY CommID\n" +
				"\t\t)\tthr ON thc.CommID=thr.CommID\n" +
				"\t\tLEFT JOIN (\n" +
				"\t\tSELECT CommID,COUNT(RoomID) AS RoomCnt\n" +
				"\t\tFROM HM_wygl_new_1853.dbo.Tb_HSPR_Room\n" +
				"\t\tWHERE rentid=1853 AND LiveStates='0001'\n" +
				"\t\tGROUP BY CommID\n" +
				"\t\t) r1 ON thc.CommID=r1.CommID\n" +
				"\t\tLEFT JOIN (\n" +
				"\t\tSELECT CommID,COUNT(RoomID) AS RoomCnt\n" +
				"\t\tFROM HM_wygl_new_1853.dbo.Tb_HSPR_Room\n" +
				"\t\tWHERE rentid=1853\n" +
				"\t\tGROUP BY CommID\n" +
				"\t\t) r2 ON thc.CommID=r2.CommID\n" +
				"\n" +
				"\t\tLEFT JOIN (\n" +
				"\t\tSELECT 步梯洋房 AS stepLadderHouse,电梯洋房 AS elevatorHouse,独立别墅 AS autocephalyVilla,\n" +
				"\t\t双拼别墅 AS doubleSpellVilla,四拼别墅 AS fourSpellVilla,联体别墅 AS conjoinedVilla,\n" +
				"\t\t商铺 AS shop,车位 AS parkingSpaces,其它 AS other,公园 AS park,写字楼 AS writingFloor,景区 AS scenicArea,\n" +
				"\t\t学校 AS school,公寓 AS apartment,商场 AS market,办公楼 AS officebuilding,酒店 AS wineshop,工业厂房 AS industrialPlants,\n" +
				"\t\t产业用房 AS industryHouse,仓库 AS warehouse,医院 AS hospital,体育馆 AS stadium,\n" +
				"\t\t图书馆 AS library,会展中心 AS exhibitionCenter,停车场 AS carpark,赛马场 AS racecourse,高尔夫球场 AS golfCourse,\n" +
				"\t\t汽车加油站 AS carGAS,飞机场 AS airport,车站 AS busStation,码头 AS pier,市政道路 AS municipalRoads,\n" +
				"\t\t桥梁 AS bridge,隧道 AS tunnel  ,CommID\n" +
				"\t\tFROM (\n" +
				"\t\tSELECT bt.DictionaryName,ISNULL(c.RoomCount,0) RoomCount ,c.CommID\n" +
				"\t\tFROM HM_wygl_new_1853.dbo.Tb_Dictionary_BuildType bt WITH(NOLOCK)\n" +
				"\t\tLEFT JOIN (\n" +
				"\t\tSELECT b.BuildType,COUNT(*) RoomCount ,f.CommID\n" +
				"\t\tFROM HM_wygl_new_1853.dbo.view_HSPR_Customer_SaleFilter f\n" +
				"\t\tINNER JOIN HM_wygl_new_1853.dbo.Tb_HSPR_Building b WITH(NOLOCK)\n" +
				"\t\tON b.rentid=f.rentid AND b.CommID = f.CommID AND convert(varchar,b.BuildSNum) = f.BuildSNum\n" +
				"\t\tINNER JOIN HM_wygl_new_1853.dbo.Tb_HSPR_CustType ct WITH(NOLOCK)\n" +
				"\t\tON ct.rentid=f.rentid AND ct.CustTypeID = f.CustTypeID\n" +
				"\t\twhere\n" +
				"\t\tf.rentid=1853 and b.rentid=1853 and ct.rentid=1853\n" +
				"\t\tAND f.IsDelete = 0 AND f.IsSale = 1 AND f.LiveType1 = 1\n" +
				"\t\tAND ISNULL(b.IsDelete,0) = 0 and\n" +
				"\t\t(ct.CustTypeName = '二手业主' OR ct.CustTypeName = '三手业主'\n" +
				"\t\tOR ct.CustTypeName = '四手业主' OR ct.CustTypeName = '五手业主'\n" +
				"\t\tOR ct.CustTypeName = '六手业主') ");

		if(divertStatistical.getCommId()!=0){
			sql.append(" AND f.CommID=").append(divertStatistical.getCommId());
		}
		if(StringUtils.hasText(divertStatistical.getActualPropertyStart())){
			sql.append(" AND f.ChargeTime>='").append(divertStatistical.getActualPropertyStart()).append("'");
		}

		if(StringUtils.hasText(divertStatistical.getActualPropertyEnd())){
			sql.append(" AND f.ChargeTime<'").append(divertStatistical.getActualPropertyEnd()).append("'");
		}

		sql.append("\tGROUP BY b.BuildType ,f.CommID\n" +
				"\t\t) c ON  bt.DictionaryCode= c.BuildType\n" +
				"\t\t) t\n" +
				"\t\t\n" +
				"\t\t\n" +
				"\t\tPIVOT (\n" +
				"\t\tMAX(RoomCount)\n" +
				"\t\tFOR DictionaryName IN (\n" +
				"\t\t步梯洋房,电梯洋房,独立别墅,双拼别墅,四拼别墅,联体别墅,商铺,车位,其它,公园,\n" +
				"\t\t写字楼,景区,学校,公寓,商场,办公楼,酒店,工业厂房,产业用房,仓库,医院,体育馆,\n" +
				"\t\t图书馆,会展中心,停车场,赛马场,高尔夫球场,汽车加油站,飞机场,车站,码头,市政道路,\n" +
				"\t\t桥梁,隧道)\n" +
				"\t\t) piv\n" +
				"\n" +
                "\t\t) r3 ON thc.CommID=r3.CommID\n");

        if(divertStatistical.getCommId()!=0){
            sql.append(" where  thc.CommID=").append(divertStatistical.getCommId());
        }

        return sql;
	}


	@Override
	public List<String> findRoomType(){
		return divertStatisticalMapper.findRoomType();
	}

	@SuppressWarnings("null")
	@Override
	public PageData findDivertStaticalLike(DivertStatistical divertStatistical) {

		PageRowBounds pageRowBounds = new PageRowBounds(divertStatistical.getPageIndex(), divertStatistical.getPageSize());
		Long start1 = System.currentTimeMillis();
		List<DivertStatistical> list = divertStatisticalMapper.findDivertStaticalLike(divertStatistical, pageRowBounds);
		Long start2 = System.currentTimeMillis();
		logger.info("{}执行时间{}","divertStatisticalMapper.findDivertStaticalLike", start2 - start1);

		if(list != null) {
			//获取部分的数据
			for (DivertStatistical dto : list) {
				// 查询条件
				dto.setActualPropertyStart(divertStatistical.getActualPropertyStart());
				String actualPropertyEnd = divertStatistical.getActualPropertyEnd();
				if (StringUtils.hasText(actualPropertyEnd)) {
					// todo 使用工具类
					Date date = DateUtil.parseDate(actualPropertyEnd);
					Calendar cal = Calendar.getInstance();
					cal.setTime(date);
					cal.add(Calendar.DATE, 1);
					Date time = cal.getTime();
					String format = new SimpleDateFormat("yyyy-MM-dd").format(time);
					dto.setActualPropertyEnd(format);
				}

				//通过项目id查询对应--查询项目累计已收楼数
				dto.setReceiptsFloor(divertStatisticalMapper.findReceiptsFloor(dto));
				String result=null;

				// 创建一个数值格式化对象
				NumberFormat numberFormat = NumberFormat.getInstance();
				// 设置精确到小数点后2位
				numberFormat.setMaximumFractionDigits(2);
				//查询常住的房间有多少套
				int a=divertStatisticalMapper.findReceiptsFloorNumber(dto);
				//查询当前项目存在几套房
				int b=divertStatisticalMapper.findCommReceiptsFloorNumber(dto);
				dto.setHouseQuantity(b);
				if(a!=0 && b!=0) {
					result = numberFormat.format(((float)a /(float)b )*100);
				}else {
					result="0";
				}

				dto.setDwellRatio(result+"%");
				if(dto.getCommId()!=0) {
					DivertStatistical number=divertStatisticalMapper.findRootNumber(dto);
					dto.setStepLadderHouse(number.getStepLadderHouse());
					dto.setElevatorHouse(number.getElevatorHouse());
					dto.setAutocephalyVilla(number.getAutocephalyVilla());
					dto.setDoubleSpellVilla(number.getDoubleSpellVilla());
					dto.setFourSpellVilla(number.getFourSpellVilla());
					dto.setConjoinedVilla(number.getConjoinedVilla());
					dto.setShop(number.getShop());
					dto.setParkingSpaces(number.getParkingSpaces());
					dto.setOther(number.getOther());
					dto.setPark(number.getPark());
					dto.setWritingFloor(number.getWritingFloor());
					dto.setScenicArea(number.getScenicArea());
					dto.setSchool(number.getSchool());
					dto.setApartment(number.getApartment());
					dto.setMarket(number.getMarket());
					dto.setOfficebuilding(number.getOfficebuilding());
					dto.setWineshop(number.getWineshop());
					dto.setIndustrialPlants(number.getIndustrialPlants());
					dto.setIndustryHouse(number.getIndustryHouse());
					dto.setWarehouse(number.getWarehouse());
					dto.setHospital(number.getHospital());
					dto.setStadium(number.getStadium());
					dto.setLibrary(number.getLibrary());
					dto.setExhibitionCenter(number.getExhibitionCenter());
					dto.setCarpark(number.getCarpark());
					dto.setRacecourse(number.getRacecourse());
					dto.setGolfCourse(number.getGolfCourse());
					dto.setCarGAS(number.getCarGAS());
					dto.setAirport(number.getAirport());
					dto.setBusStation(number.getBusStation());
					dto.setPier(number.getPier());
					dto.setMunicipalRoads(number.getMunicipalRoads());
					dto.setTunnel(number.getTunnel());
					dto.setBridge(number.getBridge());
				}
			}
		}
		Long start3 = System.currentTimeMillis();
		logger.info("{}执行时间{}","for 循环时间", start3 - start2);
		PageData PageData = new PageData(list, pageRowBounds);
		return PageData;
	}
	
	
}
