using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using WMS.DBUtility;
using WMS.IData.IBase;
using WMS.IData;
using Oracle.ManagedDataAccess.Client;

namespace WMS.SqlServerData.BaseData
{
    public class RoleData : IRole
    {
        /// <summary>
        /// 获取权限信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetRoleInfo()
        {
            string sql = "select * from t_base_roledictionary order by Role_Id ";
            return SystemDataObject.Instance.GetDataTable(sql);
        }

        /// <summary>
        /// 根据权限获取关联信息
        /// </summary>
        /// <param name="RoleId">权限编号</param>
        /// <returns></returns>
        public DataSet GetRoleRelevanceInfo(string RoleId)
        {
            DataSet ds = new DataSet();

            //获取权限对应员工SQL
            string role = GetRoleUser(RoleId);
            //获取权限对应菜单
            string rolMenu = GetRoleMenu(RoleId);
            //获取操作权限
            string funRol = GetRoleFun(RoleId);
            //供应商

            string prideRol = GetRoleFun(RoleId);

            ////仓库
            //list.Add(GetRoleCK(RoleId));
            ////商品分类
            //list.Add(GetRoleSPFL(RoleId));
            ////获取菜单
            //list.Add(GetMENU());
            ////按钮信息
            //list.Add(GetFUN());
            DataTable roletable = SystemDataObject.Instance.GetDataTable(role);
            DataTable roltable = SystemDataObject.Instance.GetDataTable(funRol);
            DataTable pridetable = SystemDataObject.Instance.GetDataTable(prideRol);

            ds.Tables.Add(roletable);
            ds.Tables.Add(roltable);
            ds.Tables.Add(pridetable);
            return ds;

        }

        #region 获取权限SQL语句
        /// <summary>
        /// 获取权限对应员工SQL
        /// </summary>
        /// <param name="RoleId"></param>
        /// <returns></returns>
        private string GetRoleUser(string RoleId)
        {
            string sql =@"select 
                                  distinct
                                  COM_NAME,
                                  t_base_UserInfo.User_Id,
                                  t_base_UserInfo.Name
                            from 
                                  t_base_UserInfo
                            left outer join   
                                  T_BASE_ROLE
                                  on t_base_UserInfo.User_Id = t_Base_Role.User_Id
                            left outer join
                                  t_base_Company
                                  on t_base_UserInfo.Com_Id = t_base_Company.Com_Id
                            where 1 = 1 ";
            if (RoleId != "")
            {
                sql = sql + " and T_BASE_ROLE.Role_ID = '" + RoleId + "'";
            }
            return sql;
        }

        /// <summary>
        /// 获取权限对应菜单
        /// </summary>
        /// <param name="RoleId"></param>
        /// <returns></returns>
        private string GetRoleMenu(string RoleId)
        {
            string sql = "select MENU_ID,MENU_NAME,P_MENU_ID,MENU_LEVEL,MENU_SORT,STATUS from t_base_menu where Menu_Id in (select Menu_Id from t_base_menurole )";
            if (RoleId != "")
            {
                sql = "select MENU_ID,MENU_NAME,P_MENU_ID,MENU_LEVEL,MENU_SORT,STATUS from t_base_menu where Menu_Id in (select Menu_Id from t_base_menurole where Role_Id = '" + RoleId + "')";
            }
            return sql;
        }

        /// <summary>
        /// 获取权限对应功能权限
        /// </summary>  t_base_FunctionInfo.p_Fun_Id,--功能父编号
        /// <param name="RoleId"></param>
        /// <returns></returns>
        private string GetRoleFun(string RoleId)
        {
            //t_base_FunctionInfo.Fun_Tag,-- 功能tag
            //                    t_base_FunctionInfo.Fun_Image,--图片地址
            string sql =@" select distinct
                                   t_base_FunctionInfo.Fun_Id,--功能编号
                                   t_base_FunctionInfo.Fun_Name,--功能名称                                                              
                                   t_base_FunctionRole.Menu_Id,
                                   t_base_FunctionInfo.FUN_DICTORY
                            from 
                                   t_base_functionrole
                            left outer join
                                   t_base_FunctionInfo
                                   on t_base_functionrole.Fun_Id = t_base_FunctionInfo.Fun_Id ";
            if (RoleId != "")
            {
                //t_base_FunctionInfo.Fun_Tag,-- 功能tag
                //                  t_base_FunctionInfo.Fun_Image,--图片地址
                sql =@" select distinct
                                   t_base_FunctionInfo.Fun_Id,--功能编号
                                   t_base_FunctionInfo.Fun_Name,--功能名称                                                                
                                   t_base_FunctionRole.Menu_Id,
                                   t_base_FunctionInfo.fun_dictory
                            from 
                                   t_base_functionrole
                            left outer join
                                   t_base_FunctionInfo
                                   on t_base_functionrole.Fun_Id = t_base_FunctionInfo.Fun_Id
                                   where t_base_functionrole.role_id = '" + RoleId +@"'";
            }
            return sql;
        }

        /// <summary>
        /// 获取权限对应价格
        /// </summary>
        /// <param name="RoleId"></param>
        /// <returns></returns>
        private string GetRolePrice(string RoleId)
        {
            string sql = " select PRICE_ID,PRICE_NAME from t_base_priceinfo where 1 = 1 ";
            if (RoleId != "")
            {
                sql = sql + " and Price_Id in (select Price_Id from t_base_Price where Role_Id = '" + RoleId + "')";
            }
            return sql;
        }

        /// <summary>
        /// 供应商
        /// </summary>
        /// <param name="RoleId"></param>
        /// <returns></returns>
        private string GetRoleGYS(string RoleId)
        {
            string sql = "select * from T_BASE_GYSROLE where 1 = 1 ";
            if (RoleId != "")
            {
                sql = sql + " and ID in (select PRO_TYPE_ID from t_base_Provider where Role_Id = '" + RoleId + "') or ID in (select Provider_Id from t_base_Provider where Role_Id = '" + RoleId + "')";
            }
            return sql;
        }

        /// <summary>
        /// 客户
        /// </summary>
        /// <param name="RoleId"></param>
        /// <returns></returns>
        private string GetRoleKh(string RoleId)
        {
            string sql = "select * from CUSTOMER_ROLE where 1 = 1 ";
            if (RoleId != "")
            {
                sql = sql + " and ID in (select CUS_TYPE_ID from t_base_Customer where ROLE_ID = '" + RoleId + "') or ID in (select CUS_ID from t_base_Customer where ROLE_ID = '" + RoleId + "')";
            }
            return sql;
        }

        /// <summary>
        /// 店铺
        /// </summary>
        /// <param name="RoleId"></param>
        /// <returns></returns>
        private string GetRoleDP(string RoleId)
        {
            string sql = "select * from SHOP_ROLE where 1 = 1 ";
            if (RoleId != "")
            {
                sql = sql + " and ID in (select SHOP_ID from t_base_shop where Role_Id = '" + RoleId + "') or ID in (select SHOP_TYPE_ID from t_base_shop where Role_Id = '" + RoleId + "')";
            }
            return sql;
        }

        /// <summary>
        /// 仓库
        /// </summary>
        /// <param name="RoleId"></param>
        /// <returns></returns>
        private string GetRoleCK(string RoleId)
        {
            string sql = "select * from STORAGE_ROLE where 1 = 1 ";

            if (RoleId != "")
            {
                sql = sql + " and ID in (select Storage_Id from t_base_Area where ROLE_ID = '" + RoleId + "') or ID in (select Area_id from t_base_Area where ROLE_ID = '" + RoleId + "')";
            }
            return sql;
        }

        /// <summary>
        /// 商品分类
        /// </summary>
        /// <param name="RoleId"></param>
        /// <returns></returns>
        private string GetRoleSPFL(string RoleId)
        {
            string sql =@"select '-1' ID,'全部' NAME,'' PID from dual
                            union all 
                            select GOO_SORT_ID ID,GOO_SORT_NAME NAME,FID PID from T_BASE_GOODS_CATEGORY where 1 = 1 ";

            if (RoleId != "")
            {
                sql = sql + " and GOO_SORT_ID in (select GOO_SORT_ID from t_base_GoodsType where Role_Id = '" + RoleId + "') ";
            }
            return sql;
        }


        #region 获取菜单权限方法
        private string GetMENU()
        {
            string sql = "select MENU_ID,MENU_NAME,P_MENU_ID,MENU_LEVEL,MENU_SORT from t_base_menu";
            return sql;
        }

        private string GetFUN()
        {
            string sql = "select FUN_ID,FUN_NAME,P_FUN_ID from t_base_functioninfo";
            return sql;
        }
        #endregion

        #endregion

        /// <summary>
        /// 角色权限新增方法
        /// </summary>
        /// <param name="list">SQL语句集合</param>
        /// <returns></returns>
        public int InsertRole(List<string> list, ref string msg)
        {
            try
            {
                SystemDataObject.Instance.ExecuteList(list);
            }
            catch (Exception ex)
            {
                msg = ex.Message;
            }
            return 0;
        }

        /// <summary>
        /// 获取角色编号
        /// </summary>
        /// <returns></returns>
        public string GetRoleId()
        {
            string sql = "select NEXT VALUE FOR SEQ_ROLE_ID ";
            DataTable dt = SystemDataObject.Instance.GetDataTable(sql);
            return dt.Rows[0][0].ToString();
        }

        /// <summary>
        /// 根据用户编号获取角色信息
        /// </summary>
        /// <param name="strUserID">用户编号</param>
        /// <returns></returns>
        public DataTable GetUserRole(string strUserID)
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.AppendLine("select ROLE_ID,ROLE_NAME,1 as isCheck from T_BASE_ROLEDICTIONARY where ROLE_ID in (select ROLE_ID from T_BASE_ROLE where USER_ID = '" + strUserID + "')");
            strSQL.AppendLine("union all");
            strSQL.AppendLine("select ROLE_ID,ROLE_NAME,0 as isCheck from T_BASE_ROLEDICTIONARY where ROLE_ID not in (select ROLE_ID from T_BASE_ROLE where USER_ID = '" + strUserID + "')");

            DataTable dt;
            dt = SystemDataObject.Instance.GetDataTable(strSQL.ToString());
            return dt;
        }


        /// <summary>
        /// 通过部门编号 和用户名获得该部门下的角色
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="com_id"></param>
        /// <returns></returns>
        public DataTable GetRoleDataByComIDAndUserID(string userId, string com_id)
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append(" select ROLE_ID, ROLE_NAME, 1 as isCheck");
            strSQL.Append(" from T_BASE_ROLEDICTIONARY");
            strSQL.Append(" where  ");
            //strSQL.Append("ROLE_ID in (select role_id from T_BASE_ROLEDEPART where dep_id = '" + com_id + "')and");
            strSQL.Append("  ROle_id in (select role_id from t_Base_Role where user_id = '" + userId + "')");
            strSQL.Append(" union all");
            strSQL.Append(" select ROLE_ID, ROLE_NAME, 0 as isCheck");
            strSQL.Append(" from T_BASE_ROLEDICTIONARY");
            strSQL.Append(" where");
            //strSQL.Append("e ROLE_ID in (select role_id from T_BASE_ROLEDEPART where dep_id = '" + com_id + "') and");
            strSQL.Append("  ROle_id not in");
            strSQL.Append(" (select role_id from t_Base_Role where user_id = '" + userId + "')");

            return SystemDataObject.Instance.GetDataTable(strSQL.ToString());
        }
    }
}