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

package com.happy.dao.impl;

import com.happy.Model.Booking;
import com.happy.Model.Booking;
import com.happy.Model.House;
import com.happy.Until.Func;
import com.happy.Until.SqlUtil;
import com.happy.Until.UUIDUtil;
import com.happy.dao.BookDao;
import com.happy.dto.BookTypeEto;
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.ArrayList;
import java.util.List;

@Repository("BookDao")
public class BookImplDao implements BookDao {

    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public NamedParameterJdbcTemplate getNamedParameterJdbcTemplate() {
        return namedParameterJdbcTemplate;
    }

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

    //查询字段
    String selectCol = "id, order_num, order_status, user_idnumber, user_name, user_phone, hotel_id, hotel_name, hotel_hposition," +
            " hotel_hposition_wens, hotel_phone, hotel_manager_id, hotel_person, hotel_township, hotel_config, hotel_type," +
            " hotel_is_canorder,hotel_is_order, house_id, house_name, house_config, house_unit_price, house_total_price, house_order_number," +
            " house_areas, house_remake, DATE_FORMAT(order_start_time,'%Y-%m-%d %T') order_start_time, DATE_FORMAT(order_end_time,'%Y-%m-%d %T') order_end_time," +
            " order_live_time, order_remake, DATE_FORMAT(create_time,'%Y-%m-%d %T') create_time, create_userid, DATE_FORMAT(update_time,'%Y-%m-%d %T') update_time," +
            " DATE_FORMAT(pay_time,'%Y-%m-%d %T') pay_time, pay_account, pay_way, refund_way, refund_amount, DATE_FORMAT(refund_time,'%Y-%m-%d %T') refund_time," +
            " DATE_FORMAT(check_out_time,'%Y-%m-%d %T') check_out_time,lock_time, remake, is_delete, DATE_FORMAT(live_time,'%Y-%m-%d %T') live_time";


    @Override
    public int insertBooking(Booking book) {
        String sql = "INSERT INTO booking (id, order_num, order_status, user_idnumber, user_name, user_phone, hotel_id," +
                " hotel_name, hotel_hposition, hotel_hposition_wens, hotel_phone, hotel_manager_id, hotel_person, hotel_township, " +
                "hotel_config, hotel_type, hotel_is_canorder, hotel_is_order, hotel_is_checkout, house_id, house_name, house_config, " +
                "house_unit_price, house_total_price, house_order_number, house_areas, house_remake, order_start_time, order_end_time," +
                " order_live_time, order_remake, create_time, create_userid, update_time, pay_time, pay_account, pay_way, refund_way," +
                " refund_amount, refund_time, check_out_time,lock_time, remake, is_delete) " +
                "VALUES (:id, :order_num, :order_status, :user_idnumber, :user_name, :user_phone, :hotel_id, :hotel_name, :hotel_hposition, " +
                ":hotel_hposition_wens, :hotel_phone, :hotel_manager_id, :hotel_person, :hotel_township, :hotel_config, :hotel_type," +
                " :hotel_is_canorder, :hotel_is_order, :hotel_is_checkout, :house_id, :house_name, :house_config, :house_unit_price," +
                " :house_total_price, :house_order_number, :house_areas, :house_remake, :order_start_time, :order_end_time, :order_live_time," +
                " :order_remake, :create_time, :create_userid, :update_time, :pay_time, :pay_account, :pay_way, :refund_way, :refund_amount," +
                " :refund_time, :check_out_time,:lock_time, :remake, :is_delete)";

        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("order_num",book.getOrderNum());
        sps.addValue("order_status",book.getOrderStatus());
        sps.addValue("user_idnumber",book.getUserIdnumber());
        sps.addValue("user_name",book.getUserName());
        sps.addValue("user_phone",book.getUserPhone());
        sps.addValue("hotel_id",book.getHotelId());
        sps.addValue("hotel_name",book.getHotelName());
        sps.addValue("hotel_hposition",book.getHotelHposition());
        sps.addValue("hotel_hposition_wens",book.getHotelHpositionWens());
        sps.addValue("hotel_phone",book.getHotelPhone());
        sps.addValue("hotel_person",book.getHotelPerson());
        sps.addValue("hotel_township",book.getHotelTownship());
        sps.addValue("hotel_config",book.getHotelConfig());
        sps.addValue("hotel_type",book.getHotelType());
        sps.addValue("hotel_is_canorder",book.getHotelIsCanorder());
        sps.addValue("hotel_is_order",book.getHotelIsOrder());
        sps.addValue("hotel_is_checkout",book.getHotelIsCheckout());
        sps.addValue("hotel_manager_id",book.getHotelManagerId());
        sps.addValue("house_id",book.getHouseId());
        sps.addValue("house_name", book.getHouseName());
        sps.addValue("house_config",book.getHouseConfig());
        sps.addValue("house_unit_price", book.getHouseUnitPrice());
        sps.addValue("house_total_price",book.getHouseTotalPrice());
        sps.addValue("house_order_number", book.getHouseOrderNumber());
        sps.addValue("house_areas",book.getHouseAreas());
        sps.addValue("house_remake", book.getHouseRemake());
        sps.addValue("order_start_time",book.getOrderStartTime());
        sps.addValue("order_end_time", book.getOrderEndTime());
        sps.addValue("order_live_time",book.getOrderLiveTime());
        sps.addValue("order_remake", book.getOrderRemake());
        sps.addValue("create_time",book.getCreateTime());
        sps.addValue("create_userid", book.getCreateUserid());
        sps.addValue("update_time",book.getUpdateTime());
        sps.addValue("pay_time", book.getPayTime());
        sps.addValue("pay_account",book.getPayAccount());
        sps.addValue("pay_way", book.getPayWay());
        sps.addValue("refund_way",book.getRefundWay());
        sps.addValue("refund_amount", book.getRefundAmount());
        sps.addValue("refund_time", book.getRefundTime());
        sps.addValue("check_out_time",book.getCheckOutTime());
        sps.addValue("lock_time",book.getLockTime());
        sps.addValue("remake", book.getRemake());
        sps.addValue("is_delete", 1);
        if(book.getId()==null){
            sps.addValue("id", UUIDUtil.generateID());
        } else{
            sps.addValue("id", book.getId());
        }

        int num = 0;
        try{
            num = namedParameterJdbcTemplate.update(sql, sps);
        }
        catch(Exception e){
            e.printStackTrace();
        }
        return num;
    }

    @Override
    public int updateBooking(Booking book) {
        String sql = "UPDATE booking SET  order_num = :order_num, order_status = :order_status, user_idnumber = :user_idnumber," +
                " user_name = :user_name, user_phone = :user_phone, hotel_id = :hotel_id, hotel_name = :hotel_name, hotel_hposition = :hotel_hposition," +
                " hotel_hposition_wens = :hotel_hposition_wens, hotel_phone = :hotel_phone, hotel_manager_id = :hotel_manager_id," +
                " hotel_person = :hotel_person, hotel_township = :hotel_township, hotel_config = :hotel_config, hotel_type = :hotel_type," +
                " hotel_is_canorder = :hotel_is_canorder,hotel_is_order = :hotel_is_order,hotel_is_checkout = :hotel_is_checkout, house_id = :house_id," +
                " house_name = :house_name, house_config = :house_config, house_unit_price = :house_unit_price, house_total_price = :house_total_price," +
                " house_order_number = :house_order_number, house_areas = :house_areas, house_remake = :house_remake, order_start_time = :order_start_time," +
                " order_end_time = :order_end_time, order_live_time = :order_live_time, order_remake = :order_remake, create_time = :create_time, create_userid = :create_userid," +
                " update_time = :update_time, pay_time = :pay_time,pay_account = :pay_account, pay_way = :pay_way, refund_way = :refund_way,refund_amount = :refund_amount," +
                " refund_time = :refund_time, check_out_time = :check_out_time,lock_time=:lock_time, remake = :remake, is_delete = :is_delete, live_time = :live_time  WHERE id = :id";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("order_num",book.getOrderNum());
        sps.addValue("order_status",book.getOrderStatus());
        sps.addValue("user_idnumber",book.getUserIdnumber());
        sps.addValue("user_name",book.getUserName());
        sps.addValue("user_phone",book.getUserPhone());
        sps.addValue("hotel_id",book.getHotelId());
        sps.addValue("hotel_name",book.getHotelName());
        sps.addValue("hotel_hposition",book.getHotelHposition());
        sps.addValue("hotel_hposition_wens",book.getHotelHpositionWens());
        sps.addValue("hotel_phone",book.getHotelPhone());
        sps.addValue("hotel_person",book.getHotelPerson());
        sps.addValue("hotel_township",book.getHotelTownship());
        sps.addValue("hotel_config",book.getHotelConfig());
        sps.addValue("hotel_type",book.getHotelType());
        sps.addValue("hotel_is_canorder",book.getHotelIsCanorder());
        sps.addValue("hotel_is_order",book.getHotelIsOrder());
        sps.addValue("hotel_is_checkout",book.getHotelIsCheckout());
        sps.addValue("hotel_manager_id",book.getHotelManagerId());
        sps.addValue("house_id",book.getHouseId());
        sps.addValue("house_name", book.getHouseName());
        sps.addValue("house_config",book.getHouseConfig());
        sps.addValue("house_unit_price", book.getHouseUnitPrice());
        sps.addValue("house_total_price",book.getHouseTotalPrice());
        sps.addValue("house_order_number", book.getHouseOrderNumber());
        sps.addValue("house_areas",book.getHouseAreas());
        sps.addValue("house_remake", book.getHouseRemake());
        sps.addValue("order_start_time",book.getOrderStartTime());
        sps.addValue("order_end_time", book.getOrderEndTime());
        sps.addValue("order_live_time",book.getOrderLiveTime());
        sps.addValue("order_remake", book.getOrderRemake());
        sps.addValue("create_time",book.getCreateTime());
        sps.addValue("create_userid", book.getCreateUserid());
        sps.addValue("update_time",book.getUpdateTime());
        sps.addValue("pay_time", book.getPayTime());
        sps.addValue("pay_account",book.getPayAccount());
        sps.addValue("pay_way", book.getPayWay());
        sps.addValue("refund_way",book.getRefundWay());
        sps.addValue("refund_amount", book.getRefundAmount());
        sps.addValue("refund_time", book.getRefundTime());
        sps.addValue("check_out_time",book.getCheckOutTime());
        sps.addValue("remake", book.getRemake());
        sps.addValue("lock_time", book.getLockTime());
        sps.addValue("is_delete", book.getIsDelete());  // 是否假删除：0删除，1正常
        sps.addValue("live_time", book.getLiveTime());
        sps.addValue("id",book.getId());

        int num = 0;
        try{
            num = namedParameterJdbcTemplate.update(sql, sps);
        }
        catch(Exception e){
            e.printStackTrace();
        }
        return num;
    }

    @Override
    public int delBooking(int id) {
        String sql = "DELETE FROM `booking` 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 Booking getById(int id) {
        String sql = "SELECT "+selectCol+" FROM `booking` WHERE id = :id ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("id",id);
        List<Booking> list = null;
        try{
            list = namedParameterJdbcTemplate.query(sql, sps,
                    new BeanPropertyRowMapper<>(Booking.class));
        }catch (Exception e){
            e.printStackTrace();
        }
        if(list != null && list.size()>0) return list.get(0);
        return null;
    }

    @Override
    public List<Booking> queryPage(String sqlx, int page, int rows) {
        SqlUtil.filterKeyword(sqlx);

        int start = (page - 1) * rows;// 每页的起始下标
        String sql = "SELECT a.*,b.name hotel_township_name FROM (select "+selectCol+" from booking) a left join hotel_dict b on a.hotel_township = b.id WHERE 1=1 "+sqlx+" ORDER BY create_time DESC limit :start,:rows ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("start", start);
        sps.addValue("rows", rows);
        List<Booking> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<>(Booking.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`booking` where 1=1 "+sqlx;
        MapSqlParameterSource sps = new MapSqlParameterSource();
        return namedParameterJdbcTemplate.queryForInt(sql, sps);
    }

    @Override
    public List<Booking> queryList(String sqlx) {
        SqlUtil.filterKeyword(sqlx);
        String sql = "SELECT "+selectCol+",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_name FROM `booking` WHERE 1=1 "+sqlx;
        List<Booking> list = null;
        try{
            list = namedParameterJdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Booking.class));
        }catch (Exception e){
            e.printStackTrace();
        }
        if(list != null && list.size()>0) return list;
        return null;
    }

    @Override
    public Double sumAccount(String sqlx){
        SqlUtil.filterKeyword(sqlx);
        String sql = "select sum(pay_account) pay_account from booking where is_delete=1"+sqlx;
        List<Booking> list = null;
        try{
            list = namedParameterJdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Booking.class));
        }catch (Exception e){
            e.printStackTrace();
        }
        if(list != null && list.size()>0) return list.get(0).getPayAccount();
        return null;
    }

    @Override
    public BookTypeEto getBookStatusSum(String sqlx){
        SqlUtil.filterKeyword(sqlx);
        String sql = "select ifnull(sum(case when order_status = 2 then 1 else 0 end),0) pendingOrderSum,count(1) orderSum,ifnull(sum(case when  order_status = 5 then 1 else 0 end),0) consumerOrderSum,ifnull(sum(case when  order_status = 5 then pay_account else 0 end),0) sumAccount from booking where 1=1"+sqlx;
        List<BookTypeEto> list = null;
        try{
            list = namedParameterJdbcTemplate.query(sql, new BeanPropertyRowMapper<>(BookTypeEto.class));
        }catch (Exception e){
            e.printStackTrace();
        }
        if(list != null && list.size()>0) return list.get(0);
        return null;
    }



}