using APT.Infrastructure.Core;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore.Storage;
//using MySql.Data.MySqlClient;
//using Npgsql;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
namespace APT.Infrastructure.Api
{

    /// <summary>
    /// 领域服务基类
    /// </summary>
    /// <typeparam name="TRepository">领域实体仓储对象</typeparam>
    /// <typeparam name="TEntity">领域实体</typeparam>
    /// <typeparam name="TKey">领域实体主键类型</typeparam>
    public class DomainServiceBase : IDomainService
    {
        private IUnitOfWork _unitOfWork;
        public DomainServiceBase(IUnitOfWork unitOfWork)
        {
            _unitOfWork = unitOfWork;
        }

        #region properties

        protected IUnitOfWork UnitOfWork { get { return _unitOfWork; } }


        #endregion

        #region methods

        public void ThrowError(string code, params object[] objs)
        {
            LibMessageUtils.ThrowError(code, objs);
        }
        /// <summary>
        /// 对数据库执行给定的 DDL/DML 命令。
        /// </summary>
        /// <param name="sql">命令字符串</param>
        /// <param name="parameters">参数</param>
        /// <returns>受影响的行数</returns>
        public int ExecuteSqlCommand(string sql, params object[] parameters)
        {
            return this.UnitOfWork.ExecuteSqlCommand(sql, parameters);
        }
        /// <summary>
        /// 对数据库执行给定的 DDL/DML 命令。
        /// </summary>
        /// <param name="transactionalBehavior">事务行为</param>
        /// <param name="sql">命令字符串</param>
        /// <param name="parameters">参数</param>
        /// <returns>受影响的行数</returns>
        public int ExecuteSqlCommand(TransactionalBehavior transactionalBehavior, string sql, params object[] parameters)
        {
            return this.UnitOfWork.ExecuteSqlCommand(transactionalBehavior, sql, parameters);
        }

        /// <summary>
        /// 对数据库执行给定的 DDL/DML 命令返回reader。
        /// </summary>
        /// <param name="sql">命令字符串</param>
        /// <param name="readerAction">reader委托</param>
        public void ExecuteReader(string sql, ReaderColumn[] readerColumns, Action<DbDataReader> readerAction)
        {
            this.UnitOfWork.ExecuteReader(sql, readerColumns, readerAction);
        }

        /// <summary>
        /// 对数据库执行给定的 DDL/DML 命令返回reader。
        /// </summary>
        /// <param name="sql">命令字符串</param>
        /// <param name="parameters">参数</param>
        /// <param name="readerAction">reader委托</param>
        public void ExecuteReader(string sql, ReaderColumn[] readerColumns, DbParameter[] parameters, Action<DbDataReader> readerAction)
        {
            this.UnitOfWork.ExecuteReader(sql, readerColumns,parameters, readerAction);
        }

        /// <summary>
        /// 对数据库执行给定的 DDL/DML 命令返回reader。
        /// </summary>
        /// <param name="commandType">命令类型</param>
        /// <param name="sql">命令字符串</param>
        /// <param name="parameters">参数</param>
        /// <param name="readerAction">reader委托</param>
        public void ExecuteReader(CommandType commandType, string sql, ReaderColumn[] readerColumns, DbParameter[] parameters, Action<DbDataReader> readerAction)
        {
            this.UnitOfWork.ExecuteReader(commandType, sql,readerColumns, parameters, readerAction);
        }



        /// <summary>
        /// 创建一个原始 SQL 查询，该查询将返回给定泛型类型的元素。
        /// </summary>
        /// <typeparam name="TElement">查询所返回对象的类型</typeparam>
        /// <param name="sql">SQL 查询字符串</param>
        /// <param name="parameters">要应用于 SQL 查询字符串的参数</param>
        /// <returns></returns>
        //public IEnumerable<TElement> SqlQuery<TElement>(string sql, params object[] parameters)
        //{
        //    return this.UnitOfWork.SqlQuery<TElement>(sql, parameters);
        //}

        /// <summary>
        /// 创建一个原始 SQL 查询，该查询将返回给定类型的元素。
        /// </summary>
        /// <param name="elementType">查询所返回对象的类型</param>
        /// <param name="sql">SQL 查询字符串</param>
        /// <param name="parameters">要应用于 SQL 查询字符串的参数</param>
        /// <returns></returns>
        public IEnumerable SqlQuery(Type elementType, string sql, ReaderColumn[] readerColumns, params Object[] parameters)
        {
            return this.UnitOfWork.SqlQuery(elementType, sql, readerColumns, parameters);
        }

        /// <summary>
        /// 获取数据库连接对象
        /// </summary>
        /// <param name="dataBaseType">数据库类型</param>
        /// <param name="connString">连接字符串</param>
        public DbConnection InitDbConnection(DataBaseType dataBaseType, string connString)
        {
            var conn = GetDbConnection(dataBaseType, connString);
            if (conn == null)
                throw new Exception("连接失败");
            return conn;
        }
        /// <summary>
        ///  对数据库执行给定的 DDL/DML 命令。
        /// </summary>
        /// <param name="commandType">命令类型</param>
        /// <param name="sql">命令字符串</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public int ExecuteNonQuery(CommandType commandType, string sql, ReaderColumn[] readerColumns, DbParameter[] parameters)
        {
            return this.UnitOfWork.ExecuteNonQuery(commandType, sql, readerColumns, parameters);
        }

        /// <summary>
        /// 根据数据库连接对数据库执行给定的 DDL/DML 命令。
        /// </summary>
        /// <param name="dataBaseType">数据库类型</param>
        /// <param name="connString">连接字符串</param>
        /// <param name="commandType">sql 类型</param>
        /// <param name="sql">sql</param>
        /// <param name="dbParameters">参数</param>
        public void ExecuteNonQueryByConn(DataBaseType dataBaseType, string connString, CommandType commandType, string sql, DbParameter[] dbParameters)
        {
            var conn = GetDbConnection(dataBaseType, connString);
            ExecuteNonQueryByConn(dataBaseType, conn, commandType, sql, dbParameters);

        }


        public void ExecuteNonQueryByConn(DataBaseType dataBaseType, DbConnection conn, CommandType commandType, string sql, DbParameter[] dbParameters, bool close = true)
        {
            try
            {
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                if (conn != null)
                {
                    var cmd = GetDbCommand(dataBaseType, conn, sql, dbParameters);
                    if (cmd != null)
                    {
                        cmd.CommandType = commandType;
                        cmd.ExecuteNonQuery();
                    }
                }
            }
            finally
            {
                if (close && conn != null)
                    conn.Dispose();
            }
        }

        /// <summary>
        /// 根据数据库连接对数据库执行给定的 DDL/DML 命令获取Reader。
        /// </summary>
        /// <param name="dataBaseType">数据库类型</param>
        /// <param name="connString">连接字符串</param>
        /// <param name="commandType">sql 类型</param>
        /// <param name="sql">sql</param>
        /// <param name="dbParameters">参数</param>
        /// <param name="readerAction">执行委托</param>
        public void ExecuteReaderByConn(DataBaseType dataBaseType, string connString, CommandType commandType,
            string sql, DbParameter[] dbParameters, Action<DbDataReader> readerAction)
        {
            var conn = GetDbConnection(dataBaseType, connString);
            try
            {
                conn.Open();
                if (conn != null)
                {
                    var cmd = GetDbCommand(dataBaseType, conn, sql, dbParameters);
                    if (cmd != null)
                    {
                        cmd.CommandType = commandType;
                        using (var reader = cmd.ExecuteReader())
                        {
                            if (readerAction != null)
                                readerAction(reader);
                        }
                    }
                }
            }
            finally
            {
                if (conn != null)
                    conn.Dispose();
            }
        }

        /// <summary>
        /// 执行查询，并返回查询所返回的结果集中第一行的第一列。 所有其他的列和行将被忽略。
        /// </summary>
        /// <param name="dataBaseType">数据库类型</param>
        /// <param name="connString">连接字符串</param>
        /// <param name="commandType">sql 类型</param>
        /// <param name="sql">sql</param>
        /// <param name="dbParameters">参数</param>
        public object ExecuteScalarByConn(DataBaseType dataBaseType, string connString, CommandType commandType, string sql, DbParameter[] dbParameters)
        {
            var conn = GetDbConnection(dataBaseType, connString);
            try
            {

                conn.Open();
                if (conn != null)
                {
                    var cmd = GetDbCommand(dataBaseType, conn, sql, dbParameters);
                    if (cmd != null)
                    {
                        cmd.CommandType = commandType;
                        var ret = cmd.ExecuteScalar();
                        return ret;
                    }
                }
            }
            finally
            {
                if (conn != null)
                    conn.Dispose();
            }
            return null;
        }

        /// <summary>
        /// 执行查询，并返回查询所返回的结果集中第一行的第一列。 所有其他的列和行将被忽略。
        /// </summary>
        /// <param name="dataBaseType">数据库类型</param>
        /// <param name="conn">数据库连接对象</param>
        /// <param name="commandType">sql 类型</param>
        /// <param name="sql">sql</param>
        /// <param name="close">是否自动关闭连接</param>
        /// <param name="dbParameters">参数</param>
        public object ExecuteScalarByConn(DataBaseType dataBaseType, DbConnection conn, CommandType commandType,
            string sql, bool close = true, params DbParameter[] dbParameters)
        {
            //var conn = GetDbConnection(dataBaseType, connString);
            if (conn == null)
                throw new Exception("数据库连接对象不能为空");
            try
            {
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                if (conn != null)
                {
                    var cmd = GetDbCommand(dataBaseType, conn, sql, dbParameters);
                    if (cmd != null)
                    {
                        cmd.CommandType = commandType;
                        var ret = cmd.ExecuteScalar();
                        return ret;
                    }
                }
            }
            finally
            {
                if (close)
                {
                    if (conn != null)
                        conn.Dispose();
                }

            }
            return null;
        }

        /// <summary>
        /// 根据数据库连接对数据库执行给定的 DDL/DML 命令获取Reader。
        /// </summary>
        /// <param name="dataBaseType">数据库类型</param>
        /// <param name="conn">连接对象</param>
        /// <param name="commandType">指令类型</param>
        /// <param name="sql">SQL语句</param>
        /// <param name="dbParameters">参数</param>
        /// <param name="readerAction">委托</param>
        /// <param name="close">是否自动关闭连接</param>
        public void ExecuteReaderByConn(DataBaseType dataBaseType, DbConnection conn, CommandType commandType,
            string sql, DbParameter[] dbParameters, Action<DbDataReader> readerAction, bool close = true)
        {
            //var conn = GetDbConnection(dataBaseType, connString);
            try
            {
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                if (conn != null)
                {
                    var cmd = GetDbCommand(dataBaseType, conn, sql, dbParameters);
                    if (cmd != null)
                    {
                        cmd.CommandType = commandType;
                        using (var reader = cmd.ExecuteReader())
                        {
                            if (readerAction != null)
                                readerAction(reader);
                        }
                    }
                }
            }
            finally
            {
                if (close)
                {
                    if (conn != null)
                        conn.Dispose();
                }
            }
        }


        /// <summary>
        /// 根据数据库连接对数据库执行给定的 DDL/DML 命令获取Reader。
        /// </summary>
        /// <param name="dataBaseType">数据库类型</param>
        /// <param name="connString">连接字符串</param>
        /// <param name="commandType">sql 类型</param>
        /// <param name="sql">sql</param>
        /// <param name="dbParameters">参数</param>
        /// <param name="pageIndex">分页索引 从1开始</param>
        /// <param name="pageLimit">每页条数 必填</param>
        /// <param name="readerAction">执行委托</param>
        public void ExecuteReaderPageByConn(DataBaseType dataBaseType, string connString, CommandType commandType,
            string sql, DbParameter[] dbParameters, int pageIndex, int pageLimit, Action<DbDataReader> readerAction)
        {
            if (pageLimit == 0)
                throw new Exception("请填写每页条数");
            if (pageIndex <= 0)
                pageIndex = 1;

            int start = (pageIndex - 1) * pageLimit;
            int end = pageIndex * pageLimit;
            if (dataBaseType == DataBaseType.Oracle)
            {
                sql = string.Format(@"SELECT* FROM(SELECT A.*, ROWNUM RN FROM({0}) A WHERE ROWNUM <= {2}) WHERE RN >= {1}", sql, start, end);
            }
            else if (dataBaseType == DataBaseType.Postgresql)
            {
                sql = $"{sql} LIMIT {pageLimit} OFFSET {start}";
            }
            else
            {
                throw new Exception("不支持的数据库类型");
            }

            var conn = GetDbConnection(dataBaseType, connString);
            try
            {
                conn.Open();
                if (conn != null)
                {

                    var cmd = GetDbCommand(dataBaseType, conn, sql, dbParameters);
                    if (cmd != null)
                    {
                        cmd.CommandType = commandType;
                        using (var reader = cmd.ExecuteReader())
                        {
                            if (readerAction != null)
                                readerAction(reader);
                        }
                    }
                }
            }
            finally
            {
                if (conn != null)
                    conn.Dispose();
            }
        }


        /// <summary>
        /// 根据数据库连接对数据库执行给定的 DDL/DML 命令获取Reader。
        /// </summary>
        /// <param name="dataBaseType">数据库类型</param>
        /// <param name="conn">连接对象</param>
        /// <param name="commandType">sql 类型</param>
        /// <param name="sql">sql</param>
        /// <param name="dbParameters">参数</param>
        /// <param name="pageIndex">分页索引 从1开始</param>
        /// <param name="pageLimit">每页条数 必填</param>
        /// <param name="readerAction">执行委托</param>
        /// <param name="close">是否自动关闭连接</param>
        public void ExecuteReaderPageByConn(DataBaseType dataBaseType, DbConnection conn, CommandType commandType,
            string sql, DbParameter[] dbParameters, int pageIndex, int pageLimit, Action<DbDataReader> readerAction
            , bool close = true)
        {
            if (conn == null)
                throw new Exception("数据库连接对象不能为空");
            if (pageLimit == 0)
                throw new Exception("请填写每页条数");
            if (pageIndex <= 0)
                pageIndex = 1;
            int start = (pageIndex - 1) * pageLimit;
            int end = pageIndex * pageLimit;
            if (dataBaseType == DataBaseType.Oracle)
            {
                if (pageIndex <= 0)
                    pageIndex = 1;

                sql = string.Format(@"SELECT* FROM(SELECT A.*, ROWNUM RN FROM({0}) A WHERE ROWNUM <= {2}) WHERE RN >= {1}", sql, start, end);
            }

            else if (dataBaseType == DataBaseType.Postgresql)
            {
                sql = $"{sql} LIMIT {pageLimit} OFFSET {start}";
            }
            else
            {
                throw new Exception("不支持的数据库类型");
            }
            //var conn = GetDbConnection(dataBaseType, connString);
            try
            {
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                if (conn != null)
                {
                    var cmd = GetDbCommand(dataBaseType, conn, sql, dbParameters);
                    if (cmd != null)
                    {
                        cmd.CommandType = commandType;
                        using (var reader = cmd.ExecuteReader())
                        {
                            if (readerAction != null)
                                readerAction(reader);
                        }
                    }
                }
            }
            finally
            {
                if (close)
                {
                    if (conn != null)
                        conn.Dispose();
                }
            }
        }


        /// <summary>
        /// 根据数据库连接对数据库执行给定的 DDL/DML 命令获取数据总条数。
        /// </summary>
        /// <param name="dataBaseType">数据库类型</param>
        /// <param name="connString">连接字符串</param>
        /// <param name="commandType">sql 类型</param>
        /// <param name="sql">sql</param>
        /// <param name="dbParameters">参数</param>
        /// <returns>数据总条数</returns>
        public int GetSqlCountByConn(DataBaseType dataBaseType, string connString, CommandType commandType,
            string sql, DbParameter[] dbParameters)
        {
            if (dataBaseType == DataBaseType.Oracle)
            {
                sql = string.Format(@"SELECT Max(RN) As SQLCOUNT FROM(SELECT A.*, ROWNUM RN FROM({0}) A )", sql);
            }

            else if (dataBaseType == DataBaseType.Postgresql)
            {
                sql = $"SELECT COUNT(1) FROM({sql}) A";
            }
            else
            {
                throw new Exception("不支持的数据库类型");
            }
            int result = 0;
            var conn = GetDbConnection(dataBaseType, connString);
            try
            {
                conn.Open();
                if (conn != null)
                {

                    var cmd = GetDbCommand(dataBaseType, conn, sql, dbParameters);
                    if (cmd != null)
                    {
                        cmd.CommandType = commandType;
                        using (var reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                object tmp = reader.GetValue(0);
                                if (tmp != null)
                                    result = Convert.ToInt32(tmp);
                            }
                        }
                    }
                }
            }
            finally
            {
                if (conn != null)
                    conn.Dispose();
            }
            return result;
        }

        private const string RuleCode_ORACLE_SQL = @"select a.ID,a.serial,b.TAKE_RETAIN_MIN,a.ROW_VERSION from  t_pf_code_rule_serial a
inner join T_PF_CODE_RULE b on a.code_type = b.code_type and a.org_id = b.org_id
and b.ENABLE_STATUS = 0
 where a.CODE_TYPE = '{0}' and rownum <= '{1}' and (b.IS_IGNORE_DATE = 1 or(b.IS_IGNORE_DATE = 0 and a.DATE_VALUE = '{2}'))
 and a.Org_Id = '{3}' and
 a.STATUS = 0
   order by a.num ";

        private const string RuleCode_MYSQL_SQL = @"select a.ID,a.serial,b.TAKE_RETAIN_MIN,a.ROW_VERSION from  t_pf_code_rule_serial a
inner join T_PF_CODE_RULE b on a.code_type = b.code_type and a.org_id = b.org_id
and b.ENABLE_STATUS = 0
 where a.CODE_TYPE = '{0}'  and (b.IS_IGNORE_DATE = 1 or(b.IS_IGNORE_DATE = 0 and a.DATE_VALUE = '{2}'))
 and a.Org_Id = '{3}' and
 a.STATUS = 0
   order by a.num limit {1}";

        private const string RuleCode_SQL_SERVER_SQL = @"select top {1} a.ID,a.serial,b.TAKE_RETAIN_MIN,a.ROW_VERSION from  t_pf_code_rule_serial a
inner join T_PF_CODE_RULE b on a.code_type = b.code_type and a.org_id = b.org_id
and b.ENABLE_STATUS = 0
 where a.CODE_TYPE = '{0}'  and (b.IS_IGNORE_DATE = 1 or(b.IS_IGNORE_DATE = 0 and a.DATE_VALUE = '{2}'))
 and a.Org_Id = '{3}' and
 a.STATUS = 0
   order by a.num ";
        private const string RuleCode_POSTGRESQL_SQL = @"select  a.""ID"",a.""SERIAL"",b.""TAKE_RETAIN_MIN"",a.""ROW_VERSION""
from  ""T_PF_CODE_RULE_SERIAL"" a
inner join ""T_PF_CODE_RULE"" b on a.""CODE_TYPE""=b.""CODE_TYPE"" and a.""ORG_ID""=b.""ORG_ID""
and b.""ENABLE_STATUS""=0
 where a.""CODE_TYPE""='{0}'  and (b.""IS_IGNORE_DATE"" =true or (b.""IS_IGNORE_DATE""=false and a.""DATE_VALUE""='{2}'))
 and a.""ORG_ID""='{3}' and 
 a.""STATUS""='0'
   order by a.""NUM"" Limit {1};";

        public string GetRuleCodes(CodeRuleParam param)
        {
            if (param == null || param.Count == 0) return null;
            var dataBaseType = this.UnitOfWork.GetDataBaseType();
            string orgId = string.Empty;
            string codeRuleSql = string.Empty;
            string updateSql = string.Empty;
            if (param.OrgId.HasValue)
            {
                switch (dataBaseType)
                {
                    case DataBaseType.Oracle:
                        {
                            orgId = BitConverter.ToString(param.OrgId.Value.ToByteArray()).Replace("-", "");
                            codeRuleSql = RuleCode_ORACLE_SQL;
                        }
                        break;
                    case DataBaseType.MySQL:
                        {
                            codeRuleSql = RuleCode_MYSQL_SQL;
                            orgId = param.OrgId.Value.ToString().ToLower();
                        }
                        break;
                    case DataBaseType.Postgresql:
                        {
                            codeRuleSql = RuleCode_POSTGRESQL_SQL;
                            orgId = param.OrgId.Value.ToString().ToLower();
                        }
                        break;
                    default:
                        {
                            codeRuleSql = RuleCode_SQL_SERVER_SQL;
                            orgId = param.OrgId.Value.ToString().ToUpper();
                        }
                        break;
                }
            }
            var conn = GetDbConnection(dataBaseType, this.UnitOfWork.ConnectionString);
            try
            {
                string date = param.Time.ToString("yyyyMMdd");
                string sql = string.Format(codeRuleSql, param.CodeType, param.Count, date, orgId);
                List<RuleCodeInfo> ruleCodeInfos = new List<RuleCodeInfo>();
                this.ExecuteReaderByConn(dataBaseType, conn, CommandType.Text, sql, null, (reader) =>
                 {
                     while (reader.Read())
                     {
                         RuleCodeInfo info = new RuleCodeInfo();
                         var obj = reader.GetValue(0);
                         if (obj != null && obj != DBNull.Value)
                         {
                             if (obj is byte[])
                             {
                                 var blob = obj as byte[];
                                 info.ID = BitConverter.ToString(blob).Replace("-", "");
                             }
                             else
                             {
                                 info.ID = Convert.ToString(obj);
                             }
                         }
                         info.Serial = LibUtils.ToString(reader.GetValue(1));// reader.GetString(0);
                         info.TaskTimeMin = LibUtils.ToInt(reader.GetValue(2));
                         info.RowVersion = LibUtils.ToInt(reader.GetValue(3));
                         ruleCodeInfos.Add(info);
                     }
                 }, false);
                if (ruleCodeInfos.Count < param.Count)
                    throw new Exception("流水码[" + param.CodeType + "]不足，请联系系统管理员");

                if (param.IsUpdate)
                {
                    List<string> updateSqls = new List<string>();
                    List<RuleCodeInfo> tempInfos = new List<RuleCodeInfo>();

                    if (dataBaseType == DataBaseType.Postgresql)
                    {

                        StringBuilder updates = new StringBuilder();
                        foreach (var item in ruleCodeInfos)
                            updates.Append(string.Format(" ('{0}',{1}),", item.ID, item.RowVersion));
                        updates.Length--;
                        updateSqls.Add("create  temp  table \"T_PF_CODE_RULE_SERIAL_TEMP\" (\"ID\" uuid,\"ROW_VERSION\" integer) ;");
                        updateSqls.Add(string.Format("insert into \"T_PF_CODE_RULE_SERIAL_TEMP\" values {0}; ", updates.ToString()));


                        var firstItem = ruleCodeInfos.FirstOrDefault();
                        var takeTime = DateTime.Now;
                        var releaseTime = DateTime.Now.AddMinutes(firstItem.TaskTimeMin == 0 ? 20 : firstItem.TaskTimeMin);
                        var takeTimeStr = dataBaseType == DataBaseType.Oracle ?
                            ("to_date('" + takeTime.ToString("yyyy/MM/dd HH:mm:ss") + "','yyyy-mm-dd hh24-mi-ss')") :
                            ("'" + takeTime.ToString("yyyy-MM-dd HH:mm:ss") + "'");
                        var releaseTimeStr = dataBaseType == DataBaseType.Oracle ?
                            ("to_date('" + releaseTime.ToString("yyyy/MM/dd HH:mm:ss") + "','yyyy-mm-dd hh24-mi-ss')") :
                             ("'" + releaseTime.ToString("yyyy-MM-dd HH:mm:ss") + "'");
                        updateSqls.Add(string.Format(@"Update {4}T_PF_CODE_RULE_SERIAL{4} set {4}STATUS{4}='{0}',{4}TAKE_TIME{4}={1},
{4}ROW_VERSION{4}={4}ROW_VERSION{4}+1,
						{4}RELEASE_TIME{4}={2}  where  exists (select 1 from ""T_PF_CODE_RULE_SERIAL_TEMP"" a where a.""ID""= ""T_PF_CODE_RULE_SERIAL"".""ID"" 
 and a.""ROW_VERSION""=""T_PF_CODE_RULE_SERIAL"".""ROW_VERSION"")   and  {4}STATUS{4}='0';",
                            1, takeTimeStr, releaseTimeStr, string.Empty, (dataBaseType == DataBaseType.Postgresql ? "\"" : string.Empty)));

                    }
                    else if (dataBaseType == DataBaseType.Oracle)
                    {
                        //创建临时表
                        //CREATE GLOBAL TEMPORARY TABLE  T_PF_CODE_RULE_SERIAL_TEMP
                        //(ID RAW(16),ROW_VERSION NUMBER(10))
                        //ON COMMIT PRESERVE ROWS;

                        StringBuilder updates = new StringBuilder();
                        foreach (var item in ruleCodeInfos)
                            updates.Append(string.Format(" into T_PF_CODE_RULE_SERIAL_TEMP(ID,ROW_VERSION) values     ('{0}')", item.ID, item.RowVersion));
                        updateSqls.Add(string.Format("insert  /*+  nologging */ all {0}  SELECT   1 FROM DUAL; ", updates.ToString()));

                        var firstItem = ruleCodeInfos.FirstOrDefault();
                        var takeTime = DateTime.Now;
                        var releaseTime = DateTime.Now.AddMinutes(firstItem.TaskTimeMin == 0 ? 20 : firstItem.TaskTimeMin);
                        var takeTimeStr = "to_date('" + takeTime.ToString("yyyy/MM/dd HH:mm:ss") + "','yyyy-mm-dd hh24-mi-ss')";
                        var releaseTimeStr = "to_date('" + releaseTime.ToString("yyyy/MM/dd HH:mm:ss") + "','yyyy-mm-dd hh24-mi-ss')";
                        updateSqls.Add(string.Format(@"Update T_PF_CODE_RULE_SERIAL set STATUS={0},TAKE_TIME={1},RELEASE_TIME={2},ROW_VERSION=ROW_VERSION+1  where exists (select 1 from T_PF_CODE_RULE_SERIAL_TEMP a where a.Id= T_PF_CODE_RULE_SERIAL.Id and a.ROW_VERSION=T_PF_CODE_RULE_SERIAL.ROW_VERSION)  and  STATUS='0';",
                            1, takeTimeStr, releaseTimeStr));

                    }
                    else
                    {
                        foreach (var item in ruleCodeInfos)
                        {
                            tempInfos.Add(item);
                            if (tempInfos.Count >= 100)
                            {
                                DoGetUpdateCodeRuleSql(dataBaseType, tempInfos, updateSqls);
                                tempInfos.Clear();
                            }
                        }
                        DoGetUpdateCodeRuleSql(dataBaseType, tempInfos, updateSqls);
                    }
                    StringBuilder sb = new StringBuilder();
                    if (dataBaseType != DataBaseType.Postgresql)
                        sb.AppendLine("begin");
                    foreach (var str in updateSqls)
                        sb.AppendLine(str);
                    if (dataBaseType != DataBaseType.Postgresql)
                        sb.AppendLine("end;");
                    this.ExecuteNonQueryByConn(dataBaseType, conn, CommandType.Text, sb.ToString(), null, false);
                    //this.ExecuteNonQuery(CommandType.Text, sb.ToString(), null);
                }
                return string.Join(",", ruleCodeInfos.Select(t => t.Serial).ToList());
            }
            finally
            {
                if (conn != null)
                    conn.Dispose();
            }
        }

        private void DoGetUpdateCodeRuleSql(DataBaseType dataBaseType, List<RuleCodeInfo> infos, List<string> updateSqls)
        {
            if (!infos.Any()) return;
            StringBuilder stringBuilder = new StringBuilder();
            foreach (var item in infos)
            {
                stringBuilder.Append("'" + item.ID + "'");
                stringBuilder.Append(",");
            }
            stringBuilder.Length--;
            var firstItem = infos.FirstOrDefault();
            var takeTime = DateTime.Now;
            var releaseTime = DateTime.Now.AddMinutes(firstItem.TaskTimeMin == 0 ? 20 : firstItem.TaskTimeMin);
            var takeTimeStr = dataBaseType == DataBaseType.Oracle ?
                ("to_date('" + takeTime.ToString("yyyy/MM/dd HH:mm:ss") + "','yyyy-mm-dd hh24-mi-ss')") :
                ("'" + takeTime.ToString("yyyy-MM-dd HH:mm:ss") + "'");
            var releaseTimeStr = dataBaseType == DataBaseType.Oracle ?
                ("to_date('" + releaseTime.ToString("yyyy/MM/dd HH:mm:ss") + "','yyyy-mm-dd hh24-mi-ss')") :
                 ("'" + releaseTime.ToString("yyyy-MM-dd HH:mm:ss") + "'");
            updateSqls.Add(string.Format(@"Update {4}T_PF_CODE_RULE_SERIAL{4} set {4}STATUS{4}='{0}',{4}TAKE_TIME{4}={1},
{4}ROW_VERSION{4}={4}ROW_VERSION{4}+1,
						{4}RELEASE_TIME{4}={2}  where {4}ID{4} in ({3})  and  {4}STATUS{4}='0';",
                1, takeTimeStr, releaseTimeStr, stringBuilder.ToString(), (dataBaseType == DataBaseType.Postgresql ? "\"" : string.Empty)));
        }

        class RuleCodeInfo
        {
            public string ID { get; set; }
            public string Serial { get; set; }
            public int TaskTimeMin { get; set; }
            public int RowVersion { get; set; }
        }

        #endregion


        #region 私有函数
        private DbConnection GetDbConnection(DataBaseType dataBaseType, string connString)
        {
            switch (dataBaseType)
            {
                //case DataBaseType.Oracle:
                //    return new OracleConnection(connString);
                //case DataBaseType.MySQL:
                //	return new MySqlConnection(connString);
                //case DataBaseType.Postgresql:
                //    return new NpgsqlConnection(connString);
            }
            return new SqlConnection(connString);
        }

        private DbCommand GetDbCommand(DataBaseType dataBaseType, DbConnection conn, string sql, DbParameter[] dbParameters)
        {
            DbCommand command = null;
            switch (dataBaseType)
            {
                case DataBaseType.Oracle:
                    //command = new OracleCommand(sql, conn as OracleConnection);
                    break;
                //case DataBaseType.MySQL:
                //	command = new MySqlCommand(sql, conn as MySqlConnection);
                //	break;
                case DataBaseType.SQL:
                    command = new SqlCommand(sql, conn as SqlConnection);
                    break;
                case DataBaseType.Postgresql:
                    //command = new NpgsqlCommand(sql, conn as NpgsqlConnection);
                    break;
            }
            if (dbParameters != null && dbParameters.Length > 0)
            {
                foreach (var item in dbParameters)
                {
                    if (item.Value == null)
                        item.Value = DBNull.Value;
                    command.Parameters.Add(item);
                }
            }
            return command;
        }

        #endregion

    }

}