public async Task <IActionResult> PutUserSetAppraiser([FromRoute] int id, [FromBody] UserSetAppraiser userSetAppraiser)
        {
            if (!ModelState.IsValid)
            {
                return(BadRequest(ModelState));
            }

            if (id != userSetAppraiser.Id)
            {
                return(BadRequest());
            }

            _context.Entry(userSetAppraiser).State = EntityState.Modified;

            try
            {
                await _context.SaveChangesAsync();
            }
            catch (DbUpdateConcurrencyException)
            {
                if (!UserSetAppraiserExists(id))
                {
                    return(NotFound());
                }
                else
                {
                    throw;
                }
            }

            return(NoContent());
        }
        public async Task <IActionResult> ToExcel([FromBody] Excel excel)
        {
            IEnumerable <UserSet>          users         = _context.UserSet;
            IEnumerable <UserSetAppraiser> appraisers    = _context.UserSetAppraiser;
            List <UserSetAppraiser>        appraisersRes = new List <UserSetAppraiser>();
            UserSet          usr = new UserSet();
            UserSetAppraiser apr = new UserSetAppraiser();

            for (int i = 0; i < excel.Ids.Count(); i++)
            {
                usr = users.FirstOrDefault(u => u.Id == excel.Ids[i]);
                usr.UserSetAppraiser = null;
                apr = appraisers.FirstOrDefault(u => u.Id == excel.Ids[i]);
                apr.IdNavigation = usr;
                appraisersRes.Add(apr);
            }

            var fileDownloadName = "Оценщики.xlsx";

            using (var package = createExcelPackage(appraisersRes))
            {
                package.SaveAs(new FileInfo(Path.Combine(@"C:\Users\user\Downloads", fileDownloadName)));
            }
            return(Ok());
        }
        public async Task <IActionResult> PostUserSetAppraiser([FromBody] UserSetAppraiser userSetAppraiser)
        {
            if (!ModelState.IsValid)
            {
                return(BadRequest(ModelState));
            }

            _context.UserSetAppraiser.Add(userSetAppraiser);
            try
            {
                await _context.SaveChangesAsync();
            }
            catch (DbUpdateException)
            {
                if (UserSetAppraiserExists(userSetAppraiser.Id))
                {
                    return(new StatusCodeResult(StatusCodes.Status409Conflict));
                }
                else
                {
                    throw;
                }
            }

            return(CreatedAtAction("GetUserSetAppraiser", new { id = userSetAppraiser.Id }, userSetAppraiser));
        }
        private string getApprFio(UserSetAppraiser apr)
        {
            string res = "";

            res = apr.IdNavigation.Surname + " " + apr.IdNavigation.Name.Substring(0, 1).ToUpper() + ". " + apr.IdNavigation.Patronymic.Substring(0, 1).ToUpper() + ".";

            return(res);
        }
        public IEnumerable <UserSetAppraiser> GetUserSetAppraiser()
        {
            IEnumerable <UserSet>          users      = _context.UserSet;
            IEnumerable <UserSetAppraiser> appraisers = _context.UserSetAppraiser;
            UserSet          usr = new UserSet();
            UserSetAppraiser apr = new UserSetAppraiser();

            for (int i = 0; i < appraisers.Count(); i++)
            {
                usr = users.FirstOrDefault(u => u.Id == appraisers.ElementAt(i).Id);
                usr.UserSetAppraiser = null;
                appraisers.ElementAt(i).IdNavigation = usr;
            }

            return(appraisers);
        }
        public async Task <IActionResult> GetSalary([FromRoute] int month)
        {
            if (!ModelState.IsValid)
            {
                return(BadRequest(ModelState));
            }

            List <SalaryClass>              salResult      = new List <SalaryClass>();
            IEnumerable <ContractSet>       contracts      = _context.ContractSet;
            IEnumerable <AppraiserContract> contrAppraiser = _context.AppraiserContract;
            IEnumerable <UserSetAppraiser>  appraisers     = _context.UserSetAppraiser;
            IEnumerable <UserSet>           users          = _context.UserSet;
            IEnumerable <SalarySettingsSet> settings       = _context.SalarySettingsSet;
            IEnumerable <ObjectSetFlat>     flats          = _context.ObjectSetFlat;
            IEnumerable <ObjectSetCar>      cars           = _context.ObjectSetCar;
            IEnumerable <ObjectSetParcel>   parcels        = _context.ObjectSetParcel;
            SalaryClass       sal    = new SalaryClass();
            ContractSet       cntr   = new ContractSet();
            AppraiserContract conApp = new AppraiserContract();
            UserSetAppraiser  appr   = new UserSetAppraiser();
            UserSet           usr    = new UserSet();
            ObjectSetFlat     flt    = new ObjectSetFlat();
            ObjectSetCar      car    = new ObjectSetCar();
            ObjectSetParcel   prc    = new ObjectSetParcel();

            DateTime currDate    = new DateTime();
            DateTime monthStart  = new DateTime();
            DateTime monthFinish = new DateTime();

            currDate = DateTime.Now;

            if (month != 0)
            {
                month       = month * (-1);
                currDate    = currDate.AddMonths(month);
                monthFinish = getLastDay(currDate);
            }
            else
            {
                monthFinish = currDate;
            }

            monthStart = new DateTime(currDate.Year, currDate.Month, 1);

            contracts = contracts.Where(cn => DateTime.Compare(cn.FinishDate, monthStart) >= 0 && DateTime.Compare(cn.FinishDate, monthFinish) <= 0);

            for (int i = 0; i < contracts.Count(); i++)
            {
                sal    = null;
                conApp = null;
                appr   = null;
                usr    = null;

                conApp = contrAppraiser.FirstOrDefault(c => c.ContractId == contracts.ElementAt(i).Id);
                appr   = appraisers.FirstOrDefault(a => a.Id == conApp.AppraiserId);
                usr    = users.FirstOrDefault(u => u.Id == appr.Id);

                flt = flats.FirstOrDefault(u => u.Id == contracts.ElementAt(i).ObjectId);
                car = cars.FirstOrDefault(u => u.Id == contracts.ElementAt(i).ObjectId);
                prc = parcels.FirstOrDefault(u => u.Id == contracts.ElementAt(i).ObjectId);

                sal = salResult.FirstOrDefault(s => s.Surname == usr.Surname);

                if (sal == null)
                {
                    sal = new SalaryClass();
                    if (flt != null)
                    {
                        sal.ContractsFlat.Add(contracts.ElementAt(i));
                    }
                    else
                    {
                        if (prc != null)
                        {
                            sal.ContractsParcel.Add(contracts.ElementAt(i));
                        }
                        else
                        {
                            sal.ContractsCar.Add(contracts.ElementAt(i));
                        }
                    }
                    sal.Surname    = usr.Surname;
                    sal.Name       = usr.Name;
                    sal.Patronymic = usr.Patronymic;
                    sal.Month      = month;

                    contracts.ElementAt(i).Client            = null;
                    contracts.ElementAt(i).AppraiserContract = null;
                    contracts.ElementAt(i).Object            = null;

                    salResult.Add(sal);
                }
                else
                {
                    salResult.Remove(sal);
                    if (flt != null)
                    {
                        contracts.ElementAt(i).Client            = null;
                        contracts.ElementAt(i).AppraiserContract = null;
                        contracts.ElementAt(i).Object            = null;
                        sal.ContractsFlat.Add(contracts.ElementAt(i));
                    }
                    else
                    {
                        if (prc != null)
                        {
                            contracts.ElementAt(i).Client            = null;
                            contracts.ElementAt(i).AppraiserContract = null;
                            contracts.ElementAt(i).Object            = null;
                            sal.ContractsParcel.Add(contracts.ElementAt(i));
                        }
                        else
                        {
                            contracts.ElementAt(i).Client            = null;
                            contracts.ElementAt(i).AppraiserContract = null;
                            contracts.ElementAt(i).Object            = null;
                            sal.ContractsCar.Add(contracts.ElementAt(i));
                        }
                    }
                    salResult.Add(sal);
                }
            }

            for (int i = 0; i < salResult.Count(); i++)
            {
                salResult.ElementAt(i).ContractsCount = salResult.ElementAt(i).ContractsCar.Count + salResult.ElementAt(i).ContractsFlat.Count + salResult.ElementAt(i).ContractsParcel.Count;

                foreach (ContractSet contr in salResult.ElementAt(i).ContractsFlat)
                {
                    salResult.ElementAt(i).Salary += contr.ContractSumm / 100 * settings.First().FlatPercent;
                }

                foreach (ContractSet contr in salResult.ElementAt(i).ContractsCar)
                {
                    salResult.ElementAt(i).Salary += contr.ContractSumm / 100 * settings.First().CarPercent;
                }

                foreach (ContractSet contr in salResult.ElementAt(i).ContractsParcel)
                {
                    salResult.ElementAt(i).Salary += contr.ContractSumm / 100 * settings.First().ParcelPercent;
                }

                salResult.ElementAt(i).Salary = Math.Round(salResult.ElementAt(i).Salary, MidpointRounding.ToEven);
            }

            if (salResult == null)
            {
                return(NotFound());
            }

            return(Ok(salResult));
        }
        public async Task <IActionResult> ToExcel([FromRoute] int month)
        {
            if (!ModelState.IsValid)
            {
                return(BadRequest(ModelState));
            }

            List <SalaryClass>              salResult      = new List <SalaryClass>();
            IEnumerable <ContractSet>       contracts      = _context.ContractSet;
            IEnumerable <AppraiserContract> contrAppraiser = _context.AppraiserContract;
            IEnumerable <UserSetAppraiser>  appraisers     = _context.UserSetAppraiser;
            IEnumerable <UserSet>           users          = _context.UserSet;
            IEnumerable <SalarySettingsSet> settings       = _context.SalarySettingsSet;
            IEnumerable <ObjectSetFlat>     flats          = _context.ObjectSetFlat;
            IEnumerable <ObjectSetCar>      cars           = _context.ObjectSetCar;
            IEnumerable <ObjectSetParcel>   parcels        = _context.ObjectSetParcel;
            SalaryClass       sal    = new SalaryClass();
            ContractSet       cntr   = new ContractSet();
            AppraiserContract conApp = new AppraiserContract();
            UserSetAppraiser  appr   = new UserSetAppraiser();
            UserSet           usr    = new UserSet();
            ObjectSetFlat     flt    = new ObjectSetFlat();
            ObjectSetCar      car    = new ObjectSetCar();
            ObjectSetParcel   prc    = new ObjectSetParcel();

            DateTime currDate    = new DateTime();
            DateTime monthStart  = new DateTime();
            DateTime monthFinish = new DateTime();

            currDate = DateTime.Now;

            if (month != 0)
            {
                month       = month * (-1);
                currDate    = currDate.AddMonths(month);
                monthFinish = getLastDay(currDate);
            }
            else
            {
                monthFinish = currDate;
            }

            monthStart = new DateTime(currDate.Year, currDate.Month, 1);

            contracts = contracts.Where(cn => DateTime.Compare(cn.FinishDate, monthStart) >= 0 && DateTime.Compare(cn.FinishDate, monthFinish) <= 0);

            for (int i = 0; i < contracts.Count(); i++)
            {
                sal    = null;
                conApp = null;
                appr   = null;
                usr    = null;

                conApp = contrAppraiser.FirstOrDefault(c => c.ContractId == contracts.ElementAt(i).Id);
                appr   = appraisers.FirstOrDefault(a => a.Id == conApp.AppraiserId);
                usr    = users.FirstOrDefault(u => u.Id == appr.Id);

                flt = flats.FirstOrDefault(u => u.Id == contracts.ElementAt(i).ObjectId);
                car = cars.FirstOrDefault(u => u.Id == contracts.ElementAt(i).ObjectId);
                prc = parcels.FirstOrDefault(u => u.Id == contracts.ElementAt(i).ObjectId);

                sal = salResult.FirstOrDefault(s => s.Surname == usr.Surname);

                if (sal == null)
                {
                    sal = new SalaryClass();
                    if (flt != null)
                    {
                        sal.ContractsFlat.Add(contracts.ElementAt(i));
                    }
                    else
                    {
                        if (prc != null)
                        {
                            sal.ContractsParcel.Add(contracts.ElementAt(i));
                        }
                        else
                        {
                            sal.ContractsCar.Add(contracts.ElementAt(i));
                        }
                    }
                    sal.Surname    = usr.Surname;
                    sal.Name       = usr.Name;
                    sal.Patronymic = usr.Patronymic;
                    sal.Month      = month;

                    contracts.ElementAt(i).Client            = null;
                    contracts.ElementAt(i).AppraiserContract = null;
                    contracts.ElementAt(i).Object            = null;

                    salResult.Add(sal);
                }
                else
                {
                    salResult.Remove(sal);
                    if (flt != null)
                    {
                        contracts.ElementAt(i).Client            = null;
                        contracts.ElementAt(i).AppraiserContract = null;
                        contracts.ElementAt(i).Object            = null;
                        sal.ContractsFlat.Add(contracts.ElementAt(i));
                    }
                    else
                    {
                        if (prc != null)
                        {
                            contracts.ElementAt(i).Client            = null;
                            contracts.ElementAt(i).AppraiserContract = null;
                            contracts.ElementAt(i).Object            = null;
                            sal.ContractsParcel.Add(contracts.ElementAt(i));
                        }
                        else
                        {
                            contracts.ElementAt(i).Client            = null;
                            contracts.ElementAt(i).AppraiserContract = null;
                            contracts.ElementAt(i).Object            = null;
                            sal.ContractsCar.Add(contracts.ElementAt(i));
                        }
                    }
                    salResult.Add(sal);
                }
            }

            for (int i = 0; i < salResult.Count(); i++)
            {
                salResult.ElementAt(i).ContractsCount = salResult.ElementAt(i).ContractsCar.Count + salResult.ElementAt(i).ContractsFlat.Count + salResult.ElementAt(i).ContractsParcel.Count;

                foreach (ContractSet contr in salResult.ElementAt(i).ContractsFlat)
                {
                    salResult.ElementAt(i).Salary += contr.ContractSumm / 100 * settings.First().FlatPercent;
                }

                foreach (ContractSet contr in salResult.ElementAt(i).ContractsCar)
                {
                    salResult.ElementAt(i).Salary += contr.ContractSumm / 100 * settings.First().CarPercent;
                }

                foreach (ContractSet contr in salResult.ElementAt(i).ContractsParcel)
                {
                    salResult.ElementAt(i).Salary += contr.ContractSumm / 100 * settings.First().ParcelPercent;
                }
            }

            string[] monthArr = new string[] { "Январь", "Февраль", "Март", "Апрель", "Май", "Июнь", "Июль", "Август", "Сентябрь", "Октябрь", "Ноябрь", "Декабрь" };

            var fileDownloadName = "Зарплата - " + monthArr[monthStart.Month - 1] + ".xlsx";

            using (var package = createExcelPackage(salResult))
            {
                package.SaveAs(new FileInfo(Path.Combine(@"C:\Users\user\Downloads", fileDownloadName)));
            }
            return(Ok());
        }
        public IEnumerable <ContractSet> GetContractSet()
        {
            IEnumerable <ContractSet>         contracts         = _context.ContractSet;
            IEnumerable <ObjectSet>           objects           = _context.ObjectSet;
            IEnumerable <ObjectSetFlat>       flats             = _context.ObjectSetFlat;
            IEnumerable <ObjectSetCar>        cars              = _context.ObjectSetCar;
            IEnumerable <ObjectSetParcel>     parcels           = _context.ObjectSetParcel;
            IEnumerable <ClientSet>           clients           = _context.ClientSet;
            IEnumerable <ClientSetIndividual> clientsIndiv      = _context.ClientSetIndividual;
            IEnumerable <ClientSetEntity>     clientsEnt        = _context.ClientSetEntity;
            IEnumerable <UserSetAppraiser>    apraisers         = _context.UserSetAppraiser;
            IEnumerable <AppraiserContract>   apraisersContract = _context.AppraiserContract;
            IEnumerable <UserSet>             users             = _context.UserSet;
            IEnumerable <AddressSet>          addresses         = _context.AddressSet;
            ContractSet         cntr    = new ContractSet();
            ClientSet           clnt    = new ClientSet();
            ClientSetIndividual indv    = new ClientSetIndividual();
            ClientSetEntity     ent     = new ClientSetEntity();
            ObjectSet           obj     = new ObjectSet();
            ObjectSetFlat       flt     = new ObjectSetFlat();
            ObjectSetCar        cr      = new ObjectSetCar();
            ObjectSetParcel     prcl    = new ObjectSetParcel();
            UserSetAppraiser    apr     = new UserSetAppraiser();
            AppraiserContract   aprCntr = new AppraiserContract();
            UserSet             usr     = new UserSet();
            AddressSet          adr     = new AddressSet();

            for (int i = 0; i < contracts.Count(); i++)
            {
                clnt             = clients.FirstOrDefault(u => u.Id == contracts.ElementAt(i).ClientId);
                clnt.ContractSet = null;
                indv             = clientsIndiv.FirstOrDefault(u => u.Id == clnt.Id);
                ent = clientsEnt.FirstOrDefault(u => u.Id == clnt.Id);
                if (indv != null)
                {
                    indv.IdNavigation = null;
                    adr = addresses.FirstOrDefault(u => u.Id == indv.AddressOfResidenceId);
                    adr.ClientSetIndividual  = null;
                    indv.AddressOfResidence  = adr;
                    clnt.ClientSetIndividual = indv;
                }
                else
                {
                    ent.IdNavigation = null;
                    adr = addresses.FirstOrDefault(u => u.Id == ent.LegalAddressId);
                    adr.ClientSetEntity  = null;
                    ent.LegalAddress     = adr;
                    clnt.ClientSetEntity = ent;
                }
                contracts.ElementAt(i).Client = clnt;

                obj             = objects.FirstOrDefault(u => u.Id == contracts.ElementAt(i).ObjectId);
                obj.ContractSet = null;
                flt             = flats.FirstOrDefault(u => u.Id == obj.Id);
                cr   = cars.FirstOrDefault(u => u.Id == obj.Id);
                prcl = parcels.FirstOrDefault(u => u.Id == obj.Id);
                if (flt != null)
                {
                    flt.IdNavigation = null;
                    adr = addresses.FirstOrDefault(u => u.Id == flt.AddressId);
                    adr.ObjectSetFlat = null;
                    flt.Address       = adr;
                    obj.ObjectSetFlat = flt;
                }
                else
                {
                    if (prcl != null)
                    {
                        prcl.IdNavigation   = null;
                        obj.ObjectSetParcel = prcl;
                    }
                    else
                    {
                        cr.IdNavigation  = null;
                        obj.ObjectSetCar = cr;
                    }
                }
                contracts.ElementAt(i).Object = obj;

                aprCntr = apraisersContract.FirstOrDefault(u => u.ContractId == contracts.ElementAt(i).Id);
                apr     = apraisers.FirstOrDefault(u => u.Id == aprCntr.AppraiserId);
                apr.AppraiserContract = null;
                usr = users.FirstOrDefault(u => u.Id == apr.Id);
                usr.UserSetAppraiser = null;
                apr.IdNavigation     = usr;
                aprCntr.Appraiser    = apr;
                aprCntr.Contract     = null;

                AppraiserContract[] arr = new AppraiserContract[1];
                arr[0] = aprCntr;
                contracts.ElementAt(i).AppraiserContract = arr;
            }

            return(contracts);
        }
        public async Task <IActionResult> ToExcel([FromBody] Excel excel)
        {
            List <ContractSet>                contractsRes      = new List <ContractSet>();
            IEnumerable <ContractSet>         contracts         = _context.ContractSet;
            IEnumerable <ObjectSet>           objects           = _context.ObjectSet;
            IEnumerable <ObjectSetFlat>       flats             = _context.ObjectSetFlat;
            IEnumerable <ObjectSetCar>        cars              = _context.ObjectSetCar;
            IEnumerable <ObjectSetParcel>     parcels           = _context.ObjectSetParcel;
            IEnumerable <ClientSet>           clients           = _context.ClientSet;
            IEnumerable <ClientSetIndividual> clientsIndiv      = _context.ClientSetIndividual;
            IEnumerable <ClientSetEntity>     clientsEnt        = _context.ClientSetEntity;
            IEnumerable <UserSetAppraiser>    apraisers         = _context.UserSetAppraiser;
            IEnumerable <AppraiserContract>   apraisersContract = _context.AppraiserContract;
            IEnumerable <UserSet>             users             = _context.UserSet;
            IEnumerable <AddressSet>          addresses         = _context.AddressSet;
            ContractSet         cntr    = new ContractSet();
            ClientSet           clnt    = new ClientSet();
            ClientSetIndividual indv    = new ClientSetIndividual();
            ClientSetEntity     ent     = new ClientSetEntity();
            ObjectSet           obj     = new ObjectSet();
            ObjectSetFlat       flt     = new ObjectSetFlat();
            ObjectSetCar        cr      = new ObjectSetCar();
            ObjectSetParcel     prcl    = new ObjectSetParcel();
            UserSetAppraiser    apr     = new UserSetAppraiser();
            AppraiserContract   aprCntr = new AppraiserContract();
            UserSet             usr     = new UserSet();
            AddressSet          adr     = new AddressSet();

            for (int i = 0; i < excel.Ids.Count(); i++)
            {
                cntr = contracts.FirstOrDefault(u => u.Id == excel.Ids[i]);

                clnt             = clients.FirstOrDefault(u => u.Id == cntr.ClientId);
                clnt.ContractSet = null;
                indv             = clientsIndiv.FirstOrDefault(u => u.Id == clnt.Id);
                ent = clientsEnt.FirstOrDefault(u => u.Id == clnt.Id);
                if (indv != null)
                {
                    indv.IdNavigation = null;
                    adr = addresses.FirstOrDefault(u => u.Id == indv.AddressOfResidenceId);
                    adr.ClientSetIndividual  = null;
                    indv.AddressOfResidence  = adr;
                    clnt.ClientSetIndividual = indv;
                }
                else
                {
                    ent.IdNavigation = null;
                    adr = addresses.FirstOrDefault(u => u.Id == ent.LegalAddressId);
                    adr.ClientSetEntity  = null;
                    ent.LegalAddress     = adr;
                    clnt.ClientSetEntity = ent;
                }
                cntr.Client = clnt;

                obj             = objects.FirstOrDefault(u => u.Id == cntr.ObjectId);
                obj.ContractSet = null;
                flt             = flats.FirstOrDefault(u => u.Id == obj.Id);
                cr   = cars.FirstOrDefault(u => u.Id == obj.Id);
                prcl = parcels.FirstOrDefault(u => u.Id == obj.Id);
                if (flt != null)
                {
                    flt.IdNavigation = null;
                    adr = addresses.FirstOrDefault(u => u.Id == flt.AddressId);
                    adr.ObjectSetFlat = null;
                    flt.Address       = adr;
                    obj.ObjectSetFlat = flt;
                }
                else
                {
                    if (prcl != null)
                    {
                        prcl.IdNavigation   = null;
                        obj.ObjectSetParcel = prcl;
                    }
                    else
                    {
                        cr.IdNavigation  = null;
                        obj.ObjectSetCar = cr;
                    }
                }
                cntr.Object = obj;

                aprCntr = apraisersContract.FirstOrDefault(u => u.ContractId == cntr.Id);
                apr     = apraisers.FirstOrDefault(u => u.Id == aprCntr.AppraiserId);
                apr.AppraiserContract = null;
                usr = users.FirstOrDefault(u => u.Id == apr.Id);
                usr.UserSetAppraiser = null;
                apr.IdNavigation     = usr;
                aprCntr.Appraiser    = apr;
                aprCntr.Contract     = null;

                AppraiserContract[] arr = new AppraiserContract[1];
                arr[0] = aprCntr;
                cntr.AppraiserContract = arr;

                contractsRes.Add(cntr);
            }

            var fileDownloadName = "Договоры.xlsx";

            using (var package = createExcelPackage(contractsRes))
            {
                package.SaveAs(new FileInfo(Path.Combine(@"C:\Users\user\Downloads", fileDownloadName)));
            }
            return(Ok());
        }