?//文件名:QueryDepositForm.aspx.cs
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

using System.Data.SqlClient;
public partial class QueryManage_QueryDepositForm : System.Web.UI.Page
{
    private static String MySQLConnectionString;
    private static System.Data.DataTable MyGuestTable = new System.Data.DataTable();
    protected void Page_Load(object sender, EventArgs e)
    {
        this.Page.Title = "當前位置：查詢管理->查詢旅客押金";   
        string MyForbidString = Session["MyForbid"].ToString();
        if (MyForbidString.IndexOf("C5") > 1)
        {
            Server.Transfer("~/SystemManage/AllErrorHelp.aspx");
        }
        if (!IsPostBack)
        {
            //創建無連接的數據表
            DataColumn[] MyKey = new DataColumn[1];
            MyGuestTable = new DataTable("旅客押金");
            DataColumn MyColumn = 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"));
            this.TextBox1.Text = DateTime.Now.Year.ToString();
            this.TextBox2.Text = DateTime.Now.Month.ToString();
            this.TextBox3.Text = DateTime.Now.Day.ToString();                
        }
        if (IsPostBack)
        {//查詢酒店旅客押金信息       
            MyGuestTable.Rows.Clear();
            string MySQL = "Select 入住編號,客人姓名,入住房號,預收押金,折扣價格,入住日期,住宿人數 From 客房入住單 Where 入住編號 NOT IN (Select 入住編號 From 客房結賬單)";
            MySQLConnectionString = ConfigurationManager.ConnectionStrings["MyHotelDBConnectionString"].ConnectionString;
            SqlConnection MyConnection = new SqlConnection(MySQLConnectionString);
            MyConnection.Open();
            System.Data.DataTable MyTable = new System.Data.DataTable();
            SqlDataAdapter MyAdapter = new SqlDataAdapter(MySQL, MyConnection);
            MyAdapter.Fill(MyTable);
            foreach (DataRow MyRow in MyTable.Rows)
            {
                string My入住編號 = MyRow[0].ToString();
                string My客人姓名 = MyRow[1].ToString();
                string My入住房號 = MyRow[2].ToString();
                string My預收押金 = MyRow[3].ToString();
                string My折扣價格 = MyRow[4].ToString();
                int My住宿人數 = Convert.ToInt16(MyRow[6].ToString());
                double MyD店內消費 = 0;
                double MyD電話費 = 0;
                double MyD用餐費 = 0;
                double MyD住宿費 = 0;

                DateTime My入住日期 = (DateTime)MyRow[5];
                int MyYear = Convert.ToInt16(this.TextBox1.Text);
                int MyMonth = Convert.ToInt16(this.TextBox2.Text);
                int MyDay = Convert.ToInt16(this.TextBox3.Text);
                DateTime My截止日期DateTimePicker = new DateTime(MyYear,MyMonth,MyDay);
                TimeSpan MySpan = My截止日期DateTimePicker.Subtract(My入住日期);
                MyD住宿費 = Convert.ToDouble(My折扣價格) * Math.Round(MySpan.TotalDays, 0) * My住宿人數;

                MySQL = "Select SUM(金額) From 話費入賬 Where 入住編號='" + My入住編號 + "'";
                SqlCommand MyCommand = new SqlCommand(MySQL, MyConnection);
                string My電話費 = MyCommand.ExecuteScalar().ToString();
                if (My電話費.Length > 0)
                {
                    MyD電話費 = Convert.ToDouble(My電話費);
                }

                MySQL = "Select SUM(金額) From 餐費入賬 Where 入住編號='" + My入住編號 + "'";
                MyCommand = new SqlCommand(MySQL, MyConnection);
                string My用餐費 = MyCommand.ExecuteScalar().ToString();
                if (My用餐費.Length > 0)
                {
                    MyD用餐費 = Convert.ToDouble(My用餐費);
                }

                MySQL = "Select SUM(消費數量*折扣價格) From 消費入賬 Where 入住編號='" + My入住編號 + "'";
                MyCommand = new SqlCommand(MySQL, MyConnection);
                string My店內消費 = MyCommand.ExecuteScalar().ToString();
                if (My店內消費.Length > 0)
                {
                    MyD店內消費 = Convert.ToDouble(My店內消費);
                }

                double MyD費用總額 = MyD住宿費 + MyD電話費 + MyD用餐費 + MyD店內消費;

                double MyD應補金額 = MyD費用總額 - Convert.ToDouble(My預收押金);

                Object[] MyCells = { My入住編號, My客人姓名, My入住房號, MyD應補金額.ToString(),My預收押金, 
                MyD費用總額.ToString(),MyD住宿費.ToString(),My用餐費,My店內消費,My電話費};
                DataRow MyNewRow = MyGuestTable.NewRow();
                MyNewRow["入住編號"] = My入住編號;
                MyNewRow["客人姓名"] = My客人姓名;
                MyNewRow["入住房號"] = My入住房號;
                MyNewRow["預收押金"] = My預收押金;
                MyNewRow["費用總額"] = MyD費用總額;
                MyNewRow["應補金額"] = MyD應補金額;
                MyGuestTable.Rows.Add(MyNewRow);               
            }
            this.GridView1.DataSource = MyGuestTable;
            this.GridView1.DataBind();
        }
    }
    public DataTable MyPrintTable
    {//設置要傳遞到打印頁的數據表
        get
        {
            return MyGuestTable;
        }
    }
    public String MyPrint截止日期
    {//設置要傳遞到打印頁的截止日期
        get
        {
            return this.TextBox1.Text+"年"+this.TextBox2.Text+"月"+this.TextBox3.Text+"日";
        }
    }
    protected void Button2_Click(object sender, EventArgs e)
    {//打印旅客押金信息
        Server.Transfer("~/QueryManage/QueryDepositPrint.aspx");
    }  
}