Exemplo n.º 1
0
        public List <ProjectMasterViewModel> GetAssignedProjects(int userId)
        {
            var _context    = new DatabaseContext();
            var queryResult = (from registration in _context.Registration
                               join A in _context.AssignedRoles on registration.RegistrationID equals A.RegistrationID into A_join
                               from A in A_join.DefaultIfEmpty()
                               join AA in _context.Registration on A.AssignToAdmin equals AA.RegistrationID into AA_join
                               from AA in AA_join.DefaultIfEmpty()
                               join _assignedProjects in _context.AssignedProjects on registration.RegistrationID equals _assignedProjects.ManagerId into RL_join
                               from _assignedProjects in RL_join.DefaultIfEmpty()
                               join projectmaster in _context.ProjectMaster on _assignedProjects.ProjectId equals projectmaster.ProjectID into D_join
                               from Proj in D_join.DefaultIfEmpty()
                               where registration.RegistrationID == userId
                               select new ProjectMasterViewModel()
            {
                ProjectID = Proj.ProjectID,
                ProjectCode = Proj.ProjectCode,
                ProjectName = Proj.ProjectName,
                FirstName = AA.FirstName,
                LastName = AA.LastName,
                MiddleName = AA.MiddleName,
                IsActive = Proj.IsActive
            }).ToList();

            return(queryResult);
        }
Exemplo n.º 2
0
        public void sr_llenar_resultados(ref DataGridView dgr, int IDS, DateTime dia)
        {
            animalitos db    = new animalitos(CN.CONEC);
            var        query = (from A in db.dbresultados
                                join B in db.dbSorteos on new { ID_LOTERIA = Convert.ToInt32(A.ID_LOTERIA) } equals new { ID_LOTERIA = B.ID_SORTEO } into B_join
                                from B in B_join.DefaultIfEmpty()
                                join C in db.dbhorarios
                                on new { A.ID_LOTERIA, ID_SORTEOHR = Convert.ToInt32(A.ID_SORTEOHR) }
                                equals new { ID_LOTERIA = C.IDSORTEOHR, ID_SORTEOHR = C.IDHORA } into C_join
                                from C in C_join.DefaultIfEmpty()
                                join D in db.dbanimalitos on new { ID_ANIMAL = Convert.ToInt32(A.ID_ANIMAL) } equals new { ID_ANIMAL = D.IDANIMALITOS } into D_join
                                from D in D_join.DefaultIfEmpty()
                                join E in db.dbestatus on new { ESTATUS = A.ESTATUS } equals new { ESTATUS = E.CODESTATUS } into E_join
                                from E in E_join.DefaultIfEmpty()
                                where
                                A.FECHA == dia &&
                                A.ID_LOTERIA == IDS
                                select new
            {
                A.ID_RESULTADO,
                NOMBRE_SORTEO = B.NOMBRE_SORTEO,
                HORA = C.HORA,
                NOMBRE_ANIMALITO = D.NOMBRE_ANIMALITO,
                NOMESTATUS = E.NOMESTATUS,
                A.FECHA,
                Column1 = A.HORA
            });

            dgr.DataSource = query;
        }
Exemplo n.º 3
0
        public List <Sigesoft.Node.WinClient.BE.FamilyMedicalAntecedentsList> GetFamilyMedicalAntecedentsReport(string pstrPersonId)
        {
            //mon.IsActive = true;
            try
            {
                SigesoftEntitiesModel dbContext = new SigesoftEntitiesModel();
                var query = (from A in dbContext.familymedicalantecedents
                             join B in dbContext.systemparameter on new { a = A.i_TypeFamilyId.Value, b = 149 }
                             equals new { a = B.i_ParameterId, b = B.i_GroupId } into B_join
                             from B in B_join.DefaultIfEmpty()
                             join C in dbContext.systemparameter on new { a = B.i_ParentParameterId.Value, b = 149 }
                             equals new { a = C.i_ParameterId, b = C.i_GroupId } into C_join
                             from C in C_join.DefaultIfEmpty()
                             join D in dbContext.diseases on new { a = A.v_DiseasesId }
                             equals new { a = D.v_DiseasesId } into D_join
                             from D in D_join.DefaultIfEmpty()

                             where A.i_IsDeleted == 0 && A.v_PersonId == pstrPersonId

                             select new Sigesoft.Node.WinClient.BE.FamilyMedicalAntecedentsList
                {
                    v_FamilyMedicalAntecedentsId = A.v_FamilyMedicalAntecedentsId,
                    v_PersonId = A.v_PersonId,
                    v_DiseasesId = A.v_DiseasesId,
                    v_DiseaseName = D.v_Name,
                    //i_TypeFamilyId = A.i_TypeFamilyId.Value,
                    i_TypeFamilyId = C.i_ParameterId,
                    v_TypeFamilyName = C.v_Value1,
                    v_Comment = A.v_Comment,
                    v_FullAntecedentName = D.v_Name + " / " + C.v_Value1 + ", " + A.v_Comment,
                    DxAndComment = D.v_Name + "," + A.v_Comment
                }).ToList();

                // add the sequence number on the fly
                var query1 = query.Select((x, index) => new Sigesoft.Node.WinClient.BE.FamilyMedicalAntecedentsList
                {
                    i_Item = index + 1,
                    v_FamilyMedicalAntecedentsId = x.v_FamilyMedicalAntecedentsId,
                    v_PersonId           = x.v_PersonId,
                    v_DiseasesId         = x.v_DiseasesId,
                    v_DiseaseName        = x.v_DiseaseName,
                    i_TypeFamilyId       = x.i_TypeFamilyId,
                    v_TypeFamilyName     = x.v_TypeFamilyName,
                    v_Comment            = x.v_Comment,
                    v_FullAntecedentName = x.v_FullAntecedentName,
                    DxAndComment         = x.DxAndComment
                }).ToList();

                return(query1);
            }
            catch (Exception ex)
            {
                return(null);
            }
        }
Exemplo n.º 4
0
        public LogList GetLog(ref OperationResult pobjOperationResult, string pintLogId)
        {
            //mon.IsActive = true;
            try
            {
                SigesoftEntitiesModel dbContext = new SigesoftEntitiesModel();

                var query = (from A in dbContext.log
                             join B in dbContext.node on A.i_NodeLogId equals B.i_NodeId
                             join C in dbContext.organization on A.v_OrganizationId equals C.v_OrganizationId into C_join
                             from C in C_join.DefaultIfEmpty()
                             join D in dbContext.systemuser on A.i_SystemUserId equals D.i_SystemUserId into D_join
                             from D in D_join.DefaultIfEmpty()
                             join J1 in dbContext.systemparameter on new { a = 102, b = A.i_EventTypeId.Value }
                             equals new { a = J1.i_GroupId, b = J1.i_ParameterId }
                             join J2 in dbContext.systemparameter on new { a = 111, b = A.i_Success.Value }
                             equals new { a = J2.i_GroupId, b = J2.i_ParameterId }
                             where A.v_LogId == pintLogId
                             select new LogList
                {
                    v_LogId = A.v_LogId,
                    i_EventTypeId = A.i_EventTypeId.Value,
                    v_EventTypeName = J1.v_Value1,
                    i_NodeId = A.i_NodeLogId.Value,
                    v_NodeName = B.v_Description,
                    v_OrganizationId = A.v_OrganizationId,
                    v_OrganizationName = C.v_Name,
                    i_SystemUserId = A.i_SystemUserId.Value,
                    v_SystemUserName = D.v_UserName,
                    d_Date = A.d_Date.Value,
                    v_ProcessEntity = A.v_ProcessEntity,
                    v_ElementItem = A.v_ElementItem,
                    i_Success = A.i_Success.Value,
                    v_SuccessName = J2.v_Value1,
                    v_ErrorException = A.v_ErrorException
                }).FirstOrDefault();


                return(query);
            }
            catch (Exception ex)
            {
                pobjOperationResult.Success          = 0;
                pobjOperationResult.ExceptionMessage = ex.Message;
                return(null);
            }
        }
        private string ConcatenateFamilyMedicalAntecedents(string pstrPersonId, int pintTypeFamilyId)
        {
            var qry = (from A in ctx.FamilyMedicalAntecedents
                       join B in ctx.SystemParameter on new { a = A.i_TypeFamilyId.Value, b = 149 }  // ANTECEDENTES FAMILIARES MÉDICOS
                       equals new { a = B.i_ParameterId, b = B.i_GroupId } into B_join
                       from B in B_join.DefaultIfEmpty()
                       join C in ctx.SystemParameter on new { a = B.i_ParentParameterId.Value, b = 149 }  // [PADRE,MADRE,HERMANOS]
                       equals new { a = C.i_ParameterId, b = C.i_GroupId } into C_join
                       from C in C_join.DefaultIfEmpty()
                       join D in ctx.Diseases on new { a = A.v_DiseasesId }
                       equals new { a = D.v_DiseasesId } into D_join
                       from D in D_join.DefaultIfEmpty()
                       where A.v_PersonId == pstrPersonId &&
                       A.i_IsDeleted == 0 && C.i_ParameterId == pintTypeFamilyId
                       select new
            {
                v_DiseasesName = D.v_Name
            }).ToList();

            return(string.Join(", ", qry.Select(p => p.v_DiseasesName)));
        }
Exemplo n.º 6
0
        //Devart.Data.PostgreSql.PgSqlMonitor mon = new Devart.Data.PostgreSql.PgSqlMonitor();

        public List <MedicalExamFieldValuesList> GetMedicalExamFieldValuesPagedAndFiltered(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.componentfieldvalues
                            join B in dbContext.systemparameter on new { a = A.i_OperatorId.Value, b = 129 } equals new { a = B.i_ParameterId, b = B.i_GroupId }
                join C in dbContext.systemparameter on new { a = A.i_IsAnormal.Value, b = 111 } equals new { a = C.i_ParameterId, b = C.i_GroupId }
                //join D in dbContext.diseases on new { A.v_Diseases equals D.v_DiseasesId }

                join D in dbContext.diseases on new { a = A.v_Diseases }
                equals new { a = D.v_DiseasesId } into D_join
                from D in D_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 MedicalExamFieldValuesList
                {
                    v_MedicalExamFieldValuesId = A.v_ComponentFieldValuesId,
                    v_MedicalExamFieldsId      = A.v_ComponentFieldId,
                    v_AnalyzingValue1          = A.v_AnalyzingValue1,
                    v_AnalyzingValue2          = A.v_AnalyzingValue2,
                    i_OperatorId    = A.i_OperatorId.Value,
                    v_OperatorName  = B.v_Value1,
                    v_IsAnormal     = C.v_Value1,
                    v_Cie10Name     = D.v_Name + " / " + D.v_CIE10Id,
                    v_LegalStandard = A.v_LegalStandard,
                    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.Value
                };

                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 <MedicalExamFieldValuesList> objData = query.ToList();
                pobjOperationResult.Success = 1;
                return(objData);
            }
            catch (Exception ex)
            {
                pobjOperationResult.Success          = 0;
                pobjOperationResult.ExceptionMessage = Common.Utils.ExceptionFormatter(ex);
                return(null);
            }
        }
Exemplo n.º 7
0
        //Devart.Data.PostgreSql.PgSqlMonitor mon = new Devart.Data.PostgreSql.PgSqlMonitor();

        public List <LogList> GetLogsPagedAndFiltered(ref OperationResult pobjOperationResult, int?pintPageIndex, int?pintResultsPerPage, string pstrSortExpression, string pstrFilterExpression, DateTime?pdatBeginDate, DateTime?pdatEndDate)
        {
            //mon.IsActive = true;

            try
            {
                SigesoftEntitiesModel dbContext = new SigesoftEntitiesModel();
                var query = from A in dbContext.log
                            join B in dbContext.node on A.i_NodeLogId equals B.i_NodeId
                            join C in dbContext.organization on A.v_OrganizationId equals C.v_OrganizationId into C_join
                            from C in C_join.DefaultIfEmpty()

                            join D in dbContext.systemuser on A.i_SystemUserId equals D.i_SystemUserId into D_join
                            from D in D_join.DefaultIfEmpty()

                            join J1 in dbContext.systemparameter on new { a = 102, b = A.i_EventTypeId.Value }
                equals new { a = J1.i_GroupId, b = J1.i_ParameterId }
                join J2 in dbContext.systemparameter on new { a = 111, b = A.i_Success.Value }
                equals new { a = J2.i_GroupId, b = J2.i_ParameterId }
                select new LogList
                {
                    v_LogId            = A.v_LogId,
                    i_EventTypeId      = A.i_EventTypeId.Value,
                    v_EventTypeName    = J1.v_Value1,
                    i_NodeId           = A.i_NodeLogId.Value,
                    v_NodeName         = B.v_Description,
                    v_OrganizationId   = A.v_OrganizationId,
                    v_OrganizationName = C.v_Name,
                    i_SystemUserId     = A.i_SystemUserId.Value,
                    v_SystemUserName   = D.v_UserName,
                    d_Date             = A.d_Date.Value,
                    v_ProcessEntity    = A.v_ProcessEntity,
                    v_ElementItem      = A.v_ElementItem,
                    i_Success          = A.i_Success.Value,
                    v_SuccessName      = J2.v_Value1,
                    v_ErrorException   = A.v_ErrorException
                };

                if (!string.IsNullOrEmpty(pstrFilterExpression))
                {
                    query = query.Where(pstrFilterExpression);
                }
                if (pdatBeginDate.HasValue && pdatEndDate.HasValue)
                {
                    query = query.Where("d_Date >= @0 && d_Date <= @1", pdatBeginDate.Value, pdatEndDate.Value);
                }
                if (!string.IsNullOrEmpty(pstrSortExpression))
                {
                    query = query.OrderBy(pstrSortExpression);
                }
                if (pintPageIndex.HasValue && pintResultsPerPage.HasValue)
                {
                    int intStartRowIndex = pintPageIndex.Value * pintResultsPerPage.Value;
                    query = query.Skip(intStartRowIndex);
                }
                if (pintResultsPerPage.HasValue)
                {
                    query = query.Take(pintResultsPerPage.Value);
                }

                List <LogList> objData = query.ToList();
                pobjOperationResult.Success = 1;
                return(objData);
            }
            catch (Exception ex)
            {
                pobjOperationResult.Success          = 0;
                pobjOperationResult.ExceptionMessage = ex.Message;
                return(null);
            }
        }
Exemplo n.º 8
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);
            }
        }
Exemplo n.º 9
0
 public RegistrationViewDetailsModel GetUserDetailsByRegistrationID(int?RegistrationID)
 {
     try
     {
         using (var _context = new DatabaseContext())
         {
             //string[] RoleCodes = new string[] { "SuperAdmin", "Manager" };
             var queryResult = (from registration in _context.Registration
                                join A in _context.AssignedRoles on registration.RegistrationID equals A.RegistrationID into A_join
                                from A in A_join.DefaultIfEmpty()
                                join AA in _context.Registration on A.AssignToAdmin equals AA.RegistrationID into AA_join
                                from AA in AA_join.DefaultIfEmpty()
                                join role in _context.Role on registration.RoleID equals role.RoleID into RL_join
                                from role in RL_join.DefaultIfEmpty()
                                join department in _context.Departments on registration.DeptId equals department.DeptId into D_join
                                from department in D_join.DefaultIfEmpty()
                                join job in _context.Jobs on registration.JobId equals job.JobId into J_join
                                from job in J_join.DefaultIfEmpty()
                                where registration.RegistrationID == RegistrationID
                                select new RegistrationViewModel()
             {
                 registration = registration,
                 roles = role,
                 departments = department,
                 jobs = job,
                 ManagerFirstName = AA.FirstName,
                 ManagerLastName = AA.LastName,
                 ManagerMiddleName = AA.MiddleName
             }).FirstOrDefault();
             if (queryResult != null)
             {
                 var objRegistrationViewDetailsModel = new RegistrationViewDetailsModel()
                 {
                     Birthdate              = string.IsNullOrEmpty(queryResult.registration.Birthdate.ToString()) ? "" : Convert.ToDateTime(queryResult.registration.Birthdate, new CultureInfo("es-US", true)).ToString("MM/dd/yyyy"),
                     DateofJoining          = string.IsNullOrEmpty(queryResult.registration.DateofJoining.ToString()) ? "" : Convert.ToDateTime(queryResult.registration.DateofJoining, new CultureInfo("es-US", true)).ToString("MM/dd/yyyy"),
                     DateofLeaving          = string.IsNullOrEmpty(queryResult.registration.DateofLeaving.ToString()) ? "" : Convert.ToDateTime(queryResult.registration.DateofLeaving, new CultureInfo("es-US", true)).ToString("MM/dd/yyyy"),
                     EmailID                = queryResult.registration.EmailID,
                     EmployeeID             = queryResult.registration.EmployeeID,
                     Gender                 = queryResult.registration.Gender,
                     IsActive               = Convert.ToBoolean(queryResult.registration.IsActive),
                     Mobileno               = queryResult.registration.Mobileno,
                     FirstName              = queryResult.registration.FirstName,
                     MiddleName             = queryResult.registration.MiddleName,
                     LastName               = queryResult.registration.LastName,
                     RegistrationID         = queryResult.registration.RegistrationID,
                     RoleID                 = Convert.ToInt32(queryResult.registration.RoleID),
                     Username               = queryResult.registration.Username,
                     WorkEmail              = queryResult.registration.WorkEmail,
                     EmergencyContact       = queryResult.registration.EmergencyContact,
                     EmergencyContactNumber = queryResult.registration.EmergencyContactNumber,
                     DeptId                 = Convert.ToInt32(queryResult.registration.DeptId),
                     ManagerId              = Convert.ToInt32(queryResult.registration.ManagerId),
                     JobId             = Convert.ToInt32(queryResult.registration.JobId),
                     JobTitle          = queryResult.jobs == null ? "" : queryResult.jobs.JobTitle,
                     RoleName          = queryResult.roles == null ? "" : queryResult.roles.Rolename,
                     DepartmentName    = queryResult.departments == null ? "" : queryResult.departments.DepartmentName,
                     ManagerFirstName  = queryResult.ManagerFirstName == null ? "" : queryResult.ManagerFirstName,
                     ManagerMiddleName = queryResult.ManagerMiddleName == null ? "" : queryResult.ManagerMiddleName,
                     ManagerLastName   = queryResult.ManagerLastName == null ? "" : queryResult.ManagerLastName
                 };
                 return(objRegistrationViewDetailsModel);
             }
             return(new RegistrationViewDetailsModel());
         }
     }
     catch (Exception ex)
     {
         throw ex;
     }
 }