// 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)); }
// 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)); }
public IHttpActionResult UserData() { var user = validateauth.getUser(Request).People.CUNI; string name = _context.Database.SqlQuery <string>("select \"FullName\" from " + CustomSchema.Schema + ".\"FullName\" where \"CUNI\" = '" + user + "'").FirstOrDefault(); return(Ok(name)); }
public IHttpActionResult BP() { ValidateAuth auth = new ValidateAuth(); CustomUser user = auth.getUser(Request); var y = B1conn.getBusinessPartners("*", user: user); return(Ok(y)); }
// 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); }
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)); }
// 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 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/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)); } }
public IHttpActionResult saveLastAltaExcel(int id) { var tempAlta = _context.TempAltas.Where(x => x.BranchesId == id && x.State == "UPLOADED"); ValidateAuth auth = new ValidateAuth(); CustomUser user = auth.getUser(Request); if (tempAlta.Count() < 0) { return(NotFound()); } foreach (var alta in tempAlta) { var person = new People(); var depId = _context.Dependencies.FirstOrDefault(x => x.Cod == alta.Dependencia).Id; person = _context.Person.FirstOrDefault(x => x.CUNI == alta.CUNI); var exist = _context.ContractDetails.Where ( x => x.CUNI == alta.CUNI && x.PeopleId == person.Id && x.DependencyId == depId && x.Dedication == "TH" && x.Linkage == 3 && x.PositionsId == 26 && x.StartDate == alta.StartDate && x.EndDate == alta.EndDate ).ToList().Any(); if (!exist) { var contract = new ContractDetail(); contract.Id = ContractDetail.GetNextId(_context); contract.DependencyId = depId; contract.CUNI = person.CUNI; contract.PeopleId = person.Id; contract.BranchesId = alta.BranchesId; contract.Dedication = "TH"; contract.Linkage = 3; contract.PositionDescription = "Docente Tiempo Horario"; contract.PositionsId = 26; contract.StartDate = alta.StartDate; contract.EndDate = alta.EndDate; contract.Active = true; _context.ContractDetails.Add(contract); alta.State = "INBANKH"; } else { alta.State = "DUPLICATE"; } } _context.SaveChanges(); tempAlta = _context.TempAltas.Where(x => x.BranchesId == id && x.State == "INBANKH"); foreach (var alta in tempAlta) { var person = new People(); person = _context.Person.FirstOrDefault(x => x.CUNI == alta.CUNI); var res = B1.AddOrUpdatePerson(user.Id, person, "DOCENTE T.H."); if (!res.Contains("ERROR")) { alta.State = "INSAP"; } else { alta.State = "ERRORINSAP"; } } _context.SaveChanges(); return(Ok(tempAlta)); }