using System;
using System.Data;
using System.Data.OleDb;
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.Collections.Generic;

namespace WebCode
{
    /// <summary>
    /// Table 的摘要说明
    /// </summary>
    public class Table
    {
        private Dictionary<string, FieldValue> dic_Table = new Dictionary<string, FieldValue>();
        private string s_tableName = "";
        private FieldValue fv_id = new FieldValue();
        private FieldValue fv_name = new FieldValue();
        private bool b_AutoIncrement = false;
        private DataSet ds = DataSet.GetInstance();
        private bool b_isExist = false;
        private bool b_trans = false;//是否使用事务
        private string s_sql = "";//使用事务返回的SQL语句

        /// <summary>
        /// 加入新数据
        /// </summary>
        /// <param name="s_tableName">表名称</param>
        public Table(string s_tableName)
        {
            GetTable(s_tableName);
        }


        public bool AutoIncrement
        {
            get
            {
                return b_AutoIncrement;
            }

            set
            {
                b_AutoIncrement = value;
            }
        }

        public bool IsTransaction
        {
            get
            {
                return b_trans;
            }
            set
            {
                b_trans = value;
            }
        }

        public string SQL
        {
            get
            {
                return s_sql;
            }
        }

        /// <summary>
        /// 获得所有字段
        /// </summary>
        /// <returns></returns>
        public List<string> GetFields()
        {
            List<string> lst_ret = new List<string>();

            foreach (KeyValuePair<string,FieldValue> kvp  in dic_Table)
            {
                lst_ret.Add(kvp.Key);
            }

            return lst_ret;
        }

        private void GetTable(string s_tableName)
        {
            s_tableName = s_tableName.ToLower();
            this.s_tableName = s_tableName;
            dic_Table = new Dictionary<string, FieldValue>();
            foreach (KeyValuePair<string, FieldValue> kvp in TableData.GetInstance()[s_tableName])
            {
                FieldValue fv = new FieldValue();
                fv.Name = kvp.Value.Name;
                fv.Type = kvp.Value.Type;
                fv.Value = "";
                dic_Table.Add(fv.Name, fv);
            }
            //找到标识字段

            foreach (KeyValuePair<string, FieldValue> var in dic_Table)
            {
                switch (var.Value.Type)
                {
                    case FieldType.Autoincrement:
                        {
                            fv_id = var.Value;
                            break;
                        }
                    case FieldType.ShowInt:
                    case FieldType.ShowChar:
                        {
                            fv_name = var.Value;
                            break;
                        }
                }


            }
        }

        public Table(int i_tableid)
        {
            TableValue tv = TableData.GetInstance()[i_tableid];
            GetTable(tv.Name);
        }


        /// <summary>
        /// 读取数据
        /// </summary>
        /// <param name="s_tableName">表名称</param>
        /// <param name="s_id">标识ID</param>
        public Table(string s_tableName, string s_id)
            : this(s_tableName)
        {
            fv_id.Value = s_id;
            //读取数据
            Read();
        }

        public FieldValue IdField
        {
            get
            {
                return fv_id;
            }
        }

        public FieldValue NameField
        {
            get
            {
                return fv_name;
            }
        }

        public string Name
        {
            get
            {
                return s_tableName;
            }
        }

        #region 数据操作

        public string MaxId()
        {
            OleDbDataReader reader = DataSet.GetInstance().GetDataReader("select max(" + fv_id.Name + ") from " + s_tableName);
            if (reader.Read())
            {
                fv_id.Value = reader.GetValue(0).ToString();
            }
            reader.Close();
            Read();
            return fv_id.Value;
        }

        /// <summary>
        /// 获得where时 不同类型等于方式不同 特别是access
        /// </summary>
        /// <param name="fv"></param>
        /// <param name="s_value"></param>
        /// <returns></returns>
        private string GetWhere()
        {
            string s_ret = "";
            switch (fv_id.Type)
            {
                case FieldType.Autoincrement:
                case FieldType.Int:
                case FieldType.ShowInt:
                case FieldType.Float:
                    {
                        s_ret = fv_id.Value;
                        break;
                    }
                default:
                    {
                        s_ret = "'" + function.GetSafeString(fv_id.Value) + "'";
                        break;
                    }
            }
            return fv_id.Name + "=" + s_ret;
        }



        private void Read()
        {
            string s_fieldList = "";


            foreach (KeyValuePair<string, FieldValue> var in dic_Table)
            {
                if (String.IsNullOrEmpty(s_fieldList))
                {
                    s_fieldList = var.Key;
                }
                else
                {
                    s_fieldList += "," + var.Key;
                }

            }

            b_isExist = false;
            OleDbDataReader reader = ds.GetDataReader("select " + s_fieldList + " from " + s_tableName + " where " + GetWhere());
            if (reader != null)
            {
                b_isExist = reader.HasRows;
                if (reader.HasRows)
                {
                    if (reader.Read())
                    {
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            string s_field = reader.GetName(i);
                            try
                            {
                                dic_Table[s_field].Value = reader.GetValue(i).ToString();
                            }
                            catch(Exception ex)
                            {

                            }
                            
                        }
                    }
                }
                reader.Close();
            }

        }

        /// <summary>
        /// 字段是否存在？
        /// </summary>
        /// <param name="s_fieldName"></param>
        /// <returns></returns>
        public bool isField(string s_fieldName)
        {
            if (dic_Table.ContainsKey(s_fieldName.ToLower()))
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public string this[string s_fieldName]
        {
            get
            {
                s_fieldName = s_fieldName.ToLower();
                if (dic_Table.ContainsKey(s_fieldName))
                {

                    return dic_Table[s_fieldName].Value;
                }
                else
                {
                    //根据类型返回
                    throw new Exception("字段在表“" + s_tableName + "”中不存在。");
                }
            }
            set
            {
                s_fieldName = s_fieldName.ToLower();
                if (dic_Table.ContainsKey(s_fieldName))
                {
                    dic_Table[s_fieldName].Value = value.ToString();
                }
            }
        }

        /// <summary>
        /// 当前的记录是否存在
        /// </summary>
        /// <returns></returns>
        public bool IsExist()
        {
            return b_isExist;
        }


        //自动判断是否保存
        public void AddOrUpdate()
        {
            if(fv_id.Value=="")
            {
                Add();
            }
            else
            {
                Update();
            }
        }

        /// <summary>
        /// 格式化值
        /// </summary>
        /// <param name="fv"></param>
        /// <returns></returns>
        private string SetValue(FieldValue fv)
        {
            string s_value = function.GetSafeString(fv.Value);
            switch (fv.Type)
            {
                case FieldType.ShowInt:
                case FieldType.Int:
                case FieldType.Autoincrement:
                case FieldType.Float:
                case FieldType.Double:
                    {
                        if (String.IsNullOrEmpty(s_value))
                        {
                            s_value = "0";
                        }
                        break;
                    }
                case FieldType.Date:
                    {
                        if (String.IsNullOrEmpty(s_value))
                        {
                            s_value = DateTime.Now.ToString("yyyy-MM-dd HH:mm");
                        }
                        break;
                    }
            }
            return s_value;
        }

        /// <summary>
        /// 增加数据
        /// </summary>
        public void Add()
        {
            string s_sql = "";
            string s_fieldList = "", s_valueList = "";
            foreach (KeyValuePair<string, FieldValue> var in dic_Table)
            {
                switch (var.Value.Type)
                {
                    case FieldType.Autoincrement:
                    case FieldType.Guid:
                        {
                            if (b_AutoIncrement)
                            {
                                //导数据的时候会用到 给变量赋值
                                if (function.GetNumber(var.Value.Value)>0)
                                {
                                    s_fieldList += "," + var.Key;
                                    string s_value = SetValue(var.Value);
                                    s_valueList += ",'" + s_value + "'";
                                }
                                
                            }
                            break;
                        }
                    case FieldType.Char:
                    case FieldType.Memo:
                        {
                            s_fieldList += "," + var.Key;
                            string s_value = SetValue(var.Value);
                            s_valueList += ",'" + s_value + "'";
                            break;
                        }
		case FieldType.Int:
                case FieldType.Float:
                    {
                        s_fieldList += "," + var.Key;
                        string s_value = SetValue(var.Value);
                        s_valueList += "," + s_value + "";
                        break;
                    }
                    default:
                        {
                            s_fieldList += "," + var.Key;
                            string s_value = SetValue(var.Value);
                            s_valueList += ",'" + s_value + "'";
                            break;
                        }

                }
            }

            if (s_fieldList.Length > 2)
            {
                s_fieldList = s_fieldList.Substring(1);
                s_valueList = s_valueList.Substring(1);
            }
            s_sql = "insert into " + s_tableName + "(" + s_fieldList + ") values (" + s_valueList + ") ";
            if (b_trans)
            {
                //使用事务不执行操作
                this.s_sql = s_sql;
            }
            else
            {
                ds.ExecuteSQL(s_sql);
                MaxId();
            }
        }

        /// <summary>
        /// 删除记录
        /// </summary>
        public void Delete()
        {
            string s_sql = "delete from " + s_tableName + " where " + GetWhere();
            if (b_trans)
            {
                //使用事务不执行操作
                this.s_sql = s_sql;
            }
            else
            {

                ds.ExecuteSQL(s_sql);
            }
        }

        /// <summary>
        /// 更新数据
        /// </summary>
        public void Update()
        {
            string s_sql = "";
            string s_valueList = "";
            foreach (KeyValuePair<string, FieldValue> var in dic_Table)
            {
                switch (var.Value.Type)
                {
                    case FieldType.Autoincrement:
                    case FieldType.Guid:
                        {
                            break;
                        }
                    case FieldType.Char:
                    case FieldType.Memo:
                        {
                            s_valueList += "," + var.Key + "='" + SetValue(var.Value) + "'";
                            break;
                        }
		case FieldType.Int:
                case FieldType.Float:
                    {
                        s_valueList += "," + var.Key + "=" + SetValue(var.Value) + "";
                        break;
                    }
                    default:
                        {
                            s_valueList += "," + var.Key + "='" + SetValue(var.Value) + "'";
                            break;
                        }

                }
            }

            if (s_valueList.Length > 2)
            {
                s_valueList = s_valueList.Substring(1);
            }
            s_sql = "update " + s_tableName + " set " + s_valueList + " where " + GetWhere();
            
            if (b_trans)
            {
                //使用事务不执行操作
                this.s_sql = s_sql;
            }
            else
            {

                ds.ExecuteSQL(s_sql);
            }
        }
        #endregion
    }

    public enum FieldType
    {
        Autoincrement = 0,
        Char = 1,
        Date = 2,
        Float = 3,
        Int = 4,
        Memo = 5,
        ShowChar = 11,
        ShowInt = 14,
        Guid = 20,
        Double=6
    }

    /// <summary>
    /// 将元数据读入到内存中保存
    /// </summary>
    public class TableData
    {
        private Dictionary<string, Dictionary<string, FieldValue>> dic_data = new Dictionary<string, Dictionary<string, FieldValue>>();
        private Dictionary<int, TableValue> dic_iddata = new Dictionary<int, TableValue>();
        private static TableData td = new TableData();
        private TableData()
        {
            //从数据库读取表数据
            Initialize();
        }

        public void Initialize()
        {
            //读取表同时自动信息
            dic_data.Clear();
            OleDbDataReader reader = DataSet.GetInstance().GetDataReader("SELECT T_Name,F_Name,F_Type,F_ID,T_ID FROM Sys_Table, Sys_Field where t_id=f_tid  ");
            while (reader.Read())
            {
                string s_tableName = reader.GetString(0).ToLower();
                string s_fieldName = reader.GetString(1).ToLower();
                Dictionary<string, FieldValue> dic_field;
                if (dic_data.ContainsKey(s_tableName))
                {
                    dic_field = dic_data[s_tableName];
                }
                else
                {
                    dic_field = new Dictionary<string, FieldValue>();
                    dic_data.Add(s_tableName, dic_field);
                }



                if (!dic_field.ContainsKey(s_fieldName))
                {
                    FieldValue fv = new FieldValue();
                    fv.Type = (FieldType)reader.GetInt32(2);
                    fv.Value = "";
                    fv.Name = s_fieldName;
                    dic_field.Add(s_fieldName, fv);
                }

                //------------------------------ 获得ID字段 
                int i_tableid = reader.GetInt32(4);
                int i_fieldid = reader.GetInt32(3);

                TableValue tv;
                if (dic_iddata.ContainsKey(i_tableid))
                {
                    tv = dic_iddata[i_tableid];
                }
                else
                {
                    tv = new TableValue();
                    tv.Name = s_tableName;
                    dic_iddata.Add(i_tableid, tv);
                }



                if (!tv.Field.ContainsKey(i_fieldid))
                {
                    FieldValue fv = new FieldValue();
                    fv.Type = (FieldType)reader.GetInt32(2);
                    fv.Value = "";
                    fv.Name = s_fieldName;
                    tv.Field.Add(i_fieldid, fv);
                }
            }
            reader.Close();
        }

        public static TableData GetInstance()
        {
            return td;
        }

        /// <summary>
        /// 返回每个表对应的字段
        /// </summary>
        /// <param name="s_tableName"></param>
        /// <returns></returns>
        public Dictionary<string, FieldValue> this[string s_tableName]
        {
            get
            {
                s_tableName = s_tableName.ToLower();
                if (dic_data.ContainsKey(s_tableName))
                {
                    Dictionary<string, FieldValue> dic_ret = new Dictionary<string, FieldValue>();
                    foreach (KeyValuePair<string, FieldValue> kvp in dic_data[s_tableName])
                    {
                        kvp.Value.Value = "";
                        dic_ret.Add(kvp.Key, kvp.Value);
                    }
                    return dic_ret;
                }

                throw new Exception("数据库中不存在名称为“" + s_tableName + "”的表。");

            }
        }

        public TableValue this[int i_tableid]
        {
            get
            {
                if (dic_iddata.ContainsKey(i_tableid))
                {
                    return dic_iddata[i_tableid];
                }
                throw new Exception("数据库中不存在ID为“" + i_tableid + "”的表。");
            }
        }
    }

    public class TableValue
    {
        public string Name;
        public Dictionary<int, FieldValue> Field = new Dictionary<int, FieldValue>();
    };

    public class FieldValue
    {
        public string Value;
        public string Name;
        public FieldType Type;
    };
}