FWHMARIADB Samples

Re: FWHMARIADB Samples

Postby luiz53 » Thu Apr 20, 2017 6:20 pm

I'm using it this way and it's working !!!!

Code: Select all  Expand view

#Include "Fivewin.ch"
#include "xbrowse.ch"
//-----------------------------------------------------------------------
//---- Classe DB_slbdaces
//----
//---- Luiz Antonio de Oliveira
//---- Data : 23/06/2016 Hora : 22:52:14
//-----------------------------------------------------------------------
*************************************************************************
CLASS DB_slbdaces FROM TDolphinQry
*************************************************************************

  Data cTableName INIT "slbdaces"
  Data cTpBanco   INIT "GRUPO"
  Data aStruct, aIdx

  Data cDbname
  Data oSetup

  Data oServer

  Data cQuery

  Data aColumns
  Data cWhere
  Data cOrder
  Data cLimit

  METHOD New()   CONSTRUCTOR
  METHOD Connect(aColumns,cWhere,cOrder,cLimit) CONSTRUCTOR
  METHOD End()

  METHOD NewCod()
  METHOD NewCodRun(oDlg,cCodigo)
  METHOD NewCodTest()

ENDCLASS

*************************************************************************
METHOD New() Class DB_slbdaces
*************************************************************************

   ::aStruct  := {}
   ::aIdx     := {} // I=INDEX KEY U=UNIQUE KEY P=PRIMARY KEY

   AADD( ::aIdx    , { "ID_ACES"       ,"P"})
   AADD( ::aIdx    , { "C01"           ,"I"})
   AADD( ::aIdx    , { "C02"           ,"I"})
   AADD( ::aIdx    , { "C03"           ,"I"})
//-------------------------------------------------------
   AADD( ::aStruct , { "ID_ACES"       ,"+",010,00})
   AADD( ::aStruct , { "C01"           ,"C",002,00})
   AADD( ::aStruct , { "C02"           ,"C",004,00})
   AADD( ::aStruct , { "C03"           ,"C",005,00})
   AADD( ::aStruct , { "C04"           ,"C",040,00})
   AADD( ::aStruct , { "C05"           ,"C",010,00})
   AADD( ::aStruct , { "CTDOR"         ,"N",006,00})
   AADD( ::aStruct , { "USUARIO"       ,"C",010,00})
   AADD( ::aStruct , { "DT_OPER"       ,"D",004,00})
   AADD( ::aStruct , { "HR_OPER"       ,"C",008,00})
   AADD( ::aStruct , { "DT_CADA"       ,"D",004,00})
   AADD( ::aStruct , { "GRAVADO"       ,"C",001,00})
   AADD( ::aStruct , { "ENVCARGA"      ,"L",001,00})
   AADD( ::aStruct , { "EXPORTA"       ,"C",008,00})
//-------------------------------------------------------
Return Self

*************************************************************************
METHOD End() Class DB_slbdaces
*************************************************************************

::super:end()
Return Nil

*************************************************************************
METHOD Connect(aColumns,cWhere,cOrder,cLimit) Class DB_slbdaces
*************************************************************************

default aColumns  := "*"

  ::oSetup   := Setup_Get()
  ::oServer  := ::oSetup:oServer
  ::aColumns := aColumns
  ::cWhere   := cWhere
  ::cOrder   := cOrder
  ::cLimit   := cLimit

  ::cDbname  := ::oSetup:VarBd:get(::cTableName)

   if valtype(::aColumns) == "C"
      ::aColumns := HB_ATokens(::aColumns, "," )
   ENDIF

   ::cQuery  := BuildQuery(::aColumns,{::cDbname}, ::cWhere,,,::cOrder,::cLimit)

return ::Super:New(::cQuery,::oServer)

*************************************************************************
METHOD NewCod()  Class DB_slbdaces
*************************************************************************
local cCodigo := ""

MsgRun("", PAD("Atenção",50),{|odlg|cCodigo := ::NewCodRun(oDlg) } )

return  cCodigo

*************************************************************************
METHOD NewCodRun(oDlg,cCodigo)  Class DB_slbdaces
*************************************************************************
   local aCodigo

   odlg:cmsg := 'Vericando Contador de Códigos !!!'
   odlg:REFRESH()
   SYSREFRESH()

   oDlg:REFRESH()
   GrupoVerificaContador("PRODUTO")

   do while .t.
      aCodigo := GrupoContadorAdd("PRODUTO",::oSetup:IpNum,SQLDateTime())

      if alltrim(aCodigo[2]) == alltrim(::oSetup:IpNum)

         cCodigo := strzero(aCodigo[1],5)
         if aCodigo[1] > 99999
            GrupoContadorNew("PRODUTO")
            odlg:cmsg := 'Contador de Codigo Será Iniciado  !!!'
            odlg:REFRESH()
            SYSREFRESH()
            syswait(1)
            loop
         endif

         if ::NewCodTest(cCodigo)
             exit
         endif
         odlg:cmsg := 'Codigo : '+cCodigo+' Já Existe Nova tentativa Será iniciada !!!'
         odlg:REFRESH()
         SYSREFRESH()
         loop
      endif
      odlg:cmsg := 'Codigo : '+cCodigo+' Pertence a Outro Usuário Nova tentativa Será iniciada !!!'
      odlg:REFRESH()
      SYSREFRESH()
   enddo

RETURN CCODIGO

*************************************************************************
METHOD NewCodTest(f_codigo)  Class DB_slbdaces
*************************************************************************
local oDb      := tConecta():new("select produto from _TABLE_ where produto = _FIELD_")
      oDb:DsBD(    "_TABLE_",::cTableName)
      oDb:DsADDVar("_FIELD_",f_codigo    )
      oDb:dsExecute()

      if oDb:lastrec() > 0
         oDb:end()
         return .f.
      endif
      oDb:end()

return .t.
 
luiz53
 
Posts: 43
Joined: Fri Jun 01, 2007 12:41 pm

Re: FWHMARIADB Samples

Postby nageswaragunupudi » Fri Apr 21, 2017 3:09 am

The two classes FWMariaConnection and FWMariaRowSet are not public classes. We can not directly instantiate them by calling New() method. Only way to create a new connection object is to call function maria_Connect() or to use command syntax. Only way to create a new rowset object is by calling oCn:RowSet( cSql ).
For the same reason we can not also INHERIT from these classes.

If you can please provide a working example using the above class to explain its functionality, we try to suggest alternative approaches to achieve the same functionality without inheriting from these classes. We request you to provide a sample which we can compile and build with Dolphin library.

Examples like : oCn:RowSet( BuildQuery( ....... ) )

If you still insist that the only way you like is by deriving from the classes, probably TDolphin or TMySql only are suitable for your programming style.
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10206
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: FWHMARIADB Samples

Postby nageswaragunupudi » Sat Apr 22, 2017 3:45 am

FWMariaDB -:- ADO -:- Dolphin/TMySql

Performance and other considerations:

When we consider developing a new MySql application, the first thing we may need to decide is whether to use.

1. ADO or
2. libmysql.dll/libmariadb.dll based library.
3. Multiuser Application on WAN/Cloud ?

1: Advantages of using ADO

ADO offers good and reliable performance and is a well-tested product. It is also highly scalable and suits large muti-user applications.

If the application is required to be adopted to different RDBMSs like MySql, MSSql, etc., it is desirable to choose ADO, because a major part of the code is portable.

1: Disadvantages of using ADO

It requires installation of ODBC driver on every client PC. When the installation is to be done on several PC’s and the PC’s are having different version of Windows operating system then things become further more difficult.

For eg. If you try to install MySQL ODBC driver on a Windows Xp PC, the installation may fail half way due to the missing Microsoft VC++ redistributable. You need to find out the right version of MS VC++ redistributable for your operating system. To summarize, it is an administration overhead when we have more number of PC’s.

2: Advantage of using DLL based libmysql.dll/libmariadb.dll based library

DLL based application is easier to deploy. You don’t need the ODBC driver installed on the client PC’s. You just need the DLL to be there on the same folder containing your application exe

2: Disadvantage of using DLL based libmysql.dll/libmariadb.dll based library

The application will be limited to MySql only forever.

3: Multi-user application on LAN/WAN/Cloud?

Effects of an ill-optimized program are not much visible in a single user environment on local host but hit the performance in multi-user environment when used on WAN/Cloud.

While the programmer is basically responsible for optimizing database design and optimizing every query for least cost (burden) on the server, the library also has to support.

Very important requirement is to place least burden on the server and the network traffic. ADO always is good for this purpose.

DLL based libraries:

TMySql is the first dll based library which enabled (x)Harbour programmers to work with MySql/MariaDB servers for the first time.

Later Dolphin came up with greatly enhanced features and also new features like embedded server and secured connectivity.

FWH libs are designed to produce high-performance applications in small to huge multi-user environments, supporting large corporate practices like server-side scripting and even providing some features that are not possible with ADO and others.

Test program:

This sample compares the three main choices, ADO, FWH libs and Dolphin in this regard.
maria14.prg
Code: Select all  Expand view

#include "fivewin.ch"

static oCn, oAdo, oServer
static oRsFWH, oRsADO, oQry
static nFwhSecs := 0, nAdoSecs := 0, nDlpSecs := 0
static aSay[ 3 ]
static lADO       := .t.
static lTraffic   := .t.

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

function Main()

   local cSql

   lADO     := MsgYesNo( "Do you have MySQL ODBC Installed?" )

   cSql     := "SELECT * FROM custbig WHERE id <= 51000"

   ConnectToServer()
   ReadData( cSql )
   BrowseData()
   DisconnectServer()

return nil

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

static function ConnectToServer()

   if lADO
      oAdo     := FW_DemoDB( "ADO" )
      lADO     := !( oAdo == nil )
   endif
   oServer  := FW_DemoDB( "DLP" )
   oCn      := FW_DemoDB()

return nil

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

static function DisconnectServer()

   if lADO
      oRsADO:Close()
   endif
   oQry:End()
   oRsFWH:Close()

   oServer:End()
   if lADO
      oAdo:Close()
   endif
   oCn:Close()

return nil

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

static function ReadData( cSql )

   local nSecs

   nSecs    := SECONDS()
   MsgRun( "Reading Data", "DLP", { || oQry   := oServer:Query( cSql ) } )
   nDlpSecs := SECONDS() - nSecs

   if lADO
      nSecs    := SECONDS()
      MsgRun( "Reading Data", "ADO", { || oRsADO := FW_OpenRecordSet( oAdo, cSql ) } )
      nAdoSecs := SECONDS() - nSecs
   endif

   nSecs    := SECONDS()
   MsgRun( "Reading Data", "FWH", { || oRsFWH := oCn:RowSet( cSql ) } )
   nFWHSecs := SECONDS() - nSecs

return nil

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

static function BrowseData()

   local oDlg, oFont, oBold, aBrw[ 3 ]

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

   DEFINE DIALOG oDlg SIZE 950,800 PIXEL TRUEPIXEL FONT oFont ;
      TITLE "Peformance Comparison : ADO - FWH - DLP"

   if lADO
   @  10,20 SAY "ADO ReadTime " + Str( nAdoSecs, 6, 3 ) SIZE 150,20 PIXEL OF oDlg FONT oBold

   @  30,00 XBROWSE aBrw[ 1 ] SIZE 0,220 PIXEL OF oDlg DATASOURCE oRsADO AUTOCOLS ;
      CELL LINES NOBORDER FOOTERS AUTOSORT

   @ 010,170 SAY "Save Time " + Str( aBrw[ 1 ]:nSaveSecs, 6, 3 ) SIZE 150,20 PIXEL OF oDlg UPDATE FONT oBold
   @ 010,320 SAY "Sort Time " + Str( aBrw[ 1 ]:nSortSecs, 6, 3 ) SIZE 150,20 PIXEL OF oDlg UPDATE FONT oBold
   @ 010,480 SAY aSay[ 1 ] PROMPT "Load On Server/NetWork = " + TRANSFORM( NetWorkTraffic( "ADO" ), "9999.999 MegaBytes" ) ;
      SIZE 300,20 PIXEL OF oDlg COLOR CLR_HRED,oDlg:nClrPane FONT oBold
   @ 010,790 SAY aBrw[ 1 ]:oSeek PROMPT aBrw[ 1 ]:cSeek SIZE 140,20 PIXEL OF oDlg COLOR CLR_BLACK,CLR_YELLOW

   BrwSetup( aBrw[ 1 ] )
   WITH OBJECT aBrw[ 1 ]
      :bChange    := { |o| oRsADO:ReSync( 1, 2 ), o:RefreshCurrent() }
      :bGotFocus  := { || oRsADO:ReSync( 1, 2 ), aBrw[ 1 ]:RefreshCurrent() }
      :bOnRefresh       := { || oDlg:Update(), If( lTraffic, aSay[ 1 ]:Refresh(), ) }
      :bOnChanges       := { || oDlg:Update(), If( lTraffic, aSay[ 1 ]:Refresh(), ) }
   END
   endif

   @ 250,20 SAY "FWH ReadTime " + Str( nFWHSecs, 6, 3 ) SIZE 150,20 PIXEL OF oDlg FONT oBold

   @ 270,00 XBROWSE aBrw[ 2 ] SIZE 0,220 PIXEL OF oDlg DATASOURCE oRsFWH AUTOCOLS ;
      CELL LINES NOBORDER FOOTERS AUTOSORT

   @ 250,170 SAY "Save Time " + Str( aBrw[ 2 ]:nSaveSecs, 6, 3 ) SIZE 150,20 PIXEL OF oDlg UPDATE FONT oBold
   @ 250,320 SAY "Sort Time " + Str( aBrw[ 2 ]:nSortSecs, 6, 3 ) SIZE 150,20 PIXEL OF oDlg UPDATE FONT oBold
   @ 250,480 SAY aSay[ 2 ] PROMPT "Load On Server/NetWork = " + TRANSFORM( NetWorkTraffic( "FWH" ), "9999.999 MegaBytes" ) ;
      SIZE 300,20 PIXEL OF oDlg COLOR CLR_HRED,oDlg:nClrPane FONT oBold
   @ 250,790 SAY aBrw[ 2 ]:oSeek PROMPT aBrw[ 2 ]:cSeek SIZE 140,20 PIXEL OF oDlg COLOR CLR_BLACK,CLR_YELLOW

   BrwSetup( aBrw[ 2 ] )
   WITH OBJECT aBrw[ 2 ]
      :bChange    := { |o| oRsFWH:ReSync(), o:RefreshCurrent() }
      :bGotFocus  := { || oRsFWH:ReSync(), aBrw[ 2 ]:RefreshCurrent() }
      :bOnRefresh       := { || oDlg:Update(), If( lTraffic, aSay[ 2 ]:Refresh(), ) }
      :bOnChanges       := { || oDlg:Update(), If( lTraffic, aSay[ 2 ]:Refresh(), ) }
   END

   @ 490,20 SAY "DLP ReadTime " + Str( nDLPSecs, 6, 3 ) SIZE 150,20 PIXEL OF oDlg FONT oBold

   @ 510,00 XBROWSE aBrw[ 3 ] SIZE 0,-40 PIXEL OF oDlg DATASOURCE oQry AUTOCOLS ;
      CELL LINES NOBORDER FOOTERS AUTOSORT

   @ 490,170 SAY "Save Time " + Str( aBrw[ 3 ]:nSaveSecs, 6, 3 ) SIZE 150,20 PIXEL OF oDlg UPDATE FONT oBold
   @ 490,320 SAY "Sort Time " + Str( aBrw[ 3 ]:nSortSecs, 6, 3 ) SIZE 150,20 PIXEL OF oDlg UPDATE FONT oBold
   @ 490,480 SAY aSay[ 3 ] PROMPT "Load On Server/NetWork = " + TRANSFORM( NetWorkTraffic( "DLP" ), "9999.999 MegaBytes" ) ;
      SIZE 300,20 PIXEL OF oDlg COLOR CLR_HRED,oDlg:nClrPane FONT oBold
   @ 490,790 SAY aBrw[ 3 ]:oSeek PROMPT aBrw[ 3 ]:cSeek SIZE 140,20 PIXEL OF oDlg COLOR CLR_BLACK,CLR_YELLOW

   BrwSetup( aBrw[ 3 ] )

   WITH OBJECT aBrw[ 3 ]
      :bOnRefresh       := { || oDlg:Update(), If( lTraffic, aSay[ 3 ]:Refresh(), ) }
      :bOnChanges       := { || oDlg:Update(), If( lTraffic, aSay[ 3 ]:Refresh(), ) }
   END

   @ 770,200 CHECKBOX lTraffic PROMPT "Show NetWorkTraffic/ServerLoad" SIZE 400,20 PIXEL OF oDlg FONT oBold ;
      COLOR CLR_HRED, oDlg:nClrPane ON CHANGE If( lTraffic, AEval( aSay, { |o| o:Refresh() } ), nil )

   ACTIVATE DIALOG oDlg CENTERED
   RELEASE FONT oFont, oBold

return nil

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

static function BrwSetup( oBrw )

   WITH OBJECT oBrw
      :SetChecks()
      :nEditTypes    := EDIT_GET

      :bRecSelHeader    := { || "RecID" }
      :bRecSelData      := { |o| Int( o:BookMark ) }
      :bRecSelFooter    := { |o| o:nLen }
      :nRecSelWidth     := "99999999"

      :bClrEdits        := { || { CLR_BLACK, CLR_YELLOW } }

      :lIncrFilter      := .t.
      :lSeekWild        := .t.
      :cFilterFld       := "street"

      :CreateFromCode()
   END

return nil

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

static function NetWorkTraffic( cCon )

   local nMB   := 0
   local cSql, oRs

TEXT INTO cSql
   SELECT * FROM information_schema.session_status
   WHERE variable_name = 'Bytes_sent'
ENDTEXT

   if cCon == "DLP"
      oRs   := oServer:Query( cSql )
   elseif cCon == "ADO"
      oRs   := FW_OpenRecordSet( oAdo, cSql )
   else
      oRs   := oCn:RowSet( cSql )
   endif

   if cCon == "ADO"
      oRs:MoveFirst()
      nMB   := Val( oRs:Fields( 1 ):Value )
   else
      oRs:GoTop()
      nMB      := Val( oRs:FieldGet( 2 ) )
   endif
   nMB /= 1000000.0
   if cCon == "DLP"
      oRs:End()
   else
      oRs:Close()
   endif

return nMB

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

EXTERNAL TDOLPHINSRV
 


This application connects to the same server using all the 3 libs, i.e., ADO, FWH and Dolphin, reads a table with about 50,000 rows and places them in three browses. (Dolpin is used to represent both dolphin and tmysql).

Image

This is the screen-shot immediately after connection and display of the browses.

The three browses also display time taken to read the table, save modifications, sorting and also the network traffic/load on server by the operations.

NetWork Traffic/Server Load: This is the volume of data the server generates and then the network carries from server to the client.

Connection and read Times:
Dolphin connects to the server faster than ADO and FWH.
Dolphin loads data from server faster than ADO and FWH ( 3.34 seconds to 4.4 seconds)

Note: FWH RecSet class loads faster but has less features than RowSet class.

Load on Server/Network Traffic: 6 MB in all cases, representing the volume of data served by the server.

Sorting Times and load on Server/network
Now let us try clicking on headers of the 3 browses to test sorting speeds. ADO sorts fastest. While both ADO and FWH sorts in well less than one second, Dolphin takes 3 seconds, i.e., almost as much time as taken for reading the entire recordset. The reason is that while ADO and FWH sort the data in the memory of the client, Dolphin reads the entire data again from the server each time. Both Dolphin and TMySql have the same behavior. This approach increases load on network and the server.

Each time the user sorts a column, Dolphin not only takes maximum time, but also places additional burden on the server and network by 6 MB each time.

Note: FWH is working on improving sorting speeds to match ADO.

This is the screen-shot after sorting one column in all 3 browses:

Image

Editing and Saving times and Load on Server/Network
Let us now try edit and modify 10 cells in each of the 3 browses. In the case of ADO and FWH, time taken to save each modification is far less than one second. Total burden on network and server is only a few KBs.
In case of Dolphin, each modification took around the same time as reading the entire table and genertes additional network/server load of 6 MB each time. For the 10 modifications 60 MB.

This is the screen-shot after editing 10 cells in each of the browses:
Image

Incremental Filters:
All the 3 browses are configured for incremental filtering on the field "street". Both ADO and FWH do the filtering process in client's memory and fast. For this reason there is no burden on the network/server.

In case of Dolphin, every keystroke including backspace, generates a new query with where clause, resulting in 3 seconds delay and additional burden of 6 MB for every keystroke.

Effects in a multi-user environment:
Imagining a multiuser environment of say 100 users using the application, each user on average modifying 100 rows, sorting 20 times, incremental seeeks with 50 keystrokes.

ADO and FWH place an addition burden on the server/network traffic by around 500 KB only and that too only for saving data and retrieving modified row data.

Dolphin on the other hand places an additional burden of 102 GigaBytes on Server and Network each for the same work, as against 500KB only by ADO/FWH.

100 * ( 100 + 20 + 50 ) * 6 = 102,000 MB = 102 GB.

Download link: http://anserkk.com/gnraomysql/view.php?id=16
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10206
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: FWHMARIADB Samples

Postby AHF » Sat Apr 22, 2017 4:48 pm

Dear Rao,

Thanks for the excellent designed trials and information report.
This is crucial information to all of us.

My experience is that with ADO opening a recordset with 500 columns / 2000 rows its slower than opening it with 100 columns / 100.000 rows.
May be with other options FWH and DLP is different.
Regards
Antonio H Ferreira
AHF
 
Posts: 838
Joined: Fri Feb 10, 2006 12:14 pm

Re: FWHMARIADB Samples

Postby nageswaragunupudi » Sat Apr 22, 2017 5:03 pm

My experience is that with ADO opening a recordset with 500 columns / 2000 rows its slower than opening it with 100 columns / 100.000 rows.

Thanks for sharing your experience. This is how we all learn from each other's experiences.
I can not say about others till I test with such tables.
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10206
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: FWHMARIADB Samples

Postby luiz53 » Mon Apr 24, 2017 12:07 pm

OK
thanks for the help

Just wanted to use by default my current system where each table has a class


nageswaragunupudi wrote:If you can please provide a working example using the above class to explain its functionality, we try to suggest alternative approaches to achieve the same functionality without inheriting from these classes. We request you to provide a sample which we can compile and build with Dolphin library.

Examples like : oCn:RowSet( BuildQuery( ....... ) )

If you still insist that the only way you like is by deriving from the classes, probably TDolphin or TMySql only are suitable for your programming style.
luiz53
 
Posts: 43
Joined: Fri Jun 01, 2007 12:41 pm

Re: FWHMARIADB Samples

Postby nageswaragunupudi » Wed Apr 26, 2017 11:58 am

Mr Luiz

I better explain more. Once a RowSet object is created, we can not change the columns and source (table or tables) of the rowset during the life of the object. Everything else like where clauses, order by, limits etc can be changed.
For this reason, even if inheritence is enabled, rowset class does not support the usage you have in mind for your derived class.
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10206
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: FWHMARIADB Samples

Postby carlos vargas » Wed Apr 26, 2017 6:11 pm

Rao, why move the cursor the browse of ADO and FWH is slow, and in Dolphin is normal?
is how if exist a process in each move.

salu2
carlos vargas
Salu2
Carlos Vargas
Desde Managua, Nicaragua (CA)
User avatar
carlos vargas
 
Posts: 1680
Joined: Tue Oct 11, 2005 5:01 pm
Location: Nicaragua

Re: FWHMARIADB Samples

Postby nageswaragunupudi » Thu Apr 27, 2017 2:51 am

carlos vargas wrote:Rao, why move the cursor the browse of ADO and FWH is slow, and in Dolphin is normal?
is how if exist a process in each move.

salu2
carlos vargas

Because we assigned
Code: Select all  Expand view

      :bChange    := { |o| oRsFWH:ReSync(), o:RefreshCurrent() }
 

to ADO and FWH browses. Every change in row involves an additional work of reading the data of the current row from the server. This has the advantage of displaying the changes made externally when we navigate to a row.

For Dolphin, it is not possible to refresh only current record, unless we re-load entire table again. So this is not provided in browse of dolphin. As a result dolphin browse can not display modifications by other browses.

Without this bChange assignment, ADO and FWH browse navigation would be faster.
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10206
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: FWHMARIADB Samples

Postby nageswaragunupudi » Thu Apr 27, 2017 1:30 pm

RowSet Method EditBaseRecord

When a table (or joined tables) contain many fields, programmers often consider it expedient to read and browse only limited number of columns. But for the purpose of editing/appending all or more fields need to be edited in the dialog. There may be more than one dialog and these dialogs can be quite complex.

Rowset provides EditBaseRecord() method to handle such situations. With this method we can edit all or selected fields from the base table, though they are not included in the recordset. When saved, the edit / append automatically carried to the RowSet automatically.

Syntax:
oRs:EditBaseRecord( [cFieldsList], // defaults to all fields
[lAppend], // defaults to edit current record
[bEditDialog], // Programmer's own dialog. Optional
[oBrw] ) // In case the rowset is being browsed.
bEditDilog: { |oRec| MyEditDialog( oRec ) }

The following is a simple demonstration of this method. The principle can be extended to multiple dialogs and more complex cases.

maria15.prg

Code: Select all  Expand view

#include "fivewin.ch"
#include "dbcombo.ch"

static aStates

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

function Main()

   local oCn, oRs
   local oDlg, oBrw

   FWNumFormat( "A", .t. )

   oCn   := FW_DemoDB()

   aStates  := oCn:Execute( "SELECT CODE,NAME FROM states" )
   oRs   := oCn:RowSet( "SELECT ID,FIRST,CITY,SALARY FROM customer" )

   DEFINE DIALOG oDlg SIZE 500,600 PIXEL TRUEPIXEL ;
      TITLE "EditBaseRecord()"

   @ 70,20 XBROWSE oBrw SIZE -20,-20 PIXEL OF oDlg ;
      DATASOURCE oRs AUTOCOLS ;
      COLSIZES -4, -20, -20 ;
      CELL LINES NOBORDER FOOTERS

   WITH OBJECT oBrw
      :Salary:nFooterType  := AGGR_SUM
      :MakeTotals()
      :CreateFromCode()
   END

   @ 20, 20 BTNBMP PROMPT "ADD"  SIZE 100,30 PIXEL FLAT OF oDlg ;
      ACTION oRs:EditBaseRecord( nil, .t., { |oRec| MyEditDlg( oRec ) }, oBrw )

   @ 20,130 BTNBMP PROMPT "EDIT" SIZE 100,30 PIXEL FLAT OF oDlg ;
      ACTION oRs:EditBaseRecord( nil, .f., { |oRec| MyEditDlg( oRec ) }, oBrw )

   ACTIVATE DIALOG oDlg CENTERED

return nil

static function MyEditDlg( oRec )

   local lNew     := ( oRec:RecNo == 0 )
   local oDlg, oFont

   DEFINE FONT oFont NAME "TAHOMA" SIZE 0,-15
   DEFINE DIALOG oDlg SIZE 400,470 PIXEL TRUEPIXEL FONT oFont ;
      TITLE If( lNew, "ADD NEW ", "EDIT" ) + " RECORD"

   @ 030,020 SAY "ID :"          SIZE 80,20 PIXEL OF oDlg RIGHT
   @ 060,020 SAY "First"         SIZE 80,20 PIXEL OF oDlg RIGHT
   @ 090,020 SAY "Last"          SIZE 80,20 PIXEL OF oDlg RIGHT
   @ 120,020 SAY "Street"        SIZE 80,20 PIXEL OF oDlg RIGHT
   @ 150,020 SAY "City"          SIZE 80,20 PIXEL OF oDlg RIGHT
   @ 180,020 SAY "State"         SIZE 80,20 PIXEL OF oDlg RIGHT
   @ 210,020 SAY "Zip"           SIZE 80,20 PIXEL OF oDlg RIGHT
   @ 240,020 SAY "HireDate"      SIZE 80,20 PIXEL OF oDlg RIGHT
   @ 270,020 SAY "Married"       SIZE 80,20 PIXEL OF oDlg RIGHT
   @ 300,020 SAY "Age"           SIZE 80,20 PIXEL OF oDlg RIGHT
   @ 330,020 SAY "Salary"        SIZE 80,20 PIXEL OF oDlg RIGHT
   @ 360,020 SAY "Notes"         SIZE 80,20 PIXEL OF oDlg RIGHT

   @ 030,120 GET oRec:ID         SIZE 100,22 PIXEL OF oDlg READONLY RIGHT
   @ 060,120 GET oRec:First      SIZE 240,22 PIXEL OF oDlg VALID !Empty( oRec:First )
   @ 090,120 GET oRec:Last       SIZE 240,22 PIXEL OF oDlg VALID !Empty( oRec:Last )
   @ 120,120 GET oRec:Street     SIZE 240,22 PIXEL OF oDlg VALID !Empty( oRec:Street )
   @ 150,120 GET oRec:City       SIZE 240,22 PIXEL OF oDlg VALID !Empty( oRec:City )
   @ 180,120 DBCOMBO oRec:State  SIZE 240,300 PIXEL OF oDlg ;
      ALIAS aStates ITEMFIELD "1" LISTFIELD "2"
   @ 210,120 GET oRec:Zip        SIZE 240,22 PIXEL OF oDlg VALID !Empty( oRec:Zip )
   @ 240,120 GET oRec:HireDate   SIZE 240,22 PIXEL OF oDlg VALID !Empty( oRec:HireDate )
   @ 270,120 CHECKBOX oRec:Married PROMPT "" SIZE 22,22 PIXEL OF oDlg
   @ 300,120 GET oRec:Age        SIZE 240,22 PIXEL OF oDlg PICTURE "99" RIGHT VALID ( oRec:Age >= 20 )
   @ 330,120 GET oRec:Salary     SIZE 240,22 PIXEL OF oDlg PICTURE "999,999.99" RIGHT VALID ( oRec:Salary > 0 )
   @ 360,120 GET oRec:Notes      SIZE 240,22 PIXEL OF oDlg

   @ 420,020 BTNBMP PROMPT "Save"   SIZE 150,30 PIXEL FLAT OF oDlg ;
      ACTION ( If( oRec:Modified(), oRec:Save(), nil ), oDlg:End() )
   @ 420,240 BTNBMP PROMPT "Cancel" SIZE 150,30 PIXEL FLAT OF oDlg ACTION oDlg:End()

   ACTIVATE DIALOG oDlg CENTERED ;
      ON PAINT oDlg:Box( 15, 10, 395, 390 )

return nil
 


Image

Download Link: http://anserkk.com/gnraomysql/view.php?id=17
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10206
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: FWHMARIADB Samples

Postby nageswaragunupudi » Wed May 03, 2017 2:29 pm

A Sample Invoicing application

Adapted from \fwh\samples\yunus.prg

An approach that can be adopted for quotations, purchase orders, etc.

mariainv.prg

Points of interest:
1) Implementation of Transactions.
2) Database integrity at server level through
a) Foreign key relationships
b) Unique constraints
c) column validations
d) calculation of dependent columns
e) updation of related tables
f) ENUM type of column
3) Triggers and stored procedures

Lack of support for CHECK constraint in MySql and MariaDB (latest stable version).

Other databases support CHECK constraint like this:
Code: Select all  Expand view

   `price` decimal( 6, 2 ) CHECK( `price` > 0 ),
 

This constraint rejects any updation of insertion with value of price <= 0. Sadly both MySql and present stable release of MariaDB do not support this constraint.
For this reason, we had to implement such constraints through Triggers. If this constraint is supported, we do not need to write all these Triggers. It is understood that latest builds of MariaDB support this constraint.

Foreign key relationships:

These constraints eliminate the possibility of buggy front-ends writing invalid data. Please see the difference in definition of these constraints. The relationship of INVNUM in invitems and invoices is defined in such a way that if an invoie is deleted all the related entries in the child table also are deleted automatically, whereas in case of clients and items any attempt to delete an entry which is already used by invoices,invitems tables is rejected.

Calculated columns:

Calculation of dependent columns is implemented at server level through Triggers. MySql 5.7 and present stable release of MariaDB implement Calculated Columns. FWHMARIADB library supports calculated columns when supported by the server.

When creating table using oCn:CreateTable, the column may be defined as:
Code: Select all  Expand view

   { "TAX", "=AMOUNT * TAXRATE / 100" }
 

In this sample we did not adopt this approach. Instead we implemented calculations inside Trigger so that the logic can work even on earier versions of MYSQL servers.

Column type ENUM (implemented by MySql):

Please see the column definition
Code: Select all  Expand view

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

This column allows only one of the values in the list.
FWMYSQL not only supports this column type, but also uses the information to automatically build EDIT_LIST_BOX in xbrowse and datarow without any effort by the programmer.
Please try editing "Items"

Code: Select all  Expand view
#include "FiveWin.ch"

REQUEST DBFCDX

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

function Main()

   local oBrush, oFont

   oCn      := FW_DemoDB( 1 )
   oCn:lShowErrors   := .t.

   // to re-create all tables again
   // DropAllTables()

   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 )

      oWndClients:bPrint := { || oBrw:Report( "Clients report",, .F.) }

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

      oBrw:CreateFromCode()
      oBrw:SetFocus()
      oBrw:bLDblClick = { || oBrw:EditSource(,, .T.) }

      oWndClients:oClient = oBrw
      oWndClients:oControl = oBrw

      DEFINE MSGBAR oMsgBar OF oWndClients 2007

      ACTIVATE WINDOW oWndClients MAXIMIZED ;
         VALID ( oWndClients := nil, .T. )
   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 )

      oWndItems:bPrint := { || oBrw:Report( "Items report",, .F.) }

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

      oBrw:CreateFromCode()
      oBrw:SetFocus()
      oBrw:bLDblClick = { || oBrw:EditSource(,, .T.) }

      oWndItems:oClient = oBrw
      oWndItems:oControl = oBrw

      DEFINE MSGBAR oMsgBar OF oWndItems 2007

      ACTIVATE WINDOW oWndItems MAXIMIZED ;
         VALID ( oWndItems := nil, .T. )
   else
      oWndItems:SetFocus()
   endif

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(,, .T.) }
      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
      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 )

   if lInvBrowse == .t.
      DEFINE BUTTON OF oBar PROMPT "Update" ;
         ACTION ( UpdateTotals(), oBrw:Refresh(), oBrw:SetFocus() )
   endif

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

   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,20,100 ) ) ;
      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, COMMENT 'CASE:UPPER'" }, ;
      { "FIRST",     'C', 20, 0, "NOT NULL, COMMENT 'CASE:PROPER'" }, ;
      { "LAST",      'C', 20, 0, "COMMENT 'CASE:PROPER'" }, ;
      { "ADDRESS1",  'C', 30, 0, "NOT NULL, COMMENT 'CASE:PROPER'" }, ;
      { "ADDRESS2",  'C', 30, 0, "COMMENT 'CASE:PROPER'" }, ;
      { "CITY",      'C', 20, 0, "NOT NULL, 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" )

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 )

   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, COMMENT 'CASE:UPPER'" }, ;
      { "NAME",     "C", 30, 0, "utf8, DEFAULT 'Name', 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" } }

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

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

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 )

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

return nil

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

static function CreateInvoices()

   local aCols, cSql

   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 }  }

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

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

   oCn:Execute( "DROP PROCEDURE IF EXISTS invoices_check" )
   oCn:Execute( cSql )

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 )

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

   oCn:Execute( "UPDATE invoices SET amount = 0" )


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

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

function UpdateTotals()

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


Image

Download Link:
http://anserkk.com/gnraomysql/view.php?id=18
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10206
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: FWHMARIADB Samples

Postby Marc Venken » Thu May 04, 2017 10:41 pm

Hello,

I'm studying the sample, but don't untherstand this :

oWndClients:oClient = oBrw
oWndClients:oControl = oBrw

What does it do/mean ?

I believe I have seen a sample where data from a dbf is uploaded(imported) into a online database with 1 function.
I even think that it created a online database from the dbf. Have you seen it ?

I have a small program that I want to port to online, but I need to get my data into the online database. The online database will be made like in the sample,
so the structures will be the same.

Marc
Marc Venken
Using: FWH 23.04 with Harbour
User avatar
Marc Venken
 
Posts: 1338
Joined: Tue Jun 14, 2016 7:51 am
Location: Belgium

Re: FWHMARIADB Samples

Postby nageswaragunupudi » Thu May 04, 2017 11:39 pm

I believe I have seen a sample where data from a dbf is uploaded(imported) into a online database with 1 function.
I even think that it created a online database from the dbf. Have you seen it ?


This is the method
Code: Select all  Expand view
oCn:ImportFromDBF( <dbfname>, [ <tablename_ifdifferent>] )


This opens the dbf file, creates a table with the same structure and copies the data. By default, it creates the table with the same name as the dbf. If we want a different table name, we can give that as the second paramter.

Example:
Code: Select all  Expand view
oCn:ImportFromDBF( "c:\fwh\samples\customer.dbf" )
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10206
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: FWHMARIADB Samples

Postby betoncu » Fri May 05, 2017 2:10 am

Dear Rao,
I have studied your sample and I have some problems that I have to ask you.
While I am saving, if there is an error I am loosing everything that I have typed.

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.

After showing the error Is it possible to give a chance to the user just only to change
the duplicate field and try to save it again?
Birol Betoncu
birol.betoncu@gmail.com
Using Harbour, FWH 19.05, BCC7
User avatar
betoncu
 
Posts: 126
Joined: Sat Oct 08, 2005 9:38 pm
Location: Cyprus (North)

Re: FWHMARIADB Samples

Postby nageswaragunupudi » Fri May 05, 2017 2:35 am

After showing the error Is it possible to give a chance to the user just only to change
the duplicate field and try to save it again?

This can also be provided. We shall revise the sample soon.
The main point that is demonstrated is that even if the front end does not do validation, the validation at the server level does not permit entry of invalid data. This is more secure than relying on validations in the front ends only.
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10206
Joined: Sun Nov 19, 2006 5:22 am
Location: India

PreviousNext

Return to FiveWin for Harbour/xHarbour

Who is online

Users browsing this forum: nageswaragunupudi and 7 guests