OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Macro help with dates & calculations

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API
View previous topic :: View next topic  
Author Message
Julianna
Power User
Power User


Joined: 15 Oct 2005
Posts: 77

PostPosted: Tue Jan 01, 2008 11:12 am    Post subject: Macro help with dates & calculations Reply with quote

Hi.

Drew Jensen had helped me create a macro for a database (http://www.oooforum.org/forum/viewtopic.phtml?t=28613&highlight=), but I'm having some difficulties with updating it.

I had originally updated the CalculateTotals sub to account for a tax change in 2006. The code I provided in the thread for that is actually incomplete, and should be:

Code:

sub CalculateTotals( oev as variant )
'
' Fill in the following fields in
' Orders table
'   Subtotal = (Asking_Price + Asking_Price2 + Asking_Price3 + Asking_Price4 + Asking_Price5) -
'              ((Asking_Price + Asking_Price2 + Asking_Price3 + Asking_Price4 + Asking_Price5) * Discount_Rate)
'
'   Total = Subtotal + (Subtotal * GST) + (Subtotal * PST) + Shipping_Cost
dim RowSet as variant
dim Sale_Date as double
dim Asking as double
dim discount as double
dim GST as double
dim PST as double
dim shipping as double
dim subtotal as double
dim Total as double


If Sale_Date < 07/01/06 Then
  RowSet = oEv.Source.Model.Parent
  with RowSet.columns
    Asking = .getByName("Asking_Price").getDouble
    Asking2 = .getByName("Asking_Price2").getDouble
    Asking3 = .getByName("Asking_Price3").getDouble
    Asking4 = .getByName("Asking_Price4").getDouble
    Asking5 = .getByName("Asking_Price5").getDouble
    Discount = .getByName("Discount_Rate").getDouble
    SubTotal =  ( Asking + Asking2 + Asking3 + Asking4 + Asking5 ) - ( ( Asking + Asking2 + Asking3 + Asking4 + Asking5 ) * Discount )
    GST = SubTotal * .07
    PST = SubTotal * .08
    shipping = .getByName("Shipping_Cost").getDouble
    TOTAL = SubTotal + GST + PST + Shipping
    .getByName("Subtotal").updateDouble( subtotal )
    .getByName("GST").updateDouble( GST )
   .getByName("PST").updateDouble( PST )
   .getByName("Total_Cost").updateDouble( TOTAL )
  end with
ElseIf Sale_Date > 07/01/06 Then
  RowSet = oEv.Source.Model.Parent
  with RowSet.columns
    Asking = .getByName("Asking_Price").getDouble
    Asking2 = .getByName("Asking_Price2").getDouble
    Asking3 = .getByName("Asking_Price3").getDouble
    Asking4 = .getByName("Asking_Price4").getDouble
    Asking5 = .getByName("Asking_Price5").getDouble
    Discount = .getByName("Discount_Rate").getDouble
    SubTotal =  ( Asking + Asking2 + Asking3 + Asking4 + Asking5 ) - ( ( Asking + Asking2 + Asking3 + Asking4 + Asking5 ) * Discount )
    GST = SubTotal * .06
    PST = SubTotal * .08
    shipping = .getByName("Shipping_Cost").getDouble
    TOTAL = SubTotal + GST + PST + Shipping
    .getByName("Subtotal").updateDouble( subtotal )
    .getByName("GST").updateDouble( GST )
   .getByName("PST").updateDouble( PST )
   .getByName("Total_Cost").updateDouble( TOTAL )
  end with
Else
  RowSet = oEv.Source.Model.Parent
  with RowSet.columns
    Asking = .getByName("Asking_Price").getDouble
    Asking2 = .getByName("Asking_Price2").getDouble
    Asking3 = .getByName("Asking_Price3").getDouble
    Asking4 = .getByName("Asking_Price4").getDouble
    Asking5 = .getByName("Asking_Price5").getDouble
    Discount = .getByName("Discount_Rate").getDouble
    SubTotal =  ( Asking + Asking2 + Asking3 + Asking4 + Asking5 ) - ( ( Asking + Asking2 + Asking3 + Asking4 + Asking5 ) * Discount )
    GST = SubTotal * .06
    PST = SubTotal * .08
    shipping = .getByName("Shipping_Cost").getDouble
    TOTAL = SubTotal + GST + PST + Shipping
    .getByName("Subtotal").updateDouble( subtotal )
    .getByName("GST").updateDouble( GST )
   .getByName("PST").updateDouble( PST )
   .getByName("Total_Cost").updateDouble( TOTAL )
  end with
end If
end sub


...or so I think. I was playing around with the code to get it to recognize another tax change, and now the above code doesn't seem to do anything, date-wise (i.e. the GST calculates at 7% regardless of the Sale_Date).

The database it's supposed to work on is:
https://sourceforge.net/project/showfiles.php?group_id=160483&package_id=180549
(ArtDatabaseTwoTaxesv1.1.1.odb.zip)

Anyone out there have time to help me figure out what the problem is?

Thanks.

Julianna
Back to top
View user's profile Send private message
probe1
Moderator
Moderator


Joined: 18 Aug 2004
Posts: 2560
Location: Chonburi Thailand Asia

PostPosted: Sun Jan 13, 2008 9:41 pm    Post subject: Reply with quote

Julianna,

SALE_DATE is defined as double, so that your IF/ELSE combination should ask for a double (you coded a multi division; 07/01 is 7, 7/06 is 1.17 - that's not your date)

I assume this is american date notation (m/d/y), so...

Try
Code:
If Sale_Date > 38899 Then


38899 is the internal date representation for 1.July 2006 (date based on standard 1899-12-31, see Tools>Options>OpenOffice.org Calc>Calculations)

BTW: what if Sale_Date IS exactly 1. July?

HTH
_________________
Cheers
Winfried
My Macros
DateTime2 extension: insert date, time or timestamp, formatted to your needs
Back to top
View user's profile Send private message Visit poster's website
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Mon Jan 14, 2008 3:23 am    Post subject: Reply with quote

Sorry, WInfried
Formatted doubles as date/times do not work with databases. Dates are a distinct type. If the underlying database engine provides a conversion from double to date/time, then it would use SQL standard 1900-01-01 as day zero rather than OOo standard 1899-12-31. Paste some values from Calc into Base. Dates are +2 days off unless the Calc document uses 1900-01-01 as day zero.

@Julianna,
Code:

...
dim Sale_Date as double
...
If Sale_Date < 07/01/06 Then
...

Apart from the syntax problem in '07/01/06', where does double Sale_Date come from? If it where a spreadsheet value, it would be a double indeed and you could calculate the way as Winfried suggested. But your database seems to be unrelated to spreadsheets.
Back to top
View user's profile Send private message
probe1
Moderator
Moderator


Joined: 18 Aug 2004
Posts: 2560
Location: Chonburi Thailand Asia

PostPosted: Tue Jan 15, 2008 7:48 am    Post subject: Reply with quote

Villeroy,

of course you are right: I saw the comparison and jumped on that - forgetting it was about Base, not Calc.

I should stay at/in the pool....
_________________
Cheers
Winfried
My Macros
DateTime2 extension: insert date, time or timestamp, formatted to your needs
Back to top
View user's profile Send private message Visit poster's website
Julianna
Power User
Power User


Joined: 15 Oct 2005
Posts: 77

PostPosted: Fri Jan 18, 2008 1:27 pm    Post subject: Reply with quote

Villeroy wrote:
Sorry, WInfried
@Julianna,
Code:

...
dim Sale_Date as double
...
If Sale_Date < 07/01/06 Then
...

Apart from the syntax problem in '07/01/06', where does double Sale_Date come from? If it where a spreadsheet value, it would be a double indeed and you could calculate the way as Winfried suggested. But your database seems to be unrelated to spreadsheets.


The Sale_Date comes from a table in the database, and that field is populated before the tax gets triggered.

I must admit I'm having some trouble following the syntax discussion. Winfried's comment I understood, but I'm not sure what to do with yours.

As for "BTW: what if Sale_Date IS exactly 1. July?", I figured I'd use a => or <= statement at some point, but I wanted to tackle the date issue first.

Thanks.
Back to top
View user's profile Send private message
probe1
Moderator
Moderator


Joined: 18 Aug 2004
Posts: 2560
Location: Chonburi Thailand Asia

PostPosted: Fri Jan 18, 2008 7:02 pm    Post subject: Reply with quote

Julianna wrote:
The Sale_Date comes from a table in the database


It's defined with dim Sale_Date as double at top of your posted code - and the variable is not assigned by any statement I see. So it should be zero at time of comparison?
Issue a print Date_Sale to see.



If it *is* set somewhere (i don't see), you can....

The internal HSQLDB stores date fields in form defined by java.sql.date, which is (since 1.4) a long, representing seconds since 1970-01-01.

So the value to compare with has to be in same form (long, containing seconds).
You can use the BASIC runtime function DATEDIFF to calculate the seconds, see F1 online help

Code should be
Code:
If Sale_Date < DateDiff("s", "1/1/1970", "1/7/2006") Then



Hope I got it right, this time.
_________________
Cheers
Winfried
My Macros
DateTime2 extension: insert date, time or timestamp, formatted to your needs
Back to top
View user's profile Send private message Visit poster's website
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sat Jan 19, 2008 3:09 am    Post subject: Reply with quote

Removed redundant code in the if...elseif...else structure. I assume the form's recordset includes a date field "Sale_Date". See comments.
Code:

sub CalculateTotals( oev as variant )
'
' Fill in the following fields in
' Orders table
'   Subtotal = (Asking_Price + Asking_Price2 + Asking_Price3 + Asking_Price4 + Asking_Price5) -
'              ((Asking_Price + Asking_Price2 + Asking_Price3 + Asking_Price4 + Asking_Price5) * Discount_Rate)
'
'   Total = Subtotal + (Subtotal * GST) + (Subtotal * PST) + Shipping_Cost
dim RowSet as variant

REM dim Sale_Date as double 'No doubles, we compare dates
dim Sale_Date '< this will be the generic value from the database
dim bas_Sale_Date as date '< this will be a type of the Basic language
dim bas_CompareDate as date '< this will be a type of the Basic language

dim Asking as double
dim discount as double
dim GST as double
dim PST as double
dim shipping as double
dim subtotal as double
dim Total as double

REM get the form
RowSet = oEv.Source.Model.Parent
REM create a date value to compare with:
bas_CompareDate = Date(2006, 7, 1) '1st of July 2006

with RowSet.columns
  REM get date value of form's current record's field named "Sale_Date":
  Sale_Date = .getByName("Sale_Date").getDate()
  REM Sale_Date is a structure containing 3 numbers year, month and day to be used in any language
  REM We want to use this date in Basic. We use a Basic function to convert the structure into Basic's date type (see Basic help on function DateSerial)
  bas_Sale_Date = DateSerial(Sale_Date.Year, Sale_Date.Month, Sale_Date.Day)
  Asking = .getByName("Asking_Price").getDouble
  Asking2 = .getByName("Asking_Price2").getDouble
  Asking3 = .getByName("Asking_Price3").getDouble
  Asking4 = .getByName("Asking_Price4").getDouble
  Asking5 = .getByName("Asking_Price5").getDouble
  Discount = .getByName("Discount_Rate").getDouble
  SubTotal =  ( Asking + Asking2 + Asking3 + Asking4 + Asking5 ) - ( ( Asking + Asking2 + Asking3 + Asking4 + Asking5 ) * Discount )
  If bas_Sale_Date < bas_CompareDate Then
    GST = SubTotal * .07
    PST = SubTotal * .08
  else
    GST = SubTotal * .06
    PST = SubTotal * .08
  endif
  shipping = .getByName("Shipping_Cost").getDouble
  TOTAL = SubTotal + GST + PST + Shipping
  .getByName("Subtotal").updateDouble( subtotal )
  .getByName("GST").updateDouble( GST )
  .getByName("PST").updateDouble( PST )
  .getByName("Total_Cost").updateDouble( TOTAL )
end with
end sub


There are constants .08 and .06 from where you calculate your GST and PST. If those constants are something like changing tax rates in the real world, then you have to distribute a new version every time when the rates change. I would implement a table where changes can be entered and let a sub-form get the current rate at the time of Sales_Date. Possibly you could drop most of that crazy Basic code, show the calculated total in a sub form and use a macro to write the calculation result into the main form.
Back to top
View user's profile Send private message
Julianna
Power User
Power User


Joined: 15 Oct 2005
Posts: 77

PostPosted: Sat Jan 19, 2008 7:22 am    Post subject: Reply with quote

Villeroy wrote:

There are constants .08 and .06 from where you calculate your GST and PST. If those constants are something like changing tax rates in the real world, then you have to distribute a new version every time when the rates change. I would implement a table where changes can be entered and let a sub-form get the current rate at the time of Sales_Date. Possibly you could drop most of that crazy Basic code, show the calculated total in a sub form and use a macro to write the calculation result into the main form.


When I originally created the db, taxes for Ontario hadn't changed in years. Then it changed twice in the past three years!

The idea of a table is great. I was actually playing around with adding a GST_Rate and PST_Rate to the table so it can be calculated for every sale. Not as efficient for high sales volumes, but it would also give the option of not charging tax to people who are exempt from the taxes.

I added GST_Rate and PST_Rate to the table, added and linked the fields to the form and modified the code, but the GST and PST aren't calculating at all now.

I checked the table, and the values I populated into the GST_Rate and PST_Rate fields from the form have been saved into the table (so I know the field was added to the form correctly).

Code:

sub CalculateTotals( oev as variant )
'
' Fill in the following fields in
' Orders table
'   Subtotal = (Asking_Price + Asking_Price2 + Asking_Price3 + Asking_Price4 + Asking_Price5) -
'              ((Asking_Price + Asking_Price2 + Asking_Price3 + Asking_Price4 + Asking_Price5) * Discount_Rate)
'
'   Total = Subtotal + (Subtotal * GST) + (Subtotal * PST) + Shipping_Cost
dim RowSet as variant

dim Asking as double
dim discount as double
dim GST_Rate as double
dim PST_Rate as double
dim GST as double
dim PST as double
dim shipping as double
dim subtotal as double
dim Total as double

REM get the form
RowSet = oEv.Source.Model.Parent

with RowSet.columns
  Asking = .getByName("Asking_Price").getDouble
  Asking2 = .getByName("Asking_Price2").getDouble
  Asking3 = .getByName("Asking_Price3").getDouble
  Asking4 = .getByName("Asking_Price4").getDouble
  Asking5 = .getByName("Asking_Price5").getDouble
  Discount = .getByName("Discount_Rate").getDouble
  SubTotal =  ( Asking + Asking2 + Asking3 + Asking4 + Asking5 ) - ( ( Asking + Asking2 + Asking3 + Asking4 + Asking5 ) * Discount )
  GST = ( SubTotal * GST_Rate )
  PST = ( SubTotal * PST_Rate )
  shipping = .getByName("Shipping_Cost").getDouble
  TOTAL = SubTotal + GST + PST + Shipping
  .getByName("Subtotal").updateDouble( subtotal )
  .getByName("GST").updateDouble( GST )
  .getByName("PST").updateDouble( PST )
  .getByName("Total_Cost").updateDouble( TOTAL )
end with
end sub
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sat Jan 19, 2008 7:38 am    Post subject: Reply with quote

Mmmmh, why do you write calculated values into the table? I mean, you could derive the calculated TOTAL at any time from existing values (including tax rates).
Back to top
View user's profile Send private message
Julianna
Power User
Power User


Joined: 15 Oct 2005
Posts: 77

PostPosted: Sat Jan 19, 2008 12:26 pm    Post subject: Reply with quote

The original intent was for the values to be stored and locked, but it seems the macro simply changes the values every time the form is launched.
Back to top
View user's profile Send private message
Julianna
Power User
Power User


Joined: 15 Oct 2005
Posts: 77

PostPosted: Sun Feb 03, 2008 6:37 am    Post subject: Reply with quote

I needed some time away from this so I could look at it properly. The following code uses additional "GST_Rate" and "PST_Rate" fields in the Orders table for the calculation.

By setting these fields to default with the current rates, this will allow for the tax rates to change however often they want and the rate only needs to be changed in the table settings. This also allows the user to override the tax rate for tax exemption situations.

Code:
sub CalculateTotals( oev as variant )
'
' Fill in the following fields in
' Purchase_Order table
'   SUBTOTAL = ASKING_PRICE - (ASKING_PRICE * DISCOUNT_RATE)
'
'   TOTAL = SUBTOTAL + (SUBTOTAL * GST) + (SUBTOTAL * PST ) + SHIPPING_COST
dim RowSet as variant
dim Sale_Date as double
dim Asking as double
dim discount as double
dim GSTRate as double
dim PSTRate as double
dim GST as double
dim PST as double
dim shipping as double
dim subtotal as double
dim Total as double

  RowSet = oEv.Source.Model.Parent
  with RowSet.columns
    Asking = .getByName("Asking_Price").getDouble
    Discount = .getByName("Discount_Rate").getDouble
    GSTRate = .getByName("GST_Rate").getDouble
    PSTRate = .getByName("PST_Rate").getDouble
    SubTotal =  Asking - ( Asking * Discount )
    GST = SubTotal * GSTRate
    PST = SubTotal * PSTRate
    shipping = .getByName("Shipping_Cost").getDouble
    TOTAL = SubTotal + GST + PST + Shipping
    .getByName("Subtotal").updateDouble( subtotal )
    .getByName("GST").updateDouble( GST )
   .getByName("PST").updateDouble( PST )
   .getByName("Total_Cost").updateDouble( TOTAL )
end with
end sub
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
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