Treeview ve listview hakkında

#Define tvwFirst    0

#Define tvwLast    1

#Define tvwNext    2

#Define tvwPrevious    3

#Define tvwChild    4

 

Public oForm

oForm = Createobject('lvForm')

oForm.Show()

 

Define Class lvForm As Form

  DataSession = 2

  Height = 400

  Width = 800

  Add Object tv As OleControl With ;

    Left =   0,Height = 400,Width = 200, OleClass = 'MSComCtlLib.TreeCtrl'

  Add Object lv As OleControl With ;

    Left = 200,Height = 400,Width = 600, OleClass = 'MSComCtlLib.ListviewCtrl.2'

 

  Procedure Init

    Thisform.tvSetUp()

    Thisform.lvSetUp('All')

  Endproc

 

  Procedure tv.NodeClick

    Lparameters Node

    Node.ensurevisible

    Thisform.lvSetUp(Node.Key)

  Endproc

 

 

  Procedure lv.ItemClick

    Lparameters Item

    Thisform.lvSetUp(Item.Key)

  Endproc

 

  Procedure lvSetUp(tcKey)

    If !(m.tcKey  == 'All' Or ;

        m.tcKey  = 'Cntry_' Or ;

        m.tcKey = 'Cust_' Or ;

        m.tcKey = 'Ord_')

      Return

    Endif

 

    With Thisform.tv

      .SelectedItem = .Nodes( m.tcKey )

      .SelectedItem.ensurevisible

    Endwith

 

    With Thisform.lv

      .ListItems.Clear()

      .ColumnHeaders.Clear()

      Do Case

        Case m.tcKey = 'All'

          .View = 2

          .ColumnHeaders.Add( , , "Country", 200)

          .ColumnHeaders.Add( , , "Customer Count", 50)

          Select country, Cnt(*) As CustomerCount ;

            From (_samples+"data\customer") ;

            Where !Empty(country) ;

            GROUP By country ;

            Into Cursor grpByCountry

          Scan

            itmX = .ListItems.Add(Recno(), 'Cntry_'+Trim(grpByCountry.country))

            itmX.Text = Trim(grpByCountry.country)

            itmX.SubItems(1) = Transform(grpByCountry.CustomerCount)

          Endscan

          Use In 'grpByCountry'

 

        Case m.tcKey = 'Cntry_'

          .View = 3

          .ColumnHeaders.Add( , , "Title",   150)

          .ColumnHeaders.Add( , , "Contact", 150)

          .ColumnHeaders.Add( , , "Company", 200)

          .ColumnHeaders.Add( , , "Country", 100)

 

          Select * From (_samples+"data\customer") ;

            Where country == Substr(m.tcKey,Len('Cntry_')+1) ;

            Into Cursor crsDetail

          Scan

            itmX = .ListItems.Add(,'Cust_'+crsDetail.cust_id)

            itmX.Text = crsDetail.Title

            itmX.SubItems(1) =crsDetail.contact

            itmX.SubItems(2) =crsDetail.Company

            itmX.SubItems(3) =crsDetail.country

          Endscan

          Use In 'crsDetail'

 

        Case m.tcKey = 'Cust_'

          .View = 3

          .ColumnHeaders.Add( , , "Order No",    50)

          .ColumnHeaders.Add( , , "Salesman",    200)

          .ColumnHeaders.Add( , , "Date Ordered",200)

          .ColumnHeaders.Add( , , "Ship Date",   200)

 

          Select * ;

            From (_samples+"data\orders") ord ;

            INNER Join (_samples+"data\employee") emp ;

            ON ord.emp_id == emp.emp_id ;

            Where cust_id == Substr(m.tcKey,Len('Cust_')+1) ;

            Into Cursor crsDetail

          Scan

            itmX = .ListItems.Add(,'Ord_'+Padl(Int(Val(crsDetail.order_id)),6,'0') )

            itmX.Text = Padl(Int(Val(crsDetail.order_id)),6,'0')

            itmX.SubItems(1) = Trim(crsDetail.First_Name - (' '+crsDetail.Last_Name))

            itmX.SubItems(2) = Transform(crsDetail.Order_date,'@YL')

            itmX.SubItems(3) = Transform(crsDetail.Shipped_on,'@YL')

          Endscan

          Use In 'crsDetail'

        Case m.tcKey = 'Ord_'

          .View = 3

          .ColumnHeaders.Add( , , "Line No",      50)

          .ColumnHeaders.Add( , , "Product Name", 250)

          .ColumnHeaders.Add( , , "Quantity",     50, 1)

          .ColumnHeaders.Add( , , "Price",        120,1)

          .ColumnHeaders.Add( , , "Ext.Price",    120,1)

 

          Select oi.*, prd.Prod_Name ;

            From (_samples+"data\orditems") oi ;

            INNER Join (_samples+"data\products") prd ;

            ON oi.product_id == prd.product_id ;

            Where Val(order_id) = Val(Substr(m.tcKey,Len('Ord_')+1)) ;

            ORDER By line_no ;

            Into Cursor crsDetail

          Scan

            itmX = .ListItems.Add(,;

              'Oi_'+Padl(Int(Val(crsDetail.order_id)),6,'0')+;

              Padl(crsDetail.line_no,6,'0') )

            itmX.Text = Transform(crsDetail.line_no)

            itmX.SubItems(1) = crsDetail.Prod_Name

            itmX.SubItems(2) = Transform(crsDetail.Quantity)

            itmX.SubItems(3) = Transform(crsDetail.Unit_Price,'@($')

            itmX.SubItems(4) = Transform(crsDetail.Unit_Price*crsDetail.Quantity,'@($')

          Endscan

          Use In 'crsDetail'

      Endcase

    Endwith

  Endproc

 

  Procedure tvSetUp(tcKey)

    Local oNode

    Local Array oNodes[1]

    With Thisform.tv

      Do Case

        Case Empty(m.tcKey)

          .linestyle = 1

          .Font.Name = 'Times New Roman'

          .Font.Size = 10

          .indentation = 5

          .LabelEdit = 1

          .HideSelection = .F.

 

          oNode = .Nodes.Add(,tvwFirst,'All','Ihracat Yaptigimiz Ulkeler:=)')

          Thisform.tvSetUp(oNode.Key)

        Case m.tcKey = 'All'

          Select country, Cnt(*) As CustomerCount ;

            From (_samples+"data\customer") ;

            Where !Empty(country) ;

            GROUP By country ;

            Into Cursor grpByCountry

          Dimension oNodes[_Tally]

          oNodes = .Null.

          Scan

            oNodes[RECNO()] = .Nodes.Add(m.tcKey,tvwChild,;

              'Cntry_'+Trim(grpByCountry.country),;

              Trim(grpByCountry.country))

          Endscan

          Use In 'grpByCountry'

          For Each oNode In oNodes

            Thisform.tvSetUp( oNode.Key )

          Endfor

        Case m.tcKey = 'Cntry_'

          Select * From (_samples+"data\customer") ;

            Where country == Substr(m.tcKey,Len('Cntry_')+1) ;

            Into Cursor crsDetail

          Dimension oNodes[_Tally]

          oNodes = .Null.

          Scan

            oNodes[RECNO()] = .Nodes.Add(m.tcKey,tvwChild, ;

              'Cust_'+crsDetail.cust_id, crsDetail.Company)

          Endscan

          Use In 'crsDetail'

          For Each oNode In oNodes

            Thisform.tvSetUp( oNode.Key )

          Endfor

        Case m.tcKey = 'Cust_'

          Select * ;

            From (_samples+"data\orders") ord ;

            INNER Join (_samples+"data\employee") emp ;

            ON ord.emp_id == emp.emp_id ;

            Where cust_id == Substr(m.tcKey,Len('Cust_')+1) ;

            Into Cursor crsDetail

          Scan

            .Nodes.Add(m.tcKey,tvwChild,;

              'Ord_'+Padl(Int(Val(crsDetail.order_id)),6,'0'),;

              'Order#:'+Padl(Int(Val(crsDetail.order_id)),6,'0'))

          Endscan

          Use In 'crsDetail'

      Endcase

    Endwith

  Endproc

Enddefine

Local strCn,strShp, testDataLoc

 

TESTDATALOC = _samples+'data\testdata.DBC'

strCn =    'Provider=MSDataShape;Persist Security Info=False;'+;

  'Data Source='+m.TESTDATALOC+';Data Provider=VFPOLEDB'

 

strShp1 = 'Shape Table customer'

 

strShp2 = [SHAPE TABLE customer ]+;

  [  APPEND ( (SHAPE TABLE orders   ]+;

  [    APPEND (TABLE orditems RELATE order_id TO order_id)) ]+;

  [  RELATE cust_id TO cust_id ) ]

 

strShp3 = [SHAPE { select Company, cust_id from customer } ]+;

  [APPEND (( SHAPE { select distinct First_name, Last_name, a.emp_id + cust_id as "Emp_sel", cust_id  from employee a inner join orders b on a.emp_id = b.emp_id }  ]+;

  [APPEND (( SHAPE { select order_date, order_net, shipped_on, emp_id + cust_id as "Emp_sel",order_id from orders }  ]+;

  [APPEND ( { select order_id, line_no, prod_name from orditems inner join products on products.product_id = orditems.product_id } AS rsOrditems  ]+;

  [RELATE order_id TO order_id )) AS rsEmployee ]+;

  [RELATE emp_sel TO emp_sel )) AS rsOrders  ]+;

  [RELATE cust_id TO cust_id ) ]

 

strShp4 = [ SHAPE  {SELECT cust_id, company FROM customer} ]+;

  [APPEND ({SELECT cust_id, order_id, order_date, order_net ]+;

  [         FROM orders ]+;

  [         WHERE order_date < {1/1/1996} AND cust_id = ?} ]+;

  [         RELATE cust_id TO PARAMETER 0) AS rsOldOrders, ]+;

  [       ({SELECT cust_id, order_id, order_date, order_net ]+;

  [         FROM orders ]+;

  [         WHERE order_date >= {1/1/1996}} ]+;

  [         RELATE cust_id TO cust_id) AS rsRecentOrders ]

 

lcSel1 = [ select customer.cust_id, ]+;

  [   customer.Company,]+;

  [   orders.order_id,]+;

  [   orders.Order_date ]+;

  [ from customer ]+;

  [  inner join orders on customer.cust_id = orders.cust_id ]

 

lcSel2 = [ select od.order_id, od.line_no, ]+;

  [   products.prod_name, ]+;

  [   products.no_in_unit as 'Packaging', ]+;

  [   od.unit_price, ]+;

  [   od.Quantity, ]+;

  [   od.unit_price * od.quantity as ExtendedPrice ]+;

  [ from orditems as od ]+;

  [  inner join products on od.product_id = products.product_id ]

 

strShp5 = [  SHAPE ]+;

  [(SHAPE {]+lcSel1+[ } as rs1 ]+;

  [    APPEND  ({]+lcSel2+[ } AS rsDetails RELATE order_id TO order_id),  ]+;

  [ SUM(rsDetails.ExtendedPrice) AS OrderTotal, ANY(rsDetails.order_id)) AS rsOrders ]+;

  [COMPUTE  rsOrders, ]+;

  [SUM(rsOrders.OrderTotal) AS CustTotal, ]+;

  [ANY(rsOrders.Company) AS Cmpny    ]+;

  [   BY cust_id ]

 

TestADO(strCn,strShp1)

TestADO(strCn,strShp2)

TestADO(strCn,strShp3)

TestADO(strCn,strShp4)

TestADO(strCn,strShp5)

 

Function TestADO(tcConn, tcSQL)

  Local oRecordset As "adodb.recordset",oConnection As "adodb.connection"

  oRecordset = Createobject("adodb.recordset")

  oConnection = Createobject("adodb.connection")

 

  With oConnection

    .ConnectionString = m.tcConn

    .Open

  Endwith

 

  With oRecordset

    .ActiveConnection = oConnection

    .Source = m.tcSQL

    .Open

  Endwith

  ShowMe(oRecordset)

Endfunc

 

Function ShowMe

  Lparameters toRecordset

  Public oForm

  oForm = Createobject('myForm', toRecordset)

  oForm.Show(1)

Endfunc

 

Define Class myform As Form

  Height = 450

  Width = 750

 

  Add Object hflex As OleControl With ;

    Top = 10, Left = 10, Height = 430, Width = 730, Name = "Hflex", ;

    OleClass = 'MSHierarchicalFlexGridLib.MSHFlexGrid'

 

  Procedure Init

    Lparameters toRecordset

    With This.hflex

      .FixedCols = 0

      .Datasource = toRecordset

      .AllowUSerResizing = 3

      .CollapseAll

    Endwith

  Endproc

Enddefine