Sääntöjoukon poiminnan periaatteet – Nepton Support

Huomoithan, että poimintasääntöjen luominen ja muokkaaminen edellyttää perustason ymmärrystä SQL:stä.

Sääntöjoukolla määritetään siirrettävään aineistoon poimittava sisältö. Tässä artikkelissa käydään läpi poiminnan perusperiaatteet ja sääntöjoukkoihin useimmiten tarvittavat lisäsäännöt. Muita poimintasääntöjen luomiseen liittyviä, oleellisia ohjeita löydät seuraavien linkkien takaa:

Yleiskuvaus sääntöjoukoista
Yleistä tietoa siitä, missä muodoissa ja millä tavoin palvelusta voidaan ladata aineistoa sääntöjoukkojen avulla, sekä mistä asetukset löytyvät palvelusta

Tietokantataulujen sisältö
Sääntöjoukon tietokantataulujen sarakekuvaukset.
Poimintasääntöjen mallit
Kattava lista sääntöjoukon poimintasääntöjen malleista, esimerkiksi palkkaliittymälle, poissaoloille ja henkilötiedoille.

Artikkelien malleja voi hyödyntää sellaisenaan, mutta niihin tulee huomioida kohdejärjestelmän vaatimukset esimerkiksi tiedon ryhmittelyn ja mahdollisten järjestelmäkohtaisen tietokenttien osalta. Mallit eivät siis toimi sellaisenaan kaikkiin tapauksiin. Tukipalvelusta löytyviin valmiisiin malleihin tulee usein lisätä tässä artikkelissa käsiteltyjä ryhmittelyn ja rajaamisen sääntöjä.

SQLite muoto yleisesti

Sääntöjoukossa esitetään 100 riviä poimintasäännöille. Kullakin rivillä on poimintasäännön nimi, käytettävä palkkalaji, poimintasääntö ja yksikkötieto (tuntia, kappaletta, desimaalia), jossa tieto siirretään aineistoon.

Poimintasäännöt kirjoitetaan SQL-lausekkeina, käyttäen SQLiten muotoa. Poimintasääntöjen lähdeaineistona on käytettävissä eri tietokantatauluja. Kun aineistoa muodostetaan, käydään jokainen aineistomuodostuksessa valittu henkilö yksitellen läpi ja poimittavassa tietokantataulussa on tiedot vain kyseistä henkilöä koskien.

Poimintasäännöt kirjoitetaan SQL-muotoon:

SELECT ... FROM <taulun nimi> WHERE ...

SQL-kielestä voidaan käyttää myös muuta toiminnallisuutta kuten JOIN, aggrekaattifunktiot (SUM, COUNT, jne).

SQL-syntaksista ja toiminnallisuuksista voit tarkemmin lukea SQLiten omasta dokumentaatiosta.

SQLiten toimintojen lisäksi palveluun on toteutettu apufunktioita, joiden avulla voidaan helpommin käsitellä esimerkiksi merkkijonoja kuin käyttäen SQLiten toiminnallisuutta.

Sääntöjoukon käyttäminen aineistosiirrossa

Sääntöjoukolla kuvataan poiminnan sisältö. Sääntöjoukon muodostamisen jälkeen aineisto voidaan ladata missä tahansa muodossa. Mikäli käytetty aineistomuoto käyttää sellaisia tietokenttiä, joita poimintasäännössä ei ole määritetty, siirtyvät ne aineistomuodosta riippuen oletusarvoisena tai tyhjänä.

Asetusryhmän ylätasolla määritetty sääntö on mahdollista korvata asettamalla aliryhmään toinen sääntö. Asetusryhmien selkeyden vuoksi emme suosittele tätä vaihtoehtoa. Ylätasolle muodostettava sääntö voi jo poissulkea tiedon poiminnan alaryhmässä. Tällöin käyttöympäristöön ei muodosteta alaryhmiin poikkeuksia, vaan kaikkia sääntöjoukon sääntöjä voidaan hallita yhtenä listana. Esimerkiksi jos tietyn palkkalajin muodostuminen halutaan estää alaryhmässä, niin yläryhmän sääntöön voidaan määritellä poissulkeva ehto alla olevan esimerkin mukaisesti. Tällöin sääntöjoukossa nähdään selkeästi aineiston muodostumisen säännöt kokonaisuudessaan ilman jakoa useiden asetusryhmien asetuksiin.

Esimerkki, jossa kysely kohdistetaan UserSalaryData-tauluun ja tuloksista jätetään pois henkilöt asetusryhmästä, jonka koodi on "tuntipalkkaiset urakkamiehet":

SELECT ...,
IFNULL(
  (
    SELECT ui.Value FROM UserInfoData ui
    WHERE ui.InfoTypeName = 'SettingGroupCode_SDSQL' AND
    datetime(UserSalaryData.SalaryRenderingDate) BETWEEN ui.ValidFrom AND ui.ValidTo
    LIMIT 1
  ),
  ''
) AS _SettingGroupCode
FROM UserSalaryData
WHERE _SettingGroupCode NOT IN ('tuntipalkkaiset urakkamiehet')

Poiminnan ryhmittely - GROUP BY

Aineisto muodostuu aina henkilöittäin. GROUP BY -ehdolla voidaan ryhmitellä henkilölle muodostuva aineisto muilla ehdoilla. Ryhmittely laskee yhteen ne rivit, joissa poiminnan ryhmittelyyn määritetyt tiedot ovat samanlaisia.

Pääsääntöisesti palkkajärjestelmiin viedään palkkaliittymässä aineisto siten, että se on ryhmitelty per säännöllä muodostettu palkkalaji (OverrideSalaryCode), per päivä (SalaryRenderingDate) ja mahdollisesti per projektit (ProjectCode1, ProjectCode2, ProjectCode3, ProjectCode4, ProjectCode5, ProjectCode6).

Mikäli on tarpeen ryhmitellä aineisto esimerkiksi tapahtumatyypeittäin, voi ryhmittelyssä käyttää ActivityTypeCodea.

Mikäli taas palkkajärjestelmässä on tärkeää eritellä tapahtumalle tehty kuvaus, niin siinä tapauksessa ryhmitellään vielä per kuvaus (Comment).

Poiminnan rajaaminen

Rajaaminen WHERE-ehdossa

Jos on tarve rajata aineiston ulkopuolelle sellaisia tapahtumia tai esim. lisiä, joissa palkkalajikoodia ei ole asetettu, tällöin tulee sääntöön lisätä WHERE-ehtoon

AND OverrideSalaryCode IS NOT NULL

Sääntöjoukon WHERE-ehdossa voidaan rajata aineiston ulkopuolelle esimerkiksi hyväksymättömät tapahtumat tai palkkauksen mukaisesti esimerkiksi tuntipalkkaiset tai kuukausipalkkaiset tapahtumat:

AND IsApproved = 'Y'
AND employeesalarytype = 'hourly'
AND employeesalarytype = 'monthly'

Rajaaminen HAVING-lausekkeella

Palkkalaji on määritelty poimintasäännössä, tai se tulee esimerkiksi työlisän tai työkorotuksen asetuksista. Poiminnoissa voi määritellä, että palkkalajia 0 ei muodosteta aineistoon HAVING-säännöllä:

HAVING
OverrideSalaryCode != 0

Asettamalla työkorotuksen-, työlisän- tai säännöstä muodostuvan palkkalajin nollaksi, se jätetään pois aineistosta.

Poiminnassa käytettävä henkilön tunniste

Aineistossa muodostuu automaattisesti henkilön tunniste sen mukaisena kuin se on kuvattu liittymäkohtaisessa aineistokuvauksessa. Tämä tunniste voidaan korvata poimimalla säännössä toinen tunniste sarakkeeksi, esimerkiksi 'AS OverridePersonnelNumber'.

Määrä poiminnassa

Määrä eli Amount muodostuu oletuksena sekunteina.
Muutat sekunnit minuuteiksi esim. näin:

SELECT *, Amount / 60 AS OverrideAmount

Otettava huomioon, että vakioitu liittymä tekee muutoksia määrien tulkintaan (tulkitaanko vastaanottavassa päässä sekunteina/minuutteina/tunteina)

Override-sarakenimen käyttö

Kun tietoa siirretään, tietokentät saavat automaattisesti sen sisällön, joka siirrettävässä aineistomuodossa on määritetty. Minkä tahansa kentän arvo voidaan korvata poimimalla SQL-säännöllä sarake, jonka nimi sisältää tekstin Override+sarakkeen nimi.

Esimerkiksi useassa liittymässä siirretään tunti- tai kappalemääriä. Tulkinta voi muodostaa useita rivejä samalle päivälle ja usein näitä tuloksia halutaan laskea yhteen päivätasolla. Tällöin siirtyvä sarake 'Amount' voidaan korvata uudella arvolla poimimalla sisältö sarakkeeksi 'OverrideAmount'.

SELECT *, SUM(Amount) AS OverrideAmount

Vastaavalla tavalla esimerkiksi kommenttikenttään voi liittää muutakin tietoa muodostamalla kyselyssä sarakkeen 'OverrideComment'. Siirtoaineiston sisältämä kenttä voidaan korvata käyttämällä tätä override-sarakenimeä. Tarkemmat tiedot sarakkeiden nimistä on esitetty liittymäkohtaisissa kuvauksissa täällä.

Käsiteltävän ajanjakson määrittely

Sääntöjoukon poimintasäännöt poimivat sisältöä aina joltakin ajanjaksolta. Käsiteltävä ajanjakso määritellään joko sääntöjoukon siirron automatisointi - artikkelissa tai käyttöliittymässä sääntöjoukon tuloksia noudettaessa. Poimintasäännöt kohdistuvat käsiteltävään ajanjaksoon ja palauttavat tulokset siltä ajanjaksolta.

Tapahtumia voidaan poimintasäännössä rajata :exportPeriodStart ja :exportPeriodEnd -muuttujilla. :exportPeriodStart korvataan kyselyä suoritettaessa käsiteltävän jakson alkupäivällä YYYY-MM-DD HH:MM:SS -muotoisena. Vastaavasti :exportPeriodEnd korvataan vietävän jakson loppupäivällä.

Alla esimerkki, jossa poimintaa rajataan kyseisillä muuttujilla.

WHERE
  DATE(EventStartDateTime) BETWEEN :ExportPeriodStart AND :ExportPeriodEnd

Poimintasäännön voimassaolo ja siihen liittyvät ongelmatilanteet

Poimintasäännöille voi asetusten tapaan määrittää voimassaolon, mikä mahdollistaa säännön toiminnan muuttamisen tietyn päivän kohdalla. Jos sääntö on määritetty asetusryhmätasolle, voi sääntö henkilön kohdalla muuttua myös sen takia, että henkilö on vaihdettu toiseen asetusryhmään. Järjestelmä käsittelee tilanteet, joissa sääntö on voimassa vain osan valitusta ajanjaksosta tai muuttuu sen aikana.

Jos käyttäjän kohdalla esiintyy kuvanmukainen tilanne, järjestelmä ajaa säännön ensimmäisen version päiville 1. - 9. ja säännön toisen version 10. päivälle ja siitä eteenpäin. Kummankin sääntöversion tuottamat rivit lisätään lopputulokseen. Jos yhdessä tai molemmissa säännöistä on käytetty jonkinlaista ryhmittelyä tai rivien yhdistämistä (esim. yhteenlaskettu tuntimäärä per henkilö), voi rivien lisääminen kummastakin tuottaa ei-halutun lopputuloksen. Järjestelmä lisää lokiin varoitusviestin tällaisesta tilanteesta.

Jos ylläolevan kuvanmukainen tilanne esiintyy ajetaan käyttäjän työaikatiedot säännön mukaisesti, mutta vain päiville 1. - 9. Lokiin jää viesti tällaisesta tilanteesta. Poimintasäännöstä ja käytettävästä liittymästä riippuen siirtotiedostosta ei välttämättä käy ilmi, että tiedot on poimittu vain osasta valittua ajanjaksoa.

Joskus voi olla tarpeen välttää tilanteita, joissa sääntö muuttuu kesken raportointijakson. Säännön muuttumista kesken raportointijakson voi välttää seuraavasti:

Määritä sääntö työyhteisön asetuksiin. Työyhteisötasolle määritetty sääntö ei muutu vaikka henkilöitä siirrellään asetusryhmien välillä. UserInfo-taulun avulla on mahdollista säännössä poimia vain ne tuntimerkinnät, joiden aikana henkilö kuului esim. tiettyyn palkkaryhmään. Myös asetusryhmää voi käyttää tässä, mutta sääntökyselyn ehtolausekkeessa voi määrittää vain aliryhmiä.
Jos sääntöä tarvitsee muuttaa, aseta muutos voimassaolotyökalua käyttäen niin, että se tapahtuu raportointijaksojen välissä. Esim. jos tiedot siirretään kuukausi kerrallaan tulisi säännön muutos asettaa voimaan kuun alussa.

Ero UserEventData- ja UserSalaryData-taulujen ajan määrässä

Kun tapahtuma merkitään palveluun, merkitty aika on taulussa UserEventData ja ajasta muodostettu tieto taulussa UserSalaryData. Molemmissa tauluissa on kenttä MarkedTime, mutta niiden arvot voivat poiketa toisistaan riippuen siitä, onko tapahtuma tehty reaaliaikaisena eli ns. kellotettuna vai ei.

Reaaliaikasesti merkityn tapahtuman alku- ja loppuajassa on mukana myös sekuntiosuus, esim. alku 08:02:57 ja loppu 16:13:12. Vastaava merkintä ei-reaaliaikaisena olisi alku 08:02:00 ja loppu 16:13:00. UserEventData-taulussa olisi määrä (Amount) 08h10min15sek, UserSalaryData-taulussa määrä olisi 08h11min00sek eli 45 sekuntia enemmän. Tällä on harvoin merkitystä, mutta mikäli tietoja poimitaan molemmista tauluista, saattaa olla tarve saada sama määrä riippumatta tapahtuman merkintätavasta.

Alla olevalla esimerkillä saadaan UserEventData- ja UserSalaryData-taulusta poimittua sama määrä riippumatta tapahtuman merkintätavasta.

CAST(strftime('%s', strftime('%Y-%m-%d %H:%M:00', EndDateTime)) AS INT)
- CAST(strftime('%s', strftime('%Y-%m-%d %H:%M:00', StartDateTime)) AS INT)

Ajat ja aikavyöhykkeet

Tapahtumien ja palkkatietojen alku- ja päättymisajat tallennetaan ja noudetaan "kuten ne on kirjattu" eli käyttäjän tarkoituksen mukaisesti. On tärkeää huomata, että emme välttämättä tiedä aikavyöhykettä, jolla aika on tallennettu, ellei tätä tietoa ole määritelty palveluun.

Esimerkkejä löydät ohjeesta Aikavyöhykkeet ja tapahtumien luonti.

On myös tärkeää huomata, että sääntöjoukon kysely käyttää SQLitea, jonka päivämäärä- ja aika-avustajat käyttävät oletusarvoisesti UTC-aikastandardia.

Tästä seuraa, että jos käytät kyselyssä käsitettä 'nyt' tai CURRENT_TIMESTAMP hakeaksesi nykyisen ajan ja sen muodon, se näytetään 2-3 tuntia todellisesta ajasta jäljessä (olettaen, että olet Suomen aikavyöhykeellä ja riippuen siitä, vaikuttaako kesäaika).

Alla esimerkki mahdollisesti ongelmallisesta kyselystä, joka palauttaa tiedot vain kuun 1. päivänä:

SELECT * FROM UserEventData WHERE strftime('%d', 'now') = '01'

Koska strftimelle ei anneta aikavyöhyketietoja, se käyttää UTC-aikaa ja sen jälkeen kysely ei palauta tuloksia, jos se suoritetaan 00:00 ja 02:00 välillä 1. päivänä (Suomessa). Voimme tarjota palvelun paikallisen aikavyöhykkeen tämän odottamattoman toiminnan korjaamiseksi mm:

SELECT * FROM UserEventData WHERE strftime('%d', 'now', 'localtime') = '01'

Lisätietoja UTC:stä ja aikavyöhykkeistä SQLitessa löytyy SQLiten omasta dokumentaatiosta.

Rajoituksia

Kirjainkoolla on merkitystä muilla kuin ASCII-merkeillä

Jos esimerkiksi poimitaan tapahtumatyypin koodin perusteella, kun koodina on YÖ, koodin jälkimmäinen merkki tulee esittää samalla kirjainkoolla millä se on tapahtumatyypin koodiin asetettu.

Esimerkiksi seuraava lauseke toimii, koska tapahtumatyypin koodin Ö-merkki on kyselyssä isolla kirjankoolla. Pienellä kirjainkoolla kysely ei toimisi:

SELECT * FROM UserSalaryData

WHERE CompensationType = 'BasicTime' AND ActivityTypeCode = 'YÖ'

Voit lukea enemmän ASCII-merkeistä / merkistöstä Wikipedian artikkelista.

Uusia tietotyyppejä saatetaan lisätä myöhemmin

Joihinkin tietokantatauluihin saatetaan lisätä rivejä ja uusia tietotyyppejä järjestelmän päivityksissä. Mikäli kyselyssä rajataan tietoja jonkin tyypin perusteella, kannattaa rajaus tehdä niin, ettei kysely ala päivityksen jälkeen poimimaan myös uusia tietotyyppejä.

Esimerkiksi seuraavissa lausekkeissa on riskinä, että ne poimivat myös jonkin myöhemmin lisätyn työaikatulkinnan.

SELECT * FROM UserSalaryData WHERE CompensationType != 'BasicTime';

SELECT * FROM UserSalaryData WHERE CompensationType NOT IN ('PublicHoliday', 'MarkedTime');