public bool ModificarDet(Mail_RolesProcesadosDet_Info info)
{
try
{
using (SqlConnection connection = new SqlConnection(builder.ToString()))
{
connection.Open();
string QueryUpdate =
"update Mail_RolesProcesadosDet set FechaEnvio = getdate(), Error = '" + info.Error + "' "
+ "where ID = @Id and Secuencia = @Secuencia";
SqlCommand commandUpdate = new SqlCommand(QueryUpdate, connection);
commandUpdate.Parameters.Add("@Id", SqlDbType.Decimal).Value = info.ID;
commandUpdate.Parameters.Add("@Secuencia", SqlDbType.Int).Value = info.Secuencia;
commandUpdate.ExecuteNonQuery();
connection.Close();
}
return(true);
}
catch (Exception ex)
{
GuardarExcepcion(new Mail_LogError_Info
{
Metodo = "ModificarDet",
Error = ex == null ? string.Empty : (ex.Message ?? string.Empty),
InnerException = ex == null || ex.InnerException == null ? string.Empty : (ex.InnerException.Message.Length > 1000 ? ex.InnerException.Message.Substring(0, 1000) : ex.InnerException.Message)
});
return(true);
}
}
public List <Mail_RolesProcesados_Info> GetCabecerasSinProcesar()
{
try
{
#region Querys
string querySelect =
"select cro_numero,cro_empre,cro_fecha,cro_usuari,cro_fecini,cro_fecfin,cro_tiprol,cro_observ,cro_tipo,cro_liq_motivo,cro_liq_refere,cro_contab,ID "
+ "from tb_Cabrol A "
+ "where not exists( "
+ "select b.ID from Mail_RolesProcesados B "
+ "where A.ID = B.ID "
+ ")and a.cro_tiprol = 7 and a.cro_tipo = 'NOM'";
string queryInsert =
"INSERT INTO [dbo].[Mail_RolesProcesados] "
+ "([ID],[FechaInicio],[TotalEmpleados],[TotalProcesados]) "
+ "VALUES(@ID, @FechaInicio, @TotalEmpleados, @TotalProcesados)";
string queryInsertDet =
"INSERT INTO [dbo].[Mail_RolesProcesadosDet] "
+ "([ID],[Secuencia],[pre_empre],[pre_codtra],[trb_email]) "
+ "VALUES(@ID, @Secuencia, @pre_empre, @pre_codtra, @trb_email)";
#endregion
#region Variables
List <Mail_RolesProcesados_Info> Lista = new List <Mail_RolesProcesados_Info>();
#endregion
using (SqlConnection connection = new SqlConnection(builder.ToString()))
{
connection.Open();
#region Select
SqlCommand commandSelect = new SqlCommand(querySelect, connection);
SqlDataReader reader = commandSelect.ExecuteReader();
while (reader.Read())
{
Lista.Add(new Mail_RolesProcesados_Info
{
ID = Convert.ToDecimal(reader[12]),
FechaInicio = DateTime.Now,
TotalEmpleados = 0,
TotalProcesados = 0,
IdEmpresa = Convert.ToInt32(reader[1]),
IdRol = Convert.ToInt32(reader[0])
});
}
reader.Close();
#endregion
#region Insert
foreach (var item in Lista)
{
#region Procedure
string queryProcedure = "EXEC [rrhh].[dbo].[rep_sob_lista] {0}, 7, {1}, 'D', 1";
queryProcedure = queryProcedure.Replace("{0}", item.IdEmpresa.ToString());
queryProcedure = queryProcedure.Replace("{1}", item.IdRol.ToString());
SqlCommand commandProcedure = new SqlCommand(queryProcedure, connection);
SqlDataReader readerProcedure = commandProcedure.ExecuteReader();
item.Lista = new List <Mail_RolesProcesadosDet_Info>();
int Secuencia = 1;
while (readerProcedure.Read())
{
Mail_RolesProcesadosDet_Info det = new Mail_RolesProcesadosDet_Info
{
ID = item.ID,
Secuencia = Secuencia++,
pre_empre = Convert.ToInt32(readerProcedure[0]),
pre_codtra = Convert.ToInt32(readerProcedure[2]),
trb_email = Convert.ToString(readerProcedure[15]),
infoRpt = new Reporte01_Info
{
pre_empresa = Convert.ToInt32(readerProcedure[0]),
pre_codtra = Convert.ToInt32(readerProcedure[2]),
pre_rol = Convert.ToInt32(readerProcedure[3]),
trb_email = Convert.ToString(readerProcedure[15]),
emp_nombre = Convert.ToString(readerProcedure[16]),
cgo_descri = Convert.ToString(readerProcedure[11]),
sub_nombre = Convert.ToString(readerProcedure[10]),
trb_cen = Convert.ToString(readerProcedure[9]),
trb_are = Convert.ToString(readerProcedure[8]),
trb_div = Convert.ToString(readerProcedure[7]),
NOM = Convert.ToString(readerProcedure[6]),
cro_fecini = Convert.ToDateTime(readerProcedure[4]),
cro_fecfin = Convert.ToDateTime(readerProcedure[5]),
ING = Convert.ToDouble(readerProcedure[12]),
EGR = Math.Abs(Convert.ToDouble(readerProcedure[13]))
}
};
det.infoRpt.Saldo = det.infoRpt.ING - det.infoRpt.EGR;
item.Lista.Add(det);
}
item.TotalEmpleados = item.Lista.Count;
readerProcedure.Close();
#endregion
SqlCommand commandInsert = new SqlCommand(queryInsert, connection);
commandInsert.Parameters.Add("@ID", SqlDbType.Decimal).Value = item.ID;
commandInsert.Parameters.Add("@FechaInicio", SqlDbType.DateTime).Value = item.FechaInicio;
commandInsert.Parameters.Add("@TotalEmpleados", SqlDbType.Int).Value = item.TotalEmpleados;
commandInsert.Parameters.Add("@TotalProcesados", SqlDbType.Int).Value = item.TotalProcesados;
commandInsert.ExecuteNonQuery();
#region Insert detalle
foreach (var itemDet in item.Lista)
{
SqlCommand commandInsertDet = new SqlCommand(queryInsertDet, connection);
commandInsertDet.Parameters.Add("@ID", SqlDbType.Decimal).Value = itemDet.ID;
commandInsertDet.Parameters.Add("@Secuencia", SqlDbType.Int).Value = itemDet.Secuencia;
commandInsertDet.Parameters.Add("@pre_empre", SqlDbType.Int).Value = itemDet.pre_empre;
commandInsertDet.Parameters.Add("@pre_codtra", SqlDbType.Int).Value = itemDet.pre_codtra;
commandInsertDet.Parameters.Add("@trb_email", SqlDbType.Text).Value = itemDet.trb_email;
commandInsertDet.ExecuteNonQuery();
}
#endregion
}
#endregion
connection.Close();
}
return(Lista);
}
catch (Exception ex)
{
GuardarExcepcion(new Mail_LogError_Info
{
Metodo = "GetCabecerasSinProcesar",
Error = ex == null ? string.Empty : (ex.Message ?? string.Empty),
InnerException = ex == null || ex.InnerException == null ? string.Empty : (ex.InnerException.Message.Length > 1000 ? ex.InnerException.Message.Substring(0, 1000) : ex.InnerException.Message)
});
return(new List <Mail_RolesProcesados_Info>());
}
}
public List <Mail_RolesProcesados_Info> GetCabecerasProcesadasAmedias()
{
try
{
List <Mail_RolesProcesados_Info> Lista = new List <Mail_RolesProcesados_Info>();
string querySelect =
"select cro_numero,cro_empre,a.ID,FechaInicio,FechaFin,TotalEmpleados,TotalProcesados from mail_rolesprocesados a inner join "
+ "tb_cabrol as b on a.ID = b.ID "
+ "where totalEmpleados != TotalProcesados and datediff(hour, FechaInicio, getdate()) > 4";
using (SqlConnection connection = new SqlConnection(builder.ToString()))
{
connection.Open();
#region Select
SqlCommand commandSelect = new SqlCommand(querySelect, connection);
SqlDataReader reader = commandSelect.ExecuteReader();
while (reader.Read())
{
Lista.Add(new Mail_RolesProcesados_Info
{
ID = Convert.ToDecimal(reader[2]),
FechaInicio = DateTime.Now,
TotalEmpleados = Convert.ToInt32(reader[5]),
TotalProcesados = Convert.ToInt32(reader[6]),
IdEmpresa = Convert.ToInt32(reader[1]),
IdRol = Convert.ToInt32(reader[0])
});
}
reader.Close();
#endregion
foreach (var item in Lista)
{
string querySelectDet =
"select pre_empre,pre_codtra,trb_email,FechaEnvio,Error from mail_RolesProcesadosDet "
+ "where ID = " + item.ID.ToString() + " and FechaEnvio is not null";
#region SelectDet
List <Mail_RolesProcesadosDet_Info> ListaDet = new List <Mail_RolesProcesadosDet_Info>();
SqlCommand commandSelectDet = new SqlCommand(querySelectDet, connection);
SqlDataReader readerDet = commandSelectDet.ExecuteReader();
while (readerDet.Read())
{
var det = new Mail_RolesProcesadosDet_Info
{
pre_codtra = Convert.ToInt32(readerDet[1]),
pre_empre = Convert.ToInt32(readerDet[0]),
};
ListaDet.Add(det);
}
readerDet.Close();
#endregion
#region Procedure
string queryProcedure = "EXEC [rrhh].[dbo].[rep_sob_lista] {0}, 7, {1}, 'D', 1";
queryProcedure = queryProcedure.Replace("{0}", item.IdEmpresa.ToString());
queryProcedure = queryProcedure.Replace("{1}", item.IdRol.ToString());
SqlCommand commandProcedure = new SqlCommand(queryProcedure, connection);
SqlDataReader readerProcedure = commandProcedure.ExecuteReader();
item.Lista = new List <Mail_RolesProcesadosDet_Info>();
int Secuencia = 1;
while (readerProcedure.Read())
{
Mail_RolesProcesadosDet_Info det = new Mail_RolesProcesadosDet_Info
{
ID = item.ID,
Secuencia = Secuencia++,
pre_empre = Convert.ToInt32(readerProcedure[0]),
pre_codtra = Convert.ToInt32(readerProcedure[2]),
trb_email = Convert.ToString(readerProcedure[15]),
infoRpt = new Reporte01_Info
{
pre_empresa = Convert.ToInt32(readerProcedure[0]),
pre_codtra = Convert.ToInt32(readerProcedure[2]),
pre_rol = Convert.ToInt32(readerProcedure[3]),
trb_email = Convert.ToString(readerProcedure[15]),
emp_nombre = Convert.ToString(readerProcedure[16]),
cgo_descri = Convert.ToString(readerProcedure[11]),
sub_nombre = Convert.ToString(readerProcedure[10]),
trb_cen = Convert.ToString(readerProcedure[9]),
trb_are = Convert.ToString(readerProcedure[8]),
trb_div = Convert.ToString(readerProcedure[7]),
NOM = Convert.ToString(readerProcedure[6]),
cro_fecini = Convert.ToDateTime(readerProcedure[4]),
cro_fecfin = Convert.ToDateTime(readerProcedure[5]),
ING = Convert.ToDouble(readerProcedure[12]),
EGR = Math.Abs(Convert.ToDouble(readerProcedure[13]))
}
};
det.infoRpt.Saldo = det.infoRpt.ING - det.infoRpt.EGR;
if (ListaDet.Where(q => q.pre_codtra == det.pre_codtra && q.pre_empre == det.pre_empre).FirstOrDefault() == null)
{
item.Lista.Add(det);
}
}
item.TotalEmpleados = item.Lista.Count;
readerProcedure.Close();
#endregion
}
connection.Close();
}
return(Lista);
}
catch (Exception ex)
{
GuardarExcepcion(new Mail_LogError_Info
{
Metodo = "GetCabecerasProcesadasAmedias",
Error = ex == null ? string.Empty : (ex.Message ?? string.Empty),
InnerException = ex == null || ex.InnerException == null ? string.Empty : (ex.InnerException.Message.Length > 1000 ? ex.InnerException.Message.Substring(0, 1000) : ex.InnerException.Message)
});
return(new List <Mail_RolesProcesados_Info>());
}
}
