//переглянути перел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);
}
}
public static void DeleteCitizen(Citizen Citizen)
{
using (ElectionContext db = new ElectionContext())
{
db.Citizen.Remove(Citizen);
db.SaveChanges();
}
}
public static void AddCitizen(Citizen Citizen)
{
using (ElectionContext db = new ElectionContext())
{
db.Citizen.Add(Citizen);
db.SaveChanges();
}
}
//////////Спостерiгач:
//переглянути перелiк виборцiв вiртуальноЁ дiльницi(у будь - який час);
//уже описаний у попередніх запитах//
//переглянути звернення громадян(у будь - який час);
//SELECT * FROM citizen_feedback
//WHERE virtual_house_id = id AND election_id = e_id;
//уже описаний у попередніх запитах//
//подати скаргу (пiд час виборiв);
//INSERT INTO observer_feedback(election_id, virtual_house_id, observer_id, text)
// VALUES(,,);
public static void AddObserverFeedback(ObserverFeedback observerFeedback)
{
using (ElectionContext db = new ElectionContext())
{
db.ObserverFeedback.Add(observerFeedback);
db.SaveChanges();
}
}
public static void DeleteVirtualHouse(VirtualHouse VirtualHouse)
{
using (ElectionContext db = new ElectionContext())
{
db.VirtualHouse.Remove(VirtualHouse);
db.SaveChanges();
}
}
////////Громадянин:
//подати звернення до голови вiртуальноЁ дiльницi (у будь - який час);
//INSERT INTO citizen_feedback(election_id, virtual_house_id, citizen_id, text)
// VALUES(,,)
public static void AddCitizenFeedback(CitizenFeedback citizenFeedback)
{
using (ElectionContext db = new ElectionContext())
{
db.CitizenFeedback.Add(citizenFeedback);
db.SaveChanges();
}
}
public static void AddVirtualHouse(VirtualHouse VirtualHouse)
{
using (ElectionContext db = new ElectionContext())
{
db.VirtualHouse.Add(VirtualHouse);
db.SaveChanges();
}
}
//проголосувати(пiд час виборiв, тiльки один раз);
//IF(EXISTS(SELECT vote_id FROM vote WHERE citizen_id = c_id AND election_id = e_id), , )
//INSERT INTO vote(election_id, virtual_house_id, citizen_id, candidate_id)
// VALUES(,,);
public static void AddVote(Vote vote)
{
using (ElectionContext db = new ElectionContext())
{
db.Vote.Add(vote);
db.SaveChanges();
}
}
public static void DeleteVirtualRegion(VirtualRegion VirtualRegion)
{
using (ElectionContext db = new ElectionContext())
{
db.VirtualRegion.Remove(VirtualRegion);
db.SaveChanges();
}
}
public static void AddElection(Election election)
{
using (ElectionContext db = new ElectionContext())
{
db.Election.Add(election);
db.SaveChanges();
}
}
public static void AddVirtualRegion(VirtualRegion VirtualRegion)
{
using (ElectionContext db = new ElectionContext())
{
db.VirtualRegion.Add(VirtualRegion);
db.SaveChanges();
}
}
public static void DeleteElection(Election election)
{
using (ElectionContext db = new ElectionContext())
{
db.Election.Remove(election);
db.SaveChanges();
}
}
public static List <ObserverFeedback> GetObserverFeedback(Election election)
{
var observerFeedback = new List <ObserverFeedback>();
using (ElectionContext db = new ElectionContext())
{
observerFeedback = db.ObserverFeedback.Where(e => e.ElectionId == election.ElectionId).ToList();
}
return(observerFeedback);
}
//переглянути усi скарги спостерiгачiв та звернення громадян(у будь - який час);
//SELECT * FROM citizen_feedback;
//SELECT * FROM observer_feedback;
public static List <CitizenFeedback> GetCitizenFeedback(Election election)
{
var citizenFeedback = new List <CitizenFeedback>();
using (ElectionContext db = new ElectionContext())
{
citizenFeedback = db.CitizenFeedback.Where(e => e.ElectionId == election.ElectionId).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нити чи видалити вибори;
//database.Add(election);
//database.Remove(election);
//var id = 1;
//var election = database.First(e => e.election_id == id);
public static Election GetElection(int electionID)
{
var election = new Election();
using (ElectionContext db = new ElectionContext())
{
election = db.Election.Where(e => e.ElectionId == electionID).First();
}
return(election);
}
//додати, зм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 Citizen GetCitizen(int CitizenID)
{
var Citizen = new Citizen();
using (ElectionContext db = new ElectionContext())
{
Citizen = db.Citizen.Where(e => e.CitizenId == CitizenID).First();
}
return(Citizen);
}
//додати, зм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 UpdateChiefHouseOfficer(int ChiefHouseOfficerId, Citizen citizen)
{
using (ElectionContext db = new ElectionContext())
{
var dbChiefHouseOfficer = db.ChiefHouseOfficer.Where(e => e.ChiefHouseOfficerId == ChiefHouseOfficerId).First();
if (dbChiefHouseOfficer != null)
{
dbChiefHouseOfficer.CitizenId = citizen.CitizenId;
}
db.SaveChanges();
}
}
public static void UpdateCitizen(Citizen Citizen)
{
using (ElectionContext db = new ElectionContext())
{
var dbCitizen = db.Citizen.Where(e => e.CitizenId == Citizen.CitizenId).First();
if (dbCitizen != null)
{
dbCitizen = Citizen;
}
db.SaveChanges();
}
}
public static void UpdateElection(Election election)
{
using (ElectionContext db = new ElectionContext())
{
var dbElection = db.Election.Where(e => e.ElectionId == election.ElectionId).First();
if (dbElection != null)
{
dbElection = election;
}
db.SaveChanges();
}
}
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();
}
}
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в(у будь - який час);
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в);
//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 <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);
}
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);
}
