📄 characterdao.cs.svn-base
字号:
using (SqlConnection con = new SqlConnection(constr))
{
con.Open();
string sql = "update Character set ";
if (u.UnitId != 0)
sql += " CharacterUnitId=@CharacterUnitId, ";
sql += " PersonnelId=@PersonnelId,Record1=@Record1,Record2=@Record2,Record3=@Record3,Record4=@Record4,Record5=@Record5,Record6=@Record6,Record7=@Record7,Record8=@Record8,Record1Sum=@Record1Sum,Record5Sum=@Record5Sum,Record7Sum=@Record7Sum,Positive=@Positive,Positiveno=@Positiveno,Redresist=@Redresist,Red=@Red,Recognition=@Recognition,Flag=@Flag,Tablet=@Tablet,Other=@Other,Complaint=@Complaint,Mdate=@Mdate,Ydate=@Ydate,CreateUserId=@CreateUserId,KouJiang=@KouJiang";
sql += " where CharacterId=@CharacterId";
SqlCommand cmd = new SqlCommand(sql, con);
if (u.UnitId != 0)
cmd.Parameters.Add(new SqlParameter("@CharacterUnitId", u.UnitId));
cmd.Parameters.Add(new SqlParameter("@CharacterId", u.CharacterId));
cmd.Parameters.Add(new SqlParameter("@PersonnelId", u.PersonnelId));
cmd.Parameters.Add(new SqlParameter("@Record1", u.Record1));
cmd.Parameters.Add(new SqlParameter("@Record2", u.Record2));
cmd.Parameters.Add(new SqlParameter("@Record3", u.Record3));
cmd.Parameters.Add(new SqlParameter("@Record4", u.Record4));
cmd.Parameters.Add(new SqlParameter("@Record5", u.Record5));
cmd.Parameters.Add(new SqlParameter("@Record6", u.Record6));
cmd.Parameters.Add(new SqlParameter("@Record7", u.Record7));
cmd.Parameters.Add(new SqlParameter("@Record8", u.Record8));
cmd.Parameters.Add(new SqlParameter("@Record1Sum", u.Record1Sum));
cmd.Parameters.Add(new SqlParameter("@Record5Sum", u.Record5Sum));
cmd.Parameters.Add(new SqlParameter("@Record7Sum", u.Record7Sum));
cmd.Parameters.Add(new SqlParameter("@Positive", u.Positive));
cmd.Parameters.Add(new SqlParameter("@Positiveno", u.Positiveno));
cmd.Parameters.Add(new SqlParameter("@Redresist", u.Redresist));
cmd.Parameters.Add(new SqlParameter("@Red", u.Red));
cmd.Parameters.Add(new SqlParameter("@Recognition", u.Recognition));
cmd.Parameters.Add(new SqlParameter("@Flag", u.Flag));
cmd.Parameters.Add(new SqlParameter("@Tablet", u.Tablet));
cmd.Parameters.Add(new SqlParameter("@Other", u.Other));
cmd.Parameters.Add(new SqlParameter("@Complaint", u.Complaint));
cmd.Parameters.Add(new SqlParameter("@KouJiang", u.KouJiang));
cmd.Parameters.Add(new SqlParameter("@Mdate", u.Mdate));
cmd.Parameters.Add(new SqlParameter("@Ydate", u.Ydate));
cmd.Parameters.Add(new SqlParameter("@CreateUserId", u.CreateUserId));
int i = cmd.ExecuteNonQuery();
con.Close();
try
{
if (i > 0)
return true;
else
return false;
}
catch (Exception e)
{
return false;
}
}
}
public static bool DelCharacter(int id)
{
using (SqlConnection con = new SqlConnection(constr))
{
con.Open();
string sql = "delete from Character where CharacterId=@id";
SqlCommand cmd = new SqlCommand(sql, con);
cmd.Parameters.Add(new SqlParameter("@id", id));
int i = cmd.ExecuteNonQuery();
con.Close();
try
{
if (i > 0)
return true;
else return false;
}
catch (Exception e)
{
return false;
}
}
}
public static List<Character> GetStatisticList(int ydate1, int mdate1, int ydate2, int mdate2, int unitid, int perid)
{
List<Character> list = new List<Character>();
using (SqlConnection con = new SqlConnection(constr))
{
if (unitid == 0 || unitid == -1)
{
string sql = "select Distinct Character.CharacterUnitId,Unit.UnitName from Character,Unit where Character.CharacterUnitId=Unit.Unitid";
SqlCommand cmd = new SqlCommand(sql, con);
con.Open();
SqlDataReader read0 = cmd.ExecuteReader();
while (read0.Read())
{
Character u = new Character();
u.UnitId = (int)read0["CharacterUnitId"];
u.UnitName = read0["UnitName"].ToString();
if (u.UnitId != 0)
{
if (unitid != -1)
{
list.Add(CharacterBL.GetStatisticByunitid(ydate1, mdate1, ydate2, mdate2, u.UnitId, u.UnitName, 0, null));
}
else
{
list.Add(CharacterBL.GetCharacterListDepartSum(ydate1, mdate1, ydate2, mdate2, u.UnitId, u.UnitName));
}
}
}
if (unitid != -1)
list.Add(CharacterBL.GetCharacterTotal(ydate1, mdate1, ydate2, mdate2,perid));
else
list.Add(CharacterBL.GetCharacterTotal(ydate1, mdate1, ydate2, mdate2,-1));
}
else
{
if (unitid != 0 && perid == 0)
{
string sql = "select Distinct Character.CharacterUnitId,Unit.UnitName,Personnel.PersonnelId,Personnel.PersonnelName from Character,Unit,Personnel where Character.CharacterUnitId=Unit.UnitId and Personnel.PersonnelId=Character.PersonnelId and CharacterUnitId=@UnitId";
SqlCommand cmd = new SqlCommand(sql, con);
cmd.Parameters.Add(new SqlParameter("@UnitId", unitid));
con.Open();
SqlDataReader read0 = cmd.ExecuteReader();
while (read0.Read())
{
Character u = new Character();
u.UnitId = (int)read0["CharacterUnitId"];
u.UnitName = read0["UnitName"].ToString();
int PersonnelId = (int)read0["PersonnelId"];
string PersonnelName = read0["PersonnelName"].ToString();
list.Add(CharacterBL.GetStatisticByunitid(ydate1, mdate1, ydate2, mdate2, unitid, u.UnitName, PersonnelId, PersonnelName));
}
}
else
{
string sql = "select ";
sql += "(select sum(Positive) 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 Positive1 ,";
sql += "(select sum(Positiveno) 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 Positive2,";
sql += "(select sum(Redresist) 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 Red1,";
sql += "(select sum(Red) 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 Red2,";
sql += "(select sum(Recognition) 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 Recognition1 ,";
sql += "(select sum(Flag) 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 Flag1,";
sql += "(select sum(Tablet) 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 Tablet1,";
sql += "(select sum(Other) 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 Other,";
sql += "(select sum(Complaint) 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 Complaint,";
sql += "(select sum(KouJiang) 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 KouJiang,";
sql += "(select sum(Record1Sum) 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 Record1Sum,";
sql += "(select sum(Record5Sum) 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 Record5Sum,";
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -