/*****************************************************************************/
/*                                                                           
*/
/* PROGRAM NAME : dr-excel.p                                                 
*/
/* DESCRIPTION  : RE - Usable Procedure which Produces Excel Spreadsheat     
*/
/* PARAMS       : INPUT TABLE - TEMP, DATABASE, DYNAMIC                      
*/
/*                INPUT CHAR  - LIST OF DATABASE FIELDS                      
*/
/*                INPUT INT   - NUMBER OF FIELDS                             
*/
/*                INPUT CHAR  - Name of table                                
*/
/*                INPUT CHAR  - FIELD LABELS                                 
*/
/*                INPUT CHAR  - TITLE                                        
*/
/* AUTHOR       : Simon Critchley                                            
*/
/* DATE         : 22/08/03                                                   
*/
/*****************************************************************************/
/*                                                                           
*/
/*****************************************************************************/


/************** VARIABLE DEFINES 
*********************************************/

DEFINE VARIABLE t-temp-table            AS HANDLE                      
NO-UNDO.
DEFINE VARIABLE b-dyn-buff              AS HANDLE                      
NO-UNDO.
DEFINE VARIABLE lv-numfield             AS INTEGER INITIAL 1           
NO-UNDO.
DEFINE VARIABLE t-temp          AS HANDLE  FORMAT ">>>>>>>>>>>>>>>>>9" 
NO-UNDO.
DEFINE VARIABLE lv-rowid                AS ROWID                       
NO-UNDO.
DEFINE VARIABLE lv-rowid-sel            AS ROWID                       
NO-UNDO.
DEFINE VARIABLE lv-log                  AS INTEGER                     
NO-UNDO.
DEFINE VARIABLE lv-alphabet             AS CHARACTER
       INITIAL "abcDEFghijklmnopqrstwyxz" NO-UNDO.
DEFINE VARIABLE lv-count                AS INTEGER INITIAL 1           
NO-UNDO.
DEFINE VARIABLE lv-cntstr               AS INTEGER                     
NO-UNDO.
DEFINE VARIABLE lv-char                 AS CHAR                        
NO-UNDO.
DEFINE VARIABLE lv-charrange            AS CHAR                        
NO-UNDO.
DEFINE VARIABLE lv-bnum                 AS INTEGER                     
NO-UNDO.
DEFINE VARIABLE iColumn                 AS INTEGER INITIAL 1           
NO-UNDO.
DEFINE VARIABLE cColumn                 AS CHARACTER                   
NO-UNDO.
DEFINE VARIABLE cRange                  AS CHARACTER                   
NO-UNDO.
DEFINE VARIABLE lv-color                AS INTEGER INITIAL 1           
NO-UNDO.


/**************** STATIC TEMP-TABLES    
**************************************/

DEFINE TEMP-TABLE t-fields
    FIELD fname AS CHAR FORMAT "x(30)" LABEL "Select Fields "
    FIELD flabel AS CHAR FORMAT "x(30)" LABEL "label"
    FIELD fint AS INT
   .
DEFINE TEMP-TABLE t-bnum
    FIELD bnum AS INT.
DEFINE TEMP-TABLE t-fields-sel
    FIELD fname AS CHAR
    FIELD flabel AS CHAR FORMAT "x(30)" LABEL "label"
    .

/**************** HANDLES AND PARAMS     
**************************************/

DEFINE VARIABLE i                          AS INTEGER                   
NO-UNDO.
DEFINE VARIABLE q                          AS HANDLE                    
NO-UNDO.
DEFINE VARIABLE h-flid                     AS HANDLE                    
NO-UNDO.
DEFINE VARIABLE hl-flid                    AS HANDLE                    
NO-UNDO.
DEFINE VARIABLE chExcelApplication         AS COM-HANDLE                
NO-UNDO.
DEFINE VARIABLE chWorkbook                 AS COM-HANDLE                
NO-UNDO.
DEFINE VARIABLE chWorksheet                AS COM-HANDLE                
NO-UNDO.
DEFINE VARIABLE chChart                    AS COM-HANDLE                
NO-UNDO.
DEFINE VARIABLE chWorksheetRange           AS COM-HANDLE                
NO-UNDO.



DEFINE INPUT PARAMETER TABLE-HANDLE FOR t-temp.
DEFINE INPUT PARAMETER ip-list           AS CHARACTER                   
NO-UNDO.
DEFINE INPUT PARAMETER ip-bnum           AS INT                         
NO-UNDO.
DEFINE INPUT PARAMETER ip-tableNAME      AS CHAR                        
NO-UNDO.
DEFINE INPUT PARAMETER ip-fieldlab       AS CHAR                        
NO-UNDO.
DEFINE INPUT PARAMETER ip-title          AS CHAR                        
NO-UNDO.

/* this param is bobaddition */

/****************** CREATE DYNAMIC BUFFER FOR TABLE-HANDLE 
**********************/

b-dyn-buff = t-temp:DEFAULT-BUFFER-HANDLE.
CREATE QUERY q.
q:SET-BUFFERS(b-dyn-buff).
q:QUERY-PREPARE("FOR EACH " + ip-TABLEname).
q:QUERY-OPEN.

/***************** READ IN FIELDS FROM PARAM LIST TO TEMP-TABLE 
*****************/

REPEAT:
lv-bnum = lv-bnum + 1.
    CREATE t-fields-sel.
    ASSIGN t-fields-sel.fname = ENTRY(lv-bnum, ip-list)
           t-fields-sel.flabel = ENTRY(lv-bnum,ip-fieldlab).
  IF lv-bnum = ip-bnum THEN
   LEAVE.
END.

lv-bnum = 0.

/**************** LOAD ALL FIELDS FROM PARAM TABLE INTO TEMP-TABLE 4 REF 
********/


REPEAT i = 1 TO b-dyn-buff:NUM-FIELDS TRANSACTION:
    CREATE t-fields.
    ASSIGN h-flid          = b-dyn-buff:BUFFER-FIELD(lv-numfield)
           t-fields.fname  = h-flid:NAME
           t-fields.fint   = i
           lv-numfield     = lv-numfield + 1
          .

END.

RUN p-cr-excel. /***** PRODUCES SPREADSHEAT  ****/


/*************** INTERNAL PROCEDURE : PRODUCES SPREADSHEAT  
*********************/

PROCEDURE p-cr-excel.


  CREATE "Excel.Application" chExcelApplication.

          chWorkbook = chExcelApplication:Workbooks:Add().

          chWorkSheet = chExcelApplication:Sheets:Item(1).


   ASSIGN /* chworksheet:Range("A1"):VALUE = " " + STRING(TODAY) + "  "
          chworksheet:Range("A1"):FONT:bold = TRUE  */
          chworksheet:Range("A3"):VALUE = ip-title
          chworksheet:Range("A3"):FONT:bold = TRUE.



    chWorkSheet:PageSetup:ORIENTATION = 2.
    chWorkSheet:PageSetup:Zoom = 80.
    chWorkSheet:PageSetup:LeftHeader =
           "        "
        + ""
        .
    chWorkSheet:PageSetup:CenterHeader  = "&D" + "  "  + "&T".
    chWorkSheet:PageSetup:RightHeader    = "&P" + " of  "  +  "&N".
    chWorkSheet:PageSetup:PrintTitleRows = "$5:$6".

FOR EACH t-fields-sel NO-LOCK:
FIND FIRST t-fields WHERE t-fields.fname = t-fields-sel.fname NO-LOCK 
NO-ERROR.
IF AVAILABLE t-fields THEN
DO:
    ASSIGN lv-char = SUBSTRING(lv-alphabet,lv-count,1)
           chWorkSheet:Columns(STRING(lv-char)):ColumnWidth = 
LENGTH(t-fields-sel.flabel) + 10
           lv-charrange = lv-char + "4"
           chWorkSheet:Range(lv-charrange):Value = t-fields-sel.flabel
          /* chWorkSheet:Range(lv-charrange):FONT:Bold = TRUE
           chWorkSheet:Range(lv-charrange):borders:colorindex = 46  */
           lv-count = lv-count + 1
           lv-char = "".
    END.
END.
/* chWorkSheet:Range("A4:l4"):AutoFormat(6,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE). 
*/

FOR EACH t-fields-sel NO-LOCK:
    FIND FIRST t-fields WHERE t-fields.fname = t-fields-sel.fname NO-LOCK 
NO-ERROR.
    IF AVAILABLE t-fields THEN
    DO:
        CREATE t-bnum.
        ASSIGN t-bnum.bnum = t-fields.fint
               t-fields-sel.flabel = t-fields.flabel.
    END.
END.


ASSIGN lv-count = 5
       lv-cntstr = 1.

REPEAT:
q:GET-NEXT.
IF q:QUERY-OFF-END THEN LEAVE.
FOR EACH t-bnum NO-LOCK:
ASSIGN h-flid         = b-dyn-buff:BUFFER-FIELD(t-bnum.bnum)
        lv-char        = SUBSTRING(lv-alphabet,lv-cntstr,1)
        lv-charrange   = lv-char + STRING(lv-count)
        chWorkSheet:Range(lv-charrange):Value = h-flid:BUFFER-VALUE
        lv-cntstr      = lv-cntstr + 1
        chWorkSheet:Range(lv-charrange):interior:colorindex = 15.
END.
      ASSIGN lv-cntstr = 1
             lv-count  = lv-count + 1.
END.
/*chWorkSheet:Range("A4:" + 
lv-charrange):AutoFormat(3,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE). */
   chExcelApplication:Visible = TRUE.
   RELEASE OBJECT chExcelApplication NO-ERROR.
   RELEASE OBJECT chWorkbook NO-ERROR.
   RELEASE OBJECT chWorksheet NO-ERROR.
   RELEASE OBJECT chChart NO-ERROR.
   RELEASE OBJECT chWorksheetRange NO-ERROR.
END.




