Macros para o BASE-Open Office

REM Macro semelhante ao AutoExec do ACCESS

Sub AutoExec
On Error Goto HandleError
Dim LastFrame As Object
Dim NumFrames As Integer
Static FormDocs As Object
Dim DBDoc As Object
Dim ImpName As String
Dim DataSource As Object
Dim Conn As Object
Dim Args(1) As New com.sun.star.beans.PropertyValue
Dim FormName As String
Dim FormDoc As Object
FormName="SWITCHBOARD" 'Mude aqui para o Forms de Abertura da Aplicação
ImpName="com.sun.star.comp.dba.ODatabaseDocument"
NumFrames=StarDesktop.Frames.Count
LastFrame=StarDesktop.Frames.getByIndex(NumFrames-1)
If LastFrame.Frames.Count>1 Then
Exit Sub
End If
If Not (LastFrame.Controller.Model.ImplementationName=ImpName) Then
Exit Sub REM not a db doc
End If
DataSource=LastFrame.Controller.DataSource
DBDoc=DataSource.DatabaseDocument
FormDocs=DBDoc.FormDocuments
Conn=DataSource.getConnection("","") REM no user/password
Args(0).Name="ActiveConnection" : Args(0).Value=Conn
if FormDocs.hasByName(FormName) Then
FormDoc=FormDocs.loadComponentFromURL(FormName,"_self",2,Args() )
FormDoc.CurrentController.Frame.ContainerWindow.setFocus()
End If
HandleError:
If Err<>0 Then
Exit Sub
End If
End Sub

REM Macro generica para abrir qualquer forms

function OpenForm( formContainer as variant, oConnection as variant, sFormName as string) as variant
Dim aProp(1) As New com.sun.star.beans.PropertyValue
aProp(0).Name = "ActiveConnection"
aProp(0).Value = oConnection
aProp(1).Name = "OpenMode"
aProp(1).Value = "open"
OpenForm = formContainer.loadComponentFromURL(sFormName,"_blank",0,aProp())
end function

function getFormsTC() as variant
getFormsTC = thisComponent.Parent.getFormDocuments
end function

function getConnectionTC() as variant
getConnectionTC = thisComponent.Drawpage.Forms(0).ActiveConnection
end function

REM Macro para abrir um forms específico

sub OpenForm_fTESTE( oev as variant )
sFormName = "fTESTE" ' Mude aqui o nome do seu forms
OpenForm( getFormsTC, getConnectionTC, sFormName )
end sub

REM Macro generica para abrir qualquer report

function getConnectionTC() as variant
getConnectionTC = thisComponent.Drawpage.Forms(0).ActiveConnection
end function

function OpenReport( reportContainer as variant, oConnection as variant, sReportName as string) as variant
Dim aProp(1) As New com.sun.star.beans.PropertyValue
aProp(0).Name = "ActiveConnection"
aProp(0).Value = oConnection
aProp(1).Name = "OpenMode"
aProp(1).Value = "open"
OpenReport=reportContainer.loadComponentFromURL(sReportName,"_blank",0,aProp())
end function

function getReportsTC() as variant
getReportsTC = thisComponent.Parent.getReportDocuments
end function

REM Macro para abrir um report especifico

sub OpenReport_rREPORT1( oEv as variant )
sReportName="rREPORT1" ' Mude aqui o nome do seu Report
OpenReport( getReportsTC, getConnectionTC, sReportName )
end sub

REM Macro para abrir um formulário sem abrir a Base de dados

Sub openOnlyfMOVIMENTOS
oContexto = CreateUnoService("com.sun.star.sdb.DatabaseContext")
oFonte = oContexto.getByName("contabilidade")'Mude aqui o nome da base de dados
oForms = oFonte.DatabaseDocument.FormDocuments
oAConnection = oFonte.getConnection("","")

Dim pProp(1) As New com.sun.star.beans.PropertyValue
pProp(0).Name = "ActiveConnection"
pProp(0).Value = oAConnection
pProp(1).Name = "OpenMode"
pProp(1).Value = "open"
oForm = oForms.loadComponentFromURL("fMOVIMENTOS", "_blank", 0, pProp())'Mude aqui o nome do seu formulário
End Sub


REM Macro para abrir um relatório sem abrir a base de dados

Sub openOnlyrMOVIMENTOS
oContexto = CreateUnoService("com.sun.star.sdb.DatabaseContext")
oFonte = oContexto.getByName("contabilidade")'Mude aqui o nome da base de dados
oReports = oFonte.DatabaseDocument.ReportDocuments
oAConnection = oFonte.getConnection("","")

Dim pProp(1) As New com.sun.star.beans.PropertyValue
pProp(0).Name = "ActiveConnection"
pProp(0).Value = oAConnection
pProp(1).Name = "OpenMode"
pProp(1).Value = "open"

oReport = oReports.loadComponentFromURL("rMOVIMENTOS", "_blank", 0, pProp())' Mude aqui o nome do seu relatório
End Sub


REM Macro para apagar valores de uma tabela

Sub DeleteAllFromTEST_TABLE2

Dim DatabaseContext As Object
Dim DataSource As Object
Dim Connection As Object
Dim Statement As Object
Dim ResultSet As Object
DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
DataSource = DatabaseContext.getByName("teste")
Connection = DataSource.GetConnection("","")
Statement = Connection.createStatement()
ResultSet = Statement.executeQuery("DELETE FROM TEST_TABLE2 WHERE ID = 2")
End Sub

REM Macro para inserir valores numa tabela

Sub InsertFromTEST_TABLE2
Dim DatabaseContext As Object
Dim DataSource As Object
Dim Connection As Object
Dim Statement As Object
Dim ResultSet As Object
DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
DataSource = DatabaseContext.getByName("teste")
Connection = DataSource.GetConnection("","")
Statement = Connection.createStatement()
ResultSet = Statement.executeQuery("INSERT INTO TEST_TABLE2(ID,F1,F2,F3)VALUES (1,'a','abc','abcd')")
End Sub

REM Macro para inserir valores numa tabela provenientes de outra

Sub SelectInsertfromTEST_TABLE2
Dim DatabaseContext As Object
Dim DataSource As Object
Dim Connection As Object
Dim Statement As Object
Dim ResultSet As Object
DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
DataSource = DatabaseContext.getByName("teste")
Connection = DataSource.GetConnection("","")
Statement = Connection.createStatement()
ResultSet = Statement.executeQuery("INSERT INTO TEST_TABLE2(ID,F1,F2,F3)SELECT ID, F1,F2,F3 FROM TEST_TABLE3")
End Sub

REM Macro para Alterar uma Tabela

sub UpdateTEST_TABLE2
Dim DatabaseContext As Object
Dim DataSource As Object
Dim Connection As Object
Dim Statement As Object
Dim ResultSet As Object
DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
DataSource = DatabaseContext.getByName("teste")
Connection = DataSource.GetConnection("","")
Statement = Connection.createStatement()
ResultSet = Statement.executeQuery("UPDATE TEST_TABLE2 SET F1=100, F2=200 WHERE ID=1")
End sub