using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Web;
using System.Xml;

namespace Neo.Legitimate.NLTISWebService.Data
{        
    public class NLTISDataBroker
    {
        private string _connectionString;
        private string _userAudit;
        private int _userID;
        private string _userIP;
        private string _userName;
        private string[] _validCommands;

        public const int DefaultProvinceID = 2; //KZN
        public const int NEW = -1;

        public string ConnectionString
        {
            get
            {
                return this._connectionString;
            }
        }

        public string UserAudit
        {
            get
            {
                return this._userAudit;
            }
        }

        public int UserID
        {
            get
            {
                return this._userID;
            }
        }

        public string UserIP
        {
            get
            {
                return this._userIP;
            }
        }

        public string UserName
        {
            get
            {
                return this._userName;
            }
        }

        public NLTISDataBroker()
        {
	        this._connectionString = ConfigurationManager.AppSettings["ConnectionString"];
            this._userID = NLTISDataBroker.ParseInt(ConfigurationManager.AppSettings["UserID"], -1);
	        this._userName = ConfigurationManager.AppSettings["UserName"];
	        this._userIP = ConfigurationManager.AppSettings["UserIP"];
	        this._userAudit = string.Concat(new string[]
	        {
		        "<XMLAUDIT username=\"",
		        this._userName,
		        "\" userip=\"",
		        this._userIP,
		        "\" workstationname=\"",
		        this._userIP,
		        "\" />"
	        });
	        this._validCommands = ConfigurationManager.AppSettings["Commands"].Split(new char[]
	        {
		        ','
	        });
        }

        public XmlDocument Execute(XmlDocument commandDoc)
        {
            XmlDocument xmlDoc = new XmlDocument();
            xmlDoc.AppendChild(xmlDoc.CreateXmlDeclaration("1.0", "utf-8", null));
            XmlElement root = xmlDoc.CreateElement("Results");
            try
            {
                using (SqlConnection connection = new SqlConnection(this.ConnectionString))
                {
                    using (SqlCommand command = connection.CreateCommand())
                    {
                        connection.Open();
                        IEnumerator enumerator = commandDoc.SelectNodes("//Commands/*").GetEnumerator();
                        try
                        {
                            while (enumerator.MoveNext())
                            {
                                XmlElement commandDef = (XmlElement)enumerator.Current;
                                this.ExecuteQuery(root, commandDef, command);
                            }
                        }
                        finally
                        {
                            IDisposable disposable = enumerator as IDisposable;
                            if (disposable != null)
                            {
                                disposable.Dispose();
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                this.SetAttribute(root, "error", ex.Message);
            }
            finally
            {
                xmlDoc.AppendChild(root);
            }
            return xmlDoc;
        }


        private void ExecuteQuery(XmlElement docRoot, XmlElement commandDef, SqlCommand command)
        {
            string commandText = this.GetAttribute(commandDef, "text");
            XmlElement root = docRoot.OwnerDocument.CreateElement(commandText);
            try
            {
                if (Array.IndexOf(this._validCommands, commandText) >= 0)
                {
                    command.CommandText = commandText;
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Clear();
                    IEnumerator enumerator = commandDef.ChildNodes.GetEnumerator();
                    try
                    {
                        while (enumerator.MoveNext())
                        {
                            XmlElement paramDef = (XmlElement)enumerator.Current;
                            SqlParameter parameter = command.Parameters.Add("@" + this.GetAttribute(paramDef, "name"), (SqlDbType)Enum.Parse(typeof(SqlDbType), this.GetAttribute(paramDef, "type"), true), NLTISDataBroker.ParseInt(this.GetAttribute(paramDef, "size"), 0));
                            parameter.Value = this.GetAttribute(paramDef, "value");
                        }
                    }
                    finally
                    {
                        IDisposable disposable = enumerator as IDisposable;
                        if (disposable != null)
                        {
                            disposable.Dispose();
                        }
                    }
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            StringBuilder err = new StringBuilder();
                            XmlElement row = docRoot.OwnerDocument.CreateElement("Row");
                            for (int i = 0; i < reader.FieldCount; i++)
                            {
                                try
                                {
                                    this.SetAttribute(row, reader.GetName(i), NLTISDataBroker.ToString(reader[i]));
                                }
                                catch (Exception ex)
                                {
                                    err.Append(reader.GetName(i));
                                    err.Append(": ");
                                    err.Append(ex.Message);
                                    err.Append("\n");
                                }
                            }
                            if (err.Length > 0)
                            {
                                this.SetAttribute(row, "error", err.ToString());
                            }
                            root.AppendChild(row);
                        }
                    }
                }
            }
            catch (Exception ex2)
            {
                this.SetAttribute(root, "error", ex2.Message);
            }
            finally
            {
                docRoot.AppendChild(root);
            }
        }

        protected string GetAttribute(XmlElement element, string attribute)
        {
            XmlAttribute attr;
            string result;
            if ((attr = element.Attributes[attribute]) != null)
            {
                result = attr.Value;
            }
            else
            {
                result = null;
            }
            return result;
        }

        public static bool IsNull(object value)
        {
            return Convert.IsDBNull(value) || value == null;
        }

        public static bool ParseBool(string s, bool defaultValue)
        {
            bool result;
            if (s == null || s.Length == 0)
            {
                result = defaultValue;
            }
            else
            {
                result = bool.Parse(s);
            }
            return result;
        }

        public static object ParseDbDate(string s)
        {
            object result;
            if (s == null || s.Length == 0)
            {
                result = DBNull.Value;
            }
            else
            {
                result = DateTime.Parse(s);
            }
            return result;
        }

        public static object ParseDbString(string s)
        {
            object result;
            if (s != null && s.Length > 0)
            {
                result = s;
            }
            else
            {
                result = string.Empty;
            }
            return result;
        }

        public static float ParseFloat(string s, float defaultValue)
        {
            float result;
            if (s == null || s.Length == 0)
            {
                result = defaultValue;
            }
            else
            {
                result = float.Parse(s);
            }
            return result;
        }


        public static string ParseInitials(string firstName, string lastName)
        {
            string s = "";
            if (firstName != null && firstName.Length > 0)
            {
                s += firstName.Substring(0, 1);
            }
            if (lastName != null && lastName.Length > 0)
            {
                s += lastName.Substring(0, 1);
            }
            return s.ToUpper();
        }

        public static int ParseInt(string s, int defaultValue)
        {
            int result;
            if (s == null || s.Length == 0)
            {
                result = defaultValue;
            }
            else
            {
                result = int.Parse(s);
            }
            return result;
        }


        protected void SetAttribute(XmlElement element, string attribute, string value)
        {
            XmlAttribute attr;
            if ((attr = element.Attributes[attribute]) == null)
            {
                attr = element.OwnerDocument.CreateAttribute(attribute);
                element.Attributes.Append(attr);
            }
            attr.Value = value;
        }

        public static string ToDateString(object value)
        {
            string result;
            if (NLTISDataBroker.IsNull(value))
            {
                result = string.Empty;
            }
            else
            {
                if (value is DateTime)
                {
                    DateTime dateTime = (DateTime)value;
                    result = dateTime.ToString("dd-MMM-yyyy");
                }
                else
                {
                    result = value.ToString();
                }
            }
            return result;
        }

        public static string ToFloatString(object value)
        {
            string result;
            if (NLTISDataBroker.IsNull(value))
            {
                result = string.Empty;
            }
            else
            {
                result = float.Parse(value.ToString()).ToString("0.00");
            }
            return result;
        }


        public static string ToString(object value)
        {
            string result;
            if (NLTISDataBroker.IsNull(value))
            {
                result = string.Empty;
            }
            else
            {
                result = value.ToString();
            }
            return result;
        }


        public static string ToString(Exception ex)
        {
            string result;
            if (ex is SqlException)
            {
                SqlException sex = (SqlException)ex;
                result = string.Format("SQLEX in {0} at line# {1}: {2}", sex.Procedure, sex.LineNumber, sex.Message);
            }
            else
            {
                result = ex.Message;
            }
            return result;
        }




    }
}