using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using VueWebApi.Models;
using VueWebApi.Tools;

namespace VueWebApi.DLL.DAL
{
    public class LoginDAL
    {
        public static DataTable dt;    //定义全局变量dt
        public static bool res;       //定义全局变量dt
        public static ToMessage mes = new ToMessage(); //定义全局返回信息对象
        public static string strProcName = ""; //定义全局sql变量
        public static List<SqlParameter> listStr = new List<SqlParameter>(); //定义全局参数集合
        public static SqlParameter[] parameters; //定义全局SqlParameter参数数组

        #region [登录查询]
        public static DataTable LoginSearch(string username,string password)
        {
          
            //执行sql
            strProcName = "select *  from [dbo].[TUser] where username=@username and password=@password";
            //创建参数
            listStr.Add(new SqlParameter("@username", username));
            listStr.Add(new SqlParameter("@password", password));
            parameters = listStr.ToArray();
            listStr.Clear();
            DataTable dt = DBHelper.GetTable(strProcName, parameters);
            return dt;
        }
        #endregion


        #region[查询菜单功能]
        public static ToMessage LoginMenu(string usercode)
        {
            string sql = "";
            var dynamicParams = new DynamicParameters();
            List<TreeObejct> list = new List<TreeObejct>();
            try
            {
                //获取菜单集合(根据登录用户编码查询权限反推菜单)
                sql = @"select distinct rt.right_code as code,rt.right_name as name,rt.right_seq from TRight rt where rt.right_code in( 
							select A.parent_id  from TRight A
							left join TRoleRightRelation B on B.right_code=A.right_code
							left join TRole C on C.role_code=B.role_code
							left join TUserRoleRelation D on D.role_code=C.role_code
							left join TUser E on E.usercode=D.user_code
							where E.usercode=@usercode)  and rt.Type='PC' order by rt.right_seq";
                dynamicParams.Add("@usercode", usercode);
                var data = DapperHelper.select<TreeObejct>(sql, dynamicParams);
                for (int i = 0; i < data.Count; i++)
                {
                    sql = @"select distinct mu.right_code as code,mu.right_name as name,mu.right_seq
							from TRight mu
                            inner join TRoleRightRelation rl on mu.right_code=rl.right_code
                            inner join TUserRoleRelation ro on rl.role_code=ro.role_code
                            where mu.type='PC' and ro.user_code=@usercode and mu.parent_id=@paent_id 
							order by mu.right_seq, mu.right_code";
                    dynamicParams.Add("@usercode", usercode);
                    dynamicParams.Add("@paent_id", data[i].code);
                    var data0 = DapperHelper.select<TreeObejctCn>(sql, dynamicParams);
                    data[i].children = data0;

                }
                mes.code = "200";
                mes.Message = "查询成功!";
                mes.data = data;
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.Message = e.Message;
            }
            return mes;
        }
        #endregion


        #region[修改密码]
        public static ToMessage UpdateUserPassword(string username, string password, string newpassword)
        {
         
            //执行sql，判断当前用户登录账号是否正确
            strProcName = "select *  from [dbo].[TUser] where username=@username and password=@password";
            //创建参数
            listStr.Add(new SqlParameter("@username", username));
            listStr.Add(new SqlParameter("@password", password));
            parameters = listStr.ToArray();
            listStr.Clear();
            dt = DBHelper.GetTable(strProcName, parameters);
            if (dt.Rows.Count <= 0)
            {
                mes.code = "300";
                mes.Message = "输入的密码不正确,请重新输入！";
            }
            else 
            {
                //执行sql，判断当前用户登录账号是否正确
                strProcName = "update [dbo].[TUser] set password=@newpassword where username=@username and password=@password";
                //创建参数
                listStr.Add(new SqlParameter("@username", username));
                listStr.Add(new SqlParameter("@password", password));
                listStr.Add(new SqlParameter("@newpassword", newpassword));
                parameters = listStr.ToArray();
                listStr.Clear();
                res = DBHelper.ExecuteSql(strProcName, parameters);
                if (res)
                {
                    mes.code = "200";
                    mes.Message = "修改密码成功！";
                }
                else 
                {
                    mes.code = "300";
                    mes.Message = "修改密码失败！";
                }               
            }
            return mes;
        }
        #endregion



    }
}