scoredao.cs.svn-base

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

                while (reader.Read())
                {
                    score = new Score();
                    score.PersonnelId = per;
                    score.PersonnelName = name;
                    if (reader["ScoreSum1"] == DBNull.Value)
                        score.MonthSum1 = 0;
                        else
                        score.MonthSum1 = (int)reader["ScoreSum1"];
                    if (reader["ScoreSum2"] == DBNull.Value)
                        score.MonthSum2 = 0;
                        else
                        score.MonthSum2 = (int)reader["ScoreSum2"];
                    if (reader["ScoreSum3"] == DBNull.Value)
                        score.MonthSum3 = 0;
                        else
                        score.MonthSum3 = (int)reader["ScoreSum3"];
                    if (reader["ScoreSum4"] == DBNull.Value)
                        score.MonthSum4 = 0;
                        else
                        score.MonthSum4 = (int)reader["ScoreSum4"];
                    if (reader["ScoreSum5"] == DBNull.Value)
                        score.MonthSum5 = 0;
                        else
                        score.MonthSum5 = (int)reader["ScoreSum5"];
                    if (reader["ScoreSum6"] == DBNull.Value)
                        score.MonthSum6 = 0;
                        else
                        score.MonthSum6 = (int)reader["ScoreSum6"];
                    if (reader["ScoreSum7"] == DBNull.Value)
                        score.MonthSum7 = 0;
                        else
                        score.MonthSum7 = (int)reader["ScoreSum7"];
                    if (reader["ScoreSum8"] == DBNull.Value)
                        score.MonthSum8 = 0;
                        else
                        score.MonthSum8 = (int)reader["ScoreSum8"];
                    if (reader["ScoreSum9"] == DBNull.Value)
                        score.MonthSum9 = 0;
                        else
                        score.MonthSum9 = (int)reader["ScoreSum9"];
                    if (reader["ScoreSum10"] == DBNull.Value)
                        score.MonthSum10 = 0;
                        else
                        score.MonthSum10 = (int)reader["ScoreSum10"];
                    if (reader["ScoreSum11"] == DBNull.Value)
                        score.MonthSum11 = 0;
                        else
                        score.MonthSum11 = (int)reader["ScoreSum11"];
                    if (reader["ScoreSum12"] == DBNull.Value)
                        score.MonthSum12 = 0;
                        else
                        score.MonthSum12 = (int)reader["ScoreSum12"];                                     
                }
            }

        } return score;
    }
    //save score
    public static bool SaveScores(Score[][] scs)
    {
        using (SqlConnection connection = new SqlConnection(constr))
        {
            connection.Open();
            string sql="";
         SqlTransaction trans = connection.BeginTransaction();
            try
            {
              
            for (int i = 0; i < scs.Length; i++)
                {
                    for (int j = 0; j < scs[i].Length; j++)
                    {
                        SqlCommand command = new SqlCommand();
                        command.Connection = connection;
                       
                        command.Transaction = trans;

                        int monthRecord = GetScoreMonth(scs[i][j].ScoreYear, scs[i][j].ItemContentId,scs[i][j].ScoreMonth,connection,trans);
                        if (monthRecord > 0)
                        {
                            sql = "UPDATE Score SET ItemId=@ItemId,ItemContentId=@ItemContentId,";
                            sql += "ScoreMonth=@ScoreMonth,ScoreYear=@ScoreYear,Score=@Score,PersonnelId=@PersonnelId ";
                            sql += "where ItemContentId=@ItemContentId and ScoreYear=@ScoreYear and ScoreMonth=@ScoreMonth";
                        }
                        else
                        {
                            sql = "Insert Score(ItemId,ItemContentId,ScoreMonth,ScoreYear,Score,PersonnelId) ";
                            sql += " values(@ItemId,@ItemContentId,@ScoreMonth,@ScoreYear,@Score,@PersonnelId)";
                        }
                        
                        command.Parameters.Add(new SqlParameter("@ItemId", scs[i][j].ItemId));
                        command.Parameters.Add(new SqlParameter("@ItemContentId", scs[i][j].ItemContentId));
                        command.Parameters.Add(new SqlParameter("@ScoreMonth", scs[i][j].ScoreMonth));
                        command.Parameters.Add(new SqlParameter("@ScoreYear", scs[i][j].ScoreYear));
                        command.Parameters.Add(new SqlParameter("@Score", scs[i][j].Score1));
                        command.Parameters.Add(new SqlParameter("@PersonnelId", scs[i][j].PersonnelId));
                        command.CommandText = sql;
                        command.ExecuteNonQuery();
                    }
                }
                    trans.Commit();
                    return true;
                }
                catch(Exception ex)
                {
                    trans.Rollback();
                    return false;

                 }
                finally
                {
                    connection.Close();
                } 
        }
    }
    public static List<Score> GetAllScoreCom(int y1,int m1,int y2,int m2, int perid)
    {
        List<Score> list = new List<Score>();
        int i = 0;
        using (SqlConnection connection = new SqlConnection(constr))
        {
            connection.Open();
            
            SqlCommand command = null;
            if (perid != 0)
            {
                string sql = "select  sum(Score) as score,ScoreYear,ScoreMonth  from Score where score.personnelid=@personnelid";
                if (y1 != 0 & y2 != 0 & m1 != 0 & m2 != 0)
                    if (y1 == y2 & m1 <= m2)
                        sql += " and ScoreYear=@ydate1 and ScoreMonth>=@Mdate1 and ScoreMonth<=@Mdate2";
                    else
                        sql += " and ((ScoreYear>@ydate1 and ScoreYear<@ydate2) or(ScoreYear=@ydate1 and ScoreMonth>=@Mdate1) or(ScoreYear=@ydate2 and ScoreMonth<=@Mdate2))";    
               
                sql+=" group by ScoreYear,ScoreMonth";                       
                      
                command = new SqlCommand(sql, connection);
                command.Parameters.Add(new SqlParameter("@ydate1", y1));
                command.Parameters.Add(new SqlParameter("@ydate2", y2));
                command.Parameters.Add(new SqlParameter("@Mdate1", m1));
                command.Parameters.Add(new SqlParameter("@Mdate2", m2));
                command.Parameters.Add(new SqlParameter("@personnelid", perid));
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Score score=new Score ();
                    score = new Score();
                    score.PersonnelId = perid;
                    score.ScoreYear =(int)reader ["ScoreYear"];
                    score.ScoreMonth =(int)reader ["ScoreMonth"];
                    if (reader["score"] == DBNull.Value)
                        score.Score1 =0;
                    else
                        score.Score1 = (int)reader["score"];
                    list.Add(score);
                        
                       
                }
            }

        } 
        return list;
   }
               
}