scoredao.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>
/// ScoreDAO 的摘要说明
/// </summary>
/// 

public class ScoreDAO
{
	public ScoreDAO()
	{
		//
		// TODO: 在此处添加构造函数逻辑
		//
	}
    private static string constr = ConfigurationManager.ConnectionStrings["dahuaConnectionString"].ConnectionString;

    //acquire score
    public static List<Score> GetAllScore(int year, bool firstHalf,int PersonnelId)
    {
        List<Score> list = new List<Score>();
        SqlConnection con = new SqlConnection(constr);
        con.Open();
        string sql = "";

        sql+="Select Item.*,ItemContent.* from Item,ItemContent,Personnel WHERE ";
        sql+="Item.ItemType=Personnel.PersonType ";
        sql += "AND Personnel.PersonnelId=@PersonnelId AND ItemContent.ItemId=Item.ItemId ";        
        sql+=" Order By Item.ItemOrder,ItemContent.ItemContentOrder";
        SqlCommand command = new SqlCommand(sql, con);
        command.Parameters.Add(new SqlParameter("@PersonnelId", PersonnelId));
        SqlDataReader reader = command.ExecuteReader();
        while (reader.Read())
        {
            Score score = new Score();
            score.ItemId = (int)reader["ItemId"];
            
            score.ItemContentId = (int)reader["ItemContentId"];
            score.ItenContentScore = (int)reader["ItemContentScore"];
            score.ItemContentName = reader["ItemContentName"].ToString();
            score.PersonnelId = PersonnelId;
            setItemContent(year, score, firstHalf);
            list.Add(score);
        }
        return list;
    }


    private static void setItemContent(int year,Score score, bool firstHalf)
    {
        if (firstHalf == false)
        {
            for (int i = 0; i < 6; i++)
                score.Month[i] = GetScore(year, i + 1,score);
        }
        else
        {
            for (int i = 0; i < 6; i++)
                score.Month[i] = GetScore(year, i + 7,score);
        }

    }

    //获取月份分数
    private static int GetScore(int year, int scoreMonth,Score score)
    {
        int value = 0;
        using (SqlConnection con = new SqlConnection(constr))
        {
            con.Open();
            string sql = "Select * from Score where ScoreYear=@ScoreYear";
            sql += " and ItemContentId=@ItemContentId and ScoreMonth=@ScoreMonth and PersonnelId=@PersonnelId";
            SqlCommand command = new SqlCommand(sql, con);
            command.Parameters.Add(new SqlParameter("@ItemContentId", score.ItemContentId));
            command.Parameters.Add(new SqlParameter("@ScoreMonth", scoreMonth));
            command.Parameters.Add(new SqlParameter("@ScoreYear", year));
            command.Parameters.Add(new SqlParameter("@PersonnelId", score.PersonnelId));
            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                value = (int)reader["Score"];
            }
            return value;
        }
    }
    private static int GetScoreMonth(int scoreYear,int itemContentId,int scoreMonth,SqlConnection connection,SqlTransaction trans)
    {
            int i = 0;
            string sql = "SELECT DISTINCT ScoreMonth from Score where ScoreYear=@ScoreYear and ItemContentId=@itemContentId and ScoreMonth=@ScoreMonth";
            SqlCommand command = new SqlCommand(sql, connection);
            command.Transaction = trans;
            command.Parameters.Add(new SqlParameter("@ScoreYear", scoreYear));
            command.Parameters.Add(new SqlParameter("@ItemContentId", itemContentId));
            command.Parameters.Add(new SqlParameter("@ScoreMonth", scoreMonth));
            SqlDataReader reader=command.ExecuteReader();
            if(reader.Read())
            {
                i++;
            }
            reader.Close();
            return i;
    }
    public static List<Score> GetAllScoreSum(int year,int unitid)
    {
        List<Score> list = new List<Score>();
        using (SqlConnection connection = new SqlConnection(constr))
        {
            connection.Open();
            string sql=null ;
            if (unitid != 0)
            {
                sql = "Select PersonnelId,PersonnelName from Personnel,Unit where personnel.unitid=unit.unitid and Unit.unitid=@Unitid and persontype='护士'";
            }
            else
            {
                sql = "Select PersonnelId,PersonnelName from Personnel where  persontype='护士'";

            }
                SqlCommand command = new SqlCommand(sql, connection);
                if (unitid != 0)
                    command.Parameters.Add(new SqlParameter("@Unitid", unitid));
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Personnel per = new Personnel();
                    per.PersonnelId = (int)reader["PersonnelId"];
                    per.PersonnelName = reader["PersonnelName"].ToString();
                    list.Add(ScoreBL.GetAllScoreSumByPerID(year, per.PersonnelId, per.PersonnelName)); 
                }
            
           
        } return list;
    }
    public static Score GetAllScoreSumByPerID(int year,int per,string name)
    {
        Score score = null;
        using (SqlConnection connection = new SqlConnection(constr))
        {
            connection.Open();
            if (per != 0)
            {
                string sql = "Select ";
                sql += "(select  sum(Score)  from Score where ScoreYear=@ScoreYear and ScoreMonth=1  and score.personnelid=@personnelid) as ScoreSum1,";
                sql += "(select  sum(Score)  from Score where ScoreYear=@ScoreYear and ScoreMonth=2  and score.personnelid=@personnelid) as ScoreSum2,";
                sql += "(select  sum(Score)  from Score where ScoreYear=@ScoreYear and ScoreMonth=3  and score.personnelid=@personnelid) as ScoreSum3,";
                sql += "(select  sum(Score)  from Score where ScoreYear=@ScoreYear and ScoreMonth=4  and score.personnelid=@personnelid) as ScoreSum4,";
                sql += "(select  sum(Score)  from Score where ScoreYear=@ScoreYear and ScoreMonth=5  and score.personnelid=@personnelid) as ScoreSum5,";
                sql += "(select  sum(Score)  from Score where ScoreYear=@ScoreYear and ScoreMonth=6  and score.personnelid=@personnelid) as ScoreSum6,";
                sql += "(select  sum(Score)  from Score where ScoreYear=@ScoreYear and ScoreMonth=7  and score.personnelid=@personnelid) as ScoreSum7,";
                sql += "(select  sum(Score)  from Score where ScoreYear=@ScoreYear and ScoreMonth=8  and score.personnelid=@personnelid) as ScoreSum8,";
                sql += "(select  sum(Score)  from Score where ScoreYear=@ScoreYear and ScoreMonth=9  and score.personnelid=@personnelid) as ScoreSum9,";
                sql += "(select  sum(Score)  from Score where ScoreYear=@ScoreYear and ScoreMonth=10  and score.personnelid=@personnelid) as ScoreSum10,";
                sql += "(select  sum(Score)  from Score where ScoreYear=@ScoreYear and ScoreMonth=11  and score.personnelid=@personnelid) as ScoreSum11,";
                sql += "(select  sum(Score)  from Score where ScoreYear=@ScoreYear and ScoreMonth=12  and score.personnelid=@personnelid) as ScoreSum12";
                SqlCommand command = new SqlCommand(sql, connection);
                command.Parameters.Add(new SqlParameter("@ScoreYear", year));
                command.Parameters.Add(new SqlParameter("@personnelid", per));
                SqlDataReader reader = command.ExecuteReader();