personneldao.cs.svn-base

医院医德医风管理系统 B/S架构

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

using System.Collections.Generic;
using System.Data.Sql;
using System.Data.SqlClient;
/// <summary>
/// PersonnelDAO 的摘要说明
/// </summary>
public class PersonnelDAO
{
	public PersonnelDAO()
	{
		//
		// TODO: 在此处添加构造函数逻辑
		//
	}
    private static string constr = ConfigurationManager.ConnectionStrings["dahuaConnectionString"].ConnectionString;
   
    //
    public static List<Personnel> GetPersonnelListByType(int unitId, string type)
    {
        List<Personnel> list = new List<Personnel>();
        using (SqlConnection connection = new SqlConnection(constr))
        {
            connection.Open();
            string sql = null;
             sql = "select * from Personnel where UnitId=@UnitId and PersonType=@PersonnelType"; 
            SqlCommand command = new SqlCommand(sql, connection);
            command.Parameters.Add(new SqlParameter("@UnitId", unitId));
            command.Parameters.Add(new SqlParameter("@PersonnelType", type));
            
            SqlDataReader reader = command.ExecuteReader();
            
            Personnel personnel1 = new Personnel();
            personnel1.PersonnelId = 0;
            personnel1.PersonnelName = "--选择人员--";
            list.Add(personnel1);
            
            while (reader.Read())
            {
                Personnel personnel = new Personnel();
                personnel.PersonnelId = (int)reader["PersonnelId"];
                personnel.UnitId = (int)reader["UnitId"];
                personnel.PersonnelName = reader["PersonnelName"].ToString();
                personnel.PersonnelType = reader["PersonType"].ToString ();
                list.Add(personnel);
            }
            connection.Close();
            return list;
        }
    }
    public static List<Personnel> GetPersonnelListByUnitId(int unitId)
    {
        using (SqlConnection connection = new SqlConnection(constr))
        {
            connection.Open();
            List<Personnel> list = new List<Personnel>();
            string sql = "select Personnel.*,Unit.UnitName from Personnel,Unit where Personnel.UnitId=@UnitId and Personnel.UnitId=Unit.UnitId";
            SqlCommand command = new SqlCommand(sql, connection);
            command.Parameters.Add(new SqlParameter("@UnitId", unitId));
            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                Personnel personnel = new Personnel();
                Set(personnel, reader);
                list.Add(personnel);
            }
            return list;
        }
    }
    public static List<Personnel> GetPersonnelList(int unitId)
    {
        List<Personnel> list = new List<Personnel>();
        using (SqlConnection connection = new SqlConnection(constr))
        {
            connection.Open();
            
            string sql = null;
            if (unitId == 0)
            {  sql = "select * from Personnel"; }
            else
            {  sql = "select * from Personnel where UnitId=@UnitId"; }
            SqlCommand command = new SqlCommand(sql, connection);
            if (unitId != 0)
            {command.Parameters.Add(new SqlParameter("@UnitId", unitId));}
            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                Personnel personnel = new Personnel();
                personnel.PersonnelId = (int)reader["PersonnelId"];
                personnel.UnitId = (int)reader["UnitId"];
                personnel.PersonnelName = reader["PersonnelName"].ToString();
                personnel.PersonnelType = reader["PersonType"].ToString();
                list.Add(personnel);
            }
            connection.Close();
            return list;
        }
    }
    public static List<Personnel> GetPersonnelListA(int unitId)
    {
        List<Personnel> list = new List<Personnel>();
        using (SqlConnection connection = new SqlConnection(constr))
        {
            connection.Open();

            string sql = null;
            if (unitId == 0)
            { sql = "select * from Personnel"; }
            else
            { sql = "select * from Personnel where UnitId=@UnitId"; }
            SqlCommand command = new SqlCommand(sql, connection);
            if (unitId != 0)
            { command.Parameters.Add(new SqlParameter("@UnitId", unitId)); }
            SqlDataReader reader = command.ExecuteReader();
            Personnel p = new Personnel();
            p.PersonnelId = 0;
            p.PersonnelName = "所有人员";
            list.Add(p);
            Personnel pb = new Personnel();
            pb.PersonnelId = -1;
            pb.PersonnelName = "集体记录";
            list.Add(pb);
            while (reader.Read())
            {
                Personnel personnel = new Personnel();
                personnel.PersonnelId = (int)reader["PersonnelId"];
                personnel.UnitId = (int)reader["UnitId"];
                personnel.PersonnelName = reader["PersonnelName"].ToString();
                list.Add(personnel);
            }
            connection.Close();
            return list;
        }
    }
    public static List<Personnel> GetPersonnelListB(int unitId)
    {
        List<Personnel> list = new List<Personnel>();
        using (SqlConnection connection = new SqlConnection(constr))
        {
            connection.Open();

            string sql = null;
            if (unitId == 0)
            { sql = "select * from Personnel"; }
            else
            { sql = "select * from Personnel where UnitId=@UnitId"; }
            SqlCommand command = new SqlCommand(sql, connection);
            if (unitId != 0)
            { command.Parameters.Add(new SqlParameter("@UnitId", unitId)); }
            SqlDataReader reader = command.ExecuteReader();
            Personnel p = new Personnel();
            p.PersonnelId = 0;
            p.PersonnelName = "所有人员";
            list.Add(p);

            while (reader.Read())
            {
                Personnel personnel = new Personnel();
                personnel.PersonnelId = (int)reader["PersonnelId"];
                personnel.UnitId = (int)reader["UnitId"];
                personnel.PersonnelName = reader["PersonnelName"].ToString();
                list.Add(personnel);
            }
            connection.Close();
            return list;
        }
    }

    //详细
    public static Personnel GetPersonnelListById(int personnelId)
    {
        using (SqlConnection connection = new SqlConnection(constr))
        {
            connection.Open();
            Personnel personnel = new Personnel();
            string sql = "select Personnel.*,Unit.UnitName from Personnel,Unit where Personnel.PersonnelId=@PersonnelId and Personnel.UnitId=Unit.UnitId";
            SqlCommand command = new SqlCommand(sql, connection);
            command.Parameters.Add(new SqlParameter("@PersonnelId", personnelId));
            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                Set(personnel, reader);
                personnel.UnitStr = reader["UnitName"].ToString();
            }
            return personnel;
        }
    }
    //添加
    public static bool AddPersonnel(Personnel personnel)
    {
        using (SqlConnection connection = new SqlConnection(constr))
        {
            connection.Open();
            string sql = "insert into Personnel(UnitId,ExamineId,PersonnelName,Sex,";
            if (personnel.BornDate != DateTime.MinValue)            
                sql+="BornDate,";
            sql+="PersonType) values(@UnitId,@ExamineId,@PersonnelName,@Sex,";
            if (personnel.BornDate != DateTime.MinValue) 
                sql += "@BornDate,";
            sql+="@PersonType)";
            SqlCommand command = new SqlCommand(sql, connection);
            command.Parameters.Add(new SqlParameter("UnitId",personnel.UnitId));
            command.Parameters.Add(new SqlParameter("ExamineId",personnel.ExamineId));
            command.Parameters.Add(new SqlParameter("PersonnelName",personnel.PersonnelName));
            command.Parameters.Add(new SqlParameter("Sex",personnel.Sex));
            if  (personnel .BornDate !=DateTime .MinValue )
            command.Parameters.Add(new SqlParameter("BornDate", personnel.BornDate));
            command.Parameters.Add(new SqlParameter("PersonType", personnel.PersonnelType));
            int i;
            try
            {
                 i= command.ExecuteNonQuery();
                 if (i > 0) return true;
                 else return false;
            }
            catch (Exception e)
            {
                return false;
            }
            
        }
    }
    //更新
    public static bool UpdatePersonnel(Personnel personnel)
    {
        using (SqlConnection connection = new SqlConnection(constr))
        {
            connection.Open();
            string sql = "update Personnel set UnitId=@UnitId,ExamineId=@ExamineId,PersonnelName=@PersonnelName,Sex=@Sex,";
            if(personnel .BornDate !=DateTime .MinValue )
                sql+="BornDate=@BornDate,";
            sql+="PersonType=@PersonType where PersonnelId=@PersonnelId";
            SqlCommand command = new SqlCommand(sql, connection);
            command.Parameters.Add(new SqlParameter("@PersonnelId", personnel.PersonnelId));
            command.Parameters.Add(new SqlParameter("@UnitId", personnel.UnitId));
            command.Parameters.Add(new SqlParameter("@ExamineId", personnel.ExamineId));
            command.Parameters.Add(new SqlParameter("@PersonnelName", personnel.PersonnelName));
            command.Parameters.Add(new SqlParameter("@Sex", personnel.Sex));
            if(personnel .BornDate !=DateTime .MinValue )
                command.Parameters.Add(new SqlParameter("@BornDate", personnel.BornDate));
            command.Parameters.Add(new SqlParameter("@PersonType", personnel.PersonnelType));
            int i = command.ExecuteNonQuery();
            if (i > 0) return true;
            else return false;
        }
    }
    public static bool DelPersonnel(int personnelId)
    {
        using (SqlConnection connection = new SqlConnection(constr))
        {
            connection.Open();
            string sql = "delete from Personnel where PersonnelId=@PersonnelId";
            SqlCommand command = new SqlCommand(sql, connection);
            command.Parameters.Add(new SqlParameter("@PersonnelId", personnelId));
            int i = command.ExecuteNonQuery();
            if (i > 0) return true;
            else return false;
            
        }
    }
    private static void Set(Personnel personnel,SqlDataReader reader)
        {
            if (personnel.PersonnelId != null)
            personnel.PersonnelId=(int)reader["PersonnelId"];
            personnel.UnitId=(int)reader["UnitId"];
            personnel.UnitStr = reader["UnitName"].ToString();
            personnel.ExamineId=(int)reader["ExamineId"];
            //personnel.OfficeHolderId=(int)reader["OfficeHolderId"];
            personnel.PersonnelType = reader["PersonType"].ToString ();
            personnel.PersonnelName = reader["PersonnelName"].ToString();
            personnel.Sex=(int)reader["Sex"];
            if (personnel.Sex == 0) personnel.SexStr = "男"; 
                else personnel.SexStr = "女";
            if (reader["BornDate"]!=DBNull.Value)
                personnel.BornDate =Convert .ToDateTime( reader["BornDate"]);
        }
    public static List<Personnel> GetPersonnelListByYM(int Ydate,int Mdate)
    {
        using (SqlConnection connection = new SqlConnection(constr))
        {
            connection.Open();
            List<Personnel> list = new List<Personnel>();
            
            string sql = "SELECT * from Personnel,Character  where Personnel.PersonnelId=Character.PersonnelId and Ydate=@Ydate and Mdate =@Mdate";
            SqlCommand cmd = new SqlCommand(sql, connection);
            cmd.Parameters.Add(new SqlParameter("@Ydate", Ydate));
            cmd.Parameters.Add(new SqlParameter("@Mdate", Mdate));
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                Personnel personnel = new Personnel();
                personnel.PersonnelId = (int)reader["PersonnelId"];
                list.Add(personnel);
            }
            return list;
        }
    }
    public static List<Unit> GetPersonnelListKSByYM(int Ydate, int Mdate)
    {
        using (SqlConnection connection = new SqlConnection(constr))
        {
            connection.Open();
            List<Unit> list = new List<Unit>();
            //string sql = "SELECT * from character where (Character.PersonnelId in (select PersonnelId from Personnel) or Character.PersonnelId=-1) and Ydate=@Ydate and Mdate =@Mdate ";
            string sql = "SELECT * from Character  where Character.PersonnelId=-1 and Ydate=@Ydate and Mdate =@Mdate";
            SqlCommand cmd = new SqlCommand(sql, connection);
            cmd.Parameters.Add(new SqlParameter("@Ydate", Ydate));
            cmd.Parameters.Add(new SqlParameter("@Mdate", Mdate));
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                Unit u = new Unit();
                u.UnitId =(int) reader["CharacterUnitId"];
                list.Add(u);
            }
            return list;
        }
    }
   
}