using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Model;
using Common;
using Oracle.ManagedDataAccess.Client;
using System.Data.Common;

namespace Dao
{
    /// <summary>
    /// 字典数据操作
    /// </summary>
    public class SysDictDataDao : IDisposable
    {
        public void Dispose()
        {
        }

        private static SysDictDataDao _instance;
        public static SysDictDataDao Instance
        {
            get {
                if (_instance == null)
                {
                    _instance = new SysDictDataDao();
                }
                return _instance;
            }
        }


        public List<SysDictData> LoadInfoList(string dictLabel, string dictType, int pageSize, int PageIndex, out int recordcount)
        {
            
            List<SysDictData> list = new List<SysDictData>();
            recordcount = 0;
            try
            {
                StringBuilder builder = new StringBuilder();
                builder.Append("from SYS_DICT_DATA where 1=1 AND DEL_FLAG=0 ");
                if (!string.IsNullOrEmpty(dictLabel))
                {
                    builder.Append(" and DICT_LABEL like '%" + dictLabel + "%'");
                }
                if (!string.IsNullOrEmpty(dictType))
                {
                    builder.Append(" and DICT_TYPE = '" + dictType + "'");
                }
                using (OracleDataReader reader = OracleHelper.ExecuteReader("select count(*) as count " + builder.ToString(), OracleHelper.Connection))
                {
                    while (reader.Read())
                    {
                        recordcount = this.ReadCount(reader);
                    }
                }
                using (OracleDataReader reader2 = OracleHelper.ExecuteReader(PubConstant.doOracleSql(PageIndex, pageSize, recordcount, "select * " + builder.ToString()).ToString() + " order by DICT_TYPE, DICT_SORT ", OracleHelper.Connection ))
                {
                    while (reader2.Read())
                    {
                        SysDictData o = new SysDictData();
                        if (this.ReadInfo(reader2, o))
                        {
                            list.Add(o);
                        }
                    }
                }
            }
            catch (Exception exception)
            {
                LogHelper.Error(exception);
                return list;
            }
            return list;

        }

        //根据条件获取数据列表
        public List<SysDictData> getAllList(SysDictData bean)
        {

            List<SysDictData> list = new List<SysDictData>();
            try
            {
                StringBuilder builder = new StringBuilder();
                builder.Append("from SYS_DICT_DATA where 1=1 AND DEL_FLAG=0 ");
                if (!string.IsNullOrEmpty(bean.DictLabel))
                {
                    builder.Append(" and DICT_LABEL like '%" + bean.DictLabel + "%'");
                }

                if (!string.IsNullOrEmpty(bean.DictValue))
                {
                    builder.Append(" and DICT_VALUE like '%" + bean.DictValue + "%'");
                }

                if (!string.IsNullOrEmpty(bean.DictType))
                {
                    builder.Append(" and DICT_TYPE = '" + bean.DictType + "'");
                }

                if (bean.ParanSource == -1)
                {
                    builder.Append(" AND (PARAN_SOURCE != " + bean.ParanSource + " OR PARAN_SOURCE IS NULL) ");
                }
                else
                {
                    builder.Append(" AND PARAN_SOURCE = " + bean.ParanSource + " ");
                }

                if (bean.Status == -1)
                {
                    builder.Append(" AND (STATUS != " + bean.Status + " OR STATUS IS NULL) ");
                }
                else
                {
                    builder.Append(" AND STATUS = " + bean.Status + " ");
                }

                using (OracleDataReader reader = OracleHelper.ExecuteReader("select * " + builder.ToString() + " order by CREATE_TIME ", OracleHelper.Connection))
                {
                    while (reader.Read())
                    {
                        SysDictData o = new SysDictData();
                        if (this.ReadInfo(reader, o))
                        {
                            list.Add(o);
                        }
                    }
                }
            }
            catch (Exception exception)
            {
                LogHelper.Error(exception);
                return list;
            }
            return list;

        }

        //添加信息
        public bool Add(SysDictData o)
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("INSERT INTO SYS_DICT_DATA ( ");
            stringBuilder.Append(" DICT_ID, DICT_VALUE, DICT_LABEL, DICT_TYPE, DICT_SORT, STATUS, CREATE_BY, CREATE_TIME, UPDATE_BY, UPDATE_TIME, REMARK ");
            stringBuilder.Append(" ) ");
            stringBuilder.Append(" VALUES (:DICT_ID, :DICT_VALUE, :DICT_LABEL, :DICT_TYPE, :DICT_SORT, :STATUS, :CREATE_BY, :CREATE_TIME, :UPDATE_BY, :UPDATE_TIME, :REMARK ");
            stringBuilder.Append("  ) ");

            string sql = stringBuilder.ToString();

            return OracleHelper.ExecuteSql(sql, 
                OracleHelper.Connection,
                new OracleParameter(":DICT_ID", (object)o.DictId),
                new OracleParameter(":DICT_VALUE", (object)o.DictValue),
                new OracleParameter(":DICT_LABEL", (object)o.DictLabel),
                new OracleParameter(":DICT_TYPE", (object)o.DictType),
                new OracleParameter(":DICT_SORT", (object)o.DictSort),
                new OracleParameter(":STATUS", (object)o.Status),
                new OracleParameter(":CREATE_BY", (object)o.CreateBy),
                new OracleParameter(":CREATE_TIME", (object)o.CreateTime),
                new OracleParameter(":UPDATE_BY", (object)o.UpdateBy),
                new OracleParameter(":UPDATE_TIME", (object)o.UpdateTime),
                new OracleParameter(":REMARK", (object)o.Remark)
                ) > 0;
        }

        //删除
        public bool Delete(SysDictData o)
        {
            if (o == null || string.IsNullOrEmpty(o.DictId))
                return false;
            //return OracleHelper.ExecuteSql("delete from SYS_DICT_DATA where DICT_ID=:DICT_ID", OracleHelper.Connection, new OracleParameter(":DICT_ID", (object)dictId)) > 0;
            return OracleHelper.ExecuteSql("UPDATE SYS_DICT_DATA SET DEL_FLAG = 2, UPDATE_BY=:UPDATE_BY, UPDATE_TIME=:UPDATE_TIME where DICT_ID=:DICT_ID", OracleHelper.Connection, new OracleParameter(":UPDATE_BY", (object)o.UpdateBy), new OracleParameter(":UPDATE_TIME", (object)o.UpdateTime), new OracleParameter(":DICT_ID", (object)o.DictId)) > 0;
            
        }

        //更新信息
        public bool Update(SysDictData o)
        {
            OracleParameter[] cmdParms = new OracleParameter[] {
                new OracleParameter(":DICT_ID", (object)o.DictId),
                new OracleParameter(":DICT_VALUE", (object)o.DictValue),
                new OracleParameter(":DICT_LABEL", (object)o.DictLabel),
                new OracleParameter(":DICT_TYPE", (object)o.DictType),
                new OracleParameter(":DICT_SORT", (object)o.DictSort),
                new OracleParameter(":STATUS", (object)o.Status),
                new OracleParameter(":CREATE_BY", (object)o.CreateBy),
                new OracleParameter(":CREATE_TIME", (object)o.CreateTime),
                new OracleParameter(":UPDATE_BY", (object)o.UpdateBy),
                new OracleParameter(":UPDATE_TIME", (object)o.UpdateTime),
                new OracleParameter(":REMARK", (object)o.Remark)
            };

            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("UPDATE SYS_DICT_DATA SET ");
            stringBuilder.Append(" DICT_VALUE=:DICT_VALUE ");
            stringBuilder.Append(" , DICT_LABEL =:DICT_LABEL ");
            stringBuilder.Append(" DICT_TYPE=:DICT_TYPE ");
            stringBuilder.Append(" , DICT_SORT =:DICT_SORT ");
            stringBuilder.Append(" , STATUS =:STATUS ");
            stringBuilder.Append(" , CREATE_BY =:CREATE_BY ");
            stringBuilder.Append(" , CREATE_TIME =:CREATE_TIME ");
            stringBuilder.Append(" , UPDATE_BY =:UPDATE_BY ");
            stringBuilder.Append(" , UPDATE_TIME =:UPDATE_TIME ");
            stringBuilder.Append(" , REMARK=:REMARK ");
            stringBuilder.Append(" WHERE DICT_ID=:DICT_ID ");

            string sql = stringBuilder.ToString();
            return (OracleHelper.ExecuteSql(sql, OracleHelper.Connection, cmdParms) > 0);
        }

        //获取信息
        public SysDictData GetByDictId(string dictId)
        {
            SysDictData o = new SysDictData();
            if (string.IsNullOrEmpty(dictId))
                return o;
            using (OracleDataReader reader = OracleHelper.ExecuteReader(string.Format("select * from SYS_DICT_DATA where DICT_ID=:DICT_ID"), OracleHelper.Connection, new OracleParameter(":DICT_ID", (object)dictId)))
            {
                if (((DbDataReader)reader).Read())
                {
                    this.ReadInfo(reader, o);
                    return o;
                }
            }
            return o;
        }

        //获取信息
        public SysDictData GetByDictValue(string dictType, string dictValue)
        {
            SysDictData o = new SysDictData();
            if (string.IsNullOrEmpty(dictValue))
                return o;
            using (OracleDataReader reader = OracleHelper.ExecuteReader(string.Format("select * from SYS_DICT_DATA where DICT_TYPE=:DICT_TYPE AND DICT_VALUE=:DICT_VALUE"), OracleHelper.Connection, new OracleParameter(":DICT_TYPE", (object)dictType), new OracleParameter(":DICT_VALUE", (object)dictValue)))
            {
                if (((DbDataReader)reader).Read())
                {
                    this.ReadInfo(reader, o);
                    return o;
                }
            }
            return o;
        }

        //获取信息
        public SysDictData Get(string dictId)
        {
            SysDictData o = new SysDictData();
            if (string.IsNullOrEmpty(dictId))
                return o;
            using (OracleDataReader reader = OracleHelper.ExecuteReader(string.Format("select * from SYS_DICT_DATA where DICT_ID=:DICT_ID"), OracleHelper.Connection, new OracleParameter(":DICT_ID", (object)dictId)))
            {
                if (((DbDataReader)reader).Read())
                {
                    this.ReadInfo(reader, o);
                    return o;
                }
            }
            return o;
        }

        //更新启用或停用
        public bool UpdateStatus(SysDictData o)
        {
            if (o == null || string.IsNullOrEmpty(o.DictId))
                return false;
            return OracleHelper.ExecuteSql("UPDATE SYS_DICT_DATA SET STATUS=:STATUS, UPDATE_BY=:UPDATE_BY, UPDATE_TIME=:UPDATE_TIME where DICT_ID=:DICT_ID", OracleHelper.Connection, new OracleParameter(":STATUS", (object)o.Status), new OracleParameter(":UPDATE_BY", (object)o.UpdateBy), new OracleParameter(":UPDATE_TIME", (object)o.UpdateTime), new OracleParameter(":DICT_ID", (object)o.DictId)) > 0;
            
        }

        //统计记录数
        private int ReadCount(OracleDataReader reader)
        {
            OracleReaderWrapper wrapper = new OracleReaderWrapper(reader);
            return wrapper.GetInt("count", 0);
        }

        //数据封装
        private bool ReadInfo(OracleDataReader reader, SysDictData o)
        {
            OracleReaderWrapper oracleReaderWrapper = new OracleReaderWrapper(reader);
            o.DictId = oracleReaderWrapper.GetString("DICT_ID", "");
            o.DictValue = oracleReaderWrapper.GetString("DICT_VALUE", "");
            o.DictLabel = oracleReaderWrapper.GetString("DICT_LABEL", "");
            o.DictType = oracleReaderWrapper.GetString("DICT_TYPE", "");
            o.DictSort = oracleReaderWrapper.GetInt("DICT_SORT", 1);
            o.ParanSource = oracleReaderWrapper.GetInt("PARAN_SOURCE", 1);
            o.DelFlag = oracleReaderWrapper.GetInt("DEL_FLAG", 0);
            o.Status = oracleReaderWrapper.GetInt("STATUS", 0);
            o.CreateBy = oracleReaderWrapper.GetString("CREATE_BY", "");
            o.CreateTime = oracleReaderWrapper.GetDateTime("CREATE_TIME");
            o.UpdateBy = oracleReaderWrapper.GetString("UPDATE_BY", "");
            o.UpdateTime = oracleReaderWrapper.GetDateTime("UPDATE_TIME");
            o.Remark = oracleReaderWrapper.GetString("REMARK", "");
            return true;
        }

    }

}