public string EliminarContratoSLA(ContratoSLA entidad)
{
using (SqlConnection conection = new SqlConnection(ConfigurationManager.ConnectionStrings["cnx"].ConnectionString))
{
conection.Open();
SqlTransaction tran = conection.BeginTransaction();
try
{
string sqltik = "DELETE FROM ContratoSLA WHERE CSL_ID=" + entidad.CSL_ID;
using (SqlCommand command = new SqlCommand(sqltik, conection, tran))
{
command.ExecuteNonQuery();
}
tran.Commit();
conection.Close();
return("ok");
}
catch (Exception ex)
{
tran.Rollback();
throw (ex);
}
}
}
public string InsertarContratoSLA(ContratoSLA entidad)
{
using (SqlConnection conection = new SqlConnection(ConfigurationManager.ConnectionStrings["cnx"].ConnectionString))
{
conection.Open();
SqlTransaction tran = conection.BeginTransaction();
try
{
string sqltik = "INSERT INTO ContratoSLA " +
" (CSL_CON_ID, CSL_SLA_ID, CSL_SER_ID, CSL_PorcentajeMedicion, CSL_Penalidad, CSL_UsuarioCreacion, CSL_FechaCreacion) VALUES" +
"('" + entidad.CSL_CON_ID + "', '" + entidad.CSL_SLA_ID + "','" + entidad.CSL_SER_ID + "','" + entidad.CSL_PorcentajeMedicion + "','" + entidad.CSL_Penalidad + "','" + entidad.CSL_UsuarioCreacion + "','" + DateTime.Now.ToString("yyyy-MM-dd") + "')";
using (SqlCommand command = new SqlCommand(sqltik, conection, tran))
{
command.ExecuteNonQuery();
}
tran.Commit();
conection.Close();
return("ok");
}
catch (Exception ex)
{
tran.Rollback();
throw (ex);
}
}
}
public List <ContratoSLA> ListadoLogroMonitoreo(ContratoSLA entidad)
{
try
{
return(monitoreoService.ListadoLogroMonitoreo(entidad));
}
catch (Exception)
{
throw;
}
}
public string EliminarContratoSLA(ContratoSLA entidad)
{
try
{
return(contratoService.EliminarContratoSLA(entidad));
}
catch (Exception)
{
throw;
}
}
public List <ContratoSLA> ListadoContratoSLA(ContratoSLA entidad)
{
try
{
return(contratoService.ListadoContratoSLA(entidad));
}
catch (Exception)
{
throw;
}
}
public List <ContratoSLA> ListadoContratoSLA(ContratoSLA entidad)
{
List <ContratoSLA> Lista = null;
try
{
using (SqlConnection conection = new SqlConnection(ConfigurationManager.ConnectionStrings["cnx"].ConnectionString))
{
conection.Open();
//Fin totalizado
string sql = "SELECT * FROM ContratoSLA csa " +
" INNER JOIN SLA s ON(csa.CSL_SLA_ID=s.SLA_ID)" +
" INNER JOIN Servicio sr ON(csa.CSL_SER_ID=sr.SER_ID)" +
" WHERE csa.CSL_CON_ID=" + entidad.CSL_CON_ID;
using (SqlCommand command = new SqlCommand(sql, conection))
{
using (SqlDataReader dr = command.ExecuteReader())
{
if (dr.HasRows)
{
Lista = new List <ContratoSLA>();
while (dr.Read())
{
ContratoSLA item = new ContratoSLA();
item.CSL_ID = dr.GetInt32(dr.GetOrdinal("CSL_ID"));
item.CSL_CON_ID = dr.GetInt32(dr.GetOrdinal("CSL_CON_ID"));
item.SLA_Descripcion = dr.GetString(dr.GetOrdinal("SLA_Descripcion"));
item.SER_Descripcion = dr.GetString(dr.GetOrdinal("SER_Descripcion"));
item.CSL_PorcentajeMedicion = dr.GetDecimal(dr.GetOrdinal("CSL_PorcentajeMedicion"));
item.CSL_Penalidad = dr.GetDecimal(dr.GetOrdinal("CSL_Penalidad"));
Lista.Add(item);
}
}
}
}
conection.Close();
}
return(Lista);
}
catch (Exception ex)
{
throw (ex);
}
}
public List <ContratoSLA> ListadoLogroMonitoreo(ContratoSLA entidad)
{
List <ContratoSLA> Lista = null;
try
{
using (SqlConnection conection = new SqlConnection(ConfigurationManager.ConnectionStrings["cnx"].ConnectionString))
{
conection.Open();
string query = "SELECT * FROM ContratoSLA csl" +
" INNER JOIN SLA s ON(csl.CSL_SLA_ID=s.SLA_ID)" +
" WHERE CSL_CON_ID=" + entidad.CSL_CON_ID;
using (SqlCommand command = new SqlCommand(query, conection))
{
using (SqlDataReader dr = command.ExecuteReader())
{
if (dr.HasRows)
{
Lista = new List <ContratoSLA>();
while (dr.Read())
{
ContratoSLA item = new ContratoSLA();
item.CSL_SLA_ID = dr.GetInt32(dr.GetOrdinal("CSL_SLA_ID"));
item.CSL_PorcentajeMedicion = dr.GetDecimal(dr.GetOrdinal("CSL_PorcentajeMedicion"));
item.CSL_Penalidad = dr.GetDecimal(dr.GetOrdinal("CSL_Penalidad"));
item.SLA_Descripcion = dr.GetString(dr.GetOrdinal("SLA_Descripcion"));
item.SLA_NomSistema = dr.GetString(dr.GetOrdinal("SLA_NomSistema"));
//item.
//Mese
string[] mes = new string[12];
mes[0] = "ENE"; mes[3] = "ABR"; mes[6] = "JUL"; mes[9] = "OCT";
mes[1] = "FEB"; mes[4] = "MAY"; mes[7] = "AGO"; mes[10] = "NOV";
mes[2] = "MAR"; mes[5] = "JUN"; mes[8] = "SEP"; mes[11] = "DIC";
List <RepMonitoreo> Resultado = null;
string queryrep = "SELECT MONTH(TIC_FechaRegistro)as REP_Periodo, YEAR(TIC_FechaRegistro)as REP_Anio FROM Ticket " +
" WHERE TIC_EMP_ID=" + entidad.CSL_EMP_ID + " AND format(TIC_FechaRegistro,'yyyy-MM-dd') BETWEEN '" + entidad.CON_FechaInicioContrato + "' AND '" + entidad.CON_FechaFinContrato + "' GROUP BY YEAR(TIC_FechaRegistro),MONTH(TIC_FechaRegistro)";
using (SqlCommand command1 = new SqlCommand(queryrep, conection))
{
using (SqlDataReader dr1 = command1.ExecuteReader())
{
if (dr1.HasRows)
{
Resultado = new List <RepMonitoreo>();
while (dr1.Read())
{
decimal logro = 0;
if (dr.GetString(dr.GetOrdinal("SLA_NomSistema")) == "TEMP_ATE_M10M")
{
using (SqlCommand command100 = new SqlCommand("MonitoreoSLA", conection))
{
command100.CommandType = CommandType.StoredProcedure;
command100.Parameters.AddWithValue("@IdContrato", entidad.CSL_CON_ID);
command100.Parameters.AddWithValue("@SlaNomSistema", "TEMP_ATE_M10M");
command100.Parameters.AddWithValue("@Anio", dr1.GetInt32(dr1.GetOrdinal("REP_Anio")));
command100.Parameters.AddWithValue("@Mes", dr1.GetInt32(dr1.GetOrdinal("REP_Periodo")));
using (SqlDataReader dr100 = command100.ExecuteReader())
{
if (dr100.HasRows)
{
while (dr100.Read())
{
logro = dr100.GetDecimal(dr100.GetOrdinal("Porcentaje"));
}
}
}
}
}
else if (dr.GetString(dr.GetOrdinal("SLA_NomSistema")) == "NRESOL_PRI_CONTACTO")
{
using (SqlCommand command100 = new SqlCommand("MonitoreoSLA", conection))
{
command100.CommandType = CommandType.StoredProcedure;
command100.Parameters.AddWithValue("@IdContrato", entidad.CSL_CON_ID);
command100.Parameters.AddWithValue("@SlaNomSistema", "NRESOL_PRI_CONTACTO");
command100.Parameters.AddWithValue("@Anio", dr1.GetInt32(dr1.GetOrdinal("REP_Anio")));
command100.Parameters.AddWithValue("@Mes", dr1.GetInt32(dr1.GetOrdinal("REP_Periodo")));
using (SqlDataReader dr100 = command100.ExecuteReader())
{
if (dr100.HasRows)
{
while (dr100.Read())
{
logro = dr100.GetDecimal(dr100.GetOrdinal("Porcentaje"));
}
}
}
}
}
else if (dr.GetString(dr.GetOrdinal("SLA_NomSistema")) == "TIC_ATE_24H")
{
using (SqlCommand command100 = new SqlCommand("MonitoreoSLA", conection))
{
command100.CommandType = CommandType.StoredProcedure;
command100.Parameters.AddWithValue("@IdContrato", entidad.CSL_CON_ID);
command100.Parameters.AddWithValue("@SlaNomSistema", "TIC_ATE_24H");
command100.Parameters.AddWithValue("@Anio", dr1.GetInt32(dr1.GetOrdinal("REP_Anio")));
command100.Parameters.AddWithValue("@Mes", dr1.GetInt32(dr1.GetOrdinal("REP_Periodo")));
using (SqlDataReader dr100 = command100.ExecuteReader())
{
if (dr100.HasRows)
{
while (dr100.Read())
{
logro = dr100.GetDecimal(dr100.GetOrdinal("Porcentaje"));
}
}
}
}
}
RepMonitoreo rep = new RepMonitoreo();
rep.REP_Logro = Math.Round(logro, 2);
rep.REP_Cumple = (dr.GetDecimal(dr.GetOrdinal("CSL_PorcentajeMedicion")) <= logro)?1:0;
rep.REP_Descripcion = mes[dr1.GetInt32(dr1.GetOrdinal("REP_Periodo")) - 1];
rep.REP_Periodo = dr1.GetInt32(dr1.GetOrdinal("REP_Periodo"));
rep.REP_Anio = dr1.GetInt32(dr1.GetOrdinal("REP_Anio"));
Resultado.Add(rep);
}
}
}
}
item.Medicion = Resultado;
//item.Medicion =
Lista.Add(item);
}
}
}
}
conection.Close();
}
return(Lista);
}
catch (Exception ex)
{
throw (ex);
}
}
