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

namespace DAL
{
    /// <summary>
    /// 公务活动资料数据访问类
    /// </summary>
    public class InvitationOfficialActivityDataService
    {
        /// <summary>
        /// 查询所有
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">可变参数数组</param>
        /// <returns>返回集合</returns>
        List<InvitationOfficialActivityData> excuteSql(string sql, params SqlParameter[] param)
        {
            return ServiceBase<InvitationOfficialActivityData>.excuteSql(new InvitationOfficialActivityData(), "InvitationOfficialActivityData", sql, CommandType.Text, param);
        }
        /// <summary>
        /// 查询所有
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">可变参数数组</param>
        /// <returns>返回集合</returns>
        List<AssociationGroup> excuteSql1(string sql, params SqlParameter[] param)
        {
            return ServiceBase<AssociationGroup>.excuteSql(new AssociationGroup(), "AssociationGroup", sql, CommandType.Text, param);
        }
        /// <summary>
        /// 查询所有团组名称
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">可变参数数组</param>
        /// <returns>返回集合</returns>
        List<DelegationInfo> excuteDelegationSql(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>
        InvitationOfficialActivityData excuteType(string sql, params SqlParameter[] param)
        {
            //查询结果放入对象集合
            List<InvitationOfficialActivityData> ioadList = excuteSql(sql, param);
            //判断集合是否为空
            if (ioadList == null || ioadList.Count == 0)
                //返回null
                return null;
            //返回单个对象
            return ioadList[0];
        }
        /// <summary>
        /// 获取单个对象
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">可变参数数组</param>
        /// <returns>返回空或者单个对象</returns>
        AssociationGroup excuteType2(string sql, params SqlParameter[] param)
        {
            //查询结果放入对象集合
            List<AssociationGroup> ioadList = excuteSql1(sql, param);
            //判断集合是否为空
            if (ioadList == null || ioadList.Count == 0)
                //返回null
                return null;
            //返回单个对象
            return ioadList[0];
        }

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

        /// <summary>
        /// 根据国家 城市 团组名 查询 邀请方信息
        /// </summary>
        /// <param name="country">国家</param>
        /// <param name="city">城市</param>
        /// <param name="teamName">团组名</param>
        /// <returns></returns>
        public InvitationOfficialActivityData GetByCountryAndCity(string country,string city)
        {
            string sql = "select * from InvitationOfficialActivityData where IsDel = 0 and Country=@Country and City=@City ";
            SqlParameter[] pars = new SqlParameter[] {
                                  new SqlParameter("Country",country),
                                  new SqlParameter("City",city)
            };

            //调用获取单个对象的方法
            return excuteType(sql,pars);
        }
        /// <summary>
        /// 获取全部
        /// </summary>
        /// <returns></returns>
        public List<InvitationOfficialActivityData> GetAll()
        {
            return excuteSql("select * from InvitationOfficialActivityData where IsDel = 0");
        }
        /// <summary>
        /// 获取团组名称
        /// </summary>
        /// <returns></returns>
        public List<DelegationInfo> GetAllDelegationI()
        {
            return excuteDelegationSql("select * from DelegationInfo Where IsDel = 0 order by Id desc");
        }
        /// <summary>
        /// 查询信息
        /// </summary>
        /// <returns>返回空或者单个对象信息</returns>
        public List<InvitationOfficialActivityData> GetInvitationOfficialActivityDataUnitArea(string Country)
        {
            //调用获取单个对象的方法
            return excuteSql("select * from InvitationOfficialActivityData where Country like '%" + Country + "%'");
        }

        /// <summary>
        /// 查询对象信息
        /// </summary>
        /// <param name="city">所在城市</param>
        /// <param name="name">酒店名称</param>
        /// <returns>返回空或者单个对象信息</returns>
        public InvitationOfficialActivityData GetInvitationOfficialActivityData(string Country, string unitName)
        {
            //调用获取单个对象的方法
            return excuteType("select * from InvitationOfficialActivityData where Country = @Country and UnitName = @UnitName and IsDel = 0 ", new SqlParameter("@Country", Country), new SqlParameter("@UnitName", unitName));
        }
        /// <summary>
        /// 查询对象信息
        /// </summary>
        /// <param name="city">所在城市</param>
        /// <param name="name">酒店名称</param>
        /// <returns>返回空或者单个对象信息</returns>
        public InvitationOfficialActivityData GetInvitationOfficialActivityData(string Country, string unitName, string Delegation)
        {
            //调用获取单个对象的方法
            return excuteType("select * from InvitationOfficialActivityData where Country = @Country and UnitName = @UnitName and IsDel = 0 and Delegation=@Delegation", new SqlParameter("@Country", Country), new SqlParameter("@UnitName", unitName), new SqlParameter("@Delegation", Delegation));
        }

        public AssociationGroup GetAssociationGroup(int id)
        {
            //调用获取单个对象的方法
            return excuteType2("select * from AssociationGroup where  Ipid='"+id+"'");
        }

        /// <summary>
        /// 获取全部 - 分页
        /// </summary>
        /// <param name="pageIndex">当前页</param>
        /// <param name="sumPage">总页数</param>
        /// <param name="totalRecord">总条数</param>
        /// <param name="Country">国家</param>
        /// <param name="unitName">邀请方</param>
        /// <param name="contact">联系人</param>
        /// <param name="delegation">团名</param>
        /// <param name="domain">涉及领域</param>
        /// <returns></returns>
        public List<InvitationOfficialActivityData> GetInvitationOfficialActivityData(int pageIndex, out int sumPage, out int totalRecord, string Country, string unitName, string contact, string delegation,string domain,string ddlOP)
        {
            string sqlwhere = "IsDel = 0 ";

            if (!string.IsNullOrWhiteSpace(Country))
            {
                if (Country.Contains(','))
                    Country = Country.Replace(',', ' ');
                else if (Country.Contains('，'))
                    Country = Country.Replace('，', ' ');
                else if (Country.Contains('、'))
                    Country = Country.Replace('、', ' ');

                if (Country.Contains(" "))
                {
                    string sqlCity = " and (";
                    string[] sqlName = Country.Split(' ');

                    for (int i = 0, len = sqlName.Length; i < len; i++)
                        if (sqlName[i] != "")
                            if (i == len - 1)
                                sqlCity += " City like '%" + sqlName[i] + "%') ";
                            else
                                sqlCity += " City like '%" + sqlName[i] + "%' or";

                    sqlwhere += sqlCity;
                }
                else  sqlwhere += " and Country like '%" + Country + "%' ";
            }
           

            if (!string.IsNullOrWhiteSpace(unitName)) sqlwhere += " and UnitName like '%" + unitName + "%' ";
            if (!string.IsNullOrWhiteSpace(ddlOP)) sqlwhere += " and Operator ="+ ddlOP + "";
            if (!string.IsNullOrWhiteSpace(contact)) {
                if (contact == "未设置") sqlwhere += " and Contact = '' ";
                else sqlwhere += " and Contact like '%" + contact + "%' ";
            }
            if (!string.IsNullOrWhiteSpace(delegation)) {
                if (delegation == "未设置") sqlwhere += " and Delegation = '' ";
                else sqlwhere += " and Delegation like '%" + delegation + "%' ";
            } 
            if (!string.IsNullOrWhiteSpace(domain)) {

                if (domain == "未设置") sqlwhere += " and Field = '' "; 
                else sqlwhere += " and Field like '%" + domain + "%' ";
            } 

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

        /// <summary>
        /// 增加
        /// </summary>
        /// <param name="cd"></param>
        /// <returns></returns>
        public bool AddInvitationOfficialActivityData(InvitationOfficialActivityData ctggd)
        {
            string sql = "insert into InvitationOfficialActivityData values(@Country,@City,@UnitName,@UnitWeb,@Field,@Address,@UnitInfo,"
            + "@Contact,@Job,@Tel,@Email,@WeChat,@FaceBook,@Ins,@Delegation,@FileName,@FilePath,@SndFileName,@SndFilePath,@OtherInformation,"
            + "@Fax,@Operator,@OperatorDate,@IsDel,@Backdrop)";

            SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@Country",ctggd.Country),
                new SqlParameter("@City",ctggd.City),
                new SqlParameter("@UnitName",ctggd.UnitName),
                new SqlParameter("@UnitWeb",ctggd.UnitWeb),
                new SqlParameter("@Field",ctggd.Field),
                new SqlParameter("@Address",ctggd.Address),
                new SqlParameter("@UnitInfo",ctggd.UnitInfo),
                new SqlParameter("@Contact",ctggd.Contact),
                new SqlParameter("@Job",ctggd.Job),
                new SqlParameter("@Tel",ctggd.Tel),
                new SqlParameter("@Email",ctggd.Email),
                new SqlParameter("@WeChat",ctggd.WeChat),
                new SqlParameter("@FaceBook",ctggd.FaceBook),
                new SqlParameter("@Ins",ctggd.Ins),
                new SqlParameter("@Delegation",ctggd.Delegation),
                new SqlParameter("@FileName",ctggd.FileName),
                new SqlParameter("@FilePath",ctggd.FilePath),
                new SqlParameter("@SndFileName",ctggd.SndFileName),
                new SqlParameter("@SndFilePath",ctggd.SndFilePath),
                new SqlParameter("@OtherInformation",ctggd.OtherInformation),
                new SqlParameter("@Fax",ctggd.Fax),              
                new SqlParameter("@Operator",ctggd.Operators),
                new SqlParameter("@OperatorDate",ctggd.OperatorsDate),
                new SqlParameter("@IsDel",ctggd.IsDel),
                new SqlParameter("@Backdrop",ctggd.Backdrop)
            };

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

        /// <summary>
        /// 编辑
        /// </summary>
        /// <param name="sdt"></param>
        /// <returns></returns>
        public bool EditInvitationOfficialActivityData(InvitationOfficialActivityData ctggd)
        {
            string sql = "update InvitationOfficialActivityData set Country=@Country,City=@City,UnitName=@UnitName,UnitWeb=@UnitWeb,"
            + "Field=@Field,Address=@Address,UnitInfo=@UnitInfo,Contact=@Contact,Job=@Job,Tel=@Tel,Email=@Email,WeChat=@WeChat,"
            + "FaceBook=@FaceBook,Ins=@Ins,Delegation=@Delegation,FileName=@FileName,FilePath=@FilePath,SndFileName=@SndFileName,SndFilePath=@SndFilePath,OtherInformation=@OtherInformation,Fax=@Fax,Operator=@Operator,"
            + "OperatorDate=@OperatorDate,IsDel=@IsDel,Backdrop=@Backdrop where Id = @Id";

            SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@Country",ctggd.Country),
                new SqlParameter("@City",ctggd.City),
                new SqlParameter("@UnitName",ctggd.UnitName),
                new SqlParameter("@UnitWeb",ctggd.UnitWeb),
                new SqlParameter("@Field",ctggd.Field),
                new SqlParameter("@Address",ctggd.Address),
                new SqlParameter("@UnitInfo",ctggd.UnitInfo),
                new SqlParameter("@Contact",ctggd.Contact),
                new SqlParameter("@Job",ctggd.Job),
                new SqlParameter("@Tel",ctggd.Tel),
                new SqlParameter("@Email",ctggd.Email),
                new SqlParameter("@WeChat",ctggd.WeChat),
                new SqlParameter("@FaceBook",ctggd.FaceBook),
                new SqlParameter("@Ins",ctggd.Ins),
                new SqlParameter("@Delegation",ctggd.Delegation),
                new SqlParameter("@FileName",ctggd.FileName),
                new SqlParameter("@FilePath",ctggd.FilePath),
                new SqlParameter("@SndFileName",ctggd.SndFileName),
                new SqlParameter("@SndFilePath",ctggd.SndFilePath),
                new SqlParameter("@OtherInformation",ctggd.OtherInformation),
                new SqlParameter("@Fax",ctggd.Fax),              
                new SqlParameter("@Operator",ctggd.Operators),
                new SqlParameter("@OperatorDate",ctggd.OperatorsDate),
                new SqlParameter("@IsDel",ctggd.IsDel),
                new SqlParameter("@Id",ctggd.Id),
                new SqlParameter("@Backdrop",ctggd.Backdrop)
            };

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

        public bool AddAssociationGroup(int id1, int id2,int Diid)
        {
            string sql = "insert into AssociationGroup values(@id1,@id2,@Diid)";

            SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@id1",id1),
                new SqlParameter("@id2",id2),
                new SqlParameter("@Diid",Diid)
                
            };

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

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool DelInvitationOfficialActivityData(int id)
        {
            if (SqlHelper.ExecuteNonQuery("update InvitationOfficialActivityData set IsDel = 1 where Id = @Id", CommandType.Text, new SqlParameter("@Id", id)) > 0)
                return true;
            return false;
        }
        /// <summary>
        /// 查询某时间段的录入数量
        /// </summary>
        /// <param name="op"></param>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public List<InvitationOfficialActivityData> getBYOP(string op, string start, string end)
        {
            //调用获取单个对象的方法
            return excuteSql("select * from InvitationOfficialActivityData where Isdel=0 and Operator=" + op + " and ( OperatorDate between '" + start + "' and '" + end + "')");
        }

        /// <summary>
        /// 查找录入者
        /// </summary>
        /// <returns></returns>
        public DataTable getOP()
        {
            DataTable dt = SqlHelper.TransferProcedure("OP_SELECT", CommandType.StoredProcedure);
            if (dt != null && dt.Rows.Count > 0)
            {
                try
                {
                    return dt;
                }
                catch
                {
                    return null;
                }
            }
            else
                return null;
        }

    }
}