XBROWSE WITH oTREE OF 2 TABLES

[joseluisysturiz]

Greetings, I know that there is a lot of this, but I am really in relation to the most lost topic that Adan mothers day ...

I have 2 tables in mysql, one I have a product so I call it COMPOSITE and in another table the products that make it up ... Example ...
- PASTEL (composed by) - TABLE 1
* flour
* eggs <- TABLE 2
* milk
* etc.

I need to show in a xBrowse the product PASTEL and with a TREE the products that compose it with their respective quantities and cost of each component and that of the total cost of the PASTEL showing or not the products that compose it, I hope to have explained, I need the tip of the thread to start with this, greetings ... thanks ...

what I need is to show the information in an xBrowse with oTree, example:

code! description!
---------------------------------
001! CAKE ! <--- COMPOSITE PRODUCT
---------------------------------
! -> sugar <--- component 1
---------------------------------
! -> flour <--- component 2
---------------------------------
! -> milk <--- component 3
---------------------------------
002! xPRODUCT!

GOOGLE TRANSLATOR...

TEMA ESPAÑOL:
viewtopic.php?f=6&t=35738

[nageswaragunupudi]

I think you are using FWH Mariadb library.
I give you a sample using states and customer tables. You can adapt the logic to your recipes work.

Code: Select all  Expand view  RUN

#include "fivewin.ch"

function Main()

   local oCn, oRs, oCust, oStates, oTree

   oCn      := FW_DemoDB()

   oCust    := oCn:RowSet( "SELECT state,city FROM customer ORDER BY city" )
   oStates  := oCn:RowSet( "SELECT code,name FROM states ORDER BY name" )
   oStates:AddChild( oCust, "state = states.code" )

   oTree    := MakeTree( oStates )

   XBROWSER oTree SETUP oBrw:aCols[ 1 ]:AddBitmap( { FWDArrow(), FWRArrow() } )

   oCn:Close()

return nil

function MakeTree( oStates )

   local oTree
   local code, i, j

   oStates:GoTop()

   TREE oTree
   for i := 1 to oStates:KeyCount()
      TREEITEM oStates:name
      oStates:SyncChild()
      if oStates:oChild:KeyCount() > 0
         TREE
         for j := 1 to oStates:oChild:KeyCount()
            TREEITEM oStates:oChild:City
            oStates:oChild:Skip( 1 )
         next
         ENDTREE
      endif
      oStates:Skip( 1 )
   next
   ENDTREE

return oTree
 

[nageswaragunupudi]

Another alternative

Code: Select all  Expand view  RUN

#include "fivewin.ch"

function Main()

   local oCn, oRs, oTree

   oCn      := FW_DemoDB()

   oRs   := oCn:RowSet( "SELECT c.state, s.name, GROUP_CONCAT( c.city ) AS cities FROM customer c LEFT JOIN states s on c.state = s.code GROUP BY c.state ORDER BY name" )
   oRs:GoTop()
   oTree := MakeTree( oRs )
   XBROWSER oTree TITLE "States and Cities" SETUP oBrw:aCols[ 1 ]:AddBitmap( { FWRArrow(), FWDArrow() } )

   oCn:Close()

return nil

function MakeTree( oRs )

   local oTree

   oRs:GoTop()

   TREE oTree
   do while !oRs:Eof()
      if !Empty( oRs:name ) .and. !Empty( oRs:Cities )
         TREEITEM oRs:name
         TREE
            AEval( HB_ATokens( oRs:Cities, "," ), <|c|
                              TREEITEM c
                              return nil
                              > )

         ENDTREE
      endif
      oRs:Skip( 1 )
   enddo
   ENDTREE

return oTree
 

[joseluisysturiz]

Greetings Mr. RAO, I am using mysql with tdolphin, little by little I will migrate to FWH Mariadb library, but for the moment I need to solve as I have now, I will guide me with the example I put and any questions, I will write again, thank you .. .

[nageswaragunupudi]

My second sample works with Dolphin, tmysql and ado.
But the limitation is that it can only display names. We can not have additional columns to show quantity, etc.
This sample has no such limitations. Works with Dolphin, tmysql and ado

Code: Select all  Expand view  RUN

#include "fivewin.ch"

function Main()

   local oCn, oRs, oTree

   oCn      := FW_DemoDB()

   oRs   := oCn:Query( "SELECT c.state, s.name, c.city, c.age, c.salary FROM customer c LEFT JOIN states s on c.state = s.code ORDER BY name" )
   oTree := MakeTree( oRs )
   XBROWSER oTree TITLE "States/Cities" ;
      SETUP ( oBrw:aCols[ 1 ]:AddBitmap( { FWRArrow(), FWDArrow() } ), ;
              oBrw:cEditPictures := { nil, "99", "999,999.99" }, ;
              oBrw:lDisplayZeros := .f., ;
              oBrw:cHeaders := { "Item", "Age", "Salary" } )

   oCn:Close()

return nil

function MakeTree( oRs )

   local oTree
   local cname

   oRs:GoTop()

   TREE oTree
   do while !oRs:Eof()
      if Empty( oRs:name ) .or. Empty( oRs:city )
         oRs:Skip( 1 )
      else
         TREEITEM oRs:name CARGO { 0, 0.00 }
         cname    := oRs:name
         TREE
         do while oRs:name == cname .and. !oRs:Eof()
            TREEITEM oRs:city CARGO { oRs:age, oRs:salary }
            oRs:Skip( 1 )
         enddo
         ENDTREE
      endif
   enddo
   ENDTREE

return oTree
 

.

[fraxzi]

Hi Rao,

Relative to your sample, can I request for another sample using MariaDB Parent-Child record set showing 4 levels of sub-tree

Please..