SQL for recovering a pull list on Horizon ILS

The Horizon ILS has a list of holds where the report is run but if the list is not kept it is lost and you have to run a sql script to recover the list.  You will require sql studio, and a login to the database.  Once logged in.

Select your ILS DATABASE using SQL Studio or change "$ILSDatabase" in the script below to be the name of your ILS Database and it will automatically select the database for you.

        

 USE [$ILSDATABASE]

 GO

update request set fill_date = NULL, fill_location = NULL,
fill_item# = NULL where
fill_date = datediff(dd,'01 jan 1970',getdate())

and fill_location = '$location' and request_status = 0

 

Below is a sample of what the script looks like in SQL Studio.

In the script change $location with your location code that is setup in Horizon to recover the branch pull list.

Customizing Shopify to remove the add to card button

Going with Shopify has been pretty good for my organization (in my opinion) the recurring costs of $29.99 a month for e-commerce capabilities is not overly onerous for a smaller organization. There are a couple of things I wonder if it can do but I'll have to look into that later.  For now I want to show a capability that shopify has that is pretty useful; the customization of the templates to remove the add to cart button of a product and replace it with something else.  It is a nifty and powerful feature.

So here is how I came across this.  It is a particular use case, you see the organization is putting on an event and they wanted to sell tickets to that event.  Which is why I did a post about Event Ticketing.  The organization already had a monaris account for taking debit payment, I decided to work with shopify because it tied in with monaris and there wouldn't be any additional cost to the organization besides shopify (and the monaris transaction cut).

So I setup the sales page, in a standard three row grid layout sorted by manual order.

So when we look at event one, the layout is the same more or less depending on what we put in the description field, and you can add images and other items.  By default all the product pages are the same and if you have your products organized by categories, then your suggested purchases come from the same pool

The description box shown below is what is shown under the add to cart button.

So we have our templates that are assigned to each product, and we have the ability to change the image, title and description.  We don't have the ability to change the link or the add to cart button.  

So with Event Two, if we want to change the add to cart button we will need to create a new template.  The procedure is provided by shopify but a few more graphics in their article would help a lot.

https://help.shopify.com/en/manual/online-store/themes/os/customize/replace-add-to-cart

To edit the theme you need to be logged into the administration section and select customize

You will then be taken to an edit screen with your home page selected and some options for editing the page as shown below.

Then click on the 3 little dots and select edit code.

Then scroll down to templates and select "Add a new template"

Shown below is the settings for the template file.  Select Product from the drop down liquid for the template type and in this case and the example provided by shopify we are naming our file requires-contact

edit product.requires-contact.liquid you should see it under the templates section as shown below.

and replace {% section 'product-template' %} with {% section 'product-template-requires-contact' %} and hit save located on the top right of the editor window.

You can see the difference in the image below. We have the original product.liquid on the right and the new product.requires-contact.liquid on the left.

Now scroll down to the section section of the templates as shown below and click on the "Add a new section", following the shopify tutorial I called mine "product-template-requires-contact"

With that done, click on product-template.liquid; select all and copy the code then click on product-template-requires-contact.liquid and paste the code into the file and click save.  At this point we can actually assign the template to the product we want to point to.  Since this is a one off this will work fine, however if I had several I had to do this two I would have to figure out a way of assigning the correct URL.

In the shopify admin, go to the products section and find the product you want to assign the template to.  Scroll down to the Online Store section it is under the Tags section as shown below next to pricing and inventory.  Change the template to requires-contact and hit save.  Now any changes you make to product-template-requires-contact.liquid can be viewed on that product page.

Going back and editing product-template-requires-contact.liquid here is where the shopify tutorial and I differ.  In their tutorial they want to you hide the whole product form; which I don't want to do.  I do want all the options and feel of having the add to cart button but I just don't want the "Add to cart" button.  I want to boot the user to another payment system like eventbright.

What I found is if I hide the form I also lose the suggested items, and if I hide only part of the form I lose some of the CSS on the site as shown below.  You can see the logo has a grey back ground and the suggested items are removed.

What I found is I need to edit the button section which is around line 220 as shown below.

Here is the code.  The highlighted green is what is added.

<a href="$Yourweblink" class="btn product-form__cart-submit">Buy Tickets</a>

               <button style="display:none;"  type="submit" name="add"

                {% comment %} {% unless current_variant.available %} aria-disabled="true"{% endunless %}

                  aria-label="{% unless current_variant.available %}{{ 'products.product.sold_out' | t }}{% else %}{{ 'products.product.add_to_cart' | t }}{% endunless %}"

                  class="btn product-form__cart-submit{% if section.settings.enable_payment_button and product.selling_plan_groups == empty %} btn--secondary-accent{% endif %}"

                       {% if settings.enable_ajax %}aria-haspopup="dialog"{% endif %} {% endcomment %}

                  data-add-to-cart >

                

                  <span data-add-to-cart-text>

                   {% comment %} {% unless current_variant.available %}

                      {{ 'products.product.sold_out' | t }}

                    {% else %}

                      {{ 'products.product.add_to_cart' | t }}

                    {% endunless %}

                     {% endcomment %}

                  </span>

                           

                  <span class="hide" data-loader>

                {% comment %}    {% include 'icon-spinner' %} {% endcomment %}

                  </span>

                </button>

              {% comment %}  {% if section.settings.enable_payment_button %}

                  {{ form | payment_button }}

                {% endif %} {% endcomment %}

As you can see I have commented out all the variable sections and added a css style on the actual cart button.  I created a link which has the same class as the "add to cart" button and gave it the name "Buy Tickets".  With the end result being very similar to our other product pages included the suggested items. However now we have a note on that product that the "buy tickets" button is going to route you to another site.

Using Event Ticketing in Shopify

The organization I work for started using shopify during the pandemic, and it has been a useful tool but because of factors beyond my control it was a little tricky to setup. However now that it is been working for about 8 months now, they are looking at running an event and selling tickets through shopify, instead of using eventbright. So I found a "app" in shopify called Event Ticketing, it seems to do the trick. The setup is pretty long, but you do get a nice result after.

It would help if you had this information when you go to create your events in Event Ticketing

• A list of events with dates, times and durations

• Shopify Graphic, 344x521px

• Ticket Graphics - PDF - generic logo is 800x300px

• Apple Wallet Icon 114px x 114px

• Apple Wallet Logo 837px x 285px

• Apple Wallet Banner 1125px x 294 px

Events are made one at a time, with tickets and graphics being done at this time.  You can go back and edit graphics for events, though it will be easier to do it all at once.

Create Event -> Customize Ticket -> Customize Variant (Shopify Version of Product). -> Make variant active (allow it to be sold)

IMPORTANT.  Besides updating the description, status and the image DO NOT EDIT the variant.

Log into shopify and select apps

 

Select Event Ticketing

Create Event

Select the event type, give it a name, location and date

Enter Ticket Details such as price inventory, ticket type (guest, comp, online, vip, etc)

Here is where you can specify a total number of tickets to sell.  You check the box set total capacity.  In the screenshot below, you can see the inventory is no longer available on a per ticket basis.  For the purposes of most library events I would recommend specifying a specific number of free tickets first if desired then taking away from the ticket pool.  Event Ticketing can split tickets into pools, but it does so equally for the ticket types so there is no 80/20, 90/10 split or anything like that. See explaination

Below I have 2 tickets: General admission and COMP which takes from the same pool.  However it will split the tickets automatically into equal pools.  It is recommended that if you want to approach an event this way, that you have multiple ticket types much like a hockey or football game (front row, 2nd row, 3rd row, 4rth row, 5 row, comps).

Additional Options were not available in the trial.

Shopify Preview.  Below is a preview of the typical setup and below is a explanation of using total capacity.  I don’t recommend using total capacity unless seats will start being sold based on how close a person is the “stage” or event.  IE. Hockey Game bowel colours.  You can click on the preview variant to view the info..

When using the set total capacity it event ticketing doubles the number of tickets for each event.  You can see the tickets either together or separated. See below.

Hit Save to save the event.

<!--IMPORTANT--!>

Don't edit the options, values, or variants in Shopify! It is critically important that you do not edit the product options, values, variants, SKU's, or duplicate the product as this is not supported yet.

DO NOT:

• Add, edit, or remove product options and values

• Duplicate the product or variants

• Add more variants

• Change SKU's

• Change the product type (keep as Ticket)

Doing any of the above actions will cause the app to be unable to issue and fulfill tickets.

It is SAFE to edit the description, images, price, title, tax, and tags. You may also delete variants.

<!--IMPORTANT--!> 

By Default, the variant (shopify version) is not active.  You must edit the variant and change the status from draft to active but do so only after your event is ready. (Ticket customization, etc). After finishing with setting up the event the next step is to customize the tickets.

With the tickets there two types of tickets you can use PDF and Apple Wallet

Setup PDF - You can get very fancy with the ticket setup.  The fastest and easiest way is to select standard.  

Then you will get a basic interface where you can upload a identity banner (branding) and change the label and text color.

For this example I have Location Name, Price, Event Start Date (Date & Time), Event Name and Ticket Type

Select Done when you are done editing.  You should do both the Apple Wallet and pdf version.  If you want to view this you must have a iOS device available

 

Once your ticket is customized you can update the image, status and description on variant. Click on the Event name to edit the variant.  It is recommended that you edit ONLY the description, status and image.

When you are done editing the image and description and want to make the item live, change the status from draft to active and save the variant.  If you want to see a preview select the preview button in the top right next to More actions.  Hit save to save your changes.

Setting up a WOLS server

What is a WOLS server? WOLS is my acronym for Wake On Lan Shutdown server. I deploy these in organizations that want to have scheduled startup and shutdown system for any windows clients.

You have to get some information from the Windows Client.

If your not on a domain or if you want to use a local admin, create a local admin account, I called my sysadm and add them to the administrators group.  Give them a good password.

Then you will need the mac address and IP of the machine(s) you want to wake and shutdown,

Allow firewall rules that allow wol (wake on lan) packets ICMP for echo requests

RPC firewall rules

Open 

File and Printer Sharing (Echo Requests)

Remote Administration

Remote Service Management

Configure registry for remote access. 

Open CMD prompt as admin and runsc config RemoteRegistry start= auto sc start RemoteRegistry

Then open regedit.exe through the start menu search or run box, and go to 

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System

find a key called LocalAccountTokenPolicy.  If it doesn't exist create it.   It is a 32-bit DWORD key named LocalAccountTokenFilterPolicy and set the value to 1.

To remove this tweak you can set the value to 0 or just delete the key.

I've had issues with powersaving on the NIC, so I do recommend disabling the power saving options on the network adapter.

Now for the WOLS Server.  Make sure your linux distro is up-to-date

Install SAMBA.

sudo apt-get install samba

one done you should be able to run the following example and it should shut down the windows machine.

net rpc shutdown -I 192.168.18.x -U sysadm%$PASSWORD -t 1 -f

Now setup Wake On Lan

The etherwake command sends a Wake-On-LAN “Magic Packet” under Linux operating system

To install etherwake

sudo apt-get install etherwake or net-tools

then in the terminal type

wakeonlan xx:xx:xx:xx:xx:xx

replacing the xx:xx:xx:xx:xx:xx with the mac address of the machine you want woke up.

You can view the video on youtube. https://youtu.be/4WOMYpmkKyM