Homestay_app_houtai/mhotel/src/com/happy/dao/impl/AdminManagerImplDao.java

package com.happy.dao.impl;

import com.happy.Model.Admin;
import com.happy.Model.AdminManager;
import com.happy.Until.Func;
import com.happy.Until.SqlUtil;
import com.happy.Until.UUIDUtil;
import com.happy.dao.AdminManagerDao;
import com.happy.dto.townshipCountDTO;
import org.apache.poi.ss.formula.functions.T;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository("AdminManagerDao")
public class AdminManagerImplDao implements AdminManagerDao {

    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public NamedParameterJdbcTemplate getNamedParameterJdbcTemplate() {
        return namedParameterJdbcTemplate;
    }

    public void setNamedParameterJdbcTemplate(NamedParameterJdbcTemplate namedParameterJdbcTemplate) {
        this.namedParameterJdbcTemplate = namedParameterJdbcTemplate;
    }

    public String selectSql =
        "select " +
        "a.id, a.type, a.manager_id, a.link_pro_url, a.admin_name, a.hotel_name, a.hotel_township, b.name hotel_township_name, a.corpn_name, a.corpn_phone, a.level, a.admin_power, a.bank_card, a.card_name, a.bank_name, a.create_id, DATE_FORMAT(a.create_date,'%Y-%m-%d %T') create_date,DATE_FORMAT(a.modify_date,'%Y-%m-%d %T') modify_date,a.status,a.remark,a.openid " +
        "from `admin_manager` a " +
        "left join hotel_dict b on a.hotel_township = b.id";


    @Override
    public int insertAdmin(AdminManager adminManager) {

        String sql = "INSERT INTO admin_manager (id, type, manager_id, admin_name, password, link_pro_url, hotel_name, hotel_township, corpn_name, corpn_phone, level, admin_power, bank_card, card_name, bank_name, create_id, create_date, status, remark) VALUES (:id, :type, :manager_id ,:admin_name, :password, :link_pro_url, :hotel_name, :hotel_township, :corpn_name, :corpn_phone, :level, :admin_power, :bank_card, :card_name, :bank_name, :create_id, :create_date, :status, :remark)";

        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("type", adminManager.getType());
        sps.addValue("manager_id", adminManager.getManagerId());
        sps.addValue("admin_name", adminManager.getAdminName());
        sps.addValue("password", adminManager.getPassword());
        sps.addValue("link_pro_url", adminManager.getLinkProUrl());
        sps.addValue("hotel_name", adminManager.getHotelName());
        sps.addValue("hotel_township", adminManager.getHotelTownship());
        sps.addValue("corpn_name", adminManager.getCorpnName());
        sps.addValue("corpn_phone", adminManager.getCorpnPhone());
        sps.addValue("level", adminManager.getLevel());
        sps.addValue("admin_power", adminManager.getAdminPower());
        sps.addValue("bank_card", adminManager.getBankCard());
        sps.addValue("card_name", adminManager.getCardName());
        sps.addValue("bank_name", adminManager.getBankName());
        sps.addValue("create_id", adminManager.getCreateId());
        sps.addValue("create_date",UUIDUtil.getNewDate());
        sps.addValue("status", 1);
        sps.addValue("remark", adminManager.getRemark());
        if(adminManager.getId()==null){
            sps.addValue("id", UUIDUtil.generateID());
        }else{
            sps.addValue("id", adminManager.getId());
        }
        int num = 0;
        try{
            num = namedParameterJdbcTemplate.update(sql, sps);
        }
        catch(Exception e){
            e.printStackTrace();
        }
        return num;
    }

    @Override
    public int updateAdmin(AdminManager adminManager) {

        StringBuffer stringBuffer =  new StringBuffer(" update `admin_manager` set  ");
        MapSqlParameterSource sps = new MapSqlParameterSource();
        // 将要修改的数据填充到查询语句中
        appendValue(adminManager,stringBuffer,sps);
        stringBuffer.append("  where id=:id  ");
        sps.addValue("id", adminManager.getId());
        int num = 0;
        try{
            num = namedParameterJdbcTemplate.update(stringBuffer.toString(), sps);
        }
        catch(Exception e){
            e.printStackTrace();
        }
        return num;
    }

    @Override
    public int delAdmin(int id) {
        String sql = "update `admin_manager` set status=0 where id=:id";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("id",id);
        int num = 0;
        try{
            num = namedParameterJdbcTemplate.update(sql, sps);
        }catch (Exception e){
            e.printStackTrace();
        }
        return num;
    }

    @Override
    public AdminManager getById(int id) {
        String sql = selectSql + " WHERE a.id = :id ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("id",id);
        List<AdminManager> list = null;
        try{
            list = namedParameterJdbcTemplate.query(sql, sps,
                    new BeanPropertyRowMapper<>(AdminManager.class));
        }catch (Exception e){
            e.printStackTrace();
        }
        if(list != null && list.size()>0) return list.get(0);
        return null;
    }

    @Override
    public List<AdminManager> queryPage(String sqlx, int page, int rows) {
        SqlUtil.filterKeyword(sqlx);
        int start = (page - 1) * rows;// 每页的起始下标
        String sql = selectSql + " WHERE a.status!=0 "+sqlx+" ORDER BY a.id DESC limit :start,:rows ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("start", start);
        sps.addValue("rows", rows);
        List<AdminManager> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<>(AdminManager.class));
        if (list != null && list.size() > 0) return list;
        return null;
    }

    @Override
    public int queryTotal(String sqlx) {
        SqlUtil.filterKeyword(sqlx);
        String sql = "SELECT count(*) FROM`admin_manager` where status!=0 "+sqlx;
        MapSqlParameterSource sps = new MapSqlParameterSource();
        return namedParameterJdbcTemplate.queryForInt(sql, sps);
    }

    @Override
    public List<AdminManager> queryList(String sqlx) {
        SqlUtil.filterKeyword(sqlx);
        String sql = selectSql + " WHERE a.status!=0 "+sqlx ;
        List<AdminManager> list = null;
        try{
            list = namedParameterJdbcTemplate.query(sql, new BeanPropertyRowMapper<>(AdminManager.class));
        }catch (Exception e){
            e.printStackTrace();
        }
        if(list != null && list.size()>0) return list;
        return null;
    }

    @Override
    public List<townshipCountDTO> getTownshipCount(){
        String sql = "select a.name,sum(case when b.id is not null then 1 else 0 end) sumNumber,sum(case when b.type=1 then 1 else 0 end) aSum,sum(case when b.type=2 then 1 else 0 end) bSum,sum(case when b.type=3 then 1 else 0 end) cSum from \n" +
                "(select * from hotel_dict where code = 10 ) a\n" +
                "left join (select a.*,b.hstatus from admin_manager a\n" +
                "left join hotel b on a.id = b.manager_id\n" +
                "where b.hstatus = 1 and a.status = 1) b on b.hotel_township = a.id\n" +
                "group by a.id ORDER BY count(1) DESC";
        List<townshipCountDTO> list = null;
        try{
            list = namedParameterJdbcTemplate.query(sql, new BeanPropertyRowMapper<>(townshipCountDTO.class));
        }catch (Exception e){
            e.printStackTrace();
        }
        if(list != null && list.size()>0) return list;
        return null;
    }

    @Override
    public List<townshipCountDTO> getResidueCount(){
        String sql = "select a.name,ifnull(b.number,0) residueNumber from \n" +
                "(select * from hotel_dict where code = 10 ) a\n" +
                "left join (select d.hotel_township,sum(a.number-ifnull(b.number,0)) number from \n" +
                "(select id,manager_id,number from house where status = 1) a\n" +
                "left join (select house_id,count(1) number from booking where DATE_FORMAT(order_start_time,'%Y-%m-%d') = CURDATE() and order_status in (2,3,4) GROUP BY house_id) b on a.id = b.house_id\n" +
                "left join hotel c on c.manager_id = a.manager_id\n" +
                "left join admin_manager d on d.id = c.manager_id\n" +
                "where c.hstatus = 1\n" +
                "GROUP BY d.hotel_township) b on a.id = b.hotel_township\n" +
                "ORDER BY number";
        List<townshipCountDTO> list = null;
        try{
            list = namedParameterJdbcTemplate.query(sql, new BeanPropertyRowMapper<>(townshipCountDTO.class));
        }catch (Exception e){
            e.printStackTrace();
        }
        if(list != null && list.size()>0) return list;
        return null;
    }

    public void appendValue(AdminManager adminManager, StringBuffer stringBuffer, MapSqlParameterSource sps){

        if (!Func.checkNull(String.valueOf(adminManager.getType()))){
            stringBuffer.append(" type=:type ,");
            sps.addValue("type", adminManager.getType());
        }
        if (!Func.checkNull(adminManager.getLinkProUrl())){
            stringBuffer.append(" link_pro_url=:link_pro_url ,");
            sps.addValue("link_pro_url", adminManager.getLinkProUrl());
        }
        if (!Func.checkNull(adminManager.getAdminName())){
            stringBuffer.append(" admin_name=:admin_name ,");
            sps.addValue("admin_name", adminManager.getAdminName());
        }
        if (!Func.checkNull(adminManager.getPassword())){
            stringBuffer.append(" password=:password ,");
            sps.addValue("password", adminManager.getPassword());
        }
        if (!Func.checkNull(adminManager.getHotelName())){
            stringBuffer.append(" hotel_name=:hotel_name ,");
            sps.addValue("hotel_name", adminManager.getHotelName());
        }
        if (!Func.checkNull(adminManager.getHotelTownship())){
            stringBuffer.append(" hotel_township=:hotel_township ,");
            sps.addValue("hotel_township", adminManager.getHotelTownship());
        }
        if (!Func.checkNull(adminManager.getCorpnName())){
            stringBuffer.append(" corpn_name=:corpn_name ,");
            sps.addValue("corpn_name", adminManager.getCorpnName());
        }
        if (!Func.checkNull(adminManager.getCorpnPhone())){
            stringBuffer.append(" corpn_phone=:corpn_phone ,");
            sps.addValue("corpn_phone", adminManager.getCorpnPhone());
        }
        if (!Func.checkNull(adminManager.getLevel())){
            stringBuffer.append(" level=:level ,");
            sps.addValue("level", adminManager.getLevel());
        }
        if (!Func.checkNull(adminManager.getAdminPower())){
            stringBuffer.append(" admin_power=:admin_power ,");
            sps.addValue("admin_power", adminManager.getAdminPower());
        }
        if (!Func.checkNull(String.valueOf(adminManager.getBankCard()))){
            stringBuffer.append(" bank_card=:bank_card ,");
            sps.addValue("bank_card", adminManager.getBankCard());
        }
        if (!Func.checkNull(adminManager.getCardName())){
            stringBuffer.append(" card_name=:card_name ,");
            sps.addValue("card_name", adminManager.getCardName());
        }
        if (!Func.checkNull(adminManager.getBankName())){
            stringBuffer.append(" bank_name=:bank_name ,");
            sps.addValue("bank_name", adminManager.getBankName());
        }
        if (!Func.checkNull(String.valueOf(adminManager.getStatus()))){
            stringBuffer.append(" status=:status ,");
            sps.addValue("status", adminManager.getStatus());
        }
        if (!Func.checkNull(adminManager.getRemark())){
            stringBuffer.append(" remark=:remark ,");
            sps.addValue("remark", adminManager.getRemark());
        }
        stringBuffer.append(" modify_date=:modify_date ");
        sps.addValue("modify_date", UUIDUtil.getNewDate());
    }

}