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

package com.happy.dao.impl;

import com.happy.Model.BookingComment;
import com.happy.Model.Hotel;
import com.happy.Model.Workflow;
import com.happy.Until.Func;
import com.happy.Until.SqlUtil;
import com.happy.Until.UUIDUtil;
import com.happy.dao.WorkflowDao;
import com.happy.vo.EvaluatePageVo;
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;
import java.util.UUID;

@Repository("WorkflowDao")
public class WorkflowImplDao implements WorkflowDao {

    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public NamedParameterJdbcTemplate getNamedParameterJdbcTemplate() {
        return namedParameterJdbcTemplate;
    }

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

    private String selectSql = " select a.id,a.link_id,a.type,a.title,a.status,a.remark,a.create_id,a.workflow_date,date_format(a.create_date, '%Y-%m-%d') as create_date,a.workflow_remark,a.workflow_name, " +
                                " case when a.type = 1 then b.hname when a.type = 2 then c.user_name else null end as createName "+
                                " from workflow a " +
                                " left join hotel b on a.type = 1 and a.create_id = b.id " +
                                " left join users c on a.type = 2 and a.create_id = c.id " +
                                " where 1=1";

    @Override
    public int insert(Workflow workflow) {
        String sql = "insert into `workflow`(id,link_id,type,title,status,remark,create_id,create_date)" +
                "values(:id,:link_id,:type,:title,:status,:remark,:create_id,:create_date) ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("link_id",workflow.getLinkId());
        sps.addValue("type",workflow.getType());
        sps.addValue("title",workflow.getTitle());
        sps.addValue("status",workflow.getStatus());
        sps.addValue("remark",workflow.getRemark());
        sps.addValue("create_id",workflow.getCreateId());
        sps.addValue("create_date",UUIDUtil.getNewDate());
        if (workflow.getId() == null) {
            sps.addValue("id", String.valueOf(UUID.randomUUID()));
        } else {
            sps.addValue("id", workflow.getId());

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

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

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

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

    public void appendValue(Workflow workflow, StringBuffer stringBuffer, MapSqlParameterSource sps){
        if (!Func.checkNull(workflow.getLinkId())){
            stringBuffer.append(" link_id=:link_id ,");
            sps.addValue("link_id",workflow.getLinkId());
        }
        if (!Func.checkNull(String.valueOf(workflow.getType()))){
            stringBuffer.append(" type=:type ,");
            sps.addValue("type",workflow.getType());
        }
        if (!Func.checkNull(workflow.getTitle())){
            stringBuffer.append(" title=:title ,");
            sps.addValue("title",workflow.getTitle());
        }
        if (!Func.checkNull(String.valueOf(workflow.getStatus()))){
            stringBuffer.append(" status=:status ,");
            sps.addValue("status",workflow.getStatus());
        }
        if (!Func.checkNull(workflow.getRemark())){
            stringBuffer.append(" remark=:remark ,");
            sps.addValue("remark",workflow.getRemark());
        }
        if (!Func.checkNull(workflow.getCreateId())){
            stringBuffer.append(" create_id=:create_id ,");
            sps.addValue("create_id",workflow.getCreateId());
        }
        if (!Func.checkNull(workflow.getWorkflowRemark())){
            stringBuffer.append(" workflow_remark=:workflow_remark ,");
            sps.addValue("workflow_remark",workflow.getWorkflowRemark());
        }
        if (!Func.checkNull(workflow.getWorkflowName())){
            stringBuffer.append(" workflow_name=:workflow_name ,");
            sps.addValue("workflow_name",workflow.getWorkflowName());
        }
        if (!Func.checkNull(workflow.getWorkflowDate())){
            stringBuffer.append(" workflow_date=:workflow_date ");
            sps.addValue("workflow_date",workflow.getWorkflowDate());
        }
    }
}