How to word a parameter input query in Base using Mariadb to select a date range?

rherbert · June 30, 2023, 9:01pm

I have set up a time log for ToDos that ties back into mu data base to select which staff did the work and which client needs to be billed for the work. I ultimately export query results into LO Calc for billing. Up to this time I have gone manually into my query in LO Base and changed the dates and manually run the query before copying into LO Calc. In BASE I use:

`tbl_Time`.`Date` > {d '2022-06-30' } AND `tbl_Time`.`Date` < {d '2022-08-01' }

I have also attached this as it appears in the graphical display for designing a Query (photo attached

) which uses:

> #2022-07-31#   and   < #2022-09-01#

What text syntax do I use such that I can create a form for query results and use a parameter input for both dates?

Thank you
Richard

Villeroy · July 1, 2023, 9:28am

[Base, Python] Todo and Done lists
Should work with MySQL as well.

Villeroy · July 4, 2023, 10:18am

Parameter queries with named parameters as in WHERE "DATE" BETWEEN :param_FROM AND :param_UNTIL require parsed mode (opposite of direct SQL).
Form-subform relations only work when master and slave are defined as parsed queries.
The only way how I can get a DATEDIFF result from my Maria-Test-DB is in direct mode:

SELECT *, TIMESTAMPDIFF(MONTH, `DT`, CURRENT_TIMESTAMP) AS `Months` FROM `Dates``

This is direct SQL mode with backtick quotes and TIMESTAMPDIFF with 3 parameters. The DATEDIFF function takes only 2 parameters and returns the difference in days. I tried 2 different JDBC connections and the SDBC driver that comes with LibreOffice.

I can use DATEDIFF in parsed mode but with 2 arguments as required by the database engine:

SELECT *, DATEDIFF( CURRENT_TIMESTAMP, "DT" ) AS "Days" FROM "Dates"

This is parsed mode with double quotes and DATEDIFF with 2 parameters. It returns the difference in days.
However, when I append WHERE "DT" BETWEEN :param_FROM AND :param_UNTIL the query fails with this error after entering the 2 query parameters

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘:param_FROM AND :param_UNTIL’ at line 1 at /home/buildslave/source/libo-core/connectivity/source/drivers/mysqlc/mysqlc_general.cxx:120

Therefore, I can not use anything like DATEDIFF to get a difference in months, years, hours, minutes, seconds. At least we can not use this record set with subforms.

And when we use DATEDIFF in order to get the difference in days, we can not use parameters, not even in parsed mode.
What is it worth to have an “integrated” SDBC driver when it does not support Base’s named parameters?

parsely · July 1, 2023, 6:10am

you are close: in the SQL string replace { d ‘2022-06-30’ } by :date_from and likewise the second {…} by :date_end
You now have a parameter query that will prompt for input.

With the form assistant you can create a form automatically, select the query as datasource of the form.

If the form has a tablecontrol than you can also add a knob and attach a macro to it, I only have the code in python, but can be converted easily to basic. dbName is the registered database name. The SQL comes from the form.activecommand()

def storeSQLasods(dbName, SQL):

    sURL = "temp9.ods"
    sURL = uno.systemPathToFileUrl("some path string" + sURL)
    # in basic sURL = converttourl( "filename" ) ?
    # args=dictToProperty({ "Hidden" : True})
    s = "private:factory/scalc"
    odoc = StarDesktop.loadComponentFromURL(s, "_blank", 0, ())

    sRangeName = "import4"
    osheet = odoc.Sheets.getByIndex(0)
    odoc.CurrentController.setActiveSheet(osheet)

    orange = osheet.getCellRangeByName("A1")
    oAddr = orange.getRangeAddress()

    odoc.DatabaseRanges.addNewByName(sRangeName, oAddr)

    orange = odoc.DatabaseRanges.getByName(sRangeName)
    orange.AutoFilter = True

    oDBRange = odoc.DatabaseRanges.getByName(sRangeName) # Range set for Query; will self adjust
    oDesc = oDBRange.getImportDescriptor()

    # Set appropriate descriptors
    for oPrp in oDesc:

        if oPrp.Name == "DatabaseName":
            oPrp.Value = dbName

        elif oPrp.Name == "SourceObject":
            oPrp.Value = SQL

        elif oPrp.Name == "SourceType":
            oPrp.Value = IMPORTMODE        # set to SQL

    oDBRange.getReferredCells().doImport(oDesc)

    odoc.setModified(False)   # Clear modified flag to not require saving question
    odoc.storeAsURL(sURL, ())

and the knob code will be

def frmjour_export_ods(oEvent):
    ''' export the data in a table control in a form, to a spreadsheet file '''
    oForm = oEvent.Source.Model.Parent
    SQL = oForm.ActiveCommand
    storeSQLasods(DBADMINNAME, SQL)

rherbert · July 1, 2023, 11:11am

Thank you @parsely.

What I have that works now is:

`tbl_Time`.`Date` > :Start_Date AND `tbl_Time`.`Date` < :Stop_Date  in SQL
and
> :Start_Date  and  < :Stop_Date  in the graphical interface

I am intrigued by your coding but I am a neophite with respect to programming and macros. I work with Indigenous Peoples and I am out to keep things user-friendly for them and simplistic for me as setting up information management for impoverished administration offices is a small aspect of help I provide.

How do I sift through code you sent to come up with a macro (basic) that I can tie to an “Export to Calc” button on a LO Base form that contains data in 1 table? What do I end up using with the button at the oForm level?

Thanks again,
Richard

parsely · July 1, 2023, 4:34pm

Upon clicking the knob a spreadsheet with the same columns (including headers) and rows as in the table control of the form will open. Converting to basic is no problem, but …

I ultimately export query results into LO Calc for billing.

But a spreadsheet can not be used for sending invoices to clients. How do you proceed from there / what do you need as the final result? May be you can edit the original question so a more to the point answer can be given.

I register that you want to keep things user friendly / ? trackable for inexperienced users?

rherbert · July 1, 2023, 5:53pm

Thank you for your reply @parsely,

I have better than rudimentary skills with LO Base, but no where near your level of expertise – I am constantly learning though. In that regard, I am not explaining myself well.

I do not know what you are referring to as “the knob”. I have searched for something referred to as the knob in LO base and have not been successful. What is, the knob?
This is my first foray in exporting data from a LO Base into Calc. I have no problem redesigning my query to tailor a result. Are there some examples out there?
I work with small remote Indigenous nations in Canada through a charity. Many are at an information management level where hard copies are kept and they have begun (or are considering) scanning documents without use of databases to search for documents. In that, there is no funding in these communities to respond to resource corporation requests for consultation. There needs to be a billable time tracking system to help keep these communities from incurring further debt. I tested this query out with sample data from another group I worked with but stopped at this level with copy/paste into LO Calc. I have an opportunity to engage with another remote community coming up this month and I am attempting to get something together that will use an exported form with a save as calc file button. I am not being hired for this data management work and so I am putting something together that can help them on my own time.
In this scenario, I only now considering how to set up calculations for time spent on the project. The two time columns need to be subtracted; then summed for each organization; and then presented in a separate invoice to each organization on a monthly basis with the time multiplied by a rate and then totalled. (….basically)

How much of this can I do in LO Base and is there a straight forward way to set this up as an invoice that may or may not be imported into SAGE for bookkeeping? I do not know what LO Base is capable of with this and what outputs can be exported for invoicing and/or bookkeeping.

Thanks
Richard

parsely · July 1, 2023, 6:45pm

sorry, knob should be replaced by button.

I sympathize with indigenous people. Can you upload (within the limits of privacy) an, extract, of what you have, f.i two organizations where one of them has more than one To_Do_ID? Or just a database containing the tbl_time will do. Drag and drop the table to a new database.
Calculations in base are a standard functionality, certainly at the level of subtraction/multiplication, with ‘group by’ it is easy to obtain a total per organization_ID per ToDo_ID.
I do not know SAGE, does it offer the opportunity to import invoice information present in .xlsx or .csv files

rherbert · July 1, 2023, 9:01pm

I can send you tables in a database. I use mariadb but I can copy into a new HSQLDB database (Yes??) and upload the .odb file. I will need to take time to change names for individuals and organizations along with contact information. The data though centered around my helping two indigenous groups of traditional leaders (Hereditary Chiefs). Meaning, I was the staff person on the ToDos. If one ToDo doer with multiple billable organizations works with 2 indigenous groups, then I will get something to you later tomorrow that respects information privacy.

Will that work??

SAGE is SImply Accounting and is the bookkeeping program First Nations are forced to use to meet standard accounting practices and forensic audits by government.Sage file Import

parsely · July 2, 2023, 12:59am

HSQLDB will be fine, 10-15 time records will do to show the principles.
SAGE looks like an application with standard ability to import data.

rherbert · July 2, 2023, 11:32am

Thank you @parsely.

I have sanitized the data to protect personal information. Find it attached.

TimeQryTest.odb (202.2 KB)

Time dates best to use for data span July and August 2022. I have attached a current working version of the entire relational database in case that is germane. There are minor differences with data I have included here.

FYI, none of the work identified in this data was billed to indigenous people. In fact, my work was donated (unfunded). Projects this data came from can be found at Recent Projects.

Thank you in advance.
Richard

parsely · July 2, 2023, 7:33pm

You are dealing with dates, a difficult datatype.
There is a DATEDIFF function, unclear to me whether MariaDB supports this, otherwise replace by an equivalent.
There is a correction for StopTimes after 24:00.
Instead of start/end dates you can consider month, that is a more distinct interval
I have added a field Hourly Rate to differentiate between projects
There is a Number column, in calc a sequence number column must be added (based on last invoice in SAGE), to comply with good bookkeeping standards.
Use an odt file with fields (insert field > more fields > database selection >…) finally insert a next record field. With CTRL+P the invoices can be generated.
Hope that LOcalc generating .xlsx output is compatible with SAGE, otherwise consider a CSV output.

TimeQryTestV1.odb (213.1 KB)

rherbert · July 2, 2023, 9:56pm

Thank you for your help @parsely!

The SQL code for your billing queries did not work in Mariadb as you suspected. The error I had was:

Screenshot_20230702_181343

The queries you sent did work at my end with the HSQLDB that you sent them back in. I have noted in past situations that there are differences with SQL between HSQLDB and Mairadb. Mariadb has not been a good resource for SQL differences when using LO Base. I have had some help from this forum (thank you to those people), one of those was @Wanderer. Regardless, I need to know who to connect with and where to get information to resolve these syntax differences.

Any thoughts on where to get information or whom to contact? I am reluctant to post/share your code as it is yours.

Thanks
Richard

Wanderer · July 2, 2023, 10:09pm

Start with posting the query from sql-view here, so we know, wich query fails. Some of us also work with MariaDB/MySQL and may have a look.
.
Usually I would suggest to switch to direct mode for SQL first, if I read “query could not be parsed”, but as it seemed to work with HSQLDB, I’m not convinced it will help in this case.

rherbert · July 2, 2023, 10:28pm

Hi @Wanderer. Thank you for responding.

Two comments back queries with test data were sent in an odb file:

TimeQryTestV1.odb (213.1 KB)

@parsely, may I post your code directly here?

Richard

Wanderer · July 2, 2023, 10:32pm

I can read sql also on my mobile, testing an odb will have to wait until I have time with my desktop…
.
And I assumed there is more than one query, so I/we have to find the place with the error, while you already know where it occurs.

rherbert · July 2, 2023, 10:40pm

I will go with the sharing of code on this forum with the odb file permits sharing with a copy and paste. The two qeiries are:

“Basic Billing”

SELECT "tbl_Organization"."Organization_ID",
 "tbl_File"."File_ID", "tbl_Time"."ToDo_ID", "tbl_Time"."Date", "tbl_Time"."Start_Time",
 "tbl_Time"."Stop_Time",
 CASEWHEN( DATEDIFF( 'MINUTE', "Start_Time", "Stop_Time" ) > 0, DATEDIFF( 'MINUTE', "Start_Time", "Stop_Time" ), 1440 - DATEDIFF( 'MINUTE', "Stop_Time", "Start_Time" ) ) AS "Difference",
 CASEWHEN( DATEDIFF( 'MINUTE', "Start_Time", "Stop_Time" ) > 0, DATEDIFF( 'MINUTE', "Start_Time", "Stop_Time" ), 1440 - DATEDIFF( 'MINUTE', "Stop_Time", "Start_Time" ) ) * "Hourly Rate" / 60 AS "Invoice",
 RIGHT( CONCAT( '000', "File_ID" ), 3 )  AS "number",
 "tbl_File"."Project_Title",  "tbl_Individual"."Name",
 "tbl_Organization"."Name",  "tbl_Organization"."Address"
 FROM "tbl_Time", "tbl_Organization",  "tbl_ToDo", "tbl_Individual",  "tbl_File"
 WHERE "tbl_Time"."Organization_ID" = "tbl_Organization"."Organization_ID"
 AND "tbl_Time"."ToDo_ID" = "tbl_ToDo"."ToDo_ID"
 AND "tbl_ToDo"."Individual_ID" = "tbl_Individual"."Individual_ID"
 AND "tbl_ToDo"."File_ID" = "tbl_File"."File_ID"
 AND "tbl_Time"."Date" > :start
 AND "tbl_Time"."Date" < :stop

“Grouped Billing”

SELECT "tbl_Organization"."Organization_ID" AS "OrganizationID",
  MIN( "tbl_File"."File_ID" ) AS "FileID",
  COUNT( "tbl_ToDo"."ToDo_ID" ) AS "Count",
  SUM( CASEWHEN( DATEDIFF( 'MINUTE', "Start_Time", "Stop_Time" ) > 0, DATEDIFF( 'MINUTE', "Start_Time", "Stop_Time" ), 1440 - DATEDIFF( 'MINUTE', "Stop_Time", "Start_Time" ) ) ) AS "Minutes",
  SUM( CASEWHEN( DATEDIFF( 'MINUTE', "Start_Time", "Stop_Time" ) > 0, DATEDIFF( 'MINUTE', "Start_Time", "Stop_Time" ), 1440 - DATEDIFF( 'MINUTE', "Stop_Time", "Start_Time" ) ) * "Hourly Rate" / 60 ) AS "Invoice",
  MIN( CONCAT( RIGHT( CONCAT( '000', "Organization_ID" ), 3 ), RIGHT( CONCAT( '000', "File_ID" ), 3 ) ) ) AS "number",
  MIN( "tbl_File"."Project_Title" ) AS "ProjectTitle",
  MIN( "tbl_Organization"."Name" ) AS "NameOrganization",
  MIN( "tbl_Organization"."Address" ) AS "Address"
  FROM "tbl_Time", "tbl_Organization", "tbl_ToDo", "tbl_Individual", "tbl_File"
  WHERE "tbl_Time"."Organization_ID" = "tbl_Organization"."Organization_ID"
  AND "tbl_Time"."ToDo_ID" = "tbl_ToDo"."ToDo_ID"
  AND "tbl_ToDo"."Individual_ID" = "tbl_Individual"."Individual_ID"
  AND "tbl_ToDo"."File_ID" = "tbl_File"."File_ID"
  AND "tbl_Time"."Date" > :start
  AND "tbl_Time"."Date" < :stop
  GROUP BY "tbl_Organization"."Organization_ID"

Thanks for looking at this.
Richard

parsely · July 3, 2023, 4:31am

DATEDIFF and CONCAT are OK with MariaDB, I suspect CASEWHEN must be replaced by a simple IF as documented built-in-functions

The invoice sequence number is a critical item, it can be easily added in a spreadsheet and hard in SQL?. In hindsight the composition of invoices must then be based on the spreadsheet. insert field > more fields > database Tab > add datasource > select the spreadsheet > and so on
With this approach only the sum of all projects for one organisation can be presented and no specification line showing each project.

Wanderer · July 3, 2023, 5:22am

I would start there also, but expect the “traditional” command CASE WHEN … THEN … ELSE … END; as syntax.

CASE WHEN DATEDIFF( 'MINUTE', "Start_Time", "Stop_Time" ) > 0 THEN DATEDIFF( 'MINUTE', "Start_Time", "Stop_Time" ) ELSE 1440 - DATEDIFF( 'MINUTE', "Stop_Time", "Start_Time" )  END AS "Minutes"

Villeroy · July 3, 2023, 10:28am

Read up the documentation of your database engine. DATEDIFF is defined somewhat different: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_datediff
In My/MariaSQL uses a function TIMESTAMPDIFF similar to HSQL’s DATEDIFF.