/* =============================================================================
   PRECHECKER XL - Diagnoza srodowiska Comarch ERP XL przed wdrozeniem myCONSULT
   Wersja  : 0.2 (hardened)
   Autor   : myCONSULT / Lukasz Kaminski
   Licencja uruchomienia: READ-ONLY (same SELECT-y + odczyt metadanych).

   CO ROBI:
     Zbiera informacje o srodowisku XL (wersja SQL, baza, moduly, skala,
     customizacje) potrzebne do bezpiecznego wdrozenia naszych aplikacji.
     Skrypt jest ODPORNY: kazda sekcja jest niezalezna (TRY/CATCH), brak danego
     modulu lub uprawnienia NIE przerywa raportu - zobaczysz adnotacje "pominieto".

   PRYWATNOSC (przeczytaj):
     Skrypt NIC nie wysyla samodzielnie. Operuje lokalnie na Twojej bazie.
     Wyniki opuszczaja Twoja firme DOPIERO gdy SAM je przeslesz do myCONSULT.
     Raport zawiera dane techniczne i liczbowe (np. liczby kontrahentow/towarow,
     nazwy niestandardowych obiektow) - NIE zawiera tresci dokumentow ani danych
     osobowych klientow.

   JAK URUCHOMIC:
     A) SSMS -> Otworz plik -> wybierz baze XL klienta -> Wykonaj (F5).
        Wynik: prawy klik w siatce -> "Zapisz wyniki jako..." (CSV) lub kopiuj.
     B) sqlcmd -S <serwer> -d <baza_XL> -E -i prechecker-xl.sql -o raport.txt
        (-E = logowanie Windows; lub -U <login_read_only> -P <haslo>)

   UPRAWNIENIA:
     Wystarczy konto z rola db_datareader na bazie XL + VIEW DEFINITION.
     NIE uruchamiaj na 'sa' - nie jest potrzebne.

   ZWROT WYNIKOW:
     Najlepiej wgraj raport na stronie: https://sklep.myconsult.com.pl/prechecker
     (bezpieczny upload). Ewentualnie: prechecker@myconsult.com.pl
============================================================================= */

SET NOCOUNT ON;

PRINT N'================================================================================';
PRINT N'  PRECHECKER XL 0.2  |  baza: ' + DB_NAME() + N'  |  data: ' + CONVERT(NVARCHAR(19), GETDATE(), 120);
PRINT N'  WAZNE: podaj recznie wersje XL (Pomoc -> O programie), np. 2025.1.4';
PRINT N'================================================================================';

/* ============================================================================
   SEKCJA 1: SQL SERVER
============================================================================ */
PRINT N'';
PRINT N'== SEKCJA 1: SQL SERVER ==';
BEGIN TRY
    SELECT
        @@SERVERNAME                      AS ServerName,
        SERVERPROPERTY('ProductVersion')  AS ProductVersion,
        SERVERPROPERTY('ProductLevel')    AS ProductLevel,
        SERVERPROPERTY('Edition')         AS Edition,
        SERVERPROPERTY('Collation')       AS Collation,
        @@VERSION                         AS FullVersion;
END TRY
BEGIN CATCH
    PRINT N'  [pominieto] Sekcja 1: ' + ERROR_MESSAGE();
END CATCH

/* ============================================================================
   SEKCJA 2: BAZA DANYCH
============================================================================ */
PRINT N'';
PRINT N'== SEKCJA 2: BAZA DANYCH ==';
BEGIN TRY
    DECLARE @ostatniBackup NVARCHAR(20) = N'(brak dostepu do msdb)';
    BEGIN TRY
        SELECT @ostatniBackup = CONVERT(VARCHAR(10), MAX(backup_finish_date), 120)
        FROM msdb.dbo.backupset
        WHERE database_name = DB_NAME() AND type = 'D';
    END TRY BEGIN CATCH SET @ostatniBackup = N'(brak dostepu do msdb)'; END CATCH

    SELECT
        DB_NAME() AS BazaXL,
        (SELECT CAST(SUM(size) * 8 / 1024 AS BIGINT) FROM sys.master_files WHERE database_id = DB_ID()) AS RozmiarMB,
        (SELECT recovery_model_desc FROM sys.databases WHERE database_id = DB_ID()) AS RecoveryModel,
        (SELECT compatibility_level FROM sys.databases WHERE database_id = DB_ID()) AS CompatibilityLevel,
        (SELECT collation_name FROM sys.databases WHERE database_id = DB_ID()) AS BazaCollation,
        @ostatniBackup AS OstatniBackupPelny;
END TRY
BEGIN CATCH
    PRINT N'  [pominieto] Sekcja 2: ' + ERROR_MESSAGE();
END CATCH

/* ============================================================================
   SEKCJA 3: SLADY WERSJI XL (heurystyka - klient potwierdza recznie)
============================================================================ */
PRINT N'';
PRINT N'== SEKCJA 3: SLADY WERSJI XL (heurystyka) ==';
BEGIN TRY
    SELECT 'KSeF' AS Funkcja,
           CASE WHEN OBJECT_ID('CDN.KSeF') IS NOT NULL OR OBJECT_ID('CDN.KSeFLog') IS NOT NULL THEN 'TAK' ELSE 'nie' END AS Obecnosc
    UNION ALL SELECT 'WMS / lokalizacje',
           CASE WHEN OBJECT_ID('CDN.WMSLokalizacje') IS NOT NULL OR OBJECT_ID('CDN.MagLokalizacje') IS NOT NULL THEN 'TAK' ELSE 'nie' END
    UNION ALL SELECT 'Produkcja - technologie',
           CASE WHEN OBJECT_ID('CDN.ProdTechnologie') IS NOT NULL THEN 'TAK' ELSE 'nie' END
    UNION ALL SELECT 'BI Point',
           CASE WHEN OBJECT_ID('CDN.BIPointRaportDef') IS NOT NULL THEN 'TAK' ELSE 'nie' END;
END TRY
BEGIN CATCH
    PRINT N'  [pominieto] Sekcja 3: ' + ERROR_MESSAGE();
END CATCH

PRINT N'-- Klucze konfiguracyjne ze sladami wersji:';
BEGIN TRY
    IF OBJECT_ID('CDN.Konfig','U') IS NOT NULL
        SELECT TOP 30 Kon_Numer, Kon_Wartosc, Kon_Komentarz
        FROM   CDN.Konfig
        WHERE  Kon_Komentarz LIKE '%ersj%'
            OR Kon_Komentarz LIKE '%2024%' OR Kon_Komentarz LIKE '%2025%' OR Kon_Komentarz LIKE '%2026%'
        ORDER BY Kon_Numer DESC;
    ELSE PRINT N'  [pominieto] brak CDN.Konfig';
END TRY
BEGIN CATCH
    PRINT N'  [pominieto] CDN.Konfig: ' + ERROR_MESSAGE();
END CATCH

/* ============================================================================
   SEKCJA 4: MODULY / FUNKCJONALNOSCI (po obecnosci tabel)
============================================================================ */
PRINT N'';
PRINT N'== SEKCJA 4: MODULY AKTYWNE ==';
BEGIN TRY
    SELECT 'Handel-Magazyn'   AS Modul, CASE WHEN OBJECT_ID('CDN.TraNag') IS NOT NULL THEN 'TAK' ELSE 'nie' END AS Obecnosc UNION ALL
    SELECT 'Zamowienia',       CASE WHEN OBJECT_ID('CDN.ZamNag') IS NOT NULL THEN 'TAK' ELSE 'nie' END UNION ALL
    SELECT 'Ksiegowosc (KH)',  CASE WHEN OBJECT_ID('CDN.KsiegiHan') IS NOT NULL OR OBJECT_ID('CDN.KsgKonta') IS NOT NULL THEN 'TAK' ELSE 'nie' END UNION ALL
    SELECT 'Srodki trwale',    CASE WHEN OBJECT_ID('CDN.SrtKarty') IS NOT NULL THEN 'TAK' ELSE 'nie' END UNION ALL
    SELECT 'Produkcja',        CASE WHEN OBJECT_ID('CDN.ProdZlecenia') IS NOT NULL THEN 'TAK' ELSE 'nie' END UNION ALL
    SELECT 'CRM',              CASE WHEN OBJECT_ID('CDN.CRMKonwersacje') IS NOT NULL OR OBJECT_ID('CDN.CRMSprawy') IS NOT NULL THEN 'TAK' ELSE 'nie' END UNION ALL
    SELECT 'Serwis',           CASE WHEN OBJECT_ID('CDN.SrwZlecenia') IS NOT NULL THEN 'TAK' ELSE 'nie' END UNION ALL
    SELECT 'Obieg dokumentow', CASE WHEN OBJECT_ID('CDN.OBDDokumenty') IS NOT NULL THEN 'TAK' ELSE 'nie' END UNION ALL
    SELECT 'Budzetowanie',     CASE WHEN OBJECT_ID('CDN.BudzetDefinicje') IS NOT NULL THEN 'TAK' ELSE 'nie' END UNION ALL
    SELECT 'WMS',              CASE WHEN OBJECT_ID('CDN.WMSDokumenty') IS NOT NULL OR OBJECT_ID('CDN.AwdNag') IS NOT NULL THEN 'TAK' ELSE 'nie' END UNION ALL
    SELECT 'BI Point',         CASE WHEN OBJECT_ID('CDN.BIPointRaportDef') IS NOT NULL THEN 'TAK' ELSE 'nie' END UNION ALL
    SELECT 'Detaliczna (POS)', CASE WHEN OBJECT_ID('CDN.POSStanowiska') IS NOT NULL OR OBJECT_ID('CDN.POS_Stanowiska') IS NOT NULL THEN 'TAK' ELSE 'nie' END;
END TRY
BEGIN CATCH
    PRINT N'  [pominieto] Sekcja 4: ' + ERROR_MESSAGE();
END CATCH

/* ============================================================================
   SEKCJA 5: SKALA BAZY (odporne - per tabela do tabeli tymczasowej)
============================================================================ */
PRINT N'';
PRINT N'== SEKCJA 5: SKALA BAZY ==';
BEGIN TRY
    IF OBJECT_ID('tempdb..#Skala') IS NOT NULL DROP TABLE #Skala;
    CREATE TABLE #Skala (Lp INT IDENTITY(1,1), Tabela NVARCHAR(60), Wierszy BIGINT NULL, Uwaga NVARCHAR(40) NULL);

    DECLARE @tabs TABLE (lp INT IDENTITY(1,1), label NVARCHAR(60), obj NVARCHAR(128));
    INSERT @tabs (label, obj) VALUES
        ('Kontrahenci','CDN.KntKarty'), ('Towary','CDN.TwrKarty'),
        ('Pracownicy','CDN.PracKarty'), ('Operatorzy XL','CDN.OpeKarty'),
        ('Magazyny','CDN.Magazyny'), ('Atrybuty (def.)','CDN.AtrybutyKlasy'),
        ('Dokumenty handlowe','CDN.TraNag'), ('Dokumenty mag.','CDN.MagNag'),
        ('Zamowienia','CDN.ZamNag');

    DECLARE @label NVARCHAR(60), @obj NVARCHAR(128), @sql NVARCHAR(MAX);
    DECLARE cur CURSOR LOCAL FAST_FORWARD FOR SELECT label, obj FROM @tabs ORDER BY lp;
    OPEN cur; FETCH NEXT FROM cur INTO @label, @obj;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF OBJECT_ID(@obj, 'U') IS NOT NULL
        BEGIN
            SET @sql = N'INSERT #Skala (Tabela, Wierszy) SELECT @l, COUNT_BIG(*) FROM ' + @obj + N';';
            BEGIN TRY EXEC sp_executesql @sql, N'@l NVARCHAR(60)', @l = @label;
            END TRY BEGIN CATCH INSERT #Skala (Tabela, Uwaga) VALUES (@label, N'blad odczytu'); END CATCH
        END
        ELSE INSERT #Skala (Tabela, Uwaga) VALUES (@label, N'brak tabeli');
        FETCH NEXT FROM cur INTO @label, @obj;
    END
    CLOSE cur; DEALLOCATE cur;
    SELECT Tabela, Wierszy, Uwaga FROM #Skala ORDER BY Lp;
    DROP TABLE #Skala;
END TRY
BEGIN CATCH
    PRINT N'  [pominieto] Sekcja 5: ' + ERROR_MESSAGE();
END CATCH

PRINT N'-- Liczby dokumentow wg typu (jesli kolumny istnieja):';
BEGIN TRY
    IF OBJECT_ID('CDN.TraNag','U') IS NOT NULL AND COL_LENGTH('CDN.TraNag','TrN_GIDTyp') IS NOT NULL
        SELECT
            (SELECT COUNT_BIG(*) FROM CDN.TraNag WHERE TrN_GIDTyp = 2033) AS FakturySprzedazy,
            (SELECT COUNT_BIG(*) FROM CDN.TraNag WHERE TrN_GIDTyp = 1521) AS FakturyZakupu;
    ELSE PRINT N'  [pominieto] CDN.TraNag / TrN_GIDTyp';
END TRY BEGIN CATCH PRINT N'  [pominieto] typy TraNag: ' + ERROR_MESSAGE(); END CATCH

BEGIN TRY
    IF OBJECT_ID('CDN.ZamNag','U') IS NOT NULL AND COL_LENGTH('CDN.ZamNag','ZaN_ZamTyp') IS NOT NULL
        SELECT
            (SELECT COUNT_BIG(*) FROM CDN.ZamNag WHERE ZaN_ZamTyp = 1280) AS ZS_Sprzedazy,
            (SELECT COUNT_BIG(*) FROM CDN.ZamNag WHERE ZaN_ZamTyp = 1281) AS ZZ_Zakupu;
    ELSE PRINT N'  [pominieto] CDN.ZamNag / ZaN_ZamTyp';
END TRY BEGIN CATCH PRINT N'  [pominieto] typy ZamNag: ' + ERROR_MESSAGE(); END CATCH

/* ============================================================================
   SEKCJA 6: CUSTOMIZACJE (ryzyko kolizji z naszymi obiektami)
============================================================================ */
PRINT N'';
PRINT N'== SEKCJA 6: CUSTOMIZACJE W BAZIE ==';
PRINT N'-- 6a: Triggery na kluczowych tabelach:';
BEGIN TRY
    SELECT
        OBJECT_NAME(t.parent_id) AS Tabela,
        t.name                   AS TriggerName,
        CASE WHEN t.name LIKE 'my[_]%' THEN 'CUSTOM (my_*)' ELSE 'do weryfikacji' END AS Typ,
        t.is_disabled            AS Wylaczony,
        t.modify_date            AS Zmodyfikowano
    FROM sys.triggers t
    WHERE OBJECT_NAME(t.parent_id) IN ('TraNag','ZamNag','MagNag','TwrKarty','KntKarty','PracKarty','ProdZlecenia')
    ORDER BY OBJECT_NAME(t.parent_id), t.name;
END TRY BEGIN CATCH PRINT N'  [pominieto] 6a: ' + ERROR_MESSAGE(); END CATCH

PRINT N'-- 6b: Niestandardowe obiekty (my_* lub poza schematem CDN/dbo):';
BEGIN TRY
    SELECT SCHEMA_NAME(schema_id) AS Schemat, name AS Obiekt, type_desc AS Typ, modify_date AS Zmodyfikowano
    FROM sys.objects
    WHERE type IN ('P','FN','TF','IF','V')
      AND (name LIKE 'my[_]%' OR SCHEMA_NAME(schema_id) NOT IN ('CDN','dbo','sys'))
    ORDER BY Schemat, name;
END TRY BEGIN CATCH PRINT N'  [pominieto] 6b: ' + ERROR_MESSAGE(); END CATCH

PRINT N'-- 6c: Podsumowanie liczbowe customizacji:';
BEGIN TRY
    SELECT
        (SELECT COUNT(*) FROM sys.triggers WHERE name LIKE 'my[_]%') AS CustomTriggers,
        (SELECT COUNT(*) FROM sys.objects WHERE type IN ('P','FN','TF','IF') AND name LIKE 'my[_]%') AS CustomProcFn,
        (SELECT COUNT(*) FROM sys.objects WHERE type IN ('U','V') AND name LIKE 'my[_]%') AS CustomTabViews,
        (SELECT COUNT(*) FROM sys.objects WHERE type = 'U' AND SCHEMA_NAME(schema_id) NOT IN ('CDN','dbo','sys')) AS TabeleInneSchematy;
END TRY BEGIN CATCH PRINT N'  [pominieto] 6c: ' + ERROR_MESSAGE(); END CATCH

/* ============================================================================
   SEKCJA 7: ATRYBUTY DYNAMICZNE
============================================================================ */
PRINT N'';
PRINT N'== SEKCJA 7: ATRYBUTY DYNAMICZNE ==';
BEGIN TRY
    IF OBJECT_ID('CDN.AtrybutyKlasy','U') IS NOT NULL
        SELECT TOP 40 AtK_ID AS ID, AtK_Nazwa AS Nazwa, AtK_Format AS Format
        FROM CDN.AtrybutyKlasy ORDER BY AtK_Nazwa;
    ELSE PRINT N'  [pominieto] brak CDN.AtrybutyKlasy';
END TRY BEGIN CATCH PRINT N'  [pominieto] Sekcja 7: ' + ERROR_MESSAGE(); END CATCH

/* ============================================================================
   SEKCJA 8: OPERATORZY (odporne na rozne nazwy kolumn)
============================================================================ */
PRINT N'';
PRINT N'== SEKCJA 8: OPERATORZY ==';
BEGIN TRY
    IF OBJECT_ID('CDN.OpeKarty','U') IS NOT NULL
    BEGIN
        DECLARE @ope NVARCHAR(MAX) = N'SELECT COUNT(*) AS LiczbaOperatorow';
        IF COL_LENGTH('CDN.OpeKarty','Ope_Zablokowane') IS NOT NULL
            SET @ope = @ope + N', SUM(CASE WHEN Ope_Zablokowane=0 THEN 1 ELSE 0 END) AS Aktywni'
                            + N', SUM(CASE WHEN Ope_Zablokowane=1 THEN 1 ELSE 0 END) AS Zablokowani';
        SET @ope = @ope + N' FROM CDN.OpeKarty;';
        EXEC sp_executesql @ope;
    END
    ELSE PRINT N'  [pominieto] brak CDN.OpeKarty';
END TRY BEGIN CATCH PRINT N'  [pominieto] Sekcja 8: ' + ERROR_MESSAGE(); END CATCH

PRINT N'';
PRINT N'================================================================================';
PRINT N'  KONIEC RAPORTU. Wgraj wynik: https://sklep.myconsult.com.pl/prechecker';
PRINT N'  Pamietaj o recznym podaniu wersji XL (Pomoc -> O programie).';
PRINT N'================================================================================';
