DocumentOrganizer/Diligent.WebAPI.Business/Services/FileEntityService.cs

using Azure.Core;
using Dapper;
using Diligent.WebAPI.Contracts.DTOs.Categories;
using Diligent.WebAPI.Contracts.DTOs.Document;
using Diligent.WebAPI.Contracts.DTOs.File;
using Diligent.WebAPI.Contracts.DTOs.Files;
using Diligent.WebAPI.Contracts.DTOs.Tags;
using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using static System.Net.Mime.MediaTypeNames;

namespace Diligent.WebAPI.Business.Services
{
    public class FileEntityService : IFileEntityService
    {
        private readonly ILogger<FileEntityService> _logger;
        private readonly DatabaseContext _context;
        private readonly IMapper _mapper;
        private readonly IConfiguration _configuration;

        public FileEntityService(DatabaseContext context, ILogger<FileEntityService> logger, IMapper mapper, IConfiguration configuration)
        {
            _context = context;
            _logger = logger;
            _mapper = mapper;
            _configuration = configuration;
        }

        public async Task UploadFileAsync(FileEntity file)
        {
            await _context.Files.AddAsync(file);
            await _context.SaveChangesAsync();
        }

        public async Task<object> GetAllFiltered(FileFilter filters)
        {
            using var connection = new SqlConnection(_configuration.GetConnectionString("WebApi"));
            var sql = "";
            if(filters.CategoryId == 0)
            {
                sql = @"SELECT Files.Id as FileId, Note, Files.Deleted, stream_id, DocumentOrganizerDocStore.name as FileName, file_stream, file_type, cached_file_size, Title, Tags.Id as TagId, Tags.Name as TagName
                        FROM Files inner join FileEntityTag on Files.Id = FileEntityTag.FilesId 
                        inner join Tags on FileEntityTag.TagsId = Tags.Id 
                        inner join DocumentOrganizerDocStore on DocumentOrganizerDocStore.stream_id = Files.DocumentId WHERE Files.Deleted = 0 and Files.CategoryId is NULL" +
                (filters.Content is null ? "" : " and contains(DocumentOrganizerDocStore.file_stream," + "'\"" + filters.Content + "*\"')");
            }
            else
            {
                sql = @"SELECT Files.Id as FileId, Note, Files.Deleted, stream_id, DocumentOrganizerDocStore.name as FileName, file_stream, file_type, cached_file_size, Title, Tags.Id as TagId, Tags.Name as TagName
                        FROM Files inner join FileEntityTag on Files.Id = FileEntityTag.FilesId 
                        inner join Tags on FileEntityTag.TagsId = Tags.Id 
                        inner join DocumentOrganizerDocStore on DocumentOrganizerDocStore.stream_id = Files.DocumentId WHERE Files.Deleted = 0 and Files.CategoryId=@categoryId" +
                (filters.Content is null ? "" : " and contains(DocumentOrganizerDocStore.file_stream," + "'\"" + filters.Content + "*\"')");
            }
            
            var files = await connection.QueryAsync<FileFilterReturn, TagResponse, FileFilterReturn>(sql,(file, tag) =>
            {
                file.Tags.Add(tag);
                return file;
            }, splitOn: "TagId",
               param: new { categoryId = filters.CategoryId });

            var filesList = new List<FileFilterReturnDto>();
            foreach (var fileItem in files.ToList())
            {
                var fileFilterReturnDto = new FileFilterReturnDto
                {
                    Stream_id = fileItem.Stream_id,
                    Cached_file_size = fileItem.Cached_file_size,
                    Category = fileItem.Category,
                    FileName = fileItem.FileName,
                    File_type = fileItem.File_type,
                    Tags = fileItem.Tags,
                    Title = fileItem.Title,
                    Note = fileItem.Note
                };
                // return File_stream in base64 format
                MemoryStream stream = new (fileItem.File_stream);
                fileFilterReturnDto.File_stream = ConvertToBase64(stream);
                filesList.Add(fileFilterReturnDto);
            }

            var filtered = filesList
                .FilterFiles(filters)
                .DistinctBy(x => x.Stream_id).ToList();

            return new
            {
                Data = filtered.ApplyPagging(filters)
                    .Select(n => new  { n.Stream_id, n.FileName,n.File_stream, n.Cached_file_size, n.File_type, n.Title, n.Note }),
                Total = filtered.Count
            };
        }

        private static string ConvertToBase64(Stream stream)
        {
            byte[] bytes;
            using (var memoryStream = new MemoryStream())
            {
                stream.CopyTo(memoryStream);
                bytes = memoryStream.ToArray();
            }

            string base64 = Convert.ToBase64String(bytes);
            return base64;
        }

        public async Task<FileEntity> GetFileEntityByIdAsync(Guid id)
        {
            var file = await _context.Files.Where(x => x.DocumentId == id).FirstOrDefaultAsync();

            if (file is null)
            {
                throw new EntityNotFoundException("File not found");
            }

            return file;
        }

        public async Task DeleteFileAsync(Guid id)
        {
            var file = await GetFileEntityByIdAsync(id);

            file.Deleted= true;

            await _context.SaveChangesAsync();
        }

        public async Task UpdateNoteAsync(Guid id, UpdateFileNoteRequest fileDto)
        {
            var file = await GetFileEntityByIdAsync(id);
            file.Note = fileDto.Note;
            await _context.SaveChangesAsync();
        }
    }
}