Разработка приложения для работы с базой данных «Гостиница»

 

 

using System.Data.OleDb;

using Microsoft.VisualBasic.CompilerServices;

 

namespace HBRS

{

public partial class frmLogin

{

public frmLogin()

{

InitializeComponent();

 

}

 

        #region Default Instance

 

        private static frmLogin defaultInstance;

 

        public static frmLogin Default

        {

            get

            {

                if (defaultInstance == null)

                {

                    defaultInstance = new frmLogin();

                    defaultInstance.FormClosed += new FormClosedEventHandler(defaultInstance_FormClosed);

                }

 

                return defaultInstance;

            }

        }

 

        static void defaultInstance_FormClosed(object sender, FormClosedEventArgs e)

        {

            defaultInstance = null;

        }

 

        #endregion

public void OK_Click(System.Object sender, System.EventArgs e)

{

//Выполняется вход в систему.

if (UsernameTextBox.Text.Trim() == "" || PasswordTextBox.Text.Trim() == "")

{

                Interaction.MsgBox("Пожалуйста заполните поля!", Constants.vbInformation, "Вход в систему");

}

else

{

                //Вводится логи и пароль.

Module1.con.Open();

var sql = "SELECT * FROM tblUser WHERE username = \'" + Module1.SafeSqlLiteral(UsernameTextBox.Text, 2) + "\' AND password = \'" + Module1.SafeSqlLiteral(PasswordTextBox.Text, 2) + "\'";

 

var cmd = new OleDbCommand(sql, Module1.con);

OleDbDataReader dr = cmd.ExecuteReader();

 

try

{

if (dr.Read() == false)

{

                        //Если логи и пароль введен не верно, то появляется окно.

                        Interaction.MsgBox("В доступе отказано!", Constants.vbCritical, "Вход в систему");

}

else

{

                        //Если логи и пароль введен правильно, то выполяется вход в систему.

                        Interaction.MsgBox("Доступ разрешён!", Constants.vbInformation, "Вход в систему");

frmMain.Default.status.Items[0].Text = "Login as : " + UsernameTextBox.Text.Trim();

DateTime datenow = DateTime.Now;

frmMain.Default.status.Items[2].Text = "Date and Time : " + datenow.ToString("MMMM dd, yyyy") + " " + DateAndTime.TimeOfDay;

Module1.con.Close();

this.Hide();

frmMain.Default.ShowDialog();

}

}

catch (Exception ex)

{

MessageBox.Show(ex.Message);

 

}

 

Module1.con.Close();

}

 

}

 

public void Cancel_Click(System.Object sender, System.EventArgs e)

{

this.Close();

ProjectData.EndApp();

}

 

}

 

Форма Номер.

using System.Collections.Generic;

using System.Data;

using System.Drawing;

using System.Diagnostics;

using Microsoft.VisualBasic;

using System.Linq;

using System;

using System.Collections;

using System.Xml.Linq;

using System.Windows.Forms;

 

 

using System.Data.OleDb;

 

namespace HBRS

{

public partial class frmRoom

{

public frmRoom()

{

InitializeComponent();

 

 

}

 

#region Default Instance

 

private static frmRoom defaultInstance;

 

/// <summary>

/// </summary>

public static frmRoom Default

{

get

{

if (defaultInstance == null)

{

defaultInstance = new frmRoom();

defaultInstance.FormClosed += new FormClosedEventHandler(defaultInstance_FormClosed);

}

 

return defaultInstance;

}

}

 

static void defaultInstance_FormClosed(object sender, FormClosedEventArgs e)

{

defaultInstance = null;

}

 

#endregion

        //Иницилизация ID

int id;

public void frmRoom_Load(System.Object sender, System.EventArgs e)

{

TabControl1.SelectTab(0);

display_room();

}

private void display_room()

{

            //Подключение к БД

Module1.con.Open();

            //Создание таблицы

DataTable Dt = new DataTable("tblRoom");

OleDbDataAdapter rs = default(OleDbDataAdapter);

//Выбирает записи из таблицы

rs = new OleDbDataAdapter("Select * from tblRoom", Module1.con);

//Идет заполнение  таблицы

rs.Fill(Dt);

int indx = default(int);

lvRoom.Items.Clear();

for (indx = 0; indx <= Dt.Rows.Count - 1; indx++)

{

ListViewItem lv = new ListViewItem();

lv.Text = (string) (Dt.Rows[indx]["ID"]).ToString();

lv.SubItems.Add(Dt.Rows[indx]["RoomNumber"].ToString());

lv.SubItems.Add(Dt.Rows[indx]["RoomType"].ToString());

lv.SubItems.Add(Dt.Rows[indx]["RoomRate"].ToString());

lv.SubItems.Add(Dt.Rows[indx]["NoOfOccupancy"].ToString());

lvRoom.Items.Add(lv);

}

rs.Dispose();

Module1.con.Close();

}

 

public void bttnCancel_Click(System.Object sender, System.EventArgs e)

{

            //Выполняет очистку

txtID.Clear();

txtRoomType.Clear();

txtRoomRate.Clear();

txtNoOfOccupancy.Clear();

bttnSave.Text = "&Сохранить";

}

 

public void bttnSave_Click(System.Object sender, System.EventArgs e)

{

            //Выполняет сохранение

Module1.con.Open();

string num = txtID.Text.Trim();

string type = txtRoomType.Text.Trim();

string rate = txtRoomRate.Text.Trim();

string occupancy = txtNoOfOccupancy.Text.Trim();

string stat = "Доступный";

if (type == null || rate == null || occupancy == null)

{

Interaction.MsgBox("Пожалуйста заполните поля!", Constants.vbInformation, "Внимание");

}

else

{

if (bttnSave.Text == "&Сохранить")

{

var sql = "SELECT * FROM tblRoom WHERE RoomNumber = " + Module1.SafeSqlLiteral(num, 2) + "";

 

var cmd = new OleDbCommand(sql, Module1.con);

OleDbDataReader dr = cmd.ExecuteReader();

 

try

{

if (dr.Read() == false)

{

                            //Происходит добавление полей

OleDbCommand add_room = new OleDbCommand("INSERT INTO tblRoom(RoomNumber,RoomType,RoomRate,NoOfOccupancy,Status) values (\'" +

Module1.SafeSqlLiteral(num, 2) + "\',\'" +

Module1.SafeSqlLiteral(type, 2) + "\',\'" +

Module1.SafeSqlLiteral(rate, 2) + "\',\'" +

Module1.SafeSqlLiteral(occupancy, 2) + "\',\'" +

stat + "\')", Module1.con);

add_room.ExecuteNonQuery();

add_room.Dispose();

Interaction.MsgBox("Номер добавлен!", Constants.vbInformation, "Добавление");

txtID.Clear();

txtRoomType.Clear();

txtRoomRate.Clear();

txtNoOfOccupancy.Clear();

}

else

{

Interaction.MsgBox("Номер уже существует!", Constants.vbExclamation, "Внимание");

}

}

catch (Exception ex)

{

MessageBox.Show(ex.Message);

}

}

else

{

                    //Обновляет данные

OleDbCommand update_room = new OleDbCommand("UPDATE tblRoom SET RoomNumber= \'" + Module1.SafeSqlLiteral(num, 2) + "\',RoomType = \'" + Module1.SafeSqlLiteral(type, 2) + "\',RoomRate = \'" + Module1.SafeSqlLiteral(rate, 2) + "\',NoOfOccupancy = \'" + Module1.SafeSqlLiteral(occupancy, 2) + "\' WHERE ID = " + id.ToString() + "", Module1.con);

update_room.ExecuteNonQuery();

update_room.Dispose();

Interaction.MsgBox("Номер сохранен!", Constants.vbInformation, "Сохранение");

bttnSave.Text = "&Сохранить";

txtID.Clear();

txtRoomType.Clear();

txtRoomRate.Clear();

txtNoOfOccupancy.Clear();

}

}

Module1.con.Close();

display_room();

}

//При выборе номера, если данные изменяются то происходит обновление.

public void lvRoom_DoubleClick(object sender, System.EventArgs e)

{

           

string a = System.Convert.ToString(Interaction.MsgBox("Обновить?", (int) Constants.vbQuestion + Constants.vbYesNo, "Обновление"));

if (a == Constants.vbYes.ToString())

{

id = int.Parse(lvRoom.SelectedItems[0].Text);

txtID.Text = lvRoom.SelectedItems[0].SubItems[1].Text;

txtRoomType.Text = lvRoom.SelectedItems[0].SubItems[2].Text;

txtRoomRate.Text = lvRoom.SelectedItems[0].SubItems[3].Text;

txtNoOfOccupancy.Text = lvRoom.SelectedItems[0].SubItems[4].Text;

 

TabControl1.SelectTab(0);

bttnSave.Text = "&Обновление";

}

}

 

public void lvRoom_SelectedIndexChanged(System.Object sender, System.EventArgs e)

{

 

}

}

}

Форма Гости.

using System.Collections.Generic;

using System.Data;

using System.Drawing;

using System.Diagnostics;

using Microsoft.VisualBasic;

using System.Linq;

using System;

using System.Collections;

using System.Xml.Linq;

using System.Windows.Forms;

 

 

using System.Data.OleDb;

 

namespace HBRS

{

public partial class frmGuest

{

public frmGuest()

{

InitializeComponent();

 

 

}

 

#region Default Instance

 

private static frmGuest defaultInstance;

 

public static frmGuest Default

{

get

{

if (defaultInstance == null)

{

defaultInstance = new frmGuest();

defaultInstance.FormClosed += new FormClosedEventHandler(defaultInstance_FormClosed);

}

 

return defaultInstance;

}

}

 

static void defaultInstance_FormClosed(object sender, FormClosedEventArgs e)

{

defaultInstance = null;

}

 

#endregion

 

public void bttnSave_Click(System.Object sender, System.EventArgs e)

{

Module1.con.Open();

string fname = txtFName.Text.Trim();

string mname = txtMName.Text.Trim();

string lname = txtLName.Text.Trim();

string add = txtAddress.Text.Trim();

string num = txtNumber.Text.Trim();

string email = txtEmail.Text.Trim();

string stat = "Active";

string remark = "Available";

 

if (fname == "" || mname == "" || lname == "" || add == "" || num == "")

{

                Interaction.MsgBox("Пожалуйста, заполните поля!", Constants.vbInformation, "Внимание");

}

else

{

                //Если все поля заполнены, то происходит добавление гостя.

OleDbCommand add_guest = new OleDbCommand("INSERT INTO tblGuest(GuestFName,GuestMName,GuestLName,GuestAddress,GuestContactNumber,GuestGender,GuestEmail,Status,Remarks) values (\'" +

fname + "\',\'" +

mname + "\',\'" +

lname + "\',\'" +

add + "\',\'" +

num + "\',\'" +

cboGender.Text + "\',\'" +

email + "\',\'" +

stat + "\',\'" +

remark + "\')", Module1.con);

add_guest.ExecuteNonQuery();

add_guest.Dispose();

Interaction.MsgBox("Гость добавлен!", Constants.vbInformation, "Добавление");

txtFName.Clear();

txtMName.Clear();

txtLName.Clear();

txtAddress.Clear();

txtNumber.Clear();

txtEmail.Clear();

}

Module1.con.Close();

display_guest();

}

 

public void frmGuest_Load(System.Object sender, System.EventArgs e)

{

display_guest();

TabControl1.SelectTab(0);

}

 

private void display_guest()

{

            //Подключение к БД

Module1.con.Open();

            //Создание таблицы

DataTable Dt = new DataTable("tblGuest");

OleDbDataAdapter rs = default(OleDbDataAdapter);

//Вбирает записи из таблицы

rs = new OleDbDataAdapter("Select * from tblGuest", Module1.con);

//Идет заполнение  таблицы

rs.Fill(Dt);

int indx = default(int);

lvGuest.Items.Clear();

for (indx = 0; indx <= Dt.Rows.Count - 1; indx++)

{

ListViewItem lv = new ListViewItem();

lv.Text = (string) (Dt.Rows[indx]["ID"]).ToString();

lv.SubItems.Add(Dt.Rows[indx]["GuestFName"].ToString());

lv.SubItems.Add(Dt.Rows[indx]["GuestMName"].ToString());

lv.SubItems.Add(Dt.Rows[indx]["GuestLName"].ToString());

lv.SubItems.Add(Dt.Rows[indx]["GuestAddress"].ToString());

lv.SubItems.Add(Dt.Rows[indx]["GuestContactNumber"].ToString());

lv.SubItems.Add(Dt.Rows[indx]["Status"].ToString());

lvGuest.Items.Add(lv);

}

rs.Dispose();

Module1.con.Close();

}

 

public void bttnCancel_Click(System.Object sender, System.EventArgs e)

{

            //Выполняет очистку

txtFName.Clear();

txtMName.Clear();

txtLName.Clear();

txtAddress.Clear();

txtNumber.Clear();

txtEmail.Clear();

}

}

}

Форма Выселения.

using System.Collections.Generic;

using System.Data;

using System.Drawing;

using System.Diagnostics;

using Microsoft.VisualBasic;

using System.Linq;

using System;

using System.Collections;

using System.Xml.Linq;

using System.Windows.Forms;

 

 

using System.Data.OleDb;

 

namespace HBRS

{

public partial class frmCheckout

{

public frmCheckout()

{

InitializeComponent();

 

 

}

 

#region Default Instance

 

private static frmCheckout defaultInstance;

 

public static frmCheckout Default

{

get

{

if (defaultInstance == null)

{

defaultInstance = new frmCheckout();

defaultInstance.FormClosed += new FormClosedEventHandler(defaultInstance_FormClosed);

}

 

return defaultInstance;

}

}

 

static void defaultInstance_FormClosed(object sender, FormClosedEventArgs e)

{

defaultInstance = null;

}

 

#endregion

//Открывает форму выбора гостя

public void bttnSearchGuest_Click(System.Object sender, System.EventArgs e)

{

frmCheckinList.Default.ShowDialog();

}

//Если номер комнаты  меняется, то происходит обновление  полей с типом комнаты и  стоимостью номера.

public void txtRoomNumber_TextChanged(System.Object sender, System.EventArgs e)

{

if (txtRoomNumber.Text == "" || txtRoomNumber.Text == "")

{

 

}

else

{

Module1.con.Open();

DataTable dt = new DataTable("tblRoom");

Module1.rs = new OleDbDataAdapter("SELECT * FROM tblRoom WHERE RoomNumber = " + txtRoomNumber.Text + "", Module1.con);

Module1.rs.Fill(dt);

txtRoomType.Text = (string) (dt.Rows[0]["RoomType"]).ToString();

txtRoomRate.Text = (string) (Conversion.Val(dt.Rows[0]["RoomRate"]).ToString("N"));

Module1.rs.Dispose();

Module1.con.Close();

}

 

}

 

        public void txtCash_KeyPress(object sender,

 

 

public void bttnCheckout_Click(System.Object sender, System.EventArgs e)

{

            //Если поля пусте то, просит заполнить их.

if (txtTransID.Text == null || txtGuestName.Text == "")

{

                Interaction.MsgBox("Выберите транзакцию!", Constants.vbExclamation, "Внимание");

}

else

{

if (Conversion.Val(txtCash.Text) < Conversion.Val(txtTotal.Text))

{

                    //Interaction.MsgBox("Недостаточно денежных средств!", Constants.vbExclamation, "Внимание");

}

else

{

                    //Происходит выселение, и обновляются поля в трех таблицах.

                    string @out = System.Convert.ToString(Interaction.MsgBox("Подтвердите", (int)Constants.vbQuestion + Constants.vbYesNo, "Подтвердите"));

if (@out == Constants.vbYes.ToString())

{

Module1.con.Open();

OleDbCommand update_trans = new OleDbCommand("UPDATE tblTransaction SET Remarks = 'Checkout' WHERE TransID = " + lblTransID.Text + "", Module1.con);

update_trans.ExecuteNonQuery();

 

                        OleDbCommand update_guest = new OleDbCommand("UPDATE tblGuest SET Remarks = 'Checkout' WHERE ID = " + lblGuestID.Text + "", Module1.con);

                        update_guest.ExecuteNonQuery();

 

OleDbCommand update_room = new OleDbCommand("UPDATE tblRoom SET Status = 'Available' WHERE RoomNumber = " + txtRoomNumber.Text + "", Module1.con);

update_room.ExecuteNonQuery();

 

Interaction.MsgBox("Выселен!", Constants.vbInformation, "Выселение");

Module1.con.Close();

clear_text();

}

}

}

}

 

public void clear_text()

{

            //Происходит очистка.

txtTransID.Clear();

txtGuestName.Clear();

txtRoomNumber.Text = "";

txtRoomRate.Clear();

txtRoomType.Clear();

txtCheckin.Clear();

txtCheckout.Clear();

txtChildren.Clear();

txtAdult.Clear();

txtAdvance.Clear();

txtDiscountType.Clear();

txtTotal.Clear();

txtSubTotal.Clear();

txtDays.Clear();

txtCash.Clear();

txtChange.Clear();

}

 

public void lblTransID_TextChanged(object sender, System.EventArgs e)

{

 

}

 

public void frmCheckout_FormClosing(object sender, System.Windows.Forms.FormClosingEventArgs e)

{

clear_text();

}

 

public void frmCheckout_Load(System.Object sender, System.EventArgs e)

{

txtRoomNumber.Clear();

dtOut.Value = DateTime.Now;

}

 

public void txtAdvance_TextChanged(System.Object sender, System.EventArgs e)

{

 

}

 

public void txtTotal_TextChanged(System.Object sender, System.EventArgs e)

{

 

}

 

 

public void txtDiscountType_TextChanged(System.Object sender, System.EventArgs e)

{

 

}

}

}

Форма Заселения.

using System.Collections.Generic;

using System.Data;

using System.Drawing;

using System.Diagnostics;

using Microsoft.VisualBasic;

using System.Linq;

using System;

using System.Collections;

using System.Xml.Linq;

using System.Windows.Forms;

 

 

using System.Data.OleDb;

 

namespace HBRS

{

public partial class frmCheckin

{

public frmCheckin()

{

InitializeComponent();

}

 

#region Default Instance

 

private static frmCheckin defaultInstance;

 

/// <summary>

/// </summary>

public static frmCheckin Default

{

get

{

if (defaultInstance == null)

{

defaultInstance = new frmCheckin();

defaultInstance.FormClosed += new FormClosedEventHandler(defaultInstance_FormClosed);

}

 

return defaultInstance;

}

}

 

static void defaultInstance_FormClosed(object sender, FormClosedEventArgs e)

{

defaultInstance = null;

}

 

#endregion

int guestID;

int roomID;

int trans_ID;

 

public void frmCheckin_FormClosing(object sender, System.Windows.Forms.FormClosingEventArgs e)

{

            //Когда происходит закрытие формы, то выводится сообщение.

string a = System.Convert.ToString(Interaction.MsgBox("Выйти?", (int) Constants.vbQuestion + Constants.vbYesNo, "Выход"));

if (a == Constants.vbNo.ToString())

{

e.Cancel = true;

}

else

{

                //Очищает поля

clear_text();

}

}

public void frmCheckin_Load(System.Object sender, System.EventArgs e)

{

            //Очищает поля, задает текущую дату и время

clear_text();

DateTime time = DateTime.Now;

string format = "dd/MM/yyyy";

txtCheckInDate.Text = time.ToString(format);

dtCheckOutDate.Text = System.Convert.ToString(DateTime.Now.AddDays(1));

transID();

pop_discount();