< Summary

Class:WinSolutions.Sveta.Server.Services.Implements.ReportService
Assembly:SVETA.Api
File(s):/opt/dev/sveta_api_build/SVETA.Api/Services/Implements/ReportService.cs
Covered lines:78
Uncovered lines:325
Coverable lines:403
Total lines:710
Line coverage:19.3% (78 of 403)
Covered branches:29
Total branches:414
Branch coverage:7% (29 of 414)

Metrics

MethodLine coverage Branch coverage
.ctor(...)100%100%
GoodAccountingReport()93.75%65.38%
GoodAccountingReportWithoutPagination()0%0%
EveryDayReport()0%0%
GetMatrixReport(...)0%0%

File(s)

/opt/dev/sveta_api_build/SVETA.Api/Services/Implements/ReportService.cs

#LineLine coverage
 1using System;
 2using System.Collections.Generic;
 3using System.ComponentModel.Design;
 4using System.Linq;
 5using System.Linq.Expressions;
 6using System.Threading.Tasks;
 7using DocumentFormat.OpenXml.Drawing.Diagrams;
 8using DocumentFormat.OpenXml.Office2010.Excel;
 9using Microsoft.EntityFrameworkCore;
 10using Microsoft.EntityFrameworkCore.Internal;
 11using Microsoft.Extensions.DependencyInjection;
 12using Microsoft.Extensions.Logging;
 13using Newtonsoft.Json;
 14using Newtonsoft.Json.Converters;
 15using SVETA.Api.Data.DTO.Movements;
 16using SVETA.Api.Data.DTO.Reports;
 17using SVETA.Api.Helpers;
 18using SVETA.Api.Services.Implements;
 19using WinSolutions.Sveta.Common;
 20using WinSolutions.Sveta.Server.Data.DataModel.Contexts;
 21using WinSolutions.Sveta.Server.Data.DataModel.Entities;
 22using WinSolutions.Sveta.Server.Data.DataModel.Kinds;
 23using WinSolutions.Sveta.Server.Data.DataModel.Reports;
 24using WinSolutions.Sveta.Server.Domain;
 25using WinSolutions.Sveta.Server.Services.Interfaces;
 26
 27namespace WinSolutions.Sveta.Server.Services.Implements
 28{
 29    public class ReportService: SvetaServiceBase, IReportService
 30    {
 31        private readonly ILogger<ReportService> _logger;
 32        private readonly SvetaDbContext _db;
 33        private readonly IGoodService _goodService;
 34        private readonly IAuthenticationService _authenticationService;
 35        private readonly IWalletPaymentService _walletPaymentService;
 36        public ReportService(IAuthenticationService authenticationService,
 37            ILogger<ReportService> logger,
 38            SvetaDbContext db,
 39            IWalletPaymentService walletPaymentService,
 40            IGoodService goodService
 341        ) : base(authenticationService)
 342        {
 343            _authenticationService = authenticationService;
 344            _walletPaymentService = walletPaymentService;
 345            _logger = logger;
 346            _db = db;
 347            _goodService = goodService;
 348        }
 49
 50        /// <summary>
 51        /// Возвращает товарный отчет
 52        /// </summary>
 53        /// <param name="contragentId">Склад для построения отчета</param>
 54        /// <param name="categoryId">категория товара - 0 для всех</param>
 55        /// <param name="dateFrom">Дата начала отбора - для всех по умолчанию</param>
 56        /// <param name="dateTo">Дата окончания отбора - для всех по умолчанию</param>
 57        /// <param name="sort">Сортировка - по умолочанию goodname, goodname|desc, category, category|desc, sum, sum|des
 58        /// <param name="filter">Фильтр по наименованию товара - по умолчанию для всех</param>
 59        /// <param name="page">Страница отбора - 0 по умолчанию</param>
 60        /// <param name="limit">Лимит отбора - 10 по умолчанию</param>
 61        /// <returns>Task&lt;PaginatedData&lt;List&lt;GoodAccount&gt;&gt;&gt;</returns>
 62        public async Task<PaginatedData<List<GoodAccount>>> GoodAccountingReport(long contragentId, long categoryId = 0,
 63            DateTime dateTo = default, string sort = default, string filter = default, int page = 0, int limit = 10)
 164        {
 165            limit = limit < 1 ? 10 : limit;
 166            var goodsList = _db.Goods
 167                .Include(good => good.Category)
 168                .Include(good => good.Prices)
 169                .ThenInclude(prices => prices.PriceTrend)
 170                .ThenInclude(trend => trend.SupplierDepartment)
 171                .ThenInclude(suplierDep => suplierDep.Contragent)
 172                .AsQueryable();
 173            List<long> cats = new List<long>();
 174            if (categoryId != 0)
 075            {
 076                cats = _goodService.ExpandCategoryTree(new List<long>() {categoryId});
 077                cats.Add(categoryId);
 078            }
 79
 180            var totalCount = goodsList.Count();
 81
 182            var filtered = goodsList.Where(good =>
 183                    !good.IsDeleted
 184                    && good.Prices.Count > 0 && good.Prices.Any(price =>
 185                        price.PriceTrend.SupplierDepartment.Contragent.Id == contragentId))
 186                .Where(good => categoryId == 0 || cats.Contains(good.Category.Id))
 187                .Where(good => string.IsNullOrEmpty(filter) || good.Name.ToLower().Contains(filter.ToLower().Trim()))
 188                .Select(good => new {Id = good.Id, Name = good.Name, CatName = good.Category.Name})
 189                .AsEnumerable();
 90
 191            var movementList = _db.MovementStatusJournals
 192                .Include(journal => journal.Movement)
 193                .ThenInclude(movement => movement.Items)
 194                .ThenInclude(item => item.Good)
 195                .Include(journal => journal.Movement)
 196                .ThenInclude(movement => movement.Sender)
 197                .Include(journal => journal.Movement)
 198                .ThenInclude(movement => movement.Customer)
 199                .Where(d => !d.Movement.IsDeleted)
 1100                .Where(d => dateFrom == default || d.Movement.CreationDateTime >= dateFrom)
 1101                .Where(d => dateTo == default || d.CreationDateTime < dateTo)
 1102                .Where(d => d.Movement.Supplier.Id == contragentId
 1103                            && (d.StatusCurrent.Id == (long) MovementsStatus.Received
 1104                                || d.StatusCurrent.Id == (long) MovementsStatus.ClaimDeclined ))
 1105                .Select(d => d.Movement)
 1106                .ToList();
 107
 5108            var result = filtered.Select(good => new GoodAccount
 5109            {
 5110                CategoryName = good.CatName,
 5111                GoodName = good.Name,
 0112                SellCount = movementList.Where(movement => movement.Items.Any(item => item.Good.Id == good.Id))
 5113                    .Count(),
 5114                SellItemCount = movementList
 0115                    .Select(movement => movement.Items.FirstOrDefault(item => item.Good.Id == good.Id))
 0116                    .Where(item => item != null)
 0117                    .Sum(item => item.Quantity),
 5118                Sum = movementList
 0119                    .Select(movement => movement.Items.FirstOrDefault(item => item.Good.Id == good.Id))
 0120                    .Where(d => d != null)
 0121                    .Sum(item => item.Price * item.Quantity),
 5122                BuyerCount = movementList
 0123                    .Where(movement => movement.Items.Any(d => d.Good.Id == good.Id))
 0124                    .GroupBy(movement => movement.Customer)
 0125                    .Select(movement => movement.First())
 5126                    .Count()
 5127            }).AsQueryable();
 128
 1129            result = result.Where(d => d.SellCount > 0);
 130
 1131            var filteredCount = result.Count();
 1132            Expression<Func<GoodAccount, object>> sortExpression =
 1133                (sort?.Split("|").ElementAtOrDefault(0) ?? "").ToLower() switch
 1134                {
 1135                    "category" => e => e.CategoryName,
 1136                    "sum" => e => e.Sum,
 1137                    "sellcount" => e => e.SellCount,
 1138                    "sellitemcount" => e => e.SellItemCount,
 1139                    _ => e => e.GoodName
 1140                };
 1141            var finished = (sort?.Split("|").ElementAtOrDefault(1) ?? "").ToLower() switch
 1142            {
 1143                "desc" => result.OrderByDescending(sortExpression),
 1144                _ => result.OrderBy(sortExpression)
 1145            };
 146
 1147            var paginated = new PaginatedData<List<GoodAccount>>
 1148            {
 1149                TotalCount = totalCount,
 1150                TotalFilteredCount = filteredCount,
 1151                Result = finished.Skip((page < 2 ? 0 : page-1) * (int)limit).Take((int)limit).ToList()
 1152            };
 153
 1154            return paginated;
 1155        }
 156        /// <summary>
 157        /// Возвращает товарный отчет без пагинации
 158        /// </summary>
 159        /// <param name="contragentId">Склад для построения отчета</param>
 160        /// <param name="categoryId">категория товара - 0 для всех</param>
 161        /// <param name="dateFrom">Дата начала отбора - для всех по умолчанию</param>
 162        /// <param name="dateTo">Дата окончания отбора - для всех по умолчанию</param>
 163        /// <param name="sort">Сортировка -goodname, goodname|desc, category, category|desc, sum, sum|desc, sellcount, s
 164        /// <param name="filter">Фильтр по наименованию товара - по умолчанию для всех</param>
 165        /// <returns>Task&lt;List&lt;GoodAccount&gt;&gt;</returns>
 166        public async Task<List<GoodAccount>> GoodAccountingReportWithoutPagination(long contragentId, long categoryId = 
 167            DateTime dateFrom = default,
 168            DateTime dateTo = default, string sort = default, string filter = default)
 0169        {
 0170            List<long> cats = new List<long>();
 0171            if (categoryId != 0)
 0172            {
 0173                cats = _goodService.ExpandCategoryTree(new List<long>() {categoryId});
 0174                cats.Add(categoryId);
 0175            }
 0176            var goodsList = _db.Goods
 0177                .Include(good => good.Category)
 0178                .Include(good => good.Prices)
 0179                .ThenInclude(prices => prices.PriceTrend)
 0180                .ThenInclude(trend => trend.SupplierDepartment)
 0181                .ThenInclude(suplierDep => suplierDep.Contragent)
 0182                .Where(good =>
 0183                    !good.IsDeleted
 0184                    && good.Prices.Count > 0 && good.Prices.Any(price =>
 0185                        price.PriceTrend.SupplierDepartment.Contragent.Id == contragentId))
 0186                .Where(good => categoryId == 0 || cats.Contains(good.Category.Id))
 0187                .Where(good => string.IsNullOrEmpty(filter) || good.Name.ToLower().Contains(filter.ToLower().Trim()))
 0188                .Select(good => new {Id = good.Id, Name = good.Name, CatName = good.Category.Name})
 0189                .AsEnumerable();
 190
 0191            var movementList = _db.MovementStatusJournals
 0192                .Include(journal => journal.Movement)
 0193                .ThenInclude(movement => movement.Items)
 0194                .ThenInclude(item => item.Good)
 0195                .Include(journal => journal.Movement)
 0196                .ThenInclude(movement => movement.Sender)
 0197                .Include(journal => journal.Movement)
 0198                .ThenInclude(movement => movement.Customer)
 0199                .Where(d => !d.Movement.IsDeleted)
 0200                .Where(d => dateFrom == default || d.Movement.CreationDateTime >= dateFrom)
 0201                .Where(d => dateTo == default || d.CreationDateTime < dateTo)
 0202                .Where(d => d.Movement.Supplier.Id == contragentId
 0203                            && (d.StatusCurrent.Id == (long) MovementsStatus.Received
 0204                                || d.StatusCurrent.Id == (long) MovementsStatus.ClaimDeclined ))
 0205                .Select(d => d.Movement)
 0206                .ToList();
 0207            var result = goodsList.Select(good => new GoodAccount
 0208            {
 0209                CategoryName = good.CatName,
 0210                GoodName = good.Name,
 0211                SellCount = movementList.Where(movement => movement.Items.Any(item => item.Good.Id == good.Id))
 0212                    .Count(),
 0213                SellItemCount = movementList
 0214                    .Select(movement => movement.Items.FirstOrDefault(item => item.Good.Id == good.Id))
 215                    .Where(item => item != null)
 216                    .Sum(item => item.Quantity),
 0217                Sum = movementList
 0218                    .Select(movement => movement.Items.FirstOrDefault(item => item.Good.Id == good.Id))
 219                    .Where(d => d != null)
 220                    .Sum(item => item.Price),
 0221                BuyerCount = movementList
 0222                    .Where(movement => movement.Items.Any(d => d.Good.Id == good.Id))
 223                    .GroupBy(movement => movement.Customer)
 224                    .Select(movement => movement.First())
 0225                    .Count()
 0226            }).AsQueryable();
 0227            result = result.Where(d => d.SellCount > 0);
 0228            Expression<Func<GoodAccount, object>> sortExpression =
 0229                (sort?.Split("|").ElementAtOrDefault(0) ?? "").ToLower() switch
 0230                {
 0231                    "category" => e => e.CategoryName,
 0232                    "sum" => e => e.Sum,
 0233                    "sellcount" => e => e.SellCount,
 0234                    "sellitemcount" => e => e.SellItemCount,
 0235                    _ => e => e.GoodName
 0236                };
 0237            var finished = (sort?.Split("|").ElementAtOrDefault(1) ?? "").ToLower() switch
 0238            {
 0239                "desc" => result.OrderByDescending(sortExpression),
 0240                _ => result.OrderBy(sortExpression)
 0241            };
 0242            return await Task.FromResult(finished.ToList());
 0243        }
 244
 245        /// <summary>
 246        /// Возвращает финансовый отчет
 247        /// </summary>
 248        /// <param name="contragentId">Идентификатор контрагента поставщика - 0 для всех</param>
 249        /// <param name="dateFrom">Дата начала отбора - по умолчанию для всех</param>
 250        /// <param name="dateTo">Дата окончания отбора - по умолчанию для всех</param>
 251        /// <param name="sort">Сортировка -по умолчанию byername, byername|desc, customer, customer|desc, holdedsum, hol
 252        /// <param name="filter">Фильтрация по назаванию покупателя - по умолчанию для всех</param>
 253        /// <param name="page">Страница отбора - 0 по умолчанию</param>
 254        /// <param name="limit">Лимит отбора - 10 по умолчанию</param>
 255        /// <returns>Task&lt;MoneyAccountWrapper&gt;</returns>
 256        public async Task<MoneyAccountWrapper> MoneyAccountingReport(long contragentId, DateTime dateFrom = default,
 257            DateTime dateTo = default, string sort = default, string filter = default, int page = 0, int limit = 10)
 258        {
 259            limit = limit < 1 ? 10 : limit;
 260            var movements = _db.Movements
 261                .AsNoTracking()
 262                .Include(movement => movement.Customer)
 263                .Include(movement => movement.Supplier)
 264                .Include(movement => movement.MovementType)
 265                .Include(movement => movement.MovementStatus);
 266            var total = movements.ToList().GroupBy(d => d.Customer.Id)
 267                .Select(d => d.First()).Count();
 268            var filterMovements = movements
 269            .Where(movement => dateFrom == default || movement.CreationDateTime >= dateFrom)
 270            .Where(movement => dateTo == default || movement.CreationDateTime <= dateTo)
 271            .Where(movement => contragentId == 0 || movement.Supplier.Id == contragentId)
 272            .Where(movement => string.IsNullOrEmpty(filter) ||
 273                               movement.Customer.ShortName.ToLower().Contains(filter.ToLower())
 274                               || movement.Customer.FullName.ToLower().Contains(filter.ToLower()))
 275            .ToList();
 276
 277            var totalFiltered = filterMovements.GroupBy(d => d.Customer.Id)
 278                .Select(d => d.First())
 279                .Count();
 280
 281            var shipInWork = new long[]
 282            {
 283                (long) MovementsStatus.ShipmentDraft, (long) MovementsStatus.PaymentAwaiting,
 284                (long) MovementsStatus.Picking,(long) MovementsStatus.ReadyForShipment,
 285                (long) MovementsStatus.Correction, (long) MovementsStatus.Shipped,
 286                (long) MovementsStatus.ClaimInProgress
 287            };
 288            var rejected = new long[]
 289            {
 290                (long) MovementsStatus.ClaimAccepted,
 291                (long) MovementsStatus.SupplierReject,
 292                (long) MovementsStatus.ShipmentReject,
 293                (long) MovementsStatus.CustomerReject
 294            };
 295
 296            var list = filterMovements
 297                .GroupBy(d => d.Customer.Id)
 298                .Select(d => d.First())
 0299                .Select(d => new MoneyAccount
 0300                {
 0301                    BuyerName = d.Customer.ShortName,
 0302                    OrderCount = filterMovements.Where(movement =>
 0303                            movement.Customer.Id == d.Customer.Id &&
 0304                            movement.MovementType.Id == (long) MovementKind.Order)
 0305                        .Count(),
 0306                    ShipmentInWorkCount = filterMovements.Where(movement =>
 0307                            movement.Customer.Id == d.Customer.Id && !movement.IsDeleted && shipInWork.Contains(movement
 0308                        .Count(),
 0309                    ShipmentReceivedCount = filterMovements.Where(movement => movement.Customer.Id == d.Customer.Id && (
 0310                            movement.MovementStatus.Id ==
 0311                            (long) MovementsStatus.Received
 0312                            || movement.MovementStatus.Id ==
 0313                            (long) MovementsStatus.ClaimDeclined))
 0314                        .Count(),
 0315                    ShipmentRejectedCount = filterMovements.Where(movement => movement.Customer.Id == d.Customer.Id
 0316                                                                               && (rejected.Contains(movement.MovementSt
 0317                                                                               || movement.IsDeleted))
 0318                        .Count(),
 0319                    HoldedMoneySum = (_walletPaymentService.GetWalletInfo(d.Customer.Id).Result) == null
 0320                        ? 0
 0321                        : (_walletPaymentService.GetWalletInfo(d.Customer.Id).Result).Hold,
 0322                    RejectedMoneySum = _db.WalletTransactions
 0323                        .Include(d => d.Movement)
 0324                        .Where(w => w.Movement.Id == d.Customer.Id &&
 0325                                    w.Status.Id == (long) TransactionStatusKind.Canceled)
 0326                        .Sum(w => w.Sum),
 0327                    AcceptedMoneySum =
 0328                        _db.WalletTransactions
 0329                            .Include(d => d.Movement)
 0330                            .Where(w => w.Movement.Id == d.Customer.Id &&
 0331                                        w.Status.Id == (long) TransactionStatusKind.Confirmed)
 0332                            .Sum(w => w.Sum),
 0333                }).AsQueryable();
 334
 335            Expression<Func<MoneyAccount, object>> sortExpression =
 336                (sort?.Split("|").ElementAtOrDefault(0) ?? "").ToLower() switch
 337                {
 338                    "customer" => e => e.BuyerName,
 339                    "holdedsum" => e => e.HoldedMoneySum,
 340                    "rejectedsum" => e => e.RejectedMoneySum,
 341                    "acceptedsum" => e => e.AcceptedMoneySum,
 342                    "ordercount" => e => e.OrderCount,
 343                    _ => e => e.BuyerName
 344                };
 345            var finished = (sort?.Split("|").ElementAtOrDefault(1) ?? "").ToLower() switch
 346            {
 347                "desc" => list.OrderByDescending(sortExpression),
 348                _ => list.OrderBy(sortExpression)
 349            };
 350            var moneyAccounts = finished.Skip((page < 2 ? 0 : page - 1) * (int) limit).Take((int) limit).ToList();
 351            return new MoneyAccountWrapper
 352            {
 353                Data = moneyAccounts,
 354                MoneyAccountReportSummary = new MoneyAccountReportSummary {HoldedSum = moneyAccounts.Sum(account => acco
 355                RejectedSum = moneyAccounts.Sum(account => account.RejectedMoneySum),
 356                AcceptedSum = moneyAccounts.Sum(account => account.AcceptedMoneySum),
 357                OrderCount = moneyAccounts.Sum(account => account.OrderCount),
 358                ShipmentCount = moneyAccounts.Sum(account =>
 359                    account.ShipmentInWorkCount + account.ShipmentReceivedCount + account.ShipmentRejectedCount)
 360                },
 361                Pagination = new Pagination
 362                {
 363                    TotalCount = total,
 364                    TotalFilteredCount = totalFiltered
 365                }
 366            };
 367        }
 368
 369        /// <summary>
 370        /// Возвращает финансовый отчет без пагинации
 371        /// </summary>
 372        /// <param name="contragentId">Идентификатор контрагента поставщика - 0 для всех</param>
 373        /// <param name="dateFrom">Дата начала отбора - по умолчанию для всех</param>
 374        /// <param name="dateTo">Дата окончания отбора - по умолчанию для всех</param>
 375        /// <param name="sort">Сортировка -по умолчанию byername, byername|desc, customer, customer|desc, holdedsum, hol
 376        /// <param name="filter">Фильтрация по назаванию покупателя - по умолчанию для всех</param>
 377        /// <returns>Task&lt;List&lt;MoneyAccount&gt;&gt;</returns>
 378        public async Task<List<MoneyAccount>> MoneyAccountingReportWithoutPagination(long contragentId,
 379            DateTime dateFrom = default,
 380            DateTime dateTo = default, string sort = default, string filter = default)
 381        {
 382            var movements = _db.Movements
 383                .AsNoTracking()
 384                .Include(movement => movement.Customer)
 385                .Include(movement => movement.Supplier)
 386                .Include(movement => movement.MovementType)
 387                .Include(movement => movement.MovementStatus);
 388            var total = movements.ToList().GroupBy(d => d.Customer.Id)
 389                .Select(d => d.First()).Count();
 390            var filterMovements = movements
 391                .Where(movement => dateFrom == default || movement.CreationDateTime >= dateFrom)
 392                .Where(movement => dateTo == default || movement.CreationDateTime <= dateTo)
 393                .Where(movement => contragentId == 0 || movement.Supplier.Id == contragentId)
 394                .Where(movement => string.IsNullOrEmpty(filter) ||
 395                                   movement.Customer.ShortName.ToLower().Contains(filter.ToLower())
 396                                   || movement.Customer.FullName.ToLower().Contains(filter.ToLower()))
 397                .ToList();
 398            var shipInWork = new long[]
 399            {
 400                (long) MovementsStatus.ShipmentDraft, (long) MovementsStatus.PaymentAwaiting,
 401                (long) MovementsStatus.Picking,(long) MovementsStatus.ReadyForShipment,
 402                (long) MovementsStatus.Correction, (long) MovementsStatus.Shipped,
 403                (long) MovementsStatus.ClaimInProgress
 404            };
 405            var rejected = new long[]
 406            {
 407                (long) MovementsStatus.ClaimAccepted,
 408                (long) MovementsStatus.SupplierReject,
 409                (long) MovementsStatus.ShipmentReject,
 410                (long) MovementsStatus.CustomerReject
 411            };
 412
 413            var list = filterMovements
 414                .GroupBy(d => d.Customer.Id)
 415                .Select(d => d.First())
 0416                .Select(d => new MoneyAccount
 0417                {
 0418                    BuyerName = d.Customer.ShortName,
 0419                    OrderCount = filterMovements.Where(movement =>
 0420                            movement.Customer.Id == d.Customer.Id &&
 0421                            movement.MovementType.Id == (long) MovementKind.Order)
 0422                        .Count(),
 0423                    ShipmentInWorkCount = filterMovements.Where(movement =>
 0424                            movement.Customer.Id == d.Customer.Id && !movement.IsDeleted && shipInWork.Contains(movement
 0425                        .Count(),
 0426                    ShipmentReceivedCount = filterMovements.Where(movement => movement.Customer.Id == d.Customer.Id && (
 0427                            movement.MovementStatus.Id ==
 0428                            (long) MovementsStatus.Received
 0429                            || movement.MovementStatus.Id ==
 0430                            (long) MovementsStatus.ClaimDeclined))
 0431                        .Count(),
 0432                    ShipmentRejectedCount = filterMovements.Where(movement => movement.Customer.Id == d.Customer.Id
 0433                                                                               && (rejected.Contains(movement.MovementSt
 0434                                                                               || movement.IsDeleted))
 0435                        .Count(),
 0436                    HoldedMoneySum = (_walletPaymentService.GetWalletInfo(d.Customer.Id).Result) == null
 0437                        ? 0
 0438                        : (_walletPaymentService.GetWalletInfo(d.Customer.Id).Result).Hold,
 0439                    RejectedMoneySum = _db.WalletTransactions
 0440                        .Include(d => d.Movement)
 0441                        .Where(w => w.Movement.Id == d.Customer.Id &&
 0442                                    w.Status.Id == (long) TransactionStatusKind.Canceled)
 0443                        .Sum(w => w.Sum),
 0444                    AcceptedMoneySum =
 0445                        _db.WalletTransactions
 0446                            .Include(d => d.Movement)
 0447                            .Where(w => w.Movement.Id == d.Customer.Id &&
 0448                                        w.Status.Id == (long) TransactionStatusKind.Confirmed)
 0449                            .Sum(w => w.Sum),
 0450                }).AsQueryable();
 451
 452            Expression<Func<MoneyAccount, object>> sortExpression =
 453                (sort?.Split("|").ElementAtOrDefault(0) ?? "").ToLower() switch
 454                {
 455                    "customer" => e => e.BuyerName,
 456                    "holdedsum" => e => e.HoldedMoneySum,
 457                    "rejectedsum" => e => e.RejectedMoneySum,
 458                    "acceptedsum" => e => e.AcceptedMoneySum,
 459                    "ordercount" => e => e.OrderCount,
 460                    _ => e => e.BuyerName
 461                };
 462            var finished = (sort?.Split("|").ElementAtOrDefault(1) ?? "").ToLower() switch
 463            {
 464                "desc" => list.OrderByDescending(sortExpression),
 465                _ => list.OrderBy(sortExpression)
 466            };
 467            return finished.ToList();
 468        }
 469
 470        /// <summary>
 471        /// Ежедневный отчет
 472        /// </summary>
 473        /// <returns>List&lt;EveryDay&gt;</returns>
 474        public List<EveryDay> EveryDayReport()
 0475        {
 0476            return _db.MovementStatusJournals
 0477                .Include(d => d.Movement)
 0478                .ThenInclude(movement => movement.Customer)
 0479                .Include(d => d.Movement)
 0480                .ThenInclude(movement => movement.Notes)
 0481                .Where(d => d.CreationDateTime >= DateTime.UtcNow.AddDays(-1) && d.CreationDateTime <= DateTime.UtcNow)
 0482                .AsEnumerable()
 0483                .GroupBy(d => d.Movement.Id)
 0484                .Select(d => d.First())
 0485                .Select(d => new EveryDay
 0486                {
 0487                    Name = d.Movement.Customer.FullName,
 0488                    Inn = d.Movement.Customer.Inn,
 0489                    MovementId = d.Movement.Id,
 0490                    Date =  d.Movement.CreationDateTime,
 0491                    Sum = d.Movement.PrepaimentSum,
 0492                    Statuses = String.Join("->", _db.MovementStatusJournals
 0493                        .Include(j => j.Movement)
 0494                        .Include(j => j.StatusCurrent)
 0495                        .Where(j => j.Movement.Id == d.Movement.Id)
 0496                        .Select(d => d.StatusCurrent.Name)),
 0497                    Comment = String.Join(";", d.Movement.Notes.Select(s => s.Body))
 0498                }).ToList();
 0499        }
 500
 501        /// <summary>
 502        /// Отчет матрица
 503        /// </summary>
 504        /// <param name="dateFrom">Дата начала отбора - по умолчанию для всех</param>
 505        /// <param name="dateTo">Дата окончания отбора - по умолчанию для всех</param>
 506        /// <returns>List&lt;MatrixReport&gt;</returns>
 507        public List<MatrixReport> GetMatrixReport(DateTime dateFrom = default, DateTime dateTo = default)
 0508        {
 0509            Dictionary<long, List<MovementStatusJournal>> dict = _db.MovementStatusJournals
 0510                .Include(d => d.StatusCurrent)
 0511                .Include(d => d.Movement)
 0512                .ThenInclude(movement => movement.Customer)
 0513                .Include(d => d.Movement)
 0514                .ThenInclude(movement => movement.MovementStatus)
 0515                .Where(d => !d.Movement.IsDeleted)
 0516                .Where(d => dateFrom == default || d.Movement.CreationDateTime >= dateFrom)
 0517                .Where(d => dateTo == default || d.Movement.CreationDateTime <= dateTo)
 0518                .AsEnumerable()
 0519                .GroupBy(d => d.Movement.Id)
 0520                .Select(d => d.First())
 0521                .OrderBy(d => d.Movement.Id)
 0522                .ToDictionary(d => d.Movement.Id,
 0523                    d => _db.MovementStatusJournals
 0524                        .Where(journal => journal.Movement.Id == d.Movement.Id).ToList());
 0525            var t = dict.Select(d => new MatrixReport
 0526            {
 0527                Name = d.Value.FirstOrDefault()?.Movement?.Customer?.FullName,
 0528                Inn = d.Value.FirstOrDefault()?.Movement?.Customer?.Inn,
 0529                MovementId = d.Key,
 0530                Date = d.Value.FirstOrDefault()?.Movement?.CreationDateTime.ToMoscowTime() ?? DateTime.Now,
 0531                Sum = d.Value.FirstOrDefault()?.Movement?.PrepaimentSum ?? 0,
 0532                OrderDraft = d.Value
 0533                    ?.FirstOrDefault(x => x.StatusCurrent.Id == (long) MovementsStatus.OrderDraft)?
 0534                    .CreationDateTime != null ? d.Value
 0535                    ?.FirstOrDefault(x => x.StatusCurrent.Id == (long) MovementsStatus.OrderDraft)?
 0536                    .CreationDateTime.ToMoscowTime() : null,
 0537                OrderInQueue = d.Value
 0538                    ?.FirstOrDefault(x => x.StatusCurrent.Id == (long) MovementsStatus.InQueue)?
 0539                    .CreationDateTime != null ? d.Value
 0540                    ?.FirstOrDefault(x => x.StatusCurrent.Id == (long) MovementsStatus.InQueue)?
 0541                    .CreationDateTime.ToMoscowTime() : null,
 0542                OrderInProgress = d.Value
 0543                    ?.FirstOrDefault(x => x.StatusCurrent.Id == (long) MovementsStatus.InProgress)?
 0544                    .CreationDateTime != null ? d.Value
 0545                    ?.FirstOrDefault(x => x.StatusCurrent.Id == (long) MovementsStatus.InProgress)?
 0546                    .CreationDateTime.ToMoscowTime() : null,
 0547                OrderReject = d.Value
 0548                    ?.FirstOrDefault(x => x.StatusCurrent.Id == (long) MovementsStatus.Reject)?
 0549                    .CreationDateTime != null ? d.Value
 0550                    ?.FirstOrDefault(x => x.StatusCurrent.Id == (long) MovementsStatus.Reject)?
 0551                    .CreationDateTime.ToMoscowTime() : null,
 0552                OrderFinished = d.Value
 0553                    ?.FirstOrDefault(x => x.StatusCurrent.Id == (long) MovementsStatus.Finished)?
 0554                    .CreationDateTime != null ? d.Value
 0555                    ?.FirstOrDefault(x => x.StatusCurrent.Id == (long) MovementsStatus.Finished)?
 0556                    .CreationDateTime.ToMoscowTime() : null,
 0557                ShipmentDraft = d.Value
 0558                    ?.FirstOrDefault(x => x.StatusCurrent.Id == (long) MovementsStatus.ShipmentDraft)?
 0559                    .CreationDateTime != null ? d.Value
 0560                    ?.FirstOrDefault(x => x.StatusCurrent.Id == (long) MovementsStatus.ShipmentDraft)?
 0561                    .CreationDateTime.ToMoscowTime() : null,
 0562                ShipmentPaimentAwating = d.Value
 0563                    ?.FirstOrDefault(x => x.StatusCurrent.Id == (long) MovementsStatus.PaymentAwaiting)?
 0564                    .CreationDateTime != null ? d.Value
 0565                    ?.FirstOrDefault(x => x.StatusCurrent.Id == (long) MovementsStatus.PaymentAwaiting)?
 0566                    .CreationDateTime.ToMoscowTime() : null,
 0567                ShipmentPicking = d.Value
 0568                    ?.FirstOrDefault(x => x.StatusCurrent.Id == (long) MovementsStatus.Picking)?
 0569                    .CreationDateTime != null ? d.Value
 0570                    ?.FirstOrDefault(x => x.StatusCurrent.Id == (long) MovementsStatus.Picking)?
 0571                    .CreationDateTime.ToMoscowTime() : null,
 0572                ShipmentCorrection = d.Value
 0573                    ?.FirstOrDefault(x => x.StatusCurrent.Id == (long) MovementsStatus.Correction)?
 0574                    .CreationDateTime != null ? d.Value
 0575                    ?.FirstOrDefault(x => x.StatusCurrent.Id == (long) MovementsStatus.Correction)?
 0576                    .CreationDateTime.ToMoscowTime() : null,
 0577                ShipmentReadyToShip = d.Value
 0578                    ?.FirstOrDefault(x => x.StatusCurrent.Id == (long) MovementsStatus.ReadyForShipment)?
 0579                    .CreationDateTime != null ? d.Value
 0580                    ?.FirstOrDefault(x => x.StatusCurrent.Id == (long) MovementsStatus.ReadyForShipment)?
 0581                    .CreationDateTime.ToMoscowTime() : null,
 0582                ShipmentShipped = d.Value
 0583                    ?.FirstOrDefault(x => x.StatusCurrent.Id == (long) MovementsStatus.Shipped)?
 0584                    .CreationDateTime != null ? d.Value
 0585                    ?.FirstOrDefault(x => x.StatusCurrent.Id == (long) MovementsStatus.Shipped)?
 0586                    .CreationDateTime.ToMoscowTime() : null,
 0587                ShipmentCustomerReject = d.Value
 0588                    ?.FirstOrDefault(x => x.StatusCurrent.Id == (long) MovementsStatus.CustomerReject)?
 0589                    .CreationDateTime != null ? d.Value
 0590                    ?.FirstOrDefault(x => x.StatusCurrent.Id == (long) MovementsStatus.CustomerReject)?
 0591                    .CreationDateTime.ToMoscowTime() : null,
 0592                ShipmentSupplierReject = d.Value
 0593                    ?.FirstOrDefault(x => x.StatusCurrent.Id == (long) MovementsStatus.SupplierReject)?
 0594                    .CreationDateTime != null ? d.Value
 0595                    ?.FirstOrDefault(x => x.StatusCurrent.Id == (long) MovementsStatus.SupplierReject)?
 0596                    .CreationDateTime.ToMoscowTime() : null,
 0597                ShipmentReject = d.Value
 0598                    ?.FirstOrDefault(x => x.StatusCurrent.Id == (long) MovementsStatus.ShipmentReject)?
 0599                    .CreationDateTime != null ? d.Value
 0600                    ?.FirstOrDefault(x => x.StatusCurrent.Id == (long) MovementsStatus.ShipmentReject)?
 0601                    .CreationDateTime.ToMoscowTime() : null,
 0602                ShipmentReceived = d.Value
 0603                    ?.FirstOrDefault(x => x.StatusCurrent.Id == (long) MovementsStatus.Received)?
 0604                    .CreationDateTime != null ? d.Value
 0605                    ?.FirstOrDefault(x => x.StatusCurrent.Id == (long) MovementsStatus.Received)?
 0606                    .CreationDateTime.ToMoscowTime() : null,
 0607                ClaimInProgress = d.Value
 0608                    ?.FirstOrDefault(x => x.StatusCurrent.Id == (long) MovementsStatus.ClaimInProgress)?
 0609                    .CreationDateTime != null ? d.Value
 0610                    ?.FirstOrDefault(x => x.StatusCurrent.Id == (long) MovementsStatus.ClaimInProgress)?
 0611                    .CreationDateTime.ToMoscowTime() : null,
 0612                ClaimAccedpted = d.Value
 0613                    ?.FirstOrDefault(x => x.StatusCurrent.Id == (long) MovementsStatus.ClaimAccepted)?
 0614                    .CreationDateTime != null ? d.Value
 0615                    ?.FirstOrDefault(x => x.StatusCurrent.Id == (long) MovementsStatus.ClaimAccepted)?
 0616                    .CreationDateTime.ToMoscowTime() : null,
 0617                ClaimDeclined = d.Value
 0618                    ?.FirstOrDefault(x => x.StatusCurrent.Id == (long) MovementsStatus.ClaimDeclined)?
 0619                    .CreationDateTime != null ? d.Value
 0620                    ?.FirstOrDefault(x => x.StatusCurrent.Id == (long) MovementsStatus.ClaimDeclined)?
 0621                    .CreationDateTime.ToMoscowTime() : null,
 0622            }).ToList();
 0623            return t;
 0624        }
 625
 626        public async Task<List<StatusReport>> StatusReports(DateTime dateFrom, DateTime dateTo)
 627        {
 628            Dictionary<long, List<MovementStatusJournal>> dict = _db.MovementStatusJournals
 629                .Include(d => d.StatusCurrent)
 630                .Include(d => d.Movement)
 631                .ThenInclude(movement => movement.Customer)
 632                .Include(d => d.Movement)
 633                .ThenInclude(movement => movement.MovementStatus)
 634                .Include(d => d.Movement)
 635                .ThenInclude(movement =>  movement.Supplier)
 636                .Include(d => d.Movement)
 637                .ThenInclude(movement => movement.DeliveryType)
 638                .Where(d => !d.Movement.IsDeleted)
 639                .Where(d => d.StatusCurrent.Id != 1)
 640                .Where(d => dateFrom == default || d.Movement.CreationDateTime >= dateFrom)
 641                .Where(d => dateTo == default || d.Movement.CreationDateTime <= dateTo)
 642                .AsEnumerable()
 643                .GroupBy(d => d.Movement.Id)
 644                .Select(d => d.First())
 645                .OrderBy(d => d.Movement.Id)
 646                .ToDictionary(d => d.Movement.Id,
 0647                    d => _db.MovementStatusJournals
 0648                        .Where(journal => journal.Movement.Id == d.Movement.Id)
 0649                        .Where(journal => journal.StatusCurrent.Id != 1).ToList());
 650            var guids = dict.Select(t => t.Value.FirstOrDefault().Movement.GUID).ToList();
 651            var jsonSettings = new JsonSerializerSettings
 652            {
 653                Culture = new System.Globalization.CultureInfo("ru-RU"), //чтобы нормально было преобразование prepaimen
 654                Converters = new List<JsonConverter>() {new BadDateFixingConverter("dd/MM/yyyy hh:mm:ss")}
 655            };
 656
 657            var lis = _db.Events.Include(d => d.CreatedByUser)
 658                .Where(d => d.Entity.ToUpper().Equals("Movement".ToUpper()))
 659                .Where(d => guids.Contains(d.RecordGuid))
 660                .Select(d => new
 661                {
 662                    GUID = d.RecordGuid,
 663                    User = d.CreatedByUser,
 664                    Record = d.ReasonJson
 665                }).ToList();
 666
 667            Dictionary<Guid, List<EventRecord>> events = _db.Events
 668                .Where(d => d.Entity.ToUpper().Equals("Movement".ToUpper()))
 669                .Where(d => guids.Contains(d.RecordGuid))
 670                .AsEnumerable()
 671                .GroupBy(d => d.RecordGuid)
 672                .Select(d => d.First())
 673                .ToDictionary(d => d.RecordGuid,
 0674                    d => lis
 0675                        .Where(ev => ev.GUID == d.RecordGuid)
 0676                        .Select(rec => new EventRecord
 0677                            {
 0678                              GUID  = rec.GUID,
 0679                              User = rec.User,
 0680                              New = JsonConvert.DeserializeObject<MovementHistoryJsonDTO>(rec.Record, jsonSettings).New
 0681                            })
 682                        .Where(rec => rec.New != null)
 0683                        .ToList());
 684
 0685            var reports = dict.SelectMany(report => report.Value.Select(journal => new StatusReport
 0686                {
 0687                    StatusName = journal.StatusCurrent.Name,
 0688                    UserName = events.FirstOrDefault(d => d.Key.Equals(journal.Movement.GUID))
 0689                        .Value.FirstOrDefault(d => d.New.MovementStatusId == journal.StatusCurrent.Id)
 0690                        .User.GetName(),
 0691                    CustomerName = journal.Movement.Customer.ShortName,
 0692                    SupplierName = journal.Movement.Supplier.ShortName,
 0693                    BaseId = journal.Movement.ParentId ?? journal.Movement.Id,
 0694                    CreationDate = journal.CreationDateTime.ToString("dd.MM.yyyy hh:mm:ss"),
 0695                    Id = journal.Movement.Id,
 0696                    StatusId = journal.StatusCurrent.Id,
 0697                    Sum = events.FirstOrDefault(d => d.Key.Equals(journal.Movement.GUID))
 0698                        .Value.FirstOrDefault(d => d.New.MovementStatusId == journal.StatusCurrent.Id).New
 0699                        .PrepaimentSum,
 0700                    Month = journal.CreationDateTime.Month,
 0701                    ParentId = journal.Movement.ParentId ?? 0,
 0702                    DeliveryType = journal.Movement.DeliveryType?.Name ?? "Самовывоз"
 0703                }))
 704                .ToList();
 705            return reports;
 706        }
 707
 708
 709    }
 710}
Generated by: ReportGenerator 4.6.1.0
08/27/2020 - 08:55:00
GitHub | www.palmmedia.de

Methods/Properties

.ctor(...)
GoodAccountingReport()
GoodAccountingReportWithoutPagination()
EveryDayReport()
GetMatrixReport(...)