public static void DeleteVirtualRegion(VirtualRegion VirtualRegion)
{
using (ElectionContext db = new ElectionContext())
{
db.VirtualRegion.Remove(VirtualRegion);
db.SaveChanges();
}
}
public static void AddVirtualRegion(VirtualRegion VirtualRegion)
{
using (ElectionContext db = new ElectionContext())
{
db.VirtualRegion.Add(VirtualRegion);
db.SaveChanges();
}
}
//додати, змiнити чи видалити вiртуальний округ;
//database.Add(virtual_region);
//database.Remove(virtual_region);
//var id = 1;
//var virtual_region = database.First(e => e.virtual_region_id == id);
public static VirtualRegion GetVirtualRegion(int VirtualRegionID)
{
var VirtualRegion = new VirtualRegion();
using (ElectionContext db = new ElectionContext())
{
VirtualRegion = db.VirtualRegion.Where(e => e.VirtualRegionId == VirtualRegionID).First();
}
return(VirtualRegion);
}
public static void UpdateVirtualRegion(VirtualRegion VirtualRegion)
{
using (ElectionContext db = new ElectionContext())
{
var dbVirtualRegion = db.VirtualRegion.Where(e => e.VirtualRegionId == VirtualRegion.VirtualRegionId).First();
if (dbVirtualRegion != null)
{
dbVirtualRegion = VirtualRegion;
}
db.SaveChanges();
}
}
////////////Голова ЦВК
//обрати чи змiнити голiв окружних комiсiй (до початку виборiв);
//database.Add(chief_region_officer);
//var id = 1;
//var virtual_house = database.First(cro => cro.chief_region_officer_id == id);
public static void AddChiefRegionOfficer(Election election, VirtualRegion virtualRegion, Citizen citizen)
{
using (ElectionContext db = new ElectionContext())
{
var chiefRegionOfficer = new ChiefRegionOfficer
{
ElectionId = election.ElectionId,
VirtulRegionId = virtualRegion.VirtualRegionId,
CitizenId = citizen.CitizenId
};
db.ChiefRegionOfficer.Add(chiefRegionOfficer);
db.SaveChanges();
}
}
public static List <Citizen> GetVirtualRegionVoters(VirtualRegion virtualRegion)
{
var virtualHouseVoters = new List <Citizen>();
using (ElectionContext db = new ElectionContext())
{
virtualHouseVoters = (from region in db.VirtualRegion
join house in db.VirtualHouse on region.VirtualRegionId equals house.VirtualRegionId
join citizenHouse in db.CitizenVirtualHouse on house.VirtualHouseId equals citizenHouse.VirtualHouseId
join citizen in db.Citizen on citizenHouse.CitizenId equals citizen.CitizenId
where region.VirtualRegionId == virtualRegion.VirtualRegionId
select citizen).ToList();
}
return(virtualHouseVoters);
}
//надати головi ЦВК остаточнi результати виборiв в окрузi (пiсля виборiв);
//SELECT candidate_id, first_name, last_name, patronymic, trunc( 100.0 * COUNT(*)/ COUNT(*) over(), 2) as percent_votes FROM virtual_region
// INNER JOIN virtual_house ON virtual_region.virtual_region_id = virtual_house.virtual_region_id
// INNER JOIN vote ON virtual_house.virtual_house_id = vote.virtual_house_id
// INNER JOIN candidate ON vote.candidate_id = candidate.candidate_id
// INNER JOIN citizen ON candidate.citizen_id = citizen.citizen_id
// WHERE vote.election_id = id AND virtual_region.virtual_region_id = virtual_id
// GROUP BY candidate_id;
public static List <Result> GetResultsVirtualRegion(Election election, VirtualRegion virtualRegion)
{
var candidateResult = new List <Result>();
using (ElectionContext db = new ElectionContext())
{
var results = from region in db.VirtualRegion
join house in db.VirtualHouse on region.VirtualRegionId equals house.VirtualRegionId
join vote in db.Vote on house.VirtualHouseId equals vote.VirtualHouseId
join candidate in db.Candidate on vote.CandidateId equals candidate.CandidateId
join citizen in db.Citizen on candidate.CitizenId equals citizen.CitizenId
where vote.ElectionId == election.ElectionId && region.VirtualRegionId == virtualRegion.VirtualRegionId
select new Result
{
CandidateId = candidate.CandidateId,
FirstName = citizen.FirstName,
LastName = citizen.LastName,
Patronymic = citizen.Patronymic,
Votes = 0,
VotesPercent = 0
};
var allVotes = results.Count();
var resultsGroupBy = results.GroupBy(e => e.CandidateId);
foreach (var group in resultsGroupBy)
{
var candidate = group.First();
candidateResult.Add(new Result
{
CandidateId = candidate.CandidateId,
FirstName = candidate.FirstName,
LastName = candidate.LastName,
Patronymic = candidate.Patronymic,
Votes = group.Count(),
VotesPercent = group.Count() / allVotes
});
}
return(candidateResult.OrderBy(e => e.Votes).ToList());
}
}
public static List <ObserverFeedback> GetVirtualRegionObserverFeedback(Election election, VirtualRegion virtualRegion)
{
var observerFeedback = new List <ObserverFeedback>();
using (ElectionContext db = new ElectionContext())
{
observerFeedback = (from region in db.VirtualRegion
join house in db.VirtualHouse on region.VirtualRegionId equals house.VirtualRegionId
join feedback in db.ObserverFeedback on house.VirtualHouseId equals feedback.VirtualHouseId
where region.VirtualRegionId == virtualRegion.VirtualRegionId
select feedback).ToList();
}
return(observerFeedback);
}
//переглянути перелiк виборцiв вiртуальних дiльниць округу (у будь - який час);
public static List <Tuple <VirtualHouse, List <Citizen> > > GetVirtualHousesAndCitizens(VirtualRegion virtualRegion)
{
var virtualHouseAndCitizens = new List <Tuple <VirtualHouse, List <Citizen> > >();
using (ElectionContext db = new ElectionContext())
{
var results = from house in db.VirtualHouse
join citizenHouse in db.CitizenVirtualHouse on house.VirtualHouseId equals citizenHouse.VirtualHouseId
join citizen in db.Citizen on citizenHouse.CitizenId equals citizen.CitizenId
where house.VirtualRegionId == virtualRegion.VirtualRegionId
select new
{
virtualHouse = house,
houseCitizen = citizen
};
var resultsGroupBy = results.GroupBy(e => e.virtualHouse.VirtualHouseId);
foreach (var group in resultsGroupBy)
{
virtualHouseAndCitizens.Add(Tuple.Create(group.First().virtualHouse, new List <Citizen>()));
foreach (var element in group)
{
virtualHouseAndCitizens[virtualHouseAndCitizens.Count - 1].Item2.Add(element.houseCitizen);
}
}
return(virtualHouseAndCitizens);
}
}
