Für alle die die Lagerbewertung auch automatisiert haben möchten, hier unser Ansatz:
SQL
Der Code ist so angepasst, dass der Stichtag immer als Heute gesetzt wird.
Anpassungen sind möglich über die grünen Zeilen, indem das "--" entfernt wird. Mögliche Anpassungen (Stichtag, EKWareneingang oder GLD, Lagerfilter)
Zudem haben wir den Code um einen Lagerfilter erweitert, da wir das
FBA Streckenlager und FBA Pending nicht zusammen erfassen möchten (doppelter Bestand).
In dem Teil "AND tWarenLager.cName IN ( XXXXX )" müsst ihr entsprechend eure Lagernamen einsetzen. Am besten alle kommasepariert einfügen, beim letzten das Komma weglassen und die nicht benötigten mit "--" auskommentieren.
Damit der Steuerberater es leichter hat, gibt´s zum Ende ein kumuliertes Ergebnis aus den gefilterten Lagern.
Code:
DECLARE @Stichtag AS DATE = CAST(GETDATE() AS DATE); -- Stichtag = HEUTE
--DECLARE @Stichtag AS DATE = CONVERT(DATE, '2023.03.01', 102); -- Stichtag Manuell
SELECT ISNULL(tWarenLager.cName, 'Gesamt') AS Lager,
--CAST(CONVERT(FLOAT, SUM(tWarenLagerEingang.fAnzahl * tArtikel.fEKNetto) - SUM(ISNULL(Warenausgang.fAnzahl, 0.0) * tArtikel.fEKNetto))AS DECIMAL(18,2)) AS EKGesamt -- Preise aus GLD des Artikels
CAST(CONVERT(FLOAT, SUM(tWarenLagerEingang.fAnzahl * CASE WHEN tWarenLagerEingang.fEKEinzel > 0.0 THEN tWarenLagerEingang.fEKEinzel ELSE tArtikel.fEKNetto END) - SUM(ISNULL(Warenausgang.fAnzahl, 0.0) * CASE WHEN tWarenLagerEingang.fEKEinzel > 0.0 THEN tWarenLagerEingang.fEKEinzel ELSE tArtikel.fEKNetto END))AS DECIMAL(18,2)) AS EKGesamt -- Preise aus Wareneingang
FROM dbo.tWarenLagerEingang
JOIN dbo.tArtikel ON tWarenLagerEingang.kArtikel = tArtikel.kArtikel
JOIN dbo.tSpracheUsed ON nStandard = 1
JOIN dbo.tArtikelBeschreibung ON tArtikel.kArtikel = tArtikelBeschreibung.kArtikel
AND tSpracheUsed.kSprache = tArtikelBeschreibung.kSprache
AND tArtikelBeschreibung.kPlattform = 1
JOIN dbo.tWarenLagerPlatz ON tWarenLagerEingang.kWarenLagerPlatz = tWarenLagerPlatz.kWarenLagerPlatz
JOIN dbo.tWarenLager ON tWarenLagerPlatz.kWarenLager = tWarenLager.kWarenLager
LEFT JOIN
(
SELECT SUM(ISNULL(tWarenLagerAusgang.fAnzahl, 0.0)) AS fAnzahl,
tWarenLagerAusgang.kWarenLagerEingang
FROM dbo.tWarenLagerAusgang
WHERE ISNULL(tWarenLagerAusgang.dErstellt, GETDATE()) < DATEADD(dd, +1, @Stichtag)
GROUP BY tWarenLagerAusgang.kWarenLagerEingang
) AS Warenausgang ON tWarenLagerEingang.kWarenLagerEingang = Warenausgang.kWarenLagerEingang
WHERE tWarenLagerEingang.dErstellt < DATEADD(dd, +1, @Stichtag)
AND tWarenLager.cName IN (
'Standardlager',
--'Standardlager - FBA (EU): AMAZON',
'Standardlager - WMS-Standardlager',
'WMS-Standardlager',
'WMS-Standardlager - Standardlager',
--'WMS-Standardlager - FBA (EU): AMAZON',
'FBA (EU): AMAZON',
'FBA (EU) (Pending): AMAZON'
)
GROUP BY ROLLUP(tWarenLager.cName);
Zusätzlich haben wir in der Abfrage noch eine Bewertung unseres "Entsorgung" Lagers, für beschädigte Waren. Das ist optional, je nachdem wie Ihr mit beschädigter Ware umgeht.
SQL - Lager "Entsorgung"
Code:
-- Lagerbewertung nur für "Entsorgung"
SELECT 'Entsorgung' AS Lager,
--CAST(CONVERT(FLOAT, SUM(tWarenLagerEingang.fAnzahl * tArtikel.fEKNetto) - SUM(ISNULL(Warenausgang.fAnzahl, 0.0) * tArtikel.fEKNetto))AS DECIMAL(18,2)) AS EKGesamt -- Preise aus GLD des Artikels
CAST(CONVERT(FLOAT, SUM(tWarenLagerEingang.fAnzahl * CASE WHEN tWarenLagerEingang.fEKEinzel > 0.0 THEN tWarenLagerEingang.fEKEinzel ELSE tArtikel.fEKNetto END) - SUM(ISNULL(Warenausgang.fAnzahl, 0.0) * CASE WHEN tWarenLagerEingang.fEKEinzel > 0.0 THEN tWarenLagerEingang.fEKEinzel ELSE tArtikel.fEKNetto END))AS DECIMAL(18,2)) AS EKGesamt -- Preise aus Wareneingang
FROM dbo.tWarenLagerEingang
JOIN dbo.tArtikel ON tWarenLagerEingang.kArtikel = tArtikel.kArtikel
JOIN dbo.tSpracheUsed ON nStandard = 1
JOIN dbo.tArtikelBeschreibung ON tArtikel.kArtikel = tArtikelBeschreibung.kArtikel
AND tSpracheUsed.kSprache = tArtikelBeschreibung.kSprache
AND tArtikelBeschreibung.kPlattform = 1
JOIN dbo.tWarenLagerPlatz ON tWarenLagerEingang.kWarenLagerPlatz = tWarenLagerPlatz.kWarenLagerPlatz
JOIN dbo.tWarenLager ON tWarenLagerPlatz.kWarenLager = tWarenLager.kWarenLager
LEFT JOIN
(
SELECT SUM(ISNULL(tWarenLagerAusgang.fAnzahl, 0.0)) AS fAnzahl,
tWarenLagerAusgang.kWarenLagerEingang
FROM dbo.tWarenLagerAusgang
WHERE ISNULL(tWarenLagerAusgang.dErstellt, GETDATE()) < DATEADD(dd, +1, @Stichtag)
GROUP BY tWarenLagerAusgang.kWarenLagerEingang
) AS Warenausgang ON tWarenLagerEingang.kWarenLagerEingang = Warenausgang.kWarenLagerEingang
WHERE tWarenLagerEingang.dErstellt < DATEADD(dd, +1, @Stichtag)
AND tWarenLager.cName = 'Entsorgung';
Die Automatisierung läuft über eine .bat und die Windows Aufgaben Planung. Zudem wird automatisch eine Mail mit der CSV im Anhang versendet (z.B. an den Steuerberater). Für letzteres benötigt ihr noch "mailsend1.19.exe" und müsst diese in den System32 Ordner packen.
.bat
Ihr müsst die Speicherorte anpassen und eure Maileinstellungen einfügen (siehe bei "ANPASSEN").
Da das Passwort im Klartext ist, solltet ihr extra hierfür eine neue Mail erstellen mit einem individuellen Passwort.
Vieles des Scripts ist für die Benennung der Dateien. Kann natürlich nach belieben angepasst werden. Wir möchten z.B. immer das Format JJMMTT vor unseren Dateien.
Wir müssen Mailsend nutzen, da wir TLS 1.2 benötigen. Je nach Mailprovider etc. gibts eventuell andere Anforderungen.
Leider haben wir den Mailinhalt nicht ohne eine temp Datei erstellen können, daher hier der Umweg.
Code:
@echo off
rem Generiere den dynamischen Dateinamen ANPASSEN
set CSV_FILENAME=%date:~8,2%%date:~3,2%%date:~0,2% Lagerbewertung (%time:~-13,2%.%time:~-8,2%.%time:~-5,2%).csv
set CSV_FILE_PATH="C:\Users\Admin\Desktop\%CSV_FILENAME%"
rem Pfad zu SQL Befehl ANPASSEN
set SQL-Befehl="C:\Users\Admin\Desktop\Lagerwert zum Stichtag.sql"
rem Führe SQLCMD-Befehl aus ANPASSEN
sqlcmd -S PC-NAME\JTLWAWI -d eazybusiness -s ";" -W -i %SQL-Befehl% -o %CSV_FILE_PATH%
rem Ermittle das aktuelle Datum und berechne den Vormonat im Format YY/MM
for /f "delims=" %%a in ('powershell -Command "(Get-Date).AddMonths(-1).ToString('yy.MM')"') do set PREVIOUS_MONTH=%%a
rem Ändere Punkt (.) in Schrägstrich (/)
set "PREVIOUS_MONTH=%PREVIOUS_MONTH:.=/%"
rem Ermittle das aktuelle Datum und formatiere es im deutschen Schema
for /f "delims=" %%a in ('powershell -Command "Get-Date -Format 'dd.MM.yyyy'"') do set CURRENT_DATE=%%a
rem Mailsend Configuration ANPASSEN
set SMTP_SERVER=smtp.domain.de
set SMTP_PORT=587
set SMTP_USERNAME=absender@domain.de
set SMTP_PASSWORD=PASSWORT
set EMAIL_FROM=absender@domain.de
set EMAIL_TO=empfänger@domain.de
set EMAIL_SUBJECT=Lagerbewertung %PREVIOUS_MONTH%
set EMAIL_BODY=Im Anhang finden Sie die Lagerbewertung im CSV-Format. Stichtag ist der %CURRENT_DATE% (%time:~-13,2%:%time:~-8,2%:%time:~-5,2% Uhr).
rem set EMAIL_BODY_FILE="C:\Users\Admin\Desktop\temp_message.txt"
rem set INLINE_EMAIL_BODY_FILE="C:\Users\Admin\Desktop\temp_message.txt,text/plain,i"
set EMAIL_ATTCH_FILE="C:\Users\Admin\Desktop\%CSV_FILENAME%,text/csv,a"
rem Erstelle eine temporäre Nachrichten-Textdatei
rem echo %EMAIL_BODY% > %EMAIL_BODY_FILE%
rem Mailsend command to send email with SSL/TLS and log output ANPASSEN
mailsend1.19.exe -f %EMAIL_FROM% -t %EMAIL_TO% -sub "%EMAIL_SUBJECT%" -smtp %SMTP_SERVER% -port %SMTP_PORT% -auth -user %SMTP_USERNAME% -pass %SMTP_PASSWORD% -starttls -M "%EMAIL_BODY%" -attach %EMAIL_ATTCH_FILE% > "C:\Users\Admin\Desktop\mailsend_log.txt" 2>&1
rem Lösche die temporäre Nachrichten-Textdatei
rem del %EMAIL_BODY_FILE%
Zuletzt noch in der Aufgabenplanung eine neue Aufgabe einstellen mit folgenden Parametern:
Trigger:
Aktion:
Die restlichen Einstellungen so setzen, wie es passt. Wir haben z.B. die Aufgabe noch so eingestellt, dass sie maximal 1 Stunde benötigen darf, bevor sie abgebrochen wird. Und die Ausführung darf sich um 30 Minuten verschieben, damit wir nicht aus Versehen in ein Backup etc. eingreifen oder den Server überlasten.
Als Ergebnis erhalten wir jeden Monat eine CSV die z.B. so aussieht und immer direkt automatisch an den Steuerberater gesendet wird
Betreff: Lagerbewertung JJ/MM
Text: Im Anhang finden Sie die Lagerbewertung im CSV-Format. Stichtag ist der 01.MM.JJJJ ( STUNDE:MINUNTE:SEKUNDE Uhr).
Code:
Lager;EKGesamt
-----;--------
Lager 1;10.00
Lager 2;10.00
Lager 3;10.00
Lager 4;10.00
Gesamt;40.00
(5 Zeilen betroffen)
Lager;EKGesamt
-----;--------
Entsorgung;5.00
(1 Zeilen betroffen)
Falls jemand Hilfe beim Anpassen oder Einrichten braucht, kann er sich gerne melden.
Wenn jemand noch Verbesserungsvorschläge hat, immer gerne her damit!
Anmerkung:
Die Automatisierung ist für eine JTL
Wawi welche auf einem lokalen Rechner läuft. Falls ihr einen Server mit einer richtigen SQL Lizenz nutzt, kann das glaube ich auch simpler umgesetzt werden. Dazu findet man Infos in Google.
Und Danke
@Manuel Pietzsch für das Script!
