public List<GESTION_INDICADORES> GestionIndicadores(int anho, int mes, String codobj, String atributo)
{
using (var context = new IndicadoresEntities())
{
var Valores = context.Database.SqlQuery<GESTION_INDICADORES>(
" SELECT " + atributo + ",txt_06,SUM(VAL_09) TOTAL_BASE, "
+ " SUM(VAL_03) CEF, "
+ " SUM(VAL_02) CNE, "
+ " SUM(VAL_01) NOC, "
+ " SUM(VAL_01+VAL_02+VAL_03) TRAMITADOS, "
+ " SUM(CASE WHEN FECHA IS NULL THEN VAL_09 ELSE 0 END) PENDIENTES, "
+ " SUM(VAL_03+VAL_02) CONTACTOS, "
+ " SUM(VAL_04) CONTACTOS_POTENCIAL, "
+ " (SUM(VAL_04)/SUM(VAL_01+VAL_02+VAL_03))*100 CONTACTABILIDAD_POTENCIAL, "
+ " (SUM(VAL_03)/SUM(VAL_01+VAL_02+VAL_03))*100 EFECTIVIDAD, "
+ " (SUM(VAL_03)/SUM(VAL_02+VAL_03))*100 EFECT_CONTACTO, "
+ " (SUM(VAL_03)/SUM(VAL_04))*100 EFECT_POTENCIAL "
+ " FROM GESTION_INDICADORES_DETALLE "
+ " where tipo = 'TABLA_BASES_UNICOS' "
+ " AND CODOBJ = '" + codobj + "'"
+ " AND ESTADO = 'A' "
+ " AND MES= " + mes
+ " AND ANHO= " + anho
+ " group by " + atributo + ",txt_06 "
+ " order by 1").ToList();
return Valores;
}
}
public List<GESTION_INDICADORES_RESUMEN> GestionIndicadoresResumen(String codobj, int anho, int mes, int dia)
{
using (var cnx = new IndicadoresEntities())
{
var resumen = cnx.Database.SqlQuery<GESTION_INDICADORES_RESUMEN>(
" select DESCRIPCION, VALOROBJ, LIMA, PROVINCIA "
+" from gestion_indicadores_resumen"
+" where CODOBJ = '"+ codobj + "'"
+" and ANHO = "+ anho
+" and MES = " + mes
+" and DIA = "+ dia).ToList();
return resumen;
}
}
public List<GESTION_INDICADORES_DETALLEbasecerrado> GestionIndicadoresCierre(int anho, int mes, String codobj,String atributo)
{
using (var context = new IndicadoresEntities())
{
var Valores2 = context.Database.SqlQuery<GESTION_INDICADORES_DETALLEbasecerrado>(
"SELECT " + atributo + ",txt_06,SUM(VAL_09) MARCACIONES,SUM(VAL_04) POTENCIAL, "
+ " sum(CASE WHEN TXT_02 = 'Fijo' THEN VAL_09 ELSE 0 END) MARCACIONES_FIJA, "
+ "sum(CASE WHEN TXT_02 = 'Celular' THEN VAL_09 ELSE 0 END) MARCACIONE_CELULAR, "
+ " (sum(CASE WHEN TXT_02 = 'Fijo' THEN VAL_09 ELSE 0 END)/((SELECT SUM(X.VAL_01+X.VAL_02+X.VAL_03) TRAMITADOS FROM GESTION_INDICADORES_DETALLE x where tipo = 'TABLA_BASES_UNICOS' AND X.CODOBJ = Z.CODOBJ AND ESTADO = 'A' AND X.MES=Z.MES AND x.txt_06 = Z.TXT_06 ))) INTENTOS_FIJO, "
+ "(sum(CASE WHEN TXT_02 = 'Celular' THEN VAL_09 ELSE 0 END)/((SELECT SUM(X.VAL_01+X.VAL_02+X.VAL_03) TRAMITADOS FROM GESTION_INDICADORES_DETALLE x where tipo = 'TABLA_BASES_UNICOS' AND X.CODOBJ = Z.CODOBJ AND ESTADO = 'A' AND X.MES=Z.MES AND x.txt_06 = Z.TXT_06 ))) INTENTOS_CELULAR, "
+ " (SUM(VAL_09)/((SELECT SUM(X.VAL_01+X.VAL_02+X.VAL_03) TRAMITADOS FROM GESTION_INDICADORES_DETALLE x where tipo = 'TABLA_BASES_UNICOS' AND X.CODOBJ = Z.CODOBJ AND ESTADO = 'A' AND X.MES=Z.MES AND x.txt_06 = Z.TXT_06 ))) REINTENTOS "
+ " FROM GESTION_INDICADORES_DETALLE z "
+ " where tipo = 'TABLA_BASES_GESTION' "
+ " AND CODOBJ = '" + codobj + "'"
+ " AND MES="+mes
+ " AND ANHO="+anho
+ " group by z." + atributo + ",z.txt_06,z.CODOBJ,z.mes"
+ " order by 1 ").ToList();
return Valores2;
}
}
public List<Sph> TablaSph(int anho, int mes, String codobj)
{
using (var context = new IndicadoresEntities())
{
var blogNames = context.Database.SqlQuery<Sph>(
"select FECHA,COUNT(distinct txt_01) logueados,COUNT(distinct txt_01) * 7 horas_trabajadas, "
+"SUM(val_03) ventas, (SELECT ROUND(VALOR,0) FROM GESTION_VARIABLES V WHERE CODOBJ=X.CODOBJ AND CODVAR = 'OBJ_VTA_DIA_NEG') "
+"objetivo, SUM(val_03)/(COUNT(distinct txt_01) * 7) sph,"
+"sum(case when txt_03 = 'REGULAR' then val_03 else 0 end) regular,"
+"sum(case when txt_03 = 'DECO' then val_03 else 0 end) deco,"
+"sum(case when txt_06 = 'LIMA' then val_03 else 0 end) lima,"
+" sum(case when txt_06 = 'PROVINCIA' then val_03 else 0 end) provincia,"
+"SUM(case when val_03 > 0 then val_08 else 0 end) tmo "
+"from dbo.GESTION_INDICADORES_DETALLE X "
+"where tipo = 'TABLA_BASES_UNICOS' "
+"and FECHA is not null "
+" and ANHO= "+anho
+ " AND MES = " + mes
+ " AND DIA >= 1"
+ " AND CODOBJ = '" + codobj + "'"
+" AND ESTADO = 'A' group by fecha, "
+" CODOBJ order by 1").ToList();
return blogNames;
}
}
private string RegistrarLogin(string usuario, string nom, string apepat, string apemat, string email, string direc, string dni, string pass)
{
using (IndicadoresEntities bd = new IndicadoresEntities())
{
if (bd.USUARIOS.Any(u => u.EMAIL == email))
{
return "El Email Ingresado Ya se Encuentra en uso";
}
else if (bd.USUARIOS.Any(u => u.CODUSUARIO == usuario))
{
return "El Nombre de Usuario Ingresado Ya se Encuentra en uso";
}
else if (bd.USUARIOS.Any(u => u.DNI == dni))
{
return "El DNI Ingresado Ya se Encuentra en uso";//descomentar esta seccion si desea evitar registrar a varios usuarios con el mismo dni
}
else
{
IndicadoresEntities bd1 = new IndicadoresEntities();
USUARIOS _usu = new USUARIOS();
_usu.NOMBRE = nom;
_usu.APEPAT = apepat;
_usu.APEMAT = apemat;
_usu.DIRECCION = direc;
_usu.DNI = dni;
_usu.PASSWORD = pass;
_usu.EMAIL = email;
_usu.CODUSUARIO = usuario;
_usu.IDROL = 1;
bd1.USUARIOS.Add(_usu);
bd1.SaveChanges();
/*log.IDDET = cc.ID;*/
return "Registro OK";
}
}
}
public List<USUARIOSDTO> ValidarLogin(string usu, string pass)
{
using (var bd = new IndicadoresEntities())
{
var qrydb = from user in bd.USUARIOS
where user.EMAIL == usu && user.PASSWORD == pass
select user;
if (qrydb.Count() > 0)
{
var usuarioDTO= USUARIOSAssembler.ToDTOs(qrydb);
return usuarioDTO; //ejecuta
}
else
{
return null; //wilmer expresate
}
}
}