using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.Entity;
using YX.Entity;
using System.Transactions;
namespace YX.DAL
{
    public class SystemUserInfo_Dal
    {
      
       public DataTable GetUserInfoByOrganization_Id(StringBuilder SqlWhere, IList<SqlParameter> IList_param)
        {
            DataTable dt = new DataTable();
            try
            {
                using (Sam_DBEntities db=new Sam_DBEntities())
                {

                    StringBuilder strSql = new StringBuilder();
                    strSql.Append(@"SELECT 
                 U.User_ID,
                U.User_Code as '用户工号',
                U.User_Name as '用户名称',
                U.User_Account as '登录账户',
 
                U.User_Sex as '性别',
                U.Title as '职称',
                U.DeleteMark as '状态',
                U.User_Remark as '备注',
                U.CreateDate  as'创建时间',
                U.Email as '邮箱'
                from Base_UserInfo U 
                LEFT JOIN Base_StaffOrganize S 
                ON U.User_ID = S.User_ID where U.DeleteMark !=0");
                    strSql.Append(SqlWhere);
                    strSql.Append(" GROUP BY U.User_ID,U.User_Code,U.User_Name,U.User_Account,U.User_Sex,U.Title,U.DeleteMark,U.User_Remark,U.CreateDate,U.Email");

                    dt = SqlHelper.ExecuteDataset(db.Database.Connection.ConnectionString, CommandType.Text, strSql.ToString(), IList_param.ToArray()).Tables[0];
                }

            }
            catch (Exception ex)
            {
                
                throw ex;
            }
            return dt;
        }
       /// <summary>
       /// 后台登陆验证
       /// </summary>
       /// <param name="name">账户</param>
       /// <param name="pwd">密码</param>
       /// <returns></returns>
       public Base_UserInfo UserLogin(string name, string pwd)
       {

           try
           {
               using (Sam_DBEntities db = new Sam_DBEntities())
               {
                  // var password = Md5Helper.Md5(pwd);
                   return db.Base_UserInfo.Where(o => o.User_Account == name && o.User_Pwd == pwd).FirstOrDefault();
               }

           }
           catch (Exception ex)
           {

               throw ex;
           }
       }
       /// <summary>
       /// 添加用户
       /// </summary>
       /// <param name="UserInfo"></param>
       /// <returns></returns>
       public int AddUserInfo(Base_UserInfo UserInfo)
       {
           int result = 0;
           try
           {
               using (Sam_DBEntities db = new Sam_DBEntities())
               {
                   using (TransactionScope trans = new TransactionScope())
                   {
                      // UserInfo.User_Pwd = Md5Helper.Md5("123456");
                       db.Base_UserInfo.Add(UserInfo);
                       result = db.SaveChanges();
                       trans.Complete();
                       return result;
                   }

               }
           }
           catch (Exception ex)
           {

               throw ex;
           }
       }
       /// <summary>
       /// 修改用户
       /// </summary>
       /// <param name="UserInfo"></param>
       /// <returns></returns>
       public int EditUserInfo(Base_UserInfo UserInfo)
       {
           int result = 0;
           try
           {
               using (Sam_DBEntities db = new Sam_DBEntities())
               {

                   using (TransactionScope trans = new TransactionScope())
                   {
                       //先删除所属部门                       
                       db.Database.ExecuteSqlCommand("delete from Base_StaffOrganize where User_ID={0}", UserInfo.User_ID);
                       //先删除用户权限               
                       db.Database.ExecuteSqlCommand("delete from Base_UserRight where User_ID={0}", UserInfo.User_ID);
                       //先删除属性
                       db.Database.ExecuteSqlCommand("delete from Base_AppendPropertyInstance where PropertyInstance_Key={0}", UserInfo.User_ID);
                        //先删除用户角色
                       db.Database.ExecuteSqlCommand("delete from Base_UserRole where User_ID={0}",UserInfo.User_ID);

                       //添加用户权限

                       if (UserInfo.Base_UserRight != null && UserInfo.Base_UserRight.Count>0)
                       {
                           string sql_userright = "";
                           foreach (var item in UserInfo.Base_UserRight)
                           {
                               sql_userright += string.Format(@"insert into Base_UserRight ([UserRight_ID]
                                                      ,[User_ID]
                                                      ,[Menu_Id]
                                                      ,[CreateDate]
                                                      ,[CreateUserId]
                                                      ,[CreateUserName])
                                        values('{0}','{1}','{2}','{3}','{4}','{5}');"
                                   , Guid.NewGuid().ToString(), UserInfo.User_ID, item.Menu_Id, DateTime.Now.ToString("yyyy-MM-dd HH: mm:ss"), item.CreateUserId, item.CreateUserName);
                           }
                           db.Database.ExecuteSqlCommand(sql_userright);
                       }

                       //添加所属部门
                       if (UserInfo.Base_StaffOrganize != null && UserInfo.Base_StaffOrganize.Count>0)
                       {
                           string sql_staff = "";
                           foreach (var item in UserInfo.Base_StaffOrganize)
                           {
                               sql_staff += string.Format(@"insert into Base_StaffOrganize 
                            ([StaffOrganize_Id]  ,[Organization_ID],[User_ID] ,[CreateDate] ,[CreateUserId],[CreateUserName] )
                            values('{0}','{1}','{2}','{3}','{4}','{5}');", Guid.NewGuid().ToString(), item.Organization_ID, UserInfo.User_ID, DateTime.Now.ToString("yyyy-MM-dd HH: mm:ss"),
                                                                       item.CreateUserId, item.CreateUserName);
                           }
                           db.Database.ExecuteSqlCommand(sql_staff);
                       }
                        //添加所属角色
                        if (UserInfo.Base_UserRole != null && UserInfo.Base_UserRole.Count>0)
                        {
                            string sql_user_role = "";
                            foreach (var item in UserInfo.Base_UserRole)
                            {
                                sql_user_role += string.Format(@"insert into Base_UserRole( [UserRole_ID]
                                  ,[User_ID],[Roles_ID],[CreateDate],[CreateUserId],[CreateUserName])
                                    values('{0}','{1}','{2}','{3}','{4}','{5}')",Guid.NewGuid().ToString(),UserInfo.User_ID,item.Roles_ID,DateTime.Now.ToString("yyyy-MM-dd HH: mm:ss"),
                                    item.CreateUserId,item.CreateUserName);
                            }
                            db.Database.ExecuteSqlCommand(sql_user_role);
                        }
                       //添加属性
                       if (UserInfo.Base_AppendPropertyInstance != null && UserInfo.Base_AppendPropertyInstance.Count>0)
                       {
                           string sql_append = "";
                           foreach (var item in UserInfo.Base_AppendPropertyInstance)
                           {
                               sql_append += string.Format(@"insert into Base_AppendPropertyInstance([PropertyInstance_ID] ,[Property_Control_ID] ,
                                [PropertyInstance_Value],[PropertyInstance_Key])values('{0}','{1}','{2}','{3}');",
                                            Guid.NewGuid().ToString(), item.Property_Control_ID, item.PropertyInstance_Value, UserInfo.User_ID);
                           }
                           db.Database.ExecuteSqlCommand(sql_append);
                       }
                      
                       //修改信息
                       db.Base_UserInfo.Attach(UserInfo);
                       //把当前实体的状态改为Modified
                       db.Entry(UserInfo).State = EntityState.Modified;//会全部字段修改
                       db.Entry(UserInfo).Property("User_Pwd").IsModified = false;//不修改某字段
                       db.Entry(UserInfo).Property("CreateDate").IsModified = false;//不修改某字段
                       db.Entry(UserInfo).Property("CreateUserId").IsModified = false;//不修改某字段
                       db.Entry(UserInfo).Property("CreateUserName").IsModified = false;//不修改某字段
                       result = db.SaveChanges();
                       trans.Complete();
                       return result;
                   }

               }
           }
           catch (Exception ex)
           {

               throw ex;
           }
       }
    }
}