SQL tabel:

   
CREATE TABLE IF NOT EXISTS `personeel` (
  `PERS_NR` int(11) NOT NULL AUTO_INCREMENT,
  `AFDELING` char(10) NOT NULL,
  `VOORNAAM` char(20) NOT NULL,
  `FAMILIENAAM` char(30) NOT NULL,
  `SALARIS` decimal(7,2) NOT NULL,
  `ACTIEF` char(1) NOT NULL,
  PRIMARY KEY (`PERS_NR`),
  UNIQUE KEY `PERS_NR` (`PERS_NR`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
   

In HMG/Harbour-Clipper:

Dit programma selecteert alle het actieve personeel, toont deze in een 'grid'. Een 'knop' zorgt er voor dat je deze selectie in een PDF document wordt opgeslagen en toont het daarna.

#include "hmg.ch"

FUNCTION Main()
/***************/
PUBLIC dbo

PUBLIC XHOST	:= 'hostname'
PUBLIC XDBUSER	:= 'user'
PUBLIC XDBPW	:= 'pasword'
PUBLIC XDBNAME	:= 'dbname'
PUBLIC aPERS_NR := {}

SET CENT ON
SET DATE FRENCH

If !IsWIndowActive (Form_1) 

   DEFINE WINDOW Form_1 ;
      AT 0,0 ;
      WIDTH  600 ;		 
      HEIGHT 600;	 
      TITLE 'Personeel' ;
      BACKCOLOR SILVER ;
      MAIN ;
      ICON  "images\form_1.ico"	

      ON KEY ESCAPE		ACTION {  Form_1.Release }
      ON KEY F1			ACTION {  Form_1.Release }

      DEFINE GRID Grid_Personeel
         ROW 10
         COL 10
         WIDTH  580
         HEIGHT 400
         FONTNAME "Arial" 
         FONTSIZE 10 
         HEADERS {'Nummer', 'Afdeling', 'Voornaam' , 'Familie naam', 'Salaris'  } 
         WIDTHS  {50        , 100,       120,      120               ,   70     }
         JUSTIFY {GRID_JTFY_LEFT, GRID_JTFY_LEFT, GRID_JTFY_LEFT, GRID_JTFY_LEFT, GRID_JTFY_RIGHT    }
         ITEMS Load_Personeel()
      END GRID     

      DEFINE BUTTON BT_1 
         PARENT Form_1 
         ROW    440
         COL    10
         WIDTH  70
         HEIGHT 70
         ACTION  {Print_Personeel()}
         CAPTION ""
         TRANSPARENT .T.
         TOOLTIP 'Print PDF'
         PICTURE "HP_PRINT"
         PICTALIGNMENT TOP
      END BUTTON

   END WINDOW

   CENTER WINDOW   Form_1

   ACTIVATE WINDOW Form_1

ELSE
   Form_1.SetFocus  
ENDIF
 
RETURN NIL

Gebruikte functies:

FUNCTION Print_Personeel()
/******************************/
PUBLIC cPDF  := 'Personeel.PDF'

IF SQL_Connect_1(XHOST,XDBUSER,XDBPW,XDBNAME)  == Nil         // geen connectie
   MSGINFO('Geen SQL connectie', 'NOK' )		 
   RETURN
ENDIF

cQuery1	:= " SELECT PERS_NR, AFDELING, VOORNAAM, FAMILIENAAM, SALARIS  "  
cQuery1	+= " FROM   PERSONEEL     " 
cQuery1	+= " WHERE  ACTIEF =  'J'    " 
cQuery1	+= " ORDER  BY 2,1   " 
cQuery1	:= dbo:Query( cQuery1 )

IF cQuery1:NetErr()                                          
   MSGINFO(cQuery1:Error(), 'NOK' )	
   RETURN
ENDIF


Personeel_PDF_INIT()
Personeel_PDF_CREATE()
Personeel_PDF_END()

dbo:Destroy()

EXECUTE FILE cPDF    

RETURN

FUNCTION Personeel_PDF_INIT()
/**********************************************/
LOCAL lSuccess       := .F.
PUBLIC nCurrentPage  := 1   

SELECT HPDFDOC cPDF TO lSuccess PAPERSIZE HPDF_PAPER_A4
SET HPDFDOC COMPRESS ALL


SET HPDFDOC PASSWORD OWNER "XPASSWORD"
SET HPDFDOC PERMISSION TO COPY
SET HPDFDOC PERMISSION TO PRINT


SET HPDFINFO AUTHOR      TO "PERSONEELS BESTAND"
SET HPDFINFO CREATOR     TO "PERSONEELS BESTAND"
SET HPDFINFO TITLE       TO "PERSONEELS BESTAND"
SET HPDFINFO SUBJECT     TO "PERSONEELS BESTAND"
SET HPDFINFO KEYWORDS    TO "PERSONEELS BESTAND"
SET HPDFINFO DATECREATED TO DATE() TIME TIME()

IF lSuccess
   nCurrentPage   := 1
   START HPDFDOC
ENDIF     

RETURN

FUNCTION Personeel_PDF_END()
/***************************/
nROW  := nROW + 5
 
END HPDFPAGE
END HPDFDOC

RETURN

FUNCTION Personeel_PDF_CREATE()
/******************************/
LOCAL i 

PUBLIC nROW           := 30
PUBLIC nMAX_ROW       := 260  
PUBLIC nTOT_SALARIS   := 0 
PUBLIC nAFD_SALARIS   := 0 

START HPDFPAGE
	SET HPDFDOC ENCODING TO "WinAnsiEncoding"

   Prt_HDR_Personeel_PDF()  

   i := 1
   DO WHILE i <= cQuery1:LastRec() 
      aCurRow1       := cQuery1:GetRow(i)

      // initialisatie afdeling
      cAFD           := aCurRow1:fieldGet(2)
      cKEY           := aCurRow1:fieldGet(2)
      nAFD_SALARIS   := 0

      Prt_Detail1_PDF( 'Afdeling: ' + cAFD, 5) 
      nROW     := nROW + 5

     
      DO WHILE i <= cQuery1:LastRec()  .AND. cAFD == cKEY

         nSALARIS       := aCurRow1:fieldGet(5)   
         Prt_Detail1_PDF( ALLTRIM(STR(aCurRow1:fieldGet(1))), 5) 
         Prt_Detail1_PDF( aCurRow1:fieldGet(3), 20) 
         Prt_Detail1_PDF( aCurRow1:fieldGet(4), 60) 
         Prt_Detail1_PDF( ALLTRIM(STR(aCurRow1:fieldGet(5))), 160 ,'R' ) 

         nROW           := nROW + 5
         nTOT_SALARIS   := nTOT_SALARIS + nSALARIS
         nAFD_SALARIS   := nAFD_SALARIS + nSALARIS
         NEWPAGE()
         
         i++
         aCurRow1       := cQuery1:GetRow(i)
         cKEY           := aCurRow1:fieldGet(2)
      ENDDO  
       
      NEWPAGE()
      // afsluitng afdeling
       
      Prt_Detail1_PDF( 'TOTAAL Afdeling: ' + cAFD, 5) 
      Prt_Detail1_PDF( ALLTRIM(STR(nAFD_SALARIS)), 160 ,'R'  )
      nROW              := nROW + 10 
   ENDDO


   NEWPAGE()

   nROW        := nROW + 10 
   Prt_Detail1_PDF( 'TOTAAL PERSONEEL', 5   )
   Prt_Detail1_PDF( ALLTRIM(STR(nTOT_SALARIS)), 160 ,'R'  )
     

RETURN

FUNCTION Prt_Detail1_PDF( cDET, nCOL, cLR ) 
/*****************************************/
DEFAULT cLR    := 'LEFT'

IF cLR == 'LEFT'
   @  nROW ,  nCOL        HPDFPRINT  cDET    SIZE 8     
ELSE
   @  nROW ,  nCOL        HPDFPRINT  cDET    SIZE 8   RIGHT  
ENDIF

RETURN NIL

FUNCTION Prt_HDR_Personeel_PDF()   
/*******************************/
nROW     := 30

@ 7,5    HPDFPRINT "PERSONEELS BESTAND" SIZE 9 COLOR RED  
@ 7,160  HPDFPRINT DTOC(DATE()) + ' ' + 'Pagina ' + ALLTRIM(STR(nCurrentPage))   SIZE 8 COLOR RED  RIGHT
@ 1, 1   HPDFPRINT RECTANGLE TO 18, 200 PENWIDTH 0 COLOR BLACK ROUNDED
nCurrentPage++ 

Prt_HDR_KOP_Personeel() 

NEWPAGE()

IF nTOT_SALARIS # 0
   NEWPAGE()
   Prt_Detail1_PDF( 'OVERDRACHT', 110, 'R')
   Prt_Detail1_PDF( ALLTRIM(STR(nAFD_SALARIS)), 160 ,'R'  )
   nROW := nROW + 10
ENDIF
RETURN NIL

FUNCTION Prt_HDR_KOP_Personeel() 
/*************************************/
NEWPAGE()
Prt_Detail1_PDF( 'Nr', 5) 
Prt_Detail1_PDF( 'Voornaam', 20) 
Prt_Detail1_PDF( 'Familienaam', 60) 
Prt_Detail1_PDF( 'Salaris', 160,'R') 
  
nROW := nROW + 10 
 
RETURN NIL

FUNCTION NEWPAGE()
/************************/

IF nROW >= nMAX_ROW
 
   Prt_Detail1_PDF( 'OVERDRACHT', 110, 'R')
   Prt_Detail1_PDF( ALLTRIM(STR(nAFD_SALARIS)), 160 ,'R'  )

   END HPDFPAGE

   START HPDFPAGE
      Prt_HDR_Personeel_PDF()  
ENDIF
RETURN

FUNCTION SQL_Connect_1(XHOST,XDBUSER,XDBPW,XDBNAME)
/**********************************************/
dbo := tmysqlserver():new(ALLTRIM(XHOST),ALLTRIM(XDBUSER),ALLTRIM(XDBPW))
IF dbo:NetErr()
   RETURN nil
ENDIF

IF!EMPTY(XDBNAME) 
   dbo:selectdb(XDBNAME)
   IF dbo:NetErr()
      RETURN nil
   ENDIF
ENDIF
 
RETURN dbo

FUNCTION Load_Personeel()
/************************/
IF SQL_Connect_1(XHOST,XDBUSER,XDBPW,XDBNAME)  == Nil         // geen connectie
   MSGINFO('Geen SQL connectie', 'NOK' )		 
   RETURN
ENDIF

cQuery1	:= " SELECT PERS_NR, AFDELING, VOORNAAM, FAMILIENAAM, SALARIS  "  
cQuery1	+= " FROM   PERSONEEL     " 
cQuery1	+= " WHERE  ACTIEF =  'J'    " 
cQuery1	+= " ORDER  BY 2,1   " 
cQuery1	:= dbo:Query( cQuery1 )

IF cQuery1:NetErr()                                          
   MSGINFO(cQuery1:Error(), 'NOK' )	
   RETURN
ENDIF

aPERSONEEL  := {}
FOR i := 1 To cQuery1:LastRec()
   aCurRow1     := cQuery1:GetRow(i)
   AADD(aPERSONEEL  , { aCurRow1:fieldGet(1), aCurRow1:fieldGet(2) , aCurRow1:fieldGet(3), aCurRow1:fieldGet(4), aCurRow1:fieldGet(5) }  )
NEXT i

dbo:Destroy() 
RETURN aPERSONEEL