characterdao.cs.svn-base

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

                    sql += "(select sum(Record7Sum) from Character,Personnel,Unit where Character.PersonnelId=Personnel.PersonnelId and Unit.Unitid=Character.CharacterUnitId ";

                    if (perid != 0)
                        sql += " and Personnel.PersonnelId=@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))";
                    sql += ")  as Record7Sum";
                    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));
                    }

                    if (perid != 0)
                        cmd.Parameters.Add(new SqlParameter("@PersonnelId", perid));
                    con.Open();
                    SqlDataReader read = null;
                    try
                    {
                        read = cmd.ExecuteReader();
                    }
                    catch (Exception e)
                    {

                    }

                    while (read.Read())
                    {
                        Character u = new Character();
                        if (read["Positive1"] != DBNull.Value)
                            u.Positive = (int)read["Positive1"];
                        else
                            u.Positive = 0;
                        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["Recognition1"] != DBNull.Value)
                            u.Recognition = (int)read["Recognition1"];
                        else
                            u.Recognition = 0;
                        if (read["Flag1"] != DBNull.Value)
                            u.Flag = (int)read["Flag1"];
                        else
                            u.Flag = 0;
                        if (read["Tablet1"] != DBNull.Value)
                            u.Tablet = (int)read["Tablet1"];
                        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.KouJiang = 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;
                        if (read["Tablet1"] != DBNull.Value)
                            u.Tablet = (int)read["Tablet1"];
                        else
                            u.Tablet = 0;
                        if (read["Other"] != DBNull.Value)
                            u.Other = (int)read["Other"];
                        else
                            u.Other = 0;
                        u.UnitId = unitid;
                        u.PersonnelId = perid;

                        list.Add(u);
                    }
                }
            }
            
            con.Close();
            return list;
        }
    }

    public static Character GetStatisticListByunitid(int ydate1, int mdate1, int ydate2, int mdate2, int unitid, string unitname, int personnelid, string personnelname)
    {
        Character u = new Character();

        using (SqlConnection con = new SqlConnection(constr))
        {
            string sql = "select";
            sql += "(select sum(Positive) from Character,Personnel,Unit where Character.PersonnelId=Personnel.PersonnelId and Unit.Unitid=Character.CharacterUnitId and Character.CharacterUnitId=@Unitid ";
            if (personnelid != 0)
                sql += " and personnel.personnelId=@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))";
            sql += ")  as Positive1";

            sql += ",(select sum(positiveno)  from Character,Personnel,Unit where Character.PersonnelId=Personnel.PersonnelId and Unit.Unitid=Character.CharacterUnitId and Character.CharacterUnitId=@Unitid ";
            if (personnelid != 0)
                sql += " and personnel.personnelId=@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))";
            sql += ") as Positive2 ";

            sql += ",(select sum(Redresist) from Character,Personnel,Unit where Character.PersonnelId=Personnel.PersonnelId and Unit.Unitid=Character.CharacterUnitId and Character.CharacterUnitId=@Unitid ";
            if (personnelid != 0)
                sql += "and personnel.personnelId=@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))";
            sql += ")  as Red1";
            sql += ",(select sum(Red) from Character,Personnel,Unit where Character.PersonnelId=Personnel.PersonnelId and Unit.Unitid=Character.CharacterUnitId and Character.CharacterUnitId=@Unitid ";
            if (personnelid != 0)
                sql += "and personnel.personnelId=@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))";
            sql += ")  as Red2";
            sql += ",(select sum(Recognition) from Character,Personnel,Unit where Character.PersonnelId=Personnel.PersonnelId and Unit.Unitid=Character.CharacterUnitId and Character.CharacterUnitId=@Unitid ";
            if (personnelid != 0)
                sql += "and personnel.personnelId=@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))";
            sql += ")  as Recognition";
            sql += ",(select sum(Flag) from Character,Personnel,Unit where Character.PersonnelId=Personnel.PersonnelId and Unit.Unitid=Character.CharacterUnitId and Character.CharacterUnitId=@Unitid ";
            if (personnelid != 0)
                sql += " and personnel.personnelId=@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))";
            sql += ")  as Flag";
            sql += ",(select sum(Tablet) from Character,Personnel,Unit where Character.PersonnelId=Personnel.PersonnelId and Unit.Unitid=Character.CharacterUnitId and Character.CharacterUnitId=@Unitid ";
            if (personnelid != 0)
                sql += "and personnel.personnelId=@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))";
            sql += ")  as Tablet";

            sql += ",(select sum(Other) from Character,Personnel,Unit where Character.PersonnelId=Personnel.PersonnelId and Unit.Unitid=Character.CharacterUnitId and Character.CharacterUnitId=@Unitid ";
            if (personnelid != 0)
                sql += "and personnel.personnelId=@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))";
            sql += ")  as Other";

            sql += ",(select sum(Complaint) from Character,Personnel,Unit where Character.PersonnelId=Personnel.PersonnelId and Unit.Unitid=Character.CharacterUnitId and Character.CharacterUnitId=@Unitid ";
            if (personnelid != 0)
                sql += " and personnel.personnelId=@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))";
            sql += ")  as Complaint";

            sql += ",(select sum(KouJiang) from Character,Personnel,Unit where Character.PersonnelId=Personnel.PersonnelId and Unit.Unitid=Character.CharacterUnitId and Character.CharacterUnitId=@Unitid ";
            if (personnelid != 0)
                sql += " and personnel.personnelId=@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))";
            sql += ")  as KouJiang";


            sql += ",(select sum(Record1Sum) from Character,Personnel,Unit where Character.PersonnelId=Personnel.PersonnelId and Unit.Unitid=Character.CharacterUnitId ";
            if (unitid != 0)
                sql += " and Character.CharacterUnitId=@UnitId";
            if (personnelid != 0)
                sql += " and Personnel.PersonnelId=@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))";
            sql += ")  as Record1Sum";

            sql += ",(select sum(Record5Sum) from Character,Personnel,Unit where Character.PersonnelId=Personnel.PersonnelId and Unit.Unitid=Character.CharacterUnitId ";
            if (unitid != 0)
                sql += " and Character.CharacterUnitId=@UnitId";
            if (personnelid != 0)
                sql += " and Personnel.PersonnelId=@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))";
            sql += ")  as Record5Sum";

            sql += ",(select sum(Record7Sum) from Character,Personnel,Unit where Character.PersonnelId=Personnel.PersonnelId and Unit.Unitid=Character.CharacterUnitId ";
            if (unitid != 0)
                sql += " and Character.CharacterUnitId=@UnitId";
            if (personnelid != 0)
                sql += " and Personnel.PersonnelId=@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))";
            sql += ")  as Record7Sum";
            SqlCommand cmd = new SqlCommand(sql, con);

            if (unitid != 0)
                cmd.Parameters.Add(new SqlParameter("@Unitid", unitid));
            if (personnelid != 0)
                cmd.Parameters.Add(new SqlParameter("@PersonnelId", personnelid));
            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();
            }