318 lines
14 KiB
C#
318 lines
14 KiB
C#
using ADSUtileriasWF.Helpers;
|
|
using DocumentFormat.OpenXml.Wordprocessing;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.ComponentModel;
|
|
using System.Data;
|
|
using System.Drawing;
|
|
using System.Text;
|
|
using System.Windows.Forms;
|
|
|
|
namespace ADSUtileriasWF.Views.RelojChecador.Gluyas
|
|
{
|
|
public partial class ReporteChecadores : Form
|
|
{
|
|
|
|
DataTable DtAsistencia;
|
|
private void ReporteChecadores_Load(object sender, EventArgs e)
|
|
{
|
|
|
|
loadchecadores(); LoadReportes();
|
|
}
|
|
|
|
public void loadchecadores()
|
|
{
|
|
DataTable dtChecadores = new DataTable();
|
|
ClsBD chk = new ClsBD();
|
|
string sqlEjecuta = "SELECT * FROM CHECADORES;";
|
|
dtChecadores = chk.EjecutaSqlDT(sqlEjecuta);
|
|
DataTable dt = new DataTable();
|
|
sqlEjecuta = "SELECT IDCHECADOR,NOMBRE FROM CHECADORES UNION SELECT 0 AS IDCHECADOR, 'Seleccione checador...' AS NOMBRE ORDER BY IDCHECADOR,NOMBRE;";
|
|
dt = chk.EjecutaSqlDT(sqlEjecuta);
|
|
|
|
DdlChecadores.DataSource = dt;
|
|
DdlChecadores.DisplayMember = "nombre";
|
|
DdlChecadores.ValueMember = "IDCHECADOR";
|
|
DdlChecadores.Refresh();
|
|
chk.Desconectar();
|
|
}
|
|
public void LoadReportes()
|
|
{
|
|
|
|
DataTable dt = new DataTable();
|
|
ClsBD chk = new ClsBD();
|
|
string sqlEjecuta = "SELECT 0 AS IdReporte, 'Asistencia' AS Nombre ORDER BY IdReporte;";
|
|
dt = chk.EjecutaSqlDT(sqlEjecuta);
|
|
|
|
DdlReporte.DataSource = dt;
|
|
DdlReporte.DisplayMember = "Nombre";
|
|
DdlReporte.ValueMember = "IdReporte";
|
|
DdlReporte.Refresh();
|
|
chk.Desconectar();
|
|
}
|
|
public ReporteChecadores()
|
|
{
|
|
InitializeComponent();
|
|
}
|
|
|
|
|
|
private void BtnGenerar_Click(object sender, EventArgs e)
|
|
{
|
|
DataTable dt_periodLog = new DataTable("dt_periodLog");
|
|
DtAsistencia = new DataTable("Asistencia");
|
|
const string formatfecha = "yyyy/MM/dd HH:mm:ss";
|
|
DateTime dfechai = txtFechaInicio.Text.ToDateTime();
|
|
DateTime dfechaf = txtFechaFin.Text.ToDateTime();
|
|
if (dfechai <= dfechaf)
|
|
{
|
|
DateTime datei = dfechai.ToString("yyyy/MM/dd").ToDateTime();
|
|
DateTime datef = dfechaf.ToString("yyyy/MM/dd").ToDateTime();
|
|
DateTime date = datei.AddDays(-1);
|
|
string fechai = dfechai.ToString(formatfecha);
|
|
string fechaf = dfechaf.ToString(formatfecha);
|
|
List<string> listafecha = new List<string>();
|
|
List<string[]> ListEmpleados = new List<string[]>();
|
|
while ((date = date.AddDays(1)) <= datef)
|
|
{
|
|
string fecha = $"{date.ToString("dddd", new System.Globalization.CultureInfo("es-MX"))}\r\n{date:dd-MM-yyyy}";
|
|
listafecha.Add(fecha);
|
|
}
|
|
|
|
this.DTGVReporte.AutoSizeColumnsMode = listafecha.Count <= 7 ? System.Windows.Forms.DataGridViewAutoSizeColumnsMode.Fill : System.Windows.Forms.DataGridViewAutoSizeColumnsMode.None;
|
|
DTGVReporte.AutoGenerateColumns = true;
|
|
DTGVReporte.Columns.Clear();
|
|
dt_periodLog.Columns.Add("NOEMPLEADO", System.Type.GetType("System.String"));
|
|
dt_periodLog.Columns.Add("FECHA", System.Type.GetType("System.String"));
|
|
dt_periodLog.Columns.Add("HORA", System.Type.GetType("System.String"));
|
|
|
|
//clsFuncionesChk clsFunciones = new clsFuncionesChk();
|
|
//clsGlobales extras = new clsGlobales();
|
|
//extras.ShowStatusBar("Generando informe...espere por favor", true, lblBarra);
|
|
//lblBarra.Refresh();
|
|
string sdwEnrollNumber = "";
|
|
int idwMachin = 1;
|
|
int idwVerifyMode = 0;
|
|
int idwInOutMode = 0;
|
|
int idwYear = 0;
|
|
int idwMonth = 0;
|
|
int idwDay = 0;
|
|
int idwHour = 0;
|
|
int idwMinute = 0;
|
|
int idwSecond = 0;
|
|
int idwWorkcode = 0;
|
|
zkemkeeper.CZKEM conectarse = new zkemkeeper.CZKEM();
|
|
bool conectado = false;
|
|
try
|
|
{
|
|
conectado = conectarse.Connect_Net(TxtSelectIP.Text, TxtSelecPuerto.Text.ToEntero());
|
|
|
|
bool isenabledevice = conectarse.EnableDevice(idwMachin, false);
|
|
DataTable dtEmpleados = GetEmpleados(conectarse, idwMachin);
|
|
|
|
bool lectura = conectarse.ReadTimeGLogData(idwMachin, fechai, fechaf);
|
|
|
|
while (conectarse.SSR_GetGeneralLogData(idwMachin, out sdwEnrollNumber, out idwVerifyMode, out idwInOutMode, out idwYear, out idwMonth, out idwDay, out idwHour, out idwMinute, out idwSecond, idwWorkcode))
|
|
{
|
|
DataRow dr = dt_periodLog.NewRow();
|
|
dr["NOEMPLEADO"] = sdwEnrollNumber;
|
|
DateTime dfecha = $"{idwYear}-{(idwMonth < 10 ? "0" : "")}{idwMonth}-{(idwDay < 10 ? "0" : "")}{ idwDay} {(idwHour < 10 ? "0" : "")}{idwHour}:{(idwMinute < 10 ? "0" : "")}{idwMinute}".ToDateTime();
|
|
if (dfecha >= dfechai && dfecha <= dfechaf)
|
|
{
|
|
if (ListEmpleados.FindAll(x => x[0] == sdwEnrollNumber).Count == 0)
|
|
{
|
|
string nombre = "";
|
|
foreach (DataRow item in dtEmpleados.Select($"NOEMPLEADO = {sdwEnrollNumber}"))
|
|
{
|
|
nombre = item["NOMBRE"].ToString();
|
|
}
|
|
ListEmpleados.Add(new string[] { sdwEnrollNumber, nombre });
|
|
}
|
|
string fecha = $"{dfecha.ToString("dddd", new System.Globalization.CultureInfo("es-MX"))}\r\n{dfecha:dd-MM-yyyy}";
|
|
//if (listafecha.FindAll(x => x == fecha).Count == 0) { listafecha.Add(fecha); }
|
|
dr["FECHA"] = fecha;// + " " + idwHour.ToString() + ":" + idwMinute.ToString() + ":" + idwSecond.ToString();
|
|
dr["HORA"] = $"{(idwHour < 10 ? "0" : "")}{idwHour}:{(idwMinute < 10 ? "0" : "")}{idwMinute}";// + ":" + idwSecond.ToString();
|
|
dt_periodLog.Rows.Add(dr);
|
|
}
|
|
}
|
|
|
|
DtAsistencia.Columns.Add("Código", System.Type.GetType("System.String"));
|
|
DtAsistencia.Columns.Add("Nombre", System.Type.GetType("System.String"));
|
|
|
|
foreach (var item in listafecha)
|
|
{
|
|
DtAsistencia.Columns.Add(item, System.Type.GetType("System.String"));
|
|
|
|
|
|
}
|
|
dt_periodLog = dt_periodLog.DefaultView.ToTable(true, "NOEMPLEADO", "FECHA", "HORA");
|
|
foreach (var user in ListEmpleados)
|
|
{
|
|
DataRow dr = DtAsistencia.NewRow();
|
|
dr["Código"] = user[0];
|
|
dr["Nombre"] = user[1];
|
|
double numero = 0;
|
|
string colname, oldcolname;
|
|
oldcolname = "";
|
|
foreach (DataRow item in dt_periodLog.Select($"NOEMPLEADO = {user[0]}"))
|
|
{
|
|
colname = item["FECHA"].ToString();
|
|
if (colname != oldcolname) { numero = 0; }
|
|
dr[colname] = $"{dr[colname]}{(numero == 0 ? "" : ((numero % 2) == 0) ? "\r\n" : " - ")}{item["HORA"]}";
|
|
numero = numero + 1;
|
|
oldcolname = colname;
|
|
}
|
|
DtAsistencia.Rows.Add(dr);
|
|
}
|
|
|
|
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
MessageBox.Show("Error al conectarse al checador " + ex.Message.ToString());
|
|
}
|
|
finally
|
|
{
|
|
if (conectado) { conectarse.EnableDevice(idwMachin, true); conectarse.Disconnect(); }
|
|
}
|
|
|
|
|
|
}
|
|
else
|
|
{
|
|
|
|
MessageBox.Show("la fecha inicio no puede ser mayor a la fecha fin ");
|
|
}
|
|
DTGVReporte.DataSource = DtAsistencia;
|
|
DTGVReporte.Refresh();
|
|
}
|
|
private DataTable GetEmpleados(zkemkeeper.CZKEM conectarse, int iMachineNumber = 1)
|
|
{
|
|
DataTable dtEmpleados = new DataTable();
|
|
int idwErrorCode = 0;
|
|
try
|
|
{
|
|
dtEmpleados.Columns.Add("NOEMPLEADO");
|
|
dtEmpleados.Columns.Add("NOMBRE");
|
|
dtEmpleados.Columns.Add("ACTIVO");
|
|
|
|
string sEnrollNumber = "";
|
|
string sName = "";
|
|
string sPassword = "";
|
|
string sCardnumber = "";
|
|
int iPrivilege = 0;
|
|
bool bEnabled = false;
|
|
int num = 0;
|
|
int index = 0;
|
|
|
|
bool readallinfo = conectarse.ReadAllUserID(iMachineNumber); // ;//read all the user information To the memory
|
|
if (readallinfo)
|
|
{
|
|
while (conectarse.SSR_GetAllUserInfo(iMachineNumber, out sEnrollNumber, out sName, out sPassword, out iPrivilege, out bEnabled))
|
|
{
|
|
|
|
conectarse.GetStrCardNumber(out sCardnumber);
|
|
// If conectarse.GetUserFaceStr(1, sEnrollNumber, iFaceIndex, sTmpData, iLength) Then
|
|
//personal.CvePersonal = sCardnumber;
|
|
|
|
DataRow Renglon = dtEmpleados.NewRow();
|
|
Renglon["NOEMPLEADO"] = sEnrollNumber;
|
|
Renglon["NOMBRE"] = sName;
|
|
Renglon["ACTIVO"] = (bEnabled ? 1 : 0);
|
|
dtEmpleados.Rows.Add(Renglon);
|
|
// While conectarse.GetUserTmp(iMachineNumber, sEnrollNumber, dwFingerIndex, TmpData, TmpLength)
|
|
// entre = True
|
|
// End While
|
|
// lvUserInfo.Items(index).SubItems.Add(sName)
|
|
// lvUserInfo.Items(index).SubItems.Add(sPassword)
|
|
// lvUserInfo.Items(index).SubItems.Add(iPrivilege.ToString())
|
|
// lvUserInfo.Items(index).SubItems.Add(iLength.ToString())
|
|
// lvUserInfo.Items(index).SubItems.Add(sTmpData)
|
|
index += 1;
|
|
num += 1;
|
|
}
|
|
}
|
|
else { conectarse.GetLastError(idwErrorCode); }
|
|
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
MessageBox.Show("Error al conectarse al checador " + ex.Message.ToString());
|
|
}
|
|
return dtEmpleados;
|
|
}
|
|
|
|
private void DdlChecadores_SelectedIndexChanged(object sender, EventArgs e)
|
|
{
|
|
|
|
int IDCHECADOR = DdlChecadores.SelectedValue.ToString().ToEntero();
|
|
DataTable dt = new DataTable();
|
|
ClsBD chk = new ClsBD();
|
|
string sqlEjecuta = $"SELECT IPCHECADOR,PUERTO FROM CHECADORES WHERE IDCHECADOR={IDCHECADOR};";
|
|
dt = chk.EjecutaSqlDT(sqlEjecuta);
|
|
chk.Desconectar();
|
|
TxtSelectIP.Text = "";
|
|
TxtSelecPuerto.Text = "";
|
|
if (dt.Rows.Count > 0)
|
|
{
|
|
TxtSelectIP.Text = dt.Rows[0]["IPCHECADOR"].ToString();
|
|
TxtSelecPuerto.Text = dt.Rows[0]["PUERTO"].ToString();
|
|
|
|
}
|
|
}
|
|
|
|
private void BtnDescargar_Click(object sender, EventArgs e)
|
|
{
|
|
var wb = new ClosedXML.Excel.XLWorkbook();
|
|
// Add a DataTable as a worksheet
|
|
var wsDep = wb.Worksheets.Add(DtAsistencia);
|
|
wsDep.Columns().AdjustToContents();
|
|
//System.IO.Stream myStream = null;
|
|
//wb.SaveAs(myStream);
|
|
|
|
//wb.SaveAs("AddingDataTableAsWorksheet.xlsx");
|
|
SaveFileDialog SaveFileDialog1 = new SaveFileDialog();
|
|
savefileAs(wb, "Asistencia.xlsx", SaveFileDialog1);
|
|
}
|
|
private void savefileAs(ClosedXML.Excel.XLWorkbook wb,string FileName, SaveFileDialog SaveFileDialog1)
|
|
{
|
|
|
|
// Set the properties on SaveFileDialog1 so the user is
|
|
// prompted to create the file if it doesn't exist
|
|
// or overwrite the file if it does exist.
|
|
SaveFileDialog1.CreatePrompt = true;
|
|
SaveFileDialog1.OverwritePrompt = true;
|
|
|
|
// Set the file name to myText.txt, set the type filter
|
|
// to text files, and set the initial directory to the
|
|
// MyDocuments folder.
|
|
SaveFileDialog1.FileName = FileName;
|
|
// DefaultExt is only used when "All files" is selected from
|
|
// the filter box and no extension is specified by the user.
|
|
SaveFileDialog1.DefaultExt = "xlsx";
|
|
SaveFileDialog1.Filter =
|
|
"Excel (*.xlsx)|*.txt|All files (*.*)|*.*";
|
|
SaveFileDialog1.InitialDirectory =
|
|
Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
|
|
|
|
// Call ShowDialog and check for a return value of DialogResult.OK,
|
|
// which indicates that the file was saved.
|
|
DialogResult result = SaveFileDialog1.ShowDialog();
|
|
System.IO.Stream fileStream;
|
|
|
|
if (result == DialogResult.OK)
|
|
{
|
|
// Open the file, copy the contents of memoryStream to fileStream,
|
|
// and close fileStream. Set the memoryStream.Position value to 0
|
|
// to copy the entire stream.
|
|
fileStream = SaveFileDialog1.OpenFile();
|
|
wb.SaveAs(fileStream);
|
|
/*myStream.Position = 0;
|
|
myStream.WriteTo(fileStream);*/
|
|
fileStream.Close();
|
|
}
|
|
}
|
|
}
|
|
}
|