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

package com.happy.dao.impl;

import com.happy.Model.*;
import com.happy.Model.app.Around_product;
import com.happy.Model.app.Arounds;
import com.happy.Model.app.News;
import com.happy.Model.app.Tongji;
import com.happy.common.http.Get_airticle;
import com.happy.common.model.airticle.Item_content;
import com.happy.dao.AppDao;
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("AppDao")
public class AppImplDao implements AppDao {

    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public NamedParameterJdbcTemplate getNamedParameterJdbcTemplate() {
        return namedParameterJdbcTemplate;
    }

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

    // 登录
    public Admin login(String admin_name,String password) {
        String sql = "select * from `admin` where admin_name=:admin_name and password=:password ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("admin_name",admin_name);
        sps.addValue("password",password);
        List<Admin> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<>(Admin.class));
        if (list != null && list.size() > 0) {
            return list.get(0);
        }
        return null;
    }

    public Admin queryByOpenid(String openid) {
        String sql = "select * from `admin` where openid=:openid ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("openid",openid);
        List<Admin> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<>(Admin.class));
        if (list != null && list.size() > 0) {
            return list.get(0);
        }
        return null;
    }

    public Admin queryByNameAndOpenid(String admin_name,String openid) {
        String sql = "select * from `admin` where admin_name=:admin_name and openid=:openid ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("admin_name",admin_name);
        sps.addValue("openid",openid);
        List<Admin> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<>(Admin.class));
        if (list != null && list.size() > 0) {
            return list.get(0);
        }
        return null;
    }

    public int updateOpenid(String openid,String admin_name){
        String sql = "update `admin` set openid=:openid where admin_name=:admin_name ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("openid",openid);
        sps.addValue("admin_name",admin_name);
        int num = 0;
        try{
            num = namedParameterJdbcTemplate.update(sql, sps);
        }
        catch(Exception e){
            e.printStackTrace();
        }
        return num;
    }

    public int updateOpenidNull(String openid){
        String sql = "update `admin` set openid=null where openid=:openid ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("openid",openid);
        int num = 0;
        try{
            num = namedParameterJdbcTemplate.update(sql, sps);
        }
        catch(Exception e){
            e.printStackTrace();
        }
        return num;
    }

    /**********************
     * ********商户绑定**********
     * ********************/
    public AdminManager login_ma(String admin_name, String password) {
        String sql = "select * from `admin_manager` where admin_name=:admin_name and password=:password ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("admin_name",admin_name);
        sps.addValue("password",password);
        List<AdminManager> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<>(AdminManager.class));
        if (list != null && list.size() > 0) {
            return list.get(0);
        }
        return null;
    }

    public AdminManager queryMaByOpenid(String openid) {
        String sql = "select * from `admin_manager` where openid=:openid ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("openid",openid);
        List<AdminManager> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<>(AdminManager.class));
        if (list != null && list.size() > 0) {
            return list.get(0);
        }
        return null;
    }

    public AdminManager queryMaByNameAndOpenid(String admin_name,String openid) {
        String sql = "select * from `admin_manager` where admin_name=:admin_name and openid=:openid ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("admin_name",admin_name);
        sps.addValue("openid",openid);
        List<AdminManager> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<>(AdminManager.class));
        if (list != null && list.size() > 0) {
            return list.get(0);
        }
        return null;
    }

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

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

    /** ================================资讯============================= **/
    public int insertNews(News news){
        String sql = "insert into `news`(media_id,title,author,digest,content,content_source_url,url,update_time,first_img,state,is_top) values(:media_id,:title,:author,:digest,:content,:content_source_url,:url,:update_time,:first_img,:state,:is_top) ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("media_id",news.getMedia_id());
        sps.addValue("title", news.getTitle());
        sps.addValue("author", news.getAuthor());
        sps.addValue("digest", news.getDigest());
        sps.addValue("content", news.getContent());
        sps.addValue("content_source_url", news.getContent_source_url());
        sps.addValue("url", news.getUrl());
        sps.addValue("update_time", news.getUpdate_time());
        sps.addValue("first_img",news.getFirst_img());
        sps.addValue("state",news.getState());
        sps.addValue("is_top",news.getIs_top());
        int num = 0;
        try{
            num = namedParameterJdbcTemplate.update(sql, sps);
        }
        catch(Exception e){
            e.printStackTrace();
        }
        return num;
    }

    public int delNews(int id){
        String sql = "update `news` set state=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;
    }

    public int updateNews(News news){
        String sql = "update `news` set title=:title,author=:author,digest=:digest,content=:content where id=:id ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("title", news.getTitle());
        sps.addValue("author", news.getAuthor());
        sps.addValue("digest", news.getDigest());
        sps.addValue("content", news.getContent());
        sps.addValue("id",news.getId());
        int num = 0;
        try{
            num = namedParameterJdbcTemplate.update(sql, sps);
        }
        catch(Exception e){
            e.printStackTrace();
        }
        return num;
    }

    public int updateTop(News news){
        String sql = "update `news` set is_top=:is_top,top_time=:top_time where id=:id ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("is_top", news.getIs_top());
        sps.addValue("top_time", news.getTop_time());
        sps.addValue("id",news.getId());
        int num = 0;
        try{
            num = namedParameterJdbcTemplate.update(sql, sps);
        }
        catch(Exception e){
            e.printStackTrace();
        }
        return num;
    }

    public News queryByTit(String title){
        String sql = "select * from `news` where title=:title ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("title", title);
        List<News> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<>(News.class));
        if (list != null && list.size() > 0) {
            return list.get(0);
        }
        return null;
    }

    public News queryById(int id){
        String sql = "select * from `news` where id=:id ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("id", id);
        List<News> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<>(News.class));
        if (list != null && list.size() > 0) {
            return list.get(0);
        }
        return null;
    }

    public List<News> queryNewPage(int page, int rows, String sqlx){
        int start = (page - 1) * rows;// 每页的起始下标
        String sql = "select * from `news` where state=1 "+sqlx+" limit :start,:rows ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("start", start);
        sps.addValue("rows", rows);
        List<News> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<>(News.class));
        if (list != null && list.size() > 0) {
            return list;
        }
        return null;
    }

    // 查询用户表中的总记录数
    public int queryNewTotal(String sqlx) {
        String sql = "select count(*) from `news` where state=1 "+sqlx;
        MapSqlParameterSource sps = new MapSqlParameterSource();
        return namedParameterJdbcTemplate.queryForInt(sql, sps);
    }

    /** ==============================周边=================================== **/
    public int insertRound(Arounds arounds){
        String sql = "insert into `around`(rtype,rtown,rname,rphone,radress,cnum,detail,first_img,show_video,detail_img,jingwei) values(:rtype,:rtown,:rname,:rphone,:radress,:cnum,:detail,:first_img,:show_video,:detail_img,:jingwei) ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("rtype", arounds.getRtype());
        sps.addValue("rtown", arounds.getRtown());
        sps.addValue("rname", arounds.getRname());
        sps.addValue("rphone", arounds.getRphone());
        sps.addValue("radress", arounds.getRadress());
        sps.addValue("cnum", arounds.getCnum());
        sps.addValue("detail", arounds.getDetail());
        sps.addValue("first_img", arounds.getFirst_img());
        sps.addValue("show_video", arounds.getShow_video());
        sps.addValue("detail_img", arounds.getDetail_img());
        sps.addValue("jingwei",arounds.getJingwei());
        int num = 0;
        try{
            num = namedParameterJdbcTemplate.update(sql, sps);
        }
        catch(Exception e){
            e.printStackTrace();
        }
        return num;
    }

    public int updateRoundById(Arounds arounds){
        String sql = "update `around` set rtype=:rtype,rtown=:rtown,rname=:rname,rphone=:rphone,radress=:radress,detail=:detail,first_img=:first_img,show_video=:show_video,detail_img=:detail_img,jingwei=:jingwei where id=:id ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("rtype", arounds.getRtype());
        sps.addValue("rtown", arounds.getRtown());
        sps.addValue("rname", arounds.getRname());
        sps.addValue("rphone", arounds.getRphone());
        sps.addValue("radress", arounds.getRadress());
        sps.addValue("detail", arounds.getDetail());
        sps.addValue("first_img", arounds.getFirst_img());
        sps.addValue("show_video", arounds.getShow_video());
        sps.addValue("detail_img", arounds.getDetail_img());
        sps.addValue("jingwei",arounds.getJingwei());
        sps.addValue("id", arounds.getId());
        int num = 0;
        try{
            num = namedParameterJdbcTemplate.update(sql, sps);
        }
        catch(Exception e){
            e.printStackTrace();
        }
        return num;
    }

    public int updateRoundCountById(Arounds arounds){
        String sql = "update `around` set cnum=:cnum where id=:id ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("cnum", arounds.getCnum());
        sps.addValue("id", arounds.getId());
        int num = 0;
        try{
            num = namedParameterJdbcTemplate.update(sql, sps);
        }
        catch(Exception e){
            e.printStackTrace();
        }
        return num;
    }

    public int delAround(int id){
        String sql = "delete from `around` 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;
    }

    public Arounds queryAroundById(int id){
        String sql = "select * from `around` where id=:id ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("id", id);
        List<Arounds> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<>(Arounds.class));
        if (list != null && list.size() > 0) {
            return list.get(0);
        }
        return null;
    }

    public Arounds queryAroundByName(String rname){
        String sql = "select * from `around` where rname=:rname ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("rname", rname);
        List<Arounds> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<>(Arounds.class));
        if (list != null && list.size() > 0) {
            return list.get(0);
        }
        return null;
    }

    public List<Arounds> queryAroundPage(String sqlx, int page, int rows){
        int start = (page - 1) * rows;// 每页的起始下标
        String sql = "select * from `around` where 1=1 "+sqlx+" order by id desc limit :start,:rows ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("start", start);
        sps.addValue("rows", rows);
        List<Arounds> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<>(Arounds.class));
        if (list != null && list.size() > 0) {
            return list;
        }
        return null;
    }

    public int queryAroundTotal(String sqlx) {
        String sql = "select count(*) from `around` where 1=1 "+sqlx;
        MapSqlParameterSource sps = new MapSqlParameterSource();
        return namedParameterJdbcTemplate.queryForInt(sql, sps);
    }

    /** =========================产品============================== **/
    public int insertProduct(Around_product around_product){
        String sql = "insert into `around_product`(aid,product_name,product_desc,price) values(:aid,:product_name,:product_desc,:price) ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("aid", around_product.getAid());
        sps.addValue("product_name", around_product.getProduct_name());
        sps.addValue("product_desc", around_product.getProduct_desc());
        sps.addValue("price", around_product.getPrice());
        int num = 0;
        try{
            num = namedParameterJdbcTemplate.update(sql, sps);
        }
        catch(Exception e){
            e.printStackTrace();
        }
        return num;
    }

    public int updateProductById(Around_product around_product){
        String sql = "update `around_product` set product_name=:product_name,product_desc=:product_desc,price=:price where id=:id ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("product_name", around_product.getProduct_name());
        sps.addValue("product_desc", around_product.getProduct_desc());
        sps.addValue("price", around_product.getPrice());
        sps.addValue("id", around_product.getId());
        int num = 0;
        try{
            num = namedParameterJdbcTemplate.update(sql, sps);
        }
        catch(Exception e){
            e.printStackTrace();
        }
        return num;
    }

    public int delAroundProduct(int id){
        String sql = "delete from `around_product` 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;
    }

    public Around_product queryAPById(int id){
        String sql = "select * from `around_product` where id=:id ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("id", id);
        List<Around_product> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<>(Around_product.class));
        if (list != null && list.size() > 0) {
            return list.get(0);
        }
        return null;
    }

    public Around_product queryAPByName(int aid,String product_name){
        String sql = "select * from `around_product` where aid=:aid and product_name=:product_name ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("aid", aid);
        sps.addValue("product_name",product_name);
        List<Around_product> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<>(Around_product.class));
        if (list != null && list.size() > 0) {
            return list.get(0);
        }
        return null;
    }

    public List<Around_product> queryAPPage(int aid,String sqlx, int page, int rows){
        int start = (page - 1) * rows;// 每页的起始下标
        String sql = "select * from `around_product` where aid=:aid "+sqlx+" order by id desc limit :start,:rows ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("aid", aid);
        sps.addValue("start", start);
        sps.addValue("rows", rows);
        List<Around_product> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<>(Around_product.class));
        if (list != null && list.size() > 0) {
            return list;
        }
        return null;
    }

    public int queryAPTotal(int aid,String sqlx) {
        String sql = "select count(*) from `around_product` where aid=:aid "+sqlx;
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("aid", aid);
        return namedParameterJdbcTemplate.queryForInt(sql, sps);
    }

    public List<Around_product> queryAP(int aid,String sqlx){
        String sql = "select * from `around_product` where aid=:aid "+sqlx+" order by id desc ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("aid", aid);
        List<Around_product> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<>(Around_product.class));
        if (list != null && list.size() > 0) {
            return list;
        }
        return null;
    }

    public List<Hotel> queryMs(String town){
        String sql = "select a.*,b.name from (select a.*,b.hotel_township from `hotel` a left join `admin_manager` b  on a.`manager_id`=b.`manager_id` where a.`status`=1 ) a left join `hotel_dict` b on a.hotel_township=b.`id` where b.name=:town ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("town", town);
        List<Hotel> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<>(Hotel.class));
        if (list != null && list.size() > 0) {
            return list;
        }
        return null;
    }

}