public List <CalendarList> GetCalendarsPagedAndFiltered(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.calendar join B in dbContext.person on A.v_PersonId equals B.v_PersonId join C in dbContext.systemparameter on new { a = A.i_LineStatusId.Value, b = 120 } equals new { a = C.i_ParameterId, b = C.i_GroupId } join D in dbContext.service on A.v_ServiceId equals D.v_ServiceId join E in dbContext.systemparameter on new { a = A.i_ServiceTypeId.Value, b = 119 } equals new { a = E.i_ParameterId, b = E.i_GroupId } join F in dbContext.systemparameter on new { a = A.i_ServiceId.Value, b = 119 } equals new { a = F.i_ParameterId, b = F.i_GroupId } join G in dbContext.systemparameter on new { a = A.i_NewContinuationId.Value, b = 121 } equals new { a = G.i_ParameterId, b = G.i_GroupId } join H in dbContext.systemparameter on new { a = A.i_CalendarStatusId.Value, b = 122 } equals new { a = H.i_ParameterId, b = H.i_GroupId } join I in dbContext.systemparameter on new { a = A.i_IsVipId.Value, b = 111 } equals new { a = I.i_ParameterId, b = I.i_GroupId } join J in dbContext.protocol on new { a = D.v_ProtocolId } equals new { a = J.v_ProtocolId } into J_join from J in J_join.DefaultIfEmpty() join K in dbContext.systemparameter on new { a = J.i_EsoTypeId.Value, b = 118 } equals new { a = K.i_ParameterId, b = K.i_GroupId } into K_join from K in K_join.DefaultIfEmpty() // Empresa / Sede Cliente ************** join oc in dbContext.organization on new { a = J.v_CustomerOrganizationId } equals new { a = oc.v_OrganizationId } into oc_join from oc in oc_join.DefaultIfEmpty() join lc in dbContext.location on new { a = J.v_CustomerOrganizationId, b = J.v_CustomerLocationId } equals new { a = lc.v_OrganizationId, b = lc.v_LocationId } into lc_join from lc in lc_join.DefaultIfEmpty() // Empresa / Sede Trabajo ******************************************************** join ow in dbContext.organization on new { a = J.v_WorkingOrganizationId } equals new { a = ow.v_OrganizationId } into ow_join from ow in ow_join.DefaultIfEmpty() join lw in dbContext.location on new { a = J.v_WorkingOrganizationId, b = J.v_WorkingLocationId } equals new { a = lw.v_OrganizationId, b = lw.v_LocationId } into lw_join from lw in lw_join.DefaultIfEmpty() //************************************************************************************ join N in dbContext.organization on new { a = D.v_OrganizationId } equals new { a = N.v_OrganizationId } into N_join from N in N_join.DefaultIfEmpty() join O in dbContext.location on new { a = N.v_OrganizationId, b = D.v_LocationId } equals new { a = O.v_OrganizationId, b = O.v_LocationId } into O_join from O in O_join.DefaultIfEmpty() join J3 in dbContext.systemparameter on new { a = D.i_ServiceStatusId.Value, b = 125 } equals new { a = J3.i_ParameterId, b = J3.i_GroupId } into J3_join from J3 in J3_join.DefaultIfEmpty() join J4 in dbContext.systemparameter on new { a = D.i_AptitudeStatusId.Value, b = 124 } equals new { a = J4.i_ParameterId, b = J4.i_GroupId } into J4_join from J4 in J4_join.DefaultIfEmpty() join J5 in dbContext.datahierarchy on new { a = B.i_DocTypeId.Value, b = 106 } equals new { a = J5.i_ItemId, b = J5.i_GroupId } into J5_join from J5 in J5_join.DefaultIfEmpty() join J1 in dbContext.systemuser on new { i_InsertUserId = A.i_InsertUserId.Value } equals new { i_InsertUserId = J1.i_SystemUserId } into J1_join from J1 in J1_join.DefaultIfEmpty() join J2 in dbContext.systemuser on new { i_UpdateUserId = A.i_UpdateUserId.Value } equals new { i_UpdateUserId = J2.i_SystemUserId } into J2_join from J2 in J2_join.DefaultIfEmpty() where A.i_IsDeleted == 0 select new CalendarList { v_CalendarId = A.v_CalendarId, d_DateTimeCalendar = A.d_DateTimeCalendar.Value, v_Pacient = B.v_FirstLastName + " " + B.v_SecondLastName + " " + B.v_FirstName, v_NumberDocument = B.v_DocNumber, v_LineStatusName = C.v_Value1, v_ServiceId = A.v_ServiceId, v_ProtocolId = A.v_ProtocolId, v_ProtocolName = J.v_Name, v_ServiceStatusName = J3.v_Value1, v_AptitudeStatusName = J4.v_Value1, v_ServiceTypeName = E.v_Value1, v_ServiceName = F.v_Value1, v_NewContinuationName = G.v_Value1, v_PersonId = A.v_PersonId, v_CalendarStatusName = H.v_Value1, i_ServiceStatusId = D.i_ServiceStatusId.Value, v_IsVipName = I.v_Value1, i_ServiceId = A.i_ServiceId.Value, i_ServiceTypeId = A.i_ServiceTypeId.Value, i_CalendarStatusId = A.i_CalendarStatusId.Value, i_MasterServiceId = A.i_ServiceId.Value, i_NewContinuationId = A.i_NewContinuationId.Value, i_LineStatusId = A.i_LineStatusId.Value, i_IsVipId = A.i_IsVipId.Value, i_EsoTypeId = J.i_EsoTypeId.Value, v_EsoTypeName = K.v_Value1, v_OrganizationLocationProtocol = oc.v_Name + " / " + lc.v_Name, v_OrganizationLocationService = N.v_Name + " / " + O.v_Name, v_CreationUser = J1.v_UserName, v_UpdateUser = J2.v_UserName, d_CreationDate = A.d_InsertDate, d_UpdateDate = A.d_UpdateDate, i_IsDeleted = A.i_IsDeleted, v_CustomerOrganizationId = oc.v_OrganizationId, v_CustomerLocationId = lc.v_LocationId, v_DocTypeName = J5.v_Value1, v_DocNumber = B.v_DocNumber, i_DocTypeId = B.i_DocTypeId.Value, d_EntryTimeCM = A.d_EntryTimeCM.Value, v_WorkingOrganizationName = ow.v_Name }; if (!string.IsNullOrEmpty(pstrFilterExpression)) { query = query.Where(pstrFilterExpression); } if (pdatBeginDate.HasValue && pdatEndDate.HasValue) { query = query.Where("d_DateTimeCalendar >= @0 && d_DateTimeCalendar <= @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 <CalendarList> objData = query.ToList(); pobjOperationResult.Success = 1; return(objData); } catch (Exception ex) { pobjOperationResult.Success = 0; pobjOperationResult.ExceptionMessage = Common.Utils.ExceptionFormatter(ex); 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); } }