using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using WMS.Model.Base;
using WMS.IData.ISystemManage;
using WMS.IData;using Oracle.ManagedDataAccess.Client;
using WMS.Common;
using WMS.DBUtility;
using WMS.Model.SystemManage;

namespace WMS.SqlServerData.SystemManageData
{
    public class SysRoleData:ISysRole
    {
        /// <summary>
        /// 获取角色信息
        /// </summary>
        /// <param name="strRoleId">Role Name</param>
        /// <returns></returns>
        public List<RoleModel> GetRole(string strRoleId)
        {
            List<RoleModel> listRole = new List<RoleModel>();
            string strSQL = "select * from T_BASE_ROLEDICTIONARY where 1 = 1 ";
            if (strRoleId != "")
            {
                strSQL += " and Role_Id = '" + strRoleId + "'";
            }
            strSQL = strSQL + " order by role_id asc";
            DataTable dt = SystemDataObject.Instance.GetDataTable(strSQL);
            if (dt != null)
            {
                listRole = ConvertHelper<RoleModel>.ConvertToList(dt);
            }
            return listRole;
        }

        /// <summary>
        /// 获取角色对应的用户信息
        /// </summary>
        /// <param name="strRoleId">Role Name</param>
        /// <returns></returns>
        public List<UserDataModel> GetRoleUser(string strRoleId)
        {
            List<UserDataModel> listUser = new List<UserDataModel>();
            string strSQL =@"select 
                                  T_BASE_USERINFO.* ,T_BASE_COMPANY.COM_NAME
                                from 
                                  T_BASE_ROLE
                                left outer join
                                  T_BASE_USERINFO on T_BASE_ROLE.USER_ID = T_BASE_USERINFO.USER_ID
                                left outer join
                                  T_BASE_COMPANY on T_BASE_USERINFO.COM_ID = T_BASE_COMPANY.COM_ID
                                where 1 = 1 ";
            if (strRoleId != "")
            {
                strSQL += " and T_BASE_ROLE.ROLE_ID = '" + strRoleId + "'";
            }
            DataTable dt = SystemDataObject.Instance.GetDataTable(strSQL);
            if (dt != null)
            {
                listUser = ConvertHelper<UserDataModel>.ConvertToList(dt);
            }
            return listUser;
        }

        /// <summary>
        /// 获取角色对象的功能操作
        /// </summary>
        /// <param name="strRoleId">Role Name</param>
        /// <returns></returns>
        public List<RoleMenuFunModel> GetRoleMenuFun(string strRoleId, string strType)
        {
            List<RoleMenuFunModel> list = new List<RoleMenuFunModel>();
            string strSQL = "";
            switch (strType)
            { 
                case "MenuFun":
                    strSQL = GetMenuFunSql(strRoleId);
                    break;
                case "GoodsType":
                    strSQL = GetGoodsTypeSql(strRoleId);
                    break;
                case "Provider":
                    strSQL = GetProviderSql(strRoleId);
                    break;
                case "Storage":
                    strSQL = GetStorageSql(strRoleId);
                    break;
            }
            DataTable dt = SystemDataObject.Instance.GetDataTable(strSQL);
            if (dt != null)
            {
                list = ConvertHelper<RoleMenuFunModel>.ConvertToList(dt);
            }
            return list;
        }

        /// <summary>
        /// 获取仓库权限SQL
        /// </summary>
        /// <param name="strRoleId"></param>
        /// <returns></returns>
        private string GetStorageSql(string strRoleId)
        {
            string strSQL = "";
            if (strRoleId != "")
            {
                strSQL =@"select '-1' as IDS,'全部' as NAMES,'0' as PID,'QB' as FLAG,'0' as ISCHECK,'' as VID from dual
                            union all
                            select STORAGE_TYPE_ID as IDS,STORAGE_TYPE_NAME as NAMES,'-1' as PID,'FL' as FLAG,'0' as ISCHECK,STORAGE_TYPE_ID as VID from T_BASE_STORAGE_TYPE
                            where STORAGE_TYPE_ID not in (select STORAGE_TYPE_ID from T_BASE_STORAGE_ROLE where ROLE_ID = '" + strRoleId +@"')
                            union all
                            select STORAGE_ID as IDS,STORAGE_NAME as NAMES,STORAGE_TYPE_ID as PID,'CK' as FLAG,'0' as ISCHECK,STORAGE_ID as VID from T_BASE_STORAGEINFO
                            where 
                            STORAGE_ID not in (select STORAGE_ID from T_BASE_STORAGE_ROLE where ROLE_ID = '" + strRoleId +@"')
                            union all
                            select STORAGE_AREA_ID as IDS,STORAGE_AREA_NAME as NAME,STORAGE_ID as PID,'KQ' as FLAG,'0' as ISCHECK,STORAGE_AREA_ID as VID from T_BASE_STORAGE_AREA
                            where 
                            STORAGE_AREA_ID not in (select STORAGE_AREA_ID from T_BASE_STORAGE_ROLE where ROLE_ID = '" + strRoleId +@"')
                            union all
                            select STORAGE_TYPE_ID as IDS,STORAGE_TYPE_NAME as NAMES,'-1' as PID,'FL' as FLAG,'1' as ISCHECK,STORAGE_TYPE_ID as VID from T_BASE_STORAGE_TYPE
                            where STORAGE_TYPE_ID in (select STORAGE_TYPE_ID from T_BASE_STORAGE_ROLE where ROLE_ID = '" + strRoleId +@"')
                            union all
                            select STORAGE_ID as IDS,STORAGE_NAME as NAMES,STORAGE_TYPE_ID as PID,'CK' as FLAG,'1' as ISCHECK,STORAGE_ID as VID from T_BASE_STORAGEINFO
                            where
                            STORAGE_ID in (select STORAGE_ID from T_BASE_STORAGE_ROLE where ROLE_ID = '" + strRoleId +@"')
                            union all
                            select STORAGE_AREA_ID as IDS,STORAGE_AREA_NAME as NAME,STORAGE_ID as PID,'KQ' as FLAG,'1' as ISCHECK,STORAGE_AREA_ID as VID from T_BASE_STORAGE_AREA
                            where 
                            STORAGE_AREA_ID in (select STORAGE_AREA_ID from T_BASE_STORAGE_ROLE where ROLE_ID = '" + strRoleId +@"')";
            }
            else
            {
                strSQL =@"select '-1' as IDS,'全部' as NAMES,'0' as PID,'QB' as FLAG,'0' as ISCHECK,'' as VID from dual
                            union all
                            select STORAGE_TYPE_ID as IDS,STORAGE_TYPE_NAME as NAMES,'-1' as PID,'FL' as FLAG,'0' as ISCHECK,STORAGE_TYPE_ID as VID from T_BASE_STORAGE_TYPE
                            union all
                            select STORAGE_ID as IDS,STORAGE_NAME as NAMES,STORAGE_TYPE_ID as PID,'CK' as FLAG,'0' as ISCHECK,STORAGE_ID as VID from T_BASE_STORAGEINFO
                            union all
                            select STORAGE_AREA_ID as IDS,STORAGE_AREA_NAME as NAME,STORAGE_ID as PID,'KQ' as FLAG,'0' as ISCHECK,STORAGE_AREA_ID as VID from T_BASE_STORAGE_AREA";
            }
            return strSQL;
        }

        /// <summary>
        /// 获取供应商权限SQL
        /// </summary>
        /// <param name="strRoleId"></param>
        /// <returns></returns>
        private string GetProviderSql(string strRoleId)
        {
            string strSQL = "";
            if (strRoleId != "")
            {
                strSQL =@"select '-1' as IDS,'全部' as NAMES,'0' as PID,'FL' as FLAG,'0' as ISCHECK,'' as VID from dual
                            union all
                            select PRO_TYPE_ID as IDS,PRO_TYPE_NAME as NAMES,'-1' as PID,'FL' as FLAG,'0' as ISCHECK,PRO_TYPE_ID as VID from t_base_provider_type
                            where PRO_TYPE_ID not in (select PRO_TYPE_ID from T_BASE_PROVIDER_ROLE where ROLE_ID = '" + strRoleId +@"')
                            union all
                            select PROVIDER_ID as IDS,PROVIDER_NAME as NAMES,PRO_TYPE_ID as PID,'GYS' as FLAG,'0' as ISCHECK,PROVIDER_ID as VID from t_base_provider_dictionary 
                            where PROVIDER_ID not in (select PROVIDER_ID from T_BASE_PROVIDER_ROLE where ROLE_ID = '" + strRoleId +@"')
                            union all
                            select PRO_TYPE_ID as IDS,PRO_TYPE_NAME as NAMES,'-1' as PID,'FL' as FLAG,'1' as ISCHECK,PRO_TYPE_ID as VID from t_base_provider_type
                            where PRO_TYPE_ID in (select PRO_TYPE_ID from T_BASE_PROVIDER_ROLE where ROLE_ID = '" + strRoleId +@"')
                            union all
                            select PROVIDER_ID as IDS,PROVIDER_NAME as NAMES,PRO_TYPE_ID as PID,'GYS' as FLAG,'1' as ISCHECK,PROVIDER_ID as VID from t_base_provider_dictionary 
                            where PROVIDER_ID in (select PROVIDER_ID from T_BASE_PROVIDER_ROLE where ROLE_ID = '" + strRoleId +@"')";
            }
            else
            {
                strSQL =@"select '-1' as IDS,'全部' as NAMES,'0' as PID,'FL' as FLAG,'0' as ISCHECK,'' as VID from dual
                            union all
                            select PRO_TYPE_ID as IDS,PRO_TYPE_NAME as NAMES,'-1' as PID,'FL' as FLAG,'0' as ISCHECK,PRO_TYPE_ID as VID from t_base_provider_type
                            union all
                            select PROVIDER_ID as IDS,PROVIDER_NAME as NAMES,PRO_TYPE_ID as PID,'GYS' as FLAG,'0' as ISCHECK,PROVIDER_ID as VID from t_base_provider_dictionary ";
            }
            return strSQL;
        }

        /// <summary>
        /// 获取商品分类权限SQL
        /// </summary>
        /// <param name="strRoleId"></param>
        /// <returns></returns>
        private string GetGoodsTypeSql(string strRoleId)
        {
            string strSQL = "";
            if (strRoleId != "")
            {
                strSQL =@"--select '-1' as IDS,'全部' as NAMES,'0' as PID,'FL' as FLAG,'0' as ISCHECK,'' as VID from dual
                            --union all
                            select GOODS_TYPE_ID as IDS,GOODS_TYPE_NAME as NAMES,PARENT_ID as PID,'FL' as FLAG,'0' as ISCHECK,GOODS_TYPE_ID as VID from T_BASE_GOODS_TYPE 
                            where GOODS_TYPE_ID not in (select GOODS_TYPE_ID from t_base_goods_role where ROLE_ID ='" + strRoleId +@"')
                            union all
                            select GOODS_TYPE_ID as IDS,GOODS_TYPE_NAME as NAMES,PARENT_ID as PID,'FL' as FLAG,'1' as ISCHECK,GOODS_TYPE_ID as VID from T_BASE_GOODS_TYPE 
                            where GOODS_TYPE_ID in (select GOODS_TYPE_ID from t_base_goods_role where ROLE_ID ='" + strRoleId +@"')";
            }
            else
            {
                strSQL =@"--select '-1' as IDS,'全部' as NAMES,'0' as PID,'FL' as FLAG,'0' as ISCHECK,'' as VID from dual
                            --union all
                            select GOODS_TYPE_ID as IDS,GOODS_TYPE_NAME as NAMES,PARENT_ID as PID,'FL' as FLAG,'0' as ISCHECK,GOODS_TYPE_ID as VID from T_BASE_GOODS_TYPE";
            }
            return strSQL;
        }

        /// <summary>
        /// 获取操作功能权限SQL
        /// </summary>
        /// <param name="strRoleId"></param>
        /// <returns></returns>
        private string GetMenuFunSql(string strRoleId)
        {
            string strSQL = "";
            if (strRoleId != "")
            {
                strSQL =@"  select '0' as IDS,'操作权限' as NAMES,'-1' as PID,'MENU' as FLAG,'0' as ISCHECK,'' as VID from dual
                                union all
                                select MENU_ID as IDS,MENU_NAME as NAMES,P_MENU_ID as PID,'MENU' as FLAG,'1' as IsCheck,MENU_ID as VID from T_BASE_MENU 
                                where MENU_ID in (select MENU_ID from T_BASE_MENUROLE where ROLE_ID = '" + strRoleId +@"')
                                union all 
                                select MENU_ID as IDS,MENU_NAME as NAMES,P_MENU_ID as PID,'MENU' as FLAG,'0' as IsCheck,MENU_ID as VID from T_BASE_MENU 
                                where MENU_ID not in (select MENU_ID from T_BASE_MENUROLE where ROLE_ID = '" + strRoleId +@"')
                                union all                                
                                select 'FUN:' + FUN_ID + ':' + MENU_ID as IDS,FUN_DICTORY as NAMES,MENU_ID as PID,'FUN' as FLAG,'1' as IsCheck,FUN_ID as VID from T_BASE_FUNCTIONINFO 
                                where MENU_ID in (select MENU_ID from T_BASE_FUNCTIONROLE where ROLE_ID = '" + strRoleId +@"') and FUN_ID in (select FUN_ID from T_BASE_FUNCTIONROLE where ROLE_ID = '" + strRoleId +@"') and length(menu_id)>2 and FUN_VISIBLE
='1'
                                union all
                                select 'FUN:' + FUN_ID +':' + MENU_ID as IDS,FUN_DICTORY as NAMES,MENU_ID as PID,'FUN' as FLAG,'0' as IsCheck,FUN_ID as VID from T_BASE_FUNCTIONINFO 
                                where MENU_ID not in (select MENU_ID from T_BASE_FUNCTIONROLE where ROLE_ID = '" + strRoleId +@"') and FUN_ID in (select FUN_ID from T_BASE_FUNCTIONROLE where ROLE_ID = '" + strRoleId +@"') and length(menu_id)>2 and FUN_VISIBLE
='1'";
            }
            else
            {
                strSQL =@"select '0' as IDS,'操作权限' as NAMES,'-1' as PID,'MENU' as FLAG,'0' as ISCHECK,'' as VID from dual
                                union all
                                select MENU_ID as IDS,MENU_NAME as NAMES,P_MENU_ID as PID,'MENU' as FLAG,'0' as IsCheck,MENU_ID as VID from T_BASE_MENU 
                                union all                                
                                select 'FUN:' + FUN_ID + ':' + MENU_ID as IDS,FUN_DICTORY as NAMES,MENU_ID as PID,'FUN' as FLAG,'0' as IsCheck,FUN_ID as VID from T_BASE_FUNCTIONINFO where length(menu_id)>2 and FUN_VISIBLE
='1'";
            }

            return strSQL;
        }

    }
}