MKB PepMax Script lcDatum = ShowInfoInputVista("Welcher Stichtag", "Welches Datum zum Stichtag?", DTOC(m_MontagDerWoche(DATE())-1)) IF EMPTY(lcDatum) THEN RETURN ENDIF ldDatum = XTOD(lcDatum) IF EMPTY(ldDatum) THEN ShowErrorVista("Falsches Datum", "Das Datum '%1%' konnte nicht als gültiges Datum erkannt werden.", lcDatum) RETURN ENDIF IF YEAR(ldDatum) != 2020 THEN ShowErrorVista("Falsches Datum", "Dieses Script funktioniert derzeit nur für Stichtage im Jahr 2020.") RETURN ENDIF lcDatumText = ALLTRIM(STR( YEAR(ldDatum) )) + "_" + PADL(ALLTRIM(STR( MONTH(ldDatum) )),2,"0") + "_" + PADL(ALLTRIM(STR( DAY(ldDatum) )),2,"0") ldDatum1 = ldDatum-DAY(ldDatum)+1 lcDatum1Text = ALLTRIM(STR( YEAR(ldDatum1) )) + "_" + PADL(ALLTRIM(STR( MONTH(ldDatum1) )),2,"0") + "_" + PADL(ALLTRIM(STR( DAY(ldDatum1) )),2,"0") ldDatum0 = ldDatum-DAY(ldDatum) lcDatum0Text = ALLTRIM(STR( YEAR(ldDatum0) )) + "_" + PADL(ALLTRIM(STR( MONTH(ldDatum0) )),2,"0") + "_" + PADL(ALLTRIM(STR( DAY(ldDatum0) )),2,"0") TEXT TO lcSQL NOSHOW WITH c_parameter AS ( SELECT 1 AS tag, dbo.GetParamInt(1046) AS feld UNION SELECT 2 AS tag, dbo.GetParamInt(1047) AS feld UNION SELECT 3 AS tag, dbo.GetParamInt(1048) AS feld UNION SELECT 4 AS tag, dbo.GetParamInt(1049) AS feld UNION SELECT 5 AS tag, dbo.GetParamInt(1050) AS feld UNION SELECT 6 AS tag, dbo.GetParamInt(1051) AS feld UNION SELECT 7 AS tag, dbo.GetParamInt(1052) AS feld ), c_sonderstunden AS ( SELECT vkid, 1 AS feld, ISNULL(sonderstunden1,0) AS sonderstunden FROM verkaeuferzev UNION SELECT vkid, 2 AS feld, ISNULL(sonderstunden2,0) AS sonderstunden FROM verkaeuferzev UNION SELECT vkid, 3 AS feld, ISNULL(sonderstunden3,0) AS sonderstunden FROM verkaeuferzev UNION SELECT vkid, 4 AS feld, ISNULL(sonderstunden4,0) AS sonderstunden FROM verkaeuferzev UNION SELECT vkid, 5 AS feld, ISNULL(sonderstunden5,0) AS sonderstunden FROM verkaeuferzev UNION SELECT vkid, 6 AS feld, ISNULL(sonderstunden6,0) AS sonderstunden FROM verkaeuferzev UNION SELECT vkid, 7 AS feld, ISNULL(sonderstunden7,0) AS sonderstunden FROM verkaeuferzev UNION SELECT vkid, 8 AS feld, ISNULL(sonderstunden8,0) AS sonderstunden FROM verkaeuferzev UNION SELECT vkid, 9 AS feld, ISNULL(sonderstunden9,0) AS sonderstunden FROM verkaeuferzev UNION SELECT vkid, 10 AS feld, ISNULL(sonderstunden10,0) AS sonderstunden FROM verkaeuferzev ), c_sonderstunden_summe AS ( SELECT s.vkid, SUM(s.sonderstunden) AS summe FROM c_parameter p INNER JOIN c_sonderstunden s ON p.feld = s.feld GROUP BY s.vkid ), c_besarten AS ( SELECT 1 AS besart, dbo.GetParamString(2777) AS name UNION SELECT 2 AS besart, dbo.GetParamString(2778) AS name UNION SELECT 3 AS besart, dbo.GetParamString(2779) AS name UNION SELECT 4 AS besart, dbo.GetParamString(2780) AS name UNION SELECT 5 AS besart, dbo.GetParamString(2781) AS name UNION SELECT 6 AS besart, dbo.GetParamString(2782) AS name UNION SELECT 7 AS besart, dbo.GetParamString(2783) AS name UNION SELECT 8 AS besart, dbo.GetParamString(2784) AS name UNION SELECT 9 AS besart, dbo.GetParamString(2785) AS name UNION SELECT 10 AS besart, dbo.GetParamString(2786) AS name ) SELECT v.vknr, v.filiale, v.name, v.kurzzeichen, v.filname AS filial_name, v.abt_anzeige AS stammabteilung, v.subabteilung, ISNULL(v.kstnr_anzeige,'') AS kostenstelle, ISNULL(CONVERT(varchar(20),v.eintritt,104),'') AS eintritt, ISNULL(CONVERT(varchar(20),v.austritt,104),'') AS austritt, CONVERT(varchar(20),v.monatsabschluss,104) AS monatsabschluss, v.personalnr AS personalnummer, ( SELECT name FROM peptext WHERE peptext.typ_n = v.saldomodus AND peptext.objekt = 'ZEV_SALDOMODUS' ) AS saldomodus, CASE WHEN v.saldomodus = 1 THEN '(Vorgabe aus Arbeitsplan)' WHEN v.saldomodus = 2 THEN dbo.FormatZeit(s.sollzeitvorgabe)+' Std. pro Woche' WHEN v.saldomodus = 3 THEN dbo.FormatZeit(s.sollzeitvorgabe)+' Std. pro Monat' WHEN v.saldomodus = 4 THEN dbo.FormatZeit(s.sollzeitvorgabe)+' Std. / '+CONVERT(varchar(10),s.solltage)+' Tage pro Woche' WHEN v.saldomodus = 5 THEN dbo.FormatZeit( ( SELECT summe FROM c_sonderstunden_summe WHERE c_sonderstunden_summe.vkid = v.vkid ) )+' Std. pro Woche (Sonderstunden)' ELSE 'Unbekannt' END AS saldomodus_wert, ISNULL('max. '+CONVERT(varchar(10), dbo.FormatZeit(s.deckel_min))+' Std.', '') AS deckelung_fehlstunden, ISNULL('max. '+CONVERT(varchar(10), dbo.FormatZeit(s.deckel_max))+' Std.', '') AS deckelung_überstunden, ISNULL('max. '+CONVERT(varchar(10), dbo.FormatZeit(z.deckel_gesamtsaldo))+' Std.', '') AS deckelung_gesamtüberstunden, ( SELECT name FROM c_besarten WHERE c_besarten.besart = v.beschaeftigungsart ) AS beschäftigungsart, CASE WHEN z.keine_abrechnung = 1 THEN 'Keine Zeitabrechnung!' ELSE '' END AS zeitabrechnung, {abgerechnet} ( SELECT ISNULL(SUM(wert), 0) FROM view_zev_kontobuchungen k WHERE konto = ( SELECT id FROM zev_konten WHERE spezial = 'U' ) AND YEAR(datum) < 2020 AND k.vkid = v.vkid ) AS resturlaub_2019, ( SELECT ISNULL(SUM(wert), 0) FROM view_zev_kontobuchungen k WHERE konto = ( SELECT id FROM zev_konten WHERE spezial = 'U' ) AND YEAR(datum) = 2020 AND typ = 'Anspruch' AND k.vkid = v.vkid ) AS urlaubsanspruch_2020, ( SELECT ISNULL(SUM(wert), 0) FROM view_zev_kontobuchungen k WHERE konto = ( SELECT id FROM zev_konten WHERE spezial = 'U' ) AND YEAR(datum) = 2020 AND datum <= ?tP1 AND typ IN ( 'Abwesenheit', 'Korrektur' ) AND k.vkid = v.vkid ) AS genommener_urlaub_2020, ( SELECT ISNULL(SUM(wert), 0) FROM view_zev_kontobuchungen k WHERE konto = ( SELECT id FROM zev_konten WHERE spezial = 'U' ) AND datum <= ?tP1 AND k.vkid = v.vkid ) AS urlaubsstand_%2%, ( SELECT ISNULL(SUM(wert), 0) FROM view_zev_kontobuchungen k WHERE konto = ( SELECT id FROM zev_konten WHERE spezial = 'K' ) AND YEAR(datum) = 2020 AND datum <= ?tP1 AND typ IN ( 'Abwesenheit', 'Korrektur' ) AND k.vkid = v.vkid ) AS kranktage_2020, dbo.FormatZeit( ( SELECT ISNULL(SUM(wert), 0) FROM view_zev_kontobuchungen_saldo k WHERE YEAR(datum) < 2020 AND k.vkid = v.vkid ) ) AS überstundensaldo_stand_2019_12_31, dbo.FormatZeit( ( SELECT ISNULL(SUM(wert), 0) FROM view_zev_kontobuchungen_saldo k WHERE datum < ?tP3 AND k.vkid = v.vkid ) ) AS überstundensaldo_stand_%6%, dbo.FormatZeit( ( SELECT ISNULL(SUM(wert), 0) FROM view_zev_kontobuchungen_saldo k WHERE datum <= ?tP1 AND k.vkid = v.vkid ) ) AS überstundensaldo_stand_%2% FROM view_verkaeufer_komplett v INNER JOIN verkaeuferzev z ON z.vkid = v.vkid LEFT OUTER JOIN zev_sollzeitvorgaben s ON s.vkid = v.vkid AND s.saldomodus = z.saldomodus AND s.datumvon IS NULL WHERE v.beschaeftigt = 1 ENDTEXT lcAbgerechnet = "" FOR lnM = 1 TO MONTH(ldDatum)-1 lcAbgerechnet = lcAbgerechnet + "ISNULL(CONVERT(varchar(10), ( SELECT a.datum FROM zev_archiv_kopf k INNER JOIN zev_abrechnung a ON k.abrechnung = a.guid WHERE k.jahrmonat = 2020"+PADL(ALLTRIM(STR(lnM)),2,"0")+" AND k.vknr = v.vknr AND k.filiale = v.filiale ), 104), '(fehlt!)') AS "+LOWER(MonatsnameKurz(lnM))+"_2020_abgerechnet, " ENDFOR lcAbgerechnet = lcAbgerechnet + "ISNULL(CONVERT(varchar(10), ( SELECT MAX(datum) FROM zev_abrechnung a INNER JOIN zev_abrechnung_verkaeufer av ON av.abrechnung = a.guid WHERE a.startdatum = ?tP3 AND a.endedatum >= ?tP1 AND av.vkid = v.vkid ), 104), '(fehlt!)') AS "+LOWER(MonatsnameKurz(MONTH(ldDatum)))+"_2020_abgerechnet, " lcSQL = STRTRAN(lcSQL, "{abgerechnet}", lcAbgerechnet) ShowStatus("Daten werden abgefragt...") m_SQLExec(lcSQL, "c_kontenuebersicht", ldDatum, lcDatumText, ldDatum1, lcDatum1Text, ldDatum0, lcDatum0Text ) SELECT c_kontenuebersicht GO TOP MenuDoForm("", "export_grid", "Kontenübersicht", "c_kontenuebersicht")