kreta/Kreta.DataAccessManual/OraFileDAL.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using Kreta.Core.Logic;
using Kreta.DataAccess.Interfaces;
using Kreta.DataAccessManual.Interfaces;
using Kreta.DataAccessManual.Util;
using Kreta.Framework;
using Kreta.Framework.Util;
using SDA.DataProvider;
using SDA.Kreta.Entities;

namespace Kreta.DataAccessManual
{
    internal class OraFileDAL : DataAccessBase, IOraFileDAL
    {
        public OraFileDAL(DalHandler handler) : base(handler) { }
        public OraFileDAL(DalHandler handler, GridParameters parameters) : base(handler, parameters) { }

        public IOraFile Get()
        {
            return OraFile.GiveAnInstance();
        }

        public IOraFile Get(int id)
        {
            var entity = OraFile.GiveAnInstance();
            entity.LoadByID(id);
            return entity;
        }

        public int Insert(IOraFile dto)
        {
            var entity = dto as OraFile;

            entity.Insert();
            dto.ID = entity.ID;
            DalHelper.Commit();

            return dto.ID;
        }

        public void UpdateLathatoFileok(int intezmenyId, int tanevId, int? tanitasiOraId, int? orarendiOraId, DateTime oraDatum, List<int> lathatoFileIds)
        {
            using (var command = new SDACommand())
            {
                command.Connection = UserContext.Instance.SDAConnection;
                command.Transaction = UserContext.Instance.SDATransaction;
                command.Parameters.Add("pIntezmenyId", intezmenyId);
                command.Parameters.Add("pTanevId", tanevId);
                command.Parameters.Add("pOraDatum", oraDatum);

                if (tanitasiOraId != null)
                {
                    command.Parameters.Add("pTanitasiOraId", tanitasiOraId);
                }
                else
                {
                    command.Parameters.Add("pTanitasiOraId", DBNull.Value);
                }
                if (orarendiOraId != null)
                {
                    command.Parameters.Add("pOrarendiOraId", orarendiOraId);
                }
                else
                {
                    command.Parameters.Add("pOrarendiOraId", DBNull.Value);
                }

                var lathatoFileIdsParameter = lathatoFileIds?.Count > 0 ? SqlLogic.ParseListToParameter(lathatoFileIds) : 0;
                command.CommandText = $@"UPDATE 
                                           T_ORAFILE
                                         SET 
                                           C_ISTANULOLATHATO = CASE WHEN 
                                                                 ID IN ({lathatoFileIdsParameter})
                                                               THEN 
                                                                 'T'
                                                               ELSE 
                                                                 'F'
                                                               END
                                        WHERE ((C_TANITASIORAID = :pTanitasiOraId AND C_ORARENDIORAID IS NULL) OR
                                               (C_ORARENDIORAID = :pOrarendiOraId AND C_TANITASIORAID IS NULL) OR
                                               (C_ORARENDIORAID = :pOrarendiOraId AND C_TANITASIORAID = :pTanitasiOraId))
                                              AND C_ORADATUM = :pOraDatum
                                              AND C_INTEZMENYID = :pIntezmenyId
                                              AND C_TANEVID = :pTanevId
                                              AND TOROLT = 'F'";
                _ = command.ExecuteNonQuery();
                DalHelper.Commit();
            }
        }

        public void Update(IOraFile dto)
        {
            var entity = dto as OraFile;
            entity.FullUpdate();
            DalHelper.Commit();
        }

        public void FullUpdate(IOraFile dto)
        {
            var entity = dto as OraFile;
            entity.FullUpdate(true);
            DalHelper.Commit();
        }

        public void Delete(IOraFile dto)
        {
            var entity = dto as OraFile;
            entity.Delete();

            DalHelper.Commit();
        }

        public void SaveOraFileData(int oraFileId, string fileNev, string megjegyzes)
        {
            using (var command = new SDACommand())
            {
                command.Connection = UserContext.Instance.SDAConnection;
                command.Transaction = UserContext.Instance.SDATransaction;
                command.CommandType = CommandType.Text;

                command.Parameters.Add("pOraFileId", oraFileId);
                command.Parameters.Add("pFileNev", fileNev);

                var sb = new StringBuilder(@"UPDATE T_DKT_FILE
                                            SET C_FILENEV = :pFileNev
                                            FROM T_DKT_FILE dktf
                                            INNER JOIN T_ORAFILE orf ON orf.ID = :pOraFileId AND orf.C_DKT_FILEID = dktf.ID ");

                if (megjegyzes != null)
                {
                    command.Parameters.Add("pMegjegyzes", megjegyzes);
                    sb.Append(@" UPDATE T_ORAFILE
                                SET C_MEGJEGYZES = :pMegjegyzes
                                WHERE ID = :pOraFileId");
                }

                command.CommandText = sb.ToString();

                command.ExecuteNonQuery();
                DalHelper.Commit();
            }
        }

        public DataSet GetOraFilesByDateRange(DateTime start, DateTime end, int? oraFileTipus)
        {
            var commandParameterList = new List<CommandParameter>
            {
                new CommandParameter("pStartDate", start.Date),
                new CommandParameter("pEndDate", end.Date),
                new CommandParameter("pOraFileTipus", oraFileTipus)
            };

            StringBuilder commandText = new StringBuilder(@"
                SELECT
                   oraf.ID Id
                  ,oraf.C_DKT_FILEID FileId
                  ,oraf.C_ORADATUM Datum
                  ,CONCAT(dktf.C_FILENEV, '.', dktf.C_EXTENSION) FileNev
                  ,oraf.C_ORARENDIORAID OrarendiOraId
                  ,oraf.C_TANITASIORAID TanitasiOraId
                FROM T_ORAFILE oraf
                INNER JOIN T_DKT_FILE dktf ON dktf.ID = oraf.C_DKT_FILEID
                WHERE :pStartDate <= oraf.C_ORADATUM AND
                    oraf.C_ORADATUM <= :pEndDate AND
                    oraf.C_ISTANULOLATHATO = 'T'");

            if (oraFileTipus.HasValue)
            {
                commandText.Append(" AND oraf.C_ORAFILETIPUSID = :pOraFileTipus");
            }

            return GetData(commandText.ToString(), commandParameterList);
        }
    }
}