FiveTech Software tech support forums

/*
* Inserting Subtotals
* Automatically recalculated
* When individual items are edited
*
*/

#include "fivewin.ch"

//----------------------------------------------------------------------------//

function Main()

   local aData, n
   local aHead

   FWNumFormat( 'A', .t. )

   /*
      This works only with Arrays. We are using test data aData
      Instead it can be any Array of real Data. Conditions are that
      a) First column should be serial number
      b) second column should be some description
      Other columns can be of any datatype
   */

   aData    := {}
   for n := 1 to 10
      AAdd( aData, { n, "Item-" + StrZero( n, 2 ), ;
         HB_RandomInt( 1, 999 ), HB_RandomInt( 1, 9999 ), ;
         Space( 5 ), ;
         HB_RandomInt( 1, 9999 ) } )
   next

   /*
      Call function BrowseWithSubTotals( aData, [aHead], [aSumCols] )
      Optionally we can provide an array of column headers in the 2nd param
      Optionally we can specify which columns are to be sub-totalled and totalled.
      If ommitted all numeric columns are totalled.
   */

   BrowseWithSubTotals( aData, aHead, { 3, 4, 6 } )

return nil

//----------------------------------------------------------------------------//

function BrowseWithSubTotals( aData, aHead, aSumCols )

   local oDlg, oBrw, oFont
   local nCol

   if Empty( aSumCols )
      aSumCols := {}
      AEval( aData[ 1 ], { |u,i| If( ValType( u ) == 'N', AAdd( aSumCols, i ), nil ) }, 3 )
   endif

   DEFAULT aHead := { "SlNo", "Item" }

   DEFINE FONT oFont NAME "TAHOMA" SIZE 0,-14
   DEFINE DIALOG oDlg SIZE 700,500 PIXEL FONT oFont

   @ 30,10 XBROWSE oBrw SIZE -10,-10 PIXEL OF oDlg ;
      DATASOURCE aData ;
      AUTOCOLS HEADERS aHead ;
      COLSIZES 40 ;
      CELL LINES FOOTERS NOBORDER FASTEDIT

   for Each nCol in aSumCols
      if ValType( aData[ 1, nCol ] ) == 'N'
         WITH OBJECT oBrw:aCols[ nCol ]
            :bEditValue    := { |x,o| If( x == nil .or. ValType( oBrw:aRow[ o:nArrayCol ] ) == 'B', ;
                                     XEval( oBrw:aRow[ o:nArrayCol ] ), ;
                                     oBrw:aRow[ o:nArrayCol ] := x ) }

            :nEditType     := EDIT_GET
            :cEditPicture  := NumPict( 10, 2 )
            :bEditWhen     := { || ! IsTotalRow(  oBrw:aRow )  }
            :bOnChange     := { || oBrw:Refresh() }
            :nFooterType   := AGGR_SUM
            :bSumCondition := { |n,o| ! IsTotalRow( o:oBrw:aRow ) }
         END
      endif
   next

   WITH OBJECT oBrw
      :nStretchCol   := STRETCHCOL_WIDEST
      :bClrStd       := { || { CLR_BLACK, If( IsTotalRow( oBrw:aRow ), CLR_YELLOW, CLR_WHITE ) } }
      :bChange       := { || oDlg:AEvalWhen() }
      :MakeTotals()
      //
      :CreateFromCode()
   END

   @ 10, 10 BUTTON "Insert SubTotal" SIZE 70,14 PIXEL OF oDlg ;
      ACTION InsertSubTotal( oBrw ) ;
      WHEN ! IsTotalRow( oBrw:aRow )
   @ 10, 90 BUTTON "Remove SubTotal" SIZE 70,14 PIXEL OF oDlg ;
      ACTION oBrw:Delete() ;
      WHEN IsTotalRow( oBrw:aRow )

   ACTIVATE DIALOG oDlg CENTERED
   RELEASE FONT oFont

return nil

//----------------------------------------------------------------------------//

static function InsertSubTotal( oBrw )

   local n, oCol, nRowFrom := 0, nRowLast

   if .not. IsTotalRow( oBrw:aRow )
      nRowLast    := oBrw:nArrayAt
      for n := nRowLast  to 1 step -1
         if IsTotalRow( oBrw:aArrayData[ n ] )
            EXIT
         else
            nRowFrom    := n
         endif
      next
      if nRowFrom > 0
         if nRowLast == Len( oBrw:aArrayData )
            AAdd( oBrw:aArrayData, Array( Len( oBrw:aRow ) ) )
         else
            AIns( oBrw:aArrayData, nRowLast + 1, Array( Len( oBrw:aRow ) ), .t. )
         endif
         oBrw:aArrayData[ nRowLast + 1, 2 ]   := "Sum : " + ;
               cValToChar( oBrw:aArrayData[ nRowFrom, 1 ] ) + "-" + ;
               cValToChar( oBrw:aArrayData[ nRowLast, 1 ] )

         for each oCol in oBrw:aCols
            if oCol:nFooterType == AGGR_SUM .and. ! Empty( oCol:nArrayCol )
               oBrw:aArrayData[ nRowLast + 1, oCol:nArrayCol ] := ;
                  MakeSumBlock( oBrw:aArrayData, oCol:nArrayCol, nRowFrom, nRowLast )
            endif
         next
         oBrw:nArrayAt  := nRowLast + 1
         oBrw:nRowSel   := oBrw:nArrayAt
         oBrw:oWnd:AEvalWhen()
         oBrw:Refresh()
         oBrw:SetFocus()
      endif
   endif

return nil

//----------------------------------------------------------------------------//

static function MakeSumBlock( aData, nCol, nRowFrom, nRowLast )

   local nRows := nRowLast - nRowFrom + 1
   local aRows := {}
   local n

   for n := nRowFrom to nRowLast
      AAdd( aRows, aData[ n ] )
   next

return { || FW_ArrSum( aRows, nCol ) }

//----------------------------------------------------------------------------//

static function IsTotalRow( aRow )
return Empty( aRow[ 1 ] )

//----------------------------------------------------------------------------//