Exemplo com FiveWin :
#include "fivewin.ch"
static oCn
function Main()
local oRs
SET DATE BRITISH
SET CENTURY ON
FW_SetUnicode( .T. )
oCn := FW_DemoDB( 1 )
CheckTable()
oRs := oCn:testunicode
oRs:Fields( "username" ):lReadOnly := .t.
oRs:lAutoAppend := .t.
XBROWSER oRs FASTEDIT TITLE "Unicode Text" SETUP BrwSetup( oBrw )
return nil
static function BrwSetup( oBrw )
local oDlg, oRs
oDlg := oBrw:oWnd
oRs := oBrw:oDbf
oDlg:bStart := { || oDlg:nHeight := 600, oDlg:Center() }
oBrw:lCanPaste := .t.
oBrw:bChange := { || oRs:ReSync(), oBrw:RefreshCurrent() }
oBrw:bGotFocus := { || If( oRs:Refresh() > 0, oBrw:Refresh(), nil ) }
return nil
static function CheckTable()
local cSql
if oCn:TableExists( "testunicode" )
return nil
endif
oCn:CreateTable( "testunicode", { ;
{ "language", 'C', 15, 0, "latin1 comment 'case:upper'" }, ;
{ "unicodetext", 'C', 40, 0, "utf8" }, ;
{ "entrymode", 'C', 20, 0, "latin1 comment 'case:proper'" }, ;
{ "username", 'C', 30, 0, "utf8" }, ;
{ "writedt", '=', 8, 0 } }, nil, "utf8" )
TEXT INTO cSql
CREATE TRIGGER testunicode_bi BEFORE INSERT ON testunicode
FOR EACH ROW
BEGIN
SET NEW.username = SUBSTRING( CONCAT_WS( ', ', @os_user, @pc_name ), 1, 30 );
END
ENDTEXT
? oCn:Execute( "DROP TRIGGER IF EXISTS testunicode_bi" )
? oCn:Execute( cSql )
TEXT INTO cSql
CREATE TRIGGER testunicode_bu BEFORE UPDATE ON testunicode
FOR EACH ROW
BEGIN
SET NEW.username = SUBSTRING( CONCAT_WS( ', ', @os_user, @pc_name ), 1, 30 );
END
ENDTEXT
? oCn:Execute( "DROP TRIGGER IF EXISTS testunicode_bu" )
? oCn:Execute( cSql )
return nil
oCn:AddColumn( cTable, aColSpec )
oCn:AlterColumn( cTable, aColSpec )
oCn:RenameColumn( cTable, cOldName, cNewName )
oCn:AddAutoInc( cTable, cCol )
oCn:MakePrimaryKey( cTable, cCol )
SELECT fields, amount, ( @ntotal = @ntotal + amount ) AS running_total
FROM mytable, ( SELECT @ntotal := 0 ) AS t
WHERE <clauses>
ORDER BY <clauses>
nTotal := 0
aData := FW_DbfToArray("AMOUNT,(nTotal := nTotal + AMOUNT)" )
#include "fivewin.ch"
function Main()
local oCn := FW_DemoDB()
local oRs, cSql
local oDlg, oFont, oBrw
oCn:lShowErrors := .t.
TEXT INTO cSql
SELECT id, ncli, fecha, descripcion, numero, tipo, importe,
( @bal := IF( tipo = '1', @bal + importe, @bal - importe ) ) AS nsaldo
FROM ctacte,
( SELECT @bal := 0 ) AS t
WHERE ncli = ?
ORDER BY fecha
ENDTEXT
oRs := oCn:RowSet( cSql, { 101 } )
oRs:GoBottom()
DEFINE FONT oFont NAME "TAHOMA" SIZE 0,-14
DEFINE DIALOG oDlg SIZE 800,700 PIXEL FONT oFont ;
TITLE "Running Totals"
@ 50,20 XBROWSE oBrw SIZE -20,-20 PIXEL OF oDlg ;
DATASOURCE oRs ;
COLUMNS "id", "Fecha", "Descripcion", "Numero", ;
"If( tipo == '1', importe, 0 )", ;
"If( tipo == '1', 0, importe )", ;
"nsaldo" ;
HEADERS "DocID", nil, nil, nil, "DEBE", "PAGO", "SALDO" ;
PICTURES "999", nil, nil, nil, "@EZ 999,999,999.99", "@EZ 999,999,999.99", "@EZ 999,999,999.99" ;
COLSIZES 50,100,100,100,100,100,110 ;
CELL LINES NOBORDER FOOTERS FASTEDIT
WITH OBJECT oBrw
AEval( :aCols, { |o| o:nEditType := EDIT_GET }, 3, 4 )
WITH OBJECT :Debe
:nFooterType := AGGR_SUM
:bEditValid := { |oGet| oGet:VarGet() > 0 }
:bOnPostEdit := { |o,x,n| If( o == VK_ESCAPE .or. x < 0, nil, ( oRs:tipo := '1', oRs:importe := x ) ) }
:bOnChange := { || oRs:Requery(), oBrw:Refresh() }
END
WITH OBJECT :Pago
:nFooterType := AGGR_SUM
:bEditValid := { |oGet| oGet:VarGet() > 0 }
:bOnPostEdit := { |o,x,n| If( o == VK_ESCAPE .or. x < 0, nil, ( oRs:tipo := '0', oRs:importe := x ) ) }
:bOnChange := { || oRs:Requery(), oBrw:Refresh() }
END
:Saldo:bFooter := { || oBrw:Debe:nTotal - oBrw:Pago:nTotal }
:MakeTotals()
:CreateFromCode()
END
@ 20,20 BTNBMP PROMPT "Delete" SIZE 60,20 PIXEL FLAT OF oDlg ACTION ;
( oRs:Delete(), oRs:ReQuery(), oBrw:MakeTotals(), oBrw:Refresh(), oBrw:SetFocus() )
ACTIVATE DIALOG oDlg CENTERED
oCn:Close()
return nil
http://anserkk.com/gnraomysql/view.php?id=131)
SELECT ID AS StateID, CODE AS StateCode, NAME AS StateName FROM states
2)
SELECT ID, CODE AS StateCode, NAME AS StateName FROM states
3)
SELECT C.ID, C.FIRST, C.CITY, S.NAME AS STATENAME FROM customer C
LEFT JOIN states S ON C.STATE = S.CODE
http://anserkk.com/gnraomysql/view.php?id=17Pontos de interesse:
1) Implementação de transações.
2) Integridade do banco de dados no nÃvel do servidor através de
a) Relações de chave estrangeira
b) restrições exclusivas
c) validações de coluna
d) cálculo de colunas dependentes
e) atualização de tabelas relacionadas
f) tipo ENUM da coluna
3) Disparadores e procedimentos armazenados
#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
http://anserkk.com/gnraomysql/view.php?id=18Isso abre o arquivo dbf, cria uma tabela com a mesma estrutura e copia os dados. Por padrão, ele cria a tabela com o mesmo nome que o dbf. Se queremos um nome de tabela diferente, podemos dar isso como o segundo parâmetro.
oCn:ImportFromDBF( "c:\f[code][/code]wh\samples\customer.dbf" )
Function edit_online(oRec,cPloeg)
oRec:ploeg2017 = cPloeg
oRec:Save()
return NIL
function setploeg(oBrw,cPloeg)
local oRs := oBrw:oDbf
local nSaveRec := oRs:RecNo()
if msgYesNo("Change selected members")
FOR I = 1 TO LEN(oBRW:aSELECTED) // obrw:aSELECTED is an array containing recnos marked
oRs:GoTo(oBRW:aSELECTED[I]))
oRs:ploeg2017 = cPloeg
oRs:Save()
next
endif
oRs:GoTo( nSaveRec )
return NIL
http://anserkk.com/gnraomysql/view.php?id=18FWCONNECT oCn HOST cHost USER cUser PASSWORD cPwd [DB cdb] [PORT nPort] [FLAGS nFlags]
or oCn := mysql_Connect( cHost, [cDB], cUser, cPwd, [nPort], [nFlags] )
or oCn := mysql_Connect( { server, database, user, password, port, flags } ) // database,port,flags are optional
or oCn := mysql_Connect( "server,database,user,password" )
// Application built with Spanish codepage
cSql := "SELECT First, Age, DATE_FORMAT( HIREDATE, '%d %M %Y %W' ) AS HireDate FROM customer"
oRs := oCn:RowSet( cSql, .t. )
XBROWSER oRs AUTOFIT
oCn:SetLocale( "zh_TW" ) // Chinese
cSql := "SELECT First, Age, DATE_FORMAT( HIREDATE, '%d %M %Y %W' ) AS HireDate FROM customer"
oRs := oCn:RowSet( cSql, .t. )
XBROWSER oRs AUTOFIT
oRs := oCn:RowSet( "select * from customers where state = &1", { "WA" } )
XBROWSER oRs
oRs:ReQuery( { "NY" }
XBROWSWER oRs
oRs:Salary := 20000
oRs:Save()
//or
oRs:Skip() // saves and skips
oRs:Append()
oRs:Name := "Andrew"
oRs:Salary := 20000
oRs:Save() // commits the new record and refreshes the rowset
oRs:Append( { "name", "salary" }, { "Anderson", 30000 } )
Se precisar de mais exemplos :
http://forums.fivetechsupport.com/viewtopic.php?f=6&t=33304&sid=ee696b28cbb3ad3f4d449bf21a27a9c8
Delpji 7, harbour 3.2, xharbour 1.2.3, Bcc7, Minigw, Minigui 19.11, hwgui 2.20, FiveWin 19.05 Gtwvw, Gtwvg, C# VS 2017