characterdao.cs.svn-base

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

            while (read.Read())
            {
                Character u = new Character();
                if (perid != -1)
                {
                    u.PersonnelName = read["PersonnelName"].ToString();
                }
                else
                {
                    u.PersonnelName = "集体记录";
                }
                u.UnitName = read["UnitName"].ToString();
                SetCharacter(u, read);
                list.Add(u);
            }
            read.Close();
            con.Close();


        }
        return list;

    }
    public static Character GetCharacterTotal(int ydate1, int mdate1, int ydate2, int mdate2,int perid)
    {
        Character u = new Character();
        using (SqlConnection con = new SqlConnection(constr))
        {
            string sql = null;
            if (perid == -1)
            {
                sql = "select sum(Record1Sum) as Record1Sum,sum(Positiveno) as Positive2,sum(Recognition) as Recognition,sum(Flag) as Flag,sum(Tablet) as Tablet,sum(Other) as Other,sum(Complaint) as Complaint,sum(KouJiang) as KouJiang,sum(Record5Sum) as Record5Sum,sum(Redresist) as Red1,sum(Red) as Red2,sum(Record7Sum) as Record7Sum from character ";
                sql += " where Character.PersonnelId=-1";
            }
            else
            {
                sql = "select sum(Record1Sum) as Record1Sum,sum(Positiveno) as Positive2,sum(Recognition) as Recognition,sum(Flag) as Flag,sum(Tablet) as Tablet,sum(Other) as Other,sum(Complaint) as Complaint,sum(KouJiang) as KouJiang,sum(Record5Sum) as Record5Sum,sum(Redresist) as Red1,sum(Red) as Red2,sum(Record7Sum) as Record7Sum from character,Personnel,Unit ";
                sql += " where Unit.Unitid=Character.CharacterUnitId and Character.PersonnelId=Personnel.PersonnelId ";
            }
            
            if (ydate1 != 0 & ydate2 != 0 & mdate1 != 0 & mdate2 != 0)
                if (ydate1 == ydate2 & mdate1 <= mdate2)
                    sql += " and (ydate=@ydate1 and mdate>=@Mdate1 and mdate<=@Mdate2)";
                else
                    sql += " and ((ydate>@ydate1 and ydate<@ydate2) or(ydate=@ydate1 and mdate>=@Mdate1) or(ydate=@ydate2 and mdate<=@Mdate2))";

            SqlCommand cmd = new SqlCommand(sql, con);
            if (ydate1 != 0 & ydate2 != 0)
            {
                cmd.Parameters.Add(new SqlParameter("@ydate1", ydate1));
                cmd.Parameters.Add(new SqlParameter("@Mdate1", mdate1));
                cmd.Parameters.Add(new SqlParameter("@ydate2", ydate2));
                cmd.Parameters.Add(new SqlParameter("@Mdate2", mdate2));
            }
            con.Open();
            SqlDataReader read = null;
            try
            {
                read = cmd.ExecuteReader();
            }
            catch (Exception e)
            { }
            while (read.Read())
            {
                if (read["Positive2"] != DBNull.Value)
                    u.Positiveno = (int)read["Positive2"];
                else
                    u.Positiveno = 0;
                if (read["Red1"] != DBNull.Value)
                    u.Redresist = (int)read["Red1"];
                else
                    u.Redresist = 0;
                if (read["Red2"] != DBNull.Value)
                    u.Red = (int)read["Red2"];
                else
                    u.Red = 0;
                if (read["Recognition"] != DBNull.Value)
                    u.Recognition = (int)read["Recognition"];
                else
                    u.Recognition = 0;
                if (read["Flag"] != DBNull.Value)
                    u.Flag = (int)read["Flag"];
                else
                    u.Flag = 0;
                if (read["Tablet"] != DBNull.Value)
                    u.Tablet = (int)read["Tablet"];
                else
                    u.Tablet = 0;
                if (read["Other"] != DBNull.Value)
                    u.Other = (int)read["Other"];
                else
                    u.Other = 0;
                if (read["Complaint"] != DBNull.Value)
                    u.Complaint = (int)read["Complaint"];
                else
                    u.Complaint = 0;
                if (read["KouJiang"] != DBNull.Value)
                    u.KouJiang = (int)read["KouJiang"];
                else
                    u.Complaint = 0;
                if (read["Record1Sum"] != DBNull.Value)
                    u.Record1Sum = (int)read["Record1Sum"];
                else
                    u.Record1Sum = 0;
                if (read["Record5Sum"] != DBNull.Value)
                    u.Record5Sum = (int)read["Record5Sum"];
                else
                    u.Record5Sum = 0;
                if (read["Record7Sum"] != DBNull.Value)
                    u.Record7Sum = (int)read["Record7Sum"];
                else
                    u.Record7Sum = 0;
                u.UnitName = "合计";


            }
            read.Close();
            con.Close();
        }
        return u;
    }
    public static List<Character> GetCharacterListDepart(int ydate, int mdate, int unitid)
    {
        List<Character> list = new List<Character>();
        using (SqlConnection con = new SqlConnection(constr))
        {
            string sql = "select Character.*,unit.* from Character,unit where Unit.UnitId=Character.CharacterUnitId and Character.Ydate=@Ydate and Character.Mdate=@Mdate ";
            sql += "and Character.PersonnelId=-1";

            if (unitid != 0)
                sql += "and Character.CharacterUnitId=@CharacterUnitId ";
            SqlCommand cmd = new SqlCommand(sql, con);
            cmd.Parameters.Add(new SqlParameter("@Ydate", ydate));
            cmd.Parameters.Add(new SqlParameter("@Mdate", mdate));
            if (unitid != 0)
                cmd.Parameters.Add(new SqlParameter("@CharacterUnitId", unitid));
            con.Open();
            SqlDataReader read = null;
            try
            {
                read = cmd.ExecuteReader();
            }
            catch (Exception e)
            {

            }
            while (read.Read())
            {
                Character u = new Character();
                SetCharacter(u, read);

                u.UnitName = read["UnitName"].ToString();
                u.PersonnelName = u.UnitName + "部 集体记录";
                list.Add(u);

            }
            read.Close();
            con.Close();
        }
        return list;
    }
    public static Character GetCharacterListDepartSum(int ydate1, int mdate1, int ydate2, int mdate2, int unitid, string unitname)
    {
        Character u = new Character();
        using (SqlConnection con = new SqlConnection(constr))
        {
            string sql = "select";
            sql += "(select sum(Positive) from character where personnelid=-1 and CharacterUnitId=@Unitid ";
            if (ydate1 != 0 & ydate2 != 0 & mdate1 != 0 & mdate2 != 0)
                if (ydate1 == ydate2 & mdate1 <= mdate2)
                    sql += " and ydate=@ydate1 and mdate>=@Mdate1 and mdate<=@Mdate2";
                else
                    sql += " and ((ydate>@ydate1 and ydate<@ydate2) or(ydate=@ydate1 and mdate>=@Mdate1) or(ydate=@ydate2 and mdate<=@Mdate2))";
            sql += ")  as Positive1";

            sql += ",(select sum(positiveno) from character where personnelid=-1 and CharacterUnitId=@Unitid ";

            if (ydate1 != 0 & ydate2 != 0 & mdate1 != 0 & mdate2 != 0)
                if (ydate1 == ydate2 & mdate1 <= mdate2)
                    sql += " and ydate=@ydate1 and mdate>=@Mdate1 and mdate<=@Mdate2";
                else
                    sql += " and ((ydate>@ydate1 and ydate<@ydate2) or(ydate=@ydate1 and mdate>=@Mdate1) or(ydate=@ydate2 and mdate<=@Mdate2))";
            sql += ") as Positive2 ";

            sql += ",(select sum(Redresist) from character where personnelid=-1 and CharacterUnitId=@Unitid ";
            
            if (ydate1 != 0 & ydate2 != 0 & mdate1 != 0 & mdate2 != 0)
                if (ydate1 == ydate2 & mdate1 <= mdate2)
                    sql += " and ydate=@ydate1 and mdate>=@Mdate1 and mdate<=@Mdate2";
                else
                    sql += " and ((ydate>@ydate1 and ydate<@ydate2) or(ydate=@ydate1 and mdate>=@Mdate1) or(ydate=@ydate2 and mdate<=@Mdate2))";
            sql += ")  as Red1";

            sql += ",(select sum(Red) from character where personnelid=-1 and CharacterUnitId=@Unitid ";
            
            if (ydate1 != 0 & ydate2 != 0 & mdate1 != 0 & mdate2 != 0)
                if (ydate1 == ydate2 & mdate1 <= mdate2)
                    sql += " and ydate=@ydate1 and mdate>=@Mdate1 and mdate<=@Mdate2";
                else
                    sql += " and ((ydate>@ydate1 and ydate<@ydate2) or(ydate=@ydate1 and mdate>=@Mdate1) or(ydate=@ydate2 and mdate<=@Mdate2))";
            sql += ")  as Red2";

            sql += ",(select sum(Recognition) from character where personnelid=-1 and CharacterUnitId=@Unitid ";
            
            if (ydate1 != 0 & ydate2 != 0 & mdate1 != 0 & mdate2 != 0)
                if (ydate1 == ydate2 & mdate1 <= mdate2)
                    sql += " and ydate=@ydate1 and mdate>=@Mdate1 and mdate<=@Mdate2";
                else
                    sql += " and ((ydate>@ydate1 and ydate<@ydate2) or(ydate=@ydate1 and mdate>=@Mdate1) or(ydate=@ydate2 and mdate<=@Mdate2))";
            sql += ")  as Recognition";

            sql += ",(select sum(Flag) from character where personnelid=-1 and CharacterUnitId=@Unitid ";
            
            if (ydate1 != 0 & ydate2 != 0 & mdate1 != 0 & mdate2 != 0)
                if (ydate1 == ydate2 & mdate1 <= mdate2)
                    sql += " and ydate=@ydate1 and mdate>=@Mdate1 and mdate<=@Mdate2";
                else
                    sql += " and ((ydate>@ydate1 and ydate<@ydate2) or(ydate=@ydate1 and mdate>=@Mdate1) or(ydate=@ydate2 and mdate<=@Mdate2))";
            sql += ")  as Flag";

            sql += ",(select sum(Tablet) from character where personnelid=-1 and CharacterUnitId=@Unitid ";
           
            if (ydate1 != 0 & ydate2 != 0 & mdate1 != 0 & mdate2 != 0)
                if (ydate1 == ydate2 & mdate1 <= mdate2)
                    sql += " and ydate=@ydate1 and mdate>=@Mdate1 and mdate<=@Mdate2";
                else
                    sql += " and ((ydate>@ydate1 and ydate<@ydate2) or(ydate=@ydate1 and mdate>=@Mdate1) or(ydate=@ydate2 and mdate<=@Mdate2))";
            sql += ")  as Tablet";

            sql += ",(select sum(Other) from character where personnelid=-1 and CharacterUnitId=@Unitid ";
            
            if (ydate1 != 0 & ydate2 != 0 & mdate1 != 0 & mdate2 != 0)
                if (ydate1 == ydate2 & mdate1 <= mdate2)
                    sql += " and ydate=@ydate1 and mdate>=@Mdate1 and mdate<=@Mdate2";
                else
                    sql += " and ((ydate>@ydate1 and ydate<@ydate2) or(ydate=@ydate1 and mdate>=@Mdate1) or(ydate=@ydate2 and mdate<=@Mdate2))";
            sql += ")  as Other";

            sql += ",(select sum(Complaint) from character where personnelid=-1 and CharacterUnitId=@Unitid ";
           
            if (ydate1 != 0 & ydate2 != 0 & mdate1 != 0 & mdate2 != 0)
                if (ydate1 == ydate2 & mdate1 <= mdate2)
                    sql += " and ydate=@ydate1 and mdate>=@Mdate1 and mdate<=@Mdate2";
                else
                    sql += " and ((ydate>@ydate1 and ydate<@ydate2) or(ydate=@ydate1 and mdate>=@Mdate1) or(ydate=@ydate2 and mdate<=@Mdate2))";
            sql += ")  as Complaint";

            sql += ",(select sum(KouJiang) from character where personnelid=-1 and CharacterUnitId=@Unitid ";
            
            if (ydate1 != 0 & ydate2 != 0 & mdate1 != 0 & mdate2 != 0)
                if (ydate1 == ydate2 & mdate1 <= mdate2)
                    sql += " and ydate=@ydate1 and mdate>=@Mdate1 and mdate<=@Mdate2";
                else
                    sql += " and ((ydate>@ydate1 and ydate<@ydate2) or(ydate=@ydate1 and mdate>=@Mdate1) or(ydate=@ydate2 and mdate<=@Mdate2))";
            sql += ")  as KouJiang";


            sql += ",(select sum(Record1Sum) from character where personnelid=-1 and CharacterUnitId=@Unitid ";
           
            if (ydate1 != 0 & ydate2 != 0 & mdate1 != 0 & mdate2 != 0)
                if (ydate1 == ydate2 & mdate1 <= mdate2)
                    sql += " and ydate=@ydate1 and mdate>=@Mdate1 and mdate<=@Mdate2";
                else
                    sql += " and ((ydate>