querydepositform.aspx.vb

旅客信息管理、酒店客房管理、各种信息查询

'文件名:QueryDepositForm.aspx.vb
Imports System.Data.SqlClient
Imports System.Data
Partial Class QueryManage_QueryDepositForm
    Inherits System.Web.UI.Page
    Private Shared MySQLConnectionString As String = ""
    Private Shared MyGuestTable As New System.Data.DataTable()
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Me.Page.Title = "当前位置：查询管理->查询旅客押金"
        Dim MyForbidString As String = Session("MyForbid").ToString()
        If (MyForbidString.IndexOf("C5") > 1) Then
            Server.Transfer("~/SystemManage/AllErrorHelp.aspx")
        End If
        If (Not IsPostBack) Then
            '创建无连接的数据表
            Dim MyKey(1) As DataColumn
            MyGuestTable = New DataTable("旅客押金")
            Dim MyColumn As New DataColumn()
            MyColumn.DataType = System.Type.GetType("System.String")
            MyColumn.ColumnName = "入住编号"
            MyGuestTable.Columns.Add(MyColumn)
            MyKey(0) = MyColumn
            MyGuestTable.PrimaryKey = MyKey
            MyGuestTable.Columns.Add("客人姓名", System.Type.GetType("System.String"))
            MyGuestTable.Columns.Add("入住房号", System.Type.GetType("System.String"))
            MyGuestTable.Columns.Add("预收押金", System.Type.GetType("System.String"))
            MyGuestTable.Columns.Add("费用总额", System.Type.GetType("System.String"))
            MyGuestTable.Columns.Add("应补金额", System.Type.GetType("System.String"))
            Me.TextBox1.Text = DateTime.Now.Year.ToString()
            Me.TextBox2.Text = DateTime.Now.Month.ToString()
            Me.TextBox3.Text = DateTime.Now.Day.ToString()
        End If
        If (IsPostBack) Then
            '查询酒店旅客押金信息       
            MyGuestTable.Rows.Clear()
            Dim MySQL As String = "Select 入住编号,客人姓名,入住房号,预收押金,折扣价格,入住日期,住宿人数 From 客房入住单 Where 入住编号 NOT IN (Select 入住编号 From 客房结账单)"
            MySQLConnectionString = ConfigurationManager.ConnectionStrings("MyHotelDBConnectionString").ConnectionString
            Dim MyConnection As New SqlConnection(MySQLConnectionString)
            MyConnection.Open()
            Dim MyTable As New System.Data.DataTable()
            Dim MyAdapter As New SqlDataAdapter(MySQL, MyConnection)
            MyAdapter.Fill(MyTable)
            For Each MyRow As DataRow In MyTable.Rows
                Dim My入住编号 As String = MyRow(0).ToString()
                Dim My客人姓名 As String = MyRow(1).ToString()
                Dim My入住房号 As String = MyRow(2).ToString()
                Dim My预收押金 As String = MyRow(3).ToString()
                Dim My折扣价格 As String = MyRow(4).ToString()
                Dim My住宿人数 As Integer = Convert.ToInt16(MyRow(6).ToString())
                Dim MyD店内消费 As Double = 0
                Dim MyD电话费 As Double = 0
                Dim MyD用餐费 As Double = 0
                Dim MyD住宿费 As Double = 0

                Dim My入住日期 As DateTime = MyRow(5)
                Dim MyYear As Integer = Convert.ToInt16(Me.TextBox1.Text)
                Dim MyMonth As Integer = Convert.ToInt16(Me.TextBox2.Text)
                Dim MyDay As Integer = Convert.ToInt16(Me.TextBox3.Text)
                Dim My截止日期DateTimePicker As New DateTime(MyYear, MyMonth, MyDay)
                Dim MySpan As TimeSpan = My截止日期DateTimePicker.Subtract(My入住日期)
                MyD住宿费 = Convert.ToDouble(My折扣价格) * Math.Round(MySpan.TotalDays, 0) * My住宿人数

                MySQL = "Select SUM(金额) From 话费入账 Where 入住编号='" + My入住编号 + "'"
                Dim MyCommand As New SqlCommand(MySQL, MyConnection)
                Dim My电话费 As String = MyCommand.ExecuteScalar().ToString()
                If (My电话费.Length > 0) Then
                    MyD电话费 = Convert.ToDouble(My电话费)
                End If
                
                MySQL = "Select SUM(金额) From 餐费入账 Where 入住编号='" + My入住编号 + "'"
                MyCommand = New SqlCommand(MySQL, MyConnection)
                Dim My用餐费 As String = MyCommand.ExecuteScalar().ToString()
                If (My用餐费.Length > 0) Then
                    MyD用餐费 = Convert.ToDouble(My用餐费)
                End If
                
                MySQL = "Select SUM(消费数量*折扣价格) From 消费入账 Where 入住编号='" + My入住编号 + "'"
                MyCommand = New SqlCommand(MySQL, MyConnection)
                Dim My店内消费 As String = MyCommand.ExecuteScalar().ToString()
                If (My店内消费.Length > 0) Then
                    MyD店内消费 = Convert.ToDouble(My店内消费)
                End If
                
                Dim MyD费用总额 As Double = MyD住宿费 + MyD电话费 + MyD用餐费 + MyD店内消费

                Dim MyD应补金额 As Double = MyD费用总额 - Convert.ToDouble(My预收押金)

                Dim MyCells() As Object = {My入住编号, My客人姓名, My入住房号, MyD应补金额.ToString(), My预收押金, MyD费用总额.ToString(), MyD住宿费.ToString(), My用餐费, My店内消费, My电话费}
                Dim MyNewRow As DataRow = MyGuestTable.NewRow()
                MyNewRow("入住编号") = My入住编号
                MyNewRow("客人姓名") = My客人姓名
                MyNewRow("入住房号") = My入住房号
                MyNewRow("预收押金") = My预收押金
                MyNewRow("费用总额") = MyD费用总额
                MyNewRow("应补金额") = MyD应补金额
                MyGuestTable.Rows.Add(MyNewRow)
            Next
            Me.GridView1.DataSource = MyGuestTable
            Me.GridView1.DataBind()
        End If
    End Sub
    '设置要传递到打印页的数据表
    Public ReadOnly Property MyPrintTable() As DataTable
        Get
            Return MyGuestTable
        End Get
    End Property
    '设置要传递到打印页的截止日期
    Public ReadOnly Property MyPrint截止日期() As String
        Get
            Return Me.TextBox1.Text + "年" + Me.TextBox2.Text + "月" + Me.TextBox3.Text + "日"
        End Get
    End Property
    Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
        '打印旅客押金信息
        Server.Transfer("~/QueryManage/QueryDepositPrint.aspx")
    End Sub
End Class