Canned responses for Google Mail

Had a patron who was disappointed that new Google mail only had one signature (unlike Outlook which allowed you to define multiple signatures).  But from Google Labs, you can setup canned responses [ActiveInBox]

Gmail has only one signature you can use, and it will add itself to all your emails and replies, every time. It is not exactly well designed or flexible for businesses.

If you’ve ever used the desktop version of Outlook you’ll know how easy it is to add different signatures to an email.

But you can also have quick access to multiple saved signatures in Gmail.

All you need to do is enable the Gmail ‘Lab’ (an extension) built by the Gmail team themselves, called Canned Responses. You can simply enable it from Gmail’s settings.   [from ActiveInBox]

Migrating PST files to Google Mail

Here is the tool to upload Outlook PST files to Google mail  https://tools.google.com/dlpage/outlookmigration

Creates a tag  <name of file>.pst and labels all imported emails with that tag.

Cleaning up harddrive - Windows

List of different tools  http://www.howtogeek.com/113012/10-best-free-tools-to-analyze-hard-drive-space-on-your-windows-pc/


WinDirStat is highly rated


TreeSize is one I have used in the past  

Website monitoring tool

A good free website monitoring tool is    uptimerobot


For lists of good tools:  

http://mashable.com/2010/04/09/free-uptime-monitoring/
https://besthostingsearch.net/tutorial/top-5-website-uptime-monitoring-tools/
http://www.capterra.com/website-monitoring-software/

SQL for all big records

This SQL will give you links to all the bib records in your III catalog:  (Sierra Listserv, Craig Borman)



SELECT
--*
--p.best_title_norm,
--p.best_author_norm,
'http://your.iii.catalog.url.here/record=b' || m.record_num || '&' || 'title=' || p.best_title_norm || '&' || 'author=' || p.best_author_norm || '/'

FROM
sierra_view.record_metadata m

LEFT OUTER JOIN sierra_view.bib_record_property p
ON (m.id = p.bib_record_id)

WHERE
m.record_type_code = 'b'

AND
m.campus_code = ''

AND
m.deletion_date_gmt IS null

LIMIT 500
--OFFSET 1500000
;



https://github.com/joemontibello/iii-sql-queries

pgAdmin error: MSVCP120.dll missing

Tried running pgAdmin, got error missing MSVCP120.dll

Downloaded Microsoft Visual C++ 2013 64 bit, still did not solve problem.


Turns out, have to download/install the x86 version, as well as x64 version,
even if you are running 64 bit Windows


From:
http://stackoverflow.com/questions/24726910/exe-gives-error-msvcp120-dll-is-missing-for-win7x64

SQL for fines Purged from III ILS

From Sierra Listserv, 2015-09

Report for fine purges from a Sierra database, used the query below to produce a report in the same format as the Fines Paid function.

WITH

  charge_type(charge_type_code, charge_type_desc) AS

  (

    VALUES

      ( '1', 'Manual Charge' ),

      ( '2', 'Overdue' ),

      ( '3', 'Replacement' ),

      ( '4', 'Adjustment' ),

      ( '5', 'Lost Book' ),

      ( '6', 'Overdue Renewed' ),

      ( '7', 'Rental' ),

      ( '8', 'Rental Adjustment' ),

      ( '9', 'Debit' ),

      ( 'a', 'Notice' ),

      ( 'b', 'Credit Card' ),

      ( 'p', 'Program' )

  ),

  fine_creation_mode(fine_creation_mode_code, fine_creation_mode_desc) AS

  (

    VALUES

      ( 'a', 'Automatic' ),

      ( 'm', 'Manual' ),

      ( 'x', 'Adjustment' )

  ),

  payment_status(payment_status_code, payment_status_desc) AS

  (

    VALUES

      ( '0', 'No Payment' ),

      ( '1', 'Full Payment' ),

      ( '2', 'Partial Payment' ),

      ( '3', 'Waive' ),

      ( '4', 'Item Busy' ),

      ( '5', 'Will Pay' ),

      ( '6', 'Purge' ),

      ( '7', 'Credit' ),

      ( '8', 'Adjustment' )

  )

SELECT

    to_char(fp.fine_assessed_date_gmt, 'MM/DD/YYYY') AS "Date Assessed",

    TRIM(BOTH FROM pn.last_name || ', ' || pn.first_name || ' ' || pn.middle_name) AS "Patron Name",

    pm.record_num AS "Patron Record",

    fp.patron_record_metadata_id AS "Patron Unique ID",

    fp.invoice_num AS "Invoice",

    fp.item_charge_amt AS "Charge Amount",

    fp.processing_fee_amt AS "Processing Fee",

    fp.billing_fee_amt AS "Billing Fee",

    ct.charge_type_desc AS "Charge Type",

    fp.charge_location_code AS "Owning Location",

    to_char(fp.paid_date_gmt, 'MM/DD/YYYY') AS "Date Paid",

    fp.tty_num AS "Statistics Group",

    fp.last_paid_amt AS "Last Payment",

    fp.iii_user_name AS "Initials",

    fcm.fine_creation_mode_desc AS "Creation Mode",

    fp.description AS "Description",

    fp.paid_now_amt AS "Amount Paid",

    ps.payment_status_desc AS "Payment Status",

    COALESCE(NULLIF(fp.payment_type_code, ''), 'null') AS "Payment Type",

    fp.payment_note AS "Payment Note"

  FROM

    sierra_view.fines_paid AS fp

    LEFT JOIN sierra_view.record_metadata AS pm

      ON (

        pm.id = fp.patron_record_metadata_id

      )

    LEFT JOIN sierra_view.patron_record_fullname AS pn

      ON (

        pn.patron_record_id = fp.patron_record_metadata_id

        AND pn.display_order = 0

      )

    LEFT JOIN charge_type AS ct USING ( charge_type_code )

    LEFT JOIN fine_creation_mode AS fcm USING ( fine_creation_mode_code )

    LEFT JOIN payment_status AS ps USING ( payment_status_code )

;

Regards,

Jim Nicholls

-----------------

For the query to work with multiple fine purges and when your fines_paid has more than just fine purge data, you need to add the following where clause:

  WHERE

    fp.payment_status_code = '6'

    AND fp.paid_date_gmt >= DATE '2014-12-16' -- Purge date, YYYY-MM-DD

I’ve added the query to the IUG clearinghouse.

http://innovativeusers.org/index.php?option=com_content&view=article&id=174&appParams[method]=Full+Record&appParams[id]=373  

Regards,

Jim

 

SQL to locate item records with no bib records

Here is SQL for III ILS to  locate item records with no bib records:

--  Locate  for item records with no links
--  John Boggs  - Sierra Listerv - 2015-09

SELECT
  *
FROM
  sierra_view.item_view
LEFT JOIN
  sierra_view.bib_record_item_record_link ON
  item_view.id = bib_record_item_record_link.item_record_id
WHERE
  bib_record_item_record_link.item_record_id is NULL

Hourly usage stats from ILS

Question:  Is there any way to get specific item usage by hour?  (From Innovative ILS)

You can get hourly checkout information through a SQL query assuming that your circ_trans view is being populated with data (I'm not sure if that is done by default for everyone yet), the period of time that is covered by your circ_trans data, and the period you want to investigate.

Here is a simple example that will give an hourly breakdown of all checkouts for yesterday:

SELECT

   TO_CHAR(ct.transaction_gmt, 'HH24:00')                AS "Hour",

   COUNT(*)                                              AS "Checkouts"

FROM

   sierra_view.circ_trans AS ct

WHERE

   ct.op_code = 'o'                                            -- checkout

   AND

   DATE(ct.transaction_gmt) = DATE(now() - INTERVAL '1 day')   -- yesterday

GROUP BY 1

ORDER BY 1

;

This example adds a column for location code and limits the output to items located in Langara's media dept:

SELECT

   ct.item_location_code                                 AS "Location",

   TO_CHAR(ct.transaction_gmt, 'HH24:00')                AS "Hour",

   COUNT(*)                                              AS "Checkouts"

FROM

   sierra_view.circ_trans AS ct

WHERE

   ct.op_code = 'o'                                            -- checkout

   AND

   DATE(ct.transaction_gmt) = DATE(now() - INTERVAL '1 day')   -- yesterday

   AND

   ct.item_location_code LIKE 'lm%'                            -- from Langara Media

GROUP BY 1,2

ORDER BY 1,2

;

This example limits the query from Example 1 to a specific item record number. This may be closest to what you're aiming for:

SELECT

   TO_CHAR(ct.transaction_gmt, 'HH24:00')                AS "Hour",

   COUNT(*)                                              AS "Checkouts"

FROM

   sierra_view.circ_trans AS ct

JOIN

   sierra_view.item_view AS iv

   ON

   iv.id = ct.item_record_id

WHERE

   ct.op_code = 'o'                                            -- checkout

   AND

   DATE(ct.transaction_gmt) = DATE(now() - INTERVAL '1 day')   -- yesterday

   AND

   iv.record_num = '1211068'                                   -- specific item record

GROUP BY 1

ORDER BY 1

;

--

Brent Searle