/// <summary>
/// 查询打卡学员人数
/// </summary>
/// <param name="dt">需要查询的日期</param>
/// <param name="IsToday">是否是今天</param>
/// <returns></returns>
public int GetAttendStudents(DateTime dt, bool IsToday)
{
DateTime dt1;
if (IsToday)
{
dt1 = Convert.ToDateTime(SQLHelper.GetServerTime().ToShortDateString());
}
else
{
dt1 = dt;
}
DateTime dt2 = dt1.AddDays(1);//结束时间是当前时间加1天
string sql = "SELECT COUNT(DISTINCT(CardNo)) FROM Attendance WHERE DTime BETWEEN '{0}' AND '{1}'";
sql = string.Format(sql, dt1, dt2);
try
{
return(Convert.ToInt32(SQLHelper.GetSingleResult(sql)));
}
catch (Exception ex)
{
throw new Exception("获取数据失败:" + ex.Message);
}
}
/// <summary>
/// 按照指定日期查询当天已签到的学员总数
/// </summary>
/// <param name="dt">查询的时间</param>
/// <param name="isToday">是否是当天</param>
/// <returns></returns>
public string GetAttendStudents(DateTime dt, bool isToday)
{
DateTime dt1;
if (isToday)//如果是当天，则直接获取服务器的时间
{
dt1 = Convert.ToDateTime(SQLHelper.GetServerTime().ToShortDateString());
}
else
{
dt1 = dt;
}
DateTime dt2 = dt1.AddDays(1.0);//将当天时间加上一天
string sql = "select count(distinct CardNo) from Attendance where DTime between '{0}' and '{1}'";
sql = string.Format(sql, dt1, dt2);
try
{
return(SQLHelper.GetSingleResult(sql).ToString());
}
catch (Exception ex)
{
return("暂时无法获取已签到的学员总数！" + ex.Message);
}
}
//Update the database-determine if the book has expired
public void UpdateOverdue(string borrowId)
{
//Define List
List <BorrowBookDetail> objList = GetDetailByBorrowId(borrowId);
//Gets the current date
DateTime today = Convert.ToDateTime(SQLHelper.GetServerTime().ToShortDateString());
//Traversing List
for (int i = 0; i < objList.Count; i++)
{
if (Convert.ToDateTime(objList[i].LastReturnDate.ToShortDateString()) < today)
{
//Change this record to expire.
try
{
if (OverdueById(objList[i].DetailId) == 1)
{
//Successful！
}
}
catch (Exception ex)
{
throw ex;
}
}
}
}
/// <summary>
/// 按照指定日期查询实到学生总数
/// </summary>
/// <param name="beginTime"></param>
/// <param name="endTime"></param>
/// <returns></returns>
public int GetSignStudents(DateTime beginTime, DateTime endTime)
{
string sql = "select count(distinct CardNo) from Attendance where DTime between '{0}' and '{1}' ";
DateTime dt1 = Convert.ToDateTime(SQLHelper.GetServerTime().ToShortDateString());
sql = string.Format(sql, beginTime, endTime);
return(Convert.ToInt32(SQLHelper.GetSingleResult(sql)));
}
//Get a MemberId
public string BuildBorrowId()
{
//Get server time converted to 14-bit characters
string borrowId = SQLHelper.GetServerTime().ToString("yyyyMMddHHmmss");
//Generate 2-bit random numbers
Random objRandom = new Random();
borrowId += objRandom.Next(0, 100).ToString("00");
//Return memberId
return('B' + borrowId);
}
public DateTime GetDBServerTime()
{
try
{
return(SQLHelper.GetServerTime());
}
catch (Exception ex)
{
throw ex;
}
}
public int GetSignedStudent()
{
string sql = "select count(distinct CardNo) from Attendance where DTime between @DTime1 and @DTime2 ";
DateTime dt1 = Convert.ToDateTime(SQLHelper.GetServerTime().ToShortDateString());
DateTime dt2 = dt1.AddDays(1.0);
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@DTime1", dt1),
new SqlParameter("@DTime2", dt2),
};
return(Convert.ToInt32(SQLHelper.GetSingleResult(sql, param)));
}
//Change the last time you logged in
public int UpdateLastLoginTime(int loginId)
{
//Preparing SQL statements
string sql = "Update SysAdmins Set LastLoginTime=@LastLoginTime Where LoginId=@LoginId ";
//Preparing variables
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@LastLoginTime", SQLHelper.GetServerTime()),
new SqlParameter("@LoginId", loginId),
};
//Start Exectution
try
{
return(SQLHelper.Update(sql, para));
}
catch (Exception ex)
{
throw ex;
}
}
//Write Exit Time
public int WriteExitTime(int logId)
{
//Preparing SQL statements
string sql = "Update LoginLogs Set ExitTime=@ExitTime Where LogId=@LogId";
//Prepare parameters
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@ExitTime", SQLHelper.GetServerTime()),
new SqlParameter("@LogId", logId),
};
//Execute and return
try
{
return(SQLHelper.Update(sql, para));
}
catch (Exception ex)
{
throw ex;
}
}
//按照指定日期查询当天已签到的学员总数
public string GetAttendStudents(DateTime dt, bool isToday)
{
//时间
DateTime dt1;
//如果是当天，直接获取服务器的时间
if (isToday)
{
dt1 = Convert.ToDateTime(SQLHelper.GetServerTime().ToShortDateString());
}
else
{
dt1 = dt;
}
//当天加上一天
DateTime dt2 = dt1.AddDays(1.0);
string sql = "select count(CardNo) from Attendance where DTime between '{0}' and '{1}'";
sql = string.Format(sql, dt1, dt2);
return(SQLHelper.GetSingleResult(sql).ToString());
}
//Gets the time of the database server
public DateTime GetServerTime()
{
return(SQLHelper.GetServerTime());
}
