using System;
using Model;
using Data_Common;
using System.Data;
using System.Data.SqlClient;

namespace Data_Access
{
	/// <summary>
	/// DBTrain 的摘要說明---顏政.。
	/// </summary>
	public class DBTrain:IDBTrain
	{
		private const string Sql_select_RailroadInfo="SELECT * FROM RailroadInfo WHERE SetOutCity=@setoutcity AND ArriveCity=@arrivecity";
		private const string Sql_select_CheciInfo="SELECT * FROM RailroadInfo WHERE CarTime=@cartime";
		private const string Parm_fromcity="@setoutcity";
		private const string Parm_tocity="@arrivecity";
		private const string Parm_Checi="@cartime";




		public DBTrain(){}

		private static SqlParameter[] GetTrainInfo()
		{
			
			SqlParameter[] parms=Sqlhelp.GetCachedParameters(Sql_select_RailroadInfo);
			if(parms==null)
			{
				parms=new SqlParameter[]{
											new SqlParameter(Parm_fromcity,SqlDbType.VarChar,20),
											new SqlParameter(Parm_tocity,SqlDbType.VarChar,20),
										};
				Sqlhelp.CacheParameters(Sql_select_RailroadInfo,parms);
			}
			return parms;
		}

		private static SqlParameter[] GetCheciInfo()
		{
			SqlParameter[] parms1=Sqlhelp.GetCachedParameters(Sql_select_CheciInfo);

			if(parms1==null)
			{
				parms1=new SqlParameter[]{
											 new SqlParameter(Parm_Checi,SqlDbType.NVarChar,20) 
										 };
				Sqlhelp.CacheParameters(Sql_select_CheciInfo,parms1);
			}
			return parms1;
		}

		

		/// <summary>
		/// 根據始發城市和目的城市查找所有的車次信息
		/// </summary>
		/// <param name="startCity">始發城市</param>
		/// <param name="endCity">目的城市</param>
		/// <returns>所有的車次信息</returns>
		public Train_shuxing[] GetTrainInfo(string startCity,string endCity)
		{
			SqlParameter[] parms=GetTrainInfo();
			parms[0].Value=startCity;
			parms[1].Value=endCity;
			
			DataTable dt=Sqlhelp.ExecuteDataTable(ConnectionInfo.ConnectionString(),Sql_select_RailroadInfo,CommandType.Text,parms);

			Train_shuxing[] TrainInfo=new Train_shuxing[dt.Rows.Count];
			Train_shuxing x;
	
			for(int i=0;i<dt.Rows.Count;i++)
			{
				x=new Train_shuxing();

				x.schedulecode=dt.Rows[i][0].ToString();
				x.fromcity=dt.Rows[i][3].ToString();
				x.tocity=dt.Rows[i][4].ToString();
				x.leavetime=DateTime.Parse(dt.Rows[i][5].ToString());
				x.arrivaltime=DateTime.Parse(dt.Rows[i][6].ToString());
				x.traintype=dt.Rows[i][1].ToString();
				x.speed=dt.Rows[i][2].ToString();
				x.distance=int.Parse(dt.Rows[i][8].ToString());
				x.days=int.Parse(dt.Rows[i][7].ToString());
				TrainInfo[i]=x;
			}

			return TrainInfo;			
		}
		/// <summary>
		/// 根據車次序號查找所有的車次信息
		/// </summary>
		/// <param name="Checi">車次序號</param>
		/// <returns>所有的車次信息</returns>
		public Train_shuxing[] GetCheciInfo(string Checi)
		{
			SqlParameter[] parms1=GetCheciInfo();
			parms1[0].Value=Checi;

			
			DataTable dt=Sqlhelp.ExecuteDataTable(ConnectionInfo.ConnectionString(),Sql_select_CheciInfo,CommandType.Text,parms1);

			Train_shuxing[] TrainInfo=new Train_shuxing[dt.Rows.Count];
			Train_shuxing x;
	
			for(int i=0;i<dt.Rows.Count;i++)
			{
				x=new Train_shuxing();

				x.schedulecode=dt.Rows[i][0].ToString();
				x.fromcity=dt.Rows[i][3].ToString();
				x.tocity=dt.Rows[i][4].ToString();
				x.leavetime=DateTime.Parse(dt.Rows[i][5].ToString());
				x.arrivaltime=DateTime.Parse(dt.Rows[i][6].ToString());
				x.traintype=dt.Rows[i][1].ToString();
				x.speed=dt.Rows[i][2].ToString();
				x.distance=int.Parse(dt.Rows[i][8].ToString());
				x.days=int.Parse(dt.Rows[i][7].ToString());
				TrainInfo[i]=x;
			}

			return TrainInfo;			
		}




	}
}