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

package com.happy.dao.impl;

import com.happy.Model.House;
import com.happy.Until.SqlUtil;
import com.happy.dao.IDCDao;
import com.happy.dto.IDCBookStatusEto;
import com.happy.dto.IDCHotelEto;
import com.happy.dto.IDCRankEto;
import com.happy.dto.IDCSum;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository("IDCDao")
public class IDCImplDao implements IDCDao {

    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public NamedParameterJdbcTemplate getNamedParameterJdbcTemplate() {
        return namedParameterJdbcTemplate;
    }

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

    @Override
    public List<IDCBookStatusEto> getBookStatusData() {
        String sql = "select a.*,ROUND(a.status_num/b.sum,2) proportion from (select order_status,case when order_status=1 then '待支付' when order_status=2 then '已支付' when order_status=3 then '待入住' when order_status=4 then '已入住' when order_status=5 then '已消费' when order_status=6 then '支付超时' when order_status=7 then '已取消' when order_status=8 then '已退单' when order_status=9 then '已退款' else '无状态' end order_status_name,count(1) status_num from booking where status_del='1' group by order_status order by order_status) a left join (select count(1) sum from booking where status_del='1') b on 1=1 order by proportion desc";
        List<IDCBookStatusEto> list = null;
        try{
            list = namedParameterJdbcTemplate.query(sql, new BeanPropertyRowMapper<>(IDCBookStatusEto.class));
        }catch (Exception e){
            e.printStackTrace();
        }
        if(list != null && list.size()>0) return list;
        return null;
    }

    @Override
    public List<IDCHotelEto> getHotelData() {
        String sql = "select a.NAME hposition,sum(case when ifnull(c.id,0)=0 then 0 else 1 end) hotelNum from hotel_dict a\n" +
                "left join (select * from admin_manager where status=1) b on b.hotel_township = a.id\n" +
                "left join (select * from hotel where status=1) c on c.manager_id = b.id\n" +
                "where a.code=10 group by a.id order by sum(case when ifnull(c.id,0)=0 then 0 else 1 end) DESC";
        List<IDCHotelEto> list = null;
        try{
            list = namedParameterJdbcTemplate.query(sql, new BeanPropertyRowMapper<>(IDCHotelEto.class));
        }catch (Exception e){
            e.printStackTrace();
        }
        if(list != null && list.size()>0) return list;
        return null;
    }

    @Override
    public List<IDCRankEto> getRankBookNumData(String sqlx) {
        SqlUtil.filterKeyword(sqlx);
        String sql = "select a.*,@curRank := @curRank + 1 AS rank from (select hotel_name,count(1) book_num from booking where order_status=2 " +sqlx + " group by hotel_id ORDER BY count(1) DESC) a,(SELECT @curRank := 0) r";
        List<IDCRankEto> list = null;
        try{
            list = namedParameterJdbcTemplate.query(sql, new BeanPropertyRowMapper<>(IDCRankEto.class));
        }catch (Exception e){
            e.printStackTrace();
        }
        if(list != null && list.size()>0) return list;
        return null;
    }

    @Override
    public List<IDCRankEto> getRankSalesAmountData(String sqlx) {
        SqlUtil.filterKeyword(sqlx);
        String sql = "select a.*,@curRank := @curRank + 1 AS rank from (select hotel_name,sum(pay_account) salesAmount from booking where order_status=2 "+sqlx+" group by hotel_id ORDER BY sum(pay_account) DESC) a,(SELECT @curRank := 0) r";
        List<IDCRankEto> list = null;
        try{
            list = namedParameterJdbcTemplate.query(sql, new BeanPropertyRowMapper<>(IDCRankEto.class));
        }catch (Exception e){
            e.printStackTrace();
        }
        if(list != null && list.size()>0) return list;
        return null;
    }

    @Override
    public IDCSum getSumData(){
        String sql = "select *,bookNumDay-bookNumyDay bookNumGrowth,salesAmountDay-salesAmountyDay salesAmountGrowth from (\n" +
                "\t(select count(1) bookNumDay from booking where order_status = 2 and DATE_FORMAT(pay_time,'%Y-%m-%d') = DATE_FORMAT(NOW(),'%Y-%m-%d')) a,\n" +
                "\t(select count(1) bookNumyDay from booking where order_status = 2 and DATE_FORMAT(pay_time,'%Y-%m-%d') = DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 1 DAY),'%Y-%m-%d')) b,\n" +
                "\t(select sum(pay_account) salesAmountDay from booking where order_status = 2 and DATE_FORMAT(pay_time,'%Y-%m-%d') = DATE_FORMAT(NOW(),'%Y-%m-%d')) c,\n" +
                "\t(select sum(pay_account) salesAmountyDay from booking where order_status = 2 and DATE_FORMAT(pay_time,'%Y-%m-%d') = DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 1 DAY),'%Y-%m-%d')) d,\n" +
                "\t(select count(1) hotelSum from admin_manager where status = 1 and level=2) e\n" +
                ")";
        List<IDCSum> list = null;
        try{
            list = namedParameterJdbcTemplate.query(sql, new BeanPropertyRowMapper<>(IDCSum.class));
        }catch (Exception e){
            e.printStackTrace();
        }
        if(list != null && list.size()>0) return list.get(0);
        return null;
    }
}