OA2021/DAL/InsuranceCostService.cs

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


namespace DAL
{
    public class InsuranceCostService
    {

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

        /// <summary>
        /// 增加
        /// </summary>
        /// <returns></returns>
        public bool AddInsuranceCost(InsuranceCost c, out int id)
        {
            string sql = "insert into InsuranceCost values(@GName,@RechargeCost,@Remarks,@Operators,@OperatorDate,@IsDel);SELECT @@IDENTITY";

            SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@GName",c.GName),
                new SqlParameter("@RechargeCost",c.RechargeCost),
                new SqlParameter("@Remarks",c.Remarks),
                new SqlParameter("@Operators",c.Operators),
                new SqlParameter("@OperatorDate",c.OperatorDate),
                new SqlParameter("@IsDel",c.IsDel)
            };

            int obj = Convert.ToInt32(SqlHelper.ExecuteScalar(sql, CommandType.Text, parameter));

            if (obj > 0)
            {
                id = obj;
                return true;
            }

            id = 0;
            return false;
        }


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

        public InsuranceCost GetInsuranceCostInfoByID(int id)
        {
            return excuteType("select * from InsuranceCost where Id = @id and isdel=0", new SqlParameter("@id", id));
            // return excuteType("select SUM(InsuranceBalance) as InsuranceBalance,GName from InsuranceCost where DId = @id and isdel=0 group by InsuranceBalance,GName", new SqlParameter("@id", id));
        }

        /// <summary>
        /// 获取全部 - 分页
        /// </summary>
        /// <returns></returns>
        public List<InsuranceCost> GetInsuranceCost(int pageIndex, out int sumPage, out int totalRecord, string tourCode, string clientName)
        {
            string sqlwhere;
            if ((tourCode == "" || tourCode == null) && (clientName == "" || clientName == null))
            {
                sqlwhere = "IsDel = 0 ";
            }
            else
            {
                sqlwhere = "IsDel = 0 and DID = '" + tourCode + "' and GName like '%" + clientName + "%'";
            }
            return PageBase<InsuranceCost>.excutePageSql(new InsuranceCost(), "InsuranceCost", "InsuranceCost", "*", "id desc", sqlwhere, 10, pageIndex, out sumPage, out totalRecord);
        }
        /// <summary>
        /// 根据id查询信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public InsuranceCost GetInsuranceCostByID(int id)
        {
            return excuteType("select * from InsuranceCost where Id = @id and isdel=0 order by id asc", new SqlParameter("@id", id));
            // return excuteType("select SUM(InsuranceBalance) as InsuranceBalance,GName from InsuranceCost where DId = @id and isdel=0 group by InsuranceBalance,GName", new SqlParameter("@id", id));
        }

        public bool EditInsuranceCostByID(InsuranceCost c, int id)
        {

            string sql = "update InsuranceCost set GName = @ClientName,RechargeCost = @RechargeCost,Remarks = @Remarks,OperatorDate = @OperatorDate where Id = @Id";

            SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@ClientName",c.GName),
                new SqlParameter("@RechargeCost",c.RechargeCost),
                new SqlParameter("@Remarks",c.Remarks),
                new SqlParameter("@OperatorDate",c.OperatorDate),
                new SqlParameter("@Id",id)
            };

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

        /// <summary>
        /// 名称 Id
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<InsuranceCost> GetInsuranceCostInfo()
        {
            return excuteSql("select * from InsuranceCost where isdel=0 order by id asc");
        }

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






        ///// <summary>
        ///// 根据ID获取 - 分页
        ///// </summary>
        //public DataTable GetAll(int pageIndex, int pageSize, out int sumPage, out int totalRecord, int id)
        //{
        //    string sql = "select g.ClientName,g.InsuranceCosts,d.TeamName from InsuranceCost as i join GroupsCustomers as g on g.Iid = i.Id join DelegationInfo as d on g.DIId = d.Id where g.IsDel = 0 and i.Id = @id ";

        //    SqlParameter[] parameter = new SqlParameter[]{
        //        new SqlParameter("@Id",id),

        //    };
        //    object result = SqlHelper.ExecuteScalar(sql, CommandType.Text, parameter);
        //    if (result != null)
        //    {
        //        totalRecord = Convert.ToInt32(result.ToString());
        //        //计算出总页数
        //        sumPage = totalRecord % 10 == 0 ? totalRecord / 10 : totalRecord / 10 + 1;
        //    }
        //    else
        //    {
        //        totalRecord = 0;
        //        //计算出总页数
        //        sumPage = 1;
        //    }
        //    return SqlHelper.TransferProcedure("exec_page", CommandType.StoredProcedure, parameter);
        //   // return SqlHelper.TransferProcedure("exec_creditCardPayment", CommandType.StoredProcedure, parameter);
        //}


    }
}