public List <ServiceComponentList> GetServiceComponents(string pstrServiceId)
{
int isDeleted = (int)SiNo.No;
int isRequired = (int)SiNo.Si;
try
{
DatabaseContext dbContext = new DatabaseContext();
var query = (from A in dbContext.ServiceComponent
join B in dbContext.SystemParameter on new { a = A.i_ServiceComponentStatusId.Value, b = 127 }
equals new { a = B.i_ParameterId, b = B.i_GroupId }
join C in dbContext.Component on A.v_ComponentId equals C.v_ComponentId
join D in dbContext.SystemParameter on new { a = A.i_QueueStatusId.Value, b = 128 }
equals new { a = D.i_ParameterId, b = D.i_GroupId }
join E in dbContext.Service on A.v_ServiceId equals E.v_ServiceId
join F in dbContext.SystemParameter on new { a = C.i_CategoryId, b = 116 }
equals new { a = F.i_ParameterId, b = F.i_GroupId } into F_join
from F in F_join.DefaultIfEmpty()
where A.v_ServiceId == pstrServiceId &&
A.i_IsDeleted == isDeleted &&
A.i_IsRequiredId == isRequired
select new ServiceComponentList
{
v_ComponentId = A.v_ComponentId,
v_ComponentName = C.v_Name,
i_ServiceComponentStatusId = A.i_ServiceComponentStatusId.Value,
v_ServiceComponentStatusName = B.v_Value1,
d_StartDate = A.d_StartDate.Value,
d_EndDate = A.d_EndDate.Value,
i_QueueStatusId = A.i_QueueStatusId.Value,
v_QueueStatusName = D.v_Value1,
ServiceStatusId = E.i_ServiceStatusId.Value,
v_Motive = E.v_Motive,
i_CategoryId = C.i_CategoryId,
v_CategoryName = C.i_CategoryId == -1 ? C.v_Name : F.v_Value1,
v_ServiceId = E.v_ServiceId,
v_ServiceComponentId = A.v_ServiceComponentId,
});
var objData = query.AsEnumerable()
.Where(s => s.i_CategoryId != -1)
.GroupBy(x => x.i_CategoryId)
.Select(group => group.First());
List <ServiceComponentList> obj = objData.ToList();
obj.AddRange(query.Where(p => p.i_CategoryId == -1));
return(obj);
}
catch (Exception ex)
{
return(null);
}
}
public BoardExamsCustom GetExamsForConsult(BoardExamsCustom data)
{
try
{
var query = (from A in ctx.ServiceComponent
join B in ctx.SystemParameter on new { a = A.i_ServiceComponentStatusId.Value, b = 127 }
equals new { a = B.i_ParameterId, b = B.i_GroupId }
join C in ctx.Component on A.v_ComponentId equals C.v_ComponentId
join D in ctx.SystemParameter on new { a = A.i_QueueStatusId.Value, b = 128 }
equals new { a = D.i_ParameterId, b = D.i_GroupId }
join E in ctx.Service on A.v_ServiceId equals E.v_ServiceId
join F in ctx.SystemParameter on new { a = C.i_CategoryId, b = 116 }
equals new { a = F.i_ParameterId, b = F.i_GroupId } into F_join
from F in F_join.DefaultIfEmpty()
where A.v_ServiceId == data.ServiceId &&
A.i_IsDeleted == (int)SiNo.No &&
A.i_IsRequiredId == (int)SiNo.Si
select new ExamsCustom
{
v_ComponentId = A.v_ComponentId,
v_PersonId = E.v_PersonId,
v_ComponentName = C.v_Name,
i_ServiceComponentStatusId = A.i_ServiceComponentStatusId.Value,
v_ServiceComponentStatusName = B.v_Value1,
d_StartDate = A.d_StartDate.Value,
d_EndDate = A.d_EndDate.Value,
i_QueueStatusId = A.i_QueueStatusId.Value,
v_QueueStatusName = D.v_Value1,
ServiceStatusId = E.i_ServiceStatusId.Value,
v_Motive = E.v_Motive,
i_CategoryId = C.i_CategoryId,
v_CategoryName = C.i_CategoryId == -1 ? C.v_Name : F.v_Value1,
v_ServiceId = E.v_ServiceId,
v_ServiceComponentId = A.v_ServiceComponentId,
});
var objData = query.AsEnumerable()
.Where(s => s.i_CategoryId != -1)
.GroupBy(x => x.i_CategoryId)
.Select(group => group.First());
List <ExamsCustom> obj = objData.ToList();
obj.AddRange(query.Where(p => p.i_CategoryId == -1));
data.List = obj;
return(data);
}
catch (Exception ex)
{
return(null);
}
}
private List <ServiceComponentList> obtenerServiceComponentsByServiceId(string serviceId)
{
try
{
var dbContext = new SigesoftEntitiesModel();
var query = (from A in dbContext.servicecomponent
join C in dbContext.systemuser on A.i_MedicoTratanteId equals C.i_SystemUserId
join D in dbContext.person on C.v_PersonId equals D.v_PersonId
join B in dbContext.component on A.v_ComponentId equals B.v_ComponentId
join F in dbContext.systemparameter on new { a = B.i_CategoryId.Value, b = 116 }
equals new { a = F.i_ParameterId, b = F.i_GroupId } into F_join
from F in F_join.DefaultIfEmpty()
join H in dbContext.service on A.v_ServiceId equals H.v_ServiceId
join I in dbContext.protocol on H.v_ProtocolId equals I.v_ProtocolId
join G in dbContext.plan on new { a = I.v_ProtocolId, b = A.v_IdUnidadProductiva }
equals new { a = G.v_ProtocoloId, b = G.v_IdUnidadProductiva }
where A.v_ServiceId == serviceId &&
A.i_IsDeleted == 0 &&
A.i_IsRequiredId == 1 &&
(A.d_SaldoPaciente.Value > 0 || A.d_SaldoAseguradora.Value > 0)
select new ServiceComponentList
{
v_ServiceComponentId = A.v_ServiceComponentId,
v_ComponentId = A.v_ComponentId,
r_Price = A.r_Price.Value,
v_ComponentName = B.v_Name,
v_CategoryName = F.v_Value1,
MedicoTratante = D.v_FirstName + " " + D.v_FirstLastName + " " + D.v_SecondLastName,
d_InsertDate = A.d_InsertDate,
d_SaldoPaciente = A.d_SaldoPaciente.Value,
d_SaldoAseguradora = A.d_SaldoAseguradora.Value,
i_EsDeducible = G.i_EsDeducible.Value,
i_EsCoaseguro = G.i_EsCoaseguro.Value,
d_Importe = G.d_Importe
}).ToList();
return(query);
}
catch (Exception ex)
{
return(null);
}
}
public static List <Categoria> GetAllComponentsByService(string pstrString)
{
int isDeleted = (int)SiNo.No;
int isRequired = (int)SiNo.Si;
try
{
// Acá traigo toda mi data que nececeito... mas que todo para los padres
var query = (from A in ctx.ServiceComponent
join B in ctx.SystemParameter on new { a = A.i_ServiceComponentStatusId.Value, b = 127 }
equals new { a = B.i_ParameterId, b = B.i_GroupId }
join C in ctx.Component on A.v_ComponentId equals C.v_ComponentId
join D in ctx.SystemParameter on new { a = A.i_QueueStatusId.Value, b = 128 }
equals new { a = D.i_ParameterId, b = D.i_GroupId }
join E in ctx.Service on A.v_ServiceId equals E.v_ServiceId
join F in ctx.SystemParameter on new { a = C.i_CategoryId, b = 116 }
equals new { a = F.i_ParameterId, b = F.i_GroupId } into F_join
from F in F_join.DefaultIfEmpty()
// Usuario Medico Evaluador / Medico Aprobador ****************************
join me in ctx.SystemUser on A.i_ApprovedUpdateUserId equals me.i_SystemUserId into me_join
from me in me_join.DefaultIfEmpty()
join pme in ctx.Professional on me.v_PersonId equals pme.v_PersonId into pme_join
from pme in pme_join.DefaultIfEmpty()
where A.v_ServiceId == pstrString &&
A.i_IsDeleted == isDeleted &&
A.i_IsRequiredId == isRequired
select new ServiceComponentList
{
v_ComponentId = A.v_ComponentId,
v_ComponentName = C.v_Name,
i_ServiceComponentStatusId = A.i_ServiceComponentStatusId.Value,
v_ServiceComponentStatusName = B.v_Value1,
d_StartDate = A.d_StartDate.Value,
d_EndDate = A.d_EndDate.Value,
i_QueueStatusId = A.i_QueueStatusId.Value,
v_QueueStatusName = D.v_Value1,
ServiceStatusId = E.i_ServiceStatusId.Value,
v_Motive = E.v_Motive,
i_CategoryId = C.i_CategoryId,
v_CategoryName = C.i_CategoryId == -1 ? C.v_Name : F.v_Value1,
v_ServiceId = E.v_ServiceId,
v_ServiceComponentId = A.v_ServiceComponentId,
ApprovedUpdateUser = me.v_UserName
});
// acá lleno mi entidad padre con la consulta de arriba
List <Categoria> xxx = new List <Categoria>();
Categoria oCategoria = null;
foreach (var item in query)
{
oCategoria = new Categoria();
oCategoria.v_ComponentId = item.v_ComponentId;
oCategoria.v_ComponentName = item.v_ComponentName;
oCategoria.i_CategoryId = item.i_CategoryId;
oCategoria.v_CategoryName = item.v_CategoryName;// item.i_CategoryId.Value == -1 ? item.v_CategoryName : item.v_ServiceComponentStatusName;
oCategoria.v_ServiceComponentStatusName = item.v_ServiceComponentStatusName;
oCategoria.v_QueueStatusName = item.v_QueueStatusName;
oCategoria.i_ServiceComponentStatusId = item.i_ServiceComponentStatusId.Value;
oCategoria.ApprovedUpdateUser = item.ApprovedUpdateUser;
xxx.Add(oCategoria);
}
//en mi caso hago un groupby porque mi data se repite
var objData = xxx.AsEnumerable()
.Where(s => s.i_CategoryId != -1)
.GroupBy(x => x.i_CategoryId)
.Select(group => group.First());
List <Categoria> obj = objData.ToList();
Categoria objCategoriaList;
List <Categoria> Lista = new List <Categoria>();
//acá recorreo los padres para poder llenar los hijos
for (int i = 0; i < obj.Count(); i++)
{
objCategoriaList = new Categoria();
objCategoriaList.i_CategoryId = obj[i].i_CategoryId.Value;
objCategoriaList.v_CategoryName = obj[i].v_CategoryName;
objCategoriaList.v_ServiceComponentStatusName = obj[i].v_ServiceComponentStatusName;
objCategoriaList.v_QueueStatusName = obj[i].v_QueueStatusName;
objCategoriaList.i_ServiceComponentStatusId = obj[i].i_ServiceComponentStatusId;
objCategoriaList.ApprovedUpdateUser = obj[i].ApprovedUpdateUser;
// en esta variable x obtengo los hijos de un padre (cada bucle me da un padre y con ese id busco sus hijos)
var x = query.ToList().FindAll(p => p.i_CategoryId == obj[i].i_CategoryId.Value);
x.Sort((z, y) => z.v_ComponentName.CompareTo(y.v_ComponentName));
ComponentDetailList objComponentDetailList;
List <ComponentDetailList> ListaComponentes = new List <ComponentDetailList>();
//recorro los hijos y los agrego dentro del padre
foreach (var item in x)
{
objComponentDetailList = new ComponentDetailList();
objComponentDetailList.v_ComponentId = item.v_ComponentId;
objComponentDetailList.v_ComponentName = item.v_ComponentName;
objComponentDetailList.v_ServiceComponentId = item.v_ServiceComponentId;
objComponentDetailList.StatusComponentId = item.i_ServiceComponentStatusId.Value;
objComponentDetailList.ApprovedUpdateUser = item.ApprovedUpdateUser;
objComponentDetailList.StatusComponent = item.v_ServiceComponentStatusName;
ListaComponentes.Add(objComponentDetailList);
}
objCategoriaList.Componentes = ListaComponentes;
Lista.Add(objCategoriaList);
}
//retorno lista jerarquizada
return(Lista);
}
catch (Exception ex)
{
return(null);
}
}
public List <TramasList> GettramasPageAndFilteredPartos(ref OperationResult pobjOperationResult, int?pintPageIndex, int?pintResultsPerPage, string pstrSortExpression, string pstrFilterExpression, DateTime?pdatBeginDate, DateTime?pdatEndDate)
{
try
{
SigesoftEntitiesModel dbContext = new SigesoftEntitiesModel();
var query = from A in dbContext.tramas
join C in dbContext.systemuser on A.i_InsertUserId equals C.i_SystemUserId
join D in dbContext.person on C.v_PersonId equals D.v_PersonId
join E in dbContext.systemuser on new { i_UpdateUserId = A.i_UpdateUserId.Value }
equals new { i_UpdateUserId = E.i_SystemUserId } into E_join
from E in E_join.DefaultIfEmpty()
join F in dbContext.person on new { v_PersonId = E.v_PersonId }
equals new { v_PersonId = F.v_PersonId } into F_join
from F in F_join.DefaultIfEmpty()
join G in dbContext.systemparameter on new { a = A.i_TipoParto.Value, b = 350 }
equals new { a = G.i_ParameterId, b = G.i_GroupId } into G_join // TIPO PARTO DETAIL
from G in G_join.DefaultIfEmpty()
join H in dbContext.systemparameter on new { a = A.i_TipoNacimiento.Value, b = 351 }
equals new { a = H.i_ParameterId, b = H.i_GroupId } into H_join // NACIMIENTO DETAIL
from H in H_join.DefaultIfEmpty()
join I in dbContext.systemparameter on new { a = A.i_TipoComplicacion.Value, b = 352 }
equals new { a = I.i_ParameterId, b = I.i_GroupId } into I_join // COMPLICACION DETAIL
from I in I_join.DefaultIfEmpty()
where A.i_IsDeleted == 0 && A.v_TipoRegistro == "Partos"
select new TramasList
{
v_TramaId = A.v_TramaId,
v_TipoRegistro = A.v_TipoRegistro,
d_FechaIngreso = A.d_FechaIngreso.Value,
tipoParto_Detail = G.v_Value1,
tipoNacimiento_Detail = H.v_Value1,
tipoCompliacacion_Detail = I.v_Value1,
d_InsertDate = A.d_InsertDate.Value,
d_UpdateDate = A.d_UpdateDate.Value,
User_Crea = D.v_FirstName + " " + D.v_FirstLastName + " " + D.v_SecondLastName,
User_Act = E.i_SystemUserId == null ? "---" : F.v_FirstName + " " + F.v_FirstLastName + " " + F.v_SecondLastName,
};
#region
if (!string.IsNullOrEmpty(pstrFilterExpression))
{
query = query.Where(pstrFilterExpression);
}
if (pdatBeginDate.HasValue && pdatEndDate.HasValue)
{
query = query.Where("d_FechaIngreso >= @0 && d_FechaIngreso <= @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);
}
#endregion
List <TramasList> objData = query.ToList();
var tramasdetalle = (from a in objData
select new TramasList
{
v_TramaId = a.v_TramaId,
v_TipoRegistro = a.v_TipoRegistro,
d_FechaIngreso = a.d_FechaIngreso,
tipoParto_Detail = a.tipoParto_Detail,
tipoNacimiento_Detail = a.tipoNacimiento_Detail,
tipoCompliacacion_Detail = a.tipoCompliacacion_Detail,
User_Crea = a.User_Crea,
User_Act = a.User_Act,
d_InsertDate = a.d_InsertDate,
d_UpdateDate = a.d_UpdateDate,
}).ToList();
pobjOperationResult.Success = 1;
return(tramasdetalle);
}
catch (Exception e)
{
pobjOperationResult.Success = 0;
pobjOperationResult.ExceptionMessage = Common.Utils.ExceptionFormatter(e);
return(null);
//Console.WriteLine(e);
//throw;
}
}
public List <TramasList> GettramasPageAndFilteredHospitalizacion(ref OperationResult pobjOperationResult, int?pintPageIndex, int?pintResultsPerPage, string pstrSortExpression, string pstrFilterExpression, DateTime?pdatBeginDate, DateTime?pdatEndDate)
{
try
{
SigesoftEntitiesModel dbContext = new SigesoftEntitiesModel();
var query = from A in dbContext.tramas
join B in dbContext.systemparameter on new { a = A.i_GrupoEtario.Value, b = 347 }
equals new { a = B.i_ParameterId, b = B.i_GroupId } into B_join // GRUPO ETARIO
from B in B_join.DefaultIfEmpty()
join C in dbContext.systemuser on A.i_InsertUserId equals C.i_SystemUserId
join D in dbContext.person on C.v_PersonId equals D.v_PersonId
join E in dbContext.systemuser on new { i_UpdateUserId = A.i_UpdateUserId.Value }
equals new { i_UpdateUserId = E.i_SystemUserId } into E_join
from E in E_join.DefaultIfEmpty()
join F in dbContext.person on new { v_PersonId = E.v_PersonId }
equals new { v_PersonId = F.v_PersonId } into F_join
from F in F_join.DefaultIfEmpty()
join G in dbContext.systemparameter on new { a = A.i_UPS.Value, b = 349 }
equals new { a = G.i_ParameterId, b = G.i_GroupId } into G_join // UPS DETAIL
from G in G_join.DefaultIfEmpty()
where A.i_IsDeleted == 0 && A.v_TipoRegistro == "Hospitalización"
select new TramasList
{
v_TramaId = A.v_TramaId,
v_TipoRegistro = A.v_TipoRegistro,
d_FechaIngreso = A.d_FechaIngreso.Value,
i_Genero = A.i_Genero,
GrupoEtario = B.v_Value1,
v_DiseasesName = A.v_DiseasesName,
v_CIE10Id = A.v_CIE10Id,
d_InsertDate = A.d_InsertDate.Value,
d_UpdateDate = A.d_UpdateDate.Value,
User_Crea = D.v_FirstName + " " + D.v_FirstLastName + " " + D.v_SecondLastName,
User_Act = E.i_SystemUserId == null ? "---" : F.v_FirstName + " " + F.v_FirstLastName + " " + F.v_SecondLastName,
d_FechaAlta = A.d_FechaAlta.Value,
i_UPS = A.i_UPS.Value,
i_Procedimiento = A.i_Procedimiento,
ups_Detail = G.v_Value1
};
if (!string.IsNullOrEmpty(pstrFilterExpression))
{
query = query.Where(pstrFilterExpression);
}
if (pdatBeginDate.HasValue && pdatEndDate.HasValue)
{
query = query.Where("d_FechaIngreso >= @0 && d_FechaIngreso <= @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 <TramasList> objData = query.ToList();
var tramasdetalle = (from a in objData
select new TramasList
{
v_TramaId = a.v_TramaId,
v_TipoRegistro = a.v_TipoRegistro,
d_FechaIngreso = a.d_FechaIngreso,
Genero = a.i_Genero == 1 ? "MASC" : "FEM",
GrupoEtario = a.GrupoEtario,
v_DiseasesName = a.v_DiseasesName,
v_CIE10Id = a.v_CIE10Id,
User_Crea = a.User_Crea,
User_Act = a.User_Act,
d_InsertDate = a.d_InsertDate,
d_UpdateDate = a.d_UpdateDate,
d_FechaAlta = a.d_FechaAlta,
i_UPS = a.i_UPS,
ups_Detail = a.ups_Detail,
i_Procedimiento = a.i_Procedimiento,
dead_prod = a.i_Procedimiento == 0 ? "NO" : "SI",
}).ToList();
pobjOperationResult.Success = 1;
return(tramasdetalle);
}
catch (Exception e)
{
pobjOperationResult.Success = 0;
pobjOperationResult.ExceptionMessage = Common.Utils.ExceptionFormatter(e);
return(null);
//Console.WriteLine(e);
//throw;
}
}
public List <TramasList> GettramasPageAndFilteredAmbulatorio(ref OperationResult pobjOperationResult, int?pintPageIndex, int?pintResultsPerPage, string pstrSortExpression, string pstrFilterExpression, DateTime?pdatBeginDate, DateTime?pdatEndDate)
{
try
{
SigesoftEntitiesModel dbContext = new SigesoftEntitiesModel();
var query = from A in dbContext.tramas
join B in dbContext.systemparameter on A.i_GrupoEtario equals B.i_ParameterId
join C in dbContext.systemuser on A.i_InsertUserId equals C.i_SystemUserId
join D in dbContext.person on C.v_PersonId equals D.v_PersonId
join E in dbContext.systemuser on new { i_UpdateUserId = A.i_UpdateUserId.Value }
equals new { i_UpdateUserId = E.i_SystemUserId } into E_join
from E in E_join.DefaultIfEmpty()
join F in dbContext.person on new { v_PersonId = E.v_PersonId }
equals new { v_PersonId = F.v_PersonId } into F_join
from F in F_join.DefaultIfEmpty()
where A.i_IsDeleted == 0 && B.i_GroupId == 347 && A.v_TipoRegistro == "Ambulatorio"
select new TramasList
{
v_TramaId = A.v_TramaId,
v_TipoRegistro = A.v_TipoRegistro,
d_FechaIngreso = A.d_FechaIngreso,
i_Genero = A.i_Genero,
GrupoEtario = B.v_Value1,
v_DiseasesName = A.v_DiseasesName,
v_CIE10Id = A.v_CIE10Id,
d_InsertDate = A.d_InsertDate,
d_UpdateDate = A.d_UpdateDate,
User_Crea = D.v_FirstName + " " + D.v_FirstLastName + " " + D.v_SecondLastName,
User_Act = E.i_SystemUserId == null ? "---" : F.v_FirstName + " " + F.v_FirstLastName + " " + F.v_SecondLastName
};
if (!string.IsNullOrEmpty(pstrFilterExpression))
{
query = query.Where(pstrFilterExpression);
}
if (pdatBeginDate.HasValue && pdatEndDate.HasValue)
{
query = query.Where("d_FechaIngreso >= @0 && d_FechaIngreso <= @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 <TramasList> objData = query.ToList();
var tramasdetalle = (from a in objData
select new TramasList
{
v_TramaId = a.v_TramaId,
v_TipoRegistro = a.v_TipoRegistro,
d_FechaIngreso = a.d_FechaIngreso,
Genero = a.i_Genero == 1 ? "MASC" : "FEM",
GrupoEtario = a.GrupoEtario,
v_DiseasesName = a.v_DiseasesName,
v_CIE10Id = a.v_CIE10Id,
User_Crea = a.User_Crea,
User_Act = a.User_Act,
d_InsertDate = a.d_InsertDate,
d_UpdateDate = a.d_UpdateDate
}).ToList();
pobjOperationResult.Success = 1;
return(tramasdetalle);
}
catch (Exception e)
{
pobjOperationResult.Success = 0;
pobjOperationResult.ExceptionMessage = Common.Utils.ExceptionFormatter(e);
return(null);
}
}
public PacientList DevolverDatosPaciente(string pstrServiceId)
{
try
{
DatabaseContext dbContext = new DatabaseContext();
var objEntity = (from a in dbContext.Service
join b in dbContext.Person on a.v_PersonId equals b.v_PersonId
join c in dbContext.SystemParameter on new { a = b.i_SexTypeId.Value, b = 100 }
equals new { a = c.i_ParameterId, b = c.i_GroupId } // GENERO
join d in dbContext.Protocol on a.v_ProtocolId equals d.v_ProtocolId
join E in dbContext.DataHierarchy on new { a = b.i_DepartmentId.Value, b = 113 }
equals new { a = E.i_ItemId, b = E.i_GroupId } into E_join
from E in E_join.DefaultIfEmpty()
join F in dbContext.DataHierarchy on new { a = b.i_ProvinceId.Value, b = 113 }
equals new { a = F.i_ItemId, b = F.i_GroupId } into F_join
from F in F_join.DefaultIfEmpty()
join G in dbContext.DataHierarchy on new { a = b.i_DistrictId.Value, b = 113 }
equals new { a = G.i_ItemId, b = G.i_GroupId } into G_join
from G in G_join.DefaultIfEmpty()
join H in dbContext.Person on a.v_PersonId equals H.v_PersonId into H_join
from H in H_join.DefaultIfEmpty()
join I in dbContext.DataHierarchy on new { a = H.i_DepartmentId.Value, b = 113 }
equals new { a = I.i_ItemId, b = I.i_GroupId } into I_join
from I in I_join.DefaultIfEmpty()
join J in dbContext.DataHierarchy on new { a = H.i_ProvinceId.Value, b = 113 }
equals new { a = J.i_ItemId, b = J.i_GroupId } into J_join
from J in J_join.DefaultIfEmpty()
join K in dbContext.DataHierarchy on new { a = H.i_DistrictId.Value, b = 113 }
equals new { a = K.i_ItemId, b = K.i_GroupId } into K_join
from K in K_join.DefaultIfEmpty()
join M in dbContext.SystemParameter on new { a = H.i_MaritalStatusId.Value, b = 101 }
equals new { a = M.i_ParameterId, b = M.i_GroupId } into M_join
from M in M_join.DefaultIfEmpty()
join N in dbContext.DataHierarchy on new { a = H.i_LevelOfId.Value, b = 108 }
equals new { a = N.i_ItemId, b = N.i_GroupId } into N_join
from N in N_join.DefaultIfEmpty()
join P in dbContext.SystemParameter on new { a = b.i_BloodGroupId.Value, b = 154 }
equals new { a = P.i_ParameterId, b = P.i_GroupId } into P_join
from P in P_join.DefaultIfEmpty()
join Q in dbContext.SystemParameter on new { a = b.i_BloodFactorId.Value, b = 155 }
equals new { a = Q.i_ParameterId, b = Q.i_GroupId } into Q_join
from Q in Q_join.DefaultIfEmpty()
join r in dbContext.ServiceComponent on a.v_ServiceId equals r.v_ServiceId
// Empresa / Sede Cliente ******************************************************
join oc in dbContext.Organization on new { a = d.v_CustomerOrganizationId }
equals new { a = oc.v_OrganizationId } into oc_join
from oc in oc_join.DefaultIfEmpty()
join z in dbContext.Organization on new { a = d.v_EmployerOrganizationId }
equals new { a = z.v_OrganizationId } into z_join
from z in z_join.DefaultIfEmpty()
join lc in dbContext.Location on new { a = d.v_CustomerOrganizationId, b = d.v_CustomerLocationId }
equals new { a = lc.v_OrganizationId, b = lc.v_LocationId } into lc_join
from lc in lc_join.DefaultIfEmpty()
//**********************************************************************************
join O in dbContext.SystemParameter on new { a = 134, b = a.i_MacId.Value }
equals new { a = O.i_GroupId, b = O.i_ParameterId } into O_join
from O in O_join.DefaultIfEmpty()
join J4 in dbContext.SystemParameter on new { ItemId = a.i_AptitudeStatusId.Value, groupId = 124 }
equals new { ItemId = J4.i_ParameterId, groupId = J4.i_GroupId } into J4_join
from J4 in J4_join.DefaultIfEmpty()
join su in dbContext.SystemUser on a.i_UpdateUserMedicalAnalystId.Value equals su.i_SystemUserId into su_join
from su in su_join.DefaultIfEmpty()
join pr in dbContext.Professional on su.v_PersonId equals pr.v_PersonId into pr_join
from pr in pr_join.DefaultIfEmpty()
join su1 in dbContext.SystemUser on a.i_UpdateUserOccupationalMedicaltId.Value equals su1.i_SystemUserId into su1_join
from su1 in su1_join.DefaultIfEmpty()
join pr2 in dbContext.Professional on su1.v_PersonId equals pr2.v_PersonId into pr2_join
from pr2 in pr2_join.DefaultIfEmpty()
where a.v_ServiceId == pstrServiceId && a.i_IsDeleted == 0
select new PacientList
{
Trabajador = b.v_FirstLastName + " " + b.v_SecondLastName + " " + b.v_FirstName,
d_Birthdate = b.d_Birthdate.Value,
//
v_PersonId = b.v_PersonId,
v_FirstLastName = b.v_FirstLastName,
v_SecondLastName = b.v_SecondLastName,
v_FirstName = b.v_FirstName,
v_BirthPlace = b.v_BirthPlace,
v_DepartamentName = I.v_Value1,
v_ProvinceName = J.v_Value1,
v_DistrictName = K.v_Value1,
v_AdressLocation = b.v_AdressLocation,
GradoInstruccion = N.v_Value1,
v_CentroEducativo = b.v_CentroEducativo,
v_MaritalStatus = M.v_Value1,
v_BloodGroupName = P.v_Value1,
v_BloodFactorName = Q.v_Value1,
v_IdService = a.v_ServiceId,
v_OrganitationName = oc.v_Name,
i_NumberLivingChildren = b.i_NumberLivingChildren,
FechaCaducidad = a.d_GlobalExpirationDate,
FechaActualizacion = a.d_UpdateDate,
N_Informe = r.v_ServiceComponentId,
v_Religion = b.v_Religion,
v_Nacionalidad = b.v_Nacionalidad,
v_ResidenciaAnterior = b.v_ResidenciaAnterior,
i_DocTypeId = b.i_DocTypeId,
v_OwnerName = b.v_OwnerName,
v_Employer = z.v_Name,
v_ContactName = b.v_ContactName,
i_Relationship = b.i_Relationship,
v_EmergencyPhone = b.v_EmergencyPhone,
//
Genero = c.v_Value1,
i_SexTypeId = b.i_SexTypeId,
v_DocNumber = b.v_DocNumber,
v_TelephoneNumber = b.v_TelephoneNumber,
Empresa = oc.v_Name,
Sede = lc.v_Name,
v_CurrentOccupation = b.v_CurrentOccupation,
FechaServicio = a.d_ServiceDate.Value,
i_MaritalStatusId = b.i_MaritalStatusId,
// Antecedentes ginecologicos
d_PAP = a.d_PAP.Value,
d_Mamografia = a.d_Mamografia.Value,
v_CiruGine = a.v_CiruGine,
v_Gestapara = a.v_Gestapara,
v_Menarquia = a.v_Menarquia,
v_Findings = a.v_Findings,
d_Fur = a.d_Fur,
v_CatemenialRegime = a.v_CatemenialRegime,
i_MacId = a.i_MacId,
v_Mac = O.v_Value1,
v_Story = a.v_Story,
Aptitud = J4.v_Value1,
b_FirmaAuditor = pr.b_SignatureImage,
FirmaTrabajador = b.b_RubricImage,
HuellaTrabajador = b.b_FingerPrintImage,
FirmaDoctorAuditor = pr2.b_SignatureImage,
}
).ToList();
var DatosMedicoMedicinaEvaluador = ObtenerDatosMedicoMedicina(pstrServiceId, Constants.EXAMEN_FISICO_ID, Constants.EXAMEN_FISICO_7C_ID);
//var DatosMedicoMedicinaAuditor = ObtenerDatosMedicoMedicinaAuditor(pstrServiceId, Constants.EXAMEN_FISICO_ID, Constants.EXAMEN_FISICO_7C_ID);
var result = (from a in objEntity
select new PacientList
{
Trabajador = a.Trabajador,
d_Birthdate = a.d_Birthdate,
v_ContactName = a.v_ContactName,
v_EmergencyPhone = a.v_EmergencyPhone,
i_Relationship = a.i_Relationship,
//
v_PersonId = a.v_PersonId,
v_FirstLastName = a.v_FirstLastName,
v_SecondLastName = a.v_SecondLastName,
v_FirstName = a.v_FirstName,
v_BirthPlace = a.v_BirthPlace,
v_DepartamentName = a.v_DepartamentName,
v_ProvinceName = a.v_ProvinceName,
v_DistrictName = a.v_DistrictName,
GradoInstruccion = a.GradoInstruccion,
v_MaritalStatus = a.v_MaritalStatus,
v_BloodGroupName = a.v_BloodGroupName,
v_BloodFactorName = a.v_BloodFactorName,
v_AdressLocation = a.v_AdressLocation,
v_IdService = a.v_IdService,
v_OrganitationName = a.v_OrganitationName,
i_NumberLivingChildren = a.i_NumberLivingChildren,
v_CentroEducativo = a.v_CentroEducativo,
FechaCaducidad = a.FechaCaducidad,
FechaActualizacion = a.FechaActualizacion,
N_Informe = a.N_Informe,
v_Religion = a.v_Religion,
v_Nacionalidad = a.v_Nacionalidad,
v_ResidenciaAnterior = a.v_ResidenciaAnterior,
i_DocTypeId = a.i_DocTypeId,
v_OwnerName = a.v_OwnerName,
v_Employer = a.v_Employer,
i_MaritalStatusId = a.i_MaritalStatusId,
//
Edad = GetEdad(a.d_Birthdate.Value),
Genero = a.Genero,
i_SexTypeId = a.i_SexTypeId,
v_DocNumber = a.v_DocNumber,
v_TelephoneNumber = a.v_TelephoneNumber,
Empresa = a.Empresa,
Sede = a.Sede,
v_CurrentOccupation = a.v_CurrentOccupation,
FechaServicio = a.FechaServicio,
MedicoGrabaMedicina = DatosMedicoMedicinaEvaluador == null ? "" : DatosMedicoMedicinaEvaluador.ApellidosDoctor + " " + DatosMedicoMedicinaEvaluador.NombreDoctor,
// Antecedentes ginecologicos
d_PAP = a.d_PAP,
d_Mamografia = a.d_Mamografia,
v_CiruGine = a.v_CiruGine,
v_Gestapara = a.v_Gestapara,
v_Menarquia = a.v_Menarquia,
v_Findings = a.v_Findings,
d_Fur = a.d_Fur,
v_CatemenialRegime = a.v_CatemenialRegime,
i_MacId = a.i_MacId,
v_Mac = a.v_Mac,
v_Story = a.v_Story,
Aptitud = a.Aptitud,
b_FirmaEvaluador = DatosMedicoMedicinaEvaluador == null ? null : DatosMedicoMedicinaEvaluador.FirmaMedicoMedicina,
b_FirmaAuditor = a.b_FirmaAuditor,
FirmaTrabajador = a.FirmaTrabajador,
HuellaTrabajador = a.HuellaTrabajador,
FirmaDoctorAuditor = a.FirmaDoctorAuditor
}
).FirstOrDefault();
return(result);
}
catch (Exception ex)
{
return(null);
}
}
