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

namespace DAL
{
    public class InvitingPartyInfoService
    {
        List<InvitingPartyInfo> excuteSql(string sql, params SqlParameter[] param)
        {
            return ServiceBase<InvitingPartyInfo>.excuteSql(new InvitingPartyInfo(), "InvitingPartyInfo", sql, CommandType.Text, param);
        }
        List<InvitationOfficialActivityData> excuteSql2(string sql, params SqlParameter[] param)
        {
            return ServiceBase<InvitationOfficialActivityData>.excuteSql(new InvitationOfficialActivityData(), "InvitationOfficialActivityData", sql, CommandType.Text, param);
        }
        InvitingPartyInfo excuteType(string sql, params SqlParameter[] param)
        {
            //查询结果放入对象集合
            List<InvitingPartyInfo> hdList = excuteSql(sql, param);
            //判断集合是否为空
            if (hdList == null || hdList.Count == 0)
                //返回null
                return null;
            //返回单个对象
            return hdList[0];
        }
        InvitationOfficialActivityData excuteTypeActivityData(string sql, params SqlParameter[] param)
        {
            //查询结果放入对象集合
            List<InvitationOfficialActivityData> hdList = excuteSql2(sql, param);
            //判断集合是否为空
            if (hdList == null || hdList.Count == 0)
                //返回null
                return null;
            //返回单个对象
            return hdList[0];
        }

        public List<InvitingPartyInfo> GetAllByDiid(int diid)
        {
            return excuteSql("select * from InvitingPartyInfo Where IsDel = 0 and Diid=" + diid+" order by Id desc");
        }

        /// <summary>
        /// 根据 团组编号 国家 城市 查询 信息
        /// </summary>
        /// <param name="diid">团组编号</param>
        /// <param name="Country">国家</param>
        /// <param name="City">城市</param>
        /// <returns></returns>
        public InvitingPartyInfo GetAddSelect(int diid,string Country,string City)
        {
            //调用获取单个对象的方法
            return excuteType("select * from InvitingPartyInfo where Isdel=0 and diid =" + diid + " and Country='"+ Country + "' and city='"+City+"';");
        }
        /// <summary>
        /// 根据 团组编号 国家 城市 查询 信息
        /// </summary>
        /// <param name="diid">团组编号</param>
        /// <param name="Country">国家</param>
        /// <param name="City">城市</param>
        /// <returns></returns>
        public InvitationOfficialActivityData GetActivityDataAddSelect(string Country, string City, string Address,string Field)
        {
            //调用获取单个对象的方法
            return excuteTypeActivityData("select * from InvitationOfficialActivityData where Country='"+ Country + "'and City='"+City+"' and Address='"+Address+"' and Field='"+Field+"';");
        }
        /// <summary>
        /// 查询单个信息
        /// </summary>
        /// <param name="diid"></param>
        /// <param name="Country"></param>
        /// <param name="City"></param>
        /// <returns></returns>
        public InvitingPartyInfo GetById(int id)
        {
            //调用获取单个对象的方法
            return excuteType("select * from InvitingPartyInfo where Id=" + id);
        }
        //增Add
        public bool Add(InvitingPartyInfo ip)
        {
            string sql = "insert into InvitingPartyInfo values(@Diid,@Country,@City,@InvitingUnit,@InvitingAddress,@Contacts,@ContactsJob,@ContactsTel,"
                                                        + "@ContactsEmail,@ContactsFax,@InvitingUrl,@Oper,@Opdate,@Isdel,@UnitWeb,@Field);SELECT @@IDENTITY";

            SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@Diid",ip.Diid),
                new SqlParameter("@Country",ip.Country),
                new SqlParameter("@City",ip.City),
                new SqlParameter("@InvitingUnit",ip.InvitingUnit),
                new SqlParameter("@InvitingAddress",ip.InvitingAddress),
                new SqlParameter("@Contacts",ip.Contacts),
                new SqlParameter("@ContactsJob",ip.ContactsJob),
                new SqlParameter("@ContactsTel",ip.ContactsTel),
                new SqlParameter("@ContactsEmail",ip.ContactsEmail),
                new SqlParameter("@ContactsFax",ip.ContactsFax),
                new SqlParameter("@InvitingUrl",ip.InvitingUrl),
                new SqlParameter("@Oper",ip.Oper),
                new SqlParameter("@Opdate",ip.Opdate),
                new SqlParameter("@Isdel",ip.Isdel),
                new SqlParameter("@UnitWeb",ip.UnitWeb),
                new SqlParameter("@Field",ip.Field)
            };

            if (SqlHelper.ExecuteNonQuery(sql, CommandType.Text, parameter) > 0)
                return true;
            return false;
        }
        //改Update
        public bool Edit(InvitingPartyInfo ip)
        {
            string sql = "update InvitingPartyInfo set Diid=@Diid,Country=@Country,City=@City,InvitingUnit=@InvitingUnit,InvitingAddress=@InvitingAddress,Contacts=@Contacts,"
                + "ContactsJob=@ContactsJob,ContactsTel=@ContactsTel,ContactsEmail=@ContactsEmail,ContactsFax=@ContactsFax,InvitingUrl=@InvitingUrl,Oper=@Oper,Opdate=@Opdate,Isdel=@Isdel," +
                "UnitWeb=@UnitWeb,Field=@Field where Id=@Id";

            SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@Diid",ip.Diid),
                new SqlParameter("@Country",ip.Country),
                new SqlParameter("@City",ip.City),
                new SqlParameter("@InvitingUnit",ip.InvitingUnit),
                new SqlParameter("@InvitingAddress",ip.InvitingAddress),
                new SqlParameter("@Contacts",ip.Contacts),
                new SqlParameter("@ContactsJob",ip.ContactsJob),
                new SqlParameter("@ContactsTel",ip.ContactsTel),
                new SqlParameter("@ContactsEmail",ip.ContactsEmail),
                new SqlParameter("@ContactsFax",ip.ContactsFax),
                new SqlParameter("@InvitingUrl",ip.InvitingUrl),
                new SqlParameter("@Oper",ip.Oper),
                new SqlParameter("@Opdate",ip.Opdate),
                new SqlParameter("@Isdel",ip.Isdel),
                new SqlParameter("@Id",ip.Id),
                new SqlParameter("@UnitWeb",ip.UnitWeb),
                new SqlParameter("@Field",ip.Field)
            };

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

        public List<InvitationOfficialActivityData> ActivityDataQurey(string ddlTeamName)
        {
            return excuteSql2("select * from InvitationOfficialActivityData Where IsDel = 0 and  Delegation='"+ ddlTeamName + "' order by OperatorDate desc");
        }
    }
}