using Oracle.DataAccess.Client;
using System.Configuration;
using System.Data;
using System;

namespace 优软MES.DataOperate
{

    class DataHelper
    {
        public static readonly string ConnectionStrings = ConfigurationSettings.AppSettings["DatabaseConnection"];
        OracleCommand command = null;
        OracleConnection connection = new OracleConnection(ConnectionStrings);

        public DataHelper() {
            connection.Open();
        }

        /// <summary>
        /// 根据表名获取该表字段数据类型
        /// </summary>
        public DataTable getColumnDataType(string TableName) {
            DataTable dt = new DataTable();
            command = new OracleCommand("select Column_Name,Data_Type from cols where TABLE_name=upper('"+TableName+"')", connection);
            OracleDataAdapter ad = new OracleDataAdapter(command);
            ad.Fill(dt);
            return dt;
        }

        /// <summary>
        /// 获取第一行第一列的信息
        /// </summary>
        public object getFieldDataByCondition(string TableName,string Field,string Condition) {
            DataTable dt = new DataTable();
            string sql = "select "+Field+" from " +TableName +" where "+Condition;
            command = new OracleCommand();
            command.CommandText = sql;
            OracleDataAdapter ad = new OracleDataAdapter();
            ad.SelectCommand = command;
            ad.Fill(dt);
            return dt.Rows[0][0];
        }

        /// <summary>
        /// 获取指定表的数量
        /// </summary>
        /// <returns></returns>
        public int getRowCount(string TableName) {
            DataTable dt = new DataTable();
            string sql = "select count(1) from "+TableName;
            command = new OracleCommand(sql, connection);
            OracleDataAdapter ad = new OracleDataAdapter(command);
            ad.Fill(dt);
            int count =int.Parse(dt.Rows[0][0].ToString());
            return count;
        }

        /// <summary>
        /// 通过表名和获取单行的记录
        /// </summary>
        public DataTable getFieldsDataByCondition(string TableName,string[] Fields,string Condition) {
            DataTable dt = new DataTable();
            string sql = "select " ;
            sql += AddField(Fields);
            sql += " from " + TableName + " where " + Condition +"and rownum=1";
            Console.Write(sql);
            command = new OracleCommand(sql, connection);
            OracleDataAdapter ad = new OracleDataAdapter(command);
            ad.Fill(dt);
            return dt;
        }

        /// <summary>
        /// 将DataTable导入到指定的表中
        /// </summary>
        /// <param name="DataTable"></param>
        /// <param name="TableName"></param>
        public void InsertDataTable(DataTable DataTable ,string TableName) {
            for (int i = 0; i < DataTable.Rows.Count; i++) {
                for (int j = 0; j < DataTable.Columns.Count; j++) {

                }
            }
        }

        /// <summary>
        /// 通过表名，字段获取DataTable类型的分页数据
        /// </summary>
        // SELECT * FROM (SELECT  RN A.*, ROWNUM  FROM (SELECT* FROM datalist) A WHERE ROWNUM <= 50) WHERE RN >= 21
        public DataTable getFieldsDatasByPageing(string TableName,string[] Fields,int CurrentPage,int PageSize) {
            string sql = "select * from (select ROWNUM RN,A.* from (select ";
            DataTable dt = new DataTable();
            sql += AddField(Fields);
            sql += " from " + TableName +") A where ROWNUM <="+CurrentPage*PageSize+") where RN>"+ (CurrentPage - 1)* PageSize;
            command = new OracleCommand(sql, connection);
            OracleDataAdapter ad = new OracleDataAdapter(command);
            ad.Fill(dt);
            return dt;
        }

        /// <summary>
        /// 通过表名，字段和条件获取DataTable类型的数据
        /// </summary>
        public DataTable getFieldsDatasByCondition(string TableName, string[] Fields, string Condition)
        {
            DataTable dt = new DataTable();
            string sql = "select ";
            sql += AddField(Fields);
            sql += " from " + TableName + "where " + Condition;
            command = new OracleCommand(sql, connection);
            OracleDataAdapter ad = new OracleDataAdapter(command);
            ad.Fill(dt);
            return dt;
        }

        /// <summary>
        /// 通过表名，字段获取DataTable类型的数据
        /// </summary>
        public DataTable getFieldsDatas(string TableName, string[] Fields)
        {
            DataTable dt = new DataTable();
            string sql = "select ";
            sql += AddField(Fields);
            sql += " from " + TableName;
            command = new OracleCommand(sql, connection);
            OracleDataAdapter ad = new OracleDataAdapter(command);
            ad.Fill(dt);
            return dt;
        }
        /// <summary>
        /// 根据指定条件更新Table
        /// </summary>
        /// <param name="TableName"></param>
        /// <param name="UpdateStatement"></param>
        /// <param name="Condition"></param>
        public void UpDateTableByCondition(string TableName,string UpdateStatement,string Condition) {

        }
        /// <summary>
        /// 执行SQL的方法获取数据
        /// </summary>
        /// <param name="SQL"></param>
        /// <returns></returns>
        public DataTable ExecuteSql(string SQL) {
            DataTable dt = new DataTable();
            command = new OracleCommand(SQL, connection);
            OracleDataAdapter ad = new OracleDataAdapter(command);
            ad.Fill(dt);
            return dt;
        }

        public string AddField(string[]  Fields) {
            string sql = " " ;
            foreach (string field in Fields)
            {
                if (!field.Equals(Fields[Fields.Length - 1]))
                {
                    sql += field + ",";
                }
                else
                {
                    sql += field;
                }
            }
            return sql;
        }
    }
}