using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;
using System.Collections.Generic;
using System.Text;
using System.Text.RegularExpressions;

namespace WebCode
{
    /// <summary>
    /// DataSet 的摘要说明
    /// </summary>
    public class DataSet
    {
        private static DataSet dateset = new DataSet();

        private DataSet()
        {
            //只为删除内容 保证多点删除
           
            
        }

        public static DataSet GetInstance()
        {
            return dateset;
        }



        //
        // TODO: 在此处添加构造函数逻辑
        //

        /// <summary>
        /// 链接从外面传入 返回一个reader ，使用完成后要立即关闭。
        /// </summary>
        /// <param name="SQL"></param>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        public OleDbDataReader GetDataReader(string SQL)
        {
            OleDbDataReader reader = null;
            OleDbCommand command = null;
            OleDbConnection connection = GetConnetion();
            try
            {
                command = new OleDbCommand(GetRightSQL(SQL), connection);
                reader = command.ExecuteReader(CommandBehavior.CloseConnection);

            }
            catch (System.Exception ex)
            {
                TempLog.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name).WriteLog(System.Reflection.MethodBase.GetCurrentMethod() + "->  | error=" + ex.Message);
            }
            finally
            {
                //reader关闭后， 数据库关闭连接也同时关闭
            }
            return reader;
        }

        public bool IsExist(string s_sql)
        {
            bool b_ret = false;
            OleDbDataReader reader = GetDataReader(s_sql);
            if (reader.Read())
                b_ret = true;
            else
                b_ret = false;

            reader.Close();

            return b_ret;
        }

        public string GetLastData(string s_column, string s_value)
        {
            return GetLastDataEnc(s_column, s_value);
        }

        private string GetLastDataEnc(string s_column, string s_value)
        {
            //得到最后的数据
            string s_ret = s_value;

            if (string.IsNullOrEmpty(s_column))
                return "";

            if (s_column.Substring(0, 1) == "_")
            {

                string[] s_con = s_column.Split('_');
                string s_name = s_con[1].ToLower();

                if (s_con.Length > 2)
                {
                    if (function.GetNumber(s_con[2]) > 0)
                    {
                        //截取字段
                        s_value = function.GetLimitChar(s_value, Int32.Parse(s_con[2]));
                    }
                }

                //格式化输出内容 _HTML_Content
                switch (s_name)
                {
                    
                    case "price":
                        {
                            s_ret = Convert.ToDouble(s_value).ToString("0.00");
                            break;
                        }
                    case "date": // 2010-02-02
                        {
                            s_ret = function.GetDate(s_value);
                            break;
                        }
                    case "time": // 2010-02-02 10:11:11
                        {
                            s_ret = function.GetTime(s_value);
                            break;
                        }
                    case "mtime": // 2010-02-02 10:11
                        {
                            s_ret = function.GetMTime(s_value);
                            break;
                        }
                    case "path":
                        {
                            //变换路径
                            s_ret = HttpContext.Current.Server.UrlEncode(s_value);
                            break;
                        }
                    case "url":
                        {
                            //格式化HTML内容
                            s_ret = HttpUtility.UrlEncode(s_value);
                            break;
                        }
                    case "nocrlf":
                        {
                            //格式化HTML内容
                            s_ret = Regex.Replace(s_value, "\r\n", "<br />", RegexOptions.IgnoreCase);
                            break;
                        }
                    case "br":
                        {
                            //格式化HTML内容
                            s_ret = s_value.Replace("\r\n", "<br />");
                            break;
                        }
                    case "html":
                        {
                            //格式化HTML内容
                            s_ret = function.GetHTMLOutput(s_value);
                            break;
                        }
                    case "temp"://
                    case "const":
                    case "csharp":
                        {
                            //动态转换内容
                            Translate ts = new Translate();


                            List<string> lst_para = new List<string>();
                            lst_para.Add(s_value);

                            switch (s_name)
                            {
                                case "temp"://
                                    {
                                        s_ret = ts.TranslateTemp(s_con[2], lst_para);
                                        break;
                                    }
                                case "const":
                                    {
                                        s_ret = ts.TranslateConst(s_con[2], lst_para);
                                        break;
                                    }
                                case "csharp":
                                    {
                                        s_ret = ts.TranslateCSharp(s_con[2], lst_para.ToArray());
                                        break;
                                    }
                            }
                            break;
                        }
                    case "autotitle"://主要为a链接对象考虑的 当字段被截取后 显示长的title
                        {
                            if (function.GetNumber(s_con[2]) > 0)
                            {
                                if (function.GetStringLength(s_ret) > Int32.Parse(s_con[2]))
                                {
                                    s_ret = " title=\"" + s_ret + "\"";
                                }
                                else
                                    s_ret = "";
                            }
                            break;
                        }
                    default:
                        {


                            if (s_name == "showhtml")
                            {
                                s_ret = function.GetPageHTMLOutput(s_value);
                            }
                            else
                            {
                                s_ret = s_value;
                            }


                            break;
                        }
                }

            }

            return s_ret;
        }

        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="s_table"></param>
        /// <param name="s_del"></param>
        /// <returns></returns>
        public bool DeleteData(string s_table, string s_del)
        {
            int i_ret = 0;
            if (CheckSourceUrl())
            {
                if (!String.IsNullOrEmpty(s_table))
                {
                    Table table = new Table(s_table);
                    i_ret = ExecuteSQL(" delete from " + s_table + " where " + table.IdField.Name + " in (" + s_del + ") ");
                }
            }

            return i_ret > 0;
        }

        public bool DeleteData()
        {
            return DeleteData(function.GetPara("OperateTable"), function.GetPara("itemid"));
        }

        
        


        /// <summary>
        /// 事务执行SQL语句
        /// </summary>
        /// <param name="lst_sql"></param>
        /// <returns></returns>
        public bool TransactionSQL(List<string> lst_sql)
        {
            bool b_ret = false;
            OleDbConnection o_conn = GetConnetion();
            OleDbCommand o_command = new OleDbCommand();
            OleDbTransaction o_trans;

            o_trans = o_conn.BeginTransaction();
            o_command.Connection = o_conn;
            o_command.Transaction = o_trans;
            try
            {
                for (int i = 0; i < lst_sql.Count; i++)
                {
                    o_command.CommandText = lst_sql[i];
                    o_command.ExecuteNonQuery();
                }
                o_trans.Commit();
                b_ret = true;
            }
            catch (Exception ex)
            {
                b_ret = false;
                o_trans.Rollback();
                TempLog.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name).WriteLog(System.Reflection.MethodBase.GetCurrentMethod() + "->  | error=" + ex.Message);
            }

            o_conn.Close();

            return b_ret;
        }


        /// <summary>
        /// 执行事务 保存Form
        /// </summary>
        /// <param name="s_table"></param>
        /// <param name="s_id"></param>
        /// <param name="dic_data"></param>
        /// <returns></returns>
        public string SaveDataTransaction(string s_table, string s_id, Dictionary<string, string> dic_data)
        {
            string s_sql = "";
            if (CheckSourceUrl())
            {
                if (!String.IsNullOrEmpty(s_table))
                {

                    Table table = new Table(s_table, s_id);
                    table.IsTransaction = true;

                    foreach (KeyValuePair<string, string> var in dic_data)
                    {
                        string s_field = var.Key.ToLower();
                        if (table.isField(s_field))
                        {
                            table[s_field] = var.Value;
                        }
                    }

                    if (String.IsNullOrEmpty(s_id))
                        table.Add();
                    else
                    {
                        table.IdField.Value = s_id;
                        table.Update();
                    }

                    s_sql = table.SQL;
                }
            }

            if (String.IsNullOrEmpty(s_sql))
            {
                throw new Exception("没有为表“" + s_table + "”生成语句。");
            }

            return s_sql;
        }

        
        /// <summary>
        /// 获得格式化好的数据字符串
        /// </summary>
        /// <param name="s_SQL"></param>
        /// <param name="s_temp"></param>
        /// <returns></returns>
        public string GetDataString(string s_SQL, string s_temp)
        {
            if (String.IsNullOrEmpty(s_SQL))
                return "";

            List<string> lst_get = GetDataList(s_SQL, s_temp);
            StringBuilder sb_ret = new StringBuilder();
            for (int i = 0; i < lst_get.Count; i++)
            {
                sb_ret.Append(lst_get[i]);
            }
            return sb_ret.ToString();
        }

        /// <summary>
        /// 打开数据库
        /// </summary>
        /// <returns></returns>
        public OleDbConnection GetConnetion()
        {
            OleDbConnection o_conn = new OleDbConnection();
            string s_connect =  function.GetConfigValue("ConnectionString");

            o_conn = new OleDbConnection(s_connect);
            o_conn.Open();
            return o_conn;
        }

        

        public int ExecuteSQL(string s_sql)
        {
            int i_ret = 0;
            OleDbConnection o_conn = GetConnetion();
            OleDbCommand o_command = new OleDbCommand(GetRightSQL(s_sql), o_conn);
            try
            {
                i_ret = o_command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                
                
            }
            
            o_conn.Close();

            return i_ret;
        }

        /// <summary>
        /// 获得格式化好的数据列表
        /// </summary>
        /// <param name="s_SQL"></param>
        /// <param name="s_temp"></param>
        /// <returns></returns>
        /// 
        public List<string> GetDataList(string s_SQL, string s_temp)
        {
            return GetDataListEnc(s_SQL, s_temp);
        }

        private List<string> GetDataListEnc(string s_SQL, string s_temp)
        {
            List<string> lst_ret = new List<string>();
            int i_cyc = 0;
            string s_ret = "";
            string s_para = "";


            //只能是查询语句 其他语句屏蔽
            if (!Regex.IsMatch(s_SQL, @"^\s*?select", RegexOptions.IgnoreCase))
                return lst_ret;

            if (string.IsNullOrEmpty(s_SQL))
                return lst_ret;

            OleDbConnection o_conn = GetConnetion();

            try
            {
                OleDbCommand o_cmd = new OleDbCommand(GetRightSQL(s_SQL), o_conn);
                o_cmd.CommandTimeout = 180;
                OleDbDataReader o_read = o_cmd.ExecuteReader();
                string s_field = "", s_value = "";
                int i_index = 0;
                while (o_read.Read())
                {
                    i_index++;
                    s_ret = s_temp;
                    for (i_cyc = 0; i_cyc < o_read.FieldCount; i_cyc++)
                    {
                        s_field = o_read.GetName(i_cyc);
                        s_value = o_read[i_cyc].ToString().Trim();
                        s_para = "@" + s_field + "@";

                        if (s_field.ToLower() == "itemnumber")
                        {
                            //自动计数
                            s_value = i_index.ToString();
                        }
                        else
                        {


                            s_value = GetLastData(s_field, s_value);
                        }
                        s_ret = Regex.Replace(s_ret, s_para, s_value, RegexOptions.IgnoreCase);

                    }
                    lst_ret.Add(s_ret);
                }
                o_read.Close();
            }
            catch (System.Exception ex)
            {
                TempLog.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name).WriteLog(System.Reflection.MethodBase.GetCurrentMethod() + "->  | error=" + ex.Message);
            }
            finally
            {
                if (o_conn.State.ToString() == "Open")
                    o_conn.Close();

                o_conn = null;
            }

            return lst_ret;
        }

        /// <summary>
        /// 将数据读取保存到内存中
        /// </summary>
        /// <param name="s_sql"></param>
        /// <returns></returns>
        public List<Dictionary<string, string>> GetDataSet(string s_sql)
        {
            List<Dictionary<string, string>> lst_ret = new List<Dictionary<string, string>>();
            OleDbDataReader reader = GetDataReader(s_sql);
            while (reader.Read())
            {
                Dictionary<string, string> dic_data = new Dictionary<string, string>();
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    dic_data.Add(reader.GetName(i).ToLower(), reader.GetValue(i).ToString());
                }
                lst_ret.Add(dic_data);
            }
            reader.Close();

            return lst_ret;
        }

    }
}