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