// 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));
}
