using Dapper;
using Newtonsoft.Json;
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 BasicSettingDAL
    {
        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 ToMessage OrganizationSearch(string orgCode, string orgName, string orgType, string userName,int startNum,int endNum,string prop,string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                if (orgCode != "" && orgCode != null)
                {
                    search += "and parent.org_code like '%'+@orgCode+'%' ";
                    dynamicParams.Add("@orgCode", orgCode);
                }
                if (orgName != "" && orgName != null)
                {
                    search += "and parent.org_name like '%'+@orgName+'%' ";
                    dynamicParams.Add("@orgName", orgName);
                }
                if (orgType != "" && orgType != null)
                {
                    search += "and parent.description=@orgType ";
                    dynamicParams.Add("@orgType", orgType);
                }
                if (userName != "" && userName != null)
                {
                    search += "and parent.lm_user like '%'+@userName+'%' ";
                    dynamicParams.Add("@userName", userName);
                }
                if (search == "")
                {
                    search = "and 1=1 ";
                }
                //search = search.Substring(3);//截取索引2后面的字符
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select 
                            parent.id,
                            parent.org_code,
                            parent.org_name,
                            parent.description as description,
                            parent.parent_id,
                            child.org_code as parentorg_code, 
                            child.org_name as parentorg_name,
                            parent.lm_user,
                            parent.lm_date
                            from TOrganization as parent 
                            left join TOrganization as child on parent.parent_id=child.id where parent.is_delete<>1 " + search;
                var data = DapperHelper.GetPageList<object>(sql, dynamicParams,prop,order, startNum, endNum, out total);
                mes.code = "200";
                mes.Message = "查询成功!";
                mes.count = total;
                mes.data = data.ToList();
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.Message = e.Message;
            }
            return mes;
        }
        #endregion

        #region[新增编辑组织架构]
        public static ToMessage AddUpdateOrganization(string organType, string organCode, string organName,string Operator, int supUnit, string operType)
        {
            var dynamicParams = new DynamicParameters();
            try
            {
                if (operType == "Add")
                {
                    var sql = @"insert into TOrganization(parent_id,org_code,org_name,lm_user,lm_date,description) 
                            values(@supUnit,@organCode,@organName,@Operator,@CreateDate,@organType)";
                    dynamicParams.Add("@supUnit", supUnit);
                    dynamicParams.Add("@organCode", organCode);
                    dynamicParams.Add("@organName", organName);
                    dynamicParams.Add("@Operator", Operator);
                    dynamicParams.Add("@CreateDate", DateTime.Now.ToString());
                    dynamicParams.Add("@organType", organType);
                    int cont = DapperHelper.SQL(sql, dynamicParams);
                    if (cont > 0)
                    {
                        mes.code = "200";
                        mes.Message = "新增操作成功!";
                    }
                    else
                    {
                        mes.code = "300";
                        mes.Message = "新增操作失败!";
                    }
                }
                if (operType == "Update") 
                {
                    var sql = @"update TOrganization set description=@organType,org_name=@organName,parent_id=@supUnit,lm_user=@Operator,lm_date=@CreateDate where org_code=@organCode";
                    dynamicParams.Add(@"organCode", organCode);
                    dynamicParams.Add("@supUnit", supUnit);
                    dynamicParams.Add("@organName", organName);
                    dynamicParams.Add("@Operator", Operator);
                    dynamicParams.Add("@CreateDate", DateTime.Now.ToString());
                    dynamicParams.Add("@organType", organType);
                    int cont = DapperHelper.SQL(sql, dynamicParams);
                    if (cont > 0)
                    {
                        mes.code = "200";
                        mes.Message = "修改操作成功!";
                    }
                    else
                    {
                        mes.code = "300";
                        mes.Message = "修改操作失败!";
                    }
                } 
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.Message = e.Message;
            }
            return mes;
        }
        #endregion

        #region [组织架构删除]
        public static ToMessage DeleteOrganization(int orgid)
        {
            var sql = "";
            var cont = 0;
            var dynamicParams = new DynamicParameters();
            try
            {
                sql = @"select * from  TOrganization  where parent_id=@orgid";
                dynamicParams.Add(@"orgid", orgid);
                cont = DapperHelper.selectcount(sql, dynamicParams);
                if (cont <= 0)
                {
                    mes.code = "300";
                    mes.Message = "当前组织有下级关联,请先删除下级组织!";
                }
                else
                {
                    sql = @"update TOrganization set is_delete='1'  where id=@orgid";
                    dynamicParams.Add(@"orgid", orgid);
                    cont = DapperHelper.SQL(sql, dynamicParams);
                    if (cont > 0)
                    {
                        mes.code = "200";
                        mes.Message = "删除操作成功!";
                    }
                    else
                    {
                        mes.code = "300";
                        mes.Message = "删除操作失败!";
                    }
                }
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.Message = e.Message;
            }
            return mes;
        }
        #endregion



        #region[用户清单数据查询]
        public static ToMessage UserSearch(string UserCode, string UserName, string StuOrg, string Enable, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                if (UserCode != "" && UserCode != null)
                {
                    search += "and u.usercode like '%'+@UserCode+'%' ";
                    dynamicParams.Add("@UserCode", UserCode);
                }
                if (UserName != "" && UserName != null)
                {
                    search += "and u.username like '%'+@UserName+'%' ";
                    dynamicParams.Add("@UserName", UserName);
                }
                if (StuOrg != "" && StuOrg != null)
                {
                    search += "and u.stu_torgcode=@StuOrg ";
                    dynamicParams.Add("@StuOrg", StuOrg);
                }
                if (Enable != "" && Enable != null)
                {
                    search += "and u.enable=@Enable ";
                    dynamicParams.Add("@Enable", Enable);
                }
                if (search == "")
                {
                    search = "and 1=1 ";
                }
                //search = search.Substring(3);//截取索引2后面的字符
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select u.usercode,u.username,u.stu_torgcode,t.org_name,u.enable,u.lm_user,u.lm_date,u.is_role  
                            from TUser u
                            left join TOrganization t on u.stu_torgcode=t.org_code where u.is_delete<>1 " + search;
                var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
                mes.code = "200";
                mes.Message = "查询成功!";
                mes.count = total;
                mes.data = data.ToList();
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.Message = e.Message;
            }
            return mes;
        }
        #endregion

        #region[用户清单新增编辑]
        public static ToMessage AddUpdateUser(string UserId, string UserCode, string UserName, string Enable, string StuOrg,string Mobile,string Email, string Operator, string OperType)
        {
            var dynamicParams = new DynamicParameters();
            try
            {
                if (OperType == "Add")
                {
                    var sql = @"insert into TUser(usercode,username,enable,mobile,lm_date,email,lm_user,stu_torgcode) 
                            values(@UserCode,@UserName,@Enable,@CreateDate,@Email,@Operator,@StuOrg)";
                    dynamicParams.Add("@UserCode", UserCode);
                    dynamicParams.Add("@UserName", UserName);
                    dynamicParams.Add("@Enable", Enable);
                    dynamicParams.Add("@Mobile", Mobile);
                    dynamicParams.Add("@CreateDate", DateTime.Now.ToString());
                    dynamicParams.Add("@Email", Email);
                    dynamicParams.Add("@Operator", Operator);
                    dynamicParams.Add("@StuOrg", StuOrg);
                    int cont = DapperHelper.SQL(sql, dynamicParams);
                    if (cont > 0)
                    {
                        mes.code = "200";
                        mes.Message = "新增操作成功!";
                    }
                    else
                    {
                        mes.code = "300";
                        mes.Message = "新增操作失败!";
                    }
                }
                if (OperType == "Update")
                {
                    var sql = @"update TUser set username=@UserName,enable=@Enable,mobile=@Mobile,lm_user=@Operator,email=@Email,stu_torgcode=@StuOrg,lm_date=@CreateDate where id=@UserId";
                    dynamicParams.Add("@UserId", UserId);
                    dynamicParams.Add("@UserName", UserName);
                    dynamicParams.Add("@Enable", Enable);
                    dynamicParams.Add("@Mobile", Mobile);
                    dynamicParams.Add("@CreateDate", DateTime.Now.ToString());
                    dynamicParams.Add("@Email", Email);
                    dynamicParams.Add("@Operator", Operator);
                    dynamicParams.Add("@StuOrg", StuOrg);
                    int cont = DapperHelper.SQL(sql, dynamicParams);
                    if (cont > 0)
                    {
                        mes.code = "200";
                        mes.Message = "修改操作成功!";
                    }
                    else
                    {
                        mes.code = "300";
                        mes.Message = "修改操作失败!";
                    }
                }
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.Message = e.Message;
            }
            return mes;
        }
        #endregion

        #region[用户清单删除]
        public static ToMessage DeleteUser(int userid)
        {
            var sql = "";
            //List<Dictionary<object, string>> list = new List<Dictionary<string, string>>();
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                sql = @"select usercode from  TUser  where id=@userid";
                dynamicParams.Add("@userid", userid);
                var data = DapperHelper.selectdata(sql, dynamicParams);

                sql = @"update TUserRoleRelation set is_delete='1'  where user_code=@usercode";
                dynamicParams.Add("@usercode",data.Rows[0]["USERCODE"].ToString());
                list.Add(new { str = sql, parm = dynamicParams });

                sql = @"update TUser set is_delete='1'  where id=@userid";
                dynamicParams.Add("@userid", userid);
                list.Add(new { str = sql, parm = dynamicParams });
                bool aa = DapperHelper.DoTransaction(list);
                if (aa)
                {
                    mes.code = "200";
                    mes.Message = "删除用户成功!";
                }
                else 
                {
                    mes.code = "300";
                    mes.Message = "删除用户失败!";
                }
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.Message = e.Message;
            }
            return mes;
        }
        #endregion

        #region[用户清单关联角色查询]
        public static ToMessage UserAssociationRole(string usercode)
        {
            string sql = "";
            var dynamicParams = new DynamicParameters();
            List<TreeObejct> list = new List<TreeObejct>();
            try
            {
                //获取角色类型集合(包含绑定标识)
                sql = @"select A.roletype_code as code,A.roletype_name as name,(case when B.roletype_code is null then 'N' else 'Y' end) flag  from TRoleType A 
                        left join (
                        select distinct B.roletype_code from TRole B 
                        inner join TUserRoleRelation C on B.role_code=C.role_code
                        where C.user_code=@usercode 
                        ) B
                        on A.roletype_code=B.roletype_code 
                        where  A.is_delete='0'";
                dynamicParams.Add("@usercode", usercode);
                var data = DapperHelper.select<TreeObejct>(sql, dynamicParams);
                for (int i = 0; i < data.Count; i++)
                {
                    sql = @"select B.role_code as code,B.role_name as name,(case when C.role_code is null then 'N' else 'Y' end) flag from TRole B 
                            left join TUserRoleRelation C on B.role_code=C.role_code
                            where B.roletype_code=@roletype_code and  C.user_code=@usercode and B.is_delete='0'";
                    dynamicParams.Add("@roletype_code", data[i].code);
                    dynamicParams.Add("@usercode", usercode);
                    var data0 = DapperHelper.select<TreeObejctCont>(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 RoleSearch(string RoleCode, string RoleName, string RoleTypeCode, string CreateUser, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                if (RoleCode != "" && RoleCode != null)
                {
                    search += "and A.role_code like '%'+@RoleCode+'%' ";
                    dynamicParams.Add("@RoleCode", RoleCode);
                }
                if (RoleName != "" && RoleName != null)
                {
                    search += "and A.role_name like '%'+@RoleName+'%' ";
                    dynamicParams.Add("@RoleName", RoleName);
                }
                if (RoleTypeCode != "" && RoleTypeCode != null)
                {
                    search += "and A.roletype_code=@RoleTypeCode ";
                    dynamicParams.Add("@RoleTypeCode", RoleTypeCode);
                }
                if (CreateUser != "" && CreateUser != null)
                {
                    search += "and A.lm_user like '%'+@CreateUser+'%' ";
                    dynamicParams.Add("@CreateUser", CreateUser);
                }
                if (search == "")
                {
                    search = "and 1=1 ";
                }
                //search = search.Substring(3);//截取索引2后面的字符
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select A.role_code,A.role_name,b.roletype_code,B.roletype_name,A.lm_user,A.lm_date,A.is_user,A.is_right  
                            from TRole A
                            left join TRoleType  B on A.roletype_code=B.roletype_code
                            where  A.is_delete<>'1' " + search;
                var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
                mes.code = "200";
                mes.Message = "查询成功!";
                mes.count = total;
                mes.data = data.ToList();
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.Message = e.Message;
            }
            return mes;
        }
        #endregion
    }
}