private IEnumerable <EmployeeModel> ReadEmployeeWithDataTable(string predicate, params SqlParameter[] parameters)
{
DataTable table = new DataTable();
Sqlconn.Open();
var cmd = new SqlCommand("select * from Employee e " + predicate, Sqlconn);
foreach (var item in parameters)
{
cmd.Parameters.Add(item);
}
SqlDataAdapter Adapter = new SqlDataAdapter(cmd);
Adapter.Fill(table);
for (int i = 0; i < table.Rows.Count; i++)
{
yield return(new EmployeeModel
{
EmployeeId = table.Rows[i].Field <int>("EmployeeId"),
FirstName = table.Rows[i].Field <string>("FirstName"),
LastName = table.Rows[i].Field <string>("LastName")
});
}
Sqlconn.Close();
}
public IEnumerable <SecurityModel> GetDoorActivity(DateTime from, DateTime to, int doorId)
{
Sqlconn.Open();
SqlCommand cmd = new SqlCommand(@"select ah.AccessHistoryId,ah.AttemptDate,e.EmployeeId, e.FirstName,e.LastName,ah.Result from AccessHistory ah join Employee
e on ah.EmployeeId = e.EmployeeId where ah.DoorId=@doorId", Sqlconn);
cmd.Parameters.AddWithValue("@From", from);
cmd.Parameters.AddWithValue("@To", to);
cmd.Parameters.AddWithValue("@doorId", doorId);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
yield return(new SecurityModel
{
AccessHistoryId = reader.GetInt32(0),
AccessAttempt = reader.GetDateTime(1),
EmployeeId = reader.GetInt32(2),
FirstName = reader.GetString(3),
LastName = reader.GetString(4),
Result = reader.GetBoolean(5),
});
}
Sqlconn.Close();
}
public void DeleteEmployee(int Id)
{
Sqlconn.Open();
SqlCommand cmd = new SqlCommand("delete Employee Where EmployeeId=@Pk", Sqlconn);
cmd.Parameters.AddWithValue("@Pk", Id);
cmd.ExecuteNonQuery();
Sqlconn.Close();
}
public void AddCredential(SecurityModel model)
{
Sqlconn.Open();
SqlCommand cmd = new SqlCommand("insert SecurityDevice(SecurityDeviceType)values(@DeviceType);select scope_identity()", Sqlconn);
cmd.Parameters.AddWithValue("@DeviceType", model.Credential);
cmd.ExecuteNonQuery();
Sqlconn.Close();
}
public string GetCachedTree()
{
lock (Sqlconn)
{
var ser = new JavaScriptSerializer();
try
{
string ConfStr = ser.Serialize(conf);
string sqlquery = string.Format("Select SavedTreeJson from SavedTree where Configuration='{0}'", ConfStr);
Sqlconn.Open();
DataTable dtres = new DataTable();
using (SqlCommand comm = new SqlCommand(sqlquery, Sqlconn))
{
using (SqlDataAdapter da = new SqlDataAdapter(comm))
{
da.Fill(dtres);
}
}
if (dtres == null || dtres.Rows.Count == 0 || dtres.Rows[0][0] == null)
{
return(null);
}
string JsonTree = dtres.Rows[0][0].ToString();
//ISdmxObjects ret = GetSdmxOBJ(dtres.Rows[0][0].ToString());
try
{
string sqlupd = string.Format("Update SavedTree set LastRequest='{1}' where Configuration='{0}'", ConfStr, DateTime.Now.ToString("yyyyMMdd HHmm"));
using (SqlCommand commupd = new SqlCommand(sqlupd, Sqlconn))
commupd.ExecuteNonQuery();
}
catch (Exception)
{
return(null);
}
return(JsonTree);
}
catch (Exception)
{
return(null);
}
finally
{
Sqlconn.Close();
}
}
}
public void CreateEmployee(EmployeeModel model)
{
Sqlconn.Open();
// SqlCommand cmd2 = new SqlCommand(@"if exists(select * from Employee e where e.EmployeeId=@Pk)
//begin
//update Employee
//set FirstName = @FName,
//LastName = @LName where EmployeeId = @Pk
//end
//else
// begin
// insert Employee(FirstName, LastName)values(@FName, @LName)
//select scope_identity()
//end");
SqlCommand cmd = new SqlCommand("insert Employee(FirstName,LastName)values(@FName,@LName)", Sqlconn);
cmd.Parameters.AddWithValue("@Fname", model.FirstName);
cmd.Parameters.AddWithValue("@LName", model.LastName);
// cmd.Parameters.AddWithValue("@Pk", model.EmployeeId);
cmd.ExecuteNonQuery();
Sqlconn.Close();
}
private IEnumerable <EmployeeModel> ReadEmployee(string predicate, params SqlParameter[] parameters)
{
Sqlconn.Open();
var cmd = new SqlCommand("select * from Employee e " + predicate, Sqlconn);
foreach (var item in parameters)
{
cmd.Parameters.Add(item);
}
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
yield return(new EmployeeModel
{
EmployeeId = reader.GetInt32(0),
FirstName = reader.GetString(1),
LastName = reader.GetString(2)
});
}
Sqlconn.Close();
}
//private IEnumerable<SecurityModel> GetUnseccessfulAttempts(DateTime from, DateTime to)
//{
// SecurityModel model = new SecurityModel();
// Sqlconn.Open();
// SqlCommand cmd = new SqlCommand("select * from AccessHistory ah where ah.AttemptDate >=from and ah.AttemptDate <= to and ah.Result=0",Sqlconn);
// cmd.Parameters.AddWithValue("@from", model.AccessAttempt);
// cmd.Parameters.AddWithValue("@to", model.AccessAttempt);
// SqlDataReader reader = cmd.ExecuteReader();
// while (reader.Read())
// {
// yield return new SecurityModel
// {
// AccessHistoryId = reader.GetInt32(0),
// AccessAttempt = reader.GetDateTime(1),
// Result = reader.GetBoolean(2)
// };
// }
//}
//private IEnumerable<SecurityModel> GetSuccessfulAttempts2(DateTime from, DateTime to)
//{
// SecurityModel model = new SecurityModel();
// Sqlconn.Open();
// SqlCommand cmd = new SqlCommand("select * from AccessHistory ah where ah.AttemptDate >=from and ah.AttemptDate <= to and ah.Result=1",Sqlconn);
// cmd.Parameters.AddWithValue("@from", model.AccessAttempt);
// cmd.Parameters.AddWithValue("@to", model.AccessAttempt);
// SqlDataReader reader = cmd.ExecuteReader();
// while (reader.Read())
// {
// yield return new SecurityModel
// {
// AccessHistoryId = reader.GetInt32(0),
// AccessAttempt = reader.GetDateTime(1),
// Result = reader.GetBoolean(2)
// };
// }
//}
//public IEnumerable<SecurityModel> GetSuspiciousAttempts(DateTime from, DateTime to)
//{
// SecurityModel model = new SecurityModel();
// var Query1 = GetUnseccessfulAttempts(from, to);
// var Query2 = GetSuccessfulAttempts2(from, to);
// foreach (var failure in Query1)
// {
// foreach (var success in Query2)
// {
// if(failure.EmployeeId==success.EmployeeId&&failure.DoorId==success.DoorId&&failure.AccessAttempt<success.AccessAttempt.AddMinutes(2))
// {
// yield return new SecurityModel
// {
// AccessHistoryId = failure.AccessHistoryId,
// AccessAttempt = failure.AccessAttempt,
// Door = failure.Door,
// FirstName = failure.FirstName,
// LastName = failure.LastName,
// Result = failure.Result
// };
// }
// }
// }
//Continue this we need to compare the 2 queries and see if the 2nd 1 has a true less then 2 minutes after a false on the 1st list and exclude it from the result
public IEnumerable <SecurityModel> GetSuspiciousAttempts(DateTime from, DateTime to)
{
SecurityModel model = new SecurityModel();
Sqlconn.Open();
SqlCommand cmd = new SqlCommand("select * from AccessHistory ah where ah.AttemptDate >=from and ah.AttemptDate <= to and ah.Result=0", Sqlconn);
cmd.Parameters.AddWithValue("@from", model.AccessAttempt);
cmd.Parameters.AddWithValue("@to", model.AccessAttempt);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
model.AccessHistoryId = reader.GetInt32(0);
var Attempt = model.AccessAttempt = reader.GetDateTime(1);
var EmpId1 = model.EmployeeId = reader.GetInt32(2);
model.FirstName = reader.GetString(3);
model.LastName = reader.GetString(4);
var door1 = model.DoorId = reader.GetInt32(5);
model.Result = reader.GetBoolean(6);
var Plus2minutes = Attempt.AddMinutes(2);
SqlCommand cmnd = new SqlCommand("select * from AccessHistory ah where ah.AttemptDate >=from and ah.AttemptDate <= to", Sqlconn);
SqlDataReader rdr = cmnd.ExecuteReader();
while (rdr.Read())
{
model.AccessHistoryId = reader.GetInt32(0);
var Attempt2 = model.AccessAttempt = reader.GetDateTime(1);
var EmpId2 = model.EmployeeId = reader.GetInt32(2);
model.FirstName = reader.GetString(3);
model.LastName = reader.GetString(4);
var door2 = model.DoorId = reader.GetInt32(5);
var Result = model.Result = reader.GetBoolean(6);
//We also need if there is no result
if (door1 == door2 && EmpId1 == EmpId2 && Attempt2 > Plus2minutes || Attempt2 < Plus2minutes && Result == false)
{
}
}
}
}
public void ThreadSaveCachedTree(object TObject)
{
string JsonTree = (string)TObject;
lock (Sqlconn)
{
var ser = new JavaScriptSerializer();
try
{
Sqlconn.Open();
string ConfStr = ser.Serialize(conf);
//string TreeName = string.Format("tree_{0}.json", Guid.NewGuid());
//SaveSdmxOBJ(SdmxOBJ, TreeName);
string sqlcount = string.Format("Select count(*) from SavedTree where Configuration='{0}'", ConfStr.Replace("'", "''"));
int conta = 0;
using (SqlCommand commconta = new SqlCommand(sqlcount, Sqlconn))
conta = Convert.ToInt32(commconta.ExecuteScalar());
if (conta == 0)
{
string sqlupd = string.Format("INSERT INTO SavedTree (Configuration, SavedTreeJson, LastUpdate, LastRequest) VALUES ('{0}', '{1}', '{2}', '{2}')"
, ConfStr.Replace("'", "''"), JsonTree.Replace("'", "''"), DateTime.Now.ToString("yyyyMMdd HHmm"));
using (SqlCommand commupd = new SqlCommand(sqlupd, Sqlconn))
commupd.ExecuteNonQuery();
}
}
catch (Exception)
{
return;
}
finally
{
Sqlconn.Close();
}
}
}