OA2021/DAL/DailyManagementService.cs

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

namespace DAL
{
    /// <summary>
    /// 日报管理数据访问类
    /// </summary>
    public class DailyManagementService
    {
        /// <summary>
        /// 查询所有
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">可变参数数组</param>
        /// <returns>返回集合</returns>
        List<DailyManagement> excuteSql(string sql, params SqlParameter[] param)
        {
            return ServiceBase<DailyManagement>.excuteSql(new DailyManagement(), "DailyManagement", sql, CommandType.Text, param);
        }


        /// <summary>
        /// 获取单个对象
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">可变参数数组</param>
        /// <returns>返回空或者单个对象</returns>
        DailyManagement excuteType(string sql, params SqlParameter[] param)
        {
            //查询结果放入对象集合
            List<DailyManagement> dmList = excuteSql(sql, param);
            //判断集合是否为空
            if (dmList == null || dmList.Count == 0)
                //返回null
                return null;
            //返回单个对象
            return dmList[0];
        }


        /// <summary>
        /// 根据编号查询对象信息
        /// </summary>
        /// <param name="id">对象编号</param>
        /// <returns>返回空或者单个对象信息</returns>
        public DailyManagement GetDailyManagementByID(int id)
        {
            //调用获取单个对象的方法
            return excuteType("select * from DailyManagement where Id = @id", new SqlParameter("@id", id));
        }

        /// <summary>
        /// 根据条件查询条件获取 - 分页
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="sumPage"></param>
        /// <param name="totalRecord"></param>
        /// <param name="dataType"></param>
        /// <param name="name"></param>
        /// <returns></returns>
        public List<DailyManagement> GetAll(int pageIndex, out int sumPage, out int totalRecord, string dailyDate,int userid)
        {
            string sqlwhere = " IsDel = 0 and userid = " + userid + "";

            if (!string.IsNullOrEmpty(dailyDate))
                sqlwhere += " and DailyDate = '" + dailyDate + "'";

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

        /// <summary>
        /// 根据条件查询条件获取审核数据 - 分页
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="sumPage"></param>
        /// <param name="totalRecord"></param>
        /// <param name="dataType"></param>
        /// <param name="name"></param>
        /// <returns></returns>
        public List<DailyManagement> GetAll(int pageIndex, out int sumPage, out int totalRecord, string dailyDate, string userid,string audit)
        {
            string sqlwhere = " IsDel = 0 and userid in (" + userid.Remove(userid.Length - 1) + ") and isAudit=" + audit + "";

            if (!string.IsNullOrEmpty(dailyDate))
                sqlwhere += " and DailyDate = '" + dailyDate + "'";

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

        /// <summary>
        /// 根据条件查询条件获取审核数据 - 分页 - 总经理
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="sumPage"></param>
        /// <param name="totalRecord"></param>
        /// <param name="dataType"></param>
        /// <param name="name"></param>
        /// <returns></returns>
        public List<DailyManagement> GetAll(int pageIndex, out int sumPage, out int totalRecord, string dailyDate, string userid, string audit, string usersidddl)
        {
            string sqlwhere = " IsDel = 0 and userid in (" + userid.Remove(userid.Length - 1) + ") and isAudit=" + audit + " and userid = " + usersidddl + "";

            if (!string.IsNullOrEmpty(dailyDate))
                sqlwhere += " and DailyDate = '" + dailyDate + "'";

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

        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="sdt">对象</param>
        public bool AddDailyManagement(DailyManagement dm)
        {
            string sql = "insert into DailyManagement values(@DailyDate,@WorkTitle,@WorkHours,@CompletionRate,@WorkContent,@UserId,@Dates,@IsAudit,@AuditUserId,@AuditDate,@IsDel)";

            SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@DailyDate",dm.DailyDate),
                new SqlParameter("@WorkTitle",dm.WorkTitle),
                new SqlParameter("@WorkHours",dm.WorkHours),
                new SqlParameter("@CompletionRate",dm.CompletionRate),
                new SqlParameter("@WorkContent",dm.WorkContent),
                new SqlParameter("@UserId",dm.UserId),
                new SqlParameter("@Dates",dm.Dates),
                new SqlParameter("@IsAudit",dm.IsAudit),
                new SqlParameter("@AuditUserId",dm.AuditUserId),
                new SqlParameter("@AuditDate",dm.AuditDate),
                new SqlParameter("@IsDel",dm.IsDel)
            };

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

        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="sdt">对象</param>
        public bool EditDailyManagement(DailyManagement dm)
        {
            string sql = "update DailyManagement set DailyDate = @DailyDate,WorkTitle = @WorkTitle,WorkHours = @WorkHours,CompletionRate = @CompletionRate,WorkContent = @WorkContent,UserId = @UserId,Dates = @Dates,IsAudit = @IsAudit,AuditUserId = @AuditUserId,AuditDate = @AuditDate where id = @Id";

            SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@DailyDate",dm.DailyDate),
                new SqlParameter("@WorkTitle",dm.WorkTitle),
                new SqlParameter("@WorkHours",dm.WorkHours),
                new SqlParameter("@CompletionRate",dm.CompletionRate),
                new SqlParameter("@WorkContent",dm.WorkContent),
                new SqlParameter("@UserId",dm.UserId),
                new SqlParameter("@Dates",dm.Dates),
                new SqlParameter("@IsAudit",dm.IsAudit),
                new SqlParameter("@AuditUserId",dm.AuditUserId),
                new SqlParameter("@AuditDate",dm.AuditDate),
                new SqlParameter("@Id",dm.Id)
            };

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

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="calendarDate"></param>
        /// <returns></returns>
        public bool DelDailyManagement(int id)
        {
            if (SqlHelper.ExecuteNonQuery("update DailyManagement set isDel = 1 where id = @Id", CommandType.Text, new SqlParameter("@Id", id)) > 0)
                return true;
            return false;
        }

        /// <summary>
        /// 审核通过
        /// </summary>
        /// <param name="calendarDate"></param>
        /// <returns></returns>
        public bool updateAuditDailyManagement(int id)
        {
            if (SqlHelper.ExecuteNonQuery("update DailyManagement set isAudit = 1 where id = @Id", CommandType.Text, new SqlParameter("@Id", id)) > 0)
                return true;
            return false;
        }

        public DailyManagement GetDMByDate(string DailyDate,int userid)
        {
            //调用获取单个对象的方法
            return excuteType("select * from DailyManagement where Isdel=0 and DailyDate=@DailyDate and UserId=@userid", new SqlParameter("@DailyDate", DailyDate), new SqlParameter("@userid", userid));
        }



    }
}