using IBM.Data.DB2;
using System.Data.Odbc;
using System;
using System.Data;
using System.Runtime.Serialization;
using System.Xml;
using System.IO;
using System.Configuration;
using System.Web;
namespace DataAgent
{
    public enum ParameterDirection
	{
		Input = System.Data.ParameterDirection.Input,
		InputOutput = System.Data.ParameterDirection.InputOutput,
		Output = System.Data.ParameterDirection.Output,
		ReturnValue = System.Data.ParameterDirection.ReturnValue
	}
    public class DB2ADO
	{
		private readonly DB2Connection cn;
		private readonly DB2Command cd;
        private DB2Transaction tr;
		private DB2DataAdapter da;
		public DB2ParameterCollection parameters = null;

		public enum DbType
		{
			BigInt = DB2Type.BigInt,
			Binary = DB2Type.Binary,
			Blob = DB2Type.Blob,
			Char = DB2Type.Char,
			Clob = DB2Type.Clob,
			Datalink = DB2Type.Datalink,
			Date = DB2Type.Date,
			DbClob = DB2Type.DbClob,
			Decimal = DB2Type.Decimal,
			Double = DB2Type.Double,
			Float = DB2Type.Float,
			Graphic = DB2Type.Graphic,
			Integer = DB2Type.Integer,
			Invalid = DB2Type.Invalid,
			LongVarBinary = DB2Type.LongVarBinary,
			LongVarChar = DB2Type.LongVarChar,
			LongVarGraphic = DB2Type.LongVarGraphic,
			Numeric = DB2Type.Numeric,
			Real = DB2Type.Real,
			RowId = DB2Type.RowId,
			SmallInt = DB2Type.SmallInt,
			Time = DB2Type.Time,
			Timestamp = DB2Type.Timestamp,
			VarBinary = DB2Type.VarBinary,
			VarChar = DB2Type.VarChar,
			VarGraphic = DB2Type.VarGraphic,
        }

        public enum CommandType
		{
			StoredProcedure = System.Data.CommandType.StoredProcedure,
			TableDirect = System.Data.CommandType.TableDirect,
			Text = System.Data.CommandType.Text
		}

		public DB2ADO()
		{
			cn = new DB2Connection();
			cd = new DB2Command();
		}

        private DataSet executeDB2ADOSQL(string connection, string SQL, string DatasetName, CommandType CommandType)
        {
            DataSet results = new DataSet();
            DateTime connStart = DateTime.Now;
            cn.ConnectionString = connection;
            cd.CommandTimeout = 120;
            cd.CommandText = SQL;
            cd.CommandType = (System.Data.CommandType)CommandType;

            try
            {
                if (DatasetName.Length == 0)
                {
                    DatasetName = "Dataset";
                }

                cn.Open();
                tr = cn.BeginTransaction();
                cd.Transaction = tr;
                cd.Connection = cn;
                da = new DB2DataAdapter(cd);

                results.DataSetName = DatasetName;

                da.Fill(results);

                if (parameters != null)
                {
                    DataTable tblParameters = results.Tables.Add("Parameters");
                    results.Tables["Parameters"].Columns.Add("ParameterName");
                    results.Tables["Parameters"].Columns.Add("ParameterValue");

                    for (int i = 0; i < parameters.Count; i++)
                    {
                        DB2Parameter param;
                        param = parameters[i];
                        if (param.Direction.Equals(ParameterDirection.InputOutput) || (param.Direction.Equals(ParameterDirection.Output) || (param.Direction.Equals(ParameterDirection.ReturnValue))))
                        {
                            DataRow row;
                            row = results.Tables["Parameters"].NewRow();
                            row["ParameterName"] = param.ParameterName;
                            row["ParameterValue"] = param.Value;
                            results.Tables["Parameters"].Rows.Add(row);
                        }
                    }
                    parameters.Clear();
                }
                tr.Commit();
            }
            catch (OdbcException errODBC)
            {
                tr.Rollback();
                XmlDocument errXML = new XmlDocument();
                errXML.AppendChild(errXML.CreateNode(XmlNodeType.Element, "ERRORS", ""));

                for (int i = 0; i < errODBC.Errors.Count; i++)
                {

                    XmlNode node = errXML.CreateNode(XmlNodeType.Element, "ERROR", "");
                    node.Attributes.Append(errXML.CreateAttribute("SQLState")).Value = errODBC.Errors[i].SQLState.ToString();
                    node.Attributes.Append(errXML.CreateAttribute("Source")).Value = errODBC.Errors[i].Source.ToString();
                    node.Attributes.Append(errXML.CreateAttribute("Message")).Value = errODBC.Errors[i].Message;
                    errXML.DocumentElement.AppendChild(node);
                }
                throw new Exception(errXML.OuterXml);
            }
            catch (Exception errSys)
            {
                throw errSys;
            }
            finally
            {
                if (cn != null)
                {
                    cn.Close();
                    cn.Dispose();
                    cd.Dispose();
                }
                if (da != null)
                {
                    da.Dispose();
                }
                DateTime connEnd = DateTime.Now;
                WriteDbLog(connStart, connEnd, DatasetName);
            }

            return results;
        }
        /// <summary>
        /// Method executes an SQL statement against an ODBC data source.  This method should only
        /// be used for raw SELECT, INSERT, UPDATE, DELETE statements -- not stored procedure calls.
        /// </summary>
        /// <param name="Connection"></param>
        /// <param name="SQL"></param>
        /// <param name="DatasetName"></param>
        /// <returns></returns>
        public DataSet executeSQL(string Connection, string SQL, string DatasetName)
		{
			return executeDB2ADOSQL(Connection, SQL, DatasetName, CommandType.Text);
		}

		/// <summary>
		/// Method executes a stored procedure against an ODBC data source.  Any parameters needed for the
		/// stored procedure should be added via the "addParameter()" method call.
		/// </summary>
		/// <param name="Connection"></param>
		/// <param name="StoredProcedureName"></param>
		/// <param name="DatasetName"></param>
		/// <returns></returns>
		public DataSet executeStoredProcedure(string Connection, string StoredProcedureName, string DatasetName)
		{
			return executeDB2ADOSQL(Connection, StoredProcedureName, DatasetName, CommandType.StoredProcedure);
		}

		/// <summary>
		/// Method adds a parameter to the collection of parameters to be used in a call against an ODBC data source
		/// </summary>
		/// <param name="Name"></param>
		/// <param name="Value"></param>
		/// <param name="Type"></param>
		/// <param name="Direction"></param>
		/// <param name="Size"></param>
		public void addParameter(string Name, object Value, DbType Type, ParameterDirection Direction, int Size)
		{
			DB2Parameter param = new DB2Parameter();

			if (parameters == null)
			{
				parameters = cd.Parameters;
			}			
			param.ParameterName = Name;
			param.Value = Value;
			param.DB2Type = (DB2Type)Type;
			param.Direction = (System.Data.ParameterDirection)Direction;
			param.Size = Size;
			parameters.Add(param);
        }
        /// <summary>
        /// Write log for tracking. 
        /// </summary>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <param name="fnName"></param>
        public void WriteDbLog(DateTime startTime, DateTime endTime, string fnName)
        {
            try
            {

                if (ConfigurationManager.AppSettings["EnableDbLogging"].ToString().Trim() == "Y")
                {
                    string sessionId = HttpContext.Current.Session["sSessionID"].ToString();
                    string strFileName = "DirectBillDBLog" + "_" + sessionId + ".csv";
                    string sPath = ConfigurationManager.AppSettings["sharedpath"].ToString() + "\\"
                        + ConfigurationManager.AppSettings["sharedfolder"].ToString()
                        + "\\" + DateTime.Now.Year.ToString() + "\\"
                        + DateTime.Now.ToString("MMM");

                    if (!Directory.Exists(sPath))
                    {
                        Directory.CreateDirectory(sPath);
                    }
                    string strFilePath = Path.Combine(sPath, strFileName);
                    if (!File.Exists(strFilePath))
                    {
                        using (StreamWriter loghdr = new StreamWriter(strFilePath, true))
                        {
                            loghdr.WriteLine("\"SessionID\",\"Method\",\"Start Time\",\"End Time\",\"Elapsed Time\"");
                        }
                    }
                    using (StreamWriter logdtl = new StreamWriter(strFilePath, true))
                    {
                        TimeSpan elapsed = endTime - startTime;
                        string elapsedstr = elapsed.TotalMilliseconds.ToString();
                        string startTimestr = startTime.ToString("yyyy'-'MM'-'dd'-'HH'.'MM'.'ss'.'ffffff").ToString();
                        string endTimestr = endTime.ToString("yyyy'-'MM'-'dd'-'HH'.'MM'.'ss'.'ffffff").ToString(); ;
                        string strLogMessage = sessionId + "," + fnName + "," + startTimestr + "," + endTimestr + "," + elapsedstr;
                        if (strLogMessage != string.Empty)
                        {
                            logdtl.WriteLine(strLogMessage);
                        }
                    }
                }

            }

            catch (Exception ex)
            {
                throw (ex);
            }
        }

    }
}