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

package com.happy.dao.impl;

import com.happy.Model.AdminManager;
import com.happy.Model.HotelCoupon;
import com.happy.Until.Func;
import com.happy.Until.SqlUtil;
import com.happy.Until.UUIDUtil;
import com.happy.dao.HotelCouponDao;
import com.happy.vo.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.namedparam.EmptySqlParameterSource;
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.Arrays;
import java.util.List;
import java.util.UUID;

@Repository("HotelCouponDao")
public class HotelCouponImplDao implements HotelCouponDao {

    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public NamedParameterJdbcTemplate getNamedParameterJdbcTemplate() {
        return namedParameterJdbcTemplate;
    }

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

    private String selectSql = "select " +
            "id,hotelIds,name,type,rebate_price,deduction_price,max_deduction,grant_number,limit_number,grant_start_date,grant_end_date," +
            "meet_price,effective_start_date,effective_end_date,effective_type,effective_day,effective_lose_day,create_id,create_date,modify_date,status,remainder_number,remainder_number" +
            " from hotel_coupon where 1=1";


    @Override
    public int insert(HotelCoupon hotelCoupon) {
        String sql = "INSERT INTO `hotel_coupon` (`id`, `hotelIds`, `name`, `type`, `rebate_price`, `deduction_price`, `max_deduction`, `grant_number`, `limit_number`, `grant_start_date`, `grant_end_date`, `meet_price`, `effective_start_date`, `effective_end_date`, `effective_day`,`effective_type`, `effective_lose_day`,`create_id`, `create_date`, `modify_date`, `status`, `remainder_number`, `reversed_number`) VALUES (:id,:hotelIds,:name,:type,:rebate_price,:deduction_price,:max_deduction,:grant_number,:limit_number,:grant_start_date,:grant_end_date,:meet_price,:effective_start_date,:effective_end_date,:effective_day,:effective_type,:effective_lose_day,:create_id,:create_date,:modify_date,:status,:remainder_number,:reversed_number)";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        if(hotelCoupon.getId()==null || "".equals(hotelCoupon.getId())){
            sps.addValue("id", String.valueOf(UUID.randomUUID()));
        }else{
            sps.addValue("id", hotelCoupon.getId());
        }
        sps.addValue("hotelIds", hotelCoupon.getHotelIds());
        sps.addValue("name", hotelCoupon.getName());
        sps.addValue("type", hotelCoupon.getType());
        sps.addValue("rebate_price", hotelCoupon.getRebatePrice());
        sps.addValue("deduction_price", hotelCoupon.getDeductionPrice());
        sps.addValue("max_deduction", hotelCoupon.getMaxDeduction());
        sps.addValue("grant_number", hotelCoupon.getGrantNumber());
        sps.addValue("limit_number", hotelCoupon.getLimitNumber());
        sps.addValue("grant_start_date", hotelCoupon.getGrantStartDate());
        sps.addValue("grant_end_date", hotelCoupon.getGrantEndDate());
        sps.addValue("meet_price", hotelCoupon.getMeetPrice());
        sps.addValue("effective_start_date", hotelCoupon.getEffectiveStartDate());
        sps.addValue("effective_end_date", hotelCoupon.getEffectiveEndDate());
        sps.addValue("effective_day", hotelCoupon.getEffectiveDay());
        sps.addValue("effective_type", hotelCoupon.getEffectiveType());
        sps.addValue("effective_lose_day", hotelCoupon.getEffectiveLoseDay());
        sps.addValue("create_id", hotelCoupon.getCreateId());
        sps.addValue("modify_date", UUIDUtil.getNewDate());
        sps.addValue("create_date", UUIDUtil.getNewDate());
        sps.addValue("status", hotelCoupon.getStatus());
        sps.addValue("remainder_number", hotelCoupon.getRemainderNumber());
        sps.addValue("reversed_number", hotelCoupon.getReversedNumber());
        int num = 0;
        try{
            num = namedParameterJdbcTemplate.update(sql, sps);
        }
        catch(Exception e){
            e.printStackTrace();
        }
        return num;
    }

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

    @Override
    public int delete(Integer id) {
        return 0;
    }

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

    @Override
    public List<HotelCoupon> queryPage(String sqlx, int page, int rows) {
        SqlUtil.filterKeyword(sqlx);
        int start = (page - 1) * rows;// 每页的起始下标
        String sql = selectSql + sqlx + " ORDER BY create_date DESC limit :start,:rows ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("start", start);
        sps.addValue("rows", rows);
        List<HotelCoupon> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<>(HotelCoupon.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 `hotel_coupon` where 1=1 "+sqlx;
        MapSqlParameterSource sps = new MapSqlParameterSource();
        return namedParameterJdbcTemplate.queryForInt(sql, sps);
    }

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

    @Override
    public int updateExpire() {
        //查询
        String sql = "SELECT `id` from `hotel_coupon` WHERE 1=1 and (DATE_FORMAT(`effective_end_date`, '%Y-%m-%d') < CURDATE() AND `effective_lose_day` IS NULL  and `status` = 1)" +
                "    OR" +
                "    (DATE_FORMAT(DATE_ADD(`effective_end_date`, INTERVAL `effective_lose_day` + `effective_day` DAY),'%Y-%m-%d') < CURDATE()  and `status` = 1)";
        int num = 0;
        try{
            List<String> ids = namedParameterJdbcTemplate.queryForList(sql, EmptySqlParameterSource.INSTANCE, String.class);
            if (ids.isEmpty()) return 0;
            //修改
            StringBuffer stringBuffer =  new StringBuffer(" update `hotel_coupon` set  ");
            MapSqlParameterSource sps = new MapSqlParameterSource();
            stringBuffer.append(" status = 3 ");
            stringBuffer.append(", modify_date=:modify_date ");
            sps.addValue("modify_date", UUIDUtil.getNewDate());
            stringBuffer.append("  where  id in (:id)");
            sps.addValue("id", ids);
            num = namedParameterJdbcTemplate.update(stringBuffer.toString(), sps);
        }catch (Exception e){
            e.printStackTrace();
        }
        return num;
    }


    @Override
    public int updateLapse(List<String> coupomIds) {
        StringBuffer stringBuffer =  new StringBuffer(" update `hotel_coupon` set  ");
        MapSqlParameterSource sps = new MapSqlParameterSource();
        // 将要修改的数据填充到查询语句中
        stringBuffer.append(" status = 2 ");//失效状态写死
        stringBuffer.append(", modify_date=:modify_date ");
        sps.addValue("modify_date", UUIDUtil.getNewDate());
        stringBuffer.append("  where  id in (:id)");
        sps.addValue("id", coupomIds);
        int num = 0;
        try{
            num = namedParameterJdbcTemplate.update(stringBuffer.toString(), sps);
        }
        catch(Exception e){
            e.printStackTrace();
        }
        return num;
    }

 @Override
    public List<CouponCollectionVo> couponCollection(String dateTime, int page, int rows) {
        int start = (page - 1) * rows;// 每页的起始下标
        String sql="SELECT\n" +
                "\t hc.id as id,hc.hotelIds as hotelIds,\n" +
                "\thc.type as type,\n" +
                "\thc.`name` as name,\n" +
                "\thc.rebate_price as rebatePrice,\n" +
                "\thc.deduction_price as deductionPrice,\n" +
                "\thc.max_deduction AS maxDeduction,\n" +
                "\thc.meet_price as meetPrice,\n" +
                "\thc.effective_start_date AS  effectiveStartDate,(SELECT COUNT( hcs.complaint_id ) AS totalCount FROM `hotel_coupon_status` hcs WHERE hcs.complaint_id = hc.id ) AS totalCount, hc.limit_number as limitNumber,hc.remainder_number as remainderNumber, \n" +
                "\thc.effective_end_date AS effectiveEndDate \n" +
                "FROM\n" +
                "\t`hotel_coupon` hc     where hc.effective_start_date < :dateTime and hc.effective_end_date> :dateTime and hc.`status` = 1  ORDER BY hc.effective_start_date DESC limit :start,:rows";

        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("dateTime", dateTime);
        sps.addValue("start", start);
        sps.addValue("rows", rows);
        List<CouponCollectionVo> list = null;
        try{
            list = namedParameterJdbcTemplate.query(sql, sps,new BeanPropertyRowMapper<>(CouponCollectionVo.class));
        }catch (Exception e){
            e.printStackTrace();
        }
        if(list != null && list.size()>0) return list;
        return null;
    }

    @Override
    public int couponCollectionTotal(String date) {
        String sql="SELECT\n" +
                "\t count(1)\n" +
                "FROM\n" +
                "\t`hotel_coupon` hc where hc.effective_start_date<:date and hc.effective_end_date>:date";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("date", date);
        return namedParameterJdbcTemplate.queryForInt(sql, sps);
    }

    @Override
    public DesignatedHotelVo designatedHotel(String hotelIds) {
        String sql="SELECT GROUP_CONCAT( h.hname ) as name FROM hotel h where FIND_IN_SET(h.id,:hotelIds)";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("hotelIds", hotelIds);
        List<DesignatedHotelVo> list = null;
        try{
            list = namedParameterJdbcTemplate.query(sql, sps,new BeanPropertyRowMapper<>(DesignatedHotelVo.class));
        }catch (Exception e){
            e.printStackTrace();
        }
        if(list != null && list.size()>0) return list.get(0);
        return null;
    }

    @Override
    public QuotaVo quota(String complaintId) {
        String sql="SELECT\n" +
                "\tCOUNT( hcs.complaint_id ) AS totalCount,\n" +
                "\t( SELECT hc.limit_number FROM hotel_coupon hc WHERE hc.id = :complaintId and hc.`status`=1 ) AS limitNumber, \n" +
                "\t( SELECT hc.remainder_number FROM hotel_coupon hc WHERE hc.id = :complaintId and hc.`status`=1 ) AS remainderNumber \n" +
                "FROM\n" +
                "\t`hotel_coupon_status` hcs \n" +
                "WHERE\n" +
                "\thcs.complaint_id = :complaintId ";

           MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("complaintId", complaintId);
        List<QuotaVo> list = null;
        try{
            list = namedParameterJdbcTemplate.query(sql, sps,new BeanPropertyRowMapper<>(QuotaVo.class));
        }catch (Exception e){
            e.printStackTrace();
        }
        if(list != null && list.size()>0) return list.get(0);
        return null;
    }

    @Override
    public int updateRemainderNumber(String complaintId,String modifyDate) {
        String sql="UPDATE `hotel_coupon`  SET remainder_number=remainder_number-1 ,modify_date=:modifyDate WHERE id=:complaintId ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("modifyDate", modifyDate);
        sps.addValue("complaintId", complaintId);
        int num = 0;
        try{
            num = namedParameterJdbcTemplate.update(sql, sps);
        }
        catch(Exception e){
            e.printStackTrace();
        }
        return num;
    }

    @Override
    public List<CardCouponPageVo> cardCouponPage(String types, String userId, int page, int rows) {
        int start = (page - 1) * rows;// 每页的起始下标
        String sql="SELECT\n" +
                "\thc.id as id,\n" +
                "\thc.hotelIds as hotelIds,\n" +
                "\thc.`name` as name,\n" +
                "\thc.type as type,\n" +
                "\thc.rebate_price as rebatePrice,\n" +
                "\thc.deduction_price as deductionPrice,\n" +
                "\thc.max_deduction as maxDeduction,\n" +
                "\thc.meet_price as meetPrice,\n" +
                "\thc.effective_end_date as effectiveEndDate ,\n" +
                "\tcount(hcs.complaint_id) AS count\n" +
                "FROM\n" +
                "\thotel_coupon_status hcs\n" +
                "\tLEFT JOIN hotel_coupon hc ON hc.id = hcs.complaint_id where FIND_IN_SET(hc.type,:status) and hcs.user_id=:userId and hcs.status=1 \n" +
                "\tGROUP BY hcs.complaint_id ORDER BY hc.effective_start_date DESC limit :start,:rows";

        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("status", types);
        sps.addValue("userId", userId);
        sps.addValue("start", start);
        sps.addValue("rows", rows);
        List<CardCouponPageVo> list = null;
        try{
            list = namedParameterJdbcTemplate.query(sql, sps,new BeanPropertyRowMapper<>(CardCouponPageVo.class));
        }catch (Exception e){
            e.printStackTrace();
        }
        if(list != null && list.size()>0) return list;
        return null;
    }

    @Override
    public int cardCouponPageTotal(String types, String userId) {
        String sql="SELECT COUNT(1) FROM (SELECT\n" +
                "\thc.id as id,\n" +
                "\thc.hotelIds as hotelIds,\n" +
                "\thc.`name` as name,\n" +
                "\thc.type as type,\n" +
                "\thc.rebate_price as rebatePrice,\n" +
                "\thc.deduction_price as deductionPrice,\n" +
                "\thc.max_deduction as maxDeduction,\n" +
                "\thc.meet_price as meetPrice,\n" +
                "\thc.effective_end_date as effectiveEndDate ,\n" +
                "\tcount(hcs.complaint_id) AS count\n" +
                "FROM\n" +
                "\thotel_coupon_status hcs\n" +
                "\tLEFT JOIN hotel_coupon hc ON hc.id = hcs.complaint_id where FIND_IN_SET(hc.type,:status) and hcs.user_id=:userId and hcs.status=1 \n" +
                "\tGROUP BY hcs.complaint_id ORDER BY hc.effective_start_date DESC ) hcs2";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("status", types);
        sps.addValue("userId", userId);

        return namedParameterJdbcTemplate.queryForInt(sql, sps);
    }

    @Override
    public List<UsefulCouponVo> usefulCoupon(String hotelId, String userId, int page, int rows) {
        int start = (page - 1) * rows;// 每页的起始下标
        String sql="SELECT\n" +
                "\t hcs.id as id , hcs.complaint_id AS complaintId,\n" +
                "\thc.hotelIds as hotelIds,\n" +
                "\thc.`name` as name,\n" +
                "\thc.type as type,\n" +
                "\thc.rebate_price as rebatePrice,\n" +
                "\thc.deduction_price as deductionPrice,\n" +
                "\thc.max_deduction as maxDeduction,\n" +
                "\thc.meet_price as meetPrice,\n" +
                "\thc.effective_end_date as effectiveEndDate \n" +
                "FROM\n" +
                "\thotel_coupon_status hcs\n" +
                "\tLEFT JOIN hotel_coupon hc ON hc.id = hcs.complaint_id where (FIND_IN_SET(:hotelId, hc.hotelIds) or hc.hotelIds=-1 ) and hcs.user_id=:userId and hcs.status=1 \n" +
                "\t ORDER BY hc.effective_start_date DESC limit :start,:rows";


        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("hotelId", hotelId);
        sps.addValue("userId", userId);
        sps.addValue("start", start);
        sps.addValue("rows", rows);
        List<UsefulCouponVo> list = null;
        try{
            list = namedParameterJdbcTemplate.query(sql, sps,new BeanPropertyRowMapper<>(UsefulCouponVo.class));
        }catch (Exception e){
            e.printStackTrace();
        }
        if(list != null && list.size()>0) return list;
        return null;
    }

    @Override
    public int usefulCouponTotal(String hotelId, String userId) {
        String sql="SELECT\n" +
                "\tCOUNT( 1 ) \n" +
                "FROM\n" +
                "\thotel_coupon_status hcs\n" +
                "\tLEFT JOIN hotel_coupon hc ON hc.id = hcs.complaint_id \n" +
                "WHERE\n" +
                "\t(FIND_IN_SET(:hotelId, hc.hotelIds) or hc.hotelIds=-1 ) \n" +
                "\tAND hcs.user_id = :userId \n" +
                "\tAND hcs.`status` = 1";

        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("hotelId", hotelId);
        sps.addValue("userId", userId);

        return namedParameterJdbcTemplate.queryForInt(sql, sps);
    }
    private void appendValue(HotelCoupon hotelCoupon, StringBuffer stringBuffer, MapSqlParameterSource sps){
        if (!Func.checkNull(String.valueOf(hotelCoupon.getHotelIds()))){
            stringBuffer.append(" hotelIds=:hotelIds ,");
            sps.addValue("hotelIds", hotelCoupon.getHotelIds());
        }
        if (!Func.checkNull(String.valueOf(hotelCoupon.getName()))){
            stringBuffer.append(" name=:name ,");
            sps.addValue("name", hotelCoupon.getName());
        }
        if (!Func.checkNull(String.valueOf(hotelCoupon.getType()))){
            stringBuffer.append(" type=:type ,");
            sps.addValue("type", hotelCoupon.getType());
        }
        if (!Func.checkNull(String.valueOf(hotelCoupon.getRebatePrice()))){
            stringBuffer.append(" rebate_price=:rebate_price ,");
            sps.addValue("rebate_price", hotelCoupon.getRebatePrice());
        }
        if (!Func.checkNull(String.valueOf(hotelCoupon.getDeductionPrice()))){
            stringBuffer.append(" deduction_price=:deduction_price ,");
            sps.addValue("deduction_price", hotelCoupon.getDeductionPrice());
        }
        if (!Func.checkNull(String.valueOf(hotelCoupon.getMaxDeduction()))){
            stringBuffer.append(" max_deduction=:max_deduction ,");
            sps.addValue("max_deduction", hotelCoupon.getMaxDeduction());
        }
        if (!Func.checkNull(String.valueOf(hotelCoupon.getGrantNumber()))){
            stringBuffer.append(" grant_number=:grant_number ,");
            sps.addValue("grant_number", hotelCoupon.getGrantNumber());
        }
        if (!Func.checkNull(String.valueOf(hotelCoupon.getLimitNumber()))){
            stringBuffer.append(" limit_number=:limit_number ,");
            sps.addValue("limit_number", hotelCoupon.getLimitNumber());
        }
        if (!Func.checkNull(String.valueOf(hotelCoupon.getGrantStartDate()))){
            stringBuffer.append(" grant_start_date=:grant_start_date ,");
            sps.addValue("grant_start_date", hotelCoupon.getGrantStartDate());
        }
        if (!Func.checkNull(String.valueOf(hotelCoupon.getGrantEndDate()))){
            stringBuffer.append(" grant_end_date=:grant_end_date ,");
            sps.addValue("grant_end_date", hotelCoupon.getGrantEndDate());
        }
        if (!Func.checkNull(String.valueOf(hotelCoupon.getMeetPrice()))){
            stringBuffer.append(" meet_price=:meet_price ,");
            sps.addValue("meet_price", hotelCoupon.getMeetPrice());
        }
        if (!Func.checkNull(String.valueOf(hotelCoupon.getEffectiveStartDate()))){
            stringBuffer.append(" effective_start_date=:effective_start_date ,");
            sps.addValue("effective_start_date", hotelCoupon.getEffectiveStartDate());
        }
        if (!Func.checkNull(String.valueOf(hotelCoupon.getEffectiveEndDate()))){
            stringBuffer.append(" effective_end_date=:effective_end_date ,");
            sps.addValue("effective_end_date", hotelCoupon.getEffectiveEndDate());
        }
        if (!Func.checkNull(String.valueOf(hotelCoupon.getEffectiveDay()))){
            stringBuffer.append(" effective_day=:effective_day ,");
            sps.addValue("effective_day", hotelCoupon.getEffectiveDay());
        }
        if (!Func.checkNull(String.valueOf(hotelCoupon.getEffectiveLoseDay()))){
            stringBuffer.append(" effective_lose_day=:effective_lose_day ,");
            sps.addValue("effective_lose_day", hotelCoupon.getEffectiveLoseDay());
        }
        if (!Func.checkNull(String.valueOf(hotelCoupon.getStatus()))){
            stringBuffer.append(" status=:status ,");
            sps.addValue("status", hotelCoupon.getStatus());
        }
        stringBuffer.append(" modify_date=:modify_date ");
        sps.addValue("modify_date", UUIDUtil.getNewDate());
    }
}