/// <summary>
/// Read Bio-Matric Settings from App.Config
/// </summary>
public BioMetricDevice GetDeviceSettings()
{
try
{
BioMetricDevice BioDevice = new BioMetricDevice();
BioDevice.DeviceType = int.Parse(ConfigurationManager.AppSettings["DeviceType"].ToString());
BioDevice.DeviceSerialNo = ConfigurationManager.AppSettings["DeviceSerialNo"].ToString();
BioDevice.IPAddress = ConfigurationManager.AppSettings["DeviceIP"].ToString();
BioDevice.AccessFilePath = ConfigurationManager.AppSettings["AccessFilePath"].ToString();
BioDevice.ExcelFilePath = ConfigurationManager.AppSettings["ExcelFilePath"].ToString();
BioDevice.DataImportMode = int.Parse(ConfigurationManager.AppSettings["DataImportMode"].ToString());
BioDevice.DataImportType = ConfigurationManager.AppSettings["DataImportType"].ToString().ToString();
BioDevice.DataExportMode = int.Parse(ConfigurationManager.AppSettings["DataExportMode"].ToString());
BioDevice.ImportTime = int.Parse(ConfigurationManager.AppSettings["DataImportTime"].ToString());
BioDevice.ExportTime = int.Parse(ConfigurationManager.AppSettings["DataExportTime"].ToString());
return(BioDevice);
}
catch (Exception ex)
{
throw (new Exception(MethodBase.GetCurrentMethod().DeclaringType.ToString() + "." + (new System.Diagnostics.StackFrame()).GetMethod().Name, ex));
}
}
/// <summary>
/// Import Data from Bio-Metric Data File(Access)
/// </summary>
public DataTable ImportDataFromBioMetricDeviceViaAccess()
{
// Initialize a AttendanceTableData to store biometric punch data
DataTable ObjAttendanceTable = new DataTable();
ObjAttendanceTable = AttendaceTable();
// Read Bio-Metric Device Settings From App.Config
BioMetricDevice BioDevice = new BioMetricDevice();
BioDevice = GetDeviceSettings();
#region Find LastImportDate
///Read Last Import Date, therefore it will import data which are punched after that date...
DataRow DtR;
try
{
SqlCommand SelectCommand = new SqlCommand();
SelectCommand.CommandType = CommandType.StoredProcedure;
SelectCommand.CommandText = "pHRM_Attendances_SelectLastImportDate";
DtR = ExecuteGetDataRow(SelectCommand);
}
catch (Exception ex)
{
throw (new Exception(MethodBase.GetCurrentMethod().DeclaringType.ToString() + "." + (new System.Diagnostics.StackFrame()).GetMethod().Name, ex));
}
DateTime LastImportDate;
LastImportDate = (DtR["LastImportDate"].ToString() == "" ? DateTime.Parse("2011-01-01 00:00:00") : DateTime.Parse(DtR["LastImportDate"].ToString()));
#endregion
try
{
string connectionString = "Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + BioDevice.AccessFilePath + ";User Id=admin;Jet OLEDB:Database Password=Timmy;";
OleDbConnection conn = new OleDbConnection(connectionString);
conn.Open();
OleDbCommand OledbCmd = new OleDbCommand();
OledbCmd.CommandText = "SELECT * FROM tmpTRecords where kqdate>#" + LastImportDate.Date.ToString() + "# and kqtime>#" + LastImportDate.TimeOfDay.ToString() + "# order by KqDate,KqTime";
OledbCmd.Connection = conn;
DataTable data = new DataTable();
OleDbDataAdapter OledbAdapter = new OleDbDataAdapter(OledbCmd);
OledbAdapter.Fill(data);
foreach (DataRow dr in data.Rows)
{
DataRow row = ObjAttendanceTable.NewRow();
row["EmployeeID"] = dr["emp_id"].ToString();
row["EmployeeCode"] = "";
row["PunchDate"] = DateTime.Parse(dr["KqDate"].ToString());
row["PunchTime"] = DateTime.Parse(dr["KqTime"].ToString());
row["dday"] = DateTime.Parse(dr["KqDate"].ToString()).Day;
row["dmonth"] = DateTime.Parse(dr["KqDate"].ToString()).Month;
row["dyear"] = DateTime.Parse(dr["KqDate"].ToString()).Year;
row["PunchSource"] = 0;
ObjAttendanceTable.Rows.Add(row);
}
}
catch (Exception ex)
{
throw (new Exception(MethodBase.GetCurrentMethod().DeclaringType.ToString() + "." + (new System.Diagnostics.StackFrame()).GetMethod().Name, ex));
}
return(ObjAttendanceTable);
}
/// <summary>
/// Import Data from Bio-Metric Data File(Excel)
/// </summary>
public DataTable ImportDataFromBioMetricDeviceViaExcel()
{
// Read Bio-Metric Device Settings From App.Config
BioMetricDevice BioDevice = new BioMetricDevice();
BioDevice = GetDeviceSettings();
// Initialize a AttendanceTableData to store biometric punch data
DataTable ObjAttendanceTable = new DataTable();
ObjAttendanceTable = AttendaceTable();
try
{
OleDbConnection oleCon = new OleDbConnection();
// Excel Connection String
if (BioDevice.ExcelFilePath.Contains(".xlsx") == true)
{
//OFFICE 2007 or LATER
oleCon.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + BioDevice.ExcelFilePath + ";Extended Properties=" + (char)34 + "Excel 12.0;HDR=YES" + (char)34 + ";";
}
else if (BioDevice.ExcelFilePath.Contains(".xls") == true)
{
//OFFICE 2003
oleCon.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;data source=" + BioDevice.ExcelFilePath + ";Extended Properties=Excel 8.0;";
}
oleCon.Open();
//Check the current ExcelFile is a valid Bio-Metric Data File or not
System.Data.DataTable dtx = null;
dtx = oleCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dtx == null)
{
return(ObjAttendanceTable);
}
string ExcelSheetName = "Sheet1$";
//foreach (DataRow row in dtx.Rows)
//{
// OleDbCommand oleCmdSelect = new OleDbCommand("select top 1 * from [" + row["TABLE_NAME"].ToString() + "]", oleCon);
// DataTable datax = new DataTable();
// OleDbDataAdapter OledbAdapterx = new OleDbDataAdapter(oleCmdSelect);
// OledbAdapterx.Fill(datax);
// if (datax.Columns[3].ColumnName == "SlotCardDate" && datax.Columns[5].ColumnName == "SlotCardTime")
// {
// ExcelSheetName=row["TABLE_NAME"].ToString();
// break;
// }
// datax.Dispose();
//}
//if (ExcelSheetName == "")
//{
// return ObjAttendanceTable;
//}
//Read Data from Bio_Matric File
OleDbCommand OledbCmd = new OleDbCommand("select * from [" + ExcelSheetName + "]", oleCon);
DataTable data = new DataTable();
OleDbDataAdapter OledbAdapter = new OleDbDataAdapter(OledbCmd);
OledbAdapter.Fill(data);
foreach (DataRow dr in data.Rows)
{
string EmployeeID = dr["JobId"].ToString();
string SlotCardDate = dr["SlotCardDate"].ToString();
string[] SlotCardTimes = dr["SlotCardTime"].ToString().Split(new char[] { ' ' });
foreach (string pTime in SlotCardTimes)
{
if (pTime != "")
{
DataRow row = ObjAttendanceTable.NewRow();
row["EmployeeID"] = EmployeeID;
row["EmployeeCode"] = "";
row["PunchDate"] = DateTime.Parse(SlotCardDate);
row["PunchTime"] = pTime;
row["dday"] = DateTime.Parse(SlotCardDate).Day;
row["dmonth"] = DateTime.Parse(SlotCardDate).Month;
row["dyear"] = DateTime.Parse(SlotCardDate).Year;
row["PunchSource"] = 0;
ObjAttendanceTable.Rows.Add(row);
}
}
}
}
catch (Exception ex)
{
throw (new Exception(MethodBase.GetCurrentMethod().DeclaringType.ToString() + "." + (new System.Diagnostics.StackFrame()).GetMethod().Name, ex));
}
return(ObjAttendanceTable);
}
