USE [TBS_BAWSE]
GO
/****** Object: StoredProcedure [dbo].[SelectNewRecords] Script Date: 02/03/2016 20:09:35 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[SelectNewRecords]
as
Select TA_UID,TA_UserID, CONVERT(VARCHAR(10), TA_Timestamp, 105) AS ActionDate , TA_Timestamp,TA_AccessPointID,TA_AuthenticationResult,TA_IDContext from TBST_TARecords
where TA_UserID IS NOT NULL and ( Transferred = 0 or Transferred is null)
///////////////////////////////////////////////////////////
USE [TBS_BAWSE]
GO
/****** Object: StoredProcedure [dbo].[UpdateTransffered] Script Date: 03/03/2016 01:26:45 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[UpdateTransffered]
@TA_UID int
as
update TBST_TARecords
set Transferred =1
where TA_UID =@TA_UID
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.ServiceProcess;
using System.Text;
using System.Data.SqlClient;
namespace FingerPrintService
{
public partial class FingerPrintDaily : ServiceBase
{
public FingerPrintDaily()
{
InitializeComponent();
}
protected override void OnStart(string[] args)
{
timer_fingerprint.Start();
using (System.IO.StreamWriter file =
new System.IO.StreamWriter(@"C:\finger.txt", true))
{
file.WriteLine("service started "+DateTime.Now.ToString());
}
}
protected override void OnStop()
{
}
private void timer_fingerprint_Elapsed(object sender, System.Timers.ElapsedEventArgs e)
{
try
{
// using (System.IO.StreamWriter file =
//new System.IO.StreamWriter(@"C:\finger.txt", true))
// {
// file.WriteLine("Timer started " + DateTime.Now.ToString());
// }
SqlConnection CN_TBS = new SqlConnection("Server=.;Database=TBS_BAWSE;User ID=sa;Password=123");
SqlCommand CM_TBS = new SqlCommand("SelectNewRecords");
CM_TBS.Connection = CN_TBS;
CM_TBS.CommandType = CommandType.StoredProcedure;
DataTable DT_TBS = new DataTable();
SqlDataAdapter DA_TBS = new SqlDataAdapter(CM_TBS);
DA_TBS.Fill(DT_TBS);
SqlCommand CM_UpdateTBS = new SqlCommand("UpdateTransffered"); CM_UpdateTBS.CommandType = CommandType.StoredProcedure; CM_UpdateTBS.Connection = CN_TBS;
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
SqlConnection CN_TBS2 = new SqlConnection("Server=192.168.0.110;Database=actatek_access_labors;User ID=sa;Password=Aa123123");
SqlCommand CM_TBS2 = new SqlCommand("SelectNewRecords"); CM_TBS2.Connection = CN_TBS2; CM_TBS2.CommandType = CommandType.StoredProcedure;
DataTable DT_TBS2 = new DataTable();
SqlDataAdapter DA_TBS2 = new SqlDataAdapter(CM_TBS2); DA_TBS2.Fill(DT_TBS2);
SqlCommand CM_UpdateTBS2 = new SqlCommand("UpdateTransffered"); CM_UpdateTBS2.CommandType = CommandType.StoredProcedure; CM_UpdateTBS2.Connection = CN_TBS2;
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
SqlConnection CN_Dawami = new SqlConnection("Server=192.168.0.24\\SQLEXPRESS;Database=Dawami;User=FingerUser;Password=Aa123123");
SqlCommand CM_Dawami = new SqlCommand("InsertNewRecordFromTBS"); CM_Dawami.Connection = CN_Dawami; CM_Dawami.CommandType = CommandType.StoredProcedure;
CN_Dawami.Open();
CN_TBS.Open();
for (int i = 0; i < DT_TBS.Rows.Count; i++)
{
CM_Dawami.Parameters.Clear();
CM_Dawami.Parameters.AddWithValue("@EMPLOYEEID", DT_TBS.Rows[i]["TA_UserID"].ToString());
CM_Dawami.Parameters.AddWithValue("@LOGDATE", DT_TBS.Rows[i]["ActionDate"].ToString());
CM_Dawami.Parameters.AddWithValue("@LOGTIME", DateTime.Parse(DT_TBS.Rows[i]["TA_Timestamp"].ToString()).ToString("hh:mm tt"));
CM_Dawami.Parameters.AddWithValue("@TERMINALID", DT_TBS.Rows[i]["TA_AccessPointID"].ToString());
string InOut;
if (DT_TBS.Rows[i]["TA_IDContext"].ToString() == "100")
{
InOut = "0";
}
else if (DT_TBS.Rows[i]["TA_IDContext"].ToString() == "200")
{
InOut = "1";
}
else
{
InOut = "0";
goto finish;
}
CM_Dawami.Parameters.AddWithValue("@INOUT", InOut);
int RowEf = 0;
try
{
RowEf = CM_Dawami.ExecuteNonQuery();
}
catch { }
//if (RowEf > 0)
finish:
CM_UpdateTBS.Parameters.Clear();
CM_UpdateTBS.Parameters.AddWithValue("@TA_UID", DT_TBS.Rows[i]["TA_UID"].ToString());
CM_UpdateTBS.ExecuteNonQuery();
}
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
CN_TBS2.Open();
for (int i = 0; i < DT_TBS2.Rows.Count; i++)
{
CM_Dawami.Parameters.Clear();
CM_Dawami.Parameters.AddWithValue("@EMPLOYEEID", DT_TBS2.Rows[i]["USERID"].ToString());
CM_Dawami.Parameters.AddWithValue("@LOGDATE", DT_TBS2.Rows[i]["ActionDate"].ToString());
CM_Dawami.Parameters.AddWithValue("@LOGTIME", DT_TBS2.Rows[i]["actiontimes"].ToString());
CM_Dawami.Parameters.AddWithValue("@TERMINALID", DT_TBS2.Rows[i]["TERMINALSN"].ToString());
if (DT_TBS2.Rows[i]["EVENTID"].ToString() == "IN")
{
CM_Dawami.Parameters.AddWithValue("@INOUT", "0");
}
else
{
CM_Dawami.Parameters.AddWithValue("@INOUT", "1");
}
int RowEf = 0;
try
{
RowEf = CM_Dawami.ExecuteNonQuery();
}
catch { }
CM_UpdateTBS2.Parameters.Clear();
CM_UpdateTBS2.Parameters.AddWithValue("@ID", DT_TBS2.Rows[i]["ID"].ToString());
CM_UpdateTBS2.ExecuteNonQuery();
}
CN_TBS.Close(); CN_TBS2.Close();
CN_Dawami.Close();
}
catch(Exception ee)
{
using (System.IO.StreamWriter file =
new System.IO.StreamWriter(@"C:\finger.txt", true))
{
file.WriteLine(ee.ToString());
}
}
}
}
}