public int Insert(ZKTecoHourAssistanceML hoursAssistanceML, String ConnectionString)
{
try
{
if (hoursAssistanceML != null)
{
StringBuilder Query = new StringBuilder();
Query.AppendFormat("INSERT INTO {0} (", TableName);
Query.AppendFormat(" {0}, ", ZKTecoHourAssistanceML.DataBase.machineNumber);
Query.AppendFormat(" {0}, ", ZKTecoHourAssistanceML.DataBase.idUser);
Query.AppendFormat(" {0}, ", ZKTecoHourAssistanceML.DataBase.verifyType);
Query.AppendFormat(" {0}, ", ZKTecoHourAssistanceML.DataBase.verifyState);
Query.AppendFormat(" {0}, ", ZKTecoHourAssistanceML.DataBase.workCode);
Query.AppendFormat(" {0}, ", ZKTecoHourAssistanceML.DataBase.dateTimeRecord);
Query.AppendFormat(" {0}, ", ZKTecoHourAssistanceML.DataBase.dateOnlyRecord);
Query.AppendFormat(" {0}, ", ZKTecoHourAssistanceML.DataBase.timeOnlyRecord);
Query.AppendLine(" _registry, idUserInsert, dateInsert");
Query.AppendLine(") VALUES( ");
Query.AppendFormat(" {0}, ", hoursAssistanceML.MachineNumber);
Query.AppendFormat(" {0}, ", hoursAssistanceML.IdUser);
Query.AppendFormat(" {0}, ", hoursAssistanceML.VerifyType);
Query.AppendFormat(" {0}, ", hoursAssistanceML.VerifyState);
Query.AppendFormat(" {0}, ", hoursAssistanceML.WorkCode);
Query.AppendFormat(" Format(convert(DateTime, '{0}'), 'yyyy-MM-dd HH:mm:ss'), ", hoursAssistanceML.DateTimeRecord);
Query.AppendFormat(" Format(convert(Date, '{0}'), 'yyyy-MM-dd'), ", hoursAssistanceML.DateTimeRecord);
Query.AppendFormat(" convert(Time, '{0}'), ", hoursAssistanceML.DateTimeRecord);
Query.AppendLine(" 1, ");
Query.AppendFormat(" {0}, ", idUser);
Query.AppendLine(" GETDATE()) ");
Query.AppendLine(" SELECT CAST(scope_identity() AS int)");
SqlConnection Conexion = new SqlConnection
{
ConnectionString = ConnectionString
};
using (SqlCommand cmd2 = new SqlCommand(Query.ToString(), Conexion))
{
Conexion.Open();
int newID = (Int32)cmd2.ExecuteScalar();
if (Conexion.State == System.Data.ConnectionState.Open)
{
Conexion.Close();
}
return(newID);
}
}
return(0);
}
catch (Exception ex)
{
throw new Exception(String.Format("{0}.Insert : {1}", core, ex));
}
}
public int save(ZKTecoHourAssistanceML hourAssistanceML)
{
try
{
if (hourAssistanceML.Id > 0)
{
return(zkTecoHourAssistanceDAL.Update(hourAssistanceML, ConnectionStrings));
}
else
{
return(zkTecoHourAssistanceDAL.Insert(hourAssistanceML, ConnectionStrings));
}
}
catch (Exception ex)
{
throw new Exception(String.Format("{0}.Save: {1}", core, ex));
}
}
public int Update(ZKTecoHourAssistanceML hoursAssistanceML, String ConnectionString)
{
try
{
if (hoursAssistanceML != null)
{
StringBuilder Query = new StringBuilder();
Query.AppendFormat("UPDATE {0} SET ", TableName);
Query.AppendFormat(" {0} = {1}, ", ZKTecoHourAssistanceML.DataBase.machineNumber, hoursAssistanceML.MachineNumber);
Query.AppendFormat(" {0} = {1}, ", ZKTecoHourAssistanceML.DataBase.idUser, hoursAssistanceML.IdUser);
Query.AppendFormat(" {0} = {1}, ", ZKTecoHourAssistanceML.DataBase.verifyType, hoursAssistanceML.VerifyType);
Query.AppendFormat(" {0} = {1}, ", ZKTecoHourAssistanceML.DataBase.verifyState, hoursAssistanceML.VerifyState);
Query.AppendFormat(" {0} = {1}, ", ZKTecoHourAssistanceML.DataBase.workCode, hoursAssistanceML.WorkCode);
Query.AppendFormat(" {0} = Format(convert(DateTime, '{1}'), 'yyyy-MM-dd HH:mm:ss'), ", ZKTecoHourAssistanceML.DataBase.dateTimeRecord, hoursAssistanceML.DateTimeRecord);
Query.AppendFormat(" {0} = Format(convert(Date, '{1}'), yyyy-MM-dd), ", ZKTecoHourAssistanceML.DataBase.dateOnlyRecord, hoursAssistanceML.DateTimeRecord);
Query.AppendFormat(" {0} = convert(Time, '{1}'), ", ZKTecoHourAssistanceML.DataBase.timeOnlyRecord, hoursAssistanceML.DateTimeRecord);
Query.AppendFormat(" idUserUpdate = {0}, ", idUser);
Query.AppendLine(" dateUpdate = GETDATE() ");
Query.AppendFormat(" WHERE id = {0}' ", hoursAssistanceML.Id);
SqlConnection Conexion = new SqlConnection()
{
ConnectionString = ConnectionString
};
Conexion.Open();
SqlCommand cmd2 = new SqlCommand(Query.ToString(), Conexion);
cmd2.ExecuteNonQuery();
Conexion.Close();
return(hoursAssistanceML.Id);
}
return(0);
}
catch (Exception ex)
{
throw new Exception(String.Format("{0}.Insert : {1}", core, ex));
}
}
/// <summary>
/// Función que lee todos los Logs de Registros del Lector y los Migra a la Base de Datos
/// </summary>
/// <returns>Total de Registros migrados</returns>
public List <string> MigrateHoursToBD()
{
try
{
ZKTecoDeviceBLL zKTecoDeviceBLL = new ZKTecoDeviceBLL();
if (zKTecoDeviceBLL.connect())
{
ICollection <HoursAssistanceInfo> hoursAssistances = zKTecoDeviceBLL.GetLogData();
int count = 0;
string firstDate = string.Empty;
string lastDate = string.Empty;
List <string> dateMigrate = new List <string>();
if (hoursAssistances != null && hoursAssistances.Count > 0)
{
foreach (HoursAssistanceInfo ihoursAssistance in hoursAssistances)
{
if (count == 0)
{
firstDate = DateTime.Parse(ihoursAssistance.DateTimeRecord).ToString("yyyy-MM-dd HH:mm:ss");
}
lastDate = DateTime.Parse(ihoursAssistance.DateTimeRecord).ToString("yyyy-MM-dd HH:mm:ss");
ZKTecoHourAssistanceML zKTecoHourAssistanceML = new ZKTecoHourAssistanceML()
{
MachineNumber = ihoursAssistance.MachineNumber,
IdUser = ihoursAssistance.IndRegID,
VerifyState = ihoursAssistance.VerifyState,
VerifyType = ihoursAssistance.VerifyType,
WorkCode = ihoursAssistance.WorkCode,
DateTimeRecord = lastDate
};
zkTecoHourAssistanceDAL.Insert(zKTecoHourAssistanceML, ConnectionStrings);
count++;
Console.WriteLine("Registro {0}", count);
}
dateMigrate.Add(firstDate);
dateMigrate.Add(lastDate);
//MigrationHistoryBLL migrationHistoryBLL = new MigrationHistoryBLL();
//MigrationHistoryML migrationHistoryML = new MigrationHistoryML()
//{
// DateStart = DateTime.Parse(firstDate),
// DateEnd = DateTime.Parse(lastDate)
//};
//migrationHistoryBLL.Save(migrationHistoryML);
}
if (count > 0)
{
return(dateMigrate);
}
return(null);
}
else
{
throw new Exception("Error al conectar con el lector");
}
}
catch (Exception ex)
{
throw new Exception(String.Format("MigrateHoursToBD: {0}", ex.Message));
}
}
/// <summary>
/// Función que lee los Logs de Registros del Lector por rango de fecha y los Migra a la Base de Datos
/// </summary>
/// <param name="startDate">Fecha de Inicio</param>
/// <param name="endDate">Fecha Fin</param>
/// <returns>Total de registros migrados</returns>
public int MigrateHoursToBD(string startDate, string endDate)
{
try
{
if (string.IsNullOrEmpty(startDate) || string.IsNullOrEmpty(endDate))
{
throw new Exception("MigrateHoursToBD: No se encontró la Fecha Inicio/Fecha Fin");
}
string _startDate = DateTime.Parse(startDate).ToString("yyyy-MM-dd hh:mm:ss");
string _endDate = DateTime.Parse(endDate).ToString("yyyy-MM-dd hh:mm:ss");
ZKTecoDeviceBLL zKTecoDeviceBLL = new ZKTecoDeviceBLL();
if (zKTecoDeviceBLL.connect())
{
ICollection <HoursAssistanceInfo> hoursAssistances = zKTecoDeviceBLL.GetLogData(_startDate, _endDate);
int count = 0;
if (hoursAssistances != null && hoursAssistances.Count > 0)
{
foreach (HoursAssistanceInfo ihoursAssistance in hoursAssistances)
{
ZKTecoHourAssistanceML zKTecoHourAssistanceML = new ZKTecoHourAssistanceML()
{
MachineNumber = ihoursAssistance.MachineNumber,
IdUser = ihoursAssistance.IndRegID,
VerifyState = ihoursAssistance.VerifyState,
VerifyType = ihoursAssistance.VerifyType,
WorkCode = ihoursAssistance.WorkCode,
DateTimeRecord = DateTime.Parse(ihoursAssistance.DateTimeRecord).ToString("yyyy-MM-dd HH:mm:ss")
};
zkTecoHourAssistanceDAL.Insert(zKTecoHourAssistanceML, ConnectionStrings);
count++;
Console.WriteLine("Registro {0}", count);
}
//MigrationHistoryBLL migrationHistoryBLL = new MigrationHistoryBLL();
//MigrationHistoryML migrationHistoryML = new MigrationHistoryML()
//{
// DateStart = startDate,
// DateEnd = endDate
//};
//migrationHistoryBLL.Save(migrationHistoryML);
}
return(count);
}
else
{
throw new Exception("Error al conectar con el lector");
}
}
catch (Exception ex)
{
throw new Exception(String.Format("MigrateHoursToBD: {0}", ex.Message));
}
}
