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 SysRoleMenuDao : IDisposable
    {
        public void Dispose()
        {
        }

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


        public List<SysRoleMenu> LoadInfoList(string roleId, int menuId, int pageSize, int PageIndex, out int recordcount)
        {
            
            List<SysRoleMenu> list = new List<SysRoleMenu>();
            recordcount = 0;
            try
            {
                StringBuilder builder = new StringBuilder();
                builder.Append("from SYS_ROLE_MENU where 1=1 ");
                if (!string.IsNullOrEmpty(roleId))
                {
                    builder.Append(" and ROLE_ID = '" + roleId + "'");
                }

                if (menuId == -1)
                {
                    builder.Append(" AND (MENU_ID != " + menuId + " OR MENU_ID IS NULL) ");
                }
                else
                {
                    builder.Append(" AND MENU_ID = " + menuId + " ");
                }
                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())
                    {
                        SysRoleMenu o = new SysRoleMenu();
                        if (this.ReadInfo(reader2, o))
                        {
                            list.Add(o);
                        }
                    }
                }
            }
            catch (Exception exception)
            {
                LogHelper.Error(exception);
                return list;
            }
            return list;

        }

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

            List<SysRoleMenu> list = new List<SysRoleMenu>();
            try
            {
                StringBuilder builder = new StringBuilder();
                builder.Append("from SYS_ROLE_MENU where 1=1 ");
                if (!string.IsNullOrEmpty(bean.RoleId))
                {
                    builder.Append(" and ROLE_ID = '" + bean.RoleId + "'");
                }

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

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

        }

        //添加信息
        public bool Add(SysRoleMenu o)
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("INSERT INTO SYS_ROLE_MENU ( ROLE_ID, MENU_ID ) ");
            stringBuilder.Append(" VALUES (:ROLE_ID, :MENU_ID ) ");

            string sql = stringBuilder.ToString();

            return OracleHelper.ExecuteSql(sql, 
                OracleHelper.Connection,
                new OracleParameter(":ROLE_ID", (object)o.RoleId),
                new OracleParameter(":MENU_ID", (object)o.MenuId)
                ) > 0;
        }

        //删除
        public bool Delete(SysRoleMenu o)
        {
            return OracleHelper.ExecuteSql("delete from SYS_ROLE_MENU where ROLE_ID=:ROLE_ID AND MENU_ID=:MENU_ID", OracleHelper.Connection, new OracleParameter(":ROLE_ID", (object)o.RoleId), new OracleParameter(":MENU_ID", (object)o.MenuId)) > 0;
            
        }

        //获取信息
        public SysRoleMenu Get(string roleId, int menuId)
        {
            SysRoleMenu o = new SysRoleMenu();
            using (OracleDataReader reader = OracleHelper.ExecuteReader(string.Format("select * from SYS_ROLE_MENU where ROLE_ID=:ROLE_ID AND MENU_ID=:MENU_ID"), OracleHelper.Connection, new OracleParameter(":ROLE_ID", (object)roleId), new OracleParameter(":MENU_ID", (object)menuId) ) )
            {
                if (((DbDataReader)reader).Read())
                {
                    this.ReadInfo(reader, o);
                    return o;
                }
            }
            return o;
        }

        //获取角色权限列表
        public Dictionary<string, SysMenu> LoadRoleMenuList(string roleId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SELECT sm.* FROM SYS_MENU sm ");
            builder.Append(" LEFT JOIN SYS_ROLE_MENU srm ON srm.MENU_ID = sm.MENU_ID ");
            builder.Append(" WHERE srm.ROLE_ID=:ROLE_ID  ");

            Dictionary<string, SysMenu> dictionary = new Dictionary<string, SysMenu>();
            using (OracleDataReader reader = OracleHelper.ExecuteReader(builder.ToString(), OracleHelper.Connection, new OracleParameter(":ROLE_ID", (object)roleId)))
            {
                while (reader != null && ((DbDataReader)reader).Read())
                {
                    OracleReaderWrapper oracleReaderWrapper = new OracleReaderWrapper(reader);
                    string menuID = oracleReaderWrapper.GetString("MENU_ID", "");
                    string menuName = oracleReaderWrapper.GetString("MENU_NAME", "");
                    string remark = oracleReaderWrapper.GetString("REMARK", "");
                    string parentID = oracleReaderWrapper.GetString("PARENT_ID", "");
                    int menuLV = oracleReaderWrapper.GetInt("MENU_LV", 0);
                    SysMenu sysMenu = new SysMenu();
                    sysMenu.MenuID = menuID;
                    sysMenu.MenuName = menuName;
                    sysMenu.Remark = remark;
                    sysMenu.ParentID = parentID;
                    sysMenu.MenuLV = menuLV;

                    dictionary[menuID] = sysMenu;
                }
            }
            return dictionary;
        }

        //修改角色权限
        public void UpdatePermission(string roleId, string[] menuIDArray)
        {
            List<KeyValuePair<string, OracleParameter[]>> list = new List<KeyValuePair<string, OracleParameter[]>>();
            string key1 = " DELETE FROM SYS_ROLE_MENU WHERE ROLE_ID=:ROLE_ID ";
            OracleParameter[] oracleParameterArray1 = new OracleParameter[1]
              {
                new OracleParameter(":ROLE_ID", (object) roleId)
              };
            list.Add(new KeyValuePair<string, OracleParameter[]>(key1, oracleParameterArray1));
            OracleHelper.ExecuteSqlTran(list, OracleHelper.Connection);
            if (menuIDArray == null || menuIDArray.Length <= 0)
                return;
            string key2 = "INSERT INTO SYS_ROLE_MENU(ROLE_ID, MENU_ID) VALUES(:ROLE_ID,:MENU_ID)";
            foreach (string menuId in menuIDArray)
            {
                OracleParameter[] oracleParameterArray2 = new OracleParameter[2]
                {
                  new OracleParameter(":ROLE_ID", (object) roleId),
                  new OracleParameter(":MENU_ID", (object) menuId)
                };
                list.Add(new KeyValuePair<string, OracleParameter[]>(key2, oracleParameterArray2));
            }
            OracleHelper.ExecuteSqlTran(list, OracleHelper.Connection);
        }

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

        //数据封装
        private bool ReadInfo(OracleDataReader reader, SysRoleMenu o)
        {
            OracleReaderWrapper oracleReaderWrapper = new OracleReaderWrapper(reader);
            o.RoleId = oracleReaderWrapper.GetString("ROLE_ID", "");
            o.MenuId = oracleReaderWrapper.GetInt("MENU_ID", 1);
            return true;
        }

    }

}