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

using Model;

namespace DAL
{
    /*教师信息业务逻辑层实现*/
    public class dalTeacher_QQ287307421
    {
        /*待执行的sql语句*/
        public static string sql = "";

        /*添加教师信息实现*/
        public static bool AddTeacher_QQ287307421(Teacher_QQ287307421 teacher_QQ287307421)
        {
            string sql = "insert into Teacher_QQ287307421(teacherNumber,teacherName,teacherPassword,teacherSex,teacherBirthday,teacherArriveDate,teacherCardNumber,teacherPhone,teacherPhoto,teacherAddress,teacherMemo) values(@teacherNumber,@teacherName,@teacherPassword,@teacherSex,@teacherBirthday,@teacherArriveDate,@teacherCardNumber,@teacherPhone,@teacherPhoto,@teacherAddress,@teacherMemo)";
            /*构建sql参数*/
            SqlParameter[] parm = new SqlParameter[] {
             new SqlParameter("@teacherNumber",SqlDbType.VarChar),
             new SqlParameter("@teacherName",SqlDbType.VarChar),
             new SqlParameter("@teacherPassword",SqlDbType.VarChar),
             new SqlParameter("@teacherSex",SqlDbType.VarChar),
             new SqlParameter("@teacherBirthday",SqlDbType.DateTime),
             new SqlParameter("@teacherArriveDate",SqlDbType.DateTime),
             new SqlParameter("@teacherCardNumber",SqlDbType.VarChar),
             new SqlParameter("@teacherPhone",SqlDbType.VarChar),
             new SqlParameter("@teacherPhoto",SqlDbType.Image),
             new SqlParameter("@teacherAddress",SqlDbType.VarChar),
             new SqlParameter("@teacherMemo",SqlDbType.VarChar)
            };
            /*给参数赋值*/
            parm[0].Value = teacher_QQ287307421.teacherNumber; //教师编号
            parm[1].Value = teacher_QQ287307421.teacherName; //教师姓名
            parm[2].Value = teacher_QQ287307421.teacherPassword; //登录密码
            parm[3].Value = teacher_QQ287307421.teacherSex; //性别
            parm[4].Value = teacher_QQ287307421.teacherBirthday; //出生日期
            parm[5].Value = teacher_QQ287307421.teacherArriveDate; //入职日期
            parm[6].Value = teacher_QQ287307421.teacherCardNumber; //身份证号
            parm[7].Value = teacher_QQ287307421.teacherPhone; //联系电话
            parm[8].Value = teacher_QQ287307421.teacherPhoto; //教师照片
            parm[9].Value = teacher_QQ287307421.teacherAddress; //家庭地址
            parm[10].Value = teacher_QQ287307421.teacherMemo; //附加信息

            /*执行sql进行添加*/
            return (DBHelp.ExecuteNonQuery(sql, parm) > 0) ? true : false;
        }

        /*根据teacherNumber获取某条教师信息记录*/
        public static Teacher_QQ287307421 getSomeTeacher_QQ287307421(string teacherNumber)
        {
            /*构建查询sql*/
            string sql = "select * from Teacher_QQ287307421 where teacherNumber='" + teacherNumber + "'"; 
            SqlDataReader DataRead = DBHelp.ExecuteReader(sql, null);
            Teacher_QQ287307421 teacher_QQ287307421 = new Teacher_QQ287307421();
            /*如果查询存在记录，就包装到对象中返回*/
            if (DataRead.Read())
            {
                teacher_QQ287307421.teacherNumber = DataRead["teacherNumber"].ToString();
                teacher_QQ287307421.teacherName = DataRead["teacherName"].ToString();
                teacher_QQ287307421.teacherPassword = DataRead["teacherPassword"].ToString();
                teacher_QQ287307421.teacherSex = DataRead["teacherSex"].ToString();
                teacher_QQ287307421.teacherBirthday = Convert.ToDateTime(DataRead["teacherBirthday"].ToString());
                teacher_QQ287307421.teacherArriveDate = Convert.ToDateTime(DataRead["teacherArriveDate"].ToString());
                teacher_QQ287307421.teacherCardNumber = DataRead["teacherCardNumber"].ToString();
                teacher_QQ287307421.teacherPhone = DataRead["teacherPhone"].ToString();
                teacher_QQ287307421.teacherPhoto = (byte[])DataRead["teacherPhoto"];
                teacher_QQ287307421.teacherAddress = DataRead["teacherAddress"].ToString();
                teacher_QQ287307421.teacherMemo = DataRead["teacherMemo"].ToString();
            }
            DataRead.Close();
            return teacher_QQ287307421;
        }

        /*更新教师信息实现*/
        public static bool EditTeacher_QQ287307421(Teacher_QQ287307421 teacher_QQ287307421)
        {
            string sql = "update Teacher_QQ287307421 set teacherName=@teacherName,teacherPassword=@teacherPassword,teacherSex=@teacherSex,teacherBirthday=@teacherBirthday,teacherArriveDate=@teacherArriveDate,teacherCardNumber=@teacherCardNumber,teacherPhone=@teacherPhone,teacherPhoto=@teacherPhoto,teacherAddress=@teacherAddress,teacherMemo=@teacherMemo where teacherNumber=@teacherNumber";
            /*构建sql参数信息*/
            SqlParameter[] parm = new SqlParameter[] {
             new SqlParameter("@teacherName",SqlDbType.VarChar),
             new SqlParameter("@teacherPassword",SqlDbType.VarChar),
             new SqlParameter("@teacherSex",SqlDbType.VarChar),
             new SqlParameter("@teacherBirthday",SqlDbType.DateTime),
             new SqlParameter("@teacherArriveDate",SqlDbType.DateTime),
             new SqlParameter("@teacherCardNumber",SqlDbType.VarChar),
             new SqlParameter("@teacherPhone",SqlDbType.VarChar),
             new SqlParameter("@teacherPhoto",SqlDbType.Image),
             new SqlParameter("@teacherAddress",SqlDbType.VarChar),
             new SqlParameter("@teacherMemo",SqlDbType.VarChar),
             new SqlParameter("@teacherNumber",SqlDbType.VarChar)
            };
            /*为参数赋值*/
            parm[0].Value = teacher_QQ287307421.teacherName;
            parm[1].Value = teacher_QQ287307421.teacherPassword;
            parm[2].Value = teacher_QQ287307421.teacherSex;
            parm[3].Value = teacher_QQ287307421.teacherBirthday;
            parm[4].Value = teacher_QQ287307421.teacherArriveDate;
            parm[5].Value = teacher_QQ287307421.teacherCardNumber;
            parm[6].Value = teacher_QQ287307421.teacherPhone;
            parm[7].Value = teacher_QQ287307421.teacherPhoto;
            parm[8].Value = teacher_QQ287307421.teacherAddress;
            parm[9].Value = teacher_QQ287307421.teacherMemo;
            parm[10].Value = teacher_QQ287307421.teacherNumber;
            /*执行更新*/
            return (DBHelp.ExecuteNonQuery(sql, parm) > 0) ? true : false;
        }


        /*删除教师信息*/
        public static bool DelTeacher_QQ287307421(string p)
        {
            string sql = "";
            string[] ids = p.Split(',');
            for(int i=0;i<ids.Length;i++)
            {
                if(i != ids.Length-1)
                  sql += "'" + ids[i] + "',";
                else
                  sql += "'" + ids[i] + "'";
            }
            sql = "delete from Teacher_QQ287307421 where teacherNumber in (" + sql + ")";
            return ((DBHelp.ExecuteNonQuery(sql, null)) > 0) ? true : false;
        }


        /*查询教师信息*/
        public static System.Data.DataTable GetTeacher_QQ287307421(int PageIndex, int PageSize, out int PageCount, out int RecordCount, string strWhere)
        {
            try
            {
                string strSql = " select Teacher_QQ287307421.* from Teacher_QQ287307421 where 1=1";
                string strShow = "teacherNumber as 教师编号,teacherName as 教师姓名,teacherSex as 性别,convert(char(11),teacherBirthday,20) as 出生日期,convert(char(11),teacherArriveDate,20) as 入职日期,teacherPhone as 联系电话,teacherPhoto as 教师照片";
                return DAL.DBHelp.ExecutePagerWhenPrimaryIsString(PageIndex, PageSize, "teacherNumber", strShow, strSql, strWhere, " teacherNumber asc ", out PageCount, out RecordCount);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        public static DataSet getAllTeacher_QQ287307421()
        {
            try
            {
                string strSql = "select * from Teacher_QQ287307421";
                return DBHelp.ExecuteDataSet(strSql, null);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


    }
}
