using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Linq.Expressions;
using System.Runtime.Serialization;
using System.Transactions;
using System.Xml.Linq;
using Vec.Common.Entities;
using Vec.ComputerCount.Entities;
using Vec.Framework.Core;
using Vec.Framework.Security;
using EntityState = Vec.Common.Entities.EntityState;

namespace Vec.ComputerCount.DAL
{
    public enum TieResolutionLogic
    {
        ByLot = 0,
        CompareBack_ByLot = 1
    }

    public interface IComputerCountDataContext
    {
        Guid ElectionElectorateVacancyId { get; set; }
        void SaveCalculation(MCalculation calc, List<MDistribution> distributions);
        MCalculation GetCalculation(Guid ElectionElectorateVacancyId, Guid CalculationId);
        List<MCalculation> GetCalculations(Guid ElectionElectorateVacancyId);
        MDistribution GetNextDistribution(Guid CalculationId, int fromCycle);
        List<MDistribution> GetAllDistributions(Guid CalculationId);
        string GetBallotPapersCounts(Guid ElectionElectorateVacancyId);
        List<MBallotPaper> LoadBallotPapersWithFrequency(Guid electionElectorateVacancyId);
        List<MBallotPaper> LoadBallotPapers();
        List<MAllocatedTicket> GetAllocatedTickets();
        Vec.ComputerCount.Entities.Round GetLastRound(Guid electionElectorateVacancyId, CountPhase? countPhaseFilter = null);
        Round GetLastDALRound(Guid electionElectorateVacancyId, CountPhase? countPhaseFilter);
        bool AllElectionElectorateVacanciesHaveCountParameterWithValue(IEnumerable<Guid> electionElectorateVacancyIds, string countParameterName, string value);
        Vec.ComputerCount.Entities.GetInformalBatchesSummaryResponse.ManualResultsEntity GetComputerCountSourceInformal(Guid electionElectorateVacancyId, IEnumerable<int> ballotBoxIds);
        Common.Entities.CountType GetManualResultsComputerCountSource(Guid electionElectorateVacancyId, bool effective);
        ATLDataEntryType GetATLDataEntryType(Guid electionElectorateVacancyId);
        InformalDataEntryType GetInformalDataEntryType(Guid electionElectorateVacancyId);
        int GetInformalTotalVotes(Guid electionElectorateVacancyId);
    }

    public class DComputerCount : IComputerCountDataContext
    {
        private const int _MaxBallotPaperPerBatch = 50;
        protected IDCentral _IDCentral;
        protected IDataContextWrapper<ComputerCountDataContext> _DataContext;
        private const int MinInactivityTimeInMinutes = 10;

        public DComputerCount(Guid ElectionElectorateVacancyId, IDCentral IDCentral)
            : base()
        {
            this.ElectionElectorateVacancyId = ElectionElectorateVacancyId;
            _IDCentral = IDCentral;
            _DataContext = new DataContextWrapper<ComputerCountDataContext>(ConnectionString);
            _DataContext.ExecuteCommand("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED");
        }

        public DComputerCount(IDataContextWrapper<ComputerCountDataContext> dataContext)
            : base()
        {
            _DataContext = dataContext;
            _DataContext.ExecuteCommand("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED");
        }

        public IDataContextWrapper<ComputerCountDataContext> GetDataContext()
        {
            var dc = new DataContextWrapper<ComputerCountDataContext>(ConnectionString);
            dc.ExecuteCommand("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED");
            return dc;
        }

        private string ConnectionString
        {
            get
            {
                return _IDCentral.GetConnectionString(ElectionElectorateVacancyId);
            }
        }

        public Guid ElectionElectorateVacancyId { get; set; }

        public void SaveCalculation(MCalculation calc, List<MDistribution> distributions)
        {
            InsertOrMergeCalculation(calc, distributions);
        }

        protected void InsertOrMergeCalculation(MCalculation calc, List<MDistribution> distributions)
        {
            List<Calculation> existingCalculation = (from c in _DataContext.Table<Calculation>()
                                                     where c.CalculationId == calc.CalculationId
                                                     select c).ToList();

            if (existingCalculation.Count == 0)
            {
                InsertCalculation(calc, distributions);
            }
            else
            {
                MergeCalculation(existingCalculation[0], calc, distributions);
            }
        }

        protected void MergeCalculation(Calculation savedCalculation, MCalculation calc, List<MDistribution> distributions)
        {
            int lastSavedCycle = 0;
            int lastSavedStep = 0;

            savedCalculation.CalculationStateId = (int)calc.CalculationState;

            if (savedCalculation.Distributions.Count > 0)
            {
                Distribution lastSavedDistribution = (from d in savedCalculation.Distributions
                                                      orderby d.Step descending
                                                      select d).FirstOrDefault();
                lastSavedStep = lastSavedDistribution.Step;
                Transfer lastSavedTransfer = (from t in lastSavedDistribution.Transfers
                                              orderby t.CycleNumber descending
                                              select t).FirstOrDefault();
                lastSavedCycle = (int)lastSavedTransfer.CycleNumber;
                MergeTransfer(lastSavedTransfer, lastSavedStep, distributions);

                _DataContext.SubmitChanges();
            }
            InsertNewTransfers(savedCalculation, distributions, lastSavedStep, lastSavedCycle);
            _DataContext.SubmitChanges();
        }

        protected void MergeTransfer(Transfer dbt, int lastSavedStep, List<MDistribution> distributions)
        {
            MTransfer latestVersionOfTransfer = (from t in distributions.Where(x => x.Step == lastSavedStep).First().Transfers
                                                 where t.CycleNo == dbt.CycleNumber
                                                 select t).FirstOrDefault();

            foreach (CandidateAllocation dbca in dbt.CandidateAllocations)
            {
                MCandidateAllocation ca = latestVersionOfTransfer.CandidateAllocations.Where(x => x.Candidate.Position == dbca.CandidatePosition).FirstOrDefault();
                if ((int)ca.CountStatus != dbca.CandidateCountStatusId)
                {
                    dbca.CandidateCountStatusId = (int)ca.CountStatus;
                    dbca.ElectorOrExcludeOrder = ca.ElectorOrExcludeOrder;
                }
            }
        }

        protected void InsertCalculation(MCalculation calculation, List<MDistribution> distributions)
        {
            var recountconfig = (from p in _DataContext.Table<CountParameter>()
                                 where p.ElectionElectorateVacancyId == ElectionElectorateVacancyId
                                 && p.Name == Vec.Common.Entities.Consts.PARAM_HasRecount
                                 && (p.Value == Vec.Common.Entities.RecountEnum.WithValidation.ToString()
                                     || p.Value == Vec.Common.Entities.RecountEnum.WithoutValidation.ToString())
                                 select p.Value);

            var counttype = Vec.Common.Entities.CountType.Primary;
            if (recountconfig.Count() > 0)
            {
                counttype = Vec.Common.Entities.CountType.Recount;
            }

            Calculation newcalc = new Calculation
            {
                CalculationId = calculation.CalculationId,
                CalculationStateId = (int)calculation.CalculationState,
                ElectionElectorateVacancyId = calculation.ElectionElectorateVacancyId,
                CalculateDateTime = calculation.CalculateDateTime,
                Quota = calculation.Quota,
                TotalBallotPapers = calculation.TotalBallotPapers,
                TotalFormalBallotPapers = calculation.TotalFormalBallotPapers,
                TotalInformalBallotPapers = calculation.TotalInformalBallotPapers,
                Vacancies = calculation.Vacancies,
                CalculatedByComputer = calculation.CalculatedByComputer,
                CalculationMethodId = (int)calculation.CalculationMethod,
                CountTypeId = (int)counttype
            };

            InsertNewTransfers(newcalc, distributions, 0, 0);
            _DataContext.InsertOnSubmit(newcalc);
            _DataContext.SubmitChanges();
        }

        protected void InsertNewTransfers(Calculation savedCalculation, List<MDistribution> distributions, int lastSavedStep, int lastSavedCycle)
        {
            foreach (MDistribution d in distributions)
            {
                Distribution currentDistribution;

                if (d.Step > lastSavedStep)
                {
                    int? candidatePosition = null;
                    if (d.Candidate != null)
                    {
                        candidatePosition = d.Candidate.Position;
                    }

                    currentDistribution = new Distribution
                    {
                        DistributionId = Guid.NewGuid(),
                        CandidatePosition = candidatePosition,
                        DistributionTypeId = (int)d.DistributionType,
                        Step = d.Step
                    };
                    savedCalculation.Distributions.Add(currentDistribution);
                }
                else
                {
                    currentDistribution = (from dbd in savedCalculation.Distributions
                                           where dbd.Step == d.Step
                                           select dbd).FirstOrDefault();
                }

                foreach (MTransfer t in d.Transfers)
                {
                    if (t.CycleNo > lastSavedCycle)
                    {
                        Transfer newtransfer = Translate(t);
                        foreach (MCandidateAllocation ca in t.CandidateAllocations)
                        {
                            newtransfer.CandidateAllocations.Add(Translate(ca));
                        }
                        currentDistribution.Transfers.Add(newtransfer);
                    }
                }
            }
        }

        public MCalculation GetCalculation(Guid ElectionElectorateVacancyId, Guid CalculationId)
        {
            return (from c in _DataContext.Table<Calculation>()
                    where c.ElectionElectorateVacancyId == ElectionElectorateVacancyId &&
                    c.CalculationId == CalculationId
                    select Translate(c)).FirstOrDefault();
        }

        public List<MCalculation> GetCalculations(Guid ElectionElectorateVacancyId)
        {
            var result = (from c in _DataContext.Table<Calculation>()
                          where c.ElectionElectorateVacancyId == ElectionElectorateVacancyId && c.CalculatedByComputer
                          select Translate(c)).ToList();

            return result;
        }

        private MCalculation Translate(Calculation c)
        {
            return new MCalculation
            {
                CalculationMethod = (CalculationMethod)(c.CalculationMethodId ?? 2),
                CalculateDateTime = c.CalculateDateTime,
                CalculationId = c.CalculationId,
                ElectionElectorateVacancyId = ElectionElectorateVacancyId,
                Quota = c.Quota,
                TotalBallotPapers = c.TotalBallotPapers,
                TotalFormalBallotPapers = c.TotalFormalBallotPapers,
                TotalInformalBallotPapers = c.TotalInformalBallotPapers,
                CalculatedByComputer = c.CalculatedByComputer,
                CountType = c.CountTypeId.HasValue ? (Common.Entities.CountType)c.CountTypeId.Value : Common.Entities.CountType.Unknown,
                Vacancies = c.Vacancies
            };
        }

        public MDistribution GetNextDistribution(Guid CalculationId, int fromCycle)
        {
            List<Transfer> transfers = (from c in _DataContext.Table<Calculation>()
                                        join d in _DataContext.Table<Distribution>() on c.CalculationId equals d.CalculationId
                                        join t in _DataContext.Table<Transfer>() on d.DistributionId equals t.DistributionId
                                        where c.CalculationId == CalculationId
                                        && t.CycleNumber == fromCycle + 1
                                        select t).ToList();
            if (transfers.Count == 0)
            {
                return null;
            }
            else
            {
                MCandidate distCandidate = null;
                if (transfers[0].Distribution.CandidatePosition != null)
                {
                    distCandidate = new MCandidate { Name = string.Format("C{0}", (int)transfers[0].Distribution.CandidatePosition), Position = (int)transfers[0].Distribution.CandidatePosition };
                }

                MDistribution returndistribution = new MDistribution
                {
                    Step = transfers[0].Distribution.Step,
                    DistributionType = (DistributionType)transfers[0].Distribution.DistributionTypeId,
                    Candidate = distCandidate,
                    Transfers = new List<MTransfer>()
                };

                returndistribution.Transfers.Add(Translate(transfers[0]));
                return returndistribution;
            }
        }

        protected MTransfer Translate(Transfer t)
        {
            MTransfer returntransfer = new MTransfer
            {
                CycleNo = (int)t.CycleNumber,
                ExhaustedPapers = (int)t.ExhaustedPapers,
                ExhaustedVotes = (int)t.ExhaustedVotes,
                FromCycles = (string)t.FromCycles,
                GainLoss = (int)t.GainLoss,
                TotalExhaustedPapers = (int)t.ProgressTotalExhaustedPapers,
                TotalExhaustedVotes = (int)t.ProgressTotalExhaustedVotes,
                TotalGainLoss = (int)t.ProgressTotalGainLoss,
                BallotPapersTotal = (int)t.TotalBallotPapers,
                WeightedTransferValue = (decimal)t.WeightedTransferValue,
                CandidateAllocations = new List<MCandidateAllocation>()
            };

            foreach (CandidateAllocation ca in t.CandidateAllocations)
            {
                returntransfer.CandidateAllocations.Add(new MCandidateAllocation
                {
                    CountStatus = (CandidateCountStatus)ca.CandidateCountStatusId,
                    Candidate = new MCandidate { Name = string.Format("C{0}", ca.CandidatePosition), Position = ca.CandidatePosition },
                    ElectorOrExcludeOrder = (int)ca.ElectorOrExcludeOrder,
                    BallotPapers = (int)ca.BallotPapers,
                    TotalBallotPapers = (int)ca.ProgressTotalBallotPapers,
                    TotalVotes = (int)ca.ProgressTotalVotes,
                    Votes = (int)ca.Votes
                });
            }

            return returntransfer;
        }

        private static MBallotInformal Translate(BallotBoxInformal bbi)
        {
            return new MBallotInformal
            {
                BallotBoxInformalId = bbi.BallotBoxInformalId,
                BallotBoxId = bbi.BallotBoxId,
                Total = bbi.Total,
                BatchNbr = bbi.BatchNbr,
                CountPhase = (CountPhase)bbi.CountPhaseId
            };
        }

        public List<MDistribution> GetAllDistributions(Guid CalculationId)
        {
            List<Distribution> distributions = (from c in _DataContext.Table<Calculation>()
                                                join d in _DataContext.Table<Distribution>() on c.CalculationId equals d.CalculationId
                                                where c.CalculationId == CalculationId
                                                orderby d.Step
                                                select d).ToList();

            List<MDistribution> returndistributions = new List<MDistribution>();
            foreach (Distribution d in distributions)
            {
                returndistributions.Add(Translate(d));
            }

            return returndistributions;
        }

        public static string DataContractSerializeObject<T>(T objectToSerialize)
        {
            using (var memoryStream = new MemoryStream())
            {
                var serializer = new DataContractSerializer(typeof(T));
                serializer.WriteObject(memoryStream, objectToSerialize);

                memoryStream.Seek(0, SeekOrigin.Begin);
                var result = new StreamReader(memoryStream).ReadToEnd();

                return result;
            }
        }

        public string GetBallotPapersCounts(Guid ElectionElectorateVacancyId)
        {
            var round = GetLastRound(ElectionElectorateVacancyId);

            int bps = (from bb in _DataContext.Table<BallotBox>()
                        join b in _DataContext.Table<Batch>() on bb.BallotBoxId equals b.BallotBoxId
                        join bp in _DataContext.Table<BallotPaper>() on b.BatchId equals bp.BatchId
                        where bb.ElectionElectorateVacancyId == ElectionElectorateVacancyId && b.CountPhaseId == (int)round.CountPhase
                        select bp).Count();

            int tf = (from bb in _DataContext.Table<BallotBox>()
                        join b in _DataContext.Table<Batch>() on bb.BallotBoxId equals b.BallotBoxId
                        join bp in _DataContext.Table<BallotPaper>() on b.BatchId equals bp.BatchId
                        where bb.ElectionElectorateVacancyId == ElectionElectorateVacancyId && b.CountPhaseId == (int)round.CountPhase && bp.FormalFlag.Value
                        select bp).Count();

            return string.Format("BP={0},TF={1}", bps, tf);
        }

        public List<MBallotPaper> LoadBallotPapersWithFrequency(Guid electionElectorateVacancyId)
        {
            using (ComputerCountDataContext dc = new ComputerCountDataContext(ConnectionString))
            {
                dc.CommandTimeout = 600; 

                var uniquePaperPatternsWithFrequency = dc.usp_Vec_ComputerCount_LoadPapersWithFrequencyAndPattern(electionElectorateVacancyId).ToList();
                var bps = new List<MBallotPaper>();

                foreach (var bp in uniquePaperPatternsWithFrequency)
                {
                    var bpelist = new List<MBallotPaperEntry>();
                    int position = 1;

                    foreach (var x in bp.PreferencePattern.TrimStart(',').Split(','))
                    {
                        bpelist.Add(new MBallotPaperEntry
                        {
                            Mark = x == "-1" ? "" : x,
                            BpValue = x == "-1" ? null : (int?)int.Parse(x),
                            Candidate = new MCandidate { Position = position, Name = position.ToString() }
                        });
                        position++;
                    }

                    var mbp = new MBallotPaper
                    {
                        BallotPaperId = bp.BallotPaperId,
                        BallotPaperNbr = bp.BallotPaperNbr.Value,
                        BatchId = bp.BatchId,
                        CycleNo = 0,
                        Entries = bpelist,
                        FormalFlag = bp.FormalFlag.Value,
                        ExhaustAfter = bp.ExhaustAfter,
                        Status = (BallotPaperStatus)bp.BallotPaperStatusId.Value,
                        WeightedTransferValue = bp.WeightedTransferValue ?? 0,
                        Frequency = bp.Frequency
                    };

                    bps.Add(mbp);
                }

                return bps.ToList();
            }
        }

        public Vec.ComputerCount.Entities.Round GetLastRound(Guid electionElectorateVacancyId, CountPhase? countPhaseFilter = null)
        {
            var currentRound = GetLastDALRound(electionElectorateVacancyId, countPhaseFilter);
            if (currentRound != null)
            {
                return new Vec.ComputerCount.Entities.Round
                {
                    ElectionElectorateVacancyId = electionElectorateVacancyId,
                    CountPhase = (CountPhase)currentRound.CountPhaseId,
                    CountNumber = currentRound.CountNumber,
                    ComputerCountSource = (currentRound.ComputerCountSourceCountTypeId.HasValue) ? (Common.Entities.CountType?)currentRound.ComputerCountSourceCountTypeId : null
                };
            }

            return new Vec.ComputerCount.Entities.Round
            {
                ElectionElectorateVacancyId = electionElectorateVacancyId,
                CountPhase = CountPhase.Initial,
                CountNumber = 1,
                ComputerCountSource = GetComputerCountSourceCountParameter(electionElectorateVacancyId)
            };
        }

        private Round GetLastDALRound(Guid electionElectorateVacancyId, CountPhase? countPhaseFilter)
        {
            var rounds = _DataContext.Table<Round>()
                                     .Where(x => x.ElectionElectorateVacancyId == electionElectorateVacancyId)
                                     .ToArray();

            var currentRound = rounds.Where(x => countPhaseFilter.HasValue == false || x.CountPhaseId == (int)countPhaseFilter)
                                      .OrderBy(x => x.CountPhaseId)
                                      .ThenBy(x => x.CountNumber)
                                      .LastOrDefault();

            return currentRound;
        }

        public List<MBallotPaper> LoadBallotPapers()
        {
            var round = GetLastRound(ElectionElectorateVacancyId);

            var bps = from bb in _DataContext.Table<BallotBox>()
                      join b in _DataContext.Table<Batch>() on bb.BallotBoxId equals b.BallotBoxId
                      join bp in _DataContext.Table<BallotPaper>() on b.BatchId equals bp.BatchId
                      where bb.ElectionElectorateVacancyId == ElectionElectorateVacancyId && b.CountPhaseId == (int)round.CountPhase
                      select new MBallotPaper
                      {
                          BallotPaperId = bp.BallotPaperId,
                          BallotPaperNbr = bp.BallotPaperNbr.Value,
                          BatchId = bp.BatchId,
                          CycleNo = 0,
                          Entries = (from e in _DataContext.Table<BallotPaperEntry>()
                                     where e.BallotPaperId == bp.BallotPaperId
                                     select new MBallotPaperEntry
                                     {
                                         BpValue = e.BpValue,
                                         Mark = e.Mark,
                                         Candidate = new MCandidate
                                         {
                                             Position = e.CandidatePosition,
                                             Name = string.Empty
                                         }
                                     }).ToList(),
                          FormalFlag = bp.FormalFlag.Value,
                          ExhaustAfter = bp.ExhaustAfter,
                          Status = (BallotPaperStatus)bp.BallotPaperStatusId.Value,
                          WeightedTransferValue = bp.WeightedTransferValue ?? 0
                      };

            return bps.ToList();
        }

        public List<MAllocatedTicket> GetAllocatedTickets()
        {
            List<MAllocatedTicket> result = new List<MAllocatedTicket>();

            var currentRound = GetLastRound(ElectionElectorateVacancyId);

            result.AddRange(from at in _DataContext.Table<AllocatedTicket>()
                            where at.ElectionElectorateVacancyId == ElectionElectorateVacancyId && at.CountPhaseId == (int)currentRound.CountPhase
                            orderby at.GroupId, at.TicketNumber
                            select new MAllocatedTicket
                            {
                                AllocatedTicketId = at.AllocatedTicketId,
                                GroupId = at.GroupId,
                                GroupName = at.GroupName,
                                TicketNumber = at.TicketNumber,
                                Votes = at.Votes,
                                BatchNbr = at.BatchNbr,
                                TimeStamp = at.Timestamp.ToArray(),

                                Preferences = (from tp in _DataContext.Table<TicketPreference>()
                                               where tp.ElectionElectorateVacancyId == ElectionElectorateVacancyId
                                               && tp.AllocatedTicketId == at.AllocatedTicketId
                                               orderby tp.Order
                                               select new MTicketPreference
                                               {
                                                   TicketPreferenceId = tp.TicketPreferenceId,
                                                   Order = tp.Order,
                                                   Value = tp.Value,
                                                   TimeStamp = tp.Timestamp.ToArray()
                                               }).ToList()
                            });

            return result;
        }

        public bool AllElectionElectorateVacanciesHaveCountParameterWithValue(IEnumerable<Guid> electionElectorateVacancyIds, string countParameterName, string value)
        {
            var countParametersWithGivenValue =
                _DataContext.Table<CountParameter>()
                            .Where(x => electionElectorateVacancyIds.Contains(x.ElectionElectorateVacancyId))
                            .Where(x => x.Name == countParameterName)
                            .ToArray()
                            .Where(x => value.Equals(x.Value, StringComparison.OrdinalIgnoreCase))
                            .ToArray();

            var result = (countParametersWithGivenValue.Length == electionElectorateVacancyIds.Count());

            return result;
        }

        public InformalDataEntryType GetInformalDataEntryType(Guid electionElectorateVacancyId)
        {
            var informalDataEntryType =
                (AllElectionElectorateVacanciesHaveCountParameterWithValue(new[] { electionElectorateVacancyId }, Consts.PARAM_AllowInformalAdjustments, "true"))
                    ? InformalDataEntryType.Adjusted : InformalDataEntryType.Absolute;

            return informalDataEntryType;
        }

        public GetInformalBatchesSummaryResponse.ManualResultsEntity GetComputerCountSourceInformal(Guid electionElectorateVacancyId, IEnumerable<int> ballotBoxIds)
        {
            var result = new GetInformalBatchesSummaryResponse.ManualResultsEntity();
            var manualResultsComputerCountSource = GetManualResultsComputerCountSource(electionElectorateVacancyId, effective: true);
            result.CountType = manualResultsComputerCountSource;

            if (manualResultsComputerCountSource == Common.Entities.CountType.Unknown)
            {
                return result;
            }

            var ballotBoxResultsQuery =
                from bb in _DataContext.Table<BallotBox>()
                join bbr in _DataContext.Table<BallotBoxResult>() on bb.BallotBoxId equals bbr.BallotBoxId
                where bbr.CountTypeId == (int)manualResultsComputerCountSource
                where bb.ElectionElectorateVacancyId == electionElectorateVacancyId
                select new
                {
                    bb.BallotBoxId,
                    bbr.BallotBoxResultId,
                    bbr.InformalVotes,
                };

            if (ballotBoxIds != null)
            {
                ballotBoxResultsQuery = ballotBoxResultsQuery.Where(x => ballotBoxIds.Contains(x.BallotBoxId));
            }

            var ballotBoxResults = ballotBoxResultsQuery.ToList();

            result.BallotBoxResults =
                (from bbr in ballotBoxResults
                 select new GetInformalBatchesSummaryResponse.BallotBoxResultEntity
                 {
                     BallotBoxId = bbr.BallotBoxId,
                     BallotBoxResultId = bbr.BallotBoxResultId,
                     InformalVotes = bbr.InformalVotes
                 }).ToList();

            return result;
        }

        public Common.Entities.CountType GetManualResultsComputerCountSource(Guid electionElectorateVacancyId, bool effective)
        {
            var currentRound = GetLastRound(electionElectorateVacancyId);
            if (currentRound.CountPhase == CountPhase.Initial || effective == false)
            {
                var computerCountSourceCountParameter = GetComputerCountSourceCountParameter(electionElectorateVacancyId);

                return computerCountSourceCountParameter;
            }

            if (currentRound.CountPhase == CountPhase.Recount)
            {
                var initialRound = GetLastRound(electionElectorateVacancyId, CountPhase.Initial);
                if (initialRound == null || initialRound.ComputerCountSource.HasValue == false || initialRound.ComputerCountSource.Value == Common.Entities.CountType.Unknown)
                {
                    return Common.Entities.CountType.Unknown;
                }

                return initialRound.ComputerCountSource.Value;
            }

            return Common.Entities.CountType.Unknown;
        }

        private Common.Entities.CountType GetComputerCountSourceCountParameter(Guid electionElectorateVacancyId)
        {
            var computerCountType = (from p in _DataContext.Table<CountParameter>()
                                     where p.Name == Consts.PARAM_ComputerCountSource
                                           && p.ElectionElectorateVacancyId == electionElectorateVacancyId
                                     select p.Value).FirstOrDefault();

            var countType = Vec.Common.Entities.CountType.Unknown;

            if (computerCountType != null)
                Enum.TryParse(computerCountType, out countType);

            return countType;
        }


        public int GetInformalTotalVotes(Guid electionElectorateVacancyId)
        {
            var informalDataEntryType = GetInformalDataEntryType(electionElectorateVacancyId);
            var currentRound = GetLastRound(electionElectorateVacancyId);

            var result = (from b in _DataContext.Table<BallotBox>()
                          join bbi in _DataContext.Table<BallotBoxInformal>()
                          on b.BallotBoxId equals bbi.BallotBoxId
                          where b.ElectionElectorateVacancyId == ElectionElectorateVacancyId
                          where
                          (
                               (informalDataEntryType == InformalDataEntryType.Absolute && (CountPhase)bbi.CountPhaseId == currentRound.CountPhase) ||
                               (informalDataEntryType == InformalDataEntryType.Adjusted)
                          )
                          select new { BallotBoxId = bbi.BallotBoxId, Total = bbi.Total })
                          .ToArray()
                          .GroupBy(x => x.BallotBoxId)
                          .ToDictionary(k => k.Key, v => v.Sum(x => x.Total));

            if (informalDataEntryType == InformalDataEntryType.Adjusted)
            {
                var computerCountSourceResults = GetComputerCountSourceInformal(ElectionElectorateVacancyId, null);
                if (computerCountSourceResults.CountType != Common.Entities.CountType.Unknown)
                {
                    foreach (var bbr in computerCountSourceResults.BallotBoxResults.Where(x => x.InformalVotes.HasValue))
                    {
                        if (result.ContainsKey(bbr.BallotBoxId))
                        {
                            result[bbr.BallotBoxId] += bbr.InformalVotes.Value;
                        }
                        else
                        {
                            result[bbr.BallotBoxId] = bbr.InformalVotes.Value;
                        }
                    }
                }
            }

            return result.Sum(x => x.Value);
        }
        
        protected MDistribution Translate(Distribution d)
        {
            MCandidate distCandidate = null;
            if (d.CandidatePosition != null)
            {
                distCandidate = new MCandidate { Name = string.Format("C{0}", (int)d.CandidatePosition), Position = (int)d.CandidatePosition };
            }

            MDistribution returndistribution = new MDistribution
            {
                Step = d.Step,
                DistributionType = (DistributionType)d.DistributionTypeId,
                Candidate = distCandidate,
                Transfers = new List<MTransfer>()
            };

            foreach (Transfer t in d.Transfers)
            {
                returndistribution.Transfers.Add(Translate(t));
            };

            return returndistribution;
        }

        protected CandidateAllocation Translate(MCandidateAllocation ca)
        {
            return new CandidateAllocation
            {
                CandidateAllocationId = Guid.NewGuid(),
                CandidateCountStatusId = (int)ca.CountStatus,
                CandidatePosition = ca.Candidate.Position,
                ElectorOrExcludeOrder = ca.ElectorOrExcludeOrder,
                BallotPapers = ca.BallotPapers,
                ProgressTotalBallotPapers = ca.TotalBallotPapers,
                ProgressTotalVotes = ca.TotalVotes,
                Votes = ca.Votes
            };
        }
        
        protected Transfer Translate(MTransfer t)
        {
            return new Transfer
            {
                TransferId = Guid.NewGuid(),
                CycleNumber = t.CycleNo,
                ExhaustedPapers = t.ExhaustedPapers,
                ExhaustedVotes = t.ExhaustedVotes,
                FromCycles = t.FromCycles,
                GainLoss = t.GainLoss,
                ProgressTotalExhaustedPapers = t.TotalExhaustedPapers,
                ProgressTotalExhaustedVotes = t.TotalExhaustedVotes,
                ProgressTotalGainLoss = t.TotalGainLoss,
                TotalBallotPapers = t.BallotPapersTotal,
                WeightedTransferValue = t.WeightedTransferValue
            };
        }

        protected MBatch Translate(Batch b)
        {
            return new MBatch
            {
                BatchId = b.BatchId,
                BallotBoxId = b.BallotBoxId,
                BatchNo = b.BatchNbr,
                Status = (BatchStatus)b.BatchStatusId,
                CountPhase = (CountPhase)b.CountPhaseId
            };
        }

        internal MBatch Translate(Batch b, Guid locationid, Guid? electionElectorateLocationId, VoteType votetype, int? voteTypeNo)
        {
            return new MBatch
            {
                BatchId = b.BatchId,
                BallotBoxId = b.BallotBoxId,
                BatchNo = b.BatchNbr,
                Status = (BatchStatus)b.BatchStatusId,
                VoteType = votetype,
                VoteTypeNo = voteTypeNo,
                LocationId = locationid,
                ElectionElectorateLocationId = electionElectorateLocationId,
                CountPhase = (CountPhase)b.CountPhaseId
            };
        }

        internal MBallotPaper Translate(BallotPaper bp)
        {
            return new MBallotPaper
            {
                BallotPaperId = bp.BallotPaperId,
                BatchId = bp.BatchId,
                CycleNo = bp.CycleNo ?? 0,
                ExhaustAfter = bp.ExhaustAfter ?? 0,
                WeightedTransferValue = bp.WeightedTransferValue ?? 0,
                Status = (BallotPaperStatus)bp.BallotPaperStatusId,
                BallotPaperNbr = bp.BallotPaperNbr ?? 0,
                FormalFlag = (bp.FormalFlag == null ? false : (bool)bp.FormalFlag)
            };
        }

        protected BallotPaper Translate(MBallotPaper mbp)
        {
            return new BallotPaper
            {
                BallotPaperId = mbp.BallotPaperId,
                BatchId = mbp.BatchId,
                CycleNo = mbp.CycleNo,
                ExhaustAfter = mbp.ExhaustAfter ?? 0,
                WeightedTransferValue = mbp.WeightedTransferValue,
                BallotPaperStatusId = (int)mbp.Status,
                BallotPaperNbr = mbp.BallotPaperNbr,
                FormalFlag = (bool)mbp.FormalFlag
            };
        }

        protected MBallotPaperEntry Translate(BallotPaperEntry bpe, List<MCandidate> candidates)
        {
            return new MBallotPaperEntry
            {
                BallotPaperEntryId = bpe.BallotPaperEntryId,
                BallotPaperId = bpe.BallotPaperId,
                Candidate = (from MCandidate c in candidates where c.Position == bpe.CandidatePosition select c).First(),
                Mark = bpe.Mark,
                BpValue = bpe.BpValue
            };
        }
    }
}