using System.Data;
using MES.Service.util;
using Oracle.ManagedDataAccess.Client;

namespace MES.Service.DB;

/// <summary>
///     用于Oracle数据库访问的帮助类
/// </summary>
public class OracleSQLHelper
{
    private readonly string _connectionString;

    public readonly string ConnectionString =
        AppsettingsUtility.Settings.DataBaseConn;

    //public static string connectionString = "Data Source = 192.168.1.19; Persist Security Info=True;User ID = abtdev; Password=abt_dev";
    public string connectionString =
        "Data Source = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.251)(PORT = 1521))(CONNECT_DATA = (SERVICE_NAME = ORCL))); Persist Security Info=True;User ID = sg_prd; Password=sgprd"; // 这里替换成你的实际连接字符串

    public OracleSQLHelper()
    {
        _connectionString = connectionString;
    }

    public OracleSQLHelper(string connectionString)
    {
        _connectionString = connectionString;
    }

    public DataTable ExecuteQuery(string query)
    {
        using (var connection = new OracleConnection(_connectionString))
        {
            using (var command = new OracleCommand(query, connection))
            {
                connection.Open();
                var dataTable = new DataTable();
                using (var reader = command.ExecuteReader())
                {
                    dataTable.Load(reader);
                }

                return dataTable;
            }
        }
    }

    // 执行非查询操作 对数据库进行 增、删、改 操作(1） 
    public int ExecuteNonQuery(string query)
    {
        using (var connection = new OracleConnection(_connectionString))
        {
            using (var command = new OracleCommand(query, connection))
            {
                connection.Open();
                return command.ExecuteNonQuery();
            }
        }
    }

    public int ExecuteNonQueryWithTransaction(OracleConnection connection,
        OracleTransaction transaction, string query)
    {
        using (var command = connection.CreateCommand())
        {
            command.Transaction = transaction;
            command.CommandText = query;
            return command.ExecuteNonQuery();
        }
    }

    public int ExecuteTransaction(string query1)
    {
        using (var connection = new OracleConnection(_connectionString))
        {
            connection.Open();

            var transaction = connection.BeginTransaction();
            var totalRowsAffected = 0;

            try
            {
                totalRowsAffected =
                    ExecuteNonQueryWithTransaction(connection, transaction,
                        query1);

                transaction.Commit();
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                throw new Exception(ex.Message);
            }

            return totalRowsAffected;
        }
    }


    // 执行查询并返回单个值
    public object ExecuteScalar(string query)
    {
        using (var connection = new OracleConnection(_connectionString))
        {
            using (var command = new OracleCommand(query, connection))
            {
                connection.Open();
                return command.ExecuteScalar();
            }
        }
    }


    // 执行存储过程
    //OracleParameter[] parameters = new OracleParameter[]
    //{
    //    new OracleParameter("param1", value1),
    //    new OracleParameter("param2", value2)
    //};
    //    sqlHelper.ExecuteStoredProcedure("存储过程名称", parameters);
    public void ExecuteStoredProcedure(string procedureName,
        OracleParameter[] parameters)
    {
        using (var connection = new OracleConnection(_connectionString))
        {
            using (var command =
                   new OracleCommand(procedureName, connection))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddRange(parameters);
                connection.Open();
                command.ExecuteNonQuery();
            }
        }
    }

    public DataTable ExecuteDataTable(string sql)
    {
        return ExecuteDataTable(sql, CommandType.Text, null);
    }

    public DataTable ExecuteDataTable(string sql, CommandType commandType)
    {
        return ExecuteDataTable(sql, commandType, null);
    }

    public DataTable ExecuteDataTable(string sql, CommandType commandType,
        OracleParameter[] parameters)
    {
        var data = new DataTable();
        using (var connection = new OracleConnection(_connectionString))
        {
            using (var command = new OracleCommand(sql, connection))
            {
                command.CommandType = commandType;
                if (parameters != null)
                    command.Parameters.AddRange(parameters);

                using (var adapter = new OracleDataAdapter(command))
                {
                    adapter.Fill(data);
                }
            }
        }

        return data;
    }


    // public int ExecuteStoredProcedure(string procedureName, params OracleParameter[] parameters)
    // {
    //     int result = 0; // 存储过程返回值
    //
    //     // 创建数据库连接对象并指定连接字符串
    //     using (OracleConnection connection =
    //            new OracleConnection(connectionString))
    //     {
    //         // 创建命令对象并指定要执行的存储过程名称
    //         using (OracleCommand command =
    //                new OracleCommand(procedureName, connection))
    //         {
    //             command.CommandType = CommandType.StoredProcedure;
    //
    //             // 添加存储过程参数
    //             foreach (OracleParameter parameter in parameters)
    //             {
    //                 command.Parameters.Add(parameter);
    //             }
    //
    //             // 添加输出参数
    //             OracleParameter outputParameter =
    //                 new OracleParameter("C_Result", OracleDbType.Int32);
    //             outputParameter.Direction = ParameterDirection.Output;
    //             command.Parameters.Add(outputParameter);
    //
    //             OracleParameter messageParameter =
    //                 new OracleParameter("C_Msg", OracleDbType.Varchar2,
    //                     1000);
    //             messageParameter.Direction = ParameterDirection.Output;
    //             command.Parameters.Add(messageParameter);
    //
    //             try
    //             {
    //                 // 打开数据库连接并执行存储过程
    //                 connection.Open();
    //                 command.ExecuteNonQuery();
    //
    //                 // 获取输出参数的值
    //                 OracleDecimal oracleDecimalValue =
    //                     (OracleDecimal)outputParameter.Value;
    //                 decimal decimalValue = 0;
    //
    //                 if (oracleDecimalValue != OracleDecimal.Null)
    //                 {
    //                     decimalValue = oracleDecimalValue.Value;
    //                 }
    //
    //                 result = Convert.ToInt32(decimalValue);
    //                 string message = messageParameter.Value.ToString();
    //                 if (result == 1)
    //                 {
    //                     throw new Exception(message);
    //                 }
    //             }
    //             catch (Exception ex)
    //             {
    //                 // 处理异常
    //                 throw new Exception(ex.Message);
    //             }
    //         }
    //     }
    //     return result;
    // }
}