?using System;
using System.Data;
using System.Configuration;
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.OleDb;
using System.Data.SqlClient;
using System.Text;

/// <summary>
/// AccessConection 的摘要說明
/// </summary>
public class AccessConection
{
    //連接字符串
    string ConnString = Convert.ToString(ConfigurationManager.ConnectionStrings["MyConnection"]);

	public AccessConection()
	{  
        //構造函數
	}
     

    //線路查詢函數
    public string Link_Select(string Line)
    {
        using(SqlConnection Conn = new SqlConnection(ConnString))
        {
            //查詢字符串(查詢線路)
//            string SqlStr = "SELECT * FROM [線路屬性] WHERE bus LIKE '%" + TempStr + "%'";
            
            //建立連接            
            Conn.Open();
            SqlCommand Cmd = new SqlCommand();
            Cmd.Connection = Conn;
            //調用存儲過程
            Cmd.CommandText = "Link_Select";
            Cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter param = Cmd.Parameters.AddWithValue("@Line", "%" + Line.Trim() + "%");
            param.Direction = ParameterDirection.Input;
            param.DbType = DbType.String;
            
            SqlDataReader Dr = Cmd.ExecuteReader();
            StringBuilder OutSB = new StringBuilder();
            string OutStr = "";
            try
            {
                while (Dr.Read())
                {                 
                    OutSB.Append("<div class='Data_Style'>");
                    OutSB.Append("<span>" + Dr["bus"].ToString() + "</span>");
                    OutSB.Append("<span>" + Dr["ticketsell"].ToString() + "</span>");
                    OutSB.Append("<span>" + Dr["price"].ToString() + "</span>");
                    OutSB.Append("<span>IC卡支持:" + Dr["ic"].ToString() + "</span>");
                    OutSB.Append("<span>出車時間:" + Dr["runtime"].ToString() + "</span>");
                    OutSB.Append("<span>所屬集團:" + Dr["lnc"].ToString() + "</span>");
                    OutSB.Append("</div>");
                    OutSB.Append("<div class='Data_Style_Longtext'>");
                    OutSB.Append(Dr["beginend"].ToString());
                    OutSB.Append("</div>");
                }
                OutStr = OutSB.ToString();
                if( "" == OutStr.Trim() )
                {
                    OutStr = "Sorry!沒有你所查找的數據";
                }
            }
            catch
            {
                OutStr = "數據庫連接錯誤";
            }
            finally
            {
                Dr.Close();
                Conn.Close();                
            }              
            return OutStr;   
        }
    }
    
    //站點查詢函數
    public string Site_Select(string Site)
    {
        using(SqlConnection Conn = new SqlConnection(ConnString))
        {
            //查詢字符串(查詢站點)
//            string SqlStr = "SELECT * FROM [線路表] WHERE sname LIKE '%" + TempStr + "%'";
            
            //建立連接            
            Conn.Open();
            SqlCommand Cmd = new SqlCommand();
            Cmd.Connection = Conn;
            //調用存儲過程
            Cmd.CommandText = "Site_Select";
            Cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter param = Cmd.Parameters.AddWithValue("@Site", "%" + Site.Trim() + "%");
            param.Direction = ParameterDirection.Input;
            param.DbType = DbType.String;
            SqlDataReader Dr = Cmd.ExecuteReader();
            StringBuilder OutSB = new StringBuilder();            
            string OutStr = "";
            try
            {
                while (Dr.Read())
                {
                    OutSB.Append("<div class='Data_Style'>");
                    OutSB.Append("乘坐<span>" + Dr["bus"].ToString() + "</span>路車");
                    OutSB.Append("可到達<span>" + Dr["sname"] + "</span>");
                    OutSB.Append("</div>"); 
                    OutSB.Append("<div style='height:5px;'></div>");               
                }
                OutStr = OutSB.ToString();
                if ("" == OutStr.Trim())
                {
                    OutStr = "Sorry!沒有你所查找的數據";
                }
            }
            catch
            {
                OutStr = "數據庫連接錯誤";
            }
            finally
            {
                Dr.Close();
                Conn.Close();                
            }  
            return OutStr;   
        }
    }

    //換乘查詢
    public string StoS_Select(string Begin_Site,string End_Site)
    {
        using (SqlConnection Conn = new SqlConnection(ConnString))
        {
            //查詢字符串(查詢站點)
/*
            StringBuilder SB = new StringBuilder();
            SB.Append(" SELECT DISTINCT TOP 10 m.起點線路 AS 起點線路, m.起點站 AS 起點站, m.中轉站 AS 中轉站, n.終點站 AS 終點站, n.終點線路 AS 終點線路, ABS(M.經過站點數 + N.經過站點數) AS 總經過站點數");
            SB.Append(" FROM    (SELECT A.bus AS 起點線路 , A.sname AS 起點站 , B.sname AS 中轉站 ,ABS(A.snum - B.snum ) AS 經過站點數");
            SB.Append(" FROM 線路表 AS A, 線路表 AS B");
            SB.Append(" WHERE A.sname LIKE '%" + TempStrBegin +"%' AND (A.sname <> B.sname AND A.bus = B.bus))");
            SB.Append(" AS m(起點線路,起點站,中轉站,經過站點數)");
            SB.Append(" ,");
            SB.Append(" (SELECT C.sname AS 中轉站 ,D.sname AS 終點站 ,D.bus AS 終點線路 ,ABS(C.snum - D.snum ) AS 經過站點數");
            SB.Append(" FROM 線路表 AS C, 線路表 AS D");
            SB.Append(" WHERE D.sname LIKE '%" + TempStrEnd + "%' AND (C.sname <> D.sname AND C.bus = D.bus))");
            SB.Append(" AS n(中轉站,終點站,終點線路,經過站點數)");
            SB.Append(" WHERE m.中轉站 = n.中轉站 AND (m.起點線路 <> n.終點線路) ORDER BY 總經過站點數"); 
            string SqlStr = SB.ToString();   
            
*/
            string OutStr = "";
            int i = 0;
                        
            //建立連接  
            Conn.Open();               
            SqlCommand Cmd = new SqlCommand();
            Cmd.Connection = Conn;            
            //調用存儲過程
            Cmd.CommandText = "StoS_Select";
            Cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter param = Cmd.Parameters.AddWithValue("@Begin_Site", "%" + Begin_Site.Trim() + "%");
            Cmd.Parameters.AddWithValue("@End_Site", "%" + End_Site.Trim() + "%");
            param.Direction = ParameterDirection.Input;
            param.DbType = DbType.String;
            SqlDataReader Dr = Cmd.ExecuteReader();
            StringBuilder OutSB = new StringBuilder();
            try
            {
                while (Dr.Read())
                {
                    i++;
                    OutSB.Append("<div class='StoS_Style_Top'>第<span>" + i + "</span>種方案,共經過<span>" + Dr["總經過站點數"].ToString() + "</span>站</div>");
                    OutSB.Append("<div class='StoS_Style_Down'>");
                    OutSB.Append("從<span>" + Dr["起點站"].ToString() + "</span>");
                    OutSB.Append("坐<span>" + Dr["起點線路"].ToString() + "</span>");
                    OutSB.Append("在<span>" + Dr["中轉站"].ToString() + "</span>");
                    OutSB.Append("換乘<span>" + Dr["終點線路"].ToString() + "</span>");
                    OutSB.Append("到<span>" + Dr["終點站"].ToString() + "</span>");
                    OutSB.Append("</div>");
                    OutStr = OutSB.ToString();
                }
                OutStr = OutSB.ToString();
                if ("" == OutStr.Trim())
                {
                    OutStr = "Sorry!沒有你所查找的數據";
                }
            }
            catch
            {   
                OutStr = "數據庫連接出錯!!!"; 
            }                                 
            finally
            {
                Conn.Close();
                Dr.Close();
            } 
            return OutStr;
        }

    }
}