public static void DeleteVirtualHouse(VirtualHouse VirtualHouse)
{
using (ElectionContext db = new ElectionContext())
{
db.VirtualHouse.Remove(VirtualHouse);
db.SaveChanges();
}
}
public static void AddVirtualHouse(VirtualHouse VirtualHouse)
{
using (ElectionContext db = new ElectionContext())
{
db.VirtualHouse.Add(VirtualHouse);
db.SaveChanges();
}
}
//переглянути звернення громадян (у будь-який час);
//SELECT * FROM citizen_feedback
//WHERE virtual_house_id = id AND election_id = e_id;
//уже описаний у попередніх запитах//
public static List <CitizenFeedback> GetCitizenFeedback(Election election, VirtualHouse virtualHouse)
{
var citizenFeedback = new List <CitizenFeedback>();
using (ElectionContext db = new ElectionContext())
{
citizenFeedback = db.CitizenFeedback.Where(e => e.ElectionId == election.ElectionId &&
e.VirtualHouseId == virtualHouse.VirtualHouseId).ToList();
}
return(citizenFeedback);
}
//вилучити громадянина зi своєї вiртуальної дiльницi (до початку виборiв);
public static void DeleteCitizenFromVirtualHouse(Citizen citizen, VirtualHouse virtualHouse)
{
using (ElectionContext db = new ElectionContext())
{
var citizenVirtualHouse = db.CitizenVirtualHouse.
Where(e => e.VirtualHouseId == virtualHouse.VirtualHouseId &&
e.CitizenId == citizen.CitizenId).OrderByDescending(e => e.RegistrationTime).First();
citizenVirtualHouse.IsActive = false;
db.SaveChanges();
}
}
//додати, змiнити чи видалити вiртуальну дiльницю;
//database.Add(virtual_house);
//database.Remove(virtual_house);
//var id = 1;
//var virtual_house = database.First(e => e.virtual_house_id == id);
public static VirtualHouse GetVirtualHouse(int VirtualHouseID)
{
var VirtualHouse = new VirtualHouse();
using (ElectionContext db = new ElectionContext())
{
VirtualHouse = db.VirtualHouse.Where(e => e.VirtualHouseId == VirtualHouseID).First();
}
return(VirtualHouse);
}
public static void UpdateVirtualHouse(VirtualHouse VirtualHouse)
{
using (ElectionContext db = new ElectionContext())
{
var dbVirtualHouse = db.VirtualHouse.Where(e => e.VirtualHouseId == VirtualHouse.VirtualHouseId).First();
if (dbVirtualHouse != null)
{
dbVirtualHouse = VirtualHouse;
}
db.SaveChanges();
}
}
//переглянути перелiк виборцiв вiртуальних дiльниць та округiв(у будь - який час);
public static List <Citizen> GetVirtualHouseVoters(VirtualHouse virtualHouse)
{
var virtualHouseVoters = new List <Citizen>();
using (ElectionContext db = new ElectionContext())
{
virtualHouseVoters = (from house in db.CitizenVirtualHouse
join citizen in db.Citizen on house.CitizenId equals citizen.CitizenId
where house.VirtualHouseId == virtualHouse.VirtualHouseId
select citizen).ToList();
}
return(virtualHouseVoters);
}
//////////Голова дільничної комісії
//виборці віртуальної дільниці
//уже описаний у попередніх запитах//
//долучити громадянина (без визначеноЁ дiльницi) до сво№Ё вiртуальноЁ дiльницi (до початку виборiв);
public static void AddCitizenToVirtualHouse(Citizen citizen, VirtualHouse virtualHouse)
{
using (ElectionContext db = new ElectionContext())
{
db.CitizenVirtualHouse.Add(new CitizenVirtualHouse
{
CitizenId = citizen.CitizenId,
VirtualHouseId = virtualHouse.VirtualHouseId,
RegistrationTime = DateTime.Now,
IsActive = true
});
db.SaveChanges();
}
}
//////////////Голова окружної комісії
//обрати чи змiнити голiв дiльничних комiсiй (до початку виборiв);
//INSERT INTO chief_house_officer(election_id, virtual_house_id, citizen_id)
// VALUES(,,);
//UPDATE chief_house_officer
//SET citizen_id = id
//WHERE chief_house_officer=1;
public static void AddChiefHouseOfficer(Election election, VirtualHouse VirtualHouse, Citizen citizen)
{
using (ElectionContext db = new ElectionContext())
{
var ChiefHouseOfficer = new ChiefHouseOfficer
{
ElectionId = election.ElectionId,
VirtualHouseId = VirtualHouse.VirtualHouseId,
CitizenId = citizen.CitizenId
};
db.ChiefHouseOfficer.Add(ChiefHouseOfficer);
db.SaveChanges();
}
}
//надати головi окружноЁ ком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_house
// 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_house.virtual_house_id = virtual_id
// GROUP BY candidate_id;
public static List <Result> GetVirtualHouseResults(Election election, VirtualHouse virtualHouse)
{
var candidateResult = new List <Result>();
using (ElectionContext db = new ElectionContext())
{
var results = from vote in db.Vote
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 && vote.VirtualHouseId == virtualHouse.VirtualHouseId
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());
}
}