OA2021/DAL/OvertimeApplicationService.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Models;
using System.Data;
using System.Runtime.InteropServices.ComTypes;
using System.Security.Cryptography;

namespace DAL
{
    /// <summary>
    /// 加班申请数据访问层
    /// </summary>
    public class OvertimeApplicationService
    {

        /// <summary>
        /// 新增考勤补卡
        /// </summary>
        /// <param name="ota"></param>
        /// <returns></returns>
        public bool AddOvertimeApplication(OvertimeApplication ota)
        {
            string sql = "INSERT INTO OvertimeApplication(UID,OverDate,StardTime,EndTime,ObjectiveTime,SubjectiveTime,Reason,MAudio,IsAudio,AudioMan,AudioTime,Remark,IsDel,Did,OvertimeType)"
                + " VALUES(@UID,@OverDate,@StardTime,@EndTime,@ObjectiveTime,@SubjectiveTime,@Reason,@MAudio,@IsAudio,@AudioMan,@AudioTime,@Remark,@IsDel,@Did,@OvertimeType)";
            SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@UID",ota.UID),
                new SqlParameter("@OverDate",ota.OverDate),
                new SqlParameter("@StardTime",ota.StardTime),
                new SqlParameter("@EndTime",ota.EndTime),
                new SqlParameter("@ObjectiveTime",ota.ObjectiveTime),
                new SqlParameter("@SubjectiveTime",ota.SubjectiveTime),
                new SqlParameter("@Reason",ota.Reason),
                new SqlParameter("@MAudio",ota.MAudio),
                new SqlParameter("@IsAudio",ota.IsAudio),
                new SqlParameter("@AudioMan",ota.AudioMan),
                new SqlParameter("@AudioTime",ota.AudioTime),
                new SqlParameter("@Remark",ota.Remark),
                new SqlParameter("@IsDel",ota.IsDel),
                new SqlParameter("@Did",ota.Did),
                new SqlParameter("@OvertimeType",ota.OvertimeType)
            };
            if (SqlHelper.ExecuteNonQuery(sql, CommandType.Text, parameter) <= 0)
                return false;
            return true;
        }

        /// <summary>
        /// 根据用户ID，人事审核状态查询
        /// 获取全部有效新增考勤补卡数据
        /// </summary>
        /// <returns></returns>
        public List<OvertimeApplication> GetAll(int UID, DateTime StartDate, DateTime EndDate, int IsAudio, int MAudit, int Did, int OvertimeType)
        {
            string sql = "select * from OvertimeApplication  Where Uid = '" + UID + "' and (OverDate >= '" + StartDate + "' and OverDate <='" + EndDate + "') " + "and IsAudio=" + IsAudio + " and MAudio =" + MAudit + " and IsDel = 0";
            if (OvertimeType != 0)
                sql = sql + " and OvertimeType = " + OvertimeType;

            return excuteSql(sql);
        }


        /// <summary>
        /// 根据用户ID，起止时间，人事审核状态，经理审核状态，团组did查询
        /// 根据条件查询条件获取 - 分页
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="sumPage"></param>
        /// <param name="totalRecord"></param>
        /// <param name="dataType"></param> 
        /// <returns></returns>
        public List<OvertimeApplication> GetAll(int pageIndex, out int sumPage, out int totalRecord, int UID, DateTime StartDate, DateTime EndDate, int IsAudio, int MAudit, int Did, int userID, int OvertimeType)
        {

            string sqlcmd = "select * from OvertimeApplication where MAudio=" + MAudit + " and IsAudio=" + IsAudio + " and (OverDate between '" + StartDate + "' and '" + EndDate + "')" + " and IsDel=0";
            if (UID > 0)
            {
                sqlcmd = sqlcmd + " and UID = " + UID;
            }
            if (Did > 0 && Did != 99 && Did != 3)
            {
                sqlcmd = sqlcmd + " and Did=" + Did;
            }

            string sqlwhere = "";
            //"1=1 and OverDate >= '"+StartDate+"' and OverDate <='"+EndDate+"' and IsAudio="+IsAudio;
            //if (UID > 0 && UID!=21 && UID!=41)
            //    sqlwhere = sqlwhere+" and UID = "+UID;

            //if (Did > 0 && Did!=99 && Did!=3)
            //    sqlwhere = sqlwhere + " and Did=" + Did;
            //if (userID == 21)
            //    sqlwhere = sqlwhere + " and UID in (10,16,18,22,37,5,11,41)";
            //sqlwhere =sqlwhere+ " and IsDel = 0";

            //if (Status == "Maudit")
            //{
            sqlwhere = sqlwhere + "1=1 and OverDate >= '" + StartDate + "' and OverDate <='" + EndDate + "' and MAudio=" + MAudit + "and IsAudio=" + IsAudio;
            //}
            //else
            //{
            //sqlwhere = sqlwhere + "1=1 and OverDate >= '" + StartDate + "' and OverDate <='" + EndDate  + "'and IsAudio=" + IsAudio;
            //}

            if (UID > 0)
            {
                sqlwhere = sqlwhere + " and UID = " + UID;
            }
            if (Did > 0 && Did != 99 && Did != 3)
            {
                sqlwhere = sqlwhere + " and Did=" + Did;
            }

            if (OvertimeType != 0)
            {
                sqlwhere = sqlwhere + " and OvertimeType=" + OvertimeType;
            }

            sqlwhere = sqlwhere + " and IsDel = 0";

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


        /// <summary>
        /// 根据用户ID，起止时间，人事审核状态，未知status，未知did查询
        /// 根据条件查询条件获取 - 分页
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="sumPage"></param>
        /// <param name="totalRecord"></param>
        /// <param name="dataType"></param> 
        /// <returns></returns>
        public List<OvertimeApplication> GetAll(int pageIndex, out int sumPage, out int totalRecord, int UID, DateTime StartDate, DateTime EndDate, int IsAudio, int Did, int userID)
        {
            string sqlwhere = "1=1 and OverDate >= '" + StartDate + "' and OverDate <='" + EndDate + "' and IsAudio=" + IsAudio;
            //if (UID > 0 && UID!=21 && UID!=41)
            //    sqlwhere = sqlwhere+" and UID = "+UID;

            //if (Did > 0 && Did!=99 && Did!=3)
            //    sqlwhere = sqlwhere + " and Did=" + Did;
            //if (userID == 21)
            //    sqlwhere = sqlwhere + " and UID in (10,16,18,22,37,5,11,41)";
            //sqlwhere =sqlwhere+ " and IsDel = 0";            

            if (UID > 0)
            {
                sqlwhere = sqlwhere + " and UID = " + UID;
            }
            if (Did > 0 && Did != 99 && Did != 3)
                sqlwhere = sqlwhere + " and Did=" + Did;

            sqlwhere = sqlwhere + " and IsDel = 0";

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


        /// <summary>
        /// 根据用户ID，起止时间查询
        /// 根据条件查询条件获取 - 所有
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="sumPage"></param>
        /// <param name="totalRecord"></param>
        /// <param name="dataType"></param>
        /// <returns></returns>
        public double GetAll(int did, int uid, string startDate, string endDate)
        {
            SqlParameter[] parameter = new SqlParameter[]
            {
                new SqlParameter("@did",did),
                new SqlParameter("@uid",uid),
                new SqlParameter("@startTime",startDate),
                new SqlParameter("@endTime",endDate)
            };
            DataTable dt = SqlHelper.TransferProcedure("Leavel_Hourse_Count", CommandType.StoredProcedure, parameter);
            if (dt != null && dt.Rows.Count > 0)
            {
                try
                {
                    return Convert.ToDouble(dt.Rows[0]["Hourse"]);
                }
                catch
                {
                    return 0.00;
                }
            }
            else
                return 0.00;
        }

        //public double GetJBDX(int uid, string startDate, string endDate) {
        //    string sql="select sum(ucac.UpCardHours) as JBDXT from UpCardAudit ucajoin UpCardAuditContent ucac on uca.id = ucac.UCAid where uca.isDel = 0 and ucac.isdel=0 and ucac.Did = 281 and isAudit = 1 and uid ="+ uid +"and (uca.YearMonth between '"+ startDate +" and '"+ endDate +"')";
        //    SqlHelper.ExecuteNonQuery(sql, CommandType.Text, null);

        //    return 0.00;
        //}



        /// <summary>
        ///加班抵休或者年假抵休的时间
        /// </summary>
        /// <param name="did">抵休原因</param>
        /// <param name="uid">用户ID</param>
        /// <param name="startDate">起始日</param>
        /// <param name="endDate">终止日</param>
        /// <returns></returns>
        public double GetDX(int did, int uid, string startDate, string endDate)
        {
            SqlParameter[] parameter = new SqlParameter[]
            {
                new SqlParameter("@did",did),
                new SqlParameter("@uid",uid),
                new SqlParameter("@startTime",startDate),
                new SqlParameter("@endTime",endDate)
            };
            DataTable dt = SqlHelper.TransferProcedure("JBDX_Count", CommandType.StoredProcedure, parameter);
            if (dt != null && dt.Rows.Count > 0)
            {
                try
                {
                    return Convert.ToDouble(dt.Rows[0]["Xtime"]);
                }
                catch
                {
                    return 0.00;
                }
            }
            else
                return 0.00;
        }

        /// <summary>
        /// 逻辑删除
        /// </summary>
        /// <param name="p"></param>
        public bool DelOvertimeApplication(int id)
        {
            string sql = "delete from OvertimeApplication where id=" + id;
            if (SqlHelper.ExecuteNonQuery(sql, CommandType.Text, null) > 0)
                return true;
            return false;
        }
        /// <summary>
        /// 根据数据ID查询
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public OvertimeApplication GetAllByID(int id)
        {
            string sql = "select * from OvertimeApplication where id=" + id;
            return excuteType(sql, null);
        }


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

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

        /// <summary>
        /// 修改加班申请
        /// </summary>
        /// <param name="oat"></param>
        /// <returns></returns>
        public bool EditOvertimeApplication(OvertimeApplication oat, int id)
        {
            string sql = "update OvertimeApplication set OverDate=@OverDate,StardTime=@StardTime,EndTime=@EndTime,ObjectiveTime=@ObjectiveTime,SubjectiveTime=@SubjectiveTime,Reason=@Reason,Remark=@Remark,Did=@Did,OvertimeType=@OvertimeType where ID=" + id;
            SqlParameter[] parameter = new SqlParameter[]
            {
                new SqlParameter("@OverDate",oat.OverDate),
                new SqlParameter("@StardTime",oat.StardTime),
                new SqlParameter("@ObjectiveTime",oat.ObjectiveTime),
                new SqlParameter("@SubjectiveTime",oat.SubjectiveTime),
                new SqlParameter("@EndTime",oat.EndTime),
                new SqlParameter("@Reason",oat.Reason),
                new SqlParameter("@Remark",oat.Remark),
                new SqlParameter("@Did",oat.Did),
                new SqlParameter("@OvertimeType",oat.OvertimeType)
            };
            if (SqlHelper.ExecuteNonQuery(sql, CommandType.Text, parameter) > 0)
                return true;
            return false;
        }

        /// <summary>
        /// 人事审核
        /// </summary>
        /// <param name="state"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool AuditOvertimeApplication(int state, int id, string name, DateTime Now)
        {
            string sql = "update OvertimeApplication set IsAudio=" + state + " ,AudioMan='" + name + "',AudioTime='" + Now + "' where ID=" + id;
            if (SqlHelper.ExecuteNonQuery(sql, CommandType.Text, null) > 0)
                return true;
            return false;
        }

        /// <summary>
        /// 部门经理审核
        /// </summary>
        /// <param name="state"></param>
        /// <param name="id"></param>
        /// <param name="name"></param>
        /// <param name="Now"></param>
        /// <returns></returns>
        public bool Maudit(int state, int id, string name, DateTime Now)
        {
            string sql = "update OvertimeApplication set MAudio=" + state + " ,AudioMan='" + name + "',AudioTime='" + Now + "' where ID=" + id;
            if (SqlHelper.ExecuteNonQuery(sql, CommandType.Text, null) > 0)
                return true;
            return false;
        }


        public List<OvertimeApplication> GetAfterSixMonth()
        {
            return excuteSql("select * from  OvertimeApplication WHERE iSDEL = 0  and OverDate>= '2021-06-01'  order by OverDate");
            //return excuteSql("select * from OvertimeApplication  Where  (OverDate >= '" + StartDate + "' and OverDate <='" + EndDate + "') " + "and IsAudio=" + IsAudio + " and MAudio =" + MAudit + " and IsDel = 0");
        }

        public List<OvertimeApplication> GetUnreviewedOvertime(string uids)
        {
            string sql = " select * from OvertimeApplication where  isdel = 0 and isAudio = 0 and uid in("+ uids +")";
            return excuteSql(sql);
        }

    }
}