<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<!--[if !mso]><style>v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style><![endif]--><style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Aptos;}
@font-face
{font-family:"Segoe UI";
panose-1:2 11 5 2 4 2 4 2 2 3;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
font-size:12.0pt;
font-family:"Aptos",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
span.gmailsignatureprefix
{mso-style-name:gmail_signature_prefix;}
span.EmailStyle19
{mso-style-type:personal-reply;
font-family:"Aptos",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:11.0pt;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1028" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="EN-US" link="blue" vlink="purple" style="word-wrap:break-word">
<div class="WordSection1">
<p class="MsoNormal"><span style="font-size:11.0pt">Hi Galen<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt">Thanks! I edited the query based on what you shared, and I think this will be helpful.
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt">I’ll put in a wishlist bug to make this a source.
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"><o:p> </o:p></span></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal" style="vertical-align:baseline"><!--[if gte vml 1]><v:shapetype id="_x0000_t75" coordsize="21600,21600" o:spt="75" o:preferrelative="t" path="m@4@5l@4@11@9@11@9@5xe" filled="f" stroked="f">
<v:stroke joinstyle="miter" />
<v:formulas>
<v:f eqn="if lineDrawn pixelLineWidth 0" />
<v:f eqn="sum @0 1 0" />
<v:f eqn="sum 0 0 @1" />
<v:f eqn="prod @2 1 2" />
<v:f eqn="prod @3 21600 pixelWidth" />
<v:f eqn="prod @3 21600 pixelHeight" />
<v:f eqn="sum @0 0 1" />
<v:f eqn="prod @6 1 2" />
<v:f eqn="prod @7 21600 pixelWidth" />
<v:f eqn="sum @8 21600 0" />
<v:f eqn="prod @7 21600 pixelHeight" />
<v:f eqn="sum @10 21600 0" />
</v:formulas>
<v:path o:extrusionok="f" gradientshapeok="t" o:connecttype="rect" />
<o:lock v:ext="edit" aspectratio="t" />
</v:shapetype><v:shape id="Picture_x0020_1" o:spid="_x0000_s1027" type="#_x0000_t75" style='position:absolute;margin-left:0;margin-top:.15pt;width:62.25pt;height:62.25pt;z-index:251660288;visibility:visible;mso-wrap-style:square;mso-width-percent:0;mso-height-percent:0;mso-wrap-distance-left:9pt;mso-wrap-distance-top:0;mso-wrap-distance-right:9pt;mso-wrap-distance-bottom:0;mso-position-horizontal:absolute;mso-position-horizontal-relative:text;mso-position-vertical:absolute;mso-position-vertical-relative:text;mso-width-percent:0;mso-height-percent:0;mso-width-relative:page;mso-height-relative:page'>
<v:imagedata src="cid:image001.png@01DAAD24.57FFA2D0" o:title="" />
<w:wrap type="square"/>
</v:shape><![endif]--><![if !vml]><img width="83" height="83" style="width:.8645in;height:.8645in" src="cid:image004.png@01DAAD24.58037360" align="left" hspace="12" v:shapes="Picture_x0020_1"><![endif]><b><span style="color:#16697A">Elizabeth
Davis</span></b><b> </b>(she/her), <b><i><span style="color:#16697A">Support & Project Management Specialist</span></i></b><span style="color:#16697A"> </span><span style="font-size:9.0pt;font-family:"Segoe UI",sans-serif"><o:p></o:p></span></p>
<p class="MsoNormal" style="vertical-align:baseline"><b><span style="color:#3366CC">Pennsylvania Integrated Library System
</span></b><b><span style="color:#4472C4">(PaILS) | SPARK</span></b><span style="color:#3366CC"> </span><span style="font-size:9.0pt;font-family:"Segoe UI",sans-serif"><o:p></o:p></span></p>
<p class="MsoNormal" style="vertical-align:baseline"><span style="color:#3366CC">(717) 256-1627</span> |
<a href="mailto:katherine.dannehl@sparkpa.org" target="_blank"><span style="color:#0563C1">elizabeth.davis@sparkpa.org</span></a> <br>
<a href="https://support.sparkpa.org/" target="_blank"><span style="color:#0563C1">support.sparkpa.org</span></a><span style="color:#3366CC"> |
</span><a href="mailto:support@sparkpa.org" target="_blank"><span style="color:#0563C1">support@sparkpa.org</span></a><span style="color:#3366CC"> </span><span style="font-size:9.0pt;font-family:"Segoe UI",sans-serif"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"><o:p> </o:p></span></p>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in">
<p class="MsoNormal"><b><span style="font-size:11.0pt;font-family:"Calibri",sans-serif">From:</span></b><span style="font-size:11.0pt;font-family:"Calibri",sans-serif"> Galen Charlton <gmc@equinoxoli.org>
<br>
<b>Sent:</b> Thursday, May 23, 2024 2:26 PM<br>
<b>To:</b> Evergreen Community Reports Email List <evergreen-reports@list.evergreen-ils.org><br>
<b>Cc:</b> Elizabeth Davis <elizabeth.davis@sparkpa.org><br>
<b>Subject:</b> Re: [Evergreen-reports] Offline Use Report<o:p></o:p></span></p>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<div>
<p class="MsoNormal">Hi,<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">There are two underlying database tables involved, offline.script (which has a row for each file loaded into an offline session) and offline.session (which has a row for each pending and completed session).<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">They're not the friendliest of tables to work with - in particular, their date/time fields are integer columns that store seconds since the Unix epoch rather than being true timestamp columns, and they don't have foreign key constraints.
That said, with direct SQL access, they are enough to write reports on which libraries and staff members are using offline, when they processed transactions, and how many transactions were processed. The to_timestamp() SQL function can be used to deal with
the date/time fields, e.g.,<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">SELECT to_timestamp(create_time) FROM offline.session ORDER BY 1 DESC LIMIT 5;
<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">They're also not set up as reporting sources, but there's no technical barrier to a small enhancement to make them accessible to the reporter (though converting those tables' date/time columns to the timestamp type would be a nice thing
to do at the cost of some more work in the code that actually does offline processing).<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">Regards,<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">Galen<o:p></o:p></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<div>
<p class="MsoNormal">On Thu, May 23, 2024 at 1:53<span style="font-family:"Arial",sans-serif"> </span>PM Elizabeth Davis via Evergreen-reports <<a href="mailto:evergreen-reports@list.evergreen-ils.org" target="_blank">evergreen-reports@list.evergreen-ils.org</a>>
wrote:<o:p></o:p></p>
</div>
<blockquote style="border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-right:0in">
<div>
<div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">Hello<o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"> <o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">I was curious if anyone has ever built a report on use of the offline mode use. I realize I can’t track how often they use it, but I am curious how often people are uploading transactions.
<o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"> <o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;vertical-align:baseline">
<!--[if gte vml 1]><v:shape id="Picture_x0020_2" o:spid="_x0000_s1026" type="#_x0000_t75" style='position:absolute;margin-left:0;margin-top:0;width:62.25pt;height:62.25pt;z-index:251658240;visibility:visible;mso-wrap-style:square;mso-width-percent:0;mso-height-percent:0;mso-wrap-distance-left:9pt;mso-wrap-distance-top:0;mso-wrap-distance-right:9pt;mso-wrap-distance-bottom:0;mso-position-horizontal:left;mso-position-horizontal-relative:text;mso-position-vertical:absolute;mso-position-vertical-relative:line;mso-width-percent:0;mso-height-percent:0;mso-width-relative:page;mso-height-relative:page' o:allowoverlap="f">
<v:imagedata src="cid:image003.png@01DAAD24.57FFA2D0" o:title="" />
<w:wrap type="square" anchory="line"/>
</v:shape><![endif]--><![if !vml]><img width="83" height="83" style="width:.8645in;height:.8645in" src="cid:image003.png@01DAAD24.57FFA2D0" align="left" hspace="12" v:shapes="Picture_x0020_2"><![endif]><b><span style="color:#16697A">Elizabeth
Davis</span> </b>(she/her), <b><i><span style="color:#16697A">Support & Project Management Specialist</span></i></b><span style="color:#16697A"> </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;vertical-align:baseline">
<b><span style="color:#3366CC">Pennsylvania Integrated Library System </span><span style="color:#4472C4">(PaILS) | SPARK</span></b><span style="color:#3366CC"> </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;vertical-align:baseline">
<span style="color:#3366CC">(717) 256-1627</span> | <a href="mailto:katherine.dannehl@sparkpa.org" target="_blank">
<span style="color:#0563C1">elizabeth.davis@sparkpa.org</span></a> <br>
<a href="https://urldefense.proofpoint.com/v2/url?u=https-3A__support.sparkpa.org_&d=DwMFaQ&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=j4SWsMDXRVOGlMb-EfMRIp9IjAos-OG-ewOKBUjTNh8&m=LEopF4gt2i0yeIXttZghdanVIwsgHU-hXHAKIKeMqIuaY5Z6xA7bjVMXbXYohxHe&s=KqtQ_xz2CmBF_Xz1B_0_fTjz5F2RC5zBl2WYU-OlhCM&e=" target="_blank"><span style="color:#0563C1">support.sparkpa.org</span></a><span style="color:#3366CC">
| </span><a href="mailto:support@sparkpa.org" target="_blank"><span style="color:#0563C1">support@sparkpa.org</span></a><span style="color:#3366CC"> </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"> <o:p></o:p></p>
</div>
</div>
<p class="MsoNormal">_______________________________________________<br>
Evergreen-reports mailing list<br>
<a href="mailto:Evergreen-reports@list.evergreen-ils.org" target="_blank">Evergreen-reports@list.evergreen-ils.org</a><br>
<a href="https://urldefense.proofpoint.com/v2/url?u=http-3A__list.evergreen-2Dils.org_cgi-2Dbin_mailman_listinfo_evergreen-2Dreports&d=DwMFaQ&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=j4SWsMDXRVOGlMb-EfMRIp9IjAos-OG-ewOKBUjTNh8&m=LEopF4gt2i0yeIXttZghdanVIwsgHU-hXHAKIKeMqIuaY5Z6xA7bjVMXbXYohxHe&s=ARjE9XqnJW4LiOjYzN8GtrWLyjaxU5a0neE9jO2hoFk&e=" target="_blank">http://list.evergreen-ils.org/cgi-bin/mailman/listinfo/evergreen-reports</a><o:p></o:p></p>
</div>
</blockquote>
</div>
<p class="MsoNormal"><br clear="all">
<br>
<span class="gmailsignatureprefix">-- </span><o:p></o:p></p>
<div>
<div>
<p class="MsoNormal">Galen Charlton<br>
Implementation and IT Manager<br>
Equinox Open Library Initiative<br>
<a href="mailto:gmc@equinoxOLI.org" target="_blank">gmc@equinoxOLI.org</a><br>
<a href="https://urldefense.proofpoint.com/v2/url?u=https-3A__www.equinoxOLI.org&d=DwMFaQ&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=j4SWsMDXRVOGlMb-EfMRIp9IjAos-OG-ewOKBUjTNh8&m=LEopF4gt2i0yeIXttZghdanVIwsgHU-hXHAKIKeMqIuaY5Z6xA7bjVMXbXYohxHe&s=BuAqn981OTckusYeb_4HrqTNiALDF115x1hAGc3L-mg&e=" target="_blank">https://www.equinoxOLI.org</a>
<br>
phone: 877-OPEN-ILS (673-6457)<br>
direct: 770-709-5581<o:p></o:p></p>
</div>
</div>
</div>
</body>
</html>