public List <OpenDoor> FindOpenDoorBase(string LockID)
{
List <OpenDoor> MyAllOpenDoor = new List <OpenDoor>();
SqlConnection MySqlConnection = new SqlConnection(ConnectionString);
string MyCommandText = "select * from OpenDoor where LockID='" + LockID + "'";
SqlCommand MySqlCommand = new SqlCommand(MyCommandText, MySqlConnection);
MySqlConnection.Open();
SqlDataReader MySqlDataReader = MySqlCommand.ExecuteReader();
OpenDoor AnyOpenDoor = null;
while (MySqlDataReader.Read())
{
AnyOpenDoor = new OpenDoor();
AnyOpenDoor.LockID = (string)MySqlDataReader["LockID"];
AnyOpenDoor.OwerName = (string)MySqlDataReader["Name"];
AnyOpenDoor.KeyID = (int)MySqlDataReader["KeyID"];
AnyOpenDoor.OpenDate = (DateTime)MySqlDataReader["CreateDate"];
MyAllOpenDoor.Add(AnyOpenDoor);
}
MySqlDataReader.Close();
MySqlConnection.Close();
return(MyAllOpenDoor);
}
public int InsertOpenDoor(OpenDoor MyOpenDoor)
{
SqlConnection MySqlConnection = new SqlConnection(ConnectionString);
string MyCommandText = "insert into OpenDoor (LockID,KeyID,CreateDate,DateStr) values (@LockID,@KeyID,@CreateDate,@DateStr)";
SqlCommand MySqlCommand = new SqlCommand(MyCommandText, MySqlConnection);
MySqlCommand.Parameters.Add(new SqlParameter("@LockID", MyOpenDoor.LockID));
MySqlCommand.Parameters.Add(new SqlParameter("@KeyID", MyOpenDoor.KeyID));
MySqlCommand.Parameters.Add(new SqlParameter("@CreateDate", MyOpenDoor.OpenDate));
MySqlCommand.Parameters.Add(new SqlParameter("@DateStr", MyOpenDoor.OpenDateStr));
MySqlConnection.Open();
int RowCount = MySqlCommand.ExecuteNonQuery();
MySqlConnection.Close();
return(RowCount);
}
public List <OpenDoor> FindOpenDoorEx(string LockID, string InConnectionString, string Name, string TimeRange)
{
List <OpenDoor> MyAllOpenDoor = new List <OpenDoor>();
if (InConnectionString != null)
{
ConnectionString = InConnectionString;
}
SqlConnection MySqlConnection = new SqlConnection(ConnectionString);
string MyNameFilter = "";
string MyDateFilter = "";
string MyAllFilter = "";
if (Name == "全部")
{
MyNameFilter = "";
}
else
{
MyNameFilter = " AND Name='" + Name + "' ";
}
//--2.-------------------------------------------------
string NowDateStr = DateTime.Now.ToString();
NowDateStr = NowDateStr.Substring(0, NowDateStr.IndexOf(" "));
string AddSubNowDateStr = NowDateStr.Replace("-", "");
//Select DATEADD(DAY,1,'20130101')
if (TimeRange == "0") //当天
{
MyDateFilter = " and CreateDate>='" + NowDateStr + "'";
}
if (TimeRange == "1") //7天之内
{
AddSubNowDateStr = "(Select DATEADD(DAY,-7,'" + AddSubNowDateStr + "')) ";
MyDateFilter = " and CreateDate>=" + AddSubNowDateStr;
}
if (TimeRange == "2") //10天之内
{
AddSubNowDateStr = "(Select DATEADD(DAY,-10,'" + AddSubNowDateStr + "')) ";
MyDateFilter = " and CreateDate>=" + AddSubNowDateStr;
}
if (TimeRange == "3") //30天之内
{
AddSubNowDateStr = "(Select DATEADD(DAY,-30,'" + AddSubNowDateStr + "')) ";
MyDateFilter = " and CreateDate>=" + AddSubNowDateStr;
}
if (TimeRange == "4") //2*30天之内
{
AddSubNowDateStr = "(Select DATEADD(DAY,-60,'" + AddSubNowDateStr + "')) ";
MyDateFilter = " and CreateDate>=" + AddSubNowDateStr;
}
if (TimeRange == "5") //3*30天之内
{
AddSubNowDateStr = "(Select DATEADD(DAY,-90,'" + AddSubNowDateStr + "')) ";
MyDateFilter = " and CreateDate>=" + AddSubNowDateStr;
}
if (TimeRange == "6") //6*30天之内
{
AddSubNowDateStr = "(Select DATEADD(DAY,-180,'" + AddSubNowDateStr + "')) ";
MyDateFilter = " and CreateDate>=" + AddSubNowDateStr;
}
if (TimeRange == "7") //一年之内
{
AddSubNowDateStr = "(Select DATEADD(DAY,-365,'" + AddSubNowDateStr + "')) ";
MyDateFilter = " and CreateDate>=" + AddSubNowDateStr;
}
if (TimeRange == "8") //三年之内
{
//string TempDateStr = "-1095";
//AddSubNowDateStr = "(Select DATEADD(DAY,"+TempDateStr+",'" + AddSubNowDateStr + "')) ";
AddSubNowDateStr = "(Select DATEADD(DAY,-1095,'" + AddSubNowDateStr + "')) ";
MyDateFilter = " and CreateDate>=" + AddSubNowDateStr;
}
if (TimeRange == "9") //五年之内
{
//string TempDateStr = "-1825";
//AddSubNowDateStr = "(Select DATEADD(DAY," + TempDateStr + ",'" + AddSubNowDateStr + "')) ";
AddSubNowDateStr = "(Select DATEADD(DAY,-1825,'" + AddSubNowDateStr + "')) ";
MyDateFilter = " and CreateDate>=" + AddSubNowDateStr;
}
if (TimeRange == "10") //全部
{
MyDateFilter = "";
}
//===========================================================================================
if (MyNameFilter == "" && MyDateFilter == "")
{
MyAllFilter = "";
}
else
{
MyAllFilter = MyNameFilter + MyDateFilter;
/*
* if (MyNameFilter == "")
* {
* MyAllFilter = MyAllFilter.Trim();
* MyAllFilter = MyAllFilter.Substring(3); //去掉“and”；
* }
* */
}
//string MyCommandText = "select * from OpenDoor where LockID='" + LockID + "'";
//string MyCommandText = "select Name,KeyID,CreateDate from OpenDoor where " + MyNameFilter + MyDateFilter + " ORDER BY CreateDate DESC";
string MyCommandText = "select Name,KeyID,CreateDate from OpenDoor where LockID=" + "'" + LockID + "'" + MyAllFilter + " ORDER BY CreateDate DESC";
SqlCommand MySqlCommand = new SqlCommand(MyCommandText, MySqlConnection);
MySqlConnection.Open();
SqlDataReader MySqlDataReader = MySqlCommand.ExecuteReader();
OpenDoor AnyOpenDoor = null;
while (MySqlDataReader.Read())
{
AnyOpenDoor = new OpenDoor();
//AnyOpenDoor.LockID = (string)MySqlDataReader["LockID"];
AnyOpenDoor.OwerName = (string)MySqlDataReader["Name"];
AnyOpenDoor.KeyID = (int)MySqlDataReader["KeyID"];
AnyOpenDoor.OpenDate = (DateTime)MySqlDataReader["CreateDate"];
MyAllOpenDoor.Add(AnyOpenDoor);
}
MySqlDataReader.Close();
MySqlConnection.Close();
return(MyAllOpenDoor);
}
