Talis Developer Network - itm_rotate report - Comments

sorting by sequence and class mark in itm_rotate

Brian Crampton — Wed, 29 Nov 2006 10:00:28 +0000

I was asked about sorting by sequence and class mark in the itm_rotate script via email and thought my answer may be useful to this topic as well.

This solution is making use of the preselect to sort the items to be rotated.

Sequence does not require an additional join as the ITEM.SEQUENCE_ID attribute contains the sequence code e.g. JF, SOFT but the ITEM.CLASS_ID attribute is an id value. To get the class number you would have to join the ITEM and CLASSIFICATION tables on the common CLASS_ID attribute and then use the CLASSIFICATION.CLASS_NUMBER attribute in the sort.

Be aware that the sort would be the standard Sybase sorting so there is no control apart from ascending or descending order. This may not be a problem but occasionally users want a slightly different order for local reasons e.g. numbers would file before alphabetical but users want it the other way.

The order in the order by statement is important. In the example below if you wanted to sort by site then class number , then by sequence you would swap I.SEQUENCE_ID and C.CLASS_NUMBER around in the list.

The preselect would look something like this.

print STDERR "Selecting items that are due for rotation.. ".
"please wait..\n";

my($query) = "select I.ITEM_ID, I.ACTIVE_SITE_ID ".
"from ITEM I, ITEM_ROTATION_LINK L, WORKS W, CLASSIFICATION C".
"where I.ITEM_ID = L.ITEM_ID ".
"and I.WORK_ID = W.WORK_ID ".
"and C.CLASS_ID = I.CLASS_ID " .
"and L.STATUS = 0 ".
"and L.CURRENT_PERIOD = 'T' ".
"and L.DUE <= '$ShortCurrentDate' ";

$query .= "and I.ACTIVE_SITE_ID in ($locations) "
if ($locations);

$query .= "order by I.ACTIVE_SITE_ID, I.SEQUENCE_ID, C.CLASS_NUMBER , W.AUTHOR_DISPLAY, W.TITLE_DISPLAY";

(@preselect_data) = &sql($d, $query);

I have included the author and title based attributes in the order by statement. If you don't want to sort by those I would remove them from the order by list.

As I said in the earlier posting such changes could have an effect on performance and any such changes should be tested on the MIS server first until you are happy with what is happening.

Thanks

Brian Crampton
Developer, Talis

preselect

Brian Crampton — Thu, 28 Sep 2006 16:55:21 +0100

No you are entirely right. Good catch.

I was using a local version of the script where I must have been playing with the preselect.

I have updated the earlier posting to avoid any confusion

Brian Crampton
Developer, Talis

Is this incorrect?

markhughes — Thu, 28 Sep 2006 15:36:01 +0100

Brian, I have just tried this and it didn't produce any results.

On closer examination, I saw that in the main.pl, the preselect section showed the L.DUE 'LongPastDate' to be L.DUE 'ShortCurrentDate'.

I altered it and it now works great.

Thanks
Mark

http://www.wirral-libraries.net/
http://www.stembystem.co.uk/

modifying the preselect

Brian Crampton — Wed, 20 Sep 2006 15:28:38 +0100

The complete code goes after the first { and before the return(0);

You then have to change the zero in the return statement to 1 e.g.

return(1);

In the Talis Scripts section there is an area (Scripts/Perl MIS reports/About MIS reports) on setting up a local area and making changes to the preselect or selection criteria

Thanks

Brian Crampton
Developer, Talis

Itm_rotate report

markhughes — Wed, 20 Sep 2006 14:52:43 +0100

Brian,

In the current preselect.pl there are the following lines:
--------------------
sub preselect
{
return(0);
}
1;
--------------------

Do I just remove these lines and replace with your code?

Cheers
Mark

http://www.wirral-libraries.net/
http://www.stembystem.co.uk/

Thanks Brian

markhughes — Tue, 19 Sep 2006 09:12:16 +0100

Thanks for that Brian,

I'll give it a try on our MIS Server.

Cheers
Mark

http://www.wirral-libraries.net/
http://www.stembystem.co.uk/

sorting the itm_rotate output

Brian Crampton — Tue, 19 Sep 2006 08:58:12 +0100

Looking at the script the only sort appears to be in the preselect where the items to be rotate are selected and then ordered by the items ACTIVE_SITE_ID. This sort is done so that the report can be grouped by site. From this sorted list of items each item is then processed to get the details about rotation and the bibliographic/sequence details and then the details sent to the output file.

The order you are seeing with each site is just the order it comes back in while it should be in the order you list. There is a defect referenced in Talis Solutions

The script needs to be changed so that the results for each item are stored in an array within PERL, sorted and then sent to the output. This is potentially quite a rewrite of the script.

A quick work around would be to use the following as a local preselect
print STDERR "Selecting items that are due for rotation.. ". "please wait..\n";


                my($query) =    "select I.ITEM_ID, I.ACTIVE_SITE_ID ".

                                "from   ITEM I, ITEM_ROTATION_LINK L, WORKS W".

                                "where  I.ITEM_ID = L.ITEM_ID ".

                                "and    I.WORK_ID = W.WORK_ID ".

                                "and    L.STATUS = 0 ".

                                "and    L.CURRENT_PERIOD = 'T' ".

                                "and    L.DUE <= '$ShortCurrentDate' ";
                $query .= "and  I.ACTIVE_SITE_ID in ($locations) "

                                                                if ($locations);
                $query .= "order by I.ACTIVE_SITE_ID, W.AUTHOR_DISPLAY, W.TITLE_DISPLAY";

(@preselect_data) = &sql($d, $query);

This will sort the items by item site then author display and then title display. This is not the ideal solution and could affect performance of the script depending on the size of database. While it would be possible to sort on other criteria it would involve linking in other tables which could be problematic. The best solution is to sort within the PERL script itself.

If you want to try it please test fully on the MIS server before going live and remember to use a local version of the itm_rotate script.

Brian Crampton
Developer, Talis

itm_rotate report

markhughes — Mon, 18 Sep 2006 16:35:32 +0100

Anyone know of a way of changing the sort order for the itm_rotate script? Currently it defaults to Sequence code, size code, class number and suffix. I would like to be able to change this order to say AUTHOR and TITLE.

Any ideas? I'm not well up on perl.

Cheers
Mark