FiveTech Software tech support forums

by **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.

by **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.

by **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).

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

by **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.

by **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.

by **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.

by **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.

by **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

by **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.

by **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

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

by **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

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

by **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

by **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" )

by **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?

by **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.

FiveTech Software tech support forums

FWHMARIADB Samples

Re: FWHMARIADB Samples

Re: FWHMARIADB Samples

Re: FWHMARIADB Samples

Re: FWHMARIADB Samples

Re: FWHMARIADB Samples

Re: FWHMARIADB Samples

Re: FWHMARIADB Samples

Re: FWHMARIADB Samples

Re: FWHMARIADB Samples

Re: FWHMARIADB Samples

Re: FWHMARIADB Samples

Re: FWHMARIADB Samples

Re: FWHMARIADB Samples

Re: FWHMARIADB Samples

Re: FWHMARIADB Samples

Who is online