public DatosAtencion GetDatosPersonalesAtencion(string serviceId)
{
try
{
DatabaseContext dbContext = new DatabaseContext();
var objEntity = (from s in dbContext.Service
join pe in dbContext.Person on s.v_PersonId equals pe.v_PersonId
join C in dbContext.SystemParameter on new { a = pe.i_SexTypeId.Value, b = 100 }
equals new { a = C.i_ParameterId, b = C.i_GroupId } into C_join
from C in C_join.DefaultIfEmpty()
where s.v_ServiceId == serviceId
select new DatosAtencion
{
Paciente = pe.v_FirstLastName,
Genero = C.v_Value1
}).FirstOrDefault();
return(objEntity);
}
catch (Exception ex)
{
Console.WriteLine(ex);
throw;
}
}
public void sr_llenar_resultados(ref DataGridView dgr, int IDS, DateTime dia)
{
animalitos db = new animalitos(CN.CONEC);
var query = (from A in db.dbresultados
join B in db.dbSorteos on new { ID_LOTERIA = Convert.ToInt32(A.ID_LOTERIA) } equals new { ID_LOTERIA = B.ID_SORTEO } into B_join
from B in B_join.DefaultIfEmpty()
join C in db.dbhorarios
on new { A.ID_LOTERIA, ID_SORTEOHR = Convert.ToInt32(A.ID_SORTEOHR) }
equals new { ID_LOTERIA = C.IDSORTEOHR, ID_SORTEOHR = C.IDHORA } into C_join
from C in C_join.DefaultIfEmpty()
join D in db.dbanimalitos on new { ID_ANIMAL = Convert.ToInt32(A.ID_ANIMAL) } equals new { ID_ANIMAL = D.IDANIMALITOS } into D_join
from D in D_join.DefaultIfEmpty()
join E in db.dbestatus on new { ESTATUS = A.ESTATUS } equals new { ESTATUS = E.CODESTATUS } into E_join
from E in E_join.DefaultIfEmpty()
where
A.FECHA == dia &&
A.ID_LOTERIA == IDS
select new
{
A.ID_RESULTADO,
NOMBRE_SORTEO = B.NOMBRE_SORTEO,
HORA = C.HORA,
NOMBRE_ANIMALITO = D.NOMBRE_ANIMALITO,
NOMESTATUS = E.NOMESTATUS,
A.FECHA,
Column1 = A.HORA
});
dgr.DataSource = query;
}
public List <Sigesoft.Node.WinClient.BE.FamilyMedicalAntecedentsList> GetFamilyMedicalAntecedentsReport(string pstrPersonId)
{
//mon.IsActive = true;
try
{
SigesoftEntitiesModel dbContext = new SigesoftEntitiesModel();
var query = (from A in dbContext.familymedicalantecedents
join B in dbContext.systemparameter on new { a = A.i_TypeFamilyId.Value, b = 149 }
equals new { a = B.i_ParameterId, b = B.i_GroupId } into B_join
from B in B_join.DefaultIfEmpty()
join C in dbContext.systemparameter on new { a = B.i_ParentParameterId.Value, b = 149 }
equals new { a = C.i_ParameterId, b = C.i_GroupId } into C_join
from C in C_join.DefaultIfEmpty()
join D in dbContext.diseases on new { a = A.v_DiseasesId }
equals new { a = D.v_DiseasesId } into D_join
from D in D_join.DefaultIfEmpty()
where A.i_IsDeleted == 0 && A.v_PersonId == pstrPersonId
select new Sigesoft.Node.WinClient.BE.FamilyMedicalAntecedentsList
{
v_FamilyMedicalAntecedentsId = A.v_FamilyMedicalAntecedentsId,
v_PersonId = A.v_PersonId,
v_DiseasesId = A.v_DiseasesId,
v_DiseaseName = D.v_Name,
//i_TypeFamilyId = A.i_TypeFamilyId.Value,
i_TypeFamilyId = C.i_ParameterId,
v_TypeFamilyName = C.v_Value1,
v_Comment = A.v_Comment,
v_FullAntecedentName = D.v_Name + " / " + C.v_Value1 + ", " + A.v_Comment,
DxAndComment = D.v_Name + "," + A.v_Comment
}).ToList();
// add the sequence number on the fly
var query1 = query.Select((x, index) => new Sigesoft.Node.WinClient.BE.FamilyMedicalAntecedentsList
{
i_Item = index + 1,
v_FamilyMedicalAntecedentsId = x.v_FamilyMedicalAntecedentsId,
v_PersonId = x.v_PersonId,
v_DiseasesId = x.v_DiseasesId,
v_DiseaseName = x.v_DiseaseName,
i_TypeFamilyId = x.i_TypeFamilyId,
v_TypeFamilyName = x.v_TypeFamilyName,
v_Comment = x.v_Comment,
v_FullAntecedentName = x.v_FullAntecedentName,
DxAndComment = x.DxAndComment
}).ToList();
return(query1);
}
catch (Exception ex)
{
return(null);
}
}
public LogList GetLog(ref OperationResult pobjOperationResult, string pintLogId)
{
//mon.IsActive = true;
try
{
SigesoftEntitiesModel dbContext = new SigesoftEntitiesModel();
var query = (from A in dbContext.log
join B in dbContext.node on A.i_NodeLogId equals B.i_NodeId
join C in dbContext.organization on A.v_OrganizationId equals C.v_OrganizationId into C_join
from C in C_join.DefaultIfEmpty()
join D in dbContext.systemuser on A.i_SystemUserId equals D.i_SystemUserId into D_join
from D in D_join.DefaultIfEmpty()
join J1 in dbContext.systemparameter on new { a = 102, b = A.i_EventTypeId.Value }
equals new { a = J1.i_GroupId, b = J1.i_ParameterId }
join J2 in dbContext.systemparameter on new { a = 111, b = A.i_Success.Value }
equals new { a = J2.i_GroupId, b = J2.i_ParameterId }
where A.v_LogId == pintLogId
select new LogList
{
v_LogId = A.v_LogId,
i_EventTypeId = A.i_EventTypeId.Value,
v_EventTypeName = J1.v_Value1,
i_NodeId = A.i_NodeLogId.Value,
v_NodeName = B.v_Description,
v_OrganizationId = A.v_OrganizationId,
v_OrganizationName = C.v_Name,
i_SystemUserId = A.i_SystemUserId.Value,
v_SystemUserName = D.v_UserName,
d_Date = A.d_Date.Value,
v_ProcessEntity = A.v_ProcessEntity,
v_ElementItem = A.v_ElementItem,
i_Success = A.i_Success.Value,
v_SuccessName = J2.v_Value1,
v_ErrorException = A.v_ErrorException
}).FirstOrDefault();
return(query);
}
catch (Exception ex)
{
pobjOperationResult.Success = 0;
pobjOperationResult.ExceptionMessage = ex.Message;
return(null);
}
}
private string ConcatenateFamilyMedicalAntecedents(string pstrPersonId, int pintTypeFamilyId)
{
var qry = (from A in ctx.FamilyMedicalAntecedents
join B in ctx.SystemParameter on new { a = A.i_TypeFamilyId.Value, b = 149 } // ANTECEDENTES FAMILIARES MÉDICOS
equals new { a = B.i_ParameterId, b = B.i_GroupId } into B_join
from B in B_join.DefaultIfEmpty()
join C in ctx.SystemParameter on new { a = B.i_ParentParameterId.Value, b = 149 } // [PADRE,MADRE,HERMANOS]
equals new { a = C.i_ParameterId, b = C.i_GroupId } into C_join
from C in C_join.DefaultIfEmpty()
join D in ctx.Diseases on new { a = A.v_DiseasesId }
equals new { a = D.v_DiseasesId } into D_join
from D in D_join.DefaultIfEmpty()
where A.v_PersonId == pstrPersonId &&
A.i_IsDeleted == 0 && C.i_ParameterId == pintTypeFamilyId
select new
{
v_DiseasesName = D.v_Name
}).ToList();
return(string.Join(", ", qry.Select(p => p.v_DiseasesName)));
}
public List <PersonMedicalHistoryList> GetAntecedentConsolidateForService(string patientId)
{
//mon.IsActive = true;
try
{
List <PersonMedicalHistoryList> lis = new List <PersonMedicalHistoryList>();
int isDeleted = (int)SiNo.No;
#region querys individuales
// Obtener todos loa antecedentes de una persona (una o varias empresas)
var historyId = (from a in ctx.History
where a.v_PersonId == patientId && a.i_IsDeleted == isDeleted
select new PersonMedicalHistoryList
{
v_AntecedentTypeName = "Ocupacionales",
v_DiseasesName = null,
v_HistoryId = a.v_HistoryId,
d_StartDate = a.d_StartDate,
d_EndDate = a.d_EndDate,
v_Occupation = a.v_workstation,
v_GroupName = null
}).ToList();
// personmedicalhistory
var q1tmp = (from A in ctx.PersonMedicalHistory
join D in ctx.Diseases on A.v_DiseasesId equals D.v_DiseasesId
where A.i_IsDeleted == isDeleted && A.v_PersonId == patientId
select new PersonMedicalHistoryList
{
v_AntecedentTypeName = "Medicos-Personales",
v_DiseasesName = D.v_Name,
d_StartDate = A.d_StartDate,
v_GroupName = null
}).ToList();
var q1 = (from A in q1tmp
select new PersonMedicalHistoryList
{
v_AntecedentTypeName = "Medicos-Personales",
v_DiseasesName = A.v_DiseasesName,
v_DateOrGroup = A.d_StartDate.Value.ToShortDateString(),
d_StartDate = A.d_StartDate.Value
}).ToList();
// typeofeep
var q2 = (from A in historyId
select new PersonMedicalHistoryList
{
v_AntecedentTypeName = "Ocupacionales, " + A.v_Occupation,
v_DiseasesName = ConcatenateTypeOfeep(A.v_HistoryId),
v_DateOrGroup = A.d_StartDate.Value.ToString("MM/yyyy") + " - " + A.d_EndDate.Value.ToString("MM/yyyy"),
d_StartDate = A.d_StartDate,
}).ToList();
// workstationdangers
var q3 = (from A in historyId
select new PersonMedicalHistoryList
{
v_AntecedentTypeName = "Ocupacionales, " + A.v_Occupation,
v_DiseasesName = ConcatenateWorkStationDangers(A.v_HistoryId),
v_DateOrGroup = A.d_StartDate.Value.ToString("MM/yyyy") + " - " + A.d_EndDate.Value.ToString("MM/yyyy"),
d_StartDate = A.d_StartDate,
}).ToList();
// noxioushabits
var q4 = (from A in ctx.NoxiousHabits
join B in ctx.SystemParameter on new { a = A.i_TypeHabitsId.Value, b = 148 } // HÁBITOS NOCIVOS
equals new { a = B.i_ParameterId, b = B.i_GroupId } into B_join
from B in B_join.DefaultIfEmpty()
where A.i_IsDeleted == 0 && A.v_PersonId == patientId
select new PersonMedicalHistoryList
{
v_AntecedentTypeName = "Hábitos Nocivos",
v_DiseasesName = B.v_Value1 + ", " + A.v_Frequency,
}).ToList();
// familymedicalantecedents
var q5tmp = (from A in ctx.FamilyMedicalAntecedents
join B in ctx.SystemParameter on new { a = A.i_TypeFamilyId.Value, b = 149 } // ANTECEDENTES FAMILIARES MÉDICOS
equals new { a = B.i_ParameterId, b = B.i_GroupId } into B_join
from B in B_join.DefaultIfEmpty()
join C in ctx.SystemParameter on new { a = B.i_ParentParameterId.Value, b = 149 }
equals new { a = C.i_ParameterId, b = C.i_GroupId } into C_join
from C in C_join.DefaultIfEmpty()
where A.i_IsDeleted == 0 && A.v_PersonId == patientId
group C by new { C.i_ParameterId, C.v_Value1 } into g
select new PersonMedicalHistoryList
{
v_AntecedentTypeName = "Familiares",
i_TypeFamilyId = g.Key.i_ParameterId,
v_TypeFamilyName = g.Key.v_Value1
}).ToList();
var q5 = (from A in q5tmp
select new PersonMedicalHistoryList
{
v_AntecedentTypeName = A.v_AntecedentTypeName,
v_DiseasesName = ConcatenateFamilyMedicalAntecedents(patientId, A.i_TypeFamilyId),
v_DateOrGroup = A.v_TypeFamilyName
}).ToList();
#endregion
#region Fusion
if (q1.Count > 0)
{
lis.AddRange(q1);
}
if (q2.Count > 0)
{
lis.AddRange(q2);
}
if (q3.Count > 0)
{
lis.AddRange(q3);
}
if (q4.Count > 0)
{
lis.AddRange(q4);
}
if (q5.Count > 0)
{
lis.AddRange(q5);
}
#endregion
return(lis.OrderBy(x => x.v_AntecedentTypeName).ToList());
}
catch (Exception ex)
{
return(null);
}
}
public List <Sigesoft.Node.WinClient.BE.PersonMedicalHistoryList> GetPersonMedicalHistoryReport(string pstrPersonId)
{
//mon.IsActive = true;
try
{
SigesoftEntitiesModel dbContext = new SigesoftEntitiesModel();
var query = (from A in dbContext.personmedicalhistory
join B in dbContext.systemparameter on new { a = A.v_DiseasesId, b = 147 }
equals new { a = B.v_Value1, b = B.i_GroupId } into B_join
from B in B_join.DefaultIfEmpty()
join C in dbContext.systemparameter on new { a = B.i_ParentParameterId.Value, b = 147 }
equals new { a = C.i_ParameterId, b = C.i_GroupId } into C_join
from C in C_join.DefaultIfEmpty()
join D in dbContext.diseases on A.v_DiseasesId equals D.v_DiseasesId
join E in dbContext.systemparameter on new { a = A.i_TypeDiagnosticId.Value, b = 139 }
equals new { a = E.i_ParameterId, b = E.i_GroupId } into E_join
from E in E_join.DefaultIfEmpty()
where (A.i_IsDeleted == 0) && (A.v_PersonId == pstrPersonId)
select new Sigesoft.Node.WinClient.BE.PersonMedicalHistoryList
{
v_PersonMedicalHistoryId = A.v_PersonMedicalHistoryId,
v_PersonId = A.v_PersonId,
v_DiseasesId = A.v_DiseasesId,
v_DiseasesName = D.v_Name,
i_TypeDiagnosticId = A.i_TypeDiagnosticId,
d_StartDate = A.d_StartDate.Value,
v_TreatmentSite = A.v_TreatmentSite,
v_GroupName = C.v_Value1,
v_TypeDiagnosticName = E.v_Value1,
v_DiagnosticDetail = A.v_DiagnosticDetail,
i_Answer = A.i_AnswerId.Value,
}).ToList();
// add the sequence number on the fly
var query1 = new List <Sigesoft.Node.WinClient.BE.PersonMedicalHistoryList>();
query1 = query.Select((x, index) => new Sigesoft.Node.WinClient.BE.PersonMedicalHistoryList
{
i_Item = index + 1,
v_PersonMedicalHistoryId = x.v_PersonMedicalHistoryId,
v_PersonId = x.v_PersonId,
v_DiseasesId = x.v_DiseasesId,
v_DiseasesName = x.v_DiseasesName,
i_TypeDiagnosticId = x.i_TypeDiagnosticId,
d_StartDate = x.d_StartDate,
v_TreatmentSite = x.v_TreatmentSite,
v_GroupName = x.v_GroupName,
v_TypeDiagnosticName = x.v_TypeDiagnosticName,
v_DiagnosticDetail = x.v_DiagnosticDetail,
i_Answer = x.i_Answer,
}).ToList();
//List<PersonMedicalHistoryList> objData = query.ToList();
return(query1);
}
catch (Exception ex)
{
return(null);
}
}
public List <recetadespachoDto> GetRecetaToReport(ref OperationResult pobjOperationResult, string serviceId)
{
try
{
using (var dbContext = new SigesoftEntitiesModel())
{
pobjOperationResult.Success = 1;
var medicamentos = MedicamentoDao.ObtenerContasolMedicamentos();
var FirmaMedicoMedicina = new ServiceBL().ObtenerFirmaMedicoExamen(serviceId, Constants.ATENCION_INTEGRAL_ID, Constants.EXAMEN_FISICO_7C_ID);
var consulta = (from r in dbContext.receta
join d in dbContext.diagnosticrepository on r.v_DiagnosticRepositoryId equals d.v_DiagnosticRepositoryId into dJoin
from d in dJoin.DefaultIfEmpty()
join s in dbContext.service on d.v_ServiceId equals s.v_ServiceId into sJoin
from s in sJoin.DefaultIfEmpty()
join C in dbContext.organization on new { id = "N009-OO000000052" } equals new { id = C.v_OrganizationId } into C_join
from C in C_join.DefaultIfEmpty()
join p in dbContext.person on s.v_PersonId equals p.v_PersonId into pJoin
from p in pJoin.DefaultIfEmpty()
where s.v_ServiceId.Equals(serviceId)
select new recetadespachoDto
{
RecetaId = r.i_IdReceta,
CantidadRecetada = r.d_Cantidad ?? 0,
//Medicamento = m.NombreCompleto,
//Presentacion = m.Presentacion,
//Ubicacion = m.Ubicacion,
NombrePaciente = p.v_FirstLastName + " " + p.v_SecondLastName + " " + p.v_FirstName,
FechaFin = r.t_FechaFin ?? DateTime.Now,
Duracion = r.v_Duracion,
Dosis = r.v_Posologia,
NombreMedico = FirmaMedicoMedicina.Value2,
RubricaMedico = FirmaMedicoMedicina.Value5,
MedicoNroCmp = FirmaMedicoMedicina.Value3,
NombreClinica = C.v_Name,
DireccionClinica = C.v_Address,
LogoClinica = C.b_Image,
Despacho = (r.i_Lleva ?? 0) == 1,
MedicinaId = r.v_IdProductoDetalle
}).ToList();
//consulta = consulta.GroupBy(p => p.RecetaId).Select(p => p.FirstOrDefault()).ToList();
foreach (var item in consulta)
{
var prod = medicamentos.FirstOrDefault(p => p.IdProductoDetalle.Equals(item.MedicinaId));
if (prod == null)
{
continue;
}
item.Medicamento = prod.NombreCompleto;
item.Presentacion = prod.Presentacion;
item.Ubicacion = prod.Ubicacion;
}
return(consulta);
}
}
catch (Exception ex)
{
pobjOperationResult.Success = 0;
pobjOperationResult.ErrorMessage = ex.Message;
pobjOperationResult.ExceptionMessage = ex.InnerException != null
? ex.InnerException.Message
: string.Empty;
pobjOperationResult.AdditionalInformation = "RecetaDespachoBl.GetDespacho()";
return(null);
}
}
//Devart.Data.PostgreSql.PgSqlMonitor mon = new Devart.Data.PostgreSql.PgSqlMonitor();
public List <LogList> GetLogsPagedAndFiltered(ref OperationResult pobjOperationResult, int?pintPageIndex, int?pintResultsPerPage, string pstrSortExpression, string pstrFilterExpression, DateTime?pdatBeginDate, DateTime?pdatEndDate)
{
//mon.IsActive = true;
try
{
SigesoftEntitiesModel dbContext = new SigesoftEntitiesModel();
var query = from A in dbContext.log
join B in dbContext.node on A.i_NodeLogId equals B.i_NodeId
join C in dbContext.organization on A.v_OrganizationId equals C.v_OrganizationId into C_join
from C in C_join.DefaultIfEmpty()
join D in dbContext.systemuser on A.i_SystemUserId equals D.i_SystemUserId into D_join
from D in D_join.DefaultIfEmpty()
join J1 in dbContext.systemparameter on new { a = 102, b = A.i_EventTypeId.Value }
equals new { a = J1.i_GroupId, b = J1.i_ParameterId }
join J2 in dbContext.systemparameter on new { a = 111, b = A.i_Success.Value }
equals new { a = J2.i_GroupId, b = J2.i_ParameterId }
select new LogList
{
v_LogId = A.v_LogId,
i_EventTypeId = A.i_EventTypeId.Value,
v_EventTypeName = J1.v_Value1,
i_NodeId = A.i_NodeLogId.Value,
v_NodeName = B.v_Description,
v_OrganizationId = A.v_OrganizationId,
v_OrganizationName = C.v_Name,
i_SystemUserId = A.i_SystemUserId.Value,
v_SystemUserName = D.v_UserName,
d_Date = A.d_Date.Value,
v_ProcessEntity = A.v_ProcessEntity,
v_ElementItem = A.v_ElementItem,
i_Success = A.i_Success.Value,
v_SuccessName = J2.v_Value1,
v_ErrorException = A.v_ErrorException
};
if (!string.IsNullOrEmpty(pstrFilterExpression))
{
query = query.Where(pstrFilterExpression);
}
if (pdatBeginDate.HasValue && pdatEndDate.HasValue)
{
query = query.Where("d_Date >= @0 && d_Date <= @1", pdatBeginDate.Value, pdatEndDate.Value);
}
if (!string.IsNullOrEmpty(pstrSortExpression))
{
query = query.OrderBy(pstrSortExpression);
}
if (pintPageIndex.HasValue && pintResultsPerPage.HasValue)
{
int intStartRowIndex = pintPageIndex.Value * pintResultsPerPage.Value;
query = query.Skip(intStartRowIndex);
}
if (pintResultsPerPage.HasValue)
{
query = query.Take(pintResultsPerPage.Value);
}
List <LogList> objData = query.ToList();
pobjOperationResult.Success = 1;
return(objData);
}
catch (Exception ex)
{
pobjOperationResult.Success = 0;
pobjOperationResult.ExceptionMessage = ex.Message;
return(null);
}
}
