www.FiveTechSoft.com
https://www.fivetechsupport.com/forums/
https://www.fivetechsupport.com/forums/viewtopic.php?t=30336
Code: Select all | Expand
/*
*
* PivotDBF.PRG
* Author: G.N.Rao, India
* Mar 09-2015 07:11 PM
*
*/
#include "FiveWin.Ch"
#include "ord.ch"
#include "xbrowse.ch"
//----------------------------------------------------------------------------//
REQUEST DBFCDX
//----------------------------------------------------------------------------//
function Main()
local aPivot
CreateSampleDBF()
aPivot := ReadPivotTable( "PVTDATA.DBF", "REGION", "PRODUCT", "SALES" )
BrowsePivot( AClone( aPivot ), "PRODUCT" )
// Invert Pivot
aPivot := ArrTranspose( aPivot )
aPivot[ 1, 1 ] := "PRODUCT"
BrowsePivot( AClone( aPivot ), "REGION" )
return (0)
//----------------------------------------------------------------------------//
init procedure PrgInit
SET DATE ITALIAN
SET CENTURY ON
SET TIME FORMAT TO "HH:MM:SS"
SET EPOCH TO YEAR(DATE())-50
SET DELETED ON
SET EXCLUSIVE OFF
RDDSETDEFAULT( "DBFCDX" )
XbrNumFormat( 'A', .t. )
SetKinetic( .f. )
SetGetColorFocus()
SetBalloon( .t. )
return
//----------------------------------------------------------------------------//
static function BrowsePivot( aPivot, cColFld )
local oDlg, oFont, oBrw, aHead
aHead := aPivot[ 1 ]
ADel( aPivot, 1, .t. )
DEFINE FONT oFont NAME "TAHOMA" SIZE 0,-14
DEFINE DIALOG oDlg SIZE 760,250 PIXEL FONT oFont ;
TITLE "PIVOT TABLE"
@ 10,10 XBROWSE oBrw SIZE -10,-10 PIXEL OF oDlg ;
DATASOURCE aPivot AUTOCOLS HEADERS aHead ;
CELL LINES FOOTERS NOBORDER
WITH OBJECT oBrw
:SetGroupHeader( cColFld, 2, Len( aHead ) )
:SetGroupTotal( cColFld, "TOTAL" )
AEval( :aCols, { |o| o:nFooterType := AGGR_SUM }, 2 )
:nStretchCol := 1
:MakeTotals()
:CreateFromCode()
END
ACTIVATE DIALOG oDlg CENTERED
RELEASE FONT oFont
return nil
//----------------------------------------------------------------------------//
static function ReadPivotTable( cDbf, cRowFld, cColFld, cValFld )
local cPath, oCn, oRs, cSql, cCol
local aCols := {}
local aPivot := {}
local lUseCase
cDbf := TrueName( cDbf )
cPath := cFilePath( cDbf )
cDbf := cFileNoExt( cDbf )
oCn := FW_OpenAdoConnection( cPath, .t. )
lUseCase := ! FW_RDBMSName( oCn ) $ "DBASE,MSACCESS"
// Get Column Names
oRs := FW_OpenRecordSet( oCn, "SELECT DISTINCT " + cColFld + " FROM " + cDbf )
aCols := oRs:GetRows()
oRs:Close()
oRs := nil
aCols := ArrTranspose( aCols )[ 1 ]
// Prepare Pivot Table SQL
cSql := "SELECT " + cRowFld
for each cCol in aCols
if lUseCase
cSql += ", SUM( CASE WHEN " + cColFld + " = '" + cCol + "' THEN " + cValFld + " ELSE 0 END ) AS " + cCol
else
cSql += ", SUM( IIF( " + cColFld + " = '" + cCol + "', " + cValFld + ", 0 ) ) AS " + cCol
endif
next
cSql += " FROM " + cDbf + " GROUP BY " + cRowFld
// ready
oRs := FW_OpenRecordSet( oCn, cSql )
aPivot := oRs:GetRows()
oRs:MoveFirst()
oRs:Close()
oCn:Close()
AIns( aCols, 1, cRowFld, .t. ) // First column header of first column
AIns( aPivot, 1, aCols, .t. ) // Make 1st row the Header Row
return aPivot
//----------------------------------------------------------------------------//
static function CreateSampleDBF()
local aCols := { ;
{ "REGION", 'C', 10, 0 }, ;
{ "PRODUCT", 'C', 10, 0 }, ;
{ "SALES", 'N', 14, 2 } }
local aRegions := { "NORTH", "EAST", "WEST", "SOUTH" }
local aProducts := { "DESKTOP", "LAPTOP", "TABLET", "MOBILE" }
local n, aData := {}
DBCREATE( "PVTDATA.DBF", aCols, "DBFCDX", .t., "DAT" )
for n := 1 to 10000
DAT->( DBAPPEND() )
DAT->( FieldPut( 1, aRegions[ HB_RandomInt( 1, 4 ) ] ) )
DAT->( FieldPut( 2, aProducts[ HB_RandomInt( 1, 4 ) ] ) )
DAT->( FieldPut( 3, HB_Random( 1000, 9999 ) ) )
next n
CLOSE DAT
return nil
//----------------------------------------------------------------------------//
Code: Select all | Expand
local cDbf := "PVTDATA.DBF"
local oCn
oCn := FW_OpenAdoConnection( cFilePath( TrueName( cDbf ) ) )
cDbf := cFileNoExt( cDbf )
XBROWSER FW_AdoPivotArray( oCn, cDbf, "REGION", "PRODUCT", "SALES", "SUM" )