OA2021/DAL/VisaCommissionService.cs

using Models;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    public class VisaCommissionService
    {
        /// <summary>
        /// 查询集合
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        List<VisaCommission> excuteSql(string sql, params SqlParameter[] param)
        {
            return ServiceBase<VisaCommission>.excuteSql(new VisaCommission(), "VisaCommission", sql, CommandType.Text, param);
        }
        /// <summary>
        /// 查询单个
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        VisaCommission excuteType(string sql, params SqlParameter[] param)
        {
            //查询结果放入对象集合
            List<VisaCommission> hdList = excuteSql(sql, param);
            VisaCommission visa = new VisaCommission();

            //判断集合是否为空
            if (hdList == null || hdList.Count == 0)
            {
                //返回null
                return null;
            }
            //返回单个对象
            return hdList[0];
        }
        /// <summary>
        /// 根据团组Id和国家查询单条
        /// </summary>
        /// <param name="ShortCode"></param>
        /// <returns></returns>
        public VisaCommission getByDiId(int diid,string Nation,int UserId)
        {
            return excuteType("select * from VisaCommission where Nation='" + Nation + "' and Isdel=0 and DiId=@diid and UId="+ UserId, new SqlParameter("@diid", diid));
        }
        /// <summary>
        /// 增加
        /// </summary>
        /// <param name="cd"></param>
        /// <returns></returns>
        public bool Add(VisaCommission Vc)
        {
            string sql = "insert into VisaCommission values(@DiId,@UId,@Number,@Nation,@CreateUserId,@CreateDateTime,@Remark,@IsDel)";

            SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@DiId",Vc.DiId),
                new SqlParameter("@UId",Vc.UId),
                new SqlParameter("@Number",Vc.Number),
                new SqlParameter("@Nation",Vc.Nation),
                new SqlParameter("@CreateUserId",Vc.CreateUserId),
                new SqlParameter("@CreateDateTime",Vc.CreateDateTime),
                new SqlParameter("@Remark",Vc.Remark),
                new SqlParameter("@IsDel",Vc.IsDel)
            };

            if (SqlHelper.ExecuteNonQuery(sql, CommandType.Text, parameter) > 0)
                return true;
            return false;
        }
        /// <summary>
        /// 获取信用卡账单类型费用
        /// </summary>
        /// <returns></returns>
        public List<VisaCommission> GetAll(int diid,int UserId)
        {
            return excuteSql("select * from VisaCommission where DiId="+diid+ " and isdel = 0 and UId="+UserId);
        }
        /// <summary>
        /// 编辑
        /// </summary>
        /// <param name="sdt"></param>
        /// <returns></returns>
        public bool Edit(VisaCommission Vc)
        {
            string sql = "update VisaCommission set Number=@Number,CreateUserId=@CreateUserId,CreateDateTime=@CreateDateTime,Remark=@Remark where Id = @Id";

            SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@Id ",Vc.Id ),
                new SqlParameter("@Number",Vc.Number),
                new SqlParameter("@Remark",Vc.Remark),
                new SqlParameter("@CreateUserId",Vc.CreateUserId),
                new SqlParameter("@CreateDateTime",Vc.CreateDateTime),
            };

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

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

    }
}