DevAndy | Denn ich habe kein Gefallen am Tod dessen, der sterben muss, spricht Gott, der Herr. So kehrt denn um, und ihr sollt leben!

Vue – Konstanten definieren

Konstanten kann man in Vue einfach im Created Hook definieren.

Generell sollte man sich das Arbeiten mit Konstanten angewöhnen.

if(UserTypeId == 7) {…} stellt den Entwickler vor die Frage, was an diesen UserType nun so besonders ist.

Besser: if(UserTypeId == USERTYPE_GUEST) . So weiß jeder, die folgende Logik betrifft den Gastbenutzer

                        created() {
                            this.USERTYPE_GUEST = 7;                            
                        },

created() {

this.USERTYPE_GUEST = 7;

Posted on 27. Mai 2021 by Andreas in Vue

Warning: count(): Parameter must be an array or an object that implements Countable in /var/www/vhosts/hosting149637.a2ebb.netcup.net/httpdocs/wp-content/themes/metro-master/loop.php on line 41

Vue – Directive

Mal angenommen man möchte alle Felder auf einer Seite deaktivieren. Man könnte input für input durchgehen und mit :disabled=”isEditable” das Feld deaktivieren.
Oder man schreibt eine Custom Directive, die diese Logik abbildet: https://vuejs.org/v2/guide/custom-directive.html

Möchte man das global haben:

        Vue.directive("disableall", {            
            bind: function (el, binding) {
                if ((!binding.hasOwnProperty('value')) || binding.value) {
                    const tags = ["input", "button", "textarea", "select"];
                    tags.forEach(tagName => {
                        const nodes = el.getElementsByTagName(tagName);
                        for (let i = 0; i < nodes.length; i++) {
                            nodes[i].disabled = true;
                            nodes[i].tabIndex = -1;
                        }
                    });
                }
            }
        });

Vue.directive("disableall", {

bind: function (el, binding) {

if ((!binding.hasOwnProperty('value')) || binding.value) {

const tags = ["input", "button", "textarea", "select"];

tags.forEach(tagName => {

const nodes = el.getElementsByTagName(tagName);

for (let i = 0; i < nodes.length; i++) {

nodes[i].disabled = true;

nodes[i].tabIndex = -1;

}

});

}

});

Oder per Instanz:

                        directives: {
                            disableall: {
                                // directive definition
                                bind: function (el, binding) {
                                    if ((!binding.hasOwnProperty('value')) || binding.value) {
                                        const tags = ["input", "button", "textarea", "select"];
                                        tags.forEach(tagName => {
                                            const nodes = el.getElementsByTagName(tagName);
                                            for (let i = 0; i < nodes.length; i++) {
                                                nodes[i].disabled = true;
                                                nodes[i].tabIndex = -1;
                                            }
                                        });
                                    }
                                }
                            }
                        }

directives: {

disableall: {

// directive definition

bind: function (el, binding) {

if ((!binding.hasOwnProperty('value')) || binding.value) {

const tags = ["input", "button", "textarea", "select"];

tags.forEach(tagName => {

const nodes = el.getElementsByTagName(tagName);

for (let i = 0; i < nodes.length; i++) {

nodes[i].disabled = true;

nodes[i].tabIndex = -1;

}

});

}

Eingebunden wird das nun mit.

<div v-disableall="!isEditable">
...
</div>

...

</div>

oder

<div v-disableall="true">
...
</div>

...

</div>

oder ohne Bedingung

<div v-disableall>
...
</div>

...

</div>

Posted on 26. Mai 2021 by Andreas in Vue

Cmd Pfad autocomplete funktioniert nicht

Cmd öffnen

reg add "hkcu\software\microsoft\command processor" /v CompletionChar /d 9 /t REG_DWORD /f
reg add "hkcu\software\microsoft\command processor" /v PathCompletionChar /d 9 /t REG_DWORD /f

1 2	reg add "hkcu\software\microsoft\command processor" /v CompletionChar /d 9 /t REG_DWORD /f reg add "hkcu\software\microsoft\command processor" /v PathCompletionChar /d 9 /t REG_DWORD /f

Befehle eingeben
Cmd neustarten und mit cd Tab den Pfad autocompleten lassen 🙂

Posted on 10. April 2021 by Andreas in Allgemein

Applikationsdesign: Datensätze abarbeiten nach Punktesystem

Mir kam mal folgende Problemstellung in die Gedanken:

Mal angenommen ich muss unterschiedliche Aufgaben abarbeiten. Diese befinden sich in einer Warteschleife. So dass sie nacheinander oder auch asynchron parallel abgearbeitet werden sollen.

Jede Aufgabe besitzt eine Priorität. Sagen wie 1 (unwichtig) – 10 (sehr wichtig). In die Warteschleife kommen immer wieder Aufgaben nach. Das Problem ist, es werden die sehr wichtigen immer zuerst abgearbeitet. Die weniger wichtigen kommen daher nie dran und werden nie verarbeitet. Die Lösung ist ein Punktesystem mit Zeitangabe. Die Priorität ist dient dann als Faktor und wird mit der Anzahl Minuten multipliziert. Das ergibt die Summe der Punkte pro Datensatz

Beispiel: Es ist Momentan 12:00 Uhr Mittags.

Datensatz 1: Prio 4, Hinzugefügt um: 11 Uhr, Punkte: 4 * 60 = 240
Datensatz 2: Prio 9, Hinzugefügt um: 8 Uhr, Punkte: 9 * 240 = 2160
Datensatz 3: Prio 2, Hinzugefügt um: 4 Uhr, Punkte: 2 * 480 = 960

Sortiert man diese Liste nun nach Punkten ergibt sich eine neue Reihenfolge

Datensatz 2: Prio 9, Hinzugefügt um: 8 Uhr, Punkte: 9 * 240 = 2160
Datensatz 3: Prio 2, Hinzugefügt um: 4 Uhr, Punkte: 2 * 480 = 960
Datensatz 1: Prio 4, Hinzugefügt um: 11 Uhr, Punkte: 4 * 60 = 240

So hat jeder Datensatz eine Chance drangenommen zu werden. Je länger er in der Warteschleife verweilt, desto höher wird sein Punktesatz.

Posted on 9. April 2021 by Andreas in Allgemein

system-versioned temporal tables oder automatische Historie auf Tabellenbasis

funktioniert ab SQL Server 2016

/*
       System Versionisierung wieder deaktivieren
*/

--ALTER TABLE dbo.MyTable SET (SYSTEM_VERSIONING = OFF)
--ALTER TABLE dbo.MyTable DROP PERIOD FOR SYSTEM_TIME;
--GO
--ALTER TABLE [dbo].[MyTable] DROP CONSTRAINT [DF_MyTable_ValidFrom];
--GO
--ALTER TABLE [dbo].[MyTable] DROP COLUMN [ValidFrom];
--GO
--ALTER TABLE [dbo].[MyTable] DROP CONSTRAINT [DF_MyTable_ValidTo];
--GO
--ALTER TABLE [dbo].[MyTable] DROP COLUMN [ValidTo];

/*
       System Versionisierung aktivieren
*/

-- Neue Spalten hinzugügen: ValidFrom, ValidTo
ALTER TABLE dbo.MyTable ADD 
  [ValidFrom] datetime2(7) GENERATED ALWAYS AS ROW START HIDDEN CONSTRAINT DF_MyTable_ValidFrom DEFAULT DATEADD(SECOND,-1, SYSUTCDATETIME()),
  ValidTo datetime2(7) GENERATED ALWAYS AS ROW END HIDDEN CONSTRAINT DF_MyTable_ValidTo DEFAULT CONVERT(datetime2 (7), '9999-12-31 23:59:59.9999999'),
  PERIOD FOR SYSTEM_TIME ([ValidFrom], ValidTo);
GO

-- History Tabelle erzeugen
ALTER TABLE dbo.MyTable
SET(SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.MyTable_History, DATA_CONSISTENCY_CHECK = ON))
GO

System Versionisierung wieder deaktivieren

--ALTER TABLE dbo.MyTable SET (SYSTEM_VERSIONING = OFF)

--ALTER TABLE dbo.MyTable DROP PERIOD FOR SYSTEM_TIME;

--GO

--ALTER TABLE [dbo].[MyTable] DROP CONSTRAINT [DF_MyTable_ValidFrom];

--GO

--ALTER TABLE [dbo].[MyTable] DROP COLUMN [ValidFrom];

--GO

--ALTER TABLE [dbo].[MyTable] DROP CONSTRAINT [DF_MyTable_ValidTo];

--GO

--ALTER TABLE [dbo].[MyTable] DROP COLUMN [ValidTo];

System Versionisierung aktivieren

-- Neue Spalten hinzugügen: ValidFrom, ValidTo

ALTER TABLE dbo.MyTable ADD

[ValidFrom] datetime2(7) GENERATED ALWAYS AS ROW START HIDDEN CONSTRAINT DF_MyTable_ValidFrom DEFAULT DATEADD(SECOND,-1, SYSUTCDATETIME()),

ValidTo datetime2(7) GENERATED ALWAYS AS ROW END HIDDEN CONSTRAINT DF_MyTable_ValidTo DEFAULT CONVERT(datetime2 (7), '9999-12-31 23:59:59.9999999'),

PERIOD FOR SYSTEM_TIME ([ValidFrom], ValidTo);

-- History Tabelle erzeugen

ALTER TABLE dbo.MyTable

SET(SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.MyTable_History, DATA_CONSISTENCY_CHECK = ON))

Posted on 15. März 2021 by Andreas in SQL

NLog Config – mit ILogger

appsettings.json

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information",
      "Hanfifre": "Information"
    }
  },
  // Konfiguration von NLog.
  "LogConfiguration": {
    "factoryAdapter": {
      "type": "Common.Logging.NLog.NLogLoggerFactoryAdapter, Common.Logging.NLog4412",
      "arguments": {
        "configType": "FILE",
        "configFile": "~/nlog.config"
      }
    }
  },

  "DbConnection": {
    "Provider": "System.Data.SqlClient"
  }
}

{

"Logging": {

"LogLevel": {

"Default": "Information",

"Microsoft": "Warning",

"Microsoft.Hosting.Lifetime": "Information",

"Hanfifre": "Information"

}

// Konfiguration von NLog.

"LogConfiguration": {

"factoryAdapter": {

"type": "Common.Logging.NLog.NLogLoggerFactoryAdapter, Common.Logging.NLog4412",

"arguments": {

"configType": "FILE",

"configFile": "~/nlog.config"

}

"DbConnection": {

"Provider": "System.Data.SqlClient"

}

Program.cs

Host.CreateDefaultBuilder(args)
    .ConfigureWebHostDefaults(webBuilder =>
    {
        webBuilder.UseStartup<Startup>();
    })
    .ConfigureAppConfiguration(
        (hostingContext, config) =>
        {
            
        }
    )
    .ConfigureLogging((hostingContext, logging) =>
    {
        logging.AddConfiguration(hostingContext.Configuration.GetSection("Logging"));
        logging.AddDebug();
        logging.AddNLog();
    })
    .UseNLog();

Host.CreateDefaultBuilder(args)

.ConfigureWebHostDefaults(webBuilder =>

{

webBuilder.UseStartup<Startup>();

})

.ConfigureAppConfiguration(

(hostingContext, config) =>

{

}

)

.ConfigureLogging((hostingContext, logging) =>

{

logging.AddConfiguration(hostingContext.Configuration.GetSection("Logging"));

logging.AddDebug();

logging.AddNLog();

})

.UseNLog();

NuGet Pakete:

NLog
NLog.Config
NLog.Extensions.Logging
NLog.AspNetCore

In der Config XXX ersetzen.

<?xml version="1.0" encoding="utf-8" ?>
<nlog xmlns="http://www.nlog-project.org/schemas/NLog.xsd"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      throwExceptions="false"
      autoReload="true"
      internalLogFile="NLOG_ERROR.log"
      internalLogLevel="Error">

	<!--Basispfade für Logdateien-->
	<variable name="defaultLogDirectory" value="${basedir}/log" />
	<variable name="defaultArchiveDirectory" value="${basedir}/log/archive" />

	<targets async="true">
		<!--Log in eine Datei, Archivierung wenn Datei ~1MB, Maximal 10 logfiles werden aufbewahrt. -->
		<target
            name="traceLog"
            xsi:type="File"
            fileName="${defaultLogDirectory}/trace.log"
            archiveFileName="${defaultArchiveDirectory}/trace.{#####}.zip"
            archiveAboveSize="1000000"
            archiveNumbering="Sequence"
            maxArchiveFiles="10"
            enableArchiveFileCompression="true"
            concurrentWrites="true"
            keepFileOpen="true"
            openFileCacheTimeout="30"
            createDirs="true"
            layout="${date:format=yy-MM-dd HH\:mm\:ss.fff} :: ${level:uppercase=true:padding=-5} :: ${callsite:className=False:fileName=False:includeSourcePath=False:methodName=True} :: ${message} ${onexception:\:\:${exception:format=ToString}}" />

		<!--Debugger, loggt ins Outputwindow von Visual Studio-->
		<target
            xsi:type="Debugger"
            name="dbg"
            layout="${time} :: ${level:uppercase=true} :: ${message}${onexception: \:\: ${exception:format=tostring}}" />

		<target
            xsi:type="ColoredConsole"
            name="terminal"
            layout="${date:format=HH\:mm\:ss.fff} :: ${level:uppercase=true:padding=-5} :: ${callsite:padding=-70} [[${threadid:padding=2}]] :: ${message} ${onexception:\:\:${exception:format=ToString}}" />

		<target xsi:type="BufferingWrapper"
				name="mailBuffer"
				bufferSize="1000"
				flushTimeout="300000"
			    slidingTimeout="false"
                overflowAction="Flush">
			<target xsi:type="Mail"
				name="mail"
				html="true"
				addNewLines="true"
				replaceNewlineWithBrTagInHtml ="true"
				subject="XXX ${level}"
				to="XXX"
				from="XXX-${machinename}@XXX.de"
				header="XXX NLog E-Mail${newline}=========================================================================${newline}"
				body="${newline}
                          Machine: ${machinename}${newline}
                          Date: ${longdate}${newline}
                          Level: ${pad:padding=5:inner=${level:uppercase=true}}${newline}
                          Callsite: ${callsite}${newline}
                          Logger: ${logger}${newline}${newline}

                          Message: ${newline}${message}${newline}${newline}

                          InnerMessages: ${newline}${exception:format=message:maxInnerExceptionLevel=10:innerFormat=message}${newline}${newline}

                          StackTrace: ${newline}${exception:format=toString}"
				footer="${newline}=========================================================================${newline}${newline}"
				smtpUserName=""
				enableSsl="false"
				smtpPassword=""
				smtpAuthentication="None"
				smtpServer="XXX" />
		</target>
	</targets>

	<rules>
		<logger name="*" minlevel="Error" writeTo="mailBuffer" />
		<logger name="*" minlevel="Trace" writeTo="traceLog,terminal,dbg" />
	</rules>
</nlog>

<?xml version="1.0" encoding="utf-8" ?>

<nlog xmlns="http://www.nlog-project.org/schemas/NLog.xsd"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

throwExceptions="false"

autoReload="true"

internalLogFile="NLOG_ERROR.log"

internalLogLevel="Error">

<target

name="traceLog"

xsi:type="File"

fileName="${defaultLogDirectory}/trace.log"

archiveFileName="${defaultArchiveDirectory}/trace.{#####}.zip"

archiveAboveSize="1000000"

archiveNumbering="Sequence"

maxArchiveFiles="10"

enableArchiveFileCompression="true"

concurrentWrites="true"

keepFileOpen="true"

openFileCacheTimeout="30"

createDirs="true"

layout="${date:format=yy-MM-dd HH\:mm\:ss.fff} :: ${level:uppercase=true:padding=-5} :: ${callsite:className=False:fileName=False:includeSourcePath=False:methodName=True} :: ${message} ${onexception:\:\:${exception:format=ToString}}" />

<target

xsi:type="Debugger"

name="dbg"

layout="${time} :: ${level:uppercase=true} :: ${message}${onexception: \:\: ${exception:format=tostring}}" />

<target

xsi:type="ColoredConsole"

name="terminal"

layout="${date:format=HH\:mm\:ss.fff} :: ${level:uppercase=true:padding=-5} :: ${callsite:padding=-70} [[${threadid:padding=2}]] :: ${message} ${onexception:\:\:${exception:format=ToString}}" />

<target xsi:type="BufferingWrapper"

name="mailBuffer"

bufferSize="1000"

flushTimeout="300000"

slidingTimeout="false"

overflowAction="Flush">

<target xsi:type="Mail"

name="mail"

html="true"

addNewLines="true"

replaceNewlineWithBrTagInHtml ="true"

subject="XXX ${level}"

to="XXX"

from="XXX-${machinename}@XXX.de"

header="XXX NLog E-Mail${newline}=========================================================================${newline}"

body="${newline}

Machine: ${machinename}${newline}

Date: ${longdate}${newline}

Level: ${pad:padding=5:inner=${level:uppercase=true}}${newline}

Callsite: ${callsite}${newline}

Logger: ${logger}${newline}${newline}

Message: ${newline}${message}${newline}${newline}

InnerMessages: ${newline}${exception:format=message:maxInnerExceptionLevel=10:innerFormat=message}${newline}${newline}

StackTrace: ${newline}${exception:format=toString}"

footer="${newline}=========================================================================${newline}${newline}"

smtpUserName=""

enableSsl="false"

smtpPassword=""

smtpAuthentication="None"

smtpServer="XXX" />

</target>

</targets>

<rules>

</rules>

</nlog>

Datenbank Tabelle:

CREATE TABLE [Logging].[XXX] (
    [LogId]       INT              IDENTITY (1, 1) NOT NULL,
    [Server]      NVARCHAR (50)    NOT NULL,
    [CreatedDate] DATETIME         NOT NULL DEFAULT GETDATE(),
    [LoggingDate] DATETIME          NOT NULL,
    [CreatedBy]   INT              NULL,
    [Scope]      NVARCHAR (MAX)   NULL,
    [Message]     NVARCHAR (MAX)   NULL,
    [Properties]  NVARCHAR (MAX)   NULL,
    [Level] NVARCHAR(100) NULL, 
    [Exception] NVARCHAR(MAX) NULL, 
    [Logger] NVARCHAR(255) NULL,      
    CONSTRAINT [PK_XXX] PRIMARY KEY CLUSTERED ([LogId] ASC),
    CONSTRAINT [FK_XXX_User] FOREIGN KEY ([CreatedBy]) REFERENCES [dbo].[User] ([UserId])
);

CREATE TABLE [Logging].[XXX] (

[LogId] INT IDENTITY (1, 1) NOT NULL,

[Server] NVARCHAR (50) NOT NULL,

[CreatedDate] DATETIME NOT NULL DEFAULT GETDATE(),

[LoggingDate] DATETIME NOT NULL,

[CreatedBy] INT NULL,

[Scope] NVARCHAR (MAX) NULL,

[Message] NVARCHAR (MAX) NULL,

[Properties] NVARCHAR (MAX) NULL,

[Level] NVARCHAR(100) NULL,

[Exception] NVARCHAR(MAX) NULL,

[Logger] NVARCHAR(255) NULL,

CONSTRAINT [PK_XXX] PRIMARY KEY CLUSTERED ([LogId] ASC),

CONSTRAINT [FK_XXX_User] FOREIGN KEY ([CreatedBy]) REFERENCES [dbo].[User] ([UserId])

);

Posted on 8. Februar 2021 by Andreas in Allgemein

Hilfsklassen für komplette Perioden

    public enum FixPeriod {
        None = 0,
        LastMonth = 1,
        CurrentMonth = 2,
        PreLastMonth = 3,
        CurrentQuarter = 4,
        LastQuarter = 5,
        PreLastQuarter = 6,
        CurrentHalfYear = 7,
        LastHalfYear = 8,
        PreLastHalfYear = 9,
        CurrentYear = 10,
        LastYear = 11,
        PreLastYear = 12
    }

public enum FixPeriod {

None = 0,

LastMonth = 1,

CurrentMonth = 2,

PreLastMonth = 3,

CurrentQuarter = 4,

LastQuarter = 5,

PreLastQuarter = 6,

CurrentHalfYear = 7,

LastHalfYear = 8,

PreLastHalfYear = 9,

CurrentYear = 10,

LastYear = 11,

PreLastYear = 12

}

    public class FixPeriodFrame {
        public DateTime StartDate { get; set; }
        public DateTime EndDate { get; set; }

        public FixPeriodFrame() {

        }

        public FixPeriodFrame(DateTime startDate, DateTime endDate) {
            this.StartDate = startDate;
            this.EndDate = endDate;
        }
    }

public class FixPeriodFrame {

public DateTime StartDate { get; set; }

public DateTime EndDate { get; set; }

public FixPeriodFrame() {

}

public FixPeriodFrame(DateTime startDate, DateTime endDate) {

this.StartDate = startDate;

this.EndDate = endDate;

}

    public static class FixPeriodExtensions {

        public static DateTime GetFirstDayOfYear(DateTime date) => new DateTime(date.Year, 1, 1);

        public static DateTime GetFirstDayOfHalfYearSecondPart(DateTime date) => new DateTime(date.Year, 7, 1);

        public static DateTime GetLastDayOfHalfYearFirstPart(DateTime date) => GetFirstDayOfHalfYearSecondPart(date).AddMilliseconds(-100);
        
        public static DateTime GetLastDayOfYear(DateTime date) => GetFirstDayOfYear(date).AddYears(1).AddMilliseconds(-100);

        public static FixPeriodFrame GetMonth(DateTime date) =>
            new FixPeriodFrame(new DateTime(date.Year, date.Month, 1), new DateTime(date.Year, date.Month, DateTime.DaysInMonth(date.Year, date.Month), 23, 59, 59));
        
        public static DateTime GetQuarterStartDate(int quarter, int year) =>
            quarter switch
            {
                1 => new DateTime(year, 1, 1),
                2 => new DateTime(year, 4, 1),
                3 => new DateTime(year, 7, 1),
                4 => new DateTime(year, 10, 1),                
            };

        public static DateTime GetQuarterEndDate(int quarter, int year) => GetQuarterStartDate(quarter, year).AddMonths(3).AddMilliseconds(-100);

        public static FixPeriodFrame GetQuarter(DateTime date) {
            int quarter = (date.Month + 2) / 3;
            return new FixPeriodFrame(GetQuarterStartDate(quarter, date.Year), GetQuarterEndDate(quarter, date.Year));
        }

        public static FixPeriodFrame GetHalfYear(DateTime date) => (date.Month <= 6) ? new FixPeriodFrame(GetFirstDayOfYear(date), GetLastDayOfHalfYearFirstPart(date)) : new FixPeriodFrame(GetFirstDayOfHalfYearSecondPart(date), GetLastDayOfYear(date));

        public static FixPeriodFrame GetYear(DateTime date) => new FixPeriodFrame(GetFirstDayOfYear(date), GetLastDayOfYear(date));

        public static FixPeriodFrame ToFixPeriodFrame(this FixPeriod fixPeriod) =>
            fixPeriod switch
            {
                FixPeriod.CurrentMonth => GetMonth(DateTime.Now),
                FixPeriod.LastMonth => GetMonth(DateTime.Now.AddMonths(-1)),
                FixPeriod.PreLastMonth => GetMonth(DateTime.Now.AddMonths(-2)),
                FixPeriod.CurrentQuarter => GetQuarter(DateTime.Now),
                FixPeriod.LastQuarter => GetQuarter(DateTime.Now.AddMonths(-3)),
                FixPeriod.PreLastQuarter => GetQuarter(DateTime.Now.AddMonths(-6)),
                FixPeriod.CurrentHalfYear => GetHalfYear(DateTime.Now),
                FixPeriod.LastHalfYear => GetHalfYear(DateTime.Now.AddMonths(-6)),
                FixPeriod.PreLastHalfYear => GetHalfYear(DateTime.Now.AddMonths(-12)),
                FixPeriod.CurrentYear => GetYear(DateTime.Now),
                FixPeriod.LastYear => GetYear(DateTime.Now.AddYears(-1)),
                FixPeriod.PreLastYear => GetYear(DateTime.Now.AddYears(-2)),
                _ => null
            };
    }

public static class FixPeriodExtensions {

public static DateTime GetFirstDayOfYear(DateTime date) => new DateTime(date.Year, 1, 1);

public static DateTime GetFirstDayOfHalfYearSecondPart(DateTime date) => new DateTime(date.Year, 7, 1);

public static DateTime GetLastDayOfHalfYearFirstPart(DateTime date) => GetFirstDayOfHalfYearSecondPart(date).AddMilliseconds(-100);

public static DateTime GetLastDayOfYear(DateTime date) => GetFirstDayOfYear(date).AddYears(1).AddMilliseconds(-100);

public static FixPeriodFrame GetMonth(DateTime date) =>

new FixPeriodFrame(new DateTime(date.Year, date.Month, 1), new DateTime(date.Year, date.Month, DateTime.DaysInMonth(date.Year, date.Month), 23, 59, 59));

public static DateTime GetQuarterStartDate(int quarter, int year) =>

quarter switch

{

1 => new DateTime(year, 1, 1),

2 => new DateTime(year, 4, 1),

3 => new DateTime(year, 7, 1),

4 => new DateTime(year, 10, 1),

};

public static DateTime GetQuarterEndDate(int quarter, int year) => GetQuarterStartDate(quarter, year).AddMonths(3).AddMilliseconds(-100);

public static FixPeriodFrame GetQuarter(DateTime date) {

int quarter = (date.Month + 2) / 3;

return new FixPeriodFrame(GetQuarterStartDate(quarter, date.Year), GetQuarterEndDate(quarter, date.Year));

}

public static FixPeriodFrame GetHalfYear(DateTime date) => (date.Month <= 6) ? new FixPeriodFrame(GetFirstDayOfYear(date), GetLastDayOfHalfYearFirstPart(date)) : new FixPeriodFrame(GetFirstDayOfHalfYearSecondPart(date), GetLastDayOfYear(date));

public static FixPeriodFrame GetYear(DateTime date) => new FixPeriodFrame(GetFirstDayOfYear(date), GetLastDayOfYear(date));

public static FixPeriodFrame ToFixPeriodFrame(this FixPeriod fixPeriod) =>

fixPeriod switch

{

FixPeriod.CurrentMonth => GetMonth(DateTime.Now),

FixPeriod.LastMonth => GetMonth(DateTime.Now.AddMonths(-1)),

FixPeriod.PreLastMonth => GetMonth(DateTime.Now.AddMonths(-2)),

FixPeriod.CurrentQuarter => GetQuarter(DateTime.Now),

FixPeriod.LastQuarter => GetQuarter(DateTime.Now.AddMonths(-3)),

FixPeriod.PreLastQuarter => GetQuarter(DateTime.Now.AddMonths(-6)),

FixPeriod.CurrentHalfYear => GetHalfYear(DateTime.Now),

FixPeriod.LastHalfYear => GetHalfYear(DateTime.Now.AddMonths(-6)),

FixPeriod.PreLastHalfYear => GetHalfYear(DateTime.Now.AddMonths(-12)),

FixPeriod.CurrentYear => GetYear(DateTime.Now),

FixPeriod.LastYear => GetYear(DateTime.Now.AddYears(-1)),

FixPeriod.PreLastYear => GetYear(DateTime.Now.AddYears(-2)),

_ => null

};

}

Posted on 27. Januar 2021 by Andreas in C#

SQL Column löschen inkl. aller Constraints

DECLARE @TableSchema NVARCHAR(255) = '';
DECLARE @TableName NVARCHAR(255) = '';
DECLARE @ColumnName NVARCHAR(255) = '';

DECLARE @sql NVARCHAR(MAX)
WHILE 1=1
BEGIN
    SELECT TOP 1 @sql = N'ALTER TABLE ' + @TableSchema + '.' + @TableName + ' DROP CONSTRAINT ['+dc.NAME+N']'
    FROM sys.default_constraints dc
    LEFT JOIN sys.columns c ON c.default_object_id = dc.object_id
    WHERE 		
        dc.parent_object_id = OBJECT_ID(@TableSchema + '.' + @TableName)
    AND c.name = @ColumnName
    IF @@ROWCOUNT = 0 BREAK
    EXEC (@sql)
END

IF EXISTS (SELECT 1
               FROM   INFORMATION_SCHEMA.COLUMNS
               WHERE  TABLE_SCHEMA=@TableSchema
            AND TABLE_NAME = @TableName
                      AND COLUMN_NAME = @ColumnName
                      )
  BEGIN
      EXEC ('ALTER TABLE ' + @TableSchema + '.' + @TableName + ' DROP COLUMN ' + @ColumnName)
  END
GO