OA2021/DAL/Fin_CommissionService.cs

using Models;
using OASystem.Domain.Entities.Groups;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    /// <summary>
    /// 新提成表DAL
    /// </summary>
    public class Fin_CommissionService
    {
        SqlHelperOA2023 DB=new SqlHelperOA2023();
        /// <summary>
        /// 查找所有数据
        /// </summary>
        /// <param name="Personnel"></param>
        /// <param name="startDate"></param>
        /// <param name="EndDate"></param>
        /// <returns></returns>
        public DataTable GetAll(int Personnel, string startDate, string EndDate)
        {

            string sqlwhere = "select * from Fin_Commission where IsDel = 0 and Personnel = " + Personnel + " and(GroupDate Between '" + startDate + "' and '" + EndDate + "')";

            return SqlHelperOA2023.QueryAll(sqlwhere);
        }

        /// <summary>
        /// 增
        /// </summary>
        /// <param name="com"></param>
        /// <returns></returns>
        public bool add(Fin_Commission com)
        {
            string sql = "insert into Fin_Commission values(@Personnel,@Diid,@GroupDate,@GroupLvl,@Detail,@Money,@WageYearMonth,@IsMakeLoss,@IsLoss,@CreateUserId," +
                "@CreateTime,@DeleteUserId,@DeleteTime,@Remark,@IsDel);SELECT @@IDENTITY";

            SqlParameter[] parameter = new SqlParameter[]{
                    new SqlParameter("Personnel",com.Personnel),
                    new SqlParameter("Diid",com.Diid),
                    new SqlParameter("GroupDate",com.GroupDate),
                    new SqlParameter("GroupLvl",com.GroupLvl),
                    new SqlParameter("Detail",com.Detail),
                    new SqlParameter("Money",com.Money),
                    new SqlParameter("WageYearMonth",com.WageYearMonth),
                    new SqlParameter("IsMakeLoss",com.IsMakeLoss),
                    new SqlParameter("IsLoss",com.IsLoss),
                    new SqlParameter("CreateUserId",com.CreateUserId),
                    new SqlParameter("CreateTime",com.CreateTime),
                    new SqlParameter("DeleteUserId",com.DeleteUserId),
                    new SqlParameter("DeleteTime",com.DeleteTime),
                    new SqlParameter("Remark",com.Remark),
                    new SqlParameter("IsDel",com.IsDel)
            };

            if (SqlHelperOA2023.ExecuteNonQuery(sql, CommandType.Text, parameter) > 0)
                return true;
            return false;
        }
        public bool del(int id)
        {
            if (SqlHelperOA2023.ExecuteNonQuery("update Fin_Commission set Isdel=1 where Id=@Id", CommandType.Text, new SqlParameter("@Id", id)) > 0)
                return true;
            return false;
        }
        /// <summary>
        /// 查找所有数据
        /// </summary>
        /// <param name="Personnel"></param>
        /// <param name="startDate"></param>
        /// <param name="EndDate"></param>
        /// <returns></returns>
        public DataTable GetByYear(int Personnel, string startDate, string EndDate,string YearMonth)
        {
            string sqlWhere = "";
            if (YearMonth!="全部")
            {
                sqlWhere += "and WageYearMonth = '" + YearMonth + "' ";
            }

            if (!string.IsNullOrWhiteSpace(startDate) && !string.IsNullOrWhiteSpace(EndDate))
            {
                sqlWhere+= "and(GroupDate Between '" + startDate + "' and '" + EndDate + "')";
            }
            string sqlwhere = "select * from Fin_Commission where IsDel = 0 "+ sqlWhere + " and Personnel = " + Personnel;

            return SqlHelperOA2023.QueryAll(sqlwhere);
        }
    }
}