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

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


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

        /// <summary>
        /// 根据编号查询已删除团组信息
        /// 20210924 贾文滔
        /// </summary>
        /// <param name="id">对象编号</param>
        /// <returns>返回空或者单个对象信息</returns>
        public DelegationInfo GetRemoveDeleByID(int id)
        {
            //调用获取单个对象的方法
            return excuteType("select * from DelegationInfo where Id = @id and isdel=1 ", new SqlParameter("@id", id));
        }

        /// <summary>
        /// 根据团组名查询对象信息
        /// </summary>
        /// <param name="id">对象编号</param>
        /// <returns>返回空或者单个对象信息</returns>
        public DelegationInfo GetDelegationInfoByName(string name)
        {
            //调用获取单个对象的方法
            return excuteType("select * from DelegationInfo where Isdel=0 and TeamName ='" + name + "'");
        }

        /// <summary>
        /// 根据国家名查询对象信息
        /// </summary>
        /// <param name="id">对象编号</param>
        /// <returns>返回空或者单个对象信息</returns>
        public DelegationInfo GetDelegationInfoByCountryName(string name)
        {
            //调用获取单个对象的方法
            return excuteType("select * from DelegationInfo where Isdel=0 and Visitcountry ='" + name + "'");
        }
        /// <summary>
        /// 获取全部
        /// </summary>
        /// <returns></returns>
        public List<DelegationInfo> GetAll()
        {
            return excuteSql("select * from DelegationInfo Where IsDel = 0 order by Id desc");
        }

        /// <summary>
        /// 获取全部
        /// </summary>
        /// <returns></returns>
        public List<DelegationInfo> GetAllByTourCode()
        {
            return excuteSql("select * from DelegationInfo Where IsDel = 0 and TourCode is not null and IsState = 0 order by VisitStartDate desc");
        }

        /// <summary>
        /// 按团组名 查询信息
        /// </summary>
        /// <param name="TeamName"></param>
        /// <returns></returns>
        public DelegationInfo GetSingleByTeamName(string TeamName)
        {
            return excuteType("select * from DelegationInfo Where IsDel = 0 and TourCode is not null and TeamName like '%" + TeamName + "%' and IsState = 0 order by VisitStartDate desc");

        }

        /// <summary>
        /// 按团组名 查询多条信息
        /// </summary>
        /// <param name="TeamName"></param>
        /// <returns></returns>
        public List<DelegationInfo> GetByListTeamName(string TeamName)
        {
            return excuteSql("select * from DelegationInfo Where IsDel = 0 and TourCode is not null and TeamName like '%" + TeamName + "%' and IsState = 0 order by VisitStartDate desc");

        }

        /// <summary>
        /// 获取全部
        /// </summary>
        /// <returns></returns>
        public List<DelegationInfo> GetAllByTourCodeInId(string arrayId)
        {
            return excuteSql("select * from DelegationInfo Where IsDel = 0 and TourCode is not null and IsState = 0 and Id in (" + arrayId + ") order by VisitStartDate desc");
        }


        /// <summary>
        ///  根据条件查询条件获取 - 分页
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="sumPage"></param>
        /// <param name="totalRecord"></param>
        /// <param name="teamName"></param>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <returns></returns>
        public List<DelegationInfo> GetAll(int pageIndex, out int sumPage, out int totalRecord, string teamName, string startTime, string endTime)
        {
            string sqlwhere = "TeamName like '%" + teamName + "%' and IsDel = 0";

            if (!string.IsNullOrEmpty(startTime) && !string.IsNullOrEmpty(endTime))
                sqlwhere += " and (VisitDate between '" + startTime + "' and '" + endTime + "')";

            return PageBase<DelegationInfo>.excutePageSql(new DelegationInfo(), "DelegationInfo", "DelegationInfo", "*", "jietuanTime desc", sqlwhere, 10, pageIndex, out sumPage, out totalRecord);
        }


        /// <summary>
        /// 根据条件查询条件获取 - 分页
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="sumPage"></param>
        /// <param name="totalRecord"></param>
        /// <param name="dataType"></param>
        /// <param name="name"></param>
        /// <returns></returns>
        public List<DelegationInfo> GetAll(int pageIndex, out int sumPage, out int totalRecord, string isState, string teamType, string teamName, string clientName, string clientUnit, string startTime, string endTime, string tourCode, string userId)
        {
            string sqlwhere = "TeamName like '%" + teamName + "%' and ClientName like '%" + clientName + "%' and ClientUnit like '%" + clientUnit + "%' and IsSure = " + isState + " and IsDel = 0";

            if (teamType != "全部")
                sqlwhere += " and TeamDid = " + teamType;

            if (!string.IsNullOrEmpty(startTime) && !string.IsNullOrEmpty(endTime))
                sqlwhere += " and (VisitDate between '" + startTime + "' and '" + endTime + "')";

            if (!string.IsNullOrEmpty(userId))
                sqlwhere += " and JietuanOperator = " + userId;

            if (!string.IsNullOrEmpty(tourCode))
                sqlwhere += " and TourCode like '%" + tourCode + "%'";

            return PageBase<DelegationInfo>.excutePageSql(new DelegationInfo(), "DelegationInfo", "DelegationInfo", "*", "jietuanTime desc", sqlwhere, 10, pageIndex, out sumPage, out totalRecord);
        }

        /// <summary>
        /// 根据条件查询条件获取 - 分页
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="sumPage"></param>
        /// <param name="totalRecord"></param>
        /// <param name="dataType"></param>
        /// <param name="name"></param>
        /// <returns></returns>
        public List<DelegationInfo> GetAllByStatistics(int pageIndex, out int sumPage, out int totalRecord, string isState, string teamType, string teamName, string clientName, string clientUnit, string startTime, string endTime, string tourCode, string userId)
        {
            string sqlwhere = "(TourCode <> '' and TourCode is not null) and TeamName like '%" + teamName + "%' and ClientName like '%" + clientName + "%' and ClientUnit like '%" + clientUnit + "%' and IsSure = " + isState + " and IsDel = 0  ";

            if (teamType != "全部")
                sqlwhere += " and TeamDid = " + teamType;

            if (!string.IsNullOrEmpty(startTime) && !string.IsNullOrEmpty(endTime))
                sqlwhere += " and (VisitDate between '" + startTime + "' and '" + endTime + "')";

            if (!string.IsNullOrEmpty(userId))
                sqlwhere += " and JietuanOperator = " + userId;

            if (!string.IsNullOrEmpty(tourCode))
                sqlwhere += " and TourCode like '%" + tourCode + "%'";

            return PageBase<DelegationInfo>.excutePageSql(new DelegationInfo(), "DelegationInfo", "DelegationInfo", "*", "VisitDate desc", sqlwhere, 10, pageIndex, out sumPage, out totalRecord);
        }

        /// <summary>
        /// 获取对象
        /// </summary>
        /// <returns></returns>
        public DelegationInfo GetDelegationInfo(string clientName, string clientUnit, string teamName, int teamDid, string visitDate)
        {
            string sql = "select * from DelegationInfo Where IsDel = 0 and ClientName = @ClientName and ClientUnit = @ClientUnit and TeamName = @TeamName and TeamDid = @TeamDid and VisitDate = @VisitDate";
            SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@ClientName",clientName),
                new SqlParameter("@ClientUnit",clientUnit),
                new SqlParameter("@TeamName",teamName),
                new SqlParameter("@TeamDid",teamDid),
                new SqlParameter("@VisitDate",visitDate)
            };
            return excuteType(sql, parameter);
        }

        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="sdt">对象</param>
        public bool AddDelegationInfo(DelegationInfo di, out int id)
        {
            string sql = "insert into DelegationInfo"
                + "(SalesQuoteNo,SalesDate,ClientName,ClientUnit,TeamName,TeamDid,VisitDate,VisitCountry,"
                + "VisitPurpose,VisitDays,VisitPNumber,SpecialNeeds,OtherNeeds,Remark,IsState,JietuanOperator,JietuanTime,IsDel,BudgetCost,TellPhone,"
                + "TeamLev,HotelComplain,OPComplain,VisaComplain,OAComplain,AirComplain,Director,TontractTime,PaymentMoney,PayDay,CGRWSPBMMC,CGRWSPWH,ZZSCBMMC,ZZSCSPWH,WeChat,OpRoyaltyLv,OpRoyaltyRemark) "
                + "values("
                + "@SalesQuoteNo,@SalesDate,@ClientName,@ClientUnit,@TeamName,@TeamDid,@VisitDate,@VisitCountry,"
                + "@VisitPurpose,@VisitDays,@VisitPNumber,@SpecialNeeds,@OtherNeeds,@Remark,@IsState,@JietuanOperator,@JietuanTime,@IsDel,@BudgetCost,@TellPhone,"
                + "@TeamLev,@HotelComplain,@OPComplain,@VisaComplain,@OAComplain,@AirComplain,@Director,@TontractTime,@PaymentMoney,@PayDay,@CGRWSPBMMC,@CGRWSPWH,@ZZSCBMMC,@ZZSCSPWH,@WeChat,@OpRoyaltyLv,@OpRoyaltyRemark);SELECT @@IDENTITY";

            SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@SalesQuoteNo",di.SalesQuoteNo),
                new SqlParameter("@SalesDate",di.SalesDate),
                new SqlParameter("@ClientName",di.ClientName),
                new SqlParameter("@ClientUnit",di.ClientUnit),
                new SqlParameter("@TeamName",di.TeamName),
                new SqlParameter("@TeamDid",di.TeamDid),
                new SqlParameter("@VisitDate",di.VisitDate),
                new SqlParameter("@VisitCountry",di.VisitCountry),
                new SqlParameter("@VisitPurpose",di.VisitPurpose),
                new SqlParameter("@VisitDays",di.VisitDays),
                new SqlParameter("@VisitPNumber",di.VisitPNumber),
                new SqlParameter("@SpecialNeeds",di.SpecialNeeds),
                new SqlParameter("@OtherNeeds",di.OtherNeeds),
                new SqlParameter("@Remark",di.Remark),
                new SqlParameter("@IsState",di.IsState),
                new SqlParameter("@JietuanOperator",di.JietuanOperator),
                new SqlParameter("@JietuanTime",di.JietuanTime),
                new SqlParameter("@IsDel",di.IsDel),
                new SqlParameter("@BudgetCost",di.BudgetCost),
                new SqlParameter("@TellPhone",di.TellPhone),
                new SqlParameter("@TeamLev",di.TeamLev),
                new SqlParameter("@HotelComplain",di.HotelComplain),
                new SqlParameter("@OPComplain",di.OPComplain),
                new SqlParameter("@VisaComplain",di.VisaComplain),
                new SqlParameter("@OAComplain",di.OAComplain),
                new SqlParameter("@AirComplain",di.AirComplain),
                new SqlParameter("@Director",di.Director),
                new SqlParameter("@TontractTime",di.TontractTime),
                new SqlParameter("@PaymentMoney",di.PaymentMoney),
                new SqlParameter("@PayDay",di.PayDay),
                //@CGRWSPBMMC,@CGRWSPWH,@ZZSCBMMC,@ZZSCSPWH
                 new SqlParameter("@CGRWSPBMMC",di.CGRWSPBMMC),
                 new SqlParameter("@CGRWSPWH",di.CGRWSPWH),
                 new SqlParameter("@ZZSCBMMC",di.ZZSCBMMC),
                 new SqlParameter("@ZZSCSPWH",di.ZZSCSPWH),
                 new SqlParameter("@WeChat",di.WeChat),
                 new SqlParameter("@OpRoyaltyLv",di.OpRoyaltyLv),
                 new SqlParameter("@OpRoyaltyRemark",di.OpRoyaltyRemark)

            };
            int obj = Convert.ToInt32(SqlHelper.ExecuteScalar(sql, CommandType.Text, parameter));
            if (obj > 0)
            {
                id = obj;
                return true;
            }
            else
            {
                id = 0;
                return false;
            }
            //if (SqlHelper.ExecuteNonQuery(sql, CommandType.Text, parameter) > 0)
            //    return true;
            //return false;
        }

        /// <summary>
        /// 编辑
        /// </summary>
        /// <param name="sdt"></param>
        /// <returns></returns>
        public bool EditDelegationInfo(DelegationInfo di)
        {
            string sql = "update DelegationInfo set "
                + "ClientName = @ClientName,ClientUnit = @ClientUnit,TeamName = @TeamName,TeamDid = @TeamDid,"
                + "VisitDate = @VisitDate,VisitStartDate = @VisitStartDate,VisitEndDate = @VisitEndDate,VisitCountry = @VisitCountry,"
                + "VisitPurpose = @VisitPurpose,VisitDays = @VisitDays,VisitPNumber = @VisitPNumber,SpecialNeeds = @SpecialNeeds,OtherNeeds = @OtherNeeds,"
                + "Remark = @Remark,JietuanOperator = @JietuanOperator,JietuanTime = @JietuanTime,BudgetCost = @BudgetCost,TellPhone = @TellPhone,"
                + "TeamLev = @TeamLev,HotelComplain = @HotelComplain,OPComplain = @OPComplain,VisaComplain = @VisaComplain,OAComplain = @OAComplain,AirComplain = @AirComplain,"
                + "Director = @Director,TontractTime = @TontractTime,PaymentMoney = @PaymentMoney,PayDay=@PayDay,CGRWSPBMMC=@CGRWSPBMMC,CGRWSPWH=@CGRWSPWH,ZZSCBMMC=@ZZSCBMMC," +
                "ZZSCSPWH=@ZZSCSPWH,WeChat=@WeChat,OpRoyaltyLv=@OpRoyaltyLv,OpRoyaltyRemark=@OpRoyaltyRemark  where Id = @Id";

            SqlParameter[] parameter = new SqlParameter[] {
                new SqlParameter("@ClientName",di.ClientName),
                new SqlParameter("@ClientUnit",di.ClientUnit),
                new SqlParameter("@TeamName",di.TeamName),
                new SqlParameter("@TeamDid",di.TeamDid),
                new SqlParameter("@VisitDate",di.VisitDate),
                new SqlParameter("@VisitStartDate",di.VisitStartDate),
                new SqlParameter("@VisitEndDate",di.VisitEndDate),
                new SqlParameter("@VisitCountry",di.VisitCountry),
                new SqlParameter("@VisitPurpose",di.VisitPurpose),
                new SqlParameter("@VisitDays",di.VisitDays),
                new SqlParameter("@VisitPNumber",di.VisitPNumber),
                new SqlParameter("@SpecialNeeds",di.SpecialNeeds),
                new SqlParameter("@OtherNeeds",di.OtherNeeds),
                new SqlParameter("@Remark",di.Remark),
                new SqlParameter("@JietuanOperator",di.JietuanOperator),
                new SqlParameter("@JietuanTime",di.JietuanTime),
                new SqlParameter("@BudgetCost",di.BudgetCost),
                new SqlParameter("@Id",di.Id),
                new SqlParameter("@TellPhone",di.TellPhone),
                new SqlParameter("@TeamLev",di.TeamLev),
                new SqlParameter("@HotelComplain",di.HotelComplain),
                new SqlParameter("@OPComplain",di.OPComplain),
                new SqlParameter("@VisaComplain",di.VisaComplain),
                new SqlParameter("@OAComplain",di.OAComplain),
                new SqlParameter("@AirComplain",di.AirComplain),
                new SqlParameter("@Director",di.Director),
                new SqlParameter("@TontractTime",di.TontractTime),
                new SqlParameter("@PaymentMoney",di.PaymentMoney),
                new SqlParameter("@PayDay",di.PayDay),
                //@CGRWSPBMMC,@CGRWSPWH,@ZZSCBMMC,@ZZSCSPWH
                 new SqlParameter("@CGRWSPBMMC",di.CGRWSPBMMC),
                 new SqlParameter("@CGRWSPWH",di.CGRWSPWH),
                 new SqlParameter("@ZZSCBMMC",di.ZZSCBMMC),
                 new SqlParameter("@ZZSCSPWH",di.ZZSCSPWH),
                 new SqlParameter("@WeChat",di.WeChat),
                 new SqlParameter("@OpRoyaltyLv",di.OpRoyaltyLv),
                 new SqlParameter("@OpRoyaltyRemark",di.OpRoyaltyRemark)
            };

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

        /// <summary>
        /// 编辑
        /// </summary>
        /// <param name="sdt"></param>
        /// <returns></returns>
        public bool EditDelegationInfoOperations(DelegationInfo di)
        {
            string sql = "update DelegationInfo set TourCode = @TourCode,VisitDate = @VisitDate,VisitStartDate = @VisitStartDate," 
                +"VisitEndDate = @VisitEndDate,VisitPurpose = @VisitPurpose,VisitDays = @VisitDays,ClientNeeds = @ClientNeeds,Remark = @Remark," 
                +"GroupsOperator = @GroupsOperator,GroupsTime = @GroupsTime,BudgetCost = @BudgetCost where Id = @Id";

            SqlParameter[] parameter = new SqlParameter[] {
                new SqlParameter("@TourCode",di.TourCode),
                new SqlParameter("@VisitDate",di.VisitDate),
                new SqlParameter("@VisitStartDate",di.VisitStartDate),
                new SqlParameter("@VisitEndDate",di.VisitEndDate),
                new SqlParameter("@VisitDays",di.VisitDays),
                new SqlParameter("@VisitPurpose",di.VisitPurpose),
                new SqlParameter("@ClientNeeds",di.ClientNeeds),
                new SqlParameter("@Remark",di.Remark),
                new SqlParameter("@GroupsOperator",di.GroupsOperator),
                new SqlParameter("@GroupsTime",di.GroupsTime),
                new SqlParameter("@BudgetCost",di.BudgetCost),
                new SqlParameter("@Id",di.Id)
            };

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

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

        /// <summary>
        /// 更改状态
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool UpdateDelegationInfoState(int id)
        {
            if (SqlHelper.ExecuteNonQuery("update DelegationInfo set IsState = 1 where Id = @Id", CommandType.Text, new SqlParameter("@Id", id)) > 0)
                return true;
            return false;
        }
        /// <summary>
        /// LiuChengYi 2014/4/16
        /// 用于出访国家的数据统计
        /// </summary>
        /// <returns></returns>
        public DataTable ReportDelegationInfo()
        {
            return SqlHelper.TransferProcedure("DelegationsToVisitStatisticsList", CommandType.StoredProcedure);
        }

        /// <summary>
        /// 更改是否操作完成状态
        /// </summary>
        /// <param name="diid"></param>
        /// <returns></returns>
        public bool EditDelegationInfoAsSure(int id)
        {
            if (SqlHelper.ExecuteNonQuery("update DelegationInfo set IsSure = 1,SureTime='" + DateTime.Now + "' where Id = @Id", CommandType.Text, new SqlParameter("@Id", id)) > 0)
                return true;
            return false;
        }
        /// <summary>
        /// LiuChengYi 2014/4/16
        /// 用于出访国家的数据统计
        /// </summary>
        /// <returns></returns>
        public DataTable ReportDelegationInfo(string year)
        {
            SqlParameter[] pars =
            {
               new SqlParameter("@year",year)
            };
            return SqlHelper.TransferProcedure("DelegationsToVisitStatisticsList", CommandType.StoredProcedure, pars);
        }

        public List<DelegationInfo> getByName(string name)
        {
            return excuteSql("select * from DelegationInfo where Isdel=0 and TeamName like '%" + name + "%' order by Id");
        }

        /// <summary>
        /// 根据年份查询 出访团组
        /// </summary>
        /// <param name="year">年份</param>
        /// <returns></returns>
        public List<DelegationInfo> getByVisitDate(string year)
        {
            return excuteSql("select * from DelegationInfo where Isdel=0 and VisitDate like '" + year + "%' order by VisitDate");
        }

        /// <summary>
        /// 贾文滔 2021-06-23
        /// 根据指定时间段查询团组
        /// </summary>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <returns></returns>
        public List<DelegationInfo> GetByTime(string startDate, string endDate)
        {
            //IsSure=1 and
            return excuteSql("select * from DelegationInfo where   Isdel=0 and (VisitEndDate between '" + startDate + "' and '" + endDate + "') order by VisitEndDate");
        }

        /// <summary>
        /// 雷怡 2021-07-26 15:58
        /// 根据团组号查询团组
        /// </summary>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <returns></returns>
        public DelegationInfo GetByTourCode(string TourCode)
        {
            return excuteType("select * from DelegationInfo where Isdel=0 and TourCode='" + TourCode + "'");
        }

        /// <summary>
        /// 根据条件查询条件获取 - 分页
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="sumPage"></param>
        /// <param name="totalRecord"></param>
        /// <param name="dataType"></param>
        /// <param name="name"></param>
        /// <returns></returns>
        public List<DelegationInfo> GetAirInfo(int pageIndex, out int sumPage, out int totalRecord, string country)
        {
            string sqlwhere = "(TourCode <> '' and TourCode is not null) and IsDel = 0  ";



            if (!string.IsNullOrEmpty(country) && !string.IsNullOrEmpty(country))
                sqlwhere += " and (VisitCountry like '%" + country + "%')";



            return PageBase<DelegationInfo>.excutePageSql(new DelegationInfo(), "DelegationInfo", "DelegationInfo", "*", "VisitDate desc", sqlwhere, 10, pageIndex, out sumPage, out totalRecord);
        }

        /// <summary>
        /// 雷怡 2021-08-03 15：20
        /// 根据时间段查询 出访团组数量
        /// </summary>
        /// <param name="startTime">开始时间</param>
        /// <param name="endTime">结束时间</param>
        /// <param name="typeId">
        /// 0:团组(38(政府团) 39(企业团) 40(散客团))
        /// 1:活动(102(未知) 248(非团组) 302(公关传媒部) 691(会务活动))
        /// </param>
        /// <returns></returns>
        public int GetMonthGroupNumber(string startTime, string endTime, int typeId)
        {
            string sql = "select * from DelegationInfo where Isdel = 0 ";

            if (typeId == 0)
            {
                sql += " and TeamDid in(38,39,40)";
            }
            else if (typeId == 1)
            {
                sql += " and TeamDid in(102,248,302,691)";
            }
            sql += " and VisitStartDate between '" + startTime + "' and '" + endTime + "'";

            return excuteSql(sql).Count;
        }

        /// <summary>
        /// 雷怡 2021-08-03 15：20
        /// 查询至今未完成的团 数量
        /// <returns></returns>
        public int GetUncollected()
        {
            string sql = "select * from DelegationInfo where isdel = 0 and IsSure = 0";
            return excuteSql(sql).Count;
        }



    }
    
}