public int CheckUser(Usuario U)
{
// DSL dsl = new DSL();
int IDT = 0;
//Usuario Us = new Usuario();
dsl = new DSL();
DataTable Dt = new DataTable();
try
{
dsl.Open(Con, Proveedor.SQLServer);
dsl.InitialSQLStatement("dbo.CheckUser", CommandType.StoredProcedure);
dsl.SetParameterProcedure("Id", ParameterDirection.Output, eTypes.Entero, null);
dsl.SetParameterProcedure("NombreU", ParameterDirection.Input, eTypes.Cadena, U.NombreUsuario);
dsl.SetParameterProcedure("Password", ParameterDirection.Input, eTypes.Cadena, U.CodigoAcceso);
IDT = int.Parse(dsl.ExecuteStoredOutPut().ToString());
dsl.Close();
return(IDT);
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
dsl = null;
}
}
public void AddCons(Consulta C, int IDCH)
{
try
{
dsl = new DSL();
dsl.Open("DATA SOURCE = DESKTOP - 3AQGIM6\\SQLEXPRESS; INITIAL CATALOG = PROYECTOINTEGRADOR; user = sa; password =#m1m2m3m4m5", Proveedor.SQLServer);
dsl.InitialSQLStatement("dbo.usp_AddCons", CommandType.StoredProcedure);
dsl.SetParameterProcedure("@IdPac", ParameterDirection.Input, eTypes.Entero, IDCH);
dsl.SetParameterProcedure("@Peso", ParameterDirection.Input, eTypes.Entero, C.Peso);
dsl.SetParameterProcedure("@Altura", ParameterDirection.Input, eTypes.Entero, C.Altura);
dsl.SetParameterProcedure("@Presion", ParameterDirection.Input, eTypes.Cadena, C.PresionArt);
dsl.SetParameterProcedure("@Motivo", ParameterDirection.Input, eTypes.Cadena, C.Motivo);
dsl.SetParameterProcedure("@Antecedentes", ParameterDirection.Input, eTypes.Cadena, C.Antecedentes);
dsl.SetParameterProcedure("@Diagnostico", ParameterDirection.Input, eTypes.Cadena, C.Diagnostico);
dsl.SetParameterProcedure("@Receta", ParameterDirection.Input, eTypes.Cadena, C.Receta);
dsl.ExecuteNonQuery();
dsl.Close();
}
catch (Exception e)
{
throw new Exception(e.Message, e);
}
finally
{
dsl = null;
}
}
public int AddLab(Modelos.Laboratorio L)
{
try
{
dsl = new DSL();
int IDL;
dsl.Open("DATA SOURCE = JARVIS; INITIAL CATALOG = PROYECTOINTEGRADOR; INTEGRATED SECURITY = YES", Proveedor.SQLServer);
dsl.InitialSQLStatement("dbo.usp_AddLab", CommandType.StoredProcedure);
dsl.SetParameterProcedure("@Nombre", ParameterDirection.Input, eTypes.Cadena, L.Nombre);
dsl.SetParameterProcedure("@Director", ParameterDirection.Input, eTypes.Cadena, L.Director);
dsl.SetParameterProcedure("@Telefono", ParameterDirection.Input, eTypes.Cadena, L.Telefono);
dsl.SetParameterProcedure("@Calle", ParameterDirection.Input, eTypes.Cadena, L.Calle);
dsl.SetParameterProcedure("@Colonia", ParameterDirection.Input, eTypes.Cadena, L.Colonia);
dsl.SetParameterProcedure("@Municipio", ParameterDirection.Input, eTypes.Cadena, L.Municipio);
dsl.SetParameterProcedure("@Estado", ParameterDirection.Input, eTypes.Cadena, L.Estado);
dsl.SetParameterProcedure("@Id", ParameterDirection.Output, eTypes.Entero, null);
IDL = int.Parse(dsl.ExecuteStoredOutPut().ToString());
dsl.Close();
return(IDL);
}
catch (Exception e)
{
throw new Exception(e.Message, e);
}
finally
{
dsl = null;
}
}
public long LoginXML(Credenciales cre)
{
DSL _connection = new DSL();
try
{
_connection.Open(ConfigurationManager.ConnectionStrings["dbLogin"].ToString(), RNConnection.DataAbstractionLayer.eProvider.SQLServer);
_connection.InitialSQLStatement("dbo.fe_Autenticarse_xml", System.Data.CommandType.StoredProcedure);
_connection.SetParameterProcedure("@nick", System.Data.ParameterDirection.Input, RNConnection.DataAbstractionLayer.eTypes.Cadena, cre.Nick);
_connection.SetParameterProcedure("@pwd", System.Data.ParameterDirection.Input, RNConnection.DataAbstractionLayer.eTypes.Cadena, cre.Password);
_connection.SetParameterProcedure("@ResultadoXML", System.Data.ParameterDirection.ReturnValue, RNConnection.DataAbstractionLayer.eTypes.XML, null);
long IdCredencial = 0;
XmlReader xmlr = _connection.ExecuteXMLReader();
XDocument XDocEsquema = XDocument.Load(xmlr);
var Cursor = from valor in XDocEsquema.Descendants("Personas")
select valor;
foreach (var item in Cursor)
{
IdCredencial = long.Parse(item.Element("Credencial").Value.ToString());
break;
}
return IdCredencial;
}
catch (Exception ex)
{
throw new Exception("Error: " + ex.Message, ex);
}
}
public List <Cita> BusCita(string Fec)
{
Paciente p = new Paciente();
List <Cita> LP = new List <Cita>();
Cita P = new Cita();
dsl = new DSL();
try
{
P = new Cita();
dsl.Open(Con, Proveedor.SQLServer);
dsl.InitialSQLStatement("SELECT * FROM dbo.ConsCitFec('" + Fec + "');", CommandType.Text);
DataTable Dt = dsl.ReturnTable();
for (int i = 0; i < Dt.Rows.Count; i++)
{
P = new Cita();
P.FechaHora = Dt.Rows[i]["FechaHora"].ToString();
P.Nombre = Dt.Rows[i]["Nombre"].ToString();
LP.Add(P);
}
//dsl.Close();
return(LP);
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
dsl = null;
}
}
public void ModLab(Modelos.Laboratorio L)
{
try
{
dsl = new DSL();
dsl.Open("DATA SOURCE = DESKTOP-3AQGIM6\\SQLEXPRESS; INITIAL CATALOG = PROYECTOINTEGRADOR; user=sa; password=#m1m2m3m4m5", Proveedor.SQLServer);
dsl.InitialSQLStatement("dbo.usp_ModLab", CommandType.StoredProcedure);
dsl.SetParameterProcedure("@Id", ParameterDirection.Input, eTypes.Entero, L.IdLab);
dsl.SetParameterProcedure("@Nombre", ParameterDirection.Input, eTypes.Cadena, L.Nombre);
dsl.SetParameterProcedure("@Director", ParameterDirection.Input, eTypes.Cadena, L.Director);
dsl.SetParameterProcedure("@Telefono", ParameterDirection.Input, eTypes.Cadena, L.Telefono);
dsl.SetParameterProcedure("@Calle", ParameterDirection.Input, eTypes.Cadena, L.Calle);
dsl.SetParameterProcedure("@Colonia", ParameterDirection.Input, eTypes.Cadena, L.Colonia);
dsl.SetParameterProcedure("@Municipio", ParameterDirection.Input, eTypes.Cadena, L.Municipio);
dsl.SetParameterProcedure("@Estado", ParameterDirection.Input, eTypes.Cadena, L.Estado);
dsl.ExecuteNonQuery();
dsl.Close();
}
catch (Exception e)
{
throw new Exception(e.Message, e);
}
finally
{
dsl = null;
}
}
public void GenerarReporteCitasDesdeBD()
{
FormarDoctoPDF documentoPDF = new FormarDoctoPDF();
documentoPDF.ConfiguracionArchivo = new ConfigArchivo();
documentoPDF.ConfiguracionEncabezado = new ConfigEncabezado();
documentoPDF.ConfiguracionPiePagina = new ConfigPiePagina();
//configuracion del archivo
documentoPDF.ConfiguracionArchivo.TipoHoja = eTipoHoja.Carta;
documentoPDF.ConfiguracionArchivo.Fuente = eTipoFuente.Arial;
documentoPDF.ConfiguracionArchivo.SizeFuente = eSizeFuente.Standar;
documentoPDF.ConfiguracionArchivo.OrientacionPagina = eOrientacion.Vertical;
documentoPDF.ConfiguracionArchivo.BCopiar = true;
documentoPDF.ConfiguracionArchivo.BImprimir = false; //false se puede imprimir, true no se puede
documentoPDF.ConfiguracionArchivo.BAccesibilidadContenido = true;
documentoPDF.ConfiguracionArchivo.Resumen = false;
#region Reporte apartir de un HTML
ReporteSimple reportecitas = new ReporteSimple();
dsl.Open(Con, RNConnection.DataAbstractionLayer.Proveedor.SQLServer);
dsl.InitialSQLStatement("SELECT * FROM ReturnValuesCitas()", System.Data.CommandType.Text);
DataTable tabla = dsl.ReturnTable();
reportecitas.Escuela = "";
reportecitas.Universidad = reportecitas.Escuela;
//configuracion de archivo
documentoPDF.ConfiguracionArchivo.NombreArchivo = ruta + "reporteCitas" + DateTime.Now.Ticks.ToString() + ".pdf";
documentoPDF.ConfiguracionArchivo.TipoReporte = eTipoReporte.Simple;
for (int i = 0; i < tabla.Rows.Count; i++)
{
string html = File.ReadAllText(ConfigurationManager.ConnectionStrings["RutaHTML"].ToString() + @"\Reporte_Citas.html");
html = html.Replace("#IDCITA#", tabla.Rows[i]["IdCita"].ToString());
html = html.Replace("#IDPACIENTE#", tabla.Rows[i]["IdPaciente"].ToString());
html = html.Replace("#FECHAHORA#", tabla.Rows[i]["FechaHora"].ToString());
reportecitas.ParrafoCuerpo = html;
}
reportecitas.InicialesUsuario = "JCVE";
documentoPDF.ObtenerDocumentoPDF(null, reportecitas);
}
public List <Paciente> BusIdPac(Paciente PP)
{
List <Paciente> LP = new List <Paciente>();
Paciente P = new Paciente();
dsl = new DSL();
try
{
P = new Paciente();
dsl.Open(Con, Proveedor.SQLServer);
dsl.InitialSQLStatement("SELECT * FROM dbo.ConsIdPac(" + PP.IdPersona + ");", CommandType.Text);
DataTable Dt = dsl.ReturnTable();
for (int i = 0; i < Dt.Rows.Count; i++)
{
P = new Paciente();
P.Nombre = Dt.Rows[i]["Nombre"].ToString();
P.ApePaterno = Dt.Rows[i]["ApePat"].ToString();
P.ApeMaterno = Dt.Rows[i]["ApeMat"].ToString();
P.Edad = int.Parse(Dt.Rows[i]["Edad"].ToString());
P.Sexo = Dt.Rows[i]["Sexo"].ToString();
P.FechaNacimiento = Dt.Rows[i]["FechaNac"].ToString();
P.EstadoCivil = Dt.Rows[i]["EstadoCivil"].ToString();
P.RFC = Dt.Rows[i]["RFC"].ToString();
P.CURP = Dt.Rows[i]["CURP"].ToString();
P.Ocupacion = Dt.Rows[i]["Ocupacion"].ToString();
P.NumeroCasa = Dt.Rows[i]["NumeroCasa"].ToString();
P.Calle = Dt.Rows[i]["Calle"].ToString();
P.Colonia = Dt.Rows[i]["Colonia"].ToString();
P.Estado = Dt.Rows[i]["Estado"].ToString();
P.Municipio = Dt.Rows[i]["Municipio"].ToString();
P.TelCasa = Dt.Rows[i]["TelefonoCasa"].ToString();
P.TelMovil = Dt.Rows[i]["TelefonoMovil"].ToString();
LP.Add(P);
}
//dsl.Close();
return(LP);
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
dsl = null;
}
}
public long Login(Credenciales cre)
{
DSL _connection = new DSL();
try
{
_connection.Open(ConfigurationManager.ConnectionStrings["dbLogin"].ToString(), RNConnection.DataAbstractionLayer.eProvider.SQLServer);
_connection.InitialSQLStatement("dbo.Autenticarse", System.Data.CommandType.StoredProcedure);
_connection.SetParameterProcedure("@nick", System.Data.ParameterDirection.Input, RNConnection.DataAbstractionLayer.eTypes.Cadena, cre.Nick);
_connection.SetParameterProcedure("@pwd", System.Data.ParameterDirection.Input, RNConnection.DataAbstractionLayer.eTypes.Cadena, cre.Password);
long IdCredencial = long.Parse(_connection.ReturnObject().ToString());
return IdCredencial;
}
catch (Exception ex)
{
throw new Exception("Error: " + ex.Message, ex);
}
}
public long LoginOUTPUT(Credenciales cre)
{
DSL _connection = new DSL();
try
{
_connection.Open(ConfigurationManager.ConnectionStrings["dbLogin"].ToString(), RNConnection.DataAbstractionLayer.eProvider.SQLServer);
_connection.InitialSQLStatement("dbo.Autenticarse_out", System.Data.CommandType.StoredProcedure);
_connection.SetParameterProcedure("@nick", System.Data.ParameterDirection.Input, RNConnection.DataAbstractionLayer.eTypes.Cadena, cre.Nick);
_connection.SetParameterProcedure("@pwd", System.Data.ParameterDirection.Input, RNConnection.DataAbstractionLayer.eTypes.Cadena, cre.Password);
_connection.SetParameterProcedure("@Credencial", System.Data.ParameterDirection.Output, RNConnection.DataAbstractionLayer.eTypes.Entero, null);
long IdCredencial = long.Parse(_connection.ExecuteStroedOutPut().ToString());
return IdCredencial;
}
catch (Exception ex)
{
return 0;
}
}
public void DelLab(int IDL)
{
try
{
dsl = new DSL();
dsl.Open("DATA SOURCE = DESKTOP-3AQGIM6\\SQLEXPRESS; INITIAL CATALOG = PROYECTOINTEGRADOR; user=sa; password=#m1m2m3m4m5", Proveedor.SQLServer);
dsl.InitialSQLStatement("dbo.usp_DelLab", CommandType.StoredProcedure);
dsl.SetParameterProcedure("@Id", ParameterDirection.Input, eTypes.Entero, IDL);
dsl.ExecuteNonQuery();
dsl.Close();
}
catch (Exception e)
{
throw new Exception(e.Message, e);
}
finally
{
dsl = null;
}
}
public void DelPac(int IDP)
{
try
{
dsl = new DSL();
dsl.Open(Con, Proveedor.SQLServer);
dsl.InitialSQLStatement("dbo.usp_DelPac", CommandType.StoredProcedure);
dsl.SetParameterProcedure("@Id", ParameterDirection.Input, eTypes.Entero, IDP);
dsl.ExecuteNonQuery();
dsl.Close();
}
catch (Exception e)
{
throw new Exception();
}
finally
{
dsl = null;
}
}
public void DelCita(int IDC)
{
try
{
dsl = new DSL();
dsl.Open("DATA SOURCE = JARVIS; INITIAL CATALOG = PROYECTOINTEGRADOR; INTEGRATED SECURITY = YES", Proveedor.SQLServer);
dsl.InitialSQLStatement("dbo.usp_DelCita", CommandType.StoredProcedure);
dsl.SetParameterProcedure("@Id", ParameterDirection.Input, eTypes.Entero, IDC);
dsl.ExecuteNonQuery();
dsl.Close();
}
catch (Exception e)
{
throw new Exception(e.Message, e);
}
finally
{
dsl = null;
}
}
public int CompAuth(long obtainedCred)
{
DSL _connection = new DSL();
try
{
_connection.Open(ConfigurationManager.ConnectionStrings["dbLogin"].ToString(), RnConection.DataAbstractionLayer.Provedor.SQLServer);
_connection.InitialSQLStatement("ComprobarNivel", System.Data.CommandType.StoredProcedure);
_connection.SetParameterProcedure("@id", System.Data.ParameterDirection.Input, RnConection.DataAbstractionLayer.eTypes.Entero, obtainedCred);
int NivelAuth = int.Parse(_connection.ReturnObject().ToString());
return(NivelAuth);
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
_connection.Close();
}
}
public long Login(Username users)
{
DSL _connection = new DSL();
try
{
_connection.Open(ConfigurationManager.ConnectionStrings["dbLogin"].ToString(), RnConection.DataAbstractionLayer.Provedor.SQLServer);
_connection.InitialSQLStatement("Autenticarse", System.Data.CommandType.StoredProcedure);
_connection.SetParameterProcedure("@nick", System.Data.ParameterDirection.Input, RnConection.DataAbstractionLayer.eTypes.Cadena, users.user);
_connection.SetParameterProcedure("@pwd", System.Data.ParameterDirection.Input, RnConection.DataAbstractionLayer.eTypes.Cadena, users.password);
long IdCredencial = long.Parse(_connection.ReturnObject().ToString());
return(IdCredencial);
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
_connection.Close();
}
}
public void AddEL(int IDP, int IDL, EstudioLab EL)
{
try
{
dsl = new DSL();
dsl.Open("DATA SOURCE = JARVIS; INITIAL CATALOG = PROYECTOINTEGRADOR; INTEGRATED SECURITY = YES", Proveedor.SQLServer);
dsl.InitialSQLStatement("dbo.usp_AddEL", CommandType.StoredProcedure);
dsl.SetParameterProcedure("@Idp", ParameterDirection.Input, eTypes.Entero, IDP);
dsl.SetParameterProcedure("@Idl", ParameterDirection.Input, eTypes.Entero, IDL);
dsl.SetParameterProcedure("@Motivo", ParameterDirection.Input, eTypes.Cadena, EL.Motivo);
dsl.ExecuteNonQuery();
dsl.Close();
}
catch (Exception e)
{
throw new Exception(e.Message, e);
}
finally
{
dsl = null;
}
}
public void ModPac(Paciente P)
{
try
{
dsl = new DSL();
dsl.Open(Con, Proveedor.SQLServer);
dsl.InitialSQLStatement("dbo.usp_ModPac", CommandType.StoredProcedure);
dsl.SetParameterProcedure("@Nombre", ParameterDirection.Input, eTypes.Cadena, P.Nombre);
dsl.SetParameterProcedure("@ApePat", ParameterDirection.Input, eTypes.Cadena, P.ApePaterno);
dsl.SetParameterProcedure("@ApeMat", ParameterDirection.Input, eTypes.Cadena, P.ApeMaterno);
dsl.SetParameterProcedure("@Edad", ParameterDirection.Input, eTypes.Cadena, P.Edad);
dsl.SetParameterProcedure("@Sexo", ParameterDirection.Input, eTypes.Cadena, P.Sexo);
dsl.SetParameterProcedure("@FechaNac", ParameterDirection.Input, eTypes.Cadena, P.FechaNacimiento);
dsl.SetParameterProcedure("@EstadoCivil", ParameterDirection.Input, eTypes.Cadena, P.EstadoCivil);
dsl.SetParameterProcedure("@RFC", ParameterDirection.Input, eTypes.Cadena, P.RFC);
dsl.SetParameterProcedure("@CURP", ParameterDirection.Input, eTypes.Cadena, P.CURP);
dsl.SetParameterProcedure("@Email", ParameterDirection.Input, eTypes.Cadena, P.Email);
dsl.SetParameterProcedure("@Ocupacion", ParameterDirection.Input, eTypes.Cadena, P.Ocupacion);
dsl.SetParameterProcedure("@TelefonoMovil", ParameterDirection.Input, eTypes.Cadena, P.TelMovil);
dsl.SetParameterProcedure("@TelefonoCasa", ParameterDirection.Input, eTypes.Cadena, P.TelCasa);
dsl.SetParameterProcedure("@Calle", ParameterDirection.Input, eTypes.Cadena, P.Calle);
dsl.SetParameterProcedure("@Colonia", ParameterDirection.Input, eTypes.Cadena, P.Colonia);
dsl.SetParameterProcedure("@Estado", ParameterDirection.Input, eTypes.Cadena, P.Estado);
dsl.SetParameterProcedure("@Municipio", ParameterDirection.Input, eTypes.Cadena, P.Municipio);
dsl.SetParameterProcedure("@Num", ParameterDirection.Input, eTypes.Cadena, P.NumeroCasa);
dsl.SetParameterProcedure("@IdPe", ParameterDirection.Input, eTypes.Entero, P.IdPersona);
dsl.ExecuteNonQuery();
dsl.Close();
}
catch (Exception e)
{
throw new Exception(e.Message, e);
}
finally
{
dsl = null;
}
}
public void AddHC(Paciente P, Consulta HC)
{
try
{
dsl = new DSL();
dsl.Open("DATA SOURCE = JARVIS; INITIAL CATALOG = PROYECTOINTEGRADOR; INTEGRATED SECURITY = YES", Proveedor.SQLServer);
dsl.InitialSQLStatement("dbo.usp_AddHC", CommandType.StoredProcedure);
dsl.SetParameterProcedure("@Id", ParameterDirection.Input, eTypes.Entero, P.IdPaciente);
dsl.SetParameterProcedure("@Peso", ParameterDirection.Input, eTypes.Entero, HC.Peso);
dsl.SetParameterProcedure("@Altura", ParameterDirection.Input, eTypes.Entero, HC.Altura);
dsl.SetParameterProcedure("@Presion", ParameterDirection.Input, eTypes.Entero, HC.PresionArt);
dsl.ExecuteNonQuery();
dsl.Close();
}
catch (Exception e)
{
throw new Exception(e.Message, e);
}
finally
{
dsl = null;
}
}
public List <Laboratorio> BusLab()
{
List <Laboratorio> LP = new List <Laboratorio>();
Laboratorio P = new Laboratorio();
dsl = new DSL();
try
{
P = new Laboratorio();
dsl.Open(Con, Proveedor.SQLServer);
dsl.InitialSQLStatement("SELECT * FROM dbo.ConsAllLab();", CommandType.Text);
DataTable Dt = dsl.ReturnTable();
for (int i = 0; i < Dt.Rows.Count; i++)
{
P = new Laboratorio();
P.IdLab = int.Parse(Dt.Rows[i]["IdLab"].ToString());
P.Nombre = Dt.Rows[i]["Nombre"].ToString();
P.Director = Dt.Rows[i]["Director"].ToString();
P.Telefono = Dt.Rows[i]["Telefono"].ToString();
P.Calle = Dt.Rows[i]["Calle"].ToString();
P.Colonia = Dt.Rows[i]["Colonia"].ToString();
P.Municipio = Dt.Rows[i]["Municipio"].ToString();
P.Estado = Dt.Rows[i]["Estado"].ToString();
LP.Add(P);
}
//dsl.Close();
return(LP);
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
dsl = null;
}
}
public void AddServ(List <ItemServLab> LS, int idl)
{
try
{
foreach (ItemServLab SL in LS)
{
dsl = new DSL();
dsl.Open("DATA SOURCE = DESKTOP-DMUTBHE; INITIAL CATALOG = PROYECTOINTEGRADOR; INTEGRATED SECURITY = YES", Proveedor.SQLServer);
dsl.InitialSQLStatement("dbo.usp_AddServs", CommandType.StoredProcedure);
dsl.SetParameterProcedure("@IdLab", ParameterDirection.Input, eTypes.Entero, idl);
dsl.SetParameterProcedure("@Serv", ParameterDirection.Input, eTypes.Cadena, SL.Servicio);
dsl.ExecuteNonQuery();
dsl.Close();
}
}
catch (Exception e)
{
throw new Exception(e.Message, e);
}
finally
{
dsl = null;
}
}
