OA2021/DAL/TravelUsersService.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Models;

namespace DAL
{
    /// <summary>
    /// 雷怡 2021-10-12 11:51
    /// 外部用户登陆表数据访问层
    /// </summary>
    public class TravelUsersService
    {
        List<TravelUsers> excuteSql(string sql, params SqlParameter[] param)
        {
            return ServiceBase<TravelUsers>.excuteSql(new TravelUsers(), "TravelUsers", sql, CommandType.Text, param);
        }

        TravelUsers excuteType(string sql, params SqlParameter[] param)
        {
            //查询结果放入对象集合
            List<TravelUsers> hdList = excuteSql(sql, param);
            TravelUsers tu = new TravelUsers();

            //判断集合是否为空
            if (hdList == null || hdList.Count == 0)
            {
                return tu;
            }
            //返回单个对象
            return hdList[0];
        }

        /// <summary>
        /// 雷怡 2021-10-12 15:09
        /// 根据条件查询条件获取 - 分页
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="sumPage"></param>
        /// <param name="totalRecord"></param>
        /// <param name="name"></param>
        /// <returns></returns>
        public List<TravelUsers> GetAll(int pageIndex, out int sumPage, out int totalRecord)
        {
            string sqlwhere = " IsDel = 0";

            //if (departmentType != "全部")
            //    sqlwhere += "Did = " + departmentType + " and IsDel = 0";
            //if (!string.IsNullOrEmpty(name) && departmentType != "全部")
            //    sqlwhere += " and CnName like '%" + name + "%' and IsDel = 0";
            //if (!string.IsNullOrEmpty(name) && departmentType == "全部")
            //    sqlwhere += "CnName like '%" + name + "%' and IsDel = 0";
            //if (string.IsNullOrEmpty(name) && departmentType == "全部")
            //    sqlwhere += "CnName like '%" + name + "%' and IsDel = 0";

            return PageBase<TravelUsers>.excutePageSql(new TravelUsers(), "TravelUsers", "TravelUsers", "*", "id asc", sqlwhere, 15, pageIndex, out sumPage, out totalRecord);
        }

        /// <summary>
        /// 雷怡 2021-10-13 17:55
        /// 删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool DelUsers(int id)
        {
            if (SqlHelper.ExecuteNonQuery("update TravelUsers set IsDel = 1 where Id = @Id", CommandType.Text, new SqlParameter("@Id", id)) > 0)
                return true;
            return false;
        }

        /// <summary>
        /// 雷怡 2021-10-14 15：24
        /// 根据Id查询信息
        /// </summary>
        /// <param name="id">编号id </param>
        /// <returns></returns>
        public TravelUsers GetbyId( int id)
        {
            return excuteType("select * from TravelUsers where isdel = 0 and Id = @id", new SqlParameter("@id", id));
        }

        /// <summary>
        /// 雷怡 2021-10-14 15：24
        /// 根据账户密码查询信息
        /// </summary>
        /// <param name="user"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        public TravelUsers GetbyUsersNameAndPassword(string user,string password)
        {
            return excuteType("select * from TravelUsers where isdel = 0 and (LoginName = @LoginName and LoginPassWord = @LoginPassWord)"
                , new SqlParameter("@LoginName", user),new SqlParameter("@LoginPassWord", password));
        }

        /// <summary>
        /// 雷怡 2021-10-14 15：24
        /// 根据账户密码查询信息
        /// </summary>
        /// <param name="user"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        public TravelUsers GetbyUsersName(string user)
        {
            return excuteType("select * from TravelUsers where isdel = 0 and LoginName = @LoginName"
                , new SqlParameter("@LoginName", user));
        }

        /// <summary>
        /// 雷怡 2021-10-14 15：39
        /// 编辑
        /// </summary>
        /// <param name="tu"></param>
        /// <returns></returns>
        public bool Edit(TravelUsers tu)
        {
            if (SqlHelper.ExecuteNonQuery("update TravelUsers set LoginPassWord=@LoginPassWord ,SubordinateUnits=@SubordinateUnits ,Oper=@Oper,OperDate=@OperDate where Id=@Id", 
                CommandType.Text, 
                new SqlParameter("@LoginPassWord", tu.LoginPassWord),
                new SqlParameter("@SubordinateUnits", tu.SubordinateUnits), 
                new SqlParameter("@Oper", tu.Oper),
                new SqlParameter("@OperDate", tu.OperDate),
                new SqlParameter("@Id", tu.Id)) > 0)
                return true;
            return false;
        }

        /// <summary>
        /// 雷怡 2021-10-14 15:48
        /// 增Add
        /// </summary>
        /// <param name="Dov"></param>
        /// <returns></returns>
        public bool Add(TravelUsers tu)
        {
            string sql = "insert into TravelUsers values(@LoginName,@LoginPassWord,@SubordinateUnits,@CareteTime,@LoginState,@Oper,@OperDate,@Isdel);";

            SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@LoginName",tu.LoginName),
                new SqlParameter("@LoginPassWord",tu.LoginPassWord),
                new SqlParameter("@SubordinateUnits",tu.SubordinateUnits),
                new SqlParameter("@CareteTime",tu.CareteTime),
                new SqlParameter("@LoginState",tu.LoginState),
                new SqlParameter("@Oper",tu.Oper),
                new SqlParameter("@OperDate",tu.OperDate),
                new SqlParameter("@Isdel",tu.Isdel)
            };

            if (SqlHelper.ExecuteNonQuery(sql, CommandType.Text, parameter) > 0)
                return true;
            return false;
        }

        /// <summary>
        /// 雷怡 2021-10-14 16:40
        /// 用户登录状态修改
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool SetLoginState(int id,int state)
        {
            string sql = "update TravelUsers set LoginState = 1  where Id =" + id;

            if (SqlHelper.ExecuteNonQuery(sql, CommandType.Text, null) > 0)
                return true;
            return false;
        }
    }
}