This is disappointing but fully appreciate the Devs position on this.
Quite sometime ago, I tried to make the reporting easier for our team. I wrote a Powershell script to report the counts. This might be something others can use so I have included below. It will probablyt need some changes as it is fairly specific to our locations and service times.
`# ======================================================================
#
SongUsageReport
#
#
======================================================================
Import-Module GetSQL
Get-Sql -quiet -session SU -lite -connection "C:\Users\HV-Media\OpenLP\Production\data\songusage\songusage.sqlite"
Write-Host "...Create the Temporary table"
$sql = "
CREATE TABLE songusage_temp (
service_date date, service text, title text, authors text, ccl_number text
)
"
try {
$rslt = Get-Sql -quiet -session SU $sql
} catch {
Write-Host " *** Create failed"
}
#
Which Service
#
$first_service_date = Read-Host "Date of first service as YYYY-MM-DD"
while ( $first_service_date ) {
$last_service_date = Read-Host "Date of last service as YYYY-MM-DD "
if ( ! $last_service_date ) {
$last_service_date = $first_service_date
}
Write-Host
Write-Host "...Truncate Temporary Table"
$sql = "DELETE FROM songusage_temp"
try {
$rslt = Get-Sql -quiet -session SU $sql
} catch {
Write-Host " *** Truncate failed"
}
#
Create a Query that will retrieve the data to enable us to report to CCLI
#
Write-Host "...Extract Data from SongUsage"
$sql = "
INSERT INTO songusage_temp
SELECT DISTINCT date(usagedate) AS service_date,
CASE
WHEN usagetime < '14:00:00' THEN 'AM'
WHEN usagetime > '14:00:00' THEN 'PM'
END AS service,
title, authors, ccl_number
FROM songusage_data
WHERE usagedate >= '$first_service_date'
AND usagedate <= '$last_service_date'
AND copyright != 'Public Domain'
ORDER BY service_date, service, title
"
try {
$rslt = Get-Sql -quiet -session SU $sql
} catch {
Write-Host " *** Extract failed"
}
Write-Host "...Report Song usage for $first_service_date until $last_service_date"
$sql = "
SELECT count(*) AS Count, title as 'Song Title', substr(authors,1,50) AS Authors, ccl_number AS 'CCLI#'
FROM songusage_temp
GROUP BY title, authors, ccl_number
ORDER BY title, authors, ccl_number
"
try {
Get-Sql -quiet -session SU $sql -OutputVariable TABLE | Format-Table | Out-String | Write-Output
} catch {
Write-Host " *** Select failed"
}
$first_service_date = Read-Host "Date of first service as YYYY-MM-DD"
}
#
All done
#
Write-Host
Write-Host "...Drop the Temporary table"
$sql = "DROP TABLE songusage_temp"
try {
$rslt = Get-Sql -quiet -session SU $sql
} catch {
Write-Host " *** Drop failed"
}
$rslt = Get-Sql -quiet -session SU -close
Write-Host
$wait = Read-Host "Press RETURN to continue"
======================================================================
Thats all folks
======================================================================`
