OA2021/DAL/CommissionService.cs

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

namespace DAL
{
    /// <summary>
    /// 提成数据访问层
    /// </summary>
    public class CommissionService
    {
        /// <summary>
        /// 返回多个obj
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        List<Commission> excuteSql(string sql, params SqlParameter[] param)
        {
            return ServiceBase<Commission>.excuteSql(new Commission(), "Commission", sql, CommandType.Text, param);
        }

        /// <summary>
        /// 返回单个obj
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        Commission excuteType(string sql, params SqlParameter[] param)
        {
            //查询结果放入对象集合
            List<Commission> hdList = excuteSql(sql, param);
            Commission com = new Commission();

            //判断集合是否为空
            if (hdList == null || hdList.Count == 0)
            {
                //返回null

                return null;
            }
            //返回单个对象
            return hdList[0];
        }

        /// <summary>
        /// 增
        /// </summary>
        /// <param name="com"></param>
        /// <returns></returns>
        public bool ADD(Commission com)
        {
            string sql = "insert into Commission values(@Personnel,@Diid,@GroupDate,@GroupLvl,@NetProfit,@Balance,@Detail,@Money," +
                          "@WageYearMonth,@IsMakeLoss,@IsLoss,@OPer,@OPDate,@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("@NetProfit",com.NetProfit),
                     new SqlParameter("@Balance",com.Balance),                     
                     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("@OPer",com.OPer),
                     new SqlParameter("@OPDate",com.OPDate),
                     new SqlParameter("@IsDel",com.IsDel)
            };

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

        //改
        public bool Edit(Commission com)
        {
            string sql = "update Commission set Personnel=@Personnel,Diid=@Diid,GroupDate=@GroupDate,GroupLvl=@GroupLvl,NetProfit=@NetProfit,Balance=@Balance" +
                            "Detail=@Detail,Money=@Money,WageYearMonth=@WageYearMonth,IsMakeLoss=@IsMakeLoss,IsLoss=@IsLoss,OPer=@OPer," +
                            "OPDate=@OPDate,IsDel=@IsDel where Id = @Id";

            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("@NetProfit",com.NetProfit),
                     new SqlParameter("@Balance",com.Balance),
                     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("@OPer",com.OPer),
                     new SqlParameter("@OPDate",com.OPDate),
                     new SqlParameter("@IsDel",com.IsDel),
                     new SqlParameter("@Id",com.Id)
            };

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

        //删
        public bool del(int id)
        {
            if (SqlHelper.ExecuteNonQuery("update Commission set Isdel=1 where Id=@Id", CommandType.Text, new SqlParameter("@Id", id)) > 0)
                return true;
            return false;
        }


        /// <summary>
        /// 查找所有数据 - 分页
        /// </summary>
        /// <returns></returns>
        public List<Commission> GetAllAndPage(int pageIndex, out int sumPage, out int totalRecord, int Personnel, string startDate, string EndDate, string YearMonth)
        {

            string sqlwhere = "IsDel = 0 and Personnel = " + Personnel + " and ( GroupDate Between '" + startDate + "' and '" + EndDate + "' )";

            if (YearMonth != "全部月份")
                sqlwhere += " and WageYearMonth ='" + YearMonth + "'";

            return PageBase<Commission>.excutePageSql(new Commission(), "Commission", "Commission", "*", "GroupDate asc", sqlwhere, 10, pageIndex, out sumPage, out totalRecord);
        }

        /// <summary>
        /// 查找所有数据
        /// </summary>
        /// <param name="Personnel"></param>
        /// <param name="startDate"></param>
        /// <param name="EndDate"></param>
        /// <returns></returns>
        public List<Commission> GetAll(int Personnel, string startDate, string EndDate)
        {

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


            return excuteSql(sqlwhere);
        }

        /// <summary>
        /// 根据月份查找数据
        /// </summary>
        /// <param name="Personnel"></param>
        /// <param name="startDate"></param>
        /// <param name="EndDate"></param>
        /// <returns></returns>
        public List<Commission> GetByYearMonth(int Personnel, string WageYearMonth)
        {

            string sqlwhere = "select * from Commission where IsDel = 0 and Personnel = " + Personnel + " and WageYearMonth='" + WageYearMonth + "'";


            return excuteSql(sqlwhere);
        }


        /// <summary>
        ///根据ID查找
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Commission GetByID(int id)
        {
            return excuteType("select * from Commission where Isdel=0 and Id=@id", new SqlParameter("@id", id));
        }

        /// <summary>
        /// 雷怡 2021-09-23 17:36
        ///根据团组编号查找
        /// </summary>
        /// <param name="Diid"></param>
        /// <returns></returns>
        public List<Commission> GetByDiid(int Diid)
        {
            return excuteSql("select * from Commission where Isdel=0 and Diid=@Diid", new SqlParameter("@Diid", Diid));
        }

        /// <summary>
        /// 雷怡 2021-09-24 17:36
        /// 查询去重团组编号
        /// </summary>
        /// <returns></returns>
        public List<Commission> GetDiidAllPage(int pageIndex, out int sumPage, out int totalRecord,  string startDate, string EndDate)
        {

            string sqlwhere = " id in (select max(id) from Commission group by Diid) and IsDel = 0 ";

            if (!string.IsNullOrEmpty(startDate) && !string.IsNullOrEmpty(EndDate))
                sqlwhere += " and (GroupDate Between '" + startDate + "' and '" + EndDate + "')";

            return PageBase<Commission>.excutePageSql(new Commission(), "Commission", "Commission", "*", "GroupDate asc", sqlwhere, 10, pageIndex, out sumPage, out totalRecord);
        }

    }
}