[Solved] Invoicing: Calculate fields in form using sql query

[kububa]

Hello,
1. I would like to build simple invoicing system for the company I work in.

2. I have four tables:
OrderItem (OrderItemId, OrderItemName, OrderItemQuantity, OrderItemUnitPrice)
Invoice (InvoiceId, InvoiceNo, InvoiceDate, CompanyId)
InvoiceItem (InvoiceItemId, InvoiceId, OrderItemId, InvoicetemNo, InvoiceItemName, InvoiceItemQuantity, InvoiceItemUnitPrice)
Company (CompanyId, CompanyName, CompanyAddress).

3. First I enter the orders in the OrderItem table.

4. Then I have the invoice form (screenshot attached): masterform is Invoice and subform is InvoiceItem.
In master form Invoice I enter just general data of the invoice and in subform InvoiceItem I enter items to be on the invoice.
In a tablegrid InvoiceItem I have a list box showing ordered items from table OrderItem

Code: Select all

SELECT `OrderItemName`, `OrderItemId` FROM `OrderItem`

.

5. Then I have a invoice designed in Sun Report Builder.
Simple way is just to execute a report based on query

Code: Select all

SELECT `Invoice`.`InvoiceNo`, `Invoice`.`InvoiceDate`, `Company`.`CompanyName`, `InvoiceItem`.`InvoiceItemNo`, `InvoiceItem`.`InvoiceItemDescription`, `InvoiceItem`.`InvoiceItemQuantity`, `InvoiceItem`.`InvoiceItemUnitPrice` FROM `InvoiceItem`, `Invoice`, `Company`, `OrderItem` WHERE `InvoiceItem`.`InvoiceId` = `Invoice`.`InvoiceId` AND `Invoice`.`CompanyId` = `Company`.`CompanyId` AND `OrderItem`.`OrderItemId` = `InvoiceItem`.`OrderItemId`

.

6. But it is not he way I would like to do this.
I would like the user to be able to modify or add new items when issuing an invoice.
When entering data in InvoiceItem subform, when choosing from the list box showing ordered items from table OrderItem I would need a macro linked to the listbox just to suggest data and to fill the data into other textboxes InvoiceItemName, InvoiceItemQuantity, InvoiceItemUnitPrice, which are analog to the ones in OrderItem table. InvoiceItemName = OrderItemName etc. Then user could make modifications.

I think this macro should first read data (name of the ordered item etc. ) from database using sql query for the current OrderItemId

Code: Select all

SELECT `OrderItemId`, `OrderItemName`, `OrderItemPrice`, `OrderItemQuantity` FROM `OrderItem`

and then fill the textboxes in subform InvoiceItem with these values.

I found the below macro to calculate value and update the textbox in a form, but I do not know how to write a macro to read data with sql and then enter them into the from.

Code: Select all

Sub calcTotal(Event As Object)
   Dim Form As Object
   Dim Quantity As Object
   Dim UnitPrice As Object
   Dim Total As Double

   Form=Event.Source.Model.Parent
   Quantity=Form.GetByName("QUANTITY") REM text box w/ quantity
   UnitPrice=Form.GetByName("UNITPRICE") REM text box with price
                Total=CInt(Quanity.Text)*CDbl(UnitPrice.Text)
   Form.Columns.getByName("TOTAL").updateDouble(Total)
End Sub

Does any one know how to do that?
Thank you!

[Villeroy]

See Calculating a value in a form with attachment http://user.services.openoffice.org/en/ ... hp?id=1266

[kububa]

So, I have this macro code. It starts from:

Code: Select all

Sub suggestInvoiceItem(Event As Object)
   Dim Form As Object
   Dim OrderItemId As Object
   Dim Name As Object
   Dim Quantity As Double
   Dim UnitPrice As Double

   Form=Event.Source.Model.Parent
   OrderItemId=Form.GetByName("OrderItemId") REM get OrderItemId which was chosen from the listbox 

Now I would like to execute below sql command

Code: Select all

SELECT `OrderItemId`, `OrderItemName`, `OrderItemPrice`, `OrderItemQuantity` FROM `OrderItem`

and get the Name, Quantity, UnitPrice.
This I do not know how to do.
Then I update the from.

Code: Select all

   Form.Columns.getByName("Name").updateDouble(InvoiceItemName)
End Sub

How to get this sql command into macro?
Shall I update textboxes in form or maybe directly tables with sql "UPDATE" and then refresh the form?

[kububa]

Ok, I found on this page how to get the result of sql query within a macro: http://www.vftw.com/view/programming/co ... orm-macro/
Begining of macro looks like this:

Code: Select all

Sub SuggestOrderItem(Event as Object)
Dim oSubForm As object
Dim Form As Object
Dim OrderItem As Integer[code]

oSubForm = Event.Source.Model.Parent
[/code]

Now I don't know only one thing: how to get the OrderItemId field from the list box in the form?
Then the macro continues, it executes sql query and get result and then it updates one text box with the value from query.

Code: Select all

dim rows
rows = createUnoService("com.sun.star.sdb.RowSet")
rows.ActiveConnection = oSubForm.ActiveConnection
rows.CommandType = com.sun.star.sdb.CommandType.COMMAND
rows.Command = "SELECT `OrderItemId`, `OrderItemNameOrder`, `OrderItemQuantity`, `OrderItemIDH`, `OrderItemPrice`, `OrderItemCurrency`, `CompanyId`, `OrderItemNo` FROM `OrderItem` WHERE `OrderItemId` = " & OrderItem
rows.execute()

dim col1, col2

while rows.next()
  col1 = rows.getInt(1)
  col2 = rows.getString(2)
wend
  Form.Columns.getByName("InvoiceItemDescription").updateString(col2)
End Sub

So, I don't know how to get the OrderItemId field from the list box in the form?
I installed Xray tool, but I'm not sure how to find the listbox name in it.
Any one knows how to do that? I also found information about listbox on this page: http://api.openoffice.org/docs/common/r ... lectedItem. I should probably use getSelectedItem, but I don't know how to do that.
Thanks for help!

[kububa]

Ok, I finally found the solution in this thread: http://www.oooforum.org/forum/viewtopic ... ueitemlist
To get the id from the listbox I use this code:

Code: Select all

sub Valuefromthelistbox
   'get the form (in this case form = records of the database)
   dim oDoc,oForm,docView,ctlView,oCtl, offset, OrderItemId
   oDoc=ThisComponent
   oForm=oDoc.DrawPage.forms.getbyindex(0) 
   oCtl=oForm.getByName("OrderItemListBox")
   OrderItemId = oCtl.ValueItemList(oCtl.SelectedItems(0))
   MsgBox OrderItemId
End Sub

I will put the odb file with macro when I finish the invoices.