FWHMARIADB Samples

[nageswaragunupudi]

For example, when I try to add a new item, if I give an existing code by mistake
the program returns a duplicate entry error and fills all of the entries on the dialog
with the fields of current record and hence I am loosing everything that I typed.

Please download the exe again and try

[betoncu]

Rao, this is exactly what I want.
I am using my own dialogs, instead of the default dialogs to append and modify the records.
How we will implement this or do we have to wait the next build for this?

Thanks in advance

[Marc Venken]

I ported my starting membership program from local to online with this sample.
The Xbrowse is working nice...

But Scoping is not clear. I use this in normal program.

This draws a button for every team our club has, and when clicking on 1 team, the scope gives only the team members
The caption is the name of the team and also the search item for the scope.

Code: Select all

   for i = 1 to len(aPloegen)
      @ nTop+nMove,nLeft BTNBMP aBtn[i] OF oDlg SIZE 80, 25 NOBORDER PROMPT aPloegen[i] 2007 ACTION SET_SCOPE_ploegen(oBrw,::cCaption) font oBold CENTER
   next

STATIC FUNCTION SET_SCOPE_ploegen(oBrw,cPloeg)
LOCAL cNName := cPloeg
DBSELECTAREA( "leden" )
leden->(dbsetorder("ploeg2017"))

("leden")->(ORDSCOPE(0, cNName ) )
("leden")->(ORDSCOPE(1, cNName ) )

leden->(dbgotop())
oBrw[oFld:nOption]:refresh()
RETURN NIL
 

[nageswaragunupudi]

Rao, this is exactly what I want.
I am using my own dialogs, instead of the default dialogs to append and modify the records.
How we will implement this or do we have to wait the next build for this?

Thanks in advance

Are you using FWH 17.04 ?
If you are using FWH 17.04, please contact me at nageswaragunupudi at gmail dot com and let me know the compiler you are using i.e, harbour /xharbor / bcc7

[nageswaragunupudi]

Mr Marc

Please use oRs:SetFilter() method

Example:

ACTION (oRs:SetFilter( "fldname = ?", { ::cCpation } ), oBrw:GoTop(), oBrw:Refresh() )

[nageswaragunupudi]

Mr Betoncu

With the revised libs I sent you,
When you make your own dialog:

oRs:bEdit := { |oRec| MyDialog( oRec ) }

function MyDialog( oRec )

local lNew := ( oRec:RecNo == 0 )

<< SAYS AND GETS >>

@ r,c, BUTTON "Save" SIZE w,h PIXEL OF oDlg ;
WHEN oRec:Modified() ;
ACTION If( oRec:Save(), oDlg:End(), nil )

[Marc Venken]

Best technique for update data.

With online databases, I believe that we better change the data in Xbrowse, rather than update the database directly ?

I use Xbrowse and sometimes I change data outside of Xbrowse like this :
For this case I create a button for eacht team, and the trainers select from Xbrowse (multiple records) and the click te button.

Code: Select all

   for i = 1 to 18
      nMove+=30
      cPloeg = alltrim(aPloegen[i])
      @ nTop,nLeft+nMove BTNBMP aBtn2[i] OF oFld:aDialogs[ 2 ] SIZE 28, 15 NOBORDER PROMPT aPloegen[i] 2007 ACTION (setploeg(oBrw[2],::cCaption),oBrw[oFld:nOption]:GoTop(),oBrw[oFld:nOption]:refresh(),oBrw[oFld:nOption]:setfocus()) font oBold CENTER
      aBtn2[i]:bClrGrad := { | lMouseOver | If( ! lMouseOver,;
      { { 0.1, 16764573, 16764573 }, ;
      { 0.1, 16764573, 16764573 } }, ;
      { { 0.1, 11524015, 11524015 }, ;
      { 0.1, 11524015, 11524015 } } ) }
   next

 

Code: Select all

function setploeg(oBrw,cPloeg)
    if msgYesNo("Change selected members")
        FOR I = 1 TO LEN(oBRW:aSELECTED) // obrw:aSELECTED is an array containing recnos marked
          leden->(dbgoto(oBRW:aSELECTED[I]))
          leden->ploeg2017 = cPloeg
        next
    endif
return NIL

 

How to make the change ?

[betoncu]

Dear Rao,
Thanks for your kind help. It works.

[Marc Venken]

Best technique for update data.

With online databases, I believe that we better change the data in Xbrowse, rather than update the database directly ?

I use Xbrowse and sometimes I change data outside of Xbrowse like this :
For this case I create a button for eacht team, and the trainers select from Xbrowse (multiple records) and the click te button.

Code: Select all

   for i = 1 to 18
      nMove+=30
      cPloeg = alltrim(aPloegen[i])
      @ nTop,nLeft+nMove BTNBMP aBtn2[i] OF oFld:aDialogs[ 2 ] SIZE 28, 15 NOBORDER PROMPT aPloegen[i] 2007 ACTION (setploeg(oBrw[2],::cCaption),oBrw[oFld:nOption]:GoTop(),oBrw[oFld:nOption]:refresh(),oBrw[oFld:nOption]:setfocus()) font oBold CENTER
      aBtn2[i]:bClrGrad := { | lMouseOver | If( ! lMouseOver,;
      { { 0.1, 16764573, 16764573 }, ;
      { 0.1, 16764573, 16764573 } }, ;
      { { 0.1, 11524015, 11524015 }, ;
      { 0.1, 11524015, 11524015 } } ) }
   next

 

Code: Select all

function setploeg(oBrw,cPloeg)
    if msgYesNo("Change selected members")
        FOR I = 1 TO LEN(oBRW:aSELECTED) // obrw:aSELECTED is an array containing recnos marked
          leden->(dbgoto(oBRW:aSELECTED[I]))
          leden->ploeg2017 = cPloeg
        next
    endif
return NIL

 

How to make the change ?

I figured it almost out ..

I Set a oRec in the beginning when I open the file :

oRs := oCn:RowSet( "SELECT * FROM leden" )
oRec := TDataRow():New( oRs )

The buttons work like this :

@ nTop,nLeft+nMove BTNBMP aBtn2 OF oFld:aDialogs[ 2 ] SIZE 28, 15 NOBORDER PROMPT aPloegen 2007 ACTION (edit_online(oRec,::cCaption),oBrw[oFld:nOption]:GoTop(),oBrw[oFld:nOption]:refresh(),oBrw[oFld:nOption]:setfocus()) font oBold CENTER

and the function edit_online

Code: Select all

Function edit_online(oRec,cPloeg)
oRec:ploeg2017 = cPloeg
oRec:Save()
return NIL
 

Strange is that when it saves, it saves as a new record (append ?) , so there a 2 items the same and only ploeg2017 is different

The forum said that this is/can be a problem with the ID inside the database. I created the online database with

oCn:ImportFromDBF( "leden.dbf" )

[nageswaragunupudi]

Modifying data through XBrowse

Change this code:

Code: Select all

function setploeg(oBrw,cPloeg)
    if msgYesNo("Change selected members")
        FOR I = 1 TO LEN(oBRW:aSELECTED) // obrw:aSELECTED is an array containing recnos marked
          leden->(dbgoto(oBRW:aSELECTED[I]))
          leden->ploeg2017 = cPloeg
        next
    endif
return NIL
 

as below:

ALTERNATIVE-1

Code: Select all

function setploeg(oBrw,cPloeg)

    local oRs := oBrw:oDbf
    local nSaveRec := oRs:RecNo()

    if msgYesNo("Change selected members")
        FOR I = 1 TO LEN(oBRW:aSELECTED) // obrw:aSELECTED is an array containing recnos marked
          oRs:GoTo(oBRW:aSELECTED[I]))
          oRs:ploeg2017 = cPloeg
          oRs:Save()
        next
    endif
    
    oRs:GoTo( nSaveRec )
    
return NIL
 

ALTERNATIVE-2

Code: Select all

function setploeg(oBrw,cPloeg)

    local uSavePos := oBrw:BookMark
    
    if msgYesNo("Change selected members")
        FOR I = 1 TO LEN(oBRW:aSELECTED) // obrw:aSELECTED is an array containing recnos marked
          oBrw:BookMark := oBrw:aSelected[ i ]   // works for dbf, rowset, ado and all
          oBrw:ploeg2017:VarPut( cPloeg ) // Works for dbf, rowset, ado and all
        next
    endif

    oBrw:BookMark := uSavePos
    
return NIL
 

Assuming that the browse column header for field `ploeg2017` is also "ploeg2017"

Above code works whatever data we are browsing, be it RDD, ADO, ROWSET, DOLPHIN or ANY

[nageswaragunupudi]

The forum said that this is/can be a problem with the ID inside the database. I created the online database with

This is a wrong advice.

The real problem is with creating TDataRow once and using it always. TDataRow works only for one record.

Actually you can directly use oRs:FieldName := <newvalue>, oRs:Save()
Modify the code like this

Code: Select all

@ nTop,nLeft+nMove BTNBMP aBtn2[i] OF oFld:aDialogs[ 2 ] SIZE 28, 15 NOBORDER PROMPT aPloegen[i] 2007 ACTION (edit_online(oRs,::cCaption),oBrw[oFld:nOption]:GoTop(),oBrw[oFld:nOption]:refresh(),oBrw[oFld:nOption]:setfocus()) font oBold CENTER

Function edit_online(oRs,cPloeg)
oRs:ploeg2017 = cPloeg
oRs:Save()
return NIL
 

[nageswaragunupudi]

mariainv.prg is revised.

Code: Select all

#include "FiveWin.ch"

REQUEST DBFCDX

static oWndMain, oWndInvoices, oWndClients, oWndItems
static aBlankItem
static nTaxRate   := 10.0
//----------------------------------------------------------------------------//
static oRsInvoices, oRsClients, oRsItems
//----------------------------------------------------------------------------//
static oCn

// Server capabilities
static lCalcColumns, lCheckConstraint
//----------------------------------------------------------------------------//

function Main()

   local oBrush, oFont

   oCn      := ConnectToServer()
   oCn:lShowErrors   := .t.

   CheckTables()
   OpenTables()

   DEFINE BRUSH oBrush RESOURCE "background"
   DEFINE FONT oFont NAME "TAHOMA" SIZE 0,-12

   DEFINE WINDOW oWndMain TITLE "Invoicing" MDI MENU BuildMenu() VSCROLL HSCROLL BRUSH oBrush
   oWndMain:SetFont( oFont )

   BuildMainBar()

   DEFINE MSGBAR PROMPT "Invoicing app" OF oWndMain 2007 KEYBOARD DATE

   ACTIVATE WINDOW oWndMain MAXIMIZED

   RELEASE BRUSH oBrush

   oCn:Close()

return nil

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

INIT PROCEDURE inv_init

   SET DATE BRITISH
   SET CENTURY ON
   SET EPOCH TO ( YEAR( DATE() ) - 50 )

   RDDSETDEFAULT( "DBFCDX" )
   SET DELETED ON

   SetGetColorFocus()

return

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

static function BuildMainBar()

   local oBar

   DEFINE BUTTONBAR oBar OF oWndMain 2007 SIZE 70, 60 //70

   DEFINE BUTTON OF oBar PROMPT "Invoices" RESOURCE "code" ACTION Invoices()
   DEFINE BUTTON OF oBar PROMPT "Clients" RESOURCE "clients" ACTION Clients()
   DEFINE BUTTON OF oBar PROMPT "Items" RESOURCE "relation" ACTION Items()
   DEFINE BUTTON OF oBar PROMPT "Exit" RESOURCE "exit" ACTION oWndMain:End()

return nil

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

static function BuildMenu()

   local oMenu

   MENU oMenu

      MENUITEM "Tasks"
      MENU
         MENUITEM "Invoices" ACTION Invoices()
         MENUITEM "Clients"  ACTION Clients()
         MENUITEM "Items"    ACTION Items()
         SEPARATOR
         MENUITEM "Exit" ACTION oWndMain:End()
      ENDMENU

      oMenu:AddMDI()
      oMenu:AddHelp( "Invoicing app", "(c) FiveTech Software" )

   ENDMENU

return oMenu

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

static function Clients()

   local oBrw, cClrBack
   local oBar, oMsgBar

   if oWndClients == nil
      DEFINE WINDOW oWndClients MDICHILD OF oWndMain TITLE "Clients"

      @ 2, 0 XBROWSE oBrw OF oWndClients LINES AUTOSORT ;
         DATASOURCE oRsClients ;
         COLUMNS "Code", "First", "Last", "Address1", "Address2", "City", "ZipCode", "Phone", "EMail" ;
         NOBORDER

      oBar  := BrwBtnBar( @oBrw, oWndClients )

      BrwColors( oBrw )
      BrwRecSel( oBrw, "RECNO" )

      WITH OBJECT oBrw
         :bLDblClick = { || oBrw:EditSource() }
         :CreateFromCode()
      END

      WITH OBJECT oWndClients
         :SetControl( oBrw )
         :bPrint     := { || oBrw:Report( "Clients report",, .F.) }
         :bPostEnd   := { || oWndClients := nil }
      END

      DEFINE MSGBAR oMsgBar OF oWndClients 2007

      ACTIVATE WINDOW oWndClients MAXIMIZED
   else
      oWndClients:SetFocus()
   endif

return nil

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

static function Items()

   local oBrw, cClrBack
   local oBar, oMsgBar

   if oWndItems == nil
      DEFINE WINDOW oWndItems MDICHILD OF oWndMain TITLE "Items"

      @ 2, 0 XBROWSE oBrw OF oWndItems LINES AUTOSORT ;
         DATASOURCE oRsItems ;
         COLUMNS "Code", "Name", "Unit", "Price" ;
         NOBORDER

      oBar  := BrwBtnBar( @oBrw, oWndItems )

      BrwColors( oBrw )
      BrwRecSel( oBrw, "RECNO" )

      WITH OBJECT oBrw
         :bEdit      := { |oRec| EditItems( oRec ) }
         :bLDblClick = { || oBrw:EditSource() }
         :CreateFromCode()
      END

      WITH OBJECT oWndItems
         :SetControl( oBrw )
         :bPrint     := { || oBrw:Report( "Items report",, .F.) }
         :bPostEnd   := { || oWndClients := nil }
      END

      DEFINE MSGBAR oMsgBar OF oWndItems 2007

      ACTIVATE WINDOW oWndItems MAXIMIZED
   else
      oWndItems:SetFocus()
   endif

return nil

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

static function EditItems( oRec )

   local oDlg
   local lSave := .F.
   local oFont
   local oBtn
   local lAdd  := ( oRec:RecNo == 0 )

   DEFINE FONT oFont NAME "Tahoma" SIZE 0, -15

   DEFINE DIALOG oDlg SIZE 402, 158 PIXEL FONT oFont ;
      TITLE If( lAdd, "New Item", "Edit Item" )

   @ 12, 10 SAY "Code:" OF oDlg SIZE 19, 8 PIXEL FONT oFont
   @ 10, 36 GET oRec:Code  OF oDlg SIZE 55, 12 PIXEL FONT oFont PICTURE "@!" UPDATE

   @ 26, 10 SAY "Name:" OF oDlg SIZE 21, 8 PIXEL FONT oFont
   @ 24, 36 GET oRec:Name  OF oDlg SIZE 155, 12 PIXEL FONT oFont UPDATE

   @ 40, 10 SAY "Unit:" OF oDlg SIZE 18, 8 PIXEL FONT oFont

   @ 38,38 COMBOBOX oRec:Unit SIZE 44,12 PIXEL OF oDlg UPDATE ;
      ITEMS oRec:FieldCbxItems( "Unit" )

   @ 40, 10 + 111 SAY "Price:" OF oDlg SIZE 18, 8 PIXEL FONT oFont
   @ 38, 36 + 111 GET oRec:Price OF oDlg SIZE 44, 12 PIXEL PICTURE  "9999999.99" FONT oFont RIGHT UPDATE

   @ 60, 067 BTNBMP PROMPT "Undo" OF oDlg SIZE 42, 14 PIXEL FLAT ;
      WHEN oRec:Modified() ;
      ACTION ( oRec:Undo(), oDlg:Update() )
   @ 60, 111 BTNBMP PROMPT "Save" OF oDlg SIZE 42, 14 PIXEL FLAT ;
      WHEN oRec:Modified() ;
      ACTION If( oRec:Save(), oDlg:End(), nil )
   @ 60, 155 BTNBMP oBtn PROMPT "Cancel" OF oDlg SIZE 42, 14 PIXEL FLAT ACTION (oDlg:End())
   oBtn:lCancel   := .t.

   ACTIVATE DIALOG oDlg CENTERED ;
      ON PAINT ( oDlg:Box( 10, 10, 110, 392 ) )

return nil

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

static function Invoices()

   local oBrw, oChild, cClrBack, cCol
   local oBar, oMsgBar, oMsgDeleted

   if oWndClients == nil
      Clients()
   endif

   if oWndItems == nil
      Items()
   endif

   if oWndInvoices == nil
      DEFINE WINDOW oWndInvoices MDICHILD OF oWndMain TITLE "Invoices"

      @ 60, 0 XBROWSE oBrw SIZE 0,200 PIXEL OF oWndInvoices LINES AUTOSORT ;
         DATASOURCE oRsInvoices ;
         COLUMNS "InvNum", "Date", "Code", "Client", "Address", "Details", "Amount", "TaxRate", "Tax", "Total", "PayDate" ;
         NOBORDER FOOTERS

      oBar  := BrwBtnBar( @oBrw, oWndInvoices, .t. )
      oWndInvoices:bPrint  := { || ViewInvoice( oBrw ) }

      DEFINE MSGBAR oMsgBar OF oWndInvoices 2007

      oBrw:Address:nWidth := 100
      oBrw:nStretchCol  := oBrw:Address:nCreationOrder

      BrwColors( oBrw )
      BrwRecSel( oBrw, "RECNO" )

      WITH OBJECT oBrw:TaxRate
         :nEditType        := EDIT_GET
         :bClrHeader       := { || { CLR_HRED, CLR_WHITE } }
      END

      for each cCol in { "Amount", "Tax", "Total" }
         oBrw:oCol( cCol ):nFooterType  := AGGR_SUM
      next

      oBrw:bLDblClick = { ||oBrw:EditSource() }
      oBrw:bEdit = { | oRec | EditInvoice( oRec ) }
      oBrw:MakeTotals()
      oBrw:CreateFromCode()
      oBrw:SetFocus()

      oWndInvoices:oControl = oBrw

      @ oBar:nHeight + 200,0 XBROWSE oChild SIZE 0,-oMsgBar:nHeight PIXEL OF oWndInvoices ;
         DATASOURCE oRsInvoices:oChild ;
         COLUMNS "ItemCode", "ItemName", "Quantity", "Unit", "Price", ;
                 "ROUND(QUANTITY*PRICE,0)", "DISCOUNT","ROUND(QUANTITY*PRICE,0)-DISCOUNT" ;
         HEADERS "ItmCode", nil, nil, nil, nil, "Amount", "Discount", "Net Amount" ;
         LINES NOBORDER FOOTERS FASTEDIT

      BrwColors( oChild )
      BrwRecSel( oChild, "KEY" )

      for each cCol in { "Amount", "Discount", "Net Amount" }
         WITH OBJECT oChild:oCol( cCol )
            :nFooterType   := AGGR_SUM
         END
      next

      for each cCol in { "Quantity", "Price", "Discount" }
         WITH OBJECT oChild:oCol( cCol )
            :nEditType := EDIT_GET
            :bClrHeader  := { || { CLR_HRED, CLR_WHITE } }
         END
      next

      oChild:bOnChanges  := { || oRsInvoices:ReSync(), oBrw:RefreshCurrent(), oBrw:MakeTotals(), oBrw:RefreshFooters() }
      oChild:MakeTotals()
      oChild:CreateFromCode()

      oBrw:bChange   := { || oRsInvoices:SyncChild(), oRsInvoices:SetOrder("SERIAL"), ;
                             oChild:Refresh(), oChild:MakeTotals(), oChild:GoTop() }

      oWndInvoices:bResized := < ||
         local oRect    := oWndInvoices:GetCliRect()
         oBrw:nHeight   := ( oRect:nHeight - oBar:nHeight - oMsgBar:nHeight ) * 0.6
         oChild:nTop    := oBrw:nTop + oBrw:nHeight
         return nil
         >

      oWndInvoices:bPostEnd   := { || oWndInvoices := nil }

      oWndInvoices:oBar:bPainted := { |h,c,o| o:SayText( "Columns with Header in Red color can be edited inline", , "R" ) }

      ACTIVATE WINDOW oWndInvoices MAXIMIZED

   else
      oWndInvoices:SetFocus()
   endif

return nil

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

static function EditInvoice( oRec )

   local lNew     := ( oRec:RecNo == 0 )
   local oDlg, oBrush, oFont, oBold, oLarge
   local oBrw, cCol, bInit, oBtn, cSql, a
   local aItems
   local oGetClient, cClient, bCliInit
   local nHt      := Int( ScreenHeight() * 0.8 )
   local nWd      := 1100
   local lSave    := .f.
   local nInvTax, nInvTotal

   if lNew
      oRec:Date      := Date()
      oRec:TaxRate   := nTaxRate
      aItems         := { AClone( aBlankItem ) }
   else
      //oRsInvoices:oChild:ReQuery()
      Eval( oRec:oBrw:bChange )
      aItems         := oRsInvoices:oChild:GetRows()
   endif

   nInvTax     := oRec:Tax
   nInvTotal   := oRec:Total

   DEFINE BRUSH oBrush RESOURCE "PAPER"
   DEFINE FONT oLarge NAME "VERDANA" SIZE 0,-30 BOLD
   DEFINE FONT oFont NAME  "TAHOMA"  SIZE 0,-15
   DEFINE FONT oBold NAME  "TAHOMA"  SIZE 0,-15 BOLD

   DEFINE DIALOG oDlg SIZE nWd, nHt PIXEL FONT oFont TRUEPIXEL ;
      TITLE If( lNew, "NEW ", "EDIT " ) + "INVOICE" TRANSPARENT ;
      BRUSH oBrush

   @  20, nWd/2-100 SAY "INVOICE" SIZE 200,36 PIXEL OF oDlg FONT oLarge CENTER

   @ 020, nWd - 190 GET oRec:InvNum PICTURE "@!" SIZE 150,26 PIXEL OF oDlg ;
      WHEN lNew ;
      VALID Len( Trim( oRec:InvNum ) ) == 6  .and. ;
            !DUPLICATE( "invoices", "invnum", oRec:InvNum, oRec:ID )

   @ 050, nWd - 190 GET oRec:Date   SIZE 150,26 PIXEL OF oDlg RIGHT ;
                  WHEN lNew ;
                  ACTION oRec:Date := Min( MsgDate( oRec:Date ), Date() )

   @  80-60, 40 SAY "Client:" SIZE 100,24 PIXEL OF oDlg

   @  80-60,150 GET oGetClient VAR oRec:Code SIZE 150,26 PIXEL OF oDlg ;
         ACTION ( PopupBrowse( oRsClients, oGetClient, nil, 2 ), ;
                  ReadClientInfo( oRec, oDlg ) ) ;
         VALID ( ReadClientInfo( oRec, oDlg ) )

   @ 125-60, 60 SAY oRec:Client SIZE 200,24 PIXEL OF oDlg FONT oBold UPDATE
   @ 150-60, 60 SAY oRec:Address SIZE 200, 60 PIXEL OF oDlg UPDATE

   @ 180-60,310 SAY "Text :" SIZE 100,24 PIXEL OF oDlg
   @ 204-60,310 GET oRec:Details SIZE nWd-310-40,26 PIXEL OF oDlg UPDATE

   @ 240-60,040 XBROWSE oBrw SIZE -40,-150+45 PIXEL OF oDlg ;
      DATASOURCE aItems ; //  COLUMNS 3,4,5,6,7,8 ;
      COLUMNS 4,5,6,7,8,9 ;
      HEADERS "ITEM", "DETAILS", "QTY", "UNIT","PRICE","DISCOUNT" ;
      PICTURES "@!", nil, "9999.999", nil, "999.99", "999,999,999" ;
      COLSIZES nil, 30 ;
      CELL LINES NOBORDER FASTEDIT FOOTERS

   ADD TO oBrw AT 6 HEADER "AMOUNT" DATA ROUND( oBrw:aRow[6] * oBrw:aRow[8], 0 ) ;
      PICTURE "999,999,999"

   ADD TO oBrw HEADER "NET" DATA ROUND( oBrw:aRow[6] * oBrw:aRow[8] - oBrw:aRow[ 9 ], 0 ) ;
      PICTURE "999,999,999"

   for each cCol in { "amount", "discount", "net" }
      WITH OBJECT oBrw:oCol( cCol )
         :nFooterType   := AGGR_SUM
      END
   next

   for each cCol in { "qty", "price", "discount" }
      WITH OBJECT oBrw:oCol( cCol )
         :nEditType     := EDIT_GET
         :bEditValid    := { |o| o:VarGet() >= 0 }
         :bOnChange     := { || oBrw:MakeTotals( { "amount", "net" } ), oBrw:RefreshFooters(), oDlg:Update() }
      END
   next

   for each cCol in { "details", "unit", "amount", "net" }
      oBrw:oCol( cCol ):bClrStd  := { || { CLR_BLACK, RGB( 240, 240, 240 ) } }
   next

   // AutoAppendCode
   WITH OBJECT oBrw
      :AddVar( "AAPPEND", nil )
      :bClrStd    := { || If( oBrw:aRow == oBrw:aAppend, { CLR_BLACK, CLR_YELLOW }, { CLR_BLACK, oBrw:nClrPane } ) }

      :bChange    := { || If( oBrw:nArrayAt < oBrw:nLen, CheckAppendRow( oBrw ), nil ) }

      :bPastEof   := { || If( oBrw:aAppend != nil .and. Empty( oBrw:aAppend[ 4 ] ), nil, ;
               ( AAdd( oBrw:aArrayData, oBrw:aAppend := AClone( aBlankItem ) ), ;
                 oBrw:GoBottom(), oBrw:GoLeftMost(), oBrw:RefreshCurrent(), ;
                 oBrw:MakeTotals(), oBrw:Refresh() ) ) }

      :bKeyDown   := { |k| If( k == VK_DELETE, ( oBrw:aAppend := nil, oBrw:Delete(), 0 ), nil ) }

   END

   WITH OBJECT oBrw:aCols[ 1 ]
      :nEditType     = EDIT_BUTTON
      :bEditBlock    = { | nRow, nCol, oCol, nKey | TableLookUp( nRow, nCol, oCol, nKey, oRsItems, "CODE" ) }
      :bOnChange     = { || oBrw:aAppend := nil, ReadItemInfo( oBrw:aRow, oBrw ), oBrw:RefreshCurrent(), ;
                            oBrw:MakeTotals(), oBrw:RefreshFooters(), oDlg:Update() }
   END

   WITH OBJECT oBrw
      :lFlatStyle    := .t.
      :nStretchCol   := 2
      :lHScroll      := .f.
      :bOnRefresh    := { || oDlg:Update() }
      //
      BrwRecSel( oBrw, "KEYNO" )
      //
      :MakeTotals()
      :CreateFromCode()
   END

   @ nHt - 139 + 45, nWd - 380 SAY "TAX @" ;
      SIZE 80,24 PIXEL OF oDlg RIGHT

   @ nHt - 140 + 45, nWd - 280 GET oRec:TaxRate PICTURE "99.99 %" ;
      SIZE 100,26 PIXEL OF oDlg RIGHT ;
      VALID ( If( oRec:TaxRate >= 0, ( oDlg:Update(), .t. ), .f. ) )

   @ nHT - 139 + 45, nWd - 170 SAY ;
      ( nInvTax := ROUND( oBrw:Net:nTotal * oRec:TaxRate / 100, 0 ) ) ;
      PICTURE "999,999,999" SIZE 105,24 PIXEL OF oDlg UPDATE RIGHT

   @ nHt - 105 + 45, nWd - 270 SAY "TOTAL" SIZE 80, 24 PIXEL OF oDlg RIGHT

   @ nHt - 105 + 45, nWd - 170 SAY ;
      ( nInvTotal := oBrw:Net:nTotal + nInvTax ) ;
      PICTURE "999,999,999" SIZE 105, 24 PIXEL OF oDlg UPDATE RIGHT

   @ nHt - 60, 040 BTNBMP PROMPT "Save"  SIZE 100,30 PIXEL OF oDlg FLAT ;
      ACTION If( SaveInvoice( oRec, oBrw ), oDlg:End(), nil )

   @ nHt - 60, 160 BTNBMP oBtn PROMPT "Cancel" SIZE 100,30 PIXEL OF oDlg FLAT ;
      ACTION oDlg:End()
   oBtn:lCancel   := .t.

   ACTIVATE DIALOG oDlg CENTERED ;
      ON PAINT (  oDlg:Box( 110-60, 40, 230-60, 300 ), ;
                  oDlg:Line( nHt - 112 + 45, nWd - 170, nHt - 112 + 45, nWd - 55 ), ;
                  oDlg:Line( nHt -  78 + 45, nWd - 170, nHt -  78 + 45, nWd - 55 ), ;
                  oDlg:Line( nHt -  75 + 45, nWd - 170, nHt -  75 + 45, nWd - 55 )  )

   RELEASE FONT oFont, oLarge
   RELEASE BRUSH oBrush

return nil

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

static function SaveInvoice( oRec, oBrw )

   local lSaved      := .f.
   local aItems, cSql, a
   local lModified   := .f.

   CheckAppendRow( oBrw )
   aItems      := oBrw:aArrayData

   if Empty( aItems ) .or. Empty( oRec:InvNum ) .or. Empty( oRec:Code ) //.or. oRec:Total <= 0
   else
      AEval( aItems, { |a| a[ 2 ] := oRec:InvNum } )
      AEval( aItems, { |a,i| a[ 3 ] := i } )

      lModified   := oRec:Modified() .or. ItemsModified( aItems )
      if lModified
         BEGIN SEQUENCE
         oCn:Execute( "BEGIN" )
            oRec:Save()
            if oCn:nError != 0
               BREAK
            endif

            if !Empty( oBrw:aDeleted )
               a  := {}
               AEval( oBrw:aDeleted, { |x| If( Empty( x[ 1 ] ), nil, AAdd( a, x[ 1 ] ) ) } )
               // Array of IDs (primary key) to delete
               if !Empty( a )
                  cSql     := "DELETE FROM invitems WHERE ID IN " + oCn:ValToSQL( a )
                  oCn:Execute( cSql )
                  if oCn:nError != 0
                     BREAK
                  endif
               endif
            endif

            oCn:Insert( "invitems", nil, aItems, .t. )
            if oCn:nError != 0
               BREAK
            endif
            oRsInvoices:ReSync()
            oCn:Execute( "COMMIT" )
            lSaved   := .t.
         RECOVER
            oCn:Execute( "ROLLBACK" )
         END SEQUENCE
      else
         lSaved   := .t.
      endif

      if lSaved
         WITH OBJECT oRec:oBrw
            :RefreshCurrent()
            :MakeTotals()
            :RefreshFooters()
            Eval( :bChange, oRec:oBrw )
         END
      else
         ? "Failed to Save Invoice"
      endif
   endif

return lSaved

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

static function ItemsModified( aItems )

   local lModified   := .f.
   local i, j
   local aData, nCols

   if Len( aItems ) == oRsInvoices:oChild:RecCount()
      aData       := oRsInvoices:oChild:GetRows()
      nCols       := Len( aItems[ 1 ] )
      for i := Len( aItems ) to 1 step -1
         for j := 1 to nCols
            if aItems[ i, j ] != aData[ i, j ]
               lModified   := .t.
               EXIT
            endif
         next
      next
   else
      lModified   := .t.
   endif

return lModified

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

static function CheckAppendRow( oBrw, aAppend )

   if Empty( ATail( oBrw:aArrayData )[ 4 ] )  // item code is empty
      ASize( oBrw:aArrayData, oBrw:nLen - 1 )
      oBrw:aAppend  := nil
      oBrw:Refresh()
   endif

return nil

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

static function ReadClientInfo( oRec, oDlg )

   local cSql, aRet
   local lValid   := .f.

   cSql  := "SELECT CONCAT_WS( ' ', FIRST, LAST ) AS CLIENT, " + ;
            "CONCAT_WS( '\r\n', ADDRESS1, ADDRESS2, CONCAT_WS( ' ', CITY, ZIPCODE ) ) " + ;
            "AS ADDRESS FROM clients WHERE CODE = ?"

   aRet  := oCn:Execute( cSql, { oRec:Code } )
   if !Empty( aRet )
      oRec:Client    := aRet[ 1, 1 ]
      oRec:Address   := aRet[ 1, 2 ]
      lValid         := .t.
   endif

   if oDlg != nil
      oDlg:Update()
   endif

return lValid

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

static function ReadItemInfo( aRow, oBrw )

   local uBm      := oRsItems:BookMark
   local bSeek    := oRsItems:ExprAsBlock( "CODE = ?", { TRIM( aRow[ 4 ] ) } )
   local lValid   := .f.

   if oRsItems:Locate( bSeek )
      aRow[ 5 ]   := Trim( oRsItems:Name )
      if aRow[ 6 ] == 0
         aRow[ 6 ]   := 1
      endif
      aRow[ 7 ]      := oRsItems:UNIT
      aRow[ 8 ]      := oRsItems:PRICE
      lValid         := .t.
   endif
   oRsItems:BookMark := uBm

   if oBrw != nil
      oBrw:RefreshCurrent()
      oBrw:MakeTotals()
      oBrw:oWnd:Update()
   endif

return lValid

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

static function ViewInvoice( oBrw )

   local oPrn, oFontTitle, oFontBold, oFontText, oPen, n
   local nVal, nPage, nItemsByPage := 10
   local aItems   := oRsInvoices:oChild:GetRows()

   PRINT oPrn NAME "INVOICE" PREVIEW

   DEFINE FONT oFontTitle NAME "Arial" SIZE 0, -19 BOLD OF oPrn
   DEFINE FONT oFontBold  NAME "Arial" SIZE 0, -12 BOLD OF oPrn
   DEFINE FONT oFontText  NAME "Arial" SIZE 0, -12 OF oPrn
   DEFINE PEN oPen WIDTH 11

   for nPage = 1 to ( Len( aItems ) / nItemsByPage ) + 1
      PAGE
         oPrn:CmSay( 3.1, 2.3, "Company Name", oFontTitle )

         oPrn:CmBox( 4.4, 10.9, 7.7, 20.15, oPen )
         oPrn:CmSay( 4.7, 11.5, oBrw:Client:Value, oFontBold )
/*
      oPrn:CmSay( 5.4, 11.5, "Address 1", oFontText )
      oPrn:CmSay( 6.1, 11.5, "Address 2", oFontText )
      oPrn:CmSay( 6.8, 11.5, "City", oFontText )
*/
         @ 5.4, 11.5 PRINT TO oPrn TEXT oBrw:Address:Value SIZE 7.0 CM FONT oFontText

         oPrn:CmBox( 8.15, 2.20, 8.75, 20.15, oPen )
         oPrn:CmSay( 8.16,  2.30, "C.I.F.:", oFontBold )
         oPrn:CmSay( 8.16,  6.00, "Invoice nº:", oFontBold )
         oPrn:CmSay( 8.16,  8.30, oBrw:InvNum:Value, oFontText )
         oPrn:CmSay( 8.16, 11.20, "Date:", oFontBold )
         oPrn:CmSay( 8.20, 12.45, DToC( oBrw:Date:Value ), oFontText )
         oPrn:CmSay( 8.16, 15.60, "PayDate:", oFontBold )
         oPrn:CmSay( 8.20, 17.50, DToC( oBrw:PayDate:Value ), oFontText )

         oPrn:CmBox( 8.90, 2.20, 9.50, 20.15, oPen )
         oPrn:CmSay ( 8.91, 2.30, "Observations:", oFontBold )

         oPrn:CmBox( 9.65, 2.20, 23.25, 20.15, oPen )
         oPrn:CmLine( 9.65, 5.20, 23.25, 5.20, oPen )
         oPrn:CmLine( 9.65, 12.20, 23.25, 12.20, oPen )
         oPrn:CmLine( 9.65, 13.80, 23.25, 13.80, oPen )
         oPrn:CmLine( 9.65, 16.10, 23.25, 16.10, oPen )
         oPrn:CmLine( 9.65, 17.10, 23.25, 17.10, oPen )
         oPrn:CmLine( 10.20, 2.20, 10.20, 20.15, oPen )

         oPrn:CmSay( 9.66,  2.30, "Code", oFontBold )
         oPrn:CmSay( 9.66,  5.30, "Description", oFontBold )
         oPrn:CmSay( 9.66, 12.30, "Quantity", oFontBold )
         oPrn:CmSay( 9.66, 14.00, "Price", oFontBold )
         oPrn:CmSay( 9.66, 16.15, "Disc", oFontBold )
         oPrn:CmSay( 9.66, 18.20, "Amount", oFontBold )

      // "INVNUM,SERIAL,ITEMCODE,ITEMNAME,QUANTITY,UNIT,PRICE,DISCOUNT"
      //     2      3       4       5        6       7     8      9
         for n = ( ( nPage - 1 ) * nItemsByPage ) + 1 to Min( Len( aItems ), nItemsByPage * nPage )
            oPrn:CmSay( 9.66 + n - ( ( nPage - 1 ) * nItemsByPage ), 3, aItems[ n ][ 4 ], oFontText )
            oPrn:CmSay( 9.66 + n - ( ( nPage - 1 ) * nItemsByPage ), 5.5, aItems[ n ][ 5 ], oFontText )
            oPrn:CmSay( 9.66 + n - ( ( nPage - 1 ) * nItemsByPage ), 12.8, AllTrim( Str( aItems[ n ][ 6 ] ) ), oFontText )
            oPrn:CmSay( 9.66 + n - ( ( nPage - 1 ) * nItemsByPage ), 14.5, AllTrim( Str( aItems[ n ][ 8 ] ) ), oFontText )
            oPrn:CmSay( 9.66 + n - ( ( nPage - 1 ) * nItemsByPage ), 16.5, AllTrim( Str( aItems[ n ][ 9 ] ) ), oFontText )
            nVal  := ROUND( aItems[ n ][ 6 ] * aItems[ n ][ 8 ] - aItems[ n ][ 9 ], 0 )
            oPrn:CmSay( 9.66 + n - ( ( nPage - 1 ) * nItemsByPage ), 18.5, AllTrim( Str( nVal ) ), oFontText )
         next

         oPrn:CmBox( 23.40, 2.20, 27.20, 20.15, oPen )
         oPrn:CmLine( 24.00, 2.20, 24.00, 20.15, oPen )
         oPrn:CmLine( 23.40, 4.95, 26.20, 4.95, oPen )
         oPrn:CmLine( 23.40, 6.45, 26.20, 6.45, oPen )
         oPrn:CmLine( 23.40, 9.35, 26.20, 9.35, oPen )
         oPrn:CmLine( 23.40, 10.95, 26.20, 10.95, oPen )
         oPrn:CmLine( 23.40, 13.80, 26.20, 13.80, oPen )
         oPrn:CmLine( 23.40, 16.65, 26.20, 16.65, oPen )
         oPrn:CmSay( 23.50,  2.40, "BASE", oFontBold )
         oPrn:CmSay( 23.50,   5.20, "%VAT", oFontBold )
         oPrn:CmSay( 23.50,   6.75, "CUOTE", oFontBold )
         oPrn:CmSay( 23.50,   9.60, "%RE", oFontBold )
         oPrn:CmSay( 23.50,  11.20, "CUOTE", oFontBold )
         oPrn:CmSay( 23.50,  14.10, "AMOUNT", oFontBold )
         oPrn:CmSay( 23.50, 16.95, "SUM BASES:", oFontBold )

         oPrn:CmSay( 23.50 + 1.5,  2.40, cValToStr( oBrw:Amount:Value ), oFontText )
         oPrn:CmSay( 23.50 + 1.5,  5.20, cValToStr( oBrw:TaxRate:Value ), oFontText )

         oPrn:CmSay( 26.44,  2.40, "TOTAL VAT:", oFontBold )
         oPrn:CmSay( 26.44,  5.50, cValToStr( oBrw:Tax:Value ), oFontText )
         oPrn:CmSay( 26.44,  7.70, "TOTAL R.E.:", oFontBold )
         oPrn:CmSay( 26.44, 13.35, "TOTAL INVOICE", oFontBold )
         oPrn:CmSay( 26.44, 17.50, cValToStr( oBrw:Total:Value ), oFontBold )
      ENDPAGE
   next

   ENDPRINT

   oFontTitle:End()
   oFontBold:End()
   oFontText:End()
   oPen:End()

return nil

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

static function BrwBtnBar( oBrw, oWnd, lInvBrowse )

   local oBar

   DEFINE BUTTONBAR oBar OF oWnd 2007 SIZE 70, 60 //70

   DEFINE BUTTON OF oBar PROMPT "New" RESOURCE "add" ;
      ACTION oBrw:EditSource( .T. )

   DEFINE BUTTON OF oBar PROMPT "Edit" RESOURCE "edit" ;
      ACTION oBrw:EditSource()

   DEFINE BUTTON OF oBar PROMPT "Delete" RESOURCE "del" ;
      ACTION If( MsgYesNo( "Confirm Delete" ), oBrw:Delete(), nil )

   DEFINE BUTTON OF oBar PROMPT "Refresh" RESOURCE "REDO" ;
      ACTION ( oBrw:oDbf:Requery(), oBrw:Refresh(), XEval( oBrw:bChange ), oBrw:SetFocus() ) ;
      TOOLTIP "Read Data Again from the Server"

   if lInvBrowse == .t.
      DEFINE BUTTON OF oBar PROMPT "Update" RESOURCE "relation" ;
         ACTION ( UpdateTotals(), oBrw:Refresh(), oBrw:SetFocus() ) ;
         TOOLTIP "Update totals from invoice items"
   endif

   DEFINE BUTTON OF oBar PROMPT "Preview" RESOURCE "report" ;
      ACTION oBar:oWnd:Print() GROUP

   DEFINE BUTTON OF oBar PROMPT "Close" RESOURCE "exit" ;
      ACTION oBar:oWnd:End()

return oBar

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

static function BrwColors( oBrw )

   local cClrBack

   oBrw:nMarqueeStyle := MARQSTYLE_HIGHLROW
   oBrw:bClrStd = { || If( oBrw:KeyNo() % 2 == 0, ;
                         { CLR_BLACK, RGB( 198, 255, 198 ) }, ;
                         { CLR_BLACK, RGB( 232, 255, 232 ) } ) }
   oBrw:bClrSel = { || { CLR_WHITE, RGB( 0x33, 0x66, 0xCC ) } }
   cClrBack = Eval( oBrw:bClrSelFocus )[ 2 ]
   oBrw:bClrSelFocus = { || { CLR_WHITE, cClrBack } }
   oBrw:SetColor( CLR_BLACK, RGB( 232, 255, 232 ) )

   oBrw:lHScroll  := .f.

return nil

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

static function BrwRecSel( oBrw, cHead )

   WITH OBJECT oBrw
      :lFooter    := .t.
      if "REC" $ Upper( cHead )
         :bRecSelHeader    := { || "RecNo" }
         :bRecSelData      := { |o| o:BookMark }
         :bRecSelClick     := { |o| o:oDbf:OrdSetFocus( 0 ), ;
                                    AEval( o:aCols, { |c| c:cOrder := "" } ), ;
                                    o:Refresh() }
      else
         :bRecSelHeader    := { || "SlNo" }
         :bRecSelData      := { |o| o:KeyNo }
      endif
      :bRecSelFooter    := { |o| o:nLen }
      :nRecSelWidth     := Replicate( '9', Len( cValToChar( Eval( oBrw:bKeyCount, oBrw ) ) ) + 1 )

   END

retur nil

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

static function Duplicate( cTable, cField, uVal, nThisID )

   local lExists     := .f.
   local cSql, cWhere, oRs, uBm

   cWhere   := oCn:ApplyParams( cField + " = ? AND ID <> ?", { uVal, nThisID } )

   if HB_ISOBJECT( cTable )
      oRs      := cTable
      uBm      := oRs:BookMark
      lExists  := oRs:Locate( cWhere )
      oRs:BookMark   := uBm
   else
      cSql  := "SELECT " + cField + " FROM " + Lower( cTable ) + ;
               " WHERE " + cWhere + " LIMIT 1"

      lExists  := !Empty( oCn:QueryResult( cSql ) )
   endif

return lExists

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

static function TableLookUp( nRow, nCol, oCol, nKey, uSource, uRetCol, aCols )

   local oDlg, oBrw, uRet
   local aPoint
   local oFont, oBold
   local aCellCoor   := oCol:oBrw:aCellCoor()

   DEFAULT uRetCol   := 1

   DEFINE FONT oFont NAME "ARIAL" SIZE 0,-12
   DEFINE FONT oBold NAME "ARIAL" SIZE 0,-12 BOLD

   aPoint      := ClientToScreen( oCol:oBrw:hWnd, { aCellCoor[ 1 ], aCellCoor[ 2 ] } )

   uSource:Sort   := "CODE"
   uSource:Seek( oCol:Value, .t. )

   DEFINE DIALOG oDlg SIZE 300,300 PIXEL TRUEPIXEL ;
      STYLE WS_POPUP OF oCol:oBrw FONT oFont ;
      COLOR CLR_BLACK, 1
   oDlg:nSeeThroClr  := 1

   @ aCellCoor[ 3 ] - aCellCoor[ 1 ],0 XBROWSE oBrw SIZE 0,0 PIXEL OF oDlg ;
      DATASOURCE uSource AUTOCOLS ;
      AUTOSORT CELL LINES NOBORDER ;
      COLOR CLR_BLACK, RGB( 232, 255, 232 )

   WITH OBJECT oBrw
      :lHScroll         := .f.
      :lRecordSelector  := .f.
      :lDrawBorder      := .t.
      //
      :lIncrFilter      := .t.
      :lSeekWild        := .t.
      :oCol( uRetCol ):oDataFont := oBold

      :cFilterFld       := "CODE"

      :bKeyDown   := { |nKey| If( nKey == VK_RETURN, (  uRet := oBrw:oCol( uRetCol ):Value, oDlg:End() ), nil ) }
      :bKeyChar   := { |nKey| If( nKey == VK_ESCAPE, ( oBrw:Seek( "" ), oDlg:End() ), nil ) }
      :bLDClickDatas := { || uRet := oBrw:oCol( uRetCol ):Value, oDlg:End() }

      :AutoFit()
      :CreateFromCode()
   END

   @ 00,00 SAY oBrw:oSeek PROMPT oBrw:cSeek PICTURE "@!" ;
      SIZE aCellCoor[ 4 ] - aCellCoor[ 2 ], aCellCoor[ 3 ] - aCellCoor[ 1 ] PIXEL OF oDlg COLOR CLR_HRED, CLR_YELLOW ;
      FONT oCol:DataFont

   WITH OBJECT oBrw:oSeek
      :lWantClick    := .t.
      :bLClicked     := { || oDlg:End() }
   END

   ACTIVATE DIALOG oDlg ;
      ON PAINT oDlg:Box( 0, 0, aCellCoor[ 3 ] - aCellCoor[ 2 ], aCellCoor[ 4 ] - aCellCoor[ 2 ] ) ;
      ON INIT BrwHelpDlgInit( oBrw, aPoint ) ;
      VALID ( oBrw:Seek( "" ), .t. )

   RELEASE FONT oFont, oBold

return uRet

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

static function BrwHelpDlgInit( oBrw, aPoint )

   local oDlg  := oBrw:oWnd
   local dy    := oDlg:GetRect():nWidth - oDlg:GetCliRect():nWidth
   local aSize

   aSize := oBrw:BrwFitSize()
   oDlg:nWidth    := aSize[ 1 ] + dy
   oDlg:nHeight   := oBrw:nTop + aSize[ 2 ]

   oDlg:SetPos( aPoint[ 1 ], aPoint[ 2 ] )
   oDlg:Shadow()

return nil

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

static function DropAllTables()

   oCn:DropTable( "invitems" )
   oCn:DropTable( "invoices" )
   oCn:DropTable( "items" )
   oCn:DropTable( "clients" )

return nil

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

static function CheckTables()

   if !oCn:TableExists( "clients" );  CreateClients(); endif
   if !oCn:TableExists( "items"   );  CreateItems();   endif
   if !oCn:TableExists( "invoices" ); CreateInvoices(); endif
   if !oCn:TableExists( "invitems" ); CreateInvItems(); endif

return nil

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

static function OpenTables()

   oRsClients     := oCn:RowSet( "select * from clients" )
   oRsItems       := oCn:RowSet( "select * from items" )
   oRsInvoices    := oCn:RowSet( "select * from invoices" )
   WITH OBJECT oRsInvoices
      :Fields( "Amount" ):lReadOnly := .t.
      :Fields( "Tax"    ):lReadOnly := .t.
      :Fields( "Total"  ):lReadOnly := .t.
      //
      :AddChild( "invitems" )
   END

   aBlankItem  := oRsInvoices:oChild:BlankRow()

return nil

//----------------------------------------------------------------------------//
// CREATION OF TABLES
//----------------------------------------------------------------------------//

static function CreateClients()

   local csql
   local aCols

   aCols    := { ;
      { "CODE",      'C', 10, 0, "latin1, UNI, NOT NULL, CHECK( CHAR_LENGTH( `code` ) > 2 ), COMMENT 'CASE:UPPER'" }, ;
      { "FIRST",     'C', 20, 0, "NOT NULL, CHECK( CHAR_LENGTH( `first` ) > 2 ), COMMENT 'CASE:PROPER'" }, ;
      { "LAST",      'C', 20, 0, "COMMENT 'CASE:PROPER'" }, ;
      { "ADDRESS1",  'C', 30, 0, "NOT NULL, CHECK( CHAR_LENGTH( `address1` ) > 2 ), COMMENT 'CASE:PROPER'" }, ;
      { "ADDRESS2",  'C', 30, 0, "COMMENT 'CASE:PROPER'" }, ;
      { "CITY",      'C', 20, 0, "NOT NULL, CHECK( CHAR_LENGTH( `city` ) > 1 ), COMMENT 'CASE:PROPER'" }, ;
      { "ZIPCODE",   'C', 20, 0 }, ;
      { "PHONE",     'C', 20, 0 }, ;
      { "EMAIL",     'C', 20, 0, "COMMENT 'CASE:LOWER'" } }

   ocn:createtable( "clients", acols, nil, "utf8" )

   oCn:Execute( "DROP PROCEDURE IF EXISTS clients_check" )

if lCheckConstraint
   // we do not need these triggers
else
//
TEXT INTO cSql
   CREATE PROCEDURE clients_check( IN cCode VARCHAR( 10 ), IN cName VARCHAR( 20 ),
      IN cAddress VARCHAR( 30 ), IN cCity VARCHAR( 20 ) )
   BEGIN
      IF CHAR_LENGTH( cCode )    < 3 OR
         CHAR_LENGTH( cName )    < 3 OR
         CHAR_LENGTH( cAddress ) < 3 OR
         CHAR_LENGTH( cCity )    < 2 THEN

         SIGNAL SQLSTATE '45000'
         SET MESSAGE_TEXT = 'Code, First, Address, City too short';
      END IF;
   END;
ENDTEXT

   oCn:Execute( cSql )

TEXT INTO cSql
   CREATE TRIGGER clients_bi BEFORE INSERT ON `clients`
   FOR EACH ROW
   BEGIN
      CALL clients_check( NEW.CODE, NEW.FIRST, NEW.ADDRESS1, NEW.CITY );
   END;
ENDTEXT

   oCn:Execute( cSql )

TEXT INTO cSql
   CREATE TRIGGER clients_bu BEFORE UPDATE ON `clients`
   FOR EACH ROW
   BEGIN
      CALL clients_check( NEW.CODE, NEW.FIRST, NEW.ADDRESS1, NEW.CITY );
   END;
ENDTEXT

   oCn:Execute( cSql )
//
endif  // if lCheckConstraint
//
   if File( "clients2.dbf" )
      use clients2
      ocn:uploadfromalias( "clients" )
      close data
   endif

return nil

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

static function CreateItems

   local cSql, aCols
   aCols    := { ;
      { "CODE",     "C",  5, 0, "latin1, UNI, NOT NULL, CHECK( CHAR_LENGTH( `code` ) > 2 ), COMMENT 'CASE:UPPER'" }, ;
      { "NAME",     "C", 30, 0, "utf8, DEFAULT 'Name', CHECK( CHAR_LENGTH( `name` ) > 2 ), COMMENT 'CASE:PROPER'" }, ;
      { "UNIT",     "ENUM ( 'Items', 'K.G', 'Metre', 'Litre' ) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT 'Items'" }, ;
      { "PRICE",     "N",  6, 2, "DEFAULT 1.0, CHECK( `price` > 0 )" } }

   oCn:CreateTable( "items", aCols, nil, "utf8" )

   oCn:Execute( "DROP PROCEDURE IF EXISTS items_check" )

if lCheckConstraint
   // no triggers required
else
//
TEXT INTO cSql
   CREATE PROCEDURE items_check( IN cCode VARCHAR( 5 ), IN cName VARCHAR(30), IN nPrice DECIMAL( 5, 2 ) )
   BEGIN
      IF nPrice <= 0.0 THEN
         SIGNAL SQLSTATE '45000'
             SET MESSAGE_TEXT = 'Price should be positive';
      END IF;
      IF CHAR_LENGTH( cCode ) < 3 THEN
         SIGNAL SQLSTATE '45000'
         SET MESSAGE_TEXT = 'Code should be more than 2 chars';
      END IF;
      IF CHAR_LENGTH( cName ) < 3 THEN
         SIGNAL SQLSTATE '45000'
         SET MESSAGE_TEXT = 'Name should be more than 2 chars';
      END IF;
   END;
ENDTEXT

   oCn:Execute( cSql )

TEXT INTO cSql
   CREATE TRIGGER items_bi BEFORE INSERT ON `items`
   FOR EACH ROW
   BEGIN
      CALL items_check( NEW.CODE, NEW.NAME, NEW.PRICE );
   END;
ENDTEXT

   oCn:Execute( cSql )

TEXT INTO cSql
   CREATE TRIGGER items_bu BEFORE UPDATE ON `items`
   FOR EACH ROW
   BEGIN
      CALL items_check( NEW.CODE, NEW.NAME, NEW.PRICE );
   END;
ENDTEXT

   oCn:Execute( cSql )
//
endif  // if lCheckConstraint
//

   if File( "items.dbf" )
      use items
      ocn:uploadfromalias( "items" )
   endif
   close data

return nil

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

static function CreateInvoices()

   local aCols, cSql

   if lCalcColumns
      aCols    := { ;
         { "INVNUM",    'C',  6, 0, "latin1, UNI, NOT NULL, CHECK( CHAR_LENGTH( INVNUM ) = 6 ), COMMENT 'CASE:UPPER'" }, ;
         { "DATE",      'D',  8, 0, "NOT NULL" }, ;
         { "CODE", "REFERENCES clients(CODE)" }, ;
         { "CLIENT",    'C', 40, 0 }, ;
         { "ADDRESS",   'C',110, 0 }, ;
         { "DETAILS",   'C', 30, 0 }, ;
         { "AMOUNT",    'N',  9, 0, "DEFAULT 0" }, ;
         { "TAXRATE",   'N',  5, 2, "DEFAULT 0" }, ;
         { "TAX=AMOUNT*TAXRATE/100", 'N', 9, 0 }, ;
         { "TOTAL=amount+tax", 'N', 9, 0}, ;
         { "PAYDATE",   'D',  8, 0 }  }
   else
      aCols    := { ;
         { "INVNUM",    'C',  6, 0, "latin1, UNI, NOT NULL, COMMENT 'CASE:UPPER'" }, ;
         { "DATE",      'D',  8, 0, "NOT NULL" }, ;
         { "CODE", "REFERENCES clients(CODE)" }, ;
         { "CLIENT",    'C', 40, 0 }, ;
         { "ADDRESS",   'C',110, 0 }, ;
         { "DETAILS",   'C', 30, 0 }, ;
         { "AMOUNT",    'N',  9, 0, "DEFAULT 0" }, ;
         { "TAXRATE",   'N',  5, 2, "DEFAULT 0" }, ;
         { "TAX",       'N',  9, 0 }, ;
         { "TOTAL",     'N',  9, 0 }, ;
         { "PAYDATE",   'D',  8, 0 }  }
   endif

   oCn:CreateTable( "invoices", aCols, nil, "utf8" )
   oCn:Execute( "DROP PROCEDURE IF EXISTS invoices_check" )

if lCheckConstraint

TEXT INTO cSql
   CREATE PROCEDURE invoices_check( IN cInvNum VARCHAR( 6 ), IN dDate DATE )
   BEGIN
      IF dDate > CURDATE() THEN
         SIGNAL SQLSTATE '45000'
         SET MESSAGE_TEXT = 'Advance Date not permitted';
      END IF;
   END;
ENDTEXT

else
//
TEXT INTO cSql
   CREATE PROCEDURE invoices_check( IN cInvNum VARCHAR( 6 ), IN dDate DATE )
   BEGIN
      IF CHAR_LENGTH( cInvNum ) < 6 THEN
         SIGNAL SQLSTATE '45000'
         SET MESSAGE_TEXT = 'InvNum should have 6 chars';
      ELSEIF dDate > CURDATE() THEN
         SIGNAL SQLSTATE '45000'
         SET MESSAGE_TEXT = 'Advance Date not permitted';
      END IF;
   END;
ENDTEXT

endif // lCheckConstraint

   oCn:Execute( cSql )

if lCalcColumns
//
oCn:Execute( "DROP TRIGGER IF EXISTS invoices_bi" )
TEXT INTO cSql
   CREATE TRIGGER invoices_bi BEFORE INSERT ON `invoices`
   FOR EACH ROW
   BEGIN
      CALL invoices_check( NEW.INVNUM, NEW.`DATE` );
   END;
ENDTEXT

   oCn:Execute( cSql )

TEXT INTO cSql
   CREATE TRIGGER invoices_bu BEFORE UPDATE ON `invoices`
   FOR EACH ROW
   BEGIN
      CALL invoices_check( NEW.INVNUM, NEW.`DATE` );
   END;
ENDTEXT

   oCn:Execute( cSql )
//
else
//
TEXT INTO cSql
   CREATE TRIGGER invoices_bi BEFORE INSERT ON `invoices`
   FOR EACH ROW
   BEGIN
      CALL invoices_check( NEW.INVNUM, NEW.`DATE` );
      SET NEW.TAX = NEW.AMOUNT * NEW.TAXRATE / 100.0;
      SET NEW.TOTAL = NEW.AMOUNT + NEW.TAX;
   END;
ENDTEXT

   oCn:Execute( cSql )

TEXT INTO cSql
   CREATE TRIGGER invoices_bu BEFORE UPDATE ON `invoices`
   FOR EACH ROW
   BEGIN
      CALL invoices_check( NEW.INVNUM, NEW.`DATE` );
      SET NEW.TAX = NEW.AMOUNT * NEW.TAXRATE / 100.0;
      SET NEW.TOTAL = NEW.AMOUNT + NEW.TAX;
   END;
ENDTEXT

   oCn:Execute( cSql )

//
endif // lCalcColumns

   if File( "invoices.dbf" )
      use invoices
      ocn:uploadfromalias( "invoices", "INVNUM,DATE,CODE,CLIENT,ADDRESS,DETAILS,TAXRATE" )
      close data
   endif

return nil

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

static function CreateInvItems()

   local aCols, cSql

   aCols    := { ;
      { "INVNUM", "REFERENCES invoices(INVNUM) ON UPDATE CASCADE ON DELETE CASCADE" }, ;
      { "SERIAL",    'N',  3, 0 }, ;
      { "ITEMCODE", "REFERENCES items(CODE)" }, ;
      { "ITEMNAME",  'C', 30, 0 }, ;
      { "QUANTITY",  'N',  8, 3, "DEFAULT 0" }, ;
      { "UNIT",     "ENUM ( 'Items', 'K.G', 'Metre', 'Litre' ) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT 'Items'" }, ;
      { "PRICE",     'N',  6, 2, "DEFAULT 0" }, ;
      { "DISCOUNT",  'N',  9, 0, "DEFAULT 0" } }

   oCn:CreateTable( "invitems", aCols, nil, "utf8" )

TEXT INTO cSql
   CREATE TRIGGER invitems_ai AFTER INSERT ON `invitems`
   FOR EACH ROW
   BEGIN
      UPDATE invoices
      SET amount = amount +
          ( ROUND( NEW.quantity * NEW.price, 0 ) - NEW.discount )
      WHERE invnum = NEW.invnum;
   END;
ENDTEXT

   oCn:Execute( cSql )

TEXT INTO cSql
   CREATE TRIGGER invitems_au AFTER UPDATE ON `invitems`
   FOR EACH ROW
   BEGIN
      UPDATE invoices
      SET amount = amount -
          ( ROUND( OLD.quantity * OLD.price, 0 ) - OLD.discount )
      WHERE invnum = OLD.invnum;
      UPDATE invoices
      SET amount = amount +
          ( ROUND( NEW.quantity * NEW.price, 0 ) - NEW.discount )
      WHERE invnum = NEW.invnum;
   END;
ENDTEXT

   oCn:Execute( cSql )

TEXT INTO cSql
   CREATE TRIGGER invitems_ad AFTER DELETE ON `invitems`
   FOR EACH ROW
   BEGIN
      UPDATE invoices
      SET amount = amount -
          ( ROUND( OLD.quantity * OLD.price, 0 ) - OLD.discount )
      WHERE invnum = OLD.invnum;
   END;
ENDTEXT

   oCn:Execute( cSql )

   if File( "invitems.dbf" )
      use invitems
      oCn:UploadFromAlias( "invitems" )
      close data
      UpdateTotals()
   endif

return nil

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

static function UpdateTotals()

   oCn:UpdateSummary( "invoices", "invnum", "amount", ;
                      "invitems", "invnum", "quantity * price - discount" )
return nil

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

static function ConnectToServer()

   local cHost, cUser, cPassword, cDb
   local nServer := 1

   nServer  := Alert( "Select Server", { "DEMO SERVER", "OWN SERVER" } )
   if nServer < 1
      QUIT
   endif
   if nServer == 1
      oCn   := FW_DemoDB( 1 )
   else
      EDITVARS cHost, cUser, cPassword, cDB
      FWCONNECT oCn HOST cHost USER cUser PASSWORD cPassword DATABASE cDB
      if oCn == nil
         ? "Connect Fail"
         QUIT
      else
         oCn:lShowErrors := .t.
         if MsgNoYes( "Create All Tables?" )
            DropAllTables()
         endif
      endif
   endif

   lCalcColumns      := ( oCn:nVersion >  5.70 )
   lCheckConstraint  := ( oCn:nVersion > 10.20 )

   if nServer == 2 .and. lCalcColumns
      MsgInfo( "Calculated Columns" + ;
               If( lCheckConstraint, CRLF + "Check Constraint", "" ), ;
               "SERVER CAPABILITIES" )
   endif

return oCn

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

Please download again the zip file from the link and unzip to any folder and run the exe. Download Link: http://anserkk.com/gnraomysql/view.php?id=18

When you execute, you have a choice to connect to the same demo server in the cloud or your own server. If you choose you can enter your credentials and connect to your own server. The program will create all the tables on your server and proceeed.

The revision is made to demonstrate the following additionally:

1) Server capabilities:
(a) Calculated columns: MySql server 5.7.1 and the current stable version of MariaDB server both provide this feature. If you server supports this feature, the invoices table will be created using this feature. Please see function CreateInvoices() to notice the difference in the table definition and also the triggers.

(b) Check Constraint: As of now only MariaDB server version 10.2.1 supports this feature. We have implemented field validatiosn through triggers. If your server supports this feature, the program creates tables to use this feature. In this case the triggers are omitted in most cases. You can see the syntax how to use CHECK constraint and also how it works during execution, if you install this version of the server.

Check constraints can be for a column or for a table. In this sample, we demonstrated only column level constraints but not table level constraints. Table level constraints can compare values of two or more columns like CHECK( thisdate > thatdate ). The constraint can not use indeterminstic values for comparison. For example ( price > 0 ) is accepted but not ( `date` <= CURDATE() ). These kind of validations are still required to be done in triggers only.

Please note that using both features of calculated columns and check constrains most of the triggers are eliminated.

2) Edit: While we used FWH default dialog for editing clients, we used programmer's own dialog for Items. This is to demonstrate how to make the dialog and how to define action for the "Save" button.

In both the cases, we do not have any validations at the dialog level but totally depend on the server validations. The purpose is the demonstrate how the front-end dialog can benefit from the server-side validations either through triggers or check constraints. However, having the validations in the front-end dialog saves round-trips to the server for validations.

3) ENUM field type: This was included in the first sample also but explained now. Only MySql/MariaDB suppots ENUM field type. At the time of creating the table, we can define the possible values of a column. Only one of the values is accepted as input by the server.

Example:

Code: Select all

{ "UNIT",     "ENUM ( 'Items', 'K.G', 'Metre', 'Litre' ) DEFAULT 'Items'" }
 

This works similar to CHECK constrain in other RDBMSs like "UNIT VarChar(5 ) CHECK( unit in ( 'Items', 'K.G', 'Metre', 'Litre' )" but differs in the way of storage.

Support to ENUM columns by FWHMARIALIB:
When the rowset is created, the permitted choices of the ENUM column also are read. These choices are availble in oRs:Fields( "unit" ):List.

When XBrowse is created, the fields is created with EDIT_LISTBOX with these choices automatically, without any effort by the programmer. Sameway the default dialog of DataRow also uses these choices automatically as combobox.

Function EditItems() demonstrates how the programmer can use this list to build combobox in his dialog.

4) Updating the values in invoices table from the aggregates in the invitems table: In the case of our sample, the `amount` column is updated when invitems table is modified through triggers. This should normally guarantee the data integrity. But a bad front-end or program can fail this, by directly writing data to this field. However in similar cases, we may like to occassionally refresh the values in the parent table from the aggregates of the child table.

Please see function UpdateTotals(). This is done by calling this method:

Code: Select all

   oCn:UpdateSummary( "invoices", "invnum", "amount", ;
                      "invitems", "invnum", "quantity * price - discount" )
 

This method internally generates the following SQL and executes:

Code: Select all

UPDATE `invoices` m
LEFT OUTER JOIN
(
   SELECT `invnum`, SUM( quantity * price - discount ) AS t01
   FROM `invitems`
   GROUP BY `invnum`
) t
ON m.invnum = t.invnum
SET 
    m.amount = IFNULL( t.t01, 0 )
 

This demonstrates the use of this method that can be used in similar circumstances, without writing complex SQL statements.

Full Syntax:

Code: Select all

   METHOD UpdateSummary( cMaster, cMasKey, acMasCols,   ;
                         cTrnTable, cTrnKey, acTrnCols, ;
                         cTrnWhere, cOperator )
 

5) Parent Child tables (in the original sample also):
When foreign key relationships are defined, creating master child tables is as easy as

Code: Select all

oRsInvoices:AddChild( "invitems" )
 

The child rowset can be referred to as oRsInvoices:oChild. The Rowset object uses the foreignkey relationships to relate the `invitems` table to the parent table.

[nageswaragunupudi]

SEQUENCES


Often we need to generate sequential ID/numbers for various documents and they should be unique. There can be sereral ways, but this is the one I personally advise.

This example accommodates upto 1-1/2 million numbers in each series and makes an ID with width of 6 characters.

This sample sample involves creation of a `sequences` table for 3 series 'IN', 'PO', 'VR' and a function to retrieve the next sequence value of any of these series. The sequence number is converted into 36-base number to reduced width requirements.

Anyone interested may run this code:

Code: Select all

   local cSql

TEXT INTO cSql
   CREATE TABLE sequences (
   series   VarChar(2) NOT NULL PRIMARY KEY,
   counter  INT UNSIGNED DEFAULT 0
   )
ENDTEXT

   oCn:Execute( cSql )
   oCn:Insert( "sequences", "series", { { 'IN' }, { 'PO' }, { 'VR' } } )

TEXT INTO cSql
   CREATE FUNCTION nextserial( cSeries VARCHAR(2) )
   RETURNS VARCHAR(6)
   BEGIN
      DECLARE retval VARCHAR(6);
      DECLARE nVal INT;

      UPDATE sequences SET counter = counter + 1 WHERE series = cSeries;
      SELECT counter INTO nVal FROM sequences WHERE series = cSeries;
      SET retval = CONCAT( UCASE( cSeries ), LPAD( CONV( nVal, 10, 36 ), 4, '0' ) );

      RETURN retval;
   END;
ENDTEXT

   oCn:Execute( cSql )
 

Once ready with this table and function, retrieving next sequence number is very easy:
Eg:
? oCn:NextSerial( 'IN' )
? oCn:NextSerial( 'VR' )

CAUTION:
Using this function inside a transaction may result in duplicates

[betoncu]

Mr. Rao,
In Maria15.prg please change the following line

Code: Select all

oRs   := oCn:RowSet( "SELECT ID,FIRST,CITY,SALARY FROM customer" )

to

Code: Select all

oRs   := oCn:RowSet( "SELECT ID, CONCAT_WS(' ', FIRST, LAST) AS CLIENT_NAME, CITY, SALARY FROM customer WHERE SALARY>50000" )

and try to add a client that has a salary less than 50,000.
The record is added. We can see it in the xbrowse, but we can not see the CLIENT_NAME field.

[nageswaragunupudi]

The record is added. We can see it in the xbrowse, but we can not see the CLIENT_NAME field.

Thanks.
Correct behavior should be that (1) the record should be added to the base table but (2) we should not see the record in the browse ( i.e. the record should not be included in the rowset)
Same is the behavior if we edit the salary to a value not covered by where condition.
We will fix this and inform you.