; DYNASET
;
; Program for Proteus
;
; (C) 2003-2004 Simone Zanella Productions
;
; This program shows an example of Dynaset, obtained through a query on Sql Server.
; The code extract all customers (CFANAGRA) whose documents (DOC) have in their rows (DOCRIGHE)
; a particular article; the names of the customers found are saved to a text file,
; where each field is separated from the next by the character "|".

#!proteus -z -j

!include "daodefs.prt"

; Allocate database and open DSN "AZIENDA"
DBHandle = DAODBNEW()
Res = DAODBOPEN(DBHandle, "AZIENDA", 0, 0, "DSN=AZIENDA;")

IF EQ(Res, -1)
  ; Error
  PrintErrors(DBHANDLE)
  ABORT 1
FI

; Allocate two recordsets
RSHandle = DAORSNEW(DBHandle)
RSH2 = DAORSNEW(DBHandle)

; Find articles: 5-129002-00, 5-111105-90, 5-112105-90
Query = "SELECT * FROM CFANAGRA INNER JOIN (DOC INNER JOIN DOCRIGHE ON " \
      "DOCRIGHE.IDDOC = DOC.IDDOC) ON DOC.CLIFOR = CFANAGRA.CLIFOR WHERE DOC.TIPODOC = 'FATTURA' AND CFANAGRA.TIPOCLIFOR = 'CLIENTE' " \
      "AND (ARTICOLO = '5-129002-00' OR  ARTICOLO = '5-111105-90' OR  ARTICOLO = '5-112105-90') " \
      "ORDER BY CFANAGRA.CLIFOR DESC, DOC.DTADOC DESC"

; Open the file where the results will go; if the file exists, it is overwritten
H = FOPEN("CLIENTI.TXT", 28)

; Open the query in the first recordset (Dynaset)
Res = DAORSOPEN(RSHandle, DAOCOpenDynaset, Query, 0)

IF EQ(Res, -1)
  ; Errore
  PrintErrors(DBHANDLE)
  ABORT 1
FI

N = 0
; If we found at least one record..
IF NOT(AND(DAORSATTRIB(RSHandle, DAORSATTISBOF), DAORSATTRIB(RSHandle, DAORSATTISEOF)))
  ; Traverse the recordset
  DAORSMOVEFIRST(RSHandle)
  
  WHILE NOT(DAORSEOF(RSHandle))
    ; Find corresponding article
    Res = DAORSOPEN(RSH2, DAOCOpenDynaset, \
          "SELECT * FROM ARTICOLI WHERE ARTICOLO = '" DAORSGETFIELDVAL(RSHandle, "ARTICOLO") "'", 0)

    ; Transcribe relevant fields  
    FWRITELN(H, \
        STRTRAN(STRTRAN(DAORSGETFIELDVAL(RSHandle, "RAGSOC") " |" \
        DAORSGETFIELDVAL(RSHandle, "INDIRIZZO") " |" \
        DAORSGETFIELDVAL(RSHandle, "CAP") " |" \
        DAORSGETFIELDVAL(RSHandle, "CITTA") " |" \
        DAORSGETFIELDVAL(RSHandle, "PROVINCIA") " |" \
        DAORSGETFIELDVAL(RSHandle, "STATO") " |" \
        TOKEN(DAORSGETFIELDVAL(RSHandle, "DOCRIGHE.DtaDoc"), 1, " ") " |" \
        DAORSGETFIELDVAL(RSHandle, "ARTICOLO") " |" \
        DAORSGETFIELDVAL(RSH2, "DESCRI"), CHR(13), " "), CHR(10), " ") )
       
    DAORSCLOSE(RSH2)
    
    DAORSMOVENEXT(RSHandle)
    INC(@N)
  LOOP
FI
; Print the number of records found
CONSOLELN N " record(s) found"

; Close main recordset and result file
DAORSCLOSE(RSHandle)
FCLOSE(H)

; Free the recordsets; free and close database
DAORSFREE(RSHandle)
DAORSFREE(RSH2)
DAODBCLOSE(DBHandle)
DAODBFREE(DBHandle)

ABORT 0


FUNCTION PrintErrors(dbhandle)

; Print all DAO errors resulting from the last operation
numerr = DAOERRCOUNT(dbhandle)
FOR x = 1 TO numerr
  CONSOLELN "Error " x ":" 
  CONSOLELN "  Code       : " DAOGETERRORNUM(dbhandle, x)
  CONSOLELN "  Description: " DAOGETERRORDESC(dbhandle, x)
  CONSOLELN "  Source     : " DAOGETERRORSRC(dbhandle, x)
  CONSOLELN ""
NEXT
RETURN