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 DBTelCustomer:ITelCustomer
	{
		public DBTelCustomer()
		{
			// 
			// TODO: 在此處添加構造函數邏輯
			//
		}

		//信息保存
		public int addIelCustomer(TelCustomerInfo info)
		{
			if( info == null)
				throw new Exception("The TelCustomer Object is null,Can't Save Null object!");
			info.id = OraHelper.createSequence("se_tel_customer");
			string addSql = "INSERT INTO tel_customer(id,cid,telnumber,position) VALUES(:id,:cid,:telnumber,:position)";
			OracleParameter [] param = new OracleParameter[]
				{
					new OracleParameter(":id",OracleType.Int32,10),
					new OracleParameter(":cid",OracleType.Int32,10),
					new OracleParameter(":telnumber",OracleType.VarChar,60),
					new OracleParameter(":position",OracleType.Int32,10),				
			};
			param[0].Value = info.id;
			param[1].Value = info.cid;
			param[2].Value = info.telnumber;
			param[3].Value = info.position;

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

		//刪除信息
		public void delTelCustomerById(int id)
		{
			string delSql = "DELETE FROM tel_customer WHERE id=:id";
			OracleParameter [] param = new OracleParameter[]
				{
					new OracleParameter(":id",OracleType.Int32,10),
			};
			param[0].Value = id;
			try
			{
				OraHelper.ExecuteNonQuery(OraHelper.GetOracleConnection(),CommandType.Text,delSql,param);
			}
			catch(Exception e)
			{
				throw e;
			}
			finally
			{
				;
			}
		}
	
		//通過來電號碼檢索來電客戶信息
		public ArrayList getTelCustomerByTelnumber(string telnumber)
		{
			ArrayList list = new ArrayList();
			string selSql = "SELECT id,telnumber,cid,position FROM tel_customer WHERE telnumber = :telnumber";
			OracleParameter [] param = new OracleParameter[]
				{
					new OracleParameter(":telnumber",OracleType.VarChar,60),
			};
			param[0].Value = telnumber;
			try
			{
				OracleDataReader oraReader = OraHelper.ExecuteReader(OraHelper.GetOracleConnection(),CommandType.Text,selSql,param);
				while(oraReader.Read())
				{
					TelCustomerInfo info = new TelCustomerInfo();
					info.id = oraReader.IsDBNull(0)?0:oraReader.GetInt32(0);
					info.telnumber = oraReader.IsDBNull(1)?"":oraReader.GetString(1);
					info.cid = oraReader.IsDBNull(2)?0:oraReader.GetInt32(2);
					info.position = oraReader.IsDBNull(3)?0:oraReader.GetInt32(3);				
					list.Add(info);				
				}		
				oraReader.Close();
				return list;
			}
			catch(Exception e)
			{
				throw e;
			}
			finally
			{
				;
			}
		}
	
		//通過編號修改信息
		public int updateTelCustomer(TelCustomerInfo info)
		{
			if( info == null)
				throw new Exception("The TelCustomer Object is null,Can't Save Null object!");
			string updateSql = "UPDATE tel_customer SET telnumber=:telnumber WHERE cid=:cid";
			OracleParameter [] param = new OracleParameter[]
				{
					
					new OracleParameter(":cid",OracleType.Int32,10),
					new OracleParameter(":telnumber",OracleType.VarChar,60),
			};
			param[0].Value = info.cid;
			param[1].Value = info.telnumber;

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

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

		}
	
		public ArrayList getTleCustomerByCid(int cid)
		{
			ArrayList list = new ArrayList();
			string selSql = "SELECT id,telnumber,cid,position FROM tel_customer WHERE cid = :cid";
			OracleParameter [] param = new OracleParameter[]
				{
					new OracleParameter(":cid",OracleType.Int32,10),
			};
			param[0].Value = cid;
			try
			{
				OracleDataReader oraReader = OraHelper.ExecuteReader(OraHelper.GetOracleConnection(),CommandType.Text,selSql,param);
				while(oraReader.Read())
				{
					TelCustomerInfo info = new TelCustomerInfo();
					info.id = oraReader.IsDBNull(0)?0:oraReader.GetInt32(0);
					info.telnumber = oraReader.IsDBNull(1)?"":oraReader.GetString(1);
					info.cid = oraReader.IsDBNull(2)?0:oraReader.GetInt32(2);
					info.position = oraReader.IsDBNull(3)?0:oraReader.GetInt32(3);				
					list.Add(info);				
				}		
				oraReader.Close();
				return list;
			}
			catch(Exception e)
			{
				throw e;
			}
			finally
			{
				;
			}
		}

		public DataSet getTelCustomerByTelnumber_DataSet(string telnumber,string tableName)
		{
			string selSql = "SELECT id,telnumber,cid,position FROM tel_customer WHERE telnumber = :telnumber";
			OracleParameter [] param = new OracleParameter[]
				{
					new OracleParameter(":telnumber",OracleType.VarChar,60),
			};
			param[0].Value = telnumber;
			try
			{
				return OraHelper.ExecuteDataSet(OraHelper.GetOracleConnection(),CommandType.Text,tableName,selSql,param);
			}
			catch(Exception e)
			{
				throw e;
			}
			finally
			{
				;
			}
		}
		public DataSet getTleCustomerByCid_DataSet(int cid,string tableName)
		{
			string selSql = "SELECT id,telnumber,cid,position FROM tel_customer WHERE cid = :cid";
			OracleParameter [] param = new OracleParameter[]
				{
					new OracleParameter(":cid",OracleType.Int32,10),
			};
			param[0].Value = cid;
			try
			{
				return OraHelper.ExecuteDataSet(OraHelper.GetOracleConnection(),CommandType.Text,tableName,selSql,param);
			}
			catch(Exception e)
			{
				throw e;
			}
			finally
			{
				;
			}
		}

		public int saveCustomerPosition(string cids,int position)
		{
			string updateSql = "UPDATE tel_customer SET position=:position WHERE cid IN ("+cids+")";
			OracleParameter [] param = new OracleParameter[]
				{
					new OracleParameter(":position",OracleType.Int32,10),
			};
			param[0].Value = position;
			try
			{
				return OraHelper.ExecuteNonQuery(OraHelper.GetOracleConnection(),CommandType.Text,updateSql,param);
			}
			catch(Exception e)
			{
				throw e;
			}
			finally				
			{
				;
			}

		}
	}
}