/// <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); }