OA2021/DAL/AttendanceAuditService.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 AttendanceAuditService
    {
        /// <summary>
        /// 查询所有
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">可变参数数组</param>
        /// <returns>返回集合</returns>
        List<AttendanceAudit> excuteSql(string sql, params SqlParameter[] param)
        {
            return ServiceBase<AttendanceAudit>.excuteSql(new AttendanceAudit(), "AttendanceAudit", sql, CommandType.Text, param);
        }

        /// <summary>
        /// 根据条件获取数据集合 - 分页
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="sumPage"></param>
        /// <param name="totalRecord"></param>
        /// <param name="yearMonth"></param>
        /// <param name="isAudit"></param>
        /// <param name="uid"></param>
        /// <returns></returns>
        public List<AttendanceAudit> GetAll(int pageIndex, out int sumPage, out int totalRecord, string yearMonth, int isAudit, string uid,int did,int userID)
        {
            string sql = "select * from (select row_number() over(order by T.ApplicationPeriod) as rownum,T.*,u.did from v_attendanceAudit T join users u on T.UserId=u.id where YearMonth='" + yearMonth + "' and T.IsDel = 0 ";
            string sql1="select count(1) from v_attendanceAudit T join users u on T.UserId=u.id where YearMonth='"+yearMonth+"' and T.IsDel = 0";
            if (uid != "全部" && userID > 0 && userID != 21)
            {
                sql += " and uid =" + uid;
                sql1 += " and uid =" + uid;
            }
            if (userID == 21)
            {
                sql = sql + " and uid in (10,16,18,22,37,5,11,117)";
                sql1 = sql1 + " and uid in (10,16,18,22,37,5,11,117)";
            }
            if (isAudit == 0)
            {
                sql += " and (IsAudit = " + isAudit + " or IsAudit = 2)";
                sql1 += " and (IsAudit = " + isAudit + " or IsAudit = 2)";
            }
            else
            {
                sql += " and IsAudit = 1";
                sql1 += " and IsAudit = 1";
            }
            if (did != 99 && did != 3 && did!=2)
            {
                sql += "and U.Did=" + did;
                sql1 += "and U.Did=" + did;
            }
            sql += ") T where rownum between " + ((pageIndex - 1) * 10 + 1) + " and " + pageIndex * 10;
            List<AttendanceAudit> adas = new List<AttendanceAudit>();
            adas = excuteSql(sql, null);
            //查询总条数
            if (SqlHelper.ExecuteScalar(sql1, CommandType.Text, null) != null)
            {
                totalRecord = Convert.ToInt32(SqlHelper.ExecuteScalar(sql1, CommandType.Text, null));
                //计算出总页数
                sumPage = totalRecord % 10 == 0 ? totalRecord / 10 : totalRecord / 10 + 1;
            }
            else
            {
                totalRecord = 0;
                //计算出总页数
                sumPage = 1;
            }
            return adas;
            //return PageBase<AttendanceAudit>.excutePageSql(new AttendanceAudit(), "AttendanceAudit", "v_attendanceAudit", "Id,userId,Number,CnName,ApplicationPeriod,Subject,Attachment", "Id asc", sqlWhere, 10, pageIndex, out sumPage, out totalRecord);
        }
    }
}