public IHttpActionResult Get() { var query = "select * from " + CustomSchema.Schema + ".lastcontracts " + " where (\"Active\"=true or \"EndDate\">=current_date) ;"; var rawresult = _context.Database.SqlQuery <ContractDetailViewModel>(query).ToList(); var user = auth.getUser(Request); var res = auth.filerByRegional(rawresult.AsQueryable(), user); return(Ok(res)); }
public static IQueryable <Civil> findBPInSAP(string CardCode, CustomUser user, ApplicationDbContext _context) { var auth = new ValidateAuth(); var query = "select 0 \"Id\",0 \"CreatedBy\",null \"Document\", ocrd.\"CardCode\" \"SAPId\", ocrd.\"CardName\" \"FullName\",ocrd.\"LicTradNum\" \"NIT\", br.\"Id\" \"BranchesId\"" + " from " + ConfigurationManager.AppSettings["B1CompanyDB"] + ".ocrd" + " inner join " + ConfigurationManager.AppSettings["B1CompanyDB"] + ".crd8" + " on ocrd.\"CardCode\" = crd8.\"CardCode\"" + " inner join " + CustomSchema.Schema + ".\"Branches\" br" + " on br.\"CodigoSAP\" = crd8.\"BPLId\"" + " where ocrd.\"validFor\" = 'Y'" + " and crd8.\"DisabledBP\" = 'N'" + " and ocrd.\"CardType\" = 'S'" + " and ocrd.\"CardCode\"= '" + CardCode + "';"; var rawresult = _context.Database.SqlQuery <Civil>(query).ToList(); if (rawresult.Count() == 0) { return(null); } var res = auth.filerByRegional(rawresult.AsQueryable(), user); if (res.Count() == 0) { return(null); } return(res.Cast <Civil>()); }
// GET api/Level public IHttpActionResult Get() { /* var query = "select * from " + CustomSchema.Schema + ".lastcontracts lc" + * " inner join " + CustomSchema.Schema + ".\"Position\" p" + * " on lc.\"PositionsId\" = p.\"Id\"" + * " where (lc.\"EndDate\" is null or lc.\"EndDate\"> current_date)" + * " and p.\"IsDesignated\" = true"; * */ var query = "select lc.\"Id\", pe.\"Document\", pe.\"CUNI\", " + "fn.\"FullName\",\r\np.\"Name\" as \"Positions\", " + "de.\"Name\" as \"Dependency\",\r\nde.\"Id\" as \"DependencyCod\"," + "\r\nbr.\"Abr\" as \"Branches\", lc.\"StartDate\"," + "\r\nlc.\"EndDateNombramiento\" as \"EndDate\"" + "\r\nfrom ADMNALRRHH.\"ContractDetail\" lc" + "\r\ninner join ADMNALRRHH.\"Position\" p" + "\r\non lc.\"PositionsId\" = p.\"Id\"" + "\r\ninner join admnalrrhh.\"People\" pe" + "\r\non pe.\"Id\" = lc.\"PeopleId\"" + "\r\ninner join admnalrrhh.\"FullName\" fn" + "\r\non fn.\"PeopleId\" = pe.\"Id\"" + "\r\ninner join admnalrrhh.\"Dependency\" de" + "\r\non de.\"Id\" = lc.\"DependencyId\"" + "\r\ninner join admnalrrhh.\"Branches\" br" + "\r\non br.\"Id\" = lc.\"BranchesId\"" + "\r\nwhere p.\"IsDesignated\" = true" + "\r\norder by lc.\"Active\" desc, lc.\"EndDate\" asc"; var rawresult = _context.Database.SqlQuery <ContractDetailViewModel>(query).ToList(); var user = auth.getUser(Request); var res = auth.filerByRegional(rawresult.AsQueryable(), user); return(Ok(res)); }
// GET api/People public IHttpActionResult Get() { string query = "select p.\"Id\",p.CUNI,p.\"Document\",lc.\"FullName\", case when (lc.\"Active\"=false and lc.\"EndDate\"<current_date) then 'Inactivo' else 'Activo' end as \"Status\", lc.\"BranchesId\" " + "from " + CustomSchema.Schema + ".\"People\" p " + "inner join " + CustomSchema.Schema + ".\"LASTCONTRACTS\" lc " + "on p.cuni = lc.cuni " + " order by \"FullName\";"; var rawResult = _context.Database.SqlQuery <ContractDetailViewModel>(query).Select(x => new { x.Id, x.CUNI, x.Document, x.FullName, x.Status, x.BranchesId }).AsQueryable(); var user = auth.getUser(Request); var result = auth.filerByRegional(rawResult, user).ToList().Select(x => new { x.Id, x.CUNI, x.Document, x.FullName, x.Status }).ToList(); return(Ok(result)); }
public IHttpActionResult History() { var user = auth.getUser(Request); var query = "select * from " + CustomSchema.Schema + ".\"Serv_Process\" " + " where \"State\" = '" + ServProcess.Serv_FileState.PendingApproval + "' " + " or \"State\" = '" + ServProcess.Serv_FileState.INSAP + "' " + " or \"State\" = '" + ServProcess.Serv_FileState.Rejected + "' " + " order by (" + " case when \"State\" = '" + ServProcess.Serv_FileState.PendingApproval + "' then 1 " + " when \"State\" = '" + ServProcess.Serv_FileState.INSAP + "' then 3 " + " when \"State\" = '" + ServProcess.Serv_FileState.Rejected + "' then 5 " + " end) asc, " + " \"CreatedAt\" desc;"; var rawresult = _context.Database.SqlQuery <ServProcess>(query).ToList(); if (rawresult.Count() == 0) { return(NotFound()); } var res = auth.filerByRegional(rawresult.AsQueryable(), user).Cast <ServProcess>(); if (res.Count() == 0) { return(Unauthorized()); } var res2 = (from r in res join b in _context.Branch.ToList() on r.BranchesId equals b.Id select new { r.Id, r.BranchesId, Branches = b.Name, r.FileType, r.State, r.SAPId, CreatedAt = r.CreatedAt.ToString("dd MMMM yyyy HH:mm") }).ToList(); return(Ok(res2)); }
// GET api/Level public IHttpActionResult Get() { //var deplist = _context.Dependencies.Include(p => p.OrganizationalUnit).Include(i => i.Parent).ToList().Select(x => new { x.Id, x.Cod, x.Name, OrganizationalUnit = x.OrganizationalUnit.Name, Parent = x.Parent.Name }).OrderBy(x => x.Cod); var deplist = (from dependency in _context.Dependencies join branch in _context.Branch on dependency.BranchesId equals branch.Id join OU in _context.OrganizationalUnits on dependency.OrganizationalUnitId equals OU.Id join parent in _context.Dependencies on dependency.ParentId equals parent.Id join performance in _context.PerformanceAreas on dependency.PerformanceAreaId equals performance.Id select new { dependency.Id, dependency.Cod, dependency.Name, OrganizationalUnit = OU.Name, Parent = parent.Name, Branch = branch.Abr, BranchesId = branch.Id, dependency.Active,dependency.Academic,dependency.PerformanceAreaId, PerformanceArea = performance.Name} ).OrderBy(x => x.Cod); var user = validator.getUser(Request); validator.filerByRegional(deplist,user); return Ok(deplist); }
// GET api/Positions public IHttpActionResult Get() { var all = from pos in _context.Position.Include(x => x.Level).Include(x => x.PerformanceArea) join brs in _context.BranchhasPositions on pos.Id equals brs.PositionId where brs.Enabled select new { pos.Id, pos.Name, pos.Level.Cod, pos.Level.Category, brs.BranchesId, pos.PerformanceAreaId, PerformanceArea = pos.PerformanceArea.Name, pos.IsDesignated, pos.DefaultLinkage, brs.Enabled }; var user = auth.getUser(Request); var filtered = auth.filerByRegional(all.AsQueryable(), user).ToList().OrderBy(x => x.Id); List <dynamic> res = new List <dynamic>(); foreach (var p in filtered) { dynamic r = new JObject(); r.Id = p.Id; r.Name = p.Name; r.Cod = p.Cod; r.Category = p.Category; r.PerformanceArea = p.PerformanceArea; r.IsDesignated = p.IsDesignated; r.DefaultLinkage = p.DefaultLinkage; if (!res.Any(x => x.Id == r.Id)) { res.Add(r); } } var xs = res.ToList().Distinct(); return(Ok(xs)); }
public IHttpActionResult Careers() //Este endpoint permite obtener la Unidad Organizacional de la carrera { ValidateAuth auth = new ValidateAuth(); string query = "select a.\"PrcCode\", a.\"PrcName\", a.\"ValidFrom\", a.\"ValidTo\", a.\"U_NUM_INT_CAR\", a.\"U_Nivel\", b.\"U_CODIGO_DEPARTAMENTO\" as \"UO\", b.\"U_CODIGO_SEGMENTO\", br.\"Id\" as \"BranchesId\" " + "from ucatolica.oprc a " + "inner join " + ConfigurationManager.AppSettings["B1CompanyDB"] + ".\"@T_GEN_CARRERAS\" b " + " on a.\"PrcCode\" = b.\"U_CODIGO_CARRERA\" " + "inner join " + CustomSchema.Schema + ".\"Branches\" br " + " on br.\"Abr\" = b.\"U_CODIGO_SEGMENTO\" " + " WHERE a.\"DimCode\" = " + 3; var rawresult = _context.Database.SqlQuery <CostCenterCarrera>(query).OrderBy(x => x.PrcCode); var user = auth.getUser(Request); var filteredList = auth.filerByRegional(rawresult.AsQueryable(), user).ToList().OrderBy(x => x.PrcCode); return(Ok(filteredList)); }
public static IQueryable <Civil> findBPInSAP(string CardCode, CustomUser user, ApplicationDbContext _context) { string condicion = ""; // Si el codigo de socio comienza con P o H, entonces se busca en socios de negocio, sino es busqueda por CI if (CardCode.Substring(0, 1).Equals("H") || CardCode.Substring(0, 1).Equals("P")) { condicion = " and ocrd.\"CardCode\"= '" + CardCode + "'"; } else { condicion = " and ocrd.\"LicTradNum\"= '" + CardCode + "'"; } var auth = new ValidateAuth(); var query = "select 0 \"Id\",0 \"CreatedBy\",null \"Document\", ocrd.\"CardCode\" \"SAPId\", ocrd.\"CardName\" \"FullName\",ocrd.\"LicTradNum\" \"NIT\", br.\"Id\" \"BranchesId\"" + " from " + ConfigurationManager.AppSettings["B1CompanyDB"] + ".ocrd" + " inner join " + ConfigurationManager.AppSettings["B1CompanyDB"] + ".crd8" + " on ocrd.\"CardCode\" = crd8.\"CardCode\"" + " inner join " + CustomSchema.Schema + ".\"Branches\" br" + " on br.\"CodigoSAP\" = crd8.\"BPLId\"" + " where ocrd.\"validFor\" = 'Y'" + " and crd8.\"DisabledBP\" = 'N'" + " and ocrd.\"CardType\" = 'S'" + condicion; var rawresult = _context.Database.SqlQuery <Civil>(query).ToList(); if (rawresult.Count() == 0) { return(null); } var res = auth.filerByRegional(rawresult.AsQueryable(), user); if (res.Count() == 0) { return(null); } return(res.Cast <Civil>()); }
// GET api/Branches public IHttpActionResult Get() { var brs = _context.Branch.Include(x => x.Dependency).Select(x => new { x.Id, x.Name, x.ADGroupName, x.ADOUName, x.Abr, Dependency = x.Dependency.Name, x.InitialsInterRegional, x.SerieComprobanteContalbeSAP, x.SocioGenericDerechosLaborales, x.InicialSN, x.CodigoSAP, x.CuentaSociosRCUNI, x.CuentaSociosHCUNI, x.VatGroup }).OrderBy(x => x.Id); var user = auth.getUser(Request); var res = auth.filerByRegional(brs, user, isBranchtable: true); return(Ok(res)); }
public IHttpActionResult CivilbyBranch(int id) { var B1 = B1Connection.Instance(); if (id != 0) { // we get the Branches from SAP var query = "select c.\"Id\", c.\"FullName\",c.\"SAPId\",c.\"NIT\",c.\"Document\",c.\"CreatedBy\",ocrd.\"BranchesId\" " + "from " + CustomSchema.Schema + ".\"Civil\" c" + " inner join " + " (select ocrd.\"CardCode\", br.\"Id\" \"BranchesId\"" + " from " + ConfigurationManager.AppSettings["B1CompanyDB"] + ".ocrd" + " inner join " + ConfigurationManager.AppSettings["B1CompanyDB"] + ".crd8" + " on ocrd.\"CardCode\" = crd8.\"CardCode\"" + " inner join " + CustomSchema.Schema + ".\"Branches\" br" + " on br.\"CodigoSAP\" = crd8.\"BPLId\"" + " where ocrd.\"validFor\" = \'Y\'" + " and crd8.\"DisabledBP\" = \'N\') ocrd" + " on c.\"SAPId\" = ocrd.\"CardCode\"" + " where ocrd.\"BranchesId\"=" + id + ";"; var rawresult = _context.Database.SqlQuery <Civil>(query); var user = auth.getUser(Request); var res = auth.filerByRegional(rawresult.AsQueryable(), user); return(Ok(res)); } else { var user = auth.getUser(Request); var brs = AD.getUserBranches(user); var brsIds = brs.Select(x => x.Id); string StrIds = ""; int n = brsIds.Count(); int i = 0; foreach (var brid in brsIds) { i++; StrIds += brid + "" + (i == n?"":", "); } var query = "select c.\"Id\", c.\"FullName\",c.\"SAPId\",c.\"NIT\",c.\"Document\",c.\"CreatedBy\",ocrd.\"BranchesId\" " + "from " + CustomSchema.Schema + ".\"Civil\" c" + " inner join " + " (select ocrd.\"CardCode\", br.\"Id\" \"BranchesId\"" + " from " + ConfigurationManager.AppSettings["B1CompanyDB"] + ".ocrd" + " inner join " + ConfigurationManager.AppSettings["B1CompanyDB"] + ".crd8" + " on ocrd.\"CardCode\" = crd8.\"CardCode\"" + " inner join " + CustomSchema.Schema + ".\"Branches\" br" + " on br.\"CodigoSAP\" = crd8.\"BPLId\"" + " where ocrd.\"validFor\" = \'Y\'" + " and crd8.\"DisabledBP\" = \'N\') ocrd" + " on c.\"SAPId\" = ocrd.\"CardCode\"" + " where ocrd.\"BranchesId\" in (" + StrIds + ");"; var rawresult = _context.Database.SqlQuery <Civil>(query); var res = auth.filerByRegional(rawresult.AsQueryable(), user); return(Ok(res)); } }