| View previous topic :: View next topic |
| Author |
Message |
damged
Newbie
Joined: 21 Feb 2006
Posts: 2
Location: Århus, Denmark
|
 Posted: Tue Feb 21, 2006 1:52 pm Post subject: Making db for managing packaging labels |
 |
|
How-to make a Base or Calc database for printing packaging labels divided by amount in package?
We pack a lot of newspapers for shipment in stacks of min. 10 and max. 100 newspapres per stack for different recipients – these need adress labels printet for each of the stacks.
So if CustomerA needs 125 newspapers and CustomerB needs 42 newspapers – then we need to make and label 2 stacks for CustomerA (100+25) and just one for CustomerB.
How can I do this without having to manually enter CustomerA 2 seperate times? (Could be several thousands instead of 125 in the example) It has to be easy to both maintain the databaseinfo and create the print.
I was thinking I could do something like this in OOo Base and then create a report from the data tables to accomplish this – but I'm kindda stuck on how to do this...
Input would be different records like this:
“CustomerName”
“Adress-line1”
“Adress-line2”
“TotalQuantity”
And output to a plain text file – which must follow these examples:
“Product” - “Descriptive text” - “date”
BeginStack-1 – “Quantity=100”
L1 ; “John Johnson”
L2 ; “Johnstreet, 13”
L3 ; “Johnsonville”
L4 ; “Total 125”
L5 ; “Stack 1 of 2 total stacks”
BeginStack-2 - – “Quantity=25”
L1 ; “John Johnson”
L2 ; “Johnstreet, 13”
L3 ; “Johnsonville”
L4 ; “Total 125”
L5 ; “Stack 2 of 2 total stacks”
BeginStack-3 – “Quantity=42”
L1 ; “Mr. Simpson”
L2 ; “Evergreen Terasse”
L3 ; “Springfield”
L4 ; “Total 42”
L5 ; “Stack 1 of 1 total stacks”
Is this possible using only OpenOffice.org – or do I need some kind of SQLdb + custom frontend to do this? |
|
| Back to top |
|
 |
DrewJensen
Super User
Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD
|
| Posted: Tue Feb 21, 2006 4:33 pm Post subject: |
|
|
Well, you most certainly can do this using OpenOffice.
As to whether to use Base of Calc, really your choice..my choice surprisingly would be Base. Your example would need one table and a little basic code to implement verbatim.
So, I created a Base database named 'stacker'.
Added one table named 'Customers' with only one added field "ID" as a primary key.
Customers( ID, CustomerName, Address1, Address2, TotalQuantity )
Populated thies table with your example data.
I then created the following macro's in the standard basic library.
| Code: | sub GenerateList
Const dq = """" ' a double quote for output
Dim FileNo As Integer
Dim CurrentLine As String
Dim Filename As String
Dim oRS as variant ' result set object
Dim oConn as variant ' connction to the database
Dim oStmt as variant ' a statement obejct to run query
DIM aRecord(1 to 6 ) as string
Dim CustomerStacks as integer
Dim TotalStacks as integer
Dim CurrentStackNo as integer
Dim CurrentStackCount as integer
oConn = CreateUnoService("com.sun.star.sdb.DatabaseContext")._
getByName("stacker")._
getConnection( "", "" )
oStmt = oConn.CreateStatement
oRS = oStmt.executeQuery( "SELECT * FROM ""Customers"" ")
if not oRS.isBeforeFirst then
exit sub
else
oRS.next
endif
FileName = "c:\labels_" & Month(date) & "_" & Day(date) & "_" & Year(date) & "_" & cstr(second(time)) & ".txt"
FileNo = Freefile
Open Filename For Output As #FileNo
do
CustomerStacks = numStacks( 100, oRS.Columns.getByName( "TotalQuantity" ).getInt )
For CurrentStackNo = 1 to CustomerStacks
TotalStacks = TotalStacks + 1
aRecord(1) = "BeginStack-" & cstr( TotalStacks ) & " –" & dq & "Quantity="
if CurrentStackNo < CustomerStacks then
CurrentStackCount = 100
else
CurrentStackCount = oRS.Columns.getByName( "TotalQuantity" ).getInt MOD 100
endif
aRecord(1) = aRecord(1) & CurrentStackCount & dq
aRecord(2) = dq & oRS.Columns.getByName( "CustomerName" ).getString & dq
aRecord(3) = dq & oRS.Columns.getByName( "Address1" ).getString & dq
aRecord(4) = dq & oRS.Columns.getByName( "Address2" ).getString & dq
aRecord(5) = dq & "Total " & oRS.Columns.getByName( "TotalQuantity" ).getString & dq
aRecord(6) = dq & "Stack 1 of " & cstr( CustomerStacks ) & " total stacks" & dq
printRecord( FileNo, aRecord() )
next
oRS.Next
loop until oRS.isAfterLast
Close #FileNo
oConn.Dispose
end sub
sub printRecord( fHandle as integer, aLines() as string )
Print #fHandle, aLines(1)
Print #fHandle, "L1:" & aLines(2)
Print #fHandle, "L2:" & aLines(3)
Print #fHandle, "L3:" & aLines(4)
Print #fHandle, "L4:" & aLines(5)
Print #fHandle, "L5:" & aLines(6)
end sub
function numStacks( aStkSize as integer, aQty as integer ) as integer
dim iStacks
if aQty <= aStkSize then
iStacks = 1
else
iStacks = int( aQty / aStkSize ) + 1
endif
numStacks = iStacks
end function |
Running the macro GenerateList yealds a text file named c:\labels_2_21_2006_27.txt with the following contents:
| Code: | BeginStack-1 –"Quantity=100"
L1:"John Jonnson"
L2:"Johnstreet 13"
L3:"Johntown"
L4:"Total 125"
L5:"Stack 1 of 2 total stacks"
BeginStack-2 –"Quantity=25"
L1:"John Jonnson"
L2:"Johnstreet 13"
L3:"Johntown"
L4:"Total 125"
L5:"Stack 1 of 2 total stacks"
BeginStack-3 –"Quantity=45"
L1:"Mr. Simpson"
L2:"Evergreen Terasse"
L3:"Springfield"
L4:"Total 45"
L5:"Stack 1 of 1 total stacks" |
It is missing the first line, but I was not sure what you really neede for product and the like. Anyway, I hope that gives you a good roadmap for how you might do this.
If you are not familiar with the Basic language in OO.o you can get the StarOffice 8 Basic Programmers reference form the Sun website, there is nothing in the code above that is not covered in this reference.
If you have any other questions about it, just ask.
Drew
_________________
Blog - http://baseanswers.spaces.live.com/ |
|
| Back to top |
|
|
damged
Newbie
Joined: 21 Feb 2006
Posts: 2
Location: Århus, Denmark
|
| Posted: Wed Feb 22, 2006 12:17 am Post subject: Basic runtime error |
|
|
Hi Drew
Thank you - this was much more than I was hoping for (I wasn't even sure it could be done) and in such a quick reply!
But unfortunatly I'm not quite there yet - this black magic, known as Basic is not working out for me. Basically (pun intended) I end up getting a "BASIC runtime error. Property or method not found" on this line of the Macro: oConn = CreateUnoService("com.sun.star.sdb.DatabaseContext")._
I don't really know how to read this - is it something I am missing - or because of an error in the tables...?
I have created a new database named stacker.odb with table Customers. In the Customers table there is an auto incrementig ID field set as primary key and fields for CustomerName, Address1, Address2 amd TotalQuantity.
So I think I have done this part right... |
|
| Back to top |
|
|
DrewJensen
Super User
Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD
|
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
Powered by phpBB © 2001, 2005 phpBB Group
|
