using System;
using CallCenter.IDAL;
using CallCenter.Modules;
using System.Data;
using System.Data.OracleClient;
using System.Collections;

namespace CallCenter.OracleDAL
{
	/// <summary>
	/// 工單處理內容數據庫操作類
	/// </summary>
	public class DBWBContent:IWBContent
	{
		public DBWBContent()
		{
			// 
			// TODO: 在此處添加構造函數邏輯
			//
		}
		#region IWBContent 成員

		public int addWBContent(WBContentInfo info)
		{
			if( info == null)
				throw new Exception("The WBContent Object is null,Can't Save Null object!");
			info.id = OraHelper.createSequence("se_wbcontent");

			string addSql = "INSERT INTO wbcontent(id,wbid,opid,opname,opdate,opcontent) VALUES(:id,:wbid,:opid,:opname,sysdate,:opcontent)";			
			OracleParameter [] param = new OracleParameter[]
				{
					new OracleParameter(":id",OracleType.Int32,10),
					new OracleParameter(":wbid",OracleType.Int32,10),
					new OracleParameter(":opid",OracleType.VarChar,60),
					new OracleParameter(":opname",OracleType.VarChar,60),
					//new OracleParameter(":opdate",OracleType.VarChar,100),
					new OracleParameter(":opcontent",OracleType.VarChar,1000),					
			};
			param[0].Value = info.id;
			param[1].Value = info.wbid;
			param[2].Value = info.opid;
			param[3].Value = info.opname;
			param[4].Value = info.opcontent;		

			try
			{
				OraHelper.ExecuteNonQuery(OraHelper.GetOracleConnection(),CommandType.Text,addSql,param);
			}
			catch(Exception e)
			{		
				Console.WriteLine(e.Message.ToString());
				Console.WriteLine(e.StackTrace);
				throw e;
			}
			finally				
			{
				;
			}
			return info.id;
		}

		public void updateWBContent(WBContentInfo info)
		{
			if( info == null)
				throw new Exception("The WBContent Object is null,Can't Save Null object!");

			string updateSql = "UPDATE wbcontent SET opid=:opid,opname=:opname,opdate=sysdate,opcontent=:opcontent WHERE id=:id";			
			OracleParameter [] param = new OracleParameter[]
				{										
					new OracleParameter(":opid",OracleType.VarChar,60),
					new OracleParameter(":opname",OracleType.VarChar,60),				
					new OracleParameter(":opcontent",OracleType.VarChar,1000),
				new OracleParameter(":id",OracleType.Int32,10),
			};						
			param[0].Value = info.opid;
			param[1].Value = info.opname;
			param[2].Value = info.opcontent;
			param[3].Value = info.id;

			try
			{
				OraHelper.ExecuteNonQuery(OraHelper.GetOracleConnection(),CommandType.Text,updateSql,param);
			}
			catch(Exception e)
			{						
				throw e;
			}
			finally				
			{
				;
			}
		}

		public ArrayList getWBContentByWBId(int wbid)
		{
			ArrayList list = new ArrayList();			
			string selSql = "SELECT id,wbid,opid,opname,opdate,opcontent FROM wbcontent WHERE wbid=:wbid ORDER BY id ASC";
			OracleParameter [] param = new OracleParameter[]
				{
					new OracleParameter(":wbid",OracleType.Int32,10),
			};
			param[0].Value = wbid;
			try
			{
				OracleDataReader dr = OraHelper.ExecuteReader(OraHelper.GetOracleConnection(),CommandType.Text,selSql,param);
				while(dr.Read())
				{
					WBContentInfo info = new WBContentInfo();
					info.id = dr.IsDBNull(0)?0:dr.GetInt32(0);;
					info.wbid = dr.IsDBNull(1)?0:dr.GetInt32(1);
					info.opid = dr.IsDBNull(2)?"":dr.GetString(2);
					info.opname = dr.IsDBNull(3)?"":dr.GetString(3);					
					DateTime date = dr.GetDateTime(4);
					info.opdate = date.ToShortDateString();										
					info.opcontent = dr.IsDBNull(5)?"":dr.GetString(5);
					
					list .Add(info);
				}
				dr.Close();
			}
			catch(Exception e)
			{
				throw e;
			}
			return list;
		}

		public void delWBContentById(int id)
		{
			string delSql = "DELETE FROM wbcontent WHERE id="+id;
			
			try
			{
				OraHelper.ExecuteNonQuery(OraHelper.GetOracleConnection(),CommandType.Text,delSql,null);
			}
			catch(Exception e)
			{
				throw e;
			}
			finally
			{
				;
			}
		}

		public void delWBContentByWBId(int wbid)
		{
			string delSql = "DELETE FROM wbcontent WHERE wbid="+wbid;
			
			try
			{
				OraHelper.ExecuteNonQuery(OraHelper.GetOracleConnection(),CommandType.Text,delSql,null);
			}
			catch(Exception e)
			{
				throw e;
			}
			finally
			{
				;
			}
		}

		public void delWBContentByWBIds(string wbid)
		{
			string delSql = "DELETE FROM wbcontent WHERE wbid IN ("+wbid+")";
			
			try
			{
				OraHelper.ExecuteNonQuery(OraHelper.GetOracleConnection(),CommandType.Text,delSql,null);
			}
			catch(Exception e)
			{
				throw e;
			}
			finally
			{
				;
			}
		}

		public DataSet getWBContentByWBId_DataSet(int wbid,string tableName)
		{
			string selSql = "SELECT id,wbid,opid,opname,opdate,opcontent FROM wbcontent WHERE wbid=:wbid ORDER BY id ASC";
			OracleParameter [] param = new OracleParameter[]
				{
					new OracleParameter(":wbid",OracleType.Int32,10),
			};
			param[0].Value = wbid;
			try
			{
				return OraHelper.ExecuteDataSet(OraHelper.GetOracleConnection(),CommandType.Text,tableName,selSql,param);
			}
			catch(Exception e)
			{
				throw e;
			}
		}
		#endregion
	}
}