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

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

        //增Add
        public bool Add(VisaCustomerCompany Dov)
        {
            string sql = "insert into VisaCustomerCompany values(@DCid,@Company,@CompanyAddress,@Phone,@Job,@LeaderName,@WorkStart,@WorkEnd,@WorkState);SELECT @@IDENTITY";

            SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@DCid",Dov.DCid),
                new SqlParameter("@Company",Dov.Company),
                new SqlParameter("@CompanyAddress",Dov.CompanyAddress),
                new SqlParameter("@Phone",Dov.Phone),
                new SqlParameter("@Job",Dov.Job),
                new SqlParameter("@LeaderName",Dov.LeaderName),
                new SqlParameter("@WorkStart",Dov.WorkStart),
                new SqlParameter("@WorkEnd",Dov.WorkEnd),
                new SqlParameter("@WorkState",Dov.WorkState)
            };

            if (SqlHelper.ExecuteNonQuery(sql, CommandType.Text, parameter) > 0)
                return true;
            return false;
        }
        //改Update
        public bool Edit(VisaCustomerCompany Dov)
        {
            string sql = "update VisaCustomerCompany set DCid=@DCid,Company=@Company,CompanyAddress=@CompanyAddress,Phone=@Phone,Job=@Job,LeaderName=@LeaderName,WorkStart=@WorkStart,WorkEnd=@WorkEnd,WorkState=@WorkState where Id = @Id";

            SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@DCid",Dov.DCid),
                new SqlParameter("@Company",Dov.Company),
                new SqlParameter("@CompanyAddress",Dov.CompanyAddress),
                new SqlParameter("@Phone",Dov.Phone),
                new SqlParameter("@Job",Dov.Job),
                new SqlParameter("@LeaderName",Dov.LeaderName),
                new SqlParameter("@WorkStart",Dov.WorkStart),
                new SqlParameter("@WorkEnd",Dov.WorkEnd),
                new SqlParameter("@WorkState",Dov.WorkState),
                new SqlParameter("@Id",Dov.Id)
            };

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


        public bool del(int DCID)
        {
            if (SqlHelper.ExecuteNonQuery("delete from VisaCustomerCompany where DCid=@DCId", CommandType.Text, new SqlParameter("@DCId", DCID)) > 0)
                return true;
            return false;

        }


        public List<VisaCustomerCompany> GetAllByDCID(int DCID)
        {
            return excuteSql("select * from VisaCustomerCompany where DCId=@DCId order by Id", new SqlParameter("@DCId", DCID));
        }

        public VisaCustomerCompany GetByID(int id)
        {
            return excuteType("select * from VisaCustomerCompany where Id=@Id", new SqlParameter("@Id", id));
        }


    }
}