sexta-feira, 17 de setembro de 2010

Reportmanager in Windows/Linux with the same source

ReportManager can also be used in Harbour in another way, via shared libraries. In the next entry i will show you how to do it. The code sample connects to a free online mysql server, browse a customer table and allows to tag/untag the desired records and to print a list of the selected customers.

The result is this :


and the source :

#include "dbinfo.ch"
#include "error.ch"
#include "hbrddsql.ch"
#include "inkey.ch"


REQUEST SDDMY, SQLMIX
ANNOUNCE RDDSYS
MEMVAR aTagList

Function main()
   LOCAL cChoice
   PRIVATE aTagList := {}

   RDDSETDEFAULT("SQLMIX")
   ? "Connecting to
www.freesql.org with user name inforpires, password mypass, database inforpires"
   IF RDDINFO(RDDI_CONNECT, {"MYSQL", "
www.freesql.org", "inforpires","mypass", "inforpires"}) == 0
      ? "Unable connect to the server !!! Check Internet and firewall"
      RETURN 1
   ENDIF
   CLS
   ? "Connected to mysql"
   ACCEPT "Drop existing table ? [Y/N]" TO cChoice
   IF cChoice == "Y"
       RDDINFO(RDDI_EXECUTE, "DROP TABLE customers")
       RDDINFO(RDDI_EXECUTE, "CREATE TABLE IF NOT EXISTS customers (CODE int(5) NOT NULL AUTO_INCREMENT PRIMARY KEY, NAME char(50)  )")
       RDDINFO(RDDI_EXECUTE, "INSERT INTO customers (name) VALUES ('John Doe'),('Moe'),('Bart'),('Homer'),('Lisa'),('Marge'),('Nelson')")
   ENDIF  
   DBUSEAREA( .T.,, "SELECT * FROM customers", "customers" )
   @24,0 SAY "SPACE-Tag/Untag ESC-End   F2-List by code    F3-List by name"
   DBEDIT(0,0,20,79,{"IIF(aScan(aTagList,field->code)!=0,'X',' ')",;  
                     "code",;
                     "name"},"myFunc",,{"Tag","Code","Name"})
   DBCLOSEALL()
   RETURN 0


// ------------------------------------------------
// Dbedit Key Handle routine
FUNCTION MyFunc(status, fld_ptr)
   Local nReturn := 1, nKey, nPos
              
   nKey = LASTKEY()
            
   DO CASE
      CASE status == 4
         IF nKey == K_SPACE
            IF (nPos := aScan(aTagList,field->code)) != 0
               ADEL(aTagList,nPos)
               ASIZE(aTagList,LEN(aTagList)-1)
            ELSE
               aadd(aTagList,field->code)
            ENDIF
            nReturn := 2  
         ELSEIF nKey == K_ESC
            nReturn := 0
         ELSEIF nKey == K_F2     
            doReport(1)
         ELSEIF nKey == K_F3     
            doReport(2)  
         ENDIF  
      ENDCASE
              
   RETURN nReturn


// ---------------------------------------------
// Call ReportMan, setup parameters AND preview
PROCEDURE doReport(nOrderMode)
  local oRep, cFilter
   
  oRep := oReport():new("custlist.rep")
  oRep:setparamvalue("OrderCondition",IF(nOrderMode==1,"code","name"))
  oRep:setparamvalue("FilterCondition","select * from customers where code in ("+implode(",",aTagList)+")" +;
                                       " order by " + IF(nOrderMode==1,"code","name") )
  oRep:preview()
 
RETURN

 // ---------------------------------------------
// Clone of php implode function for numbers
FUNCTION implode(cDelim,aArray)
   LOCAL cStr := "", nLen := LEN(aArray)
  
   AEVAL(aArray,{|x,n| cStr += STR(x) + IF(n != nLen, cDelim, "" )  })
   RETURN cStr


On my next post i will post the needed steps and a link to download the source and other files that can be compiled in linux and windows with only minor changes.

sábado, 11 de setembro de 2010

Reporting in (x)Harbour

In the good old days, a tipical customer report generated with Clipper was something along this lines :

#Include "inkey.ch"
Function Main()
Local nInitialCust, nFinalCust


    nInitialCust := 1
    nFinalCust := 9999
    CLS
    @1,1 SAY "First Customer .:" GET nInitialCust PICTURE "9999"
    @2,1 SAY "Final Customer :" GET nFinalCust PICTURE "9999" VALID nInitialCust <= nFinalCust

    READ
    IF LastKey() != K_ESC
        USE customers NEW
        SET PRINTER ON
        SET DEVICE TO PRINTER
        WHILE !EOF()
            IF field->code >= nInitialCust .and. field->code <= nFinalCust
                ? field->code,field->name
            ENDIF
            dbskip(1)
        END
        EJECT
        SET DEVICE TO SCREEN
        SET PRINTER OFF
        dbCloseArea()
    ENDIF
RETURN NIL
 
The result ? If everything was fine, some text printed on a dot matrix printer. But then things started to change. First, GDI printers only. It was impossible for Clipper to print to that. Then customers start asking to output to pdf files, email, word....
 
These days ? Well, i'm using harbour, and the need for printing to diferent options is still the same. The code base is the same, only the output mechanism as changed.
 
#Include "inkey.ch"


Function Main()
Local nInitialCust, nFinalCust, oReport


    nInitialCust := 1
    nFinalCust := 9999
    CLS
    @1,1 SAY "First Customer .:" GET nInitialCust PICTURE "9999"
    @2,1 SAY "Final Customer :" GET nFinalCust PICTURE "9999" VALID nInitialCust <= nFinalCust
    READ
    IF LastKey() != K_ESC
        oReport := TOleAuto():New("ReportMan.ReportManX")
        oReport:Filename := "customer.rpt"
        oReport:Preview = .T.
        oReport:Title := "Customers List"
        oReport:SetParamValue("nInitial",nInitialCust )
        oReport:SetParamValue("nFinal",nFinalCust )       
        oReport:Execute()
    ENDIF
RETURN NIL


The output ? The following window.

From there, the user as several choices : print, save as pdf, excel, html and others, and attach it to a email message.

How ? Using a very nice report designer called Report Manager. Its free and as many nice features that make it rival with comercial packages like crystal reports.

A quick step by step guide for making it work with our sample code follows up.

First, get it from is web page : Reportman Homepage. Download the full package and install it on your computer.
Next, if like me, you still are using dbfs for small projects, get a oledb driver, like visual foxpro oledb driver or the one from ads. This drivers will allow report manager to connect to your dbfs via dao.

The steps :
  1. Open the Designer XP from your programs and start a new report.
  2. From the report menu, choose the second option to setup data access.
  3. Choose "Microsoft DAO" and click the add button and name the new connection "custConn".
  4. Click the "Search" button and choose "Microsoft oledb for visual foxpro"
  5. Click the "Next" button and choose the path where your dbfs are (make sure you select the option that says "free table directory")
  6. Choose the "report tables" separator add a table to the report, naming it customers.
  7. On the right box, write select * from customers where code between :nInitial and :nFinal
  8. Click the "Parameters" button and add two integer parameters, naming them nInitial and nFinal. Dont forget to associate them with  the table customers. Set the first to default value of one and the other to 9999.
  9. Close the parameters window and click the "show data" just to check that there are no mistakes so far. Click the "Ok" button, to close the data setup part of our report.
  10. Choose the data separator and expand the customers entry. There you should find all the fields from the customers table.
  11. Drag the fields you want to the detail line.
  12. Again from the report menu, add a header/footer group.
  13. Add headers to the fields you choosed (click the "A" button and click&drag the mouse on the header section. Set the label text to the value you want.)
  14. Test your report, choosing the preview option.  
And that is all. Save the report to same location of your dbf and prg, naming it report.rpt
If you save the above sample as test.prg, with harbour you can test it using hbmk2 test.prg -lhbwin
Of course dont forget that you need a customers.dbf with some sample data.

Since the above sample uses OLE, its a windows only solution. Both Harbour and Reportmanager are Linux capable tools. On the next post, i will present a more portable way of interfacing Reportmanager with Harbour.