package com.bus.service.impl;

import java.io.UnsupportedEncodingException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import com.base.model.BaseOrgType;
import com.base.model.BaseWaterProperties;
import com.base.service.IBaseOrgTypeService;
import com.base.service.IBaseWaterPropertiesService;
import com.bus.model.*;
import com.bus.model.temp.*;
import com.bus.service.*;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.RowMapper;

import com.bus.dao.IBusOrgDao;
import com.core.dao.IGenericDao;
import com.core.service.impl.GenericServiceImpl;
import com.core.util.Page;

/**
 * 导入数据文件业务层实现类
 *
 * @author lha
 */
public class BusOrgServiceImpl extends GenericServiceImpl<BusOrg, Long> implements IBusOrgService {

    @Autowired
    private IBusOrgDao busOrgDao;
    @Autowired
    private IBaseOrgTypeService baseOrgTypeService;
    @Autowired
    private IBusCustomerMeterService busCustomerMeterService;
    @Autowired
    private IBusAnnualPlanForWaterService busAnnualPlanForWaterService;
    @Autowired
    private IBusQuarterlyAssessmentService busQuarterlyAssessmentService;
    @Autowired
    private IBusMonthAssessmentService busMonthAssessmentService;
    @Autowired
    private IBaseWaterPropertiesService baseWaterPropertiesService;


    @Autowired
    private org.springframework.jdbc.core.JdbcTemplate jdbcTemplate;

    @Override
    protected IGenericDao<BusOrg, Long> getGenericDao() {
        return this.busOrgDao;
    }

    @Override
    public String getMatYearFromBusOrg() {
        // TODO Auto-generated method stub
        return this.busOrgDao.getMatYearFromBusOrg();
    }


/*	@Override
	public List<BusOrgTemp> getCountInitDate(String year) {
		// TODO Auto-generated method stub
		return this.busOrgDao.getCountInitDate(year);
	}*/

    @Override
    public Page getOrgListPage(int pageNo, int pageSize, String orgNumber,
                               String orgName, String year, String busOrgCustomerNumber,
                               String address, String busOrgMeterNumber, String isPlanUser,
                               String userType, String orgTypeId) {
        String hql = "select b from BusOrg b where b.importStatus='normal'";
        if (null != orgNumber && !orgNumber.equals("")) {
            hql += " and b.orgNumber = '" + orgNumber + "'";
        }
        if (null != orgName && !orgName.equals("")) {
            hql += " and b.orgName like '%" + orgName + "%'";
        }
        if (null != year && !year.equals("")) {
            hql += " and b.year like '%" + year + "%'";
        }
        if (null != busOrgCustomerNumber && !busOrgCustomerNumber.equals("")) {
            hql += " and b.orgId in(select bcm.org.orgId from BusCustomerMeter bcm where bcm.customerNumber='" + busOrgCustomerNumber + "')";
        }
        if (null != address && !address.equals("")) {
            hql += " and b.address like '%" + address + "%'";
        }
        if (null != busOrgMeterNumber && !busOrgMeterNumber.equals("")) {
            hql += " and b.orgId in(select bc.org.orgId from BusCustomerMeter bc where bc.meterNumber='" + busOrgMeterNumber + "')";
        }
        if (null != userType && !userType.equals("")) {
            hql += " and b.newMeter = '1'";
        }
        if (null != isPlanUser && !isPlanUser.equals("")) {
            hql += " and b.isPlanUser ='" + isPlanUser + "'";
        }
        if (null != orgTypeId && !orgTypeId.equals("")) {
            hql += " and b.orgTypeId  = " + orgTypeId + "";
        }
        return busOrgDao.getPageByHql(hql, pageNo, pageSize);
    }


    @Override
    public void updateYearneedInitDate(String initYear,
                                       Date initStartTime, Date initEndTime, Date bMonthStartTime,
                                       Date bMonthEndTime, Date eMonthStartTime, Date eMonthEndTime) {
        // TODO Auto-generated method stub
        this.busOrgDao.updateYearneedInitDate(initYear, initStartTime, initEndTime, bMonthStartTime, bMonthEndTime, eMonthStartTime, eMonthEndTime);
    }

    @Override
    public void updateCountYearInitDate(Long userId, Date updateTime, String userName, String year, Date initStartTime,
                                        Date initEndTime, Date bMonthStartTime, Date bMonthEndTime,
                                        Date eMonthStartTime, Date eMonthEndTime) {
        // TODO Auto-generated method stub
        this.busOrgDao.updateCountYearInitDate(userId, updateTime, userName, year, initStartTime, initEndTime, bMonthStartTime, bMonthEndTime, eMonthStartTime, eMonthEndTime);
    }

    @Override
    public void importExceInitData(String year, List<String[]> dataList) {

        //查询到居民用水与非居民用水信息
        BaseOrgType jmOrgType = baseOrgTypeService.findUniqueBy("orgName", "居民用水");
        BaseOrgType fjmOrgType = baseOrgTypeService.findUniqueBy("orgName", "非居民用水");

        //使用导入的功能都认为是计划户
        String _isPlanUser = "yes";
        //邮政编码
        String _code = "530000";
        //说明
        String _remark = year + "导入年计划";
        //导入状态
        String _importStatus = "normal";
        //日期
        Date now = new Date();
        //用户类型全部为自来水户
        String _baseUserTypeId = "1";


        //插入用户信息表
        for (int i = 1; i < dataList.size(); i++) {
            String[] item = dataList.get(i);
            String _no = item[0];
            String _orgName = item[1];
            String _address = item[2];
            String _properties = item[3];

            //判断单位是否已经存在


            String _waterPriceStr = item[4];

            if (StringUtils.isBlank(_waterPriceStr)) {
                _waterPriceStr = "0";
            }
            _waterPriceStr = _waterPriceStr.trim().replaceAll(",", "");

            Float _waterPrice = Float.parseFloat(_waterPriceStr);
            String _customerMeter = item[5];

            //实际用水
            String _actualStr = item[6];
            if (StringUtils.isBlank(_actualStr)) {
                _actualStr = "0";
            }
            _actualStr = _actualStr.trim().replaceAll(",", "");

            Float _actual = Float.parseFloat(_actualStr);
            //计划用水

            String _planStr = item[7];
            if (StringUtils.isBlank(_planStr)) {
                _planStr = "0";
            }
            _planStr = _planStr.trim().replaceAll(",", "");

            Float _plan = Float.parseFloat(_planStr);

            BusOrg busOrg = new BusOrg();
            busOrg.setOrgName(_orgName);
            busOrg.setOrgNumber(Integer.parseInt(_no));
            busOrg.setYear(year);
            busOrg.setBaseUserType(_baseUserTypeId);
            busOrg.setAddress(_address);
            busOrg.setIsPlanUser(_isPlanUser);
            busOrg.setCode(_code);
            busOrg.setRemark(_remark);
            if (_properties != null && _properties.indexOf("居民") >= 0) {
                busOrg.setOrgType(jmOrgType);
            } else {
                busOrg.setOrgType(fjmOrgType);
            }
            busOrg.setImportId(0L);
            busOrg.setImportStatus(_importStatus);
            busOrg.setPostalAddress(_address);
            //保存到数据
            busOrg = busOrgDao.save(busOrg);


            if (_properties != null && !_properties.equalsIgnoreCase("")) {
                //当前用水性质是否存在
                BaseWaterProperties baseWaterProperties = null;
                try {
                    baseWaterProperties = baseWaterPropertiesService.findUniqueBy("propertiesName", _properties);
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
                if (baseWaterProperties == null) {
                    baseWaterProperties = new BaseWaterProperties(_properties, _waterPrice);
                    baseWaterPropertiesService.save(baseWaterProperties);
                }

            }


            //插入水表信息
            if (StringUtils.isNotBlank(_customerMeter)) {
                _customerMeter = _customerMeter.replaceAll("，", ",");
                //拿到水表号
                String[] _customerMeterList = _customerMeter.split(",");
                //循环添加水表信息
                for (int j = 0; j < _customerMeterList.length; j++) {
                    BusCustomerMeter busCustomerMeter = new BusCustomerMeter();
                    busCustomerMeter.setYear(year);
                    busCustomerMeter.setMeterNumber(Integer.parseInt(_customerMeterList[j]));
                    busCustomerMeter.setRemark(_remark);
                    busCustomerMeter.setCustomerNumber(Integer.parseInt(_no));
                    busCustomerMeter.setAddress(_address);
                    busCustomerMeter.setOrg(busOrg);
                    busCustomerMeterService.save(busCustomerMeter);
                }
            }
            //算出季度用水
            Float quarterlyPlan = _plan / 4;
            //算出月份用水
            Float monthPlan = _plan / 12;
            //生成年计划
            BusAnnualPlanForWater busAnnualPlanForWater = new BusAnnualPlanForWater();
            busAnnualPlanForWater.setFillTime(now);
            busAnnualPlanForWater.setOrg(busOrg);
            busAnnualPlanForWater.setIsCancel("yes");
            //上年用水量
            busAnnualPlanForWater.setPriYearUseWater(_actual);
            //计划用水量
            busAnnualPlanForWater.setPlanOfWater(_plan);
            busAnnualPlanForWater.setReplyOfWater(_plan);
            //计划季度用水量
            busAnnualPlanForWater.setFirstQuarterPlan(quarterlyPlan);
            busAnnualPlanForWater.setSecondQuarterPlan(quarterlyPlan);
            busAnnualPlanForWater.setThirdQuarterPlan(quarterlyPlan);
            busAnnualPlanForWater.setFourthQuarterPlan(quarterlyPlan);

            busAnnualPlanForWater.setFirstQuarterReply(quarterlyPlan);
            busAnnualPlanForWater.setSecondQuarterReply(quarterlyPlan);
            busAnnualPlanForWater.setThirdQuarterReply(quarterlyPlan);
            busAnnualPlanForWater.setFourthQuarterReply(quarterlyPlan);

            busAnnualPlanForWater.setJanPlan(monthPlan);
            busAnnualPlanForWater.setFebPlan(monthPlan);
            busAnnualPlanForWater.setMarPlan(monthPlan);
            busAnnualPlanForWater.setAprPlan(monthPlan);
            busAnnualPlanForWater.setMayPlan(monthPlan);
            busAnnualPlanForWater.setJunPlan(monthPlan);
            busAnnualPlanForWater.setJulPlan(monthPlan);
            busAnnualPlanForWater.setAugPlan(monthPlan);
            busAnnualPlanForWater.setSeptPlan(monthPlan);
            busAnnualPlanForWater.setOctPlan(monthPlan);
            busAnnualPlanForWater.setNovPlan(monthPlan);
            busAnnualPlanForWater.setDecPlan(monthPlan);

            busAnnualPlanForWater.setJanReply(monthPlan);
            busAnnualPlanForWater.setFebReply(monthPlan);
            busAnnualPlanForWater.setMarReply(monthPlan);
            busAnnualPlanForWater.setAprReply(monthPlan);
            busAnnualPlanForWater.setMayReply(monthPlan);
            busAnnualPlanForWater.setJunReply(monthPlan);
            busAnnualPlanForWater.setJulReply(monthPlan);
            busAnnualPlanForWater.setAugReply(monthPlan);
            busAnnualPlanForWater.setSeptReply(monthPlan);
            busAnnualPlanForWater.setOctReply(monthPlan);
            busAnnualPlanForWater.setNovReply(monthPlan);
            busAnnualPlanForWater.setDecReply(monthPlan);
            busAnnualPlanForWaterService.save(busAnnualPlanForWater);
            //生成季考核
            for (int j = 1; j <= 4; j++) {
                BusQuarterlyAssessment busQuarterlyAssessment = new BusQuarterlyAssessment();
                busQuarterlyAssessment.setAssessmentQuarter(j);
                busQuarterlyAssessment.setWaterPlanningIndex(quarterlyPlan);
                busQuarterlyAssessment.setReplyOfWater(quarterlyPlan);
                busQuarterlyAssessment.setOrg(busOrg);
                busQuarterlyAssessment.setIsWriteOff("no");
                busQuarterlyAssessment.setWaterPrice(_waterPrice);
                busQuarterlyAssessmentService.save(busQuarterlyAssessment);

            }
            //生成月份考核
            for (int j = 1; j <= 12; j++) {
                BusMonthAssessment busMonthAssessment = new BusMonthAssessment();
                busMonthAssessment.setAssessmentMonth(j);
                busMonthAssessment.setWaterPlanningIndex(monthPlan);
                busMonthAssessment.setReplyOfWater(monthPlan);
                busMonthAssessment.setOrg(busOrg);
                busMonthAssessment.setIsWriteOff("no");
                busMonthAssessmentService.save(busMonthAssessment);
            }
        }
    }

    @Override
    public void updateInitbusQuarterlyAssessment(int assessmentQuarter,
                                                 String year, Date initStartTime, Date initEndTime,
                                                 Date bMonthStartTime, Date bMonthEndTime, Date eMonthStartTime,
                                                 Date eMonthEndTime) {
        this.busOrgDao.updateInitbusQuarterlyAssessment(assessmentQuarter, year, initStartTime, initEndTime, bMonthStartTime, bMonthEndTime, eMonthStartTime, eMonthEndTime);

    }

    @Override
    public void updateInitbusMonthAssessment(int assessmentMonth,
                                             String year, Date initStartTime, Date initEndTime,
                                             Date bMonthStartTime, Date bMonthEndTime, Date eMonthStartTime,
                                             Date eMonthEndTime) {
        this.busOrgDao.updateInitbusMonthAssessment(assessmentMonth, year, initStartTime, initEndTime, bMonthStartTime, bMonthEndTime, eMonthStartTime, eMonthEndTime);

    }

    @Override
    public void updateInitbusHalfYearAssessment(int assessmentQuarter,
                                                String year, Date initStartTime, Date initEndTime,
                                                Date bMonthStartTime, Date bMonthEndTime, Date eMonthStartTime,
                                                Date eMonthEndTime, Float GR) {
        this.busOrgDao.updateInitbusHalfYearAssessment(assessmentQuarter, year, initStartTime, initEndTime, bMonthStartTime, bMonthEndTime, eMonthStartTime, eMonthEndTime, GR);

    }

    @Override
    public List<BusOrgTemp> getPlanYearCountData(String orgNumber,
                                                 String orgName, String year, String busOrgCustomerNumber,
                                                 String address, String userType, String orgTypeId) throws UnsupportedEncodingException {
        // TODO Auto-generated method stub
        return this.busOrgDao.getPlanYearCountData(orgNumber, orgName, year, busOrgCustomerNumber, address, userType, orgTypeId);
    }

    @Override
    public String getMaxUserPlanYear() {
        // TODO Auto-generated method stub
        return this.busOrgDao.getMaxUserPlanYear();
    }

    @Override
    public void initOneData(Long orgId, int nextYear, String type) {
        this.busOrgDao.initOneData(orgId, nextYear, type);

    }

    @Override
    public List<ReplyPrintTemp> unitYearReplyPrin(String orgNumber, String orgName,
                                                  String year, String busOrgCustomerNumber, String address,
                                                  String busOrgMeterNumber, String priYearUseWater, String userType, String orgTypeId) {
        String sql = " SELECT b.`org_name`,w.`first_quarter_reply` AS numOne,w.`second_quarter_reply` AS numTwo,w.`third_quarter_reply` AS numThird,w.`fourth_quarter_reply` AS numFourth,GROUP_CONCAT(bm.`meter_Number`) AS meterNum,w.reply_of_water "
                + " FROM `bus_org` b "
                + " JOIN `bus_annual_plan_for_water` w ON b.`org_id` = w.`org_id` JOIN `bus_customer_meter` bm ON b.`org_id`=bm.`org_id` "
                + " WHERE b.`is_plan_user`='yes' AND w.`is_cancel`='yes' ";
        if (!orgNumber.equals("")) {
            sql += " and b.org_number=" + orgNumber;
        }
        if (!orgName.equals("")) {
            try {
                orgName = new String(orgName.getBytes("iso8859-1"), "UTF-8");
            } catch (UnsupportedEncodingException e) {

                e.printStackTrace();
            }
            sql += " and b.org_name like '%" + orgName + "%'";
        }
        if (orgTypeId != null && !orgTypeId.equals("")) {
            try {
                orgTypeId = new String(orgTypeId.getBytes("iso8859-1"), "UTF-8");
            } catch (UnsupportedEncodingException e) {

                e.printStackTrace();
            }
            sql += " and b.org_type_id = " + orgTypeId + "";
        }
        sql += " and b.year=" + year;
        if (!address.equals("")) {
            try {
                address = new String(address.getBytes("iso8859-1"), "UTF-8");
            } catch (UnsupportedEncodingException e) {

                e.printStackTrace();
            }
            sql += " and b.address like '%" + address + "%'";
        }
        if (!priYearUseWater.equals("")) {
            Float f = Float.parseFloat(priYearUseWater);
            sql += " and w.pri_year_usewater >=" + f;
        }
        if (!userType.equals("")) {
            sql += " and b.user_type=" + userType;
        }
        sql += " GROUP BY b.`org_id`";
        if (!busOrgCustomerNumber.equals("")) {
            sql += " having GROUP_CONCAT(bm.`meter_Number`) like '%" + busOrgCustomerNumber + "%'";
        }
        if (!busOrgMeterNumber.equals("")) {
            sql += " and GROUP_CONCAT(bm.`customer_Number`) like '%" + busOrgMeterNumber + "%'";
        }
        RowMapper<ReplyPrintTemp> rowMapper = new RowMapper<ReplyPrintTemp>() {

            @Override
            public ReplyPrintTemp mapRow(ResultSet rs, int arg1) throws SQLException {
                ReplyPrintTemp busOrgTem = new ReplyPrintTemp(rs.getString("org_name"), rs.getFloat("numOne"), rs.getFloat("numTwo"),
                        rs.getFloat("numThird"), rs.getFloat("numFourth"), rs.getString("meterNum"), rs.getFloat("reply_of_water"));
                return busOrgTem;
            }

        };
        List<ReplyPrintTemp> list = jdbcTemplate.query(sql, rowMapper);
        return list;
    }


    public List<ReplyMonPrintTemp> unitYearReplyPrin2(String orgNumber, String orgName,
                                                   String year, String busOrgCustomerNumber, String address,
                                                   String busOrgMeterNumber, String priYearUseWater, String userType, String orgTypeId) {
        String sql = " SELECT b.`org_name`,w.jan_reply,w.feb_reply,w.mar_reply,w.apr_reply,w.may_reply,w.jun_reply,w.jul_reply,w.aug_reply,w.sept_reply,w.oct_reply,w.nov_reply,w.dec_reply,GROUP_CONCAT(bm.`meter_Number`) AS meterNum,w.reply_of_water "
                + " FROM `bus_org` b "
                + " JOIN `bus_annual_plan_for_water` w ON b.`org_id` = w.`org_id` JOIN `bus_customer_meter` bm ON b.`org_id`=bm.`org_id` "
                + " WHERE b.`is_plan_user`='yes' AND w.`is_cancel`='yes' ";
        if (!orgNumber.equals("")) {
            sql += " and b.org_number=" + orgNumber;
        }
        if (!orgName.equals("")) {
            try {
                orgName = new String(orgName.getBytes("iso8859-1"), "UTF-8");
            } catch (UnsupportedEncodingException e) {

                e.printStackTrace();
            }
            sql += " and b.org_name like '%" + orgName + "%'";
        }
        if (orgTypeId != null && !orgTypeId.equals("")) {
            try {
                orgTypeId = new String(orgTypeId.getBytes("iso8859-1"), "UTF-8");
            } catch (UnsupportedEncodingException e) {

                e.printStackTrace();
            }
            sql += " and b.org_type_id = " + orgTypeId + "";
        }
        sql += " and b.year=" + year;
        if (!address.equals("")) {
            try {
                address = new String(address.getBytes("iso8859-1"), "UTF-8");
            } catch (UnsupportedEncodingException e) {

                e.printStackTrace();
            }
            sql += " and b.address like '%" + address + "%'";
        }
        if (!priYearUseWater.equals("")) {
            Float f = Float.parseFloat(priYearUseWater);
            sql += " and w.pri_year_usewater >=" + f;
        }
        if (!userType.equals("")) {
            sql += " and b.user_type=" + userType;
        }
        sql += " GROUP BY b.`org_id`";
        if (!busOrgCustomerNumber.equals("")) {
            sql += " having GROUP_CONCAT(bm.`meter_Number`) like '%" + busOrgCustomerNumber + "%'";
        }
        if (!busOrgMeterNumber.equals("")) {
            sql += " and GROUP_CONCAT(bm.`customer_Number`) like '%" + busOrgMeterNumber + "%'";
        }
        RowMapper<ReplyMonPrintTemp> rowMapper = new RowMapper<ReplyMonPrintTemp>() {

            @Override
            public ReplyMonPrintTemp mapRow(ResultSet rs, int arg1) throws SQLException {
                ReplyMonPrintTemp busOrgTem = new ReplyMonPrintTemp(rs.getString("org_name"),
                        rs.getFloat("jan_reply"),
                        rs.getFloat("feb_reply"),
                        rs.getFloat("mar_reply"),
                        rs.getFloat("apr_reply"),
                        rs.getFloat("may_reply"),
                        rs.getFloat("jun_reply"),
                        rs.getFloat("jul_reply"),
                        rs.getFloat("aug_reply"),
                        rs.getFloat("sept_reply"),
                        rs.getFloat("oct_reply"),
                        rs.getFloat("nov_reply"),
                        rs.getFloat("dec_reply"),
                        rs.getString("meterNum"), rs.getFloat("reply_of_water"));
                return busOrgTem;
            }

        };
        List<ReplyMonPrintTemp> list = jdbcTemplate.query(sql, rowMapper);
        return list;
    }


    @Override
    public List<OrgTemp> emailPrintView(String orgNumber, String orgName,
                                        String year, String busOrgCustomerNumber, String address,
                                        String busOrgMeterNumber, String priYearUseWater, String userType, String orgTypeId) throws UnsupportedEncodingException {
        String sql = " SELECT b.`code`,b.`org_name`,b.postal_address FROM `bus_org` b " +
                " LEFT JOIN `bus_annual_plan_for_water` w ON b.`org_id` = w.`org_id` WHERE b.`year`='" + year + "' AND b.`is_plan_user`='yes' AND w.`is_cancel`='yes'";
        if (!orgNumber.equals("")) {
            sql += " and b.org_number=" + orgNumber;
        }
        if (!orgName.equals("")) {
            orgName = new String(orgName.getBytes("iso8859-1"), "UTF-8");
            sql += " and b.org_name like '%" + orgName + "%'";
        }
        if (!orgTypeId.equals("")) {
            orgTypeId = new String(orgTypeId.getBytes("iso8859-1"), "UTF-8");
            sql += " and b.org_type_id = " + orgTypeId + "";
        }
        if (!address.equals("")) {
            address = new String(address.getBytes("iso8859-1"), "UTF-8");
            sql += " and b.address like '%" + address + "%'";
        }
        if (!priYearUseWater.equals("")) {
            Float f = Float.parseFloat(priYearUseWater);
            sql += " and w.pri_year_usewater>=" + f;
        }
        if (!userType.equals("")) {
            sql += " and b.user_type=" + userType;
        }
        if (!busOrgCustomerNumber.equals("")) {
            sql += " and b.org_id in (select bm.org_id from `bus_customer_meter` bm where bm.year='" + year + "' and bm.customer_Number=" + busOrgCustomerNumber + ")";
        }
        if (!busOrgMeterNumber.equals("")) {
            sql += " and b.org_id in (select bcm.org_id from `bus_customer_meter` bcm where bcm.year='" + year + "' and bcm.meter_Number=" + busOrgMeterNumber + ")";
        }
        RowMapper<OrgTemp> rowMapper = new RowMapper<OrgTemp>() {

            @Override
            public OrgTemp mapRow(ResultSet rs, int arg1) throws SQLException {
                OrgTemp org = new OrgTemp(rs.getString("code"), rs.getString("org_name"), rs.getString("postal_address"));
                return org;
            }

        };
        List<OrgTemp> list = jdbcTemplate.query(sql, rowMapper);
        return list;
    }

    @Override
    public int checkPendingIsNull() {
        return jdbcTemplate.queryForInt("SELECT COUNT(*) FROM `bus_org` WHERE `import_status`='pending'");
    }

    @Override
    public List<Object[]> listCountWaterData(String year) {
        String sql = " SELECT bwp.`properties_name`,                                       "
                + " IFNULL((                                                            "
                + " SELECT SUM(w.`reply_of_water`) FROM `bus_annual_plan_for_water` w   "
                + " JOIN `bus_meter_read_info` info ON w.`org_id`=info.`org_id` WHERE   "
                + " bwp.`properties_id`=info.properties_id AND info.year='" + year + "'),0)  "
                + " AS reply,                                                            "
                + " IFNULL((                                                             "
                + " SELECT SUM(i.`water`) FROM `bus_meter_read_info` i                   "
                + " JOIN `bus_annual_plan_for_water` fw ON i.`org_id` = fw.`org_id`      "
                + " WHERE i.`year`='" + year + "' AND i.`properties_id`=bwp.`properties_id`),0)  "
                + " AS userWater                                                         "
                + " FROM `base_water_properties` bwp                                     "
                + " UNION ALL                                                            "
                + " ("
                + " SELECT '总计',IFNULL(SUM(bap.`reply_of_water`),0),IFNULL(SUM(bm.`water`),0)                          "
                + " FROM `base_water_properties` p JOIN `bus_meter_read_info` bm ON p.`properties_id`=bm.`properties_id` "
                + " JOIN `bus_annual_plan_for_water` bap ON bm.`org_id`=bap.`org_id` WHERE bm.`year`='" + year + "'              "
                + " )";
        RowMapper<Object[]> rowMapper = new RowMapper<Object[]>() {

            @Override
            public Object[] mapRow(ResultSet rs, int arg1)
                    throws SQLException {
                Object[] obj = new Object[3];
                obj[0] = rs.getString("properties_name");
                obj[1] = rs.getFloat("reply");
                obj[2] = rs.getFloat("userWater");
                return obj;
            }
        };
        List<Object[]> list = jdbcTemplate.query(sql, rowMapper);
        return list;
    }

    @Override
    public void updateIsplanYear(String year) {
        this.busOrgDao.updateIsplanYear(year);

    }

    @Override
    public void updateSetplanYear(String initYear, Date initStartTime,
                                  Date initEndTime, Date bMonthStartTime, Date bMonthEndTime,
                                  Date eMonthStartTime, Date eMonthEndTime, Float checkWater) {
        this.busOrgDao.updateSetplanYear(initYear, initStartTime, initEndTime, bMonthStartTime, bMonthEndTime, eMonthStartTime, eMonthEndTime, checkWater);

    }

    @Override
    public void updateCosMeter(String year) {
        this.busOrgDao.updateCosMeter(year);

    }

    @Override
    public void delteInfoForSplitOrg(Long orgId) {
        //先删除用水信息
        jdbcTemplate.update("DELETE FROM `bus_meter_read_info` WHERE `org_id`=?", orgId);
        //删除对应水表
        jdbcTemplate.update("DELETE FROM `bus_customer_meter` WHERE `org_id`=?", orgId);
        //删除年度供水计划
        jdbcTemplate.update("DELETE FROM `bus_annual_plan_for_water` WHERE `org_id`=?", orgId);
        //删除季度考核
        jdbcTemplate.update("DELETE FROM `bus_quarterly_assessment` WHERE `org_id`=?", orgId);
        //删除单位
        jdbcTemplate.update("DELETE FROM `bus_org` WHERE `org_id`=?", orgId);
    }

    @Override
    public Long findOrgNumberByYear(Integer orgNumber) {
        String sql = "SELECT o.`org_id` FROM `bus_org` o WHERE o.`org_number` = " + orgNumber + " AND o.`year` = DATE_FORMAT( CURRENT_TIMESTAMP(),'%Y' ) AND o.`import_status` = 'normal'";
        Long orgId = jdbcTemplate.queryForLong(sql);
        return orgId;
    }

    @Override
    public void updateAddress(String orgId, String postalAddress) {
        jdbcTemplate.update("UPDATE `bus_org` SET `postal_address`=? WHERE `org_id`=?", postalAddress, orgId);//

    }
}