Program CkmData

Program CkmData slúži na export údajov o použitých licenciách ISIC, ITIC a EURO<26 z vysokej školy do spoločnosti CKM SYTS, ktorá tieto licencie na Slovensku spravuje. Dáta o licenciách program preberá z databázy VŠ a vo formáte XML ich odovzdáva príslušnej webovej stránke systému CKM Online. Zdrojom dát na strane VŠ je buď databáza akademického IS školy, alebo databáza pomocného programu EMštudent. Tento koncept zodpovedá návrhu elektronickej komunikácie VŠ s CKM SYTS od spoločnosti Ipex IT, s.r.o. z roku 2011.

Systém CKM Online v súčasnosti dáta z VŠ nespracúva hneď - len ich mechanicky preberá a odkladá na nočné dávkové spracovanie. Výsledky spracovania a prípadné chybové hlásenia možno skontrolovať "ručne" na webe CKM Online v nasledujúci deň. Okamžité spracovanie údajov a automatické preberanie informácií o výsledku spracovania do IS VŠ je naplánované v ďalšej etape vývoja programu CkmData a treba na to pripraviť aj informačné systémy na VŠ (systémová integrácia).

Štruktúra údajov o licenciách (preukazoch), ktoré sa odosielajú z VŠ do CKM SYTS, je uvedená v dokumente:

Prvá verzia dokumentu vznikla v auguste 2013. V júli 2016 boli do záznamu doplnené 3 nové položky a vyžaduje sa priebežné odosielanie údajov z VŠ na dennej báze, a to pri vzniku nového záznamu o preukaze a pri zmene hodnoty hociktorej z odosielaných položiek.

Osoby zodpovedné za projekt:

  • Mgr. Katarína Cmorejová, Združenie CKM SYTS, Bratislava
    kcmorejova@ckmsyts.sk
  • Mgr. Martina Urbasek, MBA, Združenie CKM SYTS, Bratislava
    murbasek1@ckmsyts.sk
  • Ing. Róbert Orenič, Žilinská univerzita - nasadenie programu do prevádzky na VŠ
    robert.orenic@uikt.uniza.sk
  • Ing. Stanislav Sýkora, Ipex IT, s.r.o. a VŠZSP, Bratislava - návrh, vývoj a dokumentácia programu
    ssykora@ipex.sk, 02 5810 1414, 0905 700 099

História zmien v programe

  • 2013.07      - prvé inštalácie programu na VŠ
  • 2013.07.30 - je opravená šírka objektu na zobrazenie textu XML v hlavnom programe CkmData
  • 2014.05.25 - program je upravený pre databázový systém PostgreSQL
    • Systém nedokáže prevziať z programu parametre SQL príkazov bežným spôsobom (ADO.NET). Preto bola doplnená možnosť vkladania hodnôt parametrov do príkazov SQL formou textovej náhrady (nové konfiguračné prvky SqlParamInsertValues a SqlParamDateFormat).
    • V príkazovej verzii programu zlyhávalo zisťovanie aktuálneho dátumu a času zo servera metódou ExecuteScalar a muselo byť nahradené inou technikou.

[-] Základné informácie a prevzatie programu

Systémové požiadavky na prevádzku programu

  • operačný systém MS Windows 8, 7, Vista, XP alebo Windows Server 2012, 2008, 2003
  • Microsoft .NET Framework 4.0
  • databázový systém Microsoft SQL Server, Oracle, PostgreSQL alebo iný, ku ktorému existuje OLE DB provider
  • prístup na Internet

Prevzatie programu (download)

Download_CkmData.zip

Obsah inštalačného balíka

  • CkmData.exe - základný program na prácu v interaktívnom režime (obsahuje používateľské rozhranie)
  • CkmData.exe.config - konfiguračný súbor k programu
  • CkmDataCmd.exe - pomocný program na prácu v príkazovom režime
  • CkmDataLib.dll - spoločná knižnica k obidvom programom
  • IpexLib.dll, IpexWinControls.dll - podporné knižnice k obidvom programom

Všetky tieto súbory musia byť uložené spolu v jednom adresári.

Overenie funkčnosti programu

Program si prevezmite z uvedeného zdroja a rozbaľte ho v ľubovoľnom adresári. Ak váš počítač spĺňa systémové požiadavky, program "CkmData.exe" by sa mal rozbehnúť bez problémov.

[-] Inštalácia a konfigurácia programu na VŠ

Inštalácia programu

  1. Program nainštalujte priamo na databázový server, alebo na klientsky počítač s nepretržitou prevádzkou, ktorý má prístup na databázový server (najmä ak server beží v prostredí Unix).
  2. V systéme Windows zriaďte používateľský účet CkmData s utajeným heslom a prihláste sa pod ním.
  3. Stiahnite program CkmData z príslušnej webovej lokality a umiestnite ho do vhodného adresára.
  4. Nastavte konfiguračný súbor programu - podrobnejšie ďalej.
  5. Zriaďte prístup do databázového servera pre účet CkmData a dajte mu oprávnenia na čítanie a zápis vašich tabuliek s údajmi o študentských preukazoch a licenciách CKM SYTS.
  6. Na spoluprácu s programom CkmData v databáze pripravte dve uložené procedúry alebo pohľady podľa návodu uvedeného ďalej a právo na ich spúšťanie prideľte účtu CkmData.
  7. Prvý prenos údajov vyskúšajte "ručne" pomocou základného programu CkmData.
  8. Automatický export údajov pomocou bezobslužného programu CkmDataCmd nastavte v systémovom programe Plánovač úloh.

Konfigurácia programu

Konfiguračné parametre pre obidva programy sú uložené v súbore "CkmData.exe.config":

<connectionStrings> <add ...
  connectionString="Provider=SQLOLEDB; Data Source=server; Initial Catalog=databáza; Integrated Security=SSPI" />

Špecifikuje pripojenie k databáze VŠ - typ databázového servera, názov servera, názov databázy a tzv. "trusted connection". Tento typ pripojenia možno použiť vtedy, keď program beží pod účtom CkmData, ktorý má priamy prístup do databázy.

Ak pripojenie k databáze vyžaduje priame uvedenie prihlasovacieho účtu a hesla, namiesto parametra "Security=SSPI" treba použiť parametre "uid=účet; pwd=heslo". Prítomnosť prihlasovacích údajov v konfiguračnom súbore však znamená bezpečnostné riziko.

Pre databázový systém PostgreSQL pripojenie k databáze možno špecifikovať napr. takto:

  • Provider=PostgreSQL OLE DB Provider; Data Source=server; location=databáza; User ID=...; password=...

WebUrl - http://test.syts.sk/components/syts/administracia/xml-import.jsp

Určuje adresu webovej stránky CKM, ktorej treba odovzdať dáta vo formáte XML. Preddefinovaná je adresa pre testovaciu prevádzku.

SqlDateTime - select GetDate()

Príkaz v jazyku SQL, ktorým program získa zo servera aktuálny dátum a čas. V prípade databázového systému Oracle treba použiť príkaz "select sysdate  from dual". Pre databázový systém PostgreSQL treba použiť príkaz "select now()::timestamp".

SqlExport - exec db_Preukazy_CkmExport    -- @DatumOd

Príkaz v jazyku SQL, ktorým program získa zo servera zoznam licencií (preukazov) na export. Preddefinovaný zápis volá uloženú procedúru db_Preukazy_CkmExport a odovzdáva jej parameter @DatumOd typu smalldatetime. Zápis parametra za znakmi "--" má len dokumentačný význam, lebo v jazyku SQL sa považuje za komentár.

Parameter @DatumOd sa v procedúre používa na filtráciu vstupných záznamov. Program CkmData ho odovzdáva volanej procedúre takto:

  • null - majú sa vybrať len nové záznamy, ktoré ešte neboli odoslané do CKM
  • dátum - majú sa vybrať všetky záznamy, ktoré boli vytvorené alebo aktualizované od uvedeného dátumu - bez ohľadu na to, či už niekedy boli odoslané do CKM alebo nie

Namiesto volania procedúry sem možno vpísať ľubovoľný SQL script, ktorý sa vykoná na serveri a prinesie potrebné záznamy na export (select). Odkaz na parameter @DatumOd v takomto prípade treba zapísať pomocou znaku "?". Ak hodnotu parametra v skripte treba použiť viackrát, nemožno opakovane použiť znak "?", ale na začiatku skriptu treba vytvoriť pomocnú premennú:

declare @DatumOd date;  set @DatumOd = ?;
select ... where @DatumOd is null or DatumAktual >= @DatumOd ...

V starších verziách SQL Servera namiesto typu "date" treba použiť "smalldatetime".

Súčasťou údajov vybraných z databázy môže byť aj položka Id (int), ktorá obsahuje identifikátor zdrojového záznamu. Takto nazvaná položka sa nevkladá do výstupných údajov XML - použije sa len na spätné potvrdenie úspešného odoslania údajov v databáze pomocou príkazu SqlExportOk.

SqlExportOk - exec db_Preukazy_CkmExportOk    -- @Datum, @Snr, @Id | @Datum, @Pocet (pre batch)

Príkaz v jazyku SQL, ktorým program potvrdzuje v databáze úspešné odoslanie údajov. Preddefinovaný zápis tu volá uloženú procedúru db_Preukazy_CkmExportOk.

V závislosti od nastavenia konfiguračného parametra SqlExportOkBatch program odovzdáva procedúre tieto parametre:

  • @Datum, @Snr, @Id - ak SqlExportOkBatch = false, program volá procedúru osobitne pre každú licenciu a odovzdáva jej dátum a čas odoslania údajov (smalldatetime), Snr preukazu (bigint alebo varchar) a identifikátor zdrojového záznamu, ak je súčasťou vstupných údajov o licenciách (int).
  • @Datum, @Pocet - ak SqlExportOkBatch = true, program volá procedúru len raz a odovzdáva jej dátum a čas odoslania údajov (smalldatetime) a počet odoslaných preukazov (int),

Namiesto volania procedúry sem možno vpísať SQL script, ktorý sa vykoná na serveri a uloží do databázy potrebné údaje o odoslaných licenciách (update, insert a pod.). Odkazy na parametre v skripte treba zapísať pomocou znaku "?", pričom treba dodržať poradie odovzdávaných parametrov - napr. "update Preukaz  set Export = ?  where Snr = ? and Id = ?". Ak hodnotu parametra v skripte treba použiť opakovane alebo v inom poradí, na začiatku skriptu treba vytvoriť pomocné premenné:

declare @Datum smalldatetime, @Snr bigint, @Id int;  set @Datum = ?;  set @Snr = ?; set @Id = ?;
update Preukaz  set Export = @Datum  where Snr = @Snr and Id = @Id

Ak tento konfiguračný parameter je prázdny alebo je označený ako komentár (začína znakmi "--"), program nepotvrdzuje odoslanie údajov v databáze.

SqlExportOkBatch - False

Určuje spôsob potvrdzovania úspešne odoslaných údajov v databáze:

  • False - osobitne pre každú licenciu (preukaz)
  • True - súhrnne za celú dávku licencií (preukazov)

Účinok tohto parametra je vysvetlený vyššie.

SqlParamInsertValues - False

Určuje spôsob odovzdávania parametrov volania SQL príkazov z programu do databázového systém:

  • False - parametre príkazov SQL sa odovzdávajú štandardnou technikou ADO.NET
  • True - parametre príkazov SQL sa vkladajú priamo do príkazov tak, že symbolický názov parametra sa nahradí konkrétnou hodnotou parametra v tvare literalu príslušného typu

Hodnota "True" sa používa pri práci s databázovým systémom PostgreSQL, ale môže byť užitočná aj pri práci so systémom Oracle.

SqlParamDateFormat - '{0:yyyy-MM-dd HH:mm:ss}'::timestamp

Určuje formát vkladaného dátumu a času v príkazoch SQL v režime SqlParamInsertValues=True. Formát hodnoty zodpovedá metóde ToString(dDateTime) v programovacom jazyku C#.

Predefinovaná hodnota konfiguračného prvku je vhodná pre databázový systém PostgreSQL. Napr. v príkaze SqlExport spôsobí, že symbolický parameter @DatumOd sa nahradí hodnotou '2014-05-25 14:31:05'::timestamp. To je konkrétny dátum a čas v tvare reťazca, ktorý sa pretypuje (konvertuje) na interný dátový typ pre dátum a čas. Ak odovzdávaný parameter nemá definovanú hodnotu, symbolický parameter sa nahradí reťazcom "null".

Názvy symbolických parametrov v príkazoch treba písať presne podľa daných vzorov, t.j. s úvodným znakom "@" a s dodržaním malých a veľkých písmen. Symbolické parametre možno použiť v príkazoch SQL aj opakovane.

[-] Uložené procedúry na prácu s databázou

Názvy procedúr, ktoré sú preddefinované v konfiguračnom súbore programu, používa autor programu na VŠZSP v Bratislave. Pre iné VŠ, ktoré používajú pomocný program EMštudent, sú určené obdobné procedúry s názvami f_CKM_export a f_CKM_export_OK, ktorých výpis tu uvádzame ako vzor. Obdobné procedúry treba vytvoriť pre ostatné typy zdrojových databáz na VŠ.

Údaje určené na export, ktoré program získa z databázy pomocou príslušnej procedúry (konfiguračný parameter "SqlExport"), program dodatočne upraví takto:

  • hodnoty "null" nahradí prázdnym reťazcom
  • z textových hodnôt odstráni začiatočné a koncové medzery (trim)

Namiesto uložených procedúr možno s údajmi pracovať aj pomocou pohľadov, ale to býva trochu komplikovanejšie.

Uložená procedúra f_CKM_export

Procedúra je odvodená z "pohľadu", ktorý sa používal v staršom programe na komunikáciu VŠ s CKM. V porovnaní s ním je však podstatne kratšia, jednoduchšia, prehľadnejšia a na viacerých miestach aj funkčne zdokonalená.

??? Vzorové uložené procedúry pre databázu EMStudent a rok 2016 sem budú doplnené po ich prevádzkovom overení ???

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER procedure [dbo].[f_CKM_export]

  @DatumOd smalldatetime = null

as

begin

 

set nocount on;

 

 

declare @Dnes datetime, @RokAkad int, @DatumMin datetime, @Univerzita varchar(60);

 

set @Dnes       = GetDate();

set @RokAkad    = Year(@Dnes) - case when Month(@Dnes) >= 9 then 0 else 1 end;

set @DatumMin   = '20010101';

set @Univerzita = (select Txt_Poloz  from VSFAKU  where Kod_Poloz = (select Univerzita  from Nastavenia));

 

 

if @DatumOd is null

begin

   set @DatumOd = IsNull((select top 1 Date  from Ckm_Export_Log  where Pocet > 0  order by Id desc), @Dnes);

 

   if DateDiff(day, @DatumOd, @Dnes) = 0

      set @DatumOd = DateAdd(day, DateDiff(day, 0, @DatumOd), 0);

end;

 

 

select distinct

       Left(IsNull(CD05_TitulPredMenom, ''),                16)  as Titul1,

       Left(       CD06_Meno,                               32)  as Meno,

       Left(       CD07_Priezvisko,                         40)  as Priezvisko,

       Left(IsNull(CD08_TitulZaMenom,   ''),                11)  as Titul2,

       Convert(char(10), CD09_DatumNarodenia,              126)  as DatumNarodenia,

       Pohlavie                                                  as Pohlavie,

       Left(LTrim(RTrim(RTrim(Ulica) + ' ' + CisloDomu)),   50)  as Ulica,

       Left(Mesto,                                          50)  as Mesto,

       PSC                                                       as PSC,

       IsNull(Stat.Skratka, '')                                  as Stat,

       case when funkcia = 1       then '9'

            when funkcia = 2       then '3'

            when funkcia in (3, 8) then '4'

            when funkcia = 4       then '5' end                  as SocialnyUdaj,

       Left(@Univerzita,                                    19)  as Skola,

       Left(CD10_FakultaCislo,                               4)  as Fakulta,

       Convert(char(10),

         Cast(Str(Year(KoniecPlatnPreuk) - 1, 4) + '0901'

              as datetime),                                126)  as PlatnostOd,

       Left(Replace(ISIC_ITIC, ' ', ''),                    15)  as JCP,

       case when Cast(SNR as bigint) >= 0

            then Cast(SNR as bigint)

            else Cast(SNR as bigint) & 0xFFFFFFFF end            as SNR,

       case case when TypProlZnamky > 0

                 then TypProlZnamky

                 else TypPreukazu   end

            when 1 then 'ISIC'

            when 2 then 'ITIC'

            when 4 then 'EURO26' end                             as Druh_karty,

       Convert(char(10), KoniecPlatnPreuk,                 126)  as PlatnostDo,

       Left(Email,                                          50)  as Email,

       case   when IsNull(CisloMobil, '') <> '' then 'A'

                                                else ' ' end     as Mobil,

       Left(

         case when IsNull(CisloMobil, '') <> ''

              then TelPredvolbaStat + PredvolbaMobil    + CisloMobil

              else TelPredvolbaStat + TelPredvolbaMesto + Telefon

              end,

                                                            20)  as Telefon,

       Convert(char(10),

         case when DatumTlace       >= IsNull(DatumDuplikat,    @DatumMin)

               and DatumTlace       >= IsNull(DatumVydanProlZn, @DatumMin)  then DatumTlace

              when DatumDuplikat    >= IsNull(DatumVydanProlZn, @DatumMin)  then DatumDuplikat

              when DatumVydanProlZn is not null                             then DatumVydanProlZn

                                                                            else @DatumMin

              end,

                                                           126)  as DatumAktual

  into #Ckm

  from PersonalneUdaje

  left join Stat  on Stat.StatID = PersonalneUdaje.Obcianstvo

  where Year(KoniecPlatnPreuk)  > @RokAkad

    and Del = 0

    and case when TypProlZnamky > 0

             then TypProlZnamky

             else TypPreukazu   end in (1, 2, 4);

 

 

select *  from #Ckm  where DatumAktual >= @DatumOd  order by DatumAktual, JCP;

 

end

Uložená procedúra f_CKM_export_OK

V nasledovnom znení sa procedúra používa v dávkovom režime, t.j. evidujú sa len súhrnné údaje o odoslanej dávke záznamov.

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[f_CKM_export_OK]

 @Datum smalldatetime,

 @Pocet int

as

begin

 

set nocount on;

  

INSERT INTO ckm_export_log (date, pocet) VALUES (@Datum, @Pocet)

 

end

[-] Interaktívna práca s programom CkmData

Na experimentovanie a jednorazový prenos údajov o licenciách (preukazoch) do CKM slúži program CkmData.exe. Program má jednoduché ovládanie:

CkmData - program

Výber údajov z databázy

Prepínač "Záznamy:" určuje, aké záznamy sa vyberú z databázy:

  • Nové - vyberú sa len ešte neodoslané záznamy.
  • Predchádzajúci mesiac - vyberú sa záznamy vytvorené od začiatku predchádzajúceho mesiaca.
  • Od - vyberú sa záznamy vytvorené od zadaného dátumu.

Po stlačení tlačidla "Prevziať údaje" sa záznamy vyberú z databázy a zobrazia sa na kartách:

  • Zoznam vybraných licencií - v tabuľkovej forme (v názve karty je uvedený počet vybraných záznamov).
  • Súbor údajov na export v tvare XML - vo výslednom tvare na odoslanie.

Výber údajov zo súboru XML

Prepínač "Záznamy:" možno použiť aj na výber záznamov z už existujúceho súboru XML:

  • Zo súboru XML - po stlačení tlačidla "Prevziať údaje" program ponúkne dialóg na vyhľadanie vstupného súboru, z ktorého sa načítajú údaje o licenciách.

Takto možno odoslať údaje, ktoré sme si predtým uložili na disk pomocou tlačidla "Uložiť .XML".

Uloženie údajov do súboru XML

Údaje pripravené na odoslanie alebo už odoslané možno uložiť na disk pomocou tlačidla "Uložiť .XML". Takto uchovaný súbor XML možno neskôr použiť na kontrolu spracovaných údajov alebo na opakované odoslanie do CKM.

Odoslanie údajov do CKM

Údaje prevzaté z databázy alebo súboru XML sa odošlú do CKM stlačením tlačidla "Odoslať údaje". Údaje sa odošlú presne v takom tvare, ako ich vidno na karte "Súbor údajov na export v tvare XML".

[-] Použitie programu v príkazovom režime - CkmDataCmd

Na pravidelný export údajov o licenciách (preukazoch) do CKM slúži program CkmDataCmd.exe. Program je určený na prácu v príkazovom režime - nemá grafické používateľské rozhranie.

Experimentovať s programom možno v príkazovom okne systému (cmd). Po zavolaní bez parametrov program zobrazí takýto návod na použitie:

CKM SYTS - Export udajov o licenciach ISIC, ITIC a EURO<26 v prikazovom rezime

Copyright c 2013-2014, Ipex IT, Bratislava

Ipex IT, s.r.o., P.O.Box 9, 830 05 Bratislava, Slovakia

Ing. Stanislav Sykora, ssykora@ipex.sk

Ver. 2014.05.25

 

Parametre volania:

nove      - vyberu sa len este neodoslane zaznamy

mesiac    - vyberu sa zaznamy vytvorene od zaciatku predchadzajuceho mesiaca

datum ... - vyberu sa zaznamy vytvorene od zadaneho datumu

            (format podla nastavenia operacneho systemu - napr. dd.mm.rrrr)

Automatické spúšťanie programu v určitú dennú alebo večernú dobu možno načasovať pomocou systémového programu Plánovač úloh. Na takéto volanie programu odporúčame pripraviť osobitný príkazový súbor (bat), ktorý treba umiestniť v inštalačnom adresári programu:

CkmDataCmd

Príkazový súbor _CkmDataCmd.bat obsahuje len jeden riadok:

CkmDataCmd nove >>_CkmDataCmd.txt

Jeho spustenie v Plánovači úloh treba nastaviť takto:

Task Scheduler

Presmerovanie textových výstupov programu v príkazovom súbore spôsobí, že po prvom spustení programu vznikne súbor _CkmDataCmd.txt, ktorý neskôr môže obsahovať napr. takýto protokol:

25.7.2013 22:00:04 Export udajov bol uspesny, pocet zaznamov: 1

26.7.2013 22:00:09 Nie su k dispozicii ziadne zaznamy na export.

27.7.2013 22:00:09 Nie su k dispozicii ziadne zaznamy na export.

28.7.2013 22:00:16 Nie su k dispozicii ziadne zaznamy na export.

29.7.2013 22:00:09 Nie su k dispozicii ziadne zaznamy na export.

30.7.2013 22:00:09 Nie su k dispozicii ziadne zaznamy na export.

31.7.2013 22:00:10 Export udajov bol uspesny, pocet zaznamov: 1

1.8.2013 22:00:10 Nie su k dispozicii ziadne zaznamy na export.

Obdobný príkazový súbor možno pripraviť a načasovať na súhrnný mesačný export údajov.

Spúšťanie programu pomocou príkazového súboru je výhodné aj preto, že Plánovač úloh program vykoná aj vtedy, keď program bol aktualizovaný. Ak by Plánovač úloh vykonával priamo "exe", po aktualizácii programu by jeho novšiu verziu nespustil.

Večerné odoslanie údajov treba načasovať najneskôr na 23.30 (radšej skôr), lebo o 00.00 CKM štartuje nočné spracovanie všetkých súborov XML prevzatých za predchádzajúci deň z VŠ.

[-] Integrácia programu do IS vysokej školy - CkmDataLib.dll

Jadro programu, ktoré komunikuje s databázou VŠ a s webovým portálom CKM Online, sa nachádza v knižnici CkmDataLib.dll. Tú možno integrovať priamo do akademického IS VŠ alebo programu EMštudent a volať tak programovo kedykoľvek podľa potreby. Táto možnosť však bude zaujímavá až vtedy, keď portál CKM Online bude údaje spracúvať hneď po ich prevzatí z VŠ.