Computing membership expiration dates

Creating tables and queries
Post Reply
phollox
Posts: 7
Joined: Thu Sep 13, 2018 5:53 am

Computing membership expiration dates

Post by phollox »

Hello community. My first post here. I'm a newbie in Base.

I'm setting up a database to keep track of membership and payments. I created different tables with member information, using the 3 rules of normality, and created several nifty forms to fill my tables with actual data. My problem is the computation of expiration date of membership for each member. I'm trying to set this as a Query. I don't want to involve macros, if possible.

The membership could be purchased at any point of the year, and expires on Dec 31 (rules of the society). There are packages of 1 year and 3 years. So, a member could buy a 3 year membership on January 2015 (expiring on Dec 2017), and then another year membership on February 2015 (expiring now on Dec 2018), plus another year at some point of 2016, pushing the expiration to Dec 2019. I can compute an expiration date per each payment, but that is meaningless. I need some form of aggregation that's smart eough to undertand that a Payment is being done when the membership is active, and sum the purchased time to the current expiration date, and that needs to be done per each member.

I'm not handling tens of thousands of members, so using a complex convoluted approach is feasible without fearing a high impact on performance (I think).

Here's a small example

I can produce a view/query with only the membership payments

Code: Select all

memberID paymentID paymentDate expirePeriod
0        0         2012-02-02  1
0        10        2013-02-15  3
0        30        2014-01-10  1
0        60        2015-08-15  3
1        5         2012-03-12  1 
1        15        2013-04-01  1
1        20        2013-10-01  3
1        35        2014-02-01  1
I managed to add, via joins and COUNT() along with a <= condition, a payment counter per member. Not sure if that's useful.

Code: Select all

count memberID paymentID paymentDate expirePeriod
1     0        0         2012-02-02  1
2     0        10        2013-02-15  3
3     0        30        2014-01-10  1
4     0        60        2015-08-15  3
1     1        5         2012-03-12  1 
2     1        15        2013-04-01  1
3     1        20        2013-10-01  3
4     1        35        2014-02-01  1
In any case, I need to eventually compute this result. The status is computed based on the current date (September 2018).

Code: Select all

memberID expirationDate memberStatus
0        2019-12-31     active
1        2017-12-31     inactive
Any help or advice is appreciated
LibreOffice 6 on Linux (Arch Linux with kernel 4.14.55 LTS)
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: Computing membership expiration dates

Post by eremmel »

You need a query that takes the payment history in account. Such a query can be crafted, but you will need an recursive SQL statements. Such a statement is not possible with the embedded SQL engine shipped with OpenOffice (HSQLDB 1.8).
If you like to use the Split database configuration (See tutorials) you will be able to use recursive SQL and we might solve the problem. Note that you are then using HSQLDB 2.4x version or newer. You might also like to use a database like SQLite, PostgreSQL, MS SQL Server, Oracle.
MySQL has its own way of doing things, far from standard.

So let us know if you are prepared to take a deep dive in SQL.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
phollox
Posts: 7
Joined: Thu Sep 13, 2018 5:53 am

Re: Computing membership expiration dates

Post by phollox »

Thanks for the reply eremmel. I've been trying to avoid using a split configuration. I wanted portability and simplicity of the result. A single file with all the data, no requiting connections to a database, The fact that I'm using Base intead of Access is already conflictive, but is easier to tell the people that will get the data: Look, install OpenOffice and double click on the file, than telling then, Install OpenOffice, then Install MySQL or MariaDB, and connect to the database using a JDBC...

That being said, I have to admit that I never tried it. How easy is to do it, as an user, not a developer? Meaning, installing the software and checking the connection with the SQL engine?

Probably the best solution is setting a SQL server on website and using html/php to create the forms. A completely different beast. And not sure if there's an easy to trasnlate the logic behind my forms into a web-based form. I use a lot of filtering annd subforms to discriminate data

Thanks again.
LibreOffice 6 on Linux (Arch Linux with kernel 4.14.55 LTS)
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: Computing membership expiration dates

Post by eremmel »

When I look to the description of [Wizard] Create a new 'split' HSQL 2.x database and [Tutorial] Splitting an "embedded HSQL database"
It should be possible to work still with an single distribution-base-document. I never tried it, but you might play with it and try to move your current project to the 'split' database version.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Computing membership expiration dates

Post by Villeroy »

viewtopic.php?f=21&t=86071 with a macro installer for ExtractHSQLDB.py which extracts and reconnects your database automatically.
viewtopic.php?f=21&t=77543 provides a GUI to connect a Base document to an external HSQLDB and to any hsqldb.jar (the single driver file). Also included is a customizable Basic module which allows you to distribute database packages.

Your user does not need any of the above macros.
A self made database package (Base document, database files, driver and optionally forms in stand-alone documents) can be installed in simple steps:
1) Install OpenOffce or LibreOffice (meanwhile I prefer the latter).
2) Create a directory and extract the zip file to it.
3) Allow macro execution for this directory in Tools>Options>Security>Macro Security>Trusted Sources. Do NOT turn off macro security.
4) Open the contained MyDatabase.odb. 3 success messages pop up to inform you that the database has been connected to some files, to a database driver and that the database has been registered.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: Computing membership expiration dates

Post by eremmel »

I think I've a solution for you based on the attached 'split database' zip file. I took the route:
0 PAYMENTS is a simple table that hold data based on your description.
1 A payment in year Y last for year Y or Y+2.
2 Multiple payments in the same year can be considered as one payment for the sum-of-periods year.
For this I made a view in the database (vPaymentsPerMemberYear)
3 To compare the payments over a series of years we have to have an ordering
For this I made a view in the database (vPaymentsPerMemberYearRanked)
4 Based on this view (3) we can now roll up the payments and calculate the Expire Year with an recursive SQL statement.
This is found in query (qPaymentsExpiringPerMember).
5 Note that you cannot view the views in the database easy, so a copy of the view-query is also saved (qPaymentsPerMemberYear and qPaymentsPerMemberYearRanked)

Just unpack the zip file and open the base document Payments.zip and open Payments\PaymentsExpireYear_Split_HSQLDB_2.4.0.odb.
Note it is on a google drive because it is too large to attach here.

Here is the recursive SQL statement for those who do not want to open the zip file.

Code: Select all

WITH
	RECURSIVE wPaymentsExiprePerMember(MEMBER, PAYYEAR, PERIODS, EXPIREYEAR, LEVEL) AS (
		select MEMBER, PAYYEAR, PERIODS, PAYYEAR+PERIODS-1 as EXPIREYEAR, LEVEL
		from "vPaymentsPerMemberYearRanked"
		where LEVEL=0
	UNION 
		select MEMBER, PAYYEAR, PERIODS 
			, CASE WHEN X.PAYYEAR <= Y.EXPIREYEAR
					THEN  Y.EXPIREYEAR + X.PERIODS 
					ELSE X.PAYYEAR+X.PERIODS-1 
			  END as EXPIREYEAR
			, LEVEL
		from "vPaymentsPerMemberYearRanked" X  -- X: Current payment; Y: previous payment
			inner join wPaymentsExiprePerMember Y on X.MEMBER = Y.MEMBER and X.LEVEL = Y.LEVEL+1
	)
SELECT * FROM wPaymentsExiprePerMember
The trick is in the calculation of EXPIREYEAR. For the very first payment it is for the current year. When a new payment is received in a year before the ExpireYear ends, the ExpireYear is increased with the amount of periods, else we correct 1 period for the current year.
 Edit: Experienced SQL programmers might ask why I used UNION iso the common seen UNION ALL in recursive SQL (e.g. Oracle, MS SQL Server,...). It has to do with the offical standard occording this post Recursive query regression and Recursive query runs forever. I also ran into this isue. 
Last edited by eremmel on Fri Sep 14, 2018 10:04 am, edited 2 times in total.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
phollox
Posts: 7
Joined: Thu Sep 13, 2018 5:53 am

Re: Computing membership expiration dates

Post by phollox »

Thanks eremmel and Villeroy. I'll try those solutions later, and will mark this post as solved.

Thanks again
LibreOffice 6 on Linux (Arch Linux with kernel 4.14.55 LTS)
phollox
Posts: 7
Joined: Thu Sep 13, 2018 5:53 am

Re: Computing membership expiration dates

Post by phollox »

Villeroy wrote:viewtopic.php?f=21&t=86071 with a macro installer for ExtractHSQLDB.py which extracts and reconnects your database automatically.
viewtopic.php?f=21&t=77543 provides a GUI to connect a Base document to an external HSQLDB and to any hsqldb.jar (the single driver file). Also included is a customizable Basic module which allows you to distribute database packages.

Your user does not need any of the above macros.
A self made database package (Base document, database files, driver and optionally forms in stand-alone documents) can be installed in simple steps:
Thanks Villeroy for the reply. I still need to check the content of those files. But my understanding is that it's possible to distribute in a single file (a database package) a LibreOffice Base document that uses a more up-to-date version of HSQLDB, and the final user won't need to do the manual intall of the jar file (the jar file needs to be included in the db package). To build this file, is a simple as creating a ZIP and changing the extension to .odb? Also, from the description of the links, it seems like I could do the migration process only with the 2nd link. And that could also provide help to create the aforementioned database package

Thanks again, and sorry for being so newbie in this issue. I've been reading a lot these past few weeks, and your name always comes up in my searches for documentation. It was a nice surprise to see the pluto icon in a reply of my first post in this forum.

Thanks
LibreOffice 6 on Linux (Arch Linux with kernel 4.14.55 LTS)
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Computing membership expiration dates

Post by Villeroy »

No, simply you create a zip (or any other archive) with an odb, a database folder and a driver.
Tell the user to extract the archive in some dedicated directory rather than in his download folder.
Then open the extracted odb where you have set up my auto-run macro which adjusts the paths to the driver, to the database and the registration path when necessary.

The user opens the odb and sees 2 or 3 messages:
"Successfully connected this document to database backend C:\charlie\PholloxDB\database\Phollox"
"Successfully connected this document to jdbc driver C:\charlie\PholloxDB\driver\hsqldb.jar"
"This database has been registered under the name "Phollox""

Where "PholloxDB" is the dedicated folder created by your user.
"database" is a subfolder containing database files whose names start with "Phollox" (Phollox.script Phollox.properties Phollox.data)
The driver "hsqldb.jar" is located in subfolder "driver".
And Tools>Options>Base>Databases (or the data source window) shows "Phollox" as a database name pointing to the database document, possibly C:\charlie\PholloxDB\Phollox.odb

1) Apart from the optional registration, this structure of files and subfolders is what you get when using my Python macro.
2) Then you add the Basic modules "AutoInstall" and "Helpers" from my "FreeHSQLDB" library to your database document.
2a) Tools>Macros>Organize>Basic... [Organizer...], tab:Modules.
2b) Dbl-Click your database document name on the left side. With AOO you may need to select your document and push the [New] button in order to get hold of the Standard lib
2c) Then navigate to FreeHSQLDB and drag one module after the other to your document's standard library holding the Ctrl-Key[/u] in order to perform a copy rather than a shift of the module.
3) Edit your copy of AutoInstall to adjust the constants on top of the module.
4) Finally you make your document run its "onDocumentOpen" routine when the database document is opened (Tools>Customize... tab:Events).
5) Put everything (Base doc, database folder and driver folder) in a zip.
Example database: [HSQL2] Self Made Easter Sunday in SQL
----- STANDALONE FORMS --------------
If your database is registered under some name, running my form extraction macro of the FreeHSQLDB library stores all your forms in stand-alone Writer documents which can be accessed throuh OS desktop links and hyperlinks. Add a forms folder to your zip package and use the same registration name in the AutoInstall module.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply