using System;
using System.Data.SqlClient;
using System.Data;

using qminoa.Common.Data;

namespace qminoa.DA
{
	public class MrDep :IDisposable
	{
		private string conStr ;
		private SqlConnection con ;
		private SqlDataAdapter commandAdp ;

		private string paramChg(string str)
		{
			str = "@"+str;
			return str;
		}

		public bool InsertMrBranch(string branchName,string simpleCode)
		{
			SqlCommand command = new SqlCommand();

			command.CommandText = "InsertMrBranch";
			command.Connection = con;
			command.CommandType = CommandType.StoredProcedure;

			command.Parameters.Add(paramChg(DepData.BRANCHNAME_FIELD),SqlDbType.VarChar);
			command.Parameters.Add(paramChg(DepData.SIMPLECODE_FIELD),SqlDbType.VarChar);
			command.Parameters[paramChg(DepData.BRANCHNAME_FIELD)].Value = branchName;
			command.Parameters[paramChg(DepData.SIMPLECODE_FIELD)].Value = simpleCode;

			con.Open();
			int result = command.ExecuteNonQuery();
			if(result>0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}

		public bool UpdateMrBranch(int braID,string branchName,string simpleCode)
		{
			SqlCommand command = new SqlCommand();

			command.CommandText = "UpdateMrBranch";
			command.Connection = con;
			command.CommandType = CommandType.StoredProcedure;

			command.Parameters.Add(paramChg(DepData.BRANCHID_FIELD),SqlDbType.Int);
			command.Parameters.Add(paramChg(DepData.BRANCHNAME_FIELD),SqlDbType.VarChar);
			command.Parameters.Add(paramChg(DepData.SIMPLECODE_FIELD),SqlDbType.VarChar);
			//
			command.Parameters[paramChg(DepData.BRANCHID_FIELD)].Value = braID; 
			command.Parameters[paramChg(DepData.BRANCHNAME_FIELD)].Value = branchName;
			command.Parameters[paramChg(DepData.SIMPLECODE_FIELD)].Value = simpleCode;
	
			con.Open();
			int result = command.ExecuteNonQuery();
			if(result>0)
			{
				return true;
			}
			else
			{
				return false;
			}

		}

		public bool DeleteMrBranch(int braID)
		{
			SqlCommand command = new SqlCommand();

			command.CommandText = "DeleteMrBranch";
			command.Connection = con;
			command.CommandType = CommandType.StoredProcedure;

			SqlParameter param = new SqlParameter(paramChg(DepData.BRANCHID_FIELD),SqlDbType.Int);
			param.Value = braID;
			command.Parameters.Add(param);

			con.Open();
			int result = command.ExecuteNonQuery();
			if(result>0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}

		public DataSet GetDepData_By_BranchID(int branchID)
		{
			SqlCommand command = new SqlCommand();

			command.CommandText = "GetDepInfByBranchID";
			command.Connection = con;
			command.CommandType = CommandType.StoredProcedure;

			SqlParameter param = new SqlParameter(paramChg(DepData.BRANCHID_FIELD),SqlDbType.Int);
			param.Value = branchID;
			command.Parameters.Add(param);

			commandAdp.SelectCommand = command;

			DataSet data = new DataSet();
			commandAdp.Fill(data);

			return data;

		}

		public DataSet GetDepData_By_DepID(int depID)
		{
			SqlCommand command = new SqlCommand();
			command.CommandText = "GetDepInfByDepID";
			command.Connection = con;
			command.CommandType = CommandType.StoredProcedure;
			SqlParameter param = new SqlParameter(paramChg(DepData.DEPID_FIELD),SqlDbType.Int);
			param.Value = depID;
			command.Parameters.Add(param);
			commandAdp.SelectCommand = command;
			DataSet data = new DataSet();
			commandAdp.Fill(data);
			return data;
		}

		public DataSet GetDepData()
		{
			SqlCommand  command = new SqlCommand();

			command.CommandText = "GetDepInf";
			command.Connection = con;
			command.CommandType = CommandType.StoredProcedure;
			commandAdp.SelectCommand = command;
			DataSet data = new DataSet();
			commandAdp.Fill(data);
			return data;
		}

		public DataSet GetBraData()
		{
			SqlCommand  command = new SqlCommand();

			command.CommandText = "GetBraInf";
			command.Connection = con;
			command.CommandType = CommandType.StoredProcedure;
			commandAdp.SelectCommand = command;
			DataSet data = new DataSet();
			commandAdp.Fill(data);
			return data;
		}

		public bool DeleteMrDep(int depID)
		{
			SqlCommand command = new SqlCommand();
			command.CommandText = "DeleteMrDep";
			command.CommandType = CommandType.StoredProcedure;
			SqlParameter param = new SqlParameter(paramChg(DepData.DEPID_FIELD),SqlDbType.Int);
			param.Value = depID;
			command.Parameters.Add(param);
			command.Connection = con;
			con.Open();
			int i = command.ExecuteNonQuery();
			con.Close();
			if(i==0)
				return false;
			else
				return true;
		}

		public bool InsertMrDep(DepData depData)
		{
			commandAdp.InsertCommand = GetCommand();
			commandAdp.InsertCommand.CommandText = "InsertMrDep";
			int result = commandAdp.Update(depData,DepData.DEP_TABLE_NAME);
			if(result>0)
				return true;
			else
				return false;
		}

		public bool UpdateMrDep(DepData depData)
		{
			commandAdp.UpdateCommand = GetCommand();
			commandAdp.UpdateCommand.CommandText = "UpdateMrDep";
			commandAdp.UpdateCommand.Parameters.Add(new SqlParameter(paramChg(DepData.DEPID_FIELD),SqlDbType.Int));
			commandAdp.UpdateCommand.Parameters[paramChg(DepData.DEPID_FIELD)].SourceColumn = DepData.DEPID_FIELD;
			int result = commandAdp.Update(depData,DepData.DEP_TABLE_NAME);
			if(result>0)
				return true;
			else
				return false;
		}

		private SqlCommand GetCommand()
		{
			SqlCommand command = new SqlCommand();

			command.Connection = con;
			command.CommandType = CommandType.StoredProcedure;

			SqlParameterCollection param = command.Parameters;

			param.Add(new SqlParameter(paramChg(DepData.BRANCHID_FIELD),SqlDbType.Int));
			param.Add(new SqlParameter(paramChg(DepData.DEPNAME_FIELD),SqlDbType.VarChar));
			param.Add(new SqlParameter(paramChg(DepData.MANAGER_FIELD),SqlDbType.VarChar));
			param.Add(new SqlParameter(paramChg(DepData.TEL1_FIELD),SqlDbType.VarChar));
			param.Add(new SqlParameter(paramChg(DepData.TEL2_FIELD),SqlDbType.VarChar));
			param.Add(new SqlParameter(paramChg(DepData.FAX_FIELD),SqlDbType.VarChar));
			param.Add(new SqlParameter(paramChg(DepData.PROVINCE_FIELD),SqlDbType.VarChar));
			param.Add(new SqlParameter(paramChg(DepData.CITY_FIELD),SqlDbType.VarChar));
			param.Add(new SqlParameter(paramChg(DepData.DISTRICT_FIELD),SqlDbType.VarChar));
			param.Add(new SqlParameter(paramChg(DepData.STREET_FIELD),SqlDbType.VarChar));
			param.Add(new SqlParameter(paramChg(DepData.POSTCODE_FIELD),SqlDbType.VarChar));

			param[paramChg(DepData.BRANCHID_FIELD)].SourceColumn = DepData.BRANCHID_FIELD;
			param[paramChg(DepData.DEPNAME_FIELD)].SourceColumn = DepData.DEPNAME_FIELD;
			param[paramChg(DepData.MANAGER_FIELD)].SourceColumn = DepData.MANAGER_FIELD;
			param[paramChg(DepData.TEL1_FIELD)].SourceColumn = DepData.TEL1_FIELD;
			param[paramChg(DepData.TEL2_FIELD)].SourceColumn = DepData.TEL2_FIELD;
			param[paramChg(DepData.FAX_FIELD)].SourceColumn = DepData.FAX_FIELD;
			param[paramChg(DepData.PROVINCE_FIELD)].SourceColumn = DepData.PROVINCE_FIELD;
			param[paramChg(DepData.CITY_FIELD)].SourceColumn = DepData.CITY_FIELD;
			param[paramChg(DepData.DISTRICT_FIELD)].SourceColumn = DepData.DISTRICT_FIELD;
			param[paramChg(DepData.STREET_FIELD)].SourceColumn = DepData.STREET_FIELD;
			param[paramChg(DepData.POSTCODE_FIELD)].SourceColumn = DepData.POSTCODE_FIELD;

			return command;
		}

		public void Dispose()
		{
			Dispose(true);
			GC.SuppressFinalize(true);
		}

		protected virtual void Dispose(bool disposing)
		{
				
		}

		public MrDep()
		{
			conStr = System.Configuration.ConfigurationSettings.AppSettings.Get("ConnectionString");
			con = new SqlConnection(conStr);
			commandAdp = new SqlDataAdapter();
		}
	}
}