Moderador: Moderadores
oTemporario := oConexao:Execute( "SELECT CODIGO, NOME, ENDERECO FROM CLIENTES ORDER BY NOME" )
oTBrowse := { ;
{ "CODIGO", { || Pad( oTemporario:Fields( "CODIGO" ):Value, 10 ) } }, ;
{ "NOME", { || Pad( oTemporario:Fields( "NOME" ):Value, 30 ) } }, ;
{ "ENDERECO", { || Pad( oTemporario:Fields( "ENDERECO" ):Value, 30 ) } } }
BrowseADO( oTemporario, oTBrowse )
oTemporario:Close()
oTemporario := oConexao:Execute( "SELECT CODIGO, NOME, ENDERECO FROM CLIENTES ORDER BY NOME" )
WITH OBJECT oTemporario
oTBrowse := { ;
{ "CODIGO", { || Pad( :Fields( "CODIGO" ):Value, 10 ) } }, ;
{ "NOME", { || Pad( :Fields( "NOME" ):Value, 30 ) } }, ;
{ "ENDERECO", { || Pad( :Fields( "ENDERECO" ):Value, 30 ) } } }
BrowseADO( oTemporario, oTBrowse )
:Close()
ENDWITH
cQuery:= "SELECT LOGGERAL_NUMNOT, LOGGERAL_CODPRO, LOGGERAL_DATMOV, LOGGERAL_TIPMOV, LOGGERAL_CODVEN, LOGGERAL_CLIFOR, LOGGERAL_QUANT, LOGGERAL_VALUNI, LOGGERAL_OPERADOR from loggeral"
oTemporario := oCn:EXECUTE(cQuery)
INIT DIALOG oDlg TITLE "Log Geral - Consulta" ;
ICON MEMVAR->SGC_ICON ;
AT 0, 0 SIZE 1167,600 CLIPPER NOEXIT
ThisBRW := oDlg
@ 4,70 BROWSE oBrowse1 ARRAY OF oDlg SIZE 1160,460 STYLE WS_BORDER + WS_VSCROLL +WS_HSCROLL ;
ON SIZE ANCHOR_TOPABS + ANCHOR_LEFTABS + ANCHOR_RIGHTABS + ANCHOR_BOTTOMABS
oBrowse1:oStyleHead := HStyle():New( { 0xffffff, 0xbbbbbb }, 1,, 0.4, 16759929 )
oBrowse1:oFont := HFont():Add( '',0,-15,700,,,)
oBrowse1:aArray := oTemporario
oBrowse1:AddColumn( HColumn():New( "Nr.NOTA",{|v,o|o:aArray[o:nCurrent,1]},"C",16,0,.F., ) )
oBrowse1:AddColumn( HColumn():New( "Codigo",{|v,o|o:aArray[o:nCurrent,2]},"C",10,0,.F., ) )
oBrowse1:AddColumn( HColumn():New( "Movimento",{|v,o|o:aArray[o:nCurrent,3]},"D",12,0,.F., ) )
oBrowse1:AddColumn( HColumn():New( "Tipo",{|v,o|o:aArray[o:nCurrent,4]},"N",1,0,.F., ) )
oBrowse1:AddColumn( HColumn():New( "Vendedor",{|v,o|o:aArray[o:nCurrent,5]},"N",4,0,.F., ) )
oBrowse1:AddColumn( HColumn():New( "Cli/For",{|v,o|o:aArray[o:nCurrent,6]},"C",10,0,.F., ) )
oBrowse1:AddColumn( HColumn():New( "Quantidade",{|v,o|o:aArray[o:nCurrent,7]},"N",14,2,.F., ) )
oBrowse1:AddColumn( HColumn():New( "Valor Unit.",{|v,o|o:aArray[o:nCurrent,8]},"N",14,2,.F., ) )
oBrowse1:AddColumn( HColumn():New( "Operador",{|v,o|o:aArray[o:nCurrent,9]},"C",20,0,.F., ) )
@ 8,-1 TOOLBAR oToolbar1 SIZE 1160,40 ;
oToolbar1:AddButton("EXIT16BMP", , 4, 16, "Sair" ,{|| DBCLOSEALL(), oDlg:Close() },'Sair da rotina','', "oToolbutton14")
oToolbar1:CreateTool()
ACTIVATE DIALOG oDlg CENTER
oCn:Close()
RETURN NIL
STATIC FUNCTION MySqlConnection( cServer, cDatabase, cUser, cPassword, nPort )
LOCAL cnConnection
hb_Default( @nPort, 3306 )
cnConnection:= win_OleCreateObject( "ADODB.Connection" )
cnConnection:ConnectionString := iif( win_OsIs10(), "Provider=MSDASQL;", "" )
cnConnection:ConnectionString += "Driver={MariaDB ODBC 3.1 Driver};"
cnConnection:ConnectionString += ;
"Server=" + cServer + ";" + ;
"Port=" + Ltrim( Str( nPort ) ) + ";" + ;
"Stmt=;" + ;
"Database=" + cDatabase + ";" + ;
"User=" + cUser + ";" + ;
"Password=" + cPassword + ";" + ;
"Collation=utf8_general_ci;" + ;
"AUTO_RECONNECT=1;" + ;
"COMPRESSED_PROTO=0;" + ;
"PAD_SPACE=1"
cnConnection:CursorLocation := 3
cnConnection:CommandTimeOut := 600 // seconds
cnConnection:ConnectionTimeOut := 600 // seconds
RETURN cnConnection
STATIC FUNCTION win_OsIs10(); RETURN .T.
oRs := cn:Execute( "SELECT CODIGO, NOME.... " )
DO WHILE ! oRs:Eof()
? oRs:Fields( "CODIGO" ):Value // ou oRs:Fields( 0 ):Value
? oRs:Fields( "NOME" ):Value // ou oRs:Fields( 1 ):Value
oRs:MoveNext()
ENDDO
oRs:Close()
/*
TITULO : SISTEMA DE GEST¦O DE COMERCIO
DATA : 14/03/2020
PROGRAMA : CONSULTALOGGERAL.PRG
COMENTARIO : CONSULTA LOG GERAL COM MARIADB
*/
#pragma -w0
#pragma -es0
#include "hwgui.ch"
******************************************
FUNCTION LOGGERALMARIADB( )
******************************************
LOCAL oDlg;
, oLabel1, oLabel2, oLabel3;
, obusca, oBrowse1 ;
, oToolbar1;
, oToolbutton1, oToolbutton2, oToolbutton3, oToolbutton4;
, oDatepicker1 , oDatepicker2 ;
, oColuna, bColorBlock ;
, vBusca := "";
, oQuery, oRow;
, aArray, oElemento;
, aResult:= {}
PRIVATE cProgressKey:="" ,dDatepicker1:=(m->dat_hoje - 30), dDatepicker2:=m->dat_hoje, lFiltro:=.T., nCODPRO:=0;
, oLabel5 :="", oLabel7 :="", oLabel10 :="", oLabel11 :="", oLabel6 :="", oLabel9 :="", oLabel12 :="", oLabel18 :=""
oServer := MySqlConnection( "192.168.15.200", "", "sgcroot", "20r13uqse" )
oServer:Open()
oServer:Execute( "USE AMADEU")
oQuery := oServer:Execute( "SELECT LOGGERAL_NUMNOT,";
+ "LOGGERAL_CODPRO,";
+ "LOGGERAL_DATMOV,";
+ "LOGGERAL_TIPMOV,";
+ "LOGGERAL_CODVEN,";
+ "LOGGERAL_CLIFOR,";
+ "LOGGERAL_QUANT,";
+ "LOGGERAL_VALUNI,";
+ "LOGGERAL_OPERADOR ";
+ "from loggeral LIMIT 9" )
DO WHILE ! oQuery:Eof()
aAdd( aResult, {oQuery:Fields( "LOGGERAL_NUMNOT" ):Value,;
oQuery:Fields( "LOGGERAL_CODPRO" ):Value,;
oQuery:Fields( "LOGGERAL_DATMOV" ):Value,;
oQuery:Fields( "LOGGERAL_TIPMOV" ):Value,;
oQuery:Fields( "LOGGERAL_CODVEN" ):Value,;
oQuery:Fields( "LOGGERAL_CLIFOR" ):Value,;
oQuery:Fields( "LOGGERAL_QUANT" ):Value,;
oQuery:Fields( "LOGGERAL_VALUNI" ):Value,;
oQuery:Fields( "LOGGERAL_OPERADOR" ):Value} )
oQuery:MoveNext()
ENDDO
oQuery:Close()
INIT DIALOG oDlg TITLE "Log Geral - Consulta" ;
ICON MEMVAR->SGC_ICON ;
AT 0, 0 SIZE 1167,600 CLIPPER NOEXIT
ThisBRW := oDlg
@ 4,70 BROWSE oBrowse1 ARRAY OF oDlg SIZE 1160,460 STYLE WS_BORDER + WS_VSCROLL +WS_HSCROLL ;
ON SIZE ANCHOR_TOPABS + ANCHOR_LEFTABS + ANCHOR_RIGHTABS + ANCHOR_BOTTOMABS
oBrowse1:oStyleHead := HStyle():New( { 0xffffff, 0xbbbbbb }, 1,, 0.4, 16759929 )
oBrowse1:oFont := HFont():Add( '',0,-15,700,,,)
oBrowse1:aArray := aResult
oBrowse1:AddColumn( HColumn():New( "Nr.Nota",hwg_ColumnArBlock(),"C",12,0,.F., ) )
oBrowse1:AddColumn( HColumn():New( "Codigo",hwg_ColumnArBlock(),"C",12,0,.F., ) )
oBrowse1:AddColumn( HColumn():New( "Movimento",hwg_ColumnArBlock(),"C",12,0,.F., ) )
oBrowse1:AddColumn( HColumn():New( "Tipo",hwg_ColumnArBlock(),"C",1,0,.F., ) )
oBrowse1:AddColumn( HColumn():New( "Vendedor",hwg_ColumnArBlock(),"C",4,0,.F., ) )
oBrowse1:AddColumn( HColumn():New( "Cli/For",hwg_ColumnArBlock(),"C",10,0,.F., ) )
oBrowse1:AddColumn( HColumn():New( "Quantidade",hwg_ColumnArBlock(),"C",14,0,.F., ) )
oBrowse1:AddColumn( HColumn():New( "Valor Unit.",hwg_ColumnArBlock(),"C",14,0,.F., ) )
oBrowse1:AddColumn( HColumn():New( "Operador",hwg_ColumnArBlock(),"C",20,0,.F., ) )
@ 8,-1 TOOLBAR oToolbar1 SIZE 1160,40 ;
// oToolbar1:AddButton("BRW_FIL", , 4, 16, "Aplica" ,{||FILTROLOGGERAL( ),obrowse1:REFRESH()},'Aplica Filtro no Arquivo de Log Geral','', "oToolbutton1")
// oToolbar1:AddButton("BRW_DFIL", , 4, 16, "Remove" ,{||REMOVEFILTROLOGGERAL( ),obrowse1:REFRESH()},'Remover Filtro do Arquivo de Log Geral','', "oToolbutton2")
oToolbar1:AddButton("EXIT16BMP", , 4, 16, "Sair" ,{|| DBCLOSEALL(), oDlg:Close() },'Sair da rotina','', "oToolbutton14")
oToolbar1:CreateTool()
ACTIVATE DIALOG oDlg CENTER
oServer:Close()
RETURN NIL
FUNCTION MySqlConnection( cServer, cDatabase, cUser, cPassword, nPort )
LOCAL cnConnection
hb_Default( @nPort, 3306 )
cnConnection:= win_OleCreateObject( "ADODB.Connection" )
cnConnection:ConnectionString := iif( win_OsIs10(), "Provider=MSDASQL;", "" )
cnConnection:ConnectionString += "Driver={MariaDB ODBC 3.1 Driver};"
cnConnection:ConnectionString += ;
"Server=" + cServer + ";" + ;
"Port=" + Ltrim( Str( nPort ) ) + ";" + ;
"Stmt=;" + ;
"Database=" + cDatabase + ";" + ;
"User=" + cUser + ";" + ;
"Password=" + cPassword + ";" + ;
"Collation=utf8_general_ci;" + ;
"AUTO_RECONNECT=1;" + ;
"COMPRESSED_PROTO=0;" + ;
"PAD_SPACE=1"
cnConnection:CursorLocation := 3
cnConnection:CommandTimeOut := 600 // seconds
cnConnection:ConnectionTimeOut := 600 // seconds
RETURN cnConnection
FUNCTION win_OsIs10(); RETURN .T.
DO WHILE ! oQuery:Eof()
aAdd( aResult, {oQuery:Fields( "LOGGERAL_NUMNOT" ):Value,;
oQuery:Fields( "LOGGERAL_CODPRO" ):Value,;
oQuery:Fields( "LOGGERAL_DATMOV" ):Value,;
oQuery:Fields( "LOGGERAL_TIPMOV" ):Value,;
oQuery:Fields( "LOGGERAL_CODVEN" ):Value,;
oQuery:Fields( "LOGGERAL_CLIFOR" ):Value,;
oQuery:Fields( "LOGGERAL_QUANT" ):Value,;
oQuery:Fields( "LOGGERAL_VALUNI" ):Value,;
oQuery:Fields( "LOGGERAL_OPERADOR" ):Value} )
oQuery:MoveNext()
ENDDO
aResult := oQuery:getRows()Ou na declaração do browse:
oBrowse1:aArray := oQuery:getRows()
oBrowse1:AddColumn( HColumn():New( "Nr.Nota",hwg_ColumnArBlock(),"C",12,0,.F., ) )
oBrowse1:AddColumn( HColumn():New( "Codigo",hwg_ColumnArBlock(),"C",12,0,.F., ) )
oBrowse1:AddColumn( HColumn():New( "Movimento",hwg_ColumnArBlock(),"D",12,0,.F., ) )
oBrowse1:AddColumn( HColumn():New( "Tipo", hwg_ColumnArBlock(),"N",12,0,.F., ) )
oBrowse1:AddColumn( HColumn():New( "Vendedor",hwg_ColumnArBlock(),"N",10,0,.F.,1,2,'9999' ) )
oBrowse1:AddColumn( HColumn():New( "Cli/For",hwg_ColumnArBlock(),"C",10,0,.F., ) )
oBrowse1:AddColumn( HColumn():New( "Quantidade",hwg_ColumnArBlock(),"N",14,2,.F.,1,2,'99999999999.99' ) )
oBrowse1:AddColumn( HColumn():New( "Valor Unit.", hwg_ColumnArBlock(),'N',14,2,.F.,1,2,'@E 99,999,999,999.99' ) )
oBrowse1:AddColumn( HColumn():New( "Operador",hwg_ColumnArBlock(),"C",20,0,.F., ) )
alxsts escreveu:Olá!
Parabéns!
Dicas:
1 - Sempre declare as variáveis antes de as utilizar.
2 - Procure nunca usar variáveis PRIVATE e PUBLIC. Use LOCAL ou STATIC
3 - Este bloco de códigoDO WHILE ! oQuery:Eof()
aAdd( aResult, {oQuery:Fields( "LOGGERAL_NUMNOT" ):Value,;
oQuery:Fields( "LOGGERAL_CODPRO" ):Value,;
oQuery:Fields( "LOGGERAL_DATMOV" ):Value,;
oQuery:Fields( "LOGGERAL_TIPMOV" ):Value,;
oQuery:Fields( "LOGGERAL_CODVEN" ):Value,;
oQuery:Fields( "LOGGERAL_CLIFOR" ):Value,;
oQuery:Fields( "LOGGERAL_QUANT" ):Value,;
oQuery:Fields( "LOGGERAL_VALUNI" ):Value,;
oQuery:Fields( "LOGGERAL_OPERADOR" ):Value} )
oQuery:MoveNext()
ENDDO
Pode ser trocado por apenas uma linha, utilizando-se um recurso do ADO que coloca o conteúdo de um record set em um array:
aResult := oQuery:getRows()Ou na declaração do browse:oBrowse1:aArray := oQuery:getRows()
Fernando queiroz escreveu:fiz o que você falou e trazia os dados de uma linha como uma colunapode ser a versão do harbour 3.2 que eu tenho que pode estar errada
oQuery := oServer:Execute( "SELECT LOGGERAL_NUMNOT,";
+ "LOGGERAL_CODPRO,";
+ "LOGGERAL_DATMOV,";
+ "LOGGERAL_CODVEN,";
+ "LOGGERAL_CLIFOR,";
+ "LOGGERAL_QUANT,";
+ "LOGGERAL_VALUNI,";
+ "LOGGERAL_OPERADOR, ";
+ "CASE WHEN LOGGERAL_TIPMOV IS TRUE THEN 'ENTRADA' ELSE 'SAIDAS' END AS STATUS_GERAL ";
+ "from loggeral WHERE LOGGERAL_CODPRO = " + STRZERO(nCODPRO,7,0);
+ " AND LOGGERAL_DATMOV BETWEEN '" ;
+ STRZERO(YEAR(dDatepicker1),4,0) + "-" + STRZERO(MONTH(dDatepicker1),2,0) + "-" + STRZERO(DAY(dDatepicker1),2,0);
+ "' AND '";
+ STRZERO(YEAR(dDatepicker2),4,0) + "-" + STRZERO(MONTH(dDatepicker2),2,0) + "-" + STRZERO(DAY(dDatepicker2),2,0);
+ "'" )
DO WHILE ! oQuery:Eof()
aAdd( aResult, {oQuery:Fields( "LOGGERAL_NUMNOT" ):Value,;
oQuery:Fields( "LOGGERAL_CODPRO" ):Value,;
oQuery:Fields( "LOGGERAL_DATMOV" ):Value,;
oQuery:Fields( "STATUS_GERAL" ):Value,;
oQuery:Fields( "LOGGERAL_CODVEN" ):Value,;
oQuery:Fields( "LOGGERAL_CLIFOR" ):Value,;
oQuery:Fields( "LOGGERAL_QUANT" ):Value,;
oQuery:Fields( "LOGGERAL_VALUNI" ):Value,;
oQuery:Fields( "LOGGERAL_OPERADOR" ):Value} )
oQuery:MoveNext()
ENDDO
oQuery:Close()
/*
TITULO : SISTEMA DE GEST¦O DE COMERCIO
DATA : 14/03/2020
PROGRAMA : CONSULTALOGGERAL.PRG
COMENTARIO : CONSULTA LOG GERAL COM MARIADB
*/
#pragma -w0
#pragma -es0
#include "hwgui.ch"
******************************************
FUNCTION LOGGERALMARIADB( )
******************************************
LOCAL oDlg;
, oLabel1, oLabel2, oLabel3;
, obusca, oBrowse1 ;
, oToolbar1;
, oToolbutton1, oToolbutton2, oToolbutton3, oToolbutton4;
, oDatepicker1 , oDatepicker2 ;
, oColuna, bColorBlock ;
, vBusca := "";
, oQuery, oRow;
, aArray, oElemento;
, aResult:= {}
PRIVATE cProgressKey:="" ,dDatepicker1:=(m->dat_hoje - 30), dDatepicker2:=m->dat_hoje, lFiltro:=.T., nCODPRO:=0;
, oLabel5 :="", oLabel7 :="", oLabel10 :="", oLabel11 :="", oLabel6 :="", oLabel9 :="", oLabel12 :="", oLabel18 :="";
IF ! ABRE_PRODUTOS( )
DBCLOSEALL()
RETURN
END
oServer := MySqlConnection( "192.168.15.200", "", "sgcroot", "20r13uqse" )
oServer:Open()
oServer:Execute( "USE AMADEU")
oQuery := oServer:Execute( "SELECT LOGGERAL_NUMNOT,";
+ "LOGGERAL_CODPRO,";
+ "LOGGERAL_DATMOV,";
+ "LOGGERAL_CODVEN,";
+ "LOGGERAL_CLIFOR,";
+ "LOGGERAL_QUANT,";
+ "LOGGERAL_VALUNI,";
+ "LOGGERAL_OPERADOR, ";
+ "CASE WHEN LOGGERAL_TIPMOV IS TRUE THEN 'ENTRADA' ELSE 'SAIDAS' END AS STATUS_TIPO ";
+ "from loggeral WHERE LOGGERAL_DATMOV BETWEEN '";
+ STRZERO(YEAR(dDatepicker1),4,0) + "-" + STRZERO(MONTH(dDatepicker1),2,0) + "-" + STRZERO(DAY(dDatepicker1),2,0);
+ "' AND '";
+ STRZERO(YEAR(dDatepicker2),4,0) + "-" + STRZERO(MONTH(dDatepicker2),2,0) + "-" + STRZERO(DAY(dDatepicker2),2,0);
+ "'" )
DO WHILE ! oQuery:Eof()
aAdd( aResult, {oQuery:Fields( "LOGGERAL_NUMNOT" ):Value,;
oQuery:Fields( "LOGGERAL_CODPRO" ):Value,;
oQuery:Fields( "LOGGERAL_DATMOV" ):Value,;
oQuery:Fields( "STATUS_TIPO" ):Value,;
oQuery:Fields( "LOGGERAL_CODVEN" ):Value,;
oQuery:Fields( "LOGGERAL_CLIFOR" ):Value,;
oQuery:Fields( "LOGGERAL_QUANT" ):Value,;
oQuery:Fields( "LOGGERAL_VALUNI" ):Value,;
oQuery:Fields( "LOGGERAL_OPERADOR" ):Value} )
oQuery:MoveNext()
ENDDO
oQuery:Close()
INIT DIALOG oDlg TITLE "Log Geral - Consulta" ;
ICON MEMVAR->SGC_ICON ;
AT 0, 0 SIZE 1167,600 CLIPPER NOEXIT
ThisBRW := oDlg
@ 4,70 BROWSE oBrowse1 ARRAY OF oDlg SIZE 1160,495 STYLE WS_BORDER + WS_VSCROLL +WS_HSCROLL ;
ON SIZE ANCHOR_TOPABS + ANCHOR_LEFTABS + ANCHOR_RIGHTABS + ANCHOR_BOTTOMABS
oBrowse1:oStyleHead := HStyle():New( { 0xffffff, 0xbbbbbb }, 1,, 0.4, 16759929 )
oBrowse1:oFont := HFont():Add( '',0,-15,700,,,)
oBrowse1:aArray := aResult
oBrowse1:AddColumn( HColumn():New( "Nr.Nota",hwg_ColumnArBlock(),"C",12,0,.F., ) )
oBrowse1:AddColumn( HColumn():New( "Codigo",hwg_ColumnArBlock(),"C",12,0,.F., ) )
oBrowse1:AddColumn( HColumn():New( "Movimento",hwg_ColumnArBlock(),"D",12,0,.F., ) )
oBrowse1:AddColumn( HColumn():New( "Tipo", hwg_ColumnArBlock(),"C",12,0,.F., ) )
oBrowse1:AddColumn( HColumn():New( "Vendedor",hwg_ColumnArBlock(),"N",10,0,.F.,1,2,'9999' ) )
oBrowse1:AddColumn( HColumn():New( "Cli/For",hwg_ColumnArBlock(),"C",10,0,.F., ) )
oBrowse1:AddColumn( HColumn():New( "Quantidade",hwg_ColumnArBlock(),"N",14,3,.F.,1,2,'99999999999.999' ) )
oBrowse1:AddColumn( HColumn():New( "Valor Unit.", hwg_ColumnArBlock(),'N',14,2,.F.,1,2,'@E 99,999,999,999.99' ) )
oBrowse1:AddColumn( HColumn():New( "Operador",hwg_ColumnArBlock(),"C",20,0,.F., ) )
@ 8,-1 TOOLBAR oToolbar1 SIZE 1160,40 ;
oToolbar1:AddButton("BRW_FIL", , 4, 16, "Filtra" ,{||oBrowse1:aArray := FILTROLOGGERAL( ), obrowse1:REFRESH()},'Filtra o Arquivo de Log Geral','', "oToolbutton1")
oToolbar1:AddButton("EXIT16BMP", , 4, 16, "Sair" ,{|| DBCLOSEALL(), oDlg:Close() },'Sair da rotina','', "oToolbutton14")
oToolbar1:CreateTool()
ADD STATUS PANEL TO oDlg HEIGHT 30 PARTS 200,120,300
@ 4,43 SAY oLabel1 CAPTION "Codigo" SIZE 138,25 ;
STYLE SS_CENTER +WS_DLGFRAME
@ 150,43 GET oEdit1 VAR nCODPRO SIZE 90,25 STYLE ES_RIGHT PICTURE '9999999' MAXLENGTH 7
@ 245,43 OWNERBUTTON oOwnerbutton1 SIZE 24,24 ;
TEXT '' ;
COORDINATES 0, 0, 0, 0 ;
BITMAP 'FINDBMP' FROM RESOURCE TRANSPARENT;
COORDINATES 0, 0, 0, 0 ;
ON CLICK {|| ThisBRW:oEdit1:SETFOCUS(), CONSULTA_PRODUTOS( ), nCODPRO := VAL(PRODUTOS->CODPRO), ThisBRW:oEdit1:REFRESH() }
@ 283,47 SAY oLabel2 CAPTION "Periodo:" SIZE 56,18
@ 349,43 GET DATEPICKER oDatepicker1 VAR dDatepicker1 SIZE 125,24 VALID {|| (dDatepicker1 < dDatepicker2) }
@ 482,47 SAY oLabel3 CAPTION "A" SIZE 10,18
@ 499,43 GET DATEPICKER oDatepicker2 VAR dDatepicker2 SIZE 125,24
ACTIVATE DIALOG oDlg CENTER
DBCLOSEALL()
oServer:Close()
RETURN NIL
************************************************************************************
FUNCTION MySqlConnection( cServer, cDatabase, cUser, cPassword, nPort )
************************************************************************************
LOCAL cnConnection
hb_Default( @nPort, 3306 )
cnConnection:= win_OleCreateObject( "ADODB.Connection" )
cnConnection:ConnectionString := iif( win_OsIs10(), "Provider=MSDASQL;", "" )
cnConnection:ConnectionString += "Driver={MariaDB ODBC 3.1 Driver};"
cnConnection:ConnectionString += ;
"Server=" + cServer + ";" + ;
"Port=" + Ltrim( Str( nPort ) ) + ";" + ;
"Stmt=;" + ;
"Database=" + cDatabase + ";" + ;
"User=" + cUser + ";" + ;
"Password=" + cPassword + ";" + ;
"Collation=utf8_general_ci;" + ;
"AUTO_RECONNECT=1;" + ;
"COMPRESSED_PROTO=0;" + ;
"PAD_SPACE=1"
cnConnection:CursorLocation := 3
cnConnection:CommandTimeOut := 600 // seconds
cnConnection:ConnectionTimeOut := 600 // seconds
RETURN cnConnection
******************************************
FUNCTION win_OsIs10(); RETURN .T.
******************************************
*****************************************
STATIC FUNCTION onDlgInit( )
*****************************************
hwg_WriteStatus( ThisBRW,1,"Tabela: " + Alias())
hwg_WriteStatus( ThisBRW,2,LTrim(Str( RecNo(),8))+ "/" +LTrim(Str( Lastrec(),8)))
hwg_WriteStatus( ThisBRW,3,"Ordem: Codigo/Data Movimento")
RETURN Nil
********************************************************
STATIC FUNCTION FILTROLOGGERAL( )
********************************************************
LOCAL nI, aResult:={}
// FOR nI = Len( aResult ) TO 1 STEP -1
// hb_ADel( aResult, nI, .T. )
// NEXT
IF nCODPRO # 0
oQuery := oServer:Execute( "SELECT LOGGERAL_NUMNOT,";
+ "LOGGERAL_CODPRO,";
+ "LOGGERAL_DATMOV,";
+ "LOGGERAL_CODVEN,";
+ "LOGGERAL_CLIFOR,";
+ "LOGGERAL_QUANT,";
+ "LOGGERAL_VALUNI,";
+ "LOGGERAL_OPERADOR, ";
+ "CASE WHEN LOGGERAL_TIPMOV IS TRUE THEN 'ENTRADA' ELSE 'SAIDAS' END AS STATUS_TIPO ";
+ "from loggeral WHERE LOGGERAL_CODPRO = " + STRZERO(nCODPRO,7,0);
+ " AND LOGGERAL_DATMOV BETWEEN '" ;
+ STRZERO(YEAR(dDatepicker1),4,0) + "-" + STRZERO(MONTH(dDatepicker1),2,0) + "-" + STRZERO(DAY(dDatepicker1),2,0);
+ "' AND '";
+ STRZERO(YEAR(dDatepicker2),4,0) + "-" + STRZERO(MONTH(dDatepicker2),2,0) + "-" + STRZERO(DAY(dDatepicker2),2,0);
+ "'" )
DO WHILE ! oQuery:Eof()
aAdd( aResult, {oQuery:Fields( "LOGGERAL_NUMNOT" ):Value,;
oQuery:Fields( "LOGGERAL_CODPRO" ):Value,;
oQuery:Fields( "LOGGERAL_DATMOV" ):Value,;
oQuery:Fields( "STATUS_TIPO" ):Value,;
oQuery:Fields( "LOGGERAL_CODVEN" ):Value,;
oQuery:Fields( "LOGGERAL_CLIFOR" ):Value,;
oQuery:Fields( "LOGGERAL_QUANT" ):Value,;
oQuery:Fields( "LOGGERAL_VALUNI" ):Value,;
oQuery:Fields( "LOGGERAL_OPERADOR" ):Value} )
oQuery:MoveNext()
ENDDO
oQuery:Close()
ELSE
oQuery := oServer:Execute( "SELECT LOGGERAL_NUMNOT,";
+ "LOGGERAL_CODPRO,";
+ "LOGGERAL_DATMOV,";
+ "LOGGERAL_CODVEN,";
+ "LOGGERAL_CLIFOR,";
+ "LOGGERAL_QUANT,";
+ "LOGGERAL_VALUNI,";
+ "LOGGERAL_OPERADOR, ";
+ "CASE WHEN LOGGERAL_TIPMOV IS TRUE THEN 'ENTRADA' ELSE 'SAIDAS' END AS STATUS_TIPO ";
+ "from loggeral WHERE LOGGERAL_DATMOV BETWEEN '";
+ STRZERO(YEAR(dDatepicker1),4,0) + "-" + STRZERO(MONTH(dDatepicker1),2,0) + "-" + STRZERO(DAY(dDatepicker1),2,0);
+ "' AND '";
+ STRZERO(YEAR(dDatepicker2),4,0) + "-" + STRZERO(MONTH(dDatepicker2),2,0) + "-" + STRZERO(DAY(dDatepicker2),2,0);
+ "'" )
DO WHILE ! oQuery:Eof()
aAdd( aResult, {oQuery:Fields( "LOGGERAL_NUMNOT" ):Value,;
oQuery:Fields( "LOGGERAL_CODPRO" ):Value,;
oQuery:Fields( "LOGGERAL_DATMOV" ):Value,;
oQuery:Fields( "STATUS_TIPO" ):Value,;
oQuery:Fields( "LOGGERAL_CODVEN" ):Value,;
oQuery:Fields( "LOGGERAL_CLIFOR" ):Value,;
oQuery:Fields( "LOGGERAL_QUANT" ):Value,;
oQuery:Fields( "LOGGERAL_VALUNI" ):Value,;
oQuery:Fields( "LOGGERAL_OPERADOR" ):Value} )
oQuery:MoveNext()
ENDDO
oQuery:Close()
ENDIF
RETURN aResult
****************************************
STATIC FUNCTION CONSULTA_PRODUTOS( )
****************************************
LOCAL oDlgPro ;
, oLabel1, oLabel2 ;
, oBrowse4 ;
, oColuna, bColorBlock, vBusca ;
, oToolbar1 ;
, nKeyPress
PRIVATE cProgressKey:=""
DBSELECTAREA("PRODUTOS")
ORDSETFOCUS( 2 )
PRODUTOS->(dbgotop())
INIT DIALOG oDlgPro TITLE "Nota de Pedido - Consulta Produtos" ;
ICON MEMVAR->SGC_ICON ;
AT 0, 0 SIZE 1006,582 CLIPPER NOEXIT NOCLOSABLE ;
ON INIT {|This| onDlgInit3( This, oDlgPro ) }
PRODUTOSBRW := oDlgPro
@ 4,70 BROWSE oBrowse4 DATABASE OF oDlgPro SIZE 1000,480 STYLE WS_BORDER + WS_VSCROLL +WS_HSCROLL ;
ON SIZE ANCHOR_TOPABS + ANCHOR_LEFTABS + ANCHOR_RIGHTABS + ANCHOR_BOTTOMABS ;
ON POSCHANGE {|| hwg_WriteStatus( oDlgPro,2,LTrim(Str( RecNo(),8))+ "/" +LTrim(Str( Lastrec(),8))) } ;
ON CLICK {|| PESQUISAPRODUTO_onClick( ) } ;
ON KEYDOWN {|oBrowse4,nKeyPress| PESQUISAPRODUTO_onKeyDown( oBrowse4,nKeyPress ) }
oBrowse4:oStyleHead := HStyle():New( { 0xffffff, 0xbbbbbb }, 1,, 0.4, 16759929 )
oBrowse4:oFont := HFont():Add( '',0,-18,700,,,)
oBrowse4:freeze := 2
// oBrowse4:alias := 'PRODUTOS'
// SELECT (oBrowse4:alias)
bColorBlock :={|| IIF(PRODUTOS->ESTATU + PRODUTOS->ESTL02 = 0.0000 ,{255, 16777215 ,16777215,255 },{0,16777215,, }) }
oBrowse4:AddColumn( HColumn():New('Codigo', FieldBlock( 'codpro' ) ,'C',9, 0 ,.F.,1,2,'@R 999999-9',,,,,;
{|| Ordsetfocus(1), Dbgotop(), obrowse4:Refresh(), vBusca:="" , ;
hwg_WriteStatus( oDlgPro,3,"Ordem: Codigo ") } ,,,,))
oBrowse4:AddColumn( HColumn():New('Descrição/Nome Produto', FieldBlock( 'despro' ) ,'C',58, 0 ,.F.,1,,'@S58',,,,,;
{|| Ordsetfocus(2), Dbgotop(), obrowse4:Refresh(), vBusca:="" , ;
hwg_WriteStatus( oDlgPro,3,"Ordem: Descriçao") } ,,,,))
oBrowse4:AddColumn( HColumn():New('Unidade', FieldBlock( 'TIPUNI' ) ,'C',9, 0 ,.F.,1,,,,,,,,,,,))
oBrowse4:AddColumn( HColumn():New('Preço Venda', FieldBlock( 'preven' ) ,'N',14, 2 ,.F.,1,2,'@E 999,999.99',,,,,,,,,))
oBrowse4:AddColumn( HColumn():New('Estoque Loja', {|| IF(PROSER = "1",ESTATU,ESTATU*(-1)) } ,'N',12, 3 ,.F.,1,2,,,,,,,,,,))
// bColorBlock :={|| IIF(PRODUTOS->ESTATU + PRODUTOS->ESTL02 = 0.0000 ,{255, 16777215 ,16777215,255 },{0,16777215,, }) }
FOR EACH oColuna IN oBrowse4:aColumns
oColuna:bColorBlock := bColorBlock
NEXT
@ 8,-1 TOOLBAR oToolbar1 SIZE 945,40 ;
oToolbar1:AddButton("EXIT16BMP", , 4, 16, "Sair" ,{|| oDlgPro:Close() },'Sair da rotina','', "oToolbutton8")
oToolbar1:CreateTool()
ADD STATUS PANEL TO oDlgPro HEIGHT 30 PARTS 200,120,300
@ 4,43 SAY oLabel1 CAPTION "Pesquisar" SIZE 138,25 ;
STYLE SS_CENTER +WS_DLGFRAME
@ 150,43 SAY oLabel2 CAPTION "" SIZE 410,25 ;
STYLE WS_BORDER;
COLOR 16777215 BACKCOLOR 255
ACTIVATE DIALOG oDlgPro CENTER
RETURN .T.
***********************************************************************************
STATIC FUNCTION onDlgInit3( This, oDlgPro )
hwg_WriteStatus( oDlgPro,1,"Tabela: " + Alias())
hwg_WriteStatus( oDlgPro,2,LTrim(Str( RecNo(),8))+ "/" +LTrim(Str( Lastrec(),8)))
hwg_WriteStatus( oDlgPro,3,"Ordem: Descrição")
RETURN .T.
******************************************************
STATIC FUNCTION PESQUISAPRODUTO_onClick( )
IF hwg_LastKey() == MK_LBUTTON
PRODUTOSBRW:Close()
ENDIF
RETURN .T.
********************************************************
STATIC FUNCTION PESQUISAPRODUTO_onKeyDown( oBrowse4,nKey )
LOCAL nPosicao:=0, nKeyPress:=nkey
LOCAL cRegEx := "[a-zA-Z0-9 ]{1}"
KSETCAPS(.T.)
aResult:=HB_RegExALL( cRegEx, Chr(nKeyPress) )
IF nKeyPress # 8 .AND. AllTrim(Str(nKeyPress)) $ "34|33|37|38|39|40|20|16"
cProgressKey:=""
PRODUTOSBRW:oLabel2:settext(cProgressKey)
RETURN .T.
ELSEIF nKeyPress # 8 .AND. AllTrim(Str(nKeyPress)) $ [90]
nKeyPress := 90
ELSEIF nKeyPress # 8 .AND. AllTrim(Str(nKeyPress)) $ [96]
nKeyPress := 48
ELSEIF nKeyPress # 8 .AND. AllTrim(Str(nKeyPress)) $ [97]
nKeyPress := 49
ELSEIF nKeyPress # 8 .AND. AllTrim(Str(nKeyPress)) $ [98]
nKeyPress := 50
ELSEIF nKeyPress # 8 .AND. AllTrim(Str(nKeyPress)) $ [99]
nKeyPress := 51
ELSEIF nKeyPress # 8 .AND. AllTrim(Str(nKeyPress)) $ [100]
nKeyPress := 52
ELSEIF nKeyPress # 8 .AND. AllTrim(Str(nKeyPress)) $ [101]
nKeyPress := 53
ELSEIF nKeyPress # 8 .AND. AllTrim(Str(nKeyPress)) $ [102]
nKeyPress := 54
ELSEIF nKeyPress # 8 .AND. AllTrim(Str(nKeyPress)) $ [103]
nKeyPress := 55
ELSEIF nKeyPress # 8 .AND. AllTrim(Str(nKeyPress)) $ [104]
nKeyPress := 56
ELSEIF nKeyPress # 8 .AND. AllTrim(Str(nKeyPress)) $ [105]
nKeyPress := 57
ELSEIF nKeyPress # 8 .AND. AllTrim(Str(nKeyPress)) $ [106]
nKeyPress := 42
ELSEIF nKeyPress # 8 .AND. AllTrim(Str(nKeyPress)) $ [190]
nKeyPress := 46
ELSEIF nKeyPress == Asc("8") .AND. hwg_isCtrlShift( .F.,.T. )
nKeyPress := 42
ENDIF
IF nKeyPress = VK_RETURN .OR. nKeyPress = VK_ESCAPE
PRODUTOSBRW:Close()
RETURN .T.
ENDI
// IF IsAscii(Chr(nKeyPress)) .AND. nKeyPress # 8
If nKeyPress # 8
cProgressKey += Chr(nKeyPress)
nPosicao:=AScan(PRODUTOSBRW:oBrowse4:aArray,cProgressKey)
PRODUTOSBRW:oLabel2:settext(cProgressKey)
PRODUTOSBRW:oBrowse4:TOP()
DBSEEK(cProgressKey,.T.)
ELSEIF nKeyPress = 8 .AND. Len(cProgressKey) >= 1
cProgressKey:=Left(cProgressKey,Len(cProgressKey) - 1)
nPosicao:=AScan(PRODUTOSBRW:oBrowse4:aArray,cProgressKey)
PRODUTOSBRW:oLabel2:settext(cProgressKey)
PRODUTOSBRW:oBrowse4:TOP()
DBSEEK(cProgressKey,.T.)
ENDIF
IF nPosicao # 0
oBrowse4:rowPos := nPosicao
Eval( PRODUTOSBRW:oBrowse4:bGoTo, PRODUTOSBRW:oBrowse4, nPosicao )
PRODUTOSBRW:oBrowse4:Refresh()
ENDIF
return .t.
Retornar para Contribuições, Dicas e Tutoriais
Usuários vendo este fórum: Nenhum usuário registrado online e 26 visitantes