using System;
using Microsoft.Win32;
using System.Collections;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Reflection;
using System.Windows.Forms;
using System.Data.OracleClient;
using System.IO;

namespace LLNet.DAL
{
	/// <summary>
	/// Implementación de interfase de IDAL para bases de datos Oracle
	/// </summary>
	public class OracleDAL : System.Windows.Forms.UserControl, IDAL
	{
		#region Declaraciones públicas

		public static LLNet.Controls.LLEventLog EventLog;
		public static string ExeDir;
        public static string ExeLogDir;

		public event OnLoadQueriesEventHandler OnLoadQueries;

		public System.Data.OracleClient.OracleConnection cnn;	
		public System.Data.OracleClient.OracleDataAdapter adpSelect;
		public System.Data.OracleClient.OracleDataAdapter adpPagingSelect;

        public string ConnectionString(string connName)
        {
		    DataSet dsEsquemas = new DataSet();
		    dsEsquemas.ReadXml(ExeDir + "Esquemas.xml");
		    dsEsquemas.Tables[0].PrimaryKey = new DataColumn[]{dsEsquemas.Tables[0].Columns["Nombre"]};
		    DataRow dr = dsEsquemas.Tables[0].Rows.Find(connName);
            if (dr != null)
                return dr["Conexion"].ToString();
            else 
                return cnn.ConnectionString;
        }

		#endregion

		#region Declaraciones privadas

        private System.Collections.Hashtable queryMap;
		private System.Data.OracleClient.OracleCommand cmd;
		private System.Data.OracleClient.OracleCommand cmdPaging;
		private OracleTransaction activeTrans;
		private int transactionLevel = 0;


		/// <summary> 
		/// Required designer variable.
		/// </summary>
		private System.ComponentModel.Container components = null;

		#endregion
        
		#region Constructor

		public OracleDAL()
		{
			// This call is required by the Windows.Forms Form Designer.
			InitializeComponent();

			ExeDir = GetDirByAppConfig();
 
			if (ExeDir == null || ExeDir == "")
			{
				ExeDir = GetDirByRegistry();

				if (ExeDir == null || ExeDir == "") 
					ExeDir = Application.StartupPath + "\\";
			}         	

            ExeLogDir = @"C:\LogPos\";

            if (Directory.Exists(ExeLogDir))
            {
                EventLog = new LLNet.Controls.LLEventLog(ExeLogDir);

                EventLog.WriteLog(LLNet.Utils.Types.ErrorTypes.MESSAGES, "Directorio dal Log: " + ExeLogDir);

            }
            else
            {

                Directory.CreateDirectory(ExeLogDir);

                EventLog = new LLNet.Controls.LLEventLog(ExeLogDir);
                EventLog.WriteLog(LLNet.Utils.Types.ErrorTypes.MESSAGES, "Directorio dal Log: " + ExeLogDir);

            }


			OnLoadQueries += new OnLoadQueriesEventHandler(LoadQueries);
			OnLoadQueries();

		}

		private string GetDirByAppConfig()
		{
			string result;
			try
			{
				result = System.Configuration.ConfigurationSettings.AppSettings.Get("DalPath");
			}
			catch 
			{
				result = "";
			}
			return result;
		}

		private string GetDirByRegistry()
		{
			string result;
			try
			{
				RegistryKey key = Registry.LocalMachine;
				key = key.OpenSubKey(@"Software\Logistika",true);
				result = key.GetValue("DalPath",Application.StartupPath + "\\").ToString();
			}
			catch
			{
				result = "";
			}
			return result;
		}

		#endregion

		#region Propiedades

		/// <summary>
		/// Devuelve o configura la colección de QueryMaps disponibles para el módulo
		/// </summary>
		public Hashtable QueryMap 
		{		
			get{return queryMap;}
			set{queryMap = value;}
		}

		/// <summary>
		/// Devuelve en un IDataAdapter el adapter utilizado para pagineo en base de datos
		/// </summary>
		public IDataAdapter PagingAdapter
		{
			get{return adpPagingSelect;	}
		}

		/// <summary>
		/// Devuelve el objecto Command para ejecutar un SQL
		/// </summary>
		public IDbCommand SingleCommand
		{
			get{return adpSelect.SelectCommand;	}
		}

		/// <summary>
		/// Devuelve la conexión
		/// </summary>
		public IDbConnection Connection
		{
			get{return cnn;	}
		}

        /// <summary>
        /// Devuelve un adapter para ejecutar SQL
        /// </summary>
		public IDataAdapter SelectAdapter
		{
			get{return adpSelect;	}
		}

		#endregion

		#region Métodos públicos

		/// <summary>
		/// Crea una nueva transacción
		/// </summary>
		public void BeginTransaction()
		{
			if (!( (cnn.State & ConnectionState.Open) != 0))
				cnn.Open();
		
			if (transactionLevel++ == 0 && activeTrans == null)
			{
				activeTrans = cnn.BeginTransaction();
			}
		}

		/// <summary>
		/// Termina la transacción y guarda los cambios de la misma
		/// </summary>
		public void Commit()
		{
			OracleConnection conn;
			
			if (--transactionLevel == 0 && activeTrans != null)
			{
				conn = activeTrans.Connection;
				activeTrans.Commit();

				if ( (conn.State & ConnectionState.Open) != 0 )
					conn.Close();
			
				activeTrans = null;
			}
		}

		/// <summary>
		/// Termina la transaccion y descarta los cambio de la misma
		/// </summary>
		public void Rollback()
		{
			OracleConnection conn;
			
			if (--transactionLevel == 0 && activeTrans != null)
			{
				conn = activeTrans.Connection;
				activeTrans.Rollback();

				if ( (conn.State & ConnectionState.Open) != 0 )
					conn.Close();
			
				activeTrans = null;
			}
		}

		/// <summary>
		/// Actualiza un dataset en base al dataAdapter enviado
		/// </summary>
		/// <param name="adp"></param>
		/// <param name="ds"></param>
		public void Update(IDataAdapter adp, ref DataSet ds)
		{
			OracleDataAdapter oraAdp = adp as OracleDataAdapter;
			oraAdp.RowUpdating += new OracleRowUpdatingEventHandler(oraAdp_RowUpdating);
            oraAdp.RowUpdated += new OracleRowUpdatedEventHandler(oraAdp_RowUpdated);

			try
			{
				BeginTransaction();
				
				if (oraAdp.DeleteCommand != null) 
					oraAdp.DeleteCommand.Transaction = activeTrans;
				if (oraAdp.InsertCommand != null)
					oraAdp.InsertCommand.Transaction = activeTrans;
				if (oraAdp.UpdateCommand != null)
					oraAdp.UpdateCommand.Transaction = activeTrans;

				oraAdp.Update(ds);

                //if (ds.HasErrors)
                //    Rollback();
                //else
                //    Commit();
                if (ds.HasErrors)
                {
                    foreach (DataTable dt in ds.Tables)
                    {
                        foreach (DataRow row in dt.Rows)
                        {
                            if (row.HasErrors)
                            {
                                //MessageBox.Show(row.RowError, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                                EventLog.WriteLog(LLNet.Utils.Types.ErrorTypes.MESSAGES, row.RowError);
                            }
                        }
                    }

                    Rollback();
                }
                else
                {
                    Commit();
                }
			}
			catch(Exception e)
			{
                Rollback();

                EventLog.WriteLog(LLNet.Utils.Types.ErrorTypes.MESSAGES, e.Message);
				throw new Exception(e.Message);
			}
		}

        void oraAdp_RowUpdated(object sender, OracleRowUpdatedEventArgs e)
        {
        }

		/// <summary>
		/// Ejecuta un SQL sin devolver datos
		/// </summary>
		/// <param name="sql"></param>
		/// <returns></returns>
		public int ExecuteNonQuery(string sql)
		{
			int result = 0;
			EventLog.WriteLog(LLNet.Utils.Types.ErrorTypes.MESSAGES,sql);

			try
			{
				BeginTransaction();

				SingleCommand.CommandText = sql;
				SingleCommand.Transaction = activeTrans;
			
				result = SingleCommand.ExecuteNonQuery();

				Commit();
			} 
			catch (Exception e)
			{
				Rollback();
				EventLog.WriteLog(LLNet.Utils.Types.ErrorTypes.MESSAGES,e.Message);
				throw new Exception(e.Message);
			}

			return result;
		}

		// Ejecutar un SQL y devuelve en un object el resultado (INSERT INTO)
		public object ExecuteScalar(string sql)
		{
			return ExecuteScalar(sql,false);
		}
		
		public object ExecuteScalar(string sql, bool hidden)
		{
			object result = null;
		
			if (!hidden)
                EventLog.WriteLog(LLNet.Utils.Types.ErrorTypes.MESSAGES,sql);

			try
			{
				BeginTransaction();
				
				SingleCommand.CommandText = sql;
				SingleCommand.Transaction = activeTrans;
			
				result = SingleCommand.ExecuteScalar();

				Commit();
			} 
			catch (Exception e)
			{
				Rollback();
				EventLog.WriteLog(LLNet.Utils.Types.ErrorTypes.MESSAGES,e.Message);
				throw new Exception(e.Message);
			}

			return result;
		}

		/// <summary>
		/// Llena una tabla de un dataset basado en un dataAdapter
		/// </summary>
		/// <param name="ds"></param>
		/// <param name="adapter"></param>
		/// <returns></returns>
		public DataSet FillDataSet(DataSet ds, IDataAdapter adapter)
		{
			System.Data.OracleClient.OracleDataAdapter oracleAdapter = (System.Data.OracleClient.OracleDataAdapter)adapter;
			ds.EnforceConstraints = false;
			EventLog.WriteLog(LLNet.Utils.Types.ErrorTypes.MESSAGES,oracleAdapter.SelectCommand.CommandText.ToString());
			try
			{
				oracleAdapter.Fill(ds);
			}
			catch (Exception ex)
			{
				EventLog.WriteLog(ex);
			}
			return ds;
		}

		/// <summary>
		/// Llena una tabla de un dataset basado en un dataAdapter
		/// </summary>
		/// <param name="ds"></param>
		/// <param name="pTableName"></param>
		/// <param name="adapter"></param>
		/// <returns></returns>
		public DataSet FillDataSet(DataSet ds, string pTableName, IDataAdapter adapter)
		{
			    System.Data.OracleClient.OracleDataAdapter oracleAdapter = (System.Data.OracleClient.OracleDataAdapter)adapter;
			ds.EnforceConstraints = false;
			EventLog.WriteLog(LLNet.Utils.Types.ErrorTypes.MESSAGES,oracleAdapter.SelectCommand.CommandText.ToString());

			oracleAdapter.Fill(ds,pTableName);
			return ds;
		}

        /// <summary>
        /// asigna al commandText del adapter generico un SQL enviado
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
		public string SetSelectCommand(string sql)
		{
			adpSelect.SelectCommand.CommandText = sql;
			return "";
		}

		/// <summary>
		/// asigna al commandText del adapter generico un SQL enviado indicando valores de pagineo
		/// </summary>
		/// <param name="sql"></param>
		/// <param name="startRecord"></param>
		/// <param name="endRecord"></param>
		/// <returns></returns>
		public string SetSelectCommand(string sql, long startRecord, long endRecord)
		{
			try
			{
				adpPagingSelect.SelectCommand.CommandText=sql;
				adpPagingSelect.SelectCommand.Parameters[":beginPaging"].Value = startRecord;
				adpPagingSelect.SelectCommand.Parameters[":endPaging"].Value = endRecord;
				adpPagingSelect.SelectCommand.Transaction = activeTrans;
			} 
			catch (Exception ex)
			{
				return ex.Message;
			}
			return "";
		}

		public string SetSelectCommand(string sql, long startRecord, long endRecord, string rank, string rankFilter, string rankSort)
		{
			try
			{
				adpPagingSelect.SelectCommand.CommandText=sql;
				adpPagingSelect.SelectCommand.Parameters[":beginPaging"].Value = startRecord;
				adpPagingSelect.SelectCommand.Parameters[":endPaging"].Value = endRecord;
				adpPagingSelect.SelectCommand.Transaction = activeTrans;
			} 
			catch (Exception ex)
			{
				return ex.Message;
			}
			return "";
		}


		#endregion

		#region Métodos virtuales

		/// <summary>
		/// Intenta asignar el connectionString leyendo el archivo Esquemas.xml y devuelve el resultado
		/// </summary>
		/// <param name="connName"></param>
		/// <returns></returns>
		public virtual bool Connect(string connName) 
		{
			// TODO: que connect devuelva en un string para guardar errores
			DataSet dsEsquemas = new DataSet();
			dsEsquemas.ReadXml(ExeDir + "Esquemas.xml");
			dsEsquemas.Tables[0].PrimaryKey = new DataColumn[]{dsEsquemas.Tables[0].Columns["Nombre"]};
			DataRow dr = dsEsquemas.Tables[0].Rows.Find(connName);
			if (dr != null)
			{
				cnn.ConnectionString = dr["Conexion"].ToString();
				return true;
			}
			return false;
		}

		/// <summary>
		/// Basado en el modulo (componente) y en un DataTableName devuelve el adaptador que le hace CRUD
		/// </summary>
		/// <param name="pComponent">Componente</param>
		/// <param name="pTableName">DataSet.DataTable.TableName</param>
		/// <returns>DataAdapter</returns>
		public virtual IDataAdapter FindAdapter(string pTableName)
		{
			foreach(FieldInfo field in this.GetType().GetFields() )
			{
				Type IDAType = field.FieldType.GetInterface("IDataAdapter");

				// Verifica si el objeto implementa IDataAdpter
				if (IDAType!=null)
				{
                    System.Data.OracleClient.OracleDataAdapter  dataAdapter = (System.Data.OracleClient.OracleDataAdapter)field.GetValue(this);
					try 
					{
						// Verifica si el OracleDataAdapter pertenece al nombre de tabla enviada
						ITableMapping tableMapping = dataAdapter.TableMappings.GetByDataSetTable(pTableName);
						return dataAdapter;
					}
					catch (Exception ex) 
					{
						Console.WriteLine(ex.Message);
					}
				}
			}
			return null;
		}

		/// <summary>
		/// Busca el commandText del selectCommand del dataAdapter que pertenece a la tabla enviada
		/// </summary>
		/// <param name="pTableName"></param>
		/// <returns></returns>
		public virtual string FindSelectCmdText(string pTableName)
		{
			foreach(FieldInfo field in this.GetType().GetFields() )
			{
				Type IDAType = field.FieldType.GetInterface("IDataAdapter");

				// Verifica si el objeto implementa IDataAdpter
				if (IDAType!=null)
				{
                    System.Data.OracleClient.OracleDataAdapter  dataAdapter = (System.Data.OracleClient.OracleDataAdapter)field.GetValue(this);
					try 
					{
						// Verifica si el OracleDataAdapter pertenece al nombre de tabla enviada
						ITableMapping tableMapping = dataAdapter.TableMappings.GetByDataSetTable(pTableName);

						// retorna el commandText del selectCommand del dataAdapter encontrado
						return dataAdapter.SelectCommand.CommandText;
					}
					catch (Exception e) 
					{
						Console.WriteLine(e.Message);
					}
				}
				
			}
			return null;
		}

		public string FindQueryInDB(string pQueryName)
		{
			DataSet ds = new DataSet();
			SetSelectCommand("SELECT QUERY FROM CG_QUERIES WHERE DALMODULE = '" + this.GetType().Name + "' AND QUERYNAME = '" + pQueryName + "'");
		
			FillDataSet(ds,SelectAdapter);

			return ds.Tables[0].Rows[0]["QUERY"].ToString();

		}

		#endregion

		#region Métodos privados

		/// <summary>
		/// Agrega a QueryMap los queries por default para los objetos heredados
		/// </summary>
		private void LoadQueries()
		{
			QueryMap = new System.Collections.Hashtable();
			QueryMap.Add("beginPaging","SELECT * FROM (	SELECT x.*, ROWNUM tupla FROM (");
			QueryMap.Add("endPaging",") x	WHERE ROWNUM <= :endPaging ) y WHERE tupla >= :beginPaging");
			QueryMap.Add("count","SELECT COUNT(1) FROM &TABLENAME&");

			QueryMap.Add("PageCmd", "select yo.* from (select xo.*, rownum tupla from ({0}) xo where rownum <= {1}) yo where tupla >= {2} ");
			QueryMap.Add("RankCmd", "select * from ({0}) {1}");
			QueryMap.Add("SelectRankCmd", "select zo.* from ({0}) zo where (1 = 1) {1} {2}");
			QueryMap.Add("TotCmd", "select {0} from ({1}) xo");
			QueryMap.Add("TotCount", "select count(1) from ({0}) xo");			

		}

		#endregion

		#region Destructor
		/// <summary> 
		/// Clean up any resources being used.
		/// </summary>
		protected override void Dispose( bool disposing )
		{
			if( disposing )
			{
				if(components != null)
				{
					components.Dispose();
				}
			}
			base.Dispose( disposing );
		}

		#endregion

		#region Component Designer generated code
		/// <summary> 
		/// Required method for Designer support - do not modify 
		/// the contents of this method with the code editor.
		/// </summary>
		private void InitializeComponent()
		{
			this.cmd = new System.Data.OracleClient.OracleCommand();
			this.cnn = new System.Data.OracleClient.OracleConnection();
			this.adpSelect = new System.Data.OracleClient.OracleDataAdapter();
			this.adpPagingSelect = new System.Data.OracleClient.OracleDataAdapter();
			this.cmdPaging = new System.Data.OracleClient.OracleCommand();
			// 
			// cmd
			// 
			this.cmd.Connection = this.cnn;
			// 
			// cnn
			// 
			this.cnn.ConnectionString = "user id=logistika600;password=logistika600;data source=SERVER3;pooling=true;Min P" +
				"ool Size=10;Connection Lifetime=120;";
			// 
			// adpSelect
			// 
			this.adpSelect.SelectCommand = this.cmd;
			// 
			// adpPagingSelect
			// 
			this.adpPagingSelect.SelectCommand = this.cmdPaging;
			// 
			// cmdPaging
			// 
			this.cmdPaging.Connection = this.cnn;
			this.cmdPaging.Parameters.Add(new System.Data.OracleClient.OracleParameter(":BeginPaging", System.Data.OracleClient.OracleType.Number));
			this.cmdPaging.Parameters.Add(new System.Data.OracleClient.OracleParameter(":EndPaging", System.Data.OracleClient.OracleType.Number));
			// 
			// OracleDAL
			// 
			this.Name = "OracleDAL";
			this.Size = new System.Drawing.Size(32, 32);

		}
		#endregion


		private void oraAdp_RowUpdating(object sender, OracleRowUpdatingEventArgs e)
		{
			
		}
	}
}