Results 1 to 6 of 6

Thread: Maak een verjaardagskalender in Interbase

  1. #1

    Post

    Stel, je hebt een tabel met leden (of andere namen/adressen), waarin je voor elk lid een geboortedatum kunt invullen.

    En stel nou dat je een verjaardagslijst wilt maken, zodat je alle leden op hun verjaardag een electronische kaart kunt sturen. We kunnen het sturen van de kaart natuurlijk ook automatiseren, zodat je nooit meer iemands verjaardag vergeet (in ieder geval niet de kaart), maar dat valt even buiten het onderwerp van dit artikel.

    Het probleem zit hem in het omzetten van de geboortedata naar een dag en maandnummer, omdat je daarop wilt kunnen sorteren. Interbase bevat standaard maar een paar functies om kolommen te bewerken, maar met een UDF (User Defined Function) kun je zelf functies toevoegen aan Interbase. Nu komt de installatie van Interbase met een UDF waarin een aantal handige functies zitten, waaronder de functie substr die ik in dit artikel gebruik. Voor een volledig overzicht van de functies, kun je in je Interbase installatie directory, in de subdirectory examples\Udf, het bestand ib_udf.sql bekijken.

    Nu volgt een SQL script, dat je met ISQL kunt uitvoeren, dat het voorbeeld van de verjaardagskalender laat zien.

    Code:
    CREATE DATABASE 'c:\verjaardag.gdb' USER 'SYSDBA' PASSWORD 'masterkey';
    
    /*****************************************
     *	s u b s t r
     *	substr(s,m,n) returns the substring 
     *	of s which starts at position m and
     *	ending at position n.
     *****************************************/
    DECLARE EXTERNAL FUNCTION substr 
    	CSTRING(80), SMALLINT, SMALLINT
    	RETURNS CSTRING(80) FREE_IT
    	ENTRY_POINT 'IB_UDF_substr' MODULE_NAME 'ib_udf';
    
    CREATE TABLE LID (
      NAAM VARCHAR(30) NOT NULL,
      GEB_DATUM DATE,
      PRIMARY KEY (NAAM));
    
    CREATE VIEW VERJAARDAGEN (DAG, MAAND, NAAM)
      AS SELECT
        CAST(substr(CAST(GEB_DATUM AS CHAR(10)),9,10) AS INTEGER),
        CAST(substr(CAST(GEB_DATUM AS CHAR(10)),6, 7) AS INTEGER),
        NAAM FROM LID;
    
    COMMIT;
    
    INSERT INTO LID
    VALUES ('Marco Hemmes', '22-JAN-1972');
    INSERT INTO LID
    VALUES ('Rebecca Hemmes', '4-MAY-1997');
    INSERT INTO LID
    VALUES ('Ruben Hemmes', '12-NOV-1999');
    INSERT INTO LID
    VALUES ('Koningin Beatrix', '31-JAN-1938');
    INSERT INTO LID
    VALUES ('Prins Claus', '6-SEP-1926');
    INSERT INTO LID
    VALUES ('Prins Bernhard', '29-JUN-1911');
    INSERT INTO LID
    VALUES ('Prinses Juliana', '30-APR-1909');
    INSERT INTO LID
    VALUES ('Prins Willem-Alexander', '27-APR-1967');
    INSERT INTO LID
    VALUES ('Maxima Zorreguieta', '17-MAY-1971');
    INSERT INTO LID
    VALUES ('Sinterklaas', null);
    
    COMMIT;
    
    SELECT * FROM VERJAARDAGEN ORDER BY MAAND, DAG;
    De uitvoer van dit script, als het wordt gerund met ISQL, ziet er als volgt uit:

    Code:
             DAG        MAAND NAAM
    ============ ============ ==============================
    
              22            1 Marco Hemmes
              31            1 Koningin Beatrix
              27            4 Prins Willem-Alexander
              30            4 Prinses Juliana
               4            5 Rebecca Hemmes
              17            5 Maxima Zorreguieta
              29            6 Prins Bernhard
               6            9 Prins Claus
              12           11 Ruben Hemmes
          <null>       <null> Sinterklaas
    Je kunt dit voorbeeld nadoen, door het script te kopieren en te bewaren als bijvoorbeeld 'verjaardag.sql'. In een dos-prompt start je nu
    Code:
    isql -i verjaardag.sql
    en dan moet je als het goed is dezelfde uitvoer krijgen. Als isql niet in je PATH staat, geef je het volledige pad naar isql op (in de bin directory van de Interbase installatie) en als je een ander wachtwoord hebt ingesteld voor gebruiker SYSDBA dan moet je de eerste regel van het script even aanpassen.

    In het script wordt met
    Code:
    DECLARE EXTERNAL FUNCTION
    een link gelegd naar de ib_udf library, die in de Udf subdirectory van de Interbase installatie staat. In de Interbase handleiding wordt uitgelegd hoe je zelf een UDF library kunt schrijven.

    De tabel LID wordt gevuld met mijn gegevens en die van mijn kinderen en van nog een paar andere mensen. Daarnaast heb ik een VIEW VERJAARDAGEN gemaakt, waarin met de geboortedatum met CAST naar een string wordt omgezet, daarna met substr het maandnummer of dagnummer er uit wordt gehaald en dat wordt weer naar een integer omgezet. De view zelf kan geen sortering bevatten, maar met een ORDER BY MAAND, DAG in het SELECT statement, staat alles mooi op volgorde.

    Kijk voor meer informatie in de Interbase handleiding die je hier kunt downloaden.
    http://www.interbase2000.org/ib_doc.htm (10 MB)

    Marco Hemmes
    mhemmes@bergler.nl
    marco.nldelphi.net

  2. #2
    Stel je wilt alleen d?¡e verjaardagen als resultaat van een bepaalde datum tot een x aantal dagen ná die bepaalde datum, maar de overige verjaardagen niet?

    Deze vraagstelling brengt enkele problemen met zich mee, bijvoorbeeld de filtering op jaaroverschrijdingen die uiteraard ook 100% correct moet verlopen.

    Zelf kwam ik dit probleem een paar jaar geleden tegen. Ik heb toen een oplossing verzonnen voor SQL Server 7.0. Het doel van het SQL script was om serverside het hele probleem op te lossen. Effecientie heeft de nadruk niet op gelegen, omdat verjaardagen over het algemeen niet met 10000den tegelijk worden verwerkt.

    De parameters moeten de volgende waarden krijgen:

    Year1 = jaartal van de gewenste start-datum
    Year2 = jaartal+1 van de gewenste start-datum
    Date1 = gewenste startdatum
    Date2 = gewenste startdatum + aantal dagen voor overzicht + 1

    Het betreffende script toonde voor een pakket dat ik ooit schreef, de verjaardagen van zowel klanten als personeelsleden van de komende x dagen, bij de start van het programma. Tot dit doel is een FULL OUTER JOIN opgenomen met als conditie (1=0) om geen kruisbestuivingen van klanten en personeel te krijgen op één resulterende row. Dit hele stuk kan uiteraard verwijderd worden als uit maar één tabel wordt geretrieved.

    Of het onderstaande IB compatible is weet ik niet, ik gebruik IB niet. Anders: Veel plezier bij het omschrijven. ;-)

    Tot slot nog de opmerking dat als ik het script zo bekijk dat er veel optimalisatie / versimpeling mogelijk LIJKT. Ach ja, een paar jaar geleden was dit één van mijn eerste 20 scripts. Ik daag u uit. ;-)

    Code:
    SELECT 
      PersonalNo,
      ClientNo,
      ISNULL(P.RNaam,C.RNaam) Naam,
      ISNULL(P.RGebDat,C.RGebDat) GebDat,
      ISNULL(P.ROrderFoo,C.ROrderFoo) ROrderFoo
    FROM
    (
        SELECT PersonalNo,Name RNaam,Verjaardag RGebDat,
          (CONVERT(DATETIME,
          CAST(DATEPART(MONTH,Verjaardag) AS VARCHAR)+'/'+
          CAST(DATEPART(DAY,Verjaardag) AS VARCHAR)+'/1800',101)) ROrderFoo
        FROM personal
        WHERE
          (IsActive=1)
        AND
          (
            (
            CONVERT(
              DATETIME,
                CAST(DATEPART(MONTH,Verjaardag) AS VARCHAR)+'/'+
                  CAST(DATEPART(DAY,Verjaardag) AS VARCHAR)+'/'+:Year1,
                101)
              BETWEEN :Date1 AND :Date2
            )
          OR
            (
              CONVERT(
                DATETIME,
                CAST(DATEPART(MONTH,Verjaardag) AS VARCHAR)+'/'+
                  CAST(DATEPART(DAY,Verjaardag) AS VARCHAR)+'/'+:Year2,
                101)
              BETWEEN :Date1 AND :Date2
            )
          )) AS P
    FULL OUTER JOIN
    (
        SELECT ClientNo,ComName RNaam,BirthDate RGebDat, 
          (CONVERT(DATETIME,
          CAST(DATEPART(MONTH,BirthDate) AS VARCHAR)+'/'+
          CAST(DATEPART(DAY,BirthDate) AS VARCHAR)+'/1800',101)) ROrderFoo
        FROM clients
        WHERE
          (IsActive=1)
        AND
          (
            (
            CONVERT(
              DATETIME,
                CAST(DATEPART(MONTH,BirthDate) AS VARCHAR)+'/'+
                  CAST(DATEPART(DAY,BirthDate) AS VARCHAR)+'/'+:Year1,
                101)
              BETWEEN :Date1 AND :Date2
            )
          OR
            (
              CONVERT(
                DATETIME,
                CAST(DATEPART(MONTH,BirthDate) AS VARCHAR)+'/'+
                  CAST(DATEPART(DAY,BirthDate) AS VARCHAR)+'/'+:Year2,
                101)
              BETWEEN :Date1 AND :Date2
            )
          )
    ) AS C
    ON 
      (1=0) 
    ORDER BY
      ROrderFoo
    Last edited by MendriX-oud; 30-Nov-01 at 11:45.
    Mark van der Hijden (Eindhoven)

  3. #3

    Talking

    Zeg, als ik code post, waarom wordt een : D dan toch nog vertaald naar zo'n maffe smiley?
    Mark van der Hijden (Eindhoven)

  4. #4
    Old Navigator Matthijs's Avatar
    Join Date
    Mar 2001
    Location
    Ede, NL. Delphi: Delphi 7/2005 :). Matthijs schrijf je Matthijs
    Posts
    2,199
    ff je bericht edit-en en "disable smileys in this post" aanvinken! Kan bij een 'post' ook direct gebeuren!

    Oops! Marcel heeft alles vertaald dus heet het "Geen smilies in dit bericht"
    What's in a sig?

    Would my posting be less valuable if it didnot have a sig? (Vrij naar William S.)

    Let op de kleine lettertjes. For all postings: e&oe!
    This program performed an illegal function, the police are on their way

  5. #5
    Thanks.
    Mark van der Hijden (Eindhoven)

  6. #6
    Voor diegenen die belangstelling hebben voor een interbase oplossing, gebruikmakend van het script in het artikel, kun je de volgende stored procedure gebruiken:
    Code:
    SET TERM !! ;
    
    CREATE PROCEDURE VERJAARDAGEN_PERIODE (VANJAAR SMALLINT, VANMAAND SMALLINT, VANDAG SMALLINT,
                                     TOTJAAR SMALLINT, TOTMAAND SMALLINT, TOTDAG SMALLINT)
      RETURNS (DAG SMALLINT, MAAND SMALLINT, JAAR SMALLINT, NAAM VARCHAR(30))
      AS
        DECLARE VARIABLE JAARTELLER SMALLINT;
        DECLARE VARIABLE VANJ SMALLINT;
        DECLARE VARIABLE VANM SMALLINT;
        DECLARE VARIABLE VAND SMALLINT;
        DECLARE VARIABLE TOTJ SMALLINT;
        DECLARE VARIABLE TOTM SMALLINT;
        DECLARE VARIABLE TOTD SMALLINT;
      BEGIN
        JAARTELLER = :VANJAAR;
        WHILE (JAARTELLER <= :TOTJAAR) DO
        BEGIN
          IF (JAARTELLER = :VANJAAR) THEN
          BEGIN
            VANJ = :VANJAAR;
            VANM = :VANMAAND;
            VAND = :VANDAG;
          END
          ELSE
          BEGIN
            VANJ = JAARTELLER;
            VANM = 1;
            VAND = 1;
          END
          IF (JAARTELLER = :TOTJAAR) THEN
          BEGIN
            TOTJ = :TOTJAAR;
            TOTM = :TOTMAAND;
            TOTD = :TOTDAG;
          END
          ELSE
          BEGIN
            TOTJ = JAARTELLER;
            TOTM = 12;
            TOTD = 31;
          END
          FOR SELECT DAG, MAAND, :JAARTELLER, NAAM FROM VERJAARDAGEN
              WHERE (MAAND > :VANM OR (MAAND = :VANM AND DAG >= :VAND)) AND
                    (MAAND < :TOTM OR (MAAND = :TOTM AND DAG <= :TOTD))
            INTO :DAG, :MAAND, :JAAR, :NAAM
          DO
            SUSPEND;
          JAARTELLER = JAARTELLER + 1;
        END
      END !!
    
    SET TERM ; !!
    Je roept deze stored procedure bijvoorbeeld met het volgende select statement aan:

    Code:
    SELECT * FROM VERJAARDAGEN_PERIODE(2001,11,1,2002,2,1) ORDER BY JAAR, MAAND, DAG, NAAM;
    Het zal ook wel met een select lukken, maar een stored procedure vond ik wel een mooie oplossing.
    Marco Hemmes

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •