public List <ComponentsByServiceBe> GetComponentsByServiceId(string pstrServiceId) { var isDeleted = 0; var components = (from aaa in _Ctx.ServiceComponent join bbb in _Ctx.Component on aaa.v_ComponentId equals bbb.v_ComponentId join fff in _Ctx.SystemParameter on new { a = bbb.i_CategoryId, b = 116 } // CATEGORIA DEL EXAMEN equals new { a = fff.i_ParameterId, b = fff.i_GroupId } into J5_join from fff in J5_join.DefaultIfEmpty() where (aaa.v_ServiceId == pstrServiceId) && (bbb.i_ComponentTypeId == (int?)Enumeratores.ComponentType.Examen) && (aaa.i_IsDeleted == isDeleted) && (aaa.i_IsRequiredId == (int?)Enumeratores.SiNo.Si) orderby bbb.i_CategoryId, bbb.v_Name select new ComponentsByServiceBe { ComponentId = bbb.v_ComponentId, ComponentName = bbb.v_Name, ServiceComponentId = aaa.v_ServiceComponentId, CategoryId = bbb.i_CategoryId, CategoryName = fff.v_Value1 }).ToList(); return(components); }
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 List <Sigesoft.Node.WinClient.BE.ProtocolList> GetProtocolPagedAndFiltered(ref OperationResult pobjOperationResult, int?pintPageIndex, int?pintResultsPerPage, string pstrSortExpression, string pstrFilterExpression) { //mon.IsActive = true; try { SigesoftEntitiesModel dbContext = new SigesoftEntitiesModel(); var query = from A in dbContext.protocol join B in dbContext.organization on A.v_EmployerOrganizationId equals B.v_OrganizationId join C in dbContext.location on A.v_EmployerLocationId equals C.v_LocationId join D in dbContext.groupoccupation on A.v_GroupOccupationId equals D.v_GroupOccupationId join E in dbContext.systemparameter on new { a = A.i_EsoTypeId.Value, b = 118 } equals new { a = E.i_ParameterId, b = E.i_GroupId } into J3_join from E in J3_join.DefaultIfEmpty() join F in dbContext.organization on A.v_CustomerOrganizationId equals F.v_OrganizationId join I in dbContext.location on A.v_CustomerLocationId equals I.v_LocationId join G in dbContext.organization on A.v_WorkingOrganizationId equals G.v_OrganizationId into J4_join from G in J4_join.DefaultIfEmpty() join J in dbContext.location on A.v_WorkingLocationId equals J.v_LocationId into J6_join from J in J6_join.DefaultIfEmpty() join H in dbContext.systemparameter on new { a = A.i_MasterServiceId.Value, b = 119 } equals new { a = H.i_ParameterId, b = H.i_GroupId } into J5_join from H 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 Sigesoft.Node.WinClient.BE.ProtocolList { v_ProtocolId = A.v_ProtocolId, v_Protocol = A.v_Name, v_Organization = B.v_Name + " / " + C.v_Name, v_Location = C.v_Name, v_EsoType = E.v_Value1, v_GroupOccupation = D.v_Name, v_OrganizationInvoice = F.v_Name + " / " + I.v_Name, v_CostCenter = A.v_CostCenter, v_IntermediaryOrganization = G.v_Name + " / " + J.v_Name, i_ServiceTypeId = A.i_MasterServiceTypeId.Value, v_MasterServiceName = H.v_Value1, i_MasterServiceId = A.i_MasterServiceId.Value, v_OrganizationId = F.v_OrganizationId + "|" + I.v_LocationId, i_EsoTypeId = A.i_EsoTypeId, v_WorkingOrganizationId = G.v_OrganizationId, v_OrganizationInvoiceId = F.v_OrganizationId, v_GroupOccupationId = D.v_GroupOccupationId, v_CreationUser = J1.v_UserName, v_UpdateUser = J2.v_UserName, d_CreationDate = A.d_InsertDate, d_UpdateDate = A.d_UpdateDate, v_LocationId = A.v_EmployerLocationId, v_CustomerLocationId = A.v_CustomerLocationId, v_WorkingLocationId = A.v_WorkingLocationId, i_IsActive = A.i_IsActive }; if (!string.IsNullOrEmpty(pstrFilterExpression)) { query = query.Where(pstrFilterExpression); } 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 <Sigesoft.Node.WinClient.BE.ProtocolList> objData = query.ToList(); pobjOperationResult.Success = 1; return(objData); } catch (Exception ex) { pobjOperationResult.Success = 0; pobjOperationResult.ExceptionMessage = Common.Utils.ExceptionFormatter(ex); return(null); } }
public List <Sigesoft.Node.WinClient.BE.ProtocolComponentList> GetProtocolComponents(ref OperationResult pobjOperationResult, string pstrProtocolId) { //mon.IsActive = true; try { SigesoftEntitiesModel dbContext = new SigesoftEntitiesModel(); var objEntity = (from a in dbContext.protocolcomponent join b in dbContext.component on a.v_ComponentId equals b.v_ComponentId join fff in dbContext.systemparameter on new { a = b.i_CategoryId.Value, b = 116 } // CATEGORIA DEL EXAMEN equals new { a = fff.i_ParameterId, b = fff.i_GroupId } into J5_join from fff in J5_join.DefaultIfEmpty() join E in dbContext.systemparameter on new { a = a.i_OperatorId.Value, b = 117 } equals new { a = E.i_ParameterId, b = E.i_GroupId } into J1_join from E in J1_join.DefaultIfEmpty() join H in dbContext.systemparameter on new { a = a.i_GenderId.Value, b = 130 } // Genero condicional equals new { a = H.i_ParameterId, b = H.i_GroupId } into J2_join from H in J2_join.DefaultIfEmpty() join I in dbContext.systemparameter on new { a = b.i_ComponentTypeId.Value, b = 126 } // Tipo componente equals new { a = I.i_ParameterId, b = I.i_GroupId } into J3_join from I in J3_join.DefaultIfEmpty() where a.v_ProtocolId == pstrProtocolId && a.i_IsDeleted == 0 select new Sigesoft.Node.WinClient.BE.ProtocolComponentList { v_ComponentId = a.v_ComponentId, v_ComponentName = b.v_Name, //v_ServiceComponentStatusName = K.v_Value1, v_ProtocolComponentId = a.v_ProtocolComponentId, r_Price = a.r_Price, v_Operator = E.v_Value1, i_Age = a.i_Age, v_Gender = H.v_Value1, i_IsConditionalIMC = a.i_IsConditionalIMC, r_Imc = a.r_Imc, v_IsConditional = a.i_IsConditionalId == 1 ? "Si" : "No", i_isAdditional = a.i_IsAdditional, v_ComponentTypeName = I.v_Value1, i_RecordStatus = (int)RecordStatus.Grabado, i_RecordType = (int)RecordType.NoTemporal, i_GenderId = a.i_GenderId, i_IsConditionalId = a.i_IsConditionalId, i_OperatorId = a.i_OperatorId, i_IsDeleted = a.i_IsDeleted, d_CreationDate = a.d_InsertDate, v_CategoryName = fff.v_Value1, i_CategoryId = b.i_CategoryId }).ToList(); pobjOperationResult.Success = 1; return(objEntity); } catch (Exception ex) { pobjOperationResult.Success = 0; pobjOperationResult.ExceptionMessage = Common.Utils.ExceptionFormatter(ex); return(null); } }
public List <ServiceComponentList> GetServiceComponentsReport(string pstrServiceId) { //mon.IsActive = true; int isDeleted = 0; try { DatabaseContext dbContext = new DatabaseContext(); #region serviceComponentFields var serviceComponentFields = (from A in dbContext.ServiceComponent join B in dbContext.ServiceComponentFields on A.v_ServiceComponentId equals B.v_ServiceComponentId join C in dbContext.ServiceComponentFieldValues on B.v_ServiceComponentFieldsId equals C.v_ServiceComponentFieldsId join cfs in dbContext.ComponentFields on B.v_ComponentFieldId equals cfs.v_ComponentFieldId join D in dbContext.ComponentField on B.v_ComponentFieldId equals D.v_ComponentFieldId join cm in dbContext.Component on cfs.v_ComponentId equals cm.v_ComponentId join dh in dbContext.DataHierarchy on new { a = 105, b = D.i_MeasurementUnitId.Value } equals new { a = dh.i_GroupId, b = dh.i_ItemId } into dh_join from dh in dh_join.DefaultIfEmpty() where (A.v_ServiceId == pstrServiceId) && //(cm.v_ComponentId == pstrComponentId) && (A.i_IsDeleted == isDeleted) && (B.i_IsDeleted == isDeleted) && (C.i_IsDeleted == isDeleted) select new ServiceComponentFieldsList { v_ServiceComponentFieldsId = B.v_ServiceComponentFieldsId, v_ComponentFieldsId = B.v_ComponentFieldId, v_ComponentFielName = D.v_TextLabel, v_Value1 = C.v_Value1 == "" ? null : C.v_Value1, i_GroupId = D.i_GroupId.Value, v_MeasurementUnitName = dh.v_Value1, v_ComponentId = cm.v_ComponentId, v_ServiceComponentId = A.v_ServiceComponentId }).ToList(); int rpta = 0; var _finalQuery = (from a in serviceComponentFields let value1 = int.TryParse(a.v_Value1, out rpta) join sp in dbContext.SystemParameter on new { a = a.i_GroupId, b = rpta } equals new { a = sp.i_GroupId, b = sp.i_ParameterId } into sp_join from sp in sp_join.DefaultIfEmpty() select new ServiceComponentFieldsList { v_ServiceComponentFieldsId = a.v_ServiceComponentFieldsId, v_ComponentFieldsId = a.v_ComponentFieldsId, v_ComponentFielName = a.v_ComponentFielName, i_GroupId = a.i_GroupId, v_Value1 = a.v_Value1, v_Value1Name = sp == null ? "" : sp.v_Value1, v_MeasurementUnitName = a.v_MeasurementUnitName, v_ComponentId = a.v_ComponentId, v_ConclusionAndDiagnostic = a.v_Value1 + " / " + GetServiceComponentDiagnosticsReport(pstrServiceId, a.v_ComponentId), v_ServiceComponentId = a.v_ServiceComponentId }).ToList(); #endregion var components = (from aaa in dbContext.ServiceComponent join bbb in dbContext.Component on aaa.v_ComponentId equals bbb.v_ComponentId join J1 in dbContext.SystemUser on new { i_InsertUserId = aaa.i_InsertUserId } 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 = aaa.i_UpdateUserId } equals new { i_UpdateUserId = J2.i_SystemUserId } into J2_join from J2 in J2_join.DefaultIfEmpty() join fff in dbContext.SystemParameter on new { a = bbb.i_CategoryId, b = 116 } // CATEGORIA DEL EXAMEN equals new { a = fff.i_ParameterId, b = fff.i_GroupId } into J5_join from fff in J5_join.DefaultIfEmpty() // Usuario Medico Evaluador / Medico Aprobador **************************** join me in dbContext.SystemUser on aaa.i_ApprovedUpdateUserId equals me.i_SystemUserId into me_join from me in me_join.DefaultIfEmpty() join pme in dbContext.Professional on me.v_PersonId equals pme.v_PersonId into pme_join from pme in pme_join.DefaultIfEmpty() //********************************************************************* where (aaa.v_ServiceId == pstrServiceId) && (bbb.i_ComponentTypeId == (int?)Enumeratores.ComponentType.Examen) && (aaa.i_IsDeleted == 0) && (aaa.i_IsRequiredId == (int?)Enumeratores.SiNo.Si) //orderby bbb.i_CategoryId, bbb.v_Name select new { v_ComponentId = bbb.v_ComponentId, v_ComponentName = bbb.v_Name, v_ServiceComponentId = aaa.v_ServiceComponentId, v_CreationUser = J1.v_UserName, v_UpdateUser = J2.v_UserName, d_CreationDate = aaa.d_InsertDate, d_UpdateDate = aaa.d_UpdateDate, i_IsDeleted = aaa.i_IsDeleted.Value, i_CategoryId = bbb.i_CategoryId, v_CategoryName = fff.v_Value1, i_ServiceComponentStatusId = aaa.i_ServiceComponentStatusId, DiagnosticRepository = (from dr in aaa.service.diagnosticrepository where (dr.v_ServiceId == pstrServiceId) && (dr.v_ComponentId == aaa.v_ComponentId && dr.i_IsDeleted == 0) select new DiagnosticRepositoryList { v_DiseasesId = dr.diseases.v_DiseasesId, v_DiseasesName = dr.diseases.v_Name }), FirmaMedico = pme.b_SignatureImage }).AsEnumerable().Select(p => new ServiceComponentList { v_ComponentId = p.v_ComponentId, v_ComponentName = p.v_ComponentName, v_ServiceComponentId = p.v_ServiceComponentId, v_CreationUser = p.v_CreationUser, v_UpdateUser = p.v_UpdateUser, d_CreationDate = p.d_CreationDate, d_UpdateDate = p.d_UpdateDate, i_IsDeleted = p.i_IsDeleted, i_CategoryId = p.i_CategoryId, v_CategoryName = p.v_CategoryName, i_ServiceComponentStatusId = p.i_ServiceComponentStatusId, DiagnosticRepository = p.DiagnosticRepository.ToList(), FirmaMedico = p.FirmaMedico }).ToList(); components.Sort((x, y) => x.v_ComponentId.CompareTo(y.v_ComponentId)); components.ForEach(a => a.ServiceComponentFields = _finalQuery.FindAll(p => p.v_ComponentId == a.v_ComponentId)); return(components); } catch (Exception) { throw; } }
public static ProtocolList GetProtocolById(string pstrProtocolId) { try { DatabaseContext dbContext = new DatabaseContext(); var objProtocol = (from A in dbContext.Protocol join B in dbContext.Organization on A.v_EmployerOrganizationId equals B.v_OrganizationId join C in dbContext.Location on A.v_EmployerLocationId equals C.v_LocationId join D in dbContext.GroupOccupation on A.v_GroupOccupationId equals D.v_GroupOccupationId join E in dbContext.SystemParameter on new { a = A.i_EsoTypeId.Value, b = 118 } equals new { a = E.i_ParameterId, b = E.i_GroupId } join F in dbContext.Organization on A.v_CustomerOrganizationId equals F.v_OrganizationId join H in dbContext.SystemParameter on new { a = A.i_MasterServiceId.Value, b = 119 } equals new { a = H.i_ParameterId, b = H.i_GroupId } join I in dbContext.Location on A.v_CustomerLocationId equals I.v_LocationId join J in dbContext.Location on A.v_EmployerLocationId equals J.v_LocationId 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() join J3 in dbContext.Organization on new { a = A.v_WorkingOrganizationId } equals new { a = J3.v_OrganizationId } into J3_join from J3 in J3_join.DefaultIfEmpty() join J4 in dbContext.Area on new { a = C.v_LocationId } equals new { a = J4.v_LocationId } into J4_join from J4 in J4_join.DefaultIfEmpty() join J5 in dbContext.Ges on new { a = J4.v_AreaId } equals new { a = J5.v_AreaId } into J5_join from J5 in J5_join.DefaultIfEmpty() join J6 in dbContext.Occupation on new { a = J5.v_GesId, b = D.v_GroupOccupationId } equals new { a = J6.v_GesId, b = J6.v_GroupOccupationId } into J6_join from J6 in J6_join.DefaultIfEmpty() //join J7 in dbContext.groupoccupation on new { a = J4.v_AreaId } // equals new { a = J7.v_AreaId } into J7_join //from J5 in J5_join.DefaultIfEmpty() where (A.v_ProtocolId == pstrProtocolId) && (A.i_IsDeleted == 0) select new ProtocolList { v_Name = A.v_Name, v_ProtocolId = A.v_ProtocolId, //id Protocolo v_Protocol = A.v_Name, // monbre protocolo v_Organization = B.v_Name + " / " + J.v_Name, // nombre organizacion v_Location = C.v_Name, // nombre de sede v_EsoType = E.v_Value1, // Esoa, Esor v_OrganizationInvoice = F.v_Name, // empresa que factura v_CostCenter = A.v_CostCenter, // centro de costo v_IntermediaryOrganization = J3.v_Name + " / " + I.v_Name, // empresa intermediaria v_MasterServiceName = H.v_Value1, // Eso o no Eo v_OrganizationId = B.v_OrganizationId, i_EsoTypeId = E.i_ParameterId, // Id de (Esoa, Esor, Espo) v_WorkingOrganizationId = J3.v_OrganizationId, v_OrganizationInvoiceId = F.v_OrganizationId, v_GroupOccupationId = D.v_GroupOccupationId, v_Ges = J5.v_Name, v_GroupOccupation = D.v_Name, // nombre GESO v_Occupation = J6.v_Name, i_ServiceTypeId = A.i_MasterServiceTypeId.Value, i_MasterServiceId = A.i_MasterServiceId.Value, v_CreationUser = J1.v_UserName, v_UpdateUser = J2.v_UserName, d_CreationDate = A.d_InsertDate, d_UpdateDate = A.d_UpdateDate, v_ContacName = F.v_ContacName, v_Address = F.v_Address, v_CustomerOrganizationId = A.v_CustomerOrganizationId }).FirstOrDefault(); ProtocolList objData = objProtocol; return(objData); } catch (Exception ex) { return(null); } }
public static BoardProtocol GetAllProtocols(BoardProtocol data) { try { string WorkingOrganization = data.EmpresaTrabajo == "-1" ? "-1" : data.EmpresaTrabajo.Split('|')[0]; string EmployerOrganization = data.EmpresaEmpleadora == "-1" ? "-1" : data.EmpresaEmpleadora.Split('|')[0]; string CustomerOrganization = data.EmpresaCliente == "-1" ? "-1" : data.EmpresaCliente.Split('|')[0]; DatabaseContext dbContext = new DatabaseContext(); int skip = (data.Index - 1) * data.Take; var query = from A in dbContext.Protocol join B in dbContext.Organization on A.v_EmployerOrganizationId equals B.v_OrganizationId join C in dbContext.Location on A.v_EmployerLocationId equals C.v_LocationId join D in dbContext.GroupOccupation on A.v_GroupOccupationId equals D.v_GroupOccupationId join E in dbContext.SystemParameter on new { a = A.i_EsoTypeId.Value, b = 118 } equals new { a = E.i_ParameterId, b = E.i_GroupId } into J3_join from E in J3_join.DefaultIfEmpty() join F in dbContext.Organization on A.v_CustomerOrganizationId equals F.v_OrganizationId join I in dbContext.Location on A.v_CustomerLocationId equals I.v_LocationId join G in dbContext.Organization on A.v_WorkingOrganizationId equals G.v_OrganizationId into J4_join from G in J4_join.DefaultIfEmpty() join J in dbContext.Location on A.v_WorkingLocationId equals J.v_LocationId into J6_join from J in J6_join.DefaultIfEmpty() join H in dbContext.SystemParameter on new { a = A.i_MasterServiceId.Value, b = 119 } equals new { a = H.i_ParameterId, b = H.i_GroupId } into J5_join from H 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() join L in dbContext.ProtocolComponent on A.v_ProtocolId equals L.v_ProtocolId join K in dbContext.Component on L.v_ComponentId equals K.v_ComponentId where A.i_IsDeleted == 0 && (A.v_Name.Contains(data.ProtocolName) || data.ProtocolName == null) && (A.i_MasterServiceId == data.MasterService || data.MasterService == -1) && (B.v_OrganizationId == EmployerOrganization || EmployerOrganization == "-1") && (F.v_OrganizationId == CustomerOrganization || CustomerOrganization == "-1") && (G.v_OrganizationId == WorkingOrganization || WorkingOrganization == "-1") && (A.i_MasterServiceTypeId == data.MasterServiceType || data.MasterServiceType == -1) && (D.v_GroupOccupationId == data.GESO || data.GESO == "-1") && (A.i_EsoTypeId == data.EsoType || data.EsoType == -1) && (K.v_Name.Contains(data.ComponentName) || data.ComponentName == null) && A.i_IsActive == data.IsActive select new ProtocolList { v_ProtocolId = A.v_ProtocolId, v_Protocol = A.v_Name, v_Organization = B.v_Name + " / " + C.v_Name, v_Location = C.v_Name, v_EsoType = E.v_Value1, v_GroupOccupation = D.v_Name, v_OrganizationInvoice = F.v_Name + " / " + I.v_Name, v_CostCenter = A.v_CostCenter, v_IntermediaryOrganization = G.v_Name + " / " + J.v_Name, i_ServiceTypeId = A.i_MasterServiceTypeId.Value, v_MasterServiceName = H.v_Value1, i_MasterServiceId = A.i_MasterServiceId.Value, v_OrganizationId = B.v_OrganizationId + "|" + C.v_LocationId, i_EsoTypeId = A.i_EsoTypeId, v_WorkingOrganizationId = G.v_OrganizationId + "|" + J.v_LocationId, v_OrganizationInvoiceId = F.v_OrganizationId + "|" + I.v_LocationId, v_GroupOccupationId = D.v_GroupOccupationId, v_Geso = D.v_Name, v_CreationUser = J1.v_UserName, v_UpdateUser = J2.v_UserName, d_CreationDate = A.d_InsertDate, d_UpdateDate = A.d_UpdateDate, v_LocationId = A.v_EmployerLocationId, v_CustomerLocationId = A.v_CustomerLocationId, v_WorkingLocationId = A.v_WorkingLocationId, i_IsActive = A.i_IsActive, v_ComponenteNombre = K.v_Name, r_PriceFactor = A.r_PriceFactor, r_HospitalBedPrice = A.r_HospitalBedPrice, r_DiscountExam = A.r_DiscountExam, r_MedicineDiscount = A.r_MedicineDiscount, AseguradoraId = A.v_AseguradoraOrganizationId }; var list = query.ToList(); list = list.GroupBy(x => x.v_ProtocolId).Select(z => z.First()).OrderByDescending(x => x.v_ProtocolId).ToList(); int totalRecords = list.Count; if (data.Take > 0) { list = list.Skip(skip).Take(data.Take).ToList(); } data.List = list; data.TotalRecords = totalRecords; return(data); } catch (Exception ex) { return(null); } }
public BoardProductsSAMBHS GetProductsSAMBHS_PV(BoardProductsSAMBHS data) { try { DatabaseSAMBHSContext cnx = new DatabaseSAMBHSContext(); var RucEmpresa = data.RucEmpresa == null ? "" : data.RucEmpresa; var fltValue = data.ProductName == null ? "" : data.ProductName; var ListaSaldosIniciales = new List <CostoNotasIngresoPedido>(); data.IdAlmacen = int.Parse(System.Configuration.ConfigurationManager.AppSettings["appAlmacenPredeterminado"]); var periodo = DateTime.Now.Year.ToString(); if (RucEmpresa == Constants.RucWortec) { ListaSaldosIniciales = SaldosIniciales(periodo); } IQueryable query; if (data.ConStock) { #region Query Con Stock query = (from n in cnx.Producto join D in cnx.ProductoDetalle on n.v_IdProducto equals D.v_IdProducto into D_join from D in D_join.DefaultIfEmpty() join J3 in cnx.ProductoAlmacen on new { a = D.v_IdProductoDetalle, b = data.IdAlmacen, eliminado = 0, _periodo = periodo } equals new { a = J3.v_ProductoDetalleId, b = J3.i_IdAlmacen, eliminado = J3.i_Eliminado.Value, _periodo = J3.v_Periodo } into J3_join from J3 in J3_join.DefaultIfEmpty() join J4 in cnx.DataHierarchy on new { a = n.i_IdUnidadMedida.Value, b = 17 } equals new { a = J4.i_ItemId.Value, b = J4.i_GroupId } into J4_join from J4 in J4_join.DefaultIfEmpty() join J5 in cnx.Linea on n.v_IdLinea equals J5.v_IdLinea into J5_join from J5 in J5_join.DefaultIfEmpty() join J6 in cnx.DataHierarchy on new { a = n.i_IdPerfilDetraccion.Value, b = 176 } equals new { a = J6.i_ItemId.Value, b = J6.i_GroupId } into J6_join from J6 in J6_join.DefaultIfEmpty() where (n.v_Descripcion.Contains(fltValue) || n.v_CodInterno.Contains(fltValue) || fltValue == "") && n.i_Eliminado == 0 && n.i_EsServicio == 0 && n.i_EsActivoFijo == 0 && n.i_EsActivo == 1 && J3.d_StockActual > 0 && J3_join.Any(p => p.v_ProductoDetalleId == D.v_IdProductoDetalle) // <---linea clave select new { n.v_IdProducto, D.v_IdProductoDetalle, n.v_Descripcion, n.v_CodInterno, n.i_EsServicio, n.i_EsLote, n.d_PrecioVenta, n.i_IdTipoProducto, stockActual = J3.d_StockActual, J3.v_IdProductoAlmacen, d_separacion = J3.d_SeparacionTotal ?? 0, n.i_IdUnidadMedida, EmpaqueUnidadMedida = J4.v_Value1, n.d_Empaque, n.i_EsAfectoDetraccion, TasaDetraccion = J6 != null ? J6.v_Value2 : "0", TopeDetraccion = J6 != null ? J6.v_Field : "0", n.i_NombreEditable, StockDisponible = J3.d_StockActual - J3.d_SeparacionTotal, n.i_ValidarStock, n.i_EsAfectoPercepcion, n.d_TasaPercepcion, n.i_PrecioEditable, ProductoAlmacen = J3.v_IdProductoAlmacen, NroCuentaVenta = J5.v_NroCuentaVenta, NroCuentaCompra = J5.v_NroCuentaCompra, J3.i_IdAlmacen, v_NroPedidoExportacion = J3.v_NroPedido, ValorUM = string.IsNullOrEmpty(J4.v_Value2) ? "0" : J4.v_Value2, UM = "UNIDADES", Observaciones = n.v_Caracteristica, AfectoIsc = n.i_EsAfectoIsc, StockMinimo = n.d_StockMinimo == null ? 1 : J3 == null || J3.d_StockActual == null ? 1 : J3.d_StockActual <= n.d_StockMinimo ? 1 : 0, n.v_Descripcion2, i_SolicitarNroLoteIngreso = n.i_SolicitarNroLoteIngreso ?? 0, i_SolicitarNroSerieIngreso = n.i_SolicitarNroSerieIngreso ?? 0, i_SolicitaOrdenProduccionIngreso = n.i_SolicitaOrdenProduccionIngreso ?? 0, i_SolicitarNroSerieSalida = n.i_SolicitarNroSerieSalida ?? 0, i_SolicitarNroLoteSalida = n.i_SolicitarNroLoteSalida ?? 0, i_SolicitaOrdenProduccionSalida = n.i_SolicitaOrdenProduccionSalida ?? 0, J3.v_NroLote, J3.v_NroSerie, t_FechaCaducidad = J3.t_FechaCaducidad == null ? Fechanull : J3.t_FechaCaducidad, }).ToList().Select(p => { var PrecioWortec = Constants.RucWortec == RucEmpresa ? ListaSaldosIniciales.Where( a => a.v_IdProductoDetalle == p.v_IdProductoDetalle && a.NroPedido == p.v_NroPedidoExportacion).ToList() : null; return(new ProductCustomSAMBHS { TasaDetraccion = p.TasaDetraccion, TopeDetraccion = p.TopeDetraccion, v_IdProducto = p.v_IdProducto, v_IdProductoDetalle = p.v_IdProductoDetalle, v_Descripcion = p.v_Descripcion, v_CodInterno = p.v_CodInterno, i_EsServicio = p.i_EsServicio, i_EsLote = p.i_EsLote, i_IdTipoProducto = p.i_IdTipoProducto, stockActual = p.stockActual, v_IdProductoAlmacen = p.v_IdProductoAlmacen, d_separacion = p.d_separacion, i_IdUnidadMedida = p.i_IdUnidadMedida, EmpaqueUnidadMedida = p.EmpaqueUnidadMedida, d_Empaque = p.d_Empaque, i_EsAfectoDetraccion = p.i_EsAfectoDetraccion, i_NombreEditable = p.i_NombreEditable, StockDisponible = p.StockDisponible, i_ValidarStock = p.i_ValidarStock, i_EsAfectoPercepcion = p.i_EsAfectoPercepcion, d_TasaPercepcion = p.d_TasaPercepcion, i_PrecioEditable = p.i_PrecioEditable, d_Precio = Constants.RucWortec == RucEmpresa ? (PrecioWortec.Any() ? DevuelveValorRedondeado(PrecioWortec.Average(x => x.Costo), 4) : 0) : p.d_PrecioVenta, d_Descuento = 0, IdMoneda = 1, NroCuentaVenta = p.NroCuentaVenta, NroCuentaCompra = p.NroCuentaCompra, IdAlmacen = p.i_IdAlmacen, v_NroPedidoExportacion = p.v_NroPedidoExportacion, StockActualUM = p.stockActual / int.Parse(p.ValorUM), SeparacionActualUM = p.d_separacion / int.Parse(p.ValorUM), SaldoUM = (p.stockActual / int.Parse(p.ValorUM)) - p.d_separacion / int.Parse(p.ValorUM), UM = p.UM, Observacion = p.Observaciones.Trim(), EsAfectoIsc = p.AfectoIsc.HasValue && p.AfectoIsc == 1, StockMinimo = p.StockMinimo, v_Descripcion2 = p.v_Descripcion2, i_SolicitarNroLoteIngreso = p.i_SolicitarNroLoteIngreso, i_SolicitarNroSerieIngreso = p.i_SolicitarNroSerieIngreso, i_SolicitaOrdenProduccionIngreso = p.i_SolicitaOrdenProduccionIngreso, i_SolicitarNroSerieSalida = p.i_SolicitarNroSerieSalida, i_SolicitarNroLoteSalida = p.i_SolicitarNroLoteSalida, i_SolicitaOrdenProduccionSalida = p.i_SolicitaOrdenProduccionSalida, v_NroLote = p.v_NroLote, v_NroSerie = p.v_NroSerie, t_FechaCaducidad = p.t_FechaCaducidad.Value, }); }).AsQueryable(); int skip = (data.Index - 1) * data.Take; var FinalList = query.Cast <ProductCustomSAMBHS>().ToList().OrderBy(o => o.t_FechaCaducidad).ToList(); var ListProducts = FinalList.GroupBy(g => g.v_IdProducto).Select(s => s.First()).ToList(); data.TotalRecords = ListProducts.Count; if (data.Take > 0) { ListProducts = ListProducts.Skip(skip).Take(data.Take).ToList(); } data.List = ListProducts; #endregion } else { #region Query Sin Importar Stock query = (from n in cnx.Producto join D in cnx.ProductoDetalle on n.v_IdProducto equals D.v_IdProducto into D_join from D in D_join.DefaultIfEmpty() join J4 in cnx.DataHierarchy on new { a = n.i_IdUnidadMedida, b = 17 } equals new { a = J4.i_ItemId, b = J4.i_GroupId } into J4_join from J4 in J4_join.DefaultIfEmpty() join J5 in cnx.Linea on n.v_IdLinea equals J5.v_IdLinea into J5_join from J5 in J5_join.DefaultIfEmpty() join J6 in cnx.ProductoAlmacen on new { a = D.v_IdProductoDetalle, b = data.IdAlmacen, eliminado = 0, _periodo = periodo } equals new { a = J6.v_ProductoDetalleId, b = J6.i_IdAlmacen, eliminado = J6.i_Eliminado.Value, _periodo = J6.v_Periodo } into J6_join from J6 in J6_join.DefaultIfEmpty() join J7 in cnx.DataHierarchy on new { a = n.i_IdPerfilDetraccion, b = 176 } equals new { a = J7.i_ItemId, b = J7.i_GroupId } into J7_join from J7 in J7_join.DefaultIfEmpty() where n.i_Eliminado == 0 && n.i_EsServicio == 0 && n.i_EsActivoFijo == 0 && n.i_EsActivo == 1 && D_join.Any(p => p.v_IdProductoDetalle.Equals(J6.v_ProductoDetalleId)) select new { v_IdProducto = n.v_IdProducto, v_IdProductoDetalle = D.v_IdProductoDetalle, v_Descripcion = n.v_Descripcion, v_CodInterno = n.v_CodInterno, i_EsServicio = n.i_EsServicio, i_EsLote = n.i_EsLote, n.d_PrecioVenta, i_IdTipoProducto = n.i_IdTipoProducto, i_IdUnidadMedida = n.i_IdUnidadMedida, EmpaqueUnidadMedida = J4.v_Value1, d_Empaque = n.d_Empaque, i_EsAfectoDetraccion = n.i_EsAfectoDetraccion, i_NombreEditable = n.i_NombreEditable, i_ValidarStock = n.i_ValidarStock, i_EsAfectoPercepcion = n.i_EsAfectoPercepcion, d_TasaPercepcion = n.d_TasaPercepcion, i_PrecioEditable = n.i_PrecioEditable, NroCuentaVenta = J5.v_NroCuentaVenta, NroCuentaCompra = J5.v_NroCuentaCompra, NroPedido = J6.v_NroPedido, stockActual = J6.d_StockActual, v_IdProductoAlmacen = J6.v_IdProductoAlmacen, d_Separacion = J6.d_SeparacionTotal == null ? 0 : J6.d_SeparacionTotal, d_StockActual = J6.d_StockActual, i_IdAlmacen = J6.i_IdAlmacen, v_NroPedidoExportacion = J6.v_NroPedido, ValorUM = string.IsNullOrEmpty(J4.v_Value2) ? "0" : J4.v_Value2, UM = "UNIDADES", AfectoIsc = n.i_EsAfectoIsc, StockMinimo = n.d_StockMinimo == null ? 1 : J6 == null || J6.d_StockActual == null ? 1 : J6.d_StockActual <= n.d_StockMinimo ? 1 : 0, v_Descripcion2 = n.v_Descripcion2, TasaDetraccion = J7 != null ? J7.v_Value2 : "0", TopeDetraccion = J7 != null ? J7.v_Field : "0", i_SolicitarNroLoteIngreso = n.i_SolicitarNroLoteIngreso ?? 0, i_SolicitarNroSerieIngreso = n.i_SolicitarNroSerieIngreso ?? 0, i_SolicitaOrdenProduccionIngreso = n.i_SolicitaOrdenProduccionIngreso ?? 0, i_SolicitarNroSerieSalida = n.i_SolicitarNroSerieSalida ?? 0, i_SolicitarNroLoteSalida = n.i_SolicitarNroLoteSalida ?? 0, i_SolicitaOrdenProduccionSalida = n.i_SolicitaOrdenProduccionSalida ?? 0, v_NroLote = J6.v_NroLote, v_NroSerie = J6.v_NroSerie, t_FechaCaducidad = J6.t_FechaCaducidad == null ? Fechanull : J6.t_FechaCaducidad.Value, }).ToList().Select(p => { decimal?PrecioWortec = Constants.RucWortec == RucEmpresa ? ListaSaldosIniciales.Where( a => a.v_IdProductoDetalle == p.v_IdProductoDetalle && a.NroPedido == p.v_NroPedidoExportacion).Sum(a => a.Costo) : 0; return(new ProductCustomSAMBHS { v_IdProducto = p.v_IdProducto, v_IdProductoDetalle = p.v_IdProductoDetalle, v_Descripcion = p.v_Descripcion, v_CodInterno = p.v_CodInterno, i_EsServicio = p.i_EsServicio, i_EsLote = p.i_EsLote, i_IdTipoProducto = p.i_IdTipoProducto, stockActual = p.v_IdProductoAlmacen != null ? p.stockActual ?? 0 : 0, v_IdProductoAlmacen = p.v_IdProductoAlmacen, d_separacion = p.v_IdProductoAlmacen != null ? p.d_Separacion ?? 0 : 0, i_IdUnidadMedida = p.i_IdUnidadMedida, EmpaqueUnidadMedida = p.EmpaqueUnidadMedida, d_Empaque = p.d_Empaque, i_EsAfectoDetraccion = p.i_EsAfectoDetraccion, i_NombreEditable = p.i_NombreEditable, StockDisponible = p.d_StockActual, i_ValidarStock = p.i_ValidarStock, i_EsAfectoPercepcion = p.i_EsAfectoPercepcion, d_TasaPercepcion = p.d_TasaPercepcion, i_PrecioEditable = p.i_PrecioEditable, d_Precio = p.v_IdProductoAlmacen != null ? RucEmpresa == Constants.RucWortec ? PrecioWortec != null ? PrecioWortec : 0 : p.d_PrecioVenta : 0, d_Descuento = 0, IdMoneda = 1, NroCuentaVenta = p.NroCuentaVenta, NroCuentaCompra = p.NroCuentaCompra, IdAlmacen = p.v_IdProductoAlmacen != null ? -1 : p.i_IdAlmacen == 0 ? -1 : p.i_IdAlmacen, v_NroPedidoExportacion = p.NroPedido, StockActualUM = p.stockActual == 0 || p.ValorUM == "0" ? 0 : p.stockActual / int.Parse(p.ValorUM), SeparacionActualUM = p.d_Separacion == 0 || p.ValorUM == "0" ? 0 : p.d_Separacion / int.Parse(p.ValorUM), SaldoUM = p.ValorUM == "0" ? 0 : p.stockActual != 0 && p.ValorUM != "0" && p.d_Separacion != 0 ? (p.stockActual / int.Parse(p.ValorUM)) - p.d_Separacion / int.Parse(p.ValorUM) : p.stockActual == 0 && p.ValorUM != "0" && p.d_Separacion != 0 ? -p.d_Separacion / int.Parse(p.ValorUM) : p.d_Separacion == 0 && p.ValorUM != "0" && p.stockActual != 0 ? (p.stockActual / int.Parse(p.ValorUM)) : 0, UM = p.UM, EsAfectoIsc = p.AfectoIsc.HasValue && p.AfectoIsc == 1, StockMinimo = p.StockMinimo, v_Descripcion2 = p.v_Descripcion2, TasaDetraccion = p.TasaDetraccion, TopeDetraccion = p.TopeDetraccion, i_SolicitarNroLoteIngreso = p.i_SolicitarNroLoteIngreso, i_SolicitarNroSerieIngreso = p.i_SolicitarNroSerieIngreso, i_SolicitaOrdenProduccionIngreso = p.i_SolicitaOrdenProduccionIngreso, i_SolicitarNroSerieSalida = p.i_SolicitarNroSerieSalida, i_SolicitarNroLoteSalida = p.i_SolicitarNroLoteSalida, i_SolicitaOrdenProduccionSalida = p.i_SolicitaOrdenProduccionSalida, v_NroSerie = p.v_NroSerie, v_NroLote = p.v_NroLote, t_FechaCaducidad = p.t_FechaCaducidad, }); }).AsQueryable(); int skip = (data.Index - 1) * data.Take; var FinalList = query.Cast <ProductCustomSAMBHS>().ToList().OrderBy(o => o.t_FechaCaducidad).ToList(); var ListProducts = FinalList.GroupBy(g => g.v_IdProducto).Select(s => s.First()).ToList(); data.TotalRecords = ListProducts.Count; if (data.Take > 0) { ListProducts = ListProducts.Skip(skip).Take(data.Take).ToList(); } data.List = ListProducts; #endregion } return(data); } catch (Exception ex) { return(null); } }