public List <DataFromAccess> ReadRecordsWithReadTime(DateTime readFromTime, DateTime readToTime)
{
_OleDbConnection = ConfigurationManager.AppSettings["OleDbConnection"];
OleDbConnection conn = null;
OleDbDataReader readerEntryrec = null;
List <DataFromAccess> returnDataList = new List <DataFromAccess>();
try
{
conn = new OleDbConnection(@_OleDbConnection);
conn.Open();
#region modify by liudan 2009-08-13
#endregion
string queryString = "Select USERINFO.USERID,USERINFO.CardNo, CHECKINOUT.CHECKTIME, CHECKINOUT.CHECKTYPE FROM USERINFO,CHECKINOUT where CHECKINOUT.CHECKTIME>= #" +
readFromTime + "# and CHECKINOUT.CHECKTIME<=#" + readToTime + "# and USERINFO.USERID=CHECKINOUT.USERID order by CHECKINOUT.USERID";
OleDbCommand cmd =
new OleDbCommand(queryString, conn);
readerEntryrec = cmd.ExecuteReader();
while (readerEntryrec.Read())
{
DataFromAccess data = new DataFromAccess();
data.CardNo = Convert.ToString(readerEntryrec["CardNo"]);
data.IOTime = Convert.ToDateTime(readerEntryrec["CHECKTIME"]);
InOutStatusEnum statusEnum = InOutStatusEnum.Out;
string ioStatus = Convert.ToString(readerEntryrec["CHECKTYPE"]);
//进出状态 0为进 1为出
if (ioStatus.Equals("I"))
{
statusEnum = InOutStatusEnum.In;
}
data.InOrOut = statusEnum;
returnDataList.Add(data);
}
return(returnDataList);
}
catch (Exception ex)
{
// 错误信息
strErrorMsg = "异常：\n" + ex.Message;
//// 写日志
TLineEventLog el = new TLineEventLog();
el.DoWriteEventLog(strErrorMsg, EventType.Error);
return(returnDataList);
}
finally
{
if (readerEntryrec != null)
{
readerEntryrec.Close();
}
if (conn != null)
{
conn.Close();
}
}
}
//note colbert for test
public List <DataFromAccess> ReadRecordsTest(DateTime laseReadTime)
{
string _OleDbConnection = ConfigurationManager.AppSettings["OleDbConnection"];
OleDbConnection conn = null;
OleDbDataReader readerEntryrec = null;
List <DataFromAccess> returnDataList = new List <DataFromAccess>();
try
{
//User Id=staples\wang.yueqi;Password=yueqiwang;
//Jet OLEDB:Database Password=databasepw;
//ConnectionIP();
conn = new OleDbConnection(@_OleDbConnection);
conn.Open();
OleDbCommand cmd =
new OleDbCommand("Select * FROM entryrec where entry_dt >= #" +
laseReadTime + "# order by Card_No", conn);
readerEntryrec = cmd.ExecuteReader();
while (readerEntryrec.Read())
{
string cardNo = Convert.ToString(readerEntryrec["Card_No"]);
DateTime iOTime = Convert.ToDateTime(readerEntryrec["entry_dt"]);
InOutStatusEnum statusEnum = InOutStatusEnum.Out;
int i = Convert.ToInt32(readerEntryrec["Reader"]);
//进出状态 0为进 1为出
if (i == 1)
{
statusEnum = InOutStatusEnum.In;
}
DataFromAccess data = new DataFromAccess(cardNo, statusEnum, iOTime);
returnDataList.Add(data);
}
return(returnDataList);
}
finally
{
if (readerEntryrec != null)
{
readerEntryrec.Close();
}
if (conn != null)
{
conn.Close();
}
}
}
public static string GetInOutNameByInOutStatus(InOutStatusEnum inOutStatusEnum)
{
switch (inOutStatusEnum)
{
case InOutStatusEnum.In:
return("进入");
case InOutStatusEnum.Out:
return("离开");
default:
return("");
}
}
/// <summary>
/// 用于查询某个员工更多信息
/// </summary>
public List <AttendanceInAndOutRecord> GetEmployeeInAndOutRecordByCondition(int employeeID, string employeeName,
int departmentID, string doorCardNo,
DateTime iOTimeFrom,
DateTime iOTimeTo,
InOutStatusEnum iOStatus,
OutInRecordOperateStatusEnum
operateStatus,
DateTime operateTimeFrom,
DateTime operateTimeTo)
{
List <Account> accountList = _IAccountBll.GetAccountByBaseCondition(employeeName, departmentID, -1, null, true, null);
if (departmentID == -1)
{
accountList = Tools.RemoteUnAuthAccount(accountList, AuthType.HRMIS, _LoginUser, HrmisPowers.A504);
}
List <AttendanceInAndOutRecord> records =
_dalAttendanceInAndOutRecord.GetAttendanceInAndOutRecordByCondition(employeeID, doorCardNo, iOTimeFrom,
iOTimeTo,
iOStatus, operateStatus,
operateTimeFrom,
operateTimeTo);
for (int i = records.Count - 1; i >= 0; i--)
{
Account temp = Tools.FindAccountById(accountList, records[i].EmployeeId);
if (temp == null)
{
records.RemoveAt(i);
}
else
{
records[i].EmployeeName = temp.Name;
}
}
return(records);
}
public List <AttendanceInAndOutRecord> GetAttendanceInAndOutRecordByCondition(int employeeID, string doorCardNo,
DateTime iOTimeFrom,
DateTime iOTimeTo,
InOutStatusEnum iOStatus,
OutInRecordOperateStatusEnum
operateStatus,
DateTime operateTimeFrom,
DateTime operateTimeTo)
{
List <AttendanceInAndOutRecord> records = new List <AttendanceInAndOutRecord>();
SqlCommand sqlCommmand = new SqlCommand();
sqlCommmand.Parameters.Add(_EmployeeId, SqlDbType.Int).Value = employeeID;
sqlCommmand.Parameters.Add(_IOTimeStart, SqlDbType.DateTime).Value = iOTimeFrom;
sqlCommmand.Parameters.Add(_IOTimeEnd, SqlDbType.DateTime).Value = iOTimeTo;
sqlCommmand.Parameters.Add(_IOStatus, SqlDbType.Int).Value = iOStatus;
sqlCommmand.Parameters.Add(_OperateStatus, SqlDbType.Int).Value = operateStatus;
sqlCommmand.Parameters.Add(_OperateTimeStart, SqlDbType.DateTime).Value = operateTimeFrom;
sqlCommmand.Parameters.Add(_OperateTimeEnd, SqlDbType.DateTime).Value = operateTimeTo;
sqlCommmand.Parameters.Add(_DoorCardNo, SqlDbType.NVarChar, 50).Value = doorCardNo;
using (SqlDataReader sdr = SqlHelper.ExecuteReader("GetEmployeeInAndOutByCondition", sqlCommmand))
{
while (sdr.Read())
{
AttendanceInAndOutRecord record = new AttendanceInAndOutRecord();
record.RecordID = (Int32)sdr[_DBRecordID];
record.EmployeeId = (Int32)sdr[_DBEmployeeId];
record.DoorCardNo = sdr[_DBDoorCardNo].ToString();
record.IOStatus = (InOutStatusEnum)sdr[_DBIOStatus];
record.IOTime = Convert.ToDateTime(sdr[_DBIOTime]);
record.OperateStatus = (OutInRecordOperateStatusEnum)sdr[_DBOperateStatus];
record.OperateTime = Convert.ToDateTime(sdr[_DBOperateTime]);
records.Add(record);
}
}
return(records);
}
public DataFromAccess(string cardNo, InOutStatusEnum inOrOut, DateTime iOTime)
{
_CardNo = cardNo;
_InOrOut = inOrOut;
_IOTime = iOTime;
}
public List <AttendanceInAndOutRecord> GetEmployeeInAndOutRecordByCondition(int employeeID, string employeeName, int departmentID, string doorCardNo,
DateTime iOTimeFrom, DateTime iOTimeTo, InOutStatusEnum iOStatus,
OutInRecordOperateStatusEnum operateStatus, DateTime operateTimeFrom,
DateTime operateTimeTo, Account loginUser)
{
return(new AttendanceOutInRecord(loginUser).GetEmployeeInAndOutRecordByCondition(employeeID, employeeName,
departmentID, doorCardNo, iOTimeFrom,
iOTimeTo, iOStatus, operateStatus,
operateTimeFrom, operateTimeTo));
}
public List <DataFromAccess> ReadRecords(DateTime lastReadTime)
{
_OleDbConnection = ConfigurationManager.AppSettings["OleDbConnection"];
OleDbConnection conn = null;
OleDbDataReader readerEntryrec = null;
List <DataFromAccess> returnDataList = new List <DataFromAccess>();
try
{
//User Id=staples\wang.yueqi;Password=yueqiwang;
//Jet OLEDB:Database Password=databasepw;
//ConnectionIP();
conn = new OleDbConnection(@_OleDbConnection);
conn.Open();
#region modify by liudan 2009-08-13
//OleDbCommand cmd =
// new OleDbCommand("Select * FROM entryrec where entry_dt >= #" +
// lastReadTime + "# order by Card_No", conn);
//readerEntryrec = cmd.ExecuteReader();
//while (readerEntryrec.Read())
//{
// DataFromAccess data=new DataFromAccess();
// data.CardNo = Convert.ToString(readerEntryrec[_Card_No]);
// string s = (readerEntryrec[_IO]).ToString();
// int i = Convert.ToInt32(readerEntryrec[_IO]);
// //进出状态 0为进 1为出
// if (i == 1)
// {
// data.InOrOut = InOutStatusEnum.In;
// }
// else
// {
// data.InOrOut = InOutStatusEnum.Out;
// }
// data.IOTime = Convert.ToDateTime(readerEntryrec[_IOTime]);
// returnDataList.Add(data);
//}
//return returnDataList;
#endregion
string queryString = "Select USERINFO.USERID,USERINFO.CardNo, CHECKINOUT.CHECKTIME, CHECKINOUT.CHECKTYPE FROM USERINFO,CHECKINOUT where CHECKINOUT.CHECKTIME>= #" +
lastReadTime + "# and USERINFO.USERID=CHECKINOUT.USERID order by CHECKINOUT.USERID";
OleDbCommand cmd =
new OleDbCommand(queryString, conn);
readerEntryrec = cmd.ExecuteReader();
while (readerEntryrec.Read())
{
DataFromAccess data = new DataFromAccess();
data.CardNo = Convert.ToString(readerEntryrec["CardNo"]);
data.IOTime = Convert.ToDateTime(readerEntryrec["CHECKTIME"]);
InOutStatusEnum statusEnum = InOutStatusEnum.Out;
string ioStatus = Convert.ToString(readerEntryrec["CHECKTYPE"]);
//进出状态 0为进 1为出
if (ioStatus.Equals("I"))
{
statusEnum = InOutStatusEnum.In;
}
data.InOrOut = statusEnum;
returnDataList.Add(data);
}
return(returnDataList);
}
catch (Exception ex)
{
// 错误信息
strErrorMsg = "异常：\n" + ex.Message;
//// 写日志
TLineEventLog el = new TLineEventLog();
el.DoWriteEventLog(strErrorMsg, EventType.Error);
return(returnDataList);
}
finally
{
if (readerEntryrec != null)
{
readerEntryrec.Close();
}
if (conn != null)
{
conn.Close();
}
}
}