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

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

        //增Add
        public bool Add(Translator Dov)
        {
            string sql = "insert into Translator values(@OA_Oper,@Name,@Back,@Resume,@Language,@Tel,@Certificate,@knowRelations,@Shorthand,@Suit,@List,"
                + "@SuitScore,@ShortHandScore,@AbilityScore,@TimeScore,@LinkUpScore,@QualityScore,@StrainScore,@MeetScore,@CertificateScore,@Oper,@Opdate,@Isdel);SELECT @@IDENTITY";

            SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@OA_Oper",Dov.OA_Oper),
                new SqlParameter("@Name",Dov.Name),
                new SqlParameter("@Back",Dov.Back),
                new SqlParameter("@Resume",Dov.Resume),
                new SqlParameter("@Language",Dov.Language),
                new SqlParameter("@Tel",Dov.Tel),
                new SqlParameter("@Certificate",Dov.Certificate),
                new SqlParameter("@knowRelations",Dov.knowRelations),
                new SqlParameter("@Shorthand",Dov.Shorthand),
                new SqlParameter("@Suit",Dov.Suit),
                new SqlParameter("@List",Dov.List),
                new SqlParameter("@SuitScore",Dov.SuitScore),
                new SqlParameter("@ShortHandScore",Dov.ShortHandScore),
                new SqlParameter("@AbilityScore",Dov.AbilityScore),
                new SqlParameter("@TimeScore",Dov.TimeScore),
                new SqlParameter("@LinkUpScore",Dov.LinkUpScore),
                new SqlParameter("@QualityScore",Dov.QualityScore),
                new SqlParameter("@StrainScore",Dov.StrainScore),
                new SqlParameter("@MeetScore",Dov.MeetScore),
                new SqlParameter("@CertificateScore",Dov.CertificateScore),
                new SqlParameter("@Oper",Dov.Oper),
                new SqlParameter("@Opdate",Dov.Opdate),
                new SqlParameter("@Isdel",Dov.Isdel)
            };

            if (SqlHelper.ExecuteNonQuery(sql, CommandType.Text, parameter) > 0)
                return true;
            return false;
        }
        //改Update
        public bool Edit(Translator Dov)
        {
            string sql = "update Translator set OA_Oper=@OA_Oper,Name=@Name,Back=@Back,Resume=@Resume,Language=@Language,Tel=@Tel,Certificate=@Certificate,knowRelations=@knowRelations,"
                + "Shorthand=@Shorthand,Suit=@Suit,List=@List,SuitScore=@SuitScore,ShortHandScore=@ShortHandScore,AbilityScore=@AbilityScore,"
                + "TimeScore=@TimeScore,LinkUpScore=@LinkUpScore,QualityScore=@QualityScore,StrainScore=@StrainScore,MeetScore=@MeetScore,CertificateScore=@CertificateScore,"
                + "Oper=@Oper,Opdate=@Opdate,Isdel=@Isdel where Id = @Id";

            SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@OA_Oper",Dov.OA_Oper),
                new SqlParameter("@Name",Dov.Name),
                new SqlParameter("@Back",Dov.Back),
                new SqlParameter("@Resume",Dov.Resume),
                new SqlParameter("@Language",Dov.Language),
                new SqlParameter("@Tel",Dov.Tel),
                new SqlParameter("@Certificate",Dov.Certificate),
                new SqlParameter("@knowRelations",Dov.knowRelations),
                new SqlParameter("@Shorthand",Dov.Shorthand),
                new SqlParameter("@Suit",Dov.Suit),
                new SqlParameter("@List",Dov.List),
                new SqlParameter("@SuitScore",Dov.SuitScore),
                new SqlParameter("@ShortHandScore",Dov.ShortHandScore),
                new SqlParameter("@AbilityScore",Dov.AbilityScore),
                new SqlParameter("@TimeScore",Dov.TimeScore),
                new SqlParameter("@LinkUpScore",Dov.LinkUpScore),
                new SqlParameter("@QualityScore",Dov.QualityScore),
                new SqlParameter("@StrainScore",Dov.StrainScore),
                new SqlParameter("@MeetScore",Dov.MeetScore),
                new SqlParameter("@CertificateScore",Dov.CertificateScore),
                new SqlParameter("@Oper",Dov.Oper),
                new SqlParameter("@Opdate",Dov.Opdate),
                new SqlParameter("@Isdel",Dov.Isdel),
                new SqlParameter("@Id",Dov.Id)
            };

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

        /// <summary>
        /// 查找所有数据 - 分页
        /// </summary>
        /// <returns></returns>
        public List<Translator> GetAllTranslator(int pageIndex, out int sumPage, out int totalRecord, string Name, string Language, string List)
        {

            string sqlwhere = "IsDel = 0 and Name like '%" + Name + "%' and Language like '%" + Language + "%'";
            if (List != "全部")
            {
                sqlwhere += " And List like '%" + List + "%'";
            }
            return PageBase<Translator>.excutePageSql(new Translator(), "Translator", "Translator", "*", "id asc", sqlwhere, 20, pageIndex, out sumPage, out totalRecord);
        }


        public Translator getById(int id)
        {
            return excuteType("select * from Translator where Isdel=0 and Id=@id", new SqlParameter("@id", id));
        }
        /// <summary>
        /// 验证数据是否已存在
        /// </summary>
        /// <param name="name"></param>
        /// <param name="Tel"></param>
        /// <returns></returns>
        public Translator getByNameAndTel(string name, string Tel)
        {
            return excuteType("select * from Translator where Isdel=0 and name=" + name + " and Tel=" + Tel);
        }


    }
}