using System;
using CallCenter.IDAL;
using CallCenter.Modules;
using System.Data;
using System.Data.SqlClient;
using System.Data.OracleClient;
using System.Collections;
using System.Text;
using System.IO;

namespace CallCenter.OracleDAL
{
	/// <summary>
	/// 
	/// </summary>
	public class DBSysoperator:ISysoperator
	{
		public DBSysoperator()
		{
			// 
			// TODO: 在此處添加構造函數邏輯
			//
		}
	
		#region ISysoperator 成員

		public int addSysoperator(SysoperatorInfo info)
		{
			if( info == null)
				throw new Exception("The Sysoperator Object is null,Can't Save Null object!");
		
			//判斷登陸名稱是否重復
			if(info.lname.Equals(this.getSysoperatorByLname(info.lname).lname))
			{
				return -1;
			}
			//判斷坐席ID是否重復
			if(this.getSysoperatorByOid(info.oid,info.lname))
			{
				return -2;
			}
		
			string addSql = "INSERT INTO sysoperator(lname,oname,opassword,olevel,omenuid,odescription,oid,otelnumber) VALUES(@lname,@oname,@opassword,@olevel,@omenuid,@odescription,@oid,@otelnumber)";			
			SqlParameter[] param = new SqlParameter[]
				{
					new SqlParameter("@lname",SqlDbType.VarChar,60),
					new SqlParameter("@oname",SqlDbType.VarChar,60),
					new SqlParameter("@opassword",SqlDbType.VarChar,60),
					new SqlParameter("@olevel",SqlDbType.VarChar,60),
					new SqlParameter("@omenuid",SqlDbType.VarChar,200),
					new SqlParameter("@odescription",SqlDbType.VarChar,200),
				new SqlParameter("@oid",SqlDbType.Int,4),
				new SqlParameter("@otelnumber",SqlDbType.VarChar,20),
			};
			param[0].Value = info.lname;
			param[1].Value = info.oname;
			param[2].Value = info.opassword;
			param[3].Value = info.olevel;
			param[4].Value = info.omenuid;
			param[5].Value = info.odescription;
			param[6].Value = info.oid;
			param[7].Value = info.otelnumnber;

			try
			{
				OraHelper.SqlserverExecuteNonQuery(OraHelper.GetSqlserverConnection(),CommandType.Text,addSql,param);
			}
			catch(Exception e)
			{						
				throw e;
			}
			finally				
			{
				;
			}
			return 1;
		}

		public int updateSysoperator(SysoperatorInfo info)
		{
			if( info == null)
				throw new Exception("The Sysoperator Object is null,Can't Save Null object!");
				
			//判斷坐席ID是否重復
			if(this.getSysoperatorByOid(info.oid,info.lname))
			{
				return -2;
			}
			string updateSql = "UPDATE sysoperator SET oname=@oname,opassword=@opassword,olevel=@olevel,omenuid=@omenuid,odescription=@odescription,oid=@oid,otelnumber=@otelnumber WHERE lname=@lname";			
			SqlParameter [] param = new SqlParameter[]
				{
					new SqlParameter("@lname",SqlDbType.VarChar,60),
					new SqlParameter("@oname",SqlDbType.VarChar,60),
					new SqlParameter("@opassword",SqlDbType.VarChar,60),
					new SqlParameter("@olevel",SqlDbType.VarChar,60),
					new SqlParameter("@omenuid",SqlDbType.VarChar,200),
					new SqlParameter("@odescription",SqlDbType.VarChar,200),
					new SqlParameter("@oid",SqlDbType.Int,4),
					new SqlParameter("@otelnumber",SqlDbType.VarChar,20),
			};
			param[0].Value = info.lname;
			param[1].Value = info.oname;
			param[2].Value = info.opassword;
			param[3].Value = info.olevel;
			param[4].Value = info.omenuid;
			param[5].Value = info.odescription;
			param[6].Value = info.oid;
			param[7].Value = info.otelnumnber;

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

		public void saveOpopedom(string lname, string popedom)
		{
			// TODO:  添加 DBSysoperator.saveOpopedom 實現
		}

		public void delSysoperator(string lnames)
		{
			string delSql = "DELETE FROM sysoperator WHERE lname IN('"+lnames.Replace("'","''").Replace(",","','")+"')";
			
			try
			{
				OraHelper.SqlserverExecuteNonQuery(OraHelper.GetSqlserverConnection(),CommandType.Text,delSql,null);
			}
			catch(Exception e)
			{
				throw e;
			}
			finally
			{
				;
			}
		}

		public SysoperatorInfo login(string lname, string password)
		{
			SysoperatorInfo info = new SysoperatorInfo();
			string selSql = "SELECT lname,oname,olevel,omenuid,odescription,oid,otelnumber FROM sysoperator WHERE lname=@lname AND opassword=@opassword";
			SqlParameter [] param = new SqlParameter[]
				{
					new SqlParameter("@lname",SqlDbType.VarChar,60),
					new SqlParameter("@opassword",SqlDbType.VarChar,60),
			};
			param[0].Value = lname;
			param[1].Value = password;
			try
			{
				SqlDataReader dr = OraHelper.SqlserverExecuteReader(OraHelper.GetSqlserverConnection(),CommandType.Text,selSql,param);
				if(dr.Read())
				{
					info.lname = dr.IsDBNull(0)?"":dr.GetString(0);
					info.oname = dr.IsDBNull(1)?"":dr.GetString(1);
					info.olevel = dr.IsDBNull(2)?"":dr.GetString(2);
					info.omenuid = dr.IsDBNull(3)?"":dr.GetString(3);
					info.odescription = dr.IsDBNull(4)?"":dr.GetString(4);
					info.oid = dr.IsDBNull(5)?0:dr.GetInt32(5);
					info.otelnumnber = dr.IsDBNull(6)?"":dr.GetString(6);
				}
				dr.Close();
			}
			catch(Exception e)
			{
				throw e;
			}
			return info;
		}

		public SysoperatorInfo getSysoperatorByLname(string lname)
		{
			SysoperatorInfo info = new SysoperatorInfo();
			string selSql = "SELECT lname,oname,olevel,omenuid,odescription,oid,otelnumber,opassword FROM sysoperator WHERE lname=@lname";
			SqlParameter[] param = new SqlParameter[]
				{
					new SqlParameter("@lname",SqlDbType.VarChar,10),
			};
			param[0].Value = lname;
			try
			{
				SqlDataReader dr = OraHelper.SqlserverExecuteReader(OraHelper.GetSqlserverConnection(),CommandType.Text,selSql,param);
				if(dr.Read())
				{
					info.lname = dr.IsDBNull(0)?"":dr.GetString(0);
					info.oname = dr.IsDBNull(1)?"":dr.GetString(1);
					info.olevel = dr.IsDBNull(2)?"":dr.GetString(2);
					info.omenuid = dr.IsDBNull(3)?"":dr.GetString(3);
					info.odescription = dr.IsDBNull(4)?"":dr.GetString(4);
					info.oid = dr.IsDBNull(5)?0:dr.GetInt32(5);
					info.otelnumnber = dr.IsDBNull(6)?"":dr.GetString(6);
					info.opassword = dr.IsDBNull(7)?"":dr.GetString(7);
				}
				dr.Close();
			}
			catch(Exception e)
			{
				throw e;
			}
			return info;
		}

		public bool getSysoperatorByOid(int oid,string lname)
		{
			bool flag = false;
			SysoperatorInfo info = new SysoperatorInfo();
			string selSql = "SELECT lname,oname,olevel,omenuid,odescription,oid,otelnumber FROM sysoperator WHERE oid=@oid AND lname <> @lname";
			SqlParameter [] param = new SqlParameter[]
				{
					new SqlParameter("@oid",SqlDbType.Int,4),
					new SqlParameter("@lname",SqlDbType.VarChar,60),
			};
			param[0].Value = oid;
			param[1].Value = lname;
			try
			{
				SqlDataReader dr = OraHelper.SqlserverExecuteReader(OraHelper.GetSqlserverConnection(),CommandType.Text,selSql,param);
				if(dr.Read())
				{
//					info.lname = dr.IsDBNull(0)?"":dr.GetString(0);
//					info.oname = dr.IsDBNull(1)?"":dr.GetString(1);
//					info.olevel = dr.IsDBNull(2)?"":dr.GetString(2);
//					info.omenuid = dr.IsDBNull(3)?"":dr.GetString(3);
//					info.odescription = dr.IsDBNull(4)?"":dr.GetString(4);
//					info.oid = dr.IsDBNull(5)?0:dr.GetInt32(5);
//					info.otelnumnber = dr.IsDBNull(6)?"":dr.GetString(6);
					flag = true;
				}
				dr.Close();
			}
			catch(Exception e)
			{
				throw e;
			}
			return flag;
		}

		public ArrayList getALLSysoperator()
		{
			ArrayList list = new ArrayList();
			string selSql = "SELECT lname,oname,olevel,omenuid,odescription,oid,otelnumber FROM sysoperator";
			try
			{
				SqlDataReader dr = OraHelper.SqlserverExecuteReader(OraHelper.GetSqlserverConnection(),CommandType.Text,selSql,null);
				while(dr.Read())
				{
					SysoperatorInfo info = new SysoperatorInfo();
					info.lname = dr.IsDBNull(0)?"":dr.GetString(0);
					info.oname = dr.IsDBNull(1)?"":dr.GetString(1);
					info.olevel = dr.IsDBNull(2)?"":dr.GetString(2);
					info.omenuid = dr.IsDBNull(3)?"":dr.GetString(3);
					info.odescription = dr.IsDBNull(4)?"":dr.GetString(4);
					info.oid = dr.IsDBNull(5)?0:dr.GetInt32(5);
					info.otelnumnber = dr.IsDBNull(6)?"":dr.GetString(6);

					list.Add(info);
				}
				dr.Close();
			}
			catch(Exception e)
			{
				throw e;
			}
			return list;
		}

		public int changePassword(string lname,string oldpass,string newpass)
		{
			SysoperatorInfo info = this.login(lname,oldpass);
			if(lname.Equals(info.lname))
			{
				info.opassword = newpass;
				return this.updateSysoperator(info);
			}
			else
			{
				return -1;
			}
		}

		public DataSet getALLSysoperator_DataSet(string tableName){
			string selSql = "SELECT lname,oname,olevel,omenuid,odescription,oid,otelnumber,opassword FROM sysoperator";
			try
			{
				//return OraHelper.ExecuteDataSet(OraHelper.GetOracleConnection(),CommandType.Text,tableName,selSql,null);
				return OraHelper.SqlserverExecuteDataSet(OraHelper.GetSqlserverConnection(),CommandType.Text,tableName,selSql,null);
			}
			catch(Exception e)
			{
				throw e;
			}
		}
		public void BackupDatabase(string filename)
		{
			string delSql = "BACKUP DATABASE jialinerp TO DISK = '"+filename+"'";
			
			try
			{
				OraHelper.SqlserverExecuteNonQuery(OraHelper.GetSqlserverConnection(),CommandType.Text,delSql,null);
			}
			catch(Exception e)
			{
				throw e;
			}
			finally
			{
				;
			}
		}
		public void RestoreDatabase(string filename)
		{
			string delSql = "RESTORE Database jialinerp FROM DISK = '"+filename+"'";
			
			try
			{
				OraHelper.SqlserverExecuteNonQuery(OraHelper.GetSqlserverConnection(),CommandType.Text,delSql,null);
			}
			catch(Exception e)
			{
				throw e;
			}
			finally
			{
				;
			}
		}
		#endregion
	}
}