Пример #1
0
        public List <DataHierarchyList> GetDataHierarchiesPagedAndFiltered(ref OperationResult pobjOperationResult, int?pintPageIndex, int?pintResultsPerPage, string pstrSortExpression, string pstrFilterExpression, int pintGroupId)
        {
            //mon.IsActive = true;

            try
            {
                SigesoftEntitiesModel dbContext = new SigesoftEntitiesModel();

                var query = from A in dbContext.datahierarchy

                            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 J4 in dbContext.datahierarchy on new { ItemId = A.i_ParentItemId.Value, groupId = A.i_GroupId }
                equals new { ItemId = J4.i_ItemId, groupId = J4.i_GroupId } into J4_join
                from J4 in J4_join.DefaultIfEmpty()

                where A.i_GroupId == pintGroupId &&
                (A.i_IsDeleted == 0 || A.i_IsDeleted == null)

                select new DataHierarchyList
                {
                    i_GroupId        = A.i_GroupId,
                    i_ItemId         = A.i_ItemId,
                    v_Value1         = A.v_Value1,
                    i_ParentItemId   = A.i_ParentItemId.Value,
                    v_ParentItemName = J4.v_Value1,
                    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
                };


                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);
                    query = query.Take(pintResultsPerPage.Value);
                }

                List <DataHierarchyList> objData = query.ToList();
                pobjOperationResult.Success = 1;
                return(objData);
            }
            catch (Exception ex)
            {
                pobjOperationResult.Success          = 0;
                pobjOperationResult.ExceptionMessage = ex.Message;
                return(null);
            }
        }
Пример #2
0
        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);
            }
        }
Пример #3
0
        //Devart.Data.PostgreSql.PgSqlMonitor mon = new Devart.Data.PostgreSql.PgSqlMonitor();

        public List <WarehouseList> GetWarehousePagedAndFiltered(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.warehouse
                            join B in dbContext.location on A.v_LocationId equals B.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 J4 in dbContext.datahierarchy on new { ItemId = A.i_CostCenterId.Value, groupId = 110 }
                equals new { ItemId = J4.i_ItemId, groupId = J4.i_GroupId } into J4_join
                from J4 in J4_join.DefaultIfEmpty()

                where A.i_IsDeleted == 0
                select new WarehouseList
                {
                    v_OrganizationId  = A.v_OrganizationId,
                    v_WarehouseId     = A.v_WarehouseId,
                    v_LocationId      = A.v_LocationId,
                    v_LocationIdName  = B.v_Name,
                    i_CostCenterId    = A.i_CostCenterId.Value,
                    v_CenterCostoName = J4.v_Value1,
                    v_Name            = A.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
                };

                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 <WarehouseList> objData = query.ToList();
                pobjOperationResult.Success = 1;
                return(objData);
            }
            catch (Exception ex)
            {
                pobjOperationResult.Success          = 0;
                pobjOperationResult.ExceptionMessage = Common.Utils.ExceptionFormatter(ex);
                return(null);
            }
        }
Пример #4
0
        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);
            }
        }
Пример #5
0
        public BoardCliente GetClients(BoardCliente data)
        {
            try
            {
                string filtValue          = data.Name == "" ? null : data.Name;
                DatabaseSAMBHSContext cnx = new DatabaseSAMBHSContext();
                var query = (from A in cnx.Cliente
                             join J1 in cnx.SystemUser on new { i_InsertUserId = A.i_InsertaIdUsuario.Value }
                             equals new { i_InsertUserId = J1.i_SystemUserId } into J1_join
                             from J1 in J1_join.DefaultIfEmpty()

                             join J2 in cnx.SystemUser on new { i_UpdateUserId = A.i_ActualizaIdUsuario.Value }
                             equals new { i_UpdateUserId = J2.i_SystemUserId } into J2_join
                             from J2 in J2_join.DefaultIfEmpty()

                             join J3 in cnx.SystemParameter on new { a = A.i_IdTipoIdentificacion.Value, b = 150 }
                             equals new { a = J3.i_ParameterId, b = J3.i_GroupId } into J3_join
                             from J3 in J3_join.DefaultIfEmpty()

                             join J4 in cnx.ClienteDirecciones on new { IdDireccion = A.v_IdCliente, eliminado = 0, predeterminado = 1 }
                             equals new { IdDireccion = J4.v_IdCliente, eliminado = J4.i_Eliminado.Value, predeterminado = J4.i_EsDireccionPredeterminada.Value } into J4_join
                             from J4 in J4_join.DefaultIfEmpty()

                             where A.i_Eliminado == 0 && (A.v_RazonSocial.Contains(filtValue) || A.v_NroDocIdentificacion.Contains(filtValue) || filtValue == null)

                             select new ClienteCustom
                {
                    NombreRazonSocial = (A.v_ApePaterno + " " + A.v_ApeMaterno + " " + A.v_PrimerNombre + " " + A.v_SegundoNombre + " " + A.v_RazonSocial).Trim(),
                    v_IdCliente = A.v_IdCliente,
                    v_ApeMaterno = A.v_ApeMaterno,
                    v_ApePaterno = A.v_ApePaterno,
                    v_CodCliente = A.v_CodCliente,
                    i_IdLista = A.i_IdListaPrecios,
                    v_NroDocIdentificacion = A.v_NroDocIdentificacion,
                    v_PrimerNombre = A.v_PrimerNombre,
                    v_RazonSocial = (A.v_ApePaterno + " " + A.v_ApeMaterno + " " + A.v_PrimerNombre + " " + A.v_SegundoNombre + " " + A.v_RazonSocial).Trim(),
                    v_SegundoNombre = A.v_SegundoNombre,
                    i_IdTipoIdentificacion = A.i_IdTipoIdentificacion,
                    i_IdTipoPersona = A.i_IdTipoPersona,
                    t_ActualizaFecha = A.t_ActualizaFecha.Value,
                    t_InsertaFecha = A.t_InsertaFecha.Value,
                    v_UsuarioCreacion = J1.v_UserName,
                    v_UsuarioModificacion = J2.v_UserName,
                    TipoDocumento = J3.v_Value1,
                    v_FlagPantalla = A.v_FlagPantalla,
                    v_Direccion = J4 == null ? A.v_DirecPrincipal : J4.v_Direccion,
                    i_ParameterId = J3.i_ParameterId,
                    i_IdDireccionCliente = J4 == null ? -1 : J4.i_IdDireccionCliente
                }).ToList();

                int skip        = (data.Index - 1) * data.Take;
                var ListClients = query.GroupBy(g => g.v_IdCliente).Select(s => s.First()).ToList();
                data.TotalRecords = ListClients.Count;

                if (data.Take > 0)
                {
                    ListClients = ListClients.Skip(skip).Take(data.Take).ToList();
                }

                data.List = ListClients.OrderBy(x => x.v_NroDocIdentificacion).ToList();
                return(data);
            }
            catch (Exception ex)
            {
                return(null);
            }
        }
Пример #6
0
        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);
            }
        }
Пример #7
0
        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);
            }
        }
Пример #8
0
        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);
            }
        }
Пример #9
0
        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);
            }
        }