Esempio n. 1
0
        /// <summary>
        /// 删除房间
        /// </summary>
        /// <param name="id">房间ID</param>
        /// <returns>是否删除成功</returns>
        public bool DeleteRoom(string id)
        {
            string sql = string.Format(@"DELETE Room
                WHERE RoomId='{0}'", id);

            return((Convert.ToInt32(DBHerper.NonQuery(sql)) != 0)? true : false);
        }
        /// <summary>
        /// 退房信息
        /// </summary>
        /// <param name="id">记录编号</param>
        /// <returns>退房信息对象</returns>
        public CheckOutInfo CheckOutInfo(string id)
        {
            CheckOutInfo  outInfo = new DAL.CheckOutInfo();
            string        sql     = string.Format(@"SELECT [Deposit],DATEDIFF(DAY,[ArrivalDate],
                GETDATE())*(SELECT [TypePrice] FROM [dbo].[RoomType] WHERE [TypeId]=
                (SELECT [RoomTypeId] FROM [dbo].[Room] WHERE [RoomId]=(select [RoomId] from [dbo].[GuestRecord] where [ID]={0}))),
                (SELECT SUM([Num]*[Price])
                FROM [dbo].[FoodOrder] F,[dbo].[FoodList] L
                WHERE (F.FoodId=L.FoodId) AND [RoomId]=(SELECT [RoomId] FROM [dbo].[GuestRecord] WHERE [ID]={0}) 
                AND [OrderDate]>=(SELECT [ArrivalDate] FROM [dbo].[GuestRecord] WHERE [ID]={0}))
                FROM [dbo].[GuestRecord]
                WHERE [RoomId]=(SELECT [RoomId] FROM [dbo].[GuestRecord] WHERE [ID]={0}) AND [LeaveDate] IS NULL AND [ID]={0}", id);
            SqlDataReader r       = DBHerper.Reader(sql);

            if (r.Read())
            {
                outInfo.Deposit   = Convert.ToDouble(r[0]);
                outInfo.RoomTotal = Convert.ToDouble(r[1]);
                if (r[2].ToString().Equals(string.Empty))
                {
                    outInfo.FoodTotal = 0;
                }
                else
                {
                    outInfo.FoodTotal = Convert.ToDouble(r[2]);
                }
                outInfo.TotalAmount = outInfo.FoodTotal + outInfo.RoomTotal - outInfo.Deposit;
            }
            r.Close();
            return(outInfo);
        }
Esempio n. 3
0
        /// <summary>
        /// 判断该房间是有有未结账的订单
        /// </summary>
        /// <param name="id">房间ID</param>
        /// <returns>是否有</returns>
        public bool JudgeRoom(string id)
        {
            string sql = string.Format(@"SELECT count(*)
                FROM GuestRecord
                WHERE RoomId='{0}'", id);

            return((Convert.ToInt32(DBHerper.Scalar(sql)) != 0) ? true : false);
        }
Esempio n. 4
0
        /// <summary>
        /// 修改房间信息
        /// </summary>
        /// <param name="room">房间信息对象</param>
        /// <returns>是否修改成功</returns>
        public bool UpdateRoomInfo(Room room)
        {
            string sql = string.Format(@"UPDATE [dbo].[Room]
                SET [RoomTypeId]='{0}', [RoomStateId]='{1}'
                WHERE [RoomId]='{2}'", room.RoomTypeId, room.RoomStateId, room.RoomId);

            return((DBHerper.NonQuery(sql) == 1) ? true : false);
        }
Esempio n. 5
0
        /// <summary>
        /// 增加商品
        /// </summary>
        /// <param name="food">商品对象</param>
        /// <returns>增加的商品编号</returns>
        public object AddFoods(FoodList food)
        {
            string sql   = string.Format(@"INSERT [dbo].[FoodList] ([FoodName], [Unit], [Price])
                VALUES('{0}','{1}','{2}') SELECT @@IDENTITY", food.FoodName, food.Unit, food.Price);
            object judge = DBHerper.Scalar(sql);

            return(judge);
        }
Esempio n. 6
0
        /// <summary>
        /// 修改房间类型
        /// </summary>
        /// <param name="type">type</param>
        /// <returns>是否修改成功</returns>
        public bool UpdateType(RoomType type)
        {
            string sql = string.Format(@"UPDATE [dbo].[RoomType]
                SET [TypeName]='{0}',[TypeWindow]='{1}',[BedNum]='{2}',[TypePrice]='{3}'
                WHERE [TypeId]='{4}'", type.TypeName, type.TypeWindow, type.BedNum, type.TypePrice, type.TypeId);

            return((DBHerper.NonQuery(sql) == 1) ? true : false);
        }
Esempio n. 7
0
        /// <summary>
        /// 新增房间类型
        /// </summary>
        /// <param name="type">房间类型对象</param>
        /// <returns>新增的类型编号</returns>
        public object AddType(RoomType type)
        {
            string sql = string.Format(@"INSERT [dbo].[RoomType] ([TypeName], [TypeWindow], [BedNum], [TypePrice])
                VALUES('{0}','{1}','{2}','{3}') SELECT @@IDENTITY", type.TypeName, type.TypeWindow, type.BedNum, type.TypePrice);
            object obj = DBHerper.Scalar(sql);

            return(obj);
        }
Esempio n. 8
0
        /// <summary>
        /// 删除房间类型
        /// </summary>
        /// <param name="id">id</param>
        /// <returns>是否删除成功</returns>
        public bool DeleteType(string id)
        {
            string sql   = string.Format(@"DELETE [dbo].[RoomType]
                WHERE [TypeId]='{0}'", id);
            object judge = DBHerper.NonQuery(sql);

            return((Convert.ToInt32(judge) != 0) ? true : false);
        }
        /// <summary>
        /// 获取房间状态
        /// </summary>
        /// <param name="roomId">房间编号</param>
        /// <returns>房间状态</returns>
        public int GetStatistics(string roomId)
        {
            string sql   = string.Format(@"SELECT [RoomStateId]
                FROM [dbo].[Room]
                WHERE [RoomId]='{0}'", roomId);
            int    judge = Convert.ToInt32(DBHerper.Scalar(sql));

            return(judge);
        }
Esempio n. 10
0
        /// <summary>
        /// 增加客户信息
        /// </summary>
        /// <param name="info">客户信息</param>
        /// <returns>客户编号</returns>
        public string AddGuestInfo(GuestInfo info)
        {
            string sql     = string.Format(@"INSERT [dbo].[GuestInfo]([Name], [CardNo], [Phone], [Sex])
                VALUES ('{0}','{1}','{2}','{3}')
                SELECT @@IDENTITY", info.Name, info.CardNo, info.Phone, info.Sex);
            string guestId = DBHerper.Scalar(sql).ToString();

            return(guestId);
        }
Esempio n. 11
0
        /// <summary>
        /// 判断类型的房间是否存在
        /// </summary>
        /// <param name="id">类型ID</param>
        /// <returns>是否存在</returns>
        public bool JudgeTypeId(string id)
        {
            string sql   = string.Format(@"SELECT COUNT(*)
                FROM [dbo].[Room]
                WHERE [RoomTypeId] ='{0}'", id);
            object judge = DBHerper.Scalar(sql);

            return((Convert.ToInt32(judge) != 0) ? true : false);
        }
Esempio n. 12
0
        /// <summary>
        /// 增加订单
        /// </summary>
        /// <param name="guest">客户记录对象</param>
        public void AddOrder(GuestRecord guest)
        {
            string sql = string.Format(@"INSERT [dbo].[GuestRecord] ( [GuestID], [Deposit], [ArrivalDate], [RoomId])
                VALUES ('{0}','{1}','{2}','{3}')
                UPDATE [dbo].[Room]
                SET [RoomStateId]=1
                WHERE [RoomId]='{3}'", guest.GuestID, guest.Deposit, guest.ArrivalDateStr, guest.RoomId);

            DBHerper.NonQuery(sql);
        }
        /// <summary>
        /// 单个退房
        /// </summary>
        /// <param name="RoomTotal">房款</param>
        /// <param name="FoodTotal">消费</param>
        /// <param name="id">房间号</param>
        public void SCheckOut(string RoomTotal, string FoodTotal, string id)
        {
            string sql = string.Format(@"UPDATE [dbo].[GuestRecord]
                SET [LeaveDate]=GETDATE(),[RoomTotal]='{0}',[FoodTotal]='{1}'
                WHERE [RoomId]='{2}'
                UPDATE [dbo].[Room]
                SET [RoomStateId]=2
                WHERE [RoomId]='{2}'", RoomTotal, FoodTotal, id);

            DBHerper.NonQuery(sql);
        }
Esempio n. 14
0
        /// <summary>
        /// 修改客户信息
        /// </summary>
        /// <param name="info">客户信息</param>
        /// <returns>客户编号</returns>
        public string UpdateGuestInfo(GuestInfo info)
        {
            string sql     = string.Format(@"UPDATE [dbo].[GuestInfo]
                SET [Name]='{0}',[Phone]='{1}',[Sex]='{2}'
                WHERE [CardNo]='{3}'
                SELECT [ID]
                FROM [dbo].[GuestInfo]
                WHERE [CardNo]='{3}'", info.Name, info.Phone, info.Sex, info.CardNo);
            string guestId = DBHerper.Scalar(sql).ToString();

            return(guestId);
        }
Esempio n. 15
0
        /// <summary>
        /// 查询全部房间类型名称
        /// </summary>
        /// <returns>房间类型集合</returns>
        public List <string> Querys()
        {
            List <string> s   = new List <string>();
            string        sql = "SELECT [TypeName] FROM [dbo].[RoomType] GROUP BY [TypeName]";
            SqlDataReader r   = DBHerper.Reader(sql);

            while (r.Read())
            {
                s.Add(r[0].ToString());
            }
            r.Close();
            return(s);
        }
Esempio n. 16
0
        /// <summary>
        /// 删除食品
        /// </summary>
        /// <param name="id">商品ID</param>
        /// <returns>是否删除成功</returns>
        public bool DeleteFoods(string id)
        {
            string sql   = string.Format(@"DELETE [dbo].[FoodList]
                WHERE [FoodId]='{0}'", id);
            int    judge = DBHerper.NonQuery(sql);

            if (judge == 0)
            {
                return(false);
            }
            else
            {
                return(true);
            }
        }
Esempio n. 17
0
        /// <summary>
        /// 添加树列表
        /// </summary>
        /// <param name="treeView">需要添加的控件</param>
        public void ShowTreeView(TreeView treeView)
        {
            string        all  = "全部";
            TreeNode      node = treeView.Nodes.Add(all);
            string        sql  = string.Format(@"SELECT TypeName FROM roomtype
            WHERE TypeWindow=1");
            SqlDataReader r    = DBHerper.Reader(sql);

            while (r.Read())
            {
                string a = r[0].ToString();
                node.Nodes.Add(a);
            }
            r.Close();
        }
Esempio n. 18
0
        /// <summary>
        /// 判断此食品ID是否有订单
        /// </summary>
        /// <param name="id">商品ID</param>
        /// <returns>是否有订单</returns>
        public bool JudgeFoodsOrders(string id)
        {
            string sql   = string.Format(@"SELECT COUNT(*)
                FROM [dbo].[FoodOrder]
                WHERE [FoodId]='{0}'", id);
            int    judge = Convert.ToInt32(DBHerper.Scalar(sql));

            if (judge != 0)
            {
                return(true);
            }
            else
            {
                return(false);
            }
        }
Esempio n. 19
0
        /// <summary>
        /// 修改商品
        /// </summary>
        /// <param name="food">商品对象</param>
        /// <returns>是否修改成功</returns>
        public bool UpdateFoods(FoodList food)
        {
            string sql   = string.Format(@"UPDATE  [dbo].[FoodList]
                SET [FoodName]='{0}',[Unit]='{1}',[Price]='{2}'
                WHERE [FoodId]='{3}'", food.FoodName, food.Unit, food.Price, food.FoodId);
            int    judge = Convert.ToInt32(DBHerper.NonQuery(sql));

            if (judge != 0)
            {
                return(true);
            }
            else
            {
                return(false);
            }
        }
Esempio n. 20
0
        /// <summary>
        /// 通过身份证判断客户是否存在
        /// </summary>
        /// <param name="id">身份证</param>
        /// <returns>是否存在</returns>
        public bool CheckExist(string id)
        {
            string sql   = string.Format(@"SELECT COUNT(*)
                FROM [dbo].[GuestInfo]
                WHERE [CardNo]='{0}'", id);
            int    judge = Convert.ToInt32(DBHerper.Scalar(sql));

            if (judge != 0)
            {
                return(true);
            }
            else
            {
                return(false);
            }
        }
Esempio n. 21
0
        /// <summary>
        /// 增加房间信息
        /// </summary>
        /// <param name="room">房间对象</param>
        /// <returns>是否增加成功</returns>
        public bool AddRoom(Room room)
        {
            string sql = string.Format(@"SELECT count(*)
                FROM Room
                WHERE RoomId='{0}'", room.RoomId);

            if (Convert.ToInt32(DBHerper.Scalar(sql)) != 0)
            {
                return(false);
            }
            else
            {
                sql = string.Format(@"INSERT Room VALUES ('{0}','{1}','{2}')", room.RoomId, room.RoomTypeId, room.RoomStateId);
                return((Convert.ToInt32(DBHerper.NonQuery(sql)) == 1) ? true : false);
            }
        }
Esempio n. 22
0
        /// <summary>
        /// 通过房间ID查询房间类型ID
        /// </summary>
        /// <param name="roomId">房间ID</param>
        /// <returns>房间信息对象</returns>
        public RoomInfo GetRoomTypeId(string roomId)
        {
            RoomInfo      room = new RoomInfo();
            string        sql  = string.Format(@"SELECT [RoomTypeId],[RoomStateId]
                FROM [dbo].[Room]
                WHERE [RoomId]='{0}'", roomId);
            SqlDataReader r    = DBHerper.Reader(sql);

            if (r.Read())
            {
                room.RoomRype  = r[0].ToString();
                room.RoomState = r[1].ToString();
            }
            r.Close();
            return(room);
        }
        /// <summary>
        /// 查询单条订单
        /// </summary>
        /// <param name="roomid">房间编号</param>
        /// <returns>订单对象 </returns>
        public GuestRecord SLoadOrder(string roomid)
        {
            GuestRecord   record = null;
            string        sql    = string.Format(@"SELECT [RoomId],[Name],[CardNo],[Phone],CASE WHEN [Sex]=0 THEN '男' WHEN [Sex]=1 THEN '女' END,[Deposit],[ArrivalDate],R.ID
                FROM [dbo].[GuestInfo] G,[dbo].[GuestRecord] R
                WHERE G.ID=R.GuestID AND [LeaveDate] IS  NULL and [RoomId]={0}", roomid);
            SqlDataReader r      = DBHerper.Reader(sql);

            if (r.Read())
            {
                record = new GuestRecord()
                {
                    RoomId = Convert.ToInt32(r[0]), Name = r[1].ToString(), CardNo = r[2].ToString(), Phone = r[3].ToString(), SexStr = r[4].ToString(), Deposit = Convert.ToDouble(r[5]), ArrivalDate = Convert.ToDateTime(r[6]), ID = Convert.ToInt32(r[7])
                };
            }
            r.Close();
            return(record);
        }
Esempio n. 24
0
        /// <summary>
        /// 获取食品信息
        /// </summary>
        /// <returns>商品集合</returns>
        public List <FoodList> GetFood()
        {
            List <FoodList> f   = new List <FoodList>();
            string          sql = string.Format(@"SELECT [FoodId], [FoodName], [Unit], [Price]
                FROM [dbo].[FoodList]");
            SqlDataReader   r   = DBHerper.Reader(sql);

            while (r.Read())
            {
                FoodList fl = new FoodList()
                {
                    FoodId = Convert.ToInt32(r[0]), FoodName = r[1].ToString(), Unit = r[2].ToString(), Price = Convert.ToDouble(r[3])
                };
                f.Add(fl);
            }
            r.Close();
            return(f);
        }
Esempio n. 25
0
        /// <summary>
        /// 获取全部房间类型
        /// </summary>
        /// <returns>房间类型列表</returns>
        public List <RoomTypeList> GetTypeList()
        {
            List <RoomTypeList> list = new List <RoomTypeList>();
            string        sql        = string.Format(@"SELECT TypeName+CASE  WHEN TypeWindow=0 THEN ' | 无窗' WHEN TypeWindow=1 THEN ' | 有窗' END,TypeId
                FROM RoomType");
            SqlDataReader r          = DBHerper.Reader(sql);

            while (r.Read())
            {
                RoomTypeList l = new RoomTypeList()
                {
                    TypeName = r[0].ToString(), TypeID = r[1].ToString()
                };
                list.Add(l);
            }
            r.Close();
            return(list);
        }
Esempio n. 26
0
        /// <summary>
        /// 修改房间信息
        /// </summary>
        /// <param name="room">房间对象</param>
        /// <param name="id">要修改的id</param>
        /// <returns>是否修改成功</returns>
        public bool UpdateRoom(Room room, string id)
        {
            string sql = string.Format(@"SELECT count(*)
                FROM Room
                WHERE RoomId !='{0}' and RoomId='{1}'", id, room.RoomId);

            if (Convert.ToInt32(DBHerper.Scalar(sql)) != 0)
            {
                return(false);
            }
            else
            {
                sql = string.Format(@"UPDATE Room
                    SET RoomId='{0}',RoomTypeId='{1}',RoomStateId='{2}'
                    WHERE RoomId='{3}'", room.RoomId, room.RoomTypeId, room.RoomStateId, id);
                return((Convert.ToInt32(DBHerper.NonQuery(sql)) == 1) ? true : false);
            }
        }
Esempio n. 27
0
        /// <summary>
        /// 检查用户名和密码是否正确
        /// </summary>
        /// <param name="admin">管理员对象</param>
        /// <returns>是否正确</returns>
        public bool Check(Admin admin)
        {
            Encrypt encrypt = new Encrypt();
            string  pwd     = encrypt.EncryotPwd(admin.Pwd);
            string  sql     = string.Format(@"SELECT COUNT(*)
                FROM [dbo].[Admin]
                WHERE [Admins]='{0}' AND [Pwd]='{1}'", admin.Admins, pwd);

            try
            {
                int judge = Convert.ToInt32(DBHerper.Scalar(sql));
                return((judge == 1) ? true : false);
            }
            catch (Exception e)
            {
                System.Windows.Forms.MessageBox.Show(e.Message);
                return(false);
            }
        }
Esempio n. 28
0
        /// <summary>
        /// 修改密码
        /// </summary>
        /// <param name="admin">管理员对象</param>
        /// <returns>修改是否成功</returns>
        public bool UpadtePwd(Admin admin)
        {
            Encrypt encrypt = new Encrypt();
            string  pwd     = encrypt.EncryotPwd(admin.Pwd);
            string  sql     = string.Format(@"UPDATE [dbo].[Admin]
                SET [Pwd]='{0}'
                WHERE [Admins]='{1}'", pwd, admin.Admins);

            try
            {
                int judge = DBHerper.NonQuery(sql);
                return((judge == 1) ? true : false);
            }
            catch (Exception e)
            {
                System.Windows.Forms.MessageBox.Show(e.Message);
                return(false);
            }
        }
        /// <summary>
        /// 加载记录
        /// </summary>
        /// <returns>记录集合</returns>
        public List <GuestRecord> LoadRecord()
        {
            List <GuestRecord> list = new List <GuestRecord>();
            string             sql  = string.Format(@"SELECT [RoomId],[Name],[CardNo],[Phone],CASE WHEN [Sex]=0 THEN '男' WHEN [Sex]=1 THEN '女' END,[Deposit],[ArrivalDate],[LeaveDate],[RoomTotal],[FoodTotal],R.[ID]
                FROM [dbo].[GuestInfo] G,[dbo].[GuestRecord] R
                WHERE G.ID=R.GuestID AND [LeaveDate] IS NOT NULL");
            SqlDataReader      r    = DBHerper.Reader(sql);

            while (r.Read())
            {
                GuestRecord g = new GuestRecord()
                {
                    RoomId = Convert.ToInt32(r[0]), Name = r[1].ToString(), CardNo = r[2].ToString(), Phone = r[3].ToString(), SexStr = r[4].ToString(), Deposit = Convert.ToDouble(r[5]), ArrivalDate = Convert.ToDateTime(r[6]), LeaveDate = Convert.ToDateTime(r[7]), RoomTotal = Convert.ToDouble(r[8]), FoodTotal = Convert.ToDouble(r[9]), ID = Convert.ToInt32(r[10])
                };
                list.Add(g);
            }
            r.Close();
            return(list);
        }
        /// <summary>
        /// 筛选订单-按房间编号筛选
        /// </summary>
        /// <param name="RoomID">房间编号</param>
        /// <returns>客户记录对象集合</returns>
        public List <GuestRecord> ScreenOrder(int RoomID)
        {
            List <GuestRecord> list = new List <GuestRecord>();
            string             sql  = string.Format(@"SELECT [RoomId],[Name],[CardNo],[Phone],CASE WHEN [Sex]=0 THEN '男' WHEN [Sex]=1 THEN '女' END,[Deposit],[ArrivalDate],R.ID
                FROM [dbo].[GuestInfo] G,[dbo].[GuestRecord] R
                WHERE G.ID=R.GuestID AND [LeaveDate] IS  NULL AND [RoomId] LIKE '%{0}%'", RoomID);
            SqlDataReader      r    = DBHerper.Reader(sql);

            while (r.Read())
            {
                GuestRecord record = new GuestRecord()
                {
                    RoomId = Convert.ToInt32(r[0]), Name = r[1].ToString(), CardNo = r[2].ToString(), Phone = r[3].ToString(), SexStr = r[4].ToString(), Deposit = Convert.ToDouble(r[5]), ArrivalDate = Convert.ToDateTime(r[6]), ID = Convert.ToInt32(r[7])
                };
                list.Add(record);
            }
            r.Close();
            return(list);
        }