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


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

        /// <summary>
        /// 查找工资表里最新的月份
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public List<Wage> GetWageByUID(int userId)
        {
            //调用获取单个对象的方法
            return excuteSql("select * from Wage where Isdel = 0 and UserId=@userId order by Id desc", new SqlParameter("@userId", userId));
        }



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

        /// <summary>
        /// 根据编号查询对象信息
        /// </summary>
        /// <returns>返回空或者单个对象信息</returns>
        public Wage GetWageByEntity(string yearMonth,int userId)
        {
            //调用获取单个对象的方法
            return excuteType("select * from Wage where YearMonth = @YearMonth and UserId = @UserId and IsDel = 0 order by id desc", new SqlParameter("@YearMonth", yearMonth), new SqlParameter("@userId", userId));
        }

        /// <summary>
        /// 获取全部有效数据
        /// </summary>
        /// <returns></returns>
        public List<Wage> GetAll(string yearMonth)
        {
            return excuteSql("select * from Wage Where IsDel = 0 and YearMonth=@YearMonth", new SqlParameter("@YearMonth", yearMonth));
        }

        /// <summary>
        /// 获取全部有效数据 - 导出工资列表
        /// </summary>
        /// <returns></returns>
        public List<Wage> GetAllByIdStr(string idStr)
        {
            return excuteSql("select * from wage where id in (" + idStr + ")");
        }

        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="sdt">对象</param>
        public bool AddWage(Wage w)
        {
            string sql = "insert into Wage values(@YearMonth,@UserId,@StartDate,@EndDate,@Basic,@Floats, @PostAllowance, @GarmentWashSubsidies, @CommunicationSubsidies, @TrafficSubsidies,@InformationSecurityFee, @OperationBonus,@SpecialAllowance,@OtherSubsidies,@WithholdingInsurance,@SickLeave, @SomethingFalse,@LateTo,@LeaveEarly,@Absenteeism,@NotPunch,@OtherDeductions,@Should,@TotalDeductions,@TotalRealHair,@Tax,@AfterTax,@IsLock,@Remark,@Operator,@OperatorDate,@IsDel,@Mealsupplement,@Gongjijin,@GroupCost)";

            SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@YearMonth",w.YearMonth),
                new SqlParameter("@UserId",w.UserId),
                new SqlParameter("@StartDate",w.StartDate),
                new SqlParameter("@EndDate",w.EndDate),
                new SqlParameter("@Basic", w.Basic),
                new SqlParameter("@Floats",w.Floats),
                new SqlParameter("@PostAllowance",w.PostAllowance),
                new SqlParameter("@GarmentWashSubsidies",w.GarmentWashSubsidies),
                new SqlParameter("@CommunicationSubsidies",w.CommunicationSubsidies),
                new SqlParameter("@TrafficSubsidies",w.TrafficSubsidies),
                new SqlParameter("@InformationSecurityFee",w.InformationSecurityFee),
                new SqlParameter("@OperationBonus",w.OperationBonus),
                new SqlParameter("@SpecialAllowance",w.SpecialAllowance),
                new SqlParameter("@OtherSubsidies",w.OtherSubsidies),
                new SqlParameter("@WithholdingInsurance",w.WithholdingInsurance),
                new SqlParameter("@SickLeave",w.SickLeave),
                new SqlParameter("@SomethingFalse",w.SomethingFalse),
                new SqlParameter("@LateTo",w.LateTo),
                new SqlParameter("@LeaveEarly",w.LeaveEarly),
                new SqlParameter("@Absenteeism",w.Absenteeism),
                new SqlParameter("@NotPunch",w.NotPunch),
                new SqlParameter("@OtherDeductions",w.OtherDeductions),
                new SqlParameter("@Should",w.Should),
                new SqlParameter("@TotalDeductions",w.TotalDeductions),
                new SqlParameter("@TotalRealHair",w.TotalRealHair),
                new SqlParameter("@Tax",w.Tax),
                new SqlParameter("@AfterTax",w.AfterTax),
                new SqlParameter("@IsLock",w.IsLock),
                new SqlParameter("@Remark",w.Remark),
                new SqlParameter("@Operator",w.Operators),
                new SqlParameter("@OperatorDate",w.OperatorsDate),
                new SqlParameter("@IsDel",w.IsDel),
                new SqlParameter("@Mealsupplement",w.Mealsupplement),
                new SqlParameter("@Gongjijin",w.Gongjijin),
                new SqlParameter("@GroupCost",w.GroupCost)
            };

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

        /// <summary>
        /// 编辑
        /// </summary>
        /// <param name="sdt"></param>
        /// <returns></returns>
        public bool EditWage(Wage w)
        {
            string sql = "update Wage set YearMonth = @YearMonth,UserId = @UserId,StartDate = @StartDate,EndDate = @EndDate,Basic = @Basic,Floats = @Floats,PostAllowance = @PostAllowance,GarmentWashSubsidies = @GarmentWashSubsidies,CommunicationSubsidies = @CommunicationSubsidies,TrafficSubsidies = @TrafficSubsidies,InformationSecurityFee = @InformationSecurityFee,OperationBonus = @OperationBonus,SpecialAllowance = @SpecialAllowance,OtherSubsidies = @OtherSubsidies,WithholdingInsurance = @WithholdingInsurance,SickLeave = @SickLeave,SomethingFalse = @SomethingFalse,LateTo = @LateTo,LeaveEarly = @LeaveEarly,Absenteeism = @Absenteeism,NotPunch = @NotPunch,OtherDeductions = @OtherDeductions,Should = @Should,TotalDeductions = @TotalDeductions,TotalRealHair = @TotalRealHair,Tax=@Tax,AfterTax=@AfterTax,IsLock = @IsLock,Remark = @Remark,Operator = @Operator,OperatorDate = @OperatorDate,Mealsupplement=@Mealsupplement,Gongjijin=@Gongjijin,GroupCost=@GroupCost where Id = @Id";

            SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@YearMonth",w.YearMonth),
                new SqlParameter("@UserId",w.UserId),
                new SqlParameter("@StartDate",w.StartDate),
                new SqlParameter("@EndDate",w.EndDate),
                new SqlParameter("@Basic", w.Basic),
                new SqlParameter("@Floats",w.Floats),
                new SqlParameter("@PostAllowance",w.PostAllowance),
                new SqlParameter("@GarmentWashSubsidies",w.GarmentWashSubsidies),
                new SqlParameter("@CommunicationSubsidies",w.CommunicationSubsidies),
                new SqlParameter("@TrafficSubsidies",w.TrafficSubsidies),
                new SqlParameter("@InformationSecurityFee",w.InformationSecurityFee),
                new SqlParameter("@OperationBonus",w.OperationBonus),
                new SqlParameter("@SpecialAllowance",w.SpecialAllowance),
                new SqlParameter("@OtherSubsidies",w.OtherSubsidies),
                new SqlParameter("@WithholdingInsurance",w.WithholdingInsurance),
                new SqlParameter("@SickLeave",w.SickLeave),
                new SqlParameter("@SomethingFalse",w.SomethingFalse),
                new SqlParameter("@LateTo",w.LateTo),
                new SqlParameter("@LeaveEarly",w.LeaveEarly),
                new SqlParameter("@Absenteeism",w.Absenteeism),
                new SqlParameter("@NotPunch",w.NotPunch),
                new SqlParameter("@OtherDeductions",w.OtherDeductions),
                new SqlParameter("@Should",w.Should),
                new SqlParameter("@TotalDeductions",w.TotalDeductions),
                new SqlParameter("@TotalRealHair",w.TotalRealHair),
                new SqlParameter("@Tax",w.Tax),
                new SqlParameter("@AfterTax",w.AfterTax),
                new SqlParameter("@IsLock",w.IsLock),
                new SqlParameter("@Remark",w.Remark),
                new SqlParameter("@Operator",w.Operators),
                new SqlParameter("@OperatorDate",w.OperatorsDate),
                new SqlParameter("@Id",w.Id),
                new SqlParameter("@Mealsupplement",w.Mealsupplement),
                new SqlParameter("@Gongjijin",w.Gongjijin),
                new SqlParameter("@GroupCost",w.GroupCost)
            };

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



        /// <summary>
        /// 批量更新
        /// </summary>
        /// <param name="sdt"></param>
        /// <returns></returns>
        public bool EditWage(List<Wage> ws)
        {
            SqlCommand cmd = SqlHelper.createCon().CreateCommand();

            cmd.Connection.Open();
            SqlTransaction trans = cmd.Connection.BeginTransaction();
            cmd.Transaction = trans;
            string sql = "update Wage set YearMonth = @YearMonth,UserId = @UserId,StartDate = @StartDate,EndDate = @EndDate,Basic = @Basic,Floats = @Floats,PostAllowance = @PostAllowance,GarmentWashSubsidies = @GarmentWashSubsidies,CommunicationSubsidies = @CommunicationSubsidies,TrafficSubsidies = @TrafficSubsidies,InformationSecurityFee = @InformationSecurityFee,OperationBonus = @OperationBonus,SpecialAllowance = @SpecialAllowance,OtherSubsidies = @OtherSubsidies,WithholdingInsurance = @WithholdingInsurance,SickLeave = @SickLeave,SomethingFalse = @SomethingFalse,LateTo = @LateTo,LeaveEarly = @LeaveEarly,Absenteeism = @Absenteeism,NotPunch = @NotPunch,OtherDeductions = @OtherDeductions,Should = @Should,TotalDeductions = @TotalDeductions,TotalRealHair = @TotalRealHair,Tax=@Tax,AfterTax=@AfterTax,IsLock = @IsLock,Remark = @Remark,Operator = @Operator,OperatorDate = @OperatorDate,Mealsupplement=@Mealsupplement,Gongjijin=@Gongjijin,GroupCost=@GroupCost where Id = @Id";
            cmd.CommandText = sql;
            SqlParameter[] parameter = new SqlParameter[]{
                        new SqlParameter("@YearMonth",SqlDbType.VarChar),
                        new SqlParameter("@UserId",SqlDbType.Int),
                        new SqlParameter("@StartDate",SqlDbType.VarChar),
                        new SqlParameter("@EndDate",SqlDbType.VarChar),
                        new SqlParameter("@Basic", SqlDbType.Float),
                        new SqlParameter("@Floats",SqlDbType.Float),
                        new SqlParameter("@PostAllowance",SqlDbType.Float),
                        new SqlParameter("@GarmentWashSubsidies",SqlDbType.Float),
                        new SqlParameter("@CommunicationSubsidies",SqlDbType.Float),
                        new SqlParameter("@TrafficSubsidies",SqlDbType.Float),
                        new SqlParameter("@InformationSecurityFee",SqlDbType.Float),
                        new SqlParameter("@OperationBonus",SqlDbType.Float),
                        new SqlParameter("@SpecialAllowance",SqlDbType.Float),
                        new SqlParameter("@OtherSubsidies",SqlDbType.Float),
                        new SqlParameter("@WithholdingInsurance",SqlDbType.Float),
                        new SqlParameter("@SickLeave",SqlDbType.Float),
                        new SqlParameter("@SomethingFalse",SqlDbType.Float),
                        new SqlParameter("@LateTo",SqlDbType.Float),
                        new SqlParameter("@LeaveEarly",SqlDbType.Float),
                        new SqlParameter("@Absenteeism",SqlDbType.Float),
                        new SqlParameter("@NotPunch",SqlDbType.Float),
                        new SqlParameter("@OtherDeductions",SqlDbType.Float),
                        new SqlParameter("@Should",SqlDbType.Float),
                        new SqlParameter("@TotalDeductions",SqlDbType.Float),
                        new SqlParameter("@TotalRealHair",SqlDbType.Float),
                        new SqlParameter("@Tax",SqlDbType.Float),
                        new SqlParameter("@AfterTax",SqlDbType.Float),
                        new SqlParameter("@IsLock",SqlDbType.Int),
                        new SqlParameter("@Remark",SqlDbType.VarChar),
                        new SqlParameter("@Operator",SqlDbType.VarChar),
                        new SqlParameter("@OperatorDate",SqlDbType.VarChar),
                        new SqlParameter("@Id",SqlDbType.Int),
                        new SqlParameter("@Mealsupplement",SqlDbType.Float),
                        new SqlParameter("@Gongjijin",SqlDbType.Float),
                        new SqlParameter("@GroupCost",SqlDbType.Float)
                    };
            try
            {
                foreach (var w in ws)
                {

                    parameter[0].Value = w.YearMonth;
                    parameter[1].Value = w.UserId;
                    parameter[2].Value = w.StartDate;
                    parameter[3].Value = w.EndDate;
                    parameter[4].Value = w.Basic;
                    parameter[5].Value = w.Floats;
                    parameter[6].Value = w.PostAllowance;
                    parameter[7].Value = w.GarmentWashSubsidies;
                    parameter[8].Value = w.CommunicationSubsidies;
                    parameter[9].Value = w.TrafficSubsidies;
                    parameter[10].Value = w.InformationSecurityFee;
                    parameter[11].Value = w.OperationBonus;
                    parameter[12].Value = w.SpecialAllowance;
                    parameter[13].Value = w.OtherSubsidies;
                    parameter[14].Value = w.WithholdingInsurance;
                    parameter[15].Value = w.SickLeave;
                    parameter[16].Value = w.SomethingFalse;
                    parameter[17].Value = w.LateTo;
                    parameter[18].Value = w.LeaveEarly;
                    parameter[19].Value = w.Absenteeism;
                    parameter[20].Value = w.NotPunch;
                    parameter[21].Value = w.OtherDeductions;
                    parameter[22].Value = w.Should;
                    parameter[23].Value = w.TotalDeductions;
                    parameter[24].Value = w.TotalRealHair;
                    parameter[25].Value = w.Tax;
                    parameter[26].Value = w.AfterTax;
                    parameter[27].Value = w.IsLock;
                    parameter[28].Value = w.Remark;
                    parameter[29].Value = w.Operators;
                    parameter[30].Value = w.OperatorsDate;
                    parameter[31].Value = w.Id;
                    parameter[32].Value = w.Mealsupplement;
                    parameter[33].Value = w.Gongjijin;
                    parameter[34].Value = w.GroupCost;
                    if (parameter != null)
                    {
                        foreach (SqlParameter item in parameter)
                        {
                            cmd.Parameters.Add(item);
                        }
                    }
                    //SqlHelper.ExecuteNonQuery(sql, CommandType.Text, parameter);
                    cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                }
                trans.Commit();
                cmd.Connection.Close();
                cmd.Connection.Dispose();
                return true;
            }
            catch
            {
                trans.Rollback();
                cmd.Connection.Close();
                cmd.Connection.Dispose();
                return false;
            }
        }

        /// <summary>
        /// 更改锁定状态
        /// </summary>
        /// <param name="isLock"></param>
        /// <returns></returns>
        public bool UpdateWageIsLock(int isLock, string idStr)
        {
            if (SqlHelper.ExecuteNonQuery("update Wage set IsLock = @IsLock where Id in (" + idStr + ")", CommandType.Text, new SqlParameter("@IsLock", isLock)) > 0)
                return true;
            return false;
        }

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