USE [TBS_BAWSE]
GO
/****** Object: StoredProcedure [dbo].[UpdateTransffered] Script Date: 03/03/2016 02:08:47 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
USE [TBS_BAWSE]
GO
/****** Object: StoredProcedure [dbo].[SelectNewRecords] Script Date: 03/03/2016 02:08:56 AM ******/
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)
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 Con_TBS = new SqlConnection("Server=.;Database=TBS_BAWSE;User ID=sa;Password=123");
SqlCommand cmd_TBS = new SqlCommand("SelectNewRecords");
cmd_TBS.Connection = Con_TBS;
cmd_TBS.CommandType = CommandType.StoredProcedure;
SqlDataAdapter adp_TBS = new SqlDataAdapter(cmd_TBS);
DataTable dt_TBS = new DataTable();
adp_TBS.Fill(dt_TBS);
SqlCommand cmd_UpdateTBS = new SqlCommand("UpdateTransffered");
cmd_UpdateTBS.CommandType = CommandType.StoredProcedure;
cmd_UpdateTBS.Connection = Con_TBS;
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
SqlConnection Con_Actatek = new SqlConnection("Server=192.168.0.110;Database=actatek_access_labors;User ID=sa;Password=Aa123123");
SqlCommand cmd_Actatek = new SqlCommand("SelectNewRecords");
cmd_Actatek.Connection = Con_Actatek;
cmd_Actatek.CommandType = CommandType.StoredProcedure;
SqlDataAdapter adp_Actatek = new SqlDataAdapter(cmd_Actatek);
DataTable dt_Actatek = new DataTable();
adp_Actatek.Fill(dt_Actatek);
SqlCommand cmd_Update_Actatek = new SqlCommand("UpdateTransffered");
cmd_Update_Actatek.CommandType = CommandType.StoredProcedure;
cmd_Update_Actatek.Connection = Con_Actatek;
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
SqlConnection Con_Dawami = new SqlConnection("Server=192.168.0.24\\SQLEXPRESS;Database=Dawami;User=FingerUser;Password=Aa123123");
SqlCommand cmd_Dawami = new SqlCommand("InsertNewRecordFromTBS");
cmd_Dawami.Connection = Con_Dawami;
cmd_Dawami.CommandType = CommandType.StoredProcedure;
Con_TBS.Open();
Con_Dawami.Open();
for (int i = 0; i < dt_TBS.Rows.Count; i++)
{
cmd_Dawami.Parameters.Clear();
cmd_Dawami.Parameters.AddWithValue("@EMPLOYEEID", dt_TBS.Rows[i]["TA_UserID"].ToString());
cmd_Dawami.Parameters.AddWithValue("@LOGDATE", dt_TBS.Rows[i]["ActionDate"].ToString());
cmd_Dawami.Parameters.AddWithValue("@LOGTIME", DateTime.Parse(dt_TBS.Rows[i]["TA_Timestamp"].ToString()).ToString("hh:mm tt"));
cmd_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;
cmd_UpdateTBS.Parameters.Clear();
cmd_UpdateTBS.Parameters.AddWithValue("@TA_UID", dt_TBS.Rows[i]["TA_UID"].ToString());
cmd_UpdateTBS.ExecuteNonQuery();
}
cmd_Dawami.Parameters.AddWithValue("@INOUT", InOut);
try
{
int RowEf = cmd_Dawami.ExecuteNonQuery();
}
catch { }
//if (RowEf > 0)
//finish:
}
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
Con_Actatek.Open();
for (int i = 0; i < dt_Actatek.Rows.Count; i++)
{
cmd_Dawami.Parameters.Clear();
cmd_Dawami.Parameters.AddWithValue("@EMPLOYEEID", dt_Actatek.Rows[i]["USERID"].ToString());
cmd_Dawami.Parameters.AddWithValue("@LOGDATE", dt_Actatek.Rows[i]["ActionDate"].ToString());
cmd_Dawami.Parameters.AddWithValue("@LOGTIME", dt_Actatek.Rows[i]["actiontimes"].ToString());
cmd_Dawami.Parameters.AddWithValue("@TERMINALID", dt_Actatek.Rows[i]["TERMINALSN"].ToString());
if (dt_Actatek.Rows[i]["EVENTID"].ToString() == "IN")
{
cmd_Dawami.Parameters.AddWithValue("@INOUT", "0");
}
else
{
cmd_Dawami.Parameters.AddWithValue("@INOUT", "1");
}
try
{
int RowEf = cmd_Dawami.ExecuteNonQuery();
}
catch { }
cmd_Update_Actatek.Parameters.Clear();
cmd_Update_Actatek.Parameters.AddWithValue("@ID", dt_Actatek.Rows[i]["ID"].ToString());
cmd_Update_Actatek.ExecuteNonQuery();
}
Con_TBS.Close(); Con_Actatek.Close();
Con_Dawami.Close();
}
catch (Exception ee)
{
using (System.IO.StreamWriter file =
new System.IO.StreamWriter(@"C:\finger.txt", true))
{
file.WriteLine(ee.ToString());
}
}
}
}
}