FWHMARIADB Samples

[luiz53]

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

Code: Select all | Expand

#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()   enddoRETURN 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. 

[nageswaragunupudi]

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.

[nageswaragunupudi]

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

#include "fivewin.ch"static oCn, oAdo, oServerstatic oRsFWH, oRsADO, oQrystatic nFwhSecs := 0, nAdoSecs := 0, nDlpSecs := 0static 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() - nSecsreturn 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, oBoldreturn 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()   ENDreturn nil//----------------------------------------------------------------------------//static function NetWorkTraffic( cCon )   local nMB   := 0   local cSql, oRsTEXT 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()   endifreturn 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).



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:



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:


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

[AHF]

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.

[nageswaragunupudi]

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.

[luiz53]

OK
thanks for the help

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

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.

[nageswaragunupudi]

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.

[carlos vargas]

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

[nageswaragunupudi]

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

      :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.

[nageswaragunupudi]

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

#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 CENTEREDreturn nilstatic 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 

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

[nageswaragunupudi]

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

   `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

   { "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

      { "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

#include "FiveWin.ch"REQUEST DBFCDXstatic oWndMain, oWndInvoices, oWndClients, oWndItemsstatic aBlankItemstatic 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" )   ENDMENUreturn 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()   endifreturn 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()   endifreturn 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()   endifreturn 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 oBrushreturn 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   endifreturn 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.   endifreturn 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()   endifreturn 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()   endifreturn 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()   endifreturn 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 )   ENDretur 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 ) )   endifreturn 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, oBoldreturn 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(); endifreturn 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   endifreturn 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 datareturn 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()   endifreturn nil//----------------------------------------------------------------------------//function UpdateTotals()   oCn:UpdateSummary( "invoices", "invnum", "amount", ;                      "invitems", "invnum", "quantity * price - discount" )return nil 

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

[Marc Venken]

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

[nageswaragunupudi]

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

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

oCn:ImportFromDBF( "c:\fwh\samples\customer.dbf" )

[betoncu]

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?

[nageswaragunupudi]

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.