using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using DBaoBookingManagement.Entity;

namespace DBaoBookingManagement.DataAccess
{
    /// <summary>
    /// 對Message表進行操作的類
    /// </summary>
    public class MessageAccessor:DataAccessor
    {
        //根據酒店ID查詢留言
        public DataTable QueryByHotelId(int hotelId)
        {
            string sql = "select * from Message where HotelId=" + hotelId+" order by MessageTime desc";
            try
            {
                return base.Query(sql);
            }
            catch (Exception ex)
            {
                throw new Exception("根據酒店ID查詢留言時出現錯誤：" + ex.Message);
            }
        }

        //根據ID查詢留言
        public DataTable QueryById(int Id)
        {
            string sql = "select * from Message where MessageId=" + Id;
            try
            {
                return base.Query(sql);
            }
            catch (Exception ex)
            {
                throw new Exception("根據ID查詢留言時出現錯誤：" + ex.Message);
            }
        }

        //查詢所有留言
        public DataTable QueryAll()
        {
            string sql = "select * from Message order by MessageTime desc";
            try
            {
                return base.Query(sql);
            }
            catch (Exception ex)
            {
                throw new Exception("查詢所有留言時出現錯誤：" + ex.Message);
            }
        }

        //插入留言
        public bool Insert(Message entity)
        {
            double staff = entity.Staff;
            double service = entity.Service;
            double clean = entity.Clean;
            double comfortable = entity.Comfortable;
            double worth = entity.Worth;
            string goods = entity.Goods;
            string bads = entity.Bads;
            int touristTypeId = entity.TouristTypeId;
            DateTime messageTime = entity.MessageTime;
            int hotelId = entity.HotelId;
            string sql="insert into Message(Staff,Service,Clean,Comfortable,Worth,Goods,Bads,TouristTypeId,MessageTime,HotelId) values("+
                staff+","+service+","+clean+","+comfortable+","+worth+",'"+goods+"','"+bads+"',"+touristTypeId+",'"+messageTime+"',"+hotelId+")";
            try
            {
                return base.ExecuteSqlNoneQuery(sql);
            }
            catch (Exception ex)
            {
                throw new Exception("插入留言時出現錯誤：" + ex.Message);
            }
        }

        //根據留言ID刪除留言
        public bool DeleteById(int id)
        {
            string sql = "delete Message where MessageId=" + id;
            try
            {
                return base.ExecuteSqlNoneQuery(sql);
            }
            catch (Exception ex)
            {
                throw new Exception("根據留言ID刪除留言時出現錯誤：" + ex.Message);
            }
        }

        //修改留言
        public bool Update(Message entity)
        {
            int id = entity.MessageId;
            double staff = entity.Staff;
            double service = entity.Service;
            double clean = entity.Clean;
            double comfortable = entity.Comfortable;
            double worth = entity.Worth;
            string goods = entity.Goods;
            string bads = entity.Bads;
            int touristTypeId = entity.TouristTypeId;
            DateTime messageTime = entity.MessageTime;
            int hotelId = entity.HotelId;
            string sql = "update Message set Staff=" + staff + ",Service=" + service + ",Clean=" +
                clean + ",Comfortable=" + comfortable + ",Worth=" + worth + ",Goods='" + goods + "'," +
                "Bads='" + bads + "',TouristTypeId=" + touristTypeId +
                ",MessageTime='"+messageTime+"',HotelId="+hotelId+" where MessageId=" + id;
            try
            {
                return base.ExecuteSqlNoneQuery(sql);
            }
            catch (Exception ex)
            {
                throw new Exception("修改留言時出現錯誤：" + ex.Message);
            }
        }
    }
}