用户清单导入接口编写

yl

2022-06-17 5505c9884b67275780b25214cdbfcc4a2ac1578a

 VueWebApi/DLL/DAL/BasicSettingDAL.cs

@@ -84,6 +84,7 @@
        #region [组织架构查找上级单位]
        public static ToMessage PrentOrganization(string orgcode)
        {
            var sql = "";
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
@@ -116,11 +117,14 @@
                        dynamicParams.Add("@orgcode", orgcode);
                        break;
                    default:
                        orgcode = "";
                        search += "and description=@orgcode ";
                        dynamicParams.Add("@orgcode", orgcode);
                        break;
                }
                var sql = @"select roletype_code,roletype_name 
                            from TRoleType 
                            where  A.is_delete<>'1' " + search;
                   sql = @"select id,org_code,org_name 
                            from TOrganization 
                            where  is_delete<>'1' " + search;

                var data = DapperHelper.selectdata(sql, dynamicParams);
                mes.code = "200";
@@ -240,7 +244,7 @@


        #region[用户清单数据查询]
        public static ToMessage UserSearch(string UserCode, string UserName, string StuOrg, string Enable, int startNum, int endNum, string prop, string order)
        public static ToMessage UserSearch(string UserCode, string UserName, string StuOrg,string wagetype, string Enable, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
@@ -261,6 +265,11 @@
                    search += "and u.stu_torgcode=@StuOrg ";
                    dynamicParams.Add("@StuOrg", StuOrg);
                }
                if (wagetype != "" && wagetype != null)
                {
                    search += "and u.wagetype=@wagetype ";
                    dynamicParams.Add("@wagetype", wagetype);
                }
                if (Enable != "" && Enable != null)
                {
                    search += "and u.enable=@Enable ";
@@ -273,7 +282,7 @@
                //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  
                var sql = @"select u.id,u.usercode,u.username,u.stu_torgcode,t.org_name,u.password,u.mobile,u.email,u.wagetype,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);
@@ -354,23 +363,25 @@
        #endregion

        #region[用户清单新增编辑]
        public static ToMessage AddUpdateUser(string UserId, string UserCode, string UserName, string Enable, string StuOrg, string Mobile, string Email, string Operator, string OperType)
        public static ToMessage AddUpdateUser(string UserId, string UserCode, string UserName,string Password, string Enable, string StuOrg, string wagetype, 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)";
                    var sql = @"insert into TUser(usercode,username,password,enable,mobile,lm_date,email,lm_user,stu_torgcode,wagetype) 
                            values(@UserCode,@UserName,@password,@Enable,@Mobile,@CreateDate,@Email,@Operator,@StuOrg,@wagetype)";
                    dynamicParams.Add("@UserCode", UserCode);
                    dynamicParams.Add("@UserName", UserName);
                    dynamicParams.Add("@password", Password);
                    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);
                    dynamicParams.Add("@wagetype", wagetype);
                    int cont = DapperHelper.SQL(sql, dynamicParams);
                    if (cont > 0)
                    {
@@ -385,15 +396,17 @@
                }
                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";
                    var sql = @"update TUser set username=@UserName,password=@password,enable=@Enable,mobile=@Mobile,lm_user=@Operator,email=@Email,stu_torgcode=@StuOrg,wagetype=@wagetype,lm_date=@CreateDate where id=@UserId";
                    dynamicParams.Add("@UserId", UserId);
                    dynamicParams.Add("@UserName", UserName);
                    dynamicParams.Add("@password", Password);
                    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);
                    dynamicParams.Add("@wagetype", wagetype);
                    int cont = DapperHelper.SQL(sql, dynamicParams);
                    if (cont > 0)
                    {
@@ -479,8 +492,8 @@
                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'";
                             left join(select role_code  from TUserRoleRelation where user_code=@usercode) C on B.role_code=C.role_code
                             where B.roletype_code=@roletype_code  and B.is_delete='0'";
                    dynamicParams.Add("@roletype_code", data[i].code);
                    dynamicParams.Add("@usercode", usercode);
                    var data0 = DapperHelper.select<TreeObejctCn>(sql, dynamicParams);
@@ -553,6 +566,68 @@
                {
                    mes.code = "300";
                    mes.Message = "操作失败!";
                }
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.Message = e.Message;
            }
            return mes;
        }
        #endregion

        #region[用户清单导入]
        public static ToMessage ImportUserExcel(string username, string filesName)
        {
            var sql = "";
            List<object> list = new List<object>();
            DataTable ds = new DataTable();
            var dynamicParams = new DynamicParameters();

            try
            {
                ds = NPOIHelper.GetExcelDatatable(filesName);           //调用自定义方法
                int rowsnum = ds.Rows.Count;
                if (rowsnum == 0)
                {
                    mes.code = "300";
                    mes.Message = "Excel表格为空,无数据！";
                }
                else
                {
                    list.Clear();
                    for (int i = 0; i < ds.Rows.Count; i++)
                    {

                        if (ds.Rows[i][1].ToString() != "" && ds.Rows[i][1].ToString() != null)
                        {
                            sql = @"insert into TUser(usercode,username,enable,password,mobile,email,lm_user,lm_date,wagetype,stu_torgcode)
                                    values(@usercode,@username,@enable,@password,@mobile,@email,@lm_user,@lm_date,@wagetype,@stu_torgcode)";
                            dynamicParams.Add("@usercode", ds.Rows[i][1].ToString());
                            dynamicParams.Add("@username", ds.Rows[i][2].ToString());
                            dynamicParams.Add("@enable", ds.Rows[i][3].ToString());
                            dynamicParams.Add("@password", ds.Rows[i][4].ToString());
                            dynamicParams.Add("@mobile", ds.Rows[i][5].ToString());
                            dynamicParams.Add("@email", ds.Rows[i][6].ToString());
                            dynamicParams.Add("@lm_user", username);
                            dynamicParams.Add("@lm_date", DateTime.Now.ToString());
                            dynamicParams.Add("@wagetype", ds.Rows[i][7].ToString());
                            dynamicParams.Add("@stu_torgcode", ds.Rows[i][8].ToString());
                            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)
@@ -895,9 +970,14 @@
                list.Clear();
                if (json == null || json.Count <= 0)
                {
                    mes.code = "300";
                    mes.Message = "请先选择关联角色的用户信息";
                    return mes;
                    //清除用户关联角色表数据
                    sql = @"delete TUserRoleRelation where role_code=@rolecode";
                    dynamicParams.Add("@rolecode", rolecode);
                    list.Add(new { str = sql, parm = dynamicParams });
                    //标记角色表关联用户标识
                    sql = @"update TRole set is_user='N' where role_code=@rolecode";
                    dynamicParams.Add("@rolecode", rolecode);
                    list.Add(new { str = sql, parm = dynamicParams });
                }
                else
                {
@@ -939,6 +1019,219 @@
        }
        #endregion

        #region[角色清单关联功能查询]
        public static ToMessage RoleAssociationRight(string rolecode,string type)
        {

            Dictionary<object, object> dList = new Dictionary<object, object>();
            List<object> list=new List<object>();
            try
            {
                if (type == "" || type == null)
                {
                    list.Add(SeachEncode.RightLocation(rolecode, "PC"));
                    list.Add(SeachEncode.RightLocation(rolecode, "APP"));
                }
                else if (type == "APP")
                {
                    list.Add(SeachEncode.RightLocation(rolecode, type));
                }
                else {
                    list.Add(SeachEncode.RightLocation(rolecode, type));
                }
                mes.code = "200";
                mes.Message = "查询成功!";
                mes.data = list;
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.Message = e.Message;
            }
            return mes;
        }
        #endregion

        #region [角色清单关联功能保存]
        public static ToMessage SaveUserAssoctRight(string rolecode,string usercode, List<TreeDM> json)
        {
            var sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                list.Clear();
                if (json == null || json.Count <= 0)
                {
                    //清除用户关联功能表数据
                    sql = @"delete TRoleRightRelation where role_code=@rolecode";
                    dynamicParams.Add("@rolecode", rolecode);
                    list.Add(new { str = sql, parm = dynamicParams });
                    //标记角色表关联功能标识
                    sql = @"update TRole set is_right='N' where role_code=@rolecode";
                    dynamicParams.Add("@rolecode", rolecode);
                    list.Add(new { str = sql, parm = dynamicParams });
                }
                else
                {
                    //清除角色关联功能表数据
                    sql = @"delete TRoleRightRelation where role_code=@rolecode";
                    dynamicParams.Add("@rolecode", rolecode);
                    list.Add(new { str = sql, parm = dynamicParams });
                    //循环写入用户关联角色表
                    for (int i = 0; i < json.Count; i++)  //PC、APP层
                    {
                        for (int j = 0; j < json[i].children.Count; j++) //一级菜单
                        { 
                            for (int k = 0; k < json[i].children[j].children.Count; k++) //二级菜单
                            {
                                sql = @"insert into TRoleRightRelation(role_code,right_code,lm_user,lm_date) values(@role_code,@right_code,@lm_user,@lm_date)";
                                dynamicParams.Add("@role_code", rolecode);
                                dynamicParams.Add("@right_code", json[i].children[j].children[k].code);
                                dynamicParams.Add("@lm_user", usercode);
                                dynamicParams.Add("@lm_date", DateTime.Now.ToString());
                                list.Add(new { str = sql, parm = dynamicParams });
                            }
                        }
                    }
                    //标记角色表关联用户标识
                    sql = @"update TRole set is_right='Y' where role_code=@rolecode";
                    dynamicParams.Add("@rolecode", rolecode);
                    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 CurrentUnitSearch(string cuntUnitCode, string cuntUnitName, string unitAttr, string createUser, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                if (cuntUnitCode != "" && cuntUnitCode != null)
                {
                    search += "and code like '%'+@cuntUnitCode+'%' ";
                    dynamicParams.Add("@cuntUnitCode", cuntUnitCode);
                }
                if (cuntUnitName != "" && cuntUnitName != null)
                {
                    search += "and name like '%'+@cuntUnitName+'%' ";
                    dynamicParams.Add("@cuntUnitName", cuntUnitName);
                }
                if (unitAttr != "" && unitAttr != null)
                {
                    search += "and unitattribute=@unitAttr ";
                    dynamicParams.Add("@unitAttr", unitAttr);
                }
                if (createUser != "" && createUser != null)
                {
                    search += "and lm_user like '%'+@createUser+'%' ";
                    dynamicParams.Add("@createUser", createUser);
                }
                if (search == "")
                {
                    search = "and 1=1 ";
                }
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select id,code,name,unitattribute,conttacts,addr,lm_user,lm_date from TCustomer
                            where  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 AddUpdateCurrentUnit(string unitid, string unitcode, string unitname, string unitattr, string person, string contact, string description, string usercode,string OperType)
        {
            var dynamicParams = new DynamicParameters();
            try
            {
                if (OperType == "Add")
                {
                    var sql = @"insert into TCustomer(code,name,unitattribute,conttacts,conttphone,addr,lm_user,lm_date) 
                            values(@unitcode,@unitname,@unitattr,@person,@contact,@description,@usercode,@CreateDate)";
                    dynamicParams.Add("@unitcode", unitcode);
                    dynamicParams.Add("@unitname", unitname);
                    dynamicParams.Add("@unitattr", unitattr);
                    dynamicParams.Add("@person", person);
                    dynamicParams.Add("@contact", contact);
                    dynamicParams.Add("@description", description);
                    dynamicParams.Add("@usercode", usercode);
                    dynamicParams.Add("@CreateDate", DateTime.Now.ToString());
                    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 TCustomer set name=@unitname,unitattribute=@unitattr,conttacts=@person,conttphone=@contact,addr=@description,lm_user=@usercode,lm_date=@CreateDate where id=@RoleId";
                    dynamicParams.Add("@unitid", unitid);
                    dynamicParams.Add("@unitname", unitname);
                    dynamicParams.Add("@unitattr", unitattr);
                    dynamicParams.Add("@person", person);
                    dynamicParams.Add("@contact", contact);
                    dynamicParams.Add("@description", description);
                    dynamicParams.Add("@usercode", usercode);
                    dynamicParams.Add("@CreateDate", DateTime.Now.ToString());
                    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
    }
}