////NOTE: The team definition codes are hard coded here because they are not mapped/referenced in the teanstructure table.
public IEnumerable <TeamStructureALL> GetTeamStructureALLByParamsAndeSetUp(string code, string SearchValue)
{
using (MPRContext_2 entityContext = new MPRContext_2())
{
var periodsForLatestYear = entityContext.TeamStructureALLSet.OrderByDescending(x => x.Year).Take(1).FirstOrDefault();
int maxyear = periodsForLatestYear.Year;
int maxperiod = periodsForLatestYear.Period;
var query = new List <TeamStructureALL>();
if (code == "ACCT")
{
query = (from a in entityContext.TeamStructureALLSet
//where a.Year == setup.Year && a.Period == setup.Period && (a.AccountofficerName.StartsWith(SearchValue.Trim()) || a.Accountofficer_Code.StartsWith(SearchValue.Trim()))
where a.Year == maxyear && a.Period == maxperiod && (a.AccountofficerName.StartsWith(SearchValue.Trim()) || a.Accountofficer_Code.StartsWith(SearchValue.Trim()))
select new
{
Accountofficer_Code = a.Accountofficer_Code,
AccountofficerName = a.AccountofficerName
})
.AsEnumerable().Select(x => new TeamStructureALL
{
Accountofficer_Code = x.Accountofficer_Code,
AccountofficerName = x.AccountofficerName
})
.GroupBy(x => x.Accountofficer_Code).Select(o => o.FirstOrDefault())
.OrderBy(x => x.AccountofficerName)
.ToList();
}
return(query);
}
}
public IEnumerable <TeamStructure> GetTeamstructureBySetUp()
{
using (MPRContext_2 entityContext = new MPRContext_2())
{
var setup = GetSetUp();
//string maxyear = entityContext.TeamStructureSet.Max(x => x.Year);
//List<int> periodsForLatestYear = entityContext.TeamStructureSet.Where(x => x.Year == maxyear).Select(x => x.Period).ToList();
//int maxperiod = periodsForLatestYear.Max();
var periodsForLatestYear = entityContext.TeamStructureSet.OrderByDescending(x => x.Year).Take(1).FirstOrDefault();
string maxyear = periodsForLatestYear.Year.ToString();
int maxperiod = periodsForLatestYear.Period;
var query = (from a in entityContext.TeamStructureSet
where a.Year == maxyear && a.Period == maxperiod
//where a.Year == entityContext.TeamStructureSet.Max(x => x.Year) && a.Period == entityContext.TeamStructureSet.Max(x => x.Period)
//select a);
select new
{
// NPSDate = DbFunctions.TruncateTime(n.NPSDate),
// Team_StructureId = a.Team_StructureId,
Team_Code = a.Team_Code,
TeamName = a.TeamName
})
.AsEnumerable().Select(x => new TeamStructure
{
// Team_StructureId = x.Team_StructureId,
Team_Code = x.Team_Code,
TeamName = x.TeamName
// }).GroupBy(x => new { Team_Code, x.MainCaption, x.Currency }).Select(o => o.FirstOrDefault());
}).GroupBy(x => x.Team_Code).Select(o => o.FirstOrDefault())
.OrderBy(x => x.TeamName)
.ToList();
return(query);
}
}
//NOTE: The team definition codes are hard coded here because they are not mapped/referenced in the teanstructure table.
//NOTE: The below uses daily db.
public IEnumerable <TeamStructure> GetTeamstructureByDefinitionCode(string code)
{
using (MPRContext_2 entityContext = new MPRContext_2())
{
//string maxyear = entityContext.TeamStructureSet.Max(x => x.Year);
//List<int> periodsForLatestYear = entityContext.TeamStructureSet.Where(x => x.Year == maxyear).Select(x => x.Period).ToList();
//int maxperiod = periodsForLatestYear.Max();
var periodsForLatestYear = entityContext.TeamStructureSet.OrderByDescending(x => x.Year).Take(1).FirstOrDefault();
string maxyear = periodsForLatestYear.Year.ToString();
int maxperiod = periodsForLatestYear.Period;
//var setup = GetSetUp();
var query = new List <TeamStructure>();
if (code == "TEM")
{
query = (from a in entityContext.TeamStructureSet
//where a.Year == setup.Year && a.Period == setup.Period
where a.Year == maxyear && a.Period == maxperiod
select new
{
Team_Code = a.Team_Code,
TeamName = a.TeamName
})
.AsEnumerable().Select(x => new TeamStructure
{
Team_Code = x.Team_Code,
TeamName = x.TeamName
})
.GroupBy(x => x.Team_Code).Select(o => o.FirstOrDefault())
.OrderBy(x => x.TeamName)
.ToList();
}
//else if (code == "ACCT")
//{
// query = (from a in entityContext.TeamStructureSet
// where a.Year == setup.Year && a.Period == setup.Period
// select new
// {
// Accountofficer_Code = a.Accountofficer_Code,
// AccountofficerName = a.AccountofficerName
// })
// .AsEnumerable().Select(x => new TeamStructure
// {
// Accountofficer_Code = x.Accountofficer_Code,
// AccountofficerName = x.AccountofficerName
// })
// .GroupBy(x => x.Accountofficer_Code).Select(o => o.FirstOrDefault())
// .OrderBy(x => x.AccountofficerName)
// .ToList();
//}
else if (code == "BRH")
{
query = (from a in entityContext.TeamStructureSet
//where a.Year == setup.Year && a.Period == setup.Period
where a.Year == maxyear && a.Period == maxperiod
select new
{
Branch_Code = a.Branch_Code,
BranchName = a.BranchName
})
.AsEnumerable().Select(x => new TeamStructure
{
Branch_Code = x.Branch_Code,
BranchName = x.BranchName
})
.GroupBy(x => x.Branch_Code).Select(o => o.FirstOrDefault())
.OrderBy(x => x.BranchName)
.ToList();
}
else if (code == "DIV")
{
query = (from a in entityContext.TeamStructureSet
//where a.Year == setup.Year && a.Period == setup.Period
where a.Year == maxyear && a.Period == maxperiod
select new
{
Division_Code = a.Division_Code,
DivisionName = a.DivisionName
})
.AsEnumerable().Select(x => new TeamStructure
{
Division_Code = x.Division_Code,
DivisionName = x.DivisionName
})
.GroupBy(x => x.Division_Code).Select(o => o.FirstOrDefault())
.OrderBy(x => x.DivisionName)
.ToList();
}
else if (code == "GRP")
{
query = (from a in entityContext.TeamStructureSet
// where a.Year == setup.Year && a.Period == setup.Period
where a.Year == maxyear && a.Period == maxperiod
select new
{
Group_Code = a.Group_Code,
GroupName = a.GroupName
})
.AsEnumerable().Select(x => new TeamStructure
{
Group_Code = x.Group_Code,
GroupName = x.GroupName
})
.GroupBy(x => x.Group_Code).Select(o => o.FirstOrDefault())
.OrderBy(x => x.GroupName)
.ToList();
}
else if (code == "REG")
{
query = (from a in entityContext.TeamStructureSet
// where a.Year == setup.Year && a.Period == setup.Period
where a.Year == maxyear && a.Period == maxperiod
select new
{
Region_Code = a.Region_Code,
RegionName = a.RegionName
})
.AsEnumerable().Select(x => new TeamStructure
{
Region_Code = x.Region_Code,
RegionName = x.RegionName
})
.GroupBy(x => x.Region_Code).Select(o => o.FirstOrDefault())
.OrderBy(x => x.RegionName)
.ToList();
}
else if (code == "DIR")
{
query = (from a in entityContext.TeamStructureSet
//where a.Year == setup.Year && a.Period == setup.Period
where a.Year == maxyear && a.Period == maxperiod
select new
{
DIRECTORATECODE = a.DIRECTORATECODE,
DIRECTORATENAME = a.DIRECTORATENAME
})
.AsEnumerable().Select(x => new TeamStructure
{
DIRECTORATECODE = x.DIRECTORATECODE,
DIRECTORATENAME = x.DIRECTORATENAME
})
.GroupBy(x => x.DIRECTORATECODE).Select(o => o.FirstOrDefault())
.OrderBy(x => x.DIRECTORATENAME)
.ToList();
}
else if (code == "ZON")
{
query = (from a in entityContext.TeamStructureSet
//where a.Year == setup.Year && a.Period == setup.Period
where a.Year == maxyear && a.Period == maxperiod
select new
{
Zone_Code = a.Zone_Code,
ZoneName = a.ZoneName
})
.AsEnumerable().Select(x => new TeamStructure
{
Zone_Code = x.Zone_Code,
ZoneName = x.ZoneName
})
.GroupBy(x => x.Zone_Code).Select(o => o.FirstOrDefault())
.OrderBy(x => x.ZoneName)
.ToList();
}
return(query);
}
}
